1. В дополнительный столбец, в котором будем указывать рейтинг, вставляем функцию РАНГ (пишем в ячейке =РАНГ и выбираем из списка предложенную EXCEL функцию, жмем в строке формул fx)
2. Заполняем аргументы в открывшемся окне: «Число» - указываем первое значение в нашей таблице в той же строке, где находится формула.
3. «Ссылка» - указываем весь массив данных, т.е. диапазон со всеми числами (значениями продаж).
4. Фиксируем границы этого диапазона (нажимаем F4 на клавиатуре) для того, чтобы при протягивании в дальнейшем адрес диапазона не «съезжал» и нажимаем ОК.
5. Протягиваем, формулу на все ячейки столбца «рейтинг» вниз.
При пользовании данной функцией, расчет рейтинга производится автоматически, и если вы изменили какое-либо значение, то по рейтингу произойдет автоматический пересчет.
Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA
и далее "перевести")
Предположим что у Вас есть вот такой отчёт по продажам торговых представителей:
Из него Вам необходимо узнать сколько карандашей продал торговый представитель Иванов в январе .
ПРОБЛЕМА : Как суммировать данные по нескольким критериям??
РЕШЕНИЕ
: Способ 1:
БДСУММ(A1:G16;F1;I1:K2)
В английской версии:
DSUM(A1:G16,F1,I1:K2)
КАК ЭТО РАБОТАЕТ:
![](https://i0.wp.com/excelworld.ru/Statyi/Priemy/SUM_by_several/SUM_by_several_criteria_Month_p1.gif)
Из указанной нами базы данных A1:G16 функция БДСУММ извлекает и суммирует данные столбца Количество (аргумент "Поле " = F1 ) по заданным в ячейках I1:K2 (Продавец = Иванов ; Продукция = Карандаши ; Месяц = Январь ) критериям.
МИНУСЫ : Список критериев должен быть на листе.
ПРИМЕЧАНИЯ
: Количество критериев суммирования ограничено оперативной памятью.
ОБЛАСТЬ ПРИМЕНЕНИЯ
: Любая версия Excel
Способ 2:
СУММПРОИЗВ((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)
В английской версии:
SUMPRODUCT((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)
КАК ЭТО РАБОТАЕТ:
Функция СУММПРОИЗВ формирует массивы из значений ИСТИНА и ЛОЖЬ, согласно выбранным критериям, в памяти Excel.
Если-бы вычисления производились в ячейках листа (для наглядности я всю работу формулы продемонстрирую так, как-будто вычисления происходят на листе, а не в памяти), то массивы выглядели бы так:
Очевидно что если например, D2=Карандаши , то значение будет равно ИСТИНА, а если D3=Папки , то ЛОЖЬ (так как критерием отбора товара в нашем примере является значение Карандаши ).
Зная о том что значение ИСТИНА всегда равно 1, а ЛОЖЬ всегда равно 0 мы продолжаем работать с массивами как с числами 0 и 1.
Перемножив полученные значения массивов между собой последовательно, мы получим ОДИН массив из нолей и единиц. Там где выполнялись все три критерия отбора, (ИВАНОВ, КАРАНДАШИ, ЯНВАРЬ
) т.е. все условия принимали значения ИСТИНА получаем 1 (1*1*1 = 1), если же хотя-бы одно условие не выполнялось - получим 0 (1*1*0 = 0 ; 1*0*1 = 0 ; 0*1*1 = 0).
Теперь осталось только умножить полученный массив на массив содержащий данные, которые нам необходимо в итоге просуммировать (диапазон F2:F16 ) и собственно, просуммировать то что на 0 не умножилось.
Теперь сравните полученные при помощи формулы и при пошаговом вычислении на листе массивы (выделены красным).
Думаю всё понятно:)
МИНУСЫ : СУММПРОИЗВ - "тяжёлая" формула массива. При вычислениях на больших диапазонах данных заметно увеличивается время пересчёта.
ПРИМЕЧАНИЯ
ОБЛАСТЬ ПРИМЕНЕНИЯ : Любая версия Excel
Способ 3: Формула массива
СУММ(ЕСЛИ((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2);F2:F16))
В английской версии:
SUM(IF((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2),F2:F16))
КАК ЭТО РАБОТАЕТ: Точно так же как и Способ №2. Есть только два отличия - данная формула вводится нажатием Ctrl+Shift+Enter , а не просто нажатием Enter и массив 0-й и 1-ц не умножается на диапазон суммирования, а отбирается с помощью функции ЕСЛИ.
МИНУСЫ : Формулы массива при вычислениях на больших диапазонах данных заметно увеличивают время пересчёта.
ПРИМЕЧАНИЯ
: Количество обрабатываемых массивов ограничено 255.
ОБЛАСТЬ ПРИМЕНЕНИЯ
: Любая версия Excel
Способ 4:
СУММЕСЛИМН(F2:F16;B2:B16;I2;D2:D16;J2;A2:A16;K2)
Из данной статьи вы узнаете о 5 полезных свойствах сводных таблиц, которые помогу вам быстро и детально проанализировать ситуацию (на примере анализа клиентской базы). Вы узнаете:
1. Как сгруппировать данные;
2. Какие показатели можно рассчитать при сведении данных;
3. Как одновременно рассчитать несколько показателей по одному параметру при сведении данных;
4. Какие дополнительные возможности расчета при сведении данных вы можете использовать?
5. О возможности сортировки.
И на основании этого анализа мы затронем мощнейшую технику планирования мероприятий по стимулированию сбыта на рынках FMCG.
Для начала сделаем сводную таблицу. Возьмем простую таблицу продажи клиентам по дням.
Установим курсор в левый верхний угол нашей таблицы, затем переходим в меню "Вставка" и нажимаем на кнопку "Сводная таблица":
В диалоговом окне "Создание сводной таблицы" нажимаем "ОК":
Получили сводную таблицу на новом листе:
1-е полезное свойство сводной таблицы для бизнес анализа - группировка данных
Итак, у нас есть отгрузки клиентам по дням, мы хотим понять, в каком диапазоне отгрузок у нас максимальные объемы продаж. Для этого нам надо сгруппировать отгрузки в диапазоны.
Перетаскиваем поле "Отгрузка сумма" в область сводной таблицы "Название строк" (зажимаем поле "Отгрузка_сумма" левой кнопкой мыши и перетаскиваем в раздел сводной "Название строк"):
Мы вывели все отгрузки в левый столбец сводной. Теперь устанавливаем курсор наши отгрузки (как на рисунке):
Заходим в меню Excel "Данные" и нажимаем на кнопку "Группировать"
В появившемся диалоговом окне устанавливаем шаг группировки "5000" (вы можете ввести любой) и нажимаем "ОК"
Получаем сгруппированные объемы продаж с заданным шагом:
Для того, чтобы группировка выглядела красиво и воспринималось, нажимаем еще раз кнопку "Группировать" и руками ставим ровные значения, для значения "начиная с" - "-15 000" (ниже минимального значения, кратного 5000) "по" - "45 000" (больше максимальной группы, кратное 5000).
Получаем сгруппированные данные по сумме отгрузке:
2-е полезное свойство сводных таблиц для бизнес анализа -
возможность рассчитать различные сводные параметры по полям из исходной таблицы
Итак, отгрузки сгруппировали, теперь посмотрим - какие объемы продаж приходятся на каждый диапазон отгрузок. Для этого просуммируем отгрузки в сводной.
Зажимаем левой кнопкой мыши поле "Отгрузка_сумма" и перетаскиваем его в поле сводной "Значения":
Сводная по умолчанию рассчитала "Количество по полю Отгрузка_сумма", т.е. кол-во записей в нашей исходной таблице на листе "Данные". Т.к. таблица у нас содержит информацию по продажам клиентам по дням, то наш показатель "Количество по полю Отгрузка_сумма" - это количество отгрузок клиентам.
В итоге в сводной таблице мы видим количество отгрузок клиентам в разных диапазонах отгрузок:
Как нам из количества отгрузок получить сумму отгрузок?
Нажимаем левой кнопкой мыши на поле "Количество по полю Отгрузка сумма" в области сводной таблицы "Значения", и в раскрывшемся меню выбираем "Параметры полей значений…"
В раскрывшемся окне выбираем интересующую нас операцию сведения данных (Сумма, количество, среднее, максимум, минимум...). Выберем нужную нам операцию "сумма" и нажимаем "ОК".
Получаем суммарный объём продаж для каждого диапазона отгрузок:
Т.е. мы видим, какой объем продаж приходится на отгрузки в диапазоне от 0 до 5000 руб, от 5000 до 10 000 руб. и т.д. И видно, что максимальный объём отгрузок приходится на диапазон от нуля до 5000 руб.
3 свойство – возможность для одного поля рассчитывать различные операции сведения данных
Теперь мы хотели бы посмотреть, какое количество отгрузок и какие средние отгрузки у нас приходятся на каждый из диапазонов. Для этого мы с помощью сводной таблицы рассчитаем количество отгрузок и средние отгрузки.
В область сводной таблицы "Значения" перетаскиваем еще 2 раза поле "Отгрузка_сумма" и в параметрах поля значений для второго выбираем "количество" и для третьего поля выбираем "среднее".
Получаем для каждого диапазона отгрузок объем продаж, количество отгрузок и среднюю отгрузку:
Теперь мы видим, в каком диапазоне отгрузок максимальный объем продаж и максимальное количество отгрузок. В нашем примере это для диапазона от 0 до 5000 руб. и объём продаж и количество отгрузок максимально.
4-е свойство сводных таблиц - возможность проводить дополнительные расчеты
Для наглядности анализа данных добавим еще 2 параметра - "Долю по объёма продаж по каждой группе"и "Долю количества отгрузок для каждой группы".
Для этого в поле сводной таблицы "Значения" перетащим еще 2 раза поле "Отгрузка сумма"
Причем для одного параметра в меню "Параметры поля значений" () мы выберем операцию "сумма", а для второго операцию "количество" .
Получаем таблицу следующего вида:
Теперь еще раз заходим в "Параметры полей значений" и входим во вкладку "Дополнительные вычисления":
Выбираем в поле "Дополнительные вычисления" пункт "Доля от общей суммы"
Получаем таблицу, в которой для каждого диапазона отгрузок клиентам мы видим объём продаж, количество отгрузок, среднюю отгрузку, долю объема продаж по каждой группе и долю количества отгрузок по каждой группе:
5 полезное свойство - сортировка
Теперь для наглядности от максимальной к минимальной группе по объёму продаж сделаем сортировку. Для этого установим курсор в поле с объёмом продаж по группам и нажмем на кнопку "сортировка от максимального к минимальному":
Видно, что максимальная группа по объёму продаж и количеству отгрузок - это группа "от 0 до 5000 руб." средние продажи в этой группе составляют 1971 руб.
Обратите внимание! Средняя отгрузка по всем клиентам значительно отличается от 86% отгрузок. Причем отличается значительно
- по всем группам средняя отгрузка равна 2 803 руб. (в строке общий итог).
- А по 86% отгрузок 1 971 руб.
Это серьезная разница, и если мы будем стимулировать продажи опираясь на 86% отгрузок и среднюю по ним - 1 971 руб., то наши действия будут точней, а эффект гораздо выше, т.к. мы сможем заинтересовать максимальное количество клиентов.
Данный пример показывает мощнейшую технику планирования мероприятий по стимулированию сбыта на рынках массового сбыта и прогнозирования объёма продаж, которая может помочь вам значительно влиять на ситуацию и изменять её.
Если есть вопросы, обращайтесь.
Точных вам прогнозов!
Присоединяйтесь к нам!
Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа :
- Novo Forecast Lite - автоматический расчет прогноза в Excel .
- 4analytics - ABC-XYZ-анализ и анализ выбросов в Excel.
- Qlik Sense Desktop и QlikView Personal Edition - BI-системы для анализа и визуализации данных.
Тестируйте возможности платных решений:
- Novo Forecast PRO - прогнозирование в Excel для больших массивов данных.
Во второй части нашего учебника по функции ВПР (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 . После этого можно использовать следующую формулу:
VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)
=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)
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,0)+2)&":$C16"),2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4;0)+2)&":$C16");2;ИСТИНА);"")
В этой формуле:
- $F$2 – ячейка, содержащая имя покупателя (она неизменна, обратите внимание – ссылка абсолютная);
- $B$ – столбец Customer Name ;
- Table4 – Ваша таблица (на этом месте также может быть обычный диапазон);
- $C16 – конечная ячейка Вашей таблицы или диапазона.
Эта формула находит только второе совпадающее значение. Если же Вам необходимо извлечь остальные повторения, воспользуйтесь предыдущим решением.
Если Вам нужен список всех совпадений – функция ВПР тут не помощник, поскольку она возвращает только одно значение за раз – и точка. Но в Excel есть функция INDEX (ИНДЕКС), которая с легкостью справится с этой задачей. Как будет выглядеть такая формула, Вы узнаете в следующем примере.
Извлекаем все повторения искомого значения
Как упоминалось выше, ВПР не может извлечь все повторяющиеся значения из просматриваемого диапазона. Чтобы сделать это, Вам потребуется чуть более сложная формула, составленная из нескольких функций Excel, таких как INDEX (ИНДЕКС), SMALL (НАИМЕНЬШИЙ) и ROW (СТРОКА)
Например, формула, представленная ниже, находит все повторения значения из ячейки F2 в диапазоне B2:B16 и возвращает результат из тех же строк в столбце C.
{=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 (тип_сопоставления).
Использовав 0 в третьем аргументе, Вы говорите функции ПОИСКПОЗ искать первое значение, в точности совпадающее с искомым значением. Это равносильно значению 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, Вам может понравиться вот такой наглядный и запоминающийся способ:
![](https://i2.wp.com/office-guru.ru/images/excel/translations1/funktsiya-vpr-excel-rukovodstvo-20.png)
При вводе имени, Microsoft Excel будет показывать подсказку со списком подходящих имен, так же, как при вводе формулы.
- Нажмите Enter и проверьте результат
В целом, какой бы из представленных выше методов Вы ни выбрали, результат двумерного поиска будет одним и тем же:
Используем несколько ВПР в одной формуле
Бывает так, что основная таблица и таблица поиска не имеют ни одного общего столбца, и это мешает использовать обычную функцию ВПР . Однако, существует ещё одна таблица, которая не содержит интересующую нас информацию, но имеет общий столбец с основной таблицей и таблицей поиска.
Давайте разберем следующий пример. У нас есть основная таблица (Main table) со столбцом SKU (new) , куда необходимо добавить столбец с соответствующими ценами из другой таблицы. Кроме этого, у нас есть 2 таблицы поиска. Первая (Lookup table 1) содержит обновленные номера SKU (new) и названия товаров, а вторая (Lookup table 2) – названия товаров и старые номера SKU (old) .
Чтобы добавить цены из второй таблицы поиска в основную таблицу, необходимо выполнить действие, известное как двойной ВПР или вложенный ВПР .
- Запишите функцию ВПР
, которая находит имя товара в таблице Lookup table 1
, используя SKU
, как искомое значение:
VLOOKUP(A2,New_SKU,2,FALSE)
=ВПР(A2;New_SKU;2;ЛОЖЬ)Здесь New_SKU – именованный диапазон $A:$B в таблице Lookup table 1 , а 2 – это столбец B, который содержит названия товаров (смотрите на рисунке выше)
- Запишите формулу для вставки цен из таблицы Lookup table 2
на основе известных названий товаров. Для этого вставьте созданную ранее формулу в качестве искомого значения для новой функции ВПР
:
VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)
=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)Здесь Price – именованный диапазон $A:$C в таблице Lookup table 2 , а 3 – это столбец C, содержащий цены.
На рисунке ниже виден результат, возвращаемый созданной нами формулой:
Динамическая подстановка данных из разных таблиц при помощи ВПР и ДВССЫЛ
В начале разъясним, что мы подразумеваем под выражением “Динамическая подстановка данных из разных таблиц”, чтобы убедиться правильно ли мы понимает друг друга.
Бывают ситуации, когда есть несколько листов с данными одного формата, и необходимо извлечь нужную информацию с определенного листа в зависимости от значения, которое введено в заданную ячейку. Думаю, проще это объяснить на примере.
Представьте, что имеются отчеты по продажам для нескольких регионов с одинаковыми товарами и в одинаковом формате. Требуется найти показатели продаж для определенного региона:
Если у Вас всего два таких отчета, то можно использовать до безобразия простую формулу с функциями ВПР и ЕСЛИ (IF), чтобы выбрать нужный отчет для поиска:
VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
- $D$2 – это ячейка, содержащая название товара. Обратите внимание, здесь мы используем абсолютные ссылки, чтобы избежать изменения искомого значения при копировании формулы в другие ячейки.
- $D3 – это ячейка с названием региона. Используем абсолютную ссылку для столбца и относительную ссылку для строки, поскольку планируем копировать формулу в другие ячейки того же столбца.
- FL_Sal es и CA_Sales – названия таблиц (или именованных диапазонов), в которых содержаться соответствующие отчеты о продажах. Вы, конечно же, можете использовать обычные названия листов и ссылки на диапазоны ячеек, например ‘FL Sheet’!$A$3:$B$10 , но именованные диапазоны гораздо удобнее.
Однако, когда таких таблиц много, функция ЕСЛИ – это не лучшее решение. Вместо нее можно использовать функцию ДВССЫЛ (INDIRECT), чтобы возвратить нужный диапазон поиска.
Как Вы, вероятно, знаете, функция ДВССЫЛ используется для того, чтобы вернуть ссылку, заданную текстовой строкой, а это как раз то, что нам сейчас нужно. Итак, смело заменяем в представленной выше формуле выражение с функцией ЕСЛИ на ссылку с функцией ДВССЫЛ . Вот такая комбинация ВПР и ДВССЫЛ отлично работает в паре:
VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
- $D$2 – это ячейка с названием товара, она неизменна благодаря абсолютной ссылке.
- $D3 – это ячейка, содержащая первую часть названия региона. В нашем примере это FL .
- _Sales – общая часть названия всех именованных диапазонов или таблиц. Соединенная со значением в ячейке D3, она образует полное имя требуемого диапазона. Ниже приведены некоторые подробности для тех, кто не имеет опыта работы с функцией ДВССЫЛ .
Как работают ДВССЫЛ и ВПР
Во-первых, позвольте напомнить синтаксис функции ДВССЫЛ (INDIRECT):
- A1 , если аргумент равен TRUE (ИСТИНА) или не указан;
- R1C1 , если F ALSE (ЛОЖЬ).
Итак, давайте вернемся к нашим отчетам по продажам. Если Вы помните, то каждый отчёт – это отдельная таблица, расположенная на отдельном листе. Чтобы формула работала верно, Вы должны дать названия своим таблицам (или диапазонам), причем все названия должны иметь общую часть. Например, так: CA_Sales , FL_Sales , TX_Sales и так далее. Как видите, во всех именах присутствует “_Sales”.
Функция ДВССЫЛ соединяет значение в столбце D и текстовую строку “_Sales”, тем самым сообщая ВПР в какой таблице искать. Если в ячейке D3 находится значение “FL”, формула выполнит поиск в таблице FL_Sales , если “CA” – в таблице CA_Sales и так далее.
Результат работы функций ВПР и ДВССЫЛ будет следующий:
Если данные расположены в разных книгах Excel, то необходимо добавить имя книги перед именованным диапазоном, например:
VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Если функция ДВССЫЛ ссылается на другую книгу, то эта книга должна быть открытой. Если же она закрыта, функция сообщит об ошибке #REF! (#ССЫЛ!).
Как правило, эффективность больших по объему, сложных операций не может быть охарактеризована с помощью одного показателя 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 – наиболее эффективный, но зато сравнительно дорогой; X 20 – самый дешевый, но зато не столь эффективный. Дело принимающего решение – разобраться в том, какой ценой мы можем оплатить известное повышение эффективности или, наоборот, какой эффективности мы согласны пожертвовать, чтобы не нести слишком больших материальных потерь.
S
x
x
xx
x
xx
x
xx
xx
x
xx
xx
x
xx
Ввиду
того, что комплексная оценка операции
сразу по нескольким показателям
затруднительна, на практике объединяют
несколько показателей в один обобщенный
показатель. Нередко в качестве такого
критерия берут дробь; в числителе ставят
те показатели 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 * - максимально возможное значение W 1), и при этом ограничении ищем решение, обращающее в максимум W 2 .
Далее снова назначается «уступка» в показателе W 2 , ценой которой можно максимизировать W 3 и т.д. Такой способ хорош тем, что здесь сразу видно, ценой какой «уступки» в одном показателе приобретается выигрыш в другом. При этом свобода выбора решения, приобретаемая ценой даже незначительных уступок, может оказаться существенной, т.к. в районе максимума обычно эффективность решения меняется очень слабо.
Так или иначе, при любом способе формализации, задача количественного обоснования решения по нескольким показателям остается не до конца определенной, и окончательный выбор решения определяется волевым актом «командира». Дело исследователя - представить в распоряжение «командира» достаточное количество данных, позволяющее ему всесторонне оценить преимущество и недостатки каждого варианта решения и, опираясь на них, сделать окончательный выбор.