Применение программы MS Excel для решение практических задач

Применение программы MS Excel для решение практических задач

Чупракова Ирина Владимировна

Преподаватель информатики

НИК (филиала) ФГБОУ ВО «ЮГУ»

Современные технологии обработки информации часто приводят к тому, что возникает необходимость представления данных в виде таблиц.

Электронная таблица позволяет хранить в табличной форме большое количество исходных данных, результатов, а также связей (алгебраических или логических соотношений) между ними. При изменении исходных данных все результаты автоматически пересчитываются и заносятся в таблицу. Электронные таблицы не только автоматизируют расчеты, но и являются эффективным средством моделирования различных вариантов и ситуаций. Меняя значения исходных данных, можно следить за изменением получаемых результатов и из множества вариантов решения задачи выбрать наиболее приемлемый.

Электронная таблица – самая распространенная и мощная технология для профессиональной работы с данными, которая позволяет решать различный спектр прикладных задач.

Практическое применение электронных таблиц при решении прикладных задач можно представить следующей практической работой.

Итоговая практическая работа MS Excel

1.     На лист 1, создайте таблицу по образцу (таб.1), заполните ее данными

2.    

Расчет формул: сначала рассчитайте формулы только для одного сотрудника (Петровский А.Н.)

¾   Всего часов, выделите ячейку J4 и в строке формул нажмите кнопку ∑ отобразиться формула=Сумм(C4:I4) нажмите «Enter».

Для расчета количества выходных, больничных, отпуск, отпуск за свой счет необходимо воспользоваться кнопкой fx (мастер функций) в строке формул. В появившемся диалоговом окне выбрать категорию «Статистические» формула СЧЁТЕСЛИ (где нужно искать; что нужно найти), нажать кнопку далее, с правой стороны в поле диапазон щелкнуть по зеленой стрелочке и мышкой указать диапазон C4:I4, вернуться в мастер функций (щелкнуть по зеленой стрелочке еще раз) в поле критерии ввести символ для подсчета выходных дней «в». Нажать ОК.

Аналогично рассчитать:

¾   Больничные: = СЧЁТЕСЛИ (C4:I4; «б»);

¾   Отпуск: = СЧЁТЕСЛИ (C4:I4; «о»);

¾   Отпуск за свой счет: = СЧЁТЕСЛИ(C4:I4; «а»);

¾   Чтобы подсчитать количество отработанных дней в мастере функций выбрать категорию «Статистические» функцию СЧЕТ указать диапазон (C4:I4);

При расчете формул с определенными или отдельно стоящими ячейками необходимо использовать абсолютную ссылку на ячейку (зафиксировать ячейку нажать кнопку f4 с клавиатуры):

¾   Оклад: (всего часов*оклад): = Всего часов*оклад (руб.час) - (фиксируется ячейка $P$3-стоимость 1 часа 250 р);

¾   Премия: (оклад*премия): = Оклад*$Q$3 ($Q$3 – премия 40%);

¾   С.Н. –северная надбавка: = Оклад*С.Н.;

¾   Р.К. –районный коэффициент: = Оклад*Р.К.;

¾   Подоходный налог: =(Оклад+Премия+С.Н.+Р.К.)*подоходный налог;

¾   Аванс: =(Оклад+Премия+С.Н.+Р.К.)*Аванс;

¾   Удержано: =Подоходный налог+Аванс;

¾    Выдано: =(Оклад+Премия+С.Н.+Р.К)-Удержано.

3.     Для расчета формул остальным сотрудникам необходимо воспользоваться функцией автозаполнения, т.е. щелкнуть мышкой в ячейке  J4 (в которой посчитана формула Всего часов для одного сотрудника), взять за левый нижний угол ячейки (маркер автозаполнения) появиться черный крестик потянуть за него вниз (т.е. скопировать формулу) для остальных сотрудников. Остальные формулы рассчитать таким же способом.

Расчет Итого осуществляется кнопкой ∑ «Автосумма»  или следующей формулой:

СУММ= (диапазон данных);

Ваша оценка «3»

4.     Расчет статистических данных осуществляется через в мастер функций,  выбрать категорию статистические:

¾   Минимальное значение: =мин(диапазон данных);

¾   Максимальное значение: =мах(диапазон данных);

¾   Среднее значение: =срзнач(диапазон данных).

Измените ячейку Оклад с 250, на 195, Премия с 43% на 33%.

5.     Построение диаграммы, диаграмма — это графическое отображение табличных данных. Необходимо построить гистограмму, которая будет отражать динамику роста Окладов (значит нужно выделить столбец ФИО нажать кнопку Ctrl с клавиатуры и удерживая выделить столбец Оклад), выбрать меню «Вставить» команда «Диаграмма».

6.     Постройте точечный график, который будет отражать зарплату сотрудников (Выдано);

7.     Постройте график, который будет отражать Северную надбавку и Районный коэффициент сотрудников.

8.     Сортировка и фильтрация данных в таблице позволяет упорядочить данные и сделать некоторую выборку данных из массива по определенным критериям. Выделите всю таблицу начиная со столбца «ФИО» выбрать меню «Данные» команда «Сортировка», в диалоговом окне выберите «Сортировать в алфавитном порядке».

Ваша оценка «4».

Задание: Работа с несколькими листами

1.     Переименуете Лист 1 в Вахта январь 1-7 (щелкнуть правой кнопкой мыши по Лист 1 выбрать команду Переименовать), скопировать весь лист (щелкнуть правой кнопкой мыши по листу «Вахта январь» выбрать команду «Скопировать/переместить»  поставить галочку в поле «Создать копию» появится копия листа «Вахта январь 1-7»,  переименовать копию листа присвоить имя «Вахта январь 8-14», на текущем листе внести изменения: поменять название вахты, числа вахты, Оклад (руб.час -150), Премия – 15%. Аванс – 0%

2.     Скопировать лист «Вахта январь 8-14», переименовать в «Вахта январь 15-21», внести следующие изменения: поменять название вахты, числа вахты, Оклад (руб.час -1140), Премия – 20%,  Аванс – 0%

3.     Скопировать лист «Вахта январь 15-21», переименовать в «Вахта январь 22-28», внести следующие изменения: поменять название вахты, числа вахты, Оклад (руб.час -130), Премия – 15%, Аванс – 0%.

4.     Скопировать лист «Вахта январь 22-28», переименовать в «Вахта январь-итоговая», внести следующие изменения:  скрыть  все столбцы кроме: Табельный номер, ФИО, Оклад, Удержано  и Выдано. (Скрытие столбца осуществляется через контекстное меню, команда «Скрыть»).

Посчитать Оклад, Удержано  и Выдано за все вахты: для расчета ячейки Оклад поставить курсор в ячейку Р3, поставить знак равно, перейти на лист Вахта январь 1-7 выделить ячейку Р3+ перейти на лист Вахта январь 8-14 выделить ячейку Р3+перейти на лист Вахта январь 15-21 выделить ячейку Р3+ перейти на лист Вахта январь 22-28 выделить ячейку Р3 Enter.  Таким образом суммируются все ячейки скопируйте формулу для других сотрудников. Аналогично посчитайте столбцы «Удержано» и «Выдано».

5.     Постройте объемную гистограмму, которая будет отражать зарплату сотрудников за январь месяц.

Ваша оценка  «5».