Лаб. работа 4, 6 и задание 3. работа в excel

1.Реферат
Введение
2.Основная часть
2.1. ЛАБОРАТОРНАЯ РАБОТА № 4
2.1.1. Предварительные замечания о форматах числовых данных в MS Excel и о функциях округления
2.1.2. Формула расчета зарплаты работника с помощью коэффициента трудового участия (КТУ)
2.1.3. Использование MS Excel для расчета зарплат
2.2. Дополнительные задания к лабораторной работе №4
2.2.1. Расчет зарплаты работников с измененной общей суммой при сохранении числа работников и при равных КТУ
2.2.2. Расчет зарплаты работников при изменении числа работников и при равных КТУ
2.2.3. Расчет зарплаты работников при различных КТУ
2.3. ЛАБОРАТОРНАЯ РАБОТА № 6
2.3.1. Пример создания одномерной таблицы подстановки
2.3.2. Пример создания двумерной таблицы подстановки
2.4. Задания к лабораторной работе №6
2.4.1. Построение графиков функций одной переменной с помощью таблицы подстановки
2.4.2. Остальные задания к лабораторной работе №6
2.5. ЗАДАНИЕ №3
2.5.1. Задания по лабораторным работам №4 и №6
2.5.2. Задание по построению алгоритма автоматического поиска элемента заданной таблице
2.6. Описание приложений
Заключение
Список использованных источников
3.Приложения
3.1. Расчет зарплаты работников при разных квалификациях работников и разных КТУ и большей численности коллектива
3.2. Применение двумерной таблицы подстановки для построения графиков сложных функций в трехмерном пространстве

Лаб. работа 4, 6 и задание 3. работа в excel

0,333333
673 333,33р.
2
Петров П. П.
12
22
1
0,333333
673 333,33р.
3
Сидоров С.С.
12
22
1
0,333333
673 333,33р.
Итого
792
1
2 020 000,00р.
2.2.2. Расчет зарплаты работников при изменении числа работников и при равных КТУ
Рассмотрим заполнение таблицы зарплаты для другого общего числа работников при равных КТУ. Мы выбираем число работников большее в 2 раза, оно равно шести. Также мы изменяем общую сумму заработка. Эти изменения требуют следующих последовательных действий:
скопировать таблицу, приготовленную ранее для трех работников в другое место на свободном участке, пусть новая таблица вставлена в диапазон A8:B13;
выделить ряд, соответствующий надписи «Итого» и вызвать меню «Вставка», пункт «Ряд». Таким образом, между рядами третьего работника и строкой «Итого» появляется три пока незаполненных ряда, теперь, таблица расположена в диапазоне ячеек A8:B16;
вносим коррективы в сумму произведений, которая находится в строке «Итого», правильная формула будет выглядеть так ..=СУММПРОИЗВ(C10:C15;D10:D15;E10:E15);
вносим коррективы в абсолютные ссылки в ячейках для доли зарплаты (ячейка F10) и зарплаты (ячейка G10) для первого работника, правильный вид формул будет ..=C10*D10*E10/$E$16 и ..=F10*$G$8, соответственно;
указываем значения 3, 4, 5 соответственно сверху вниз в пустых ячейках первой колонки, придумываем и записываем фамилии работников;
указываем для работников 3, 4 и 5 те же значения квалификации , отработанного времени D и КТУ (столбцы с третьего по пятый), что определены для работников 1, 2 и 3;
изменяем общую сумму заработка (в верхнюю правую ячейку таблицы вводим значение 139 000);
производим коррекцию долей зарплаты и зарплаты для остальных работников, для этого выделяем диапазон F10:G10, нажимаем Ctrl-C (копируем формулы), выделяем диапазон F10:G15 и нажимаем Ctrl-C (вставляем формулы).
Результаты расчетов в окончательном виде представлены в табл. 2.6.
Таблица 2.6
Вид таблицы с расчетами зарплаты работников с измененной общей суммой и измененным числом работников при равных КТУ
Общая начисленная сумма:
139 000,00
№ п.п.
ФИО
α
D
КТУ
Доля
Заработная плата
1
Иванов И. И.
12
22
1
0,166667
23 166,67р.
2
Петров П. П.
12
22
1
0,166667
23 166,67р.
3
Сидоров С.С.
12
22
1
0,166667
23 166,67р.
4
Васильев В.В.
12
22
1
0,166667
23 166,67р.
5
Дементьев Д.Д.
12
22
1
0,166667
23 166,67р.
6
Федоров Ф.Ф.
12
22
1
0,166667
23 166,67р.
Итого
1584
1
139 000,00р.
2.2.3. Расчет зарплаты работников при различных КТУ
Рассмотрим заполнение таблицы зарплаты в более обобщенном случае, при различных КТУ работников. Пусть для шести работников КТУ задаются следующим массивом {0,877; 1,324; 1,139; 0,450; 1,264; 0,946}. Эти значения подбираем следующим способом: первые пять – произвольно, а последнее находим из условия нормировки КТУ коллектива. Так, например, если для пяти работников КТУ указаны в диапазоне ячеек E22:E26, то в ячейку E27 мы вводим формулу ..= 6 – СУММ(E22:E26). В остальном, порядок вычисления полностью описывается указанным выше в п. 2.1.3 алгоритмом. Результат вычислений приведен ниже в табл. 2.7.
Таблица 2.7
Вид таблицы с расчетами зарплаты работников при различных КТУ
Общая начисленная сумма:
139 000,00р.
№ п.п.
ФИО
Α
D
КТУ
Доля
Заработная плата
1
Иванов И. И.
12
22
0,877
0,146167
20 317,17р.
2
Петров П. П.
12
22
1,324
0,220667
30 672,67р.
3
Сидоров С.С.
12
22
1,139
0,189833
26 386,83р.
4
Васильев В.В.
12
22
0,450
0,075000
10 425,00р.
5
Дементьев Д.Д.
12
22
1,264
0,210667
29 282,67р.
6
Федоров Ф.Ф.
12
22
0,946
0,157667
21 915,67р.
Итого
1584
1
139 000,00р.
2.3. ЛАБОРАТОРНАЯ РАБОТА № 6
Таблицы подстановки являются мощным встроенным средством MS Excel для быстрого вычисления значений функций одной и двух переменных. Таблицы подстановки вычисляются как одномерные или двумерные массивы. Рассмотрим примеры применения таблиц подстановки.
2.3.1. Пример создания одномерной таблицы подстановки
Проиллюстрируем построение одномерной таблицы подстановки на примере вычисления значений функции Y = 5*X. Создадим новую книгу и выберем в ней пустой лист. Введем в ячейку B2 значение 1, затем под ней в ячейку B3 выражение ..=5*B2. Для ввода значения B2 можно также указать на него мышью. Теперь заполним диапазон ячеек С2:K2 значениями 2, 3, …, 10. Сделать это проще всего так:
выделить ячейку B2 (границы клетки будут выделены жирными линиями);
навести указатель мыши на правый нижний угол ячейки, и нажать клавишу Ctrl;
удерживая клавишу Ctrl протащить мышью выделение на нужную длину (до ячейки K2).
Этот способ известен как автозаполнение. Альтернативным способом ввода значений является копирование формулы. Для этого нужно:
ввести в ячейку С2 значение ..=B2+1;
скопировать ячейку С2 (клавиши Ctrl-C или специальный значок);
выделить диапазон D2:K2;
вставить (клавиши Ctrl-V или специальный значок).
Таким образом, в диапазон B2:K2 мы ввели диапазон значений переменной X, а в ячейке B3 уже находится значение функции Y =5*X для значения X = 1. Теперь приступаем к вводу таблицы подстановки:
выделяем диапазон B2:K3;
вызываем пункт меню «Данные», «Таблица подстановки»;
появляется диалоговое окно, в котором представлены два поля – ряды и колонки;
в верхнее поле, соответствующее рядам, вводим адрес ячейки B2 (то есть, указываем в верхнем поле B2);
нажимаем OK, MS Excel вычисляет таблицу подстановки.
2.3.2. Пример создания двумерной таблицы подстановки
Проиллюстрируем создание двумерной таблицы подстановки на примере таблицы умножения натуральных чисел от 1 до 10. Алгоритм действий следующий:
введем в ячейку B6 число 1, затем под ней в ячейку B7 тоже значение 1, а в ячейку B8 выражение ..=B6*B7;
заполним диапазоны С8:K8 и B9:B17 значениями 2, 3, …, 10 (делается это с помощью любого из указанных выше в п. 2.1.1 способа, то есть применяется автозаполнение или ввод формул);
выделяем диапазон C8:K17;
вызываем пункт меню «Данные», «Таблица подстановки»;
появляется диалоговое окно, в котором представлены два поля – ряды и колонки;
в верхнее поле, соответствующее рядам, вводим адрес ячейки B6 (указываем в верхнем поле B6);
в нижнее поле, соответствующее колонкам, вводим адрес ячейки B7 (указываем в верхнем поле B7);
нажимаем OK, MS Excel вычисляет таблицу подстановки.
Здесь необходимо отметить, что подобную таблицу (двухмерную таблицу для функции двух переменных) в MS Excel можно также с помощью абсолютных ссылок. Однако простое общей трудоемкости указанного в данном разделе способа и способа с введением абсолютных ссылок показывает, что именно таблицы подстановки являются менее затратными. Следует также отметить, что для массивов большой размерности способ с использованием абсолютных ссылок становится практически неприменимым из-за большого количества рутинной ручной работы, которая, в силу своей специфики, плохо поддается элементарному визуальному контролю.
2.4. Задания к лабораторной работе №6
2.4.1. Построение графиков функций одной переменной с помощью таблицы подстановки
Согласно заданию, необходимо использовать двумерную таблицу подстановки для построения графиков функций Y1=X2 и Y2=X3 от одной переменной. Двухмерность в данном случае вызвана тем, что одно измерение служит областью изменения независимой переменной X, а другое измерение определяется наличием двух (а не одной) функции.
Как известно графики функций Y1=X2 и Y2=X3 имеют два отличия:
1. –Y1(X) = Y1(X), в то время как –Y2(X) = –Y2(X), в математике это называют четностью и нечетностью функций, соответственно.
2. Для значений |X| < 1 (при значениях X, по модулю меньших единицы, то есть –1 < X < 1) всегда имеем |Y2(X)| < |Y1(X)|, для значений |X| = 1 всегда верно |Y2(X)| = |Y1(X)|, а для остальных значений |X| > 1 |Y2(X)| > |Y1(X)|.
Выберем для переменной X диапазон значений от -2 до 2 с шагом 0,04 (всего 101 значение). Алгоритм построения графиков функций следующий:
выбираем в книге MS Excel свободный лист;
с помощью приема автозаполнение, описанного ранее в п. 2.3.2 в диапазон B1:CX1 вводим ряд целых чисел от –50 до 50 с шагом 1;
в ячейку B2 вводим выражение ..=B1/25 в ячейку B2;
копируем формулу в ячейке B2 и производим вставку этой формулы диапазон B2:CX2, теперь нами заполнен весь диапазон изменения переменной X;
в ячейки A2, A3 и A4 вводим соответственно X, Y=X*X и Y=X*X*X, эти данные нужны для пояснения, а также далее они будут подписями на графике;
в ячейки B3 и B4 вводим соответственно формулы квадрата и куба, то есть ..=СТЕПЕНЬ(B2;2) и =СТЕПЕНЬ(B2;2);
далее действуем аналогично тому, как описано ранее (разделы 2.3.1 и 2.3.2), то есть, выделяем диапазон B2:CX2, вызываем пункт меню «Данные», «Таблица подстановки», в диалоговом окне в верхнем поле указываем B2, нажимаем OK.
Нами получены значения массивов функций Y1=X2 и Y2=X3 в текстовом виде. Осталось изобразить их на графике, что является стандартной процедурой MS Excel. Делается это так:
выделяем диапазон A2:CX2;
вызываем меню «Вставка», пункт «Диаграмма»;
выбираем тип диаграммы точечная (четвертая в списке);
в дальнейшем можно жать на ОК (выбор исходных данных диаграммы и ее типа сделан);
последним шагом выбираем расположение диаграммы – на отдельном листе.
Полученную диаграмму можно отформатировать согласно общим правилам форматирования диаграмм в MS Excel [1,2]. Этот этап включает выбор единиц шкал X и Y, мест расположения делений шкал (около осей, внизу или вверху), форматирование линий сетки, выбор места расположения подписей (Y=X*X и Y=X*X*X), и т.д. Согласно требованиям задания для зависимости Y2=X3 нами выбрана дополнительная ось Y. Для этого:
на диаграмме выделяем ряд Y=X*X*X;
вызываем меню «Формат», пункт «Формат ряда данных»;
во вкладке «Оси» выделяем значение, соответствующее появлению дополнительной оси.
Окончательный вид графиков функций Y1=X2 и Y2=X3 от одной переменной X построенных по таблице подстановки с помощью инструментальных средств пакета MS Excel приведен на рис. 2.1.
Рисунок 2.1. Графики функций Y1=X2 и Y2=X3 (обозначения на графике синей и розовой линиями, соответственно). Ось X – значения переменной X (аргумента обеих функций), –2 до 2 с шагом 0,04. Ось Y – значения функций Y1=X2 и Y2=X3 (разметка делений первой слева, второй – справа).
2.4.2. Остальные задания к лабораторной работе №6
Построение таблицы умножения целых чисел в диапазоне от 1 до 10 было представлено ранее в п. 2.3.2.
Следующее задание предусматривает построение таблицы подстановки для выдаваемой на руки суммы и суммы удерживаемого подоходным налогом в течение года в зависимости от месячного заработка (без вычета подоходного налога). Предполагается, что месячный доход одинаков в каждом месяце.
Решение этой задаче происходит в полном соответствии с приведенной ранее в п. 2.4.1 схемой. Роль независимой переменной X теперь играет месячный доход в рублях без вычета подоходного налога. Эту величину мы будем изменять от 1 000 до 50 000 рублей с шагом 1 000 рублей. Обозначим эту переменную как M. Вместо функций Y1=X2 и Y2=X3 (как в п. 2.4.1.), мы будем иметь удерживаемую и выдаваемую суммы, U(M) и V(M), соответственно. Удерживаемая подоходным налогом сумма за год U(M) определяется условием . Сумма V(M), выдаваемая на руки за год, рассчитывается как разность между полным заработком без учета подоходного налога и удержанной суммой. Таким образом, выдаваемая на руки сумма за год задается выражением . В таблице подстановки мы задаем денежный формат представления данных, как описано ранее в п. 2.1.3. Остальные действия полностью соответствуют описанному ранее в п. 2.4.1 алгоритму построения таблицы подстановки и диаграммы по построенных исходным данным двух зависимостей.
Для упрощения создания диаграммы в данном случае можно использовать сделанное ранее для подготовки предыдущей задачи форматирование (графики функций Y1=X2 и Y2=X3, рис. 2.1). Для этого необходимо осуществить следующие действия:
скопировать лист имеющейся диаграммы (зависимостей Y1=X2 и Y2=X3), эта операция производится с помощью контекстного меню названия листа, способ см. в п. 2.2.1;
далее нужно поменять исходные данные для нового (скопированного) листа диаграммы со старых (данные X, зависимости Y1=X2 и Y2=X3) на новые (данные M, зависимости N(M) и U(M));
для совершения этого действия надо перейти на лист старой диаграммы (данные X, зависимости Y1=X2 и Y2=X3) и вызвать пункт «Исходные данные» из меню «Диаграмма».
В дальнейшем необходимо немного откорректировать форматирование осей и некоторых других деталей оформления диаграммы с помощью стандартных приемов MS Excel [1,2].
Построенные зависимости удерживаемой суммы годового подоходного налога и выдаваемой за год на руки суммы от величины месячного дохода, если он одинаков в каждом месяце, приведены на рис. 2.2.
Рисунок 2.2. Графики зависимостей удерживаемой подоходным налогом за год суммы U(M) и выдаваемой за год на руки суммы V(M) от величины месячного дохода M, если он одинаков в каждом месяце (обозначения на графике синей и розовой линиями, соответственно). Ось X – M, месячный доход до вычета подоходного налога (от 1 000 до 50 000 рублей с шагом 1 000 рублей). Ось Y – удерживаемая подоходным налогом за год сумма U(M) – синий цвет, сумма V(M), выдаваемая за год на руки – розовый цвет.
2.5. ЗАДАНИЕ №3
2.5.1. Задания по лабораторным работам №4 и №6
Первое задание предусматривает выполнение с помощью приобретенных в ходе выполнения лабораторной работы №4 навыков разделения сумма, равной 139 000 рублей поровну на 6 человек. Ранее в п. 2.2.2 именно эти данные (общее количество работников – 6, общая сумма заплаты – 139 000 рублей) были использованы в качестве иллюстрации произведения расчетов для произвольно заданных параметров таблицы расчета зарплат с помощью КТУ (при равных КТУ).
Для выполнения второго задания необходимо с помощью таблиц подстановки построить график функции X5π. Выполнение этого задания происходит в полном соответствии со схемой применения одномерных таблиц подстановки (раздел 2.3.1.). Так как 5π  5*3,14159  15,7, то X5π  X15,7 является очень сильной степенной функцией, то есть ее значения большие нуля (X > 0), но существенно меньшие единицы (X << 1) – очень малы, значение при X = 1 равно 1, а при значениях больших единицы (X > 0) функция стремительно растет. Поэтому нами выбран интервал изменения переменной X от 0,3 до 3 с шагом 0,02 (всего 131 значение).
В приведенном в п. 2.3.1 примере использования таблицы подстановки для одномерного массива нами использовалась в качестве функции зависимость 5*X, теперь необходимо ввести функцию X5π , пусть значение X находится в ячейке B2, тогда для ввода значения исследуемой функции в ячейку B3 надо ввести выражение ..=СТЕПЕНЬ(B2;5*ПИ()). Это выражение является степенной функцией, причем основанием является значение из ячейки B2, а степенью – выражение 5π. Функция ПИ() не имеет аргументов, она возвращает число π  3,14159 с достаточной точностью.
В остальном алгоритм построения таблицы подстановки и последующего графика полностью соответствуют описанному ранее в п. 2.3.1 описанию.
Диаграммы также строится по стандартной схеме. Единственным изменением является учет быстрого роста рассматриваемой функции даже для достаточно небольшого выбранного интервала от 0,3 до 3. Мы используем в данном случае логарифмический масштаб оси Y. Делается это так:
на диаграмме выделяется ось Y;
выбирается пункт «Формат оси» из меню «Формат»;
в появляющемся диалоговом окне выбирается вкладка «Шкала», и в ее полях «минимум» и «максимум» выставляются значения 0.000000001 и 100000000 (проще эти значения набрать в научном формате как 1E-9 и 1E8, соответственно);