Функція TEXT дає змогу змінити спосіб відображення числа, застосувавши до нього форматування з кодами форматів. Це корисно, коли потрібно відобразити числа в зручному для читання форматі або об’єднати їх із текстом чи символами.
Примітка.
Функція TEXT перетворює числа на текст, що може ускладнити посилання в подальших обчисленнях. Найкраще зберігати вихідне значення в одній клітинці, а потім використовувати функцію TEXT в іншій клітинці. Потім, якщо знадобиться створити інші формули, завжди посилайтеся на вихідне значення, а не на результат функції TEXT.
Технічні відомості
Синтаксис
TEXT(значення;формат)
Синтаксис функції TEXT має такі аргументи:
| Ім’я аргументу | Опис |
|---|---|
| значення | Числове значення, яке потрібно перетворити на текст. |
| формат | Текстовий рядок, що визначає формат, який потрібно застосувати до вказаного значення. |
Огляд
У найпростішому випадку функція TEXT має такий вигляд:
- =TEXT(значення, яке потрібно відформатувати;"код формату, який потрібно застосувати")
Нижче наведено популярні приклади, які можна скопіювати прямо до Excel, щоб поекспериментувати самостійно. Зверніть увагу, що коди форматів беруться в лапки.
| Формула | Опис |
|---|---|
| =TEXT(1234,567;"###0,00 ₴") | Грошовий формат із роздільником груп розрядів і двома розрядами дробової частини, наприклад 1234,57 ₴. Зверніть увагу, що Excel округлює значення до двох розрядів дробової частини. |
| =TEXT(TODAY();"DD.MM.YY") | Сьогоднішня дата у форматі ММ/ДД/РР, наприклад 14.03.12 |
| =TEXT(TODAY();"DDDD") | Сьогоднішній день тижня, наприклад понеділок |
| =TEXT(NOW();"HH:MM") | Поточний час, наприклад 13:29. |
| =TEXT(0,285;"0,0%") | Відсотковий формат, наприклад 28,5%. |
| =TEXT(4,34 ,"# ?/?") | Дробовий формат, наприклад 13/3. |
| =TRIM(TEXT(0,34;"# ?/?")) | Дробовий формат, наприклад 1/3. Зверніть увагу, що функція TRIM використовується, щоб видалити пробіл перед дробовою частиною. |
| =TEXT(12200000;"0,00E+00") | Експоненційний формат, наприклад 1,22E+07. |
| =TEXT(1234567898;"[<=9999999]#####;( ####) ###-####") | Додатковий формат (номер телефону), наприклад (123) 456-7898. |
| =TEXT(1234;"0000000") | Додавання нулів на початку, наприклад 0001234. |
| =TEXT(123456;"##0° 00' 00''") | Спеціальний формат (широта або довгота). |
Примітка.
- Хоча для змінення форматування можна використовувати функцію TEXT, це не єдиний спосіб. Ви можете змінити формат без формули, натиснувши клавіші Ctrl+1 (або
+1 на комп'ютері Mac), а потім виберіть потрібний формат у діалоговому вікні Формат числа клітинок>. - Якщо ви використовуєте "MM" або "mm" поодинці, він поверне місяць. Якщо ви об'єднаєте його з "HH" протягом декількох годин або "SS" протягом декількох секунд, замість цього буде повернуто хвилини, наприклад. "HH:MM", "MM:SS"
Завантажте наші приклади
За бажання можна завантажити книгу з усіма прикладами застосування функції TEXT, які наведено в цій статті, і кількома іншими. Ви можете скористатися цими прикладами або створити власні коди форматів для функції TEXT.
Завантаження прикладів функції TEXT у програмі Excel
Інші доступні коди форматів
Щоб знайти інші доступні коди форматів, скористайтеся діалоговим вікном Формат клітинок .
- Натисніть клавіші Ctrl+1 (+1 на комп'ютері Mac), щоб відкрити діалогове вікно Формат клітинок .
- Виберіть потрібний формат на вкладці Число .
- Виберіть параметр Настроюваний .
- Потрібний код формату відобразиться в полі Тип. Виділіть увесь вміст поля Тип, крім крапки з комою (;) і символу @. У прикладі нижче виділено та скопійовано лише код dd.mm.yyyy.
- Натисніть клавіші Ctrl+C , щоб скопіювати код формату, а потім натисніть кнопку Скасувати , щоб закрити діалогове вікно Формат клітинок .
- Тепер залишилося натиснути клавіші Ctrl+V, щоб вставити код формату у функцію TEXT, наприклад =TEXT(B2;"dd.mm.yyyy"). Переконайтеся, що ви вставляєте код формату в лапки ("код формату"), інакше Excel надасть вам повідомлення про помилку.
Коди форматів за категоріями
Нижче наведено кілька прикладів застосування різних числових форматів до значень за допомогою діалогового вікна Формат клітинок , а потім скористайтеся параметром Настроювані , щоб скопіювати ці коди форматів до функції TEXT .
Вибір числового формату
Нулі на початку
Чому Програма Excel видаляє нулі на початку?
Excel сприймає послідовність цифр, введену в клітинку, як число, а не як цифровий код, наприклад артикул або номер SKU. Щоб зберегти нулі на початку послідовностей цифр, застосуйте до відповідного діапазону клітинок текстовий формат, перш ніж вставляти або вводити значення. Виділіть стовпець або діапазон, до якого потрібно додати значення, а потім натисніть клавіші Ctrl+1 , щоб відкрити діалогове вікно Формат > клітинок , а потім на вкладці Число виберіть текст. Тепер Excel зберігатиме початкові нулі.
Якщо ви вже ввели дані, а програма Excel видалила нулі на початку, їх можна додати знову за допомогою функції TEXT . Ви можете посилатися на верхню клітинку зі значеннями та використовувати формулу =TEXT(значення;"00000"), де кількість нулів у формулі відповідає загальній кількості символів, а потім скопіюйте та вставте їх до решти діапазону.
Якщо з якоїсь причини потрібно перетворити текстові значення на числа, їх можна помножити на 1, наприклад =D4*1, або використати подвійний унарний оператор (--), наприклад =--D4.
Відображення роздільників розрядів
В Excel групи розрядів розділяються пробілом, якщо код формату містить пробіл, оточений знаками номера (#) або нулями. Наприклад, якщо рядок формату – "####", у програмі Excel число 12200000 відображається як 12 200 000.
Пробіл після заповнювача цифри задає ділення числа на 1000. Наприклад, якщо використовується код формату "# ###,0 ", число 12200000 відображається в Excel як 12 200,0.
Примітка.
- Роздільник груп розрядів залежить від регіональних параметрів. Для України це пробіл, а в інших країнах і регіонах може використовуватися кома або крапка.
- Роздільник груп розрядів можна застосовувати в числових, грошових і фінансових форматах.
Числові, грошові та фінансові формати
Нижче наведено приклади стандартних чисел (лише роздільники розрядів і десяткових розрядів), грошових і бухгалтерських форматів. Грошовий формат дає змогу вставити вибраний символ грошової одиниці та вирівняти його за значенням, а фінансовий формат вирівнює символ грошової одиниці за лівим краєм клітинки та значенням за правим краєм. Зверніть увагу на різницю між кодами форматів грошової одиниці та бухгалтерського обліку нижче, коли фінансовий формат використовує зірочку (*), щоб створити поділ між символом і значенням.
Щоб знайти код формату для символу грошової одиниці, спочатку натисніть клавіші Ctrl+1 (або +1 на комп'ютері Mac), виберіть потрібний формат, а потім виберіть символ із розкривного списку Символ :
Потім у розділі Категорія виберіть пункт Настроюваний і скопіюйте код формату разом із символом грошової одиниці.
Примітка.
Функція TEXT не підтримує форматування за допомогою кольору. Якщо скопіювати в діалоговому вікні "Формат клітинок" код формату, у якому використовується колір, наприклад # ##0,00_₴;[Червоний]-# ##0,00_₴, то функція TEXT прийме його, але колір не відображатиметься.
Дати
Спосіб відображення дат можна змінювати, використовуючи поєднання символів "D" (для дня), "M" (для місяця) і "Y" (для року).
Коди форматів у функції TEXT не чутливі до регістра, тому можна використовувати "M" або "m", "D" або "d", "Y" або "y".
|
Мінда рекомендує… Якщо ви надаєте спільний доступ до файлів і звітів Excel користувачам із різних країн, швидше за все, дані потрібно надавати різними мовами. Excel MVP, Mynda Treacy має чудове рішення в цій статті Дати Excel, що відображаються різними мовами . У ній також є зразок книги, який можна завантажити. |
|---|
Значення часу
Спосіб відображення часу можна змінити за допомогою поєднання символів "H" (для годин), "M" (для хвилин) і "S" (для секунд). Крім того, для подання часу в 12-годинному форматі можна використовувати символи "AM/PM".
Якщо залишити "AM/PM" або "A/P", час відображатиметься на основі 24-годинної години.
Оскільки для відображення хвилин "m" може бути кілька місяців або хвилин, це потрібно зробити у форматі "h:m" або "hh:mm", щоб програма Excel правильно відображ мала хвилини. Якщо пропустити значення "h", програма Excel відобразить значення місяця. Щоб відобразити лише хвилини, використовуйте функцію RIGHT, як показано в прикладі.
У функції TEXT регістр кодів форматів не враховується, тому допускається використання символів "H" і "h", "M" і "m", "S" і "s", "AM/PM" і "am/pm".
Якщо навколо "h" ("[h]") використовуються квадратні дужки, програма Excel не скидає кількість годин кожні 24 години. Іншими словами, квадратні дужки дають змогу відобразити загальний проміжок часу. Ви можете використовувати квадратні дужки, щоб відображати проміжок часу в годинах, хвилинах і секундах.
Відсотки
Для відображення десяткових значень можна використовувати різні відсоткові (%) формати.
Дроби
Десяткові числа можна відображати у вигляді дробів, використовуючи коди форматів "?/?".
Експоненційний формат
Експоненційний формат – це спосіб відображення значення у вигляді десяткового числа від 1 до 10, помноженого на 10 у певній степені. Цей формат часто використовується, щоб скоротити великі числа.
Додаткові формати
В Excel доступні чотири додаткові формати:
- "Поштовий індекс" ("00000");
- "Індекс + 4" ("00000-0000");
- Номер телефону – "[<=9999999]###-###;( ###) ###-####"
- "Табельний номер" ("000-00-0000").
Спеціальні формати відрізнятимуться залежно від локалізації, але якщо спеціальних форматів для вашої локалізації немає або вони не відповідають вашим потребам, ви можете створити власні в діалоговому вікні Формат клітинок>– настроюване .
Типовий сценарій
Функція TEXT рідко використовується окремо, а частіше застосовується в поєднанні з чимось іще. Припустімо, потрібно об'єднати текст і числове значення, наприклад "Звіт надруковано: 14.03.12" або "Щотижневий дохід: 66 348,72 грн". Такі рядки можна ввести вручну, але Excel може зробити це за вас. На жаль, якщо поєднати текст і форматовані числа, наприклад дати, час, грошову одиницю тощо, програма Excel не знає, як їх відобразити, тому числове форматування втрачається. Тут функція TEXT неоціненна, оскільки вона дає змогу примусово форматувати значення потрібним чином, використовуючи код формату, наприклад "MM/DD/YY" для формату дати.
У наведеному нижче прикладі ви побачите, що станеться, якщо спробувати об'єднати текст і число, не використовуючи функцію TEXT . У цьому випадку ми використовуємо амперсанд (&), щоб об'єднати текстовий рядок, пробіл (" "), і значення з =A2&" "&B2.
Як бачите, програма Excel видалила форматування з дати в клітинці B2. У наступному прикладі показано, як застосувати потрібне форматування за допомогою функції TEXT.
Оновлена формула:
- клітинка C2:=A2&" "&TEXT(B2;"dd.mm.yy") – формат дати
Запитання й відповіді
Як перетворити числа на текст, наприклад 123 на "сто двадцять три"?
На жаль, це не можна зробити за допомогою функції TEXT ; потрібно використовувати код Visual Basic for Applications (VBA). Це посилання має метод: Перетворення числового значення на англійські слова в Excel.
Чи можна змінити регістр тексту?
Так, для цього можна скористатися функціями UPPER, LOWER та PROPER. Наприклад, формула =UPPER("привіт") повертає результат "ПРИВІТ".
Чи можна за допомогою функції TEXT додати новий рядок (розрив рядка) до клітинки так само, як за допомогою сполучення клавіш Alt+Enter?
Так, але це потрібно зробити кілька кроків. Спочатку виділіть клітинку або клітинки, у яких це має статися, і натисніть клавіші Ctrl+1, щоб відкрити діалогове вікно Формат > клітинок, а потім елемент керування вирівнюванням > тексту встановіть прапорець Обтікання текстом.> Потім відрегулюйте завершену функцію TEXT , щоб включити функцію ASCII CHAR(10), де потрібно розрив рядка. Можливо, знадобиться змінити ширину стовпця залежно від того, як вирівнюється остаточний результат.
У цьому прикладі використано формулу: ="Сьогодні: "&CHAR(10)&TEXT(TODAY();"dd.mm.yy")
Чому Excel перетворює введені числа на щось на кшталт "1,22E+07"?
Ця функція називається Експоненційною нотацією, і excel автоматично перетворює числа, довші за 12 цифр, якщо клітинки відформатовано як загальні, і 15 цифр, якщо клітинки відформатовано як число. Якщо потрібно вводити довгі числові рядки, але не потрібно перетворювати їх, відформатуйте потрібні клітинки як текст , перш ніж вводити або вставляти значення в програму Excel.
Відображення дат різними мовами
|
|
Мінда рекомендує… Якщо ви надаєте спільний доступ до файлів і звітів Excel користувачам із різних країн, швидше за все, дані потрібно надавати різними мовами. Excel MVP, Mynda Treacy має чудове рішення в цій статті Дати Excel, що відображаються різними мовами . У ній також є зразок книги, який можна завантажити. |
|---|