Лаб. работа 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. Применение двумерной таблицы подстановки для построения графиков сложных функций в трехмерном пространстве

В дальнейшем необходимо немного откорректировать форматирование осей и некоторых других деталей оформления диаграммы с помощью стандартных приемов 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), выдаваемая за год на руки – розовый цвет.
ЗАДАНИЕ №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, соответственно);
в двух следующих полях отмечем основную и дополнительную единицу как 10 и 10 (логарифмический масштаб требует всегда кратных десяти чисел);
отмечаем пересечение с осью X в точке 1 и, наконец, отмечаем птичкой логарифмический масштаб оси и нажимаем OK.
Окончательный вид полученного графика приведен на рис. 2.3.
Рисунок 2.3. График функции X5π. Ось X – значения переменной X в диапазоне от 0,3 до 3 с шагом 0,02. Ось Y – значения функции X5π в логарифмическом масштабе.
2.5.2. Задание по построению алгоритма автоматического поиска элемента заданной таблице
Необходимо по данной таблице, представляющей телефоны 40 учеников, подготовить лист для нахождения номера телефона ученика, фамилия которого будет указываться в одной из ячеек. Согласно заданию, необходимо рассмотреть случай:
Фамилии перечислены в алфавитном порядке.
Принять, что в списке однофамильцев нет.
Для решения поставленной задачи представим исходные данные в виде таблицы в MS Excel, формат представления входных данных этого задания проиллюстрирован ниже в табл. 2.8.
Таблица 2.8.
Формат представления данных для выполнения задания №3: таблица представляющая телефоны 40 учеников (в таблице приведены не все строки, полное содержание таблицы представлено в MS Excel)
№№ п.п. Фамилия Номер телефона 1 Абрамов 45-75-54 2 Акулова 56-98-05 3 Бобров 45-69-58 4 Быстров 23-98-52 5 Вяткина 43-03-55 6 Гамова 86-50-52 7 Гуськов 58-30-56 8 Деев 95-90-48 9 Елкин 79-20-58 10 Жирков 51-28-50 11 Жукова 54-20-27 12 Зубов 86-31-53 13 Игнатьева 38-62-82 14 Илюшин 49-50-58 15 Каменев 91-93-67 16 Курилова 76-58-54 17 Лукьяненко 94-49-53 … … … 39 Юдина 90-41-85 40 Якин 71-62-62
Решение данной задачи производится путем применения встроенных в MS Excel функций работы с табличными данными. Доступ к функциям возможен с помощью пункта «Функция» меню «Вставка».
Предположим, что нам надо найти телефон ученика «Каменев» (15-й элемент в приведенной выше табл. 2.8). Алгоритм автоматического поиска телефона этого ученика будет происходить в два этапа:
Поиск номера строки в таблице, в которой во второй колонке находится заданная фамилия.
Извлечение по найденному номеру строки из третьего столбца соответствующего номера телефона.
Теперь рассмотрим детальную последовательность действий:
поместим полные данные таблицы в диапазон A1:C41;
поместим фамилию ученика для поиска телефона в отдельную ячейку, то есть в ячейке E5 расположим слово ..Каменев;
в ячейке F5 записываем функцию поиска номера строки в виде ..=ПОИСКПОЗ(E5; B2:B21; 0). Первым аргументом является искомый элемент списка «Каменев», вторым – массив значений, в котором происходит поиска этого элемента (множество всех фамилий). Третий аргумент определяет особенности обработки ситуаций, когда в списке отдельный элемент встречается несколько раз, но в нашем случае это не важно, так как по условию все элементы уникальны (однофамильцев в списке нет);
в ячейке G5 записываем функцию извлечения заданного элемента (номера телефона) из массива (все телефоны) по определенному на предыдущем этапе номеру строки, функция вызывается в виде ..=ИНДЕКС(C2:C21; F5).
Задача решена, в итоге в ячейке F5 стоит номер строки 15, в которой обнаружена запрашиваемая фамилия «Каменев», а в строке G5 стоит номер телефона 91-93-67, записанный под 15-м номером в общем списке телефонов.
Описание приложений
Работа включает два приложения:
Первое приложение (п. 3.1) представляет расчет зарплаты работников с помощью КТУ при большей численности трудового коллектива (чем принималось в ходе выполнения курсовой работы), различных КТУ и разной квалификации работников.
Второе приложение (п. 3.2) содержит построенный на основе двумерной таблицы подстановки график функции двух переменных z(x, y) = x2 – 2*y2. Эта функция имеет очень примечательный вид в трехмерном пространстве (x, y, z), она также известна как «седло».
Заключение
В представляемой курсовой работе достигнуты следующие основные результаты:
Проанализированы особенности расчеты зарплаты работников с помощью коэффициентов трудового участия (КТУ). Для расчетов этого типа рассмотрены несколько основных наиболее важных частных случаев: равные КТУ, различные КТУ, различная общая численность трудового коллектива. Полученные зависимости оформлены в виде таблиц MS Excel, а также таблиц MS Word.
Показаны способы применения таблиц подстановки для вычисления значений функций одной и двух переменных. Для нескольких конкретных примеров (линейные и степенные функции) описана точная последовательность действий пользователя для вычислений значений массивов. Полученные зависимости оформлены в виде графиков, представляющих собой диаграммы MS Excel.
В конце выполнения курсовой работы с помощью стандартных средств MS Excel решена задача поиска элемента в таблице MS Excel: для таблицы, представляющей фамилии и телефоны 40 учеников реализован алгоритм автоматического поиска телефона по заданной фамилии.
В приложении к курсовой работе содержатся иллюстрация применения приобретенных в ходе выполнения всех заданий навыков эффективных вычислений с помощью MS Excel.
Выводом из проделанной работы является овладения необходимыми навыками ускоренных вычислений с помощью средств MS Excel, возможным направлением дальнейших исследований является всестороннее изучение по мере необходимости других основных особенности пакета программ MS Excel.
Список использованных источников
Мотов В.В. Word, Excel, PowerPoint - просто, кратко, быстро. Издательство: Инфра-М, 2007 г. 206 стр.
Мак-Федрис П. Формулы и функции в Microsoft Office Excel 2003. Издательство: Вильямс, 2006 г. 576 стр.
Приложения
Расчет зарплаты работников при разных квалификациях работников и разных КТУ и большей численности коллектива
Мы произведем расчет зарплаты работников с помощью КТУ при более общих условиях, чем принималось в ходе выполнения курсовой работы (табл. 3.1).
Таблица 3.1
Расчет зарплаты работников при разных КТУ, разной квалификации работников α и увеличенной численности трудового коллектива
Общая начисленная сумма: 300 000,00р. № п.п. ФИО Α D КТУ Доля Заработная плата 1 Бобров Л.П. 8 22 0,80 0,023608 7 082,26р. 2 Быстров А.Д. 8 22 0,90 0,026558 7 967,54р. 3 Васильев В.В. 8 22 0,95 0,028034 8 410,18р. 4 Водкин А.Б. 8 22 1,00 0,029509 8 852,82р. 5 Вяткина Е.В. 8 22 1,05 0,030985 9 295,46р. 6 Гамова П.П. 8 22 1,10 0,032460 9 738,10р. 7 Дементьев Д.Д. 10 22 1,15 0,042420 12 725,93р. 8 Жирков В.Ю. 10 22 1,20 0,044264 13 279,23р. 9 Жукова А.А. 10 22 0,80 0,029509 8 852,82р. 10 Зубов Ф.П. 10 22 0,90 0,033198 9 959,42р. 11 Иванов И. И. 10 22 0,95 0,035042 10 512,73р. 12 Игнатьева Е.П. 10 22 1,00 0,036887 11 066,03р. 13 Илюшин А.Л. 12 22 1,05 0,046477 13 943,19р. 14 Осин К.Г. 12 22 1,10 0,048691 14 607,16р. 15 Пенкина В.А. 12 22 1,15 0,050904 15 271,12р. 16 Петров П. П. 12 22 1,20 0,053117 15 935,08р. 17 Рудин В.В. 12 22 0,80 0,035411 10 623,39р. 18 Рыков А.Г. 12 22 0,90 0,039838 11 951,31р. 19 Северов. А.Н. 14 22 0,95 0,049059 14 717,82р. 20 Селезнева К.А. 14 22 1,00 0,051641 15 492,44р. 21 Серов П.Д. 14 22 1,05 0,054224 16 267,06р. 22 Сидоров С.С. 14 22 1,10 0,056806 17 041,68р. 23 Сухов Р.О. 14 22 1,15 0,059388 17 816,30р. 24 Федоров Ф.Ф. 14 22 1,20 0,061970 18 590,93р. Итого 5964,2 1 300 000,00р.
Для создания этой таблицы мы использовали следующие данные:
увеличили численности трудового коллектива до 24 человек;
учли разную квалификацию работников α (меняем ее от 8 до 14 с шагом 2);
учли, как и ранее разные КТУ (меняем их от 0,8 до 1,2 с шагом 0,2);
увеличили общий фонд зарплаты до 300 000 рублей.
Применение двумерной таблицы подстановки для построения графика сложной функции в трехмерном пространстве
Нами рассмотрена известная в математике функция «седло», общий вид которой z(x, y) = a*x2 – b*y2 (a > 0, b > 0). Действуем по следующей схеме:
Выбираем диапазоны изменения обеих независимых переменных (x, y) от –12 до 12 с шагом 1 (всего 25 значений).
Двумерную таблицу подстановки строим по описанной ранее методике (п. 2.3.2), получаем таблицу подстановки в диапазоне A3: AA28.
Копируем диапазон переменной x (C3:AA3) в в 30-й ряд (C30:AA30), а диапазон переменной y (B4:B28) в колонку B (B31:B55) под вычисленной таблицей подстановки (под A3:AA28). В ячейке C31 пишем ..=С4, затем копируем содержимое ячейки С31 на весь диапазон C31:AA55.
Диапазон B30:AA55 и используем для построения диаграммы, используем тип диаграммы – «Поверхность». Она представляет собой трехмерную поверхность.

Рисунок 3.1 График функции двух переменных z(x, y) = x2 – 2*y2. Ось X – переменная x, ось Y – переменная y, ось Z – значение функции z(x, y).
14
1
13