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

Цели работы:

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

До сих пор вы работали только с одним листом рабочей книги. Часто бывает полезно использовать несколько рабочих листов.

В нижней части экрана видны ярлычки листов. Если щелкнуть на ярлычке левой клавишей мыши, то указанный лист становится активным и перемещается наверх. Щелчок правой кнопкой на ярлычке вызовет контекстно-зависимое меню для таких действий с листом, как перемещение, удаление, переименование и т.д.

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

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

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

ХОД РАБОТЫ

Задание 1. На < Листе 1> заполните и оформите таблицу согласно рисунку:

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

Задание 2. Сохраните таблицу в личном каталоге рабочего диска под именем jurnal.xks.

Задание 3. Создайте аналогичные листы для предметов алгебра и геометрия, для чего:
3.1.Скопируйте таблицу Литература на следующий лист, используя команды меню: Правка – Переместить/скопировать

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

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

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

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

Связь рабочих листов

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

Задание 6. На < Листе 2> создайте таблицу – Ведомость итоговых оценок за 1 четверть, для чего:
6.1. Переименуйте < Лист 2> в Лист < 1 четверть>
6.2.Заполните таблицу ссылками на соответствующие ячейки других листов:

В ячейку А2 занесите формулу = Литература! А2

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

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

В ведомости заполнятся колонки № и Фамилия учащегося.

В ячейку СЗ занесите формулу =Литература! L3
Размножьте формулу на последующие 4 ячейки столбца.

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

Задание 7. Удалите листы, которые не будут использоваться, в рабочей книге (3 – 16).

Для удаления листа выберите команду Удалить из контекстно-зависимого для ярлычков. Для удаления сразу нескольких рабочих листов предварительно выделите их при нажатой клавише < Cirl>.
Пока информация рабочего листа занимает один экран, достаточно одного окна. Если это не так, то можно открыть несколько окон и одновременно отслеживать на экране разные области рабочего файла.

В нашем примере это также удобно сделать, расположив в разных окнах разные рабочие листы.

Задание 8. Проверьте правильность заполнения таблицы.

8.1. Откройте для просмотра еще одно окно. Выполните команды меню: Окно - Новое окно.
8.2. В новом окне выберите рабочий лист < Литература>.
8.3. Выполните команды меню: Окно - Упорядочить окна - Упорядочить, (*) каскадом.

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

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

Задание 9. Проверьте, как работает связь таблиц.
9.1. На листе < Литература> Руслановой Галине исправьте две текущие оценки 3 на 4.
Внимание! Изменилась итоговая оценка Руслановой Галины за 1 четверть, как на листе < Литература>, так и на листе < 1 четверть>.
9.2. Исправьте текущие оценки Руслановой Галине опять на 3.
Таким образом, связь между различными листами одной рабочей книги действует.

Связь между файлами

Связь между двумя файлами достигается за счет введения в - один файл формулы связи со ссылкой на ячейку в другом файле. Файл, который получает данные из другого, называется файлом назначения, а файл, который отдает данные, - файлом-источником.

Как только связь устанавливается, Excel копирует величину из ячейки в файле-источнике в ячейку - файла назначения. Величина в ячейке назначения автоматически обновляется.

Задание 10. Осуществите связь между листами разных рабочих книг. Заполните столбец в Ведомости оценками по литературе, взяв их из файла jurnal.xls.
10. 1. Очистите от оценок столбец Литература, используя команды меню: Правка – Очистить содержимое.
10.2.В ячейку СЗ занесите формулу: ='A: \PETROV\[jurnal1.хls]Литература ‘! L3,

'A: \PETROV\[jurnal1.хls]Литература ‘! L3 - путь к файлу jurnal.xls и листу < Литература>. Обязательно в «». Имя файла обязательно в квадратных скобках. В пути должен быть указан ваш рабочий диск и ваш личный каталог.
10.3. Размножьте формулу на последующие 4 ячейки столбца.
Столбец заполнился оценками по литературе, т.е., связь установлена.

Задание 11. Самостоятельно заполните ведомость 1 четверть по предметам алгебра и геометрия.

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

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

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

14.1. На листе < Литература>. В ячейку А10 введите текст: «Получили оценку 5: »
14.2. Скопируйте это текст в ячейки А17 и А24.
14.3. В ячейке А17 измените текст на: «Получили оценку 4: », а в ячейке А24 на: «Получили оценку 3: ».
14.4. С использованием Автофильтра выберите записи с итоговой оценкой 5 за 1 четверть.
14.5. Выделите фамилии учеников и скопируйте их в 11-ю строку в столбце В.
14.6. С ячеек с фамилиями, которые были только что скопированы, снимите обрамление и фон.
14.7.Аналогичные действия произведите для учеников, которые получили оценку 3 и 4.
14.8.Отмените Автофильтр, выполнив команду: Данные – Фильтр – Автофильтр.
В результате всех действий лист < Литература> будет иметь вид:

Задание 15. Сохраните таблицу на диске в личном каталоге под именем work7.xls.

Задание 16. Распечатайте лист < Литература>, предварительно сняв колонтитулы. Меню: Файл - Печать, (*) - выделенные листы.

Задание 17. Подведите итоги.

Проверьте:

знаете ли вы, что такое:

рабочая книга Excel;
рабочий лист;
правила записи формул для связи рабочих листов;
упорядочивание окон каскадом;

· упорядочивание окон мозаикой.

умеете ли вы:

вставлять и удалять рабочий лист;
переименовывать, перемещать и копировать рабочие листы;
открывать, закрывать и упорядочивать окна;

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

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

⇐ Предыдущая 1 2 3 4 567 8 9 10 Следующая ⇒