Funkcja ADR.POŚREDNIA programu Excel - omówienie
Funkcja ADR.POŚR w programie Excel może być używana, gdy masz odwołanie do komórki lub zakresu jako ciąg tekstowy i chcesz uzyskać wartości z tych odwołań.
W skrócie - możesz użyć formuły pośredniej, aby zwrócić referencję określoną przez ciąg tekstowy.
W tym samouczku Excela pokażę, jak korzystać z funkcji pośredniej w Excelu na kilku praktycznych przykładach.
Ale zanim przejdę do przykładów, przyjrzyjmy się najpierw jego składni.
FUNKCJA POŚREDNIA Składnia
=ADR.POŚR(tekst_odn, [a1])
Argumenty wejściowe
- ref_text - Ciąg tekstowy zawierający odwołanie do komórki lub nazwanego zakresu. Musi to być prawidłowe odwołanie do komórki, w przeciwnym razie funkcja zwróci #REF! błąd
- [a1] - Wartość logiczna, która określa, jakiego typu odwołania należy użyć tekst odnośnika. Może to być PRAWDA (wskazuje odwołanie do stylu A1) lub FAŁSZ (wskazuje odwołanie do stylu R1K1). Jeśli zostanie pominięty, domyślnie jest to PRAWDA.
Dodatkowe uwagi
- ADR.POŚR jest funkcją lotną. Oznacza to, że jest on ponownie obliczany za każdym razem, gdy skoroszyt programu Excel jest otwarty lub za każdym razem, gdy obliczenia są uruchamiane w arkuszu. Zwiększa to czas przetwarzania i spowalnia skoroszyt. Chociaż możesz użyć formuły pośredniej z małymi zestawami danych z niewielkim lub żadnym wpływem na szybkość, możesz zauważyć, że skoroszyt działa wolniej, gdy używasz go z dużymi zestawami danych
- Tekst referencyjny (ref_text) może mieć postać:
- Odwołanie do komórki, które z kolei zawiera odwołanie w formacie odwołania w stylu A1 lub W1K1.
- Odwołanie do komórki w cudzysłowie podwójnym.
- Nazwany zakres, który zwraca odwołanie
Przykłady korzystania z funkcji pośredniej w programie Excel
Teraz zanurkujmy i spójrzmy na kilka przykładów, jak używać funkcji ADR.POŚR w programie Excel.
Przykład 1: Użyj odwołania do komórki, aby pobrać wartość
Pobiera odwołanie do komórki jako ciąg tekstowy jako dane wejściowe i zwraca wartość w tym odwołaniu (jak pokazano w poniższym przykładzie):
Formuła w komórce C1 to:
=ADR.POŚR("A1")
Powyższa formuła przyjmuje odwołanie do komórki A1 jako argument wejściowy (w podwójnych cudzysłowach jako ciąg tekstowy) i zwraca wartość w tej komórce, która wynosi 123.
Teraz, jeśli myślisz, dlaczego po prostu nie użyję =A1 zamiast funkcji ADR.POŚR, masz prawidłowe pytanie.
Oto dlaczego…
Kiedy używasz =A1 lub =$A$1, daje ten sam wynik. Ale kiedy wstawisz wiersz powyżej pierwszego wiersza, zauważysz, że odwołania do komórek automatycznie zmienią się, aby uwzględnić nowy wiersz.
Możesz również użyć funkcji ADR.POŚR, jeśli chcesz zablokować odwołania do komórek w taki sposób, aby nie zmieniały się po wstawieniu wierszy/kolumn do arkusza.
Przykład 2: Użyj odwołania do komórki w komórce, aby pobrać wartość
Możesz również użyć tej funkcji, aby pobrać wartość z komórki, do której odwołanie jest przechowywane w samej komórce.
W powyższym przykładzie komórka A1 ma wartość 123.
Komórka C1 ma odwołanie do komórki A1 (jako ciąg tekstowy).
Teraz, gdy użyjesz funkcji ADR.POŚR i użyjesz C1 jako argumentu (który z kolei zawiera adres komórki jako ciąg tekstowy), przekonwertuje wartość w komórce A1 na prawidłowe odwołanie do komórki.
To z kolei oznacza, że funkcja odwołuje się do komórki A1 i zwraca w niej wartość.
Zauważ, że nie musisz tutaj używać podwójnych cudzysłowów, ponieważ C1 ma odwołanie do komórki przechowywane tylko w formacie ciągu tekstowego.
Ponadto w przypadku, gdy ciąg tekstowy w komórce C1 nie jest prawidłowym odwołaniem do komórki, funkcja pośrednia zwróci #REF! błąd.
Przykład 3: Tworzenie odwołania przy użyciu wartości w komórce
Możesz także utworzyć odwołanie do komórki, używając kombinacji alfabetu kolumn i numeru wiersza.
Na przykład, jeśli komórka C1 zawiera liczbę 2 i używasz formuły =POŚREDNIA(„A”&C1) wtedy odwołuje się do komórki A2.
Praktycznym zastosowaniem tego może być tworzenie dynamicznych odwołań do komórek na podstawie wartości w innej komórce.
Jeśli ciąg tekstowy użyty w formule zawiera odwołanie, którego program Excel nie rozumie, zwróci błąd ref (#REF!).
Przykład 4: Oblicz sumę zakresu komórek
Możesz również odwoływać się do zakresu komórek w taki sam sposób, w jaki odwołujesz się do pojedynczej komórki za pomocą funkcji ADR.POŚR w programie Excel.
Na przykład, =ADR.POŚR(„A1:A5”) odnosiłoby się do zakresu A1:A5.
Następnie możesz użyć funkcji SUMA, aby znaleźć sumę, lub funkcji DUŻA/MAŁA/MIN/MAKS, aby wykonać inne obliczenia.
Podobnie jak funkcja SUMA, możesz również używać funkcji takich jak LARGE, MAX/MIN, COUNT itp.
Przykład 5: Tworzenie odniesienia do arkusza za pomocą funkcji ADR.POŚR
Powyższe przykłady obejmowały sposób odwoływania się do komórki w tym samym arkuszu. Możesz również użyć formuły ADR.POŚR, aby odwołać się do komórki w innym arkuszu lub innym skoroszycie.
Oto coś, co musisz wiedzieć o odwoływaniu się do innych arkuszy:
- Załóżmy, że masz arkusz o nazwie Arkusz1, a w arkuszu w komórce A1 masz wartość 123. Jeśli przejdziesz do innego arkusza (powiedzmy Arkusz2) i odwołasz się do komórki A1 w Arkuszu1, formuła będzie wyglądać tak: =Arkusz1!A1
Jednak…
- Jeśli masz arkusz zawierający dwa lub więcej niż dwa słowa (ze znakiem spacji pomiędzy) i odwołujesz się do komórki A1 w tym arkuszu z innego arkusza, formuła będzie wyglądać tak: =’Zbiór danych’!A1
W przypadku wielu słów program Excel automatycznie wstawia pojedyncze cudzysłowy na początku i na końcu nazwy arkusza.
Zobaczmy teraz, jak utworzyć funkcję ADR.POŚR, aby odwoływać się do komórki w innym arkuszu.
Załóżmy, że masz arkusz o nazwie Zestaw danych, a komórka A1 w nim ma wartość 123.
Teraz, aby odwołać się do tej komórki z innego arkusza, użyj następującej formuły:
=ADR.POŚR("'Zbiór danych'!A1")
Jak widać, odwołanie do komórki musi również zawierać nazwę arkusza roboczego.
Jeśli masz nazwę arkusza roboczego w komórce (powiedzmy A1), możesz użyć następującej formuły:
=ADR.POŚR("'"&A1&"'!A1")
Jeśli masz nazwę arkusza roboczego w komórce A1 i adres komórki w komórce A2, formuła będzie wyglądać tak:
=ADR.POŚR("'"&A1&"'!"&A2)
Podobnie możesz również zmodyfikować formułę, aby odwoływała się do komórki w innym skoroszycie.
Może to być przydatne, gdy próbujesz utworzyć arkusz podsumowania, który pobiera dane z wielu różnych arkuszy.
Pamiętaj również, że używając tej formuły do odwoływania się do innego skoroszytu, skoroszyt ten musi być otwarty.
Przykład 6: Odwoływanie się do nazwanego zakresu za pomocą formuły POŚREDNIEJ
Jeśli utworzyłeś nazwany zakres w programie Excel, możesz odwołać się do tego nazwanego zakresu za pomocą funkcji ADR.POŚR.
Załóżmy na przykład, że masz oceny dla 5 uczniów z trzech przedmiotów, jak pokazano poniżej:
W tym przykładzie nazwijmy komórki:
- B2:B6: Matematyka
- C2:C6: Fizyka
- D2:D6: Chemia
Aby nazwać zakres komórek, po prostu zaznacz komórki i przejdź do pola nazwy, wprowadź nazwę i naciśnij Enter.
Teraz możesz odwołać się do tych nazwanych zakresów za pomocą formuły:
=ADR.POŚR("Nazwany zakres")
Na przykład, jeśli chcesz poznać średnią ocen z matematyki, użyj wzoru:
=ŚREDNIA(POŚREDNIA("Matematyka"))
Jeśli masz nazwaną nazwę zakresu w komórce (F2 w poniższym przykładzie ma nazwę Matematyka), możesz użyć jej bezpośrednio w formule.
Poniższy przykład pokazuje, jak obliczyć średnią przy użyciu nazwanych zakresów.
Przykład 7: Tworzenie zależnej listy rozwijanej przy użyciu funkcji pośredniej programu Excel
To doskonałe zastosowanie tej funkcji. Za jego pomocą można łatwo utworzyć zależną listę rozwijaną (nazywaną również warunkową listą rozwijaną).
Załóżmy na przykład, że masz listę krajów w wierszu i nazwy miast dla każdego kraju, jak pokazano poniżej:
Teraz, aby utworzyć zależną listę rozwijaną, musisz utworzyć dwa nazwane zakresy, A2:A5 o nazwie US i B2:B5 o nazwie Indie.
Teraz wybierz komórkę D2 i utwórz listę rozwijaną dla Indii i USA. Byłaby to pierwsza lista rozwijana, w której użytkownik ma możliwość wyboru kraju.
Teraz utwórz zależną listę rozwijaną:
- Wybierz komórkę E2 (komórkę, w której chcesz uzyskać zależną listę rozwijaną).
- Kliknij kartę Dane
- Kliknij Sprawdzanie danych.
- Wybierz opcję Lista jako kryteria walidacji i użyj następującej formuły w polu źródłowym: = ADR.POŚR($D$2)
- Kliknij OK.
Teraz, gdy wprowadzisz Stany Zjednoczone w komórce D2, lista rozwijana w komórce E2 pokaże stany w Stanach Zjednoczonych.
A kiedy wprowadzisz Indie w komórce D2, menu rozwijane w komórce E2 pokaże stany w Indiach.
Oto kilka przykładów użycia funkcji ADR.POŚR w programie Excel. Te przykłady będą działać we wszystkich wersjach programu Excel (Office 365, Excel2021-2022/2016/2013/2013)
Mam nadzieję, że ten samouczek okazał się przydatny.
- Funkcja WYSZUKAJ.PIONOWO Excela.
- Funkcja WYSZUKAJ.POZIOMO Excela.
- Funkcja INDEKS Excela.
- Funkcja PODAJ.POZYCJĘ Excela.
- Funkcja przesunięcia Excela.
