Рабочая книга 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