При работе с MS Excel вы, возможно, не раз сталкивались с ошибкой при попытке произвести арифметические действия над числами. Программа почему-то не выполняет вычисления и выдает сообщение о том, что такую операцию следует проводить над числовыми элементами. Почему случаются такие проблемы и что с ними делать, читайте ниже.
Число в текстовом формате: причины и проблемы
Ошибка возникает в случае, если программа по каким-то причинам не воспринимает числа как числа, считая их текстом. Это может произойти при копировании данных из другого источника - другой программы, интернет-странички и т. д. Проблема также может появиться при импорте информации из другого файла или БД.
Обратите внимание на то, как выглядит поле с цифрой. Если в уголке поля вы видите маленький зеленый треугольник, это значит, что числовая информация в нем введена как текстовая. Описанная выше проблема при попытке произвести вычисления также говорит об этом.
Еще одна проблема при хранении цифр как строк возникает при попытке их сортировки. Возьмем для примера ряд чисел: 10, 211, 15, 1021, 26, 100, 128. Их сортировка не представляет трудности, и программа легко с ней справляется. Но, если эти числа записаны в строковом формате, сортировка будет кардинально отличаться от привычной и необходимой. Эта разница показана в таблице:
Что же делать в этом случае и как преобразовать текст в число в Excel?
Исправление ошибки
Как уже было указано выше, на проблему указывает зеленая отметка в уголке поля. Такая пометка может быть на единичных полях, целом диапазоне, столбце или записи. Программа самостоятельно определяет несоответствие между форматом ячейки и значением, которое в нее внесено, и сигнализирует нам об этом. Теперь нужно только верно использовать этот сигнал.
Выделите поле или ряд полей, в котором наблюдается проблемная ситуация. Несколько разрозненных ячеек можно выделить с помощью клавиши Ctrl, графу или запись выделяют, кликнув мышкой на заголовке. Чтобы выделить весь лист, нужно нажать на соединение заголовков записей и граф в верхнем левом углу страницы Excel.
Рядом с выделенным диапазоном полей появится значок - кнопка с восклицательным знаком, указывающий на наличие ошибки. При нажатии на кнопку появляется меню, первым пунктом которого показано, какая именно проблема в выделенном участке листа («Число сохранено как текст»). Нажмите пункт «Преобразовать в число». Зеленые индикаторы ошибки сразу исчезнут, и над числами можно будет проводить необходимые действия.
Использование специальной вставки
Еще один способ, помогающий преобразовать текст в число в Excel, - использование спецвставки. В одну из свободных ячеек внесите число 1, предварительно установив формат как общий: на вкладке «Главная» программы, панель инструментов «Число» показывает формат выделенного поля.
Скопируйте единицу. Для этого сделайте на ней правый клик и выберите команду «Копировать» из меню. Второй способ - кнопка на панели инструментов «Буфер обмена». И третий - горячие клавиши Ctrl+C.
Выделите диапазон чисел в текстовом формате, который собираетесь приводить к числовому. На той же панели «Буфер обмена» нажмите «Вставить» / «Специальная вставка». Откроется окно для ввода параметров. В нем отметьте точкой элемент «умножить» блока «Операция» и нажмите «ОК» - текст Excel преобразуется в число.
Ту же операцию можно произвести с помощью контекстного меню. Выделив нужный диапазон, кликните правой кнопкой мыши, выберите «Специальная вставка» / «Специальная вставка». Откроется то же окно.
Использование формул и макросов
Еще один способ преобразовать текст в число в Excel - формула. Вставьте рядом с проблемной ячейкой или графой пустой столбец. Введите в верхнее поле нового столбца формулу. Для этого напишите «=». Программа воспринимает этот знак как намерение добавления вычисления и предлагает выбрать функцию. Нажмите значок функции fx под панелью инструментов.
В открывшемся окне в строке поиска введите текст «значен» и найдите функцию «ЗНАЧЕН». В параметрах функции укажите нужную ячейку и нажмите «ОК». В соседней с искомой ячейкой вы получите число вместо текста. Чтобы скопировать формулу на все поля столбца, кликните мышкой в правом нижнем углу ячейки, содержащей ее, получив курсор в виде изображения крестика, зажмите его и проведите вниз по всей длине. Формула появится по всей графе.
Тем, кто знаком с программированием, поможет преобразовать текст в число Excel VBA. Этот встроенный язык предлагает команды трансформации типов данных. Для целых чисел подойдет операция CInt, для дробных - CDbl. Ниже показан пример использования VBA для нашего случая:
Sub TestModule()
Cells(1, 12) = CInt(Cells(1, 11))
Обратная задача
Иногда стоит обратная задача - преобразовать в Excel число в текст прописью. Эта проблема так же решаема, хоть и не так просто, как предыдущая. Существуют специальные надстройки, которые добавляются в программу. Затем для изменения отображения чисел вызываются функции.
Еще один способ - использование формул. Он значительно более громоздкий, так как подразумевает создание пользовательских справочников - именованных диапазонов в диспетчере имен. Затем эти имена используются в формуле на основе функции «ПОДСТАВИТЬ».
Какой бы способ вы ни выбрали, чтобы в Excel преобразовать текст в число, применив его на практике несколько раз, вы приспособитесь делать это быстро и эффективно, получая нужный результат.
Каждый пользователь, которому много приходится работать с документами, хотел бы упростить, автоматизировать свою работу хотя бы в какой-то степени . Специальные средства, реализованные разработчиками в редакторе таблиц Microsoft Excel, позволяют это сделать. В этой статье разберёмся как в Excel преобразовать число в текст. Давайте же начнём. Поехали!
Стоит отметить, что по умолчанию функция перевода числа в текст отсутствует в программе. Для того чтобы такая возможность появилась в Экселе, необходимо скачать и установить специальную надстройку «sumprop». После того как вы скачаете пакет, переместите файл «sumprop» в папку, путь к которой можно посмотреть следующим образом. Откройте меню «Файл» и нажмите «Параметры». В окне параметров Excel перейдите на вкладку «Надстройки». Внизу, в пункте «Управление» укажите «Надстройки Excel» и щёлкните по кнопке «Перейти». В открывшемся окне нажмите «Обзор». Далее, скопируйте путь к папке, в которую нужно будет переместить файл «sumprop». После этого запустите Эксель заново и ещё раз откройте окно надстроек (повторите вышеописанные действия). В разделе «Доступные надстройки» отметьте галочкой пункт «Сумма прописью». Нужная функция появится в программе.
Теперь рассмотрим, как этим пользоваться. В ячейку введите какое-нибудь число и нажмите на кнопку «Вставить функцию». В появившемся окне выберите «Определённые пользователем» в разделе «Категория». В списке ниже вы найдёте функции «ЧислоПрописью», «СуммаПрописью» и несколько её вариаций для валют (рубли, гривны, доллары, евро). Удобно использовать, если необходимо указать какую-либо денежную сумму словами, что очень часто требуется в различной документации.
Обратите внимание, что после того, как вы применили функцию к какой-либо ячейке, все числа, которые вы будете в неё вводить, сразу будут преобразованы в текст. Используйте «ЧислоПрописью» или «СуммаПрописью» в зависимости от того, какие задачи стоят перед вами сейчас.
Как видите, нет ничего сложного. Благодаря возможности установки пакетов расширений можно существенно увеличить функциональность программы. Научившись правильно использовать возможности Microsoft Excel , вы значительно повысите эффективность своей работы, сэкономив несколько часов кропотливого труда. Пишите в комментариях была ли статья полезной для вас и задавайте вопросы, которые могли у вас возникнуть в процессе.
Бывает что, когда пытаешься провести вычисления с данными, формулы не работают или Excel выдает ошибку. Причины сразу могут быть неясны. Особенно часто проблема встречается после выгрузки из учетных систем, таких как 1С или SAP. Как избежать проблемы, когда формулы не работают? Чаще всего необходимо преобразовать текст в число в Excel? Несколько полезных способов, как бороться с этим несчастьем читайте ниже.
Очень часто числовые данные выгруженные из разных программ или из-за неправильных действий пользователя Excel воспринимается как текст, т.е. формулы возможно будут считаться неверно. Есть много способов преобразовать текст в число!
С самого начала проверьте формат чисел. Выделите диапазон с данными (на нашем примере столбец B). Нажмите горячую клавишу (меню Формат ячеек если правой кнопкой мыши). На вкладке Число выберите Формат ячеек — Числовой.
Очень удобно пользоваться меню Число на главной панели. Здесь сразу доступны удобные форматы. Слева-направо: денежный, процентный, числовой + кнопки уменьшения/увеличения количества знаков после запятой. Рекомендую.
2. Исправьте ошибки — преобразуйте текст в число специальным инструментом
Первый шаг не помог? Не беда. Видите зеленые треугольники в верхних левых углах ячеек? Это значит что в ячейке что-то не так, Excel подсказывает Вам, что возможно там не число или формула отличается от соседних и т.п. Наведите на эту ячейку указатель, или выделите все эти ячейки и нажмите на появившийся слева значок.
В нашем случае Число сохранено как текст — кликните Преобразовать в число. Вуаля!
Это же доступно по адресу Формулы — Раздел Зависимости формул -
3. Умножьте все числа на 1
Не помог предыдущий вариант? Значит, ошибка довольно редкая, но решается просто. Есть крутая хитрость. В столбце С все значения столбца B перемножьте на 1. Да, буквально так, для ячейки C2 запишите =B2*1 и протяните вниз. Так Excel принудительно преобразовывает имеющиеся значения в число.
4. Удалите лишний символ в ячейках при помощи Найти и Заменить
И пункт 3 не помог? Значит в ячейках закрался какой-то символ, например как в ячейке В7 на первой картинке после первого числ стоит символ » «, это может быть даже не обычный пробел (поэтому скопируйте именно этот символ). Такими вещами очень часто грешит 1С, причем любой конфигурации.
Пользуемся инструментом Найти и заменить, подробное описание . Если быстро нажимаете , появиться окно.
В поле Найти: вставляем скопированный символ. В поле Заменить на: ничего не ставим (т.е. заменяем на пусто). Жмем Заменить все.
Должно помочь!
5. Функция
И конечно, есть универсальный способ применить функцию , она лечит все ошибки с числами, кроме лишних символов (не касается пробелов) и преобразовывает текст в число. Очень удобно использовать ее для проверок в расчетах, как подстраховку от ошибок.
6. Перевод времени в число
На самом деле время и дата это уже число, но в нужном формате. Но в их обработке может быть сразу несколько хитростей. Читаем в этой статье.
Кстати для того, чтобы в начале и конце ячеки с текстом (например при записи ФИО мы часто ошибаемся и ставим пробел в конце, который не видо). А так же, чтобы убрать двойные пробелы, есть классная функция . Она убирает все лишние пробелы!
Поделитесь нашей статьей в ваших соцсетях:(в дробной части числа). Например, 0,75 соответствует 18:00, 0,5 - 12:00. Если, по какой-то причине, значения ВРЕМЕНИ сохранены в десятичной форме, например, 10,5 часов, то их можно преобразовать в стандартный формат времени (часы: минуты: секунды). В статье также показано как преобразовать число 1800 в 18:00.
Если в ячейке содержится число 40638,5, то изменив соответствующим образом Формат ячеек можно его отобразить в виде 05.04.2011 12:00:00 (40638 соответствует дате 05.04.2011, а 0,5 соответствует 12 часам дня, подробнее в статье ). Для этого вызовите Диалоговое окно Формат ячеек, нажав CTRL+1 , выберите числовой формат Дата, затем выберите в поле Тип - формат 14.03.01 13:30. Также с помощью Формата ячеек число 40638,5 можно отобразить как 12:00:00.
Преобразование ВРЕМЕНИ из десятичного числа в стандартный формат времени (часы: минуты: секунды)
Пример1а
Если в ячейке A1 содержится значение времени (часов) в десятичной форме (10,5), то для отображения в стандартном формате времени используйте формулу =ТЕКСТ(A1/24; "ч:мм") Результат 10:30 в текстовом формате (функция =ТЕКСТ() возвращает текстовое значение). 0,5 означает долю часа , т.е. полчаса, т.е. 30 минут.
Если значение нужно в формате времени, то можно записать формулу =A1/24 . Формат ячейки, содержащий формулу, необходимо перевести в формат Время (см. файл примера ).
Другой вариант: =ВРЕМЯ(10;30;0) Аргументами функции ВРЕМЯ() могут быть только целые числа, поэтому 0,5 часа были заменены 30 минутами. Чтобы не переводить доли часов в минуты, можно написать формулу =ВРЕМЯ(A1;ОСТАТ(A1;1)*60;0) Результат 10:30 АМ.
Пример1б
Пусть в ячейке А1 находится 52,84. 52 - это часы, а 84 - доля часа. Результат нужно отобразить в виде 52 ч 50 мин (в текстовом формате). Используйте формулу =ЦЕЛОЕ(A1)&" ч "&ТЕКСТ(A1/24;"мм")&" мин" . Если нужен результат в 2-х ячейках (отдельно часы, отдельно минуты), то =ЦЕЛОЕ(A1) вернет часы, а =ЦЕЛОЕ(ОСТАТ(A1;1)*60) минуты.
В отличие от примера 1а, здесь количество часов превышает 24 (количество часов в сутках). Поэтому формула =ТЕКСТ(A1/24;"ч:мм") вернет 4:50, т.е. количество часов будет уменьшено на 48=2*24 (на максимальное количество часов кратных 24, которое меньше заданного значения часов). Следовательно, формат ВРЕМЯ использовать нельзя, т.к. он отображает количество часов от 0 до 23.
Пример2
Если целая часть числа содержит часы, а десятичная минуты, например, 10,75, то формула =ВРЕМЯ(ЦЕЛОЕ(A1);100*(A1-ЦЕЛОЕ(A1));0) переведет это число в формат времени 11:15. Этот подход отличается от рассмотренного выше Примера 1а (число 10,5), когда десятичная часть обозначала долю часа, а не минуты. Т.е. 0,5 в первом случае 30 минут, а во втором - 50 минут. Главное не запутаться. Лучше - не сохраняйте время в десятичном формате. Храните часы и минуты в разных ячейках, затем используйте функцию ВРЕМЯ() .
Пример3
Если число 145 означает 145 минут, то перевести его в формат ВРЕМЯ можно формулой =ВРЕМЯ(0;145;0) . Результат 2:25 АМ (утра), т.к. 2 (часа)*60 минут + 25 минут = 145 минут.
Преобразование 1800 в 18:00
4. Прямое преобразование в формат времени
В формате Дата
принято, что одним суткам соответствует число единица. Соответственно, конкретное время, это часть суток: двенадцать дня это 0,5; шесть вечера это 0,75. Число равное 1/24 (т.е. 0,041667) соответствует одному часу. Учитывая, что в сутках 24 часа, в часе 60 минут, а в сутках 1440 минут, напишем следующую формулу: =ЦЕЛОЕ(A1/100)/24+ОСТАТ(A1;100)/1440
. Далее изменяем формат ячейки на Время
.
Хочу начать эту статью короткой историей из своего опыта. Было время, когда приходилось трудиться куратором договоров и при составлении этих самых договоров набирать на клавиатуре сумму договора прописью.
И поскольку реестр договоров велся в Excel, то возник соблазн воспользоваться какой-нибудь формулой, которая бы перевела мне число, набранное цифрами, в число написанное прописью, причем по-русски. Перебрав все стандартные формулы в Excel, и найдя в категории «текстовые» функцию БАТТЕКСТ с описанием «преобразует число в текст», признаюсь, обрадовался. Разочарование наступило очень быстро, когда число, преобразованное в текст прописью оказалось на непонятном мне языке.
Как преобразовать число в текст прописью?
После этого была создана надстройка, в которой были объединены все наиболее востребованные инструменты для преобразования чисел в текст прописью на русском языке. Кроме людей, работающих с договорами, эта надстройка может стать незаменимым инструментом в повседневной работе бухгалтеров, менеджеров, экономистов, финансистов и вообще всех пользователей, кто использует в своей деятельности Microsoft Office Excel для работы с договорами, платежками, приходниками, расходниками, счетами-фактурами и прочими документами, формируемыми в Excel.
Надстройка, предназначенная для расширения возможностей Excel, позволяет использовать для преобразования чисел в текст функций, определенных пользователем, аналогично тому, как используются стандартные функции в Excel. Кроме этого запуск надстройки вызывает диалоговое окно, в котором можно выбирать по своему усмотрению различные параметры, такие как область действия, выбор валюты для формирования суммы прописью, выбор строчной либо заглавной буквы, с которой будет начинаться сумма прописью и выбор координат для места вставки суммы прописью относительно числа, написанного цифрами.
Надстройка позволяет:
2. преобразовать любое число от 0 до 999 999 999 999 в прописной текст;
3. преобразовать все числа в выбранном Вами диапазоне;
4. использовать при написании чисел прописью одну из валют на выбор: рубли, доллары, евро, тенге, гривны ;
5. выбрать строчную либо заглавную букву в каждом первом слове суммы прописью;
6. выбрать любую позицию для вставки суммы прописью, относительно суммы цифрами;
7. использовать функции, определенные пользователем, аналогично стандартным формулам, которые позволят трансформировать число и сумму в текст прописью с использованием любой из представленных валют.
Как преобразовать текст, написанный прописью в число цифрами?
Возможна и обратная процедура, позволяющая трансформировать количественные числительные, суммы, написанные прописью в числа, написанные цифрами. Для этого создана надстройка, аналогичная той, которая описана выше.
Надстройка позволяет:
1. Одним кликом мыши вызвать диалоговое окно макроса прямо с панели инструментов Excel;
2. преобразовать любую сумму, написанную прописью в число от 0 до 999 999 999, 99;
3. найти и преобразовать все суммы прописью в выбранном Вами диапазоне;
4. находить и преобразовывать суммы, написанные прописью с использованием любых валют: рублей, гривен, долларов, евро и др.;
5. выбрать любую позицию для вставки числа относительно ячейки с суммой прописью;
6. использовать пользовательскую функцию, аналогично стандартной формуле, которая позволит трансформировать сумму прописью в число.