перетворення числа в текст і назад у Microsoft Excel

Зміст

• Перетворення числа в текстовий вигляд
  • Спосіб 1: форматування через контекстне меню
  • Спосіб 2: інструменти на стрічці
  • Спосіб 3: Використання функції
• Перетворення тексту в число
  • Спосіб 1: перетворення за допомогою значка про помилку
  • Спосіб 2: конвертація за допомогою вікна форматування
  • Спосіб 3: конвертація за допомогою інструментів на стрічці
  • Спосіб 4: застосування формули
  • Спосіб 5: застосування спеціальної вставки
  • Спосіб 6: використання інструменту " Текст стовпцями»
  • Спосіб 7: застосування макросів
• Питання та відповіді

Однією з частих завдань, з якими стикаються користувачі програми Ексель, є перетворення числових виразів в текстовий формат і назад. Це питання часто змушує витратити на рішення багато часу, якщо Користувач не знає чіткого алгоритму дій. Давайте розберемося, як можна вирішити обидві задачі різними способами.

Перетворення числа в текстовий вигляд

Всі осередки в Ексель мають певний формат, який задає програмі, як їй розглядати ту чи іншу вираз. Наприклад, навіть якщо в них будуть записані цифри, але формат виставлений текстовий, то додаток буде розглядати їх, як простий текст, і не зможе проводити з такими даними математичні обчислення. Для того, щоб Excel сприймав цифри саме як число, вони повинні бути вписані в елемент листа із загальним або числовим форматом.

Для початку розглянемо різні варіанти вирішення завдання конвертації чисел в текстовий вид.

Спосіб 1: форматування через контекстне меню

Найчастіше користувачі виконують форматування числових виразів в текстові через контекстне меню.

1. Виділяємо ті елементи листа, в яких потрібно перетворити дані в текст. Як бачимо, у вкладці»Головна" на панелі інструментів у блоці «Число» у спеціальному полі відображається інформація про те, що дані елементи мають загальний формат, а значить, цифри, вписані в них, сприймаються програмою, як число.

2. Натискаємо правою кнопкою миші по виділенню і в меню вибираємо позицію " Формат комірок...» .

3. У вікні форматування переходимо у вкладку " Число» , якщо він був відкритий в іншому місці. У блоці налаштувань " числові формати» вибираємо позицію " текстовий» . Для збереження змін тиснемо на кнопку « OK» у нижній частині вікна.

4. Як бачимо, після даних маніпуляцій в спеціальному полі висвічується інформація про те, що осередки були перетворені в текстовий вид.

5. Але якщо ми спробуємо підрахувати автосумму, то вона відобразиться в комірці нижче. Це означає, що перетворення було здійснено не повністю. В цьому і полягає одна з фішок Excel. Програма не дає завершити перетворення даних найбільш інтуїтивно зрозумілим способом.

6. Щоб завершити перетворення, нам потрібно послідовно подвійним клацанням лівої кнопки миші помістити курсор в кожен елемент діапазону окремо і натиснути на клавішу Enter . Щоб спростити завдання замість подвійного клацання можна використовувати натискання функціональної клавіші F2 .

7. Після виконання даної процедури з усіма осередками області, дані в них будуть сприйматися програмою, як текстові вирази, а, отже, і автосумма буде дорівнює нулю. Крім того, як бачимо, лівий верхній кут осередків буде пофарбований в зелений колір. Це також є непрямим ознакою того, що елементи, в яких знаходяться цифри, перетворені в текстовий варіант відображення. Хоча ця ознака не завжди є обов'язковим і в деяких випадках така позначка відсутня.

Урок: Як змінити формат в Excel

Спосіб 2: інструменти на стрічці

Перетворити число в текстовий вид можна також скориставшись інструментами на стрічці, зокрема, використавши поле для показу формату, про який йшла розмова вище.

1. Виділяємо елементи, дані в яких потрібно перетворити в текстовий вид. Перебуваючи у вкладці»Головна" натискаємо по піктограмі у вигляді трикутника праворуч від поля, в якому відображається формат. Воно розташоване в блоці інструментів «Число» .

2. У списку варіантів форматування вибираємо пункт " текстовий» .

3. Далі, як і в попередньому способі, послідовно встановлюємо курсор в кожен елемент діапазону подвійним клацанням лівої кнопки миші або натисканням клавіші F2 , а потім клацаємо по клавіші Enter .

Дані перетворюються в текстовий варіант.

Спосіб 3: Використання функції

Ще одним варіантом перетворення числових даних в тестові в Ексель є застосування спеціальної функції, яка так і називається – ТЕКСТ . Даний спосіб підійде, в першу чергу, якщо ви хочете перенести числа як текст в окремий стовпець. Крім того, він дозволить заощадити час на перетворенні, якщо обсяг даних занадто великий. Адже, погодьтеся, що перещелківать кожну клітинку в діапазоні, що налічує сотні або тисячі рядків – це не найкращий вихід.

1. Встановлюємо курсор в перший елемент діапазону, в якому буде виводитися результат перетворення. Клацаємо по значку " Вставити функцію» , який розміщений біля рядка формул.

2. Запускається вікно майстри функцій . В категорії »текстові" виділяємо пункт " ТЕКСТ» . Після цього натискаємо на кнопку «OK» .

3. Відкривається вікно аргументів оператора ТЕКСТ . Ця функція має наступний синтаксис:

   =ТЕКСТ (значення; формат)

   Вікно, що відкриється, має два поля, які відповідають даним аргументам:»Значення" і " Формат» .

   У полі»Значення" потрібно вказати перетворюване число або посилання на клітинку, в якій воно знаходиться. У нашому випадку це буде посилання на перший елемент оброблюваного числового діапазону.

   У полі " Формат» потрібно вказати варіант відображення результату. Наприклад, якщо ми введемо «0» , то текстовий варіант на виході буде відображатися без десяткових знаків, навіть якщо в исходнике вони були. Якщо ми внесемо «0,0» , то результат буде відображатися з одним десятковим знаком, якщо «0,00» , то з двома, і т. д.

   Після того, як всі необхідні параметри введені, клацаємо по кнопці «OK» .


4. Як бачимо, значення першого елемента заданого діапазону відобразилося в осередку, яку ми виділили ще в першому пункті даного керівництва. Для того, щоб перенести і інші значення, потрібно скопіювати формулу в суміжні елементи листа. Встановлюємо курсор в нижній правий кут елемента, який містить формулу. Курсор перетворюється в маркер заповнення, який має вигляд невеликого хрестика. Затискаємо ліву кнопку миші і протягуємо по порожніх осередках паралельно діапазону, в якому знаходяться вихідні дані.

5. Тепер весь ряд заповнений необхідними даними. Але і це ще не все. По суті, всі елементи нового діапазону містять в собі формули. Виділяємо цю область і тиснемо на значок «копіювати» , який розташований у вкладці »Головна" на стрічці інструментів групі " Буфер обміну» .

6. Далі, якщо ми хочемо зберегти обидва діапазони (оригінальний та перетворений), не знімаємо виділення з області, яка містить формули. Кількома по ній правою кнопкою миші. Відбувається запуск контекстного списку дій. Вибираємо в ньому позицію " Спеціальна вставка» . Серед варіантів дій в списку вибираємо " значення та формати чисел» .

   Якщо ж користувач бажає замінити дані вихідного формату, то замість зазначеного дії потрібно виділити саме його і зробити вставку тим же способом, який вказаний вище.


7. У будь-якому випадку, в обраний діапазон будуть вставлені дані в текстовому вигляді. Якщо ж ви все-таки вибрали вставку в вихідну область, то осередки, що містять формули, можна очистити. Для цього виділяємо їх, натискаємо правою кнопкою миші і вибираємо позицію " очистити вміст» .

На цьому процедуру перетворення можна вважати закінченою.

Урок: Майстер функцій в Excel

Перетворення тексту в число

Тепер давайте розберемося, якими способами можна виконати зворотне завдання, а саме як перетворити текст в число в Excel.

Спосіб 1: перетворення за допомогою значка про помилку

Найпростіше і швидше за все виконати конвертацію текстового варіанту за допомогою спеціального значка, який повідомляє про помилку. Цей значок має вигляд знаку оклику, вписаного в піктограму у вигляді ромба. Він з'являється при виділенні осередків, які мають позначку в лівому верхньому кутку зеленим кольором, обговорювану нами раніше. Ця позначка ще не свідчить про те, що дані знаходяться в осередку обов'язково помилкові. Але цифри, розташовані в осередку має текстовий вигляд, викликають підозри у програми в тому, що дані можуть бути внесені некоректно. Тому про всяк випадок вона їх позначає, щоб користувач звернув увагу. Але, на жаль, такі позначки Ексель видає не завжди навіть тоді, коли цифри представлені в текстовому вигляді, тому нижче описаний спосіб підходить не для всіх випадків.

1. Виділяємо осередок, в якій міститься зелений індикатор про можливу помилку. Кількома по з'явилася піктограмі.

2. Відкривається список дій. Вибираємо в ньому значення « перетворити на число» .

3. У виділеному елементі дані тут же будуть перетворені в числовий вид.

Якщо подібних текстових значень, які слід перетворити, не одне, а безліч, то в цьому випадку можна прискорити процедуру перетворення.

1. Виділяємо весь діапазон, в якому знаходяться текстові дані. Як бачимо, піктограма з'явилася одна для всієї області, а не для кожного осередку окремо. Клацаємо по ній.

2. Відкривається вже знайомий нам список. Як і минулого разу, вибираємо позицію " перетворити на число» .

Усі дані масиву будуть перетворені у вказаний вигляд.

Спосіб 2: конвертація за допомогою вікна форматування

Як і для перетворення даних з числового виду в текст, в Ексель існує можливість зворотного конвертації через вікно форматування.

1. Виділяємо діапазон, що містить цифри в текстовому варіанті. Кількома правою кнопкою миші. У контекстному меню вибираємо позицію " Формат комірок...» .

2. Виконується запуск вікна форматування. Як і в попередній раз, переходимо у вкладку «Число» . У групі " числові формати» нам потрібно вибрати значення, які дозволять перетворити текст у число. До них відноситься пункти " загальний» і " числовий» . Який би з них ви не вибрали, програма буде розцінювати цифри, введені в осередок, як числа. Виробляємо вибір і тиснемо на кнопку. Якщо ви вибрали значення " числовий» , то в правій частині вікна з'явиться можливість відрегулювати уявлення числа: виставити кількість десяткових знаків після коми, встановити роздільниками між розрядами. Після того, як настройка виконана, тиснемо на кнопку «OK» .

3. Тепер, як і в разі перетворення числа в текст, нам потрібно проклацати всі осередки, встановивши в кожну з них курсор і натиснувши після цього клавішу Enter .

Після виконання цих дій всі значення обраного діапазону перетворюються в потрібний нам вид.

Спосіб 3: конвертація за допомогою інструментів на стрічці

Перевести текстові дані в Числові можна, скориставшись спеціальним полем на стрічці інструментів.

1. Виділяємо діапазон, який повинен піддатися трансформації. Переходимо у вкладку»Головна" на стрічці. Кількома по полю з вибором формату в групі «Число» . Вибираємо пункт " числовий» або " загальний» .

2. Далі прощелківаем вже не раз описаним нами способом кожну клітинку перетворюваної області із застосуванням клавіш F2 і Enter .

Значення в діапазоні будуть перетворені з текстових в Числові.

Спосіб 4: застосування формули

Також для перетворення текстових значень в Числові можна використовувати спеціальні формули. Розглянемо, як це зробити на практиці.

1. У порожній комірці, розташованої паралельно першому елементу діапазону, який слід перетворити, ставимо знак " дорівнює» (=) і подвійний символ»мінус" (&8212;) . Далі вказуємо адресу першого елемента трансформованого діапазону. Таким чином, відбувається подвійне множення на Значення «-1» . Як відомо, множення "мінус» на «мінус»дає "плюс". Тобто, в цільової осередку ми отримуємо те ж значення, яке було спочатку, але вже в числовому вигляді. Дана процедура називається подвійним бінарним запереченням.

2. Тиснемо на клавішу Enter , після чого отримуємо готове перетворене значення. Для того, щоб застосувати дану формулу для всіх інших осередків діапазону, використовуємо маркер заповнення, який раніше був застосований нами для функції ТЕКСТ .

3. Тепер ми маємо діапазон, який заповнений значеннями з формулами. Виділяємо його і тиснемо на кнопку «копіювати» у вкладці »Головна" або застосовуємо поєднання клавіш Ctrl+C .

4. Виділяємо вихідну область і виробляємо клацання по ній правою кнопкою миші. В активованому контекстному списку переходимо по пунктах «Спеціальна вставка» і " значення та формати чисел» .

5. Всі дані вставлені в потрібному нам вигляді. Тепер можна видалити транзитний діапазон, в якому знаходиться формула подвійного бінарного заперечення. Для цього виділяємо дану область, кліком правої кнопки миші викликаємо контекстне меню і вибираємо в ньому позицію " очистити вміст» .

До речі, для перетворення значень даним методом зовсім не обов'язково використовувати виключно подвійне множення на «-1» . Можна застосовувати будь-яке інше арифметичне дію, яке не веде до зміни значень (додавання або віднімання нуля, виконання зведення в першу ступінь і т. д.)

Урок: як зробити автозаповнення в Excel

Спосіб 5: застосування спеціальної вставки

Наступний спосіб за принципом дії дуже схожий на попередній з тією лише різницею, що для його використання не потрібно створювати додатковий стовпець.

1. В будь-яку порожню клітинку на аркуші вписуємо цифру «1» . Потім виділяємо її і тиснемо на знайомий значок " копіювати» на стрічці.

2. Виділяємо область на аркуші, яку слід перетворити. Кількома по ній правою кнопкою миші. У меню двічі переходимо по пункту " Спеціальна вставка» .

3. У вікні спеціальної вставки виставляємо перемикач в блоці «операція» в позицію " помножити» . Слідом за цим тиснемо на кнопку «OK» .

4. Після цієї дії всі значення виділеної області будуть перетворені в Числові. Тепер при бажанні можна видалити цифру «1» , яку ми використовували в цілях конвертації.

Спосіб 6: використання інструменту " Текст стовпцями»

Ще одним варіантом, при якому можна перетворити текст в числовий вид, є застосування інструменту «Текст стовпцями» . Його є сенс використовувати тоді, коли замість коми в якості роздільника десяткових знаків використовується крапка, а в якості роздільника розрядів замість пробілу – апостроф. Цей варіант сприймається в англомовному Екселі, як числовий, але в російськомовній версії цієї програми всі значення, які містять зазначені вище знаки, сприймаються як текст. Звичайно, можна перебити дані вручну, але якщо їх багато, це займе значну кількість часу, тим більше що існує можливість набагато більш швидкого вирішення проблеми.

1. Виділяємо фрагмент листа, вміст якого потрібно перетворити. Переходимо у вкладку»дані" . На стрічці інструментів в блоці " робота з даними» натискаємо по значку «Текст за стовпцями» .

2. Запускається майстер текстів . У першому вікні зверніть увагу, щоб перемикач формату даних стояв в позиції " з роздільниками» . За замовчуванням він повинен знаходитися в цій позиції, але перевірити стан буде не зайвим. Потім натискаємо на кнопку »далі" .

3. У другому вікні також залишаємо все без змін і тиснемо на кнопку «далі».

4. А ось після відкриття третього вікна майстри текстів потрібно натиснути на кнопку »Детальніше" .

5. Відкривається вікно додаткової настройки імпорту тексту. У полі " Розділювач цілої та дробової частини» встановлюємо точку, а в поле " роздільник розрядів» &8212; апостроф. Потім робимо одне клацання по кнопці «OK» .

6. Повернення до третього вікна майстри текстів і тиснемо на кнопку »Готово" .

7. Як бачимо, після виконання даних дій числа прийняли звичний для російськомовної версії формат, а це значить, що вони одночасно були перетворені з текстових даних в Числові.

Спосіб 7: застосування макросів

Якщо вам часто доводиться перетворювати великі області даних з текстового формату в числовий, то є сенс в цих цілях записати спеціальний макрос, який буде використовуватися при необхідності. Але для того, щоб це виконати, перш за все, потрібно в своїй версії екселя включити макроси і панель розробника, якщо це ще не зроблено.

1. Переходимо у вкладку»Розробник" . Тиснемо на значок на стрічці «Visual Basic» , який розміщений у групі «Код» .

2. Запускається стандартний редактор макросів. Вбиваємо або копіюємо в нього наступний вираз:

   
Sub Текст_в_число()
Selection.NumberFormat = "General"
Selection.Value = Selection.Value
End Sub


   Після цього закриваємо редактор, виконавши натискання стандартної кнопки закриття у верхньому правому куті вікна.


3. Виділяємо фрагмент на аркуші, який потрібно перетворити. Тиснемо на значок «макроси» , який розташований на вкладці »Розробник" в групі «Код» .

4. Відкривається вікно записаних у вашій версії Програми макросів. Знаходимо макрос з найменуванням " Текст_в_число» , виділяємо його і тиснемо на кнопку " виконати» .

5. Як бачимо, тут же відбувається перетворення текстового виразу в числовий формат.

Урок: як створити макрос в Екселі

Як бачимо, існує досить багато варіантів перетворення в Excel цифр, які записані в числовому варіанті, в текстовий формат і в зворотному напрямку. Вибір певного способу залежить від багатьох факторів. Перш за все, це поставлене завдання. Адже, наприклад, швидко перетворити текстовий вираз з іноземними роздільниками в Числове можна тільки використавши інструмент «Текст стовпцями» . Другий фактор, який впливає на вибір варіанту – це обсяги і частота виконуваних перетворень. Наприклад, якщо ви часто використовуєте подібні перетворення, має сенс зробити Запис макросу. І третій фактор-Індивідуальна зручність користувача.