Практическая работа на тему:"Рабочая книга Excel. Связь листов."

Практическая работа № 9 - 9

Тема: Рабочая книга Excel. Связь листов.

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

Путь к файлам: Сетевое окружение\238-dc\SharedFolder\excel\jurnal.xls

Листы рабочей книги

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

Задание 1. Заполнить таблицу согласно образцу.

Задание 2. Для чисел в ячейках, содержащих даты проведения занятий, задать формат представления даты дд.мм.

Задание 3. Итоговую оценку в столбце L вычислить как среднее арифметическое, используя функцию СРЗНАЧ.

• Поместить курсор в ячейку L3.

• Ввести выражение: =СРЗНАЧ(С3:К3).

• Используя функцию автозаполнения скопировать формулу в диапазон ячеек L4:L7.

Задание 4. Установить формат представления данных в ячейках столбца L – Числовой без десятичных знаков.

• Выделить диапазон ячеек L3:L7.

• В меню Формат выбрать команду Ячейки.

• Активизировать вкладку Число.

• Выбрать формат представления данных Числовой.

• В поле Число десятичных знаков установить значение 0.

• Щёлкнуть по кнопке ОК. Функция должна возвратить все целые значения.

Задание 5. На листах рабочей книги Excel 2 и 3 создать странички для предметов: алгебра, геометрия.

• Выделить всю таблицу (диапазон ячеек A1:L7).

• В меню Правка выбрать команду Копировать.

• Перейти на Лист2.

• В меню Правка выбрать команду Вставить.

• Перейти на Лист3.

• Вновь выполнить команду Вставить из меню Правка.

Задание 6. Переименовать листы Лист1 в литература, Лист2 в алгебра, Лист3 в геометрия.

1. Дважды щёлкнуть на ярлычке соответствующего листа и задать в диалоговом окне новое имя.

2. Щёлкнуть по ярлычку правой кнопкой мыши и в открывшемся контекстно-зависимом меню выбрать команду Переименовать.

Задание 7. На листах алгебра и геометрия в таблицах соответственно изменить: названия предметов, оценки.

Задание 8. Создать новый лист рабочей книги Excel.

1. Щёлкнуть правой кнопкой по ярлычку последнего листа и в контекстном меню выбрать команду Добавить.

2. В открывшемся диалоговом окне Вставка на вкладке Общие выбрать ярлычок Лист и щёлкнуть по кнопке ОК.

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

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

Задание 9. Используя функцию связи со ссылкой внести в столбец А вновь созданного листа, фамилии учащихся, взятые из листа литература рабочей книги Excel.

• Переименовать вновь созданный лист в 1 четверть.

• В ячейку А2 занести формулу: =литература!B2 (литература! - ссылка на другой лист, символ ! обязателен. B2 - адрес ячейки на листе Литература).

• Используя функцию автозаполнения скопировать формулу в ячейки А3:А7.

Задание 10. Оформить столбец B формулами, считывающими оценки за 1 четверть по предмету литература.

• В ячейку B2 занести формулу: =литература!L3.

• Скопировать формулу в ячейки диапазона B3:B7. Столбец должен заполниться оценками за 1 четверть по литературе, т.о. будет установлена связь между листом 1четверть и листом литература.

Задание11. Выполнить самостоятельно. Оформить столбцы С и D формулами, по которым будут считываться оценки за 1 четверть с листов алгебра и геометрия.

Ведомость должна выглядеть так:

Задание 12. Сохранить файл под именем журнал.

Работа с несколькими окнами

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

Задание 13. Вывести на экран сразу два документа.

• Открыть для просмотра ещё одно окно, выбрав в меню Окно команду Новое.

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

• Выполнить последовательно команды меню: Окно äРасположить ä Расположить рядом. На экране должно разместиться 2 окна, одно из которых будет активным.

Задание 14. Проверить как работает связь таблиц.

• На листе Литература Руслановой Галине исправить две оценки 3 на 4

Обратить внимание, что на листе 1четверть изменилась итоговая оценка с 3 на 4.

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

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

Задание 15. Скопировать файл jurnal.xls в собственную папку.

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

• Поместить курсор в ячейку Е3.

• Ввести формулу:=’d:\название папки\[jurnal.xls]русский’!L3

Знак ‘ (апострoф) находится на той же клавише, что и русская буква Э. Чтобы его набрать следует переключиться в режим ввода английских символов.

=’d:\папка\[jurnal.xls]русский’! - путь к файлу jurnal.xls и листу .

• Скопировать формулу в диапазон ячеек B3:B7 столбца Е. Столбец должен заполниться итоговыми оценками по русскому языку.

Задание 17. Выполнить самостоятельно. Внести в ведомость итоговых оценок оценки по географии, взяв их из файла jurnal.xls.