En leser sendte meg et interessant formelproblem denne uken, så tenkte at jeg ville dele det som en formelutfordring. Problemet er dette:
Du har en fast månedlig betaling, en startdato og et gitt antall måneder. Hvilken formel kan du bruke til å summere totale betalinger etter år, basert på følgende regneark:
Med andre ord, hvilken formel fungerer i E5, kopiert til I5, for å få en sum for hvert år som vises?
Jeg fant selv en formel, men jeg vil også gjerne se ideene dine. Hvis du er interessert, legg igjen en kommentar med formelen du foreslår.
Du kan bruke følgende navngitte områder i formelen din hvis du vil: mos (C5), mengde (C6), start (C7), slutt (C8).
Du kan laste ned regnearket nedenfor.
Svar (klikk for å utvide)Så mange flotte formler! Takk til alle som tok seg tid til å sende inn svar. Nedenfor er mine vandrende tanker om problemet og noen av løsningene nedenfor.
Merk: Jeg har aldri avklart hvordan månedegrensene skal håndteres. Jeg fulgte bare et annet regneark som et eksempel. De viktigste opplysningene er 30 betalinger, fra 1. mars: 10 betalinger i 2017, 12 betalinger i 2018 og 8 betalinger (balansen) i 2019.
Så hvis du sliter med å forstå hvordan du kan prøve å løse et problem som dette, må du først fokusere på betalinger. Når du vet hvor mange betalinger det er om året, kan du bare multiplisere tallet med beløpet, og du er ferdig.
Så hvordan kan du finne antall betalinger i et gitt år? I kommentarene nedenfor finner du mange gode ideer. Det er flere mønstre jeg la merke til, og jeg har listet noen få nedenfor. Dette er et pågående arbeid …
Design mønstre
IF + AND/OR + YEAR + MONTH
IF er en pålitelig beredskap i så mange formler, og den brukes i mange av de foreslåtte formlene for å finne ut om året av interesse er "innenfor grensene" for start- og sluttdatoene. I mange tilfeller kombineres IF med OR eller AND for å holde formlene kompakte.
IFERROR + DATEDIF + MAX + MIN
DATEDIF kan returnere forskjellen mellom to datoer i måneder, så ideen her er å bruke MAX og MIN (for kortfattethet, i stedet for IF) for å beregne en startdato og sluttdato for hvert år, og la DATEDIF få månedene mellom. DATEDIF kaster en #NUM feil når startdatoen ikke er mindre enn sluttdatoen, så IFERROR brukes til å fange feilen og returnere null. Se formler av 闫 强, Arun og David nedenfor.
MAX + MIN + YEAR + MONTH
Robert og Peters formler gjør nesten alt arbeidet med MAX og MIN, uten IF i sikte. Fantastisk. Hvis ideen om å bruke MAX og MIN til å erstatte IF er ny for deg, forklarer denne artikkelen konseptet.
DAYS360
Excel DAYS360-funksjonen returnerer antall dager mellom to datoer basert på et 360-dagers år. Det er en måte å beregne måneder basert på ideen om at hver måned har 30 dager.
SUM + DATE
Dette er min ineffektive (men elegante!) Tilnærming ved bruk av DATE-funksjonen og en matrisekonstant med et tall for hver måned. DATE-funksjonen spinner opp en dato for hver måned i året ved hjelp av en matrisekonstant, og boolsk logikk brukes til å sjekke overlapping.
