Текст в цифри и обратно в Microsoft Excel

Една от честите задачи, с които се сблъскват потребителите на програмата Excel, е да конвертира числови изрази в текстов формат и обратно. Този въпрос често ви принуждава да отделите много време за решаване, ако потребителят не знае ясен алгоритъм за действия. Нека видим как можете да разрешите и двата проблема по различни начини.

Преобразувайте число в текст

Всички клетки в Excel имат определен формат, който показва на програмата как да третира този или този израз. Например, дори ако те съдържат номера, но форматът е текстов, приложението ще ги третира като обикновен текст и няма да може да извършва математически изчисления с такива данни. За да може Excel да третира номера точно като числа, те трябва да бъдат вписани в листов елемент с общ или цифров формат.

Първо, нека разгледаме различните варианти за решаване на проблема с преобразуването на цифри в текстова форма.

Метод 1: Форматирайте чрез контекстното меню

Най-често потребителите извършват форматирането на цифрови изрази в текст чрез контекстното меню.

  1. Изберете елементите на листа, в които искате да конвертирате данните в текст. Както можете да видите, в раздела "Начало" на лентата с инструменти в блока "Номер" в специалното поле се показва информация, че тези елементи имат общ формат, което означава, че цифрите, вписани в тях, се възприемат от програмата като число.
  2. Общ формат в Microsoft Excel

  3. Кликнете върху десния бутон на мишката върху селекцията и в менюто, което се показва, изберете елемента "Форматиране на клетки ..." .
  4. Отворете прозореца за форматиране в Microsoft Excel

  5. В отворения прозорец на формата отидете в раздела "Номер" , ако е отворен другаде. В блока за настройки "Цифрови формати" изберете елемента "Текст" . За да запазите промените, кликнете върху бутона " OK" в долната част на прозореца.
  6. Прозорец за форматиране в Microsoft Excel

  7. Както можете да видите, след тези манипулации в специално поле се показва информация, че клетките са преобразувани в текстова форма.
  8. Клетките се преобразуват в текстов формат в Microsoft Excel

  9. Но ако се опитаме да изчислим сумата, тя ще бъде показана в клетката по-долу. Това означава, че трансформацията не е пълна. Това е един от чиповете на Excel. Програмата не ви позволява да завършите обмена на данни по най-интуитивния начин.
  10. Автоматично сумиране в Microsoft Excel

  11. За да завършите реализация, трябва да кликнете два пъти върху левия бутон на мишката, за да поставите курсора във всеки елемент от обхвата поотделно и да натиснете клавиша Enter . За да опростите задачата, можете да използвате функционалния клавиш F2 вместо да кликнете два пъти.
  12. Конвертирайте номера в текст в Microsoft Excel

  13. След като тази процедура се изпълнява с всички клетки в областта, данните в тях ще се възприемат от програмата като текстови изрази и следователно автоматично сумата ще бъде нула. Освен това, както можете да видите, горният ляв ъгъл на клетките ще бъде оцветен в зелено. Това също е индиректен знак, че елементите, в които се намират числата, са преобразувани в текстова версия на дисплея. Въпреки че тази функция не винаги е задължителна и в някои случаи липсва такава марка.

Автоматичната сума е 0 в Microsoft Excel

Урокът: Как да промените формата в Excel

Метод 2: инструменти на лентата

Можете също да преобразувате число в текстова форма, като използвате инструментите на лентата, по-специално, като използвате полето, за да покажете формата, разгледан по-горе.

  1. Изберете елементите, чиито данни искате да конвертирате в текст. В раздела "Начало" кликнете върху иконата на триъгълника вдясно от полето, в което се показва форматът. Той се намира в полето "Номер" .
  2. Превключете към форматиране на касета в Microsoft Excel

  3. В отворения списък с опции за форматиране изберете елемента "Текст" .
  4. Изберете текстов формат в Microsoft Excel

  5. След това, както при предишния метод, последователно задайте курсора на всеки елемент от диапазона, като щракнете двукратно върху левия бутон на мишката или натиснете клавиша F2 и след това кликнете върху клавиша Enter .

Данните се преобразуват в текстова версия.

Метод 3: Използвайте функцията

Друга възможност за преобразуване на цифрови данни за тестване в Excel е използването на специална функция, наречена - TEXT . Този метод е подходящ, преди всичко, ако искате да прехвърлите номера като текст в отделна колона. Освен това, ако времето за преобразуване е твърде голямо, то ще спести време. В края на краищата, вие ще се съгласите, че perekchelkivat всяка клетка в диапазон, който включва стотици или хиляди линии - това не е най-доброто решение.

  1. Задайте курсора на първия елемент от диапазона, в който ще се покаже резултатът от реализацията. Кликваме върху иконата "Вмъкване на функция" , която се намира близо до формулата.
  2. Отидете в съветника за функции в Microsoft Excel

  3. Прозорецът Магьосници се отваря. В категорията "Текст" изберете елемента "ТЕКСТ" . След това кликнете върху бутона "OK" .
  4. Отворете аргументите на функцията TEXT в Microsoft Excel

  5. Отваря прозореца за аргументи на оператор TEXT . Тази функция има следния синтаксис:

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

    Отвореният прозорец има две полета, които съответстват на тези аргументи: "Стойност" и "Формат" .

    В полето "Стойност" трябва да посочите номера , който трябва да се преобразува, или да се посочи клетката, в която се намира. В нашия случай това ще бъде препратка към първия елемент на обработения цифров диапазон.

    В полето "Формат" трябва да посочите опцията за показване на резултата. Например, ако въведете "0" , текстовата версия на изхода ще се покаже без десетични знаци, дори ако са в източника. Ако въведете "0,0" , резултатът ще се покаже с един десетичен знак, ако "0,00" , след това с две и т.н.

    След като въведете всички необходими параметри, кликнете върху бутона "OK" .

  6. Аргументи на функцията TEXT в Microsoft Excel

  7. Както можете да видите, стойността на първия елемент от зададения диапазон бе показана в клетката, която идентифицирахме в първия параграф на това ръководство. За да прехвърлите други стойности, трябва да копирате формулата в съседни елементи на листа. Задайте курсора в долния десен ъгъл на елемента, който съдържа формулата. Курсорът се преобразува в маркер за попълване, който прилича на малък кръст. Задръжте левия бутон на мишката и плъзнете празните клетки успоредно на обхвата, в който се намират оригиналните данни.
  8. Филтър в Microsoft Excel

  9. Сега цялата серия е пълна с необходимите данни. Но това не е всичко. Всъщност всички елементи на новия диапазон съдържат формули. Изберете тази област и кликнете върху иконата "Копиране" , която се намира в раздела "Начало" на лентата с инструменти в групата "Клипборд" .
  10. Копиране в Microsoft Excel

  11. Освен това, ако искаме да запазим и двата диапазона (източник и преобразуван), не премахвайте избора от областта, която съдържа формулите. Кликнете върху него с десния бутон на мишката. Стартира се списъкът с контекстни действия. Избираме позицията "Специално вмъкване" в нея . Сред опциите за действия в отворения списък изберете "Стойности и формати на номерата" .

    Специална вложка в Microsoft Excel

    Ако потребителят желае да замени данните в оригиналния формат, то вместо посоченото действие е необходимо да го изберете и да го поставите по същия начин както по-горе.

  12. Специално вмъкване в оригиналния диапазон в Microsoft Excel

  13. Във всеки случай избраният обхват ще бъде въведен в текстова форма. Ако все пак сте избрали да вмъкнете в областта на източника, тогава клетките, съдържащи формулите, могат да бъдат изчистени. За да направите това, изберете ги, щракнете с десния бутон на мишката и изберете елемента "Изчистване на съдържание" .

Изчистване на съдържание в Microsoft Excel

Това завършва процедурата за преобразуване.

Урокът: Съветник за функции в Excel

Конвертиране на текст на номер

Сега нека разберем как можете да изпълнявате обратния проблем, а именно как да конвертирате текст в номер в Excel.

Метод 1: Преобразувайте с помощта на иконата за грешка

По-лесно и по-бързо е да конвертирате текстова версия, като използвате специална икона, която показва грешка. Тази икона има формата на удивителен знак в иконата под формата на диамант. Той се появява, когато изберете клетки, отбелязани със зелено в горния ляв ъгъл, което обсъждахме по-рано. Този знак все още не показва, че данните в клетката задължително са погрешни. Но номерата, разположени в клетка, която има текстови вид, предизвикват подозрение в програмата, че данните могат да бъдат въведени неправилно. Така че, само в случай, тя ги маркира, така че потребителят обръща внимание. Но, за съжаление, Excel не винаги дава такива означения дори когато цифрите са представени в текстова форма, така че описаният по-долу метод не е подходящ за всички случаи.

  1. Изберете клетка, която съдържа зелена светлина, показваща възможна грешка. Кликнете върху изобразената пиктограма.
  2. Икона на грешка в Microsoft Excel

  3. Извежда се списък с действия. Изберете стойността " Convert to number" .
  4. Преобразувайте до номер в Microsoft Excel

  5. В избрания елемент данните ще бъдат преобразувани незабавно в цифров вид.

Стойността в клетката се преобразува в число в Microsoft Excel

Ако има повече от една такава текстова стойност за преобразуване и много, тогава в този случай можете да ускорите процедурата за конвертиране.

  1. Изберете целия диапазон, в който се намират текстовите данни. Както можете да видите, пиктограмата се появи една за цялата област, а не за всяка клетка поотделно. Кликваме върху него.
  2. Икона за диапазона в Microsoft Excel

  3. Списъкът, който вече ни е познат, се отваря. Също като последния път, изберете позицията "Преобразуване в номер" .

конвертиране в диапазон в Microsoft Excel

Всички данни на масива ще бъдат преобразувани в посочения изглед.

Преобразуването в номер се извършва в Microsoft Excel

Метод 2: Преобразуване чрез използване на прозореца за форматиране

Както при конвертирането на данни от цифров изглед в текст в Excel, можете да преобразувате обратно през прозореца за форматиране.

  1. Изберете диапазона, съдържащ номерата в текстовата версия. Щракнете с десния бутон на мишката. В контекстното меню изберете елемента "Форматиране на клетки ..." .
  2. Отворете прозореца за форматиране в Microsoft Excel

  3. Прозорецът за форматиране е стартиран. Както и в предходното време, отворете раздела "Брой" . В групата "Цифрови формати" трябва да изберете стойности, които ще ни позволят да превърнем текста в число. Те включват елементите "Общи" и "Числени" . Който и да изберете, програмата ще третира номерата, въведени в клетката като числа. Направете избор и кликнете върху бутона. Ако сте избрали стойността "Numerical" , в дясната част на прозореца ще ви позволи да настроите цифровото представяне: задайте броя десетични знаци след десетичната запетая, задайте сепараторите между цифрите. След като конфигурацията е завършена, кликнете върху бутона "OK" .
  4. Прозорецът за форматиране в Microsoft Excel

  5. Сега, както при преобразуването на число в текст, трябва да кликнете върху всички клетки, като зададете курсора на всеки от тях и след това натиснете клавиша Enter .

Кликване върху клетки в Microsoft Excel

След извършване на тези действия, всички стойности на избрания диапазон се преобразуват в желания формат.

Метод 3: Преобразуване с инструменти на лентата

Преобразувайте текстовите данни в цифрови, като използвате специално поле върху лентата с инструменти.

  1. Изберете обхвата, който ще се трансформира. Отворете раздела Начална страница на лентата. Кликваме върху полето с избор на формат в групата "Брой" . Изберете елемента "Цифров" или "Общи" .
  2. Форматиране на текстов формат в цифров формат чрез панделка в Microsoft Excel

  3. След това кликваме върху всяка клетка от областта, която ще се преобразува повече от веднъж, използвайки клавишите F2 и Enter .

Кликвания за преобразуване в цифров формат в Microsoft Excel

Стойностите в диапазона ще бъдат преобразувани от текст в цифров.

Метод 4: прилагане на формулата

Можете също така да използвате специални формули за преобразуване на текстови стойности в цифрови стойности. Нека да разгледаме как да го направим на практика.

  1. В празна клетка, разположена успоредно на първия елемент от диапазона, който трябва да се преобразува, поставете знака за равенство (=) и двойния знак минус (-) . След това посочваме адреса на първия елемент от трансформирания диапазон. Така се получава двойно умножение по стойност "-1" . Както знаете, умножаването на "минус" с "минус" дава "плюс". Това означава, че в целевата клетка получаваме същата стойност, която беше първоначално, но вече в цифрова форма. Тази процедура се нарича двойно двоично отрицание.
  2. Формула в Microsoft Excel

  3. Натиснете клавиша Enter , след което получим завършената стойност. За да приложите тази формула към всички останали клетки в диапазона, използвайте маркера за запълване, който преди това приложихме към функцията TEXT .
  4. Филтър за двойна формула за двойно отрицание в Microsoft Excel

  5. Сега имаме диапазон, който е изпълнен с стойности с формули. Изберете го и кликнете върху бутона "Копиране" в раздела "Начало" или използвайте клавишната комбинация Ctrl + C.
  6. Копиране на числени стойности в Microsoft Excel

  7. Изберете областта на източника и кликнете върху него с десния бутон на мишката. В активирания контекстен списък отидете на елементите "Специално вмъкване" и "Стойности и формати на номерата" .
  8. Използване на специална вложка в Microsoft Excel

  9. Всички данни се вмъкват във формата, от която се нуждаем. Сега можете да премахнете диапазона на транзита, в който се намира формулата за двойно двоично отрицание. За да направите това, изберете тази област, щракнете с десния бутон върху контекстното меню и изберете елемента "Изчистване на съдържанието" в него.

Изчистване на съдържанието на диапазона на транзита в Microsoft Excel

Между другото, за трансформацията на стойностите по този метод не е необходимо да се използва само двойно умножение по "-1" . Можете да използвате всяко друго аритметично действие, което не води до промяна в стойностите (добавяне или изваждане на нула, ефективност на покачването до първото захранване и т.н.)

Урокът: Как да направите автоматично довършване в Excel

Метод 5: използване на специална вложка

Следващият метод е много подобен на предишния, с единствената разлика, че не е необходимо да създавате допълнителна графа, за да го използвате.

  1. Във всяка празна клетка на листа въведете числото "1" . След това го изберете и кликнете върху познатата икона "Копирай" на лентата.
  2. Копиране на цифра 1 в Microsoft Excel

  3. Изберете областта на листа, която ще се преобразува. Кликнете върху него с десния бутон на мишката. В менюто, което се отваря, кликнете два пъти върху елемента "Специално поставяне" .
  4. Преминете към специална вложка в Microsoft Excel

  5. В специалния прозорец на вложката поставете превключвателя в блока "Работа" в позиция "Умножение" . След това кликнете върху бутона "OK" .
  6. Специална вложка в Microsoft Excel

  7. След това действие всички стойности на избраната област ще бъдат преобразувани в цифрови стойности. Сега, ако искате, можете да изтриете номера "1" , който използвахме за целите на конверсията.

Обхватът се преобразува в число, като се използва специална вложка в Microsoft Excel

Метод 6: Използвайте инструмента "Колонен текст"

Друга възможност, при която можете да конвертирате текст в цифров вид, е използването на инструмента "Текстови колони" . Има смисъл да се използва, когато вместо запетая се използва период като десетичен сепаратор и апостроф се използва като пространствен сепаратор вместо пространство. Тази опция се възприема в английския език Excel като цифров, но в руската версия на тази програма всички стойности, които съдържат горните знаци, се третират като текст. Разбира се, можете да прекъсвате данните ръчно, но ако има много от тях, това ще отнеме значително време, особено след като има възможност за много по-бързо разрешаване на проблема.

  1. Изберете фрагмента от листа, чието съдържание искате да конвертирате. Отворете раздела "Данни" . На лентата с инструменти в блока "Работа с данни" кликнете върху иконата "Текст по колони" .
  2. Преминете към текстовия инструмент по колони в Microsoft Excel

  3. Ще започне съветникът за текст . В първия прозорец отбележете, че превключвателят за формат на данни е в позиция "Delimited" . По подразбиране той трябва да е в тази позиция, но няма да е излишно да се проверява състоянието. След това кликнете върху бутона "Напред" .
  4. Първият прозорец на съветника за текст в Microsoft Excel

  5. Във втория прозорец оставете всичко останало и кликнете върху бутона "Напред".
  6. Вторият прозорец на съветника за текст в Microsoft Excel

  7. Но след отварянето на третия прозорец на съветника за текст, трябва да кликнете върху бутона "Още подробности" .
  8. Третият прозорец на съветника за текст в Microsoft Excel

  9. Показва се допълнителният прозорец за импортиране на текст. В полето "Цифров и частичен сепаратор" задайте точката, а в "Ограничител на разделителя" - апостроф. Затем делаем один щелчок по кнопке «OK» .
  10. Дополнительная настройка импорта текста в Microsoft Excel

  11. Возвращаемся в третье окно Мастера текстов и жмем на кнопку «Готово» .
  12. Завершение работы в Мастере текста в Microsoft Excel

  13. Как видим, после выполнения данных действий числа приняли привычный для русскоязычной версии формат, а это значит, что они одновременно были преобразованы из текстовых данных в числовые.

Разделители приняли обычный формат в Microsoft Excel

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

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

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

  3. Запускается стандартный редактор макросов. Вбиваем или копируем в него следующее выражение:


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

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

  4. Редактор макросов в Microsoft Excel

  5. Выделяем фрагмент на листе, который нужно преобразовать. Жмем на значок «Макросы» , который расположен на вкладке «Разработчик» в группе «Код» .
  6. Переход к списку макросов в Microsoft Excel

  7. Открывается окно записанных в вашей версии программы макросов. Находим макрос с наименованием «Текст_в_число» , выделяем его и жмем на кнопку «Выполнить» .
  8. Окно макросов в Microsoft Excel

  9. Как видим, тут же происходит преобразование текстового выражения в числовой формат.

Преобразование текста в число с помощью макросов выполнено в Microsoft Excel

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

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