Aby wyszukiwanie zapasów w Excelu wybierało najbardziej szczegółową pasującą regułę, najpierw wskaż kwalifikujące się wiersze, a następnie policz określone w nich pola. Zwróć ilość z jedynego wiersza z najwyższym wynikiem i oznacz remisy. Ten przewodnik pokazuje, jak uzyskać taki wynik dla syntetycznych zasobów graficznych w Excelu dla Maca.

Które wersje Excela dla Maca obsługują to podejście?

Poniższa formuła korzysta z LET, XLOOKUP i obliczeń tablic dynamicznych. Microsoft dokumentuje te funkcje dla programów Excel dla Microsoft 365 dla Maca, Excel 2024 dla Maca i Excel 2021 dla Maca. Przewodnik po funkcjach programu Excel 2021 dla Maca wyraźnie wymienia LET, XLOOKUP i tablice dynamiczne. Co nowego w Excelu 2021 dla Maca, Funkcja LET.

Excel 2016 i Excel 2019 nie obsługują XLOOKUP, zgodnie z wyraźną informacją Microsoftu o zgodności. Do tej formuły użyj jednej z obsługiwanych edycji na Maca; zmiana sposobu wprowadzania formuły nie zapewni brakującej funkcji. Opcjonalna formuła inspekcyjna w dalszej części tego artykułu korzysta z FILTER, również udokumentowanej dla trzech wymienionych wyżej obsługiwanych edycji na Maca. Funkcja XLOOKUP, Funkcja FILTER.

Co w Twoim zestawieniu oznacza „najbardziej szczegółowa” reguła?

W tym przykładzie wyszukujesz ilość przypisaną do zasobu graficznego na podstawie trzech atrybutów: etykiety zasobu, wykończenia i rozmiaru. Puste pole reguły oznacza, że reguła akceptuje dowolną wartość tego atrybutu. Wypełnione pole musi być równe odpowiedniej wartości wejściowej. Spośród kwalifikujących się reguł wygrywa ta, która ma najwięcej wypełnionych pól.

To jawna zasada skoroszytu przyjęta na potrzeby tego przykładu. Excel nie wywnioskuje jej z Twoich opisów. Tutaj wszystkie atrybuty mają taką samą wagę: reguła określająca zasób i wykończenie ma taki sam poziom szczegółowości jak reguła określająca zasób i rozmiar. Jeśli obie pasują i mają najwyższy wynik, formuła zgłasza remis.

Zwykła funkcja XLOOKUP sama nie rozstrzygnie tej kolejności. Microsoft dokumentuje, że domyślnie zwraca pierwszy pasujący element. Odwrócenie kierunku wyszukiwania zmienia stronę, od której zaczyna, ale nie szereguje reguł według szczegółowości. Dlatego formuła najpierw oblicza zwycięski wiersz, a dopiero potem pobiera jego ilość za pomocą XLOOKUP. Funkcja XLOOKUP.

Skonfiguruj przykładowe reguły dla zasobów graficznych

W pustym arkuszu wpisz poniższe nagłówki i wartości w zakresie A1:D6. Puste komórki w tabeli muszą pozostać puste w Excelu. Wszystkie etykiety i ilości mają charakter przykładowy.

Wiersz arkusza A: Zasób B: Wykończenie C: Rozmiar D: Ilość
2 30
3 Tile 18
4 Tile Matte 12
5 Tile Matte Small 7
6 Disc Large 10

Reguła w wierszu 2 jest celową regułą domyślną: nie określa żadnych atrybutów, więc jej poziom szczegółowości wynosi zero. Wiersz 3 określa jeden atrybut, wiersz 4 — dwa, a wiersz 5 — trzy. Te ilości są alternatywnymi przypisanymi wartościami, więc wyszukiwanie zwraca jedną z nich. Nie sumuje ilości z reguły domyślnej i wyjątków.

Wpisz nagłówki Asset, Finish, Size i Result w zakresie H1:K1. Następnie wpisz Tile w H2, Matte w I2 i Small w J2. Uzupełnij wszystkie trzy atrybuty wejściowe. W tym układzie puste pola oznaczają nieograniczone warunki tylko w tabeli reguł; brakujące dane wejściowe powodują wyświetlenie komunikatu.

Używaj wartości liczbowych, w tym liczbowego zera, jeśli ma zastosowanie. Ogranicz zakresy do wierszy z faktycznymi regułami. Rozszerzenie ich na niewykorzystane puste wiersze wprowadziłoby dodatkowe reguły z samymi pustymi polami, zgodnie z przyjętymi tu zasadami dopasowania.

Wprowadź formułę wybierającą zwycięską regułę

Zaznacz K2, wprowadź pełną formułę poniżej i naciśnij Return. Korzysta ona z tabeli reguł i komórek wejściowych zdefiniowanych wyżej. Obliczenia tablic dynamicznych są wykonywane w ramach formuły, a końcowy wynik zajmuje jedną komórkę. Dokumentacja Microsoftu opisuje zwykłe wprowadzanie formuł tablic dynamicznych, bez dawnych skrótów klawiaturowych do wprowadzania tablic. Formuły tablic dynamicznych i zachowanie rozlanych tablic.

=IF(OR(H2="",I2="",J2=""),"Complete the asset details",
  LET(
    asset,$A$2:$A$6,
    finish,$B$2:$B$6,
    size,$C$2:$C$6,
    qty,$D$2:$D$6,
    eligible,
      (((asset="")+(asset=H2))>0)*
      (((finish="")+(finish=I2))>0)*
      (((size="")+(size=J2))>0),
    specificity,--(asset<>"")+--(finish<>"")+--(size<>""),
    best,MAX(IF(eligible,specificity,-1)),
    winner,eligible*(specificity=best),
    IF(SUM(eligible)=0,"No matching rule",
      IF(SUM(winner)>1,"Tie: review rules",
        IF(XLOOKUP(1,winner,ISNUMBER(qty),FALSE,0),
          XLOOKUP(1,winner,qty,"No matching rule",0),
          "Check rule quantity"
        )
      )
    )
  )
)

Dla przykładowych danych wejściowych K2 powinno zwrócić 7. Kwalifikują się cztery reguły, ale tylko reguła w wierszu 5 określa wszystkie trzy atrybuty.

Jak działają kwalifikacja i poziom szczegółowości

LET przypisuje zakresom i wynikom pośrednim lokalne nazwy, dzięki czemu łatwiej prześledzić proces wyboru. Microsoft dokumentuje, że nazwy te obowiązują w zakresie formuły. Funkcja LET.

Dla każdego atrybutu wyrażenie kwalifikujące sprawdza, czy komórka reguły jest pusta albo równa wartości wejściowej. Dodawanie łączy te dwie możliwości, a porównanie przekształca wynik w test logiczny. Mnożenie trzech testów atrybutów wymaga spełnienia wszystkich warunków. W przykładach funkcji FILTER Microsoft stosuje ten sam wzorzec mnożenia do łączenia kryteriów tablicowych za pomocą logiki AND. Funkcja FILTER.

specificity liczy wypełnione pola reguły. Podwójne operatory jednoargumentowe przekształcają każdy test niepustości w liczbę, dając wyniki od zera do trzech. IF przypisuje niekwalifikującym się wierszom wynik minus jeden na potrzeby obliczenia rankingu; następnie MAX znajduje najwyższy wynik spośród kwalifikujących się reguł. Minus jeden utrzymuje niekwalifikujący się wiersz poniżej nawet nieograniczonej reguły domyślnej, której wynik wynosi zero. Funkcja IF, Funkcja MAX.

winner oznacza wyłącznie kwalifikujące się wiersze z najwyższym wynikiem. Przed zwróceniem ilości formuła sprawdza, czy nie ma dopasowania albo czy zwycięzców jest wielu. Końcowy test liczbowy celowo odrzuca pustą ilość oraz liczbę zapisaną jako tekst. Microsoft dokumentuje, że ISNUMBER nie konwertuje liczb tekstowych na wartości liczbowe. Liczbowe zero pozostaje prawidłowym wynikiem. Funkcje IS.

Sprawdź wyniki przed rozszerzeniem arkusza

Zmień H2:J2 na każdą z poniższych kombinacji. Są to oczekiwane wyniki wyliczone na podstawie przykładowych reguł, a nie zmierzone stany magazynowe.

Zasób Wykończenie Rozmiar Oczekiwany wynik Warunek zwycięski
Tile Matte Small 7 Trzy określone pola
Tile Matte Large 12 Zasób i wykończenie
Tile Gloss Small 18 Tylko zasób
Disc Gloss Large 10 Zasób i rozmiar
Arc Matte Small 30 Nieograniczona reguła domyślna

Następnie tymczasowo zmień wiersz 6 na zasób Tile, wykończenie Matte, rozmiar Small i ilość 9. Po przywróceniu pierwotnych danych wejściowych wynikiem powinno być Tie: review rules: dwie reguły określają teraz wszystkie trzy pasujące atrybuty. Nawet identyczne ilości nadal oznaczałyby remis, ponieważ wybór reguły jest niejednoznaczny. Następnie przywróć pierwotne wartości wiersza 6.

Aby przetestować gałąź braku dopasowania, tymczasowo zmień cztery zakresy reguł w formule tak, by zaczynały się od wiersza 3, wykluczając regułę domyślną. Dane wejściowe Arc, Matte, Small powinny wtedy zwrócić No matching rule. Po zakończeniu przywróć zakresy od wiersza 2. Aby sprawdzić ilość, wyczyść D5, zachowując pierwotne dane wejściowe; oczekuj Check rule quantity, a następnie przywróć 7.

Sprawdź nakładające się reguły i rozstrzygnij remisy

Aby zobaczyć wszystkie reguły kwalifikujące się dla bieżących danych wejściowych, wpisz osobną formułę w M2. Wcześniej pozostaw pusty zakres M2:P6. Wynik pokazuje pasujące wiersze reguł w pierwotnej kolejności; nie szereguje ich.

=FILTER(
  $A$2:$D$6,
  ((($A$2:$A$6="")+($A$2:$A$6=H2))>0)*
  ((($B$2:$B$6="")+($B$2:$B$6=I2))>0)*
  ((($C$2:$C$6="")+($C$2:$C$6=J2))>0),
  "No matching rule"
)

FILTER zwraca wiersze wybrane przez tablicę logiczną uwzględniania, a ostatni argument podaje komunikat, gdy żaden wiersz nie spełnia warunków. Puste komórki źródłowe mogą być wyświetlane jako zera w tym wyniku inspekcji; nadal traktuj puste komórki w oryginalnej tabeli reguł jako nieograniczone warunki. Funkcja FILTER.

Formuła inspekcyjna rozlewa wynik na sąsiednie komórki. Umieść ją poza tabelą Excela i pozostaw obszar wynikowy pusty. Microsoft dokumentuje, że formuły tablicowe z rozlanym wynikiem nie są obsługiwane wewnątrz tabel oraz że zajęte komórki wyjściowe mogą powodować błąd #SPILL!. Formuły tablic dynamicznych i zachowanie rozlanych tablic.

Rozstrzygnij remis, poprawiając duplikat, zawężając nakładającą się regułę albo definiując dodatkową zasadę priorytetu. Na przykład reguły zasób-plus-wykończenie oraz zasób-plus-rozmiar mają tu obie wynik równy dwa. Skoroszyt nie może określić, który atrybut jest ważniejszy, dopóki nie zakodujesz tej preferencji. Zmiana kolejności wierszy nie powinna decydować o wyniku przy zasadach stosowanych w tym artykule.

Rozszerz wyszukiwanie bez zmiany jego znaczenia

Dla innego zasobu wprowadź jego trzy atrybuty w H3:J3 i skopiuj K2 do K3. Odwołania do danych wejściowych przesuną się wraz z formułą, a znaki dolara utrzymają stałe zakresy reguł. Dodając reguły poniżej wiersza 6, rozszerz wszystkie cztery zakresy w formule głównej oraz oba wymiary formuły inspekcyjnej w taki sam sposób.

Trzymaj każdy atrybut w osobnej kolumnie i stosuj spójne etykiety bez przypadkowych spacji. Ta formuła porównuje pełne wartości pól; nie wyszukuje fragmentów w połączonym opisie zasobu. Kontrola ilości sprawdza, czy wybrana wartość jest liczbą, dlatego wszelkie dodatkowe wymagania dotyczące zapasów — takie jak nieujemne liczby całkowite — zastosuj do danych źródłowych.

Wynikowe wyszukiwanie ma jasną kolejność decyzji: wymagaj kompletnych danych wejściowych, wskaż kwalifikujące się reguły, znajdź najwyższy poziom szczegółowości, odrzuć remisy i zwróć wybraną ilość liczbową. Dzięki temu można łączyć ogólne wartości domyślne ze szczegółowymi wyjątkami, a nierozstrzygnięte wybory pozostają widoczne.

Źródła