Progress-servis55.ru

Новости из мира ПК
11 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Выбор массива по условию 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. Значения в столбцах на первом листе могут повторяться, но все пять значений одновременно они уникальны. Спасибо за любой совет.

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

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

Читать еще:  Столбцы в excel стали цифрами
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.]упс, тоже просмотрел, что ВБА, но тогда исправляйте пример

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

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

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

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

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

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

[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

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

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

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

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’а, форумчанам будет гораздо удобнее отвечать на Ваши вопросы, если они будут чётко сформулированы, максимально близко к реальной задаче.

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

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

Поддерживаю товарища 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:

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

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

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

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

Ссылка на основную публикацию
Adblock
detector