Впр формулы excel: Краткий справочник: функция ВПР — Служба поддержки Майкрософт
Содержание
Excel. Облегчая использование функции ВПР
Это третья глава книги Билла Джелена. Всё о ВПР: от первого применения до экспертного уровня.
Присвойте имя вашей таблице подстановки. Проблема: моя таблица подстановки расположена на другом листе. Формула ВПР может сбивать с толку:
=ВПР(A2;’Таблица подстановки’!$A$1:$B$30;2;ЛОЖЬ)
Стратегия: присвойте имя диапазону, включающему таблицу подстановки. Для этого выделите ячейки А2:В30. Щелкните в поле имя слева от строки формул. Введите простое имя, например, Описание и нажмите Enter. Теперь формула ВПР принимает вид: =ВПР(A2;Описание;2;ЛОЖЬ). Учтите, что имя диапазона не должно содержать пробелов и начинаться с цифры (подробнее см. Excel. Имена диапазонов).
Рис. 3.1. Введите имя таблицы подстановки в поле слева от строки формул
Скачать заметку в формате Word или pdf, примеры в формате Excel
Копирование ВПР на большое число столбцов. Проблема: я использовал ВПР, чтобы извлечь данные за январь.
Мне нужно скопировать формулу в одиннадцать столбцов.
Стратегия: чтобы упростить копирование, подготовьтесь к нему:
- В окне Аргументы функции в поле Искомое_значение введите ссылку, выделите ее и нажмите <F4> три раза. Это приведет к изменению А2 до $А2. Такая смешанная ссылка позволит корректно протащить формулу по столбцам. ВПР всегда будет искать значение в столбце А.
- В окне Аргументы функции в поле Таблица введите ссылку на диапазон, выделите ее и нажмите <F4> один раз. Таблица подстановки будет иметь четыре знака доллара. Т.е., ВПР после протаскивания формулы всегда будет ссылаться на один и тот же диапазон. Или присвойте таблице подстановки имя, и используйте его в ВПР.
Наибольшая проблема – это третий аргумент. Может быть, проще всего отредактировать формулу, заменяя 2 на 3, потом на 4, на 5, и так далее. Тем не менее, позвольте предложить два более оригинальных метода:
- Используйте дополнительную строку с числами от 2 до 13.
Расположите этот ряд выше таблицы подстановки, которую вы пытаетесь построить (рис. 3.2). Затем, вместо того, чтобы указывать в качестве третьего параметра 2, дайте ссылку, на ячейку, которая вернет значение 2 – B1; выделите ссылку и нажмите <F4> два раза, чтобы изменить его на В$1. При протаскивании формулы сохранится ссылка на первую строку и соответствующий столбец (поэкспериментируйте!). - Второе решение еще более изящно. Используйте функцию СТОЛБЕЦ(В1), которая возвращает номер столбца для указанной ячейки. Поскольку В1 находится во второй колонке, функция вернет 2. Я не хочу сказать, что в мире компьютерных фанатов это простейший способ написания цифры 2. Тем не менее, преимущество заключается в том, что при копировании этой формулы вправо, ссылка автоматически изменится на С1, а функция СТОЛБЕЦ(С1) вернет 3. Этот способ позволяет обойтись без значений в строке 1.
Расположите этот ряд выше таблицы подстановки, которую вы пытаетесь построить (рис. 3.2). Затем, вместо того, чтобы указывать в качестве третьего параметра 2, дайте ссылку, на ячейку, которая вернет значение 2 – B1; выделите ссылку и нажмите <F4> два раза, чтобы изменить его на В$1. При протаскивании формулы сохранится ссылка на первую строку и соответствующий столбец (поэкспериментируйте!).Рис. 3.2. Использование дополнительной строки с числами
Рис. 3.3. Использование функции СТОЛБЕЦ()
Примечание: это совпадение, что формула в B4 ссылается на СТОЛБЕЦ(В1).
Вы используете формулу СТОЛБЕЦ(В1), так как вам нужно число 2 для выборки из таблицы подстановки, а столбец В – это второй столбец рабочего листа. Даже если в таблице на рисунке выше расположить формулу в ячейке XEG4, третий аргумент все равно остался бы СТОЛБЕЦ(В1).
Дополнительные сведения: второй способ будет замедлять работу вашей функции ВПР. Вы почувствуете это, если у вас тысячи или десятки тысяч таких формул.
Альтернативные стратегии: вы можете ускорить работу если вместо ВПР используете конструкцию из двух функций: ПОИСКПОЗ и ИНДЕКС (см. главу 5).
Преобразуйте таблицу подстановки в инструмент Excel ТАБЛИЦА. Проблема: я постоянно добавляю новые строки в нижнюю часть моей таблицы подстановки. Затем, я должен переписать формулы ВПР для включения новых строк.
Стратегия: использование инструмента ТАБЛИЦА упрощает этот процесс. ТАБЛИЦА автоматически включает в себя вновь добавленные строки и переписывать формулы ВПР не нужно.
Рассмотрим пример.
Таблица подстановки (рис. 3.4, область $F$2:$G$5) содержит только четыре строки, по одной для каждого истекшего месяца в году. Новые данные за май (D10:D11) возвращают ошибку #Н/Д, так как мая нет в таблице подстановки.
Рис. 3.4. Мая нет в таблице подстановки, поэтому в основной таблице ошибка #Н/Д
При добавлении новых данных в F6:G6, формулы в D10:D11 остаются прежними, ссылаясь лишь на $F$2:$G$5, поэтому ВПР по-прежнему возвращает #Н/Д (рис. 3.5). Однако, если вы измените в ВПР ссылку на таблицу подстановки на $F$2:$G$6 то теперь формула вернет корректный результат.
Рис. 3.5. Май всё еще не является частью таблицы подстановки, до тех пор, пока вы не перепишите формулы ВПР
Вернемся к первоначальной ситуации (см. рис. 3.4). Кликните на любую ячейку в диапазоне $F$2:$G$5 и нажмите сочетание клавиш Ctrl+Т (Т английское). Excel отображает диалоговое окно создание таблицы. Нажмите Оk (рис. 3.6). Диапазон $F$1:$G$5 автоматически отформатируется, строки получат чередование цветов, в заголовках появятся раскрывающиеся фильтры, в правом нижнем углу правой нижней ячейки ТАБЛИЦЫ появится маленький треугольник (если за него потянуть, область ТАБЛИЦЫ расширится), появится новая вкладка на ленте Excel: РАБОТА С ТАБЛИЦАМИ –> КОНСТРУКТОР (рис.
3.7).
Рис. 3.6. Определите диапазон $F$1:$G$5 в виде ТАБЛИЦЫ
Рис. 3.7. Отформатированная ТАБЛИЦА
Отметим, что на данный момент в оригинальной формуле ВПР ничего не изменилось. Ссылка на таблицу подстановки не переключилась автоматически на ТАБЛИЦУ (рис. 3.8). Отличие рис. 3.4 от рис. 3.8 в том, что во втором случае таблица подстановки превращена в ТАБЛИЦУ. Это заметно по чередованию цветов строк и раскрывающимся фильтрам.
Рис. 3.8. Хотя таблица подстановки преобразована в ТАБЛИЦУ, формула ВПР осталась той же
Однако, если вы наберете новые данные в F6:G6, ТАБЛИЦА автоматически расширится, чтобы включать в себя ряд 6. Почему-то Excel автоматически обновит формулы ВПР, и включит в них всю таблицу подстановки. Это кажется невероятным, но это так (рис. 3.9).
Рис. 3.9. Формулы ВПР автоматически изменили ссылку на расширенную таблицу подстановки
Бесплатное руководство по формулам Excel на GCFGlobal
55е88168166282983499813а
Введение
Основы формул и функций
Насадки для формул
Сценарии
Расширенный сценарий: создание счета
Викторина
Список основных формул Excel
Мелисса Эскибель
Категории: Excel®, Формулы, Функции
Теги: Список формул Excel
В Excel есть сотни функций, которые могут быть очень полезны при обработке чисел и преобразовании данных в информацию.
Вот три категории критических функций, которые каждый опытный пользователь Excel должен уметь читать и использовать.
Посмотрите эти формулы и функции в действии, загрузив: List of Excel Formulas.xlsx
Это только логическое
ЕСЛИ
Синтаксис: =ЕСЛИ(логический тест,значение, если истинно, значение, если ложно)
Как читать: Если это правда, то это, иначе это.
Использование: Для создания данных там, где их нет.
Пример: если в этом году закупки больше, чем в прошлом году, то мы хотим обозначить статус как «рост». Если покупка в этом году меньше, чем в прошлом году, она должна быть обозначена как «отказ». Наша формула могла бы выглядеть так:
И
Синтаксис: =И(логический1,логический2,логический п)
Как читать: Это верно И это есть правда И это правда
Использование: Для проверки правильности всех условий, выраженных в каждой логике.
Скажем, мы продолжаем наш пример выше. Если нам нужно, чтобы количество покупок превышало 20, а общее количество покупок превышало 50 000 долларов США, мы могли бы создать формулу в столбце C, если бы мы записали количество покупок в столбец B.
Результат покажет либо ИСТИНА, либо ЛОЖЬ. Затем мы могли бы использовать это новое значение для создания оператора IF, показывающего Y или пробел для Premium? Столбец.
ИЛИ
Синтаксис: =ИЛИ(логический1,логический2,логический n )
Как читать: Это верно ИЛИ это верно ИЛИ это верно
Использование: Для проверки правильности каких-либо условий, выраженных в каждой логике. Если мы по-новому интерпретируем значение «премиум», например, более 20 покупок или более 50 000 долларов США, это будет выглядеть так:
.
Если любое из условий истинно, значение ячейки будет ИСТИНА.
С функцией AND, описанной выше, все условия должны были быть истинными. Как зависит! Условные функции
СУММЕСЛИ
Синтаксис: =СУММЕСЛИ(диапазон,критерий,диапазон суммы)
Как читать: Какой диапазон вы хотите исследовать и по каким критериям? Когда найдено, что вы хотите суммировать?
Использование: для сложения значений в определенном диапазоне на основе определенных критериев.
Пример: у нас есть значения из разных местоположений. Мы только хотим создать сумму с теми из Детройта. Итак, наша формула будет выглядеть так:
.
От A2 до A14 мы найдем значение Detroit. Значения, которые мы хотим добавить, находятся в диапазоне от B2 до B14. Если бы мы хотели сложить все значения, превышающие 500 000 долларов, мы могли бы сделать это с помощью более простой формулы СУММЕСЛИ, потому что то, что мы хотим исследовать, это то же самое, что мы хотим добавить.
В этом случае вам не нужно указывать диапазон суммы, если он будет таким же, как диапазон. Эта формула будет выглядеть так:Вы заметили, что критерий заключен в двойные кавычки? Если ваш критерий не является одним значением или ссылкой на ячейку, он обычно должен быть заключен в двойные кавычки.
СУММЕСЛИМН
Синтаксис: =СУММЕСЛИМН(диапазон суммы,диапазон критериев1,критерий1,диапазон критериев2,критерий2,диапазон критериев n ,критерий n )
Как читать: Сложите этот диапазон, если каждый из этих диапазонов критериев удовлетворяет соответствующие им критерии.
Использование: Для выполнения SUMIF с более чем одним набором критериев. Например, если мы расширим наш пример выше, включив в него оба набора критериев, наша формула СУММЕСЛИМН будет выглядеть так:
.
В этом случае необходимо указать от B2 до B14, чтобы оценить, превышают ли значения $500 000, даже если это также диапазон суммы.
Очистка с помощью текстовых функций
ТЕКСТ
Синтаксис: =ТЕКСТ(значение,формат текста)
Как читать: Поместите это числовое значение в этот текстовый формат.
Использование: для решения проблемы с нулевыми значениями, заполненными спереди, например, с некоторыми почтовыми индексами.
Пример: исходный столбец почтового индекса отформатирован с использованием специального формата, называемого почтовым индексом. Однако, когда вы пытаетесь использовать этот столбец в слиянии, вы, скорее всего, получите значения в столбце «Фактическое значение», в котором будут отсутствовать нули перед последним почтовым индексом. Применив текстовую функцию и указав формат «00000», слияние фактически будет использовать нулевой номер, заполненный в начале.
Обратите внимание на двойные кавычки. Они необходимы для того, чтобы Excel распознал формат.
TRIM
Синтаксис: =TRIM(текст)
Как читать: Удалите все начальные и конечные пробелы из этого значения, оставив только одиночные пробелы между словами.
