Automatyczne wypełnianie wartości na podstawie wielu zależności w programie Excel

Mam trzy kolumny z wpisanymi już informacjami. Wartość w kolumnie C zależy od dwóch identyfikatorów z kolumn A i amp; B (tj. George jest zawsze sprzedawcą jabłek w Ameryce, ale Sarah jest sprzedawcą Beets).

Mam wystarczającą ilość danych w wierszach 1-8, że chcę, aby komórki od 9 roku zaczęły automatycznie wypełniać wymagane informacje za każdym razem, gdy wprowadzam nowy wiersz.

Tak więc, jeśli wejdę w „Beets” w A9 i „Australia” w B9, to C9 powinien pociągnąć wartość „Amy”. Wyciągnę tę wartość, aby automatycznie uzupełnić resztę tabeli, aby przyszłe wiersze były dodawane automatycznie.

Użyłem funkcji INDIRECT (ROW-1), aby odwołać się do całej kolumny nad komórką za pomocą formuły, aby uzyskać poprawne dane, jeśli to ma sens ...

       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)

microsoft-excel microsoft-excel-2010 worksheet-function

— Stacker
źródło

Czy istniejące dane odpowiadają wszystkim możliwym wartościom dla C, czy może napotkasz kombinację, w której musisz wprowadzić nową wartość dla C?

— fixer1234

Hi Fixer, tak, przykro mi, że wiele zmian jednocześnie wystąpiło, zdałem sobie sprawę, że w moim przykładzie zakres nie został poprawnie zdefiniowany, ale poprawka wciąż powraca # N / A. Nie ma potrzeby obsługi błędów, można ręcznie wprowadzić dowolną nową kombinację kraju / produktu. Funkcja pośrednia ma nadzieję, że ta nowa wartość zostanie dodana przy następnym wystąpieniu tego zdarzenia. P.S. Próbuję również wzoru jako Array

— Stacker

O wiele prostsze podejście: utwórz tabelę przeglądową z dwiema kolumnami - A i B i C dla siedmiu kombinacji. Dla każdego nowego wiersza wyszukaj A i amp; B.

— fixer1234

Hi Fixer, Doceń swoją odpowiedź i rozwiązanie, choć wolałbym się dowiedzieć, dlaczego moja formuła nie działa ... Również przykład jest bardziej uproszczonym rozwiązaniem. Mam wiele produktów (4+) możliwych produktów na kraj (10+), co sprawia, że kombinacje są nieograniczone dla tabeli przeglądowej.

— Stacker

@Stacker: BTW, jeśli „zaadresujesz” swój komentarz w ten sposób, osoba otrzyma flagę wiadomości, w przeciwnym razie nikt nie będzie świadomy Twojego wpisu (potrzebny, jeśli więcej niż jedna osoba jest powiązana z pytaniem lub odpowiedzią). Sugestia: dodaj kolumnę pomocnika za pomocą A i B (można ukryć). Następnie po prostu wykonaj indeks / dopasowanie w wierszach powyżej bieżącego wiersza.

— fixer1234

Oto odpowiedź, która wydaje się działać:

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

Praca od środka:

_{WYSZUKAJ.PIONOWO (B9, PRZESUNIĘCIE (B $ 2: 7 C,} MECZ (A9, 2 USD: 7,0 USD) _{-1, 0, 2, 2), 2, 0)}
MATCH(A9, A$2:A$7, 0) znajdzie A9 ( Beets ) w zasięgu A2:A7 { Apple, Apple, Beets, Beets, Carrot, Carrot }, uzyskując wartość indeksu w zakresie 1-6. Oczywiście, ponieważ A2 = A3, A4 = A5, i A6 = A7, wartość indeksu zawsze będzie wynosić 1, 3 lub 5; dla Beets, to jest 3.
MATCH(A9, A$2:A$7, 0)-1 przekształca powyższe, aby mieściło się w zakresie 0-5 (konkretnie, 0, 2 lub 4; dla Beets, to jest 2).
OFFSET(B$2:C$7, powyższe , 0, 2, 2) mówi
- weź B2:C7 region,
- z lewego górnego rogu ( B2 ), idź w dół dwa rzędy (od pierwszego wystąpienia Beets w A2:A7 jest w trzecim rzędzie, oznacza to, że musimy zejść w dół o dwa (3-1) wiersze od wiersza 2) i przejść w prawo do zerowych kolumn, przybywa do celi B4,
- a następnie weź region 2 × 2, zaczynając od tego punktu. co prowadzi nas do zasięgu B4:C5, który jest
```
Australia   Amy
America     Sarah
```
  która jest ogólnoświatową listą sprzedawców buraków. Zauważ, że chcemy regionu, który ma dwie kolumny, ponieważ chcemy skojarzyć kraje ze sprzedawcami, ale wysokość powinna być liczbą krajów (co zdarza się w tym przykładzie dwa). Gdyby było siedem krajów, zmienilibyśmy to na OFFSET(_{B 2 USD: 7 USD, MECZ (A9, 2 USD: 7,0 USD) -1, 0,} 7 , 2)
  (Chociaż, jeśli było siedem krajów (i trzy towary), blok wprowadzający miałby 21 rzędów wysokości, więc używamy zakresów od wiersza 2 do wiersza 22.)
VLOOKUP(B9, _{OFFSET (B $ 2: C 7 USD, MECZ (A9, A 2: A 7,0) -1, 0, 2, 2)}, 2, 0)
VLOOKUP (i jego bracia, LOOKUP i HLOOKUP ) są zasadniczo skrót dla INDEX + MATCH. Przeszukuje pierwszą kolumnę ( V oznacza v pionowy) regionu / tablicy sprzedawców buraków ( B4:C5 ) dla B9 ( Australia ) a następnie zwraca wartość z drugiej kolumny wiersza, który pasował do wartości wyszukiwania ( Australia ). Australia jest w rzędzie 4. Ponieważ mówimy o kolumnach B i C, Kolumna C jest „drugą kolumną” w tym kontekście, więc to doprowadza nas do komórki C4, który zawiera Amy.

… Co jest pożądanym wynikiem dla wiersza 9, odkąd Amy jest australijskim sprzedawcą buraków.

— Scott
źródło

Fantastyczne, dzięki za pomoc, a także wyjaśnienie. Działa bardzo dobrze Nie mogę ci podziękować :)

— Stacker

Moja przyjemność. Przepraszam za tak długie opublikowanie wyjaśnienia.

— Scott