Comment rechercher la première valeur non nulle et renvoyer l’en-tête de la colonne correspondante dans Excel ?
Lorsque vous travaillez avec des données dans Excel, il est courant de devoir identifier la position de la première entrée non nulle au sein d’une ligne et d’afficher l’en-tête de colonne associé. Par exemple, dans un jeu de données où chaque ligne représente un élément ou une personne différente et les colonnes des périodes ou catégories, vous souhaiterez peut-être savoir à quel moment une valeur apparaît pour la première fois dans chaque ligne. Vérifier manuellement chaque ligne à la recherche de la première valeur non nulle peut s’avérer chronophage, surtout à mesure que la taille des données augmente. Automatiser ce processus de recherche améliore non seulement l’efficacité, mais réduit également les erreurs, rendant ainsi vos analyses plus fiables. Cet article présente plusieurs méthodes pratiques pour atteindre cet objectif, allant de l’utilisation de formules Excel polyvalentes à celle de macros VBA particulièrement utiles pour les grands jeux de données ou les rapports récurrents.
- Rechercher la première valeur non nulle et renvoyer l’en-tête de colonne correspondant à l’aide d’une formule
- Utiliser une macro VBA pour trouver et renvoyer l’en-tête de colonne de la première valeur non nulle dans chaque ligne
Rechercher la première valeur non nulle et renvoyer l’en-tête de colonne correspondant à l’aide d’une formule
Pour identifier efficacement l’en-tête de colonne correspondant à la première valeur non nulle dans une ligne donnée, vous pouvez utiliser une formule intégrée d’Excel — une solution idéale pour les jeux de données de petite à moyenne taille, où simplicité de configuration et recalcul en temps réel sont essentiels.
1. Sélectionnez une cellule vide pour afficher le résultat ; dans cet exemple, la cellule K2 est utilisée.
=INDEX($B$1:$I$1,MATCH(TRUE,INDEX(B2:I2<>0,),0))2. Après avoir saisi la formule, appuyez sur Entrée pour la valider. Ensuite, sélectionnez K2 et utilisez la poignée de recopie pour faire glisser la formule vers le bas afin de l’appliquer aux autres lignes selon vos besoins.
Remarque : Dans la formule ci-dessus, B1:I1 fait référence à la plage des en-têtes de colonne que vous souhaitez renvoyer, et B2:I2 correspond aux données de la ligne que vous analysez pour trouver la première valeur non nulle.
Si vos données commencent dans des colonnes ou des lignes différentes, pensez à ajuster les plages de la formule en conséquence. Par ailleurs, cette formule fonctionne efficacement à condition qu’il y ait au moins une valeur non nulle dans chaque ligne analysée ; si toutes les valeurs sont nulles, la formule renverra une erreur. Dans ce cas, envisagez d’encapsuler la formule dans SIERREUR comme suit : =IFERROR(INDEX($B$1:$I$1,MATCH(TRUE,INDEX(B2:I2<>0,),0)),"No non-zero") afin de renvoyer un message personnalisé au lieu d’une erreur.
Cette solution fondée sur les formules est idéale pour obtenir des résultats dynamiques qui se mettent à jour instantanément dès que vos données d’entrée changent. Toutefois, avec des jeux de données extrêmement volumineux, les performances de calcul peuvent être ralenties ; dans ce cas, une approche VBA s’avère souvent préférable pour optimiser l’automatisation de votre flux de travail et réduire les interventions manuelles.
Utiliser une macro VBA pour trouver et renvoyer l’en-tête de colonne de la première valeur non nulle dans chaque ligne
Si vous devez souvent effectuer cette tâche de recherche sur de nombreuses lignes ou sur de grands jeux de données, ou si vous souhaitez automatiser le processus pour gagner en efficacité, l’utilisation d’une macro VBA constitue une alternative pratique. Cette méthode est particulièrement avantageuse pour la génération périodique de rapports ou lorsque vous gérez des tableaux de données dont la taille évolue fréquemment. La macro recherchera, dans chaque ligne spécifiée, la première valeur non nulle et renverra l’en-tête de colonne correspondant dans une cellule cible.
1. Cliquez sur l’onglet Développeur > Visual Basic pour ouvrir la fenêtre Microsoft Visual Basic pour Applications. (Si l’onglet Développeur n’est pas visible, vous pouvez l’ajouter via)Fichier > Options > Personnaliser le ruban.) Dans l’éditeur VBA, cliquez sur Insertion > Module.
2.Copiez-collez le code VBA suivant dans le nouveau module :
Sub LookupFirstNonZeroAndReturnHeader()
Dim ws As Worksheet
Dim dataRange As Range
Dim headerRange As Range
Dim outputCell As Range
Dim r As Range
Dim c As Range
Dim firstNonZeroCol As Integer
Dim i As Long
Dim xTitleId As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = Application.ActiveSheet
Set dataRange = Application.InputBox("Select the data range (excluding headers):", xTitleId, Selection.Address, Type:=8)
If dataRange Is Nothing Then Exit Sub
Set headerRange = ws.Range(dataRange.Offset(-1, 0).Resize(1, dataRange.Columns.Count).Address)
For i = 1 To dataRange.Rows.Count
Set r = dataRange.Rows(i)
firstNonZeroCol = 0
For Each c In r.Columns
If c.Value <> 0 And c.Value <> "" Then
firstNonZeroCol = c.Column - dataRange.Columns(1).Column + 1
Exit For
End If
Next c
Set outputCell = r.Cells(1, r.Columns.Count + 1)
If firstNonZeroCol > 0 Then
outputCell.Value = headerRange.Cells(1, firstNonZeroCol).Value
Else
outputCell.Value = "No non-zero"
End If
Next i
On Error GoTo 0
MsgBox "Completed! Results are in the column to the right of your data.", vbInformation, "KutoolsforExcel"
End Sub
3. Pour exécuter la macro, cliquez sur le bouton Exécuter ou appuyez sur la touche F5. Une boîte de dialogue s’ouvrira pour vous demander de sélectionner la plage de données (en excluant les en-têtes de colonne). Une fois la macro exécutée, la colonne immédiatement à droite de la plage sélectionnée sera remplie par l’en-tête correspondant à la première valeur non nulle de chaque ligne, ou par le message « Aucune valeur non nulle » si aucune valeur non nulle n’est trouvée.
Cette approche VBA se distingue par son efficacité dans les tâches répétitives et s’avère particulièrement adaptée à la gestion de grands jeux de données, réduisant considérablement les efforts manuels. Veillez toutefois à ce que les macros soient activées dans votre environnement Excel et n’oubliez jamais de sauvegarder votre classeur avant d’exécuter du code.
Remarque : en cas d’erreurs, assurez-vous que votre sélection exclut la ligne d’en-têtes et inclut uniquement les lignes de données.
Libérez la magie d’Excel avec Kutools IA
- Exécution intelligente : effectuez des opérations sur les cellules, analysez des données et créez des graphiques, le tout à partir de commandes simples.
- formules personnalisées : créez des formules sur mesure pour rationaliser vos flux de travail.
- Programmation VBA : Écrivez et implémentez du code VBA en toute simplicité.
- Interprétation de formules : Décryptez facilement les formules complexes.
- Traduction de texte : Franchissez les barrières linguistiques directement depuis vos feuilles de calcul.
Meilleurs outils de productivité Office
Boostez vos compétences Excel avec Kutools pour Excel et découvrez une efficacité inégalée.Kutools pour Excel propose plus de 300 fonctionnalités avancées pour améliorer votre productivité et Gagner du temps.Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte une interface à onglets à Office et rend votre travail bien plus facile
- Activez l’édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de 50 % et vous fait économiser des centaines de clics de souris chaque jour !
Tous les compléments Kutools. Un seul installateur
Kutools for Office regroupe les compléments pour Excel, Word, Outlook et PowerPoint, ainsi que Office Tab Pro, ce qui en fait le choix idéal pour les équipes travaillant à travers les applications Office.
- Suite tout-en-un— Compléments Excel, Word, Outlook et PowerPoint + Office Tab Pro
- Un seul installateur, une seule licence— installation en quelques minutes (compatible MSI)
- Fonctionne mieux ensemble— productivité optimisée dans toutes les applications Office
- Essai gratuit de 30 jours avec toutes les fonctionnalités— aucune inscription, aucune carte bancaire
- Meilleur rapport qualité-prix— économisez par rapport à l’achat de compléments individuels