Čitatelj mi je ovaj tjedan poslao zanimljiv problem s formulom, pa sam pomislio da bih ga podijelio kao izazov za formulu. Problem je sljedeći:
Imate fiksnu mjesečnu uplatu, datum početka i zadani broj mjeseci. Koju formulu možete koristiti za zbrajanje ukupnih uplata po godinama, na temelju sljedećeg radnog lista:
Drugim riječima, koja formula djeluje u E5, kopirana u I5, da bi se dobio zbroj za svaku prikazanu godinu?
I sam sam smislio formulu, ali volio bih vidjeti i vaše ideje. Ako ste zainteresirani, ostavite komentar s formulom koju predlažete.
U svojoj formuli možete koristiti sljedeće imenovane opsege: mos (C5), iznos (C6), početak (C7), kraj (C8).
Radni list možete preuzeti u nastavku.
Odgovor (kliknite za proširenje)Toliko sjajnih formula! Hvala svima koji su odvojili vrijeme za predaju odgovora. Ispod su moja nesretna razmišljanja o problemu i neka od rješenja u nastavku.
Napomena: Nikad nisam pojasnio kako treba postupati s granicama mjeseca. Samo sam slijedio drugi radni list kao primjer. Ključni podaci su 30 plaćanja, počevši od 1. ožujka: 10 plaćanja u 2017. godini, 12 plaćanja u 2018. godini i 8 plaćanja (saldo) u 2019. godini.
Dakle, ako se trudite shvatiti kako biste mogli pokušati riješiti ovakav problem, prvo se usredotočite na plaćanja. Jednom kada znate koliko je uplata godišnje, možete samo pomnožiti taj broj s iznosom i gotovi ste.
Pa, kako možete pronaći broj plaćanja u određenoj godini? U komentarima ispod pronaći ćete puno dobrih ideja. Primijetio sam nekoliko uzoraka, a nekoliko sam naveo u nastavku. Ovo je posao koji je u tijeku …
Uzorci dizajna
IF + AND/OR + YEAR + MONTH
AKO je pouzdano stanje pripravnosti u toliko formula i koristi se u mnogim predloženim formulama kako bi se utvrdilo je li godina od interesa "u granicama" datuma početka i završetka. U mnogim se slučajevima IF kombinira s OR ili AND kako bi formule bile kompaktne.
IFERROR + DATEDIF + MAX + MIN
DATEDIF može vratiti razliku između dva datuma u mjesecima, pa je ovdje ideja koristiti MAX i MIN (za kratkoću, umjesto IF) za izračunavanje datuma početka i završetka za svaku godinu, a DATEDIF treba dobiti mjesece između. DATEDIF baca grešku #NUM kada datum početka nije manji od datuma završetka, pa se IFERROR koristi za hvatanje pogreške i vraćanje nule. Pogledajte dolje formule 闫 强, Arun i David.
MAX + MIN + YEAR + MONTH
Formule Roberta i Petera rade gotovo sav posao s MAX-om i MIN-om, bez IF-a na vidiku. Nevjerojatno. Ako vam je nova ideja da koristite MAX i MIN za zamjenu IF, ovaj članak objašnjava koncept.
DAYS360
Funkcija Excel DAYS360 vraća broj dana između dva datuma na temelju 360-dnevne godine. To je način izračuna mjeseca na temelju ideje da svaki mjesec ima 30 dana.
SUM + DATE
Ovo je moj neučinkovit (ali elegantan!) Pristup koji koristi funkciju DATE i konstantu niza s brojem za svaki mjesec. funkcija DATE vrti datum za svaki mjesec u godini pomoću konstante niza, a logička logika koristi se za provjeru preklapanja.
