Excel

Sum hver niende kolonne

Sum Every Nth Column

Excel -formel: Sum hver niende kolonneGenerisk formel | _+_ | Resumé

For at opsummere hver niende kolonne kan du bruge en formel baseret på funktionerne SUMPRODUCT, MOD og COLUMN.





I det viste eksempel er formlen i L5:

excel-opslagsdata fra et andet ark
= SUMPRODUCT (--( MOD ( COLUMN (rng)- COLUMN (rng.first)+1,n)=0),rng)
Forklaring

Kernen bruger SUMPRODUCT til at summe værdier i en række, der er blevet 'filtreret' ved hjælp af logik baseret på MOD.Nøglen er denne:





 
= SUMPRODUCT (--( MOD ( COLUMN (B5:J5)- COLUMN (B5)+1,K5)=0),B5:J5)

Dette udsnit af formlen bruger funktionen KOLONNE til at få et sæt 'relative' kolonnetal for området (forklaret detaljeret her ), der ser sådan ud:

{1,2,3,4,5,6,7,8,9}



Dette går i MOD sådan:

 
 MOD ( COLUMN (B5:J5)- COLUMN (B5)+1,K5)=0

hvor K5 er værdien for N i hver række. MOD -funktionen returnerer resten for hvert kolonnenummer divideret med N. Så når N = 3 f.eks. Returnerer MOD sådan noget som dette:

{1,2,0,1,2,0,1,2,0}

Bemærk, at nulpunkterne vises for kolonne 3, 6, 9 osv. Formlen bruger = 0 til at tvinge en SAND, når resten er nul og en FALSK, når ikke, så bruger vi en dobbelt-negativ (-) til at tvinge SAND og FALSK overfor enere og nuller. Det efterlader et array som dette:

{0,0,1,0,0,1,0,0,1}

Hvor 1'er nu angiver 'nth værdier'. Dette går ind i SUMPRODUCT som array1 sammen med B5: J5 som array2. SUMPRODUCT gør derefter sin ting, først multiplicerer og summerer derefter produkter fra arrays.

De eneste værdier, der 'overlever' multiplikation, er dem, hvor array1 indeholder 1. På denne måde kan du tænke på logikken i array1 'filtrere' værdierne i array2.

Sum hver anden kolonne

Hvis du vil opsummere hver anden kolonne, skal du bare tilpasse denne formel efter behov, og huske på, at formlen automatisk tildeler 1 til den første kolonne i området. For at opsummere EVEN kolonner skal du bruge:

excel formel ugedag fra dato
 
 MOD ({1,2,3,4,5,6,7,8,9},K5)=0

For at opsummere ODD -kolonner skal du bruge:

 
= SUMPRODUCT (--( MOD ( COLUMN (A1:Z1)- COLUMN (A1)+1,2)=0),A1:Z1)
Forfatter Dave Bruns


^