Форматування чисел у Excel VBA
У робочих аркушах Excel цифри представлені у всіх форматах. Можливо, ви вже знайомі зі спливаючим вікном у Excel для використання різних числових форматів:
Форматування чисел полегшує їх читання та розуміння. За замовчуванням Excel для чисел, введених у клітинки, - це формат "Загальні", що означає, що номер відображається точно так, як ви його ввели.
Наприклад, якщо ввести кругле число, напр. 4238, він буде відображатися як 4238 без десяткової коми або роздільників тисяч. Буде відображено десяткове число, таке як 9325,89 з десятковою комою та десятковими знаками. Це означає, що він не буде вишиковуватись у стовпці з круглими числами, і буде виглядати надзвичайно безладно.
Крім того, не показуючи роздільників тисяч, важко побачити, наскільки велике число насправді, без підрахунку окремих цифр. Це мільйони чи десятки мільйонів?
З точки зору користувача, який дивиться вниз по стовпцю чисел, це ускладнює читання та порівняння.
У VBA у вас є доступ до точно такого ж діапазону форматів, який ви маєте на передньому кінці Excel. Це стосується не тільки введеного значення у клітинку на аркуші, але й таких речей, як вікна повідомлень, елементи керування UserForm, діаграми та графіки, а також рядок стану Excel у нижньому лівому куті аркуша.
Функція Format є надзвичайно корисною функцією у VBA з точки зору презентації, але вона також дуже складна з точки зору гнучкості, яка пропонується у відображенні цифр.
Як використовувати функцію форматування у VBA
Якщо ви показуєте вікно повідомлення, то функцію Формат можна використовувати безпосередньо:
| 1 | Формат MsgBox (1234567.89, "#, ## 0.00") |
Це відобразить велику кількість, використовуючи коми, щоб розділити тисячі та показати 2 знаки після коми. Результат буде 1 234 567,89. Нулі замість хешу гарантують, що десяткові дроби відображатимуться як 00 цілими числами, а для числа, меншого за 1, є початковий нуль.
Символ хештегу (#) являє собою заповнювач цифр, який відображає цифру, якщо вона доступна в цій позиції, або нічого.
Ви також можете використовувати функцію форматування для звернення до окремої клітинки або діапазону клітинок для зміни формату:
| 1 | Аркуші ("Аркуш1"). Діапазон ("A1: A10"). NumberFormat = "#, ## 0.00" |
Цей код встановлює діапазон клітинок (від A1 до A10) у користувацькому форматі, який розділяє тисячі комами та показує 2 знаки після коми.
Якщо ви перевірите формат клітинок на передній панелі Excel, то виявите, що створено новий власний формат.
Ви також можете відформатувати номери в рядку стану Excel у нижньому лівому кутку вікна Excel:
| 1 | Application.StatusBar = Формат (1234567.89, "#, ## 0.00") |
Ви видаляєте це з рядка стану за допомогою:
| 1 | Application.StatusBar = "" |
Створення рядка формату
У цьому прикладі буде додано текст "Загальний обсяг продажів" після кожного номера, а також буде додано роздільник тисяч
| 1 | Аркуші ("Аркуш1"). Діапазон ("A1: A6"). NumberFormat = "#, ## 0.00" "Загальний обсяг продажів" "" |
Ось так будуть виглядати ваші цифри:
Зверніть увагу, що комірка A6 має формулу "SUM", і вона включатиме текст "Загальний обсяг продажів" без необхідності форматування. Якщо застосовано форматування, як у наведеному вище коді, це не додасть додатковий екземпляр "Загальний обсяг продажів" у клітинку A6
Хоча зараз у клітинках відображаються буквено -цифрові символи, цифри все ще присутні у цифровій формі. Формула "SUM" все ще працює, оскільки вона використовує числове значення у фоновому режимі, а не спосіб форматування числа.
Кома у рядку формату містить роздільник тисяч. Зауважте, що вам потрібно ввести це в рядок лише один раз. Якщо число обчислюється мільйонами або мільярдами, воно все одно розділить цифри на групи по 3
Нуль у рядку форматування (0) є заповнювачем цифр. Він відображає цифру, якщо вона є, або нуль. Його розташування дуже важливо для забезпечення однорідності з форматуванням
У рядку форматування символи хешу (#) не відображатимуть нічого, якщо немає цифри. Однак, якщо є число типу .8 (усі десяткові дроби), ми хочемо, щоб воно відображалося як 0.80, щоб воно співпадало з іншими числами.
Використовуючи один нуль ліворуч від десяткової коми та два нулі праворуч від десяткової коми у рядку форматування, це дасть необхідний результат (0,80).
Якби праворуч від десяткової коми знаходився лише один нуль, результат мав би бути „0,8“, і все відображатиметься на один десятковий знак.
Використання рядка форматування для вирівнювання
Ми можемо захотіти побачити всі десяткові числа в діапазоні, вирівняному за їх десятковими крапками, щоб усі десяткові коми знаходилися безпосередньо один під одним, однак на кожному числі є багато місць десяткових дробів.
Для цього ви можете використовувати знак питання (?) У рядку формату. "?" Означає, що число відображається, якщо воно є, або пробіл
| 1 | Аркуші ("Аркуш1"). Діапазон ("A1: A6"). NumberFormat = "#, ## 0.00 ??" |
Це відобразить ваші номери таким чином:
Тепер усі десяткові коми розташовуються один під одним. Ячейка A5 має три знаки після коми, і це нормально вирівнює вирівнювання, але використання символу "?" Вирівнює все ідеально.
Використання буквальних символів у рядку формату
Ви можете додати будь -який буквальний символ до рядка формату, поставивши перед ним зворотну косу риску (\).
Припустимо, що ви хочете показати певний індикатор валюти для своїх номерів, який не залежить від вашого регіону. Проблема в тому, що якщо ви використовуєте індикатор валюти, Excel автоматично посилається на ваш локальний та змінює його на відповідний для локалі, встановленої на панелі керування Windows. Це може мати наслідки, якщо ваш додаток Excel розповсюджується в інших країнах, і ви хочете переконатися, що незалежно від регіону, індикатор валюти завжди однаковий.
Ви також можете вказати, що цифри в мільйонах у наведеному нижче прикладі:
| 1 | Аркуші ("Аркуш1"). Діапазон ("A1: A6"). NumberFormat = "\ $#, ## 0.00 \ м" |
Це дасть на вашому аркуші такі результати:
Використовуючи зворотну косу риску для відображення буквальних символів, вам не потрібно використовувати зворотну косу риску для кожного окремого символу в рядку. Ви можете використовувати:
| 1 | Аркуші ("Аркуш1"). Діапазон ("A1: A6"). NumberFormat = "\ $#, ## 0.00 \ mill" |
Після кожного числа у форматованому діапазоні відображатиметься «фреза».
Більшість символів можна використовувати як літерали, але не зарезервовані символи, такі як 0, #,?
Використання коми у рядку формату
Ми вже бачили, що коми можна використовувати для створення тисяч роздільників для великих чисел, але їх також можна використовувати іншим способом.
Використовуючи їх у кінці числової частини рядка форматування, вони виконують роль масштабників тисяч. Іншими словами, вони будуть ділити кожне число на 1000 кожного разу, коли з’являється кома.
У наведених прикладах ми показуємо їх із показником, що це мільйони. Вставивши одну кому в рядок форматування, ми можемо показати ці числа, поділені на 1000.
| 1 | Аркуші ("Аркуш1"). Діапазон ("A1: A6"). NumberFormat = "\ $#, ## 0.00, \ m" |
Це покаже цифри, поділені на 1000, хоча вихідне число все одно буде залишатися у фоновому режимі в комірці.
Якщо ви поставите дві коми в рядку форматування, то числа будуть поділені на мільйон
| 1 | Аркуші ("Аркуш1"). Діапазон ("A1: A6"). NumberFormat = "\ $#, ## 0.00 ,, \ m" |
Це буде результат, використовуючи лише одну кому (розділіть на 1000):
Створення умовного форматування в рядку формату
Ви можете налаштувати умовне форматування у передньому кінці Excel, але це також можна зробити у своєму коді VBA, що означає, що ви можете керувати рядком формату програмно, щоб внести зміни.
У рядку формату можна використовувати до чотирьох розділів. Кожен розділ розділений крапкою з комою (;). Чотири розділи відповідають позитивному, негативному, нульовому та текстовому
| 1 | Діапазон ("A1: A7"). NumberFormat = "#, ## 0.00; [Червоний]-#, ## 0.00; [Зелений]#, ## 0.00; [Синій]" |
У цьому прикладі ми використовуємо однакові символи хеша, коми та нуля, щоб надати тисячі роздільників та два десяткових знака, але тепер у нас є різні розділи для кожного типу значення.
Перший розділ стосується позитивних чисел і нічим не відрізняється від того, що ми вже бачили раніше з точки зору формату.
Другий розділ для від’ємних чисел вводить колір (червоний), який утримується в парі квадратних дужок. Формат такий же, як і для позитивних чисел, за винятком того, що попереду був доданий знак мінус (-).
Третій розділ для нульових чисел використовує колір (зелений) у квадратних дужках з числовим рядком таким же, як і для позитивних чисел.
Останній розділ стосується текстових значень, і все, що йому потрібно, це колір (синій) знову в квадратних дужках
Це результат застосування цього рядка формату:
Ви можете піти далі з умовами в рядку форматування. Припустимо, що ви хотіли б показати кожне позитивне число вище 10000 як зелене, а кожне інше число як червоне, ви можете використовувати цей рядок форматування:
| 1 | Діапазон ("A1: A7"). NumberFormat = "[> = 10000] [Зелений]#, ## 0.00; [<10000] [Червоний]#, ## 0.00" |
Цей рядок формату містить умови для> = 10000, встановлені у квадратних дужках, так що зелений буде використовуватися лише там, де число більше або дорівнює 10000
Ось такий результат:
Використання дробів у форматуванні рядків
Дроби не часто використовуються в електронних таблицях, оскільки вони зазвичай прирівнюються до десяткових дробів, з якими всі знайомі.
Однак іноді вони служать певній меті. У цьому прикладі відображатимуться долари та центи:
| 1 | Діапазон ("A1: A7"). NumberFormat = "#, ## 0" "доларів і" "00/100" "центів" "" |
Ось результат, який буде отриманий:
Пам’ятайте, що, незважаючи на те, що цифри відображаються у вигляді тексту, вони все ще знаходяться у фоновому режимі як цифри, і всі формули Excel все ще можна використовувати на них.
Формати дати та часу
Дати - це фактично числа, і ви можете використовувати їх формати так само, як і для чисел. Якщо ви відформатуєте дату як числове число, ви побачите велику кількість ліворуч від десяткової коми і кількість знаків після коми. Число ліворуч від десяткової коми показує кількість днів, починаючи з 01 січня 1900 року, а десяткові знаки показують час на основі 24 годин
| 1 | Формат MsgBox (зараз (), "dd-mmm-yyyy") |
Це відформатує поточну дату, яка буде показувати '08-липень-2020 '. Використання "ммм" для місяця відображає перші три символи назви місяця. Якщо вам потрібна повна назва місяця, ви використовуєте "мммм"
У рядок формату можна включити час:
| 1 | Формат MsgBox (зараз (), "dd-mmm-yyyy hh: mm AM/PM") |
Буде відображатись '08-липень 2020 01:25 вечора '
"Hh: mm" позначає години та хвилини, а AM/PM використовує 12-годинний годинник на відміну від 24-годинного.
Ви можете включити текстові символи до рядка формату:
| 1 | Формат MsgBox (зараз (), "dd-mmm-yyyy hh: мм AM/PM" "сьогодні" "") |
На дисплеї відображатиметься "08-Липень-2020 сьогодні о 13:25"
Ви також можете використовувати буквальні символи, використовуючи зворотну косу риску спереду так само, як і для рядків числового формату.
Попередньо визначені формати
Excel має ряд вбудованих форматів для чисел та дат, які можна використовувати у своєму коді. Вони в основному відображають те, що є у передньому кінці форматування чисел, хоча деякі з них виходять за межі того, що зазвичай доступне у спливаючому вікні. Крім того, у вас немає гнучкості щодо кількості десяткових знаків або того, чи використовуються тисячі роздільників.
Загальний номер
Цей формат відображатиме номер точно таким, яким він є
| 1 | Формат MsgBox (1234567,89, "Загальний номер") |
Результат буде 1234567,89
Валюта
| 1 | Формат MsgBox (1234567.894, "Валюта") |
Цей формат додасть символ валюти перед номером, наприклад $, £ залежно від вашого регіону, але він також відформатує число до 2 знаків після коми і розділить тисячі комами.
Результат складе 1 234 567,89 доларів
Виправлено
| 1 | Формат MsgBox (1234567.894, "Виправлено") |
Цей формат відображає принаймні одну цифру зліва, але лише дві цифри праворуч від десяткової коми.
Результат буде 1234567,89
Стандартний
| 1 | Формат MsgBox (1234567.894, "Стандартний") |
Тут відображається число з тисячами роздільників, але лише до двох знаків після коми.
Результат буде 1 234 567,89
Відсоток
| 1 | Формат MsgBox (1234567.894, "Відсоток") |
Число помножується на 100 і в кінці числа додається символ відсотка (%). Формат відображається до 2 знаків після коми
Результат буде 123456789,40%
Науковий
| 1 | Формат MsgBox (1234567.894, "Науковий") |
Це перетворює число в експоненціальний формат
Результат буде 1.23E+06
Так ні
| 1 | Формат MsgBox (1234567.894, "Так/Ні") |
Це відображає "Ні", якщо число нульове, інакше відображається "Так"
Результатом буде "Так"
Правда/Неправда
| 1 | Формат MsgBox (1234567.894, "True/False") |
Це відображає "False", якщо число дорівнює нулю, інакше відображається "True"
Результатом буде "Правда"
Увімкнено вимкнено
| 1 | Формат MsgBox (1234567.894, "Увімк./Вимк.") |
Це відображає "Вимкнено", якщо число нульове, інакше відображається "Увімкнено"
Результатом буде "Увімкнено"
Загальна дата
| 1 | Формат MsgBox (зараз (), "Загальна дата") |
Це відобразить дату як дату та час, використовуючи позначення AM/PM. Спосіб відображення дати залежить від ваших налаштувань на панелі керування Windows (Годинник та регіон | регіон). Він може відображатися як "мм/дд/рррр" або "дд/мм/рррр"
Результат буде "07.07.2020 15:48:25"
Довге побачення
| 1 | Формат MsgBox (зараз (), "довга дата") |
Буде відображатися довга дата, визначена на Панелі керування Windows (Годинник та регіон | Регіон). Зауважте, що він не включає час.
Результатом буде "вівторок, 7 липня 2022 року"
Середня дата
| 1 | Формат MsgBox (зараз (), "Середня дата") |
Тут відображається дата, визначена в налаштуваннях короткої дати, визначена мовою на Панелі керування Windows.
Результатом буде '07 -20 липня '
Коротке побачення
| 1 | Формат MsgBox (зараз (), "Коротка дата") |
Відображає коротку дату, визначену на панелі керування Windows (годинник та регіон | регіон). Спосіб відображення дати залежить від вашого регіону. Він може відображатися як "мм/дд/рррр" або "дд/мм/рррр"
Результатом буде "7.07.2020"
Довгий час
| 1 | Формат MsgBox (зараз (), "довгий час") |
Відображає тривалий час, як визначено на Панелі керування Windows (Годинник та регіон | Регіон).
Результат буде "4:11:39 вечора"
Середній час
| 1 | Формат MsgBox (зараз (), "середній час") |
Відображає середній час, визначений вашою мовою на Панелі керування Windows. Зазвичай це встановлюється як 12-годинний формат з використанням годин, хвилин та секунд, а також у форматі AM/PM.
Результат буде '04: 15 PM '
Короткий час
| 1 | Формат MsgBox (зараз (), "короткий час") |
Відображає середній час, визначений у Панелі керування Windows (Годинник та регіон | Регіон). Зазвичай це встановлюється як 24-годинний формат з годинами та хвилинами
Результат буде '16: 18 '
Небезпека використання заздалегідь визначених форматів Excel у датах та часах
Використання заздалегідь визначених форматів дат і часу в Excel VBA дуже залежить від налаштувань на Панелі керування Windows, а також від того, для якої локалі встановлено
Користувачі можуть легко змінити ці налаштування, і це вплине на те, як ваші дати та час відображаються в Excel
Наприклад, якщо ви розробляєте програму Excel, яка використовує заздалегідь визначені формати у вашому коді VBA, вони можуть повністю змінитися, якщо користувач перебуває в іншій країні або використовує іншу мову для вас. Ви можете виявити, що ширини стовпців не відповідають визначенню дати, або у формі користувача елемент керування Active X, наприклад елемент зі списком (спадне меню), занадто вузький, щоб дати та час відображалися належним чином.
Під час розробки програми Excel потрібно враховувати географічну аудиторію
Користувацькі формати для чисел
Існує ряд різних параметрів, які можна використовувати під час визначення рядка форматування:
| Персонаж | Опис |
| Нульовий рядок | Немає форматування |
| 0 | Заповнювач цифр. Відображає цифру або нуль. Якщо для цієї позиції є цифра, вона відображає цифру, інакше вона відображає 0. Якщо цифр менше, ніж нулів, то ви отримаєте початкові або кінцеві нулі. Якщо після десяткової коми цифр більше, ніж нулів, число округлюється до кількості десяткових знаків, позначених нулями. Якщо перед десятковою точкою є більше цифр, ніж нулі, вони відображатимуться нормально. |
| # | Заповнювач цифр. Тут відображається цифра або нічого. Він працює так само, як і нульовий заповнювач вище, за винятком того, що початкові та кінцеві нулі не відображаються. Наприклад, 0,75 відображатиметься з використанням нульових заповнювачів, але це буде 0,75 з використанням # заповнювачів. |
| . Десяткова точка. | Дозволений лише один рядок формату. Цей символ залежить від налаштувань на панелі керування Windows. |
| % | Відсоток заповнювача. Множить число на 100 і розміщує символ % там, де він відображається у рядку форматування |
| , (кома) | Тисяча роздільників. Це використовується, якщо використовується 0 або # заповнювачів, а рядок форматування містить кому. Одна кома ліворуч від десяткової коми позначає округлення до найближчої тисячі. Наприклад, ## 0, Дві суміжні коми зліва від роздільника тисяч означають округлення до найближчого мільйона. Наприклад, ## 0 ,, |
| E- E+ | Науковий формат. Це відображає число експоненціально. |
| : (двокрапка) | Роздільник часу - використовується при форматуванні часу для поділу годин, хвилин і секунд. |
| / | Розділювач дати - використовується для визначення формату дати |
| - + £ $ ( ) | Відображає буквальний символ.Щоб відобразити символ, відмінний від перерахованого тут, передуйте йому зворотну косу риску (\) |
Визначені користувачем формати дати та часу
Усі ці символи можна використовувати у форматуванні рядка під час форматування дати та часу:
| Персонаж | Значення |
| c | Відображає дату як ddddd, а час як ttttt |
| d | Відображення дня у вигляді числа без нуля |
| дд | Відобразіть день як число з нулем на початку |
| ддд | Відображення дня як абревіатури (нд - сб) |
| dddd | Відображення повної назви дня (неділя - субота) |
| ddddd | Відобразіть серійний номер дати як повну дату відповідно до короткої дати в міжнародних налаштуваннях панелі керування Windows |
| dddddd | Відображає серійний номер дати як повну дату відповідно до Long Date у міжнародних налаштуваннях Панелі керування Windows. |
| w | Відображає день тижня як число (1 = неділя) |
| ww | Відображає тиждень року як число (1-53) |
| м | Відображає місяць як число без нуля |
| мм | Відображає місяць як число з початковими нулями |
| ммм | Відображає місяць як абревіатуру (січень-грудень) |
| мммм | Відображає повну назву місяця (січень - грудень) |
| q | Відображає квартал року як число (1-4) |
| y | Відображає день року як число (1-366) |
| yy | Відображає рік як двозначне число |
| рррр | Відображає рік у вигляді чотиризначного числа |
| h | Відображає годину як число без нуля |
| hh | Відображає годину як число з початковим нулем |
| n | Відображає хвилину як число без нуля |
| нн | Відображає хвилину як число з початковим нулем |
| s | Відображає друге як число без нуля |
| ss | Відображає друге як число з початковим нулем |
| ttttt | Відображення серійного номера часу як повного часу. |
| ДООБІДУ, ПІСЛЯ ОБІДУ | Використовуйте 12-годинний годинник та відображайте AM або PM для вказівки до або після полудня. |
| дообіду, після обіду | Використовуйте 12-годинний годинник та використовуйте am або pm для вказівки до або після обіду |
| A/P | Використовуйте 12-годинний годинник та використовуйте A або P для вказівки до або після обіду |
| а/с | Використовуйте 12-годинний годинник та використовуйте a або p для вказівки до або після обіду |
