Un cititor mi-a trimis săptămâna aceasta o problemă interesantă cu formula, așa că m-am gândit să o împărtășesc ca o provocare cu formula. Problema este următoarea:
Aveți o plată lunară fixă, o dată de începere și un anumit număr de luni. Ce formulă puteți utiliza pentru a suma totală a plăților pe an, pe baza următoarei foi de lucru:
Cu alte cuvinte, ce formulă funcționează în E5, copiată în I5, pentru a obține o sumă pentru fiecare an afișat?
Am inventat eu însăși o formulă, dar mi-ar plăcea să văd și ideile. Dacă sunteți interesat, lăsați un comentariu cu formula pe care o propuneți.
Puteți utiliza următoarele intervale denumite în formula dvs. dacă doriți: mos (C5), suma (C6), start (C7), sfârșit (C8).
Puteți descărca foaia de lucru de mai jos.
Răspundeți (faceți clic pentru a extinde)Atâtea formule grozave! Mulțumim tuturor celor care și-au făcut timp să trimită un răspuns. Mai jos sunt gândurile mele divergente asupra problemei și câteva dintre soluțiile de mai jos.
Notă: nu am clarificat niciodată cum trebuie tratate limitele lunii. Urmăream doar o altă foaie de lucru ca exemplu. Informațiile cheie sunt 30 de plăți, începând cu 1 martie: 10 plăți în 2017, 12 plăți în 2018 și 8 plăți (soldul) în 2019.
Deci, dacă vă chinuiți să înțelegeți cum ați putea încerca să rezolvați o astfel de problemă, concentrați-vă mai întâi pe plăți. După ce știți câte plăți sunt într-un an, puteți înmulți numărul respectiv cu suma și ați terminat.
Deci, cum ați putea găsi numărul de plăți într-un anumit an? În comentariile de mai jos veți găsi o mulțime de idei bune. Există câteva modele pe care le-am observat și le-am enumerat câteva mai jos. Aceasta este o lucrare în curs …
Modele de design
IF + AND/OR + YEAR + MONTH
IF este un standby de încredere în atâtea formule și este folosit în multe dintre formulele sugerate pentru a afla dacă anul de interes este „în limitele” datei de început și de sfârșit. În multe cazuri, IF este combinat cu OR sau AND pentru a menține compacte formule.
IFERROR + DATEDIF + MAX + MIN
DATEDIF poate returna diferența dintre două date în luni, deci ideea de aici este să folosiți MAX și MIN (pentru concizie, în loc de IF) pentru a calcula data de începere și data de sfârșit pentru fiecare an și lăsați DATEDIF să obțină lunile între. DATEDIF aruncă o eroare #NUM când data de începere nu este mai mică decât data de încheiere, astfel încât IFERROR este utilizat pentru a prinde eroarea și a reveni la zero. Vezi mai jos formulele lui 闫 强, Arun și David.
MAX + MIN + YEAR + MONTH
Formulele lui Robert și Peter fac aproape toată munca cu MAX și MIN, fără IF la vedere. Uimitor. Dacă ideea de a folosi MAX și MIN pentru a înlocui IF este nouă pentru dvs., acest articol explică conceptul.
DAYS360
Funcția Excel DAYS360 returnează numărul de zile dintre două date pe baza unui an de 360 de zile. Este un mod de a calcula lunile pe baza ideii că fiecare lună are 30 de zile.
SUM + DATE
Aceasta este abordarea mea ineficientă (dar elegantă!) Folosind funcția DATE și o constantă de matrice cu un număr pentru fiecare lună. funcția DATE creează o dată pentru fiecare lună a unui an folosind o constantă de matrice, iar logica booleană este utilizată pentru a verifica suprapunerea.
