Monsieur Excel
Pour tout savoir faire sur Excel !

30 mai 2011

Des « exact() » en rafale…

Suite à l’article présenté il y a quatre jours, un lecteur m’a demandé s’il était possible, en une seule cellule, de calculer le nombre de cellules « exactement » égales dans une série de deux vecteurs, c’est-à-dire – dans notre exemple – affichant un VRAI en colonne D.

C’est ce que nous avons fait avec l’exemple ci-dessous dans la cellule D9. Nous en avons profité pour calculer aussi, en C9, le nombre de cellules considérées comme égales, donc ayant un VRAI en colonne C. Pour les formules en C1:C6 et D1:D6, revoyez l'article d'il y a quatre jours...

Une fois de plus, la solution miracle passe par des formules matricielles, donc validées avec la combinaison [Ctrl]-[Maj]-[Entrée]

Formule de C9 : =somme(1*(A1:A6=B1:B6))

Formule de D9 : =somme(1*exact(A1:A6;B1:B6))

Remarque – Dans les deux formules, vous pouvez remplacer le « 1* » par « -- », mais je trouve pour ma part cette solution – souvent proposée dans les sites de conseil Excel – moins facilement compréhensible que la multiplication par 1. L’idée dans les deux cas est d’obtenir une valeur de 1 car 1*VRAI est évalué comme +1, le même résultat étant obtenu par « -- » qui correspond à (-1)*(-1), donc aussi à +1

26 mai 2011

Tutorial de la fonction exact()

La fonction exact() est une des fonctions orphelines d’Excel, connue par peu d’utilisateurs. Elle est intéressante car c’est la seule façon simple de vérifier si le contenu de deux cellules est réellement identique.

En effet, comme nous l’avons revu récemment avec notre série d’articles sur les fonctions de recherche, Excel fait bien la différence entre les lettres accentuées et celles qui ne le sont pas, mais il est en revanche indifférent au fait qu’il s’agisse de majuscules ou de minuscules.

Nous en voyons l’illustration dans le tableau ci-dessus où le test A1=B1 ne donne pas le même résultat que exact(A1;B1) quand la capitalisation n’est pas la même dans les deux cellules, comme on le voit dans les lignes 1 et 3.

Le cas de la ligne 6 est particulier : il se trouve que le contenu de la cellule B est "Lebleu ", avec un espace excédentaire à la fin. J’ai mis ce dernier exemple pour vous rappeler que la présence d’un espace supplémentaire, même s’il a beau être normalement invisible, rend la cellule différente dans tous les cas de figure.

22 mai 2011

Extraction rapide depuis un TCD

Prenons notre base type, déjà présente dans plusieurs articles. Cette base comporte 25 fiches. Supposons que nous en ayons fait un TCD ; que ce soit en Excel 2003, 2007 ou 2010 n’a aucune importance : la fonctionnalité que nous allons présenter existe dans toutes les versions…

Voici donc la base à partir de laquelle le TCD a été construit :

Le TCD affiche le salaire moyen par âge et par sexe. Une particularité des TCD est que, quand on effectue un double clic dans une cellule de résultat, Excel crée automatiquement une nouvelle feuille dans laquelle la partie de la base correspondant aux titres de la ligne et de la colonne du TCD est extraite.

Ainsi, dans le TCD ci-dessous, la cellule active est la cellule C7, qui correspond aux garçons de 27 ans.

Un double clic dans cette cellule insère automatiquement une feuille dans laquelle on trouve l’extraction de la base correspondant aux garçons de 27 ans, comme nous pouvons le voir dans le cliché en bas de l’image ci-dessous.

Remarque 1 – Si l’on voulait extraire toutes les personnes de 27 ans, tous sexes confondus, il faudrait faire un double clic sur la cellule D7, celle du salaire moyen des 27 ans.

Remarque 2 – Bien entendu, avec ce mode d’extraction, tous les champs de la base sont extraits. On ne peut alors pas sélectionner les champs à extraire. En revanche il est très aisé, dans cette nouvelle feuille, de sélectionner toutes les colonnes superflues, puis de les détruire rapidement avec un clic droit et
« Supprimer ».

18 mai 2011

UpSlide – Autres informations

Nous venons de voir – avec les trois articles précédents – comment fonctionnaient les barres d’outils UpSlide pour Excel et Powerpoint.

Aspects techniques

Les barres d’outils Upslide ont été développées en .NET avec la technologie VSTO.

Une des raisons pour lesquelles les barres d’outils n’ont pas été programmées en VBA est que le niveau de sécurité des macros développées en VBA est extrêmement léger : les protections des feuilles Excel, des classeurs et des modules VBA sont très peu fiables et l’on trouve hélas aisément sur Internet de quoi les contourner.

Le fait d’avoir codé en .NET nous a apporté plusieurs avantages :

● un déploiement et des mises à jour simplifiés ;

● le développement d'interface graphiques mieux intégrées à Excel et à Powerpoint ;

● un développement et une maintenance grandement simplifiés par le langage et l'environnement de développement.

Pour en savoir plus sur la technologie VSTO, vous pouvez consulter :

http://en.wikipedia.org/wiki/Visual_Studio_Tools_for_Office

Aspects financiers

Après ces trois articles sur UpSlide, je vois venir la question : « Et tout cela, combien ça coûte ? »

A titre indicatif, l’acquisition des deux barres (Excel et Powerpoint) pour une équipe de 20 personnes revient normalement à 5.000 € HT en coût fixe pour la personnalisation des deux barres, sauf s’il y a vraiment un grand nombre de modèles, formats, et autres éléments de personnalisation à prévoir. Par ailleurs, il y a un loyer de 5.000 € HT par an, soit un coût de 250 € HT par an et par cadre.

Autant dire que – si vos cadres ne chôment pas, ce que nous vous souhaitons – l’amortissement de ces barres d’outils ne pose aucun problème vu le temps gagné d’une part et la qualité supérieure des modèles et des présentations, d’autre part.

Si vous souhaitez voir une présentation de ces barres dans votre entreprise, contactez-moi : thiriez@hec.fr.

14 mai 2011

UpSlide pour Powerpoint

Dans Powerpoint, l'onglet UpSlide est immédiatement visible, car il est logé en tout premier onglet. Comme dans le cas de la barre « UpSlide pour Excel », tout est personnalisé pour l’entreprise qui acquiert le logiciel : on utilise ses modèles de présentation et – pour chacun d’entre eux – les types de diapositives prédéfinis par l’utilisateur.

Pour créer une présentation, on commence par sélectionner dans la première commande le type de présentation souhaité. Ensuite, on clique dans «le bouton « Diapositive » pour choisir le type de diapositive voulu.

UpSlide introduit dans Powerpoint le concept de « section » déjà présent dans Word : une section représente un chapitre et on peut en créer une à tout moment. On peut aussi – selon le même principe – définir des sous-sections.

Quand on clique dans le bouton « Pieds de page », le titre de la présentation et son bas de page (la date, par exemple), sont automatiquement reportés en bas de page dans toutes les diapositives, ainsi que le numéro de chaque diapositive.

Quand on clique dans le bouton « Table des matières », des diapositives sont automatiquement créées (ou actualisées, si elles existent déjà) pour une table des matières générale (la liste des sections) et une table des matières par section. Si toutes les diapositives d’une section ont été déplacées, cela est pris en compte automatiquement dans ces tables des matières, ainsi que la pagination.

Si l’on a modifié le titre d’une diapositive, ce titre est actualisé dans la table des matières dès que l’on clique dans le bouton « Table des matières ».

Quand un import d’Excel est actif, le bouton « Imports Excel » actualise le lien avec Excel et rafraîchit l’import.

Le bouton « Illustrations » donne un accès direct à des séries de schémas, de graphiques, de symboles, de logos et de cartes, tous ceux-ci personnalisés bien entendu pour la société cliente.

Le bouton « Arranger les formes » recadre automatiquement les blocs de la diapositive active, ce qui est utile quand certains de ces blocs ont été déplacés suite à des manœuvres douteuses…

Nous avons récemment fait une démonstration d’UpSlide dans une grande entreprise française, présentation durant laquelle une présentation complète partant de zéro – rédaction des textes comprise – de 10 diapositives regroupées en deux sections, le tout avec trois imports d’Excel, a été réalisée en moins de 10 minutes chrono.

La grande entreprise en question a passé sa commande le jour même de la présentation, c’est dire combien le produit a été convaincant.

Voilà ce que vous apporte la barre « UpSlide pour Powerpoint » : un immense gain de productivité, plus la garantie de respecter la charte graphique et les habitudes de présentation de votre entreprise.

10 mai 2011

UpSlide pour Excel (b)

La particularité principale d’UpSlide, que j’avais gardée pour la bonne bouche, est que tous les formats – numériques ou autres – toutes les mises en page, les modèles de graphes, les modèles de votre entreprise,… sont personnalisés pour respecter la charte graphique de votre entreprise, la façon dont elle aime présenter les titres, les valeurs, ses rapports,…

Bien entendu, dans un souci de performance, rien de tout cela n’est programmé en VBA. Il s’agit d’une application professionnelle qui se greffe sur Excel et qui est transparente pour l’utilisateur.

UpSlide pour Excel vous apporte donc les avantages suivants :

un immense gain de productivité, ne serait-ce par exemple que la réalisation d’un waterfall (ou bridge) en un clic au lieu de 15 à 30 minutes de travail pour un bon spécialiste d’Excel ;

la garantie de respecter dans tous vos modèles la charte graphique de l’entreprise (polices, tailles, couleurs,…) ;

l’utilisation de modèles, non plus au titre de classeurs comme cela est prévu dans Excel (*.xlt), mais sous la forme d’onglets qui se créent et se remplissent d’un clic ;

un export optimisé vers Powerpoint ;

un envoi facilité d’e-mail avec le fichier Excel en pièce jointe.

Par d’ailleurs, d’un clic, vous transformez n’importe quel graphe d’Excel déjà présent dans votre classeur en un graphe utilisant exclusivement vos couleurs et vos polices !

Exemple de mise en forme

Il m’a fallu – grâce à UpSlide – moins de 40 secondes pour transformer le premier tableau ci-dessous de façon à obtenir le second tableau, où toutes les constantes ont été divisées par 1.000 et où le formatage améliore sensiblement la qualité de la présentation.

Remarque – La seule chose que ne fait pas UpSlide est de transformer automatiquement le contenu de la cellule A2 ! Mais là, on avait prévu dès le texte initial qu’il s’agirait de K€…

06 mai 2011

UpSlide pour Excel (a)

Je vous ai présenté la société Finance 3.1 le 8 novembre 2009, et nous avons fait le point sur sa première année d’activité le 8 février 2011.

Finance 3.1 vient de sortir un outil de productivité particulièrement performant : la barre d’outils UpSlide, qui est en fait une double barre d’outils : une barre pour Excel et une pour Powerpoint. Pour le moment, mais cela évoluera dans les mois à venir, UpSlide n’est disponible que pour les entreprises, pour un ensemble de 20 postes ou plus ; il est possible de prendre une licence pour moins de 20 postes, à condition de payer le prix prévu pour 20 postes :)

Cette barre d’outils est disponible pour toutes les versions d’Excel. Avec Excel 2003, vous avez une barre d’outils et un menu spécialisé. Avec les versions ultérieures, il y a un ruban UpSlide :

La commande « Smart Format » permet de décorer d’un clic l’ensemble d’un tableau. Non seulement votre tableau est immédiatement décoré mais vous évitez par la même occasion les graves problèmes posés par la commande « Mettre sous forme de tableau » dont nous avons parlé dans les articles du 30 septembre et du 3 octobre 2009. Pour mémoire, cette commande avait été présentée initialement dans les articles du 27 et du 31 octobre 2008.

Chacune des trois commande « Titres », « Item » et « Totaux » fait boucler sur une série de formats de titres, d’éléments individuels et de totaux.

Dans le bloc suivant, on distingue trois commandes de mise en forme dans la première ligne, la multiplication ou la division par 1.000, et l’inversion, de toutes les constantes dans la seconde ligne, et divers formats de décimales, de pourcentages et de fonds de couleur dans la troisième ligne.

La première commande du bloc suivant, « Graphiques spéciaux », permet de construire en un seul clic des graphes particuliers tels que le waterfall (ou bridge) ou le Marimekko, graphes qui seraient longs à créer à la main…

Dans la partie droite du ruban, on notera en particulier un bouton pour exporter directement le bloc ou le graphe sélectionné dans la diapositive active de Powerpoint et un bouton pour redimensionner cet objet avant export afin qu’il ait aussitôt la taille voulue dans la diapositive…

Notons aussi la commande « Tableaux standards » pour insérer d’un clic les feuilles contentant les modèles de votre entreprise (bilan, compte de résultat, rapport d'activité,...), et enfin la commande « e-mail » vous permettant de rapidement envoyer un mail avec le classeur en pièce jointe.

Si vous souhaitez bénéficier d’une démo d’UpSlide dans votre entreprise, envoyez un mail à thiriez@hec.fr.

02 mai 2011

Faire simple, ou compliqué ?

Vous avez à partir de la cellule A1 toute une série de cellules comportant chacune une date et une heure, puis deux adresses e-mail, et enfin du texte, le tout séparé par des virgules.

Vous souhaitez récupérer en colonnes B et C les deux adresses e-mail. Pour cela, vous disposez de deux solutions radicalement différentes. La première, comme nous l’avons fait dans les lignes 4 et 5, revient à utiliser des formules appropriées en B4 et C4 :

B4 : =stxt(A4;cherche("""";A4)+1;(cherche("""";A4;cherche("""";A4)+1)-1)-cherche("""";A4))

C4 : =stxt(A4;cherche("""";A4;cherche("""";A4;cherche("""";A4)+1)+1)+1;(cherche("""";A4;cherche("""";A4;cherche("""";A4;cherche("""";A4)+1)+1)+1)-1)-cherche("""";A4;cherche("""";A4;cherche("""";A4)+1)+1))

Tout cela fonctionne parfaitement bien, comme nous pouvons le constater dans les cellules de B4 à C5. Il y a cependant une autre solution, bien plus simple…

Il suffit pour cela de sélectionner le bloc concerné, soit seulement A1:A2 dans cet exemple, puis d'utiliser la commande « Convertir » du menu « Données » (en Excel 2007, onglet « Données, bouton « Convertir »), de sélectionner l’option « Délimité », de prendre l’option « Virgule » et de valider.

Le contenu de la colonne A se répartit alors dans les colonnes de A à D, les virgules jouant le rôle de séparateurs de colonne. Il ne reste enfin plus qu’à détruire les colonnes A et D pour disposer des deux colonnes contenant les adresses e-mail.