Kaley aus Nashville arbeitet an einer Ticketing-Tabelle. Für jede Veranstaltung wählt sie einen Ticketplan. In diesem Ticketplan können 4 bis 16 Ticketarten für die Veranstaltung angegeben werden. Kaley möchte eine Formel, die in die Nachschlagetabelle wechselt und * alle * Übereinstimmungen zurückgibt und gegebenenfalls neue Zeilen einfügt.
Ich habe zwar kein VLOOKUP, das dies lösen kann, aber die neuen in Excel 2016 integrierten Power Query-Tools können es lösen.
Hinweis
Wenn Sie über die Windows-Version von Excel 2010 oder Excel 2013 verfügen, können Sie Power Query kostenlos von Microsoft herunterladen. Leider ist Power Query für Excel für Android, Excel für iOS oder Excel für Mac noch nicht verfügbar.
Um das Ziel zu veranschaulichen: Mike McCann und die Mechaniker erscheinen im Allen Theatre mit Ticketplan C. Da die Nachschlagetabelle vier übereinstimmende Zeilen enthält, möchte Kaley vier Zeilen mit Mike McCann und den Mechanikern, von denen jede eine andere Übereinstimmung aufweist die Nachschlagetabelle.
Wählen Sie eine Zelle in der Originaltabelle aus. Drücken Sie Strg + T, um diese Daten als Tabelle zu markieren. Benennen Sie die Tabelle auf der Registerkarte Tabellentools von Tabelle1 in Anzeigen um. Wiederholen Sie diesen Vorgang für die Nachschlagetabelle und nennen Sie sie Tickets.
Wählen Sie eine Zelle in der Tabelle Shows aus. Wählen Sie auf der Registerkarte Daten die Option Aus Tabelle / Bereich.
Öffnen Sie nach dem Öffnen des Power Query-Editors das Dropdown-Menü Schließen & Laden und wählen Sie Schließen und Laden in….
Wählen Sie im Dialogfeld Daten importieren die Option Nur Verbindung erstellen.
Gehen Sie zum Tickets-Tisch. Wiederholen Sie die Schritte, um nur eine Verbindung zu Tickets herzustellen. Sie sollten beide Verbindungen im Bereich Abfragen sehen:
Wählen Sie eine leere Zelle aus. Wählen Sie Daten, Daten abrufen, Abfragen kombinieren, Zusammenführen.
Das Dialogfeld "Zusammenführen" besteht aus sechs Schritten. Der 3. und 4. scheinen mir nicht intuitiv zu sein.
- Wählen Sie Shows aus der oberen Dropdown-Liste
- Wählen Sie Tickets aus der zweiten Dropdown-Liste.
- Klicken Sie oben auf die Überschrift Ticketplan, um diese Spalte als Fremdschlüssel in der Tabelle Shows auszuwählen.
- Klicken Sie unten auf die Überschrift Ticketplan, um diese Spalte als Schlüsselfeld in der Nachschlagetabelle auszuwählen.
- Öffnen Sie den Join-Typ und wählen Sie Inner (nur übereinstimmende Zeilen).
- OK klicken
Die Ergebnisse sind zunächst enttäuschend. Sie sehen alle Felder aus Tabelle 1 und eine Spalte mit der Aufschrift Tabelle, Tabelle, Tabelle.
Klicken Sie oben in der Spalte Tickets auf das Symbol Erweitern.
Deaktivieren Sie den Ticketplan, da Sie dieses Feld bereits haben. Das verbleibende Feld heißt Tickets.Ticket-Typ, sofern Sie nicht die Option Ursprünglichen Namen als Präfix verwenden deaktivieren.
Erfolg! Jede Zeile für jede Show explodiert in mehrere Zeilen.
Ich bin mit der Sortierung der Daten nicht besonders zufrieden. Durch das Sortieren nach Datum werden die Ticket-Typen auf seltsame Weise sortiert.
Schau Video
Im heutigen Fall wurde das Video aufgenommen, nachdem der Artikel geschrieben wurde. Ich schlage vor, den Ticket-Typen eine Sequenzspalte hinzuzufügen, um die Sortierreihenfolge zu steuern.
Video-Transkript
Lernen Sie Excel aus Podcast, Episode 2204: Alle VLOOKUPs zurückgeben.
Hey, willkommen zurück im Netcast, ich bin Bill Jelen. Die heutige Frage aus Nashville Music City. Ich war dort unten in Nashville, jemand ist für das Planen des Ladens von Tickets in ein Ticketsystem verantwortlich. Wir haben also Folgendes: Wir haben eine Liste von Veranstaltungen - bevorstehende Veranstaltungen - wir haben das Datum, den Veranstaltungsort und einen Ticketplan. Also, obwohl etwas im Palast abgehalten wird, gibt es möglicherweise andere Ticketpläne - vielleicht ist der Boden mit Sitzplätzen konfiguriert, oder vielleicht ist es nur ein Stehplatz, oder?
Je nachdem, welche Art von Ticketplan Sie haben, müssen Sie hierher zur Nachschlagetabelle kommen und alle passenden Ereignisse finden. Im Wesentlichen werden wir das tun, was ich als VLOOKUP-Explosion bezeichne. Wenn also etwas bei Hannah C ist, werden sie zu Hannah C hinuntergehen und wenn es 1, 2, 3, 4, 5, 6 - 7 Gegenstände in Hannah C gibt, werden wir haben um sieben Zeilen zurückzugeben - was bedeutet, dass Sie sechs weitere Zeilen einfügen und diese Daten nach unten kopieren müssen. In Ordung.
Jetzt machen wir das überhaupt nicht mit einem VLOOKUP, aber Sie bekommen das Konzept - wir machen einen VLOOKUP und geben alle Antworten als neue Zeilen zurück. Also gut, ich werde diese beiden Tabellen nehmen und sie mit Strg + T zu einer echten Tabelle machen. Die ersten heißen Tabelle 1 - schrecklicher Name, nennen wir das Events oder Shows, nennen wir es Shows - und die zweite, hey, hier ist, was ich gelernt habe, weil ich das geübt habe - wir müssen haben ein Sequenzfeld hier. Also = REIHE (A1), doppelklicken Sie und kopieren Sie diese nach unten und kopieren Sie dann spezielle Werte und fügen Sie sie ein. In Ordung. Jetzt machen wir daraus eine Tabelle - Strg + T, und wir nennen das eine Tickets.
In Ordung. Wir haben also Shows, wir haben Tickets. Ich gehe zur Registerkarte Daten und bin hier in der Show-Sache. Ich möchte sagen, dass ich meine Daten aus einer Tabelle oder einem Bereich abrufen möchte - dies ist übrigens Power Query. Wenn Sie wieder in Excel 2010 oder 2013 sind, können Sie dies kostenlos von Microsoft herunterladen. Laden Sie das Power Query-Tool herunter. Wenn Sie einen Mac, iOS oder Android verwenden, gibt es leider keine Power Query für Sie. Okay, also aus einer Tabelle oder einem Bereich … finde jemanden, der einen … einen Freund hat, der einen … Windows-PC hat, und lass ihn dies einrichten. In Ordung. Hier ist eine Tabelle, wir werden nichts dagegen tun, nur schließen und laden, schließen und laden und dann "Nur Verbindung erstellen" sagen, perfekt. Wir werden hier zu unserer zweiten Tabelle kommen: Daten abrufen, aus einer Tabelle oder einem Bereich, wir tun nichts mit dieser, Close & Load,Schließen & Laden unter "Nur Verbindung erstellen", OK. Was wir jetzt haben, ist, dass wir eine Verbindung zur ersten Tabelle und eine Verbindung zur zweiten Tabelle haben. Wir werden diese beiden nicht zusammenführen, was im Wesentlichen wie VLOOKUP ist, oder ein Datenbank-Joint, denke ich, ist wirklich so, wie es ist. Kombinieren Sie Abfragen, wir werden zusammenführen. In Ordung.
Nun, sieben Dinge, die Sie in diesem Dialogfeld tun müssen - und es ist ein wenig verwirrend -, werden wir Shows als erste Tabelle auswählen. Wählen Sie Tickets als zweiten Tisch. Wählen Sie aus, welches Feld sie gemeinsam haben. Dies können mehrere Felder sein - Sie können bei gedrückter Ctrl-Taste klicken -, aber in diesem Fall gibt es nur einen Ticketplan. und dann Ticketplan; und dann werden wir den Join-Typ in einen inneren Join mit "nur den übereinstimmenden Zeilen" ändern. In Ordung. Jetzt klicken Sie auf OK und Sie denken, dass Ihr gesamtes Problem gelöst sein wird, aber Sie sind nur niedergeschlagen, weil hier alle Daten von A sind - sie haben überhaupt keine neuen Zeilen eingefügt - und hier drüben, nur ein langweiliges dummes Feld namens Tickets, das nur Tisch, Tisch, Tisch hat, hah.
Aber zum Glück befindet sich oben ein Erweiterungssymbol, und wir werden das erweitern - ich brauche keinen Plan, das habe ich bereits - Ticket-Typ und Sequenz. Ich möchte nicht, dass es Tickets.TicketType heißt, was Power Query tun möchte - also deaktiviere ich dieses Kontrollkästchen. In Ordung. Im Moment haben wir 17 Datenzeilen; Wenn ich auf OK klicke, BAM! Da ist die Explosion. Also, Michael Seeley und der Starlighter tauchen mit all den verschiedenen Ticketarten auf, so. Okay, und zu sehen, wie diese Ticketarten nacheinander angezeigt werden, ist großartig. Aber Michael Seeley ist nicht die nächste Show, die nächste Show ist am 5. Juni. Wenn ich also versuche, dies nach Datum zu sortieren - das macht mich wahnsinnig, kann ich das nicht erklären. Nach Datum sortieren, und Mike Man und die Mechaniker kommen auf 65, aber dann sind alle Tickets vermasselt. Sie'Ich bin in der falschen Sequenz, und deshalb musste ich diese Sequenz machen - fühlt sich so an. Ich kann nach Reihenfolge sortieren. Also jetzt, 6, 5, schön, und dann sind die Tickets korrekt. Und tatsächlich brauchen wir diese Spalte zu diesem Zeitpunkt nicht mehr. Ich kann also mit der rechten Maustaste klicken und entfernen und dann schließen und laden - dieses Mal werde ich tatsächlich schließen und laden, nicht schließen und laden - und wir haben unser Ergebnis. In Ordung.
Also sind wir von einer Liste von Ereignissen zu dieser ganzen großen Liste übergegangen, aber hier ist der großartige Teil: Ich habe das vermasselt, Mike Man und Mechanics sind nicht Palast B, sondern Palast C. Also komme ich zum Original oben rechts zurück -Hand Ecke für weitere Informationen über das Buch.
In Ordung. Themen in dieser Episode: Kaley in Nashville muss einen VLOOKUP durchführen, um alle Übereinstimmungen zurückzugeben, wobei normalerweise neue Zeilen eingefügt werden. Und es ist eine Ticketing-Datenbank, okay? Ich werde dies eine VLOOKUP-Explosion nennen, da jede Show in bis zu 16 Zeilen explodiert. Wir werden Power Query verwenden, um dies zu lösen, und ich habe erfahren, dass das Datum in der falschen Sequenz angezeigt wird, es sei denn, wir fügen dem Ticket-Typ ein Sequenzfeld hinzu. Machen Sie beide Sätze mit Strg + T zu einer Tabelle; benennen Sie sie als Shows und Tickets; und dann aus jeder Tabelle Daten abrufen, Aus Tabelle, Schließen & Laden, um nur eine Verbindung herzustellen; Wiederholen Sie dies für die andere Tabelle. dann Daten, Daten abrufen, Abfragen kombinieren, zusammenführen; und dann dieses Dialogfeld, es ist ziemlich verwirrend für mich - wählen Sie Ereignisse, wählen Sie Tickets, klicken Sie in beiden auf Ticket-Typ, ändern Sie die Verbindung in einen inneren Join,Klicken Sie auf OK, und Sie erhalten das schrecklich enttäuschende Ergebnis, in dem nur eine Spalte mit der Aufschrift Tabelle, Tabelle, Tabelle, Tabelle angezeigt wird. Klicken Sie oben auf das Symbol Erweitern. Wählen Sie das Feld Ticketsequenz. Stellen Sie nicht den Namen der Tabelle voran. und Sie können nach Datum sortieren, nach Reihenfolge sortieren; Schließen und Laden in die Tabelle. Das Schöne ist, wenn sich die zugrunde liegenden Daten ändern - aktualisieren Sie einfach und Sie haben Ihre Ergebnisse.
Um die Arbeitsmappe aus dem heutigen Video herunterzuladen, besuchen Sie die URL in der YouTube-Beschreibung. Auch eine Liste der kommenden Seminare - Ich würde mich freuen, Sie bei einem meiner Live-Power Excel-Seminare zu sehen.
Ich möchte Kaley dafür danken, dass sie in Nashville aufgetaucht ist und mir diese großartige Frage gestellt hat. Ich möchte, dass Sie vorbeischauen. Wir sehen uns beim nächsten Mal für einen weiteren Netcast von.
Laden Sie die Excel-Datei herunter
So laden Sie die Excel-Datei herunter: return-all-vlookups.xlsx
Power Query überrascht mich immer wieder. Dies ist der zweite Teil einer dreitägigen Serie, in der die Antwort Power Query lautet:
- Dienstag: Konvertieren Sie eine Spalte mit Datum / Uhrzeit in ein Datum
- Heute: Alle VLOOKUPs zurückgeben
- Donnerstag: Erstellen Sie eine Umfrage für jedes der 1100 Elemente
Ich habe eine komplette YouTube-Wiedergabeliste mit Dingen, die ich mit Power Query gelöst habe.
Excel-Gedanke des Tages
Ich habe meine Excel Master-Freunde um Rat zu Excel gebeten. Der heutige Gedanke zum Nachdenken:
"Wenn Sie Zweifel haben, verwenden Sie die RUNDE Funktion!"
Mike Girvin
