Forums d'entraide informatique - Astuces - Conseils

1. Automatiser
1.1  Rechercher une valeur dans une table
=RECHERCHE(valeur cherchée;table)
impératif pour que la fonction rechercher fonctionne :
Réf. Triées dans l'ordre croissant
Si une réf n'existe pas elle prend la réf la plus proche donc la table de désignation doit avoir des réf de 1
à 20 pour cet exemple.
=RECHERCHEV(valeur cherchée;table;n°de colonne;valeur proche)
valeur cherchée = n° de colonne = 1, 2 ou 3 valeur proche = vrai (approximativement) ou faux (plus
précis, plus exacte)
Grâce à la RECHERCHEV on a plus les limites de la fonction RECHERCHE
1.2  Entrer une fonction simple : fonction SI
=SI(condition;vrai;faux)
1.3  créer une condition imbriquée
=SI(condition1;vrai;si(condtion2;vrai;faux))
1.4  Eviter le message d'erreur #NA lorsqu'une référence n'existe pas
Fonction : ESTNA
Syntaxe :=ESTNA(valeur)
Exemple :
Si(ref=0;"";si(estna(recherchev(ref;design;2;faux));"cette désignation n'existe
pas";recherchev(ref;design;2;faux)))
1.5  Eviter le message d'erreur #VALEUR lorsqu'une référence n'existe pas
Fonction : ESTERR
Syntaxe :=ESTERR(valeur)
Exemple : =si(esterr(qte*pu);"";qte*pu)
1.6  protéger une feuille de calcul
sélectionner les colonnes dans lesquelles vous voulez écrire
Format – cellule – Protection – déverrouillé
Outil – protection – protéger feuilleD:\12excel les outils avancés .doc imprimé le 04/09/00 à 09:37
Reproduction non contrôlée DOCUMENT NON PROTEGE page 1 / 10
Ce document est la propriété d’EADS France; il ne peut être communiqué à des tiers
et/ou reproduit sans
l'autorisation préalable écrite d’EADS France et son contenu ne peut être divulgué. © -
EADS France - 2000
Modèle SQ-CTI-MS GDE-MS-V1 Ind. B du 10/07/00
Centre Informatique
Montmorency Suresnes  GDF 07.12 Ind. A
GUIDE
DOMAINE DE PRESTATION :
MISE A DISP. ET SUP. DE POSTES ET STATIONS DE TRAVAIL ET MOYENS ASSOC.
OBJET DU GUIDE :
EXCEL - LES OUTILS AVANCÉS
Rédigé le 7/10/98 Approuvé le 23/10/98
Fabienne Le Thiec GDF 07.12 Ind. A
D:\12excel les outils avancés .doc imprimé le 04/09/00 à 09:37
Reproduction non contrôlée DOCUMENT NON PROTE GE page 2 / 10
Ce document est la propriété d’EADS France; il ne peut être communiqué à des tiers et/ou reproduit
sans
l'autorisation préalable écrite d’EADS France et son contenu ne peut être divulgué. © - EADS
France - 2000
Modèle SQ-CTI-MS GDE-MS-V1 Ind. B du 10/07/00
Table des Matières
1. AUTOMATISER........................................................................................................................................................................ 3
1.1 RECHERCHER UNE VALEUR DANS UNE TABLE ......................................................................................................................3
1.2 ENTRER UNE FONCTION SIMPLE : FONCTION SI ....................................................................................................................3
1.3 CRÉER UNE CONDITION IMBRIQUÉE........................................................................................................................................3
1.4 EVITER LE MESSAGE D'ERREUR #NA LORSQU'UNE RÉFÉRENCE N'EXISTE PAS.................................................................3
1.5 EVITER LE MESSAGE D'ERREUR #VALEUR LORSQU'UNE RÉFÉRENCE N'EXISTE PAS.....................................................3
1.6 PROTÉGER UNE FEUILLE DE CALCUL ......................................................................................................................................3
2. CONSOLIDER............................................................................................................................................................................ 4
2.1 CONSOLIDER SANS LIAISON.....................................................................................................................................................4
2.2 CONSOLIDER AVEC LIAISON....................................................................................................................................................4
2.3 TRIER UN TABLEAU...................................................................................................................................................................4
2.4 EFFACER LE PLAN .....................................................................................................................................................................4
3. CRÉER UNE MATRICE......................................................................................................................................................... 5
3.1 OBTENIR LES PARENTHÈSE MATRICIELLES............................................................................................................................5
3.2 CALCULER LE CUMUL PAR POSTE...........................................................................................................................................5
3.3 EXTRAIRE LE MOIS D'UNE DATE ..............................................................................................................................................5
3.4 CALCULER LE CUMUL PAR MOIS.............................................................................................................................................5
4. CALCULER AVEC DES  DATES ET DES HEURES ..................................................................................................... 6
4.1 FORMAT......................................................................................................................................................................................6
4.2 CALCULER ENTRE .....................................................................................................................................................................6
4.3 DATE DU JOUR...........................................................................................................................................................................6
5. LES TABLEAUX CROISÉS DYNAMIQUES................................................................................................................... 7
5.1 CRÉER UN TABLEAU DYNAMIQUE...........................................................................................................................................7
5.2 MODIFIER LE TABLEAU CROISÉ DYNAMIQUE ........................................................................................................................7
5.3 MODIFIER UN CHAMP DYNAMIQUE.........................................................................................................................................7
5.4 COPIER TOUS LES ÉLÉMENTS DE LA RUBRIQUE "PAGE" SUR UNE PAGE DIFFÉRENTE......................................................7
5.5 ACTUALISER LE TABLEAU CROISÉ DYNAMIQUE...................................................................................................................7
5.6 OBTENIR LE DÉTAIL D'UN POSTE.............................................................................................................................................7
5.7 MASQUER LE DÉTAIL D'UN POSTE...........................................................................................................................................7
5.8 GROUPER DES POSTE................................................................................................................................................................7
5.9 DISSOCIER DES POSTES.............................................................................................................................................................7
6. RÉSOUDRE UNE ÉQUATION............................................................................................................................................. 8
6.1 VALEUR CIBLE...........................................................................................................................................................................8
7. LE SOLVEUR............................................................................................................................................................................. 9
7.1 ENREGISTRER LE RÉSULTAT TROUVER PAR LE SOLVEUR EN TANT QUE SCÉNARIO.........................................................9
8. FAIRE UNE SYNTHÈSE DES DIFFÉRENTS SCÉNARIOS ....................................................................................10
Elaboration et automatisation d'une facture
Gestion des messages d'erreur
Récapitulatif de plusieurs tableaux
Calculs sur dates
Création et utilisation d'une matrice
Présentation et synthèse de tableaux
Résolution d'équations à 1 ou plusieurs inconnues GDF 07.12 Ind. A
D:\12excel les outils avancés .doc imprimé le 04/09/00 à 09:37
Reproduction non contrôlée DOCUMENT NON PROTE GE page 4 / 10
Ce document est la propriété d’EADS France; il ne peut être communiqué à des tiers et/ou reproduit
sans
l'autorisation préalable écrite d’EADS France et son contenu ne peut être divulgué. © - EADS
France - 2000
Modèle SQ-CTI-MS GDE-MS-V1 Ind. B du 10/07/00
2. Consolider
2.1  Consolider sans liaison
Activer la première cellule de destination de la consolidation
Donnée – Consolider
Choisir la fonction de calcul à utiliser
Accéder à la zone référence
Pour chaque feuille à consolider :
Activer la,
Sélectionner les cellules concernées
Cliquer sur le bouton ajouter
Au besoin, préciser si vous avez englobé dans la sélection les étiquettes de données
NB : on consolider dans un même classeur ou bien à partir de plusieurs classeurs
2.2  Consolider avec liaison
Procéder comme ci-dessus puis cocher la cas "lier aux données"
2.3  Trier un tableau
Se positionner dans le tableau à trier
Donnée
Trier
Ou
Icône AZ ZA
2.4  Effacer le plan
Données
Grouper et créer un plan
Effacer le plan
NB reconsolidation impossible
Les modifications faites dans le détail du plan sont prises en compte GDF 07.12 Ind. A
D:\12excel les outils avancés .doc imprimé le 04/09/00 à 09:37
Reproduction non contrôlée DOCUMENT NON PROTE GE page 5 / 10
Ce document est la propriété d’EADS France; il ne peut être communiqué à des tiers et/ou reproduit
sans
l'autorisation préalable écrite d’EADS France et son contenu ne peut être divulgué. © - EADS
France - 2000
Modèle SQ-CTI-MS GDE-MS-V1 Ind. B du 10/07/00
3. Créer une matrice
3.1  Obtenir les parenthèse matricielles
CTRL + SHIFT + ENTREE
Permet d'additionner des cellules de même catégorie ou même mois.
3.2  Calculer le cumul par poste
{=somme(si(catégorie=E5;montant;0))}
3.3  extraire le mois d'une date
Fonction : mois{}
Syntaxe : mois{valeur}
3.4  Calculer le cumul par mois
{=somme(si(categorie=E5;si(mois(date)=$G$4;montant)))}
permet d'additionner des chiffres de même catégorie ou même date. GDF 07.12 Ind. A
D:\12excel les outils avancés .doc imprimé le 04/09/00 à 09:37
Reproduction non contrôlée DOCUMENT NON PROTE GE page 6 / 10
Ce document est la propriété d’EADS France; il ne peut être communiqué à des tiers et/ou reproduit
sans
l'autorisation préalable écrite d’EADS France et son contenu ne peut être divulgué. © - EADS
France - 2000
Modèle SQ-CTI-MS GDE-MS-V1 Ind. B du 10/07/00
4. Calculer avec des dates et des heures
4.1  Format
HH:MMHeures minutes
4.2  Calculer entre
Heures [H]:MM:SS
Minutes [MM]:SS
4.3  Date du jour
=aujourd'hui
ou
CTRL + · GDF 07.12 Ind. A
D:\12excel les outils avancés .doc imprimé le 04/09/00 à 09:37
Reproduction non contrôlée DOCUMENT NON PROTE GE page 7 / 10
Ce document est la propriété d’EADS France; il ne peut être communiqué à des tiers et/ou reproduit
sans
l'autorisation préalable écrite d’EADS France et son contenu ne peut être divulgué. © - EADS
France - 2000
Modèle SQ-CTI-MS GDE-MS-V1 Ind. B du 10/07/00
5. Les tableaux croisés dynamiques
5.1  Créer un tableau dynamique
Données
Tableau croisé dynamique
Suivre l'assistant
5.2  Modifier le tableau croisé dynamique
Se positionner dans le tableau croisé dynamique
Données
Tableau croisé dynamique ou icône
5.3  Modifier un champ dynamique
Données
Champ dynamique ou icône
5.4  Copier tous les éléments de la rubrique "page" sur une page différente
Tout afficher
5.5  Actualiser le tableau croisé dynamique
Données
Actualiser les données (après modification du tableau principal)
5.6  Obtenir le détail d'un poste
Se positionner sur le poste
5.7  Masquer le détail d'un poste
5.8  Grouper des poste
Sélectionner les postes à groupe
5.9  Dissocier des postes GDF 07.12 Ind. A
D:\12excel les outils avancés .doc imprimé le 04/09/00 à 09:37
Reproduction non contrôlée DOCUMENT NON PROTE GE page 8 / 10
Ce document est la propriété d’EADS France; il ne peut être communiqué à des tiers et/ou reproduit
sans
l'autorisation préalable écrite d’EADS France et son contenu ne peut être divulgué. © - EADS
France - 2000
Modèle SQ-CTI-MS GDE-MS-V1 Ind. B du 10/07/00
6. Résoudre une équation
6.1  Valeur cible
Outils
Valeur cible
·  Cellule à définir
·  Valeur à atteindre
·  Cellule à modifier GDF 07.12 Ind. A
D:\12excel les outils avancés .doc imprimé le 04/09/00 à 09:37
Reproduction non contrôlée DOCUMENT NON PROTE GE page 9 / 10
Ce document est la propriété d’EADS France; il ne peut être communiqué à des tiers et/ou reproduit
sans
l'autorisation préalable écrite d’EADS France et son contenu ne peut être divulgué. © - EADS
France - 2000
Modèle SQ-CTI-MS GDE-MS-V1 Ind. B du 10/07/00
7. Le solveur
Outils
Solveur
·  Cellule à définir
·  Valeur à atteindre
·  Cellule à modifier
·  Contraintes à ajouter
7.1  Enregistrer le résultat trouver par le solveur en tant que scénario
Outils
Solveur
Résoudre
Enregistrer le scénario GDF 07.12 Ind. A
D:\12excel les outils avancés .doc imprimé le 04/09/00 à 09:37
Reproduction non contrôlée DOCUMENT NON PROTE GE page 10 / 10
Ce document est la propriété d’EADS France; il ne peut être communiqué à des tiers et/ou reproduit
sans
l'autorisation préalable écrite d’EADS France et son contenu ne peut être divulgué. © - EADS
France - 2000
Modèle SQ-CTI-MS GDE-MS-V1 Ind. B du 10/07/00
8. Faire une synthèse des différents scénarios
Outils
Gestionnaire de scénarios
Synthèse