Discussion :

[Arkham46]

Bonjour,

Défi intéressant...
J'en tenté une solution qui fonctionne même avec des choix de plusieurs caractères.
J'ai ajouté un pipe (|) pour séparer les éléments à la concaténation, histoire de réduire les possibilités que la concaténation d'éléments différents soit identique.
Genre :
ab + cd + ef => abcdef
a + bcde + f => abcdef également
En ajoutant un séparateur, il reste peu de risque.



Formule E2 : Compte le nombre choix <= au choix 1

Code :

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

=NB.SI($A2:$C2;"<="&A2)

Formule F2 : Compte le nombre choix <= au choix 2

Code :

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

=NB.SI($A2:$C2;"<="&B2)

Formule G2 : Compte le nombre choix <= au choix 3

Code :

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

=NB.SI($A2:$C2;"<="&C2)

Formule H2 : Concatène les choix

Code :

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

=INDEX(A2:C2;1;EQUIV(1;E2:G2;0)) & "|" & INDEX(A2:C2;1;EQUIV(2;E2:G2;0)) & "|" & INDEX(A2:C2;1;EQUIV(3;E2:G2;0))

Les EQUIV recherche l'indice des ordres 1, 2 ou 3.
Puis l'INDEX trouve le choix correspondant à l'indice trouvé.

Formule I2 : compte les occurrences

Code :

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

=NB.SI(H:H;H2)

[Pierre Fauconnier]

Salut.

Une idée avec des matricielles un tantinet plus simple. Au lieu d'attribuer une lettre à une matière, tu peux leur attribuer un poids en puissance de 2. Dès lors, l'addition de trois matières (colonne VALEUR), quel que soit leur ordre , donnera toujours le même montant unique (l'addition de combinaisons différentes de ces valeurs ne pouvant jamais donner le même résultat). Il suffit alors de concaténer les matières choisies sur la ligne portant le plus petit numéro pour une somme donnée pour créer des "codes" des groupes de matières (colonne CODES). Dès lors, un TCD peut être construit avec les données de la colonne CODES en lignes pour afficher les combinaisons sans doublons. Bien entendu, les matières ne sont pas affichées dans l'ordre, mais les combinaisons sont bien recréées et isolées, ce qui était, je crois, le but de ta question.

Ca a l'avantage de ne pas passer par un tableau intermédiaire (à part le tableau pour l'indication du "poids" de chaque matière)

[jfm22000]

[...]

Désolé je ne peux pas lire la pièce jointe...

[jfm22000]

[...]

Tout ceci semble terriblement efficace mais là... je ne comprends vraiment pas
Il y a forcément quelque chose qui me dépasse...
Je regarde en détail ce week end! Merci!

[Arkham46]

Désolé je ne peux pas lire la pièce jointe...

J'ai essayé de la remettre...

Tu as plein de solutions à analyser, bonne lecture ce WE.

[zenpbb]

Bonjour,
Petite variante à la proposition de Pierre pour la colonne Code (cf post #22) : en Excel 2016, on peut "redécoder" la somme des puissances 2 pour récupérer les 3 choix triés selon la valeur "Initiales" :

Code :

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

{=JOINDRE.TEXTE(", ";VRAI;SI(BITET(t_Thèmes4[Initiales];[@[Valeur]]);t_Thèmes4[Thème];""))}

[Pierre Fauconnier]

Salut

[...]

Code :

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

{=JOINDRE.TEXTE(", ";VRAI;SI(BITET(t_Thèmes4[Initiales];[@[Valeur]]);t_Thèmes4[Thème];""))}

Juste une petite correction: Ce n'est pas en XL2016 que l'on peut utiliser JOINDRE.TEXTE, mais uniquement en XL365 (XL 2016/2019 avec abonnement 365). La fonction JOINDRE.TEXTE est "reconnue" mais pas utiisable en version 2016 sans abonnement 365, c'est pourquoi je ne l'ai pas utilisée ici... (Voir mon billet de blog...) (c'est un scandale lié à la politique commerciale de MS, mais c'est ainsi...)

Sinon =>

PS: Je ne peux que déplorer cette manoeuvre commerciale de Microsoft, car la concaténation par plage et/ou matricielle, on attendait quand même ça depuis très très très longtemps...

[jfm22000]

Merci à tous pour vos contributions, j'ai de quoi m'occuper ce week end!

[Pierre Fauconnier]

[...]
1) une feuille "themes" comportant en sa colonne A tous tes thèmes. Cette colonne peut comporter ou non des lignes blanches (peu importe) et peu importe également l'ordre de tes thémes. Tu pourrais d'ailleurs intervenir sur cette feuille quand et comment te chante
[...]
Voilà tout. et toutes modif, tant sur la feuille themes que sur ta feuille de travail sera "répercutée" comme il se doit et de manière simple et conviviale.

Chacun trouvera la simplicité et la convivialité où il souhaite en fonction de ses critères, ceux d'unparia n'étant ni ceux de jfm22000 ni les miens ni ceux d'autres utilisateurs d'Excel. La simplicité et la convivialité ne sont jamais objectives

La solution que je propose, sans dire qu'elle est la meilleure ou la plus conviviale ou la plus simple (peu me chaut), une fois mise ne place, ne demande rien d'autre que d'encoder (ou copier/coller) les données dans le tableau des choix qui grandit tout seul. Lorsque la liste des matières avec leur pondération est établie (je suppose que cette liste n'évolue pas tous les matins), il suffit de remplir les trois premières colonnes du tableau structuré (qui grandit donc tout seul) (les deux autres peuvent être cachées si elles gênent), puis d'actualiser le TCD. Si de nouvelles combinaisons venaient à être saisies, elles sont intégrées dans le TCD par simple actualisation de celui-ci, et il n'y a rien à recopier, étendre... => juste saisir les données dans les colonnes Choix1, Choix2 et Choix3 et actualiser le TCD. Si une nouvelle matière devait être ajoutée, il suffirait de la saisir dans son tableau, la "difficulté" consistant à lui attribuer la puissance de 2 suivante. Avec des listes déroulantes s'appuyant sur ce tableau, il n'y a de nouveau rien d'autre à faire que de saisir les données... Selon mes critères, je ne vois pas plus simple ni plus convivial

Avantages: Le tableau matières/poids (lui aussi structuré et sans lignes vides quelle horreur!!) peut être utilisé pour remplir les trois colonnes de choix par liste déroulante, le tcd peut être classé (à la demande ou une fois pour toutes) par ordre croissant ou décroissant du nombre de choix de combinaisons, offrant d'autres analyses futures en deux clics (pourcentage de chaque combinaison sur le total des saisies, ...)... En fait, dès la mise en place du tableau de pondération et des deux formules matricielles simplifiées (par rapport aux premières que j'ai proposées), il suffit d'encoder des données dans le tableau des choix. L'actualisation du TCD n'est pas un problème ni une complexité, tant il me semble évident que cet outil fait partie des bases incontournables de l'excellien professionnel

L'utilisateur appréciera chaque solution donnée selon ses critères de simplicité, et a en tout cas "du grain à moudre" quant aux différents outils utilisés et astuces diverses mises en place pour la solution souhaitée (mais perso, ne pas utiliser les tableaux structurés et laisser l'utilisateur saisir sa liste "avec des trous de convivialité" sont des idées qui me laissent perplexes). Il me semble aberrant de ne pas utiliser les outils d'Excel, et les tableaux structurés, les TCD et fonctions matricielles font pleinement partie des outils qu'un excellien doit connaître et maîtriser. Après, qu'ils soient jugés conviviaux ou pas, compliqués ou pas n'a pas d'importance pour moi, puisque chacun a sa propre grille de critères pour évaluer ces aspects d'un outil...

[unparia]

J'ai supprimé ma dernière proposition pour plusieurs raisons :
- laisser au demandeur le temps d'ingurgiter les autres solutions
- j'ai trouvé plus léger et beaucoup plus souple que de faire appel à MOYENNE-GEOMETRIQUE

Je ne montrerai cette dernière solution que si le demandeur en exprime le souhait, notamment s'il éprouve des difficultés à mettre en place les autres propositions qui lui ont été faites.

[Pierre Fauconnier]

Sur base de la proposition supprimée par unparia (Merci Jacques, car ça simplifie l'encodage d'une nouvelle matière ) sur laquelle je me suis appuyée...

On peut effectivement travailler avec une moyenne géométrique, toujours en matricielle. Le poids d'une matière n'a plus besoin d'être calculé (on gagne une colonne et le calcul de la puissance de 2 à ajouter, que l'on aurait pu automatiser), on se sert de la ligne sur laquelle on la trouve dans le tableau, qui peut être déplacé sur une autre feuille si souhaité. L'idée du TCD (lui aussi à déplacer sur une autre feuille) reste pour moi intéressante pour les raisons déjà évoquées... La construction est en fait identique en termes de conception (j'ai juste ajouter les listes déroulantes dans le tableau des choix qui s'appuient sur le tableau des matières et prend en compte ses modifications de manière automatique ), l'astuce étant de trouver un poids unique pour les combinaisons identiques.

[unparia]

Oui.
J'ai (lire mon message précédent) changé d'épaule depuis et laissé aux oubliettes la moyenne géométrique (lourde en calculs en arrière plan) pour lui préférer la concaténation des N°s de ligne de la feuille thème, numéros mis en ordre alphabétique (PETITE.VALEUR) et séparés par un tiret, tout simplement, genre "2-4-10" pour un thème de la ligne 4, un de la ligne 10 et un de la ligne 2 (exemple).
On évite ainsi :
- un calcul plus lourd (moyenne géométrique)
- l'inconvénient d'un éventuel très grand nombre, plus tard, de thèmes. (la valeur du "poids" et son calcul).
Amitiés

[Pierre Fauconnier]

Eh ben dis donc...

Ca en fait de la matière à potasser avec toutes ces techniques, outils, fonctions et formules à appréhender, comprendre et mettre en oeuvre.

Belle discussion que celle-ci, je trouve, éclairante sur pas mal d'aspects Excel évoqués dans les différentes propositions et qui ouvrent la porte sur de chouettes solutions

[unparia]

Ah...

Comme tu t'en es sans aucun doute aperçu, Pierre, la fonction PETITE.VALEUR connaît quelques difficultés à s'appliquer directement à trois valeurs extraites par EQUIV, ce qui oblige alors à 3 colonnes "tremplins" de valeurs issues de EQUIV. (trop d'arguments pour les petits muscles de PETITE.VALEUR)

Et je me suis couché en maudissant cette faiblesse de PETITE.VALEUR et en me disant qu'il serait bon de chercher comment éviter ces 3 tremplins.

Je viens de me réveiller en sursaut avec ce que je pense être la solution et si j'ai raison :
- je me donnerais des baffes de ne pas avoir eu ce réflexe de matheux plus tôt
- plus qu'une seule colonne à cacher.
- ni moyenne géométrique, ni concaténation de N°s de ligne (ni d'aucune autre sorte), ni formule matricielle

Mes premiers tests (que je viens de faire) conduisent à un excellent résultat, mais je multiplierai demain matin le nombre de ces tests (au maximum raisonnable) pour être absolument certain de l'absence d'un os.
Bonne nuit et amitiés

[unparia]

Mes tests (très nombreux et variés) ayant été concluants ---->>

Préambule :
comme dit plus haut, j'ai mis aux oubliettes la solution utilisant la moyenne géométrique du fait de la lourdeur des calculs induits.
Cet aspect n'était toutefois pas le seul ayant motivé cette décision.
En existait un autre, bien plus gênant, résultant des limites de VBA en matière d'arithmétique flottante. Et bien que fût infime la possibilité d'obtenir un résultat VBA "identique" pour deux moyennes géométriques appliquées à des combinaisons différentes, on ne pouvait l'exclure de manière catégorique, notamment si appliquée à des nombres plus importants.

J'ai également écarté finalement la méthode "par concaténation des N°s de ligne", qui obligeait l'ajout d'un tri alourdissant et qui nécessitait lui-même des tremplins du fait de faiblesses de la fonction PETITE.VALEUR (voir ce que j'en ai dit dans mon message précédent)
-------------------------------

Utilisons alors les factorielles, qui, elles, ne risquent pas de s'exprimer par des décimales

Base de la réflexion :

Si S est la somme des factorielles de x nombres entiers distincts et > 0, S ne peut être la somme des factorielles que d'une seule combinaison de x nombres entiers distincts et > 0
Ainsi :
si S = 9 et totalise les factorielles de 3 nombres entiers distincts et > 0, ces 3 nombres ne peuvent être que 1,3 et 2
si S = 32 --->> ces nombres sont forcément 2, 3 et 4
10 ne saurait être la somme des factoielles de 3 nombres entiers distincts et > 0

On voit que l'on "tient" dès lors là un bon moyen d'attribuer sans faille des valeurs distinctes "identificatrices" à des combinaisons distinctes de nombres entiers distincts et > 0.

le hic ? on atteint très vite des valeurs énormes. Cela se traduit, pour ce qui nous intéresse, par une gêne dès lors que le nombre de thèmes (et donc de lignes) est élevé.

Remède :
la simple factorielle (n!) n'est pas la seule à générer ce que j'ai exposé plus haut en ce qui concerne la somme des factorielles.
Est également dans ce cas la double factorielle (n!!).
Et les résultats de la double factorielle sont des valeurs nettement inférieures à ceux de la simple factorielle.
Pour en avoir un aperçu :
10! = 3628800, alors que 10!! = 3840

Bonne nouvelle :
EXCEL nous offre la fonction FACTDOUBLE et nous allons en profiter sans complexe.
Il y a à nouveau un petit hic : si le nombre de nos thèmes distincts est supérieur ou égal à 200. Mais je "vois" mal un nombre si important (plus de 199) de disciplines distinctes enseignées dans un établissement, voire par l'ensemble des établissements d'une même académie.
FACDOUBLE va donc pouvoir répondre à notre problématique.
et voici comment --->> les deux images suivantes sont auto-explicites --->>
1) une feuille nommée "thèmes" -->>

2) la feuille de travail -->>


Voilà. Le mécanisme est là et il est efficace tel quel.
On peut bien évidemment (si l'on y tient ou pour le simple plaisir de changer de "style" de traitement) procéder autrement. A chacun son plaisir; le fondement du mécanisme compte finalement seul.
Amitiés


EDIT (et pour la petite histoire). J'ai également pensé, à un instant, à l'utilisation des logarithmes népériens. J'ai laissé "tomber" cette idée pour deux raisons :
1) je me serais à nouveau trouvé confronté aux limites de VBA en matière de précision en arithmétique flottante ... et aux risques, aussi infimes fussent-ils, alors identiques à ceux qui accompagnaient l'utilisation de la moyenne géométrique
2) il m'aurait fallu faire un nombre alors encore plus considérable de tests (y compris "sériés") et :
a) je commence à fatiguer
b) les louvines réapparaissent et attendent que je les pêche -->> re

EDIT 2 : et en parlant de pêche :
J'ai l'habitude de systématiquement supprimer de mon disque dur tout ce qui a pu être conduit à terme. Je vais faire une exception car le présent exercice va trouver une application extrêmement intéressante pour moi en matière de pêche (analyse des résultats en fonction de facteurs divers, avec des combinaisons de facteurs bien supérieures à 3 facteurs et un nombre de facteurs distincts pouvant être supérieur à la centaine ).
Je suis d'ailleurs à ce propos à peu près certain que d'autres domaines seraient intéressés, y compris (et entre autres) en matière électorale, en matière de voeux de composition d'équipes, etc ....
Je laisse à chacun le soin de voir ce qu'il pourrait faire utilement (pour lui) sur ces bases et d'adapter au besoin. Ce dernier aspect ayant nettement plus à voir avec la créativité qu'avec quoi que ce soit d'autre

[zenpbb]

Bonsoir unparia,

J'ai bien lu ta contribution basée sur FACTDOUBLE, et c'est vrai qu'on découvre presque tous les jours des utilisations détournées des fonctions qu'Excel met à disposition.

Je n'ai peut-être pas bien compris, mais j'ai du mal à voir l'avantage dans ce cas de la fonction FACTDOUBLE(x) par rapport à PUISSANCE(2;x) qui était la base implicite de la proposition de Pierre.
Ainsi, dans ta proposition, ne peut-on pas utiliser en colonne D : =PUISSANCE(2;EQUIV(A13;Tableau14[Choix];0)) ... plutôt que =FACTDOUBLE(EQUIV(A13;Tableau14[Choix];0) ..., ou alors y-a-t-il une subtilité que je n'ai pas perçue ?

Par ailleurs, l'une et l'autre de ces deux formules me semblent être limitées bien en dessous de la centaine de facteurs distincts que tu mentionnes dans ton EDIT 2, car on monte assez vite en puissance de dix.

En tout état de cause, sujet bien intéressant...

PS : J'espère quand même que tu ne vas pas tenter de te servir de ces formules pour optimiser tes activités de pêche, parce que ça m'étonnerait qu'Excel (si grand soit-il) puisse être à l'origine de la pêche miraculeuse !

[unparia]

Bonjour zenpbb

ou alors y-a-t-il une subtilité que je n'ai pas perçue ?

c'est surtout que tu n'as pas vraiment compris le mécanisme en cause (que proposait Pierre) et qui n'a pas vraiment quelque-chose en commun On ne va pas recommencer tout, mais résumer ce qui était proposé : attribuer des valeurs (en puissances de deux) distinctes à chaque thème.

me semblent être limitées bien en dessous de la centaine de facteurs distincts que tu mentionnes dans ton EDIT 2, car on monte assez vite en puissance de dix.

Essaye et tu verras (j'ai testé et ai parlé de cette limite, qui est très précisément 199)

te servir de ces formules pour optimiser tes activités de pêche, parce que ça m'étonnerait qu'Excel (si grand soit-il) puisse être à l'origine de la pêche miraculeuse !

Excel seul, non. Ce que je lui ferai "cracher" sur la base de ce que je lui "donnerai"-->> oui, pardi ... et comment !
On ne parle pas de "miracles", mais de choix et décisions affinés en fonction de facteurs présents (ce que je fais déjà, mais plus douloureusement, avec mes seuls mémoire et neurones).
Allez.
Amitiés.

EDIT : ah oui. Je vois ce que tu veux dire et tu as en effet raison d'en faire la remarque.
on gagnerait ainsi sur les x de rang élevé et on perdrait sur ceux de rang < 8. Mais ces derniers étant les moins nombreux si l'on traite un grand nombre de thèmes, c'est plus intéressant pour moi, alors que ce l'est moins dans le cas de cette discussion (8 thèmes).
Je retiens donc ta proposition car elle fait mieux mon affaire "pêche". Merci.

[Pierre Fauconnier]

[...]

Je me posais un peu la même question: Qu'apporte FACTDOUBLE par rapport aux puissances de 2? (notamment en termes de simplicité et de convivialité, notions subjectives s'il en est)...

Dans mon premier exemple, le poids était renseigné à la main, l'idée étant de montrer comment on crée une valeur qui ne peut représenter qu'une seule combinaison de x poids. Mais on peut bien sûr l'obtenir par calcul dans le tableau des choix, colonne D, comme illustré ci-dessous.





Pourquoi je préfère le système utilisant les puissances de 2?

Avec les puissances de 2, on peut aller en théorie jusqu'à 2^1023 avec Excel, ce qui veut dire que l'on pourrait créer des combinaisons de x valeurs parmi 1023 valeurs distinctes. La limite me semble donc théorique.

Quel que soit le nombre de choix réalisé, on est certain que si une valeur représente l'addition d'une série de x puissances de 2, alors elle ne peut représenter que cette suite de x valeurs, à l'exclusion de tout autre suite de x ou de y valeurs. On ne peut pas être sûr de cela avec les moyennes géométriques, par exemple.

Si une valeur représente l'addition d'une série de puissances de 2, alors on peut retrouver cette série de puissances (et donc les thèmes y associés) sur base de la valeur donnée, et on peut également tester qu'une puissance de 2, et donc un thème, est présent dans la suite représentée par cette valeur, par un simple calcul binaire.

En effet, 84 ne peut être que la somme des puissances de 2 suivantes: 4, 16 et 64. La somme d'une autre série de puissances de 2, quel qu'en soit le nombre, ne peut donner 84. Et en réalisant un traitement sur les bits, on peut déterminer que 16 fait partie du "choix" 84, mais pas 8, par exemple.

[unparia]

Bonjour Pierre
Tout-à-fait
Et je vais utiliser dans la foulée la fonction SOMME.SERIESCARRES plutôt que d'additionner les puissances une par une

[unparia]

Et cela va de surcroît me faciliter la vie pour ce que je vais faire (pêche). Et ce d'autant que les déterminations et extractions sont bien plus complexes du fait de l'attribution de "coefficients" d'importance aux facteurs.
Pour que l'on comprenne mieux -->> restons dans l'esprit du demandeur et réduisons à 2 les choix de disciplines à analyser -->>
il est par exemple clair que choisir les maths en "principal" et la physique en "complément" n'est pas du tout la même chose que choisir la physique en "principal" et les maths en "complément".
Je vais avoir du boulot ... c'est sûr.