Die heutige Frage von Quentin, der an meinem Atlanta Power Excel-Seminar teilgenommen hat. Quentin muss für jeden der über 1000 Kunden in Excel die gleichen 7 Umfragefragen generieren.
Wie Sie in dieser Abbildung sehen können, befinden sich die Kunden in A. Die zu wiederholenden Fragen befinden sich in Spalte D.
Sie könnten dies mit VBA oder Formeln lösen, aber dies ist Power Query-Woche um, also werde ich einen coolen Trick in Power Query verwenden.
Wenn Sie zwischen jeder Umfrage eine leere Zeile wünschen, fügen Sie eine Sequenznummer und nach der letzten Frage die Nummer 7 hinzu.
Drücken Sie in beiden Datensätzen Strg + T. Benennen Sie den zweiten Datensatz mit einem Namen, an den Sie sich erinnern können, z. B. Fragen oder Umfrage.
Verwenden Sie im zweiten Datensatz Daten aus Tabelle.
Der Power Query-Editor wird geöffnet. Wählen Sie auf der Registerkarte Start das Dropdown-Menü Schließen & Laden und dann Schließen & Laden in…. Wählen Sie im nächsten Dialogfeld Nur Verbindung erstellen.
Sie sind jetzt wieder in Excel. Wählen Sie eine beliebige Zelle in der Kundentabelle in Spalte A. Daten, Aus Tabelle. Klicken Sie nach dem Öffnen des Abfrage-Editors in der Multifunktionsleiste auf die Registerkarte Spalte hinzufügen und wählen Sie dann Benutzerdefinierte Spalte. Die Formel lautet =#"Questions"(einschließlich # und Anführungszeichen).
Im Editor wird eine neue Spalte mit dem Wert Tabelle angezeigt, der in jeder Zeile wiederholt wird. Klicken Sie in der Spaltenüberschrift auf das Symbol Erweitern.
Wählen Sie beide Felder in der Tabelle aus. Wählen Sie auf der Registerkarte Start die Option Schließen und Laden.
Ein neues Arbeitsblatt mit den 7 Fragen wird für jeden der über 1000 Kunden wiederholt.
Schau Video
Video-Transkript
Lernen Sie Excel aus Podcast Episode 2205: Explosion der Kreditumfrage.
Hey, willkommen zurück bei Netcast, ich bin Bill Jelen. Jetzt, erst gestern in Folge 2204, war es Kaylee aus Nashville, die eine VLOOKUP-Explosion durchführen musste - für jeden Gegenstand hier in Spalte D hatten wir eine passende Reihe von Gegenständen in Spalte G und mussten diese explodieren lassen. Wenn also Palast C 8 Gegenstände hätte, würden wir 8 Reihen bekommen.
Heute haben wir Quentin. Jetzt war Quentin in meinem Seminar in Atlanta, aber er kommt tatsächlich aus Florida, und Quentin hat hier in Spalte A fast 1000 Kunden - also mehr als 1000 Kunden - und für jeden Kunden muss er diese Umfrage erstellen. - diese Umfrage von 1, 2, 3, 4, 5, 6 Fragen. Und was ich hier tun werde, ist, dass ich eine Sequenznummer nur mit den Nummern 1 bis 7 hinzufüge, damit ich auf diese Weise eine schöne leere Zeile dazwischen erstellen kann. Ich werde diese beiden Datensätze in eine Tabelle umwandeln. Wir versuchen also, diese 7 Zeilen für jeden dieser 1000 Kunden zu explodieren. Das ist das Ziel.
Jetzt kann ich das mit VPA machen; Ich kann das mit Formeln machen; Aber es ist eine Art "Power Query Week" hier. Wir sind gerade dabei, dies ist unser drittes Power Query-Beispiel in Folge, also werde ich Power Query verwenden. Ich werde diesen linken in einen Tisch verwandeln. Ich werde sehr vorsichtig sein, dies nicht Tabelle 1 zu nennen. Ich werde ihm einen Namen geben. Wir müssen diesen Namen später wiederverwenden, also werde ich ihn Fragen nennen - so. Und dann ist dies Tabelle 2, aber ich werde das in Kunden umbenennen - nicht so wichtig, dass ich dieses umbenenne, weil es das zweite ist, das den Namen haben muss. Also werden wir das wählen; Daten; und wir werden From Table / Range sagen. Daten abrufen und transformieren - Dies wird als Power Query bezeichnet. Es ist in Excel 2016 integriert. Wenn Sie 2010 oder 2013 unter Windows haben,Kein Mac, kein iOS, kein Android. Sie können Power Query kostenlos von Microsoft herunterladen.
Wir werden also Daten aus Tabelle / Bereich abrufen. Hier ist unser Tisch - wir werden nichts dagegen tun, nur schließen und laden; Schließen & Laden in; nur eine Verbindung erstellen; Okay, und sehen Sie, der Name dieser Abfrage ist Fragen. Es wird der gleiche Name wie hier verwendet. Und dann kommen wir zurück zu diesem und Data; Aus Tabelle / Bereich; Es gibt also eine Liste unserer 1000 oder mehr Kunden.
Hey, hier ist ein Gruß an Miguel Escobar, meinen Freund, der Mitautor von M Is For (DATA) MONKEY) ist. Ich werde einen Link dazu in das Video einfügen - ein großartiges Buch über Power Query - das mir dabei geholfen hat. Wir werden eine brandneue benutzerdefinierte Spalte einfügen, und die Formel für benutzerdefinierte Spalten lautet hier: = # "Der Name der Abfrage". Ohne Miguel hätte ich das nie herausgefunden, also danke an Miguel dafür.
Und wenn ich auf OK klicke, sieht es nicht so aus, als hätte es funktioniert - wir bekommen nur Tisch, Tisch, Tisch, aber genau das hatten wir gestern mit Kaylee und dem Ticketing. Und alles, was ich tun muss, ist dies zu erweitern, und ich werde tatsächlich sagen, dass ich die Sequenz wahrscheinlich nicht brauche … nun, lassen Sie es uns für alle Fälle einfügen. Wir können es herausnehmen, nachdem wir es gesehen haben. Im Moment haben wir 1000 Zeilen und jetzt haben wir 7000 Zeilen - wunderschön. Ich kann jetzt sehen, dass es in Sequenz erscheint, also brauche ich das nicht. Ich werde mit der rechten Maustaste klicken und nur diese eine Spalte entfernen. Und dann kann ich nach Hause; Schließen & Laden; und BAM! - Wir sollten jetzt mehr als 7000 Zeilen mit 6 Fragen und einem Leerzeichen für jeden Kunden haben. Quentin war davon im Seminar begeistert. Cool, cooler Trick - vermeidet VBA, vermeidet eine ganze Reihe von Formeln mit Index,und solche Dinge - ein guter Weg.
Aber hey, heute lass mich dich mit M Is For (DATA) MONKEY losschicken. Ken Puls und Miguel Escobar haben das größte Buch über Power Query geschrieben. Ich liebe dieses Buch; In 2 Stunden wirst du ein Profi mit diesem Buch.
Also gut, heute abschließen - Quentin muss eine identische Umfrage für 1000 verschiedene Kunden erstellen. Es gibt 6 oder 7 oder 8 Fragen für jeden Kunden. Jetzt könnten wir dies mit VBA oder Makro tun, aber da wir hier eine Power Query ausführen, lassen Sie uns eine Power Query durchführen. Ich habe den Fragen eine zusätzliche leere Frage hinzugefügt. Ich habe eine Sequenznummer hinzugefügt, um sicherzustellen, dass das Leerzeichen dort bleibt. die Kunden zu einem Tisch machen; mache die Fragen zu einem Tisch; Es ist wirklich wichtig, dass Sie Fragen etwas nennen, an das Sie sich erinnern können - ich habe meine "Fragen" genannt. Fügen Sie die Fragen zu Power Query hinzu, nur als Verbindung. Erstellen Sie dann beim Hinzufügen der Kunden zu Power Query eine neue benutzerdefinierte Spalte mit der Formel: # "Der Name der ersten Abfrage" und erweitern Sie diese Spalte im Power Query-Editor. Schließen &Laden Sie zurück in die Tabelle, und Sie sind fertig. Ein erstaunlicher Trick - ich liebe Power Query - das Beste, was Excel seit 20 Jahren passieren kann.
Ich möchte Quentin für die Teilnahme an meinem Seminar danken. Er war schon ein paar Mal in meinem Seminar - großartiger Kerl. Ich möchte mich bei Ihnen für Ihren Besuch bedanken. 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: Loan-Survey-Explosion.xlsx
Power Query überrascht mich immer wieder. Lesen Sie das Buch M is for Data Monkey, um mehr über Power Query zu erfahren.
Excel-Gedanke des Tages
Ich habe meine Excel Master-Freunde um Rat zu Excel gebeten. Der heutige Gedanke zum Nachdenken:
"Mit AGGREGATE kann man alles machen, außer es zu verstehen."
Liam Bastick
