Użyj funkcji INDEX z dwiema funkcjami MATCH z dokładnym dopasowaniem, aby zwrócić ilość z przecięcia wiersza zasobu i kolumny formatu. W tym poradniku utworzysz mały arkusz z zasobami graficznymi, poznasz formułę i zobaczysz alternatywne rozwiązanie z zagnieżdżoną funkcją XLOOKUP dla obsługiwanych wersji programu Excel na komputery Mac.

Która metoda wyszukiwania działa w programie Excel dla komputerów Mac?

W tym zadaniu masz nazwę zasobu, na przykład Dot Grid, i format, na przykład PNG. Chcesz uzyskać pojedynczą ilość z miejsca, w którym się przecinają. Takie działanie nazywa się wyszukiwaniem dwukierunkowym.

Firma Microsoft podaje, że funkcje INDEX i MATCH są dostępne w programach Excel dla Microsoft 365 dla komputerów Mac, Excel 2024 dla komputerów Mac i Excel 2021 dla komputerów Mac. Obie metody opisane w tym artykule działają w tych wersjach programu Excel na komputery Mac. Zobacz dokumentację firmy Microsoft dotyczącą funkcji INDEX i funkcji MATCH.

Funkcja XLOOKUP jest również udokumentowana dla tych wersji programu Excel na komputery Mac. Firma Microsoft wyraźnie zaznacza, że nie jest dostępna w programach Excel 2016 i Excel 2019, mimo że te wersje widnieją na szerszej liście zgodności na stronie. Jeśli Twoja wersja nie obsługuje funkcji XLOOKUP, użyj metody z funkcjami INDEX i MATCH. Microsoft: funkcja XLOOKUP

Na początek użyj funkcji INDEX i MATCH, jeśli chcesz osobno sprawdzić pozycje wiersza i kolumny podczas nauki. Wybierz zagnieżdżoną funkcję XLOOKUP, jeśli już z niej korzystasz i wolisz jej domyślne dokładne dopasowanie.

Przygotuj małą tabelę ilości zasobów graficznych

W pustym arkuszu wpisz poniższe przykładowe dane w zakresie A1:D5. Pierwsza kolumna tabeli poniżej odpowiada kolumnie arkusza A; nie dodawaj dodatkowej kolumny z numerami wierszy. Podane ilości mają charakter przykładowy.

Zasób SVG PNG JPG
Tile Arc 12 18 6
Dot Grid 8 24 10
Loop Ribbon 15 9 0
Soft Square 7 14 5

Każdy zasób zajmuje jeden wiersz, a każdy format jedną kolumnę. W tym przykładzie użyj niepowtarzających się nazw zasobów i nagłówków formatów. Jeśli znana ilość wynosi zero, wpisz jawnie wartość liczbową zero; nierozstrzygnięte ilości sprawdź przed użyciem tabeli.

Utwórz osobny obszar wyszukiwania w tym samym arkuszu:

Komórka Wpisz
F1 Zasób do znalezienia
F2 Dot Grid
G1 Format do znalezienia
G2 PNG
H1 Ilość

Każdy z trzech zakresów źródłowych pełni inną funkcję: A2:A5 zawiera nazwy zasobów, B1:D1 — nagłówki formatów, a B2:D5 — wyłącznie ilości. Umieszczenie nagłówków poza zakresem ilości sprawia, że pozycje względne są prawidłowo dopasowane.

Zwróć ilość za pomocą funkcji INDEX i dwóch funkcji MATCH

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

=INDEX($B$2:$D$5,MATCH(F2,$A$2:$A$5,0),MATCH(G2,$B$1:$D$1,0))

Wynikiem powinna być wartość 24: ilość dla zasobu Dot Grid w kolumnie PNG. Instrukcje firmy Microsoft dla komputerów Mac opisują zaznaczenie komórki na wynik, wpisanie formuły zaczynającej się od = i naciśnięcie klawisza Return. Microsoft: wpisywanie formuły

Zrozum pozycje wiersza i kolumny

Pierwsza funkcja MATCH znajduje pozycję zasobu w zakresie A2:A5, a druga — pozycję formatu w zakresie B1:D1. Ostatni argument 0 wymusza dokładne dopasowanie. Bez tego argumentu funkcja MATCH domyślnie wykonuje dopasowanie przybliżone, które wymaga uporządkowania danych. Microsoft: funkcja MATCH

Dla podanych wartości wejściowych dwa obliczenia wewnętrzne wyglądają następująco:

=MATCH(F2,$A$2:$A$5,0)

Wynik: 2.

=MATCH(G2,$B$1:$D$1,0)

Wynik: 2.

Pełna formuła wybiera więc wiersz 2 i kolumnę 2 w bloku ilości:

=INDEX($B$2:$D$5,2,2)

To komórka arkusza C3, zawierająca wartość 24. Funkcja INDEX używa pozycji w podanym zakresie, dlatego w tym przypadku jej drugi wiersz odpowiada wierszowi 3 arkusza. Firma Microsoft dokumentuje, że podanie obu pozycji zwraca wartość z ich przecięcia. Microsoft: funkcja INDEX

Zablokuj zakresy źródłowe podczas kopiowania formuły

Znaki dolara blokują zakresy źródłowe. Odwołania do danych wejściowych F2 i G2 pozostają względne, więc skopiowanie formuły H2 w dół do H3 sprawi, że następna formuła odczyta F3 i G3, korzystając z tej samej tabeli źródłowej. Na komputerze Mac możesz też zaznaczyć odwołanie na pasku formuły i nacisnąć Command+T, aby przełączać typy odwołań. Microsoft: przełączanie między odwołaniami względnymi i bezwzględnymi

Na przykład wpisz Tile Arc w komórce F3, a JPG w G3, a następnie skopiuj H2 do H3. Nowy wynik powinien wynosić 6. Dzięki temu utworzysz krótką listę zapytań bez zmieniania oryginalnej tabeli zasobów.

Użyj zagnieżdżonej funkcji XLOOKUP w obsługiwanych wersjach programu Excel na komputery Mac

W tym samym arkuszu i przy tych samych danych wyszukiwania możesz zamiast tego wpisać tę formułę w komórce H2:

=XLOOKUP(F2,$A$2:$A$5,XLOOKUP(G2,$B$1:$D$1,$B$2:$D$5))

Formuła zwraca 24 dla zasobu Dot Grid i formatu PNG. Dokumentacja firmy Microsoft zawiera przykład z zagnieżdżoną funkcją XLOOKUP, która wykonuje wyszukiwanie pionowe i poziome, aby zwrócić wartość z przecięcia. Dokładne dopasowanie jest ustawieniem domyślnym. Microsoft: funkcja XLOOKUP

W tym przykładzie wewnętrzne wyszukiwanie wybiera kolumnę ilości dla formatu PNG: 18, 24, 9 i 14. Zewnętrzne wyszukiwanie wybiera wpis odpowiadający zasobowi Dot Grid. Użycie tych samych trzech zakresów źródłowych ułatwia porównanie tej metody z wcześniejszą formułą.

Sprawdź wynik przed użyciem większej tabeli

Użyj poniższych przypadków jako krótkiego testu akceptacyjnego. W każdym wierszu zmień wartości F2 i G2, a następnie porównaj H2 z oczekiwanym wynikiem. Jeśli chcesz zachować pierwotny przykład, przywróć później wartości Dot Grid i PNG.

Wartość zasobu Wartość formatu Oczekiwany wynik Co sprawdza
Dot Grid PNG 24 Przecięcie w środku tabeli
Tile Arc JPG 6 Pierwszy zasób i ostatni format
Soft Square SVG 7 Ostatni zasób i pierwszy format
Loop Ribbon JPG 0 Jawnie podana ilość zero
Tile Arcs PNG #N/A Brak nazwy zasobu
Dot Grid TIFF #N/A Brak nagłówka formatu

Jeśli wynik liczbowy jest nieprawidłowy, zanim zmienisz metodę wyszukiwania, sprawdź granice zakresów. W tej tabeli zakres A2:A5 musi odpowiadać czterem wierszom zakresu B2:D5, a zakres B1:D1 musi odpowiadać jego trzem kolumnom. Na przykład uwzględnienie A1 tylko w zakresie wyszukiwania zasobów przesuwa obliczoną pozycję poza właściwy wiersz ilości.

Jeśli rozszerzysz tę tabelę o kolejny zasób w wierszu 6, zaktualizuj zakres zasobów do A2:A6 i blok ilości do B2:D6. Dodając format w kolumnie E, zaktualizuj zakres nagłówków do B1:E1 i blok ilości do B2:E5. Te zmiany zakresów parami zachowują układ wymagany przez formuły.

Duplikaty nazw również wymagają uwagi. Funkcja MATCH z dokładnym dopasowaniem zwraca pierwszą pasującą pozycję i nie rozróżnia wielkich i małych liter; w wyszukiwaniu tekstu interpretuje też znaki * i ? jako symbole wieloznaczne. W tej konfiguracji używaj niepowtarzających się nazw, które nie zawierają tych znaków. Microsoft: funkcja MATCH

Wyświetl czytelny komunikat, gdy brakuje etykiety

Gdy podstawowe wyszukiwanie działa, możesz zastąpić błąd braku dopasowania czytelnym komunikatem:

=IFNA(INDEX($B$2:$D$5,MATCH(F2,$A$2:$A$5,0),MATCH(G2,$B$1:$D$1,0)),"Check asset or format")

Funkcja IFNA wyświetla podany komunikat, gdy ujęta w nią formuła zwraca błąd #N/A; w pozostałych przypadkach pozostawia wynik bez zmian. Jest udokumentowana dla programów Excel dla Microsoft 365 dla komputerów Mac, Excel 2024 dla komputerów Mac i Excel 2021 dla komputerów Mac. Microsoft: funkcja IFNA

W tym przykładzie zasób Tile Arcs zwraca komunikat Sprawdź zasób lub format, a dla zasobu Loop Ribbon z formatem JPG nadal zwracana jest wartość 0. Dzięki temu brak etykiety jest odróżniony od zapisanej ilości równej zero. Jeśli komórka źródłowa z ilością sama zawiera błąd #N/A, ta formuła go również zastąpi, dlatego sprawdź komórkę źródłową, gdy etykiety wydają się prawidłowe.

Do codziennej pracy używaj niepowtarzających się nazw zasobów, zachowaj zgodność trzech zakresów źródłowych i po zmianie tabeli sprawdź kilka znanych przecięć. Następnie możesz pobrać ilość dowolnego potrzebnego zasobu graficznego, zmieniając wartości wejściowe zasobu i formatu.

Źródła