|
|
лр. Лабораторная работа 3 Листы. Лабораторная работа 3. Рабочие листы книги Excel. Цели работы
Лабораторная работа №3. Рабочие листы книги Excel.
Цели работы:
Научиться пользоваться несколькими листами одной рабочей книги Excel для создания табличных документов.
Научиться выполнять с рабочими листами операции копирования, удаления, переименования.
Научиться осуществлять связь между листами с помощью путем ссылок в формулах.
Закрепить навыки по заполнению и форматированию электронных таблиц.
Краткие теоретические сведения
Книга – это документ Excel, который при сохранении на диск становится файлом с именем, указанным пользователем, и стандартным расширением .xls. Это имя – имя книги.
Книга Excel состоит из листов с именами Лист1, Лист2, … или с именами, заданными пользователем. Количество листов в книге и их последовательность можно менять.В рабочей книге может быть 255 листов. Лист может иметь 256 колонок и 65536 строк.
А
ктивный или текущий лист – это верхний (видимый) лист, в котором находится указатель активной ячейки. Для каждого листа в нижней части окна Excel имеется ярлычок (см.рис.1). Активизирует лист щелчок левой кнопкой мыши на его ярлычке. При этом лист перемещается наверх и становится видимым, а ярлычок ярким. Щелчок правой кнопкой на ярлычке вызывает контекстное меню для выполнения с листом перемещения, удаления, переименования и т. д.
Рис.1 Ярлыки рабочих листов и кнопки прокрутки.
В формулах могут использоваться данные:
одного (текущего) листа
нескольких листов одной книги
данные из другой книги, т.е. из другого файла.
Например, нужно сослаться на данные блока А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 с помощью контекстного меню. Для этого:
Вывести контекстное меню листа, щелкнув правой кнопкой мыши на ярлыке Листа3.
Выбрать пункт Переместить/Скопировать. На экране появится диалоговое окно Переместить или скопировать
В списке Перед листом выбрать В конец книги, установить флажок «Создавать копию». Результирующий вид окна Переместить или скопировать см. на рис.2.
Нажать кнопку ОК. В книге появится четвертый лист с именем Лист3(2).
Переименовать Лист1. Для этого:
По аналогии с действиями выше вывести контекстное меню Лист1, выбрать пункт Переименовать. В ярлыке Лист1 появится текстовый курсор.
Вместо названия «Лист1» ввести «Канцтовары» и нажать Enter.
Переименовать остальные листы: «Лист2» - «Оргтехника», «Лист3» – «Компьютеры», «Лист3(2)» – «Итого». Результирующий вид ярлыков в левом нижнем углу окна Excel представлен на рис.3.
Рис.3. Названия листов после переименования.
Э тап №2. Оформление листов рабочей книги.
Ввести данные на лист «Канцтовары» согласно рис.4.
Заполнить ячейку С7 формулой автосуммы для сложения данных столбца «Стоимость».
Рис.4. Вид листа «Канцтовары»
 Путем копирования содержимого листа «Канцтовары» на лист «Оргтехника» оформить его путем редактирования согласно рис.5. Для этого:
Выделить ячейки А1:С7 на листе «Канцтовары».
Скопировать выделение в буфер.
Перейти на лист «Оргтехника», выполнив щелчок на ярлыке листа «Оргтехника» в нижней левой части окна Excel.
А ктивизировать ячейку А1 и вставить содержимое буфера.
Отредактировать содержимое листа «Оргтехника» согласно рис.5.
Ввести формулу для расчета итоговой стоимости в ячейку С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.01.02 – 2 года. Его износ составляет 12,5% годовых (годовой износ).
Автомобиль грузоподъемностью до 0,5 т. Срок эксплуатации на 1.01.02 – 3 года. Его годовой износ - 20%.
Автомобиль грузоподъемностью от 0,5 т до 2 т. Срок эксплуатации на 1.01.02 – 5 лет. Его годовой износ - 14,3%.
Здание. Срок эксплуатации на 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»
Списаны материалы на ремонт помещения (удалить соответствующие строки, исправить нумерацию позиций).
Продано книг «Работа в Excel 7.0» 73 шт, приобретено для дальнейшей реализации 120 шт. Ввести формулу в ячейку К10 следующим образом: набрать в ячейке знак равенства, мышью перейти на лист «1.01.02» и щелкнуть ячейку с количество книг «Работа в Excel 7.0» и сразу же в строке формул дописать «-73+120» и нажать Enter. Произойдет возврат на лист «1.04.02г.». Должна получиться формула: =’1.01.02’! К13-73+120
Продано книг «Office 97 в целом» 96 шт, приобретено 145 шт. Формулу ввести аналогичным образом.
Отредактировать формулу расчета стоимости имущества предприятия на 01.04.02 – удалить ссылку на ячейки списанных материалов.
Создать внешний вид листа «1.07.02г.» копированием содержимого листа «1.04.02г.» и редактированием его согласно рис.13. Редактирование происходит с учетом того, что:
Рис.13. Итоговый внешний вид листа «1.07.02»
Продан автомобиль грузоподъемностью до 0,5 т. (удалить строку, исправить нумерацию позиций)
Приобретена офисная мебель стоимостью 15485 р. (отнести в МБП, добавить строку, заполнить ее соответствующими данными и формулами).
Отредактировать формулу расчета стоимости имущества предприятия на 01.07.02 – отредактировать диапазон ячеек, содержащих остаточную стоимость МБП, т.к. была добавлена строка.
Создать внешний вид листа «1.10.02г.» копированием содержимого листа «1.07.02г.» и редактированием его согласно рис.14. Редактирование происходит с учетом того, что:
Рис.14. Итоговый внешний вид листа «1.10.02»
Продано книг «Работа в Excel» – 90 шт., приобретено – 55 шт. (отредактировать формулу расчета количества книг по аналогии с подобной формулой листа «1.04.02»).
Приобретен телефон «Panasonic» стоимостью 7300 р. (отнести в МБП, добавить строку, заполнить ее соответствующими данными и формулами).
Отредактировать формулу расчета стоимости имущества предприятия на 01.10.02 – отредактировать диапазон ячеек, содержащих остаточную стоимость МБП, т.к. была добавлена строка.
Создать внешний вид листа «1.01.03г.» копированием содержимого листа «1.10.02г.» и редактированием его согласно рис.15. Редактирование происходит с учетом того, что:
Рис.15. Итоговый внешний вид листа «1.01.03»
Списаны халаты и приобретена униформа в количестве 15 шт. по цене 345 р. (в строке «Халаты» изменить данные, формула в столбце МБП = 15*345)
Увеличился износ основных средств и здания на 1 год (отредактировать формулу износа)
Отредактировать формулу расчета стоимости имущества предприятия на 01.01.03 – отредактировать диапазон ячеек, содержащих остаточную стоимость МБП, т.к. была добавлена строка.
На листе «Среднегодовая стоимость имущества» произвести расчет среднегодовой стоимости имущества F по приведенной в задании формуле с использованием функции СУММ и панели ввода функции, указывая мышью ячейки со стоимостью имущества на заданные даты. Внешний вид рабочего листа «Среднегодовая стоимость имущества» представлен на рис.16.
Рис.16. Итоговый вид листа «Среднегодовая стоимость имущества»
Вариант 2.
Задание: Пользуясь возможностями Excel, создать рабочую книгу из 6 листов, содержащих листы классного журнала. Первые 4 листа содержат листы журнала по предметам. На каждом из них имеются:
Список класса.
Текущие оценки.
Итоговая оценка за четверть.
Пятый лист содержит ведомость итоговых оценок за четверть, заполненная оценками с использованием ссылок на соответствующие листы по предметам.
Шестой лист содержит сведения об учащихся.
Примечание. Символом помечено задание для выполнения.
Правила расчета
1 четверть = среднее значение по строке ученика.
Исходные данные и порядок работы.
Создать с помощью команды «Вставка/Лист» книгу Excel из 6 листов. Задать названия листов согласно рис. 17:
Рис.17.Названия листов классного журнала
Создать внешний вид листа «Сведения» согласно рис.18 на 10 учащихся.
Создать лист «Русский язык» на 10 учащихся согласно рис.18.
Рис.18. Исходный вид листа предмета.
Задать связь между листами «Сведения» и «Русский язык» для выбора фамилии первого ученика: в ячейке с фамилией первого ученика ввести знак равенства, перейти с помощью мыши на лист «Сведения», щелкнуть мышью ячейку с фамилией первого ученика (в строку формул помещается ссылка на эту ячейку) и затем нажать Enter.
Размножить полученную формулу на столбец «Фамилия имя ученика».
Заполнить листы сведениями об оценках и посещаемости.
В столбце «1 четверть» для первого ученика ввести формулу для подсчета среднего балла по соответствующей ему строке, используя функцию СРЗНАЧ и панель ввода функций (см.рис.8). Для этой ячейки задать числовой формат без знаков после запятой.
Заполнить листы остальных 3-х предметов путем копирования и последующего редактирования содержимого листа «Русский язык» на остальные листы.
Заполнить лист «Ведомость» согласно рис.19.
Рис.19. Исходный вид листа «Ведомость»
По аналогии с листами предметов заполнить столбец «Фамилия имя» фамилиями учащихся с листа «Сведения».
Столбцы предметов заполнить ссылками на ячейки с четвертными оценками соответствующих листов предметов.
Данные столбца «1 четверть» рассчитать как среднее значение итоговых оценок каждого учащегося по предметам с помощью функции СРЗАЧ.
Проверить связь листов: на листе «Русский язык» изменить текущие оценки одному из учеников так, чтобы изменилась четвертная оценка. Затем перейти на лист «Ведомость» и убедиться, что новая оценка появилась в ведомости и отразилась на значении среднего балла.
Вариант 3.
Задание: Пользуясь возможностями Excel, создать рабочую книгу из 5 листов, содержащих расписание занятий 3-х классов школы.
Первые 3 листов содержат расписание уроков классов каждого класса.
Четвертый лист содержит сводное расписание уроков.
Пятый лист содержит расписание звонков.
Примечание. Символом помечено задание для выполнения.
Исходные данные и порядок работы.
Создать с помощью команды «Вставка/Лист» книгу Excel из 5 листов. Задать названия листов согласно рис. 20:
Р
ис.20. Названия листов рабочей книги.
Создать внешний вид листа «Класс 1а» согласно рис.21.
Рис.21. Внешний вид листа с расписанием класса.
 Заполнить листы остальных 2-х классов путем копирования и последующего редактирования содержимого листа «Класс 1а» на остальные листы.
Создать внешний вид листа «Звонки» согласно рис.22.
Создать внешний вид листа «Расписание» согласно рис.23, создав таблицы для трех классов.
Задать связь между листами «Звонки» и «Расписание» для вывода времени уроков в столбец время сводного расписания. Для этого активизировать ячейку B4 на листе «Расписание», ввести знак равенства, перейти на лист «Звонки», щелкнуть мышью ячейку со временем урока 1 (в строку формул помещается ссылка на эту ячейку) и затем нажать Enter.
Рис.23. Исходный внешний вид листа сводного расписания.
Размножить полученную формулу на столбец «Время» для 1«а» класса.
По аналогии заполнить столбец «Время» для оставшихся двух классов.
Заполнить расписание 1«а» класса путем задания связей между листами. Для этого активизировать ячейку С4, ввести знак равенства, перейти с помощью мыши на лист «Класс 1а» и щелкнуть ячейку с названием предмета первого урока понедельника (в строку формул помещается ссылка на эту ячейку) и затем нажать Enter.
Размножить полученную формулу на столбец «Понедельник» для класса 1«а». Расписание понедельника 1«а» класса будет заполнено.
Выделить полученный столбец и размножить его на оставшиеся дни 1«а» класса. Расписание для этого класса будет заполнено.
По аналогии с 1«а» классом заполнить расписание 1«б» и 1«в».
Проверить связь листов: на листе «Класс 1а» изменить расписание понедельника. Затем перейти на лист «Расписание» и убедиться, что сводное расписание изменилось.
Изменить расписание звонков на листе «Звонки». Затем перейти на лист «Расписание» и убедиться, что сводное расписание изменилось.
Вариант 4.
Задание: Пользуясь возможностями Excel, создать рабочую книгу из 5 листов, содержащих сведения о сборе налогов 4-мя районами области за год.
Первые 4 листа содержат сведения о сборе налогов каждого из районов.
Пятый лист содержит сводную ведомость сбора налогов по области за год.
Примечание. Символом помечено задание для выполнения.
Правила расчета
Итого = сумма по строке.
Итого по месяцам = сумма данных соответствующего месяца по районам
Исходные данные и порядок работы.
Создать с помощью команды «Вставка/Лист» книгу Excel из 5 листов. Задать названия листов согласно рис. 24:
Рис.24. Названия листов рабочей книги.
Создать внешний вид листа «Район1» согласно рис.25.
Рис.25.Внешний вид листа «Район1».
 Рассчитать в столбце «Итого» итоговую сумму налогов, собранных в январе, с использованием автосуммы по строке (кнопка на панели инструментов).
Размножить полученную формулу на столбец «Итого».
Заполнить листы остальных 3-х районов путем копирования и последующего редактирования содержимого листа «Район1» на остальные листы.
Создать внешний вид листа «Итог» согласно рис.26.
Задать связь между листами «Итого» и листами районов для вычисления итогового сбора налогов по видам. Для этого активизировать ячейку B4 на листе «Итог», нажать кнопку f x и в окне «Мастер функций» выбрать функцию СУММ (см.пример). В панели ввода функции (см.рис.8) заполнить 4 строки данными по каждому району, находящимися в столбце «Налог на имущество» на разных листах.
Размножить полученную формулу на столбец.
Размножить полученный столбец на все остальные столбцы таблицы.
Ввести формулу для подсчета итога с помощью автосуммы.
Рис.26. Исходный внешний вид листа «Итог»
Вариант 5.
Задание: Пользуясь возможностями Excel, создать рабочую книгу из 5 листов, содержащих сведения о зарплате 10 рабочих в течении 4-х недель.
Первые 4 листа содержат сведения о заработке каждого рабочего по неделям.
Пятый лист содержит сведения о рабочих.
Шестой лист содержит сводную ведомость расчета заработка рабочих за 4 недели.
Примечание. Символом помечено задание для выполнения.
Правила расчета:
Заработано= коэффициент разряда * (изготовлено деталей – брак) * стоимость детали
Заработано итого = сумма недельных заработков
Исходные данные и порядок работы.
Создать с помощью команды «Вставка/Лист» книгу Excel из 6 листов. Задать названия листов согласно рис. 27.
Рис.27. Названия листов рабочей книги.
Создать внешний вид листа «Сведения» согласно рис.28.
Рис.28. Исходный внешний вид листа «Сведения»
Создать внешний вид листа «Неделя1» для 10 рабочих согласно рис.29.
Рис.29. Внешний вид листа «Неделя1»
Задать связь между листами «Сведения» и «Неделя1» для заполнения таблицы листа «Неделя1»: активизировать ячейку В4, ввести знак равенства, перейти мышью на лист «Сведения», щелкнуть ячейку с ФИО первого рабочего (в строку формул помещается ссылка на эту ячейку) и нажать Enter.
Размножить формулу на весь столбец (он заполняется фамилиями рабочих).
По аналогии с фамилией заполнить столбец с коэффициентами разряда.
Заполнить произвольными данными столбцы «Изготовлено деталей» и «Брак».
Создать формулы для расчета величины «Заработано» на листе «Неделя1» по формуле, указанной в задании с учетом того, что стоимость детали выбирается с листа «Сведения», а остальные данные – с текущего листа. Для последующего правильного изменения формулы при копировании применить абсолютную адресацию.
Размножить формулу на весь столбец «Заработано».
Скопировать содержимое листа «Неделя1» на листы «Неделя2», «Неделя3», «Неделя4» и изменить данные о количестве произведенных деталей.
Создать внешний вид листа «Итог» согласно рис.30.
Рис.30. Исходный вид листа «Итог».
По аналогии с листом «Неделя1»задать связь между листами «Сведения» и «Итого» для заполнения столбца «ФИО».
Задать связь между листами «Итог» и «Неделя1» для заполнения столбца «Заработано в неделю 1»: активизировать ячейку С4, ввести знак равенства, перейти мышью на лист «Неделя1», щелкнуть ячейку с заработком первого рабочего (в строку формул помещается ссылка на эту ячейку) и нажать Enter.
Размножить формулу на весь столбец «Заработано в неделю 1».
По аналогии заполнить столбцы «Заработано в неделю 2», «Заработано в неделю 3», «Заработано в неделю 4».
Создать формулу для расчета итогового заработка с помощью функции автосуммы данных по строке. |
|
|
Скачать 0.84 Mb.