Funkcja POŚREDNIA w programie Excel
Funkcja pośrednia w programie Excel to wbudowana funkcja, która służy do odwoływania się i pobierania wartości komórek z ciągu tekstowego, ta formuła pobiera odwołanie z komórki, do której się odwołuje, ma dwa argumenty, pierwszy argument nie jest opcjonalny, a drugi argument jest opcjonalny, do którego odwołuje się typ dopasowania, ta funkcja może być również używana z inną formułą.
Funkcja POŚREDNIA w programie Excel ma wiele zastosowań. Zwraca odwołanie do komórki określone przez ciąg tekstowy. Na przykład, jeśli komórka B1 zawiera ciąg, powiedzmy, że zakres c4 i c4 ma wartość „Jessica”. Teraz, jeśli użyjemy funkcji INDIRECT i przekażemy tekst odniesienia jako B1, zwróci wartość zawartą w zakresie komórek c4, czyli „Jessica”.
Funkcja INDIRECT programu Excel umożliwia określenie adresu komórki POŚREDNIO. Jak widać w powyższym POŚREDNIM przykładzie programu Excel, jeśli komórka B1 zawiera tekst C4, ta pośrednia formuła programu Excel zwraca zawartość komórki C4 ( Jessica ). Ta funkcja jest niezwykle użyteczną funkcją. Funkcji POŚREDNI możesz użyć, gdy chcesz zmienić odwołanie do komórki w pośredniej formule programu Excel bez zmiany samej formuły.
POŚREDNI Formuła Excela
Wyjaśnienie
Ref_text to odwołanie do komórki, która zawiera odwołanie w stylu A1, odwołanie w stylu W1K1, nazwę zdefiniowaną jako odwołanie lub odwołanie do komórki jako ciąg tekstowy.
argument a1 jest opcjonalny
| a1 | Fałsz lub 0, gdy ref_text jest w stylu W1K1 |
| a1 | Pominięte lub Prawda (1), gdy ref_text jest w stylu A1 |
Jak korzystać z funkcji POŚREDNI w programie Excel?
Weźmy przykład POŚREDNIEGO Excela przed użyciem funkcji adresu w skoroszycie programu Excel:
Załóżmy, że w przypadku witryny internetowej www.xyz.com mamy dane analityczne Google dotyczące odwiedzających witrynę z różnych kanałów odbiorców w pięciu różnych krajach, jak pokazano w poniższej tabeli:
Możemy użyć funkcji POŚREDNI w programie Excel w celu konwersji ciągu tekstowego na odwołanie, czyli odwołując się do komórki H4 i używając tej nazwy jako odwołania. Użycie funkcji POŚR. W programie Excel w tym przypadku polega na odwołaniu się do zakresów nazw przy użyciu wartości komórki, więc w komórce I4 moglibyśmy po prostu użyć funkcji sumy, aby obliczyć całkowitą liczbę odwiedzających z kraju Kanada.
Teraz możemy bezpośrednio obliczyć sumę odwiedzających z każdego kraju za pomocą funkcji sumy, ale używając funkcji POŚREDNI w programie Excel, możemy obliczyć sumę odwiedzających z różnych krajów, zmieniając odniesienie do nazwy, zamiast zmieniać samą formułę pośredniego programu Excel.
Stworzyliśmy zakres nazw dla każdego odwiedzającego kraj; można to łatwo zrobić, wpisując zakres nazw w miejscu tuż pod schowkiem, jak pokazano poniżej.
Inną metodą jest przejście do formuł -> menedżer nazw -> wybierz zakres, który chcesz nazwać -> kliknij menedżer nazw -> wpisz zakres nazw.
W podobny sposób stworzył nazwany zakres w programie Excel także dla gości z innych krajów.
Teraz, w I4, obliczając sumę odwiedzającego z kraju Kanada za pomocą funkcji POŚREDNI i używając zakresu nazw w H4 (Kanada, nazwa powyżej dla F3: F7), otrzymujemy całkowitą sumę odwiedzającego.
Jeśli utworzymy listę danych przy użyciu walidacji danych dla krajów i zastosujemy tę listę do H4
Zmieniając różne nazwy krajów z Kanady po Indie, USA, Australię lub Singapur, otrzymujemy całkowitą sumę odwiedzającego w I4 za pomocą funkcji POŚREDNI w programie Excel.
Tak więc w każdym przypadku nie zmieniamy pośredniej formuły programu Excel, która znajduje się w komórce I4; zmieniamy odniesienie do nazwy w H4 i obliczamy sumę odwiedzających dla każdego kraju.
Możemy pójść o krok dalej i odwoływać się do nazw na poziomie arkusza, używając funkcji POŚREDNIE w programie Excel. Na przykład będziemy teraz używać funkcji INDIRECT w odniesieniu do różnych arkuszy w skoroszycie.
Stworzyliśmy różne arkusze z nazwami krajów dla całkowitej liczby sprzedaży dokonanych dla każdego kanału wyszukiwania w różnych krajach.
W przypadku Indii
W przypadku USA
Podobnie w innych krajach.
Ponownie będziemy obliczać całkowitą sprzedaż dokonaną przez każdy kanał za pomocą funkcji POŚREDNI w programie Excel dla różnych arkuszy z nazwami krajów jako odniesieniem.
W tym przypadku, ponieważ mamy dane sprzedaży w różnych arkuszach, będziemy musieli połączyć różne elementy, które są zakresem danych, więc ref_text rozpocznie się od nazwy arkusza, czyli w tym przypadku Indie w B4, wtedy będziemy użyj operatora & o nazwie ampersand, który jest używany do konkatenacji.
Tak więc otrzymujemy całkowitą sumę sprzedaży w Indiach; teraz, jeśli zmienimy nazwę kraju arkusza w B4 z Indii na inne kraje, takie jak USA, Singapur lub Kanada, otrzymamy całkowitą wartość sprzedaży tych krajów.
Ponownie w tym przypadku po prostu zmieniliśmy wartości ref_text w komórce B4 bez zmiany głównej formuły pośredniego programu Excel, która znajduje się w C4, aby uzyskać sumę sprzedaży dla różnych krajów znajdujących się w różnych arkuszach za pomocą funkcji POŚREDNI.
Teraz użyjemy funkcji POŚREDNI, używając odwołania R1C1, aby uzyskać wartość sprzedaży z ostatniego miesiąca. Mamy sprzedaż dla różnych miesięcy w kolumnach B, C i D przez trzy miesiące, styczeń, luty i marzec, które mogą wzrosnąć do kolejnych miesięcy, takich jak kwiecień, maj, czerwiec i tak dalej. Tak więc tabela sprzedaży zawsze będzie rosła w zależności od dodania nadchodzącej sprzedaży miesięcznej. Tak więc w tym POŚREDNIM przykładzie Excela chcemy obliczyć sprzedaż z ostatniego miesiąca.
Tak więc ostatni miesiąc to marzec i chcemy obliczyć całkowitą sprzedaż w marcu, która wynosi 249 344 USD, używając funkcji POŚR.
W tym przykładzie POŚREDNIE w Excelu znajdziemy użycie drugiego argumentu funkcji POŚREDNI
INDIRECT (ref_text, a1)
a1: który jest argumentem opcjonalnym, jest wartością logiczną (wartością logiczną), która określa, jaki typ odwołania (ref_text) znajduje się w komórce
jeśli a1 jest prawdziwe (1) lub pominięte, ref_text jest traktowane jako odniesienie w stylu A1
jeśli a1 ma wartość false (0), ref_text jest interpretowane jako styl W1K1
Czym styl A1 różni się od stylu R1C1?
Zwykle funkcja POŚREDNI w programie Excel używa notacji A1. Adres każdej komórki składa się z litery kolumny i numeru wiersza. Jednak funkcja POŚREDNI w programie Excel obsługuje również notację W1K1. W tym systemie komórka A1 jest określana jako komórka R1C1, komórka A2 jako R2C1 i tak dalej.
Aby zmienić notację W1K1, wybierz opcję Plik->, aby otworzyć okno dialogowe opcji programu Excel, kliknij kartę formuły i zaznacz opcję stylu odniesienia W1K1. Teraz zauważysz, że wszystkie litery kolumn zmieniają się na cyfry. Dostosują się również wszystkie odwołania do komórek w formułach.
Tak więc, kiedy używamy stylu R1C1 w funkcji INDIRECT w programie Excel, przekazujemy wartość a1 jako fałsz, a jeśli używamy stylu A1, przekazujemy wartość true do a1.
Teraz, w tym POŚREDNIM przykładzie Excela, będziemy używać stylu W1K1 ze względu na to, że kolumny z miesiącem będą rosły.
Używając stylu W1K1, łączna wartość sprzedaży znajduje się w wierszu 10, a ostatnia wartość kolumny, która znajduje się w kolumnie D, zostanie obliczona przy użyciu funkcji ILOŚĆ , która da numer ostatniej kolumny zawierającej wartość. Tak więc count (10:10) w tym przypadku da 4, co oznacza numer kolumny w stylu W1K1. Tak więc skontaktowaliśmy się z tą wartością, aby uzyskać R10C4, który zawiera żądaną wartość, czyli całkowitą wartość sprzedaży z ostatniego miesiąca (249 344 USD).
A ponieważ używamy tutaj stylu R1C1, przekażemy fałszywą wartość dla argumentu a1.
Teraz dodajmy jeszcze jedną kolumnę z kwietniową wyprzedażą.
Zatem uzyskaj nowe wartości sprzedaży z ostatniego miesiąca odpowiadające wartościom z ostatniego miesiąca kwietnia. Podobnie, jeśli dodamy więcej miesięcy, zawsze otrzymamy nową wartość sprzedaży z ostatniego miesiąca bez zmiany formuły pośredniego Excela dla każdego miesiąca. To jest specjalność funkcji POŚREDNIE w programie Excel.
Jak dostosować tablicę tabel w funkcji WYSZUKAJ.PIONOWO w programie Excel?
Mamy pięć krajów i kanał wyszukiwania oraz sprzedaż, którą strona internetowa dokonała za pośrednictwem tych różnych kanałów z różnych krajów. Teraz w komórce B3 chcemy uzyskać sprzedaż, która została przeprowadzona poprzez wyszukiwanie organiczne w kraju Stany Zjednoczone.
Dla każdej tabeli dla różnych krajów nazwaliśmy każdą tabelę skróconą nazwą kraju, Ind dla Indii, Stanów dla USA, Can dla Kanady i tak dalej .
Jeśli korzystamy bezpośrednio z funkcji PRZEGLĄDANIA POŚREDNIEGO programu Excel, aby uzyskać wartość, jak pokazano poniżej
Otrzymujemy błąd, ponieważ funkcja PRZEGLĄDANIA POŚREDNIEGO programu Excel nie jest w stanie rozpoznać tablicy tabeli przekazanej jako wartość komórki B2, więc użyjemy funkcji POŚREDNIE w programie Excel, aby funkcja vlookup otrzymała tablicę table_array rozpoznaną jako prawidłowy argument.
Jeśli zmienimy wartości B1 i B2, otrzymamy różne wartości Sale w B3, zgodnie z wymaganiami.
Uwaga: w zakresach nazw nie jest rozróżniana wielkość liter. Stąd - Ind, IND i InD są takie same dla Excela.
W następnym przykładzie POŚREDNIE w programie Excel będziemy używać funkcji POŚREDNIE w programie Excel do tworzenia listy zależnej. Mamy listę stanowisk pracownika w firmie; następnie dla każdego stanowiska mamy nazwiska pracowników.
Teraz w komórce I2 utworzymy listę dla różnych rang pracowników jako ich stanowiska, aw I3 pokażemy listę, która będzie zależna od wartości w I2. Jeśli I2 jest reżyserem, to I3 powinno odzwierciedlać nazwiska reżyserów, których Andrew i Micheal; jeśli I2 jest menedżerem, to I3 powinno odzwierciedlać imiona i nazwiska menedżera, czyli Camille, David, Davis i William iw ten sam sposób dla przełożonego.
W tym przypadku ponownie użyjemy funkcji INDIRECT w programie Excel do utworzenia listy zależnej. Walidacja danych, w ustawieniach wybierających kryteria walidacji w postaci listy oraz w polu źródłowym, będziemy używać funkcji POŚREDNIE w programie Excel w następujący sposób.
= POŚREDNI ($ I $ 2)
Kiedy wybieramy nazwiska reżyserów w I3, odzwierciedla to jako lista z nazwiskami Andrew i Micheal, kiedy I2 jest menedżerem, wtedy I3 odzwierciedla nazwiska menedżera Camille, Davida, Davisa i Williama i podobnie dla przełożonego, jak pokazano poniżej na rysunku.
Rzeczy do zapamiętania
- Argument ref_text musi być prawidłowym odwołaniem do komórki, w przeciwnym razie POŚREDNI zwróci #REF! Błąd. Argument ref_text może odnosić się do innego skoroszytu.
- Jeśli ref_text odwołuje się do innego skoroszytu (odwołania zewnętrznego), drugi skoroszyt musi być otwarty. Jeśli skoroszyt źródłowy nie jest otwarty, INDIRECT zwraca #REF! Wartość błędu.
- Jeśli ref_text odnosi się do zakresu komórek poza limitem wierszy wynoszącym 1 048 576 lub limitem kolumn wynoszącym 16 384 (XFD), funkcja INDIRECT zwraca #REF! Błąd.
