Formule pour identifier une rupture dans une suite de nombre

[stich02]

Bonjour à tous,

Je reviens vers vous car je n'arrive pas à trouver une formule qui me permette d'identifier une rupture dans une suite de nombres (sur deux colonnes).

Je vous joins un fichier qui éclairera (enfin je l'espère) ce que je viens de dire.

Merci d'avance.

Stich02

 

[Dranreb]

Re : Formule pour identifier une rupture dans une suite de nombre

Bonsoir.

Une petite fonction personnalisée peut être ?

Function ChiffresManquants(ByVal Plg2x2 As Range) As String
Dim T(), NbMq As Long, PremMq As Long, DernMq As Long, Mq As Long, Rés() As String
T = Plg2x2.Value
If T(1, 1) <> T(2, 1) Then Exit Function
PremMq = T(1, 2) + 1
DernMq = T(2, 2) - 1
If DernMq < PremMq Then Exit Function
ReDim Rés(PremMq To DernMq)
For Mq = PremMq To DernMq: Rés(Mq) = Mq: Next Mq
ChiffresManquants = Join(Rés, ", ")
End Function

À partir de C3, par exemple:

=ChiffresManquants(DECALER($A3;-1;0):$B3)

 

[JBARBE]

Re : Formule pour identifier une rupture dans une suite de nombre

Bonsoir à tous,

Peut-être ceci :

Option Explicit

Sub Essai()
Dim i As Long
Dim j As Long
For i = 3 To 65536
Cells(i, 2).Select
 If Cells(i, 2) = "" Then Exit Sub
 For j = 1 To 65536
 If ActiveCell <> ActiveCell.Offset(-1, 0) + 1 And ActiveCell.Interior.ColorIndex = ActiveCell.Offset(-1, 0).Interior.ColorIndex Then
   ActiveCell.Offset(0, 1) = "rupture"
 End If
 If ActiveCell + 1 = ActiveCell.Offset(1, 0) + 1 And ActiveCell.Interior.ColorIndex + 1 = ActiveCell.Offset(1, 0).Interior.ColorIndex Then
 Exit For
 Else
 Exit For
 End If
 Next j
Next i
End Sub

bonne soirée

 

[stich02]

Re : Formule pour identifier une rupture dans une suite de nombre

Bonsoir Dranreb,

Je te remercie de ta réponse qui répond parfaitement à mes attentes.
Une fois de plus je reste admiratif devant de ce que l'on peut réaliser quand on maîtrise (ô combien) le VBA et je crains malheureusement que ce ne soit jamais mon cas. Peut-être dans une autre vie...

Encore un grand merci.

Bonne soirée.

Jean-Marie

 

[stich02]

Re : Formule pour identifier une rupture dans une suite de nombre

Bonsoir JBARBE,

Merci également pour cette macro et le temps que tu as bien voulu consacrer à mon problème.

Bonne soirée

Jean-Marie

 

[JBARBE]

Re : Formule pour identifier une rupture dans une suite de nombre

Bonsoir JBARBE,

Merci également pour cette macro et le temps que tu as bien voulu consacrer à mon problème.

Bonne soirée

Jean-Marie

Petite modif ici mais je m'incline devant un tel professionnalisme de Dranreb

Option Explicit

Sub Essai()
Dim i As Long
Dim j As Long
For i = 3 To 65536
Cells(i, 2).Select
 If Cells(i, 2) = "" Then Exit Sub
 For j = 1 To 65536
 If ActiveCell <> ActiveCell.Offset(-1, 0) + 1 And ActiveCell.Interior.ColorIndex = ActiveCell.Offset(-1, 0).Interior.ColorIndex Then
   ActiveCell.Offset(0, 1) = ActiveCell.Offset(-1, 0) + 1
 End If
 If ActiveCell > ActiveCell.Offset(0, 1) + 1 And _
 ActiveCell.Offset(0, 1) <> "" And ActiveCell.Interior.ColorIndex = ActiveCell.Offset(-1, 0).Interior.ColorIndex Then
   ActiveCell.Offset(0, 1) = "rupture"
 End If
 If ActiveCell + 1 = ActiveCell.Offset(1, 0) + 1 And ActiveCell.Interior.ColorIndex + 1 = ActiveCell.Offset(1, 0).Interior.ColorIndex Then
 Exit For
 Else
 Exit For
 End If
 Next j
Next i
End Sub

 

[mapomme]

Re : Formule pour identifier une rupture dans une suite de nombre

Bonjour stich02, le forum,

Une tentative via formules. J'ai dû utiliser deux colonnes intermédiaires (C et D) car les formules matricielles dépassaient le nombre maximum de parenthèses imbriquées autorisé en Excel 2003.

Formules pour la min et max à chaque rupture (colonne C2 et D2 à copier vers le bas):
min:

=SI(A2=A3;"";MIN(DECALER(B2;1-NB.SI($A$2:A2;A2);0;NB.SI($A$2:A2;A2);1)))

max:

=SI(A2=A3;"";MAX(DECALER(B2;1-NB.SI($A$2:A2;A2);0;NB.SI($A$2:A2;A2);1)))

Formule matricielle en E2 à copier vers la droite puis vers le bas:

=SI(ESTERREUR(PETITE.VALEUR(SI(ESTERREUR(EQUIV(LIGNE(INDIRECT($C2 & ":" & $D2));DECALER($B2;1-NB.SI($A$2:$A2;$A2);0;NB.SI($A$2:A2;$A2);1);0));LIGNE(INDIRECT($C2 & ":" & $D2));"");COLONNES($E$1:E$1)));"";PETITE.VALEUR(SI(ESTERREUR(EQUIV(LIGNE(INDIRECT($C2 & ":" & $D2));DECALER($B2;1-NB.SI($A$2:$A2;$A2);0;NB.SI($A$2:A2;$A2);1);0));LIGNE(INDIRECT($C2 & ":" & $D2));"");COLONNES($E$1:E$1)))

On a aussi appliqué une Mise en Forme Conditionnelle au tableau pour séparer chaque bloc de valeurs:
la formule pour le vert:

=EST.IMPAIR(SOMME(1/NB.SI($A$2:$A2;$A$2:$A2)))

la formule pour l'orange:

=EST.PAIR(SOMME(1/NB.SI($A$2:$A2;$A$2:$A2)))

Formule matricielle: Elle doit être validée par la combinaison des touches Ctrl+Maj+Entrée au lieu de la seule touche Entrée comme une formule classique.
Si la validation matricielle est correcte, alors Excel entoure la formule d'accolades {=.......}.
Chaque fois que cette formule sera modifiée, la validation devra se faire par Ctrl+Maj+Entrée. Les accolades apparaissent à la validation et ne doivent pas être saisies au clavier.

Edit : joint le bon fichier.

 

[mapomme]

Re : Formule pour identifier une rupture dans une suite de nombre

Bonsoir le forum,

Je n'avais pas joint le bon fichier dans mon précédent message, l'erreur est rectifiée. Voir #7

 

[mapomme]

Re : Formule pour identifier une rupture dans une suite de nombre

Bonsoir le forum,

La même chose en excel 2010 (sans doute valable pour Excel 2007) sans colonne auxilliaire.

Formule matricielle en C2 à copier vers la droite et vers le bas:

=SI($A2<>$A3;SIERREUR(PETITE.VALEUR(SI(ESTERREUR(EQUIV(LIGNE(INDIRECT(MIN(DECALER($B2;1-NB.SI($A$2:$A2;$A2);0;NB.SI($A$2:$A2;$A2);1)) & ":" & MAX(DECALER($B2;1-NB.SI($A$2:$A2;$A2);0;NB.SI($A$2:$A2;$A2);1))));DECALER($B2;1-NB.SI($A$2:$A2;$A2);0;NB.SI($A$2:A2;$A2);1);0));LIGNE(INDIRECT(MIN(DECALER($B2;1-NB.SI($A$2:$A2;$A2);0;NB.SI($A$2:$A2;$A2);1)) & ":" &MAX(DECALER($B2;1-NB.SI($A$2:$A2;$A2);0;NB.SI($A$2:$A2;$A2);1))));"");COLONNES($C$1:C$1));"");"")

 

[stich02]

Re : Formule pour identifier une rupture dans une suite de nombre

Bonjour à tous,

Je voulais remercier mapomme pour toutes ses formules. Je les garde précieusement car même si pour ce fichier la formule matricielle plante excel (?) quand je l'intègre dans le fichier définitif je réutilise déjà les MFC et les MAX ET MIN dans d'autres fichiers et elles me sont bien utiles. Du reste j'essaierai de nouveau quand j'aurai excel 2010.

Encore merci pour ton dévouement.