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

---

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

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

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

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

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

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

СЗ = ВЗ * $С$2;

D3 =B3 – C3.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ЗАДАНИЕ ц. ИСПОЛЬЗОВАНИЕ СТАТ1/1СТИЧЕСКИХ, МАТЕМАТИЧЕСКИХ И ТЕКСТОВЫХ «И»УНКЦИЙ

Составьте таблицу следующег^»о вида. Введите в таблицу заголовок.

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

1. Заполните остальные ячейки.

Задача 1. Рассчитать выручку от троката машин на четыре часа, 1 день, одну неделю

4. Добавьте итоговый столбец. Выделите ячейки В5:О5 и
кните по кнопке СУММА в группе Редактирование на вкладке
Главная.

5. Скопируйте формулу из ячейки О5 в ячейки О6:О7.

6. Выделите ячейки В5:В8. Выполните щелчок на кнопке СУММА.

7. Скопируйте формулу из ячейки В8 в ячейки С8:О8.

Расчет процента

8. Установите курсор на ячейку Н5. Активизируйте кнопку
Процент в группе Число на вкладке Главная для задания формата
ячейки.

9. Наберите формулу = О5/О8 и, не нажимая клавишу ЕТЧТЕЯ,
нажмите клавишу Р4. Формула примет вид = = О5/$О$8, т.е. от­
носительный адрес О8 заменится на абсолютный $О$8 (не ме­
няющийся при копировании формулы в другие ячейки). Нажмите
клавишу ЕМТЕК. Появится величина в процентах.

10. Скопируйте формулу из ячейки Н5 в ячейки Н6:Н8.

Имена ячеек

11.Присвойте ячейке С8 имя «Всего»: установите курсор на
ячейку О8, выполните команду вкладка Главная — Определенные
имена — Присвоить имя, введите имя ячейки Всего.

12.Очистите ячейки Н5:Н8 для расчета процентов по другой
формуле.

13.Введите имя ячейки в формулу расчета процента: установите
курсор на ячейку Н5 и введите формулу =О5/Всего, скопируйте
формулу в Н6:Н8.

14.Быстрый переход к ячейке по ее имени: откройте список в
поле Имя в строке Формула, выберите имя ячейки Всего. Для пере­
хода к ячейке по ее имени можно также выполнить команду Глав­
ная — Редактирование — Найти и выделить — Перейти и выбрать
имя ячейки.

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

15. Определите зависимость между ячейками на вкладке Форму­
лы в группе Зависимости формул.

16. Установите курсор на ячейку О8. Активизируйте кнопки
Влияющие ячейки, Зависимые ячейки.

Влияющие ячейки — это ячейки, на которые ссылается формула в текущей ячейке. Зависимые ячейки — это ячейки, содержащие формулы, которые ссылаются на текущую ячейку.

17. Удалите все стрелки командой Убрать все стрелки.

18. Сохранить таблицу под именевш-я Таблица3.х1§х. Рабочему ли­
сту, на котором находится таблица, пн^щэисвойте имя Задача 1.

19. Следующему листу присвойте зимя Задача 2 (если в рабочей
книге только один лист, добавьте ешщ «е один командой Вставить —
Диет на ярлыке листа).

Задача 2. Определить выручку от- продажи товаров на лотках и тенденцию роста доходов. СоставигЕ=» таблицу следующей формы (на листе Задача 2):

Итого

Годы [Номер лотка;Раи-вг

Доход

54600

ЩЩ||$ 5008

68453… 52748

2009

50456 36914 34109 31745

………………………

1(111 Среднегодовой доход ; Максимальный доход Минимальный доход ;

Е6. Введите формулу

1. Установите курсор на яч ^
=СУММ(О4:О6).

2. Установите курсор на ячейку Е_?ЕЭ. Введите формулу, используя
Мастер функций: на вкладке Форму_г^ы в группе Библиотека функ­
ций — Вставить функцию — выбермг^м-е функцию СУММ, нажмите
на кнопку ОК, в появившемся ди»_логовом окне в поле Число 1
введите диапазон суммируемых чисе_э~1 В7:В9.

3. Мастер функций можно вызв-^в_ть также при одновременном
нажатии клавиш 8Н1РТ + РЗ. Для гп «лучения пояснения по функ­
ции вызовите Справку.

4. Самостоятельно подсчитайте с^–мму в ячейке Е12 любым спо­
собом.

функции СРЗНАЧ. МАКС. МИ ЯЧ

В ячейку Е14 введите формулу =СРЗНАЧ(Е6;Е9;Е12)

В ячейку О15 введите формул>^ =МАКС(О4:В12)

7. В ячейку О16 введите формулу =МИН(В4:О12).

8. Очистите ячейки Е14, 015, В16 и введите формулы функций
через Мастер функций.

Функция РАНГ

Функция РАНГ определяет ранг (номер) элемента в общей со­вокупности.

9. В ячейку С4 введите формулу =РАНГ($О4;$О$4:$О$12), где
О4 содержит число, для которого определяется ранг, а В4:Ш2 —
массив чисел, среди которого определяется ранг.

10. Скопировать формулу в ячейки С5:С12.

Функция ТЕНДЕНЦИЯ

И. Выполнить подготовительные операции: в ячейки В20:В22 введите соответственно значения из ячеек Е12, Е9, Е6; в ячейки С20.-С24 введите годы: 2007-2009.

12.В ячейку В23 введите формулу =ТЕНДЕНЦИЯ(В20:В22;С20:
С22;С23). Скопируйте формулу из ячейки В23 в ячейку В24.

13.Задайте в ячейках В23:В24 формат целых чисел. Для этого
следует: выделить нужные ячейки, выполнить на вкладке Главная в
группе Ячейки, выбрать Ячейки — Формат — Формат ячейки; кате­
горию Числовой.

14.Сохраните таблицу. Третьему листу рабочей книги присвойте
имя Задача 3.

Задача 3. Муниципалитет города с кодом 10 ввел налог на лиц старше 18 лет в размере 10%. Определить величину налога. Соста­вить таблицу следующей формы (на листе Задача 3).

Функция ЕСЛИ

15. Введите в таблицу заглавие, шапку, цифровые и текстовые
данные.

16. В ячейку Е4 введите формулу =ЕСЛИ (И (В4=10; С4>18);
О4*0,1; 0). Формула означает, что если код города равен 10 и воз­
раст старше 18 лет, то сумма налога определяется умножением до­
хода на величину налога. В противном случае сумма налога равна
нулю. Скопируйте формулу из ячейки Е4 в Е5:Е6.

Задача 4. Произвести анализ обэ~^>ема продаж и определить на­сколько объем продаж, совершеннь_аг^к каждым агентом в отдельно­сти, отличается от среднего объема ипо всей группе агентов. Соста­вить таблицу следующей формы (на_ листе Задача 4):

Анализрбъ Объем пред,

4790

‘"3567

5873

; ;

ла продаж

Фамилия И.О.

рткпонение! Квадратичное !от среднего! отклонение

Марков П.Д.

и

Ни,; Антонов. С,Н,

;ШЧэрновИ.6.

Сред.арифметич, Дисперсия Станд. отклонение

функции ОКРУГЛ. АВ5. СТЕПЕНЬ. КОРЕНЬ

17. В ячейку В9 введите формулу =ОКРУГЛ(СРЗНАЧ (В5:В7);0).

18. В ячейку С5 введите формул:^ =АВ8(В5-$В$9). Скопируйте
формулу из ячейки С5 в ячейки С6:<ШГ7.

19. В ячейку О5 введите форму-^пу =СТЕПЕНЬ(С5;2). Скопи­
руйте формулу из ячейки В5 в ячейж=^л В6:В7.

20. В ячейку О10 введите формулу =ОКРУГЛ(СРЗНАЧ(О5:В7); 0).

21. В ячейку В11 введите формулу- =ОКРУГЛ(КОРЕНЬ (В10); 0).

функции ДИСПР и СТАНДОТКСЛОНП (по генеральной сово-купности)

22.Очистите ячейки В10 и В11, =чтобы произвести расчет дис­
персии и стандартного отклонение „ используя соответствующие
функции.

23.В ячейку О10 введите формул^^ =ДИСПР(В5:В7).

В ячейку В11 введите формулд-^ =СТАНДОТКЛОНП (В5:В7