Spis treści
Jeśli przynajmniej znasz tę funkcję VPR (WYSZUKAJ.PIONOWO) (jeśli nie, to najpierw uruchom tutaj), powinieneś zrozumieć, że ta i inne podobne do niej funkcje (VIEW, INDEX i SEARCH, SELECT itp.) zawsze dają wynik wartość – numer, tekst lub datę, której szukamy w podanej tabeli.
Ale co, jeśli zamiast wartości chcemy uzyskać hiperłącze na żywo, klikając na które moglibyśmy natychmiast przeskoczyć do znalezionego dopasowania w innej tabeli, aby spojrzeć na to w ogólnym kontekście?
Załóżmy, że mamy dużą tabelę zamówień dla naszych klientów jako dane wejściowe. Dla wygody (choć nie jest to konieczne) przekonwertowałem tabelę na dynamiczny „inteligentny” skrót klawiaturowy Ctrl+T i dałem na kartę Konstruktor (Projekt) Jej imie tabZamówienia:
Na osobnym arkuszu Skonsolidowany Zbudowałem tabelę przestawną (choć nie musi to być dokładnie tabela przestawna – w zasadzie każda tabela się nadaje), w której według wstępnych danych wyliczana jest dynamika sprzedaży według miesięcy dla każdego klienta:
Dodajmy do tabeli zamówień kolumnę z formułą, która wyszukuje nazwę klienta dla bieżącego zamówienia w arkuszu Skonsolidowany. Do tego używamy klasycznego zestawu funkcji INDEKS (INDEKS) и WIĘCEJ EKSPOZYCYJNE (MECZ):
Teraz zawińmy naszą formułę w funkcję KOMÓRKA (KOMÓRKA), który poprosimy o wyświetlenie adresu znalezionej komórki:
I wreszcie wszystko, co wyszło, wstawiamy w funkcję HIPERŁĄCZE (HIPERŁĄCZE), który w programie Microsoft Excel może tworzyć na żywo hiperłącze do podanej ścieżki (adresu). Jedyne, co nie jest oczywiste, to to, że będziesz musiał przykleić znak hash (#) na początku do otrzymanego adresu, aby link był poprawnie postrzegany przez Excel jako wewnętrzny (od arkusza do arkusza):
Teraz, gdy klikniesz w któryś z linków, natychmiast przeskoczymy do komórki z nazwą firmy na arkuszu z tabelą przestawną.
Aby było naprawdę dobrze, poprawmy nieco naszą formułę, aby przejście nastąpiło nie na nazwisko klienta, ale na konkretną wartość liczbową dokładnie w kolumnie miesiąca, w której zostało zrealizowane odpowiednie zamówienie. Aby to zrobić, musimy pamiętać, że funkcja INDEKS (INDEKS) w Excelu jest bardzo wszechstronny i może być używany m.in. w formacie:
=INDEKS( XNUMXD_zakres; Numer kolejki; Numer_kolumny )
Czyli jako pierwszy argument możemy podać nie kolumnę z nazwami firm w przestawie, ale cały obszar danych tabeli przestawnej, a jako trzeci argument podać numer kolumny, której potrzebujemy. Można to łatwo obliczyć za pomocą funkcji MIESIĄC (MIESIĄC), który zwraca numer miesiąca dla daty transakcji:
Ulepszenie 2. Piękny symbol linku
Drugi argument funkcji HIPERŁĄCZE – tekst, który jest wyświetlany w komórce z linkiem – można upiększyć, jeśli zamiast banalnych znaków „>>” użyjesz niestandardowych znaków z czcionek Windings, Webdings itp. W tym celu możesz użyć funkcji SYMBOL (ZWĘGLAĆ), które mogą wyświetlać znaki według ich kodu.
Na przykład kod znaku 56 w czcionce Webdings da nam ładną podwójną strzałkę dla hiperłącza:
Ulepszenie 3. Podświetl bieżący wiersz i aktywną komórkę
Otóż dla ostatecznego zwycięstwa piękna nad zdrowym rozsądkiem możesz również dołączyć do naszego pliku uproszczoną wersję podświetlania aktualnej linii i komórki, do której prowadzimy link. Będzie to wymagało prostego makra, które zawiesimy, aby obsłużyć zdarzenie zmiany wyboru na arkuszu Skonsolidowany.
Aby to zrobić, kliknij prawym przyciskiem myszy kartę Podsumowanie i wybierz polecenie Zobacz i wysłuchaj kod (Pogląd kod). Wklej następujący kod do otwartego okna edytora Visual Basic:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.Interior.ColorIndex = -4142 Cells(ActiveCell.Row, 1)).Resize(1, 14).Interior.ColorIndex = 6 ActiveCell.Interior.ColorIndex = 44 End Sub
Jak łatwo zauważyć, tutaj najpierw usuwamy wypełnienie z całego arkusza, a następnie wypełniamy całą linię w podsumowaniu kolorem żółtym (kod koloru 6), a następnie pomarańczowym (kod 44) bieżącą komórką.
Teraz po wybraniu dowolnej komórki w komórce podsumowania (nie ma to znaczenia – ręcznie lub w wyniku kliknięcia naszego hiperłącza), cały wiersz i komórka z potrzebnym miesiącem zostaną podświetlone:
Uroda 🙂
PS Pamiętaj tylko, aby zapisać plik w formacie z obsługą makr (xlsm lub xlsb).
- Tworzenie linków zewnętrznych i wewnętrznych z funkcją HYPERLINK
- Tworzenie e-maili z funkcją HYPERLINK