Выбор массива по условию excel

Выборка значений из таблицы Excel по условию

Если приходиться работать с большими таблицами определенно найдете в них дублирующийся суммы разбросаны вдоль целого столбца. В тоже время у вас может возникнуть необходимость выбрать данные из таблицы с первым наименьшим числовым значением, которое имеет свои дубликаты. Нужна автоматическая выборка данных по условию. В Excel для этой цели можно успешно использовать формулу в массиве.

Как сделать выборку в Excel по условию

Чтобы определить соответствующие значение первому наименьшему числу нужна выборка из таблицы по условию. Допустим мы хотим узнать первый самый дешевый товар на рынке из данного прайса:

Автоматическую выборку реализует нам формула, которая будет обладать следующей структурой:

В месте «диапазон_данных_для_выборки» следует указать область значений A6:A18 для выборки из таблицы (например, текстовых), из которых функция ИНДЕКС выберет одно результирующие значение. Аргумент «диапазон» означает область ячеек с числовыми значениями, из которых следует выбрать первое наименьшее число. В аргументе «заголовок_столбца» для второй функции СТРОКА, следует указать ссылку на ячейку с заголовком столбца, который содержит диапазон числовых значений.

Естественно эту формулу следует выполнять в массиве. Поэтому для подтверждения ее ввода следует нажимать не просто клавишу Enter, а целую комбинацию клавиш CTRL+SHIFT+Enter. Если все сделано правильно в строке формул появятся фигурные скобки.

Обратите внимание ниже на рисунок, где в ячейку B3 была введена данная формула в массиве:

Выборка соответственного значения с первым наименьшим числом:

С такой формулой нам удалось выбрать минимальное значение относительно чисел. Далее разберем принцип действия формулы и пошагово проанализируем весь порядок всех вычислений.

Как работает выборка по условию

Ключевую роль здесь играет функция ИНДЕКС. Ее номинальное задание – это выбирать из исходной таблицы (указывается в первом аргументе – A6:A18) значения соответственные определенным числам. ИНДЕКС работает с учетом критериев определённых во втором (номер строки внутри таблицы) и третьем (номер столбца в таблице) аргументах. Так как наша исходная таблица A6:A18 имеет только 1 столбец, то третий аргумент в функции ИНДЕКС мы не указываем.

Чтобы вычислить номер строки таблицы напротив наименьшего числа в смежном диапазоне B6:B18 и использовать его в качестве значения для второго аргумента, применяется несколько вычислительных функций.

Функция ЕСЛИ позволяет выбрать значение из списка по условию. В ее первом аргументе указано где проверяется каждая ячейка в диапазоне B6:B18 на наличие наименьшего числового значения: ЕСЛИB6:B18=МИНB6:B18. Таким способом в памяти программы создается массив из логических значений ИСТИНА и ЛОЖЬ. В нашем случаи 3 элемента массива будут содержат значение ИСТИНА, так как минимальное значение 8 содержит еще 2 дубликата в столбце B6:B18.

Следующий шаг – это определение в каких именно строках диапазона находится каждое минимальное значение. Это нам необходимо по причине определения именно первого наименьшего значения. Реализовывается данная задача с помощью функции СТРОКА, она заполняет элементы массива в памяти программы номерами строк листа. Но сначала от всех этих номеров вычитается номер на против первой строки таблицы – B5, то есть число 5. Это делается потому, что функция ИНДЕКС работает с номерами внутри таблицы, а не с номерами рабочего листа Excel. В тоже время функция СТРОКА умеет возвращать только номера строк листа. Чтобы не получилось смещение необходимо сопоставить порядок номеров строк листа и таблицы с помощи вычитанием разницы. Например, если таблица находится на 5-ой строке листа значит каждая строка таблицы будет на 5 меньше чем соответственная строка листа.

После того как будут отобраны все минимальные значения и сопоставлены все номера строк таблицы функция МИН выберет наименьший номер строки. Эта же строка будет содержать первое наименьшее число, которое встречается в столбце B6:B18. На основании этого номера строки функции ИНДЕКС выберет соответствующее значение из таблицы A6:A18. В итоге формула возвращает это значение в ячейку B3 в качестве результата вычисления.

Как выбрать значение с наибольшим числом в Excel

Поняв принцип действия формулы, теперь можно легко ее модифицировать и настраивать под другие условия. Например, формулу можно изменить так, чтобы выбрать первое максимальное значение в Excel:

Если необходимо изменить условия формулы так, чтобы можно было в Excel выбрать первое максимальное, но меньше чем 70:

=70;»»;B6:B18));СТРОКА(B6:B18)-СТРОКА(B5);»»)))’ >

Как в Excel выбрать первое минимальное значение кроме нуля:

Как легко заметить, эти формулы отличаются между собой только функциями МИН и МАКС и их аргументами.

Теперь Вас ни что не ограничивает. Один раз разобравшись с принципами действия формул в массиве Вы сможете легко модифицировать их под множество условий и быстро решать много вычислительных задач.

5 основных функции для работы с массивами

Доброго времени суток друзья!

Сегодня я бы хотел затронуть тему массивов, так как частенько возникает ситуация, когда нужно найти значение в большом массиве данных. Для этих целей в Excel существует целый раздел функции для работы с массивами, которые помогают нам выполнять работы с массивами, но сейчас мы рассмотрим самые распространенные.

Читать еще: Котангенс в excel

Обратите внимание, на эти функции, так как работа с огромными массивами данных, одна из самых распространенных и правильное использование этих функции позволит вам значительно упростить и облегчить работу с таблицами Excel.

Ну, что же, изучим необходимые функции для работы с массивами:

1. Функция ВЫБОР (CHOOSE)

Позволит вам выбрать значение из общего списка по указанному номеру позиции:

=ВЫБОР(2;»Стул»;»Стол»;»Шкаф»;»Диван»)

2. Функция ИНДЕКС (INDEX)

Эта функция возвращает указанное значение из одно- или двумерного диапазона:

=ИНДЕКС(A1:C6;4;3)

Как видно с примера, полученное значение 37, в указанном диапазоне стоит на пересечении строки №4 и столбика №3 в диапазоне A1:C6 указанном в формуле. В более простом примере показано как в диапазоне С1:С6, на 2 месте находится значение 15:

=ИНДЕКС(С1:С6;2)

3. Функция ПОИСКПОЗ (MATCH)

Эта функция вернет позицию значения, которое вы будете искать в указанном диапазоне:

=ПОИСКПОЗ(B3;B2:B5;0)

С примера вы можете видеть что слово «Стол» занимает 2 позицию в указанном диапазоне. Замечу, что третий аргумент в функции не является обязательным. При введенном значении 0, функция вернет ту позицию элемента массива, которое точно совпадает со значением, которое мы ищем. В случае, когда точное совпадение отсутствует, функция выдаст ошибку #Н/Д (#N/A).

4. Функция ГПР (HLOOKUP)

Ищет значение в указанном диапазоне и возвращает значение ячейки, которая находится в указанной строке того же столбца: =ГПР(C1;$B$1:$E$2;1;ЛОЖЬ). Как видите с примера, функция ГПР ищет в указанном диапазоне $B$1:$E$2 (знаком $ я указал абсолютную ссылку) и согласно условию возвращает искомое значение из первой строки, а аргумент «ЛОЖЬ» означает, что-либо, будет найдено нужное значение, либо мы получим ошибку #Н/Д.

5. Функция ВПР (VLOOKUP)

Во многом, даже очень во многом, похожа на функцию ГПР, но используется не для горизонтальных массивов, а вертикальных, что наиболее распространено в использовании.

=ВПР(B4;$B$2:$C$5;2;ЛОЖЬ)

Как видим, формула идентична предыдущей функции ГПР и так же ищет указанный номер «B4» в диапазоне $B$2:$C$5 со знаком $ (это сделано для создания абсолютной ссылки, что бы при копировании формулы на диапазон, аргумент не будет изменен), в третьем столбце, так как аргумент функции равен 2. Ну и четвёртый аргумент равен значению «ЛОЖЬ», это означает, что-либо будет найдено совпадение значений, либо будет получено сообщение об ошибке #Н/Д. Теперь при необходимости, мы копируем формулу, и она перенесёт все правильные аргументы по всему диапазону вычислений. Это возможно стало из-за абсолютной ссылки на массив значений, а вот первый аргумент на B4, при копировании, должен измениться на B5 и так далее.

А на этом у меня всё! Я очень надеюсь, что описание 5 основных функций для работы с массивами вам стали ближе и понятнее. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

Не забудьте поблагодарить автора!

Деньги для людей умных составляют средство, для глупцов — цель. А. Декурсель

Выбор массива по условию excel

Подскажите, пожалуйста, как решить задачу. Есть данные (пример во вложении), нужно данные на листе 1 загнать в массив и из этого массива по условиям на листе 2 вывести значения в столбец G. Например, если в массиве столбец 1 равен условию на втором листе в столбце B и столбец 2 из массива равен условию на листе 2 из столбца С и т. д. то выводится значение в стоблец G. Для первого варианта ответ 100, для второго 170. Значения в столбцах на первом листе могут повторяться, но все пять значений одновременно они уникальны. Спасибо за любой совет.

Читать еще: Excel lookup на русском

Pelena

Дата: Воскресенье, 14.02.2016, 12:55 | Сообщение № 2

«Черт возьми, Холмс! Но как. »
ЯД 41001765434816

Ответить

Bregra1

Дата: Воскресенье, 14.02.2016, 13:03 | Сообщение № 3

Udik

Дата: Воскресенье, 14.02.2016, 13:30 | Сообщение № 4

неправда Ваша 🙂
коды символов для Условия1: 65 и 192
Pelena, видать за Вас отредактировала, и в примере привели совпадающие наборы строки 5-6 и последние 4строки совпадают.

а так можно было бы не массивкой

[p.s.]упс, тоже просмотрел, что ВБА, но тогда исправляйте пример

а так можно было бы не массивкой

[p.s.]упс, тоже просмотрел, что ВБА, но тогда исправляйте пример Udik

вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com

а так можно было бы не массивкой

[p.s.]упс, тоже просмотрел, что ВБА, но тогда исправляйте пример Автор — Udik
Дата добавления — 14.02.2016 в 13:30

Bregra1

Дата: Воскресенье, 14.02.2016, 13:40 | Сообщение № 5

Rioran

Дата: Воскресенье, 14.02.2016, 14:07 | Сообщение № 6

Предлагаю такой вариант, пример во вложении. Даже если будет ошибка уникальности — возьмёт первое попавшееся значение при просмотре строк сверху вниз.

Роман, Москва, voronov_rv@mail.ru
Яндекс-Деньги: 41001312674279

Сообщение Bregra1, здравствуйте.

Bregra1

Дата: Воскресенье, 14.02.2016, 14:57 | Сообщение № 7

Udik

Дата: Воскресенье, 14.02.2016, 15:00 | Сообщение № 8

200?’200px’:»+(this.scrollHeight+5)+’px’);»>
Option Explicit
Public Sub test()
Dim arr1()
Dim i As Integer, j%, k%, buffRaw%
Dim buffBool As Boolean
Dim buff2 As Byte
Const strL1 = «Лист1»
Const strL2 = «Лист2»

With Worksheets(strL1)
arr1 = .Range(«A2:F13»).Value
End With
With Worksheets(strL2)
For i = 2 To 4
buff2 = 0
For j = 1 To UBound(arr1)
buffBool = True
For k = 1 To 5
buffBool = (buffBool And arr1(j, k) = .Cells(i, k + 1).Value)
Next k
If buffBool Then
buff2 = buff2 + 1
buffRaw = j
End If
Next j
Select Case buff2
Case 0
.Cells(i, 7).Value = «Not Find»
Case 1
.Cells(i, 7).Value = arr1(buffRaw, 6)

Case Else
.Cells(i, 7).Value = «doubles»

End Select
Next i
End With

Case Else
.Cells(i, 7).Value = «doubles»

End Select
Next i
End With

вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com

Case Else
.Cells(i, 7).Value = «doubles»

End Select
Next i
End With

Bregra1

Дата: Воскресенье, 14.02.2016, 15:04 | Сообщение № 9

Udik

Дата: Воскресенье, 14.02.2016, 15:09 | Сообщение № 10

вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com

Ответить

Bregra1

Дата: Воскресенье, 14.02.2016, 15:17 | Сообщение № 11

Rioran

Дата: Воскресенье, 14.02.2016, 15:32 | Сообщение № 12

Bregra1, зачастую структура исходных данных значительно влияет на способ решения. В моём варианте был расчёт, что все условия идут подряд друг за другом. Если же условия могут находится в разнесённых друг от друга столбцах, то можно либо их сгруппировать в одном месте для применения моего кода, либо ввести дополнительный массив-указатель, определяющий номера столбцов с условиями.

Если в коде что-то не понятно — готов пояснить.

Поддерживаю товарища Udik’а, форумчанам будет гораздо удобнее отвечать на Ваши вопросы, если они будут чётко сформулированы, максимально близко к реальной задаче.

Если в коде что-то не понятно — готов пояснить.

Поддерживаю товарища Udik’а, форумчанам будет гораздо удобнее отвечать на Ваши вопросы, если они будут чётко сформулированы, максимально близко к реальной задаче. Rioran

Роман, Москва, voronov_rv@mail.ru
Яндекс-Деньги: 41001312674279

Ответить

Сообщение Bregra1, зачастую структура исходных данных значительно влияет на способ решения. В моём варианте был расчёт, что все условия идут подряд друг за другом. Если же условия могут находится в разнесённых друг от друга столбцах, то можно либо их сгруппировать в одном месте для применения моего кода, либо ввести дополнительный массив-указатель, определяющий номера столбцов с условиями.

Если в коде что-то не понятно — готов пояснить.

Поддерживаю товарища Udik’а, форумчанам будет гораздо удобнее отвечать на Ваши вопросы, если они будут чётко сформулированы, максимально близко к реальной задаче. Автор — Rioran
Дата добавления — 14.02.2016 в 15:32

Как выбрать в Экселе все значения по нескольким условиям

Здравствуйте, друзья. Известные нам функции поиска данных, такие, как ВПР и ПОИСКПОЗ, ищут в массиве первое совпадение и возвращают его. Во многих случаях это и требуется, но что делать, когда следует получить все результаты, а не только первый, и искать их по нескольким критериям, а не по одному?

В этой статье мы решим такую задачу. Описываю пример. Есть ведомость школьных оценок 1-3 классов. Мы хотим выбрать класс, оценку, и увидеть учеников класса, которые получили этот балл:

Задача интересная, и достаточно простая. Однако, нам понадобится сделать дополнительный столбец. Действуем в несколько этапов.

Создание вспомогательной колонки

Сейчас необходимо добавить справа от ведомости еще один столбец. Назовём его «технический», т.к. данные в нем будут неинформативными, вспомогательными.

В этой колонке пронумеруем строки так, чтобы напротив каждой строчки, удовлетворяющей условию поиска, стоял её порядковый номер в таблице-выборке.

Смотрите на рисунке, чего мы хотим добиться. Ищем всех учеников класса, которые получили 5. Первый из списка, кто получил такую оценку – Соколов. Напротив него нужно поставить единичку. Второй в списке – Козлов, даём ему второй номер по порядку, третий – Волков, ему ставим третий номер и т.д. Под этими номерами они потом попадут в табличку с выборкой.

Формулу укажу на примере ячейки D3:

Функция СУММ для каждой строки сложит значение из предыдущей ячейки, а так же, результат сравнения класса в ведомости с искомым, оценки в ведомости с искомыми. Два уточнения:

В ячейке D2 – текст, и функция СУММ его проигнорирует. В остальных строчках будет браться число из ячейки сверху;
ФОРМУЛА И(A3=$L$2;C3=$L$3) вернёт ИСТИНУ (единицу), когда оба условия внутри неё выполнятся, или ЛОЖЬ (ноль), если хотя бы одно из условий не выполнится. Таким образом, когда функция И найдет совпадение класса и оценки, СУММ увеличит порядковый номер на единицу, что нам и требовалось

Смотрите на картинке результат. Все найденные совпадения я выделил красным цветом с помощью условного форматирования.

Вывод отобранных строк в таблицу

Теперь осталось вывести на экран выборку учеников класса с нужной оценкой. Для этого сопоставим номера в столбце F с номерами в технической колонке D. Удобнее всего это сделать с помощью комбинации функций ИНДЕКС и ПОИСКПОЗ:

Алгоритм работы на примере ячейки G3:

Функция ПОИСКПОЗ получает числовой индекс строки из ячейки F3 и ищет такое же значение в техническом столбце D. Возвращает либо порядковый номер найденной ячейки в массиве, либо ошибку #Н/Д
Функция ИНДЕКС получает из ведомости класс, находящийся в ячейке, порядковый номер которой мы получили в первом пункте
Далее функция ЕСЛИОШИБКА выводит на экран класс, если он найден, или пустую строку, если ПОИСКПОЗ вернула ошибку

Такой порядок работает и для остальных столбцов: «Фамилия», «Баллы». Теперь если изменить искомый класс или оценку, формулы сами всё пересчитают и переформируют список. Можно скрыть дополнительный столбец, чтобы не мешал.

Для удобного выбора оценки и класса, я создал раскрывающийся список. В этой статье вы можете прочитать, как это делается.

Мы получили полностью автоматизированный интерактивный проект, способный формировать списки «налету», сразу после того, как вы сделаете свой выбор. На этом всё, и я готов отвечать на ваши вопросы!

голоса

Рейтинг статьи

Оценка статьи:

Загрузка...