Formule de calcul pour quantité et ca par mois et par ville
Bonjour à Toutes et Tous,
Je suis à la recherche d'une formule qui me permettrait de calculer une quantité et un CA par mois ET par ville. Donc plusieurs critères et je n'y arrive pas.
Ex :
Feuille "CA" (par rapport à la feuille "Contrats")
Nombre de contrat conclus en mai 2018 sur Nantes (J3)
CA HT total effectué durant le mois de mai 2018 sur Nantes (K3)
Pour mes prévisions :
Feuille "CA"
Nombre de contrat prévu sur février 2019 sur Paris (D12)
CA HT total prévu durant le mois de février 2019 sur Paris (E12)
Cela me ferait gagner beaucoup de temps.
Un grand merci d'avance pour votre aide.
Bonjour,
Excel est planté sur mon PC, mais votre demande est possible à partir de formules NB.SI.ENS() pour le nombre de contrats et SOMME.SI.ENS() pour le CA total.
Les conditions à mettre sont :
- -> Ville
- -> Date de début supérieure ou égale au dernier jour du mois (transformer les en-têtes "janvier", "fevrier", "..." en date type 01/01/2018 affiché au format "mmmm")
- -> Date de fin supérieure ou égale au premier jour du mois
bonjour
sans aucun VBA ni aucune formule (car tu as une excellente base de données bien présentée
fait en 3 minutes
peut gérer des milliers de lignes, durant 20 ans.
amitiés
Bonjour jpbt84
Merci beaucoup de ton aide. Je suis désolé mais j'ai l'impression de ton fichier est vide.
Bonjour jmd,
Merci beaucoup de ton retour.
Il n'y a pas de formule, c'est normal ? Sais-tu comment je pourrais avoir le nombre e contrat par ville et par mois stp ?
Merci d'avance.
Bonjour,
Excel est planté sur mon PC, mais votre demande est possible à partir de formules NB.SI.ENS() pour le nombre de contrats et SOMME.SI.ENS() pour le CA total.
Les conditions à mettre sont :
- -> Ville
- -> Date de début supérieure ou égale au dernier jour du mois (transformer les en-têtes "janvier", "fevrier", "..." en date type 01/01/2018 affiché au format "mmmm")
- -> Date de fin supérieure ou égale au premier jour du mois
Bonjour Pedro 22,
Voici une copie écran de mon fichier.
Un grand merci pour ton aide.
J'ai pu ouvrir ton fichier, et j'espérais que tu tentes pas toi-même quelque chose à partir des éléments que je t'ai fourni ! Si c'est tout cuit tu ne progresseras pas !
Ma proposition :
Ma proposition :
Test.xlsx
Bonjour Pedro 22,
J'avais réussi à trouver la formule suivante : =NB.SI.ENS(Contrats!A:A;"Paris";Contrats!F:F;"=9") pour la quantité par mois et par ville mais pas top, j'ai l'impression.
Pourrais-tu m'expliquer les 2 formules stp car je ne comprends rien. Ca m'aiderait pour la suite.
Un grand merci à toi d'avance.
Je n'ai pas utilisé ta colonne avec le n° du mois, mais directement la date de début.
Plutôt que d'écrire manuellement les valeurs à chercher, comme dans ton exemple : =NB.SI.ENS(Contrats!A:A;"Paris";Contrats!F:F;"=9"), je fais référence à la première colonne de ton tableau de synthèse pour la ville et à la ligne d'en-tête pour le mois.
J'ai d'ailleurs modifié cette ligne pour remplacer le texte ("Janvier") par une date ("01/01/2018") mais avec une formule un peu plus élaborée pour que l'année change automatiquement en fonction de la valeur en haut à gauche de chaque tableau et que chaque date se base sur la précédente + 1 mois (si la première date n'est pas janvier, tu auras quand même un tableau sur 12 mois glissants).
PS : j'écrirais plutôt ta formule =NB.SI.ENS(Contrats!A:A;"Paris";Contrats!F:F;"=9") comme ceci =NB.SI.ENS(Contrats!$A:$A;"Paris";Contrats!$F:$F;9)
Merci beaucoup pour tes explications très claires. J'arrive à peu près à comprendre.
Juste
Dernière petite question stp, comment puis-je additionner les données de 2 feuilles distinctes ?
Ex : Janvier sur Nantes = Addition des quantités et CA par ville entre feuille "Contrats" et feuilles "contrats archivés"
Suis vraiment désolé de toutes ces questions mais j'apprends.
Un grand merci d'avance de ton retour
Je n'ai pas ouvert ton fichier, mais il suffit d'additionner 2 formules NB.SI.ENS() ou SOMME.SI.ENS(), chacune faisant référence à l'une des 2 feuilles.
Exemple : =NB.SI.ENS(Contrats!$A:$A;"Paris";Contrats!$F:$F;9)+NB.SI.ENS('Contrats archivés'!$A:$A;"Paris";'Contrats archivés'!$F:$F;9)
Je n'ai pas ouvert ton fichier, mais il suffit d'additionner 2 formules NB.SI.ENS() ou SOMME.SI.ENS(), chacune faisant référence à l'une des 2 feuilles.
Exemple :
=NB.SI.ENS(Contrats!$A:$A;"Paris";Contrats!$F:$F;9)+NB.SI.ENS('Contrats archivés'!$A:$A;"Paris";'Contrats archivés'!$F:$F;9)
D'accord, je vais essayer. Je reviens vers toi dès que j'ai terminé
Un grand merci à toi pour tout, vraiment.
Je n'ai pas ouvert ton fichier, mais il suffit d'additionner 2 formules NB.SI.ENS() ou SOMME.SI.ENS(), chacune faisant référence à l'une des 2 feuilles.
Exemple :
=NB.SI.ENS(Contrats!$A:$A;"Paris";Contrats!$F:$F;9)+NB.SI.ENS('Contrats archivés'!$A:$A;"Paris";'Contrats archivés'!$F:$F;9)
Tout fonctionne. Un grand merci à toi.
Bonjour,
Donc Pedro utilise 2 formules, 1 pour le calcul de la quantité et 1 pour la somme du CA HT
Concernant la première formule (Prise en B3)
=NB.SI.ENS(Contrats!$A:$A;CA!$A3;Contrats!$B:$B;"<="&DATE($A$1;MOIS(B$1)+1;1);Contrats!$B:$B;">="&CA!B$1)
La fonction NB.SI.ENS() va permettre de compter le nombre de cellule d’une plage de données répondant à plusieurs critères, ces critères fonctionnent par paire, 1 paramètre contenant les données et le paramètres suivant contenant le critère, comme il s’agit de la fonction .ENS il est possible d’ajouter des paires de paramètres, a noter que ces paires de paramètres s’additionnent, il faut que TOUTES les conditions répondent aux critères pour compter +1.
- Donc Pedro met la première paire de paramètre suivant :
Données 1 : Contrats!$A:$A -> Le nom des villes dans la base de données de la feuille Contrats
Critères 1 : CA!$A3 -> Le nom de la ville dans le tableau de synthèse. Attention pas de $ devant le chiffre représentant la ligne pour que lors de la copie, la référence s’adapte. - Ensuite, Pedro indique une seconde paire de paramètres
Données 2 : Contrats!$B:$B -> La date de début dans la base de données des contrats
Critères 2 : "<="&DATE($A$1;MOIS(B$1)+1;1) -> Le critère détermine qu’il faut que la date de début soit inférieur ou égale à la date composée de l’année en A1, du mois de la première ligne plus 1, par exemple pour Janvier, il faut que la date de début soit inférieur ou égale à Février et pour le jour, Pedro définie le premier jour du mois. Donc pour Janvier 2018, Pedro test que la date de début soit inférieure ou égale au 1er février 2018 (Pour ma part, je ferais un strictement inférieur) - Enfin, Pedro propose une troisième paire de paramètres
Données 3 : Contrats!$B:$B -> La date de début dans la base de données des contrats
Critères 3 : ">="&CA!B$1 -> Ce critère test que la date de début est bien supérieur ou égale à la date contenu dans la première ligne, donc ici, il faut que la date de début soit supérieur ou égale au premier janvier 2018.
En conclusion pour cette formule :
SI, la ville de NANTES correspond à la ville contenue dans la colonne A de la base de données ET que la date de début est inférieure ou égale au 1er février 2018 ET que la date de début est supérieure ou égale au 1er janvier 2018, ce sera +1.
Pour la seconde formule de calcul du CA, c’est le même principe, mais le premier paramètre de cette formule doit contenir la plage contenant les données à additionner.
Donc pour Janvier 2018, Pedro test que la date de début soit inférieure ou égale au 1er février 2018 (Pour ma part, je ferais un strictement inférieur)
Salut @jpbt84
Merci pour les précisions, voilà qui devrait servir à toute personne qui n'est pas très familière avec les formules ".SI.ENS" !
Effectivement le "=" peut disparaître de la formule, j'ai dû l'oublier en faisant un copier coller de la condition précédente...