« Rechercher et sélectionner »

Quand je développe des modèles dans les entreprises, dans environ 20% des cas, les besoins sont assez clairs et – après les avoir analysés – je peux assurer le développement tout seul, quitte à faire le point de temps en temps avec le client pour vérifier que la trajectoire est toujours bonne.

Le plus souvent – dans les 80% de cas restants – les besoins s’affinent au cours de la réalisation du modèle et celle-ci a donc lieu en équipe car je travaille alors, le plus souvent, avec un ou deux représentants de l’entreprise.

Quand cela arrive, les gens avec qui je travaille sont toujours surpris de constater la fréquence avec laquelle j’utilise les commandes « Rechercher » et « Remplacer », qui sont cachées dans la commande « Recherche et sélectionner », à la droite de l’onglet « Accueil ».

Vous avez d’ailleurs tout à fait intérêt à ignorer le ruban – avec lequel il faut deux clics pour arriver à la commande « Rechercher », et à appeler directement la commande « Rechercher » avec le raccourci [Ctrl]-f. Cela vous met dans l’onglet « Rechercher » et vous pouvez alors cliquer dans l’onglet « Remplacer » ou – pourquoi pas, puisque vous êtes déjà au clavier – utiliser son raccourci [Alt]-p. 

Remarque – Quand vous avez trouvé la première occurrence, vous pouvez passer rapidement à la suivante avec le raccourci [Alt]-v, qui correspond à la lettre soulignée dans « Suivant ».

Les jokers « ? », pour un caractère, et « * », pour une chaîne de longueur quelconque, fonctionnent avec les deux commandes. Je vous expliquerai dans un article ultérieur pourquoi ils sont cependant totalement inutiles dans le cas de la commande « Remplacer ».

Une autre façon d’étager les X

Une autre solution est possible pour mettre des étiquettes de l’axe des X en deux niveaux. Nous voyons à gauche le premier graphe créé il y a quatre jours, sans la distinction entre le réalisé et le prévu.

La seconde solution que nous avons choisie, illustrée par la graphe à droite, fait référence à un axe des X supplémentaire en E2:E17, dans lequel nous avons entré la formule =C2&car(13)&B2.

L’avantage de cette solution est que l’affichage du trimestre et de l’année prend moins de place en hauteur qu’il n’en prenait auparavant, ce qui est particulièrement intéressant si, comme dans certains tableaux de bord, on souhaite pouvoir – dans un rectangle de taille donnée – allouer le plus de place possible au graphe lui-même.

L’inconvénient de la solution est que le texte apparaissant au niveau inférieur doit être court : s’il dépasse la place prise par le texte correspondant du niveau supérieur, il ne peut plus apparaître en horizontal !

Remarque – On arrive à bien distinguer les années dans notre exemple car, dans les « Options » de l’axe horizontal, nous avons choisi un « Intervalle entre les graduations » de 4.

Des étiquettes des X à étages

Parfois, vous souhaitez créer un graphe dans lequel l’axe des X possède plusieurs niveaux hiérarchiques, comme dans les deux graphes ci-dessous.

La technique pour cela est très simple, il suffit de prendre comme axe des X le bloc A2:C17. Excel va alors naturellement gérer les trois niveaux d’étiquettes.

Dans le cas du second graphe, nous avons voulu mettre en relief les valeurs réalisées par rapport aux valeurs prévues, que nous souhaitons afficher en reouge.

Pour cela, la technique est très simple… Il suffit d’entrer en D19:E19 la formule =$D2, puis de tirer ces deux formules vers le bas jusqu’à la ligne 34. Ici, nous avons aussi, pour la lisibilité, récupéré les étiquettes des colonnes A:C, mais cela n’a aucune utilité pour le graphe.

Il suffit alors d’entrer la formule =na() dans les cellules prévues du bloc C19:C34 et dans les cellules passées du bloc C19:C34, d’ajouter la série C19:C34 dans le graphe du bas et de la mettre en rouge pour obtenir le résultat du second graphe.

Remarque – Il ne serait pas difficile de comparer la date actuelle à une cellule affichant la date de début du trimestre actuel, avec en D19 la formule =si(date<=date_réf;$D2;na()) et en E19 la formule =si(date>=date_réf;na();$D2).

Animation en PPT d’un graphe

Quand vous collez un graphe dans une diapositive de PowerPoint (PPT), vous souhaitez parfois animer ce graphe en dévoilant par exemple les séries une par une.

Voici la procédure à suivre pour atteindre cet objectif…

Sélectionner le graphe dans Excel, copiez-le puis collez-le dans la diapositive voulue.

Sélectionnez le graphe dans la diapositive, comme on peut le voir dans la première image ci-dessous.

Cliquer sur « Ajouter une animation » puis sur « Autres effets d’entrée » et sélectionnez alors « Apparaître » ou tout autre effet à votre goût, puis validez.

Cliquer à présent sur le bouton « Options d’effet ». Vous avez maintenant le choix entre quatre options, la première d’entre elles – « Par série » – étant à mon avis le plus souvent la mieux adaptée.

Validez et lancez la présentation… La seconde image – ci-dessus – montre le résultat obtenu après l’affichage des trois premières séries.

Remarque – Avec un histogramme cumulé et aussi – mieux encore ! – des lignes de série, l’impact visuel est plus impressionnant, car on voit les séries s’empiler une à une…

Dernière non vide ou non nulle ?

Dans l’article publié le 4 octobre, nous cherchions la dernière valeur non vide dans une ligne. Aujourd’hui, nous nous attaquons à un problème légèrement différent : quelle est la valeur de la dernière cellule non nulle dans une ligne ?

En fait, il suffit de modifier légèrement les deux premières formules pour que cela fonctionne…

Pour faire le test, nous avons entré en A7, puis recopié à droite, la formule =A1.

Remarque 1 – Nous constatons au passage que, quand  l’original est vide, la formule renvoie « 0 » et non une cellule vide…

Pour A9, il suffit de remplacer NON(ESTVIDE(A1:J1)) par (A7:J7>0)…

Pour A10, il suffit de remplacer (A1:J1<>"") par (A7:J7<>0)…

En revanche, pour l’index(equiv(…)), il n’y a pas de solution de remplacement simple.

Remarque 2 – Cela nous montre, une fois de plus, que le traitement des cellules à 0 et celui des cellules vides peut être assez différent…

Un petit mot en faveur des pigeons

Une fois n’est pas coutume, je vais utiliser la tribune de ce blog pour faire passer un message de nature économique. Je suis entièrement d’accord avec les remarques faites par les fameux « pigeons » à François Hollande.

En dehors de ma spécialisation en  modélisation, tout particulièrement via Excel depuis maintenant plus de 30 ans, j’ai toujours été ce que l’on appelait un « venture capitalist » et que l’on appelle aujourd’hui « créateur de start-up » et/ou (selon le cas) business angel.

Mon mentor en la matière, lorsque j’étais étudiant au MIT, était le général Doriot, le créateur d’American Research & Development (ARD), la première société – historiquement – de capital-risque au monde :

http://fr.wikipedia.org/wiki/Georges_Doriot

J’ai été à l’origine de – ou participé à – la création d’une vingtaine d’entreprises. 

La toute première à 26 ans, alors que je venais d’obtenir mon doctorat du MIT : c’était Flight Transportation Associates, créée avec mon directeur de thèse au MIT. Nous avons fondé cette société pour pouvoir facturer mes premières interventions de conseil : ma thèse de doctorat portait sur la modélisation dans le transport aérien et cinq compagnies aériennes (pas Air France, qui ne croyait qu’aux polytechniciens !) étaient déjà mes clientes. 

Ma dernière création, la société de conseil Finance 3.1, a été lancée en octobre 2009 – pour mes 65 ans –, en association avec deux jeunes consultants. C’est cette société qui a développé les géniales barres d’outils Upslide pour Excel et Powerpoint (cf. les articles du 6 au 18 mai 2011). UpSlide vient de passer le cap des 1.000 licences et l’équipe compte à présent 8 personnes.

Le général Doriot expliquait ainsi les principes du venture capital : « Vous participez à la création de 100 entreprises. 90% disparaissent. 10% survivent. 1% réussit réellement et vous rend au moins du 100 pour 1. Il a prouvé la validité de ce principe avec la création de Digital Equipment…

En voulant taxer fortement les plus-values obtenues quand on tient enfin l’entreprise qui a vraiment réussi, on annihile toute volonté de création d’entreprise à risque.

Si l’on abat tous les arbres fruitiers, on fait une récolte, une seule, et après il n’y a plus rien. Si l’on veut assurer une production régulière, il faut préserver les arbres fruitiers… 

Monsieur le Président, si vous voulez assurer la richesse de notre pays et de vos électeurs, veillez à favoriser l'investissement et la prise de risques, en les récompensant largement, sinon les entrepreneurs iront ailleurs en Europe ou, plus tragiquement encore, en Chine.

Car, comme le disent nos amis chinois, par la voix de Lao Tseu : « Quand le riche s'appauvrit, le pauvre meurt ».

La dernière cellule non vide

Il est parfois nécessaire, dans un bloc, de récupérer la valeur de la dernière cellule non vide du bloc. Nous prenons ici le bloc à fond bleu ciel A1:J1 et nous voulons récupérer – dans les cellules à fond jaune – la valeur de la dernière cellule non vide de ce bloc.
 

Comme c’est fréquemment le cas en modélisation, il y a de multiples solutions possibles. Nous vous en montrons trois ici, chacune ayant un caractère particulier. Les formules de la colonne A sont reproduites en colonne B.

Une formule matricielle

La première solution est une formule matricielle, donc validée par [Ctrl]-[Maj]-[Entrée]. Elle récupère par la fonction Index() la valeur du vecteur dont l’indice est le numéro de la dernière colonne dans laquelle on a trouvé une cellule non vide.

Cette formule nous montre – une fois de plus – que l’on peut pratiquement résoudre tous les problèmes d’Excel à l’aide de formules matricielles.

Une formule originale

La seconde formule est doublement originale.

Tout d’abord car elle crée un vecteur grâce à la formule 1/(A1:J1<>"") : quand on évalue cette formule, on obtient {1.#DIV/0!.1.1.#DIV/0!.1.1.#DIV/0!.1.#DIV/0!}, donc un vecteur contenant un 1 pour chaque position non vide et la valeur d’erreur #DIV/0! pour chaque position vide.

La seconde originalité vient de ce que le troisième argument est le vecteur lui-même alors qu’intuitivement on l’aurait imaginé en deuxième argument plutôt qu’en troisième.

La formule la plus simple

La troisième solution est la plus simple que j’aie pu imaginer mais elle exige que les cellules concernées ne reçoivent que des valeurs positives, ce qui est quand même fréquemment le cas dans des tableaux de données Excel.

Si l’on remplace le premier argument « -1 » par « -10^33 », cette formule marchera aussi tant que le tableau contiendra des nombres quelconques supérieurs à -10^33.

Remarque – Cet article a déjà été publié le 21 septembre 2008, mais – cette fois-ci – nous allon le compléter…