Excel. Использование функций СМЕЩ и ДВССЫЛ для поиска

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

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

Функции СМЕЩ и ДВССЫЛ являются весьма сложными для восприятия. Настоящая заметка и примеры, содержащиеся в ней, должны позволить вам освоить эти функции. См. также ссылки на дополнительный материал в конце заметки.

Суммирование по диапазону, размер которого вычисляется в ячейке. Проблема: мне нужно просуммировать значения в диапазоне, начинающемся в ячейке A5, а количество строк диапазона суммирования задано в ячейке С5 (рис. 7.1).

Стратегия: использовать подвижную (гибкую) функцию СМЕЩ, которая позволит вам:

  • Начать отсчет из конкретной ячейки или диапазона ячеек;
  • Сместить начало отсчета на заданное число строк и столбцов;
  • Задать число строк и столбцов диапазона, считая от нового начала отсчета.

Рис. 7.1. Эта формула суммирует значения в диапазоне А5_А9

Рис. 7.1. Эта формула суммирует значения в диапазоне А5:А9

Читать полностью

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

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

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

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

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

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

Читать полностью

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

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

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

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

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

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

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

Читать полностью

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

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

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

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

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

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

Читать полностью

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

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

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

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

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

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

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

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

Читать полностью

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

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

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

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

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

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

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

Читать полностью

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

Рубрика: 7. Полезняшки 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р.

Джелен. Всё о ВПР. Обложка

Читать полностью

Excel. Имена диапазонов

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

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

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

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

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

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

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

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

Читать полностью

Уэйн Л. Винстон. Microsoft Excel 2013. Анализ данных и бизнес-моделирование

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

Эта книга может быть полезна, как начинающим, так и более опытным пользователям. Она – не полное руководство по Excel, а освещает избранные темы. По какому принципу автор отбирал эти темы, для меня осталось загадкой. Но затронутые темы описаны интересно, глубоко, с разнообразными практическими примерами (естественно, американскими :)). Представлены новейшие инструменты Excel, появившиеся в версиях 2010 и 2013 гг. Рассмотрены функции ИНДЕКС, ПОИСПОЗ, ВПР, СУММЕСЛИ, СМЕЩ, ДВССЫЛ и многие другие. Рассказывается о множественной регрессии, экспоненциальном сглаживании, статистических функциях, функциях даты и времени. Рассмотрено построение наглядных диаграмм, решение сложных задач оптимизации, имитационное моделирование по методу Монте-Карло.

Винстон Уэйн Л. Microsoft Excel 2013. Анализ данных и бизнес-моделирование. – М.: Издательство «Русская редакция»; СПб.: «БХВ-Петербург», 2015. – 864 с. [1]

Винстон. MS Excel 2013. Анализ данных и бизнес-моделирование. Обложка

Читать полностью

Частичная защита листа Excel

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

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

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

Рис. 1. Окно настройки параметров защиты листа

Рис. 1. Окно настройки параметров защиты листа

Читать полностью