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, в строку «Найти» вставьте ранее выделенные пробелы, строку «Заменить на» оставьте пустой.

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

28 комментариев для “Excel. Преобразование как бы чисел”

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

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

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

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

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

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

  5. Григорий

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

    1. Анастасия

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

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

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

  7. PHP программист

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

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

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

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

  10. Вячеслав

    у меня другая проблема..

    Есть числа с пробелами. Например «9400 1096 9993 9894 7443 70». При попытке убрать пробелы с помощью клавиш «Ctrl + H, в строку «Найти» вставьте ранее выделенные пробелы, строку «Заменить на» оставьте пустой», в итоге получается ерунда. Набор цифр с пробелами превращается в «9,40011E+21».
    Уже пробовал ДО удаления пробелов разные форматы ставить — и числовой, и текстовый,и общий.. Ничего не получается.

    Подскажите пожалуйста — как в итоге получить исходный набор цифр, но без пробелов?

    1. Вячеслав, дело в том, что Excel обеспечивает обработку чисел с точностью только до 15 знаков (см., например, справку Excel, раздел Технические характеристики и ограничения вычислений). Чтобы преодолеть это ограничение, задайте текстовый формат. В качестве первого символа в ячейке поставьте апостроф — ‘. Excel будет интерпретировать данные в ячейке, как текст, а не как число. При этом сам апостроф не выводится (подробнее см. Excel. Работа с номерами кредитных карт).

      1. Вячеслав

        Спасибо) Попробовал, работает. А может быть знаете способ, как в существующие в таблице строки, за один раз можно в каждой ячейке добавить "'" перед цифрами?

      2. Вячеслав

        Попробовал по формуле ="'"&D13
        Апостроф добавляется везде, но он видим в ячейках. И в последующем через "Ctrl + H" и "пробел" не получается отформатировать.. Иксель пишет, что подлежащие замене данные не найдены.

Добавить комментарий

Ваш адрес email не будет опубликован.