Макроси(VBA процедури) (63)
Різне (39)
Баги та глюки Excel (4)
Що таке модуль? Які бувають модулі?
Будь-який код VBA має десь зберігатися. Для зберігання кодів VBA використовуються модулі, які зберігаються в книзі. Книга може містити скільки завгодно модулів. Кожен модуль у свою чергу може містити множину процедур (макросів).
Усі модулі, що є в книзі, можна подивитися через редактор VBA ( Alt+F11). Наявні модулі відображені в лівій частині редактора в провіднику об'єктів (Project Explorer).
рис.1
Сам провідник об'єктів може бути не відображено за замовчуванням і тоді його необхідно відобразити: натиснути Ctrl+Rабо в меню редактора VBA- View-Project Explorer
Модулі поділяються на п'ять основних типів:
Взагалі, якщо точніше, то лише два типи модуля - звичайний і модуль класу, т.к. Модуль листа, Модуль книги, Модуль для користувача формиі Модуль класупо суті є модулями класів. Але спеціально розділив їх у кілька типів, т.к. Саме такі типи часто використовуються при поясненнях у різних підручниках і на різноманітних форумах і в самих книгах Excel вони на вигляд і деякому функціоналу різні.
Для того, щоб створити новий стандартний модуль(Module), модуль класу (ClassModule)або форму користувача(UserForm)треба просто у вікні Провідника об'єктів (Project Explorer) клацнути правою кнопкою миші, вибрати пункт Insertі потім тип об'єкта, що додається( Module, ClassModule, UserForm). Також додати модуль можна і через меню: Insert-Тип модуля.
Видалити теж просто: клацнути правою кнопкою миші на потрібному модулі у вікні проекту та вибрати Remove. Докладніше про видалення наприкінці статті цієї статті:
СТАНДАРТНИЙ МОДУЛЬ
на рис.1 Module1
.
Найпоширеніший тип модулів, який використовують у більшості випадків. Саме в них макрорекордер створює записані макроси. Усі коди та процедури в таких модулях пишуться вручну або копіюються з інших джерел (іншого модуля, з цього сайту і т.п.). В основному саме в стандартних модулях міститься більшість кодів. Вони призначені для зберігання основних процедур та Public змінних, які можуть бути доступні згодом з будь-якого модуля. Як створити стандартний модуль: у вікні провідника об'єктів клацаємо правою кнопкою миші- Insert-Module. При записі макрорекордером модулі створюються автоматично і автоматично присвоюються імена.
Багато кодів, опублікованих у статтях на сайті, необхідно розміщувати саме у стандартних модулях. Для цього достатньо створити новий стандартний модуль, скопіювати текст коду із сайту та вставити.
МОДУЛЬ ЛИСТА
Лист1 або Sheet1 - на рис.1: Аркуш1(аркуш1), Лист2(Лист2), Аркуш3(Лист3).
Для кожного аркуша книги є окремий модуль. Потрапити в модуль аркуша простіше, ніж у інші модулі. Для цього треба просто клацнути правою кнопкою миші по ярличку аркуша та вибрати з контекстного меню пункт Вихідний текст (View Code)
в залежності від версії Excel цей пункт російською може називатися так само: Переглянути кодабо Вихідний код :
Можна і важчим шляхом піти через редактор VBA: Alt+F11і у вікні Провідника об'єктів (Project Explorer) двічі клацнути по об'єкту з ім'ям аркуша або правою кнопкою миші на модулі аркуша - View code.
Розміщуючи код у модулі аркуша слід пам'ятати, що з копіюванні чи перенесенні цього аркуша до іншої книжку код як і буде скопійований, т.к. є частиною листа. Це і плюс, і мінус одночасно. Плюс у тому, що розмістивши код у модулі аркуша можна використовувати цей аркуш як шаблон для поширення зі своїми кнопками виклику цих кодів (у тому числі створення книг кодом) і весь функціонал буде доступний. Мінус же полягає в деяких нюансах звернення до осередків (докладніше можна ознайомитися в цій статті: Як звернутися до діапазону з VBA) і необхідності розміщення ВСІХ процедур у цьому аркуші, інакше при переносі в інші книги коди можуть працювати з помилками.
У модулі аркуша містяться вбудовані процедури подій, кожна з яких відповідає за обробку певної події на цьому аркуші. Подивитися їх можна так: вибираєте об'єкт (на малюнку нижче список у лівій частині) Worksheet, а у правому списку вибираєте подію (У цьому списку всі процедури, доступні для вибраного аркуша):
Процедури, події яких вже використовуються, виділяються жирним шрифтом.
Назви подійних процедур носять досить інформативні імена і більша їх частина не потребує ретельного розшифрування. Але найчастіше вживані в будь-якому випадку вважаю за потрібне описати:
- Activate- виникає при активації самого аркуша (але не виникає, якщо відбувся перехід з однієї книги до іншої і цей аркуш є там активним)
- BeforeDoubleClick- виникає при подвійному кліку миші на будь-якому осередку аркуша. Важливо звертати увагу на аргументи, що передаються: Target і Cancel. Target - посилання на комірку, в якій було здійснено дію; Cancel - відповідає за скасування режиму редагування
- BeforeRightClick- виникає при натисканні правою кнопкою миші на будь-якому осередку аркуша. Важливо звертати увагу на аргументи, що передаються: Target і Cancel. Target - посилання на комірку, в якій було здійснено дію; Cancel - відповідає за відміну показу спливаючого меню
- Calculate- виникає при перерахуванні функцій та формул на аркуші
- Change- З'являється при зміні значень осередків на аркуші. Важливо звертати увагу на аргумент Target. Target - посилання на комірку, яка була змінена. Може відрізнятися від активного в момент обробки комірки
- Deactivate- виникає при переході з цього листа на інший лист цієї книги
- FollowHyperlink- виникає при переході за гіперпосиланням, створеним у цьому листі
- SelectionChange- виникає при зміні адреси виділеного осередку/області. Важливо звертати увагу на аргумент Target. Target – посилання на діапазон осередків, які були виділені. Збігається з виділеними на даний момент осередками
Досить важливий момент:якщо захочете познайомитися ближче з подієвими процедурами, завжди звертайте увагу на змінні, які передаються аргументами в процедуру. У більшості випадків рекомендую використовувати саме ці змінні, а не вигадувати будь-які можливості для обчислення об'єкта, який спричинив виникнення події. Для події аркуша Worksheet_Changeце змінна Target. Для прикладу вставте наведений нижче код модуль будь-якого листа:
| Private Sub Worksheet_Change(ByVal Target As Range) MsgBox "Адреса зміненого осередку:"&Target.Address&_ Адреса активного осередку:& Selection.Address, vbInformation, "www.сайт" End Sub |
Private Sub Worksheet_Change(ByVal Target As Range) MsgBox "Адреса зміненого осередку: " & Target.Address & _ "; Адреса активного осередку: " & Selection.Address, vbInformation, "www.сайт" End Sub
Після цього запишіть в комірку A1 значення 5 та натисніть Enter. Подія Change спрацює на момент завершення редагування - тобто. у момент натискання Enter. При цьому буде здійснено перехід на комірку A2 (у більшості випадків, якщо налаштуваннями не встановлено інше) і з'явиться повідомлення, яке покаже, що змінили комірку A1, а виділено зараз A2. Тобто. Target – це завжди посилання саме на змінену комірку незалежно від того, що зараз виділено. Ця подія (Worksheet_Change) не спрацьовуватиме при зміні значень осередків з формулами. Тільки ручне введення.
Примітка: для всіх кодів, наведених на сайті, достатньо просто відкрити необхідний модуль (Книги або аркуша)та вставити запропонований код. Коригування може знадобитися тільки у випадках, коли в модулі Листа або Книги вашого файлу вже є код у потрібній процедурі.
МОДУЛЬ КНИГИ
ЦяКнига або ThisWorkbook - на рис.1: Ця книга
.
У модуль книги можна потрапити тільки через провідник об'єктів (Project Explorer) редактора VBA - подвійне клацання по ЦяКнига (ThisWorkbook)або права кнопка миші на модулі - View code. У модулі книги також містяться "вбудовані" подієві процедури. Так само як і для аркуша вибираємо у списку об'єктів (вгорі зліва) Workbook. У правому вікні вибору процедур, як і з модулем аркуша, будуть усі процедури, доступні для об'єкта Ця книга. Приклад використання подійних процедур книги можна переглянути у статті Як відстежити подію (наприклад, виділення осередків) у будь-якій книзі?
Але там застосовуються ті самі правила - головне не забувати про аргументи, доступні з цих процедур і передані ним самим Excel. Наприклад, для події Workbook_BeforeClose є аргумент Cancel. Це можна використовувати, якщо не хочете, щоб книгу закрив користувач, не заповнивши комірку A1. Ось приклад такого коду:
Private Sub Workbook_BeforeClose(Cancel As Boolean) If Me.Sheets("Звіт").Range("A1").Value = "" Then MsgBox "Необхідно заповнити комірку A1 на аркуші "Звіт", vbCritical, "www. Cancel = True "скасовуємо закриття книги End If End Sub
З коду видно, що на аркуші "Звіт" повинен бути не порожній осередок A1 (лист "Звіт" теж повинен існувати в цій книзі). Але є ще одна річ - якесь Me. Це коротке звернення до об'єкта модуля класу, в даному випадку це рівнозначне зверненню ThisWorkbook. І ще один приклад коду для модуля ЭтаКнига, який забороняє зберігати вихідну книгу, дозволяючи зберегти її лише через пункт Зберегти як(SaveAs):
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If SaveAsUI = False Then "використовується просто зберегти MsgBox "Ця книга є шаблоном. Зберігати її можна тільки через Зберегти як", vbCritical, "www.сайт" Cancel = True "скасовуємо збереження книги End If End Sub
Таке може знадобитися, якщо книга є шаблоном із полями для заповнення і необхідно запобігти випадковому збереженню вихідного документа. Хоча це можна так само зробити без макросів – книгу можна зберегти з правами лише на читання.
МОДУЛІ ФОРМ
UserForm - на рис.1 UserForm1
.
Містяться всередині форми користувача (UserForm) і її об'єктів. У Користувальницьких формах в основному все зав'язано саме на процедурах подій самої форми і на елементах цієї форми (Кнопки, ТекстБокси, КомбоБокси (випадають списки) і т.д.). Дуже зручно використовувати форми користувача у своїх додатках для спілкування з користувачем. Т.к. через форми дуже зручно відстежувати дії користувача і можна заборонити доступ до аркушів з даними шляхом їх приховування. Створюється форма так само як і модуль: у вікні провідника об'єктів клацаємо правою кнопкою миші- Insert-UserForm. Приклади кодів з використанням форм можна переглянути у статтях: Кожному користувачеві свій лист/діапазон , Як залишити в комірці тільки цифри або текст?
Модуль класу
ClassModule - на рис.1 Class1
.
Найчастіше створюється спеціально для відстеження подій різних об'єктів. Навряд чи знадобиться початківцям вивчення VBA, хоча все залежить від поставленого завдання. Але зазвичай початківцям вивчати це здається надто складним. У будь-якому випадку, перед роботою з модулями класів краще навчитися хоч трохи працювати зі звичайними модулями та самостійно писати процедури. Як додати такий модуль: у вікні провідника об'єктів клацаємо правою кнопкою миші- Insert-Class Module. Докладніше про модулі класів та роботу з ними можна почитати у цій статті: Робота з модулями класів . Там описані всі основні принципи та приклад прикладного файлу.
ВИДАЛЕННЯ МОДУЛЯ
Дії видалення будь-якого з модулів однакові всім типів. Для цього необхідно перейти в проект VBA потрібної книги, вибрати потрібний модуль, клацнути по ньому правою кнопкою миші і в меню вибрати Remove (Ім'я модуля)...(Remove Module1, Remove UserForm1, Remove Class1 тощо). Після цього з'явиться вікно із запитом "Do you want to export (ім'я модуля) before removing it?". Це означає, що VBA запитує: чи хочете зберегти копію кодів модуля перед видаленням? Як правило вибирати слід Ні. Але якщо Ви хочете зберегти текст кодів з модуля в окремому файлі, то погоджуєтеся, натиснувши Так. Буде запропоновано вибрати папку для збереження модуля і можна навіть задати окреме ім'я.
ПЕРЕНОС, ІМПОРТ та ЕКСПОРТ МОДУЛЯ
Іноді потрібно модуль із однієї книги перемістити в іншу. Зробити це можна кількома способами. Найпростіший – відкрити обидві книги, перейти у провідник проектів – знайти потрібний модуль – захопити його лівою кнопкою миші і не відпускаючи кнопку перетягнути на проект іншої книги: 
Слід пам'ятати, що це можна перенести і скопіювати лише стандартний модуль, модуль класу і модуль UserForm . Коди модулів аркушів і книги доведеться переносити як звичайний текст: переходимо в модуль ЕтаКнига (звідки хочемо копіювати) -копіюємо весь код -переходимо в модуль ЕтаКнига другої книги і вставляємо скопійоване: 
Експорт модуля (збереження в окремий файл)
Якщо треба зберегти стандартний модуль, модуль класу чи модуль форми і переносити відразу ж у іншу книжку, можна експортувати модуль. Навіщо це може бути потрібно? Як правило, щоб перенести коди з дому на роботу, переслати комусь на інший ПК (пересилання файлу з макросами може бути заборонено політикою безпеки компанії) тощо. Робиться це просто: клацаємо на модулі правої кнопки миші Export file.
Експортовані модулі мають різні розширення, залежно від типу модуля. Для стандартних модулів це .bas(Module1.bas), для модулів класу - .cls(Class1.cls). А ось для модулів форм буде створено аж два файли: UserForm1 .frmта UserForm1 .frx. Їх важливо зберігати разом – один без іншого не може бути імпортований надалі у файл. У файлі.frx зберігається інформація про візуальне відображення форми та її елементи, якщо можна так сказати. У файлі.frm зберігаються безпосередньо тексти кодів для форми та службова інформація(ім'я та розміри форми, деякі глобальні директиви та посилання на файл.frx). Тому не рекомендується без відповідних навичок перейменовувати ці два файли, сподіваючись, що потім все запрацює.
Імпорт модуля (перенесення експортованого раніше у нову книгу)
Для перенесення експортованого модуля в іншу книгу треба просто у провіднику об'єктів виділити потрібний проект правою кнопкою миші. Import module-Вибрати в діалоговому вікні потрібний модуль.
Експортувати можна будь-який модуль, а ось імпортувати – ні. Модулі аркушів і книг хоч і експортуються в окремі файли(до речі, з розширенням.cls), але імпортувати їх не вийде у вигляді, як це передбачається. Вони будуть імпортовані як нові модулі класу і тільки. Тому для перенесення кодів із модулів аркушів та книг доведеться використовувати все одно копіювання та вставку безпосередньо кодів.
І на довершення - можна переносити модулі автоматично, кодами VBA: Як додати код процедури програмно, скопіювати модуль
("Bottom bar":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left"," :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","text ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"text textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; background-color:#333333;opacity:0.6;filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px "Lucida Sans Unicode", "Lucida Grande", sans-serif, Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px "Lucida Sans Unicode", "Lucida Grande", sans-serif, Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssress "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))
Працюючи в "Екселі", можна виконати безліч різних завдань, у тому числі математичних. Широкий набір інструментів програми дозволяє робити необхідні дії, що не потребують додаткових програм. Однією з таких функцій є модуль – в Excel він використовується не так часто, але має важливе призначення.
Що таке модуль
Відповідно до визначення, модуль - це математична дія, що повертає абсолютне значення числа, тобто саме число без його знака. Незважаючи на специфіку, використання модуля можна зустріти не тільки під час вирішення математичних завдань, але також і в економічних моделях, фізиці та багатьох інших точних науках.
Як обчислити модуль у "Екселі"?
Для того, щоб обчислити модуль в Excel, можна скористатися кількома способами, найпростішим з яких є функція ABS. Вона повертає абсолютне значення вибраного числа чи математичного виразу.
Синтаксис функції модуля дуже простий - в "Екселі" достатньо задати в рядку формул "=ABS", після чого в дужках вказати аргумент, яким може бути число, посилання на комірку, а також деяку функцію, що повертає числове значення. Також цю операцію можна виконати, натиснувши кнопку "Вставити функцію", де вибрати відповідну функцію, або скориставшись пошуком, вказавши слово "модуль" або "ABS".
Обчислення суми модулів
Одна з поширених операцій на Excel - сума по модулю. Вона дозволяє складати значення осередків без урахування їх знака. Щоб зробити це, не потрібно спочатку обчислювати модуль кожного числа, після чого користуватися функцією суми.
При виконанні операції, пов'язаної з кількома значеннями, модуль Excel може працювати одночасно з цілим набором або діапазоном осередків. Так, для обчислення суми за модулем достатньо скористатися наступною конструкцією запиту:
=СУМ(ABS(A1:A4))
Тут у стовпці А перших чотирьох рядках вказані значення, котрим необхідно зробити додавання по модулю.
Приклади
Щоб закріпити розуміння опису модуля в Excel та принципів його роботи, варто розглянути кілька простих прикладів, що демонструють роботу функції.
Для обчислення модуля деякого заданого числа або математичного прикладу, наприклад, різниці 2 і 5, слід скористатися наступним записом:
=ABS(2-5)
Результатом цього запиту буде число "3".
Якщо потрібно обчислити суму модулів елементів матриці, значення якої записані в таблицю, з діапазоном A1: C3, найшвидшим способом знаходження відповіді буде конструкція.
Мій колега якось запитав мене, як використовувати формули Excel, щоб обчислити суму абсолютних значень у вказаному діапазоні. Це питання досить регулярно спливає на форумах, і багато користувачів часто мають великі труднощі при роботі з цією, здавалося б, простою дією.
На жаль, у Microsoft Excel не існує вбудованої функції, здатної виконати підсумовування по модулю, тому Ви повинні трохи попрацювати, щоб отримати правильну відповідь.
Ось наші дані:
Ми бачимо, що сума чисел у діапазоні A2:A8дає результат -60 :
10 + 10 + 20 + -20 + 30 + -40 + -50 = -60
Якби ми розглядали абсолютні значення (числа без знака "-"), то результат був би 180 :
10 + 10 + 20 + 20 + 30 + 40 + 50 = 180
Варіант 1 – Використання допоміжного стовпця
На мій погляд, найкращий спосіб підрахунку суми абсолютних значень у Excel – використовувати допоміжний стовпець. У осередок B2вводимо формулу:
Потім простягнемо її до осередку B8. Функція ABSповертає модуль числа. Тож тепер ми можемо просто підсумувати діапазон B2:B8і це дасть нам результат 180 .
СУМ(B2: B8)
=SUM(B2:B8)
У моєму прикладі діапазон A1:A8- Це повноцінна таблиця даних. Тому при додаванні формули =ABS(A2)в комірку В 2, Excel розширив таблицю та автоматично заповнив усі осередки у стовпці. Далі я перейшов на вкладку Конструктор(Design), яка знаходиться у групі вкладок Робота з таблицями(Table tools), і поставив галочку біля опції Рядок підсумків(Total Row). Усі значення у стовпці Bавтоматично підсумувалися, а результат відобразився в окремому рядку.
Для підрахунку суми у рядку підсумків використовується функція ПРОМІЖНІ ВИСНОВКИ(SUBTOTAL). Це універсальна функція, яка може виконувати підсумовування, як і функція СУМ(SUM). Але є й суттєві відмінності, наприклад, ПРОМІЖНІ ВИСНОВКИ(SUBTOTAL) повністю ігнорує числа, які були приховані вручну або за допомогою фільтрації. Є ще кілька відмінностей, але це мало стосується теми цієї статті.
Спосіб з допоміжним стовпцем хороший тим, що дає велику гнучкість, якщо необхідно використовувати дані надалі, наприклад, як таблиці або зведеної таблиці. Крім цього, допоміжний стовпець може використовуватися для сортування чисел за модулем.
Це дуже хороший спосіб, але що ж робити, коли необхідно все вмістити в одну формулу без будь-яких допоміжних стовпців?
Варіант 2 – Використання функції СУМ у формулі масиву або СУМПРОВИЗВ
Використовувати формулу масиву або СУМПРОВИЗВ(SUMPRODUCT) для вирішення такого завдання – дуже грубий підхід!
Функція СУМу формулі масиву:
СУМ(ABS(A2:A8))
=SUM(ABS(A2:A8))
При введенні формули масиву не забудьте натиснути Ctrl+Shift+Enter.
Формула з СУМПРОЗВ:
СУМПРОВИЗВ(ABS(A2:A8))
=SUMPRODUCT(ABS(A2:A8))
Враховуючи, що можна використовувати ефективнішу функцію СУМІСЛІ(SUMIF), щоб отримати той самий результат (див. варіант 3), шлях з використанням цих двох формул стає непереважним. Обидві формули чудово працюють із невеликими діапазонами даних, впевнений, ви навіть не відчуєте різницю. Якщо ж знадобиться підсумувати велику кількість значень, швидкість роботи помітно уповільнюватиметься.
Варіант 3 – Використання СУМІСЛІ
Думаю, що цей підхід найзручніший із усіх раніше перерахованих. За допомогою функції СУМІСЛІ(SUMIF) значення поділяються на 2 масиви: з позитивними та негативними числами та підсумовуються. Нуль зі зрозумілих причин ігнорується. Потім ми просто віднімаємо з позитивної суми негативну (тобто підсумовуємо їх). Формула виглядає так:
СУМІСЛИ(A2:A8,">0")-СУМІСЛІ(A2:A8,"<0")
=SUMIF(A2:A8,">0")-SUMIF(A2:A8,"<0")
Можна записати і в такому вигляді:
СУМ(СУМІСЛИ(A2:A8,(">0","<0"})*{1,-1})
=SUM(SUMIF(A2:A8,(">0","<0"})*{1,-1})
Якщо ми візьмемо перший варіант формули, то отримаємо наступне:
60-(-120) = 180
Для повноти картини підрахуємо суму за допомогою другого варіанту:
СУМ((60,-120)*(1,-1)) = СУМ((60,120)) = 180
Думаю, тепер ви знаєте всі основні способи підрахунку суми абсолютних значень в Excel. Якщо ви використовуєте інший підхід для підсумовування чисел за модулем, поділіться ним у коментарях. Успіхів!
В Excel. Сьогодні розберемо формулу запису "модуль в Excel". Модуль числа застосовується визначення абсолютної величини числа, наприклад довжини відрізка. Нижче ми наводимо кілька способів розрахунку модуля числа Ексель, основна функція - ABS, а додатковий розрахунок за допомогою функцій ЯКЩО і КОРІНЬ.
Як випливає з визначення, модуль числа - це невід'ємне число, значення числа. Тобто. якщо у нас є від'ємне число -7, то по модулю воно дорівнюватиме 7. Записується модуль як дві вертикальні лінії:
|-7| = 7
Навіщо застосовується? Якщо ми маємо значення вектора рівне -7, де мінус позначає його зворотний напрямок, те щоб знайти довжину самого вектора, необхідно вирахувати модуль числа (т.к. довжина може бути негативною величиною).
Так само досить часто використання модуля можна зустріти при розрахунку негативного значення часу, але про це у нас є окрема .
У більшості мов програмування модуль числа знаходиться функцією ABS (від абсолютного значення, Abs olute). Наш улюблений Excel не є винятком.
У осередку введемо формулу:
Як випливає з опису функції, значення такої формули буде 7.
Як порахувати модуль через ЯКЩО, альтернативний спосіб
По суті, дія функції «Модуль» це визначення чи негативне число чи ні і передача його значення. Тобто легко впоратися основна функція умови =якщо().
ЯКЩО(A1<0;A1*-1;A1)
Я особисто про ABS завжди забуваю та пишу через ЯКЩО.
Модуль числа та КОРІНЬ
Як мені підказали, є особливо хитрий спосіб знайти модуль числа через функцію КОРІНЬ. Зводимо число на другий ступінь і знаходимо з вираження корінь.
КОРІНЬ(A1*A1)
Сам би не здогадався.
Приклад файлу з 3-ма розрахунками прикладаю.
Модуль числа в VBA
Як ми вже сказали у більшості мов програмування, знаходження модуль проходить за допомогою функції ABS. VBA не є винятком.
Щоб записати розрахунок у коді, пропишіть приблизно наступне:
A=Abs(-7)
Тут А дорівнюватиме 7.
В цілому ось так, якщо щось потрібно пояснити, пишіть у коментарі і не соромтеся.
Поділіться нашою статтею у ваших соцмережах:Функція ABS знаходить абсолютне значення числа, переданого як аргумент, і повертає відповідне значення.
Приклади використання функції ABS в Excel
Визначити довжину проекції відрізка на вісь Ox, який заданий координатами початкової (-7; -4) та кінцевої (19; 44) точок.
Вихідні дані:
Для визначення довжини проекції на вісь Ox використовуємо таку формулу:
B4 та B2 – початкова та кінцева координати відповідно. Різниця цих координат є довжиною проекції, що шукається. Аналогічно знайдемо величину проекції на вісь Oy. Отримані результати:
В результаті обчислення формули за модулем визначилася проекція відрізка на вісь Ox.
Розрахунок різниці між найменшим негативним та позитивним числом
Знайти різницю між найменшим і найменшим абсолютним значеннями елементів масиву.
Вихідні дані:
Для розрахунку використовуємо таку формулу (формула масиву CTRL+SHIFT+Enter):
Функція MIN виконує пошук найменшого значення в діапазоні. Для пошуку найменшого невід'ємного числа використовується вираз МІН (ABS (B3: B12)).
Отриманий результат:
Сума за модулем Excel для негативних чисел
У таблиці Excel записані всі транзакції підприємства за певний проміжок часу. Негативні значення відповідають витратним операціям. Розрахувати суму видатків за зазначений період.
Вихідні дані:
Для розрахунку використовуємо таку формулу масиву (CTRL+SHIFT+Enter):
Алгоритм розрахунків:
- При переборі елементів діапазону B3:B12, кожне значення перевіряється на належність до діапазону негативних чисел.
- Функція СУМ повертає суму абсолютних величин (завдяки використанню функції ABS), взятих із діапазону відібраних негативних чисел.
- Якщо від'ємних чисел (витратних операцій) немає, буде повернуто значення 0 (нуль).
Результати розрахунків:
Тобто загальні витрати підприємства за вказаний період становили 29020.
Особливості використання функції ABS в Excel
Функція має наступний варіант синтаксичного запису:
ABS(число)
Єдиний аргумент цієї функції (є обов'язковим для заповнення) – число. Він характеризує деяке речове число, у якого буде визначено абсолютне значення.
Примітки 1:
- Функція ABS приймає аргументи, які є числовими значеннями, текстовими уявленнями чисел або даними логічного типу (ІСТИНА, брехня).
- Якщо в якості аргументу функції, що розглядається, був переданий текстовий рядок, який не може бути перетворений до числового типу даних (наприклад, =ABS(«текст»)), результатом виконання буде код помилки #ЗНАЧ!.
- Ця функція може бути використана як формула масиву для роботи з великими діапазонами даних.
Примітки 2:
- Модуль числа дозволяє визначити якусь фізичну величину, яка математично може бути задана негативним числом, але в реальному світі представлена лише абсолютним (ненегативним) значенням, наприклад, довжина будь-якого відрізка.
- При написанні макросу може знадобитися розрахунок абсолютних значень деяких чисел. VBA використовується однойменна функція ABS() (як і в багатьох інших мовах програмування).
- В Excel існує два альтернативні способи отримання абсолютного значення деякої величини:
- використання функцій КОРІНЬ і СТУПЕНЬ з показником ступеня 2. Наприклад, результатом обчислень =КОРІНЬ(СТУПЕНЬ(A1;2)) завжди буде число з діапазону позитивних значень (за умови, що в клітинці A1 містяться дані числового типу);
- за допомогою функції ЯКЩО. Наприклад, для повернення модуля числа -5 можна використовувати запис =ЯКЩО(A1<0;A1*(-1);A1), где A1 – ячейка, в которой хранится значение -5.