Recherche sur ligne + sélection de case différente

Le 20/08/2018 à 17:40

Bonjour à tous,

Nouveau sur le forum je viens y poser ma première question

J'ai un fichier excel avec de nombreuses données agrégées etje souhaiterais pouvoir en extraire certaines sur une autre feuille.

Pour cela je dois parvenir à trouver "x" qui apparaît une fois ou plus sur chaque ligne, puis faire ressortir la données "y" liée à "x" qui se trouve 4 colonnes plus loin. Cependant "x" peut être présent une ou plusieurs fois par ligne, et souvent dans des colonnes différentes. Un peu comme ça :

__ A_B_C__D_E_ F_G_H_I__J__K_L_M_N

1 | * | * | * | x | * | * | * | y | * | * | * | * | * | *

2 | * | x | * | * | * | y | * | * | * | * | * | * | * | *

3 | * | * | * | * | * | * | x | * | * | * | y | * | * | *

4 | * | * | x | * | * | * | y | * | x | * | * | * | y | *

5 | * | * | * | * | * | x | * | * | * | y | * | * | * | *

De façon à arriver à cela:

1 | somme de la valeur de "y" de la ligne 1 = 12

2 | somme de la valeur de "y" de la ligne 2 = 34

3 | somme de la valeur de "y" de la ligne 3 = 8

4 | somme de la valeur de "y" de la ligne 4 = 16+12

5 | somme de la valeur de "y" de la ligne 5 = 11

Merci d'avance et bonne journée à toute la communauté

Le 20/08/2018 à 18:49
Troplent a écrit :

Bonjour à tous,

De façon à arriver à cela:

1 | somme de la valeur de "y" de la ligne 1 = 12

2 | somme de la valeur de "y" de la ligne 2 = 34

3 | somme de la valeur de "y" de la ligne 3 = 8

4 | somme de la valeur de "y" de la ligne 4 = 16+12

5 | somme de la valeur de "y" de la ligne 5 = 11

Merci d'avance et bonne journée à toute la communauté

Suivant quelle logique ?

Crdlmt

Le 20/08/2018 à 18:52

Bonjour,

merci de mettre un fichier exemple.

Le 21/08/2018 à 04:41

Bonjour Troplent,

je te propose ce fichier Excel :

10exo-troplent.xlsm (14.84 Ko)

note bien que P1:P5 est vide ; fais Ctrl e ➯ miracle ! j'ai les mêmes résultats que toi ! y'a pas à dire, les grands esprits se rencontrent, hein ? (bon, j'avoue, j'ai triché : j'ai lu dans tes pensées )

pour mettre les résultats sur une autre feuille, j'suis sûr que tu sauras adapter, pas vrai ? si, hein ? bien, ça, très bien !

faut bien qu'tu bosses un peu, après t'être reposé d'avoir fourni un fichier invisible, avec des x et y à deviner !

Alt F11 pour voir le code VBA, puis revenir sur Excel

si besoin, tu peux demander une adaptation.

merci de me dire si ça te convient.

dhany

Le 27/08/2018 à 17:11

Salut Dhany,

Tout d'abord merci beaucoup, et désolé pour le tableau d'exemple j'ai pensé faire bien en allant dans de l'abstrait :p.

Je vois qu'il s'agit donc de passer par les macros (moi qui pensait pouvoir y échapper ), je m'y mets de ce pas et je reviendrai vers vous si j'encontre des problèmes

Bien à vous,

Troplent

Le 27/08/2018 à 19:22

Bonjour,

Maintenant qu'il y a un fichier, on comprend la logique !

un essai ;

11exo-troplent.xlsm (13.72 Ko)

Crdlmt

Le 28/08/2018 à 04:45

Bonjour DjiDji59430,

je suis ravi que mon fichier t'a aidé à comprendre la logique de l'exercice ! comme tu as repris mon fichier et mon exemple numérique, je crois que tu aurais pu le mentionner, n'est-ce pas ?

au début, j'ai cru que tu n'avais pas vu qu'en exécutant ma macro, ça affiche les résultats en colonne P ; je me suis dit : « tiens, DjiDji a repris mon fichier juste pour mettre les résultats en colonne R ? »

puis j'ai vu ta formule matricielle en R1 ; ça, c'est très bien d'avoir proposé une solution par formule, bravo !

y'a juste un p'tit défaut : c'est par rapport à la valeur 10 que j'avais choisie pour x, et 10 est une constante !

j'avais choisi des nombres tout à fait au hasard, et si j'ai choisi 10 pour x c'est de façon purement arbitraire.

du demandeur.

si tu avais mieux regardé ma solution, tu aurais vu qu'elle utilise pour x la valeur de la cellule B7

➯ c'est une variable que l'utilisateur peut choisir à son gré.

comme tu as repris mon fichier, il te suffit juste de faire Alt F11 pour voir mon code VBA :

Option Explicit

Sub Essai()
  Dim total%, i As Byte, j As Byte
  For i = 1 To 5
    total = 0
    For j = 1 To 10
      If Cells(i, j) = [B7] Then total = total + Cells(i, j + 4)
    Next j
    Cells(i, 16) = total
  Next i
End Sub

note bien que le test est : If Cells(i, j) = [B7] Then

j'ai réécrit ainsi ta formule matricielle (sera à valider par Ctrl Maj Entrée) :

=SIERREUR(INDEX($A1:$N1;PETITE.VALEUR(SI($A1:$N1=B$7;COLONNE($A:$N));1)+4)+SIERREUR(INDEX($A1:$N1;PETITE.VALEUR(SI($A1:$N1=B$7;COLONNE($A:$N));2)+4);0);0)

j'ai remplacé les deux 10 par B$7 mais pas seulement ! car j'ai ajouté la fonction SIERREUR() pour éviter ceci :

screen 1

tu peux voir que c'est bien mieux comme ça :

screen 2

j'ai aussi supprimé ma sub Essai() pour en faire une procédure événementielle ; ainsi, quand on change la valeur x de B7, les résultats de la sub aussi sont modifiés, pas seulement ceux de la formule matricielle.

Option Explicit

Dim total%, i As Byte, j As Byte

Private Sub Worksheet_Change(ByVal Target As Range)
  With Target
    If .CountLarge > 1 Then Exit Sub
    If .Address <> "$B$7" Then Exit Sub
    For i = 1 To 5
      total = 0
      For j = 1 To 10
        If Cells(i, j) = .Value Then total = total + Cells(i, j + 4)
      Next j
      Cells(i, 16) = total
    Next i
  End With
End Sub

voici le nouveau fichier correspondant :

7exo-troplent-2.xlsm (15.34 Ko)

dhany

Le 28/08/2018 à 10:52

Re

dhany :

le fichier, je n'ai pas vu qu'il était de toi, excuse moi. Donc je travaillais sur un fichier réel, qui n'était pas pour moi un exemple, d'ou la valeur 10 dans la formule (je n'étais pas dans ton esprit !) et qui n'était pas supposé avoir du VBA.

Au départ, j'avais mis 2 sierreur(), puis je me suis dit (je ne sais pas pourquoi), en regardant le tableau, qu'il devait y avoir forcement une valeur, 2 au maximum. ==>un seul sierreur()

Et si je propose une solution en formule, c'est que je suis nul en VBA.

Je comprends ta macro, mais je n'aurais pas su l'écrire !

Ceci explique cela.

Crdlmt

Le 28/08/2018 à 10:59

merci pour tes infos ! je te souhaite bonne continuation pour les exos Excel !

dhany

Le 29/08/2018 à 13:51

Bonjour DjiDji, bonjour Dhany,

Merci beaucoup pour le temps passé sur cette matrice, qui je dois bien l'avoué m'est bien plus lisible que le VBA .

L'intégration de la variante B7 et de SIERREUR() que tu as réalisée Dhany me facilite bien la tâche, cependant un problème persiste.

En effet la matrice ne calcul que les deux premières occurrences de x par ligne.

Ainsi dans le tableur ci-joint, on devrait être à 56 en AD4 et non à 28

Avez-vous une idée pour résoudre ce problème sans rendre le tout trop compliqué (sachant qu'une occurrence de 10 x me paraît être suffisant) ?

4exo-troplent-3.xlsm (15.43 Ko)
Le 29/08/2018 à 14:01

Bonjour Troplent,

j'suis pas très doué avec les formules matricielles (j'préfère largement le VBA !).

aussi, j'préfère laisser à DjiDji ou un autre intervenant le soin de te répondre.

dhany

Le 29/08/2018 à 14:28

Pour vous éclairer un peu plus, je suis sur des tableaux de résultats d'élections comme celui joint avec ce commentaire.

Le problème c'est d’agréger automatiquement les voix de toutes les listes LEXG par exemple.

Si dans cet exemple il n'y a que deux LEXG et la matrice fonctionne très bien, il y a par contre de nombreux LDIV et seuls les deux premiers sont agréger.

J'ai bien essayer en adaptant ton VBA Dhany, mais la sélection totale semble trop grande pour fonctionner (6,7 millions de cellules).

7euro-2014-resultats-communes-c.xlsx (301.24 Ko)
Le 29/08/2018 à 14:42

Re

Encore une fois, si le problème posé avait pu l’être de façon exhaustive .....!

Solution pas compliquée :

13exo-troplent-3.xlsm (16.85 Ko)

Crdlmt

Rechercher des sujets similaires à "recherche ligne selection case differente"