Użyj funkcji POKRĘTŁO w formułach programu Excel, aby zmienić zakres odwołań do komórek używanych w formule bez konieczności edytowania samej formuły. Zapewnia to używanie tych samych komórek, nawet gdy zmienia się arkusz kalkulacyjny.
Uwaga: Instrukcje w tym artykule dotyczą programów Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel dla komputerów Mac i Excel Online.
Użyj zakresu dynamiki z formułą COUNTIF - INDIRECT
Funkcja INDIRECT może być używana z wieloma funkcjami, które akceptują odwołanie do komórki jako argument, takie jak funkcje SUMA i LICZ.JEŻELI.
Użycie INDIRECT jako argumentu dla LICZBY powoduje utworzenie dynamicznego zakresu odwołań do komórek, które mogą być zliczane przez funkcję, jeśli wartości komórek spełniają kryteria. Czyni to, przekształcając dane tekstowe, czasami nazywane ciągiem tekstowym, w odwołanie do komórki.
Ten przykład jest oparty na danych pokazanych na powyższym obrazku. Formuła COUNTIF - INDIRECT utworzona w samouczku to:
= COUNTIF (POŚREDNIA (E1 & ":" i E2), "> 10")
W tym wzorze argument dla funkcji INDIRECT zawiera:
- Komórki odwołują się do E1 i E2, które zawierają dane tekstowe D1 i D6.
- Operator zakresu, dwukropek (:) otoczone podwójnymi cudzysłowami (' '), który zamienia dwukropek w ciąg tekstowy.
- Dwa ampersandy (&), które są używane do łączenia lub łączenia ze sobą dwukropków z odwołaniami do komórek E1 i E2.
Wynikiem jest to, że INDIRECT przekształca ciąg tekstowy D1: D6 w odnośnik komórki i przekazuje go do funkcji LICZ.JEŻELI, które mają zostać policzone, jeśli komórki z odniesieniami są większe niż 10.
Funkcja INDIRECT akceptuje dowolne wejścia tekstowe. Mogą to być komórki w arkuszu, które zawierają odwołania do tekstu lub komórki tekstowej, które są wprowadzane bezpośrednio do funkcji.
Dynamicznie Zmień zakres Formuły
Pamiętaj, że celem jest stworzenie formuły z zakresem dynamicznym. Zakres dynamiczny można zmienić bez edytowania samej formuły.
Zmieniając dane tekstowe znajdujące się w komórkach E1 i E2, od D1 i D6 do D3 i D7, zakres sumowany przez funkcję można łatwo zmienić z D1: D6 na D3: D7. Eliminuje to potrzebę bezpośredniej edycji formuły w komórce G1.
Funkcja LICZ.JEŻELI w tym przykładzie liczy tylko komórki zawierające liczby, jeśli są większe niż 10. Mimo że cztery z pięciu komórek w zakresie D1: D6 zawierają dane, tylko trzy komórki zawierają liczby. Komórki, które są puste lub zawierają dane tekstowe, są ignorowane przez funkcję.
Zliczanie tekstu za pomocą LICZ.JEŻELI
Funkcja LICZ.JEŻELI nie jest ograniczona do liczenia danych numerycznych. Zlicza również komórki zawierające tekst, sprawdzając, czy pasują do określonego tekstu.
Aby to zrobić, w komórce G2 wpisywana jest następująca formuła:
= COUNTIF (POŚREDNIA (E1 & ":" i E2), "dwa")
W tym wzorze funkcja INDIRECT odwołuje się do komórek od B1 do B6. Funkcja COUNTIF zawiera liczbę komórek z wartością tekstową dwa w nich.
W takim przypadku wynikiem jest 1.
COUNTA, COUNTBLANK i INDIRECT
Dwie inne funkcje obliczania Excela to COUNTA, która zlicza komórki zawierające dowolny typ danych, ignorując tylko puste lub puste komórki, oraz LICZ.BLANK, która zlicza tylko puste lub puste komórki w zakresie.
Ponieważ obie te funkcje mają podobną składnię do funkcji LICZ.JEŻELI, można je zastąpić w powyższym przykładzie przy pomocy INDIRECT, aby utworzyć następujące formuły:
= COUNTA (POŚREDNIA (E1 & ":" i E2))= COUNTBLANK (POŚREDNIA (E1 & ":" i E2)
Dla zakresu D1: D6, COUNTA zwraca odpowiedź 4, ponieważ cztery z pięciu komórek zawierają dane. Funkcja COUNTBLANK zwraca odpowiedź 1, ponieważ w zakresie znajduje się tylko jedna pusta komórka. Zaletą korzystania z funkcji INDIRECT we wszystkich tych formułach jest to, że nowe komórki można wstawiać w dowolnym miejscu w zakresie. Zakres dynamicznie przesuwa się wewnątrz różnych funkcji, a wyniki są odpowiednio aktualizowane. Bez funkcji INDIRECT każda funkcja musiałaby być edytowana, aby obejmowała wszystkie 7 komórek, w tym nową. Zalety funkcji INDIRECT polegają na tym, że wartości tekstowe można wstawiać jako odwołania do komórek i dynamicznie aktualizować zakresy, gdy zmienia się arkusz kalkulacyjny. Dzięki temu ogólna konserwacja arkusza kalkulacyjnego jest znacznie łatwiejsza, szczególnie w przypadku bardzo dużych arkuszy kalkulacyjnych. Dlaczego warto korzystać z funkcji POŚREDNIA?
