Лекция 6. Примеры использования функций

Функция ЕСЛИ()

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

а)

б)

	Рис.6.1. Схемы структуры ЕСЛИ()

Пример 1.

Вычислить значение аргумента F, если известно, что

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

Значение_если_истина — это значение, которое возвращается, если лог_выражение равно ИСТИНА. В нашем задании – это значение, полученное в результате выполнения действий: В12*А5+234. Значение_если_истина может быть формулой или текстом.

Значение_если_ложь — это значение, которое возвращается, если лог_выражение равно ЛОЖЬ. В нашем задании – это значение, полученное в результате выполнения действий: (С12+67)*0,2. Значение_если_ложь может быть формулой или текстом. Если лог_выражение равно ЛОЖЬ, а значение_если_ложь пусто (то есть после значение_если_истина стоит точка с запятой с последующей закрывающей скобкой), то возвращается значение 0. Значение_если_ложь может быть формулой или текстом.

Построение функции ЕСЛИ() с помощью мастера

1. Активизировать ячейку, в которой должен быть получен результат.

2. Выполнить Вставка\Функции, выбрать в категории Логические функцию ЕСЛИ() и нажать ОК. Появится окно Аргументы функции (рис. 7.2).

Рис. 6.2. Диалоговое окно «Аргументы функции» для функции ЕСЛИ()

3. Установить курсор в аргумент Логическое выражение и указать: A1>100.

4. Установить курсор в аргумент Значение_если_истина и указать: В12*А5+234.

5. Установить курсор в аргумент Значение_если_ложь и указать: (С12+67)*0,2.

6. Нажать ОК.

Следует учитывать, что до 7 функций ЕСЛИ() могут быть вложены друг в друга в качестве значений аргументов значение_если_истина и значение_если_ложь для конструирования более сложных проверок (см. схему, б)). Такая вложенность используется в лабораторной. работе 5 при выполнении пункта 6. Когда значения аргументов значение_если_истина и значение_если_ложь вычислены, функция ЕСЛИ() возвращает полученное значение. Если один из аргументов функции ЕСЛИ является массивом, при выполнении функции ЕСЛИ вычисляются все элементы массива.

Пример 2. Вычислить значение аргумента F, если известно, что

1 Активизировать ячейку, в которой должен быть получен результат.

2 Выполнить Вставка\Функции, выбрать в категории Логические функцию ЕСЛИ() и нажать ОК.

3 В окне Аргументы функции установить курсор в аргумент Логическое выражение. Поскольку логическое выражение содержит два условия одновременно (т.е. К3 должно находиться в пределах от 10 до 200 не включительно), следует встроить в него логическую функцию И(). Для этого нужно щелкнуть по кнопке со стрелкой, находящейся слева от строки формул и открыть перечень встраиваемых функций. Выбрать И() и нажать ОК. Если функции И() в данном перечне нет, следует выбрать Другие функции и найти нужную функцию в полном перечне.

4 В окне Аргументы функции И() установить курсор в аргумент Логическое значение 1 и ввести К3>10. Установить курсор в Логическое выражение 2 и ввести K3<200. Если условий будет больше, то следует продолжить их ввод. Нажать ОК. Появится окно с сообщением об ошибке, которое следует закрыть.

5 Для того, чтобы продолжить формирование функции ЕСЛИ() (вернуться в функцию ЕСЛИ()), нужно двойным щелчком выделить слово ЕСЛИ в строке формул и нажать кнопку f_x слева от строки формул. Появится окно Аргументы функции ЕСЛИ, в котором будет отражена встроенная функция И() (рис.7.3).

Рис. 6.3. Пример заполнения логического выражения функции ЕСЛИ()

6 Установить курсор в аргумент Значение_если_истина. Ввести (D2/A8)*. Дальше нужно встроить функцию КОРЕНЬ. Для этого выполнить щелчок по кнопке со стрелкой слева от строки формул, открыть перечень встраиваемых функций и найти функцию КОРЕНЬ.

7 В окне Аргументы функции КОРЕНЬ() установить курсор в аргумент Число и ввести С11 (щелкнуть мышью по ячейке с этим адресом). Нажать ОК.

8 Продолжить формирование функции ЕСЛИ(), т.е. опять двойным щелчком выделить слово ЕСЛИ в строке формул и нажать кнопку f_x слева от строки формул. Появится окно Аргументы функции ЕСЛИ, в котором будут заполнены Логическое выражение и Значение_если_истина (рис. 6.4).

Рис. 6.4. Заполнение аргументов функции (Логическое выражение и Значение если истина)

9 Установить курсор в аргумент Значение_если_ложь. Встроить вторую функцию ЕСЛИ(), для чего выполнить щелчок по кнопке со стрелкой слева от строки формул, открыть перечень встраиваемых функций и найти функцию ЕСЛИ. В открывшемся окне Аргументы функции ЕСЛИ-два в качестве Логического выражения будет условие 200<=K3<=250, представленное функцией И() (выполнить действия по аналогии с пунктом 4).

10 Для того, чтобы продолжить формирование функции ЕСЛИ()-два, нужно двойным щелчком выделить второе слово ЕСЛИ в строке формул и нажать кнопку f_x слева от строки формул. Появится окно Аргументы функции ЕСЛИ, в котором будет отражена встроенная функция И().

11 Установить курсор в аргумент Значение_если_истина. Встроить функцию COS (щелкнуть по кнопке со стрелкой слева от строки формул, открыть перечень встраиваемых функций и найти функцию COS). В открывшемся окне Аргументы функции COS в аргумент Число ввести Е10. Нажать ОК. Закрыть сообщение об ошибке.

12 Продолжить формирование функции ЕСЛИ()-два (двойным щелчком выделить второе слово ЕСЛИ в строке формул и нажать кнопку f_x слева от строки формул). Установить курсор в аргумент Значение_если_ложь. Ввести текст Значение не соответствует требуемому интервалу.

13 Правильно сформированная функция для данной задачи будет иметь вид в строке формул:

=ЕСЛИ(И(K3>10;K3<200);(D2/A8)*КОРЕНЬ(C11);ЕСЛИ(И(K3>=200;K3<=250);(COS(E10)+345)*0,2;"Значение не соответствует требуемому интервалу"))

Пример 3.

В диапазоне В2:В7 имеются значения (рис. 6.5). Определить количество элементов данного диапазона, которые больше или равны 50.

Рис. 6.5. Данные по задаче.

Порядок действий:

1. Активизировать ячейку В10.

2. Выполнить Вставка\Функции, выбрать в категории Статистические функцию СЧЕТЕСЛИ() и нажать ОК.

3. В окне Аргументы функции установить курсор в аргумент Диапазон ввести В2:В7 (или выделить диапазон мышью).

4. Установить курсор в аргумент Критерий и ввести >=50. Нажать ОК. В ячейке В10 будет получен результат 3.

Общий вид построенной функции в строке формул: =СЧЁТЕСЛИ(B2:B7;">=50")

Пример 4.

В диапазоне В2:В7 имеются значения (рис. 6.5). Если количество значений, которые больше или равны 50, больше трех, то вывести в ячейке В11 максимальное из элементов данного диапазона, иначе – минимальное из элементов диапазона.

Порядок действий:

1. Активизировать ячейку В11.

2. Выполнить Вставка\Функции, выбрать в категории Логические функцию ЕСЛИ() и нажать ОК.

3. В окне Аргументы функции установить курсор в аргумент Логическое выражение и встроить функцию СЧЕТЕСЛИ().

4. В окне Аргументы функции СЧЕТЕСЛИ() установить курсор в аргумент Диапазон и ввести В2:В7 (или выделить диапазон мышью). Установить курсор в аргумент Критерий и ввести >=50. Нажать ОК. Закрыть сообщение об ошибке.

5. Продолжить формирование функции ЕСЛИ() (двойным щелчком выделить слово ЕСЛИ в строке формул и нажать кнопку f_x слева от строки формул). В окне Аргументы функции установить курсор в аргумент Логическое выражение, после встроенной функции СЧЕТЕСЛИ и ввести >3.

6. Затем установить курсор в аргумент Значение_если_истина. Встроить функцию МАКС (щелкнуть по кнопке со стрелкой слева от строки формул, открыть перечень встраиваемых функций и найти функцию МАКС). В открывшемся окне Аргументы функции МАКС в аргумент Число1 ввести диапазон В2:В7. Нажать ОК.

7. Вернуться в функцию ЕСЛИ(). У становить курсор в аргумент Значение_если_ложь. Встроить функцию МИН (щелкнуть по кнопке со стрелкой слева от строки формул, открыть перечень встраиваемых функций и найти функцию МИН). В открывшемся окне Аргументы функции МИН в аргумент Число1 ввести диапазон В2:В7. Нажать ОК.Общий вид построенной функции в строке формул:

ЕСЛИ(СЧЁТЕСЛИ(B2:B7;">=50")>3;МАКС(B2:B7);МИН(B2:B7)).

ЛЕКЦИЯ 7. ИСПОЛЬЗОВАНИЕ ВСТРОЕННЫХ ФУНКЦИЙ ЕСЛИ()

Задача «Награждение участников конкурсных работ «Лучший по специальности»

Записать данные по задаче «Награждение участников конкурсных работ «Лучший по специальности» (рис. 8.1).

Задание 1. Рассчитать сумму выдаваемой премии (диапазон D9:D14) с учетом специализации участников конкурса, полученного балла и выделенной денежной суммы (F3 и G3).

Порядок действий:

1. Активизировать ячейку D9 и выполнить Вставка\Функция (или кнопка f_x слева от строки формул).
2. Выбрать функцию ЕСЛИ(). В аргумент Логическое выражение введем В9=”магистр”, что означает «Ячейка В9 содержит значение магистр?».
3. Установить курсор в аргумент Значение_если_истина. Если специализация студента «магистр», то сумма получаемой премии будет рассчитываться с учетом полученного балла (С9) и размера выделенной суммы для магистров (F3). Следовательно, необходимо проанализировать содержимое ячейки С9, учитывая тот факт, что информация в данной ведомости изменяется (т.е. изменяются фамилии участников, их специализации и полученные баллы).

Рис. 7.1. Данные по задаче «Награждение участников конкурсных работ «Лучший по специальности»

1. Встроим вторую функцию ЕСЛИ() (ЕСЛИ-два). В аргумент Логическое выражение введем С9<4. В аргумент Значение_если_истина введем Не награждается. В аргумент Значение_если_ложь встроим третью функцию ЕСЛИ (ЕСЛИ-три).
2. В аргумент Логическое выражение функции ЕСЛИ-три встроим функцию И(), которая в Логическом значении 1 будет иметь С9>=4, а в Логическом значении 2 будет иметь С9<=6. Вернемся в функцию ЕСЛИ-три.. В аргумент Значение_если_истина введем 0,1*$F$3 ( т.е. 10% от выделенной суммы для магистров ). В аргумент Значение_если_ложь встроим четвертую функцию ЕСЛИ (ЕСЛИ-четыре).
3. В аргумент Логическое выражение функции ЕСЛИ-четыре встроим функцию И(), которая в Логическом значении 1 будет иметь С9>=7, а в Логическом значении 2 будет иметь С9<=9. Вернемся в функцию ЕСЛИ-четыре.. В аргумент Значение_если_истина введем 0,4*$F$3 ( т.е. 40% от выделенной суммы для магистров ). В аргумент Значение_если_ложь внесем $F$3 (т.е. полную сумму награждения магистров).
4. Теперь следует рассмотреть ситуацию, когда награждается премией специалист. Для этого нужно вернуться в первое ЕСЛИ. Если функция формировалась правильно, то аргумент Значение_если_ложь должен быть пуст. Установить курсор в этот аргумент и встроить ЕСЛИ-пять.
5. В аргумент Логическое выражение функции ЕСЛИ-пять введем С9<4. В аргумент Значение_если_истина введем Не награждается. В аргумент Значение_если_ложь встроим функцию ЕСЛИ-шесть.
6. В аргумент Логическое выражение функции ЕСЛИ-шесть встроим функцию И(), которая в Логическом значении 1 будет иметь С9>=4, а в Логическом значении 2 будет иметь С9<=6. Вернемся в функцию ЕСЛИ-шесть. В аргумент Значение_если_истина введем 0,1*$G$3 ( т.е. 10% от выделенной суммы для специалистов ). В аргумент Значение_если_ложь встроим функцию ЕСЛИ-семь.
7. В аргумент Логическое выражение функции ЕСЛИ-семь встроим функцию И(), которая в Логическом значении 1 будет иметь С9>=7, а в Логическом значении 2 будет иметь С9<=9. Вернемся в функцию ЕСЛИ-семь. В аргумент Значение_если_истина введем 0,4*$G$3 ( т.е. 40% от выделенной суммы для специалистов ). В аргумент Значение_если_ложь внесем $G$3 (т.е. полную сумму награждения специалистов).
8. Полученную в ячейке D9 формулу расчета скопировать на остальные ячейки диапазона.

Правильно составленная формула имеет вид:

=ЕСЛИ(B9="магистр"; ЕСЛИ(C9<4;"не награждается"; ЕСЛИ(И(C9>=4;C9<=6);0,1*$F$3; ЕСЛИ(И(C9>=7;C9<=9);0,4*$F$3;$F$3))); ЕСЛИ(C9<4;"не награждается"; ЕСЛИ(И(C9>=4;C9<=6);0,1*$G$3; ЕСЛИ(И(C9>=7;C9<=9);0,4*$G$3;$G$3))))

Задание для самоконтроля

На основе таблиц задания 1 определить уровень подготовки специалистов и магистров, на основе результатов конкурсных работ (диапазон С9:С14), а именно:

– если суммарный балл по всем участникам меньше или равен 40 – удовлетворительный уровень подготовки;

– если суммарный балл по всем участникам находится в интервале от 41 до 60 – высокий уровень подготовки;

– если суммарный балл больше 60 – очень высокий уровень подготовки.

Описать порядок действий и представить построенную функцию.

Правильно составленная формула имеет вид:

=ЕСЛИ(СУММ(C9:C14)<=40;"удовлетворительный уровень подготовки"; ЕСЛИ(И(СУММ(C9:C14)>=41;СУММ(C9:C14)<=60);"высокий уровень подготовки"; "очень высокий уровень подготовки"))

Дата добавления: 2014-01-06