Mini-ats102.ru

ООО “Мультилайн”
5 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Excel works

Функция ВПР — это один из самых полезных компьютерных трюков не только в Excel. Кто узнал о нем, тот без нее жить не может, серьезно. Итак, предположим, что у нас есть две таблицы с текстом. Нужно значения одного списка (Фамилии) передать в ячейки другого, в зависимости от текста-условий (Номера ТС). Если конкретнее пример ниже:

ВПР

Задача1. В одном файле хранится список ФИО сотрудников и транспортных средств (Таблица1). В Таблице2 для некоторых автомобилей заполнены номера накладных. Причем таблицы не совпадают по количеству строк. Цель. Для каждой строки Таблицы2 заполнить ФИО сотрудников. Для этого как раз пригодится функция ВПР.

Функция ВПР. Решение задачи

Прежде чем решать задачу, я познакомлю вас подробнее с этой мощнейшей функцией.

Функция ВПР ищет значение в левом столбце Таблицы1 и возвращает (записывает) значение ячейки, находящейся в столбце Таблицы2 под определенным номером, той же строки. Во как 🙂 Но на самом деле все проще. Для этого нам нужно понять из чего состоит ВПР

Переменные. Функция ВПР

  • Искомое_значение — то самое значение, которое мы ищем в левом столбце Таблицы1. Номер ТС в Таблице2.
  • Таблица — все столбцы Таблицы1, причем первый столбец должен быть, который мы ищем (Номер ТС)
  • Номер_столбца — номер столбца в Таблице1, из которого возвращаем значения (ФИО)
  • [интервальный_просмотр] — может принимать только два значения — Ложь или Истина: Ложь – ищет точное совпадение, Истина – приблизительное. В 95% случаев требуется искать точное значение, т.е. выбирать ЛОЖЬ.

Использование ВПР в Excel и решение примера

Итак, напишем функцию для нашей задачи:

Функция ВПР в Excel

E:E — это диапазон значений, по которым Excel будет сравнивать условия с Таблицей 1. A:B — вся Таблица1 (обязательно, чтобы первым столбцом был столбец для поиска условий). Число 2 это тот по счету столбец в Таблице 1, который мы будем переносить в ячейку F1. ЛОЖЬ — смотрите выше.

Я тысячу раз слышал как люди говорят: «Давайте заВПРим это» или «ну тут можно ВПРом сделать», и это отлично! Значит люди экономят время, зная о простых и действенных методах. Не отставайте!

Не забудьте растащить функцию до конца диапазона. Не знаете как это сделать быстро? Читайте тут.

А как можно сделать ту же функцию с удобными названными диапазонами (на картинке ниже)? Нравится? Читайте тут.

Функция ВПР в Excel

Комментарии по ВПР

  • Если для одной ячейки в Таблице2 есть несколько значений, в Таблице1 будет выдаваться первое значение.
  • Если совпадающих данных нет, будет возвращаться ошибка «не найдено» (Н/Д#). Чтобы ее не отображать, используйте формулы =ЕСЛИОШИБКА()
  • Чтобы определить какой именно номер столбца записать в формулу, при выделении диапазона показывается, номер столбца.

Номер столбца

  • Чтобы посчитать сумму для нескольких значений из одной таблицы в другой, воспользуйтесь СУММЕСЛИ

Что в итоге:

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

Как выглядит синтаксис ВПР?

Синтаксис функции в Excel – это набор параметров, с помощью которых её можно вызвать и задать. Запись аналогична с методом записи математических функций.

Читайте так же:
Как вернуть биос в исходное состояние

Посмотреть правильный вид опции можно, открыв табличный процессор:

  • Используйте уже созданный документ, либо откройте новый пустой лист;
  • Кликните на клавишу «Формулы» , как показано на рисунке ниже;
  • В строке поиска напечатайте «ВПР» или «VLOOKUP» в зависимости от языка программы;
  • Настройте категорию «Полный перечень» ;
  • Кликните на «Найти» .

впр в excel примеры

Рис.2 – поиск формул в Эксель

В результате поиска формулы вы увидите её нахождение в перечне. Нажав на элемент, внизу экрана отобразится его формула.

За скобками указывается название функции, а внутри скобок – её параметры. Внутри формулы каждый отдельный параметр прописывается в угловых <> скобах.

Общий вид описания для ВПР выглядит так:

впр в excel примеры

Рис.3 – перечень параметров

Рассмотрим детальнее каждое из значений, которое описывается в скобках:

  • <ЧТО> — первый элемент. Вместо него вам нужно прописать именно то значение, которое вы хотите найти в таблице. Также, можно вписывать адрес ячейки в таблице;
  • <НОМЕР_СТОЛБЦА> — тут нужно напечатать номер столбика, в рамках которого будет осуществляться перебор данных.
  • <ГДЕ> — здесь юзер определяет количество ячеек, задавая их размерность в виде двумерного массива данных. Первый столбик – это элемент «ЧТО»;
  • <ОТСОРТИРОВАНО> — этот элемент функции ВПР отвечает за сортировку первого столбца по возрастанию (первый столбик для «ГДЕ»). В результате успешной сортировки, значение становится истинным (единица). Если возникают какие-либо неточности или ошибки во время ввода параметров – появляется ложное значение сортировки (ноль). Стоит заметить, что во время задания ВПР <ОТСОРТИРОВАНО> можно пропустить, и тогда его значение по умолчанию принимается как истина.

Читайте также:

Использование функции ВЫБОР для создания новой таблицы просмотра

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

Использование функции ВЫБОР подразумевает создание новой таблицы для просмотра, в котором значения столбцов Месяц и Город уже объединены. Наша формула будет выглядеть следующим образом:

Основной момент данной формулы заключается в части ВЫБОР(<1;2>;B2:B13&C2:C13;D2:D13), который делает две вещи:

  1. Объединяет значения столбцов Месяц и Город в один массив: ЯнвМосква, ФевМосква …
  2. Объединяет два массива в таблицу, состоящую из двух столбцов.

Результатом работы данной функции будет таблица, которая выглядит следующим образом:

Массив-для-просмотра

Теперь формула стала более понятной.

ВАЖНО: Так как мы использовали формулу массива, по окончании ввода формулы нажмите Ctrl+Shift+Enter, чтобы дать знать программе о наших намерениях. После нажатия данной комбинации клавиш, программа автоматически установит фигурные скобки в начале и в конце формулы.

ВПР не работает

Как работает ВПР в Excel читайте в посте “Функция ВПР – чудо экономии времени”. Здесь же вы узнаете, что делать, если ВПР не работает. Так бывает. Скорее всего вы допустили ошибку. Есть четыре наиболее распространенных варианта ошибок:

  1. Ошибка Н/Д в каждой ячейке.
  2. Ошибка Н/Д преимущественно в нижней части таблицы.
  3. Ошибка Н/Д в отдельных ячейках.
  4. Ошибка #ССЫЛКА.

впр excel ошибки

А теперь разберем каждый вариант подробнее.

Читайте так же:
Как в фотошопе сделать фон другого цвета

Вариант 1. ВПР вернула ошибку #Н/Д во всех ячейках

  • Вы неправильно выделили диапазон поиска – начали не с того столбца:

впр excel ошибка #Н/Д

Все будет в порядке, как только вы исправите ошибку:

=впр(B3;'[прайс-лист.xlsx]Лист1′! $C$3:$E$7 ;3;ложь)

  • Не совпадает текст, по которому ВПР ищет данные.

Например, в одном файле текст “Компьютер AF”, в другом файле “КомпьютерAF”. Если не заметили, разница всего лишь в наличии дефиса во втором случае. Тем не менее, ВПР не считает такое расхождение мелочью и, следовательно, не сработает.

Единственное исключение – допускаются разные регистры. Например, “Компьютер AF” и “компьютер af” не составит проблемы для ВПР.

Идентичность текста удобно проверять функцией =ЯЧЕЙКА=ЯЧЕЙКА. Смотрите пример на рисунке ниже и описание под ним:

excel проверка ячеек

  1. Введите в ячейку D2 знак “=”
  2. Выделите ячейку B2 с первым значением Планшет DC.
  3. Опять введите “=”.
  4. Выделите ячейку C2 со вторым значением.
  5. Кликните Enter и протяните формулу вниз.

В результате в ячейках отобразится либо “ИСТИНА” (значения совпадают), либо “ЛОЖЬ” (значения не совпадают).

Как видите, не совпадают значения в строке 4: “Компьютер А” и “Компьютер F” (в ячейке D4 видим слово “ЛОЖЬ”).

Использовать функцию можно как в одном, так и в разных файлах.

Что можно предпринять, если текст не совпадает?

  1. Если вы регулярно работаете с этими файлами, поменяйте текст вручную. Единожды потратив на это время, вы значительно сэкономите его в будущем. Если смена текста невозможна, создайте дополнительный столбец, куда введете текст из второго файла.
  2. Иногда текст не совпадает из-за каких-то наборов знаков, которые повторяются в каждой ячейке. К примеру, в одном из файлов везде добавлен артикул: “010-01583 Компьютер AF”, а в другом файле этого артикула нет “Компьютер AF”. В этом случае текст можно заменить автоматически. Об этом читайте в следующих постах.
  • В одном из файлов в тексте содержатся невидимые пробелы. Это хитрая разновидность несовпадения текста. Хитрая, потому что не всегда ее можно заметить. Вот полюбуйтесь:

excel как проверить текст в ячейке

Как быть? Воспользуйтесь формулой =СЖПРОБЕЛЫ(ЯЧЕЙКА). Эта формула убирает все лишние пробелы. Подробнее – в посте о работе с текстом.

  • В одном файле числа сохранены в виде текста, в другом – в виде чисел:

excel число в виде текста

Для устранения проблемы сделайте следующее:

  1. Выделите весь столбец с числами в виде текста (на рисунке выше диапазон B2:В5).
  2. Подведите курсор к значку “!” и кликните по появившейся стрелочке.
  3. Выберите из выпадающего списка “Преобразовать в число” – см. рисунок ниже.

excel преобразовать текст в число

Вариант 2. ВПР подтянула значения только в верхней части таблицы, в нижней — #Н/Д

  • Смещение диапазона. Чаще всего это происходит, если данные подтягиваются из одного и того же файла. Например, из Листа 1 на Лист 2 или даже из одного и того же листа. Что происходит? Мы тянем формулу вниз, а вместе с ней тянется и диапазон. Вот так:

впр выдает ошибку

Как исправить? Надо закрепить диапазон:

  1. Кликните дважды по ячейке с формулой (С5) – отобразится формула.
  2. В отобразившейся формуле поместите курсор на В12 и нажмите клавишу F4 на клавиатуре.
  3. Переместите курсор на С14 в этой же формуле и опять нажмите F4.
  4. Кликните Enter и формула преобразится, из а) в б)
Читайте так же:
Можно ли временно удалить страницу в контакте

а) =ВПР(В5; В12:С14 ;2;ложь)

б) =ВПР(В5; $B$12:$С$14 ;2;ложь)

Значок “$” указывает на то, что диапазон закреплен. Теперь при протягивании формулы вниз, он не будет смещаться.

Вариант 3. ВПР подтягивает только часть значений, остальные – #Н/Д

  • В некоторых ячейках текст не совпадает на 100%.

Если таких ячеек немного, то проще всего исправить это вручную, копируя из одного файл в другой.

Вариант 4. ВПР возвращает ошибку #ССЫЛКА

  • Вы неверно указали номер столбца. ВПР всегда считает столбцы, начиная с первого столбца выделенного диапазона. Поэтому на рисунке ниже красные номера неверные, зеленые – правильные:

впр excel номер столбца

И еще несколько советов по функции ВПР в Excel:

  1. При вводе формулы используете точку с запятой, а не запятые.
  2. Если протягиваете формулу вправо, не забывайте закреплять ссылку в начале формулы, чтобы формула имела вид: =ВПР( $В5 ;$B$12:$С$14;2;ложь). Для этого наведите курсор на В5 (прямо в формуле) и трижды нажмите клавишу F4 на клавиатуре.
  3. Не используйте аргумент “ИСТИНА” в ВПР, если работаете с текстом. Это приблизительный поиск, в котором будет много ошибок.

9 комментариев к “ ВПР не работает ”

Очень хорошая статья! Без воды и написано именно то, что надо по сути
Но, чтобы статья стала почти исчерпывающей я бы добавил, что иногда ВПР может возвращать ошибку, которая может быть не замечена пользователем, так как не появляется #Н/Д или #ССЫЛКА, однако, при этом функция ВПР() работает некорректно, так как результат ошибочный.
Это происходит в тех случаях, когда в формуле последний аргумент не ЛОЖЬ, а ИСТИНА (или вообще не приведен, а это приравнивается к тому, как если бы была указана ИСТИНА) и при этом первый столбец не отсортирован. Формула вида =ВПР(В5;$B$12:$С$14;2) корректно работать то будет.

Спасибо за комментарий! Да, конечно, это все совершенно верно для ВПР с аргументом ИСТИНА (или без него). Но поскольку статья является фактически продолжением статьи, в которой рассматривалась исключительно ВПР с аргументом ЛОЖЬ, то данная ошибка не приведена.

Функция впр в excel

Для удобства просмотра я перенес Таблицу №2 на первый лист. Все совпало, протягиваем или копируем формулу в ячейки D5 и D6, Таблице №1 добавляем название столбца- Цена товара. Вот так с помощью функции впр мы совместили данные из двух таблиц, находящихся на разных листах, хотя можно и на одном листе и даже в разных книгах.

ВПР в Excel

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

В этом видео подробно показано, как использовать функцию ВПР в Excel:

Рекомендуем смотреть видео в полноэкранном режиме, в настойках качества выбирайте 1080 HD, не забывайте подписываться на канал в YouTube, там Вы найдете много интересного видео, которое выходит достаточно часто. Приятного просмотра!

Читайте так же:
Как в ворде добавить вкладку разработчик

Синтаксис функции ВПР ( VLOOKUP ) в Google Таблицах

Вот как выглядит формула VLOOKUP :

  • search_key — это значение или элемент, который вы ищете. Например, в случае с рестораном это будет бургер или пицца.
  • диапазон — это диапазон, который будет использоваться в функции Vlookup. В крайнем левом столбце этого диапазона будет выполняться поиск search_key.
  • index — это номер столбца, из которого вы хотите получить результат. Первый столбец в диапазоне — 1, второй столбец — 2 и так далее. Обратите внимание, что это значение должно быть от 1 до общего количества столбцов. В противном случае будет возвращено #VALUE! Ошибка.
  • is_sorted — [ ИСТИНА по умолчанию] — в этом аргументе вы можете указать, ищете ли вы точное или приблизительное совпадение. Вы можете использовать FALSE для точного совпадения и TRUE для приблизительного совпадения. Когда вы используете ИСТИНА, список необходимо отсортировать по возрастанию. Если вы не укажете здесь значение, по умолчанию будет ИСТИНА. Обратите внимание, что для использования

Теперь давайте рассмотрим несколько примеров, чтобы понять, как использовать функцию Google Sheets Vlookup в реальных сценариях.

ВПР — одна из дополнительных функций Excel. Он используется в качестве функции базы данных для выполнения вычислений в таблицах, импортированных из базы данных. В данной статье комбинированное использование функций ВПР и СУММ в Excel для определения конкретных критериев и нахождения суммы значений, представленных во всех ВПР.

Объяснение ВПР с суммой в Excel

Нам нужно понимать различные термины, связанные с использованием ВПР и СУММ в Excel, чтобы найти общую сумму значений, соответствующих критериям. В функции поиска следует учитывать в основном четыре элемента, включая значение поиска, диапазон поиска, индексы столбцов и логическое значение.

Формулу следует вводить как = СУММ (ВПР (значение поиска, диапазон поиска, индекс столбца и логическое значение))

  • Значение поиска — Это значение, которое мы ищем для определения точно совпадающей суммы. Это определенно изменяет значение поиска, чтобы определить сумму разных столбцов с использованием разных критериев.
  • Диапазон поиска — Это диапазон ячеек, который используется для поиска данных по заданным критериям. Как правило, это будет таблица данных, сгенерированных из разных источников.
  • Индексы столбцов — Чтобы найти сумму, необходимо ввести массив индексов. Можно ввести все индексы столбцов и несколько индексов столбцов в зависимости от требований. Это полезно для идентификации столбцов, в том числе в сумме.
  • Логическая ценность — Соответствующее логическое значение 0 и 1, истина или ложь для выбора значений, которые совпадают или приблизительно

Заметка: Функция Sum также применяется к строкам совпадающих значений с помощью СУММПРОИЗВ в Excel и СУММЕСЛИ в Excel.

Лучшие примеры ВПР с функцией СУММ

Ниже приведены несколько примеров ВПР с функцией СУММ.

Пример # 1

Использование простой суммы и функции vlookup

Vlookup с Sum Пример 2

Продажи ноутбука определяются с помощью суммы и vlookup. Но это также можно просто использовать по формуле суммы. Причина использования vlookup с суммой заключается в возможности изменения значений поиска в ячейке G3 для определения продаж других товаров, таких как DVD и телефон. При изменении поискового значения для телефона генерируются продажи телефона, как показано на снимке экрана ниже.

Читайте так же:
Как включить семейный режим в яндекс браузере

Vlookup с Sum Пример 2-1

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

Пример # 2

Определение суммы продаж, произведенных на двух разных листах, с помощью Excel VLOOKUP и SUM. Для объяснения этого примера используются следующие данные.

Vlookup с Sum Пример 3

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

Vlookup с Sum Пример 3-1

Чтобы определить продажи, полученные от других клиентов, формула перетаскивается в другие строки, добавляя абсолютную ссылку на ячейку в диапазон данных. Это даст точные результаты при использовании суммы и vlookup.

Пример # 3

Суммирование значений, представленных в альтернативных столбцах

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

Vlookup с Sum Пример 4

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

Vlookup с Sum Пример 4-1

Чтобы получить продажи других продуктов, простое изменение значения в ячейке B14 помогает получить желаемый результат. Это лучшая функция, предоставляемая Excel VLOOKUP.

Vlookup с Sum Пример 4-2

Как использовать ВПР с функцией СУММ?

ВПР и СУММ также можно использовать для следующих целей:

  1. Определение суммы совпадающих значений в столбцах
  2. Определение суммы совпадающих значений в строках

Для понимания создана таблица, как показано на рисунке ниже.

Vlookup с Sum Пример 1

# 1 — Определение суммы совпадающих значений в столбцах

Если мы хотим определить общий объем продаж кулера с января по декабрь, конкретные критерии следует ввести в пустую ячейку.

Vlookup с Sum Пример 1-1

В этом случае поисковое значение представлено в ячейках с именами A15 и A2: M11 — это диапазон данных, числа от 2 до 13 — это индексы для столбцов. Применяя их в Excel vlookup и формуле суммы, можно получить общую стоимость охладителя продаж. После ввода формулы нажмите CTRL, SHIFT и ENTER одновременно, чтобы получить результат; в противном случае отображается только значение первой ячейки без всех значений. Этот процесс применим к остальным продуктам, чтобы найти сумму значений в разных столбцах.

# 2 — Определение суммы совпадающих значений в строках

Нижеприведенный лист показывает продажи кулера в разных строках. Они суммируются с помощью функции СУММПРОИЗВ.

Vlookup с Sum Пример 1-2

В этом случае поисковое значение представлено в ячейке A15, которая рассматривается как критерий для определения суммы продаж. Мы можем изменить это значение на другие продукты, чтобы узнать о продажах. Но в этом процессе простое нажатие клавиши ввода после написания формулы дает лучшие результаты.

Что нужно помнить о Excel Vlookup с Sum

При использовании функции ВПР необходимо помнить следующее, разработав соответствующий синтаксис:

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector