Формат функций

Формат функции

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

Формат функции

СЧЕТЕСЛИ (интервал; критерии).

Воспользуйтесь мастером функций, на 2 шаге укажите интер­вал B5:D16 и критерий <10.

ЗАДАНИЕ 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 (если число не принадлежит диапазону) должен быть получен в ячейке Н12.

В ячейку Н12 вводится формула:

- ЕСЛИ (И (Н10>5; Н10<10); 1; 0)

В ячейке H12 получится значение 1, если число принадле­жит диапазону, и значение 0, если число вне диапазона.

ЗАДАНИЕ 6. Заполните формулами таблицу для обработки одно­мерных массивов (данные по каждому году).

6.1. Ячейку G11 отведите для ввода года и присвойте ей имя год

(команда Вставка –Имя - Присвоить)

Именованная ячейка будет адресоваться абсолютно. При вводе в формулу имени ячейки необходимо выбрать это имя в списке и щелкнуть на нем. Excel вставит указанное имя в фор­мулу.

6.2. В ячейку G12 с использованием Мастера функций вве­дите формулу:

=ЕСЛИ(год=1992;МАКС(B5:B16);ЕСЛИ(год=1993;МАКС(C5:C16);ЕСЛИ(год=1994;МАКС(D5:D16);"данные отсутствуют")))

Проанализируйте формулу. Несмотря на сложный синтаксис, смысл ее очевиден. В зависимости от года, который вводится в именованную ячейку год, определяется максимум в том или ином диапазоне таблицы 1. Диапазон В5:В16 - это одномерный массив данных за 1992 г.; С5:С16 - массив данных за 1993г; D5:D16 - зa 1995 г.

6.3. Замените в формуле в ячейке G11 относительную адре­сацию ячеек на абсолютную.

Для выполнения следующих выборок эту формулу можно ско­пировать в ячейки G13:G16 и отредактировать, заменив функцию МАКС на требуемые по смыслу.

Но прежде необходимо заменить относительную адреса­цию ячеек на абсолютную, иначе копирование формулы будет производиться неправильно.

=ЕСЛИ(год=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;СЧЕТЕСЛИ($D$5:$D$16;"<10"); "данные отсутствуют")))

6.7. Введите в ячейку G11 год: 1992.

6.8. Проверьте правильность заполнения таблицы 3 значениями

ЗАДАНИЕ 7. Сохраните результаты работы под тем же именем work_5.xls в личном каталоге.

ЗАДАНИЕ 8. Представьте данные таблицы 1 графически, располо­жив диаграмму на отдельном рабочем листе.

8.1. Выделите блок A5:D16 и выполните команду меню Встав­ка – Диаграмма; после чего вырежьте диаграмму и вставьте на другой лист.

8.2. Выберите тип диаграммы и элементы оформления по сво­ему усмотрению.

8.3. Распечатайте диаграмму, указав в верхнем колонтитуле фамилию, а в нижнем — дату и время.

ЗАДАНИЕ 9. Вернитесь к рабочему листу с таблицами.

ЗАДАНИЕ 10. Подготовьте таблицу к печати, воспользовавшись предварительным просмотром печати.

10.1. Выберите альбомную ориентацию и подберите ширину полей так, чтобы все 3 таблица умещались на странице.

10.2. Уберите сетку.

10.3. Укажите в верхнем колонтитуле фамилию, а в нижнем — дату и время.

ЗАДАНИЕ 11. Сохраните результаты роботы под тем же именин work5.xls в личном каталоге.

ЗАДАНИЕ 12. Распечатайте результаты работы на принтере.

ЗАДАНИЕ 13 (дополнительное).

Определите количество меся­цев в каждом году с количеством осадков в пределах (>20; <80) мм и в пределах (< 10; >100) мм.

13.1. Создайте вспомогательную таблицу для оп­ределения месяцев с количеством осадков в пределах

(>20;<80)

13.2. В ячейку В:21 занесите формулу: =ЕСЛИ(И(В5>20;В5<80)1;0).

13.3. Заполните этой формулой ячейки В22:В32. В ячейках, где условие выполняется, появляется 1.

13.4. В ячейке ВЗЗ подсчитайте сумму месяцев за 1992 г., удовлетворяющих этому условию.

13.5. Выделите ячейки В21:ВЗЗ и скопируйте формулы в об­ласть С21:D33.

В ячейках СЗЗ и D33 получилось количество месяцев за 1993 и 1995 гг., удовлетворяющих условию (>20; <80).

13.6. Аналогично создайте вспомогательную таблицу для оп­ределения числа месяцев с количеством осадков в пределах (<10; >100)(формулу необходимо изменить в соответствии с условием).

13.7. В ячейку G17 занесите формулу:

=ЕСЛИ(год=1992;В33;ЕСЛИ (год=1993;С33;ЕСЛИ(год=1995;D33; «данные отсутствуют»))).

13.8. Скопируйте эту формулу в ячейки G18 и отредактируйте.

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

ЗАДАНИЕ14. Сохраните результат работы под тем же именем work5.xls в личном каталоге.

ЗАДАНИЕ 15. Подведите итоги.

Проверьте:

Знаете ли вы: Умеете ли вы:

	· Логические функции ЕСЛИ И ИЛИ
		· Использовать встроенные функции EXCEL для решения типовых задач обработки массивов.

Предъявите преподавателю:

• краткий конспект;
• файл work5.xls на экране и на рабочем диске в личном каталоге.
• распечатанную таблицу и диаграмму.

Дата добавления: 2014-12-25