Лабораторная работа №3.
Рабочие листы книги Excel.
Цели работы:
1. Научиться пользоваться несколькими листами одной рабочей книги Excel для создания табличных документов.
2. Научиться выполнять с рабочими листами операции копирования, удаления, переименования.
3. Научиться осуществлять связь между листами с помощью путем ссылок в формулах.
4. Закрепить навыки по заполнению и форматированию электронных таблиц.
Краткие теоретические сведения
Книга – это документ Excel, который при сохранении на диск становится файлом с именем, указанным пользователем, и стандартным расширением.xls. Это имя – имя книги.
Книга Excel состоит из листов с именами Лист1, Лист2, … или с именами, заданными пользователем. Количество листов в книге и их последовательность можно менять.В рабочей книге может быть 255 листов. Лист может иметь 256 колонок и 65536 строк.
 |
Активный или текущий лист – это верхний (видимый) лист, в котором находится указатель активной ячейки. Для каждого листа в нижней части окна Excel имеется ярлычок (см.рис.1). Активизирует лист щелчок левой кнопкой мыши на его ярлычке. При этом лист перемещается наверх и становится видимым, а ярлычок ярким. Щелчок правой кнопкой на ярлычке вызывает контекстное меню для выполнения с листом перемещения, удаления, переименования и т. д.
Рис.1 Ярлыки рабочих листов и кнопки прокрутки.
В формулах могут использоваться данные:
1) одного (текущего) листа
2) нескольких листов одной книги
3) данные из другой книги, т.е. из другого файла.
Например, нужно сослаться на данные блока А6:С8 на Листе5 книги tovar.xls, находящейся в каталоге SKLAD диска С. Тогда:
А6:С8 - ссылка на блок из текущего листа
Лист5!А6:С8 – из любого листа данной книги tovar.xls
| С:\SKLAD\ | [tovar.xls] | Лист5! | А6:С8 | – из любой книги Excel |
| Путь доступа | Книга | Лист | Блок |
Операции с листами рабочей книги:
Выделение текущего листа – щелчок левой кнопкой мыши в верхнем левом углу рабочего листа.
Выделение листа – щелчки левой кнопки мыши по вкладкам нужных листов при нажатой клавише CTRL или SHIFT для смежных листов.
Удаление текущего листа – команда Правка/Удалить лист.
Удаление любого листа – щелчок правой кнопкой мыши на вкладке листа и затем команда Удалить из появившегося контекстного меню.
Вставка листа – команда Вставка/лист добавляет новый лист перед текущим листом. Вставлять листы можно путем копирования - команда Правка/Переименовать Скопировать лист... выводит на экран окно Переместить или скопировать. Для копирования нужно установить флажок Создать копию.
Переименование листа - щелчок правой кнопкой мыши на вкладке листа и затем команда Переименовать из появившегося контекстного меню. Затем в ярлыке листа ввести новое название – набор символов длиной до 31-го символа, кроме знаков *: / \? [ ].
Перемещение - щелчок правой кнопкой мыши на вкладке листа и затем команда Переместить/Скопировать. На экран выдается окно Переместить или скопировать, в котором в списке Перед листом выбирается лист, перед которым размещается перемещаемый.
Пример задания.
Создать книгу Excel для расчета стоимости закупленных товаров, содержащую 4 рабочих листа с таблицами по каждой группе товаров. Внешний вид рабочих листов представлен на рис. 4,5,6,7.
Порядок работы
Этап №1. Сформировать рабочую книгу Excel из 4-х листов. Для этого:
èСкопировать Лист3 в конец рабочей книги Excel с помощью контекстного меню. Для этого:
1) Вывести контекстное меню листа, щелкнув правой кнопкой мыши на ярлыке Листа3.
2) Выбрать пункт Переместить/Скопировать. На экране появится диалоговое окно Переместить или скопировать
3) В списке Перед листом выбрать В конец книги, установить флажок «Создавать копию». Результирующий вид окна Переместить или скопировать см. на рис.2.
4) Нажать кнопку ОК. В книге появится четвертый лист с именем Лист3(2).
èПереименовать Лист1. Для этого:
1) По аналогии с действиями выше вывести контекстное меню Лист1, выбрать пункт Переименовать. В ярлыке Лист1 появится текстовый курсор.
2) Вместо названия «Лист1» ввести «Канцтовары» и нажать Enter.
èПереименовать остальные листы: «Лист2» - «Оргтехника», «Лист3» – «Компьютеры», «Лист3(2)» – «Итого». Результирующий вид ярлыков в левом нижнем углу окна Excel представлен на рис.3.
Рис.3. Названия листов после переименования.
Этап №2. Оформление листов рабочей книги.
è Ввести данные на лист «Канцтовары» согласно рис.4.
è Заполнить ячейку С7 формулой автосуммы для сложения данных столбца «Стоимость».
|
1) Выделить ячейки А1:С7 на листе «Канцтовары».
2) Скопировать выделение в буфер.
3) Перейти на лист «Оргтехника», выполнив щелчок на ярлыке листа «Оргтехника» в нижней левой части окна Excel.
4) 
Активизировать ячейку А1 и вставить содержимое буфера.
5) Отредактировать содержимое листа «Оргтехника» согласно рис.5.
6) Ввести формулу для расчета итоговой стоимости в ячейку С7.
èПо аналогии оформить лист «Компьютеры» согласно рис.6.
èОформить лист «Итого» согласно внешнему виду, представленному на рис.7.
Этап №3. Вычисление итоговой стоимости товаров.
èВ ячейке В1 листа «Итого» подсчитать итоговую стоимость товара.
1) Для этого активизировать ячейку В1 и на панели инструментов нажать кнопку
f x для вызова мастера функций.
2) В открывшемся окне «Мастера функций – шаг 1 из 2» из списка «Категории» выбрать «Математические», из списка «Функции» - СУММ.
 |
3) Нажать ОК. Разворачивается панель ввода и изменения функций. Примерный вид представлен на рис.8.
Рис.8. Итоговый вид панели ввода функции СУММ с данными для расчета
итоговой стоимости товаров.
4) В панели ввода для функции СУММ в строке «Число1» указать диапазон ячеек, содержащих цену канцтоваров следующим образом: нажать кнопку сворачивания панели (см.рис.8). Окно уменьшается в размере до одной строки. Далее мышью перейти на лист «Канцтовары» и выделить диапазон С3:С6, содержащий стоимости товаров. Адрес диапазона с указанием на название листа поместится в строку панели ввода функций.
5) Развернуть окно ввода функции СУММ нажатием на кнопку, расположенную в оставшейся от панели ввода функции строке.
6) Перейти в строку «Число2» и по аналогии заполнить ее ценами оргтехники с листа «Оргтехника».
7) Перейти в строку «Число3» и по аналогии заполнить ее ценами компьютеров с листа «Компьютеры». Получившийся вид панели ввода функции СУММ представлен на рис.8.
 |
8) Нажать ОК. Результирующий вид листа «Итого» приведен на рис.9.
Рис.9. Вид листа «Итого» после ввода формулы для расчета
итоговой стоимости товаров.
Индивидуальные задания.
1. Составить и оформить документ Excel согласно индивидуальному варианту.
2. Сохранить документ под именем «Лаб3+<Фамилия>» в созданной ранее папке.
Вариант 1.
Задание: Пользуясь возможностями Excel создать рабочую книгу из 6 листов для расчета среднегодовой стоимости имущества.
Примечание. Символом è помечено задание для выполнения.
Исходные данные и порядок работы: Пусть среднегодовая стоимость имущества предприятия F вычисляется по формуле 
, где
A – стоимость имущества предприятия на 1.01.02
В – стоимость имущества предприятия на 1.04.02
С – стоимость имущества предприятия на 1.07.02
D – стоимость имущества предприятия на 1.10.02
E – стоимость имущества предприятия на 1.01.03
Расчет каждой величины будем производить на отдельном листе книги Excel.
èСоздать с помощью команды «Вставка/Лист» книгу Excel из 6 листов. Задать названия листов согласно рис. 10:
Рис.10. Названия листов рабочей книги.
èСоздать внешний вид листа «1.01.02» согласно рис11.
Рис.11. Исходный вид листа «1.01.02»
èПроизвести расчет износа и остаточной стоимости основных средств исходя из следующих соображений.
К имуществу предприятия относятся:
I. Основные средства предприятия: Это в таблице позиции 1-4 (строки более темного фона). При копировании формулы учесть разные проценты износа и срок эксплуатации.
1. Компьютер. Срок эксплуатации на 1.01.02 – 2 года. Его износ составляет 12,5% годовых (годовой износ).
2. Автомобиль грузоподъемностью до 0,5 т. Срок эксплуатации на 1.01.02 – 3 года. Его годовой износ - 20%.
3. Автомобиль грузоподъемностью от 0,5 т до 2 т. Срок эксплуатации на 1.01.02 – 5 лет. Его годовой износ - 14,3%.
4. Здание. Срок эксплуатации на 1.01.02 – 10 лет. Его годовой износ - 1,2%.
Для основных средств рассчитывается: износ (столбец Е, ячейки Е3-Е6) и остаточная стоимость (столбец С, ячейки С3-Е6) по формулам:
Износ основных средств = первоначальная стоимость * срок эксплуатации * годовой износ
Основные средства по остаточной стоимости = первоначальная стоимость – износ
Пример формулы с умножением на проценты для расчета износа компьютера: =D3*2*12,5%.
II. Материалы. Это доска обрезная, уголок алюминиевый, железо оцинкованное (в таблице по позиции 5-7). Для них в расчете используется только стоимость (колонка «Материалы»).
III. Малоценные и быстроизнашиваемые предметы (сокращение МБП). Это в таблице позиции 8-10 (строки белее светлого фона), содержащие халаты, калькулятор, телефон.
Для МБП рассчитывается износ (ячейки I10-I12) и остаточная стоимостьМБП (ячейки G10-G12) по формулам:
Износ МБП = первоначальная стоимость * 50%
Остаточная стоимость МБП = первоначальная стоимость МБП – износ
IV. Товары. Это в таблице позиции 11-14. Для них рассчитывается общая цена книг каждого наименования (столбец «Товары»):
Товары = кол-во * цена за единицу
èПроизвести расчет в ячейке Н18 стоимости имущества предприятия на 01.01.02г. с использованием функции СУММ и панели ввода функций (см. рис.8), в которой заполнить четыре строки.
Стоимость имущества предприятия на 01.01.02 = СУММ(основные средства по остаточной стоимости, материалы, остаточная стоимость МБП, товары)
èСоздать внешний вид листа «1.04.02» копированием содержимого листа «1.01.02г.» и редактированием его согласно рис.12. Редактирование происходит с учетом того, что:
Рис.12. Итоговый внешний вид листа «1.04.02»
1. Списаны материалы на ремонт помещения (удалить соответствующие строки, исправить нумерацию позиций).
2. Продано книг «Работа в Excel 7.0» 73 шт, приобретено для дальнейшей реализации 120 шт. Ввести формулу в ячейку К10 следующим образом: набрать в ячейке знак равенства, мышью перейти на лист «1.01.02» и щелкнуть ячейку с количество книг «Работа в Excel 7.0» и сразу же в строке формул дописать «-73+120» и нажать Enter. Произойдет возврат на лист «1.04.02г.». Должна получиться формула: =’1.01.02’! К13-73+120
3. Продано книг «Office 97 в целом» 96 шт, приобретено 145 шт. Формулу ввести аналогичным образом.
èОтредактировать формулу расчета стоимости имущества предприятия на 01.04.02 – удалить ссылку на ячейки списанных материалов.
èСоздать внешний вид листа «1.07.02г.» копированием содержимого листа «1.04.02г.» и редактированием его согласно рис.13. Редактирование происходит с учетом того, что:
Рис.13. Итоговый внешний вид листа «1.07.02»
1. Продан автомобиль грузоподъемностью до 0,5 т. (удалить строку, исправить нумерацию позиций)
2. Приобретена офисная мебель стоимостью 15485 р. (отнести в МБП, добавить строку, заполнить ее соответствующими данными и формулами).
èОтредактировать формулу расчета стоимости имущества предприятия на 01.07.02 – отредактировать диапазон ячеек, содержащих остаточную стоимость МБП, т.к. была добавлена строка.
èСоздать внешний вид листа «1.10.02г.» копированием содержимого листа «1.07.02г.» и редактированием его согласно рис.14. Редактирование происходит с учетом того, что:
Рис.14. Итоговый внешний вид листа «1.10.02»
1. Продано книг «Работа в Excel» – 90 шт., приобретено – 55 шт. (отредактировать формулу расчета количества книг по аналогии с подобной формулой листа «1.04.02»).
2. Приобретен телефон «Panasonic» стоимостью 7300 р. (отнести в МБП, добавить строку, заполнить ее соответствующими данными и формулами).
èОтредактировать формулу расчета стоимости имущества предприятия на 01.10.02 – отредактировать диапазон ячеек, содержащих остаточную стоимость МБП, т.к. была добавлена строка.
èСоздать внешний вид листа «1.01.03г.» копированием содержимого листа «1.10.02г.» и редактированием его согласно рис.15. Редактирование происходит с учетом того, что:
Рис.15. Итоговый внешний вид листа «1.01.03»
1. Списаны халаты и приобретена униформа в количестве 15 шт. по цене 345 р. (в строке «Халаты» изменить данные, формула в столбце МБП = 15*345)
2. Увеличился износ основных средств и здания на 1 год (отредактировать формулу износа)
èОтредактировать формулу расчета стоимости имущества предприятия на 01.01.03 – отредактировать диапазон ячеек, содержащих остаточную стоимость МБП, т.к. была добавлена строка.
èНа листе «Среднегодовая стоимость имущества» произвести расчет среднегодовой стоимости имущества F по приведенной в задании формуле с использованием функции СУММ и панели ввода функции, указывая мышью ячейки со стоимостью имущества на заданные даты. Внешний вид рабочего листа «Среднегодовая стоимость имущества» представлен на рис.16.
Рис.16. Итоговый вид листа «Среднегодовая стоимость имущества»
Вариант 2.
Задание: Пользуясь возможностями Excel, создать рабочую книгу из 6 листов, содержащих листы классного журнала. Первые 4 листа содержат листы журнала по предметам. На каждом из них имеются:
1. Список класса.
2. Текущие оценки.
3. Итоговая оценка за четверть.
Пятый лист содержит ведомость итоговых оценок за четверть, заполненная оценками с использованием ссылок на соответствующие листы по предметам.
Шестой лист содержит сведения об учащихся.
Примечание. Символом è помечено задание для выполнения.
Правила расчета
1 четверть = среднее значение по строке ученика.
Исходные данные и порядок работы.
èСоздать с помощью команды «Вставка/Лист» книгу Excel из 6 листов. Задать названия листов согласно рис. 17:
Рис.17.Названия листов классного журнала
èСоздать внешний вид листа «Сведения» согласно рис.18 на 10 учащихся.
èСоздать лист «Русский язык» на 10 учащихся согласно рис.18.
Рис.18. Исходный вид листа предмета.
èЗадать связь между листами «Сведения» и «Русский язык» для выбора фамилии первого ученика: в ячейке с фамилией первого ученика ввести знак равенства, перейти с помощью мыши на лист «Сведения», щелкнуть мышью ячейку с фамилией первого ученика (в строку формул помещается ссылка на эту ячейку) и затем нажать Enter.
èРазмножить полученную формулу на столбец «Фамилия имя ученика».
èЗаполнить листы сведениями об оценках и посещаемости.
èВ столбце «1 четверть» для первого ученика ввести формулу для подсчета среднего балла по соответствующей ему строке, используя функцию СРЗНАЧ и панель ввода функций (см.рис.8). Для этой ячейки задать числовой формат без знаков после запятой.
èЗаполнить листы остальных 3-х предметов путем копирования и последующего редактирования содержимого листа «Русский язык» на остальные листы.
èЗаполнить лист «Ведомость» согласно рис.19.
Рис.19. Исходный вид листа «Ведомость»
èПо аналогии с листами предметов заполнить столбец «Фамилия имя» фамилиями учащихся с листа «Сведения».
èСтолбцы предметов заполнить ссылками на ячейки с четвертными оценками соответствующих листов предметов.
èДанные столбца «1 четверть» рассчитать как среднее значение итоговых оценок каждого учащегося по предметам с помощью функции СРЗАЧ.
èПроверить связь листов: на листе «Русский язык» изменить текущие оценки одному из учеников так, чтобы изменилась четвертная оценка. Затем перейти на лист «Ведомость» и убедиться, что новая оценка появилась в ведомости и отразилась на значении среднего балла.
Вариант 3.
Задание: Пользуясь возможностями Excel, создать рабочую книгу из 5 листов, содержащих расписание занятий 3-х классов школы.
Первые 3 листов содержат расписание уроков классов каждого класса.
Четвертый лист содержит сводное расписание уроков.
Пятый лист содержит расписание звонков.
Примечание. Символом è помечено задание для выполнения.