Un lettore mi ha inviato un interessante problema di formula questa settimana, quindi ho pensato di condividerlo come una sfida di formula. Il problema è questo:
Hai un pagamento mensile fisso, una data di inizio e un determinato numero di mesi. Quale formula puoi utilizzare per sommare i pagamenti totali per anno, in base al seguente foglio di lavoro:
In altre parole, quale formula funziona in E5, copiata in I5, per ottenere una somma per ogni anno mostrato?
Ho escogitato una formula io stesso, ma mi piacerebbe anche vedere le tue idee. Se ti interessa, lascia un commento con la formula che proponi.
Se lo desideri, puoi utilizzare i seguenti intervalli denominati nella formula: mos (C5), importo (C6), inizio (C7), fine (C8).
Puoi scaricare il foglio di lavoro qui sotto.
Rispondi (fai clic per espandere)Tante fantastiche formule! Grazie a tutti coloro che hanno dedicato del tempo per inviare una risposta. Di seguito sono riportati i miei pensieri vaghi sul problema e alcune delle soluzioni di seguito.
Nota: non ho mai chiarito come gestire i limiti dei mesi. Stavo solo seguendo un altro foglio di lavoro come esempio. Le informazioni chiave sono 30 pagamenti, a partire dal 1 ° marzo: 10 pagamenti nel 2017, 12 pagamenti nel 2018 e 8 pagamenti (il saldo) nel 2019.
Quindi, se stai lottando per capire come potresti provare a risolvere un problema come questo, concentrati prima sui pagamenti. Una volta che sai quanti pagamenti sono in un anno, puoi semplicemente moltiplicare quel numero per l'importo e il gioco è fatto.
Quindi, come potresti trovare il numero di pagamenti in un dato anno? Nei commenti qui sotto troverai molte buone idee. Ci sono diversi modelli che ho notato e ne ho elencati alcuni di seguito. Si tratta di un lavoro in corso…
Modelli di progettazione
IF + AND/OR + YEAR + MONTH
IF è un affidabile supporto in tante formule, ed è utilizzato in molte delle formule suggerite per capire se l'anno di interesse è "nei limiti" delle date di inizio e di fine. In molti casi, IF è combinato con OR o AND per mantenere le formule compatte.
IFERROR + DATEDIF + MAX + MIN
DATEDIF può restituire la differenza tra due date in mesi, quindi l'idea qui è di usare MAX e MIN (per brevità, invece di IF) per calcolare una data di inizio e una data di fine per ogni anno e lasciare che DATEDIF ottenga i mesi tra. DATEDIF genera un errore #NUM quando la data di inizio non è inferiore alla data di fine, quindi IFERROR viene utilizzato per rilevare l'errore e restituire zero. Vedi le formule di 闫 强, Arun e David sotto.
MAX + MIN + YEAR + MONTH
Le formule di Robert e Peter fanno quasi tutto il lavoro con MAX e MIN, senza IF in vista. Sorprendente. Se l'idea di utilizzare MAX e MIN per sostituire IF è nuova per te, questo articolo spiega il concetto.
DAYS360
La funzione Excel DAYS360 restituisce il numero di giorni tra due date in base a un anno di 360 giorni. È un modo per calcolare i mesi basato sull'idea che ogni mese ha 30 giorni.
SUM + DATE
Questo è il mio approccio inefficiente (ma elegante!) Utilizzando la funzione DATE e una costante di matrice con un numero per ogni mese. la funzione DATE imposta una data per ogni mese dell'anno utilizzando una costante di matrice e la logica booleana viene utilizzata per verificare la sovrapposizione.
