Bendroji formulė
=SUMPRODUCT(--(MOD(COLUMN(rng)-COLUMN(rng.first)+1,n)=0),rng)
Santrauka
Apibendrinant kiekvieną n-ąjį stulpelį, galite naudoti formulę, pagrįstą funkcijomis SUMPRODUCT, MOD ir COLUMN.
Parodytame pavyzdyje formulė L5 yra:
=SUMPRODUCT(--(MOD(COLUMN(B5:J5)-COLUMN(B5)+1,K5)=0),B5:J5)
Paaiškinimas
Esmė naudoja sumą SUMPRODUCT, kad susumuotų eilutės vertes, kurios buvo „filtruotos“ naudojant logiką, pagrįstą MOD. Svarbiausia yra tai:
MOD(COLUMN(B5:J5)-COLUMN(B5)+1,K5)=0
Šis formulės fragmentas naudoja funkciją COLUMN, kad gautumėte diapazono „santykinių“ stulpelių skaičių rinkinį (išsamiai paaiškintą čia), kuris atrodo taip:
(1,2,3,4,5,6,7,8,9)
Tai patenka į MOD, taip:
MOD((1,2,3,4,5,6,7,8,9),K5)=0
kur K5 yra N vertė kiekvienoje eilutėje. Funkcija MOD grąžina kiekvieno stulpelio skaičiaus likutį, padalytą iš N. Taigi, pavyzdžiui, kai N = 3, MOD grąžins maždaug taip:
(1,2,0,1,2,0,1,2,0)
Atkreipkite dėmesį, kad 3, 6, 9 ir tt stulpeliuose rodomi nuliai. Formulė naudoja = 0, kad priverstų TIESĄ, kai likutis yra nulis, o NETIESA, kai ne, tada mes naudojame dvigubą neigiamą (-), kad priverstume TIKRĄ ir NETIESA vienetams ir nuliams. Tai palieka tokį masyvą:
(0,0,1,0,0,1,0,0,1)
Kur 1s dabar nurodo „n-tas reikšmes“. Tai patenka į SUMPRODUCT kaip masyvas1 kartu su B5: J5 kaip masyvas2. Tada „SUMPRODUCT“ daro savo dalyką, pirmiausia daugindamas, tada sumuodamas masyvų sandaugas.
Vienintelės reikšmės, kurios „išgyvena“ dauginimąsi, yra tos, kur masyvas1 turi 1. Tokiu būdu galite pagalvoti apie masyvo1 logiką, kuri „filtruoja“ masyvo2 reikšmes.
Sumuokite kiekvieną kitą stulpelį
Jei norite susumuoti kiekvieną kitą stulpelį, tiesiog prireikus pritaikykite šią formulę, turėdami omenyje, kad formulė automatiškai priskiria 1 pirmajam diapazono stulpeliui. Apibendrinant EVEN stulpelius, naudokite:
=SUMPRODUCT(--(MOD(COLUMN(A1:Z1)-COLUMN(A1)+1,2)=0),A1:Z1)
Apibendrinant ODD stulpelius, naudokite:
=SUMPRODUCT(--(MOD(COLUMN(A1:Z1)-COLUMN(A1)+1,2)=1),A1:Z1)
