Yleinen kaava
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(start&":"&end)))=dow))
Yhteenveto
Jos haluat laskea viikonpäivät (maanantaisin, perjantaisin, sunnuntaisin jne.) Kahden päivämäärän välillä, voit käyttää matriisikaavaa, joka käyttää useita toimintoja: SUMPRODUCT, WEEKDAY, ROW ja INDIRECT. Esitetyssä esimerkissä solun E6 kaava on
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B6&":"&C6)))=D6))
Kaavan yleisessä versiossa alku = aloituspäivä, loppu = lopetuspäivä ja dow = viikonpäivä.
Selitys
Ytimessä tämä kaava käyttää WEEKDAY-funktiota useiden päivämäärien testaamiseen sen selvittämiseksi, laskeeko ne tietylle viikonpäivälle (dow), ja SUMPRODUCT-funktiolle, jotta summa lasketaan yhteen.
Annettuna päivämäärä WEEKDAY palauttaa yksinkertaisesti numeron välillä 1 ja 7, joka vastaa tiettyä viikonpäivää. Oletusasetuksilla 1 = sunnuntai ja 7 = lauantai. Joten 2 = maanantai, 6 = perjantai ja niin edelleen.
Tämän kaavan temppu on ymmärtäminen, että Excelin päivämäärät ovat vain sarjanumeroita, jotka alkavat 1.1.1900. Esimerkiksi 1. tammikuuta 2016 on sarjanumero 42370 ja 8. tammikuuta 42377. Excelin päivämäärät näyttävät päivämääriltä vain, kun päiväysnumeromuotoa käytetään.
Joten kysymys tulee - miten voit luoda joukon päivämääriä, jotka voit syöttää WEEKDAY-toimintoon saadaksesi selville vastaavat viikonpäivät?
Vastaus on käyttää RIVIÄ epäsuorien toimintojen kanssa, kuten:
ROW(INDIRECT(date1&":"&date2))
INDIRECT sallii ketjutettujen päivämäärien "42370: 42377" tulkinnan rivinumeroina. Sitten ROW-funktio palauttaa seuraavanlaisen taulukon:
(42370;42371;42372;42373;42374;42375;42376;42377)
WEEKDAY-toiminto arvioi nämä numerot päivämääriksi ja palauttaa tämän taulukon:
(6;7;1;2;3;4;5;6)
joka testataan tiettyä viikonpäivää vastaan (tässä tapauksessa 6, D6: sta). Kun testin tulokset on muutettu yhdeksi ja 0: ksi kaksoisviivalla, SUMPRODUCT käsittelee tämän ryhmän
(1;0;0;0;0;0;0;1)
Joka palauttaa 2.
SEQUENCE: llä
Uuden SEQUENCE-funktion avulla tämä kaava voi yksinkertaistua tällä tavoin:
=SUMPRODUCT(--(WEEKDAY(SEQUENCE(end-start+1,1,start,1))=dow))
Tässä versiossa käytämme SEQUENCE-sovellusta päivämäärämallin luomiseen suoraan, ilman epäsuoraa tai riviä.
