Premier entier manquant dans la suite des entiers naturels croissante

[Slyces]

Bonjour,
Pour les besoin d'un atelier de recherche mathématiques, Maths en Jeans, je voudrais créer sur excel une formule qui puisse, en une seule cellule (c'est important) faire les choses suivant :

Chercher dans deux collections de valeurs différentes, soit toutes les cellules de la même colonnes mais situées au dessus de celle où se situe la formule, et toutes les cellules de la même ligne mais situées à gauche de la cellule où se situe la formule.

Donc, dans ces deux collections, où les valeurs sont dans un ordre aléatoire et parfois en doublon, je dois trouver le premier entier manquant dans la suite des entiers naturels croissante : (un exemple)

J'ai les valeurs 0 1 4 6 7 9 8 0 4 80 38 39 89 3 9 ; Le premier entier manquant, dans l'ordre "0,1,2,3,4,5 ..." est ici 2.

Je n'ai pas réussi à résoudre ce problème avec mes propres connaissances, car je ne peux pas passer par d'autres cellules pour faire ma fonction en plusieurs fois (en effet, je dois étendre cette formule sur tout un tableau).

J'ai bien eu l'idée de faire des SI imbriqués (Je compte le nombre de 0, s'il n'y en a pas je l'affiche, sinon je compte les 1 ...) mais c'est très vite limité par le nombre d'imbrications et la lenteur d'écriture de la fonction.

En espérant que vous pourrez m'aider, ci dessous, un exemple du tableau auquel je veux parvenir (Ne vous occupez pas des deux premières colonnes et lignes, elles ne sont pas créées par la formule que je cherche), ainsi qu'un lien d'une discussion plus ou moins ressemblante mais où les solutions ne sont pas expliquées étapes par étapes et je ne peux pas les adapter.

https://www.excel-downloads.com/thr...s-manquants-dans-une-suite-excel-2007.161608/

 

[Nairolf]

Re : Premier entier manquant dans la suite des entiers naturels croissante

Salut Slyces,

Il manque le fichier.

Je n'aurais pas le temps de regarder ça ce weekend, mais si le sujet est encore en cours la semaine prochaine, je m'y pencherais dessus.

Bon weekend le forum!

 

[job75]

Re : Premier entier manquant dans la suite des entiers naturels croissante

Bonjour Slyces, bienvenue sur XLD, salut Nairolf,

En l'absence de fichier voici le mien avec cette formule matricielle en D4 :

=MIN(SI(ESTNA(EQUIV(LIGNE(INDIRECT(MIN(B4:B18)+1&":"&MAX(B4:B18)));B4:B18;0));LIGNE(INDIRECT(MIN(B4:B18)+1&":"&MAX(B4:B18)))))

Il serait facile de déterminer le 2ème, 3ème, 4ème etc... manquant, si ça vous intéresse dites-le.

A+

 

[job75]

Re : Premier entier manquant dans la suite des entiers naturels croissante

Re,

Bon, dans ce 2ème fichier j'ai créé la liste des manquants avec cette formule en E4 à tirer vers le bas :

=PETITE.VALEUR(SI(NON(NB.SI(B$4:B$18;LIGNE(INDIRECT(MIN(B$4:B$18)+1&":"&MAX(B$4:B$18)))));LIGNE(INDIRECT(MIN(B$4:B$18)+1&":"&MAX(B$4:B$18))));LIGNES($4:4))

Edit : j'ai remplacé EQUIV par NB.SI, c'est un peu plus léger.

A+

 

[Slyces]

Re : Premier entier manquant dans la suite des entiers naturels croissante

Merci beaucoup ! J'ai justement créé ce sujet car les formules que tu avais postées sur le sujet linké semblaient résoudre tous mes soucis mais je n'arrivais pas à les comprendre et/ou les adapter à mon cas :/

Je viens de rajouter le fichier sur mon premier post, il permet de mieux comprendre mon problème (je pensais l'avoir mis la première fois désolé)

 

[job75]

Re : Premier entier manquant dans la suite des entiers naturels croissante

Re,

Faudrait avoir de la suite dans les idées, vous avez écrit au post #1 :

J'ai les valeurs 0 1 4 6 7 9 8 0 4 80 38 39 89 3 9 ;

Où sont elles dans votre fichier

Pour l'instant c'est le trou noir.

A+

 

[CISCO]

Re : Premier entier manquant dans la suite des entiers naturels croissante

Bonsoir à tous, bonsoir Job75

Deux autres possibilités en pièce jointe, d'après le fichier de Job75.

@ plus

PS : La formule proposée par Job75 ne donne pas toujours la bonne solution, il me semble... A vérifier.

 

[CISCO]

Re : Premier entier manquant dans la suite des entiers naturels croissante

Bonsoir

Une autre pour la route

MIN(SI(DECALER(B$4;;;MAX(DECALER(B$4;;;EQUIV(9^9;B:B;1)-3)))<>LIGNE(INDIRECT("1:"&MAX(DECALER(B$4;;;EQUIV(9^9;B:B;1)-3))))-1;LIGNE(INDIRECT("1:"&MAX(DECALER(B$4;;;EQUIV(9^9;B:B;1)-3))))-1))

, toujours en matriciel, à valider donc avec Ctrl+maj tempo + entrer.

@ plus

 

[job75]

Re : Premier entier manquant dans la suite des entiers naturels croissante

Re,

Avec cette fonction VBA on ne se prend vraiment pas la tête :

Function PEM(P1 As Range, P2 As Range) 'Premier Entier Manquant
While Application.CountIf(P1, PEM) + Application.CountIf(P2, PEM)
PEM = PEM + 1
Wend
End Function

Je l'utilise en J8 avec cette formule =PEM($B8:I8;J$2:J7)

Maintenant à vous d'en faire ce que vous voulez.

Fichier joint.

A+

 

[Slyces]

Re : Premier entier manquant dans la suite des entiers naturels croissante

Woaw, ça marche parfaitement ! Merci beaucoup !!! Si à l'occasion tu as le temps d'expliquer un peu ce que tu as fait, je ne serais vraiment pas contre !
En tout cas désolé si j'ai pas été clair du premier coup ^^

 

[job75]

Re : Premier entier manquant dans la suite des entiers naturels croissante

Bonjour Slyces, le forum,

Je n'ai vraiment rien fait de spécial, sauf qu'il faut mettre la macro dans un module standard (Alt+F11) et enregistrer le fichier en .xls ou .xlsm.

Maintenant on peut aussi entrer une formule matricielle en J8 :

=MIN(SI(NON(NB.SI($B8:I8;LIGNE(INDIRECT("1:"&MAX($B8:I8;J$2:J7)+2))-1)+NB.SI(J$2:J7;LIGNE(INDIRECT("1:"&MAX($B8:I8;J$2:J7)+2))-1));LIGNE(INDIRECT("1:"&MAX($B8:I8;J$2:J7)+2))-1))

A valider par Ctrl+Maj+Entrée et tirer sur tout le tableau C3:S19.

L'exécution est beaucoup moins rapide que celle de la fonction VBA.

Fichier joint.

A+

 

[job75]

Re : Premier entier manquant dans la suite des entiers naturels croissante

Re,

Formule en J8 nettement meilleure car elle évite le risque de calculs trop longs :

=MIN(SI(NON(NB.SI($B8:I8;LIGNE(INDIRECT("1:"&$A8+J$1+1))-1)+NB.SI(J$2:J7;LIGNE(INDIRECT("1:"&$A8+J$1+1))-1));LIGNE(INDIRECT("1:"&$A8+J$1+1))-1))

Fichier (2).

Edit : j'utilise $A8+J$1 mais on peut aussi utiliser LIGNES(J$2:J7)+COLONNES($B8:I8)

A+

 

[job75]

Re : Premier entier manquant dans la suite des entiers naturels croissante

Re,

Je viens de tester les durées d'exécution des formules sur tout le tableau C3:S19.

Et là c'est tout le contraire de ce que je croyais :

- formule du post #9 (fonction VBA PEM) => 0,099 seconde

- formule matricielle du post #12 => 0,028 seconde.

La formule matricielle est donc 3,5 fois plus rapide.

Son seul inconvénient c'est qu'à cause de INDIRECT elle est volatile : toute modification du classeur entraîne le recalcul de tout le tableau.

A+

 

[job75]

Re : Premier entier manquant dans la suite des entiers naturels croissante

Bonjour le fil, le forum,

Pour que le dernier mot reste à VBA, il faut une macro bien plus compliquée qu'au post #9 :

Function PEM(P1, P2) 'Premier Entier Manquant
Dim ub1&, ub2&, t
'Application.Volatile 'uniquement pour tester la durée
ub1 = P1.Count: ub2 = P2.Count
If ub1 > 1 Then P1 = P1 'matrice plus rapide
If ub2 > 1 Then P2 = P2 'matrice plus rapide
For PEM = 0 To ub1 + ub2 + 1
  For Each t In P1
    If t = PEM Then GoTo 1
  Next
  For Each t In P2
    If t = PEM Then GoTo 1
  Next
  Exit Function
1 Next
End Function

Durée d'exécution 0,014 seconde (sur Win 7 - Excel 2010).

La fonction est donc 2 fois plus rapide que la formule matricielle du post #12.

Voir le fichier (2) joint et la macro qui permet de tester la durée.

A+

 

[Slyces]

Re : Premier entier manquant dans la suite des entiers naturels croissante

Encore merci