A voir également:
- Grosse addition n'affiche pas les cellules qu'il aditionne
- Formule addition excel - Guide
- Verrouiller cellules excel - Guide
- Le clavier de mon telephone ne s'affiche plus - Guide
- Faites afficher avec un fond coloré les cellules qui contiennent une valeur comprise entre 250 et 350. quel nombre est dessiné en surbrillance ? - Forum VB / VBA
- Ce programme est écrit en python. il construit un mot secret dans une variable mais il ne l'affiche pas. modifiez-le pour qu'il affiche le mot secret. exécutez-le. quel est ce mot secret ? ✓ - Forum Python
7 réponses
Mike-31
Messages postés
18318
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
26 avril 2024
5 076
2 sept. 2016 à 06:58
2 sept. 2016 à 06:58
Re,
Exemple de formule pour la cellule H1067
=SOMME.SI.ENS(H4:H1066;D4:D1066;"Article standard")
et si tu dois utiliser le fichier sur des ordinateurs avec un Excel antérieur à la version 2007
=SOMMEPROD((D4:D1066="Article standard")*(H4:H1066))
OU SUR TOUTE LES VERSIONS
=SOMME.SI(D4:D1066;"Article standard";H4:H1066)
Et quelque soit la formule retenue si tu dois la dupliquer sans avoir à la modifier exemple pour J1067 ou K1067 etc met la colonne D en référence absolue avec des dollars $ comme ci dessous
Exemple de formule pour la cellule H1067
=SOMME.SI.ENS(H4:H1066;D4:D1066;"Article standard")
et si tu dois utiliser le fichier sur des ordinateurs avec un Excel antérieur à la version 2007
=SOMMEPROD((D4:D1066="Article standard")*(H4:H1066))
OU SUR TOUTE LES VERSIONS
=SOMME.SI(D4:D1066;"Article standard";H4:H1066)
Et quelque soit la formule retenue si tu dois la dupliquer sans avoir à la modifier exemple pour J1067 ou K1067 etc met la colonne D en référence absolue avec des dollars $ comme ci dessous
=SOMME.SI($D$4:$D$1066;"Article standard";H4:H1066)
Mike-31
Messages postés
18318
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
26 avril 2024
5 076
1 sept. 2016 à 23:07
1 sept. 2016 à 23:07
Bonsoir,
moi ce que je ne comprends pas c'est = A2+A5+A8+A11+...+A1075
pour additionner les sommes qui correspondent a des articles standards
si tu as un tableau avec dans une colonnes les articles et une colonne les valeurs à additionner tu peux suivant ta version d'Excel utiliser la syntaxe SOMMEPROD ou SOMME.SI.ENS et peut être SOMME.SI
mais pour te donner la solution il faudrait voir un bout de ton fichier anonymisé, pour cela
1) Clic sur ce lien https://www.cjoint.com/
2) Clic sur le bouton Parcourir pour sélectionner ton fichier
3) Clic sur le bouton Créer le lien en bas de la page
4) Au bout de quelques secondes en haut de la page en bleu souligné un lien sera généré, tu le sélectionnes et tu le copies dans une réponse
moi ce que je ne comprends pas c'est = A2+A5+A8+A11+...+A1075
pour additionner les sommes qui correspondent a des articles standards
si tu as un tableau avec dans une colonnes les articles et une colonne les valeurs à additionner tu peux suivant ta version d'Excel utiliser la syntaxe SOMMEPROD ou SOMME.SI.ENS et peut être SOMME.SI
mais pour te donner la solution il faudrait voir un bout de ton fichier anonymisé, pour cela
1) Clic sur ce lien https://www.cjoint.com/
2) Clic sur le bouton Parcourir pour sélectionner ton fichier
3) Clic sur le bouton Créer le lien en bas de la page
4) Au bout de quelques secondes en haut de la page en bleu souligné un lien sera généré, tu le sélectionnes et tu le copies dans une réponse
Merci pour ta reponse Mike,
Voici le fichier http://www.cjoint.com/c/FIbwnCRaoDW
Sur cet ordi a la maison les cellules ont pris des couleurs et on les distingue bien lorsqu'on doubleclick sur la somme en bas
Mais au bureau j'ai essayé sur 3 ordis different et ca ne fonctionnait pas. Qu en penses tu? Est ce que les formules que tu as proposé pourraient faire l'affaire?
Voici le fichier http://www.cjoint.com/c/FIbwnCRaoDW
Sur cet ordi a la maison les cellules ont pris des couleurs et on les distingue bien lorsqu'on doubleclick sur la somme en bas
Mais au bureau j'ai essayé sur 3 ordis different et ca ne fonctionnait pas. Qu en penses tu? Est ce que les formules que tu as proposé pourraient faire l'affaire?
Raymond PENTIER
Messages postés
58397
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
26 avril 2024
17 094
Modifié par Raymond PENTIER le 2/09/2016 à 01:26
Modifié par Raymond PENTIER le 2/09/2016 à 01:26
Bonjour à vous deux.
Mike-31 a raison : c'est une véritable hérésie d'écrire une formule de 1264 caractères comme tu as osé le faire, Georges, alors que la formule
=SOMME.SI(D4:D1066;"Article standard";M4:M1066)
est tellement plus élégante et "parlante", et n'utilise que 46 caractères ...
Du coup, tu n'as plus besoin des couleurs pour vérifier que tu n'as oublié aucune cellule ...
Et je pense que le temps de calcul par Excel est plus court.
C'est bien, la retraite ! Surtout aux Antilles ... :-)
Raymond (INSA, AFPA, CF/R)
Mike-31 a raison : c'est une véritable hérésie d'écrire une formule de 1264 caractères comme tu as osé le faire, Georges, alors que la formule
=SOMME.SI(D4:D1066;"Article standard";M4:M1066)
est tellement plus élégante et "parlante", et n'utilise que 46 caractères ...
Du coup, tu n'as plus besoin des couleurs pour vérifier que tu n'as oublié aucune cellule ...
Et je pense que le temps de calcul par Excel est plus court.
C'est bien, la retraite ! Surtout aux Antilles ... :-)
Raymond (INSA, AFPA, CF/R)
Ok merci Mrs la formule a réussie et ca facilitie vraiment.
Maintenant un autre soucis ce pose, j'ai ce meme tableau (journalier) en mensuel.
Le Mensuel consiste au collage de tous les journaliers l'un à la suite de l'autre, (ca part vraiment loin), et a la fin il y a un TOTAL ou encore une fois j'ai mis pour chaque ligne, la somme de chaque jour (manuellement).
SOMME.SI pourrait-il remplacé cette somme manuelle (en largeur cette fois)?
Je peux vous joindre un exemple du fichier si ce n'est pas assez claire.
Merci pour votre aide
Maintenant un autre soucis ce pose, j'ai ce meme tableau (journalier) en mensuel.
Le Mensuel consiste au collage de tous les journaliers l'un à la suite de l'autre, (ca part vraiment loin), et a la fin il y a un TOTAL ou encore une fois j'ai mis pour chaque ligne, la somme de chaque jour (manuellement).
SOMME.SI pourrait-il remplacé cette somme manuelle (en largeur cette fois)?
Je peux vous joindre un exemple du fichier si ce n'est pas assez claire.
Merci pour votre aide
Raymond PENTIER
Messages postés
58397
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
26 avril 2024
17 094
>
Georges89
3 sept. 2016 à 02:58
3 sept. 2016 à 02:58
Oui, ce serait vraiment plus facile avec le fichier ...
Vous trouverez le fichier mensuel sur ce lien: http://www.cjoint.com/c/FIdb1j1BXAZ
Concernant les totaux au bas du tableau j'appliquerais la formule donnée précedemment comme pour le 1er jour (en bas du tableau en rouge).
Maintenant il s'agit de la fin du tableau (à la fin à droite) vous verrez le TOTAL du mois. Je pense que ca vous ferra marrer comment je l'ai fait. Cependant j'ai essayé.
Merci de me montrer dans quelles circinstances somme.si ou une autre formule pourrait remplacer ce travail de fourmis. A ce que je sache la formule ne fonctionne que pour des colonnes et pas pour un travail en ligne..
On verra bien
Concernant les totaux au bas du tableau j'appliquerais la formule donnée précedemment comme pour le 1er jour (en bas du tableau en rouge).
Maintenant il s'agit de la fin du tableau (à la fin à droite) vous verrez le TOTAL du mois. Je pense que ca vous ferra marrer comment je l'ai fait. Cependant j'ai essayé.
Merci de me montrer dans quelles circinstances somme.si ou une autre formule pourrait remplacer ce travail de fourmis. A ce que je sache la formule ne fonctionne que pour des colonnes et pas pour un travail en ligne..
On verra bien
Raymond PENTIER
Messages postés
58397
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
26 avril 2024
17 094
3 sept. 2016 à 04:23
3 sept. 2016 à 04:23
Remplacer
=BAX4+BAZ4+BBB4+BBD4+BBF4+BBH4+BBJ4+BBL4+BBN4+BBP4+BBR4+BBT4
par
=SOMME.SI(G$3:BBU$3;"Qté";G4:BBU4)
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Qui t'a dit que la fonction ne marchait qu'en colonne ?
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Les fonctions NB.SI, SOMME.SI, MOYENNE.SI, NB.SI.ENS, SOMME.SI.ENS, MOYENNE.SI.ENS ont été conçues pour remplacer les séquences de décompte, d'addition ou de calcul de moyenne comme ce que tu as fait. Il suffit d'avoir la plage contenant les critères, de désigner le critère choisi, et d'avoir la plage contenant les valeurs à traiter (même nombre de cellules que la première plage).
=BAX4+BAZ4+BBB4+BBD4+BBF4+BBH4+BBJ4+BBL4+BBN4+BBP4+BBR4+BBT4
par
=SOMME.SI(G$3:BBU$3;"Qté";G4:BBU4)
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Qui t'a dit que la fonction ne marchait qu'en colonne ?
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Les fonctions NB.SI, SOMME.SI, MOYENNE.SI, NB.SI.ENS, SOMME.SI.ENS, MOYENNE.SI.ENS ont été conçues pour remplacer les séquences de décompte, d'addition ou de calcul de moyenne comme ce que tu as fait. Il suffit d'avoir la plage contenant les critères, de désigner le critère choisi, et d'avoir la plage contenant les valeurs à traiter (même nombre de cellules que la première plage).
Raymond PENTIER
Messages postés
58397
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
26 avril 2024
17 094
>
Georges89
3 sept. 2016 à 05:36
3 sept. 2016 à 05:36
En BAX4 ta formule =G4+AZ4+CS4+EL4+GE4+HX4+JQ4+LJ4+NC4+OV4+QO4+SH4+ UA4+VT4+XM4+ZF4+AAY4+ACR4+AEK4+AGD4+AHW4+AJP4+ALI4+ANB4+AOU4+AQN4+ASG4+ATZ4+AVS4+AXL4+AZE4
peut s'écrire plus simplement
=SOMME.SI.ENS(G4:BAU4;G2:BAU2;"LUCIEN";G3:BAU3;"Qté")
... à condition bien entendu que "LUCIEN" soit saisi en G2 au lieu de H2, en AZ2 au lieu de BA2 etc., et en faisant un alignement "Centré sur plusieurs colonnes" avec G2:I2, en AZ2:BB2, etc.
peut s'écrire plus simplement
=SOMME.SI.ENS(G4:BAU4;G2:BAU2;"LUCIEN";G3:BAU3;"Qté")
... à condition bien entendu que "LUCIEN" soit saisi en G2 au lieu de H2, en AZ2 au lieu de BA2 etc., et en faisant un alignement "Centré sur plusieurs colonnes" avec G2:I2, en AZ2:BB2, etc.
Georges89
>
Raymond PENTIER
Messages postés
58397
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
26 avril 2024
3 sept. 2016 à 17:32
3 sept. 2016 à 17:32
Merci pour cette formule également j'en avais besoin pour les totaux à la fin dans le mensuel (colonnes en gris) qui calcule le total de chaque commercial et la derniere colonne (en bleu) le total de tous la bas j'utiliserais la formule SOMME.SI.
Cependant je ne comprends pas pour cette formule =SOMME.SI.ENS(G4:BAU4;G2:BAU2;"LUCIEN";G3:BAU3;"Qté")
Pourquoi "LUCIEN" doit être saisi en G2 au lieu de H2?
Et c'est quoi un alignement "Centré sur plusieurs colonnes" avec G2:I2, en AZ2:BB2, etc. ??
Cependant je ne comprends pas pour cette formule =SOMME.SI.ENS(G4:BAU4;G2:BAU2;"LUCIEN";G3:BAU3;"Qté")
Pourquoi "LUCIEN" doit être saisi en G2 au lieu de H2?
Et c'est quoi un alignement "Centré sur plusieurs colonnes" avec G2:I2, en AZ2:BB2, etc. ??
Raymond PENTIER
Messages postés
58397
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
26 avril 2024
17 094
>
Georges89
3 sept. 2016 à 20:39
3 sept. 2016 à 20:39
"LUCIEN" est le critère qui correspond à "Qté" et doit donc se trouver dans la même colonne. Tu dois rectifier toutes les données dans G2:BAS2 en plaçant ces désignations dans la première des 3 ou 4 cellules, comme d'ailleurs tu l'as fait pour "GUY OMER".
Procédure : Tu écris "LUCIEN" en G2 ; tu sélectionnes G2:I2 ; tu fais clic-droit puis Format de cellule/Alignement du texte/Horizontal/Centrer sur plusieurs cellules
En effet on évite toujours de fusionner des cellules dans un tableau qui sert de "base de données".
Procédure : Tu écris "LUCIEN" en G2 ; tu sélectionnes G2:I2 ; tu fais clic-droit puis Format de cellule/Alignement du texte/Horizontal/Centrer sur plusieurs cellules
En effet on évite toujours de fusionner des cellules dans un tableau qui sert de "base de données".
Merci beaucoup pour votre aide, c'est vraiment intéressant excel, donc si je comprends bien, après le déplacement des noms des colonnes comme dit, Lucien qui correspond à Qté, cette formule =SOMME.SI.ENS(G4:BAU4;G2:BAU2;"LUCIEN";G3:BAU3;"Qté" calculera le total qté de lucien.
Maintenant pour calculer le Prix total (en BAY4) comment sera t il calculé? Faudra il ajouter Lucien1 par exemple au dessus de P.T de chaque colonne et le mettre en couleur blanche (là il sera invisble et ne genera pas le tableau)
Vraiment vous me faites gagner des heures et des heures là, Merci
Maintenant pour calculer le Prix total (en BAY4) comment sera t il calculé? Faudra il ajouter Lucien1 par exemple au dessus de P.T de chaque colonne et le mettre en couleur blanche (là il sera invisble et ne genera pas le tableau)
Vraiment vous me faites gagner des heures et des heures là, Merci
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
Raymond PENTIER
Messages postés
58397
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
26 avril 2024
17 094
4 sept. 2016 à 03:14
4 sept. 2016 à 03:14
... suite
Concernant la formule en BAX4, je te l'avais proposée afin d'illustrer l'utilité et le fonctionnement de la fonction SOMME.SI.ENS comme tu le demandais.
"Faudra il ajouter Lucien1 par exemple au dessus de P.T de chaque colonne et le mettre en couleur blanche" Oui, c'est presque ça ; mais il faut conserver la même orthographe "LUCIEN", puisque la formule ne totalise que les valeurs en ligne 4 pour lesquelles il y a "LUCIEN" en ligne 2 ET "Qté" en ligne 3.
Le problème est que l'intitulé recopié en I2 empêcherait l'affichage complet du texte en G2 !
Il faudra contourner la difficulté en insérant une ligne après la ligne 2 ; en I3 on mettra la formule =G2 pour ne pas risquer d'avoir des orthographes différentes (ne serait-ce qu'un espace), avec un alignement à droite.
Concernant la formule en BAX4, je te l'avais proposée afin d'illustrer l'utilité et le fonctionnement de la fonction SOMME.SI.ENS comme tu le demandais.
"Faudra il ajouter Lucien1 par exemple au dessus de P.T de chaque colonne et le mettre en couleur blanche" Oui, c'est presque ça ; mais il faut conserver la même orthographe "LUCIEN", puisque la formule ne totalise que les valeurs en ligne 4 pour lesquelles il y a "LUCIEN" en ligne 2 ET "Qté" en ligne 3.
Le problème est que l'intitulé recopié en I2 empêcherait l'affichage complet du texte en G2 !
Il faudra contourner la difficulté en insérant une ligne après la ligne 2 ; en I3 on mettra la formule =G2 pour ne pas risquer d'avoir des orthographes différentes (ne serait-ce qu'un espace), avec un alignement à droite.
ATTENTION : quand tu as 4 colonnes par intitulé (avec une colonne "Offert"), la formule ne doit pas être copiée aveuglément :...
Ainsi en Y3 ce ne sera pas =W2 mais =V2 !
Raymond PENTIER
Messages postés
58397
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
26 avril 2024
17 094
4 sept. 2016 à 05:42
4 sept. 2016 à 05:42
... suite
Les autres lignes seront décalées vers le bas.
Il faut aussi penser à mettre en BAY3 la formule =BAX2, et de même pour les colonnes suivantes.
Le premier cumul mensuel de Qté sera donc en BAX5 avec la formule
=SOMME.SI.ENS(G5:BAU5;G2:BAU2;"LUCIEN";G4:BAU4;"Qté")
et le premier cumul de P.T sera en cellule BAY5 avec la formule
=SOMME.SI.ENS(G5:BAU5;G3:BAU3;"LUCIEN";G4:BAU4;"P.T")
Mais dans les colonnes suivantes il faudrait chaque fois remplacer "LUCIEN" par "GUY OMER" et tous les autres intitulés, soit après recopie, soit après ressaisie des formules ... C'est mortel !
Excel va nous aider, si nous modifions légèrement nos formules, qui iront lire directement les noms en ligne 2 ou 3 de la colonne :
=SOMME.SI.ENS($G5:$BAU5;$G$2:$BAU$2;$2:$2;$G$4:$BAU$4;"Qté")
à recopier (sans mise en forme) jusqu'en BAX1043
=SOMME.SI.ENS($G5:$BAU5;$G$3:$BAU$3;$3:$3;$G$4:$BAU$4;"P.T")
à recopier (sans mise en forme) jusqu'en BAY1043
puis copier-coller la plage BAX5:BAY1043 en BAZ5, BBB5, BBF5 etc
Et pour terminer en BBV5 nous aurons la formule
=SOMME.SI($BAX$4:$BBU$4;"Qté";$BAX5:$BBU5)
et en BBW5 la formule
=SOMME.SI($BAX$4:$BBU$4;"P.T";$BAX5:$BBU5)
Les autres lignes seront décalées vers le bas.
Il faut aussi penser à mettre en BAY3 la formule =BAX2, et de même pour les colonnes suivantes.
Le premier cumul mensuel de Qté sera donc en BAX5 avec la formule
=SOMME.SI.ENS(G5:BAU5;G2:BAU2;"LUCIEN";G4:BAU4;"Qté")
et le premier cumul de P.T sera en cellule BAY5 avec la formule
=SOMME.SI.ENS(G5:BAU5;G3:BAU3;"LUCIEN";G4:BAU4;"P.T")
Mais dans les colonnes suivantes il faudrait chaque fois remplacer "LUCIEN" par "GUY OMER" et tous les autres intitulés, soit après recopie, soit après ressaisie des formules ... C'est mortel !
Excel va nous aider, si nous modifions légèrement nos formules, qui iront lire directement les noms en ligne 2 ou 3 de la colonne :
=SOMME.SI.ENS($G5:$BAU5;$G$2:$BAU$2;$2:$2;$G$4:$BAU$4;"Qté")
à recopier (sans mise en forme) jusqu'en BAX1043
=SOMME.SI.ENS($G5:$BAU5;$G$3:$BAU$3;$3:$3;$G$4:$BAU$4;"P.T")
à recopier (sans mise en forme) jusqu'en BAY1043
puis copier-coller la plage BAX5:BAY1043 en BAZ5, BBB5, BBF5 etc
Et pour terminer en BBV5 nous aurons la formule
=SOMME.SI($BAX$4:$BBU$4;"Qté";$BAX5:$BBU5)
et en BBW5 la formule
=SOMME.SI($BAX$4:$BBU$4;"P.T";$BAX5:$BBU5)
Raymond PENTIER
Messages postés
58397
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
26 avril 2024
17 094
4 sept. 2016 à 05:52
4 sept. 2016 à 05:52
... fin
Pour l'instant je laisse tomber la ligne 1044, en attendant de savoir comment tu gères les colonnes I, L, O etc ainsi que les lignes 5, 9, 13 etc
Pour le moment, le fichier est devenu https://www.cjoint.com/c/FIedXfJ53B4
Mais 1400 colonnes de 1000 lignes, c'est énorme ! ça fait plus de 5 Mo ...
Je me demande si un tel travail ne mériterait pas d'être fait sur Access, et non sur Excel !
Pour l'instant je laisse tomber la ligne 1044, en attendant de savoir comment tu gères les colonnes I, L, O etc ainsi que les lignes 5, 9, 13 etc
Pour le moment, le fichier est devenu https://www.cjoint.com/c/FIedXfJ53B4
Mais 1400 colonnes de 1000 lignes, c'est énorme ! ça fait plus de 5 Mo ...
Je me demande si un tel travail ne mériterait pas d'être fait sur Access, et non sur Excel !
Raymond PENTIER
Messages postés
58397
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
26 avril 2024
17 094
>
Georges89
Modifié par Raymond PENTIER le 5/09/2016 à 02:37
Modifié par Raymond PENTIER le 5/09/2016 à 02:37
La formule lit la donnée se trouvant dans la même colonne que la cellule concernée. Si en BAX5 il y a la formule
=SOMME.SI.ENS($G5:$BAU5;$G$2:$BAU$2;$2:$2;$G$4:$BAU$4;"Qté")
Excel saura que dans la ligne 2 c'est la colonne BAX qui compte, et renverra donc le contenu de la cellule BAX2, c'est-à-dire "Alain".
=SOMME.SI.ENS($G5:$BAU5;$G$2:$BAU$2;$2:$2;$G$4:$BAU$4;"Qté")
Excel saura que dans la ligne 2 c'est la colonne BAX qui compte, et renverra donc le contenu de la cellule BAX2, c'est-à-dire "Alain".
