Excel. Использование ВПР для решения общих проблем

Рубрика: 7. Полезняшки Excel

Это четвертая глава книги Билла Джелена. Всё о ВПР: от первого применения до экспертного уровня. В главе рассматривается базовое использование ВПР. Более сложные примеры рассматриваются в главах 6 и 7.

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

Рис. 4.1. Какие элементы из столбца С есть в наличии на складе (в столбце D)

Рис. 4.1. Какие элементы из столбца С есть в наличии на складе (в столбце D)?

Скачать заметку в формате Word или pdf, примеры в формате Excel

Стратегия: введите в ячейку В2 формулу ВПР с использованием таблицы подстановки D2:D16. Для номера столбца подстановки, используйте число 1. Если элемент найден в списке товаров на складе, ВПР вернет название товара. Если товара нет в наличии, вы получите ошибку #Н/Д (рис. 4.2). Формула будет выглядеть так =ВПР(A2; =$D$2:$D$16;1;ЛОЖЬ) или так =ВПР(A2;Таблица24[[#Все];[Товары на складе]];1;ЛОЖЬ)

Рис. 4.2. Товары с Н_Д отсутствуют на складе

Рис. 4.2. Товары с #Н/Д отсутствуют на складе

Если вы выполняете анализ для себя, то вы вполне можете удовлетвориться таким результатом. Можно отсортировать список А1:В6 по столбцу В по возрастанию. Все имеющиеся в наличии товары будут отображаться в верхней части списка (рис. 4.3).

Рис. 4.3. Товары, которых нет в наличии отсортированы вниз таблицы

Рис. 4.3. Товары, которых нет в наличии отсортированы вниз таблицы

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

Рис. 4.4. Формула с ВПР может возвращать значения ИСТИНА_ЛОЖЬ или любые слова

Рис. 4.4. Формула с ВПР может возвращать значения ИСТИНА/ЛОЖЬ или любые слова

В столбце C, функция ЕОШИБКА получает результат от ВПР и преобразует его в значение ИСТИНА или ЛОЖЬ. Немного странно, что отсутствие товара дает значение ИСТИНА. Ситуация прояснится, если вы обратите внимание на заголовок в C1 – «Отсутствует?». Формула в С2: =ЕОШИБКА(ВПР(A2;$A$10:$A$23;1;ЛОЖЬ)).

Вполне вероятно, что перевернутая логика столбца C может вас раздражать. Если позвонил клиент и спросил, есть ли некий товар в наличии, вы должны были бы сказать: «это ложь, что товар есть в наличии». Большинство людей так не говорят, поэтому желательно, чтобы значение ИСТИНА означало бы, что товар есть в наличии. Добавьте функцию НЕ перед формулой. Это функция просто поменяет ЛОЖЬ на ИСТИНА и наоборот. Формула в D2 =НЕ(ЕОШИБКА(ВПР(A2;$A$10:$A$23;1;ЛОЖЬ))).

Начиная с версии 2007 в Excel появились новые функции проверки ошибок, в частности ЕСЛИОШИБКА. Формула в столбце Е вернет слова Out of Stock! (Нет на складе). Я не большой поклонник такого подхода. Если вашему менеджеру не понравился столбец B, он не полюбит и столбец Е. Формула в Е2 =ЕСЛИОШИБКА(ВПР(A2;$A$10:$A$23;1;ЛОЖЬ);"Out of Stock!").

Для абсолютного контроля над тем, какие слова или словосочетания появятся в ответ на запрос, используйте функцию ЕСЛИ. Вы можете указать слова в кавычках, которые будут использоваться для значений ИСТИНА и ЛОЖЬ. Формула в F2 =ЕСЛИ(ЕОШИБКА(ВПР(A2;$A$10:$A$23;1;ЛОЖЬ));"Out of Stock — Back Ordered";"Cha-Ching!").

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

Что делать, если вам потребуются некоторые расчеты, после того, как вы узнаете, какие товары есть в наличии? Рекомендую воспользоваться тем же типом формулы, что и в F2, но вместо слов использовать 1 для наличия на складе и 0 – для отсутствия. Формула в G2 =ЕСЛИ(ЕОШИБКА(ВПР(A2;$A$10:$A$23;1;ЛОЖЬ));0;1). В этом случае вы можете умножить цены или штуки на значение 1 или 0 и оценить, какую часть заказа можно заполнить прямо сейчас (рис. 4.5). Хотя общая стоимость заказа $13 357, в наличии на складе товаров лишь на $7115 (по-хорошему, еще не хватает оценка наличия заказанного количества, а не только конкретных товаров).

Рис. 4.5. ВПР возвращает нули и единицы, что облегчает вычисления в столбце F

Рис. 4.5. ВПР возвращает нули и единицы, что облегчает вычисления в столбце F

Объединение двух списков. Проблема: у меня есть список продаж по клиентам с начала месяца. Мой коллега только что прислал мне список продаж за вчерашний день. Мне нужно совместить и объединить эти списки.

Рис. 4.6 Необходимо объединить эти списки

Рис. 4.6 Необходимо объединить эти списки

Задачу можно выполнить в три этапа (рис. 4.7):

  • Сначала с помощью ВПР определите, появились ли новые клиенты (есть в таблице 2, но нет в таблице 1). Отсортируйте таблицу 2, собрав все #Н/Д вместе. Формула в F2 =ВПР(D2;$A$1:$B$19;1;ЛОЖЬ).
  • Добавьте новых клиентов в таблицу 1. Теперь таблица 1 включает всех клиентов, но не все продажи. Создайте дополнительный столбец в таблице 1, в который с помощью ВПР поместите новые продажи из таблицы 2. Используйте функцию ЕСЛИОШИБКА, чтобы вместо #Н/Д отразить нули. Формула в С2 =ЕСЛИОШИБКА(ВПР(А2;$D$1:$E$8;2;ЛОЖЬ);0).
  • Создайте еще один столбец в таблице 1, в котором просуммируйте месячные и новые продажи. Формула в D2 =B2+C

Рис. 4.7. Три этапа объединения данных

Рис. 4.7. Три этапа объединения данных: а) новые клиенты; б) новые продажи по всем клиентам; в) суммарные продажи по всем клиентам

Если таблица 2 и промежуточные столбцы В и С в таблице 1 вам не нужны (рис. 4.7в), скопируйте данные из D2:D22 в буфер, и вставьте их на то же место специальной вставкой (только значения). После этого таблицу 2 и столбцы В и С можно удалить.

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

Рис. 4.8. Требуется упорядочить этот список, используя формулу

Рис. 4.8. Требуется упорядочить этот список, используя формулу

Стратегия: используйте функцию РАНГ, чтобы ранжировать результаты от лучшего к худшему. На рис. 4.9 формула в А4 =РАНГ(C4;$C$4:$C$13).

Рис. 4.9. Helen имела вчера наибольший объем продаж, поэтому ее ранг равен 1

Рис. 4.9. Helen имела вчера наибольший объем продаж, поэтому ее ранг равен 1

Мы создали удобную структура для извлечения имен на основе ранга. Следующая формула вернет имя лучшего продавца =ВПР(1,$A$4:$C$13,2,ЛОЖЬ). Однако, эта формула не универсальна и при протаскивании потребуется руками менять 1 на 2, на 3 и т.д. Вместо этого в качестве первого аргумента функции ВПР можно использовать функцию СТРОКА(А1). Эта функция вернет 1, а при протаскивании вниз по столбцу, автоматически перейдет в СТРОКА(А2), которая вернет двойку и т.д. Более того, чтобы в таблице не просто вывести имена лучших торговых представителей, но и объемы продаж, в качестве третьего аргумента функции ВПР (у нас это двойка) можно использовать функцию СТОЛБЕЦ(В1), возвращающую двойку. Тогда при протаскивании вправо по строке, функция автоматически перейдет в СТОЛБЕЦ(С1), возвращающую тройку. Итак, функция в ячейке В19 (рис. 4.10) =ВПР(СТРОКА(A1);$A$4:$C$13;СТОЛБЕЦ(B1);ЛОЖЬ). Если протянуть функцию по области В19:С23, получим таблицу на рис. 4.5.

Рис. 4.10 Формула ВПР фактически сортирует данные из исходной таблицы

Рис. 4.10 Формула ВПР фактически сортирует данные из исходной таблицы

К сожалению, функция РАНГ не справится с ситуацией, если двое (или более) торговых представителей будут иметь одинаковый объем продаж (рис. 4.11). Gail и Helen имеют ранг 1, но нет никого с рангом 2. ВПР вернет ошибку при попытке найти представителя с рангом 2.

Рис. 4.11. Две пары торговых представителя имеют одинаковый объем продаж и ранг 1 и3

Рис. 4.11. Две пары торговых представителя имеют одинаковый объем продаж и ранг 1 и 3

Пожалуй, лучшее решение – это добавление в формулу, вычисляющую ранг, слагаемое которые подсчитает, сколько записей выше этой записи имеют точно такое же значение. На рис. 4.12 формула в А4 примет вид =РАНГ(C4;$C$4:$C$13)+СЧЁТЕСЛИ(C$3:C3;C4).

Рис. 4.12. Модифицированная формула ранжирования справилась с дублями

Рис. 4.12. Модифицированная формула ранжирования справилась с дублями

Еще два слова о формуле в А4. Вы хотите узнать, есть ли в любой клетке выше С4 такой же объем продаж, как и в С4. Конечно, нет, потому что единственная клетка выше С4 является заголовком. Тем не менее, вы хотите ввести эту формулу правильно, чтобы подготовиться к протаскиванию. Вы используете смешанную ссылку =СЧЁТЕСЛИ(C$3:C3;C4) (подробнее см. Относительные, абсолютные и смешанные ссылки на ячейки в Excel). В первом аргументе этой функции C$3 говорит о том, что диапазон сравнения всегда будет начинается с С3. С3 говорит, что формула будет расширять диапазон сравнения по мере протаскивания вниз. С4 говорит, что вы сравниваете значения в диапазоне со значением в текущей строке. Когда вы скопируете формулу вниз на одну строку формула в А5 станет =РАНГ(C5;$C$4:$C$13)+СЧЁТЕСЛИ(C$3:C4;C5).


Прокомментировать