Čtenář mi tento týden zaslal zajímavý problém se vzorcem, a tak jsem si myslel, že ho budu sdílet jako výzvu se vzorci. Problém je v tomto:
Máte pevnou měsíční platbu, datum zahájení a daný počet měsíců. Jaký vzorec můžete použít k součtu celkových plateb podle roku na základě následujícího listu:
Jinými slovy, jaký vzorec funguje v E5, zkopírovaný do I5, aby získal částku za každý zobrazený rok?
Sám jsem přišel na vzorec, ale rád bych viděl i vaše nápady. Máte-li zájem, zanechte komentář s vámi navrhovaným vzorcem.
Ve svém vzorci můžete použít následující pojmenované rozsahy: mos (C5), částka (C6), start (C7), konec (C8).
Níže uvedený list si můžete stáhnout.
Odpověď (kliknutím rozbalíte)Tolik skvělých vzorců! Děkuji všem, kteří si našli čas na odpověď. Níže jsou moje nesourodé myšlenky na problém a některá níže uvedená řešení.
Poznámka: Nikdy jsem nevyjasnil, jak by se mělo zacházet s hranicemi měsíců. Jen jsem sledoval jiný list jako příklad. Klíčovou informací je 30 plateb, počínaje 1. březnem: 10 plateb v roce 2017, 12 plateb v roce 2018 a 8 plateb (zůstatek) v roce 2019.
Pokud se tedy snažíte porozumět tomu, jak se můžete pokusit vyřešit takový problém, zaměřte se nejprve na platby. Jakmile zjistíte, kolik plateb je za rok, stačí toto číslo vynásobit částkou a máte hotovo.
Jak tedy můžete zjistit počet plateb v daném roce? V komentářích níže najdete spoustu dobrých nápadů. Existuje několik vzorů, které jsem si všiml, a několik z nich jsem uvedl níže. Toto je nedokončená výroba…
Designové vzory
IF + AND/OR + YEAR + MONTH
IF je důvěryhodná pohotovost v tolika vzorcích a v mnoha navrhovaných vzorcích se používá k určení, zda je rok zájmu „v mezích“ data zahájení a ukončení. V mnoha případech je IF kombinován s OR nebo AND, aby byly vzorce kompaktní.
IFERROR + DATEDIF + MAX + MIN
DATEDIF může vrátit rozdíl mezi dvěma daty v měsících, takže zde je myšlenka použít MAX a MIN (pro stručnost, místo IF) k výpočtu počátečního a koncového data pro každý rok a nechat DATEDIF získat měsíce mezi. DATEDIF vyvolá chybu # ČÍSLO, když počáteční datum není menší než konečné datum, takže IFERROR se používá k zachycení chyby a vrácení nuly. Viz vzorce od 闫 强, Aruna a Davida níže.
MAX + MIN + YEAR + MONTH
Robert a Peterovy vzorce dělají téměř veškerou práci s MAX a MIN, bez IF v dohledu. Úžasný. Pokud je pro vás myšlenka použití MAX a MIN k nahrazení IF nová, vysvětluje tento článek tento koncept.
DAYS360
Funkce Excel DAYS360 vrací počet dní mezi dvěma daty na základě 360denního roku. Jedná se o způsob výpočtu měsíců na základě myšlenky, že každý měsíc má 30 dní.
SUM + DATE
Toto je můj neefektivní (ale elegantní!) Přístup pomocí funkce DATE a konstanty pole s číslem pro každý měsíc. funkce DATE roztočí datum pro každý měsíc roku pomocí konstanty pole a logická logika se používá ke kontrole překrytí.
