Это шестая глава книги Билла Джелена. Всё о ВПР: от первого применения до экспертного уровня.

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

Рис. 6.1. Комиссионный процент в зависимости от объема продаж

Подробнее »Excel. Продвинутый поиск с помощью ВПР и не только

Это пятая глава книги Билла Джелена. Всё о ВПР: от первого применения до экспертного уровня.

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

Для меня функция ИНДЕКС совершенно непонятна. Проблема: я недавно прочитал справку Excel по этой функции. Кто в здравом уме будет использовать =ИНДЕКС(В4:G22;2;4), чтобы указать на ячейку F6 (рис. 5.1)?

Рис. 5.1. Функция ИНДЕКС кажется бесполезной

Подробнее »Excel. Функции ИНДЕКС и ПОИСПОЗ

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

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

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

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

Это третья глава книги Билла Джелена. Всё о ВПР: от первого применения до экспертного уровня.

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

=ВПР(A2;’Таблица подстановки’!$A$1:$B$30;2;ЛОЖЬ)

Стратегия: присвойте имя диапазону, включающему таблицу подстановки. Для этого выделите ячейки А2:В30. Щелкните в поле имя слева от строки формул. Введите простое имя, например, Описание и нажмите Enter. Теперь формула ВПР принимает вид: =ВПР(A2;Описание;2;ЛОЖЬ). Учтите, что имя диапазона не должно содержать пробелов и начинаться с цифры (подробнее см. Excel. Имена диапазонов).

Рис. 3.1. Введите имя таблицы подстановки в поле слева от строки формул

Подробнее »Excel. Облегчая использование функции ВПР

Это вторая глава книги Билла Джелена. Всё о ВПР: от первого применения до экспертного уровня. Раз вы читаете эту главу, видимо, ваша ВПР возвращает ошибку #Н/Д. Этот симптом может быть вызван одной из нескольких проблем. Чтобы найти лекарство, посмотрите, что из следующего применимо к вашему случаю:

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

Разрозненные #Н/Д. Проблема: отдельные ВПР выдают ошибки #Н/Д (рис. 2.1).

Рис. 2.1. BG33-9 – это новый товар, которого нет в таблице подстановки

Подробнее »Excel. Устранение проблем с функцией ВПР

Билл Джелен – MVP Microsoft, автор целого ряда книг по Excel (см., например, Сводные таблицы в Microsoft Excel 2013), ведущий сайта http://mrexcel.com/. Мне нравится, как он пишет, а также мне нравится его сайт, на котором можно за разумные деньги приобрести интересные книги. Ранее я опубликовал перевод книги Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel, приобретенной на сайте MrExcel.com. Сегодня представляю перевод небольшой книги, посвященной всецело одной функции – ВПР. Надо отметить, что это действительно мощная и интересная функция Excel, которую я использую в своей работе очень широко. И я уже писал о ней – см., например, Использование функции ВПР для извлечения данных из таблицы с двумя параметрами.

Bill ‘MrExcel’ Jelen. VLOOKUP Awesome Quick: From Your First VLOOKUP to Becoming a VLOOKUP Guru (Билл Джелен. Всё о ВПР: от первого применения до экспертного уровня). – Holy Macro! Books, 2012 – 74р.

Подробнее »Билл Джелен. Всё о ВПР: от первого применения до экспертного уровня

Возможно, вам приходилось работать с листами, в которых использовалась, формула типа: =СУММ(А5000:А5078). Вы гадали, что же находится в ячейках А5000:А5078!? Если в ячейках А5000:А5078 содержатся объемы продаж по регионам, не кажется ли вам формула =СУММ(ПродажиРегионы) более понятной? В данной главе описываются способы присвоения имен отдельным ячейкам и диапазонам ячеек, а также способы вставки имен диапазонов в формулы. [1]

Как создать именованный диапазон?             

Существуют три способа создания именованных диапазонов:

• путем ввода имени диапазона в поле Имя;
• путем выбора на вкладке ФОРМУЛЫ в группе Определенные имена инструмента Создать из выделенного;
• путем выбора на вкладке ФОРМУЛЫ в группе Определенные имена инструментов Присвоить имя или Диспетчер имен.

Для создания имени диапазона с помощью поля Имя (рис. 1.1) выделите ячейку или диапазон ячеек, которым требуется присвоить имя, установите курсор в поле Имя, введите имя диапазона, и нажмите клавишу <Enter>. На рис. 1.1 ячейке В3 присвоено имя Старт.

Рис. 1.1. Создание имени диапазона путем выбора диапазона ячеек и ввода имени в поле Имя

Подробнее »Excel. Имена диапазонов