Lukija lähetti minulle tällä viikolla mielenkiintoisen kaavaongelman, joten ajattelin jakaa sen kaavan haasteena. Ongelma on tämä:
Sinulla on kiinteä kuukausimaksu, aloituspäivä ja tietty kuukausimäärä. Millä kaavalla voit laskea kokonaismaksut vuosittain seuraavan laskentataulukon perusteella:
Toisin sanoen mikä kaava toimii E5: ssä, kopioitu I5: ään, saadaksesi summan jokaisesta näytetystä vuodesta?
Itse keksin kaavan, mutta haluaisin nähdä myös teidän ideoitanne. Jos olet kiinnostunut, jätä kommentti ehdottamallasi kaavalla.
Voit käyttää kaavassasi seuraavia nimettyjä alueita, jos haluat: mos (C5), määrä (C6), alku (C7), loppu (C8).
Voit ladata laskentataulukon alla.
Vastaa (laajenna napsauttamalla)Niin monia hienoja kaavoja! Kiitos kaikille, jotka viettivät aikaa vastauksen lähettämiseen. Alla on hämmentäviä ajatuksiani ongelmasta ja joitain alla olevia ratkaisuja.
Huomaa: En ole koskaan selvittänyt, miten kuukausirajoja tulisi käsitellä. Seurasin vain toista laskentataulukkoa esimerkkinä. Niiden avaintiedot ovat 30 maksua 1. maaliskuuta alkaen: 10 maksua vuonna 2017, 12 maksua vuonna 2018 ja 8 maksua (saldo) vuonna 2019.
Joten, jos yrität ymmärtää, miten voit yrittää ratkaista tällaisen ongelman, keskity ensin maksuihin. Kun tiedät kuinka monta maksua vuodessa on, voit vain kertoa kyseisen määrän summalla ja olet valmis.
Joten kuinka voit löytää maksujen määrän tiettynä vuonna? Alla olevista kommenteista löydät paljon hyviä ideoita. Huomasin useita malleja, ja olen luetellut muutaman alla. Tämä on keskeneräinen työ …
Suunnittelumalleja
IF + AND/OR + YEAR + MONTH
IF on luotettava valmiustila niin monissa kaavoissa, ja sitä käytetään monissa ehdotetuissa kaavoissa selvittääkseen, onko kiinnostava vuosi aloitus- ja lopetuspäivän rajoissa. Monissa tapauksissa IF yhdistetään OR- tai AND-kaavojen kanssa, jotta kaavat pysyvät tiiviinä.
IFERROR + DATEDIF + MAX + MIN
DATEDIF voi palauttaa kahden päivämäärän eron kuukausina, joten ajatuksena on käyttää MAX- ja MIN-arvoja (lyhyyyden sijasta IF: n sijasta) laskemaan kunkin vuoden aloitus- ja lopetuspäivä ja antamaan DATEDIF: n saada kuukaudet. DATEDIF heittää #NUM-virheen, kun aloituspäivä ei ole pienempi kuin lopetuspäivä, joten IFERRORia käytetään virheen havaitsemiseen ja nollan palauttamiseen. Katso alla olevat 闫 强: n, Arunin ja Davidin kaavat.
MAX + MIN + YEAR + MONTH
Robertin ja Peterin kaavat tekevät melkein kaiken työn MAX: n ja MIN: n kanssa, ilman IF: tä. Hämmästyttävä. Jos ajatus MAX: n ja MIN: n käyttämisestä IF: n korvaamiseen on sinulle uusi, tässä artikkelissa selitetään käsite.
DAYS360
Excel DAYS360 -funktio palauttaa kahden päivämäärän välisten päivien määrän 360 päivän vuodessa. Se on tapa laskea kuukausia ajatuksen perusteella, että jokaisella kuukaudella on 30 päivää.
SUM + DATE
Tämä on tehoton (mutta tyylikäs!) Lähestymistapani, kun käytän PÄIVÄYS-funktiota ja taulukon vakiota, jonka numero on jokaiselle kuukaudelle. DATE-funktio kehittää päivämäärän vuoden jokaiselle kuukaudelle taulukon vakion avulla, ja loogista logiikkaa käytetään päällekkäisyyksien tarkistamiseen.
