Excel. Преобразование как бы чисел

Рубрика: 7. Полезняшки Excel

Когда я работал в издательстве (сначала верстальщиком, а затем начальником производственного отдела) моим основным рабочим инструментом была программа вёрстки PageMaker от фирмы Adobe (ныне на смену ему пришел InDesign). Когда я освоил все нюансы работы в PageMaker`е, я стал задумываться о том, что еще можно сделать для сокращения рутинных операций, увеличения доли творчества в работе верстальщика. И я открыл для себя язык Script`ов, поддерживаемый PageMaker`ом. Было это в далеком 1995-м году. Описания языка скриптов нигде не было, но я был настойчив, и нашел вариант – заказал англоязычную книгу у дистрибутора Adobe в России. Через несколько месяцев книга была у меня! Счастью не было предела! Сначала я самостоятельно освоил самые простые скрипты, которые позволяли ускорить работу и внедрить стандарты обработки текста разными верстальщиками. А затем придумал процедуру автоматической верстки текстовых объявлений.

С тех пор прошло много времени и уже давно моим основным инструментом в работе является Excel. Привычка же повышать производительность собственного труда и моих коллег / подчиненных осталась. Так что уже много лет я совершенствуюсь в применении Excel`я. Тем, кто также как и я хочет больше узнать об этой поистине безграничной по своим возможностям программе, могу рекомендовать книгу Холи Д., Холи Р. Excel 2007. Трюки. СПб: Издательство Питер. 2008 г., 368 с.

Скачать заметку в формате Word, примеры в формате Excel

Сегодня об одном из трюков, изложенных в книге, а также о моих методах борьбы с как бы числами… 🙂

Нередко (особенно при импорте из внешних источников) данные выглядят как числа, но числами не являются. Их нельзя суммировать, и вообще с точки зрения Excel это текст. В Excel`е визуально отличить текст от чисел довольно просто. Сделайте колонку достаточно широкой. Числа, по умолчанию, форматируются по правому краю ячейки, текст – по левому (для лучшего понимания откройте файл Excel, лист «Пример1»):

Видно, что числа в колонке «прилипли» к левому краю, то есть Excel их не воспринимает, как числа.

Выделите пустую ячейку, скопируйте её в буфер, выделите диапазон, который вы хотите превратить из как бы чисел в настоящие числа, выберите «Специальная вставка» (на ленте или с помощью правой кнопки мыши), в открывшемся окне выберите «значения» и «сложить», нажмите Ok:

Как бы числа преобразовались в числа:

Эффект связан с тем, что когда мы специальной вставкой прибавляем значение пустой ячейки, Excel прибавляет ноль и при этом превращает как бы число в число.

Иногда это не работает – см. в файле Excel лист «Пример2». Обратите внимание на рисунке ниже: в строке формул между числом 88 и курсором есть пробелы.

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

Выделите диапазон, в котором вы хотите избавиться от пробелов. Откройте диалоговое окно «Найти и заменить», например, с помощью «горячих» клавиш Ctrl + H, в строку «Найти» вставьте ранее выделенные пробелы, строку «Заменить на» оставьте пустой.

Нажмите «Заменить все». Вуаля (готово) 🙂

Комментарии: 18 комментариев

Большое спасибо, преобразование путём «вставить как значения И СЛОЖИТЬ» — не знал, очень помогло! 🙂

А еще можно просто умножить на 1)) поставить рядом 1 , скопировать ее, затем выделить столбец с данными — правой кнопкой мыши в менюшке выбрать «умножить». Тоже станут числами 🙂

Анна, спасибо за еще один вариант решения проблемы. Подтверждаете истину, что в Excel (как, впрочем, и в менеджменте :)) к решению ведет несколько путей

Привет! А зелёные треугольнички в левом верхнем углу ячейки ни на какие мысли не наталкивают?

Стандартное средство Екселя проверки — ни перемножать не надо, ни суммировать.

Щёлкаем по ячейке (или выделяем диапазон) — появляется ромбик с !. Щёлкаем на ромбик — выбираем преобразовать в число.
Всё!

Большое спасибо! Помог вариант с пробелом. В замене обычный пробел не срабатывал, а скопировать его из строки формул сама не догадалась 🙂

Добрый день, всем!
Ни один из вариантов: сложение, умножение, зеленый треугольничек мне не помогает. Моя проблема в Excele следующая: функция СЛЧИС() выдает результат «как бы число» с выравниванием по левому краю с десятичной запятой, а не десятичной точкой. В результате другая функция, например, такая ЕСЛИ(;;) неправильно работает. Как преобразовать число с десятичной запятой в число с десятичной точкой? Кто может помогите.

Григорий, вам должна помочь также функция замены (см. пример с пробелом): выделяете диапазон с неправильными числами, открываете окно поиска/замены (Ctrl+F), на вкладке «Заменить» в поле «Найти» вставляете запятую, в поле «Заменить на» — точку.

СПАСИБО!!!
Вы мне очень помогли!!!

Не помог ни один из вышеперечисленных вариантов. О замене пробела через Ctrl+F сам догадался. Программа пробелов просто не видит. Сложение (как и умножение) пустой ячейки (как и 1) также не дало ничего. Умножал, вычитал и добавлял 1 — безуспешно. Зеленых треугольничков нет.
Единственный видимый способ — вручную удалить пробел, что разумеется не возможно при обилии данных. Что делать?

Артем, скорее всего у Вас не обычный пробел, а что-то похожее на пробел 🙂 Поэтому программа и не видит пробел. Выделите в строке формул «лишнее» содержимое, и именно его вставьте в окно Найти и заменить.

Как имитировать зелёный треугольник в ячейках одного из столбцов, файла, который создается путём генерации из скриптового языка, например PHP?

Спасибо, особенно за удаление пробелов из чисел!!

Также легко преобразовать как-бы даты в даты. Просто выделить весь диапазон где есть даты и через CTRL+H заменить точку на точку. Как-бы даты станут датами и к ним теперь можно будет применять все функции дат.

СПАСИБО!!!
Очень помог метод-по другому никак не получалось

Что-то подобное:
=1*ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);»,»;».»;1)
СЖПРОБЕЛЫ() для удаления лишних пробелов,
ПОДСТАВИТЬ() для замены точек/запятых
1*» или «0+» преобразует в число

Выдаст #ЗНАЧ! если в ячейке был не пробел, а перенос строки (Alt+Enter)

Спасибо!

Автор спасибо за статью, поборол свою проблему!!!

СПАСИБО


Прокомментировать