Лабораторная работа по Информатике 4

ЛАБОРАТОРНАЯ  РАБОТА № 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*108. Если еще уменьшить ширину столбца, будет выведена строка ###. Это означает, что ширины столбца недостаточно для отображения числа.

Полезно запомнить сочетание клавиш для наложения основного формата: 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) формат с двумя цифрами после десятичного разделителя. Получим правильный результат



А

В

С

1

минимальный оклад

60,00

2







3

разряд

коэфф.

оклад

4

15

7,36

441,60

5

16

8,17

490,20

6

17

9,07

544,20






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

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



5