Créer une somme en fonction de critères dans une autre colonne

Bonsoir OHMIK,

Tes explications sont succinctes et ton classeur pas assez clair pour pouvoir te conseiller convenablement.

Exemple : il y a manifestement 2 personnes dont tu veux suivre les dépenses mais tu ne dis pas si les totaux vont concerner une ou deux personnes.  Il faudrait que tu décrives mieux le résultat auquel tu veux parvenir.

Je me suis donc contenté de répondre strictement à ta question et j'ai introduit la formule qui permet de sommer par lettre le contenu de la base de données dans la plage A26:D140 où je n'ai pas non plus compris la différence entre la colonne A et B...

Comme cela tu peux utiliser la formule de somme par lettre.

Une autre façon serait de travailler à l'aide des Tableaux croisés dynamiques qui feraient le travail à ta place. 

Par exemple une seule base de données contenant la date de la dépenses et le code et tu peux faire un TCD qui te présentera par mois et par lettre. 

https://www.cjoint.com/c/NHvxVPqWEKo

Si tu veux aller plus loin : explique plus clairement ce que tu veux réaliser.

A bientôt

Chris

voir post ci-dessous.

Bonjour LM et Chris,

lien fichier : https://www.cjoint.com/c/NHwpl07YMH0

---------------------------------------------------------------------------------------------

pour chaque feuille mensuelle :

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Ctg = abréviation de "catégorie" (pour le code catégorie)

le fond gris clair d'une ligne "Epargne" est mis par MFC
(colonnes B à F ou colonnes K à O, selon que c'est pour
Juliette ou Lucas).

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

* en C22 : =SOMME(C2:C21)
* en D22 : =SOMME(D2:D21)
* en F22 : =SOMME(F2:F21)

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

* en L22 : =SOMME(L2:L21)
* en M22 : =SOMME(M2:M21)
* en O22 : =SOMME(O2:O21)

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

* en i4 : =C22-D22-F22
* en i5 : =L22-M22-O22
* en i6 : =i4+i5

* en i9 : =D22+M22

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

ATTENTION : pour TOUT ce qui est en ligne 25 et DESSOUS :
il n'y a RIEN à SAISIR ; c'est SEULEMENT À LIRE, IL NE FAUT
RIEN CHANGER !

* pour Juliette : c'est la liste des dépenses de D2:D21 qui ont
un Ctg en E2:E21 (une seule ligne par catégorie de dépense).

* pour Lucas : c'est la liste des dépenses de M2:M21 qui ont
un Ctg en N2:N21 (une seule ligne par catégorie de dépense).

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

bien sûr, c'est l'utilisateur qui doit saisir un code Ctg à droite
du montant de la dépense (en colonne E ou N).

je n'ai pas mis de liste déroulante car c'est plus facile de taper
"C" que de le choisir dans une longue liste où il faut faire un
défilement pour le trouver ; et comme "C" est l'initiale du mot
"Cadeaux", c'est facile de s'en rappeler (peut-être pas au début,
mais ça viendra vite avec la pratique) ; idem pour les dépenses
régulières et obligatoires comme le "Loyer" ➯ Ctg = "L".

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Mnt = abréviation de "Montant"

le "Mnt" de C25 est à la fois pour les nombres situés dessous
ET pour D25 ; le "Mnt" de L25 est à la fois pour les nombres
situés dessous ET pour M25.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

nom défini Ctg : Fait référence à : =Catégories!$A$2:$B$27

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

* en A25 : =UNIQUE(E2:E21)

* en B26 : =SI(A26="";"";RECHERCHEV(A26;Ctg;2;0))
* en C26 : =SI(A26="";0;SOMME.SI(E$2:E$21;A26;D$2:D$21))
(ces 2 formules ont été recopiées vers le bas jusqu'en ligne 45)

* en D25 : =SOMME(C26:C45)

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

* en J25 : =UNIQUE(N2:N21)

* en K26 : =SI(J26="";"";RECHERCHEV(J26;Ctg;2;0))
* en L26 : =SI(J26="";0;SOMME.SI(N$2:N$21;J26;M$2:M$21))
(ces 2 formules ont été recopiées vers le bas jusqu'en ligne 45)

* en M25 : =SOMME(L26:L45)

---------------------------------------------------------------------------------------------

pour la feuille "Sep" seulement :

en A26 et dessous, les couleurs du fond sont mises par MFC ;
idem en J26 et dessous.

---------------------------------------------------------------------------------------------

pour la feuille "TOTAUX" :

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

* en B2 : =SOMME(Sep:Août!C22)
* en C2 : =SOMME(Sep:Août!D22)
* en D2 : =SOMME(Sep:Août!F22)

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

* en B6 : =SOMME(Sep:Août!L22)
* en C6 : =SOMME(Sep:Août!M22)
* en D6 : =SOMME(Sep:Août!O22)

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

* en O2 : =SOMME(Sep:Août!i9)

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

* en K5 : =Catégories!A2
* en L5 : =Catégories!B2
(ces 2 formules ont été recopiées vers le bas jusqu'en ligne 24)

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

* en M5 :

=SOMME.SI(Sep!A$26:A$45;K5;Sep!C$26:C$45)
  +SOMME.SI(Oct!A$26:A$45;K5;Oct!C$26:C$45)
  +SOMME.SI(Nov!A$26:A$45;K5;Nov!C$26:C$45)
  +SOMME.SI(Déc!A$26:A$45;K5;Déc!C$26:C$45)
  +SOMME.SI(Jan!A$26:A$45;K5;Jan!C$26:C$45)
  +SOMME.SI(Fév!A$26:A$45;K5;Fév!C$26:C$45)
  +SOMME.SI(Mars!A$26:A$45;K5;Mars!C$26:C$45)
  +SOMME.SI(Avr!A$26:A$45;K5;Avr!C$26:C$45)
  +SOMME.SI(Mai!A$26:A$45;K5;Mai!C$26:C$45)
  +SOMME.SI(Juin!A$26:A$45;K5;Juin!C$26:C$45)
  +SOMME.SI(Juil!A$26:A$45;K5;Juil!C$26:C$45)
  +SOMME.SI(Août!A$26:A$45;K5;Août!C$26:C$45)

* en N5 :

=SOMME.SI(Sep!J$26:J$45;K5;Sep!L$26:L$45)
  +SOMME.SI(Oct!J$26:J$45;K5;Oct!L$26:L$45)
  +SOMME.SI(Nov!J$26:J$45;K5;Nov!L$26:L$45)
  +SOMME.SI(Déc!J$26:J$45;K5;Déc!L$26:L$45)
  +SOMME.SI(Jan!J$26:J$45;K5;Jan!L$26:L$45)
  +SOMME.SI(Fév!J$26:J$45;K5;Fév!L$26:L$45)
  +SOMME.SI(Mars!J$26:J$45;K5;Mars!L$26:L$45)
  +SOMME.SI(Avr!J$26:J$45;K5;Avr!L$26:L$45)
  +SOMME.SI(Mai!J$26:J$45;K5;Mai!L$26:L$45)
  +SOMME.SI(Juin!J$26:J$45;K5;Juin!L$26:L$45)
  +SOMME.SI(Juil!J$26:J$45;K5;Juil!L$26:L$45)
  +SOMME.SI(Août!J$26:J$45;K5;Août!L$26:L$45)

* en O5 : =M5+N5

(ces 3 formules ont été recopiées vers le bas jusqu'en ligne 24)

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

* en M25 : =SOMME(M5:M24)
* en N25 : =SOMME(N5:N24)

* en O25 : =M25+N25
bien sûr, la valeur de O25 est la même que celle de O2,
même si la formule est différente.  ;)

---------------------------------------------------------------------------------------------

pour la feuille "Catégories" :

plutôt que des codes "A" à "Z", c'est bien mieux d'utiliser des
codes explicites (qui seront faciles à retenir et à utiliser) :

* L pour Loyer ; E pour EDF
* O pour Eau (comme le E est déjà pris pour EDF,
   j'ai choisi O car phonétiquement, c'est l'eau)

* F pour Free (abonnement internet seulement
   OU : abonnement internet et téléphone)

* T pour Téléphone (au cas où tu aurais un abonnement
   téléphonique chez un autre opérateur que Free)

* A pour Alimentation (c'est mieux que N pour Nourriture,
   car ainsi, c'est valable aussi pour les boissons)

* V pour Vêtements ; H pour Hygiène & soins
* Ac pour Accessoires ; P pour Papeterie
* Es pour Essence ; Hô pour Hôtel

* C pour Cadeaux (tous les cadeaux, dont ceux de Noël) ;
   pour l'intitulé "Max", j'ai supposé que c'est comme
   "Cadeau Max" (c'est pour ça que j'ai mis la dépense
   en catégorie "C" = Cadeaux).

* M pour Meubles & déco ; Pc pour des ordinateurs
* Im pour Imprimante ; Am pour Appareils ménagers
   (lave-linge ou aspirateur, par exemple)

* Tp pour Travaux de plomberie
* Té pour Travaux d'électricité

* D pour Divers (pour toute dépense qui ne fait pas
   partie des autres catégories)

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

bien sûr, tu peux ajouter d'autres catégories ;
par exemple "Sports" et "Loisirs".

rhodo

Bonjour Rhodo et peut-être LM...

Je te félicite Rhodo car tu as eu la patience d'interpréter la demande de LM.

Pour répondre à tes demandes, je me suis d'abord concentré sur les feuilles individuelles et je les ai un peu améliorées (tri alpha et totaux)

Puis je me suis attaqué aux totaux... et je n'ai pas trouvé de solution miracle... puis je me suis demandé pourquoi s'encombrer avec toutes ces formules alors que le nombre de choix était relativement petit : nous pouvons très sincèrement limité les sortes de dépenses aux 26 lettres de l'alphabet et donc de faire des tableaux de synthèse qui reprennent TOUTES les lettres.

Dès lors, tout devient beaucoup plus simple et nous pouvons utiliser les formules de consolidation pour effectuer les totaux.

Je me suis aussi permis de ne plus mettre les couleurs car je trouve qu'elles alourdissent plus qu'elles ne facilitent la lecture (mais cela c'est une question de goût...)

Donc voici une nouvelle proposition :

https://www.cjoint.com/c/NHAjZbgpqRo

Dis moi ce que tu en penses

Reste à savoir si LM nous fera le plaisir de se manifester à nouveau...

A bientôt

Chris

Bonjour Chris,

j'ai regardé ton nouveau fichier ; c'est très bien aussi.  :)

------------------------------------------------------------------------------------------------------

1) à propos des couleurs

tu as eu raison de ne pas les mettre ; moi aussi, je trouve que ça alourdit
la présentation ; je les avait mises uniquement pour faire plaisir à LM car
il avait l'air d'y tenir ; mais comme c'est long à mettre toutes les règles de
MFC pour la seule feuille "Sep", je ne l'avais pas fait pour les autres
feuilles mensuelles ; en feuille "Totaux", j'avais mis des couleurs en
K5:L24 seulement car ce n'est pas par MFC.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

pour la couleur de fond de T25, tu as mis du vert clair, mais c'est la couleur
qui est pour Juliette : le vert clair de A1:F1 ; c'est du brun clair pour Lucas :
J1:O1 ; comme T25 est pour les 2, ça aurait été mieux du blanc sur violet,
comme en i6.  ;)

------------------------------------------------------------------------------------------------------

2) notes pour la feuille "Sep" :

pour les données de base, j'ai vu que tu as ajouté le Téléphone : 100 pour
Juliette et 150 pour Lucas ; tu lui as fait dépenser plus que ce qu'il a gagné :
686,15 > 582 ➯ son compte bancaire est passé dans le rouge, et il devra
payer des frais de découvert sur 104,15 € à un taux d'au moins 15 %.  ;)

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

pour la plage de 26 cellules C26:C51 : il y a 7 nombres et 19 vides ; c'est
pour éviter tous ces vides que j'avais choisi d'utiliser UNIQUE() et de
mettre plusieurs formules en conséquence ; mais comme ce n'est pas
possible d'utiliser UNIQUE() à partir de 2 plages différentes, ce que tu
as fait est la meilleure solution pour indiquer la somme des dépenses
pour à la fois Juliette et Lucas (l'autre solution étant d'utiliser VBA).

d'un autre côté, comme tu l'as écrit, si on se limite à 26 catégories (une
pour chaque lettre de l'alphabet), ça ne fait quand même pas trop de
vides (relativement parlant, car par exemple, si y'aurait que 3 nombres,
ça ferait tout de même 23 vides, ce qui est beaucoup, mais moins que
s'il y avait 100 ou 200 catégories).

------------------------------------------------------------------------------------------------------

3) notes pour la feuille "Jan" :

en B25, il faut =A1 au lieu de =A2 ; en K25, il faut =J1 au lieu de =J2.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

la structure de la feuille "Jan" n'est pas totalement identique à celle des
autres feuilles : sous la ligne 22 des TOTAUX, il y a 2 lignes vides au
lieu d'une seule ➯ il faut supprimer la ligne 24 pour que les 3 tableaux
en dessous soient à partir de la ligne 24 (c'est important si on doit
faire quelque chose sur un groupe de plusieurs feuilles).

------------------------------------------------------------------------------------------------------

4) note pour la feuille "Catégories" :

pour toutes les catégories (colonne B) dont l'initiale est la lettre du code
catégorie (colonne A "Ctg") : c'est très facile à retenir et à utiliser ; mais
pour les autres, c'est bien moins évident : il n'y a pas de "B" dans
"Accessoires" ; pas de "i" dans "Essence" ; pas de "J" dans "Hôtel" ;
pas de "K" dans "Ordinateur" ; pas de "N" dans "Travaux électricité" ;
pas de "Q" dans "Travaux plomberie" ... mais y'a quand même un
"G" dans "Appareils ménagers".  ;)

------------------------------------------------------------------------------------------------------

LM n'est toujours pas revenu sur son sujet, ni sur ce forum, ni sur
Excel-Pratique : voir ce lien ; donc il n'a pas répondu non plus à
fanfan38 ; ça fait maintenant 5 jours sans réponse(s), mais peut-
être que LM est débordé par des affaires personnelles, ou que
ça lui prend beaucoup de temps pour voir la solution de fanfan
+ la 1ère solution de Chris + ma propre solution ; puis y'aura
la 2ème solution de Chris.  ;)

@LM : il faut éviter de faire du crossposting car les contributeurs
n'aiment pas passer du temps pour rien sur un exercice quand le
demandeur ne revient pas sur son sujet car il a eu une réponse
ailleurs !  :(  et là, carrément, tu ne réponds à personne, alors
que comme le dit si bien fanfan38 dans sa signature :
« Bonjour, la moindre des politesses est de répondre... » ;
j'espère quand même que si tu ne réponds pas, ce n'est
pas à cause d'ennuis de santé.

rhodo

Bonsoir Rhodo,

Je vois que tu as analysé à fond ma proposition.

A propos de ta remarque concernant le choix des codes... effectivement il n'y a pas de lien entre les codes et les libellés et je pense qu'il n'en faut pas (n'oublions pas que c'est LM qui doit construire les libellés et donc on ne sait pas du tout où l'on va).  

Par contre, je pense que, dans le contexte de ma proposition (afficher tous les codes), ce serait bien d'inverser la recherche des libellés de frais dans la saisie des frais en faisant une liste déroulante sur les libellés à la place des codes et trouver les codes par recherches automatiques.

Cela rendrait la saisie plus simple.  Si LM se manifeste, je veux bien en mettre en place une qui permet une saisie partielle avec recherche rapide.

Enfin, on s'est quand même bien amusé avec ce cas...

A bientôt

Chris