Финансовый и денежный форматы

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

Рис. 9. Установка числовых форматов

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

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

Процентный формат

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

Дробный формат

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

Рис. 10. Виды дробных форматов

 

Экспоненциальный формат

Экспоненциальный формат используется для отображения очень больших или очень маленьких чисел в экспоненциальной записи. Например, масса планеты Земля (5980000000000000000000000 кг) будет отображена как 5,98E+24, что означает 5,98 умноженное на 10²⁴ (десять в двадцать четвертой степени).

Текстовый формат

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

Дополнительные форматы

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

Личные форматы

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

Рис. 11. Создание личных форматов

 

Структура числового формата

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

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

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

0 (ноль) – метка цифры. Гарантирует отображение не менее заданного количества цифр в целой части числа и заданного количества цифр в десятичной части. Количество цифр задается числом нулей в шаблоне.

# – метка цифры. Гарантирует отображение не более заданного количества цифр в десятичной части. Если число имеет больше цифр в десятичной части, то отображаемое число округляется.

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

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

[Зеленый]# 00,00;[Синий]-0,000 положительные числа отображаются зеленым цветом ([Зеленый]), минимум два знака целой части (00), с разделением разрядов (#), не более двух знаков после запятой (,00).   Например: 10 000,00 или 00,10   отрицательные числа отображаются синим цветом ([Синий]), минимум один знак целой части (0), знак минус перед отрицательным числом (-), не более трех знаков после запятой (,000)   Например: -10000,000 или -0,100

Цвет шрифта

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

[Красный]Основной;[Зеленый]Основной устанавливает отображение положительных чисел и нулевых значений красным шрифтом, а отрицательных – зеленым.

Условия

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

В качестве операторов сравнения можно использовать знаки: = (равно), > (больше), < (меньше), >= (больше или равно), <= (меньше или равно), <> (не равно). Оператор сравнения и значение указываются в квадратных скобках.

[Синий][<=36,6]Основной;[Красный][>36,6]Основной устанавливает отображение чисел меньших или равных 36,6 синим шрифтом, а чисел более 36,6 – красным шрифтом.

Отображение дат и времени

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

 

Таблица 2 – Коды отображения даты и времени

Символ	Назначение
М	Месяцы в виде чисел от 1 до 12
ММ	Месяцы в виде чисел от 01 до 12
МММ	Месяцы в виде Янв
ММММ	Месяцы в виде Январь
МММММ	Месяцы первой буквой месяца
Д	Дни в виде чисел от 1 до 31
ДД	Дни в виде чисел от 01 до 31
ДДД	Дни в виде Вс
ДДДД	Дни в виде Воскресенье
ГГ	Года в виде 00 – 99
ГГГГ	Года в виде 1900 – 9999
ч	Часы в виде чисел от 0 до 23
чч	Часы в виде чисел от 00 до 23
М	Минуты в виде чисел от 0 до 59
ММ	Минуты в виде чисел от 00 до 59
С	Секунды в виде чисел от 0 до 59
СС	Секунды в виде чисел от 00 до 59

Текст

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

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

Текст может располагаться и перед числом и после числа, а также одновременно перед числом и после числа.

0,00_ "Прибыль";0,00_ "Убыток";0,00;Основной устанавливает отображение положительных чисел с текстом Прибыль, отрицательных – с текстом Убыток, а нулевых значений без текста

 

Текст можно добавлять и в форматы с условиями.

[<=36,6]0,0"_ Нормальная";[>36,6]0,0"_ Повышенная" устанавливает отображение чисел меньших или равных 36,6 с текстом Нормальная, а чисел более 36,6 – с текстом Повышенная.

 

Форматирование ячеек

Форматирование ячеек производится с помощью групп Шрифт, Выравнивание, Стили вкладки Главная.

Группа Шрифт аналогична такой же группе в Microsoft Word 2007. С её помощью настраиваются тип и размер шрифта, границы и цвет заливки ячеек.

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

Группа Стили позволяет применить к ячейке набор стандартных стилей. Имеется две коллекции стилей: Форматировать как таблицу и Стили ячейки.

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

(Выделить ячейку → Правая клавиша мыши → Формат ячеек…)

или (Главная → Ячейки → Формат → Формат ячеек…)

 

Задание по работе

 

Задание 1

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

2. На листе Дата в столбце А создайте последовательность дат от 01.01.2011 до конца месяца. Скопируйте получившуюся последовательность в столбцы B, C, D, E, F.

· к столбцу А примените формат даты – 01.01.2011;

· к столбцу В примените формат даты – 1 января 2011 г.;

· к столбцу С примените формат даты – 1-янв-2011;

· к столбцу D примените формат даты – 01Я11 (день, месяц одной буквой, две последние цифры года);

· к столбцу Е примените формат даты – 1 января 2011 - суббота (день, месяц, год, тире, день недели);

· к столбцу F примените формат даты – суббота (день недели, цвет синий).

· к диапазону A1:F31 примените форматирование Все границы, шрифт полужирный, цвет красный.

 

3. На листе Время в столбце А создайте последовательность времени от 00:00 до конца суток с шагом 30 минут. Скопируйте получившуюся последовательность в столбцы B, C, D.

· к столбцу В примените формат даты – AM/PM.;

· к столбцу С примените формат даты – 0:00:00;

· к столбцу D примените формат даты – 0:00 шрифт красный (цвет с помощью пользовательского формата);

· к диапазону A1:D48 примените форматирование: Толстая внешняя граница, шрифт курсив, заливка ячеек – серая.

 

4. На листе Числовой в диапазоне А1:А40 создайте геометрическую прогрессию от 1 с шагом (-1,5). Скопируйте получившуюся последовательность в столбцы B, C, D, E.

· к столбцу В примените формат – два знака после запятой;

· к столбцу С примените формат – пять знаков после запятой;

· к столбцу D примените формат – только целая часть, отрицательные числа красным цветом без знака минус;

· к столбцу Е примените формат – положительные числа синим цветом, отрицательные – зеленым.

 

5. На листе Текстовый в диапазоне А1:А20 создайте с помощью автозаполнения последовательность дней недели. Скопируйте получившуюся последовательность в столбцы B, C, D, E, F.

· к столбцам А, B, C, D, E, F примените формат - цвет текста синий, черный белый, красный, голубой, желтый;

· к диапазону A1:А20 примените форматирование: Все границы, цвет границ белый, цвет заливки черный.

 

6. На листе Дробный в столбце А создайте с числовую последовательность от 1 до 10 с шагом 0,5. Скопируйте получившуюся последовательность в столбцы B, C, D.

· к столбцу А примените формат – числовой, один знак после запятой;

· к столбцам B, C, D примените формат – Простыми дробями (1/4), Половинными дробями (1/2), Четвертными дробями (2/4).

 

7. На листе Экспоненциальный создайте:

в диапазоне А1:А40 геометрическую прогрессию от 50 с шагом 25.

в диапазоне В1:В40 геометрическую прогрессию от 50 с шагом (-25).

в диапазоне С1:с40 геометрическую прогрессию от 50 с шагом (0,0001).

· к столбцам В, С примените формат даты – AM/PM.;

· к столбцу С примените формат Экспоненциальный;

· к диапазону A1:С40 примените стиль ячейки Примечание.

 

8. На листе Денежный в диапазоне А1:А25 создайте геометрическую прогрессию от 1000 с шагом (-1,2). Скопируйте получившуюся последовательность в столбцы B, C, D, E, F.

· к столбцу В примените формат – денежный р. белорусский;

· к столбцу С примените формат – денежный $ английский (США);

· к столбцу D примените формат – финансовый $ английский (США);

· к столбцу Е примените формат – денежный? Евро (? 123);

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

9. Сохраните документ.

 

Контрольные вопросы

1. Виды числовых форматов.

2. Установка числовых форматов.

3. Структура числового формата

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

5. Числовой, дробный и экспоненциальный форматы.

6. Текстовый формат.

7. Денежный и финансовый форматы.

8. Создание пользовательских форматов.

9. Форматирование ячеек.

10. Применение стилей ячеек.

 

ЛАБОРАТОРНАЯ РАБОТА № 3

 

Вычисления в MS Excel

 

Методические рекомендации

 

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

 

Пример: =СУММ(В2:В8)*30

СУММ() – функция;

В2 и В8 – ссылки на ячейки;

: (двоеточие) и * (звездочка) – операторы;

30 – константа.

 

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

СУММ(число1;число2;…)

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

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

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

Константа – постоянное (не вычисляемое) значение. Формула и результат вычисления формулы константами не являются.

Все формулы в MS Excel должны начинаться со знака равенства, например, =А1+В2 или =СУММ(В1;С5).

С формулами можно работать точно так же, как и с константами. В формулах может использоваться обозначение диапазона ячеек, например =СУММ(A1:B2). Чтобы задать адрес диапазона, надо указать адреса его левой верхней и правой нижней ячеек, разделенных двоеточием. В частности, A1:B2 – это диапазон, состоящий из ячеек A1, A2, B1, B2, а формула =СУММ(A1:B2) определяет сумму значений, хранящихся в этих четырех ячейках.

Операторы

Арифметические операторы

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

 

Таблица 3 - Арифметические операторы

Оператор	Значение	Пример
+	Сложение	A1+A2
–	Вычитание	A1-A2
Отрицание	-A1
*	Умножение	A1*A2
/	Деление	A1/A2
%	Процент	50%
^	Возведение в степень	A1^2

Операторы сравнения

Операторы сравнения используются для сравнения двух значений. Результатом сравнения является логическое значение: либо ИСТИНА, либо ЛОЖЬ (таблица 4).

 

Таблица 4 - Операторы сравнения

Оператор	Значение	Пример
=	Равно	A1=B1
>	Больше	A1>B1
<	Меньше	A1<B1
>=	Больше или равно	A1>=B1
<=	Меньше или равно	A1<=B1
<>	Не равно	A1<>B1