Fonction Excel, fonction de recherche d'apprentissage, application de recherche de correspondance multi-conditions

Cet article partage une formule Excel pour calculer la commission de vente. Il utilise principalement la fonction de recherche pour trouver des correspondances avec plusieurs conditions. J'espère qu'il sera utile à tout le monde !

Récemment, un étudiant de notre groupe d'échange d'apprentissage a posé une question sur le calcul des commissions.

J'ai à peu près compris le problème de cet élève grâce à une simple communication. Comme le montre le tableau ci-dessous :

La zone des rangées 1 à 5 est le tableau des commissions correspondant aux différents taux d'achèvement et aux différents montants de signature. La zone des lignes 8 à 12 correspond au taux d'achèvement réel et au montant de la commande des quatre utilisateurs. Il est maintenant nécessaire de calculer le montant de la commission de ces quatre utilisateurs en fonction du taux d'achèvement réel et du montant de la commande.

Cet exemple concerne principalement les problèmes suivants :

1. Comment trouver le niveau de taux d'achèvement correspondant en fonction du taux d'achèvement de l'utilisateur et des données sur le montant de la commande ?

2. La présentation du tableau de comparaison des commissions est bidimensionnelle, ce qui rend plus difficile la correspondance de l'ensemble du tableau.

Maintenant, nous allons analyser et résoudre ce problème étape par étape avec vous.

Première étape : faites correspondre les données du taux d'achèvement aux engrenages correspondants.

Entrez la formule dans la cellule D9 :

=LOOKUP(B9,{0.7,0.8,0.9,1})=LOOKUP(B9,{0.7,0.8,0.9,1})

解析：

LOOKUP（查找值，查找区域，返回区域），其中第三参数可以省略，省略时第二参数就作为查找区域和返回区域。

注意：

第一参数和第二参数的数据必须按升序排列，否则函数LOOKUP不能返回正确的结果，文本不区分大小写。

如果在查找区域中找不到查找值，则查找第二参数中小于等于查找值的最大数值。

如果查找值小于第二参数中的最小值，函数LOOKUP返回错误值#N/A。

其实可以简单理解为当X查找值时，那么就取X值。

本例中函数公式可以理解为X时，A用户的完成率为0.9992，通过X可以看到0.9是小于等于0.9992的最大值。那么按照lookup函数查找规则应该返回0.9，这样我们就完成了4个用户完成率的分档。

第二步：以同样的方式完成签单金额的分档。

在E9单元格输入公式：

=LOOKUP(C9/10000,{0,30,50,80,100,150,200},{"30万以下","30-50","50-80","80-100","100-150","150-200","200万以上"})，双击填充公式。

解析：

这里的公式中，LOOKUP有三个参数，第一参数为查找值，第二参数为查找区域，第三参数为返回指定的文本。

第三步：根据用户完成率和签单金额所处的分档来查找对应的提成。

这一步很简单，根据D9在A1-H5区域找到提成所在行，根据E9在A1-H5区域找到提成所在列，即可得到对应的提成结果。

F9单元格输入公式：

=VLOOKUP(D9,$A:$H,MATCH(E9,$A:$H,0),0)，双击填充。

解析：

VLOOKUP（查找值，查找区域，返回第几列，0）

Match（查找值，查找区域，0），需要注意的是，match函数的查找区域只能是单行单列。

上方公式的含义：使用VLOOKUP函数，在A1-H5区域内查找D9单元格值在第几行，再使用Match函数在A1-H1区域内查找E9单元格值在第几列，根据查找到的行号和列号即可得到对应的提成。

第四步：最后我们使用INT函数将公式计算结果统计出来。

首先在G9单元格输入="=INT("&F9&")"

Analyse :

🎜RECHERCHE (valeur de recherche, zone de recherche , zone de retour), le troisième paramètre peut être omis, et lorsqu'il est omis, le deuxième paramètre est utilisé comme zone de recherche et zone de retour. 🎜🎜🎜Remarque : 🎜🎜🎜Les données du premier paramètre et du deuxième paramètre doivent être classées par ordre croissant, sinon la fonction RECHERCHE ne peut pas renvoyer le résultat correct et le texte n'est pas sensible à la casse. 🎜🎜Si la valeur de recherche n'est pas trouvée dans la zone de recherche, recherchez la valeur maximale dans le deuxième paramètre qui est inférieure ou égale à la valeur de recherche. 🎜🎜Si la valeur de recherche est inférieure à la valeur minimale du deuxième paramètre, la fonction LOOKUP renvoie valeur d'erreur a>#N /A. 🎜🎜En fait, cela peut être simplement compris comme lorsque X recherche la valeur , alors la valeur X est prise. 🎜🎜Dans cet exemple, lorsque la formule de fonction peut être comprise comme X, le taux d'achèvement de l'utilisateur A est de 0,9992. Ensuite, selon la règle de recherche de la fonction de recherche, 0,9 devrait être renvoyé. De cette manière, nous avons terminé l'évaluation de 4 taux d'achèvement des utilisateurs. 🎜🎜🎜🎜Étape 2 : Complétez la notation du montant de signature de la même manière. 🎜🎜🎜🎜Entrez la formule dans la cellule E9 : 🎜🎜=LOOKUP(C9/10000,{0,30,50,80,100,150,200},{"under 300,000","30-50","50-80 " ,"80-100","100-150","150-200","Plus de 2 millions"}), double-cliquez pour remplir la formule. 🎜🎜🎜🎜🎜 Analyse : 🎜🎜🎜Dans la formule ici, LOOKUP a trois paramètres. Le premier paramètre est la valeur de recherche, le deuxième paramètre est la zone de recherche et le troisième paramètre est de renvoyer le texte spécifié. 🎜🎜🎜🎜Étape 3 : Trouvez la commission correspondante en fonction du taux de complétion des utilisateurs et du niveau du montant de la commande. 🎜🎜🎜🎜Cette étape est très simple. Recherchez la ligne de commission dans la zone A1-H5 basée sur D9, et recherchez la colonne de commission dans la zone A1-H5 basée sur E9 pour obtenir le résultat de commission correspondant. 🎜🎜🎜Entrez la formule dans la cellule F9 : 🎜🎜🎜=VLOOKUP(D9,$A$1:$H$5,MATCH(E9,$A$1:$H$1,0),0) , Double-cliquez pour remplir. 🎜🎜🎜🎜🎜 Analyse : 🎜🎜🎜VLOOKUP (trouver la valeur, zone de recherche, colonne de retour, 0) 🎜🎜Match (trouver la valeur, zone de recherche, 0), il faut faire attention De plus, la zone de recherche de la fonction match ne peut être qu'une seule ligne et une seule colonne. 🎜🎜La signification de la formule ci-dessus : utilisez la fonction RECHERCHEV pour trouver dans quelle ligne la valeur de la cellule D9 se trouve dans la zone A1-H5, puis utilisez la fonction Match pour trouver dans quelle colonne la valeur de la cellule E9 se trouve dans la zone A1-H5. zone A1-H1 Selon le numéro de ligne et le numéro de colonne trouvés, vous pouvez obtenir la commission correspondante. 🎜🎜🎜🎜Étape 4 : Enfin, nous utilisons la fonction INT pour compter les résultats du calcul de la formule. 🎜🎜🎜🎜Entrez d'abord ="=INT("&F9&")" dans la cellule G9. C'est tout. 🎜

Le résultat final est le suivant :

Maintenant, les statistiques des données de commission sur le nombre d'utilisateurs ont été complétées étape par étape. Si vous ne souhaitez pas utiliser la colonne auxiliaire et souhaitez obtenir le résultat en une seule étape, combinez simplement les formules ci-dessus.

Dans cet exemple, il est un peu long de combiner les formules de fonctions entre elles, mais les fonctions utilisées, à l'exception de la fonction RECHERCHE, qui nécessite une étude, sont les fonctions les plus basiques et les plus couramment utilisées, même si vous êtes novice en fonctions. C'est fait facilement ! En fait, le but principal du tutoriel d'aujourd'hui est de vous dire qu'avant de devenir génial, si vous rencontrez un gros problème difficile à résoudre, vous pouvez le diviser en plusieurs petits problèmes et les résoudre un par un. le gros problème sera résolu !

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!