Liste ordonnée sans doublon

Le problème que nous avons à résoudre aujourd’hui est assez délicat. Nous avons une série de valeurs en ligne 1, et nous souhaitons obtenir en ligne 3 une liste décroissante de  ces valeurs, mais sans doublon. Et, dans la ligne 4, il faut obtenir une liste croissante sans doublon.

Ce n’est pas un problème évident, mais il n’est pas nécessaire non plus d’être un génie pour trouver la solution. Essayez donc de voir si vous y arrivez…

Nous avons représenté les formules de A3 et B3 dans la copie d'écran.

La formule de B3 est matricielle, donc validée avec [Ctrl]-[Maj]-[Entrée].

Elle est ensuite reproduite à droite.

Pour les formules de la ligne 4, c’est la même chose, en intervertissant « max » et « min ».

Remarque – On aurait pu entrer « $A1:$L1 » à la place de « 1:1 », mais notre solution est plus compacte et fonctionne parfaitement bien s’il n’y a rien à droite dans la ligne 1.

La fonction Remplacer()

A l’instar de la fonction substitue(), qui a été l’objet des cinq derniers messages – du 6 au 22 septembre –, la fonction remplacer() sert à remplacer une chaîne de caractères par une autre chaîne de caractères.

Sa syntaxe est : =remplacer(texte;n°car;nb.car.;chaîne)

Cette formule va prendre le texte original et, à partir du caractère dont le numéro est n° car, remplacer un nombre de caractères égal à nb.car par la chaîne proposée pour le remplacement.

 
Avec remplacer(), la chaîne à remplacer est identifiée par sa position et sa longueur, alors qu’avec substitue(), elle l’était par son contenu et son numéro d’occurrence.

Par ailleurs, substitue() permet d’effectuer plusieurs remplacement d’un coup – quand le n° d’occurrence n’est pas spécifié – alors que remplacer() ne permet d’effectuer qu’un remplacement à la fois.

En conclusion, ces deux fonctions sont complémentaires, chacune offrant des possibilités que l’autre ne propose pas.

Remarque – Cet article est une actualisation d’un article publié le 28 avril 2008.

Plus sur les groupes de « 1 »

Nous prolongeons aujourd’hui l’article publié il y a quatre jours, d’une part en montrant une autre solution dont la formulation est nettement plus compacte , d’autre part en montrant comme on peut engendrer des séquences aléatoires de « 0 » et de « 1 » d’une telle longueur, de 36 caractères.

Une solution par formule matricielle

La copie d’écran nous montre le même résultat que celui obtenu avec la première solution, avec en plus dans les lignes 13 et 14 les solutions proposées par Roberto Mensa.

Les deux formules imaginées par Roberto sont des formules matricielles, donc validées avec [Ctrl]-[Maj]-[Entrée]. Les voici :

A13 : =texte(somme(10^{0;2;4;6;8;10}*(2+nbcar(substitue(substitue($A$3;"01"; "0|1");"10";"1|0"))-nbcar(substitue("|"&substitue(substitue($A$3;"01";
"0|1");"10";"1|0")&"|";"|"&rept(1;{1;2;3;4;5;6})&"|";"||")))/{1;2;3;4;5;6});"|"&rept("00|";6))

A14 : =texte(somme(10^{6;4;3;2;1;0}*(2+nbcar(substitue(substitue($A$3;"01"; "0|1");"10";"1|0"))-nbcar(substitue("|"&substitue(substitue($A$3;"01";
"0|1");"10";"1|0")&"|";"|"&rept(1;{1;2;3;4;5;6})&"|";"||")))/{1;2;3;4;5;6});"|00|00|0|0|0|0|")

Je vous laisse le plaisir de décortiquer ces deux formules…

Série aléatoire de « 0 » et de « 1 »

Pour créer une série aléatoire de 36 valeurs en « 0 » et en « 1 », nous allons utiliser la fonction decbin(), qui assure la conversion d’un nombre décimal en nombre binaire. Le problème, c’est que le plus gros argument positif de cette fonction est 511, ce qui fait en binaire neuf « 1 » d’affilée.

Pour engendrer un nombre aléatoire de 36 bits (dinary digit, donc « 0 » ou
« 1 ») , l’astuce revient à concaténer quatre fonctions. Chacune d’entre elles tire un nombre aléatoire de 0 à 511,999 mais – comme les décimales sont ignorées – cela revient à un nombre entier aléatoire de 0 à 511.

Encore faut-il résoudre un problème, le fait que les « 0 » à gauche ne sont pas affichés, car ils ne sont pas significatifs. Ce problème est résolu grâce à la formule droite("00000000"&decbin(…);9) !

Voici donc la formule permettant de créer un nombre aléatoire de 36 bits :

=droite("00000000"&decbin(512*alea());9)&droite("00000000"&decbin(512*alea());9)&droite("00000000"&decbin(512*alea());9)&droite("0000000"&decbin(512*alea());9)

Comptage de groupes de « 1 »

Un challenge posé récemment dans un de mes groupes d'experts était de trouver une formule calculant, dans un bloc contenant des « 0 » et des « 1 », le nombre de groupes de un « 1 », de deux « 1 » consécutifs (au bord ou séparés par des « 0 »), de trois « 1 », … et ainsi de suite.

Pour comprendre la formule que j’ai trouvée, il est préférable d’avoir lu les articles précédents sur la fonction substitue(), en particulier le tout dernier, publié il y a quatre jours.

Dans le modèle reproduit ci-dessous, nous affichons en A7:A11 les résultats obtenus dans les cellules A1:A5. Cette présentation décalée nous permet d’éviter d’avoir une copie d’écran trop large…

Voici la formule magique entrée en A7 puis recopiée vers le bas :

="Un 1 : "&nbcar(substitue(A1;"0";"00"))+2-nbcar(substitue("0"&substitue(A1;"0";"00")&"0";"010";"00"))&" - "&

"Deux 1 : "&(nbcar(substitue(A1;"0";"00"))+2-nbcar(substitue("0"&substitue(A1;"0";"00")&"0";"0110";"00")))/2&" - "&

"Trois 1 : "&(nbcar(substitue(A1;"0";"00"))+2-nbcar(substitue("0"&substitue(A1;"0";"00")&"0";"01110";"00")))/3&" - "&

"Quatre 1 : "&(nbcar(substitue(A1;"0";"00"))+2-nbcar(substitue("0"&substitue(A1;"0";"00")&"0";"011110";"00")))/4&" - "&

"Cinq 1 : "&(nbcar(substitue(A1;"0";"00"))+2-nbcar(substitue("0"&substitue(A1;"0";"00")&"0";"0111110";"00")))/5&" - "&

"Six 1 : "&(nbcar(substitue(A1;"0";"00"))+2-nbcar(substitue("0"&substitue(A1;"0";"00")&"0";"01111110";"00")))/6

Remarque – Nous avons été obligés de faire un double substitue() : le premier pour doubler tous les « 0 », le second pour ajouter des « 0 » aux bords.

Certes, cette formule est un peu lourde, mais elle fonctionne parfaitement !

Un bug de substitue()

La fonction substitue() est censée remplacer toutes les occurrences d’une chaîne donnée de caractères par une autre chaîne donnée.

En fait, les choses ne sont pas si simples que cela…

Dans les trois exemples ci-dessous, nous avons la même formule entrée en B1, puis recopiée vers le bas, dont le texte est reproduit en colonne C.

Pour la ligne 1, aucun problème : les trois occurrences de « 0110 » sont bien remplacées par « 0a0 »…

En ligne 2, on constate que seules la première et la troisième occurrence sont modifiées.

En ligne 3, seules la première et la seconde occurrence sont modifiées.

Cela est dû à une sorte de bug de conception (ou de programmation ?) qui fait que la partie du texte qui a été modifiée ne peut plus concourir à une seconde modification. Dans le cas de la cellule A2, puisque le « 0110 » des quatre premiers caractères a été remplacé par « 0a0 », c’est uniquement à partir du cinquième caractère de A2 que les tentatives de remplacement de chaîne reprennent, ce qui fait que la seconde occurrence de « 0110 » n’est plus reconnue comme telle et modifiée, puisqu’elle démarre avec le « 0  » en quatrième position qui n’est plus considéré comme candidat…

Et c’est pour cette raison que, pour la chaîne en A3, les deux premiers remplacements attendus ont bien lieu, mais que le troisième est loupé…

En conclusion, faites bien attention quand vous utilisez la fonction substitue() : il faut bien la connaître pour l’utiliser sans tomber dans ses pièges…