Formule Excel juste qui une fois étirée devient fausse

Le 17/06/2023 à 15:55

Bonjour à tous,

Je ne comprends pas j'ai créé des formules qui sont justes car en mettant différents codes pour tester, j'obtiens bien les noms désirés, mais lorsque j'étire la formule dans les cellules en dessous j'obtiens des messages d'erreur. J'ai vérifier dans "Options" --> "Formules" et "Automatique" est bien cliqué.

J'ai fait le test de copier la formule qui fonctionne et de la coller dans un bloc note et avec ctrl + H j'ai changé automatique toutes les références de recherche "J22" par "J23", puis j'ai copier la formule que j'ai coller dans la case concernée et ça fonctionne. Je ne comprends rien ?????

Voici un exemple d'une de mes formules qui foncitonne : =RECHERCHEX(J22;Algues!$E$5:$E$24;Algues!$D$5:$D$24;RECHERCHEX(J22;'Allergies al.'!$E$137:$E$149;'Allergies al.'!$D$137:$D$149;RECHERCHEX(J22;'Auto-immunité'!$E$108:$E$152;'Auto-immunité'!$D$108:$D$152;RECHERCHEX(J22;Cancer!$E$52:$E$135;Cancer!$D$52:$D$135;RECHERCHEX(J22;Candida!$E$35:$E$96;Candida!$D$35:$D$96;RECHERCHEX(J22;Cerveau!$E$148:$E$397;Cerveau!$D$148:$D$397;RECHERCHEX(J22;Coeur!$E$97:$E$216;Coeur!$D$97:$D$216;RECHERCHEX(J22;Dentaire!$E$159:$E$231;Dentaire!$D$159:$D$231;RECHERCHEX(J22;Digestion!$E$117:$E$299;Digestion!$D$117:$D$299;RECHERCHEX(J22;Foie!$E$182:$E$330;Foie!$D$182:$D$330;RECHERCHEX(J22;Gluten!$E$45:$E$87;Gluten!$D$45:$D$87;RECHERCHEX(J22;Glycémie!$E$46:$E$163;Glycémie!$D$46:$D$163;RECHERCHEX(J22;'GI et Poumons'!$E$59:$E$199;'GI et Poumons'!$D$59:$D$199;RECHERCHEX(J22;Hor.F!$E$72:$E$166;Hor.F!$D$72:$D$166;RECHERCHEX(J22;Hor.M!$E$92:$E$183;Hor.M!$D$92:$D$183;RECHERCHEX(J22;Immunité!$E$127:$E$202;Immunité!$D$127:$D$202;RECHERCHEX(J22;Infections!$E$159:$E$294;Infections!$D$159:$D$294;RECHERCHEX(J22;Inflammation!$E$114:$E$203;Inflammation!$D$114:$D$203;RECHERCHEX(J22;Joker!$E$66:$E$140;Joker!$D$66:$D$140;RECHERCHEX(J22;Lyme!$E$117:$E$255;Lyme!$D$117:$D$255;RECHERCHEX(J22;Métaux!$E$35:$E$58;Métaux!$D$35:$D$58;RECHERCHEX(J22;Méthylation!$E$133:$E$216;Méthylation!$D$133:$D$216;RECHERCHEX(J22;Microbiome!$E$115:$E$142;Microbiome!$D$115:$D$142;RECHERCHEX(J22;Minéraux!$E$60:$E$89;Minéraux!$D$60:$D$89;RECHERCHEX(J22;Mitochondries!$E$40:$E100;Mitochondries!$D$40:$D$100;RECHERCHEX(J22;Reins!$E$58:$E$88;Reins!$D$58:$D$88;RECHERCHEX(J22;Surrénales!$E$31:$E$59;Surrénales!$D$31:$D$59;RECHERCHEX(J22;Télomères!$E$5:$E$34;Télomères!$D$5:$D$34;RECHERCHEX(J22;Thyroïde!$E$73:$E$107;Thyroïde!$D$73:$D$107;RECHERCHEX(J22;Toxines!$E$44:$E$69;Toxines!$D$44:$D$69;RECHERCHEX(J22;Vitamines!$E$47:$E$76;Vitamines!$D$47:$D$76)))))))))))))))))))))))))))))))

Et voici un exemple de la formule étirée qui me donne le message "#VALEUR!" : =RECHERCHEX(J23;Algues!$E$5:$E$24;Algues!$D$5:$D$24;RECHERCHEX(J23;'Allergies al.'!$E$137:$E$149;'Allergies al.'!$D$137:$D$149;RECHERCHEX(J23;'Auto-immunité'!$E$108:$E$152;'Auto-immunité'!$D$108:$D$152;RECHERCHEX(J23;Cancer!$E$52:$E$135;Cancer!$D$52:$D$135;RECHERCHEX(J23;Candida!$E$35:$E$96;Candida!$D$35:$D$96;RECHERCHEX(J23;Cerveau!$E$148:$E$397;Cerveau!$D$148:$D$397;RECHERCHEX(J23;Coeur!$E$97:$E$216;Coeur!$D$97:$D$216;RECHERCHEX(J23;Dentaire!$E$159:$E$231;Dentaire!$D$159:$D$231;RECHERCHEX(J23;Digestion!$E$117:$E$299;Digestion!$D$117:$D$299;RECHERCHEX(J23;Foie!$E$182:$E$330;Foie!$D$182:$D$330;RECHERCHEX(J23;Gluten!$E$45:$E$87;Gluten!$D$45:$D$87;RECHERCHEX(J23;Glycémie!$E$46:$E$163;Glycémie!$D$46:$D$163;RECHERCHEX(J23;'GI et Poumons'!$E$59:$E$199;'GI et Poumons'!$D$59:$D$199;RECHERCHEX(J23;Hor.F!$E$72:$E$166;Hor.F!$D$72:$D$166;RECHERCHEX(J23;Hor.M!$E$92:$E$183;Hor.M!$D$92:$D$183;RECHERCHEX(J23;Immunité!$E$127:$E$202;Immunité!$D$127:$D$202;RECHERCHEX(J23;Infections!$E$159:$E$294;Infections!$D$159:$D$294;RECHERCHEX(J23;Inflammation!$E$114:$E$203;Inflammation!$D$114:$D$203;RECHERCHEX(J23;Joker!$E$66:$E$140;Joker!$D$66:$D$140;RECHERCHEX(J23;Lyme!$E$117:$E$255;Lyme!$D$117:$D$255;RECHERCHEX(J23;Métaux!$E$35:$E$58;Métaux!$D$35:$D$58;RECHERCHEX(J23;Méthylation!$E$133:$E$216;Méthylation!$D$133:$D$216;RECHERCHEX(J23;Microbiome!$E$115:$E$142;Microbiome!$D$115:$D$142;RECHERCHEX(J23;Minéraux!$E$60:$E$89;Minéraux!$D$60:$D$89;RECHERCHEX(J23;Mitochondries!$E$40:$E101;Mitochondries!$D$40:$D$100;RECHERCHEX(J23;Reins!$E$58:$E$88;Reins!$D$58:$D$88;RECHERCHEX(J23;Surrénales!$E$31:$E$59;Surrénales!$D$31:$D$59;RECHERCHEX(J23;Télomères!$E$5:$E$34;Télomères!$D$5:$D$34;RECHERCHEX(J23;Thyroïde!$E$73:$E$107;Thyroïde!$D$73:$D$107;RECHERCHEX(J23;Toxines!$E$44:$E$69;Toxines!$D$44:$D$69;RECHERCHEX(J23;Vitamines!$E$47:$E$76;Vitamines!$D$47:$D$76)))))))))))))))))))))))))))))))

Le 17/06/2023 à 16:00

Bonjour,

Parce qu'en copiant la formule et non pas la cellule, les références ne se décalent pas. Poste le classeur si tu veux en savoir plus. on peut en outre très certainement les simplifier.

Daniel

Le 17/06/2023 à 16:51

Voici le classeur mais j'ai déjà corriger manuellement certaines cellules, en tous les cas toutes celles du groupe des solutions.

Il s'agit des formules sur la feuille "Fiche"

13soins-et-harmonisations-vierge.zip (730.60 Ko)
Le 17/06/2023 à 17:19

C'est exprès que tu as écrit :

RECHERCHEX(P22;Gluten!$E$45:$E$87;Gluten!$D$45:$MD$87

?

Le 17/06/2023 à 17:24

A la place de :

RECHERCHEX(M29;Mitochondries!$E$40:$E107

Mets :

RECHERCHEX(M29;Mitochondries!$E$40:$E$107
Le 17/06/2023 à 17:54

Merci. J'ai pourtant essayé d'éviter les erreurs mais celles-là m'avaient échappées. J'ai corrigé les erreurs et j'ai vérifier toutes les cellules ça fonctionne. Par contre lorsque j'étire l'ensemble du tableau les résultats ne sont pas bon ???

Le 17/06/2023 à 18:03

Comment sais-tu que les résultats ne sont pas bons. Dis-moi avec quelle cellule tu testes et ce que tu devrais trouver.

Daniel

Le 17/06/2023 à 18:28

Excuse moi, je pense que j'ai "parlé" trop vite. J'ai étiré le tableau et je suis en train de faire des tests il semble que ça fonctionne correctement.

Le 17/06/2023 à 18:29

Est-ce que tu pourrais tester cette formule :

=LET(plg; ASSEMB.V('Allergies al.'!$D$5:$D$133; Analyses!$D$5:$D$54; 'Auto-immunité'!$D$5:$D$101; Cancer!$D$5:$D$45; Candida!$D$5:$D$28; Cerveau!$D$5:$D$143; Coeur!$D$5:$D$92; Dentaire!$D$5:$D$154; Digestion!$D$5:$D$112; Foie!$D$5:$D$175; Gluten!$D$5:$D$38; Glycémie!$D$5:$D$39; 'GI et Poumons'!$D$5:$D$51; Hor.F!$D$5:$D$64; Hor.M!$D$5:$D$85; Immunité!$D$5:$D$120; Infections!$D$5:$D$152; Inflammation!$D$5:$D$106; Joker!$D$5:$D$59; Lyme!$D$5:$D$110; Métaux!$D$5:$D$28; Méthylation!$D$5:$D$127; Microbiome!$D$5:$D$108; Minéraux!$D$5:$D$53; Mitochondries!$D$5:$D$33; Reins!$D$5:$D$52; Surrénales!$D$5:$D$24; Thyroïde!$D$5:$D$66; Toxines!$D$5:$D$37; Vitamines!$D$5:$D$40); RECHERCHEX(G12; DECALER(plg; ; 1); plg))

en H12 ?

Daniel

Le 18/06/2023 à 10:29

Oups, désolé, ça ne fonctionnera pas avec Excel 2019.

Daniel

Le 18/06/2023 à 11:16

Comme je m'étais trompée, j'ai office 365 avec un abonnement et non 2019 que j'avais auparavant, j'ai tout de même testé ta formule en HI12 et le résultat est #N/A.

Dommage car cela aurait beaucoup plus court et clair. Merci de toute ton aide.

Le 18/06/2023 à 11:59

J'ai testé celle-ci :

=LET(tbl;ASSEMB.V('Allergies al.'!$D$5:$E$133; Analyses!$D$5:$E$54; 'Auto-immunité'!$D$5:$E$101; Cancer!$D$5:$E$45; Candida!$D$5:$E$28; Cerveau!$D$5:$E$143; Coeur!$D$5:$E$92; Dentaire!$D$5:$E$154; Digestion!$D$5:$E$112; Foie!$D$5:$E$175; Gluten!$D$5:$D$38; Glycémie!$D$5:$D$39; 'GI et Poumons'!$D$5:$D$51; Hor.F!$D$5:$D$64; Hor.M!$D$5:$D$85; Immunité!$D$5:$D$120; Infections!$D$5:$D$152; Inflammation!$D$5:$D$106; Joker!$D$5:$D$59; Lyme!$D$5:$D$110; Métaux!$D$5:$D$28; Méthylation!$D$5:$E$127; Microbiome!$D$5:$E$108; Minéraux!$D$5:$E$53; Mitochondries!$D$5:$E$33; Reins!$D$5:$E$52; Surrénales!$D$5:$E$24; Thyroïde!$D$5:$E$66; Toxines!$D$5:$E$37; Vitamines!$D$5:$E$40);INDEX(PRENDRE(tbl;;1);EQUIV(G12;EXCLURE(tbl;;1);0)))

Daniel

Le 18/06/2023 à 12:10

Un poil plus court :

=LET(tbl;ASSEMB.V('Allergies al.'!$D$5:$E$133;Analyses!$D$5:$E$54;'Auto-immunité'!$D$5:$E$101;Cancer!$D$5:$E$45;Candida!$D$5:$E$28;Cerveau!$D$5:$E$143;Coeur!$D$5:$E$92;Dentaire!$D$5:$E$154;Digestion!$D$5:$E$112;Foie!$D$5:$E$175;Gluten!$D$5:$D$38;Glycémie!$D$5:$D$39;'GI et Poumons'!$D$5:$D$51;Hor.F!$D$5:$D$64;Hor.M!$D$5:$D$85;Immunité!$D$5:$D$120;Infections!$D$5:$D$152;Inflammation!$D$5:$D$106;Joker!$D$5:$D$59;Lyme!$D$5:$D$110;Métaux!$D$5:$D$28;Méthylation!$D$5:$E$127;Microbiome!$D$5:$E$108;Minéraux!$D$5:$E$53;Mitochondries!$D$5:$E$33;Reins!$D$5:$E$52;Surrénales!$D$5:$E$24;Thyroïde!$D$5:$E$66;Toxines!$D$5:$E$37;Vitamines!$D$5:$E$40);RECHERCHEX(G12;EXCLURE(tbl;;1);PRENDRE(tbl;;1)))
Le 18/06/2023 à 12:14

En effet elle fonctionne !!! Je l'ai étirée elle fonctionne. Par contre lorsque je passe aux colonnes KL la première cellule donne un mauvais résultat, elle me donne le même résultat que celui HI même si dans cellule J j'ai mis un autre code que dans la cellule G.

Je ne comprends pas tout de ta formule, j'ai essayé de la changée en =LET(tbl;ASSEMB.V('Allergies al.'!$D$5:$E$133; Analyses!$D$5:$E$54; 'Auto-immunité'!$D$5:$E$101; Cancer!$D$5:$E$45; Candida!$D$5:$E$28; Cerveau!$D$5:$E$143; Coeur!$D$5:$E$92; Dentaire!$D$5:$E$154; Digestion!$D$5:$E$112; Foie!$D$5:$E$175; Gluten!$D$5:$D$38; Glycémie!$D$5:$D$39; 'GI et Poumons'!$D$5:$D$51; Hor.F!$D$5:$D$64; Hor.M!$D$5:$D$85; Immunité!$D$5:$D$120; Infections!$D$5:$D$152; Inflammation!$D$5:$D$106; Joker!$D$5:$D$59; Lyme!$D$5:$D$110; Métaux!$D$5:$D$28; Méthylation!$D$5:$E$127; Microbiome!$D$5:$E$108; Minéraux!$D$5:$E$53; Mitochondries!$D$5:$E$33; Reins!$D$5:$E$52; Surrénales!$D$5:$E$24; Thyroïde!$D$5:$E$66; Toxines!$D$5:$E$37; Vitamines!$D$5:$E$40);INDEX(PRENDRE(tbl;;1);EQUIV(J12;EXCLURE(tbl;;1);0))) pour le tableau J/KL

Mais c'est pas bon, peux-tu m'expliquer comment la rendre fonctionnel pour tous mes minis tableaux : J/KL, M/NO, P/QR ?

Le 18/06/2023 à 14:40

La fonction ASSEMB.V met les uns sous les autres :

'Allergies al.'!$D$5:$E$133

Analyses!$D$5:$E$54

etc. Comme si c'était un seul tableau :

capture d ecran 2023 06 18 143610

La fonction LET affecte ce tableau à la variable tbl.

EXCLURE(tbl;;1)

utilise la seconde colonne du tableau tbl.

PRENDRE(tbl;;1)

utilise la première colonne de ce tableau.

Je vais regarder pourquoi ça ne fonctionne pas.

Daniel

Le 18/06/2023 à 14:48

A priori, quand je recopie la formule de la cellule H12 dans la cellule K12, ca paraît correct :

capture d ecran 2023 06 18 144611

Daniel

Rechercher des sujets similaires à "formule juste qui fois etiree devient fausse"