Постановка задачи.

Имеется таблица, содержащая количество осадков в миллиметрах, построенная на основе наблюдений метеостанции г. Екатеринбурга (рисунок 6.1).

Рисунок 6.1

Определить для всей таблицы в целом:

1) минимальное количество осадков, выпавших за 3 года;

2) суммарное количество осадков, выпавших за 3 года;

3) среднемесячное количество осадков по итогам 3-летних наблюдений;

4) максимальное количество осадков, выпавших за 1 месяц, по итогам 3-летних наблюдений;

5) количество засушливых месяцев за все 3 года, в которые выпало меньше 10 мм осадков.

Данные оформить в виде отдельной таблицы 2 (рисунок 6.2).

Те же данные определить для каждого года и оформить в виде отдельной табли­цы 3 (рисунок 6.3).

Дополнительно для каждого года определить:

1) количество месяцев в году с количеством осадков в пределах от 20 до 80 мм;

2) количество месяцев с количеством осадков вне нормы, т.е. <10 или >100) мм (рисунок 6.3).

Рисунок 6.2

Рисунок 6.3

При вводе года в таблицу 3 (рисунок 6.3) должны отражаться данные именно за этот год, в случае некорректного ввода должно выдаваться сообщение "данные отсутству­ют".

Структура электронной таблицы позволяет использовать ее для решения задач, сходных с задачами обработки массивов. В качестве одномерных массивов можно рас­сматривать строки или столбцы электронной таблицы, заполненные однотипными чи­словыми или текстовыми данными. Аналогом двумерного массива является прямо­угольная область таблицы, также заполненная однотипными данными.

В нашей задаче область B5:D16исходной таблицы на рисунке 6.1 можно рассматри­вать как двумерный массив из 3 столбцов и 12 строк, а данные по каждому году В5:В16; С5:С16; D5:D16 как одномерные массивы по 12 элементов каждый.

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

Ход работы:

Задание 1. Заполните таблицу согласно рисунок 6.1 и оформите ее по своему усмот­рению,

Задание 2. Сохраните таблицу на диске в личном каталоге под именем work6.xls.

Задание 3. На том же листе создайте и оформите еще 2 таблицы, как показано на рисунках 6.2 и 6.3.

Задание 4. Заполните формулами ячейки G4:G8 таблицы 2, согласно рисунка 6.2, для обработки двумерного массива В5 : D16 (данные за 3 года).

Используя мастер функций , занесите формулы:

· в ячейку G4 =МАКС (B5:D16)

· в ячейку G5 =МИН (B5:D16) и так далее в соответствии с требуемой обработ­кой двумерного массива B5:D16.

· определите количество засушливых месяцев за 3 года.

Для определения воспользуйтесь функцией СЧЕТЕСЛИ, которая подсчитывает количество непустых ячеек, удовлетворяющих заданному критерию внутри интервала.

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

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

Воспользуйтесь мастером функций на 1-м шаге в категории статистических функций найдите функцию СЧЕТЕСЛИ, на 2 шаге укажите интервал B5:D16 и крите­рий <10.

Задание 5. Познакомьтесь с логическими функциями пакета Excel.

· Воспользуйтесь мастером функций.

· В диалоговом окне мастера функций выберите функцию Логические.

· Посмотрите, какие логические функции и их имена используются в русской версии Excel.

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

При решении ряда задач значение ячейки необходимо вычислять одним из не­скольких способов в зависимости от того, выполняется или нет некоторое условие или несколько условий. Так, в нашей задаче в зависимости от введенного года в таблице 2 (рисунок 6.2) должен обрабатываться тот или иной столбец таблицы 1 (рисунок 6.1). Для реше­ния таких задач применяют логическую функцию ЕСЛИ.

ФОРМАТ ФУНКЦИИ

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

Первый аргумент функции ЕСЛИ - логическое выражение (в частном случае - ус­ловное выражение), которое принимает одно из двух значений: "Истина" или "Ложь". Функция ЕСЛИ возвращает одно значение, если указанное условие истинно, и другие, если оно ложно.

Пример:

В ячейке Н6 нужно записать максимальное из двух чисел, содержащихся в ячей­ках Н2 и Н5.

Формула, введенная в ячейку Н6:

=ЕСЛИ(Н2>Н5;Н2;Н5)

означает, что если значение ячейки Н2 больше значения ячейки Н5, то в ячейке Н6 бу­дет записано значение из Н2, в противном случае - из Н5.

В качестве Н2 или Н5 можно записать вложенную функцию ЕСЛИ. Число вло­женных ЕСЛИ не должно превышать семи. На месте логического выражения можно использовать одну из логических функций И или ИЛИ.

ФОРМАТ ФУНКЦИЙ:

И (логическое_выражение1; логическое_выражение2,...)

ИЛИ (логическое_выражение1; логическое_выражение2,...)

В скобках может быть указано до пятидесяти логических выражений. Функция И принимает значение "Истина", если одновременно все логические выражения истинны. Функция ИЛИ принимает .значение "Истина", если хотя бы одно из логических выра­жений истинно.

Пример:

Определить, входит ли в заданный диапазон (5-10) число, содержащееся в ячейке H10. Ответ 1 (если число принадлежит диапазону) и 0 (если число не принадлежит диапазону) должен быть получен в ячейке H12.

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

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

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

Задание 6. Заполните формулами таблицу 3, согласно рисунка 6.3, для обработки од­номерных массивов (данные по каждому году).

· Ячейку G11 отведите для ввода года и присвойте ей имя «год» (команда Вставка, Имя, Присвоить).

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

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

=ЕСЛИ(год= 1992;МАКС(В5:В16);ЕСЛИ(год= 1993 ;МАКС(С5:С16);

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

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

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

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

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

=ECЛИ(гoд=1992;МАКС($В$5:$В$16);ЕСЛИ(год=1993;МАКС($С$5:$С$16);

ECЛИ(гoд=l994;MAKC($D$5:$D$16);" данные отсутствуют")))

Внимание! Все массивы в формуле адресованы абсолютно, ячейка ввода года также адресована абсолютно.

· Скопируйте формулу из ячейки G12 в ячейки G13 :G16.

· Отредактируйте формулы в ячейках G13:G15, заменив функцию МАКС на требуемые по смыслу.

· Отредактируйте формулу в ячейке G16. Смените функцию МАКС на функцию СЧЕТЕСЛИ и добавьте критерий " <10 ".

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

=ЕСЛИ(год=1992;СЧЕТЕСЛИ($В$5:$В$16;"<10");ЕСЛИ(год=1993;

СЧЕТЕСЛИ($C$5:$C$16;"<10");ECЛИ(гoд=:1994;CЧЕTECЛИ

($D$5:$D$l6;"<10"); "данные отсутствуют")))

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

· Проверьте правильность заполнения таблицы 3 значениями (рисунок 6.3).

Задание 7. Сохраните результаты работы в файле под тем же именем work_6.xk в личном каталоге.

Задание 8. Представьте данные таблицы 1 (рисунок 6.1) графически, расположив диа­грамму на отдельном рабочем листе.

· Выделите блок A4:D16 и выполните команду меню Вставка, Диаграмма.

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

· Расположите диаграмму на отдельном листе.

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

Задание 9. Вернитесь к рабочему листу с таблицами.

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

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

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

Задание 11. Сохраните результаты работы в файле под тем же именем work_6.xts в личном каталоге.

Задание 12. Распечатайте результаты работы на принтере.

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

· Создайте вспомогательную таблицу (рисунок 6.4) для определения месяцев с коли­чеством осадков в пределах (>20;<80) мм.

Рисунок 6.4

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

=ЕСЛИ(И(В5>20;В5<80);1;0)

· Заполните этой формулой ячейки В22: В32,

· В ячейках, где условие выполняется, появляется 1.

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

· Выделите ячейки B2:B33 и скопируйте формулы в область C2:D33. В ячейках С33 и D33 получилось количество месяцев за 1993 и 1994 гг., удовлетворяющих условию (>20; <80).

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

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

=ECЛИ(год= 1992; В33;ЕСЛИ(год= 1993 ,СЗЗ ;ЕСЛИ(год= 1994;D33;

"данные отсутствуют'')))

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

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

Задание 14. Сохраните результаты работы в файле под тем же именем work_6.x's в личном каталоге.

Задание 15. Подведите итоги.

Проверьте:

- знаете ли вы, что такое: логические функции ЕСЛИ, И, ИЛИ.

- умеете ли вы: использовать встроенные функции Excel для решения типовых задач обработки массивов.