Le date sono spesso una parte importante dell'analisi dei dati. Spesso fai domande come quando è stato acquistato il prodotto, quanto tempo impiegherà l'attività per il progetto o qual è il ricavo medio per il trimestre fiscale? L'inserimento delle date corrette è fondamentale per garantire risultati accurati. Tuttavia, per comprendere correttamente questi risultati, è necessario formattare le date per renderle più facili da capire.
Importante: Poiché le regole che regolano l'interpretazione delle date da parte di qualsiasi programma di calcolo sono complesse, dovresti essere il più specifico possibile per le date che inserisci. Ciò garantirà che i calcoli della data siano il più precisi possibile.
Comprensione dei calcoli e dei formati delle date
Excel archivia le date come numeri sequenziali chiamati valori sequenziali. Ad esempio, in Excel per Windows, il 1 gennaio 1900 è ordinale 1 e il 1 gennaio 2008 è ordinale 39448 perché è 39.448 giorni dopo il 1 gennaio 1900.
Excel memorizza i valori temporali come frazioni decimali perché il tempo viene trattato come una frazione di giorno. Un numero decimale è un valore nell'intervallo da 0 (zero) a 0,99999999, che rappresenta un orario compreso tra 0:00:00 (12:00:00) e 23:59:59 (23:59:59).
Poiché i valori di data e ora sono valori, possono essere aggiunti, sottratti e inclusi in altri calcoli. È possibile visualizzare una data come valore ordinale e un'ora come frazione decimale modificando il formato di una cella contenente una data o un'ora in un formato generale.
Comprendere i due sistemi di data
Excel per Mac ed Excel per Windows supportano i sistemi di data 1900 e 1904. Il sistema di data predefinito per Excel per Windows è 1900; e il sistema di data predefinito per Excel per Mac è 1904.
Excel per Windows è stato originariamente basato sul sistema di data 1900 perché ha migliorato la compatibilità con altri programmi di fogli di calcolo sviluppati per il sistema operativo MS-DOS e Microsoft Windows e quindi è diventato il sistema di data standard. Excel per Mac era originariamente basato sul sistema di data del 1904 perché migliorava la compatibilità con i primi computer Macintosh che non supportavano date precedenti al 2 gennaio 1904, quindi è diventato il sistema di data predefinito.
La tabella seguente mostra la prima data e la data dell'ultimo periodo per ciascun sistema di date e i valori sequenziali associati a ciascuna data.
Poiché i due sistemi di data utilizzano giorni di inizio diversi, la stessa data è rappresentata da numeri di serie diversi in ciascun sistema di data. Ad esempio, il 5 luglio 2007 può avere due valori consecutivi diversi a seconda del sistema di data utilizzato.
La differenza tra i due sistemi di data è di 1.462 giorni; Ciò significa che il numero di serie di una data nel sistema di data del 1900 è sempre inferiore di 1.462 giorni rispetto al valore consecutivo della stessa data nel sistema di data del 1904. Al contrario, il numero di serie di una data nel sistema di data del 1904 è sempre di 1.462 giorni inferiore al valore consecutivo delle stesse date nel sistema di data 1900. 1.462 giorni equivalgono a 4 anni e un giorno (incluso un giorno bisestile).
Cambiare il modo in cui viene interpretato un anno a due cifre
Importante: Per interpretare correttamente i valori dell'anno, immettere quattro cifre (ad esempio 2001, non 01). Inserendo anni a quattro cifre, Excel non interpreterà questo secolo.
Se hai immesso una data a due cifre dell'anno in una cella di testo o un argomento di testo in una funzione, ad esempio "= anno" ("1/1/31"), Excel interpreta l'anno come segue:
00 – 29 interpretato come gli anni dal 2000 al 2029. Ad esempio, quando si inserisce una data 5/28/19 Excel pensa che la data potrebbe essere il 28 maggio 2019.
da 30 a 99 interpretato come gli anni dal 1930 al 1999. Ad esempio, quando si inserisce una data 5/28/98 Excel pensa che la data potrebbe essere il 28 maggio 1998.
In Microsoft Windows, è possibile modificare la modalità di interpretazione dell'anno a due cifre per tutti i programmi Windows installati.
Windows 10
Pannello di controllo e poi seleziona Pannello di controllo.
Nel capitolo orologio, lingua e regione clic.
Fare clic sull'icona della lingua e standard regionali.
Nella finestra di dialogo regione premi il bottone Opzioni extra.
Apri la scheda Data .
in campo
Fare clic sul pulsante ok.
Windows 8
Ricerca Ricerca), entra nella casella di ricerca elemento del pannello di controllo Pannello di controllo.
Nel capitolo orologio, lingua e regione clic.
Nella finestra di dialogo regione premi il bottone Opzioni extra.
Apri la scheda Data .
in campo dopo aver inserito due cifre dell'anno, interpretarlo come l'anno compreso tra cornici, modificando il limite superiore per la palpebra.
Quando il limite superiore cambia, l'anno passa automaticamente al limite inferiore.
Fare clic sul pulsante ok.
Windows 7
Fare clic sul pulsante Cominciare e seleziona l'oggetto Pannello di controllo.
Seleziona un oggetto.
Nella finestra di dialogo regione premi il bottone Opzioni extra.
Apri la scheda Data .
in campo dopo aver inserito due cifre dell'anno, interpretarlo come l'anno compreso tra cornici, modificando il limite superiore per la palpebra.
Quando il limite superiore cambia, l'anno passa automaticamente al limite inferiore.
Fare clic sul pulsante ok.
Modificare il formato della data predefinito per visualizzare l'anno in quattro cifre
Per impostazione predefinita, quando si immettono le date in un libro, le date vengono formattate per visualizzare due cifre per l'anno. Quando si modifica il formato della data predefinito in un formato diverso utilizzando questa procedura, la visualizzazione delle date precedentemente immesse nella cartella di lavoro verrà modificata nel nuovo formato fino alla formattazione nella finestra di dialogo. " Formato cella"" (sul casa in un gruppo numero premi il bottone chiamata della finestra di dialogo).
Windows 10
Nella casella di ricerca sulla barra delle applicazioni, digita Pannello di controllo e poi seleziona Pannello di controllo.
Nel capitolo orologio, lingua e regione clic modificare i formati di data, ora e numero .
Fare clic sull'icona della lingua e standard regionali.
Nella finestra di dialogo regione premi il bottone Opzioni extra.
Apri la scheda Data .
In un breve elenco Formato data
Fare clic sul pulsante ok.
Windows 8
Scorri dal bordo destro dello schermo, premi il pulsante Ricerca(oppure, se stai usando un mouse, passa il mouse sull'angolo in alto a destra dello schermo, spostalo verso il basso e fai clic su Ricerca), entra nella casella di ricerca elemento del pannello di controllo e poi premere o Seleziona Pannello di controllo.
Nel capitolo orologio, lingua e regione clic modificare la data, l'ora o il formato dei numeri.
Nella finestra di dialogo regione premi il bottone Opzioni extra.
Apri la scheda Data .
In un breve elenco Formato data selezionare un formato che utilizzi quattro cifre per l'anno (aaaa).
Fare clic sul pulsante ok.
Windows 7
Fare clic sul pulsante Cominciare e seleziona l'oggetto Pannello di controllo.
Scegliere oggetto lingua e standard regionali.
Nella finestra di dialogo regione premi il bottone Opzioni extra.
Apri la scheda Data .
In un breve elenco Formato data selezionare un formato che utilizzi quattro cifre per l'anno (aaaa).
Fare clic sul pulsante ok.
Modifica del sistema di data in Excel
Il sistema di data cambia automaticamente quando si apre un documento creato su un'altra piattaforma. Ad esempio, se stai lavorando in Excel e apri un documento che è stato creato in Excel per Mac, il " Sistema di data del 1904"viene installato automaticamente.
È possibile modificare il sistema di data seguendo i passaggi seguenti.
Si prega di selezionare File > Opzioni > Inoltre.
Nel capitolo quando si calcola questo libro seleziona il libro che desideri e poi seleziona o deseleziona la casella usa il sistema di data del 1904 .
Problema: ho problemi con le date tra le cartelle di lavoro che utilizzano sistemi di data diversi
Potrebbero verificarsi problemi durante la copia e incolla di date o la creazione di riferimenti esterni tra cartelle di lavoro in base a due diversi sistemi di data. Le date possono essere visualizzate quattro anni prima o dopo la data prevista. Questi problemi possono verificarsi se si utilizza Excel per Windows, Excel per Mac o entrambi.
Ad esempio, se si copia la data 5 luglio 2007 da una cartella di lavoro che utilizza il sistema di data 1900 e quindi la si incolla in una cartella di lavoro che utilizza il sistema di data 1904, la data viene visualizzata come 5 luglio 2011, ovvero 1.462 giorni dopo . Inoltre, se si copia la data 5 luglio 2007 da una cartella di lavoro che utilizza il sistema di data 1904 e quindi la si incolla in una cartella di lavoro che utilizza il sistema di data 1900, la data sarà il 4 luglio 2003, ovvero 1.462 giorni prima. ... Per informazioni generali, vedere Sistemi di data in Excel.
Correggere l'errore di copia e incolla
Per impostare la data su quattro anni e un giorno dopo, aggiungere 1.462 alla data
Foglio1 $ A $ 1 + 1462
Per impostare la data a quattro anni e precedenti, sottrarre 1.462 da essa. Esempio
Foglio1 $ A $ 1-1462
Informazioni aggiuntive
Puoi sempre porre una domanda alla community tecnica di Excel, chiedere aiuto alla community di Answers o suggerire una nuova funzionalità o un miglioramento nel sito Web Excel User Voice.
Il modo più semplice e veloce per inserire la data o l'ora corrente in una cella è premere la combinazione di tasti di scelta rapida CTRL + ";" (data corrente) e CTRL + MAIUSC + ";" (ora attuale).
È molto più efficiente utilizzare la funzione TODAY(). Dopotutto, non solo imposta, ma aggiorna automaticamente anche il valore della cella ogni giorno senza l'intervento dell'utente.
Come inserire la data corrente in Excel
Per inserire la data corrente in Excel, utilizzare la funzione OGGI (). Per fare ciò, seleziona lo strumento "Formule" - "Data e ora" - "OGGI". Questa funzione non ha argomenti, quindi puoi semplicemente digitare nella cella: "= TODAY ()" e premere Invio.
Data corrente nella cella:
Se è necessario che la cella aggiorni automaticamente il valore non solo della data corrente, ma anche dell'ora, è meglio utilizzare la funzione "= TDATA ()".
La data e l'ora correnti nella cella.
Come impostare la data corrente in Excel su intestazioni e piè di pagina
L'inserimento della data corrente in Excel è implementato in diversi modi:
- Impostando i parametri delle intestazioni e dei piè di pagina. Il vantaggio di questo metodo è che la data e l'ora correnti vengono stampate su tutte le pagine contemporaneamente.
- Utilizzando la funzione OGGI ().
- Utilizzando la combinazione di tasti di scelta rapida CTRL +; - per impostare la data corrente e CTRL+SHIFT+; - per impostare l'ora corrente. Svantaggio: in questo metodo, il valore della cella non verrà aggiornato automaticamente agli indicatori correnti all'apertura del documento. Ma in alcuni casi, lo svantaggio dei dati è un vantaggio.
- Utilizzo delle macro VBA utilizzando le seguenti funzioni nel codice del programma: Data (); Ora (); Ora ().
Intestazioni e piè di pagina consentono di impostare la data e l'ora correnti nella parte superiore o inferiore delle pagine del documento che verrà inviato alla stampante. Inoltre, l'intestazione e il piè di pagina ci permettono di numerare tutte le pagine del documento.
Per rendere la data corrente in Excel e l'impaginazione utilizzando intestazioni e piè di pagina, procedi come segue:
Intestazioni e piè di pagina ci consentono di fare molto di più che impostare date e impaginazione. Puoi anche aggiungere un luogo per la firma della persona responsabile della segnalazione. Ad esempio, modifichiamo ora la parte inferiore sinistra della pagina nell'area dell'intestazione e del piè di pagina.
Ogni transazione viene eseguita in un determinato momento o periodo e quindi legata a una data specifica. In Excel, le date vengono convertite in numeri interi. Cioè, ogni data ha il suo numero intero, ad esempio 01/01/1900 è il numero 1 e 02/01/1900 è il numero 2, ecc. La definizione di anni, mesi e giorni non è altro che il corrispondente tipo di formattazione per valori numerici successivi. Per questo motivo, anche le operazioni di data più semplici eseguite in Excel (ad esempio l'ordinamento) sono molto problematiche.
Ordina in Excel per data e mese
Utilizzando l'esempio della tabella mostrata nella figura seguente, mostreremo in che modo prepararlo per un comodo ordinamento delle date. Il nostro elenco di transazioni di date copre l'intervallo di celle B1: C15.
Prima di tutto, creeremo una colonna in cui numerare tutte le transazioni. Considera questa azione come una buona forma per qualsiasi tipo di ordinamento. Infatti, in questi casi, se i dati vengono rimescolati in modo errato, abbiamo sempre la possibilità di tornare alla visualizzazione originale della tabella. Per questo:
Di conseguenza, la colonna verrà automaticamente riempita con una sequenza di numeri di transazione da 1 a 14.
Consiglio utile! In Excel, la maggior parte delle attività ha diverse soluzioni. Per normalizzare automaticamente le colonne in Excel, puoi usare il tasto destro del mouse. Per fare ciò, devi solo spostare il cursore sull'indicatore del cursore della tastiera (nella cella A2) e tenere premuto solo il pulsante destro del mouse e trascinare l'indicatore lungo la colonna. Dopo aver rilasciato il tasto destro del mouse, apparirà automaticamente un menu di scelta rapida da cui è necessario selezionare l'opzione "Riempi". E la colonna verrà automaticamente riempita con una sequenza di numeri, simile al primo metodo di completamento automatico.
Ora è il momento di creare colonne ausiliarie che aiuteranno in modo significativo a ordinare l'elenco delle transazioni in modo di alta qualità. Ci sono alcuni semplici passaggi da seguire:
- Compila le celle D1, E1, F1 con i nomi delle intestazioni: "Anno", "Mese", "Giorno".
- Per ogni colonna, inserisci le funzioni appropriate sotto le intestazioni e copiale lungo ogni colonna:
- D1: = ANNO (B2);
- E1: = MESE (B2);
- F1: = GIORNO (B2).
Di conseguenza, dovremmo ottenere il seguente risultato:
La nostra tabella è pronta e offre la possibilità di eseguire l'ordinamento multilaterale delle transazioni per data. Per prima cosa, facciamo un ordinamento di prova per vedere come funzionano le cose.
Supponiamo di voler ordinare le date della transazione per mese. In questo caso, l'ordine dei giorni e degli anni non ha importanza. Per fare ciò, è sufficiente accedere a qualsiasi cella della colonna Mese (E) e selezionare lo strumento: DATI - Ordina e filtra - Ordina ascendente.
Ora, per ripristinare l'ordinamento e riportare i dati della tabella alla loro forma originale, vai in una cella qualsiasi nella colonna "N." (A) e selezionerai di nuovo lo stesso strumento "Ordina ascendente".
Come ordinare le date in base a più condizioni in Excel
E ora puoi iniziare un ordinamento complesso delle date in base a diverse condizioni. L'attività è la seguente: le transazioni devono essere ordinate nel seguente ordine:
- Anni in ascesa.
- Mesi nel periodo di alcuni anni - decrescente.
- Giorni in periodi di mesi specifici - decrescenti.
Il modo per implementare l'attività:
Di conseguenza, abbiamo eseguito un complesso ordinamento delle date in base a diverse condizioni:
Per ordinare i valori della tabella in formato Data, Excel fornisce opzioni di elenchi a discesa come Vecchio con Nuovo e Nuovo con Vecchio. Ma in pratica, quando si lavora con grandi quantità di dati, il risultato non sempre soddisfa le aspettative. Poiché per Excel le date sono numeri interi, è più sicuro ed efficiente ordinarle utilizzando il metodo descritto in questo articolo.
I fogli di calcolo Excel offrono la possibilità di lavorare con vari tipi di testo e informazioni numeriche. È disponibile anche l'elaborazione della data. In questo caso, potrebbe essere necessario isolare un numero specifico dal valore totale, ad esempio un anno. Ci sono funzioni separate per questo: ANNO, MESE, GIORNO e GIORNO SETTIMANALE.
Esempi di utilizzo delle funzioni per l'elaborazione delle date in Excel
Le tabelle di Excel archiviano le date presentate come una sequenza di valori numerici. Inizia il 1 gennaio 1900. Questa data corrisponderà al numero 1. Allo stesso tempo, il 1 gennaio 2009 è indicato nelle tabelle come il numero 39813. Questo è il numero di giorni tra due date designate.
La funzione ANNO viene utilizzata in modo analogo a quelle adiacenti:
- MESE;
- GIORNO;
Tutti mostrano valori numerici corrispondenti al calendario gregoriano. Anche se il calendario Hijri è stato selezionato nel foglio di calcolo Excel per visualizzare la data inserita, quando si estrae l'anno e altri valori composti utilizzando le funzioni, l'applicazione presenterà un numero che è l'equivalente nel sistema di cronologia gregoriano.
Per utilizzare la funzione ANNO, inserisci la seguente formula di funzione con un argomento in una cella:
ANNO (indirizzo cella numerico con data)
L'argomento della funzione è obbligatorio. Può essere sostituito con "date_num_format". Negli esempi seguenti, puoi vederlo chiaramente. È importante ricordare che quando si visualizza la data come testo (orientamento automatico lungo il bordo sinistro della cella), la funzione ANNO non verrà eseguita. Ciò risulterà nella visualizzazione #VALUE. Pertanto, le date formattate devono essere numeriche. I giorni, i mesi e gli anni possono essere separati da punti, barre o virgole.
Consideriamo un esempio di utilizzo della funzione ANNO in Excel. Se abbiamo bisogno di ottenere l'anno dalla data originale, la funzione RIGHT non ci aiuterà, poiché non funziona con le date, ma solo con testo e valori numerici. Per separare l'anno, il mese o il giorno dalla data completa, Excel fornisce funzioni per lavorare con le date.
Esempio: C'è una tabella con un elenco di date e in ognuna di esse è necessario separare il valore del solo anno.
Inseriamo i dati iniziali in Excel.
Per risolvere il problema, è necessario inserire la formula nelle celle della colonna B:
ANNO (l'indirizzo della cella, dalla cui data si vuole estrarre il valore dell'anno)
Di conseguenza, stiamo estraendo gli anni da ogni data.
Un esempio simile della funzione MESE in Excel:
Un esempio di utilizzo delle funzioni GIORNO e GIORNO SETTIMANALE. La funzione GIORNO calcola il numero di qualsiasi giorno da una data:
La funzione WEEKDAY restituisce il numero del giorno della settimana (1° lunedì, 2° martedì... ecc.) per qualsiasi data:
Nel secondo argomento opzionale della funzione WEEKDAY, specifica il numero 2 per il nostro formato del giorno della settimana (da lunedì-1 a domenica-7):
Se si omette il secondo argomento facoltativo, verrà utilizzato il formato predefinito (dall'inglese da domenica-1 a sabato-7).
Creiamo una formula dalle combinazioni delle funzioni INDICE e GIORNO SETTIMANA:
Diamo una visione più chiara dell'implementazione di questa funzione.
Esempi di applicazioni pratiche di funzioni per lavorare con le date
Queste funzioni primitive sono molto utili per raggruppare i dati per anno, mese, giorno della settimana e giorno specifico.
Supponiamo di avere un semplice report di vendita:
Abbiamo bisogno di organizzare rapidamente i dati per l'analisi visiva senza utilizzare le tabelle pivot. Per fare ciò, presenteremo il report in una tabella dove potrai raggruppare comodamente e velocemente i dati per anni, mesi e giorni della settimana:
Ora abbiamo uno strumento per lavorare con questo rapporto sulle vendite. Possiamo filtrare e segmentare i dati in base a criteri temporali specifici:
Inoltre, puoi stilare un istogramma per analizzare i giorni della settimana più venduti, capire quale giorno della settimana ha più vendite:
In questa prospettiva, è molto conveniente segmentare i rapporti di vendita per periodi di tempo lunghi, medi e brevi.
Va notato subito che per ottenere la differenza tra due date, nessuna delle funzioni di cui sopra ci aiuterà. Per questo compito, dovresti usare la funzione RAZDAT dedicata:
Il tipo di valori nelle celle "data" richiede un approccio speciale durante l'elaborazione dei dati. Pertanto, dovresti usare le funzioni appropriate per questo tipo in Excel.
Creiamo una sequenza di date e orari di vario genere:01.01.09, 01.02.09, 01.03.09, ..., gennaio, aprile, luglio, ..., lun, mar, mer, ..., Q1, Q2, ..., 09: 00, 10: 00, 11:00, ... ecc.
Perché ogni valore di data corrisponde a un certo numero (vedi articolo), quindi gli approcci per la formazione di sequenze descritti nell'articolo sono applicabili per le date. Tuttavia, ci sono anche alcune peculiarità.
Sotto sequenza 01.01.09, 01.02.09, 01.03.09 (primi giorni dei mesi) possono essere formati dalla formula = DATA CEE (B2, STRINGA (A1)), in cella B2 ci deve essere una data - il primo elemento della sequenza ( 01.01.09 ).
La stessa sequenza può essere formata utilizzando il tasto destro del mouse. Lascia entrare la cella la2 valore inserito 01.01.09 ... Seleziona la cella la2 ... Tenendo premuto il tasto destro del mouse, copia il valore da la2 nelle celle sottostanti. Dopo aver rilasciato il tasto destro del mouse, apparirà un menu contestuale, in cui è necessario selezionare l'elemento Compila per mese.
Modificando il formato delle celle contenenti la sequenza 01.01.09, 01.02.09, 01.03.09, Su MMM(vedi articolo) otteniamo la sequenza gennaio, febbraio, marzo, ...
La stessa sequenza può essere inserita utilizzando l'elenco di completamento automatico Pulsante Office / Opzioni Excel / Base / Impostazioni di base per lavorare con Excel / Modifica elenchi(accedere gennaio, poi Indicatore di riempimento copiare).
Le celle non conterranno date, come nel caso precedente, ma valori di testo.
Allo stesso modo, puoi formare una sequenza di giorni della settimana. lun, mar, mer, ...
Sequenza di quarti 1 mq, 2 mq, ... possono essere formati utilizzando le idee dell'articolo.
Utilizzando lo strumento, è possibile creare solo sequenze di giorni della settimana. E anche con un passo di un mese e un anno.
Sequenza primi mesi dei trimestri gennaio, aprile, luglio, ottobre, gennaio, ... può essere creato inserendo in due celle i primi due elementi della sequenza ( gennaio, aprile), quindi (dopo averli selezionati) copia giù marcatore di riempimento... Le celle conterranno valori di testo. Per fare in modo che le celle contengano date, usa la formula = DATEC ($ G $ 16, (LINEA (A2) -LINE ($ A $ 1)) * 3) Si presume che la sequenza inizi dalla cella G16 , la formula deve essere inserita nella cella G17 (vedi file di esempio).
Sequenza temporale 09:00, 10:00, 11:00, ... può essere formato utilizzando. Lascia entrare la cella la2 valore inserito 09 :00 ... Seleziona la cella la2 ... copiamo Indicatore di riempimento, valore da la2 nelle celle sottostanti. La sequenza sarà formata.
Se si desidera generare una sequenza temporale con incrementi di 15 minuti ( 09:00, 09:15, 09:30, ... ), quindi puoi usare la formula = B15 + 1/24/60 * 15 (Si presume che la sequenza inizi con cella B15 , la formula va inserita in B16 ). La formula restituirà il risultato in formato data.
Un'altra formula = TESTO (B15 + 1/24/60 * 15; "hh: mm") restituirà il risultato in formato testo.
