Partage pratique de compétences Excel : Comment trouver la personne numéro un avec plusieurs critères

Le classement est simple ; mais s'il existe plusieurs catégories de projets et qu'il peut y avoir la même performance, comment connaître rapidement la personne qui se classe première dans chaque partage ? Cela nécessite de faire correspondre plusieurs conditions pour trouver le numéro un souhaité. Deux solutions sont proposées ici, mais aucune n’est parfaite. Pouvez-vous les améliorer ?

La réunion annuelle de félicitations est sur le point de commencer. Quels collègues deviendront les champions des ventes cette année ? Trouvons-les ensemble !

Les données de vente de différents types d'appareils électriques sur la plateforme de commerce électronique d'une entreprise sont présentées dans la figure :

Les données ne contiennent désormais que le numéro de commande, le nom du produit, le nom du personnel de l'entreprise et le volume des ventes. il faut compter les ventes de chaque type de produit dans le format indiqué ci-dessous champion.

En voyant cette question, je me demande à quelles méthodes vous pouvez penser ? Tableau croisé dynamique, fonction MAX ou RECHERCHEV...

Le vétéran recommande à tout le monde deux méthodes : la première est colonne auxiliaire + formule ; la seconde est tableau croisé dynamique + formule ;

Méthode 1 : Colonne auxiliaire + Formule

Étape 1 : Ajouter une colonne auxiliaire

Tout d'abord, résumez les ventes de chaque personne par nom de produit. Pour additionner selon les conditions, la fonction SUMIFS est utilisée ici à des fins statistiques. Bien que le même résultat puisse être obtenu en utilisant un tableau croisé dynamique, celui-ci ne peut pas obtenir l'effet final souhaité en une seule fois, il est donc plus pratique d'utiliser des colonnes auxiliaires.

Formule :

=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))

Format de formule : =SUMIFS(zone de sommation, zone de condition 1, condition 1, zone de condition 2, condition 2...)

SUMIFS est une fonction de sommation multi-conditions. Le premier paramètre est la colonne où se trouvent les données à additionner, et les paramètres suivants sont regroupés par paires pour former un ensemble. de conditions. Dans cet exemple, le premier ensemble de conditions concerne le personnel de l'entreprise, donc la zone de conditions 1 est la colonne C, la condition 1 est C2 ; le deuxième ensemble de conditions est le nom du produit, la zone de conditions 2 est la colonne B et la condition 2 est B2 ; Avec la colonne auxiliaire, l'étape suivante consiste à connaître quel est le volume de ventes le plus élevé dans chaque catégorie. Ce qu'il faut noter ici, c'est que dans le tableau des résultats statistiques, le nom du champion des ventes vient en premier et le volume des ventes en dernier. Dans les statistiques réelles, il n’est pas nécessaire de compter dans cet ordre. Nous compterons en premier selon ce qui convient le mieux.

🎜🎜🎜Étape 2 : Comptez les ventes les plus élevées🎜🎜🎜🎜Habituellement, lorsqu'on parle de valeur maximale, la première chose qui vient à l'esprit est la fonction MAX. L'utilisation de cette fonction est très similaire à SUM. Il vous suffit de donner un ensemble de nombres ou une zone de données pour obtenir la valeur maximale de cet ensemble de nombres. 🎜🎜Dans l'exemple d'aujourd'hui, parce que ce que nous voulons obtenir est la valeur maximale dans la même catégorie, c'est-à-dire en comptant la valeur maximale en fonction des conditions, nous ne pouvons pas utiliser directement la fonction MAX pour obtenir le résultat 🎜🎜Ce type de comptage. la valeur maximale basée sur les conditions inclut la formule de routine fixe : 🎜🎜=MAX(zone de données*(zone de condition 1=condition 1)*(zone de condition 2=condition 2)...)🎜🎜Ceci L'exemple n'a qu'une seule condition, qui est le nom du produit, donc la formule est : =MAX($E$2:$E$750*($B$2:$B$750=G2))🎜🎜🎜🎜 Vous devez faire attention à trois choses lorsque vous utilisez cette formule : 🎜🎜 (1) La plage doit être précise, il n'est pas recommandé de sélectionner la colonne entière comme zone de calcul ; 🎜🎜 (2) La formule implique des opérations sur les tableaux. appuyez sur la touche Ctrl+Shift+Enter. Après avoir appuyé sur la touche, une paire d'accolades sera automatiquement ajoutée à la formule 🎜🎜(3 ) Parce que la formule doit être déroulée vers le bas, afin d'éviter que la zone de calcul ne change ; , des références absolues doivent être utilisées pour les plages concernées. 🎜🎜Le principe spécifique de cette formule implique le principe de calcul de valeurs logiques et de tableaux, que nous expliquerons spécifiquement dans le futur. 🎜🎜A cette étape, découvrez le personnel de l'entreprise correspondant aux ventes les plus élevées de chaque type de produit et complétez toutes les statistiques. 🎜🎜🎜🎜Étape 3 : Trouver le personnel champion 🎜🎜🎜🎜Vérifier le personnel en fonction des ventes Il s'agit en fait d'une recherche de références, qui peut être complétée à l'aide de fonctions de référence telles que RECHERCHEV ou INDEX. 🎜🎜C'est presque réussi, il est maintenant temps d'éplucher la pomme. La caractéristique de l’épluchage des pommes est la finesse et la précision. 🎜🎜🎜Premier détail : Les ventes cumulées dans la source de données sont à droite du commerçant. 🎜🎜🎜Si nous utilisons RECHERCHEV, nous devons utiliser la routine de recherche inversée, et la formule est relativement compliquée. Il est possible de combiner INDEX et MATCH, et la formule n'est pas difficile : 🎜🎜=INDEX($C$2:$C$750,MATCH(I2,$E$2:$E$750,0))🎜 🎜🎜🎜🎜🎜Deuxième détail : Les ventes maximales peuvent être les mêmes. 🎜🎜

La combinaison de ces deux fonctions peut être qualifiée de partenariat classique. Mais il reste encore un détail : on ne peut pas exclure que les ventes maximales des deux catégories de produits soient les mêmes. Afin d'éviter d'éventuelles erreurs de recherche où les ventes maximales des différentes catégories sont les mêmes, nous devons faire correspondre en fonction des deux conditions de nom du produit et de ventes. La formule devient :

=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))

L'une des routines courantes pour la correspondance multi-conditions consiste à utiliser le symbole de connexion & à la chaîne plusieurs conditions ensemble Formez une nouvelle condition à interroger. Bien entendu, la zone de requête doit également être liée avec &.

Bien sûr, comme ce type de recherche multi-conditions, et si vous ne souhaitez pas utiliser Vlookup pour inverser la recherche, vous pouvez également utiliser la fonction LOOKUP pour compléter :

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

La deuxième routine courante pour la correspondance multi-conditions consiste à utiliser le signe égal = pour plusieurs conditions, créez une expression avec la zone de recherche, puis multipliez les expressions.

routine de formule Il est : =LOOKUP(1,0/(condition zone=condition), cible zone) S'il existe plusieurs conditions, vous pouvez directement mettre à niveau la routine vers : =LOOKUP(1,0). /((Zone conditionnelle 1=Condition 1)*(Zone conditionnelle 2=Condition 2)*(Zone conditionnelle 3=Condition 3)..., zone cible)

Méthode 2 : Pivot tableau + Formule

Étape 1 : Statistiques et classement

Placez le nom du produit et l'entreprise Faites glisser la personne dans la zone de ligne, faites glisser le volume des ventes deux fois vers la zone de valeur, puis définissez le mode d'affichage de la valeur du volume des ventes 2 sur « ordre décroissant » selon le didacticiel de l'année dernière de Tribe Nest Education « Hé, faites glisser la souris deux fois pour obtenez les statistiques de performances et les classements !" , le champ de base est "Personnel d'entreprise" pour obtenir les statistiques de performances des ventes et les classements par catégorie de produits

🎜🎜Étape 2, organisez le tableau en perspective🎜🎜Cliquez sur le tableau croisé dynamique et cliquez sur les commandes "Afficher sous forme de tableau" et "Répéter toutes les étiquettes d'éléments" dans le menu déroulant "Mise en page du rapport" dans le groupe d'options "Mise en page" dans l'onglet "Conception". Cliquez ensuite avec le bouton droit sur le tableau croisé dynamique, sélectionnez "Sous-classification et résumé "Personnel d'entreprise"", et annulez la sous-classification et les éléments récapitulatifs dans le tableau. suivant : 🎜🎜🎜🎜Étape 3, entrez la formule pour obtenir le nom et la performance du champion🎜 🎜Entrez la formule dans la cellule G2 : 🎜🎜=INDEX(L$2:L$200,MATCH( $G2&1,$K$2:$K$200&$N$2:$N$200,0)) 🎜🎜Après la saisie, appuyez sur Ctrl+Maj+Entrée pour terminer. 🎜🎜🎜🎜🎜Ensuite, faites un clic droit et déroulez la formule. 🎜🎜🎜🎜🎜 Dans le didacticiel d'aujourd'hui, nous avons appris plusieurs fonctions, à savoir SUMIFS, MAX, INDEX, MATCH, LOOKUP, et avons également appris deux routines pour la correspondance multi-conditions que vous pouvez utiliser directement lorsque vous rencontrez des problèmes similaires. 🎜🎜Cependant, la solution d’aujourd’hui est imparfaite. Bien que nous nous soyons demandé de « faire pipi dans la pomme » et de prêter attention aux détails du didacticiel, nous avons quand même manqué un détail très important : les ventes les plus élevées de produits similaires peuvent être les mêmes. 🎜🎜Recommandations d'apprentissage associées : 🎜Tutoriel Excel🎜🎜

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!