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

1. Форматирование.

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

Создайте новую рабочую книгу Форматирование.xls.

1. Общие сведения.

Команды форматирования сосредоточены в меню "Формат". Наиболее часто используется команда меню "Формат/ Ячейки" (полезно запомнить для нее комбинацию клавиш Ctrl+1). Разу­меется, эту команду можно применить сразу к блоку, предвари­тельно выделив его. Наиболее важные команды привязаны к кнопкам панели инструментов "Форматирование".

Excel предоставляет возможность очень быстро оформить список по вашему вкусу. Для этого нужно применить команду меню: "Формат/Автоформат".

ПРИМЕР 1.

Автоформатирование таблицы. Скопируйте из рабочей книги Адресация.xls таблицу с рабочего листа "Фирма". Выделите какую-нибудь ячейку в таблице. В меню выберите команду "Формат/ Автоформат". Выберите один из списка форматов. Испытайте, например, "Классический 1". Многие из предлагаемых в списке форматов используют затенение и цветной фон в оформлении ячеек. Этого нужно избегать - и порошок в лазер­ном принтере расходуется бесполезно, и читать труднее.

Как избавиться от наложенного на таблицу формата? Нужно выделить таблицу и выбрать в меню команду "Правка/ Очи­стить/ Форматы".

Когда Вы удаляете содержимое ячейки или блока нажатием на клавишу Del, то наложенный формат сохраняется. Полная очистка и содержимого, и форматов - команда "Правка/ Очи­стить/ Все".

2. Числовые форматы

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

Перейдите на новый рабочий лист. Введите в ячейку А1 число 525687. Оно отображается точно так же, как хранится в ячейке. В ячейку В1 введите 00253 - ведущие нули исчезнут. В С1 введите ,34 - появится 0 перед десятичным разделителем. Наконец, в D1 введите какое-нибудь число с большим количе­ством цифр, например 123456789,123456. Будет выведено ок­ругленное число. Если уменьшать ширину столбца, то число будет выводиться в экспоненциальной форме (научной нота­ции), например 1.23Е+08, т.е. 1,23*10⁸. Если еще уменьшить ширину столбца, будет выведена строка ###. Это означает, что ширины столбца недостаточно для отображения числа.

Полезно запомнить сочетание клавиш для наложения основ­ного формата: Ctrl+Shift+~.

Десятичные разряды и разделитель тысяч. Скопируйте число из А1 в А2. Выделите ячейку А2 и нажмите Ctrl+1. Поя­вится диалоговое окно с несколькими вкладками, нас интересу­ет вкладка "Число". Слева на этой вкладке расположен список форматов. Первый элемент списка нами только что освоен - формат "Основной" (или "Общий"). Выделим второй элемент - "Числовой". Вид вкладки сразу меняется. В правой части вклад­ки можно задать количество десятичных разрядов после деся­тичного разделителя (по умолчанию 2) и установить или сбро­сить флажок разделителя тысяч. Можно также задать представ­ление отрицательных чисел: красным или черным цветом. Установим флажок разделителя тысяч. Выводимое число примет вид: 5 2569. Нужно подчеркнуть, что само число осталось неизменным, изменилось только его представление. Десятичный разделитель (точка) и разделитель тысяч (пробел) задаются не в Excel, а в панели управления Windows. Изменение этих устано­вок мы изучали в первой главе.

Денежный и финансовый форматы. Величины в денежном выражении представляются с двумя цифрами после точки и символом денежной единицы. Заметим, что символ денежной единицы "по умолчанию" задается в Windows в Панели управ­ления, там же, где формат даты и десятичный разделитель. Но в Excel можно выбирать при форматировании другие знаки де­нежной единицы. Это и правильно, так как на одном и том же рабочем листе могут присутствовать расчеты и в рублях, и в долларах. Введите в ячейку A3 число - 45254 и скопируйте его в А4. Наложите на A3 денежный формат, а на А4 - финан­совый. Увеличьте для наглядности ширину столбца А (в верхней адресной полосе передвиньте мышью вправо разделитель между столбцами А и В). Вы увидите, что в ячейке А4 знак минус ока­зался прижат к левому краю ячейки, а само число - к правому краю. Так что денежный формат в A3 выглядит более привычно. Еще дважды отформатируйте A3: выберите в выпадающем спи­ске на вкладке в качестве знака денежной единицы доллар как $ и как USD (такое обозначение принято в валютном дилинге).

Дроби. В ячейку можно ввести число в форме рациональной дроби, например 18/43. Но при этом оно будет воспринято как текст (напоминаю, что признаком этого является выравнивание по левому краю). Чтобы рациональная дробь была воспринята как число, нужно ввести ее как смешанную дробь, предварив нулем и пробелом: 0 18/43. В ячейке появится 18
/
43, а в строке ввода (при выделении ячейки) 0,418604651162791, т.е. деся­тичное приближение дроби. Если мы введем дробь 5/8, то нас ожидает еще больший конфуз: Excel воспримет это число как дату - день и месяц текущего года: 05-авг (в строке ввода: 05/08/2000).

ЗАДАЧА 1.

Получить рациональные приближения числа в виде дробей:

а) с двумя цифрами в числителе и знаменателе;

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

ПРИМЕР 2.

Наследование формата. Рассмотрим фрагмент таблицы для расчета окладов работников бюджетной сферы по Единой тарифной сетке

А В С 1 минимальный оклад 60 000 2 3 разряд коэфф. оклад 4 15 7,36 441 600 5 16 8,17 490 200 6 17 9,07 544 200

Оклад вычисляется по формуле: (минимальный ок­ лад)* (коэффициент). Таблица была составлена до деноминации (до 01/01/98). Ячейки столбца С были отформатированы: нало­жен формат, содержащий разделитель тысяч (кнопка "Формат с разделителями") и 0 знаков после десятичного разделителя (кнопка "Уменьшить разрядность"). После деноминации нужно изменить минимальный оклад: вместо 60 000 введем 60.

Табли­ца принимает вид, представленный на рисунке. А В С 1 минимальный оклад 60 2 3 разряд коэфф. оклад 4 15 7,36 442 S 16 8,17 490 6 17 9,07 544

Результат явно неверен: в С4 должно выводиться число 441,6 Это произошло потому, что блок С4:С6 сохранил фор­мат целых чисел. Наложим на этот блок (а также на С1) формат с двумя цифрами после десятичного разделителя. Получим пра­вильный результат

В этом примере ошибка была не столь опасна, так как мы должны были применить формат "рубли-копейки". Но можно привести примеры таблиц, пользователь которых может неверно воспринимать результаты расчетов из-за неудачного формати­рования. Например, вычисляем проценты и для этого формати­руем ячейку с результатом, щелкая мышью по кнопке "Про­
центный формат". Но формат, привязанный к этой кнопке, ок­ругляет проценты до целых. В результате пользователь может получить искаженное представление о результатах расчетов. Трудно предложить рецепт, как избежать этого. Один совет дать можно: если результаты расчетов должны быть округлены, это­го округления надо достигать не форматированием, а явным применением функции ОКРУГЛ и ее модификациями (они изу­чались во второй лабораторной работе, в разделе 3 "Числовые функции").

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

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

Сначала определим, что такое форматная

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

В секции форматной строки используются символы форма­
тирования
. Чтобы выяснить их назначение, обратитесь к Справ­ке: "Форматирование листов/ Форматирование чисел, значений даты и времени/ Пользовательские форматы чисел, дат и времени" и нажмите в окне Справки кнопку "Основные число­вые форматы".

ПРИМЕР 3.

Символы форматирования. Символы 0, #, ? используют­ся как шаблоны для отображения цифр.

Введите в A1:D1 число 42,1, а в A2:D2 - число 23,76. По очереди выделяйте блоки В1:В2, С1:С2, D1:D2. После выделе­ния блока нажимайте Ctrl+1 и на вкладке "Числа" выбирайте пункт "все форматы". В поле ввода "Тип" (в Excel 5.0/7.0 это поле ввода называется "Код") набирайте форматы, которые по­казаны в таблице в третьей строке (вводить эти коды в третью строку таблицы не нужно).

	А	В	С	D
1	42,1	042,100	42,1	42,1
2	23,76	023,760	23,76	23,76
3	Основной	000,00	###.###	???,???

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

Шаблон цифры # нужен для задания разделителя тысяч. Введем в ячейку С5 число 1234679. Наложим на него формат # ###,##. Разделителем тысяч является пробел. (Вид разделите­ля тысяч задается в Панели управления Windows.) В ячейке ото­бражается число 123 468. Введем в эту же ячейку 32,257. Отображается 32,26. А если наложить на эту ячейку формат 0 000.00, то будет отображено число 0 032,26, что не входит в наши планы. Если наложить формат ? ???,?? то перед числом появятся невидимые пробелы. Если постепенно уменьшать ширину столбца С, то с какого-то момента появятся значки #, сви­детельствующие о том, что представление числа не помещается в ячейке.

Разумеется, эти символы шаблона можно использовать в од­ном форматном выражении. Например, # ###,00 означает, что две десятичные цифры после точки выводятся всегда, даже если они равны 0.

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

Символ * вызывает повторение следующего за ним символа. Наложите на ячейку формат *-. Тогда в ней будет выводиться строка из минусов, если только ячейка не пуста (содержимое ячейки не имеет значения, лишь бы там было число).

Символ _ (подчеркивание) вставляет пробел, равный ширине следующего за ним символа. Расположите в А6:А17 числа от 1 до 12. Нужно, чтобы эти порядковые номера заканчивались точ­кой и от правого края ячейки их отделял пробел, равный по ши­рине круглой закрывающей скобке. Для этого наложим на блок формат 0.__).

ПРИМЕР 4.

Секции форматной строки.

Рассмотрим форматную строку # ##0,00;[Красный]( # ##0,00);0,00;"Счет: "@

Воспроизведите фрагмент таблицы (рис. 5): левая колонка - формат "Общий", правая ко­лонка - на те же значения нало­жен пользовательский формат.

1,147	1,15
0,12	0,12
-2345,7	(2 347,0)
0	0,00
Счет:	Иванов

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

ЗАДАЧА 2.

В ячейке должна выводиться сумма в рублях (копеек заведомо нет). Отрицательные суммы выводятся крас­ным цветом и со знаком минус. К числу добавляется символ де­нежной единицы р., отделенный от числа пробелом, размер ко­торого равен ширине знака "минус". Имеется разделитель ты­сяч.

ЗАДАЧА 3.

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

ЗАДАЧА 4.

В ячейке содержится число. Выводите его с двумя цифрами после точки и с разделителем тысяч. Если число положительное, то оно должно предваряться словом "Приход: ", если отрицательное - словом "Расход: ", если число равно ну­лю, то должно выводиться слово "Ничего".

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

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

ЗАДАЧА 5.

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

ПРИМЕР 5.

Явный условный формат. До сих пор был использован подразумеваемый условный формат: не указывали в фор­матных секциях условий применения форматов, и Excel приме­нял форматные секции для положительных, отрицательных, ну­левых чисел и для текста. Но можно каждую секцию предварить условием в квадратных скобках. В блоке А1:А10 содержатся оценки: 2, 3, 4, 5. Пусть двойки выводятся черным цветом, пятерки - красным, а тройки и чет­верки - зеленым. Выделим блок А1:А10 и наложим пользова­тельский формат

[Черный] [=2]; Красный] [=5][;[Зеленый] .

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

ЗАДАЧА 6.

В блоке записаны проценты: 12.45%, 30% и т.д. Выводить проценты с одной цифрой после точки. Числа, боль­шие 50%, выводить красным цветом.

4. Оформление таблиц

В диалоговом окне "Форматирование ячеек" есть вкладки "Выравнивание", "Шрифт", "Граница", "Вид", с помощью кото­рых можно профессионально оформить заголовки таблиц. Ис­пользуя эти средства, можно также подготовить бланки доку­ментов.

Перейдите на новый рабочий лист и назовите его "Книги".

ПРИМЕР 6.

Пример взят из с изменениями. Для книжного магазина составляется ежедневная таблица продаж книг.

Продано книг Дата Всего в том числе Научная Техническая Художественная 15-мар 523 150 200 173 16-мар 475 124 140 211 17-мар 360 104 98 158 18-мар 371 132 107 132

Нужно оформить такую таблицу на рабочем листе. Сначала введем в ячейки текстовые строки. В ячейку А2 - Про­ дано книг, в А4 - Дата, в В4 - Всего, в С4 - науч­ ная, в D4 - техническая, в Е4 - художественная, в СЗ - в том числе. В ячейку А5 введем дату 15/03. В С5:Е5 введем числа 150, 200, 173. Выделим блок C5:F5 и щелкнем кнопку Автосумма. В F5 появится формула =СУММ(С5:Е5). Перетащим ячейку F5 в ячейку В5.

Таблица примет вид, как показано на рисунке.

	А	В	С	D	Е	F
1
2	Продано книг
3			в том числе
4	Дата	Всего	Научная	Техническая	Художественная
5	15 мар	523	150	200	173

Приступим к оформлению заголовка таблицы. Выделим ячейки А2:Е2 и щелкнем кнопку "Объединить и поместить в центре". В выпадающем списке "Размер" (шрифта) выберем размер 20 пунктов. Автоматически увеличится высота строки 2. Аналогично поступим с блоком СЗ:ЕЗ. Размер шрифта назна­чим 14 пунктов.

Выделим блок А4:В4. Нажмем клавиши Ctrl+1. Перейдем на вкладку "Выравнивание". Выберем вертикальную ориентацию текста. По вертикали выберем выравнивание по центру. Перей­дем на вкладку "Шрифт" и выберем размер 14 пунктов. Щелк­нем кнопку "ОК".

Выделим блок С4:Е4. Нажмем клавиши Ctrl+1. Перейдем на вкладку "Выравнивание". Выберем вертикальную ориентацию текста. По вертикали и горизонтали выберем выравнивание по центру. Перейдем на вкладку "Шрифт" и выберем размер 13 пунктов.

Слово "художественная" - слишком длинное и делает за­головок сильно вытянутым по вертикали. Чтобы этого избежать, вставим в это слово символ переноса и пробел: "художест­венная" (пробел нужен, чтобы Excel воспринимал это как два слова). Выделим ячейку с этим словом, нажмем клавиши Ctrl+1 и на вкладке "Выравнивание" установим флажок "Переносить по словам". Переноса не произойдет - высота столбца еще уве­личится. Схватим мышью нижнюю границу строки 4 на адрес­ной полосе и принудительно уменьшим ее. Тогда строка "
художественная
" превратится в две строки.

Теперь выполним действие, которое стало возможным толь­ко в Excel 97, - объединение ячеек по вертикали. Выделим блок АЗ:А4, нажмем клавиши Ctrl+1, выбираем вкладку "Вы­равнивание", установим флажок "объединение ячеек". То же проделаем с блоком ВЗ:В4,

Нарисуем рамки вокруг элементов заголовка. Выделим блок А2:Е4, нажмем Ctrl+1, выберем вкладку "Граница". Щелкнем кнопки "Внешняя" и "Внутренняя". В результате элементы заго­ловка будут разделены линиями. Можно выбрать разную тол­щину линий для границы общего заголовка в блоке А2:Е2 и других заголовков. Разберитесь самостоятельно, как это сделать.

Покрасим заголовок. Для этого выделим блок А2:Е4, на­жмем Ctrl+1, выберем вкладку "Вид". Укажем на палитре цвет со слабой интенсивностью, например светло-голубой.

Отформатируем первую строку таблицы. Выделим ее, уста­новим выравнивание по ширине и величину шрифта 12 пунктов. Введем еще несколько строк (при этом копируем ячейку с сум­мой). Чтобы копировать формат в новые строки, удобно пользо­ваться кнопкой "Формат по образцу" (на ней нарисована кисть).

Мы еще вернемся к этому примеру в главе 9. Забегая вперед, заметим, что вертикальное объединение ячеек мешает проведе­нию операции отбора строк таблицы, отвечающих определен­ному критерию. >

Если нужно, чтобы в заголовках, состоящих из нескольких слов, слова располагались по строкам так, как нам требуется, а не произвольно, то нажимайте в местах принудительного пере­носа слов сочетание клавиш Alt+Enter.

5. Условное форматирование

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

Перейдите на новый рабочий лист и назовите его "Условное форматирование".

План погашения кредита
Месяц	Непогашенная сумма основного долга, тыс. руб.	Процентный платеж, тыс.руб.	Месячная выплата основного долга, тыс.руб.	Сумма месячного погашенного взноса, тыс.руб.
	1800
1	1500	36,0	300	336
2	1200	30,0	300	330
3	900	24,0	300	324 318
4	600	18,0	300
5	300	12,0	300	312
6	0	6,0	300	306
Итого		126,0	1800,0	1926,0

ПРИМЕР
7.

Введите в А1 число 1, в блок В1:В15 арифметическую про­грессию 1, 2, ..., 15, в С1 введите формулу =$А$1*В1 и скопи­руйте ее в С2:С15. Скройте столбец В. Материал для экспери­ментов подготовлен. Требуется наложить на С1:С15 формат: если число лежит в диапазоне от 10 до 20, то выводится курси­вом; если от 20 до 40, то выводится полужирным курсивом в рамке; если больше 40, то выводится красными цифрами на го­лубом фоне.

Решение. Выделите блок С1:С15. Выберите в меню: "Фор­мат/ Условное форматирование". В диалоговом окне укажите для Условия 1: значение между 10 и 20 (заполняете четыре поля ввода), щелкните кнопку "Формат". Появится новое диалоговое окно с тремя вкладками. На вкладке "Шрифт" выберите "кур­сив" и щелкните "ОК". После этого щелкните кнопку "А также »". Диалоговое окно расширится. Заполните "Условие 2", еще раз щелкните "А также »" и заполните "Условие 3". Теперь вводите в ячейку А1 различные числа и прослеживайте, как из­меняется формат ячеек в зависимости от выводимого значения.

Отменим "Условие 2". Для этого выделите С1 :С15, откройте окно "Условное форматирование" и щелкните кнопку "Уда­лить". Вам будет предложено новое диалоговое окно, где Вы сможете установить флажки для удаляемых условий. Установи­те флажок для условия 2.

ПРИМЕР 8.

Условное форматирование можно задавать формулой. Вве­дем в Е1:Е15 прогрессию 1, 2, ..., 15. Нужно выделить полу­жирным шрифтом числа, кратные трем, но не кратные двум.

Решение. Выделим блок Е1:Е15, выберем в меню пункт "Формат/ Условное форматирование". В диалоговом окне ука­жите для Условия 1 в выпадающем списке не "значение", как в предыдущем примере, а "формула". В следующем поле ввода введите формулу =И(ОСТАТ( А1, 3) = 0, ОСТАТ( А1, 2) <> 0). Щелкните кнопку "Формат" и выберите полужирный шрифт. Будут выделены числа 3, 9, 15.

Проанализируем введенную формулу. Она должна возвра­щать значение ИСТИНА или ЛОЖЬ. В ней фигурирует отно­сительный адрес активной ячейки, входящей в блок. В выделенном блоке активная ячейка показана другим цветом.

Три интересных примера приведены в Справке. Найдите раздел "Форматирование листов/ Форматирование текста и яче­ек/ Выделение данных, отвечающих определенным свойствам". (В Excel 2000 - "Форматирование листов/ Условное формати­
рование/ Формула в качестве критерия форматирования".)

ЗАДАЧА 7.

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

	А	В	С	D	...	м	N
1		янв	фев	мар	...	дек	Итого
2	доход	500,00		500,00	...	500,00	4500,00
3	с нач.года	500,00	500,00	1000,00	...	4500,00	4500,00

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