Как да създадете потребителска функция в Microsoft Excel

Anonim

В тази статия ще научим как да създаваме потребителски функции в Microsoft Excel с помощта на VBA.

Потребителска функция:- Microsoft Excel вече има много функции, но все пак всеки има различни изисквания, ситуация, можем да създадем своя собствена функция според изискването, което се нарича Потребителска функция. Можем да използваме User Defined функция като други функции в Excel.

По -долу са темите, за които ще създадем дефинирана от потребителя функция:

1). Как да преброим броя думи в клетка или диапазон?

2). Как да извлечете дума от изречение или клетка в Excel?

3). Как да създадете формулата за ISO?

4). Как да знам името на работния лист и работната книга, използвайки VBA?

5). Как да извлечете първата и последната дума от клетка в Excel?

Как да създадете дефинирана от потребителя функция за преброяване на броя думи в клетка или диапазон?

Имаме данни в лист 1, в който имаме някои адреси, така че искаме да преброим думите в клетка или диапазон, като създадем потребителско определена функция чрез VBA в Excel.

За да направите дефинираната от потребителя функция, моля, следвайте дадените по-долу стъпки:-

  • Отворете VBA Page и натиснете клавиша Alt+F11.
  • Поставете модул.

Напишете кода по -долу:

 Функция WORDSCOUNT (rRange As Range) As Long Dim rCell As Range Dim Count As Long За всеки rCellInrRange lCount = lCount + Len (Trim (rCell)) - Len (Replace (Trim (rCell), "", "")) + 1 Следваща rCell WORDSCOUNT = lКрайна функция за преброяване

Обяснения на кода: - За да направим дефинираната от потребителя функция, стартираме кода на името на функцията и дефинираме променливите. Използвахме „За всеки цикъл“ в кода, за да преброим думите в диапазона.
Как да използвате тази функция в Excel?

За да използвате тази функция, следвайте дадените по-долу стъпки:-

  • Отидете на Excel лист.
  • За да преброите думите за клетка, въведете формулата в клетка D7.
  • = WORDSCOUNT (C7), клетка C7 е клетката, в която искаме да изчислим думите.
  • Функцията ще върне 6, което означава, че клетка C7 съдържа 6 думи.
  • За да направите същото изчисление за останалите клетки, копирайте същата формула и поставете в диапазона.

  • За да преброите думите в диапазона, използвайте формулата като = WORDSCOUNT (C7: C16) и натиснете Enter.
  • Функцията ще върне броя на думите.


Забележка:- Този UDF ще бъде полезен за преброяване на думите в диапазон или в една клетка.

Сега ще напишем кода за преброяване на думата, като използваме посочения разделител (,). Следвайте следните стъпки:-

 Функция SEPARATECOUNTWORDS (rRange As Range, незадължителен разделител като вариант) As Long Dim rCell As Range Dim Count As Long If IsMissing (separator) then separator = "," End If For Each rCellInrRange lCount = lCount + Len (Trim (rCell)) - Len (Замяна (Trim (rCell), разделител, "")) Следваща rCell SEPARATECOUNTWORDS = l Функция за край

За да използвате тази функция, следвайте следните стъпки:-

  • Отидете на Excel лист.
  • За да преброим конкретните разделители в думата, ще използваме тази дефинирана функция.
  • = SEPARATECOUNTWORDS (C7) и натиснете Enter.
  • Функцията ще върне броя на конкретните разделители.


Как да извлечете дума от изречение или клетка в Microsoft Excel с помощта на VBA?

Имаме данни в лист 1. В които имаме някои адреси, така че искаме да извлечем думите от изречение или клетка или диапазон, като създадем потребителско дефинирана функция чрез VBA в Excel.


За да направите дефинираната от потребителя функция, моля, следвайте дадените по-долу стъпки:-

  • Отворете VBA Page и натиснете клавиша Alt+F11.
  • Поставете модул.
  • Напишете кода по -долу:-
 Функция GETWORD (текст като вариант, N като цяло число, незадължителен разделител като вариант) като низ, ако липсва (разделител), тогава разделител = "" край, ако GETWORD = разделяне (текст, разделител) (N - 1) крайна функция

Код Обяснение:- В гореспоменатия код споменахме името на функцията с променливите. И тогава бяхме определили критериите за извличане на думата от изречение или клетка.

Сега ще научим как да използваме тази формула. Следвайте следните стъпки:-

  • Отидете на Excel лист.
  • Използвайте тази формула в клетка D7.
  • = GETWORD (C7,2) и натиснете Enter.
  • Функцията ще върне втора дума от клетката, защото във формулата, която споменахме за 2nd числова дума. Ако искате да извлечете думата, която е поставена на 3rd позиция, трябва да промените числото от 2 на 3 във формулата.


Как да създадете формула за номер на ISO седмица в Microsoft Excel с помощта на VBA?

Ще научим как можем да създадем ISO формула за номер на седмица в Excel с този UDF. Тази функция ще използваме, за да идентифицираме, че споменатата дата принадлежи към кой номер на седмицата в годината.

Имаме списък с дати в листа, а във втората колона искаме да извлечем номерата на седмиците.

За да създадете UDF за това изискване, следвайте дадените по -долу стъпки:-

  • Отворете VBA Page и натиснете клавиша Alt+F11.
  • Поставете модул.
  • Напишете кода по-долу:-
 Функция ISOWEEKNUMBER (Indate As Date) As Long Dim Dt As Date Dt = DateSerial (Year (Indate - Weekday (Indate - 1) + 4), 1, 3) ISOWEEKNUMBER = Int ((Indate - Dt + Weekday (Dt) + 5 ) / 7) Крайна функция

Код Обяснение:-:- В горния код споменахме името на функцията с променливите. След това зададохме стойността на датата и след това определихме критериите на функцията „ISOWEENUMBER“.
Как можем да използваме тази функция в нашия Excel файл?

  • Отидете на Excel лист.
  • Въведете формулата в клетка D7.
  • = ISOWEEKNUMBER (C7) и натиснете Enter.
  • Функцията ще върне седмицата за въведената дата в клетката. Сега, за да извлечете номера на седмицата за всяка дата, копирайте същата формула в диапазона.


Сега ще научим как да върнем ISO стандартите в началото на годината в Excel- първи понеделник в годината.

Тази функция ще провери основно 1ул На коя дата ще падне понеделник в годината и тогава той ще започне да изчислява броя на седмиците от тази дата. Нека да видим как можем да създадем UDF за това изискване.


Следвайте дадените по -долу стъпки:-

  • Отворете VBA Page и натиснете клавиша Alt+F11.
  • Поставете модул.
  • Напишете кода по -долу:-
 Функция ISOSTYR (Година като цяло число) Като дата Dim WD As Integer Dim NY As Date NY = DateSerial (Year, 1, 1) WD = (NY - 2) Mod 7 Ако WD <4 Тогава ISOSTYR = NY - WD Else ISOSTYR = NY - WD + 7 End If End функция

Код Обяснение: - В горния код споменахме името на функцията с променливите. След това бяхме задали критериите за променливите и след това бяхме дефинирали въвеждането на формулата.

Просто трябва да предоставите 2001 г. в този формат и формулата ще ви даде 1ул Понеделник от годината.

Сега ще научим как да използваме UDF в Excel файл. Следвайте следните стъпки:-

  • Отидете на Excel лист.
  • Въведете формулата в клетка D7.
  • = ISOSTYR (C7) и натиснете Enter.
  • Функцията ще върне датата на 1ул Понеделник от първата седмица на Нова година.
  • За да върнете датата на 1ул В понеделник от първата седмица на Нова година, копирайте същата формула и поставете в диапазона.


Как да разберете името на работния лист и работната книга, като използвате VBA в Microsoft Excel?

Следвайте дадения код и стъпки по -долу:-

  • Отворете VBA Page и натиснете клавиша Alt+F11.
  • Поставете модул.
  • Напишете кода по -долу:-
 Функция Име на работен лист () Име на работен лист = Обхват ("A1"). Parent.Name Крайна функция

Код Обяснение:- В горния код споменахме името на функцията и след това бяхме дефинирали как да знаем името на листа.

За да използвате тази формула, просто трябва да въведете формулата във всяка клетка по този начин: -= Име на работен лист (). Функцията ще върне името на листа.


За да създадете функцията за име на работна книга, следвайте дадените по-долу стъпки и код:-

  • Отворете VBA страница, натиснете клавиша Alt+F11.
  • Поставете модул.
  • Напишете кода по-долу:-
 Функция Име на работна книга () Име на работна книга = Крайна функция на тази работна книга. Име

Код Обяснение:-:- В горния код споменахме името на функцията и след това бяхме дефинирали как да опознаем името на работната книга.

За да използвате тази формула, просто трябва да въведете формулата във всяка клетка по този начин: - = Име на работна книга (). Функцията ще върне името на листа.


Как да извлечете първата и последната дума от клетка, като използвате VBA в Microsoft Excel?

Имаме данни в sheet1, в които имаме някои адреси, така че искаме да извлечем последната и първата дума от изречение или клетка или диапазон чрез създаване на дефинирана от потребителя функция чрез VBA в Excel.

Първо ще напишем функцията за извличане на първата дума. Моля, следвайте следните стъпки:-

  • Отворете VBA страница, натиснете клавиша Alt+F11.
  • Поставете модул

Напишете кода по -долу:-

 Функция GETFW (Text As String, незадължителен разделител като вариант) 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 (Текст като низ, незадължителен разделител като вариант) Затъмняване на LW като низ, ако липсва (разделител) Тогава разделителят = "" Край, ако LW = StrReverse (текст) LW = наляво (последна дума, InStr (1, LW, разделител, vbTextCompare) ) GETLW = StrReverse (Замяна (LW, разделител, "")) Крайна функция

Сега ще научим как да използваме тази формула. Следвайте следните стъпки:-

  • Отидете на Excel лист.
  • Използвайте тази формула в клетка D9.
  • = GETLW (C9) Натиснете Enter.
  • Функцията ще върне последната дума от данните. Сега, за да извлечете последната дума за всички клетки, копирайте същата формула в диапазона.

Това са функциите, които можем да дефинираме чрез VBA и след това да ги използваме като формула на Excel. Също така можем да създадем много повече потребителски дефинирани функции. Продължавайте да учите с нас, ние ще измислим по -сложни формули.