Как произвести форматирование области построения диаграммы?

1. Как произвести форматирование рядов данных?
2. Как произвести форматированиеосей диаграммы?
3. Как произвести форматирование сетки?
4. Как произвести форматирование легенды?
5. Как использовать тренды в диаграмме?

 

 

Задание 10

1. Запустите Excel 2000 и сохраните Книгу 1 в своей папке Работы по Excel под именем Работа 10.Переименуйте Лист1 на Список, а Лист2 — на Сортировка,а Лист3 — на Автофильтр, Лист4 — на Расширенный фильтр, Лист 5 – на Форма
2. На листе Список создайте таблицу, приведенную на рис. 10.6.а.

	А	В	С	D	E	F	G
	Номер группы	Номер зач.кн.	Код предмета	Таб.№ препод.	Вид занятия	Дата	Оценка
	04Эк		п1	а1	лекция	12.06.05
	04Эк		п2	а2	практ.	25.05.05
	04Эк		п1	а1	лекция	12.06.05
	04Эк		п2	а2	практ.	20.05.05
	04Эк		п1	а1	лекция	12.06.05
	04Эк		п2	а3	практ.	25.05.05
	04СТ		п1	а3	лекция	12.06.05
	04СТ		п1	а2	практ.	25.05.05
	04СТ		п1	а1	лекция	07.06.05
	04Юр		п2	а2	практ.	25.05.05
	04Юр		п1	а1	лекция	07.06.05
	04Жур		п2	а2	практ.	20.05.05
	04Жур		п1	а3	лекция	07.06.05
	04Инж		п2	а2	практ.	20.05.05
	04Инж		п1	а3	лекция	07.06.05
	04Инж		п2	а1	практ.	20.05.05

Рис.10.6а

 

 

1. Произведите копирование списка (базы данных) с листа Списокна лист Сортировка.Сделайте сортировку на трех уровнях по возрастанию: по преподавателям, по номеру группы, по коду предмета. Выполните сортировку по другим полям.
2. Скопируйте с листа Список на лист Автофильтрисходную базу данных. Выберите из списка данные, используя критерий: для преподавателя а1 выбрать сведения о сдаче экзамена на положительную оценку, вид занятий — лекция.Отмените результат автофильтрации
3. Выберите из списка данные, используя критерий: для группы04Экполучить сведения о сдаче экзамена по предметуп1 на оценки 3 и 4.
4. Скопируйте с листаСписок на листРасширенный фильтр исходную базу данных. Сформируйте в области условий отбора Критерий сравнения— о сдаче экзаменов студентами группы 04Эк по предметуп1на оценки 4 или 5. Произведите фильтрацию записей на том же листе.
5. Сформируйте в области условий отбора Вычисляемый критерий — для каждого пре­подавателя выбрать сведения о сдаче студентами экзамена на оценку выше средней, вид занятий — лекция. Произведите фильтрацию записей на том же листе.
6. Скопируйте с листаСписок на листФорма исходную базу данных. Добавьте новый столбец справа. Просмотрите записи списка с помощью формы данных, добавьте новые списки
7. Сформируйте условие отбора с помощью формы данных — для преподавателя а1выбрать сведения о сдаче студентами экзамена на положительную оценку, вид заня­тий — лекция.
8. Сохраните книгу Работа 10.xls

Технология работы 10

1. Пуск → Все программы → Microsoft Excel. Файл → Сохранить как… →в строкеПапкаустановите свою папкуРаботы по Excel→в строкеИмя файлаустановите Работа10 →в строкеТип файлаустановите Книга Microsoft Excel → Сохранить
2. На листе Список создайте таблицу, приведенную на рис. 10.6.а.
3. Произведите копирование списка (базы данных) с листа Списокна лист Сортировка.Сделайте сортировку на трех уровнях по возрастанию: по преподавателям, по номеру группы, по коду предмета. Для этого: установите курсор в поле списка и введите командуДанные ® Сортировка. При этом должна выделиться вся область списка. Если этого не произошло, то пред­варительно выделите весь список, а затем введите указанную команду; в диалоговом окне «Сортировка диапазона» установите:

Сортировать по: поле «Таб. № препод.», по возрастанию

Затем по: поле «Номер группы», по возрастанию.

В последнюю очередь по: поле «Код предмета», по возрастанию. Выполните сортировку по другим полям.

1. Скопируйте с листа Список на лист Автофильтрисходную базу данных. Выберите из списка данные, используя критерий: для преподавателя а1 выбрать сведения о сдаче экзамена на положительную оценку, вид занятий — лекция.Для этого: установите курсор в область списка и выполните командуДанные ® Фильтр ® Автофильтр; в каждом столбце появятся кнопки списка. Сформируйте условия отбора записей: в столбце Таб. № препод. нажмите кнопку |, из списка условий отбора выберите а1; в столбце Оценка нажмите кнопку , из списка условий отбора выберитеУс­ловие и в диалоговом окне сформируйте условие отбора >2; в столбце Вид занятия нажмите кнопку , из списка условий отбора выбе­рителекция. Отмените результат автофильтрации: Данные, Фильтр, Автофильтр.
2. Выберите из списка данные, используя критерий: для группы 04Эк получить сведения о сдаче экзамена по предмету п1 на оценки 3 и 4. Для этого воспользуйтесь ана­логичной п. 4 технологией фильтрации.
3. Скопируйте с листа Список на лист Расширенный фильтр исходную базу данных так, чтобы она начиналась с ячейки А5 (см.рис.10.6b).

	А	В	С	D	E	F	G
	Номер группы	Номер зач.кн.	Код предмета	Таб.№ препод.	Вид занятия	Дата	Оценка
	04Эк		п1	а1	лекция	12.06.05
	04Эк		п2	а2	практ.	25.05.05
	04Эк		п1	а1	лекция	12.06.05
	04Эк		п2	а2	практ.	20.05.05
	04Эк		п1	а1	лекция	12.06.05
	04Эк		п2	аЗ	практ.	25.05.05
	04СТ		п1	аЗ	лекция	12.06.05
	04СТ		п1	а2	практ.	25.05.05
	04СТ		п1	а1	лекция	07.06.05
	04Юр		п2	а2	практ.	25.05.05
	04Юр		п1	а1	лекция	07.06.05
	04Жур		п2	а2	практ.	20.05.05
	04Жур		п1	аЗ	лекция	07.06.05
	04Инж		п2	а2	практ.	20.05.05
	04Инж		п1	аЗ	лекция	07.06.05
	04Инж		п2	а1	практ.	20.05.05

Рис.10.6b

 

Сформируйте в области условий отбора Критерий сравнения — о сдаче экзаменов студентами группы 04Эк по предмету п1 на оценки 4 или 5. Для этого:

Создайте область условий: в ячейки А1:G1 скопируйте об­ласть имен полей,т.е. ячейки А5:G5; в первую строку после имен полей введите: в столбец Номер группы — значение 04Эк,в столбец Код предмета —значения п1, в столбец Оценка — условие >3.

Произведите фильтрацию записей на том же листе: щелкните по любой ячейке списка(базы данных); выполните командуДанные ® Фильтр ® Расширенный фильтр; в диалоговом окне «Расширенный фильтр» с помощью мыши задайте пара­метры, например: Исходный диапазон: A5:G21, Диапазон условия: A1:G2 ; нажмите кнопку ОК.

 

1. Сформируйте в области условий отбора Вычисляемый критерий — для всех пре­подавателей выбрать сведения о сдаче студентами экзамена на оценку выше средней, вид занятий — лекция. Для этого: очистите вторую строку области условий (A2:G2); восстановите исходный вид списка: щелкните по любой ячейке списка, далее Данные ® Фильтр ® Отобразить все; в столбец Вид занятия введите точное значения — слово лекция; переименуйте! в области критерияимя поляОценка,на имяОценка1; в столбец Оценка1введите вычисляемый критерий, например, вида =G6>CP3HA4($G$6:$G$21), где G6 — адрес первой клетки с оценкой в исходном списке, $G$6 : $G$21 — блок ячеек с оценками, СРЗНАЧ — функция вычисления среднего значения.

	А	В	С	D	E	F	G
	Номер группы	Номер зач.кн.	Код предмета	Таб.№ препод.	Вид занятия	Дата	Оценка1
					лекция		=G6>СРЗНАЧ($G$6:$G$21)

 

	А	В	С	D	E	F	G	H
	Номер группы	Номер зач.кн.	Код предмета	Таб.№ препод.	Вид занятия	Дата	Оценка	Номер записи
	04Эк		п1	а1	лекция	12.06.05
	04Эк		п2	а2	практ.	25.05.05
	04Эк		п1	а1	лекция	12.06.05
	04Эк		п2	а2	практ.	20.05.05
	04Эк		п1	а1	лекция	12.06.05
	04Эк		п2	аЗ	практ.	25.05.05
	04СТ		п1	аЗ	лекция	12.06.05
	04СТ		п1	а2	практ.	25.05.05
	04СТ		п1	а1	лекция	07.06.05
	04Юр		п2	а2	практ.	25.05.05
	04Юр		п1	а1	лекция	07.06.05
	04Жур		п2	а2	практ.	20.05.05
	04Жур		п1	аЗ	лекция	07.06.05
	04Инж		п2	а2	практ.	20.05.05
	04Инж		п1	аЗ	лекция	07.06.05
	04Инж		п2	а1	практ.	20.05.05

 

Номер группы	Номер зач.кн.	Код предмета	Таб.№ препод.	Вид занятия	Дата	Оценка
04Эк		п1	а1	лекция	12.06.05
04Эк		п2	а2	практ.	25.05.05
04Эк		п1	а1	лекция	12.06.05
04Эк		п2	а2	практ.	20.05.05
04Эк		п1	а1	лекция	12.06.05
04Эк		п2	аЗ	практ.	25.05.05
04СТ		п1	аЗ	лекция	12.06.05
04СТ		п1	а2	практ.	25.05.05
04СТ		п1	а1	лекция	07.06.05
04Юр		п2	а2	практ.	25.05.05
04Юр		п1	а1	лекция	07.06.05
04Жур		п2	а2	практ.	20.05.05
04Жур		п1	аЗ	лекция	07.06.05
04Инж		п2	а2	практ.	20.05.05
04Инж		п1	аЗ	лекция	07.06.05

 

Рис.10.6с

 

 

Произведите фильтрацию записей на том же листе: установите курсор в область списка; выполните команду Данные ® Фильтр ® Расширенный фильтр;в диалоговом окне «Расширенный фильтр» с помощью мыши задайте пара­метры, например: Исходный диапазон: А5:G21, Диапазон условия: А2:G2; нажмите кнопку ОК.На рис. 10.6с представлены таблицы области условий отбора, списка (базы данных) и результата работы по п.6

 

1. Скопируйте с листа Списокна лист Формаисходную базу данных. Добавьте новый столбец справа с именем поля Номер записи и пронумеруйте строки этого поля от 1 до 16. Просмотрите записи списка с помощью формы данных. Для этого: установите курсор в область списка и выполните командуДанные ® Форма;просмотрите записи списка и внесите необходимые изменения с помощью кнопок Назад и Далее. С помощью кнопки Добавить можете добавить новые записи.
2. Сформируйте условие отбора с помощью формы данных — для преподавателя а1 выбрать сведения о сдаче студентами экзамена на положительную оценку, вид заня­тий — лекция.Для этого: нажмите кнопку Критерии, название которой поменяется на Правка; в пустых строках имен полей списка введите критерии: в строку Таб № препод. введитеа1,в строку Вид занятия введителекции,в строку Оценкавведите условие > 2. Просмотрите отобранные записи, нажимая на кнопку Назад или Далее.
3. Сохраните книгу Работа 10.xls

 

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

1. Как называются элементы базы данных Excel?
2. Сколько уровней сортировки предусмотрены в среде Excel 2003?
3. Как задаются дополнительные установки сортировки?
4. Что такое фильтрация данных?
5. Как работать с автофильтром?
6. Что выбирается в диалого­вом окне «Пользовательский автофильтр»
7. Чем отличается расширенный фильтр от автофильтра?
8. Как формируется область критериев поиска для расширенного фильтра?
9. Что представляет собойвычисляемый критерий для расширенного фильтра?
10. Как производится фильтрация с помощью формы данных?

 

Задание 11

1.Запустите Excel 2000. Переименуйте Лист1 на Структура, Лист2 — на Автоструктура,а Лист3 — на Итоги. Сохраните Книгу 1 в своей папке Работы по Excel под именем Работа 11.

2.Скопируйте на лист Структура исходную базу данных из работы 10 (рис.10.6а задания 10) и отсортируйте строки списка по номеру учебной группы. Вставьте пустые разделяющие строки между учебными группами

3.Создайте структурные части таблицы для учебных групп и для столбцов Код предмета, Таб. № препод., Вид занятий

4.Отмените структурирование. Повторите п.3 задания

5.Создайте таблицу расчета заработной платы ( рис. 11.2а), в которой значения в пустых ячейках вычислите по формулам

	A	B	C	D	E	F	G	H	I
1.	Фамилия	Зарплата	Подоходный налог	Пенсионный фонд	Общий налог	Надбавка	Премия	Итого доплат	Сумма к выдаче
2.	Иванов
3.	Петров
4.	Сидоров
5.	Смирнова
6.	Итого

Рис. 11.2а

6. Создайте автоструктуру таблицы расчета заработной платы и сравните с изображени­ем на рис. 11.3а

Рис. 11.3а. Вид таблицы после автоструктурирования

 

 

7. Скопируйте на лист Итогитаблицу из листаСтруктура; отмените структурирование. Отсортируйте записи списка по номеру группы, коду предмета, виду занятий

8. Создайте 1 -и уровень итогов — средний балл по каждой учебной группе.

9. Создайте 2-й уровень итогов — средний балл по каждому предмету для каждой учеб­ной группы.

10. Создайте 3-й уровень итогов — средний балл по каждому виду занятий для каждого предмета по всем учебным группам.

11. Сохраните книгу Работа 10.xls

 

Технология работы 11

1. Пуск → Все программы → Microsoft Excel. Переименуйте Лист1 на Структура, Лист2 — на Автоструктура,а Лист3 — на Итоги. Сохраните Книгу 1 в своей папке Работы по Excel под именем Работа 11. Файл → Сохранить как… →в строкеПапкаустановите свою папкуРаботы по Excel→в строкеИмя файлаустановите Работа11 →в строкеТип файлаустановите Книга Microsoft Excel → Сохранить

2. Скопируйте на лист Структура работы 11 исходную базу данных из работы 10 (рис.10.6а задания 10): откройте работы 10 выделите таблицу для учебных групп ® через контекстное меню скопируйте таблицу в буфер обмена ®откройте работу 11,лист Структура ® вставьте таблицу из буфера, начиная с ячейки А1 ® вернитесь в работу 10,лист Список и нажмите Esc ® вернитесь в работу 11,лист Структура. Отсортируйте строки списка по номеру учебной группы: отметьте любую ячейку таблицы ® Данные ® Сортировка ®Сортировать пономеру группы ® Ок. Вставьте пустые разделяющие строки между учебными группами:щелкните по ячейке строки с другим, отличным от предыдущей строки, номером группы® Вставка ® Строки

3.Создайте структурные части таблицы для учебных групп и для столбцов Код предмета, Таб. № препод., Вид занятий: выделите блок строк, относящихся к первой группе ® Данные ® Группа и Структура ® Группировать…® Группировать строки ® ОК.Аналогичные действия произведите для других групп и столбцов Код предмета, Таб. № препод., Вид занятий

4.Отмените структурирование: Данные ® Группа и Структура ® Разгруппировать. Повторите п.3 задания

5.Создайте таблицу расчета заработной платы ( рис. 11.2а), в которой значения в пустых ячейках вычислите по формулам:

• Подоходный налог = 0,13*3ар.плата

• Пенсионный фонд = 0,01* Зар.плата

• Общий налог = Подоходный налог + Пенсионный фонд

• Итого доплат = Надбавка + Премия

• Сумма к выдаче = Зар.плата - Общий налог + Итого доплат

•В строке Итого подсчитываются суммы по каждому столбцу

Формулы вводятся по методике, изложенной в задании 8.

6.Создайте автоструктуру таблицы расчета заработной платы: установите курсор в любую ячейку области данных ® Данные ® Группа и Структура ® Создать структуру .Появились поля (слева и сверху) с кнопками иерархических уровней и линиями, на концах которых находятся кнопки со знаками плюс и минус. Выделите столбцы от Подоходного налога до Итогов доплат и создайте для них дополнительный иерархический уровень. Сравните ваш результат с изображени­ем на рис. 11.3а

7.Скопируйте на лист Итогитаблицу из листаСтруктура; отмените структурирование. Отсортируйте записи списка по номеру группы, коду предмета, виду занятий: Данные ® Сортировка ®в строке Сортировать по выбрать имя поля Номер группы ®в строке Затем - Код пред­мета ®в строке В последнюю очередь по - Вид занятий ® ОК

8. Создайте 1 -и уровень итогов — средний балл по каждой учебной группе: установите курсор в произвольную ячейку списка ® Данные ® Итоги ®в диалоговом окне Промежуточные итоги укажите: При каждом изменении в - Номер группы,Операция - Среднее,Добавить итоги по - Оценка,Заменять текущие итоги – нет, Итоги под данными – да ® ОК. В результате будет рассчитан средний балл по каждой учебной группе.

9. Создайте 2-й уровень итогов — средний балл по каждому предмету для каждой учеб­ной группы: установите курсор в произвольную ячейку списка ® Данные ® Итоги ®в диалоговом окне Промежуточные итоги укажите: При каждом изменении в – Код предмета,Операция - Среднее,Добавить итоги по - Оценка,Заменять текущие итоги – нет, Конец страницы между группами - нет, Итоги под данными – да ® ОК. В результате будет рассчитан средний балл по каждому предмету каждой учебной группы

10. Создайте 3-й уровень итогов — средний балл по каждому виду занятий для каждого предмета по всем учебным группам: установите курсор в произвольную ячейку списка ® Данные ® Итоги ®в диалоговом окне Промежуточные итоги укажите: При каждом изменении в – Вид занятий,Операция - Среднее,Добавить итоги по - Оценка,Заменять текущие итоги – нет, Конец страницы между группами - нет, Итоги под данными – да ® ОК. В результате будет рассчитан средний балл по каждому виду занятий определен­ного предмета в учебной группе

11. Сохраните изменения в книге Работа 11.xls

 

 

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

1. Что такое структурирование таблицы?
2. Какие элементы таблицы составляют ее структуру?
3. Как выполнить структурирование таблицы ручным способом?
4. Что такое иерархические группы структуры?
5. Для каких таблиц и как выполняется автоструктурирование?
6. Перечислите функции, используемые при подведении автоматических итогов по группам

 

 

Задание 12

1. Запустите Excel 2000. Переименуйте Лист1 на Список продаж, Лист2 — на Свод_табл, Лист3 — на Конс_распол, Лист3 — на Конс_категор. Сохраните Книгу 1 в своей папке Работы по Excel под именем Работа 12.

2. На листе Список продаж создайте таблицу Телевизоры, аналогичную представленной на рис.12.11.

3. Создайте сводную таблицу и сравните ее с рис.12.12

 

Телевизоры
Фирма	Марка	Размер экрана	Цена	Поступил	Продано	Сумма
Sharp	14HSC			5 дек 03
Samsung	3382ZR			9 янв 04
Samsung	3385ZR			9 янв 04
Sony	14M1K			5 дек 03
Sony	14T1R			5 дек 03
Panasonic	1406RT			15 янв 04
Panasonic	14S1TCC			15 янв 04
Sharp	20HSC			5 дек 03
Samsung	5035ZR			9 янв 04
Samsung	5082ZR			9 янв 04
Hitachi				17 дек 03
Toshiba				17 дек 03
Sony	21M1K			5 дек 03
Sony	21T1R			5 дек 03
Panasonic				15 янв 04
Philips	21PT137A			17 дек 03
Всего

Рис.12.11.

 

Рис.12.12

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

5. На листе Конс_распол создайте таблицу Зарплата за январь, аналогичную представленной на рис.12.13. Подоходный налог, Сумма к выдаче и Итого вычислите по формулам. Создайте таблицу Зарплата за февраль, скопировав таблицу Зарплата за январь и внеся изменения в столбец Зарплата

Зарплата за январь		Зарплата за февраль
Фамилия	Зарплата	Подоходный налог	Сумма к выдаче		Фамилия	Зарплата	Подоходный налог	Сумма к выдаче
Иванов					Иванов
Петров					Петров
Сидоров					Сидоров
Смирнова					Смирнова
Итого					Итого

Рис.12.13.

6. Создайте таблицу Консолидированная зарплата за январь и февраль; сравните ее с рис.12.14

 

Консолидированная зарплата за январь и февраль
	Зарплата	Подоходный налог	Сумма к выдаче
Иванов
Петров
Сидоров
Смирнова
Итого

 

 

Рис.12.14

 

 

7. На листе Конс_ категор создайте таблицы Зарплата за январь и Зарплата за февраль, внеся в последнюю таблицу дополнительный столбец Премия(рис.12.15).Создайте таблицу Консолидированная зарплата за январь и февраль; сравните ее с рис.12.16

Зарплата за январь		Зарплата за февраль
Фамилия	Зарплата	Подоходный налог	Сумма к выдаче		Фамилия	Зарплата	Подоходный налог	Премия	Сумма к выдаче
Иванов					Иванов
Петров					Петров
Сидоров					Сидоров
Смирнова					Смирнова
Итого					Скворцов
					Итого

Рис.12.15

 

Консолидированная зарплата за январь и февраль
	Зарплата	Подоходный налог	Премия	Сумма к выдаче
Иванов
Петров
Сидоров
Смирнова
Скворцов
Итого

Рис.12.16

8. Сохраните изменения в файле Работа12.xls

Технология работы 12

1. Запустите Excel 2000. Переименуйте Лист1 на Список продаж, Лист2 — на Свод_табл, Лист3 — на Конс_распол, Лист3 — на Конс_категор. Сохраните Книгу 1 в своей папке Работы по Excel под именем Работа 12

2. На листе Список продаж создайте таблицу Телевизоры, аналогичную представленной на рис.12.11. (Можете скопировать эту таблицу с рисунка 12.11 задания на лист Список.Возможно, придется на листе Список убрать лишние строки)

3. Создайте сводную таблицу: Щелкните по любой ячейке таблицы на листе Список ® Данные ® Сводная таблица ® Окно Шаг 1 из 3: Далее ®Окно Шаг 2 из 3: Далее ® Окно Шаг 3 из 3: Установить переключатель Существующий лист ®Щелкнуть по ярлыку листа Свод_табл ® Щелкнуть по ячейке листа Свод_табл, с которой будет установлена сводная таблица ® Макет… ®Перетащить поля:

Фирма - на Страницу,