Gdy już wiesz, jak używać funkcji pośredniej w programie Excel, możesz pobierać informacje z innych arkuszy, odwoływać się do nazwanych zakresów i łączyć je z innymi funkcjami, aby stworzyć naprawdę wszechstronne narzędzie. Pogodzenie się z tym może zająć trochę praktyki, ale dzięki funkcji pośredniej możesz zrobić więcej, niż mogłoby się wydawać.
Instrukcje zawarte w tym artykule dotyczą programów Excel dla Microsoft 365, Excel 2019 i Excel 2016.
Co to jest funkcja pośrednia?
Funkcja pośrednia to sposób na przekształcenie ciągu tekstowego w odwołanie. Oznacza to, że pobiera informacje z odwołania do innej komórki lub zakresu. Tworzy odwołanie z tekstu i nie zmienia się, gdy komórki, wiersze lub kolumny są zmieniane, dodawane lub usuwane z cytowanego zakresu. Tworzone referencje są oceniane w czasie rzeczywistym, więc referencje są zawsze dokładne w stosunku do danych, z których pochodzą.
Jeśli to trochę zagmatwane, nie martw się. Formuła pośrednia może być łatwiejsza do zrozumienia dzięki poprawnym przykładom i praktyce. Jeśli masz wątpliwości, wykonaj poniższe czynności, a wkrótce to zrozumiesz.
Korzystanie z funkcji pośredniej z nazwanymi zakresami
Nazwane zakresy w programie Excel to świetny sposób na zbieranie danych w ramach jednego odniesienia, a funkcja pośrednia ułatwia pobieranie tych informacji. Oto jak to zrobić:
-
Otwórz dokument Excel z już zastosowanymi nazwanymi zakresami. W naszym przykładzie mamy informacje o sprzedaży różnych produktów spożywczych i napojów, z pieniędzmi zarobionymi w ciągu każdego dnia tygodnia zebranymi w nazwanych zakresach zatytułowanych po produktach.
Image -
Wybierz komórkę dla nazwanego zakresu i wprowadź do niej jedną z nich. W naszym przykładzie użyliśmy Burgers. Dodaj inne tytuły oznaczeń i kolory, jeśli chcesz.
Image -
Wybierz inną komórkę, do której chcesz skierować wynik pośredni. Ponieważ chcemy zsumować całą wielkość sprzedaży z tygodnia dla określonej żywności, w tym przypadku hamburgerów, wpiszemy w komórce:
=SUM(ADR. POŚR(G5)
-
To oznacza funkcję SUMA, która użyje funkcji pośredniej do pobrania informacji z nazwanego zakresu w komórce G5, w tym przypadku Burgery. Produkcja to 3781, całkowita sprzedaż w tygodniu dla Burgerów.
W naszym przykładzie możemy zastąpić Burgery w komórce G5 lemoniadą lub deserami, pozostałymi dwoma nazwanymi zakresami, a wynik zmieni się na ich sumę SUMA.
Image
Korzystanie z funkcji pośredniej na wielu arkuszach
Formuła pośrednia jest jeszcze skuteczniejsza, gdy używasz jej do pobierania informacji z innych arkuszy. Nie musisz też używać nazwanych zakresów, aby to zrobić.
- Otwórz dokument Excel z wieloma arkuszami lub utwórz je ze wszystkimi niezbędnymi informacjami.
- W arkuszu, do którego ma trafić wynik pośredni, utwórz komórkę z nazwą arkusza, z którego chcesz pobrać informacje. W naszym przykładzie jest to FoodSales.
-
Ponieważ chcemy pobrać informacje z naszego arkusza FoodSales, aby zsumować liczbę sprzedanych Burgerów, wpisaliśmy następujące zakres komórek i nazwy arkuszy z własnymi nazwami):
=SUM(ADR. POŚR(B4&"!B4:B10"))
Image - To oznacza ją jako funkcję SUMA, ponieważ próbujemy znaleźć sumę. Następnie wyznacza komórkę B4 jako tekst odniesienia dla funkcji pośredniej. & łączy elementy tej funkcji, po których następuje cudzysłów i wykrzyknik, a następnie zakres komórek, z których chcemy wyciągnąć dane. B4 do B10
- Wynik to całkowita sprzedaż Burgerów w tym tygodniu. Kiedy tworzymy nowy arkusz FoodSales2 na nowy tydzień z różnymi numerami, musimy tylko dostosować komórkę B4, aby zawierała FoodSales2, aby uzyskać dane o sprzedaży Burger za ten tydzień.
Korzystanie z funkcji pośredniej z odniesieniem do stylu R1C1
W przypadku arkuszy, które stale się rozwijają, w których odwołanie, którego chcesz użyć, nie zawsze znajduje się w tej samej komórce, można użyć odwołań stylu W1K1 z formułą pośrednią, aby uzyskać potrzebne informacje. Będziemy nadal korzystać z naszych przykładów sprzedaży żywności, ale wyobraźmy sobie, że jest to arkusz na wyższym poziomie, który analizuje ogólne sumy sprzedaży tygodniowej.
- Otwórz dokument Excela ze wszystkimi danymi, z których chcesz czerpać dane, i wybierz komórkę do wyjścia funkcji pośredniej. W naszym przykładzie patrzymy na miesięczne sumy sprzedaży żywności i chcemy poznać najnowszą sumę sprzedaży w danym miesiącu.
-
W naszym przykładzie formuła wygląda tak:
=POŚREDNIA("R12C"&LICZBA(12:12), FAŁSZ)
Image - Funkcja pośrednia używa R12 (wiersz 12), po którym następuje C do oznaczenia kolumny w cudzysłowie. & łączy ze sobą dwie części funkcji. Używamy funkcji ILE. NIEPUSTYCH, aby zliczyć wszystkie niepuste komórki w wierszu 12 (wybierając wiersz lub wpisując 12:12), po których następuje przecinek. FALSE oznacza to jako odwołanie R1C1.
-
Wynikiem jest wtedy ostatni wpis w naszej tabeli, w tym przypadku 8102 lub 8, 102 USD. Kiedy w końcu dodamy dane sprzedaży z kwietnia, najnowszy numer sprzedaży zostanie automatycznie zaktualizowany w czasie rzeczywistym.
Image
