Bralec mi je ta teden poslal zanimivo težavo s formulo, zato sem mislil, da jo bom delil kot izziv formule. Težava je v tem:
Imate fiksno mesečno plačilo, datum začetka in določeno število mesecev. Katero formulo lahko uporabite za seštevanje celotnih plačil po letih na podlagi naslednjega delovnega lista:
Z drugimi besedami, katera formula deluje v E5, kopirana v I5, da dobimo vsoto za vsako prikazano leto?
Sama sem pripravila formulo, vendar bi rada videla tudi vaše ideje. Če vas zanima, pustite komentar s formulo, ki jo predlagate.
V svoji formuli lahko uporabite naslednje imenovane obsege, če želite: mos (C5), znesek (C6), začetek (C7), konec (C8).
Spodnji delovni list lahko prenesete.
Odgovor (kliknite za razširitev)Toliko odličnih formul! Hvala vsem, ki ste si vzeli čas za oddajo odgovora. Spodaj so moje razburjene misli o težavi in nekatere spodnje rešitve.
Opomba: Nikoli nisem pojasnil, kako je treba obravnavati mesečne meje. Kot primer sem samo sledil drugemu delovnemu listu. Ključni podatki so 30 plačil od 1. marca: 10 plačil v letu 2017, 12 plačil v letu 2018 in 8 plačil (stanje) v letu 2019.
Če se torej trudite razumeti, kako bi lahko poskušali rešiti takšno težavo, se najprej osredotočite na plačila. Ko enkrat veste, koliko plačil je na leto, lahko to številko samo pomnožite z zneskom in ste končali.
Torej, kako lahko najdete število plačil v določenem letu? V spodnjih komentarjih boste našli veliko dobrih idej. Opazil sem več vzorcev, nekaj pa sem jih naštel spodaj. To je delo v teku …
Oblikovalni vzorci
IF + AND/OR + YEAR + MONTH
IF je zanesljivo stanje pripravljenosti v toliko formulah in se v številnih predlaganih formulah uporablja za ugotavljanje, ali je leto zanimanja "v mejah" začetnega in končnega datuma. V mnogih primerih je IF kombinacija z ALI ali AND, da ostanejo formule kompaktne.
IFERROR + DATEDIF + MAX + MIN
DATEDIF lahko vrne razliko med dvema datumoma v mesecih, zato je tu ideja uporabiti MAX in MIN (za kratkost, namesto IF) za izračun začetnega datuma in končnega datuma za vsako leto, DATEDIF pa naj dobi mesece med. DATEDIF vrže napako #NUM, ko začetni datum ni manjši od končnega datuma, zato se IFERROR uporablja za zajem napake in vrnitev ničle. Glej formule 闫 强, Arun in David spodaj.
MAX + MIN + YEAR + MONTH
Formuli Roberta in Petra skoraj vse delo opravita z MAX in MIN, brez vidnega IF. Neverjetno. Če je ideja o uporabi MAX in MIN za nadomestitev IF za vas nova, ta članek razlaga koncept.
DAYS360
Funkcija Excel DAYS360 vrne število dni med dvema datumoma na podlagi 360-dnevnega leta. To je način izračuna mesecev na podlagi ideje, da ima vsak mesec 30 dni.
SUM + DATE
To je moj neučinkovit (a eleganten!) Pristop, ki uporablja funkcijo DATE in matrično konstanto s številom za vsak mesec. funkcija DATE zavrti datum za vsak mesec v letu z uporabo konstante matrike, za preverjanje prekrivanja pa se uporablja logična logika.
