Formule = SI multiple ou SOMMEPROD
Bonjour à tous,
Il me semble avoir vu ici l'utilisation de SOMMEPROD avec des conditions.
L'aide d'Excel ne reprend que la possibilité de somme et je n'ai pas trouvé d'aide en ce sens.
Ma question est de savoir si je peux remplacer 4 ou 5 conditions voire plus par une formule unique SOMMEPROD ?
Je joins un tableur qui explicite cette demande.
Cordialement
Tonthon
Bonjour Elconqueror
Désolé, je me suis sans doute mal exprimé.
En fait, je souhaiterais savoir si l'on peut avoir une formule unique
dans les colonnes F4 G4 H4 I4 J4 K4
puis dans les lignes 5, 6, 7 etc
qui regrouperait les 4 SI mentionnés + par défaut "rien"
Dans je dis "unique", je pense que nous nous comprenons c'est à dire la même structure incluant la variante par rapport copro1, copro2 etc
Cordialement
Tonthon
Bonsoir
avec une petite formule simple en F5 qui s'incrémente sur tout le tableau
=CHOISIR((($D5<>"")*1+($E5<>"")*2)+1;"";CHOISIR(EQUIV($B5;{"fds";"copro1";"copro2";"copro3";"autres"};0);SI($D5*F$2/1000=0;"";$D5*F$2/1000);SI(F$3=$B5;$D5;"");SI(F$3=$B5;B5;"");SI(F$3=$B5;$D5;"");"");CHOISIR(EQUIV($B5;{"copro1";"copro2";"copro3";"autres"};0);SI(DECALER(F$3;;-1)=$B5;$E5;"");SI(DECALER(F$3;;-1)=$B5;$E5;"");SI(DECALER(F$3;;-1)=$B5;$E5;"");""))Cordialement
Bonsoir Amadéus
Que dire ? Bravo simplement génial .
(Confidentiellement, j'y arrivais via un tas de colonnes intermédaires avec des si à ne pas en finir, à donner le vertige)
Pourrais-tu me "décortiquer" un peu "ta petite formule" pour que je tente de la comprendre pour de la reproduire, d'une manière schématique ?
Cordialement
Tonthon
Bonsoir
=CHOISIR((($D5<>"")*1+($E5<>"")*2)+1;"";CHOISIR(EQUIV($B5;{"fds";"copro1";"copro2";"copro3";"autres"};0);SI($D5*F$2/1000=0;"";$D5*F$2/1000);SI(F$3=$B5;$D5;"");SI(F$3=$B5;B5;"");SI(F$3=$B5;$D5;"");"");CHOISIR(EQUIV($B5;{"copro1";"copro2";"copro3";"autres"};0);SI(DECALER(F$3;;-1)=$B5;$E5;"");SI(DECALER(F$3;;-1)=$B5;$E5;"");SI(DECALER(F$3;;-1)=$B5;$E5;"");""))Aide Excel
valeur1, valeur2, ... sont de 1 à 29 arguments de valeur parmi lesquels CHOISIR sélectionne une valeur ou une action à exécuter en fonction de l'argument no_index spécifié. Ces arguments peuvent être des nombres, des références de cellule, des noms définis, des formules, des fonctions ou du texte.
La formule est constituée en 3 parties
CHOISIR(no_index;valeur1;valeur2;…)
Le No_index est représenté par
CHOISIR((($D5<>"")*1+($E5<>"")*2)+1 (Les débits sont col D, les crédits Col E)
Si nous avons un débit en D5, ($D5<>"") renvoie VRAI >>VRAI*1 renvoie 1
Dans ce cas $E5 est vide donc ($E5<>"") renvoie FAUX puisque $E5="" >> FAUX*1 renvoie 0
L'addittion des deux donne
($D5<>"")*1+($E5<>"")*2)=1+0=1
et
($D5<>"")*1+($E5<>"")*2)+1=1+1=2
La Fonction CHOISIR utilisera donc le 2éme argument soit la Valeur2
Dans le cas d'un Crédit,
($D5<>"")*1+($E5<>"")*2)+1=0+2+1=donnera 3 (0+2+1) donc 3éme argument ou valeur3
et si les colonnes Débit et Crédit sont toutes deux vides
($D5<>"")*1+($E5<>"")*2)+1=0+0+1=1 donc valeur1
Dans la formule totale
Valeur1 est "" (Rien)
valeur2 est
CHOISIR(EQUIV($B5;{"fds";"copro1";"copro2";"copro3";"autres"};0);SI($D5*F$2/1000=0;"";$D5*F$2/1000);SI(F$3=$B5;$D5;"");SI(F$3=$B5;B5;"");SI(F$3=$B5;$D5;"");"")valeur3 est
CHOISIR(EQUIV($B5;{"copro1";"copro2";"copro3";"autres"};0);SI(DECALER(F$3;;-1)=$B5;$E5;"");SI(DECALER(F$3;;-1)=$B5;$E5;"");SI(DECALER(F$3;;-1)=$B5;$E5;"")Dans valeur2, le No index est donné par
EQUIV($B5;{"fds";"copro1";"copro2";"copro3";"autres"};0) qui renvoie 1 pour fds, 2 pour copro1, etc
et donc à l'argument ou valeur correspondant.
Les arguments ou valeurs reprennent les conditions SI initiales par colonne.
Si tu veux un éclaircissement supplémentaire, fais signe.
Cordialement
La richesse d'Excel fait qu'il y a souvent plusieurs solutions à des problèmes pas trop compliqués.
J'admets que j'ai un peu triché par rapport à la solution d'Amadéus car j'ai inséré un ligne supplémentaire et il faut désactiver l'affichage des valeurs zéro pour avoir exactement le même résultat.
Ma solution repose en partie sur une formule matricielle à valider par la combinaison magique habituelle.
A+
Bonjour,
Pour Amadéus
Merci pour ce développement : mes neurones vont être occupés un certain temps mais je pense y arriver
et reviendrais vers toi si besoin est.
Pour Lomanic
Merci pour cette variante qui repose sur une fonction qui m'est plus familière mais dont je ne maitrise pas la combinaison magique habituelle... tu peux m'en dire un peu plus ?
Cordialement
Tonthon
Il s'agit de la combinaison de touches "Shift+Ctrl+Entrée" entrées simultanément pour faire apparaître les crochets entourant la formule. D'accord, "magique" est peut-être excessif, mais elle m'a permis de résoudre bon nombre de problèmes, d'autant plus que la fonction "SOMMEPROD()" n'existait pas dans les premières versions d'Excel.
A+
Bonjour,
Pour Lomanic
Je ne connaissais pas cette formule magique, c'est "enregistré"
Je vais me pencher un peu plus tard sur ta solution et reviendrai vers toi le cas échéant
Pour Amadéus
J'ai repris tes formules en les décomposant dans des formules de base.... cf tableur joint, ce qui m'a permis de comprendre
- n°_index au niveau des cellules Débit et Crédit (c'est une formule de base qui me sera très utile à l'avenir)
- choisir au niveau choix 2
- par contre 'je patauge" au niveau choix 3
J'ai mis en rouge avec un ? mes questions.
Pour la mise en forme, tu utilises la formule magique ?
Est-ce qu'il y a une méthode pour l'élaboration d'une telle formule ou chaque cas est un cas particulier ?
Cordialement
Tonthon
Bonjour
J'ai essayé de détailler les explications dans le fichier
J'aime bien ceux, qui comme toi essaient d'apprendre et de comprendre...J'ai l'impression de servir à autre chose qu'à refaire le travail à chaque fois...
N'hésite pas si un point ta échappé.
Cordialement
Bonjour Amadéus
Petite question qui n'a rien à voir avec le sujet, pourquoi Amadéus ? Fan de Mozart ?
"L'élève que je suis" a bossé sur tes explications -mille merci encore- en passant pas mal de temps.
J'ai repris le tout à ma façon et je m'aperçois que je suis hyper exigeant
Il y a un autre point où ma logique est froissée (c'est affreux d'être trop cartésien)
Ceci dit, prends cela pour un joute, un jeu. cf tableur joint
En tout cas, j'ai compris et cela n'est pas pour te faire plaisir mais bien réel.
Je me demande s'il ne faut pas changer le titre car il me semble que cette discussion peut être profitable à beaucoup.
Cordialement
Tonthon
Bonjour
La réponse à tes remarques dans le fichier joint
Bonne journée
Bonjour Amadéus,
J'ai pris connaissance de tes réponses à mes remarques, je m'incline sur la justesse de ceux-ci.
A propos de la cellule fusionnée, j'ai tendance à oublier que seule la première cellule est remplie....
Cela m'ennuierait de savoir que mes remarques auraient pu te froisser, rassures-moi qu'il en n'est rien.
A propos du pourquoi Amadéus
Bien cordialement
Tonthon
Bonjour
Cela m'ennuierait de savoir que mes remarques auraient pu te froisser, rassures-moi qu'il en n'est rien.
A propos du pourquoi Amadéus Pas de réponse ?
Je n'ai pas trouvé de motif pour être froissé dans aucune de tes remarques. La curiosité lorsqu'on veut apprendre est plus qu'honorable.
Pour Amadéus, je n'en sais trop rien. Sans doute qu'il me fallait un pseudo, et comme je me sentais d'un niveau très moyen, j'ai probablement voulu me tourner moi-même en dérision..
(Mon ego n'était pas le motif)
Cordialement