Čitateľ mi tento týždeň poslal zaujímavý problém so vzorcom, takže som si myslel, že ho budem zdieľať ako výzvu so vzorcom. Problém je v tomto:
Máte pevnú mesačnú splátku, dátum začatia a daný počet mesiacov. Aký vzorec môžete použiť na sčítanie celkových platieb podľa rokov na základe nasledujúceho pracovného hárka:
Inými slovami, aký vzorec funguje v E5 a skopírovaný do I5, aby sa získala suma za každý zobrazený rok?
Sama som vymyslela vzorec, ale rada si pozriem aj vaše nápady. Ak máte záujem, zanechajte komentár k vzorcu, ktorý navrhujete.
Ak chcete, môžete vo svojom vzorci použiť nasledujúce pomenované rozsahy: mos (C5), množstvo (C6), začiatok (C7), koniec (C8).
Nižšie si môžete stiahnuť pracovný list.
Odpoveď (kliknutím rozbalíte)Toľko skvelých vzorcov! Ďakujeme všetkým, ktorí si našli čas na odoslanie odpovede. Ďalej uvádzam moje neslušné myšlienky o probléme a niektoré z riešení uvedených nižšie.
Poznámka: Nikdy som nevyjasnil, ako by sa malo narábať s hranicami mesiacov. Iba som sledoval ďalší pracovný hárok ako príklad. Kľúčovou informáciou je 30 platieb od 1. marca: 10 platieb v roku 2017, 12 platieb v roku 2018 a 8 platieb (zostatok) v roku 2019.
Ak teda ťažko pochopíte, ako by ste sa mohli pokúsiť vyriešiť takýto problém, zamerajte sa najskôr na platby. Keď viete, koľko platieb je za rok, stačí toto číslo vynásobiť sumou a máte hotovo.
Ako by ste teda zistili počet platieb v danom roku? V komentároch nižšie nájdete veľa dobrých nápadov. Existuje niekoľko vzorov, ktoré som si všimol, a niekoľko z nich som uviedol nižšie. Toto je nedokončená práca…
Dizajnové vzory
IF + AND/OR + YEAR + MONTH
IF je dôveryhodná pohotovostná funkcia v toľkých vzorcoch a v mnohých navrhovaných vzorcoch sa používa na zistenie, či je rok záujmu „v medziach“ dátumu začatia a ukončenia. V mnohých prípadoch je IF kombinovaný s OR alebo AND, aby boli vzorce kompaktné.
IFERROR + DATEDIF + MAX + MIN
DATEDIF môže vrátiť rozdiel medzi dvoma dátumami v mesiacoch, takže tu je myšlienka použiť MAX a MIN (pre krátkosť, namiesto IF) na výpočet počiatočného a konečného dátumu pre každý rok a nechať DATEDIF dostať mesiace medzi. DATEDIF hodí chybu # ČÍSLO, ak dátum začatia nie je menší ako dátum ukončenia, takže na zachytenie chyby a vrátenie nuly sa použije funkcia IFERROR. Nižšie nájdete vzorce od 闫 un, Aruna a Davida.
MAX + MIN + YEAR + MONTH
Robertove a Peterove vzorce robia takmer všetku prácu s MAX a MIN, bez IF v dohľade. Úžasný. Ak je pre vás myšlienka použitia MAX a MIN na nahradenie IF nová, tento článok vysvetľuje tento koncept.
DAYS360
Funkcia Excel DAYS360 vracia počet dní medzi dvoma dátumami na základe 360-dňového roku. Je to spôsob výpočtu mesiacov na základe myšlienky, že každý mesiac má 30 dní.
SUM + DATE
Toto je môj neefektívny (ale elegantný!) Prístup využívajúci funkciu DATE a konštantu poľa s číslom pre každý mesiac. funkcia DATE roztáča dátum pre každý mesiac v roku pomocou konštanty poľa a na kontrolu prekrytia sa používa logická logika.
