Функция Excel SUMIF

Question

У меня есть этот лист Excel, где мне нужно рассчитать стоимость перевозки.

У меня есть таблица тарифов на 2 типа грузовых автомобилей (большой и маленький) по разным направлениям - все с разными ценами. Как это:

City    Big Lorry   Small Lorry
City A  1000        1150
City B  1800        1950
City C  2600        2750
City D  3900        3950

Теперь в другой таблице я хочу ввести Грузовик и пункт назначения, и Excel должен автоматически заполнить цену в соответствии с таблицей выше. Как это:

City    Lorry   Price
City A  Small   
City D  Big 
City C  Big 
City B  Small   
City D      Small
City A      Big

Пожалуйста, смотрите ссылку на OneDrive, куда я загрузил файл Excel, чтобы было легко понять, что мне нужно.

Файл Excel

Благодарю.

Answer 1

Вы можете сделать это за один шаг в Excel, и вам не нужен SumIf. Просто используйте следующую формулу в столбце Цена =VLOOKUP(A9,$A$2:$C$5,IF(B9="Small",3,2),FALSE) . Это будет искать значение города в вашем первом столбце, а затем вернуть соответствующую цену, основанную на размере грузовика.

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

Answer 2

Вы можете вкладывать SUMIF в IF IFment.

=IF(B1="Big",SUMIF(Sheet1!A2:A5,A1,Sheet1!B2:B5),IF(B1="Small",SUMIF(Sheet1!A2:A5,A1,Sheet1!C2:C5),"Invalid Name"))

=IF(Size="Big",SUMIF(City List,City,Big Cost),IF(Size="Small",SUMIF(City List,City,Small Cost),"Invalid Name"))

A1 = город
B1 = размер грузовика

В идеале в исходной таблице у меня должен быть столбец размера вместо двух столбцов стоимости. Это позволило бы SUMIF или VLOOKUP работать нормально.
CityA Small 1500
CityA Large 1800
CityB Small 1350
CityB Large 1600

Answer 3

Лучше всего это сделать с помощью VLOOKUP, но это будет немного сложнее, чем у вашего садового сорта VLOOKUP, поскольку вы смотрите на два столбца данных.

Хотя это можно сделать за один шаг, это менее надежно на будущее, так как если добавить больше размеров, вам придется обновить формулу, и это может стать довольно неудобным.

Шаг 1
VLOOKUP нужно будет найти одно значение в целевой таблице, но вы ищете два. Чтобы это исправить, вам понадобится вспомогательный столбец, и поскольку VLOOKUPS начинаются слева от выбранного диапазона данных, он должен быть первым столбцом в вашей таблице.

• В исходной таблице добавьте столбец слева (убедитесь, что диапазон таблиц включает новый столбец, если вы используете 2007>)

• Предполагая, что этот новый столбец находится в столбце A, а размер указан в B, а пункт назначения - в C, добавьте в него следующую формулу, скопировав ее в диапазон:

= СЦЕПИТЬ ($ В2, С2 $)

или же

= CONCATENATE([@ Размер], [@ Distance])

Вы также можете сделать =$B2&$C2 но другим пользователям это будет проще понять

• Если вы предпочитаете, теперь вы можете скрыть этот столбец

Шаг 2
Затем пришло время для VLOOKUP.

Предполагая, что размер указан в A, а пункт назначения - в B, в ячейку, в которую вы хотите вернуть значение, добавьте следующую формулу:

=VLOOKUP(CONCATENATE($A$2,$B$2),SourceTable,4,FALSE)

Если вы не используете таблицы, замените SourceTable (или как вы его называли) диапазоном исходной таблицы. Обратите внимание, что число 4 представляет количество столбцов в нужном значении.