Excel : aide formule

[Dimitri2018]

Dans une colonne (A par exemple, j'ai des chiffres de 1 à 22 (résultat d'autres formules) sur une quinzaine de lignes (donc A10 à A25).
Je voudrais trouver une formule qui me permette de savoir combien de fois, en additionnant deux chiffres de la colonne A le résultat fait 22.

EX :

5
8
16
9
13
8
17

La première ligne et la troisième en s'additionnant fait 22 et la deuxième et la dernière font 22. Donc réponse finale : 2

 

[Dimitri2018]

Et en plus, la quatrième et la cinquième en s'additionnant font aussi 22, vivement la formule pour ne plus avoir ce genre d'erreur...

 

[les_innommables66]

Bonjour,

Si le nombre de lignes est toujours le même (ou si tu as une borne supérieure), voir copie d'écran et la formule en R1

Les autres formules sont simples

Cordialement
Nicolas

 

[daffyb]

En effet, tu as besoin d'un truc qui calcule pour toi, car tu as tout faux dans ton exemple
Pour résoudre ton problème, je passe par un tableau intermédiaire dans lequel j'additionne les nombres 2 à 2 puis j'applique la formule qui va bien.
Voir illustration :

 

[les_innommables66]

Pour obtenir les cellules de la ligne 1, tu peux utiliser une seule formule matricielle = transpose(A2:A16)

Tu peux également ne remplir qu'une moitié du tableau et ne pas avoir besoin de diviser par deux le résultat

 

[les_innommables66]

Et pour raffiner un peu, la formule en cellule B2 =SI(COLONNE(B1)<=LIGNE(A2);"";B$1+$A2) peut être recopiée partout de B2 à P16:



Il ne reste plus qu'à supprimer la division par deux de la cellule R1

C'est plus joli

 

[Dimitri2018]

Déjà, merci beaucoup pour cette réponse rapide. Je me mets au travail.

 

[zeltron54]

Bonjour,

Une autre méthode, serait de rechercher dans la plage A10:A25 la valeur qui additionnée donne 22 et d'afficher le numéro de ligne ou se trouve cette valeur.
Pour cela dans la colonne B de B10 à B25 tu mets comme valeur la ref de la cellule .
Dans la colonne C , en C10 la formule sera : =SI.NON.DISP(LIGNE(INDIRECT(RECHERCHEV(22-A10;$A$10:$B$25;2;FAUX)));"")
tirer la formule vers le bas jusqu"en C25.
En cellule C27 la formule pour avoir le nombre de lignes pouvant donner 22 sera : =NB.SI(C10:C25;">0")

 

[daffyb]

Bonjour,

Une autre méthode, serait de rechercher dans la plage A10:A25 la valeur qui additionnée donne 22 et d'afficher le numéro de ligne ou se trouve cette valeur.
Pour cela dans la colonne B de B10 à B25 tu mets comme valeur la ref de la cellule .
Dans la colonne C , en C10 la formule sera : =SI.NON.DISP(LIGNE(INDIRECT(RECHERCHEV(22-A10;$A$10:$B$25;2;FAUX)));"")
tirer la formule vers le bas jusqu"en C25.
En cellule C27 la formule pour avoir le nombre de lignes pouvant donner 22 sera : =NB.SI(C10:C25;">0")

Voir la pièce jointe 122725

Très intéressant !! tu ne penses pas comme nous, j'aime bien cette vision

 

[zeltron54]

Après réflexion, je m'étais embêté à convertir la ligne de la cellule alors qu"en inscrivant dans la colonne B directement le numèro de la ligne la formule se simplifie.
Formule dans la cellule B10: =SI.NON.DISP(RECHERCHEV(22-A10;$A$10:$B$25;2;FAUX);"")
formule à tirer vers le bas jusqu'en C25
Dans la colonne B il suffit d'y écrire le numéro de la ligne.
Pour le total pas de changement.

 

[zeltron54]

Encore plus simple, sans colonnes intermédiaires
dans la colonne B, de B10 à B25 la formule de recherche qui affiche la ligne ou est la valeur complémentaire à 22 est trouvée: (formule en B10)
=SI.NON.DISP(EQUIV(22-A10;$A$10:$A$25;0)+9;"")
Formule à tirer vers le bas
la formule du total devient : =NB.SI(B10:B25;">0")

 

[daffyb]

Encore plus simple, sans colonnes intermédiaires
dans la colonne B, de B10 à B25 la formule de recherche qui affiche la ligne ou est la valeur complémentaire à 22 est trouvée: (formule en B10)
=SI.NON.DISP(EQUIV(22-A10;$A$10:$A$25;0)+9;"")
Formule à tirer vers le bas
la formule du total devient : =NB.SI(B10:B25;">0")

Voir la pièce jointe 122766

Je me permets une petite remarque
Dans ton calcul, tu autorises d'addition d'un élément à lui même (11 par exemple). a+

 

[zeltron54]

@daffyb
Effectivement la formule recherche dans la colonne le complément à 22 sans distinction.
Beau défi : je regarde ça ! :banghead:

Edit : il faut avouer aussi que seul (11+11) font 22 donc c'est le seul cas où la valeur fera référence à la même cellule !

Allez pour Daffyb la fromule devient:
=SI.NON.DISP(SI(A10<>11;(EQUIV(22-A10;$A$10:$A$25;0)+9);"");"")

 

[daffyb]

perso, je fais ça

en B10
=SI.NON.DISP(SI(LIGNE(A10)<>(EQUIV(22-A10;$A$10:$A$25;0)+9);EQUIV(22-A10;$A$10:$A$25;0)+9;"");"")

 

[daffyb]

@daffyb
Effectivement la formule recherche dans la colonne le complément à 22 sans distinction.
Beau défi : je regarde ça ! :banghead:

Edit : il faut avouer aussi que seul (11+11) font 22 donc c'est le seul cas où la valeur fera référence à la même cellule !

Par contre, s'il y a deux 11 alors ça fonctionne

 

[zeltron54]

@ daffyb

Et oui je n'avais pas pensé qu'il pourrait y avoir 2 x 11, donc ta formule est la bonne :mad:
je la remets là pour dimitri2018 (s'il revient un jour)

=SI.NON.DISP(SI(LIGNE(A10)<>(EQUIV(22-A10;$A$10:$A$25;0)+9);EQUIV(22-A10;$A$10:$A$25;0)+9;"");"")

 

[Dimitri2018]

Merci, merci, je n'étais pas parti du tout mais en train d'essayer les différentes propositions. et je surtout, j'essaie de comprendre les propositions. Donc merci beaucoup. Et effectvement, j'étais ennuyé par le deux fois 11 maintenant résolu de mains de maîtres....

 

[zeltron54]

OK! content pour toi .

 

[Dimitri2018]

Mais j'ai une autre question. J'ai donc dans ma colonne B9 à B21 (par exemple) des nombres de 1 à 22 (qui sont des réponses de formules et j'aimerais que les réponses en colonne B renvoie à des cellules contenant une image. J'ai bien essayé dans propriétés de cocher "Déplacer et dimensionner avec les cellules" mais cela ne fonctionne pas et me donne une réponse zéro mais ne m'apporte pas d'image. Et ma formule par rapport à la réponse en B9 est (je suis certain que vous allez sourire parce qu'il existe un moyen probablement moins long d'y parvenir) =SI(B9=0;E37;SI(B9=1;E38;SI(B9=2;E39;SI(B9=3;E40;SI(B9=4;E41;SI(B9=5;E42;SI(B9=6;E43;SI(B9=7;E44;SI(B9=8;E45;SI(B9=9;E46;SI(B9=10;E47;SI(B9=11;E48;SI(B9=12;E49;SI(B9=13;E50;SI(B9=14;E51;SI(B9=15;E52;SI(B9=16;E53;SI(B9=17;E54;SI(B9=18;E55;SI(B9=19;E56;SI(B9=20;E57;SI(B9=21;E58;SI(B9=22;E59;D6))))))))))))))))))))))). Et donc dans E37 à E59 une cellule avec une image. Comment "fusionner" l'image avec la cellule ?
Merci pour vos réponses.