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

Какое форматирование применимо к ячейкам в эксель. Применение условного форматирования в Excel

Форматирование ячеек – это одно из основных отличий Excel от калькулятора. Умелое форматирование позволяет разношерстный набор данных привести к удобному для осмысления виду: выделить итоговую строку, шапку, убрать «лишние» знаки после запятой, добавить границы и т.д. В общем, форматирование позволяет набор «серой массы данных» превратить в структурированную и читабельную информацию.

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

Если этих кнопок не достаточно, то можно обратиться к Формату ячеек в контекстном меню (через правую кнопку мыши), либо с помощью сочетания горячих клавиш Ctrl+1, что существенно быстрее. Откроется известное многим окошко.

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

Принципы форматирования таблиц в Microsoft Excel

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

Защита данных

С помощью данной функции можно защитить содержимое ячеек, поэтому она также доступна в окне форматирования в отдельно вкладке.

Здесь мы можем путем установки галочки:

• скрыть формулы;
• защитить ячейки.

Данные опции работают только после защиты листа. Более подробно ознакомиться с тем, как защитить данные в Эксель можно в нашей статье – “Как поставить пароль для защиты файла в Excel: книга, лист”.

На панели инструментов найти эти опции можно в группе “Ячейки” (вкладка “Главная”), нажав кнопку “Формат”.

В открывшемся списке представлен блок под названием “Защита”. Также здесь есть возможность сразу включить защиту листа. Если нужно перейти в окно “Формат ячеек”, следует нажать на одноименную кнопку.

Форматирование таблиц

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

Автоформатирование

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

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

Находясь во вкладке «Главная» кликаем по кнопке «Форматировать как таблицу». Данная кнопка размещена на ленте в блоке инструментов «Стили». После этого открывается большой список стилей с предустановленными свойствами, которые пользователь может выбрать на свое усмотрение. Достаточно просто кликнуть по подходящему варианту.

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

Переход к форматированию

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

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

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

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

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

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

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

Если вы откроете окно «Формат ячеек» чрез контекстное меню, то нужные настройки будут располагаться во вкладке «Число» в блоке параметров «Числовые форматы». Собственно, это единственный блок в данной вкладке. Тут производится выбор одного из форматов данных:

После того, как выбор произведен, нужно нажать на кнопку «OK».

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

Для параметра «Дата» доступна возможность установить, в каком виде дата будет выводиться на экран (только числами, числами и наименованиями месяцев и т.д.).

Аналогичные настройки имеются и у формата «Время».

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

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

Впрочем, если вы хотите более точно произвести форматирование, то в этом списке нужно кликнуть по пункту «Другие числовые форматы…». Откроется уже знакомое нам окно «Формат ячеек» с полным перечнем изменения настроек.

Выравнивание

Целый блок инструментов представлен во вкладке «Выравнивание» в окне «Формат ячеек».

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

Кроме того, в этой же вкладке можно позиционировать текст внутри ячейки по горизонтали и вертикали.

В параметре «Ориентация» производится настройка угла расположения текста в ячейке таблицы.

Блок инструментов «Выравнивание» имеется так же на ленте во вкладке «Главная». Там представлены все те же возможности, что и в окне «Формат ячеек», но в более усеченном варианте.

Во вкладке «Шрифт» окна форматирования имеются широкие возможности по настройке шрифта выделенного диапазона. К этим возможностям относятся изменение следующих параметров:

• тип шрифта;
• начертание (курсив, полужирный, обычный)
• размер;
• цвет;
• видоизменение (подстрочный, надстрочный, зачеркнутый).

На ленте тоже имеется блок инструментов с аналогичными возможностями, который также называется «Шрифт».

Во вкладке «Граница» окна форматирования можно настроить тип линии и её цвет. Тут же определяется, какой граница будет: внутренней или внешней. Можно вообще убрать границу, даже если она уже имеется в таблице.

А вот на ленте нет отдельного блока инструментов для настроек границы. Для этих целей во вкладке «Главная» выделена только одна кнопка, которая располагается в группе инструментов «Шрифт».

Во вкладке «Заливка» окна форматирования можно производить настройку цвета ячеек таблицы. Дополнительно можно устанавливать узоры.

На ленте, как и для предыдущей функции для заливки выделена всего одна кнопка. Она также размещается в блоке инструментов «Шрифт».

Если представленных стандартных цветов вам не хватает и вы хотите добавить оригинальности в окраску таблицы, тогда следует перейти по пункту «Другие цвета…».

После этого открывается окно, предназначенное для более точного подбора цветов и оттенков.

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

На ленте аналогичные функции можно увидеть после клика по кнопке «Формат», которая расположена во вкладке «Главная» в блоке инструментов «Ячейки». Как видим, появляется список, в котором имеется группа настроек «Защита». Причем тут можно не только настроить поведение ячейки в случае блокировки, как это было в окне форматирования, но и сразу заблокировать лист, кликнув по пункту «Защитить лист…». Так что это один из тех редких случаев, когда группа настроек форматирования на ленте имеет более обширный функционал, чем аналогичная вкладка в окне «Формат ячеек».

. Урок:
Как защитить ячейку от изменений в Excel
Как видим, программа Excel обладает очень широким функционалом по форматированию таблиц. При этом, можно воспользоваться несколькими вариантами стилей с предустановленными свойствами. Также можно произвести более точные настройки при помощи целого набора инструментов в окне «Формат ячеек» и на ленте. За редким исключением в окне форматирования представлены более широкие возможности изменения формата, чем на ленте.

Трансформация в “Умную таблицу”

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

Производим выделение нужных элементов. На ленте программы находим группу инструментов “Стили” (вкладка “Главная“) и жмем кнопку “Форматировать как таблицу”.

Назначение и удаление форматов в Excel

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

Назначение формата

1. Выделите ячейку (диапазон ячеек).
2. Выберите команду «Формат»-«Ячейки» (Ctrl+1).
3. В появившемся окне диалога (подробно окно будет рассмотрено позже) введите нужные параметры форматирования.
4. Нажмите кнопку «Ок»

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

Удаление формата

1. Выделите ячейку (диапазон ячеек).
2. Выберите команду «Правка»-«Очистить»-«Форматы».
3. Для удаления значений в ячейках надо выбрать команду «Все» подменю «Очистить».

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

Форматирование с помощью панелей инструментов

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

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

Форматирование отдельных символов

Форматирование можно применять к отдельным символам текстового значения в ячейке так же, как и ко всей ячейке. Для этого выделите нужные символы и затем в меню «Формат» выберите команду «Ячейки». Установите нужные атрибуты и нажмите кнопку «Ок». Нажмите клавишу Enter, чтобы увидеть результаты своего труда.

Применение автоформата

Автоматические форматы Excel — это заранее определенные сочетания числового формата, шрифта, выравнивания, границ, узора, ширины столбца и высоты строки.

Чтобы использовать автоформат, надо выполнить следующие действия:

1. Введите нужные данные в таблицу.
2. Выделите диапазон ячеек, которые необходимо форматировать.
3. В меню «Формат» выберите команду «Автоформат». При этом откроется окно диалога.

В начало страницы

В начало страницы

Заливка ячеек

Если мы перейдем во вкладку “Заливка” в окне “Формат ячеек”, то получим доступ к следующим настройкам:

• цвет фона выделенных ячеек;
• выбор узора в качестве заливки;
• цвет узора.

Отслеживать результат можно в специальном поле “Образец”.

Щелкнув по кнопке “Способы заливки” можно попасть в настройки градиентной заливки (хотя данной опцией пользуются не часто).

На панели инструментов программы, как и в случае с границами, для заливки предусмотрена только одна кнопка в группе “Шрифт” (вкладка “Главная”), нажатие на которую откроет список возможных вариантов.

При выбор пункта “Другие цвета” откроется окно в котором мы можем более детально настроить цвет в одной из двух вкладок – “Цвет” или “Спектр”.

Форматирование чисел в виде текста

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

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

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

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

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

На вкладке Главная

в группе
число
щелкните стрелку рядом с полем
числовой формат
и выберите пункт
текст
.

Если вы не видите параметр
текст
, прокрутите список до конца списка с помощью полосы прокрутки.

Чтобы использовать десятичные знаки в числах, хранящихся как текст, возможно, придется вводить эти числа с десятичными разделителями.

При вводе числа, начинающегося с нуля (например, кода продукта), этот ноль по умолчанию удаляется. Если требуется сохранить ноль, можно создать пользовательский числовой формат, который не позволит приложению Excel удалять начальные нули в числах. Например, при вводе десятизначного кода продукта Excel по умолчанию изменяет число 0784367998 на 784367998. В данном случае можно создать пользовательский числовой формат с кодом 0000000000

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

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

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

Как сделать условное форматирование в Excel

Инструмент «Условное форматирование» находится на главной странице в разделе «Стили».

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

Сравним числовые значения в диапазоне Excel с числовой константой. Чаще всего используются правила «больше / меньше / равно / между». Поэтому они вынесены в меню «Правила выделения ячеек».

Введем в диапазон А1:А11 ряд чисел:

Выделим диапазон значений. Открываем меню «Условного форматирования». Выбираем «Правила выделения ячеек». Зададим условие, например, «больше».

Введем в левое поле число 15. В правое – способ выделения значений, соответствующих заданному условию: «больше 15». Сразу виден результат:

Выходим из меню нажатием кнопки ОК.

Условное форматирование по значению другой ячейки

Сравним значения диапазона А1:А11 с числом в ячейке В2. Введем в нее цифру 20.

Выделяем исходный диапазон и открываем окно инструмента «Условное форматирование» (ниже сокращенно упоминается «УФ»). Для данного примера применим условие «меньше» («Правила выделения ячеек» — «Меньше»).

В левое поле вводим ссылку на ячейку В2 (щелкаем мышью по этой ячейке – ее имя появится автоматически). По умолчанию – абсолютную.

Результат форматирования сразу виден на листе Excel.

Значения диапазона А1:А11, которые меньше значения ячейки В2, залиты выбранным фоном.

Зададим условие форматирования: сравнить значения ячеек в разных диапазонах и показать одинаковые. Сравнивать будем столбец А1:А11 со столбцом В1:В11.

Выделим исходный диапазон (А1:А11). Нажмем «УФ» — «Правила выделения ячеек» — «Равно». В левом поле – ссылка на ячейку В1. Ссылка должна быть СМЕШАННАЯ или ОТНОСИТЕЛЬНАЯ! , а не абсолютная.

Каждое значение в столбце А программа сравнила с соответствующим значением в столбце В. Одинаковые значения выделены цветом.

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

В нашем примере в момент вызова инструмента была активна ячейка А1. Ссылка $B1. Следовательно, Excel сравнивает значение ячейки А1 со значением В1. Если бы мы выделяли столбец не сверху вниз, а снизу вверх, то активной была бы ячейка А11. И программа сравнивала бы В1 с А11.

Чтобы инструмент «Условное форматирование» правильно выполнил задачу, следите за этим моментом.

Проверить правильность заданного условия можно следующим образом:

1. Выделите первую ячейку диапазона с условным форматированим.
2. Откройте меню инструмента, нажмите «Управление правилами».

В открывшемся окне видно, какое правило и к какому диапазону применяется.

Условное форматирование – несколько условий

Исходный диапазон – А1:А11. Необходимо выделить красным числа, которые больше 6. Зеленым – больше 10. Желтым – больше 20.

• 1 способ. Выделяем диапазон А1:А11. Применяем к нему «Условное форматирование». «Правила выделения ячеек» — «Больше». В левое поле вводим число 6. В правом – «красная заливка». ОК. Снова выделяем диапазон А1:А11. Задаем условие форматирования «больше 10», способ – «заливка зеленым». По такому же принципу «заливаем» желтым числа больше 20.
• 2 способ. В меню инструмента «Условное форматирование выбираем «Создать правило».

Заполняем параметры форматирования по первому условию:

Нажимаем ОК. Аналогично задаем второе и третье условие форматирования.

Обратите внимание: значения некоторых ячеек соответствуют одновременно двум и более условиям. Приоритет обработки зависит от порядка перечисления правил в «Диспетчере»-«Управление правилами».

То есть к числу 24, которое одновременно больше 6, 10 и 20, применяется условие «=$А1>20» (первое в списке).

Условное форматирование в Excel с использованием формул

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

Есть столбец с числами. Необходимо выделить цветом ячейки с четными. Используем формулу: =ОСТАТ($А1;2)=0.

Выделяем диапазон с числами – открываем меню «Условного форматирования». Выбираем «Создать правило». Нажимаем «Использовать формулу для определения форматируемых ячеек». Заполняем следующим образом:

Для закрытия окна и отображения результата – ОК.

Границы и линии

В окне “Формат ячеек” можно также выполнить настройки границ, перейдя в одноименную вкладку, в которой представлены следующие параметры:

• тип линии;
• цвет;
• вариант отображения: нет;
• внешние;
• внутренние (если выделено две и более ячейки).

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

На ленте программы специальной группы инструментов для настройки границ нет. В блоке “Шрифт” представлена лишь одна кнопка, раскрывающая перечень доступных параметров.

Выбрав пункт “Другие границы” мы попадем во вкладку “Границы” окна форматирования, которую описали выше.

Условное форматирование строки по значению ячейки

Задача: выделить цветом строку, содержащую ячейку с определенным значением.

Таблица для примера:

Необходимо выделить красным цветом информацию по проекту, который находится еще в работе («Р»). Зеленым – завершен («З»).

Выделяем диапазон со значениями таблицы. Нажимаем «УФ» — «Создать правило». Тип правила – формула. Применим функцию ЕСЛИ.

Порядок заполнения условий для форматирования «завершенных проектов»:

Обратите внимание: ссылки на строку – абсолютные, на ячейку – смешанная («закрепили» только столбец).

Аналогично задаем правила форматирования для незавершенных проектов.

В «Диспетчере» условия выглядят так:

Когда заданы параметры форматирования для всего диапазона, условие будет выполняться одновременно с заполнением ячеек. К примеру, «завершим» проект Димитровой за 28.01 – поставим вместо «Р» «З».

«Раскраска» автоматически поменялась. Стандартными средствами Excel к таким результатам пришлось бы долго идти.

Установка стиля для целой строки

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

И применим его ко всему диапазону.

Это позволит раскрасить всю таблицу. Обратите внимание на первый доллар в формуле. Он означает, что столбец зафиксирован, а менять будет только номер строки. Его устанавливать надо обязательно, иначе стиль применяться ко всей строке не будет.

Содержание

По умолчанию, те

Здесь (слева направо) кнопки: по левому краю, по центру и по правому краю.

Выравнивание по вертикали

изменяется с помощью окна «Формат ячеек», закладка «Выравнивание»:

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

Это можно сделать двумя способами:

• кнопка «Объединить и поместить в центре»
  на панели инструментов «Форматирование» ;
• галочка «объединение ячеек»
  в окне «Формат ячеек», закладка «Выравнивание».

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

При активной объединенной ячейке на панели инструментов «Форматирование» будет вдавлена кнопка «Объединить и поместить в центре». Если при этом нажать ее, объединенная ячейка опять распадется на «родные» маленькие, а значение перенесется в верхнюю левую ячейку.

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

• Выделяем «нужные» ячейки и нажимаем Delete ;
• Выделяем «нужные» ячейки, щелкаем правой кнопкой и выбираем «Очистить содержимое»;
• Выделяем «нужные» ячейки, заходим в меню «Правка» → «Очистить» → «Выбираем нужный вариант»;
• Клавиша ← Backspace очищает содержимое активной
  (одной!) ячейки и включает режим редактирования. Ее удобно использовать при необходимости удалить одно (не все, как по Delete ) значение из выделенного диапазона;
• Если «родную» неотформатированную ячейку протащить с помощью маркера автозаполнения по отформатированным, произойдет своего рода удаление;
• пункт меню «Правка» → «Удалить…» и пункт контекстного меню «Удалить…» приведет к открытию окна «Удаление ячеек».

В отличие от предыдущих способов (очистка), при этом способе данные из ячеек не просто стираются, а со смещением всех соседних ячеек, строк и столбцов. Меняется строение всей таблицы.

Есть два способа изменить цвет заливки выделенных ячеек:

• кнопка «Цвет заливки» на панели инструментов «Форматирование» ;
• окно «Формат ячеек», закладка «Вид»:

Лист Excel по умолчанию представляет собой таблицу. Однако сетка таблицы не выводится на печать, пока мы их не наведем. Существует три способа добавить границы к выделенным ячейкам:

• Кнопка «Границы» на панели инструментов «Форматирование» ;
• окно «Граница», вызываемое из кнопки «Границы» —> «Нарисовать границы…» (см. выше):

• окно «Формат ячеек», закладка «Граница»:

Формат ячеек по умолчанию («Общий») Править

По умолчанию после создания документа все ячейки находятся в формате «Общий». Этот формат имеет ряд хитростей:

• числа выравниваются по правому краю, а текст — по левому;
• если, изменяя ширину столбца, сделать ее меньше определенной, то число в ячейках заменяется на символы «#». Это не ошибка. Это означает, что нужно сделать столбец пошире;
• если число очень большое («6000000000000») или очень маленькое («0,00000000000001»), оно автоматически преобразуется в экспоненциальный (научный) формат («6E+12» и «1E-14» соответственно);
• при изменении ширины столбца округляются десятичные дроби. Например, если написать «3,1415», затем изменить ширину так, чтобы «5» перестала помещаться, в ячейке отобразится «3,142».

Необходимость изменения формата по умолчанию на другой Править

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

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

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

1. автоматически после ввода определенных данных в ячейку Excel сам сменит формат ячейки;
2. с помощью кнопок на панели инструментов «Форматирование».
3. с помощью окна «Формат ячеек»;

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

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

• Дата
  . Если в ячейке написать «1.2.3» или «1/2/3», Excel заменит это на «01.02.2003» (первое число второго месяца третьего года). При этом формат ячейке будет автоматически преобразован в «Дата». Если написать «1.2» или «1/2», то Excel заменит это на «01.фев».;
• Процентный
  . Если в ячейке написать «1%», формат ячейки автоматически сменится на «Процентный»;
• Время
  . Если в ячейке написать «13:46:44» или «13:46», формат ячейки автоматически сменится на «Время»;

Изменение формата ячеек с помощью кнопок на панели инструментов «Форматирование» Править

На панели инструментов «Форматирование» находятся 5 кнопок, с помощью которых можно быстро изменить формат выделенных ячеек.

Описание кнопок (слева направо):

• Денежный формат
  . Будет использована денежная единица по умолчанию (см. выше);
• Процентный формат
  . Если в ячейке уже будет находится число, то Excel домножит его на 100 и добавит знак «%». Все правильно, ведь 1 арбуз — это «100%», а «0,7» арбуза — «70%»;
• Формат с разделителями (числовой формат)
  . В этом формате будут отделяться пробелом группы разрядов (сотни, сотни тысяч и т.д.) и будет добавлено 2 знака после запятой;
• Увеличить разрядность
  . Добавляет один десятичный разряд;
• Уменьшить разрядность
  . Убирает один десятичный разряд.

Выравнивание содержимого

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

Окно “Формат ячеек”

Переключившись во вкладку “Выравнивание” мы получим обширный набор параметров, включающий в себя:

• выравнивание по горизонтали;
• выравнивание по вертикали;
• отступ;
• варианты отображения: перенос текста;
• автоподбор ширины;
• объединение ячеек;

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

Для других достаточно просто поставить галочку напротив.

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

Инструменты на ленте

На ленте программы есть специальная группа инструментов “Выравнивание”, где можно выполнить самые популярные настройки.

Если их окажется недостаточно, чтобы попасть во вкладку “Выравнивание” окна форматирования ячеек, можно щелкнуть по значку (в виде незаконченного квадрата со стрелкой внутри), который расположен в правой нижней части блока инструментов.

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

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

Формальные ячейки

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

Применение стилей Excel

Нажмите Главная > Стили ячеек и выберите стиль.

Изменение стиля Excel

Выделите ячейки со стилем Excel.

В разделе Главная > Стили ячеек щелкните правой кнопкой мыши примененный стиль.

Выберите Изменить > Формат, чтобы внести нужные изменения.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Примеры настраиваемых форматов ячеек

Что такое настраиваемые форматы можно посмотреть в другом материале. Сейчас же посмотрим некоторые примеры настраиваемых форматов ячеек. Сценариев использования настраиваемых форматов очень много, всё зависит от того какой результат мы хотим получить.

Минуточку внимания!

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

Исходные значения

Возьмем для примеров такие значения:

Здесь в ячейках B2:B10 исходные значения, в ячейках С2:C10 ссылки на исходные значения. Для наглядности условное форматирование будем применять к ячейкам С2:C10.

Добавляем текст к значению

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

Необходимо применить форматирование к ячейкам, добавив к значению единицы измерения, например «мм». Для этого можно ввести новый формат – #» мм» (здесь и далее, что бы не путать знаки в формате, вводить необходимо только описание выделенное зеленым цветом). Обратите внимание, Excel не воспринимает знак пробела вне кавычек. Хотя на самом деле, пробел вне кавычек обрабатывается, но имеет другое предназначение, о чем будет показано в одном из следующих примеров. В результате получим:

Так же обратите внимание, что в формате использовали только один знак «#», для определения цифрового разряда. По этому Excel автоматический округлил не целые значения до ближайших целых.

Если изменить формат скажем на — «Размер:» #» мм» получим в результате такое отображение:

Остались ли значения?

Хотя конечно текст перед значением используется не так часто. Но всё таки можно настроить и такое представление данных. Сейчас для нас визуально выглядит так как будто к диапазону С2:C10 применена формула (что бывает очень часто, для специального отображения используется формула, а не настраиваемый формат). Что будет если вернуть отображение ячеек С2:C10 к прежнему виду, применив «Общий» формат, а к ячейкам B2:B10 применить первый настроенный формат с окончанием «мм». Давайте попробуем, и в результате получим:

Вот сейчас мы видим действие форматирования наиболее ярко. Как Вы помните в ячейках С2:C10 только ссылки на значения B2:B10, при этом ячейки С2:C10 отображаются «как есть», те просто отображают значения других ячеек без применения дополнительного форматирования. При этом к исходным ячейкам применено настраиваемое форматирование, поэтому они отображаются с окончанием «мм».

Что ж, решим теперь другую проблему, в нашем первом формате производится округление, изменим форматирование таким образом, что бы значение отображалось со знаками после запятой. Для этого исправим форматирование на — #,0″ мм» . В результате получим:

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

Добавляем нули

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

Что бы решить такую задачу, введем новое форматирование, например до 4 знаков – 0000 . Довольно просто:

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

Тысячи

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

Применим к ячейкам С2:С10 следующий формат – # ###,00 (ВНИМАНИЕ! В формате последний знак не отображается — это пробел)

Результат будет такой. Для наглядности к ячейкам В2:В10 применено числовое форматирование с разделением на разряды. Кроме того обратите внимание что в формате на самом деле два знака пробела, но интерпретируются они по-разному. Первый пробел делит значение на разряды, в то время как последний знак формата, пробел, делит при отображении значение на 1 000. Тем самым меняя представление числа на представление в тысячах.

Миллионы

Если мы добавим к формату еще один пробел в окончании, то получим

Получили значения в миллионах. Таким образом, один пробел делит значение на 1 000, каждый следующий пробел возводит начальное значение в степень равную позиции пробела. То есть два пробела это 1000 во 2 степени, три пробела 1000 в 3 степени и т.д. Вернемся к формату, да получили значение в миллионах но пропали значения до запятой, всё из за того что мы использовали знак «#» определяющий цифровой разряд. Если мы изменим формат, заменив последний перед запятой символ «#» символом «0» — # ##0,00 (не забудьте в конце два пробела). То получим, вполне правильное отображение чисел в миллионах.

И снова несколько замечаний, во первых обратите внимание что значения округляются, округляются до последнего знака после запятой, или если десятичной запятой нет то к ближайшему целому. В нашем примере если бы не было в формате «,00» то кроме значений 1 и 30 все остальные были бы равны 0. В примере отображения в тысячах и миллионах наиболее четко видно отличие применения символа «#» от «0». Если бы в формате мы заменили все символы решетки нулем, то не получили бы результат который хотели бы. Нуль определяет значащий разряд, если в разряде нет значения – отображается нуль. Решетка определяет просто цифровой разряд, если в разряде нет значения, то и отображаться ни чего не будет. Поэтому если бы мы заменили все символы «#» на нули в формате, то получили бы значения в миллионах, но слева от значения стояли бы нули.

Место для подписи

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

Опять поменяем исходные данные, но теперь просто введем в ячейку В12 фамилию, а в ячейке С12 сделаем ссылку на ячейку с фамилией. Теперь к ячейке С12 применим форматирование – @*_

Красиво получилось. Но самое интересно в том, что если менять размер ячейки С12, то символ подчеркивания всё равно будет заполнять всё свободное место ячейки. Теперь представьте, в отчете у Вас не одно место для подписи, а несколько, поменяли размер, сколько времени потребуется для того что бы подогнать значения в каждой ячейки, добавив или удалив символы подчеркивания. Примените форматирование, и Excel сделает всё за Вас.

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

Выделяем результаты в отчете

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

Для этого используем три элемента формата, задав для положительных и отрицательных чисел выделение цветом — [Синий]# ##0,0#;[Красный]-# ##0,0#;0

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

В качестве заключения

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);
• форматы;
• примечания;
• гиперссылки.

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

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

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

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

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