Vous avez besoin d’extraire le trimestre de vos dates dans Excel ? Ce post va vous montrer comment extraire le trimestre du calendrier à partir de n’importe quelle valeur de date dans Excel.
Un projet Excel peut vous demander de résumer vos données par intervalles de dates. L’un de ces intervalles de dates est le trimestre.
Un trimestre est un intervalle de trois mois, il y a donc quatre trimestres dans chaque année. Il s’agit des trimestres standard de l’année civile.
- Le trimestre 1 comprend toutes les dates entre le 1er janvier et le 31 mars.
- Le deuxième trimestre comprend toutes les dates entre le 1er avril et le 30 juin.
- Le troisième trimestre comprend toutes les dates entre le 1er juillet et le 30 septembre.
- Le quatrième trimestre comprend toutes les dates entre le 1er octobre et le 31 décembre.
Les trimestres sont importants dans des tâches telles que les rapports financiers, car les performances d’une entreprise sont souvent évaluées sur une période de trois mois. Heureusement, Excel permet de travailler avec des données trimestrielles de différentes manières.
Comment extraire le trimestre d’une date Excel ?
Ce billet de blog explique toutes les différentes façons d’extraire le trimestre d’une date Excel. Vous pouvez choisir la méthode la mieux adaptée à votre situation, qu’il s’agisse de formules, de VBA, de Power Query, de tableau croisé dynamique, de Power Pivot ou de scripts Office.
Obtenez votre copie du classeur d’exemple utilisé dans cet article pour suivre le processus.
Extraire le quart en utilisant la fonction CHOOSE
Une façon d’extraire le quart est d’utiliser la fonction CHOOSE. La fonction CHOOSE vous permet de sélectionner une valeur dans une liste en fonction d’une valeur d’index.
L’idée est d’extraire d’abord le mois de votre date, puis de l’utiliser comme index pour sélectionner le trimestre dans une liste.
= MONTH ( B2 )Supposons que votre date se trouve dans la cellule B2. Sélectionnez une cellule vide et tapez la fonction MONTH ci-dessus dans la barre de formule. Le mois sera extrait sous la forme d’un nombre compris entre 1 et 12.
= CHOOSE ( MONTH ( B2 ), 1, 1, 1, 2, 2, 2, 3, 3, 3, 4, 4, 4 )Vous pouvez ensuite utiliser le résultat de la fonction MONTH comme premier argument de la fonction CHOOSE.
Les arguments restants indiquent quel serait le trimestre pour chacun des douze mois possibles, de sorte que les arguments restants sont toujours 1, 1, 1, 2, 2, 2, 3, 3, 3, 4, 4, 4.
- Les mois 1, 2 et 3 sont rattachés au trimestre 1.
- Les mois 4, 5 et 6 sont rattachés au trimestre 2.
- Les mois 7, 8 et 9 sont mis en correspondance avec le trimestre 3.
- Les mois 10, 11 et 12 sont rattachés au trimestre 4.
Appuyez sur la touche Entrée pour quitter la barre de formule. Dans cet exemple, 2 est le quart correct !
La fonction CHOOSE est utile lorsqu’il y a un petit nombre de valeurs possibles. Comme il n’y a que douze mois possibles, la fonction CHOOSE permet d’extraire le trimestre.
💡 Conseil: vous pouvez ajuster cette formule pour qu’elle fonctionne pour des trimestres d’années non civiles. Par exemple, si votre année commence le 1er juillet, vous pouvez utiliser 3, 3, 3, 4, 4, 4, 1, 1, 1, 2, 2, 2 pour les arguments CHOISIR.
Extraire le quart en utilisant la fonction ROUNDUP
ROUNDUP est une autre fonction que vous pouvez utiliser pour extraire le quart d’une date.
Cette méthode est concise car elle tire parti de la longueur fixe d’un quart, ainsi que de l’arrondi des nombres entiers.
= MONTH ( B2 )Extrayez le mois de votre date en sélectionnant une cellule vide et en tapant la fonction MONTH dans la barre de formule. Vous obtenez ainsi un nombre compris entre 1 et 12.
= MONTH ( B2 ) / 3Divisez ce mois par le nombre de mois par trimestre. Le nombre de mois par trimestre est toujours de 3, il suffit donc de diviser par 3.
= ROUNDUP ( MONTH ( B2 ) / 3, 0 )Tapez la fonction ROUNDUP pour arrondir le résultat de la division.
Cet arrondi est nécessaire si le mois n’est pas un multiple pair de 3. Par exemple, le mois d’avril est le quatrième mois, mais 4 n’est pas un multiple pair de 3 car 4 / 3 = 1,333.
En arrondissant la valeur de 1,333 à 2, vous arrivez correctement au quart 2 pour avril.
Spécifiez zéro 0 pour le second argument de ROUNDUP pour indiquer que vous voulez arrondir à la valeur entière suivante.
Appuyez sur Entrée pour quitter la barre de formule, et vous avez maintenant le quart de votre date !
Extraire le quart en utilisant VBA
Si vous devez extraire des trimestres d’un ensemble de dates et que vous souhaitez une valeur statique, vous pouvez utiliser le VBA plutôt que les fonctions.
Avec cette méthode, vous pouvez parcourir en boucle une plage de dates sélectionnée et extraire les quartiers dans une plage adjacente.
Pour mettre en œuvre cette méthode, ouvrez votre éditeur VBA à partir d’Excel en allant dans l’onglet du ruban Développeur et en cliquant sur Visual Basic.
Si l’onglet Développeur n’est pas activé, vous pouvez toujours ouvrir l’éditeur en appuyant sur Alt + F11 sur votre clavier.
📝 Remarque: l’onglet Développeur est désactivé par défaut. Vous pouvez activer l’onglet Développeur si vous ne le voyez pas dans votre ruban Excel.
Allez dans le menu Insertion et sélectionnez Module.
Cela créera un endroit où vous pourrez ajouter votre code VBA.
Sub ExtractQuarters()
Dim rng As Range
For Each rng In Selection
If IsDate(rng.Value) Then
rng.Offset(0, 1).Value = DatePart("q", rng.Value)
End If
Next rng
End Sub
Collez le code de la macro VBA ci-dessus dans le nouveau module.
Le code est conçu pour parcourir en boucle la plage de cellules sélectionnée. Si la cellule contient une date, le quart sera extrait dans la cellule adjacente à l’aide de la fonction DatePart intégrée.
DatePart extrait le quart uniquement si vous fournissez l’argument "q". Le quart est extrait dans rng.Offset(0, 1) qui est la cellule de la colonne adjacente.
Pour exécuter le code, retournez à votre feuille et sélectionnez la plage de cellules de dates souhaitée.
Ensuite, allez dans l’onglet du ruban Développeur et cliquez sur Macros pour ouvrir la boîte de dialogue Macro.
Vous pouvez également utiliser le raccourci clavier Alt + F8 pour ouvrir la boîte de dialogue Macro.
Dans la fenêtre Macro, sélectionnez la macro ExtractQuarters, puis cliquez sur Exécuter.
Après l’exécution de la macro, les quarts apparaissent dans la colonne adjacente !
⚠️ Attention: Ce code produira des valeurs statiques et les résultats ne seront pas mis à jour lorsque les dates changeront. Vous devrez réexécuter la macro pour mettre à jour les trimestres.
Extraire le trimestre en utilisant Power Query
Dans les situations où vous devez extraire des trimestres à partir de dates qui ne se trouvent pas dans Excel, l’utilisation de Power Query est une bonne méthode pour le faire.
Avec Power Query, vous pourrez facilement extraire les trimestres dans une nouvelle colonne Excel.
Power Query est disponible dans Excel 2016 et les versions ultérieures. Si vous avez une version d’Excel antérieure à 2016, vous devrez peut-être installer le complément gratuit Power Query.
Dans l’exemple ci-dessus, les dates se trouvent dans un fichier texte CSV appelé DatedTransactions.csv.
Pour interroger ce fichier à l’aide de Power Query, allez dans l’onglet Données du ruban Excel, puis cliquez sur From Text/CSV.
Recherchez le fichier, puis cliquez sur Importer.
Avant que Power Query importe les données, une fenêtre d’aperçu apparaît. Parce que vous voulez ajouter une nouvelle colonne à ces données, cliquez sur Transformer les données.
Dans l’éditeur Power Query, sélectionnez votre colonne de dates. Allez sur l’onglet du ruban Ajouter une colonne, puis cliquez sur le menu Date.
📝 Remarque: le menu Date sera grisé si vous n’avez pas sélectionné une colonne de date.
Dans le menu Date, allez dans le sous-menu Trimestre, puis sélectionnez le sous-menu Trimestre de l’année.
= Table.AddColumn(#"Changed Type", "Quarter", each Date.QuarterOfYear([TransactionDate]), Int64.Type)Votre nouvelle colonne Quarter est créée et contient tous les trimestres extraits.
Power query créera automatiquement la formule du code m ci-dessus en arrière-plan, qui extrait les valeurs du trimestre.
Pour importer toutes ces colonnes dans Excel, allez dans l’onglet du ruban Accueil, puis cliquez sur Fermer et charger.
Power Query importe le fichier entier, y compris les trimestres extraits, dans une nouvelle feuille Excel.
C’est tout ce qui est nécessaire pour la méthode Power Query !
💡 Conseil: si les données du fichier source changent, vous pouvez facilement mettre à jour les résultats de Power Query. Allez dans l’onglet Données et cliquez sur le bouton Actualiser pour rafraîchir la requête.
Extraire le trimestre en utilisant un tableau croisé dynamique
Les tableaux croisés dynamiques sont un moyen simple de résumer vos données dans Excel.
Ils vous permettent de découper, de découper et d’agréger rapidement vos données en quelques clics.
Les tableaux croisés dynamiques fonctionnent aussi très bien avec les dates ! Vous pouvez résumer vos données en regroupant les dates en années, mois et trimestres.
Tout d’abord, vous devrez créer un tableau croisé dynamique basé sur vos données avec les valeurs de date.
Sélectionnez une cellule dans votre ensemble de données, puis allez dans l’onglet Insertion et sélectionnez la commande Tableau croisé dynamique.
Cela ouvrira le menu Tableau croisé dynamique à partir d’un tableau ou d’une plage où vous pourrez sélectionner l’emplacement de votre nouveau tableau croisé dynamique.
Sélectionnez soit une nouvelle feuille de travail, soit une feuille de travail existante pour l’emplacement et appuyez sur le bouton OK.
Vous avez maintenant un tableau croisé dynamique vide et lorsque vous sélectionnez une cellule à l’intérieur de celui-ci, vous devriez voir la liste des champs du tableau croisé dynamique apparaître sur la droite.
Ajoutez votre champ de date dans la zone Rows de la liste PivotTable Fields.
Vous pouvez remarquer qu’Excel crée des champs supplémentaires pour les années, les trimestres et les mois.
Si le champ Trimestre n’apparaît pas automatiquement, vous pouvez le créer en regroupant votre champ de date.
Cliquez avec le bouton droit de la souris sur la colonne de date dans votre tableau croisé dynamique et sélectionnez l’option Groupe dans le menu.
Cela ouvrira le menu Groupement et vous pourrez sélectionner l’option Quartiers et appuyer sur le bouton OK.
Le résultat final dans votre tableau croisé dynamique est maintenant résumé par trimestre !
Extraire le trimestre en utilisant une colonne calculée de Power Pivot
Power Pivot est une fonction d’Excel qui permet de calculer de nouvelles colonnes de valeurs sur la base de vos données existantes.
Une seule formule DAX Expression est nécessaire pour calculer la colonne entière. Cela fait de Power Pivot une méthode puissante d’extraction de données.
Vous devez vous assurer que le module complémentaire Power Pivot est activé dans votre Excel pour extraire des trimestres d’une colonne de dates à l’aide de Power Pivot.
Allez dans l’onglet du ruban Développeur puis cliquez sur Compléments COM pour activer le complément Power Pivot d’Excel.
Assurez-vous que l’option Microsoft Power Pivot pour Excel est cochée dans la fenêtre des compléments COM, puis cliquez sur le bouton OK.
Power Pivot est maintenant activé !
Dans l’exemple ci-dessus, la feuille contient deux colonnes de données. Vous pouvez créer un tableau Power Pivot à partir de ces données.
Sélectionnez la table entière, puis allez dans l’onglet du ruban Power Pivot et cliquez sur la commande Ajouter au modèle de données.
Si vos données ne se trouvent pas dans un tableau Excel, il vous sera demandé de créer un tableau Excel.
Vous pouvez accepter la valeur par défaut en cliquant sur le bouton OK dans la fenêtre Créer un tableau.
Cela ouvrira la fenêtre de l’éditeur Power Pivot où vous pourrez ajouter une nouvelle colonne calculée.
Allez dans l’onglet du ruban Conception, puis cliquez sur le bouton Ajouter. Vous créez ainsi une nouvelle colonne calculée, vide, pour les trimestres.
= FORMAT ( [TransactionDate] , "q" )La formule ci-dessus peut être utilisée pour calculer le trimestre.
Cet exemple extrait les trimestres de la colonne TransactionDate. Le deuxième argument "q" indique que le trimestre est le résultat du format souhaité.
Malheureusement, la fonction FORMAT renvoie le quart comme une valeur texte. Mais il serait peut-être préférable que le quart se comporte comme un nombre !
Vous pouvez modifier le type de données de la colonne afin que les résultats soient des valeurs numériques plutôt que du texte.
Sélectionnez la colonne et allez dans l’onglet Accueil du ruban, puis sélectionnez l’option Nombre entier dans la liste déroulante Type de données.
= INT ( FORMAT ( [TransactionDate] , "q" ) )Une autre option serait d’utiliser la formule ci-dessus pour forcer le type de données à un nombre entier. La fonction INT renvoie la partie entière d’un nombre et force la colonne à un type de données de type nombre entier.
Vous pouvez ensuite renommer la colonne calculée en lui donnant un nom plus intuitif, comme Quarter.
Double-cliquez avec le bouton gauche de la souris sur l’en-tête de la colonne calculée 1 et renommez-la en Quart.
La colonne calculée est complète !
Vous pouvez maintenant cliquer sur l’icône Enregistrer dans le coin supérieur gauche de l’éditeur de power pivot, puis fermer l’éditeur.
Comment pouvez-vous utiliser cette colonne calculée ? Elle peut être utilisée dans un PivotTable connecté au modèle de données.
Allez dans l’onglet Insertion et cliquez sur la partie inférieure de la commande PivotTable puis sélectionnez From Data Model.
Sélectionnez un emplacement pour votre tableau croisé dynamique, soit en tant que nouvelle feuille de travail, soit en tant que feuille de travail existante, puis appuyez sur le bouton OK.
Dans le volet Champs du tableau croisé dynamique, vous pouvez ensuite développer le tableau pour afficher le nouveau champ Trimestre que vous avez créé.
Faites glisser Quarter dans la zone Rows et faites glisser Amount dans la zone Values. Le tableau croisé dynamique qui en résulte présente le montant total par trimestre.
Ce tableau croisé dynamique est un bon exemple de la façon dont les données trimestrielles peuvent être présentées.
Extraire le trimestre en utilisant des scripts Office
Les scripts Office sont un moyen relativement nouveau d’automatiser des tâches Excel. Pour créer un script Office, vous devez utiliser Excel pour le web et être connecté à une licence professionnelle Microsoft 365.
Un script Office peut être construit pour boucler une plage sélectionnée de dates, extraire le quart et placer la valeur dans une colonne adjacente.
Ouvrez votre fichier dans le navigateur web en vous connectant à http://www.office.com.
Allez dans l’onglet du ruban Automate, puis sélectionnez l’élément de menu Nouveau script.
function main(workbook: ExcelScript.Workbook) {
/* Extracts quarters from selected range to adjacent column */
/* Selected range */
let rng = workbook.getSelectedRange();
let rows = rng.getRowCount();
let cols = rng.getColumnCount();
/* Loop through the cells */
for (let i = 0; i < rows; i++) {
for (let j = 0; j < cols; j++) {
/* Cell date */
let cell_date = rng.getCell(i, j).getValue() as number;
/* Convert to Office Scripts date */
let script_date = OfficeScriptsDate(cell_date);
/* Extract quarter */
let currQtr = Qtr(script_date);
/* Put quarter in adjacent column */
rng.getCell(i, j + 1).setValue(currQtr);
};
};
}Remplacez tout le contenu de la fenêtre de l’éditeur de code par le code Office Script ci-dessus.
Ce code est la fonction principale, qui doit être nommée main pour indiquer qu’elle sera la première fonction à être exécutée dans ce script Office.
La fonction principale bouclera la plage sélectionnée et extraira les quartiers dans la colonne adjacente, mais cette fonction aura besoin de l’aide de deux morceaux de code supplémentaires.
function OfficeScriptsDate(cell_date: number) {
/* Converts cell date to Office Scripts date */
/* Days between Jan 1, 1900 and Jan 1, 1970 */
const days_offset = 25568;
/* Seconds per day */
const sec_per_day = 86400;
/* Milliseconds per second */
const ms_per_sec = 1000;
/* Days between Jan 1, 1970 and the cell date */
var xl_days = Math.floor(cell_date - days_offset);
/* Convert days to seconds */
var xl_sec = xl_days * sec_per_day;
/* Convert seconds to milliseconds and convert to datetime */
var date_info = new Date(xl_sec * ms_per_sec);
/* Convert datetime to date-only */
return new Date(date_info.getFullYear(), date_info.getMonth(), date_info.getDate());
}
Faites défiler jusqu’au bas de la fenêtre de l’éditeur de code et ajoutez le code ci-dessus.
Cette fonction OfficeScriptsDate convertit une date de cellule en une date Office Scripts.
Une date Excel est en fait un nombre représentant le nombre de jours depuis le 1er janvier 1900. En revanche, Office Scripts utilise le 1er janvier 1970 comme valeur de départ. Il y a donc toujours un écart de 25 568 jours !
En soustrayant 25 568 jours de la date de la cellule, on la convertit en une date Office Scripts.
function Qtr(script_date: Date) {
/* Extracts quarter from date */
/* Month from date */
let monthNum = script_date.getMonth();
/* Quarter from month */
switch (monthNum) {
case 0: return 1; //Jan
case 1: return 1; //Feb
case 2: return 1; //Mar
case 3: return 2; //Apr
case 4: return 2; //May
case 5: return 2; //Jun
case 6: return 3; //Jul
case 7: return 3; //Aug
case 8: return 3; //Sep
case 9: return 4; //Oct
case 10: return 4; //Nov
case 11: return 4; //Dec
};
};
Faites à nouveau défiler l’écran jusqu’au bas de la fenêtre de l’éditeur de code et ajoutez le code ci-dessus.
Cette fonction Qtr va simplement extraire le trimestre de la date en fonction de son mois.
📝 Note: Les mois commencent à partir de 0, ce qui signifie que janvier est le mois 0 dans les scripts Office !
Sélectionnez une colonne de dates puis cliquez sur le bouton Exécuter dans l’éditeur de code Office Script.
Dans l’exemple ci-dessus, la colonne B contient les dates sélectionnées. Après l’exécution du code, les trimestres extraits apparaissent dans la colonne C adjacente !
⚠️ Attention: Assurez-vous que la colonne adjacente est vide car le script Office écrasera tout ce qui s’y trouve !
Conclusions
L’analyse et la synthèse de vos données par trimestre civil peuvent être nécessaires pour vos rapports. Mais si vous ne disposez que d’une date, vous devrez extraire le trimestre d’une manière ou d’une autre.
Heureusement, il existe de nombreuses façons de tirer le meilleur parti de vos rendez-vous.
Pour une quantité relativement faible de données, l’utilisation de la fonction CHOOSE ou ROUNDUP est un bon choix. Les deux options permettront d’accomplir la tâche, mais la formule ROUNDUP sera un peu plus concise.
Si vous avez besoin d’un résultat statique qui ne changera pas si vous modifiez les dates, la méthode VBA pourrait être le bon choix. Mais vous devrez être à l’aise avec le codage en VBA et les problèmes de sécurité supplémentaires que posent les classeurs équipés de macros.
Pour les grands ensembles de données, la méthode du tableau croisé dynamique ou Power Pivot donne de bons résultats lorsque vous devez résumer les résultats par trimestre. Alors que la méthode Power Query est particulièrement adaptée lorsque vos données se trouvent en dehors d’Excel.
L’extraction du quart à l’aide de scripts Office peut s’avérer supérieure si vous travaillez dans un environnement Office uniquement Web.