В Excel имеется множество встроенных числовых форматов, но если ни один из них не удовлетворяет пользователя, то можно создать собственный числовой формат. Например, число -5,25 можно отобразить в виде дроби -5 1/4 или как (-)5,25 или 5,25- или, вообще в произвольном формате, например, ++(5)руб.###25коп. Рассмотрены также форматы денежных сумм, процентов и экспоненциального представления.
Для отображения числа можно использовать множество форматов. Согласно российским региональным стандартам ( Кнопка Пуск/ Панель Управления/ Язык и региональные стандарты ) число принято отображать в следующем формате: 123 456 789,00 (разряды разделяются пробелами, дробная часть отделяется запятой). В EXCEL формат отображения числа в ячейке можно придумать самому. Для этого существует соответствующий механизм – пользовательский формат. Каждой ячейке можно установить определенный числовой формат. Например, число 123 456 789,00 имеет формат: # ##0,00;-# ##0,00;0
Пользовательский числовой формат не влияет на вычисления, меняется лишь отображения числа в ячейке. Пользовательский формат можно ввести через диалоговое окно Формат ячеек , вкладка Число , ( все форматы ), нажав CTRL+1 . Сам формат вводите в поле Тип , предварительно все из него удалив.
Числа в текстовом формате в Excel
Рассмотрим для начала упомянутый выше стандартный числовой формат # ##0,00;-# ##0,00;0 В дальнейшем научимся его изменять.
Точки с запятой разделяют части формата: формат для положительных значений; для отрицательных значений; для нуля. Для описания формата используют специальные символы.
- Символ решетка (#) означает любую цифру.
- Символ пробела в конструкции # ##0 определяет разряд (пробел показывает, что в разряде 3 цифры). В принципе можно было написать # ###, но нуль нужен для отображения 0, когда целая часть равна нулю и есть только дробная. Без нуля (т.е. # ###) число 0,33 будет отражаться как ,33.
- Следующие 3 символа ,00 (запятая и 00) определяют, как будет отображаться дробная часть. При вводе 3,333 будут отображаться 3,33; при вводе 3,3 – 3,30. Естественно, на вычисления это не повлияет.
Вторая часть формата – для отображения отрицательных чисел. Т.е. можно настроить разные форматы для отражения положительных и отрицательных чисел. Например, при формате # ##0,00;-###0;0 число 123456,3 будет отображаться как 123 456,30, а число -123456,3 как -123456. Если формата убрать минус, то отрицательные числа будут отображаться БЕЗ МИНУСА.
Третья часть формата – для отображения нуля. В принципе, вместо 0 можно указать любой символ или несколько символов (см. статью Отображение в MS EXCEL вместо 0 другого символа ).
Есть еще и 4 часть – она определяет вывод текста. Т.е. если в ячейку с форматом # ##0,00;-# ##0,00;0;»Вы ввели текст» ввести текстовое значение, то будет отображено Вы ввели текст .
Например, формат 0; ; ; позволяет заменить все отрицательные, равные нулю и текстовые значения на 0. Все положительные числа будут отображены как целые числа (с обычным округлением).
Пользовательские форматы чисел в Excel с нуля до профи за 40 минут!
В создаваемый числовой формат необязательно включать все части формата (раздела). Если заданы только два раздела, первый из них используется для положительных чисел и нулей, а второй — для отрицательных чисел. Если задан только один раздел, этот формат будут иметь все числа. Если требуется пропустить какой-либо раздел кода и использовать следующий за ним раздел, в коде необходимо оставить точку с запятой, которой завершается пропускаемый раздел.
Рассмотрим пользовательские форматы на конкретных примерах.
Значение в ячейке
Формат ячейки
Отображение
Примечание
Источник: excel2.ru
Стандартные форматы данных в Excel и Google таблицах
Довольно часто, пользователи путают понятия «тип данных» и «формат ячеек». Эти понятия взаимосвязаны, но все же разные.
Тип данных – характеризует информацию, хранящуюся в ячейке.
Формат данных – отвечает за отображение символов в ячейке.
В этой статье мы разберем подробнее разницу этих понятий, рассмотрим стандартные варианты форматирования данных, а также покажем, как создать собственный формат.
Скачать файлы из этой статьи
Обзорное видео о работе с форматами данных в 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 таблицам
- 21 июня 2021
- 9272
- MS Excel Video File Google Sheets
Источник: birdyx.ru
Применение пользовательских форматов
В Excel имеется большое количество встроенных форматов, однако бывают случаи, когда необходимо создать собственный формат для более наглядной визуализации данных.
Создание пользовательских форматов
Excel позволяет создать свой (пользовательский) формат ячейки. Многие знают об этом, но очень редко пользуются из-за кажущейся сложности. Однако это достаточно просто, главное понять основной принцип задания формата.
Для того, чтобы создать пользовательский формат необходимо открыть диалоговое окно Формат ячеек и перейти на вкладку Число. Можно также воспользоваться сочетанием клавиш Ctrl + 1.
В поле Тип вводится пользовательские форматы, варианты написания которых мы рассмотрим далее.
В поле Тип вы можете задать формат значения ячейки следующей строкой:
[цвет]»любой текст»КодФормата»любой текст»
Посмотрите простые примеры использования форматирования. В столбце А — значение без форматирования, в столбце B — с использованием пользовательского формата (применяемый формат в столбце С)
Какие цвета можно применять
В квадратных скобках можно указывать один из 8 цветов на выбор:
Синий, зеленый, красный, фиолетовый, желтый, белый, черный и голубой.
Далее рассмотрим коды форматов в зависимости от типа данных.
Числовые форматы
| # | Символ числа. Незначащие нули в начале или конце число не отображаются | ###### | 001234 | 1234 |
| Символ числа. Обязательное отображение незначащих нулей | 000000 | 1234 | 001234 | |
| , | Используется в качестве разделителя целой и дробной части | ####,# | 1234,12 | 1234,1 |
| пробел | Используется в качестве разделителя разрядов | # ###,#0 | 1234,1 | 1 234,10 |
Форматы даты
| М | Отображает числовое значение месяца | от 1 до 12 |
| ММ | Отображает числовое значение месяца в формате 00 | от 01 до 12 |
| МММ | Отображает сокращенное до 3-х букв значение месяца | от Янв до Дек |
| ММММ | Полное наименование месяца | Январь — Декабрь |
| МММММ | Отображает первую букву месяца | от Я до Д |
| Д | Выводит число даты | от 1 до 31 |
| ДД | Выводит число в формате 00 | от 01 до 31 |
| ДДД | Выводит день недели | от Пн до Вс |
| ДДДД | Выводит название недели целиком | Понедельник — Пятница |
| ГГ | Выводит последние 2 цифры года | от 00 до 99 |
| ГГГГ | Выводит год даты полностью | 1900 — 9999 |
Стоит обратить внимание, что форматы даты можно комбинировать между собой. Например, формат «ДД.ММ.ГГГГ» отформатирует дату в привычный нам вид 31.12.2017, а формат «ДД МММ» преобразует дату в вид 31 Дек.
Форматы времени
Аналогичные форматы есть и для времени.
| ч | Отображает часы | от 0 до 23 |
| чч | Отображает часы в формате 00 | от 00 до 23 |
| м | Отображает минуты | от 0 до 59 |
| мм | Минуты в формате 00 | от 00 до 59 |
| с | Секунды | от 0 до 59 |
| сс | Секунды в формате 00 | от 00 до 59 |
| [ч] | Формат истекшего времени в часах | например, [ч]:мм -> 30:15 |
| [мм] | Формат истекшего времени в минутах | например, [мм]:сс -> 65:20 |
| [сс] | Формат истекшего времени в секундах | — |
| AM/PM | Для вывода времени в 12-ти часовом формате | например, Ч AM/PM -> 3 PM |
| A/P | Для вывода времени в 12-ти часовом формате | например, чч:мм AM/PM -> 03:26 P |
| чч:мм:сс.00 | Для вывода времени с долями секунд |
Текстовые форматы
Создание пользовательских форматов для категорий значений
Все что мы описали выше применяется к ячейке вне зависимости от ее значения. Однако существует возможность указывать различные форматы, в зависимости от следующих категорий значений:
- Положительные числа
- Отрицательные числа
- Нулевые значения
- Текстовый формат
Для этого мы можем в поле Тип указать следующую конструкцию:
Формат положительных значений ; отрицательных ; нулевых ; текстовых
Соответственно для каждой категории можно применять формат уже описанного нами вида:
[цвет]»любой текст»КодФормата»любой текст»
В итоге конечно может получится длинная строка с форматом, но если приглядеться подробнее, то сложностей никаких нет.
Смотрите какой эффект это дает. В зависимости от значения, меняется форматирование, а если вместо числа указано текстовое значения, то Excel выдает «нет данных».
Редактирование и копирование пользовательских форматов
Чтобы отредактировать созданный пользовательский формат необходимо:
- Выделить ячейки, формат которых вы хотите отредактировать.
- Открыть диалоговое окно Формат ячеек и перейти на вкладку Число. Можно также воспользоваться сочетанием клавиш Ctrl + 1.
- Изменить строку форматирования в поле Тип.
Распространить созданный пользовательский формат на другие ячейки можно следующими способами:
- Использовать функцию копирования по образцу.
- Выделить ячейки, открыть окно Формат ячеек, на вкладке Число в списке Все форматы выбрать нужный формат и нажать ОК.
Для удаления установленного формата ячейки, можно просто задать другой формат или удалить созданный из списка:
Источник: micro-solution.ru