Użyj funkcji IFNA wokół rozlewanego wyszukiwania, aby uzupełnić jego wyniki #N/A danymi z drugiej tabeli referencyjnej. Ten przykład w programie Excel dla komputerów Mac dopasowuje etykiety zasobów graficznych, zachowuje ilości znalezione już w tabeli głównej i wyraźnie oznacza etykiety nieobecne w obu tabelach.

Które wersje programu Excel dla komputerów Mac obsługują to podejście?

Użyj programu Excel dla Microsoft 365 dla komputerów Mac, Excel 2024 dla komputerów Mac lub Excel 2021 dla komputerów Mac. Microsoft wymienia te wersje zarówno dla funkcji XLOOKUP, jak i IFNA. Dokumentacja funkcji XLOOKUP wyraźnie wyklucza programy Excel 2016 i Excel 2019, mimo że te wersje figurują na szerszej liście zgodności na stronie. Poniższe formuły są przeznaczone dla obsługiwanych wersji na komputery Mac. Funkcja XLOOKUP, funkcja IFNA

Zadanie jest konkretne: masz jedną kolumnę z żądanymi etykietami zasobów i chcesz uzyskać po jednej ilości dla każdej etykiety. Tabela główna ma pierwszeństwo; druga tabela dostarcza ilości tylko wtedy, gdy wyszukiwanie w tabeli głównej zwraca #N/A. Wszystkie poniższe etykiety i ilości są przykładowe.

Skonfiguruj dwie tabele referencyjne i listę żądanych zasobów

Zacznij od pustego arkusza w jednym skoroszycie. Użyj poniższych adresów komórek, aby formuły działały bez zmiany odwołań. Te niewielkie tabele referencyjne to zwykłe zakresy arkusza; nie musisz konwertować ich na obiekty tabel programu Excel.

Wprowadź tabelę główną w zakresie D1:E4:

Komórka w kolumnie D Etykieta zasobu Komórka w kolumnie E Ilość
D1 Zasób E1 Ilość
D2 Kontur liścia E2 8
D3 Tekstura kropek E3 0
D4 Wzór kafelkowy E4 12

Wprowadź drugą tabelę referencyjną w zakresie G1:H5:

Komórka w kolumnie G Etykieta zasobu Komórka w kolumnie H Ilość
G1 Zasób H1 Ilość
G2 Tekstura kropek H2 9
G3 Szkic krzywej H3 5
G4 Tekstura ziarna H4 3
G5 Kontur liścia H5 10

Wprowadź Żądany zasób w komórce J1, a następnie wpisz pięć poniższych etykiet w zakresie J2:J6, po jednej w komórce i w tej kolejności:

  1. Kontur liścia
  2. Tekstura kropek
  3. Szkic krzywej
  4. Tekstura ziarna
  5. Motyw łuku

Zachowaj wartości ilości jako liczby i wypełnij wszystkie komórki ilości w obu tabelach referencyjnych. W tym przykładzie zero oznacza zapisaną ilość równą zero. Nie oznacza, że zasobu brakuje.

Utwórz rozlaną listę etykiet

Wprowadź nagłówki Zasób, Ilość główna i Uzupełniona ilość w komórkach A1, B1 i C1. Pozostaw zakres A2:C20 pusty, a następnie wprowadź tę formułę w komórce A2:

=FILTER(J2:J20,J2:J20<>"","")

Formuła odfiltrowuje puste komórki z listy żądanych zasobów. Przy podanych danych rozlewa pięć etykiet zasobów do zakresu A2:A6. Microsoft dokumentuje funkcję FILTER dla trzech wymienionych wyżej wersji na komputery Mac i wyjaśnia, że jej ostatni argument określa wynik, gdy żadne wiersze nie spełniają warunku. W tym przypadku jest nim pusty ciąg. Funkcja FILTER

Późniejsze formuły używają A2#, aby odwołać się do całego tego rozlanego zakresu. Operator # dostosowuje się do rozmiaru rozlanego zakresu, dzięki czemu nie trzeba na sztywno wpisywać jego obecnego końca, takiego jak A6. Microsoft opisuje to działanie w dokumentacji operatora zakresu rozlanego.

Uzupełnij brakujące ilości danymi z drugiej tabeli referencyjnej

Najpierw utwórz wyszukiwanie w tabeli głównej w komórce B2:

=XLOOKUP(A2#,$D$2:$D$4,$E$2:$E$4)

Formuła przeszukuje kolumnę zasobów tabeli głównej i zwraca odpowiadające im ilości. Funkcja XLOOKUP domyślnie stosuje dokładne dopasowanie i zwraca #N/A, gdy nie znajdzie dopasowania i nie podano alternatywnego wyniku. Funkcja XLOOKUP

W podanym przykładzie zakres B2:B6 powinien zawierać kolejno 8, 0, #N/A, #N/A i #N/A. Pozostaw błędy oznaczające brak dopasowania do następnego kroku.

Teraz wprowadź formułę uzupełniającą w komórce C2:

=IFNA(B2#,XLOOKUP(A2#,$G$2:$G$5,$H$2:$H$5,"Not listed"))

Funkcja IFNA sprawdza rozlany zakres wyników wyszukiwania w tabeli głównej. W pozycjach zawierających #N/A formuła wstawia odpowiedni wynik z drugiego wyszukiwania. Pozostałe wyniki główne pozostają bez zmian. Microsoft wyraźnie dokumentuje, że funkcja IFNA zwraca tablicę wyników, gdy jej pierwszy argument jest formułą tablicową. Funkcja IFNA

Oczekiwany wynik w arkuszu:

Zasób w kolumnie A Ilość główna w kolumnie B Uzupełniona ilość w kolumnie C
Kontur liścia 8 8
Tekstura kropek 0 0
Szkic krzywej #N/A 5
Tekstura ziarna #N/A 3
Motyw łuku #N/A Nie ma na liście

Odczytaj te wiersze jako test pierwszeństwa. Kontur liścia pozostaje 8, mimo że druga tabela referencyjna zawiera 10. Tekstura kropek pozostaje 0, mimo że druga tabela referencyjna zawiera 9. Szkic krzywej i tekstura ziarna otrzymują ilości z drugiej tabeli. Motyw łuku pozostaje wyraźnie nierozwiązany, ponieważ nie ma go w żadnej z tabel referencyjnych.

Obie tablice muszą opisywać te same żądane zasoby w tej samej kolejności. W tej konfiguracji obie pochodzą z A2#. Jeśli dostosowujesz istniejący skoroszyt, nie łącz wyników głównych posortowanych w jeden sposób z wyszukiwaniem uzupełniającym opartym na liście etykiet w innej kolejności.

Użyj jednej formuły, jeśli nie potrzebujesz kolumny pośredniej

Aby połączyć wyszukiwanie główne i uzupełnianie w jednej formule, zastąp formułę w komórce C2 poniższym kompletnym wariantem:

=IFNA(
    XLOOKUP(A2#,$D$2:$D$4,$E$2:$E$4),
    XLOOKUP(A2#,$G$2:$G$5,$H$2:$H$5,"Not listed")
)

Formuła korzysta z tych samych przykładowych danych i powinna zwrócić te same pięć uzupełnionych ilości. Kolumna B nie jest już potrzebna do obliczeń. Zachowanie wersji dwuetapowej podczas konfigurowania skoroszytu ułatwia sprawdzenie, z której tabeli pochodzi każdy wynik.

Ustal, czy wyniku rzeczywiście brakuje

Zanim dostosujesz formułę, sprawdź, co zwraca pierwsze wyszukiwanie. Brakująca etykieta, wynik wyglądający na pusty i ilość równa zero wymagają różnych decyzji.

Wynik główny Działanie tej formuły uzupełniającej Co sprawdzić
Wartość liczbowa, w tym 0 Zachowuje ją Sprawdź, czy jest to zamierzona ilość główna.
#N/A Używa wyszukiwania w drugiej tabeli Sprawdź, czy błąd oznacza brak etykiety.
Pusty ciąg, "" Zachowuje go Sprawdź, czy formuła główna ukrywa brakujące dopasowania.
Inny błąd Pozostawia go widocznym Napraw formułę lub odwołanie będące źródłem problemu.

Na przykład jeśli istniejące wyszukiwanie główne używa "" jako argumentu if_not_found, funkcja IFNA nie rozpozna tego pustego ciągu jako #N/A. Usuń ten argument z wyszukiwania głównego, jak pokazano w B2, albo użyj powyższej połączonej formuły. Logika uzupełniania wymaga zachowania rozpoznawalnego wyniku oznaczającego brak dopasowania. Funkcja XLOOKUP, funkcja IFNA

Sprawdź także same ilości w tabelach referencyjnych. Funkcja IFNA reaguje na wartość błędu; nie sprawdza, czy komórka ilości została wypełniona. Jeśli dopasowany rekord główny ma nieuzupełnioną ilość, uzupełnij ten rekord albo określ osobną regułę dla nieuzupełnionych ilości przed zastosowaniem tego sposobu.

Jest jeszcze jedno rozróżnienie: istniejący błąd #N/A w danych ilości tabeli głównej również uruchomi to wyszukiwanie uzupełniające. W tym przykładzie wymagane są czyste numeryczne kolumny ilości, w których #N/A wynika wyłącznie z nieudanego wyszukiwania. Sprawdź ten warunek, zanim uznasz każde zastąpienie za brakujące dopasowanie zasobu.

Sprawdź pierwszeństwo i upewnij się, że rozlewanie działa

Przed wstawieniem własnych etykiet zasobów użyj pięciu wierszy z przykładu jako małego testu akceptacyjnego. Razem sprawdzają dopasowanie główne, prawidłowe zero, dwa dopasowania w drugiej tabeli oraz etykietę nieobecną w obu tabelach referencyjnych.

Dla dodatkowego sprawdzenia tymczasowo zmień ilość konturu liścia w drugiej tabeli z 10 na 14. Uzupełniona ilość powinna pozostać równa 8. Następnie zmień ilość szkicu krzywej w drugiej tabeli z 5 na 6; jego uzupełniona ilość powinna wynosić 6. Potem przywróć pierwotne wartości. Są to przykładowe kontrole dla Twojego skoroszytu, a nie zgłoszone wyniki testów programu Excel.

W każdej tabeli referencyjnej zachowaj jeden zamierzony rekord na etykietę zasobu. Funkcja XLOOKUP zwykle przeszukuje pozycje od początku i zwraca pierwsze dopasowanie, więc nie należy oczekiwać, że powtarzające się etykiety połączą ilości. Przed poleganiem na tym schemacie wyszukiwania rozstrzygnij, co oznaczają zduplikowane etykiety. Funkcja XLOOKUP

Odblokuj zakres wyników

Każdą formułę rozlewającą wprowadź tylko w jej komórce początkowej. Excel automatycznie wypełni sąsiednie komórki, a edytować można tylko lewą górną komórkę rozlanego zakresu. Umieszczaj formuły poza obiektami tabel programu Excel; Microsoft obsługuje tabele jako dane źródłowe, ale nie obsługuje formuł rozlewanych wewnątrz nich. Formuły tablic dynamicznych i działanie rozlewanych tablic

Jeśli zobaczysz #SPILL!, zaznacz komórkę formuły, aby wyświetlić zamierzony obszar wyników. Jeśli jest dostępna, użyj opcji Select Obstructing Cells z alertu błędu i przenieś istniejące wpisy blokujące wynik. Scalone komórki również mogą uniemożliwiać rozlewanie; przenieś wynik w inne miejsce albo rozdziel objęte problemem komórki. Jak naprawić błąd #SPILL!

Celowo rozszerzaj dane wejściowe

Formuła listy żądanych zasobów sprawdza zakres J2:J20. Etykiety dodane w tym zakresie są uwzględniane automatycznie; w przypadku etykiet wpisanych poniżej trzeba rozszerzyć zakres formuły. Wyszukiwania w tabelach referencyjnych korzystają też ze stałych zakresów kończących się w wierszach 4 i 5. Dodając rekordy, rozszerzaj jednocześnie każdy zakres etykiet zasobów i odpowiadający mu zakres ilości.

W większym arkuszu tabele programu Excel z odwołaniami strukturalnymi mogą automatycznie dostosowywać zakresy źródłowe po dodaniu lub usunięciu wierszy. Formuły wynikowe pozostaw w siatce arkusza poza tymi tabelami. Formuły tablic dynamicznych i działanie rozlewanych tablic

Dane wejściowe z tego przykładu zachowaj w tym samym skoroszycie. Microsoft dokumentuje ograniczoną obsługę tablic dynamicznych między skoroszytami: połączone tablice dynamiczne wymagają, aby oba skoroszyty były otwarte, a odświeżenie po zamknięciu źródłowego skoroszytu może spowodować błąd #REF!. Formuły tablic dynamicznych i działanie rozlewanych tablic

Gdy oczekiwane wiersze będą się zgadzać, użyj uzupełnionej kolumny jako wyniku ilości zasobów. Zachowaj zasadę pierwszeństwa tabeli głównej, nie zmieniaj znaczenia zer i oznaczaj zasoby nieobecne w obu tabelach jako Nie ma na liście, aby świadomie uzupełnić dane referencyjne.

Źródła