Spis treści
Na pierwszy rzut oka (szczególnie podczas czytania pomocy) funkcja POŚREDNI (POŚREDNI) wygląda prosto, a nawet niepotrzebnie. Jego istotą jest przekształcenie tekstu, który wygląda jak link, w pełnoprawny link. Tych. jeśli musimy odwołać się do komórki A1, to możemy zwyczajowo utworzyć bezpośredni link (wprowadzić znak równości w D1, kliknąć A1 i nacisnąć Enter) lub użyć POŚREDNI w tym samym celu:
Zwróć uwagę, że argument funkcji – odwołanie do A1 – jest wpisany w cudzysłów, więc w rzeczywistości jest to tekst.
„No dobrze”, mówisz. „A jaka jest korzyść?”
Ale nie oceniaj po pierwszym wrażeniu – to zwodnicze. Ta funkcja może Ci pomóc w wielu sytuacjach.
Przykład 1. Transpozycja
Klasyka gatunku: trzeba obrócić pionową średnicę
rowek do poziomu (transpozycja). Oczywiście możesz użyć specjalnej wkładki lub funkcji TRANSP (TRANSPONOWAĆ) w formule tablicowej, ale możesz sobie poradzić z naszym POŚREDNI:
Logika jest prosta: aby uzyskać adres następnej komórki, sklejamy literę „A” ze znakiem specjalnym „&” oraz numerem kolumny bieżącej komórki, którą daje nam funkcja KOLUMNA (KOLUMNA).
Odwrotną procedurę lepiej wykonać nieco inaczej. Ponieważ tym razem musimy utworzyć łącze do komórek B2, C2, D2 itp., wygodniej jest użyć trybu łącza R1C1 zamiast klasycznej „bitwy morskiej”. W tym trybie nasze komórki będą się różnić tylko numerem kolumny: B2=R1C2, C2=R1C3, D2=R1C4 itd.
Tutaj pojawia się drugi opcjonalny argument funkcji. POŚREDNI. Jeśli jest równy KŁAMLIWY (FAŁSZYWE), możesz ustawić adres łącza w trybie R1C1. Możemy więc łatwo przetransponować zakres poziomy z powrotem na pionowy:
Przykład 2. Suma według przedziału
Przeanalizowaliśmy już jeden sposób sumowania po oknie (zakresie) o danym rozmiarze na arkuszu za pomocą funkcji SPRZEDAŻ (ZRÓWNOWAŻYĆ). Podobny problem można również rozwiązać za pomocą POŚREDNI. Jeśli potrzebujemy podsumować dane tylko z pewnego okresu zasięgu, to możemy je skleić z kawałków, a następnie przekształcić w pełnoprawny link, który możemy wstawić wewnątrz funkcji SUMA (SUMA):
Przykład 3. Lista rozwijana inteligentnych tabel
Czasami program Microsoft Excel nie traktuje inteligentnych nazw tabel i kolumn jako pełnych łączy. Na przykład przy próbie stworzenia listy rozwijanej (zakładka Dane – Walidacja danych) na podstawie kolumny Zatrudnienie z inteligentnego stołu Ludzie otrzymamy błąd:
Jeśli „opakowujemy” link naszą funkcją POŚREDNI, to Excel bez problemu to zaakceptuje, a nasza lista rozwijana będzie dynamicznie aktualizowana przy dodawaniu nowych pracowników na końcu inteligentnej tabeli:
Przykład 4. Niezniszczalne linki
Jak wiesz, program Excel automatycznie koryguje adresy referencyjne w formułach podczas wstawiania lub usuwania kolumn wierszy w arkuszu. W większości przypadków jest to poprawne i wygodne, ale nie zawsze. Załóżmy, że musimy przenieść nazwiska z kartoteki pracowników do raportu:
Jeśli umieścisz zwykłe linki (wpisz =B2 w pierwszej zielonej komórce i skopiuj ją w dół), to po usunięciu np. Daszy dostaniemy #LINK! błąd w zielonej komórce odpowiadającej jej. (#REF!). W przypadku korzystania z funkcji tworzenia linków POŚREDNI nie będzie takiego problemu.
Przykład 5: Zbieranie danych z wielu arkuszy
Załóżmy, że mamy 5 arkuszy z raportami tego samego typu od różnych pracowników (Michaił, Elena, Iwan, Siergiej, Dmitrij):
Załóżmy, że kształt, wielkość, położenie i kolejność towarów oraz miesięcy we wszystkich tabelach są takie same – różnią się tylko liczby.
Możesz zebrać dane ze wszystkich arkuszy (nie sumuj ich, ale umieść je pod sobą w „stosie”) za pomocą tylko jednej formuły:
Jak widać, idea jest taka sama: przyklejamy link do żądanej komórki danego arkusza i POŚREDNI zamienia go w „na żywo”. Dla wygody nad tabelą dodałem litery kolumn (B,C,D), a po prawej numery wierszy, które należy pobrać z każdego arkusza.
Pułapki
Jeśli używasz POŚREDNI (POŚREDNI) musisz pamiętać o jego słabościach:
- Jeśli łączysz się z innym plikiem (poprzez wklejenie nazwy pliku w nawiasy kwadratowe, nazwę arkusza i adres komórki), to działa tylko wtedy, gdy oryginalny plik jest otwarty. Jeśli go zamkniemy, otrzymamy błąd #LINK!
- ADR.POŚR nie może odwoływać się do dynamicznego nazwanego zakresu. Na statyczne – nie ma problemu.
- ADR.POŚR jest funkcją zmienną lub „niestabilną”, tzn. jest obliczana ponownie dla każdej zmiany w dowolnej komórce arkusza, a nie tylko wpływającej na komórki, jak w przypadku normalnych funkcji. Ma to zły wpływ na wydajność i lepiej nie dać się ponieść dużym tabelom ADR.
- Jak stworzyć dynamiczny zakres z automatycznym dopasowywaniem rozmiaru
- Sumowanie w oknie zakresu na arkuszu z funkcją OFFSET