Compter des valeurs dans Excel
Nombre de valeurs dans une colonne
S'il est nécessaire de connaître le nombre de valeurs dans une colonne, ou plutôt dans une partie d'une colonne, la fonction '=NB()', fournit cette information. Ceci fonctionne également pour les dates, mais pas avec des valeurs alphanumériques :
Pour compter les valeurs alphanumériques, le truc suivant sert bien: On spécifie la taille de la plage à traiter (40000 lignes par exempls) et déduit ensuite le nombre de cellules vides trouvées dans la plage. L'exemple suivant (pour l'instant encore avec un Excel en Allemand) le démontre :
On peut voir que la formule '=D3-NB.VIDES(DECALER(D6;0;0;D3))' compte les cellules non vides sur les 40'000 lignes dès cellule "D6", aussi si des cellules vides sont parsemées.
Dernière ligne contenant une valeur
Si on cherche la dernière ligne contenant une valeur dans une plage, Excel ne nous offre pas de solution directe. Cependant, il peut être très convivial se connaître l'étendu de la plage de données par exemple pour des calculs matriciels qui doivent contenir précisément les données, même si des valeurs vides sont permises. Dans la discussion suivante, nous allons progresser vers des formulations de plus en plus compactes en termes de taille du classeur sur disque.
Variante A
Dans l'exemple précédent, nous souhaitons trouver la dernière ligne contenant une valeur non-vide dans la colonne "D". Nous nous servons d'une colonne avec des calculs auxiliaires qui affichent un numéro de ligne si la colonne "D" contient une valeur et zéro sinon. Dans la cellule "E6", c'est-à-dire la première cellule des calculs auxiliaires, nous saisissons '=LIGNE()*(1-(D6=""))' et étirons la formule ensuite jusqu'à la cellule "E15".
La soustraction '(1-())' donne raison à la conversion du résultat logique de la comparaison '(D6="")' et correspond à une négaction 'NON()'. L'expression '(1-(D6=""))' rend zéro pour les cellules vides et 1 autrement. Or, nous pouvons directement multiplier par 'LIGNE()'.
La valeur maximale de la plage "E6:E15" est le numéro de ligne cherché.
En préparation de la variant qui marche sans colonne auxiliaire, nous reformulons les calculs de la colonne "E" par des formules matricielles. Pour des raisons de simplicité, nous utilisons des références fixes aux plages :
'{=LIGNE(D6:D15)*(1-(D6:D15=""))}'
La formule es saisie en sélectionnant la plage du calcul et saisissant ensuite la formule (sans les accolades) et finissant par "Ctrl"+"Maj"+"Valider". La formule est mise entre accolades, ce qui indique une formule matricielle. On peut vérifier que les deux types de formules fournit les mêmes résultats, mais se distinguent de manière considèrable par le fait que la fonction 'LIGNE()' traite toute une plage et renvoie le numéro de ligne dans la plage comme élément d'une matrice. L'expression '(D6:D15="")' est également évaluée sur la plage spécifiée et rend les valeurs FAUX et VRAI pour chaque cellule de la plage en tant que matrice aux mêmes dimensions que celle des numéros de ligne.
Le résultat de l'expression entière est de nouveau une matrice dont les éléments sont affichées dans la plage "G6:G15".
Variante B
Pour déterminer la dernière ligne de la colonne "D" conenant une valeur non vide, nous n'avons pas besoin d'afficher les valeurs de la matrice générée par l'expression '{=LIGNE(D6:D15)*(1-(D6:D15=""))}'. Nous pouvons directement envoyer la matrice vers la fonction 'MAX()', ce qui rend la valeur cherchée. Nous saisissons donc dans une cellule, par exemple "E4", la formule matricielle suivante :
'{=MAX(LIGNE(DECALER(D6;0;0;D3))*(1-(DECALER(D6;0;0;D3)="")))}'
qui nous fournit le résultat, et ceci sans passer par une colonne auxiliaire. L'expression 'DECALER(D6;0;0;D3)' correspond à la plage "D6:D40005", mais permet d'adapter facilement l'étendu de la plage en modifiant la valeur de D3.
Commentaires :