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

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

Содержание
  1. Поиск в Excel по нескольким критериям
  2. Пример 1: поиск по 2 различным критериям
  3. Пример 2: ВПР для двух критериев с предварительно просмотренной таблицей на другом листе
  4. Извлекаем 2-е, 3-е и т. д. значения с помощью ВПР
  5. Получение всех повторений желаемого значения
  6. Часть 1:
  7. Часть 2:
  8. Часть 3:
  9. Часть 4:
  10. Часть 5:
  11. 2D поиск по известной строке и столбцу
  12. Функции ВПР и ПОИСК
  13. Функция СУММПРОИЗВ
  14. Функции ИНДЕКС и ПОИСК
  15. Именованные диапазоны и оператор пересечения
  16. Использование нескольких ВПР в одной формуле
  17. Динамическая подстановка данных из разных таблиц с использованием ВПР и КОСВЕННО
  18. Как работают INDIRECT и VLOOKUP
  19. Попрактикуйтесь
  20. Альтернативные решения
  21. A6
  22. Пример Vpr Excel в форме для вашего. Excel: надстройка для обратного ВПР, или как «пойти влево» ВПР
  23. Использование функции COLUMN для указания столбца для извлечение
  24. Создание составного ключа с помощью & «|» &
  25. Основная информация об ИНДЕКСЕ и ПОИСКЕ
  26. ИНДЕКС — синтаксис и использование функций
  27. MATCH — синтаксис и приложение-функция
  28. Как использовать ИНДЕКС и ПОИСК в Excel
  29. Почему INDEX/SEARCH лучше, чем VLOOKUP?
  30. 4 основных преимущества использования ПОИСКА/ИНДЕКСА в Excel:
  31. ИНДЕКС и ПОИСК — примеры формул
  32. Как искать слева с помощью ПОИСКА и ИНДЕКСА
  33. Вычисления с использованием ИНДЕКСА и ПОИСКА в Excel (СРЕДНИЙ, МАКС, МИН)
  34. Что нужно помнить при использовании функции СРЕДНИЙ с ИНДЕКС и ПОИСК
  35. Как использовать ИНДЕКС и ПОИСК для поиска известной строки и столбца
  36. Множественный поиск с ИНДЕКСОМ и ПОИСКОМ
  37. INDEX и ПОИСК в сочетании с ЕСЛИОШИБКА в Excel
  38. Функция VLOOKUP ()
  39. INDEX () function
  40. Функция ПОИСК ()
  41. Что такое ВПР в Excel — область действия
  42. Функция ВПР в Excel — как использовать
  43. Необходимо заполнить значения u200b u200b для функции VLOOKUP
  44. VLOOKUP — инструкция по работе с двумя условиями

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

Функция VLOOKUP в Excel — действительно мощный инструмент для выполнения поиска определенного значения в базе данных. Однако есть существенное ограничение — его синтаксис позволяет искать только одно значение. Что делать, если вам нужно искать несколько условий? Вы найдете решение ниже.

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

Предположим, у нас есть список заказов, и мы хотим найти Количество товаров (Кол-во) На основе двух критериев — Имя клиента (Заказчик) и Название продукта (Товар). Дело осложняется тем, что каждый из покупателей заказал несколько видов товаров, что видно из приведенной ниже таблицы:

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

ВПР (B1, $ A $ 5: $ C $ 14,3, FALSE)
= VLOOKUP (B1, $ A $ 5: $ C $ 14, 3, FALSE)

— эта формула вернет результат 15 , соответствующий продукту Яблоки , поскольку это первое совпадающее значение.

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

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

ВПР («Jeremy Hill Sweets», $ A $ 7: $ D $ 18,4, FALSE)
= VLOOKUP («Jeremy Hill Sweets»; $ A $ 7: $ D $ 18; 4; FALSE )

ВПР (B1, $ A $ 7: $ D $ 18,4, FALSE)
= VLOOKUP (B1; $ A $ 7: $ D $ 18; 4; FALSE )

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Предположим, один столбец таблицы содержит имена клиентов (Имя клиента), а другой — продукты (Продукт), которые они купили. Попробуем найти 2-й, 3-й и 4-й товары, приобретенные данным покупателем..

Самый простой способ — добавить вспомогательный столбец перед столбцом Имя клиента и заполнить его именами клиентов с номером повторения каждого имени, например Джон Доу1 , Джон Доу2 и т. д. Сосредоточьтесь на нумерации с помощью функции СЧЁТЕСЛИ (СЧЁТЕСЛИ), учитывая, что имена клиентов указаны в столбец B:

B2 & COUNTIF ($ B $ 2: B2, B2)
= B2 & COUNTIF ($ B $ 2: B2; B2)

Затем вы можете использовать обычную функцию VLOOKUP найти нужный заказ. Например:

  • Найдите 2-го Дэна Брауна :

    VLOOKUP («Dan Brown2 «, $ A $ 2: $ C $ 16,3, FALSE)
    = VLOOKUP (» Dan Brown2 «; $ A $ 2: $ C $ 16; 3; FALSE)

  • Найдите 3-й продукт, заказанный покупателем Дэн Браун :

    ВПР («Дэн Браун3», $ A $ 2: $ C $ 16,3, FALSE)
    = VLOOKUP («Dan Brown3»; $ A $ 2: $ C $ 16; 3; FALSE)

Если вы ищете только 2-е повторение, вы можете сделать это без вспомогательного столбца, создав более сложную формулу:

ЕСЛИОШИБКА (ВПР ($ F $ 2, КОСВЕННО («$ B $» & (MATCH ($ F $ 2, Table4 , 0) +2) & «: $ C16»), 2, FALSE), «»)
= IFERROR (VLOOKUP ($ F $ 2; INDIRECT («$ B $» & (SEARCH ($ F $ 2; Table4; 0) +2) & «: $ C16»); 2; TRUE); «»)

В этой формуле:

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

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

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

Получение всех повторений желаемого значения

Как упоминалось выше, VLOOKUP не может получить все повторяющиеся значения из просматриваемого диапазона. Для этого вам понадобится немного более сложная формула, состоящая из нескольких функций Excel, таких как ИНДЕКС (ИНДЕКС), МАЛЕНЬКИЙ (МАЛЕНЬКИЙ) и СТРОКА (СТРОКА)

Например, формула ниже находит все повторения значения из ячейки F2 в диапазоне B2: B16 и возвращает результат из тех же строк в столбце C.

(= ЕСЛИОШИБКА (ИНДЕКС ($ C $ 2: $ C $ 16, МАЛЫЙ (ЕСЛИ ($ F $ 2 = B2: B16, ROW (C2: C16) -1, » «), СТРОКА () — 3)),» «))
(= ЕСЛИОШИБКА (ИНДЕКС ($ C $ 2: $ C $ 16; МАЛЫЙ (ЕСЛИ ($ F $ 2 = B2: B16 ; LINE (C2: C16) -1; «»); LINE () — 3)); «»))

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

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

Часть 1:

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

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

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

Часть 2:

ROW () — 3
ROW () — 3

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

Часть 3:

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

Функция МАЛЕНЬКИЙ (МАЛЕНЬКИЙ) возвращает n-е наименьшего значения в массиве данных. В нашем случае, какую позицию (от наименьшей) вернуть в строке, определяет функция ROW (LINE) (см. Часть 2). Итак, для ячейки F4 функция SMALL ((array); 1) возвращает 1st (наименьший) элемент массив, т.е. 1 … Для ячейки F5 возвращает 2-й наименьший элемент массива, то есть 3 и т. д.

Часть 4:

ИНДЕКС ($ C $ 2: $ C $ 16, МАЛЫЙ (IF ($ F $ 2 = B2: B16, ROW (C2: C16) -1, «»), ROW () — 3))
ИНДЕКС ($ C $ 2: $ C $ 16; МАЛЕНЬКИЙ (IF ($ F $ 2 = B2: B16; LINE (C2: C16) -1; «»); LINE () — 3))

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

Часть 5:

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

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

2D поиск по известной строке и столбцу

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

Итак, давайте обратимся к нашей таблице и напишем формулу с функцией VLOOKUP , где можно найти информацию о стоимости лимонов, проданных в марте.

Есть несколько способов выполнить 2D-поиск. Просмотрите варианты и выберите наиболее подходящий.

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

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

VLOOKUP («Лимоны», $ A $ 2: $ I $ 9, MATCH («Мар», $ A $ 1: $ I $ 1.0), FALSE)
= VLOOKUP («Лимоны»; $ A $ 2: $ I $ 9; MATCH («Mar»; $ A $ 1: $ I $ 1; 0); FALSE)

Приведенная выше формула представляет собой обычную функцию ВПР , которая ищет точное совпадение для значения «Лимоны» в ячейках с A2 по A9. Но так как вы не знаете, в каком столбце находятся продажи за март, вы не можете указать номер столбца для третьего аргумента функции VLOOKUP … Вместо этого используется функция ПОИСК , чтобы определить этот столбец.

MATCH («Mar», $ A $ 1: $ I $ 1.0)
SEARCH («Mar»; $ A $ 1: $ I $ 1; 0)

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

  • Поиск символов «Мар» — аргумент lookup_value (lookup_value);
  • Мы ищем в ячейках от A1 до I1 — аргумент lookup_array (lookup_array);
  • Вернуть точное совпадение — аргумент match_type (match_type).

Использование 0 в третьем аргументе вы говорите, что функции SEARCH ищут первое значение, которое точно соответствует желаемому значению. Это то же самое, что и значение FALSE (ЛОЖЬ) для четвертого аргумента VLOOKUP .

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

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

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

СУММПРОИЗВ (($ A $ 2: $ A $ 9 = «Лимоны») * ($ A $ 1: $ I $ 1 = «Мар»), $ A $ 2: $ I $ 9)
= СУММПРОИЗВ (($ A $ 2: $ A $ 9 = «Лимоны») * ($ A $ 1: $ I $ 1 = «Мар»); $ A $ 2: $ I $ 9)

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

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

ИНДЕКС ($ A $ 2: $ I $ 9, ПОИСКПОЗ («Лимоны», $ A $ 2: $ A $ 9,0), ПОИСКПОЗ («Мар», $ A $ 1: $ I $ 1.0))
= ИНДЕКС ($ A $ 2: $ I $ 9; ПОИСК («Лимоны»; $ A $ 2: $ A $ 9; 0); ПОИСК («Мар»; $ A $ 1: $ I $ 1; 0))

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

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

Когда вы вводите имя, Microsoft Excel w Я буду отображать всплывающую подсказку со списком совпадающих имен, как при вводе формулы.

  1. Нажмите Enter и проверьте результат

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

Использование нескольких ВПР в одной формуле

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

Давайте посмотрим на следующий пример. У нас есть основная таблица со столбцом SKU (новый) , куда вы хотите добавить столбец с соответствующими ценами из другой таблицы. Кроме того, у нас есть 2 таблицы поиска. Первая (таблица поиска 1) содержит обновленные номера SKU (новые) и названия продуктов, а вторая (таблица поиска 2) — названия продуктов и старые номера SKU (старые) .

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

  1. Функция записи VLOOKUP , которая находит имя продукта в таблице Таблица поиска 1 , используя Артикул в качестве желаемого значения:

    ВПР (A2, New_SKU, 2, FALSE)
    = VLOOKUP (A2, New_SKU; 2, FALSE)

    Здесь New_SKU — именованный диапазон $ A: $ B в таблице Таблица поиска 1 , и 2 — это столбец B, который содержит названия товаров (см. рисунок выше)

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

    ВПР (ВПР (A2, New_SKU, 2, FALSE), Price, 3, FALSE)
    = VLOOKUP (VLOOKUP (A2; New_SKU; 2; FALSE); Price; 3; FALSE)

    Здесь Price — именованный диапазон $ A: $ C в таблица Таблица поиска 2 , а 3 — это столбец C, содержащий цены.

Рисунок ниже показан результат, возвращаемый созданной нами формулой:

Динамическая подстановка данных из разных таблиц с использованием ВПР и КОСВЕННО

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

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

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

Если у вас всего два таких отчета, вы можете использовать невероятно простую формулу с функциями ВПР и ЕСЛИ A (ЕСЛИ), чтобы выбрать отчет, который вы хотите ищите:

ВПР ($ D $ 2, IF ($ D3 = «FL», FL_Sales, CA_Sales), 2, FALSE)
= VLOOKUP ($ D $ 2; IF ($ D3 = «FL»; FL_Sales; CA_Sales); 2; FALSE)

  • $ D $ 2 Является ли ячейка, содержащая название товара. Обратите внимание, что здесь мы используем абсолютные ссылки, чтобы избежать изменения значения поиска при копировании формулы в другие ячейки.
  • $ D3 Это ячейка с именем область, край. Мы используем абсолютную ссылку на столбец и относительную ссылку на строку, поскольку планируем скопировать формулу в другие ячейки того же столбца.
  • FL_Sal es и CA_Sales — имена таблиц (или именованных диапазонов), содержащих соответствующие отчеты о продажах. Конечно, вы можете использовать обычные имена листов и ссылки на диапазоны ячеек, например «FL Sheet»! $ A $ 3: $ B $ 10 , но именованные диапазоны намного удобнее.

Однако, когда таких много таблицы, функция IF A — не лучшее решение. Вместо этого вы можете использовать функцию INDIRECT (КОСВЕННЫЙ) для возврата желаемого диапазона поиска.

Как вы, вероятно, знаете, функция INDIRECT используется для возврата ссылки в виде текстовой строки, что нам сейчас и нужно. Итак, мы можем смело заменить в приведенной выше формуле выражение с функцией IF A на ссылку с функцией INDIRECT … Вот комбинация ВПР и КОСВЕННО отлично работают в парах:

ВПР ($ D $ 2, КОСВЕННО ($ D3 & «_ Продажи») , 2, FALSE)
= VLOOKUP ($ D $ 2; INDIRECT ($ D3 & «_ Sales»); 2; FALSE)

  • $ D $ 2 — это ячейка с названием товара, оно не изменилось за счет абсолютной ссылки.
  • $ D3 Это ячейка, содержащая первую часть названия региона. В нашем примере это FL .
  • _Sales — общая часть имени всех именованных диапазонов или таблиц. В сочетании со значением в ячейке D3 образует полное имя требуемого диапазона. Ниже приведены некоторые подробности для тех, кто плохо знаком с функцией INDIRECT.

Как работают INDIRECT и VLOOKUP

Во-первых, напомню синтаксис функции КОСВЕННЫЙ (КОСВЕННЫЙ):

  • A1 , если аргумент ИСТИНА (ИСТИНА) или не указано;
  • R1C1 , если F ALSE (FALSE).

Итак, вернемся к нашим отчетам о продажах. Если вы помните, каждый отчет представляет собой отдельную таблицу, расположенную на отдельном листе. Чтобы формула работала правильно, вы должны назвать свои таблицы (или диапазоны), и все имена должны иметь общую часть. Например, вот так: CA_Sales , FL_Sales , TX_Sales и т. Д. Как видите, присутствует «_Sales» во всех именах.

Функция INDIRECT объединяет значение в столбце D и текстовую строку «_Sales», тем самым информируя VLOOKUP в какой таблице искать. Если ячейка D3 содержит «FL», формула будет искать в таблице FL_Sales , если «CA» находится в таблице CA_Sales и т. Д.

Результат функции VLOOKUP и INDIRECT будет следующим:

Если данные находятся в разных книгах Excel, вам необходимо добавить имя книги перед именованным диапазоном, например:

ВПР ($ D $ 2, КОСВЕННО ($ D3 & «Workbook1! _Sales»), 2, FALSE)
= VLOOKUP ($ D $ 2; INDIRECT ($ D3 & «Workbook1! _Sales «); 2; FALSE)

Если функция INDIRECT относится к другой книге, эта книга должна быть открытой. Если он закрыт, функция сообщит об ошибке. # ССЫЛКА! (# ССЫЛКА!).

Как правило, эффективность крупномасштабных сложных операций невозможно охарактеризовать одним показателем W; он должен привлечь другие, дополнительные W 1, W 2, …, W, чтобы помочь ему; некоторые из них нужно делать больше, другие меньше. Например, при оценке деятельности предприятия необходимо учитывать ряд показателей:

  • полный ассортимент продукции,

    себестоимость и т. д.

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

    собственные потери,

    время работы,

    расход боеприпасов и т. д.

Такая множественность показателей эффективности, одни из которых желательно максимизировать, а другие минимизировать, характерна для любой сложной задачи исследования операций. В этом случае правильная формулировка — «достижение максимального эффекта при заданных затратах» или «достижение заданного эффекта при минимальных затратах». В общем случае не существует решения, которое преобразовало бы не более одного индикатора W 1 и одновременно в максимум (или минимум) другой индикатор W 2; Причем для нескольких индикаторов такого решения не существует. Однако количественный анализ эффективности может быть полезен и в случае нескольких индикаторов, поскольку он позволяет заранее отказаться от явно иррациональных вариантов решения, которые уступают лучшим вариантам по всем параметрам.

Давайте посмотрим на пример. Пусть проводится анализ боевой операции Q, оцениваемой по двум показателям:

W — вероятность выполнения боевой задачи;

S — стоимость затраченных средств.

Первый показатель желательно повернуть на максимум, а второй на минимум.

Предположим, предлагается 20 различных решений x 1, x 2,…, x 20. Для каждого из них известны значения обоих показателей W и S (см. рис. 1.1).

На рисунке видно, что от некоторых вариантов решения можно сразу отказаться. Каким вариантам следует отдать предпочтение при оценке эффективности двух показателей. Очевидно, те, которые лежат одновременно справа и на нижней границе области (на рис. 1.1 — пунктирная линия). Итак, есть четыре варианта X 16, X 17, X 19, X 20. Из них X 16 является наиболее эффективным, но относительно дорогим; Х 20 — самый дешевый, но не такой эффективный. Задача лица, принимающего решения, состоит в том, чтобы выяснить, какой ценой мы можем заплатить за определенное повышение эффективности или, наоборот, какой эффективностью мы согласны пожертвовать, чтобы не понести слишком больших материальных потерь.

S x

x

x x x

x x x

x x

x x x

x x

x x

x

x x

В связи с тем, что комплексная оценка работы сразу по нескольким показателям затруднительна, на практике несколько показателей объединяются в один обобщенный показатель. Часто в качестве такого критерия используется дробь; в числителе поставьте те показатели W 1, …, W , которые желательно увеличить, а в знаменателе — те, которые желательно уменьшить:

U =
(4)

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

Составные критерии часто принимаются как «взвешенная сумма» индивидуальных показателей эффективности:

U = α
+ α
+… + Α
(5)

где α — положительные или отрицательные коэффициенты .

Положительные устанавливаются у тех показателей, которые желательно максимизировать; отрицательный — для тех, которые желательно минимизировать. Абсолютные значения коэффициентов соответствуют степени важности показателей. Критерий формы (5) имеет тот же недостаток (возможность взаимной компенсации разнородных показателей) и может привести к неверным рекомендациям. Однако в тех случаях, когда α i выбирается не произвольно, а так, чтобы составной критерий наилучшим образом выполнял свою функцию, с его помощью можно получить малоценные результаты.

В некоторых случаях задача с несколькими индикаторами может быть сведена к задаче с одним индикатором, если выбран один (основной) индикатор эффективности W 1 и стремится повернуть его на максимум, и только некоторые ограничения накладываются на остальные вспомогательные показатели W 2, W 3, …

W
;… W
; W
; … W

Эти ограничения затем будут включены в набор указанных условий a 1, a 2,….

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

Существует еще один возможный способ построения компромиссного решения, который можно назвать « последовательными уступками ». Пусть показатели эффективности расположены в порядке убывания важности: сначала основной W 1, затем другие, вспомогательные: W 2, W 3,…. Для простоты предположим, что каждый из них нужно максимизировать (если это не так, достаточно изменить знак индикатора). Процедура построения компромиссного решения следующая. Сначала ищется решение, которое максимизирует основной показатель эффективности. W 1. Затем, исходя из практических соображений и точности, с которой известны исходные данные (часто малые), некоторая «уступка» ΔW 1, которую мы соглашаемся допустить, чтобы максимизировать второй показатель W 2. Мы накладываем ограничение на индикаторе W 1 так, чтобы он был не меньше W 1 * — ∆W 1 (W 1 * i s максимально возможное значение W 1), и с этим ограничением мы ищем решение, которое превращает W 2 в максимум.

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

Так или иначе, при любом способе формализации задача количественного обоснования решения по нескольким показателям остается не полностью определенной, а окончательный выбор решения определяется волевым актом «командира». Задача исследователя — предоставить «командиру» достаточный объем данных, позволяющий комплексно оценить достоинства и недостатки каждого варианта решения и на их основе сделать окончательный выбор.

Предположим, вы хотите суммировать значения с более чем одним условием, например общий объем продаж товара в конкретном регионе. Это хороший вариант использования функции СУММЕСЛИМН в формуле.

Обратите внимание на этот пример, где у нас есть два условия: требуется объем продаж мясо (из столбца C) в область South (из столбца a).

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

= СУММЕСЛИМН (D2: D11, a2: A11, Юг, C2: C11, мясо)

Результатом является значение 14 719.

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

СУММЕСЛИМН — арифметическая формула. Вычисляет числа, которые в данном случае находятся в столбце D. Первый шаг — указать расположение чисел.

= СУММЕСЛИМН (D2: D11,

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

Затем вам нужно найти данные, которые удовлетворяют двум условиям, и ввести первое условие, указав расположение данных для функции (a2 : A11), а также каково условие — «Юг». Обратите внимание на запятые между отдельными аргументами.

= СУММЕСЛИМН (D2: D11, A2: A11, » Юг »;

Кавычки вокруг слова« Юг »указывают на то, что это текстовые данные.

Наконец, вы вводите аргументы для второго условия — диапазон ячеек (C2: C11), содержащих слово «Мясо», а также само слово (заключенное в кавычки), чтобы Excel мог сопоставить их. Завершите формулу, закрыв закрывающую круглую скобку ) , а затем нажмите Enter. Результат снова равен 14 719.

= СУММЕСЛИ (D2: D11; A2: A11, «Южный»; C2: C11, «Мясо»)

При вводе функции СУММЕСЛИМН в Excel, если вы не помните эти аргументы, справка готова. После ввода формулы = СУММЕСЛИМН ( автозаполнение формулы отображается под формулой, а список аргументов отображается в нужном вам порядке.

Взгляните на изображение формул автозаполнения и списка аргументов в нашем примере диапазон_суммы — это D2: D11, столбец чисел, который нужно добавить. диапазон_критериев1 — a2. A11 — столбец данных, в котором condition1 «Южный».

Когда вы вводите формулу, оставшиеся аргументы будут отображаться в завершении формулы (здесь не показано); диапазон_критериев2 — это диапазон C2: C11, который представляет собой столбец данных, содержащий condition2 — «Мясо»..

Если вы нажмете СУММЕСЛИМН в автозаполнении формул, откроется статья, в которой вы можете узнать больше.

Попрактикуйтесь

Если вы хотите поэкспериментировать с функцией СУММЕСЛИМН, вот несколько примеров данных и формулы, использующей эту функцию.

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

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

Region

Продавец

Продажи

Запад

Восточный

Песоцкий

Северный

Песоцкий

Молоко продукты

Маринова

Oriental

Песоцкий

Северный

Тема: Принятие решений по нескольким критериям индикаторы.

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

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

Для построения комплексной оценки необходимо решить две задачи:

Первая проблема состоит в том, что рассматриваемые критерии критериев имеют неодинаковую значимость;

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

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

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

— первый подход , используемый при отсутствии статистических данных о значении рассматриваемых показателей;

— второй подход используется при наличии статистических данных (пределов изменения) по значениям рассматриваемых показателей.

При использовании первого подхода перевод в баллы происходит следующим образом: лучшее значение рассматриваемого показателя принимается равным 1 баллу, а худшее — в долях этого балла. Такой подход прост, дает объективную оценку, но в то же время не учитывает лучшие достижения, лежащие за пределами рассмотренных вариантов.

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

n = 1 + 3 322 lg N , где

N — количество статистические наблюдения;

n — принятая система начисления баллов, полученная с использованием правил округления.

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

Задача:

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

Выполните оценку, используя 2 подхода:

    при отсутствии статистических данных по значение рассматриваемых показателей;

    при наличии.

Пределы изменения показателей установлены для следующего количества наблюдений (N):

    для четных вариантов N = 8;

Th Оценка значимости должна проводиться на основе парной оценки по мнению исполнителя.

Таблица 1.

Варианты работы

tasks

Нет. альтернатив

задачи

Нет. альтернатив

задачи

Нет. альтернатив

задачи

Нет. альтернатив

задачи

Нет. альтернатив

задачи

Нет. альтернатив

Таблица 2.

Исходные данные:

Альтернативные решения

индикаторы

A6

X 1

X 2

X 3

X 4

X 5

1. В дополнительном столбце, в котором мы будем указывать рейтинг, вставляем функцию RANK (пишем в ячейку = RANK и выбираем из списка функцию, предложенную EXCEL, нажимаем в строке формул fx)


2. В открывшемся окне заполняем аргументы: «Число» — указываем первое значение в нашей таблице в той же строке, где находится формула.


3. «Ссылка» — указать весь массив данных, т.е. диапазон со всеми числами (значениями продаж).


4. Мы фиксируем границы этого диапазона (нажимаем F4 на клавиатуре), чтобы адрес диапазона не «выдвигался» при перетаскивании дальше и нажатии ОК.


5. Распространите формулу до всех ячеек в столбце «рейтинг».


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

Если вам понравился материал или даже пригодился, Вы можете отблагодарить автора, переведя определенную сумму с помощью кнопки ниже:
(для перевода по карте нажмите VISA, а затем «перевести»)



Пример Vpr Excel в форме для вашего. Excel: надстройка для обратного ВПР, или как «пойти влево» ВПР

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

Использование функции COLUMN для указания столбца для извлечение

Если таблица, в которую вы извлекаете данные с помощью ВПР, имеет ту же структуру, что и таблица поиска, но просто содержит меньше строк, то в ВПР вы можете использовать функцию СТОЛБЕЦ () для автоматического вычисления номера столбцов, которые нужно получить. В этом случае все формулы ВПР будут одинаковыми (с поправкой на первый параметр, который изменяется автоматически)! Обратите внимание, что первый параметр имеет абсолютную координату столбца.

Создание составного ключа с помощью & «|» &

Если возникает необходимость поиска по нескольким столбцам одновременно, то необходимо составить составной ключ для поиска. Если бы возвращаемое значение было не текстовым (как в случае с полем «Код»), а числовым, то для этого подходила бы более удобная формула СУММЕСЛИМН, а ключ составного столбца вообще не требовался бы.

Это моя первая статья для Lifehacker. Если вам понравилось, приглашаю вас посетить мой сайт, а также с удовольствием читаю в комментариях о ваших секретах использования функции ВПР и тому подобное. Спасибо. 🙂

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

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

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

Основная информация об ИНДЕКСЕ и ПОИСКЕ

Поскольку цель этого руководства — показать возможности функции INDEX и SEARCH для реализации вертикального поиска в Excel, мы не будем останавливаться на их синтаксисе и применении.

Мы дадим здесь необходимый минимум для понимания сути, а затем подробно разберем примеры формул, показывающих преимущества использования INDEX и SEARCH вместо ВПР .

ИНДЕКС — синтаксис и использование функций

Функция ИНДЕКС (ИНДЕКС) в Excel возвращает значение из массива с заданными номерами строки и столбца. Функция имеет следующий синтаксис:

Каждый аргумент имеет очень простое объяснение:

  • array (массив) — это диапазон ячеек, из которого нужно извлечь значение.
  • row_num (line_number) — номер строки в массиве, из которой нужно извлечь значение. Если не указан, то требуется аргумент. column_num (column_number).
  • column_num (column_number) — номер столбца в массиве, из которого нужно извлечь ценить. Если не указан, то требуется аргумент. row_num (line_number)

Если указаны оба аргумента, функция INDEX возвращает значение из ячейка на пересечении указанной строки и столбца.

Вот простейший пример функции ИНДЕКС (ИНДЕКС):

INDEX (A1: C10,2,3)
= INDEX (A1: C10; 2; 3)

Формула выполняет поиск в диапазоне A1: C10 и возвращает значение ячейки в 2-й строке и 3m столбце, то есть из ячейки C2 .

Очень просто, правда? Однако на практике вы не всегда знаете, какая строка и столбец вам нужны, и поэтому вам нужна помощь функции SEARCH .

MATCH — синтаксис и приложение-функция

Функция MATCH (MATCH) в Excel выполняет поиск указанного значения в диапазоне ячеек и возвращает относительное положение этого значения в диапазоне.

Например, если в диапазоне B1: B3 содержатся значения Нью-Йорк, Париж, Лондон, то следующая формула вернет цифру 3 , потому что «Лондон» является третьим элементом в списке.

MATCH («Лондон», B1: B3,0)
= ПОИСК («Лондон»; B1: B3; 0)

Функция MATCH (MATCH) имеет следующий синтаксис:

MATCH (lookup_value, lookup_array,)
MATCH (lookup_value; lookup_array; [match_type])

  • lookup_value (lookup_value) — это номер или текст, который вы ищете. Аргумент может быть значением, включая логическое значение, или ссылкой на ячейку.
  • lookup_array (lookup_array) — диапазон ячеек, в котором выполняется поиск.
  • match_type (match_type) — этот аргумент сообщает функции ПОИСК , хотите ли вы найти точное или приблизительное совпадение:
    • 1 или не указано — находит максимальное значение, меньшее или равное желаемому. Сканируемый массив должен быть в порядке возрастания, то есть от наименьшего к наибольшему.
    • 0 — находит первое значение, равное искомому. Для комбинации ИНДЕКС / ПОИСК всегда требуется точное совпадение, поэтому третий аргумент функции ПОИСК должен быть равен 0 .
    • -1 — находит наименьшее значение, большее или равное желаемому. Сканируемый массив должен быть в порядке убывания, то есть от наибольшего к наименьшему.

На первый взгляд, преимущества функции ПОИСК вызывает сомнения. Кому нужно знать положение элемента в диапазоне? Мы хотим знать значение этого элемента!

Напомню, что относительное положение желаемого значения (т.е. номер строки и/или столбца) — это именно то, что мы должны указать для аргументов Функция row_num (line_number) и/или column_num (column_number) INDEX (INDEX). Как вы помните, функция ИНДЕКС может возвращать значение на пересечении данной строки и столбца, но не может определить, какая строка и столбец нас интересуют.

Как использовать ИНДЕКС и ПОИСК в Excel

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

Еще не совсем понятно? Введите функции ИНДЕКС и ПОИСК в такой форме:

ИНДЕКС (, (ПОИСКПОЗ (желаемое значение, столбец, в котором мы ищем, 0))
= ИНДЕКС (столбец, из которого нужно извлечь; (ПОИСК (желаемое значение; столбец, в котором ищем; 0))

Я думаю будет еще проще разобраться на примере. Предположим, у вас есть следующий список столиц штатов:

Давайте найдем население одной из столиц, например Японии, по следующей формуле:

ИНДЕКС ($ D $ 2: $ D $ 10, MATCH («Япония», $ B $ 2: $ B $ 10,0))
= ИНДЕКС ($ D $ 2: $ D $ 10; ПОИСК («Япония»; $ B $ 2: $ B $ 10; 0))

Теперь давайте посмотрим, что делает каждый элемент этой формулы:

  • Функция MATCH (MATCH) ищет «Япония» в столбце B , а именно — в ячейках B2: B10 , и возвращает число 3 , потому что » Япония »находится на третьем месте в списке.
  • Функция ИНДЕКС (ИНДЕКС) использует 3 в качестве аргумента row_num (line_number), который указывает, из какой строки нужно вернуть значение. Те. получается простая формула:

    ИНДЕКС ($ D $ 2: $ D $ 10,3)
    = ИНДЕКС ($ D $ 2: $ D $ 10,3)

    Формула говорит примерно так: поиск в ячейках от D2 до D10 и извлечение значения из третьей строки, то есть из ячейки D4 , поскольку счет начинается со второй строки.

Вот результат в Excel:

Важно! Количество строк и столбцов в массиве, которое использует функция ИНДЕКС (ИНДЕКС), должно соответствовать значениям аргументов row_num (line_number) и функция column_num (column_number) MATCH (ПОИСК). В противном случае результат формулы будет неверным.

Стоп, стоп … почему мы не можем просто использовать функцию VLOOKUP (ВПР)? Есть ли смысл тратить время на поиски лабиринтов SEARCH и INDEX ?

VLOOKUP («Япония», $ B $ 2: $ D $ 2.3)
= VLOOKUP («Япония»; $ B $ 2: $ D $ 2; 3)

В этом случае он делает не имеет смысла! Этот пример предназначен только для демонстрационных целей, чтобы вы могли понять, как функции SEARCH и INDEX работают в парах. Следующие примеры продемонстрируют вам истинную мощь этого пакета. INDEX и SEARCH , которые легко справляются со многими трудными ситуациями, когда VLOOKUP не работает.

Почему INDEX/SEARCH лучше, чем VLOOKUP?

Решая, какую формулу использовать для вертикального поиска, большинство гуру Excel считают, что INDEX / SEARCH намного лучше, чем VLOOKUP … Однако многие пользователи Excel по-прежнему используют VLOOKUP , поскольку эта функция намного проще. Это происходит потому, что очень немногие люди полностью понимают преимущества переключения с ВПР на связку INDEX и ПОИСК , а кто-то хочет тратить время на изучение более сложной формулы.

4 основных преимущества использования ПОИСКА/ИНДЕКСА в Excel:

1. Искать справа налево. Любой грамотный пользователь Excel знает, что VLOOKUP не может смотреть влево, а это означает, что желаемое значение обязательно должно находиться в крайнем левом столбце исследуемого диапазона. В случае SEARCH / INDEX столбец поиска может находиться либо слева, либо справа от диапазона поиска. Пример: покажет эту функцию в действии.

2. Безопасное добавление или удаление столбцов. Формулы с функцией VLOOKUP перестают работать или возвращают ошибочные значения, если вы удаляете или добавляете столбец в таблицу поиска. Для функции ВПР любой вставленный или удаленный столбец изменит результат формулы, поскольку синтаксис ВПР требует, чтобы вы указали весь диапазон и конкретный номер столбца. из которого вы хотите извлечь данные.

Например, если у вас есть таблица A1: C10 , и вы хотите извлечь данные из столбца B , то вам нужно установить значение 2 для аргумента col_index_num (column_number) функции VLOOKUP , например:

VLOOKUP («значение поиска», A1: C10,2)
= VLOOKUP («значение поиска»; A1: C10; 2)

Если позже вы вставите новый столбец между столбцами A и B , тогда значение аргумента необходимо будет изменить с 2 на 3 , иначе формула вернет результат из только что вставленного столбца.

Использование ПОИСК / ИНДЕКС . Вы можете удалить или добавить столбцы в исследуемый диапазон, не искажая результат, поскольку столбец, содержащий желаемое значение определяется напрямую. Действительно, это большое преимущество, особенно когда вам приходится работать с большими объемами данных. Вы можете добавлять и удалять столбцы, не беспокоясь об исправлении всех используемых вами функций VLOOKUP .

3. Нет ограничения на размер желаемого значения. Используя VLOOKUP , помните об ограничении длины желаемого значения в 255 символов, иначе вы рискуете получить ошибку #VALUE! (#VALUE!). Итак, если таблица содержит длинные строки, единственное допустимое решение — использовать INDEX / SEARCH .

Предположим, вы используете эта формула с ВПР ищет в ячейках от B5 до D10 значения, указанного в ячейке A2 :

ВПР (A2, B5: D10,3, FALSE)
= VLOOKUP (A2, B5: D10, 3, FALSE)

Формула не будет работать, если значение в ячейке A2 длиннее 255 символов. Вместо этого вам нужно использовать аналогичную формулу INDEX / SEARCH :

INDEX (D5: D10, MATCH (TRUE, ИНДЕКС (B5: B10 = A2,0), 0))
= ИНДЕКС (D5: D10; ПОИСК (ИСТИНА; ИНДЕКС (B5: B10 = A2; 0); 0))

4. Более высокая скорость работы. Если вы работаете с небольшими таблицами, разница в производительности Excel, скорее всего, будет незначительной, особенно в последних версиях.. Если вы работаете с большими таблицами, содержащими тысячи строк и сотни формул поиска, Excel будет работать намного быстрее, если вместо этого будут использоваться SEARCH и INDEX ВПР … В целом такая замена увеличивает скорость работы Excel на 13% .

Влияние ВПР Производительность Excel особенно заметна, если книга содержит сотни сложных формул массива, таких как VLOOKUP + SUM … Дело в том, что для проверки каждого значения в массиве требуется отдельный вызов функции ВПР … Следовательно, чем больше значений содержит массив и чем больше формул массива содержит ваша таблица, тем медленнее работает Excel.

С другой стороны hand, формула с функциями SEARCH и INDEX , она просто выполняет поиск и возвращает результат, выполняя ту же работу намного быстрее.

ИНДЕКС и ПОИСК — примеры формул

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

Как искать слева с помощью ПОИСКА и ИНДЕКСА

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

Функции ПОИСК и ИНДЕКС Excel намного более гибок, и им все равно, где находится столбец со значением, которое нужно получить. Например, вернемся к таблице со столицами штатов и населением. На этот раз мы напишем формулу ПОИСК / ИНДЕКС , которая покажет, какое место в населении занимает столица России (Москва).

Как видно на рисунке ниже, формула отлично справляется с этим:

ИНДЕКС ($ A $ 2: $ A $ 10, MATCH («Россия», $ B $ 2: $ B $ 10.0))

Теперь у вас не должно возникнуть проблем с пониманием того, как работает эта формула:

  • Сначала воспользуемся функцией ПОИСКПОЗ (ПОИСКПОЗ), который находит позицию «Россия» в списке:

    MATCH («Россия», $ B $ 2: $ B $ 10.0))
    = ПОИСК («Россия»; $ B $ 2: $ B $ 10; 0))

  • Затем мы устанавливаем диапазон для функции ИНДЕКС (ИНДЕКС), из которого для извлечения значения. В нашем случае это A2: A10 .
  • Затем мы соединяем обе части и получаем формулу:

    INDEX ($ A $ 2: $ A $ 10; MATCH («Россия»; $ B $ 2: $ B $ 10; 0))
    = ИНДЕКС ($ A $ 2: $ A $ 10; ПОИСК («Россия»; $ B $ 2: $ B $ 10; 0))

Подсказка: Правильное решение — всегда использовать абсолютные ссылки для ИНДЕКС и ПОИСК , чтобы диапазоны поиска не терялись при копировании формулы в другие ячейки.

Вычисления с использованием ИНДЕКСА и ПОИСКА в Excel (СРЕДНИЙ, МАКС, МИН)

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

1. МАКС (МАКС). Формула находит максимум в столбце D C в той же строке:

INDEX ($ C $ 2: $ C $ 10, ПОИСКПОЗ (МАКС ($ D $ 2: I $ 10), $ D $ 2: D $ 10,0))
= ИНДЕКС ($ C $ 2: $ C $ 10; ПОИСК (МАКС ( $ D $ 2: I $ 10); $ D $ 2: D $ 10; 0))

Результат: Пекин

2. MIN (МИН). Формула находит минимум в столбце D и возвращает значение из столбца C в той же строке:

INDEX ( $ C $ 2: $ C $ 10, ПОИСКПОЗ (MIN ($ D $ 2: I $ 10), $ D $ 2: D $ 10,0))
= ИНДЕКС ($ C $ 2: $ C $ 10; ПОИСК (MIN ($ D $ 2: I $ 10); $ D $ 2: D $ 10; 0))

Результат: Лима

3. СРЕДНИЙ (СРЕДНИЙ). Формула вычисляет среднее значение в диапазоне D2: D10 , затем находит ближайшее к нему и возвращает значение из столбца C в той же строке:

ИНДЕКС ($ C $ 2: $ C $ 10, ПОИСКПОЗ (СРЕДНИЙ ($ D $ 2: D $ 10), $ D $ 2: D $ 10,1))
= ИНДЕКС ($ C $ 2: $ C $ 10; ПОИСК (СРЕДНИЙ ($ D $ 2: D $ 10); $ D $ 2: D $ 10; 1))

Результат : Москва

Что нужно помнить при использовании функции СРЕДНИЙ с ИНДЕКС и ПОИСК

Использование функции СРЕДНИЙ в сочетании с ИНДЕКС и ПОИСК , в качестве третьего аргумента функции ПОИСК чаще всего необходимо указать 1 или -1 , если вы не уверены, содержит ли просматриваемый диапазон значение, равное среднему. Если вы уверены, что такое значение существует, введите 0 , чтобы найти точное совпадение.

  • Если вы укажете 1 , значения в столбце поиска должны быть отсортированы в порядке возрастания, и формула вернет максимальное значение, меньшее или равное среднему.
  • Если вы укажете -1 , значения в столбце поиска должны быть отсортированы в порядке убывания, и возвращается минимальное значение, которое больше или равно среднему.

В нашем примере значения в столбце D упорядочены в порядке возрастания, поэтому мы используем тип соответствия 1 … Формула ИНДЕКС / SEARCHPO Z возвращает «Москва», потому что население города Москва находится ближе всего к среднее (12 269 006).

Как использовать ИНДЕКС и ПОИСК для поиска известной строки и столбца

Эта формула эквивалентна двумерному поиску ВПР и позволяет s вы можете найти значение на пересечении определенной строки и столбца.

В этом примере формула INDEX / SEARCH будет очень похож на формулы, которые мы уже обсуждали в этом руководстве, с одним отличием. Угадайте, какой?

Как вы помните, синтаксис функции ИНДЕКС (ИНДЕКС) допускает три аргумента:

ИНДЕКС ( array, row_num,)
INDEX (array; line_num; [column_num])

И я поздравляю тех из вас, кто это угадал!

Начнем написав шаблон формулы. Для этого возьмите уже знакомую формулу ИНДЕКС / ПОИСК и добавьте к ней еще одну функцию ПОИСК , которая вернет номер столбца.

INDEX (Ваша таблица, (MATCH (, столбец для поиска, 0)), (MATCH (, строка для поиска, 0))
= ИНДЕКС (Ваша таблица, (MATCH (значение для вертикального поиска, столбец для поиска, 0)), (MATCH (значение для горизонтального поиска, строка для поиска, 0))

Обратите внимание, что для двумерного поиска вам необходимо указать всю таблицу в аргументе array (array) functions INDEX (INDEX).

Теперь давайте проверим эту закономерность. Ниже представлен список самых густонаселенных стран мира. Предположим, наша задача — узнать численность населения США в 2015 году.

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

Итак, давайте начнем t с двумя функциями ПОИСК , которые вернут номера строк и столбцов для функции ИНДЕКС :

  • ПОИСК по столбцу — мы ищем в столбце B , а точнее в диапазоне B2: B11 , значение, указанное в ячейке H2 (США). Функция будет выглядеть так:

    MATCH ($ H $ 2, $ B $ 1: $ B $ 11.0)
    = SEARCH ($ H $ 2; $ B $ 1: $ B $ 11; 0)

    4 , поскольку «США» — это 4-й элемент списка в столбце B (включая заголовок) .

  • MATCH for string — мы ищем значение ячейки H3 (2015) в строке 1 , то есть в ячейках A1: E1 :

    MATCH ($ H $ 3, $ A $ 1: $ E $ 1.0)
    = ПОИСК ($ H $ 3; $ A $ 1: $ E $ 1; 0)

    Результатом этой формулы будет 5 , потому что «2015» находится в 5-м столбце.

Теперь мы вставляем эти формулы в функцию INDEX и вуаля:

ИНДЕКС ($ A $ 1: $ E $ 11, ПОИСКПОЗ ($ H $ 2, $ B $ 1: $ B $ 11,0), ПОИСКПОЗ ($ H $ 3, $ A $ 1 : $ E $ 1.0))
= ИНДЕКС ($ A $ 1: $ E $ 11; ПОИСК ($ H $ 2; $ B $ 1: $ B $ 11; 0); ПОИСК ($ H $ 3; $ A $ 1: $ E $ 1; 0))

Если мы заменим функции ПОИСК на значения, они вернутся, формула станет легкой и строгой вперед:

ИНДЕКС ($ A $ 1: $ E $ 11,4,5))
= ИНДЕКС ($ A $ 1: $ E $ 11; 4; 5))

Эта формула возвращает значение на пересечении 4-й строки и 5-го столбца в диапазоне A1: E11 , то есть значение ячейки E4 … Просто? Да!

Множественный поиск с ИНДЕКСОМ и ПОИСКОМ

В руководстве по ВПР мы показали пример формулы с функция VLOOKUP для поиска по нескольким критериям. Однако существенным ограничением этого решения было добавление вспомогательного столбца. Хорошие новости: формула INDEX / ПОИСК может выполнять поиск по значениям в двух столбцах, без необходимости создания вспомогательного столбца!

Предположим, у нас есть список заказов и мы хотим найти сумму по двум критериям — имя покупателя (Клиент) и товар (Товар). Дело осложняется тем, что один покупатель может купить несколько разных товары сразу, а имена покупателей указаны в таблице на листе Таблица поиска расположены в произвольном порядке.

Это формула ИНДЕКС / ПОИСК решает проблему:

(= INDEX («Таблица поиска»! $ A $ 2: $ C $ 13, ПОИСКПОЗ (1, (A2 = «Таблица поиска»! $ A $ 2: $ A $ 13) *
(B2 = «Таблица поиска»! $ B $ 2: $ B $ 13), 0), 3))
(= ИНДЕКС («Таблица поиска»! $ A $ 2: $ C $ 13; ПОИСКПОЗ (1; (A2 = «Таблица поиска»! $ A $ 2: $ A $ 13) *
(B2 = «Таблица поиска»! $ B $ 2: $ B $ 13); 0); 3))

Эта формула более сложна, чем другие, которые мы обсуждали ранее, но вооружена знанием функций ИНДЕКС и ПОИСК Вы победите ее. Самая сложная часть — это функция ПОИСК , я думаю, сначала ее следует объяснить.

MATCH (1, (A2 = «Таблица поиска»! $ A $ 2 : $ A $ 13), 0) * (B2 = «Таблица поиска»! $ B $ 2: $ B $ 13)
MATCH (1; (A2 = «Таблица поиска»! $ A $ 2: $ A $ 13); 0) * (B2 = «Таблица поиска»! $ B $ 2: $ B $ 13)

В приведенной выше формуле желаемое значение равно 1 , а массив поиска является результатом умножения. Хорошо, а что нам размножать и почему? Разберем его по порядку:

  • Мы берем первое значение в столбце A (Клиент) на листе Main таблицу и сравните ее со всеми именами клиентов в таблице на листе Таблица поиска (A2: A13).
  • Если соответствие найдено, уравнение возвращает 1 (ИСТИНА), а если нет — 0 (ЛОЖЬ).
  • Затем мы делаем то же самое для значений столбца. B (Продукт).
  • Затем мы умножаем результаты (1 и 0). Только если совпадения будут найдены в обоих столбцах (т.е. оба критерия верны), вы получите 1 … Если оба критерия ложны или выполняется только один из них, вы получите 0 .

Теперь вы понимаете, почему мы спросили 1 , каково желаемое значение? Верно, что функция ПОИСК вернула позицию только при соблюдении обоих критериев..

Примечание: В этом случае вы должны использовать третий необязательный аргумент функции ИНДЕКС … Это необходимо потому что в первом аргументе мы устанавливаем всю таблицу и должны сообщить функции, из какого столбца извлечь значение. В нашем случае это столбец C (Sum), поэтому мы ввели 3 .

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

Если все сделано правильно, вы получите результат как на картинке ниже:

INDEX и ПОИСК в сочетании с ЕСЛИОШИБКА в Excel

Как вы, наверное, уже заметили (и более одного раза), если вы вводите неверное значение, например, которого нет в просматриваемом массиве, формула ИНДЕКС / ПОИСК сообщает об ошибке # Н/Д (# Н/Д) или # ЗНАЧ! (# ЗНАЧЕНИЕ!). Если вы хотите заменить такое сообщение чем-то более понятным, вы можете вставить формулу с ИНДЕКС и ПОИСК в функцию ЕСЛИОШИБКА .

Синтаксис функции ЕСЛИОШИБКА очень простой:

ЕСЛИОШИБКА (значение, значение_если_ошибка)
ЕСЛИОШИБКА (значение ; value_if_error)

Где аргумент value (value) — это значение, проверенное на наличие ошибки (в нашем случае результат формулы ИНДЕКС / ПОИСК ); а аргумент value_if_error (value_if_error) — это значение, возвращаемое, если формула выдает ошибку.

Например, вы можете вставить в функцию ЕСЛИОШИБКА , например:

ЕСЛИОШИБКА (ИНДЕКС ($ A $ 1: $ E $ 11, ПОИСКПОЗ ($ G $ 2, $ B $ 1: $ B $ 11,0), MATCH ($ G $ 3, $ A $ 1: $ E $ 1.0)),
«Совпадений не найдено. Повторите попытку!») = ЕСЛИОШИБКА (ИНДЕКС ($ A $ 1: $ E $ 11 ; SEARCH ($ G $ 2; $ B $ 1: $ B $ 11; 0); SEARCH ($ G $ 3; $ A $ 1: $ E $ 1; 0));
«Нет совпадений найдено. Повторите попытку! «)

А теперь, если кто-то введет неправильное значение, формула вернет такой результат:

Если вы предпочитаете оставлять ячейку пустой в случае ошибки, вы можете использовать кавычки («») в качестве значение второго аргумента функции ЕСЛИОШИБКА … Примерно так:

ЕСЛИОШИБКА (ИНДЕКС (массив, ПОИСКПОЗ (искомое_значение, искомое_массив, 0), «» )
ЕСЛИОШИБКА (ИНДЕКС (массив; ПОИСК (поиск_значение; поиск_массив у; 0); «»)

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

Если вы ищете функции поиска, Google показывает, что ВПР намного популярнее, чем ИНДЕКС.. Это понятно, потому что для того, чтобы дать функции ИНДЕКС такую ​​же функциональность, что и ВПР, нужно использовать другую формулу — ПОИСК. Мне всегда было сложно попробовать освоить две новые функции одновременно. Но они предоставляют больше возможностей и гибкости при создании электронных таблиц. Но обо всем по порядку.

Функция VLOOKUP ()

Предположим, у вас есть таблица с данными сотрудников. В первом столбце указан табельный номер сотрудника, в остальных — другие данные (ФИО, отдел и т. Д.). Если у вас есть табельный номер, вы можете использовать функцию ВПР для возврата определенной информации о сотруднике. Синтаксис формулы = VLOOKUP (lookup_value; table; column_number; [range_view]). Она говорит Excel: «Найдите в таблице строку, первая ячейка которой соответствует lookup_value, и вернуть значение ячейки с порядковым номером column_number ».

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

Когда ключевое поле находится справа от данных, которые вы хотите получить, ВПР не поможет. Если, конечно, можно было бы задать column_number -1, то проблем не было бы. Одним из распространенных решений является добавление нового столбца A, копирование имен сотрудников в этот столбец, заполнение табельных номеров с помощью VLOOKUP, сохранение их как значений и удаление временного столбца A.

INDEX () function

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

Начнем с функции ИНДЕКС. Кошмарное название. Когда кто-то говорит «индекс», у меня в голове нет ни одной ассоциации, что делает эта функция. И ей требуется целых три аргумента: = INDEX (array; line_number; [column_num]).

Проще говоря, Excel переходит в массив данных и возвращает значение на пересечении указанной строки и столбца. Как бы просто. Таким образом, формула = ИНДЕКС ($ A $ 2: $ C $ 6; 4; 2) вернет значение в ячейке B5.

Применительно к нашей задаче для возврата табельного номера сотрудника формула должна иметь вид = ИНДЕКС ($ A $ 2: $ 6 долларов;?; 1). Это звучит как чепуха, но если мы заменим вопросительный знак формулой ПОИСКПОЗ, у нас будет решение.

Функция ПОИСК ()

Синтаксис этой функции следующий: следующим образом: = MATCH (lookup_value; lookup_array; [match_type]).

Она сообщает Excel: «Найти lookup_value в массиве данных и вернуть номер строки массива, в котором встречается это значение. «Таким образом, чтобы найти, в какой строке имя сотрудника находится в ячейке A10, нужно написать формулу = ПОИСК (A10; $ B $ 2: $ B $ 6; 0). Если ячейка A10 содержит имя« Колин Фарел «, тогда ПОИСКПОЗ вернет 5-ю строку массива B2: B6.

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

INDEX ($ A $ 2: $ B $ 6; SEARCH (A10; $ B $ 2: $ B $ 6,0); 1)

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

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

Что такое ВПР в Excel — область действия

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

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

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

Примеры для наглядности: в таблицах 1,2 — исходные данные, в таблице 3 — что нужно получить.

Таблица исходных данных 1

Таблица объединенных данных 3

ПОЛНОЕ ИМЯ . ZP . Fine
Иванов 20 000 ₽ 38 000 ₽
Петров 19 000 ₽ 12 000 ₽
Сидоров 21 000 ₽ 200 ₽

Функция ВПР в Excel — как использовать

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

Теперь нам нужно вызвать функцию. Это можно сделать по-разному:

Необходимо заполнить значения u200b u200b для функции VLOOKUP

Результат налицо — в таблице 3 (см. выше).

VLOOKUP — инструкция по работе с двумя условиями

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

Например, необходимо в таблице 4 вставить цену из таблицы 5.

Телефон таблица характеристик 4

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

Добавьте крайний левый столбец. Например, назовем его «Союз». В первой ячейке значений у нас B 2, пишем конструкцию «= B 2 & C 2». Воспроизводим мышкой. Получается как в таблице 6.

Таблица характеристик телефона 6

Union Имя RAM Цена
ZTE 0.5 ZTE 0,5 1 990 ₽
ZTE 1 ZTE 1 3 099 ₽
DNS1 DNS 1 3 100 ₽
DNS 0,5 DNS 0,5 2 240 ₽
Alcatel 1 Alcatel 1 4 500 ₽
Alcatel 256 Alcatel 256 450 ₽

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

Посмотрите видеоурок о том, как использовать функцию ВПР в Excel для чайников:

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

Здравствуйте, дорогой читатель!

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

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

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

Если расшифровываете название функции ВПР, то по первой букве станет понятно, как работает эта функция, В означает «Вертикаль» , то есть ищет значения в, но для горизонтальных списков у нас будет. Впервые функция ВПР стала доступна нам в Excel 2000.
Функция ВПР в Excel имеет следующий синтаксис:

= VLOOKUP (_search value _; _ table_; _ column number_; _ [interval view] _) где:

  • lookup_value — это именно то значение, которое нам нужно искать, и это может быть любое значение: число, дата, текст, ссылка на ячейку который содержит искомое значение или значение, полученное по другой формуле;
  • table — это два или более столбца с разными данными, кстати, функция не учитывать регистр символов в поиске;
  • column_number — это номер столбца в указанном диапазоне, из которого будет значение получено, что находится в найденной строке;
  • interval_view — этот параметр определяет, что именно мы будем искать; для точного совпадения аргумент будет равен «FALSE» или приблизительного совпадения, аргумент станет TRUE … Этот параметр является необязательным, но важным тем не менее. В приведенных ниже примерах я продемонстрирую, как создавать точные и приблизительные формулы.

Теперь, используя пример, давайте рассмотрим, как искать некоторые данные на другом листе, поскольку на практике , функция VLOOKUP очень редко используется для поиска на текущем листе:

VLOOKUP («GM»; $ A $ 5: $ B $ 10; 2)

Формула ищет текст «GM» в столбце A текущего листа..
Совет! При использовании аргумента «таблица» желательно использовать такую ​​опцию, как (это адрес ячейки со знаком $). В этом случае диапазон поиска станет фиксированным и не изменится при копировании формулы.

Давайте посмотрим на пример поиска значений, как функция ВПР работает в другой книге :

VLOOKUP («GM»; [Путь к файлу] Base! A2: B10; 2)

Как видите, ничего сложного, аргумент только что получил сложно. «table» , теперь к нему привязана ссылка на файл с его именем.

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

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

ВПР функция при подстановке может использовать следующие символы:

  • «?» (вопросительный знак ) — позволяет заменить любой один символ;
  • «*» (звездочка) — заменит любое количество и последовательность символов .

ВПР («A *»; $ A $ 2: $ B $ 10; 1; FALSE)

Совет! Для правильной работы функции ВПР необходимо использовать параметр «ЛОЖЬ» в качестве четвертого аргумента.
Что ж, если мы уже затронули тему точного или приблизительного совпадения в синтаксисе функции ВПР, то давайте присмотритесь к нему поближе:

  • если аргумент «Интервальный просмотр» равен «ЛОЖЬ» , в этом случае формула ищет точное совпадение с аргументом подстановочного значения. Если в формуле встретятся два или более значений, которые соответствуют аргументу «требуемое значение», то будет выбрано первое из списка, в случае если совпадения не найдено, формула вернется;
  • если аргумент «Интервальный просмотр» имеет значение «ИСТИНА» , формула будет искать приблизительное совпадение, точнее, функция будет сначала ищите точное совпадение, и только потом, не найдя его, выберете приблизительное.

Совет! В случае, когда аргумент «интервал просмотра» равен «ИСТИНА» или не указан, необходимо значение в первом столбце, от наименьшего к наибольшему. В противном случае существует вероятность получения ошибочного результата от функции ВПР..

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

= VLOOKUP (200, $ A $ 2: $ B $ 15, 2, FALSE)

Несмотря на то, что у нас несколько 200 значений, мы получили только одно, так как при точном совпадении функции ВПР система использует только первое значение, которое было найдено в указанном диапазоне.
Теперь давайте проверим, как работает функция ВПР для приблизительного совпадения значений. Посмотрим, какая машина движется со скоростью 260 км/ч. Первое, что вы делаете, когда «Интервальное представление» равно «ИСТИНА» — вы сортируете свой диапазон значений по первому столбцу в порядке возрастания. . Это необходимо и важно, поскольку функция ВПР находит следующее по величине значение из заданного условия, а затем поиск останавливается. Если вы не будете следовать советам по сортировке, результатом будет сообщение или другие странные результаты.

Для поиска мы используем следующую ВПР:

= VLOOKUP (260; $ A $ 2: $ B $ 15; 2; TRUE)

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

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

И это все для мне! Очень надеюсь, что все вышесказанное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читабельности и вдохновляет писать новые статьи! Делитесь прочитанным с друзьями и им нравится!

Оцените статью
clickpad.ru
Добавить комментарий