Dopasuj indeks na 2 warunkach?

Dane:

Mógłbym skorzystać z pomocy przy tworzeniu formuły dopasowania indeksu opartej na 2 elementach warunkowych. W przypadku załączonego zestawu danych muszę skonfigurować formułę (i uczynić ją powtarzalną dla innych sklepów i przyszłych dat), która pobiera numer danych w oparciu o konkretną datę (11/9/15, w tym przypadku) i dla store # 414. W tym przykładzie chcę, aby zwrócona została odpowiedź „132”. Mam nadzieję, że to ma sens.

Każda pomoc byłaby mile widziana!

Oto dwa rozwiązania, odpowiadające moim sugestiom w komentarzach do pytania. W obu przypadkach zakładam, że A1: C30 zawiera dane z pytania.

Korzystanie z funkcji bazy danych

Pierwsze rozwiązania wykorzystują funkcje bazy danych Excel. Wszystkie funkcje bazy danych traktują zakres komórek jako bazę danych, gdzie każdy wiersz jest rekordem, a każda kolumna jest polem. Pierwszy wiersz zawiera nazwy kolumn. Funkcje bazy danych przyjmują również inny zakres komórek jako kryteria wyszukiwania, gdzie pierwszy wiersz to nazwy kolumn, a drugi wiersz to rzeczywiste kryteria. Biorąc to pod uwagę, w E1: F2 (lub gdziekolwiek, ale to właśnie umieściłem w tych przykładach) umieść:

       E         F
 1   Store #     Date
 2       414  11/9/15

To są kryteria. Następnie w E4 (lub gdziekolwiek) umieścić =DGET(A1:C30,"Data",E1:F2). To używa DGET funkcja bazy danych do wyszukania wartości kolumny podanej w bazie danych ( A1:C30 ), nazwa kolumny ( "Data" ) i kryteria ( E1:F2 ). W takim przypadku spowoduje to 132. Zmiana zawartości F2 na 11/2/15 zmieni DGET wartość do 55itd.

Jest to prawdopodobnie najczystszy sposób, ponieważ łatwo go rozszerzyć, jeśli masz dodatkowe kolumny w danych i kryteriach, których chcesz użyć do dopasowania tych kolumn. Możesz również ponownie wykorzystać części kryteriów, aby zrobić inne rzeczy. Na przykład, =DSUM(A1:C30,"Data",E1:E2) zsumuje wszystkie wartości danych dla magazynu 414, =DSUM(A1:C30,"Data",F1:F2) podsumuje wszystkie wartości danych dla 11/9/15, itd. Nie zakłada też niczego o kolejności sortowania kolumn. Wadą jest to, że jeśli nie korzystasz z bazy danych, funkcje (np. ja: -)), będziesz musiał ponownie przeczytać pomoc na nich za każdym razem, gdy z nich korzystasz (tak jak to zrobiłem :-)), więc może nie jest to możliwe do utrzymania.

Korzystanie z formuł indeksowania i wyszukiwania

Drugi sposób łączy niektóre formuły wyszukiwania Excela.

Skonfiguruj arkusz taki sam jak powyżej, dane z pytania w A1: C30 i kryteria w E1: F2. Zauważ, że w tym przypadku użyjemy tylko E2 i F2, ale możesz zostawić E1 i F1 jako etykiety co do E2: F2.

Następnie w E4:

 =VLOOKUP(F2,INDEX(B:B,MATCH(E2,A:A,0),1):INDEX(C:C,MATCH(E2,A:A,1),1),2)

Rozbijając to od wewnątrz:

MATCH(E2,A:A,0)

To znajdzie pierwszy mecz ( 0 ) w pierwszej kolumnie ( A:A ), który pasuje do sklepu # in E2. Zwraca pozycję względną, w tym przypadku, ponieważ cała pierwsza kolumna jest tablicą odnośników, pozycją będzie numer wiersza pierwszego wystąpienia magazynu # w E2. W przykładowych danych, jeśli umieścisz je w komórce, będzie ona miała wartość 7.

INDEX(B:B,MATCH(E2,A:A,0),1)

Spowoduje to wygenerowanie odwołania do komórki w drugiej kolumnie ( B:B ) w rzędzie określonym przez MATCH i kolumna 1. Jeśli umieścisz to w samej komórce, wartością będzie wartość komórki, do której istnieje odwołanie, która na przykładzie będzie wartością z B7, lub 11/10/14.

MATCH(E2,A:A,1)

Znajduje ostatni mecz ( 1 ) w pierwszej kolumnie, która pasuje do sklepu #. Jeśli umieścisz to w komórce, wartość będzie 25.

INDEX(C:C,MATCH(E2,A:A,1),1)

Spowoduje to wygenerowanie odwołania do komórki w trzeciej kolumnie ( C:C ) w rzędzie określonym przez MATCH i kolumna 1. Jeśli umieścisz to w komórce samodzielnie, wartością będzie wartość komórki, do której istnieje odwołanie, która w przykładzie będzie wartością z C25, lub 132.

INDEX(B:B,MATCH(E2,A:A,0),1):INDEX(C:C,MATCH(E2,A:A,1),1)

To łączy oba INDEX formuły do ​​generowania odniesienia do wyszukiwania daty. W przykładowych danych będzie to B7: C25 (jeśli umieścisz je w komórce, otrzymasz #VALUE! ponieważ powoduje więcej niż jedną wartość. Jeśli umieścisz go w komórce jako formułę tablicową, otrzymasz wartość górnej lewej komórki lub 11/10/14 ).

=VLOOKUP(F2,INDEX(B:B,MATCH(E2,A:A,0),1):INDEX(C:C,MATCH(E2,A:A,1),1),2)

To wszystko łączy. To używa VLOOKUP szukać daty z F2, w zakresie komórek generowanym przez te dwa INDEX formuły (B7: C25) i zwrócenie drugiej kolumny (finał 2 ) w pasującym wierszu.

Voilá!