Obecný vzorec
=SUMPRODUCT(--(MOD(COLUMN(rng)-COLUMN(rng.first)+1,n)=0),rng)
souhrn
Chcete-li sečíst každý n-tý sloupec, můžete použít vzorec založený na funkcích SUMPRODUCT, MOD a COLUMN.
V zobrazeném příkladu je vzorec v L5:
=SUMPRODUCT(--(MOD(COLUMN(B5:J5)-COLUMN(B5)+1,K5)=0),B5:J5)
Vysvětlení
V jádru používá SUMPRODUCT k součtu hodnot v řádku, které byly "filtrovány" pomocí logiky založené na MOD. Klíčem je toto:
MOD(COLUMN(B5:J5)-COLUMN(B5)+1,K5)=0
Tento úryvek vzorce používá funkci COLUMN k získání sady „relativních“ čísel sloupců pro rozsah (podrobně vysvětlený zde), který vypadá takto:
(1,2,3,4,5,6,7,8,9)
To jde do MOD takto:
MOD((1,2,3,4,5,6,7,8,9),K5)=0
kde K5 je hodnota N v každém řádku. Funkce MOD vrátí zbytek pro každé číslo sloupce dělené N. Takže například když N = 3, MOD vrátí něco takového:
(1,2,0,1,2,0,1,2,0)
Všimněte si, že nuly se objevují pro sloupce 3, 6, 9 atd. Vzorec používá = 0 k vynucení TRUE, když je zbytek nula, a FALSE, pokud ne, pak použijeme dvojitý zápor (-) k vynucení TRUE a FALSE k jednotkám a nulám. To ponechává pole takto:
(0,0,1,0,0,1,0,0,1)
Kde 1 s nyní označují „n-té hodnoty“. To jde do SUMPRODUCT jako pole1, spolu s B5: J5 jako pole2. SUMPRODUCT pak udělá svou věc, nejprve znásobí a poté sčítá produkty polí.
Jediné hodnoty, které „přežijí“ násobení, jsou ty, kde pole1 obsahuje 1. Tímto způsobem můžete uvažovat o logice pole1 „filtrující“ hodnoty v poli2.
Sečtěte každý druhý sloupec
Pokud chcete sečíst všechny další sloupce, přizpůsobte tento vzorec podle potřeby a mějte na paměti, že vzorec automaticky přiřadí 1 prvnímu sloupci v rozsahu. Chcete-li sčítat EVEN sloupce, použijte:
=SUMPRODUCT(--(MOD(COLUMN(A1:Z1)-COLUMN(A1)+1,2)=0),A1:Z1)
Chcete-li sečíst sloupce ODD, použijte:
=SUMPRODUCT(--(MOD(COLUMN(A1:Z1)-COLUMN(A1)+1,2)=1),A1:Z1)
