ВОЗМОЖНОСТИ АВТОФОРМАТИРОВАНИЯ

Практическая работа №1 Работа в MS Excel

ЗАДАНИЕ 1. ПОСТРОЕНИЕ ПРОСТОЙ ЭЛЕКТРОННОЙ ТАБЛИЦЫ

1. Создайте папку с именем группы. В ней создайте папку со своим именем. В ней создайте рабочую книгу Ехсеl c названием зарплата.

2. Начиная с клетки А1, создайте электронную таблицу по образцу, приведенному на рис. 1 (ведомость зарплаты). Сначала заполните строку 1 и столбец А. Все заголовки и фамилии вводите с первой позиции клетки. Колонку Зарплата заполните значениями в пределах от 15000 руб. до 50 000 руб.

Рис. 1 Ведомость зарплаты

3. При заполнении клеток С2, D2 используются формулы:

С2 = В2 * $В$12;

D2 =B2 – C2.

Для ввода абсолютной ссылки – поставить курсор перед адресом ячейки в строке формул и нажать F4.

4. Диапазоны клеток СЗ:С9 и DЗ:D9 заполняются путем копирования соответствующих формул. Для этого нужно выделить блок ячеек и вызвать операцию вкладка Главная — группа Редактирование или с использованием мыши путем «протаскивания» за маркер заполнения в правом нижнем углу выделенной клетки.

5. Клетка В10 рассчитывается по формуле =СУММ(В2:В9). В клетки С10, D10 эта формула копируется. Сохраните заполненную таблицу на первом листе файла Excel, который назовите ZP1.

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

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

8. Скопируйте таблицу на второй лист. Вставьте новые графы Стаж, Премия и Всего начислено после графы Зарплата, используя команду вкладки Главная — группа Ячейки — Вставить.

9. Самостоятельно задайте формулы для их вычисления, исходя из того, что премия составляет 45 процентов от зарплаты, а Всего начислено— это Зарплата + Премия. Отредактируйте все остальные формулы, руководствуясь задачей.

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

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

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

13. Скопируйте таблицу на том же листе. Измените алгоритм расчета премии. Если стаж меньше определенной величины (например, 5 лет), то принимается ставка 12%, если больше — 20%. Формула должна использовать функцию ЕСЛИ. Как изменится формула, если шкала будет задана так: до 5 лет - 12%, от 5 лет до 15 — 20%, свыше 15 — 25%?

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

15. Вставьте перед колонкой Фамилия новую графу Табельный номер и заполните ее значениями: 100, 101, 102 и т.д. (вкладка Главная — Редактирование — Заполнить — Прогрессия).

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

17. Задайте следующие имена для диапазонов ячеек (выделить диапазон и в контекстном меню выбрать команду Имя Диапазона— ввести Имя или на вкладке Формулы в группе Определенные имена выбрать команду Присвоить имя):

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

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

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

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

18. Выделите всю таблицу и выполните последовательность действий — Формулы— Определенные имена — Присвоить имя — Применить имена. Укажите применить все имена из списка. Проверьте изменения в формулах.

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

20. Сохраните таблицу на втором листе под именем ZP2 в своем файле.

ЗАДАНИЕ 2 СОЗДАНИЕ СТРУКТУРЫ

Ехсеl дает возможность использовать структурирование рабочего листа для удобства представления таблиц большого объема. Для создания структуры можно использовать автоматическое структурирование выделенной области (Данные— Структура — Группировать — Создание структуры).

1. Скопируйте таблицу на третий лист и назовите лист ZP3. Выделите всю область таблицы и выполните автоматическое структурирование. Научитесь сворачивать и разворачивать полученную структуру до разных уровней, используя кнопки «+» и «-».

2. Удалите созданную структуру Данные — Структура — Разгруппировать — Удалить структуру.

3. Вставьте еще один столбец перед столбцом Табельный номер — Подразделение и заполните его произвольными названиями различных отделов: бухгалтерия, плановый отдел, отдел кадров, производственный отдел и т.д.

4. Отсортируйте таблицу по подразделениям, а внутри подразделений — по фамилиям. Для этого:

• выделите диапазон А2:К11 и выполните команду Данные — Сортировка и фильтр — Сортировка;

• в поле Сортировать поукажите Столбец А, отметьте по возрастанию от А до Я;

• нажмите Добавить уровеньи в поле Затем поукажите Столбец С, отметьте по возрастанию;

• нажмите кнопку ОК.

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

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

• в поле При каждом изменении в:укажите Столбец А

• в поле Операция:укажите СУММА

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

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

6. Сдайте работу преподавателю.

ЗАДАНИЕ 3. ФОРМАТИРОВАНИЕ ТАБЛИЦЫ

1. Откройте файл зарплата, созданный в предыдущей работе. Скопируйте таблицу на четвертый лист и назовите его ZP4.

2. Установите с помощью мыши ширину столбцов с учетом возможных их значений и ширины заголовков, например, для столбца фамилий ширину 20 символов.

3. Измените высоту итоговой строки (сделайте ее «выше») и высоту строк, составляющих «шапку» (сделайте их «ниже»).

4. Отцентрируйте названия столбцов и значения в строках шапки таблицы. Для этого выделите блок и щелкните на инструменте «по центру».

5. Установите формат столбца D — целое число, а всех остальных — дробные числа с двумя разрядами после запятой. Если при этом ширина некоторых столбцов окажется недостаточной измените ее.

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

7. Вставьте вторую строку «за январь 2016 г.».

8. Выделите блок, состоящий из строки 1, шириной от столбца А до последнего столбца таблицы. Отцентрируйте заголовок в пределах этого блока (используйте инструмент, на котором нанесена буква «а» со стрелками слева и справа) в группе Выравнивание на вкладке Главная. Установите для заголовка жирный шрифт размером 12 пунктов. Аналогично поступите со второй строкой заголовка.

9. Выделите «шапку» таблицы. Установите для нее шрифт размером 14 пунктов. При необходимости измените ширину некоторых столбцов.

10. Для ячеек Табельный номер, Всего начислено, Пенсионный фонд задайте формат вывода текста в несколько строк (вкладка Главная — группа Выравнивание — Переносить по словам).

11. Сохраните таблицу на новом листе под именем ZP5.

12. Отмените сетку на экране (вкладка Разметка страниц — группа Параметры листа — Сетка — снять флажок Вид).

13. Расчертите таблицу горизонтальными и вертикальными линиями. Используйте жирные и тонкие линии (можно использовать рамки, выделив таблицу, вкладка Главная — группа Шрифт — выбрать вид рамки).

14. Измените цвета символов итоговой строки и заголовка. Измените цвета шапки и графы Выдать, например сделайте ее светлосерой.

15. Выведите таблицу на экран в режиме предварительного просмотра (Кнопка Office— Печать — Предварительный просмотр) и измените масштаб таблицы.

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

17. Познакомьтесь на вкладке Разметка страниц с группой Тема. Просмотрите разные темы и эффекты.

18. Попробуйте удалить одну из строк и вставить одну строку в середину таблицы. Что при этом происходит?

19. Сохраните таблицу.

ВОЗМОЖНОСТИ АВТОФОРМАТИРОВАНИЯ

Для изменения внешнего вида ячеек рабочего листа можно также использовать автоформатирование:

• выделить нужные ячейки;

• вкладка Главная — группа Стили — Стиль ячеек;

или выделить всю таблицу — вкладка Главная — группа Стили — Форматировать как таблицу. Команда помогает существенно экономить время.

20. Примените автоформатирование к своей таблице. Сохраните отформатированную таблицу в своем файле на листе с именем ZP6.

Не нашли, что искали? Воспользуйтесь поиском по сайту: