Spis treści
Gratulacje! Dotarłeś do ostatniego dnia maratonu 30 funkcji Excela w 30 dni. To była długa i ciekawa podróż, podczas której nauczyłeś się wielu przydatnych rzeczy o funkcjach Excela.
30 dnia maratonu poświęcimy naukę funkcji POŚREDNI (POŚREDNIA), która zwraca łącze określone przez ciąg tekstowy. Za pomocą tej funkcji możesz tworzyć zależne listy rozwijane. Na przykład wybór kraju z listy rozwijanej określa, które opcje pojawią się na liście rozwijanej miast.
Przyjrzyjmy się więc bliżej teoretycznej części funkcji POŚREDNI (POŚREDNIO) i zbadać praktyczne przykłady jego zastosowania. Jeśli masz dodatkowe informacje lub przykłady, podziel się nimi w komentarzach.
Funkcja 30: POŚREDNIA
Funkcjonować POŚREDNI (POŚREDNIA) zwraca link określony przez ciąg tekstowy.
Jak korzystać z funkcji ADR.POŚR?
Ponieważ funkcja POŚREDNI (POŚREDNIA) zwraca link podany przez ciąg tekstowy, możesz go użyć do:
- Utwórz niezmienne łącze początkowe.
- Utwórz odwołanie do statycznego nazwanego zakresu.
- Utwórz łącze, korzystając z informacji o arkuszu, wierszu i kolumnie.
- Utwórz nie przesuwającą się tablicę liczb.
Składnia ADR.POŚR (POŚREDNIA)
Funkcjonować POŚREDNI (ADR.POŚR) ma następującą składnię:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- ref_tekst (link_do_komórki) to tekst linku.
- a1 – jeśli jest równy TRUE (TRUE) lub nie jest określony, zostanie użyty styl linku A1; a jeśli FALSE (FALSE), to styl R1C1.
Pułapki POŚREDNIE (POŚREDNIE)
- Funkcjonować POŚREDNI (ADR.POŚR) jest przeliczana po każdej zmianie wartości w arkuszu programu Excel. Może to znacznie spowolnić skoroszyt, jeśli funkcja jest używana w wielu formułach.
- Jeśli funkcja POŚREDNI (POŚREDNIO) tworzy łącze do innego skoroszytu Excel, skoroszyt ten musi być otwarty lub formuła zgłosi błąd #ADR! (#POŁĄCZYĆ!).
- Jeśli funkcja POŚREDNI (POŚREDNIA) odwołuje się do zakresu, który przekracza limit wierszy i kolumn, formuła zgłosi błąd #ADR! (#POŁĄCZYĆ!).
- Funkcjonować POŚREDNI (ADR.POŚR) nie może odwoływać się do dynamicznego nazwanego zakresu.
Przykład 1: Utwórz nie przesuwający się link początkowy
W pierwszym przykładzie kolumny C i E zawierają te same liczby, ich sumy są obliczane za pomocą funkcji SUMA (SUM) są również takie same. Jednak formuły są nieco inne. W komórce C8 formuła to:
=SUM(C2:C7)
=СУММ(C2:C7)
W komórce E8 funkcja POŚREDNI (POŚREDNIA) tworzy łącze do komórki początkowej E2:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
Jeśli wstawisz wiersz u góry arkusza i dodasz wartość za styczeń (styczeń), kwota w kolumnie C nie zmieni się. Formuła zmieni się, reagując na dodanie linii:
=SUM(C3:C8)
=СУММ(C3:C8)
Jednak funkcja POŚREDNI (POŚREDNIA) naprawia E2 jako komórkę początkową, więc styczeń jest automatycznie uwzględniany w obliczeniach sum w kolumnie E. Komórka końcowa uległa zmianie, ale komórka początkowa nie uległa zmianie.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
Przykład 2: Link do statycznego nazwanego zakresu
Funkcjonować POŚREDNI (POŚREDNIA) może utworzyć odwołanie do nazwanego zakresu. W tym przykładzie niebieskie komórki tworzą zakres Lista numerów. Ponadto tworzony jest również zakres dynamiczny z wartości w kolumnie B NumListaDyn, w zależności od liczby liczb w tej kolumnie.
Sumę dla obu zakresów można obliczyć, podając po prostu jego nazwę jako argument funkcji SUMA (SUM), jak widać w komórkach E3 i E4.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
Zamiast wpisywać nazwę zakresu do funkcji SUMA (SUMA), Możesz odwołać się do nazwy zapisanej w jednej z komórek arkusza roboczego. Na przykład, jeśli nazwa Lista numerów jest zapisany w komórce D7, to formuła w komórce E7 będzie wyglądać tak:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
Niestety funkcja POŚREDNI (POŚREDNIA) nie może utworzyć odwołania do zakresu dynamicznego, więc po skopiowaniu tej formuły do komórki E8 pojawi się błąd #ADR! (#POŁĄCZYĆ!).
Przykład 3: Utwórz łącze, korzystając z informacji o arkuszu, wierszu i kolumnie
Możesz łatwo utworzyć łącze na podstawie numerów wierszy i kolumn, a także używając wartości FAŁSZ (FAŁSZ) dla drugiego argumentu funkcji POŚREDNI (POŚREDNI). W ten sposób powstaje link do stylu R1C1. W tym przykładzie do linku dodaliśmy dodatkowo nazwę arkusza – „MyLinks”!R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
Przykład 4: Utwórz nie przesuwającą się tablicę liczb
Czasami musisz użyć tablicy liczb w formułach programu Excel. W poniższym przykładzie chcemy uśrednić 3 największe liczby w kolumnie B. Liczby można wprowadzić do formuły, tak jak w komórce D4:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
Jeśli potrzebujesz większej tablicy, prawdopodobnie nie będziesz chciał wprowadzać wszystkich liczb w formule. Drugą opcją jest użycie funkcji RZĄD (ROW), tak jak w formule tablicowej wprowadzonej w komórce D5:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
Trzecią opcją jest użycie funkcji RZĄD (Solidaryzować się POŚREDNI (ADR.POŚR), tak jak w przypadku formuły tablicowej w komórce D6:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
Wynik dla wszystkich 3 formuł będzie taki sam:
Jeśli jednak wiersze zostaną wstawione u góry arkusza, druga formuła zwróci niepoprawny wynik, ponieważ odwołania w formule będą się zmieniać wraz z przesunięciem wiersza. Teraz, zamiast średniej z trzech największych liczb, formuła zwraca średnią z 3, 4 i 5 największych liczb.
Korzystanie z funkcji POŚREDNI (POŚREDNIA), trzecia formuła zachowuje prawidłowe odwołania do wierszy i nadal wyświetla poprawny wynik.