2015-05-10
3201
«Операции с условием в MS Excel»
Цель работы: Освоить работу с условными операторами в Excel. Научиться использовать имеющиеся функции с условием для конкретных задач.
Задание:
1. Открыть Excel и созданный ранее документ. Создать новый лист и назвать его if(x).
2. Вычислить значение заданной функции одной переменной f1 с условием.
3. Вычислить количество точек функции, попадающих в заданный интервал.
4. Вычислить значения заданной функции одной переменной f2.
5. Вычислить сумму тех значений функции, аргументы которых лежат в заданном интервале.
6. Вычислить значение функции двух переменных.
7. Вычислить максимальное и минимальное значение функции.
8. Вычислить количество положительных и сумму отрицательных элементов функции.
9. Посчитать произведение тех значений функции, которые меньше 2.
10. Сохранить документ.
Пример выполнения задания:
Задание:
1. 
,
где х меняется от –2 до 2 с шагом 0,2. Интервал: [–0,2; 0,2].
2. 
,
где х меняется от –4 до 4 с шагом 0,5. Интервал [–1; 1]
|
где х меняется от –4 до 4 с шагом 0,5, а y от –2 до 2 с шагом 0,25.
1. Откроем документ (\ Файл \ Открыть...) и создадим новый лист (\ Вставка \ Лист). Переименуем лист при помощи контекстного меню в if(x).
2. Рассмотрим алгоритм вычисления данной функции.
Для этого построим блок-схему:
Для записи таких выражений в Excel есть встроенные логические функции. В данном случае нам необходима функция ЕСЛИ(;;), ее окно ввода выглядит следующим образом:
В первое поле ввода «Лог_выражение» вводится условие, записанное в нашей блок-схеме в ромбе. Во второе поле ввода «Значение_если_истина» вводится, то что присваивается в случае истинности условия. В третье поле ввода записывается оставшееся выражение, которое присваивается в случае невыполнения условия.
Данную функцию можно записать и без вызова ее окна, для этого записываем:
=ЕСЛИ(условие;выр_истина;выр_ложь)
где в первую позицию записывается условие, как и в первое поле ввода, во вторую позицию записывается выражение, предназначенное для второго поля ввода, и, наконец,
в третью позицию записываем оставшееся выражение.
Рассмотрим первый способ использования данной функции на примере 1.
В ячейках А1 и В1 запишем заголовок таблицы: «х» и «f1(x)» соответственно. В ячейку А2 введем –2, в А3 –1,8, теперь выделим ячейки А2 и А3 и растянем до ячейки А22. Теперь установим курсор ячейки на В2 и на строке формул нажмем кнопку 
, появится окно:
Развернем поле выбора «Категория» и выберем «Логические», получим:
В поле выбора «Выберите функцию:» выберем «ЕСЛИ» и нажмем «ОК». Получим окно функции «ЕСЛИ». Заполним первое поле этого окна:
Обращаем ваше внимание на то, что вместо х набрано А2, так как ячейке В2, содержащей значения функции, соответствует значение аргумента из ячейки А2. Далее, аналогично заполняя поля «Значение_если_истина» и «Значение_если_ложь», получим:
Теперь, нажав «ОК», мы получим следующий результат:
Обратите внимание, как в строке формул заполнилась сама функция ЕСЛИ().
Осталось только растянуть формулу до ячейки В22 и получить окончательный результат.
3. Нам осталось вычислить количество значений функции, попадающих в заданный интервал.
Для этого предназначена функция СЧЁТЕСЛИ().
В ячейке D2 произведем вычисления, для чего установим курсор ячейки на D2 и нажмем кнопку , выберем категорию «Статистические» и найдем функцию СЧЁТ-ЕСЛИ(), нажмем «ОК» и получим окно вида:
В поле «Диапазон» установим курсор и произведем выделение ячеек с В2 до В22, а в поле «Критерий» запишем «>=-0,2», получим:
Теперь, нажав «ОК», мы получим количество значений функции, которые больше или равны –0,2, так как данная функция Excel позволяет находить количество не в интервале, а на заданной полуоси. Но нам необходимо найти количество значений функции, попадающих в заданный интервал. Как известно, вычисление количества в любом интервале можно представить как разность количеств на двух полуосях. Например, если нам необходимо вычислить количество элементов, попадающих в интервал [a,b], представим его разностью между количеством на полуоси «>=a» и количеством с полуоси «>b», в результате мы получим количество элементов, попадающих в заданный интервал. Применим это к нашей задаче. В ячейке D2 мы уже вычислили количество элементов «>=-0,2», теперь в ячейку D3 вычислим количество элементов «>0,2» и в ячейку D4 запишем разность:
=D2-D3
Получим результат:
4. Теперь наберем более сложную функцию, но прежде нарисуем ее блок-схему:
Обратите внимание, что на одной оси условного оператора вложен еще один условный оператор. Теперь реализуем эту схему.
В ячейки F1 и G1 наберем заголовок для таблицы: x и f2(x) соответственно. В ячейки F2 – F18 занесем значения аргумента. В ячейку G2 запишем:
=ЕСЛИ(F2<=-2;F2*F2+1;ЕСЛИ(F2>=2;F2-3;1))
Теперь, нажав кнопку 
на строке формул, получим:
Остается только растянуть формулу до ячейки G18.
5. Вычислим сумму тех значений функции, аргументы которых лежат в заданном интервале. Для этого есть функция СУММЕСЛИ().
Нам необходимо вычислить сумму только тех элементов, аргументы которых лежат в заданном интервале. Для этого установим курсор ячейки на пустую ячейку I2 и нажмем кнопку ,в математических функций найдем нужную нам и нажмем «ОК». Получаем окно следующего вида:
В первое поле ввода – «Диапазон» – вводятся адреса тех ячеек, по которым будет происходить проверка условия, во второе поле – «Критерий» – вводится условие, накладываемое на заданный «Диапазон». В третье поле – «Диапазон_суммирования» – вводятся адреса ячеек, данные в которых и будут суммироваться (заметим, что они зависят от ячеек, заданных в поле «Диапазон»). Напомним, что критерий суммирования ограничивает полупрямую, а не отрезок, поэтому поступим так же, как и в случае с вычислением количества. Для нашего интервала [-1; 1], сначала вычислим сумму с критерием «>=-1», а затем «>1»,
а искомый результат получим вычитая из первой суммы вторую.
Для первой суммы получим формулу:
Обратите внимание, как при этом выглядит запись в строке формул.
Теперь, нажав кнопку «ОК», получим результат. Аналогично вычислим вторую сумму:
Теперь для ячейки I4 запишем формулу:
=I2-I3
нажмем Enter и получим окончательный результат.
6. Вычислим значения функции двух переменных, так же, как это делалось в лабораторной работе №1.
Для этого вставим новый лист и назовем его if(x,y). Ячейки А2-А18 заполним значениями х. Ячейки B1-R1 заполним значениями y. Теперь установим курсор в ячейку В2 и запишем туда формулу:
ЕСЛИ(A2*A2+B1*B1<=9;A2*A2+B1*B1+1;A2*A2+B1*B1-1)
Но растягивать такую формулу пока нельзя, так как будет происходить смещение ссылок на значения для x и y. Закрепим для значений x имя столбца – А, а для значений y номер строки – 1. Получим формулу вида:
ЕСЛИ($A2*$A2+B$1*B$1<=9;$A2*$A2+B$1*B$1+1;$A2*$A2+B$1*B$1-1)
Ее можно растянуть до R1,получим:
7. Вычислим максимальное и минимальное значения этой функции.
Для этого установим курсор ячейки на В20 и в «Статистических» функциях выберем МАКС(), а в первое поле ввода запишем: «B2:R18». Аналогично в ячейку В21 вычислим минимум при помощи МИН(). Получим:
8. Теперь вычислим количество положительных и сумму отрицательных элементов функции.
Для этого установим курсор ячейки на С20 и в строку формул запишем:
=СЧЁТЕСЛИ(B2:R18;”>0”)
а в ячейку С21:
=СУММЕСЛИ(B2:R18;”<0”)
Как видим, в случае, когда суммируются и проверяются на критерий одни и те же данные, писать их дважды (до и после «критерия») не надо.
Получаем:
9. Теперь вычислим произведение тех значений функции, которые меньше 2.
Для этого установим курсор в ячейку Е21 и в строке формул запишем:
=ПРОИЗВЕД(ЕСЛИ(B2:R18<2;B2:R18))
нажмем Ctrl+Shift+Enter, получим результат:
Обратите внимание на то, как выглядит запись в строке формул: в результате нажатия вышеуказанной комбинации клавиш Excel заключил всю формулу в фигурные скобки.
