Comment obtenir Excel 2007 et 2010 pour ignorer les cellules vides dans un graphique ou un graphique

Robbie écrit principalement sur Skyrim, mais met également occasionnellement en lumière les chances des applications Microsoft telles qu’Excel et Outlook.

Comment configurer Excel 2007 et Excel 2010 pour ignorer les cellules vides lors de la création d’un graphique ou d’un graphique

C’est un problème que j’ai moi-même eu, où je voulais créer un graphique lorsque la plupart des cellules de la plage que vous souhaitez représenter sont vides (contient des cellules vides).

Afin de résoudre ce problème et de créer un graphique qui affiche correctement les données, vous pouvez être tenté comme je l’ai fait par le passé de copier les valeurs des données dans une nouvelle plage afin que les données soient contiguës (sans lacunes). Cela peut être extrêmement long et très ennuyeux, surtout si la quantité de données est importante. Excel est censé faciliter notre vie collective, j’ai donc décidé de chercher une solution à ce problème qui impliquait le moins de travail manuel supplémentaire possible.

Dans mon cas, je souhaitais tracer les moyennes hebdomadaires du nombre de ventes quotidiennes réalisées par mon équipe. Lorsque j’ai créé un graphique en sélectionnant la plage de données, tout ce que j’ai obtenu était un graphique sans données.

Nous examinerons deux méthodes pour résoudre ce problème :

1. Comment changer la façon dont le graphique traite les cellules vides ou vides
2. Comment utiliser une formule pour changer le contenu des cellules vides en #N/A (Excel ignore automatiquement les cellules avec #N/A lorsque vous créez un graphique)

Dans la figure ci-dessous, vous pouvez voir :

• Les données que j’utilise pour créer les graphiques (à gauche)
• Le tableau initial vierge (en haut à droite)
• Le tableau complété (en bas à droite)

Méthode 1 : configurer la manière dont Excel traite les cellules masquées et vides

La première méthode que nous allons utiliser pour créer un graphique qui affiche correctement les données consiste à modifier la façon dont Excel 2007 et Excel 2010 traitent les cellules vides dans les graphiques.

Par défaut, dans un graphique, lorsqu’Excel voit une cellule vide, il crée un espace. Lorsque vous avez une plage de données avec plus de cellules vides que de cellules avec des données, cela signifie qu’Excel crée effectivement un graphique plein de lacunes.

Pour commencer, nous devons créer un graphique des ventes hebdomadaires moyennes.

• Nous créons un graphique linéaire en sélectionnant les données dans B3:B31 et D3:D31 (cliquez et maintenez le Ctrl touche pour sélectionner des colonnes qui ne sont pas adjacentes)
• Clique le Insérer onglet et sélectionnez l’onglet ligne bouton dans le graphiques groupe et sélectionnez ligne

Maintenant que nous avons créé le graphique, nous devons changer la façon dont Excel traite les cellules vides. Pour faire ça:

Faites défiler pour continuer

• clic-droit le tableau et choisissez Sélectionnez des dates
• Cliquez sur Cellules cachées et vides

• lacunes est le paramètre par défaut comme nous en avons discuté précédemment
• Zéro dans notre exemple, les données seront représentées graphiquement comme évaluerzéro, zéro, zéro, zéro, zéro, zéro, évaluer
• Connecter les points de données avec la ligne fait exactement ce qu’il dit, relie les points de données ensemble

La figure ci-dessous montre notre graphique créé à l’aide de Zéro (à gauche) et Connecter les points de données avec la ligne (droit). lacunes nous avons déjà vu avec le tableau vierge ci-dessus.

• J’ai aussi chronométré l’axe horizontal en clic-droit et en sélectionnant Formater l’axe
• sous le Options d’axe onglet, j’ai changé l’onglet Unité principale à Fixe 7 jours
• j’ai changé le Positionner l’axe à Sur les graduations de sorte qu’il a montré la date complète

Méthode 2 : Utiliser une formule pour convertir les cellules vides en #N/A

La deuxième méthode pour s’assurer que les données des graphiques Excel contenant un grand nombre d’espaces ou de cellules vides correctement consiste à utiliser une formule. Pour ce faire, nous utiliserons le fait que lorsqu’Excel voit #N/A dans une cellule, il ne l’inclut pas dans un graphique. La formule que nous utiliserons en E3 est :

=SI(D3= » », #N/A,D3)

la SI la fonction examine le contenu de la cellule D3 et SI D3 est égal à «  » (c’est-à-dire une cellule vide) puis il change la cellule actuelle en #N/A. Si Excel trouve quelque chose dans la cellule, il copiera simplement la cellule. Par exemple, il copiera le contenu de D3 vers E3.

J’ai un article qui parle de SI fonction plus en détail. Je couvre également l’utilisation IFERRORO pour supprimer les erreurs attendues et utiliser SI avec les fonctions logiques ET, OU et NE PAS. Il peut être trouvé ici:

• Ensuite, je copie la formule dans la colonne E.

• Je crée ensuite un graphique exactement comme nous l’avons fait ci-dessus en utilisant les dates de la colonne B et les résultats des formules de la colonne E

Comme vous pouvez le voir sur la figure ci-dessus, le graphique affiche correctement les données, même si Excel utilise Afficher les cellules vides cendre lacunes (le paramètre par défaut pour tous les graphiques créés dans Excel 2007 et Excel 2010).

Nous rangerions ensuite le graphique comme nous l’avons fait ci-dessus,

• Ranger l’axe horizontal
• Suppression de la Légende
• Ajout d’un Titre (clique le Disposition onglet avec le graphique sélectionné, choisissez l’onglet Titre du graphique et sélectionnez le type de titre que vous préférez)

Emballer

Souvent, les données vous parviennent à partir de feuilles de calcul sur lesquelles vous n’avez aucun contrôle et qui ne sont pas toujours formatées exactement comme vous le souhaitez. Plutôt que de rééditer les données pour les adapter à ce que vous voulez qu’elles fassent, il est toujours préférable et plus rapide d’avoir Excel pour résoudre tous les problèmes de formatage que les données peuvent avoir et produire les résultats que vous souhaitez sans modification manuelle importante de votre part.

Dans cet article, nous avons examiné un problème que je rencontre souvent :

Comment créer des graphiques à l’aide de données contenant un grand nombre de cellules vides. Nous avons examiné deux méthodes pour résoudre ce problème :

• Changer la façon dont Excel traite les cellules vides dans le graphique lui-même
• utilisant SI instructions pour remplacer les cellules vides par #N/A (Excel 2007 et Excel 2010 ignorent les cellules contenant #N/A lors de la création de graphiques)

utilisant SI les instructions dans une formule sont plus flexibles que de modifier la façon dont Excel traite les cellules vides dans un graphique, car vous pouvez lui demander de changer n’importe quoi en #N/A, pas seulement les cellules vides. Cela signifie que si vous recevez des feuilles de calcul contenant des fichiers inutiles ou des données dont vous ne voulez pas, vous pouvez également demander à Excel de les ignorer afin de créer un graphique à partir des données.

J’espère que vous avez trouvé cet article utile et informatif. N’hésitez pas à laisser vos commentaires ci-dessous et merci beaucoup d’avoir lu !

Cet article est exact et fidèle au meilleur de la connaissance de l’auteur. Le contenu est uniquement à des fins d’information ou de divertissement et ne remplace pas un conseil personnel ou un conseil professionnel en matière commerciale, financière, juridique ou technique.

© 2013 Robbie C Wilson

Mitja le 14 janvier 2019 :

Salut. Cela fonctionne, mais mon problème est qu’après tout cela, mon graphique affiche NA() dans la légende du graphique, ce qui est vraiment ennuyeux. Comment l’enlever?? Cela n’arrive qu’à mon travail où nous avons un bureau en 2013. À la maison où j’utilise 2019, cela fonctionne comme il se doit.

Farzad au 30 septembre 2018 :

Merci, c’était simple

Srikanth le 22 janvier 2018 :

le #N/A est vraiment utile, le bouton afficher caché dans les données sélectionnées n’est pas assez utile

femme le 02 novembre 2017 :

Dans mon cas, cela a fonctionné comme un charme dans un nuage de points avec un ensemble de valeurs X et de nombreuses séries Y. Le résultat d’une formule étant «  » est traité comme une valeur nulle, pas comme une cellule vide. Avec le résultat #N/A, la ligne s’arrête à la dernière valeur valide générée, ce qui correspond exactement au comportement dont j’avais besoin. Mais je suis d’accord avec J Reilly, il devrait être possible de générer une cellule vide. Parfois, vous voulez que la ligne soit interrompue pour les cellules vides, ce qui est le comportement « Gaps » pour les cellules vraiment vides. L’exemple ci-dessus montre le comportement « Connecter des points de données avec une ligne ».

hé le 13 octobre 2016 :

L’astuce #NA ne fonctionne pas pour mes cartes. Il affiche toujours les cellules #NA comme vides

J Reilly le 15 août 2016 :

La formule =IF(D3= » », #N/A,D3) est incorrecte, doit se lire =IF(D3= » », #N/A,E3).

De plus, vous évitez le comportement stupide d’Excel concernant les écarts. Ce que je verrais, c’est que s’il y a une cellule nulle (impossible à obtenir avec une formule), le graphique ne devrait rien montrer (pas une ligne de jonction). Cependant, même avec la fonction NA(), il affiche toujours une valeur de zéro.

Robbie C. Wilson (auteur) le 10 juin 2013 :

Bonjour Haseena,

Merci beaucoup pour vos commentaires, je suis vraiment heureux que vous l’ayez trouvé utile.

Robbie C. Wilson (auteur) le 10 juin 2013 :

Merci pour votre aimable commentaire Jabelufiroz, ravi que cela vous ait plu.

firozo d’Inde le 10 juin 2013 :

Impressionant. A voté.

Haseena d’Inde le 10 juin 2013 :

vraiment bien expliqué..voté et utile.