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

Логические функции позволяют производить логический выбор и логическое вычисление.

Функция	Действие
И (логическое условие 1, логическое условие 2….)	Возвращает значение ИСТИНА, если все аргументы истинны
ИЛИ (логическое условие 1, логическое условие 2….)	Возвращает значение ИСТИНА, если истинным является хотя бы один из аргументов
ЕСЛИ (лог_выражение; значение_если_истина; значение_если_ложь)	Возвращает одно значение, если указанное условие дает в результате значение ИСТИНА, и другое значение, если условие дает в результате значение ЛОЖЬ

Функции И/ ИЛИ могут проверять несколько критериев или условий для последующего их использования в функции ЕСЛИ. Функция ЕСЛИ используется для проверки условий и принятия решений.

Функция И.

Функция И возвращает значение ИСТИНА, если все логические аргументы имеют значение истина. Функция объединяет условия проверки, вычисление которых дает значение ИСТИНА или ЛОЖЬ.

Логическую функцию И удобно использовать, если необходимо в большой таблице найти объекты (сотрудники, автомобили и т.д.), которые удовлетворяют всем условиям. Например, если необходимо из нескольких тысяч сотрудников определить лиц, соответствующих следующим условиям: высшее образование, опыт работы по профилю не менее пяти лет, стаж работы в данной организации не менее двух лет, знание английского и немецкого языков, владение компьютером и т.д. Минимально может быть одно условие, а максимально - не более 255. Значение ИСТИНА будет в том случае, если все эти условия выполняются. Если не выполняется хотя бы одно условие, то выдаётся значение ЛОЖЬ.

Синтаксис: И(логическое_значение1, [логическое_значение2], ...)

Логическое_значение1. Первое проверяемое условие, вычисление которого дает значение ИСТИНА или ЛОЖЬ.

Логическое_значение2. Второе проверяемое условие, вычисление которого дает значение Истина или ЛОЖЬ.

Задание № 26. Для выполнения определенного задания необходимо выявить менеджеров первого отдела.Результат разместить в диапазоне I3-I11.

Первый способ.

• Курсор установите на ячейку, в которую требуется вставить функцию. В данном случае курсор установите на ячейку I3.

• В строке формул ввести =И(C3="менеджер";A3=1)

• Нажмите клавишу <Enter>

• С помощью автозаполнения заполните диапазон I4:I11.

Второй способ.

Согласно условиям задачи – сотрудник должен быть менеджером и работать в первом отделе. Предварительно в ячейкуI2 необходимо ввести с клавиатуры вопрос «Менеджер первого отдела?».

Далее:

• Курсор установите на ячейку, в которую требуется вставить функцию. В данном случае курсор установите на ячейку I3.

• Нажмите пиктограмму (Вставить функцию)

• В поле «Категория» выберете группу «Логические».

• В поле «Выберите функцию» выберите «И»

• Нажмите кнопку «ОК».

В появившемся окне «Аргументы функции»

• В поле «Логическое_значение1» введите С3=«менеджер»

• В поле «Логическое_значение2» введите A3=1

• Нажмите кнопку «ОК».

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

В приведенном примере в ячейке C3 содержится слово директор, а ячейка A3 вообще пуста, но, тем не менее, в данной функции необходимо указывать адреса ячеек, которые содержатся в первой строчке таблицы.

• С помощью автозаполнения заполните ячейки I4-I11.

Результат выполнения: I4,I8,I10 - ИСТИНА. I3, I5, I6, I7, I9, I11 – ЛОЖЬ.

Задание № 27. Руководством принято решение повысить оклад для слесарей, имеющих оклад менее 14000. Необходимо выявить есть ли таковые?Результат разместить в диапазоне J3-J11.

Результат выполнения:

I11 - ИСТИНА.

I3, I4,I5, I6, I7, I8, I9, I10 – ЛОЖЬ.

Функция ИЛИ.

Функция ИЛИ возвращает значение ИСТИНА, если хотя бы один из логических аргументов имеет значение истина и возвращает значение ЛОЖЬ, когда все логические аргументы имеют значение ЛОЖЬ.

Минимально может быть одно условие, а максимально - не более 255.

Синтаксис: ИЛИ(логическое_значение1;логическое_значение2;...)

Логическое_значение1, логическое_значение2,... — от 1 до 255 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.

Задание № 28. Руководством принято решение выплатить премию сотрудникам первого отдела и слесарям. Необходимо выявить есть ли таковые? Результат разместить в диапазоне K3-K11.

Необходимо в ячейку K2 ввести «Сотрудники первого отдела и слесари».

Первый способ.

• Курсор установите на ячейку, в которую требуется вставить функцию. В данном случае курсор установите на ячейку K3.

• В строке формул ввести =ИЛИ(A3=1;C3="Слесарь")

• Нажмите клавишу <Enter>

• С помощью автозаполнения заполните диапазон K4:K11.

Второй способ.

• Курсор установите на ячейку, в которую требуется вставить функцию. В данном случае курсор установите на ячейку K3.

• Нажмите пиктограмму (Вставить функцию)

• В поле «Категория» выберете группу «Логические».

• В поле «Выберите функцию» выберите «ИЛИ»

• Нажмите кнопку «ОК».

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

В появившемся окне «Аргументы функции»

• В поле «Логическое_значение1» введите A3=1

• В поле «Логическое_значение2» введите С3=«слесарь»

• Нажмите кнопку «ОК».

• С помощью автозаполнения заполните ячейки K4-I11.

Результат выполнения:

K4, K7, K8, K10, K11 - ИСТИНА.

K3, K5, K6, K9 – ЛОЖЬ.

Задание № 29. Руководством принято решение выплатить отправить на обучение менеджеров и бухгалтеров. Необходимо выявить есть ли таковые? Результат разместить в диапазоне L3-L11

Результат выполнения:

K4, K5, K6, K8, K9, K10 - ИСТИНА.

K3, K7, K11– ЛОЖЬ.

Функция ЕСЛИ.

Функция ЕСЛИ возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.

Функция ЕСЛИ используется при проверке условий для значений и формул.

Синтаксис: ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)

Лог_выражение — любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, >4000 — логическое выражение; если значение в ячейке B2 ,больше 4000, это выражение принимает значение ИСТИНА, а в противном случае — значение ЛОЖЬ. Этот аргумент может использоваться в любом операторе сравнения.

Значение_если_истина — значение, которое возвращается, если аргумент «лог_выражение» имеет значение ИСТИНА. Если аргумент «лог_выражение» имеет значение ИСТИНА вводится значение, например F2-E2. Аргумент «значение_если_истина» может быть текстом, числом, датой, формулой.

Значение_если_ложь — значение, которое возвращается, если «лог_выражение» имеет значение ЛОЖЬ.Если аргумент «Значение_ если ложь» вводится выражение , если логическое выражение ложно, например F2. может быть формулой. Аргумент «значение_если_ложь» может быть текстом, числом, датой, формулой. Аргумент «значение_если_ложь» можно использовать вложенную функцию если. В MS Excel 2010 можно использовать до 64 вложенных функций ЕСЛИ.

Задание № 29. Определить стимулирующую надбавку сотрудникам первого отдела в размере 700 рублей на человека, а остальным сотрудникам – 0. Результат разместить в диапазоне M3-M11.

Сначала в ячейкуK2 необходимо ввести «Надбавка сотрудникам первого отдела».

Первый способ:

• Курсор установите на ячейку, в которую требуется вставить функцию. В данном случае курсор установите на ячейку M3.

• В строке формул ввести =ЕСЛИ(A3=1;700;0)

• Нажмите клавишу <Enter>

• С помощью автозаполнения заполните диапазон M4:M11.

Второй способ:

• Курсор установите на ячейку, в которую требуется вставить функцию. В данном случае курсор установите на ячейку M3.

• Нажмите пиктограмму (Вставить функцию)

• В поле «Категория» выберете группу «Логические».

• В поле «Выберите функцию» выберите функцию «ЕСЛИ»

• Нажмите кнопку «ОК».

В появившемся окне «Аргументы функции»

• В поле «Лог_выражение» введите A3=1

• В поле «Значение_если_ истина» введите 700

• В поле «Значение_если_ ложь» введите 0

• Нажмите кнопку «ОК».

• С помощью автозаполнения заполните ячейки M4-M11.

Результат выполнения:

М4, M8, M10 – 700.

M3, M5, M6, M7, M9 M11 – 0.

Задание № 30. Определить размер командировочных слесарям в размере 500 рублей на человека, а остальным сотрудникам - 0. Результат разместить в диапазоне N3-N11.

Результат выполнения:

N7, N11– 500

N3, N4, N5, N6, N8, N9, N10–0.

Использование функций И/ИЛИ в функции ЕСЛИ.

Рассмотрим использование вложенных функций. В функции ЕСЛИ используется только одно логическое выражение. Если же использовать нужно несколько логических выражений, то необходимо в функцию ЕСЛИ вставить функцию ИЛИ. В этом случае функция ЕСЛИ является внешней, и перед ней ставится знак =(равно), а функция ИЛИ – внутренней, знак равно опускается.

Рассмотрим на конкретном примере.

Задание № 31. Определить размер командировочных менеджерам и слесарям в размере 600 рублей на человека, а остальным сотрудникам - 0. Результат разместить в диапазоне O3-O11.

Первый способ:

В ячейкуO2 ввести «Командировочные менеджерам и слесарям».

Далее:

• Курсор установите на ячейку, в которую требуется вставить функцию. В данном случае курсор установите на ячейку O3.

• В строке формул ввести =ЕСЛИ(ИЛИ(C3="менеджер";C3="слесарь");600;0)

• Нажмите клавишу <Enter>

• С помощью автозаполнения заполните диапазон O4:O11.

Второй способ:

В ячейкуO2 ввести «Командировочные менеджерам и слесарям».

Далее:

• Курсор установите на ячейку, в которую требуется вставить функцию. В данном случае курсор установите на ячейку O3.

• Нажмите пиктограмму (Вставить функцию)

• В поле «Категория» выберете группу «Логические».

• В поле «Выберите функцию» выберите функцию «ЕСЛИ»

• Нажмите кнопку «ОК».

В появившемся окне «Аргументы функции»

• В поле «Лог_выражение» введите ИЛИ(C3= «менеджер»;С3= «слесарь»).

• В поле «Значение_если_ истина» введите 600

• В поле «Значение_если_ ложь» введите 0

• Нажмите кнопку «ОК».

• С помощью автозаполнения заполните диапазон O4:O11.

Третий способ:

В ячейкуO2 ввести «Командировочные менеджерам и слесарям» .

Далее:

• Курсор установите на ячейку, в которую требуется вставить функцию. В данном случае курсор установите на ячейку O3.

• Нажмите пиктограмму (Вставить функцию)

• В поле «Категория» выберете группу «Логические».

• В поле «Выберите функцию» выберите функцию «ЕСЛИ»

• Нажмите кнопку «ОК».

В появившемся окне «Аргументы функции»

• В поле «Значение_если_ истина» введите 600

• В поле «Значение_если_ ложь» введите 0

Обратите внимание на то, что поле «Лог_выражение» не заполнено.

• Установите курсор на поле «Лог_выражение»

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

• Выберите функцию ИЛИ

• В появившемся окне «Аргументы функции»

• В поле «Логическое_значение1» введите C3= «менеджер»;

• В поле «Логическое_значение2» введите С3=«слесарь»

• Нажмите кнопку «ОК».

• С помощью автозаполнения заполните диапазон O4:O11.

Результат выполнения:

O4, O6 – 600.

O3, O5, O7 O8, O9, O10, O11– 0.

Задание № 32. Определить размер премии менеджерам и слесарям в размере 600 рублей на человека, а остальным сотрудникам - 1000. Результат разместить в диапазоне P3-P11.

Результат выполнения:

P4, P5, P7, P8, P9 P10, P11– 600.

P3, P6 –1000.

Многократное использование функции ЕСЛИ.

Задание № 33. Определить размер оклада менеджерам 20000, слесарям 15000, бухгалтерам 25000, директору 35000,а остальным сотрудникам 0. Результат разместить на Лист 2! в диапазоне D3-D11.

Выполняем:

• Скопируйте диапазон A2:H11 на Лист2! в такой же диапазон

• Очистите диапазон D3:D11

Первый способ:

• Курсор установите на ячейку, в которую требуется вставить функцию. В данном случае курсор установите на ячейку D3.

• В строке формул ввести =ЕСЛИ(C3="менеджер";20000;ЕСЛИ(C3="слесарь";15000;ЕСЛИ(C3="бухгалтер";25000;ЕСЛИ(C3="директор";35000;0))))

• Нажмите клавишу <Enter>

• С помощью автозаполнения заполните диапазон D4:D11.

Второй способ:

• Курсор установите на ячейку, в которую требуется вставить функцию. В данном случае курсор установите на ячейку D3.

• Нажмите пиктограмму (Вставить функцию)

• В поле «Категория» выберете группу «Логические».

• В поле «Выберите функцию» выберите функцию «ЕСЛИ»

• Нажмите кнопку «ОК».

В появившемся окне «Аргументы функции»

• В поле «Лог_выражение» введите C3="менеджер"

• В поле «Значение_если_ истина» введите 20000

• Поставьте курсор поле «Значение_если_ ложь»

Нажмите в строке формул на треугольник рядом с функцией ЕСЛИ

• Выберите функцию ЕСЛИ

• По аналогии с предыдущим заполните остальные окна.

Результат выполнения:

Задание № 34. Определить размер премии от оклада менеджерам 10%, слесарям 15%, бухгалтерам 18%, директору 20 %,а остальным сотрудникам 0. Результат разместить на Лист 2! в диапазоне E3-E11.

Результат выполнения:

Функции для работы с базами данных.

База_данных — диапазон ячеек, образующих список или базу данных. База данных представляет собой список связанных данных, в котором строки данных являются записями, а столбцы — полями. Верхняя строка списка содержит заголовки всех столбцов.

Поле — столбец, используемый функцией.

Условия — интервал ячеек, который содержит задаваемые условия. В качестве значения аргумента «условия» может использоваться любой интервал, содержащий по крайней мере один заголовок столбца и по крайней мере одну ячейку с условием, расположенную под заголовком столбца.

Создайте таблицу на Лист 1! как показано на рис.

Функция БСЧЁТ.

Функции БСЧЁТ считает числовые записи в поле БД, удовлетворяющие критерию.

Синтаксис: БСЧЁТ(база_данных, поле, условия)

Задание № 35. Подсчитать количество сотрудников, получивших премию в размере 3000. Результат разместить в диапазоне G39.

В ячейкуA39ввести «Подсчитать количество сотрудников, получивших премию в размере 3000».

Первый способ:

• Курсор установите на ячейку, в которую требуется вставить функцию. В данном случае курсор установите на ячейку G39.

• В строке формул введите =БСЧЁТ(A2:H11;E2;G37:G38)

• Нажмите клавишу <Enter>

Второй способ:

• Курсор установите на ячейку, в которую требуется вставить функцию. В данном случае курсор установите на ячейку G39.

• Нажмите пиктограмму (Вставить функцию)

• В поле «Категория» выберете группу «Работа с базой данных».

• В поле «Выберите функцию» выберите «БСЧЁТ»

• Нажмите кнопку «ОК».

В появившемся окне «Аргументы функции»

• В поле «База_данных» введите A2:H11

• В поле «Поле» введите E2

• В поле «Критерий» введите G37:G38

• Нажмите кнопку «ОК».

Результат выполнения: 4

Задание № 36. Подсчитать количество сотрудников работающих в первом отделе. Результат разместить в диапазоне С40.

Результат выполнения: 3

Функция БДСУММ.

Функция БДСУММ суммирует числа в поле (столбце) записей списка или базы данных, которые удовлетворяют заданному условию.

Синтаксис: БДСУММ (база_данных;поле;условие)

Задание № 37. Определить сумму премий менеджерам. Результат разместить в диапазоне С41.

В ячейкуA41ввести «Определить сумму премий менеджерам».

Первый способ.

• Курсор установите на ячейку, в которую требуется вставить функцию. В данном случае курсор установите на ячейку G41.

• В строке формул ввести =БДСУММ(A2:H11;E2;E37:E38)

• Нажмите клавишу <Enter>

Второй способ.

• Курсор установите на ячейку, в которую требуется вставить функцию. В данном случае курсор установите на ячейку G41.

• Нажмите пиктограмму (Вставить функцию)

• В поле «Категория» выберете группу «Работа с базой данных».

• В поле «Выберите функцию» выберите «БДСУММ»

• Нажмите кнопку «ОК».

В появившемся окне «Аргументы функции»

• В поле «База_данных» введите A2:H11

• В поле «Поле» введите E2

• В поле «Критерий» введите E37:E38

• Нажмите кнопку «ОК».

Результат выполнения:10000

Задание № 37. Определить сумму премий сотрудников первого отдела. Результат разместить в диапазоне G42.

Результат выполнения:7000

Функция ДСРЗНАЧ.

ДСРЗНАЧ усредняет значения в поле (столбце) записей списка или базы данных, удовлетворяющие заданным условиям.

Синтаксис: ДСРЗНАЧ (база_данных, поле, условия)

Задание № 38. Определить средний оклад менеджеров.Результат разместить в диапазоне F43.

В ячейкуA43ввести «Определить средний оклад менеджеров».

Первый способ.

• Курсор установите на ячейку, в которую требуется вставить функцию. В данном случае курсор установите на ячейку F43.

• В строке формул ввести =ДСРЗНАЧ(A2:H11;D2;E37:E38)

• Нажмите клавишу <Enter>

Второй способ.

• Курсор установите на ячейку, в которую требуется вставить функцию. В данном случае курсор установите на ячейку F43.

• Нажмите пиктограмму (Вставить функцию)

• В поле «Категория» выберете группу «Работа с базой данных».

• В поле «Выберите функцию» выберите «ДСРЗНАЧ»

• Нажмите кнопку «ОК».

В появившемся окне «Аргументы функции»

• В поле «База_данных» введите A2:H11

• В поле «Поле» введите D2

• В поле «Критерий» введите E37:E38

• Нажмите кнопку «ОК».

Результат выполнения: 20200

Задание № 39. Определить среднюю премию менеджеров.Результат разместить в диапазоне G44.

Результат выполнения: 2000