Użyj FILTER, aby zwrócić w Excelu dla komputerów Mac wszystkie ilości powiązane z etykietą zasobu graficznego. Wynikowa tablica może rozlać się na komórki arkusza, posłużyć jako dane wejściowe do kolejnego obliczenia lub zostać zsumowana bez utraty powtarzających się pasujących wpisów.

W tym przewodniku użyto syntetycznych etykiet zasobów graficznych i ilości. Zadanie polega na wybraniu jednej etykiety, pobraniu wszystkich jej ilości i ponownym wykorzystaniu tych poszczególnych liczb w obliczeniu. Microsoft opisuje FILTER jako funkcję zwracającą tablicę na podstawie określonych kryteriów. Funkcja FILTER.

Które wersje programu Excel dla komputerów Mac obsługują tę metodę?

W aktualnej dokumentacji funkcji Microsoft wymienia wprost programy Excel dla Microsoft 365 dla komputerów Mac, Excel 2024 dla komputerów Mac oraz Excel 2021 dla komputerów Mac jako obsługujące FILTER. Użyj jednej z tych wersji w poniższych formułach. Funkcja FILTER.

Aby sprawdzić swoją instalację, otwórz program Excel i wybierz Excel → Informacje o programie Excel. Microsoft opisuje tę ścieżkę menu w systemie Mac, a przewodnik identyfikacji wersji wyjaśnia, że okno Informacje w aplikacji Mac wyświetla numer wersji i typ licencji. Sprawdź zarówno edycję, jak i numer kompilacji. Udostępnianie dokumentów w innych wersjach pakietu Office dla komputerów Mac; Informacje o pakiecie Office: jakiej wersji pakietu Office używam?.

Programy Excel 2019 dla komputerów Mac i Excel 2016 dla komputerów Mac nie należą do wersji, w których udokumentowano dostępność FILTER. Jeśli używasz którejś z tych edycji, obsługiwanym sposobem pracy z tą konkretną procedurą opartą na tablicach dynamicznych jest otwarcie skoroszytu w jednej z wymienionych wersji na komputer Mac. Poniższe instrukcje wymagają spełnienia tego warunku wstępnego; nie są obejściem z użyciem starszej formuły tablicowej.

Przygotuj mały przykład ilości zasobów

W pustym arkuszu wprowadź poniższe dane w zakresie A1:B7. Pierwszy wiersz zawiera nagłówki. Wprowadź ilości jako liczby, w tym zero w B6.

Wiersz arkusza Kolumna A: Zasób Kolumna B: Ilość
1 Zasób Ilość
2 Kafelek A 4
3 Łuk B 7
4 Kafelek A 2
5 Kropka C 5
6 Kafelek A 0
7 Kafelek A 4

Wpisz Wybrany zasób w D1, a następnie Kafelek A w D2. Wpisz Pasujące ilości w F1. Pozostaw puste komórki pod F2, aby wynik miał miejsce na rozwinięcie.

W przykładzie celowo uwzględniono dwie ilości równe cztery i jedną ilość równą zero dla tej samej etykiety. Prawidłowy wynik musi zachować wszystkie cztery pasujące wiersze: 4, 2, 0 i 4. Usunięcie powtarzających się wartości lub pominięcie zera zmieniłoby żądany wynik.

Zwróć wszystkie pasujące ilości za pomocą funkcji FILTER

Zaznacz F2, wpisz poniższą formułę i naciśnij Return:

=FILTER(B2:B7,A2:A7=D2)

Formuła używa B2:B7 jako tablicy ilości, a A2:A7=D2 jako testu uwzględnienia. Zakresy obejmują te same sześć wierszy źródłowych, spełniając wymaganie Microsoftu, by tablica uwzględnienia miała zgodną wysokość lub szerokość. Funkcja FILTER.

Wynik dla przykładowych danych zajmuje komórki F2:F5:

Komórka Wynik
F2 4
F3 2
F4 0
F5 4

Wprowadź formułę tylko w F2; Excel automatycznie umieści dodatkowe wyniki w sąsiednich komórkach. Microsoft nazywa to zachowanie rozlewaniem. Aby później zmienić formułę, edytuj komórkę w lewym górnym rogu wyniku, czyli tutaj F2. Formuły tablic dynamicznych i zachowanie rozlewania tablic.

Dwa wystąpienia liczby cztery odpowiadają osobnym pasującym wierszom źródłowym. Zachowaj oba, gdy kolejne obliczenie musi uwzględniać każde wystąpienie wybranego zasobu.

Dlaczego funkcja XLOOKUP nie pobiera tych czterech wierszy

Microsoft opisuje XLOOKUP jako funkcję zwracającą element odpowiadający pierwszemu znalezionemu dopasowaniu. Może ona zwrócić wiele kolumn z pasującego wiersza, ale ta możliwość nie oznacza wyliczenia wszystkich wierszy zawierających tę samą etykietę. Funkcja XLOOKUP.

Na przykład poniższa formuła zwraca z przykładowych danych tylko 4:

=XLOOKUP(D2,A2:A7,B2:B7)

Taki wynik jest odpowiedni, gdy odpowiedź znajduje się w jednym pasującym wierszu. W tym zadaniu ilości znajdują się w czterech osobnych pasujących wierszach, więc użyj wyniku funkcji FILTER.

Wykorzystaj tablicę w kolejnych obliczeniach

Odwołaj się do całego wyniku za pomocą F2#. Microsoft opisuje znak krzyżyka jako operator zakresu rozlanego: odwołuje się do całej tablicy wygenerowanej z komórki początkowej. Dzięki temu formuła użyta w dalszych obliczeniach nie będzie ograniczona do obecnego wyniku zajmującego cztery komórki. Wskazówki i przykłady formuł tablicowych.

Umieszczaj formuły zależne obok wyniku, a nie bezpośrednio pod nim, aby zostawić miejsce na większą liczbę pasujących ilości. Poniższe przykłady wykorzystują kolumny H–J.

Dodaj wszystkie pasujące ilości

Wpisz Łączna ilość w H1, a następnie wpisz tę formułę w H2:

=SUM(F2#)

Przykładowy wynik to 10: cztery plus dwa plus zero plus cztery. Funkcja SUM przyjmuje zakresy i dodaje ich wartości liczbowe. Funkcja SUM.

Jeśli potrzebujesz tylko sumy, możesz obliczyć ją bezpośrednio w innej pustej komórce:

=SUM(FILTER(B2:B7,A2:A7=D2))

Wybierz widoczną tablicę, jeśli chcesz sprawdzić poszczególne ilości lub ponownie je wykorzystać. Wybierz zagnieżdżoną formułę, jeśli arkusz wymaga tylko końcowej sumy.

Pomnóż każdą ilość przez wspólny współczynnik

Załóżmy, że przykład wymaga trzech kopii każdej pasującej ilości. Wpisz Kopie w I1, a następnie liczbę 3 w I2. Wpisz Skorygowane ilości w J1, a następnie wpisz tę formułę w J2:

=F2#*$I$2

Oczekiwane wyniki w zakresie J2:J5 to 12, 6, 0 i 12. Microsoft opisuje obliczenia tablicowe, które mnożą wartości i zwracają wiele wyników, a w przewodniku kalkulatora dla komputerów Mac wskazuje gwiazdkę jako operator mnożenia. Wskazówki i przykłady formuł tablicowych; Używanie programu Excel jako kalkulatora w Excelu dla komputerów Mac.

Aby uzyskać jedną skorygowaną sumę, wpisz poniższą formułę w H4:

=SUM(F2#*$I$2)

Oczekiwana suma to 30. Są to przykładowe obliczenia na podstawie podanych danych syntetycznych. Działania arytmetyczne sprawdzono niezależnie; formuły skoroszytu zweryfikowano z dokumentacją Microsoftu, a nie przez ich uruchomienie w programie Excel dla komputerów Mac.

Zdecyduj, co ma się stać, gdy nic nie pasuje

Zmień D2 na Fala D, czyli etykietę, której nie ma w przykładzie. Oryginalna dwuargumentowa formuła FILTER zwraca #CALC!, ponieważ Excel nie może zwrócić pustej tablicy. Microsoft opisuje opcjonalny trzeci argument jako sposób określenia wartości zwracanej w przypadku braku wyników. Jak naprawić błąd #CALC!.

Wybierz tę wartość w zależności od potrzeb kolejnego obliczenia. Poniższe zestawienie dotyczy tego arkusza:

Wymaganie Formuła lub podejście Konsekwencja
Brakujące dopasowania powinny pozostać widoczne podczas obliczeń Zachowaj =FILTER(B2:B7,A2:A7=D2) Brak dopasowań zwraca #CALC!, co skłania do sprawdzenia danych.
Wynik ma być przede wszystkim czytelną listą Użyj =FILTER(B2:B7,A2:A7=D2,"No matches") Brak dopasowań zwraca komunikat tekstowy.
Brakująca etykieta powinna wnosić zero do sumy Użyj =SUM(FILTER(B2:B7,A2:A7=D2,0)) Brak dopasowań daje sumę równą zero.

Wartość domyślna zero to świadomie przyjęta reguła obliczeń. Nie oznacza, że istnieje pasujący wiersz źródłowy z ilością zero. W tym przykładzie zasób Kafelek A ma rzeczywistą ilość zero; dla Fala D nie ma żadnych wierszy źródłowych. Zachowaj to rozróżnienie zawsze wtedy, gdy obecność zasobu ma znaczenie.

Komunikat tekstowy również wymaga ostrożności: Microsoft dokumentuje, że funkcja SUM ignoruje tekst w odwołanych komórkach. W efekcie zsumowanie wyniku zawierającego wyłącznie Brak dopasowań może dać zero i ukryć brak pasujących wierszy. Jeśli obliczenia zależne powinny zostać zatrzymane przy brakującej etykiecie, użyj widocznego błędu. Funkcja SUM.

Przed kontynuowaniem pierwotnych przykładów liczbowych przywróć D2 do wartości Kafelek A.

Dodaj drugi warunek tylko wtedy, gdy wymaga tego zadanie

Jeśli kolejne zadanie wyraźnie wymaga dodatnich ilości, użyj testu etykiety i testu dodatniej ilości:

=FILTER(B2:B7,(A2:A7=D2)*(B2:B7>0))

Dla przykładowych danych zwraca to 4, 2 i 4. Microsoft opisuje mnożenie warunków jako test AND w funkcji FILTER. Ta wersja celowo pomija pasujące zero, dlatego zachowaj oryginalną formułę, gdy potrzebujesz każdej pasującej ilości. Funkcja FILTER.

Rozwiąż problemy z wynikiem i uwzględniaj nowe wiersze

Jeśli formuła nie zwraca oczekiwanych czterech ilości, sprawdź arkusz w następującej kolejności:

  1. Sprawdź miejsce na wynik. Zablokowany zakres wyniku powoduje błąd #SPILL!. Przed ponowną próbą przenieś potrzebne dane poza planowany obszar rozlania.
  2. Sprawdź położenie formuły. Formuły tablicowe rozlewające wynik należy umieszczać w siatce arkusza poza tabelą programu Excel.
  3. Sprawdź zależności skoroszytu. Microsoft obsługuje połączone tablice dynamiczne między skoroszytami tylko wtedy, gdy oba skoroszyty są otwarte; odświeżenie po zamknięciu źródła może spowodować błąd #REF!.

Te ograniczenia opisano w artykule Formuły tablic dynamicznych i zachowanie rozlewania tablic.

Następnie sprawdź etykiety i ilości źródłowe. Porównaj D2 z komórkami zawierającymi odpowiednie zasoby i upewnij się, że ilości wprowadzono jako liczby. Sama wiarygodnie wyglądająca suma nie wystarczy do weryfikacji, ponieważ funkcja SUM może ignorować wartości tekstowe. Funkcja SUM.

Przykładowe formuły odwołują się do wierszy źródłowych od 2 do 7. Jeśli dodasz dane w wierszu 8, edytuj oba zakresy źródłowe, aby je uwzględnić. Na przykład po wpisaniu Kafelek A w A8 i liczbowej ilości 3 w B8 zastąp formułę w F2 następującą formułą:

=FILTER(B2:B8,A2:A8=D2)

Oczekiwana tablica to 4, 2, 0, 4, 3, a suma wyniesie 13. Odwołanie F2# uwzględnia rozszerzony wynik, ale nie może sprawić, że pierwotne zakresy źródłowe obejmą pominięty wiersz.

W przypadku często rozrastającej się listy Microsoft zaleca tabelę programu Excel z odwołaniami strukturalnymi, które dostosowują się do dodawanych lub usuwanych wierszy tabeli. Umieść rozlewający się wynik poza tą tabelą. Formuły tablic dynamicznych i zachowanie rozlewania tablic.

Na koniec sprawdź poszczególne wyniki, zanim oprzesz się na sumie: dla sześciu pierwotnych wierszy źródłowych wybór Kafelek A powinien zwrócić cztery ilości, w tym obie czwórki i zero. Gdy wynik będzie się zgadzał, użyj F2# w obliczeniach i wybierz jawną regułę postępowania przy braku dopasowań, odpowiednią do celu arkusza.

Źródła