Formules Matricielles

Accueil

Formule Matricielle
Sommeprod
Dénombrement
EliminationNA
Constantes Matricielles
Exemples
Fonction Perso matricielle

-Calendrier annuel
-Compter les occurences uniques
-Compter les occurences uniques avec critère
-Occurences uniques sur zones filtrée
-Le plus fréquent
-Recherche d'un mot dans un champ
-Recherche suivant 2 critères
-Recherche avec résultats multiples
-Filtre Base de données
-Appartenance à un ensemble
-Total par ensemble
-Exclusion
-Somme 1 ligne sur 2
-Somme champ conditionnelle
-Somme conditionnelle avec entête
-Recherche valeurs associées à un nom
-Recherche multiple
-Recherche d'un code dans un champ
-Liste sans doublons
-Liste sans doublons conditionnelle
-Liste sans Vide
-Liste conditionnelle
-Liste conditionnelle avec fonction personnalisée
-Liste différence
-Classement avec égalité
-Chaînes de caractères
-Partie numérique d'une chaîne
-Dates
-Tri alpha matriciel
-Tri avec doublons
-Extrait de Compte
-Recherche dans un champ multi-zones
-Recherche valeurs alpha associées à un nom avec transposition
-Recherche sur une partie de code
-Remarque sur l'écriture des formules matricielles
-Fonction Perso pour remplacer SommeProd()
-
Fonction perso matricielle
-
Somme BD par catégorie

Matriciel3D

Formule matricielle

Matricielles Synthèse

Pour calculer le total Prix*Qte en colonne D, la méthode classique consiste à écrire la formule
=B2*C2 en D2 puis à recopier la formule dans la colonne.

Les formules matricielles opèrent sur des matrices.

-Sélectionner D2:D7
=B2:B7*C2:C7
-Valider avec Maj+Ctrl+entrée

On obtient une formule matricielle {=B2:B7*C2:C7}

SommeProd()

Les fonctions Nb.si() et Somme.Si() sont limitées à une condition.
La fonction SommeProd() permet d'obtenir plusieurs conditions:

SommeProd((champ1=condition1)*(champ2=conditions2)*(.....))

ou

SommeProd(--(champ1=condition1);--(champ2=conditions2);(.....))

Cette fonction effectue des opérations sur des matrices.

-Les champs doivent avoir la même taille
-Les conditions doivent être placées entre ()
-Ne pas utiliser Et()/Ou(): Utiliser * et +
-Utiliser *1 pour convertir les Vrai/Faux en 1/0 ou --
-Nommer les champs pour une meilleure lisibilité et portabilité des formules

Sur l'exemple ci dessous, on compte le nombre de personnes appartenant au service compta
ET qui ont la qualification Q1. - SommeProd -

=SOMMEPROD((B2:B10="Compta")*(C2:C10="Q1"))

Si les champs B2:B10 ont étés nommés Service et Qualif:

=SOMMEPROD((Service="Compta")*(Qualif="q1"))

ou

=SOMMEPROD(--(Service="Compta");--(Qualif="Q1"))

Pour obtenir la somme des salaires:
=SOMMEPROD((Service="Compta")*(Qualif="q1")*D2:D10)

ou

=SOMMEPROD(--(Service="Compta");--(Qualif="q1");D2:D10)

Remarque:Les fonctions BdSomme(), BdNbval(),.. sont plus rapides mais nécessitent un champ critère.

Autre exemple

Sur cet exemple, on compte le nombre d'hommes qui ont un salaire>3000.

=SOMMEPROD((Genre="H")*(Salaire>3000))

La somme des salaires est: =SOMMEPROD((Genre="H")*(Salaire>3000)*Salaire)

Noms de champ
Genre  $C$4:$C$12
Salaire $D$4:$D$12

Dénombrement

Sur l'exemple, on veut le nombre de produits pour lesquels le CaRéel est supérieur au CaPrévu

=SOMMEPROD((CaRéel>CaPrévu)*1)

*1 transforme les valeurs booléennes en 0/1

Autres écritures:
=SOMMEPROD(--(CaRéel>CaPrévu))
=SOMMEPROD(N(CaRéel>CaPrévu))

Elimination du texte dans les zones numériques

Total: =SOMMEPROD(--(Ref="x");Qte)

Nb lignes: =SOMMEPROD(--(Ref="x"))

Elimination du texte dans les zones dates

=SOMME(SI(ESTNUM(Date);--(MOIS(Date)=D2)))
valider avec Maj+ctrl+entrée

Cumul par mois

Cumul km par mois

Elimination de #N/A

Sur l'exemple, on veut faire la somme des montants.
La colonne des montants contient #N/A

=SOMME((code=E2)*SI(ESTNA(montant);0;montant))
Valider avec Maj+ctrl+entrée

Constantes matricielles

{2;3;4} représente un vecteur colonne.
{2.3.4} représente un vecteur ligne.
{0."Nul";25."Très Moyen";50."Moyen";80."Très Bien"} représente un tableau de 4 lignes et 2 colonnes

=SOMMEPROD({2;3;4}*{4;5;6}) donne le produit scalaire( 2*4)+(3*4)+(4*6)
=SOMMEPROD({2;3;4}+{4;5;6}) donne la somme (2+4)+(3+5)+(4+6)

Sur cet exemple, nous obtenons les 3 plus grandes valeurs d'un champ.

=GRANDE.VALEUR(A2:A7;{1;2;3})

En A1, on a A,A+,A-,A,B,... On veut obtenir une valeur 7,8,6,..

=INDEX({7;8;6;4;5;3;1;2;0};EQUIV(A1;{"A";"A+";"A-";"B";"B+";"B-";"C";"C+";"C-"};0))

Donne une appréciation sur une note en B6

=RECHERCHEV(B6;{0."Nul";25."Très Moyen";50."Moyen";80."Très Bien"};2;VRAI)

Un vecteur colonne {1;2;3;4;...10} peut être remplacé par:

=LIGNE(1:10)

Un vecteur colonne variable se génère avec:

=LIGNE(INDIRECT("1:"&LIGNES(champ)))

Un vecteur ligne variable se génère avec:

=COLONNE(INDIRECT("1:"&COLONNES(champ)))

Pour générer janvier,février,...,Décembre

janvier
février
mars
....
Décembre

-Sélectionner 12 cellules
=TEXTE(DATE(;LIGNE(1:12);1);"mmmm")
-Valider avec Maj+ctrL+entrée

Pour générer dimanche,lundi,mardi,...

-Sélectionner 7 cellules
=TEXTE(DATE(;1;LIGNE(1:7));"jjjj")
-Valider avec Maj+ctrL+entrée

Pour générer A,B,C,...

-Sélectionner 26 cellules verticales
=CAR(LIGNE($65:$90))
-Valider avec Maj+ctrl+entrée

Calendrier annuel

Pour créer un calendrier annuel 2009 avec une seule formule matricielle:

-Sélectionner un champ de 12 colonnes et 31 lignes
=DATE(2009;COLONNE(1:12);LIGNE(1:31))
-Valider avec Maj+Ctrl+Entrée

CalendrierAnnuel

Pour ne pas afficher le 31 des mois à 30 jours, utiliser la MFC ou

=SI(MOIS(DATE(an;COLONNE(1:12);LIGNE(1:31)))=COLONNE(1:12);DATE(an;COLONNE(1:12);LIGNE(1:31));"")

Exemples

Vérifier que 2 listes contiennent les mêmes éléments

=SI(SOMMEPROD(NB.SI(liste1;liste2))=NBVAL(liste1);"ok";"NoOk")

Compter les occurences uniques

=SOMMEPROD(1/NB.SI(A2:A8;A2:A8))

Si cellules vides dans le champ:

=SOMME(SI(A1:A8<>"";1/NB.SI(A1:A8;A1:A8)))
Valider avec Maj+Ctrl+entrée

Occurences uniques numérique avec la fonction Frequence(données,intervalle)

-Avec ou sans cellules vides dans le champ

=NB(1/FREQUENCE(champ;champ))
ou
=SOMMEPROD(--(FREQUENCE(champ;champ)>0))

-Si 0 dans le champ

=NB(1/FREQUENCE(SI(champ>0;champ);champ))
ou
=SOMMEPROD(--(FREQUENCE(SI(champ>0;champ);champ)>0))

Frequence
CompterOccurences
ValUniquesConcat

Ces formules matricielles deviennent lentes pour un nombre d'éléments important (>1sec pour 4000 items).
La fonction personnalisée NbDiff(champ) donne 0,015sec pour 4000 items (dans un module :Alt+F11 puis insertion/module)

Function NbDiff(champ)
  Set MonDico = CreateObject("Scripting.Dictionary")
  a = champ
  For Each c In a
    If c <> "" Then MonDico(c) = c
  Next c
  NbDiff = MonDico.Count
End Function

Dans le tableur: =NbDiff(A1:A10000)

Nombre d'items différents sur champs discontinus

=itemsdiff((A2:A11;D2:D11))

ItemsDiffChampDiscontinu

Function ItemsDiff(champ As Range)
  Application.Volatile
  Set d = CreateObject("Scripting.Dictionary")
  For Each c In champ
    If c.Value <> "" Then d(c.Value) = ""
 Next c
 ItemsDiff = d.Count
End Function

Occurences uniques texte avec Frequence(données,intervalle)

=NB(1/FREQUENCE(EQUIV(chmp;chmp;0);LIGNE(INDIRECT("1:"&LIGNES(chmp)))))

Si cellules vides dans le champ
=NB(1/FREQUENCE(SI(chmp<>"";EQUIV(chmp;chmp;0));LIGNE(INDIRECT("1:"&LIGNES(chmp)))))
Valider avec maj+ctrl+entrée

Occurences uniques numérique avec critère

=NB(1/FREQUENCE(SI(critere="x";ch);ch))
Valider avec maj+ctrl+entrée

FrequenceCritere

Autres exemples

Occurence2
Occurence3

Occurences uniques texte avec critère

=NB(1/FREQUENCE(SI(critere="x";EQUIV(ch;ch;0));LIGNE(INDIRECT("1:"&LIGNES(ch)))))
Valider avec Maj+Ctrl+Entrée

FrequenceCritereTexte

Si le champ ch comporte des vides

=NB(1/FREQUENCE(SI((critere="x")*(ch<>"");EQUIV(ch;ch;0));LIGNE(INDIRECT("1:"&LIGNES(ch)))))

Avec fonction personnalisée

Function ItemsDifferentsCritere(champ, champcritere, critere)
  Set MonDico = CreateObject("Scripting.Dictionary")
  a = champ
  b = champcritere
  For i = 1 To champ.Count
    If b(i, 1) = critere And a(i, 1) <> "" Then
       temp = a(i, 1)
       MonDico(temp) = temp
    End If
  Next i
  ItemsDifferentsCritere = MonDico.Count
End Function

Dans le tableur:=ItemsDifferentsCritere(A1:A4000;B1:B4000;"x")

FonctionPerso
FonctionPerso2

Pour plusieurs critères, sous forme matricielle (+ rapide)

Function ItemsDifferentsCritereMat(champ As Range, champcritere As Range, critere As Range)
  Set mondico = CreateObject("Scripting.Dictionary")
  a = champ
  b = champcritere
  Dim retour()
  ReDim retour(1 To critere.Count)
  For k = 1 To critere.Count
     For i = 1 To champ.Count
       If b(i, 1) = critere(k) And a(i, 1) <> "" Then
         temp = a(i, 1)
         mondico(temp) = temp
       End If
     Next i
     retour(k) = mondico.Count
     mondico.RemoveAll
   Next k
   ItemsDifferentsCritereMat = Application.Transpose(retour)
End Function

Exemple avec 2 critères

NombreNnomsDifférents2Critères
FonctionNombreItemsDifférents2Critères

Autre exemple

=NB(1/FREQUENCE(SI(GAUCHE(champ;2)="CP";EQUIV(champ;champ;0));LIGNE(INDIRECT("1:"&LIGNES(champ)))))
Valider avec Maj+ctrl+entrée

Occurences uniques dates pour une semaine

=NB(1/FREQUENCE(SI(Sem=D2;dates);dates))
Valider avec Maj+ctrl+entrée

Occurences uniques dates par Nom/Semaine

=NB(1/FREQUENCE(SI((SEM=$E4)*(NOM=F$3);DATE);DATE))
valider avec Maj+ctrl+entrée

NombreDatesNomSem
NombreDatesMatriculeFonction

On veut le nombre d'occurences uniques Client-Référence
pour les références commençant par FV,FA,FD

En F2:=NB(1/FREQUENCE(SI(GAUCHE(ref;2)=$E2;EQUIV(client&ref;client&ref;0));LIGNE(INDIRECT("1:"&LIGNES(client)))))
valider avec Maj+ctrl+entrée
En F9: =NB(1/FREQUENCE(SI((GAUCHE(ref;2)=$E2)*(client=F$8);EQUIV(ref;ref;0));LIGNE(INDIRECT("1:"&LIGNES(client)))))
valider avec Maj+ctrl+entrée

Un nombre d'items très important peut conduire à des temps de calcul trop élevés. Dans ce cas, utiliser une requête SQL :

Select Vendeur,COUNT(*) AS Ttal FROM (SELECT DISTINCT Vendeur,Cmd From MaBD) GROUP BY vendeur

ADOGroupBY

Occurences uniques avec 1 critere avec fonction personnalisée

Avec une fonction personalisée, le calcul du nombre d'occurences uniques est beaucoup plus rapide.

Items Différents 1 ou 2 Critères
Items Différents 1 Critère Date

Autre exemple

Items Différents 1 Critère ou 2 critères

-Sélectionner F2:H18
=NbValUniques(C2:C100;A2:A100;B2:B100)
-Valider avec maj+ctrl+entrée

Solution formule (plus lent)
=NB(1/FREQUENCE(SI((critère1=K2)*(critère2=L2);EQUIV(Valeurs;Valeurs;0));LIGNE(INDIRECT("1:"&LIGNES(Valeurs)))))
Valider avec maj+ctrl+entrée

Occurences uniques sur une zone filtrée

=SOMME(--(FREQUENCE(SI(SOUS.TOTAL(3;DECALER(Nom;LIGNE(INDIRECT("1:"&LIGNES(Nom)))-1;;1));
EQUIV(Nom;Nom;0));LIGNE(INDIRECT("1:"&LIGNES(Nom))))>0))
valider avec maj+ctrl+entrée

ou

=SOMME(--(FREQUENCE(SI(SOUS.TOTAL(3;INDIRECT("A"&LIGNE(Nom)));EQUIV(Nom;Nom;0));
LIGNE(INDIRECT("1:"&LIGNES(Nom))))>0))
valider avec maj+ctrl+entrée

FiltreOuccurUniques

Le nombre ou mot le plus fréquent dans une liste

Numérique: =MODE(champ)
Texte sans vides:
=INDEX(Champ;MODE(EQUIV(champ;Champ;0)))
Valider avec Maj+ctrl+entrée

Texte avec vides: =INDEX(champ;MODE(SI(champ<>"";EQUIV(champ;champ;0))))
Valider avec Maj+ctrl+entrée

Le second mot le + fréquent
=INDEX(champ;MODE(SI((champ<>"")*(champ<>D1);EQUIV(champ;champ;0))))
Valider avec Maj+ctrl+entrée

PlusFréquent

Avec critère
=INDEX(champ;MODE(SI((champ<>"")*(caté="caté1");EQUIV(champ;champ;0))))

Temps pour 4000 items 2,75 s

Fonction personnalisée (0,04s pour 4000 items)

Function MotPlusFrequent(champ)
  Set MonDico = CreateObject("Scripting.Dictionary")
  a = champ
  For Each c In a
    MonDico(c) = MonDico(c) + 1
  Next c
  m = 0
  For Each c In MonDico
    If MonDico.Item(c) > m Then m = MonDico.Item(c): temp = c
  Next c
  MotPlusFrequent = temp
End Function

Plus Fréquent Critère
Frequence Texte
Frequence Texte Classement
Frequence Texte critère
Frequence Texte Date
Frequence Texte Mac
Frequence Texte Date Mac

Fréquence avec SQL

Frequence SQL

Sql = "SELECT vendeur,COUNT(*) AS ttal FROM mabd GROUP BY vendeur ORDER BY COUNT(*) DESC"

Somme d'un champ sans les doublons

=SOMME(SI(champ<>"";champ*(1/NB.SI(champ;champ))))
Valider avec Maj+ctrl+entrée

Doublon dans un champ

Donne VRAI s'il y a au moins un doublon dans le champ.

NbSiDoublons

=SOMMEPROD(--(NB.SI(champ;champ)>1))>0

Recherche mot dans un champ

-Recherche le numéro de ligne d'un mot dans un champ
-Donne la valeur associée dans une autre table

=INDEX(resultat;MAX(SI(champ=G2;LIGNE(INDIRECT("1:"&LIGNES(champ))))))
Valider avec Maj+ctrl+entrée

Recherche Mot Champ
Recherche Mot Champ2

Si le mot existe plusieurs fois, pour obtenir toutes les lignes de résultat

=SI(LIGNES($1:1)<=NB.SI(champ;$G$2);INDEX(resultat;
PETITE.VALEUR(SI(champ=$G$2;LIGNE(INDIRECT("1:"&LIGNES(champ))));LIGNES($1:1)));"")
Valider avec Maj+ctrl+entrée

Recherche avec 2 conditions

On fait une recherche sur 2 critères (Nom+ Prénom)

=INDEX(Villes;EQUIV(1;(Noms=F2)*(Prénoms=G2);0))
valider avec Maj+Ctrl+entrée

ou
=INDEX(Villes;EQUIV(F2&" "&G2;Noms&" "&Prénoms;0))
valider avec Maj+Ctrl+entrée

Remarque: Sommeprod() ne permet pas de récupérer une valeur Alpha.
La formule =BDLIRE(A1:C10;"ville";F1:G2) donnerait le même résultat mais plus rapidement.

RecherchePlusieursConditions

Autre exemple

On recherche un tarif en fonction d'un article et d'un poids.

=INDEX(tarif;EQUIV(1;(article=A2)*(poids>=B2);0))
Valider avec Maj+Ctrl+entrée

Autre exemple

On cherche le temps mini pour un club et une catégorie:

=MIN(SI((Club=H2)*(Cat=I2);temps))
=INDEX(nom;EQUIV(1;(Club=H2)*(Cat=I2)*(temps=H6);0))

Recherche avec résultats multiples

On recherche toutes les valeurs associées à un code.

-Sélectionner E9
=SI(LIGNES($1:1)<=NB.SI(Code;$E$6);INDEX(result;PETITE.VALEUR(SI(Code=$E$6;
LIGNE(INDIRECT("1:"&LIGNES(Code))));LIGNES($1:1)));"")
-Valider avec Maj+Ctrl+Entrée

Recherche tous texte
Recherche Tous Num

Le dernier: =INDEX(result;MAX(SI(Code=E6;LIGNE(INDIRECT("1:"&LIGNES(Code)));0)))

Le 2e : =INDEX(result;PETITE.VALEUR(SI(Code=E6;LIGNE(INDIRECT("1:"&LIGNES(Code))));2))

Si la table est triée

En E9: =SI(LIGNES($1:1)<=NB.SI(Code;$E$6);INDEX(result;EQUIV($E$6;Code;0)+LIGNES($1:1)-1;0);"")

Avec 2 conditions

-Sélectionner F9
=SI(LIGNES($1:1)<=SOMMEPROD((code1=$F$6)*(code2=$G$6));
INDEX(Resultat;PETITE.VALEUR(SI((code1=$F$6)*(code2=$G$6);LIGNE(INDIRECT("1:"&LIGNES(code1))));LIGNES($1:1)));"")
-Valider avec Maj+Ctrl+Entrée

Recherche tous texte2Cond

Le premier:=INDEX(Resultat;EQUIV(1;(code1=F7)*(code2=G7);0))
Le dernier:=INDEX(Resultat;MAX(SI((code1=$F$7)*(code2=$G$7);LIGNE(INDIRECT("1:"&LIGNES(code1))))))
Le 2e: =INDEX(Resultat;PETITE.VALEUR(SI((code1=$F$7)*(code2=$G$7);LIGNE(INDIRECT("1:"&LIGNES(code1))));2))

Filtre base de données

On veut récupérer les lignes pour une catégorie (a3)

Nom de champ: bd =Ventes!$G$7:$L$100

-Sélectionner A7:D11
=SIERREUR(INDEX(bd;PETITE.VALEUR(SI(INDEX(bd;;3)=A3;LIGNE(INDIRECT("1:"&LIGNES(bd))));LIGNE(1:5));COLONNE(A:D));"")
-Valider avec maj+ctrl+entrée

Pour Excel<2007:

-Sélectionner une cellule
=SI(LIGNES($1:1)<=NB.SI(Catégorie;$A$3);
   INDEX(produit;PETITE.VALEUR(SI($A$3=Catégorie;LIGNE(INDIRECT("1:"&LIGNES(produit))));LIGNES($1:1)));"")
-Valider avec maj+ctrl+entrée
-Recopier vers le bas

ou

-Sélectionner une cellule
=SI(LIGNES($1:1)<=NB.SI(INDEX(bd;;3);$A$3);
INDEX(INDEX(bd;;1);PETITE.VALEUR(SI($A$3=INDEX(bd;;3);LIGNE(INDIRECT("1:"&LIGNES(bd))));LIGNES($1:1)));"")
-Valider avec maj+ctrl+entrée
-Recopier vers le bas

Filtre BD
Filtre BD Bis
Filtre BD ter
Filtre BD 2 critères
Filtre BD 2 critères2

Recherche dans un champ discontinu

On veut récupérer le titre (AAA,BBB,CCC) qui correspond à la valeur maxi:

En K2:
=INDEX($A$1:$G$1;MAX(SI((A2:G2=J2)*(MOD(COLONNE(A:G);3)=1);COLONNE(A:G))))
Valider avec Maj+ctrl+entrée

RechercheChampdiscontinu

Appartenance à un ensemble

On veut compter le nombre de lettres A,B,C

=SOMMEPROD(NB.SI(A1:B10;inclus))

On veut la somme des montants pour les codes aa,bb,cc

=SOMMEPROD(SOMME.SI(codes;tcodes;montant))

ou

=SOMMEPROD((codes="aa")+(codes="bb")+(codes="cc"))*montant

ou

=SOMMEPROD((codes={"aa"."bb"."dd"})*montant)

ou

=SOMMEPROD(SOMME.SI(Codes;{"aa";"bb";"cc"};Montant))

Si le champ n'a qu'une colonne.

Autre exemple

=SOMME(NB.SI(A1:C6;{"aa";"bb";"cc"}))

Autre exemple

On veut le total scandinavie.

=SOMMEPROD((Pays={"finlande"."suède"."norvège"})*montant)
=SOMMEPROD(SOMME.SI(Pays;scandinavie;montant))
=SOMME((Pays=TRANSPOSE(scandinavie))*montant)
valider avec maj+ctrl+entrée

Exemple multi-zones

NbSi Multi Zones

=SOMME(NB.SI(INDIRECT({"A2:A8";"C4:C10"});{"aa"."bb"."cc"}))

Autre exemple multi-zones

Donne le nombre de cellules des champs tab1 et tab2 contenant aa

=SOMME(NB.SI(INDIRECT({"tab1";"tab2"});"aa"))

Donne le nombre de cellules des champs tab1,tab2,tab3 contenant aa

=SOMME(NB.SI(INDIRECT("tab" &{1;2;3});"aa"))

Moyenne des champs C6:E6,H6,M6:N6,Q6 supérieurs à 0

=SOMME(SOMME.SI(INDIRECT({"C6:E6";"H6";"M6:N6";"Q6"});">0"))/
SOMME(NB.SI(INDIRECT({"C6:E6";"H6";"M6:N6";"Q6"});">0"))

Autre exemple multi-zones

Donne le nombre de a ou b dans les champs A1:L1;A2:L2;A4:L4;A8:L8

=SOMMEPROD(NB.SI(DECALER(A1:L1;{0.1.3.7};);{"a";"b"}))

Donne le nombre de 3 dans A1,B1,D1,G1,L1;A2,B2,D2,G2,L2;A4,B4,D4,G4,L4;...

=SOMMEPROD(NB.SI(DECALER(INDIRECT({"A1";"B1";"D1";"G1";"L1"});{0.1.3.7};);3))

Donne le nombre de a ou c dans D2:D4,G2:G4,J2:J4,..

=SOMMEPROD(NB.SI(DECALER(D2:D4;;(LIGNE(1:5)-1)*3);{"a"."c"}))

Autre exemple multi-zones

Nombres >7 et <=10: =INDEX(FREQUENCE((A1:A10;C4:C10);{7;10});2)
Nombres =6: =INDEX(FREQUENCE((A1:A10;C4:C10);{5;6});2)

Total par ensemble

On veut le total du CA de chaque service

=SOMME(SOMME.SI(noms;SI(service=G2;NomsService);Ca))
Valider avec maj+ctrl+entrée

Total service

Nombre d'occurences à l'intérieur d'une chaîne

On veut connaître le nombre de fois où aa,bb,cc apparaît dans la chaîne xx aa zz cc kk yy zz

=SOMME(--ESTNUM(TROUVE({"aa";"bb";"cc"};A2;1)))

Nombres compris entre 2 valeurs avec Frequence()

=INDEX(FREQUENCE(A2:A10;{100;300});2)

Rapprochement de noms

On veut calculer la somme des CA de sociétés dont les noms ne sont pas orthographiés de la même façon.
Par ex L'oréal distribution, S.A. L'oréal,Maquillage L'oréal.

Proches

En D2:=SOMME(SI(ESTNUM(CHERCHE(société;D2));ca))
valider avec Maj+ctrl+entrée

Exclusion

On compte le nombre de lettres dans le champ A1:B9 en excluant A,B,C

=NBVAL(champ)-SOMMEPROD(NB.SI(champ;Exclus))
=SOMMEPROD(--(NB.SI(Exclus;champ)=0)*(champ<>""))
=NBVAL(champ)-SOMMEPROD(NB.SI(champ;{"A";"B";"C"}))

On compte le nombre de cellules dans le champ A1:B9 en excluant A,B,C

=SOMMEPROD(--(NB.SI(Exclus;champ)=0))

Exclus

Total pour la famille des codes 1000xx et un client A

=SOMMEPROD((GAUCHE(code;4)="1000")*(client="A")*montant)

Si 1000 est à l'intérieur des codes:

=SOMMEPROD(ESTNUM(CHERCHE("1000";code))*(client="A")*montant)

Somme 1 ligne sur 2 avec texte

Somme1LigneSur2

Sans texte
=SOMMEPROD((MOD(LIGNE(champ)-1;2)=0)*champ)

Avec texte
=SOMME(SI(MOD(LIGNE(champ)-1;2)=0;champ))
Valider avec Maj+ctrl+entrée

ou

=SOMMEPROD(--(MOD(LIGNE(champ)-1;2)=0);champ)

Si le champ peut être déplacé
=SOMME(SI(MOD(LIGNE(INDIRECT("1:"&LIGNES(champ)))+1;2)=0;champ))
Valider avec Maj+ctrl+entrée

ou

=SOMMEPROD(--(MOD(LIGNE(INDIRECT("1:"&LIGNES(champ)))-1;2)=0);champ)

Somme des nombres >0
=SOMME(SI((MOD(LIGNE(INDIRECT("1:"&LIGNES(champ)))+1;2)=0)*(champ>0);champ))
Valider avec Maj+ctrl+entrée

ou

=SOMMEPROD((MOD(LIGNE(INDIRECT("1:"&LIGNES(champ)))+1;2)=0)*(champ>0);champ)

Si le champ contient des cellules avec des erreurs #NA,#DIV/0!

=SOMME(SI(ESTNUM(champ);champ*(MOD(LIGNE(champ)-1;2)=0)))
Valider avec maj+ctrl+entrée

Somme de champ conditionnelle

On veut le total de Janvier,Février,Mars qui vérifient la condition ok.

=SOMMEPROD(--(B2:F10="ok");C2:G10)

Ci dessous on veut:

Total CA ok: =SOMMEPROD(--(B2:H10="ok");C2:I10)
Total Bonus ok: =SOMMEPROD(--(B2:H10="ok");D2:J10)

Somme d'un champ pour un code

=SOMME.SI(A3:G9;"code1";B3:H9)

Somme conditionnelle avec entête

Pour chaque ligne, on veut la somme des CA et la somme des Bonus.

Total CA: =SOMMEPROD(--($B$2:$F$2="CA");B3:F3) ou =SOMME.SI($B$2:$I$2;"ca";B3:G3)
Total Bonus: =SOMMEPROD(--($C$2:$G$2="Bonus");C3:G3) ou =SOMME.SI($B$2:$I$2;"bonus";B3:G3)

Entête
Entête2

Autre solution sans entête: =SOMMEPROD(MOD(COLONNE(B3:F3);2);B3:F3)
Autre solution sans entête: =SOMME(SOMME(DECALER(B3:B11;;{0.2.4})))

On veut la somme des colonnes avec l'entête CA et qui vérifient la condition ok.

=SOMMEPROD((D1:H1="CA")*(B2:B10="ok");D2:H10)

Les entêtes sont CA Janvier,CA Février,CA Mars

=SOMMEPROD((GAUCHE(D1:H1;2)="CA")*(B2:B10="ok");D2:H10)

Les entêtes sont Janvier,Février,Mars

=SOMMEPROD((ESTNUM(DATEVAL("01/"&D1:H1)))*(B2:B10="ok");D2:H10)

Sans entête: =SOMME(SOMME.SI(B2:B10;"ok";DECALER(D2:D10;;{0.2.4})))

Les entêtes sont CA et Bonus

Total CA ok: =SOMMEPROD((C2:K2="CA")*(B3:J11="ok");C3:K11)
Total Bonus ok:=SOMMEPROD((E2:M2="Bonus")*(D3:L11="ok");E3:M11)

Autre exemple multi-zones

=SOMMEPROD(--($B$1:$G$1="valeur")*(rep="O");B2:G9)



ou sans en-tête mais avec des noms de champ Val1,Val2,Val3

=SOMME(SOMME.SI(rep;"O";INDIRECT("val"&{1;2;3})))

Autre exemple

On veut le total des nombres placés sous le mot Composant.

Autre exemple

On veut le total des ventes en tenant compte du taux de change.

=SOMME(SI(titre="VTE";nombre*DECALER(nombre;3;)))
valider avec Maj+ctrl+entrée

MatTotalTitre

Moyenne des colonnes en jaunes sans tenir compte des 0

En S3: =MOYENNE(SI((entete="Note")*(B3:R3<>"");B3:R3))

Moyenne Si

Sans entête:
=SOMME(SOMME.SI(INDIRECT({"B3:D3";"F3";"I3";"K3:M3";"O3";"R3"});">0"))/
SOMME(NB.SI(INDIRECT({"B3:D3";"F3";"I3";"K3:M3";"O3";"R3"});">0"))

Recherche multiple

On veut obtenir les noms des entreprises qui pratiquent l'activité en G2:

=SI(ESTNUM(PETITE.VALEUR(SI(Activité=$G$2;LIGNE(Activité));LIGNE()-2));
INDEX(Entreprise;PETITE.VALEUR(SI(Activité=$G$2;LIGNE(Activité)-1);LIGNE()-2));"")
valider avec Maj+ctrl+entrée

MatricielRechercheMultiple

Noms de champ
Activité =BD!$B$2:$E$27
Entreprise =BD!$A$2:$A$27

Recherche d'un code dans un champ

On veut la valeur associée à un code en utilisant les titres en B1:I1

=INDEX(donnees;MAX((donnees=K2)*LIGNE(donnees)*(titre="code"))-LIGNE(donnees)+1;
MAX((donnees=K2)*COLONNE(donnees)*(titre="code"))-COLONNE(donnees)+2)
Valider avec Maj+ctrl+entrée

RechCode

Si plusieurs réponses
=SI(GRANDE.VALEUR((donnees=$K$2)*LIGNE(donnees)*(titre="code");LIGNES($1:1))>0;
INDEX(donnees;MOD(GRANDE.VALEUR(SI((donnees=$K$2)*(titre="code");COLONNE(donnees)*10^5+LIGNE(donnees));
LIGNES($1:1));10^5)-LIGNE(donnees)+1;
ENT(GRANDE.VALEUR(SI((donnees=$K$2)*(titre="code");COLONNE(donnees)*10^5+LIGNE(donnees));LIGNES($1:1))/10^5)-COLONNE(donnees)+2);"")
Valider avec Maj+ctrl+entrée

Si les codes sont alphanumériques et uniques (ou si les codes ne peuvent exister dans la liste des valeurs):

=SOMME.SI(B2:H11;K2;C2:I11)

On recherche les valeurs associées à un nom dans un champ

RechTous

Toutes les valeurs
=SI(GRANDE.VALEUR((donnees=$K$2)*LIGNE(donnees);LIGNES($1:1))>0;
INDEX(donnees;MOD(PETITE.VALEUR(SI((donnees=$K$2);COLONNE(donnees)*10^5+
LIGNE(donnees));LIGNES($1:1));10^5)-LIGNE(donnees)+1;
ENT(PETITE.VALEUR(SI((donnees=$K$2);COLONNE(donnees)*10^5+LIGNE(donnees));LIGNES($1:1))/10^5)-COLONNE(donnees)+2);"")
Valider avec Maj+ctrl+entrée

Si une seule valeur
=INDEX(donnees;MAX((donnees=P2)*LIGNE(donnees))-LIGNE(donnees)+1;MAX((donnees=P2)*
COLONNE(donnees))-COLONNE(donnees)+2)
Valider avec Maj+ctrl+entrée

Total
=SOMME.SI(B2:H9;K2;C2:I9)

Liste sans doublons

- Liste Sans Doublons

Cas1:La liste initiale doit commencer en A2, C1 doit être libre et si on déplace le champ Champ1,
il faut déplacer la colonne A entière:

En C2:
=INDEX($A:$A;MIN(SI(ch<>"";SI(NB.SI(C$1:C1;ch)=0;LIGNE(ch);LIGNES(ch)+LIGNE(ch)))))
Valider avec Maj+ctrl+entrée

Supprimer les 0 avec Outils/Options/Affichage/Valeur 0

Si A1 est vide
=INDEX($A:$A;MIN(SI(Ch<>"";SI(NB.SI(C$1:C1;Ch)=0;LIGNE(Ch)))))
Valider avec Maj+ctrl+entrée

(=INDEX(A:A;FAUX) en matriciel donne le contenu de A1)

Cas2: C1 doit être libre et la dernière cellule de ch doit être vide
En C2:
=INDEX(Ch;MIN(SI(Ch<>"";SI(NB.SI(C$1:C1;Ch)=0;EQUIV(Ch;Ch;0);LIGNES(Ch)))))
Valider avec Maj+ctrl+entrée

Cas3: C1 doit être libre
En C2:
=SI(MIN(SI(Champ2<>"";SI(NB.SI(C$1:C1;Champ2)=0;EQUIV(Champ2;Champ2;0))))<>0;
INDEX(Champ2;MIN(SI(Champ2<>"";SI(NB.SI(C$1:C1;Champ2)=0;EQUIV(Champ2;Champ2;0)))));"")
Valider avec Maj+ctrl+entrée

Explications



Cas4: Pas de cellule vide dans le champ
-Sélectionner C2:C8
=SI(ESTTEXTE(INDEX(Champ;PETITE.VALEUR(SI(EQUIV(Champ;Champ;0)=
LIGNE(INDIRECT("1:"&LIGNES(Champ)));EQUIV(Champ;Champ;0);"");LIGNE(INDIRECT("1:"&LIGNES(Champ))))));
INDEX(Champ;PETITE.VALEUR(SI(EQUIV(Champ;Champ;0)=
LIGNE(INDIRECT("1:"&LIGNES(Champ)));EQUIV(Champ;Champ;0);"");LIGNE(INDIRECT("1:"&LIGNES(Champ)))));"")
Valider avec Maj+ctrl+Entrée

Avec une fonction personnalisée (Liste Sans Doublons)

-Sélectionner C2:C13
=SansDoublonsTrié(A2:A13)
-Valider avec Maj+ctrl+Entrée

Sans doublons trié numérique

En C2: =MIN(SI((NB.SI($C$1:C1;champ)=0)*(champ>0);champ))
Valider avec Maj+ctrl+entrée

Calcul des totaux par nom

=INDEX(BD!$A:$A;MIN(SI(nom<>"";SI(NB.SI(A$1:A1;nom)=0;LIGNE(nom);LIGNES(nom)+LIGNE(nom)))))
Valider avec maj+ctrl+entrée

=SI(A2<>"";SOMME.SI(nom;A2;montant);0)

SDTotaux

Liste sans doublons conditionnelle

On veut la liste sans doublons pour Cond="x"

=INDEX($A:$A;MIN(SI(cond="x";SI(NB.SI(D$1:D1;champ)=0;LIGNE(champ);LIGNES(champ)+LIGNE(champ)))))
Valider avec maj+ctrl+entrée

Si A1 est vide
=INDEX(A:A;MIN(SI(cond="x";SI(NB.SI(D$1:D1;champ)=0;LIGNE(champ)))))
Valider avec maj+ctrl+entrée

SansDoublonsCond

On veut seulement les mots commençant par la lettre C

=INDEX(Champ;MIN(SI(GAUCHE(Champ;1)="C";SI(NB.SI(C$1:C1;Champ)=0;
LIGNE(INDIRECT("1:"&LIGNES(Champ)));LIGNES(Champ)))))
Valider avec Maj+ctrl+entrée

ListeSansDoublonsCond

Autre exemple

En E5:
=INDEX($B:$B;MIN(SI(codes=$D$2;SI(NB.SI(D$4:D4;noms)=0;LIGNE(noms);LIGNES(noms)+LIGNE(noms)))))
Valider avec Maj+ctrl+entrée

SansDoublonsCond

ou

=INDEX(noms;MIN(SI(codes=$D$2;SI(NB.SI(E$4:E4;noms)=0;LIGNE(INDIRECT("1:"&LIGNES(noms)));LIGNES(noms)))))
Valider avec Maj+ctrl+entrée

ListeSansDoublonsExclus

Présentation d'une BD en colonnes

En D2:=INDEX(service;MIN(SI(service<>"";SI(NB.SI($C2:C2;service)=0;EQUIV(service;service;0);LIGNES(service)))))

En D4: =SI(LIGNES($1:1)<=NB.SI(service;D$2);
INDEX(nom;PETITE.VALEUR(SI(service=D$2;LIGNE(INDIRECT("1:"&LIGNES(service))));LIGNES($1:1)));"")

Liste sans vides

En C2:
=SI(LIGNES($1:1)<=NBVAL(Champ);
INDEX(Champ;PETITE.VALEUR(SI(Champ<>"";LIGNE(INDIRECT("1:"&LIGNES(Champ))));LIGNES($1:1)));"")
Valider avec Maj+Ctrl+entrée

ou

=SI(LIGNES($1:1)<=NBVAL(champ);
INDEX(champ;PETITE.VALEUR(SI(champ<>"";EQUIV(champ;champ;0));LIGNES($1:1)));"")
Valider avec Maj+Ctrl+entrée

Si A1 est vide:
=INDEX(A:A;MIN(SI(champ<>"";SI(NB.SI(C$1:C1;champ)=0;LIGNE(champ)))))
Valider avec Maj+Ctrl+entrée

ListeSansVide
ListeSansVide2
Fonction VBA Sans Vides + rapide
Fonction VBA Sans Vides triée

Version triée
Sélectionner C2:C12
=INDEX(champ;EQUIV(GRANDE.VALEUR(NB.SI(champ;">="&champ);
LIGNE(INDIRECT("1:"&LIGNES(champ))));NB.SI(champ;">="&champ);0))
Valider avec Maj+Ctrl+entrée

Si le champ a plusieurs colonnes

Ordre colonnes
=SI(LIGNES($1:1)<=NBVAL(champ);
INDEX(champ;MOD(PETITE.VALEUR(SI(champ<>"";COLONNE(champ)*10^5+
LIGNE(INDIRECT("1:"&LIGNES(champ))));LIGNES($1:1));10^5);
ENT(PETITE.VALEUR(SI(champ<>"";COLONNE(champ)*10^5+LIGNE(INDIRECT("1:"&LIGNES(champ))));
LIGNES($1:1))/10^5)-COLONNE(champ)+1);"")
Valider avec Maj+Ctrl+entrée

Ordre lignes
=SI(LIGNES($1:1)<=NBVAL(champ);
INDEX(champ;PETITE.VALEUR(SI(champ<>"";LIGNE(INDIRECT("1:"&LIGNES(champ))));LIGNES($1:1));
MOD(PETITE.VALEUR(SI(champ<>"";LIGNE(INDIRECT("1:"&LIGNES(champ)))*10^5+COLONNE(champ));
LIGNES($1:1));10^5)-COLONNE(champ)+1);"")
Valider avec Maj+Ctrl+entrée

En ligne

=SI(COLONNES($A:A)<=NBVAL(champ);
INDEX(champ;PETITE.VALEUR(SI(champ<>"";EQUIV(champ;champ;0));COLONNES($A:A)));"")

Pour obtenir le résultat en colonne



=SI(LIGNES($1:1)<=NBVAL(ch);
INDEX(ch;PETITE.VALEUR(SI(ch<>"";EQUIV(ch;ch;0));LIGNES($1:1)));"")
Valider avec maj+ctrl+entrée

Avec fonction personnalisée FonctionSansVideTrié

Liste conditionnelle

On veut la liste des items ayant X dans la colonne B.

=SI(LIGNES($1:1)<=NB.SI(cond;"x");
INDEX(champ;PETITE.VALEUR(SI(cond="x";LIGNE(INDIRECT("1:"&LIGNES(champ))));LIGNES($1:1)));"")
Valider avec Maj+ctrl+entrée

ou

=SI(LIGNES($1:1)<=NB.SI(cond;"x");
INDEX(champ;PETITE.VALEUR(SI(cond="x";EQUIV(champ;champ;0));LIGNES($1:1)));"")
Valider avec Maj+ctrl+entrée

Si A1 est vide (élimine les doublons):
=INDEX(A:A;MIN(SI(cond="x";SI(NB.SI(D$1:D1;champ)=0;LIGNE(champ)))))
Valider avec Maj+ctrl+entrée

Pour Excel>=2007
-Sélectionner K2:K13
=SIERREUR(INDEX(champ;PETITE.VALEUR(SI(cond="x";LIGNE(INDIRECT("1:"&LIGNES(champ))));LIGNE(INDIRECT("1:"&LIGNES(champ)))));"")
Valider avec Maj+ctrl+entrée

ListeCond
ListeCond5
ListeCondBDunSeulChamp
RechercheNomBD
ListeCondBDunSeulChamp2
PlanningTachesJour
Liste Conditionnelle 2 Conditions
Liste Conditionnelle entre 2 dates
ListeCondHorizontale

Pour récupérer le résultat dans une seule cellule.

=ListeCondUneCellule(champ;cond;"x")

ListeCondUneCellule

Function ListeCondUneCellule(champDonnées As Range, champCond As Range, cond)
  temp = ""
  For i = 1 To champDonnées.Count
     If champCond(i) = cond Then temp = temp & champDonnées(i) & " "
  Next i
  ListeCondUneCellule = temp
End Function

Autre exemple

A1 doit être vide.
En F2:
=INDEX(A:A;MIN(SI(Noms<>"";SI((NB.SI(F$1:F1;Noms)=0)*(cond="x");LIGNE(Noms)))))
Valider avec maj+ctrl+entrée

ListeCond3
ListeCondDates

Autre exemple

Matcond

=SI(LIGNES($1:1)<=NB.SI(INDEX(cond;COLONNES($A:A););"x");
INDEX(noms;PETITE.VALEUR(SI(INDEX(cond;COLONNES($A:A);)="x";EQUIV(noms;noms;0));LIGNES($1:1)));"")
valider avec maj+ctrl+entrée

ou (si A1 est vide)

=INDEX($1:$1;MIN(SI(INDEX(cond;COLONNES($A:A);)="x";SI(NB.SI(A$20:A20;noms)=0;COLONNE(noms)))))
valider avec maj+ctrl+entrée

Autre exemple

En A10
=SI(LIGNES($1:1)<=NB.SI(Sports;A$9);
INDEX(noms;PETITE.VALEUR(SI(Sports=A$9;EQUIV(noms;noms;0));LIGNES($1:1)));"")
valider avec maj+ctrl+entrée

SportsNoms

Autre exemple

On veut la liste des produits dont le code commence par 2.

=SI(LIGNES($1:1)<=SOMMEPROD(--(GAUCHE(num;1)=$D$2));
INDEX(num;PETITE.VALEUR(SI(GAUCHE(num;1)=$D$2;LIGNE(INDIRECT("1:"&LIGNES(num))));LIGNES($1:1)));"")
-Valider avec maj+Ctrl+Entrée

Autres exemples

ListeCond 2 critères
ListeConditionnelleColonne
ListeConditionnelleEmail
ListeConditionnelle01

Liste conditionnelle avec appartenance à un ensemble

On veut les lignes pour lesquelles les conditions sont X,Y ou Z

ListeConditionnelleEnsemble

=SI(LIGNES($1:1)<=SOMME(NB.SI(cond;ListeCond));
INDEX(champ;PETITE.VALEUR(SI(cond=TRANSPOSE(ListeCond);LIGNE(INDIRECT("1:"&LIGNES(champ))));LIGNES($1:1)));"")
Valider avec maj+ctrl+entrée

ou

=SI(LIGNES($1:1)<=SOMME(NB.SI(cond;{"X";"Y";"Z"}));
INDEX(champ;PETITE.VALEUR(SI(cond={"X"."Y"."Z"};LIGNE(INDIRECT("1:"&LIGNES(champ))));LIGNES($1:1)));"")

On veut les mots qui contiennent X,Y ou Z

ListeConditionnelleEnsemble
ListeConditionnelleEnsembleSansDoublons

=SI(LIGNES($1:1)<=SOMME(NB.SI(champ;"*"&cond&"*"));
INDEX(champ;PETITE.VALEUR(SI(ESTNUM(CHERCHE(TRANSPOSE(cond);champ));
LIGNE(INDIRECT("1:"&LIGNES(champ))));LIGNES($1:1)));"")
Valider avec maj+ctrl+entrée

ou

=SI(LIGNES($1:1)<=SOMME(NB.SI(champ;"*"&{"X";"Y";"Z"}&"*"));
INDEX(champ;PETITE.VALEUR(SI(ESTNUM(CHERCHE({"X"."Y"."Z"};champ));
LIGNE(INDIRECT("1:"&LIGNES(champ))));LIGNES($1:1)));"")

Extraction des lignes d'une BD

En A6:
=SI(LIGNES($1:1)<=NB.SI(INDEX(BD;;3);$B$1);
INDEX(BD;PETITE.VALEUR(SI(INDEX(BD;;3)=$B$1;LIGNE(INDIRECT("1:"&LIGNES(BD))));
LIGNES($1:1));EQUIV(A$5;Titre;0));0)
Valider avec Maj+ctrl+entrée

Mat BD Extrait
Mat BD Extrait 2

Recherche de valeurs alpha associées à un nom

On recherche les langues pour un nom.

RechercheLangues

-Sélectionner G5
=SI(LIGNES($1:1)<=NB.SI(Noms;$G$2);INDEX(Langues;PETITE.VALEUR(SI(Noms=$G$2;
LIGNE(INDIRECT("1:"&LIGNES(Noms))));LIGNES($1:1)));"")
-Valider avec Maj+Ctrl+Entrée

Détail

Pour 2 critères
=SI(LIGNES($1:1)<=SOMMEPROD((Noms=$B$2)*(prénoms=$C$2));
INDEX(Langues;PETITE.VALEUR(SI((Noms=$B$2)*(prénoms=$C$2);
LIGNE(INDIRECT("1:"&LIGNES(Noms))));LIGNES($1:1)));"")

Avec une fonction personnalisée(cf fichier RechercheLangues)

-Sélectionner G5:G11
=SansDoublonsTriéCritère(Langues;Noms;G2)
-Valider avec Maj+Ctrl+Entrée

Si la liste est triée

En D5: =SI(LIGNES($1:1)<=NB.SI(Caté;$D$2);INDEX(Réf;EQUIV($D$2;Caté;0)-1+LIGNES($1:1));"")

ListeCondTrié

Autre exemple

On veut les items qui commencent par ***

=SI(LIGNES($1:1)<=NB.SI(Items;"~*~*~**");
INDEX(Items;PETITE.VALEUR(SI(GAUCHE(Items;3)="***";LIGNE(INDIRECT("1:"&LIGNES(Items))));LIGNES($1:1)));"")
Valider avec Maj+ctrl+entrée

ListeCond

Liste conditionnelle avec condition variable

On ne fait apparaître que les noms pour la compétence choisie en A2.

DVCascadeCompétences
DVCascadeCompétences2

En A10:
=SI(LIGNES($1:1)<=NB.SI(INDEX(cond;;EQUIV($A$2;competences;0));"x");
INDEX(noms;PETITE.VALEUR(SI(INDEX(cond;;EQUIV($A$2;competences;0))="x";
LIGNE(noms));LIGNES($1:1))-LIGNE(noms)+1);"")
Valider avec maj+ctrl+entrée

Liste conditionnelle pour un ensemble de codes

On veut extraire du tableau les lignes dont les codes appartiennent à l'ensemble des codes en A3:A5.

ListeCondEnsemble

En G2:
=SI(LIGNES($G$2:$G2)<=SOMME(--(NB.SI(code;cond)));
INDEX(code;PETITE.VALEUR(SI((NB.SI(cond;code));LIGNE(INDIRECT("1:"&LIGNES(code))));LIGNES($G$2:$G2)));"")
Valider avec Maj+ctrl+entrée

=SI(LIGNES($G$2:$G2)<=SOMME(--(NB.SI(code;cond)));
INDEX(val;PETITE.VALEUR(SI((NB.SI(cond;code));LIGNE(INDIRECT("1:"&LIGNES(code))));LIGNES($G$2:$G2)));"")
Valider avec Maj+ctrl+entrée

Liste conditionnelle onglet

On veut extraire sur un onglet nommé Physique les lignes de la matière Physique. L'onglet est copiable.

En A1:
=STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99)

En A3:
=SI(LIGNES($1:1)<=NB.SI(matiere;$A$1);
INDEX(dates;PETITE.VALEUR(SI(matiere=$A$1;LIGNE(INDIRECT("1:"&LIGNES(matiere))));LIGNES($1:1)));"")
valider avec maj+ctrl+entrée

ListeCondOnglet

Liste conditionnelle avec fonction personnalisée

-Cette fonction est très rapide et utilisable comme une fonction standard sans connaître VBA
-Rapide 0,2 sec pour 10.000 lignes)
-1 ou 2 critère(s) de sélection et choix d'une colonne de tri

Filtre BD 1 ou 2 conditions

Liste différence

Noms de champ
Liste1 =Feuil1!$A$2:$A$10
Liste2 =Feuil1!$B$2:$B$10

En D2:
=SI(LIGNES($1:1)<=NBVAL(Liste1)-SOMMEPROD(NB.SI(Liste2;Liste1));
INDEX(Liste1;PETITE.VALEUR(SI((NB.SI(Liste2;Liste1)=0)*(Liste1<>"");
LIGNE(INDIRECT("1:"&LIGNES(Liste1))));LIGNES($1:1)));"")

DiffListes
DiffListesPlusieurs
Valeurs Manquantes

Liste communs

=SI(LIGNES($1:1)<=SOMMEPROD(NB.SI(Liste2;Liste1));
INDEX(Liste1;PETITE.VALEUR(SI((NB.SI(Liste2;Liste1)=1)*(Liste1<>"");LIGNE(INDIRECT("1:"&LIGNES(Liste1))));
LIGNES($1:1)));"")

Liste des ouvrages pour un mot clé

En F2:
=SI(LIGNES($1:1)<=NB.SI(Titre;"*"&$D$2&"*");
INDEX(Titre;PETITE.VALEUR(SI(ESTNUM(CHERCHE($D$2;Titre));LIGNE(INDIRECT("1:"&LIGNES(Titre))));LIGNES($1:1)));"")

En G2:
=SI(LIGNES($1:1)<=NB.SI(Titre;"*"&$D$2&"*");
INDEX(Auteur;PETITE.VALEUR(SI(ESTNUM(CHERCHE($D$2;Titre));LIGNE(INDIRECT("1:"&LIGNES(Titre))));LIGNES($1:1)));"")

MatRecherche

Liste conditionnelle horizontale

ListeCondHoriz

-Sélectionner D2
=MIN(SI((NB.SI($D$1:D1;code)=0)*(code>0);code))
Valider avec Maj+Ctrl+Entrée

-Sélectionner E2
=SI(COLONNES($A:A)<=NB.SI(code;$D2);
INDEX(ville;PETITE.VALEUR(SI(code=$D2;LIGNE(INDIRECT("1:"&LIGNES(ville))));COLONNES($A:A)));"")
Valider avec Maj+Ctrl+Entrée

Transformation de colonnes en lignes

Transforme colonnes en lignes

Autre exemple

En B8:
=SI(LIGNES($1:1)<=NB.SI(code;$B$6);
INDEX(ville;PETITE.VALEUR(SI(code=$B$6;COLONNE(code));LIGNES($1:1))-COLONNE(code)+1);"")
Valide avec maj+ctrl+entrée

ListeCondHoriz

Donne les phrases contenant le mot cherché

En E2:
=SI(LIGNES($1:1)<=NB.SI(phr;"*"&$C$2&"*");
INDEX(phr;PETITE.VALEUR(SI(ESTNUM(CHERCHE(BD!$C$2;phr));LIGNE(INDIRECT("1:"&LIGNES(phr))));LIGNES($1:1)));"")

RechercheMot

Pour extraire la liste des noms en rouge

ListeCondCouleur

En C2:
=SI(LIGNES($1:1)<=SOMME(--(couleurfond(Noms)=3));
INDEX(Noms;PETITE.VALEUR(SI(couleurfond(Noms)=3;LIGNE(INDIRECT("1:"&LIGNES(Noms))));LIGNES($1:1)));"")
Valider avec Maj+ctrl+entrée

Function couleurFond(champ As Range)
  Application.Volatile
  Dim temp()
  ReDim temp(1 To champ.Count)
  For i = 1 To champ.Count
   temp(i) = champ(i).Interior.ColorIndex
  Next i
  couleurFond = Application.Transpose(temp)
End Function

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Calculate
End Sub

Pour extraire la liste des noms en gras

ListeCondGras

=SI(LIGNES($1:1)<=SOMME(--estgras(Items));
INDEX(Items;PETITE.VALEUR(SI(estgras(Items);LIGNE(INDIRECT("1:"&LIGNES(Items))));LIGNES($1:1)));"")
valider avec Maj+ctrl+entrée

Function EstGras(champ As Range)
  Dim temp()
  ReDim temp(1 To champ.Count)
  For i = 1 To champ.Count
    temp(i) = champ(i).Font.Bold
  Next i
  EstGras = Application.Transpose(temp)
End Function

Pour extraire la liste des factures en €

FonctionEstEuroMat

En D2:
=SI(LIGNES($1:1)<=SOMME(--EstEuroMat(montant));
INDEX(facture;PETITE.VALEUR(SI(EstEuroMat(montant);LIGNE(INDIRECT("1:"&LIGNES(montant))));LIGNES($1:1)));"")
valider avec maj+ctrl+entrée

Function EstEuroMat(champ As Range)
  Application.Volatile
  Dim temp()
  ReDim temp(1 To champ.Count)
  For i = 1 To champ.Count
    temp(i) = IIf(InStr(champ(i).Text, "€") > 0, True, False)
  Next i
  EstEuroMat = Application.Transpose(temp)
End Function

Classement avec égalité

On veut le classement par points décroissant. Il y a égalité.

En E2:=GRANDE.VALEUR(points;LIGNES($1:1))
En D2:=INDEX(pays;PETITE.VALEUR(SI(points=E2;LIGNE(INDIRECT("1:"&LIGNES(pays))));NB.SI(E$2:E2;E2)))
Valider avec Maj+ctrl+entrée

ClassementEgalité
ClassementEgalité2

Autre exemple

On veut la liste des noms dans l'ordre décroissant des notes (sans les notes).

-Sélectionner D2:D10
=INDEX(nom;EQUIV(GRANDE.VALEUR(Notes-LIGNE(Notes)/10^10;
LIGNE(INDIRECT("1:"&LIGNES(nom))));Notes-LIGNE(Notes)/10^10;0))
-Valider avec Maj+ctrl+entrée

Classement égalité
Classement égalité2

=Notes-LIGNE(Notes)/10^10 permet de différencier les notes égales.

Autre exemple

Top 5 :On veut les 5 premiers. Il y a égalité.

Top5Egalité

-Sélectionner D2:D6
=INDEX(Nom;EQUIV(GRANDE.VALEUR(Ca-LIGNE(Ca)/10^10;{1;2;3;4;5});Ca-LIGNE(Ca)/10^10;0))
Valider avec Maj+ctrl+entrée

Les 5 premiers, les 5 derniers, les autres

En E3:
=INDEX(noms;EQUIV(GRANDE.VALEUR(points-LIGNE(points)/10^10;COLONNES($A:A));points-LIGNE(points)/10^10;0))
Valider avec maj+ctrl+entrée

En E6:
=SI(COLONNES($A:F)<=NBVAL(noms)-5;
INDEX(noms;EQUIV(GRANDE.VALEUR(points-LIGNE(points)/10^10;COLONNES($A:F));points-LIGNE(points)/10^10;0));"")
Valider avec maj+ctrl+entrée

5Premiers

On veut les 3 premiers d'une catégorie - Il y a égalité -

-Sélectionner H2:H5
=INDEX(Nom;EQUIV(GRANDE.VALEUR(SI(cat=$F$2;points-LIGNE(points)/10^10);{1;2;3});points-LIGNE(points)/10^10;0))
-Valider avec Maj+Ctrl+entrée

3PremiersCaté

Rang sans tenir compte des doublons

En C2: =SOMMEPROD((notes>=B2)/NB.SI(notes;notes))

RangSansTenirCompteDoublons

Pour que les doublons n'aient pas le même rang

En C2: =RANG(B2;notes)+NB.SI($B$2:B2;B2)-1

ou =SOMMEPROD(--(B2<notes))+NB.SI($B$2:B2;B2)

Rang pour une catégorie

=SOMMEPROD(--(Catégorie=B2)*(C2<Note))+1

Transporteur le moins cher pour un département

TransporteurMoinsCher

Première position

=INDEX(transp;MIN(SI(C$2:C$10=MIN(SI(depart=$A15;C$2:C$10));LIGNE(INDIRECT("1:"&LIGNES(depart))))))
valider avec maj+ctrl+entrée

2eme position

=INDEX(transp;MIN(SI(C$2:C$10=PETITE.VALEUR(SI(depart=$A21;C$2:C$10);2);LIGNE(INDIRECT("1:"&LIGNES(depart))))))
valider avec maj+ctrl+entrée

Classement par transporteur

ClassementTransporteur

Chaînes de caractères

Recherche par la droite dans une chaîne de caractères

On recherche la position du dernier caractère ',' pour récupérer la dernière partie 37.35
En A1: HABT,1025,G1590,TRAM,37.35

=DROITE(A1;EQUIV(",";STXT(A1;NBCAR(A1)-LIGNE($1:$255);1);0))
-Valider avec Maj+ctrl+entrée

ou

=STXT(A1;CHERCHE("|";SUBSTITUE(A1;",";"|";NBCAR(A1)-NBCAR(SUBSTITUE(A1;",";""))))+1;99)

Compter le nombre de caractères appartenant à un ensemble

A1 contient: xxBxxCxxxx : On veut le nombre de fois où apparaissent les caractères A,B ou C

 Chaque caractère (A,B,C) n'est compté qu'une fois s'il apparaît plusieurs fois:

=SOMMEPROD(--(ESTNUM(CHERCHE({"A";"B";"C"};A1))))    --> 2

Si les caractères cherchés(ABC) sont dans la cellule B1

=SOMMEPROD(--(ESTNUM(CHERCHE(STXT(B1;LIGNE(INDIRECT("1:"&NBCAR(B1)));1);A1))))

 Chaque caractère (A,B,C) est compté à chaque fois q'il apparaît:

=SOMMEPROD(--(ESTNUM(CHERCHE(STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)));1);"ABC"))))

On veut supprimer le texte après le dernier \

chaine1\chaine2\chaine3\chaine4\Asupprimer pour obtenir chaine1\chaine2\chaine3\chaine4

=GAUCHE(A1;NBCAR(A1)-EQUIV("\";STXT(A1;NBCAR(A1)-LIGNE($1:$255);1);0)-1)
-Valider avec Maj+ctrl+entrée

ou

=GAUCHE(A1;CHERCHE("|";SUBSTITUE(A1;"\";"|";NBCAR(A1)-NBCAR(SUBSTITUE(A1;"\";""))))-1)

On veut récupérer la dernière partie (Ecran standard)

A1: /Informatique/Périphériques de sortie/Moniteur/Ecran standard

=DROITE(A1;EQUIV("/";STXT(A1;NBCAR(A1)-LIGNE($1:$255);1);0))
-Valider avec Maj+ctrl+entrée

A1: /Informatique/Périphériques de sortie/Moniteur/Ecran standard/

=SUBSTITUE(DROITE(A1;EQUIV("/";STXT(A1;NBCAR(A1)-LIGNE($1:$255);1);0));"/";"")
-Valider avec Maj+ctrl+entrée

ou

=SUBSTITUE(STXT(A1;CHERCHE("|";SUBSTITUE(A1;"/";"|";NBCAR(A1)-NBCAR(SUBSTITUE(A1;"/";""))-1))+1;99);"/";"")

Extraction des chaînes de chaine1\chaine2\chaine3\chaine4

ExtraitChaines

Chaîne avant le dernier \
=GAUCHE(A1;CHERCHE("|";SUBSTITUE(A1;"\";"|";NBCAR(A1)-NBCAR(SUBSTITUE(A1;"\";""))))-1)

ou

=GAUCHE(A1;NBCAR(A1)-EQUIV("\";STXT(A1;NBCAR(A1)-LIGNE($1:$255);1);0)-1)
valider avec maj+ctrl+enttrée

Chaîne après le dernier \
=STXT(A1;CHERCHE("|";SUBSTITUE(A1;"\";"|";NBCAR(A1)-NBCAR(SUBSTITUE(A1;"\";""))))+1;99)

ou

=DROITE(A1;EQUIV("\";STXT(A1;NBCAR(A1)-LIGNE($1:$255);1);0))
Valider avec maj+ctrl+entrée

On veut récupérer 8,741.59

H 0000000190364654 Internal Message JEAN JEAN 8,741.59 Settled
H 0000000190146254 Internal Message JEAN JEAN 4,644,824.24 Settled

=DROITE(SUBSTITUE(A1;" Settled";"");EQUIV(" ";STXT(SUBSTITUE(A1;" Settled";"");
NBCAR(SUBSTITUE(A1;" Settled";""))-LIGNE($1:$255);1);0))
Valider avec Maj+ctrl+entrée

Ci dessous, on récupère l'avant dernière occurence

H 0000000190364654 Internal Message JEAN JEAN 8,741.59 xxxx
H 0000000190146254 Internal Message JEAN JEAN 4,644,824.24 yyyy

=STXT(GAUCHE(A1;CHERCHE("|";SUBSTITUE(A1;" ";"|";NBCAR(A1)-NBCAR(SUBSTITUE(A1;" ";""))-0)));
CHERCHE("|";SUBSTITUE(A1;" ";"|";NBCAR(A1)-NBCAR(SUBSTITUE(A1;" ";""))-1))+1;99)

On veut la dernière date de C2 si B2 est barré

=SI(NON(estbarré(B2));"";SI(ESTERREUR(CHERCHE(",";C2));C2;DROITE(C2;EQUIV(",";
STXT(C2;NBCAR(C2)-LIGNE($1:$255);1);0)) ))
Valider avec Maj+ctrl+entrée

Dans un module (Alt+F11 puis Insertion/Module)

Function EstBarré(c)
  Application.Volatile
  EstBarré = c.Font.Strikethrough
End Function

Récupération du nom et du prénom

NomPrénom

=GAUCHE(A4;EQUIV(VRAI;EXACT(STXT(A4;LIGNE($1:$255);3);MAJUSCULE(STXT(A4;LIGNE($1:$255);3)));0))
-Valider avec Maj+ctrl+entrée

=STXT(A4;EQUIV(VRAI;EXACT(STXT(A4;LIGNE($1:$255);3);MAJUSCULE(STXT(A4;LIGNE($1:$255);3)));0)+1;999)
-Valider avec Maj+ctrl+entrée

Si les noms et prénoms sont dans un ordre quelconque

NomPrénom2

En B7:=SI(CODE(STXT(A7;2;1))>=97;
GAUCHE(A7;EQUIV(VRAI;EXACT(STXT(A7;LIGNE($1:$255);3);MAJUSCULE(STXT(A7;LIGNE($1:$255);3)));0));
STXT(A7;EQUIV(FAUX;EXACT(STXT(A7;LIGNE($1:$255);3);MAJUSCULE(STXT(A7;LIGNE($1:$255);3)));0)+1;999))
Valider avec maj+ctrl+entrée

En C7:=SI(CODE(STXT(A7;2;1))>=97;
STXT(A7;EQUIV(VRAI;EXACT(STXT(A7;LIGNE($1:$255);3);MAJUSCULE(STXT(A7;LIGNE($1:$255);3)));0)+1;999);
GAUCHE(A7;EQUIV(FAUX;EXACT(STXT(A7;LIGNE($1:$255);3);MAJUSCULE(STXT(A7;LIGNE($1:$255);3)));0)))
Valider avec maj+ctrl+entrée

Mise en ordre nom/prénom

Dans la liste originale, les noms/prénoms sont dans un ordre quelconque.

En B7:=SI(CODE(STXT(A7;2;1))>=97;A7;
STXT(A7;EQUIV(FAUX;EXACT(STXT(A7;LIGNE($1:$255);3);MAJUSCULE(STXT(A7;LIGNE($1:$255);3)));0)+1;999)&" "&
GAUCHE(A7;EQUIV(FAUX;EXACT(STXT(A7;LIGNE($1:$255);3);MAJUSCULE(STXT(A7;LIGNE($1:$255);3)));0)))
Valider avec maj+ctrl+entrée

En C7:=SI(CODE(STXT(A7;2;1))>=97;
STXT(A7;EQUIV(VRAI;EXACT(STXT(A7;LIGNE($1:$255);3);MAJUSCULE(STXT(A7;LIGNE($1:$255);3)));0)+1;999)&" "&
GAUCHE(A7;EQUIV(VRAI;EXACT(STXT(A7;LIGNE($1:$255);3);MAJUSCULE(STXT(A7;LIGNE($1:$255);3)));0));A7)
Valider avec maj+ctrl+entrée

Découpage d'une adresse

En A1: 28 rue du grand Faubourg 33015 La Rochelle

Découpe adresse
Découpe adresse2

Rue
=GAUCHE(A1;NBCAR(A1)-EQUIV(VRAI;ESTNUM(--(STXT(A1;NBCAR(A1)-LIGNE($1:$255);1)));0)-5)
Valider avec Maj+ctrl+entrée

Code postal
=STXT(A1;NBCAR(A1)-EQUIV(VRAI;ESTNUM(--(STXT(A1;NBCAR(A1)-LIGNE($1:$255);1)));0)-4;5)
-Valider avec Maj+ctrl+entrée

Ville
=STXT(A1;NBCAR(A1)-EQUIV(VRAI;ESTNUM(--(STXT(A1;NBCAR(A1)-LIGNE($1:$255);1)));0)+2;99)
-Valider avec Maj+ctrl+entrée

En VBA:

Function CodePostal(chaine)
  p = 1
  CodePostal = ""
  Do While p <= Len(chaine) - 4 And CodePostal = ""
     If Mid(chaine, p, 5) Like "#####" Then CodePostal = Mid(chaine, p, 5) Else p = p + 1
  Loop
End Function

Function Rue(chaine)
  p = 1
  Do While p <= Len(chaine) - 4 And Rue = ""
    If Mid(chaine, p, 5) Like "#####" Then Rue = Left(chaine, p - 2) Else p = p + 1
  Loop
End Function

Function Ville(chaine)
  p = 1
  Do While p <= Len(chaine) - 4 And Ville = ""
    If Mid(chaine, p, 5) Like "#####" Then Ville = Mid(chaine, p + 6) Else p = p + 1
  Loop
End Function

Si l'adresse se présente ainsi

Durand Alain 14280 Paris

Le code postal s'obtient par

=STXT(A1;EQUIV(VRAI;ESTNUM(--(STXT(A1;LIGNE($1:$255);1)));0);5)
Valider avec maj+ctrl+entrée

Donne le premier nombre de 5 chiffres d'une chaîne

1432,rue de Milan 78180 Montigny (12345)

=STXT(A1;EQUIV(1;ESTNUM(--(STXT(A1;LIGNE($1:$255);5)))*
(NBCAR(SUBSTITUE(STXT(A1;LIGNE($1:$255);5);",";""))=5);0)+1;5)
Valider avec maj+ctrl+entrée

Renvoie VRAI si une chaîne contient au moins un chiffre

A1 contient aaa123bbbb

=NB(--(STXT(A1;LIGNE($1:$255);1)))>0
Valider avec maj+ctrl+entrée

Renvoie VRAI si la chaîne en A1 contient au moins un chiffre.

Donne la partie numérique d'une chaîne (1 seule occurrence)

A1 contient XYZ123. On veut 123.

=STXT(A1;EQUIV(VRAI;ESTNUM(--(STXT(A1;LIGNE($1:$255);1)));0);99)
-Valider avec Maj+Ctrl+Entrée

A1 contient Tph 0130556677 France. On veut 0130556677.

=STXT(A1;EQUIV(VRAI;ESTNUM(CNUM(STXT(A1;LIGNE($1:$255);1)));0);NB(--(STXT(A1;LIGNE($1:$255);1))))
-Valider avec Maj+Ctrl+Entrée

A1 contient Total de Toto : 256.25 xxx. Pour obtenir 256.25

=STXT(A1;EQUIV(VRAI;ESTNUM(CNUM(STXT(A1;LIGNE($1:$255);1)));0);
NB(--(STXT(SUBSTITUE(A1;".";"0");LIGNE($1:$255);1))))
-Valider avec Maj+ctrl+entrée

pour obtenir le résultat en numérique avec , (256,25)

=CNUM(SUBSTITUE(STXT(A1;EQUIV(VRAI;ESTNUM(CNUM(STXT(A1;LIGNE($1:$255);1)));0);
NB(--(STXT(SUBSTITUE(A1;".";"0");LIGNE($1:$255);1))));".";","))
-Valider avec Maj+ctrl+entrée

A1 contient MR DANIEL 1210 DUPOND. On veut les 4 caractères 1210.

=STXT(A1;EQUIV(VRAI;ESTNUM(CNUM(STXT(A1;LIGNE($1:$255);1)));0);4)
-Valider avec Maj+Ctrl+Entrée

A1 contient ABN AMRO BANK NV 3.375% 21/01/2014. On veut 3,375.

=SUBSTITUE(STXT(A1;EQUIV(VRAI;ESTNUM(CNUM(STXT(A1;LIGNE($1:$232);1)));0);
NB(--(STXT(SUBSTITUE(GAUCHE(A1;CHERCHE("%";A1)-1);".";"0");LIGNE($1:$232);1))));".";",")*1
-Valider avec Maj+Ctrl+Entrée

A1 contient Equipe design Plans d'exécution et estimé 40 jours Lun 12-10-29 Ven 12-12-21. On veut 40

=STXT(A1;EQUIV(VRAI;ESTNUM(--(STXT(A1;LIGNE($1:$255);1)));0);CHERCHE("jour";A1)-
EQUIV(VRAI;ESTNUM(--(STXT(A1;LIGNE($1:$255);1)));0))
valider avec Maj+ctrl+entrée

A1 contient abcd55 ef-ghi 2 5 7 1 3 4 14 13

=STXT(A1;NBCAR(A1)-EQUIV(FAUX;ESTNUM(--(STXT(SUBSTITUE(A1;" ";"0");NBCAR(SUBSTITUE(A1;" ";"0"))-LIGNE($1:$255);1)));0)+1;99)
valider avec Maj+ctrl+entrée

A1 contient 3 Apprenti Sorcier 208

=GAUCHE(A1;CHERCHE(" ";A1)-1) -->3
=DROITE(A1;EQUIV(" ";STXT(A1;NBCAR(A1)-LIGNE($1:$255);1);0)) --> 208

S'il n'y a pas d'espace

=GAUCHE(A1;EQUIV(VRAI;NON(ESTNUM(--(STXT(A1;LIGNE($1:$255);1))));0)-1) --> 3
=DROITE(A1;EQUIV(VRAI;NON(ESTNUM(--(STXT(A1;NBCAR(A1)-LIGNE($1:$255);1))));0)) -->208

Transformation rue

=STXT(A1;EQUIV(FAUX;ESTERREUR(TROUVE(STXT(A1;LIGNE($1:$255);1);"ABCDEFGHIJKLMNOPQRSTUVWXYZ"));0);999)&
"("&GAUCHE(A1;EQUIV(FAUX;ESTERREUR(TROUVE(STXT(A1;LIGNE($1:$255);1);"ABCDEFGHIJKLMNOPQRSTUVWXYZ"));0)-1)&")"
Valider avec Maj+Ctrl+Entrée

Nombre de majuscules en A1

=SOMMEPROD(((CODE(STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)));1))>=65)*
(CODE(STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)));1))<=96)))
ou
=NBCAR(A1)-SOMMEPROD(--ESTERREUR(TROUVE(STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)));1);
"ABCDEFGHIJKLMNOPQRSTUVWXYZ")))
ou
=NBCAR(A1)-SOMMEPROD(--ESTERREUR(TROUVE(STXT(A1;LIGNE($1:$255);1);"ABCDEFGHIJKLMNOPQRSTUVWXYZ")))

Elimine le numérique en début de chaîne

=SUPPRESPACE(STXT(A1;EQUIV(FAUX;ESTNUM(--(STXT(A1;LIGNE($1:$255);1)));0);999))
Valider avec Maj+ctrl+entrée

Caractères invalides

On veut connaître le nombre de caractères invalides en B2

=SOMMEPROD(--(ESTERREUR(CHERCHE(STXT(B2;LIGNE($1:$255);1);A2))))

Séparation numérique

=GAUCHE(A2;EQUIV(VRAI;ESTNUM(CNUM(STXT(A2;LIGNE($1:$255);1)));0)-2)
Valider avec Maj+ctrl+entrée

=STXT(A2;EQUIV(VRAI;ESTNUM(CNUM(STXT(A2;LIGNE($1:$255);1)));0);99)
Valider avec Maj+ctrl+entrée

Séparation majuscule

=STXT(A1;1;EQUIV(VRAI;EXACT(STXT(A1;LIGNE($2:$255);1);MAJUSCULE(STXT(A1;LIGNE($2:$255);1)));0))
valider avec maj+ctrl+entrée

=STXT(A1;EQUIV(VRAI;EXACT(STXT(A1;LIGNE($2:$255);1);MAJUSCULE(STXT(A1;LIGNE($2:$255);1)));0)+1;999)
valider avec maj+ctrl+entrée

Compter les AB + CD dans le champ A1:A5

AB|AB|CD|SD|AB
ZZ|AB|CD|SD|AB
CD|SD|ZZ

=SOMMEPROD((NBCAR(A1:A5)-NBCAR(SUBSTITUE(A1:A5;{"AB"."CD"};"")))/2)

Recherche du mot après la date

On veut extraire le mot après la date 040310 (Shell)

Paiement Carte 040310 Shell 2562 Montereau

MotAprèsDate

=GAUCHE(STXT(A1;EQUIV(VRAI;ESTNUM(CNUM(STXT(A1;LIGNE($1:$255);1)));0)+7;999);
CHERCHE(" ";STXT(A1;EQUIV(VRAI;ESTNUM(CNUM(STXT(A1;LIGNE($1:$255);1)));0)+7;999))-1)
valider avec maj+ctrl+entrée

Position de la première lettre majuscule dans une chaîne

Chaîne en A1: abc123MNP

=MIN(SI(ESTERREUR(TROUVE(CAR(LIGNE($65:$90));A1));"";TROUVE(CAR(LIGNE($65:$90));A1)))
valider avec maj+ctrl+entrée

Nombre de caractères communs entres 2 chaînes (en A2 et B2)

=SOMMEPROD(NBCAR(B2)-NBCAR(SUBSTITUE(B2;STXT(A2;LIGNE($1:$255);1);"")))

Dates

Nombres de jours ouvrés entre 2 dates (samedi jour ouvré)

Les dates sont en A2 et B2

=SOMMEPROD((JOURSEM(LIGNE(INDIRECT(A2&":"&B2));2)<7)*(NB.SI(fériés;LIGNE(INDIRECT(A2&":"&B2)))=0))

Nombre de jours ouvrés entre 2 dates en A2,B2 (sans samedi,dimanche, jours fériés, vacances)

=SOMMEPROD((JOURSEM(LIGNE(INDIRECT(A2&":"&B2));2)<6)*(NB.SI(fériés;LIGNE(INDIRECT(A2&":"&B2)))=0)*
(ESTNA(EQUIV(LIGNE(INDIRECT(A2&":"&B2));LIGNE(INDIRECT(dvac1&":"&fvac1));0)))*
(ESTNA(EQUIV(LIGNE(INDIRECT(A2&":"&B2));LIGNE(INDIRECT(dvac2&":"&fvac2));0))))

Nombre de jours entre 2 dates (différents de mercredi et dimanche )

Les dates sont en A1 et B1

=SOMMEPROD(--ESTNA(EQUIV(JOURSEM(LIGNE(INDIRECT(A1&":"&B1)));{1;4};0)))

Samedis non férié entre 2 dates en B1 et b2

=SOMMEPROD((JOURSEM(LIGNE(INDIRECT(B1&":"&B2));2)=6)*(NB.SI(fériés;LIGNE(INDIRECT(B1&":"&B2)))=0))

Dimanches non férié entre 2 dates en B1 et b2

=SOMMEPROD((JOURSEM(LIGNE(INDIRECT(B1&":"&B2));2)=7)*(NB.SI(fériés;LIGNE(INDIRECT(B1&":"&B2)))=0))

Nb de jours fériés entre 2 dates en B1 et B2

=SOMMEPROD(NB.SI(fériés;LIGNE(INDIRECT(B1&":"&B2))))

Nombre de vendredis entre 2 dates (A1,B1)

=ENT((B1-JOURSEM(B1-5)-A1+8)/7)

Nombre de dimanches dans le mois

=SOMMEPROD(--(JOURSEM(LIGNE(INDIRECT(DATE(an;Mois;1)&":"&DATE(an;Mois+1;0)));2)>6))
=ENT((DATE(an;mois+1;0)-JOURSEM(DATE(an;mois+1;0))-DATE(an;mois;1)+8)/7)

Nombre de samedis et dimanches dans le mois

=SOMMEPROD(--(JOURSEM(LIGNE(INDIRECT(DATE(an;Mois;1)&":"&DATE(an;Mois+1;0)));2)>5))

Jour ouvré précédent

La date est en A2

=MAX((JOURSEM(A2-LIGNE(1:6);2)<6)*(NB.SI(Fériés;A2-LIGNE(1:6))=0)*(A2-LIGNE(1:6)))
Valider avec Maj+Ctrl+entrée

Avec la macro complémentaire Utilitaire analyse:=SERIE.JOUR.OUVRE(A2;-1;Fériés)

Jours ouvré précédent sans le mercredi

=MAX((JOURSEM(A2-LIGNE(1:6);2)={1.2.4.5})*(NB.SI(Fériés;A2-LIGNE(1:6))=0)*(A2-LIGNE(1:6)))

Prochain jour ouvré

La date est en A2

=MIN(SI((JOURSEM(A2+LIGNE(1:6);2)<6)*(NB.SI(fériés;A2+LIGNE(1:6))=0)<>0;(A2+LIGNE(1:6))))

Liste des vendredis entre 2 dates (A1 et A2)

LundiMois

-Sélectionner n cellules
=SI(ESTNUM(PETITE.VALEUR(SI(JOURSEM(LIGNE(INDIRECT(A1&":"&A2)))=6;LIGNE(INDIRECT(A1&":"&A2)));
{1;2;3;4;5}));PETITE.VALEUR(SI(JOURSEM(LIGNE(INDIRECT(A1&":"&A2)))=6;
LIGNE(INDIRECT(A1&":"&A2)));{1;2;3;4;5});"")
Valider avec Maj+Ctrl+entrée

Liste des Samedis/Dimanches et jours fériés d'une année

En A3:
=SI(A2<>"";SI(MIN(SI((JOURSEM(A2+LIGNE($1:$7);2)>5)+(NB.SI(fériés;A2+LIGNE($1:$7))=1);A2+
LIGNE($1:$7)))<=DATE(an;12;31);
MIN(SI((JOURSEM(A2+LIGNE($1:$7);2)>5)+(NB.SI(fériés;A2+LIGNE($1:$7))=1);A2+LIGNE($1:$7)));"");"")
Valider avec Maj+Ctrl+entrée

SamediDimancheFériésAnnée
DimancheEtFériésAnnée

Liste des dimanches et jours fériés du mois choisi

-Sélectionner 9 cellules
=PETITE.VALEUR(SI((JOURSEM(LIGNE(INDIRECT(DATE(an;Mois;1)&":"&DATE(an;Mois+1;0))))=1)+(NB.SI(fériés;
LIGNE(INDIRECT(DATE(an;Mois;1)&":"&
DATE(an;Mois+1;0))))>0);LIGNE(INDIRECT(DATE(an;Mois;1)&":"&DATE(an;Mois+1;0))));LIGNE($1:$9))
Valider avec Maj+Ctrl+entrée

Liste des jours fériés de l'année qui sont des Samedi ou Dimanche

-Sélectionner 5 cellules
=PETITE.VALEUR(SI((NB.SI(fériés;LIGNE(INDIRECT(DATE(an;1;1)&":"&DATE(an;12;31))))>0)*(JOURSEM(LIGNE(INDIRECT(DATE(an;1;1)&":"&DATE(an;12;31)));2)>5);LIGNE(INDIRECT(DATE(an;1;1)&":"&DATE(an;12;31))));LIGNE($1:$5))
-Valider avec Maj+ctrl+entrée

DimancheFériésMois

=SI(ESTNUM(PETITE.VALEUR(SI((JOURSEM(LIGNE(INDIRECT(DATE(an;Mois;1)&":"&DATE(an;Mois+1;0))))=1)+
(NB.SI(fériés;LIGNE(INDIRECT(DATE(an;Mois;1)&":"&DATE(an;Mois+1;0))))>0);
LIGNE(INDIRECT(DATE(an;Mois;1)&":"&DATE(an;Mois+1;0))));LIGNE($1:$9)));
PETITE.VALEUR(SI((JOURSEM(LIGNE(INDIRECT(DATE(an;Mois;1)&":"&DATE(an;Mois+1;0))))=1)+
(NB.SI(fériés;LIGNE(INDIRECT(DATE(an;Mois;1)&":"&DATE(an;Mois+1;0))))>0);
LIGNE(INDIRECT(DATE(an;Mois;1)&":"&DATE(an;Mois+1;0))));LIGNE($1:$9));"")

x jours ouvrés plus tard (samedi, dimanche fériés)

A2 contient la date et B2 le nombre de jours ouvrés à ajouter

=PETITE.VALEUR(SI((JOURSEM(A2+LIGNE(1:50);2)<6)*(NB.SI(fériés;A2+LIGNE(1:50))=0);A2+LIGNE(1:50));B2)
valider avec maj+ctrl+entrée

x jours ouvrés plus tard (mercredi,samedi,dimanche fériés)

A2 contient la date et B2 le nombre de jours ouvrés à ajouter

=PETITE.VALEUR(SI((JOURSEM(A2+LIGNE(1:50);2)={1.2.4.5})*(NB.SI(fériés;A2+LIGNE(1:50))=0);A2+LIGNE(1:50));B2)
valider avec maj+ctrl+entrée

x jours ouvrés plus tard (dimanche férié)

A2 contient la date et B2 le nombre de jours ouvrés à ajouter

=PETITE.VALEUR(SI((JOURSEM(A2+LIGNE(1:50);2)<7)*(NB.SI(fériés;A2+LIGNE(1:50))=0);A2+LIGNE(1:50));B2)
valider avec maj+ctrl+entrée

x jours ouvrés avant (samedi,dimanche fériés)

A2 contient la date et B2 le nombre de jours ouvrés à retrancher

=GRANDE.VALEUR(SI((JOURSEM(A2-LIGNE(1:50);2)<6)*(ESTNA((EQUIV(A2-LIGNE(1:50);fériés;0))));A2-LIGNE(1:50));B2)

Liste des Jours ouvrés d'une année

1er jour en D2:

=MIN(SI((JOURSEM(DATE(an;1;0)+LIGNE($1:$7);2)<6)*(NB.SI(fériés;DATE(an;1;0)+LIGNE($1:$7))=0);
DATE(an;1;0)+LIGNE($1:$7)))
Valider avecMaj+trl+entrée

2e jour en D3:

=SI(D2<>"";SI(MIN(SI((JOURSEM(D2+LIGNE($1:$7);2)<6)*(NB.SI(fériés;D2+LIGNE($1:$7))=0);
D2+LIGNE($1:$7)))<=DATE(an;12;31);
MIN(SI((JOURSEM(D2+LIGNE($1:$7);2)<6)*(NB.SI(fériés;D2+LIGNE($1:$7))=0);D2+LIGNE($1:$7)));"");"")

JOAnnée

JOsansVacances

Liste des Jours ouvrés entre 2 dates

JO2Dates
JO2Date2

D2: =MIN(SI((JOURSEM(B1+LIGNE($1:$7);2)<6)*(NB.SI(fériés;B1+LIGNE($1:$7))=0);B1+LIGNE($1:$7)))
Valider avec Maj+ctrl+entrée
D3: =SI(D2<>"";SI(D2+1<=$B$2;MIN(SI((JOURSEM(D2+LIGNE($1:$7);2)<6)*(NB.SI(fériés;D2+LIGNE($1:$7))=0);D2+LIGNE($1:$7)));"");"")
Valider avec Maj+ctrl+entrée

F2: =MIN(SI((JOURSEM(B1+LIGNE($1:$7);2)<6)*(JOURSEM(B1+LIGNE($1:$7);2)<>3)*(NB.SI(fériés;B1+LIGNE($1:$7))=0);B1+LIGNE($1:$7)))
Valider avec Maj+ctrl+entrée
F3: =SI(F2<>"";SI(F2+1<=$B$2;MIN(SI((JOURSEM(F2+LIGNE($1:$7);2)<6)*(JOURSEM(F2+LIGNE($1:$7);2)<>3)*(NB.SI(fériés;F2+LIGNE($1:$7))=0);
F2+LIGNE($1:$7)));"");"")
Valider avec Maj+ctrl+entrée

Liste des jours ouvrés entre 2 dates (B1 et B2) sans vacances

JO2DatesSans Vacances

=PETITE.VALEUR(SI((JOURSEM(LIGNE(INDIRECT($B$1&":"&$B$2));2)<6)* (NB.SI(fériés;
LIGNE(INDIRECT($B$1&":"&$B$2)))=0)*
(ESTNA(EQUIV(LIGNE(INDIRECT($B$1&":"&$B$2));LIGNE(INDIRECT(dvac1&":"&fvac1));0)))*
(ESTNA(EQUIV(LIGNE(INDIRECT($B$1&":"&$B$2));LIGNE(INDIRECT(dvac2&":"&fvac2));0)))*
(ESTNA(EQUIV(LIGNE(INDIRECT($B$1&":"&$B$2));LIGNE(INDIRECT(dvac3&":"&fvac3));0)));
LIGNE(INDIRECT($B$1&":"&$B$2)));LIGNE($1:$260))

Liste des jours ouvrés du mois choisi

Sélectionner 25 cellules

=SI(ESTNUM(PETITE.VALEUR(SI((JOURSEM(LIGNE(INDIRECT(DATE(an;Mois;1)&":"&DATE(an;Mois+1;0)));2)<6)*
(NB.SI(fériés;LIGNE(INDIRECT(DATE(an;Mois;1)&":"&DATE(an;Mois+1;0))))=0);
LIGNE(INDIRECT(DATE(an;Mois;1)&":"&DATE(an;Mois+1;0))));LIGNE($1:$31)));
PETITE.VALEUR(SI((JOURSEM(LIGNE(INDIRECT(DATE(an;Mois;1)&":"&DATE(an;Mois+1;0)));2)<6)*
(NB.SI(fériés;LIGNE(INDIRECT(DATE(an;Mois;1)&":"&DATE(an;Mois+1;0))))=0);
LIGNE(INDIRECT(DATE(an;Mois;1)&":"&DATE(an;Mois+1;0))));LIGNE($1:$31));"")

Liste des jours fériés entre 2 dates

JF2Dates

-Sélectionner C2:M2
=SI(ESTNUM(PETITE.VALEUR(SI((NB.SI(fériés;LIGNE(INDIRECT(A2&":"&B2)))>0);
LIGNE(INDIRECT(A2&":"&B2)));COLONNE($A:$K)));
PETITE.VALEUR(SI((NB.SI(fériés;LIGNE(INDIRECT(A2&":"&B2)))>0);LIGNE(INDIRECT(A2&":"&B2)));COLONNE($A:$K));"")
-Valider maj+ctrl+entrée

Nombre de jours fériés entre 2 dates

=SOMME(--ESTNUM(SI((NB.SI(fériés;LIGNE(INDIRECT(A2&":"&B2)))>0);LIGNE(INDIRECT(A2&":"&B2)))))
-Valider maj+ctrl+entrée

On recherche un mois dans un texte

Il faut extraire le mois présent dans le texte en A1 (mars).

Le 23 mars 1995

=INDEX(TEXTE(DATE(;LIGNE(1:12);1);"mmmm");EQUIV(1;NB.SI(A1;"*"&TEXTE(DATE(;LIGNE(1:12);1);"mmmm")&"*");0))
Valider avec Maj+Ctrl+entrée

On recherche un jour dans un texte

Il faut extraire le jour présent dans le texte en A1 (jeudi).

Le jeudi 14 mars 2012

=INDEX(TEXTE(DATE(;1;LIGNE(1:7));"jjjj");EQUIV(1;NB.SI(A1;"*"&TEXTE(DATE(;1;LIGNE(1:7));"jjjj")&"*");0))
Valider avec Maj+Ctrl+entrée

Nb de jours depuis le dernier contrôle

En B3:
=SI(NB.SI(B$9:B$500;$A3);AUJOURDHUI()-MAX(SI(B$9:B$500=$A3;$A$9:$A$500));"")
Valider avec maj+ctrl+entrée

DerniereIntervention

Planning à partir d'une BD

En C5:
=SI(SOMMEPROD((B5>=Début)*(B5<=Fin)*(Entreprise=$F$1)*(Etat="Accepte"))>0;
INDEX(Client;MIN(SI((B5>=Début)*(B5<=Fin);LIGNE(Client)))-1);"")
Valider avec Maj+ctrl+entrée

CalendrierAnnuelBD

Tri matriciel

-Sélectionner D2:D8
=INDEX(champ;EQUIV(GRANDE.VALEUR(NB.SI(champ;">="&champ);
LIGNE(INDIRECT("1:"&LIGNES(champ))));NB.SI(champ;">="&champ);0))
Valider avec maj+ctrl+entrée

Tri matriciel alpha
Tri matriciel alpha sans doublons

Recherche dans un champ multi-zones

On veut le nom associé au plus grand nombre du champ livre.

Multi-zones

Si on utilise la ligne de titre B2:I2

=INDEX(NOMS;MAX((donnees=MAX(donnees*(titre="livre")))*LIGNE(donnees)*(titre="LIVRE"))-LIGNE(donnees)+1)
Valider avec maj+ctrl=+entrée

Noms de champ
donnees $B$3:$I$11
NOMS $A$3:$A$11
titre $B$2:$I$2

Si plusieurs réponses

=SI(GRANDE.VALEUR((donnees=MAX(donnees*(titre="livre")))*LIGNE(donnees)*(titre="LIVRE");LIGNES($1:1))>0;
INDEX(NOMS;GRANDE.VALEUR((donnees=MAX(donnees*(titre="livre")))*
LIGNE(donnees)*(titre="LIVRE");LIGNES($1:1))-LIGNE(donnees)+1);"")
Valider avec maj+ctrl=+entrée

Sans utiliser la ligne de titres

=INDEX(NOMS;EQUIV(MAX(LIVRE);DECALER(INDIRECT("b3:b11");;
2*(EQUIV(VRAI;(NB.SI(DECALER(INDIRECT("b3:b11");;{0;2;4;6});MAX(LIVRE))>0);0)-1));0))
Valider avec maj+ctrl=+entrée

Fonctions multi-zones

Recherche de valeurs alpha associées avec transposition

On veut obtenir la liste des valeurs alpha associées à un nom. MatricielTranspose

-Sélectionner B9:B13
=TRANSPOSE(INDEX(machine;PETITE.VALEUR(SI(nom=B8;EQUIV(machine;machine;0));
    COLONNE(INDIRECT("1:"&COLONNES(machine))))))

-Valider avec Maj+Crl+Entrée

MFC pour cacher les #Nombre:
=ESTERREUR(A2)

Tri avec doublons

On veut une copie triée du tableau en A1:D5. Il y a des doublons.

-Sélectionner G3:J7
=INDEX(INDEX(Tbl;;{1.2.3.4});EQUIV(GRANDE.VALEUR(INDEX(Tbl;;1)-LIGNE(INDEX(Tbl;;1))/10^10;{1;2;3;4;5});
INDEX(Tbl;;1)-LIGNE(INDEX(Tbl;;1))/10^10;0))
-valider maj+ctrl+entrée

Tri

Tri personnalisé

On veut un tri personnalisé dans l'ordre U D V T Q

-Sélectionner A2:E2
=STXT("UDVTQ";PETITE.VALEUR(TROUVE(A2:E2;"UDVTQ");{1.2.3.4.5});1)
-Valider avec maj+ctrl+entrée

ou

=STXT("UDVTQ";PETITE.VALEUR(TROUVE(A2:E2;"UDVTQ");COLONNE(INDIRECT("1:"&COLONNES(A2:E2))));1)

TriPerso

Fonction personnalisée matricielle

On veut compter le nombre de cellules rouges contenant Chat

=SOMMEPROD((couleurfond(B2:B10)=3)*(B2:B10="chat"))

ou

=SOMMEPROD((couleurfond(B2:B10)=couleurfond(D2))*(B2:B10="chat"))

Fonction couleur
Fonction couleur2
Fonction couleur3
Fonction couleur4

Function couleurFond(champ As Range)
  Application.Volatile
  Dim temp()
  ReDim temp(1 To champ.Count)
  For i = 1 To champ.Count
     temp(i) = champ(i).Interior.ColorIndex
  Next i
  If champ.Rows.Count > 1 Then    couleurFond = Application.Transpose(temp) Else couleurFond = temp
End Function

Pour MAJ
Dim celluleAvant
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Not IsEmpty(celluleAvant) Then
    If Not Intersect(Range(celluleAvant), [B2:B10]) Is Nothing Then Calculate
  End If
  celluleAvant = Target.Address
End Sub

Somme des cellules d'un champ hors dates

=SOMMEPROD(--(NON(estdate(champ)));champ)

Function EstDate(champ As Range)
  Application.Volatile
  Dim temp()
  ReDim temp(1 To champ.Count)
  For i = 1 To champ.Count
     temp(i) = IsDate(champ(i))
  Next i
  EstDate = Application.Transpose(temp)
End Function

Extrait de Compte en fonction du mois choisi

MontantEchéance

-En D5
=SI(LIGNES($1:1)<=SOMME(--(MOIS(Dates)=MOIS($D$3)));
INDEX(Dates;PETITE.VALEUR(SI(MOIS(Dates)=MOIS($D$3);LIGNE(INDIRECT("1:"&LIGNES(Dates))));LIGNES($1:1)));"")
-Valider avec maj+ctrl+entrée

En E5:
=SI(LIGNES($1:1)<=SOMME(--(MOIS(Dates)=MOIS($D$3)));
INDEX(BD;PETITE.VALEUR(SI(MOIS(Dates)=MOIS($D$3);LIGNE(INDIRECT("1:"&LIGNES(Dates))));LIGNES($1:1));COLONNE()-3);"")

Recherche sur une partie de code

-La table contient des codes aa,bb,cc,... Le code cherché est bb rouge

Pour obtenir le prix
=INDEX(prix;EQUIV(1;NB.SI(A2;code&"*");0)))
valider avec maj+ctrl+entrée

ou

=INDEX(prix;EQUIV(1;EQUIV(code&"*";A2;0);0))
valider avec maj+ctrl+entrée

ou

=SI(SOMME(NB.SI(A2;"*"&Code))=0;"Inconnu";INDEX(prix;EQUIV(1;NB.SI(A2;code&"*");0))))
valider avec maj+ctrl+entrée

Rech Partie Code
Rech Partie Code2
Existence code cellule table codes

On recherche une reférence abrégée à partir d'une référence commerciale

Pour PRODUIT AA ROUGE, il faut retrouver la référence abrégée PRODUIT AA puis AAAAA.

=INDEX(COMPOSANT;EQUIV(1;EQUIV(RefAbr&"*";A4;0);0))

On recherche un code dans un texte

ExtraitMot

Il faut retrouver le code présent dans le texte (ASA,APA,DPD)

=INDEX(Codes;EQUIV(1;EQUIV("*"&Codes&"*";A2;0);0))
Valider avec maj+ctrl+entrée

ou =INDEX({"ASA";"APA";"DPD"};EQUIV(1;EQUIV("*"&{"ASA";"APA";"DPD"}&"*";A2;0);0))

Autre exemple

Pour FIDJI BLACK/RED VENERE T.48, on recherche la référence associée à BLACK/RED

En B2: =INDEX(ref;EQUIV(1;NB.SI(A2;"*"&couleur&"*");0))

RechercheCouleur

Total des montants sans doublons

=SOMMEPROD((code<>"x")*(NoDos<>DECALER(NoDos;1;))*montant)

Somme des points des participants qui ont un indicateur de présence renseigné à Ok

=SOMME((Données="Ok")*(nature="presence")*(DECALER(nature;1;)="points")*(Noms=DECALER(Noms;1;))*
SI(ESTNUM(DECALER(Données;1;));DECALER(Données;1;)))+
SOMME((Données="Ok")*(nature="presence")*(DECALER(nature;-1;)="points")*(Noms=DECALER(Noms;-1;))*
SI(ESTNUM(DECALER(Données;-1;));DECALER(Données;-1;)))
-Valider avec Maj+ctrl+entrée

MatDecaler

Remarque sur l'écriture des formules matricielles (tri matriciel)

On veut obtenir en D2:D1000 le champ A2:A1000 trié

TriMat Champ
TriMat Cellule

1ere méthode: La formule matricielle est écrite une seule fois dans un champ

-Sélectionner D2:D1000
=INDEX(champ;EQUIV(GRANDE.VALEUR(NB.SI(champ;">="& champ);LIGNE(INDIRECT("1:"&LIGNES(champ))));
NB.SI( champ;">="&champ);0))
-Valider avec Maj+ctrl+entrée

Pour 1.000 éléments, si on modifie une cellule le temps de recalcul est < 1sec

2eme méthode :La formule est écrite dans la cellule D2 puis recopiée

=SI(LIGNES($1:1)<=NBVAL(champ);INDEX(champ;EQUIV(P ETITE.VALEUR(NB.SI(champ;"<"&champ);LIGNES($1:1));
NB.SI(champ;"<"&champ);0));"")
-Valider avec Maj+ctrl+entrée

Pour 1000 éléments, si on modifie une cellule, le temps de recalcul est >10 sec

En plus du gain de temps lorsque la formule est écrite une seule fois dans un champ, il y a un gain de place mémoire.

Dictionary pour remplacer Sommeprod()

Comment améliorer Sommeprod() lorsque cette fonction

- travaille sur des champs de taille importante
- est recopiée x1000 fois

Sur l'exemple en PJ, avec une fonction perso matricielle, on passe d'un temps de recalcul de 3 sec à 0,05 sec pour 4.000 lignes

=SOMMEPROD((dates=A2)*(numero=B2)) ou =CombienFois(numero; dates)

CombienPerso
CombienSommeProd

Sur l'exemple ci dessous, pour une BD de 60.000 lignes et 2x400 formules,
On obtient un temps de recalcul de 1,2 sec contre 15 sec avec Sommeprod()

MatricielPerso
Total par code
Total par code 2 critères

Somme d'une BD par catégorie

On veut la somme du CA pour le secteur primaire (secteurs A,B,C)

=SOMME(SI(ESTNUM(EQUIV(Secteur;SecteurPri;0));CAHT))

SommeBDClasse
SommeBDClasse2

 

 

 


 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


Exemples
SommeProd
NA texte
Matricielles synthèse
Recherche2Conditions
Compte noms différents
NbSi
NbSi critere
Recherche langues nom
Recherche mot champ
Recherche mot champ2
Doublons
Union diff listes
Compacte
Somme groupe
Moyenne multiCriteres
3 Premiers catégorie
Recherche tous num
Recherche tous texte
Somme sauf codes
Texte début chaîne Top5Egalité
Top5 égalité2
Recherche spécial
Decaler colonne
Recherche champ onglet
Inclus dans
Diff 2 listes
Diff2ListesNum
Communs 2 listes
Intervalles
Fonction couleur
Rang catégorie
Equiv index
Transforme colonnes
Matrice

Somme 1 colonne rur 6
Cherche
Cherche2
Cherche3
Sans doublons
Somme 1colonne sur 5 Condition
Somme 1ligne sur5 Condition
Doublé
Equiv multiple
Plus proche
Tri
MatricielDifférence
MontantEchéance
ListeComplément
SuiviKms
Chaînes

NomPrenomExtrait
Cherche droite
Découpe adresse
Extrait numérique
Matriciel majuscule

Dates

Date maxi
Date mini
Jours ouvrés
Calendrier annuel
NbJours sauf mercredi Dimanche
Glissant 12 mois
Semaines
Date échéance mini
Intervalles temps JoursOuvrésSansVacances