Etirer une formule en respectant des sauts de cellules

Question

Bon, je vous l'accorde, le titre n'est pas tres évocateur je m'explique donc et vous transmets un petit exemple en pièce jointe. attention, l'exercice doit etre résolu (si cela est possible) sur la meme feuille et sur la meme ligne. dans l'exemple, j'ai une première partie de ligne qui comporte mes datas (ici les longueur, largeurs et hauteurs d'un cube.) . j'ai 5 cubes. a la suite, je souhaite calculer le volume de chaque cube soit LxlXh (que l'on retrouve en Q2 pour le cube 1) mon probleme est d'étirer cette formule automatiquement pour calculer les cubes 2,3,4 et 5. et c'est là que ca bug.je n'arrive pas à générer une incrémentation automatique (etirer la fonction de Q2) qui avancerait de 3 cases à chaque fois. si d'aventure quelqu'un a la solution, je suis preneur car je travaille sur un tableau comportant 365 jours et plusieurs tableaux consécutifs pour calculer des heures sup.... j'ai commencé manuellement mais franchement, je m'arrache la tete sans compter le risque d'erreurs.... mille merci d'avance à tous. clt Railman...

LouReeD · Answer

Une des solutions, formule en Q2 : =INDIRECT("L2C"&((COLONNE()-17)*3)+1;FAUX)*INDIRECT("L2C" & ((COLONNE()-17)*3)+2;FAUX)*INDIRECT("L2C" & ((COLONNE()-17)*3)+3;FAUX) J'utilise la fonction INDIRECT avec en paramètre "Faux" afin de travailler avec les repère L1C1 en équivalence du A1 car cela veut dire Ligne 1 Colonne 1. Avec la fonction Colonne je crée un décalage de 3 colonnes. Pour cela la colonne Q = 17, donc pour la formule en colonne Q il ne doit pas y avoir de décalage on a donc colonne Q = 17, moins 17 = 0 x par 3 = 0, et on additionne 1, 2 et 3 pour récupérer les données en colonne 1, 2 et 3 La formule en tirant par la droite va en colonne R = 18, on a donc bien la récupération des colonnes 4,5 et 6 : 18-17 = 1 x3 = 3, en y additionnant 1,2 et 3 on obtient 4,5 et 6. La fonction INDIRECT permet de récupérer la valeur se trouvant à l'adresse entre guillemet donc INDIRECT("L2C" & ((COLONNE()-17)*3)+1;FAUX) on obtient la valeur se trouvant en ligne 2 et colonne ((COLONNE()-17)*3)+1 qui si la formule est en colonne Q donne 1, donc ligne 2 colonne 1 = A2. Si les formules devaient être tirées vers le bas, il faudrait alors rendre le "2" de "L2" variable, il suffit alors de remplacer le "2" par son équivalent LIGNE() : INDIRECT("L" & LIGNE() & "C" & ((COLONNE()-17)*3)+1;FAUX), ce qui donne en Q2 L pour ligne LIGNE() donne 2 C pour colonne et e reste on l'a vu donne 1 donc INDIRECT(L2C1) = A2. @ bientôt LouReeD...

LouReeD · Answer

Après, pour créer 5 formules cela fait riche ! @ bientôt LouReeD...

themmanuel · Answer

Est il vraiment obligatoire de garder le tableau sur une seul ligne comme indiqué sur votre fichier?

Si cela est possible, ça serait bien plus pratique de mettre Long/large/haut en colonne (A2/A3/A4) et sur la premiére ligne, cube 1 cube 2 etc. et sur la cinquiéme ligne le calcul du volume. les formule s'étirerons plus facilement.

La solution proposer par LouReed fonctionne parfaitement mais si votre base de données est grande, les calcul vont vite faire ramer l'ordinateur et le cpu..

saboh12617 · Answer

Vous êtes vraiment sur XL 2010 ? Une proposition plus performante en Q2 : =PRODUIT(INDEX($A2:$O2;1;(COLONNE()-17)*3+{1;2;3})) Peut être besoin de faire un CTRL+MAJ+ENTREE sur les anciennes versions, ou de remplacer "PRODUIT" par SOMMEPROD. A tester...

BsAlv · Answer

Même idée que saboh12617 et si le nombre de cellules est assez petit (&lt;10.000 par exemple, décaler est volatile) =PRODUIT(DECALER($A2:$C2;;3*(COLONNE()-COLONNE($Q$1))))...

Andreas · Answer

Solution possible a coller dans la cellule Q2 et étirer a droite: =INDEX($A2:$O2;1;1+(COLONNE()-COLONNE($Q$2))*3)*INDEX($A2:$O2;1;2+(COLONNE()-COLONNE($Q$2))*3)*INDEX($A2:$O2;1;3+(COLONNE()-COLONNE($Q$2))*3) Cordialement...

saboh12617 · Answer

L'idée des différentes formules proposées est toujours la même : se baser sur le numéro de colonne de la cellule contenant la formule pour aller chercher dans les colonnes A:O les 3 colonnes correspondantes. Pour cela, on peut remarquer ceci : La formule en "Q" (colonne 17) doit aller chercher les colonnes A,B,C (colonnes 1,2,3). La formule en "R" (colonne 18) doit aller chercher les colonnes D,E,F (colonnes 4,5,6). La formule en "S" (colonne 19) doit aller chercher les colonnes G,H,I (colonnes 7,8,9). De là on peut déduire un "pattern" qui se répète. Si l'on retire 17 au numéro de la colonne contenant la formule, on obtient : (pour Q) : 17 - 17 → 0 → 1,2,3 (pour R) : 18 - 17 → 1 → 4,5,6 (pour S) : 19 - 17 → 2 → 7,8,9 Et en poussant un peu : (pour Q) : 0 → 0*3 + &#91;1,2,3&#93; → 1,2,3 Soit &#91;nbCol - 17&#93;*3 + {1,2,3} (pour R) : 1 → 1*3 + &#91;1,2,3&#93; → 4,5,6 (pour S) : 2 → 2*3 + &#91;1,2,3&#93; → 7,8,9 C'est le schéma de calcul que l'on peut retrouver dans ma proposition de formule sous forme compactée : (COLONNE()-17)*3+{1;2;3}) ou bien dans la formule de @Andreas sous forme "éclatée". Chez @LooReeD aussi l'idée est la même, et @BsAlv utilise ce principe mais via DECALER au lieu de INDEX. Maintenant, on peut utiliser INDEX pour aller récupérer les valeurs à multiplier dans la matrice A2:Q2 en se basant sur leur numéro de colonne, que nous venons de calculer. En espérant que cette petite explication vous offre des pistes de lecture/compréhension...