Задание 5.
Познакомьтесь с логическими функциями пакета Excel.
5.1. Воспользуйтесь мастером функции, нажав на кнопку
5.2. В диалоговом окне мастера функций выберите функцию
Логические.
5.3. Посмотрите, какие логические функции и их имена используются в русской версии Excel.
Логические функции
В Excel имеются логические функции, список которых можно увидеть, нажав на кнопку и выбрав в диалоговом окне функцию Логические.
При решении ряда задач значение ячейки необходимо вычислять одним из нескольких способов в зависимости от того, выполняется или нет некоторое условие или несколько условий. Так, в нашей задаче в зависимости от введенного года в табл. 2 должен обрабатываться тот или иной столбец табл.1. Для решения таких задач применяют логическую функцию ЕСЛИ.
Формат функции: ЕСЛИ (<логическое выражение>; <выражение1>;<выражение2>)
Первый аргумент функции ЕСЛИ - логическое выражение (в частном случае условное выражение), которое принимает одно из двух значений: "Истина" или "Ложь". В первом случае функция ЕСЛИ принимает значение выражения 1, а во втором случае - значение выражения 2.
Пример.
В ячейке H5 нужно записать максимальное из двух чисел, содержащихся в ячейках Н2 и Н5.
Формула, введенная в ячейку Н5:
= ЕСЛИ (Н2>Н5; Н2; Н5) означает, что если значение ячейки Н2 больше значения ячейки Н5, то в ячейке Н5 будет записано значение из Н2, в противном случае - из Н5.
В качестве выражения 1 или выражения 2 можно записать вложенную функцию ЕСЛИ. Число вложенных ЕСЛИ не должно превышать семи. На месте логического выражения можно использовать одну из логических функций И или ИЛИ.
Формат функций:
И (<логическое выражение 1>;<логическое выражение 2>.....)
ИЛИ (<логическое выражение 1>;<логическое выражение 2>.....)
В скобках может быть указано до пятидесяти логических выражений. Функция И принимает значение "Истина", если одновременно все логические выражения истинны. Функция ИЛИ принимает значение "Истина", если хотя бы одно из логических выражений истинно.
Пример.
Определить, входит ли в заданный диапазон (5; 10) число, содержащееся в ячейке Н10. Ответ 1(если число принадлежит диапазону) и 0 (если число не принадлежит диапазону) должен быть получен в ячейке H12.
В ячейку Н12 вводится формула:
=ЕСЛИ (И (Н10>5; Н10<10); 1; 0)
В ячейке H12 получится значение 1, если число принадлежит диапазону, и значение 0, если число вне диапазона.
Задание 6.
Заполните формулами табл. 3 для обработки одномерных массивов (данные по каждому году).
6.1. Ячейку G11 отведите для ввода года и присвойте ей имя Год
(команда Формулы, Присвоить имя)
Именованная ячейка будет адресоваться абсолютно. При вводе в формулу имени ячейки необходимо выбрать это имя в списке и щелкнуть на нем. Excel вставит указанное имя в формулу.
6.2. В ячейку G12 с использованием Мастера функций введите формулу:
=ЕСЛИ (год=1992; МАКС (В5:В16); ЕСЛИ (год=1993; МАКС (С5:С16); ЕСЛИ (год=1994; МАКС (D5:D16);" данные отсутствуют")))
Проанализируйте формулу. Несмотря на сложный синтаксис, смысл ее очевиден. В зависимости от года, который вводится в именованную ячейку год, определяется максимум в том или ином диапазоне табл. 1. Диапазон В5:В16 - это одномерный массив данных за 1992 г.; С5:С16 - массив данных за 1993 г.; D5:D16 – за 1995 г.
6.3. Замените в формуле в ячейке G11 относительную адресацию ячеек на абсолютную.
Для выполнения следующих выборок эту формулу можно скопировать в ячейки G13:G16 и отредактировать, заменив функцию МАКС на требуемые по смыслу.
Но прежде необходимо заменить относительную адресацию ячеек на абсолютную, иначе копирование формулы будет производиться неправильно.
Можно легко изменить тип адресации, если в формуле установить курсор на адрес изменяемой ячейки и нажать клавишу [F5].
=ЕСЛИ (год=1992; МАКС ($В$5:$В$16); ЕСЛИ (год=1993; МАКС ($С$5:$С$16); ЕСЛИ (год=1995; MAKC ($D$5:$D$16); "данные отсутствуют"))).
Внимание! Все массивы в формуле адресованы абсолютно, ячейка ввода года также адресована абсолютно.
6.4. Скопируйте формулу из ячейки G12 в ячейки G13:G16.
6.5. Отредактируйте формулы в ячейках G13:G16, заменив функцию МАКС на требуемые по смыслу.
6.6. Отредактируйте формулу в ячейке G16. Смените функцию МАКС на функцию СЧЕТ ЕСЛИ и добавьте критерий " <10 ".
После редакции функция должна иметь вид:
=ЕСЛИ (год=1992; СЧЕТ ЕСЛИ ($В$5:$В$16;"<10"); ЕСЛИ (год=1993; СЧЕТ ЕСЛИ ($С$5:$С$ 16;"< 10"); ЕСЛИ (год=1995;CЧET ECЛИ ($D$5:$D$ 16;"<10");"данные отсутствуют")))
6.7. Введите в ячейку G11 год: 1992.
6.10. Проверьте правильность заполнения табл. 3 значениями (рис. 5.3).
ЗАДАНИЕ 7.
Сохраните результаты работы под тем же именем work_5.xls в личном каталоге.
ЗАДАНИЕ 8.
Представьте данные табл. 1 графически, расположив диаграмму на отдельном рабочем листе.
8.1. Выделите блок A5:D16 и выполните команду меню Вставка, Диаграмма на новом листе.
8.2. Выберите тип диаграммы и элементы оформления по своему усмотрению.
ЗАДАНИЕ 9.
Вернитесь к рабочему листу с таблицами.
ЗАДАНИЕ 10.
Сохраните результаты работы под тем же именем work5.xls в личном каталоге.
ЗАДАНИЕ 12(дополнительное).
Определите количество месяцев в каждом году с количеством осадков о пределах (>20;<80) мм и в пределах (< 10; >100) мм.
13.1. Создайте вспомогательную таблицу (рис.5.4) для определения месяцев с количеством осадков в пределах (>20;<80) мм.
Рис. 5.4
13.2. В ячейку В21 занесите формулу:
=ЕСЛИ (И (В5>20; В5<80);1;0)
13.3. Заполните этой формулой ячейки В22:В32. В ячейках, где условие выполняется, появляется 1.
13.4. В ячейке ВЗЗ подсчитайте сумму месяцев за 1992 г., удовлетворяющих этому условию.
13.5. Выделите ячейки В21:ВЗЗ и скопируйте формулы в область C21:D33.
В ячейках СЗЗ и D33 получилось количество месяцев за 1993 и 1995 гг., удовлетворяющих условию (>20; <80).
13.6. Аналогично создайте вспомогательную таблицу для определения числа месяцев с количеством осадков в пределах (<10; >100)
(формулу необходимо изменить в соответствии с условием).
13.7. В ячейку G17 занесите формулу:
=ЕСЛИ (год=1992;ВЗЗ; ЕСЛИ год=1993;СЗЗ; ЕСЛИ (год=1995;D33; "данные отсутствуют"')).
13.8. Скопируйте эту формулу в ячейку G18 и отредактируйте.
13.9. Оформите на свой вкус вспомогательные таблицы и добавьте к ним заголовки и обозначения.