Accueil

Somme 3D feuilles non contigues

On veut la somme des feuilles non contigues Feuil1,Feuil2,Feuil3. Nf est un champ contenant les noms des feuilles.

S3D non contigues

=SOMMEPROD(N(INDIRECT("'"&nf&"'!B2")))

Si les noms des feuilles sont génériques:

=SOMMEPROD(N(INDIRECT("'Feuil"&LIGNE($1:$3)&"'!B2")))

Somme de Janvier 18,Février 18,Mars 18 non contigues

S3D non contigues

=SOMMEPROD(N(INDIRECT("'"&TEXTE(DATE(2018;LIGNE($1:$3);1);"mmmm aa")&"'!B2")))

Entre 2 mois

=SOMMEPROD(SOMME.SI(INDIRECT("'"&TEXTE(DATE(2018;LIGNE(INDIRECT(MOIS("1/"&D2)&":"&MOIS("1/"&E2)));1);"mmmm aa")&"'!b2");">0"))

Somme 3D conditionnelle avec Somme.si()

Somme3D

On veut la somme des cellules B1 de Feuil1,Feuil2,Feuil3,... si A1="X".

=SOMMEPROD(SOMME.SI(INDIRECT("Feuil"&LIGNE(1:4)&"!A1");"X";INDIRECT("Feuil"&LIGNE(1:4)&"!B1")))

Si les noms de feuilles ne sont pas génériques

Créer un champ NomsFeuilles contenant les noms des feuilles:

=SOMMEPROD(SOMME.SI(INDIRECT(NomsFeuilles&"!A1");"X";INDIRECT(NomsFeuilles&"!B1")))

ou si les noms des feuilles sont x,y,z

=SOMMEPROD(SOMME.SI(INDIRECT({"x";"y";"z"}&"!A1");"X";INDIRECT({"x";"y";"z"}&"!B1")))

Si les noms d'onglets sont : Janvier,Février,Mars,.. Décembre

Somme 3D cond Janvier-Décembre

=SOMMEPROD(SOMME.SI(INDIRECT(TEXTE(DATE(2009;LIGNE(1:12);1);"mmmm")&"!A1");"X";
INDIRECT(TEXTE(DATE(2009;LIGNE(1:12);1);"mmmm")&"!B1")))

Somme les cellules B3 des feuilles qui contiennent NORD dans A1

=SOMMEPROD(SOMME.SI(INDIRECT("'"&nf&"'!A1");"Nord";INDIRECT("'"&nf&"'!B3")))

Somme 3D cond

Pour que la formule soit recopiable

=SOMMEPROD(SOMME.SI(INDIRECT("'"&nf&"'!A1");"Nord";DECALER(INDIRECT("'"&nf&"'!B3");LIGNE()-3;COLONNE()-2)))
=SOMMEPROD(SOMME.SI(INDIRECT("'"&nf&"'!A1");"Nord";INDIRECT("'"&nf&"'!"&ADRESSE(LIGNES($1:3);COLONNES($A:B)))))

Somme.Si 3D conditionnel sur un champ

On veut calculer les totaux de Dupont, Durand, Martin des onglets aa,bb,cc,...
Les noms des onglets sont dans un champ nommé NomFeuilles

SommeSi3D
SommeSiEns3D
S3DChamp
S3DChamp2
SommeSi3D2
SommeSi3DPlusieursColonnes
SommeSi3DDate
SommeSi3DNomsFeuillesAuto
SommeSi3DNombreFeuilles
SommeSi3DChoixFeuilles
SommeSi3DNbMoisNbLignes
Somme3DHeures
SommeSi3DMois
SommeSi3DMois2
SommeSi3DChantier
CumulMois

-Sélectionner B2
=SOMMEPROD(SOMME.SI(INDIRECT("'"&NomFeuilles&"'!A2:A10");A2;INDIRECT("'"&NomFeuilles&"'!B2:B10")))

Pour obtenir les noms des feuilles automatiquement en E2:E12

SommeSi3D2

-Créer un nom de champ NF:
=STXT(LIRE.CLASSEUR(1);TROUVE("]";LIRE.CLASSEUR(1))+1;99)&INDIRECT("iv65000")

-Sélectionner E2
=SI(LIGNES($1:1)<=NBVAL(NF);INDEX(NF;LIGNES($1:1));"")

-Créer un nom de champ NomFeuilles:=DECALER(Recap!$E$3;;;NB.SI(Recap!$E$3:$E$12;"><"&""))

Pour obtenir la somme sans noms des onglets dans la feuille

SommeSi3DSansNomsFeuille

-Créer un nom de champ
NomFeuilles =STXT(LIRE.CLASSEUR(1);TROUVE("]";LIRE.CLASSEUR(1))+1;99)&INDIRECT("iv65000")

-Sélectionner C2
=SOMMEPROD(SOMME.SI(INDIRECT("'"&NomFeuilles&"'!A2:A10");B2;INDIRECT("'"&NomFeuilles&"'!B2:B10")))

Attention! tous les onglets du classeur sont pris en compte. La formule ne doit pas être dans la même colonne
que la colonne de sommation (B sur l'exemple)

Si les noms des onglets sont génériques: Feuil1,Feuil2,Feuil3

=SOMMEPROD(SOMME.SI(INDIRECT("'Feuil"&LIGNE($1:$3)&"'!A2:A10");A2;INDIRECT("'Feuil"&LIGNE($1:$3)&"'!B2:B10")))

Si les noms des onglets sont génériques: Janvier,Février,Mars,..,Décembre

=SOMMEPROD(SOMME.SI(INDIRECT(TEXTE(DATE(2009;LIGNE($1:$12);1);"mmmm")&"!A2:A10");A2;
INDIRECT(TEXTE(DATE(2009;LIGNE($1:$12);1);"mmmm")&"!B2:B10")))

Si les noms des onglets sont Semaine 01,Semaine 02,Semaine 03
=SOMMEPROD(SOMME.SI(INDIRECT("'Semaine "&TEXTE(LIGNE($1:$3);"00")&"'!A2:A10");A2;
INDIRECT("'semaine "&TEXTE(LIGNE($1:$3);"00")&"'!B2:B10")))

Si on ajoute des semaines semaine 01,Semaine 02,Seamaine 03,...créer un nom de champ NbFeuilles: =Lire.Classeur(4)
=SOMMEPROD(SOMME.SI(INDIRECT("'Semaine "&TEXTE(LIGNE(INDIRECT("1:"&1+nbfeuilles-2));"00")&"'!d10:d25");A2;
INDIRECT("'semaine "&TEXTE(LIGNE(INDIRECT("1:"&1+nbfeuilles-2));"00")&"'!q10:q25")))

Somme3DHeures

Si les noms des onglets sont génériques: 2004,2005,…2009
=SOMMEPROD(SOMME.SI(INDIRECT(LIGNE($2004:$2009)&"!A2:A10");A2;INDIRECT(LIGNE($2004:$2009)&"!B2:B10")))

Si on ajoute des années 2010,2011,...créer un nom de champ NbFeuilles: =Lire.Classeur(4)
=SOMMEPROD(SOMME.SI(INDIRECT(LIGNE(INDIRECT("2004:"&2004+NbFeuilles-2))&"!A2:A10");A2;
INDIRECT(LIGNE(INDIRECT("2004:"&2004+NbFeuilles-2))&"!B2:B10")))

Autre exemple

Pour obtenir le nombre de kms d'un véhhicule en B3:
=SOMMEPROD(SOMME.SI(INDIRECT("'"&nomfeuilles&"'!B4:B34");$A3;INDIRECT("'"&nomfeuilles&"'!C4:C34")))

Somme3DChauffeur

Pour que la formule soit copiable pour plusieurs colonnes
En B3:
=SOMMEPROD(SOMME.SI(INDIRECT("'"&nomfeuilles&"'!B4:B34");$A3;
DECALER(INDIRECT("'"&nomfeuilles&"'!C4:C34");0;COLONNE()-2)))

Autre exemple

On choisit l'onglet de début et de fin en B1 et B2.
Les noms sont en colonne B mais pas au même endroit

En C4:
=SOMMEPROD(SOMME.SI(INDIRECT("'"&TEXTE(LIGNE(INDIRECT($B$1&":"&$B$2));"00")&"'!B4:B20");$B4;
DECALER(INDIRECT("'"&TEXTE(LIGNE(INDIRECT($B$1&":"&$B$2));"00")&"'!C4:C20");0;COLONNE()-3)))

Conso3D

Pour des noms d'onglets non génériques
Le champ nf contient le nom des feuilles, B1 contient le nom de la première feuille et B2 le nom de la dernière feuille.

=SOMMEPROD(SOMME.SI(INDIRECT("'"&DECALER(nf;EQUIV($B$1;nf;0)-1;; EQUIV($B$2;nf;0)-
EQUIV($B$1;nf;0)+1) &"'!B4:B20");$B4; DECALER(INDIRECT("'"&DECALER(nf;EQUIV($B$1;nf;0)-1;;
EQUIV($B$2;nf;0)-EQUIV($B$1;nf;0)+1)& "'!C4:C20");0;COLONNE()-3)))

Somme 3D conditionnel sur champ multi-colonnes

SommeSi3DConditionnelMultiColonnes
SommeSi3DPlusieursColonnes

Nom de champ à créer
NomsFeuilles =Feuille1!$E$2:$E$4

=SOMMEPROD(SOMME.SI(INDIRECT("'"&NomsFeuilles&"'!A2:A10");A2;DECALER(INDIRECT("'"&NomsFeuilles&"'!B2:B10");0;
COLONNE(A:AE)-1)))

VBA

SommeSi3DConditionnelMultiColonnes

=S3DMultiCol(2; 4; "A2:A10";A2; "B2:B10";31)

Function S3DMultiCol(début, fin, champCritère, critère, champSomme, nbCol)
  tot = 0
  For s = début To fin
    For c = 1 To nbCol
      tot = tot + Application.SumIf(Sheets(s).Range(champCritère), critère, Sheets(s).Range(champSomme).Offset(, c - 1))
    Next c
  Next s
  S3DMultiCol = tot
End Function

Noms de feuilles génériques et nombre de feuilles variable

Nom de champ
NbFeuilles =LIRE.CLASSEUR(4)&INDIRECT("iv65000")

En B5:
=SOMMEPROD(SOMME.SI(INDIRECT("'Feuille"&LIGNE(INDIRECT("1:"&NbFeuilles-1))&"'!A2:A100");A5;
INDIRECT("'Feuille"&LIGNE(INDIRECT("1:"&NbFeuilles-1))&"'!c2:c100")))

Somme3DGénérique
Somme3DNonGénérique
Somme3DNonGénérique2

En VBA

=s3d(2;NbFeuilles;"A2:A100";A5;"C2:C100")

Function S3D(début, fin, champCritère, critère, champSomme)
  tot = 0
  For s = début To fin
    tot = tot + Application.SumIf(Sheets(s).Range(champCritère), critère, Sheets(s).Range(champSomme))
  Next s
  S3D = tot
End Function

Somme 3D sur un champ variable

Il y a plusieurs colonnes. On veut la somme d'une colonne pour une date.

1-Il y a plusieurs colonnes. Les colonnes des champs à sommer sont au même endroit dans les feuilles

=SOMMEPROD(SOMME.SI(INDIRECT("'"&Nf&"'!A2:A100");A5;DECALER(INDIRECT("'"&Nf&"'!c2:c100");;
EQUIV(B5;aa!$C$1:$K$1;0)-1)))

Somme3DNonGénériquePlusieursColonnes

2-Les colonnes des champs à sommer ne sont pas au même endroit dans les feuilles

Il n'y a pas de solution avec Sommeprod()

En VBA: =s3D2(2;NbFeuilles;"A2:A100";A5;"C2:C100";B5;"C1:Z1")

Somme3DNonGénériquePlusieursColonnes

Function S3D2(début, fin, champCritère, critère, champSomme, critère2, ChampCritère2)
  tot = 0
  For s = début To fin
    p = Application.Match(critère2, Sheets(s).Range(ChampCritère2), 0)
    If Not IsError(p) Then tot = tot + Application.SumIf(Sheets(s).Range(champCritère), critère,
      Sheets(s).Range(champSomme).Offset(, p - 1))
  Next s
  S3D2 = tot
End Function

Somme 3D avec feuilles non contigües

Somme des onglets sélectionnés

On veut obtenir la somme des cellules B2 des onglets sélectionnés en colonne H.

Le total s'obtient avec

=SOMME(SI(cond="x";N(INDIRECT(nf&"!B2"))))
Valider avec maj+ctrl+entrée

Pour une formule copiable en ligne et colonne
=SOMME(SI(cond="x";N(DECALER(INDIRECT(nf&"!B2");LIGNE()-2;COLONNE()-2))))
Valider avec maj+ctrl+entrée

Somme3DSelection
Sum3D
Sum3DCopy

Autre exemple

Un champ nommé Agents contient les noms des feuilles à sommer.

En C2:
=SOMMEPROD(SOMME.SI(INDIRECT(Agents&"!C" & LIGNE());"<>0"))
ou
=SOMMEPROD(N(INDIRECT(Agents&"!c"&LIGNE())))

Somme3DNonContigües

Si les noms des feuilles sont génériques (Feuil1,Feuil2,...Feuil4):

=SOMMEPROD(SOMME.SI(INDIRECT("feuil"&LIGNE($1:$4)&"!C" & LIGNE());">0"))

Somme des onglets non contigus commençant par FRA

On veut la somme des cellules B3 des onglets commençant par FRA.

Somme Feuilles Noms Commence Par
Compte Feuilles Noms Commence Par
Somme Feuilles Noms Commence Par 01
Somme Feuilles Noms Commence Par 01 2 conditions

Pour obtenir les noms des feuilles automatiquement

-Créer un nom de champ nf:
=STXT(LIRE.CLASSEUR(1);TROUVE("]";LIRE.CLASSEUR(1))+1;99)&INDIRECT("iv65000")

Total en B3

=SOMME(SI(GAUCHE(NF;3)=$B$1;N(INDIRECT("'"&NF&"'!B3"))))
Valider avec maj+ctrl+entrée

Nombre de 1 dans les cellules A1 des feuilles Fiche1,Fiche2,Fiche3,….

=SOMMEPROD((GAUCHE(nf;5)="Fiche")*(N(INDIRECT("'"&nf&"'!A1"))=1))

Compte3DCommencePar

Pour toutes les feuilles

=SOMMEPROD(--(N(INDIRECT("'"&nf&"'!A1"))=1))
ou
=SOMMEPROD(NB.SI(INDIRECT("'"&nf&"'!A1");1))

Somme 3D entre un mois de départ et un mois de fin

Pour une cellule

S3DMois

=SOMMEPROD(N(INDIRECT(TEXTE(DATE(2006;LIGNE(INDIRECT(MOIS("1/"&B2)&":"&MOIS("1/"&B3)));1);"mmmm")&"!B2")))

Pour un champ

=SOMMEPROD(SOMME.SI(INDIRECT(TEXTE(DATE(2006;LIGNE(INDIRECT(MOIS("1/"&B2)&":"&
MOIS("1/"&B3)));1);"mmmm")&"!A1:A10");">0"))

Somme 3D entre une feuille de début et une feuille de fin avec noms de feuilles génériques

=SOMMEPROD(SOMME.SI(INDIRECT("feuil"&LIGNE(INDIRECT(B2&":"&B3))&"!A1");"<>0"))

Ou

=SOMMEPROD((N(INDIRECT("'Feuil"&LIGNE(INDIRECT(B2&":"&B3))&"'!A1"))))

S3DVariable

Somme 3D entre une feuille de début et une feuille de fin avec noms de feuilles non génériques

-Les noms des feuilles sont en F2:F6.
-La feuille de début est spécifiée en B2 et la feuille de fin en B3.

=SOMMEPROD(SOMME.SI(INDIRECT(DECALER(NomFeuilles;EQUIV(B2;NomFeuilles;0)-1;;
   EQUIV(B3;NomFeuilles;0)-EQUIV(B2;NomFeuilles;0)+1)&"!A1:A10");">0"))

S3DVariable

NB.si 3 dimensions compte avec condition

On veut compter le nombre de X en A1 dans Feuil1,Feuil2,Feuil3,...

=SOMMEPROD(NB.SI(INDIRECT("Feuil"&LIGNE(1:4)&"!A1");"X"))

NBSi3D
NBSI3D
NBSI3D2
NBSI3D3
NBSI3D4

Si les noms des onglets sont janvier,février,mars,...,décembre

=SOMMEPROD(NB.SI(INDIRECT(TEXTE(DATE(2010;LIGNE(1:12);1);"mmmm")&"!B2");"x"))

Si les noms des onglets sont dans un champ nommé nf

=SOMMEPROD(NB.SI(INDIRECT("'"&nf&"'!A1");"X"))

Autre exemple

Mat3DNbSI

NB.SI 3D compte 0

On veut compter le nombre de 0 dans les champs A2:A10 des differents onglets

Compte3DConditionnel1

=SOMMEPROD(NB.SI(INDIRECT($C$2:$C$6&"!A2:A10");"0"))

Pour obtenir les noms des onglets automatiquement

-Sélectionner C1:C6
-=NomsOnglets()
-Valider avec Maj+Ctrl+Entrée

Function NomsOnglets() ' fonction matricielle
  Application.Volatile
  Dim temp()
  ReDim temp(1 To Sheets.Count)
  j = 1
  For i = 1 To Sheets.Count
    temp(j) = Sheets(i).Name
    j = j + 1
  Next i
  NomsOnglets = Application.Transpose(temp)
End Function

Noms de feuilles génériques

Si les feuilles sont nommées Feuil1,Feuil2,....

=SOMMEPROD(NB.SI(INDIRECT("feuil"&LIGNE(1:5)&"!A2:A10");"0"))

- Compte3DConditionnel2 -

Autres exemples

CompteNombreRdansChamp
CompteNombreRdansChamp2
NBNbLignes3D

Compte 3D pour des feuilles contigues avec Frequence(champ;intervalle)

Nombres<=100: =FREQUENCE(Feuil1:Feuil4!A2:A100;100)
Nombres>100: =INDEX(FREQUENCE(Feuil1:Feuil4!A2:A100;100);2)
Nombres compris entre 101 et 300: =INDEX(FREQUENCE(Feuil1:Feuil4!A2:A100;{100;300});2)

3DFrequence

Compte le nombre de cellules occupées dans la colonne A de toutes les feuilles

Nom de champ à créer
nf =STXT(LIRE.CLASSEUR(1);TROUVE("]";LIRE.CLASSEUR(1))+1;99)&INDIRECT("iv65000")

=SOMMEPROD(NB.SI(INDIRECT("'"&nf&"'!A:A");"><"&""))

Somme 3D multi-conditions avec SommeProd() et N()

Somme de feuilles non contiguës

=SOMMEPROD(N(INDIRECT(nf&"!A1")))

Somme de feuilles génériques non contigües

Donne la somme de Feuil1,Feuil2,Feuil3,Feuil4 même si elles ne sont pas contigües

=SOMMEPROD((N(INDIRECT("'Feuil"&LIGNE(1:4)&"'!A1"))))

Noms de feuilles génériques et nombre de feuilles variable

Les feuilles à additionner sont nommées A1,A2,A3,...Leur nombre est variable

S3DNbFeuillesVariable

=SOMMEPROD((N(INDIRECT("'A"&LIGNE(INDIRECT("1:"&nbFeuilles-1))&"'!C2"))))

Somme3DJourMois

Somme entre 2 feuilles variables

-Les noms des feuilles sont génériques Feuil1,Feuil2,....
-On veut la somme entre les feuilles 2 et 4

=SOMMEPROD((N(INDIRECT("'Feuil"&LIGNE(INDIRECT(B1&":"&B2))&"'!A2"))))

Somme3D2Feuilles
Somme3D2ChampFeuilles

Les noms des feuilles (feuil2,feuil4) en B1 et B2

=SOMMEPROD((N(INDIRECT("'Feuil"&LIGNE(INDIRECT(STXT(B1;6;2)&":"&STXT(B2;6;2)))&"'!A2"))))

Pour des noms de feuilles non génériques: Si NomsFeuilles est un champ contenant les noms des feuilles.

Somme3D2Feuilles

=SOMMEPROD((N(INDIRECT(DECALER(NomsFeuilles;EQUIV(B1;NomsFeuilles;0);;EQUIV(B2;NomsFeuilles;0)-
EQUIV(B1;NomsFeuilles;0)+1)&"!A2"))))

Somme de Feuil1,Feuil5,Feuil9,Feuill11

=SOMMEPROD((N(INDIRECT("'Feuil"&{1;5;9;11}&"'!A1")) ))

Somme de Feuil1,Feuil4,Feuil7,Feuill10

Les noms des feuilles sont génériques de 3 en 3(Feuil1,Feuil4,Feuil7,Feuill10)

=SOMMEPROD((N(INDIRECT("'Feuil"&LIGNE(1:4)*3-2&"'!A1"))))

Nombre de feuilles variables avec noms de feuilles générique

Créer un nom de champ NbFeuilles =LIRE.CLASSEUR(4)

=SOMMEPROD((N(INDIRECT("'Feuil"&LIGNE(INDIRECT("1:"&NbFeuilles-1))&"'!A2"))))

Nombre de feuilles variables avec noms de feuilles non génériques

S3DNombreFeuillesVariable
S3DNombreFeuillesVariable2

Créer un nom de champ nf =STXT(LIRE.CLASSEUR(1);TROUVE("]";LIRE.CLASSEUR(1))+1;99)&INDIRECT("iv65000")

=SOMMEPROD(N(INDIRECT("'"&nf&"'!C5")))

Somme avec 1 condition

Somme3D

=SOMMEPROD(N(INDIRECT("'"&D2:D5&"'!b2"))*(T(INDIRECT("'"&D2:D5&"'!a2"))="x"))

Avec noms de feuilles génériques (Feuil1,Feuil2,..,Feuil4)

=SOMMEPROD((T(INDIRECT("'Feuil"&{1.2.3.4}&"'!a2"))="x")*N(INDIRECT("'Feuil"&{1.2.3.4}&"'!C2")))

ou
=SOMMEPROD((T(INDIRECT("'Feuil"&LIGNE(1:4)&"'!a2"))="x")*N(INDIRECT("'Feuil"&LIGNE(1:4)&"'!C2")))

Avec 2 conditions

=SOMMEPROD((T(INDIRECT("'Feuil"&{1.2.3.4}&"'!a2"))="x")*(T(INDIRECT("'Feuil"&{1.2.3.4}&"'!b2"))="x")*
N(INDIRECT("'Feuil"&{1.2.3.4}&"'!c2")))

ou
=SOMMEPROD((T(INDIRECT("'Feuil"&LIGNE(1:4)&"'!a2"))="x")*(T(INDIRECT("'Feuil"&LIGNE(1:4)&"'!b2"))="x")*
N(INDIRECT("'Feuil"&LIGNE(1:4)&"'!c2")))

Avec 1 condition et un champ

Il y a 4 onglets: Mois1,Mois2,Mois3,Mois4 et les champs ont 5 cellules.

=SOMMEPROD((T(DECALER(INDIRECT("'Mois"&{1.2.3.4}&"'!a2");{0;1;2;3;4};0))="x")*
N(DECALER(INDIRECT("'Mois"&{1.2.3.4}&"'!C2");{0;1;2;3;4};0)))

ou

=SOMMEPROD((T(DECALER(INDIRECT("'Mois"&COLONNE(A:D)&"'!a2");LIGNE($1:$5)-1;0))="x")*
N(DECALER(INDIRECT("'Mois"&COLONNE(A:D)&"'!C2");LIGNE($1:$5)-1;0)))

Somme entre 2 Mois

En D2:

=SOMMEPROD(N(INDIRECT("'"&TEXTE(DATE(2009;LIGNE(INDIRECT(MOIS("1/"&$B$1)&":"&
MOIS("1/"&$B$2)));1); "mmmm")&"'!b" & LIGNE())))

Compte 3D avec 2 conditions

On veut le nombre de lignes pour VGA=OUI

=SOMMEPROD((T(DECALER(INDIRECT(TEXTE(DATE(2009;{1.2.3.4.5.6.7.8.9.10.11.12};1);"mmmm")&"!a2");
LIGNE(1:5)-1;0))="VGA")*
(T(DECALER(INDIRECT(TEXTE(DATE(2009;{1.2.3.4.5.6.7.8.9.10.11.12};1);"mmmm")&"!b2");LIGNE(1:5)-1;0))="OUI"))

ou
=SOMMEPROD((T(DECALER(INDIRECT(TEXTE(DATE(2009;COLONNE(A:L);1);"mmmm")&"!a2");LIGNE(1:5)-1;0))="VGA")*
(T(DECALER(INDIRECT(TEXTE(DATE(2009;COLONNE(A:L);1);"mmmm")&"!b2");LIGNE(1:5)-1;0))="OUI"))

Somme 3D avec 2 conditions

Somme 3D 2 conditions

=SOMMEPROD((T(DECALER(INDIRECT(TEXTE(DATE(;COLONNE(A:L);1);"mmmm")&"!d2");LIGNE(1:100)-1;0))="Aurélie")*
(T(DECALER(INDIRECT(TEXTE(DATE(;COLONNE(A:L);1);"mmmm")&"!x2");LIGNE(1:100)-1;0))="OUI")*(N(DECALER(INDIRECT(TEXTE(DATE(;COLONNE(A:L);1);"mmmm")&"!aa2");LIGNE(1:100)-1;0))))

Somme de 12 feuilles discontinues nommées A-01,A-02,..,A-A11,A-12

=SOMMEPROD(N(INDIRECT("'A-"&TEXTE(LIGNE(1:12);"00")&"'!C50")))

Compte 3D entre 2 dates

On veut connaître pour une personne le nombre de jours de congés entre 2 dates

=SOMMEPROD((N(DECALER(INDIRECT(TEXTE(DATE(2009;{1.2.3.4.5.6.7.8.9.10.11.12};1);"mmmm")&"!B5");;LIGNE($1:$31)-1))>=$B$6)*(N(DECALER(INDIRECT(TEXTE(DATE(2009;{1.2.3.4.5.6.7.8.9.10.11.12};1);"mmmm")&"!b5");;LIGNE($1:$31)-1))<=$B$7)*(T(DECALER(INDIRECT(TEXTE(DATE(2009;{1.2.3.4.5.6.7.8.9.10.11.12};1);"mmmm")&"!b5");
EQUIV($B$5;Janvier!$A$6:$A$25;0);LIGNE($1:$31)-1))=A9))

Planning3D

ou
=SOMMEPROD((N(DECALER(INDIRECT(TEXTE(DATE(2009;COLONNE(A:L);1);"mmmm")&"!B5");;LIGNE($1:$31)-1))>=$B$6)*
(N(DECALER(INDIRECT(TEXTE(DATE(2009;COLONNE(A:L);1);"mmmm")&"!b5");;LIGNE($1:$31)-1))<=$B$7)*
(T(DECALER(INDIRECT(TEXTE(DATE(2009;COLONNE(A:L);1);"mmmm")&"!b5");
EQUIV($B$5;Janvier!$A$6:$A$25;0);LIGNE($1:$31)-1))=A9))

Somme 3D avec 3 conditions

On veut la somme des valeurs pour la personne A dont date1 est < à 01/01/10 et dont date2 est > à 01/01/10.

Mat3D3cond
Mat3DSomme3cond

=SOMMEPROD((ESTNUM(CHERCHE($A3;T(DECALER(INDIRECT("Feuil"&COLONNE(A:C)&"!a2");LIGNE($1:$20)-1;0)))))*
(N(DECALER(INDIRECT("Feuil"&COLONNE(A:C)&"!c2");LIGNE($1:$20)-1;0))<=B$2)*
(N(DECALER(INDIRECT("Feuil"&COLONNE(A:C)&"!d2");LIGNE($1:$20)-1;0))>=B$2)*
(N(DECALER(INDIRECT("Feuil"&COLONNE(A:C)&"!b2");LIGNE($1:$20)-1;0))))

-Colonne(A:C) représente 3 feuilles (à adapter).
-LIGNE($1:$20) représente le nb de lignes.

Somme 3D pour un chantier et un nom

=SOMMEPROD((T(DECALER(INDIRECT(TEXTE(DATE(2009;COLONNE(A:L);1);"mmmm")&"!a4");LIGNE($1:$1000)-1;0))=$A4)*
(T(DECALER(INDIRECT(TEXTE(DATE(2009;COLONNE(A:L);1);"mmmm")&"!b4");LIGNE($1:$1000)-1;0))=B$4)*
(N(DECALER(INDIRECT(TEXTE(DATE(2009;COLONNE(A:L);1);"mmmm")&"!c4");LIGNE($1:$1000)-1;0))))

Total pour un chantier, un nom et du mois 1 au mois N

=SOMMEPROD((T(DECALER(INDIRECT(TEXTE(DATE(2009;COLONNE(INDIRECT("a:"&F4));1);
"mmmm")&"!a4");LIGNE($1:$1000)-1;0))=$A4)*
(T(DECALER(INDIRECT(TEXTE(DATE(2009;COLONNE(INDIRECT("a:"&F4));1);"mmmm")&"!b4");LIGNE($1:$1000)-1;0))=B$4)*
(N(DECALER(INDIRECT(TEXTE(DATE(2009;COLONNE(INDIRECT("a:"&F4));1);"mmmm")&"!c4");LIGNE($1:$1000)-1;0))))

Somme3D2Conditions

Somme 3D conditionnelle sur une expression

On veut calculer le total des CA par mois de Feuil1,Feuil2,Feuil3,Feuil4

=SOMMEPROD((MOIS(N(DECALER(INDIRECT("feuil"&{1.2.3.4}&"!A2");LIGNE($1:$50)-1;0)))=B2)*
N(DECALER(INDIRECT("feuil"&{1.2.3.4}&"!B2");LIGNE($1:$50)-1;0)))

Mat3dExpression

Par semaine

A2 contient le numéro de semaine (1,2,3,..)

=SOMMEPROD((ENT(MOD(ENT((N(DECALER(INDIRECT("feuil"&{1.2.3.4}&"!A2");LIGNE($1:$50)-1;0))-2)/7)+0,6;52+5/28))+1=B2)*
N(DECALER(INDIRECT("feuil"&{1.2.3.4}&"!B2");LIGNE($1:$50)-1;0)))

Noms de feuilles non génériques

En C2:
=SOMMEPROD((MOIS(N(DECALER(INDIRECT(nf&"!A2");LIGNE($1:$50)-1;0)))=B2)*
N(DECALER(INDIRECT(nf&"!B2");LIGNE($1:$50)-1;0)))

S3DExpression

Somme 3D avec nom local à chaque feuille

On veut obtenir la somme des cellules nommées Galva dans chaque feuille.

=SOMMEPROD((N(INDIRECT(nfeuilles&"!galva"))))

Si les noms de feuille sont génériques (feuil1,Feuil2,..) :=SOMMEPROD((N(INDIRECT("feuil"&LIGNE(1:4)&"!galva"))))

ou

=SOMMEPROD(SOMME.SI(INDIRECT(nfeuilles&"!galva");">0"))

Somme3DNomChamp

Recherche 3D

On recherche le salaire de Durand dans les feuilles Feuil1,Feuil2,.....

Rech3D
Mat3DRecherche
Mat3DRecherche2

1-On recherche le nom de la feuille qui contient le nom

=NB.SI(INDIRECT("'"&nf&"'!A2");A2) (en matriciel) --> 0 1 0 0
=(NB.SI(INDIRECT("'"&nf&"'!A2");A2)>0) -->FAUX VRAI FAUX FAUX
=EQUIV(VRAI;(NB.SI(INDIRECT("'"&nf&"'!A2");A2)>0);0) --> 2
=INDEX(nf;EQUIV(VRAI;(NB.SI(INDIRECT("'"&nf&"'!A2");A2)>0);0)) --> Feuil2

2-L'adressage indirect permet d'accéder au salaire associé au nom

=INDIRECT("'"&INDEX(nf;EQUIV(VRAI;(NB.SI(INDIRECT("'"&nf&"'!A2");A2)>0);0))&"'!B2")
Valider avec maj+ctrl+entrée

Autre exemple

On recherche l'onglet qui contient le code cherché (A4).

=INDEX(nf;EQUIV(VRAI;N(INDIRECT("'"&nf&"'!C8"))=A4;0))
Valider avec maj+ctrl+entrée

Pour obtenir le nombre d'employés

=INDIRECT("'"&B4&"'!C4")
ou
=INDIRECT("'"&INDEX(nf;EQUIV(VRAI;N(INDIRECT("'"&nf&"'!C2"))=A4;0))&"'!C4")

Recherche3D

Autre exemple

Un classeur contient des onglets avec des nos de mandat.

Recherche3D

Dans un onglet de synthèse, on veut obtenir le nom de l'onglet où est situé chaque mandat.

Noms de champs à créer avec Insertion/Nom/Définir
Nf =STXT(LIRE.CLASSEUR(1);TROUVE("]";LIRE.CLASSEUR(1))+1;99)&INDIRECT("iv65000")
NomsFeuilles =DECALER(Synthèse!$H$3;;;NB.SI(Synthèse!$H$3:$H$22;"><"&""))

Pour obtenir en B2 le nom de l'onglet où est situé le mandat en A2.
=INDEX(NomsFeuilles;EQUIV(VRAI;(NB.SI(INDIRECT("'"&NomsFeuilles&"'!B2:B100");A2)>0);0))
Valider avec Maj+ctrl+entrée

Pour obtenir les noms d'onglets sous forme d'hyperlien:

=SI(LIGNES($1:1)<=NBVAL(Nf);LIEN_HYPERTEXTE("#'"&INDEX(Nf;LIGNES($1:1)) & "'!A1";INDEX(Nf;LIGNES($1:1)));"")

Recherche3D/Réponses multiples

On recherche la liste de tous les onglets qui contiennent un code.

MatRech3D

=SI(SOMMEPROD(--(NB.SI(INDIRECT("'"&nf&"'!A2:A100");$A2)>0))>=COLONNES($A:A);
INDEX(nf;PETITE.VALEUR(SI((NB.SI(INDIRECT("'"&nf&"'!A2:A100");$A2)>0)=VRAI;LIGNE(INDIRECT("1:"&LIGNES(nf))));
COLONNES($A:A)));"")

Pour une recherche sur une partie du code

=SI(SOMMEPROD(--(NB.SI(INDIRECT("'"&nf&"'!A2:A100");"*"&$A2&"*")>0))>=COLONNES($A:A);
INDEX(nf;PETITE.VALEUR(SI((NB.SI(INDIRECT("'"&nf&"'!A2:A100");"*"&$A2&"*")>0)=VRAI;
LIGNE(INDIRECT("1:"&LIGNES(nf))));COLONNES($A:A)));"")

Si les noms des onglets sont Feuil1,Feuil2,Feuil3

=SI(SOMMEPROD(--(NB.SI(INDIRECT("'Feuil"&LIGNE($1:$3)&"'!A2:A100");$A2)>0))>=COLONNES($A:A);
"Feuil"&PETITE.VALEUR(SI((NB.SI(INDIRECT("'Feuil"&LIGNE($1:$3)&"'!A2:A100");$A2)>0)=VRAI;LIGNE($1:$3));
COLONNES($A:A));"")

Recherche d'un mot dans tous les onglets d'un classeur

On recherche les adresses des cellules contenant le mot cherché.
Pour utiliser dans un autre classeur, faire glisser la feuille Interro dans le classeur.

En D1:
=SI(SOMMEPROD(--(NB.SI(INDIRECT("'"&nf&"'!A1:M30");$A2)>0))>=COLONNES($A:A);
INDEX(nf;PETITE.VALEUR(SI((NB.SI(INDIRECT("'"&nf&"'!A1:M30");$A2)>0)=VRAI;LIGNE(INDIRECT("1:"&LIGNES(nf))));
COLONNES($A:A)));"")
Valider avec maj+ctrl+entrée

En D2:
=SI(D$1<>"";SI(NB.SI(INDIRECT("'"&D$1&"'!"&$A$5);$A$2)>=LIGNES($1:1);
ADRESSE(MOD(PETITE.VALEUR(SI(INDIRECT("'"&D$1&"'!"&$A$5)=$A$2;COLONNE(INDIRECT("'"&D$1&"'!"&$A$5))*
10^5+LIGNE(INDIRECT("'"&D$1&"'!"&$A$5)));LIGNES($1:1));10^5);
ENT(PETITE.VALEUR(SI(INDIRECT("'"&D$1&"'!"&$A$5)=$A$2;COLONNE(INDIRECT("'"&D$1&"'!"&$A$5))*10^5+
LIGNE(INDIRECT("'"&D$1&"'!"&$A$5)));LIGNES($1:1))/10^5));"");"")
Valider avec maj+ctrl+entrée

RechMotClasseur

Recherchev 3D

Retour numérique avec feuilles génériques

RechercheV3DNum
RechercheH3DNum
RechercheH3DMoyenne

=SOMMEPROD(SOMME.SI(INDIRECT("Feuil"&LIGNE(1:4)&"!A2:A6");$A$2;INDIRECT("Feuil"&LIGNE(1:4)&"!B2:B6")))

ou si L2:L5 contient le nom des feuilles:

=SOMMEPROD(SOMME.SI(INDIRECT("'"&L2:L5&"'!A2:A6");$A$2;INDIRECT("'"&L2:L5&"'!B2:B6")))

Retour numérique avec feuilles non génériques

Créer un nom de champ nf

=STXT(LIRE.CLASSEUR(1);TROUVE("]";LIRE.CLASSEUR(1))+1;99)&INDIRECT("iv65000")

En C2:
=SOMMEPROD(SOMME.SI(INDIRECT("'"&nf&"'!A2:A6");$B$2;INDIRECT("'"&nf&"'!B2:B6")))

Recherchev3DNumNonGenérique

Attention! La recherche se fait dans toutes les feuilles. La formule ne doit pas être dans la même colonne
que la colonne de retour (B sur l'exemple)

Retour alphanumérique avec noms de feuilles génériques

RechercheV3DAlphaNumGénérique
RechercheV3DAlphaNumGénérique2

Si la valeur retournée est alphabétique:
=RECHERCHEV(A2;INDIRECT("feuil"&EQUIV(VRAI;(NB.SI(INDIRECT("Feuil"&LIGNE(1:4)&"!A2:B6");A2)>0);0)&"!A2:B6");2;FAUX)
valider avec Maj+Ctrl+entrée

Equiv/Index 3D

EquivIndex3D
EquivIndex3D2
EquivIndex3D3
interro3D
interro3D2cond

Pour récupérer la position
=EQUIV(A2;INDIRECT("feuil"&EQUIV(VRAI;(NB.SI(INDIRECT("Feuil"&LIGNE(1:4)&"!A2:A6");A2)>0);0)&"!a2:a6");0)
valider avec Maj+Ctrl+entrée

Pour récupérer la valeur
=INDEX(INDIRECT("feuil"&EQUIV(VRAI;(NB.SI(INDIRECT("Feuil"&LIGNE(1:4)&"!A2:a6");A2)>0);0)&"!b2:b6");EQUIV(A2;
INDIRECT("feuil"&EQUIV(VRAI;(NB.SI(INDIRECT("Feuil"&LIGNE(1:4)&"!A2:A6");A2)>0);0)&"!a2:a6");0))
valider avec Maj+Ctrl+entrée

Pour noms de feuilles non génériques
NomsFeuilles est un nom de champ qui contient les noms des feuilles. Les valeurs cherchées sont en colonne A et la valeur
retournée en colonne B.

=INDEX(INDIRECT("'"&INDEX(nomsfeuilles;EQUIV(VRAI;(NB.SI(INDIRECT("'"&nomsfeuilles&"'!a2:a100");$A2)>0);0))&
"'!b2:b100");
EQUIV($A2;INDIRECT("'"&INDEX(nomsfeuilles;EQUIV(VRAI;(NB.SI(INDIRECT("'"&nomsfeuilles&"'!a2:a100");$A2)>0);0))&
"'!a2:a100");0))
valider avec Maj+Ctrl+entrée

Autre exemple

On veut les noms associés à des valeurs sur différentes feuilles.

Créer un nom de champ NF: =STXT(LIRE.CLASSEUR(1);TROUVE("]";LIRE.CLASSEUR(1))+1;99)&INDIRECT("iv65000")

En A4:
=INDEX(INDIRECT(INDEX("'"&nf;EQUIV(VRAI;(NB.SI(INDIRECT("'"&nf&"'!b2:b1000");A4)>0);0))&"'!a2:a1000");
EQUIV(A4;INDIRECT(INDEX("'"&nf;EQUIV(VRAI;(NB.SI(INDIRECT("'"&nf&"'!b2:b1000");A4)>0);0))&"'!b2:b1000");0))
valider avec maj+ctrl+entrée

EquivIndex3D

nf peut ête également être un champ nommé qui ne contient que les nos d'onglets où la recherche doit s'effectuer.

Equiv/Index 3D avec réponses multiples

On recherche les commandes d'un client (en A2) dans plusieurs onglets (colonne E)

Recherche3D

Nom de feuille en A10:
=SI(SOMMEPROD(--(NB.SI(INDIRECT("'"&nf&"'!E2:E100");$A$2)>0))>=LIGNES($1:1);
INDEX(nf;PETITE.VALEUR(SI((NB.SI(INDIRECT("'"&nf&"'!E2:E100");$A$2)>0)=VRAI;LIGNE(INDIRECT("1:"&
LIGNES(nf))));LIGNES($1:1)));"")
Valider avec maj+ctrl+entrée

Purchase order en B10:
=SI($A10<>"";INDEX(INDIRECT("'"&$A10&"'!a2:a100");EQUIV($A$2;INDIRECT("'"&$A10&"'!E2:E100");0));"")

Pour obtenir Purchase order sans colonne nom de feuille:
=INDEX(INDIRECT("'"&INDEX(nf;PETITE.VALEUR(SI((NB.SI(INDIRECT("'"&nf&"'!E2:E100");$A$2)>0)=VRAI;LIGNE($1:$3));
LIGNES($1:1)))&"'!a2:a100");
EQUIV($A$2;INDIRECT("'"&INDEX(nf;PETITE.VALEUR(SI((NB.SI(INDIRECT("'"&nf&"'!E2:E100");$A$2)>0)=VRAI;
LIGNE($1:$3));LIGNES($1:1)))&"'!E2:E100");0))

Autre exemple

La valeur cherchée (A2) peut être présente dans plusieurs onglets (colonne A). On veut récupérer les valeurs associées (colonne B).

En B2:
=INDEX(INDIRECT("'"&INDEX(nf;PETITE.VALEUR(SI((NB.SI(INDIRECT("'"&nf&"'!a2:a100");$A$2)>0)=VRAI;
LIGNE($1:$3));LIGNES($1:1)))&"'!b2:b100");
EQUIV($A$2;INDIRECT("'"&INDEX(nf;PETITE.VALEUR(SI((NB.SI(INDIRECT("'"&nf&"'!a2:a100");$A$2)>0)=VRAI;
LIGNE($1:$3));LIGNES($1:1)))&"'!a2:a100");0))

En C2:
=SI(SOMMEPROD(--(NB.SI(INDIRECT("'"&nf&"'!a2:a100");$A$2)>0))>=LIGNES($1:1);
INDEX(nf;PETITE.VALEUR(SI((NB.SI(INDIRECT("'"&nf&"'!a2:a100");$A$2)>0)=VRAI;LIGNE($1:$3));LIGNES($1:1)));"")

interro3DRéponsesMultiples
interro3DRéponsesMultiplesEnglish

Autre exemple

interro3DRéponsesMultiples
interro3DPremierDernier

La valeur cherchée (A2) peut être présente dans plusieurs onglets. On veut récupérer les dates associées.

En D2: Nom de l'onglet où la valeur à été trouvée
=SI(SOMMEPROD(--(NB.SI(INDIRECT("'"&nf&"'!c2:c100");$A$2)>0))>=LIGNES($1:1);
INDEX(nf;PETITE.VALEUR(SI((NB.SI(INDIRECT("'"&nf&"'!c2:c100");$A$2)>0)=VRAI;LIGNE(INDIRECT("1:"&nbOnglet-1)));
LIGNES($1:1)));"")

En C2: Date
=INDEX(INDIRECT("'"&INDEX(nf;PETITE.VALEUR(SI((NB.SI(INDIRECT("'"&nf&"'!c2:c100");$A$2)>0)=VRAI;
LIGNE(INDIRECT("1:"&nbOnglet-1)));LIGNES($1:1)))&"'!a2:a100");
EQUIV($A$2;INDIRECT("'"&INDEX(nf;PETITE.VALEUR(SI((NB.SI(INDIRECT("'"&nf&"'!c2:c100");$A$2)>0)=VRAI;
LIGNE(INDIRECT("1:"&nbOnglet-1)));LIGNES($1:1)))&"'!c2:c100");0))

Synthèse de nos de chèques

On veut un recap des nos de chèques des onglets Janvier,Février,...

ChequeRecap

Pour récupérer les nos de chèque(en A2)
=SI(ESTNUM(PETITE.VALEUR(janvier:décembre!$C$2:$C$100;LIGNES($1:1)));
PETITE.VALEUR(janvier:décembre!$C$2:$C$100;LIGNES($1:1));0)

Pour récupérer les noms des onglets où sont situés les chèques(en B2)

Si le no de chèque ne figure que sur un seul onglet
=SI(A2>0;TEXTE("1/"&EQUIV(VRAI;(NB.SI(INDIRECT(TEXTE(DATE(2009;
LIGNE($1:$12);1);"mmmm")&"!A2:C10");$A2)>0);0); "mmmm");"")
valider avec Maj+ctrl+entrée

Si le no de chèque figure sur plusieurs onglets(anomalie)
=SI(SOMMEPROD(--(NB.SI(INDIRECT("'"&nf&"'!c2:c100");$A2)>0))>=COLONNES($A:A);
INDEX(nf;PETITE.VALEUR(SI((NB.SI(INDIRECT("'"&nf&"'!c2:c100");$A2)>0)=VRAI;
LIGNE(INDIRECT("1:12")));COLONNES($A:A)));"")
valider avec Maj+ctrl+entrée

Consolidation 3D de valeurs numériques

On consolide des listes situées en Div1,Div2,Div3.

En A2: =PETITE.VALEUR(Div1:Div3!$A$1:$A$10;LIGNES($1:1))

DV3D

Consolidation 3D de tableaux

En A2: =PETITE.VALEUR(Feuil1:Feuil4!$A$1:$A$10;LIGNES($1:1)) Valider avec Maj+ctrl+entrée
En B2:=RECHERCHEV($A2;INDIRECT("Feuil"&EQUIV(VRAI;(NB.SI(INDIRECT("Feuil"&LIGNE($1:$4)&"!A2:C10");$A2)>0);0)&
"!A2:C10");2;FAUX)

Conso3DTableaux

Si les noms des feuilles (aa,bb,cc,dd) sont en J2:J5

En A2: =PETITE.VALEUR(aa:dd!$A$1:$A$10;LIGNES($1:1)) valider avec Maj+ctrl+entrée
En B2: =RECHERCHEV($A2;INDIRECT(INDEX($J$2:$J$5;EQUIV(VRAI;(NB.SI(INDIRECT($J$2:$J$5&"!A2:A10");$A2)>0);0))&
"!A2:C10");2;0)

Si les noms des feuilles sont Janvier,Février,Mars,Avril

En A2: =PETITE.VALEUR(Janvier:Avril!$A$1:$A$10;LIGNES($1:1)) valider avec Maj+ctrl+entrée
En B2: =RECHERCHEV($A2;INDIRECT(INDEX(TEXTE(DATE(2009;{1;2;3;4};1);"mmmm");
EQUIV(VRAI;(NB.SI(INDIRECT(TEXTE(DATE(2009;{1;2;3;4};1);"mmmm")&"!A2:A10");$A2)>0);0))&"!A2:C10");2;0)

Moyenne 3D conditionnelle

On veut la moyenne des nombres en B5 si B5 est >0

Les feuiiles sont 1,2,3,4,5

=SOMME('1:5'!B5)/SOMMEPROD(NB.SI(INDIRECT(LIGNE(1:5)&"!B5");">0"))

ou

=MOYENNE(SI(N(INDIRECT(LIGNE(1:5)&"!B5"))>0;N(INDIRECT(LIGNE(1:5)&"!B5"))))
valider avec maj+ctrl+entrée

ou

=SOMMEPROD(SOMME.SI(INDIRECT(LIGNE(1:5)&"!B5");">0"))/SOMMEPROD(NB.SI(INDIRECT(LIGNE(1:5)&"!B5");">0"))

Si les noms des feuilles sont dans un champ nommé nf

=MOYENNE(SI(N(INDIRECT("'"&nf&"'!B5"))>0;N(INDIRECT("'"&nf&"'!B5"))))
valider avec maj+ctrl+entrée

Moyenne3D
Moyenne3D1
Moyenne3D2

On veut la moyenne des nombres en B5 si B5 est >0 sans les #N/A

Moyenne3DSans 0 et NA

=MOYENNE(SI(ESTNUM(N(INDIRECT(LIGNE(1:5)&"!B5")));SI(N(INDIRECT(LIGNE(1:5)&"!B5"))>0;
N(INDIRECT(LIGNE(1:5)&"!B5")))))
valider avec Maj+ctrl+entrée

ou

=SOMMEPROD(SOMME.SI(INDIRECT(LIGNE(1:5)&"!B5");">0")/SOMMEPROD(NB.SI(INDIRECT(LIGNE(1:5)&"!B5");">0")))

ou

=SOMMEPROD(SOMME.SI(INDIRECT(LIGNE(1:5)&"!B5");"<>#N/A")/SOMMEPROD(NB.SI(INDIRECT(LIGNE(1:5)&"!B5");">0")))

On veut la moyenne des nombres en B5 si B5 est >5 et inférieur à 20 sans les #N/A

=MOYENNE(SI(ESTNUM(N(INDIRECT(LIGNE(1:5)&"!B5")));SI((N(INDIRECT(LIGNE(1:5)&"!B5"))>5)*
(N(INDIRECT(LIGNE(1:5)&"!B5"))<20);N(INDIRECT(LIGNE(1:5)&"!B5")))))
valider avec maj+ctrl+entrée

Maximum 3D

On recherche le CA maxi sur 12 feuilles ainsi que la date associée.
Un champ nommé nf contient les noms des feuilles

Maxi3D
Mini3D

Maximum en A3:=MAX(Janvier:Décembre!A2:A20)
Date en B3:=SOMMEPROD(SOMME.SI(INDIRECT(nf&"!a2:a20");A3;INDIRECT(nf&"!b2:B20")))
Nom de l'onglet en C3:=INDEX(nf;EQUIV(VRAI;(NB.SI(INDIRECT(nf&"!a2:a20");A3)>0);0))
valider avec Maj+ctrl+entrée
Remarque en D3:=RECHERCHEV(A3;INDIRECT(INDEX(nf;EQUIV(VRAI;(NB.SI(INDIRECT(nf&"!a2:a20");A3)>0);0))&
"!a2:c20");3;0)
valider avec Maj+ctrl+entrée

Si les onglets sont nommés Janvier,Févier,Mars,.. le champ nf n'est plus nécessaire

Date:=SOMMEPROD(SOMME.SI(INDIRECT(TEXTE(DATE(2009;LIGNE(1:7);1);"mmmm")&"!a2:a20");A3;
INDIRECT(TEXTE(DATE(2009;LIGNE(1:7);1);"mmmm")&"!b2:B20")))

Nom onglet:=TEXTE("1/"&EQUIV(VRAI;(NB.SI(INDIRECT(TEXTE(DATE(2009;
LIGNE($1:$12);1);"mmmm")&"!a2:a20");A3)>0);0); "mmmm")

Minimum 3D d'onglets non contigues

On veut le minimun des feuilles commençant par FCL

=MIN(SI(GAUCHE(nfc;3)="FCL";N(DECALER(INDIRECT("'"&nfc&"'!d3");LIGNE()-3;COLONNE()-4))))

Noms de champs
nf =STXT(LIRE.CLASSEUR(1);TROUVE("]";LIRE.CLASSEUR(1))+1;99)&INDIRECT("iv65000")
nfc =DECALER($M$2;;;NB.SI($M$2:$M$10;"><"&"")-1)

Min3DNonContigües

Minimum 3D sans les zéros

Un champ nommé nf contient les noms des feuilles

Min3DSansZero
Mini3D

=MIN(SI(N(INDIRECT(nf&"!B2"))>0;N(INDIRECT(nf&"!B2"))))
Valider avec maj+ctrl+entrée

Si les onglets sont nommés Janvier,Févier,Mars,.. le champ nf n'est plus nécessaire

=MIN(SI(N(INDIRECT(TEXTE(DATE(2009;LIGNE($1:$12);1);"mmmm")&"!B2"))>0;
N(INDIRECT(TEXTE(DATE(2009;LIGNE($1:$12);1);"mmmm")&"!B2"))))
Valider avec maj+ctrl+entrée

Cumul 3D

On veut le total de toutes les feuilles précédant la feuille courante.

Les noms des feuilles sont génériques

=INDIRECT("'Mois "&CNUM(DROITE(CELLULE("filename";A1);2))-1&"'!e1")+B1

Cumul3D

Si on veut que la formule soit copiable
=DECALER(INDIRECT("'Mois "&CNUM(DROITE(CELLULE("filename";$A$1);2))-1&"'!e1");LIGNE()-1;COLONNE()-5)+B1

Si les noms des feuilles sont Janvier,Février,..

=INDIRECT("'"&TEXTE(DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";
CELLULE("filename";A1))+1;99))-1;"mmmm")&"'!E1")+B1

Cumul3D2

On peut également sommer tous les onglets précédents

=SOMMEPROD((N(INDIRECT("'Mois "&LIGNE(INDIRECT("1:"&CNUM(DROITE(CELLULE("filename";A1);2))))&"'!B1"))))

ou si le nom de l'onglet est présent en A1

=SOMMEPROD((N(INDIRECT("'Mois "&LIGNE(INDIRECT("1:"&CNUM(DROITE(A1;2))))&"'!B1"))))

S3Dcumul

Pour une formule copiable en ligne et colonne
=SOMMEPROD((N(DECALER(INDIRECT("'Mois "&LIGNE(INDIRECT("1:"&CNUM(DROITE($A$1;2))))&"'!B1");LIGNE()-1;
COLONNE()-5))))

=SOMMEPROD(N(INDIRECT("'"&TEXTE(DATE(2010;LIGNE(INDIRECT(MOIS("1/janvier")&":"&
MOIS("1/"&A1)));1); "mmmm")&"'!B1")))

S3Dcumul2

RechercheV 3D multi-classeurs

On recherche le prix d'un produit dans plusieurs feuilles de plusieurs classeurs ouverts.
Un champ nf contient les noms des classeurs/feuilles.

En B2:=RECHERCHEV(A2;INDIRECT("'"&INDEX(nf;EQUIV(VRAI;(NB.SI(INDIRECT("'"&nf&"'!A2:A6");A2)>0);0))&"'!A2:B6");2;0)
valider avec Maj+ctrl+entrée

Nom de la feuille contenant les code cherché:
=INDEX(nf;EQUIV(VRAI;(NB.SI(INDIRECT("'"&nf&"'!A2:A6");A2)>0);0))
valider avec Maj+ctrl+entrée

Test
Test2

Somme 3D multi-classeurs

Pour additionner les cellules A1 des feuilles Feuil1 des classeurs ouverts a.xls, b.xls, c.xls,..

=SOMMEPROD(N(INDIRECT(nf&"!A1")))
ou
=SOMMEPROD(SOMME.SI(INDIRECT(nf&"!A1");"<>0"))

nf est le nom de champ qui contient le nom des classeurs+feuille (E2:E4)

Pour que les formules soient copiables en ligne/colonne

=SOMMEPROD(N(INDIRECT(nf&"!"&ADRESSE(LIGNE($1:1);COLONNE($A:A)))))
=SOMMEPROD(SOMME.SI(INDIRECT(nf&"!"&ADRESSE(LIGNE($1:1);COLONNE($A:A)));"<>0"))

Noms de classeurs génériques

Si les noms des classeurs sont 011110.xls,021110.xls,... 301110.xls (30 jours du mois de novembre 2010).

=SOMMEPROD(N(INDIRECT(TEXTE(LIGNE(1:30);"00")&"1110"&".xls!A1")))
ou
=SOMMEPROD(N(INDIRECT(TEXTE(LIGNE(1:30);"00")&"1110"&".xls!"&ADRESSE(LIGNE($1:1);COLONNE($A:A)))))

Si une cellule contient le mois(11) et une autre l'année (2010) , JOUR(DATE(An;mois+1;0)) donne le nombre de jours du mois.
Les formules ci dessus deviennent:

=SOMMEPROD(N(INDIRECT(TEXTE(LIGNE(INDIRECT("1:"&JOUR(DATE(An;mois+1;0))));"00")&mois&DROITE(An;2)&".xls!A1")))
ou
=SOMMEPROD(N(INDIRECT(TEXTE(LIGNE(INDIRECT("1:"&JOUR(DATE(An;mois+1;0))));"00")&
mois&DROITE(An;2)&".xls!"&ADRESSE(LIGNE($1:1);COLONNE($A:A)))))

Somme.Si 3D conditionnel multi-classeurs sur un champ

Pour plusieurs classeurs ouverts, il suffit d'ajouter le nom du classeur au nom de la feuille.

=SOMMEPROD(SOMME.SI(INDIRECT("'"&NomFeuilles&"'!A2:A10");A2;INDIRECT("'"&NomFeuilles&"'!B2:B10")))

Consolidation 3D de texte

On veut obtenir la liste triée des noms en colonne A des onglets choisis parmi aa,bb,cc,dd,..

Feuilles contigues

-Sélectionner B2:B34
=Liste3D("A2:A100";2;5)
Valider vec Maj+ctrl+entrée

Fonction Sans Doublons Trié 3D

-Sélectionner A2:A15
=SansDoublonsTrié3D("A1:A20";"feuil1";"feuil5")
-Valider avec maj+ctrl+entrée

Fonction Sans Doublons Trié 3DBis

Feuilles non contigues

Le champ qui contient le nom des onglets choisis est nommé nf

Pour obtenir les noms consolidés:

-Sélectionner A2:A8
=conso3D("A2:A100";nf)
-Valider avec Maj+ctrl+entrée

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

Conso3DTexte

Function conso3D(champ As String, Liste As Range)
  Application.Volatile
  Set mondico = CreateObject("Scripting.Dictionary")
  For Each s In Liste
    Set f = Sheets(s.Value)
    For Each c In f.Range(champ)
      If c.Value <> "" Then mondico(c.Value) = c.Value
    Next c
  Next s
  Dim b()
  ReDim b(1 To Application.Caller.Rows.Count)
  i = 1
  For Each c In mondico.items
    b(i) = c
    i = i + 1
  Next
  Call tri(b, 1, mondico.Count)
  conso3D = Application.Transpose(b)
End Function

Sub tri(a, gauc, droi) ' Quick sort
ref = a((gauc + droi) \ 2)
g = gauc: d = droi
Do
Do While a(g) < ref: g = g + 1: Loop
Do While ref < a(d): d = d - 1: Loop
If g <= d Then
temp = a(g): a(g) = a(d): a(d) = temp
g = g + 1: d = d - 1
End If
Loop While g <= d
If g < droi Then Call tri(a, g, droi)
If gauc < d Then Call tri(a, gauc, d)
End Sub

Récapitulatif 3D avec cellules fusionnées

En B2:
=INDEX(INDIRECT("'"&INDEX(NF;EQUIV(VRAI;(NB.SI(INDIRECT("'"&NF&"'!a2:a100");A2)>0);0))&"'!b2:b100");
EQUIV(A2;INDIRECT("'"&INDEX(NF;EQUIV(VRAI;(NB.SI(INDIRECT("'"&NF&"'!a2:a100");A2)>0);0))&"'!a2:a100");0)+
MOD(LIGNE()-2;3))
valider avec Maj+ctrl+entrée

Recap3DCellulesFusionnées

Recherche 3D VBA

Recherche toutes les commandes d'un client dans plusieurs feuilles.

Recherche3DVBA
Recherche3D2CritèresVBA
Recherche 3D 2 Critères Câbles

Function cherche3D(début, fin, clé, champRecherche, champRésultat)
  Application.Volatile
  nlig = Application.Caller.Rows.Count
  ncol = Application.Caller.Columns.Count
  Dim b()
  ReDim b(1 To nlig, 1 To ncol)
  n = 0
  For s = début To fin
    Set f = Sheets(s)
    Tab1 = f.Range(champRecherche).Value
    Tab3 = f.Range(champRésultat).Value
    For lig = 1 To UBound(Tab1)
      If (UCase(Tab1(lig, 1)) = UCase(clé) Or (clé = "*" And Tab1(lig, 1) <> "")) Then
        n = n + 1: If n > nlig Then cherche3D = "Pas assez de lignes!": Exit Function
        For k = 1 To ncol: b(n, k) = Tab3(lig, k): Next k
      End If
    Next lig
  Next s
  cherche3D = b
End Function

Consolidation 3D

On consolide les commandes de plusieurs onglets.

ConsoOnglets 3D
Fonction sansDoublons3D
Conso Onglets 3D Cond itionnel

Function Conso3D(début, fin, champConso)
  Application.Volatile
  nlig = Application.Caller.Rows.Count
  ncol = Application.Caller.Columns.Count
  Dim b()
  ReDim b(1 To nlig, 1 To ncol)
  n = 0
  For s = début To fin
    Set f = Sheets(s)
    tab1 = f.Range(champConso).Value
    For lig = 1 To UBound(tab1)
      If tab1(lig, 1) <> "" Then
         n = n + 1: If n > nlig Then Conso3D = "Pas assez de lignes!": Exit Function
         For k = 1 To ncol - 1: b(n, k) = tab1(lig, k): Next k
         b(n, k) = Sheets(s).Name
      End If
    Next lig
  Next s
  Conso3D = b
End Function

Fonction Somme3D 2 critères

Cette fonction perso matricielle calcule la somme de plusieurs onglets suivant 2 critères.
-Les listes des codes et des villes sont obtenues et triées automatiquement par la fonction.
-Cette fonction est rapide: grâce à Dictionary, la recherche de la ligne et de la colonne du tableau de cumul Tbl() se fait très rapidement.

-Sélectionner A1:E10
=S3DTriée(1;3; "a2:a20";"b2:b20"; "c2:c20")
-valider avec maj+ctrl+entrée

Fonction Somme3D 2 critères
Fonction Somme3D 2 critères2
Fonction Somme3D 2 critères MAC
Fonction Somme3D 1 col Num
Fonction Somme3D N col Num
Fonction Somme3D N col Num Index
Fonction Somme3D N col Num MAC
Fonction Somme3D N col Num IndexMAC

Function S3DTriée(début, fin, critLigne, CritColonne, ChampSomme)
  Application.Volatile
  Dim Tbl()
  ReDim Tbl(0 To Application.Caller.Rows.Count, 0 To Application.Caller.Columns.Count)
  Set dLig = CreateObject("Scripting.Dictionary")
  Set dCol = CreateObject("Scripting.Dictionary")
  For s = début To fin
    a = Sheets(s).Range(critLigne).Value
    b = Sheets(s).Range(CritColonne).Value
    For i = LBound(a) To UBound(a)
      If a(i, 1) <> "" Then If Not dLig.exists(a(i, 1)) Then dLig(a(i, 1)) = ""
      If b(i, 1) <> "" Then If Not dCol.exists(b(i, 1)) Then dCol(b(i, 1)) = ""
    Next i
  Next s
  crit1 = dLig.keys: Call Tri(crit1, LBound(crit1), UBound(crit1))
  dLig.RemoveAll: For i = 0 To UBound(crit1): dLig(crit1(i)) = i + 1: Next
  lig = 1: For Each c In dLig.keys: Tbl(lig, 0) = c: lig = lig + 1: Next c
  crit1 = dCol.keys: Call Tri(crit1, LBound(crit1), UBound(crit1))
  dCol.RemoveAll: For i = 0 To UBound(crit1): dCol(crit1(i)) = i + 1: Next
  k = 1: For Each c In dCol.keys: Tbl(0, k) = c: k = k + 1: Next c
  For s = début To fin
    idxLig = Sheets(s).Range(critLigne).Value
    idxCol = Sheets(s).Range(CritColonne).Value
    a = Sheets(s).Range(ChampSomme).Value
    For lig = LBound(a) To UBound(a)
      cléLig = CStr(idxLig(lig, 1)): clécol = idxCol(lig, 1)
      If cléLig <> "" And clécol <> "" Then
        ligtbl = dLig(cléLig): coltbl = dCol(clécol)
        Tbl(ligtbl, coltbl) = Tbl(ligtbl, coltbl) + a(lig, 1)
      End If
    Next lig
  Next s
  S3DTriée = Tbl
End Function

Autre version

Somme3D 2 critères

-Sélectionner A1:H12
=s3DTriée(1;5;"A2:A10";"B1:E1";"B2:E10")
-Valider avec maj+ctrl+entrée