Fonctions Excel VBA. Bases de VBA : fonctions intégrées et définies par l'utilisateur. Créer une fonction en VBA avec un argument

La section « Structure », en plus de regrouper et de dissocier les données, a une autre fonction – « Sous-totaux ». Avec son aide, vous pouvez définir un regroupement automatique des données avec un résumé de celles-ci. Pour que ce dont nous parlons soit plus clair, considérons comme exemple un tableau pour les salariés avec leurs résultats de ventes pour le trimestre.

En sélectionnant le tableau et en cliquant sur l'icône « Sous-totaux », Excel affichera la fenêtre suivante :

Dans le champ « Pour chaque changement de : », vous devez préciser le titre de la colonne du tableau par lequel la synthèse sera déterminée. Les cellules des lignes supérieures du tableau sélectionné font office d'en-tête. Les étiquettes dans les cellules de la colonne spécifiée serviront d'en-têtes de ligne. Le programme vérifiera la similitude des titres. Si les en-têtes de ligne correspondent, alors il l'ajoute au calcul total ; s'ils ne correspondent pas, alors le résultat pour le groupe défini par un en-tête est affiché dans une ligne supplémentaire. Par conséquent, nous vous recommandons de trier à l’avance cette colonne.

Dans le deuxième champ « Opération : » vous sélectionnez quel total doit être additionné : Somme, quantité, moyenne, etc.. Dans le dernier champ « Ajouter le total par : » vous sélectionnez la colonne qui contient les données nécessaires pour afficher le résultat. .

Vous trouverez ci-dessous 3 cases à cocher (drapeaux) :

Remplacer les totaux actuels – s'ils sont définis, les résultats résumés précédemment seront remplacés, sinon, de nouveaux résultats seront ajoutés aux anciens ;
Fin de page entre les groupes – si défini, une fois imprimé, chaque groupe séparé avec le total sera situé sur une nouvelle feuille ;
Totaux sous les données – si défini, le résultat sera situé à la fin du groupe, sinon au début.

Le bouton Supprimer tout supprime toute la structure de données.

Voici comment la table a été transformée après lui avoir appliqué la fonction décrite :

Les nouvelles cellules ajoutées contiennent des mathématiques fonction SOUS-TOTAL(), que vous trouverez dans l'article sur les fonctions mathématiques - Fonctions mathématiques Excel.

Le mécanisme de synthèse des sous-totaux présente des caractéristiques communes avec un autre outil plus puissant de l'application Excel -

Cet article décrit la syntaxe de la formule et l'utilisation des fonctions SOUS-TOTAUX dans Microsoft Excel.

Description

Renvoie un sous-total à une liste ou une base de données. Il est généralement plus facile de créer une liste de sous-totaux à l'aide de la commande du bureau Excel. Sous-totaux en groupe Structure sur l'onglet Données. Mais si une telle liste a déjà été créée, elle peut être modifiée en changeant la formule avec la fonction SOUS-TOTAL.

Syntaxe

Les arguments de la fonction SOUS-TOTAL sont décrits ci-dessous.

Numéro_fonction- argument requis. Un nombre compris entre 1 et 11 ou entre 101 et 111 qui représente la fonction utilisée pour calculer les sous-totaux. Les fonctions 1 à 11 prennent en compte les lignes masquées manuellement, tandis que les fonctions 101 à 111 ignorent ces lignes ; les cellules filtrées sont toujours exclues.

Numéro_fonction (y compris les valeurs cachées)	Numéro_fonction (hors valeurs cachées)	Fonction





		PRODUIT
		ÉCART-TYPE
		STDEV

Remarques

S'il existe déjà des formules de sommation dans les arguments "lien1;lien2;..." (totaux imbriqués), ces totaux imbriqués sont ignorés pour éviter une double sommation.

Pour les constantes function_number de 1 à 11, la fonction SOUS-TOTAL prend en compte les valeurs des lignes masquées à l'aide de la commande Masquer les lignes(menu Format, sous-menu Masquer ou afficher) en groupe Cellules sur l'onglet maison dans l'application de bureau Excel. Ces constantes sont utilisées pour obtenir des sous-totaux basés sur les nombres masqués et non masqués dans la liste. Pour les constantes function_number 101 à 111, la fonction SOUS-TOTAL exclut les valeurs des lignes masquées par la commande Masquer les lignes. Ces constantes permettent d'obtenir des sous-totaux, en prenant en compte uniquement les numéros non masqués de la liste.

La fonction SUBTOTAL exclut toutes les lignes qui ne sont pas incluses dans le résultat du filtre, quelle que soit la valeur de la constante function_number utilisée.

La fonction SOUS-TOTAL s'applique aux colonnes de données ou aux ensembles de données verticaux. Il n'est pas destiné aux lignes de données ou aux ensembles de données horizontaux. Ainsi, lors de la définition des sous-totaux d'un ensemble de données horizontales à l'aide de la valeur constante function_number de 101 et supérieure (par exemple, SUBTOTAL(109,B2:G2)), le masquage de la colonne n'affectera pas le résultat. Cependant, cela sera affecté par le masquage de la ligne lors du sous-total d'un ensemble de données vertical.

Exemple

Copiez les exemples de données du tableau suivant et collez-les dans la cellule A1 d'une nouvelle feuille de calcul Excel. Pour afficher les résultats des formules, sélectionnez-les et appuyez sur F2, puis appuyez sur Entrée. Si nécessaire, modifiez la largeur des colonnes pour voir toutes les données.

Données




Formule	Description	Résultat
SOUS-TOTAL(9;A2:A5)	La valeur du sous-total de la plage de cellules A2:A5, obtenue en utilisant le nombre 9 comme premier argument.
SOUS-TOTAL(1;A2:A5)	La moyenne du sous-total de la plage de cellules A2:A5, obtenue en utilisant le chiffre 1 comme premier argument.
Remarques
Le premier argument de la fonction SOUS-TOTAL doit être une valeur numérique (1-11, 101-111). Cet argument numérique est utilisé pour sous-totaliser les valeurs (plages de cellules, plages nommées) spécifiées comme arguments suivants.

Lorsque vous travaillez dans Excel, il est souvent nécessaire de résumer les sous-totaux dans un tableau. Voyons comment cela peut être réalisé à l'aide d'un exemple spécifique.

Exigences du tableau pour l'utilisation des sous-totaux

Tous les tableaux n'ont pas la possibilité d'utiliser la fonction de calcul des sous-totaux. Vous trouverez ci-dessous une liste des principales exigences obligatoires pour les tableaux :

Pas de cellules vides, c'est-à-dire toutes les lignes et colonnes doivent être remplies de données.
Vous ne pouvez pas utiliser plusieurs lignes dans un en-tête de tableau. Il doit être représenté sur une seule ligne. Et son emplacement compte aussi. Cela ne devrait être que sur la toute première ligne et nulle part ailleurs.
Le format du tableau doit être présenté sous la forme d'une zone régulière de cellules.

Alors, maintenant que nous avons décidé des principaux critères de « pertinence » des tableaux, commençons à calculer les sous-totaux.

Disons que nous avons un tableau avec les résultats des ventes de produits, ventilés ligne par ligne et par jour. Vous devez calculer les ventes totales de tous les articles pour chaque jour, puis calculer les ventes totales pour tous les jours.

Marquez n'importe quelle cellule du tableau, passez à l'onglet « Données », recherchez la section « Structure », cliquez dessus et dans la liste qui s'ouvre, cliquez sur l'option « Sous-total ».
En conséquence, une fenêtre apparaîtra dans laquelle nous effectuerons d'autres réglages en fonction de notre tâche.
Nous devons donc calculer les ventes quotidiennes de tous les produits. Les informations sur la date de vente sont placées dans la colonne du même nom. Sur cette base, nous remplissons les champs de paramètres requis.
Suite aux actions entreprises, le tableau affichera les sous-totaux par groupe (par date). En face de chaque groupe, vous pouvez voir une icône moins qui, lorsque vous cliquez dessus, réduira les lignes qu'il contient.
Si vous le souhaitez, vous pouvez supprimer les données inutiles du champ de visibilité, ne laissant que le total général et les sous-totaux. En cliquant sur le bouton « plus », vous pouvez étendre les lignes à l'intérieur des groupes.

Note: Après avoir apporté des modifications et ajouté de nouvelles données, les sous-totaux seront recalculés automatiquement.

Tout d’abord, marquez la cellule où le total du comptage doit être affiché. Ensuite, cliquez sur l'icône « Insérer une fonction » ( effets) à côté de la barre de formule sur le côté gauche.
L'assistant de fonction s'ouvrira. Sélectionnez la catégorie « Liste alphabétique complète », recherchez la fonction « TOTAL INTERMÉDIAIRE » dans la liste proposée, placez le curseur dessus et cliquez sur OK.
Vous devez maintenant définir les paramètres des fonctions. Dans le champ « Function_number » nous indiquons le numéro qui correspond à l'option de traitement de l'information souhaitée. Il y a onze options au total :
Dans le champ « Référence 1 », indiquez les coordonnées de la plage pour laquelle vous souhaitez calculer les totaux. Au total, vous pouvez spécifier jusqu'à 255 plages. Après avoir renseigné les coordonnées du premier lien, une ligne apparaîtra pour ajouter le suivant. La saisie manuelle des coordonnées n'est pas très pratique et il existe une forte probabilité de se tromper. Par conséquent, nous plaçons simplement le curseur dans le champ de saisie des informations, puis, avec le bouton gauche de la souris, marquons la zone de données souhaitée. De même, vous pouvez ajouter les liens suivants si nécessaire. Une fois terminé, confirmez les paramètres en appuyant sur le bouton OK.
En conséquence, la cellule contenant la formule affichera le résultat du calcul des sous-totaux.

Note: Comme les autres fonctions Excel, vous pouvez utiliser « SOUS-TOTAL » sans recourir à l'assistant de fonction. Pour ce faire, saisissez manuellement une formule dans la cellule souhaitée qui ressemble à ceci.

La fonctionnalité de sous-total d'Excel est un moyen simple et pratique d'analyser les données dans une colonne ou un tableau spécifique.

Les calculs intermédiaires réduisent le temps de calcul et vous permettent de visualiser rapidement les données sur le montant, la quantité de marchandises, le revenu total, etc.

D'ailleurs, pour travailler plus efficacement avec les tableaux, vous pouvez vous familiariser avec notre matériel.

Exceller contiennent une grande quantité d’informations. Il est souvent peu pratique de visualiser et de rechercher des données.

Pour faciliter la vie des utilisateurs, les développeurs du programme ont créé des fonctions de regroupement des données.

Avec leur aide, vous pouvez facilement rechercher les champs et les colonnes dont vous avez besoin, ainsi que masquer les informations qui ne sont pas nécessaires pour le moment.

Vous pouvez résumer plusieurs groupes de feuilles à l'aide de la fonction "Sous-totaux".

Regardons de plus près le principe de fonctionnement de cette fonction et sa mise en œuvre. Exceller.

Syntaxe de la fonction

Dans Excel l'option s'affiche commeRÉSULTATS.INTERMÉDIAIRES (N°; lien 1; lien 2; lien 3;…; lien N ) , où le numéro est la désignation de la fonction, le lien est la colonne par laquelle le résultat est résumé.

Les champs de lien peuvent inclure un maximum de 29 liens et espacements utilisés dans l'exécution des sous-totaux.

Le nombre peut être n'importe quel nombre compris entre 1 et 11. Ce numéro indique au programme lequel doit être utilisé pour calculer les totaux dans la liste sélectionnée.

Syntaxe des options de totaux indiqué dans le tableau :

Syntaxe	Action
1-MOYENNE	Déterminer la moyenne arithmétique de deux ou plusieurs valeurs.
2-COMPTE	Calculez le nombre de nombres présentés dans la liste d’arguments.
3-COMPTE	Comptez tous les arguments non vides.
4-MAX	Affiche la valeur maximale d'un ensemble défini de nombres.
5 MINUTES	Affiche la valeur minimale d'un ensemble défini de nombres.
6-PRODUIT	Multiplie les valeurs spécifiées et renvoie le résultat.
7-DÉVIATION STD	Analyse de l'écart type de chaque échantillon.
DÉVIATION À 8 STADES P	Analyse des écarts pour l'ensemble des données totales.
9-SOMME	Renvoie la somme des nombres sélectionnés.
10-DISP	Analyse de la variance de l'échantillon.
11-DISPR	Analyse de la variance commune.

Pour le travail "Résultats intermédiaires" Avec les fonctions déjà utilisées, entre parenthèses d'arguments, il suffit d'indiquer uniquement le numéro de la fonction, et non sa désignation par lettre.

Exemple de mise en œuvre de sous-totaux

Sous-totaux en cours d'exécution comprend les opérations suivantes :

Regroupement de données ;
Création de résultats ;
Création de niveaux pour les groupes.

Regroupement de tables

Groupe de tableaux- c'est une liste Exceller . L'avantage de cette fonction est que lorsque vous déplacez une ligne de la liste des éléments groupés, le groupe entier sera déplacé vers le nouvel emplacement. Les calculs sont effectués de la même manière à l'aide de fonctions standards - pour tous les groupes ensemble.

Suivez les instructions pour regrouper les lignes. Dans notre cas, on relie les colonnes A, B, C :

1 A l'aide de la souris, sélectionnez les colonnes A, B, C, comme le montre la figure ci-dessous.

2 Maintenant, en gardant la sélection, Ouvrez le champ « Données » dans la barre d'outils du programme. Ensuite, sur le côté droit de la fenêtre des options, recherchez l'icône "Structure" et cliquez dessus ;

3 Dans la liste déroulante cliquez sur "Groupe". Si vous vous êtes trompé au stade de la sélection des colonnes du tableau souhaitées, cliquez sur "Dissocier" et répétez l'opération à nouveau.

4 Dans une nouvelle fenêtre dans sélectionnez « Chaînes » et cliquez sur "D'ACCORD";

5 Maintenant encore sélectionner les colonnes A, B, C et procéder à un regroupement, mais déjà par colonnes :

À la suite de l'action, toutes les lignes précédemment sélectionnées forment un nouveau groupe d'éléments. Un panneau de contrôle de groupe apparaîtra à côté des colonnes et des lignes. Avec son aide, vous pouvez masquer des détails ou afficher des éléments cachés. Pour cela, cliquez sur les signes « - » et « + » à gauche des lignes ou au dessus des colonnes.

Création de sous-totaux

L'option sous-total, grâce à l'utilisation de groupes et de onze fonctions standards (tableau 1), permet de résumer rapidement les données du tableau complété.

Regardons l'utilisation pratique de cette fonction.

Par exemple, nous devons savoir combien d’articles de chaque taille ont été achetés par les clients sur le site d’une boutique en ligne. Notre fiche en Exceller est une base de données théorique qui reflète tous les clients, le type de paiement et les articles qu'ils ont commandés.

Le résultat de l'action sera l'apparition de groupes triés par taille d'élément. Suivez les instructions:

À PROPOS déterminez pour quelles données vous devez effectuer les calculs finaux. Dans notre cas c'est la colonne "Taille". Son contenu doit être trié. Le tri s'effectue du plus gros élément au plus petit. Sélectionnez la colonne « Taille »;

Trouver le terrain "Tri" et cliquez dessus ;

Dans la fenêtre qui apparaît, sélectionnez "Trier par plage de valeurs spécifiées" et appuyez sur la touche "D'ACCORD".

En conséquence, toutes les tailles seront affichées par ordre décroissant. Ce n'est qu'après cela que nous pourrons effectuer des résultats intermédiaires qui affectent les données du tableau des tailles des articles commandés.

Maintenant passons à autre chose pour mettre en œuvre la fonction « Sous-total » :

Dans la barre d'outils du programme, ouvrez le champ « Données » ;
Sélectionnez la vignette « Structure » ;
Presse "Sous-totaux";

Ensuite, une fenêtre s'ouvrira pour configurer et afficher les sous-totaux. Dans le premier champ, sélectionnez le nom de la colonne pour laquelle vous résumerez. Dans notre cas, il s'agit de "Taille". À chaque changement dans ce champ, les données changeront dans les totaux ;

Dans le champ "Opération" sélectionnez le type de fonction, qui s'applique à tous les éléments de la colonne sélectionnée dans le premier champ. Pour calculer les dimensions, vous devez utiliser le comptage de quantités ;

Dans le grincement "Ajouter les totaux par" vous sélectionnez la colonne dans laquelle les sous-totaux seront affichés. Sélectionnez l'élément « Taille » ;

Après avoir réglé à nouveau tous les paramètres revérifiez les valeurs saisies et cliquez sur OK.

En conséquence, le tableau sera affiché sur la feuille, regroupé par taille. Il est désormais très facile de voir tous les articles commandés par taille Petit, Extra Large et ainsi de suite.

Dans cet exemple, nous regardons un petit tableau, mais en pratique il peut contenir plusieurs milliers de lignes, donc en utilisant des sous-totaux devient un moyen très pratique de sélectionner rapidement des paramètres :

Comme vous pouvez le voir sur la figure ci-dessus, tous les totaux sont affichés entre les groupes affichés sur une nouvelle ligne.

Conclusion pour les vêtements de taille Petit – 5 pièces, pour les vêtements de taille Extra large - 2 pièces. Le nombre total d'éléments est également indiqué sous le tableau.

Niveaux de groupe

Vous pouvez également afficher les groupes par niveau.

Cette méthode d'affichage vous permet de réduire la quantité d'informations visuelles sur l'écran - vous ne verrez que les données totales nécessaires - le nombre de vêtements de chaque taille et la quantité totale.

Cliquez sur le premier deuxième ou troisième niveau dans le panneau de configuration des groupes. Sélectionnez l'option de présentation des informations la plus appropriée :

Premier niveau affiche uniquement le nombre total d'éléments,deuxième – quantité de chaque taille et nombre total,troisième – faites pivoter toutes les données de la feuille.

Supprimer des totaux

Après un certain temps, d'autres données seront saisies dans le tableau, il sera donc nécessaire de regrouper les informations.

Les anciens sous-totaux et leur type de calcul ne seront plus pertinents, il faudra donc les supprimer.

Pour supprimer un total, sélectionnez l'onglet « Données » - onglet « Structure » - "Sous-totaux".

Dans la fenêtre qui s'ouvre, cliquez sur le bouton « Supprimer tout » et confirmez l'action avec le bouton « OK ».

Fig. 13 - suppression de données

Vidéos thématiques :

Lorsque vous travaillez avec des tableaux, il arrive souvent que, en plus des totaux généraux, vous deviez également ajouter des totaux intermédiaires. Par exemple, dans un tableau des ventes de biens pour un mois, dans lequel chaque ligne individuelle indique le montant des revenus provenant de la vente d'un type spécifique de produit par jour, vous pouvez additionner les sous-totaux quotidiens de la vente de tous les produits, et à la fin du tableau, indiquez le montant du revenu mensuel total de l'entreprise. Voyons comment créer des sous-totaux dans Microsoft Excel.

Malheureusement, tous les tableaux et ensembles de données ne conviennent pas à l'application de la fonction de sous-total. Les principales conditions sont les suivantes :

Le tableau doit être formaté comme une zone de cellule normale ;
L'en-tête du tableau doit être constitué d'une seule ligne et être placé sur la première ligne de la feuille ;
Le tableau ne doit pas contenir de lignes contenant des données vides.

Création de sous-totaux dans Excel

Passons au processus lui-même. Une section distincte située sur le panneau supérieur du programme est responsable de l'utilisation de cet outil.

Sélectionnez n'importe quelle cellule du tableau et accédez à l'onglet "Données". Cliquez sur le bouton "Total", qui se trouve sur le ruban dans la boîte à outils "Structure".

Une fenêtre s'ouvrira dans laquelle vous devrez configurer l'affichage des sous-totaux. Dans notre exemple, nous devons afficher le montant des revenus totaux pour tous les produits pour chaque jour. La valeur de la date se trouve dans la colonne du même nom. Ainsi, sur le terrain "A chaque changement" sélectionner une colonne "Date de".
Sur le terrain "Opération" sélectionnez la valeur "Somme", puisque nous devons calculer exactement le montant par jour. En plus du montant, de nombreuses autres opérations sont disponibles, parmi lesquelles : quantité, maximum, minimum, produit.
Puisque les valeurs des revenus sont affichées dans la colonne "Montant des revenus, frottez.", puis sur le terrain "Ajouter les totaux par", sélectionnez-le dans la liste des colonnes du tableau.
De plus, vous devez cocher la case, si elle n'y est pas, à côté du paramètre "Remplacer les totaux cumulés". Cela vous permettra, lors du recalcul du tableau, si vous effectuez la procédure de calcul des sous-totaux avec celui-ci pas pour la première fois, de ne pas dupliquer plusieurs fois l'enregistrement des mêmes totaux.
Si vous cochez la case "Fin de page entre groupes", lors de l'impression, chaque bloc du tableau avec les sous-totaux sera imprimé sur une page séparée.
Lors de l'ajout d'une coche à côté d'une valeur « Résultats sous les données » des sous-totaux seront définis sous le bloc de lignes, dont la somme y est additionnée. Si vous décochez la case, ils seront affichés au-dessus des lignes. Pour la plupart, le placement sous les lignes est plus pratique, mais le choix lui-même est purement individuel.
Une fois terminé, cliquez sur "D'ACCORD".

En conséquence, des sous-totaux sont apparus dans notre tableau. De plus, tous les groupes de lignes unis par un sous-total peuvent être réduits en cliquant simplement sur le signe «-« à gauche du tableau en face du groupe spécifique.

De cette façon, vous pouvez réduire toutes les lignes du tableau, ne laissant visibles que les sous-totaux et les totaux généraux.

Il convient également de noter que si les données des lignes du tableau changent, les sous-totaux seront recalculés automatiquement.

Formule « RÉSULTATS.INTERMÉDIAIRES »

En plus de ce qui précède, il est possible d'afficher les sous-totaux non pas via un bouton sur le ruban, mais en appelant une fonction spéciale via "Insérer une fonction".

Après avoir préalablement cliqué sur la cellule où seront affichés les sous-totaux, cliquez sur le bouton indiqué, qui se situe à gauche de la barre de formule.

Ouvrira "Assistant de fonction", où parmi la liste des fonctions nous recherchons l'élément "SOUS-TOTAUX". Sélectionnez-le et cliquez "D'ACCORD".

Dans une nouvelle fenêtre, vous devrez saisir les arguments de la fonction. En ligne "Numéro de fonction" saisir le numéro d'une des onze options de traitement des données, à savoir :
- 1 — valeur moyenne arithmétique ;
- 2 — nombre de cellules ;
- 3 — nombre de cellules remplies ;
- 4 — valeur maximale dans le tableau de données sélectionné ;
- 5 - valeur minimum;
- 6 — produit des données dans les cellules ;
- 7 — écart type de l'échantillon ;
- 8 — écart type pour la population ;
- 9 - somme;
- 10 — variance de l'échantillon ;
- 11 — dispersion dans la population générale.

Dans la colonne "Lien 1" fournissez un lien vers le tableau de cellules pour lequel vous souhaitez définir des valeurs intermédiaires. Il est permis d'introduire jusqu'à quatre tableaux distincts. Lors de l'ajout des coordonnées d'une plage de cellules, une fenêtre apparaît immédiatement permettant d'ajouter la plage suivante. Comme la saisie manuelle d'une plage n'est pas pratique dans tous les cas, vous pouvez simplement cliquer sur le bouton situé à droite du formulaire de saisie.

La fenêtre des arguments de la fonction sera réduite et vous pourrez simplement sélectionner le tableau de données souhaité avec le curseur. Après sa saisie automatique dans le formulaire, cliquez sur le bouton situé à droite.

La fenêtre des arguments de la fonction réapparaîtra. Si vous devez ajouter un ou plusieurs tableaux de données, utilisez le même algorithme décrit ci-dessus. Sinon, cliquez simplement "D'ACCORD".

Les sous-totaux de la plage de données sélectionnée seront générés dans la cellule dans laquelle se trouve la formule.

La syntaxe de la fonction elle-même est la suivante : INTERMEDIATE TOTAL(function_number, cell_array_addresses) . Dans notre situation, la formule ressemblera à ceci : "INTERMÉDIAIRE.TOTALS(9;C2:C6)". Cette fonction, utilisant cette syntaxe, peut être saisie manuellement dans les cellules, sans appeler "Maîtres de fonctions". Il est juste important de ne pas oublier de mettre un signe devant la formule dans la cellule «=» .

Il existe donc deux manières principales de générer des sous-totaux : via un bouton sur le ruban et via une formule spéciale. De plus, l'utilisateur doit déterminer quelle valeur sera affichée sous forme de total : somme, minimum, moyenne, maximum, etc.

Site sur l'informatique