Praktischer Austausch von Excel-Kenntnissen: So finden Sie die Person Nummer eins mit mehreren Kriterien

Ranking ist einfach; aber wenn es mehrere Projektkategorien gibt und die Leistung möglicherweise gleich ist, wie kann man dann schnell herausfinden, welche Person in jeder Aktie den ersten Platz belegt? Dies erfordert den Abgleich mehrerer Bedingungen, um die gewünschte Nummer eins zu finden. Hier werden zwei Lösungen angeboten, aber keine ist perfekt. Können Sie sie verbessern?

Das jährliche Ehrungstreffen steht vor der Tür. Welche Kollegen werden dieses Jahr die Vertriebschampions? Lasst uns sie gemeinsam finden!

Die Verkaufsdaten verschiedener Elektrogeräte auf der E-Commerce-Plattform eines Unternehmens sind wie in der Abbildung dargestellt:

Die Daten enthalten nur die Auftragsnummer, den Produktnamen, den Namen des Geschäftspersonals und das Verkaufsvolumen Es ist notwendig, die Verkäufe jedes Produkttyps in dem unten gezeigten Format zu zählen.

Wenn ich diese Frage sehe, frage ich mich, welche Methoden Ihnen einfallen? Pivot-Tabelle, MAX-Funktion oder VLOOKUP...

Der Veteran empfiehlt jedem zwei Methoden: die erste ist Hilfsspalte + Formel; die zweite ist Pivot-Tabelle + Formel.

Methode 1: Hilfsspalte + Formel

Schritt 1: Hilfsspalte hinzufügen

Fassen Sie zunächst die Verkäufe jeder Person nach Produktnamen zusammen. Um nach Bedingungen zu summieren, wird hier für die Statistik die SUMIFS-Funktion verwendet. Obwohl mit einer Pivot-Tabelle das gleiche Ergebnis erzielt werden kann, kann die Pivot-Tabelle nicht den endgültigen gewünschten Effekt auf einmal erzielen, sodass es bequemer ist, Hilfsspalten zu verwenden.

Formel:

=SUMIFS(D:D,C:C,C2,B:B,B2)=SUMIFS(D:D,C:C,C2,B:B,B2)

公式格式：=SUMIFS(求和区域，条件区域1，条件1，条件区域2，条件2……)

SUMIFS是一个多条件求和函数，第一参数是要求和的数据所在的列，后面的参数两个一组，构成一组条件。在这个例子中，第一组条件是业务人员，因此条件区域1就是C列，条件1是C2；第二组条件是产品名称，条件区域2就是B列，条件2是B2。

有了辅助列，下一步就可以找到每个品类中最高的销售额是多少了。这里需要注意的是，统计结果表里销售冠军姓名在前销售额在后。实际统计时并非必须按这样的先后顺序统计，哪个方便我们就先统计哪个。

第2步：统计最高销售额

通常一说最大值，首先想到的就是MAX函数。这个函数的用法和SUM很像，只需要给出一组数或者一个数据区域，就能得到这一组数中最大的值。

在今天这个例子中，因为我们要得到的是同一个品类中的最大值，也就是按条件统计最大值，所以无法直接用MAX函数得到结果，

这类按条件统计最大值的有固定的套路公式：

=MAX(数据区域*（条件区域1=条件1）*（条件区域2=条件2）……)

本例只有一个条件，就是产品名称，因此公式为：=MAX($E:$E0*($B:$B0=G2))

 使用这个公式套路需要注意三个地方：

（1）范围要准确，不建议选择整列作为计算区域；

（2）公式涉及数组运算，在输入公式后需要按Ctrl+Shift+Enter键，按键后会自动在公式中添加一对大括号；

（3）因为公式要下拉，为了避免计算区域发生改变，所以涉及到的范围需要使用绝对引用。

这个公式具体原理涉及到逻辑值和数组的计算原理，以后我们会专门进行讲解。

到这一步，再找出每类产品下最高销售额对应的业务人员就完成了全部的统计。

第3步：找出冠军人员

根据销售额查人员，这实际上就是一个查找引用，使用VLOOKUP或者INDEX等引用函数都可以完成。

接近成功，现在要削苹果了。削苹果的特点就是细、准。

第一个细节：数据源中的累计销售额位于业务人员的右侧。

如果用VLOOKUP，我们就得使用反向查找的套路，公式相对还是比较复杂。如果用INDEX与MATCH组合倒是可以，公式也不难：

=INDEX($C:$C0,MATCH(I2,$E:$E0,0))

Formelformat: =SUMIFS(Summationsbereich, Bedingungsbereich 1, Bedingung 1, Bedingungsbereich 2, Bedingung 2...)

SUMIFS ist eine Summationsfunktion mit mehreren Bedingungen. Der erste Parameter ist die Spalte, in der sich die zu summierenden Daten befinden, und die folgenden Parameter werden paarweise gruppiert, um einen Satz zu bilden der Bedingungen. In diesem Beispiel handelt es sich bei dem ersten Satz von Bedingungen um das Geschäftspersonal, also ist Bedingungsbereich 1 Spalte C, Bedingung 1 ist C2; der zweite Satz von Bedingungen ist Produktname, Bedingungsbereich 2 ist Spalte B und Bedingung 2 ist B2. Mit der Hilfsspalte gilt es im nächsten Schritt herauszufinden, welches Umsatzvolumen in jeder Kategorie am höchsten ist. Hierbei ist zu beachten, dass in der statistischen Ergebnistabelle der Name des Verkaufschampions an erster Stelle und die Verkaufsmenge an letzter Stelle steht. In tatsächlichen Statistiken ist es nicht notwendig, in dieser Reihenfolge zu zählen. Wir zählen zuerst, was bequemer ist.

🎜🎜🎜Schritt 2: Zählen Sie die höchsten Umsätze🎜🎜🎜🎜Wenn man vom Maximalwert spricht, fällt einem normalerweise als Erstes die MAX-Funktion ein. Die Verwendung dieser Funktion ist der von SUM sehr ähnlich. Sie müssen lediglich einen Zahlensatz oder einen Datenbereich angeben, um den Maximalwert in diesem Zahlensatz zu erhalten. 🎜🎜Da wir im heutigen Beispiel den Maximalwert in derselben Kategorie erhalten möchten, d Der auf Bedingungen basierende Maximalwert umfasst die feste Routineformel: 🎜🎜=MAX(data area*(condition area 1=condition 1)*(condition area 2=condition 2)...)🎜🎜This Beispiel hat nur eine Bedingung, nämlich den Produktnamen, daher lautet die Formel: =MAX($E$2:$E$750*($B$2:$B$750=G2))🎜🎜🎜🎜 Sie müssen auf drei achten Dinge bei Verwendung dieser Formel: 🎜🎜 (1) Der Bereich muss genau sein. Es wird nicht empfohlen, die gesamte Spalte als Berechnungsbereich auszuwählen. 🎜🎜(2) Die Formel umfasst Array-Operationen. Sie müssen Strg + Umschalt drücken +Enter-Taste nach Eingabe der Formel. Nach dem Drücken der Taste werden automatisch zwei geschweifte Klammern zur Formel hinzugefügt. 🎜🎜(3) Da die Formel nach unten gezogen werden muss, um zu verhindern, dass sich der Berechnungsbereich ändert. Für die beteiligten Bereiche müssen absolute Bezüge verwendet werden. 🎜🎜Das spezifische Prinzip dieser Formel beinhaltet das Berechnungsprinzip logischer Werte und Arrays, das wir in Zukunft genauer erläutern werden. 🎜🎜In diesem Schritt ermitteln Sie das Geschäftspersonal mit den höchsten Umsätzen für jeden Produkttyp und vervollständigen alle Statistiken. 🎜🎜🎜🎜Schritt 3: Finden Sie das Champion-Personal 🎜🎜🎜🎜Überprüfen Sie das Personal anhand des Umsatzes. Hierbei handelt es sich eigentlich um eine Suche nach Referenzen, die mithilfe von Referenzfunktionen wie SVERWEIS oder INDEX durchgeführt werden kann. 🎜🎜Fast gelungen, jetzt geht es ans Schälen des Apfels. Das Schälen von Äpfeln zeichnet sich durch Dünnheit und Genauigkeit aus. 🎜🎜🎜Erstes Detail: Die kumulierten Umsätze in der Datenquelle stehen rechts neben der Geschäftsperson. 🎜🎜🎜Wenn wir VLOOKUP verwenden, müssen wir die umgekehrte Suchroutine verwenden, und die Formel ist relativ kompliziert. Es ist möglich, INDEX und MATCH zu kombinieren, und die Formel ist nicht schwierig: 🎜🎜=INDEX($C$2:$C$750,MATCH(I2,$E$2:$E$750,0))🎜 🎜🎜🎜🎜🎜Zweites Detail: Der maximale Umsatz kann gleich sein. 🎜🎜

Die Kombination dieser beiden Funktionen kann als klassische Partnerschaft bezeichnet werden. Aber es gibt noch ein Detail: Wir können nicht ausschließen, dass die Höchstverkäufe der beiden Produktkategorien gleich sind. Um mögliche Suchfehler zu vermeiden, wenn die maximalen Umsätze verschiedener Kategorien gleich sind, müssen wir einen Abgleich auf der Grundlage der beiden Bedingungen Produktname und Umsatz durchführen. Die Formel lautet:

=INDEX($C$2:$C $750,MATCH(G2&I2,$B$2:$B$750&$E$2:$E$750,0))=INDEX($C:$C0,MATCH(G2&I2,$B:$B0&$E:$E0,0))

多条件匹配常用套路之一就是用连接符号&把多个条件串在一起组成一个新的条件来查询，当然查询区域也需要用&串在一起。

当然，像这种多条件查找，并且不愿意利用Vlookup反相查找的话，也可以用LOOKUP函数来完成：

=LOOKUP(1,0/(($E:$E0=I2)*($B:$B0=G2)),$C:$C0)

多条件匹配常用套路之二就是把多个条件各自用等号＝与查找区域建立起表达式，然后把表达式进行相乘。

公式的套路是：=LOOKUP(1,0/(条件区域=条件),目标区域)，如果是多个条件的话，可以直接将套路升级为：=LOOKUP(1,0/((条件区域1=条件1)*(条件区域2=条件2)*(条件区域3=条件3)……,目标区域)

方法二：透视表+公式

第1步：统计业绩并排名

将产品名称和业务人员拖入行区域，销售额拖两次到值区域，然后按照部落窝教育去年的教程《嘿，鼠标拖两下一次搞定业绩统计和排名！》设置销售额2的值显示方式为“降序排列”，基本字段为“业务人员”获得按产品分类的销售业绩统计和排名。

第2步，整理透视表

单击透视表，点击“设计”选项卡“布局”选项组“报表布局”下拉菜单中的“以表格形式显示”和“重复所有项目标签”命令。接着在透视表上右击，选择“分类汇总“业务人员””，取消表格中的分类汇总项。表格变成下方模样：

第３步，输入公式获取冠军姓名和业绩

在G2单元格中输入公式：

=INDEX(L:L0,MATCH($G2&1,$K:$K0&$N:$N0,0))

Eine der gängigen Routinen für den Mehrfachbedingungsabgleich ist die Verwendung des Verbindungssymbols & zur Zeichenfolge Mehrere Bedingungen zusammen bilden eine neue abzufragende Bedingung. Natürlich muss der Abfragebereich auch mit & aneinandergereiht werden.

Natürlich, wie Diese Art der Suche mit mehreren Bedingungen, und wenn Sie nicht bereit sind, Vlookup zum Umkehren der Suche zu verwenden, können Sie auch die LOOKUP-Funktion verwenden, um Folgendes zu vervollständigen:

=LOOKUP(1,0/(($E$2 :$E$750=I2)*( $B$2:$B$750=G2)),$C$2:$C$750)

Die zweite übliche Routine für den Mehrfachbedingungsabgleich ist die Verwendung des Gleichheitszeichens = für mehrere Bedingungen, um einen Ausdruck mit dem Suchbereich zu erstellen und dann die Ausdrücke zu multiplizieren.

Formelroutine Es lautet: =LOOKUP(1,0/(condition area=condition), target area) Wenn mehrere Bedingungen vorhanden sind, können Sie die Routine direkt aktualisieren auf: =LOOKUP(1,0 /((Bedingter Bereich 1=Bedingung 1)*(Bedingter Bereich 2=Bedingung 2)*(Bedingter Bereich 3=Bedingung 3)..., Zielbereich)

Methode 2: Pivot Tabelle +Formel

Schritt 1: Statistik und Ranking

Produktnamen und Unternehmen platzieren Drag Ziehen Sie die Person in den Zeilenbereich, ziehen Sie das Verkaufsvolumen zweimal in den Wertebereich und stellen Sie dann den Wertanzeigemodus von Verkaufsvolumen 2 gemäß dem Tutorial auf „Absteigende Reihenfolge“ ein. „Hey, ziehen Sie die Maus zweimal, um die Leistungsstatistiken zu erhalten.“ Rankings auf einmal!“ , das Basisfeld ist „Geschäftspersonal“, um Verkaufsleistungsstatistiken und Rankings nach Produktkategorie zu erhalten

🎜🎜Schritt 2, organisieren Sie die perspektivische Tabelle🎜🎜Klicken Sie auf die Pivot-Tabelle und klicken Sie im Dropdown-Menü „Berichtslayout“ im Dropdown-Menü „Layout“ auf die Befehle „Als Tabelle anzeigen“ und „Alle Elementbezeichnungen wiederholen“. " Optionsgruppe auf der Registerkarte „Design“. Klicken Sie dann mit der rechten Maustaste auf die Pivot-Tabelle, wählen Sie „Unterklassifizierung und Zusammenfassung „Geschäftspersonal““ und löschen Sie die Unterklassifizierungs- und Zusammenfassungselemente in der Tabelle. Die Tabelle sieht wie folgt aus: 🎜 🎜🎜🎜Schritt 3: Geben Sie die Formel ein, um den Namen und die Leistung des Champions zu erhalten🎜🎜Geben Sie ein Formel in Zelle G2: 🎜🎜=INDEX(L$2:L$200,MATCH( $G2&1,$K$2:$K$200&$N$2:$N$200,0))🎜🎜After Um die Eingabe zu beenden, drücken Sie Strg+Umschalt+Eingabetaste. 🎜🎜🎜🎜🎜Klicken Sie dann mit der rechten Maustaste und lassen Sie die Formel fallen. 🎜🎜🎜🎜🎜 Im heutigen Tutorial haben wir mehrere Funktionen gelernt, nämlich SUMIFS, MAX, INDEX, MATCH, LOOKUP, und auch zwei Routinen für den Multi-Bedingungs-Abgleich gelernt, die Sie direkt verwenden können. 🎜🎜Die heutige Lösung ist jedoch unvollkommen. Obwohl wir uns im Tutorial darum bemüht haben, „in den Apfel zu pinkeln“ und auf die Details zu achten, haben wir dennoch ein sehr wichtiges Detail übersehen – die höchsten Umsätze ähnlicher Produkte könnten gleich sein. 🎜🎜Verwandte Lernempfehlungen: 🎜Excel-Tutorial🎜🎜

Das obige ist der detaillierte Inhalt vonPraktischer Austausch von Excel-Kenntnissen: So finden Sie die Person Nummer eins mit mehreren Kriterien. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!