Formuła programu Excel: wyodrębnij wszystkie dopasowania za pomocą kolumny pomocniczej -

Spisie treści

Formuła ogólna

=IF(rowcheck,INDEX(data,MATCH(rownum,helper,0),column),"")

Podsumowanie

Jednym ze sposobów wyodrębnienia wielu dopasowań w programie Excel jest użycie INDEKSU i PODAJNIKA z kolumną pomocniczą, która oznacza pasujące dane. Pozwala to uniknąć złożoności bardziej zaawansowanej formuły tablicowej. W pokazanym przykładzie formuła w H6 to:

=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")

gdzie ct (G3), dane (B3: E52) i pomocnik (E3: E52) to nazwane zakresy.

Wyjaśnienie

Wyzwaniem związanym z formułami wyszukiwania, które pobierają więcej niż jedno dopasowanie, jest zarządzanie duplikatami (tj. Wieloma dopasowaniami). Formuły wyszukiwania, takie jak WYSZUKAJ.PIONOWO i INDEKS + PODAJ.POZYCJĘ, mogą łatwo znaleźć pierwsze dopasowanie, ale znacznie trudniej jest wyszukać „wszystkie dopasowania”, gdy kryteria zawierają więcej niż jedno dopasowanie.

Ta formuła radzi sobie z tym wyzwaniem, używając kolumny pomocniczej, która zwraca wartość liczbową, której można użyć do łatwego wyodrębnienia wielu dopasowań. Formuła w kolumnie pomocnika wygląda następująco:

=SUM(E2,AND(C3=$I$3,D3=$J$3))

Kolumna pomocnicza sprawdza każdy wiersz w danych, aby sprawdzić, czy dział w kolumnie C jest zgodny z wartością w I3, a budynek w kolumnie D jest zgodny z wartością w J3. Oba testy logiczne muszą zwracać wartość TRUE, aby AND zwróciło wartość TRUE.

Dla każdego wiersza wynik funkcji AND jest dodawany do „wartości powyżej” w kolumnie pomocnika w celu wygenerowania liczby. Praktycznym efektem tej formuły jest zwiększający się licznik, który zmienia się tylko wtedy, gdy zostanie znalezione (nowe) dopasowanie. Następnie wartość pozostaje taka sama aż do znalezienia następnego dopasowania. Działa to, ponieważ wyniki PRAWDA / FAŁSZ zwracane przez operację AND są przekształcane do wartości 1/0 jako część operacji sumowania. Wyniki FALSE nic nie dodają, a wyniki TRUE dodają 1.

W obszarze wyodrębniania formuła wyszukiwania nazwy w kolumnie H wygląda następująco:

=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")

Pracując od wewnątrz, część INDEKS + PODAJ.POZYCJĘ wyszukuje nazwę pierwszego znalezionego dopasowania, używając numeru wiersza w kolumnie G jako wartości dopasowania:

INDEX(data,MATCH($G6,helper,0),1)

INDEKS otrzymuje wszystkie 3 kolumny danych jako tablicę (nazwany zakres „dane”), a PODAJNIKA jest skonfigurowane tak, aby dopasować numer wiersza w kolumnie pomocnika (nazwany zakres „pomocnik”) w trybie dopasowania ścisłego (trzeci argument ustawiony na zero) .

Tu widać sprytność formuły. Kolumna pomocnicza oczywiście zawiera duplikaty, ale to nie ma znaczenia, ponieważ PODAJ.POZYCJĘ dopasuje tylko pierwszą wartość. Zgodnie z projektem każda „pierwsza wartość” odpowiada właściwemu wierszowi w tabeli danych.

Formuły w kolumnach I i J są takie same jak H, z wyjątkiem numeru kolumny, który w każdym przypadku jest zwiększany o jeden.

Instrukcja JEŻELI zawijająca formułę INDEKS / PODAJ.POZYCJĘ wykonuje prostą funkcję - sprawdza każdy numer wiersza w obszarze wyodrębniania, aby zobaczyć, czy numer wiersza jest mniejszy lub równy wartości w G3 (nazwany zakres „ct”), czyli łączna liczba wszystkich pasujących rekordów. Jeśli tak, logika INDEX / MATCH jest uruchamiana. Jeśli nie, funkcja JEŻELI zwraca pusty ciąg („”).

Formuła w G3 (nazwany zakres „ct”) jest prosta:

=MAX(helper)

Ponieważ maksymalna wartość w kolumnie pomocnika jest taka sama jak całkowita liczba dopasowań, potrzebujemy tylko funkcji MAX.

Uwaga: obszar wyodrębniania należy skonfigurować ręcznie, aby obsługiwał tyle danych, ile potrzeba (tj. 5 rzędów, 10 rzędów, 20 rzędów itd.). W tym przykładzie jest ograniczona do 5 wierszy, aby zachować zwartość arkusza.

Nauczyłem się tej techniki z książki Mike'a Girvina Control + Shift + Enter.