Discussion :

[bolide7]

Bonjour à tous,
Dans un classeur EXCEL 2010, j’ai de nombreuses feuilles identiques intitulées « Janvier 2013 », « Février 2013 », etc. avec en colonne :
- A : Numéro de facture : Exemple : "01 01 2013" : Numéro de facture dans l’année - Mois - Année
- B : Numéro de client : Exemple : "28 001" : Département - Numéro de client dans le département
- G : Montant de la facture : En euros
Serait il possible de faire un tableau récapitulatif du chiffre d’affaires par mois et par département ?
Je souhaiterai donc retrouver la feuille correspondant au mois du tableau récapitulatif puis les clients d’un même département avant d’additionner les montants des factures de tous ces clients.
Pour une meilleure compréhension, je joins un petit fichier qui ne reprend que 2 mois (Janvier 2013 et Février 2013).
Je confirme que le fichier original est bien sur EXCEL 2010 alors que le fichier joint est sur EXCEL 2003.
D’avance, merci beaucoup.
Bon après-midi.
bolide7

[Renyeu]

Bonjour,
Une proposition en pj... Fonctionnelle mais pas top
Cdlt
Renyeu

[78chris]

Bonjour

Une solution par TCD en utilisant plages avec étiquettes et les colonnes clients et CA qui doivent être côte à côte.
Groupement sur les code client par 1000 : restera à renommer ces groupes avec le numéro de département

[bolide7]

Bonjour Renyeu et 78chris,
Grand merci à vous deux pour vos très rapides réponses.
Mille excuses pour ce retard inacceptable, mais justifié.
Mon ordinateur portable ACER a lâché (une des 2 charnières de l'écran cassée puis plus d'affichage sur la moitié gauche de l'écran).
J'ai du acheter un nouvel ordinateur portable HP pour pouvoir enfin vous répondre.
Je n'ai pas encore terminé sa configuration, mais je prends le temps pour ces quelques lignes.
Comme j'aime comprendre ce que je fais, j'aurai apprécié comprendre pourquoi Renyeu me demande de multiplier la première partie de sa formule par 1 ?
Quel est la justification de cette multiplication par 1 indispensable au bon fonctionnement de sa formule ?

Par ailleurs, j'ai essayé de clarifier cette formule, qui d'après son auteur n'est pas top : Remplacement de la division par 1000 et de la multiplication par 1 par la fonction "GAUCHE" et en prenant les 2 chiffres de gauche du code client, qui correspondent au numéro du département.

=SOMMEPROD(GAUCHE(INDIRECT("'"&A7&"'!$B$6:$B$15");2)=B7;INDIRECT("'"&A7&"'!$G$6:$G$15"))

Hélas et j'ignore pourquoi cette fonction modifiée ne marche pas ?
Pourtant, cette fonction extrait le numéro du département.

D'avance, merci beaucoup pour vos futurs explications.
Bonne soirée
bolide7

[bolide7]

Bonjour à tous,
Après des dizaines d'essais, j'ai finit par trouver une formule avec la fonction GAUCHE, qui fonctionne.

Code :

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

=SOMMEPROD((GAUCHE(INDIRECT("'"&A7&"'!$B$6:$B$15");2)="28")*1;INDIRECT("'"&A7&"'!$G$6:$G$15"))

Dans cette formule, "28" est le numéro du département.

Cependant, pour faciliter le recopiage de cette formule dans des centaines de cellules, je me heurte encore à un problème insolvable : La fonction ne marche plus si je remplace le numéro du département par la cellule B7 qui contient le numéro de ce département.
Pourquoi ?
Comment faire pour remplacer dans la fonction le "28" par la cellule B7 ?
D'avance, merci beaucoup.
bolide7

[bolide7]

Bonsoir à tous,
Après des dizaines d'essais, j'ai finit par trouver une formule avec la fonction GAUCHE, qui fonctionne avec la cellule B7.
Il faut mettre la multiplication par 1 avant la référence de la cellule B7 et non après.

Code :

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

=SOMMEPROD((GAUCHE(INDIRECT("'"&A7&"'!$B$6:$B$15");2)*1=$B7)*(INDIRECT("'"&A7&"'!$G$6:$G$15")))

Dans cette formule, le numéro du département se trouve dans la cellule B7.

Cependant, je me heurte encore à un nouveau problème insolvable : La fonction ne marche pas parce que certaines cellules de ma plage sont vides.
Comment transformer cette fonction pour qu'elle ne soit plus perturber par ces cellules vides ?
D'avance, merci beaucoup.
bolide7

[bolide7]

Bonsoir à tous,

J'ai testé une formule qui devrait permettre de contourner mon problème (SOMMEPROD n'apprécie pas les cellules vides).

Code :

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

=SOMMEPROD((GAUCHE(INDIRECT("'"&A7&"'!$B$6:$B$15");2)*1=$B7)*(INDIRECT("'"&A7&"'!$G$6:$G$15"))*(INDIRECT("'"&A7&"'!$B$6:$B$15)<>""))

Hélas, j'ignore totalement pourquoi cette formule ne marche toujours pas.
Comment transformer cette fonction pour qu'elle ne soit plus perturber par ces cellules vides ?
D'avance, merci beaucoup.
Bon Noël à tous.
bolide7

[SfJ5Rpw8]

C'est la fonction "Gauche" qui n'aime pas 0 comme paramètre :
essai :

Code :

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

=SOMMEPROD((GAUCHE(CTXT(INDIRECT("'"&A7&"'!$B$6:$B$15"));2)*1=$B7)*(INDIRECT("'"&A7&"'!$G$6:$G$15")))

[bolide7]

Bonjour bbil,

Même si cela revient certainement au même, ce ne sont pas des 0 mais des cellules vides, qui sont présentes dans mes tableaux.

Comme d'habitude, ta proposition de formule est irréprochable et répond exactement aux contraintes de mes tableaux.

Au lieu de faire un simple copier / coller, j'aurai réellement apprécié comprendre cette nouvelle CTXT, que je ne connais pas. Si j'ai bien compris, GAUCHE et INDIRECT correspond à des 0 et des 1. Dans ces conditions, quel est l'intérêt de CTXT, qui va transformer ces 0 et ces 1 en des 0,00 et en des 1,00 ?

De mon côté, à force d'intenses recherches sur Internet, cette nuit le Pére Noël m'a également apporté une proposition, qui fonctionne.

Code :

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

=SOMMEPROD((GAUCHE(INDIRECT("'"&A7&"'!$B$6:$B$15");2)*1=$B7&"")*(INDIRECT("'"&A7&"'!$G$6:$G$15")))

D'après ce que j'ai vu sur Internet, cette formule permet d'avoir des chiffres, mais également du texte dans la cellule B7. De plus, elle a surtout l'avantage de ne pas être perturbé par des cellules vides.

Ne comprenant pas la totalité de ta formule, quelle est la formule la plus logique par rapport à mes tableaux ?
Quelle est la plus rapide pour EXCEL ?

Dans l'attente de ces futurs réponses, pour lesquelles d'avance je te remercie, je te souhaite un Joyeux Noël.

Encore grand merci pour avoir solutionner mon problème.

bolide7

[bolide7]

Bonjour à tous,
Je viens de m'apercevoir que la formule communiquée sur mon précédent message était fausse.
La formule exacte est :

Code :

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

=SOMMEPROD((GAUCHE(INDIRECT("'"&A7&"'!$B$6:$B$15");2)=$B7&"")*(INDIRECT("'"&A7&"'!$G$6:$G$15")))

Je tiens à m'excuser pour cette erreur de retranscription.
Bonne fin de journée à tous.
bolide7

[danixdb]

Bonjour à tous,

En créant préalablement des noms pour chaque colonnes utilisées dans la formule ci-dessous, j'arrive à retrouver ton résultat.

1 - Création des 12 noms pour la colonne CA de chaque feuille mensuelle
ex : CA_Janv
Référence à :
=DECALER('Janvier 2013'!$G$6;0;0;NBVAL('Janvier 2013'!$B:$B))

2 - Création de 12 noms pour la colonne Ville de chaque feuille mensuelle
ex et_Janv
=DECALER('Janvier 2013'!$B$6;0;0;NBVAL('Janvier 2013'!$B:$B))

3 - Mise en place de la formule suivante dansta colone de résultat (feuille CA_Mensuel_Département)
=SOMMEPROD(CHOISIR(MOIS(A7);CA_Janv;CA_Fev)*(GAUCHE(CHOISIR(MOIS($A7);Dept_Janv;Dept_Fev);2)=CTXT($B7;0;VRAI)))

à compléter avec les 10 autres noms (Mars à décembre) pour la partie CA et Dept, cela devrait te donner le résultat escompté

Slts

[bolide7]

Bonsoir danixdb,

Merci beaucoup pour ta formule, qui fonctionne très bien.
Cela en fait donc une de plus.

Pour clore cette discussion, j’aimerai comprendre pourquoi dans la formule

Code :

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

=SOMMEPROD((GAUCHE(INDIRECT("'"&A7&"'!$B$6:$B$15");2)="28")*1;INDIRECT("'"&A7&"'!$G$6:$G$15"))

qui fonctionne également, lorsque je remplace le chiffre du département par la cellule B7, qui correspond à ce même numéro de département (le "28")

Code :

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

=SOMMEPROD((GAUCHE(INDIRECT("'"&A7&"'!$B$6:$B$15");2)=B7)*1;INDIRECT("'"&A7&"'!$G$6:$G$15"))

la formule ne fonctionne plus.

D’avance, merci beaucoup pour cette précision ou plus exactement pour cet éclairage.
bolide7

[danixdb]

Bonjour Bolide7, le Forum

Je suppose que ta cellule B7 est en numérique, donc il te faudra la convertir en texte pour que la formule te donne un résultat attendu.
C'est pourquoi tu trouveras dans la première formule "28", le dépatement est entouré de guillemets et la cellule B7 est numérique d'ou l'utilisation de la fonction CTXT qui permet de convertir en texte une cellule numérique
CTXT(B7;0;vrai) donnera "28" dans ton cas et non 28

Slts

[bolide7]

Bonsoir danixdb,
Merci beaucoup pour cette précision.
Si je comprends bien la fonction GAUCHE fonctionne avec du texte et non du numérique (des nombres).
Autrement dit, si je recherchais toutes les cellules qui commencent avec les deux lettres "AB" et que dans la cellule B7 il y ait d'inscrit ces 2 lettres, on pourrait écrire la fonction de 2 façons sans qu'il ait le moindre problème :

Code :

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

=SOMMEPROD((GAUCHE(INDIRECT("'"&A7&"'!$B$6:$B$15");2)=AB)*1;INDIRECT("'"&A7&"'!$G$6:$G$15"))

Code :

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

=SOMMEPROD((GAUCHE(INDIRECT("'"&A7&"'!$B$6:$B$15");2)=B7)*1;INDIRECT("'"&A7&"'!$G$6:$G$15"))

Bonne nuit.
bolide7