Операции над рабочими листами в MS Excel. Практическая работа

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

Операции над рабочими листами

Цель занятия: изучить основные операции над рабочими листами MS Exсel, научиться использовать связи между листами.

Задачи занятия: приобрести практическое умение использования связи рабочих листов.

Порядок выполнения работы:

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

Теоретические сведения

При щелчке по листу панели инструментов «Стандартная» открывается новая рабочая книга. Она состоит из рабочих листов, имена которых Лист1, Лист2,... Эти имена выведены на ярлыках в нижней левой части окна рабочей книги.

Перемещение между листами

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

Переход к первому листу

Ярлык

Контекстное меню

Контекстное меню появляется при щелчке по ярлыку листа правой кнопкой мыши.

Копирование рабочего листа

1. Открыть контекстное меню листа, который копируем;

2. Выбрать пункт Переместить/скопировать;

3. В окне «перед листом» выбрать лист, перед которым хотите сделать копию;

4. Щелкнуть в окне Создавать копию;

5. Нажать ОК.

Совместное использование нескольких рабочих листов

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

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

<имя листа> ! <адрес ячейки>

При вводе формулы со ссылкой надо:

1. Нажать клавишу = в требуемой ячейке листа, в которую вставляется формула;

2. Щелкнуть на ярлыке листа, в котором берется адрес ячейки;

3. Щелкнуть на требуемой ячейке в этом листе;

4. Нажать Enter.

Задание №1

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

Ход работы:

1. Используя контекстное меню для ярлыка Лист 1,переименуйте этот лист, дав ему имя Приход.

2. Наберите на этом листе шапку для данных о приходе товаров:

Наименование товара

Цена

Количество

3. Создайте копию листа Приход после него (перед листом 2).

4. Переименуйте копию, дав имя Расход.

5. Создайте копию листа Расход после него.

6. Переименуйте копию, дав имя Остатки.

7. Введите на лист Приход наименования и цены товаров:

Клей по цене 30 руб. 50 коп. Кнопки по цене 20 руб. 75 коп. Скрепки по цене 10 руб. 90 коп.

8. Используя связь рабочих листов, перенесите для первого товара введенные данные

с листа Приход на листы Расход и Остатки

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

10. Введите любые числа в графу Количество на листы Приход и Расход

11. Используя связь рабочих листов, найдите величину остатка товаров на листе Остатки

12. Подсчитайте стоимость каждого вида товаров на листе Остатки

Задание №2

Аптека имеет 3 филиала. Требуется создать сводную ведомость, отражающую итоги недельных продаж лекарств для этих филиалов.

Ход работы:

1. На Листе 1 создайте шаблон следующей таблицы:

Продажа лекарств филиалами аптеке № 7

Товар

Итого за неделю

01.07

03.07

05.07

07.07

Анальгин

Аспирин

Цитрамон

2. Сделайте 2 копии данного листа.

3. Переименуйте эти листы, задав им имена «Филиал 1», «Филиал 2», «Филиал 3».

4. Внесите исходные данные в эти листы (выборочно).
5. Найдите итоговые суммы за неделю.

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

Задание №3

Рассчитать квартальную потребность в кормах для каждого вида корма (вес каждого корма и стоимость). Расчет произвести на отдельном рабочем листе с именем Квартал.

Ход работы:

1. Шаблон таблицы разработать только для листа Январь по нижеприведенному образцу. Отформатировать данные самостоятельно.
2. Шаблоны таблиц для листов Февраль, Март, Квартал - это копии листа Январь. Откорректировать их самостоятельно.

3. Вид корма и цену брать из листа Январь.

4. Исходные данные на эти листы (а также расчеты по ним) внести самостоятельно.

5. Исходные данные для квартального расчета брать из рабочих листов с именами Январь, Февраль, Март.

Потребность в кормах

ЯНВАРЬ

Вид корма

Цена

Вес

Стоимость

сено

солома

силос

сенаж

свежая трава

комбикорм

ИТОГО:

Задание №4

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

Ход работы:

1. На рабочем листе создать таблицу со сведениями о работниках:

N

Фамилия

Дата рождения

Образование

Ставка

1.

Борисов

01.09.67

Высшее

340 руб.

2.

Васильев

02.05.56

Высшее

490,5 руб.

3.

Романов

07.08.81

Среднее

250 руб.

2. Переименовать данный лист в Отдел кадров.

3. На Листе 2 создать таблицу с табелем рабочего времени, пользуясь связью с листом Отдел кадров.

За апрель месяц

отработано

рабочих дней:

Борисов - 15

Васильев - 17

Романов - 21

4. Переименовать данный лист в Учет времени.

5. Используя связь рабочих листов, на Листе 3 произвести расчет зарплаты сотрудникам, считая, то зарплата рассчитывается как ставка * количество отработанных дней.

6. В отдельной колонке произвести расчет оплаты за обучение, считая, что плата вычисляется как 2% от минимального оклада (считать, что он равен 4000 рублей) + 3% от зарплаты. Величины процентов и минимального оклада занести в отдельные ячейки и при расчетах использовать абсолютную адресацию.