Советы и лайфхаки

Формат эксель ячеек – 27.Форматы ячеек и создание таблиц в Excel. Автоформат и условное форматирование. Основные приемы работы в ms excel.

Содержание

Пользовательские форматы ячеек в Excel

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

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

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

Выделим весь диапазон с цифрами, далее необходимо нажать на правую кнопку мыши выбрать «Формат ячеек» (Format Cells) , и на вкладке «Число»  (Number) выберете пункт «Все форматы» (Custom).


Далее в поле «Тип» введем следующий текст

[Синий]+0,0;[Красный]-0,0;0,0

Нажимаем «ОК» и получим желаемый результат.

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

В данное поле можно ввести 4 формата через «; » (точка с запятой) в следующей последовательности:

  1. положительное число;
  2. отрицательное число;
  3.  

  4. ноль;
  5. если формат — текст;

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

Например, чтобы отображать номер телефона в формате +7 (905) 222-35-96
Необходимо написать следующий формат +7 (#) 000-00-00
в данном случае введенное число 79052223596 будет отображено как +7 (905) 222-35-96

Если указать квадратные скобки («[]») перед форматом, то в них можно указать цвет текста, всего поддерживается около 7 основных цветов. В этом есть некоторые неудобство, так, например, зеленый цвет слишком яркий и сливается с фоном и оттенок поменять нельзя, поэтому я выбрал синий цвет.

В нашем примере для положительного формата числа я написал:
[Синий]+0,0
где [Синий] — это цвет ячейки
«+» — это необходимость всегда вставлять знак «+» в положительных числах
0,0 — это формат самого числа. 0 (ноль) — это обязательное знакоместо, которое показывает, что в данном месте будет обязательно введено какое-то целое число, потом запятая и после запятой будет только одна цифра (один разряд). Например, если число было бы 256,253 — то оно будет отображено как +256,2 (256 — как целое число и одна двойка после запятой).

Еще пример, если указать формат «000,00», то число 25,2365 будет отражено как 025,23 и так далее.

Далее мы указали формат отрицательного числа
[Красный]-0,0
Тут аналогично  [Красный] — это цвет числа, «— « — указать минус перед отрицательным числом в формате 0,0 (одна цифра после запятой)

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

# (решетка) — в примере выше, это одно необязательное знакоместо, практически то же самое, что и ноль, но в случае отсутствия числа ничего выводиться не будет, а если бы был ноль, то был бы отражен ноль.

Далее в нашем примере для нулевого значения мы указали формат „0,0“, а для текста просто пропустили.

Надеюсь, теперь вас стало понятно как сделать пользовательский формат ячеек.

[ads]

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

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

0,00» кг»

В данном случае введено число 25,2336 будет отражено как 25,23 кг

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

Еще пример отображения дюймов в ячейке
Введите следующую маску формата

0,0″» « (два апострофа в кавычках)

Как отобразить логические значения? Введем формат

«Да»; «Да»; «Нет»

в данном случае, любое положительное число и отрицательное значение будет отражено как «Да», а ноль будет отражен как «Нет»

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

[Синий]↑+0,0%;[Красный] ↓-0,0%;0,0

Специальные символы вы можете найти в Excel на вкладке «Вставка», панель «Символы», значок «Символ»

Если вам понравилась статья, то, пожалуйста ставьте лайки («мне нравится») и до встречи в новых уроках.

sirexcel.ru

Пользовательские форматы в Excel

При необходимости Вы можете легко добавить к стандартным числовым форматам Excel свои собственные. Для этого выделите ячейки, к которым надо применить пользовательский формат, щелкните по ним правой кнопкой мыши и выберите в контекстном меню команду Формат ячеек (Format Cells) — вкладка Число (Number), далее — Все форматы (Custom)

В появившееся справа поле Тип: введите маску нужного вам формата из последнего столбца этой таблицы:

 Как это работает…

На самом деле все очень просто. Как Вы уже, наверное, заметили, Excel использует несколько спецсимволов в масках форматов:

  • 0 (ноль) — одно обязательное знакоместо (разряд), т.е. это место в маске формата будет заполнено цифрой из числа, которое пользователь введет в ячейку. Если для этого знакоместа нет числа, то будет выведен ноль. Например, если к числу 12 применить маску 0000, то получится 0012, а если к числу 1,3456 применить маску 0,00 — получится 1,35.

  • # (решетка) — одно необязательное знакоместо — примерно то же самое, что и ноль, но если для знакоместа нет числа, то ничего не выводится

  • (пробел) — используется как разделитель групп разрядов по три между тысячами, миллионами, миллиардами и т.д.

  • [ ] — в квадратных скобках перед маской формата можно указать цвет шрифта. Разрешено использовать следующие цвета: черный, белый, красный, синий, зеленый, жёлтый, голубой.

Плюс пара простых правил:

  • Любой пользовательский текст (кг, чел, шт и тому подобные) или символы (в том числе и пробелы) — надо обязательно заключать в кавычки.

  • Можно указать несколько (до 4-х) разных масок форматов через точку с запятой. Тогда первая из масок будет применяться к ячейке, если число в ней положительное, вторая — если отрицательное, третья — если содержимое ячейки равно нулю и четвертая — если в ячейке не число, а текст (см. выше пример с температурой).

Ссылки по теме

 

www.planetaexcel.ru

Форматы ячеек в Excel: основные понятия

Если кто не помнит или не знает, приложение MS Excel является одним из самых мощных средств работы не только с табличными данными, но и с массивами БД, создаваемыми или импортируемыми. При этом каждое значение в программе может иметь собственную функциональность. Форматы ячеек в Excel таковы, что в них можно задать любое значение, причем не только числовое, но и процентное, логическое.

Форматы ячеек в Excel: это что?

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

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

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

Если в формате установлено числовое значение, здесь тоже имеются свои хитрости. Например, знаки после запятой могут и не отображаться. Стандартные значения таблиц Excel таковы, что показывается только целое число. Мантисса отображается только в случае ввода данных после запятой. При этом она округляется. Таким образом, введя, например, значение числа «пи» (3,14), пользователь при отображении одного знака после точки и установленном округлении увидит значение 3,1. Чтобы отобразить все знаки, придется установить в параметрах разделителя их число.

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

Форматы ячеек в Excel: чем пользоваться?

Таблицы, создаваемые в Excel или в любых других подобных программах, не то что поддерживаются большинством программ такого направления, но и могут быть преобразованы в базы данных или даже в программные коды. В некотором смысле это относится к скриптам Visual Basic. Их платформа является встроенной в основной пакет Microsoft Office. Рассматривая вопрос о том, какие вообще бывают форматы ячеек в Excel, можно выделить несколько основных типов вводимых данных:

  • числовые;
  • тестовые;
  • дата и время;
  • экспоненциальные;
  • финансовые и денежные;
  • дробные;
  • процентные.

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

Как изменить параметры ячейки?

Изменение формата ячейки производится путем установки на ячейке прямоугольника. Он свидетельствует о ее активном состоянии, с вызовом контекстного меню через правый клик, где выбирается соответствующая строка. При этом для каждого типа формата можно задавать дополнительные условия. Например, отображение определенного количества знаков после запятой, наличие разделителя, обозначение валюты и т. д. В дополнительных форматах можно использовать и некоторые интересные вещи: введение почтового индекса, номера телефона или даже табельного номера, применяемого, к примеру, в «1С Бухгалтерии».

Кстати, форматы ячеек в Excel «1С Бухгалтерия» воспринимает как свои собственные, но в текстовом варианте. При этом даже не требуется производить экспортирование файла. Собственно, и сами файлы могут быть запросто восприняты другими программами, работающими с базами данных (тот же Access, построенный по образу и подобию Excel). Кроме того, если в созданном файле предусмотрено наличие связей или исполняемых скриптов VB, любая другая программа все это может открыть и сохранить в первозданном виде.

Дополнительные настройки

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

Связь с языками программирования

Кроме выше упомянутого языка Visual Basic, можно использовать и некоторые другие средства. Например, форматы ячеек в Excel Delphi-среда может изменять совершенно просто. Правда, иногда у людей, пишущих программы на основе этого языка, могут возникать проблемы. Ведь многие для изменения того же числового формата применяют строку «exRange.NumberFormat := ‘0.0’;». А такой вариант срабатывает только при условии, что в ячейках дробная часть числа равна нулю. При наличии других чисел формат остается текстовым. Впрочем, речь сейчас не о том. В данной ситуации нужно просто поменять команду или использовать правильный синтаксис.

Вместо итога

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

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

fb.ru

Форматирование ячеек в Excel — Академия Excel


Расчёты с использованием сложных формул, построение сводных таблиц и графиков, написание макросов — это явно не то, с чего началось Ваше знакомство с Excel. На первых порах ваши таблички выглядели примерно вот так (см. рисунок ниже) и самая главная проблема была в том: «Как сделать из чисел проценты, а суммы со знаком рубль/доллар?»



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


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


Оглавление

  1. Где вводить пользовательские форматы
  2. Как изменить пользовательский формат
  3. Коды для настройки формата
  4. Коды для числовых форматов
  5. Автоматическое округление
  6. Пользовательские форматы для дат
  7. Форматы для отображения времени
  8. Форматы для «истекшего» времени
  9. Цифровые форматы для ЦВЕТОВ
  10. Проверка условий в форматах
  11. Применение форматов чисел в формуле

Что такое формат чисел в Excel?


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


  • чисел (миллионы и сотни тысяч показывать без ненужных нулей), 


  • дат (показывать день недели и только название месяца), 


  • времени, долей, процентов и других числовых значений,


  • цветом шрифта обращать внимание пользователей на отрицательные числа или значения с ошибками.



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

Где вы можете использовать пользовательские форматы чисел?


Самый распространённый вариант использования пользовательских форматов – это непосредственно таблица на листе Excel, но также Вы можете использовать их:

  • в сводных таблицах — с помощью настроек поля значения

  • при построении графиков (на примере подписей данных)

  • в формулах (через функцию ТЕКСТ)


Для начала давайте всё же разберёмся с основными понятиями.

Формат ячейки и Пользовательский формат


Давайте внесём некоторую ясность в понимание этих двух терминов. Вот как я вижу эти два термина:


Пользовательский формат  — это код, который позволяет по-разному отображать ЗНАЧЕНИЕ ячейки, без возможности изменения внешнего вида (цвета заливки, размера и начертания шрифта).


Формат ячейки — это ВСЁ: заливка ячейки, настройки шрифта, толщины, типа и цвета границ ячейки.


Оба понятия используют как синонимы, но всё-таки это несколько разные вещи и в большей степени эта статья посвящена именно пользовательскому формату.


Например, в таблице ниже показаны 7 разных пользовательских форматов, примененных к той же дате, 1 мая 2020 года:



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



Давайте разберёмся что происходит при вводе значений в ячейку и как изменить формат.

Общий формат по умолчанию


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


Посмотрите как корректируются значения в столбце D (применён Общий формат) при изменении ширина столбца.




Рисунок 4. Отличие отображения чисел с общим форматом и числовым


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


На приведенном ниже рисунке значения в столбцах B и D одни и те же. Просто ширина столбца D меньше и Excel делает корректировки значений.

Где найти и как изменить формат ячейки?


Мне известны пять способов изменения формата ячейки:

  1. Использование списка популярных форматов на вкладке Главная (выпадающий список и пиктограммы)
  2. Нажатием Горячих клавиш (сочетания зависят от языковых настроек компьютера, вот эти работают всегда:Ctrl + 1  — числовой; Ctrl + 5  — процентный формат)
  3. Диалоговое окно Формат ячеек (через правый клик мыши или сочетание клавиш Ctrl + 1)
  4. Копирование формата через Специальную вставку или использование «метёлки» (кнопка Формат по образцу)
  5. Через диалоговое окно Найти и заменить.


Как видите, в Excel достаточно много уже готовых форматов, однако возможны ситуации, в которых ни один вам не подойдет. К примеру, если вам нужно видеть не просто дату, а ещё и день недели, то в стандартном наборе формата вы его не найдёте. Резонный вопрос: А как тогда быть? Читаем следующий раздел 🙂

Где вводить пользовательские форматы

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

 
Рисунок 5. Диалоговое окно «все форматы» в диалоговом окне Формат ячеек

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

Примечание. Форматы, которые Вы создаёте самостоятельно хранятся в текущем Excel-файле, а не в Excel вообще. Если вы скопируете значение, отформатированное в соответствии с пользовательским форматом, из одного файла в другой, то формат будет перенесен в книгу вместе со значением.

Как создать собственный формат

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

  1. Выберите ячейку (диапазон ячееек) со значениями, которые вы хотите отформатировать
  2. Нажмите сочетание клавиш Ctrl + 1> Число> Все форматы
  3. Введите код формата и просмотрите в поле как будет выглядеть значение в ячейке.
  4. Нажмите OK, чтобы сохранить и применить только что созданный формат


Как показывает практика, на шаге 3 возникают основные сложности, т.к. пока вам не совсем понятно что писать в поле Тип.


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


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

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


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


Предупреждение: после удаления пользовательского формата нет «отмены»!

Структура формата и справочная информация


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

[Зеленый]0;[Красный]-0;[Синий]»тут нолик»;[Фиолетовый]»введи число, а не текст»


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

  1. До первой точки с запятой указываем как будут отображаться Положительные значения (на примере формата выше — числа больше нуля будут написаны зелёным цветом шрифта)
  2. Ставим точку с запятой и пишем как отображать Отрицательные значения (красным цветом будем красить числа меньшие нуля)
  3. Ещё одна точка с запятой и указываем формат Нулевых значений (нестандартный вариант, когда в ячейке пишем 0, а пользователь видит текст «тут нолик»)
  4. Заключительный разделитель (;) и формат для Текстовых значений (также необычный формат — пишешь произвольный текст, а показывает сообщение об ошибке)

Не все разделы необходимы


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


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


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


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

Коды для настройки формата

Коды для числовых форматов

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

Ноль (0) 


Ноль (0) используется для принудительного отображения нулей, когда число имеет меньше цифр, чем нули в формате. Например, пользовательский формат 0,00 будет показывать ноль как 0,00, 1,1 как 1,10 и 0,5 как 0,50.



Рисунок 7. Примеры использования 0 (ноль) в формате

Знак решётка (#) 


Знак решётка (#) является заполнителем для необязательных цифр. Когда число имеет меньше цифр, чем # символов в формате, ничего не будет отображаться. Например, пользовательский формат #,## будет отображать 1,15 как 1,15 и 1,1 как 1,1.



Рисунок 8. Примеры использования # (решетки) в формате

Пробел ( )


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



Рисунок 9. Примеры использования   (пробела) в формате


Будьте внимательны при вводе пробелов — не ставьте лишних. Можете скопировать форматы:


0 » тыс.» — для тысяч (ноль пробел кавычки пробел тыс точка кавычки), 


0,0  » млн.»  — для миллионов (ноль запятая ноль пробел пробел кавычки пробел млн точка кавычки)


Если у Вас встречаются как тысячи, так и миллионы, то формат надо будет несколько модернизировать:


[>1000000]#,0 » млн»;[>1000]#,0 » тыс.»;# ##0,00


И опять призываю вводить внимательно (лучше скопировать). На всякий случай заменил символ пробел на слово Пробел

[>1000000]#,0ПробелПробел» млн»;[>1000]#,0Пробел» тыс.»;# ##0,00


Дальше в статье расскажу как осуществлять проверку условий в формате (узнаете что такое [>1000000]) и какие есть ограничения.

Вопросительный знак (?)


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



Рисунок 10. Примеры использования ? (знака вопроса) в формате


Вот не уверен, что будете использовать, но о том, что такое есть, знать надо.

Звёздочка (*)


Звёздочка (*) используется для повторения символов. Символ, следующий за звездочкой, будет повторяться, чтобы заполнить оставшееся пространство в ячейке.



Рисунок 11. Примеры использования * (звёздочки) в формате

Подчеркивание (_)


Подчеркивание (_) используется для добавления пробела в числовом формате. Символ, следующий за символом подчеркивания, определяет, сколько места нужно добавить. Обычным использованием символа подчеркивания является добавление пространства для выравнивания положительных и отрицательных значений, когда числовой формат добавляет круглые скобки только к отрицательным числам. Например, числовой формат «0 _»; (0) » добавляет немного места справа от положительных чисел, чтобы они оставались выровненными с отрицательными числами, заключенными в круглые скобки.



Рисунок 12. Примеры использования _ (подчёркивания) в формате

Знаки, которые можно использовать в формате

Итак, мы разобрались с основными символами, которые можно использовать в формате. Можно условно разделить все остальные на две группы:

  • те, что вводятся в поле формата и не требуют никакой дополнительной обработки
  • символы, которые надо вводить «слегка замудрённо» 
  • Например, символы звездочки (*), хеш (#) и процента (%) не могут использоваться непосредственно в пользовательском формате чисел, так как они не будут отображаться в ячейке. На помощь приходит обратная косая черта (\). Вводим её перед символом и теперь он будет отображаться в ячейке.

Рисунок 13. Пример знаков кода формата, отображаемых без кавычек 

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

1.   Смените раскладку клавиатуры на английский язык 

2.   нажмите клавишу NUM LOCK 

3.   введите с цифровой клавиатуры код соответствующего символа. 
 

Рисунок 14. Коды символы денежных единиц для формата 

Некоторые символы будут работать некорректно в формате пользовательского формата. Например, символы звездочки (*), хеш (#) и процента (%) не могут использоваться непосредственно в пользовательском формате — они не будут отображаться в результате. На помощь приходит обратная косая черта (\). Поместив обратную косую черту перед символом, вы можете использовать их в пользовательских форматах: 


Стоимость


Код


результат


100


\ # 0


# 100


100


\ * 0


* 100


100


\% 0

% 100

Автоматическое округление


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

Форматы номеров для ТЕКСТА


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


Пользовательские форматы для дат

Даты в Excel — это просто цифры, поэтому вы можете использовать пользовательские форматы, чтобы изменить способ отображения. Excel многие конкретные коды, которые вы можете использовать для отображения компонентов даты по-разному. На следующем экране показано, как Excel отображает дату в D5, 3 сентября 2018 года, с различными форматами пользовательских номеров:

Рисунок 15. Коды формата для дат

Форматы для отображения времени

Показываем время «обычное»

Время в Excel — это дробные части дня. Например, 6:00 – 0,25; 12:00 — 0,5, а 18:00 — 0,75. Вы можете использовать следующие коды в пользовательских форматах для отображения компонентов времени по-разному. На следующем экране показано, как Excel отображает время в D5, 9:35:07, с различными форматами пользовательских номеров:

Рисунок 16. Кодовые коды формата для «обычного» времени
Примечание: М (одна М русская) и ММ (две большие буквы М русские) нельзя использовать отдельно в пользовательском формате чисел, так как они конфликтуют с кодом номера месяца в кодах формата даты.

Форматы для «истекшего» времени

Истекшее время — это особый случай для отображения значений, превышающих 24 для часов и 60 для минут и секунд. Достаточно добавить квадратные скобки [], чтобы увидеть в ячейке сколько прошло часов, минут и секунд. На следующем экране показано, как Excel показывает прошедшее время, основанное на значении в D5, которое составляет 1,25 дня:

Рисунок 17.  Кодовые коды формата для «истёкшего» времени

Цифровые форматы для ЦВЕТОВ

Существует два способа определения цвета в формате ячеек. Самый распространённый вариант – написать в квадратных скобках название цвета. Excel знает следующие 8 цветов по имени в цифровом формате:
[черный] [белый] [красный] [зеленый] [синий] [желтый] [пурпурный] [голубой]. Имена цветов должны появляться в скобках.

Рисунок 18. Пример формата ячейки с цветами (пишем название текстом)
Если вам мало 8 цветов, то радостная весть в том, что также можно указать цвета по номеру индекса (Цвет1, Цвет2, Цвет3 и т. Д.). Нижеприведенные примеры используют формат пользовательского номера: [ЦветX] 0, где X — номер от 1 до 56
Символы треугольника добавлены только для того, чтобы сделать цвета более удобными для просмотра. Первое изображение отображает все 56 цветов на стандартном белом фоне. На втором изображении изображены те же цвета на сером фоне. Обратите внимание, что первые 8 цветов соответствуют названному списку цветов выше.

Рисунок 19. Результат применения формата [ЦветN]0″▲ ▼»] на белом фоне

Рисунок 20. Результат применения формата [ЦветN]0″▲ ▼»] на сером фоне

Проверка условий в форматах


Форматы пользовательских номеров также допускают условия, которые записываются в квадратных скобках, таких как [> 100] или [

[Красный] [
Для отображения значений, больших или равных 100 в синем, вы можете расширить формат следующим образом:

[Красный] [ = 100] 0

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

Применение форматов чисел в формуле

Хотя большинство форматов чисел применяются непосредственно к ячейкам на листе, вы также можете применять форматы чисел внутри формулы с помощью функции ТЕКСТ.Например, с допустимой датой в A1, следующая формула будет отображать только имя месяца:
= ТЕКСТ ( A1 , «мммм» )
Результатом функции TEXT всегда является текст, поэтому вы можете конкатенировать результат TEXT с другими строками:

= «Контракт истекает в« & TEXT ( A1 , «mmmm» )
На приведенном ниже экране показаны форматы чисел в столбце C, применяемые к номерам в столбце B с использованием функции TEXT:

 

Использование в формате проверочных условий

Если формат должен быть применен только в том случае, когда число отвечает определенным условиям, укажите условие в квадратных скобках [условное значение]. Условие состоит из оператора сравнения и значения (рис. 12). В первом случае формат проверяет попадает ли значение в диапазон от 0 до 1, и выводит значение в процентах; в противном случае выводится текст о недопустимом значении. Во втором случае числа не более 100 отображаются красным цветом, а более — синим. Любопытно, что третье условие мне ввести не удалось. Возможно, Excel поддерживает не более двух условий в одном формате.

Рис. 12. Использование проверочных условий в формате
Некоторые дополнительные возможности форматирования
Рис. 13. Выравнивание положительных и отрицательных чисел, если последние отображаются в скобках
Для заполнения всей ширины ячейки определенным знаком добавьте перед ним звездочку * в числовом формате. Например, для заполнения стрелками пустой области ячейки справа от числа следует указать #*→, для добавления перед числом знаков подчеркивания *­_# (рис. 14).

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

Рис. 15. Пользовательский денежный формат
Выше (см. рис. 8) мы показали, что пробел после кода числа эквивалентен делению на тысячу. Только что рассмотренный прием разделения на рубли и копейки может быть использован для масштабирования числа в сотнях. Это полезно для таких мер, как центнер, а также для перевода сантиметров в метры (рис. 16). Любопытно, что в Excel есть недокументированная особенность, которая позволяет без кавычек вводить довольно много текста (хотя некоторые буквы, как русского, так и английского алфавита всё же не проходят). На рис. 16 я привел два таких примера.

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

Рис. 21. П

vh10295.rdkd.ru

Как в Excel создать пользовательский формат чисел — Трюки и приемы в Microsoft Excel

Несмотря на то что Excel предлагает большое разнообразие встроенных числовых форматов; вы можете прийти к выводу, что ни один из них не соответствует вашим потребностям. В таком случае, вероятно, вы захотите создать собственный формат чисел. Это делается на вкладке Число диалогового окна Формат ячеек (рис. 49.1).

Самый простой способ вызвать это окно — нажать Ctrl+1. Вы также можете нажать кнопку его запуска в группе Число вкладки Главная (маленькая кнопка со стрелкой расположена справа от слова Число).

Рис. 49.1. Пользовательские (настраиваемые) числовые форматы можно создать на вкладке Число окна Формат ячеек

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

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

  1. Нажмите Ctrl+1 для вызова диалогового окна Формат ячеек.
  2. На вкладке Число выберите категорию (все форматы).
  3. Введите свой числовой формат в поле Тип. В табл. 49.1 и 49.2 представлены примеры кодов, которые вы можете использовать для создания собственных пользовательских форматов чисел.
  4. Нажмите кнопку ОК, чтобы закрыть диалоговое окно Формат ячеек.

Части строки формата числа

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

Следующие рекомендации помогут вам определить, какие из этих четырех разделов необходимо указывать.

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

Следующий пример пользовательского числового формата задает различные форматы для каждого из этих типов:
[3еленый]0сновной;[Красный]-Основной;[Черный]Основной;[Синий]Основной

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

Когда вы создаете пользовательские числовые форматы, не забывайте поле Образец на вкладке Число диалогового окна Формат ячеек. Здесь выводится значение активной ячейки, отформатированное тем способом, что задан в поле Тип.

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

excelexpert.ru

Числовое форматирование в Excel — Microsoft Excel для начинающих

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

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

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

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

  1. Выделите ячейки, к которым требуется применить числовой формат.
  2. На вкладке Главная нажмите на стрелку рядом с полем Числовой формат. Появится выпадающее меню.
  3. Выберите нужный формат. В нашем примере мы выберем Длинный формат даты.
  4. Числовое форматирование в выбранных ячейках изменится. Для некоторых форматов можно использовать команды Увеличить или Уменьшить разрядность, чтобы изменить количество выводимых десятичных знаков.

Варианты числовых форматов в Excel

На рисунке ниже представлены 11 вариантов числовых форматов, которые нам предлагает Excel.

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

1. Общий

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

Например, если Вы вводите «1-5», в ячейке отобразится число в кратком формате даты «1/5/2010».

2. Числовой

В формате Числовой значения отображаются с десятичными знаками. Например, если Вы введете в ячейку число «4», то в ней отобразится «4,00».

3. Денежный

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

4. Финансовый

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

5. Краткий формат даты

Краткий формат даты отображает содержимое ячейки в виде ДД.ММ.ГГГГ. Например, 8 августа 2014 года будет выглядеть как «08.08.2014».

6. Длинный формат даты

Длинный формат даты отобразит то же самое значение в виде «8 августа 2014 г.».

7. Время

Формат Время, позволяет отображать содержимое ячеек в виде Ч:ММ:СС. Например: 8:40:25 или 17:35:15.

8. Процентный

В процентном формате значения в ячейках умножаются на 100 и выводятся со знаком процента. По умолчанию отображается два знака после запятой, но Вы можете задать любое другое количество десятичных знаков. Например, если ввести в ячейку число «0,75», то оно будет выглядеть как «75,00%».

9. Дробный

Данный формат позволяет отобразить содержимое ячейки в виде дроби. Например, если ввести значение «1/4» в ячейку, которая имеет формат Общий, то в итоге мы получим дату «01.апр«. Если же ввести точно такое же значение в ячейку, имеющую формат Дробный, то отобразится дробь «1/4».

10. Экспоненциальный

Данный формат отображает значения в ячейках в экспоненциальном виде. Например, если ввести в ячейку число»140000″, то оно отобразится как «1,40E+05».

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

11. Текстовый

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

Другие числовые форматы

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

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

Оцените качество статьи. Нам важно ваше мнение:

office-guru.ru

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

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

Урок: Как форматировать таблицы в Microsoft Word

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

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

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

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

  1. Выделяем диапазон ячеек или таблицу.
  2. Находясь во вкладке «Главная» кликаем по кнопке «Форматировать как таблицу». Данная кнопка размещена на ленте в блоке инструментов «Стили». После этого открывается большой список стилей с предустановленными свойствами, которые пользователь может выбрать на свое усмотрение. Достаточно просто кликнуть по подходящему варианту.
  3. Затем открывается небольшое окно, в котором нужно подтвердить правильность введенных координат диапазона. Если вы выявили, что они введены не верно, то тут же можно произвести изменения. Очень важно обратить внимание на параметр «Таблица с заголовками». Если в вашей таблице есть заголовки (а в подавляющем большинстве случаев так и есть), то напротив этого параметра должна стоять галочка. В обратном случае её нужно убрать. Когда все настройки завершены, жмем на кнопку «OK».

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Урок: Как изменить формат ячейки в Excel

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

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

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

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

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

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

Шрифт

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

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

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

Граница

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

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

Заливка

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

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

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

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

Защита

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

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


.
Урок: Как защитить ячейку от изменений в Excel

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

Мы рады, что смогли помочь Вам в решении проблемы.

Задайте свой вопрос в комментариях, подробно расписав суть проблемы. Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

Да Нет

lumpics.ru

Отправить ответ

avatar
  Подписаться  
Уведомление о