9.3. Работа с многостраничными книгами

В одной книге в разных таблицах (таблицы на разных рабочих листах) могут использоваться одни и те же данные и формулы. Например, можно ввести формулу в одну таблицу, которая ссылается на числовые данные другой таблицы. Если изменятся числовые данные в таблице, на которую ссылается эта формула, то Excel автоматически пересчитает результат формулы. Это средство, называемое трехмерным сшиванием, дает возможность «сшить» две таблицы или более для объединения данных и формул, а также позволяет объединить данные из нескольких таблиц в одну «главную» таблицу. Это в свою очередь позволяет разделять большие и сложные таблицы на меньшие по размерам и более удобные для обработки.

Примером «трехмерной» формулы может служить следующая формула:

=СУММ (Лист1:Лист3!A1:B2).

Эта «трехмерная» формула складывает числовые значения ячеек блоков А1:В2 листов, расположенных подряд в книге и имеющих названия Лист 1, …, Лист 3. При наборе этой формулы надо учитывать, что имена ячеек даже в русифицированных версиях Excel записываются латинскими буквами.

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

Для этого выполните следующие действия.

1) Войдите в среду MS Excel. Появившуюся на экране рабочую книгу (Книга 1) переименуйте в Session.xls.

Сделайте настройку новой книги, для этого выполните команду Сервис, Параметры.

Н а в к л а д к е Общие установите:

Стиль ссылок R1C1 – сбросить флажок;

Листов в рабочей книге – 5;

Стандартный шрифт – Arial Cyr, размер – 10.

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

Н а в к л а д к е Вид у становите флажки следующих параметров:

Отображать: строку формул, строку состояния, объекты;

Параметры окна: авторазбиение на страницы, сетка, заголовки строк и столбцов, горизонтальная и вертикальная полосы прокрутки, ярлычки листов.

Н а в к л а д к е Вычисления установите флажки следующих параметров:

Автоматически, Точность как на экране.

Н а в к л а д к е Правка установите флажки следующих параметров:

Правка прямо в ячейке, Перетаскивание ячеек, Переход к другой ячейке после ввода в направлении Вниз.

Далее периодически по мере выполнения задания записывайтерабочую книгу на диск (вручную или сделайте автосохранение, например,каждые 5 мин).

2) На первом рабочем листе создайте форму (т. е. таблицу, не содержащую пока сведений о студентах) экзаменационной ведомости в соответствии с рис. 6.

Для получения требуемой формы заполните ячейки в соответствии с рис. 7.

Далее отформатируйте ячейки А1:Е1. Для этого выделите этот блок ячеек, выполните команду Формат, ячейки и на вкладке Выравнивание выберите опции:

– по горизонтали – по центру выделения;

– по вертикали–по верхнему краю.

Выделите текст жирным шрифтом. Отформатируйте шапку таблицы. Измените в соответствии с заданием ширину столбцов.

ЭКЗАМЕНАЦИОННАЯ ВЕДОМОСТЬ

Группа 21-к Дисциплина ______________

№ п/п	Фамилия, имя, отчество	Номер зачетной книжки	Оценка	Подпись экзаменатора

«отлично» _________________

«хорошо» _________________

«удовлетворительно» _________

«неудовлетворительно» _______

И т о г о____________

Рис. 6. Вид экзаменационной ведомости

Адрес ячейки	Текст	Адрес ячейки	Текст
B1	ЭКЗАМЕНАЦИОННАЯ ВЕДОМОСТЬ	B5	Фамилия, имя, отчество
A3	Группа 21-к	C5	Номер зачетной книжки
C3	Дисциплина	D5	Оценка
A5	№ п/п	E5	Подпись экзаменатора

Рис. 7. Содержимое некоторых ячеек ведомости

3) Присвойте каждому студенту порядковый номер. Для этого в ячейку А6 введите цифру 1, а в А7 – 2. Далее выделите ячейки А6 и А7, затем протащите мышью маленький квадратик, расположенный в правом нижнем углу блока.

4) Заполните колонку «фамилия, имя, отчество» произвольным образом, но так, чтобы в списке были однофамильцы.

5) Переименуйте Лист 1 в Экзамен 1.

6) Произвольным образом по пятибалльной системе (используя оценки 2 – «неудовлетворительно», 3 – «удовлетворительно», 4 – «хорошо», 5 – «отлично») заполните ведомость.

7) Далее подсчитайте количество оценок 2, 3, 4, 5 и общее количество оценок по каждой дисциплине. Для этого используйте вспомогательные столбики: F (для пятерок), G (для четверок), H (для троек), I (для двоек). Для каждого студента в ячейках этих столбиков будут находиться три нуля и одна единица (в том столбике, который соответствует фактической оценке).

С помощью Мастер функций в ячейки F6:I6 введите формулы (рис. 8).

Ссылка	Формула	Ссылка	Формула
F6	Если (D6 = 5; 1; 0)	H6	Если (D6 = 3; 1; 0)
G6	Если (D6 = 4; 1; 0)	I6	Если (D6 = 2; 1; 0)

Рис. 8. Формулы в ячейках F6:I6

Далее скопируйте (используя мышь) эти формулы во все остальные ячейки дополнительных столбиков. Определите имена блоков ячеек по каждому дополнительному столбику. Например, для столбика F это надо сделать так. Выделите все значения дополнительного столбика (F6 – последняя ссылка). Затем введите команду Вставка, Имя, Присвоить, в диалоговом окне в строке Имя наберите слово «отлично» и щелкните по кнопке Добавить.

Затем выделите столбцы F – I и сделайте их скрытыми (команда контекстного меню Формат, Скрыть).

8) Введите названия итогового количества полученных оценок («отлично», «хорошо», «удовлетворительно», «неудовлетворительно») в столбец В. Введите в ячейки столбца С формулы для подсчета суммарного количества оценок определенного вида. Например, введите (с помощью Мастер функций и выделения имени блока ячеек «отлично») формулу СУММ («отлично»). Для подсчета общего количества оценок, полученных на данном экзамене, надо установить курсор в пустой ячейке, находящейся под ячейками, где подсчитывались суммы по всем видам оценок, щелкнуть по кнопке , выделить блок, где подсчитывались эти суммы и нажать клавишу ввода.

9) Сделайте две копии рабочего листа «Экзамен 1» с именами «Экзамен 2» и «Экзамен 3». Это можно сделать, щелкнув по ярлыку рабочего листа и используя далее контекстное меню. Заполните в ведомостях названия дисциплин (ячейка D3) на экзаменационных листах: «Информатика», «Математика», «Физика». Измените оценки в ведомостях по математике и физике произвольным образом. Рабочий лист с ведомостью по информатике будет иметь вид, который изображен на рис. 9.

Выполните командуСервис, Параметры, вкладка Вид, установите флажок Формулы. Результат должен иметь вид, изображенный на рис. 10. Снимите флажок Формулы.

Рис.9. Вид экзаменационной ведомости (флажок Формулы сброшен)

Рис. 10. Вид экзаменационной ведомости (флажок Формулы установлен)

10) Составьте ведомость назначения на стипендию. Пусть минимальная стипендия равна 200 р. Стипендия начисляется студентам, сдавшим все экзамены, так:

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

если средний балл по результатам сессии более 4,3, но менее либо равен 4,66, выплачивается минимальная стипендия.

Создайте лист Стипендия, на который из столбцов А и В листа Экзамен1 скопируйте фамилии и порядковые номера студентов.

В ячейку D2 введите минимальный размер стипендии. В ячейки C4, D4 введите заголовки «Средний балл», «Стипендия». С помощью Мастера функций (и щелкая в процессе диалога по ярлычкам листов и нужным ячейкам) введите в ячейку С5 формулу (для вычисления среднего балла для первого студента по списку):

=Срзнач(Экзамен1!D6; Экзамен2!D6; Экзамен3!D6).

Скопируйте формулу по всем ячейкам столбца С.

По ранее описанной методике введите расчетные формулы в столбец D. Например, в ячейке D5 должна появиться формула:

=Если(С5>4,66;$D$2*1,5; Если(C5>4,33;$D$2;0)).

В режиме отображения значений ведомость должна иметь вид, изображенный на рис. 11.

Рис. 11. Таблица «Стипендия» в режиме отображения значений

В режиме отображения формул экзаменационная ведомость должна иметь вид, изображенный на рис. 12, 13.

11) По трем экзаменационным ведомостям постройте столбчатую диаграмму, отражающую результаты сессии. При этом ряды выбираются по результатам трех экзаменов, а категориями являются номера студентов. По оси абсцисс укажите Ф.И.О. Улучшите внешний вид диаграммы в соответствии с возможностями программы.

Рис. 12. Таблица «Стипендия» в режиме отображения формул (столбцы А – С)

Рис. 13. Таблица «Стипендия» в режиме отображения формул (столбец D)