Πώς να δημιουργήσετε μια συνάρτηση καθορισμένη από το χρήστη στο Microsoft Excel

Anonim

Σε αυτό το άρθρο, θα μάθουμε πώς να δημιουργούμε λειτουργίες που ορίζονται από το χρήστη στο Microsoft Excel χρησιμοποιώντας το VBA.

Λειτουργία που ορίζεται από το χρήστη:- Το Microsoft Excel έχει ήδη πολλές λειτουργίες, αλλά εξακολουθούν να έχουν όλοι διαφορετικές απαιτήσεις, κατάσταση, μπορούμε να δημιουργήσουμε τη δική μας συνάρτηση σύμφωνα με την απαίτηση που ονομάζεται Λειτουργία καθορισμένη από το χρήστη. Μπορούμε να χρησιμοποιήσουμε τη συνάρτηση User Defined όπως άλλες συναρτήσεις στο Excel.

Παρακάτω είναι τα θέματα για τα οποία θα δημιουργήσουμε τη συνάρτηση που ορίζεται από το χρήστη:

1). Πώς να μετρήσετε τον αριθμό των λέξεων στο Κελί ή το εύρος;

2). Πώς να εξαγάγετε μια λέξη από μια πρόταση ή κελί στο Excel;

3). Πώς να δημιουργήσετε τον τύπο για το ISO;

4). Πώς να γνωρίζετε το όνομα του φύλλου εργασίας και του βιβλίου εργασίας χρησιμοποιώντας το VBA;

5). Πώς να εξαγάγετε την πρώτη και τελευταία λέξη από ένα κελί στο Excel;

Πώς να δημιουργήσετε τη συνάρτηση που ορίζεται από τον χρήστη για να μετρά τον αριθμό των λέξεων στο Κελί ή το Εύρος;

Έχουμε δεδομένα στο φύλλο 1 στο οποίο έχουμε ορισμένες διευθύνσεις, οπότε θέλουμε να μετρήσουμε τις λέξεις σε ένα κελί ή μια περιοχή δημιουργώντας τη συνάρτηση που ορίζεται από το χρήστη μέσω VBA στο Excel.

Για να ορίσετε τη συνάρτηση που ορίζεται από το χρήστη, ακολουθήστε τα παρακάτω βήματα:-

  • Ανοίξτε τη σελίδα VBA και πατήστε το πλήκτρο Alt+F11.
  • Εισαγάγετε μια ενότητα.

Γράψτε τον παρακάτω κωδικό:

 Λειτουργία WORDSCOUNT (rRange As Range) As Long Dim rCell As Range Dim Count Όση ώρα για κάθε rCellInrRange lCount = lCount + Len (Trim (rCell)) - Len (Αντικατάσταση (Trim (rCell), "", "")) + 1 Επόμενο rCell WORDSCOUNT = l Συνάρτηση τερματισμού αριθμού

Επεξηγήσεις κώδικα: - Για να ορίσουμε τη συνάρτηση που ορίζεται από το χρήστη ξεκινάμε τον κωδικό στο όνομα της συνάρτησης και ορίζουμε τις μεταβλητές. Χρησιμοποιήσαμε "Για κάθε βρόχο" στον κώδικα για να μετρήσουμε τις λέξεις στο εύρος.
Πώς να χρησιμοποιήσετε αυτήν τη λειτουργία στο Excel;

Για να χρησιμοποιήσετε αυτήν τη λειτουργία, ακολουθήστε τα παρακάτω βήματα:-

  • Μεταβείτε στο Φύλλο Excel.
  • Για να μετρήσετε τις λέξεις για ένα κελί, εισαγάγετε τον τύπο στο κελί D7.
  • = WORDSCOUNT (C7), το κελί C7 είναι το κελί στο οποίο θέλουμε να υπολογίσουμε τις λέξεις.
  • Η συνάρτηση θα επιστρέψει 6, που σημαίνει ότι το κελί C7 περιέχει 6 λέξεις.
  • Για να κάνετε τον ίδιο υπολογισμό για τα υπόλοιπα κελιά, αντιγράψτε τον ίδιο τύπο και επικολλήστε στο εύρος.

  • Για να μετρήσετε τις λέξεις στο εύρος, χρησιμοποιήστε τον τύπο ως = WORDSCOUNT (C7: C16) και πατήστε Enter.
  • Η συνάρτηση θα επιστρέψει τον αριθμό των λέξεων.


Σημείωση:- Αυτό το UDF θα σας βοηθήσει να μετρήσετε τις λέξεις σε μια περιοχή ή σε ένα κελί.

Τώρα θα γράψουμε τον κωδικό για να μετρήσουμε τη λέξη χρησιμοποιώντας τον καθορισμένο οριοθέτη (,). Ακολουθήστε τα παρακάτω βήματα:-

 Λειτουργία SEPARATECOUNTWORDS (rRange As Range, Optional separator As Variant) As Long Dim rCell As Range Dim Count As Long If IsMissing (διαχωριστικό) Στη συνέχεια διαχωριστής = "," Τέλος αν για κάθε rCellInrRange lCount = lCount + Len (Trim (rCell)) - Len (Αντικατάσταση (Περικοπή (rCell), διαχωριστής, "")) Επόμενο rCell SEPARATECOUNTWORDS = l Λειτουργία τερματισμού αριθμού

Για να χρησιμοποιήσετε αυτήν τη λειτουργία, ακολουθήστε τα παρακάτω βήματα:-

  • Μεταβείτε στο Φύλλο Excel.
  • Για να μετρήσουμε τους συγκεκριμένους οριοθέτες στη λέξη, θα χρησιμοποιήσουμε αυτήν την καθορισμένη συνάρτηση.
  • = SEPARATECOUNTWORDS (C7) και πατήστε Enter.
  • Η συνάρτηση θα επιστρέψει τον αριθμό των συγκεκριμένων οριοθετητών.


Πώς να εξαγάγετε μια λέξη από μια πρόταση ή κελί στο Microsoft Excel χρησιμοποιώντας το VBA;

Έχουμε δεδομένα στο φύλλο 1. Στην οποία έχουμε ορισμένες διευθύνσεις, έτσι θέλουμε να εξαγάγουμε τις λέξεις από μια πρόταση ή κελί ή ένα εύρος δημιουργώντας τη συνάρτηση που ορίζεται από το χρήστη μέσω VBA στο Excel.


Για να ορίσετε τη συνάρτηση που ορίζεται από το χρήστη, ακολουθήστε τα παρακάτω βήματα:-

  • Ανοίξτε τη σελίδα VBA και πατήστε το πλήκτρο Alt+F11.
  • Εισαγάγετε μια ενότητα.
  • Γράψτε τον παρακάτω κωδικό:-
 Λειτουργία GETWORD (Κείμενο ως παραλλαγή, Ν ως ακέραιος, Προαιρετικός οριοθέτης ως παραλλαγή) Ως συμβολοσειρά αν IsMissing (οριοθέτης) Τότε οριοθέτης = "" Τερματισμός εάν GETWORD = Διαχωρισμός (Κείμενο, οριοθέτης) (Ν - 1) Λειτουργία λήξης

Επεξήγηση κώδικα:- Στον παραπάνω κωδικό, αναφέραμε το όνομα της συνάρτησης με τις μεταβλητές. Και τότε είχαμε καθορίσει τα κριτήρια για την εξαγωγή της λέξης από πρόταση ή κελί.

Τώρα θα μάθουμε πώς να χρησιμοποιούμε αυτόν τον τύπο. Ακολουθήστε τα παρακάτω βήματα:-

  • Μεταβείτε στο Φύλλο Excel.
  • Χρησιμοποιήστε αυτόν τον τύπο στο κελί D7.
  • = GETWORD (C7,2) και πατήστε Enter.
  • Η συνάρτηση θα επιστρέψει τη δεύτερη λέξη από το κελί επειδή στον τύπο που είχαμε αναφέρει για το 2nd λέξη αριθμού. Εάν θέλετε να ανακτήσετε τη λέξη που βρίσκεται στο 3rd θέση, πρέπει να αλλάξετε τον αριθμό από 2 σε 3 στον τύπο.


Πώς να δημιουργήσετε τον τύπο αριθμού εβδομάδας ISO στο Microsoft Excel χρησιμοποιώντας το VBA;

Θα μάθουμε πώς μπορούμε να δημιουργήσουμε τον τύπο αριθμού εβδομάδας ISO στο Excel με αυτό το UDF. Αυτή τη συνάρτηση θα χρησιμοποιήσουμε για να προσδιορίσουμε ότι η αναφερόμενη ημερομηνία ανήκει σε ποιον αριθμό εβδομάδας του έτους.

Έχουμε μια λίστα ημερομηνιών στο φύλλο και στη δεύτερη στήλη, θέλουμε να ανακτήσουμε τους αριθμούς της εβδομάδας.

Για να δημιουργήσετε το UDF για αυτήν την απαίτηση, ακολουθήστε τα παρακάτω βήματα:-

  • Ανοίξτε τη σελίδα VBA και πατήστε το πλήκτρο Alt+F11.
  • Εισαγάγετε μια ενότητα.
  • Γράψτε τον παρακάτω κωδικό:-
 Λειτουργία ISOWEEKNUMBER (Αναγραφή ως ημερομηνία) Όσο Dim Dt ως ημερομηνία Dt = Ημερομηνία Σειρά (Έτος (Ένδειξη - Εβδομάδα (Ένδειξη - 1) + 4), 1, 3) ISOWEEKNUMBER = Int ((Ένδειξη - Dt + Εβδομάδα (Dt) + 5 ) / 7) Λειτουργία Τέλους

Επεξήγηση κώδικα:-:- Στον παραπάνω κώδικα, έχουμε αναφέρει το όνομα της συνάρτησης με τις μεταβλητές. Στη συνέχεια, είχαμε ορίσει την τιμή ημερομηνίας και στη συνέχεια είχαμε ορίσει τα κριτήρια της συνάρτησης "ISOWEENUMBER".
Πώς μπορούμε να χρησιμοποιήσουμε αυτήν τη συνάρτηση στο αρχείο Excel;

  • Μεταβείτε στο Φύλλο Excel.
  • Εισαγάγετε τον τύπο στο κελί D7.
  • = ISOWEEKNUMBER (C7) και πατήστε Enter.
  • Η συνάρτηση θα επιστρέψει την εβδομάδα για την καταχωρημένη ημερομηνία στο κελί. Τώρα για να ανακτήσετε τον αριθμό εβδομάδας για κάθε ημερομηνία, αντιγράψτε τον ίδιο τύπο στο εύρος.


Τώρα θα μάθουμε πώς να επιστρέφουμε τα πρότυπα ISO έναρξη του έτους στο Excel- Πρώτη Δευτέρα του Έτους.

Αυτή η συνάρτηση θα ελέγξει βασικά ότι 1st Η Δευτέρα του έτους πρόκειται να πέσει σε ποια ημερομηνία και στη συνέχεια θα αρχίσει να υπολογίζει τον αριθμό των εβδομάδων από την ημερομηνία αυτή. Ας δούμε πώς μπορούμε να δημιουργήσουμε το UDF για αυτήν την απαίτηση.


Ακολουθήστε τα παρακάτω βήματα:-

  • Ανοίξτε τη σελίδα VBA και πατήστε το πλήκτρο Alt+F11.
  • Εισαγάγετε μια ενότητα.
  • Γράψτε τον παρακάτω κωδικό:-
 Λειτουργία ISOSTYR (Έτος ως ακέραιος) Ως ημερομηνία Dim WD Ως ακέραιος Dim NY As Date NY = DateSerial (Έτος, 1, 1) WD = (NY - 2) Mod 7 If WD <4 Then ISOSTYR = NY - WD Else ISOSTYR = NY - WD + 7 End If End Λειτουργία

Επεξήγηση κώδικα: - Στον παραπάνω κώδικα, έχουμε αναφέρει το όνομα της συνάρτησης με τις μεταβλητές. Και, στη συνέχεια, είχαμε θέσει τα κριτήρια για τις μεταβλητές και στη συνέχεια είχαμε ορίσει την είσοδο του τύπου.

Απλώς πρέπει να δώσετε το έτος 2001 σε αυτήν τη μορφή και ο τύπος θα σας δώσει 1st Δευτέρα του έτους.

Τώρα θα μάθουμε πώς να χρησιμοποιούμε το UDF στο αρχείο Excel. Ακολουθήστε τα παρακάτω βήματα:-

  • Μεταβείτε στο Φύλλο Excel.
  • Εισαγάγετε τον τύπο στο κελί D7.
  • = ISOSTYR (C7) και πατήστε Enter.
  • Η συνάρτηση θα επιστρέψει την ημερομηνία του 1st Δευτέρα της πρώτης εβδομάδας της Πρωτοχρονιάς.
  • Για να επιστρέψετε την ημερομηνία του 1st Τη Δευτέρα της πρώτης εβδομάδας της Πρωτοχρονιάς, αντιγράψτε τον ίδιο τύπο και επικολλήστε στην περιοχή.


Πώς να γνωρίζετε το όνομα του φύλλου εργασίας και του βιβλίου εργασίας χρησιμοποιώντας το VBA στο Microsoft Excel;

Ακολουθήστε τον παρακάτω κωδικό και βήματα:-

  • Ανοίξτε τη σελίδα VBA και πατήστε το πλήκτρο Alt+F11.
  • Εισαγάγετε μια ενότητα.
  • Γράψτε τον παρακάτω κωδικό:-
 Function Worksheetname () Worksheetname = Range ("A1"). Parent.Name End Function

Επεξήγηση κώδικα:- Στον παραπάνω κώδικα, έχουμε αναφέρει το όνομα της συνάρτησης και στη συνέχεια είχαμε ορίσει πώς να γνωρίζουμε το όνομα του φύλλου.

Για να χρησιμοποιήσετε αυτόν τον τύπο, απλά πρέπει να εισαγάγετε τον τύπο σε οποιοδήποτε κελί με αυτόν τον τρόπο: -= Όνομα φύλλου εργασίας (). Η συνάρτηση θα επιστρέψει το όνομα του φύλλου.


Για να δημιουργήσετε τη συνάρτηση για το όνομα του βιβλίου εργασίας, ακολουθήστε τα παρακάτω βήματα και κωδικό:-

  • Ανοίξτε τη σελίδα VBA πατήστε το πλήκτρο Alt+F11.
  • Εισαγάγετε μια ενότητα.
  • Γράψτε τον παρακάτω κωδικό:-
 Function Workbookname () Workbookname = ThisWorkbook.Name End Function

Επεξήγηση κώδικα:-:- Στον παραπάνω κώδικα, έχουμε αναφέρει το όνομα της συνάρτησης και στη συνέχεια είχαμε ορίσει πώς να γνωρίσουμε το όνομα του βιβλίου εργασίας.

Για να χρησιμοποιήσετε αυτόν τον τύπο, απλά πρέπει να εισαγάγετε τον τύπο σε οποιοδήποτε κελί με αυτόν τον τρόπο: - = Όνομα βιβλίου εργασίας (). Η συνάρτηση θα επιστρέψει το όνομα του φύλλου.


Πώς να εξαγάγετε την πρώτη και τελευταία λέξη από ένα κελί χρησιμοποιώντας VBA στο Microsoft Excel;

Έχουμε δεδομένα στο φύλλο 1 στο οποίο έχουμε ορισμένες διευθύνσεις, οπότε θέλουμε να εξαγάγουμε την τελευταία και την πρώτη λέξη από μια πρόταση ή κελί ή ένα εύρος δημιουργώντας τη συνάρτηση που ορίζεται από το χρήστη μέσω VBA στο Excel.

Αρχικά, θα γράψουμε τη συνάρτηση εξαγωγής της πρώτης λέξης. Ακολουθήστε τα παρακάτω βήματα:-

  • Ανοίξτε τη σελίδα VBA πατήστε το πλήκτρο Alt+F11.
  • Εισαγάγετε μια ενότητα

Γράψτε τον παρακάτω κωδικό:-

 Λειτουργία GETFW (Κείμενο ως συμβολοσειρά, Προαιρετικός διαχωριστής ως παραλλαγή) Dim FW As String If IsMissing (Separator) Then Separator = "" End If FW = Left (Text, InStr (1, Text, Separator, vbTextCompare)) GETFW = Replace (FW , Διαχωριστής, "") Λειτουργία λήξης

Επεξήγηση κώδικα: - Στον παραπάνω κώδικα, έχουμε αναφέρει το όνομα της συνάρτησης με τις μεταβλητές. Και στη συνέχεια έχουμε καθορίσει τα κριτήρια για την εξαγωγή της λέξης από πρόταση ή κελί.

Τώρα θα μάθουμε πώς να χρησιμοποιούμε αυτόν τον τύπο. Ακολουθήστε τα παρακάτω βήματα:-

  • Μεταβείτε στο Φύλλο Excel.
  • Χρησιμοποιήστε αυτόν τον τύπο στο κελί D9.
  • = GETFW (C9) και πατήστε Enter.
  • Η συνάρτηση θα επιστρέψει την πρώτη λέξη από τα δεδομένα. Τώρα, για να ανακτήσετε την πρώτη λέξη για όλα τα κελιά, αντιγράψτε τον ίδιο τύπο στο εύρος.

Τώρα θα γράψουμε τον κώδικα για να εξάγουμε την τελευταία λέξη από το κελί. Ακολουθήστε τον παρακάτω κωδικό:-

  • Ανοίξτε τη σελίδα VBA πατήστε το πλήκτρο Alt+F11.
  • Εισαγάγετε μια ενότητα.
  • Γράψτε τον παρακάτω κωδικό:-
 Συνάρτηση GETLW (Κείμενο ως συμβολοσειρά, Προαιρετικός διαχωριστής ως παραλλαγή) Dim LW As String If IsMissing (Separator) Then Separator = "" End If LW = StrReverse (Text) LW = Left (lastword, InStr (1, LW, Separator, vbTextCompare) ) GETLW = StrReverse (Replace (LW, Separator, "")) End function

Τώρα θα μάθουμε πώς να χρησιμοποιούμε αυτόν τον τύπο. Ακολουθήστε τα παρακάτω βήματα:-

  • Μεταβείτε στο Φύλλο Excel.
  • Χρησιμοποιήστε αυτόν τον τύπο στο κελί D9.
  • = GETLW (C9) Πατήστε Enter.
  • Η συνάρτηση θα επιστρέψει την τελευταία λέξη από τα δεδομένα. Τώρα, για να ανακτήσετε την τελευταία λέξη για όλα τα κελιά, αντιγράψτε τον ίδιο τύπο στο εύρος.

Αυτές είναι οι συναρτήσεις που μπορούμε να ορίσουμε μέσω VBA και στη συνέχεια να τις χρησιμοποιήσουμε ως τύπο του Excel. Επίσης, μπορούμε να δημιουργήσουμε πολλές περισσότερες λειτουργίες που ορίζονται από το χρήστη. Συνεχίστε να μαθαίνετε μαζί μας, θα βρούμε πιο δύσκολους τύπους.