Inhalte
Wenn Sie zumindest mit der Funktion vertraut sind VPR (SVERWEIS) (wenn nicht, dann zuerst hier ausführen), dann sollten Sie verstehen, dass diese und andere ähnliche Funktionen (VIEW, INDEX und SEARCH, SELECT usw.) immer als Ergebnis liefern Wert – die Nummer, der Text oder das Datum, nach denen wir in der angegebenen Tabelle suchen.
Was aber, wenn wir anstelle eines Wertes einen Live-Hyperlink erhalten möchten, durch Klicken auf den wir sofort zu der gefundenen Übereinstimmung in einer anderen Tabelle springen könnten, um sie in einem allgemeinen Kontext zu betrachten?
Nehmen wir an, wir haben eine große Auftragstabelle für unsere Kunden als Eingabe. Der Einfachheit halber (obwohl dies nicht erforderlich ist) habe ich die Tabelle in eine dynamische „intelligente“ Tastenkombination umgewandelt Ctrl+T und gab auf der Registerkarte Bauherr (Design) ihr Name tabBestellungen:
Auf einem separaten Blatt Konsolidiert Ich habe eine Pivot-Tabelle gebaut (obwohl es nicht unbedingt eine Pivot-Tabelle sein muss – im Prinzip ist jede Tabelle geeignet), in der nach den Ausgangsdaten die Verkaufsdynamik nach Monaten für jeden Kunden berechnet wird:
Fügen wir der Bestelltabelle eine Spalte mit einer Formel hinzu, die den Namen des Kunden für die aktuelle Bestellung auf dem Blatt nachschlägt Konsolidiert. Dazu nutzen wir den klassischen Funktionsbund INDEX (INDEX) и AUSGESETZTER (PASSEN):
Jetzt packen wir unsere Formel in eine Funktion CELL (ZELLE), die wir auffordern, die Adresse der gefundenen Zelle anzuzeigen:
Und schließlich setzen wir alles, was sich herausgestellt hat, in eine Funktion HYPERLINK (HYPERLINK), die in Microsoft Excel einen Live-Hyperlink zu einem bestimmten Pfad (Adresse) erstellen kann. Das einzige, was nicht offensichtlich ist, ist, dass Sie die Raute (#) am Anfang an die empfangene Adresse kleben müssen, damit der Link von Excel korrekt als intern (von Blatt zu Blatt) wahrgenommen wird:
Wenn Sie jetzt auf einen der Links klicken, springen wir sofort zu der Zelle mit dem Namen des Unternehmens auf dem Blatt mit der Pivot-Tabelle.
Um es wirklich gut zu machen, verbessern wir unsere Formel etwas, sodass der Übergang nicht auf den Namen des Kunden erfolgt, sondern auf einen bestimmten Zahlenwert genau in der Monatsspalte, wenn der entsprechende Auftrag abgeschlossen wurde. Dazu müssen wir uns daran erinnern, dass die Funktion INDEX (INDEX) in Excel ist sehr vielseitig und kann unter anderem im Format verwendet werden:
=INDEX( XNUMXD_Bereich; Zeilennummer; Spaltennummer )
Das heißt, wir können als erstes Argument nicht die Spalte mit den Firmennamen im Pivot angeben, sondern den gesamten Datenbereich der Pivot-Tabelle und als drittes Argument die Nummer der benötigten Spalte hinzufügen. Sie lässt sich leicht mit der Funktion berechnen MONAT (MONAT), die die Monatsnummer für das Abschlussdatum zurückgibt:
Verbesserung 2. Schönes Linksymbol
Zweites Funktionsargument HYPERLINK – der Text, der in einer Zelle mit einem Link angezeigt wird – kann schöner gemacht werden, wenn man statt der banalen Zeichen „>>“ Sonderzeichen aus Windings, Webdings-Fonts und dergleichen verwendet. Dazu können Sie die Funktion verwenden SYMBOL (VERKOHLEN), die Zeichen anhand ihres Codes anzeigen kann.
So ergibt beispielsweise der Zeichencode 56 in der Schriftart Webdings einen hübschen Doppelpfeil für einen Hyperlink:
Verbesserung 3. Aktuelle Zeile und aktive Zelle hervorheben
Nun, für den endgültigen Sieg der Schönheit über den gesunden Menschenverstand können Sie auch eine vereinfachte Version der Hervorhebung der aktuellen Zeile und der Zelle, zu der wir dem Link folgen, an unsere Datei anhängen. Dazu ist ein einfaches Makro erforderlich, das wir aufhängen, um das Auswahländerungsereignis auf dem Blatt zu verarbeiten Konsolidiert.
Klicken Sie dazu mit der rechten Maustaste auf den Tabellenreiter Zusammenfassung und wählen Sie den Befehl aus Anzeigen Code (Aussicht Code). Fügen Sie den folgenden Code in das sich öffnende Visual Basic-Editorfenster ein:
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
Wie Sie leicht sehen können, entfernen wir hier zuerst die Füllung aus dem gesamten Blatt und füllen dann die gesamte Zeile in der Zusammenfassung mit Gelb (Farbcode 6) und dann Orange (Code 44) mit der aktuellen Zelle.
Wenn nun eine beliebige Zelle innerhalb der Zusammenfassungszelle ausgewählt wird (egal ob manuell oder durch Klicken auf unseren Hyperlink), wird die gesamte Zeile und Zelle mit dem Monat, den wir benötigen, hervorgehoben:
Schönheit 🙂
PS Denken Sie nur daran, die Datei in einem makrofähigen Format (xlsm oder xlsb) zu speichern.
- Erstellung externer und interner Links mit der HYPERLINK-Funktion
- Erstellen von E-Mails mit der HYPERLINK-Funktion