Как найти цену слова по таблицам

Привет, друзья. Как часто вам приходится для какого-то значения искать соответствие в таблице Эксель? Например, нужно в справочнике найти адрес человека, или в прайсе – цену товара. Если такие задачи встречаются – этот пост именно для вас!

Я выполняю подобные процедуры каждый день и без описанных ниже функций мне действительно пришлось бы туго. Берите на заметку и применяйте их в работе!

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

Синтаксис функции ВПР такой: =ВПР(Искомое_значение; таблица_для_поиска; номер_выводимого_столбца; [тип_сопоставления]). Рассмотрим аргументы:

• Искомое значение – значение, которое будем искать. Это обязательный аргумент;
• Таблица для поиска – тот массив ячеек, в котором будет поиск. Столбец с искомыми значениями должен быть первым в этом массиве. Это тоже обязательный аргумент;
• Номер выводимого столбца – порядковый номер столбца (начиная с первого в массиве), из которого функция выведет данные при совпадении искомых значений. Обязательный аргумент;
• Тип сопоставления – выберите «1» (или «ИСТИНА») для нестрогого совпадения, «0» («ЛОЖЬ») – для полного совпадения. Аргумент необязателен, если его упустить – будет выполнен поиск нестрогого совпадения.

1. Классический поиск «MS Office».

2. Условное форматирование (выделение нужных ячеек цветом)

3. Настройка фильтров по одному или нескольким значениям.

4. Фрагмент макроса для перебора ячеек в диапазоне и поиска нужного значения.

Поиск нескольких слов

Не зная, как найти слово в таблице в Еxcel, следует также воспользоваться функцией раздела «Редактирование» – «Найти и выделить». Далее нужно отталкиваться от искомой фразы:

• если фраза точная, введите ее и нажмите клавишу «Найти все»;
• если фраза разбита другими ключами, нужно при написании ее в строке поиска дополнительно проставить между всеми словами «*».

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

1) Классический поиск (обыкновенный).

Вызвать панель (меню) поиска можно сочетанием горячих клавиш ctrl+F. (Легко запомнить: F- Found).

Окно поиска состоит из поля, в которое вводится искомый фрагмент текста или искомое число, вкладки с дополнительными настройками («Параметры») и кнопки «Найти».

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

Условное форматирование для искомых ячеек.

Поиск неточного совпадения с помощью ВПР

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

В массиве В5:С12 указаны процентные ставки по кредитам в зависимости от суммы займа. В ячейке В2 Указываем сумму кредита и хотим получить в С2 ставку для такой сделки. Задача сложна тем, что сумма может быть любой и вряд ли будет совпадать с указанными в массиве, поиск по точному совпадению не подходит:

Тогда запишем формулу нестрогого поиска: =ВПР(B2;B5:C12;2;ИСТИНА). Теперь из всех представленных в столбце В данных программа будет искать ближайшее меньшее. То есть, для суммы 8 000 будет отобрано значение 5000 и выведен соответствующий процент.

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

Функция ГПР имеет такой же синтаксис, как и ВПР, но ищет результат не в столбцах, а в строках. То есть, просматривает таблицы не сверху вниз, а слева направо и выводит заданный номер строки, а не столбца.

Функция ПРОСМОТР работает аналогично ВПР, но имеет другой синтаксис. Я использую её, когда таблица данных содержит несколько десятков столбцов и для использования ВПР нужно дополнительно просчитывать номер выводимой колонки. В таких случаях функция ПРОСМОТР облегчает задачу. И так, синтаксис: =ПРОСМОТР(Искомое_значение; Массив_для_поиска; Массив_для_отображения):

• Искомое значение – данные или ссылка на данные, которые нужно искать;
• Массив для поиска – одна строка или столбец, в котором ищем аналогичное значение. Данный массив обязательно сортируем по возрастанию;
• Массив для отображения – диапазон, содержащий данные для выведения результатов. Естественно, он должен одного размера с массивом для поиска.

При такой записи вы даёте не относительную ссылку массива результатов. А прямо на него указываете, т.е. не нужно предварительно просчитывать номер выводимого столбца. Используем функцию ПРОСМОТР в первом примере для функции ВПР (основные средства, инвентарные номера): =ПРОСМОТР(B2;B5:B10;D5:D10). Задача успешно решена!

Еще один способ поиска данных – комбинирование функций ПОИСКПОЗ и ИНДЕКС.

Первая из них, служит для поиска значения в массиве и получения его порядкового номера: ПОИСКПОЗ(Искомое_значение; Просматриваемый_массив; [Тип сопоставления]). Аргументы функции:

• Искомое значение – обязательный аргумент
• Просматриваемый массив – одна строка или столбец, в котором ищем совпадение. Обязательный аргумент
• Тип сопоставления – укажите «0» для поиска точного совпадения, «1» — ближайшее меньшее, «-1» — ближайшее большее. Поскольку функция проводит поиск с начала списка в конец, при поиске ближайшего меньшего – отсортируйте столбец поиска по убыванию. А при поиске большего – сортируйте его по возрастанию.

Позиция необходимого значения найдена, теперь можно вывести его на экран с помощью функции ИНДЕКС(Массив; Номер_строки; [Номер_столбца]):

• Массив – аргумент указывает из какого массива ячеек нужно выбрать значение
• Номер строки – указываете порядковый номер строки (начиная с первой ячейки массива), которую нужно вывести. Здесь можно записать значение вручную, либо использовать результат вычисления другой функции. Например, ПОИСКПОЗ.
• Номер столбца – необязательный аргумент, указывается, если массив состоит из нескольких столбцов. Если аргумент упущен, формула использует первый столбец таблицы.

Теперь скомбинируем эти функции, чтобы получить результат:

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

Чувствуете, как растут и крепчают Ваши знания и умения? Тогда не останавливайтесь, продолжайте читать ! В следующем посте мы будем рассматривать расчеты кредита в Эксель: будет сложно и интересно!

3) Третий способ поиска слов в таблице «Excel» — это использование фильтров.

Фильтр устанавливается во вкладке «Данные» или сочетанием клавиш ctrl+shift+L.

Кликнув по треугольнику фильтра можно в контекстном меню выбрать пункт «Текстовые фильтры», далее «содержит…» и указать искомое слово.

После нажатия кнопки «Ок» на Экране останутся только ячейки столбца, содержащие искомое слово.

Пример

В этом простейшем примере извлекаем первое слово из ячейки с помощью комбинации — функция ЛЕВСИМВ + функция ПОИСК. Поскольку пробел — регистронезависимый символ, для этого случая можно использовать и функцию НАЙТИ.

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

1. ПОИСК возвращает позицию пробела между именем и фамилией.
2. Длина имени вычисляется как позиция пробела минус 1.
3. Функция ЛЕВСИМВ извлекает имя на основе его длины.

4) Способ поиска номер четыре — это макрос VBA для поиска (перебора значений).

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

Sub Poisk()

‘ ruexcel.ru макрос проверки значений (поиска)

Dim keyword As String

keyword = «Искомое слово» ‘присвоить переменной искомое слово

On Error Resume Next ‘при ошибке пропустить

For Each cell In Selection ‘для всх ячеек в выделении (выделенном диапазоне)

If cell.Value = «» Then GoTo Line1 ‘если ячейка пустая перейти на «Line1″

If InStr(StrConv(cell.Value, vbLowerCase), keyword) > 0 Then cell.Interior.Color = vbRed ‘если в ячейке содержится слово окрасить ее в красный цвет (поиск)

Line1:

Next cell

End Sub

Другие примеры использования

Найти первую цифру в ячейке:

=МИН(ЕСЛИОШИБКА(ПОИСК({0:9:8:7:6:5:4:3:2:1},A1),1000))

Найти первую цифру в ячейке и вернуть все, что перед ней:

=ЛЕВСИМВ(A1,МИН(ЕСЛИОШИБКА(ПОИСК({0:9:8:7:6:5:4:3:2:1},A1),1000))-1)

Узнать, содержит ли ячейка латиницу. Формула вернет «ИСТИНА» или «ЛОЖЬ»:

=СЧЁТ(ПОИСК({«a»:»b»:»c»:»d»:»e»:»f»:»g»:»h»:»i»:»j»:»k»:»l»:»m»:»n»:»o»:»p»:»q»:»r»:»s»:»t»:»u»:»v»:»w»:»x»:»y»:»z»};A1))>0

Найти кириллицу в тексте аналогичным путем:

=СЧЁТ(ПОИСК({«а»:»б»:»в»:»г»:»д»:»е»:»ё»:»ж»:»з»:»и»:»й»:»к»:»л»:»м»:»н»:»о»:»п»:»р»:»с»:»т»:»у»:»ф»:»х»:»ц»:»ч»:»ш»:»щ»:»ъ»:»ы»:»ь»:»э»:»ю»:»я»};A1))>0

Фильтр

Вот как искать в Экселе, используя фильтр:

1. Выделите какую-нибудь заполненную ячейку.
2. Нажмите Главная — Сортировка — Фильтр.
3. В верхней строке у клеток появятся стрелочки. Это выпадающее меню. Откройте его.
4. В текстовом поле введите запрос и нажмите «OK».
5. В столбце будут отображаться только ячейки, содержащие искомую фразу.
6. Чтобы сбросить результаты, в выпадающем списке о.
7. Чтобы отключить фильтр, повторно нажмите на него в сортировке.

Этот способ не подойдёт, если вы не знаете, в каком ряду нужное вам значение.

Чтобы найти в Excel какую-то фразу или число используйте встроенные возможности интерфейса. Можно выбрать дополнительные параметры поиска и включить фильтр.

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

Теперь научимся получать по значению координаты не целого листа, а текущей таблицы. Одним словом, нам нужно найти по значению 5277 вместо D9 получить заголовки:

• для столбца таблицы – Март;
• для строки – Товар4.

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

1. Для заголовка столбца. В ячейку D2 введите формулу: На этот раз после ввода формулы для подтверждения жмем как по традиции просто Enter:
2. Для строки вводим похожую, но все же немного другую формулу:

В результате получены внутренние координаты таблицы по значению – Март; Товар 4:

На первый взгляд все работает хорошо, но что, если таблица будет содержат 2 одинаковых значения? Тогда могут возникнуть проблемы с ошибками! Рекомендуем также посмотреть альтернативное решение для поиска столбцов и строк по значению.

Поиск позиции в массивах с Числами

1. Произведем поиск позиции в НЕ сортированном списке числовых значений (диапазон B8:B14 )

Столбец Позиция приведен для наглядности и не влияет на вычисления.

Найдем позицию значения 30 с помощью формулы =ПОИСКПОЗ(30;B8:B14;0)

Формула ищет точное значение 30. Если в списке его нет, то будет возвращена ошибка #Н/Д.

2. Произведем поиск позиции в отсортированном по возрастанию списке числовых значений (диапазон B31:B37 )

Сортированные списки позволяют искать не только точные значения (их позицию), но и позицию ближайшего значения. Например, в списке на картинке ниже нет значения 45, но можно найти позицию наибольшего значения, которое меньше либо равно, чем искомое значение, т.е. позицию значения 40.

Это можно сделать с помощью формулы =ПОИСКПОЗ(45;B31:B37;1)

Обратите внимание, что тип сопоставления =1 (третий аргумент функции).

3. Поиск позиции в списке отсортированном по убыванию выполняется аналогично, но с типом сопоставления = -1. В этом случае функция ПОИСКПОЗ() находит наименьшее значение, которое больше либо равно чем искомое значение.

Во второй части нашего учебника по функции ВПР (VLOOKUP) в Excel мы разберём несколько примеров, которые помогут Вам направить всю мощь ВПР на решение наиболее амбициозных задач Excel.

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

Что ж, давайте приступим.

Поиск в Excel по нескольким критериям

Функция ВПР в Excel – это действительно мощный инструмент для выполнения поиска определённого значения в базе данных. Однако, есть существенное ограничение – её синтаксис позволяет искать только одно значение. Как же быть, если требуется выполнить поиск по нескольким условиям? Решение Вы найдёте далее.

Пример 1: Поиск по 2-м разным критериям

Предположим, у нас есть список заказов и мы хотим найти Количество товара (Qty.), основываясь на двух критериях – Имя клиента (Customer) и Название продукта (Product). Дело усложняется тем, что каждый из покупателей заказывал несколько видов товаров, как это видно из таблицы ниже:

• Обычная функция ВПР не будет работать по такому сценарию, поскольку она возвратит первое найденное значение, соответствующее заданному искомому значению. Например, если Вы хотите узнать количество товара Sweets, заказанное покупателем Jeremy Hill, запишите вот такую формулу:
• =VLOOKUP(B1,$A$5:$C$14,3,FALSE)
  =ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
• – эта формула вернет результат 15, соответствующий товару Apples, так как это первое совпадающее значение.

Есть простой обходной путь – создать дополнительный столбец, в котором объединить все нужные критерии.

В нашем примере это столбцы Имя клиента (Customer) и Название продукта (Product).

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

Итак, Вы добавляете вспомогательный столбец в таблицу и копируете по всем его ячейкам формулу вида: =B2&C2. Если хочется, чтобы строка была более читаемой, можно разделить объединенные значения пробелом: =B2&” “&C2. После этого можно использовать следующую формулу:

1. =VLOOKUP(«Jeremy Hill Sweets»,$A$7:$D$18,4,FALSE)
   =ВПР(«Jeremy Hill Sweets»;$A$7:$D$18;4;ЛОЖЬ)
2. или
3. =VLOOKUP(B1,$A$7:$D$18,4,FALSE)
   =ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)

Где ячейка B1 содержит объединенное значение аргумента lookup_value (искомое_значение), а 4 – аргумент col_index_num (номер_столбца), т.е. номер столбца, содержащего данные, которые необходимо извлечь.

Пример 2: ВПР по двум критериям с просматриваемой таблицей на другом листе

Если Вам необходимо обновить основную таблицу (Main table), добавив данные из второй таблицы (Lookup table), которая находится на другом листе или в другой рабочей книге Excel, то Вы можете собрать искомое значение непосредственно в формуле, которую вставляете в основную таблицу.

Как и в предыдущем примере, Вам понадобится в таблице поиска (Lookup table) вспомогательный столбец с объединенными значениями. Этот столбец должен быть крайним левым в заданном для поиска диапазоне.

Итак, формула с ВПР может быть такой:

=VLOOKUP(B2&» «&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&» «&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)

Здесь в столбцах B и C содержатся имена клиентов и названия продуктов соответственно, а ссылка Orders!$A&$2:$D$2 определяет таблицу для поиска на другом листе.

Чтобы сделать формулу более читаемой, Вы можете задать имя для просматриваемого диапазона, и тогда формула станет выглядеть гораздо проще:

=VLOOKUP(B2&» «&C2,Orders,4,FALSE)
=ВПР(B2&» «&C2;Orders;4;ЛОЖЬ)

Чтобы формула работала, значения в крайнем левом столбце просматриваемой таблицы должны быть объединены точно так же, как и в критерии поиска. На рисунке выше мы объединили значения и поставили между ними пробел, точно так же необходимо сделать в первом аргументе функции (B2&” “&C2).

Запомните! Функция ВПР ограничена 255 символами, она не может искать значение, состоящее из более чем 255 символов. Имейте это ввиду и следите, чтобы длина искомого значения не превышала этот лимит.

Соглашусь, добавление вспомогательного столбца – не самое изящное и не всегда приемлемое решение. Вы можете сделать то же самое без вспомогательного столбца, но в таком случае потребуется гораздо более сложная формула с комбинацией функций INDEX (ИНДЕКС) и MATCH (ПОИСКПОЗ).

Извлекаем 2-е, 3-е и т.д. значения, используя ВПР

Вы уже знаете, что ВПР может возвратить только одно совпадающее значение, точнее – первое найденное. Но как быть, если в просматриваемом массиве это значение повторяется несколько раз, и Вы хотите извлечь 2-е или 3-е из них? А что если все значения? Задачка кажется замысловатой, но решение существует!

Предположим, в одном столбце таблицы записаны имена клиентов (Customer Name), а в другом – товары (Product), которые они купили. Попробуем найти 2-й, 3-й и 4-й товары, купленные заданным клиентом.

Простейший способ – добавить вспомогательный столбец перед столбцом Customer Name и заполнить его именами клиентов с номером повторения каждого имени, например, John Doe1, John Doe2 и т.д. Фокус с нумерацией сделаем при помощи функции COUNTIF (СЧЁТЕСЛИ), учитывая, что имена клиентов находятся в столбце B:

=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)

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

• Находим 2-й товар, заказанный покупателем Dan Brown:
  =VLOOKUP(«Dan Brown2»,$A$2:$C$16,3,FALSE)
  =ВПР(«Dan Brown2»;$A$2:$C$16;3;ЛОЖЬ)
• Находим 3-й товар, заказанный покупателем Dan Brown:
  =VLOOKUP(«Dan Brown3»,$A$2:$C$16,3,FALSE)
  =ВПР(«Dan Brown3»;$A$2:$C$16;3;ЛОЖЬ)

На самом деле, Вы можете ввести ссылку на ячейку в качестве искомого значения вместо текста, как представлено на следующем рисунке:

• Если Вы ищите только 2-е повторение, то можете сделать это без вспомогательного столбца, создав более сложную формулу:
• =IFERROR(VLOOKUP($F$2,INDIRECT(«$B$»&(MATCH($F$2,Table4[Customer Name],0)+2)&»:$C16″),2,FALSE),»»)
  =ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ(«$B$»&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&»:$C16″);2;ИСТИНА);»»)
• В этой формуле:

• $F$2 – ячейка, содержащая имя покупателя (она неизменна, обратите внимание – ссылка абсолютная);
• $B$ – столбец Customer Name;
• Table4 – Ваша таблица (на этом месте также может быть обычный диапазон);
• $C16 – конечная ячейка Вашей таблицы или диапазона.

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

Если Вам нужен список всех совпадений – функция ВПР тут не помощник, поскольку она возвращает только одно значение за раз – и точка. Но в Excel есть функция INDEX (ИНДЕКС), которая с легкостью справится с этой задачей. Как будет выглядеть такая формула, Вы узнаете в следующем примере.

Извлекаем все повторения искомого значения

1. Как упоминалось выше, ВПР не может извлечь все повторяющиеся значения из просматриваемого диапазона.

   Чтобы сделать это, Вам потребуется чуть более сложная формула, составленная из нескольких функций Excel, таких как INDEX (ИНДЕКС), SMALL (НАИМЕНЬШИЙ) и ROW (СТРОКА)

2. Например, формула, представленная ниже, находит все повторения значения из ячейки F2 в диапазоне B2:B16 и возвращает результат из тех же строк в столбце C.
3. {=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,»»),ROW()-3)),»»)}
   {=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;»»);СТРОКА()-3));»»)}

Введите эту формулу массива в несколько смежных ячеек, например, в ячейки F4:F8, как показано на рисунке ниже. Количество ячеек должно быть равным или большим, чем максимально возможное число повторений искомого значения. Не забудьте нажать Ctrl+Shift+Enter, чтобы правильно ввести формулу массива.

Если Вам интересно понять, как она работает, давайте немного погрузимся в детали формулы:

Часть 1:

IF($F$2=B2:B16,ROW(C2:C16)-1,»»)
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;»»)

$F$2=B2:B16 – сравниваем значение в ячейке F2 с каждым из значений диапазона B2:B16. Если найдено совпадение, то выражение СТРОКА(C2:C16)-1 возвращает номер соответствующей строки (значение -1 позволяет не включать строку заголовков). Если совпадений нет, функция IF (ЕСЛИ) возвращает пустую строку.

Результатом функции IF (ЕСЛИ) окажется вот такой горизонтальный массив: {1,»»,3,»»,5,»»,»»,»»,»»,»»,»»,12,»»,»»,»»}

Часть 2:

ROW()-3
СТРОКА()-3

Здесь функция ROW (СТРОКА) действует как дополнительный счётчик. Так как формула скопирована в ячейки F4:F9, мы вычитаем число 3 из результата функции, чтобы получить значение 1 в ячейке F4 (строка 4, вычитаем 3), чтобы получить 2 в ячейке F5 (строка 5, вычитаем 3) и так далее.

Часть 3:

SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,»»),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;»»);СТРОКА()-3))

Функция SMALL (НАИМЕНЬШИЙ) возвращает n-ое наименьшее значение в массиве данных. В нашем случае, какую по счёту позицию (от наименьшего) возвращать – определено функцией ROW (СТРОКА) (смотри Часть 2).

Так, для ячейки F4 функция НАИМЕНЬШИЙ({массив};1) возвращает 1-й (наименьший) элемент массива, то есть 1.

Для ячейки F5 возвращает 2-й наименьший элемент массива, то есть 3, и так далее.

Часть 4:

INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,»»),ROW()-3))
ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;»»);СТРОКА()-3))

Функция INDEX (ИНДЕКС) просто возвращает значение определённой ячейки в массиве C2:C16. Для ячейки F4 функция ИНДЕКС($C$2:$C$16;1) возвратит Apples, для F5 функция ИНДЕКС($C$2:$C$16;3) возвратит Sweets и так далее.

Часть 5:

IFERROR()
ЕСЛИОШИБКА()

В завершение, мы помещаем формулу внутрь функции IFERROR (ЕСЛИОШИБКА), поскольку вряд ли Вас обрадует сообщение об ошибке #N/A (#Н/Д) в случае, если количество ячеек, в которые скопирована формула, будет меньше, чем количество повторяющихся значений в просматриваемом диапазоне.

Двумерный поиск по известным строке и столбцу

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

Итак, давайте обратимся к нашей таблице и запишем формулу с функцией ВПР, которая найдет информацию о стоимости проданных в марте лимонов.

Существует несколько способов выполнить двумерный поиск. Познакомьтесь с возможными вариантами и выберите наиболее подходящий.

Функции ВПР и ПОИСКПОЗ

Вы можете использовать связку из функций ВПР (VLOOKUP) и ПОИСКПОЗ (MATCH), чтобы найти значение на пересечении полей Название продукта (строка) и Месяц (столбец) рассматриваемого массива:

=VLOOKUP(«Lemons»,$A$2:$I$9,MATCH(«Mar»,$A$1:$I$1,0),FALSE)
=ВПР(«Lemons»;$A$2:$I$9;ПОИСКПОЗ(«Mar»;$A$1:$I$1;0);ЛОЖЬ)

Формула выше – это обычная функция ВПР, которая ищет точное совпадение значения “Lemons” в ячейках от A2 до A9. Но так как Вы не знаете, в каком именно столбце находятся продажи за март, то не сможете задать номер столбца для третьего аргумента функции ВПР. Вместо этого используется функция ПОИСКПОЗ, чтобы определить этот столбец.

MATCH(«Mar»,$A$1:$I$1,0)
ПОИСКПОЗ(«Mar»;$A$1:$I$1;0)

В переводе на человеческий язык, данная формула означает:

• Ищем символы “Mar” – аргумент lookup_value (искомое_значение);
• Ищем в ячейках от A1 до I1 – аргумент lookup_array (просматриваемый_массив);
• Возвращаем точное совпадение – аргумент match_type (тип_сопоставления).

Использовав в третьем аргументе, Вы говорите функции ПОИСКПОЗ искать первое значение, в точности совпадающее с искомым значением. Это равносильно значению FALSE (ЛОЖЬ) для четвёртого аргумента ВПР.

Вот так Вы можете создать формулу для поиска по двум критериям в Excel, что также известно, как двумерный поиск или поиск в двух направлениях.

Функция СУММПРОИЗВ

Функция СУММПРОИЗВ (SUMPRODUCT) возвращает сумму произведений выбранных массивов:

=SUMPRODUCT(($A$2:$A$9=»Lemons»)*($A$1:$I$1=»Mar»),$A$2:$I$9)
=СУММПРОИЗВ(($A$2:$A$9=»Lemons»)*($A$1:$I$1=»Mar»);$A$2:$I$9)

Функции ИНДЕКС и ПОИСКПОЗ

В следующей статье я буду объяснять эти функции во всех деталях, так что сейчас можете просто скопировать эту формулу:

=INDEX($A$2:$I$9,MATCH(«Lemons»,$A$2:$A$9,0),MATCH(«Mar»,$A$1:$I$1,0))
=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ(«Lemons»;$A$2:$A$9;0);ПОИСКПОЗ(«Mar»;$A$1:$I$1;0))

Именованные диапазоны и оператор пересечения

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

1. Выделите таблицу, откройте вкладку Formulas (Формулы) и нажмите Create from Selection (Создать из выделенного).
2. Отметьте галочками Top row (в строке выше) и Left column (в столбце слева). Microsoft Excel назначит имена диапазонам из значений в верхней строке и левом столбце Вашей таблицы. Теперь Вы можете осуществлять поиск, используя эти имена, напрямую, без создания формул.
3. В любой пустой ячейке запишите =имя_строки имя_столбца, например, так:

   =Lemons Mar
   … или наоборот:

   =Mar Lemons

Источник: https://office-guru.ru/excel/prodvinutye-primery-s-funkciei-vpr-167.html

Выборка данных в Microsoft Excel

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

Выполнение выборки

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

Способ 1: применение расширенного автофильтра

Наиболее простым способом произвести отбор является применение расширенного автофильтра. Рассмотрим, как это сделать на конкретном примере.

1. Выделяем область на листе, среди данных которой нужно произвести выборку. Во вкладке «Главная» щелкаем по кнопке «Сортировка и фильтр». Она размещается в блоке настроек «Редактирование». В открывшемся после этого списка выполняем щелчок по кнопке «Фильтр».
   

   Есть возможность поступить и по-другому. Для этого после выделения области на листе перемещаемся во вкладку «Данные». Щелкаем по кнопке «Фильтр», которая размещена на ленте в группе «Сортировка и фильтр».



2. После этого действия в шапке таблицы появляются пиктограммы для запуска фильтрования в виде перевернутых острием вниз небольших треугольников на правом краю ячеек. Кликаем по данному значку в заглавии того столбца, по которому желаем произвести выборку. В запустившемся меню переходим по пункту «Текстовые фильтры». Далее выбираем позицию «Настраиваемый фильтр…».



3. Активируется окно пользовательской фильтрации. В нем можно задать ограничение, по которому будет производиться отбор. В выпадающем списке для столбца содержащего ячейки числового формата, который мы используем для примера, можно выбрать одно из пяти видов условий:
   • равно;
   • не равно;
   • больше;
   • больше или равно;
   • меньше.

   Давайте в качестве примера зададим условие так, чтобы отобрать только значения, по которым сумма выручки превышает 10000 рублей. Устанавливаем переключатель в позицию «Больше». В правое поле вписываем значение «10000». Чтобы произвести выполнение действия, щелкаем по кнопке «OK».



4. Как видим, после фильтрации остались только строчки, в которых сумма выручки превышает 10000 рублей.



5. Но в этом же столбце мы можем добавить и второе условие. Для этого опять возвращаемся в окно пользовательской фильтрации. Как видим, в его нижней части есть ещё один переключатель условия и соответствующее ему поле для ввода. Давайте установим теперь верхнюю границу отбора в 15000 рублей. Для этого выставляем переключатель в позицию «Меньше», а в поле справа вписываем значение «15000».

   Кроме того, существует ещё переключатель условий. У него два положения «И» и «ИЛИ». По умолчанию он установлен в первом положении. Это означает, что в выборке останутся только строчки, которые удовлетворяют обоим ограничениям.

   Если он будет выставлен в положение «ИЛИ», то тогда останутся значения, которые подходят под любое из двух условий. В нашем случае нужно выставить переключатель в положение «И», то есть, оставить данную настройку по умолчанию.

   После того, как все значения введены, щелкаем по кнопке «OK».



6. Теперь в таблице остались только строчки, в которых сумма выручки не меньше 10000 рублей, но не превышает 15000 рублей.



7. Аналогично можно настраивать фильтры и в других столбцах. При этом имеется возможность сохранять также фильтрацию и по предыдущим условиям, которые были заданы в колонках. Итак, посмотрим, как производится отбор с помощью фильтра для ячеек в формате даты. Кликаем по значку фильтрации в соответствующем столбце. Последовательно кликаем по пунктам списка «Фильтр по дате» и «Настраиваемый фильтр».

Источник: https://lumpics.ru/how-to-make-a-sample-in-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:

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

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

Скачать пример выборки из таблицы в Excel.

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

Источник: https://exceltable.com/formuly/vyborka-iz-tablicy-po-usloviyu

Поиск и подстановка по нескольким условиям

89123 01.05.2015 Скачать пример

Постановка задачи

Если вы продвинутый пользователь Microsoft Excel, то должны быть знакомы с функцией поиска и подстановки ВПР или VLOOKUP (если еще нет, то сначала почитайте эту статью, чтобы им стать).

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

А если у нас их несколько?

Предположим, что у нас есть база данных по ценам товаров за разные месяцы:

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

Способ 1. Дополнительный столбец с ключом поиска

Это самый очевидный и простой (хотя и не самый удобный) способ. Поскольку штатная функция ВПР (VLOOKUP) умеет искать только по одному столбцу, а не по нескольким, то нам нужно из нескольких сделать один!

Добавим рядом с нашей таблицей еще один столбец, где склеим название товара и месяц в единое целое с помощью оператора сцепки (&), чтобы получить уникальный столбец-ключ для поиска:

Теперь можно использовать знакомую функцию ВПР (VLOOKUP) для поиска склеенной пары НектаринЯнварь из ячеек H3 и J3 в созданном ключевом столбце:

Плюсы: Простой способ, знакомая функция, работает с любыми данными.

Минусы: Надо делать дополнительный столбец и потом, возможно, еще и прятать его от пользователя. При изменении числа строк в таблице — допротягивать формулу сцепки на новые строки (хотя это можно упростить применением умной таблицы).

Способ 2. Функция СУММЕСЛИМН

Если нужно найти именно число (в нашем случае цена как раз число), то вместо ВПР можно использовать функцию СУММЕСЛИМН (SUMIFS), появившуюся начиная с Excel 2007.

По идее, эта функция выбирает и суммирует числовые значения по нескольким (до 127!) условиям.

Но если в нашем списке нет повторяющихся товаров внутри одного месяца, то она просто выведет значение цены для заданного товара и месяца:

Плюсы: Не нужен дополнительный столбец, решение легко масштабируется на большее количество условий (до 127), быстро считает.

Минусы: Работает только с числовыми данными на выходе, не применима для поиска текста, не работает в старых версиях Excel (2003 и ранее).

Способ 3. Формула массива

О том, как спользовать связку функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) в качестве более мощной альтернативы ВПР я уже подробно описывал (с видео). В нашем же случае, можно применить их для поиска по нескольким столбцам в виде формулы массива. Для этого:

1. Выделите пустую зеленую ячейку, где должен быть результат.
2. Введите в строке формул в нее следующую формулу:
3. Нажмите в конце не Enter, а сочетание Ctrl+Shift+Enter, чтобы ввести формулу не как обычную, а как формулу массива.

Как это на самом деле работает:

Функция ИНДЕКС выдает из диапазона цен C2:C161 содержимое N-ой ячейки по порядку. При этом порядковый номер нужной ячейки нам находит функция ПОИСКПОЗ.

Она ищет связку названия товара и месяца (НектаринЯнварь) по очереди во всех ячейках склеенного из двух столбцов диапазона A2:A161&B2:B161 и выдает порядковый номер ячейки, где нашла точное совпадение.

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

Плюсы: Не нужен отдельный столбец, работает и с числами и с текстом.

Минусы: Ощутимо тормозит на больших таблицах (как и все формулы массива, впрочем), особенно если указывать диапазоны «с запасом» или сразу целые столбцы (т.е. вместо A2:A161 вводить A:A и т.д.) Многим непривычны формулы массива в принципе (тогда вам сюда).

Ссылки по теме

Источник: https://www.planetaexcel.ru/techniques/2/224/

Запрос на выборку данных (формулы) в MS EXCEL

Суть запроса на выборку – выбрать из исходной таблицы строки, удовлетворяющие определенным критериям (подобно применению стандартного Фильтра). Произведем отбор значений из исходной таблицы с помощью формул массива. В отличие от применения Фильтра (CTRL+SHIFT+L или ) отобранные строки будут помещены в отдельную таблицу.

В этой статье рассмотрим наиболее часто встречающиеся запросы, например: отбор строк таблицы, у которых значение из числового столбца попадает в заданный диапазон (интервал); отбор строк, у которых дата принаждежит определенному периоду; задачи с 2-мя текстовыми критериями и другие. Начнем с простых запросов.

1. Один числовой критерий (Выбрать те Товары, у которых цена выше минимальной)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист Один критерий — число).

Необходимо отобразить в отдельной таблице только те записи (строки) из Исходной таблицы, у которых цена выше 25.

Решить эту и последующие задачи можно легко с помощью стандартного фильтра. Для этого выделите заголовки Исходной таблицы и нажмите CTRL+SHIFT+L. Через выпадающий список у заголовка Цены выберите Числовые фильтры…, затем задайте необходимые условия фильтрации и нажмите ОК.

Будут отображены записи удовлетворяющие условиям отбора.

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

Критерий (минимальную цену) разместим в ячейке Е6, таблицу для отфильтрованных данных — в диапазоне D10:E19. 

Теперь выделим диапазон D11:D19 (столбец Товар) и в Строке формул введем формулу массива:

=ИНДЕКС(A11:A19;НАИМЕНЬШИЙ(ЕСЛИ($E$6=$B$13:$B$21 гарантирует, что будут отобраны даты не позже заданной (включая). Условие $B$13:$B$21>0 необходимо, если в диапазоне дат имеются пустые ячейки. Знак * (умножение) используется для задания Условия И (все 3 критерия должны выполняться для строки одновременно).

Примечание. Случай, когда список несортирован, рассмотрен в статье Поиск ДАТЫ (ЧИСЛА) ближайшей к заданной, с условием в MS EXCEL. Несортированный список.

7. Один Текстовый критерий (Выбрать Товары определенного вида)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист Один критерий — Текст).

Задача решается аналогично Задачам 1 и 3. Более подробное решение см. в статье Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть1. Обычный поиск.

8. Два Текстовых критерия (Выбрать Товары определенного вида, поставленные в заданный месяц)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист 2 критерия — текст (И)).

• Для отбора строк используется формула массива:
• =ИНДЕКС($A$11:$A$19;НАИМЕНЬШИЙ(ЕСЛИ(($F$6=$A$11:$A$19)*($F$7=$B$11:$B$19);СТРОКА($A$11:$A$19)-СТРОКА($A$10);30);СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК($A$11:$A$19)))))
• Выражение ($F$6=$A$11:$A$19)*($F$7=$B$11:$B$19) задает оба условия (Товар и Месяц).

Выражение СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК($A$11:$A$19))) формирует массив последовательных чисел {1:2:3:4:5:6:7:8:9}, т.е. номера строк в таблице.

9. Два Текстовых критерия (Выбрать Товары определенных видов)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист 2 критерия — текст (ИЛИ)).

1. В отличие от Задачи 7 отберем строки с товарами 2-х видов (Условие ИЛИ).
2. Для отбора строк используется формула массива:
3. =ИНДЕКС(A$11:A$19;НАИБОЛЬШИЙ((($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19))*(СТРОКА($A$11:$A$19)-СТРОКА($A$10)); СЧЁТЕСЛИ($A$11:$A$19;$E$6)+СЧЁТЕСЛИ($A$11:$A$19;$E$7)-ЧСТРОК($A$11:A11)+1))

Условие ($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19) гарантирует, что будут отобраны товары только заданных видов из желтых ячеек (Товар2 и Товар3). Знак + (сложение) используется для задания Условие ИЛИ (должен быть выполнен хотя бы 1 критерий).

Вышеуказанное выражение вернет массив {0:0:0:0:1:1:1:0:0}. Умножив его на выражение СТРОКА($A$11:$A$19)-СТРОКА($A$10), т.е. на массив последовательных чисел {1:2:3:4:5:6:7:8:9}, получим массив позиций (номеров строк таблицы), удовлетворяющих критериям. В нашем случае это будет массив {0:0:0:0:5:6:7:0:0}.

С помощью функции НАИБОЛЬШИЙ() выведем 3 значения из позиции 5 (строка 15 листа), 6 (16) и 7 (17), т.е. значения Товар2, Товар2 и Товар3.

Для этого используем выражение СЧЁТЕСЛИ($A$11:$A$19;$E$6)+СЧЁТЕСЛИ($A$11:$A$19;$E$7)-ЧСТРОК($A$11:A11)+1, которое последовательно (начиная со строки 11) будет возвращать числа 3; 2; 1; 0; -1; -2; … Формула НАИБОЛЬШИЙ(…;3) вернет число 5, НАИБОЛЬШИЙ(…

;2) вернет число 6, НАИБОЛЬШИЙ(…;1) вернет число 7, а НАИБОЛЬШИЙ(…;0) и далее вернет ошибку, которую мы скроем условным форматированием.

И наконец, с помощью функции ИНДЕКС() последовательно выведем наши значения из соответствующих позиций: =ИНДЕКС(A$11:A$19;5) вернет Товар2, =ИНДЕКС(A$11:A$19;6) вернет Товар2, =ИНДЕКС(A$11:A$19;7) вернет Товар3.

10. Отбор значений с учетом повторов

В разделе Отбор на основании повторяемости собраны статьи о запросах с группировкой данных. Из повторяющихся данных сначала отбираются уникальные значения, а соответствующие им значения в других столбцах — группируются (складываются, усредняются и пр.).

• Наиболее популярные статьи из этого раздела:
• Отбор уникальных значений (убираем повторы из списка) в MS EXCEL
• Отбор уникальных значений с суммированием по соседнему столбцу в MS EXCEL
• Отбор повторяющихся значений в MS EXCEL
• Отбор уникальных значений из двух диапазонов в MS EXCEL
• Отбор уникальных СТРОК с помощью Расширенного фильтра в MS EXCEL
• В качестве примера приведем решения следующей задачи: Выбрать Товары, цена которых лежит в определенном диапазоне и повторяется заданное количество раз или более.
• В качестве исходной возьмем таблицу партий товаров.

Предположим, что нас интересует сколько и каких партий товаров поставлялось по цене от 1000р. до 2000р. (критерий 1). Причем, партий с одинаковой ценой должно быть минимум 3 (критерий 2).

1. Решением является формула массива:
2. =НАИМЕНЬШИЙ(СТРОКА($A$14:$A$27)*($C$14:$C$27>=$B$7)*($C$14:$C$27=$B$10);F14+($G$8-$G$9))
3. Эта формула возвращает номера строк, которые удовлетворяют обоим критериям.
4. Формула =СУММПРОИЗВ(($C$14:$C$27>=$B$7)*($C$14:$C$27=$B$10)) подсчитывает количество строк, которые удовлетворяют критериям.

В файле примера на листе «10.Критерий — колич-во повторов» настроено Условное форматирование, которое позволяет визуально определить строки удовлетворяющие критериям, а также скрыть ячейки, в которых формула массива возвращает ошибку #ЧИСЛО!

11. Используем значение критерия (Любой) или (Все)

В фильтре Сводных таблиц MS EXCEL используется значение (Все), чтобы вывести все значения столбца. Другими словами, в выпадающем списке значений критерия содержится особое значение, которое отменяет сам критерий (см. статью Отчеты в MS EXCEL, Отчет №3).

В файле примера на листе «11. Критерий Любой или (Все)» реализован данный вариант критерия.

Формула в этом случае должна содержать функцию ЕСЛИ(). Если выбрано значение (Все), то используется формула для вывода значений без учета данного критерия. Если выбрано любое другое значение, то критерий работает обычным образом.

• =ЕСЛИ($C$8=»(Все)»;НАИМЕНЬШИЙ((СТРОКА($B$13:$B$26)-СТРОКА($B$12))*($D$13:$D$26>=$D$8);F13+($G$6-$G$7));
• НАИМЕНЬШИЙ((СТРОКА($B$13:$B$26)-СТРОКА($B$12))*($D$13:$D$26>=$D$8)*($C$13:$C$26=$C$8);F13+($G$6-$G$7)))
• Остальная часть формулы аналогична рассмотренным выше.

Источник: https://excel2.ru/articles/zapros-na-vyborku-dannyh-formuly-v-ms-excel

Excel выборка данных из таблицы

​Смотрите также​ новый формат. Чтобы​ выпадающего списка. Нам​Задача №2 – выбрать​ строка таблицы будет​ из таблицы (например,​ своеобразный отчет, который​.​ условным форматированием. Установим​.

​ в позицию​​ положение​​ результатов, которые удовлетворяют​ со строки 11)​ этого События: ЕСЛИ($D$15:$D$21;СТРОКА(A$15:A$21);»»)​: После ввода формулы вместо​ которая возвращает несколько​

​Суть запроса на выборку​ формат присваивался для​ нужны все Фамилии​ из исходной таблицы​ на 5 меньше​ текстовых), из которых​ можно форматировать в​Активируется окно настройки сортировки.​ первым ограничением нижнюю​Аналогичным образом в столбец​«Равно»​«ИЛИ»​ заданным условиям, с​ будет возвращать числа​

1. Один числовой критерий (Выбрать те Товары, у которых цена выше минимальной)

​- Функция НАИМЕНЬШИЙ() сортирует​ клавиши ENTER (ВВОД)​ значений, то можно​ – выбрать из​ целой строки, а​ клиентов из столбца​

​ товары, которые поступили​ чем соответственная строка​ функция ИНДЕКС выберет​ стиль отличный от​ Обязательно устанавливаем галочку​ границу отбора по​

​ с выручкой вписываем​. В поле справа​, то тогда останутся​ последующим выводом их​ 3; 2; 1;​ полученный массив номеров​​ нужно нажать сочетание​​ использовать другой подход,​ исходной таблицы строки,​ не только ячейке​ A, без повторений.​ в продажу 20.09.2015.​ листа.​

​ одно результирующие значение.​ исходной таблицы. В​

​ напротив параметра​ выручке в 15000​ формулу следующего содержания:​ от него вписываем​ значения, которые подходят​ на листе отдельным​ 0; -1; -2;​ строк: первыми идут​ клавиш CTRL+SHIFT+ENTER. Это​ который рассмотрен в​ удовлетворяющие определенным критериям​ в столбце A,​

​Перед тем как выбрать​ То есть критерий​​После того как будут​​ Аргумент «диапазон» означает​ этот отчет можно​«Мои данные содержат заголовки»​​ рублей, а вторым​​=ИНДЕКС(C2:C29;НАИМЕНЬШИЙ(ЕСЛИ(15000​

​ слово​​ под любое из​​ списком или в​ … Формула НАИБОЛЬШИЙ(…;3) вернет​ номера строк Событий,​

• ​ сочетание клавиш используется​​ разделах ниже: 5.а,​
• ​ (подобно применению стандартного​​ мы используем смешанную​​ уникальные значения в​​ отбора – дата.​​ отобраны все минимальные​ область ячеек с​
• ​ вынести не все​, если шапка имеется,​​ условием верхнюю границу​​Опять набираем сочетание клавиш​«Картофель»​ двух условий. В​

​ исходном диапазоне.​​ число 5, НАИБОЛЬШИЙ(…;2) вернет​

​ которые удовлетворяют критерию;​ для ввода формул​ 7, 10 и​ Фильтра). Произведем отбор значений​ ссылку в формуле​ Excel, подготовим данные​​ Для удобства искомую​​ значения и сопоставлены​

​ числовыми значениями, из​ столбцы, а только​ а галочки нет.​​ в 20000 рублей.​​Ctrl+Shift+Enter​. Переключатель нижнего блока​ нашем случае нужно​

​Наиболее простым способом произвести​ число 6, НАИБОЛЬШИЙ(…;1) вернет​- Функция ИНДЕКС() выводит​ массива.​ 11. В этих​ из исходной таблицы​

​ =$A4.​​ для выпадающего списка:​ дату введем в​ все номера строк​ которых следует выбрать​ нужные (хотя после​ В поле​Вписываем в отдельном столбце​

​.​ так же ставим​ выставить переключатель в​ отбор является применение​ число 7, а НАИБОЛЬШИЙ(…;0)​ названия Событий из​Скопируйте формулу массива вниз​ случаях используются формулы​ с помощью формул​KDE123​Выделите первый столбец таблицы​ отдельную ячейку, I2.​

2. Два числовых критерия (Выбрать те Товары, у которых цена попадает в диапазон)

​ таблицы функция МИН​ первое наименьшее число.​ применения фильтра ненужные​«Сортировать по»​ граничные условия для​

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

​ указанный строк.​ на нужное количество​ массива, возвращающие одно​ массива. В отличие​: Помогите, пожалуйста!​ A1:A19.​

​Для решения задачи используется​ выберет наименьший номер​ В аргументе «заголовок_столбца»​ столбцы можно скрыть).​указываем наименование того​ выборки.​ меняется только первое​«Равно»​«И»​ как это сделать​ ошибку, которую мы​Пусть имеется Исходная таблица​ ячеек. Формула вернет​

​ значение. ​​ от применения Фильтра​​Необходимо из таблицы​Выберите инструмент: «ДАННЫЕ»-«Сортировка и​ аналогичная формула массива.​

1. ​ строки. Эта же​​ для второй функции​​Основной недостаток – сложность​
2. ​ столбца, в котором​

​Как и в предыдущем​​ значение координат, а​​. В поле напротив​​, то есть, оставить​​ на конкретном примере.​

• ​ скроем условным форматированием.​ с перечнем Товаров​​ только те значения​​Пусть имеется Исходная таблица​ (​
• ​ на листе «Разработка»​​ фильтр»-«Дополнительно».​​ Только вместо критерия​
• ​ строка будет содержать​
• ​ СТРОКА, следует указать​ реализации трехуровневого Связанного​ содержатся скопированные значения​ способе, поочередно выделяем​ в остальном формулы​ него делаем запись​​ данную настройку по​​Выделяем область на листе,​

​И наконец, с помощью​ и Датами поставки​ Товаров, которые были​​ с перечнем Товаров​​CTRL+SHIFT+L​ выбрать строки для​В появившемся окне «Расширенный​ }.​ первое наименьшее число,​ ссылку на ячейку​

​ списка. Но, единожды​ случайных чисел. В​ пустые столбцы новой​ полностью идентичны.​ –​ умолчанию. После того,​ среди данных которой​ функции ИНДЕКС() последовательно​

​ (см. файл примера,​ поставлены в диапазоне​ и Ценами (см.​или Данные/ Сортировка​ которых состояние «Вып»​ фильтр» включите «скопировать​Подобные формулы вводятся и​ которое встречается в​

​ с заголовком столбца,​ его создав и​ поле​ таблицы и вписываем​

3. Один критерий Дата (Выбрать те Товары, у которых Дата поставки совпадает заданной)

1. ​Как видим, таблица заполнена​«Мясо»​ как все значения​ нужно произвести выборку.​ выведем наши значения​ лист 2 критерия​
2. ​ указанных дат. В​ файл примера, лист​ и фильтр/ Фильтр)​
3. ​ и дата разработки​
4. ​ результат в другое​

4. Два критерия Дата (Выбрать те Товары, у которых Дата поставки попадает в диапазон)

• ​ в другие столбцы​ столбце B6:B18. На​ который содержит диапазон​ поняв принцип работы,​«Сортировка»​
• ​ в них соответствующие​ данными, но внешний​

​. И вот далее​​ введены, щелкаем по​ Во вкладке​ из соответствующих позиций:​ — Дата-Текст).​

​ остальных ячейках будут​​ Диапазон Чисел).​​ отобранные строки будут​

​ находится в пределах​​ место», а в​

​ (принцип см. выше).​

​ основании этого номера​​ числовых значений.​ этот недостаток в​оставляем настройки по​ три формулы. В​ вид её не​ мы выполняем то,​ кнопке​

​«Главная»​ =ИНДЕКС(A$11:A$19;5) вернет Товар2, =ИНДЕКС(A$11:A$19;6) вернет Товар2, =ИНДЕКС(A$11:A$19;7) вернет Товар3.

​В отличие от Задачи​ содержаться ошибки #ЧИСЛО!​Критерии (нижнюю и верхнюю​ помещены в отдельную​ границы «Начало отсчета​ поле «Поместить результат​Теперь используем текстовый критерий.

​ строки функции ИНДЕКС​Естественно эту формулу следует​ достаточной мере компенсируется.​ умолчанию. В поле​

1. ​ первый столбец вносим​ совсем привлекателен, к​ чего ранее не​
2. ​«OK»​​щелкаем по кнопке​​В разделе Отбор на​ 5 будем отбирать​ Ошибки в файле примера​
3. ​ границы цены) разместим​
4. ​ таблицу.​ — Конец отсчета»​ в диапазон:» укажите​
5. ​ Вместо даты в​​ выберет соответствующее значение​ выполнять в массиве.​Алгоритм создания запроса на​«Порядок»​ следующее выражение:​
6. ​ тому же, значения​
7. ​ делали: устанавливаем переключатель​

​.​«Сортировка и фильтр»​​ основании повторяемости собраны​​ строки только того​ (Лист 4.Диапазон Дат) скрыты​ в диапазоне​В этой статье рассмотрим​

​ (Столбец L). Выбранные​​ $F$1.​ ячейку I2 введем​ из таблицы A6:A18.​ Поэтому для подтверждения​

• ​ выборку следующий:​можно выбрать параметр​=ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(($D$2=C2:C29);СТРОКА(C2:C29);»»);СТРОКА(C2:C29)-СТРОКА($C$1))-СТРОКА($C$1))​ даты заполнены в​ совместимости условий в​Теперь в таблице остались​
• ​. Она размещается в​ статьи о запросах​ Товара, который указан​ с помощью Условного​

5. Один критерий Дата (Выбрать те Товары, у которых Дата поставки не раньше/ не позже заданной)

1. ​Е5:Е6​ наиболее часто встречающиеся​ данные разместить на​Отметьте галочкой пункт «Только​ текст «Товар 1».​ В итоге формула​ ее ввода следует​
2. ​ШАГ 1​ как​В последующие колонки вписываем​ ней некорректно. Нужно​
3. ​ позицию​

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

​ запросы, например: отбор​ новом листе.​ уникальные записи» и​ Немного изменим формулу​ возвращает это значение​ нажимать не просто​Сначала создадим Лист​«По возрастанию»​ точно такие же​ исправить эти недостатки.​«ИЛИ»​

5а. Один критерий Дата (Выбрать События, которые Закончились/ не начались/ происходят на заданную дату)

​ которых сумма выручки​«Редактирование»​ Из повторяющихся данных​

​ дат должен быть​Аналогичную формулу нужно ввести​Т.е. если Цена Товара​ строк таблицы, у​Хотелось бы все​ нажмите ОК.​ массива: {}.​ в ячейку B3​

• ​ клавишу Enter, а​Списки​, так и​ формулы, только изменив​ Некорректность даты связана​. Теперь строчка, содержащая​ не меньше 10000​
• ​. В открывшемся после​
• ​ сначала отбираются уникальные​ отсортирован (для каждого​ и для дат​ попадает в указанный​ которых значение из​ это сделать без​В результате мы получили​Такая большая функция выборки​ в качестве результата​ целую комбинацию клавиш​, в котором будут​«По убыванию»​
• ​ координаты сразу после​ с тем, что​ любое из указанных​
• ​ рублей, но не​
• ​ этого списка выполняем​
• ​ значения, а соответствующие​ из товаров), по​ в столбец E.​ интервал, то такая​

​ числового столбца попадает​ макросов.​ список данных с​ в Excel.​ вычисления.​

​ CTRL+SHIFT+Enter. Если все​ содержаться перечень дирекций​. Для случайной выборки​

6. Два критерия: Дата и Текст (Выбрать Товары определенного вида, у которых Дата поставки не позже заданной)

1. ​ наименования оператора​ формат ячеек соответствующего​ условий, будет выводиться​ превышает 15000 рублей.

   ​ щелчок по кнопке​ им значения в​

2. ​ товару сортировка не​В ячейке​ запись появится в​ в заданный диапазон​Алексей К​ уникальными значениями (фамилии​​Поняв принцип действия формулы,​ сделано правильно в​ и названия отделов​
3. ​ это значения не​ИНДЕКС​ столбца общий, а​ на экран. Щелкаем​
4. ​Аналогично можно настраивать фильтры​​«Фильтр»​
5. ​ других столбцах -​

​ требуется.​J12​ новой таблице Отфильтрованные​ (интервал); отбор строк,​: С использованием расширенного​ без повторений).​Сначала возьмем два числовых​ теперь можно легко​ строке формул появятся​ (см. файл примера).​ имеет. После того,​на соответствующие нужным​ нам нужно установить​ по кнопке​ и в других​

​.​​ группируются (складываются, усредняются​Для отбора строк, дата​вычислено количество строк​ данные.​ у которых дата​ фильтра во вложении.​​

7. Один Текстовый критерий (Выбрать Товары определенного вида)

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

​ формат даты. Выделяем​«OK»​ столбцах. При этом​Есть возможность поступить и​ и пр.).​ которых не позже​ исходной таблицы, удовлетворяющих​В отличие от предыдущей​ принаждежит определенному периоду;​

8. Два Текстовых критерия (Выбрать Товары определенного вида, поставленные в заданный месяц)

• ​KDE123​Теперь нам необходимо немного​Задача – отобрать товары,​ настраивать под другие​Обратите внимание ниже на​А​
• ​ жмем на кнопку​ аналогии с предыдущим​
• ​ весь столбец, включая​​.​
• ​ имеется возможность сохранять​ по-другому. Для этого​Наиболее популярные статьи из​
• ​ (включая саму дату),​ критериям:​ задачи создадим два​ задачи с 2-мя​

9. Два Текстовых критерия (Выбрать Товары определенных видов)

​: Здорово! Спасибо!​ модифицировать нашу исходную​ которые стоят меньше​ условия. Например, формулу​ рисунок, где в​) будет извлекаться формулой​

1. ​«OK»​ способом.​ ячейки с ошибками,​Как видим, в новой​
2. ​ также фильтрацию и​ после выделения области​
3. ​ этого раздела:​​ используется формула массива:​

​=СЧЁТЕСЛИМН(B12:B20;»>=»&$E$6;B12:B20;»​ Динамических диапазона: Товары​ текстовыми критериями и​Поясни как работает.​ таблицу. Выделите первые​ 400 и больше​ можно изменить так,​ ячейку B3 была​ массива из исходной​.​

​Каждый раз после ввода​ и кликаем по​ выборке существуют ограничения​ по предыдущим условиям,​ на листе перемещаемся​Отбор уникальных значений (убираем​=ИНДЕКС(A13:A21;​Строки исходной таблицы, которые​ и Цены (без​

​ другие. Начнем с​ Как обновить результаты​ 2 строки и​ 200 рублей.

Объединим​ чтобы выбрать первое​ введена данная формула​ таблицы с перечнем​После этого все значения​ не забываем набирать​ выделению правой кнопкой​ по дате (с​ которые были заданы​ во вкладку​ повторы из списка)​НАИМЕНЬШИЙ(ЕСЛИ(($E$7=$A$13:$A$21)*($E$8>=$B$13:$B$21)*($B$13:$B$21>0);СТРОКА($B$13:$B$21);»»);СТРОКА($B$13:$B$21)-СТРОКА($B$12))​ удовлетворяют критериям, выделены также​ них можно обойтись,​ простых запросов.​ отбора, изменив критерии​ выберите инструмент: «ГЛАВНАЯ»-«Ячейки»-«Вставить»​ условия знаком «*».​

​ максимальное значение в​ в массиве:​ сотрудников:​ таблицы выстраиваются в​ сочетание клавиш​

10. Отбор значений с учетом повторов

Источник: https://my-excel.ru/tablicy/excel-vyborka-dannyh-iz-tablicy.html

Как упростить выборку данных из нескольких однотипных отчетов в Excel

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

Допустим, нужно уточнить, сколько заплатили определенному контрагенту в одном из кварталов прошедшего года.

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

В новом файле будет три вида листов. Первый предназначен для оглавления. Второй – рабочий, именно здесь будут задаваться параметры поиска и выводиться искомое значение. Для примера, результат выборки сумм оплат за IV квартал одному из трех контрагентов – компании «Гурман» приведен на рисунке 1.

Что касается третьей группы листов, здесь размещается вся исходная информация в однотипных таблицах (одинаковая структура, порядок размещения данных, единые единицы измерения). В нашем случае – те самые отчеты об оплатах поставщикам (по одному листу-таблице для каждого контрагента).

Количество таких листов должно соответствовать количеству однотипных таблиц, участвующих в выборке.

Рисунок 1. Рабочий лист Скачать

Создаем лист «Оглавление книги». Здесь нужно будет перечислить имена листов с исходными данными. Имена вносятся вручную начиная с ячейки А3.

• Например, для трех контрагентов это может выглядеть как на рисунке 2.
• Рисунок 2. Оглавление

Далее каждой исходной таблице необходимо присвоить имя, соответствующее названию листа. Для этого ее нужно выделить, нажать правую кнопку мыши, выбрать из появившегося контекстного меню пункт «Имя диапазона», ввести название и нажать «ОК». Например, это может выглядеть как на рисунке 3. Лист исходных данных.

1. Рисунок 3. Лист исходных данных
3. Кстати, имя таблице можно назначить, воспользовавшись вкладкой меню «Формулы» – в группе «Определенные имена» выбрать «Диспетчер имен» (аналогично комбинации клавиш Ctrl + F3), далее – «Создать», ввести название и нажать «ОК».

По той же схеме назначается имя «Поставщик» диапазону на листе «Оглавление книги» с перечислением названий исходных листов. Например, на рисунке 4 это диапазон «А3:А5», рассчитанный на трех контрагентов.

Рисунок 4. Название пунктов оглавления

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

Пользователю будет удобнее, если он сможет указать наименование нужного контрагента в заданном списке. Для создания такого списка выбираем в меню вкладку «Данные», группу «Работа с данными», далее – «Проверка данных», тип данных «Список», в поле «Источник» вводим ранее заданное имя «Поставщик» (например, на рисунке 5.

Создание перечня поставщиков показано, как этот список создается в ячейке В1).

Рисунок 5. Создание перечня поставщиков

Для параметра «Период» также создается раскрывающийся список, но возможные значения вводятся вручную через точку с запятой в соответствующей ячейке: I;II;III;IV. Например, на рисунке 6. Создание списка периодов отражено, как такой список готовится в ячейке В3.

Рисунок 6. Создание списка периодов

Наконец задается формула, которая будет искать нужные данные по заданным параметрам. Она вводится в ячейку, в которой они должны отражаться. Например, на рисунке 7. Ввод формулы, связующей условия выборки данных это ячейка В5, а формула выглядит так: «=ВПР($B$3;ДВССЫЛ($B$1);2;ЛОЖЬ)», где:

Рисунок 7. Ввод формулы, связующей условия выборки данных

Сергей Изотов, руководитель направления финансовой и нематериальной мотивации персонала ГК «Блок»

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

Для этого нужно открыть редактор Visual Basic Editor (комбинация клавиш «Alt+F11» или кликнуть правой кнопкой мыши на ярлык любого листа и выбрать «Исходный текст» или же в группе «Код» вкладки меню «Разработчик» щелкнуть по пункту «Visual Basic») и вставить в проект стандартный модуль. А в этот модуль добавить два кода программы (см. рисунок 8. Код программы) – ListSheet и NameAdded.

Рисунок 8. Код программы

С помощью макроса ListSheet на листе «Оглавление книги» создается список, состоящий из гиперссылок на листы с исходными данными (см. перечень контрагентов в оглавлении книги). Текст макроса выглядит так:

Поскольку оглавление по умолчанию формируется из всех листов файла (по порядку, слева направо), а нужно первые два по порядку – «Оглавление книги» и «Рабочий лист» – в нем пропустить, предпоследней строкой программы удаляются данные из диапазона ячеек A1:A2, куда попадут названия этих служебных листов («Sheets(«Оглавление книги»).Range(«A1:A2»).ClearContents»).

Макрос NameAdded присваивает таблицам с исходными значениями имена, соответствующие названиям листов, на которых они расположены. Внимание! Последние не должны содержать пробелов.

Это условие позволяет исключить распространение действия макроса на листы «Оглавление книги» и «Рабочий лист».

Кстати, при попытке присвоить имя макрос проверяет, есть ли в названии листа пробел: если да – это признается ошибкой, которая удаляется (строка 13 Err.Clear).

Сам макрос приведен ниже:

При копировании кода второй программы следует учесть несколько моментов. А именно какие строки можно менять при использовании для нужд другой компании. В частности, четвертая строка (nm.

Delete) удаляет ранее существующие имена исходных таблиц, пятая (Next nm) означает переход к следующему имени. В седьмой строке в переменную «sAlert» записываются названия листов, которые не могут быть использованы в именах, например, первые два листа: «Оглавление книги» и «Рабочий лист».

В 10 строке (Names.Add Name:=wks.Name, RefersTo:=wks.Range) указывается диапазон, которому следует присвоить имя.

В строке 17 (MsgBox) выводятся названия листов, которые не могут быть использованы в именах. В строке 18 (Names.Add Name:=»Поставщик», RefersTo:=Range («’Оглавление книги) указывается диапазон ячеек, из которых формируется список оглавления. Например, это может быть диапазон A3:A5.

Запустить оба кода программы можно нажатием сочетания клавиш «Alt+F8» или в окне «Макрос» выбрать макросы по названию и нажать кнопку «Выполнить».

В результате работы макросов получится готовый к работе файл.

Источник: https://www.fd.ru/recommend/2351-kak-uprostit-vyborku-dannyh-iz-neskolkih-odnotipnyh-otchetov-s-pomoshchyu-excel