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

Пользовательский ЧИСЛОвой формат в MS EXCEL (через Формат ячеек)

В Excel имеется множество встроенных числовых форматов, но если ни один из них не удовлетворяет пользователя, то можно создать собственный числовой формат. Например, число -5,25 можно отобразить в виде дроби -5 1/4 или как (-)5,25 или 5,25- или, вообще в произвольном формате, например, ++(5)руб.###25коп. Рассмотрены также форматы денежных сумм, процентов и экспоненциального представления.

Для отображения числа можно использовать множество форматов. Согласно российским региональным стандартам ( Кнопка Пуск/ Панель Управления/ Язык и региональные стандарты ) число принято отображать в следующем формате: 123 456 789,00 (разряды разделяются пробелами, дробная часть отделяется запятой). В EXCEL формат отображения числа в ячейке можно придумать самому. Для этого существует соответствующий механизм – пользовательский формат. Каждой ячейке можно установить определенный числовой формат. Например, число 123 456 789,00 имеет формат: # ##0,00;-# ##0,00;0

Пользовательский числовой формат не влияет на вычисления, меняется лишь отображения числа в ячейке. Пользовательский формат можно ввести через диалоговое окно Формат ячеек, вкладка Число, (все форматы), нажав CTRL+1. Сам формат вводите в поле Тип, предварительно все из него удалив.

Рассмотрим для начала упомянутый выше стандартный числовой формат # ##0,00;-# ##0,00;0
В дальнейшем научимся его изменять.

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

  • Символ решетка (#) означает любую цифру.
  • Символ пробела в конструкции # ##0 определяет разряд (пробел показывает, что в разряде 3 цифры). В принципе можно было написать # ###, но нуль нужен для отображения 0, когда целая часть равна нулю и есть только дробная. Без нуля (т.е. # ###) число 0,33 будет отражаться как ,33.
  • Следующие 3 символа ,00 (запятая и 00) определяют, как будет отображаться дробная часть. При вводе 3,333 будут отображаться 3,33; при вводе 3,3 – 3,30. Естественно, на вычисления это не повлияет.

Вторая часть формата – для отображения отрицательных чисел. Т.е. можно настроить разные форматы для отражения положительных и отрицательных чисел. Например, при формате # ##0,00;-###0;0 число 123456,3 будет отображаться как 123 456,30, а число -123456,3 как -123456. Если формата убрать минус, то отрицательные числа будут отображаться БЕЗ МИНУСА.

Третья часть формата – для отображения нуля. В принципе, вместо 0 можно указать любой символ или несколько символов (см. статью Отображение в MS EXCEL вместо 0 другого символа).

Есть еще и 4 часть – она определяет вывод текста. Т.е. если в ячейку с форматом # ##0,00;-# ##0,00;0;»Вы ввели текст» ввести текстовое значение, то будет отображено Вы ввели текст.

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

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

Рассмотрим пользовательские форматы на конкретных примерах.

Применение пользовательских форматов

Создание пользовательских форматов

Excel позволяет создать свой (пользовательский) формат ячейки. Многие знают об этом, но очень редко пользуются из-за кажущейся сложности. Однако это достаточно просто, главное понять основной принцип задания формата.

Для того, чтобы создать пользовательский формат необходимо открыть диалоговое окно Формат ячеек и перейти на вкладку Число. Можно также воспользоваться сочетанием клавиш Ctrl + 1.

В поле Тип вводится пользовательские форматы, варианты написания которых мы рассмотрим далее.

В поле Тип вы можете задать формат значения ячейки следующей строкой:

[цвет]»любой текст»КодФормата»любой текст»

Посмотрите простые примеры использования форматирования. В столбце А — значение без форматирования, в столбце B — с использованием пользовательского формата (применяемый формат в столбце С)

Какие цвета можно применять

В квадратных скобках можно указывать один из 8 цветов на выбор:

Синий, зеленый, красный, фиолетовый, желтый, белый, черный и голубой.

Далее рассмотрим коды форматов в зависимости от типа данных.

Числовые форматы

Форматы даты

Стоит обратить внимание, что форматы даты можно комбинировать между собой. Например, формат «ДД.ММ.ГГГГ» отформатирует дату в привычный нам вид 31.12.2017, а формат «ДД МММ» преобразует дату в вид 31 Дек.

Читать еще:  Как в excel связать ячейки

Форматы времени

Аналогичные форматы есть и для времени.

Текстовые форматы

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

Создание пользовательских форматов для категорий значений

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

  1. Положительные числа
  2. Отрицательные числа
  3. Нулевые значения
  4. Текстовый формат

Для этого мы можем в поле Тип указать следующую конструкцию:

Формат положительных значений ; отрицательных ; нулевых ; текстовых

Соответственно для каждой категории можно применять формат уже описанного нами вида:

[цвет]»любой текст»КодФормата»любой текст»

В итоге конечно может получится длинная строка с форматом, но если приглядеться подробнее, то сложностей никаких нет.

Смотрите какой эффект это дает. В зависимости от значения, меняется форматирование, а если вместо числа указано текстовое значения, то Excel выдает «нет данных».

Редактирование и копирование пользовательских форматов

Чтобы отредактировать созданный пользовательский формат необходимо:

  1. Выделить ячейки, формат которых вы хотите отредактировать.
  2. Открыть диалоговое окно Формат ячеек и перейти на вкладку Число. Можно также воспользоваться сочетанием клавиш Ctrl + 1.
  3. Изменить строку форматирования в поле Тип.

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

  1. Использовать функцию копирования по образцу.
  2. Выделить ячейки, открыть окно Формат ячеек, на вкладке Число в списке Все форматы выбрать нужный формат и нажать ОК.

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

Изменение формата ячеек в Excel

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

Основные виды форматирования и их изменение

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

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

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

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

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

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

  1. Выделяем ячейки, которые нужно соответствующим образом отформатировать. Выполняем клик правой кнопкой мыши. Вследствие этого открывается контекстный список действий. Нужно остановить выбор на пункте «Формат ячеек…».

  • Активируется окно форматирования. Выполняем переход во вкладку «Число», если окно было открыто в другом месте. Именно в блоке параметров «Числовые форматы» находятся все те варианты изменения характеристик, о которых шел разговор выше. Выбираем пункт, который соответствует данным, находящимся в выбранном диапазоне. При необходимости в правой части окна определяем подвид данных. Жмем на кнопку «OK».
  • После этих действий формат ячеек изменен.

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

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

    1. Переходим во вкладку «Главная». При этом, нужно выделить соответствующие ячейки на листе, а в блоке настроек «Число» на ленте открыть поле выбора.

    Просто производим выбор нужного варианта. Диапазон сразу же после этого изменит своё форматирование.

    Но в указанном списке представлены только основные форматы. Если вы хотите более точно указать форматирование, то следует выбрать пункт «Другие числовые форматы».

  • После данных действий, откроется окно форматирования диапазона, о котором уже шёл разговор выше. Пользователь может выбрать тут любой из основных или дополнительных форматов данных.
  • Способ 3: блок инструментов «Ячейки»

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

      Выделяем диапазон на листе, который следует отформатировать. Располагаясь во вкладке «Главная», кликаем по значку «Формат», который находится в группе инструментов «Ячейки». В открывшемся списке действий выбираем пункт «Формат ячеек…».

    Читать еще:  Как в excel изменить формат ячеек

  • После этого активируется уже хорошо знакомое нам окно форматирования. Все дальнейшие действия точно такие же, как уже было описано выше.
  • Способ 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 данными, никому не удаться сразу все красиво и правильно заполнить с первой попытки.

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

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

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

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

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

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

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

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

    1. Число. Здесь задается способ отображения числовых значений.
    2. Выравнивание. На этой вкладке можно управлять положением текста. Причем текст можно отображать вертикально или по диагонали под любым углом. Еще обратите внимание на раздел «Отображение». Очень часто используется функция «перенос по словам».
    3. Шрифт. Задание стилевого оформления шрифтов, размера и цвета текста. Плюс режимы видоизменений.
    4. Граница. Здесь задаются стили и цвета оформления границ. Дизайн всех таблиц лучше оформлять именно здесь.
    5. Заливка. Название закладки говорит само за себя. Доступны для форматирования цвета, узоры и способы заливки (например, градиентом с разным направлением штрихов).
    6. Защита. Здесь устанавливаются параметры защиты ячеек, которые активируются только после защиты целого листа.

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

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

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

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

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

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

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

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

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

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

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

    Читать еще:  Как собрать данные с нескольких листов excel на один лист

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

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

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

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

    Доступные числовые форматы в Excel

    В этом курсе:

    В Excel числа, содержащиеся в ячейках, можно преобразовать, например, в денежные единицы, проценты, десятичные числа, даты, номера телефонов или номера социального страхования США.

    Выделите ячейку или диапазон ячеек.

    На вкладке Главная выберите в раскрывающемся списке формат Числовой.

    Вы также можете выбрать один из следующих вариантов:

    Нажмите клавиши CTRL + 1 и выберите число.

    Щелкните ячейку или диапазон ячеек правой кнопкой мыши, выберите команду Формат ячеек. и формат Числовой.

    Нажмите кнопку вызова диалогового окна рядом с надписью число а затем выберите число.

    Выберите нужный формат.

    Числовые форматы

    Чтобы просмотреть все доступные числовые форматы, на вкладке Главная в группе Число нажмите кнопку вызова диалогового окна рядом с надписью Число.

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

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

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

    Используется для отображения денежных значений с выравниванием обозначений денежных единиц и десятичных разделителей в столбце.

    Отображает числовые представления даты и времени как значения даты в соответствии с заданным типом и языковым стандартом (местоположением). Форматы даты, начинающиеся со звездочки (*), соответствуют формату отображения даты и времени, заданному на панели управления. На форматы без звездочки параметры, заданные на панели управления, не влияют.

    Отображает числовые представления даты и времени как значения времени в соответствии с заданным типом и языковым стандартом (местоположением). Форматы времени, начинающиеся со звездочки (*), соответствуют формату отображения даты и времени, заданному на панели управления. На форматы без звездочки параметры, заданные на панели управления, не влияют.

    В этом формате значение ячейки умножается на 100, а результат отображается со знаком процента (%). Можно задать количество знаков в дробной части.

    Отображает число в виде дроби выбранного типа.

    Отображает число в экспоненциальном представлении, заменяя часть числа на E+n, где E обозначает экспоненциальное представление, то есть умножение предшествующего числа на 10 в степени n. Например, экспоненциальный формат с двумя знаками в дробной части отображает 12345678901 как 1,23E+10, то есть 1,23, умноженное на 10 в 10-й степени. Можно задать количество знаков в дробной части.

    Содержимое ячейки (включая числа) обрабатывается как текст и отображается именно так, как было введено.

    Число отображается в виде почтового индекса, телефонного номера или страхового номера (SSN).

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

    Вы можете применить различные форматы к числам, чтобы изменить способ их отображения. Только форматы изменяют способ отображения чисел и не влияют на значения. Например, если вы хотите, чтобы число отображалось в денежных единицах, щелкните ячейку с числовым значением > денежный.

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

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

    Похожие статьи

    Ссылка на основную публикацию
    Похожие публикации
    Adblock
    detector