Обработка данных метеостанции.

 

Цель работы:

• закрепить навыки по использованию функций Excel;
• научиться решать типовые задачи по обработке массивов с использованием электронных таблиц;
• познакомиться с логическими функциями Excel

 

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

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

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

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

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

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

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

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

Данные оформить в виде отдельной таблицы.

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

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

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

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

 

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

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

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

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

 

ХОД РАБОТЫ:

 

ЗАДАНИЕ 1. Заполните таблицу согласно рисунку и оформите ее по своему усмотрению.

 

ЗАДАНИЕ 2. Сохраните таблицу на диске в личном каталоге под именем work5.xls

 

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

 

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

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

4.1. В ячейку G5 =МАКС (B5:D16)

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

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

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

 

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

 

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

Воспользуйтесь мастером функций, на 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:$В$1б);ЕСЛИ(год=1993;МАКС($С$5:$С$16);ЕСЛИ(год=1 995;MAKC($D$5:$D$16); "данные отсутствуют")))

 

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

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

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

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

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

 

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

ЕСЛИ($С$5:$С$16;"<Ю");ЕСЛИ(год=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 на экране и на рабочем диске в личном каталоге.
• распечатанную таблицу и диаграмму.

 

 

ПРАКТИЧЕСКАЯ РАБОТА №6