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

Учебная работа. Лабораторная работа: Лабораторная работа по Информатике 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
. Поя­вится диалоговое окно с несколькими вкладками, нас энтузиазму­ет вкладка «Число
«. Слева на данной нам вкладке размещен перечень форматов. 1-ый элемент перечня нами лишь что освоен — формат «Главный
» (либо «Общий
«). Выделим 2-ой элемент — «Числовой
«. Вид вкладки сходу изменяется. В правой части вклад­ки можно задать количество десятичных разрядов опосля деся­тичного разделителя (по дефлоту 2) и установить либо сбро­сить флаг разделителя тыщ. Можно также задать представ­ление отрицательных чисел: красноватым либо черным цветом. Установим флаг разделителя тыщ. Выводимое число воспримет вид: 5 2569
. необходимо выделить, что само число осталось постоянным, поменялось лишь его знак валютной единицы «по
умолчанию
» задается в 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-мя цифрами в числителе и знаменателе;

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

ПРИМЕР 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
) формат с 2-мя цифрами опосля десятичного разделителя. Получим пра­вильный итог

А

В

С


1

малый оклад
60,00

2


3

разряд
коэфф.
оклад

4

15
7,36
441,60

5

16
8,17
490,20

6

17
9,07
544,20

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

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

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

Поначалу определим, что такое

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

В секции форматной строчки употребляются




Чтоб узнать их предназначение, обратитесь к Справ­ке: «Форматирование
листов
/ Форматирование
чисел
, значений
даты
и времени
/ Пользовательские форматы чисел, дат и времени
» и нажмите в окне Справки клавишу «Главные число­вые форматы
«.

ПРИМЕР 3.

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

Введите в A
1:
D
1
число 42,1
, а в A
2:
D
2
— число 23,76
. По очереди выделяйте блоки В1:В2, С1:С2,
D
1:
D
2.
Опосля выделе­ния блока жмите 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
выводятся и незначащие нули, по шаблону числа ?
выводятся пробелы, так что числа оказываются выровненными по десятичному раздели­телю, шаблон числа #
оставил формат # ###,##
. Разделителем тыщ является пробел. (Вид разделите­ля тыщ задается в Панели управления Windows.) В ячейке ото­бражается число 123 468.
Введем в эту же ячейку 32,257
. Отображается 32,26.
А если наложить на эту ячейку формат 0 000.00
, то будет отображено число 0 032,26
, что не заходит в наши планы. Если наложить формат ? ???,??
то перед числом покажутся невидимые пробелы. Если равномерно уменьшать ширину столбца С
, то с какого-то момента покажутся значки #
, сви­детельствующие о том, что к примеру, # ###,00
значит, что две десятичные числа опосля точки выводятся постоянно, даже если они равны 0
.

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

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

знак _
(подчеркивание) вставляет пробел, равный ширине последующего за ним знака. Расположите в А6:А1
7 числа от 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.

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

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

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

задачка 5.

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

ПРИМЕР 5.

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

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

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

задачка 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 — науч­
ная,
в D
4 — техно,
в Е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: формат
«. Покажется новое диалоговое окно с 3-мя вкладками. На вкладке «шрифт
» изберите «кур­сив» и щелкните «ОК
«. Опосля этого щелкните клавишу «Также
»». Диалоговое окно расширится. Заполните «Условие 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

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

]]>