David a une feuille de calcul qui comprend seulement deux colonnes: une date et une valeur de portefeuille pour chaque date. La feuille de calcul comporte des lignes pour chaque jour de négociation de janvier 1999 à nos jours. David doit supprimer toutes les lignes sauf celles qui correspondent au dernier jour de bourse de chaque mois. Il a essayé de filtrer, mais cela n’a pas aidé, il est donc confus sur la meilleure façon de supprimer les lignes inutiles.

En fournissant des moyens de résoudre ce problème, l’une des clés de la question sur laquelle je vais me concentrer est que David a déclaré que ses données « ont des lignes pour chaque jour de négociation ». Pour moi, cela signifie que certaines dates (jours non marchands) ne sont pas incluses dans ses données. Ceci est essentiel car cela signifie que nous n’avons pas à trouver une solution qui, avant de décider de conserver une ligne ou non, détermine si une date est un jour de négociation.

Cela rend le travail beaucoup plus facile. Maintenant, nous pouvons simplement trouver, à partir des dates de la colonne A, les lignes qui contiennent en fait la dernière date (ou « la plus élevée ») d’un mois donné. Je me concentrerai d’abord sur une approche manuelle qui repose sur une colonne d’aide. Puisque David a dit que ses données se composent uniquement des colonnes A (date) et B (valeur), je suggérerai d’utiliser la colonne C comme colonne d’aide. (Je vais également supposer que la ligne 1 contient des en-têtes de colonne et que les données réelles commencent à la ligne 2.)

En vous assurant que vos données sont triées de manière à ce que les dates soient dans l’ordre croissant, mettez la formule suivante dans la cellule C2:

=IF(DAY(A3)<DAY(A2),"EOM","X")

Copiez cette formule pour toutes vos données, et vous avez essentiellement terminé. Vous pouvez maintenant, si vous le souhaitez, utiliser le filtrage basé sur la colonne C. Si vous filtrez de sorte que seules les lignes contenant « EOM » soient affichées, alors vous avez vos valeurs finales pour chaque mois. Si vous filtrez de sorte que seules les lignes contenant « X » soient affichées, vous pouvez supprimer ces lignes, supprimer le filtre et n’avoir que les lignes avec les valeurs de fin de mois dans vos données.

Comme c’est souvent le cas, il existe une pléthore de formules que vous pouvez utiliser dans la colonne C au lieu de celle que je suggère. J’ai suggéré celui-ci, cependant, car il fait une comparaison très simple qui sera toujours testable – si le jour «baisse» de la valeur dans la ligne suivant celle actuelle. Dans tous les scénarios possibles, cela ne sera vrai qu’au bout d’un mois. Donc, cette ligne est marquée avec « EOM » et le reste avec « X ».

Je dois souligner que si vous choisissez d’utiliser une formule différente, assurez-vous qu’elle ne teste pas si la date de la colonne A est le dernier jour du mois. Pourquoi? Parce que ce n’est peut-être pas le cas – rappelez-vous que dans les données de David, la colonne A contient les dates des jours de négociation, et il est très possible que le dernier jour de négociation d’un mois ne tombe pas le dernier jour du mois. (Les week-ends et jours fériés, en d’autres termes, sont exclus, par définition, des données de David.)

Si vous utilisez Office 365 (ou ce que Microsoft, ces jours-ci, appelle Microsoft 365), il existe également un moyen de récupérer uniquement les dates de fin de mois et leurs valeurs. Supposons que vos données soient en A2: B5000. (N’oubliez pas que A1: B1 contient des en-têtes de colonne.) Mettez la formule suivante dans la cellule E2:

=FILTER(A2:B5000,DAY(A3:A5001)<DAY(A2:A5000),1)

C’est ça; une seule formule dans une seule cellule. Vous devrez peut-être formater la colonne E pour afficher correctement les dates, mais cette utilisation de la fonction FILTER effectue la même comparaison déjà décrite et extrait uniquement les dates et les valeurs de fin de mois. (C’est assez élégant, si vous y réfléchissez.) N’oubliez pas, cependant, que cela ne fonctionnera que dans Office 365; cela ne fonctionnera pas dans Excel 2019, Excel 2016 ou toute version antérieure du programme.

Si vous préférez une approche macro-basée, alors la courte macro suivante fera l’affaire:

Sub DelRows()

Dim LastRow As Long     Dim J As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row     LastRow = LastRow - 1     For J = LastRow To 2 Step -1         If Month(Cells(J, 1)) = Month(Cells(J + 1, 1)) Then             Rows(J).EntireRow.Delete         End If     Next J End Sub

La macro détermine la dernière ligne de la feuille de calcul (stockée dans la variable LastRow), puis utilise une boucle For …​ Next pour parcourir les lignes en arrière. Si le mois de la ligne actuelle est égal au mois de la ligne suivante, la ligne est supprimée. Notez que la macro décrémente LastRow avant de sauter dans la boucle For …​ Next. Ceci est fait parce que l’hypothèse est que la dernière ligne de données sera toujours le dernier jour de négociation de vos données, donc elle reste toujours avec les données dépouillées.

Cette macro peut être lente à exécuter, car elle supprime la plupart des lignes de la feuille de calcul, une par une. Lorsqu’il sera terminé, cependant, il ne vous restera plus que vos données de fin de mois.

Une dernière remarque: les approches utilisées dans cette astuce sont, à l’exception de la fonction FILTER, destructrices de vos données. Lorsque vous les utilisez, les données de votre feuille de calcul seront perdues à jamais. Cela signifie que vous devriez réfléchir à deux fois (ou trois fois) avant de les exécuter sur quoi que ce soit, sauf une copie de vos données d’origine.

_Note: _

Si vous souhaitez savoir comment utiliser les macros décrites sur cette page (ou sur toute autre page des sites ExcelTips), j’ai préparé une page spéciale qui comprend des informations utiles.

lien: / excelribbon-ExcelTipsMacros [Cliquez ici pour ouvrir cette page spéciale dans un nouvel onglet de navigateur].

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (13768) s’applique à Microsoft Excel 2007, 2010, 2013, 2016, 2019 et Excel dans Office 365.