Comment classer facilement des nombres tout en ignorant les cellules vides dans Excel ?

Lorsque vous travaillez avec des données dans Excel, il est courant de rencontrer des listes contenant des cellules vides. Si vous utilisez les fonctions standard de classement d’Excel, telles que RANK ou RANK.EQ, sur de telles listes, les cellules vides génèrent généralement des erreurs ou des résultats inattendus dans le classement. Cela complique l’interprétation de vos données, surtout si vous souhaitez laisser ces cellules vides sans afficher d’erreurs ni de rangs arbitraires. Classer efficacement les nombres tout en conservant automatiquement les cellules vides intactes améliore la clarté et la convivialité de vos résultats, pour une feuille de calcul plus professionnelle et plus facile à lire.

Dans cet article, découvrez des instructions étape par étape pour accomplir cette tâche à l’aide de formules et de macros VBA. Les solutions détaillées incluent également des explications des paramètres, des conseils pratiques et des suggestions de dépannage afin de vous aider à éviter les pièges courants.

• Classer des valeurs en ignorant les cellules vides par ordre croissant à l’aide de formules
• Classer des valeurs en ignorant les cellules vides par ordre décroissant à l’aide d’une formule
• Classer des valeurs en ignorant les cellules vides à l’aide de VBA

Classer des valeurs en ignorant les cellules vides par ordre croissant à l’aide de formules

Dans les cas où vous devez attribuer des classements par ordre croissant tout en ignorant les cellules vides, une approche courante consiste à utiliser plusieurs colonnes auxiliaires et une logique de formule pour s’assurer que les valeurs vides sont ignorées dans le classement.

Scénario d’application : Utilisez cette méthode lorsque vous souhaitez générer des classements incrémentiels (du plus petit au plus grand) tout en conservant la position des cellules vides, notamment dans des plages continues où les entrées manquantes ne doivent pas perturber la numérotation des rangs.

Pour effectuer un classement croissant en ignorant les cellules vides, suivez ces étapes, qui impliquent deux colonnes auxiliaires pour construire le résultat :

1. Sélectionnez une cellule vide adjacente à vos valeurs – par exemple, la cellule B2 si votre liste commence en A2 – et saisissez la formule suivante :

=IF(ISBLANK($A2),"",VALUE($A2&"."&(ROW()-ROW($B$2))))

La formule renvoie une cellule vide si A2 est vide ; sinon, elle génère des nombres décimaux en ajoutant .0, .1, .2, .3, etc., à la valeur de A2, au fur et à mesure que vous faites glisser la poignée de recopie vers le bas pour étendre la formule à toutes les lignes contenant des données.

Explications des paramètres et conseils :

• $A2 : La première cellule à trier. Ajustez-la si votre liste commence sur une autre ligne.
• $B$2 : la cellule dans laquelle vous saisissez cette formule. Veillez à utiliser des références absolues (par exemple, $A2 et $B$2) pour que la formule fonctionne correctement lorsqu’elle est copiée vers le bas.

2. Dans la colonne suivante, par exemple C2, saisissez cette formule pour générer une liste triée de valeurs auxiliaires :

=SMALL($B$2:$B$8,ROW()-ROW($C$1))

Cette formule extrait séquentiellement les plus petites valeurs, puis les suivantes, et ainsi de suite à partir de B2:B8 (remarque : modifiez la plage si vos données s’étendent davantage) au fur et à mesure que vous recopiez la formule vers le bas.

Explications des paramètres :

• $B$2:$B$8 : la plage dans laquelle la première formule auxiliaire est utilisée.
• $C$1 : cellule située juste au-dessus de celle dans laquelle vous saisissez la formule ; ce décalage détermine l’ordre du classement.

3. Dans la cellule D2, saisissez la formule suivante pour attribuer des rangs tout en laissant les cellules vides intactes :

=IFERROR(MATCH($B2,$C$2:$C$8,0),"")

Cette formule recherche la valeur située en B2 parmi les résultats triés en C2:C8. Si elle trouve une correspondance, elle affiche le rang ; sinon (par exemple, pour les cellules vides), elle n’affiche rien, préservant ainsi l’apparence vide des cellules concernées. Faites glisser la poignée de recopie vers le bas pour appliquer la formule à toutes les lignes concernées.

Paramètres :

• $B2 : La cellule contenant la valeur auxiliaire utilisée pour le classement.
• $C$2:$C$8 : la plage des valeurs auxiliaires triées.

Précautions : Si vous ajoutez ou supprimez des données, pensez à mettre à jour toutes les plages utilisées dans vos formules afin qu’elles correspondent à la nouvelle taille de vos données. Pour les listes très longues, privilégiez les plages dynamiques ou les tableaux Excel afin de minimiser les ajustements manuels.

Dépannage : Si des rangs sont manquants ou mal alignés, vérifiez que toutes les plages utilisées dans les formules auxiliaires sont correctement alignées. Un mauvais alignement entre les colonnes entraîne des classements incorrects ou des erreurs involontaires.

Classer des valeurs en ignorant les cellules vides par ordre décroissant à l’aide d’une formule

Lorsque vous souhaitez attribuer des classements en ordre décroissant — la valeur la plus élevée se voyant attribuer le rang 1 —, une méthode plus rapide consiste à utiliser une seule formule. Celle-ci s’avère particulièrement utile pour les notes d’examen, les objectifs de vente et autres jeux de données similaires, où les cellules vides correspondent à des informations manquantes ou indisponibles, et où vous ne souhaitez ni leur attribuer un rang ni afficher d’erreurs.

Sélectionnez une cellule sur la même ligne que la première entrée de données, là où vous souhaitez que le résultat s’affiche, et saisissez :

=IF(ISNA(RANK(A2,A$2:A$8)),"",RANK(A2,A$2:A$8))

Après avoir saisi la formule, utilisez la poignée de recopie pour la copier vers le bas à côté de vos données. Cette formule vérifie si la fonction RANKrenvoie une erreur (par exemple, lorsque)A2 est vide) ; si c’est le cas, elle laisse le résultat vide au lieu d’afficher « #N/A ». Si la cellule contient une valeur valide, elle affiche le rang approprié.

Paramètres :

• A2 : La cellule à trier (ajustez-la en fonction de votre plage de données).
• A$2:A$8 : la plage complète de vos données (utilisez une référence absolue pour la recopier).

Rappels en cas d’erreur : Si vous voyez toujours des erreurs « #N/A », assurez-vous que les références de votre formule correspondent bien à la plage de données prévue et qu’aucune cellule triée ne contient de valeurs non numériques.

Classer des valeurs en ignorant les cellules vides à l’aide de VBA

Pour les utilisateurs à l’aise avec les macros et souhaitant automatiser le tri sur une plage contenant des cellules vides – qu’il s’agisse d’un ordre croissant ou décroissant – une macro VBA personnalisée simplifie grandement le processus en éliminant le recours à plusieurs colonnes auxiliaires ainsi que la maintenance continue des formules.

Mode d’emploi :

1. Accédez à l’onglet Développeur et cliquez sur Visual Basic pour ouvrir l’éditeur Microsoft Visual Basic pour Applications. Si l’onglet Développeur n’est pas visible, consultez ce guide : Afficher l’onglet Développeur dans Excel.

2. Dans la nouvelle fenêtre Microsoft Visual Basic pour Applications, cliquez sur Insertion > Module, puis collez l’un des codes suivants dans la fenêtre du module :

• Pour effectuer un classement croissant en ignorant les cellules vides :

  Sub RankSkipBlank_Ascending()
      Dim WorkRng As Range
      Dim Cell As Range
      Dim NumArr() As Double
      Dim Ws As Worksheet
      Dim OutputCell As Range
      Dim i As Long, j As Long
      
      On Error Resume Next
      xTitleId = "KutoolsforExcel"
      
      Set WorkRng = Application.Selection
      Set WorkRng = Application.InputBox("Please select the range to rank", xTitleId, WorkRng.Address, Type:=8)
      Set Ws = WorkRng.Worksheet
      
      Set OutputCell = Application.InputBox("Please select the first cell to output the ascending ranking", xTitleId, Type:=8)
      If OutputCell Is Nothing Then Exit Sub
      
      j = 0
      ReDim NumArr(1 To WorkRng.Rows.Count)
      
      For Each Cell In WorkRng
          If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then
              j = j + 1
              NumArr(j) = Cell.Value
          End If
      Next Cell
      
      Dim temp As Double
      Dim k As Long
      
      For i = 1 To j - 1
          For k = i + 1 To j
              If NumArr(i) > NumArr(k) Then    ' ← CHANGE HERE
                  temp = NumArr(i)
                  NumArr(i) = NumArr(k)
                  NumArr(k) = temp
              End If
          Next k
      Next i
      
      Dim RankArr() As Double
      ReDim RankArr(1 To j)
      For i = 1 To j
          RankArr(i) = NumArr(i)
      Next i
      
      Dim RankValue As Long
      Dim r As Long: r = 0
      
      For Each Cell In WorkRng
          r = r + 1
          If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then
              RankValue = 0
              For k = 1 To j
                  If Cell.Value = RankArr(k) Then
                      RankValue = k        ' 1 = smallest
                      Exit For
                  End If
              Next k
              OutputCell.Offset(r - 1, 0).Value = RankValue
          Else
              OutputCell.Offset(r - 1, 0).Value = ""
          End If
      Next Cell
  
  End Sub

• Pour effectuer un classement décroissant en ignorant les cellules vides :

  Sub RankSkipBlank_Descending()
      Dim WorkRng As Range
      Dim Cell As Range
      Dim NumArr() As Double
      Dim Ws As Worksheet
      Dim OutputCell As Range
      Dim i As Long, j As Long
      
      On Error Resume Next
      xTitleId = "KutoolsforExcel"
      
      Set WorkRng = Application.Selection
      Set WorkRng = Application.InputBox("Please select the range to rank", xTitleId, WorkRng.Address, Type:=8)
      Set Ws = WorkRng.Worksheet
      
      Set OutputCell = Application.InputBox("Please select the first cell to output the descending ranking", xTitleId, Type:=8)
      If OutputCell Is Nothing Then Exit Sub
      
      j = 0
      ReDim NumArr(1 To WorkRng.Rows.Count)
      
      For Each Cell In WorkRng
          If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then
              j = j + 1
              NumArr(j) = Cell.Value
          End If
      Next Cell
      
      Dim temp As Double
      Dim k As Long
      
      For i = 1 To j - 1
          For k = i + 1 To j
              If NumArr(i) < NumArr(k) Then
                  temp = NumArr(i)
                  NumArr(i) = NumArr(k)
                  NumArr(k) = temp
              End If
          Next k
      Next i
      
      Dim RankArr() As Double
      ReDim RankArr(1 To j)
      For i = 1 To j
          RankArr(i) = NumArr(i)
      Next i
      
      Dim RankValue As Long
      Dim r As Long: r = 0
  
      For Each Cell In WorkRng
          r = r + 1
          If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then
              RankValue = 0
              For k = 1 To j
                  If Cell.Value = RankArr(k) Then
                      RankValue = k
                      Exit For
                  End If
              Next k
              OutputCell.Offset(r - 1, 0).Value = RankValue
          Else
              OutputCell.Offset(r - 1, 0).Value = ""
          End If
      Next Cell
  
  End Sub

3. Appuyez sur F5 pour exécuter la macro. Une boîte de dialogue s’ouvrira pour vous demander de sélectionner la plage que vous souhaitez classer. Ensuite, une autre boîte de dialogue vous invitera à choisir la première cellule où les résultats du classement doivent être placés. La macro affichera alors les rangs à partir de la cellule sélectionnée, et toute cellule vide dans la plage source restera vide.

Conseils :

• Si rien ne se produit, assurez-vous que les macros sont activées et que vous disposez des autorisations requises pour exécuter du code dans votre classeur.
• L’exécution d’une macro VBA est irréversible. Pensez à copier ou sauvegarder vos données avant de lancer la macro.

Meilleurs outils de productivité Office

🤖	Kutools IA Aide: Révolutionnez Analyse des données grâce à :Exécution intelligente   |  Générez du code|  Créez formules personnalisées  |  Analysez des données et générez des graphiques|  Appelez Fonctions améliorées…
	Fonctionnalités populaires:Rechercher, mettre en surbrillance ou Marquer les doublons   |  Supprimer les lignes vides   |  Combinez les colonnes ou cellules sans perdre de données   |   Arrondi sans utiliser de formule...
	Super RECHERCHEV:RechercheValeurs avec critères multiples  |  RechercheValeurs avec valeurs multiples  |   RechercheValeurs dans plusieurs feuilles   |   Correspondance floue....
	Liste déroulante avancée:Créez rapidement une liste déroulante   |  Liste déroulante dépendante   |  Liste déroulante à sélection multiple....
	Gestionnaire de colonnes:Ajouter un nombre précis de colonnes|Déplacer des colonnes|Basculer la visibilité des colonnes masquées|Comparer des plages et des colonnes...
	Fonctionnalités vedettes:Mise au point de la grille   |  Vue de conception   |Barre de formule améliorée   | Gestionnaire de classeurs et de feuilles   |  Bibliothèque de ressources(Texte automatique)|  Sélecteur de date   |  Consolider les feuilles de calcul  |  Chiffrer/Déchiffrer les cellules   | Envoyer des e-mails par liste   |  Super Filtre   |   Filtre spécial(Filtrer les cellules avec une police en gras/italique/barré...) ...
	… et bien plus encore… et plus encore:(,)Supprimer des caractères spécifiques, ...)|   50+Typesde graphiques(, ...)|   40+ Formules pratiques(Calculer l'âge en fonction de la date de naissance, ...)|   19 Outils d’insertion(,Insérer une image depuis un chemin , ...)|   de conversion (Convertir en mots,Conversion de devises, ...)|Fusionner et scinder   (Fusion avancée des lignes,Diviser les cellules , ...)|, ...)   |

Utilisez Kutools dans la langue de votre choix – disponible en anglais, espagnol, allemand, français, chinois et 40+ autres langues !

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...

En savoir plus sur Kutools pour Excel...Téléchargement gratuit...

---

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 !

En savoir plus sur Office Tab...Téléchargement gratuit...

---

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

En savoir plus sur Kutools for Office...Téléchargement gratuit…