Как в экселе поменять знаки на цифры
Обновлено: 05.07.2024
При введите номер, содержащий более 15 цифр в ячейке в Microsoft Excel, Excel любые цифры за пятнадцатым местом на нули. Например, введите номер удостоверения кредитной карты в следующем формате:
В этой ситуации Excel последнюю цифру на ноль.
Причина
Excel спецификации IEEE 754 для хранения и вычисления номеров с плавающей точкой. Excel сохраняет только 15 значимых цифр в номере и изменяет цифры после пятнадцатого места в ноль
Обходной путь
Добавление кавычка
Чтобы не изменить цифры на ноль, введите одну кавычка перед вводом номера.
Для этого выберите пустую ячейку, введите одну кавычка ('), а затем введите номер. Все цифры отображаются в ячейке.
Формат ячеек
Чтобы не вводить кавычка в каждой затронутой ячейке, перед вводом каких-либо данных можно форматирование ячеек в виде текста.
Выберите все затронутые ячейки и нажмите кнопку Ctrl+1, чтобы открыть диалоговое окно Format Cells.
На вкладке Номер выберите Текст из списка Категорий и выберите ОК.
Дополнительные сведения
Такое поведение происходит только в том случае, если ячейка отформатирована как Номер, а число, в которое вошел, превышает 15 цифр. Для ячеек, форматированные в виде текста, можно ввести до 32 767 символов. Excel на таблице отображается до 1024 символов.
Так как настраиваемые форматы номеров предназначены для работы в основном с числами, невозможно создать настраиваемый формат номеров, который хранит более 15 цифр. Например, нельзя использовать следующий формат для хранения 16-символьного удостоверения кредитной карты в виде номера:
Однако, если ввести номер в ячейке, которая форматирована как текст, все символы остаются как вы введите их, так как Excel сохраняет номер в виде текста, а не как номер.
Многие пользователи программы Эксель привыкли к тому, что в качестве названий столбцов таблицы служат латинские буквы. Однако иногда может случиться такое, что вместо букв отображаются цифры подобно нумерации строк.
Такое возможно по ряду причин:
- программные сбои;
- пользователь сам изменил соответствующую настройку, но не заметил, как это сделал или забыл.
- возможно, другой пользователь, работающий с таблицей внес изменения в настройки.
На самом деле, независимо от того, какая именно причина привела к смене обозначений, многие пользователи спешат вернуть все на место, т.е. чтобы столбцы вновь обозначались буквами латинского алфавита. Давайте посмотрим, как это сделать в Excel.
Смена обозначений столбцов
В Эксель предусмотрено два метода, пользуясь которыми, можно вернуть горизонтальную координатную панель к тому виду, к которому привыкло большинство пользователей программы. Давайте рассмотрим оба.
Метод 1: настройка параметров программы
Данный метод предполагает внесение изменений в параметры программы. Вот, что нужно сделать:
Примечание: стиль ссылок R1C1 – это и есть параметр, включение которого меняет латинские буквы на цифры на горизонтальной координатной панели.
Метод 2: настройки в Режиме разработчика
Прежде, чем приступить к выполнению действий, описанных в данном методе, необходимо активировать режим Разработчика, так как по умолчанию он выключен.
Теперь можно перейти к основному алгоритму действий:
Заключение
Многих пользователей, которые никогда с этим не сталкивались, может смутить ситуация, когда вместо привычных названий столбцов в виде латинских букв отображаются цифры. Но не стоит из-за этого переживать, ведь вернуть панель координат к привычному виду можно достаточно быстро, воспользовавшись одним из способов, описанных выше.
Поиск и замена данных – одна из часто применяемых операций в Excel. Используют даже новички. На ленте есть большая кнопка.
Команда поиска придумана для автоматического обнаружения ячеек, содержащих искомую комбинацию символов. Поиск данных может производиться в определенном диапазоне, целом листе или даже во всей книге. Если активна только одна ячейка, то по умолчанию поиск происходит на всем листе. Если требуется осуществить поиск значения в диапазоне ячеек Excel, то такой диапазон нужно предварительно выделить.
Далее вызываем Главная → Редактирование → Найти и выделить → Найти (кнопка с рисунка выше). Поиск также можно включить с клавиатуры комбинацией клавиш Сtrl+F. Откроется диалоговое окно под названием Найти и заменить.
В единственном поле указывается информация (комбинация символов), которую требуется найти. Если не использовать подстановочные символы или т.н. джокеры (см. ниже), то Excel будет искать строгое совпадение заданных символов. Для вывода результатов поиска предлагается два варианта: выводить все результаты сразу – кнопка Найти все; либо выводить по одному найденному значению – кнопка Найти далее.
После запуска поиска программа Excel быстро-быстро просматривает содержимое листа (или указанного диапазона) на предмет наличия искомой комбинации символов. Если такая комбинация обнаружена, то в случае нажатия кнопки Найти все Excel вываливает все найденные ячейки.
Если в нижней части окна выделить любое значение и затем нажать Ctrl+A, то в диапазоне поиска будут выделены все соответствующие ячейки.
Если же запуск поиска произведен кнопкой Найти далее, то Excel выделяет ближайшую ячейку, соответствующую поисковому запросу. При повторном нажатии клавиши Найти далее (либо Enter с клавиатуры) выделяется следующая ближайшая ячейка (подходящая под параметры поиска) и т.д. После выделения последней ячейки Excel перепрыгивает на самую верхнюю и начинается все заново. На этом познания о поиске данных в Excel у большинства пользователей заканчиваются.
Поиск нестрогого соответствия символов
Иногда пользователь не знает точного сочетания искомых символов что существенно затрудняет поиск. Данные также могут содержать различные опечатки, лишние пробелы, сокращения и пр., что еще больше вносит путаницы и делает поиск практически невозможным. А может случиться и обратная ситуация: заданной комбинации соответствует слишком много ячеек и цель поиска снова не достигается (кому нужны 100500+ найденных ячеек?).
Для решения этих проблем очень хорошо подходят джокеры (подстановочные символы), которые сообщают Excel о сомнительных местах. Под джокерами могут скрываться различные символы, и Excel видит лишь их относительное расположение в поисковой фразе. Таких джокеров два: звездочка «*» (любое количество неизвестных символов) и вопросительный знак «?» (один «?» – один неизвестный символ).
Есть еще один джокер – знак «?». Под ним может скрываться только один неизвестный символ. К примеру, указав для поиска критерий 1?6, Excel найдет все ячейки содержащие последовательность 106, 116, 126, 136 и т.д. А если указать 1??6, то будут найдены ячейки, содержащие 1006, 1016, 1106, 1236, 1486 и т.д. Таким образом, джокер «?» накладывает более жесткие ограничения на поиск, который учитывает количество пропущенных знаков (равный количеству проставленных вопросиков «?»).
В случае неудачи можно попробовать изменить поисковую фразу, поменяв местами известные символы, сократив их, добавить новые подстановочные знаки и др. Однако это еще не все нюансы поиска. Бывают ситуации, когда в упор наблюдаешь искомую ячейку, но поиск почему-то ее не находит.
Продвинутый поиск
Мало, кто обращается к кнопке Параметры в диалоговом окне Найти и заменить. А зря. В ней скрыто много полезностей, которые помогают решить проблемы поиска. После нажатия кнопки Параметры добавляются дополнительные поля, которые еще больше углубляют и расширяют условия поиска.
С помощью дополнительных параметров поиск в Excel может заиграть новыми красками в прямом смысле слова. Так, искать можно не только заданное число или текст, но и формат ячейки (залитые определенным цветом, имеющие заданные границы и т.д.).
После нажатия кнопки Формат выскакивает знакомое диалоговое окно формата ячеек, только в этот раз мы не создаем, а ищем нужный формат. Формат также можно не задавать вручную, а выбрать из имеющегося, воспользовавшись специальной командой Выбрать формат из ячейки:
Таким образом можно отыскать, к примеру, все объединенные ячейки, что другим способом сделать весьма проблематично.
Поиск формата – это хорошо, но чаще искать приходится конкретные значения. И тут Excel предоставляет дополнительные возможности для расширения и уточнения параметров поиска.
Первый выпадающий список Искать предлагает ограничить поиск одним листом или расширить его до целой книги.
По умолчанию (если не лезть в параметры) поиск происходит только на активном листе. Для повторения поиска на другом листе все действия нужно проделать еще раз. А если таких листов много, то поиск данных может отнять немало времени. Однако если выбрать пункт Книга, то поиск произойдет сразу по всем листам активной книги. Выгода очевидна.
Список Просматривать с выпадающими вариантами по строкам или столбцам, видимо, сохранился от старых версий, когда поиск требовал много ресурсов и времени. Сейчас это не актуально. В общем, я не пользуюсь.
В диалоговом окне поиска есть еще две галочки Учитывать регистр и Ячейка целиком. По умолчанию Excel игнорирует регистр, но можно сделать так, чтобы «иванов» и «Иванов» отличались. Галочка Ячейка целиком также может оказаться весьма полезной, если ищется ячейка не с указанным фрагментом, а полностью состоящая из искомых символов. К примеру, как найти ячейки, содержащие только 0? Обычный поиск не подойдет, т.к. будут выдаваться и 10, и 100. Зато, если установить галочку Ячейка целиком, то все пойдет, как по маслу.
Поиск и замена данных
Данные обычно ищутся не просто так, а для каких-то целей. Такой целью часто является замена искомой комбинации (или формата) на другую. Чтобы найти и заменить в выделенном диапазоне Excel одни значения на другие, в окне Найти и заменить необходимо выбрать вкладку Замена. Либо сразу выбрать на ленте команду Главная → Редактирование → Найти и выделить → Заменить.
Еще удобнее применить сочетание горячих клавиш найти и заменить в Excel – Ctrl+H.
Диалоговое окно увеличится на одно поле, в котором указываются новые символы, которые будут вставлены вместо найденных.
По аналогии с простым поиском, менять можно и формат.
Кнопка Заменить все позволяет одним махом заменить одни символы на другие. После замены Excel показывается информационное окно с количеством произведенных замен. Кнопка Заменить позволяет производить замену по одной ячейке после каждого нажатия. Если найти и заменить в Excel не работает, попробуйте изменить параметры поиска.
Жмем Ok. Все переносы строк заменились пробелами.
Функция поиска и замены при правильном использовании заменяет часы работы неопытного пользователя. Настоятельно рекомендую использовать все вышеизложенное. Если что-то не ищется в ваших данных или наоборот, выдает слишком много лишних ячеек, то попробуйте уточнить поиск с помощью подстановочных символов «*» и «?» или настраиваемых параметров поиска. Важно понимать, что если вы ничего не нашли, это еще не значит, что там этого нет.
Теперь вы знаете, как в эксель сделать поиск по столбцу, строке, любому диапазону, листу или даже книге.
Если для каких-либо ячеек на листе был установлен текстовый формат (это мог сделать пользователь или программа при выгрузке данных в Excel), то введенные потом в эти ячейки числа Excel начинает считать текстом. Иногда такие ячейки помечаются зеленым индикатором, который вы, скорее всего, видели:
Причем иногда такой индикатор не появляется (что гораздо хуже).
В общем и целом, появление в ваших данных чисел-как-текст обычно приводит к большому количеству весьма печальных последствий:
- перестает нормально работать сортировка - "псевдочисла" выдавливаются вниз, а не располагаются по-порядку как положено:
- функции типа ВПР (VLOOKUP) не находят требуемые значения, потому как для них число и такое же число-как-текст различаются:
- при фильтрации псевдочисла отбираются ошибочно
- многие другие функции Excel также перестают нормально работать:
- и т.д.
Особенно забавно, что естественное желание просто изменить формат ячейки на числовой - не помогает. Т.е. вы, буквально, выделяете ячейки, щелкаете по ним правой кнопкой мыши, выбираете Формат ячеек (Format Cells) , меняете формат на Числовой (Number) , жмете ОК - и ничего не происходит! Совсем!
Способ 1. Зеленый уголок-индикатор
Если на ячейке с числом с текстовом формате вы видите зеленый уголок-индикатор, то считайте, что вам повезло. Можно просто выделить все ячейки с данными и нажать на всплывающий желтый значок с восклицательным знаком, а затем выбрать команду Преобразовать в число (Convert to number) :
Все числа в выделенном диапазоне будут преобразованы в полноценные.
Если зеленых уголков нет совсем, то проверьте - не выключены ли они в настройках вашего Excel (Файл - Параметры - Формулы - Числа, отформатированные как текст или с предшествующим апострофом).
Способ 2. Повторный ввод
Если ячеек немного, то можно поменять их формат на числовой, а затем повторно ввести данные, чтобы изменение формата вступило-таки в силу. Проще всего это сделать, встав на ячейку и нажав последовательно клавиши F2 (вход в режим редактирования, в ячейке начинает мигаеть курсор) и затем Enter. Также вместо F2 можно просто делать двойной щелчок левой кнопкой мыши по ячейке.
Само-собой, что если ячеек много, то такой способ, конечно, не подойдет.
Способ 3. Формула
Можно быстро преобразовать псевдочисла в нормальные, если сделать рядом с данными дополнительный столбец с элементарной формулой:
Двойной минус, в данном случае, означает, на самом деле, умножение на -1 два раза. Минус на минус даст плюс и значение в ячейке это не изменит, но сам факт выполнения математической операции переключает формат данных на нужный нам числовой.
Само-собой, вместо умножения на 1 можно использовать любую другую безобидную математическую операцию: деление на 1 или прибавление-вычитание нуля. Эффект будет тот же.
Способ 4. Специальная вставка
Этот способ использовали еще в старых версиях Excel, когда современные эффективные менеджеры под стол ходили зеленого уголка-индикатора еще не было в принципе (он появился только с 2003 года). Алгоритм такой:
- в любую пустую ячейку введите 1
- скопируйте ее
- выделите ячейки с числами в текстовом формате и поменяйте у них формат на числовой (ничего не произойдет)
- щелкните по ячейкам с псевдочислами правой кнопкой мыши и выберите команду Специальная вставка (Paste Special) или используйте сочетание клавиш Ctrl+Alt+V
- в открывшемся окне выберите вариант Значения (Values) и Умножить (Multiply)
По-сути, мы выполняем то же самое, что и в прошлом способе - умножение содержимого ячеек на единицу - но не формулами, а напрямую из буфера.
Способ 5. Текст по столбцам
Если псеводчисла, которые надо преобразовать, вдобавок еще и записаны с неправильными разделителями целой и дробной части или тысяч, то можно использовать другой подход. Выделите исходный диапазон с данными и нажмите кнопку Текст по столбцам (Text to columns) на вкладке Данные (Data) . На самом деле этот инструмент предназначен для деления слипшегося текста по столбцам, но, в данном случае, мы используем его с другой целью.
Пропустите первых два шага нажатием на кнопку Далее (Next) , а на третьем воспользуйтесь кнопкой Дополнительно (Advanced) . Откроется диалоговое окно, где можно задать имеющиеся сейчас в нашем тексте символы-разделители:
После нажатия на Готово Excel преобразует наш текст в нормальные числа.
Способ 6. Макрос
Если подобные преобразования вам приходится делать часто, то имеет смысл автоматизировать этот процесс при помощи несложного макроса. Нажмите сочетание клавиш Alt+F11 или откройте вкладку Разработчик (Developer) и нажмите кнопку Visual Basic. В появившемся окне редактора добавьте новый модуль через меню Insert - Module и скопируйте туда следующий код:
Теперь после выделения диапазона всегда можно открыть вкладку Разрабочик - Макросы (Developer - Macros) , выбрать наш макрос в списке, нажать кнопку Выполнить (Run ) - и моментально преобразовать псевдочисла в полноценные.
Также можно добавить этот макрос в личную книгу макросов, чтобы использовать позднее в любом файле.
С датами бывает та же история. Некоторые даты тоже могут распознаваться Excel'ем как текст, поэтому не будет работать группировка и сортировка. Решения - те же самые, что и для чисел, только формат вместо числового нужно заменить на дату-время.
Читайте также: