1. Opérations sur les dates avec Excel

Opérations sur les dates avec Excel.mp4

Objectifs attendus :

Comprendre comment Excel gère les dates et les heures en interne (système de numérotation séquentielle).
Maîtriser les fonctions de date comme AUJOURDHUI(), MAINTENANT(), ANNEE(), MOIS(), JOUR().
Effectuer des calculs de durées (nombre de jours entre deux dates, ajout ou retrait de jours/mois/années).
Utiliser les fonctions de date dans des conditions (SI, NB.SI.ENS, etc.).

Description de la video

Description : Ce cours vous enseigne comment manipuler des dates et des heures dans Excel. Vous apprendrez à calculer des durées, à extraire des informations spécifiques (jours, mois, années) et à utiliser les dates dans des formules complexes. C'est essentiel pour le suivi de projets, les calculs de paie ou la gestion d'échéances.

Exercices d'application

Exercice 1 : Calcul de la durée de travail totale

Vous êtes responsable du suivi du temps pour une équipe. Vous devez calculer le nombre total d'heures travaillées par jour, en tenant compte des pauses, et la durée totale sur la semaine.

Calcul de la durée journalière :
- Créez un tableau avec les colonnes Heure de début, Heure de fin et Durée (h:mm).
- Saisissez des heures au format hh:mm (ex : 09:00 et 17:30).
- Dans la colonne Durée, entrez la formule simple =B2-A2.
- Astuce : Si le résultat est un nombre décimal, formatez la cellule Durée en [h]:mm (avec les crochets pour afficher les heures au-delà de 24).
Calcul du total hebdomadaire :
- Créez une nouvelle ligne pour le "Total Hebdomadaire".
- Utilisez la fonction SOMME sur la colonne Durée (=SOMME(C2:C6) par exemple).
- Si le résultat est incorrect (par exemple, 10 heures alors que vous savez que c'est plus), c'est probablement un problème de format. Formatez la cellule du total en [h]:mm. Cela garantira que le total s'affiche correctement, même s'il dépasse 24 heures.

Exercice 2 : Planification de tâches et échéances

Vous gérez un projet et avez besoin de calculer la date et l'heure de fin d'une tâche en ajoutant une durée spécifique à une date de début.

Création du tableau :
- Créez un tableau avec les colonnes Tâche, Date et Heure de Début, Durée (en heures) et Date et Heure de Fin.
- Dans la colonne Durée, entrez une durée en heures (ex : 5, 25, 48).
Calcul de la date et heure de fin :
- Pour ajouter la durée à la date de début, vous devez convertir la durée en une valeur que Excel peut comprendre comme une fraction d'un jour. Une heure est 1/24 d'un jour.
- Dans la colonne Date et Heure de Fin, entrez la formule =B2+(C2/24).
- Astuce : Vous pouvez aussi utiliser la fonction TEMPS pour plus de précision. D2+TEMPS(E2;F2;G2) où E2 contient les heures, F2 les minutes et G2 les secondes.

Exercice 3 : Extraction d'informations de date et heure

Vous avez une liste de transactions avec des horodatages complets (date et heure). Vous devez analyser les ventes par jour de la semaine et par heure de la journée pour identifier des tendances.

Extraction du jour de la semaine :
- Ajoutez une colonne Jour de la semaine.
- Utilisez la fonction JOURSEM pour obtenir un numéro de 1 à 7 pour le jour de la semaine (=JOURSEM(A2;2)). Le 2 à la fin indique que la semaine commence par le lundi (1).
- Vous pouvez ensuite utiliser une formule SI ou CHOISIR pour convertir ces nombres en noms de jours ("Lundi", "Mardi", etc.).
Extraction de l'heure :
- Ajoutez une colonne Heure de la transaction.
- Utilisez la fonction HEURE pour extraire l'heure de l'horodatage (=HEURE(A2)).

2. Calculs sur les heures et dates dans Excel

Calculs sur les heures et dates dans Excel.mp4

Objectifs attendus :

Comprendre le fonctionnement interne d'Excel pour les dates (nombres entiers) et les heures (fractions de nombres entiers).
Effectuer des additions et des soustractions d'heures et de dates pour calculer des durées totales.
Utiliser les fonctions HEURE(), MINUTE() et SECONDE() pour extraire des parties d'une heure.
Formater correctement les cellules pour afficher des durées supérieures à 24 heures ou des jours de la semaine.

Description de la video

Ce titre se concentre sur les subtilités des calculs impliquant des dates et des heures, notamment lorsqu'ils s'étendent sur plus de 24 heures ou qu'il faut extraire des informations précises comme le jour de la semaine. Le tutoriel vous apprendra à manipuler ces valeurs numériques et à utiliser le format de cellule adéquat pour un affichage correct.

Exercices d'application

Exercice 1 : Calcul de l'âge et de l'ancienneté

Vous avez une liste d'employés avec leurs dates de naissance et leurs dates d'embauche. Vous devez calculer leur âge exact et leur ancienneté dans l'entreprise.

Calcul de l'âge :
- Créez un tableau avec les colonnes Nom, Date de Naissance et Âge.
- Dans la cellule de l'âge du premier employé, entrez la formule =ENT((AUJOURDHUI()-B2)/365,25).
  - B2 est la cellule de la date de naissance.
  - AUJOURDHUI() est une fonction qui renvoie la date du jour.
- Tirez la formule vers le bas pour calculer l'âge de tous les employés.
Calcul de l'ancienneté :
- Ajoutez une colonne Date d'Embauche et une colonne Ancienneté.
- Pour calculer l'ancienneté en années, mois et jours, utilisez la fonction DATEDIF.
- Dans la cellule d'ancienneté, entrez la formule =DATEDIF(C2;AUJOURDHUI();"Y") & " ans, " & DATEDIF(C2;AUJOURDHUI();"YM") & " mois, " & DATEDIF(C2;AUJOURDHUI();"MD") & " jours".
  - C2 est la cellule de la date d'embauche.
  - Y pour les années, YM pour les mois (en excluant les années complètes), MD pour les jours (en excluant les mois complets).

Exercice 2 : Gestion des échéances de projet

Vous suivez les étapes d'un projet avec des dates de début et des durées en jours. Vous devez calculer la date de fin, et mettre en évidence les tâches qui sont en retard.

Calcul de la date de fin :
- Créez un tableau avec les colonnes Tâche, Date de Début, Durée (en jours) et Date de Fin.
- Dans la cellule Date de Fin, entrez la formule =B2+C2.
  - B2 est la date de début.
  - C2 est la durée en jours.
- Copiez la formule pour toutes les tâches.
Mise en évidence des retards :
- Ajoutez une colonne Statut où vous devrez identifier les tâches en cours ou en retard.
- Dans cette colonne, utilisez une fonction SI pour afficher "En retard" si la date de fin est antérieure à la date du jour.
- La formule sera =SI(D2<AUJOURDHUI();"En retard";"En cours").
- Appliquez une mise en forme conditionnelle pour mettre en rouge les cellules de la colonne Date de Fin qui sont en retard.

Exercice 3 : Détection des week-ends et des jours fériés

Vous devez calculer la date d'achèvement d'une tâche en fonction d'un nombre de jours ouvrés, en excluant les samedis, dimanches et jours fériés.

Utilisation de la fonction SERIE.JOUR.OUVRE :
- Créez un tableau avec Date de Début, Jours Ouvrés et Date de Fin.
- Créez une liste des jours fériés dans une autre partie de votre feuille.
- Dans la cellule Date de Fin, entrez la formule =SERIE.JOUR.OUVRE(A2;B2;C2:C10).
  - A2 est la date de début.
  - B2 est le nombre de jours ouvrés.
  - C2:C10 est la plage des jours fériés que vous avez définis.
- Cette fonction calculera automatiquement la date de fin en sautant les week-ends et les jours fériés.

3. Extraire des données entre deux dates avec Excel

Les_POURCENTAGES_avec_EXCEL_-_Convertir_et_Calculer__Tutoriel_simple_(0).mp4

Objectifs attendus :

Utiliser les filtres automatiques d'Excel pour sélectionner des données par date.
Maîtriser les critères de filtrage de date (égal à, avant, après, entre).
Combiner plusieurs conditions pour extraire des données entre deux dates spécifiques.
Appliquer des formules pour extraire des données dynamiquement.

Description de la video

Ce tutoriel vous explique comment filtrer et extraire des informations spécifiques à partir d'une grande base de données, en ne conservant que les entrées qui correspondent à une plage de dates donnée. C'est une compétence essentielle pour analyser des données sur une période précise.

Exercices d'application

Exercice 1 : Filtrage simple avec un tableau

Imaginez que vous avez un tableau de bord des commandes avec des milliers de lignes, et que vous devez rapidement trouver toutes les commandes passées au mois de janvier 2024.

Préparation des données : Créez un tableau avec les colonnes ID Commande, Date de Commande, et Montant. Remplissez la colonne Date de Commande avec des dates de différentes années et mois.
Application du filtre :
- Sélectionnez une cellule dans votre tableau et allez dans l'onglet Données, puis cliquez sur Filtrer. Des flèches de filtre apparaîtront sur les en-têtes de colonne.
- Cliquez sur la flèche du filtre de la colonne Date de Commande.
- Dans le menu qui apparaît, sous Filtres par dates, sélectionnez Entre....
- Dans la fenêtre qui s'ouvre, entrez "01/01/2024" comme date de début et "31/01/2024" comme date de fin.
- Cliquez sur OK. Votre tableau affichera désormais uniquement les commandes de janvier 2024.

Exercice 2 : Extraction dynamique avec les fonctions FILTRE et GRANDE.VALEUR

Cet exercice est plus avancé. Vous voulez une solution qui s'adapte automatiquement et affiche les commandes des 30 derniers jours dans une zone distincte de votre feuille de calcul.

Création du tableau source : Créez un grand tableau avec les mêmes en-têtes qu'auparavant (ID Commande, Date de Commande, Montant).
Mise en place des critères de dates :
- Dans deux cellules séparées (par exemple, D2 et E2), définissez les dates de début et de fin.
- Dans D2, entrez la formule =AUJOURDHUI()-30 pour obtenir la date d'il y a 30 jours.
- Dans E2, entrez la formule =AUJOURDHUI() pour obtenir la date du jour.
Utilisation de la fonction FILTRE :
- Dans une cellule vide (par exemple, G2), utilisez la fonction FILTRE. La syntaxe de base est =FILTRE(plage_données; critère1; critère2).
- Pour extraire les données entre vos deux dates, la formule serait : =FILTRE(A2:C100; (B2:B100>=D2)*(B2:B100<=E2); "Aucune donnée trouvée").
  - A2:C100 est la plage de votre tableau source.
  - (B2:B100>=D2) est le premier critère (la date est égale ou postérieure à la date de début).
  - (B2:B100<=E2) est le deuxième critère (la date est égale ou antérieure à la date de fin).
  - * agit comme un "ET" logique, combinant les deux critères.
  - "Aucune donnée trouvée" est le message affiché si aucune donnée ne correspond.
Application de la mise en forme conditionnelle : Pour mettre en évidence visuellement les commandes les plus récentes, appliquez une mise en forme conditionnelle sur les données extraites, en utilisant des barres de données sur la colonne Montant pour une meilleure visibilité.

Page updated

Google Sites

Report abuse