Рабочая книга Excel. Связь таблиц

Цели работы:

- научиться использовать несколько листов рабочей книги;

- научиться осуществлять связь между листами одной рабочей книги и связь между файлами.

Постановка задачи.

Необходимо создать классный журнал. Для простоты ограничимся тремя предметами: литература, алгебра, геометрия. Отдельный лист рабочей книги отводится для каждого предмета. Он содержит список класса (ограничимся пятью учениками), текущие оценки и итоговую оценку за 1-ю четверть. На отдельном листе должна быть представлена ведомость итоговых оценок за 1-ю четверть, которая должна быть заполнена оценками с использованием ссылок на соответствующие листы по предметам.

Методические указания

ЗАДАНИЕ 1. На Листе 1 заполните и оформите таблицу 8.1.

Таблица 8.1.

Литература
№	Фамилия учащегося	2.09	9.09	16.09	23.09	30.09	7.10	14.10	21.10	28.10	1 четв.
	Арбузов Артем
	Иванов Игорь
	Орлова Ольга
	Попов Олег
	Русланова Галина

Для чисел в ячейках, содержащих даты проведения занятий, задайте формат Дата (Код: Д.ММ). Оценки за I четверть вычислите по формуле как среднее арифметическое текущих оценок, используя функцию СРЗНАЧ и ОКРУГЛ - чтобы округлить среднее значение до целого числа.

ЗАДАНИЕ 2. Сохраните таблицу под именем jurnal.xls.

ЗАДАНИЕ 3. Создайте аналогичные листы для предметов алгебра и геометрия, для чего:

3.1. Скопируйте таблицу Литература на следующий лист, используя команды меню: Правка, Переместить/скопировать..., Перед листом <Лист2>, Создать копию [х]. После выполнения команды появится лист <Лист 1 [2]>.

3.2. Скопируйте таблицу еще раз, используя те же команды меню. После выполнения этой команды появится лист <Лист 1[3]>.

ЗАДАНИЕ 4. Переименуйте листы: <Лист 1> - в <Литература>, <Лист 1 [2]> - в <Алгебра>, <Лист 1 [3]> в <Геометрия>.

Для этого дважды щелкните на ярлычке соответствующего листа и задайте в диалоговом окне новое имя. Можно один раз щелкнуть по ярлычку правой кнопкой мыши и открыть контекстно-зависимое меню, в котором выбрать пункт Переименовать.

ЗАДАНИЕ 5. На листах <Алгебра> и <Геометрия> в таблицах соответственно измените название предметов, текущие оценки, даты.

ЗАДАНИЕ 6. На <Листе 2> создайте таблицу – «Ведомость итоговых оценок за I четверть» (табл. 8.2), для чего:

6.1. Переименуйте <Лист 2> в лист <1 четверть>

6.2. Заполните таблицу ссылками на соответствующие ячейки других листов:

§ В ячейку А2 занесите формулу =Литература!А2. Литература! - ссылка на другой лист, символ «!» обязателен. А2 - адрес ячейки на листе <Литература>, используется относительная адресация.

§ Размножьте формулу на последующие 5 ячеек столбца А и соответствующие ячейки столбца В. В ведомости заполнятся колонки № и Фамилия учащегося.

§ В ячейку СЗ занесите формулу =Литература!L3.

§ Размножьте формулу на последующие 4 ячейки столбца.

Столбец заполнился оценками за I четверть по литературе.

Таким образом будет установлена связь между листом <1 четверть> и листом <Литература>.

ЗАДАНИЕ 7. Удалите листы, которые не будут использоваться в рабочей книге.

Для удаления листа выберите команду Удалить из контекстно-зависимого меню для ярлычков. Для удаления сразу нескольких рабочих листов предварительно выделите их при нажатой клавише <Ctrl>.

ЗАДАНИЕ 8. Проверьте правильность заполнения таблицы.

8.1.Откройте для просмотра еще одно окно. Выполните команды меню: Окно, Новое.

8.2. В новом окне выберите рабочий лист <Литература>.

8.3. Выполните команды меню: Окно, Расположить.... Рядом [•].

При открытии нескольких окно активным всегда является только одно окно. Для активизации другого окна нужно щелкнуть по нему мышью,

ЗАДАНИЕ 9. Проверьте, как работает связь таблиц.

9.1. На листе <Литература> Руслановой Галине исправьте две текущие оценки 3 на 4.

Внимание! Изменилась итоговая оценка Руслановой Галины за I четверть, как на листе <Литература>, так и на листе <1 четверть>.

9.2. Исправьте текущие оценки Руслановой Галине опять на 3. Таким образом, связь между различными листами одной рабочей книги действует.

ЗАДАНИЕ 10. Осуществите связь между листами разных рабочих книг. Заполните столбец в Ведомости оценками по литературе, взяв их из файла jurnal.xls.

10.1. Очистите от оценок столбец Литература, используя команды меню: Правка. Очистить, Все.

10.2. В ячейку СЗ занесите формулу: 'C:\Student\[ jurnal.xls]Литература'!L3

'C:\Student\[jurnal.xls]Литература' - путь к файлу jurnal.xls и листу <Литература>. Путь обязательно указывается в одинарных кавычках. Имя файла обязательно в квадратных скобках. В пути должен быть указан ваш рабочий диск и ваш личный каталог.

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

ЗАДАНИЕ 11. Самостоятельно заполните ведомость оценок за I четверть по предметам алгебра и геометрия.

ЗАДАНИЕ 12. Раскройте еще два окна и разместите в них листы <Алгебра> и <Геометрия>, расположите окна рядом.

ЗАДАНИЕ 13. Закройте все окна, кроме окна <Литература>. Разверните это окно на весь экран.

ЗАДАНИЕ 14. На листе <Литература> напечатайте список учеников, которые закончили I четверть с оценкой 5,4,3 по предмету:

14.1. На листе <Литература> в ячейку А10 введите текст: "Получили оценку 5:".

14.2. Скопируйте этот текст в ячейки А17 и А24.

14.3. В ячейке А17 измените текст на: "Получили оценку 4:", а в ячейке А24 на: "Получили оценку 3:".

14.4. С использованием Автофильтра выберите записи с итоговой оценкой 5 за I четверть.

14.5. Выделите фамилии учеников и скопируйте их в 11-ю строку в столбец В.

14.6. С ячеек с фамилиями, которые были только что скопированы, снимите обрамление и фон.

14.7. Аналогичные действия произведите для учеников, которые получили оценку 3 и 4 (рис. 5.)

14.8. Отмените Автофильтр, выполнив команды: Данные, Фильтр, Автофильтр. В результате всех действий лист <Литература> будет иметь следующий вид:

Таблица 8.3.

Литература
№	Фамилия учащегося	2.09	9.09	16.09	23.09	30.09	7.10	14.10	21.10	28.10	1 четв.
	Арбузов Артем
	Иванов Игорь
	Орлова Ольга
	Попов Олег
	Русланова Галина
Получили оценку 5:
	Иванов Игорь
	Попов Олег
Получили оценку 4:
	Арбузов Артем
	Орлова Ольга
Получили оценку 3:
	Русланова Галина

ЗАДАНИЕ 15. Сохраните таблицу под именем lab8.xls. Предъявите преподавателю файлы: jurnal.xls и lab8.xls.

Лабораторная работа №9