Praktischer Austausch von Excel-Kenntnissen: Die 10 von Profis am häufigsten verwendeten Formeln

Dieser Artikel hat die 10 am häufigsten verwendeten Excel-Formeln für alle am Arbeitsplatz zusammengestellt. Ich hoffe, er kann Ihnen bei der Lösung Ihrer Probleme helfen.

Es ist wirklich ineffizient, wenn Sie nicht wissen, wie Formelfunktionen zu verwenden, aber es gibt so viele Formeln und Funktionen, dass es unmöglich ist, sie alle auf einmal zu lernen. Dies ist ein Dilemma, mit dem viele Profis konfrontiert sind.

Heute habe ich zehn häufig verwendete Formeln zusammengefasst, um sie mit Ihnen zu teilen. Ich glaube, dass Sie auch am Arbeitsplatz erfolgreich sein können, wenn Sie diese zehn Formeln lernen.

Formel 1: Bedingtes Zählen

Bedingtes Zählen ist in Excel-Anwendungen sehr verbreitet. Beispielsweise ist die Anzahl der Frauen in der Statistikerliste ein typischer Vertreter des bedingten Zählens.

Bedingtes Zählen erfordert die Verwendung der COUNTIF-Funktion. Die Funktionsstruktur ist =COUNTIF (statistischer Bereich, Bedingung). In diesem Beispiel ist die erste Formel =COUNTIF(B:). B,G2 ), B:B ist der statistische Bereich, G2 ist die Bedingung und das Formelergebnis zeigt an, dass in Spalte B 14 „weibliche“ Daten vorhanden sind. =COUNTIF(统计区域,条件)，在本例第一个公式=COUNTIF(B:B,G2)中，B:B就是统计区域，G2是条件，公式结果表示B列中为“女”的数据有14个。

第二参数条件可以不使用单元格引用，直接用具体内容作为条件，当条件为文本时，需要在条件两边添加英文状态的双引号，比如第二个公式=COUNTIF(B:B,"女")就是如此。 

公式2：快速标注重复数据

平时的工作中也经常会遇到标注重复值的问题，例如在一份销售明细表中，对重复的销售人员姓名进行标注。

首先使用公式=COUNTIF(A:A,A2)计算出每个姓名出现的次数，当结果大于1就表示姓名重复，进而使用IF函数得到最终的结果。

公式为：=IF(COUNTIF(A:A,A2)=1,"","重复")，结果如图所示。

判断姓名是否重复还有一种情况：第一次出现不算重复，从第二次起才算重复。

遇到这种情况，只需要修改COUNTIF函数的条件区域即可，公式为：=IF(COUNTIF($A:A2,A2)=1,"","重复")，结果如图所示。

公式3：多条件计数

如果要对多个条件进行统计，就要用到COUNTIFS函数，例如需要统计出男性中学历为本科的人数。

COUNTIFS的函数结构为=COUNTIFS(数据区域1,条件1,数据区域2,条件2,……)，最多可以有127组条件。

本例的第一个公式=COUNTIFS(B:B,G2,C:C,G3)中，共有两组条件，B列是对性别进行判断，C列是对学历进行判断。

同样，条件可以引用单元格，也可以直接使用具体内容，这与COUNTIF函数完全一致。

公式4：条件求和

除了条件计数，条件求和的应用也很广泛，例如在销售明细表中统计出电视销量的合计。

按条件求和时需要用到SUMIF函数，函数的结构为=SUMIF(条件区域,条件,求和区域)，在本例中，条件区域是B列，求和区域是C列，条件可以使用单元格也可以直接使用具体内容，要统计出电视销量的合计就有两种写法：=SUMIF(B:B,"电视",C:C)和=SUMIF(B:B,B2,C:C)。

公式5：多条件求和

有条件求和就会有多条件求和，例如要按照销售人员和商品名称两个条件进行求和时，就要用到多条件求和函数SUMIFS了。

SUMIFS函数的结构为=SUMIFS(求和区域,条件区域1,条件1,条件区域2,条件2,……)，在本例中，求和区域是D列的销售数量，第一个条件区域是B列的销售人员，第二个条件区域是C列的商品名称，因此最终的公式为：

=SUMIFS(D:D,B:B,"沈伊杰",C:C,"壁挂空调")

Die zweite Parameterbedingung kann keine Zellreferenzen verwenden, sondern direkt bestimmte Inhalte als Bedingung verwenden. Wenn es sich bei der Bedingung um Text handelt, müssen Sie auf beiden Seiten der Bedingung englische Anführungszeichen hinzufügen, z. B. die zweite Formel = COUNTIF(B: B,"female")Das ist es. 🎜🎜🎜Formel 2: Wiederholte Daten schnell markieren🎜🎜🎜🎜Bei unserer täglichen Arbeit stoßen wir häufig auf das Problem, wiederholte Werte zu markieren, beispielsweise in einer Verkaufsdetailtabelle , markieren Sie doppelte Verkäufernamen. 🎜🎜 🎜🎜Verwenden Sie zunächst die Formel =COUNTIF(A:A,A2), um zu berechnen, wie oft jeder Name vorkommt. Wenn das Ergebnis größer als 1 ist, bedeutet dies, dass der Name wiederholt wird, und verwenden Sie dann die IF-Funktion, um das Endergebnis zu erhalten. 🎜🎜Die Formel lautet: =IF(COUNTIF(A:A,A2)=1,"","Repeat") und das Ergebnis ist wie in der Abbildung dargestellt. 🎜🎜 🎜🎜Es gibt eine andere Möglichkeit festzustellen, ob ein Name wiederholt wird: Das erste Vorkommen gilt nicht als Wiederholung, sondern nur das zweite Vorkommen als Wiederholung. 🎜🎜In diesem Fall müssen Sie nur den bedingten Bereich der ZÄHLENWENN-Funktion ändern. Die Formel lautet: =IF(COUNTIF($A$1:A2,A2)=1,"","Repeat" ) code>, das Ergebnis ist wie in der Abbildung gezeigt. 🎜🎜<img border="0" style="max-width:90%" style="max-width:90%" src="https://img.php.cn/upload/article/000/000/024/c45ec2b9145c73bfae5a34b18f7d3137-3.png" alt="Praktischer Austausch von Excel-Kenntnissen: Die 10 von Profis am häufigsten verwendeten Formeln" > 🎜🎜🎜<span style="font-size: 18px;">Formel 3: Zählen mehrerer Bedingungen 🎜🎜🎜🎜Wenn Sie mehrere Bedingungen zählen möchten, müssen Sie beispielsweise die ZÄHLENWENN-Funktion verwenden die Zahl der Männer mit Hochschulabschluss. 🎜🎜<img border="0" style="max-width:90%" style="max-width:90%" src="https://img.php.cn/upload/article/000/000/024/c45ec2b9145c73bfae5a34b18f7d3137-4.png" alt="Praktischer Austausch von Excel-Kenntnissen: Die 10 von Profis am häufigsten verwendeten Formeln" > 🎜🎜Die Funktionsstruktur von COUNTIFS ist =COUNTIFS(Datenbereich 1, Bedingung 1, Datenbereich 2, Bedingung 2,...) und es können bis zu 127 Sätze von Bedingungen vorhanden sein. 🎜🎜In der ersten Formel dieses Beispiels =COUNTIFS(B:B,G2,C:C,G3) gibt es zwei Sätze von Bedingungen, um das Geschlecht zu beurteilen, und Spalte C, um den Bildungshintergrund zu beurteilen. 🎜🎜Ähnlich können sich Bedingungen auf Zellen beziehen oder bestimmte Inhalte direkt verwenden, was genau das Gleiche ist wie die ZÄHLENWENN-Funktion. 🎜🎜🎜<span style="font-size: 18px;">Formel 4: Bedingte Summierung🎜🎜🎜🎜Neben der bedingten Zählung wird auch die bedingte Summierung häufig verwendet, beispielsweise zum Zählen von TV-Verkäufen in der Gesamtsumme der Verkaufsdetails. 🎜🎜<img border="0" style="max-width:90%" style="max-width:90%" src="https://img.php.cn/upload/article/000/000/024/4227d766cdfc201c232b681ae1d6f6d5-5.png" alt="Praktischer Austausch von Excel-Kenntnissen: Die 10 von Profis am häufigsten verwendeten Formeln" > 🎜🎜Beim Summieren nach Bedingungen müssen Sie die Funktion SUMIF verwenden. Die Struktur der Funktion ist =SUMIF (Bedingungsbereich, Bedingung, Summierungsbereich). In diesem Beispiel ist der Bedingungsbereich Spalte B und der Summierungsbereich ist Spalte C. Die Bedingungen können auch direkt in Zellen verwendet werden, um bestimmte Inhalte zu verwenden. Um die Gesamtzahl der TV-Verkäufe zu berechnen, gibt es zwei Möglichkeiten: =SUMIF(B:B,"TV",C:C) und <code>=SUMIF (B:B,B2,C:C). 🎜🎜🎜Formel 5: Summe mit mehreren Bedingungen 🎜🎜🎜🎜Wenn es eine bedingte Summe gibt, wird es beispielsweise eine Summe mit mehreren Bedingungen geben, die auf den beiden basiert Bedingungen des Verkäufers und des Produktnamens. Bei der Summierung wird die Summationsfunktion mit mehreren Bedingungen SUMIFS verwendet. 🎜🎜 🎜🎜Die Struktur der SUMIFS-Funktion ist =SUMIFS (Summenbereich, Bedingungsbereich 1, Bedingung 1, Bedingungsbereich 2, Bedingung 2,... In diesem Beispiel ist der Summationsbereich die Verkaufsmenge in Spalte D). Der erste Bedingungsbereich ist der Verkäufer in Spalte B und der zweite Bedingungsbereich ist der Produktname in Spalte C. Die endgültige Formel lautet also: 🎜🎜=SUMIFS(D:D,B:B,"Shen Yijie" ,C:C ,„Wandmontierte Klimaanlage“)🎜

Es ist zu beachten, dass sich die Position des Summationsbereichs von SUMIFS von der von SUMIF unterscheidet. Der Summationsbereich von SUMIFS befindet sich im ersten Parameter, während sich der Summationsbereich von SUMIF im dritten Parameter befindet . Lass dich nicht verwirren!

Formel 6: Berechnen Sie das Geburtsdatum anhand der ID-Nummer

Um das Geburtsdatum anhand der ID-Nummer zu ermitteln, muss dieses Problem denjenigen, die in Personalverwaltungspositionen arbeiten, und der Formel bekannt sein ist relativ einfach:

=TEXT(MID(A2,7,8),"0-00-00") erhält das erforderliche Ergebnis, wie in der Abbildung gezeigt:

Um das Prinzip dieser Formel zu verstehen , Sie müssen zuerst den Personalausweis kennen. Es gibt einige Regeln für die Nummer. Die derzeit verwendeten Personalausweise sind grundsätzlich 18-stellig, und die acht Ziffern ab der siebten Ziffer stellen das Geburtsdatum dar.

Diese Formel umfasst zunächst zwei Funktionen. Die MID-Funktion hat drei Parameter: =MID (wo extrahiert werden soll, mit welchem ​​Wort begonnen werden soll und wie viele Wörter extrahiert werden sollen).

MID(A2,7,8) bedeutet, dass ab der siebten Zahl der Zelle A2 acht Ziffern abgefangen werden. Der Effekt ist wie in der Abbildung dargestellt:

Nachdem das Geburtsdatum extrahiert wurde, ist dies kein Effekt Zu diesem Zeitpunkt benötigen wir die Funktion TEXT. Die TEXT-Funktion ist =TEXT (der zu verarbeitende Inhalt, in diesem Beispiel der zu verarbeitende Inhalt). Verarbeitet wird der MID-Funktionsteil und das Anzeigeformat ist „0-00-00“. Natürlich können Sie ihn auch im Format „0年00月00日“ anzeigen. Die Formel sollte in =TEXT(MID( geändert werden. A2,7,8),"0年00月00日" " Y")

Hier wird eine versteckte Excel-Funktion DATEDIF verwendet. Die Funktion erfordert drei Parameter und die Grundstruktur ist =DATEDIF (Startdatum, Frist, Berechnungsmethode). In diesem Beispiel ist das Startdatum das Geburtsdatum, wobei B2 als erster Parameter verwendet wird; die Frist ist heute, wobei die Funktion TODAY() als zweiter Parameter verwendet wird; die Berechnungsmethode wird nach Jahr berechnet, wobei „Y“ verwendet wird; als dritter Parameter. Wenn Sie das Alter direkt anhand der ID-Nummer berechnen müssen, kann die Formel wie folgt geschrieben werden:

=DATEDIF(TEXT(MID(A2,7,8),"0-00-00"),TODAY(), „Y“ )

Formel 8: Je nach Intervall werden unterschiedliche Ergebnisse erzielt

Diese Art von Problem tritt hauptsächlich bei der Leistungsbeurteilung auf. Beispielsweise führt das Unternehmen eine Leistungsbeurteilung von Mitarbeitern durch und muss diese ermitteln Die Belohnungsstufe basiert auf den Bewertungsergebnissen. Die Bewertungsregeln lauten: Eine Punktzahl von 50 oder weniger ist E, eine Punktzahl von 50-65 (einschließlich) ist D, eine Punktzahl von 65-75 (einschließlich) ist C, eine Punktzahl von 75-90 (einschließlich) ist B, und eine Punktzahl von 90 oder mehr ist A.

Sie können die Formel =LOOKUP(E2,{0;50;65;75;90},{"E";"D";"C";"B";"A"}) verwenden, um die Belohnungen zu erhalten Für jede Mitarbeiterebene ist das Ergebnis wie in der Abbildung dargestellt:

Es ist schwierig, das Prinzip dieser Formel zu erklären. Sie können sich auf die vorherigen Tutorials zur LOOKUP-Funktion beziehen. Um diese Art von Problem zu lösen, reicht es tatsächlich aus, sich an die Routine zu erinnern: LOOKUP gibt die entsprechenden Ergebnisse nach Intervall zurück: =LOOKUP(score, {Liste der unteren Grenzwerte}, {Liste der Belohnungsstufen}) und Punkte verwenden Die Werte zwischen den unteren Grenzwerten werden durch Semikolons getrennt, und die Belohnungsstufen werden ebenfalls durch Semikolons getrennt.

Sie können auch die entsprechende Beziehung zwischen der Untergrenze der Punktzahl und der Belohnungsstufe in die Tabelle eingeben. Die Formel kann als =LOOKUP(E2,$I$2:$J$6) geändert werden, und das Ergebnis ist wie gezeigt in der Abbildung.

Formel 9: Daten zum Abgleich einzelner Bedingungen

Was können Sie tun, wenn Sie am Arbeitsplatz dominieren möchten, wenn Sie nicht wissen, wie Sie einen Abgleich durchführen sollen? Wie kann ich einen Einzelbedingungsabgleich ohne SVERWEIS durchführen?

Die Grundstruktur der VLOOKUP-Funktion ist =VLOOKUP (wonach gesucht werden soll, wo gesucht werden soll, nach welcher Spalte gesucht werden soll, wie man danach suchen soll, um zum Beispiel den höchsten Bildungsabschluss anhand des Namens zu finden). Sie können die Formel =VLOOKUP(G2,B:E,4,0) verwenden, um das erforderliche Ergebnis wie im Bild gezeigt zu erhalten:

Es gibt zwei wichtige Punkte, die Sie kennen müssen, wenn Sie diese Funktion verwenden: ①Der gesuchte Inhalt muss sich in der ersten Spalte des Suchbereichs befinden. Wenn Sie beispielsweise nach Namen suchen, beginnt der Suchbereich mit Spalte B statt mit Spalte A.

②Die Spaltennummer bezieht sich auf die Spalte im Suchbereich und nicht auf die Spalte in der Tabelle. Wenn Sie beispielsweise das höchste Bildungsniveau finden möchten, befindet es sich in der 4. Spalte des Suchbereichs, nicht auf der Spaltennummer 5 der Tisch.

Formel 10: Daten mit mehreren Bedingungen abgleichen

Sie werden wirklich unbesiegbar sein, wenn Sie lernen, Daten mit mehreren Bedingungen abzugleichen!

Geben Sie ein Beispiel für den Abgleich der Verkaufsmenge anhand von zwei Bedingungen: Name und Produktname, wie in der Abbildung gezeigt:

Die Formel lautet =LOOKUP(1,0/(($A:$A=E2)*($B:$B=F2)),$C:$C)

Die Routine zur Verwendung der LOOKUP-Funktion für den Abgleich mit mehreren Bedingungen lautet: = LOOKUP(1,0 /((Suchbereich 1 = Suchwert 1) * (Suchbereich 2 = Suchwert 2) *... * (Suchbereich n = Suchwert n)), Ergebnisbereich), sollte beachtet werden dass zwischen mehreren Suchbedingungen eine Multiplikationsbeziehung besteht und diese in denselben Klammersatz gesetzt werden müssen wie der Nenner von 0/.

Okay, die zehn am häufigsten verwendeten Formeln werden hier vorgestellt. Einmal verwendet, können Sie den Arbeitsplatz wirklich dominieren!

Verwandte Lernempfehlungen: Excel-Tutorial

Das obige ist der detaillierte Inhalt vonPraktischer Austausch von Excel-Kenntnissen: Die 10 von Profis am häufigsten verwendeten Formeln. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!