Aide Formule plage variable

[Beast464]

Bonjour le forum
Voici mon probleme:

Je suis sur une feuille3:

IF(Sheet1!$A2=0,0,IF(AND(OR(Sheet2!C2<>0,Sheet2!D2<>0,Sheet2!E2<>0),(MOD(COLUMNS($D1:D1),ROUND(Sheet1!$A2,0))=0)),Sheet1!F2,0)))

Cette formule verifie:a droite et a gauche de la cellule de depart D2, si sur la feuille 2 elles sont differentes de 0 ( Verifie aussi la cellule de depart). Et si c'est le cas, on met F2 dans la cellule de la feuille 3 correspondante.

Mon probleme est que parfois il faudrait que ce soit les 2 cellules a droite et les 2 cellules a gauche, ou les 4 , ou rien.
Pour cela j'ai donc cree un coef 2 3 4 0 etc.
Mais je ne sais pas comment ecrire quelque chose comme ceci:

IF(Sheet2!"Colonne-coef"&2:"Colonne+coef"&2<>0)

Evidemment cela ne marche pas ^^

Une idee serait la bienvenue
Merci d'avance pour tout aide.

Si besoin d'un exemple j'en creerai un, mais probablement soit impossible a ecrire de la sorte soit un pb de syntaxe.

 

[Beast464]

Re : Aide Formule plage variable

Serait-il possible d'avoir quelques explication sur la formule :

SUMPRODUCT((OFFSET(Sheet3!D3,0,0,1,Sheet1!$R3)=0)*1)<>(Sheet1!$R3)

Que fait chaque partie de la formule? j'essaye de l'adapter mais je n'y arrive pas

 

[Tibo]

Re : Aide Formule plage variable

Bonjour,

Le fonction OFFSET (DECALER in French) construit une matrice virtuelle composée de plusieurs cellules :

=OFFSET(cellule_départ,décalage_vertical,décalage_horizontal,hauteur,largeur)

Ici, OFFSET(Sheet3!D3,0,0,1,Sheet1!$R3) retourne une plage qui débute en D3 et de largeur le contenu de Sheet1!$R3. (Les autres paramètres sont ici neutres : décalage vertical de 0 ligne, décalage horizontal de 0 colonne, hauteur de 1 ligne)

Ensuite, on teste si chacun des éléments (cellules) de cette matrice est égal à 0. On va obtenir en retour une matrice de VRAI et de FAUX.

Pour Excel, VRAI vaut 1 et FAUX vaut 0.

En multipliant la matrice de VRAI et FAUX par 1, on va obtenir cette fois une matrice de 1 et de 0.

La fonction SUMPRODUCT va nous donner la somme des 1 et des 0 de cette matrice.

Si le résultat est différent de 0, ça veut dire qu'un des éléments de la matrice était différent de 0, ou que la matrice n'était pas constituée que de 0.

Ce qu'il faut réussir à appréhender avec OFFSET, c'est l'aspect virtuel de la matrice créée.

Voilou pour la tentative d'explication.

@+

 

[Beast464]

Re : Aide Formule plage variable

Je vois , mais du coup pour lui dire de faire l inverse de ce qu'elle fait actuellement. Je lui dis de tester si il y a des 1 dans la matrice plutot aue des 0?

Pour mettre 0 dans les cases testees vrai et le chiffre ailleurs.

SUMPRODUCT((OFFSET(Sheet3!D3,0,0,1,Sheet1!$R3)=0)*1)<>(Sheet1!$R3)

MArche mais resultats non conforme a ce que je veux.

 

[Tibo]

Re : Aide Formule plage variable

re,

Sous réserve d'avoir compris ta demande :

=SUMPRODUCT((OFFSET(Sheet3!D3,0,0,1,Sheet1!$R3)=[B][COLOR=red]1[/COLOR][/B])*1)

permet de connaître combien de 1 il y a dans la matrice définir avec OFFSET

Si souci persiste, essaye de recréer un petit bout de fichier basé uniquement sur ce problème

A te (re)lire

@+

 

[Beast464]

Re : Aide Formule plage variable

Oups ui pardon c'est ce que je voulais ecrire, je fais un petit fichier pour te montrer la difference.

les cases en rouge devraient donner 0 logiquement.. mais je dois avoir mal compris ^^

Regarde la pièce jointe beast464.zip

 

[Tibo]

Re : Aide Formule plage variable

re,

en B17, la partie de la formule :

 DECALER(Sheet2!B3;0;0;1;Sheet1!$B3+1)

retourne la matrice suivante :

{0\0\6}

ce qui correspond bien à ton tableau de la Sheet2

donc,

 SOMMEPROD((DECALER(Sheet2!B3;0;0;1;Sheet1!$B3+1)=0)*1)

va retourner 2

(nombre de zéros)

Qui est différent de la valeur de B3+1 en Sheet1.

Je te laisse revoir un peu

@+

 

[Beast464]

Re : Aide Formule plage variable

Ce que je ne comprends pas c'est que je veux obtenir l'opposé du premier tableau sheet3.
Soit les cases en rouge du troisieme tableau contenant des zero.
ce qui n'est pas le cas ici.
Donc en mettent le decaler = 1 je pensais obtenir ce resultats. Et ce n'est pas cela apparemment

 

[Tibo]

Re : Aide Formule plage variable

re,

S'il s'agit d'obtenir l'inverse, il suffit d'inverser les réponses VRAI et FAUX du test.

en A16 :

=SI(Sheet1!$A2=0;0;SI(SOMMEPROD((DECALER(Sheet2!A2;0;0;1;Sheet1!$B2+1)=0)*1)<>
(Sheet1!$B2+1);0;Sheet1!$F2))

à recopier vers la droite et vers le bas

Je te laisse tester

@+

 

[Beast464]

Re : Aide Formule plage variable

J'avais deja teste cette option, et cela ne me mettais que des 0. J'ai reteste, et ca a l'air de marcher. J'avais du laisser Decaler=1 en inversant.
Je reviens par ici si je recontre des problemes.
As tu eu le temps de regarder si il etait possible de tester les cases avant et apres?
Apres plusieurs test il est assez frequent que cela fausse mes donnees. Toujours moins aue si je le faisais a la main evidemment

En tout cas merci de ton temps, j'avance

 

[Beast464]

Re : Aide Formule plage variable

Ca marchait jusqu'a ce que je rajoute une condition.. je patauge.

J'ai voulu placer les valeurs a un intervalle precis, et pas toute les cases.

Voir fichier ci joint.Regarde la pièce jointe beast464.zip

en rajoutant une condition ET.

 

[Tibo]

Re : Aide Formule plage variable

Bonjour,

Sans trop de certitude, à essayer en A16 (de ton dernier tableau) :

=SI(Sheet1!$A2=0;0;SI(ET(SOMMEPROD((DECALER(Sheet2!A2;0;0;1;Sheet1!$B2+1)=0)*1)
<>(Sheet1!$B2+1);SOMMEPROD((DECALER(Sheet2!A2;0;0;1;SI(COLONNE()<ABS(
-Sheet1!$B2-1);COLONNE();-Sheet1!$B2-1))=0)*1)<>(Sheet1!$B2+1));Sheet1!$F2;0))

à recopier vers le bas et vers la droite

Je te laisse faire les tests.

Si problème, reviens avec un fichier sur lequel se présente le problème avec un petit commentaire sur ce qui est attendu et pourquoi.

A te (re)lire

@+

 

[Beast464]

Re : Aide Formule plage variable

Hmmm em fait je pensais pouvoir simplifier une formule

Ce que j'ai fais c'est que j'ai d'abord copier les formule selon l'intervalle.
Et ensuite j'ai soustrait

=SI(Sheet1!$A2=0;0;SI(SOMMEPROD((DECALER(Sheet2!A2;0;0;1;Sheet1!$B2+1)=0)*1)<>
(Sheet1!$B2+1);0;Sheet1!$F2))

^^ merci pour ton code je test ceci