background preloader

Decaler : une fonction vraiment indispensable.

Decaler : une fonction vraiment indispensable.
DECALER(départ;décalage bas;décalage droite; hauteur; largeur) Définition La fonction DECALER (offset en anglais) sert à définir une plage de cellules dans la feuille excel en partant d'une cellule de départ, on décale la référence de cette cellule d'un certain nombre de lignes et de colonnes, ce qui définit la cellule en haut à gauche de la plage et enfin on indique la largeur et la hauteur de la plage. Attention si on écrit dans la feuille de calcul =DECALER($B$2; 7 ; 2 ; 16 ; 3) Excel renvoie une valeur d'erreur car excel ne sait pas que faire d'une plage de cellule inscrite de cette façon. Pour tester votre compréhension de cette formule, inscrivez quelque chose dans chacune des cellules de la plage en bleu et écrivez dans la feuille =NBVAL(DECALER($B$2; 7 ; 2 ; 16 ; 3)) Excel évalue cette formule, remplace DECALER($B$2; 7 ; 2 ; 16 ; 3) par la plage renvoyée : D9:24 et renvoie le nombre de cellules contenant une valeur dans cette plage (16x3=48). Paramètres Dans l'exemple ci-dessus Usage Related:  Liste déroulante - fonction décalerTableurs

Les fonctions Recherche et Matrices : DECALER - Bureautique > EXCEL : Les fonctions utiles - Fiches Pratiques Descriptif : Cette fonction permet de renvoyer une plage de cellules qui correspond à un nombre déterminé de lignes et de colonnes. La fonction DECALER n'a pas pour vocation de décaler physiquement des cellules. Elle permet simplement de renvoyer une référence à une plage de cellules. Syntaxte : DECALER(réf;lignes;colonnes;hauteur;largeur) réf : Correspond à la référence à partir de laquelle le décalage doit être effectué.lignes : Correspond au nombre de lignes de décalage par rapport à la première celulle de la plage réf (la cellule supérieure gauche). Pour mieux comprendre l'intérêt de cette fonction, nous allons observer deux exemples concrets d'utilisation. Premier exemple : Soit un tableau représentant le volume des ventes par période. Aperçu du tableau d'origine : Note : La feuille de calcul s'appelle RECAP. Pour simplifier la compréhension de l'exemple, nous allons définir des noms pour nos plages de cellules. Aperçu : Plage PERIODE :réf : RECAP! Plage VENTES :réf : RECAP! Soit :

Excel formules mathematiques · Deuxième plage de critères, la colonne du sexe, la colonne B (dans la formule B:B), · Deuxième critère, le sexe saisie en cellule F2, La formule =SOMMEPROD() Réalisation d'opérations conditionnelles comptant et additionnant en utilisant des conditions multiples Les formules permettant de calculer le nombre de valeurs ou les sommes sont fondées sur deux conditions ou plus. La fonction que nous allons utiliser est SOMMEPROD ; celle-ci appartient à la catégorie Math & Trigo. Exemple : Compter le nombre de mois où un commercial fait une vente. Exemple 1 : Compter le nombre des ventes faites par Jules sur le mois de février. C'est une formule conditionnelle qui emploie deux conditions. = SOMMEPROD((A2:A13="Jules")*(B2:B13="Février")) Résultat de formule: 2 Exemple 2 : Compter les mois où les ventes sont plus grandes qu'une quantité spécifique Vous pouvez employer une formule semblable à celle ci-dessus pour travailler avec des données numériques. = SOMMEPROD((B2:B13="Janvier")*(C2:C13>200))

La gestion des doublons dans Excel Comment gérer les doublons? Cette question est récurrente lorsque l'on souhaite traiter et analyser des données, que ce soit pour les compter, les filtrer, les regrouper ou les supprimer. Ce tutoriel présente quelques solutions disponibles dans l'application Excel: * Les fonctions intégrées. * Les formules. * Les macros. Plusieurs exemples sont transposables dans différents chapitres et vous pourrez aisément les adapter à vos projets. II-A. Le menu Validation: empêcher la saisie de doublons dans une plage de cellules▲ Vous pouvez utiliser les options du menu Validation pour empêcher la saisie de doublons dans une plage de cellules: Par exemple dans Excel2002: Sélectionnez la plage de cellules A1:A10. Utilisez le menu Données Option Validation Onglet Options Sélectionnez l'option Personnalisé dans la liste de choix. Saisissez dans le champ Formule: =NB.SI($A$1:$A$10;A1)<2 Cliquez sur le bouton OK pour valider. II-B. II-B-1. Sélectionnez la colonne A. II-B-2. II-B-3. II-C. II-D. IV-A. IV-B. IV-C.

Fonction avancée : DECALER Exemple 4 Récupérer la valeur d'une cellule en fonction d'une origine et de déplacements "nord-sud-est-ouest". Quand la valeur est récupérée en E8, elle est mise en évidence (en bleu) dans la plage d'origine via une mise en forme conditionnelle. Remarquez les objets compteurs associés aux cellules E2, E3, E5 et E6. Formule en F2 : Elle permet de fixer le sens du déplacement Formule en F3 : =SI(ET(E3<>0;E2<>0);"err";"") Elle permet de détecter une incohérence : on monte et on descend. Formule en F5 : Formule en F6 : =SI(ET(E6<>0;E5<>0);"err";"") Elle permet de détecter une incohérence : on se déplace à gauche et à droite. Formule en E8 =SI(OU(F3="err";F6="err");"impossible";DECALER(E18;F2;F5;1;1)) 1° Le SI permet d'éviter un déplacement absurde (par exemple, monter et descendre simultanément !).

Gestion des onglets et des classeurs Sheets.Add ajoute un onglet au classeur actif. Application.DisplayAlerts = False ' supprime les messages d'avertissement Sheets(1).Delete Sheets("xxx").Delete Sheets.count donne le nombre d'onglets du classeur actif. Sheets("Feuil2").Copy Before:=Sheets(3) Sheets("Feuil1").Copy ' Copie l'onglet dans dans un nouveau classeur Workbooks.Open(fichier, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad, OpenConflictDocument Ouvre le fichier spécifié. Ferme le fichier spécifié. -Créer un nom de champ NBFeuilles =LIRE.CLASSEUR(4)&INDIRECT("iv65000") Dans une cellule du tableur =NbFeuilles -Créer un nom de champ NomsFeuilles =STXT(LIRE.CLASSEUR(1);TROUVE("]";LIRE.CLASSEUR(1))+1;99)&INDIRECT("iv65000") -Sélectionner des cellules horizontales =NomsFeuilles -Valider avec Maj+Ctrl+entrée On veut créer un onglet pour chaque service. Création d’onglets à partir d’un modèle CréationFichesModèle.xls

Show Details On-demand in Excel [Tutorial + Training Program] Posted on April 7th, 2011 in Charts and Graphs , products , VBA Macros - 47 comments Yesterday, we have seen a beautiful example of how showing details (like distribution) on-demand can increase the effectiveness of your reports. Today, we will learn how to do the same in Excel. Before jumping in to the tutorial, In this post, I have explained one technique of using charts + VBA to dynamically show details for a selected item. There are 4 other ways to do the same – viz. using cell comments, pivot charts, group / un-group feature and hyperlinks. Click here to get the video training – Showing on-demand details in Excel [Alternative payment link] How does the on-demand details chart work – demo: This is a replica of yesterday’s chart from Amazon. Creating this chart in Excel – Step-by-step Instruction So you are ready to learn how to do this chart? 1. This is how I have setup the source data for the chart. All this data is in the range Table1. 2. The formula is quite simple. Displaying Circles:

Listes en cascade Accueil La technique classique des listes en cascade utilise les noms de champs et la fonction =Indirect() La maintenance des noms de champs en cas d'ajout/suppression peut devenir fastidieuse lorsque le nombre de champs devient important. En outre, les listes en cascade avec Indirect() ne supportent pas les champs dynamiques. L'utilisation de la fonction Decaler() évite le nommage des listes et la gestion des contraintes sur les noms de champs(pas d'espace ou de caractères spéciaux) L'utilisation d'une BD et de la fonction Decaler() facilite la maintenance des listes en cascade et permet en outre la récupération d'informations associées aux listes (le prix d'une référence produit par ex). Listes en cascade avec noms de champ et Indirect() La liste des modèles en B2 dépend de la marque choisie en A2: Cascade_indirect.xls Cascade_indirect Formulaire.xls - Nommer Marque le champ E2:E4 (Sélection E2:E4 puis Insertion/Nom/Définir) -Les listes nommées peuvent être sur un autre onglet. Avec Prix ou

Formation Excel VBA JB Recherche plus rapide dans un ComboBox grâce à la saisie intuitive (type Google) 1- Données/validation permet la saisie intuitive (semi-automatique) . Avec la formule: =DECALER(Liste;EQUIV(A2&"*";Liste;0)-1;;NB.SI(Liste;A2&"*")) -En frappant les premières lettres et en cliquant sur la flèche, on obtient la liste des items commençant par les lettres frappées. 2- Avec un ComboBox, l'AutoCompletion (MatchEntryComplete) permet en frappant la ou les premières lettres de positionner le combobox sur le premier item mais n'affiche pas les autres items qui correspondent aux lettres frappées. 3- Dans les exemples ci dessous, l'affichage des items d'une liste déroulante intuitive se fait au fur et mesure de la frappe des caractères comme sur le moteur de recherche Google dans un Combobox situé dans le tableur ou dans un formulaire. Ce type de recherche intuitive est également connu sous le nom de saisie semi-automatique, recherche intelligente.

Macro fusion plusieurs fichiers excel Bonjour, Je souhaite fusionner 6 fichiers excel en un seul. Mes fichiers à fusionner s'appellent "C1, C2, C3, C4, C5 et C6.xls". Chaque fichier comporte plusieurs onglet mais dans un 1er temps, seule l'onglet "Feuil1" est à récupérer, sans prendre l'en-tête (à partir de A2). Les 6 fichiers évoluent régulièrement et indépendamment, le nombre de lignes change (pas le nombre de colonnes) il faut donc regrouper toutes les lignes les unes derrière les autres.

Excel - la fonction SOMME.SI nommage des cellules Ce travail se prête bien au nommage de cellules. C'est à dire au remplacement d'une plage de cellules par un nom déterminé. Pour ce faire, nous allons : sélectionner la plage A3:B18, choisir : onglet Formules + groupe Noms définis + Définir un nom, nommer la plage tableau. fonction NB.SI La fonction NB.SI permet de compter le nombre de cellules situées à l'intérieur d'une plage qui répondent à un certain critère. Observons la cellule E3 : Il s'agit de compter, dans la plage de cellule A3:A18, nommée tableau, le nombre de fois où apparaît la chaîne de caractères "Indre" figurant en A3. Nous écrivons donc en E3 =NB.SI(tableau;A3) La syntaxe générale est la suivante : =NB.SI(plage_de_cellules;critère) note : le critère retenu peut être une cellule, une valeur numérique ou une chaîne de caractères (ne pas oublier les " "). fonction SOMME.SI La fonction SOMME.SI permet d'additionner des cellules d'une plage si elles répondent à un critère donné. Observons la cellule F3 :

La mise en forme conditionnelle Accueil La mise en forme conditionnelle complète la commande Format/Cellule/Nombre. Mise en forme conditionnelle simple Modifier la mise en forme des nombres compris entre 2 valeurs On veut mettre en gras et fond bleu les nombres compris entre 100 et 200 -Selectionner B3:B9 -Format/Mise en forme conditionnelle -La valeur de la cellule est comprise entre 100 et 200 -Cliquer sur le bouton Format et choisir la mise en forme. Colorier les cellules contenant une chaîne de caractères particulière On veut colorier en jaune les cellules contenant la lettre C. Sélectionner le champ à colorier Format/Mise en forme conditionnelle Colorier les lignes pour lesquelles la date est supérieure à la date du jour -Sélectionner le champ -Format/mise en forme conditionnelle -La formule est: =$C2>AUJOURDHUI() Colorier les week-end Pour faire apparaître en couleur les Samedi et Dimanche: -Sélectionner le champ A1:A30 -Format/Mise en forme conditionnelle -Choisir La formule est -=OU(JOURSEM(A1)=1;JOURSEM(A1)=7) ou MFCInclus

Fonction Excel : INDEX + EQUIV La fonction Excel INDEX utilisée avec la fonction EQUIV permet la recherche de valeur dans un tableau. Prérequis : Avant de passer à la suite, consultez le tutoriel de la fonction INDEX ainsi que celui de la fonction EQUIV. Pour vous aider à mieux comprendre, l'exemple utilisé ici est une combinaison des exemples des 2 tutoriels indiqués, leur lecture est donc vivement recommandée. Combinaison INDEX + EQUIV : Si la fonction EQUIV renvoie le numéro de ligne : =INDEX(tableau; EQUIV(valeur_recherchée; plage_de_recherche; 0); no_colonne) Si la fonction EQUIV renvoie le numéro de colonne : =INDEX(tableau; no_ligne; EQUIV(valeur_recherchée; plage_de_recherche; 0)) Exemple d'utilisation : L'objectif ici est de trouver le numéro de dossier ainsi que le nombre de points du lieu recherché. Sélectionnez pour commencer la fonction INDEX : Dans "Matrice", entrez la plage de cellules du tableau. Positionnez-vous dans "No_lig" et ajoutez la fonction EQUIV en cliquant ici :

Sommaire Excel Excel est le très puissant et convivial tableur de Microsoft. C'est grâce à lui que des milliers d'entreprises s'appuient pour faire leurs comptes, statistiques, listes, et autres graphiques et tableaux. Adoré par les uns pour sa puissance, détesté par les autres pour son apparente complexité, Excel devient votre plus fidèle allié pour peu que vous vous initiez à ses finesses. NOUVEAU : Les modèles Excel - Comment les utiliser ? NOUVEAU : Non-correspondance - Mettez en surbrillances le svaleurs de cellules non correspondantes dans deux colonnes Calendrier perpétuel - Jean-Claude Ring vous offre ce calendrier perpétuel. Sudoku Help - Jean-Claude Ring propose ici de vous aider à terminer vos Sudoku. Meteor - Un tout petit jeu VBA Excel sans prétention. Créez un historique de toutes les modifications apportées dans un classeur, et apprenez ainsi à partager un classeur en lecture-écriture avec plusieurs utilisateurs simultanément. Cliquez ici si vous cherchez des infos sur VBA Excel Avec VBA)

Tutoriels Nommer une Plage Dynamique Selon les cas, la taille d'une plage n'est pas forcément connue à l'avance, et est susceptible d'évoluer. La taille des plages nommées peut être ainsi variable et nous allons voir comment gérer ce type de références. Pour illustrer cette situation, prenons un exemple : Les ouvrages d'une collection se répartissent sur plusieurs gammes de A à D, chaque gamme correspond à un prix, indiqué dans le tableau Tarif. Dans le tableau Chiffre d'Affaires, nous voulons calculer le CA généré par chaque ouvrage, en fonction de son prix indiqué dans le tableau Tarif : Pour les calculs du CA, nous avons donc besoin de nommer les plages : Gamme (actuellement de C19 à C22)Prix_Tarif (actuellement de D19 à D22) Cependant, nous savons que le tarif va s'enrichir d'autres gammes, mais nous ne savons pas dans quelles proportions. Nommer directement les plages Gamme sur C19:C22 et Prix_Tarif sur D19:D22 n'est donc pas une solution idéale. La formule =NBVAL($C:$C) renvoie 5

Related: