Przyjrzyj się tej liczbie:
Załóżmy, że chcesz wygenerować z tego raport, tak jakbyś filtrował w Regionie. Oznacza to, że jeśli filtrujesz na północ, zobaczysz:
Ale co by było, gdybyś chciał mieć wersję tego samego opartą na formule?
Oto wynik, którego szukasz w kolumnach I: K:
Oczywiście jest to ten sam raport, ale nie ma tutaj przefiltrowanych elementów. Jeśli chcesz mieć nowy raport na temat Wschodu, dobrze byłoby po prostu zmienić wartość w G1 na Wschód:
Oto jak to się robi. Przede wszystkim nie jest to wykonywane przy użyciu funkcji WYSZUKAJ. Więc skłamałem co do tytułu tej techniki!
Kolumna F nie była wcześniej wyświetlana i można ją ukryć (lub przenieść w inne miejsce, aby nie kolidowała z raportem).
To, co pokazano w kolumnie F, to numery wierszy, w których G1 znajduje się w kolumnie A; to znaczy, które wiersze zawierają wartość „Północ”? Technika ta polega na wykorzystaniu komórkę wyżej, więc musi rozpocząć się co najmniej rzędu 2. To odpowiada wartości „Północ” przeciw kolumnie A, ale zamiast całej kolumny, użyj przesunięcie funkcji: OFFSET($A$1,F1,0,1000,1).
Ponieważ F1 wynosi 0, to OFFSET(A1,0,0,1000,1)jest A1: A1000. (1000 jest dowolne, ale wystarczająco duże, aby wykonać zadanie - możesz ustawić dowolną inną liczbę).
Wartość 2 w F2 to pierwsza „Północ”. Chcesz również dodać z powrotem wartość F1 na końcu, ale jak dotąd jest to zero.
„Magia” ożywa w celi F3. Wiesz już, że pierwsza północ znajduje się w wierszu 2. Więc chcesz rozpocząć wyszukiwanie dwóch wierszy poniżej A1. Możesz to zrobić, podając 2 jako drugi argument funkcji PRZESUNIĘCIE.
Formuła w F3 automatycznie wskazywać na 2, która została obliczona w komórce F2: Przy kopiowaniu formuły w dół, a zobaczysz =OFFSET($A$1,F2,0,1000,1)co jest OFFSET($A$1,2,0,1000,1)co jest A3: A1000. Więc dopasowujesz północ do tego nowego zakresu, a północ znajduje się w trzeciej komórce tego nowego zakresu, więc PODAJNIJ daje 3.
Dodając wartość z komórki powyżej, F2, zobaczysz 3 plus 2 lub 5, czyli wiersz zawierający drugą północ.
Ta formuła jest wypełniona wystarczająco daleko, aby uzyskać wszystkie wartości.
W ten sposób uzyskasz numery wierszy, w których znajdują się wszystkie rekordy północy.
Jak przetłumaczyć te numery wierszy na wyniki w kolumnach od I do K? Wszystko odbywa się za pomocą jednej formuły. Wprowadzić tę formułę w I2: =IFERROR(INDEX(A:A,$F2),””). Skopiuj w prawo, a następnie skopiuj w dół.
Dlaczego warto używać funkcji IFERROR? Gdzie jest błąd? Zwróć uwagę na komórkę F6 - zawiera # N / A (dlatego chciałbyś ukryć kolumnę F), ponieważ po wierszu 15 nie ma już północy. Więc jeśli kolumna F jest błędem, zwróć puste miejsce. W przeciwnym razie weź wartość z kolumny A (a po wypełnieniu po prawej stronie B i C).
$ F2 jest bezwzględnym odniesieniem do kolumny F, więc prawe wypełnienie nadal odnosi się do kolumny F.
Ten artykuł gościnny pochodzi od MVP programu Excel, Boba Umlasa. Jest to jedna z jego ulubionych technik z jego książki „Excel Outside the Box”.
Excel nieszablonowy »
