XLérateur

Un post récapitulatif pour mes 10 fonctionnalités préférées sur Excel !

On va la faire à la MTV, en commençant par la fin !

10 – Les connecteurs pour dessiner efficacement et proprement
- Utilisation des connecteurs pour créer rapidement des dessins sur Excel :
2000 à 2003 : Affichage de la barre d’outils Dessin, puis :

2007 : dans l’onglet Insertion, sous “formes”. A noter que l’outil est beaucoup plus facile à utiliser sur 2003 que sous 2007 !

9-Copier une plage comme une image
Très utile pour copier des tableaux ou des graphiques comme des images (par exemple pour les coller ensuite dans powerpoint), voici la procédure :
2000-2003 : touche MAJ appuyée, puis Edition et choisir Copier une image…
2007 : Onglet Accueil, puis coller, en tant qu’image… et choisir copier comme image (au niveau de la logique MS, ca ressemble au bouton démarrer pour arrêter windows XP )

8-Ajuster les pages pour l’impression
Bien que relativement bien connu, cette astuce est néanmoins incontournable dès lors que l’on veut imprimer des tableaux. On peut ainsi laisser vide le nombre de pages en hauteur pour n’avoir qu’un ajustement sur la largeur !

7a-Créer très rapidement une série de noms
A partir des noms de lignes ou de colonnes d’un tableau, on peut ainsi très rapidement créer une série de noms.
2000-2003 : Menu Insertion, Noms, Créer…

2007 : Onglet Formules (et non plus insertion comme sous 2003), Créer à partir de la sélection:

7b-Toujours dans les noms, coller une liste avec leurs adresses
Pour utiliser cette fonctionnalité, il faut bien sur que des noms aient été créés!
2000-2003 : facile d’accès : Insertion, nom, coller une liste

2007 : c’est très bien caché, car le nom de la fonctionnalité a été changé :

6-validation et zone nommée
C’est une astuce que j’utilise fréquemment pour faire référence à une liste se trouvant dans une autre feuille :
1- Nommer la liste (2000-2003: insertion, noms, définir / 2007: onglet formules, gestionnaire de noms)
2- Dans données validation, choisir liste et faire référence au nom:

5- Quelques fonctions favorites
Evidemment, on ne peut éviter sommeprod, qui peut compléter adéquatement somme.si, lorsqu’on doit gérer plusieurs critères. Dans Excel 2007, il faut aussi regarder somme.si.ens qui fonctionne sur le même principe.
J’adore aussi Decaler,notamment en lien avec des noms dynamiques (voir plus bas).
Finalement, les fonctions index et equiv et leurs combinaisons (à la place de recherchev).

4-Les tableaux croisés dynamiques
Si vous ne les connaissez pas et que vous utilisez des listes de données (une table dont la première ligne contient les en-tête pour chaque colonne), c’est un outil incontournable pour l’analyse. je n’ai pas le temps de développer ici (ca prendrait un cd complet !), mais voici l’instruction de base :
2000-2003 : Données – Rapport de tableaux croisés dynamiques
2007 : Insertion – Rapport de tableaux croisés dynamiques
On fait ensuite glisser les champs aux emplacements désirés (en champs de lignes, de colonnes, de données ou encore de pages pour filtrer les données).

3- Les noms dynamiques
Il s’agit de créer des noms avec la fonction DECALER qui permettent de s’ajuster aux données. Par exemple, créer un nom pour faire référence à la table contenant les données alimentant un tableau croisés dynamique.
On peut aussi utiliser des noms dynamiques pour des images ou encore pour des listes.
Pour un cours complet sur ces utilisations : cliquez ici

2- 2007 seulement – la punaise sur le nom du fichier
Uniquement sur 2007, en cliquant à droite du nom du fichier à partir du bouton office, on garde en permanence les fichiers qu’on utilise le plus.
Sous 2003, on peut aussi créer des raccourcis vers les fichiers les plus utilisés (dans l’explorateur windows), mais c’est moins rapide et intuitif.

1-Les raccourcis-clavier incontournables
Certainement l’astuce permettant de gagner le plus de temps, le raccourci-clavier:
ALT : en dessinant, qui permet de caler l’image sur les bords de la cellule
F4 : répéter la dernière action
F4 : dans une formule, ajouter/supprimer des $
F3 : dans une formule (ou ailleurs), insérer un nom. Aussi utile dans validation ou mise en forme conditionnelle
CTRL + G, U, I : respectivement Gras, Souligné (Underlined) et Italique
ALT + F11 : afficher VBE
CTRL + Entrée : en éditant une formule, ca permet de recopier sur toute la plage sélectionnée
CTRL + ; : : insérer la date et l’heure
Encore plus sur ce sujet : ici

Et voilà, bravo si vous avez tout lu !
Si vous avez votre propre liste (de 5 ou 10) ou que vous voulez compléter celle-ci, n’hésitez pas !

A bientôt.

Gaetan

Cette question m’a été posé récemment par un lecteur :
Comment cumuler des heures et ajouter 7:48 pour les cellules contenant JF (pour jours fériés).
Voici le fichier correspondant

La première chose est de se souvenir de changer le format de la cellule pour afficher les heures supérieures à 24 :
XL 97-2003 : Format – Cellules – Onglet Nombre, choisir Personnalisée et taper [hh]:mm
XL 2007 : vous pouvez cliquer en bas à droite de la zone contenant les mises en formes pour afficher cet onglet :

Reste à s’occuper de la formule : =SOMMEPROD((B2:B6=”JF”)*(“7:48″))+SOMME(B2:B6)

Il s’agit en fait d’une formule sommeprod qui va créer une première matrice de chiffres renvoyant VRAI si le contenu de la cellule est égale à JF et FAUX s’il est différent de JF :
{FAUX;FAUX;FAUX;VRAI;FAUX}
Comme on multiplie ensuite ce résultat par “7:48″, le VRAI est transformé en 1 et le FAUX en 0.
D’où l’équivalent de :
{0;0;0;0,325;0}
0,325 correspondant à “7:48″*1

Il ne reste plus ensuite qu’à y ajouter la somme des autres cellules.
SOMME(B2:B6)

A bientot.

Gaetan Mourmant

Bonjour à tous,

Ce post fait suite à celui-ci

Suite à cet article, j’ai eu quelques commentaires et voici la version 2 du calendrier qui permet :
– un appel du calendrier par clic droit à partir de n’importe quelle cellule
– l’appel de la macro pour n’importe quel classeur, puisqu’il s’agit maintenant d’une macro complémentaire.

Vous pouvez la télécharger ici :
http://www.xlerateur.com/calendrierv2.zip

Il suffit de la dézipper à l’endroit désiré (le répertoire des macros complémentaires est l’idéal, mais ce n’est pas obligatoire). Assez souvent, un double-clic sur le fichier suffit à le lancer.
Toutefois, la procédure officielle est la suivante : dans Excel, choisir le menu Outils – Macro complémentaire. Finalement, en cliquant sur le bouton parcourir, vous pouvez sélectionner le fichier que vous venez de dézipper.

A bientôt

Gaetan

Excel a un calendrier déjà fourni, mais parfois cela nécessite l’installation d’Access.

Si vous voulez avoir un calendrier qui fonctionne sans Access, je vous propose le suivant, développé par Iznoggod et Daniel M. J’y ai ajouté du code supplémentaire permettant d’avoir le no de la semaine (formule de Laurent Longre), ce qui donne après toutes ces combinaisons l’utilitaire suivant :

http://www.polykromy.com/blog/calendrier.zip

Je viens aussi de rajouter une vidéo expliquant le fonctionnement et comment l’utiliser dans vos propres programmes :
http://www.polykromy.com/blog/calendrier.html

Pour voir la version initiale :
http://perso.wanadoo.fr/frederic.sigonneau/code/Calendriers/CalFr2.zip

Pour la fonction du no de semaine :
http://xcell05.free.fr/pages/form/dateheure.htm#Numsemaine

Bonne journée.

Gaetan

Bonjour à tous,

Après plusieurs mois de développement, je vous annonce la sortie de mon dernier produit, dont je suis particulièrement fier

De plus, jusqu’au 21 juin, bénéficiez d’une promotion exceptionnelle.

Je vous laisse découvrir :

Bonne journée.

Gaetan Mourmant

Bonjour,

En utilisant le menu Données – Validation, on peut obliger l’utilisateur à saisir une date supérieure ou égale à la date du jour et inférieure à la date du jour plus un an.

Ce qui nous donne ceci :

Bonne journé.

Gaetan Mourmant
www.polykromy.com