Analyse comparative : concept, types et exemples. Calcul des KPI dans des exemples et des formules Excel Effectué selon plusieurs critères à la fois

Sujet : Prendre des décisions sur plusieurs critères indicateurs.

En pratique, il faut généralement choisir une décision de gestion non pas selon un critère, mais selon plusieurs. Par conséquent, leurs valeurs dans une évaluation comparative sont multidirectionnelles, c'est-à-dire sur un indicateur, l'alternative gagne et sur d'autres, elle perd.

Dans ces conditions, il est nécessaire de réduire le système d'évaluation des indicateurs envisagé à un système complexe, sur la base duquel une décision sera prise.

Pour construire une évaluation complète, deux problèmes doivent être résolus :

Le premier problème est que les indicateurs critères considérés ont une signification inégale ;

Le deuxième problème est caractérisé par le fait que les indicateurs sont évalués dans différentes unités de mesure, et pour construire une évaluation complète, il est nécessaire de passer à un seul compteur.

Le premier problème est résolu en appliquant l'une des quatre modifications de la méthode d'expertise, à savoir la méthode de comparaison par paires, qui permet d'en quantifier la signification. L'essence de la méthode de comparaison par paires est qu'un expert (spécialiste, investisseur potentiel, consommateur) procède à une évaluation par paires des indicateurs critères considérés, déterminant lui-même leur degré d'importance sous la forme d'un score. Après cela, après avoir effectué le traitement approprié des informations reçues, le coefficient de signification est calculé pour chacun des indicateurs critères considérés.

Le deuxième problème est résolu en utilisant un seul compteur pour les indicateurs privés. Le plus souvent, un compteur est utilisé comme tel. Dans ce cas, l'évaluation est réalisée selon deux approches :

- première approche utilisé en l'absence de données statistiques sur la valeur des indicateurs considérés ;

- deuxième approche utilisé en présence de données statistiques (limites de changement) sur la valeur des indicateurs considérés.

Lors de l'utilisation de la première approche pour convertir en points, on procède comme suit : la meilleure valeur de l'indicateur considéré est prise égale à 1 point, et les pires valeurs sont en fractions de ce point. Cette approche est simple, donne une évaluation objective, mais en même temps ne prend pas en compte les meilleures réalisations qui se situent en dehors des options envisagées.

Pour éliminer cette lacune, des informations sont nécessaires sur les limites de changement de l'indicateur considéré. Si disponible, la deuxième approche est utilisée. Dans ce cas, une échelle de conversion est construite pour convertir en points. Dans ce cas, le système de notation est choisi en utilisant les dispositions de la théorie des statistiques selon la formule de Sturges :

n = 1 + 3,322 LG N , Où

N est le nombre d'observations statistiques ;

n est le système de notation accepté obtenu à l'aide des règles d'arrondi.

La conversion en points est effectuée sur la base de l'échelle de conversion construite en utilisant la procédure d'interpolation des données tabulaires.

Exercice:

Parmi les 6 options de solutions alternatives, dont chacune est évaluée selon 5 critères indicateurs, il faut choisir la meilleure option.

Évaluez en utilisant 2 approches :

1) en l'absence de données statistiques sur la valeur des indicateurs considérés ;

2) si disponible.

Des limites de changement d'indicateurs sont établies pour le nombre d'observations suivant (N) :

Pour les variantes paires N = 8 ;

L'évaluation de la signification doit être réalisée sur la base d'une évaluation par paires selon l'interprète.

Tableau 1.

Options de tâche

numéro de travail	1	2	3	4	5
Nombre d'alternatives	1,2,3,4,5,6	2,4,8,9,11,15	1,3,5,7,9,10	4,6,8,12,13,14	1,5,10,11,12,15
numéro de travail	6	7	8	9	10
Nombre d'alternatives	6,7,10,11,14,15	3,4,5,8,9,10	7,8,9,10,13,15	1,2,3,13,14,15	2,4,5,7,12,13
numéro de travail	11	12	13	14	15
Nombre d'alternatives	1,7,8,9,10,11	6,9,12,13,14,15	2,5,7,9,10,11	7,8,9,10,11,12	1,2,3,4,8,9
numéro de travail	16	17	18	19	20
Nombre d'alternatives	1,2,3,10,12,13	2,5,7,8,10,15	1,6,7,12,13,14	3,4,5,6,10,14	2,8,11,12,14,15
numéro de travail	21	22	23	24	25
Nombre d'alternatives	1,2,6,7,9,10	3,5,8,9,13,14	4,7,8,10,11,12	5,6,7,8,11,13	8,9,10,11,12,13
numéro de travail	26	27	28	29	30
Nombre d'alternatives	1,3,4,10,11,15	2,3,5,8,9,15	1,4,7,11,13,15	2,6,7,8,12,14	1,10,11,12,8,4

Tableau 2.

Donnée initiale:

№№	Solutions alternatives
indicateurs	A1	A2	A3	A4	A5	A6	A7	A8	A9	A10	A11	A12	A13	A14	A15
X 1	5	10	15	6	11	16	7	14	18	20	19	8	21	13	10
X 2	10	9	8	8	5	7	4	9	5	8	7	7	6	3	2
X 3	4	3	5	10	6	5	11	7	7	9	8	12	8	5	9
X 4	1	2	3	4	4	3	2	1	1	3	4	2	2	4	3
X 5	10	14	13	11	12	20	21	23	17	18	19	24	22	16	18

Tableau 3

Exemple:

Quatre variantes de solutions alternatives sont proposées, chacune étant évaluée par 5 critères indicateurs. En fonction des conditions de la tâche, il est nécessaire de choisir la meilleure option.

Dans un premier temps, il est nécessaire de quantifier la signification de chaque indicateur. La méthode de comparaison par paires est utilisée, basée sur des évaluations d'experts.

Sur la base de cette évaluation, un tableau est établi - une matrice et le coefficient de signification -Kзi est calculé.

L'évaluation quantitative de la signification des indicateurs est déterminée comme suit : si, dans une évaluation en binôme, un expert (spécialiste, investisseur potentiel, consommateur) a donné la préférence à l'un des facteurs, alors le numéro du facteur préféré est mis en la ligne et la colonne de la matrice d'évaluation quantitative (voir tableau 4). Après cela, pour chaque ligne, le nombre de préférences accordées à l'un ou l'autre facteur dans leur évaluation par paires et leur somme (Σпi) sont déterminés. Ensuite, le coefficient de significativité est calculé selon la formule suivante :

Quantification de la signification des indicateurs :

Tableau 4

X1	X2	X3	X4	X5	ΣPi		Kzi
X1	1	1	3	1	5	3		0,2
X2	1	2	2	2	5	3		0,2
X3	3	2	3	4	5	2		0,133
X4	1	2	4	4	5	2		0,133
X5	5	5	5	5	5	5		0,333
∑∑Pi					15	1

Première approche.

La première approche de conversion en points se caractérise par le fait que la meilleure valeur de l'indicateur est prise égale à 1 point, la pire est estimée en fractions de ce point. Cette approche est simple, donne une évaluation comparative objective, mais prend en compte les meilleures réalisations qui ne sont pas incluses dans les options comparatives.

Code d'indexation	Score en points				Kzi	Score en points prenant en compte Ksi
	A1	A2	A3	A4		A1	A2	A3	A4
X1	0,3	0,35	0,7	1	0,2	0,06	0,07	0,14	0,2
X2	0,89	0,45	1	0,89	0,2	0,178	0,09	0,2	0,178
X3	0,91	1	0,64	0,82	0,133	0,121	0,133	0,085	0,110
X4	0,25	0,5	1	0,33	0,133	0,033	0,066	0,133	0,043
X5	1	0,52	0,48	0,61	0,333	0,333	0,173	0,159	0,203
Évaluation complète					0,725	0,532	0,717	0,73 4

Par exemple : Х1А1 : 6/20=0,3

H2А1 : 8/9=0,89

Conclusion: en utilisant la première approche, la meilleure option parmi les alternatives sera l’option A4, car elle présente l’évaluation globale la plus élevée. Viennent ensuite les options A1, A3, A2.

Deuxième approche.

Élimine les inconvénients de la première approche, mais son utilisation nécessite des informations sur les limites de changement de l'indicateur considéré. Parallèlement, une échelle de conversion est construite pour la conversion en points. Le système de notation est choisi sur la base des dispositions de la théorie statistique et dépend du nombre d'observations qui sous-tendent la formation des limites d'évolution des indicateurs.

Supposons que, dans notre exemple, 8 observations aient été réalisées (N=8), ce qui a permis d'établir les limites suivantes d'évolution des indicateurs qualitatifs (voir tableau 3).

En présence de ces indicateurs, une échelle de conversion en points est construite.

- Formule Sturges,

où N est le nombre d'observations.

Par conséquent, l'évaluation de l'indicateur de qualité se fera selon un système en 4 points, à savoir : n = 4.

- plage de variation,

où - les valeurs maximales et minimales des limites de changement de l'i-indicateur.

L'étape de changement d'indicateur.

L'échelle de conversion en points est un tableau dans lequel pour chaque point sont indiquées les limites d'évolution des indicateurs. Lors de la traduction des valeurs des indicateurs en points sur cette échelle, si la valeur de l'indicateur se situe dans l'intervalle, la procédure d'interpolation des données tabulaires est utilisée.

Échelle de conversion en points

Ensuite, les indicateurs de qualité de tous les produits sont évalués en points. Par exemple, selon l'alternative A1 : la valeur numérique de l'indicateur est extraite des données initiales, puis à l'aide de l'échelle de conversion en points, l'intervalle où tombe cette valeur est déterminé. Après cela, un score est attribué : à partir de la valeur numérique de l'indicateur, la limite inférieure de l'évolution de l'indicateur dans cet intervalle est soustraite d'un pas et l'intervalle précédent est ajouté. Selon les indicateurs X4, X5 - à partir de la limite supérieure du changement de l'indicateur dans cet intervalle, la valeur numérique de l'indicateur est divisée par un pas et l'intervalle précédent est ajouté.

Les valeurs obtenues sont résumées dans le tableau suivant.

indicateur	Score en points				Kzi	Score en points prenant en compte Ksi
	A1	A2	A3	A4		A1	A2	A3	A4
X1	0,2	0,4	1,8	3	0,2	0,04	0,08	0,36	0,6
X2	3	1	3,5	3	0,2	0,6	0,2	0,7	0,6
X3	2,33	2,66	1,33	2	0,134	0,313	0,357	0,179	0,268
X4	0	2,34	4	1,67	0,134	0	0,314	0,536	0,224
X5	3,04	1,44	1,12	1,92	0,334	1,02	0,481	0,374	0,642
Évaluation complète					1,973	1,432	2,149	2,334

Conclusion: en utilisant la deuxième approche, la meilleure option parmi les alternatives sera l’option A4, car elle présente l’évaluation globale la plus élevée. Viennent ensuite les options A3, A2, A1.

Ceci est un chapitre d'un livre : Michael Girvin. Ctrl+Maj+Entrée. Maîtriser les formules matricielles dans Excel.

Sélections basées sur une ou plusieurs conditions. Un certain nombre de fonctions Excel utilisent des opérateurs de comparaison. Par exemple, SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF et AVERAGEIFS. Ces fonctions effectuent des sélections basées sur une ou plusieurs conditions (critères). Le problème est que ces fonctions ne peuvent qu’additionner, compter et faire la moyenne. Et si vous souhaitez imposer des conditions à la recherche, par exemple la valeur maximale ou l'écart type ? Dans ces cas-là, puisqu’il n’y a pas de fonction intégrée, vous devez inventer une formule matricielle. Cela est souvent dû à l’utilisation de l’opérateur de comparaison de tableaux. Le premier exemple de ce chapitre montre comment calculer la valeur minimale sous une condition.

Utilisons la fonction IF pour sélectionner les éléments d'un tableau qui répondent à une condition. Sur la fig. 4.1 dans le tableau de gauche il y a une colonne avec les noms des villes et une colonne avec l'heure. Il faut trouver le temps minimum pour chaque ville et placer cette valeur dans la cellule correspondante du tableau de droite. La condition de sélection est le nom de la ville. Si vous utilisez la fonction MIN, vous pouvez trouver la valeur minimale de la colonne B. Mais comment sélectionner uniquement les nombres qui s'appliquent uniquement à Auckland ? Et comment copier les formules dans la colonne ? Étant donné qu'Excel n'a pas de fonction MINESLI intégrée, vous devez écrire une formule originale combinant les fonctions IF et MIN.

Riz. 4.1. Objectif de la formule : sélectionner le temps minimum pour chaque ville

Télécharger la note au format ou au format

Comme le montre la fig. 4.2, vous devez commencer à saisir la formule dans la cellule E3 avec la fonction MIN. Mais tu ne peux pas argumenter numéro 1 toutes les valeurs de la colonne B !? Vous souhaitez sélectionner uniquement les valeurs liées à Auckland.

Comme le montre la fig. 4.3, à l'étape suivante, entrez la fonction IF comme argument numéro 1 pour MIN. Vous mettez un IF à l'intérieur d'un MIN.

En positionnant le curseur à l'endroit où l'argument est saisi expression_log Avec la fonction SI (Fig. 4.4), vous sélectionnez la plage avec les noms des villes A3:A8, puis appuyez sur F4 pour rendre les références de cellules absolues (voir, par exemple, pour plus de détails). Ensuite, vous tapez l'opérateur comparatif, le signe égal. Enfin, vous sélectionnerez la cellule à gauche de la formule, D3, en laissant sa référence relative. La condition formulée vous permettra de sélectionner uniquement Aucklands lors de la visualisation de la plage A3:A8.

Riz. 4.4. Créer un opérateur de tableau dans un argument expression_log Fonctions SI

Vous avez donc créé un opérateur de tableau avec un opérateur de comparaison. À tout moment pendant le traitement du tableau, l'opérateur tableau est un opérateur de comparaison, son résultat sera donc un tableau de valeurs VRAI et FAUX. Pour le vérifier, sélectionnez le tableau (pour cela, cliquez sur l'argument dans la bulle d'aide expression_log) et appuyez sur F9 (Fig. 4.5). Habituellement, vous utilisez un argument expression_log, renvoyer VRAI ou FAUX ; ici, le tableau résultant renverra plusieurs valeurs VRAI et FAUX, donc la fonction MIN sélectionnera le nombre minimum uniquement pour les villes qui correspondent à la valeur VRAI.

Riz. 4.5. Pour voir un tableau de valeurs VRAI et FAUX, cliquez sur l'argument dans l'info-bulle expression_log et appuyez sur F9

Dans la deuxième partie de notre tutoriel sur la fonction RECHERCHEV(RECHERCHEV) dans Excel, nous analyserons quelques exemples qui vous aideront à diriger toute la puissance RECHERCHEV pour résoudre les tâches Excel les plus ambitieuses. Les exemples supposent que vous possédez déjà une connaissance de base du fonctionnement de cette fonction. Sinon, vous pourriez être intéressé à commencer par la première partie de ce didacticiel, qui explique la syntaxe et l'utilisation de base. RECHERCHEV. Eh bien, commençons.

Rechercher dans Excel selon plusieurs critères

Fonction RECHERCHEV dans Excel est un outil très puissant pour effectuer des recherches sur une valeur particulière dans une base de données. Cependant, il existe une limitation importante : sa syntaxe vous permet de rechercher une seule valeur. Que faire si vous souhaitez effectuer une recherche selon plusieurs conditions ? Vous trouverez la solution ci-dessous.

Exemple 1 : Recherche selon 2 critères différents

Supposons que nous ayons une liste de commandes et que nous voulions trouver Quantité de marchandises(Qté.), sur la base de deux critères - Nom du client(Client) et Le nom du produit(Produit). L'affaire est compliquée par le fait que chacun des acheteurs a commandé plusieurs types de marchandises, comme le montre le tableau ci-dessous :

fonction régulière RECHERCHEV ne fonctionnera pas dans ce scénario car il renverra la première valeur trouvée qui correspond à la valeur de recherche donnée. Par exemple, si vous souhaitez connaître la quantité d'un article Bonbons commandé par l'acheteur Jérémy Hill, écrivez la formule suivante :

RECHERCHEV(B1,$A$5 :$C$14,3,FALSE)
=RECHERCHEV(B1,$A$5 :$C$14,3,FALSE)

- cette formule renverra le résultat 15 correspondant au produit pommes, car c'est la première valeur qui correspond.

Il existe une solution de contournement simple : créez une colonne supplémentaire dans laquelle combiner tous les critères souhaités. Dans notre exemple, ce sont les colonnes Nom du client(Client) et Le nom du produit(Produit). N'oubliez pas que la colonne fusionnée doit toujours être la colonne la plus à gauche de la plage de recherche, puisque c'est la colonne de gauche que la fonction RECHERCHEV lève la tête lorsqu'il recherche une valeur.

Ainsi, vous ajoutez une colonne auxiliaire au tableau et copiez la formule suivante sur toutes ses cellules : =B2&C2. Si vous souhaitez que la chaîne soit plus lisible, vous pouvez séparer les valeurs combinées par un espace : =B2&" "&C2. Après cela, vous pouvez utiliser la formule suivante :

RECHERCHEV("Jeremy Hill Sweets", 7$A$ : $D$18,4,FALSE)
=RECHERCHEV("Jeremy Hill Sweets",7$A$:$D$18,4,FALSE)

RECHERCHEV(B1,$A$7 :$D$18,4,FALSE)
=RECHERCHEV(B1,$A$7 :$D$18,4,FALSE)

Où est la cellule B1 contient la valeur concaténée de l'argument valeur_recherche(lookup_value) et 4 - argument col_index_num(numéro_colonne), c'est-à-dire le numéro de la colonne contenant les données à récupérer.

Exemple 2 : RECHERCHEV selon deux critères avec tableau affiché sur une autre feuille

Si vous devez mettre à jour la table principale (Table principale) en ajoutant les données de la deuxième table (Table de recherche), qui se trouve sur une autre feuille ou dans un autre classeur Excel, vous pouvez alors collecter la valeur souhaitée directement dans la formule que vous insérez dans la table principale.

Comme dans l'exemple précédent, vous aurez besoin d'une colonne auxiliaire dans la table de recherche avec les valeurs combinées. Cette colonne doit être la colonne la plus à gauche de la plage de recherche.

Donc la formule avec RECHERCHEVça pourrait être comme ça :

RECHERCHEV(B2&" "&C2,Commandes!$A&$2:$D$2,4,FALSE)
=RECHERCHEV(B2&" "Commandes!$A&$2:$D$2;4;FALSE)

Ici, les colonnes B et C contiennent respectivement les noms des clients et les noms des produits, ainsi que le lien Commandes !$A&$2 :$D$2 définit un tableau à rechercher dans une autre feuille.

Pour rendre la formule plus lisible, vous pouvez donner un nom à la plage de vues, et la formule paraîtra alors beaucoup plus simple :

RECHERCHEV(B2&" "&C2,Commandes,4,FALSE)
=RECHERCHEV(B2&" "Commandes;4;FALSE)

Pour que la formule fonctionne, les valeurs de la colonne la plus à gauche du tableau consulté doivent être combinées de la même manière que dans les critères de recherche. Dans la figure ci-dessus, nous avons combiné les valeurs et mis un espace entre elles, tout comme vous devez le faire dans le premier argument de la fonction (B2&” « &C2).

Souviens-toi! Fonction RECHERCHEV limité à 255 caractères, il ne peut pas rechercher une valeur de plus de 255 caractères. Gardez cela à l’esprit et assurez-vous que la longueur de la valeur souhaitée ne dépasse pas cette limite.

Je suis d'accord que l'ajout d'une colonne auxiliaire n'est pas la solution la plus élégante et pas toujours acceptable. Vous pouvez faire la même chose sans la colonne d'assistance, mais cela nécessiterait une formule beaucoup plus complexe avec une combinaison de fonctions INDICE(INDICE) et CORRESPONDRE(CORRESPONDRE).

On extrait le 2ème, le 3ème, etc. valeurs à l'aide de RECHERCHEV

Tu le sais déjà RECHERCHEV ne peut renvoyer qu'une seule valeur correspondante, plus précisément la première trouvée. Mais que se passe-t-il si cette valeur est répétée plusieurs fois dans le tableau affiché et que vous souhaitez en extraire la 2ème ou la 3ème ? Et si toutes les valeurs ? Le problème semble complexe, mais la solution existe !

Supposons qu'une colonne du tableau contienne les noms des clients (Nom du client) et que l'autre colonne contienne les produits (Produit) qu'ils ont achetés. Essayons de retrouver les 2ème, 3ème et 4ème articles achetés par un client donné.

Le plus simple est d'ajouter une colonne auxiliaire avant la colonne nom du client et remplissez-le avec les noms de clients avec le numéro de répétition de chaque nom, par exemple, John Doe1, John Doe2 etc. Nous allons faire l'affaire avec la numérotation en utilisant la fonction COUNTIF(COUNTIF), étant donné que les noms des clients sont dans la colonne B :

B2&COUNTIF($B$2:B2,B2)
=B2&COUNTIF($B$2:B2,B2)

Après cela, vous pouvez utiliser la fonction normale RECHERCHEV pour trouver l'ordre souhaité. Par exemple:

• Nous trouvons 2ème Dan Brun:

  RECHERCHEV("Dan Brown2",2$A$:16,3$C$,FALSE)
  =RECHERCHEV("Dan Brown2",$A$2 :$C$16,3,FALSE)

• Nous trouvons 3ème article commandé par le client Dan Brun:

  RECHERCHEV("Dan Brown3",2$A$:16,3$C$,FALSE)
  =RECHERCHEV("Dan Brown3",$A$2 :$C$16,3,FALSE)

Si vous cherchez seulement 2ème répétition, vous pouvez le faire sans la colonne auxiliaire en créant une formule plus complexe :

SIERREUR(RECHERCHEV($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4,0)+2)&":$C16"),2,FALSE),"")
=SIERREUR(RECHERCHEV($F$2;INDIRECT("$B$"&(MATCH($F$2;Table4;0)+2)&":$C16");2;TRUE);"")

Dans cette formule :

• $F$2- une cellule contenant le nom de l'acheteur (il est inchangé, attention - le lien est absolu) ;
• $B$- colonne nom du client;
• Tableau4- Votre table (ce lieu peut aussi être un range régulier) ;
• 16 $CAN- la cellule de fin de votre table ou plage.

Cette formule trouve uniquement la deuxième valeur correspondante. Si vous devez extraire les répétitions restantes, utilisez la solution précédente.

Si vous avez besoin d'une liste de toutes les correspondances - la fonction RECHERCHEV ce n'est pas une aide, car il ne renvoie qu'une seule valeur à la fois. Mais Excel a une fonction INDICE(INDEX), qui peut facilement faire face à cette tâche. À quoi ressemblera une telle formule, vous l’apprendrez dans l’exemple suivant.

Récupérer toutes les répétitions de la valeur souhaitée

Comme mentionné ci-dessus, RECHERCHEV ne peut pas extraire toutes les valeurs en double de la plage numérisée. Pour ce faire, vous avez besoin d'une formule un peu plus complexe, composée de plusieurs fonctions Excel, comme INDICE(INDICE), PETIT(PETIT) et RANGÉE(DOUBLER)

Par exemple, la formule ci-dessous recherche toutes les répétitions de la valeur de la cellule F2 dans la plage B2:B16 et renvoie le résultat des mêmes lignes de la colonne C.

(=SIERREUR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"") )
(=SIERREUR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,"");ROW()-3)),"") )

Entrez cette formule matricielle dans plusieurs cellules adjacentes, telles que les cellules F4:F8 comme le montre la figure ci-dessous. Le nombre de cellules doit être égal ou supérieur au nombre maximum possible de répétitions de la valeur recherchée. N'oubliez pas de cliquer Ctrl+Maj+Entrée pour saisir correctement la formule matricielle.

Si vous souhaitez comprendre comment cela fonctionne, examinons un peu les détails de la formule :

Partie 1:

SI($F$2=B2:B16,ROW(C2:C16)-1,"")
SI($F$2=B2:B16;ROW(C2:C16)-1;"")

$F$2=B2:B16– comparez la valeur de la cellule F2 avec chacune des valeurs de la plage B2:B16. Si une correspondance est trouvée, alors l'expression CHAÎNE(C2:C16)-1 renvoie le numéro de la ligne correspondante (valeur -1 permet de ne pas inclure la ligne d'en-tête). S'il n'y a aucune correspondance, la fonction SI(IF) renvoie une chaîne vide.

Résultat de la fonction SI(SI) il y aura un tel tableau horizontal : (1,"",3,"",5,"","","","","","",12,"","", "" )

Partie 2:

LIGNE()-3
CHAÎNE()-3

Ici la fonction RANGÉE(LINE) fait office de compteur supplémentaire. Puisque la formule est copiée dans les cellules F4:F9, nous soustrayons le nombre 3 à partir du résultat de la fonction pour obtenir la valeur 1 dans une cellule F4(ligne 4, soustrayez 3) pour obtenir 2 dans une cellule F5(ligne 5, soustraire 3) et ainsi de suite.

Partie 3 :

PETIT(SI($F$2=B2:B16,LIGNE(C2:C16)-1,""),LIGNE()-3))
PETIT(IF($F$2=B2:B16,LIGNE(C2:C16)-1,"");LIGNE()-3))

Fonction PETIT(PETITS) retours nième la plus petite valeur du tableau de données. Dans notre cas, la position (parmi la plus petite) à retourner est déterminée par la fonction RANGÉE(LIGNE) (voir Partie 2). Donc pour une cellule F4 fonction PETIT((tableau),1) Retour 1er(le plus petit) élément du tableau, c'est-à-dire 1 . Pour cellule F5 Retour 2ème le plus petit élément du tableau, c'est-à-dire 3 , et ainsi de suite.

Partie 4 :

INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
INDICE($C$2:$C$16;PETIT(IF($F$2=B2:B16;ROW(C2:C16)-1;"");ROW()-3))

Fonction INDICE(INDEX) renvoie simplement la valeur d'une cellule spécifique dans un tableau C2:C16. Pour cellule F4 fonction INDEX(2$C$:16,1$C$) reviendra pommes, Pour F5 fonction INDEX ($CAN 2 : $CAN 16 ; 3) reviendra Bonbons et ainsi de suite.

Partie 5 :

SIERREUR()
SIERREUR()

Enfin, nous mettons la formule à l'intérieur de la fonction SIERREUR(SIERREUR), car il est peu probable que vous soyez satisfait du message d'erreur #N / A(#N/A) si le nombre de cellules dans lesquelles la formule est copiée est inférieur au nombre de valeurs en double dans la plage affichée.

Recherche 2D par ligne et colonne connues

Effectuer une recherche 2D dans Excel implique de rechercher une valeur par un numéro de ligne et de colonne connu. En d’autres termes, vous extrayez la valeur de la cellule à l’intersection d’une ligne et d’une colonne particulières.

Alors, tournons-nous vers notre tableau et écrivons une formule avec une fonction RECHERCHEV, qui trouvera des informations sur le coût des citrons vendus en mars.

Il existe plusieurs manières d'effectuer une recherche 2D. Découvrez les options et choisissez celle qui vous convient le mieux.

Fonctions RECHERCHEV et MATCH

Vous pouvez utiliser un tas de fonctions RECHERCHEV(RECHERCHEV) et CORRESPONDRE(MATCH) pour trouver la valeur à l'intersection des champs Le nom du produit(chaîne) et Mois(colonne) du tableau en question :

RECHERCHEV("Citrons",$A$2 :$I$9,MATCH("Mar",$A$1 :$I$1,0),FALSE)
=RECHERCHEV("Citrons",$A$2 :$I$9,MATCH("Mar",$A$1 :$I$1,0),FALSE)

La formule ci-dessus est une fonction régulière RECHERCHEV, qui recherche une correspondance exacte de la valeur « Citrons » dans les cellules A2 à A9. Mais comme vous ne savez pas dans quelle colonne se trouvent les ventes de mars, vous ne pourrez pas définir le numéro de colonne pour le troisième argument de la fonction. RECHERCHEV. Au lieu de cela, la fonction est utilisée CORRESPONDRE pour définir cette colonne.

MATCH("Mars",$A$1:$I$1,0)
MATCH("Mars";$A$1:$I$1;0)

Traduite en langage humain, cette formule signifie :

• Recherche de personnages "Mar" - argument valeur_recherche(lookup_value);
• Recherche dans les cellules de A1 à I1 - argument recherche_array(lookup_array);
• Renvoyer une correspondance exacte - argument Type de match(Type de match).

En utilisant 0 dans le troisième argument, vous dites fonctions CORRESPONDRE recherchez la première valeur qui correspond exactement à la valeur que vous recherchez. Cela équivaut à la valeur FAUX(FALSE) pour le quatrième argument RECHERCHEV.

C'est ainsi que vous pouvez créer une formule de recherche bidirectionnelle dans Excel, également appelée recherche bidimensionnelle ou recherche bidirectionnelle.

Fonction SOMMEPRODUIT

Fonction SOMME PRODUIT(SUMPRODUCT) renvoie la somme des produits des tableaux sélectionnés :

SUMPRODUCT(($A$2:$A$9="Citrons")*($A$1:$I$1="Mar"),$A$2:$I$9)
=SUMPRODUCT(($A$2:$A$9="Citrons")*($A$1:$I$1="Mar");$A$2:$I$9)

Fonctions INDEX et MATCH

Dans le prochain article, j'expliquerai ces fonctions en détail, donc pour l'instant vous pouvez simplement copier cette formule :

INDEX($A$2 :$I$9,MATCH("Citrons",$A$2 :$A$9.0),MATCH("Mar",$A$1:$I$1.0))
=INDEX($A$2:$I$9;MATCH("Citrons";$A$2:$A$9;0);MATCH("Mar";$A$1:$I$1;0))

Plages nommées et opérateur d'intersection

Si vous n'aimez pas toutes ces formules Excel complexes, vous aimerez peut-être cette méthode visuelle et mémorable :

Lorsque vous entrez un nom, Microsoft Excel affiche une info-bulle avec une liste de noms correspondants, tout comme lorsque vous entrez une formule.

1. Cliquez sur Entrer et vérifie le résultat

En général, quelle que soit la méthode ci-dessus que vous choisissez, le résultat d'une recherche bidimensionnelle sera le même :

Utilisation de plusieurs RECHERCHEV dans une seule formule

Il arrive que la table principale et la table de recherche n'aient pas une seule colonne en commun, ce qui empêche d'utiliser la fonction habituelle RECHERCHEV. Cependant, il existe une autre table qui ne contient pas les informations qui nous intéressent, mais qui possède une colonne commune avec la table principale et la table de recherche.

Jetons un coup d'œil à l'exemple suivant. Nous avons une table principale avec une colonne SKU (nouveau), où vous souhaitez ajouter une colonne avec les prix correspondants d'un autre tableau. De plus, nous disposons de 2 tables de recherche. Le premier (table de recherche 1) contient des numéros mis à jour SKU (nouveau) et les noms de produits, et le second (table de recherche 2) - les noms de produits et les anciens numéros SKU (ancien).

Pour ajouter les prix de la deuxième table de recherche à la table principale, vous devez effectuer une action appelée double RECHERCHEV ou imbriqué RECHERCHEV.

1. Écrire une fonction RECHERCHEV, qui trouve le nom du produit dans le tableau table de recherche 1 en utilisant UGS, comme valeur souhaitée :

   RECHERCHEV (A2, Nouveau_SKU, 2, FALSE)
   =RECHERCHEV(A2;Nouveau_SKU;2;FALSE)

   Ici Nouveau_SKU- plage nommée $A : $B dans la table table de recherche 1, UN 2 - il s'agit de la colonne B, qui contient les noms des marchandises (voir l'image ci-dessus)

2. Écrire une formule pour insérer les prix d'un tableau table de recherche 2 basé sur des noms de produits bien connus. Pour ce faire, collez la formule que vous avez créée précédemment comme valeur de recherche pour la nouvelle fonction. RECHERCHEV:

   RECHERCHEV(RECHERCHEV(A2,Nouveau_SKU,2,FALSE),Prix,3,FALSE)
   =RECHERCHEV(RECHERCHEV(A2,Nouveau_SKU,2,FALSE),Prix,3,FALSE)

   Ici prix- plage nommée $A : $C dans la table table de recherche 2, UN 3 est la colonne C contenant les prix.

La figure ci-dessous montre le résultat renvoyé par la formule que nous avons créée :

Substitution dynamique des données de différentes tables à l'aide de RECHERCHEV et INDIRECT

Tout d'abord, clarifions ce que nous entendons par « Substitution dynamique des données de différentes tables » pour nous assurer que nous nous comprenons correctement.

Il existe des situations où il existe plusieurs feuilles avec des données du même format et il est nécessaire d'extraire les informations nécessaires d'une certaine feuille, en fonction de la valeur saisie dans une cellule donnée. Je pense qu'il est plus facile d'expliquer cela avec un exemple.

Imaginez que vous disposiez de rapports de ventes pour plusieurs régions avec les mêmes produits et dans le même format. Vous souhaitez connaître les chiffres de ventes d'une région spécifique :

Si vous n'avez que deux de ces rapports, vous pouvez utiliser une formule honteusement simple avec des fonctions RECHERCHEV Et SI(IF) pour sélectionner le rapport souhaité à rechercher :

RECHERCHEV($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=RECHERCHEV($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)

• $D$2 est une cellule contenant le nom du produit. Notez que nous utilisons ici des références absolues pour éviter de modifier la valeur de recherche lors de la copie de la formule dans d'autres cellules.
• $D3 est une cellule avec le nom de la région. Nous utilisons une référence de colonne absolue et une référence de ligne relative car nous prévoyons de copier la formule dans d'autres cellules de la même colonne.
• FL_Sales Et CA_Ventes– les noms des tables (ou plages nommées) qui contiennent les rapports de ventes correspondants. Vous pouvez bien sûr utiliser les noms de feuilles et les références de plages de cellules habituels, par exemple 'Feuille FL' !$A$3 :$B$10, mais les plages nommées sont beaucoup plus pratiques.

Cependant, lorsqu'il existe de nombreuses tables de ce type, la fonction SI n'est pas la meilleure solution. A la place, vous pouvez utiliser la fonction INDIRECT(INDIRECT) pour renvoyer la plage de recherche souhaitée.

Comme vous le savez probablement, la fonction INDIRECT est utilisé pour renvoyer un lien donné par une chaîne de texte, ce qui est exactement ce dont nous avons besoin maintenant. Alors n'hésitez pas à remplacer dans la formule ci-dessus l'expression par la fonction SI faire le lien avec la fonction INDIRECT. Voici une combinaison RECHERCHEV Et INDIRECT fonctionne très bien avec :

RECHERCHEV($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=RECHERCHEV($D$2;INDIRECT($D3&"_Sales");2;FALSE)

• $D$2- il s'agit d'une cellule avec le nom du produit, elle est inchangée du fait du lien absolu.
• $D3 est la cellule contenant la première partie du nom de la région. Dans notre exemple, ceci FL.
• _Ventes- la partie commune du nom de toutes les plages ou tables nommées. Combiné avec la valeur de la cellule D3, il forme le nom complet de la plage requise. Vous trouverez ci-dessous quelques détails pour ceux qui débutent dans la fonction INDIRECT.

Comment fonctionnent INDIRECT et VLOOKUP

Tout d'abord, laissez-moi vous rappeler la syntaxe de la fonction INDIRECT(INDIRECT):

• A1 si l'argument est VRAI(VRAI) ou non spécifié ;
• R1C1, Si FAUSSI(MENSONGE).

Revenons donc à nos rapports de ventes. Si vous vous en souvenez, chaque rapport est un tableau distinct situé sur une feuille distincte. Pour que la formule fonctionne correctement, vous devez nommer vos tables (ou plages), et tous les noms doivent avoir une partie commune. Par exemple, comme ceci : CA_Ventes, FL_Ventes, TX_Ventes et ainsi de suite. Comme vous pouvez le voir, tous les noms contiennent « _Sales ».

Fonction INDIRECT connecte la valeur de la colonne D et la chaîne de texte "_Sales", indiquant ainsi RECHERCHEV dans quelle table chercher. Si la cellule D3 contient la valeur « FL », la formule recherchera dans le tableau FL_Ventes, si « CA » est dans le tableau CA_Ventes et ainsi de suite.

Le résultat des fonctions RECHERCHEV Et INDIRECT sera le suivant :

Si les données se trouvent dans différents livres Excel, vous devez alors ajouter le nom du livre avant la plage nommée, par exemple :

RECHERCHEV($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=RECHERCHEV($D$2;INDIRECT($D3&"Workbook1!_Sales");2;FALSE)

Si la fonction INDIRECT fait référence à un autre classeur, ce classeur doit être ouvert. S'il est fermé, la fonction signalera une erreur. #réf!(#REF!).

Supposons que vous souhaitiez additionner des valeurs comportant plusieurs conditions, telles que la somme des ventes d'un produit dans une région particulière. Il s'agit d'un bon cas d'utilisation de la fonction SUMIFS dans une formule.

Faites attention à cet exemple où nous avons deux conditions : le montant des ventes est requis viande(de la colonne C) à Sud zone (de la colonne a).

Voici une formule qui peut être utilisée pour accomplir :

=SUMIFS(D2:D11; a2: A11; "Sud"; C2: C11; "viande")

Le résultat est la valeur 14 719.

Considérez une représentation plus détaillée de chaque partie de la formule.

SUMIFS est une formule arithmétique. Calcule les nombres, qui dans ce cas se trouvent dans la colonne D. La première étape consiste à spécifier l'emplacement des nombres.

=SUMIFS(D2:D11,

En d’autres termes, vous souhaitez que la formule additionne les nombres de cette colonne s’ils remplissent certaines conditions. Cette plage de cellules est la première argument dans cette formule, le premier élément de données que la fonction prend en entrée.

Ensuite, vous devez trouver les données qui remplissent les deux conditions et saisir la première condition, en spécifiant l'emplacement des données (a2:A11) pour la fonction, ainsi que la condition « Sud ». Notez les virgules entre les arguments individuels.

=SUMIFS(D2:D11,A2:A11,"Sud";

Les guillemets autour du mot « Sud » indiquent qu'il s'agit de données textuelles.

Enfin, vous entrez des arguments pour la deuxième condition - la plage de cellules (C2: C11) contenant le mot « Viande » et le mot lui-même (entre guillemets) afin qu'Excel puisse les faire correspondre. Terminez la formule en fermant la parenthèse fermante ) , puis appuyez sur Entrée. Le résultat est encore une fois à 14 719.

=SUMIFS(D2:D11,A2:A11,"Sud",C2:C11,"Viande")

Lorsque vous tapez la fonction SUMIFS dans Excel, si vous ne vous souvenez pas de ces arguments, l'aide est prête. Après avoir entré la formule = SOMMAIRES ( La saisie semi-automatique de la formule apparaîtra sous la formule et la liste des arguments sera répertoriée dans le bon ordre.

Jetez un œil à l'image des formules de saisie semi-automatique et à la liste des arguments dans notre exemple somme_plage est D2:D11, la colonne de nombres à additionner. critères_range1-a2. A11 - colonne de données contenant condition1"Sud".

Au fur et à mesure que vous tapez la formule, le reste des arguments (non affichés ici) apparaîtra dans la complétion de la formule ; condition_range2 est la plage C2:C11, qui est la colonne de données dans laquelle le condition2- "Viande".

Si cliquez SOMMAIRES la fin de la formule ouvrira un article dans lequel vous pourrez obtenir plus d’informations.

Essayez de pratiquer

Si vous souhaitez expérimenter la fonction SUMIFS, voici quelques exemples de données et une formule qui utilise la fonction.

Dans ce classeur Excel pour le Web, vous pouvez travailler avec des exemples de données et de formules ici. Modifiez les valeurs et les formules ou ajoutez les vôtres pour voir comment les résultats changent instantanément.

Copiez toutes les cellules du tableau ci-dessous et collez-les dans la cellule A1 d'une nouvelle feuille Excel. Vous pouvez ajuster la largeur des colonnes pour améliorer l'affichage des formules.

Région	Vendeur		Ventes
Ouest
Oriental	Pesotski
Nord	Pesotski	Laitier
	Marinova
Oriental	Pesotski
Nord

Dans cet article, vous découvrirez 5 propriétés utiles des tableaux croisés dynamiques qui vous aideront à analyser rapidement et en détail la situation (en utilisant l'exemple de l'analyse de la clientèle). Tu vas apprendre:

1. Comment regrouper les données ;
2. Quels indicateurs peuvent être calculés lors de la synthèse des données ;
3. Comment calculer simultanément plusieurs indicateurs pour un paramètre lors de la synthèse des données ;
4. Quelles options de calcul supplémentaires pouvez-vous utiliser lors de la synthèse des données ?
5. À propos de la possibilité de trier.

Et sur la base de cette analyse, nous aborderons la technique la plus puissante pour planifier les promotions des ventes sur les marchés FMCG.

Commençons par un tableau croisé dynamique. Prenons un simple tableau des ventes aux clients par jour.

Placez le curseur dans le coin supérieur gauche de notre tableau, puis allez dans le menu « Insertion » et cliquez sur le bouton « Tableau croisé dynamique » :

Dans la boîte de dialogue Créer un tableau croisé dynamique, cliquez sur OK :

Nous avons un tableau croisé dynamique sur une nouvelle feuille :

1ère fonctionnalité utile d'un tableau croisé dynamique pour l'analyse commerciale – regroupement de données

Ainsi, nous avons des expéditions aux clients par jour, nous voulons comprendre dans quelle gamme d'expéditions nous avons le maximum de ventes. Pour ce faire, nous devons regrouper les expéditions en plages.

Faites glisser le champ « Montant d'expédition » vers la zone « Noms de lignes » du tableau croisé dynamique (maintenez enfoncé le champ « Montant d'expédition » avec le bouton gauche de la souris et faites-le glisser vers la section « Noms de lignes » du tableau croisé dynamique) :

Nous avons affiché toutes les expéditions dans la colonne de gauche du récapitulatif. Maintenant, nous plaçons le curseur sur nos expéditions (comme sur la figure) :

Allez dans le menu Excel "Données" et cliquez sur le bouton "Groupe"

Dans la boîte de dialogue qui apparaît, définissez l'étape de regroupement sur "5000" (vous pouvez en saisir n'importe laquelle) et cliquez sur "OK".

On obtient des volumes de ventes groupés avec une étape donnée :

Pour le groupe avait l'air sympa et perçu, appuyez à nouveau Bouton "Groupe" et définissez manuellement des valeurs égales, pour la valeur "à partir de" - "-15 000" (en dessous de la valeur minimale, un multiple de 5 000) "à" - "45 000" (supérieur au groupe maximum, un multiple de 5 000).

Nous obtenons des données regroupées par montant d'envoi :

2ème fonctionnalité utile des tableaux croisés dynamiques pour l'analyse commerciale -
la possibilité de calculer divers paramètres récapitulatifs par champs de la table source

Ainsi, les expéditions ont été regroupées, voyons maintenant quels volumes de ventes tombent sur chaque gamme d'expéditions. Pour ce faire, additionnons les expéditions dans le récapitulatif.

Faites un clic gauche sur le champ « Shipping_amount » et faites-le glisser vers le champ récapitulatif « Valeurs » :

Le récapitulatif calcule par défaut "Quantité par champ Shipment_amount", c'est-à-dire le nombre d'enregistrements dans notre tableau d'origine sur la feuille "Données". Parce que Puisque notre tableau contient des informations sur les ventes aux clients par jour, notre indicateur « Quantité dans le champ Shipment_amount » est le nombre d'expéditions aux clients.

En conséquence, dans le tableau croisé dynamique, nous voyons le nombre d'expéditions aux clients dans différentes plages d'expédition :

Comment pouvons-nous obtenir le montant des expéditions à partir du nombre d’expéditions ?

On fait un clic gauche sur le champ "Quantité par champ Montant de l'expédition" dans la zone du tableau croisé dynamique "Valeurs", et dans le menu qui s'ouvre, sélectionnez "Paramètres des champs de valeur..."

Dans la fenêtre qui s'ouvre, sélectionnez l'opération de réduction de données qui nous intéresse (Somme, quantité, moyenne, maximum, minimum...). Sélectionnez l'opération dont nous avons besoin "somme" et cliquez sur "OK".

Nous obtenons le volume total des ventes pour chaque gamme d’expédition :

Ceux. nous voyons combien les ventes diminuent sur les expéditions comprises entre 0 et 5 000 roubles, entre 5 000 et 10 000 roubles. etc. Et il est clair que le volume maximum des expéditions se situe entre zéro et 5 000 roubles.

3 propriétés - la possibilité pour un champ de calculer diverses opérations de réduction de données

Nous aimerions maintenant voir combien d’envois et quelle moyenne d’envois nous avons dans chacune des gammes. Pour ce faire, nous utilisons le tableau croisé dynamique pour calculer le nombre d’envois et les envois moyens.

Dans la zone du tableau croisé dynamique "Valeurs", nous faisons glisser le champ "Shipping_amount" 2 fois de plus et dans les paramètres du champ de valeur pour la seconde nous sélectionnons "quantité" et pour le troisième champ nous sélectionnons "moyenne ".

Nous obtenons pour chaque tranche d'expéditions le volume des ventes, le nombre d'expéditions et l'expédition moyenne :

Nous pouvons maintenant voir dans quelle plage d’expéditions se situe le volume de ventes maximum et le nombre maximum d’expéditions. Dans notre exemple, il s'agit d'une fourchette de 0 à 5 000 roubles. et le volume des ventes et le nombre d'expéditions autant que possible.

4ème propriété des tableaux croisés dynamiques - la possibilité d'effectuer des calculs supplémentaires

Pour plus de clarté dans l'analyse des données, ajoutons 2 paramètres supplémentaires - « Part par volume de ventes pour chaque groupe » et « Part du nombre d'expéditions pour chaque groupe ».

Pour cela, dans le champ du tableau croisé dynamique "Valeurs", faites glisser encore 2 fois le champ "Montant des frais de port".

De plus, pour un paramètre du menu "Paramètres du champ de valeurs" () on sélectionnera l'opération "somme", et pour la deuxième opération "quantité".

On obtient le tableau suivant :

Maintenant encore une fois, nous allons dans les « Paramètres des champs de valeur » et entrons dans l'onglet « Calculs supplémentaires » :

Sélectionnez dans le champ "Calculs complémentaires" la rubrique "Part du montant total"

Nous obtenons un tableau dans lequel pour chaque gamme d'expéditions aux clients, nous voyons le volume des ventes, le nombre d'expéditions, l'expédition moyenne, la part des ventes pour chaque groupe et la part du nombre d'expéditions pour chaque groupe :

5 propriétés utiles - tri

Maintenant, pour plus de clarté, du groupe maximum au groupe minimum par volume de ventes, nous allons trier. Pour cela, placez le curseur dans le champ avec le volume des ventes par groupes et cliquez sur le bouton « trier du maximum au minimum » :

On peut voir que le groupe maximum en termes de volume de ventes et de nombre d'expéditions est le groupe "de 0 à 5 000 roubles". les ventes moyennes dans ce groupe sont de 1971 roubles.

Note! L’expédition moyenne pour tous les clients est très différente de 86 % des expéditions. De plus, cela diffère considérablement

• pour tous les groupes, l'envoi moyen est de 2 803 roubles. (en ligne totale).
• Et pour 86 % des expéditions, 1 971 roubles.

C'est une différence sérieuse, et si nous stimulons les ventes sur la base de 86% des expéditions et de leur moyenne - 1 971 roubles, alors nos actions seront plus précises et l'effet est beaucoup plus élevé, car. Nous pourrons intéresser le maximum de clients.

Cet exemple montre une technique puissante de planification de promotions sur le marché de masse et de prévision des ventes qui peut vous aider à avoir un impact important et à faire la différence.

Si vous avez des questions, veuillez contacter.

Des prédictions précises pour vous !

Rejoignez-nous!

Téléchargez des applications gratuites de prévision et de business intelligence:

• Novo Prévisions Lite- automatique calcul des prévisions V exceller.
• 4analyses- Analyse ABC-XYZ et analyse des émissions dans Exceller.
• Qlik Sense Bureau et QlikViewPersonal Edition - Systèmes BI pour l'analyse et la visualisation des données.

Testez les fonctionnalités des solutions payantes :

• Novo Prévisions PRO- prévisions dans Excel pour de grands tableaux de données.