ADR.POŚR (INDIRECT) w Excelu: tekst jako odwołanie

=ADR.POŚR(E2) (po angielsku INDIRECT) odczytuje komórkę, której adres jest wpisany jako tekst w E2. Jeśli E2 zawiera C4, formuła zwraca wartość z C4. Adres można też zbudować z części: =ADR.POŚR("C"&E3) odczytuje kolumnę C w wierszu o numerze z E3. Tabele pokazują formuły po angielsku, ale możesz w nich wpisywać formuły także po polsku, ze średnikami.

FormułyResetuj

F2fx

=INDIRECT(E2)

	A	B	C	D	E	F
1	Product	Category	Price		Address	Value
2	Apple	Fruit	$1.20		C4	$0.80
3	Pear	Fruit	$1.50		6	$1.10
4	Carrot	Vegetable	$0.80
5	Bread	Bakery	$2.40
6	Milk	Dairy	$1.10

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.W polskim Excelu: =ADR.POŚR(E2)

F2 odczytuje C4, cenę Carrot, $0.80. Zmień E2 na C3 albo B5, a F2 się dostosuje. F3 łączy "C" i 6 z E3 w adres C6 i zwraca $1.10. Zmień E3 na 2, aby dostać cenę Apple.

Składnia funkcji ADR.POŚR

=INDIRECT(ref_text, [a1])

• ref_text (adres_tekst): tekst, który zapisuje odwołanie: "C4", "B2:B6", "Prices!A2", "'Price list'!A2:B9".
• a1: PRAWDA albo pominięty dla adresów w stylu A1. FAŁSZ czyta styl R1C1, w którym "R4C3" oznacza wiersz 4, kolumnę 3; to pasuje, gdy i wiersz, i kolumna są liczbami. W polskim Excelu ten styl zapisuje się jako W1K1 (wiersz, kolumna), czyli "W4K3".

Jeśli tekst nie jest poprawnym adresem, wynikiem jest #ADR! (po angielsku #REF!). ADR.POŚR zwraca prawdziwe odwołanie, więc działa w SUMA, LICZ.JEŻELI, WYSZUKAJ.PIONOWO i każdej funkcji, która przyjmuje zakres.

Zakres zbudowany z liczb

Adres może być całym zakresem. Doklejenie do niego liczby daje zakres, którego rozmiar pochodzi z komórki.

FormułyResetuj

F2fx

=SUM(INDIRECT("B2:B"&(1+E2)))

	A	B	C	D	E	F
1	Month	Sales			Rows	Total
2	Jan	4,200			3	12,900
3	Feb	3,900
4	Mar	4,800
5	Apr	5,100
6	May	4,600
7	Jun	5,300

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.W polskim Excelu: =SUMA(ADR.POŚR("B2:B"&(1+E2)))

Przy 3 w E2 tekst zmienia się w B2:B4, a F2 dodaje Jan do Mar: 12,900. W polskim Excelu formuła to =SUMA(ADR.POŚR("B2:B"&(1+E2))). Ustaw E2 na 6, aby dostać pół roku, 27,900. 1+E2 jest potrzebne, bo dane zaczynają się w wierszu 2. Tę samą sumę można zapisać bez ADR.POŚR, =SUMA(B2:INDEKS(B2:B7;E2)), która nie jest nietrwała; strona o PRZESUNIĘCIE porównuje te sposoby.

Odwołanie do arkusza nazwanego w komórce

Nazwa arkusza też może pochodzić z komórki. Dzięki temu jedna formuła podsumowania staje się wyszukiwaniem po arkuszach: każdy wiersz czyta arkusz nazwany w kolumnie A. Apostrofy wokół nazwy sprawiają, że działa to także dla nazw ze spacjami.

FormułyResetuj

B2fx

=SUM(INDIRECT("'"&A2&"'!B2:B4"))

	A	B
1	Month	Total
2	Jan	12,500
3	Feb	12,200
4	Mar	13,700

SummaryJanFebMar

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.W polskim Excelu: =SUMA(ADR.POŚR("'"&A2&"'!B2:B4"))

B2 buduje tekst 'Jan'!B2:B4 i go sumuje: 12,500. W polskim Excelu formuła to =SUMA(ADR.POŚR("'"&A2&"'!B2:B4")). B3 i B4 to ta sama formuła skopiowana w dół, więc czytają Feb (12,200) i Mar (13,700). Otwórz kartę Feb i zmień liczbę: podsumowanie się dostosuje. Wpisz Feb w miejsce Jan w A2, a B2 zsumuje Feb. B2:B4 w cudzysłowie to tekst, więc nie zmienia się przy kopiowaniu formuły w dół; zmienia się tylko odwołanie do A2.

Zależne listy rozwijane

Druga lista rozwijana, której elementy zależą od pierwszej, to klasyczne zadanie dla ADR.POŚR. W Excelu zwykle robi się to tak:

1. Elementy każdej kategorii wpisz w osobnej kolumnie i nazwij każdy zakres nazwą kategorii: zaznacz kolumny razem z nagłówkami i użyj Formuły > Utwórz z zaznaczenia > Górny wiersz. To tworzy nazwy Fruit, Vegetable i Dairy.
2. Nadaj A2 listę kategorii: Dane > Poprawność danych, Zezwalaj: Lista, Źródło Fruit,Vegetable,Dairy (w polskim Excelu elementy oddzielasz średnikami).
3. Nadaj B2 listę ze Źródłem =ADR.POŚR(A2). Gdy A2 zawiera Fruit, lista czyta zakres o nazwie Fruit.

Tabela poniżej buduje to samo z arkuszem dla każdej kategorii zamiast nazwanego zakresu. D2 używa ADR.POŚR, aby rozlać elementy arkusza nazwanego w A2, a lista w B2 czyta D2:D4.

FormułyResetuj

D2fx

=INDIRECT("'"&A2&"'!A2:A4")

	A	B	C	D
1	Category	Item		Items for the category
2	Fruit▾	Apple▾		Apple
3				Pear
4				Plum

FormFruitVegetableDairy

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.W polskim Excelu: =ADR.POŚR("'"&A2&"'!A2:A4")

Wybierz Dairy w A2: D2:D4 zmienia się na Milk, Butter, Cheese, a razem z nimi wybory w B2. B2 zachowuje starą wartość, dopóki nie wybierzesz nowej; Excel zachowuje się tak samo, dlatego formularze często dodają obok elementu sprawdzenie, na przykład =LICZ.JEŻELI(D2:D4;B2)>0. W Excelu 365 możesz pominąć nazwane zakresy i skierować drugą listę na rozlaną formułę, na przykład =ADR.POŚR("'"&A2&"'!A2:A4") w komórce pomocniczej i =D2# jako Źródło. Strona o liście rozwijanej opisuje resztę konfiguracji.

ADR.POŚR jest nietrwała i ignoruje wstawione wiersze

Dwa efekty uboczne wynikają z tego, że ADR.POŚR czyta tekst zamiast odwołania:

• Przelicza się po każdej zmianie. Excel nie wie, na które komórki wskaże tekst, więc przelicza każdą funkcję ADR.POŚR po każdej edycji w dowolnym miejscu skoroszytu. Kilkadziesiąt nie szkodzi; dziesiątki tysięcy spowalniają każde naciśnięcie klawisza. INDEKS z numerem wiersza (=INDEKS(C:C;E3)) daje ten sam wynik co =ADR.POŚR("C"&E3) i przelicza się tylko wtedy, gdy zmieniają się jego dane wejściowe.
• Adres się nie przesuwa. Wstaw wiersz nad wierszem 4, a =C4 zmieni się w =C5, ale =ADR.POŚR("C4") nadal czyta C4, który jest teraz innym wierszem. Czasem właśnie o to chodzi: odwołanie ma zostać na stałej komórce bez względu na to, co dzieje się z arkuszem. Częściej to błąd, który czeka, aż ktoś wstawi wiersz.

ADR.POŚR do innego skoroszytu działa tylko wtedy, gdy ten skoroszyt jest otwarty; gdy jest zamknięty, zwraca #ADR!.

Ćwiczenie: cena według numeru wiersza

FormułyResetuj

F2fx

	A	B	C	D	E	F
1	Product	Category	Price		Row	Price
2	Apple	Fruit	$1.20		5
3	Pear	Fruit	$1.50
4	Carrot	Vegetable	$0.80
5	Bread	Bakery	$2.40
6	Milk	Dairy	$1.10

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.

Twoja kolej: W F2 użyj ADR.POŚR, aby zwrócić cenę z kolumny C w wierszu o numerze wpisanym w E2.

SprawdźPokaż odpowiedź