Задание для самостоятельного выполнения. Постройте совмещенные графики по одному из вариантов
Постройте совмещенные графики по одному из вариантов. Для каждого варианта даны два варианта изменения предела X и шага: а) и б).
- построить график по варианту а).
- изменить пределы в соответствии с вариантом б).
- отредактируйте график по варианту б)
- при редактировании обратите внимание на оформление.
- расположите таблицу XY – график на одном листе.
- сохраните под именем work4_5.xls. и распечатайте график и таблицу на одном листе.
Варианты задания:
Вариант № | Функция 1 | Функция 2 | Предел изменения Х | Шаг |
Y=-2/x | Y= -4/x | a) (0,5 5) б) (1 10) | 0,5 | |
Y=х-2 | Y= х-3 | a) (-3 -0,5) б) (-2 -0,75) | 0,5 0,25 | |
Y=х2 | Y= х3 | a) (-3 3) б) (-6 6) | 0,5 | |
Y=2х | Y= (1/2)х | a) (-2 2) б) (-4 4) | 0,5 | |
Y=ln x | Y= log10x | a) (0,5 10) б) (0,5 19,5) | 0,5 | |
Y=х-1/2 | Y=х1/2 | a) (0,5 9) б) (0,1 1,8) | 0,5 0,1 |
ПРАКТИЧЕСКАЯ РАБОТА №5
ОБРАБОТКА ДАННЫХ МЕТЕОСТАНЦИИ.
Цель работы:
- закрепить навыки по использованию функций 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 1994 г.
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 и 1994 гг., удовлетворяющих условию (>20; <80).
13.6.Аналогично создайте вспомогательную таблицу для определения числа месяцев с количеством осадков в пределах (<10; >100)(формулу необходимо изменить в соответствии с условием).
13.7.В ячейку G17 занесите формулу:
=ЕСЛИ(год=1992;В33;ЕСЛИ(год=1993;С33;ЕСЛИ(год=1994;D33; «данные отсутствуют»))).
13.8. Скопируйте эту формулу в ячейки G18 и отредактируйте.
13.9. Оформите на свой вкус вспомогательные таблицы и добавьте к ним заголовки и обозначения.
ЗАДАНИЕ14. Сохраните результат работы под тем же именем work5.xls в личном каталоге.
ЗАДАНИЕ 15. Подведите итоги.
Проверьте:
Знаете ли вы: Умеете ли вы:
· Логические функции ЕСЛИ И ИЛИ |
· Использовать встроенные функции EXCEL для решения типовых задач обработки массивов. |
Предъявите преподавателю:
- краткий конспект;
- файл work5.xls на экране и на рабочем диске в личном каталоге.
- распечатанную таблицу и диаграмму.