Задача №6. Оплата труда сотрудников отдела

Лабораторная работа 4

Функции Excel. Логические функции

Цели работы:

1. Изучить структуру логических функций.

2. Научиться применять их в своих документах.

Краткие теоретические сведения

Главным инструментом пользователя при работе с электронными таблицами являются функции. По назначению их можно разбить на несколько групп: арифметические функции; функции округления; степенные; тригонометрические; логические; финансовые; сводные; функции выбора и поиска; матричные функции и т.д. (всего более 150 функций). Функция может быть частью сложной формулы или представлять собой формулу.

Правила ввода функции:

Ввод функцииначинается с набора имени функции русскими буквами или ее выбора из списка поля имени,или с помощью меню Вставка/Функция… .
За именем в круглых скобках набираются аргументы функций согласно синтаксису конкретной функции. В некоторых функциях количество аргументов непостоянно. Необязательные аргументы в написании синтаксиса функции указаны в квадратных скобках.
Ввод адресов и ключевых слов, используемых в функциях, можно осуществлять как заглавными, так и строчными английскими буквами с клавиатуры или с помощью мыши (щелчок на ячейке, значение которой используется в формуле).
Иногда формулы могут иметь очень длинный, необозримый вид. В этих случаях удобно разбивать их на отдельные строки внутри ячейки (как текст), используя клавиши. Левый Alt + Enter.

Логические функции позволяют строить формулы, результаты выполнения которых зависят от некоторых условий. Эти условия задаются операциями отношений ( =; <; >; <=; >=; <>) между двумя операндами (слагаемыми, сомножителями, уменьшаемым и вычитаемым и т.д.). Результатом выполнения условия является значение «истина» или «ложь», которые определяют выбор способа вычисления результата функции.

Синтаксис функций «ЕСЛИ», «И», «ИЛИ».

1. ЕСЛИ (<условие>;<результат, если условие истинно>;[<результат, если условие ложно>])– функция проверяет логическое<условие>, и если оно истинно, возвращает первый результат, если ложно - второй.

Примеры: =ЕСЛИ(А3= «Аня»; «девушка»; «юноша»);

=ЕСЛИ(А1<100; «число менее 100»; «больше или равно 100»)

=ЕСЛИ(В7<=17,5; D15;D17).

Аргументом функции ЕСЛИ ( ) может быть другая функция (при формировании более сложных условий). Уровень вложенности функций не более семи.

2. И(<условие>;<условие>;<условие>;….) – возвращает значение ИСТИНА, если истины все аргументы. Функция называется функцией логического умножения.

3. ИЛИ(<условие>;<условие>;<условие>;….) – возвращает значение ИСТИНА, если истинен хотя бы один из аргументов. Функция называется функцией логического сложения.

Часто функции И(), ИЛИ() используются для задания сложного условия функции ЕСЛИ(). Они по смыслу соответствуют союзам в русском языке. Особенность имеет функция ИЛИ(): она используется в Excel только в объединяющем смысле этого слова.

Пример: =ЕСЛИ(и(А1>0;A1<5); «число в интервале (0;5)»; если(А1<0; «число отрицательное»; «число больше 5 или равно 0»))

Пример 1. В ячейки А1 и А2 введены числа. В ячейке С1 написать какое из чисел больше. Результат получить как:

· выражение текстового типа;

· выражение числового типа.

Решение.

1. Формула в ячейке С1, где результат текстового типа выглядит следующим образом:

Формула в ячейке С1, где результат числового типа выглядит следующим образом: =ЕСЛИ(А1>А2; А1; А2)

Пример 2. В ячейки А1, А2, А3 введены числа. В ячейке С1 написать ответ на вопрос: «Правда ли, что значение в ячейке А1 больше остальных?»

Решение.

1 способ без использования логической функции: И(.)

Формула в ячейке С1 выглядит следующим образом:

=ЕСЛИ(А1>А2;ЕСЛИ(А1>А3;«А1 самое большое»; «А1 не самое большое»); «А1 не самое большое»)

Здесь вторая функция ЕСЛИ() является вторым аргументом первой функции ЕСЛИ().

Аргументы для внешней функции«ЕСЛИ().

2 способ с использованием логической функции: И()

Формула в ячейке С1 выглядит следующим образом:

=ЕСЛИ(И(А1>А2;А1>А3);«А1 самое большое»;«А1 не самое большое»)

При записи сложных условий функция ЕСЛИ() может получиться громоздкой и ненаглядной. Чтобы упростить получение итогового выражения, можно использовать более понятный способ записи.

Пример 3. Записать функцию ЕСЛИ() для того, чтобы определить, что значение только одной из двух ячеек А1 и А2 больше 0.

Решение.

Сформулируем сначала функцию словами:

Если (А1>0 и А2<0) или (А1<0 и A2>0), то «Да», иначе «Нет».

Используется одна функция ИЛИ(), аргументами которой являются значения двух функций И(). Теперь перепишем это выражение по правилам, принятым в Excel:

ЕСЛИ (ИЛИ ( И(A1>0;A2<0) ; И(A1<0;A2>0) ) )

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

Исходные данные: фиксированный оклад продавцов; сумма, на которую «продано» товара; норма объема продаж; процент «премии», в зависимости от объема продаж.

Правила расчета:

Если «Продано» меньше «Нормы» или «Норма», то «Премия» = 0руб.

Если «Продано» больше «Нормы», то «Премия» = «Продано» *20%

Если «Продано» больше двух «Норм», то «Премия» = «Продано» *20%+1000руб.

ВНЕШНИЙ ВИД РАБОЧЕГО ЛИСТА

Рис.1

èПодготовить исходное состояние документа согласно рис.1.

èВ ячейку D4 введите формулу для расчета «Премии» для Петрова И.Н.

=ЕСЛИ(С4>2*B$1;C4*20%+1000;ЕСЛИ(С4>B$1;C4*20%;0))

или =ЕСЛИ(С4<=B$1;0; ЕСЛИ(С4>2*B$1; C4*20%+1000; C4*20%))

è Размножить формулу ячейки D4 на диапазон соответствующий списку фамилий, используя маркер заполнения, или через буфер обмена.

èВ ячейку Е4 ввести формулу для расчета Заработка для Петрова И.Н.: =В4+D4

è Размножить формулу ячейки Е4 на диапазон соответствующий списку фамилий.

èВ ячейках строки «Всего» получить суммы по столбцам: «Оклад», «Продано», «Премия», «Заработок», применив функцию автосуммирования.

èИзменить исходные данные в столбце «Продано» и проанализировать новые результаты.

Индивидуальные задания.

Составить и оформить документ Excel, состоящий из нескольких листов. На каждом листе оформить отдельную задачу.
Сохранить таблицу в созданной ранее папке под названием «Лаб4+Фамилия».

Задача №1.

Требуется построить таблицу выявления факта пригодности призывников (Годен/Не годен) к военной службе, например, к зачислению в летное училище.

Призывник годен, ЕСЛИ рост более 150см И менее 200см И вес более 55кг И менее 100кг.

ВНЕШНИЙ ВИД РАБОЧЕГО ЛИСТА

Правила оформления:

Столбец «Отбор» должен выделяться фоном, и синим курсивным шрифтом, если призывник «Годен» к службе.

Задача №2.

Требуется построить таблицу для начисления праздничной премии женщинам организации ко дню 8 Марта в размере 300 руб.(величина премии может меняться)

Правила расчета:

Если значение текущего месяца – ячейки В1–«март»,то женщинам назначается премия в размере 300руб.

Правила оформления:

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

ВНЕШНИЙ ВИД РАБОЧЕГО ЛИСТА

Задача №3.

Задание: Вычислить значения кусочно-ломаной функции Y(x) следующего вида:

в диапазоне изменения аргумента Х от -6 до+16 с шагом

ВНЕШНИЙ ВИД РАБОЧЕГО ЛИСТА

Методические указания к выполнению:

è Значения Х и Y находятся в строках 1 и 2 таблицы:

èВ ячейку В2 ввести формулу для расчета значения функции Y(x) в зависимости от значения аргумента функции Х – ячейки В1.

èРазмножить формулу ячейки B2на диапазон ячеек С2:М2.

Задача №4.

Требуется построить таблицу вычисления прибавки к вкладу по истечении года хранения.

Банковский процент зависит от величины вклада:

Если вклад 40 тыс.руб и более, то 20%, иначе

Если вклад 20 тыс.руб и более, то 12%, иначе

Если вклад 10 тыс.руб и более, то 10%, иначе – 0%.

ВНЕШНИЙ ВИД РАБОЧЕГО ЛИСТА

Задача 5. Турнирная таблица

Задание: Создать таблицу определения призеров по результатам чемпионата.

В играх участвуют три команды. Для каждой игры фиксируется ее счет – число забитых и пропущенных мячей (Колонки «з» и «п»). В колонке «о» вычисляется число очков, полученных по результатам игры. Проигрыш приносит 0 очков, ничья – 1, выигрыш – 2 очка. Если игры еще не было (данные о забитых и пропущенных мячах не введены) ячейка «о» остается пустой.

Порядок расчета:

По играм чемпионата формируются данные:

1.Очки по каждой игре каждой команды, которые вычисляются из следующих соображений:

еслив клетках забитых и пропущенных мячей пусто (функция ЕПУСТО()), то в клетке «о» команды тоже пусто;

иначе, если забитых мячей больше чем пропущенных, то 2 очка;

иначе, если забитых мячей равно количеству пропущенных, то 1 очко, иначе0. 2. Число сыгранных игр с помощью функции СЧЁТ вычисляется по ячейкам очков в строке команды.

3. Число всех добытых командой очков в чемпионате.

4. Число забитых и пропущенных мячей.

5. Разность забитых и пропущенных мячей.

6. Число очков команд с учетом соотношения забитых и пропущенных мячей. Расчет ведется так, чтобы определить места команд, имеющих равное количество очков. Для этого определяется разность забитых и пропущенных мячей. Чтобы учесть ее влияние, к сумме очков прибавляется некоторая дробная величина. Для этого к разности мячей прибавляется сначала 100 (чтобы избежать возможности уменьшения очков, если разность отрицательна), затем результат делится на 1000 (чтобы избежать изменения целой части результата) и все это прибавляется к сумме добытых командой очков, т.е. Sо + ((Sз-Sп)+100)/1000.

7. Занятое место в чемпионате (определяется функцией РАНГ() по данным столбца «Очки+Разность»)

Задача №6. Оплата труда сотрудников отдела

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

Исходные данные: фонд зарплаты отдела (фонд з/п) за неделю, предполагаемый оклад для каждого сотрудника (оклад), минимальный размер оплаты труда (МРОТ).

Правила расчета:

1. ПФ. Премиальный фонд равен нулю, если сумма окладов (оклад; всего) больше или равна фонду з/п, иначе ПФ равен разности фонд з/п и суммы окладов.

2. Фактическая зарплата. Предполагаемый оклад сотрудника выплачивается полностью, если суммы фонда з/п достаточно для выплаты всем сотрудникам. Если суммы недостаточно, то оклад каждого уменьшается пропорционально сумме фонда.

3. Премия. После начисления фактической зарплаты остаток фонда з/п считается премиальным фондом (ПФ), который распределяется в соответствии с правилами:

· малооплачиваемым (оклад менее 5 МРОТ) делается доплата до 5 МРОТ, если достаточно премиального фонда. Для определения этого выполняется расчет суммы максимальной доплаты (Распределение ПФ: максимальная доплата).

· Фактическая доплата (Распределение ПФ: факт. доплата) равна максимальной или уменьшенной пропорционально ПФ.

· остаток премиального фонда (после начисления фактических доплат) распределяется между всеми пропорционально окладам (Распределение ПФ: Премия).

· Итоговая премия вычисляется как сумма фактической доплаты и премии (Распределение ПФ: Итого премия)

Если премия равна 0, то доплата и премия не начисляются (=0)

4. Итого начислено. Вычисляется как сумма оклада и итоговой премии.

Замечание. Правильность расчета подтверждает тот факт, что значение «Итого начислено» равно фонду зарплаты отдела.

Примерный внешний вид рабочего лита приведен для 2-х случаев: фонда з/п недостаточно для выплаты выплат и фонда з/п достаточно для всех выплат. Случай1:

Случай 2:

Дата добавления: 2018-06-27; просмотров: 2508; Мы поможем в написании вашей работы!

Поделиться с друзьями:

Мы поможем в написании ваших работ!