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))

ASTUCE EXCEL: SUPPRIMER LES LIGNES VIDES par Sophie Marchand Si vous travaillez avec un tableau Excel et que vous désirez supprimer les lignes vides, il existe une fonctionnalité à cet effet. Bien sûr, vous pourriez utiliser des détours, comme une fonctionnalité de tri ou de filtre, mais il existe une fonctionnalité encore plus performante. Il y a quelques temps, je vous ai présenté la fonctionnalité "Atteindre" ("Go to"). Je vous suggère d’abord de relire cet article. C’est cette fonctionnalité que nous utiliserons pour supprimer les lignes vides d’un tableau de données. 1 – Sélectionnez votre tableau (ci-bas de B4 à L27) 2 – Cliquez sur F5 pour faire apparaître la boîte de dialogue de la fonctionnalité "Atteindre" ("Go to") et cliquez sur "Cellules.." 3 – Choisissez "Cellules vides" ("Blanks") 4 – Cliquez sur CTRL + le signe "-" 5 – Sélectionnez ensuite "Déplacer les cellules vers le haut" ("Shift cells up") 6 – Vous obtiendrez ainsi un tableau départi de ses lignes vides Vous aimez cet article? Sophie Marchand, M.Sc., CPA, CGA

FONCTIONS EXCEL: RECHERCHEV, RECHERCHEH ET EQUIV par Sophie Marchand Si vous ne connaissez pas encore l’utilité des fonctions RECHERCHEV (vlookup en anglais) et RECHERCHEH (hlookup en anglais), il vous manque définitivement un outil précieux dans votre boîte à outils! Ces deux fonctions permettent notamment de faire des recherches très efficaces et d’automatiser bon nombre de processus. Cela dit, comme toutes les fonctions d’Excel, ces dernières sont davantage intéressantes lorsqu’utilisées en combinaison avec d’autres fonctions. Recherchev: Cette fonction permet de chercher une valeur dans la première colonne d’un tableau de données et d’en extraire une autre valeur, sur la même ligne (la deuxième valeur, la huitième valeur, etc., selon vos spécifications). Rechercheh: Cette fonction est identique à la fonction précédente sauf qu’elle cherche une valeur dans la première ligne d’un tableau et renvoie une valeur dans cette même colonne (la deuxième valeur, la huitième valeur, etc., selon vos spécifications). Equiv: . Like this: Dans "Excel"

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

TABLEAUX CROISÉS DYNAMIQUES: 5 TYPES DE CALCULS par Sophie Marchand Si vous utilisez fréquemment Excel, vous savez que vous pouvez utiliser les tableaux croisés dynamiques pour compiler et présenter vos données sous différentes dimensions. La plupart des usagers présentent les données sous les formats déjà présents dans la base de données sous-jacente. 5 types de calculs Excel dispose de commandes pour présenter vos données selon 5 grands types de calculs: SommeCompteMoyenneMaxMin Par exemple, en partant d’une base de données comprenant 60 000 transactions de ventes pour 10 marchés différents, Excel nous permet de présenter les données comme suit: Procédure Pour afficher les 5 types de calculs précédents, vous devez d’abord ajouter 5 fois le champs numérique sous-jacent (dans ce cas-ci les ventes ou "sales amount") dans la zone "Valeurs" (ou "Values" en anglais). Ensuite, pour chacun des champs, vous devez cliquer sur l’option "Paramètres des champs de valeurs" ou "Value Field Settings" en anglais. Vous aimez cet article? Like this:

FONCTION EXCEL : SOMMEPROD (SUMPRODUCT) par Sophie Marchand Saviez-vous qu’il existe une façon très simple d’additionner une série de multiplications dans Excel? En effet, la fonction SOMMEPROD (en français) ou SUMPRODUCT (en anglais) vous évite d’additionner une par une les multiplications d’une série de données. Par exemple, si vous devez multiplier des coûts unitaires par des quantités et ensuite les additionner, cela peut se faire en une seule étape, tel qu’illustré ci-bas. Si vous avez des questions, n’hésitez pas à me rejoindre. Les articles suivants pourraient vous intéresser:Fonction Excel: Somme.si (sumif)Astuce Excel: Somme.si (sumif) avec plusieurs conditionsExcel: Additionner plusieurs onglets en un clic de sourisFonction Excel: Décaler (Offset)Fonction Excel: Index/Equiv (Index/Match)Fonctions Excel: Recherchev, Rechercheh et Equiv Like this: J'aime chargement… Sur le même thème Dans "Excel" À LA DEMANDE GÉNÉRALE .

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.

Créer une liste déroulante à partir d'une plage de cellules Pour faciliter la saisie des données ou pour limiter les entrées aux éléments que vous définissez, vous pouvez créer une liste déroulante d'entrées valides compilées à partir de cellules situées ailleurs dans le classeur. Lorsque vous créez une liste déroulante pour une cellule, celle-ci affiche une flèche dans la cellule. Pour entrer des informations dans cette cellule, cliquez sur la flèche, puis sur l'entrée voulue. Pour créer une liste déroulante à partir d'une plage de cellules, utilisez la commande Validation des données intégré dans le groupe Outils de données de l'onglet Données. Pour créer une liste d'entrées valides pour la liste déroulante, tapez les entrées dans une seule colonne ou une ligne ne contenant pas de cellules vides. Par exemple : Définition d'un nom Sélectionnez la cellule dans laquelle créer la liste déroulante. La boîte de dialogue Validation des données s'affiche. Cliquez sur l'onglet Paramètres. Affichage d'un message de saisie Remarques

Créer un tableau croisé dynamique avec Excel 2010 - Bureautique Dans cet article, nous allons décrire comment créer facilement des tableaux croisés dynamiques avec Excel 2010. Un tableau croisé dynamique permet de réaliser rapidement des tableaux de synthèse sur des volumes de données importants. Grâce à ce type de tableau, vous aurez la possibilité de calculer des sous-totaux et agréger des données chiffrées. Les résultats du tableau pourront être modifiés en temps réel en créant des filtres par catégorie. Note : Un article présentant les tableaux croisés dynamiques sur Excel 2000 est également disponible sur notre site. Afin d'illustrer notre article, nous allons utiliser un fichier Excel représentant le stock d'une librairie. Aperçu : Création du tableau croisé dynamique : Sélectionnez une cellule dans votre tableau source (A2 par exemple). Note : le fait de sélectionner une cellule du tableau permet de remplir automatiquement le champ Tableau/Plage. Si la plage de données est incorrecte, vous pouvez la modifier en cliquant sur Aperçu du tableau :

Related: