Meine 3 bevorzugten Möglichkeiten, Daten in Excel -Tabellen zu verwenden

Drei praktische Funktionen für die Excel -Datenextraktion und Zusammenfassung

Angenommen, Sie haben eine große Arbeitsmappe mit wunderschön formatierten, gefilterten und sortierten Tischen. Sie denken vielleicht, dass die Arbeit erledigt ist, aber in Wirklichkeit wartet Excel darauf, dass Sie mehr auf diesen Tischen tun und Ihnen dabei helfen, das Beste aus der harten Arbeit zu nutzen, die Sie bisher geleistet haben.

In diesem Artikel werden drei Funktionen oder Kombinationen von Funktionen eingeführt, die ich häufig verwende, mit denen Informationen in Excel -Tabellen extrahieren oder zusammengefasst werden können.

vlookup und hlookup

vlookup und hlookup werden beide verwendet, um Werte an bestimmten Stellen in einer Tabelle zu finden und abzurufen.

• vlookup basiert auf einer vertikalen Datentabelle und schaut in der ersten Spalte (vertikal) in der Tabelle nach.
• hlookup basiert auf der horizontalen Datentabelle und schaut in der ersten Zeile (horizontal) in der Tabelle nach.

vlookup

Hier habe ich eine Liste der Testergebnisse und die erforderlichen Noten für jede Klasse (wir nennen es Tabelle 1). Ich habe auch eine Tabelle mit Studentennoten (von diesem Zeitpunkt an Tabelle 2 bezeichnet). Ich möchte, dass Excel die Informationen in Tabelle 1 verwendet, um die fehlenden Spalten in Tabelle 2 zu vervollständigen.

Ich werde Vlookup verwenden, da ich möchte, dass Excel die Werte in der ersten Spalte von Tabelle 1 nachgibt, um die Noten jedes Schülers in Tabelle 2 zurückzugeben. Die Syntax der Vlookup -Funktion lautet wie folgt:

<code>=VLOOKUP(a,b,c,d)</code>

von:

• a ist der Wert, nach dem man gesucht werden muss (im obigen Beispiel, das ist der Wert in Spalte E),
,
• b
ist eine Tabelle, die die Referenzwerte enthält (in diesem Fall sind es die Zellen A1 bis B9 oder Tabelle 1),
• c
ist die Spaltennummer in dieser Tabelle (ich möchte, dass sie die Ebene zurückgibt, also ist es die zweite Spalte in Tabelle 1) und
• d
ist eine optionale Bedingung, die Excel zeigt, ob die Suchwerte ("true") oder genaue Suchwerte ("False") approximieren sollen. Wenn es leer ist, ist der Standardwert wahr.

In meinem Fall würde ich diese Formel in Zelle F2 eingeben, um die Noten von Tom zu berechnen und dann Autofill zu verwenden, um andere Klassen in der Tabelle zu finden:

<code>=VLOOKUP(E2,$A:$B,2,TRUE)</code>

Ich habe das $ -Symbol verwendet, um eine absolute Referenz für den obigen Wert zu erstellen b , da ich möchte, dass Excel kontinuierlich die Zellen A1 bis B9 verwendet, um den Wert zu ermitteln. Ich habe auch "true" für den Wert d

verwendet, da die Bewertungsgrenze Tabelle enthält, nicht den Rang, der einer einzelnen Punktzahl zugeordnet ist.

hlookup

Hier haben wir die gleichen Grenzinformationen der Ebene, aber diesmal wird sie horizontal angezeigt. Dies bedeutet, dass die Daten, die wir abrufen möchten, in der zweiten Reihe der Grenztabelle befinden.

Die Syntax der Hlookup -Funktion ähnelt der von vlookup:

<code>=HLOOKUP(a,b,c,d)</code>

von:

• a ist der Wert zu finden (in diesem Beispiel der Wert in Spalte c),
• b ist eine absolute Referenz auf die Zelle, die den Suchwert enthält (in diesem Fall ist es A1 bis I2),
• c ist die Zeilennummer in der Tabelle (ich möchte, dass sie die Ebene zurückgibt, also ist es die zweite Zeile) und
• d (optional) ist "true" (ungefährer Wert) oder "Falsch" (exakter Wert).

Also werde ich diese Formel in Zelle C5 eingeben, um die Noten von TOM zu berechnen und dann Autofill zu verwenden, um andere Noten in der Tabelle zu finden:

<code>=VLOOKUP(a,b,c,d)</code>

Index und Match

Ein weiterer effektiver Weg, um Werte zu finden und abzurufen, ist in Index und Übereinstimmung, insbesondere wenn sie zusammen verwendet werden. Der Index sucht und gibt den Wert der definierten Position nach und gibt den Wert des Wertes nach und gibt das Bit des Werts zurück. Zusammen können sie dynamisches Datenabruf implementieren.

einzelne Grammatik

Bevor wir diese Funktionen zusammen betrachten, sehen wir sie kurz einzeln an.

Die Indexsyntax ist

<code>=VLOOKUP(E2,$A:$B,2,TRUE)</code>

wobei a der Bereich der Zellen ist, die Daten enthalten, b ist die zu bewertende Zeilenzahl und c die zu bewertende Spaltennummer.

basierend darauf

<code>=HLOOKUP(a,b,c,d)</code>

bewertet die Zellen B2 bis D8 und gibt die Werte in der vierten Reihe und in der zweiten Spalte in diesem Bereich zurück.

Für die Übereinstimmung folgen wir

<code>=HLOOKUP(B5,$A:$I,2,TRUE)</code>

wobei x der Wert ist, den wir suchen, y ist der Wertbereich, den wir suchen, und z (optional) ist der passende Typ.

basierend darauf

<code>INDEX(a,b,c)</code>

wird mir sagen, wo sich die Nummer 5 in Bereich B2 bis B8 befindet, während 0 Excel einreicht, um eine genaue Übereinstimmung durchzuführen.

Verwenden in Kombination

In diesem Beispiel möchte ich, dass Excel mir die Anzahl der Ziele mitteilt, die ein bestimmter Spieler in einem bestimmten Monat erzielt. Insbesondere möchte ich wissen, wie viele Tore ein Spieler C im dritten Monat erzielt wurde, aber ich werde diese Formel erstellen, damit ich diese Bedingungen jederzeit ändern kann.

Um dies zu tun, muss ich Excel über die Position von Player C in der Tabelle ermitteln und mir dann den Wert in der dritten Spalte der Daten mitteilen.

In der Zelle G4 werde ich mit der Indexfunktion beginnen, da ich möchte, dass Excel aus meinen Originaldaten nachgibt und einen Wert zurückgibt. Ich werde dann Excel sagen, wo ich nach diesen Daten suchen soll.

<code>INDEX(B2:D8,4,2)</code>

Der nächste Teil der Indexsyntax ist die Zeilennummer, die je nach Spieler, den ich in Zelle G2 erkläre, variiert. Wenn ich beispielsweise Spieler A finden möchte, wird es die erste Reihe sein. Dazu starte ich die Match -Funktion, da ich möchte, dass Excel dem Player in der Zelle G2 mit der entsprechenden Zelle in der Player -Spalte (A2: A8) übereinstimmt und herausfindet, welche Reihe sie sich befindet. Ich habe auch die letzten 0 hinzugefügt, weil ich möchte, dass Excel das genaue Abruf zurückgibt.

<code>=VLOOKUP(a,b,c,d)</code>

Jetzt, da ich der Excel -Index -Funktionsleitungsnummer mitgeteilt habe, muss ich dies mit der Spaltennummer tun. In meinem Beispiel repräsentiert die Spaltennummer die monatliche Nummer, die ich in Zelle G3 getippt habe.

<code>=VLOOKUP(E2,$A:$B,2,TRUE)</code>

Als ich die Eingabetaste drückte, sagte Excel mir richtig, dass Spieler C im dritten Monat fünf Tore erzielt hatte.

Jetzt kann ich jeden Wert in meiner Nachschlagetabelle ändern, um die Gesamtzahl eines jeden Spielers in jedem Monat zu finden.

countif und sumif

Wie Sie aus ihren Namen sehen können, zählen diese beiden Funktionen und die Summenwerte basierend auf den von Ihnen festgelegten Bedingungen. Alles, was nicht in Ihrem Zustand enthalten ist, wird nicht hinzugefügt oder gezählt, selbst wenn es sich in dem von Ihnen angegebenen Bereich befindet.

countif

countif zählt Zellen, die spezifische Bedingungen enthalten. Die Syntax ist

<code>=HLOOKUP(a,b,c,d)</code>

wobei a der Bereich ist, den Sie zählen möchten, und b ist die Bedingung für das Zählen.

In ähnlicher Weise werde ich Countifs verwenden, wenn ich mehrere Bedingungen einbeziehen möchte:

<code>=HLOOKUP(B5,$A:$I,2,TRUE)</code>

wobei a und b die erste Bereiche der Bereichsbedingung sind und c und d die zweite Bereichsbedingungspaarung (Sie kann bis zu 127 Paare haben).

Wenn ein Zustand ein Text oder ein logisches oder mathematisches Symbol ist, muss es in doppelte Zitate eingeschlossen sein.

In meiner folgenden Gehaltstabelle möchte ich die Anzahl der Personen berechnen, die über 40.000 GBP verdienen, und die Anzahl der Personen, die mehr als 1.000 Pfund Boni erhalten.

Um die Anzahl der Mitarbeiter mit einem Gehalt von mehr als 40.000 GBP zu berechnen, muss ich diese Formel in Zelle D8:

eingeben

<code>INDEX(a,b,c)</code>

wobei C2: C6 der Gehaltsbereich ist, "& gt; 40000" ist der Zustand.

Um die Anzahl der Servicepersonal zu berechnen, die einen Bonus von über £ 1.000 erhalten, werde ich Countifs verwenden, weil ich zwei Bedingungen habe.

<code>INDEX(B2:D8,4,2)</code>

B2: B6, "Services" Teil ist der erste Bereich bedingte Paarung, d2: d6, "& gt; 1000" ist der zweite.

Auch wenn ich in meiner Tabelle oben tausende Ziffern mit Kommas trenne, füge ich diese Kommas nicht in die Formel ein, da Kommas hier unterschiedliche Funktionen haben.

sumif

sumif summiert Zellen gemäß den von Ihnen festgelegten Bedingungen. Es funktioniert ähnlich wie Countif, enthält jedoch mehr Parameter in Klammern. Die Syntax ist

<code>MATCH(x,y,z)</code>

wobei a der Bereich der Zellen ist, den Sie vor Anfrage bewerten möchten, b ist die Bedingung für diese Bewertung (dies kann ein Wert oder eine Zellreferenz sein), c (optional) ist die Zelle, die hinzugefügt wird, wenn sie sich von a unterscheidet.

Diesmal müssen wir drei Dinge berechnen: das Gesamtgehalt für mehr als 40.000 GBP, das Gesamtgehalt für die Serviceabteilung und den Gesamtbonus für Mitarbeiter mit mehr als 35.000 GBP.

Um das Gesamtgehalt von mehr als £ 40.000 zu berechnen, muss ich die folgende Formel in Zelle D8:

eingeben

<code>=VLOOKUP(a,b,c,d)</code>

wobei C2: C6 das Gehalt in der Tabelle zitiert, "& gt; 40000" sagt Excel, nur Werte zu summen, die diesen Betrag überschreiten.

Als nächstes möchte ich das Gesamtgehalt der Serviceabteilung herausfinden. Also werde ich in Zelle C9

eingeben

<code>=VLOOKUP(E2,$A:$B,2,TRUE)</code>

wobei B2: B6 Zitate Abteilungsspalten, "Services" Sagen Sie Excel Ich suche nach Mitarbeitern in der Serviceabteilung, C2: C6 Tell excel über die Löhne dieser Mitarbeiter werden zusammengefasst.

Meine letzte Aufgabe war es, herauszufinden, wie viel Bonus die Mitarbeiter über 35.000 Pfund erhielten. In Zelle C10 werde ich

eingeben

<code>=HLOOKUP(a,b,c,d)</code>

wobei C2: C6 Excel anspricht, die Löhne zu bewerten, "& gt; 35000" sind die Bedingungen für diese Löhne, d2: d6 Sagen Sie Excel, sich zu treffen, um sich zu treffen, um sich zu treffen Die Bedingungen persönlicher Bonussumme.

Excel hat auch die Sumifs -Funktion, die dieselbe Prozedur ausführt, jedoch für mehrere Bedingungen. Es unterscheidet sich sehr von Sumifs Syntax:

<code>=HLOOKUP(B5,$A:$I,2,TRUE)</code>

wobei a der Bereich der Zellen ist, ist b der erste Bereich, der bewertet wird, c ist b , d und e sind die nächste Bereiche der Bereichsbedingung (Sie können bis zu 127 Paare haben).

Mit der obigen Tabelle möchte ich die Boni für Mitarbeiter zusammenfassen, die mehr als 45.000 Pfund verdienen. Hier ist die Formel, die ich eingeben werde:

<code>INDEX(a,b,c)</code>

---

Versuchen Sie nach dem Beherrschen der oben genannten Funktionen die Xlookup -Funktion, mit der einige der Nachteile von VLOOKUP gelöst werden sollen, indem die Werte links und rechts neben der Spalte Find Value gefunden werden, ohne die Daten neu zu ordnen.

Das obige ist der detaillierte Inhalt vonMeine 3 bevorzugten Möglichkeiten, Daten in Excel -Tabellen zu verwenden. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!