Ако имате таблица с данни, тогава можете да изчислите сумата по колона с обичайната функция =SUM(). Вероятно сте забелязали, че в случай на избор на определени стойности, такава функция не работи правилно. Как да изчислим сбора само от избрани стойности? За да направите това, е възможно да създадете междинни суми в Excel.
1. Номер_функция. Това е функцията, която трябва да използвате за изчисляване на общите суми.
Пример. Изчислете сумата в колоната Сума за всички месеци, в които са продадени повече от 20 единици.
Както можете да видите на първата снимка, ние филтрирахме цялата стойност в колоната „Продадени единици“ по-голяма от 20. В клетка C15 пишем формула като
МЕЖДИННИ.ОБЩИ (9;C2:C13)
Стойността на функцията ще се промени, ако промените условията на филтъра.
Моля, не забравяйте, че същото действие може да бъде превърнато във функция
Междинни суми в Excel, сума само от видимите клетки
Интересното е, че в списъка с номера на функциите за формулата на междинната сума, както е на снимката по-горе, няма интересна функция. За да изчислите сумата от всички видими клетки, трябва да използвате кода 109 .
Междинни суми в Excel. Създайте таблица на отделен лист
тези. с междинни суми за всяка група редове, например за половин година в нашия пример.
Какво трябва да се направи:
1. Уверете се, че данните са сортирани поне по една колона
2. Изберете диапазон
3. Отидете на лентата с инструменти в менюто Данни, секция Структура и изберете Междинни суми
В менюто, което се отваря, направете настройки, изберете по коя колона да сортирате (Половин година) и какво да направите (Операция), както и в коя колона да изчислите:
Трябва да изглежда като снимката по-горе.
Много е удобно, когато таблицата е голяма и трябва да се сортира по артикул, тримесечие, сметка и т.н.
Важно е да се отбележи:
- ако използвате опцията Таблица (Вмъкване - Таблица), при изчисляване на сумата, "Общ ред" автоматично се добавя към тази интерактивна таблица, която до голяма степен замества междинните суми. В отворения раздел Работа с таблици - Дизайнер, можете да персонализирате интерактивната таблица за почти всякакви условия. Все още почти никога не използвам тази функция, т.к. такава таблица, дори за 10 хиляди реда, може значително да натовари системата. Въпреки че в Excel работя до 500K реда без проблеми, дори и без PowerPivot.
Споделете нашата статия във вашите социални мрежи:Преди да започнете да създавате свои собствени VBA функции, добре е да знаете, че Excel VBA има богата колекция от предварително вградени функции, които можете да използвате, докато пишете своя код.
Списък с тези функции може да се види във VBA редактора:
- Отворете работна книга на Excel и стартирайте VBA редактора (щракнете, за да направите това Alt+F11), и след това натиснете F2.
- Изберете библиотека от падащия списък в горния ляв ъгъл на екрана VBA.
- Ще се появи списък с вградени VBA класове и функции. Щракнете върху името на функцията, за да се покаже нейното кратко описание в долната част на прозореца. натискане F1ще отвори страницата за онлайн помощ за тази функция.
В допълнение, пълен списък с вградени VBA функции с примери може да бъде намерен на сайта на Visual Basic Developer Center.
Персонализирани процедури "Функция" и "Под" във VBA
В Excel Visual Basic набор от команди, които изпълняват конкретна задача, се поставя в процедура. функция(Функция) или под(Подпрограма). Основната разлика между процедурите функцияИ подтова е процедурата функциявръща резултат, процедура под- Не.
Следователно, ако искате да извършите действия и да получите някакъв резултат (например да сумирате няколко числа), тогава процедурата обикновено се използва функция, и за да извършите просто някои действия (например да промените форматирането на група клетки), трябва да изберете процедурата под.
Аргументи
Различни данни могат да се предават на VBA процедури с помощта на аргументи. Списъкът с аргументи се посочва при деклариране на процедура. Например процедурата подвъв VBA добавя даденото цяло число (Integer) към всяка клетка в избрания диапазон. Можете да предадете това число на процедурата с помощта на аргумент, като този:
Sub AddToCells(i като цяло число) ... End Sub
Имайте предвид, че има аргументи за процедури функцияИ подвъв VBA е по избор. Някои процедури не изискват аргументи.
Незадължителни аргументи
VBA процедурите могат да имат незадължителни аргументи. Това са аргументи, които потребителят може да посочи, ако желае, и ако са пропуснати, процедурата използва стойностите по подразбиране за тях.
Връщайки се към предишния пример, за да направите целочислен аргумент за функция по избор, той ще бъде деклариран по следния начин:
Поддобавяне към клетки (по избор i като цяло число = 0)
В този случай целочисленият аргумент ипо подразбиране ще бъде 0.
В една процедура може да има няколко незадължителни аргумента, всички от които са изброени в края на списъка с аргументи.
Предаване на аргументи по стойност и по препратка
Аргументите във VBA могат да бъдат предадени на процедура по два начина:
- ByVal– предаване на аргумент по стойност. Това означава, че само стойността (тоест копие на аргумента) се предава на процедурата и следователно всички промени, направени в аргумента вътре в процедурата, ще бъдат загубени, когато процедурата излезе.
- ByRef- предаване на аргумент чрез препратка. Тоест действителният адрес на местоположението на аргумента в паметта се предава на процедурата. Всички промени, направени в аргумент в процедурата, ще бъдат запазени, когато процедурата излезе.
Използване на ключови думи ByValили ByRefв декларацията на процедурата можете да посочите как аргументът се предава на процедурата. Това е показано в примери по-долу:
Не забравяйте, че аргументите във VBA се предават по подразбиране. С други думи, ако не се използват ключови думи ByValили ByRef, тогава аргументът ще бъде предаден чрез препратка.
Преди да продължите с процедурите функцияИ подпо-подробно ще бъде полезно да разгледаме отново характеристиките и разликите между тези два вида процедури. Следват кратки дискусии на VBA процедурите функцияИ поди са показани прости примери.
VBA процедура "Функция"
Редакторът на VBA разпознава процедурата функция
Функция ... Крайна функция
Както бе споменато по-рано, процедурата функциявъв VBA (за разлика от под) връща стойност. Следните правила важат за върнати стойности:
- Типът данни на връщаната стойност трябва да бъде деклариран в заглавката на процедурата функция.
- Променливата, която съдържа връщаната стойност, трябва да бъде наречена по същия начин като процедурата функция. Тази променлива не е необходимо да се декларира отделно, тъй като тя винаги съществува като неразделна част от процедурата. функция.
Това е добре илюстрирано в следния пример.
Пример за VBA функция: Извършване на математическа операция върху 3 числа
По-долу е даден пример за код на VBA процедура функция, който приема три аргумента от тип Двойна(числа с двойна точност с плаваща запетая). В резултат на това процедурата връща друг тип число Двойнаравен на сбора от първите два аргумента минус третия аргумент:
Функция SumMinus(dNum1 като двойно, dNum2 като двойно, dNum3 като двойно) Като двойна SumMinus = dNum1 + dNum2 - dNum3 Крайна функция
Тази много проста VBA процедура функцияилюстрира как данните се предават към процедура чрез аргументи. Можете да видите, че типът данни, върнат от процедурата, е дефиниран като Двойна(думите казват като двойнослед списъка с аргументи). Този пример също показва как е резултатът от процедурата функциясе съхранява в променлива със същото име като името на процедурата.
Извикване на VBA процедурата "Функция"
Ако горната проста процедура функциявмъкнат в модул в редактора на Visual Basic, той може да бъде извикан от други VBA процедури или да се използва в работен лист в работна книга на Excel.
Извикайте VBA процедура "Функция" от друга процедура
процедура функцияможе да бъде извикан от друга VBA процедура, като просто присвоите тази процедура на променлива. Следващият пример показва извикване на процедура SumMinus, което беше определено по-горе.
Sub main() Dim total as Double total = SumMinus(5, 4, 3) End Sub
Извикайте VBA процедура "Функция" от работен лист
VBA процедура функцияможе да се извика от работен лист на Excel по същия начин като всяка друга вградена функция на Excel. Следователно, процедурата, създадена в предишния пример функция – SumMinusможе да бъде извикан чрез въвеждане на следния израз в клетка на работен лист:
Сума минус(10, 5, 2)
VBA подпроцедура
Редакторът на VBA разбира, че пред него има процедура под, когато срещне група от команди, затворени между следните начални и затварящи оператори:
Под... Край Под
VBA процедура "Sub": Пример 1. Подравняване в центъра и промяна на размера на шрифта в избрания диапазон от клетки
Помислете за пример за проста VBA процедура под, чиято задача е да промени форматирането на избрания диапазон от клетки. Клетките са центрирани (вертикално и хоризонтално) и размерът на шрифта се променя на зададения от потребителя:
Sub Format_Centered_And_Sized(По избор iFontSize като цяло число = 10) Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Size = iFontSize End Sub
Тази процедура подизпълнява действия, но не връща резултат.
Този пример също използва аргумент Optional iFontSize. Ако аргументът iFontSizeне е преминало в процедура под, тогава стойността му по подразбиране е 10. Ако обаче аргументът iFontSizeпремина в процедура под, тогава избраният диапазон от клетки ще бъде зададен на размера на шрифта, посочен от потребителя.
VBA Подпроцедура: Пример 2: Центрирано подравняване и удебелен шрифт в избран диапазон от клетки
Следващата процедура е подобна на току-що обсъдената, но този път, вместо преоразмеряване, към избрания диапазон от клетки се прилага удебелен стил на шрифта. Това е примерна процедура под, който не приема аргументи:
Sub Format_Centered_And_Bold() Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Bold = True End Sub
Извикване на процедура "Sub" в Excel VBA
Извикайте VBA процедура "Sub" от друга процедура
За да извикате VBA процедура подот друга VBA процедура, трябва да напишете ключовата дума обади се, име на процедурата поди по-нататък в скоби са аргументите на процедурата. Това е показано в примера по-долу:
Sub main() Формат на обаждането_Центъриран_и_размер(20) Край под
Ако процедурата Format_Centered_And_Sizedима повече от един аргумент, те трябва да бъдат разделени със запетаи. Като този:
Sub main() Формат на извикване_Centered_And_Sized(arg1, arg2, ...) Край под
Извикайте VBA процедурата "Sub" от работен лист
Процедура подне може да се въведе директно в клетка на лист на Excel, както може да се направи с процедура функциязащото процедурата подне връща стойност. Въпреки това, процедури под, които нямат аргументи и са декларирани като Обществени(както е показано по-долу) ще бъде достъпно за потребителите на работния лист. По този начин, ако простите процедури, обсъдени по-горе подвмъкнат в модул в редактора на Visual Basic, процедурата Format_Centered_And_Boldще бъде достъпно за използване в работен лист на Excel и процедурата Format_Centered_And_Sized– няма да бъде достъпно, защото има аргументи.
Ето един лесен начин да стартирате (или изпълните) процедура под, достъпен от работния лист:
- Щракнете върху Alt+F8(натиснете клавиша алти докато го държите натиснат, натиснете клавиша F8).
- В списъка с макроси, който се показва, изберете този, който искате да стартирате.
- Щракнете върху Бягай(бягай)
За извършване на процедура подбързо и лесно, можете да му зададете клавишна комбинация. За това:
- Щракнете върху Alt+F8.
- В списъка с макроси, който се показва, изберете този, на който искате да зададете клавишна комбинация.
- Щракнете върху Параметри(Опции) и в диалоговия прозорец, който се показва, въведете клавишната комбинация.
- Щракнете върху Добреи затворете диалоговия прозорец макрос(макрос).
внимание:Когато присвоявате клавишна комбинация на макрос, уверете се, че не се използва като стандарт в Excel (напр. ctrl+c). Ако изберете вече съществуваща клавишна комбинация, тя ще бъде пренасочена към макроса и в резултат на това потребителят може да стартира макроса случайно.
VBA Обхват на процедурата
Част 2 от този урок обсъжда обхвата на променливите и константите и ролята на ключовите думи. ОбществениИ Частен. Тези ключови думи могат да се използват и с VBA процедури:
Не забравяйте, че ако преди деклариране на VBA процедура функцияили подключова дума не е въведена, свойството по подразбиране е зададено за процедурата Обществени(тоест ще бъде достъпен навсякъде в този VBA проект). Това е в контраст с декларациите на променливи, които по подразбиране са Частен.
Ранно излизане от VBA процедури "Функция" и "Под"
Ако трябва да прекратите изпълнението на VBA процедура функцияили под, без да чакате естествения му край, тогава за това има оператори функция за излизанеИ изход под. Използването на тези оператори е показано по-долу, като се използва проста процедура като пример. функция A, който очаква да получи положителен аргумент за извършване на по-нататъшни операции. Ако на процедурата се подаде неположителна стойност, тогава не могат да се извършват други операции, така че на потребителя трябва да се покаже съобщение за грешка и процедурата трябва да излезе незабавно:
Функция VAT_Amount(sVAT_Rate As Single) Като Единична DAT_Amount = 0 Ако sVAT_Rate<= 0 Then MsgBox "Expected a Positive value of sVAT_Rate but Received " & sVAT_Rate Exit Function End If ... End Function
Моля, имайте предвид, че преди да завършите процедурата функция – ДДС_сума, в кода се вмъква вградена VBA функция MsgBox, който показва изскачащ прозорец с предупреждение до потребителя.
Функция-процедурае специален вид VBA процедура, която връща резултат. Функциите на дефинирани от потребителя процедури, като вградените функции на VBA, могат да имат незадължителни и именувани аргументи. Не можете да използвате записващото устройство за макроси за записване на функционална процедура, въпреки че можете да редактирате макрос, записан от рекордера, и да го превърнете във функционална процедура.
Основната разлика между функция-процедура и други процедури, освен факта, че функциите връщат стойност, а процедурите не, е, че една функция-процедура използва ключови думи функцияИ крайна функция.
Синтаксис:
Име на функция()
„Изявления на VBA
крайна функция
Функцията е ключова дума, декларираща началото на функция.
Име - името на функцията. Имената на функциите следват същите правила като други имена на VBA идентификатори.
Arglist - списък с аргументи към тази функция, незадължителен елемент.
Тип - произволен тип връщана стойност на функцията. Ако типът не е дефиниран, резултатът, върнат от функцията на процедурата, е от тип Variant.
Име = израз - присвояване на функция, което казва на VBA каква стойност трябва да върне функцията, по избор. Въпреки това, винаги трябва да включвате оператор за присвояване във функция на процедура.
Крайна функция - ключови думи, които завършват функцията.
Дори ако функцията няма аргументи (например Сега, Дата), скоби трябва да се използват в декларацията на функцията.
Обикновено функцията е предназначена за извършване на изчисление и връщане на резултат. При деклариране на процедура на функция се посочва името на всеки аргумент, предаден на функцията. Имената на аргументите в списъка са разделени едно от друго със запетая и трябва да следват правилата, които се прилагат за всеки VBA идентификатор.
По-долу е даден списък на използването на проста функционална процедура, която премахва началните и крайните интервали и отчита броя на знаците в израза.
Без да използвате функцията, списъкът ще изглежда така:
От този прост пример мисля, че основната идея за използване на функции-процедури е ясна - подобряване на четливостта на програмния код и намаляването му (с други думи, функцията-процедурата се пише, когато едно и също "парче" код се среща повече от 2-3 пъти в програмния код). Наистина, ако нашата функция-процедура се състои не от един ред, а, да речем, от 10 реда; и програмният код ще използва тази функция-процедура 5 пъти, тогава общият списък на програмата ще бъде по-малък от 38 реда.
Както беше посочено по-рано, VBA предава всички аргументи на функцията на процедурата като типове варианти. Можете да декларирате специфични типове данни за всеки аргумент в списъка с аргументи.
Когато пишете VBA код в Excel, на всяка стъпка се използва набор от вградени оператори. Тези оператори са разделени на математически, низови, сравнителни и логически оператори. След това ще разгледаме подробно всяка група оператори.
Математически оператори
Основните VBA математически оператори са изброени в таблицата по-долу.
Дясната колона на таблицата показва приоритета на оператора по подразбиране при липса на скоби. Чрез добавяне на скоби към израз, можете да промените реда, в който VBA операторите се изпълняват, както желаете.
Строкови оператори
Основният оператор на низ в Excel VBA е операторът за конкатенация & (сливане):
Оператори за сравнение
Операторите за сравнение се използват за сравняване на две числа или низове и връщане на булева стойност от тип булев(Истина или лъжа). Основните оператори за сравнение на Excel VBA са изброени в тази таблица:
Логически оператори
Логическите оператори, като операторите за сравнение, връщат булева стойност от тип булев(Истина или лъжа). Основните логически оператори на Excel VBA са изброени в таблицата по-долу:
Таблицата по-горе не изброява всички логически оператори, налични във VBA. Пълен списък с логически оператори може да бъде намерен на сайта на Visual Basic Developer Center.
Вградени функции
Във VBA има много вградени функции, които могат да се използват при писане на код. По-долу са изброени някои от най-често използваните:
| Функция | Действие |
|---|---|
| коремни мускули | Връща абсолютната стойност на даденото число.
|
| Хр | Връща ANSI знака, съответстващ на числовата стойност на параметъра.
|
| Дата | Връща текущата системна дата. |
| Дата Добавяне | Добавя определен интервал от време към дадена дата. Синтаксис на функцията: Добавяне на дата( интервал , номер , дата на ) Къде е аргументът интервалопределя вида на интервала от време, добавен към даденото датав размера, посочен в аргумента номер . Аргумент интервалможе да приеме една от следните стойности:
|
| DateDiff | Изчислява броя на определени интервали от време между две дадени дати.
|
| ден | Връща цяло число, съответстващо на деня от месеца в дадена дата. пример: ден („29/01/2015”)връща числото 29. |
| час | Връща цяло число, съответстващо на броя часове в дадения момент. пример: Час („22:45:00“)връща числото 22. |
| InStr | Той приема цяло число и два низа като аргументи. Връща позицията на срещане на втория низ в първия, като започва търсенето на позицията, дадена от цяло число.
Забележка:Аргументът за число може да не е посочен, като в този случай търсенето започва от първия символ на низа, посочен във втория аргумент на функцията. |
| международен | Връща цялата част от даденото число. пример: Int(5,79)връща резултат 5. |
| Isdate | се завръща Вярноако дадената стойност е дата, или Невярно– ако датата не е.
|
| IsError | се завръща Вярноако дадената стойност е грешка, или Невярно– ако не е грешка. |
| Липсва | Името на незадължителен аргумент на процедурата се предава като аргумент на функцията. Липсвасе завръща Вярноако не е предадена стойност за въпросния аргумент на процедурата. |
| IsNumeric | се завръща Вярноако дадената стойност може да се третира като число, в противен случай се връща Невярно. |
| Наляво | Връща посочения брой знаци от началото на дадения низ. Синтаксисът на функцията е такъв: Наляво( линия , дължина ) където линияе оригиналният низ и дължинае броят на знаците за връщане, като се брои от началото на низа.
|
| Лен | Връща броя на знаците в низ. пример: Лен("abcdej")връща числото 7. |
| месец | Връща цяло число, съответстващо на месеца на дадена дата. пример: Месец („29/01/2015”)връща стойността 1. |
| средата | Връща посочения брой знаци от средата на дадения низ. Синтаксис на функцията: Среда( линия , Започнете , дължина ) където линияе оригиналният низ Започнете- позицията на началото на низа, който трябва да бъде извлечен, дължинае броят на знаците, които трябва да бъдат извлечени.
|
| Минута | Връща цяло число, съответстващо на броя минути в даденото време. Пример: Минута ("22:45:15")връща стойността 45. |
| Сега | Връща текущата системна дата и час. |
| правилно | Връща посочения брой знаци от края на дадения низ. Синтаксис на функцията: правилно ( линия , дължина ) Където линияе оригиналният низ и дължинае броят на знаците за извличане, като се брои от края на дадения низ.
|
| Второ | Връща цяло число, съответстващо на броя секунди в даденото време. пример: Второ („22:45:15“)връща стойността 15. |
| кв | Връща корен квадратен от числовата стойност, предадена в аргумента.
|
| Време | Връща текущото системно време. |
| Ubound | Връща горния индекс на посоченото измерение на масива. Забележка:За многоизмерни масиви, незадължителен аргумент може да бъде индексът на кое измерение да се върне. Ако не е посочено, по подразбиране е 1. |
VBA използва следните типове функции:
Математически вградени функции;
Математически функции, които не са представени във VBA;
Функции за форматиране на данни;
Функции за преобразуване на типа
Математически вградени функции
|
Връщана стойност |
|||||||||||||||||||||
|
Абсолютната стойност на числото |
|||||||||||||||||||||
|
arctan(x) – дъгова тангенс на стойността на параметъра, дадена в радиани |
|||||||||||||||||||||
|
sin(x) - връща синуса на ъгъла от стойността на параметъра, дадена в радиани |
|||||||||||||||||||||
|
cos(x) - косинус на посочения ъгъл в радиани |
|||||||||||||||||||||
|
tg(x) - връща тангенса на ъгъла от стойността на параметъра, посочена в радиани |
|||||||||||||||||||||
|
e x - връща числото e, повдигнато до определената степен, където e е основата на естествения логаритъм |
|||||||||||||||||||||
|
ln(x) - връща естествения логаритъм на стойността на числов израз |
|||||||||||||||||||||
|
|
|||||||||||||||||||||
|
Случайно число от интервал) Именовани числови формати
Повече информация по темата
|
- връща квадратния корен на числов израз
