Последовательность выполнения работы. 1. Открыть файл для формирования таблицы.

Выполнить задание 1, для чего:

1. Открыть файл для формирования таблицы.

2. Заполнить строки 1 и 2 и столбец А. Все заголовки и фамилии вводить с первой позиции ячейки.

Столбец В («Зарплата») заполнить значениями от 8000 руб. до 30000 руб.

3. В ячейке С3 и D3 ввести формулы:

С3 = В3 * С$2 (для указания размера налога использовать абсолютный адрес)

D3 = B3 – C3

4. Скопировать формулы из строки 3 в остальные строки таблицы.

5. Просуммировать данные столбца В (в ячейке В15), используя функцию СУММ. В ячейке А15 набрать Итого.

6. Скопировать эту формулу в ячейки С15 и D15.

7. Сохранить заполненную таблицу под именем ZPL1.xls.

8. В созданном файле поменять у нескольких сотрудников заработную плату. Для редактирования содержимого ячеек использовать клавишу F2 или дважды щелкнуть мышкой на соответствующей ячейке. Обратить внимание на изменение вычисляемых ячеек.

9. Установить налог 15% в ячейке С2. Сравнить полученные итоговые данные с предыдущими значениями.

10. Вставить новые графы «Премия» и «Всего начислено» после графы «Зарплата». Самостоятельно задать формулы для их вычисления, исходя из того, что премия составляет определенный процент от зарплаты (процент премии задать в ячейке), а «Всего начислено» - это «Зарплата» + «Премия».

Отредактировать формулу вычисления налога (Всего начислено * Е$2).

Отредактировать формулу вычисления столбца Выплатить (=Всего начислено - Налог).

11. Удалить одну строку из таблицы (сотрудник уволен). Проверить формулы итоговой строки, обратить внимание на изменение диапазонов в формулах.

12. Дополнить таблицу еще тремя строками, включив их между, например, 5 и 6 строками (приняты три новых сотрудника). Заполнить эти строки. Фамилии и зарплату ввести, формулы – скопировать.

13. Сохранить таблицу под именем ZPL2.xls.

14. В созданном файле ZPL2.xls в ставить перед колонкой «Налог» еще две колонки «Пенсионный фонд» и «Налогооблагаемая база». Установить, что в пенсионный фонд удерживается в размере 1% от начислений зарплаты и премии. Отчисления в пенсионный фонд не входят в налогооблагаемую базу, т.е. «Налогооблагаемая база» вычисляется как «Зарплата» + «Премия» - «Пенсионныйфонд». Внести все необходимые изменения в формулы.

15. Изменить алгоритм расчета подоходного налога с учетом прогрессивной шкалы налогообложения. Если налогооблагаемая база меньше определенной величины (например, 12000), то принимается ставка 12%, если больше – 20%. Формула должна использовать функцию ЕСЛИ.

16. Проанализировать полученные результаты.

17. Изменить формулу расчета столбца Выплатить (=Налогооблагаемая база - Налог).

18. Вставить перед колонкой «Фамилия» новую графу «Табельный номер» и заполнить ее значениями: 100, 101, 102 и т.д. ( в ячейке А3 набрать 100, выделить блок ячеек с А3 до конца списка фамилий, выполнить вкладка Главная - группа Редактирование - кнопка – – ).

19. Под строкой «Итого» вставить еще две строки для вычисления среднего и максимального значения начисленной и выданной зарплаты. Для этого использовать встроенные статистические функции МАКС и СРЗНАЧ, указать диапазон для работы этих функций. Набрать названия строк с полученными данными.

20. Задать имена для диапазонов ячеек, поочередно выделяя соответствующие столбцы ( вкладка Формулы - группа Определенные имена - кнопка ):

Зарплата – для столбца с начисленными зарплатами;

Премия – для столбца с премиями;

Налог – для столбца с налогами;

Пенсионный фонд – для столбца с отчислениями в пенсионный фонд.

21. Выделить всю таблицу, зайти в вкладка Формулы - группа Определенные имена - кнопка . Применить все именаиз списка. Проверить изменения в формулах.

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

22. Сохранить таблицу в своей папке под именем ZPL3.xls.

23. Отчитаться перед преподавателем, продемонстрировав три созданных файла.

 

Выполнить задание 2, для чего:

1. В созданном файле ZPL3.xls для создания структуры добавить после фамилии столбец «Отдел» и заполнить его, чередуя названия отделов, например, бухгалтерия, плановый, экономический.

2. Отсортировать таблицу по отделам, а внутри отделов – по фамилиям. Для этого:

- выделить таблицу без строк с итоговыми данными, выполнить вкладка Данные- группа Сортировка и фильтр - кнопка- ;

- в поле Сортировать по указать Столбец C (Отдел) - отметить опцию От А до Я (По возрастанию);

- добавить новый уровень кнопкой ;

- в поле Затем по указать Столбец B (Фамилия) - отметить опцию От А до Я (По возрастанию);

- нажать кнопку ОК.

3. Выделить всю область таблицы и выполнить автоматическое структурирование:

вкладка Данные - группа Структура - кнопка- - . Научиться сворачивать и разворачивать полученную (горизонтальную) структуру до разных уровней, используя кнопки «+» и «-».

4. Сохранить файл с созданной структурой под именем ZPLSTR.xls.

5. В созданном файле ZPLSTR.xls удалить созданную структуру (вкладка Данные - группа Структура – кнопка - .

6. Удалить строки с вычисленными данными Итого, Макс, Срзнач.

7. Подвести промежуточные итоги по отделам, используя формулу суммирования. Для этого:

- выделить всю таблицу, выполнить вкладка Данные - группа Структура - кнопка . На экране появится диалоговое окно Промежуточные итоги.

- в поле При каждом изменении в: указать Столбец C (Отдел);

- в поле Операция: указать Сумма;

- в поле Добавить итоги по: отметить все столбцы, кроме столбцов А, В и С;

- отметить опции Заменить текущие итоги и Итоги под данными.

- нажать кнопку ОК.

8. Изучить полученную структуру и формулы для подведения промежуточных итогов.

9. Применить кнопки- на вкладке Структура. Проанализировать полученный вид документа. Сохранить изменения в файле ZPLSTR1.xls.

10. Отчитаться перед преподавателем, продемонстрировав два файла.

 

5. Контрольные вопросы

1. Что может находиться в ячейке таблицы?

2. Что такое адрес ячейки? Какие бывают адреса?

3. Как выделить группу (диапазон) ячеек? Как задать адрес группы (дтапазона) ячеек?

4. Что такое формула? Из чего состоит формула?

5. Что такое МАСТЕР ФУНКЦИЙ? Для чего он используется? Как его вызвать?

6. Что такое имя диапазона? Для чего вводят имена диапазонов?

7. Как установить имя диапазона? Как применить имя диапазона в таблице?

8. Как упорядочить данные в таблице?

9. Как скопировать формулу без изменения адресов в ней?

10. Как скопировать формулу с изменениями адресов в ней?

11. Как скопировать значение ячейки без изменения (варианты)?

12. Как вставить (удалить) строку (столбец), несколько строк (столбцов)?

13. Как создать (удалить) структуру таблицы?

14. Как установить (отменить) фильтр?

15. Как выполнить подведение итогов? Какие итоги называются промежуточными? Какой итог называется общим?

16. Как сворачивать и разворачивать разделы списка после подведения итогов?

17. Как отменить итоги?

18. Как изменить имя листа Excel?

Лабораторная работа 7