Formule Excel pour additionner les plages de cellules non chevauchantes?

Disons que j'ai des nombres dans A1 à A20 et que je voulais append des plages non-chevauchantes de 5 cellules dans la colonne A et stocker les résultats dans les cellules de la colonne E, cela ressemblerait à ceci (si les formules ont été tapées dans chaque cellule de la colonne E manuellement):

E1 = SUM(A1:A5) E2 = SUM(A6:A10) E3 = SUM(A11:A15) E4 = SUM(A16:A20)

Cependant, je ne veux pas taper les formules dans les cellules E manuellement. Je veux pouvoir sélectionner la formule dans E1 et la faire glisser vers E4, tout en maintenant les plages non cumulables de 5 cellules en A! Excel ne me donne pas ce comportement par défaut, il le fait à la place:

 E1 = SUM(A1:A5) E2 = SUM(A2:A6) E2 = SUM(A3:A7) E4 = SUM(A4:A8)

Voyez comment les plages de 5 cellules dans chaque fonction SUM () se chevauchent? par exemple A1: A5 et A2: A6. Ce n'est pas ce que je veux.

Alors, quelle est une formule qui me permettrait de faire cela? Fondamentalement, le pseudocode suivant fonctionnerait, mais je ne parviens pas à mettre en œuvre quelque chose comme ça dans Excel:

 SUM(CELL(COLUMN, (CURRENT_ROW - 1) * 5 + 1):CELL(COLUMN, (CURRENT_ROW - 1) * 5 + 5))

Par exemple, pour E2, CURRENT_ROW = 2, ce qui signifie qu'il ressemblerait à ceci:

 SUM(CELL(A, (2 - 1) * 5 + 1):CELL(A, (2 - 1) * 5 + 5)) = SUM(CELL(A, 6):CELL(A, 10))

Ce pseudocode suppose que CELL possède la méthode signature CELL (row, column).

La key est la fonction OFFSET. Le décalage prend une cellule de départ et plusieurs lignes, colonnes et hauteur et largeur optionnelles pour générer une reference à une cellule / scope à la volée. L'astuce consiste à utiliser d'autres fonctions pour générer le décalage de ligne et d'autres parameters.

Je viens de bash quelque chose qui semble faire ce que tu veux, je verrai si je peux l'expliquer …

Colonne A: entiers (A1) 0, (A2) 1, (A3) 2,3,4,5 etc … c'est un index pour combien vous êtes en train de compenser, vous pourriez probablement supprimer ceci en utilisant quelque chose comme le Fonction ROW) Colonne D (ou toute autre localization arbitraire): les nombres que vous souhaitez résumer, à partir de D1 – Je viens d'utiliser (D1) 1, (D2) 2,3,4,5 …

La colonne B est celle qui vous intéresse, la formule est:

 =SUM(OFFSET(D$1,A1*5,0,5,1))

Ce qu'il fait: addition de la plage définie par: Un bloc de cellules commençant (A1 * 5) cellules vers le bas et 0 en travers de $ D $ 1, qui est de 5 de haut par 1 de large. Voir l'édition, j'ai laissé cela parce que vous pouvez mettre des nombres arbitraires dans les cellules A et utiliser ce principe.

J'espère que ça a un sens? Excel ne se prête pas aux explications textuelles!

Edit: Supprime les cellules A1 …, se souvient que ROW () permet des renvois automatiques, ce qui signifie que vous pouvez faire

  =SUM(OFFSET(D$1,(ROW(B1)-1)*5,0,5,1))

Lorsque cette formule se trouve dans la cellule B1 (collé) et vos données se trouvent dans la colonne D à partir de 1.

Cette formule obtiendra les résultats corrects lorsqu'elle est input dans la cellule E1 et est peuplée dans E2, E3 etc.

 =SUM(OFFSET(A1,ROW(A1)*4-4,0,5,1))