Встроенные логические функции

Функция ЕСЛИ проверяет, выполняется ли условие, и возвращает одно значение, если условие выполняется, и другое значение, если нет;

функция И проверяет, все ли аргументы имеют значение ИСТИНА, и возвращает значение ИСТИНА, если истинны все аргументы;

функция ИЛИ проверяет, какое значение имеют аргументы, и возвращает значение ЛОЖЬ только в том случае, если все аргументы имеют значение ЛОЖЬ;

Категория Ссылки и массивы

функция ПРОСМОТР(искомое_значение; массив)или ПРОСМОТР(искомое_значение; просматриваемый_вектор; вектор_результатов).

В случае массива ищет искомое значение в одной строке, одном столбце или столбце (строке) массива и возвращает значение из другого столбца (строки);

функция ВПР/ГПР (искомое_значение;таблица;номер_столбца/номер_строки;интервальный_просмотр) ищет значение в крайнем левом столбце /в верхней строке и возвращает значение ячейки, находящейся в указанном столбце той же строки/ указанной строке того же столбца(по умолчанию таблица должна быть отсортирована по возрастанию);

функция ТРАНСП(массив) преобразует вертикальный диапазон ячеек в горизонтальный, и наоборот (использованиефункции рассмотрено в примерах 4.4 –4.6).

Пример 3.1.Рассчитайте надбавку к окладу в зависимости от стажа работы. Для сотрудников, чей стаж работы меньше или равен 1 году, надбавка составляет 10% от оклада, если стаж работы от 1 до 3 лет, то надбавка составляет 30% от оклада, для остальных сотрудников – 50% от оклада.

Решение. Вернитесь к примеру 1.1. Вставьте столбец «Надбавка» перед последним столбцом.

В ячейке F2 вызовите встроенную функцию ЕСЛИ. В диалоговом окне введите следующие значения для логического выражения и значения, если выражение истинно (рис. 10).

Так как у нас еще два возможных варианта начисления надбавки в зависимости от стажа, то необходимо вызвать еще одно диалоговое окно, и сделать надо это из строки Значение_если_ложь. Установите курсор в эту строку и вызовите функцию ЕСЛИ из Поля адреса (рис. 1). Во втором диалоговом окне заполните строки следующим образом (рис. 11):

Рис. 11. Диалоговое окно вложенной функции ЕСЛИ

Формула в ячейке F2будет содержать вложенное ЕСЛИ:

=ЕСЛИ(E2<=1;B2*10%;ЕСЛИ(E2<=3;B2*30%;B2*50%))(рис. 11).

Скопируйте формулу в другие ячейки и пересчитайте таблицу с учетом начисленной надбавки (добавьте надбавку к полученной формуле в столбце «К выдаче»). Формула примет вид: =B2+F2-C2-D2. Результат выполнения задания приведен на рис. 12.

Рис. 12. Результат выполнения задания 3.1

Пример 3.2.Рассчитайте обычную стипендию в размере 1030 руб. и повышенную в размере 1800 руб. в зависимости от результатов сдачи сессии.

Таблица 11.

Решение. Стипендия выплачивается студенту в том случае, если у него нет оценок «3». Для расчета столбца "Признак выплаты стипендии" воспользуемся встроенной функциейИ(логич. выражение1, логич. выражение2, …). В качестве логических выражений будем сравнивать каждую оценку с 3-ой: =И(C3>3;D3>3;E3>3;F3>3). Далее рассчитайте самостоятельно столбец «Средний бал». Для расчета столбца «Стипендия» используйте встроенную функцию ЕСЛИ. В ячейке I3 формула будет иметь вид: =ЕСЛИ(G3=ЛОЖЬ;0;ЕСЛИ(H3=5;1800;1030)).

Результат выполнения задания приведен на рис. 13.

Рис. 13. Результат выполнения задания 3.2

Пример 3.3.Рассчитайте Комиссионное вознаграждение для сотрудников страховой компании по следующей схеме.

Решение. Вернемся к примеру 2.1. Добавьте справа столбец «Комиссионное вознаграждение».Справа от таблицы добавимтарификационную табличку для выбора процента вознаграждения.                                                           Таблица 12.

Присвоим этому диапазону ячеек имя «Тариф» (меню Формулы / Присвоить имя).

Для каждой фамилии рассчитаем данные в графе «Комиссионное вознаграждение» как произведение объема страховых сделок за III квартал и значения процента, вычисленного функцией ПРОСМОТР по таблице «Тариф».

Для функцииПРОСМОТРвыберем ПРОСМОТР(искомое_значение; массив), где в качестве искомого значения указываем значение за III квартал Иванова, а в качестве массива указываем имя«Тариф»: =C3*ПРОСМОТР(C3;Тариф).

Результат выполнения задания приведен на рисунке 14.

Рис. 14. Результат выполнения задания 3.3

Пример 3.4. Создайте Ведомость заработной платы для сотрудников организации.

Решение. Назовем три листа в книге Excel соответственно Отдел, Оклад, Зарплата. На листах Отдел и Оклад заполним данными ячейки согласно рис. 15.

Используя пункт меню Формулы / Присвоить имя / присвоим диапазону А2:А8 на листе Отдел имя Должности, диапазону А2:В8 на листе Отдел имя Должн_Отд, а диапазону А2:В8 на листе Оклад имя Должн_Окл.

Рис.15. Таблицы «Должности_Отделы», «Должности_Оклады»

Заполним листЗарплатасогласно рис. 16 и рассчитаем столбец Стаж работы, используя встроенную функцию СЕГОДНЯ(). Стаж рассчитаем в годах (для этого воспользуемся тем фактом, что в году 365 дней).

При заполнении столбца Должность на листе Зарплата значения будем выбиратьиз раскрывающегося списка. Для этого воспользуемся механизмом создания Списков: в окне меню Данные / Проверка / Параметры в качестве Типа данных выберемСписок, а в качестве Источника укажем=Должность, т.е. выполним установки согласно рисунку 17.

Рис. 16. Данные листа «Зарплата»

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

Для заполнения столбцов Отдел и Оклад воспользуемся встроенной функциейВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр).

Если интервальный_просмотр имеет значение ИСТИНА, то значения в первом столбце аргумента «таблица» должны быть расположены в возрастающем порядке,если содержит значение ЛОЖЬ, то «таблица» не обязана быть отсортированной.

Рис. 17. Диалоговое окно для создания Списков

В задании функция ВПР в ячейке D2 будет иметь следующий вид:

=ВПР(С9; Должн_Отд;2;ЛОЖЬ)

функция ВПР в ячейке F2 будет иметь следующий вид:

=ВПР(С9; Должн_Окл;2;ЛОЖЬ)

Скопируем формулы в другие ячейки столбца.

Столбец Надбавка рассчитаем в зависимости от стажа работы: если стаж работы меньше пяти лет, то надбавка не начисляется, если стаж работы от пяти до восьми лет, то % надбавки возьмем из ячейки Е1, если стаж работы больше восьми лет, то % надбавки возьмемиз ячейки Е2.

Столбец Премия рассчитаем для всех одинаково, процентом из ячейки Е3 от суммы Оклада и Надбавки.

Столбец Всего начисленорассчитаем как сумму столбцов Оклад, Надбавка и Премия.

РассчитаемОтчисления в пенсионный фонд. Процент отчисления возьмемиз ячейки Е4.

Налогооблагаемая база рассчитывается как разница между столбцами Всего начислено и Пенсионный фонд.

Рассчитаем столбец Налог. Процент налога возьмемиз ячейки Е5. Полученные значения округлим до 2-х знаков после запятой: =ОКРУГЛ(L8*$E$5;2).

Для расчета столбца Выплатить из Всего начислено вычтемНалог и Пенсионный фонд. Результат расчетов приведен на рисунке 18.Необходимо учесть, что значения в столбцах F («Стаж работы») и Н («Надбавка») будут отличаться от приведенных на рисунке, т.к. функцияСЕГОДНЯ() возвращает текущую дату на момент выполнения задания.

Рис. 18.Заполненная ведомость заработной платы

На отдельном листе построим столбчатую гистограмму по всем сотрудникам и их суммам к выплате.

Для возможности анализа данных включимАвтофильтр (предварительно выделим всю таблицу, включая шапку, и выберем пункт меню Данные / Фильтр).Отобразим на экране только сотрудников бухгалтерии.

Отсортируем таблицу по столбцу Отдел (Данные / Сортировка), предварительно выделив всю таблицу.Рассчитаем промежуточные итоги по налогу и суммам к выплате для каждого отдела (Данные / Итоги) (Рис. 19).

Рис. 19. Расчет промежуточных итогов