Comment calculer la moyenne de cellules non adjacentes en excluant les zéros dans Excel ?
Lorsque vous travaillez avec des données Excel, il arrive fréquemment que vous deviez calculer la moyenne d’un ensemble de nombres, sans que toutes les cellules pertinentes soient adjacentes, tout en souhaitant exclure Valeurs nulles (qui peuvent représenter des données manquantes ou des valeurs annulées). Par exemple, dans une feuille de ventes ou d’inventaire, certains articles peuvent avoir un stock nul ou aucune vente à certaines dates ; dans ces cas, un simple calcul de moyenne pourrait fausser l’analyse, car les zéros influencent le résultat. Leur exclusion permet d’analyser uniquement les points de données actifs.
Supposons que vous disposiez d’un tableau de fruits tel que celui illustré ci-dessous et que vous souhaitiez calculer la moyenne des valeurs de la colonne « Quantité », en excluant les zéros. Les méthodes suivantes vous guident pas à pas vers plusieurs approches pratiques pour réaliser cette opération sous Excel, vous offrant une grande flexibilité selon que vos données soient situées dans des cellules non adjacentes, réparties sur plusieurs lignes ou colonnes, ou que vous préfériez utiliser des fonctions intégrées d’Excel ou des formules simples.
- Moyenne de cellules non adjacentes en excluant les zéros à l’aide d’une formule
- Moyenne de cellules non adjacentes en excluant les zéros avec une macro VBA
- Moyenne de cellules non adjacentes en excluant les zéros à l’aide d’une fonction intégrée d’Excel
Moyenne de cellules non adjacentes en excluant les zéros à l’aide d’une formule
Cette méthode utilise une formule matricielle pour calculer rapidement la moyenne de cellules non adjacentes — par exemple, une cellule sur deux dans une ligne ou une colonne — tout en excluant les zéros parmi les cellules sélectionnées. Elle s’applique lorsque vos cellules cibles suivent un motif à intervalle fixe et se révèle particulièrement efficace pour traiter des données périodiques, comme des valeurs relevées tous les deux jours, ou toute série régulière mais non contiguë.
Limites : Cette approche convient surtout lorsque les cellules non adjacentes sont disposées à intervalles réguliers. Pour des sélections arbitraires effectuées manuellement, consultez la méthode basée sur une formule ci-dessous.
1. Sélectionnez une cellule vide dans laquelle vous souhaitez afficher le résultat, puis saisissez la formule matricielle suivante :
=AVERAGE(IF(MOD(COLUMN(C2:G2)-COLUMN(C2),2)=0,IF(C2:G2,C2:G2)))Appuyez ensuite simultanément sur les touches Ctrl + Maj + Entrée(pour les anciennes versions d’Excel ; sous Excel 365 ou 2019, il suffit d’appuyer sur)Entrée, car ces versions prennent en charge les tableaux dynamiques).
Remarque : Dans la formule, C2 et G2 représentent respectivement la première et la dernière cellule de votre plage cible. Le chiffre « 2 » indique l’intervalle entre les colonnes ; modifiez cette valeur si vos cellules non adjacentes sont espacées par un nombre différent de colonnes. Ajustez à la fois la plage (C2:G2) et l’intervalle (2) en fonction de la disposition de vos données.
Conseils:
- Assurez-vous que la plage sélectionnée inclut toutes les cellules non adjacentes que vous souhaitez prendre en compte pour la moyenne.
- Si votre sélection de données ne repose pas sur des intervalles réguliers, optez pour la solution suivante, fondée sur une formule applicable à des cellules arbitraires.
- Cette formule ignore automatiquement les cellules vides et les valeurs nulles, vous offrant ainsi la moyenne réelle des points de données significatifs.
- Pour éviter toute erreur, assurez-vous que vos références de cellules sont exactes et que vous avez bien utilisé la combinaison de touches requise pour les formules matricielles dans les anciennes versions d’Excel.
Moyenne de cellules non adjacentes en excluant les zéros avec une macro VBA
Si vous devez rapidement calculer la moyenne d’un groupe de cellules non adjacentes tout en omettant les zéros, et que votre sélection ne suit pas un motif fixe (par exemple, vous souhaitez sélectionner manuellement un ensemble de cellules spécifiques réparties sur différentes lignes et colonnes), une macro VBA constitue une solution flexible et puissante. Ce code parcourra les cellules sélectionnées et calculera la moyenne en excluant toute cellule contenant un zéro.
Cette méthode est idéale pour :
- Traitement rapide de sélections volumineuses ou irrégulières.
- Enregistrez votre logique personnalisée pour la réutiliser facilement sur des jeux de données similaires.
- Contournez les limites des formules classiques de feuille de calcul pour réaliser des sélections complexes.
1. Cliquez sur Développeur > Visual Basic ; dans la fenêtre qui s’ouvre, cliquez sur Insertion > Module, puis collez le code suivant dans le module :
Sub AverageNonAdjacentExcludeZero()
Dim rng As Range
Dim cell As Range
Dim SumVal As Double
Dim CountVal As Long
Dim SelectedRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set SelectedRng = Application.Selection
Set SelectedRng = Application.InputBox("Select non-adjacent cells to average (exclude zeros)", xTitleId, Type:=8)
On Error Resume Next
SumVal = 0
CountVal = 0
For Each cell In SelectedRng
If IsNumeric(cell.Value) And cell.Value <> 0 Then
SumVal = SumVal + cell.Value
CountVal = CountVal + 1
End If
Next
If CountVal > 0 Then
MsgBox "Average (excluding zeros): " & SumVal / CountVal, vbInformation, xTitleId
Else
MsgBox "No non-zero numeric cells selected.", vbExclamation, xTitleId
End If
End Sub2. Cliquez sur le bouton 
Exécuter, ou appuyez sur F5dans l’éditeur VBA. Une boîte de dialogue s’ouvre alors pour vous demander de sélectionner vos cellules. À l’aide de votre souris, maintenez la touche Ctrlenfoncée tout en cliquant pour sélectionner les cellules non adjacentes dont vous souhaitez calculer la moyenne. Cliquez ensuite sur OKet la macro affichera instantanément le résultat en excluant les zéros.
Moyenne de cellules non adjacentes en excluant les zéros à l’aide d’une fonction intégrée d’Excel
Excel propose également des méthodes intégrées pour accomplir cette tâche avec peu, voire aucune formule — idéales pour des calculs ponctuels rapides ou des inspections visuelles.
Utilisez la Barre d'état :
- Maintenez la touche Ctrl enfoncée et utilisez votre souris pour sélectionner manuellement chaque cellule non adjacente que vous souhaitez inclure dans la moyenne, en ignorant celles contenant des valeurs nulles.
- Une fois la sélection effectuée, consultez la barre d’état Excel située en bas à droite de votre fenêtre : la valeur Moyenne s’affiche automatiquement dès que plusieurs cellules sont sélectionnées.
- Cette méthode est idéale pour des vérifications visuelles rapides et de petits jeux de données, mais elle n’affiche pas le résultat directement dans une cellule de la feuille de calcul.
Pour choisir la méthode la plus adaptée, prenez en compte la taille de vos données, le niveau de dispersion de vos cellules sélectionnées, ainsi que la nécessité d’obtenir une formule réutilisable ou simplement une vérification visuelle rapide. Si vous rencontrez des erreurs (telles que)#DIV/0!), assurez-vous qu’au moins une valeur non nulle est présente dans votre sélection. Lorsque vous utilisez du VBA, sauvegardez toujours votre classeur avant d’exécuter de nouvelles macros afin d’éviter toute perte accidentelle de données.
Sélection rapide de lignes ou colonnes à intervalles (non adjacentes) dans Plage limitée sous Excel :
La fonctionnalité Kutools pour Excel « Sélectionner des lignes/colonnes à intervalles réguliers » vous permet de sélectionner facilement des lignes ou des colonnes à intervalles réguliers dans une plage définie sous Excel, comme illustré dans la capture d’écran ci-dessous. Idéal avant d’appliquer des formules ou des macros, cet outil vous aide à cibler précisément les cellules à traiter.
Kutools pour Excel– Boostez Excel avec plus de 300 outils essentiels, pour gagner en rapidité et en simplicité, et profitez des fonctionnalités d’intelligence artificielle pour un traitement de données plus intelligent et une productivité accrue.Obtenez-le dès maintenant
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