Dites que vous voulez pouvoir compter des éléments uniques à partir d'une liste, mais avec une torsion. Et disons que vous travaillez avec cette feuille de calcul:
La colonne D compte le nombre de lignes dans chacune des sections de la colonne B et la colonne C compte le nombre de sections uniques en fonction des cinq premiers caractères de la colonne A pour cette section. Les cellules B2: B11 contiennent ARG et vous pouvez compter huit éléments uniques dans les cinq premiers caractères de A2: A11 car A7: A9 contiennent chacun 11158, de sorte que les deux doublons ne sont pas comptés. De même, le 5 dans D12 vous indique qu'il y a cinq lignes pour BRD, mais dans les lignes 12:16, il y a trois éléments uniques des cinq premiers caractères, puisque 11145 est répété et 11173 est répété.
Mais comment dites-vous à Excel de faire cela? Et quelle formule pourriez-vous utiliser dans C2 qui pourrait être copiée en C12 et C17?
La formule de comptage simple dans D2,, =COUNTIF(B:B,B2)compte le nombre de fois que B2 (ARG) existe dans la colonne B.
Vous utilisez une colonne d'assistance pour isoler les cinq premiers caractères de la colonne A, comme dans cette figure:
Ensuite, vous devez en quelque sorte indiquer que pour ARG, vous n'êtes intéressé que par les cellules F2: F11 pour trouver le nombre d'éléments uniques. En général, vous trouverez cette valeur en utilisant la formule matricielle illustrée dans cette figure:
Vous utilisez temporairement la cellule C3 pour afficher la formule; vous pouvez voir qu'il n'est pas présent dans C3 dans les figures précédentes. (Vous apprendrez bientôt comment cette formule fonctionne.)
Alors, quelle est la formule en C2, C12 et C17? La réponse surprenante (et cool) est montrée dans cette figure:
Whoa! Comment cela marche-t-il?
Jetez un œil à Answer dans les noms définis dans cette figure:
C'est la même formule que celle d'une figure précédente, mais au lieu d'utiliser la plage F2: F11, elle utilise une plage nommée Rg. De plus, la formule était une formule matricielle, mais les formules nommées sont traitées comme s'il s'agissait de formules matricielles! Autrement dit, =Answern'est pas entré avec Ctrl + Maj + Entrée mais est simplement entré comme d'habitude.
Alors, comment Rg est-il défini? Si la cellule C1 est sélectionnée (ce qui est une étape importante pour comprendre cette astuce), elle est définie comme dans cette figure:
Voilà =OFFSET(Loan_Details!$F$1,MATCH(Loan_Details!$B1,Loan_Details!$B:$B,0)-1,0,COUNTIF(Loan_Details!$B:$B,Loan_Details!$B1),1).
Loan_Details est le nom de la feuille, mais vous pouvez regarder cette formule sans le long nom de la feuille. Un moyen simple de le faire est de nommer temporairement la feuille quelque chose de simple, comme x, puis de regarder à nouveau le nom défini:
Cette formule est plus facile à lire!
Vous pouvez voir que cette formule correspond à $ B1 (notez la référence relative à la ligne actuelle) contre toute la colonne B et soustrait 1. Vous soustrayez 1 parce que vous utilisez OFFSET de F1. Maintenant que vous connaissez la formule de C, jetez un œil à celle de C2:
La MATCH($B2,$B:$B,0)partie de la formule est 2, donc la formule (sans la référence au nom de la feuille) est:
=OFFSET($F$1,2-1,0,COUNTIF($B:$B,$B2),1)
ou:
=OFFSET($F$1,1,0,COUNTIF($B:$B,$B2),1)
ou:
=OFFSET($F$1,1,0,10,1)
Parce que COUNTIF($B:$B,$B2)c'est 10, il y a 10 ARG. Il s'agit de la plage F2: F11. En fait, si la cellule C2 est sélectionnée et que vous appuyez sur F5 pour aller à Rg, vous voyez ceci:
Si la cellule de départ était C12, appuyer sur F5 pour aller à Rg produit ceci:
Alors maintenant, avec Answer défini comme =SUM(1/COUNTIF(rg,rg)), vous avez terminé!
Regardons de plus près comment cette formule fonctionne, en utilisant un exemple beaucoup plus simple. Normalement, la syntaxe de COUNTIF est =COUNTIF(range,criteria), comme =COUNTIF(C1:C10, "b")dans cette figure:
Cela donnerait 2 comme nombre de b dans la plage. Mais le passage de la plage elle-même en tant que critère utilise chaque élément de la plage comme critère. Si vous mettez en évidence cette partie de la formule:
et appuyez sur F9, vous voyez:
Chaque élément de la plage est évalué, et cette série de nombres signifie qu'il y a un a et il y a deux b, trois c et quatre d. Ces nombres sont divisés en 1, donnant 1, ½, ½, ⅓, ⅓, ⅓, ¼, ¼, ¼, ¼, comme vous pouvez le voir ici:
Donc, vous avez 2 moitiés, 3 tiers, 4 quarts et 1 entier, et en les additionnant, vous obtenez 4. Si un élément était répété 7 fois, alors vous auriez 7 septièmes et ainsi de suite. Plutôt cool! (Chapeau à David Hager pour avoir découvert / inventé cette formule.)
Mais attendez une minute. Dans l'état actuel des choses, vous ne devez saisir cette formule que dans C2, C12 et C17. Ne serait-il pas préférable de le saisir dans C2, de le remplir et de ne l'afficher que dans les bonnes cellules? En fait, vous pouvez le faire. Vous pouvez modifier la formule dans C2 pour qu'elle soit =IF(B1B2,Answer,""), et lorsque vous la remplissez, elle fait le travail:
Mais pourquoi s'arrêter ici? Pourquoi ne pas transformer la formule en une formule nommée, comme indiqué ici:
Pour que cela fonctionne, la cellule C2 doit être la cellule active (ou la formule doit être différente). Vous pouvez maintenant remplacer les formules de la colonne C par =Answer2:
Vous pouvez voir que C3 a =Answer2, comme toutes les cellules de la colonne C. Pourquoi ne pas continuer cela dans la colonne D? La formule en D2, après avoir également appliqué la comparaison à B1 et B2, est montrée ici:
Donc, si vous gardez la cellule D2 sélectionnée et définissez une autre formule, dites Réponse3:
alors vous pouvez entrer =Answer3dans la cellule D2 et remplir:
Voici la partie supérieure de la feuille de calcul, avec les formules affichées, suivie de la même capture d'écran avec les valeurs affichées:
Lorsque d'autres personnes essaient de comprendre cela, elles peuvent se gratter la tête au début!
Cet article invité provient d'Excel MVP Bob Umlas. Il est tiré du livre More Excel Outside the Box. Pour voir les autres sujets du livre, cliquez ici.
