Formule matricielle
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.
|
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
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
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))
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
Autres exemples
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
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")
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
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.
Nb
Valeurs Uniques 1 critère
Fonction
N b Valeurs Uniques 1 critère
Fonction
Nb Valeurs Uniques 1 critère MAC
Fonction
N b Valeurs Uniques 2 critères
Fonction
Nb Valeurs Uniques 2 critères MAC
-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
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
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
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.
=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.
Recherche
Plusieurs Conditions
Recherche Plusieurs
Conditions Rapide
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
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
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
=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
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.
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))
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
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)
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
Moyenne des colonnes en jaunes sans tenir compte des 0
En S3: =MOYENNE(SI((entete="Note")*(B3:R3<>"");B3:R3))
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
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
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
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
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)
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
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
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
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
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")
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
Autre exemple
=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
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
=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));"")
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
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.
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
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)));"")
Liste conditionnelle horizontale
-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
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
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)));"")
Pour extraire la liste des noms en rouge
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
=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 €
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
ClassementEgalité
2 colonnes
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é.
-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
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
3 Premiers Caté
3 prix moins chers
produit
Rang sans tenir compte des doublons
En C2: =SOMMEPROD((notes>=B2)/NB.SI(notes;notes))
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
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
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
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
=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
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
=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)
-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
Dates
compléments
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
=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)));"");"")
Liste des Jours ouvrés entre 2 dates
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
=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
-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
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
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.
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
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 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)
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
-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
ERecherche partie
code 3
Existence code cellule table
codes
Rech code famille
Extraction
mot liste
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
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))
Autre exemple
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
Union de 2 zones
-Sélectionner E2:E19
=SI(LIGNE()-1<=NBVAL(zone1);INDEX(zone1;LIGNE()-1);INDEX(zone2;LIGNE()-1-NBVAL(zone1)))
-Valider avec maj+ctrl+entrée
Remarque sur l'écriture des formules matricielles (tri matriciel)
On veut obtenir en D2:D1000 le champ A2:A1000 trié
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)
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
SommeSi
1 critère
SommeSi 2 critères
SommeSi 3
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))
