Интернет 

Автоматическое изменение формата ячейки после ввода данных. Как в Excel изменить форматирование содержимого ячеек Как в эксель сделать числовой формат

При заполнении листов Excel данными, никому не удаться сразу все красиво и правильно заполнить с первой попытки.

В процессе работы с программой постоянно что-то нужно: изменять, редактировать, удалять, копировать или перемещать. Если в ячейку введенные ошибочные значения, естественно мы хотим их исправить или удалить. Но даже такая простая задача иногда может создавать сложности.

Как задать формат ячейки в Excel ?

Содержимое каждой ячейки Excel состоит из трех элементов:

  1. Значение: текст, числа, даты и время, логическое содержание, функции и формулы.
  2. Форматы: тип и цвет границ, тип и цвет заливки, способ отображения значений.
  3. Примечания.

Все эти три элемента совершенно не зависимы между собой. Можно задать формат ячейки и ничего в нее не записывать. Или добавить примечание в пустую и не отформатированную ячейку.



Как изменить формат ячеек в Excel 2010?

Чтобы изменить формат ячеек следует вызвать соответствующее диалоговое окно комбинацией клавиш CTRL+1(или CTRL+SHIFT+F) или из контекстного меню после нажатия правой кнопкой мышки: опция «Формат ячеек».

В данном диалоговом окне доступно 6 закладок:


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

Какое форматирование применимо к ячейкам в Excel?

Каждая ячейка всегда имеет какой-то формат. Если не было никаких изменений, то это «Общий» формат. Он же является стандартным форматом Excel, в котором:

  • числа выравниваются по правой стороне;
  • текст выравнен по левой стороне;
  • шрифт Colibri с высотой 11 пунктов;
  • ячейка не имеет границ и заливки фона.

Удаление формата – это изменение на стандартный формат «Общий» (без границ и заливок).

Стоит отметить, что формат ячеек в отличие от их значений, нельзя удалить клавишей DELETE.

Чтобы удалить формат ячеек следует их выделить и использовать инструмент «Очистить форматы», который находится на закладке «Главная» в разделе «Редактирование».

Если желаете очистить не только формат, но и значения то выберите из выпадающего списка инструмента (ластика) опцию «Очистить все».

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

  • содержимое (то же что и клавиша DELETE);
  • форматы;
  • примечания;
  • гиперссылки.

Опция «Очистить все» объединяет в себе все эти функции.

Удаление примечаний

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

  1. Инструментом ластиком: опция «Очистить примечания».
  2. Кликнуть по ячейка с примечанием правой кнопкой мышки, а из появившегося контекстного меню выбрать опцию «Удалить примечание».

Примечание. Второй способ более удобный. При одновременном удалении нескольких примечаний следует предварительно выделить все их ячейки.

Формат ячейки включает в себя параметры, которые определяют: » вид представления числового значения ячейки; » выравнивание и ориентацию содержимого ячейки; » вид шрифта; « обрамление ячейки; заполнение (фон) ячейки. Кроме этого, можно установить параметры защиты данных, записанных в ячейку. Обычно ячейки форматируются путем изменения отдельных

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

Стандартные форматы В Excel имеется шесть стандартных форматов (наборов параметров) ячеек, которые "могут применяться во всех документах. В начале работы с новой таблицей все ячейки имеют одинаковый стандартный формат, используемый по умолчанию. Этот формат называется Обычный. Он имеет следующие параметры:

Изображенное значение повторяет истинное;

Выравнивание по горизонтали зависит от типа данных;

Выравнивание по вертикали выполняется по нижнему краю;

Текст без переноса слов;

Ориентация изображенного значения горизонтальная;

Для отображения содержимого используется стандартный шрифт без выделения и эффектов;

Обрамления вокруг ячеек нет;

Заполнения нет.

Остальные стандартные форматы отличаются от формата Обычный только отображением чисел в ячейках:

Финансовый (или С разделителями) - число округляется до двух знаков после десятичного разделителя, например, число 43,569 представляется в виде 43,57;

Финансовый(0) - число округляется до целого, например число 43,569 представляется в виде 44;

Денежный - число округляется до двух знаков после десятичного разделителя и добавляется знак денежной единицы, например число 43,569 на «российском» компьютере представляется в виде 43,57р.;

Денежный(0) - число округляется целого и добавляется знак денежной единицы, например, число 43,569 представляется в виде 44р.;

Процентный - если в ячейку после установки формата введено число от 0 до 1, то оно умножается на 100, затем число (независимо от величины) округляется до целого и к нему добавляется знак %, в результате этого, например, числа 0,43569 и 43,569 будут представляться одинаково 44%. Если в диалоговом окне Параметры на вкладке Правка выключить переключатель Автоматический ввод, процентов, то на 100 будут умнйжаться все числа, а не только от 0 до 1. В результате этого, например, числа 0,43569 и 43,569 будут представляться как 44% и 4357%. То же самое будет происходить, если этот переключатель включен, однако числа были введены в ячейки до того, как им был присвоен формат Процентный.

При использовании финансовых и денежных форматов между разрядами единиц, тысяч, миллионов и т. д. устанавливается разделитель, заданный в настройках Windows: вкладка Числа в программе Язык и стандарты на Панели управления. Обычно это пробел. При настройке Windows задается и знак денежной единицы валюты: вкладка Денежная единица в программе Язык и стандарты на Панели управления. Для рубля это обычно р. Используемый разделитель разрядов можно изменить, не изменяя при этом общую настройку Windows.

Для этого следует в диалоговом окне Параметры на вкладке Международные в группе Числа выключить переключатель Использовать системные разделители и в поле Разделитель разрядов ввести другой разделитель. Иногда в качестве такого разделителя используется запятая или апостроф. Для применения стандартных форматов могут использоваться соответствующие инструменты панели Форматирование и клавиатурные комбинации, а также диалоговое окно Стиль. Представление числового значения Как уже отмечалось, значения после их ввода в ячейки имеют вид, установленный по умолчанию.

Однако существует возможность изменить вид ячеек по своему желанию. Для изменения отдельных параметров могут использоваться инструменты панели Форматирование и клавиатурные комбинации. Однако наиболее полные возможности по форматированию ячеек предоставляет диалоговое окно Формат ячеек. Для вызова окна Формат ячеек необходимо выполнить одно из следующих действий:

Выполнить команду Ячейки...(Формат);

Выполнить команду Формат ячеек... контекстного меню ячейки (выделенных ячеек);

Нажать комбинацию Ctrl+1.

На вкладке Число окна Формат ячеек в списке Числовые форматы представлены названия групп имеющихся в Excel видов представления числовых значений. По умолчанию в списке выбрано значение Общий, при котором числовые значения отображаются в том виде, в каком они были введены пользователем. После выбора другого значения на вкладке появляются дополнительные опции, с помощью которых задаются параметры внешнего вида числовых значений.

Например, при выборе значения Числовой появляются:

Поле Число десятичных знаков - установка количества знаков после десятичного разделителя;

Переключатель Разделитель групп разрядов - включение разделителя групп разрядов (пробела) между группами разрядов единиц, тысяч, миллионов и т. д.;

Список Отрицательные числа - выбор вида представления отрицательных чисел. При выборе формата удобно пользоваться полем Образец, которое изменяется в соответствии с изменениями опций вкладки. Если в списке Числовые форматы выбрано значение (все форматы), то появляется список Тип, включающий в себя все имеющиеся форматы чисел, которые, однако, записаны в виде кодов (условных обозначений). Разобравшись с этими кодами, пользователь в поле Тип может самостоятельно задавать форматы.

Так, например, в кодах форматов используются следующие обозначения:

Необязательная цифра, т. е. если на месте знака # в числе цифры нет, то на это место ничего не выводится;

Обязательная цифра, т. е. если на месте знака 0 в числе цифры нет, то на это место выводится нуль (обычно таким образом задается вывод нулей после десятичного разделителя, например, формат 0.000 устанавливает обязательный вывод на экран трех знаков после разделителя);

Обязательное место, т. е. если на месте знака? в числе цифры нет, то на это место выводится пробел (обычно таким образом обеспечивается выравнивание чисел в одном столбике, например, формат?????0.00 устанавливает выравнивание по десятичному разделителю всех чисел, имеющих до шести цифр перед разделителем); ч, чч, м, мм, с, ее, Д, ДД часы, минуты, секунды и дни месяца (при использовании однобуквенного обозначения числа меньше 10 выводятся без ведущего нуля, т. е., например, время 6 часов 5 минут в формате ч:м будет иметь вид 6:7, а в формате чч:мм - 06:07); М, ММ, МММ, ММММ месяц (например, февраль в разных форматах будет иметь вид 2,02, фее, февраль); IT, ГГГГ год (запись с использованием 2-х или 4-х цифр, соответственно).

Разделители в форматах даты и времени определяются установками, заданными в настройках Windows:

вкладки Дата и Время в программе Язык и стандарты на Панели управления. Более подробную информацию об обозначениях, используемых в кодах, можно найти в справочной системе Excel. Созданный пользователем формат можно затем удалить, нажав кнопку Удалить. При изменении формата числовых значений их истинные значения (введенные или вычисленные по формулам) не изменяются, т. е., например, независимо от того, сколько знаков после запятой выведено на экран, в вычислениях по формулам будут использоваться все имеющиеся знаки.

Иначе говоря, установка нового формата изменяет только внешний вид числового значения, не изменяя его величину. Вместе с тем, существует возможность установки такого режима, при котором точность истинных значений всегда будет равна точности представления чисел на экране. Для этого необходимо в диалоговом окне Параметры на вкладке Вычисления в группе Параметры книги включить переключатель точность как на экране. Эта установка будет действовать для всех таблиц книги.

Для установки формата числовых значений может использоваться панель Форматирование, на которой располагаются следующие инструменты:

Денежный формат установка стандартного формата Денежный;

Процентный формат установка стандартного формата Процентный;

Формат с разделителями установка стандартного формата Финансовый,

Увеличить разрядность увеличение на единицу количества знаков после десятичного разделителя;

Уменьшить разрядность..;

уменьшение на единицу количества знаков после десятичного разделителя. Клавиатурные комбинации для установки форматов числовых значений:

Ctrl+Shift+" .... установка формата Обычный;

CtrI+Shift+1 ... установка формата числа 0.00 с разделителем групп разрядов;

Ctrl+Shift+2.... установка формата времени чмм;

Ctrl+Shift+З.... установка формата даты ДД. МММ.ГГ;

Ctrl+Shift+4.... установка формата Денежный;

Ctrl+Shift+5.... установка формата Процентный;

CtrI+Shift+6.... установка формата 0.00Е+00 (экспоненциальный вид). Если в ячейку введено числовое значение, однако требуется, чтобы оно воспринималось программой как текстовое, то для такого преобразования в диалоговом окне Форма ячеек на вкладке Число в списке Числовые форматы следует выбрать значение Текстовый.



Графики и диаграммы (5)
Работа с VB проектом (12)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (39)
Баги и глюки Excel (3)

Не изменяется формат ячейки

В который раз на одном из форумов увидел вопрос с описанием непонятного бага: при попытке изменить формат ячеек или вызывать диалоговое окно Формат ячеек(правая кнопка мыши-Формат ячеек (Cell Format) ничего не происходит. Что делает невозможным изменить визуальный стиль ячеек. При этом происходить данная проблема может не для всех ячеек, а выборочно для любой ячейки листа. Если кто-то не понял проблему: для некоторых ячеек невозможно назначить или изменить границы ячеек, цвет заливки, цвет и стиль шрифта и т.п.

Возможная причина: в какой-то момент при работе с файлом в ячейках возник конфликт форматов и стиль ячеек был заблокирован. Как правило это происходит при работе с файлом в версиях Excel 2007 и выше. Я предполагаю, что связано это с тем, что в новых форматах файлов данные о форматах ячеек хранятся в XML-схемах и порой при изменениях происходит конфликт стилей и Excel не может определить текущий формат ячейки и как следствие изменить. Не могу сказать 100% действительно ли в этом причина, но других вариантов у меня пока нет. Надо узнавать у Microsoft, но никто из "владельцев" проблемы не может сказать в какой момент причина появилась, а без этого вряд ли получиться что-то нарыть. Сам я смоделировать подобную ситуацию не смог.

Решение: я пока знаю два возможных варианта решения данной проблемы. Какой из них использовать, если вы столкнулись с данной проблемой - выбирать вам.

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

вариант 1

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

Вариант 2

  1. копируете любую ячейку, формат для которой изменяется без проблем
  2. выделяете "непослушные" ячейки-правая кнопка мыши-Специальная вставка (PasteSpecial) - Форматы (Formats)

Данный вариант (в книгах, любезно предоставленных носителями проблемы) у меня срабатывал довольно неплохо. В чем его очевидное преимущество перед первым вариантом - вы не теряете все форматы в книге. Однако есть и другой нюанс - нельзя будет с достоверностью сказать, что проблема в очень скором будущем не повторится. Хотя нельзя сказать и того, что она повторится.

Статья помогла? Поделись ссылкой с друзьями! Видеоуроки

{"Bottom bar":{"textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24,"textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance":30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500,"textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100%; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive":"","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40}}

Формат ячейки в программе Эксель задает не просто внешний вид отображения данных, но и указывает самой программе, как именно их следует обрабатывать: как текст, как числа, как дату и т.д. Поэтому очень важно правильно установить данную характеристику диапазона, в который будут вноситься данные. В обратном случае, все вычисления будут просто некорректными. Давайте выясним, как изменить формат ячеек в Microsoft Excel.

Сразу определим, какие форматы ячеек существуют. Программа предлагает выбрать один из следующих основных видов форматирования:

  • Общий;
  • Денежный;
  • Числовой;
  • Финансовый;
  • Текстовый;
  • Дата;
  • Время;
  • Дробный;
  • Процентный;
  • Дополнительный.

Кроме того, существует разделение на более мелкие структурные единицы вышеуказанных вариантов. Например, форматы даты и времени имеют несколько подвидов (ДД.ММ.ГГ., ДД.месяц.ГГ, ДД.М, Ч.ММ PM, ЧЧ.ММ и др.).

Изменить форматирование ячеек в Excel можно сразу несколькими способами. О них мы подробно поговорим ниже.

Способ 1: контекстное меню

Самый популярный способ изменения форматов диапазона данных – это использование контекстного меню.


После этих действий формат ячеек изменен.

Способ 2: блок инструментов «Число» на ленте

Форматирование также можно изменить, используя инструменты, находящиеся на ленте. Этот способ выполняется даже быстрее предыдущего.


Способ 3: блок инструментов «Ячейки»

Ещё одним вариантом настройки данной характеристики диапазона является использования инструмента в блоке настроек «Ячейки» .


Способ 4: горячие клавиши

И наконец, окно форматирования диапазона можно вызвать при помощи так называемых горячих клавиш. Для этого нужно предварительно выделить изменяемую область на листе, а затем набрать на клавиатуре комбинацию Ctrl+1 . После этого, откроется стандартное окно форматирования. Изменяем характеристики так же, как об этом было уже сказано выше.

Кроме того, отдельные комбинации горячих клавиш позволяют менять формат ячеек после выделения диапазона даже без вызова специального окна:

  • Ctrl+Shift+- — общий формат;
  • Ctrl+Shift+1 — числа с разделителем;
  • Ctrl+Shift+2 — времени (часы.минуты);
  • Ctrl+Shift+3 — даты (ДД.ММ.ГГ);
  • Ctrl+Shift+4 – денежный;
  • Ctrl+Shift+5 – процентный;
  • Ctrl+Shift+6 — формат О.ООЕ+00.

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

При копировании ЧИСЛОвых данных в EXCEL из других приложений бывает, что Числа сохраняются в ТЕКСТовом формате. Если на листе числовые значения сохранены как текст, то это может привести к ошибкам при выполнении вычислений. В ряде случаев можно быстро изменить текстовый формат на числовой сразу в нескольких ячейках. Произведем это преобразование с помощью Специальной вставки из Буфера обмена.

Если ячейка содержит число, но число сохранено как текст, то это может стать ошибкой в вычислениях. Например функция СУММ() игнорирует числа сохраненные как текст и может вернуть не то значение, которое от нее ожидают.

Как правило, в таких ячейках установлен текстовый формат. Если изменить формат ячейки на числовой (через Формат ячеек или Главная/ Число/ Числовой формат ), то изменений не последует.

Теперь присмотримся и увидим в левом верхнем углу каждой ячейки зеленый треугольник. При выделении ячейки сбоку от нее появится желтенький значок, нажав который можно преобразовать значения в числа.

После преобразования числа будут выравнены по правому краю ячейки (если, конечно, в формате ячейки установлено Выравнивание по горизонтали - по значению ).

Чтобы преобразовать сразу несколько значений - выделите их. У верхней ячейки появится желтый значок - с помощью него преобразуйте сразу все значения.

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

  • введите в любую пустую ячейку число 1 ;
  • выделите эту ячейку и скопируйте ее значение в Буфер обмена (CTRL+C );
  • выделите ячейки, которые содержат числа сохраненные как текст;
  • выберите Специальная ставка (на вкладке Главная в группе Буфер обмена щелкните стрелку рядом с командой Вставить , а затем выберите в списке пункт Специальная ставка );
  • выберите Умножить ;
  • нажмите ОК .

После умножения на 1 , числовые значения не изменятся, но будут преобразованы в числа (если по ошибке были выделены текстовые значения, операция умножения на них никак не повлияет).

Для EXCEL 2007: Если до умножения ячейки имели текстовый формат ячеек, то он будет сохранен (числовые значения все равно будут преобразованы, но будут выровнены по левому краю, как текст). Изменить формат ячеек на числовой можно стандартным способом через меню Формат ячеек (нажав CTRL+1 ).

Этот же прием поможет преобразовать ДАТЫ, сохраненные как текст в формат ДАТ (