Маски форматов
Формат чисел
Формат данных
Каждое число в таблице можно представить в разных форматах (с различным количеством десятичных позиций, незначащих нулей и пр.). Для изменения формата содержимого ячейки необходимо:
¯ выбрать команду Ячейки меню Формат;
¯ в диалоговом окне Формат ячеек выбрать вкладыш Число (рис.21);
¯ в списке Числовые форматы выбрать тип формата содержимого ячейки, а в полях справа – параметры формата;
¯ в поле Образец будет отображаться пример содержимого ячейки в выбранном формате;
¯ чтобы ввести новый формат, следует выбрать пункт все форматы, а затем в поле Тип ввести новый формат;
Маски форматов чисел позволяют управлять отображением значений в ячейках. Значение масок следующее.
Основной – числа выводятся в том формате, в котором они были введены.
# – если число имеет справа от десятичной запятой большее количество цифр, чем указано знаков # в правой части формата, то число округляется до указанного количества десятичных знаков после запятой. Если число имеет слева от запятой большее количество десятичных знаков, чем знаков # в левой части формата, то выводится большее количество цифр.
0 (нуль) – тоже что и #, но если в числе меньшее количество цифр, чем указано знаков 0 в шаблоне, выводятся отсутствующие нули.
? – тоже что и знак 0, за исключением того, что Microsoft Excel выводит пробел вместо незначащих нулей с обеих сторон от десятичной запятой, таким образом, что число оказывается выровненным по десятичной запятой. Можно также использовать этот символ для рациональных дробей, которые имеют различное число десятичных знаков.
, (запятая) – этот символ определяет, сколько цифр (соответственно количеству символов 0 и #) выводится справа и слева от десятичной запятой. Можно также использовать запятую в шаблонах времени дня для того, чтобы создать форматы времени дня, отображающие части секунды.
%% – число умножается на 100 и добавляется знак %.
(пробел) – разделитель тысяч. Тысячи разделяются пробелами, если шаблон содержит пробелы, окруженные с обеих сторон знаками # или 0. Пробел, который идет вслед за знаком шаблона, масштабирует число, округляя его до тысяч. Шаблон, в котором за символами 0,0 следуют два пробела, будет отображать число
43 500 000 как 43,5.
E+00 – экспоненциальный формат числа.
грн. – + / ( ) : – отображаются эти символы.
\ – отображается следующий за ним символ формата. Сам символ «обратная косая черта» не отображается. Действует так же, как если бы следующий за ним символ был заключен в двойные кавычки.
Таблица 1. Примеры использования масок
| Введенное число | Маска | Отображение числа |
| 76, 364 | 000, 000 | 076, 364 |
| 44, 894 | ###, ### | 044, 894 |
| 7636, 4 | ####, # | 7636, 4 |
| 781,7 | $###, | $782, |
| 7,6 | #,000 | 7,600 |
| 2,4 | 000,# | 002,4 |
| 3,25 | #. /. | 13/4 |
| 0,8 | ?,# | ,8 |
| 0,2 | 0,00 | 0,20 |
| 0,7 | #,00 | ,70 |
| 0,4 | ?,#% | 40% |
* – повторяет следующий за ним символ формата столько раз, сколько нужно, чтобы заполнить всю ширину столбца. В каждой секции формата не может быть больше одного символа » * «.
подчеркивание (_) – делает пробел шириной, соответствующей следующему символу шаблона. Например, для того, чтобы Microsoft Excel делал пробел на ширину символа скобок и положительные числа оказывались выровненными так же, как и отрицательные числа, заключенные в скобки, следует ввести _) в конце секции формата для положительных чисел.
«meкcm» – отображает любой текст, заключенный в двойные кавычки.
@ – если в ячейке есть текст, то этот текст отображается в том месте формата, где находится символ @.
Пользовательский ЧИСЛОвой формат в EXCEL (через Формат ячеек)
history 31 марта 2013 г.
Для отображения числа можно использовать множество форматов. Согласно российским региональным стандартам ( Кнопка Пуск/ Панель Управления/ Язык и региональные стандарты ) число принято отображать в следующем формате: 123 456 789,00 (разряды разделяются пробелами, дробная часть отделяется запятой). В EXCEL формат отображения числа в ячейке можно придумать самому. Для этого существует соответствующий механизм – пользовательский формат. Каждой ячейке можно установить определенный числовой формат. Например, число 123 456 789,00 имеет формат: # ##0,00;-# ##0,00;0
Рассмотрим для начала упомянутый выше стандартный числовой формат # ##0,00;-# ##0,00;0 В дальнейшем научимся его изменять.
Точки с запятой разделяют части формата: формат для положительных значений; для отрицательных значений; для нуля. Для описания формата используют специальные символы.
Третья часть формата – для отображения нуля. В принципе, вместо 0 можно указать любой символ или несколько символов (см. статью Отображение в MS EXCEL вместо 0 другого символа ).
Например, формат 0;\0;\0;\0 позволяет заменить все отрицательные, равные нулю и текстовые значения на 0. Все положительные числа будут отображены как целые числа (с обычным округлением).
В создаваемый числовой формат необязательно включать все части формата (раздела). Если заданы только два раздела, первый из них используется для положительных чисел и нулей, а второй — для отрицательных чисел. Если задан только один раздел, этот формат будут иметь все числа. Если требуется пропустить какой-либо раздел кода и использовать следующий за ним раздел, в коде необходимо оставить точку с запятой, которой завершается пропускаемый раздел.
Рассмотрим пользовательские форматы на конкретных примерах.
Применение пользовательских форматов
Создание пользовательских форматов
Excel позволяет создать свой (пользовательский) формат ячейки. Многие знают об этом, но очень редко пользуются из-за кажущейся сложности. Однако это достаточно просто, главное понять основной принцип задания формата.
Для того, чтобы создать пользовательский формат необходимо открыть диалоговое окно Формат ячеек и перейти на вкладку Число. Можно также воспользоваться сочетанием клавиш Ctrl + 1.
В поле Тип вводится пользовательские форматы, варианты написания которых мы рассмотрим далее.
В поле Тип вы можете задать формат значения ячейки следующей строкой:
[цвет]»любой текст»КодФормата»любой текст»
Какие цвета можно применять
В квадратных скобках можно указывать один из 8 цветов на выбор:
Синий, зеленый, красный, фиолетовый, желтый, белый, черный и голубой.
Далее рассмотрим коды форматов в зависимости от типа данных.
Числовые форматы
| Символ | Описание применения | Пример формата | До форматирования | После форматирования |
|---|---|---|---|---|
| # | Символ числа. Незначащие нули в начале или конце число не отображаются | ###### | 001234 | 1234 |
| 0 | Символ числа. Обязательное отображение незначащих нулей | 000000 | 1234 | 001234 |
| , | Используется в качестве разделителя целой и дробной части | ####,# | 1234,12 | 1234,1 |
| пробел | Используется в качестве разделителя разрядов | # ###,#0 | 1234,1 | 1 234,10 |
Форматы даты
Стоит обратить внимание, что форматы даты можно комбинировать между собой. Например, формат «ДД.ММ.ГГГГ» отформатирует дату в привычный нам вид 31.12.2017, а формат «ДД МММ» преобразует дату в вид 31 Дек.
Форматы времени
Аналогичные форматы есть и для времени.
Текстовые форматы
Текстовый форматов как таковых не существует. Иногда требуется продублировать значение в ячейке и дописать в начало и конец дополнительный текст. Для этих целей используют символ @.
Создание пользовательских форматов для категорий значений
Все что мы описали выше применяется к ячейке вне зависимости от ее значения. Однако существует возможность указывать различные форматы, в зависимости от следующих категорий значений:
Для этого мы можем в поле Тип указать следующую конструкцию:
Формат положительных значений ; отрицательных ; нулевых ; текстовых
Соответственно для каждой категории можно применять формат уже описанного нами вида:
[цвет]»любой текст»КодФормата»любой текст»
В итоге конечно может получится длинная строка с форматом, но если приглядеться подробнее, то сложностей никаких нет.
Смотрите какой эффект это дает. В зависимости от значения, меняется форматирование, а если вместо числа указано текстовое значения, то Excel выдает «нет данных».
Редактирование и копирование пользовательских форматов
Чтобы отредактировать созданный пользовательский формат необходимо:
Распространить созданный пользовательский формат на другие ячейки можно следующими способами:
Для удаления установленного формата ячейки, можно просто задать другой формат или удалить созданный из списка:
Microsoft Excel
трюки • приёмы • решения
Коды пользовательских форматов чисел
В табл. 49.1 кратко описаны коды форматирования, доступные для различных пользовательских (настраиваемых) форматов.
Таблица 49.1. Коды, используемые для создания пользовательских числовых форматов
| Код | Что он делает |
|---|---|
| Основной | Отображает число в общем формате |
| # | Служит в качестве числового заполнителя, который отображает значащие цифры, но не отображает незначащие нули |
| 0 (нуль) | Служит в качестве числового заполнителя, который показывает незначащие нули, если в числе меньше цифр, чем нулей в формате |
| ? | Служит в качестве числового заполнителя, который добавляет пробелы для незначащих нулей на каждой стороне от десятичной точки так, чтобы десятичная точка выравнивалась при форматировании шрифтом фиксированной ширины; также используется для дробей с различным количеством цифр |
| . (точка) | Отображает десятичную точку |
| % | Выводит проценты |
| , (запятая) | Отображает разделитель тысячных |
| Е- Е+ е- е+ | Показывает экспоненциальное значение |
| $ — + / () : пробел | Отображает указанный символ |
| Выводит следующий символ в формате | |
| * (звездочка) | Повторяет следующий символ так, чтобы оказалась занятой вся ширина столбца |
| _ (подчеркивание) | Оставляет пространство, равное ширине следующего символа |
| «текст» | Отображает текст внутри двойных кавычек |
| @ | Служит в качестве текстового заполнителя |
| [цвет] | Отображает символы в заданном цвете и может быть любой из следующих текстовых строк (без учета регистра): Черный, Синий, Голубой, Зеленый, Пурпурный, Красный, Белый или Желтый |
| [ЦВЕТ n] | Отображает соответствующий цвет в цветовой палитре, где n — число от 0 до 56 |
| [условное значение] | Позволяет вам устанавливать собственные критерии для каждого раздела числового формата |
В табл. 49.2 представлены коды, используемые для создания форматов даты и времени.
Тест с ответами на тему: EXCEL (эксель)
2) Файл с расширением XLS содержит
3) При выделении одного фрагмента на рабочем листе, выделяется
4) Можно ли выделить на рабочем листе несколько фрагментов?
5) Для выделения мышкой нескольких областей следует прижать клавишу
6) Можно ли форматировать числа и текст, находящиеся на рабочем листе?
7) Вставка новой панели инструментов осуществляется выбором в главном меню:
Можно ли изменить имя рабочего листа и названия рабочей книги?
9) Сколько чисел можно записать в одной ячейке?
10) Можно ли в одной программе EXCEL открыть
11) В какой строке окна программы находятся кнопки, относящиеся к окну программы Свернуть, Развернуть/Восстановить, Закрыть
12) В какой строке какого окна находятся кнопки, относящиеся к окну документа Свернуть, Развернуть/Восстановить, Закрыть, если это окно было развернуто (была нажата кнопка Развернуть)
13) Формулы для расчетов вводятся
14) Можно ли сохранить в файле на диске отдельно созданную таблицу, не сохраняя при этом всю рабочую книгу?
15) Как можно изменить ширину столбца?
16) Как можно изменить высоту строки?
17) Можно ли сделать строку невидимой
18) Можно ли сделать невидимыми один или несколько столбцов
19) Имена каких строк и столбцов при копировании формулы =$A23+C$21 не будут меняться:
20) Имена каких строк и столбцов при копировании формулы =$F15+K$44 будут меняться:
21) Какая из формул выводит дату следующего дня
22) Какая из формул содержит абсолютную ссылку
23) В каком случае имя адресуемого листа рабочей книги заключено в одинарные кавычки:
='[Книга2]Финансовый отчет’!$A$1+'[Книга2]Финансовый отчет’!$B$1
24) В ячейке C4 формула =B4/B2. Как она будет выглядеть, если переместить ее в ячейку C5?
25) Формула =B4/B2 копируется из ячейки C4 в ячейку C5. Каков результат в ячейке C5?
26) Для подтверждения ввода в ячейку нужно:
27) Содержимое активной ячейки отображено в:
28) В последовательные ячейки столбца таблицы Excel введены названия дней недели: «понедельник», «вторник», «среда». Активна последняя ячейка. списка. Мышь указывает на правый нижний угол ячейки списка, при этом ниже правого уголка ячейке виден знак «Плюс». Что произойдет, если «протянуть» мышь на пару ячеек вниз?
29) Каково число диапазонов, суммируемых в формуле: =СУММ(F2;F6:F15;$A$6:C13;H1:H5;J1;L1;N1)
30) Сколько фрагментов может храниться в буфере обмена Office-XP?
31) Какой символ нужно ввести перед последовательностью цифр, чтобы они интерпретировались как текст?
32) Какие из приведенных элементов характеризуют электронную таблицу:
33) Документ (файл) Excel это:
34) Столбцы электронной таблицы обычно обозначаются
35) Введенный в ячейку текст обычно автоматически выравнивается:
36) Строки электронной таблицы обычно обозначаются
37) В таблице выделены два столбца. Что произойдет при попытке изменить ширину столбца:
38) Формула в ячейке выглядела так: =СУММ(B2:C8) В рабочем листе таблицы был удален первый столбец и перед первой строкой вставлена новая строка. Какой вид приняла формула?
39) На принтере могут быть отпечатаны:
40) Чтобы выделить элемент диаграммы можно:
41) Содержимое ячейки: #########. В чем причина?
42) Может ли функция Excel не иметь аргументов?
43) Каково значение в ячейке C3?
44) Во время прокрутки листа Excel адрес (имя) активной ячейки меняется?
45) Во время прокрутки списка листов Excel меняется текущий лист?
46) В ячейку введен текст. Его длина превысила размер ячейки. Соседняя справа ячейка занята. Что будет отображено в ячейке с текстом?
47) В ячейку введен текст. Его длина превысила размер ячейки. Соседняя справа ячейка не занята.
Что будет отображено в ячейке с текстом?
48) В списке заголовки столбцов размещены во первой строке, первый столбец содержит наименования лекарств. Какую ячейку сделать активной перед выполнением команды «закрепить области», чтобы при прокрутке листа не исчезали заголовки и наименования?
49) Какие из приведенных ниже выражений удовлетворяют правилам построения формул Excel?
50) Для создания принудительного перехода текстового содержимого ячейки в другую строку той же ячейки следует использовать сочетание клавиш:
51) Какие элементы окна приложения специфичны (не используются в других приложениях) для Excel?
52) Верно ли, что каждый документ Excel создается на основе шаблона?
53) Можно ли на рабочем листе в одной таблице применить денежный формат и «в рублях» и «в долларах»?
54) Какой код маски числового формата позволяет заменять незначащие цифры пробелами?
55) К ячейкам A1:E1 применена операция «Объединить и выровнять по центру». Чтобы снять объединение ячеек, не изменяя формата текста нужно:
56) Можно ли менять формат шрифта текста колонтитулов?
57) В ячейки A1 и B2 введены числа 24 и 12 соответственно. В ячейку C1 введено: A1/B1/
Каков будет результат в ячейке С1?
58) В формулу нужно ввести ссылку на ячейку. Что для этого нужно сделать?
59) В электронной таблице нельзя удалить:
60) Какие из приведенных ниже выражений могут являться формулами Excel?
61) MS Excel выводит на экран адрес активной (выделенной) ячейки в
62) Строки таблицы Excel обычно обозначаются
63) Какие элементы окна приложения Excel специфичны (не используются в других окнах приложений MS Office)?
64) В ячейку введено число 0,70 и применен процентный формат. Каков будет результат, отображенный в ячейке?
65) Число в ячейке по умолчании выравнивается
66) Текст в ячейке по умолчании выравнивается
67) Какой результат отобразится в ячейке C4 при копировании в нее формулы Excel =A2*B$1 из ячейки B2?