Recherche toutes les valeurs <=0,02

[jbf]

Bonjour,

J'ai créé un distancier entre environ 2000 coordonnées géographiques.

J'ai donc un tableau Excel avec 2000 lignes par 2000 colonnes.

Dans ce distancier, j'ai 50 distances inférieures à 0,02 km. J'aimerais que les 50 distances <=0,02 km se mettent les unes à la suite des autres dans un nouveau tableau dans la colonne A. En colonne B je voudrais obtenir les coordo de la colonne A du précédent tableau. En colonne C les coordo de la ligne 2 du précédent tableau.

L'exemple joint sera beaucoup plus parlant.

Merci pour votre aide.

JBF

 

[Grand Chaman Excel]

Re : Recherche toutes les valeurs <=0,02

Bonjour JBF,

Voici une macro fait en vitesse qui devrait fonctionner.
Reste à changer la dimension du tablo. Je n'ai aucune idée du temps que la macro va prendre pour rouler sur 2000 lignes x 2000 colonnes, mais ça risque de prendre plusieurs secondes...

Sub Distancier()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim c As Range, rg As Range
    Dim tablo() As Variant
    
    Set ws1 = ThisWorkbook.Sheets(1)        'à adapter, onglet du distancier
    Set ws2 = ThisWorkbook.Sheets(2)        'à adapter, onglet pour le nouveau tableau
    
    Set rg = ws1.Range("B3").Resize(5, 5)   'à adapter selon dimensions du dstancier (2000 x 2000)
    
    ReDim tablo(1 To 3, 1 To 1)
    tablo(1, 1) = "Distances"
    tablo(2, 1) = "Coordo Colonne A"
    tablo(3, 1) = "Coordo Ligne 2"
    
    For Each c In rg
        If c > 0 And c <= 0.02 Then
            ReDim Preserve tablo(1 To 3, 1 To UBound(tablo, 2) + 1)
            tablo(1, UBound(tablo, 2)) = c
            tablo(2, UBound(tablo, 2)) = ws1.Cells(c.Row, 1)
            tablo(3, UBound(tablo, 2)) = ws1.Cells(2, c.Column)
            
        End If
    Next
    
    ws2.Range("A1").Resize(UBound(tablo, 2), 3) = Application.Transpose(tablo)

End Sub

A+

 

[jbf]

Re : Recherche toutes les valeurs <=0,02

Bonsoir et merci pour la réponse.

J'ai oublié de préciser que je cherche une solution avec une formule et sans VBA.

 

[Monique]

Re : Recherche toutes les valeurs <=0,02

Bonjour,

Un essai
Formules matricielles, à valider par ctrl, maj et entrée.
Le temps de recalcul va être long

 

[jbf]

Re : Recherche toutes les valeurs <=0,02

Bonjour Monique et merci pour ces formules.

J'aurais besoin, si possible, d'avoir qq précisions.

1°/ J'ai ajouté en D6 une valeur de "0,01". En B12 ça renvoi la bonne coordonnée (A6) par contre en C12 ça renvoi une erreur. La coordonnée affichée correspond à F2 alors qu'elle devrait être D2.

Par contre, si en D6 vous indiquez "0,016" : tous les résultats en B10:C13 sont corrects.

Sauriez-vous m'expliquer pourquoi?


2°/ J'ai essayé de dupliquer votre exemple en collant deux tableaux similaires à partir de H2. Vous constaterez que vos formules semblent correctement fonctionner en H10:H13 et en I10:I13.

En revanche, je ne comprends pas l'erreur #REF! que j'obtiens J10:J13. Pouvez-vous m'éclairer?

Merci pour votre aide.

 

[Monique]

Re : Recherche toutes les valeurs <=0,02

Bonjour,

J’y étais quand j’ai vu ton message.

La formule pour la ligne et celle pour la colonne étaient indépendantes.
Cette fois-ci, elles sont toujours indépendantes mais…
la formule cherche la position, dans les lignes puis dans les colonnes, des 0,01 ex aequo départagés.

Pour départager les ex aequo :
PETITE.VALEUR(SI(Tablo>0;Tablo+LIGNE(Tablo)/11^11+COLONNE(Tablo)/9^9);1puis 2 puis 3))

Au choix : avec ou sans plage intermédiaire de calculs.

Tu as un format conditionnel dans le tableau, les couleurs des 3 premières valeurs correspondent aux couleurs de la colonne A

J’ai modifié les coordonnées, au lieu de références à 17 caractères, tu as I, II, III, IV, V
(c’est quand même plus facile pour vérifier les résultats)

Ton 2ème fichier
En J10 de ton 2ème fichier : Index(A2:M2; et non H2:M2
Cette partie
PETITE.VALEUR(SI(Tablo2=$H10;COLONNE(Tablo2));NB.SI($H$10:$H10;$H10)))
renvoie un n° de colonne de la feuille Excel et non du tableau

 

[jbf]

Re : Recherche toutes les valeurs <=0,02

Aille Aille Aille, le niveau est trop élevé pour moi!

Je n'arrive pas à comprendre tes formules. Pourquoi en B19 tu indiques toujours dans ta formule <LIGNES(D$11😀11) ?

Ca ne devrait pas plutôt être <LIGNES(B$19B:19) et en B20 : <LIGNES(B$19B:20)?

Tu as d'ailleurs fait comme je viens de le décrire en C19:C24. Est-ce une erreur de copié collé en B19:B24 ou une feinte que je ne comprends pas?

 

[Monique]

Re : Recherche toutes les valeurs <=0,02

Re,

C'est un copié-collé mal fini.

J'ai copié la partie "Petite.Valeur" de laformule D11 et je l'ai collée à l'intérieur de la formule en B19.
Et je n'ai pas changé les références.

En C19, c'est le même copié collé d'une partie de formule dans une autre
mais cette fois, j'ai modifié les références.

Tu as l'œil, dis donc !

 

[jbf]

Re : Recherche toutes les valeurs <=0,02

Je ne sais pas si j'ai l’œil, j'ai juste essayer de comprendre la formule! Je préfère que ce soit une erreur de copié collé car sinon je ne comprenais plus trop...

Je suis entrain d'essayer de transposer l'exemple que tu m'as fait sur mon cas précis.

Je reviendrais vers toi tout à l'heure pour te dire si ça fonctionne.

En attendant peux tu m'indiquer l'utilité de mettre des 9^9 et des 11^11 dans tes formules. Je vois passer ce genre de chose dans certains formules mais sans jamais savoir à quoi ça sert.

Si tu veux bien prendre encore qq minutes pour m'expliquer ça serait sympa.

Merci.

 

[Monique]

Re : Recherche toutes les valeurs <=0,02

Re,

Ligne(Tablo)/11^11, c’est une toute petite minuscule valeur ajoutée à toutes les valeurs du tablo
Colonne(Tablo)/9^9, c’est une autre toute petite minus valeur

Chaque valeur du tableau est augmentée de ces deux petites valeurs
Les ex aequo de la même ligne ne peuvent pas être dans la même colonne.
Il n’y a donc plus d’ex aequo (dans la formule, en tout cas)

Our mieux voir : tu saisis des 1 ou des 0,01 de B5 à F29
En B31, tu tapes =B25+LIGNE(B25)/11^11+COLONNE(B25)/9^9
Tu mets un format à 13 ou 14 décimales
Tu copies vers la droite et le bas
et tu regardes ce que ça donne

 

[jbf]

Re : Recherche toutes les valeurs <=0,02

Merci pour les précisions.

Bon j'ai un (gros) problème avec Excel pour adapter tes formules sur mon cas précis. Excel n'arrive pas à calculer faute de ressource. Tout ça pour ça!

<<Excel a manqué de ressources lors de la tentative de calcul d'une ou plusieurs formules. Ces formules n'ont donc pas pu être évaluées.>>

A part changer de portable, j'ai quoi comme solution?

 

[Monique]

Re : Recherche toutes les valeurs <=0,02

Re,

Il faudrait enlever les formats conditionnels, supprimer les colonnes inutiles à droite, les lignes inutiles en bas (même si elles ont l'air vide), etc.

Nommer de façon dynamique les 3 plages : colonne A, ligne2 et tableau (tu as des formules dans le fichier joint)

Les formules matricielles et c'est là le problème :
on peut les nommer pour gagner de la vitesse (elles ne seront à valider que par "Entrée").
J'ai nommé les formules dans seulement une vingtaine de cellules, le fichier a perdu du poids.
Il a sûrement gagné en vitesse, mais je ne vois rien, il est trop petit.

Pour donner un nom à une formule,
tu la copies dans la barre de formule,
tu vas dans Insertion - Nom - Définir,
dans la zone du haut, tu saisis le nom choisi pour la formule,
dans la zone du bas, tu colles la formule et tu cliques sur"Ajouter".
(c'est un peu différent dans Excel 2007).

Ensuite, il reste la macro qui devrait être plus rapide.
2000 lignes sur 2000 colonnes, c'est énorme pour des formules matricielles.

 

[jbf]

Re : Recherche toutes les valeurs <=0,02

Bonjour,

Supprimer les lignes/colonnes vides : je sélectionne toutes les lignes/colonnes vide, clic droit, supprimer puis j'enregistre mon fichier? Ou bien existe-t-il une solution pour réellement supprimer physiquement toutes les lignes/colonnes vides?

Nommer les cellules: je vais appliquer tes conseils mais j'ai tout de même un doute dans le sens où mon distancier contient cette formule dans chaque cellule pour calculer la distance à vol d'oiseau entre chaque coordonnée géographique :

SI(ESTERREUR(ACOS(SIN(RADIANS(GAUCHE($A13;TROUVE(";";$A13)-1)))*SIN(RADIANS(GAUCHE(B$2;TROUVE(";";B$2)-1)))+COS(RADIANS(GAUCHE($A13;TROUVE(";";$A13)-1)))*COS(RADIANS(GAUCHE(B$2;TROUVE(";";B$2)-1)))*COS(RADIANS((STXT($A13;TROUVE(";";$A13)+1;10))-(STXT(B$2;TROUVE(";";B$2)+1;10)))))*6371);"0";ARRONDI(ACOS(SIN(RADIANS(GAUCHE($A13;TROUVE(";";$A13)-1)))*SIN(RADIANS(GAUCHE(B$2;TROUVE(";";B$2)-1)))+COS(RADIANS(GAUCHE($A13;TROUVE(";";$A13)-1)))*COS(RADIANS(GAUCHE(B$2;TROUVE(";";B$2)-1)))*COS(RADIANS((STXT($A13;TROUVE(";";$A13)+1;10))-(STXT(B$2;TROUVE(";";B$2)+1;10)))))*6371;2))

Cette formule s'applique donc 4.000.000 de fois puisque j'ai 2000 lignes par 2000 colonnes.

Le distancier est sur la Feuille 1 et le tableau qui recense toutes les coordonnées <=0,02 sur la feuille 2. Il faudrait peut être que je créé deux fichiers pour tenter de soulager Excel.

 

[jbf]

Re : Recherche toutes les valeurs <=0,02

Re,

Je suis désolé mais je n'arrive plus à suivre les nouvelles formules que tu as nommé dans le dernier fichier joint (DistancierJbfV2.xls).

Ce qui me pose précisément problème ce sont les formules "longues" sur la Feuille1. Pour ce qui est des 3 formules pour l'onglet "Tablo" je n'ai pas eu de problèmes.

En revanche, pour les formules de l'onglet "Feuille1" je suis dessus depuis ce matin et je dois dire que je suis complètement paumé.

Le texte des formules que tu as affiché à côté des appellations des formules ne correspond pas aux textes que je retrouve dans les formules dans le gestionnaire des noms ni dans le premier fichier que tu avais appelé DistancierJbfv1.xls. Les cellules de références ne sont pas non plus les mêmes et font référence à des cellules vides. Vu que mon niveau n'est pas à la hauteur du tiens, je rame complétement pour essayer de comprendre qu'elle est la bonne formule à appliquer.

Par exemple:

Pour la formule FoDistanceLongue :

• Sur Feuille1 DistancierJbfV2.xls -> SI(SOMME((Tablo<=0,02)*(Tablo>0))<LIGNES(Feuil1!F$15:F20);"";PETITE.VALEUR(SI(Tablo>0;Tablo);LIGNES(Feuil1!F$15:F20)))
  
  
• Dans le gestionnaire des noms DistancierJbfV2.xls -> SI(SOMME((Tablo<=0,02)*(Tablo>0))<LIGNES(Feuil1!C$15:C32);"";PETITE.VALEUR(SI(Tablo>0;Tablo);LIGNES(Feuil1!C$15:C32)))
  
  
• Sur Feuille1 du DistancierJbfV1.xls -> SI(SOMME((Tablo<=0,02)*(Tablo>0))<LIGNES(B$19:B19);"";INDEX($A$1:$A$7;MIN(SI(Tablo+LIGNE(Tablo)/11^11+COLONNE(Tablo)/9^9=PETITE.VALEUR(SI(Tablo>0;Tablo+LIGNE(Tablo)/11^11+COLONNE(Tablo)/9^9);LIGNES(B$19:B19));LIGNE(Tablo)))))

Je suis paumé car les cellules de référence des deux premières formules font référence à des cellules vides mais différentes d'une formule à l'autre. La formule étant encore différente dans le DistancierJbfV1.xls que du coup je ne sais plus du tout ce que je dois prendre comme cellules de référence.

Est ce qu'on pourrait repartir d'un exemple simple où sur la Feuille1 :
A1 = Distances, B1=CoordoColA et C1=CoordoLigneDeux
La première ligne incrémentée d'une formule serait donc la ligne A2:C2 jusqu'à A60:C60.

Merci de ton aide

 

[jbf]

Re : Recherche toutes les valeurs <=0,02

Bonjour Monique,

Je retente ma chance pour te demander à nouveau de l'aide...