Des astuces Excel expliquées, pas seulement montrées

Deux cellules suffisent en entrée : la date de début et le nombre de jours. La fonction SEQUENCE se charge du reste, avec une formule de la forme SEQUENCE(nombre_de_jours ; 1 ; date_de_début ; 1). Elle renvoie une plage entière à partir d’une seule cellule — ce qu’Excel appelle un résultat « déversé ».

Le résultat sort en nombres : il ne reste qu’à appliquer un format de date à la colonne. Modifiez la date de début ou la durée, et le calendrier entier se reconstruit instantanément.

L’intérêt va plus loin que l’affichage. Cette plage déversée peut alimenter une liste déroulante : dans la validation des données, on référence la première cellule suivie du signe dièse, par exemple $B$5#. La liste suit alors automatiquement la taille du calendrier, sans plage nommée à redimensionner.

Ajouter une colonne calculée suppose de savoir quelle fonction employer, et dans quel ordre les imbriquer. C’est précisément là que la plupart des utilisateurs décrochent. Le volet Copilot, intégré au ruban d’Excel, renverse la démarche : on décrit le résultat attendu en une phrase, et il propose une colonne prête à être insérée.

La formule produite reste une formule Excel ordinaire, visible et modifiable. Sur une extraction de noms et de codes, Copilot mobilise sans hésiter LET pour nommer ses étapes intermédiaires, puis GAUCHE, STXT, NBCAR, MAJUSCULE ou MINUSCULE selon le besoin. Le résultat est souvent plus lisible que ce qu’on aurait écrit dans l’urgence.

C’est un accélérateur, pas un substitut à la compréhension : c’est vous qui signez le classeur, et vous qui devrez le corriger dans six mois.

Excel sait lire un PDF depuis l’onglet Données : Obtenir des données, À partir d’un fichier, À partir d’un PDF. Power Query s’ouvre et présente ce qu’il a détecté — chaque tableau repéré, page par page, dans un navigateur d’objets.

On sélectionne le ou les tableaux voulus, on corrige au passage ce qui doit l’être — en-têtes promus, colonnes vides supprimées, types rétablis — puis on charge dans la feuille. Aucune formule, aucun code.

Le vrai bénéfice arrive au PDF suivant : la requête étant enregistrée, remplacer le fichier source et actualiser suffit. Ce qui était une corvée mensuelle devient un clic.

MAX et MIN ne savent pas filtrer. Pendant des années, obtenir « le montant le plus élevé pour la région Est » supposait une formule matricielle validée par une combinaison de touches, incompréhensible pour qui reprenait le fichier.

MAX.SI.ENS et MIN.SI.ENS ont réglé la question. La syntaxe suit celle de SOMME.SI.ENS : d’abord la plage dont on cherche l’extremum, puis les couples plage de critère et critère, autant que nécessaire. MAX.SI.ENS(montants ; régions ; "Est" ; années ; 2026) se lit presque comme une phrase.

Ce sont les fonctions en SI.ENS qui transforment un tableau en outil d’analyse : la même logique s’applique à SOMME.SI.ENS, NB.SI.ENS et MOYENNE.SI.ENS.

NB.SI.ENS accepte autant de couples plage/critère que nécessaire, et les combine par un ET logique : une ligne n’est comptée que si elle satisfait toutes les conditions. Avec un seul couple, elle remplace avantageusement NB.SI, dont il devient inutile de se souvenir.

Les critères ne se limitent pas à l’égalité. On écrit ">1000" pour une comparaison, "<>" pour exclure les vides, ou "Dupont*" pour tout ce qui commence par Dupont — l’astérisque remplace une suite de caractères, le point d’interrogation un caractère unique.

C’est cette fonction, et non le filtre manuel, qui permet de construire un tableau de synthèse qui se met à jour tout seul.

Trouver le maximum est immédiat. Trouver la deuxième ou la troisième plus grande valeur l’est beaucoup moins — et trier le tableau pour y parvenir revient à sacrifier son ordre d’origine.

GRANDE.VALEUR règle le problème : elle prend une plage et un rang, et renvoie la valeur de ce rang. GRANDE.VALEUR(plage ; 1) donne le maximum, GRANDE.VALEUR(plage ; 2) la suivante, et ainsi de suite. En plaçant les rangs 1, 2 et 3 dans une colonne, on obtient un classement qui se recalcule à chaque modification du tableau.

Reste à retrouver le nom associé à chaque valeur : c’est le rôle de RECHERCHEX, qui cherche la valeur classée dans la colonne des montants et renvoie le libellé correspondant. Deux fonctions, et le podium se met à jour tout seul.

La protection de feuille pose un dilemme connu. Sans mot de passe, elle se lève en deux clics et ne protège rien. Avec mot de passe, elle finit par bloquer la seule personne qui en avait besoin, le jour où le mot de passe est perdu — et la direction refuse souvent le principe même.

Une autre voie consiste à surveiller le classeur par le code, en deux temps. À chaque changement de sélection, on mémorise le nombre de colonnes occupées. À chaque modification, on le compare à celui d’avant : s’il a changé, une colonne a été ajoutée ou supprimée, et l’opération est annulée.

L’annulation passe par Application.Undo, exactement comme un Ctrl+Z. Le détail qui compte est l’encadrement par EnableEvents : sans lui, l’annulation serait elle-même vue comme une modification et relancerait l’événement en boucle.

L’utilisateur voit sa suppression se défaire d’elle-même, avec un message qui explique pourquoi. Aucun mot de passe n’est en jeu, et la feuille reste modifiable partout ailleurs.

Private Nb_col_initial      As Long

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
' 1) Pour enregistrer le nombre de colonnes
    Nb_col_initial = Sh.UsedRange.Columns.Count
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
' 2) Pour détecter des ajouts/suppressions de colonnes
    Dim Nb_col_actuel       As Long

    'Récupérer le nombre de colonnes actuel après un changement
    Nb_col_actuel = Sh.UsedRange.Columns.Count

    'Comparer le nombre de colonnes avant et après le changement
    If Nb_col_actuel <> Nb_col_initial Then

        'Si une colonne a été ajoutée ou supprimée => annuler l'action
        Application.EnableEvents = False
        Application.Undo 'Ctrl + Z
        MsgBox "L'ajout ou la suppression de colonnes est interdit dans cette zone.", _
               vbExclamation, "Ajout/suppression de colonne interdit !"
        Application.EnableEvents = True
    End If

End Sub

Dans un fichier partagé, les formules ne survivent jamais très longtemps : une saisie directe par-dessus, et le calcul est remplacé par une valeur figée. Le plus gênant est que rien ne le signale — le classeur continue d’afficher un nombre plausible.

La parade repose sur la même mécanique en deux temps, et sur une subtilité de calendrier. Une fois la saisie faite, il est trop tard pour savoir ce que la cellule contenait : la formule a déjà disparu. Le test doit donc avoir lieu *avant*, au moment où l’utilisateur sélectionne la cellule.

C’est le rôle du premier événement : il parcourt la sélection et mémorise, dans un booléen, la présence d’au moins une formule — c’est ce que teste la propriété HasFormula. Le second événement se contente alors de lire ce booléen et d’annuler la saisie si nécessaire.

Les zones de saisie restent libres, les zones calculées deviennent intouchables, et personne n’a de mot de passe à gérer.

Public booFormule_dans_cell     As Boolean

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
' 1) Pour détecter une plage de cellules contenant une FORMULE

    Dim n           As Long

    For n = 1 To Target.Cells.Count

        If Target.Cells(n).HasFormula Then
            booFormule_dans_cell = True
            Exit For
        Else
            booFormule_dans_cell = False
        End If

    Next n

End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
' 2) Pour annuler l'édition d'une cellule contenant une FORMULE
'    (formule détectée dans la SelectionChange ci-dessus)

    If booFormule_dans_cell = True Then

        Application.EnableEvents = False
        Application.Undo 'Ctrl + Z sur la dernière action
        Application.EnableEvents = True
        MsgBox "Cette zone contient une formule et ne doit pas être éditée.", _
               vbExclamation, "Ne pas modifier"

    End If

End Sub

C’est l’erreur la plus fréquente et la plus coûteuse sur un tableau de bord Excel : la source a été complétée, le tableau croisé affiche encore les valeurs de la semaine dernière, et personne ne s’en aperçoit avant la réunion.

Un événement de feuille règle la question, et le choix de l’événement compte autant que le code. Placé sur la feuille qui *porte* le tableau croisé, l’événement de changement de sélection se déclenche dès qu’on vient regarder le tableau — c’est-à-dire exactement au moment où il doit être à jour, et pas à chaque frappe dans la table source.

La méthode employée mérite elle aussi l’attention. PivotCache.Refresh relit les données de la source, ce qui est indispensable pour voir apparaître les lignes ajoutées. RefreshTable, souvent proposée à sa place, se contente de redessiner le tableau à partir du cache existant : les nouvelles lignes n’apparaîtraient pas.

Pour tout actualiser d’un coup — tableaux croisés, requêtes Power Query et connexions externes —, ThisWorkbook.RefreshAll fait le travail en une ligne, au prix d’un rafraîchissement complet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Pour actualiser un TCD en particulier
PivotTables("TCD des Primes").PivotCache.Refresh

End Sub

La commande se trouve dans l’onglet Données, juste à côté des outils d’import : À partir d’une image, au choix depuis un fichier ou depuis le presse-papiers. Excel analyse l’image, reconnaît la grille et propose les données extraites.

Un volet de vérification s’ouvre alors et signale les cellules dont il n’est pas certain. On corrige à la volée, on insère, et le tableau atterrit dans la feuille. Une photo de tableau prise en réunion devient exploitable en une minute.