Советы и рекомендации для Excel — поддержка Office. Excel впр

Советы и рекомендации для Excel — поддержка Office

Мгновенное заполнение ячеек

Excel

\r\n \r\n

Функция \"Мгновенное заполнение\" экономит ваше время при вводе текста.

\r\n

Просто начните вводить текст в столбце справа от данных. В этом примере мы ввели Кузнецов, а затем Артем. После этого приложение Excel волшебным образом предложило варианты заполнения остальных ячеек. Чтобы принять эти предложения, нажмите клавишу ВВОД, и все будет готово.

\r\n

\r\n Другие способы заполнения\r\n

\r\n \r\n \r\n \r\n \r\n","App":"Excel","AssetId":"1a2adcad-4bae-444f-8fe1-c733e60c24f3","Category":null}

Определяйте итоги без формул

Excel

\r\n \r\n

Совет по работе с таблицами. Чтобы добавить строку итогов, не вводя формулу, просто щелкните в любом месте таблицы Excel. Затем нажмите клавиши CTRL+SHIFT+T, и Excel добавит для вас строку итогов.

\r\n

\r\n Подробные сведения о таблицах\r\n

\r\n \r\n \r\n \r\n \r\n","App":"Excel","AssetId":"d0d78148-55bb-47e1-a777-dd2d74bc7fd1","Category":null}

Визуализируйте данные в таблицах

Excel

Где та кнопка?.. Спросите помощника

Excel

Сводить или не сводить?

Excel

Яркое и эффектное представление данных с помощью гистограмм

Excel

Настраиваемая фильтрация данных

Excel

Построение диаграммы: просто как ALT+F1

Excel

ЕСЛИМН — функция для самых требовательных аналитиков

Excel

Практичные шаблоны со специальными возможностями

Excel

Возможность закрепления без необходимости прокрутки

Excel

Мгновенное создание сводных таблиц без подготовки

Excel

Функция ВПР поможет найти иголку в стоге сена

Excel

Каков ваш статус? Узнавайте его с первого взгляда

Excel

support.office.com

Функция Excel ВПР (VLOOKUP) и ГПР (HLOOKUP) с примерами использования

Добрый день, многоуважаемые читатели блога statanaliz.info.

В данной публикации рассмотрим крайне полезною функцию Excel - ВПР. Формула настолько полезна, что может служить индикатором навыков работы в Excel. Кто не умеет пользоваться ВПР, тот находится на низшей ступени развития, питекантроп фактически. Шутка. На самом деле функция ВПР не очень известная новичкам, но в то же время очень распространена среди бывалых юзеров (и лузеров) Excel. И вот почему.

Кто часто использует Excel, и не только в качестве калькулятора, наверняка сталкивался с ситуацией, когда данные из одной таблички по какому-то характерному признаку нужно перенести в другую табличку, где присутствует тот же характерный признак. Например, складские остатки товаров из одной таблички необходимо поставить рядом с продажами соответствующих товаров в другой табличке и т.п. Такая задача довольно-таки распространена. Я лично сталкиваюсь с подобным по несколько раз в день, иногда даже в час.

Давайте теперь посмотрим, что такое ВПР в целом, что она умеет, как работает и где следует применять. Формула ВПР по некоторому ключевому полю умеет «подтягивать» данные из одного места Эксель в другое. Диапазоны данных могут находиться где угодно: на одном листе, на разных листах и даже в разных книгах Excel. Собственно, как и любая другая функция. Ключевое поле должно присутствовать в обоих диапазонах данных (и там, куда «подтягиваем», и там, откуда берем данные).

Пример использования функции ВПР

Работу формулы ВПР предлагаю рассмотреть на примере. Для этого представим, что перед нами стоит задача определить стоимость проданных товаров. Стоимость рассчитывается, как произведение количества и цены. Сделать это очень легко, если количество и цены находятся в соседних колонках. Достаточно в пустом столбце рядом прописать формулу произведения двух ячеек и «протянуть» вниз до конца перечня товаров. Однако данные могут быть представлены не в столь удобном виде. Например, исходная информация может находиться в совершенно разных таблицах (диапазонах данных) и в другом порядке. В первой таблице указаны количества проданных товаров:

Во второй – данные о ценах:

Если бы перечень товаров в обеих таблицах совпадал, то, зная магическое сочетание Ctrl+C и Ctrl+V, данные о ценах можно было бы легко подставить к данным о количестве. Однако очередность позиций в обеих таблицах не совпадает. Тупо скопировать цены и подставить к количеству не получится – наименования товаров не совпадут.

Данные по многим товарам не соответствуют друг другу. Поэтому мы не можем прописать формулу умножения и «протянуть» вниз на все позиции.

Что делать? Надо как-то цены из второй таблицы подставить к соответствующему количеству в первой, т.е. цену товара А к количеству товара А, цену Б к количеству Б и т.д.

Вот так.

Есть любители делать подобную операцию вручную, однако данную задачу элегантно решает функция ВПР. Как она работает? Собственно, легко, хотя может быть и не совсем понятно с первого раза. Итак, нам нужно цены из второй таблицы «подтянуть» к количеству в первую таблицу в полном соответствии с наименованием товара.

Добавим вначале в первую таблицу новый столбец, в котором будут проставлены необходимые цены посредством функции ВПР.

Формулу ВПР, как и любую другую, можно вызвать с помощью Мастера функций, а можно и вручную прописать. Рассмотрим оба варианта, хотя конечный результат будет одинаковым. Каждый выбирает, как ему удобно. Для вызова функции с помощью Мастера нужно активировать ячейку, где будет прописана формула и нажать кнопку f(x) в самом начале строки формул. Появится диалоговое окно Мастера, где из списка всех функций нужно выбрать ВПР.

Кликаем по надписи «ВПР». Открывается следующее диалоговое окно.

Теперь нужно заполнить предлагаемые поля. В первом окошке «Искомое_значение» нужно указать критерий для ячейки, в которую мы вписываем формулу. В нашем случае это ячейка с наименованием товара «А».

Следующее поле «Таблица». В нем нужно указать диапазон данных, где будет осуществляться поиск нужных значений. В нашем случае это вторая таблица с ценой. Ведь именно цену мы «подтягиваем» к количеству. При этом нужно понимать важную особенность. Крайний левый (то есть первый слева) столбец выделяемого диапазона должен содержать те самые критерии, по которым осуществляется поиск, то есть это должен быть столбец с наименованиями товаров. Затем таблица выделяется вправо минимум до того столбца, где находятся искомые значения (цены). Можно и дальше вправо выделить, но это уже ни на что не влияет, так как столбец с искомыми значениями будет однозначно определен следующим параметром. Главное, чтобы выделенная таблица начиналась со столбца с критериями и захватывала нужный столбец с данными.

Следующее поле «Номер_столбца» - это число, на которое столбец с искомыми данными (ценами) отстоит от столбца с критерием (наименованием товара) включительно. То есть отсчет идет, начиная с самого столбца с критерием. Если у нас во второй таблице оба столбца находятся рядом, то нужно указать число 2 (первый – критерий, второй - цены). Часто бывает, что данные отстоят от критерия на 10 или 20 столбцов. Это не важно, Excel все сосчитает.

Последнее поле «Интервальный_просмотр», где указывается тип поиска: точное (0) или приблизительное (1) совпадение критерия. Сейчас ставим 0 (или ЛОЖЬ).

Все, поля заполнены и выглядят примерно так.

Нажимаем ОК или «Ввод». Если все правильно и значение критерия есть в обеих таблицах, то на месте только что введенной формулы появится некоторое значение. Остается только протянуть (или просто скопировать) формулу вниз до последней строки таблицы.

Собственно, все, расчет готов. Теперь можно рассчитать стоимость с помощью простого умножения количества на цену, а формулу затем протянуть вниз до конца таблицы, так как пары цена-количество теперь совпадают.

Формулу ВПР можно прописать непосредственно в ячейке, как я обычно и делаю. Это позволяет увеличить скорость работы в Excel. Как ни странно, написать формулу вручную получается быстрее, чем заполнить кучу полей в Мастере функций. Тут все почти тоже самое, только между параметрами нужно ставить знак точки с запятой «;». Так выглядит та же формула, написанная пальчиками.

Название «впр» можно написать маленькими буквами, реестр значения не имеет.

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

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

Функция ВПР имеет свои заморочки и особенности, о которых следует помнить, дабы не сильно расстраиваться, когда вместо значений появляются ошибки.

1. Первую особенность можно считать общей для функций, которые используются для многих ячеек путем прописывания формулы в одной из них и дальнейшим копированием в остальные. Здесь нужно обращать внимание на относительность и абсолютность ссылок. Конкретно в ВПР критерий (первое поле) должно иметь относительную ссылку (без знаков $), так как у каждой ячейки свой собственный критерий. А вот диапазон должен иметь абсолютную ссылку (адрес диапазона прописывается через $, как на скриншоте Мастера функций выше). Если этого не сделать, то при копировании формулы диапазон «поедет» вниз и многие значения просто не найдутся, так как искать будет негде.

2. Номер столбца, указываемый в третьем поле «Номер_столбца» при использовании Мастера функций, должен отсчитываться, начиная с самого критерия. Ну, это я уже говорил.

3. Если в таблице, где происходит поиск данных, нет искомого критерия, то выдается ошибка #Н/Д. Данная особенность многих раздражает, так как в этом случае трудно подсчитать итоги (сумму, среднюю и др.). Проблему можно обойти разными способами. Например, с помощью функций СУММЕСЛИ (вместо ВПР) или ЕСЛИОШИБКА (установить перед ВПР).

4. Если в качестве критерия используются числовые значения (числовые коды, артикулы и проч.), то формула ВПР чувствительна к формату ячейки! Если в одной таблице критерий будет в числовом формате, а в другой в текстовом, то даже при полном совпадении значений функция выдаст ошибку #Н/Д. Данное обстоятельство многих ставит в тупик и заставляет думать, что ВПР не работает. На самом деле пугаться не нужно. Часто достаточно проверить совпадение формата полей с критерием и, если надо, сделать их одинаковыми. Либо опять же использовать функцию СУММЕСЛИ – ей формат по барабану.

5. Еще одно замечание на счет поля с критерием. Практика подсказывает, что значения критерия должны быть не очень длинными, что бы максимально уменьшить вероятность «случайных» различий. К примеру, лишний пробел между словами или одна неправильная буква делает, казалось бы, одинаковые значения критерия несопоставимыми. Поэтому артикул или штрихкод товара, вполне годятся, а вот название в несколько слов для критерия лучше не использовать.

6. Функция ВПР из диапазона с искомыми данными выдает первое сверху значение. Это значит, что, если во второй таблице, откуда мы пытаемся «подтянуть» некоторые данные, присутствуют несколько ячеек с одинаковым критерием, то в рамках выделенного диапазона ВПР захватит первое сверху значение. Об этом следует помнить. К примеру, если мы хотим к цене товара подтянуть количество из другой таблицы, а там этот товар встречается несколько раз (в нескольких строках), то к цене подтянется первое сверху количество. Остальные проигнорируются.

7. Последний параметр формулы, который 0 (нуль), ставить нужно обязательно. Иначе формула может криво работать.

8. После использования ВПР саму формулу лучше сразу удалить, оставив только полученные значения. Делается это очень просто. Выделяем диапазон с полученными значениями, нажимаем «копировать» и на это же место с помощью специальной вставки вставляем значения. Если таблицы находятся в разных книгах Excel, то очень удобно разорвать внешние связи (оставив вместо них только значения) с помощью специальной функции, которая находится по пути Данные → Изменить связи.

После вызова функции разрывания внешних связей появится диалоговое окно, где нужно нажать кнопку «Разорвать связь» и затем «Закрыть».

Это позволит удалить сразу все внешние ссылки. Полезная функция, возьмите на заметку.

Чуть не забыл. Есть еще одна похожая функция – ГПР. Отличие в том, что ГПР «просматривает» список данных по горизонтали. Сам никогда не пользовался, но чисто теоретически все то же самое.

Напоследок предлагаю посмотреть видеурок об использовании функции ВПР.

На сегодня все. Всех благ и до новых встреч на блоге statanaliz.info.

statanaliz.info

Исправление ошибки #Н/Д в функции ВПР

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке) .

В этом разделе описываются наиболее распространенные причины ошибочный результат в функции ВПР и рекомендации вместо использования функций индекс и ПОИСКПОЗ .

Проблема: искомое значение не находится в первом столбце аргумента таблица

Одно ограничение ВПР — что он только можно искать значения в крайнем левом столбце в массиве таблицы. Если искомое значение в первом столбце массива не, появляется ошибка # н/д.

В следующей таблице нам нужно узнать количество проданной капусты.

Ошибка #Н/Д в функции ВПР: искомое значение не находится в первом столбце массива таблицы

Так как искомое значение «Kale» появляется во втором столбце (продукты) для аргумента A2:C10 приводит к ошибке # н/д. В этом случае Excel найти его в столбце A, столбец B.

Решение: можно попытаться устранить эту ошибку настраивая вашей ВПР для ссылки на правильный столбец. Если это невозможно, попробуйте переместить столбцов. Может быть также настоятельно нецелесообразной, если у вас крупный или сложный электронные таблицы Каковы результаты вычислений других значений ячеек, или может быть возникли другие логические причины, почему вы просто нельзя перемещаться столбцы. Чтобы решить, используйте сочетание функций индекс и ПОИСКПОЗ, которые можно найти значение в столбце независимо от его положение расположение в таблицу подстановки. Смотрите следующий раздел.

Вместо графика рекомендуется использовать индекс и ПОИСКПОЗ

Индекс и ПОИСКПОЗ предлагаются хорошо для многих случаях, в которых функция ВПР не отвечает вашим требованиям. Главное преимущество индекс и ПОИСКПОЗ — можно искать значения в столбце в любом месте таблицы подстановки. Индекс возвращает значение из указанного таблица или диапазон, согласно его положение. ПОИСКПОЗ Возвращает относительное положение значения в таблицу или диапазон. Использование функций индекс и ПОИСКПОЗ вместе в формуле для поиска значения в таблице и массива, указав относительное положение значения из таблицы или массива.

Существует несколько преимуществ индекс и ПОИСКПОЗ вместо функции ВПР.

С помощью функций индекс и ПОИСКПОЗ, возвращаемое значение не обязательно в том же столбце как столбец подстановки. Это отличается от функции ВПР, в котором должен быть в указанном диапазоне возвращаемое значение. Как это имеет значение? В функции ВПР вам нужно знать номер столбца, содержащий возвращаемое значение. Не может показаться сложным, он может быть громоздкими при наличии больших таблиц и подсчитать количество столбцов. Кроме того Если вы Добавление и удаление столбца в таблице, у вас есть пересчет и обновить значение аргумента номер_столбца . С помощью функций индекс и ПОИСКПОЗ не считая необходим как столбец подстановки отличается от столбец, содержащий возвращаемое значение.
С индекс и ПОИСКПОЗ, вы можете указать строки или столбца в массиве, или указать оба. Это означает, что вы можете поиск значений по горизонтали и по вертикали.
Можно использовать для поиска значений в любом столбце индекс и ПОИСКПОЗ. В отличие от функции ВПР, в котором вы можете только искать значения в первом столбце таблицы — индекс и ПОИСКПОЗ будет работать в случае искомое значение в первом столбце, последней или в любом месте между ними.
Индекс и ПОИСКПОЗ позволяют сделать динамическая ссылка на столбец, содержащий возвращаемое значение. Это означает, что можно добавлять столбцы в таблицу не нарушая индекс и ПОИСКПОЗ. С другой стороны, разрывы ВПР, если вам нужно добавить столбец в таблице, поскольку оно обеспечивает статическую ссылку в таблицу.
Индекс и ПОИСКПОЗ предлагает гибкость с совпадения. ИНДЕКС и ПОИСКПОЗ можно найти точное совпадение или значение, которое больше или меньше, чем искомое значение. Функция ВПР будет искать только подходящие для точного значения или значения (по умолчанию). Функция ВПР также предполагается, что по умолчанию в первом столбце массива таблицы сортируются в алфавитном порядке, что предположим, что таблицы не настроен таким образом, ВПР вернет близкие первой таблицы, которая не может быть данные, которые вы ищете.

Синтаксис

Для создания синтаксис для индекс и ПОИСКПОЗ, необходимо использовать массива/ссылочный аргумент от функций индекс и вложить синтаксис соответствия внутри его. Это представляются в виде:

= INDEX(array or reference, MATCH(lookup_value,lookup_array,[match_type])

Чтобы заменить ВПР в приведенном выше примере воспользуемся индекс и ПОИСКПОЗ. Синтаксис будет выглядеть следующим образом:

=ИНДЕКС(C2:C10;ПОИСКПОЗ(B13;B2:B10;0))

Что означает:

= Индекс (возврат значения из C2:C10, которому будут СООТВЕТСТВОВАТЬ (Kale, которая находится там, где в массиве B2: B10, в котором возвращает значение первое значение, соответствующее Kale))

Функции ИНДЕКС и ПОИСКПОЗ можно использовать вместо функции ВПР

Формула ищет в C2:C10 первое значение, соответствующее значению Капуста (B7), и возвращает значение в ячейке C7 (100).

Проблема: не найдено точное совпадение

Если аргумент Интервальный_просмотр имеет значение ЛОЖЬ, и ВПР не удается найти точное совпадение в данных, возвращается ошибка # н/д.

Решение: Если вы уверены, соответствующие данные содержатся в электронной таблице и ВПР не перехвата его, длиться проверить, правильно ли ссылок на ячейки не скрытых пробелов или непечатаемые символы. Кроме того обеспечение следования правильный тип данных ячейки. Например ячеек, содержащих числа должно отформатировано как число, а не текст.

Кроме того можно выполнить очистку данных в ячейках с помощью функции ПЕЧСИМВ или СЖПРОБЕЛЫ .

Проблема: искомое значение меньше, чем наименьшее значение в массиве

Если аргумент Интервальный_просмотр имеет значение ИСТИНА, и искомое значение меньше, чем наименьшее значение в массиве — появляется ошибка # н/д. TRUE выглядит приблизительно соответствуют массива и возвращает ближе значение меньше, чем искомое значение.

В приведенном ниже примере искомое значение равно 100, но в диапазоне B2:C10 нет значений меньше 100, поэтому возникает ошибка.

Ошибка #Н/Д в функции ВПР, если искомое значение меньше, чем наименьшее значение в массиве

Решение.

Исправьте искомое значение.
Если не удается изменить искомое значение и вам большей гибкости с совпадающими значениями, рекомендуется использовать индекс и ПОИСКПОЗ вместо функции ВПР, обратитесь к разделу выше в этой статье. С индекс и ПОИСКПОЗ можно искать значения больше, чем меньше для или равно искомое значение. Дополнительные сведения об использовании индекс и ПОИСКПОЗ вместо ВПР обратитесь к предыдущему разделу в этой статье.

Проблема: столбец подстановки не отсортирован в порядке возрастания

Если аргумент Интервальный_просмотр имеет значение ИСТИНА, и один из столбцов подстановки не отсортированы по возрастанию (A-Z) — появляется ошибка # н/д.

Решение.

Изменение функции ВПР для поиска точного совпадения. Чтобы сделать это, задайте для аргумента Интервальный_просмотр значение FALSE. Нет сортировки необходим значение ЛОЖЬ.
Для поиска значения в несортированной таблице можно также использовать функции ИНДЕКС и ПОИСКПОЗ.

Проблема: значение является большим числом с плавающей запятой

При наличии значения времени или больших десятичных чисел в ячейках Excel возвращает ошибку # н/д из-за точность число с плавающей запятой. Число с плавающей запятой являются чисел, которые следуют за после десятичной запятой. (Excel значения времени сохраняются в виде числа с плавающей запятой.) Excel нельзя хранить чисел с очень большими плавающая запятая, поэтому для правильной работы функции, плавающей запятой числа нужно округляется до 5 десятичных разрядов.

Решение. Округлите числа до 5 десятичных разрядов с помощью функции ОКРУГЛ.

У вас есть вопрос об определенной функции?

Задать вопрос на форуме сообщества, посвященном Excel

Помогите нам улучшить Excel

У вас есть предложения по улучшению следующей версии Excel? Если да, ознакомьтесь с темами на портале пользовательских предложений для Excel.

См. также

support.office.com