John a une feuille de calcul qui contient les enregistrements utilisés dans un système de suivi des coûts. Les numéros d’enregistrement sont entrés dans la colonne A, les emplacements dans la colonne B et les coûts dans la colonne C. Tous les enregistrements n’ont pas une valeur de coût entrée dans la colonne C. Jean veut déterminer un nombre d’enregistrements «avec l’emplacement X et le coût <> 0».

Votre première impulsion peut être d’utiliser l’une des fonctions de feuille de calcul conçues pour le comptage, telles que CountIf. Le seul problème est que CountIf ne permet pas de vérifier deux conditions dans le calcul d’une solution. Il existe cependant quelques solutions que vous pouvez utiliser, sans avoir besoin d’ajouter des colonnes supplémentaires ou des calculs intermédiaires.

La première solution (et peut-être la plus simple) consiste à utiliser la fonction de feuille de calcul SOMMEPROD. Cette fonction vous permet de compter ou de additionner les données d’une colonne, d’une ligne ou d’un tableau avec autant de critères que vous le souhaitez. La syntaxe de base est la suivante:

=SUMPRODUCT( (CONDITION1)  (CONDITION2)  (CONDITION3) * (DATACELLS) )

Dans ce cas particulier, vous pouvez mettre la formule ensemble comme ceci:

=SUMPRODUCT((B2:B101="X")*(C2:C101>0))

Cela fournit deux conditions différentes qui sont vérifiées.

Tout d’abord, les cellules de la colonne B sont vérifiées pour voir si elles sont égales à «X», puis les cellules correspondantes de la colonne C sont vérifiées pour voir si elles sont égales à 0. Les deux conditions renvoient Vrai (1) ou Faux (0). Ces résultats sont ensuite multipliés les uns par les autres, ce qui donne 1 ou 0. La fonction SOMMEPROD les ajoute ensuite, ce qui entraîne un décompte cumulatif.

Une autre solution consiste à créer une formule matricielle qui fera le calcul pour vous. Les formules matricielles sont différentes des formules régulières, en ce sens qu’elles fonctionnent sur un certain nombre de cellules, en les parcourant pour produire un résultat. Considérons la formule suivante:

=(B2="X")*(C2>0)

Cela renvoie une valeur unique, 1 ou 0. La formule utilise la même logique de base décrite dans l’explication précédente de la solution SUMPRODUCT. Les deux comparaisons logiques renvoient 1 ou 0, qui sont multipliés les uns par les autres, ce qui donne 1 ou 0 comme réponse. Maintenant, considérons la formule suivante:

=SUM((B2:B101="X")*(C2:C101>0))

Cela ressemble maintenant beaucoup à la formule précédente SUMPRODUCT, mais cela ne fonctionnera pas correctement comme une formule simple. En effet, SUM n’est pas conçu pour fonctionner de manière itérative sur une plage de cellules. Si vous entrez cette formule sous forme de formule matricielle (appuyez sur Maj + Ctrl + Entrée pour l’entrer), Excel comprend que vous souhaitez travailler sur chacune des plages, à son tour, pour calculer la somme finale, qui est un nombre d’enregistrements qui répondre aux critères énoncés.

Les différentes façons dont vous pouvez utiliser les formules matricielles constituent un sujet assez vaste.

Pour plus d’informations sur le fonctionnement des formules matricielles, consultez les autres numéros de _WordTips, _ ou consultez le site Web suivant:

http://www.cpearson.com/excel/ArrayFormulas.aspx

Une troisième option consiste à utiliser les fonctions de feuille de calcul de la base de données pour renvoyer un décompte. À l’aide de ceux-ci, vous définissez une «table de critères» dans votre feuille de calcul, puis la fonction utilise les critères pour analyser les enregistrements. Les étapes suivantes supposent que les étiquettes de colonne pour les trois colonnes sont RecNum, Location et Cost:

  1. Trouvez quelques cellules vides, soit sur la même feuille de calcul que vos enregistrements, soit sur une autre feuille de calcul. (Pour cet exemple, je suppose que vous utilisez les colonnes J et K.)

  2. Dans la cellule J1, entrez le mot Emplacement.

  3. Dans la cellule K1, entrez le mot Coût.

  4. Dans la cellule J2, entrez X.

  5. Dans la cellule K2, entrez> 0. Vous avez maintenant entré votre tableau de critères dans les cellules J1: K2.

  6. Sélectionnez les cellules J1: K2.

  7. Choisissez Nom dans le menu Insertion, puis choisissez Définir. Excel affiche la boîte de dialogue Définir le nom. (Voir la figure 1.)

  8. Entrez le nom Critères, puis cliquez sur OK.

  9. Dans la cellule où vous souhaitez un nombre d’enregistrements répondant à vos critères, saisissez ce qui suit:

=DCOUNT(B1:C101,2,Criteria)

Notez que le premier argument utilisé avec DCOUNT est les deuxième et troisième colonnes de votre liste d’enregistrements. Cet argument inclut également les étiquettes de colonne, qui sont nécessaires pour que DCOUNT puisse localiser les correspondances de critères appropriées dans la table des critères (troisième argument).

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (2815) s’applique à Microsoft Excel 97, 2000, 2002 et 2003.