- Excel - Réactiver les options de la souris
Votre souris Excel ne fonctionne plus. Plus de poignée de recopie ni de cliquer glisser. Pour réactiver cette option, allez dans Ficher > Options. Dans la catégorie Avancé, sous Options d'édition, activez la case à cocher "Activer la poignée de remplissage et le glisser déposer des cellules"

- Excel - Mise en forme conditionnelle avec date
Pour réaliser des mises en forme conditionnelle avec des dates, il faut créer des nouvelles règles à base de formules.
La colonne F du tableau contient des dates. Sélectionnez la colonne F puis saisissez ces formules :

1. Demain en couleur
   =F2=AUJOURDHUI()+1


2. Les lundi en couleur :
   =JOURSEM(F2)=2


3. Mars en couleur :
   =MOIS(F2)=3


4. Une date en couleur :
   =F2=DATE(2024;2;29)


5. Entre deux date :
   =ET(F2>DATE(2024;2;25);F2≤DATE(2024;2;29))


6. Dates Dépassées avec fonction AUJOURDHUI() - Sélection de tout le tableau sans les étiquettes :
   
   =$F2<AUJOURDHUI()

58 - Excel - Copier/Coller vers Excel online
Si vous devez effectuer un copier/coller d'un tableau Excel Desktop (en local) vers un Excel online (pour le web), vous constaterez que les formules ne sont pas conservées.

Pour contourner ce problème, désactivez les formules principales de votre fichier local en rajoutant un espace (ou un apostrophe) devant le signe égal de vos formule. Une fois en ligne, enlevez les espaces.

57 - Excel - Formater une date
Vous recevez depuis votre serveur des dates mal formatées comme, par exemple, 20230722. Excel est incapable de reconnaitre ce format.

Commencez par sélectionner vos dates

Voici la procédure :

1. Onglet Données > Convertir
2. Etape 1 : Type de données : ne rien faire (Délimité) ; suivant
3. Etape 2 : Séparateurs : ne rien faire (Tabulation) ; suivant
4. Etape 3 : Format - Sélectionner Date AMJ - clique sur Terminer

56 - Comment rajouter des 0 (zéro) après un chiffre Excel ?
On sait comment rajouter des 0 devant un nombre dans Excel par le biais des formats personnalisés (000000) mais comment en rajouter derrière ? Voici une solution à base de formule :

=A2 & REPT("0";6‑NBCAR(A2))

Est utilisé ci-dessus la fonction REPT() pour répéter un texte et la fonction NBCAR() pour compter le nombre de caractère d'une cellule.

55 - Une liste déroulante à partir de deux colonnes Excel
Pré-requis : astuce Excel n° 28

Pour créer une liste déroulante à partir de deux colonnes Excel, faites d'abord votre liste avec une seule colonne mais en utilisant une plage nommée.

Modifiez ensuite votre plage nommée (onglet Formules, Gestionnaire de nom). Excel est obligé de suivre...

Petite restriction : il ne faut pas retourner sous le menu Données /
Validation car la source est considérée comme erronée. Il faudra l'effacer
pour en sortir.

54 - Créer des listes déroulantes en cascade avec Excel
Pré-requis : astuce Excel n° 28

Pour créer des listes déroulantes en cascade avec Excel (liste imbriquées entre elles), on nomme une plage FRANCE avec des villes et une autre ESPAGNE avec des villes puis une plage PAYS avec FRANCE et ESPAGNE.
A partir de la cellule A1, dans validation des données, bouton Validation des données de l'onglet Données, choisissez Liste dans le menu déroulant et saisissez =Pays dans la Source pour faire référence à la plage des pays.

Placez-vous ensuite en B2 et créez une liste déroulante de la même manière qu’en A1 en indiquant =INDIRECT(A1) dans la Source. A1 faisant référence à la première liste déroulante.

liquez sur Ok. Vous aurez alors un message d’erreur, mais cliquez sur Oui.

Voir ici le tuto correspondant : www.votreassistante.net/creer-listes-deroulantes-cascade-excel/

53 - Fonction SOMME.SI entre plusieurs classeurs Excel
D'après Microsoft, la fonction SOMME.SI ne fonctionne pas si le classeur en référence est fermé.

En effet lorsque dans une cellule ont fait référence à la cellule ou à une plage de cellules d'un autre classeur, certaines fonctions ne fonctionnent pas si le classeur de référence est fermé. Les fonctions NB.VIDE, NB.SI et DECALER entre autres ne fonctionnent pas non plus. Ce comportement est voulu par la conception même du produit.

Il faut donc utilisez une combinaison des fonctions SOMME() et SI().

Votre fonction SOMME.SI dans la cellule B1 de votre classeur

=SOMME.SI('[Classeur1.xlsx]Feuil1'!A2:A10;A2:A10>0)

doit donc être saisie comme suit :

=SOMME(SI('[Classeur1.xlsx]Feuil1'!A2:A10>0;'[Classeur1.xlsx]Feuil1'!A2:A10;0))


Maintenant cela devrait fonctionner même si le classeur Classeur1.xlsx est fermé.
Si cela ne fonctionne pas, vous pouvez aussi utiliser une fonction matricielle en validant la fonction SOMME et SI par un Ctrl + MAJ + Enter. Vous devriez obtenir ceci :

={SOMME(SI('[Classeur1.xlsx]Feuil1'!A2:A10>0;'[Classeur1.xlsx]Feuil1'!A2:A10;0))}

Les accolades signifient que vous utilisez une fonction matricielle. Cela devrait aller beaucoup mieux :-)

52 - Liens avancés entre plusieurs classeurs Excel
Objectif : réaliser des liens entre classeurs en utilisant la poignée de recopie tout en récupérant dynamiquement le nom des différentes feuilles dans la formule.

Le problème, c'est qu'un simple lien entre classeur Excel n'accepte pas la concaténation.

C'est la fonction INDIRECT() qui permet la référence d'un lien entre classeur par une chaîne de caractère. Grace à cette fonction INDIRECT(), on va pouvoir saisir les & de concaténation dans la formule du lien avancé comme ceci :

=INDIRECT("[Classeur1.xlsx]" & A2 & "!B2")

La colonne A contient les noms des feuilles et la colonne B les chiffres à récupérer.




Pour aller plus loin, on peut aussi récupérer le nom du classeur qui se trouve dans la colonne C :

=INDIRECT("'[" & C2 &".xlsx]" & A2 & "'!B2") 

51 - Afficher le texte d'un contrôle Liste dans une cellule
Le contrôle de formulaire zone de liste déroulante (visible dans l'onglet Développeur d'Excel) a l'énorme avantage de pouvoir afficher une liste qui provient d'un autre classeur. Ce qui n'est pas le cas d'une liste de validation de données proposé dans l'onglet Données d'Excel (voir astuce n°28).

Depuis l'onglet Développeur d'Excel, insérez un contrôle de formulaire de type zone de liste déroulante. Quant-on lie un contrôle Liste déroulante à une cellule, cela n'affiche dans la cellule que le numéro de la ligne sélectionnée de la liste. Pour afficher le texte de la liste :

• Par un clic droit sur votre liste, lier votre liste à une cellule qui n'est pas celle dans laquelle vous voulez afficher le texte de la liste (par exemple C1)


• Dans la cellule dans laquelle vous voulez afficher la liste, saisissez la formule =INDEX(plage-de-votre-liste;C1)

50 - Effet de Mise en forme conditionnelle sur des lignes ou des colonnes
Pré-requis : Voir les astuces sur la mise en forme conditionnelle n°31 et n°6. Pour avoir un remplissage sur des ligne ou des colonnes entière, on sélectionne toutes les lignes sur lesquelles on veut agir et on créé une nouvelle règle de type formule comme par exemple =A1>100.
On bloc avec un dollars ($) la colonne pour remplir la ligne comme ceci =$A1>100
Ou bien
On bloc avec un dollars ($) la ligne pour remplir la colonne comme ceci =A$1>100
Autre Exemple
On bloc avec un dollars ($) la colonne pour remplir la ligne comme ceci =$A1="Cap-Ferret"

49 - Répéter les étiquettes d'élément d'un Rapport (TCD)
Depuis Excel 2010 : Dans la fenêtre des champs, clic droit sur un champ de la zone Lignes > Paramètres de champs > Disposition et impression > Répéter les étiquettes d'élément

Avant Excel 2010, cette option n'existait pas. En attendant, Voici une procédure pour, dans un tableau normal, dupliquer la cellule A1 dans les cellules au dessous :
A la souris, sélectionnez toute les cellules que vous voulez remplir (par exemple A2:A10). Saisir la formule =A1 et faire au clavier Ctrl+ Entrée. cela duplique la cellule A1 dans toute les cellules sélectionnées.

48 - Afficher un Rapport (TCD) comme un tableau normal

1. Dans l'onglet Création > Disposition du rapport > Afficher sous forme tabulaire


2. Dans l'onglet Création > Disposition du rapport > Sous-totaux > Ne pas afficher les sous-totaux


3. Répéter les étiquettes d'élément du Rapport (TCD) : voir astuce n°49


4. Si besoin, faîtes un copier/coller par valeur dans une nouvelle feuille de ce tableau

47 - Supprimer les dimanche d'une liste Excel

Objectif : Supprimer les dimanche d'une liste de date dans Excel.

Pour cet exemple, nous n'utiliserons que les 2 premières colonnes d'Excel.

Dans la colonne A saisissez une suite de dates.
Dans la colonne B saisissez et dupliquer cette fonction : =SI(JOURSEM(A1)=1;"dimanche";A1)

Soit, si le jour de la semaine de la cellule A1 est un dimanche, ecrire "dimanche", sinon ecrire la date qui est dans la cellule A1.



Sélectionner la colonne B et faire F5 (Atteindre)
Cliquer sur le bouton "Cellules" et cocher l'option "Formule" et ne garder que "texte".

Faire un clic droit sur la première cellule sélectionnée et choisir "Supprimer".
Laisser l'option sur "Décaler les cellules vers le haut" et faire oK.
Les dimanches sont supprimé !

Pour ne garder que la collone B, faite lui subir un copier /coller par valeur (avec le bouton coller "123"). Cela supprimera les formules en conservant les valeurs.
Vous pouvez, ensuite, supprimer la collone A.

46 - Excel Avancé - TCD
Les tableaux croisés dynamique d'Excel permettent aussi de travailler sur des données regroupées.

Exemple 1 : Afficher la moyenne des résultats par tranche d'age (de 5 ans).



Exemple 2 : Calculer le nombre de visiteurs par semaine.

45 - Excel Avancé - Fonctions INDEX-EQUIV
Les fonctions INDEX et EQUIV

Objectif: connaître le prénom du meilleur. On cherche d'abord le MAX(), puis, avec la fonction EQUIV(), la ligne correspondant à ce max et enfin, avec la fonction INDEX(), la valeur correspondant à cette ligne.

44 - Excel Avancé - Valeur cible
Onglet, Données, Analyse de scénarios.

43 - Excel Avancé - Fonctions statistiques


La plage à sélectionner est la même que celle de la fonction NB.SI(), soit B4:B8 en référence absolue. La cellule cliquée (soit ici B4) doit être la première de la liste et sans référence absolue (pour qu'Excel puisse dupliquer la formule).


Autres exemples sur les doublons :

- Si il y a des doublons (ici le chiffre 18) :
=SI(NB.SI(A1:A10;18)>1;"oui";"non")

- Nombre de cellule distinctes d'une plage :
= SOMMEPROD(1/NB.SI(B5:B20;B5:B20))
Cette fonction SOMMEPROD permet d'obtenir le produit d'une somme (soit matrice1 * matrice3 + matrice2 * matrice4).

- Rechercher le nombre de doublons d'une plage :
=NBVAL(A1:A10)-SOMMEPROD(1/NB.SI(A1:A10;A1:A10))

- Afficher la valeur la plus fréquente :
=MODE(A1:A10)
Attention, cette fonction MODE, qui affiche une valeur, ne marche qu'avec des valeurs numérique !

- Afficher le nombre de valeur la plus fréquente (soit le nb.si du mode) :
=NB.SI(A1:A10;MODE(A1:A10))

42 - Excel Avancé - Taux de prime
Exemple de calcul avec les fonctions NB et NBVAL :



Important : La cellule D6 doit être au format poucentage.

Voici la fonction SI qui permet d'obtenir une prime de 10% du réalisé si les objectifs sont atteint (cellule D2):

=SI(C2>=B2;C2*10%;"Pas de prime")

41 - Excel Base - Budget du ménage
Exemple de fonction SI avec une double condition (via une fonction ET).

40 - Excel Base - Référence absolue
Exemple de calcul avec des cellules en référence absolue (signe dollar) :

39 - Excel Expert - NB.SI avec 2 critères
Il n'est malheureusement pas possible de réaliser un NB.SI avec plusieurs critères. Excel refuse l'utilisation d'une fonction ET dan un NB.SI. Une solution est d'utiliser la fonction SOMMEPROD

=SOMMEPROD((plage1=critèr1)*(plage2=critère2))

Attention, cette fonction ne contiens pas le signe multiplié. Il faut le saisir manuellement.

38 - Excel Base - Facture
Une simple facture dans Excel:

37 - Excel Expert - Recherche croisée
Pour réaliser une recherche croisée sur un tableau à double entrée, on utilise la fonction INDEX [syntaxe: INDEX(matrice;no_ligne;no_colonne)] avec 2 fonctions EQUIV [syntaxe: EQUIV(valeur_cherché;matrice;0 si la valeur cherché existe)]. La première fonction EQUIV permet de récupérer le numéro de ligne et l'autre fonction EQUIV permet d'obtenir le numéro de colonne (qui sont deux des trois arguments attendu par la fonction INDEX) :

INDEX(matrice;EQUIV();EQUIV())

Exemple : On recherche le prix d'un metrage pour un département donné. Pour cela, on créé dans le tableau ci-dessous trois plages nommées : prix (C3:F10) qui correspond aux données principales du tableau, dpt (B3:B10) pour la colonne B et metrage (C2:F2) pour la ligne 2.



Fonction qui recherche le prix correspondant au département 33 et au metrage 0,2 (résultat: 12) :

=INDEX(prix;EQUIV(33;dpt;0);EQUIV(0,2;metrage;0))

Fonction qui recherche le prix correspondant au département 44 et au metrage 0,9 (résultat: 36) :

=INDEX(prix;EQUIV(44;dpt;0);EQUIV(0,9;metrage;0))

36 - Excel Base - Fonction inverse de Concatener
Pour réaliser la fonction inverse de concatener sous Excel 2007 et 2010 qui assemble plusieurs chaînes de caractère, sélectionner les cellules à séparer puis dans le menu Données / Convertir cocher l'option "Délimité" et cliquer sur le bouton Suivant. Choisissez enfin le type de délémiteur.

35 - Excel Avancé - Consolidation Excel
La consolidation dans Excel (Données / Consolider) ne restitue pas les colonnes constituées de textes car elle réalise un calcul (une somme) qui est impossible sur du texte. Seule les lignes (ou colonnes) spécifiées comme des étiquettes sont restituées (case à cocher).
Autre critique : L'outil consolider d'Excel reste incapable (même en version 2010) de récupérer les noms de feuilles dans le tableau de résultat: seul le nom du classeur est restitué.

34 - Excel Base - Excel 2010
Les nouveautés d'Excel 2010 par rapport à Excel 2007

Menu Fichier
Le bouton Office de 2007 est redevenu un onglet "Fichier". Donc nouveau bouton pour accéder à toutes les propriétés du fichier (en haut à droite). Le nouveau menu Fichier s'ouvre, par défaut sur le sous menu Information avec les options de protections, d'accessibilités et de récupérations.

Ruban
Le ruban est désormait modifiable: Onglet Fichier / Options / Personaliser le ruban.

Sparkline
Nouveau Graphiques sparkline : Onglet Insertion, groupe "Graphiques sparkline". Ils permettent de faire apparaitre des courbes, histogramme et différences entre positif et négatif directement à l'interieur des cellules. Possède sont propre onglet à apparition dynamique.

Filtre Segment
Nouveau Filtre Segment : Onglet Insertion, groupe Filtre. Affiche les connexions existantes.

Capture d'écran
Onglet Insertion, groupe Illustration. Permet d'insérer une capture d'un autre écran dans Excel.

Activation
Nouvelle façon d'activer Excel (licence) par l'onglet Fichier/ Activer le produit.

Macro
Macro sous Excel 2010 : toujours depuis l'onglet Affichage (groupe Macros à droite toute de l'écran)
mais la barre d'outils "Developpeur" est maintenant directement accessible dans Fichier / Options / Personaliser le ruban : il suffit de cocher la case "Developpeur".

Impression
Nouvelle écran d'Apercu avant impression : celui-ci apparait dans l'onglet Fichier (on peut le quitter par la touche clavier Echap ou en changant d'onglet: Accueil). Cet aperçu est directement accessible depuis l'onglet Fichier / Imprimer.

33 - Excel Avancé - Pyramide des âges dans Tableaux croisés

Voir l'excellent :-) tuto d'Easy-Micro pour réaliser une pyramide des âges dans un tableaux croisé dynamique Excel.

32 - Calc Base - La touche clavier F4 sous OpenOffice Calc 3
La touche clavier F4 qui, sous OpenOffice Calc, ouvre et ferme l'écran OpenOffice Base ne réalise donc plus, comme dans Microsoft Excel, la répétition d'action. Si cela vous manque, vous pouvez réatribuer cette action de répétition à cette touche clavier :

Dans le menu Outils / Personnaliser... onglet "Clavier". Choisir "F4" dans le liste, puis, en bas, choisir la catégorie "Edition" puis la fonction "Répéter". Appuyez enfin sur le bouton "Modifier".

Sinon, vous pouvez toujours utiliser le raccourci classique de répétition : Ctrl + Shift + Y

31 - Excel Base - Mise en forme conditionnelle
Pour raliser une mise en forme conditionnelle "classique" avec Excel 2007, allez dans l'onglet Accueil, groupe Style, ouvrez le menu (bouton) "Mise en forme conditionnelle" et choisissez l'option "Nouvelle règle".

Dans la fenêtre, choisir la dernière option "Utiliser une formule...".
L'écran proposé est celui d'Excel 2003 (voir astuce n° 6).

Profitez-en, tout de même, pour découvrir les superbes nouvelles formes conditionnelle 2007 comme les icônes ou les barres de données visiblent directement depuis le menu (bouton) "Mise en forme conditionnelle" de l'onglet Accueil, groupe Style.

30 - Calc Expert - OpenOffice.org Basic
OpenOffice.org Basic est très proche de Microsoft Visual Basic pour Applications (VBA) et de Microsoft Visual Basic.

Les bases du langage Basic
ENREGISTREUR DE MACRO
Menus Outils, Macros, Enregistrer une macro.
Le résultat visible dans l'éditeur est assez laborieux car OpenOffice Basic utilise ici la technique UNO (Universal Network Objects, objets réseau universels) qui fournit une interface compatible avec différents langages de programmation.
ECRIRE UNE MACRO EN BASIC
Menu Outils, Macros, Gérer les macros, OpenOffice.org Basic (Alt + F11)



Dans la boite de dialogue Macro, sélectionnez dans la liste de gauche l'item "Standard" sous le nom de votre classeur CALC et cliquez sur le bouton "Nouveau". Un nouveau module de code est créé et l'éditeur OpenOffice.org Basic s'ouvre :

- REM signifie REMarque (un commentaire). Peut être remplacée par l'apostrophe.
- Sub Main, est le début d'une macro avec son nom
- End Sub, la fin de la macro

Premier exemple de Macro
Faire apparaitre un message à partir d'un bouton

Voir un exemple en ligne d'utilisation de la MsgBox

Pour relier cette macro à un bouton, aller dans Calc dans le menu Affichage, Barre d'outils, Contrôles de formulaire. Clic droit sur votre bouton, menu Contrôle, Onglet Evénements, Lors du déclenchement... Sélectionner votre macro. N'oubliez pas de désactiver le mode Conception (sur la barre d'outils) avant de tester votre bouton.

Autre exemple de Macro
Saisir du texte dans la cellule A1.


Quelques exemples de Macro



Pour aller plus loin
Guide de programmation de OpenOffice.org BASIC
How To Programmation
FAQ OpenOffice

29 - Excel Base - Calcul de TVA sous Excel
Pour calculer une TVA sous Excel - toutes versions:



Attention, Excel arrondis toujours au chiffre superieur. Pour éviter des erreurs, dans le menu "Format / Cellule", sélectionnez un format "pourcentage" avec deux chiffres après la virgule.

28 - Excel Avancé - Liste déroulante dans Excel - toutes versions

Créer une liste déroulante en utilisant les données des cellules

Pour réaliser une liste déroulante dans Excel, saisissez une liste dans une plage de cellule, par exemple en colonne Z (assez loin pour être caché). Sélectionnez ensuite la plage de cellule ou vous voulez faire apparaître cette liste.
Sous Excel 2003, ouvrez la boite de dialogue "Validation des données" du menu "Données / Validation", ou, pour Excel 2007, la commande "Validation des données" intégré dans le groupe "Outils de données" de l'onglet "Données".

Sélectionnez ensuite, dans l'onglet "Options", l'item "Liste" dans le champ "Autoriser" puis sélectionnez la liste de la colonne z dans le champ "Source". Laissez les options "Ignorer si vide" et "Liste déroulante dans la cellule" cochées.
Validez par "Ok".

Vous pouvez aussi saisir manuellement une vraie liste (séparé par des points virgule) dans le champ "Source".

---

Créer une liste déroulante en utilisant des formules Excel

Outre la sélection dans les cellules et la saisie manuelle des données, vous pouvez également utiliser une formule dans le champ source pour créer une liste déroulante Excel. Toute formule qui renvoie une liste de valeurs peut être utilisée pour créer une liste déroulante dans Excel.

Votre liste est dans la plage A2:A8, soit une liste de 7 items.
Commencez par sélectionner la (ou les) cellule(s) dans laquelle vous souhaitez créer la liste déroulante (colonne B, par exemple).
Créez une nouvelle liste déroulante (Données/Validation des données) puis dans le champ Source, entrez la formule suivante :

Syntaxe de la fonction DECALER (référence ; lignes ; colonnes ; [hauteur] ; [largeur])

Cela retourne un tableau contenant la liste des sept couleurs en A2: A8. Notez que si vous entrez la formule dans une cellule, sélectionnez-la et appuyez sur F9, vous verrez qu'elle renvoie un tableau des noms de couleurs.

---

Remplir dynamiquement une liste déroulante

Modifier la formule comme ceci :

Le chiffre 7 de la première formule est remplacé par NBVAL($A$2:$A$100). La fonction NBVAL compte les cellules non vides dans la plage A2:A100. Par conséquent, la fonction DECALER s'ajuste automatiquement pour inclure toutes les cellules non vides.

Source: Cours-gratuit.com

Pour aller plus loin, voir les astuces n°54 et 55.

27 - Excel Base - Excel 2007
Généralités
- Des feuilles de plus d'1 million de ligne pour 16 000 colonnes.
- Format de fichier : xlsx (standard), xltx (modèle), xlsm (macro).
- Fonction "Live Preview" : Affichage dynamique des options de formatage.

Les formules de calcul
Les formules disposent d'une saisie automatique de manière à respecter obligatoirement leur format.
- Groupement des formules par thème: groupe Thèmes de l'onglet Mise en page, cliquez sur Thèmes.
- Evaluation des formules: groupe Audit de formules de l’onglet Formules, cliquez sur Évaluation de formule.
- Gestionnaire de noms: Onglet Formules, groupe Noms définis.
- Macro complémentaire : accessiblent via le menu Office, boutons "options Excel", menu "compléments", gérer les compléments Excel et "Atteindre".

Mise en forme
- Mode d'affichage (onglet Affichage avec le mode Mise en page).
- Style et Mise en forme automatique :
Onglet Accueil, bouton "Mettre sous forme de tableau". Ce bouton correspond au bouton "Tableau" de l'onglet "Insertion". Cela rajoute un onglet "Création" qui contient un bouton "Styles rapides".
Pour supprimer cette mise en forme "Tableau", 2 possibilités :
a - Soit en gardant le style "tableau" :
Aller dans le menu du bouton "Styles rapides" de l'onglet "Création", choisir la dernière option "Effacer".
Cela supprime le format mais conserve le style "Tableau" et les filtres.
b - Soit en gardant le format ce cellule : Dans l'onglet "Création", choisir "Convertir en plage" (groupe Outils). Cela supprime le style "Tableau" mais garde le format de cellule.
Si l'on veut tout effacer (format et style), on réalise les 2 étapes successivement.
- Format de cellule : dorénavant directement visible depuis l'onglet "Accueil".
- Style de cellule (Onglet Accueil, groupe Nombre). Cliquer sur "Normal" pour revenir sur un style de base.
- Nom de cellule : avec 2003, menu "Insertion / Noms / Définir". Avec 2007, onglet Formules, groupe "Noms définis / Définir un nom".
- Formats conditionnels : Onglet Accueil, groupe Style. Pour réaliser un format personalisé, ouvrir le menu (bouton) "Mise en forme conditionnelle" et choisir l'option "Nouvelle règle". Dans la fenêtre, choisir la dernière option "Utiliser une formule...". L'écran proposé est celui d'Excel 2003 (voir astuce n° 31). Sinon, en plus des règles "classiques", trois types de mise en forme conditionnelle sont proposés : Barres de données, Nuances de couleurs et Jeux d'icônes. Chacune réagira en fonction des chiffres contenu dans vos cellules.

Graphiques
- Graphiques "classique" : Onglet Insertion, groupe Graphiques.
- Graphiques SmartArts : Une représentation graphique de vos informations (+ organigrammes hiérarchiques). Sous l'onglet Insertion, cliquez sur SmartArt dans le groupe Illustrations. Voir l'onglet "Outils SmartArts".

Gestion de base de données
- Tris : Onglet Données, groupe Trier et filtrer. Le tri (Onglet Données) permet de trier sur les jeux d'icônes installé par la mise en forme conditionnelle (onglet Accueil). Attention d'utiliser des icônes avec un max de 3 dessins et de rajouter au moins 3 clés de tri: 1 pour l'icône max (flêche vers haut), 1 pour l'icône min (flêche vers bas) et 1 pour la colonne (tarifs).
- Filtres : Onglet Données, groupe Trier et filtrer ou bien Onglet Accueil, groupe Edition.
- Gestion des plans: Présenter une liste de données sous forme de plan dans une feuille de calcul (dans le groupe Plan de l'onglet Données).

Tableaux croisés dynamiques
- Création et utilisation par l'onglet Insertion, groupe Tableaux (nouvelle présentation!).
- Champ calculé : Dans la Liste déroulante de la barre d'outil flotantte: menu Formule / Champ calculé.
Par exemple, "=Tarifs * 10%".
Pour réaliser 2 colonnes identiques comme un deuxième tarif en pourcentage, rajouter un champ calculé nommé "Tarif 2" avec la formule "=Tarifs" puis clic droit / paramètre de champs / % par colonne.
- Champ dynamique : Un double clic sur un champ de ligne ou de colonnes (un champ dynamique donc). Peut être trier par le bouton "Avancés" : par défaut, manuel (les villes peuvent êtres déplacées à la souris). Sinon, tri par rapport à un autre champ de données.
- Element calculé : pour réaliser des calculs sur les itèms d'un champ. Pour créer un élément calculé (et pas un champ calculé :-), clic préalable obligatoire sur un élément d'étiquette (par exemple le mot Excel) et pas sur une donnée ou sur un bouton (un champ).
Puis clic dans le menu déroulant de la barre d'outils flottante TCD / formules / Elément calculé.

Protection
Onglet "Révision", groupe "Modifications".
- Le bouton "Protéger le classeur" ne permet que de protéger la stucture et les fenêtres du fichier. Les autres options (limiter les autorisations) sont pour une gestion des autorisations des membres d'un réseau Microsoft.
- La protection des cellules (menu format de cellule) est gérée par le bouton "Protéger la feuille".
Le menu "Format / vérouiller la cellule" de l'onglet "Accueil" déverouille une cellule si celle-ci est déjà vérouillée (c'est le cas par défaut: l'option est active - enfoncée).
- Bouton "Permettre la modification des plages": Je protège la feuille (par le bouton correspondant) en laissant un mot de passe sur certaine cellule.

Impression de document
- Aperçu avant impression : bouton Office (Ctrl+F2).
- Mise en page : Onglet du même nom.
- En-tête et pied de page : Onglet Insertion.

26 - Excel Avancé - La barre d'outils "Formulaires" dans Excel 2007
Sous Excel 2007, la fonctionnalité "Formulaires", accessible dans les versions antèrieures par le menu Affichage / Barre d'outils / Formulaires qui permettait de créer des boutons de macro, se retrouve dans l'onglet "Développeur", zone "contrôles".

Pour afficher cet onglet, cliquez sur le bouton Office, puis sur options Excel. Dans Standard, activez la case à cocher "Afficher l'onglet Développeur dans le ruban".

Par un clique sur "insérer", on retrouves les contrôles activeX et les contrôles de l'ancienne barre "formulaires".

25 - Excel Base - Les menus d'Excel 2007
Pour retrouver l'emplacement des commandes de menu et de barre d'outils dans Excel 2007, ouvrez le fichier Excel (format xls) des associations du ruban d'Excel 2007 proposé par Polytechnique Montréal.

Si vous êtes un inconditionel d'Excel 2003, vous pouvez retrouver le menu classique 2003 et la barre d'outils 2003 dans Office 2007 avec le shareware Addintools (30 dollard us).

24 - Excel Avancé - Tableaux croisé dynamique dans Excel 2003
Elément calculé : Pour créer un élément calculé (et pas un champ calculé :-), clic préalable obligatoire sur un élément d'étiquette (par exemple le mot Excel) et pas sur une donnée ou sur un bouton (un champ).
Puis clic dans le menu déroulant de la barre d'outils flottante TCD / formules / Elément calculé.

Astuce : Losque l'on veut afficher (comparer) 2 colonnes identiques, il suffit de tirer le même champ 2 fois dans le TCD.

Voir l'excellent tutorial sur les tableaux croisé dynamique par Jean-Baptiste Viet (jeanviet.info).

LIREDONNEESTABCROISDYNAMIQUE()
1er argument : nom d'étiquette de colonne (entre guillment)
2ième argument : Nom du tableaux, soit la référence de cellule - en absolue - à l'intersection du tableau (classque : $A$3)
3ième argument (facultatif) : Nom d'étiquette de ligne
4ième argument (facultatif) : Valeur d'étiquette

LIREDONNEESTABCROISDYNAMIQUE('Tarif';$A$3;'ville';'Bordeaux')

23 - Excel Avancé - Quelques fonctions pour OpenOffice Calc Basic
À coller dans l'éditeur Basic (Environnement de Développement Intégré - EDI)
Menu Outils / Macro...

Function prix_solde(prix_de_vente As Double) As Double
prix_solde = 0.80 * prix_de_vente
End Function

Function surface_du_cercle(radius As Double) As Double
surface_du_cercle = 3,14159 * radius * radius
End Function

Function le_carré(x As Double) As Double
le_carré = x * x
End Function


A partir des propriétés d'un bouton de formulaire, onglet Evènement:

Sub afficher_un_message
MsgBox "Bienvenue sur Easy-Micro", 16, "Information"
End Sub

22 - Excel Expert - Quelques astuces Excel VBA
Voici quelques astuces pour Visual Basic for Application (VBA)

Sub Divers()

'Imprimer la feuille active
ActiveWindow.SelectedSheets.PrintOut Copies:=1

'Supprimer la ligne active
Selection.EntireRow.Delete

'Afficher une image dans un formulaire
CommandButton1.Picture = LoadPicture("c:\windows\Bulees de savon.bmp")

End Sub

21 - Excel Avancé - Ouvrir Excel depuis l'invite de commande
Pour ouvrir l'invite de commande:
Démarrer / Exécuter... / cmd

Par exemple, pour ouvrir Excel:
cd "C:\Program Files\Microsoft Office\OFFICE11"

puis "excel.exe"

Autre exemple, si l'on veut démarrer Excel en ouvrant le classeur "C:\Temp\Test.xls" en lui transmettant les paramètres "alain", "pierre" et "paul", la ligne de commande devrait être de ce type :

excel.exe /e/alain/pierre/paul "C:\Temp\Test.xls"

Les paramètres doivent suivre immédiatement /e et ne doivent comprendre aucun espace. Leur récupération peut s'opérer immédiatement après le lancement d'Excel par le biais d'une macro Workbook_Open ou Auto_Open placé dans le classeur ouvert. Cette macro doit récupérer le contenu de la ligne de commande par GetCommandLine et utiliser les fonctions de chaîne de caractères pour localiser et séparer les paramètres personnels.

Plus d'infos sur X-CELL.

20 - Excel Expert - Traduction des noms de fonctions pour VBA
En Visual Basic for Application (VBA), les fonctions doivent être écrites en Anglais.

Exemple:

SOMME = SUM
AUJOURDHUI = TODAY
SOMME.SI = SUMIF
BDSOMME = DSUM
MOYENNE = AVERAGE
NB.SI = COUNTIF
NBVAL = COUNTA
PRINCPER = PPMT
RECHERCHEV = VLOOKUP

Voici un site qui propose la traduction, en 9 langues de 339 fonctions d'Excel 97:
Christian Herbé.

19 - Excel Avancé - L'ancienneté au mois près avec OpenOffice Calc
Le problème : à partir de la date d'embauche, calculer l'ancienneté d'un employé au mois près.
Supposons que la date d'embauche soit dans la cellule A2. On pourrait effectuer une simple différence : =aujourdhui()-A2.
Cette simple formule renvoie le nombre de jours. Mais surtout, il ne faut pas oublier de spécifier un format personalisé (un format défini par l'utilisateur) comme "#" (diese) qui signifie que l'on désir un chiffre à la place de la date retournée par Calc.

Pour l'avoir en années: =(aujourdhui()-A2)/365

Mais le résultat n'est pas fiable. Il faut donc créer une formule plus longue qui calcule la différence entre deux dates en années, puis en mois.

Voici la formule de base:


La fonction ANNEES, spécifique à Calc, calcule la différence en années entre deux dates. Syntaxe: =ANNEES(Date_initiale;Date_de_fin;Type). Le type détermine le mode de calcul de la différence. Les valeurs possibles sont 0 pour calculer l'intervalle ou 1 pour les années.
La fonction NB.MOIS calcule la différence en mois entre deux dates. La fonction MOD (soit modulo) renvoie la différence après la division d'un nombre (ici 12).
Le "&" permet de concatèner une chaîne de caractères et un nombre dans une formule.

Mais pour le mot an, il faut prévoir le cas ou il y en a plusieurs. il faut donc ajouter une fonction SI qui vérifie si l'on met un "s" au mot an.

Voici la formule finale:


Pour vérifier si cela fonctionne, saisissez votre date de naissance dans la cellule A2.
Voir aussi la même astuce adaptée pour Microsoft Excel.

18 - Excel Expert - Utiliser une formule matricielle dans un tableur
Ce type de formule a la particularité de s'appliquer à une matrice, c'est-à-dire à une plage de cellule ayant plusieurs lignes et colonnes.

Une formule matricielle se crée de la même façon qu'une autre formule à la différence près que vous devez sélectionner plusieurs cellules pour la saisir et que vous devez appuyer sur les touches Ctrl + Shift + Entrée pour la valider. On travail donc sur plusieurs cellules à la fois (sur des plages de cellules). Certaines fonctions telle que la fonction FREQUENCE sont des fonctions matricielles.

Exemple: Calculer le nombre de personnes ayant entre tel ou tel âge.

Réalisez le tableau ci-contre puis sélectionnez la plage D2 à D5 (ci-contre en noir) et saisissez la fonction FREQUENCE comme ceci:

=frequence(A2:A10;C2:C5)
puis appuyer sur les touches Ctrl + Shift + Entrée pour la valider.

Vous devez obtenir ceci:
{=frequence(A2:A10;C2:C5)}

Pour modifier une formule matricielle, cliquez dans dans la barre des formules et appuyez sur Ctrl + / (Ctrl + slash). Une fois les changement effectués, appuyez sur Ctrl + Shift + Entrée pour la valider. Pour sortir sans modifier, appuyez sur la touche Echap (Esc).

17 - Excel Avancé - Comparatif entre Excel (PackOffice) et Calc (OpenOffice)
Nombreux sont les utilisateurs d'Excel qui migrent sous OpenOffice Calc (version 3.x).
Voici la liste des changements les plus importants que vous pourrez rencontrer:

Les menus
• Le menu Fichier / Mise en page est passé sous Calc dans le menu Format / Page
• Format / Mise en forme automatique d'Excel = le menu Format / AutoFormat de Calc.
• Les Zones d'impressions du menu Fichier sont, sous Calc, dans le menu Format.
Les feuilles
• Sortir d'un groupe de travail (multi-sélection de feuilles) utilisez à nouveau shift.
Les cellules
• Pour réaliser un retour à la ligne, sans changer de cellule on utilise la combinaison clavier Ctrl + Entrée (et non pas Alt + Entrée comme dans Excel).
• La poignée de recopie de Calc incrémente par défaut, donc s'il on veut recopier un texte ou un chiffre, on en saisie deux (dans deux cellules), on sélectionne ces deux cellules et on tire avec la poignée (l'inverse d'Excel en quelque sorte).
• La touche clavier F4 ouvre et ferme l'écran d'OpenOffice Base. Elle ne fait donc plus l'action de répétition comme dans Excel. Utilisez la combinaison Ctrl + Shift + Y (voir aussi l'astuce Tableau n° 32).
• Pour transformer une référence de cellule relative (par exemple, la cellule A22) en référence absolue ($A$22), il faut utiliser le raccourci clavier Shift + F4 (et non pas comme dans Excel la seule touche F4 qui sous Calc ouvre la listes des bases de données accessibles).
• Pour réaliser une série personalisée (comme l'incrémentation des mois à partir d'une date), on tire la poignée de recopie puis on ouvre le menu Edition/Remplir/Série.
• Les adresses de cellules entre les feuilles ne s'ecrivent plus avec un ! (point d'exclamation) mais avec un simple point. Par exemple la cellule A2 de la feuille janvier se note sous Calc: janvier.A2 (et non pas janvier!A2 comme sous Excel).
Les filtres
• Les filtres d'Excel s'appelent sous Calc, "Autofiltres", les filtres automatique, "standard" et les filtres élaborés, "filtre spécial". Les caractèrs génériques utilisés dans les filtres spécials de Calc sont le . (point) pour rechercher une lettre et le .* (point étoile) pour rechercher une chaîne de caractère. Ils remplacent les ? (point d'intérogation) et * (étoile) d'Excel. Par exemple, si vous voulez faire un filtre sur toutes les villes dont le nom commence par P, il faudra saisir: P.* (soit P point étoile) et utiliser le filtre spécial en cochant l'option 'caractèrs génériques'. Pour filtrer par le vide, on utilise l'expression ="=", mais attention; sans cocher l'option 'caractèrs génériques'.
Les outils
• L'outil "Tableau Croisé Dynamique" d'Excel s'appel, sous Calc, "Pilote de Données". Un double clic sur le bouton correspondant aux champs de données dans la fenêtre de l'assistant (clic droit Activer) permet de spécifier le type de calcul demandé.
• L'outil "Table de données" d'Excel s'appel dans Calc "Opération multiple". Attention: dans cet outils, Calc nous demande de spécifier la formule de base. La sélection ne doit donc pas la contenir sous Calc, ce qui était le cas sous Excel. La formule ne doit donc pas être sélectionnée avant de lancer cet outil (note: les fonctions matricielles fonctionnent très bien sous Calc).
Les boutons de commandes
• Trois nouveaux boutons dans Calc: L'export direct au format PDF, l'édition de fichier (pour basculer en mode lecture seul) et le bouton de Gallery pour obtenir des images.
Touches clavier
• Trois nouvelles touches: F4 pour afficher les bases de données enregistrées sous OpenOffice Base, F5 pour afficher la fenêtre du navigateur OpenOffice et enfin F11 pour afficher les styles.
• Pour le fun, voir l'oeuf de pâques de Calc: =GAME("StarWars")

16 - Excel Avancé - Formats personnalisés d'Excel
• Format 1: Saisir un chiffre avec un texte qui apparait automatiquement.
Par exemple, je saisi le chiffre 12 dans une cellule et 12 Tomates apparait automatiquement.
Afficher pour cela la boîte de dialogue Format/Cellule et sélectionnez la dernière catégorie (Personnalisé). Saisissez ce format:

# ###" Tomates"

Attention: pas d'espace après le dernier dièse!

Exemple qui affiche des nombres au format mètre carré (symbole m²) : # ###" m²"

Si vous voulez le même effet mais avec la saisie d'un texte. Par exemple, je saisie "Golden" dans une cellule et apparait "Pommes Golden": Saisissez ce format:

"Pommes "@

Note: le arobase (@) signifie ici qu'il s'agit de texte.

• Format 2: Même besoin que l'exemple "Format 1" mais prévoir que le mot "Tomates" qui apparait soit au singulier si je saisie le chiffre 1 ou 0 et au pluriel pour les autres cas.
Saisissez ce format conditionnel:

[<=1] 0" Tomate";[>1] 0" Tomates"

Note : Attention aux espaces. Le seul espace requit ici est juste avant le zéro (0).
Aucun autre espace ne doit être saisi.

Si vous voulez que le contenu de la cellule apparaisse en rouge lorsqu'il n'y a qu'une ou zéro tomate, saisissez ce format conditionnel:

[Rouge][<=1] 0" Tomate";[>1] 0" Tomates"

• Format 3: Vous voulez que les cellules saisies avec des chiffres soit remplie automatiquement avec un tiret (-). Saisissez ce format personnalisé:

0*-

Note: l'étoile (*) signifie ici : "remplir la cellule avec le signe suivant.


La fonction TEXTE
=TEXTE(A1;"mmmm")
La fonction TEXTE() convertit une valeur en texte selon un format spécifique.
Syntaxe: TEXTE(valeur;format_texte)

15 - Excel Avancé - Lignes en colonnes et vice versa
Vous avez récupérez des données en ligne (plage horizontale). Vous voulez les afficher en colonnes (plage verticale).

Pour cela, il vous faut utiliser la fonction TRANSPOSE.
Mais attention, cette fonction doit être utilisée sous la forme d'une Formule Matricielle qui renvoie plusieurs résultats suivant la plage sélectionné.

Par exemple, vos données sont en lignes, dans les trois premières cellules A1, B1 et C1 (soit la plage A1:C1).
Vous voulez les transposer en colonne, dans la plage A2:A4.

Commencez par saisir la fonction TRANSPOSE dans la celulle A2 comme ceci:

=TRANSPOSE($A$1:$C$1).

Sélectionnez ensuite la plage A2:A4 en commençant par la cellule contenant la formule, soit la colonne dans laquel vous voulez transposer vos données.
Appuyez sur F2, puis sur CTRL+MAJ+ENTREE.

14 - Excel Avancé - Déprotection Excel
Si vous avez oublié le mot de passe qui protège une feuille de calcul de votre classeur Microsoft Excel, vous pouvez utiliser cet outil. Il s'agit d'un exécutable qui crée un fichier Excel (deprotectionexcel.xls) qui contient une superbe macro créée par Bruno GUISTI qui efface le mot de passe d'origine et en crée un autre.

Une fois le fichier "DeprotectionExcel.exe" téléchargé, exécutez-le par un double clic. Cela crée un fichier "deprotectionexcel.xls" dans un sous-dossier "Excel" dans un dossier "Mes documents" qui se trouve à la racine de votre disque dur (C:).
Attention, il ne s'agit pas de votre classique dossier "Mes documents", mais bien d'un nouveau dossier accessible via le poste de travail!

Ouvrez le fichier "deprotectionexcel.xls" et suivez les instructions:
Cliquez sur le bouton gris en bas à droite de la feuille Excel et lancer la première recherche de mot de passe sur le fichier sur lequel vous vous trouvez ("deprotectionexcel.xls").

Ensuite vous pouvez lancer la recherche de mot de passe sur n'importe quel fichier Excel. Notez que cela ne marche que pour les mots de passe posés sur les feuilles Excel - et pas sur les classeurs Excel.

Attention: Easy-Micro ne serait, en aucun cas, responsable de l'utilisation d'un tel outil.
Téléchargable directement sur www.telecharger.com.

13 - Excel Avancé - Extraire une partie d'un texte d'une cellule dans une autre
Si vous avez le texte 75000 Paris dans la cellule A1 et que vous vouliez extraire le mot Paris dans la cellule B1, on peut utiliser trois fonctions simultanément:

La fonction STXT (sous texte, extrait une chaine d'une autre), TROUVE (trouve un caractère ou un espace dans une chaine) et NBCAR (nombre de caractères).

Exemple: =stxt(A1;trouve(" ";A1;1)+1;nbcar(A1)).

Le +1 est pour ne pas récupérer le caractère espace.

Syntaxe de la fonction STXT:
STXT(texte_cherché;début;fin)

Syntaxe de la fonction TROUVE:
TROUVE(texte_cherché;texte;rang)

Si, par contre, vous voulez retirer 75000 de la cellule A1:

Exemple: =stxt(A1;1;trouve(" ";A1;1)-1).

12 - Excel Avancé - Fusionner les textes de deux cellules dans une seule
Si les textes à fusionner se trouvent dans les cellules A1 et B1 et que vous vouliez fusionner ces deux textes dans la cellule C1, il suffit d'utiliser la fonction CONCATENER comme ceci:

=concatener(A1;" ";B1)

Les deux guillemets servent pour l'espace entre les mots.

11 - Excel Avancé - L'ancienneté au jour près avec Excel
Le problème : à partir de la date d'embauche, calculer l'ancienneté d'un employé au jour près.
Supposons que la date d'embauche soit dans la cellule A2. On pourrait effectuer une simple différence :
=aujourdhui()-A2. Cette simple formule renvoie le nombre de jours.
Pour l'avoir en années: =(aujourdhui()-A2)/365
Mais le résultat n'est pas fiable. Il faut donc créer une formule plus longue qui calcule la différence entre deux dates en années, puis en mois puis en jours.

Voici la formule de base:

Ne chercher pas dans la liste (fx) des fonctions, la fonction DATEDIF : elle n'est pas référencée!
Elle renvoie la différence entre deux dates selon la valeur du troisième argument :
"y" (pour années), "m" (pour mois), "d" (pour jours),
"ym" (pour nombre de mois après déduction des années),
"yd" (pour nombre de jours après déduction des années),
"md" (pour nombre de jours après déduction des années et des mois).

Le "&" permet d'écrire du texte dans une formule (concatene une chaîne de caractères et un nombre). Mais pour le mot an, il faut prévoir le cas ou il y en a plusieurs. il faut donc ajouter une fonction SI qui vérifie si l'on met un "s" au mot an. Voici la formule de base améliorée:


Mais pour avoir la différence entre les deux dates en années, puis en mois puis en jours, il faut aussi calculer les mois et les jours restant. Voici enfin la formule finale:


Pour vérifier si cela fonctionne, saisissez votre date de naissance dans la cellule A2.
Voir aussi la même astuce adaptée pour OpenOffice Calc.

10 - Excel Expert - Supprimer les doublons d'une colonne dans Excel
Exemple adapté à partir d'une astuce d'Eric Renaud (http://ericrenaud.free.fr).

Microsoft Excel ne sais pas supprimer les doublons dans une colonne. Il faut donc fabriquer une macro qui réalisera cette tâche. Ouvrez l'éditeur VBA d'Excel (ALT + F11), insérez un nouveau module (menu Insertion, Module) et copier/coller le programme ci-dessous dans l'écran blanc de droite.



Fermez l'éditeur d'Excel (menu Fichier, Fermer et retourner à Microsoft Excel).
Dans Excel, triez le contenu de votre tableau par ordre alphabétique sur la colonne A à l'aide du menu Données, Trier. Éxécutez ensuite la macro "supprdoublons" avec le racourci clavier ALT + F8. Recommencez le tri et la macro tant que vous avez des doublons à supprimer.

Cette macro efface les doublons d'une colonne sans supprimer les lignes.

09 - Excel Expert - Somme d'une colonne avec un nombre de lignes variable
Exemple adapté à partir d'une astuce d'Eric Renaud (http://ericrenaud.free.fr).

Qui n'a pas eu de problèmes lorsque l'on rajoute une ligne supplémentaire dans une somme? Si la ligne est rajoutée au milieu de la colonne : tous va bien! Mais si l'on rajoute une ligne en bas de notre colonne, cette nouvelle ligne n'est pas prise en compte par la somme.
La solution (proposée par Eric Renaud) est d'utiliser les fonctions DECALER et NB dans la fonction SOMME.
Exemple pour une somme sur la colonne A ou les valeurs commencent à la ligne 2 :

=SOMME(DECALER($A$1;NB(A:A);;-NB(A:A)-1))

Attention, l'exemple ci-dessus ne doit pas être taper dans la colonne A sous peine de référence circulaire.

08 - Excel Expert - Un bip d'alerte sur mesure sous Excel
Exemple adapté à partir du magazine "Windows News" - Mai 2003.

Excel ne dispose d'aucune fonction de Bip. Mais VBA propose l'instruction Beep qui émet un son court standard. Il suffit donc de créer une fonction VBA. Ouvrez l'éditeur VBA (ALT + F11) et entrez dans un module (menu Insertion, Module) la fonction ci-dessous.

Dans la feuille de calcul, voici la formule à taper pour surveiller la cellule A1 : =si(A1>100;bip(3);""). Entrez-la dans n'importe quelle cellule vide: elle n'affiche rien du tout mais produit trois bips dès que la valeur de A1 dépasse 100.

07 - Excel Expert - Barre d'outils perso à affichage automatique
Exemple adapté à partir du magazine "Windows News" - Septembre 2004.

Pour qu'une barre d'outils personnelle s'affiche uniquement lorsqu'un classeur précis est ouvert il faut, dans un premier temps créer la barre d'outils en passant par le menu Affichage, Barre d'outils, Personnaliser.

Cliquez sur le bouton Nouvelle et nommez-la, par exemple, mabarre. Passez ensuite à l'onglet Commandes pour y insérer les commandes ou les macros de votre choix. Choisissez uniquement les macros du classeur actif. Terminez en cliquant sur Fermer et éteignez ensuite la nouvelle barre d'outils.

Ouvrez maintenant l'éditeur VBA en tapant ALT+F11. Dans le volet des projets, sélectionnez celui qui correspond au classeur actif et faites Insertion, Module. Dans le volet de droite, copiez/collez les deux petites routines ci-dessous:

06 - Excel Expert - Une couleur conditionnelle sous Microsoft Excel 2003
Vous voulez mettre en valeur les noms de vos clients qui ont réglés vos factures. Pour cela il faut attribuer un format conditionnel à la plage de cellules qui contiend la liste des noms (ici la colonne A). Sélectionnez le premier nom (A2 dans l'exemple) et choisissez la commande Format, Mise ne forme conditionnelle.

Dans la liste Condition, sélectionnez La formule est. Dans la zone voisine, entrez la formule: =C2<>"" : le format sera appliqué si la cellule C2 n'est pas vide.
Cliquez sur le bouton Format et définissez les paramètres de mise en formes à appliquer (Motifs vert dans l'exemple). Validez en cliquant sur OK.

Il ne reste plus qu'à copier le format sur les autres cellules de la colonne A. Veillez à ce que la cellule A2 soit bien sélectionnée et cliquez sur le bouton Pinceau. Peignez les autres cellules de la première colonne.

05 - Excel Expert - Masquer les zéros dans un champ de cellules d'Excel
Avec Microsoft Excel, vous pouvez obtenir que rien ne soit affiché dans les cellules contenant zéro. Il suffit de dérouler le menu OUTILS, OPTIONS, AFFICHAGE, et de désélectionner VALEURS ZERO dans la zone FENETRE avant de valider par OK. Mais cette mesure à un inconvénient: elle s'applique à tous les classeurs ouverts.

Heureusement, vous pouvez la restreindre à une plage de cellules.
Pour cela, sélectionnez cette plage et déroulez FORMAT, CELLULES.
Activez l'onglet NOMBRE.
Dans la zone CATEGORIE, cliquez sur PERSONNALISEE et, dans le champ TYPE, tapez strandard;standard;"" puis validez par OK. Les zéros disparaissent.

Les trois termes, séparés par des points-virgules, que vous avez tapés décrivent la façon dont Excel doit afficher respectivement les valeurs positives, négatives et nulles. Pour ces dernières, le nombre sera donc affiché sous la forme d'une chaîne de caractères vide (deux guillemets consécutifs) et rien n'apparaîtra dans la cellule.

04 - Excel Avancé - Pourcentage d'un total dans un tableur
Vous voulez connaître le poucentage que représente chaque catégorie d'un tableau par rapport à l'ensemble des données.

Dans l'exemple illustré ci-dessous, valable sous Microsoft Excel ou Calc d'OpenOffice.org, les chiffres occupent les cellules B2 à B5.

En C2, tapez la formule :
=B2/SOMME($B$2:$B$5).
N'oubliez pas les $ : ils spécifient que la plage B2:B5 est une référence fixe (absolue) qui ne sera pas modifiée quand vous dupliquerez la formule.
Recopiez la cellule C2 sur le champ C3:C5. Sélectionnez le champ C2:C5 et cliquez sur le bouton % de la barre d'outils Mise en forme.

03 - Excel Expert - Protéger ses Macros
Si vous voulez protéger par un mot de passe les macros d'un fichier, ouvrez la fenêtre de l'éditeur VBA (Alt + F11) et sélectionnez le nom du fichier dans le fenêtre Projet - VBAProject (en haut à gauche de l'écran).
Si vous ne voyez pas la fenêtre de l'explorateur de projet, affichez-là par le menu Affichage, Explorateur de projets.
Ouvrez le menu Outils, Propriétés de VBAProject.... Dans l'onglet Protection, cochez Verrouiller le projet pour l'affichage et entrez le mot de passe. Enregistrez et fermez le fichier.

02 - Excel Base - Dupliquer rapidement une feuille de calcul
Il existe de nombreuses façons pour dupliquer une feuille de calcul comme la technique du clique droit de souris sur l'onglet de la feuille à dupliquer avec une sélection du menu Déplacer ou copier puis une coche dans l'option Créer une copie.
Mais tous cela reste bien long à réaliser. Le plus simple (et le plus rapide) est de cliquer sur la feuille à dupliquer, puis, toute en maintenant la touche Ctrl (Contrôle) du clavier, faire un déplacement latérale avec la souris.

01 - Excel Expert - Appliquer un format à intervalles réguliers
Exemple adapté à partir du magazine "L'Ordinateur Individuel" (Mai 2005).

Pour créer un petit planning, vous avez inscrit dans la colonne d'un tableau une série de dates commençant par un dimanche. Vous voulez attribuer un format particulier à toutes les cellules contenant un dimanche, c'est-à-dire une cellule sur sept. Or Excel ne sait pas faire ce genre d'opération.

Dans la photo d'écran ci-contre, le calendrier s'étend des cellules A2 à A2. A l'aide des différentes options du menu FORMAT, CELLULES (police, couleurs...), donnez à la cellule A2 l'aspect que doivent prendre les dimanches.

Ouvrez ensuite l'éditeur VBA en tapant ALT+F11 ou par le menu Outils, Macro, Visual Basic Editor. Déroulez le menu Insertion, module.

Recopier (ou copier/coller) le code ci-contre dans l'éditeur VBA d'Excel puis déroulez le menu Fichier, Fermer et retournez à Microsoft Excel. Sélectionnez ensuite le champ A2:A20. Tapez ALT+F8 pour ouvrir la fenêtre Macros. Sélectionnez la macro MARQUECELL et cliquez sur EXECUTER. Dans la fenêtre qui apparaît, tapez 7 car vous voulez faire ressortir une cellule sur 7 (la macro fonctionne aussi avec d'autre valeurs). Validez par OK. Les dimanches (soit une cellule sur 7) s'affichent désormais dans un format visible. Sympas, non?

Option Explicit Sub MARQUECELL() Dim c,i,p Selection.Item(1,1).Copy i = 0 p = InputBox("Marquer une cellule sur:") p = CInt(p) For Each c In Selection If i Mod p = 0 Then c.PasteSpecial xlPasteFormats End If i = i + 1 Next c Application.CutCopyMode = false End Sub