Pobierz przykładowy skoroszyt
Ten samouczek pokazuje, jak używać Excel ADR.Funkcja w programie Excel, aby utworzyć odwołanie do komórki z tekstu.
INDIRECT Przegląd funkcji
Funkcja ADR.POŚR Tworzy odwołanie do komórki z ciągu tekstowego.
(Zwróć uwagę, jak pojawiają się dane wejściowe formuły)
Funkcja ADR.POŚR Składnia i wejścia:
| 1 | =ADR.POŚR(tekst_odn;C1) |
ref_text - Ciąg reprezentujący odwołanie do komórki lub odwołanie do zakresu. Ciąg może być w formacie R1C1 lub A1 albo może być nazwanym zakresem.
a1 - OPCJONALNIE: Wskazuje, czy odwołanie jest w formacie R1C1 czy A1. FAŁSZ dla R1C1 lub PRAWDA / Pominięte dla A1.
Co to jest funkcja ADR.POŚR?
Funkcja ADR.POŚR umożliwia podanie ciągu tekstowego i zinterpretowanie go przez komputer jako rzeczywiste odwołanie. Może to służyć do odwoływania się do zakresu w tym samym arkuszu, innym arkuszu, a nawet innym skoroszycie.
UWAGA: Funkcja POŚREDNIA jest jedną z funkcji ulotnych. W większości przypadków, gdy pracujesz w arkuszu kalkulacyjnym, komputer ponownie obliczy formułę tylko wtedy, gdy dane wejściowe zmieniły swoje wartości. Jednak funkcja lotna przelicza każdy kiedy dokonasz zmiany w dowolnej komórce. Należy zachować ostrożność, aby upewnić się, że nie spowodujesz długiego czasu ponownego obliczenia z powodu nadmiernego użycia funkcji lotnej lub uzależnienia wielu komórek od wyniku funkcji ulotnej.
Utwórz odwołanie do komórki
Powiedz, że chcesz pobrać wartość z A2, ale chcesz się upewnić, że formuła gorset na A2 niezależnie od wstawiania/usuwania nowych wierszy. Możesz napisać formułę
| 1 | =ADR.POŚR("A2") |
Zauważ, że argumentem wewnątrz naszej funkcji jest ciąg tekstowy „A2”, a nie odwołanie do komórki. Ponadto, ponieważ jest to ciąg tekstowy, nie ma potrzeby wskazywania odniesienia bezwzględnego, takiego jak $A$2. Tekst nigdy się nie zmieni, a zatem ta formuła zawsze będzie wskazywać na A2, bez względu na to, dokąd zostanie przeniesiona.
POŚREDNI numer wiersza
Możesz łączyć ze sobą ciągi tekstowe i wartości z komórek. Zamiast pisać „A2”, tak jak poprzednio, możemy pobrać wartość liczbową z komórki B2 i użyć jej w naszej formule. Napisalibyśmy formułę jak
| 1 | =ADR.POŚR("A"&B2) |
Symbol „&” jest tutaj używany do łączenia ciągu tekstowego „A” z wartością z komórki B2. Tak więc, jeśli wartość B2 wynosi obecnie 10, to nasz wzór odczytałby to jako
| 123 | =ADR.POŚR("A"&10)=ADR.POŚR("A10")=A10 |
INDIRECT wartość kolumny
Możesz także łączyć w odwołaniu do kolumny. Tym razem powiedzmy, że wiemy, że chcemy pobrać wartość z wiersza 10, ale chcemy mieć możliwość zmiany kolumny, z której ma być pobierana. Umieścimy literę kolumny, którą chcemy w komórce B2. Nasza formuła mogłaby wyglądać
| 1 | =ADR.POŚR(B2 & "10") |
Jeśli wartością B2 jest „G”, to nasz wzór wylicza się tak
| 123 | =POŚREDNIA("G"&10)=ADR.POŚR("G10")=G10 |
POŚREDNI styl r1c1
W naszym poprzednim przykładzie musieliśmy użyć litery, aby wskazać odwołanie do kolumny. Dzieje się tak, ponieważ używaliśmy tak zwanych odwołań do stylu A1. W stylu A1 kolumny są oznaczone literą, a wiersze liczbami. Odwołania bezwzględne są oznaczone znakiem „$” przed pozycją, która ma pozostać bezwzględna.
W r1c1 zarówno wiersze, jak i kolumny zaczynają się od numeru. Bezwzględne odniesienie do a1 zostałoby zapisane jako
| 1 | =R1K1 |
Możesz to przeczytać jako „Wiersz 1, Kolumna 1”. Odnośniki względne są podane w nawiasach, ale liczba wskazuje pozycję względem komórki z formułą. Jeśli więc piszemy formułę w komórce A10 i musimy odwołać się do komórki A1, napisalibyśmy formułę
| 1 | =R[-9]C |
Możesz to przeczytać jako „Komórka 9 rzędów w górę, ale w tej samej kolumnie.
Powodem, dla którego może to być pomocne, jest fakt, że ADR.POŚR może obsługiwać notację r1c1. Rozważ poprzedni przykład, w którym pobieraliśmy wartość z wiersza 10, ale chcieliśmy mieć możliwość zmiany kolumny. Zamiast podawać list, załóżmy, że w komórce B2 wpisujemy liczbę. Nasza formuła może wtedy wyglądać tak:
| 1 | =ADR.POŚR("R10C"&B2,FAŁSZ) |
Pomijaliśmy 2NS kłótnia do tej pory. Jeśli ten argument zostanie pominięty lub True, funkcja będzie oceniać przy użyciu stylu A1. Ponieważ jest to False, zostanie ocenione w r1c1. Załóżmy, że wartość B2 wynosi 5. Nasz wzór oceni to w ten sposób
| 12 | =ADR.POŚR("R10C5";FAŁSZ)=$E$10 |
POŚREDNIE różnice z A1 vs r1c1
Pamiętasz, że poprzednio pokazaliśmy, że skoro zawartość tej formuły była ciągiem tekstowym, nigdy się nie zmieniła?
| 1 | =ADR.POŚR("A2") |
Ta formuła zawsze będzie patrzeć na komórkę A2, bez względu na to, gdzie przeniesiesz formułę. W r1c1, ponieważ możesz wskazać pozycję względną za pomocą nawiasów, ta zasada nie jest spójna. Jeśli umieścisz tę formułę w komórce B2
| 1 | =ADR.POŚR("RC[-1]") |
Będzie patrzeć na komórkę A2 (ponieważ kolumna A znajduje się na lewo od kolumny B). Jeśli skopiujesz tę formułę do komórki B3, tekst wewnątrz pozostanie taki sam, ale ADR.POŚR będzie teraz patrzeć na komórkę A3.
POŚREDNI z nazwą arkusza
Możesz również połączyć nazwę arkusza z odwołaniami POŚREDNIMI. Ważną zasadą do zapamiętania jest to, że należy umieszczać pojedyncze cudzysłowy wokół nazw i należy oddzielić nazwę arkusza od odwołania do komórki za pomocą wykrzyknika.
Załóżmy, że mamy tę konfigurację, w której podajemy nazwę naszego arkusza, wiersz i kolumnę.
Nasz wzór na połączenie wszystkich tych elementów w referencję wygląda tak:
| 1 | =POŚREDNIA("'" & A2 & "'!" & B2 & C2) |
Nasza formuła zostanie następnie oceniona w następujący sposób:
| 123 | =ADR.POŚR("'" & "Arkusz2" & "'!" & "B" & "5")=ADR.POŚR("'"Arkusz2'!B5")='Arkusz2'!B5 |
Technicznie rzecz biorąc, ponieważ w słowie „Arkusz2” nie ma spacji, nie mamy potrzebować pojedyncze cudzysłowy. Można napisać coś takiego jak
| 1 | =Arkusz2!A2 |
Nie zaszkodzi jednak umieścić cudzysłowy, gdy ich nie potrzebujesz. Najlepszą praktyką jest ich uwzględnienie, aby formuła mogła obsłużyć wystąpienie, w którym mogą być potrzebne.
POŚREDNIO do innego skoroszytu
Wspomnimy również, że ADR.POŚR może utworzyć odwołanie do innego skoroszytu. Ograniczeniem jest to, że ADR.POŚR nie pobiera wartości z zamkniętego skoroszytu, więc to konkretne zastosowanie ma ograniczoną praktyczność. Jeśli skoroszyt, na który wskazuje funkcja POŚREDNIA, jest nieotwarty, funkcja zwróci „#REF!” błąd.
Składnia podczas pisania nazwy skoroszytu jest taka, że musi być ona w nawiasach kwadratowych. Użyjmy tej konfiguracji i spróbujmy pobrać wartość z komórki C7.
Nasza formuła będzie
| 1 | =ADR.POŚR("'[" & A2 & "]" & B2 & "'!C7") |
Ponownie zwróć uwagę na rozmieszczenie pojedynczych cudzysłowów, nawiasów i wykrzyknika. Nasza formuła zostanie następnie oceniona w następujący sposób:
| 123 | =ADR.POŚR("'[" & "Przykład.xlsx" & "]" & "Podsumowanie" & "'!C7")=ADR.POŚR("'[Przykład.xslx]Podsumowanie'!C7")='[Przykład.xlsx]Podsumowanie'!C7 |
POŚREDNI do budowania zakresu dynamicznego
Gdy masz duży zestaw danych, ważne jest, aby spróbować zoptymalizować formuły, aby nie wykonywały więcej pracy niż to konieczne. Na przykład, zamiast odwoływać się do całej kolumny A, możemy po prostu odwołać się do dokładnej liczby komórek na naszej liście. Rozważ następujący układ:
W komórce B2 umieściliśmy formułę
| 1 | =ILE.LICZB(A:A) |
Funkcja ILE.NIEPUSTYCH jest bardzo łatwa do obliczenia przez komputer, ponieważ po prostu sprawdza, ile komórek w kolumnie A ma jakąś wartość, w przeciwieństwie do konieczności wykonywania jakichkolwiek kontroli logicznych lub operacji matematycznych.
Teraz zbudujmy naszą formułę, która zsumuje wartości w kolumnie A, ale chcemy mieć pewność, że uwzględnia tylko dokładny zakres wartości (A2:A5). Napiszemy naszą formułę jako
| 1 | =SUMA(ADR.POŚR("A2:A"&B2)) |
Nasza funkcja ADR.POŚR pobierze liczbę 5 z komórki B2 i utworzy odwołanie do zakresu A2:A5. Suma może następnie wykorzystać ten zakres do obliczeń. Jeśli dodamy kolejną wartość do komórki A6, liczba w B2 zostanie zaktualizowana, a nasza formuła SUMA zaktualizuje się automatycznie, aby uwzględnić tę nową wartość.
PRZESTROGA: Wraz z wprowadzeniem tabel w pakiecie Office 2007 znacznie wydajniejsze jest przechowywanie danych w tabeli i używanie odwołań strukturalnych zamiast tworzenia formuły, której użyliśmy w tym przykładzie, ze względu na niestabilny charakter funkcji ADR.POŚR. Jednak mogą to być przypadki, w których musisz utworzyć listę przedmiotów i nie możesz użyć tabeli.
Dynamiczne wykresy z INDIRECT
Weźmy poprzedni przykład i przejdźmy jeszcze jeden krok. Zamiast pisać formułę, która da nam sumę wartości, utworzymy nazwany zakres. Możemy nazwać ten zakres „MyData” i odnieść się do
| 1 | =ADR.POŚR("A2:A"&LICZBA($A:$A)) |
Zauważ, że ponieważ umieszczamy to w nazwanym zakresie, zamieniliśmy odwołanie na B2 i zamiast tego umieściliśmy tam bezpośrednio funkcję ILE.NIEPUSTYCH.
Teraz, gdy mamy ten nazwany zakres, możemy go użyć na wykresie. Utworzymy pusty wykres liniowy, a następnie dodamy serię danych. Dla wartości serii możesz napisać coś takiego:
| 1 | =Arkusz1!MojeDane |
Wykres będzie teraz używał tego odniesienia do wykreślania wartości. W miarę dodawania większej liczby wartości do kolumny A, POŚREDNIA będzie odnosić się do coraz większego zakresu, a nasz wykres będzie nadal aktualizowany wszystkimi nowo dodanymi wartościami.
Dynamiczna walidacja danych z INDIRECT
Podczas zbierania informacji od użytkowników, czasami istnieje potrzeba uzależnienia jednej opcji do wyboru od wcześniejszego wyboru. Rozważ ten układ, w którym nasza pierwsza kolumna pozwala użytkownikowi wybrać między owocami, warzywami i mięsem.
W 2NS kolumna, nie chcemy mieć dużej listy pokazującej każdy możliwy wybór, ponieważ już trochę zawęziliśmy. Stworzyliśmy więc 3 inne listy, które wyglądają tak:
Następnie przypiszemy każdy z te listy do nazwanego zakresu. Oznacza to, że wszystkie owoce będą w gamie zwanej „Owoce”, a warzywa w „Warzywa” itp.
Wracając do naszej tabeli, jesteśmy gotowi do skonfigurowania walidacji danych w 2NS kolumna. Stworzymy walidację typu List z danymi wejściowymi:
| 1 | =ADR.POŚR(A2) |
INDIRECT odczyta wybór dokonany w kolumnie A i zobaczy nazwę kategorii. Zdefiniowaliśmy zakresy o tych nazwach, więc funkcja ADR.POŚR przyjmie tę nazwę i utworzy odwołanie do żądanego zakresu.
Dodatkowe uwagi
Użyj funkcji ADR.POŚR, aby utworzyć odwołanie do komórki z tekstu.
Najpierw utwórz ciąg tekstowy, który reprezentuje odwołanie do komórki. Ciąg musi być w zwykłej literze kolumny i numerze wiersza w stylu A1 (M37) lub w stylu R1C1 (R37C13). Możesz wpisać odwołanie bezpośrednio, ale zwykle będziesz odwoływał się do komórek, które definiują wiersze i kolumny. Na koniec wprowadź wybrany format odwołania do komórki. PRAWDA lub pominięta dla odwołania stylu A1 lub FAŁSZ dla stylu R1K1.
Podczas pracy z formułami POŚREDNIMI możesz chcieć użyć Funkcja WIERSZ aby uzyskać numer wiersza odniesienia lub KOLUMNA Funkcja aby uzyskać numer kolumny (nie literę) referencji.
Wróć do listy wszystkich funkcji w Excelu
POŚREDNI w Arkuszach Google
Funkcja ADR.POŚR działa dokładnie tak samo w Arkuszach Google, jak w Excelu:
