Форматы ячеек в excel: Форматы ячеек в Excel / Skillbox Media

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

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

Все начинали с подобных таблиц 🙂

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

Содержание

1. О чём вообще речь? Покажи примеры!
2. Что такое формат чисел?
3. Где вы можете найти числовые форматы?
4. Общий формат по умолчанию
5. Как изменить формат ячейки?
6. Как создавать свои собственные форматы
7. Как создать собственный формат номера
8. Как изменить пользовательский формат
9. Структура формата и справочная информация
10. Не все разделы необходимы
11. Коды для настройки формата
12. Пользовательские форматы для дат
13. Форматы для отображения времени
14. Цифровые форматы для ЦВЕТОВ
15. Проверка условий
16. Применение форматов в формуле ТЕКСТ
17. Примеры с сайтов

О чём вообще речь? Покажи примеры!

В Excel достаточно много уже готовых форматов, однако возможны ситуации, в которых ни один вам не подойдет.

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

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

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

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

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

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

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

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

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

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

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

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

Где вы можете найти числовые форматы?

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

Эта кнопка открывает диалоговое окно «Формат ячеек». Вы найдете полный список форматов чисел, организованных по категориям, на вкладке «Число»:

Диалоговое окно «Формат ячеек» на вкладке «Число»

---

Примечание. Вы можете открыть диалоговое окно «Формат ячеек» с помощью сочетания клавиш Ctrl + 1

---

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

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

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

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

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

При вводе данных Excel иногда автоматически меняет числовые форматы. Например, если вы введете допустимую дату, Excel изменится на формат «Дата». Если вы введете процент, равный 5%, Excel изменится на «Процент» и так далее.

Способ 1. Формат по образцу (одноразовое использование)

Способ 2. Формат по образцу (МНОГОразовое использование)

Всё как и в первом способе, только делайте двойной клик по иконке Формат по образцу. Чтобы завершить использование формата по образцу нажмите ESC

Способ 3. Через специальную вставку

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Положительные значения (зелёным цветом)
2. Отрицательные значения (красным цветом перед числом будем ставить -)
3. Нулевые значения (будем писать текст «тут нолик»)
4. Текстовые значения (будем показывать текст «введи число, а не текст»)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Коды формата для дат

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

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

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

Кодовые коды формата для «обычного» времени

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

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

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

Кодовые коды формата для «прошедшего» времени

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

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

• [черный]
• [белый]
• [красный]
• [зеленый]
• [синий]
• [желтый]
• [пурпурный]
• [голубой]

Имена цветов должны появляться в скобках.

Пример формата ячейки с цветами (пишем название текстом)

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

Символы треугольника добавлены только для того, чтобы сделать цвета более удобными для просмотра. Первое изображение отображает все 56 цветов на стандартном белом фоне. На втором изображении изображены те же цвета на сером фоне. Обратите внимание, что первые 8 цветов соответствуют названному списку цветов выше.

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

Проверка условий

Форматы пользовательских номеров также допускают условия, которые записываются в квадратных скобках, таких как [> 100] или [<= 100]. Когда вы используете условные обозначения в пользовательских числовых форматах, вы переопределяете стандартную структуру >0, <0, 0, текст. Например, чтобы отображать значения ниже 100 красным цветом, вы можете использовать:

[Красный][<100]0;0

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

[Красный][<100]0;[Синий][>=100]0

Если оставить <100 и >100 (без равно), тогда в ячейке с числом 100 увидите ###########. Это значит, что Excel не может определить как отображать 100. Увеличение ширины столбца не исправит ситуации, нужно менять формат, добавлять >=

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

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

Применение форматов в формуле ТЕКСТ

Хотя большинство форматов чисел применяются непосредственно к ячейкам на листе, вы также можете применять форматы чисел внутри формулы с помощью функции ТЕКСТ. Например, в ячейке A1 написана формула СЕГОДНЯ(). Ниже два варианта получения названия месяца.

• в B2 с помощью формата
• в B4 с помощью формулы ТЕКСТ(A1;»ММММ») (м — вводим на русском ЗАГЛАВНЫМИ)

ВАЖНО: результатом функции ТЕКСТ всегда является текстовое значение, поэтому вы можете соединять результат формулы с другими текстовыми значениями: =«Отчёт продаж за :» & ТЕКСТ(A1; «ММММ»)

Примеры с сайтов

https://excel2. ru/articles/polzovatelskiy-chislovoy-format-v-ms-excel-cherez-format-yacheek

А какие вы используете нестандартные форматы?

Какие испытываете сложности в их создании?

Тип и формат данных в Excel и Google таблицах. Cоздаем свой формат

Введение

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

Тип данных – характеризует информацию, хранящуюся в ячейке.

Формат данных – отвечает за отображение символов в ячейке.

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

Скачать файлы из этой статьи

Рабочие файлы

Обзорное видео о работе с форматами данных в Excel и Google таблицах смотрите ниже. Приятного просмотра!

Тип данных в Excel и Google таблицах

Основными типами хранения данных в Excel и Google таблицах являются: текст и число.

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

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

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

Формат данных в Excel и Google таблицах

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

На картинке ниже отчетливо видна разница понятий «Тип данных» и «Формат данных».

Текстовый формат данных в Excel

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

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

Текстовый формат данных в Google таблицах

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

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

Удалили лишние пробелы и псевдо-числа преобразовались в полноценные.

Числовой формат данных

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

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

Стандартные форматы данных в Excel

Excel поддерживает следующие форматы:

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

Числовой – расширенный способ представления чисел. Можно задать количество знаков после запятой или добавить разделитель групп разрядов.

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

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

Формат Дата – используется для ввода даты или даты и времени.

Формат Время – предназначен для ввода времени или даты и времени.

При использовании процентного формата значение ячейки, умножается на 100 с символом % в конце.

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

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

Текстовые данные отображаются точно так же, как вводятся и обрабатываются, вне зависимости от их содержания.

Дополнительный формат включает в себя несколько видов данных: почтовый индекс, номер телефона и табельный номер.

Стандартный формат данных в Google таблицах

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

Главное о формате данных в Excel и Google таблицах

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

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

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

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

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

Большинство числовых форматов применяются непосредственно к ячейке, однако Вы можете также применять числовые форматы внутри формулы с помощью функции ТЕКСТ().

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

Символ, какую опцию он выполняет, примеры «ДО» и «ПОСЛЕ» и какой формат был применен к ячейке.

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

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

Больше цветов доступно в цветовой кодировке (см.ниже). Для этого, вместо названия цвета, указываем его номер, например [Цвет1], [Цвет2], [Цвет3].

Пользовательский числовой формат в Excel: [ЦветX] 0″ ◉» , где X — число между 1-56:

Пользовательский формат данных в Google таблицах

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

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

Примеры пользовательских форматов данных в Google таблицах

Основные правила создания пользовательского формата в Google таблицах

Цветовая кодировка для пользовательского формата в Google таблицах

Заключение

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

Изучите работу в программе Excel на наших курсах: бесплатные онлайн-курсы по Excel

Пройдите бесплатный тест на нашем сайте, чтобы объективно оценить свой уровень владения инструментами и функциями программы Excel: пройти тест

У нас Вы можете заказать выполнение задач по MS Excel и Google таблицам

Формат ячеек в Excel — javatpoint

следующий → ← предыдущая MS Excel, сокращение от Microsoft Excel, относится к мощному программному обеспечению для работы с электронными таблицами, разработанному Microsoft, чтобы помочь пользователям записывать данные в ячейках на нескольких рабочих листах. По умолчанию структура и внешний вид рабочего листа являются базовыми, и каждая ячейка имеет одни и те же настройки. Однако Excel предлагает различные варианты форматирования ячеек по нашему выбору. Хотя в большинстве случаев это необязательный шаг, он может помочь нам сделать наш рабочий лист эффективным и продуктивным разными способами. В этой статье обсуждается краткое введение в формат ячеек в Excel и то, как мы можем использовать его преимущества на нашем листе Excel. Каждая ячейка может быть отформатирована или изменена с совершенно другим внешним видом по желанию пользователя. Что такое формат ячеек в Excel? Функция Excel Format Cells позволяет пользователям настраивать или изменять форматирование одной или нескольких ячеек и/или внешний вид их значений на листе, но без изменения самих чисел. Ячейки формата предоставляют различные параметры управления, которые позволяют пользователям изменять представление отображаемых данных в ячейках. Мы можем использовать формат ячеек для изменения стиля даты и времени, добавления/удаления цветов шрифта или фона, вставки границы определенного стиля, защиты ячеек и многого другого. Используя форматирование Excel, мы обычно добавляем завершающие или последние штрихи, чтобы подготовить и представить данные точно. Например, предположим, что у нас есть рабочий лист с очень большими данными, включая некоторые даты и цены. Мы можем добавить знаки валюты (например, $) в ячейки, содержащие цены, и настроить формат дат для представления стандартных дат (xx/xx/xxxx). Таким образом, форматирование ячеек помогает нам привлечь внимание к определенным данным и выделить важное содержимое. Однако в окне «Формат ячеек» есть различные форматы. Доступ к ячейкам формата Существует несколько вариантов доступа или открытия диалогового окна «Формат ячеек» в Excel. К таким параметрам относятся лента, контекстное меню (меню, вызываемое правой кнопкой мыши) и сочетания клавиш. Использование ленты : Лента — это централизованная область, расположенная в верхней части окна Excel. Он состоит из большинства инструментов и функций Excel. При открытии диалогового окна «Формат ячеек» нам нужно перейти на вкладку Home и нажать кнопку 9.0025 Еще значок в правом нижнем углу группы Форматирование чисел. Использование контекстного меню : Контекстное меню относится к контекстному меню. Мы можем нажать , щелкнуть правой кнопкой мыши по нужной ячейке Excel с помощью мыши и выбрать параметр Формат ячеек , чтобы открыть диалоговое окно «Формат ячеек». Использование сочетания клавиш : сочетание клавиш — это самый быстрый способ доступа к диалоговому окну «Формат ячеек» в Excel. Мы можем использовать ярлык Ctrl + 1 ‘ внутри окна Excel, чтобы открыть диалоговое окно «Формат ячеек». Однако мы должны нажать клавишу «1» из числового ряда в области клавиатуры, а не с цифровой клавиатуры. Каковы элементы окна «Формат ячеек»? Элементы окна «Формат ячеек» относятся к вкладкам в его диалоговом окне. Как правило, окно «Формат ячеек» или диалоговое окно «Формат ячеек» имеет шесть вкладок, которые обсуждаются ниже: Номер вкладки На вкладке «Число» представлены различные форматы для изменения внешнего вида или форматирования числовых значений в ячейках. Обычно это изменяет способ отображения чисел в ячейках без изменения точных значений. Ниже приведены доступные или существующие числовые форматы в Excel: Общий В Excel Общий является стилем формата по умолчанию. Всякий раз, когда мы создаем новый лист Excel, каждая ячейка рабочего листа соответствует общему формату. В соответствии с этим форматом Excel автоматически идентифицирует значения, введенные в соответствующие ячейки, и отображает их, угадывая наиболее подходящий формат. Например, предположим, что у нас есть ячейка Excel с форматированием «Общее». Если мы введем любое число, например 2-3 в ячейке Excel считывает значение как дату и преобразует его в короткий формат даты, например 02-Mar . Если мы выберем конкретную ячейку и проверим строку формул, Excel отобразит полную дату в формате 02-03-текущий год (т.е. 2022). Если мы не хотим, чтобы Excel преобразовывал введенное значение в дату, мы должны указать другой формат, необходимый для отображения значения. Номер Как следует из названия, ‘Номер 9Формат 0026’ отображает введенные значения в виде числовых значений. Он также добавляет десятичные дроби с заданными значениями, даже если мы не вводим их вручную. Например, предположим, что мы установили ячейку Excel с форматированием «Число». Если мы введем в ячейку любое число, например 23 , Excel прочитает значение как числовое значение и изменит свое представление, добавив десятичные дроби, такие как 23,00 . В частности, формат «Число» устанавливает ячейку (ячейки) для отображения чисел в общей форме. Валюта Формат Валюта — это специальный формат Excel, используемый специально для значений валюты, таких как цены в ячейках. Формат Валюта добавляет знак валюты (например, $, €, ? и т. д.) перед значениями, введенными в соответствующие ячейки. По умолчанию Excel добавляет знак валюты в соответствии с выбранным географическим регионом; однако его можно изменить соответствующим образом. Например, предположим, что мы установили ячейку Excel с форматированием «Валюта». Если мы введем любое число, например 23 в ячейке Excel считывает значение как валюту и меняет представление, добавляя знак валюты, например $23,00 . Формат Валюта чаще всего используется для представления общих денежных значений в ячейках Excel. Бухгалтерия Формат Excel Бухгалтерский учет аналогичен формату валюты. Этот конкретный формат также используется для отображения введенных значений в денежной форме. Но он немного отличается от формата валюты тем, что он выравнивает или выравнивает знак валюты и десятичные дроби в соответствующих столбцах. Используя формат учета, мы можем облегчить зрителям чтение или понимание огромного списка значений валюты. На изображении выше символ валюты выровнен по левому краю из-за формата учета, а формат валюты добавляет его перед соответствующим значением без какого-либо выравнивания. Дата Формат Date используется для представления заданных или введенных чисел в виде дат. Обычно мы используем короткая дата и длинная дата форматы в Excel. Короткий формат даты представляет данные числа как ДД-ММ-ГГГГ , тогда как длинный формат даты представляет то же самое, что и ДД МЕСЯЦ ГГГГ . В Excel мы можем выбирать между различными форматами даты в диалоговом окне «Формат ячеек». Мы только меняем представление, используя разные форматы даты, но не сами даты. Время Формат Time используется для представления введенных значений или чисел в виде времени. Обычно он отображает числа в виде HH/MM/SS , где HH означает часы, MM означает минуты, а SS означает секунды. Кроме того, к формату времени можно добавить AM (до полудня) или PM (после полудня) в зависимости от выбранного формата даты, например 12-часового или 24-часового формата. Мы можем выбирать между различными форматами времени в диалоговом окне «Формат ячеек». Например, 13:30, 13:30 и т. д. Процент Формат Percentage помогает отображать введенные числа в процентах с десятичными разрядами. Этот конкретный формат добавляет знак процента (%) в конце заданного значения в ячейке. Кроме того, десятичные разряды автоматически добавляются со значениями. По умолчанию десятичные дроби складываются до двух цифр. Тем не менее, мы можем изменить его соответствующим образом. Например, предположим, что мы установили ячейку Excel с форматированием «Проценты». Если мы введем в ячейку любое число, например 0,23 , Excel считывает значение в процентах и ​​меняет свое представление, добавляя знак процента и десятичные дроби, такие как 23,00% . Дробь Когда пользователь вводит любую дробь в ячейку Excel, значение автоматически изменяется на даты или десятичные дроби. Excel автоматически корректирует введенное значение, угадывая наиболее подходящий формат ячейки. Использование 9Формат дроби 0025 может помешать Excel автоматически изменять дробь в нужных ячейках. Формат дроби использует косую черту при отображении чисел в ячейках. Например, когда мы вводим любую дробь, например 2/3, в ячейку Excel, она изменяется на 02 марта. Но если мы установим эту конкретную ячейку как дробь, введенное число не изменится и будет отображаться в том виде, в котором оно было введено, т. Е. 2/3 . Научный 9Формат 0025 Scientific позволяет пользователям устанавливать нужные ячейки Excel в качестве ссылки на научную запись, что означает экспоненциальную форму. Когда пользователь вводит слишком большое число, Excel автоматически преобразует соответствующее число или ячейку в экспоненциальное представление. Например, предположим, что мы установили ячейку Excel с форматированием «Научный». Если мы введем в ячейку любое число, например 1 50 000 , Excel прочитает значение как большое целое число и изменит свое представление, преобразовав его в экспоненциальное представление, например 9.0025 1.50Е+05. Чтобы запретить Excel автоматически преобразовывать большие целые числа в научный формат, мы можем установить ячейку как «Число» или другой желаемый формат. Текст Формат Text помогает пользователям установить желаемую ячейку (ячейки) только как текст. Он сохраняет введенные значения в формате обычного текста. Независимо от того, что мы вводим в ячейки, они будут отображать те же значения, что и были введены. Excel автоматически использует текстовый формат, когда пользователь вводит как текст, так и числа в ячейку Excel. Текстовый формат не участвует в вычислениях при использовании формул или функций для соответствующих ячеек. Например, предположим, что мы установили две ячейки Excel (B3, B4) в формате «Текст». Вводим числа в обе ячейки. Когда мы пытаемся сложить числа обеих ячеек в другую ячейку (B5), это не дает ожидаемого результата. Это связано с тем, что числа отображаются как числовые значения, но они являются текстовыми значениями, поскольку соответствующие ячейки отформатированы как текст. Сумма не видна на изображении выше, так как текстовые значения не могут быть сложены вместе. Специальный Специальный формат представляет введенные значения или числа в специальном формате. Этот числовой формат в основном используется для почтовых индексов, дополнительных четырехзначных почтовых индексов, телефонных номеров и номеров социального страхования. Специальный формат помогает легко отслеживать списки и значения базы данных. Пользовательский Хотя в Excel есть много предопределенных числовых форматов, могут быть шансы, что нам может понадобиться использовать определенный формат, которого нет в Excel. Мы можем воспользоваться Пользовательский Числовой формат Excel в таком случае. Используя Пользовательский формат, мы можем создать любой желаемый числовой формат для выбранных ячеек. Чтобы создать собственный формат, мы должны указать код формата, используя соответствующую структуру. Excel также отображает существующие коды форматов, которые можно использовать в качестве отправной точки для пользовательского числового формата. Вкладка выравнивания На вкладке «Выравнивание» представлены различные параметры форматирования для выравнивания значений ячеек на листе. Обычно это изменяет способ выравнивания значений в ячейках без изменения точных значений. Используя эту вкладку, мы обычно можем выбирать между горизонтальным или вертикальным выравниванием, направлением текста, ориентацией и некоторыми элементами управления текстом. Ниже перечислены доступные или существующие параметры/настройки вместе с их деталями, к которым мы можем получить доступ из окна «Формат ячеек» в Excel: Выравнивание текста : Используя раздел выравнивания текста, мы можем выбрать выравнивание содержимого ячейки по горизонтальной и/или вертикальной оси. Раскрывающийся список горизонтального выравнивания дает доступ к таким выравниваниям, как левое, правое, центральное, заливка, выравнивание и т. д. Кроме того, раскрывающийся список вертикального выравнивания дает доступ к таким выравниваниям, как верхнее, центральное, нижнее, выравнивание и т. д. используйте поле «Отступ», чтобы увеличить/уменьшить отступ между текстом и границей ячейки. Управление текстом : Используя раздел управления текстом, мы можем выбрать такие параметры, как перенос текста, сжатие по размеру и объединение ячеек. Опция «обтекание текстом» помогает сделать содержимое ячейки видимым, отображая его на нескольких строках. Параметр «объединить ячейки» помогает объединить несколько ячеек в одну большую ячейку. Параметр «Уменьшить текст» помогает сделать содержимое ячейки видимым, изменив его размер внутри ячейки. Направление текста : Используя раздел направления текста, мы можем выбрать направление текста между контекстом, слева направо и справа налево. Ориентация : Используя раздел ориентации, мы можем выбрать поворот содержимого ячейки под любым желаемым углом. Мы можем определить или ввести желаемый угол поворота в поле перед Градусами. Вкладка «Шрифт» На вкладке «Шрифт» представлены различные параметры форматирования, которые помогают настроить шрифты для значений ячеек на листе. Обычно это изменяет способ отображения шрифтов в ячейках без изменения точных значений. Используя эту вкладку, мы обычно можем изменить внешний вид, например стиль шрифта, размер, цвет и т. д. Ниже приведены доступные или существующие параметры/настройки для изменения шрифтов, к которым мы можем получить доступ из окна «Формат ячеек» в Excel: Шрифт : Используя раздел «Шрифты», мы можем выбирать между различными дизайнами установленных шрифтов, которые будут применяться к выбранным ячейкам Excel. Стиль шрифта : Используя раздел «Стиль шрифта», мы можем выбрать другой стиль, например полужирный, курсив и т. д. Размер : Используя раздел «Размер», мы можем выбрать размер шрифта, который будет отображаться в выбранных ячейках Excel. Если мы не находим подходящий размер шрифта (например, 13) в списке, мы можем ввести нужный размер в поле и нажать клавишу Enter. Подчеркивание : Используя раздел Подчеркивание, мы можем добавить подчеркивание под шрифтами или текстами внутри ячеек. В раскрывающемся списке перечислены дополнительные настройки подчеркивания, такие как одна строка, двойная строка и т. д. Цвет : Используя раздел «Цвет», мы можем применить цвета к шрифтам в выбранных ячейках. Мы можем выбрать существующие цвета или выбрать желаемый цвет. Эффекты : Используя раздел «Эффекты», мы можем выбрать эффекты шрифта между зачеркнутым, надстрочным и нижним индексом. Предварительный просмотр : В разделе предварительного просмотра отображаются изменения, выбранные для шрифтов. Вкладка «Граница» На вкладке «Граница» представлены различные параметры форматирования, которые помогают добавить или удалить границу с одной или нескольких сторон ячейки на листе. Раздел также позволяет нам выбрать стиль и цвет линии границы. Ниже приведены доступные или существующие параметры/настройки для настройки границ, к которым мы можем получить доступ из окна «Формат ячеек» в Excel: Стиль : Используя раздел «Стиль», мы можем выбирать между различными стилями линий для использования в качестве границ нужных ячеек. Он отображает множество стилей границ, таких как пунктирные, пунктирные, полужирные и т. д. Цвет : Используя раздел «Цвета», мы можем выбирать между различными существующими цветами, используемыми для границ. Предустановки : Используя раздел «Предустановки», мы можем выбрать одну из трех предопределенных комбинаций границ, таких как «Нет» (без границ), «Контур» (границы со всех сторон) и «Внутри» (границы в соединительных линиях сетки нескольких ячеек). Граница : Используя раздел «Граница», мы можем добавить границу с любой конкретной стороны, например, сверху, снизу, слева, справа, по диагонали и т. д. Вкладка для заполнения На вкладке «Заливка» представлены различные параметры форматирования для заливки цветом фоновой области ячеек на листе. В разделе представлены существующие цвета, которые мы можем выбрать. Кроме того, мы можем выбрать индивидуальные цвета. Excel также имеет некоторые настройки для добавления узоров и эффектов к фону ячеек. Ниже приведены доступные или существующие параметры/настройки для настройки цветов, к которым мы можем получить доступ из окна «Формат ячеек» в Excel: Цвет фона : Используя раздел «Цвет фона», мы можем выбрать желаемый цвет, который будет применяться в качестве фона ячейки для выбранных ячеек на листе. Эффекты заливки : Используя раздел «Эффекты заливки», мы можем выбрать цветовой градиент или эффект затенения и направление эффекта. Больше цветов : Используя раздел «Дополнительные цвета», мы можем создать любой собственный цвет, который будет использоваться в качестве фона ячейки. Цвет узора : Используя раздел «Цвета узора», мы можем добавить дополнительный цвет узора для выбранного стиля узора, используемого в ячейке в качестве заливки или фона. Стиль узора : Используя раздел «Стиль узора», мы можем выбирать между различными предопределенными узорами, которые будут использоваться в дополнение к цвету фона ячейки. Защитный выступ Вкладка «Защита» предоставляет два специальных параметра, а именно Заблокировано и Скрыто . Оба варианта не действуют, пока мы не защитим рабочий лист. Если мы выберем параметр «Заблокировано» на вкладке «Защита», Excel запретит нам вносить следующие изменения в выбранные ячейки: Ввод данных в пустую ячейку. Изменение данных ячейки или формул. Изменение размера ячейки. Перемещение ячейки. Удаление ячейки или ее содержимого. Если мы выберем параметр «Скрытый» на вкладке «Защита», Excel скроет формулы для соответствующих ячеек из строки формул. Однако результаты будут одинаковыми. Как отформатировать ячейку в Excel? Сначала нам нужно выделить ячейки на листе. Далее нам нужно перейти в окно «Формат ячеек» и настроить параметры на соответствующих вкладках, таких как «Число», «Выравнивание», «Шрифт», «Граница», «Заливка» и «Защита». После настройки параметров мы должны нажать кнопку «ОК», чтобы применить изменения на листе. Важные моменты, которые следует помнить Условное форматирование — еще один мощный инструмент Excel, который помогает пользователям форматировать определенные ячейки на основе различных условий. Однако его нет в диалоговом окне «Формат ячеек». Вместо этого он помещается на вкладку «Главная» в группе «Стиль». Средство рисования формата Excel помогает пользователям копировать форматирование одной ячейки в другую. Доступ к нему можно получить на вкладке «Главная» в группе «Буфер обмена». Помимо сочетания клавиш «Ctrl + 1», мы также можем использовать комбинацию клавиш «Alt + H + FM», чтобы мгновенно запустить диалоговое окно «Формат ячеек». Следующая темаМенеджер правил Excel ← предыдущая следующий →

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

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

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

Использование пользовательского форматирования в Excel

В Excel много встроенных числовых форматов, но что, если нужный вам формат не включен во встроенные форматы?

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

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

Символ	Функция
#	. Опциональный номер. Если пользовательский формат равен #, то в ячейке будет отображаться любое число в ней.
.0	Десятичная точка. Десятичные точки определяются количеством нулей, которые вы ставите после точки. Если пользовательский формат — #.00, а значение ячейки — 1,5, ячейка будет отображать 1,50.
,	Разделитель тысяч. Если пользовательский формат — #,### , а значение ячейки — 5000, в ячейке будет отображаться 5000.
\	Отображает следующий за ним символ. Если пользовательский формат — #\M , а значение ячейки — 500, ячейка будет отображать 500M.
@	Текстовый заполнитель. Если пользовательский формат — @[Red] , а значение ячейки — MUO, ячейка будет отображать MUO красным цветом.
» »	Отображает пользовательский текст. Если пользовательский формат — # «Годы» , а значение ячейки равно 5, в ячейке будет отображаться 5 лет.
%	Умножает число на 100 и показывает его в процентах. Если пользовательский формат — #% , а значение ячейки — 0,05, то в ячейке будет отображаться 5%
[ ]	Создает условие. Если пользовательский формат [>10]#;; и значение ячейки меньше 10, ячейка будет пустой.

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

 "Положительно"; «Отрицательный»; «Ноль» 

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

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

1. Пример пользовательского форматирования Excel: текстовые суффиксы

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

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

1. На вкладке Домашний перейдите в раздел Номер и нажмите Общие .
2. В меню выберите Дополнительные числовые форматы . Это откроет новое окно.
3. В новом окне под Категория , выберите Пользовательский .
4. Выберите любой из форматов.
5. Введите строку ниже под Введите :

    # "Часы" 

6. Нажмите OK .

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

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

2. Пример пользовательского форматирования Excel: разделитель десятичных и тысячных разрядов

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

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

1. Во вкладке Домашний перейдите в раздел Номер и нажмите Общие .
2. В меню выберите Дополнительные числовые форматы . Это откроет новое окно.
3. В новом окне в разделе Категория выберите Пользовательский .
4. Выберите формат, созданный в предыдущем разделе.
5. Введите строку ниже под Введите :

    ###,###.0 «Часы»; "Значение отрицательное!"; "-" 

6. Нажмите OK .

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

Пользовательские форматы с текстовыми строками позволяют создавать новые объекты в Excel. Например, вы можете использовать пользовательское форматирование для создания маркированного списка в Excel.

3. Пример пользовательского форматирования Excel: цвета и условия

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

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

Как упоминалось ранее, вы можно создавать собственные условия внутри квадратных скобок ( [ ] ) и использовать скобки для указания цвета шрифта. Давайте приступим к делу. Имея это в виду, давайте создадим собственный формат с условиями.

1. В новом окне в разделе Категория выберите Пользовательский .
2. Выберите любой из форматов.
3. Введите строку ниже под Введите :

    [>=10][Зеленый]"Пройдено";[<10][Красный]"Не пройдено" 

4. Нажмите OK .

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

С помощью пользовательского форматирования можно сохранить значения ячеек без изменений и отображать текст на основе значений ячеек.