Monsieur Excel
Pour tout savoir faire sur Excel !

28 mai 2014

Cellule vide – ou pas vide ?

La notion de vide dans Excel est une notion pour le moins ambiguë, et peut sembler tout aussi mystérieuse que l’orthographe du mot« ambiguë »…

Nous avons illustré cela dans le petit exemple ci-dessous :


En A3:A6, nous avons entré les formules reproduites en B3:B6. La cellule A2 est vide, comme nous avons essayé de l’indiquer en B2, mais comment dire qu’il y a du vide sans rien dire ?….

En C2, nous avons la formule =estvide(A2), reproduite ensuite vers le bas. Nous constatons que seule la cellule A2 est considérée comme véritablement vide. Les cellules A3 et A6, qui contiennent chacune un texte entièrement vide, ne sont pas considérées comme vides par estvide().

Et pourtant, en E2, la formule =nb.vide($A$2:$A$6) – reproduite en E1 – nous informe qu’il y a trois cellules vides dans le bloc. Dans ce cas, les cellules A3 et A6 sont donc bien reconnues comme vides. Allez comprendre…

Remarque 1 – En E5, nous avons entré la formule matricielle dont le texte est reproduit en E4. Nous constatons qu’elle joue exactement le même rôle que la fonction nb.vide(). Nous l’avons mise là pour vous fournir un exemple de plus d’utilisation de formule matricielle.

Remarque 2 – Une fois encore, l’aide de Microsoft fait très fort, cette fois-ci en nous fournissant un renseignement contenant deux erreurs dans la même phrase : d’une part, ce n’est pas « " " » qui représente un texte vide, mais « "" » ; d’autre part, les cellules contenant « " " » ne sont jamais comptées comme vides !


rédigé par Hervé Thiriez | 0 commentaire(s)

23 mai 2014

Copier un graphe au format GIF

Parfois, vous pouvez avoir envie d’enregistrer un graphe en format GIF, par exemple pour le retoucher ensuite avec l’aide d’un logiciel de dessin.

La macro publiée ci-dessous vous permet d’automatiser le processus de la sauvegarde d’un graphe sous la forme d’une image GIF. Je l’ai trouvée dans Raj Excel : http://rajexcel.blogspot.fr/

Sub Graphe_en_GIF()
    Dim sFileName As String
    sFileName = ThisWorkbook.Path & "\" & ActiveChart.Name & ".gif"
    ActiveChart.Export Filename:=sFileName, FilterName:="GIF"
End Sub

Si vous voulez en savoir plus sur la méthode Export, vous pouvez consulter :
http://support.microsoft.com/?kbid=163103

Cet article fait référence à Excel 97, mais ses conseils sont encore valables avec les versions plus récentes d’Excel.

Si vous voulez développer une macro avec des réglages plus évolués, par exemple avec un choix du format graphique à utiliser, vous pouvez aussi consulter l’article suivant de Jon Peltier :
http://peltiertech.com/WordPress/enhanced-export-chart-procedure/


rédigé par Hervé Thiriez | 0 commentaire(s)

18 mai 2014

Consolidation avec identification

Une autre technique de consolidation revient à utiliser la commande « Consolider » de l’onglet « Données ».

Pour cela, créons un onglet « Consolidé » dans lequel nous récupérons les mêmes dates en B1:G1 mais où – pour tout compliquer – les titres des lignes ne sont plus placés dans le même ordre qu’auparavant (comme vous pouvez le constater en comparant avec l’article précédent).

Dans la fenêtre associée à la commande « Consolider », nous identifions les trois blocs originaux à consolider, mais surtout en cochant l’option « Colonne de gauche ». Dans ce cas précis, il n’est pas nécessaire de cocher aussi « Ligne du haut », mais ce peut être un bon réflexe de le faire quand même…

Dès que nous validons, nous obtenons le résultat consolidé ! Excel a bien été capable de récupérer les données en tenant compte du nouvel ordre des titres de lignes. Il en aurait fait de même avec les titres des colonnes si ceux-ci aussi avaient été permutés.


Remarque 1 – Attention à bien activer la cellule A1 avant de lancer la consolidation ! En effet, sinon, le coin supérieur gauche de la consolidation sera la cellule active, et la consolidation ignorera vos titres actuels et ne sera plus capable de vérifier les titres des lignes et des colonnes.

Remarque 2 – Il y a bien une option « Lier aux données sources » mais je ne vois pas bien son intérêt car, dans ce cas, elle insère une ligne pour chaque onglet à consolider et chaque ligne à consolider, ce qui revient en fait à physiquement dupliquer toutes les parties concernées des onglets à consolider.


rédigé par Hervé Thiriez | 0 commentaire(s)

11 mai 2014

Consolidation sans les onglets A-Z

Une autre technique de consolidation permet de consolider « tous » les autres onglets du classeur sur n’importe quel onglet intermédiaire que l’on aura inséré. Mais il faut alors que tous les autres onglets soient consolidés…

Insérons donc un onglet « Conso » juste après l’onglet « Allemagne », et collons-y les même titres de ligne et de colonne que dans les autres onglets.

Puis, en B2, entrons la formule =somme('*'!B2).

Dès que nous validons cette formule, elle se transforme aussitôt en =somme(Allemagne!B2;U.K.:France!B2), et cela marche parfaitement, comme nous le constatons ci-dessous où nous observons le résultat avec la cellule C3 active.


En fait, il n’est pas indispensable que tous les autres onglets soient consolidés en même temps, mais il faudrait alors regrouper au début – ou à la fin – du classeur les onglets à ignorer, et modifier la formule de somme ci-dessous pour, dans le premier cas, ne démarrer qu’à partir du premier onglet voulu et, dans le second cas, s’arrêter au dernier onglet voulu.

Remarque – Si le nom d’un des onglets cités dans la formule contient au moins un espace, Excel ajoute automatiquement les apostrophes nécessaires. Ainsi, si l’on renomme « U.K. » en « Grande Bretagne », la formule se transforme aussitôt en : =somme(Allemagne!C3;'Grande Bretagne:France'!C3).


rédigé par Hervé Thiriez | 0 commentaire(s)

06 mai 2014

Consolidation à géométrie variable

Nous avons créé un onglet par pays avec, dans la cellule B2, puis recopiée en B2:G6, la formule suivante :
Allemagne : =ent(10*alea())
U.K. : =10*ent(10*alea())
France : =100*ent(10*alea())

Pour consolider ces informations dans la  feuille « Conso », il suffit de mettre un onglet vierge baptisé « A » avant le premier pays à consolider et un autre onglet vierge baptisé « Z » après le dernier pays.

La formule de consolidation miracle, comme on peut le voir ci-dessous, entrée en B2, puis recopiée en B2:G6, est : =somme(A:Z!B2).


Il suffit de sortir un pays du bloc (A:Z) pour l’exclure de la consolidation. C’est ce que l’on peut constater avec la seconde image ci-dessus, dans laquelle l’Angleterre a été sortie : il n’y a donc plus de dizaines dans le résultat consolidé…

Remarque – Bien entendu, le temps que l’on sorte l’onglet « U.K. » du bloc A:Z, la fonction alea() a fait son effet et tout recalculé, ce qui explique que les résultats ne soient plus les mêmes à l’exclusion de l’Angleterre près. Pour éviter cela, il aurait fallu préalablement se mettre en calcul manuel…

Cette technique de consolidation est superbe pour modifier en temps réel un périmètre de consolidation. Mais elle suppose évidemment que, dans chaque onglet, les titres des lignes et ceux des colonnes soient toujours placés au même endroit.


rédigé par Hervé Thiriez | 2 commentaire(s)

01 mai 2014

Modification de l’échelle

Le bloc « Zoom » de l’onglet « Affichage » nous permet, en un clic, d’afficher l’onglet actif à une échelle de 100%, ou de zoomer aussitôt sur la sélection. Cette dernière fonctionnalité est très pratique pour donner à une sélection une échelle maximale, afin d’optimiser sa lisibilité. Je l’utilise souvent lors de mes formations.

Il donne aussi accès à la commande « Zoom », dans laquelle on peut personnaliser l’échelle de l’affichage.

Une autre façon de zoomer – ou de « dézoomer » – rapidement consiste à maintenir la touche [Ctrl] enfoncée et à utiliser la mollette de la souris : quand on la fait rouler vers soi, on diminue l’échelle, et inversement…

Une particularité moins connue est que, si l’on prend une échelle inférieure à 40%, par exemple 39%, on voit aussitôt apparaître les noms utilisés dans la feuille, comme vous pouvez le constater dans la copie d’écran ci-dessous.

Nous avions attribué un nom à chaque zone colorée.

On constate que les noms apparaissent pour les zones colorées en orange et en bleu, mais pas pour les deux blocs en jaune qui sont identifiés par un seul nom :

Toto est en effet défini par =Feuil1!$E$3:$G$5;Feuil1!$I$7:$J$10

Donc, à une échelle inférieure à 40%, on a une vision des noms définis dans la feuille, avec trois exceptions :

  • les noms attachés à une cellule unique ;
  • les noms correspondant à des zones disjointes ;
  • les noms dynamiques (aussi appelés élastiques).
Dans le cas des noms dynamiques, c’est compréhensible car ces noms n’apparaissent ni dans la liste des noms appelée par [F5] lors de la création d’une formule, ni dans la liste des noms apparaissant à gauche de la barre de formule.

C'est plus surprenant dans les deux autres cas, où les noms apparaissent à la fois dans la zone « Nom » (à gauche de la barre de formule) et dans la liste affichée par [F5].



rédigé par Hervé Thiriez | 0 commentaire(s)