Tak więc dowiedzieliśmy się już, czym jest odniesienie 3D w programie Excel. Zabawnym faktem jest to, że normalne odwoływanie się do Excela 3D nie działa z funkcjami warunkowymi, takimi jak funkcja SUMA.JEŻELI. W tym artykule dowiemy się, jak uzyskać referencje 3D pracujące z funkcją SUMA.JEŻELI.
Ogólna formuła SUMIF z odniesieniem 3D w programie Excel
Wygląda na skomplikowaną, ale tak nie jest (tak bardzo).
| =SUMAPRODUKT(SUMA.JEŻELI(ADR.POŚR("'"&nazwy_zakres_nazw_arkuszy&"'!" & "zakres_kryteriów");kryteria,ADR.POŚR("'"&nazwy_zakres_nazw_arkuszy&"'!" &"suma_zakresu"))) |
"'"name_range_of_sheet_names"'":Jest to nazwany zakres zawierający nazwy arkuszy. To jest bardzo ważne.
"zakres_kryteriów":Jest to tekstowe odniesienie do kryteriów zawierających zakres. (Powinno być takie samo we wszystkich arkuszach do pracy referencyjnej 3D.)
kryteria:To po prostu warunek, który chcesz postawić do podsumowania. Może to być odwołanie do tekstu lub komórki.
"zakres_sum":Jest to tekstowe odniesienie do zakresu sumy. (Powinno być takie samo we wszystkich arkuszach do pracy referencyjnej 3D.)
Dość teorii, załóżmy, że odwołanie 3D z działającą funkcją SUMIF.
Przykład: Suma według regionu z wielu arkuszy przy użyciu odwołań 3D programu Excel:
Bierzemy te same dane, które wzięliśmy w prostym przykładzie odniesienia 3D. W tym przykładzie mam pięć różnych arkuszy zawierających podobne dane. Każdy arkusz zawiera dane z miesiąca. W arkuszu głównym chcę uzyskać sumę jednostek i kolekcję według regionu ze wszystkich arkuszy. Zróbmy to najpierw dla jednostek. We wszystkich arkuszach jednostki mieszczą się w zakresie D2:D14.
Teraz, jeśli używasz normalnego odniesienia 3D z funkcją SUMA.JEŻELI,
=SUMA.JEŻELI(sty:kwi!A2:A14;nadrzędny!B4,sty:kwi!D2:D14)
Zwróci #ARG! błąd. Więc nie możemy tego użyć. Użyjemy wspomnianej powyżej ogólnej formuły.
Korzystając z powyższej ogólnej formuły 3D z odniesieniami SUMIF programu Excel, wpisz tę formułę w komórce C3:
| =SUMAPRODUKT(SUMA.JEŻELI(ADR.POŚR("'"&Miesiące&"'!" & "A2:A14");Główny!B3,ADR.POŚR("'"&Miesiące&"'!" &"D2:D14"))) |
Tutaj miesiące to nazwany zakres zawierający nazwy arkuszy. To jest kluczowe.
Kiedy wciśniesz Enter, otrzymasz dokładny wynik.
Jak to działa?
Rdzeniem formuły jest funkcja ADR.POŚR, a nazwany zakres. Tutaj ciąg"'"&Miesiące&"'!" & „A2:A14”przekłada się na tablicę odniesień do zakresu każdego arkusza w nazwany zakres.
| {"'sty'!D2:D14";"'lut'!D2:D14";"'Mar'!D2:D14";"'Kwi'!D2:D14"} |
Ta tablica zawiera odniesienie tekstowezakresów, a nie rzeczywistych zakresów. Teraz, ponieważ jest to odwołanie tekstowe, może być używane przez funkcję ADR.POŚR, aby przekonwertować je na rzeczywiste zakresy. Dzieje się tak w przypadku obu funkcji ADR.POŚR. Po rozwiązaniu tekstów wewnątrz funkcji ADR.POŚR (przytrzymaj mocno), formuła wygląda tak:
| =SUMPRODUCT(SUMIF(INDIRECT(({"'sty'!A2:A14";"'lut'!A2:A14";"'Mar'!A2:A14";"'kwi'!A2:A14"}) , Master!B3,INDIRECT({"'sty'!D2:D14";"'lut'!D2:D14";"'Mar'!D2:D14";"'Kwi'!D2:D14"}))) |
Teraz zaczyna działać funkcja SUMA.JEŻELI (a nie POŚREDNIA, jak można się domyślić). Warunek jest dopasowany do pierwszego zakresu„„ Sty”! A2: A14”. Tutaj funkcja ADR.POŚR działa dynamicznie i konwertuje ten tekst na rzeczywisty zakres (dlatego jeśli spróbujesz najpierw rozwiązać funkcję POŚREDNIA za pomocą klawisza F9, nie uzyskasz wyniku). Następnie sumuje dopasowane wartości w zakresie"'Sty'! D2:D14".Dzieje się tak dla każdego zakresu w tablicy. Na koniec otrzymamy tablicę zwróconą przez funkcję SUMA.JEŻELI.
| =PROD.SUMA({97;82;63;73}) |
Teraz SUMPRODUCT robi to, co robi najlepiej. Sumuje te wartości i działa nasza funkcja 3D SUMIF.
Więc tak, w ten sposób można osiągnąć funkcję 3D SUMIF. To trochę skomplikowane, zgadzam się z tym. W tej formule 3D jest dużo miejsca na błędy. Sugerowałbym użycie funkcji SUMA.JEŻELI na każdym arkuszu w określonej komórce, a następnie użycie normalnego odwołania 3D do zsumowania tych wartości.
Mam nadzieję, że wystarczająco wyjaśniłem. Jeśli masz jakiekolwiek wątpliwości dotyczące programu Excel odwołującego się do dowolnego innego zapytania związanego z Excelem / VBA, zapytaj w sekcji komentarzy poniżej.
Odniesienie względne i bezwzględne w programie Excel | Odwoływanie się w Excelu to ważny temat dla każdego początkującego. Nawet doświadczeni użytkownicy programu Excel popełniają błędy w odwoływaniu się.
Odniesienie do arkusza dynamicznego | Podaj arkusze referencyjne dynamicznie, korzystając z funkcji ADR.POŚR programu Excel. To proste…
Rozszerzanie referencji w Excelu | Rozszerzające się odniesienie rozwija się po skopiowaniu w dół lub w prawo. W tym celu używamy znaku $ przed numerem kolumny i wiersza. Oto jeden przykład…
Wszystko o absolutnym referencji | Domyślny typ odniesienia w programie Excel jest względny, ale jeśli chcesz, aby odwołanie do komórek i zakresów było bezwzględne, użyj znaku $. Oto wszystkie aspekty odwoływania się bezwzględnego w programie Excel.
Popularne artykuły:
50 skrótów Excela, które zwiększą Twoją produktywność | Przyspiesz swoje zadanie. Te 50 skrótów przyspieszy pracę w programie Excel.
Funkcja WYSZUKAJ.PIONOWO w programie Excel | Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.
LICZ.JEŻELI w Excelu 2016 | Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby policzyć konkretną wartość. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.
Jak korzystać z funkcji SUMIF w programie Excel? | To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.