Egy olvasó küldött nekem egy érdekes képletproblémát a héten, ezért úgy gondolta, hogy megosztom képletkihívásként. A probléma a következő:
Van egy fix havi kifizetése, egy kezdési dátuma és egy adott hónapok száma. Milyen képlettel alkalmazhatja a teljes kifizetést évenként az alábbi munkalap alapján:
Más szavakkal, melyik képlet működik az E5-ben, átmásolva az I5-be, hogy összeget kapjunk minden bemutatott évre?
Magam is kitaláltam egy képletet, de szívesen megnézném az ötleteit is. Ha érdekel, hagyjon megjegyzést az Ön által javasolt képlettel.
Ha szeretné, a következő megnevezett tartományokat használhatja a képletében: mos (C5), összeg (C6), kezdet (C7), vég (C8).
Az alábbi munkalapot letöltheti.
Válasz (kattintson a kibontáshoz)Annyi nagyszerű képlet! Köszönet mindenkinek, aki időt szakított a válasz benyújtására. Az alábbiakban bemutatom a problémával kapcsolatos, és néhány alábbi megoldást.
Megjegyzés: Soha nem tisztáztam, hogyan kell kezelni a hónap határait. Csak egy másik munkalapot követtem példaként. Alapvető információ: március 1-jétől kezdődően 30 fizetés: 10 befizetés 2017-ben, 12 befizetés 2018-ban és 8 kifizetés (az egyenleg) 2019-ben.
Tehát, ha nehezen érti, hogyan próbálhatja meg megoldani egy ilyen problémát, akkor először a fizetésekre koncentráljon. Miután megtudta, hogy hány kifizetés van egy évben, egyszerűen megszorozhatja ezt a számot az összeggel, és kész.
Szóval, hogyan találhatja meg az adott évben a kifizetések számát? Az alábbi megjegyzésekben sok jó ötletet talál. Számos mintát észleltem, és az alábbiakban felsoroltam néhányat. Ez egy folyamatban lévő munka …
Tervezési minták
IF + AND/OR + YEAR + MONTH
Az IF megbízható készenlét annyi képletben, és a javasolt képletek közül sokban használják annak kiderítésére, hogy az érdeklődési év a határokon belül van-e a kezdési és a befejezési dátumra. Sok esetben az IF-t OR vagy AND-vel kombinálják, hogy a képletek kompaktak maradjanak.
IFERROR + DATEDIF + MAX + MIN
A DATEDIF megadhatja két dátum közötti különbséget hónapokban, ezért az az ötlet, hogy a MAX és a MIN (rövidítés helyett IF helyett) értékeket használjuk az egyes évek kezdő és befejező dátumának kiszámításához, és hagyjuk, hogy a DATEDIF megkapja a közti hónapokat. A DATEDIF #NUM hibát dob, ha a kezdő dátum nem kevesebb, mint a befejezés dátuma, ezért az IFERROR-t használják a hiba felfogására és a nulla visszaadására. Lásd alább 闫 强, Arun és David képleteit.
MAX + MIN + YEAR + MONTH
Robert és Peter képletei szinte az összes munkát MAX-mal és MIN-mel végzik, IF nem látható. Elképesztő. Ha új ötlet a MAX és MIN használata az IF helyettesítésére, ez a cikk elmagyarázza a koncepciót.
DAYS360
Az Excel DAYS360 függvény 360 napos év alapján adja vissza a két dátum közötti napok számát. Ez a hónapok kiszámításának módja azon az elképzelésen alapul, hogy minden hónapnak 30 napja van.
SUM + DATE
Ez az én nem hatékony (de elegáns!) Megközelítésem, amely a DATE függvényt és egy tömbállandót használ, számmal minden hónapban. a DATE függvény egy tömbállandó segítségével felpörget egy dátumot az év minden hónapjára, és az átfedés ellenőrzésére logikai logikát használunk.
