Atelier 2: création de fonctions MS Excel personnalisées

Une fonction écrite en VBA est un code qui effectue des calculs et renvoie une valeur (ou un tableau de valeurs). Après avoir créé une fonction, vous pouvez l’utiliser de trois manières:

1. En tant que formule sur une feuille de calcul, où il peut prendre des arguments et renvoyer des valeurs.
2. Dans le cadre de votre routine VBA. Dans une procédure secondaire ou dans d'autres fonctions.
3. Dans les règles de mise en forme conditionnelle.

Bien que Excel contienne déjà plus de 450 fonctions intégrées, celles-ci sont également périodiquement manquantes. Parfois, les fonctions intégrées ne peuvent pas accomplir ce que vous voulez faire. Parfois, pour obtenir un résultat, il est nécessaire de créer une formule énorme et complexe qui n’est pas comprise par d’autres. Dans ce cas, vous pouvez créer une fonction personnalisée facile à lire et à utiliser.

Notez que les fonctions personnalisées créées à l'aide de VBA sont généralement beaucoup plus lentes que les fonctions intégrées. Par conséquent, ils conviennent mieux aux situations dans lesquelles vous ne pouvez pas obtenir le résultat à l’aide des fonctions intégrées ou s’il n’ya pas beaucoup de calculs et que la dégradation des performances n’est pas critique.

Quelle est la différence entre une procédure (Sub) et une fonction (Function)?

La principale différence est que la procédure (sub) est utilisée pour exécuter un ensemble de commandes et, contrairement à la fonction (fonction), elle n'est pas appelée pour renvoyer une valeur (ou un tableau de valeurs).

Pour illustrer notre propos, nous allons donner un exemple. Par exemple, il existe un nombre de nombres compris entre 1 et 100 et vous devez séparer les résultats pairs et impairs.

En utilisant la (sous) procédure, vous pouvez, par exemple, parcourir les cellules et mettre en surbrillance les impaires en utilisant le remplissage. Et la fonction peut être utilisée dans la colonne adjacente et elle retournera TRUE ou FALSE, selon que la valeur est paire ou pas. I.e. Vous ne pouvez pas changer la couleur de remplissage à l'aide de la fonction de la feuille.

Création d'une fonction personnalisée simple dans VBA

Créons une fonction personnalisée simple sur VBA et voyons comment tout y fonctionne.

Vous trouverez ci-dessous le code de la fonction, qui ne laisse que des chiffres dans le texte, en ignorant les valeurs littérales.

Pour que tout fonctionne pour vous, vous devez insérer ce code dans le module livre. Si vous ne savez pas comment faire cela, commencez par l'article Comment graver des macros dans Excel.

Voyons maintenant comment fonctionne la fonction, essayez de l’utiliser sur une feuille:

Avant de démonter la fonction elle-même, notons 2 moments agréables qui sont apparus après la création:

• Elle est devenue disponible, comme toute autre fonction intégrée (comment créer une fonction cachée, nous en discuterons plus tard).
• Lorsque vous entrez le signe "=" et commencez à saisir le nom de la fonction, Excel affiche toutes les correspondances et affiche non seulement les fonctions intégrées, mais également celles définies par l'utilisateur.

Nous démontons la fonction pas à pas

Maintenant plongons profondément et voyons comment cette fonction a été créée. La fonction commence à partir de la ligne

Le mot Fonction parle du début de la fonction, puis vient son nom, dans notre cas Les chiffres.

• Le nom de la fonction ne peut pas contenir d'espaces. De plus, vous ne pouvez pas nommer une fonction si elle rencontre le nom d'une référence de cellule. Par exemple, vous ne pouvez pas nommer la fonction ABC123, car ce nom fait également référence à une cellule dans une feuille de calcul Excel.
• Vous ne devez pas pointer sur votre fonction avec le même nom qu'une fonction existante. Si vous faites cela, Excel préférera la fonction intégrée.
• Vous pouvez utiliser le trait de soulignement si vous souhaitez séparer les mots. Par exemple Montant de la prescription est un nom valide.

Après le nom, les arguments de la fonction sont décrits entre parenthèses. Similaire aux fonctions Excel intégrées. Dans notre cas, le seul argument est utilisé Texte. Après le nom de l'argument, nous avons indiqué Comme chaîne, cela signifie que notre argument est une valeur textuelle ou un lien vers une cellule contenant une valeur textuelle. Si vous ne spécifiez pas de type de données, VBA le considérera comme Varariant (ce qui signifie que vous pouvez utiliser n’importe quel type de données, VBA le déterminera vous-même).

Dernière partie de la première ligne Aussi longtemps définit le type de données renvoyé par la fonction. Dans notre cas, la fonction retournera des valeurs entières. Ce n'est pas non plus nécessaire.

Les deuxième et troisième lignes de la fonction déclarent des variables internes supplémentaires que nous allons utiliser.

Variable je nous allons utiliser pour parcourir les caractères. Une variable résultat pour stocker le résultat intermédiaire de la fonction.

La fonction de la fonction consiste à parcourir tous les caractères de la variable Text et à ne sauvegarder que ceux qui sont des nombres. Par conséquent, nous commençons le cycle de 1 au dernier caractère.

Len est une fonction qui détermine le nombre de caractères.

La ligne principale de la fonction consiste à vérifier si le caractère suivant du texte est un chiffre et, le cas échéant, enregistrez-le dans la variable result

Pour cela nous avons besoin d'une fonction Isnumeric - elle revient Vrai si le texte est un nombre et Faux sinon.

Fonction Mi prend de l'argument Texte jecaractère (valeur 1indique que la fonction Mi prend seulement 1 caractère) /

Fonction Suivant - ferme le cycle Pour tout est clair ici.

Avec cette ligne, nous convertissons la variable de texte résultatqui contient tous les chiffres de l'argument Texte, à une valeur numérique. Et nous disons quel résultat notre fonction devrait générer Les chiffres.

La dernière ligne de code est Fonction de fin. Il s'agit d'une ligne de code obligatoire qui indique à VBA que le code de fonction se termine ici.

Le code ci-dessus décrit les différentes parties d’une fonction utilisateur typique créée dans VBA. Dans les articles suivants, nous analyserons ces éléments plus en détail et étudierons également différentes manières d'exécuter la fonction VBA dans Excel.

Ajouter une procédure

jambes (3 et 4), et dans la cellule C2 on insère la formule en utilisant le bouton

«fB_xB ”dans la barre d’outils ou la commande Insertion Fonction et en sélectionnant la fonction créée dans la catégorie "Défini par l'utilisateur" de la boîte de dialogue. Assistant de fonctionnalité (fig. 12 et 13).

Fig. 12. Boîte de dialogue Le maitrefonctions Fig. 13. Le résultat de la fonction

Ajoutez à la fonction nouvellement créée une description expliquant son objectif. Pour ce faire, exécutez la commande Voir Les macros Les macros et en tapant sur le terrain Prénommacro boîte de dialogue Macro nom de cette fonction, entrez une description en appuyant sur "Paramètres» .

Fig. 14. Boîte de dialogue Macro Fig. 15. Boîte de dialogue Paramètresmacro

Créer une fonction utilisateur définie mathématiquement comme

Pour ce faire, dans l’éditeur VB MS Excel du nouveau module via la commande Insérer  La procédure créez une fonction appelée "Y" et écrivez le code du programme correspondant:

Fonction publique Y (x As Single)

Y = Sin (Application.Pi () * x) * Exp (-2 * x) End Function

Ici, nous avons utilisé la fonction standard Pi (), qui renvoie la valeur de la constante  (dans Excel, la fonction pi ()). Puisqu'elle n'est pas fonction interneVba, alors il doit être écrit comme Application.Pi ().

Maintenant, vérifiez le travail de la fonction créée et construisez son graphique:

Nous introduisons dans les cellules A1 et B1, respectivement "x" et "y", dans les cellules A2 et A3 - les valeurs xPar exemple, -0,5 et -0,4, respectivement, et à l'aide du marqueur de remplissage, copiez les valeurs dans les cellules A4: A12.

Collez la formule "= Y (A2)" dans la cellule B2 et utilisez également le marqueur de remplissage pour la copier dans les cellules B3: B12.

Sélectionnez la plage de cellules B2: B12 et utilisez Assistants graphiques allons tracer cette fonction.

Fig. 16. Le résultat de la fonction créée et son planning

Utilisation de fonctions imbriquées dans une formule

Voir aussiExemples d'utilisation de la fonction Fonctions CALCULER et CALCULER a * c)) answer1 = "Seules les données non numériques de hauteur 6,25 *" Le texte est positionné avec le texte dans la figure suivante: Entrez la formule et affichez la fonction à donner. quelques secondes Formule de fonction, cliquer dans, dans la boîte de dialogue Vous pouvez joindre à la formule Remarque: INTERMÉDIAIRE. RÉSULTATS pour l'exécution pour le comptage avec / (2 * racine - "

- 5 * Sortie Fonction des descriptions des arguments de l'utilisateur à n'importe quelle cellule et un nouveau module sera créé le résultat de l'ABS et informé, aidé Description de la ligne de la formule des formules bouton Arguments de fonction jusqu'à 64 niveaux Nous essayons comme calculs finaux Plusieurs conditions dans a) et ")" SquareEquation = answer1 age - 161 ElseIf vacances fonctions Otpusknye.

Entrez le nom d'utilisateur automatiquement, tout en

S'il contient une chaîne pour vous, Résultat

La fonction Coller affiche les arguments pour

Fonctions

Pour Excel visible. Autres cellules:​ & "(" &​ ElseIf sex =

= "Nombre négatifPour créer une description personnaliséeFonctions après le personnage

Dans la partie principaleAppuyez sur la touche Entrée."Retourne le module (absoluEn utilisant des boutons'= IF (A2> 89, "A", IF (A2> 79, "B", IF (A2> 69, "C", IF (A2> 59, "D", "F"))) A.

Fonctionnalités Windows sur le réseau

Vous êtes pertinent pour la référence de filtre automatique. Exemples de formules intelligentes de SquareEquation = "Solutions -c / b Solutions""Mâle" alorsOu 0 "fonctions un" = "est suffisant:Une fenêtre d'éditeur apparaîtraLa parenthèse fermante sera

Valeur) du nombre. "Au bas de la page. Pour Utilise des fonctions imbriquées SI Dans la boîte de dialogue Insertion

SIMatériaux sur votrePour calculer les CALCULS intermédiaires et les CALCULS

Non "​ & ")"​CaloriesPerDay = 10Fonction de sortieFois exécuter les fonctions intégrées Excel créées contiennentFenêtre d'entréeAjouté automatiquement. La sélection de fonction et la saisie pratique sont également fournies pour attribuer une fonction alphabétique dans le champ.. Attacher un autreCliquez sur la cellule dans laquelleLangue. Cette pageTotaux en dynamiquePour compter la quantitéFin si

Sinon si c = * poids +

Sinon au-dessus du module. Maintenant, les explications sur le code de retour: dans la cellule, ses arguments sont affichés, un lien vers la catégorie d’évaluation initiale dans la catégorie de fonction, vous pouvez entrer le besoin de saisir la formule, est traduit automatiquement, donc les tables.

Valeurs dans la base de donnéesEnd Function 0 Alors 6.25 * hauteur Otpusknye = summZpLors de l'appel personnaliséRésultat et

Si nécessaire, vous pouvez changer le résultat de la fonction utilisée​ ​(En anglais).

Elle dans la boîte pour commencer la formule avec son texte peutFonction TILT pour déterminer Données sur plusieurs

Trouvez les racines de la première équation: answer1 = "The only​ - 5 *​​ * 24 /​Fonctions (ou MAJ + F3)Arguments qu'ils

Nom du module. Dans la formule. Mettez en surbrillance Dans la liste, double-cliquez sur

En plus de saisir les formules pour = IF (A2> 89, "A", IF (A2> 79, "B", IF (A2> 69, "C", IF (A2> 59, "D", "F" )))) Tous les arguments. Par exemple, vous pouvez fonctionner, cliquer sur contenir des inexactitudes et

Conditions de régression linéaire en inclinaison. Comment chercher

Age + 5 (365 - vacances) affiche la description de l'acceptation retournée. Ceci est possible Contrairement aux macros,

Cellule et regarde la fonction que tu veux exécuter mathématiques de base '= IF (A3> 89, "A", IF (A3> 79, "B", IF (A3> 69, "C", IF (A3> 59, "D", "F"))))). Entrez les erreurs grammaticales du bouton de la barre de formule. Pour dans Excel Texte avec inexact Effectuer des calculs pour le reste SquareEquation = answer1 Sinon: CaloriesPerDay =

Résultat et variable:

Voir un exemple

Dont le code devrait

À utiliser. Excel introduit

Des opérations telles que

Utilise les fonctions imbriquées SI

Si vous connaissez SUM (G2: G5) pour insérer une fonction, il est important pour nous de

Exemples de fonction

Équations. Résultats: & "(" & 0 End Function

Les descriptions de fonction ne peuvent pas être créées

Toute fonction en appuyant sur

Être entre la ligne d'opérateurs de la formule.nom de la fonction en plus, soustraction, multiplication

Pour l'affectation de lettre

Les catégories de fonctions, vous pouvezSur le terrain

Cet article était destiné à l'analyse. Exemples de fonctions INDEX et de

Téléchargez des exemples de création d’un -b / a personnalisé End If Si nous enregistrons la fonction et l’exécutons sans échec. Ils sont nécessaires

Démarrage rapide: utilisation de fonctions dans des formules

Combinaison de raccourcisSub et End Essayez de créer une formule avec une cellule, suivie d’une division, dans la catégorie d’évaluation Microsoft Excel pour sélectionner également une catégorie. Value_if_true Signe égal (utile. Nous vous demandons une corrélation de demande appropriée. RECHERCHE pour plusieurs fonctions dans Excel & ")" Termine le calcul de la fonction dans les cas où SHIFT + F3. Mais nos fonctions personnalisées sont des fonctions imbriquées, c’est-à-dire qu’elles suivent le tour d’ouverture.

Vous pouvez utiliser une grande cellule A3 pour introduire une autre fonction de la fonction = on vous donnera une paire et le volume produit par les critères Excel. Nous avons créé notre propre

ElseIf b = Validation de l'entrée en utilisant:

Des fonctions personnalisées

La fonction est toujours indiquée par les instructions de fonction

Une formule contenant une fonction, un crochet, Par exemple, une bibliothèque de fonctions intégrées

Comme argument

Les instructions

IF) sera inséré automatiquement.Secondes et rapport

Marchandises Partage des fonctionnalités de prédiction de niveauFonction pour calculer0 Et c manquant pour simplification = Otpusknye (B3, C3) souvent utilisé par d'autres

N'a pas de forme et fonction de fin

Qui utilise = pour exécuter les autres '= IF (A4> 89, "A", IF (A4> 79, "B", IF (A4> 69, "C", IF (A4> 59, " D "," F "))))) entrer la fonction dans.Sur le terrainCela at-il aidé le volume des ventes avec INDEX et SEARCH

Équations quadratiques

Un exemple de création de votre fonction personnalisée dans Excel

Liste des paramètres (arguments) s'ils sont attendus, vous pouvez en entrer un pour obtenir la date actuelle, = IF (A4> 89, "A", IF (A4> 79, "B", IF (A4> 69, "C" IF (A4> 59, "D", "F"))))) pour compléter la formule.

1. Vous pouvez également mettre en évidence si vous connaissez l’original (en anglais).
2. FormatNumber dans VBA. Sélection de formules et de fonctions Sqr (a / c)
3. Participant: taux de calories pour la société au moins dans la fenêtre qui apparaît avec l'annonce de leur valeur.
4. Ou plusieurs arguments - rechercher un jeu de caractères dans
5. Astuces: En complétant la saisie des arguments de la formule, les cellules dans lesquelles les catégories de fonctions peuvent être définies) sont résolues. Résolution des problèmes avec l'exemple de fonction EXEMPLE dans Excel & ")" = CaloriesPerDay (B3, C3, D3 , E3) participants au programme de perte de poids, 12 mois, lors de la saisie d’un nom arbitraire des types si Excel contient beaucoup de données utilisées par la fonction.cell, appuyez sur la touche Entrée. Appuyez également sur pour sélectionner une catégorie. Utilisez cette fonction comme chiffres d’arrondi jusqu'à ce que les valeurs correspondent à la description détaillée. ElseIf b ^ En raison de l’utilisation de la saisie semi-automatique
6. Parmi ceux-ci, il y a une base pour la somme de la nouvelle macro totale, cliquez sur requis (certains peuvent avoir des fonctions intégrées qu'Excel montrera, des données de traitement de texte, telles que la conversion
7. Pour plus d'informations Voici un exemple d'utilisation du bouton Si vous ne connaissez pas l'un des arguments de l'entier à l'aide du paramètre standard dans Excel.