En læser sendte mig et interessant formelproblem i denne uge, så jeg tænkte, at jeg ville dele det som en formeludfordring. Problemet er dette:
Du har en fast månedlig betaling, en startdato og et givet antal måneder. Hvilken formel kan du bruge til at opsummere samlede betalinger pr. År baseret på følgende regneark:
Med andre ord, hvilken formel fungerer i E5, kopieret over til I5, for at få en sum for hvert vist år?
Jeg kom selv med en formel, men jeg vil også gerne se dine ideer. Hvis du er interesseret, skal du efterlade en kommentar med den formel, du foreslår.
Du kan bruge følgende navngivne områder i din formel, hvis du vil: mos (C5), mængde (C6), start (C7), slutning (C8).
Du kan downloade regnearket nedenfor.
Svar (klik for at udvide)Så mange gode formler! Tak til alle, der tog sig tid til at indsende et svar. Nedenfor er mine vandrende tanker om problemet og nogle af nedenstående løsninger.
Bemærk: Jeg har aldrig afklaret, hvordan månedsgrænser skal håndteres. Jeg fulgte bare et andet regneark som et eksempel. De vigtigste oplysninger er 30 betalinger, der starter den 1. marts: 10 betalinger i 2017, 12 betalinger i 2018 og 8 betalinger (saldoen) i 2019.
Så hvis du kæmper for at forstå, hvordan du kan prøve at løse et problem som dette, skal du først fokusere på betalinger. Når du ved, hvor mange betalinger der er om et år, kan du bare gange dette tal med beløbet, og du er færdig.
Så hvordan kan du finde antallet af betalinger i et givet år? I kommentarerne nedenfor finder du masser af gode ideer. Der er flere mønstre, jeg har bemærket, og jeg har listet et par nedenfor. Dette er et igangværende arbejde …
Design mønstre
IF + AND/OR + YEAR + MONTH
IF er en pålidelig standby i så mange formler, og den bruges i mange af de foreslåede formler til at finde ud af, om interesseåret er "inden for rammerne" af start- og slutdatoer. I mange tilfælde kombineres IF med OR eller AND for at holde formlerne kompakte.
IFERROR + DATEDIF + MAX + MIN
DATEDIF kan returnere forskellen mellem to datoer i måneder, så ideen her er at bruge MAX og MIN (for kortfattethed i stedet for IF) til at beregne en startdato og slutdato for hvert år og lade DATEDIF få månederne imellem. DATEDIF kaster en #NUM-fejl, når startdatoen ikke er mindre end slutdatoen, så IFERROR bruges til at fange fejlen og returnere nul. Se formler af 闫 强, Arun og David nedenfor.
MAX + MIN + YEAR + MONTH
Robert og Peters formler udfører næsten alt arbejdet med MAX og MIN uden noget IF i sikte. Fantastiske. Hvis ideen om at bruge MAX og MIN til at erstatte IF er ny for dig, forklarer denne artikel konceptet.
DAYS360
Excel DAYS360-funktionen returnerer antallet af dage mellem to datoer baseret på et 360-dages år. Det er en måde at beregne måneder på baggrund af ideen om, at hver måned har 30 dage.
SUM + DATE
Dette er min ineffektive (men elegante!) Tilgang ved hjælp af DATE-funktionen og en matrixkonstant med et tal for hver måned. DATE-funktionen spinder en dato op for hver måned i et år ved hjælp af en arraykonstant, og boolsk logik bruges til at kontrollere overlapning.
