Возможности автоформатирования

Задание 1. Построение простой электронной таблицы

 

1. Создайте свою папку. Запустите Excel.

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

 

Рис. 1.

 

3. При заполнении клеток C3, D3 используются формулы: C3 =B3*C$2 D3 =B3-C3

 

4. Диапазоны клеток C3:C9 и D3:D9 заполняются путем копирования соответствующих формул. Для этого нужно выделить блок ячеек и вызвать операцию Правка – Заполнить – Вниз или с использованием мыши путем «протаскивания».

5. Просмотрите формулы для всех сотрудников. Обратите внимание на автоматическое изменение некоторых адресов ячеек. Адрес какой ячейки не изменился? Почему? Обязательно найдите ответы на эти вопросы; при необходимости обратитесь к преподавателю.

6. Клетка В11 рассчитывается по формуле =СУММ(В3:В10). В клетки С11, D11 эта формула копируется. Сохраните заполненную таблицу в своей папке под именем ZP1.XLS.

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

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

Вставьте новые графы «Премия» и «Всего начислено» после графы «Зарплата». Самостоятельно задайте формулы для их вычисления, исходя из того, что премия составляет 40% от зарплаты, а «Всего начислено» – это «Зарплата» + «Премия». Отредактируйте все остальные формулы, руководствуясь задачей.

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

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

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

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

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

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

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

16. Задайте следующие имена для диапазонов ячеек (Вставка – Имя – Присвоить):

Зарплата – для столбца с начисленными зарплатами;
Премия – для столбца с премиями;
Налог – для столбца с налогами;
Пенсионный фонд – для столбца с отчислениями в пенсионный фонд.

17. Выделите всю таблицу и выполните команду Вставка – Имя – Применить. Укажите Применить все имена из списка. Проверьте изменения в формулах.

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

19. Сохраните таблицу в своей папке под именем ZP1.XLS.

 

 

Задание 2. Форматирование таблицы

 

1. Откройте таблицу ZP1.XLS, созданную в предыдущей работе.

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

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

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

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

6. Вставьте второй строку «за январь 2004г.»

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

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

9. Для ячеек «Табельный номер», «Всего начислено», «Пенсионный фонд» задайте формат вывода текстов в несколько строк (Формат – Ячейка – вкладка Выравнивание – Переносить по словам).

10. Запишите таблицу под новым именем ZP3.XLS.

11. Отмените сетку на экране (Сервис – Параметры – вкладка Вид) и сбросьте флажок у элемента “ сетка ”.

12. Расчертите таблицу горизонтальными и вертикальными линиями. Используйте жирные и тонкие линии.

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

14. Выведите вашу таблицу на экран в режиме предварительного просмотра и измените масштаб таблицы.

15. Сохраните таблицу под прежним именем (ZP3.XLS)

Возможности автоформатирования

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

Ø Формат – Автоформат;

Команда Автоформат позволяет существенно экономить время. В них заранее определены наборы параметров, содержащие форматы чисел, шрифты, типы выравнивания, рамки, узоры, а также ширину столбцов и высоту строк.

Примените автоформатирование к своей таблице. Сохраните отформатированную таблицу в своей папке под именем ZP4.XLS.

 

Задание 3. Использование статистических и математических функций

 

1. Составьте таблицу следующего вида (рис.2.). Введите в таблицу заголовок.

Рис. 2.

 

2. Заполните ячейку В4. Затем протащите мышь вправо до ячейки F4 включительно (указатель мыши необходимо навести на ячейку В4 таким образом, чтобы он принял форму черного крестика). Отпустите левую кнопку мыши. Клетки автоматически заполнятся названиями месяцев.

3. Заполните остальные ячейки согласно рис.2.