Excel-Tabellen meistern: So erstellst und verwendest du sie wie ein Profi

1. Einführung: Mein Name ist Al Chen, ich bin Solutions Architect bei Coda, ein Tool, das das Beste aus Tabellen, Dokumenten und Anwendungen zusammenbringt. Ich bin seit mehr als 10 Jahren Excel -Power-User unterrichtete mehr als 10.000 MBA-Studenten. Heutzutage verbringe ich meine Zeit damit, Tutorials und Videos zu Excel abellen und anderen verwandten Tools zu erstellen . Heute werde ich Ihnen beibringen, wie Excel -Tabellen eine relativ unbekannte Funktion in Excel zu verwenden . Sobald Sie die Leistungsfähigkeit von Excel -Tabellen erfahren haben, werden Sie nie zurückblicken. Ich werde Ihnen beibringen, wie Sie Excel -Tabellen erstellen, Strukturreferenzen und wie Sie Ihre Tabelle formatieren, damit sie für Ihr Team und Ihre Kollegen sehr schön und hübsch aussehen. Ich freue mich sehr, dass die Schüler diese Klasse besuchen, denn sobald Sie die grundlegenden Funktionen und Funktionen von Excel -Tabellen lernen, sehen Sie die Macht, die sie für den Rest Ihrer Dashboards, Ihre Datenanalyse, Aufgaben, und andere Dinge im Zusammenhang mit Excel-Tabellenkalkulationen. Nur wenn Sie diese eine einfache Funktion lernen, wird es hoffentlich die Art und Weise ändern, wie Sie Excel in der Zukunft angehen. Sobald du fertig bist, diese Klasse zu absolvieren, hoffe ich, du wirst an den Klassenprojekten teilnehmen. Es beinhaltet die Analyse von Flugverspätungsdaten aus dem Department of Transportation von 2015, Umwandlung in eine Excel -Tabelle, sowie Analyse dieser Daten durch Erstellen eines Dashboards, Erstellen eines Berichts für den Endbenutzer, und auch ein paar Einblicke darüber schreiben, was Sie bei der Analyse der Daten sammeln können , nachdem Sie Excel -Tabellen verwendet haben. Ich hoffe wirklich, dass Sie Ihre Ergebnisse zu den Daten innerhalb der Projektgalerie posten. Beginnen wir sofort mit der Suche nach den Vorteilen von Excel -Tabellen. 2. Die Vorteile von Excel-Tabellen: In dieser Lektion werde ich Ihnen die Vorteile von Excel -Tabellen zeigen, indem ich eine kurze Demo einer Tabelle mache, die ich in der Klassenarbeitsmappe erstellt habe, in der ich Ihnen zeigen werde, wie Sie filtern und sortieren, eine Übersichtszeile zu Ihren Daten hinzufügen und auch fügen Sie der Excel abelle neue Daten hinzu. Ich werde diese Themen später in der Klasse eingehender untersuchen. Wir schauen uns gerade eine Excel -Tabelle an. In Schritt 1 klicken wir einfach auf die Tabelle unten und scrollen nach unten über die erste Kopfzeile, die Zeile 11 ist. ich einen Bildlauf vorbeiführe, werden Sie feststellen, dass die Spalten oberen Symbolleiste oder in der Multifunktionsleiste klebrig werden. Dies ist eine der netten Funktionen von Excel -Tabellen, in denen Sie die Spalten hier oben sehen können, wenn Sie vorbei scrollen. Es ist, als ob Sie Ihre erste Zeile in einer Weise einfrieren , wenn Sie dies zuvor in Ihren Excel -Dateien getan haben. Ich kann meine Daten auch schnell filtern und sortieren, indem ich einfach auf das „Dropdown“ hier in einer dieser Spalten klicke, und ich kann auf „Aufsteigend“ oder „Absteigend“ klicken und Dinge filtern, wie Sie es zuvor in Ihren Excel -Dateien getan haben. Dies ist eine der Standardfunktionen, die in Ihrer Excel-Tabelle enthalten sind. Nehmen wir an, ich möchte eine zusammenfassende Zeile in Schritt 3 erstellen. Ich kann in meine Tabelle klicken, auf die Registerkarte „Tabelle“ in der Multifunktionsleiste klicken, und dann bemerken Sie, dass die gesamte Zeile Kontrollkästchen standardmäßig deaktivieren. Wenn ich darauf klicke, sehen Sie in Zeile 39, gibt es eine zusammenfassende Zeile, die Sie Ihre Daten im Grunde nach Durchschnittswerten, Summen, Zählungen, all diesen typischen Metriken zusammenfassen können. Ich werde das vorerst deaktivieren. Eine der mächtigsten Funktionen mit Tabellen ist hier in Schritt 4 aufgeführt. Ich möchte, dass Sie versuchen, der Tabelle eine neue Datenzeile hinzuzufügen. Wenn ich einfach hier drüben klicke und „Enter“ drücke und beginne, etwas wie S510 einzugeben, und das ist ein Film, wenn ich eine andere Zeile S200 eingeben möchte, bemerken Sie, dass die Tabelle automatisch die Formatierung zu den neuen Datenzeilen hinzufügt, und diese Datenzeilen werden als Objekt in die Tabelle aufgenommen. Nun, eine Sache, die ich möchte, dass Sie sich vorstellen, diese Tabelle mit einer bedingten Formatierung erstellen zu müssen . Sie müssten viele wirklich interessante Formeln und Funktionen tun , um diese gebänderten Zeilen so zu färben. Das Schöne an Excel -Tabellen ist, dass Excel sich um all diese bedingte Formatierung für Sie kümmert , so dass, wenn Sie neue Datenzeilen hinzufügen, sie automatisch farbcodiert werden, damit die Daten auffallen. Schließlich möchte ich, dass Sie versuchen, eine Formel, beliebige Formel in Spalte K, wo es heißt, Meine Formel. Ich werde gleich schreiben, die eine Zelle in vielleicht der gleichen Reihe hier getroffen hat, und Sie bemerken, dass es Ihnen diese Referenzen gibt, die Spalte, anstelle eines traditionellen Schlachtschiffs Art von Referenz, haben wir A1 und B5. Es ist ein wenig anders als das, was Sie sehen werden, aber wir werden mehr darüber sprechen, was das ist und warum es wirklich mächtig ist in einer zukünftigen Lektion über strukturierte Referenzen. Um zusammenzufassen, haben wir einige der Vorteile und Funktionen von Excel -Tabellen durchlaufen. Wir haben Ihnen gezeigt, wie Sie Ihre Tabellen etwas weniger fehleranfälligmachen Ihre Tabellen etwas weniger fehleranfällig indem Sie diese bedingte Formatierung haben, die leicht angewendet wird, und auch haben wir wirklich schnell durchlaufen, wie Sie Tabellen filtern und sortieren können. Ein Schlüssel zum Mitnehmen aus dieser Lektion besteht darin, einfach mit dem Tisch in der Klassenarbeitsmappe zu spielen . In der nächsten Lektion werden wir darüber sprechen, wie Sie Ihre eigene Tabelle von Grund auf neu erstellen können, damit Sie mit einer Analyse beginnen können. 3. Excel-Tabelle erstellen: Wir werden eine Excel -Tabelle von Grund auf neu erstellen, indem Sie eine Liste von Restaurants in der Klassenarbeitsmappe verwenden. Lasst uns loslegen. Wir haben eine Liste von Restaurants hier mit dem Standort, dem Umsatz, dem Franchising-Status, und Sie können mehr über diesen Datensatz erfahren, indem Sie auf diese URL klicken , die ich hier in dieser Zelle habe. Lassen Sie uns jedoch Schritt für Schritt durchlaufen, wie Sie eine Tabelle erstellen und einige der Features in dieser Tabelle verwenden. Setzen Sie eine wir gehen, um auf „Einfügen“ und dann „Tabelle“, und wir werden diese Tabelle nennen, Restaurants. Ich möchte, dass Sie zuerst alle Ihre Daten auswählen. Es sind ungefähr 50 Reihen. Wechseln Sie dann zu Tabelle einfügen. Standardmäßig wird es sagen, dass meine Tabelle Header hat. Normalerweise möchten Sie dies wie überprüft halten, da unsere Tabelle Header hat, können Sie hier in Zeile 10 sehen. Ich werde auf „Okay“ klicken, und so erstellt Excel standardmäßig eine Tabelle für Sie. Sie bemerken, dass Sie die gebänderten Farbreihen erhalten, und Sie erhalten auch diese Drop-Downs zum schnellen Filtern und Sortieren. Sie sehen, was die Vorteile hier der Tabelle ist, wenn Sie Enter drücken und Sie wieder ein neues Restaurant eingeben und sagen, dass dies Arby ist, Excel wird diese Tabelle automatisch erweitern, um diese neue Zeile von Daten einzuschließen. Wenn ich neue Datenzeilen, Taco Bell, hinzufüge werden Sie sehen, wie die Excel -Tabelle automatisch die Zeilen für Sie codiert, so dass Sie sich nicht um die bedingte Formatierung kümmern müssen , wie Sie es normalerweise mit einem normalen Liste der Daten. Jetzt werde ich einige Daten aus der Tabelle löschen und versuchen, in Schritt 2 durch die leeren Zellen zu navigieren. Lassen Sie uns einfach ein paar dieser Zeilen löschen. Sie bemerken, dass dies alles weg ist, aber Excel denkt immer noch, dass diese Zeilen wahrscheinlich Tabelle sind. Bevor wir diese Zeilen löschen, möchte ich, dass Sie navigieren, indem Sie „Command“ oder „Control“ mit den Pfeiltasten drücken. Sie bemerken, dass sich das Verhalten ein wenig von dem unterscheidet, was Sie erwarten würden. Sie können zu den Rändern der Tabelle gehen, anstatt wenn es eine neue Zelle mit einem Wert gibt. Ich werde nur diese Zeilen auswählen, mit der rechten Maustaste klicken und dann „Löschen“ drücken, Tabellenzeilen löschen, und diese Zeilen werden aus meiner Tabelle gelöscht, so dass sie nicht als leere Zeilen gezählt werden. Wir haben die leeren Zeilen gelöscht, und jetzt möchte ich, dass Sie einige andere Optionen für Excel -Tabellen tun, wie wir diese Tabelle in eine Pivot-Tabelle konvertieren können , wenn wir wollen, indem Sie auf „Tabelle“ in der Multifunktionsleiste, und klicken Sie dann auf „Mit PivotTable zusammenfassen“. Ich werde das vorhandene Arbeitsblatt in diesem Fall verwenden und der Bereich wird Zelle J14 sein. Sobald ich auf „OK“ für die Pivot-Tabelle klicke, werden Sie sehen, dass dies hier erstellt wird, also haben wir die Möglichkeit, eine Pivot-Tabelle zu erstellen, wenn wir wollen. Wenn Sie zu einem beliebigen Zeitpunkt Ihre Excel -Tabelle wieder in einen normalen Datenbereich konvertieren möchten , müssen Sie nur noch einmal auf die „Tabelle“ -Multifunktionsleiste klicken, und eine der Optionen in der Symbolleiste ist in Bereich konvertieren. Wenn ich auf „In Bereich konvertieren“ klicke, entfernt Excel automatisch die Drop-Downs für mich, aber die bedingte Formatierung bleibt weiterhin auf der Tabelle, so dass Sie das entfernen können, indem Sie die Formatierung löschen, wenn Sie möchten. Ich werde diese Umwandlung in Bereich rückgängig machen. Eine letzte Sache, die ich tun möchte, ich habe vergessen zu erwähnen ist in Schritt 1 ist, dass wir diese Tabelle in Restaurants oben links umbenennen möchten . Wenn ich auf eine Zelle in der Tabelle klicke und wieder auf die Registerkarte „Tabelle“ in der Multifunktionsleiste klicke, stellen Sie fest, dass Excel mir den Tabellennamen Tabelle4 gegeben hat. Ich werde dies in Restaurants umbenennen und „Enter“ drücken, damit ich in Zukunft, wenn ich diese Tabelle jemals referenzieren möchte, einfach Restaurants anstelle von Table4 oder was auch immer Excel mir als Standard gibt, nennen kann . In dieser Lektion haben wir einige der Vorteile der Verwendung einer Excel -Tabelle und wie Sie eine Excel-Tabelle erstellen, wie wir Zeilen löschen und einige schnelle Formatierung auf die Tabellen anwenden können. In der nächsten Lektion werden wir näher erläutern, wie Sie berechnete Spalten in Ihrer Excel -Tabelle filtern, sortieren und erstellen. 4. Excel-Tabelle filtern und sortieren: Wir werden tiefer in das Filtern und Sortieren Ihrer Excel -Tabelle eintauchen und auch, wie Sie Ihrer Tabelle eine neue Formel hinzufügen, die als berechnete Spalten bezeichnet wird. Lasst uns eintauchen. Wir haben die gleiche Tabelle von Restaurants auch wie eine Excel -Tabelle. Wenn ich hier auf „Tabelle“ klicke, sehen Sie, dass diese Tabelle Rest2 heißt. Wie Sie bereits gesehen haben, können Sie nach jeder dieser Spalten sortieren , indem Sie auf „Aufsteigend“ oder „Absteigend“ klicken, und ich kann diese Sortierung einfach wirklich schnell zu jeder gewünschten Spalte hinzufügen. Ich möchte, dass Sie jetzt in Schritt zwei, ist es, eine Zusammenfassung Zeile zu dieser Tabelle hinzuzufügen und den durchschnittlichen Umsatz zu finden, die in Spalte D in unserer Liste der Restaurants ist. Wenn Sie zu Tabelle gehen, klicken Sie auf „Total Row“ und Sie bemerken ganz unten in Zeile 61 wir diese dunkelblaue Zeile für unsere Summen erhalten. In der Spalte Vertrieb möchte ich, dass Sie auf das Dropdown-Menü klicken und einfach auf „Durchschnitt“ klicken. Jetzt können wir nur den durchschnittlichen Umsatz in all diesen Restaurants finden, was ungefähr $34 Millionen ist. Das ist eine wirklich schöne Möglichkeit, die durchschnittliche Summenanzahl schnell zu erhalten, genau wie in einer traditionellen Pivot-Tabelle, aber in einer Excel -Tabelle wird sie durch diese allerletzte Zeile am unteren Rand Ihrer Tabelle gehandhabt. In Schritt drei erstellen wir einen Slicer basierend auf der Franchising-Spalte. Jetzt Slicer wie eine schöne optisch ansprechende Art und Weise für Ihr Team, Ihre Kollegen, schnell mit Ihrer Excel -Tabelle zu interagieren, werde ich hier auf meine „Tabelle“ klicken, stellen Sie sicher, dass Sie auf der Registerkarte Tabelle in der Multifunktionsleiste sind und klicken Sie auf „Einfügen Slicer“. Sie erhalten dieses Menü hier, wo Sie auswählen können, auf welche Spalte Sie aufschneiden möchten. Ich werde nur auf „Franchising“ klicken, „Okay“ drücken, und Sie werden dieses kleine Menü bekommen, wo Sie auswählen und wählen Sie Ja oder Nein. Wenn Sie auf Ja oder Nein klicken, wird diese Franchising-Spalte automatisch auf diesen bestimmten Wert gefiltert. Das ist ein wirklich nettes Feature mit Ihren Schneidemaschinen zu haben. Wenn ich hier auf diese Option klicke, diese Häkchenoption im Slicer, kann ich jetzt mehrere Optionen in meinem Slicer Box auswählen. Ich werde nur den Slicer für jetzt entfernen, auf „Löschen“ drücken und zurück zu meiner Haupttabellenansicht hier. Schließlich werden wir eine Formel in die erste leere Spalte auf der rechten Seite schreiben, die Spalte I ist, um den durchschnittlichen Umsatz pro Geschäft zu berechnen. Lassen Sie mich diesen Filter hier rückgängig machen. Wenn ich auf Zelle I10 klicke und durchschnittliche Verkäufe schreibe, bemerken Sie, dass Excel diese Spalte automatisch als neue Spalte zu meiner Tabelle hinzufügt, und wenn ich beginne, gleich in Zelle I11 zu schreiben, entspricht Umsatz, was Spalte D geteilt durch Spalte F ist, was Standorte, schauen Sie sich die Formel an, die erstellt wird, Verkäufe geteilt nach Standorten, lassen Sie uns „Enter“ drücken und sehen, was passiert. Die Formel wird automatisch bis zur letzten Zeile in meiner Tabelle gefüllt, so dass ich keine Doppelklicken oder meine Formel ganz nach unten ziehen muss. Formeln in Tabellen werden automatisch in einem Schritt auf die gesamte Spalte angewendet, sodass Sie sich keine Gedanken über all diese zusätzlichen manuellen Schritte zum Erstellen der Formel machen müssen. Möglicherweise haben Sie etwas Ähnliches getan, was in einer Excel -Tabelle als berechnete Spalte bezeichnet wird. Möglicherweise haben Sie etwas Ähnliches in einer Pivot-Tabelle getan, die als berechnetes Feld bezeichnet wird. Wir machen nur das [unhörbare] in einer Excel -Tabelle. ein wenig zurücktreten, wenn Sie diese Formel aufbauen und diese Tabelle von Grund auf aufbauen müssten , und um auch die Zusammenfassungsdaten hier in dieser Zeile 61 zu erhalten, müssten Sie eine komplizierte Liste von Formeln erstellen. Möglicherweise verwenden Sie eine Formel COUNT A, um alle Zellen auszuwählen, die einen leeren Wert haben. Das Schöne an dieser Gesamtzeile ist , dass sie automatisch am Ende der Tabelle hinzugefügt wird. Sie müssen sich keine Sorgen darüber machen, all diese zusätzlichen Formeln hinzuzufügen , die Ihr Leben nur ein wenig schwieriger machen. Wir haben darüber gesprochen, wie man mehr Filtern, Sortieren und Erstellen berechneter Spalten in einer Excel -Tabelle durchführen kann. Nun sieht die Excel-Tabellenformel, die Sie in der Spalte für den durchschnittlichen Umsatz gesehen haben, knifflig Daher werden wir in der nächsten Lektion zu strukturierten Referenzen tiefer eingehen. 5. Formeln in strukturierten Referenzen: Wir werden tiefer in strukturierte Referenzen eintauchen. Ich werde auf verschiedene Arten gehen, wie Sie Formeln mit strukturierten Referenzen in Ihren Excel -Tabellen erstellen können . Eine kurze Anmerkung zu diesen strukturierten Referenzen ist, dass Sie, wie bei durchschnittlichen Umsätzen, in der Tabelle selbst auf die Spalte verweisen, indem Sie dieses kaufmännische Und-Zeichen plus den Spaltennamen in Klammern setzen. Dies unterscheidet sich von dem, wenn Sie gleich, Zelle K5 oder so etwas oder K9 tun. In Excel -Tabellen referenzieren Sie die gesamte Spalte, daher wird die Formel beim Schreiben einer Formel allein durch die gesamte Spalte ausgefüllt. Wenn Sie sich außerhalb der Tabelle befinden, wie wir hier in Schritt 1 haben, werden wir gleich schreiben. Ich werde noch einmal auf die Verkaufsspalte verweisen. Sie bemerken, dass ich zuerst den Tabellennamen Rest3 setzen muss, gefolgt von der strukturierten Referenz, die @Sales ist. Dies gibt mir nur den Wert für diese Zeile, obwohl sie auf die gesamte Spalte verweist. Dies ist eine Sache, an die Sie sich gewöhnen müssen, wenn Sie Zellen und Zeilen in Ihrer Tabelle referenzieren. Nur um zu bestätigen, klicke ich hier auf „Tabelle“, in der Tat genannt Rest3. Schließlich möchte ich, dass Sie in Schritt 2 eine weitere Übung mit dem Verweisen auf diese Tabelle ausführen, ist das Schreiben gleich und versuchen Sie, die gesamte Tabelle mit den Headern auszuwählen. Sie bemerken, dass es Rest3 #All sagt. Dies sagt Excel, dass Sie die gesamte Tabelle gleichzeitig referenzieren. Wenn Sie nur auf die Daten verweisen möchten, können Sie einfach gleich schreiben und einfach alles ohne den Header auswählen und Sie werden nur sehen, es steht Rest3, aber Sie können auch schreiben [#Data und Sie erhalten das gleiche Ergebnis. Dies wird mir einen Fehler geben, weil ich die Tabelle in einer Zelle repräsentiere , so dass das nicht funktioniert. Ich möchte, dass Sie eine SUMME-Formel auf die Speicherplatzspalte außerhalb der Tabelle schreiben , indem Sie das, was wir gerade mit Formeln und automatischen Füllungen mit Tabellenreferenzen gelernt haben. Lasst uns die Formel hier schreiben, gleich Summe. Der Name unseres Tisches ist Rest3. Ich kann hier auf eine dieser strukturierten Referenzen klicken, Rest3 und versuchen wir, nur die Spalte Standorte zu referenzieren. Ich kann die linke Klammer schreiben. In der Minute, in der Sie eine linke Klammer schreiben, haben Sie die Möglichkeit, eine der Spalten in Ihrer Tabelle auszuwählen. Ich kann nun auf „Standort“ klicken Sie auf die rechte Klammer. Was ich hier mache, ist, dass ich Orte mit einem S schreiben muss, um auf diese Spalte zu verweisen, Spalte F. Was ich hier sage, ist alles in der Spalte Locations in meiner Rest3-Tabelle zu summieren . Drücken Sie „Enter“ und das ist 1.735. Ich werde das nur so formatieren, dass es eine Zahl ist. Sie fragen sich vielleicht, warum ist das einfacher, als nur gleich Summe zu schreiben und dann alle Zellen hier auszuwählen? Traditionell tun Sie das mit einer normalen Liste von Daten, aber das Schöne an diesen strukturierten Referenzen ist, wenn ich hier bis zur letzten Zeile meiner Tabelle gehe und eine neue Zeile, 51, Taco Bell, hinzufüge und einen Wert zu meiner Spalte Locations hinzufüge, Nehmen wir an, bei 100 oder 1.000 wird diese Zahl automatisch in meiner Summe aktualisiert, da diese strukturierte Referenz basierend auf dem, was in Ihrer Tabelle ist, erweitert und schrumpft. Das ist eine der leistungsfähigsten Funktionen von Excel -Tabellen ist , wenn neue Daten zu Ihrer Tabelle hinzugefügt werden, werden die strukturierten Referenzen aktualisiert. Stellen Sie sich vor, dies mit einer COUNT-Formel zu tun, die Sie heute verwenden könnten, um alle Werte in Ihrer Spalte zu zählen. Die Strukturreferenzen machen es zu einer viel saubereren, weniger fehleranfälligen Möglichkeit, auf alle Werte in Ihrer Tabelle zu verweisen. Schließlich werden wir eine kleine Übung machen, um einige zusammenfassende Statistiken hier in Spalte M und N zu erstellen . Ich möchte, dass Sie eine SUMIFS-Formel schreiben, um alle Verkäufe für diese Restaurants basierend auf Standorten mit 75 Grad oder Plus zusammenzufassen , 50 oder 75 für Restaurants, die Franchise haben und ohne Franchise. Fangen wir an, es auszuschreiben. Ich kann gleichwertige Sumifs schreiben. Ich werde nur hier hineinzoomen, damit Sie alle meine Daten sehen können, gleich Sumifs. Ich werde meinen Cursor verwenden, um meine Reichweite zu saugen. Mein Summenbereich wird nur meine Verkaufsdaten sein, Komma treffen, und dann werden meine ersten Kriterien Standorte sein. Ich möchte alle Orte, die [unhörbar] sind. Ich werde diese Zeile hier löschen, nur um sicherzustellen, dass wir die richtigen Standortdaten haben. Ich werde auf „Zeilen löschen“ klicken. Du fragst dich vielleicht, wie ich die Kriterien einbringen werde? Normalerweise lege ich in meiner Excel -Datei diese kleinen Hilfsspalten ein. Ich bin einige, die die Zahl 1.000, 75, 50, 25 und 0 schreiben . Auf diese Weise kann ich der SUMIFS-Formel mitteilen, was ich als meine Kriterien referenzieren soll. Lassen Sie uns voran gehen und schreiben Sie die Formel gleich Sumifs. Mein Summenbereich wird wieder Verkäufe sein, Komma, mein Kriterienbereich wird Standorte sein, die weniger als 1.000 sind. In diesem Fall musste ich ein kaufmännisches Und-Zeichen weniger als 1.000, Komma, aber größer als oder gleich 75 für diese Zeile, ich werde einfach das doppelte Anführungszeichen eingeben, kaufmännisches Und-Zeichen. Dann möchte ich auch nur Geschäfte sehen, die in diesem Fall ein Franchise haben. Ich kann Komma Franchising setzen, ja. Nur um zusammenzufassen, ich nehme die Verkäufe, summiere den Umsatz, wenn Standorte weniger als 1.000, aber größer als oder gleich 75 sind , drücke „Enter“. Jetzt haben wir meine Formel aufgebaut, drücken Sie „Enter“. Wenn ich diese Formel einfach unten ausfülle, es mir automatisch den Wert, da ich meine Kriterien und Spalte L dynamisch referenzieren habe. Ich kopiere die gleiche Formel und lege sie in diese Zellen genau hier. Aber die Hauptsache ist, ich werde das Franchising hier in Nein ändern, weil es Zellen sind. Ich möchte, dass mein Bericht hier kein Franchising hat. Ich werde jetzt diese Werte wirklich schnell zusammenfassen, nur um sicherzustellen, dass der Gesamtumsatz das ist, was ich erwarte, das ist 1,689. Wieder, wenn ich hier zu meiner Verkaufsspalte gehe und Summe schreibe, ist dies in der Tat 1,689. Auf diese Weise würden Sie die SUMIFS-Formel mit dynamischen Zellreferenzen für unsere Standorte verwenden , aber mithilfe von strukturierten Referenzen die Daten tatsächlich aus der Excel -Tabelle ziehen. Wir haben gesehen, wie Sie mit strukturierten Referenzen auf die Spalten in Ihrer Tabelle verweisen können, und wir haben auch gezeigt, wie Sie strukturierte Referenzen in einigen der beliebtesten Formeln verwenden können, die Sie heute verwenden, wie SUM und SUMIFS. Wir haben auch gesehen, wie Sie Ihre Formeln mit strukturierten Referenzen schreiben, Ihre Formeln etwas einfacher zu lesen und viel einfacher zu debuggen sind, besonders wenn Ihre Kollegen die Formeln oder Ihre Excel -Datei von Ihnen abholen. Wir besprechen nun, wie Sie strukturierte Verweise aus Ihren Excel -Tabellen mit einer derbeliebtesten Formeln verwenden Ihren Excel -Tabellen mit einer der können, die Sie heute verwenden, nämlich VLOOKUP. 6. VLOOKUP und strukturierte Referenzen: Wir werden jetzt die VLOOKUP-Formel verwenden, um eine Excel -Tabelle zu suchen , um die Daten in unsere Hauptdatentabelle zu bringen. jetzt von unseren gleichen Listen von Restaurants arbeiten, sehen Sie die Spalten L und M, ich habe den Namen der Stadt plus die Bevölkerungszahl dieser bestimmten Stadt. In Schritt 1 möchte ich, dass Sie diese Liste der Städte und der Bevölkerung in eine Excel -Tabelle namens Pop umwandeln. Lassen Sie uns diese Zellen auswählen. Sie können auf „Tabelle einfügen“ klicken oder eine Verknüpfung, die ich verwende, ist dieses Drücken von Control T, das nur das Tabellenmenü für Sie aufruft. Meine Tabelle hat Header Hit „okay“ und jetzt haben wir dies als Tabelle. Nur um sicher zu sein, klicken Sie auf „Tabelle“ und denken Sie daran, wir möchten diesen Tabellennamen umbenennen, um Pop für unsere Bevölkerung zu sein. Wir haben jetzt zwei Tabellen in diesem Arbeitsblatt, wir haben Rest312, und dann haben wir unsere Pop-Tabelle, und Sie können dies in der oberen linken Ecke unseres Arbeitsblatts sehen. Ich möchte, dass Sie jetzt in Schritt 2 eine neue Spalte zu unserer Haupttabelle hinzufügen und einen VLOOKUP schreiben , um die Bevölkerung unserer Stadt basierend auf Spalte C, die unsere Standortdaten sind, zu suchen . Lassen Sie uns diese Population nennen und wie wir bereits gesehen haben, wenn Sie eine neue Spalte hinzufügen, nimmt die Excel -Tabelle diese Spalte automatisch als Teil der Tabelle auf. Beginnen wir mit dem Schreiben der VLOOKUP-Formel, die Sie sehr an VLOOKUP gewöhnt sind. Wir möchten die Spalte „Location“ aus dem Tisch unseres Restaurants nachschlagen und feststellen, wie sich diese strukturierte Referenz einfach in einen Hut-Symbol-Standort verwandelt. Unser Tabellen-Array ist, dass Sie einfach nur Pop schreiben und bemerken können, wie, wenn ich Pop schreibe, es die Zellen in meiner Pop-Tabelle hervorhebt. Ich könnte auch linke Klammer Hashtag schreiben und dies verweist auf die gesamte Tabelle plus die Header, die ich nicht wirklich brauche, aber es wird sich nicht wirklich auf meine Formel auswirken. Lassen Sie es einfach als Pop, 2, 0, weil ich die zweite Spalte zurückgeben möchte, die die Bevölkerung ist und wenn ich auf „Enter“ klicke, füllt es automatisch die Formel für meine gesamte Liste von Restaurants aus. Wir haben eine Menge nicht anwendbar, weil nicht jede einzelne Stadt in meinem Restaurant-Tisch ist in unserer Location-Tabelle. Aber sagen wir, ich möchte meiner Pop-Tabelle eine neue Datenzeile hinzufügen, die Schritt 4 ist. Nehmen wir an, ich möchte die Bevölkerung für Louisville, Kentucky, hinzufügen. Ich werde das hier vorerst durch eine Paste kopieren und sagen wir, Louisville ist 50.000. Beachten Sie, wie diese Zeile in meiner Restaurant-Tabelle automatisch gefüllt wird, da meine VLOOKUP-Formel automatisch gefüllt wird, da meine VLOOKUP-Formel immer eine Suche nach meiner Pop-Tabelle durchführt und sobald ich eine Stadt zu meiner Pop-Tabelle hinzufüge, wird die strukturierte Referenz automatisch erweitert in meiner Suche, die in dieser Spalte genau hier ist. Wir können das Gleiche mit Indexübereinstimmung machen, die Sie möglicherweise vorher verwenden. Ich kann Index schreiben und ich möchte meine gesamte Bevölkerung hier auswählen. Ich möchte diese Standortspalte innerhalb meiner Pop-City-Position mit „Enter“ übereinstimmen, und ich möchte tatsächlich eine genaue Übereinstimmung durchführen, weshalb es mir Werte in jeder einzelnen Zelle gibt. Lasst uns Komma Null machen. Es sollte so funktionieren, aber, oh, tut mir leid, es ist in diesem Spiel. Dies wird Ihnen genau die gleichen Ergebnisse wie die VLOOKUP geben, aber beachten Sie, wie wir die strukturierte Referenz jetzt in mehreren Tabellen verwenden. Wir betrachten die Population aus einer Pop-Tabelle, weil es sich um einen Wert handelt, den Sie zurückziehen möchten. Wir passen die Standortspalte, die diese Spalte ist, und dann vergleichen wir das mit der Stadtspalte, die diese Pop-Tabelle ist. Eine letzte Sache, die über soziale Referenzen zu beachten ist, ist, dass, wenn Sie das @-Symbol sehen , das auch eine Möglichkeit ist, Excel mitzuteilen, dass dies diese Zeile in meiner Tabelle referenziert, weshalb Sie nur Seattle, Washington, aber nicht die gesamte Spalte sehen , die wäre nur Standort für sich selbst. Dies würde immer noch funktionieren, aber um genauer zu sein, möchten Sie dieses @-Symbol einfügen, um sicherzustellen, dass Sie nur auf diese Datenzeile verweisen. Wir haben uns untersucht, wie Sie die VLOOKUP-Formel verwenden können, eine wirklich gängige Formel, die heute verwendet wird, mit Strukturreferenzen aus Ihren Excel -Tabellen, um Daten dynamisch nachzuschlagen, so dass, wenn Sie Ihrer Nachschlagetabelle neue Daten hinzufügen, Ihre Hauptstatistik Tabelle, dass sie nicht automatisch aktualisiert und korrekt eingezogen werden. Wir haben gesehen, wie Sie strukturierte Referenzen und Formeln verwenden können Jetzt gehen wir in einen fortgeschritteneren Anwendungsfall der Verwendung strukturierter Referenzen mit einer Dropdown-Liste und Datenvalidierung in der nächsten Lektion ein. 7. Drop-down-Listen und Datenvalidierung: Wir werden eine Dropdown-Liste mit Excel -Tabellen erstellen, und wir zeigen Ihnen, wie diese flexiblen Optionen mithilfe der Tabelle automatisch erweitert werden können. Mit unseren gleichen Listen von Restaurants haben wir diese separate Liste von Daten hier genannt KnownFor. Wir haben nur einige verschiedene Attribute, mit denen wir unsere Restaurants kennzeichnen möchten. Aber ich möchte diese Liste von Attributen in eine Dropdown-Liste verwandeln , die wir in unserer Haupttabelle von Restaurants verwenden können. In Schritt 1 verwandeln wir die Liste „Bekannt für“ in eine Tabelle namens „Bekannt für“. Genau hier oben werden wir Tabelle einfügen und meine Tabelle hat Kopfzeilen. Dann in Ordnung. Im Tabellennamen möchten wir diese knownFor nennen. Wir haben dies als unsere Haupttabelle für die Auswahl von Attributen. Ich möchte, dass Sie jetzt zu Formeln gehen und dann zu Name definieren gehen und dieser Tabelle einen Define-Namen der Typen geben. Wir möchten die Kopfzeilen nicht einschließen. Nun sind der Tabellenname und Define-Namen zwei separate Konzepte. Dies ist etwas, das ein wenig schwierig wird, wenn Dropdown-Liste mit Data Validation erstellt wird. Wir gehen zu „Formeln“, „Name definieren“ und Sie bemerken, hier habe ich bereits eine Reihe von definierten Namen, die Tabellen enthält. Ich werde auf „Plus“ klicken, und rufen wir diese Typen an. Wie groß ist der Bereich der Zellen hier? Sie können einfach knownFor schreiben, weil das unsere Liste unserer Tabelle sein wird. Sie bemerken, wie, wenn ich hier auf die Tabelle klicke, es immer noch knownFor sagt, wenn ich zu Formeln zurückgehe, den Namen definieren, werden Sie sehen, dass, wenn ich auf Typen klicke, es auf meinen Zellbereich verweist, der bekanntFor ist. Gehen wir jetzt zu Schritt 3. Ich möchte eine neue Spalte in meiner Haupttabelle namens KnownFor erstellen und dann zu Datenvalidierung gehen, was Sie vorher wahrscheinlich getan haben, und eine Dropdown-Liste erstellen, in der wir die Typen definieren Namen verwenden werden. Nur um zusammenzufassen, haben wir einen Define-Namen namens Typen, die auf unsere knownFor Tabelle verweist. Klicken wir auf „Spalte J“ Bekannt für und jetzt gehen wir zu „Daten“ „Datenvalidierung“. Wir werden das ändern erlaubt zu Liste und was ist unsere Quelle wird sein? Sie können versucht sein, gleich zu schreiben, KnownFor als unsere Liste, aber wir müssen tatsächlich unseren Define-Namen namens Typen verwenden , der auf unsere KnownFor Tabelle verweist. Lassen Sie uns gleich schreiben Typen, drücken Sie „OK“, und lassen Sie uns sehen, was in diesem Dropdown passiert. Sie bemerken, dass wir alle Werte aus unserer KnownFor -Tabelle erhalten. Ich kann auch einfach diese Formel nach unten ziehen und Sie können auf das Dropdown-Menü klicken, Sie können alle diese Werte sehen. Eine Sache zu beachten ist, wenn Sie auf Formeln, Definieren Name und in meiner KnownFor -Tabellenreferenz hier klicken , wenn Sie versehentlich die gesamte Tabelle hier so ausgewählt haben, bemerken Sie, wie sich die Referenz von KnownFor zu KnownFor ändert Alle bekannt für. Ich weiß, dass das kompliziert aussieht, aber der Grund, warum dies problematisch ist, ist, wenn Sie in Ordnung sind, wird dies jetzt die KnownFor Spaltenüberschrift innerhalb des Dropdowns enthalten, was Sie nicht geschehen möchten. Deshalb werde ich das neu definieren, nur um bekannt zu sein. Auf diese Weise haben Sie nur die auswählbaren Optionen in der Tabelle „KnownFor“. Wenn ich dies den ganzen Weg nach unten auf den Rest meiner Tabelle ziehe, habe ich jetzt dieses Attribut, das ich dann aus meiner KnownFor -Tabelle auswählen kann. Ein weiterer Vorbehalt über Dropdown-Liste, die Sie vielleicht gesehen haben, ist, wenn ich dies von Smoothies zu Smoothies-YM ändere, Sie bemerken, wie alle meine Sandwiches immer noch Sandwiches bleiben. Ich muss tatsächlich auf das Dropdown klicken und dieses in Sandwiches-YM umbenennen. Nur um sicherzustellen, dass Sie hier in Ihrer KnownFor Dropdown-Liste Werte korrigieren, müssen Sie es manuell hier in Ihrer Haupttabelle ändern. Nun, natürlich, der Vorteil der Verwendung von Excel -Tabellen in unserer Dropdown-Liste hier, ist, wenn ich einen neuen Wert zu meiner knownFor Tabelle hinzufüge, sagen wir, ich nenne diese Pfannkuchen, ich bekomme jetzt Pfannkuchen als auswählbare Option in meiner Dropdown-Liste, was wirklich schön zu sehen ist, weil ich mir keine Sorgen darüber machen muss die Formel auf die richtigen Zellbezüge in dieser Spalte verweist. Ich kann einfach über den Define-Namen auf die Tabelle verweisen. Nun, ein weiterer Schritt hier ist, dass wir dies ein wenig vereinfachen können, indem die indirekte Funktion und unser Quellfeld für die Datenvalidierung verwenden. Lassen Sie uns das alles für jetzt löschen und sagen wir, ich klicke auf Datenvalidierung. In diesem Quellfeld, anstatt Typen zu schreiben, die auf diesen Namen verweisen, der die auf diesen Namen verweisen, auf diese Tabelle verweist, kann ich tatsächlich die indirekte Funktion schreiben und den Tabellennamen schreiben, der knownFor ist. Dann fügen Sie den Spaltennamen ein , der („KnownFor [KnownFor]“) Was ich Excel hier sage, ist mir die knownFor -Tabelle zu geben, die dies ist und der Spaltenname ist bekannt für, drücken Sie „OK“ und dies wird mir auch das Dropdown-Menü geben -Liste für meine Datenvalidierung. Nur eine andere Möglichkeit, keinen Define-Namen verwenden zu müssen, dann auf die Tabelle verweist, wenn Sie sie nur verknüpfen und einfach auf die Tabelle selbst verweisen möchten , indem Sie diese indirekte Problemumgehung verwenden. Um zusammenzufassen, denken Sie darüber nach, was Sie tun müssen, um diese Dropdown-Validierung mit einer dynamisch automatisch erweiterbaren Liste zu erstellen , die so etwas wie die Zählfunktion oder die Offset-Funktion verwendet. Die Vorteile dieser Definition Name Now besteht darin, dass Sie dies überall in Ihrer Excel -Datei verwenden können , wo Sie auf diese bestimmte Tabelle verweisen möchten. Wenn Sie wieder zur vorherigen Lektion zurückkehren, denken Sie darüber nach, wie Ihre Tabellennamen und Ihre Definiernamen Variablen sein können , die auf alle verschiedenen Tabellen in Ihrer Excel -Datei verweisen. Wir haben eine Dropdown-Liste mit der Funktion „Datenvalidierung“ in Excel erstellt. Wir verwenden Excel -Tabellen als die auswählbaren Optionen in unserer Dropdown-Liste. Wir haben eine Vielzahl verschiedener Dinge in Excel -Tabellen mit strukturierten Referenzen und Formeln erledigt . Lassen Sie uns nun in die Formatierung unserer Tabellen eintauchen, damit sie für unser Team und für unsere Kollegen wirklich schön und hübsch aussehen . 8. Excel-Tabellen formatieren: Wir werden über schnelle Möglichkeiten sprechen, wie Sie Ihre Tabellen so formatieren können, dass sie wirklich schön und schön und gut formatiert für Ihr Team und für Ihre Kollegen aussehen . Wir haben einen neuen Datensatz in dieser Lektion. Es ist eine Liste der Bitcoin-Preise nach Datum im Jahr 2013. Wie Sie sehen können, war der Preis damals offensichtlich viel niedriger. Wir haben die offene, hohe, niedrige, enge, und die Spread für diese verschiedenen täglichen Bitcoin-Preise. In Schritt 1 möchte ich, dass Sie auf das Dropdown-Menü im Tabellenformatierungsabschnitt klicken , um Ihr eigenes benutzerdefiniertes Tabellenformat zu erstellen. Wenn ich auf die Tabelle klicke, Registerkarte Tabelle in der Multifunktionsleiste, und wenn ich auf dieses Dropdown-Menü hier klicken, können Sie eine Vielzahl von verschiedenen Formatierungsoptionen sehen , die Sie auf Ihre Tabelle anwenden können , basierend auf dem, was Sie für Ihre Berichte benötigen, Ihre Dashboards und auch, was Ihre Kollegen und Teamkollegen für sie nützlich finden. Nun, eine Sache, die wirklich nützlich für diese Formatierungen ist, ist, dass Sie dies noch weiter anpassen können, indem Sie ein benutzerdefiniertes Tabellenformat erstellen , indem Sie mit der rechten Maustaste auf das Tabellenformat in dieser Multifunktionsleiste klicken, auf Duplizieren klicken und dann auf nennen wir diese AlBitCoinTable. Der Grund, warum ich meine Tabelle in diesem speziellen Szenario anpassen möchte , ist, dass ich vielleicht genau wie die Wochenend-Bitcoin-Preise hervorheben möchte, die Tag der Woche sechs und sieben ist, und dann den Wochentag markieren möchte Preise, die Tag der Woche eins bis fünf sind. Mein erster Zeilenstreifen, das ist wie die erste farbcodierte Reihe. Ich kann das ändern, anstatt eine Größe von eins zu sein, ändert sich auf eine Größe von fünf. Dann kann ich in meinem zweiten Zeilenstreifen das von einer Größe von eins zu einer Größe von zwei ändern. Was dies tut, ist anstelle von wechselnden Farben jede Reihe, es wechselt Farben alle fünf Zeilen, und dann zwei Zeilen, fünf Reihen, zwei Reihen entsprechen Wochentagen und Wochenenden. Darüber hinaus können Sie hier auch auf Format klicken, um wirklich einfach die Farbe des jeweiligen Bandes zu formatieren, wenn Sie möchten. Ich werde vorerst nur „Okay“ treffen. Wenn ich hier in meinen Tabellenformaten wieder auf das Drop-down-Menü klicke, kann ich auf dieses benutzerdefinierte Format klicken. Sie bemerken, wie sich die Formatierung jetzt von jeder anderen Zeile ändert, um farbcodiert zu werden, zuerst fünf Zeilen, dann zwei Zeilen, um die Wochenend-und Wochentagspreise von Bitcoin von 2013 wirklich hervorzuheben. Schließlich ist eine weitere Sache bei der Tabellenformatierung manchmal, dass Sie bestimmte Teile Ihrer Spalten farblich codieren möchten. Gehen wir zurück zu unserer regulären Tabellenformatierung hier, was in etwa so ist. Wenn Sie auf die erste Spalte klicken, stellen Sie fest, wie sie automatisch die erste Spalte der Daten hervorhebt, die aufgerufen werden soll. Letzte Spalte hebt nur die letzte Spalte und dann gebänderte Spalten hervor. Wenn Sie eine bestimmte Art von Formatierung haben, wo Sie Farbcodierung gebändert haben. Klicken wir nochmals auf diesen. Beachten Sie, wie standardmäßig gebundene Zeilen vorhanden sind. Wenn ich auf gebänderte Spalten und nicht markierte gebänderte Zeilen klicke, hebt jede andere Spalte anstelle von Zeilen hervor. Nur einige weitere Formatierungen, die Sie auf Ihre Tabelle anwenden können , basieren auf den Anforderungen Ihres Teams. Wir erläutern, wie Sie Ihre Tabellenformatierung mit einigen Excel-Formatierungen für Excel -Tabellen anpassen können, aber auch, wie Sie die abwechselnde Anzahl von Zeilenfarben für Ihre Excel-Tabelle anpassen können. Wir haben viel darüber erfahren, wie Sie Excel -Tabellen für Formeln, strukturierte Referenzen und Formatierungen verwenden können . Lassen Sie uns nun alles in Ihr Projekt für diese Klasse einbinden. 9. Letzte Gedanken: Herzlichen Glückwunsch, Sie haben diese Klasse in Excel -Tabellen genommen, und wir haben alles gelernt, wie Sie Excel-Tabellen erstellen, wie strukturierte Referenzen in Ihren täglichen Excel-Formeln wie VLookup, einige SUMIFS verwenden . Wie Sie sie auf Dropdown-Listen und Datenvalidierung anwenden können , damit Ihre Tabellen in Excel und Ihre Dashboards und Berichte leichter lesbar sind, sind Ihre Formeln einfacher zu debuggen. Ihre Kollegen werden viel glücklicher sein, wenn sie sich Ihre Excel -Dateien ansehen. Ich hoffe, Sie nehmen alle Erkenntnisse aus dieser Klasse und wenden sie auf das Klassenprojekt an, wo Sie einen Datensatz vom Verkehrsministerium zu Flugverspätungen analysieren . Ich hoffe, Sie werden Ihre Ergebnisse und Screenshots Ihrer Berichte in der Projektgalerie für alle zu sehen. Vielen Dank, dass Sie an dieser Klasse teilgenommen haben, und ich freue mich auf Ihre Projekte.