Как в эксель убрать числовой формат
Общий формат — это числовой формат по умолчанию, Excel применяется при его введите. В большинстве случае числа, отформатированные в формате Общий, отображаются так, как вы их введите. Однако если ширины ячейки недостаточно для демонстрации всего числа, то формат Общий округляет числа, которые имеют десятичность. Для чисел, содержащих более 12 знаков, в формате Общий используется научное (экспоненциальное) представление.
Выберите ячейку или диапазон ячеек, которые нужно переформаировать на числовом формате по умолчанию.
Выбор ячейки или диапазона
Чтобы выделить
Выполните следующие действия
Щелкните ячейку или воспользуйтесь клавишами со стрелками, чтобы перейти к нужной ячейке.
Щелкните первую ячейку диапазона, а затем перетащите указатель мыши на его последнюю ячейку. Или удерживая нажатой клавишу SHIFT, нажимайте клавиши со стрелками, чтобы расширить выделение.
Кроме того, можно выделить первую ячейку диапазона, а затем нажать клавишу F8 для расширения выделения с помощью клавиш со стрелками. Чтобы остановить расширение выделенной области, еще раз нажмите клавишу F8.
Большой диапазон ячеек
Щелкните первую ячейку диапазона, а затем, удерживая нажатой клавишу SHIFT, щелкните последнюю ячейку диапазона. Чтобы перейти к последней ячейке, можно использовать полосу прокрутки.
Все ячейки листа
Чтобы выделить весь лист, также можно нажать клавиши CTRL+A.
Примечание: Если лист содержит данные, при нажатии клавиш CTRL+A выделяется текущий диапазон. Повторное нажатие клавиш CTRL+A приведет к выделению всего листа.
Несмежные ячейки или диапазоны ячеек
Выделите первую ячейку или диапазон ячеек, а затем, удерживая нажатой клавишу CTRL, выделите другие ячейки или диапазоны.
Можно также выбрать первую ячейку или диапазон ячеек, а затем нажать клавиши SHIFT+F8 для включения в выделение других несмежных ячеек или диапазонов. Чтобы остановить включение ячеек и диапазонов, снова нажмите клавиши SHIFT+F8.
Примечание: Отменить выделение отдельных несмежных ячеек или диапазонов без отмены всего выделения невозможно.
Столбец или строку целиком
Щелкните заголовок сроки или столбца.
1. Заголовок строки
2. Заголовок столбца
Чтобы выбрать ячейки в строке или столбце, вы можете выбрать первую ячейку и нажать клавиши CTRL+SHIFT+СТРЕЛКА (стрелка вправо или СТРЕЛКА ВЛЕВО — для строк, СТРЕЛКА ВВЕРХ или СТРЕЛКА ВНИЗ — для столбцов).
Примечание: Если в строке или столбце содержатся данные, при нажатии сочетания CTRL+SHIFT+клавиша со стрелкой будет выделена строка или столбец до последней заполненной ячейки. Повторное нажатие этого сочетания приведет к выделению строки или столбца полностью.
Смежные строки или столбцы
Протащите указатель мыши по заголовкам строк или столбцов. Либо выделите первую строку или первый столбец, а затем, удерживая нажатой клавишу SHIFT, выделите последнюю строку или последний столбец.
Несмежные строки или столбцы
Щелкните заголовок первой строки или столбца выделения, а затем, удерживая нажатой клавишу CTRL, щелкните заголовки столбцов или строк, которые требуется добавить в выделение.
Первую или последнюю ячейку в строке или столбце
Выберите ячейку в строке или столбце и нажмите клавиши CTRL+СТРЕЛКА (стрелка вправо или СТРЕЛКА ВЛЕВО для строк, СТРЕЛКА ВВЕРХ или СТРЕЛКА ВНИЗ — для столбцов).
Первую или последнюю ячейку на листе или в таблице Microsoft Office Excel
Чтобы выделить первую ячейку на листе или в списке Excel, нажмите сочетание клавиш CTRL+HOME.
Чтобы выделить последнюю ячейку, содержащую данные или форматирование, на листе или в списке Excel, нажмите сочетание клавиш CTRL+END.
Ячейки до последней используемой ячейки листа (нижний правый угол)
Выделите первую ячейку, а затем нажмите клавиши CTRL+SHIFT+END, чтобы расширить выделенную область до последней используемой ячейки листа (нижний правый угол).
Ячейки до начала листа
Выделите первую ячейку и нажмите сочетание клавиш CTRL+SHIFT+HOME, чтобы расширить выделение до начала листа.
Больше или меньше ячеек, чем имеется в активном выделении
Удерживая нажатой клавишу SHIFT, щелкните последнюю ячейку, которую нужно включить в новую выделенную область. В эту область войдет прямоугольный диапазон между активная ячейка и выделенной ячейкой.
Совет: Чтобы отменить выделение ячеек, щелкните любую ячейку на листе.
На вкладке Главная нажмите кнопку запуска диалогового окна рядом с полем Число.
Microsoft Excel предварительно программируется, чтобы упростить ввод дат. Например, 12/2 изменяется на 2-дек. Это очень неприятно, если вы вводите что-то, что не хотите менять на дату. К сожалению, отключить эту возможность не получится. Однако существует несколько способов обойти это.
Предформатация ячеек, в которые нужно ввести числа, в виде текста. В этом случае Excel не будет пытаться изменить то, что вы вводите в даты.
Если нужно ввести всего несколько чисел, можно сделать так, чтобы Excel не превнося их в даты, введите:
Пробел перед вводом числа. После нажатия ввод пробел останется в ячейке. (См. заметки)
Апостроф (') перед вводом числа, например '11-53 или '1/47. Апостроф не отображается в ячейке после нажатия ввода.
Нуль и пробел перед вводом дроби, например 1/2 или 3/4, чтобы они не менялись, например, на 2-янв или 4-мар. Введите 0 0 1/2 или 0 3/4. Ноль не остается в ячейке после нажатия ввод, и ячейка становится типом дробного числа.
Выйдите из ячеек, в которые нужно ввести числа.
Нажмите клавиши CTRL+1 (1 в строке чисел над клавишами QWERTY), чтобы открыть формат ячеек.
Выберите тексти нажмите кнопку ОК.
Выберите ячейки, в которые необходимо ввести числа.
Щелкните Главная > Числовой формат > Текст.
Рекомендуется использовать апостроф вместо пробела для ввода данных, если вы планируете использовать функции подменю для данных. Такие функции, как ПОИСКПОЗ и ВПР, не учитывают апострофы при вычислении результатов.
Если число в ячейке выровнено по левому краю, обычно это означает, что оно не отформатировано как число.
При введении в ячейку числа с буквой "е", например 1e9, оно автоматически преобразуется в научное число: 1,00E+09. Чтобы избежать этого, введите перед числом апостроф: ‘1e9
В зависимости от введенного числа в левом верхнем углу ячейки может появиться маленький зеленый треугольник, указывающий на то, что число хранится как текст ,что для Excel является ошибкой. Не обращайте внимание на треугольник или щелкните его. Слева появится поле. Щелкните поле, а затем выберитеигнорировать ошибку , из-за чего треугольник будет отопуститься.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Еще один, более редкий, случай – когда Excel показывает вам ошибку «Слишком много различных форматов ячеек».
Такая ошибка возникает, если в рабочей книге превышается предельно допустимое количество форматов, которое программа может сохранять:
- для Excel 2003 – это 4000 форматов (файл с расширением .xls)
- для Excel 2007 и новее – это 64000 (файл .xlsx)
Причем под форматом в данном случае понимается любая уникальная комбинация параметров:
- шрифт
- заливка
- границы ячеек
- числовой формат
- условное форматирование
Так, например, если вы оформили небольшой фрагмент листа подобным образом:
. то Excel запомнит в книге 9 разных форматов ячеек, а не 2, как кажется на первый взгляд. Ведь толстая линия по периметру создаст, фактически 8 различных вариантов. Тяга к красоте в большом отчете приведет к появлению сотен и тысяч подобных комбинаций, которые Excel будет вынужден запоминать. Размер файла от этого, само собой, увеличивается.
Что же делать? Если у вас файл старого образца, сохраните его с расширением .xlsx
Ну а если это не спасает, тогда придется удалять лишнее.
Изменение цвета, шрифта, границ, выравнивания и других форматов ячеек вручную было бы утомительным и трудоемким. К счастью, Microsoft Excel предоставляет несколько быстрых и простых способов очистить форматирование рабочего листа.
Функция очистки форматов.
- Выберите ячейку или диапазон ячеек, из которых вы хотите удалить оформление.
- На вкладке Главная в группе Редактирование щелкните стрелку рядом с кнопкой Очистить.
- Выберите «Очистить форматы» .
Это удалит все форматирование ячеек (включая условное форматирование, числовые форматы, шрифты, цвета, границы и т. д.), но сохранит содержимое ячейки.
Советы по очистке формата.
С помощью этой функции вы можете легко приводить в исходное состояние не только одну ячейку, но всю строку, столбец или рабочий лист.
- Чтобы очистить форматирование от всех ячеек на листе, выберите весь лист, нажав Ctrl + A или, нажав треугольник в верхнем левом углу рабочего листа, а затем используйте кнопку Очистить форматы .
- Дабы удалить форматирование из всего столбца или строки, щелкните заголовок столбца или строки, чтобы выбрать их целиком.
- Если сделать эту операцию в несмежных ячейках или диапазонах, то выберите начальную точку, нажмите и удерживайте CTRL и затем выбирайте дальше другие ячейки или области.
Если вы хотите использовать этот инструмент буквально в один клик, вы можете добавить параметр «Очистить форматы» на панель инструментов быстрого доступа или на ленту Excel. Это может быть особенно полезно, если вы получаете много файлов от своих коллег или клиентов, и они нуждаются в том, чтобы представить данные так, как вы хотите.
Добавьте параметр «Очистить форматы» на панель быстрого доступа.
Если приходится часто выполнять подобные операции, то вы можете добавить иконку очистки на панель быстрого доступа в верхнем левом углу:
Для этого выполните следующие действия:
- На листе Excel щелкните Файл >Параметры, а затем выберите Панель быстрого доступа на левой панели.
- В разделе «Выбрать команды из» выберите «Все команды».
- Прокрутите список команд до пункта «Очистить форматы», выберите его и нажмите кнопку «Добавить», чтобы переместить его в правую часть.
- Щелкните ОК.
Как можно использовать «Формат по образцу».
Думаю, все знают, как использовать этот инструмент для копирования форматирования в Excel. Но задумывались ли вы, что его также можно использовать для удаления формата? Все, что требуется, – это 3 быстрых шага:
Примечание. «Очистка формата» и «Формат по образцу» не могут очистить форматирование, примененное только к какой-то части содержимого ячейки.
Например, если вы выделили только одно слово в ячейке цветом, как показано на скриншоте ниже, такое форматирование не будет удалено:
Мы рассмотрели основные способы, чтобы убрать форматирование в ячейках таблицы Excel.
Как сделать пользовательский числовой формат в Excel - В этом руководстве объясняются основы форматирования чисел в Excel и предоставляется подробное руководство по созданию настраиваемого пользователем формата. Вы узнаете, как отображать нужное количество десятичных знаков, изменять выравнивание или цвет шрифта,…
Как изменить формат даты в Excel и создать собственное форматирование - Это руководство посвящено форматированию даты в Excel и объясняет, как установить вид даты и времени по умолчанию, как изменить их формат и создать собственный. Помимо чисел, наиболее распространенными типами данных,…
7 способов поменять формат ячеек в Excel - Мы рассмотрим, какие форматы данных используются в Excel. Кроме того, расскажем, как можно быстро изменять внешний вид ячеек самыми различными способами. Когда дело доходит до форматирования ячеек в Excel, большинство…
Денежный и финансовый формат — как установить и что предпочесть? - Когда мы хотим, чтобы числа отображались в Excel как денежные значения, мы должны отформатировать их соответствующим образом. В сегодняшней публикации мы подробно объясним, как применить финансовый либо денежный форматы. Рассмотренные в…
Процентный формат ячеек и особенности его применения в Excel - В этом коротком руководстве вы найдете много полезных сведений и узнаете, как показать результаты вычислений в процентах, как в Excel заменить число на процент по мере ввода и многое другое.…
Округление в Excel – какую из 12 формул лучше использовать? - В статье объясняется использование ОКРУГЛ, ОКРУГЛТ, ОКРУГЛВВЕРХ, ОКРВНИЗ, ЦЕЛОЕ, ОТБР и множества других функций округления в Excel. Также приводятся примеры формул для изменения десятичных чисел до целых или до определенного…
6 способов — как безопасно удалить лишние пустые строки в Excel - Это руководство научит вас нескольким простым приемам безопасного удаления нескольких пустых строк в Excel без потери информации. Пустые строки в таблице — это проблема, с которой мы все время от…
Как в Excel разделить текст из одной ячейки в несколько - В руководстве объясняется, как разделить ячейки в Excel с помощью формул и стандартных инструментов. Вы узнаете, как разделить текст запятой, пробелом или любым другим разделителем, а также как разбить строки на…
8 способов разделить ячейку Excel на две или несколько - Как разделить ячейку в Excel? С помощью функции «Текст по столбцам», мгновенного заполнения, формул или вставив в нее фигуру. В этом руководстве описаны все варианты, которые помогут вам выбрать технику, наиболее подходящую…
Как перенести текст в ячейке Excel на следующую строку? - В этом руководстве показано, как автоматически сделать перенос текста в одной ячейке и как вставлять разрыв строки вручную. Вы также узнаете о наиболее распространенных причинах, по которым перенос в Excel не…
При импорте в Excel данных из внешних программ, иногда возникает весьма неприятная проблема - дробные числа превращаются в даты:
Так обычно происходит, если региональные настройки внешней программы не совпадают с региональными настройками Windows и Excel. Например, вы загружаете данные с американского сайта или европейской учётной системы (где между целой и дробной частью - точка), а в Excel у вас российские настройки (где между целой и дробной частью - запятая, а точка используется как разделитель в дате).
При импорте Excel, как положено, пытается распознать тип входных данных и следует простой логике - если что-то содержит точку (т.е. российский разделитель дат) и похоже на дату - оно будет конвертировано в дату. Всё, что на дату не похоже - останется текстом.
Давайте рассмотрим все возможные сценарии на примере испорченных данных на картинке выше:
- В ячейке A1 исходное число 153.4182 осталось текстом, т.к. на дату совсем не похоже (не бывает 153-го месяца)
- В ячейке A2 число 5.1067 тоже осталось текстом, т.к. в Excel не может быть даты мая 1067 года - самая ранняя дата, с которой может работать Excel - 1 января 1900 г.
- А вот в ячейке А3 изначально было число 5.1987, которое на дату как раз очень похоже, поэтому Excel превратил его в 1 мая 1987, услужливо добавив единичку в качестве дня:
Вот такие варианты. И если текстовые числа ещё можно вылечить банальной заменой точки на запятую, то с числами превратившимися в даты такой номер уже не пройдет. А попытка поменять их формат на числовой выведет нам уже не исходные значения, а внутренние коды дат Excel - количество дней от 01.01.1900 до текущей даты:
Лечится вся эта история тремя принципиально разными способами.
Способ 1. Заранее в настройках
Если данные ещё не загружены, то можно заранее установить точку в качестве разделителя целой и дробной части через Файл - Параметры - Дополнительно (File - Options - Advanced) :
Снимаем флажок Использовать системные разделители (Use system separators) и вводим точку в поле Разделитель целой и дробной части (Decimal separator) .
После этого можно смело импортировать данные - проблем не будет.
Способ 2. Формулой
Если данные уже загружены, то для получения исходных чисел из поврежденной дата-тексто-числовой каши можно использовать простую формулу:
=--ЕСЛИ( ЯЧЕЙКА("формат";A1)="G" ; ПОДСТАВИТЬ(A1;".";",") ; ТЕКСТ(A1;"М,ГГГГ") )
В английской версии это будет:
=--IF (CELL ("format ";A1)="G"; SUBSTITUTE (A1;".";","); TEXT (A1;"M ,YYYY "))
Логика здесь простая:
- Функция ЯЧЕЙКА (CELL) определяет числовой формат исходной ячейки и выдаёт в качестве результата "G" для текста/чисел или "D3" для дат.
- Если в исходной ячейке текст, то выполняем замену точки на запятую с помощью функции ПОДСТАВИТЬ (SUBSTITUTE) .
- Если в исходной ячейке дата, то выводим её в формате "номер месяца - запятая - номер года" с помощью функции ТЕКСТ (TEXT) .
- Чтобы преобразовать получившееся текстовое значение в полноценное число - выполняем бессмысленную математическую операцию - добавляем два знака минус перед формулой, имитируя двойное умножение на -1.
Способ 3. Макросом
Если подобную процедуру лечения испорченных чисел приходится выполнять часто, то имеет смысл автоматизировать процесс макросом. Для этого жмём сочетание клавиш Alt + F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , вставляем в нашу книгу новый пустой модуль через меню Insert - Module и копируем туда такой код:
Останется выделить проблемные ячейки и запустить созданный макрос сочетанием клавиш Alt + F8 или через команду Макросы на вкладке Разработчик (Developer - Macros) . Все испорченные числа будут немедленно исправлены.
Если для каких-либо ячеек на листе был установлен текстовый формат (это мог сделать пользователь или программа при выгрузке данных в 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'ем как текст, поэтому не будет работать группировка и сортировка. Решения - те же самые, что и для чисел, только формат вместо числового нужно заменить на дату-время.
Читайте также: