Discussion :

[marcb03]

Bonjour,

J'ai fait pas mal de recherches, mais je bute sur un problème:

J'aurais besoin d'insérer dans une formule SOMME.SI.ENS une fonction allant rechercher sur une autre feuille du classeur une plage variable pour effectuer le calcul en fonction d'une valeur à renseigner dans une cellule.

J'ai sur la feuille 1 un tableau avec les années en colonnes, différentes catégories (A,B,C) en lignes et des montants en valeurs.
Sur la feuille 2, il faudrait que j'additionne les montants correspondants sous une même colonne "Année" et trois sous colonnes pour les catégories A, B,C.
La fonction SOMME.SI.ENS permet parfaitement de faire le tri sur les catégories qui sont inscrites en lignes sur ma première feuille, mais pas sur les années qui sont inscrites en colonnes.
En fait, c'est la partie en rouge de la formule =SOMME.SI.ENS(Feuil1!B2:B11;Feuil1!$A$2:$A$11;A$2) qu'il faudrait que je puisse rendre variable en fonction de la valeur de ma cellule A1.
Pas de tableau croisé dynamique car j'ai besoin sur la feuille 2 de mettre côte à côte plusieurs années ventilées chacune avec les sous-colonnes par catégories.

Si quelqu'un de plus calé que moi veut bien se pencher sur mon problème, je mets une pièce jointe pour une meilleure compréhension.

Merci d'avance de vos réponses.

[antonysansh]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

DECALER(Feuil1!B2;0;EQUIV(Feuil2!A1;Feuil1!A1:G1;0);NBVAL(Feuil1!B2:B65536);1)

La formule ci-dessus ira cherche la bonne colonne dans la feuille 1

Je te laisse consulter l'aide des différentes fonctions utiliser pour mieux les comprendre

Antony

[papouclo]

Bonjour,
Antony,
Une idée intéressante mais attention !
EQUIV (Feuil2!A1 ; Feuil1!A1:G1 ; 0) donne 2
et
DECALER(Feuil1!B2 ;0 ; 2 ; NBVAL(Feuil1!B2:B65536) ; 1) donne finalement la plage D2 : D11 correspondant non pas à l'année 2010 mais à 2012.

marcb03,
Le problème ennuyeux, dans ta feuille 2, est (comme trop souvent) la fusion des cellules A1:A3 . Cela oblige à trouver une certaine périodicité qui , toutes les trois colonnes,
fera utliser une même plage de la feuille 1 pour le calcul (B2:B11 pour les colonnes A,B,C ; C2:C11 pour les colonnes D,E,F ...)
Première proposition avec la fonction DECALER proposée par Antony
Appelons catégories la plage A2:A11 de la feuille 1.
La formule de calcul = ENT(COLONNE()-1/3) + 1 donne lorsqu'elle est écrite dans une cellule d'une des trois colonnes A,B,C le même résultat 1 , ce qui est le nombre de
colonnes dont il faut décaler la plage nommée catégories pour obtenir la plage B2:B11 qui nous intéresse.
La même formule écrite dans une cellule des colonnes D,E,F renvoie 2 c'est à dire le nombre de colonnes dont il faut décaler la plage nommée catégories pour obtenir
la plage C2:C11 qui correspond à l'année 2011 ...
d'où la formule finale en A3 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMME.SI(catégories;A$2;DECALER(catégories;;ENT((COLONNE()-1)/3)+1))

Recopier cette formule vers la droite jusqu'en R3.
Cette proposition est mise en oeuvre dans la feuille 2 du classeur joint.
Deuxième proposition avec la fonction SOMMEPROD
En plus du nom catégories, utilisons le nom années pour la plage B1:G1 (feuil1) et le nom tabval pour la plage B2:G11 (toujours feuil1).
Se placer en A3 et définir un nouveau nom calculannée par :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=DECALER(Feuil3!$A$1;;3*ENT((COLONNE()-1)/3))

Ecrire alors en A3 la formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD((catégories=Feuil3!A$2)*(années=calculannée)*tabval)

et recopier cette formule vers la droite jusqu'en R3.
Cette proposition est mise en oeuvre dans la feuille 3 du classeur joint.
Remarque pour cette seconde démarche :
Si quelqu'un peut m'expliquer pourquoi, alors que cette démarche en deux temps (définition du nom calculannée puis formule) donne des résultats satisfaisants, l'écriture en A3 de la formule :
=SOMMEPROD((catégories=Feuil3!A$2)*(années=DECALER(Feuil3!$A$1;;3*ENT((COLONNE()-1)/3)))*tabval)
renvoie le message d'erreur #VALEUR!, merci d'avance!
Cordialement
Claude