Excel извлекает самое последнее значение для пары "счет / значение элемента"

У меня есть таблица Excel с двумя вкладками. На первой вкладке отображаются продажи предметов покупателя за определенный период, включая дату продажи. поэтому столбцы на 1-й вкладке

CUSTOMER | ITEM | DATE | QTY | PRICE | EXTENSION

Данные испытаний:

Customer No.    Date     Item     Qty Ship  Price   Ext. 
100207        8/19/2015  7044503    3       90.85   272.55
100207        8/12/2015  7044503    10     90.85    908.5
100207        8/12/2015  7044504    16     91.99    1471.84
100207        7/31/2015  7044505    9      93.41    840.69
100207        7/31/2015  7045427    2         73    146
112179        7/22/2015  6951235    1      23.65    23.65
112179        7/22/2015  6951235    1      23.65    23.65
112179        7/15/2015  6951235    1      23.65    23.65
112179         7/8/2015  6951235    1      23.65    23.65
112179        6/30/2015  6951235    1      23.65    23.65
112179        4/22/2015 0290130075  9       2.75    22.86
112179        9/23/2015 0290130075  9       2.54    22.86
112179         9/9/2015 0290130075  9       2.40    22.86
112308         9/9/2015 VWR40101    1     451.00   451.00
112308        8/31/2015 VWR40101    1     451.00   451.00
112308        8/26/2015 VWR65020    3     186.00   558.00
112308        8/12/2015 VWR65020    2     167.75    335.5
112308        7/31/2015 VWR65020    2     175.00    350.0
112308        7/22/2015 VWR65020    4     177.75    711.0

Столбцы на 2-й вкладке

CUSTOMER | ITEM | TOTAL SALES

Данные испытаний:

Customer No.    Item      Total
100207          7044503   1181.05
100207          7044504   1471.84
100207          7044505    840.69
100207          7045427    146
112179          6951235    118.25
112179          290130075   68.58
112308          VWR40101    902
112308          VWR65020    1954.5
Grand Total                 6682.91

Я пытаюсь добавить еще один столбец на вторую вкладку для LAST SALE PRICE

Я знаю, что это, вероятно, комбинация MAX(), MATCH(), И VLOOKUP(), но я не уверен, как соединить их вместе для достижения моей цели.

Спасибо!


excel vlookup excel-formula
person AlliDeacon    schedule 25.04.2016    source источник
comment
Покажите, пожалуйста, некоторые тестовые данные, которые мы можем использовать для проверки формулы.   -  person Scott Craner    schedule 25.04.2016
comment
И под последней ценой продажи вы имеете в виду цену на максимальную дату (то есть на самую последнюю дату)?   -  person BruceWayne    schedule 25.04.2016

Ответы (2)


arrow_upward
2
arrow_downward

Обычная формула (без массива), настройте диапазоны в соответствии с вашими данными:

=INDEX(Sheet1!$E$2:$E$20,MATCH(1,INDEX((Sheet1!$A$2:$A$20=A2)*(Sheet1!$C$2:$C$20=B2)*(Sheet1!$B$2:$B$20=MAX(INDEX((Sheet1!$A$2:$A$20=A2)*(Sheet1!$C$2:$C$20=B2)*Sheet1!$B$2:$B$20,))),),0))
person tigeravatar    schedule 25.04.2016
comment
Комментарий, я закончил тем, что добавил столбец Max (DATE) в свою сводную таблицу, а затем использовал функции INDEX / MATCH! Огромное спасибо :) - person AlliDeacon; 26.04.2016

arrow_upward
1
arrow_downward

Попробуйте эту формулу массива

=INDEX(Sheet1!$E$2:$E$100,MIN(IF((Sheet1!$A$2:$A$100 = A2)*(Sheet1!$B$2:$B$100 = B2)*(Sheet1!$C$2:$C$100 = MAX(IF((Sheet1!$A$2:$A$100 = A2)*(Sheet1!$B$2:$B$100 = B2),Sheet1!$C$2:$C$100))),(ROW(Sheet1!$A$2:$A$100)-1))))

Без тестовых данных это сложно проверить.

Поскольку это формула массива, ее необходимо подтвердить с помощью Ctrl-Shift-Enter при выходе из режима редактирования. Если все сделано правильно, Excel поместит {} вокруг формулы.

Если вы используете Excel 2010 или новее, вы можете использовать эту формулу:

=INDEX(Sheet1!$E$2:$E$100,AGGREGATE(15,6,(ROW(Sheet1!$A$2:$A$100)-1)/((Sheet1!$A$2:$A$100 = A2)*(Sheet1!$B$2:$B$100 = B2)*(Sheet1!$C$2:$C$100 = AGGREGATE(14,6,Sheet1!$C$2:$C$100/((Sheet1!$A$2:$A$100 = A2)*(Sheet1!$B$2:$B$100 = B2)),1))),1))
person Scott Craner    schedule 25.04.2016