Sommario
Per convertire una spesa in un'unità di tempo (cioè giornaliera, settimanale, mensile, ecc.) In altre unità di tempo, è possibile utilizzare una formula a due vie INDICE e CONFRONTA. Nell'esempio mostrato, la formula in E5 (copiata su e giù) è:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
dove data (O5: S9), vunits (N5: N9) e hunits (O4: S4) sono denominati intervalli, come spiegato di seguito.
Spiegazione
Per convertire una spesa in un'unità di tempo (cioè giornaliera, settimanale, mensile, ecc.) In altre unità di tempo, è possibile utilizzare una formula a due vie INDICE e CONFRONTA. Nell'esempio mostrato, la formula in E5 (copiata su e giù) è:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Questa formula utilizza una tabella di ricerca con intervalli denominati come mostrato di seguito:
Intervalli denominati: dati (O5: S9), vunits (N5: N9) e hunits (O4: S4).
introduzione
L'obiettivo è convertire una spesa in un'unità di tempo in una spesa equivalente in altre unità di tempo. Ad esempio, se abbiamo una spesa mensile di $ 30, vogliamo calcolare una spesa annuale di $ 360, una spesa settimanale di $ 7,50, ecc.
Come tante sfide in Excel, molto dipende da come affronti il problema. Potresti prima essere tentato di considerare una catena di formule IF annidate. Questo può essere fatto, ma ti ritroverai con una formula lunga e complicata.
Un approccio più pulito consiste nel creare una tabella di ricerca che contiene i fattori di conversione per tutte le conversioni possibili, quindi utilizzare una formula a due vie INDICE e CORRISPONDENZA per recuperare il valore richiesto per una determinata conversione. Una volta ottenuto il valore, puoi semplicemente moltiplicarlo per l'importo originale.
La tabella di conversione
La tabella di conversione ha gli stessi valori sia per le etichette verticali che per quelle orizzontali: giornaliera, settimanale, bisettimanale, mensile e annuale. Le unità "da" sono elencate verticalmente e le unità "da" sono elencate orizzontalmente. Ai fini di questo esempio, si desidera abbinare prima la riga, quindi la colonna. Quindi, se vogliamo convertire una spesa mensile in una spesa annuale, abbiniamo la riga "mensile" e la colonna "annuale" e restituiamo 12.
Per popolare la tabella stessa, utilizziamo un mix di semplici formule e costanti:
Nota: personalizza i valori di conversione per soddisfare le tue esigenze specifiche. L'immissione di un valore come = 1/7 è un modo semplice per evitare di immettere valori decimali lunghi.
La formula di ricerca
Poiché dobbiamo individuare un valore di conversione basato su due input, un'unità di tempo "da" e un'unità di tempo "a", abbiamo bisogno di una formula di ricerca bidirezionale. INDICE e CONFRONTA forniscono una buona soluzione. Nell'esempio mostrato, la formula in E5 è:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Lavorando dall'interno verso l'esterno, la prima funzione MATCH individua la riga corretta:
MATCH($D5,vunits,0) // find row, returns 4
Estraiamo l' unità di tempo "da" originale dalla colonna D, che usiamo per trovare la riga a destra nell'intervallo denominato vunits (N5: N9). Nota $ D5 è un riferimento misto con la colonna bloccata, quindi la formula può essere copiata.
La seconda funzione MATCH individua la colonna:
MATCH(F$4,hunits,0) // find column, returns 5
Qui, otteniamo il valore di ricerca dall'intestazione della colonna nella riga 4 e lo usiamo per trovare la colonna giusta "a" nelle unità di intervallo denominate (O4: S4). Ancora una volta, nota F $ 4 è un riferimento misto con la riga bloccata, quindi la formula può essere copiata.
Dopo che entrambe le formule MATCH restituiscono i risultati a INDICE, abbiamo:
=$C5*INDEX(data,4,5)
L'array fornito a INDEX è l'intervallo di dati denominato , (O5: S9). Con una riga di 4 e una colonna di 5, INDICE restituisce 12, quindi otteniamo un risultato finale di 12000 come questo:
=$C5*INDEX(data,4,5) =1000*12 =12000
