Comment rechercher la première valeur non nulle et renvoyer l'en-tête de colonne correspondant 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 dans 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 représentent des périodes ou des catégories, vous pourriez vouloir savoir quand une valeur apparaît pour la première fois pour chaque ligne. Vérifier manuellement chaque ligne pour la première valeur non nulle peut être chronophage, surtout lorsque la taille des données augmente. Automatiser ce processus de recherche améliore non seulement l'efficacité mais réduit également les erreurs, rendant vos analyses plus fiables. Cet article explique plusieurs méthodes pratiques pour atteindre cet objectif, de l'utilisation de formules Excel polyvalentes à l'emploi 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 avec 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 avec une formule
Pour identifier efficacement l'en-tête de colonne dans une ligne donnée où apparaît la première valeur non nulle, vous pouvez utiliser une formule Excel intégrée. Cette approche convient particulièrement aux petits et moyens ensembles de données où le recalcul en temps réel et la simplicité de configuration sont importants.
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 confirmer. Ensuite, sélectionnez K2 et utilisez la poignée de recopie pour faire glisser la formule vers le bas afin de l'appliquer au reste des lignes si nécessaire.
Remarque : Dans la formule ci-dessus, B1:I1 fait référence à la plage d'en-têtes de colonnes que vous souhaitez renvoyer, et B2:I2 est la ligne de données que vous analysez pour la première valeur non nulle.
Si vos données commencent dans des colonnes ou des lignes différentes, n'oubliez pas d'ajuster les plages de formules en conséquence. De plus, cette formule fonctionne efficacement tant qu'il y a au moins une valeur non nulle dans chaque ligne analysée ; si toutes les valeurs sont nulles, la formule renverra une erreur. Dans ces cas, envisagez d'inclure la formule dans IFERROR comme ceci : =IFERROR(INDEX($B$1:$I$1,MATCH(TRUE,INDEX(B2:I2<>0,),0)),"Aucune valeur non nulle") pour renvoyer un message personnalisé au lieu d'une erreur.
Cette solution basée sur une formule est idéale lorsque vous voulez des résultats dynamiques et mis à jour instantanément lorsque vos données d'entrée changent. Cependant, pour des ensembles de données extrêmement volumineux, la vitesse de calcul peut être affectée, et vous pourriez envisager une approche VBA pour améliorer l'automatisation du flux de travail ou réduire les opérations 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 des ensembles de données volumineux, ou si vous souhaitez automatiser le processus pour gagner en efficacité, utiliser une macro VBA est une alternative pratique. Cette méthode est particulièrement avantageuse pour la génération de rapports périodiques ou lorsque vous manipulez des tableaux de données qui changent fréquemment de taille. 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 for 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 et 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 vous invitera à sélectionner la plage de données (à l'exclusion des en-têtes de colonne). Après l'exécution de la macro, la colonne immédiatement à droite des données sélectionnées sera remplie avec l'en-tête de la première valeur non nulle pour chaque ligne, ou un message "Aucune valeur non nulle" si aucune valeur non nulle n'est trouvée.
Cette approche VBA excelle pour les tâches répétitives et est excellente pour gérer de grands ensembles de données, réduisant ainsi les efforts manuels. Cependant, assurez-vous que les macros sont activées dans votre environnement Excel et sauvegardez toujours votre classeur avant d'exécuter du code.
Remarque : Si vous rencontrez des erreurs, vérifiez que votre sélection exclut la ligne d'en-tête et inclut uniquement les lignes de données.
Découvrez la magie d'Excel avec Kutools AI
- Exécution intelligente : Effectuez des opérations sur les cellules, analysez les données et créez des graphiques, le tout piloté par des commandes simples.
- Formules personnalisées : Générez des formules adaptées pour rationaliser vos flux de travail.
- Codage VBA : Écrivez et implémentez du code VBA sans effort.
- Interprétation des formules : Comprenez facilement des formules complexes.
- Traduction de texte : Surmontez les barrières linguistiques dans vos feuilles de calcul.
Meilleurs outils de productivité pour Office
Améliorez vos compétences Excel avec Kutools pour Excel, et découvrez une efficacité incomparable. Kutools pour Excel propose plus de300 fonctionnalités avancées pour booster votre productivité et gagner du temps. Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte l’interface par onglets à Office, simplifiant considérablement votre travail.
- Activez la modification 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é de50 % et réduit des centaines de clics de souris chaque jour !
Tous les modules complémentaires Kutools. Une seule installation
La suite Kutools for Office regroupe les modules complémentaires pour Excel, Word, Outlook & PowerPoint ainsi qu’Office Tab Pro, idéal pour les équipes travaillant sur plusieurs applications Office.
- Suite tout-en-un — modules complémentaires Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un installateur, une licence — installation en quelques minutes (compatible MSI)
- Une synergie optimale — productivité accélérée sur l’ensemble des applications Office
- Essai complet30 jours — sans inscription, ni carte bancaire
- Meilleure valeur — économisez par rapport à l’achat d’add-ins individuels