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

Цели работы:

- закрепить навыки по использованию функций Excel;

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

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

 

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

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

Таблица 6.1.

Количество осадков (мм)
Январь	37,2	34,5	8,0
Февраль	11,4	51,3	1,2
Март	16,5	20,5	3,8
Апрель	19,5	26,9	11,9
Май	11,7	45,5	66,3
Июнь	129,1	71,5	60,0
Июль	57,1	152,9	50,6
Август	43,8	96,6	145,2
Сентябрь	85,7	74,8	79,9
Октябрь	86,0	14,5	74,9
Ноябрь	12,5	21,0	56,6
Декабрь	21,2	22,3	9,4

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

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

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

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

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

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

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

Таблица 6.2.

Данные за 1998-2000 годы
Макс. кол-во осадков за 3 года (мм)
Мин. кол-во осадков за 3 года (мм)
Суммарное кол-во осадков за 3 года (мм)
Среднемесячное кол-во осадков за 3 года
Кол-во засушливых месяцев за 3 года

Те же данные определить для каждого года и оформить в виде отдельной таблицы 6.3. Дополнительно для каждого года определить:

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

§ количество месяцев в году с количеством осадков вне нормы (до 10 или свыше 100 мм).

 

Таблица 6.3.

Данные за один год
Введите год:
Макс. кол-во осадков в году (мм)	129,1
Мин. кол-во осадков в году (мм)	11,4
Суммарное кол-во осадков за год (мм)	531,7
Среднемесячное кол-во осадков в году	44,308
Кол-во засушливых месяцев в году
количество месяцев в пределах от 20 до80 мм
количество месяцев вне нормы (до 10 или свыше 100 мм)

 

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

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

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

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

 

Методические указания.

 

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

 

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

 

ЗАДАНИЕ 3. На том же листе создайте и оформите еще 2 таблицы (табл. 6.2 и 6.3)

 

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

4.1. В ячейку G5 =MAKC(B5:D16)

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

4.3. Определите количество засушливых месяцев за 3 года. Для определения воспользуйтесь функцией СЧЕТ ЕСЛИ, которая подсчитывает количество непустых ячеек, удовлетворяющих заданному критерию внутри интервала. Формат функции: СЧЕТ ЕСЛИ (интервал; критерии). Воспользуйтесь мастером функций, на 2 шаге укажите интервал B5:D16 и критерий <10.

 

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

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

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

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

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

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

Например, в ячейке Н6 нужно записать максимальное из двух чисел, содержащихся в ячейках Н2 и Н5. Формула, введенная в ячейку Н6: = ЕСЛИ (Н2>Н5; Н2; Н5) означает, что если значение ячейки Н2 больше значения ячейки Н5, то в ячейке Н6 будет записано значение из Н2, в противном случае из Н5.

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

На месте логического выражения можно использовать одну из логических функций И или ИЛИ. Формат функций: И(<логическое выражение 1>; <логическое выражение 2>,....); ИЛИ(<логическое выражение 1>; <логическое выражение 2>,....). В скобках может быть указано до пятидесяти логических выражении. Функция И принимает значение "Истина", если одновременно все логические выражения истинны. Функция ИЛИ принимает значение "Истина", если хотя бы одно из логических выражений истинно.

Например, требуется определить, входит ли в заданный диапазон (5; 10) число, содержащееся в ячейке Н10. Ответ: 1(если число принадлежит диапазону) и 0 (если число не принадлежит диапазону) должен быть получен в ячейке H12. В ячейку H12 вводится формула: =ЕСЛИ(И (Н10>5; Н10<10); 1; 0) В ячейке H12 получится значение 1, если число принадлежит диапазону, и значение 0, если число вне диапазона.

 

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

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

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

6.2. В ячейку G12 с использованием Мастера функций введите формулу: =ЕСЛИ(год=1998; МАКС(В5:В16); ЕСЛИ(год=1999; МАКС(С5:С16); ЕСЛИ (год=2000; МАКС(D5:D16); " данные отсутствуют"))).

Проанализируйте формулу. Несмотря на сложный синтаксис, смысл ее очевиден. В зависимости от года, который вводится в именованную ячейку «год», определяется максимум в том или ином диапазоне табл. 6.1. Диапазон В5:В16 - это одномерный массив данных за 1998 г.; С5:С16 - массив данных зa l999 г.; D5:D16 – зa 2000г..

6.3. Замените в формуле в ячейке G12 относительную адресацию ячеек на абсолютную: =ЕСЛИ(год=1998; МАКС($В$5:$В$16); ЕСЛИ(год=1999; МАКС($С$5:$С$16); ЕСЛИ (год=2000; МАКС($D$5:$D$16); " данные отсутствуют"))).

Для выполнения следующих выборок эту формулу можно скопировать в ячейки G13:G16 и

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

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

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

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

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

=ЕСЛИ(год=1998; СЧЕТ ЕСЛИ($В$5:$В$16; <10); ЕСЛИ(год=1999; СЧЕТ ЕСЛИ($С$5:$С$16; <10); ЕСЛИ (год=2000; СЧЕТ ЕСЛИ($D$5:$D$16; <10); " данные отсутствуют"))).

6:7. Введите в ячейку G11 год: 1998.

6. 8. Проверьте правильность заполнения табл. 6.3 значениями.

 

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

 

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

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

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

 

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

 

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

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

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

 

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

 

ЗАДАНИЕ 12. Определите количество месяцев в каждом году с количеством осадков в пределах (>20; <80) мм и в пределах (<10; >100) мм.

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

Таблица 6.4.

Вспомогательная таблица для определения числа месяцев в году с количеством осадков в пределах от 20 до80 мм
Сумма

 

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

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

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

12.5. Выделите ячейки В21:ВЗЗ и скопируйте формулы в область C21:D33. В ячейках СЗЗ и D33 получилось количество месяцев за 1999 и 2000 гг., удовлетворяющих условию (>20;<80).

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

12.7. В ячейку G17 занесите формулу: =ЕСЛИ(год=1998; ВЗЗ; ЕСЛИ(год=1999; СЗЗ; ЕСЛИ (год=2000; D33; " данные отсутствуют")))

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

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

 

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

 

Лабораторная работа № 7