Connaître le nombre de lignes ou caractères dans une cellule, une sélection

NOMBRE DE ...

  ... LIGNES/COLONNES dans une selection

Par formule : =lignes(A1:A10) / =colonnes(A1:C1)
Par VBA : selection.rows.count / selection.columns.count
Par VBA EXCEL 5 : selection.lignes.nombrede

... CELLULES Si

=nb.si(A:A;"toto")
=nb.si(A:A;"*total*")
application.countif(columns(1), "toto")
application.countif(selection, "toto")
application.countif(sheets("feuil1").range("B:B"), "toto")
application.countif(sheets("feuil1").range("B:B"), nomvariable)

... VALEURS

=nbval(Feuil1!A1:10)
nb_valeurs = application.counta(range("A:A"))

... VALEURS DIFFERENTES

=SOMME(1/NB.SI(Feuil1!A1:10;Feuil1!A1:10)) 'formule matricielle
=SOMME(N(Frequence(Plage;Plage)>0)) 'formule matricielle

... CELLULES à la fois supérieures à 10 et donc le reste d'une division par 10 est différent de 0

Exemple : dans 2-12-22-32-40-403
={SOMME((Plage>=10)*(Plage<100)*(MOD(Plage;10)<>0))} formule matricielle
=> Resultat = 3

... CELLULES aux valeurs comprises entre 1 & 5, dans la plage A1 à A29

=COUNTIF(A1:A29;">=1")-COUNTIF(A1:A29;">5")
voilà une fonction personnalisée en VBA : news

... FEUILLES où A1, parmi les feuilles Feuil1 à Feuil9

=SUM(COUNTIF(INDIRECT("sheet"&{1,2,3,4,5,6,7,8,9}&"!A1"),10)) (formule matricielle)
=SUM(COUNTIF(INDIRECT("sheet"&ROW(INDIRECT("1:9"))&"!A1"),10)) (formule matricielle)

... DECIMALES

=MAX(NBCAR(A1)-NBCAR(ENT(A1))-1;0)
=MAX(NBCAR(TEXTE(MOD(A1;1);"@"))-2;0)

voilà une fonction personnalisée en VBA :

Function HowLong(dNum As Double)
If InStr(CStr(dNum), ".") = 0 Then
HowLong = 0
Exit Sub
End If
HowLong = Len(CStr(dNum)) - Len(Right(dNum, InStr(CStr(dNum), ".")))
End Function


... VALEURS PAIRES : news

.. VALEURS égales à X et Récurrence

=SI(A1="chocolat";NB.SI(A$1:A1;"chocolat");""), à recopier vers le bas

 

SOMME et SOMME SI ...

Somme d'une plage de cellules

=somme(A1:A10)

Utiliser la somme automatique d'Excel
Application.CommandBars("Auto Sum").FindControl(ID:=226).Execute : ' SendKeys "~" 'pour valider
Somme si

somme des cellules de la plage A8:Z8 sous condition que leur valeur soit égale à "12h30"
=SOMME(SI(A8:Z8="12h30";1;0)) 'formule matricielle à valider par Ctrl Maj Entrée

somme d'une plage Montants si la plage Dates contient des dates en l'année 2000 (par AV)
=SOMME(SI(TEXTE(Dates;"aaaa")="2000";montants)) 'formule matricielle à valider par Ctrl Maj Entrée
autres exemples d'AV : news

Somme selon plusieurs critères =SOMME(SOMME.SI(Plage;{">=12";"<=40";"<>"})*{1;1;-1})

Explication de L.L. : news 'formule matricielle à valider par Ctrl Maj Entrée

Compte & addition en vba de valeurs dans un intervalle (20 à 49)
Exemple par LL dans le groupe de discussion microsoft.public.fr.excel le lundi 10 avril 2000 à 19:48, sujet "Re: tri de cellules et addition"

with Worksheetfunction
Nombre = .CountIf(range("Quantité"), ">=20").CountIf(range("Quantité"), ">49")
Somme = .SumIf(range("Quantité"), ">=20").SumIf(range("Quantité"), ">49")
msgbox Nombre
end with
Somme des cellules visibles uniquement : news

 

AUTRES DETERMINATIONS

Méthodes pour trouver ou sélectionner la derniere valeur : news Valeur maximale ou minimale

=MAX($A$1:$A$10) ou =MIN($A$1:$A$10)

Minimum d'une plage sans valeur zéro

{=MIN(SI(A1:A5<>0;A1:A5))} 'formule matricielle à valider par Ctrl Maj Entrée

Minimum d'une plage B1:B100 pour les lignes où A1:A100 contient le mot "Zaza"

=MIN(SI(A1:A100="Zaza";B1:B100)) 'formule matricielle à valider par Ctrl Maj Entrée

si le mot Zaza risque de ne pas être présent, préférer la formule matricielle suivante
=SI(OU(A1:A100="Zaza");MIN(SI(A1:A100="Zaza";B1:B100));#N/A)

Trouver les 5 plus grandes valeurs/plus petites Sélectionner 5 cellules dans une colonne, puis édition formule [F2] :
=GRANDE.VALEUR(maPlage;{1;2;3;4;5})
validation matricielle.
(maPlage est la plage cellules contenant la liste de valeurs, genre A2:A99) faire un filtre automatique et tu sélectionnes *10 premiers...*. =GRANDE.VALEUR(maplage;LIGNE(INDIRECT("1:5")))
=GRANDE.VALEUR(maplage;LIGNE(INDIRECT("2:5")))
=GRANDE.VALEUR(maplage;LIGNE(INDIRECT("3:5")))
=GRANDE.VALEUR(maplage;LIGNE(INDIRECT("4:5")))
=GRANDE.VALEUR(maplage;LIGNE(INDIRECT("5:5")))

Trouve la valeur la plus près de la moyenne

=INDEX(A1:A10,MATCH(MIN(ABS(A1:A10-AVERAGE(A1:A10))),ABS(A1:A10-AVERAGE(A1:A10)),0)) 'formule matricielle à valider par Ctrl Maj Entrée

=INDEX(A1:A10;equiv(MIN(ABS(A1:A10-moyenne(A1:A10)));abs(A1:A10-moyenne(A1:A10));0))

 

 


Plages filtrées & Nombre de valeurs numériques différentes

{=SOMME(N(FREQUENCE(SOUS.TOTAL(9;DECALER(Rge;LIGNE(INDIRECT("1:"&LIGNES(Rge))) -1;;1));SOUS.TOTAL(9;DECALER(Rge;LIGNE(INDIRECT("1:"&LIGNES(Rge)))-1;;1)))>0)) -(SOUS.TOTAL(3;Rge)NB(Rge))}

{=NB.DIFF(SOUS.TOTAL(9;DECALER(Rge;SUITE(Rge);;1)))-(SOUS.TOTAL(3;Rge)<NB(Rge))}

{=SUM(IF(FREQUENCY(IF(LEN(A1:A12)>0,MATCH(A1:A12,A1:A12,0),""),IF(LEN(A1:A12)>0,MATCH(A1:A12,A1:A12,0),""))>0,1))}
{=SOMME(SI(FREQUENCE(SI(NBCAR(A1:A12)>0;EQUIV(A1:A12;A1:A12;0);"");SI(NBCAR(A1:A12)>0;EQUIV(A1:A12;A1:A12;0);""))>0;1)

 

Fréquences

1) S'il s'agit de dénombrer les valeurs les plus fréquentes dans chacune des colonnes A1:A50 à J1:J50: En L1, saisis la formule =MODE(A1:A50), et recopie-la jusqu'à la cellule U1. Tu obtiendras les 10 valeurs les plus fréquentes de chaque colonne. Pour dénombrer ensuite les occurences de ces 10 valeurs dans la plage A51:J51, =SOMMEPROD(NB.SI(L1:U1;A51:J51)).

2) S'il s'agit de dénombrer les 10 valeurs les plus fréquentes de la A1:J50 *dans son ensemble*, indépendamment des colonnes:

En L1, saisis la formule =MODE(A1:J50) En L2 : =MODE(SI(NB.SI(L$1:L1;A$1:J$50)=0;A$1:J$50)), à valider par la combinaison de touches *Ctrl-Maj-Entrée* (formule matricielle), puis
recopie cette formule vers le bas jusqu'en L10. Ces valeurs sont les plus fréquentes de la plage A1:J50. Pour compter le nombre d'occurence de ces 10 valeurs dans la plage A51:J51, =SOMMEPROD(NB.SI(A51:J51;L1:L10)).

 

Dénombrement de chaînes de caractères

Nombre de CARACTERES dans une CELLULE =nbcar(A1) celluleactive.caractères.nombrede /selection.characters.count

Nombre de lettres A dans une plage de cellules =SOMME(N(STXT(A1:A8663;{1.2.3.4.5.6.7};1)="A")) à valider par Ctrl-Maj-Entrée

Nombre de cellules contenant du texte dans B3:B8 =somme(esterreur(B3:B8/B3:B8)*1), essayez aussi : =SOMME(N(ESTTEXTE(Plage))) Voir aussi actionchaine.htm Nombre de mots dans une cellule =LEN(SUBSTITUTE(TRIM(A1),CHAR(32),CHAR(32)&CHAR(32)) )-LEN(TRIM(A1))+1 Dernier mot dans une cellule =TRIM(CALL("Crtdll","strrchr","CCI",A1,32)) (call ) fonction.appelante)

Connaître le Numéro de la ligne/colonne d'une cellule

Récupérer le numéro de la ligne ou colonne à l'aide d'une formule :
=ligne()     /     =colonne()
=row()     /      =column()		 Récupérer le numéro de la ligne ou colonne à l'aide d'instructions VBA :
dim Plage As range
Set Plage = selection
msgbox "Ligne : " & Plage.Row &", Colonne :" &Plage.Column
'Vous pouvez directement utiliser selection.row / selection.column
Récupérer la lettre de la colonne à l'aide d'une formule :
=Left(activecell.address(0, 0), (activecell.Column < 27) + 2)
merci Laurent		 Récupérer la lettre de la ligne ou colonne à l'aide d'instructions VBA :
function ColumnLetter()
dim C As Integer, L As Integer
with activecell
C = .Column
if C > 26 then L = 2 Else L = 1
ColumnLetter = Left$(.address(0, 0), L)
end with
end function
merci Myrna

!!!!!!!! Récupérer le numéro de la colonne ou ligne après la recherche d'une valeur :

cells.find("A", , , xlWhole, , , true).Column 'ou Row pour ligne
cells.find("A", , , xlWhole, , , true).EntireColumn.select
Numéro de ligne du maximum : =EQUIV(MAX(A1:A50);A1:A50;0)

 

 

 


Nombre de cellules de telle couleur parmi une plage

Il faut créer deux nouvelles fonctions, BgColor et BgColorcountif où :

BgColor permet de connaître l'index de la couleur de fond de la cellulle de référence (A1 par exemple),
BgColorcountif permet de déterminer combien de cellules parmi A1:A10 ont la couleur recherchée, en l'occurence celle de A1.

Pour créer les fonctions BgColor et BgColorcountif, insérer le code des fonctions ci-dessous dans un module.

function BgColorcountif(SearchArea As Object, BgColor As Integer) As Integer
for each Cell In SearchArea
BgColorcountif = BgColorcountif + Abs(Cell.Interior.ColorIndex = BgColor)
next Cell
end function

function BgColor(CkCell As Object)
BgColor = Abs(CkCell.Interior.ColorIndex)
end function

Utilisation :
aller dans la cellule B1, écrivez la formule : =BgColorcountif(A1:A10;Bgcolor(A1))
Le tour est joué!!

ATTENTION : ces formules ne marchent pas si la plage de cellules fait l'objet d'une mise en forme conditionnelle. Pour un tel cas, essayez le code du texte suivant bgcolor.txt

Compter (additionner ...) le nombre de cellules de telle couleur : news
La macro suivante recherche le premier nombre (constant) en rouge dans la feuille active et le copie dans la cellule Feuil2!A1.

Sub Test()
dim Zone As range, Cell As range, CRouge As range
for each Zone In cells.Specialcells(xlCelltypeConstants)
for each Cell In Zone
if Cell.Font.ColorIndex = 3 then [Feuil2!A1] = Cell: Exit Sub
next Cell
next Zone
end Sub

DIVERS

A prenant les chiffres de 1 à 9... et en excluant la répétition d'un MEME CHIFFRE et en respectant l'ordre CROISSANT des chiffres:
** Comment lister toutes les variantes possibles d'un nombre à 3 chiffres (123,234,....etc..789) ? ... 312, 432 ou 987 sont exclus (chiffres rangés non croissants)
réponse par Bernard G.

Sub test()
sheets.Add
For i = 1 To 9
For j = i + 1 To 9
For k = j + 1 To 9
N = N + 1
Cells(N, "A") = 100 * i + 10 * j + k
Next k
Next j
Next i
End Sub