| leçon 2| leçon 3 | leçon 4 | leçon 5
Je pense que vous savez déjà grâce à la dernière leçon que les dates et les heures dans Excel sont stockées sous forme de nombres ordinaux, dont le point de départ est considéré comme un certain 0 janvier 1900. Heureusement, dans les cellules, nous ne voyons pas ces chiffres, mais des dates et des heures qui nous sont familières et qui peuvent être stockées dans une variété de formats. Dans cette leçon, vous apprendrez à saisir des dates et des heures dans Excel pour obtenir le formatage souhaité.
Saisir des dates et des heures dans Excel
Les dates et heures dans Excel peuvent être saisies sous forme de nombre ordinal ou de fraction de jour, mais comme vous le comprenez vous-même, ce n'est pas tout à fait pratique. De plus, avec cette approche, vous devrez à chaque fois appliquer un certain format numérique à la cellule.
Excel propose plusieurs formats pour saisir des données temporaires. Si vous utilisez ce format, Excel convertira automatiquement la date (ou l'heure) saisie en un nombre ordinal (une fraction de jour) et appliquera le format à la cellule. Dates(ou Heure) défini par défaut.
La figure ci-dessous montre un tableau des options de saisie de date et d'heure prises en charge par Excel. La colonne de gauche affiche les valeurs qui doivent être saisies dans la cellule et la colonne de droite affiche le résultat de la conversion. A noter que les dates saisies sans préciser d'année se voient attribuer l'année en cours, à savoir celle définie dans les paramètres de votre système opérateur.
Ce n'est pas tout options possibles, pris en charge par Excel. Mais même ces options vous suffiront amplement.
Certaines options d'affichage de la date affichées dans la colonne de droite peuvent varier. Cela dépend de vos paramètres régionaux et du format d'affichage de la date et de l'heure dans les paramètres de votre système d'exploitation.
Travailler avec des cellules dans Microsoft Excel, qui contiennent une date ou une heure, vous avez la possibilité de leur appliquer un formatage différent. Par exemple, vous pouvez afficher dans une cellule uniquement le jour de la semaine, ou uniquement le mois et l'année, ou encore uniquement l'heure.
Vous pouvez accéder à tous les formats possibles sur l'onglet Nombre boîte de dialogue Format de cellule. Dans la catégorie Date Les formats de date intégrés sont :
Pour appliquer la mise en forme à une cellule, sélectionnez simplement format requis en coupe Taper et appuyez sur D'ACCORD. Le formatage requis sera appliqué. Si les formats de nombres intégrés ne vous suffisent pas, vous pouvez utiliser la catégorie Tous les formats. Ici, vous pouvez également trouver beaucoup de choses intéressantes.
Si aucune des options ne vous convient, vous pouvez créer un format numérique personnalisé. Il est assez facile à créer si vous utilisez les formats de nombres intégrés comme modèle. Pour créer un format numérique personnalisé, procédez comme suit :
Comme vous pouvez le constater, tout est assez simple !
Dans cette leçon, nous avons appris à personnaliser le format d'affichage des dates et des heures dans Microsoft Excel, et avons également examiné plusieurs options utiles pour les saisir dans une feuille de calcul. Dans la prochaine leçon, nous parlerons de
Les feuilles de calcul Excel offrent la possibilité de travailler avec différents types informations textuelles et numériques. Le traitement des dates est également disponible. Dans ce cas, il peut être nécessaire d'isoler un nombre spécifique, par exemple une année, de la valeur totale. Il existe des fonctions distinctes pour cela : ANNÉE, MOIS, JOUR et JOUR DE LA SEMAINE.
Exemples d'utilisation de fonctions pour traiter les dates dans Excel
Les tableaux Excel stockent les dates, qui sont représentées sous la forme d'une séquence de valeurs numériques. Cela commence le 1er janvier 1900. Cette date correspondra au chiffre 1. D'ailleurs, le 1er janvier 2009 est inclus dans les tableaux sous le chiffre 39813. C'est exactement le nombre de jours entre les deux dates désignées.
La fonction ANNÉE s'utilise de la même manière que les fonctions associées :
- MOIS;
- JOUR;
Ils affichent tous des valeurs numériques selon le calendrier grégorien. Même si dans Feuille de calcul Excel Pour afficher la date saisie, le calendrier Hijri a été sélectionné, puis en isolant l'année et les autres valeurs des composants à l'aide de fonctions, l'application présentera un nombre équivalent dans le système de chronologie grégorien.
Pour utiliser la fonction ANNÉE, vous devez saisir la formule de fonction suivante avec un argument dans une cellule :
ANNÉE (adresse de cellule avec date au format numérique)
L'argument de la fonction est obligatoire. Il peut être remplacé par "date_in_numeric_format". Dans les exemples ci-dessous, vous pouvez clairement le voir. Il est important de rappeler que lors de l'affichage de la date sous forme de texte (orientation automatique vers le bord gauche de la cellule), la fonction ANNÉE ne sera pas exécutée. Son résultat sera l'affichage #VALEUR. Par conséquent, les dates formatées doivent être présentées sous forme numérique. Les jours, les mois et les années peuvent être séparés par un point, une barre oblique ou une virgule.
Regardons un exemple de travail avec la fonction ANNÉE dans Excel. Si nous devons obtenir l'année à partir de la date source, la fonction RIGHT ne nous aidera pas car elle ne fonctionne pas avec les dates, mais uniquement avec le texte et les valeurs numériques. Pour séparer l'année, le mois ou le jour de la date complète, Excel propose des fonctions permettant de travailler avec les dates.
Exemple : Il y a un tableau avec une liste de dates et dans chacune d'elles il faut séparer la valeur de l'année uniquement.
Entrons les données sources dans Excel.
Pour résoudre le problème, vous devez saisir la formule dans les cellules de la colonne B :
ANNÉE (adresse de la cellule à partir de la date à laquelle vous devez extraire la valeur de l'année)
En conséquence, nous extrayons les années de chaque date.
Un exemple similaire de la fonction MOIS dans Excel :
Un exemple de travail avec les fonctions DAY et WEEKDAY. La fonction DAY permet de calculer le jour de n'importe quel jour à partir d'une date :
La fonction WEEKDAY renvoie le numéro du jour de la semaine (1-lundi, 2-mardi... etc.) pour n'importe quelle date :
Dans le deuxième argument optionnel de la fonction WEEKDAY, vous devez spécifier le chiffre 2 pour notre format de comptage du jour de la semaine (du lundi-1 au dimanche-7) :
Si vous omettez le deuxième argument facultatif, le format par défaut (anglais du dimanche-1 au samedi-7) sera utilisé.
Créons une formule à partir de combinaisons des fonctions INDEX et WEEKDAY :
Voyons une forme plus compréhensible d'implémentation de cette fonction.
Exemples d'application pratique de fonctions pour travailler avec des dates
Ces fonctions primitives sont très utiles pour regrouper les données par : années, mois, jours de la semaine et jours spécifiques.
Disons que nous avons un simple rapport de ventes :
Nous devons organiser rapidement les données pour une analyse visuelle sans utiliser de tableaux croisés dynamiques. Pour ce faire, nous présentons le rapport dans un tableau où vous pouvez regrouper facilement et rapidement les données par année, mois et jour de la semaine :
Nous disposons désormais d'un outil pour travailler avec ce rapport de ventes. Nous pouvons filtrer et segmenter les données en fonction de certains critères temporels :
De plus, vous pouvez créer un histogramme pour analyser les jours les plus vendus de la semaine, pour comprendre à quel jour de la semaine ils tombent. le plus grand nombre ventes:
Sous cette forme, il est très pratique de segmenter les rapports de ventes sur des périodes longues, moyennes et courtes.
Il convient de noter tout de suite que pour faire la différence entre deux dates, aucune des fonctions décrites ci-dessus ne nous aidera. Pour cette tâche, vous devez utiliser la fonction RAZNDAT spécialement conçue :
Le type de valeurs dans les cellules de date nécessite une approche particulière lors du traitement des données. Par conséquent, vous devez utiliser des ce type fonctions dans Excel.
Les dates sont souvent une partie importante de l’analyse des données. Vous posez souvent des questions telles que : quand le produit a-t-il été acheté, combien de temps faudra-t-il pour terminer une tâche d'un projet ou quel est le revenu moyen pour le trimestre fiscal ? La saisie correcte des dates est essentielle pour garantir des résultats précis. Cependant pour compréhension correcte Ces résultats doivent être formatés par date pour les rendre plus faciles à comprendre.
Important:Étant donné que les règles régissant la façon dont les dates sont interprétées par tout programme de calcul sont complexes, vous devez être aussi précis que possible sur la date avec chaque entrée. Cela garantira que les calculs de date sont aussi précis que possible.
À propos des calculs et des formats de date
Excel stocke les dates sous forme de nombres séquentiels appelés valeurs séquentielles. Par exemple, dans Excel pour Windows, le 1er janvier 1900 correspond au numéro de séquence 1 et le 1er janvier 2008 au numéro de séquence 39448, car il se situe 39 448 jours après le 1er janvier 1900.
Excel stocke les valeurs de temps sous forme décimale car le temps est traité comme une fraction de journée. Nombre décimal est une valeur comprise entre 0 (zéro) et 0,99999999, représentant l'heure de 0:00:00 (00:00:00) à 23:59:59 (23:59:59).
Étant donné que les valeurs de date et d'heure sont des valeurs, elles peuvent être ajoutées, soustraites et incluses dans d'autres calculs. Vous pouvez afficher une date sous forme de valeur ordinale et une heure sous forme décimale en modifiant le format de la cellule contenant la date ou l'heure vers un format commun.
Comprendre les deux systèmes de dates
Excel pour Mac et Excel pour Windows prennent en charge les systèmes de date 1900 et 1904. Le système de date par défaut pour Excel pour Windows est 1900 ; et le système de date par défaut pour Excel pour Mac est 1904.
Excel pour Windows était à l'origine basé sur le système de date 1900 car il améliorait la compatibilité avec d'autres tableurs développés pour le système d'exploitation MS-DOS et Microsoft Windows, et c'est ainsi qu'il est devenu le système de date standard. Excel pour Mac était à l'origine basé sur le système de date de 1904 car il améliorait la compatibilité avec les premiers ordinateurs Macintosh, qui ne prenaient pas en charge les dates avant le 2 janvier 1904, il est donc devenu le système de date par défaut.
Le tableau ci-dessous indique la première date et la date de la dernière période pour chaque système de date ainsi que la signification séquentielle associée à chaque date.
Étant donné que les deux systèmes de date utilisent des jours de départ différents, la même date est représentée par des numéros de série différents dans chaque système de date. Par exemple, le 5 juillet 2007 peut avoir deux significations séquentielles différentes selon le système de date utilisé.
La différence entre les deux systèmes de date est de 1 462 jours ; Cela signifie que le numéro de série d'une date dans le système de date 1900 est toujours inférieur de 1 462 jours à la valeur de série de la même date dans le système de date 1904. À l'inverse, le numéro de série d'une date dans le système de date 1904 est toujours de 1 462 jours. inférieure à la valeur sérielle des mêmes dates dans le système de date 1900. 1 462 jours équivaut à 4 ans et un jour (y compris un jour bissextile).
Changer la façon dont une année à deux chiffres est interprétée
Important: Pour vous assurer que les valeurs de l'année sont interprétées correctement, saisissez quatre chiffres (par exemple, 2001 plutôt que 01). Si vous saisissez des années à quatre chiffres, Excel n’interprétera pas les siècles.
Si vous entrez une année à deux chiffres dans une cellule de texte ou un argument de texte dans une fonction, telle que « =année » (« 1/1/31 »), Excel interprète l'année comme suit :
00 – 29 est interprété comme des années de 2000 à 2029. Par exemple, lors de la saisie d'une date 5/28/19 Excel pense que la date pourrait être le 28 mai 2019.
de 30 à 99 interprété comme des années de 1930 à 1999. Par exemple, lors de la saisie d'une date 5/28/98 Excel pense que la date pourrait être le 28 mai 1998.
Dans Microsoft Windows, vous pouvez modifier la façon dont deux chiffres de l'année sont interprétés pour tout le monde programmes installés Fenêtres.
Windows 10
Panneau de contrôle puis sélectionnez Panneau de contrôle.
Dans la rubrique horloge, langue et région cliquez sur .
Cliquez sur l'icône Langue et normes régionales.
Dans la boîte de dialogue région cliquez sur le bouton Options supplémentaires.
Ouvrir l'onglet Date .
Dans le champ
Cliquez sur le bouton D'ACCORD.
Windows 8
Recherche Recherche), saisissez dans le champ de recherche Élément du panneau de configuration Panneau de contrôle.
Dans la rubrique horloge, langue et région cliquez sur .
Dans la boîte de dialogue région cliquez sur le bouton Options supplémentaires.
Ouvrir l'onglet Date .
Dans le champ après avoir saisi deux chiffres de l'année, interprétez-la comme l'année entre cadres, modifiant la limite supérieure de la paupière.
À mesure que la limite supérieure change, l’année passe automatiquement à la limite inférieure.
Cliquez sur le bouton D'ACCORD.
Windows 7
Cliquez sur le bouton Commencer et sélectionnez Panneau de contrôle.
Sélectionnez l'élément.
Dans la boîte de dialogue région cliquez sur le bouton Options supplémentaires.
Ouvrir l'onglet Date .
Dans le champ après avoir saisi deux chiffres de l'année, interprétez-la comme l'année entre cadres, modifiant la limite supérieure de la paupière.
À mesure que la limite supérieure change, l’année passe automatiquement à la limite inférieure.
Cliquez sur le bouton D'ACCORD.
Modifiez le format de date par défaut pour afficher l'année sur quatre chiffres
Par défaut, lorsque vous entrez des dates dans le classeur, les dates sont formatées pour afficher les années à deux chiffres. Lorsque vous modifiez le format de date par défaut en un autre format à l'aide de cette procédure, l'affichage des dates précédemment saisies dans le classeur sera remplacé par le nouveau format jusqu'à ce qu'il soit formaté dans la boîte de dialogue. Format de cellule" (sur l'onglet Maison dans le groupe nombre cliquez sur le bouton appeler la boîte de dialogue).
Windows 10
Dans la zone de recherche de la barre des tâches, tapez Panneau de contrôle puis sélectionnez Panneau de contrôle.
Dans la rubrique horloge, langue et région cliquez modifier les formats de date, d'heure et de nombres .
Cliquez sur l'icône Langue et normes régionales.
Dans la boîte de dialogue région cliquez sur le bouton Options supplémentaires.
Ouvrir l'onglet Date .
Sur la courte liste Format des dates
Cliquez sur le bouton D'ACCORD.
Windows 8
Faites glisser votre doigt depuis le bord droit de l'écran, appuyez sur Recherche(ou si vous utilisez une souris, déplacez votre souris vers le coin supérieur droit de l'écran, déplacez-la vers le bas et cliquez sur Recherche), saisissez dans le champ de recherche Élément du panneau de configuration puis appuyez sur ou Sélectionner Panneau de contrôle.
Dans la rubrique horloge, langue et région cliquez modifier les formats de date, d'heure ou de nombres.
Dans la boîte de dialogue région cliquez sur le bouton Options supplémentaires.
Ouvrir l'onglet Date .
Sur la courte liste Format des dates sélectionnez un format qui utilise quatre chiffres pour l'année (aaaa).
Cliquez sur le bouton D'ACCORD.
Windows 7
Cliquez sur le bouton Commencer et sélectionnez Panneau de contrôle.
Sélectionnez un élément Options régionales et linguistiques.
Dans la boîte de dialogue région cliquez sur le bouton Options supplémentaires.
Ouvrir l'onglet Date .
Sur la courte liste Format des dates sélectionnez un format qui utilise quatre chiffres pour l'année (aaaa).
Cliquez sur le bouton D'ACCORD.
Changer le système de date dans Excel
Le système de date change automatiquement lorsque vous ouvrez un document créé sur une autre plateforme. Par exemple, si vous travaillez dans Excel et que vous ouvrez un document créé dans Excel pour Mac, le message " système de date 1904" est installé automatiquement.
Vous pouvez modifier le système de date en suivant les étapes ci-dessous.
Sélectionner Déposer > Possibilités > En plus.
Dans la rubrique lors du calcul de ce livre sélectionnez le livre que vous souhaitez puis cochez ou décochez la case utiliser le système de date 1904 .
Problème : rencontrer des problèmes avec les dates entre des livres utilisant des systèmes de dates différents
Vous pouvez rencontrer des problèmes lors du copier-coller de dates et lors de la création de liens externes entre des livres basés sur deux différents systèmes date Les dates peuvent être sorties quatre ans plus tôt ou plus tard que la date prévue. Ces problèmes peuvent survenir si vous utilisez Excel pour Windows, Excel pour Mac ou les deux.
Par exemple, si vous copiez la date du 5 juillet 2007 à partir d'un classeur qui utilise le système de date 1900, puis que vous la collez dans un classeur qui utilise le système de date 1904, la date apparaît comme le 5 juillet 2011, soit 1 462 jours. plus tard. De plus, si vous copiez la date du 5 juillet 2007 à partir d'un classeur qui utilise le système de date 1904, puis que vous la collez dans un classeur qui utilise le système de date 1900, la date sera le 4 juillet 2003, soit 1 462 jours plus tôt. informations générales peuvent être trouvés dans la section Systèmes de dates dans Excel.
Correction d'une erreur de copier-coller
Pour définir la date sur quatre ans et un jour plus tard, ajoutez-y 1 462.
Feuille1!$A$1+1462
Pour définir la date sur quatre ans et avant, soustrayez-y 1 462. Exemple
Feuille 1!$A$1-1462
Plus d'informations
Vous pouvez toujours poser une question à un spécialiste de la communauté Excel Tech, demander de l'aide à la communauté Answers et également suggérer nouvelle fonctionnalité ou amélioration sur le site Excel User Voice
Si vous travaillez avec des données temporelles, vous devrez peut-être insérer un certain nombre de dates dans le tableau. Par exemple, dans une application de suivi des ventes, vous pouvez saisir une série de dates pour un trimestre, en veillant bien sûr à ignorer les dates tombant le week-end.
Utilisation de la saisie semi-automatique
La plupart moyen efficace entrez plusieurs dates, ce qui ne nécessite aucune formule - utilisez la fonction de remplissage automatique. Entrez la première date, puis faites glisser la poignée de remplissage de cellule tout en cliquant avec le bouton droit. Relâchez le bouton de la souris et sélectionnez la commande du menu contextuel A remplir par mois(Fig. 108.1).
Dans certaines situations, vous devez saisir deux dates avant de pouvoir utiliser la fonction de remplissage automatique. Par exemple, si vous devez saisir des dates correspondant à des lundis consécutifs, vous devez saisir les deux premières dates de la série. Sélectionnez ensuite les deux dates, faites un clic droit et sélectionnez dans menu contextuel article Remplir par jour.
Utiliser des formules
Une autre façon de saisir une série de dates consiste à utiliser des formules. L'avantage d'une formule (plutôt qu'une fonction de saisie semi-automatique) est que vous pouvez modifier la première date et que toutes les autres se mettront à jour automatiquement. Entrez la date de début dans la cellule, puis utilisez des formules (en copiant la colonne) pour créer dates supplémentaires. Pour les exemples suivants, supposons que vous ayez entré la première date de la série dans la cellule A1 et la formule dans la cellule A2. Vous pouvez ensuite copier cette formule dans la colonne autant de fois que nécessaire.
Pour générer une série de dates séparées de sept jours, utilisez cette formule : =A1+7 .
Pour créer une série de dates séparées par un mois, utilisez la formule suivante : =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
Pour créer une série de dates séparées par une année, utilisez cette formule : =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)
Pour créer une série incluant uniquement les jours de la semaine (tous les jours sauf le samedi et le dimanche), utilisez la formule suivante. Il suppose que la date dans la cellule A1 n'est pas un jour férié : =IF(WEEKDAY(A1)=6,A1+3,A1+1) . Cette formule renvoie numéro de série dates, vous devez donc formater les cellules pour afficher les dates.
Le plus simple et manière rapide Pour saisir la date ou l'heure actuelle dans une cellule, il suffit d'appuyer sur la combinaison de touches de raccourci CTRL+« ; » (date actuelle) et CTRL+MAJ+« ; » (heure actuelle).
Il est beaucoup plus efficace d'utiliser la fonction TODAY(). Après tout, non seulement il définit, mais il met également à jour automatiquement la valeur de la cellule chaque jour sans intervention de l'utilisateur.
Comment mettre la date actuelle dans Excel
Pour insérer la date actuelle dans Excel, utilisez la fonction TODAY(). Pour cela, sélectionnez l'outil « Formules » - « Date et heure » - « AUJOURD'HUI ». Cette fonction n'a pas d'argument, vous pouvez donc simplement taper dans la cellule : « =TODAY() » et appuyer sur ENTRÉE.
Date actuelle dans la cellule :
S'il est nécessaire que la valeur dans la cellule soit automatiquement mise à jour non seulement date actuelle, mais il est alors préférable d'utiliser la fonction « =TDATE() ».
La date et l'heure actuelles dans la cellule.
Comment définir la date actuelle dans Excel sur les en-têtes et pieds de page
L'insertion de la date du jour dans Excel se réalise de plusieurs manières :
- En définissant les options d’en-tête et de pied de page. L'avantage de cette méthode est que la date et l'heure actuelles sont indiquées sur toutes les pages en même temps.
- Utilisation de la fonction AUJOURD'HUI().
- En utilisant la combinaison de touches de raccourci CTRL+ ; – pour régler la date actuelle et CTRL+SHIFT+ ; – pour régler l’heure actuelle. L'inconvénient est cette méthode La valeur de la cellule ne sera pas automatiquement mise à jour avec les valeurs actuelles lors de l'ouverture du document. Mais dans certains cas, le manque de données constitue un avantage.
- Utilisation de macros VBA à l'aide de fonctions dans le code du programme : Date();Time();Now() .
Les en-têtes et pieds de page vous permettent de définir la date et l'heure actuelles en haut ou en bas des pages du document qui seront sorties sur l'imprimante. De plus, le pied de page nous permet de numéroter toutes les pages du document.
Pour créer la date actuelle dans Excel et la numérotation des pages à l'aide des en-têtes et des pieds de page, procédez comme suit :
Les en-têtes et pieds de page nous permettent de faire plus que simplement définir des dates et des numéros de page. Vous pouvez également ajouter un emplacement pour la signature de la personne responsable du rapport. Par exemple, modifions maintenant la partie inférieure gauche de la page dans la zone d'en-tête et de pied de page.