Funkcja INDIRECT w Microsoft Excel

Jedną z wbudowanych funkcji programu Excel jest INDYWIDUALNA . Jego zadaniem jest powrót do elementu arkusza, w którym znajduje się zawartość komórki, która jest w nim wskazana jako argument linku w formacie tekstowym.

Wydaje się, że nie ma w tym nic szczególnego, ponieważ możliwe jest wyświetlanie zawartości jednej komórki w drugiej na bardziej proste sposoby. Jednak, jak się okazuje, pewne niuanse są związane z używaniem tego operatora, co czyni go wyjątkowym. W niektórych przypadkach ta formuła jest w stanie rozwiązać takie problemy, z którymi inne metody po prostu nie mogą sobie poradzić lub będzie to znacznie trudniejsze. Dowiemy się bardziej szczegółowo, czym jest operator DVSSYL i jak można go wykorzystać w praktyce.

Zastosowanie wzoru

Sama nazwa tego operatora DVSSYL oznacza "Podwójne odniesienie" . W rzeczywistości oznacza to jego cel - dane wyjściowe poprzez łącze z jednej komórki do drugiej. I, w przeciwieństwie do większości innych funkcji, które działają z łączami, należy je określić w formacie tekstowym, tzn. Alokować po obu stronach w cudzysłów.

Ten operator należy do kategorii funkcji "References and arrays" i ma następującą składnię:

=ДВССЫЛ(ссылка_на_ячейку;[a1])

Tak więc formuła ma tylko dwa argumenty.

Argument "Link do komórki" jest przedstawiany jako łącze do elementu arkusza, dane w nim zawarte muszą być wyświetlane. W takim przypadku określone łącze musi mieć formę tekstową, czyli być "zapakowane" w cudzysłów.

Argument "A1" nie jest obowiązkowy iw większości przypadków nie trzeba go w ogóle podawać. Może mieć dwa znaczenia: "TRUE" i "FALSE" . W pierwszym przypadku operator definiuje łącza w stylu "A1" , czyli ten styl jest domyślnie zawarty w Excelu. Jeśli wartość argumentu nie zostanie podana w ogóle, wówczas zostanie uznana dokładnie za "PRAWDA" . W drugim przypadku odniesienia definiowane są w stylu "R1C1" . Ten styl linków powinien być konkretnie zawarty w ustawieniach programu Excel.

Mówiąc prościej, DVSSYL jest rodzajem ekwiwalentu łączenia jednej komórki z drugą po znaku "równości". Na przykład w większości przypadków wyrażenie

=ДВССЫЛ("A1")

będzie równoznaczne z wyrażeniem

=A1

Ale w przeciwieństwie do wyrażenia "= A1", operator jest przypisany nie do konkretnej komórki, ale do współrzędnych elementu na arkuszu.

Rozważmy, co to oznacza w najprostszym przykładzie. W komórkach B8 i B9 zapisana jest formuła zapisana z "=", a funkcja jest ALTERNATYWNA . Obie formuły odnoszą się do elementu B4 i wyświetlają jego zawartość na arkuszu. Oczywiście ta treść jest taka sama.

Dodajemy jeszcze jeden pusty element do tabeli. Jak widać, linie przesunęły się. We wzorze z użyciem "równości" wartość pozostała taka sama, ponieważ odnosi się do końcowej komórki, nawet jeśli zmieniły się jej współrzędne, ale dane wyjściowe operatora zostały zmienione. Wynika to z faktu, że odnosi się to nie do elementu arkusza, ale do współrzędnych. Po dodaniu linii adres B4 zawiera kolejny element arkusza. Jego treść jest teraz formułą i wyświetla się na arkuszu.

Ten operator może wysyłać do innej komórki nie tylko liczby, ale także tekst, wynik obliczania formuł i wszelkie inne wartości, które znajdują się w wybranym elemencie arkusza. Ale w praktyce funkcja ta rzadko jest używana samodzielnie, ale znacznie częściej stanowi integralną część złożonych formuł.

Należy zauważyć, że operator ma zastosowanie do linków do innych arkuszy, a nawet do zawartości innych książek Excel, ale w tym przypadku muszą one zostać uruchomione.

Teraz przyjrzyjmy się konkretnym przykładom zastosowania operatora.

Przykład 1: Pojedyncza aplikacja operatora

Na początek przyjrzyjmy się najprostszemu przykładowi, w którym funkcja INDEX działa samodzielnie, abyś mógł zrozumieć istotę swojej pracy.

Mamy arbitralny stół. Zadaniem jest wyświetlenie danych pierwszej komórki pierwszej kolumny w pierwszym elemencie oddzielnej kolumny przy użyciu badanego formuły.

1. Wybierz pierwszy pusty element kolumny, w którym planujemy wstawić formułę. Klikamy na ikonę "Wstaw funkcję" .
2. Pojawi się okno Kreatora opcji. Przechodzimy do kategorii "Referencje i tablice" . Z listy wybierz wartość "ALBUM" . Klikamy przycisk "OK" .
3. Zostanie uruchomione okno dla argumentów określonego operatora. W polu "Link do komórki" należy podać adres tego elementu na arkuszu, którego zawartość zostanie wyświetlona. Oczywiście można go wprowadzić ręcznie, ale będzie to o wiele bardziej praktyczne i wygodne, aby wykonać następujące czynności. Ustaw kursor w polu, a następnie kliknij lewym przyciskiem myszy na odpowiednim elemencie na arkuszu. Jak widać, tuż po tym jego adres był wyświetlany w polu. Następnie z obu stron wybierz link z cudzysłowem. Jak pamiętamy, jest to cecha pracy z argumentem tej formuły.

   W polu "A1" , ponieważ pracujemy w zwykłym typie współrzędnych, możemy wpisać wartość "PRAWDA" , a możesz zostawić ją pustą, co zrobimy. Będzie to równoważne działanie.

   Następnie kliknij przycisk "OK" .

4. Jak widać, teraz zawartość pierwszej komórki pierwszej kolumny tabeli jest wyświetlana w tym elemencie arkusza, w którym znajduje się formuła " POŚREDNIA" .
5. Jeśli chcemy zastosować tę funkcję w komórkach znajdujących się poniżej, to w tym przypadku będziemy musieli wprowadzić formułę do każdego elementu osobno. Jeśli spróbujemy skopiować go za pomocą znacznika wypełnienia lub innej metody kopiowania, wówczas wszystkie elementy kolumny będą wyświetlały tę samą nazwę. Chodzi o to, że jak pamiętamy, link działa jako argument w formie tekstowej (zawarty w cudzysłowie), a zatem nie może być względny.

Lekcja: Kreator funkcji w programie Excel

Przykład 2: użycie operatora w złożonej formule

A teraz spójrzmy na przykład znacznie częstszego korzystania z operatora DVSSYL , gdy jest on integralną częścią złożonej formuły.

Posiadamy miesięczną tabelę dochodu przedsiębiorstwa. Musimy obliczyć kwotę dochodu na określony czas, na przykład od marca do maja lub od czerwca do listopada. Oczywiście możesz użyć prostej formuły podsumowania, aby to zrobić, ale w tym przypadku, jeśli musisz obliczyć całkowity wynik dla każdego okresu, zawsze będziemy musieli zmienić tę formułę. Ale dzięki funkcji EVALUATE możliwe będzie dokonanie zmiany w zsumowanym zakresie, po prostu w poszczególnych komórkach wskazujących odpowiedni miesiąc. Spróbujemy najpierw skorzystać z tej opcji, aby obliczyć kwotę za okres od marca do maja. W takim przypadku zostanie użyta formuła z kombinacją operatorów SUM i SAS .

1. Przede wszystkim, w osobnych elementach na arkuszu, wpisujemy nazwy miesięcy początku i końca okresu, dla którego będą dokonywane obliczenia, odpowiednio, "marzec" i "maj" .
2. Teraz przypisz nazwę do wszystkich komórek w kolumnie "Przychody" , która będzie podobna do nazwy odpowiedniego miesiąca. Oznacza to, że pierwszy element w kolumnie "Przychody" , który zawiera kwotę przychodów, należy nazwać "Styczeń" , drugi - "Luty" itp.

   Aby nazwać pierwszy element kolumny, zaznacz go i kliknij prawym przyciskiem myszy. Otwiera menu kontekstowe. Wybierz pozycję "Przypisz nazwę ..." .

3. Otworzy się okno do tworzenia nazwy. W polu "Nazwa" wpisujemy nazwę "Styczeń" . Nie musisz wprowadzać żadnych zmian w oknie, ale na wszelki wypadek możesz sprawdzić, czy współrzędne w polu "Zakres" odpowiadają adresowi komórki zawierającej kwotę przychodów za styczeń. Następnie kliknij przycisk "OK" .
4. Jak widać, teraz, po wybraniu tego elementu w oknie nazwy, wyświetla się nie jego adres, ale nazwa, którą mu daliśmy. Podobną operację wykonujemy z wszystkimi pozostałymi elementami kolumny "Przychody" , przypisując kolejno nazwy "Luty" , "Marca" , "Kwietnia" itp. do grudnia włącznie.
5. Wybierz komórkę, w której będzie wyświetlana suma wartości określonego przedziału, i wybierz ją. Następnie kliknij ikonę "Wstaw funkcję" . Jest umieszczony po lewej stronie linii formuły i po prawej stronie pola, w którym wyświetlana jest nazwa komórek.
6. W aktywowanym oknie kreatora funkcji przechodzimy do kategorii "Matematyczne" . Tam wybieramy nazwę "SUMM" . Klikamy przycisk "OK" .
7. Po wykonaniu tej akcji uruchamiane jest okno argumentów operatora SUMM , którego jedynym zadaniem jest sumowanie wskazanych wartości. Składnia tej funkcji jest bardzo prosta:

   =СУММ(число1;число2;…)

   Ogólnie liczba argumentów może osiągnąć 255 . Ale wszystkie te argumenty są jednorodne. Reprezentują liczbę lub współrzędne komórki, w której ta liczba jest zawarta. Mogą również działać jako wbudowana formuła, która oblicza pożądaną liczbę lub wskazuje adres elementu arkusza, w którym jest umieszczony. W tym przypadku wbudowanej funkcji będziemy używać w tym przypadku operatora DVSSYL .

   Ustaw kursor w polu "Numer 1" . Następnie kliknij ikonę w kształcie odwróconego trójkąta po prawej stronie pola nazwy zakresu. Zostanie otwarta lista ostatnio używanych funkcji. Jeśli wśród nich jest nazwa "DVSSYL" , to od razu kliknij na nią, aby przejść do okna argumentów tej funkcji. Ale może się okazać, że nie znajdziesz go na tej liście. W takim przypadku kliknij nazwę "Inne funkcje ..." na samym dole listy.

8. Okno Kreatora funkcji już nam znanych zaczyna. Przechodzimy do sekcji "Referencje i tablice" i wybieramy nazwę operatora DVSSYL . Po tej czynności kliknij przycisk "OK" w dolnej części okna.
9. Wyświetlane jest okno argumentu operatora INDEX . W polu "Link do komórki" podajemy adres elementu arkusza, który zawiera nazwę początkowego miesiąca zakresu kwoty przeznaczonej do obliczenia. Zauważ, że w tym przypadku nie musisz łączyć się z cudzysłowami, ponieważ w tym przypadku adres nie będzie współrzędną komórki, ale jej zawartość, która ma już format tekstowy (słowo "marzec" ). Pole "A1" pozostaje puste, ponieważ używamy standardowego typu oznaczenia współrzędnych.

   Po wyświetleniu adresu w polu, nie spiesz się, aby nacisnąć przycisk "OK" , ponieważ jest to funkcja zagnieżdżona, a akcje z nią związane różnią się od zwykłego algorytmu. Klikamy na nazwę "SUMM" na pasku formuły.

10. Następnie wracamy do okna argumentów SUMM. Jak widać, pole "Numer 1" już wyświetliło operator DVSYL wraz z jego zawartością. Ustaw kursor na tym samym polu zaraz po ostatnim znaku w rekordzie. Umieść dwukropek ( :) . Ten symbol oznacza znak adresu zakresu komórek. Następnie, bez usuwania kursora z pola, ponownie kliknij ikonę w kształcie trójkąta, aby wybrać funkcje. Tym razem na liście ostatnio używanych operatorów nazwa "DVSSYL" powinna być dokładnie obecna, ponieważ ostatnio użyliśmy tej funkcji. Klikamy na nazwie.
11. Okno argumentu operatora INDEX ponownie się otworzy. Wprowadzamy adres elementu na arkuszu w polu "Cell reference" , gdzie znajduje się nazwa miesiąca, który kończy okres obliczeniowy. Znów współrzędne muszą być wpisane bez cudzysłowów. Pole "A1" jest ponownie puste. Następnie kliknij przycisk "OK" .
12. Jak widać, po tych działaniach program oblicza i wyświetla wynik dodania dochodu przedsiębiorstwa za określony okres (marzec - maj) do wcześniej wybranego elementu arkusza, w którym znajduje się sama formuła.
13. Jeśli zmienimy w komórkach, w których wprowadzane są nazwy miesięcy początku i końca okresu rozliczeniowego, dla innych, na przykład "czerwiec" i "listopad" , wynik odpowiednio się zmieni. Kwota dochodu za określony okres czasu zostanie dodana.

Lekcja: Jak obliczyć kwotę w Excelu

Jak widać, pomimo tego, że funkcja ta nie jest jedną z najpopularniejszych wśród użytkowników, pomaga rozwiązywać zadania o różnej złożoności w Excelu znacznie łatwiej niż przy pomocy innych narzędzi. Przede wszystkim operator ten jest przydatny w złożonych formułach, w których jest integralną częścią wyrażenia. Ale należy zauważyć, że wszystkie możliwości operatora DVSSYL są dość trudne do zrozumienia. Wyjaśnia to niską popularność tej użytecznej funkcji dla użytkowników.

• ← Wklejanie komórek na podstawie wartości w programie Microsoft Excel
• Wstawianie tekstu w komórce za pomocą formuły w programie Microsoft Excel →