Microsoft Excel

Достаточно часто в Excel выгружаются данные из сторонних программ для их дальнейшей обработки, и нередко дальнейшее использование этих данных в формулах даёт непредсказуемый результат: числа не суммируются, между датами невозможно рассчитать количество дней и т.д.

В этой статье рассматриваются причины появления таких проблем и различные способы их устранения

Причина первая. Число сохранено как текст

В этом случае видно, что числа или даты прижаты к левому краю ячейки (как текст) и, как правило, в левом верхнем углу ячейки имеется маркер ошибки (зелёный треугольник) и тег, который при наведении мышки поясняет, что число сохранено как текст.

Никакие изменения формата на Числовой, Общий или Дата ситуацию не исправляют, но если кликнуть в строку формул (или нажать F2), а затем Enter, то число становится числом, а дата датой. При большом количестве таких чисел вариант, согласитесь, неприемлемый.

Есть несколько способов решения данной проблемы

·             С помощью маркера ошибки и тега. Если в левом верхнем углу ячеек виден маркер ошибки (зелёный треугольник) и тег, то выделяем ячейки, кликаем мышкой по тегу и выбираем вариант Преобразовать в число

·             С помощью операции Найти/Заменить. Предположим, в таблице есть числа с десятичной запятой, сохраненные как текст. Выделяем диапазон с числами -- нажимаем Ctrl+h (либо находим на вкладке Главная или в меню Правка для версий до 2007 команду Заменить) -- в поле Найти вводим , (запятую) -- в поле Заменить на тоже вводим , (запятую) -- Заменить все. Таким образом, делая замену запятой на запятую, мы имитируем редактирование ячейки аналогично F2 -- Enter

​

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

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

Аналогичную замену можно проделать и формулой (см. ниже), используя функцию ПОДСТАВИТЬ()

·             С помощью Специальной вставки. Этот способ более универсальный, так как работает и с дробными числами, и с целыми, а также с датами. Выделяем любую пустую ячейку -- выполняем команду Копировать -- выделяем диапазон с проблемными числами -- Специальная вставка -- Сложить -- ОК. Таким образом, мы к числам (или датам) прибавляем 0, что никак не влияет на их значение, зато переводит в числовой формат

Вариантом этого приёма может быть умножение диапазона на 1

·             С помощью инструмента Текст по столбцам. Этот приём удобно использовать если преобразовать нужно один столбец, так как если столбцов несколько, то действия придётся повторять для каждого столбца отдельно. Итак, выделяем столбец с числами или датами, сохраненными как текст, устанавливаем формат ячейки Общий (для чисел можно установить, к примеру, Числовой или Финансовый). Далее выполняем команду Данные -- Текст по столбцам -- Готово

·             С помощью формул. Если таблица позволяет задействовать дополнительные столбцы, то для преобразования в число можно использовать формулы. Чтобы перевести текстовое значение в число, можно использовать двойной минус, сложение с нулём, умножение на единицу, функции ЗНАЧЕН(), ПОДСТАВИТЬ(). Более подробно можно почитать здесь. После преобразования полученный столбец можно скопировать и вставить как значения на место исходных данных

·             С помощью макросов. Собственно, любой из перечисленных способов можно выполнить макросом. Если Вам приходится часто выполнять подобное преобразование, то имеет смысл написать макрос и запускать его по мере необходимости.

Приведу два примера макросов:

1) умножение на 1

view plainprint?

2) текст по столбцам

view plainprint?

Причина вторая. В записи числа присутствуют посторонние символы.

Чаще всего этими посторонними символами являются пробелы. Они могут располагаться как внутри числа в качестве разделителя разрядов, так и до/после числа. В этом случае, естественно, число становится текстом.

Убрать лишние пробелы также можно с помощью операции Найти/Заменить. В поле Найти вводим пробел, а поле Заменить на оставляем пустым, далее Заменить все. Если в числе были обычные пробелы, то этих действий будет достаточно. Но в числе могут встречаться так называемые неразрывные пробелы (символ с кодом 160). Такой пробел придётся скопировать прямо из ячейки, а затем вставить в поле Найти диалогового окна Найти/Заменить. Либо можно в поле Найти нажать сочетание клавиш Alt+0160 (цифры набираются на цифровой клавиатуре). 

Пробелы можно удалить и формулой. Варианты:

Для обычных пробелов: =--ПОДСТАВИТЬ(B4;" ";"")

Для неразрывных пробелов: =--ПОДСТАВИТЬ(B4;СИМВОЛ(160);"")

Сразу для тех и других пробелов: =--ПОДСТАВИТЬ(ПОДСТАВИТЬ(B4;СИМВОЛ(160);"");" ";"")

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