Формула в excel впр как суммировать подходящие значения
Добрый день!
Несколько часов бьюсь над простой задачей, но безрезультатно.
Есть 2 таблицы:
Таблица 1
Дата | Сумма за день
01.01 | —
02.01 | —
03.10 | —
04.10 | —
05.10 | —
06.10 | —
.
Таблица 2
Дата | Сумма накладной
01.01 | 250
02.01 | 200
02.01 | 999
03.01 | 380
04.01 | 500
05.01 | 310
05.01 | 900
Нужно подтянуть значение в первую таблицу, при этому просуммировать результат совпадающих значениях (2 накладные за 02.01, 2 накладные за 05.01).
ВПР в зависимости от "True" или "False" дает или первое или последнее значение суммы накладной, но не их сумму. Пробовал добавлять СУММЕСЛИ, но в результате получал 0, не понятно откуда взявшийся или ошибки.
Помогите, пожалуйста, с формулой!
Функция ВПР
Совет: Попробуйте использовать новую функцию ПРОСМОТРX , улучшенную версию функции ВЛОП, которая работает в любом направлении и по умолчанию возвращает точные совпадения, что упрощает и удобнее в использовании, чем предшественницу.
Если вам нужно найти что-то в таблице или диапазоне по строкам, используйте В ПРОСМОТР. Например, можно найти цену автомобильной части по номеру части или имя сотрудника на основе его ИД.
Совет: Ознакомьтесь с этими видеороликами с YouTube от Microsoft Creators, чтобы узнать больше о ВЛИО!
Самая простая функция ВПР означает следующее:
=ВРОТ.В.(Что вы хотите найти, где ее нужно найти, номер столбца в диапазоне, содержащего возвращаемую величину, возвращает приблизительное или точное совпадение, обозначенные как 1/ИСТИНА или 0/ЛОЖЬ).
Совет: Секрет функции ВПР состоит в организации данных таким образом, чтобы искомое значение (Фрукт) отображалось слева от возвращаемого значения, которое нужно найти (Количество).
Используйте функцию ВПР для поиска значения в таблице.
ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])
=ВLOOKUP(A2;’Сведения о клиенте’! A:F;3;ЛОЖЬ)
Значение для поиска. Иного значения должно быть в первом столбце диапазона ячеек, который указан в table_array.
Например, если массив таблицы охватывает ячейки B2:D7, lookup_value должны быть в столбце B.
Искомое_значение может являться значением или ссылкой на ячейку.
Диапазон ячеек, в котором будет выполнен поиск искомого_значения и возвращаемого значения с помощью функции ВПР. Можно использовать именуемый диапазон или таблицу, а не ссылки на ячейки, а имена в аргументе.
Первый столбец в диапазоне ячеев должен содержать lookup_value . Диапазон ячеев также должен включать возвращаемую величину, которая требуется найти.
Номер столбца (начиная с 1 в левом большинстве столбцов table_array),содержащий возвращаемую величину.
Логическое значение, определяющее, какое совпадение должна найти функция ВПР, — приблизительное или точное.
Приблизительное совпадение: 1/ИСТИНА предполагает, что первый столбец в таблице отсортировали по алфавиту или по числу, а затем будут выполнять поиск ближайшего значения. Это способ по умолчанию, если не указан другой. Например, =ВКП(90;A1:B100;2;ИСТИНА).
Точное совпадение: 0/ЛОЖЬ ищет точное значение в первом столбце. Например, =ВКП("Кузнецов";A1:B100;2;ЛОЖЬ).
Начало работы
Для построения синтаксиса функции ВПР вам потребуется следующая информация:
Значение, которое вам нужно найти, то есть искомое значение.
Диапазон, в котором находится искомое значение. Помните, что для правильной работы функции ВПР искомое значение всегда должно находиться в первом столбце диапазона. Например, если искомое значение находится в ячейке C2, диапазон должен начинаться с C.
Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона указать диапазон B2:D11, следует посчитать B первым столбцом, C — вторым и так далее.
При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.
Теперь объедините все перечисленное выше аргументы следующим образом:
=ВЛОП(искомого значения; диапазон, содержащий искомые значения, номер столбца в диапазоне, содержащий возвращаемую величину, приблизительное совпадение (ИСТИНА) или Точное совпадение (ЛОЖЬ)).
Примеры
Вот несколько примеров использования функции ВПР.
Пример 1
Пример 2
Пример 3
Пример 4
Пример 5
Функцию ВЛОП можно использовать для объединения нескольких таблиц в одну, если одна из таблиц имеет поля, общие для всех остальных. Это особенно полезно, если вам нужно поделиться книгой с людьми, у которых есть более старые версии Excel, которые не поддерживают функции данных с несколькими таблицами в качестве источников данных, путем объединения источников в одну таблицу и изменения источника данных функции данных в новую таблицу, функцию данных можно использовать в более старых версиях Excel (при условии, что функция данных поддерживается более старой версией).
В этом столбце столбцы A–F и H имеют значения или формулы, которые используют только значения на этом сайте, а в остальных столбцах используется В., а для получения данных из других таблиц используются значения из столбцов A (код клиента) и B (Доверенность).
Скопируйте таблицу с общими полями на новый и придать ей имя.
Чтобы открыть диалоговое окно Управление отношениями, > в > управления отношениями нажмите кнопку Data > Data Tools (Управление отношениями).
Для каждой из указанных связей обратите внимание на следующее:
Поле, которое связывает таблицы (в скобки в диалоговом окне). Это первый lookup_value для формулы ВЛВП.
Имя связанной таблицы подытов. Это первый table_array в формуле ВЛИО.
Поле (столбец) в связанной таблице подытовки с данными, которые должны быть в новом столбце. Эта информация не отображается в диалоговом оке Управление связями. Чтобы узнать, какое поле нужно извлечь, необходимо посмотреть в связанной таблице подыска. Обратите внимание на номер столбца (A=1) — это col_index_num формуле.
Чтобы добавить поле в новую таблицу, введите формулу в первом пустом столбце с использованием сведений, собранных на шаге 3.
В нашем примере в столбце G для получения данных "Ставка счета" из четвертого столбца (col_index_num = 4) из таблицы "Доверенности" используется столбец "Доверенность" lookup_value(table_array) с формулой =В.В.ПРОСМОТР([@Attorney];tbl_Attorneys;4;ЛОЖЬ).
В формуле также можно использовать ссылку на ячейку и ссылку на диапазон. В нашем примере это будет =ВЛП(A2;’Защитники’! A:D,4;ЛОЖЬ).
Продолжайте добавлять поля, пока не будут добавлены все необходимые поля. При подготовке книги, содержащей функции данных, в которой используются несколько таблиц, измените источник данных функции на новую таблицу.