Tableaux de bord de gestion.

Des développements d'applications sur mesure pour la gestion de votre entreprise sur Excel-Dev.fr.

Planning gestion des absences.

Besoin de planning pour gérer les absences de vos collaborateurs ou salariés?

Excel-pratique des applications Excel professionnelles.

Vous cherchez des applications professionnelles, visitez le site Excel-Pratique.com

Excel-ligue1 des applications sportives pour Excel.

Retrouvez toutes les applications Excel dédiées au sport, la ligue1, la Ligue des Champions et bien d'autres, sur la page sportive du BloG.

Fichier des Fonctions et Astuces

Retrouvez su votre PC toutes les fonctions et astuces développées sur le BloG, télechargez le fichier des Fonctions.

lundi 29 décembre 2014

Fichier des fonctions Excel.


Le fichier des fonctions Excel contient toutes les formules abordées sur le Blog et il sera actualisé dès qu'une nouvelle leçon sur les fonctions du tableur sera traitée, ceci afin d'avoir constamment un fichier le plus complet possible.









Vous trouverez de nombreux exemples et astuces, afin de vous aider à maîtriser EXCEL.


Une table des matières avec des liens hypertexte, vous permettra de vous déplacer et d'accéder aisément à l'exemple que vous avez besoin.

Alors n'hésitez pas à le télécharger, c’est totalement gratuit.

Je vous rappelle que vous pouvez me demander d’aborder un point particulier d’Excel, en utilisant la  page demander un sujet.

En attendant de vous compter parmi les fidèles du Blog, je vous souhaite une très bonne année 2015.




Téléchargez le fichier.


A très bientôt sur le Blog.

Maîtriser Excel, c'est facile !!!

En discuter sur le Forum?

Print Friendly and PDF


Restez informé, abonnez-vous à la newsletter, entrez votre adresse mail:


Delivered by FeedBurner

vendredi 26 décembre 2014

Excel - Calculer les Jours Ouvrés entre deux dates.

Dans la continuité de mon article sur les 50 fonctions Excel de base, nous allons voir aujourd'hui les formules NB.JOURS.OUVRES et NB.JOURS.OUVRES.INTL.


Ces deux fonctions vous permettent de calculer le nombre de jours entre deux dates, mais la seconde est utile pour définir des jours de Week-End différents du samedi et du dimanche.

NB.JOURS.OUVRES:

Indique le nombre de jours ouvrés entre deux dates, le nombre de jours de travail, samedi inclus en tenant compte des jours fériés ou pas. Jours_fériés est une matrice qui contient des dates à considérer comme des jours fériés.

Syntaxe =NB.JOURS.OUVRES(date_début;date_fin;jours_fériés)

date_début     est une date qui représente la date de début.
date_fin     est une date qui représente la date de fin.
jours_fériés     représente une plage facultative d'une ou de plusieurs dates à exclure du calendrier des jours de travail, comme les jours fériés ou d'autres jours contractuellement chômés. La liste peut être soit une plage de cellules contenant les dates, soit une constante matricielle des numéros de série qui représentent les dates.

Exemple :
Si nous avons les deux dates suivantes en F21 et F22 et sur la feuille JOURS FERIES en G10 à G22 les dates à considérer comme jours fériés:
21/01/2014
31/08/2014
=NB.JOURS.OUVRES(F21;F22;) affichera 159
=NB.JOURS.OUVRES(F21;F22;'JOURS FERIES'!G10:G22) affichera 152
Dans ce cas précis les jours de WE, même s'ils n'apparaissent pas dans la fonction sont le samedi et le dimanche. Nous allons voir avec NB.JOURS.OUVRES.INTL comment les modifier.

NB.JOURS.OUVRES.INTL:

Indique le nombre de jours ouvrés entiers compris entre deux dates à l’aide de paramètres identifiant les jours du week-end et leur nombre. Les jours du week-end et ceux qui sont désignés comme des jours fériés ne sont pas considérés comme des jours ouvrés.

Non compatible avec des versions d’Excel antérieures à 2010.

Syntaxe: NB.JOURS.OUVRES.INTL(date_début, date_fin, [weekend], [jours_fériés])

date_début     est une date qui représente la date de début.
date_fin     est une date qui représente la date de fin.
week_end    Facultatif. Indique les jours de la semaine qui représentent les jours du week-end et qui ne sont pas compris dans le nombre de jours ouvrés entiers inclus entre date_début et date_fin. L’argument week-end est un numéro de week-end ou une chaîne qui indique la date du week-end.
jours_fériés     représente une plage facultative d'une ou de plusieurs dates à exclure du calendrier des jours de travail, comme les jours fériés ou d'autres jours contractuellement chômés. La liste peut être soit une plage de cellules contenant les dates, soit une constante matricielle des numéros de série qui représentent les dates.
                                                   
Si nous reprenons notre exemple précédent,
=NB.JOURS.OUVRES.INTL(F21;F22;1;) affichera toujours 159.
=NB.JOURS.OUVRES.INTL(F21;F22;1;'JOURS FERIES'!G10:G22) affichera 152.

Utilisation la fonction =NB.JOURS.OUVRES

Vous trouverez sur la page de Téléchargements, un fichier exemple relatif à ces fonctions, je vous recommande de le télécharger pour mieux comprendre cette leçon. Ce fichier contient également une matrice vous permettant de calculer automatiquement les jours fériés.

Maîtriser Excel, c'est facile !!!

En discuter sur le Forum?

Print Friendly and PDF


Restez informé, abonnez-vous à la newsletter, entrez votre adresse mail:


Delivered by FeedBurner

mercredi 24 décembre 2014

Excel - Joyeux Noël 2014

Le Blog Excel-Dev vous souhaite un joyeux Noël et un excellent réveillon !!!!

Joyeux Noël à tous et à toutes !!!!


Maîtriser Excel, c'est facile !!!

En discuter sur le Forum?

Print Friendly and PDF

Cet article vous a intéressé, merci de le recommander avec un Google +1 et de le partager sur Facebook, Twitter, Viadeo ou Linkedin, en utilisant les liens mis à votre disposition.

Pour suivre nos prochains articles abonnez-vous au Blog ou inscrivez-vous au flux RSS.

En savoir plus, consulter l'actualité de Excel-Dev sur :
- Facebook :Excel-Ligue1, Excel-Dev
- Twitter : Le blog Excel-Dev

Restez informé, abonnez-vous à la newsletter, entrez votre adresse mail:


Delivered by FeedBurner

dimanche 21 décembre 2014

Excel - Fonction DECALER

Formule Excel - DECALER - Fonction RECHERCHE et REFERENCE


A quoi peut donc bien servir la formule DECALER?

La fonction DECALER n'a pas pour rôle de décaler physiquement les cellules dans la feuille ni de modifier la sélection. Elle renvoie simplement une référence. La fonction DECALER peut être utilisée avec toutes les fonctions exigeant une référence comme argument.


Cette fonction Excel, renvoie une référence à une plage qui correspond à un nombre déterminé de lignes et de colonnes d'une cellule ou plage de cellules. La référence qui est renvoyée peut être une cellule unique ou une plage de cellules. Vous pouvez spécifier le nombre de lignes et de colonnes à renvoyer.

Syntaxe: DECALER(réf;lignes;colonnes;hauteur;largeur)

On utilise la Fonction d'Excel DECALER pour définir des plages de cellules de façon dynamique.

Vous avez, dans vos travaux Excel, surement déjà défini des plages nommées afin de les réutiliser dans des validations de données ou autres. L'inconvénient c'est que vos plages sont fixes et difficiles à manipuler. DECALER va vous permettre d'utiliser ces plages même si vous rajoutez de nouveaux éléments par la suite.

Prenons l'exemple d'une liste de noms que nous réutilisons dans une validation de données, comme dans la feuille de temps.

Sur la Feuil1, nous avons en A1 à A7 la liste suivante:



Nous voulons utiliser cette liste par la suite. Nous pourrions la nommer directement en sélectionnant A2 à A7 et définir le nom PRENOMS. Cette façon de procéder nous limite, puisque si nous ajoutons un prénom à cette liste, il nous faudra modifier l'étendue de la plage nommée. Nous allons voir qu'avec DECALER ce problème n'existe plus.

Nous allons utiliser la formule suivante =DECALER(Feuil1!$A$1;1;0;NBVAL(Feuil1!$A:$A)-1;1), qui se traduit ainsi, on démarre de A1, on descend d'une ligne, on reste dans la même colonne, on évalue le nombre de cellules de la colonne A qui contiennent une valeur « NBVAL » et on retire la ligne d'entête, avec une largeur d'une colonne.

Si nous tapons cette formule dans la feuille nous obtenons #VALEUR, Excel n'est pas capable d'afficher le résultat puisqu'il s'agit d'une plage de cellules.


Nous allons donc utiliser cette formule avec un NOM. Dans le Menu FORMULE/GESTIONNAIRE DE NOMS nous créons un NOUVEAU NOM. Dans la boite de dialogue nous entrons Prénoms et notre formule =DECALER(Feuil1!$A$1;1;0;NBVAL(Feuil1!$A:$A)-1;1) dans Fait référence à: et nous validons.


Maintenant si nous cliquons sur le petit carré à droite de la formule, Excel nous affiche la zone sélectionnée dans la feuille, zone qui correspond à nos prénoms sans l'entête.


Si nous créons une Validation de données à partir de notre liste nommée Prénoms, tous les prénoms s'afficheront et nous pourrons sélectionner celui que nous voulons.
Maintenant si j'ajoute un nouveau prénom dans ma liste, il apparait instantanément dans ma liste de Validation.

Une liste de Validation qui s'ajuste c'est bien, mais si notre liste fait plusieurs centaines de lignes, cela devient vite pénible de rechercher une valeur dans une liste qu'il faut faire défiler jusqu'à l’information voulue. C'est encore DECALER qui va nous aider à simplifier quelque peu notre recherche dans cette liste.

Donc nous avons toujours notre liste de prénoms, mais cette fois nous allons ajouter A, B, C, D, etc, et ensuite trier notre liste par ordre alphabétique comme ceci.


Notre validation précédente va nous afficher exactement cette liste. Mais nous voulons quand nous saisissons D uniquement avoir David et Denis à l'affichage. Alors nous allons créer un nouveau NOM, contenant =DECALER(Prénoms;EQUIV(F8&"*";Prénoms;0)-1;;NB.SI(Prénoms;F8&"*")).
Cette nouvelle formule fait référence à la formule DECALER précédente et rajoute la possibilité d'afficher les prénoms correspondant à la première lettre demandée. Ainsi si nous entrons D dans la zone de recherche Excel affichera Davis et Denis uniquement.
Décortiquons notre formule, La référence cette n'est plus A1 mais Prénoms, la ligne est cette fois la position relative de notre recherche dans Prénoms "EQUIV(F8&"*";Prénoms;0)-1)", et la hauteur est déterminée par le nombre de cellules non vide dans la liste, répondant à la condition spécifiée "NB.SI(Prénoms;F8&"*")". F8 est la cellule ou nous allons positionnez notre Validation de données.
Donc nous créons un nouveau nom, Prénoms2, qui fait référence à notre formule, =DECALER(Prénoms;EQUIV(F8&"*";Prénoms;0)-1;;NB.SI(Prénoms;F8&"*")) et nous l'utilisons dans notre nouvelle Validation de données, pour obtenir un affichage comme montré par l'image ci-dessous.


Il vous suffit de rajouter une fonction de tri automatique par VBA pour avoir votre liste toujours correctement triée.

Si vous souhaitez voir d'autres formules Excel consultez cette page.


Maîtriser Excel, c'est facile !!!

En discuter sur le Forum?

Print Friendly and PDF


Restez informé, abonnez-vous à la newsletter, entrez votre adresse mail:


Delivered by FeedBurner

vendredi 19 décembre 2014

Excel - Comment réaliser une Feuille de temps.

Modèle de feuille de temps Excel.


Petit cours Excel pour créer une feuille de temps. Feuille de temps qui va nous permettre:
-de gérer le temps passé en clientèle,
-de déterminer le montant HT de facturation correspondante,
-de savoir si nous avons facturé ou non notre prestation.

Ce modèle est composé:
-de trois tables nommées et réalisées avec le formatage tableau,
-d'un tableau de feuille de temps, lui aussi préformaté en tableau,
-d'un tableau croisé dynamique.

Libre à vous par la suite de compléter ce fichier avec d'autres fonctions qui vous sont nécessaires ou me demander de la faire pour vous, moyennant rétribution.

je suppose que vous avez un minimum de connaissances Excel, que vous savez nommer une table, utiliser une RECHERCHEV, utiliser la Validation de données et formater un tableau automatiquement avec Excel.

Commençons avec la feuille des Paramètres.
1-Ouvrez un nouveau classeur et créez 4 onglets.
2-Dans le nom du permier onglet, saisissez Param ou Paramètres.
3-Positionnez vous dans la cellule B7 et entrez "Clients" dans le cellule.
4-Ensuite saisissez un nom de client, pour l'exemple nous mettons Client A.
5-Une fois ceci fait, dans le Menu Accueil/Mettre sous forme de tableau, choisissez un style de tableau. Dans la boite de dialogue qui s'affiche cochez Mon tableau comporte de entête et validez. Nous venons de créer un mini-tableau nommé Tableau1 par Excel.
6-Nous allons modifier ce nom en le nommant Clients, positionnez vous dans le tableau, un menu temporaire Outils Tableaux apparaît au-dessus du menu classique. Cliquez dessus et à l'extrême gauche vous voyez une case modifiable comportant Tableau1, modifiez la.
7-Création du tableau TauxHoraire, positionnez vous en D7 entrez "Taux Horaire" et répétez les étapes 4 à 6, en modifiant Clients par TauxHoraire et formatez la cellule en style monétaire.
8-Création du tableau Status, Positionnez vous en F7 entrez "Status" et répétez les étapes 4 à 6, en modifiant Clients par Notes.

Une fois ceci fait, nous disposons de trois tableaux qui vont nous servir à alimenter la feuille de temps avec une Validation de Données.



Poursuivons avec la feuille de temps.
1-Saisissez Tableau comme nom de l'Onglet 2.
2-Positionnez vous en B7 et entrez de gauche à droite, les noms suivants, Date, Mois, Année, Heure début, Heure fin, Durée, Taux, Horaire, Montant, Client, Description et Statuts.
3-Nous allons formater les cellules en-dessous.
-Les cellules Date, Heure début, Heure Fin, on un formatage date et heure, comme ceci, 19/12/2014 et 9:30.
-Les cellules Mois, Année, Durée, Client, Description et Status reste en Standart.
-les cellules Taux Horaire et Montant, on un formatage monétaire.
4-Une fois ceci fait, dans le Menu Accueil/Mettre sous forme de tableau, choisissez un style de tableau. Dans la boite de dialogue qui s'affiche cochez Mon tableau comporte des entêtes et validez. Nous venons de créer un tableau nommé Tableau4 par Excel.
6-Nous allons modifier ce nom en le nommant TableauSuivi, positionnez vous dans le tableau, un menu temporaire Outils Tableaux apparaît au-dessus du menu classique. Cliquez dessus et à l'extrême gauche vous voyez une case modifiable comportant Tableau4, modifiez la.
7-Avant de continuer, allez sur l'Onglet 4 et nommez le Table, puis en A2 à A13 entrez 1 à 12 et en B2 à B13 entrez dans l'ordre les 12 mois de l'année. Sélectionnez toute la zone et nommez la "Mois".


8-Revenons à notre Feuille de temps, positionnez vous en-dessous de l'entête Mois et entrez la formule suivante, =RECHERCHEV(MOIS([@Date]);Mois;2;FAUX). Cette cellule va déterminer le mois et l'afficher en clair.
9-Sous l'entête Année, entrez la formule =ANNEE([@Date]) ce qui extraira l'année de la date saisie en colonne B.
10-Sous Durée, entrez la formule =([Heure fin]-[Heure début])*24. Pourquoi 24, parce que pour Excel, 1 est égal à 24 heures. Si vous voulez en apprendre plus sur le temps dans Excel regardez cette page.
11-La cellule sous l'entête Taux Horaire, va contenir une validation de données, basée sur notre tableau TauxHoraire. Nous allons d'abord créer un nouveau nom, avec le gestionnaire de noms. Ouvrez le et sélectionnez nouveau, puis dans la boite de dialogue affichée, nommez le TauxHoraires avec un S. Dans la zone Fait référence à: saisissez =DECALER(TauxHoraire;0;;NBVAL(TauxHoraire)). Si vous avez respecter la position des cellules et les noms de tableaux que j'ai indiqué cela doit fonctionner. Ensuite positionnez vous  sous l'entête Taux Horaire et sélectionnez Validation de données dans le menu Données. Dans la boite de dialogue qui s'affiche choisissez Liste et =TauxHoraires comme Source et validez par OK. Maintenant notre cellule affiche les données listées dans le tableau TauxHoraire.
12-Nous allons répéter l'étape 11, pour les Entêtes Client et Status. Générez des nouveaux noms ClientB et NotesB puis entrez les formules suivantes pour chaque nom dans l'ordre =DECALER(Clients;0;;NBVAL(Clients)) et =DECALER(Notes;0;;NBVAL(Notes)). Ensuite créer
la validation de données en liant les cellules avec les noms. =ClientsB et =NotesB.

Notre feuille de temps est prête, vous pouvez masquer les colonnes des entêtes Mois et Années qui ne sont pas utiles lors de la saisie de données.



Maintenant nous allons attaquer la dernière étape avec la création d'un tableau croisé dynamique, qui va nous permettre d'analyser nos données. Si vous ignorez tout des tableaux croisés dynamiques visitez cette page.

1-Sélectionnez l'onglet 3 et saisissez Analyse comme nom.
2-Retournez à la feuille de temps et positionnez vous n'importe où dans le tableau. Puis sélectionnez le menu Insertion/tableau croisé dynamique (à l'extrême gauche).
3-Dans la boite de dialogue sélectionner feuille de calcul existante et dans Emplacement taper =Analyse!$B$7, puis validez.
4-Il ne vous reste plus qu'à créer votre tableau croisé en choisissant les zones à activer.
-FILTRES: Status, Année.
-COLONNES: Mois
-LIGNES: Clients
-VALEURS: Somme de Montant.



Voilà tout est prêt pour suivre votre travail de consultant ou autre. Pour ajouter un élément dans les tableaux positionnez vous à gauche, sous la dernière cellule saisie et entrez vos nouvelles données, les tableaux s'ajusteront automatiquement.

Pour ceux qui préfèrent avoir le fichier tout fait, voici un outil de feuille de temps et de facturation pour les travailleurs indépendants et les consultants qui travaillent principalement à l’heure.
Ce fichier va vous permettre de façon simple de suivre votre temps de travail, et la facturation associée.

Fichier Compatible uniquement avec Excel 2007 à 2013.

Comment utiliser ce fichier?
Utilisez les boutons situés en haut de feuille, pour vous déplacer d'une feuille à l'autre.

La feuille Paramètres, va vous permettre d'ajouter, de modifier ou de supprimer des éléments qui vous facilite la gestion de la feuille de temps.

La feuille de temps, va vous permettre de suivre  vos heures de travail, les montants calculés en fonction de votre taux horaire, si ces montants ont été facturés ou doivent l'être.

La feuille Analyse facturation, vous permet de suivre vos factures et de savoir ce qu'il faut en faire, ou combien avez-vous facturé à un client ou sur un mois, etc. Le Filtre principal, Notes, vous permet d'un clique de choisir d'afficher, toutes les notes, les à facturer ou les facturés.

Vous trouverez le fichier en page de téléchargements.

Maîtriser Excel, c'est facile !!!

En discuter sur le Forum?

Print Friendly and PDF

Cet article vous a intéressé, merci de le recommander avec un Google +1 et de le partager sur Facebook, Twitter, Viadeo ou Linkedin, en utilisant les liens mis à votre disposition.

Pour suivre nos prochains articles abonnez-vous au Blog ou inscrivez-vous au flux RSS.


Restez informé, abonnez-vous à la newsletter, entrez votre adresse mail:


Delivered by FeedBurner

mercredi 17 décembre 2014

Excel - Fonctions logiques ET/OU

Fonction Excel - ET/OU - Formule logique

L'utilisation d'Excel ne se limite pas à des opérations comme les additions, les soustractions, les multiplications ou les divisions.
La plus part du temps la comparaison entre différents éléments d'un tableau ou d'une base de données est nécessaire voir impérative. Cette action passe le plus souvent par la fonction SI, mais pas que, dans certains cas l'adjonction de nouveaux opérateurs est indispensable. Nous allons voir aujourd'hui l'intérêt des fonctions ET et OU.

D'abord un point rapide.

-ET: Vérifie si toutes les conditions à comparer sont vraies et renvoie VRAI ou FAUX.

Syntaxe ET(valeur_logique1;valeur_logique2;...)
valeur_logique1,valeur_logique2, ... représentent les conditions à remplir. Il est possible de saisir jusqu'à 255 conditions.

-OU: Vérifie si au moins une condition est vraie et renvoie VRAI ou FAUX.

Syntaxe OU(valeur_logique1;valeur_logique2;...)
valeur_logique1,valeur_logique2, ... représentent les conditions à remplir ou pas. Il est possible de saisir jusqu'à 255 conditions.


Nous avons le tableau suivant, reprenant les prénoms, le sexe et l’âge d'un groupe d'adolescents.
Je prends volontairement un exemple faisant appel aussi bien à du texte qu'à des valeurs, ainsi nous pouvons voir que ces fonctions ne sont limitées à des valeurs.



Nous voulons extraire plusieurs informations de notre tableau:

-Savoir quelle fille à un âge supérieur à la moyenne de l'ensemble.
Dans ce cas, nous devons vérifier que deux conditions soient vraies, sexe = F et Age > à 20. Nous entrons la formule suivante dans la cellule E3, =SI(ET(C3="F";D3>$D$9);"Oui";"Non") et ensuite nous la dupliquons jusqu'à E8.



-Savoir quel adolescent est une fille ou a un âge supérieur à la moyenne.
Dans ce cas, nous devons vérifier qu'une de deux conditions, sexe =F ou Age > à Moyenne, est vraie. Nous entrons la formule suivante dans la cellule F3, =SI(OU(C3="F";D3>$D$9);"Oui";"Non") et ensuite nous la dupliquons jusqu'à F8.



-Savoir quelle fille ou garçon a un âge supérieur à la moyenne.
Dans ce cas la fonction SI suffit, =SI(D3>$D$9;"Oui";"Non"), j'aurais pu entrer aussi =SI(ET(OU(C3="F";C3="M");D3>$D$9);"Oui";"Non") pour obtenir le même résultat mais on ne va pas non plus se compliquer la vie. Sachez seulement qu'il est possible d'imbriquer ET et Ou.



J'ai deux questions à vous poser.

Si nous rajoutons une colonne contenant la moyenne des notes.



-Quel garçon ayant un âge supérieur à la moyenne de l'ensemble, a une moyenne de note au moins égale à 12?
-Quel fille n'a pas un âge supérieur à la moyenne de l'ensemble et n'a pas une moyenne de note au moins égale à 12?

Indiquez le nom, mais aussi la formule utilisée.

En savoir plus sur les fonctions logiques? Retrouvez les 50 formules Excel de base.

Maîtriser Excel, c'est facile !!!

En discuter sur le Forum?

Print Friendly and PDF


Restez informé, abonnez-vous à la newsletter, entrez votre adresse mail:


Delivered by FeedBurner

mardi 16 décembre 2014

Excel- Comment créer un lien dynamique

Fonction Excel - LIEN_HYPERTEXTE - Formule recherche et référence.


Cette fonction, crée un raccourci permettant d’ouvrir un document stocké sur un serveur réseau, un intranet ou sur Internet. Lorsque vous cliquez sur la cellule contenant la fonction LIEN_HYPERTEXTE, Microsoft Excel ouvre le fichier stocké à l’adresse emplacement_lien.

L'intérêt principal de cette fonction c'est qu'elle crée un lien dynamique, contrairement au LIEN HYPERTEXTE contenu dans le menu contextuel d'une cellule Excel, qui lui est un lien statique.
Elle permet également de modifier à distance des liens contenus dans un fichier auquel d'autres fichiers font référence.

Syntaxe LIEN_HYPERTEXTE(emplacement_lien, [nom_convivial]).

  • emplacement_lien: Obligatoire. Représente le chemin d’accès et le nom de fichier du document à ouvrir.
  • nom_convivial: Facultatif. Représente le texte de renvoi ou la valeur numérique qui s’affiche dans la cellule.


Comme nous n'avons pas de réseau ou d'intranet sous la main, nous allons faire un exemple de lien dynamique avec internet. Dans ce cas autant utiliser mes comptes Twitter, Facebook, LinkedIn et Blogger histoire de me faire un peu de pub!

Dans notre feuille Excel en B2 à B5, nous inscrivons les noms des réseaux sociaux, cités ci-dessus et dans C2 à C5 nous entrons les liens web correspondants.


Une fois ceci fait nous créons une validation de données en B7, à partir des cellules B2 à B5. Si vous ne savez comment faire regardez le post sur la Validation de donnée.



Ensuite en C7, allons chercher l'adresse du lien internet pour permettre à notre fonction d'aller chercher la page demandée, à l'aide d'un RECHERCHEV.



Pour finir nous entrons la formule suivante en B9, =LIEN_HYPERTEXTE(C7;B7),  dans ce cas C7 correspondant emplacement_lien et B7 à nom_convivial.



Voilà notre lien dynamique est maintenant créé, il nous suffit de sélectionner un réseau dans la cellule B7, pour que notre lien hypertexte en B9, change de nom et d'adresse instantanément.



Une autre façon de procéder, est de créer un fichier contenant des zones réservées à des liens quelconques. Puis de lier ce fichier avec d'autres et par la suite partager ce fichier avec vos utilisateurs. Il vous suffit maintenant de mettre à jour ce fichier avec de nouveaux liens pour que ces liens soient activés ou mis à jour dans les autres fichiers. J’utilisais cette façon de faire avec le fichier Excel-Ligue1 de la saison 2013-2014. Ce qui me permettait par exemple d'informer les utilisateurs d'une nouvelle version du fichier et de leur indiquer la page internet de téléchargement sans qu'ils viennent sur le Blog.

Si vous souhaitez voir d'autres fonctions Excel en détail, rendez-vous sur cette page.

Maîtriser Excel, c'est facile !!!

En discuter sur le Forum?

Print Friendly and PDF


Cet article vous a intéressé, merci de le recommander avec un Google +1 et de le partager sur Facebook, Twitter, Viadeo ou Linkedin, en utilisant les liens mis à votre disposition.

Pour suivre nos prochains articles abonnez-vous au Blog ou inscrivez-vous au flux RSS.

Restez informé, abonnez-vous à la newsletter, entrez votre adresse mail:


Delivered by FeedBurner

lundi 15 décembre 2014

Le tour d'Excel en 50 fonctions.

Fonction Excel : Fonctions de base
50 fonctions pour vous aider à tirer profit d'Excel. Vous trouverez sur cette page un florilège des fonctions qu'un jour ou l'autre vous serez amené à utiliser.















1 - Introduction :


Sans vouloir être réducteur et résumé Excel à 50 fonctions, je vais vous présenter ici, celles que j'utilise le plus dans mes fichiers, développements, et qui me permettent de résoudre la plus part des problèmes rencontrés au quotidien.
Les fonctions détaillées ci-dessous existent pour la plus part dans les versions 2007,2010 et 2013 d'Excel. Certaines ne sont maintenues que pour des raisons de compatibilité avec 2007, mais restent très utiles.
Pour des raisons évidentes, les formules présentées sur cette page, le sont de façon résumée, mais chacune d'elle a fait ou fera l'objet d'un article dédié.
Les exemples d'utilisation présentés, ne sont là que pour vous donner une idée des possibilités offertes par ces fonctions, ils ne sont pas exhaustifs.
Parfois les fonctions vont vous sembler peu intéressantes ou basiques, mais ne vous y fiez pas, imbriquées entre-elles, elles deviennent de redoutables outils de résolution de problèmes.
Si vous utilisez au quotidien, d'autres fonctions que celles présentées ici, n’hésitez pas à nous en parler dans les commentaires, nous sommes là pour nous améliorer!

2 - Date et Heure :

Les fonctions Dates et Heures permettent la manipulation et le calcul du temps, elles sont spécifiques à cette utilisation.

  • ANNEE: renvoie l'année, entre 1900 et 9999, à partir d'un numéro de série utilisé par Excel. =ANNEE(49897) renvoie 2036. Idéal pour extraire l'année d'une date et l'utiliser pour d'autres calculs par la suite.
  • AUJOURDHUI: Donne la date du jour au format JJ/MM/AAAA. Si nous sommes le 13 décembre 2014 =AUJOURDHUI() donne 13/12/2014. Vous permet à l'ouverture d'Excel, d'actualiser la date du jour. Utile si l'on a besoin d'avoir une date de référence dans un fichier, par exemple un suivi de trésorerie journalière.
  • DATEVAL: Convertit une date texte en date utilisable par Excel. Si nous avons 10/12/2014 au format texte dans la cellule A1,  =DATEVAL(A1) donne 41983 dans le format Excel. A utiliser si par exemple les données rapatriées dans le tableur, le sont au format texte.
  • JOUR: Extrait le numéro du jour pour une date donnée. =JOUR("21/12/2014") donnera 21. Utile pour faire des plannings ou des calendriers entre autre.
  • JOUR360: Calcul le nombre de jours entre deux dates, sur la base d'une année de 360 jours. Si vous avez 41994 en A1 et 42345 en B1 alors =JOURS360(A1;B1) donnera 346, nombre correspondant à l'écart entre ces deux dates. =JOUR("21/12/2014";"07/12/2015") affichera la même chose. Peut s'avérer utile pour des calculs financiers qui font référence à des écarts de dates.
  • JOURSEM: indique le jour de la semaine à partir d'une date. Si vous avez 12/12/2014 en A1, =JOURSEM(A1;1) donnera 1 et =JOURSEM(A1;2) donnera 7 suivant le critère Type_retour que vous aurez choisi. 1 donne dimanche comme premier jour, 2 donnes Lundi comme premier jour et dimanche comme le septième, etc.....Vous avez jusqu'à 10 Type_retour différents de quoi adapter Excel à tous vos besoins. Tout indiqué pour un calendrier nécessitant des journées de semaine décalées, exemple magasin.
  • MOIS: Extrait le numéro du mois pour une date donnée. =JOUR("21/12/2014") donnera 12. Utile pour faire des plannings ou des calendriers par exemple.
  • NO.SEMAINE: Renvoie le numéro de la semaine correspondant à une date donnée. Si nous reprenons l'exemple ci-dessus =NO.SEMAINE(A1) donne 1. 

3 - Information :

Les fonctions informations, vont nous permettre de vérifier le contenu des cellules auditer et  de faire réagir le tableur suivant nos besoins en fonction des résultats.

  • CELLULE: Vous donne une série d'information sur une cellule, adresse, contenu, protection... =CELLULE("contenu";A1) vous indique ce qu’elle contient, =CELLULE("Protege";A1) vous informe sur l'état de la cellule, est-elle protégée ou non. Utile pour faire réagir une VBA en fonction de l'information fournie ou toute autre chose.
  • ESTERR: vérifie si le contenu d'une cellule fait référence à une erreur (#NA, #Valeur,...) et renvoie VRAI ou FAUX. A utiliser mixée avec un SI par exemple pour déterminé une action à tenir.
  • ESTERREUR: Ressemble à si méprendre à ESTERR.
  • ESTNA : Plus spécifique que les deux précédentes fonctions, vérifie si la cellule contient l'erreur #NA et renvoie VRAI ou FAUX. Comme la plus part des fonctions EST est à mixer avec SI.
  • ESTVIDE: Contrôle qu'une référence renvoie à une cellule vide et renvoie VRAI ou FAUX. Si A1 contient 3, =ESTVIDE(A1) renvoie FAUX.  Comme la plus part des fonctions EST est à mixer avec SI.

4 - Logique :

Les fonctions logiques permettent le contrôle de la réalisation ou non de conditions que nous avons préalablement défini.

  • ET: Vérifie si toutes les conditions sont vraies et renvoie VRAI ou FAUX. Si nous avons 3 en A1, 5 en B1 et 2 en C1, =ET(A1>C1;B1>C1) donne VRAI car toutes les conditions sont vrais. Utilisation principale avec la fonction SI.
  • OU: A l'inverse de ET, OU vérifie si au moins une condition est vraie et renvoie VRAI ou FAUX. Reprenons l'exemple ci-dessus, =OU(A1>C1;C1>B1) renvoie VRAI car une des deux conditions est vraie. A mixer avec SI principalement.
  • SI: La fonction par excellence, vérifie que la condition est respectée et renvoie la valeur spécifiée si la condition est vraie et une autre si la condition est fausse. Toujours avec le même exemple cité pour ET, =SI(A1>C1;1;2) donne 1 car la condition A1>C1 est vraie. A utiliser dans quasiment tous les cas de figure où l'on veut faire des tests. Les conditions SI peuvent s'enchainer les unes derrière les autres, pour tester plusieurs conditions à la suite, mais attention à la visibilité de la formule lorsque l'on revient dessus après quelques temps.  
  • SIERREUR: Arrivée depuis 2007 dans Excel, cette fonction permet en cas d'erreur, de faire réagir le tableur, comme nous le voulons. Imaginons que nous ayons une RECHERCHEV basée sur l'exemple de ET et qui donne une erreur, SIERREUR(RECHERCHEV(6;A1:C1;2);0) nous permet de renvoyer une valeur 0 plutôt qu'une erreur Excel type #NA ou #VALEUR. C'est nettement plus sympathique pour présenter un tableau. Cette fonction se substitue à un mixe SI et ESTERR par exemple.
  • VRAI: Permet de renvoyer VRAI dans une autre fonction.
  • FAUX: Renvoie FAUX dans une autre fonction.
Pour en apprendre plus sur les fonctions logiques vous pouvez consulter cette page.

5 - Maths et Trigonométrie :

Les fonctions Mathématiques permettent d'exécuter des calculs simples ou complexes.

  • ALEA: Fonction volatile. Renvoie un nombre aléatoire entre 0 et 1.
  • ALEA.ENTRE.BORNES: Fonction volatile. Renvoie un nombre aléatoire entre deux nombres que vous spécifiez.
  • ARRONDI: Arrondi un nombre au nombre de chiffre indiqué. =ARRONDI(3,14116;2) donne 3,14, car nous lui avons demandé d'arrondir à 2 chiffres après la virgule. Contrairement au formatage d'une cellule avec deux chiffres après la virgule, ARRONDI transforme le nombre 3,14116 pour en faire 3,14. Cette fonction vous permet lors de calculs générant x chiffres après la virgule d'éviter de les trainer dans les tableaux suivants et ainsi de créer des résultats incompréhensibles au final.
  • MOYENNE: Comme son nom l'indique cette fonction calcule la moyenne de nombres ou de cellules contenant des nombres. =MOYENNE(3;10;4;56) renvoie 18,25.
  • SOMME: Calcule la somme d'une série de cellules contenant des nombres. Si A1=2 et B1=10, =SOMME(A1:B1) affiche 12.
  • SOMME.SI: Cette somme conditionnelle, additionne les cellules spécifiées pour un certain critère et affiche la somme des résultats.  Si en A1 à A7 nous avons une suite de prénoms, en B1 à B7 des montants, =SOMME.SI(A1:A7;"Mélanie";B1:B7) donnera la somme de tous les montants correspondant au critère Mélanie. Il existe aussi SOMME.SI.ENS pour faire la même chose mais avec plusieurs critères. Ces fonctions sont très utiles pour extraire des informations d'un tableau et les réutiliser par ailleurs dans votre fichier.
  • SOUS.TOTAL: Intercalée dans vos tableaux ou listes, cette fonction, suivant certains critères, effectuera un sous-total qui pourra par la suite être additionné pour faire un total général. En fonction du critère choisit vous pourrez obtenir, la somme, la moyenne, le maximum, le minimum, le produit,..., de la zone de cellules de référence. 

6 - Recherche et Référence :

Les fonctions Recherche et Référence permettent la définition de plage de cellules et l'interrogation de celles-ci par la suite.
  • ADRESSE: Crée une référence absolue ou relative, par rapport à une ligne et à une colonne. =ADRESSE(1;1;1) donne $A$1, =ADRESSE(1;1;2) donne A$1. Un exemple de son utilisation est donné sur le post Afficher une image ou un logo en fonction d'un choix.
  • CHOISIR: Comme son nom l'indique, cette fonction choisit une valeur parmi une multitude de valeurs, en fonction d'un numéro d'index. Si nous avons 2 en C5, 3 en D5 et 4 en E5, =CHOISIR(3;C5;D5;E5) affichera 4 qui correspond à la valeur de la troisième cellule. Utile pour sélectionner une donnée dans une liste ou tableau, en fonction d'un critère que vous sélectionnez dans une liste ou d'un résultat calculé auparavant.
  • COLONNE: Renvoie le numéro d'une colonne de référence. =COLONNE(A1) affichera 1.
  • DECALER: Sert à définir une plage de cellules dans une feuille en partant d'une cellule, on décale la référence de cette cellule d'un certain nombre de lignes et de colonnes. Difficile de donner ici un exemple, j'utilise cette fonction indispensable pour gérer des bases de données par exemple.
  • EQUIV: Recherche un élément spécifique dans une plage de cellules et renvoie sa position relative. Si nous avons 2 en C5, 3 en D5 et 4 en E5, =EQUIV(2;C5:E5;0) donnera 1, car la valeur 2 est située dans la première cellule de la zone de référence.  A associer avec INDEX.
  • INDEX: Permet de renvoyer la valeur d'une cellule se trouvant à l'intersection d'une ligne et d'une colonne dans un tableau. Si nous avons 2 en C5, 3 en D5 et 4 en E5, =INDEX(C5:E5;1;2) affichera 3. A mixer avec EQUIV.
  • LIEN_HYPERTEXTE: Crée un raccourci permettant d’ouvrir un document stocké sur un serveur réseau, un intranet ou sur Internet. Lorsque vous cliquez sur la cellule contenant la fonction LIEN_HYPERTEXTE, Microsoft Excel ouvre le fichier stocké à l’adresse emplacement_lien. Syntaxe LIEN_HYPERTEXTE(emplacement_lien, [nom_convivial]). Exemple, les fichiers de vos collaborateurs font référence, à l'aide de LIEN_HYPERTEXTE, à un fichier source contenant des liens divers, vous pouvez mettre à jour ces liens pour tout le monde uniquement en modifiant le fichier source. 
  • LIGNE: Renvoie le numéro d'une Ligne de référence. =LIGNE(A1) affichera 1.
  • RECHERCHE: Cherche dans la première ligne ou colonne d'un tableau pour la valeur spécifiée, puis renvoie une valeur à partir de la même position dans la dernière ligne ou colonne de la matrice.
  • RECHERCHEH: Cherche une valeur donnée dans la première ligne de la matrice d'un tableau et renvoie une valeur se trouvant sur la même colonne mais dans une autre ligne de la matrice du tableau. Si nous avons 1, 2, 3 en A1 à C1 et Paris, Lille, Rouen en A2 à C2, =RECHERCHEH(1;A1:C2;2) affichera Paris.
  • RECHERCHEV: Cherche une valeur donnée dans la première colonne de la matrice d'un tableau et renvoie une valeur se trouvant sur la même ligne mais dans une autre colonne de la matrice du tableau. Si nous avons 1, 2, 3 en A1 à A3 et Paris, Lille, Rouen en B1 à B3, =RECHERCHEV(2;A1:B3;2) donnera Lille.

7 - Statistiques :

Les fonctions Statistiques nous permettent de classer nos calculs, de déterminer des maxima ou des minima et par la suite d'utiliser ces résultats ultérieurement.

  • MAX: Renvoie le plus grand nombre d'un ensemble de valeur. Si nous avons 1, 2, 3 en A1 à A3, =MAX(A1:A3) affichera 3 qui est la valeur la plus forte de la liste.
  • MIN: Renvoie le plus petit nombre d'un ensemble de valeur. Si nous avons 1, 2, 3 en A1 à A3, =MIN(A1:A3) affichera 1 qui est la valeur la plus faible de la liste.
  • NB.SI: Détermine le nombre de cellules non vide dans une liste, répondant à la condition spécifiée. Si nous avons 2, 3, 3, 0, 4 en A1 à A5, =NB.SI(A1:A5;3) donnera 2 car il existe deux cellules correspondantes au critère 3.
  • NB.VIDE: Compte le nombre de cellule vide dans une plage spécifiée. Si nous reprenons l'exemple de NB.SI. Le résultat de =NB.VIDE(A1:A5) sera égale à 0 car aucune cellule n'est vide.
  • NBVAL: A l'inverse de NB.VIDE, cette fonction détermine le nombre de cellule non vide. Avec le même exemple =NBVAL(A1:A5) affichera 5 car aucune cellule n'est vide dans la liste.
  • RANG: Renvoie le rang d'un nombre dans une liste de valeur en fonction d'un ordre de tri. Toujours avec le même exemple, =RANG(A1;A1:A5;1) donnera 2 et =RANG(A1;A1:A5;0) donnera 4. Formule maintenue pour la compatibilité avec les versions antérieures à 2007.

8 - Texte :

Ces fonctions permettent la manipulation, l'extraction, la conversion de texte dans le tableur.

  • CNUM: Converti un nombre écrit en texte en nombre. Si vous avez 23 stocké en texte en A1, =CNUM(A1) affichera 23 converti en nombre et vous pourrez ainsi utiliser votre résultat pour d'autres calculs par la suite.
  • CONCATENER: Assemble plusieurs chaines de caractère, nombre ou texte, pour n'en former qu'une seule. Si nous avons Bonjour, les, amis en A1 à A3, =CONCATENER(A1;"";A2;"";A3) afficher Bonjours les amis comme résultat. =CONCATENER(A1;A2;A3) donne Bonjourslesamis.
  • DROITE: Extrait les derniers caractères situés à droite de la cellule, en fonction d'un nombre défini. Si nous avons Bonjours en A1, =DROITE(A1;5) affichera jours.
  • GAUCHE: Extrait les premiers caractères situés à gauche de la cellule, en fonction d'un nombre défini. Si nous avons Bonjours en A1, =GAUCHE(A1;3) affichera Bon.
  • NBCAR: Renvoie le nombre de caractères contenus dans une cellule. Avec Bonjours en A1, =NBCAR(A1) donnera 8.
  • STXT: Renvoie un nombre déterminé de caractères contenus dans une cellule, à partir d'une position définie et suivant un nombre défini. Toujours avec le même exemple, =STXT(A1;4;1) donnera j.
  • TROUVE: Renvoie la position de départ d'une chaine de texte à l'intérieur d'une autre chaine de texte. Si nous avons Bonjours les amis dans la cellule A1, =TROUVE("les";D11;1) donnera 10, =TROUVE("Les";D11;1) donnera une erreur, car cette fonction distingue les majuscules et les minuscules.

Si vous imbriquez entre elles les fonctions texte ci-dessus vous pourrez extraire n'importe quelle information d'un texte, comme montré dans le tuto de cette page.


9 - Téléchargements :


Fonctions Excel.

10 - Autres références au sujet (ou articles complémentaires):


Les 50 fonctions essentielles.

11 - Autres liens :

Excel
Forum

Print Friendly and PDF


Cet article vous a intéressé, merci de le recommander avec un Google +1 et/ou de le partager sur Facebook, Twitter, Viadeo ou LinkedIn, en utilisant les liens mis à votre disposition.

Pour suivre nos prochains articles abonnez-vous au Blog ou inscrivez-vous au flux RSS.

Restez informé, abonnez-vous à la newsletter, entrez votre adresse mail:


Delivered by FeedBurner

mercredi 10 décembre 2014

Excel - La fonction RECHERCHE

Fonction Excel - RECHERCHE - Formule recherche et référence.


Vous connaissez surement les fonctions RECHERCHEV et RECHERCHEH dans Excel et vous devez même les utiliser assez souvent dans vos fichiers et tableaux pour retrouvez dans des tables des valeurs ou textes à réutiliser par la suite dans vos travaux.
Mais connaissez vous la fonction RECHERCHE. Dans certains cas ou les deux autres fonctions sont inutilsables, RECHERCHE peut vous aider.

Cette fonction offre deux possibilités:
-Une recherche matricielle.
-Une recherche vectorielle.

Nous allons voir un exemple d'utilisation pour chaque possibilité, ceux-ci ne sont pas exhaustifs et il doit exister beaucoup d'autres possibilités d'utiliser cette fonction.

Recherche Matricielle

RECHERCHE (valeur_cherchée,matrice)

La forme matricielle de recherche, cherche dans la première ligne ou colonne d'un tableau pour la valeur spécifiée, puis renvoie une valeur à partir de la même position dans la dernière ligne ou colonne de la matrice.

Avec un exemple ce sera plus parlant.
Imaginons que nous ayons la liste suivante, correspondant à des clients grandes surfaces.

CORA Lille
AUCHAN Lyon
CASINO Nancy
AUCHAN Nantes
CORA Toulouse
CASINO Lyon

D'un autre coté, nous avons la liste des grandes surfaces (je ne fais pas de pub, les pubs c'est en bas ou à droite) avec les taux de commissions applicables aux ventes réalisées avec chacune d'elles.

Clients Taux Com.
AUCHAN  10%
CASINO 12%
CORA 11%

Et enfin nous avons un tableau récapitulatif des ventes réalisées par nos vendeurs avec nos différents clients.


Nous voulons calculer pour chacun de nos vendeurs le montant de commission que nous devons lui verser en fonction du client avec lequel il à réalisé son CA.

Pour réaliser notre calcul nous devons arriver à extraire de la liste clients du tableau les noms des grandes surfaces. Inutile d'essayer avec RECHERCHEV, la fonction ne parviendra pas à trouver la correspondance entre les deux listes à notre disposition.
Nous allons utiliser RECHERCHE, qui dans ce cas s'avère être une excellente solution, car elle va extraire le premier mot d'une suite termes, qu'elle va ensuite comparer à une liste existante.

Commencons par entrer  =RECHERCHE(B3;$B$11:$B$13) dans la première cellule de la colonne taux, qui va chercher B3 (CORA Lille) dans la liste B11 à B13, et nous obtenons CORA (Magique!!!). Ensuite nous Complétons notre formule avec RECHERCHEV, =RECHERCHEV(RECHERCHE(B3;$B$11:$B$13);$B$11:$C$13;2) afin d'obtenir notre taux de commission de 11%.

Une fois notre formule répétées dans les cellules suivantes notre tableau est complet et correcte.


Petite précision qui a son importance, la table ou liste de référence, ici B11 à B13, doit être triée dans l'ordre croissant ou alphabétique pour du texte dans le cas contraire la fonction renverra une réponse fausse.

Recherche véctorielle

RECHERCHE (valeur_cherchée,vecteur_recherche,vecteur_résultat)

La forme vectorielle de recherche cherche dans une plage une seule ligne ou colonne (appelée vecteur) pour une valeur et renvoie une valeur à partir de la même position dans une seconde plage à une ligne ou une colonne (Aie ma tête!!!).

Dans cet exemple nous avons un tableau avec des magasins et le CA correspondant, ainsi qu'une table de niveau de CA et des Taux de commissions.

Nous voulons déterminer les taux de commission dans le tableau 1. RECHERCHEV est parfait dans ce cas précis. L'image ci-dessous nous montre le même résultat si nous utilisons =RECHERCHE(C3;$B$11:$B$13;$C$11:$C$13) Colonne D ou =RECHERCHEV(C3;$B$11:$C$13;2) Colonne F.



Sauf que RECHERCHEV ne fontionne que dans un sens, de gauche à droite, et RECHERCHEH de haut en bas, tandis que RECHERCHE ne s'occupe pas de la disposition des colonnes ou des lignes, vous pouvez lier n'importe quelle liste à votre première colonne, du momment qu'elle comporte le même nombre de lignes et qu'elle soit triée dans l'ordre croissant.
Voici ce qui se produit si je déplace la colonne CA de la gauche vers la droite dans la table de référence, RECHERCHEV à perdue le fil, tandis que RECHERCHE est resté correcte.



Comme je l'ai dit plus avant, ce ne sont que deux exemples parmis d'autres, à vous de trouver maintenant dans quels cas utiliser RECHERCHE, nul doute que vous allez maintenant vous poser plus de questions avant d'utiliser RECHERCHEV ou RECHERCHEH.

Si vous avez d'autres exemples ou des compléments d'informations, n'hésitez pas à nous en faire part dans les commentaires ou dans le Forum.

Maîtriser Excel, c'est facile !!!

En discuter sur le Forum?

Print Friendly and PDF


Cet article vous a intéressé, merci de le recommander avec un Google +1 et de le partager sur Facebook, Twitter, Viadeo ou Linkedin, en utilisant les liens mis à votre disposition.

Pour suivre nos prochains articles abonnez-vous au Blog ou inscrivez-vous au flux RSS.

Restez informé, abonnez-vous à la newsletter, entrez votre adresse mail:


Delivered by FeedBurner

mardi 9 décembre 2014

Excel - Formater et additionner plusieurs onglets en même temps.

Une astuce pour gagner du temps et faciliter vos travaux sur Excel!!!!


Excel - Formater et Additionner plusieurs onglets en même temps.

Vous utilisez Excel régulièrement et vous êtes souvent contraint de créer des tableaux mensuels sur plusieurs feuilles afin de suivre l'évolution de différentes activités de votre entreprise ou d'autres choses.
Vous utilisez la plus part du temps le même tableau sur chaque onglet, vous créer votre premier tableau et vous le dupliquez sur plusieurs feuilles en utilisant la fonctionnalité Déplacer ou Copier....

Je vais vous montrer comment créer et formater le même tableau sur chacune de vos feuilles et les consolider sur un onglet total de façon simple et rapide.

Imaginons que nous voulons suivre le CA par type d'article sur plusieurs mois.
Première chose à faire, créer le nombre d'onglets nécessaire à notre suivi. 1 onglet par mois, 12 mois, donc 12 onglets mensuels et un onglet Total.

Deuxième étape, sélectionner tous vos onglets individuellement un utilisant la touche Ctrl et le clique droit de la souri. Les onglets sont devenus gras et l'onglet actif est vert.


A partir de cet instant, tout ce que vous ferez sur l'onglet actif sera reproduit automatiquement sur les autres feuilles sélectionnées de votre classeur.

Maintenant, il vous suffit de créer votre tableau et de la formater comme bon vous semble, comme celui ci par exemple.

Une fois votre tableau terminé, vous pouvez dissocier vos onglet, en double cliquant sur l'un d'eux. Déplacez-vous d'un onglet à l'autre, vous constaterez que le contenu est absolument identique.

Nous allons maintenant consolider (additionner) le contenu de chaque tableau dans le tableau total (ou récap, cumul, comme vous voulez).
Positionnez vous sur le tableau de totalisation, dans la première cellule vide, tapez + et utilisez CTRL+ Page-up pour vous déplacer jusqu'au premier onglet dans la première cellule de données, puis faite un clique de souri.
Répétez cette opération pour tout vos tableaux jusqu'à la récap et validez par entrée.
Les données de la cellule 1 de vos tableaux sont totalisées sur la cellule 1 du tableau total comme ceci =+Feuil1!C3+Feuil2!C3.
Il ne vous reste plus qu'a dupliquer cette formule sur les autres cellules du tableau et le tour est joué.

Maintenant des que vous remplissez une cellule de n'importe quel tableau mensuel, le tableau total ce met à jour instantanément.

Par la suite si vous devez modifier vos tableaux, il vous suffit de procéder de la même façon pour supprimer, ajouter des lignes, des colonnes, modifier le formatage, etc....

Maîtriser Excel, c'est facile !!!

En discuter sur le Forum?

Print Friendly and PDF

Cet article vous a intéressé, merci de le recommander avec un Google +1 et de le partager sur Facebook, Twitter, Viadeo ou Linkedin.

Pour suivre nos prochains articles abonnez-vous au Blog ou inscrivez-vous au flux RSS.

En savoir plus, consulter l'actualité de Excel-Dev sur :
- Facebook :Excel-Ligue1, Excel-Dev
- Twitter : Le blog Excel-Dev

lundi 8 décembre 2014

Graphique Excel - Limiter l'affichage aux données actives

Excel - Graphique - Comment limiter l'affichage aux données actives et améliorer sa compréhension?

Lorsque vous générez un graphique de façon automatique à l'aide de formules faisant référence à d'autres cellules d'un fichier, les données à zéro sont quand même affichées dans votre graphique. Excel vous permet si vous gérer manuellement votre fichier de cocher ou décocher les étiquettes à afficher.

Mais dans un contexte entièrement automatisé et protégé, ou vous n'êtes pas l'utilisateur du fichier, comme par exemple pour le fichier Excel-Ligue1, vous n'avez plus cette possibilité, vous devez trouver d'autres solutions.

Nous allons voir ici une façon d'éliminer de façon automatique cet affichage parasite.

Nous avons un tableau qui récapitule les CA du premier semestre pour nos différents magasins. Etant donné que ce tableau est une récap et qu'il tire ses informations d'autres tableaux, les cellules qui ne comptent pas de CA dans les tableaux sources sont affichées à zéro. Ce qui comme nous le montre l'exemple ci-dessous génère des courbes bizarres dans notre graphique, puisque les mois non encore entrés dans nos tableaux sont affichés à zéro.
Le graphique est basé sur le tableau récap - CA -Semestre 1

Vous allez me dire, qu'il suffit d'inscrire une condition si avec "" dans le tableau récap, comme ceci =SI(OU(+C7="";C7=0);"";C7). Si la condition définie, n'est pas respectée, Excel affiche un vide et le tour est joué. Erreur, comme le montre l'exemple suivant.



Alors comment faire? Tout simplement en générant volontairement une erreur dans Excel. Pour ce faire nous allons utiliser la fonction NA() qui renvoie une réponse du type #NA, donc une erreur pour Excel.

Dans l'exemple ci-dessous la formule de l'exemple précédent =SI(OU(+C7="";C7=0);"";C7) à été remplacée par la formule suivante =SI(OU(+C7="";C7=0);NA();C7). Ce qui veut dire que si la condition n'est pas remplie, alors Excel génère une erreur, erreur que le graphique n'interprète pas et qu'il ignore. Donc pas d'affichage dans le graphique.



Poussons un peu le vice et disons que nous avons la possibilité d'avoir des magasins en travaux sur un ou plusieurs mois, donc un CA de zéro sur la période, notre précédent exemple ne gérera pas cette hypothèse et affichera un #NA dans le tableau. L'exemple ci-dessous montre comment palier à ce manque et avoir un graphique qui tient compte de toutes les hypothèses.
La formule =SI(OU(+C7="";C7=0);NA();C7) a été modifiée de façon à tenir compte des éventuels vrais zéro à afficher, =SI(C23="";NA();C23).



Voilà notre problème résolu, par contre notre tableau n'est pas top, si vous devez le présenter également, je vous conseil de créer deux fois le même tableau, l'un pour le graphique avec les erreurs (cerclé rouge) et l'autre sans erreur pour la présentation (cerclé bleu), comme ceci vous masquez le graphique avec erreur (cerclé rouge). Dans ce cas vous devez modifier la source des données du graphique et indiquer qu'il doit afficher les cellules masquées.



Voilà, il ne vous reste plus qu'a épater vos collègues et votre patron avec votre superbe graphique tout automatique.

Pour ceux qui souhaiteraient voir le fichier Excel c'est ici.

Si vous connaissez une autre méthode ou si vous avez besoin d'autres explications, les commentaires et le forum sont là pour ça.

Maîtriser Excel, c'est facile !!!

En discuter sur le Forum?

Print Friendly and PDF

Cet article vous a intéressé, merci de le recommander avec un Google +1 et de le partager sur Facebook, Twitter, Viadeo ou Linkedin, en utilisant les liens mis à votre disposition.

Pour suivre nos prochains articles abonnez-vous au Blog ou inscrivez-vous au flux RSS.

En savoir plus, consulter l'actualité de Excel-Dev sur :
- Facebook :Excel-Ligue1, Excel-Dev
- Twitter : Le blog Excel-Dev

Restez informé, abonnez-vous à la newsletter, entrez votre adresse mail:


Delivered by FeedBurner