Aby połączyć odpowiednie miary zapasów w Excelu na Mac, dodaj pasujące tablice liczbowe za pomocą +. Jeśli wiersze zasobów różnią się kolejnością, najpierw wyszukaj każdy zasób, a następnie dodaj zwrócone ilości. Dzięki temu zachowujesz oddzielne wyniki dla każdej miary.

Ten przewodnik używa syntetycznych zasobów graficznych z dwiema miarami: kopiami do druku i kartami próbnych. Zadaniem jest połączenie ilości z dwóch lokalizacji magazynowych przy jednoczesnym zachowaniu identyfikowalności każdego zasobu i miary. Wszystkie ilości są przykładowe.

Wybór formuły w zależności od sposobu dopasowania zasobów

Przed napisaniem formuły ustal, co sprawia, że dwie wartości odpowiadają sobie. Dopasowanie pozycji działa tylko wtedy, gdy oba bloki wejściowe mają taką samą kolejność zasobów i miar. Dopasowanie etykiet zasobów pozwala, aby wiersze źródłowe występowały w różnych kolejnościach.

Warunek wejściowy Obsługiwane podejście Wynik
Te same zasoby, ta sama kolejność wierszy, te same kolumny miar Dodawanie równowielkich zakresów liczbowych Jeden wynik na zasób i miarę
Unikalne etykiety zasobów, inna kolejność wierszy Dodawanie dwóch wielokolumnowych wyników XLOOKUP Miary jednego zasobu w osobnych komórkach
Powtarzające się etykiety zasobów, które należy połączyć Użycie oddzielnego obliczenia SUMIF dla każdej miary Jeden wynik na żądany zasób i miarę

Poniższe przykłady wywodzą te podejścia z udokumentowanych przez Microsoft obliczeń tablicowych, zwracania wieloelementowych wyszukiwań i sumowania warunkowego. Wybierz metodę pasującą do struktury danych przed skopiowaniem formuły.

Potwierdź swoją wersję na Macu

Przykład wyszukiwania dotyczy Excela dla Microsoft 365 na Mac, Excel 2024 na Mac i Excel 2021 na Mac. Microsoft jawnie stwierdza, że XLOOKUP jest niedostępne w Excelu 2016 i Excel 2019, mimo że te edycje pojawiają się na liście ogólnej zastosowania na stronie. Dokumentacja funkcji XLOOKUP firmy Microsoft.

Na Macu menu aplikacji pakietu Office zawiera okno dialogowe Informacje, które pokazuje wersję i typ licencji. W Excelu sprawdź Excel → Informacje o programie Excel. Zidentyfikuj edycję produktu oraz numer wersji przed wyborem formuły. Informacje o Office: Jakiej wersji Office używam?.

Opcjonalna formuła HSTACK wymieniona dalej w artykule wymaga Excela dla Microsoft 365 na Mac lub Excel 2024 na Mac; Microsoft nie wymienia Excela 2021 na Mac dla tej funkcji. Główne obliczenia jej nie wymagają. Dokumentacja funkcji HSTACK firmy Microsoft.

Przygotowanie dwóch małych inwentarzy zasobów graficznych

Użyj pustego arkusza z zwykłymi zakresami komórek. Wpisz inwentarz pierwszej lokalizacji w A1:C4:

Zasób Kopie do druku Karty próbne
Tile-A 8 3
Tile-B 5 7
Tile-C 6 2

Wpisz inwentarz drugiej lokalizacji w E1:G4, pozostawiając kolumnę D pustą:

Zasób Kopie do druku Karty próbne
Tile-A 4 2
Tile-B 9 1
Tile-C 3 5

Oba bloki liczbowe mają wysokość trzech wierszy i szerokość dwóch kolumn. Ich pierwsza kolumna liczbowa zlicza kopie do druku; druga zlicza karty próbne. Etykiety znajdują się poza zakresami arytmetycznymi.

W J1:L4 przygotuj obszar wyjściowy poniżej. Wpisz początkowo tylko nagłówki i etykiety zasobów; komórki ilościowe pozostaw puste.

Zasób Połączone kopie do druku Połączone karty próbne
Tile-A
Tile-B
Tile-C

W tych przykładach każda ilość jest liczbą, a każdy zasób występuje raz w każdym źródle. Brakujący rekord i zapisana zero mają inne znaczenie: użyj zera, gdy ilość jest znana jako zero, a brakujący rekord rozstrzygnij przed potraktowaniem go jako zera.

Dodawanie wyrównanych tablic w celu zachowania każdego odpowiedniego wyniku

Przy pokazanej kolejności wierszy źródłowych wpisz tę kompletą formułę arkusza w K2:

=B2:C4+F2:G4

Potwierdź formułę klawiszem Return. W Excelu z dynamicznymi tablicami wyjście zajmie K2:L4: trzy wiersze zasobów i dwie kolumny miar. Microsoft udokumentował, że formuły tablicowe mogą zwracać wiele wyników i że wyniki dynamiczne rozlewają się na sąsiednie komórki. Wskazówki i przykłady formuł tablicowych.

Oczekiwany wynik to:

Zasób Połączone kopie do druku Połączone karty próbne
Tile-A 12 5
Tile-B 14 8
Tile-C 9 7

Obliczenie następuje według pozycji: B2+F2 daje 12, a C2+G2 daje 5. Każda pozostała pozycja podlega tej samej regule. Dwa bloki źródłowe stają się jednym blokiem wynikowym o tych samych wymiarach.

Dla inwentarza jedokolumnowego odpowiednikiem byłaby formuła =B2:B4+F2:F4. Zwraca ona trzy ilości, po jednej na wiersz. Wersja dwukolumnowa po prostu zachowuje drugą miarę obok pierwszej.

Dlaczego zewnętrzna SUMA daje niewłaściwy kształt

Te formuły obliczają sumę ogólną:

=SUM(B2:C4,F2:G4)

=SUM(B2:C4+F2:G4)

Dla tej próbki każda zwraca 55. Ta liczba miesza wszystkie trzy zasoby i obie miary. Microsoft udokumentował, że SUM dodaje wartości podane jako liczby, odwołania lub zakresy; owijanie tablicy wynikowej w SUM zatem redukuje ją do jednej wartości. Dokumentacja funkcji SUMA firmy Microsoft.

Użyj powyższego wyniku sześciu komórek jako testu akceptacji. Pojedyncza komórka zawierająca 55 odpowiada na inne zadanie, nawet jeśli jej arytmetyka jest poprawna.

Wersja niezależna od edycji dla wyrównanych wierszy

Jeśli wolisz formuły w poszczególnych komórkach, wpisz =B2+F2 w K2. Przeciągnij uchwyp wypełnienia w prawo do L2, a następnie wypełnij K2:L2 w dół do wiersza 4. Odwołania względne dostosują się, obliczając każdą odpowiadającą parę. Microsoft udokumentował to zachowanie uchwypu wypełnienia dla Excela na Mac. Używanie Excela jako kalkulatora w Excelu na Mac.

To daje te same sześć ilości bez rozlanego wyniku. Nadal zależy to od zgodności kolejności wierszy źródłowych.

Dopasowanie etykiet zasobów, gdy wiersze źródłowe są w różnych kolejnościach

Gdy pozycje wierszy nie mogą być zaufane, najpierw wyczyść poprzedni wynik z K2. Zachowaj J2:J4 jako listę żądanych zasobów. Wpisz poniższe w K2:

=XLOOKUP($J2,$A$2:$A$4,$B$2:$C$4)+XLOOKUP($J2,$E$2:$E$4,$F$2:$G$4)

Każde wyszukiwanie znajduje zasób w J2 i zwraca jego dwie kolumny ilości. Dodanie zwróconych wierszy daje 12 w K2 i 5 w L2. Microsoft udokumentował zarówno domyślne dopasowanie dokładne, jak i zwracanie wielu elementów z wielokolumnowego zakresu zwracanego. Dokumentacja funkcji XLOOKUP firmy Microsoft.

Skopiuj formułę z K2 do K3:K4, pozostawiając L2:L4 puste dla wyników poziomych. Znaki dolara utrwalają zakresy źródłowe; żądany zasób zmienia się z każdym wierszem wyjściowym.

Jako diagnostyka, zmień kolejność pełnych rekordów drugiego źródła tak, by Tile-C było na początku. Przenieś każdą etykietę razem z obiema ilościami. Wynik oparty na wyszukiwaniu powinien zachować oczekiwane wartości, podczas gdy dodawanie oparte na pozycji sparowałoby teraz inne zasoby.

Brakująca etykieta zwraca #N/A, ponieważ formuła pomija opcjonalną zastępę brakującego dopasowania. Powtarzająca się etykieta zwraca pierwsze dopasowanie zamiast łączyć jej rekordy. To są udokumentowane granice wyszukiwania. Dokumentacja funkcji XLOOKUP firmy Microsoft.

Wyjście zawiera tylko zasoby wymienione w J2:J4. Przed użyciem własnych danych upewnij się, że ta lista żądanych zasobów zawiera każdy zasób, którego zamierzasz raportować z obu lokalizacji.

Łączenie powtarzających się rekordów zasobów po jednej mierze na raz

Jeśli kilka wierszy celowo należy do tego samego zasobu, użyj sumowania warunkowego. Z oryginalnymi zakresami próbki wpisz tę kompletą formułę w K2:

=SUMIF($A$2:$A$4,$J2,B$2:B$4)+SUMIF($E$2:$E$4,$J2,F$2:F$4)

Skopiuj ją w prawo do L2, a następnie skopiuj K2:L2 w dół do wiersza 4. Przesunięcie w prawo zmienia zakresy ilości na kolumny kart próbnych, zachowując stałe zakresy etykiet. Każda komórka wyjściowa sumuje tylko swój własny zasób i miarę. Microsoft udokumentował używanie jednego zakresu dla kryteriów i innego dla odpowiadających ilości. Dokumentacja funkcji SUMIF firmy Microsoft.

Aby zademonstrować powtarzające się rekordy, dołącz Tile-A, 2 i 1 w A5:C5. Przed przeliczeniem rozszerz zakresy kryteriów i ilości pierwszego źródła do wiersza 5:

=SUMIF($A$2:$A$5,$J2,B$2:B$5)+SUMIF($E$2:$E$4,$J2,F$2:F$4)

Skopiuj zrewidowaną formułę w prawo i w dół jak wcześniej. Ilustracyjne wyjście dla Tile-A staje się 14 kopie do druku i 6 kart próbnych. Stały zakres kończący się na wierszu 4 pominąłby dołączony rekord, więc konserwacja zakresów jest częścią tej metody.

Zachowaj każdy zakres kryteriów o tym samym rozmiarze i kształcie co jego zakres ilości. Zwróć też uwagę, że SUMIF traktuje * i ? jako symbole wieloznaczne; syntetyczne etykiety tutaj nie zawierają ani jednego, ani drugiego. Dokumentacja funkcji SUMIF firmy Microsoft.

Rozwiązywanie błędów rozlewania i sprawdzanie gotowego inwentarza

Rozlana formuła potrzebuje miejsca na całe swoje wyjście. Umieść ją poza tabelą Excela, a edytuj lub zastępuj przez jej lewą górną komórkę. Microsoft udokumentował oba ograniczenia. Dynamiczne formuły tablicowe i zachowanie rozlanych tablic.

Jeśli pojawi się #SPILL!, sprawdź przeznaczone miejsce docelowe pod kątem istniejących wpisów i scalanych komórek. Przenieś blokujące treści do innej lokalizacji lub wybierz pusty obszar wyjściowy. Microsoft identyfikuje zablokowane miejsca docelowe i scalone komórki jako przyczyny błędów rozlewania. Jak naprawić błąd #SPILL!.

Sprawdź ilości pod kątem wpisów tekstowych, jeśli dodawanie zwraca #VALUE!. Microsoft zauważa, że bezpośrednie dodawanie może się nie powieść na niecyfrowym tekście, podczas gdy SUM ignoruje tekst. Zastąpienie formuły SUM zmieniłoby więc zarówno kształt wyniku, jak i obsługę tekstu. Dokumentacja funkcji SUMA firmy Microsoft.

Dla opcjonalnego bloku wynikowego z etykietami w Microsoft 365 na Mac lub Excel 2024 na Mac, użyj oryginalnej wyrównanej próbki na nowym pustym obszarze:

=HSTACK(A2:A4,B2:C4+F2:G4)

To umieszcza etykiety zasobów obok obliczonych miar. HSTACK dołącza kolumny; + wewnątrz formuły wykonuje dodawanie. Nie dopasowuje etykiet ani nie koryguje różnych kolejności wierszy źródłowych. Dokumentacja funkcji HSTACK firmy Microsoft.

Zakończ sprawdzając trzy rzeczy: wyjście zawiera każdy zamierzony zasób, każda miara pozostaje w swojej kolumnie, a jeden ręcznie sprawdzony wiersz zgadza się z ilościami źródłowymi. Dla wyrównanych danych wejściowych użyj dodawania zakresów. Dla unikalnych etykiet w zmieniających się kolejnościach wierszy dodaj wyniki wyszukiwania. Dla powtarzających się rekordów sumuj miary każdego zasobu oddzielnie. Gotowy inwentarz powinien zachować szczegóły niezbędne do identyfikacji, co zlicza każda ilość.

Źródła