contingut
En el treball professional amb fulls de càlcul, no és estrany interactuar amb dates i hores. No podràs prescindir-ne. Per tant, Déu mateix va ordenar aprendre a treballar amb dades d'aquest tipus. Això us estalviarà una gran quantitat de temps i evitarà molts errors mentre treballeu amb fulls de càlcul.
Malauradament, molts principiants no saben com es processen les dades. Per tant, abans de considerar aquesta classe d'operacions, cal dur a terme un programa educatiu més detallat.
Com es representa una data a Excel
La informació de la data es processa com el nombre de dies des del 0 de gener de 1900. Sí, no us equivoqueu. De fet, des de zero. Però això és necessari perquè hi hagi un punt de partida, de manera que l'1 de gener ja es considera el número 1, etc. El valor de data màxim compatible és 2958465, que al seu torn és el 31 de desembre de 9999.
Aquest mètode permet utilitzar dates per a càlculs i fórmules. Així, Excel permet determinar el nombre de dies entre dates. L'esquema és senzill: el segon es resta d'un nombre i, a continuació, el valor resultant es converteix en un format de data.
Per a més claredat, aquí teniu una taula que mostra les dates amb els seus valors numèrics corresponents.
Per determinar el nombre de dies que han passat de la data A a la data B, cal restar el primer de l'últim. En el nostre cas, aquesta és la fórmula = B3-B2. Després d'introduir-lo, el resultat és el següent.
És important tenir en compte que el valor està en dies perquè hem escollit un format diferent per a la cel·la que la data. Si inicialment haguéssim escollit el format "Data", el resultat hauria estat aquest.
És important parar atenció a aquest punt en els vostres càlculs.
És a dir, per mostrar el número de sèrie correcte que es correspongui completament amb la data, cal utilitzar qualsevol format que no sigui la data. Al seu torn, per convertir el número en una data, hauríeu d'establir el format adequat.
Com es representa el temps a Excel
La manera com es representa el temps a Excel és lleugerament diferent de la data. El dia es pren com a base i les hores, minuts i segons són les seves parts fraccionades. És a dir, 24 hores és 1, i qualsevol valor més petit es considera com la seva fracció. Així, 1 hora és 1/24 d'un dia, 1 minut és 1/1140 i 1 segon és 1/86400. La unitat de temps més petita disponible a Excel és 1 mil·lisegon.
Similar a les dates, aquesta forma de representació permet realitzar càlculs amb el temps. És cert que aquí hi ha una cosa inconvenient. Després dels càlculs, obtenim una part del dia, no el nombre de dies.
La captura de pantalla mostra els valors en format numèric i en format "Hora".
El mètode de càlcul de l'hora és similar a la data. Cal restar el temps anterior al temps posterior. En el nostre cas, aquesta és la fórmula = B3-B2.
Com que la cel·la B4 primer tenia un format general, després al final de la introducció de la fórmula, canvia immediatament a "Hora".
Excel, quan treballa amb el temps, realitza les operacions aritmètiques habituals amb nombres, que després es tradueixen al format de temps que ens coneix.
Format de data i hora
Pel que sabem, les dates i les hores es poden emmagatzemar en diferents formats. Per tant, cal saber introduir-los correctament perquè el format sigui correcte.
Per descomptat, podeu utilitzar el número de sèrie del dia o part del dia quan introduïu la data i l'hora, però aquest enfocament és molt inconvenient. A més, hauràs d'aplicar constantment un determinat format a la cel·la, la qual cosa només augmenta el malestar.
Per tant, Excel us permet especificar l'hora i la data de diferents maneres. Si apliqueu un d'ells, el programa converteix immediatament la informació al número adequat i aplica el format correcte a la cel·la.
Consulteu la taula següent per obtenir una llista dels mètodes d'entrada de data i hora compatibles amb Excel. La columna de l'esquerra mostra els formats possibles i la columna de la dreta mostra com es mostraran a Excel després de la conversió. És important tenir en compte que si no s'especifica l'any, s'assigna automàticament l'actual, que està configurat al sistema operatiu.
De fet, hi ha moltes més maneres de mostrar. Però amb aquests n'hi ha prou. A més, l'opció de gravació de la data específica pot variar segons el país o la regió, així com la configuració del sistema operatiu.
Format personalitzat
Mentre treballa amb cel·les, l'usuari pot determinar quin serà el format. Pot fer que només es mostrin l'hora, el mes, el dia, etc. També és possible ajustar l'ordre en què es formula la data, així com els separadors.
Per accedir a la finestra d'edició, heu d'obrir la pestanya "Nombre", on trobareu l'opció de la finestra "Format cel·les". Al quadre de diàleg que s'obre, hi haurà una categoria "Data" en la qual podeu seleccionar el format de data correcte.
Si seleccioneu la categoria "Hora", apareixerà, en conseqüència, una llista amb opcions per mostrar l'hora.
Per aplicar una opció de format específica a una cel·la, heu de seleccionar el format desitjat i fer clic a D'acord. Després d'això, s'aplicarà el resultat. Si no hi ha prou formats que ofereix Excel, podeu trobar la categoria "Tots els formats". També hi ha moltes opcions.
Si cap opció és adequada, sempre és possible crear la vostra. És molt fàcil fer això. Només cal que seleccioneu els formats preestablerts com a mostra i seguiu aquests passos:
- Seleccioneu la cel·la de la qual voleu canviar el format.
- Obriu el quadre de diàleg "Format de cel·les" i cerqueu la pestanya "Nombre".
- A continuació, s'obre la categoria “Tots els formats”, on trobem el camp d'entrada “TIP”. Allà heu d'especificar un codi de format de número. Després d'introduir-lo, feu clic a "D'acord".
- Després d'aquests passos, la cel·la mostrarà informació de data i hora en un format personalitzat.
Ús de funcions amb dates i hores
Quan es treballa amb dates i hores, l'usuari pot utilitzar més de 20 funcions diferents. I tot i que aquesta quantitat pot ser massa per a algú, totes es poden utilitzar per aconseguir determinats objectius.
Per accedir a totes les funcions possibles, cal anar a la categoria “Data i hora” del grup “Biblioteca de funcions”. Considerarem només algunes de les funcions principals que permeten extreure diversos paràmetres de dates i hores.
CURS()
Proporciona la possibilitat d'obtenir l'any que correspon a una data concreta. Com ja sabeu, aquest valor pot estar entre 1900 i 9999.
La cel·la 1 mostra la data en el format DDDD DD.MM.AAAA hh:mm:ss. Aquest és el format que hem creat abans. Posem com a exemple una fórmula que determina quants anys han passat entre dues dates.
Al mateix temps, si us fixeu més de prop, resulta que la funció no va calcular el resultat completament correcte. El motiu és que només utilitza dates en els seus càlculs.
MES ()
Amb aquesta funció, podeu destacar el número del mes corresponent a una data concreta. Retorna un resultat que va de l'1 al 12. Aquest número al seu torn correspon al número del mes.
DIA()
Similar a les funcions anteriors, aquesta retorna el número del dia en una data determinada. El resultat del càlcul pot anar d'1 a 31.
TEMPS()
Com el seu nom indica, aquesta funció retorna el número de l'hora, que oscil·la entre 0 i 23.
MINUTS()
Una funció que retorna el nombre de minuts en una cel·la específica. Els possibles valors que es retornen són del 0 al 59.
SEGONS()
Aquesta funció retorna els mateixos valors que l'anterior, excepte que retorna segons.
DIA()
Amb aquesta funció, podeu esbrinar el número del dia de la setmana que s'utilitza en aquesta data. Els valors possibles són de l'1 al 7, però tingueu en compte que el compte enrere comença a partir de diumenge, no dilluns, com fem habitualment.
Tanmateix, utilitzant el segon argument, aquesta funció us permet personalitzar el format. Per exemple, si passeu el valor 2 com a segon paràmetre, podeu definir el format perquè el número 1 signifiqui dilluns en lloc de diumenge. Això és molt més convenient per a l'usuari domèstic.
Si escrivim 2 al segon argument, aleshores en el nostre cas la funció retornarà el valor 6, que correspon a dissabte.
AVUI ()
Aquesta funció és molt senzilla: no calen arguments perquè funcioni. Retorna el número de sèrie de la data establerta a l'ordinador. Si s'aplica a una cel·la per a la qual s'estableix el format General, es convertirà automàticament al format "Data".
TATA ()
Aquesta funció tampoc requereix cap argument. Funciona de la mateixa manera que l'anterior, només amb la data i l'hora. S'utilitza si cal inserir a la cel·la la data i l'hora actuals establertes a l'ordinador. I igual que en la funció anterior, en aplicar aquesta, la cel·la es converteix automàticament al format de data i hora, sempre que abans s'hagi configurat el format "General".
Tant la funció anterior com aquesta es canvien automàticament cada cop que es torna a calcular el full, la qual cosa permet visualitzar l'hora i la data més actualitzades.
Per exemple, aquesta fórmula pot determinar l'hora actual.
=AVUI () - AVUI ()
En aquest cas, la fórmula determinarà la fracció de dia en format decimal. És cert que haureu d'aplicar el format de l'hora a la cel·la en què està escrita la fórmula, si voleu mostrar exactament l'hora i no el número.
LA DATA()
Aquesta funció té tres arguments, cadascun dels quals s'ha d'introduir. Després dels càlculs, aquesta funció retorna el número de sèrie de la data. La cel·la es converteix automàticament al format "Data" si abans tenia un format "General".
L'argument Dia o Mes pot ser positiu o negatiu. En el primer cas, la data augmenta, i en el segon, disminueix.
També podeu utilitzar operacions matemàtiques en els arguments de la funció DATA. Per exemple, aquesta fórmula afegeix 1 any, 5 mesos i 17 dies a la data de la cel·la A1.
I aquesta fórmula permet convertir una cadena de text en una data de treball completa, que es pot utilitzar en altres funcions.
TEMPS()
Igual que la funció LA DATA(), aquesta funció té tres paràmetres necessaris: hores, minuts i segons. Després d'utilitzar-lo, apareixerà un nombre decimal a la cel·la resultant, però la cel·la mateixa es formatarà en el format "Hora" si abans tenia el format "General".
Pel seu principi de funcionament, la funció TEMPS() и LA DATA() moltes coses semblants. Per tant, no té sentit centrar-s'hi.
És important tenir en compte que aquesta funció no pot tornar un temps superior a 23:59:59. Si el resultat és superior a això, la funció es reinicia automàticament a zero.
funcions LA DATA() и TEMPS() es poden aplicar conjuntament.
En aquesta captura de pantalla, la cel·la D1, que utilitzava aquestes dues funcions, té un format de data i hora.
Funcions de càlcul de data i hora
En total hi ha 4 funcions que permeten realitzar operacions matemàtiques amb data i hora.
DADES()
Amb aquesta funció, podeu esbrinar el número ordinal d'una data que està darrere d'un nombre conegut de mesos (o abans d'un determinat). Aquesta funció pren dos arguments: la data d'inici i el nombre de mesos. El segon argument pot ser positiu o negatiu. La primera opció s'ha d'especificar si es vol calcular la data futura, i la segona, si és l'anterior.
EOMES()
Aquesta funció permet determinar el número ordinal de l'últim dia del mes que està darrere o abans d'una data determinada. Té els mateixos arguments que l'anterior.
DIA DE TREBALL ()
Igual que la funció DADES(), només el retard o l'avançament es produeix en un nombre determinat de dies hàbils. La sintaxi és semblant.
Aquestes tres funcions retornen un número. Per veure la data, heu de convertir la cel·la al format adequat.
ESBORRA ()
Aquesta funció senzilla determina el nombre de dies laborables entre la data 1 i la data 2.