☰

Сумма ВПР для каждого значения в строке

tag-icon • tag-icon microsoft-excel worksheet-function spreadsheet google-spreadsheets
Сумма ВПР для каждого значения в строке

Должен быть способ сделать это, но я все еще борюсь...

==Данные==

Лист1

        A           B
+---------------+-------+
| Fishy_Product | Price |
+---------------+-------+
| Cod           | $9.99 |
| Trout         | $8.99 |
| Haddock       | $6.99 |
| Kippers       | $3.99 |
+---------------+-------+

Лист2

       A            B         C       D        E
+------------+-------------+-----+--------+---------+
|            | Total Spend | Cod | Trout  | Haddock |
+------------+-------------+-----+--------+---------+
| Customer 1 | ?           |   1 |        |       3 |
| Customer 2 | ?           |     |      2 |         |
| Customer 3 | ?           |   2 |      2 |       2 |
+------------+-------------+-----+--------+---------+

==Вызов==

Для каждого клиента мне нужно рассчитатьОбщая сумма расходовумножив «покупки» в Листе2 на «продукт/цену» в Листе1. Например, я мог бы вычислитьОбщая сумма расходовдля Клиента 1 по направлениям: =(C2*vlookup(c1,'Sheet1!A:B,2,0))+(D2*vlookup(D1,'Sheet1!A:B,2,0))+(E2*vlookup(E1,'Sheet1!A:B,2,0))и т.д.

НОНа Лист1 постоянно добавляются новые продукты.

Как только «селедка» будет добавлена ​​в столбец F, мне придется вручную вернуться назад и обновить всю формулу. (Обратите внимание, всякий раз, когда новый продукт добавляется в Лист1, он автоматически появляется в качестве нового столбца в Листе2 из-за используемой формулы). Помимо личной лени, проблема в том, что я могу не заметить, когда нужны обновления, и работать с неправильными итогами.

Есть ли способ рассчитатьОбщая сумма расходовбез необходимости вручную обновлять формулу каждый раз при добавлении нового элемента/столбца?

Заранее большое спасибо всем, кто сможет мне в этом помочь.

PS Должен добавить, что работаю в Google Таблицах.

решение1

Просто используйте SUMPRODUCT()для расчетов:

введите описание изображения здесь

Формула в B8:

=SUMPRODUCT((A$2:A$5=C$7:E$7)*(B$2:B$5*C8:E8))

Перетащите вниз, но учтите, что он не будет автоматически обновляться при добавлении товаров.

Для ясности: порядок продуктов не имеет значения:

введите описание изображения здесь

Результаты те же.

решение2

Эту проблему можно решить, внеся небольшие изменения в ориентацию данных:

:Предостережение:

Показанный метод работает как с Google Sheet, так и с Excel.

введите описание изображения здесь

  • Формула в ячейке листа 2 H19 и заполнение:

     =IFERROR(SUMPRODUCT(VLOOKUP(Sheet1!G13,$G$19:$K$22,{3,4,5},FALSE))*Sheet1!H13,"")

Примечание.

Если SUMPRODUCT имеет один аргумент, он ведет себя во многом как SUM. Но когда SUMPRODUCT имеет несколько аргументов, он возвращает сумму произведения своих аргументов. Вот работа с одним аргументом.

{3,4,5} — столбцы I, J и K в диапазоне данных $G$19:$K$22, которые можно изменять.

При необходимости измените ссылки на ячейки в формуле.

решение3

Вы выбираете большую область для таблицы VLookup, чтобы вместить все элементы. скажем, возьмите 5000 строк. Теперь каждый раз, когда добавляются новые элементы, вы снова сортируете два столбца A и B на A, содержащих элементы. В момент, когда вы выполняете СОРТИРОВКУ, все будет вести себя правильно, так что никаких проблем.

Спасибо.

Связанный контент