Файл: Практикум Для студентов вузов Кемерово 2013 4 удк 004 (076) ббк 32. 81я7 И74.pdf
ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 02.12.2023
Просмотров: 563
Скачиваний: 8
ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.
3. Электронные таблицы Excel
138
Окончательный вид диаграммы рассеяния с нанесенным графи- ком регрессии показан на рис. 3.16.
Подобный способ построения регрессии в EXCEL являет- ся универсальным и применим для построения регрессионных функций любого вида с любым количеством параметров.
Построение линии тренда
Последовательность действий:
1. Построить диаграмму рассеяния, аналогично п. 1 пре- дыдущей части работы.
2. Щелкнуть по одной из точек на диаграмме правой кнопкой мыши и в контекстном меню выбрать пункт «Добавить линию тренда». Появляется окно диалога (рис. 3.22).
3. Выбрать вид регрессии установкой переключателя в нужную позицию.
4. Установить флажок «Показывать уравнение на диа- грамме». После щелчка по клавише [Закрыть] на диаграмме по- является график линии тренда и ее уравнение (см. рис. 3.19).
Рис. 3.22. Окно диалога для добавления линии тренда
3. Электронные таблицы Excel
139
Если присмотреться, видно, что линия тренда ложится на экспериментальные точки не так точно, как регрессия, постро- енная с помощью «Поиска решения». Параметры уравнения ли- нии тренда отличаются от значений параметров регрессии, най- денной с помощью «Поиска решения».
Сравнение методов определения параметров регрессии
Для сравнения двух способов определения параметров регрессии надо вычислить для линии тренда значение функ- ционала (3.8):
1. В ячейку I1 ввести заголовок «Тренд».
2. В ячейку I2 ввести формулу вычисления регрессионной функции с использованием численных значений параметров ли- нии тренда, взятых из уравнения на диаграмме линии тренда.
3. Скопировать формулу в ячейки Е3:Е11.
4. В ячейках J2:J11 вычислить отклонения линии тренда от экспериментальных значений y
i
5. В ячейку J12 ввести формулу
=СУММКВ(J2:J11)
Такой способ вычисления суммы квадратов отклонений удобен тем, что не требует вычисления самих квадратов, по- скольку функция СУММКВ(…), как можно понять из ее назва- ния, вычисляет именно сумму квадратов своих аргументов (яче- ек диапазона J2:J11).
6. Сравнить значения вычисленных сумм квадратов откло- нений. Для рассматриваемого примера, как видно из рис. 3.16, указанная сумма для линии тренда оказалась больше, чем для регрессии, параметры которой были определены «Поиском ре- шения». Таким образом, построение линии тренда не позволило найти реальный минимум функционала (3.8), а значит, ее пара- метры не отражают зависимость у(х) наиболее точно.
В EXCEL такое расхождение в большей или меньшей сте- пени наблюдается для всех нелинейных видов регрессии. При- чины этого объясняются на основе законов математической ста- тистики, изучаемых в курсе высшей математики.
3. Электронные таблицы Excel
140
Варианты заданий
Таблица 3.8
Xi Yi
Xi Yi
Xi Yi
1 2
3 1
0,302 1,276 0,946 0,301 0,593 1,128 2
0,556 1,565 0,992 0,339 1,111 0,873 3
1,296 2,220 1,327 0,423 1,785 0,788 4
1,469 2,165 1,400 0,432 1,937 0,769 5
1,627 2,452 2,218 0,779 2,042 0,812 6
2,249 2,482 2,655 0,933 2,342 0,712 7
2,983 2,855 3,155 1,321 3,002 0,660 8
3,096 2,837 3,467 1,766 3,320 0,643 9
3,236 2,887 3,765 2,097 4,240 0,625 10 3,672 3,023 4,397 3,193 4,342 0,614 4
5 6
1 0,638 1,045 0,588 1,589 0,874 1,324 2
1,612 0,738 0,833 1,911 1,462 1,935 3
1,622 0,738 1,194 2,126 2,165 2,925 4
2,522 0,565 1,562 2,362 2,359 3,118 5
3,137 0,521 2,537 3,376 2,886 4,838 6
3,900 0,463 2,883 3,901 2,982 4,573 7
4,623 0,420 3,861 4,836 3,494 7,028 8
5,311 0,416 4,296 5,095 3,574 6,640 9
6,246 0,353 5,009 5,428 4,121 10,287 10 6,550 0,359 5,707 6,219 4,166 9,710 7
8 9
1 0,913 3,667 0,128 0,240 0,630 1,236 2
1,338 5,361 1,109 0,589 1,252 1,048 3
1,447 6,038 1,746 1,167 1,916 1,002 4
1,666 6,853 2,532 2,187 2,105 0,898 5
2,607 16,612 2,977 3,435 2,212 0,975 6
3,190 30,838 2,987 3,242 2,418 0,979 7
3,510 40,393 3,851 7,207 2,551 0,894 8
4,359 92,118 4,079 8,858 3,146 0,849 9
4,572 113,603 4,549 15,172 3,683 0,865 10 5,017 181,838 5,031 20,935 4,375 0,828
3. Электронные таблицы Excel
141
Окончание табл. 3.8 10 11 12 1
0,721 0,905 0,413 1,522 0,736 1,105 2
0,987 1,073 0,741 1,513 0,758 1,162 3
1,487 1,522 1,070 1,700 1,026 1,269 4
2,103 2,388 1,942 1,818 2,003 2,056 5
2,406 3,252 2,570 2,142 2,865 2,800 6
2,709 3,889 3,032 2,321 3,573 3,542 7
3,469 7,400 3,337 2,156 3,803 3,928 8
4,432 14,841 3,624 2,502 4,487 5,228 9
5,406 34,562 4,017 2,434 5,206 7,563 10 5,531 36,439 4,265 2,856 5,467 8,063 13 14 15 1
0,667 0,451 0,651 1,574 0,633 0,526 2
1,097 0,613 1,408 1,358 1,446 0,419 3
1,670 0,816 2,057 1,255 1,821 0,383 4
2,176 0,885 3,016 1,036 2,066 0,307 5
2,939 1,225 3,089 1,160 2,946 0,229 6
3,645 1,393 3,660 1,126 3,227 0,217 7
4,204 1,538 4,118 0,988 3,593 0,188 8
4,620 1,622 4,700 0,984 3,957 0,179 9
5,089 1,690 5,630 0,901 4,692 0,136 10 5,274 1,724 6,514 0,920 5,564 0,104
3. Электронные таблицы Excel
142
Лабораторная работа № 4
Расчет ведомости на выплату заработной платы
Цель работы:
1. Освоение дополнительных приемов создания и автома- тизации расчетов в таблицах.
2. Знакомство с форматами представления числовых дан- ных.
Постановка задачи
Заработная плата сотрудников предприятия за неделю за- висит от тарифной ставки (стоимости одного часа), которая оп- ределяется должностью сотрудника, и количества отработанных часов. Сотрудникам начисляются дополнительно:
Разовая премия как определенный процент от заработ- ка по тарифу; значение премиального коэффициента может из- меняться при очередном начислении.
Надбавка за стаж непрерывной работы. Она равна 10 % от заработка по тарифу, если стаж составляет от 5 до 10 полных лет, и 20 % от заработка, если стаж превышает 10 лет. Если стаж работы меньше 5 лет, надбавка не начисляется.
Кроме того, из зарплаты автоматически высчитывается подоходный налог:
Первые 2000 р. налогом не облагаются;
С оставшейся части зарплаты взимается налог 13 %.
Необходимо провести начисление заработной платы со- трудникам компании (за неделю).
Задание:
Создать таблицу расчета зарплаты сотрудников по опи- санному образцу (см. рис. 3.23).
Порядок выполнения работы
1. Создать в рабочей книге два листа: Справочник и Зарплата.
3. Электронные таблицы Excel
143 2. На листе Зарплата создать заголовок таблицы: Расчет
заработной платы. Для этого ввести текст заголовка в ячейку
А1, установить для ячейки шрифт Times New Roman полужир- ный, кегль 14, затем выделить ячейки А1:L1 и установить вы- равнивание по горизонтали по центру выделения.
3. В ячейку А2 ввести текст Месяц, а в ячейку В2 - фор- мулу =СЕГОДНЯ(). Функция СЕГОДНЯ() не имеет аргумента и автоматически вычисляет текущую дату по показаниям систем- ного календаря. Назначить ячейкам А2:В2 стиль "Студенче- ский" (число в ячейке В2 покажет номер текущего дня, считая от 1.01.1900). Изменить формат числа в ячейке В2 на "Дата" (ва- риант, соответствующий рис. 3.23 – месяц и год).
4. В ячейку J2 ввести текст: Премия, %: , в ячейку K2 – произвольное число в диапазоне 0,05
0,25. Для ячеек диапазона
J2:K2 назначить стиль «Студенческий», затем изменить в ячейке
К2 формат числа на процентный с одним десятичным знаком и установить для диапазона общую полужирную границу.
5. Строку 3 пропустить.
6. В ячейки строки 4 ввести заголовки столбцов таблицы:
A – ФИО
B – Отдел
C – Должность
D – Стаж (лет)
E – Тариф, руб/ч
F – Отработал часов
G – Заработок по тарифу, руб
H – Премия, руб
I – За стаж, руб
J –Начислено всего, руб
K – Налог, руб
L – Сумма к выдаче, руб
Назначить ячейкам с заголовками формат «Студенче- ский». Вручную подобрать ширину столбцов так, чтобы заго- ловки выглядели, как показано на рис. 3.23.
3. Электронные таблицы Excel
144 7. Выделить диапазон А5:L25. Назначить ячейкам стиль
«Студенческий».
8. В ячейках, где будут помещаться денежные суммы, из- менить формат чисел на «Числовой» с двумя десятичными зна- ками. При установлении формата дополнительно установить флажок «Разделитель групп разрядов» для облегчения чтения длинных чисел.
9. Заполнить первый столбец произвольными фамилиями и инициалами.
10. Т.к. названия отделов в ячейках столбца «Отдел» по- вторяются (см. рис. 3.23), для ускорения ввода в эти ячейки соз- дать пользовательский список готовых вариантов ввода. Для создания списка:
перейти на лист Справочник;
заполнить диапазон А1:А9 по образцу рис. 3.24;
выделить диапазон А3:А9 и присвоить ему имя Отдел
(см. § 3.2);
145
Рис. 3.23. Итоговый вид таблицы расчета зарплаты
145
3.
Эл
ект
ро
нны
е
т
аб
лиц
ы
E
xce
l
3. Электронные таблицы Excel
146
Рис. 3.24. Создание списков значений для ввода на листе Справочник
перейти на лист Зарплата. Выделить диапазон
В5:В25. Выполнить команду: «Данные» – «Работа с данными» –
[Проверка данных]. Возникает окно диалога «Проверка вводи- мых значений». В окне указать: «Тип данных» – Список,
«Источник» – =Отдел (рис. 3.25), затем щелкнуть [ОК].
После этого при активизации любой ячейки диапазона
В5:В25 рядом с ней возникает значок списка. Раскрыв список, можно мышью выбрать из него нужное значение.
Рис. 3.25. Назначение параметров списка ввода
3. Электронные таблицы Excel
147 11. Заполнить диапазон В5:В25, используя созданный список. Названия отделов выбрать произвольно, но так, чтобы в списке оказались работники всех отделов, не менее двух чело- век из каждого отдела.
12. Создать на листе Справочник список типовых долж- ностей для ввода ( см. рис. 3.24):
Бухгалтер
Грузчик
Директор
Инженер
Кассир
Менеджер
Начальник отдела
Программист
Рабочий
Секретарь
Присвоить диапазону С3:С12, содержащему список, имя
Должность.
13. Создать для ячеек столбца «Должность» (С5:С25) на листе Зарплата список значений для ввода на основе диапазо- на Должность. Заполнить ячейки произвольным образом, но так, чтобы в нем присутствовали все должности из списка.
14. Заполнить ячейки D5:D25 произвольными значениями стажа работы в фирме (в диапазоне 1
30 лет).
15. Допустим, что часовая тарифная ставка сотрудника определяется только его должностью. В этом случае указание должности сразу же определяет размер ставки. Надо сделать так, чтобы для любой должности из созданного списка должно- стей значение ставки заносилось в таблицу автоматически. Это позволяет добиться экономии затрат труда и избежать случай- ных опечаток при вводе.
Excel предоставляет такую возможность. В категории функций
«Ссылки и таблицы» имеется функция
ВПР(значение; таблица; номер). Эта функция ищет в пер- вом столбце указанной в качестве второго аргумента таблицы
(диапазона) указанное в качестве первого из аргументов значе- ние и возвращает значение из той же строки, находящееся в столбце с указанным номером (у функции имеется еще четвер- тый – необязательный – аргумент; в нашем случае его следует опустить).
3. Электронные таблицы Excel
148
Последовательность действий:
перейти на лист Справочник;
создать рядом со списком должностей столбец Тариф, в котором указать значения тарифных ставок, соответствующих должностям (см. рис. 3.24);
перейти на лист Зарплата;
в ячейку E5 ввести формулу.
=ВПР(C5; Справочник!$C$3:$D$12; 2)
Для вставки функции можно воспользоваться Мастером функций, для создания ссылки на диапазон С3:D12 листа
Справочник использовать механизм быстрого создания ссы- лок (не прекращая редактирования формулы, перейти на лист
Справочник и мышью выделить нужный диапазон).
мышью скопировать формулу из ячейки E5 в диапазон
E6:E25.
16. Заполнить вручную ячейки столбца Отработал ча-
сов (F5:F25). Значения в ячейках выбрать произвольно из диа- пазона 16
50. Изменить формат чисел в ячейках, установив чис- ло десятичных знаков – 0.
17. Ввести в ячейку G5 формулу расчета заработка перво- го сотрудника. Заработок рассчитывается как произведение та- рифной ставки на количество отработанных часов. Формулу для ячейки создать самостоятельно. Скопировать формулу в ячейки диапазона G6:G25.
18. Ввести в ячейку H5 формулу расчета премии первого сотрудника как произведения заработка на коэффициент, нахо- дящийся в ячейке K2. Поскольку формула далее должна копи- роваться вниз, а коэффициент в каждой строке должен браться из одной и той же ячейки K2, ссылку на ячейку сделать абсо- лютной. Формулу создать самостоятельно и скопировать в ячейки H6:H25. Изменить в ячейке K2 значение коэффициента премии и убедиться, что премия автоматически пересчитывает- ся для всех сотрудников.
19. В ячейку I5 ввести формулу расчета надбавки за стаж.
Надбавка вычисляется по принципу:
3. Электронные таблицы Excel
149 0,
5 10 % *
,
5 10 20 % *
,
1 0
если стаж
Надбавка
Заработок если
стаж
Заработок если стаж
Формулу создать самостоятельно по аналогии с (3.1) и скопировать в ячейки I6:I25.
20. В ячейке J5 вычислить общую сумму начислений для первого сотрудника (сумма значения ячеек G5, H5, I5), исполь- зуя прием автосуммирования – выделить диапазон G5:J5 и щелкнуть кнопку «Главная» – «Редактирование» – [
]. Затем выделить отдельно ячейку J5 и скопировать полученную в ней формулу =СУММ(G5:I5) в ячейки J6:J25.
21. В ячейке К5 создать самостоятельно с использованием функции ЕСЛИ(…) формулу расчета подоходного налога:
Скопировать полученную формулу в ячейки K6:K25.
22. В ячейке L5 создать формулу вычисления суммы к вы- даче (разности между «Начислено всего» и «Налог»), скопиро- вать в ячейки L6:L25.
23. В ячейку К26 ввести текст ИТОГО: В ячейке L26 с помощью автосуммирования вычислить итоговую сумму выда- ваемых денег. Назначить ячейкам K26:L26 стиль «Студенче- ский», затем дополнительно – полужирное начертание симво- лов.
Выделить ячейки, содержащие денежные суммы. Изме- нить формат чисел с «Числовой» на «Денежный» (обозначение валюты – «р.»). Затем изменить формат на «Финансовый». Оце- нить изменение вида отдельных ячеек в разных форматах. Сде- лать вывод о том, какой формат более удобен для итогового представления денежных сумм.
Обратите внимание, что в форматах «Денежный»
и «Финансовый» группы разрядов разделяются автоматически.
25. Создать лист Зарплата–формулы, на котором ото- бразить содержимое листа Зарплата в режиме показа формул.
3. Электронные таблицы Excel
150
Лабораторная работа № 5
Анализ таблицы данных
Цель работы: знакомство с приемами обработки и анали- за таблиц данных.
Предварительные сведения о таблицах данных
Одной из часто встречающихся задач в Excel является ра- бота с таблицей, содержащей одинаковые наборы сведений о значительном количестве однотипных объектов. Примером мо- жет служить созданная в предыдущей работе таблица расчета зарплаты – в ней содержится один и тот же набор сведений о каждом сотруднике (Ф. И. О., место работы, должность, стаж ра- боты и т.д.). Такая таблица называется списком, или таблицей дан-
ных (ТД).
ТД обязана иметь определенную структуру. В первой строке диапазона, занятого таблицей, должны размещаться тек- стовые заголовки столбцов (столбцы в ТД называются полями).
Каждый заголовок поля должен целиком помещаться в одну ячейку. Если заголовок поля длинный и должен занять несколь- ко строк внутри одной ячейки, надо установить для ячеек строки заголовков выравнивание с переносом по словам. В последую- щих строках размещаются данные. Каждая строка данных в ТД называется записью. Между строкой заголовков и строками за- писей нельзя оставлять пустые строки.
По умолчанию любой диапазон листа, который заполнен данными так, что ни одна строка или столбец не являются пол- ностью пустыми, при выполнении команд раздела «Данные» рассматривается как ТД. Первая строка считается строкой заго- ловков (если в ней окажутся пустые ячейки, соответствующие поля получат имена по умолчанию: Столбец 1, Столбец 2 и т.д.). Границами ТД считаются ближайшие к ней полностью пустые строки и столбцы.
После создания ТД выделение любой ячейки в ней делает
ТД активной, после чего можно проводить анализ данных в ней средствами вкладки «Данные».
138
Окончательный вид диаграммы рассеяния с нанесенным графи- ком регрессии показан на рис. 3.16.
Подобный способ построения регрессии в EXCEL являет- ся универсальным и применим для построения регрессионных функций любого вида с любым количеством параметров.
Построение линии тренда
Последовательность действий:
1. Построить диаграмму рассеяния, аналогично п. 1 пре- дыдущей части работы.
2. Щелкнуть по одной из точек на диаграмме правой кнопкой мыши и в контекстном меню выбрать пункт «Добавить линию тренда». Появляется окно диалога (рис. 3.22).
3. Выбрать вид регрессии установкой переключателя в нужную позицию.
4. Установить флажок «Показывать уравнение на диа- грамме». После щелчка по клавише [Закрыть] на диаграмме по- является график линии тренда и ее уравнение (см. рис. 3.19).
Рис. 3.22. Окно диалога для добавления линии тренда
3. Электронные таблицы Excel
139
Если присмотреться, видно, что линия тренда ложится на экспериментальные точки не так точно, как регрессия, постро- енная с помощью «Поиска решения». Параметры уравнения ли- нии тренда отличаются от значений параметров регрессии, най- денной с помощью «Поиска решения».
Сравнение методов определения параметров регрессии
Для сравнения двух способов определения параметров регрессии надо вычислить для линии тренда значение функ- ционала (3.8):
1. В ячейку I1 ввести заголовок «Тренд».
2. В ячейку I2 ввести формулу вычисления регрессионной функции с использованием численных значений параметров ли- нии тренда, взятых из уравнения на диаграмме линии тренда.
3. Скопировать формулу в ячейки Е3:Е11.
4. В ячейках J2:J11 вычислить отклонения линии тренда от экспериментальных значений y
i
5. В ячейку J12 ввести формулу
=СУММКВ(J2:J11)
Такой способ вычисления суммы квадратов отклонений удобен тем, что не требует вычисления самих квадратов, по- скольку функция СУММКВ(…), как можно понять из ее назва- ния, вычисляет именно сумму квадратов своих аргументов (яче- ек диапазона J2:J11).
6. Сравнить значения вычисленных сумм квадратов откло- нений. Для рассматриваемого примера, как видно из рис. 3.16, указанная сумма для линии тренда оказалась больше, чем для регрессии, параметры которой были определены «Поиском ре- шения». Таким образом, построение линии тренда не позволило найти реальный минимум функционала (3.8), а значит, ее пара- метры не отражают зависимость у(х) наиболее точно.
В EXCEL такое расхождение в большей или меньшей сте- пени наблюдается для всех нелинейных видов регрессии. При- чины этого объясняются на основе законов математической ста- тистики, изучаемых в курсе высшей математики.
3. Электронные таблицы Excel
140
Варианты заданий
Таблица 3.8
Xi Yi
Xi Yi
Xi Yi
1 2
3 1
0,302 1,276 0,946 0,301 0,593 1,128 2
0,556 1,565 0,992 0,339 1,111 0,873 3
1,296 2,220 1,327 0,423 1,785 0,788 4
1,469 2,165 1,400 0,432 1,937 0,769 5
1,627 2,452 2,218 0,779 2,042 0,812 6
2,249 2,482 2,655 0,933 2,342 0,712 7
2,983 2,855 3,155 1,321 3,002 0,660 8
3,096 2,837 3,467 1,766 3,320 0,643 9
3,236 2,887 3,765 2,097 4,240 0,625 10 3,672 3,023 4,397 3,193 4,342 0,614 4
5 6
1 0,638 1,045 0,588 1,589 0,874 1,324 2
1,612 0,738 0,833 1,911 1,462 1,935 3
1,622 0,738 1,194 2,126 2,165 2,925 4
2,522 0,565 1,562 2,362 2,359 3,118 5
3,137 0,521 2,537 3,376 2,886 4,838 6
3,900 0,463 2,883 3,901 2,982 4,573 7
4,623 0,420 3,861 4,836 3,494 7,028 8
5,311 0,416 4,296 5,095 3,574 6,640 9
6,246 0,353 5,009 5,428 4,121 10,287 10 6,550 0,359 5,707 6,219 4,166 9,710 7
8 9
1 0,913 3,667 0,128 0,240 0,630 1,236 2
1,338 5,361 1,109 0,589 1,252 1,048 3
1,447 6,038 1,746 1,167 1,916 1,002 4
1,666 6,853 2,532 2,187 2,105 0,898 5
2,607 16,612 2,977 3,435 2,212 0,975 6
3,190 30,838 2,987 3,242 2,418 0,979 7
3,510 40,393 3,851 7,207 2,551 0,894 8
4,359 92,118 4,079 8,858 3,146 0,849 9
4,572 113,603 4,549 15,172 3,683 0,865 10 5,017 181,838 5,031 20,935 4,375 0,828
3. Электронные таблицы Excel
141
Окончание табл. 3.8 10 11 12 1
0,721 0,905 0,413 1,522 0,736 1,105 2
0,987 1,073 0,741 1,513 0,758 1,162 3
1,487 1,522 1,070 1,700 1,026 1,269 4
2,103 2,388 1,942 1,818 2,003 2,056 5
2,406 3,252 2,570 2,142 2,865 2,800 6
2,709 3,889 3,032 2,321 3,573 3,542 7
3,469 7,400 3,337 2,156 3,803 3,928 8
4,432 14,841 3,624 2,502 4,487 5,228 9
5,406 34,562 4,017 2,434 5,206 7,563 10 5,531 36,439 4,265 2,856 5,467 8,063 13 14 15 1
0,667 0,451 0,651 1,574 0,633 0,526 2
1,097 0,613 1,408 1,358 1,446 0,419 3
1,670 0,816 2,057 1,255 1,821 0,383 4
2,176 0,885 3,016 1,036 2,066 0,307 5
2,939 1,225 3,089 1,160 2,946 0,229 6
3,645 1,393 3,660 1,126 3,227 0,217 7
4,204 1,538 4,118 0,988 3,593 0,188 8
4,620 1,622 4,700 0,984 3,957 0,179 9
5,089 1,690 5,630 0,901 4,692 0,136 10 5,274 1,724 6,514 0,920 5,564 0,104
3. Электронные таблицы Excel
142
Лабораторная работа № 4
Расчет ведомости на выплату заработной платы
Цель работы:
1. Освоение дополнительных приемов создания и автома- тизации расчетов в таблицах.
2. Знакомство с форматами представления числовых дан- ных.
Постановка задачи
Заработная плата сотрудников предприятия за неделю за- висит от тарифной ставки (стоимости одного часа), которая оп- ределяется должностью сотрудника, и количества отработанных часов. Сотрудникам начисляются дополнительно:
Разовая премия как определенный процент от заработ- ка по тарифу; значение премиального коэффициента может из- меняться при очередном начислении.
Надбавка за стаж непрерывной работы. Она равна 10 % от заработка по тарифу, если стаж составляет от 5 до 10 полных лет, и 20 % от заработка, если стаж превышает 10 лет. Если стаж работы меньше 5 лет, надбавка не начисляется.
Кроме того, из зарплаты автоматически высчитывается подоходный налог:
Первые 2000 р. налогом не облагаются;
С оставшейся части зарплаты взимается налог 13 %.
Необходимо провести начисление заработной платы со- трудникам компании (за неделю).
Задание:
Создать таблицу расчета зарплаты сотрудников по опи- санному образцу (см. рис. 3.23).
Порядок выполнения работы
1. Создать в рабочей книге два листа: Справочник и Зарплата.
3. Электронные таблицы Excel
143 2. На листе Зарплата создать заголовок таблицы: Расчет
заработной платы. Для этого ввести текст заголовка в ячейку
А1, установить для ячейки шрифт Times New Roman полужир- ный, кегль 14, затем выделить ячейки А1:L1 и установить вы- равнивание по горизонтали по центру выделения.
3. В ячейку А2 ввести текст Месяц, а в ячейку В2 - фор- мулу =СЕГОДНЯ(). Функция СЕГОДНЯ() не имеет аргумента и автоматически вычисляет текущую дату по показаниям систем- ного календаря. Назначить ячейкам А2:В2 стиль "Студенче- ский" (число в ячейке В2 покажет номер текущего дня, считая от 1.01.1900). Изменить формат числа в ячейке В2 на "Дата" (ва- риант, соответствующий рис. 3.23 – месяц и год).
4. В ячейку J2 ввести текст: Премия, %: , в ячейку K2 – произвольное число в диапазоне 0,05
0,25. Для ячеек диапазона
J2:K2 назначить стиль «Студенческий», затем изменить в ячейке
К2 формат числа на процентный с одним десятичным знаком и установить для диапазона общую полужирную границу.
5. Строку 3 пропустить.
6. В ячейки строки 4 ввести заголовки столбцов таблицы:
A – ФИО
B – Отдел
C – Должность
D – Стаж (лет)
E – Тариф, руб/ч
F – Отработал часов
G – Заработок по тарифу, руб
H – Премия, руб
I – За стаж, руб
J –Начислено всего, руб
K – Налог, руб
L – Сумма к выдаче, руб
Назначить ячейкам с заголовками формат «Студенче- ский». Вручную подобрать ширину столбцов так, чтобы заго- ловки выглядели, как показано на рис. 3.23.
3. Электронные таблицы Excel
144 7. Выделить диапазон А5:L25. Назначить ячейкам стиль
«Студенческий».
8. В ячейках, где будут помещаться денежные суммы, из- менить формат чисел на «Числовой» с двумя десятичными зна- ками. При установлении формата дополнительно установить флажок «Разделитель групп разрядов» для облегчения чтения длинных чисел.
9. Заполнить первый столбец произвольными фамилиями и инициалами.
10. Т.к. названия отделов в ячейках столбца «Отдел» по- вторяются (см. рис. 3.23), для ускорения ввода в эти ячейки соз- дать пользовательский список готовых вариантов ввода. Для создания списка:
перейти на лист Справочник;
заполнить диапазон А1:А9 по образцу рис. 3.24;
выделить диапазон А3:А9 и присвоить ему имя Отдел
(см. § 3.2);
145
Рис. 3.23. Итоговый вид таблицы расчета зарплаты
145
3.
Эл
ект
ро
нны
е
т
аб
лиц
ы
E
xce
l
3. Электронные таблицы Excel
146
Рис. 3.24. Создание списков значений для ввода на листе Справочник
перейти на лист Зарплата. Выделить диапазон
В5:В25. Выполнить команду: «Данные» – «Работа с данными» –
[Проверка данных]. Возникает окно диалога «Проверка вводи- мых значений». В окне указать: «Тип данных» – Список,
«Источник» – =Отдел (рис. 3.25), затем щелкнуть [ОК].
После этого при активизации любой ячейки диапазона
В5:В25 рядом с ней возникает значок списка. Раскрыв список, можно мышью выбрать из него нужное значение.
Рис. 3.25. Назначение параметров списка ввода
3. Электронные таблицы Excel
147 11. Заполнить диапазон В5:В25, используя созданный список. Названия отделов выбрать произвольно, но так, чтобы в списке оказались работники всех отделов, не менее двух чело- век из каждого отдела.
12. Создать на листе Справочник список типовых долж- ностей для ввода ( см. рис. 3.24):
Бухгалтер
Грузчик
Директор
Инженер
Кассир
Менеджер
Начальник отдела
Программист
Рабочий
Секретарь
Присвоить диапазону С3:С12, содержащему список, имя
Должность.
13. Создать для ячеек столбца «Должность» (С5:С25) на листе Зарплата список значений для ввода на основе диапазо- на Должность. Заполнить ячейки произвольным образом, но так, чтобы в нем присутствовали все должности из списка.
14. Заполнить ячейки D5:D25 произвольными значениями стажа работы в фирме (в диапазоне 1
30 лет).
15. Допустим, что часовая тарифная ставка сотрудника определяется только его должностью. В этом случае указание должности сразу же определяет размер ставки. Надо сделать так, чтобы для любой должности из созданного списка должно- стей значение ставки заносилось в таблицу автоматически. Это позволяет добиться экономии затрат труда и избежать случай- ных опечаток при вводе.
Excel предоставляет такую возможность. В категории функций
«Ссылки и таблицы» имеется функция
ВПР(значение; таблица; номер). Эта функция ищет в пер- вом столбце указанной в качестве второго аргумента таблицы
(диапазона) указанное в качестве первого из аргументов значе- ние и возвращает значение из той же строки, находящееся в столбце с указанным номером (у функции имеется еще четвер- тый – необязательный – аргумент; в нашем случае его следует опустить).
3. Электронные таблицы Excel
148
Последовательность действий:
перейти на лист Справочник;
создать рядом со списком должностей столбец Тариф, в котором указать значения тарифных ставок, соответствующих должностям (см. рис. 3.24);
перейти на лист Зарплата;
в ячейку E5 ввести формулу.
=ВПР(C5; Справочник!$C$3:$D$12; 2)
Для вставки функции можно воспользоваться Мастером функций, для создания ссылки на диапазон С3:D12 листа
Справочник использовать механизм быстрого создания ссы- лок (не прекращая редактирования формулы, перейти на лист
Справочник и мышью выделить нужный диапазон).
мышью скопировать формулу из ячейки E5 в диапазон
E6:E25.
16. Заполнить вручную ячейки столбца Отработал ча-
сов (F5:F25). Значения в ячейках выбрать произвольно из диа- пазона 16
50. Изменить формат чисел в ячейках, установив чис- ло десятичных знаков – 0.
17. Ввести в ячейку G5 формулу расчета заработка перво- го сотрудника. Заработок рассчитывается как произведение та- рифной ставки на количество отработанных часов. Формулу для ячейки создать самостоятельно. Скопировать формулу в ячейки диапазона G6:G25.
18. Ввести в ячейку H5 формулу расчета премии первого сотрудника как произведения заработка на коэффициент, нахо- дящийся в ячейке K2. Поскольку формула далее должна копи- роваться вниз, а коэффициент в каждой строке должен браться из одной и той же ячейки K2, ссылку на ячейку сделать абсо- лютной. Формулу создать самостоятельно и скопировать в ячейки H6:H25. Изменить в ячейке K2 значение коэффициента премии и убедиться, что премия автоматически пересчитывает- ся для всех сотрудников.
19. В ячейку I5 ввести формулу расчета надбавки за стаж.
Надбавка вычисляется по принципу:
3. Электронные таблицы Excel
149 0,
5 10 % *
,
5 10 20 % *
,
1 0
если стаж
Надбавка
Заработок если
стаж
Заработок если стаж
Формулу создать самостоятельно по аналогии с (3.1) и скопировать в ячейки I6:I25.
20. В ячейке J5 вычислить общую сумму начислений для первого сотрудника (сумма значения ячеек G5, H5, I5), исполь- зуя прием автосуммирования – выделить диапазон G5:J5 и щелкнуть кнопку «Главная» – «Редактирование» – [
]. Затем выделить отдельно ячейку J5 и скопировать полученную в ней формулу =СУММ(G5:I5) в ячейки J6:J25.
21. В ячейке К5 создать самостоятельно с использованием функции ЕСЛИ(…) формулу расчета подоходного налога:
Скопировать полученную формулу в ячейки K6:K25.
22. В ячейке L5 создать формулу вычисления суммы к вы- даче (разности между «Начислено всего» и «Налог»), скопиро- вать в ячейки L6:L25.
23. В ячейку К26 ввести текст ИТОГО: В ячейке L26 с помощью автосуммирования вычислить итоговую сумму выда- ваемых денег. Назначить ячейкам K26:L26 стиль «Студенче- ский», затем дополнительно – полужирное начертание симво- лов.
Выделить ячейки, содержащие денежные суммы. Изме- нить формат чисел с «Числовой» на «Денежный» (обозначение валюты – «р.»). Затем изменить формат на «Финансовый». Оце- нить изменение вида отдельных ячеек в разных форматах. Сде- лать вывод о том, какой формат более удобен для итогового представления денежных сумм.
Обратите внимание, что в форматах «Денежный»
и «Финансовый» группы разрядов разделяются автоматически.
25. Создать лист Зарплата–формулы, на котором ото- бразить содержимое листа Зарплата в режиме показа формул.
3. Электронные таблицы Excel
150
Лабораторная работа № 5
Анализ таблицы данных
Цель работы: знакомство с приемами обработки и анали- за таблиц данных.
Предварительные сведения о таблицах данных
Одной из часто встречающихся задач в Excel является ра- бота с таблицей, содержащей одинаковые наборы сведений о значительном количестве однотипных объектов. Примером мо- жет служить созданная в предыдущей работе таблица расчета зарплаты – в ней содержится один и тот же набор сведений о каждом сотруднике (Ф. И. О., место работы, должность, стаж ра- боты и т.д.). Такая таблица называется списком, или таблицей дан-
ных (ТД).
ТД обязана иметь определенную структуру. В первой строке диапазона, занятого таблицей, должны размещаться тек- стовые заголовки столбцов (столбцы в ТД называются полями).
Каждый заголовок поля должен целиком помещаться в одну ячейку. Если заголовок поля длинный и должен занять несколь- ко строк внутри одной ячейки, надо установить для ячеек строки заголовков выравнивание с переносом по словам. В последую- щих строках размещаются данные. Каждая строка данных в ТД называется записью. Между строкой заголовков и строками за- писей нельзя оставлять пустые строки.
По умолчанию любой диапазон листа, который заполнен данными так, что ни одна строка или столбец не являются пол- ностью пустыми, при выполнении команд раздела «Данные» рассматривается как ТД. Первая строка считается строкой заго- ловков (если в ней окажутся пустые ячейки, соответствующие поля получат имена по умолчанию: Столбец 1, Столбец 2 и т.д.). Границами ТД считаются ближайшие к ней полностью пустые строки и столбцы.
После создания ТД выделение любой ячейки в ней делает
ТД активной, после чего можно проводить анализ данных в ней средствами вкладки «Данные».