Permutation aléatoire d’une série

Le problème que nous avons à résoudre aujourd’hui est original : nous avons besoin de créer une permutation aléatoire d’une série de nombres.

Les données du problème sont la borne inférieure de la série (5 dans notre exemple) et le nombre de valeurs consécutives (7 dans notre exemple) que nous souhaitons permuter. Le but est d’obtenir un résultat tel que celui affiché en colonne A, avec un permutation des valeurs de 5 à 11.

La formule de A2, recopiée ensuite jusqu’en A50, est :
=si(ligne()>$E$2+1;"";alea())

La formule de B2, recopiée ensuite jusqu’en B50, est : =si(estnum(A2);$E$1+equiv(grande.valeur($A$1:$A$50;ligne()-1);
$A$1:$A$50;0)-2;"")

Remarque 1 – Si vous voulez utiliser ce modèle pour préparer votre ticket de loto, il suffit d’entrer 1 en E1 et 49 en E2. Pour jouer une combinaison à 7 numéros, prenez alors les numéros affichés de B2 à B8.

Remarque 2 – Et maintenant, un challenge pour les champions : essayez à présent de résoudre ce problème en utilisant une seule colonne (A) au lieu de deux (A et B), donc avec une seule formule en A2, recopiée vers le bas. Je vous proposerai une solution dans le prochain article !

Trouver le nombre manquant

Toujours dans les échanges entre fêlés d’Excel, j’ai encore découvert un challenge intéressant qui a provoqué de multiples propositions.

Vous avez en A2:A8 une série de valeurs ayant la propriété suivante : ce sont tous des nombres entiers, formant une séquence non ordonnée dans laquelle un seul nombre est manquant. Dans notre exemple, on voit tout de suite qu’il s’agit du nombre 4.

Pour que vous compreniez mieux les formules proposées, il faut connaître la fameuse formule donnant la somme des valeurs de 1 à n :  
1 + 2  + … + (n-1) + n = n * (n+1) / 2

Vous trouvez en B2 à B5 les formules proposées par quatre personnes, avec le nom de l’auteur à droite.

La formule que je propose est celle qui m’est venue immédiatement à l’esprit : elle est intuitive et applique tout simplement, sans la moindre fioriture, la fameuse formule.

Roberto Mensa propose une formule plus courte, mais dont la compréhension demande un peu plus de travail…

Henk de Jong, en utilisant lui aussi la fonction lignes(), parvient à réduire encore la taille de la formule.

Bruce McPherson, enfin, trouve la formule qui est à la fois la plus courte et la plus originale. Bravo !

Flexibilité et robustesse des formules

Mais la compacité d’une formule n’est pas tout. Il est souhaitable qu’une formule soit flexible et/ou robuste.

Qu’advient-il donc des quatre formules si l’on insère par exemple deux lignes blanches entre la ligne 7 et la ligne 8 ?

Dans ce cas, seules ma formule et celle de McPherson continuent à fournir le bon résultat !

Qu’advient-il enfin si, dans une des deux nouvelles cellules de la liste, nous entrons une valeur faisant doublon avec une autre valeur de la liste ?

Dans ce cas, aucune des quatre formules ne résiste.

Mais un autre membre du groupe, Elias Estrada, a trouvé une formule – certes deux fois plus longue, et à valider avec [Ctrl]-[Maj]-[Entrée] – qui résiste même à cette agression :

=min(si(nb.si(A2:A8;ligne(indirect(min(A2:A8)&":"&max(A2:A8))))=0;ligne(indirect(min(A2:A8)&":"&max(A2:A8)))))

Impressionnant, n’est-ce pas !

Cela nous prouve, une fois de plus, qu’il y a de multiples façons de modéliser dans Excel !

A qui appartient le nombre ?

Dans le dernier article, nous avons vu comment identifier le plus petit entier unique dans un tableau de plusieurs lignes et trois colonnes. Aujourd’hui, nous cherchons à identifier – en H1 et H2 – la personne qui a proposé ce nombre unique.

Comme dans l’article précédent, nous vous proposons deux solutions, la première – plus naturelle – avec une formule matricielle, la seconde avec une formule validée normalement. Les auteurs de ces deux formules sont les mêmes que dans l’article précédent.

Formule de H1 : =index(A:A;min(si(B1:D50=F1;ligne(B1:D50))))

Formule de H2 : =index(A:A;sommeprod(ligne(B1:D50)*(B1:D50=F2)))

Remarque 1 – Ce n’est pas la première fois que nous proposons une solution en validation directe avec sommeprod() alors que – sans sommeprod() – on est obligé d’utiliser une formule matricielle !

Remarque 2 – Les valeurs dans les colonnes B à D ne sont pas les mêmes que dans l’article précédent puisque – comme je l’ai expliqué à la fin de cet article – j’ai rendu aléatoires les valeurs de ces trois colonnes.

Le plus petit entier unique

Dans un forum Excel que je consulte régulièrement, un challenge a été posé récemment. Plusieurs personnes (en colonne A) doivent proposer jusqu’à trois nombres entiers. Le but du jeu est d’avoir dans sa liste le plus petit nombre que personne d’autre n’a choisi.

Dans l’exemple ci-dessous, on voit en F1 et en F2 le résultat de notre tableau en A1:D12, soit la valeur 7.

Question : Quelle formule utiliser pour trouver le plus petit entier unique ?

Voici deux réponses intéressantes.

La première solution a été proposée par Elias Estrada, avec une formule matricielle, donc validée avec [Ctrl]-[Maj]-[Entrée].

Formule de F1 : =min(si(nb.si(B1:D50;B1:D50)=1;B1:D50))

La seconde solution, proposée par Stephen Dunn, ne requiert même pas de formule matricielle. Elle est en revanche sensiblement plus complexe :

Formule de F2 : =min(index($B$1:$D$50+10^9*((nb.si($B$1:$D$50;$B$1:$D$50)>1)+($B$1:$D$50=""));))

Je vous laisse le plaisir de la décortiquer pour comprendre son mode de fonctionnement… Cela revient à ajouter un grand nombre dès que la cellule concernée contient un nombre non unique ou est vide.

Remarque – On peut remplacer 10^9 par 100 si l’on est sûr que le plus petit nombre unique est inférieur à 100…

Pour pouvoir tester ces formules en « grandeur nature », je me suis amusé à créer des séries aléatoires pour les trois nombres proposés. Voici les formules que j’ai entrées en ligne :

B1 : =ent(1+9*alea())

C1 : =ent(B1+1+9*alea())

J’ai ensuite recopié C1 en D1, puis le bloc B1:D1 vers le bas…

« Outils de tableau – Création »

Quand la cellule active appartient à un tableau, depuis Excel 2007, on peut accéder à un ruban « Outils de tableau – Création », que nous avons reproduit ci-dessous en deux morceaux.

Le nom du tableau apparaît dans la parti gauche de ce ruban : il est aisé de remplacer le nom proposé par défaut par un autre nom, comme nous l’avons fait dans cet exemple avec « SummerSchool ».

La commande « Synthétiser avec un tableau croisé dynamique » est en fait équivalente à la commande « TblCroiséDynamique » du ruban « Insertion ». Il aurait été plus cohérent de simplement nommer cette commande « Insertion Tableau Croisé Dynamique » pour que l’on comprenne bien qu’il s’agit de la même chose…

Les deux autres commandes de ce second bloc du ruban sont bien utiles. La première sert à effectivement ôter tous les doublons, en laissant à l’utilisateur le choix des champs qu’il utiliser pour déterminer ces doublons. La seconde permet de facilement « Convertir en plage », une commande très utile qu’il n’est pas facile de trouver quand on fait un clic droit sur une cellule du tableau.

Lisez bien l’article « La commande "Mettre sous forme de tableau" » du 10 mars 2012 pour mieux saisir l’utilité de cette commande.

Dans la partie droite du ruban, nous avons les blocs « Options de style de tableau » et « Styles de tableau ». Quand on modifie les options du premier bloc, cela modifie aussitôt les styles proposés dans le second bloc.

Remarque – L’option « Ligne des totaux », contrairement à son libellé, ne crée pas un total pour chaque champ numérique, mais seulement pour le tout dernier champ, quand celui-ci est numérique…

Du positif sur les tableaux…

A ce jour, je n’ai quasiment dit dans ce blog que du mal de la commande « Mettre sous forme de tableau »… En particulier, qu’elle insère des menus déroulants dont on n’a pas forcément besoin, et surtout – bien plus grave encore ! – qu’elle crée une demi-fusion des lignes et des colonnes qui se transforme en fusion totale dès que deux tableaux ont des lignes ou des colonnes en commun.

Ajout simplifié d’une colonne ou d’une ligne

Dès que l’on entre quoi que ce soit dans la première cellule à droite du nom du dernier champ du tableau, ou dans la première cellule sous la dernière ligne de la première colonne du tableau, la dimension du tableau est immédiatement actualisée.

Création rapide d’un champ calculé

Dans l’exemple ci-dessous, nous avons ajouté à la base un dernier champ baptisé « Ratio ». En G2, nous avons saisi la formule =[@Salaire]/([@Age]-10).

En fait, [@Salaire] est apparu automatiquement dès que nous avons cliqué en E2 et [@Age] quand nous avons cliqué en D2…

Dès la validation de la formule, celle-ci a été étendue de façon automatique par Excel à toute la colonne de la base !

Fixation des titres pour le défilement

Enfin, nous voyons, dans la seconde partie de la copie d’écran que, dès que nous faisons défiler la table vers le bas, le nom des colonnes est remplacé de façon automatique (à condition que la cellule active soitr dans la base) par le nom des champs.

Cela vous évite d’avoir à créer une séparation horizontale et à figer les volets !

Insertion d’une ligne ou d’une colonne dans la base

Quand vous sélectionnez une ou plusieurs cellules dans la même ligne ou la même colonne du tableau, un clic droit fait apparaître des commandes « Insérer » et « Supprimer » permettant d’insérer ou de supprimer des éléments du tableau sans pour autant perturber ce qui se trouve en dehors de celui-ci dans la même feuille.

En guise de conclusion…

Il y a donc quand même quelques fonctionnalités intéressantes dans les tableaux.

Ce qui est regrettable, c’est que Microsoft n’ait pas gardé – en plus – la commande « Format automatique » d’Excel 2003, qui était bien pratique pour décorer rapidement un tableau.

Ajout auto de commentaires

Un de mes clients m’a récemment demandé de lui construire une macro permettant de mettre – de façon automatique – le contenu de la colonne C comme commentaire des cellules de la colonne A.

Nous voyons ci-dessous le tableau obtenu après exécution de la macro, qui est reproduite en dessous. Chaque cellule de A1 à A9 a bien reçu un commentaire, comme nous pouvons le constater avec la cellule A5 .

Remarque 1 – La macro efface tout commentaire antérieur éventuel grâce à la commande ClearComments.

Remarque 2 – S’il y avait un commentaire antérieur et que cette commande était absente, la macro s’arrêterait là avec une erreur.

Remarque 3 – On pourrait certes éviter ce problème sans la commande ClearComments, en mettant en amont la commande On Error Resume Next, mais ce serait moins « propre ».