Jedna formuła, wiele wyników: listy, które rozlewają się na komórki poniżej.
Nowoczesny Excel i szybkość · 5 krótkich ćwiczeń w żywym arkuszu: sam wpisujesz każdą formułę i odpowiadaj na pytania, które każą przewidywać wyniki lub wykrywać błędy. Każda odpowiedź jest sprawdzana od razu.
Co będziesz ćwiczyć
Wyświetlanie unikatowych wartości za pomocą UNIQUE
Niektóre funkcje zwracają kilka wartości. Automatycznie *rozlewają się* na komórki poniżej. UNIKATOWE (Angielski: UNIQUE) zwraca każdą wartość tylko raz.
Dzięki tablicom dynamicznym jedna formuła może zwrócić wiele wartości, a one same wypełniają komórki poniżej albo obok. Nazywa się to rozlewaniem. Dzięki temu wiele dawnych, wieloetapowych zadań, na przykład budowanie posortowanej listy unikalnych wartości, da się wykonać jedną formułą.
Jedna formuła, wiele komórek
Wyobraź sobie listę schroniska dla zwierząt. A2:A7 zawiera typ zwierzęcia (Dog, Cat, Dog, Rabbit, Cat, Dog), a B2:B7 wiek (3, 5, 2, 1, 7, 4). Wpisz =UNIKATOWE(A2:A7) w D2, a Excel wypełni D2:D4 wartościami Dog, Cat i Rabbit. Wpisałeś tylko jedną formułę. Kolorowa ramka wokół D2:D4 to zakres rozlania.
Odwołaj się do całego rozlania znakiem #: =ILE.NIEPUSTYCH(D2#) liczy, ile jest teraz elementów, nawet jeśli dane zmienią się później.
Wiek od najstarszego do najmłodszego (7, 5, 4, 3, 2, 1). Zwierzęta starsze niż 3 (Cat, Cat, Dog). Różne typy zwierząt w kolejności alfabetycznej (Cat, Dog, Rabbit).
SORTUJ (Angielski: SORT) przyjmuje zakres, kolumnę, według której sortujesz, oraz 1 dla porządku rosnącego albo -1 dla malejącego. Aby posortować całą tabelę według wieku, podaj obie kolumny: =SORTUJ(A2:B7;2;-1). UNIKATOWE (Angielski: UNIQUE) zwraca każdą wartość raz. FILTRUJ (Angielski: FILTER) zachowuje wiersze, w których warunek jest PRAWDA.
Dla dwóch warunków pomnóż testy, co oznacza I: =FILTRUJ(B2:B7;(A2:A7="Dog")*(B2:B7>2)) zwraca wieki 3 i 4. Jeśli nic nie pasuje, FILTRUJ (Angielski: FILTER) podaje #OBL!. Trzeci argument pozwala wybrać własny komunikat: =FILTRUJ(A2:A7;B2:B7>10;"brak").
Łączenie funkcji
Ponieważ każda funkcja zwraca listę, możesz przekazać wynik jednej do następnej. Zacznij znów od listy schroniska (A2:A7 zwierzę, B2:B7 wiek). Aby uzyskać tylko zwierzęta starsze niż 2, najstarsze jako pierwsze, z oboma kolumnami widocznymi, zagnieźdź filtr w sortowaniu:
=SORTUJ(FILTRUJ(A2:B7;B2:B7>2);2;-1)
Filtr zostawia cztery wiersze (Dog 3, Cat 5, Cat 7, Dog 4). Sortowanie ustawia je według drugiej kolumny, od największej: Cat 7, Cat 5, Dog 4, Dog 3.
Czytaj zagnieżdżone formuły od środka: najpierw filtr, potem sortowanie. Jeśli długi łańcuch jest trudny do śledzenia, zbuduj go krok po kroku w osobnych komórkach, a gdy każdy krok będzie poprawny, połącz je w jedną formułę.
Błąd #ROZLANIE!
#ROZLANIE! oznacza, że komórki, do których wynik chce się rozlać, nie są puste. Wyczyść blokującą komórkę albo przenieś formułę. Ten sam błąd pojawia się, gdy wpiszesz formułę rozlewającą się w tabeli Excela, bo tabele nie mogą przechowywać rozlewania. Trzymaj formuły rozlewające się obok tabeli.
Stare pliki i znak @
W skoroszycie utworzonym w starszym Excelu możesz zobaczyć znak @ wewnątrz formuły. Oznacza on dawne zachowanie zwracające jedną wartość, zwane przecięciem niejawnym. Excel go dodaje, aby plik nadal dawał dokładnie takie same wyniki jak zawsze. W nowej formule zwykle nie jest potrzebny: wpisz formułę bez @ i pozwól jej się rozlać.
Wskazówki
Tablice dynamiczne są dostępne w Excelu 2021, Excelu 2024 i Microsoft 365. Starsze wersje pokazują #NAZWA?.
Nie wpisuj nic w zakres rozlania. Edytować można tylko formułę w lewej górnej komórce. Jeśli chcesz zachować stałe wartości, skopiuj rozlanie i wklej je jako wartości.
Połącz je: =ILE.NIEPUSTYCH(UNIKATOWE(A2:A7)) zwraca 3, czyli liczbę różnych typów zwierząt.