Автоматическое заполнение значений на основе нескольких зависимостей в Excel

Question

У меня есть три столбца с уже заполненной информацией. Значение в столбце C зависит от двух идентификаторов из столбцов A и B (т. Е. Джордж всегда является продавцом яблок в Америке, а Сара - продавцом свеклы).

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

Таким образом, если я введу в "Свекла" в A9 и "Австралия" в B9, то C9 должен получить значение "Эми". Я опущу это значение, чтобы автоматически заполнить оставшуюся часть таблицы, чтобы будущие строки добавлялись автоматически.

Я использовал функцию INDIRECT(ROW-1) для ссылки на весь столбец над ячейкой с формулой, чтобы получить правильные данные, если это имеет смысл ...

       A         B         C
1   Product  Country    Contact 
2   Apple    Australia  John
3   Apple    America    George
4   Beets    Australia  Amy
5   Beets    America    Sarah
6   Carrot   Australia  Greg
7   Carrot   America    Belinda
8   Apple    Australia  John 
9   Beets    Australia  _Formula:_

=INDEX((INDIRECT("C2:C"&ROW()-1)),MATCH(A10,IF((INDIRECT("B2:B"&ROW()-1))=B10,(INDIRECT("A2:A"&ROW()-1)))),0)

Answer 1

Вот ответ, который, кажется, работает:

=VLOOKUP(B9, OFFSET(B$2:C$7, MATCH(A9,A$2:A$7,0)-1, 0, 2, 2), 2, 0)

Работаем изнутри:

• VLOOKUP(B9, OFFSET(B$2:C$7, MATCH(A9,A$2:A$7,0) -1, 0, 2, 2), 2, 0)
MATCH(A9, A$2:A$7, 0) находит A9 (Beets) в диапазоне A2:A7 { Apple , Apple , Beets , Beets , Carrot , Carrot }, получая значение индекса в диапазоне 1-6.  Конечно, поскольку A2 = A3 , A4 = A5 и A6 = A7 , значение индекса всегда будет равно 1, 3 или 5; для Beets это 3.
• MATCH(A9, A$2:A$7, 0)-1 переназначает вышеприведенное, чтобы быть в диапазоне 0-5 (в частности, 0, 2 или 4; для Beets это 2).
• OFFSET(B$2:C$7, the_above, 0, 2, 2) говорит
  • взять регион B2:C7 ,
  • от верхнего левого угла (B2) спуститесь на две строки (так как первое вхождение Beets в A2:A7 находится в третьей строке, это означает, что мы должны спуститься на две (3−1) строки из строки 2) и идти направо ноль столбцов, прибывающих в ячейку B4 ,
  • и затем возьмите область 2 × 2, начиная с этой точки. что приводит нас к диапазону B4:C5 , который

    Австралия Эми Америка Сара

    который является всемирным списком продавцов свеклы.  Обратите внимание, что нам нужен регион шириной в два столбца, потому что мы хотим связать страны с продавцами, но высотой должно быть число стран (в данном примере это две).  Если бы было семь стран, мы бы изменили это на OFFSET(B$2:C$7, MATCH(A9,A$2:A$7,0)-1, 0, 7, 2)
    (Хотя, если бы было семь стран (и три товара), вводный блок имел бы высоту 21 ряд, поэтому мы будем использовать диапазоны от строки 2 до строки 22).
• VLOOKUP(B9, OFFSET(B$2:C$7, MATCH(A9,A$2:A$7,0)-1, 0, 2, 2) , 2, 0)
VLOOKUP (и его братья, LOOKUP и HLOOKUP), по сути, являются сокращением для INDEX+MATCH . Этот поиск первого столбца V обозначают об ertical) из свеклы продавцов региона / массива B4:C5 для B9 Australia , а затем возвращает значение из 2 - го столбца строки, соответствующей значению подстановки Australia ,  Australia в ряду 4.  Поскольку мы говорим о столбцах B и C , столбец C в данном контексте является «вторым столбцом», поэтому мы получаем ячейку C4 , которая содержит Amy .

… Что является желаемым результатом для строки 9, так как Эми является австралийским поставщиком свеклы.