Jokaisen n: nnen sarakkeen yhteenlaskemiseksi voit käyttää SUMPRODUCT-, MOD- ja COLUMN -funktioihin perustuvaa kaavaa.
Esitetyssä esimerkissä L5: n kaava on:
laskea kuinka monta kertaa arvo näkyy sarakkeessa
= SUMPRODUCT (--( MOD ( COLUMN (rng)- COLUMN (rng.first)+1,n)=0),rng)Selitys
Käytännössä SUMPRODUCT summaa rivin arvot, jotka on 'suodatettu' käyttäen MOD -logiikkaa.Avain on tämä:
= SUMPRODUCT (--( MOD ( COLUMN (B5:J5)- COLUMN (B5)+1,K5)=0),B5:J5)
Tämä kaavan katkelma käyttää COLUMN -funktiota saadakseen joukon suhteellisia sarakenumeroita alueelle (selitetty yksityiskohtaisesti tässä ) joka näyttää tältä:
{1,2,3,4,5,6,7,8,9}
Tämä menee MODiin seuraavasti:
MOD ( COLUMN (B5:J5)- COLUMN (B5)+1,K5)=0
jossa K5 on N: n arvo jokaisella rivillä. MOD -funktio palauttaa loput jokaisesta sarakkeen numerosta jaettuna N: llä. Joten esimerkiksi kun N = 3, MOD palauttaa jotain seuraavanlaista:
{1,2,0,1,2,0,1,2,0}
Huomaa, että sarakkeissa 3, 6, 9 jne. Näkyy nollia. Kaava käyttää = 0 pakottaakseen TOSI, kun loppuosa on nolla, ja EPÄTOSI, jos ei, käytämme kaksoisnegatiivista (-) pakottaaksesi TOSI ja VÄÄRÄ yhdelle ja nollille. Jäljelle jää tällainen matriisi:
{0,0,1,0,0,1,0,0,1}
Missä 1: t osoittavat nyt n: nnen arvon. Tämä menee SUMPRODUCTiin taulukkona 1 ja B5: J5 taulukkoksi 2. SUMPRODUCT tekee sitten tehtävänsä ensin kertomalla ja sitten laskemalla yhteen matriisituotteet.
Ainoat arvot, jotka selviävät kertolaskuista, ovat niitä, joissa taulukko1 sisältää 1. Tällä tavalla voit ajatella taulukon 1 logiikkaa, joka suodattaa taulukon2 arvot.
Summaa joka toinen sarake
Jos haluat laskea yhteen jokaisen sarakkeen, muokkaa tätä kaavaa tarpeen mukaan. Muista kuitenkin, että kaava määrittää automaattisesti 1 alueen ensimmäiselle sarakkeelle. Jos haluat summata EVEN -sarakkeet, käytä seuraavaa:
kuinka tyhjät rivit poistetaan excel mac: sta
MOD ({1,2,3,4,5,6,7,8,9},K5)=0
Summaa ODD -sarakkeet seuraavasti:
Kirjailija Dave Bruns= SUMPRODUCT (--( MOD ( COLUMN (A1:Z1)- COLUMN (A1)+1,2)=0),A1:Z1)