Le cœur d'Excel avancé : organiser, analyser et sécuriser des données. Une base de données Excel est une liste : une ligne d'en-têtes, une ligne par enregistrement, aucune ligne ni colonne vide, pas de cellules fusionnées.
| Fonction | Rôle | Exemple |
|---|---|---|
SOMME.SI / SOMME.SI.ENS | Somme avec un ou plusieurs critères | =SOMME.SI.ENS(F:F;D:D;"Rabat";A:A;2020) |
NB.SI / NB.SI.ENS | Compter avec un ou plusieurs critères | =NB.SI.ENS(E:E;"Rabat";A:A;"Appt";D:D;"oui") |
MOYENNE.SI.ENS | Moyenne avec critères | |
BDSOMME, BDMAX, BDMIN, BDNB, BDMOYENNE | Fonctions de base de données : (base ; champ ; zone de critères) | =BDSOMME(A1:C11;"CA";E1:E2) avec E1 = « Magasin », E2 = « Magasin 2 » |
RECHERCHEV / RECHERCHEX | Chercher une valeur dans une table | =RECHERCHEX(C2;Client!A:A;Client!D:D) |
© TSC COMMERCE – OTHMAN ZT · Cours protégé, reproduction interdite
Tableau de synthèse créé à partir d'une liste, qui regroupe et calcule (somme, nombre, moyenne) les données selon les champs placés en lignes, colonnes, valeurs et filtres. Il se réorganise par glisser-déposer et se met à jour par Actualiser.
| Outil | Méthode |
|---|---|
| Validation des données | Données › Validation des données › Autoriser : Nombre entier, entre 2017 et 2022 ; onglet Message de saisie et Alerte d'erreur (style Arrêt, message) |
| Verrouiller des formules | Sélectionner tout › Format de cellule › Protection › décocher Verrouillée ; sélectionner la colonne de formules › cocher Verrouillée ; Révision › Protéger la feuille |
| Mot de passe du classeur | Fichier › Informations › Protéger le classeur › Chiffrer avec mot de passe |
| Partage et suivi | Enregistrer sur OneDrive › Partager ; co-édition et historique des versions ; commentaires (Révision › Nouveau commentaire) |
| Exporter / importer du texte | Fichier › Enregistrer sous › Texte (séparateur : tabulation) ou CSV ; Données › À partir d'un fichier texte/CSV |
| Publier en HTML | Fichier › Enregistrer sous › Page web › Publier ; option « Republier automatiquement à chaque enregistrement » |
© TSC COMMERCE – OTHMAN ZT · Cours protégé, reproduction interdite
CA par année : Magasin 1 : 2010 : 312 645,67 ; 2011 : 431 211,40 ; 2012 : 215 931,60 ; 2013 : 3 225 800,33 ; 2014 : 2 562 867,45. Magasin 2 : 215 931,60 ; 2 562 867,45 ; 115 931,60 ; 2 225 800,33 ; 3 225 800,33. 1) Placez chaque magasin dans une feuille (Magasin1, Magasin2) et consolidez la somme. 2) Avec un tableau Magasin / Année / CA, utilisez les fonctions BD pour trouver le CA le plus élevé, le CA minimal du magasin 1 et le total du magasin 2. 3) Dans le tableau des salaires, triez par immatricule croissant, transposez-le dans une autre feuille et filtrez les salaires supérieurs à 8 000.
1) Nouvelle feuille › Données › Consolider › Somme › références Magasin1!A1:F2 et Magasin2!A1:F2 › « Ligne du haut » et « Colonne de gauche ».
| Année | 2010 | 2011 | 2012 | 2013 | 2014 |
|---|---|---|---|---|---|
| CA consolidé | 528 577,27 | 2 994 078,85 | 331 863,20 | 5 451 600,66 | 5 788 667,78 |
2) Liste A1:C11 (Magasin, Année, CA) ; critères : E1 « Magasin », E2 vide pour tous, « Magasin 1 » ou « Magasin 2 ».
| Question | Formule | Résultat |
|---|---|---|
| CA le plus élevé | =BDMAX(A1:C11;"CA";E1:E2) (E2 vide) | 3 225 800,33 |
| CA minimal du magasin 1 | =BDMIN(A1:C11;"CA";G1:G2) (G2 = Magasin 1) | 215 931,60 |
| Total du magasin 2 | =BDSOMME(A1:C11;"CA";H1:H2) (H2 = Magasin 2) | 8 346 331,31 |
3) Données › Trier › Immatricule › Du plus petit au plus grand ; Copier › nouvelle feuille › Collage spécial › Transposé ; Données › Filtrer › Salaire › Filtres numériques › Supérieur à 8 000.
1) Dans une liste de ventes (ID_client, Produit, Prix, Qté, Total), insérez des sous-totaux par client. 2) Un tableau de stocks par produit et par entrepôt pour quatre villes : groupez les colonnes par ville et les lignes par famille de produits. 3) Dans une liste de biens immobiliers (Nature, Superficie, Jardin, Garage, Ville, Prix…), comptez sans filtre ni tri le nombre d'appartements et de maisons avec garage, par ville.
=NB.SI.ENS($E$2:$E$200;$A2;$A$2:$A$200;B$1;$D$2:$D$200;"oui") en adaptant les colonnes (E = Ville, A = Nature, D = Garage), puis recopier. Autre solution : un TCD (Ville en lignes, Nature en colonnes, Garage en filtre = oui, Nombre de Nature en valeurs).1) Avec les tables Client (ID_client, Nom, Adresse, Ville) et Commande (Num cde, Année, ID_client, Produit, Prix, Qté, Total), affichez le total des ventes par année et par ville, filtrable par nom de client. 2) Créez un graphique croisé dynamique filtré par ville. 3) Dans un tableau Année / ID_client, n'acceptez que des années de 2017 à 2022 et des ID supérieurs à 9 000, avec un message d'erreur.
1) Protégez un classeur par mot de passe puis ôtez la protection. 2) Calculez Total = Prix × Qté et verrouillez la colonne Total. 3) Partagez le classeur, suivez les modifications quand « Produit 21 » devient « ProduitAA ». 4) Exportez une feuille en fichier texte et réimportez-la. 5) Publiez la feuille en HTML avec mise à jour automatique.
=B2*C2 recopié ; Ctrl + A › Format de cellule › Protection › décocher Verrouillée ; sélectionner D:D › cocher Verrouillée ; Révision › Protéger la feuille (avec ou sans mot de passe).Les vidéos de ce chapitre seront bientôt disponibles.