Een lezer stuurde me deze week een interessant formuleprobleem, dus ik dacht dat ik het zou delen als een formule-uitdaging. Het probleem is dit:
Je hebt een vast maandbedrag, een startdatum en een bepaald aantal maanden. Welke formule kunt u gebruiken om de totale betalingen per jaar op te tellen, op basis van het volgende werkblad:
Met andere woorden, welke formule werkt in E5, gekopieerd naar I5, om een bedrag voor elk getoond jaar te krijgen?
Ik heb zelf een formule bedacht, maar ik zou ook graag jouw ideeën willen zien. Als u geïnteresseerd bent, laat dan een opmerking achter met de formule die u voorstelt.
U kunt desgewenst de volgende benoemde bereiken in uw formule gebruiken: mos (C5), amount (C6), start (C7), end (C8).
U kunt het onderstaande werkblad downloaden.
Antwoord (klik om uit te vouwen)Zoveel geweldige formules! Dank aan iedereen die de tijd heeft genomen om een antwoord in te dienen. Hieronder staan mijn gedachten over het probleem en enkele van de onderstaande oplossingen.
Opmerking: ik heb nooit duidelijk gemaakt hoe met maandgrenzen moet worden omgegaan. Ik volgde gewoon een ander werkblad als voorbeeld. De belangrijkste informatie is 30 betalingen, beginnend op 1 maart: 10 betalingen in 2017, 12 betalingen in 2018 en 8 betalingen (het saldo) in 2019.
Dus als u moeite heeft om te begrijpen hoe u een probleem als dit zou kunnen proberen op te lossen, concentreer u dan eerst op betalingen. Als u eenmaal weet hoeveel betalingen er in een jaar zijn, kunt u dat aantal vermenigvuldigen met het bedrag en u bent klaar.
Dus, hoe zou u het aantal betalingen in een bepaald jaar kunnen vinden? In de reacties hieronder vind je veel goede ideeën. Er zijn verschillende patronen die ik heb opgemerkt, en ik heb er hieronder een paar opgesomd. Dit is een work in progress …
Ontwerp patronen
IF + AND/OR + YEAR + MONTH
IF is een betrouwbare stand-by in zoveel formules, en het wordt in veel van de voorgestelde formules gebruikt om erachter te komen of het interessante jaar binnen de grenzen van de begin- en einddatum valt. In veel gevallen wordt IF gecombineerd met OR of AND om formules compact te houden.
IFERROR + DATEDIF + MAX + MIN
DATEDIF kan het verschil tussen twee datums in maanden retourneren, dus het idee hier is om MAX en MIN te gebruiken (kortheidshalve, in plaats van IF) om een startdatum en einddatum voor elk jaar te berekenen, en DATEDIF de tussenliggende maanden te laten krijgen. DATEDIF genereert een #GETAL-fout als de startdatum niet minder is dan de einddatum, dus IFERROR wordt gebruikt om de fout op te vangen en nul te retourneren. Zie onderstaande formules van 闫 强, Arun en David.
MAX + MIN + YEAR + MONTH
De formules van Robert en Peter doen bijna al het werk met MAX en MIN, zonder IF in zicht. Verbazingwekkend. Als het idee om MAX en MIN te gebruiken om IF te vervangen nieuw voor je is, legt dit artikel het concept uit.
DAYS360
De Excel-functie DAGEN360 retourneert het aantal dagen tussen twee datums op basis van een jaar van 360 dagen. Het is een manier om maanden te berekenen op basis van het idee dat elke maand 30 dagen heeft.
SUM + DATE
Dit is mijn inefficiënte (maar elegante!) Benadering met behulp van de DATE-functie en een matrixconstante met een getal voor elke maand. de functie DATUM draait een datum voor elke maand van een jaar met behulp van een matrixconstante, en booleaanse logica wordt gebruikt om overlap te controleren.
