In questo articolo, impareremo come ottenere l'ultima voce per mese in Excel.
Scenario:
Quando si lavora con set di dati, a volte è necessario estrarre alcuni risultati in base a criteri diversi. Qui il criterio del problema è che il valore deve essere l'ultima voce corrispondente a un determinato mese. Il problema può essere interpretato in due modi.
- Ultima voce in colonna per mese
- Ultimo valore della data del mese in colonna
Devi pensare qual è la differenza tra i due. La differenza è che il primo problema estrae l'ultima voce dalla colonna che corrisponde al nome del mese mentre il secondo problema estrae il valore della data più vicino dal valore della data di fine mese. Capiremo entrambe le situazioni una per una. In primo luogo considereremo come ottenere l'ultima voce in colonna per mese.
Come ottenere l'ultima voce nella colonna per mese in Excel?
Per questo utilizzeremo la funzione TESTO e la funzione CERCA. Qui usiamo un attributo della funzione CERCA per cercare l'ultimo valore. La funzione CERCA accetta 3 argomenti valore di ricerca, matrice di ricerca e matrice di risultati. Quindi usiamo l'argomento array di ricerca come array 1/lookup. Per maggiori dettagli, vedere la sintassi della formula seguente:
sintassi della formula:
| =CERCA(2,1/(TESTO(date,"mmaaaa")=TESTO(mese,"maaaa")),valori) |
date: array di date in data
valori: array di risultati nei dati
mese : criterio mese
Esempio :
Tutti questi potrebbero essere fonte di confusione da capire. Capiamo come utilizzare questa formula in un esempio. Qui abbiamo i dati con i valori di data e prezzo. Per questo abbiamo bisogno dell'ultima voce del mese di gennaio 2019 che sarà l'ultima voce con la data di gennaio 2019. Qui per l'array di date e l'array di prezzo abbiamo usato gli intervalli denominati.
Usa la formula:
| =CERCA(2,1/(TESTO(date,"maaaa")=TESTO(F9,"maaaa")),Prezzo) |
Spiegazione:
- TEXT(dates,"mmyyyy") restituirà l'array di valori nel formato "mmyyyy" dopo averlo convertito in valori di testo che è
{ "012019"; "012019" ; "012019" ; "012019" ; "022019" ; "022019" ; "022019" ; "022019" ; "032019" ; "032019" ; "032019" ; "032019" ; "032019" }
- TEXT(F9,"mmyyyy") restituirà il valore della data (nella cella F9) nel formato "mmyyyy" dopo aver convertito il valore della data di ricerca che è "012019" e questo valore verrà abbinato all'array sopra indicato.
- Prendere la divisione con 1 convertirà l'array di valori Veri in 1 e valori Falsi in errore #DIV/0. Quindi otterremo l'array restituito come.
{ 1; 1; 1; 1; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! ; #DIV/0! }
- Ora la funzione CERCA trova il valore 2 nell'array che non verrà trovato. Quindi restituisce la voce corrispondente all'ultimo valore 1.
Intervalli denominati utilizzati
date: C3:C15
Prezzo: G3: G15
L'ultimo Prezzo è 78,48 corrispondente al 31-01-2019. Copia la formula in altre celle usando Ctrl + D o trascinando verso il basso dall'angolo in basso a destra della cella.
Come puoi vedere la formula restituisce tutti i valori richiesti. La funzione restituisce l'errore #N/D, se il valore del mese di ricerca non corrisponde a nessuna voce di data. Nell'esempio sopra le voci della data sono ordinate. Quindi l'ultima voce del mese corrisponde all'ultima voce del mese. Ora, se le voci della data non sono ordinate, prima ordineremo i valori della data e utilizzeremo la formula sopra.
Come ottenere la voce Ultima data per mese nella colonna in Excel?
Per questo utilizzeremo la funzione VLOOKUP e EOMONTH. La funzione EOMONTH prende il valore della data del mese e restituisce l'ultima data del mese richiesto. La funzione CERCA.VERT trova l'ultima data del mese o la data precedente più vicina e restituisce un valore corrispondente a quel valore.
Usa la formula:
| = CERCA.VERT ( EOMESE (F3,0), tabella, 2) |
Spiegazione :
- EOMESE (F3,0) restituisce l'ultima data dello stesso mese. 0 argomento utilizzato per restituire la data dello stesso mese.
- Ora il valore di ricerca diventa l'ultima data del mese specificato.
- Ora il valore verrà cercato nella prima colonna della tabella e cercherà l'ultima data, se trovato restituisce il valore corrispondente al valore e se non trovato restituisce l'ultimo risultato corrispondente, più vicino al valore di ricerca e restituisce il risultato corrispondente.
- La funzione restituisce il valore dalla seconda colonna della tabella poiché il terzo argomento è 2.
Qui la formula restituisce il 78.48 come risultato. Ora copia la formula in altre celle usando la scorciatoia Ctrl + D o trascinando verso il basso dall'angolo in basso a destra della cella.
Come puoi vedere la formula funziona bene. C'è solo un problema. Quando si cerca l'ultima data corrispondente al mese di aprile, la funzione restituisce il valore corrispondente a marzo, poiché nella tabella non è presente la data di aprile. Assicurati di catturare questi risultati.
Ecco tutte le note di osservazione sull'uso della formula.
Appunti :
- Utilizzare la formula corrispondente al problema indicato nell'articolo.
- Le funzioni CERCA.VERT e CERCA sono funzioni quando il valore di ricerca è numero e non è presente nell'array di ricerca, restituisce il valore corrispondente solo al numero più vicino inferiore al valore di ricerca.
- Excel archivia i valori della data come numeri.
- Le due funzioni sopra indicate restituiscono un solo valore.
- Usa il 4° argomento nella funzione CERCA.VERT come 0 per ottenere la corrispondenza esatta del valore di ricerca nella tabella.
Spero che questo articolo su Come ottenere l'ultima voce per mese in Excel sia esplicativo. Trova altri articoli sull'estrazione di valori dalla tabella utilizzando le formule qui. Se ti è piaciuto il nostro blog, condividilo con i tuoi amici su Facebook. E puoi anche seguirci su Twitter e Facebook. Ci piacerebbe avere tue notizie, facci sapere come possiamo migliorare, completare o innovare il nostro lavoro e renderlo migliore per te. Scrivici al sito di posta elettronica
Trova l'ultima riga con dati misti in Excel : lavorare con numeri, testo o celle vuote nello stesso array. Estrai l'ultima riga con una cella non vuota usando la funzione CONFRONTA in Excel.
Trovare l'ultimo giorno di un dato mese : la funzione EOMONTH restituisce l'ultima data del mese, del valore di data specificato.
Come ottenere l'ultimo valore nella colonna : Ultima voce in un dato di grandi dimensioni, una combinazione della funzione CELLA e INDICE restituisce l'ultimo valore in una colonna o in un elenco di dati.
Differenza con l'ultima cella non vuota : prende la differenza alternativa dall'ultimo valore nell'elenco con i numeri usando la funzione SE e CERCA in Excel.
Trova l'ultima riga di dati con valori di testo in Excel : In una matrice di valori di testo e celle vuote, restituisci l'ultimo valore nella matrice utilizzando la funzione CONFRONTA e RIPETI in Excel.
Come utilizzare la funzione SUMPRODUCT in Excel: restituisce la SOMMA dopo aver preso il prodotto dei valori corrispondenti in più matrici in Excel.
Come usare i caratteri jolly in Excel : Ricerca, conteggio, somma, media e altre operazioni matematiche su celle che corrispondono a frasi utilizzando i caratteri jolly in Excel.
Articoli popolari:
Come usare la funzione SE in Excel : l'istruzione IF in Excel controlla la condizione e restituisce un valore specifico se la condizione è VERA o restituisce un altro valore specifico se FALSO.
Come utilizzare la funzione CERCA.VERT in Excel : questa è una delle funzioni più utilizzate e popolari di excel che viene utilizzata per cercare il valore da diversi intervalli e fogli.
Come utilizzare la funzione CONTA.SE in Excel : Conta i valori con le condizioni utilizzando questa straordinaria funzione. Non è necessario filtrare i dati per contare valori specifici. La funzione Countif è essenziale per preparare la tua dashboard.
Come utilizzare la funzione SOMMA.SE in Excel : Questa è un'altra funzione essenziale della dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.