на тему «Анализ данных в MS Excel».
Задание
Данные об изменении температуры воздуха в Мадриде, Риме и Париже в зимние месяцы представлены на отдельных листах рабочей книги Excel. На основании этих данных:
а) Построить графики изменения температуры;
б) Подобрать к каждому графику линию тренда и сделать прогноз температуры на 1 марта;
в) Построить гистограмму распределения данных и определить, сколько дней в каждом из указанных городов температура воздуха была ниже 0° С, от 0 до 10° С, выше 10 С;
г) Для каждого города определить минимальную, максимальную и среднюю температуру воздуха в декабре, январе и феврале;
д) Для каждого города определить кол-во дней зимой с температурой ниже нуля;
е) С помощью логических функций Excel сгенерировать фразы, автоматически комментирующие результаты анализа данных (например, «Самым теплым зимним месяцем в Мадриде был…», «Самый теплый зимний день был в …» и т.д.).
Методические рекомендации
На рабочем листе «Рим» представлены исходные данные о температуре воздуха в Риме с 01.12.96 по 16.12.96.(рис. 1). Аналогичную структуру имеют данные на листах «Мадрид» и «Париж».
Графики изменения температуры воздуха, полученные с помощью Мастера диаграмм (Вставка -> Диаграмма -> На новом листе), показаны на рисунке 2. Поскольку данные находятся на разных рабочих листах, можно построить сначала один график, а затем добавить остальные, дважды щелкнув мышью по диаграмме и выбрав команду Вставка -> новые данные.
Рисунок 1- Исходные данные о температуре воздуха в Риме.
Рисунок 2 - Графики изменения температуры воздуха в Париже, Риме и Мадриде.
Линия тренда позволяет определить тенденцию изменения представленных на диаграмме данных. Чтобы добавить линию тренда, следует выделить график, в меню Вставка выбрать команду Линия тренда, выбрать тип линии тренда, а во вкладке «Параметры» установить переключатель «Показывать уравнение на диаграмме» и указать количество периодов (в нашем случае дней), для которых необходимо сделать прогноз. На рисунке 3 показана линия тренда для графика температуры в Риме в математическом виде её уравнение записывается так:
y = 0,0062x² - 437,31x + 8E+06
Рисунок 3 - Линия тренда для графика температуры в Риме.
Гистограмма распределения данных отображает количество попаданий некоторой величины (частоту) в заданный интервал (карман). В задании оговорены следующие интервалы: меньше 0, от 0 до 10 и больше 10. Поэтому необходимо сначала ввести столбец данных -0;10 а затем воспользоваться меню Сервис –> Анализ данных –> Гистограмма.
Самым простым, но не самым эффективным способом выполнения задания г) является построение таблицы, показанной на рисунке 4.
Рисунок 4 – Пример построения таблицы.
Перед вводом формул в ячейки B3:J5 удобно присвоить имена блокам ячеек, содержащим значения температур для каждого месяца в каждом городе (Мадрид_декабрь, Мадрид_январь, Мадрид_февраль… и т.д.). Тогда, например, формула в ячейке B3 будет записана как: =МИН(Мадрид_декабрь), а в ячейке C5: =МАКС(Париж_декабрь)
Для подсчета количества данных, удовлетворяющих некоторому условию (задание д), удобно воспользоваться математической функцией СЧЕТЕСЛИ(диапазон;условие). Так, для подсчета количества дней с температурой ниже нуля в каждом городе, следует ввести формулы:
=СЧЕТЕСЛИ(Рим!B2:B91;”<0”);
=СЧЕТЕСЛИ(Мадрид!B2:B91;”<0”);
=СЧЕТЕСЛИ(Париж!B2:B91;”<0”).
Чтобы автоматически сгенерировать указанные в задании е) фразы, запишем сначала формулу для определения самого теплого зимнего месяца в Мадриде:
=ЕСЛИ(И(D3>G3;D3>J3);”декабрь”;ЕСЛИ(И(G3>D3;G3>J3); “январь”;”февраль”))
Для того, чтобы в строке выводилось не только название месяца, но и комментарий, воспользуемся операцией конкатенации “&”:
=”Самым теплым месяцем в Мадриде был “ & ЕСЛИ(И(D3>G3;D3>J3);”декабрь”;ЕСЛИ(И(G3>D3;G3>J3); “январь”;”февраль”))
В результате, для приведенных на рис.4 данных, будет сгенерирован ответ “Самым теплым месяцем в Мадриде был февраль”
Результаты выполнения курсовой работы должны быть аккуратно оформлены, сопровождены комментариями и распечатаны. На одном листе формата А4 можно располагать несколько рисунков.
ПРИЛОЖЕНИЯ
ПРИЛОЖЕНИЕ 1
