Тема: Работа с мастером функций, построение диаграмм и графиков

Редактирование таблиц. Листы: переименование, копирование. Относительные и абсолютные адреса ячеек. Имена ячеек. Автосуммирование.

Задание 3.

1. Откройте файл Счет.

2. Перейдите на Лист2 и внесите номер дома и название улицы, используя данные таблицы Листа1.

Таблица3.

СЧЕТ
выписан за		2009г.	балансодержателем жилого помещения.
Расположенного в квартире №		дома		по ул.
общей площадью		кв.м.
Квартиросъемщика
лицевой счет				за следующие виды услуг:
Виды услуг	Задолжен-ность платель-щика, тг.	Ед. измерения	Тариф за единицу услуги, тг.	Число дней, когда услуга оказывалась	Расчитано для оплаты, тг.	Льгота, тг.	Итого начисл. для оплаты, тг.

Наем жилого помещения		кв.м	0,9		0,9		0,9
Платежи за коммунальные услуги
Холодная вода		с чел.в мес.	5,47		5,47		5,47
Горячая вода		с чел.в мес.	6,29		6,29		6,29
Канализация		с чел.в мес.	1,65		1,65		1,65
Отопление		кв.м	0,72		0,72		0,72
Газ		с чел.в мес.	1,7		1,7		1,7
Электроэнергия		с чел.в мес.	5,6		5,6		5,6
Платежи за техническое обслуживание и ремонт жилого здания
Уборка подъезда		с квартиры	9,62		9,62		9,62
Содержание двора		с квартиры	17,27		17,27		17,27
Обслуж. Мусоропровода		с квартиры	6,78		6,78		6,78
Лифт		с чел.в мес.	4,8		4,8		4,8
Телеантенна		с квартиры	4,3		4,3		4,3
Домофон		с квартиры
Радио		с квартиры	3,61		3,61		3,61
ИТОГО:
Счет выписан

Указание: Чтобы приравнять значение текущей ячейки какой-либо другой, поставьте в текущей ячейке знак =. Затем щелкните левой кнопкой мыши на ячейке с исходными данными.

3. Создайте 5 копий Листа2. Указание: Для копирования и переименования листов щелкните левой кнопкой "мыши" по вкладке с названием листа и выберите соответствующую команду.

4. Переименуйте листы. Назовите: Лист1 – Список жильцов, Лист2 – Счет, Лист3 – Квартира1, Лист4 – Квартира2 и т.д.

5. Вернитесь к Списку жильцов. Присвойте ячейкам, в которых записана жилая площадь, имена: Площадь1, Площадь2 и т.д.

Указание: Чтобы присвоить ячейке имя, щелкните на этой ячейке. Затем щелкните в окошке " Имя " (слева от Строки формул) и введите имя.

6. Внесите данные в шапки таблиц на листах Квартира1 – Квартира5, используя данные таблицы Список жильцов (таблица1): номера квартир и лицевых счетов, фамилии жильцов, размер жилой площади. Указание: Чтобы вставить размер жилой площади, установите курсор на нужной ячейке и используйте команду Вставка – Имя – Вставить – выберите нужное имя.

7. Измените формулы в столбцах Рассчитано для оплаты: там, где оплата рассчитывается в зависимости от величины жилой площади, вместо 1 подставьте адрес ячейки, в которой указана общая площадь; там, где оплата рассчитывается в зависимости от количества человек, вместо 1 подставьте адрес соответствующей ячейки из таблицы Список жильцов; в формулу столбцов Льгота вместо 0 подставьте адреса соответствующих ячеек из таблицы Список жильцов. Указание. Редактирование проводите в строке формул. Чтобы вставить в формулу адрес ячейки другого листа, надо щелкнуть левой кнопкой "мыши" по вкладке с названием листа, а затем по нужной ячейке в этом листе. Когда все изменения в формулу будут внесены, нажмите ENTER.

8. Скопируйте формулы в соответствующие ячейки столбцов Рассчитано для оплаты и Льгота. Указание: Чтобы при копировании формул адреса ячеек не изменялись, сделайте их абсолютными.

Относительные адреса: А1, В2, С3 и т.д. Абсолютные адреса: $A1 (не изменяется столбец), A$1 (не изменяется строка), $A$1 (не изменяется ячейка).

Поименованные ячейки при копировании формул не изменяются.

Таблица4.

СЧЕТ
выписан за		2009г.	балансодержателем жилого помещения.
Расположенного в квартире №		дома		по ул.	Гоголя
общей площадью	63,9	кв. м.
квартиросъемщика	Собакевич М.С.
лицевой счет				за следующие виды услуг:
Виды услуг	Задолжен-ность платель-щика, тг.	Ед. измерения	Тариф за единицу услуги, тг.	Число дней, когда услуга оказывалась	Рассчитано для оплаты, тг.	Льгота, тг.	Итого начисл. для оплаты, тг.

Наем жилого помещения		кв.м	0,9		57,51		57,51
Платежи за коммунальные услуги
Холодная вода		с чел.в мес.	5,47		10,94	2,735	8,21
Горячая вода		с чел.в мес.	6,29		12,58	3,145	9,44
Канализация		с чел.в мес.	1,65		3,30	0,825	2,48
Отопление		кв.м	0,72		46,01	11,502	34,51
Газ		с чел.в мес.	1,7		3,40	0,85	2,55
Электроэнергия		с чел.в мес.	5,6		11,20	2,8	8,40
Платежи за техническое обслуживание и ремонт жилого здания
Убора подъезда		с квартиры	9,62		9,62		9,62
Содержание двора		с квартиры	17,27		17,27		17,27
Обслуж. Мусоропровода		с квартиры	6,78		6,78		6,78
Лифт		с чел.в мес.	4,8		9,60		9,60
Телеантенна		с квартиры	4,3		4,30		4,30
Домофон		с квартиры			0,00		0,00
Радио		с квартиры	3,61		3,61		3,61
ИТОГО:							174,26
Счет выписан	26.09.99

9. Подсчитайте итоговую сумму в строке ИТОГО.

Указание: Установите курсор на ячейке, куда нужно ввести сумму.
Используйте команду Автосуммирование.

10. В строку под таблицей введите сегодняшнюю дату.

Указание: Используйте команду Вставить – Функцию – Даты – Сегодня

11. Должны получиться таблицы, как Таблица 4. Сохраните книгу под именем Счета.

Вставка функций, сортировка данных.

Задание 4.

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

1. Откройте книгу Счета. Перейдите на чистый лист. Назовите его Сводные данные.

2. На нем создайте таблицу и внесите в нее соответствующие данные:

- в столбце 4 просуммируйте оплату всех коммунальных услуг по каждому квартиросъемщику отдельно;

- в столбце 5 просуммируйте оплату всех услуг по техническому обслуживанию и ремонту по каждому квартиросъемщику отдельно.

Таблица5.

Ф. И. О	Общая сумма оплаты	Наем	Ком. Услуги	Тех. Обслуживание и ремонт

Чичиков П.И.	132,21	36,18	49,65	46,38
Ноздрев А.Т.	149,52	27,54	70,80	51,18
Манилов Ф.А.	261,56	65,52	135,26	60,78
Собакевич М.С.	174,26	57,51	65,57	51,18
Коробочка Н.П.	134,81	14,4	43,75	46,38

3. Отсортируйте фамилии по алфавиту.

Указание: Выделите группу ячеек. Используйте команду Данные – Сортировка.

4. Добавьте строки для расчета минимальных, максимальных и средних значений величин.

5. Вычислите минимальные, максимальные и средние значения по столбцам.

Указание: Используйте команду Вставка – Функция – (категория) Статистические.

Построение диаграмм и графиков.

Задание5.

1. По данным Таблицы5 постройте диаграмму так, чтобы в ней отражалась доля каждой категории в общей сумме оплаты. Дайте диаграмме название. Указание: Используйте команду Вставка – Диаграмма. Данные расположены в столбцах.

2. Сохраните книгу.

Рис.1.

Задание 6.

1. Создайте новую книгу.

2. Создайте Таблицу5 и Таблицу6 на отдельных листах.

Таблица5.

Общее число предприятий промышленности
по формам собственности (единиц)
Форма собственности
Государственная собственность
Частная собственность
Смешанная собственность
Собственность общ.объединений, организаций

3. По данным Таблицы5 постройте набор круговых диаграмм по каждой форме собственности в отдельности. Каждой диаграмме дайте название. Выберите оформление, которое бы наиболее наглядно представляло данные. Пример см. Рис.2.

Рис.2.

Таблица6.

Объем продукции (работ, услуг) в действующих ценах
предприятий промышленности (в процентах)
Форма собственности
Государственная собственность	83,1	59,4	62,2	48,2	25,8
Частная собственность	16,7	40,2	0,2	0,3	1,3
Смешанная собственность			37,5	51,3	72,7
Собственность общ.объединений, организаций	0,2	0,4	0,1	0,2	0,2