Classeur Excel et Formule Complexe Lignes / Colonnes
Résolu
GTA5488
Messages postés
15
Date d'inscription
Statut
Membre
Dernière intervention
-
Raymond PENTIER Messages postés 58988 Date d'inscription Statut Contributeur Dernière intervention -
Raymond PENTIER Messages postés 58988 Date d'inscription Statut Contributeur Dernière intervention -
Bonjour à tous ! :)
Voilà je sèche un peu concernant une formule que je n'arrive pas à "étirer" étant donné qu'il y a des variables contenues dans des Lignes ... et dans des colonnes. C'est un vrai casse tête.
Je savais que j'allais en arriver là .. Mais je ne suis pas assez aguerri pour faire tout ça en Macro.
Pour comprendre, je vous joins mon Classeur via We transfer (je sais c'est un carnage) :
https://wetransfer.com/downloads/5b1bb9f85536f728a0b3c3d666e215f320210617124434/542081
Mon besoin :
Le premier onglet "A25080R30P0" est l'onglet où nous venons rentrer nos valeurs. La seule Formule qu'il y a est en colonne S, histoire d'avoir un aperçu de ce qui se passe plus tard.
Le deuxième onglet "A25081R30P0", c'est la même chose que le premier, avec des valeurs différentes.
Le troisième onglet c'est THE onglet "Appairage Total" ... c'est là que je fais toutes les opérations pour ressortir les infos des colonnes S dans les 2 premiers onglets.
Le But est simple, je veux appairer des pièces des 2 références en fonction de leur valeur de E1 et E2.
J'ai donc fait un tableau qui fait tous les calculs :
Cellule O2 : Je viens reprendre le numéro de la première pièce du tableau référence A25080R30P0
Cellule P2 : Je viens reprendre le numéro de la deuxième pièce du tableau référence A25080R30P0
Ainsi de suite .. pendant 500 lignes
Cellule O4 : Je viens reprendre le numéro de barre de la première pièce du tableau référence A25080R30P0
Cellule P4 : Je viens reprendre le numéro de barre de la deuxième pièce du tableau référence A25080R30P0
Ainsi de suite ... pendant 500 lignes
Cellule O5 :
- Différence entre E1 de la 1ère pièce référence A25080R30P0 et E2 de la première pièce référence A25081R30P0
- Différence entre E2 de la 1ère pièce référence A25080R30P0 et E1 de la première pièce référence A25081R30P0
- Si une de ces deux différences est supérieur à 0.005 on note NOK
- Si les deux différences sont inférieures ou égales à 0.005 on note 1
Cellule O6 :
- Différence entre E1 de la 1ère pièce référence A25080R30P0 et E2 de la deuxième pièce référence A25081R30P0
- Différence entre E2 de la 1ère pièce référence A25080R30P0 et E1 de la deuxième pièce référence A25081R30P0
- Si une de ces deux différences est supérieur à 0.005 on note NOK
- Si les deux différences sont inférieures ou égales à 0.005 on note 1
Ainsi de suite pendant 500 lignes ...
Jusque là je m'en sors .. Sauf que je ne sais pas quoi faire pour "étirer" cette formule pour les autres pièces (Cellules P5 à P515 / Cellules Q5 à Q515 ... et ainsi de suite)
Est-ce que quelqu'un a une astuce simple à mettre en place ? J'avoue que ça fait vraiment usine à gaz ...
L'objectif est de comparé chaque pièce VS chaque pièce et voir si elles sont appairables ou pas ... Ensuite je sors un classement des pièces en fonction du nombre avec lesquelles on peut les appairer .. Et j'appaire en partant du moins vers le plus ...
Je ne sais pas si c'est vraiment très clair. Mais j'espère que l'un d'entre vous pourra m'aider !
En attendant merci beaucoup pour le temps que vous avez pris de lire ... et bon après midi !
:)
Voilà je sèche un peu concernant une formule que je n'arrive pas à "étirer" étant donné qu'il y a des variables contenues dans des Lignes ... et dans des colonnes. C'est un vrai casse tête.
Je savais que j'allais en arriver là .. Mais je ne suis pas assez aguerri pour faire tout ça en Macro.
Pour comprendre, je vous joins mon Classeur via We transfer (je sais c'est un carnage) :
https://wetransfer.com/downloads/5b1bb9f85536f728a0b3c3d666e215f320210617124434/542081
Mon besoin :
Le premier onglet "A25080R30P0" est l'onglet où nous venons rentrer nos valeurs. La seule Formule qu'il y a est en colonne S, histoire d'avoir un aperçu de ce qui se passe plus tard.
Le deuxième onglet "A25081R30P0", c'est la même chose que le premier, avec des valeurs différentes.
Le troisième onglet c'est THE onglet "Appairage Total" ... c'est là que je fais toutes les opérations pour ressortir les infos des colonnes S dans les 2 premiers onglets.
Le But est simple, je veux appairer des pièces des 2 références en fonction de leur valeur de E1 et E2.
J'ai donc fait un tableau qui fait tous les calculs :
Cellule O2 : Je viens reprendre le numéro de la première pièce du tableau référence A25080R30P0
Cellule P2 : Je viens reprendre le numéro de la deuxième pièce du tableau référence A25080R30P0
Ainsi de suite .. pendant 500 lignes
Cellule O4 : Je viens reprendre le numéro de barre de la première pièce du tableau référence A25080R30P0
Cellule P4 : Je viens reprendre le numéro de barre de la deuxième pièce du tableau référence A25080R30P0
Ainsi de suite ... pendant 500 lignes
Cellule O5 :
- Différence entre E1 de la 1ère pièce référence A25080R30P0 et E2 de la première pièce référence A25081R30P0
- Différence entre E2 de la 1ère pièce référence A25080R30P0 et E1 de la première pièce référence A25081R30P0
- Si une de ces deux différences est supérieur à 0.005 on note NOK
- Si les deux différences sont inférieures ou égales à 0.005 on note 1
Cellule O6 :
- Différence entre E1 de la 1ère pièce référence A25080R30P0 et E2 de la deuxième pièce référence A25081R30P0
- Différence entre E2 de la 1ère pièce référence A25080R30P0 et E1 de la deuxième pièce référence A25081R30P0
- Si une de ces deux différences est supérieur à 0.005 on note NOK
- Si les deux différences sont inférieures ou égales à 0.005 on note 1
Ainsi de suite pendant 500 lignes ...
Jusque là je m'en sors .. Sauf que je ne sais pas quoi faire pour "étirer" cette formule pour les autres pièces (Cellules P5 à P515 / Cellules Q5 à Q515 ... et ainsi de suite)
Est-ce que quelqu'un a une astuce simple à mettre en place ? J'avoue que ça fait vraiment usine à gaz ...
L'objectif est de comparé chaque pièce VS chaque pièce et voir si elles sont appairables ou pas ... Ensuite je sors un classement des pièces en fonction du nombre avec lesquelles on peut les appairer .. Et j'appaire en partant du moins vers le plus ...
Je ne sais pas si c'est vraiment très clair. Mais j'espère que l'un d'entre vous pourra m'aider !
En attendant merci beaucoup pour le temps que vous avez pris de lire ... et bon après midi !
:)
A voir également:
- Classeur Excel et Formule Complexe Lignes / Colonnes
- Formule moyenne excel plusieurs colonnes - Guide
- Formule si et excel - Guide
- Formule somme excel colonne - Guide
- Word et excel gratuit - Guide
- Classer par ordre alphabétique excel plusieurs colonnes - Guide
9 réponses
Bonjour GTA.
Ma suggestion :
J'espère que ça te conviendra ...
Ma suggestion :
- Ecrire "Barre N°" en N4, "Ligne" en N3, et la série 5, 6, 7 ... en O3, O4, O5 ...
- Saisir en O5 la formule =SI(ET($H:$H="";$A:$A="");"";SI(OU(ABS(INDIRECT("$C"&$3:$3)-$K5)>0,005;ABS(INDIRECT("$D"&$3:$3)-$J5)>0,005);"NOK";1)) à recopier en bas et à droite.
J'espère que ça te conviendra ...
Bonjour Raymond,
Merci pour ta réponse rapide !
Je viens d'essayer ta formule mais la cellule m'indique une erreur ...
Je ne vois pas d'où cela peut venir, la formule à l'air de bien s'incrémenter.
Merci pour ta réponse rapide !
Je viens d'essayer ta formule mais la cellule m'indique une erreur ...
Je ne vois pas d'où cela peut venir, la formule à l'air de bien s'incrémenter.
Difficile, pour ne pas dire impossible, de découvrir une erreur de formule sur une simple image.
Apparemment ta formule est bien identique à la mienne ; c'est donc dans le format des données qu'il y a un loup ...
Alors soit tu envoies ton fichier Excel, sois tu essaies d'appliquer les conseils de mon petit tuto
https://www.cjoint.com/c/KFvqWsp8fhV
Apparemment ta formule est bien identique à la mienne ; c'est donc dans le format des données qu'il y a un loup ...
Alors soit tu envoies ton fichier Excel, sois tu essaies d'appliquer les conseils de mon petit tuto
https://www.cjoint.com/c/KFvqWsp8fhV
Bonjour Raymond :)
Le fichier est le même que celui de mon premier post (lien wetransfer), j'ai juste recopié ta formule et ajouté les données que tu m'avais demandé. :)
https://wetransfer.com/downloads/5b1bb9f85536f728a0b3c3d666e215f320210617124434/542081
Je vais aller jeter un oeil sur ton tuto en attendant.
Merci. Bonne journée.
Le fichier est le même que celui de mon premier post (lien wetransfer), j'ai juste recopié ta formule et ajouté les données que tu m'avais demandé. :)
https://wetransfer.com/downloads/5b1bb9f85536f728a0b3c3d666e215f320210617124434/542081
Je vais aller jeter un oeil sur ton tuto en attendant.
Merci. Bonne journée.
Raymond,
Tu trouveras le fichier MAJ avec ce nouveau lien :
https://we.tl/t-DT1b1cOovJ
J'ai apporté quelques modifications au fichier. Par contre pour le calcul et ta formule, je ne comprends pas. J'ai vraiment du mal à comprendre comment fonctionne la fonction INDIRECT. J'ai même essayé de faire l'exemple donné par Microsoft dans un classeur vierge mais certains cas de figures ne fonctionnent pas ... Est-ce qu'il peut y avoir un problème de version d'Excel ?
Lors de ton premier essai, tu as ouvert mon lien et utiliser mon fichier directement ?
Merci en tout cas.
Bonne journée.
Tu trouveras le fichier MAJ avec ce nouveau lien :
https://we.tl/t-DT1b1cOovJ
J'ai apporté quelques modifications au fichier. Par contre pour le calcul et ta formule, je ne comprends pas. J'ai vraiment du mal à comprendre comment fonctionne la fonction INDIRECT. J'ai même essayé de faire l'exemple donné par Microsoft dans un classeur vierge mais certains cas de figures ne fonctionnent pas ... Est-ce qu'il peut y avoir un problème de version d'Excel ?
Lors de ton premier essai, tu as ouvert mon lien et utiliser mon fichier directement ?
Merci en tout cas.
Bonne journée.
- Je te redis que nous avons la même version ...
- Oui, j'avais travaillé directement sur ton fichier.
- Ce fichier MAJ est différent : il y a une colonne de plus (inutilisée, d'ailleurs)et tu as créé, j'ignore pour quelle raison, une formule matricielle ... Le format du fichier est passé en xlsm !
Mais si tu enlèves ces foutues accolades qui ont transformé ma formule normale en une formule matricielle, tout marchera parfaitement.
Bonjour Raymond,
Tu me demandes de refaire la même manip que j'ai faite lors de ma première réponse et où ça ne marchait déjà pas.
Donc en attendant j'essaie d'autres choses, j'essaie de comprendre la fonction INDIRECT, j'essaie de comprendre ta logique avec les infos que tu veux bien me donner. D'où le dernier fichier WeTransfer et les modifications. J'essaie juste d'essayer de résoudre ce qui ne fonctionne pas.
Mais tu as l'air sûr de toi donc :
- j'ai retéléchargé le premier fichier que j'ai envoyé sur le post de base,
- j'ai remplacé la ligne "Barre N°" par une suite de chiffre (5,6, 7 etc) dans les cellules O3, P3, Q3 ... (tu avais mis O3, O4, O5 dans ta réponse)
- j'ai copié/collé la formule que tu m'as donné en O5,
Comme tu peux voir dans le lien ci dessous, ça ne fonctionne pas. J'ai toujours ce #REF! comme résultat malheureusement.
https://wetransfer.com/downloads/3738150fea6184d8cff94f3a7da6445c20210623065551/250e99
Je n'arrive pas à comprendre comment la machine peut déduire que la partie INDIRECT("$C"&$3:$3) fait référence à la cellule "C5" en fait ...
Merci pour ton temps.
Je te souhaite une bonne journée.
GT
Tu me demandes de refaire la même manip que j'ai faite lors de ma première réponse et où ça ne marchait déjà pas.
Donc en attendant j'essaie d'autres choses, j'essaie de comprendre la fonction INDIRECT, j'essaie de comprendre ta logique avec les infos que tu veux bien me donner. D'où le dernier fichier WeTransfer et les modifications. J'essaie juste d'essayer de résoudre ce qui ne fonctionne pas.
Mais tu as l'air sûr de toi donc :
- j'ai retéléchargé le premier fichier que j'ai envoyé sur le post de base,
- j'ai remplacé la ligne "Barre N°" par une suite de chiffre (5,6, 7 etc) dans les cellules O3, P3, Q3 ... (tu avais mis O3, O4, O5 dans ta réponse)
- j'ai copié/collé la formule que tu m'as donné en O5,
Comme tu peux voir dans le lien ci dessous, ça ne fonctionne pas. J'ai toujours ce #REF! comme résultat malheureusement.
https://wetransfer.com/downloads/3738150fea6184d8cff94f3a7da6445c20210623065551/250e99
Je n'arrive pas à comprendre comment la machine peut déduire que la partie INDIRECT("$C"&$3:$3) fait référence à la cellule "C5" en fait ...
Merci pour ton temps.
Je te souhaite une bonne journée.
GT
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
GT.
Je veux bien te consacrer un peu de mon temps, mais pas le gaspiller ...
Je veux bien te consacrer un peu de mon temps, mais pas le gaspiller ...
- Oui, j'ai indiqué dans mon message O3, O4, O5 ... mais l'illustration montrait bien qu'il s'agissait de O3, P3, Q3 ...
- Tu as apporté une modification très importante, et fatale, à ma formule, en remplaçant des références absolues
ET($H:$H="";$A:$A="")
par des références relativesET(H5="";A5="")
, ce qui fausse les recopies à droite.
Raymond,
Je crois qu'il y a une erreur de de ma part sur le dernier lien effectivement. Ce n'était pas le bon fichier.
Je l'ai enregistré maintenant sous ton nom pour être sûr que nous avons le même. Il est ici : https://wetransfer.com/downloads/60ce2a6e85035697145572599613e93420210623135509/595375
Je crois qu'il y a une erreur de de ma part sur le dernier lien effectivement. Ce n'était pas le bon fichier.
Je l'ai enregistré maintenant sous ton nom pour être sûr que nous avons le même. Il est ici : https://wetransfer.com/downloads/60ce2a6e85035697145572599613e93420210623135509/595375
Ben ... moi non plus !
Je vais essayer de trouver un autre PC (hors boulot) avec Excel pour tester la manip.
En attendant, est ce que tu peux m’envoyer le fichier que toi tu as modifié et où cela fonctionnait ? De façon à voir si en l’ouvrant j’ai le même problème ou pas.
C’est quand même étrange.
J’ai également essayé de faire ce petit tuto : https://support.microsoft.com/fr-fr/office/fonction-indirect-474b3a3a-8a26-4f44-b491-92b6306fa261
Testé sur un classeur Excel vierge au boulot, le =indirect(A4) ne fonctionne pas … alors que les trois autres cas fonctionnent …
En attendant, est ce que tu peux m’envoyer le fichier que toi tu as modifié et où cela fonctionnait ? De façon à voir si en l’ouvrant j’ai le même problème ou pas.
C’est quand même étrange.
J’ai également essayé de faire ce petit tuto : https://support.microsoft.com/fr-fr/office/fonction-indirect-474b3a3a-8a26-4f44-b491-92b6306fa261
Testé sur un classeur Excel vierge au boulot, le =indirect(A4) ne fonctionne pas … alors que les trois autres cas fonctionnent …
Bonjour Raymond,
Ca à l'air de fonctionner ! Par contre en ouvrant ton fichier et sans rien toucher je n'ai pas la même formule que toi :
Je ne sais pas pourquoi des "@" sont venus se glisser ici ... Mais la formule à l'air de fonctionner !
Je pense qu'il y a un souci de version d'Excel et que c'est à cause de ça que nous avions des différences de résultats.
En tout cas, je n'ai pas compris la formule ... Mais ça fonctionne ! Ahaha
Un grand merci à toi pour ta patience ;)
Bonne journée.
Gautier
Ca à l'air de fonctionner ! Par contre en ouvrant ton fichier et sans rien toucher je n'ai pas la même formule que toi :
Je ne sais pas pourquoi des "@" sont venus se glisser ici ... Mais la formule à l'air de fonctionner !
Je pense qu'il y a un souci de version d'Excel et que c'est à cause de ça que nous avions des différences de résultats.
En tout cas, je n'ai pas compris la formule ... Mais ça fonctionne ! Ahaha
Un grand merci à toi pour ta patience ;)
Bonne journée.
Gautier
Salut Gautier.
Finalement nous n'avons peut-être pas vraiment la même version : Le fait que des
> Il m'est impossible de le vérifier, car je n'ai plus accès à tes liens sur wetransfer.com dont la durée de vie est beaucoup trop courte ...
En O5, la fonction INDIRECT("C"&3:3) donne comme résultat le texte C5,
car c'est la valeur 5 qui est dans la ligne 3:3 de la colonne en cours (O).
Comme la formule sera recopiée, il faut "bloquer" certaines données pour qu'elles ne changent pas lors de la recopie ; en effet en copiant O5 en P6, ce début de formule s'adapterait automatiquement en INDIRECT("D"&4:4) ; c'est pourquoi il faut saisir INDIRECT("$C"&$3:$3).
ABS(INDIRECT("$C"&$3:$3)-$K5) calcule l'écart (en valeur absolue) entre INDIRECT("$C"&$3:$3) et K5.
ABS(INDIRECT("$D"&$3:$3)-$J5) calcule l'écart (en valeur absolue) entre INDIRECT("$D"&$3:$3) et J5.
La fonction OU entraîne l'affichage de "NOK" si l'un de ces 2 écarts n'est pas nul, et 1 si les 2 écarts sont nuls.
Finalement nous n'avons peut-être pas vraiment la même version : Le fait que des
@apparaissent dans tes formules semble montrer que tu utilises une version en ligne d'Excel.
> Il m'est impossible de le vérifier, car je n'ai plus accès à tes liens sur wetransfer.com dont la durée de vie est beaucoup trop courte ...
En O5, la fonction INDIRECT("C"&3:3) donne comme résultat le texte C5,
car c'est la valeur 5 qui est dans la ligne 3:3 de la colonne en cours (O).
Comme la formule sera recopiée, il faut "bloquer" certaines données pour qu'elles ne changent pas lors de la recopie ; en effet en copiant O5 en P6, ce début de formule s'adapterait automatiquement en INDIRECT("D"&4:4) ; c'est pourquoi il faut saisir INDIRECT("$C"&$3:$3).
ABS(INDIRECT("$C"&$3:$3)-$K5) calcule l'écart (en valeur absolue) entre INDIRECT("$C"&$3:$3) et K5.
ABS(INDIRECT("$D"&$3:$3)-$J5) calcule l'écart (en valeur absolue) entre INDIRECT("$D"&$3:$3) et J5.
La fonction OU entraîne l'affichage de "NOK" si l'un de ces 2 écarts n'est pas nul, et 1 si les 2 écarts sont nuls.