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

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

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

Всі осередки в Ексель мають певний формат, який задає програмі, як їй розглядати ту чи іншу вираз. Наприклад, навіть якщо в них будуть записані цифри, але формат виставлений текстовий, то додаток буде розглядати їх, як простий текст, і не зможе проводити з такими даними математичні обчислення. Для того, щоб 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. У порожній клітинці, розташованій паралельно першому елементу діапазону, який потрібно перетворити, ставимо знак «дорівнює» (=) і подвійний символ «мінус» (-). Далі вказуємо адресу першого елемента трансформованого діапазону. Таким чином, відбувається подвійне множення на значення «-1». Як відомо, множення «мінус» на «мінус» дає «плюс». Тобто, в цільовій комірці ми отримуємо те ж значення, яке було спочатку, але вже в числовому вигляді. Даная процедура називається подвійним бінарним запереченням.
2. Тиснемо на клавішу Enter, після чого отримуємо готове перетворене значення. Для того, щоб застосувати цю формулу для всіх інших осередків діапазону, використовуємо маркер заповнення, який раніше був застосований нами для функції ТЕКСТ.
3. Тепер ми маємо діапазон, який заповнений значеннями з формулами. Виділяємо його і тиснемо на кнопку «Копіювати» у вкладці «Головна» або застосовуємо поєднання клавіш Ctrl + C.
4. Виділяємо вихідну область і виробляємо клацання по ній правою кнопкою миші. В активувати контекстному списку переходимо по пунктам «Спеціальна вставка» і «Значення і формати чисел».
5. Всі дані вставлені в потрібному нам вигляді. Тепер можна видалити транзитний діапазон, в якому знаходиться формула подвійного бінарного заперечення. Для цього виділяємо цю область, кліком правої кнопки миші викликаємо контекстне меню і вибираємо в ньому позицію «Очистити вміст».

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

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

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

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

1. У будь-яку вільну позицію на аркуші вписуємо цифру «1». Потім виділяємо її і тиснемо на знайомий значок «Копіювати» на стрічці.
2. Виділяємо область на аркуші, яку слід перетворити. Кількома по ній правою кнопкою миші. У меню, двічі переходимо по пункту «Спеціальна вставка».
3. У вікні спеціальної вставки виставляємо перемикач в блоці «Операція» в позицію «Помножити». Слідом за цим тиснемо на кнопку «OK».
4. Після цього дії все значення виділеної області будуть перетворені в числові. Тепер при бажанні можна видалити цифру «1», яку ми використовували в цілях конвертації.

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

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

1. Виділяємо фрагмент листа, вміст якого потрібно перетворити. Переходимо у вкладку «Дані». На стрічці інструментів в блоці «Робота з даними» натискаємо по значку «Текст за стовпцями».
2. Запускається Майстер текстів. У першому вікні зверніть увагу, щоб перемикач формату даних стояв в позиції «З роздільниками». За замовчуванням він повинен знаходитися в цій позиції, але перевірити стан буде не зайвим. Потім натискаємо на кнопку «Далі».
3. У другому вікні також залишаємо все без змін і тиснемо на кнопку «Далі».
4. А ось після відкриття третього вікна Майстра текстів потрібно натиснути на кнопку «Детальніше».
5. Відкривається вікно додаткового налаштування імпорту тексту. В поле «Роздільник цілої та дробової частини» встановлюємо точку, а в полі «Разделитель розрядів» - апостроф. Затем делаем один щелчок по кнопке «OK» .
6. Возвращаемся в третье окно Мастера текстов и жмем на кнопку «Готово» .
7. Как видим, после выполнения данных действий числа приняли привычный для русскоязычной версии формат, а это значит, что они одновременно были преобразованы из текстовых данных в числовые.

Способ 7: применение макросов

Если вам часто приходится преобразовывать большие области данных из текстового формата в числовой, то имеется смысл в этих целях записать специальный макрос, который будет использоваться при необходимости. Но для того, чтобы это выполнить, прежде всего, нужно в своей версии Экселя включить макросы и панель разработчика, если это до сих пор не сделано.

1. Переходим во вкладку «Разработчик» . Жмем на значок на ленте «Visual Basic» , который размещен в группе «Код» .
2. Запускается стандартный редактор макросов. Вбиваем или копируем в него следующее выражение:

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


   После этого закрываем редактор, выполнив нажатие стандартной кнопки закрытия в верхнем правом углу окна.

3. Выделяем фрагмент на листе, который нужно преобразовать. Жмем на значок «Макросы» , который расположен на вкладке «Разработчик» в группе «Код» .
4. Открывается окно записанных в вашей версии программы макросов. Находим макрос с наименованием «Текст_в_число» , выделяем его и жмем на кнопку «Выполнить» .
5. Как видим, тут же происходит преобразование текстового выражения в числовой формат.

урок: Как создать макрос в Экселе

Как видим, существует довольно много вариантов преобразования в Excel цифр, которые записаны в числовом варианте, в текстовый формат и в обратном направлении. Выбор определенного способа зависит от многих факторов. Прежде всего, это поставленная задача. Ведь, например, быстро преобразовать текстовое выражение с иностранными разделителями в числовое можно только использовав инструмент «Текст столбцами» . Второй фактор, который влияет на выбор варианта – это объемы и частота выполняемых преобразований. Например, если вы часто используете подобные преобразования, имеет смысл произвести запись макроса. И третий фактор – индивидуальное удобство пользователя.

• ← Зміна найменування стовпців з числового на буквене
• Транспонування таблиці в Microsoft Excel →