Содержание:
Если для каких-либо ячеек листа был установлен текстовый формат (это могло быть сделано пользователем или программой при загрузке данных в Excel), то числа, введенные позже в эти ячейки, Excel начинает рассматривать как текст. Иногда такие ячейки отмечаются зеленым индикатором, который вы наверняка видели:
А иногда такой индикатор не появляется (что гораздо хуже).
В общем, появление чисел в виде текста в ваших данных обычно приводит к множеству весьма неприятных последствий:
- сортировка перестает работать нормально — «псевдоцифры» выдавливаются и располагаются не в том порядке, как положено:
- функции типа ВПР (ВПР) не находите нужные значения, так как для них число и одно и то же число в виде текста различны:
- при фильтрации ошибочно выбираются псевдономера
- многие другие функции Excel также перестают работать должным образом:
- и так далее
Особенно забавно, что естественное желание просто сменить формат ячейки на числовой не помогает. Те. вы буквально выбираете ячейки, щелкаете по ним правой кнопкой мыши, выбираете Формат ячейки (Формат ячеек), измените формат на численный (количество), сжимать OK — и ничего не происходит! Совсем!
Возможно, «это не баг, а фича», конечно, но нам от этого легче не становится. Итак, давайте рассмотрим несколько способов исправить ситуацию – один из них вам обязательно поможет.
Способ 1. Уголок зеленого индикатора
Если вы видите зеленый угол индикатора на ячейке с числом в текстовом формате, то считайте, что вам повезло. Вы можете просто выделить все ячейки с данными и нажать на всплывающий желтый значок с восклицательным знаком, а затем выбрать команду Преобразовать в число (Преобразовать в число):
Все числа в выбранном диапазоне будут преобразованы в полные числа.
Если зеленых углов вообще нет, то проверьте, отключены ли они в настройках Excel (Файл – Параметры – Формулы – Числа в текстовом формате или с апострофом.).
Способ 2: Повторный вход
Если ячеек немного, то можно изменить их формат на числовой, а затем повторно ввести данные, чтобы изменение формата вступило в силу. Проще всего это сделать, встав на ячейку и последовательно нажимая клавиши. F2 (войдите в режим редактирования, ячейка начнет мигать курсором), а затем Enter. Также вместо F2 вы можете просто дважды щелкнуть по ячейке левой кнопкой мыши.
Само собой, если клеток много, то этот метод, конечно, не подойдет.
Способ 3. Формула
Быстро преобразовать псевдочисла в обычные можно, если рядом с данными сделать дополнительный столбец с элементарной формулой:
Двойной минус в данном случае означает, по сути, умножение на -1 дважды. Минус на минус даст плюс и значение в ячейке не изменится, но сам факт выполнения математической операции переключает формат данных на нужный нам числовой.
Конечно, вместо умножения на 1 можно использовать любую другую безобидную математическую операцию: деление на 1 или сложение и вычитание нуля. Эффект будет тот же.
Метод 4: Специальная вставка
Этот метод использовался в старых версиях Excel, когда современные эффективные менеджеры ушли под стол зеленого угла индикатора еще не было в принципе (он появился только в 2003 году). Алгоритм такой:
- введите 1 в любую пустую ячейку
- скопировать его
- выделите ячейки с числами в текстовом формате и измените их формат на числовой (ничего не произойдет)
- щелкните правой кнопкой мыши по ячейкам с псевдоцифрами и выберите команду Специальная вставка (Специальная вставка) или используйте сочетание клавиш Ctrl + Alt + V
- в открывшемся окне выберите опцию Ценности (Ценности) и Размножаться (Умножить)
По сути, мы делаем то же самое, что и в предыдущем методе — умножаем содержимое ячеек на единицу — но не формулами, а прямо из буфера.
Способ 5. Текст по столбцам
Если преобразуемые псевдочисла также записаны с неправильными десятичными или тысячными разделителями, можно использовать другой подход. Выберите исходный диапазон с данными и нажмите кнопку Текст по столбцам (Текст в столбцы) таб Данные (Дата). На самом деле этот инструмент предназначен для разделения липкого текста на столбцы, но в данном случае мы используем его для другой цели.
Пропустите первые два шага, нажав на кнопку Следующая (Следующий), а на третьем воспользуйтесь кнопкой Дополнительно (Advanced). Откроется диалоговое окно, где вы можете установить символы-разделители, доступные на данный момент в нашем тексте:
После нажатия на Завершить Excel преобразует наш текст в обычные числа.
Способ 6. Макрос
Если вам приходится часто делать подобные преобразования, то имеет смысл автоматизировать этот процесс с помощью простого макроса. Нажмите Alt+F11 или откройте вкладку. разработчик (Разработчик) И нажмите Визуальный Бейсик. В появившемся окне редактора добавьте новый модуль через меню Вставка – Модуль и скопируйте туда следующий код:
Sub Convert_Text_to_Numbers() Selection.NumberFormat = «Общий» Selection.Value = Selection.Value End Sub
Теперь после выбора диапазона вы всегда можете открыть вкладку Разработчик – Макросы (Разработчик — Макросы), выбираем в списке наш макрос, нажимаем кнопку Run (Бежать) — и мгновенно конвертировать псевдоцифры в полноценные.
Вы также можете добавить этот макрос в свою личную книгу макросов для последующего использования в любом файле.
PS
Та же история происходит и с датами. Некоторые даты также могут распознаваться Excel как текст, поэтому группировка и сортировка не будут работать. Решения те же, что и для чисел, только формат необходимо заменить на дату-время вместо числового.
- Разделение липкого текста на столбцы
- Расчеты без формул специальной вклейкой
- Преобразуйте текст в числа с помощью дополнения PLEX