Задача 1.
ОПИСАНИЕ ЗАДАЧИ
В течение многих лет ежедневно работники гидрометеорологической службы наблюдают за погодой и скрупулезно записывают данные: скорость и направление ветра, сведения об осадках, дневные и ночные колебания темпер атуры воздуха и т. д. Для чего это делается?
Чтобы понять это, проведем статистическую обработку собранного массива данных.
ЦЕЛЬ МОДЕЛИРОВАНИЯ
Цель моделирования учебная: выяснить, какую информацию можно получить, обработав числовой массив.
II этап. Разработка модели
ИНФОРМАЦИОННАЯ МОДЕЛЬ
| Объект | Параметры | |
| название | значение | |
| Массив температур в ноябре | Дневные температуры t°Cдн Ночные температуры t°CH Среднестатистическая температура t°CСТ | Исходные данные Исходные данные Исходные данные |
Исходные температуры
| День месяца | |||||||||||||||
| Дневная t°C | -1 | -2 | -6 | -10 | -11 | -7 | -3 | -2 | |||||||
| Ночная t°C | -1 | -3 | -2 | -10 | -5 | -10 | -14 | -15 | -10 | -2 |
| День месяца | |||||||||||||||
| Дневная t°C | -3 | -2 | -5 | -11 | -12 | -17 | -20 | -18 | -17 | -19 | -17 | ||||
| Ночная t°C | -5 | -5 | -2 | -4 | -7 | -16 | -16 | -20 | -25 | -24 | -20 | -22 | -20 |
Математическая модель обработки массива будет подробно, с пояснениями представлена ниже. Для обработки используются стандартные логические и статистические функции, имеющиеся во многих средах.
КОМПЬЮТЕРНАЯ МОДЕЛЬ
Дата вводится в формате ДАТА (например, 12.09.1998). При правильном вводе запись размещается в ячейке с выравниванием вправо.
| Ниже приведены формулы для расчетов. Формулы, которые заменены знаком?, введите самостоятельно. Там, где нужно, замените относительные адреса на абсолютные. | |||
| Ячейка | Формула | Пояснение | |
| А6 | =А5+1 | Подсчет следующей даты | (1) |
| D5 | =? | Подсчет среднесуточной температуры | (2) |
| В36 | =МАКС(В5:В34) | Максимальная дневная температура | (3) |
| С36 | =? | Минимальная ночная температура | (4) |
| D36 | =? | Средняя температура за ноябрь | (5) |
| Е5 | =ЕСЛИ(D5>С2;1;0) | Отмечаются (1) теплые дни, когда температура была выше среднестатистической | (6) |
| Е36 | =? | Количество теплых дней СУММ или СЧЕТЕСЛИ | (7) |
| F5 | =ЕСЛИ(В5=В36;А5;"-") | Отмечается дата, когда была самая высокая дневная температура | (8) |
| G5 | =? | Отмечаются (протапл.) дни, когда следовало бы протапливать помещения (среднесут. t° < 8 °С) | (9) |
Примечание. При подсчете по формуле 5 установить фиксированный формат с 1 знаком в дробной части: Формат|Числа|Фиксированный. В ячейках, где ведутся расчеты, определяющие дату, формат ячеек должен быть, соответственно, ДАТА.
Задача. Ввести в таблицу EXCEL исходные данные по образцу:
| А | В | С | D | Е | F | G | |
| Исследование массива температур | |||||||
| Среднестат. темп-ра | 1,5 | ||||||
| Температура | |||||||
| Дата | Дневная | Ночная | Среднесут. | Теплые дни | Дата max | Протапливание | |
| 01.11.98 | Формула 2 | Формула 6 | Формула 8 | Формула 9 | |||
| 02.11.98 | Заполнить вниз | ||||||
| 30.11.98 | -17 | -20 | |||||
| Мах дневных | Min ночных | Средняя | Кол-во теплых | ||||
| Формула 3 | Формула 4 | Формула 5 | Формула7 |
Компьютерный эксперимент
ПЛАН ЭКСПЕРИМЕНТА
ТЕСТИРОВАНИЕ
Провести тестовый расчет модели.
ЭКСПЕРИМЕНТ 1
Исследовать колебания дневных, ночных и среднесуточных температур.
ЭКСПЕРИМЕНТ 2
Определить теплые дни, когда дневная температура была выше среднестатистической, и количество таких дней в ноябре.
ЭКСПЕРИМЕНТ 3
Определить даты, когда дневная температура была максимальной.
ЭКСПЕРИМЕНТ 4
Определить дни, когда следовало протапливать помещения (среднесуточная температура ниже +8 °С).
ЭКСПЕРИМЕНТ 5
Использовать модель для исследования температуры в другие месяцы. На Gismeteo вы найдете дневник погоды с реальными значениями температур за несколько последних месяцев. Особенностью является то, что за некоторые дни значения температур отсутствуют. Удостоверьтесь, что ваша модель правильно обрабатывает данные и в этом случае.
ПРОВЕДЕНИЕ ИССЛЕДОВАНИЯ
1. Введите расчетные формулы и скопируйте их (там, где это указано) в 2-3 соседние ячейки. Результаты сравните с примером расчета.
Сравнение с контрольным образцом показывает правильность введения формул (форма тестирования).
ПРИМЕР РАСЧЕТА
| А | В | С | D | Е | F | G | |
| Исследование массива температур | |||||||
| Среднестат. темп-ра | 1,5 | ||||||
| Температура | |||||||
| Дата | Дневная | Ночная | Среднесут. | Теплые дни | Дата max | Протапливание | |
| 01.11.98 | 6,5 | - | протапливать | ||||
| 02.11.98 | 9,0 | 02.11.98 | |||||
| 03.11.98 | 8.0 | - | - | ||||
| ... | |||||||
| 30.11.98 | -17 | -20 | -18,5 | - | Протапливать | ||
| Мах дневных | Min ночных | Средняя | Кол-во теплых дней | ||||
| -25 | -6.3 |
2. Заполните формулами строки на все дни месяца.
3. Постройте диаграммы дневных, ночных и среднесуточных температур по данным блока ячеек B5:D34.
Для построения диаграммы необходимо ввести вспомогательный расчетный столбец, в котором по дате будет рассчитываться порядковый день месяца. Этот столбец будет использован в диаграмме в качестве меток по оси х.
4. Определите холодные дни, когда дневная температура была ниже среднестатистической, и количество таких дней в ноябре. Формулу составьте самостоятельно.
5. Определите даты, когда ночная температура была минимальной. Формулу составьте самостоятельно.
Задача 2. Компания по снабжению электроэнергией взимает ежемесячную плату с клиентов по следующим тарифам: если потребление не больше 250 кВт/ч – 1,4 рубля за 1 кВт/ч, если больше 250 кВт/ч – 1,2 рубля за 1 кВт/ч. Требуется рассчитать оплату клиентам, указанным в табл.3.
Таблица 1
| Клиент | Потребление | Сумма оплаты | Всего за лето | ||||
| июнь | июль | август | июнь | июль | август | ||
| Федоров А.П. | |||||||
| Семенов П.И. | |||||||
| Кузнецов Т.С. | |||||||
| Васин О.Д. | |||||||
| Беляев Л.Б | |||||||
| Потребили < 250 кВт/ч | Общий итог: |
1. Заполните таблицу в диапазоне А1:H8.
2. Для расчета оплаты воспользуемся функцией ЕСЛИ.
Введите в ячейку Е3: =ЕСЛИ(В3<251;В3*1,4;В3*1,2).
3. Скопируйте ячейку Е3 на весь диапазон Е3:G7.
4. Рассчитайте сумму за лето: вставьте в ячейку H3 функцию суммирования =СУММ(Е3:G3).
5. Скопируйте ячейку H3 в диапазон H4:H7.
6. Общий итог рассчитайте самостоятельно.
7. Найдем число клиентов, потребивших в июне не больше 250 кВт/ч.
Для этого введите в ячейку В8 формулу =СЧЁТЕСЛИ(B3:B7;<251).
8. Скопируйте формулу в ячейки С8:D8.
Задача 3. Компания по снабжению электроэнергией взимает ежемесячную плату с клиентов по следующим тарифам: за первые 200 кВт/ч – 1,4 рубля за 1 кВт/ч, за вторые 200 кВт/ч – 1,2 рубля за 1 кВт/ч, за оставшиеся – 1 рубль за 1 кВт/ч. Требуется выставить счет клиентам.
1. Введите в ячейку А11 заголовок Тройной вариант.
2. Скопируйте табл. 3 в диапазон А12:H19.
3. Очистите ячейки E14:G18.
4. Рассчитаем оплату за месяц:
- если потребление < 201 кВт/ч, умножаем его на 1,4;
- если от 200 до 400 кВт/ч – за первые 200 кВт/ч потребитель платит
280 руб. и за оставшиеся – по 1,2 руб. за кВт/ч. (для формирования
условия здесь необходимо использовать функцию И);
- если потребление больше 400 кВт/ч, то за 400 кВт/ч потребитель
заплатит 520 руб. плюс по одному рублю за остаток.
Получаем следующую формулу для расчета в ячейке Е14:
=ЕСЛИ(B14<201; B14*1,4;0) +
ЕСЛИ(И(B14>200; B14<401); (B14-200)*1,2+280;0) +
ЕСЛИ(B14>400;(B14-400)*1+520;0)
5. Заполните оставшиеся пустыми ячейки.
6. Исправьте текст в ячейке А19 на Потребили < 200 кВт/ч.
7. В ячейку А20 занесите 200-400 кВт/ч.
8. Рассчитайте число клиентов в ячейках В19 и В20.
Задание
Для табл. 1 вычислите суммарное потребление каждого клиента за летний период. Оплата рассчитывается по следующему тарифу:
1) 1 кВт/ч – 1,4 рубля,
2) если полученная сумма за все лето больше 1000 руб. – скидка 5%;
3) если больше 1500 руб. – скидка 8%;
4) если больше 2000 руб. – 10%.
Определите, сколько клиентов получили скидки.
Указание к выполнению. Скопируйте табл1. в новый диапазон и выполните необходимую корректировку последней строки.
[1] На рисунке редактируется вложенная функция СУММ. Диапазон K10:K19 является единственным аргументом функции и отображается в поле ввода Число1. Справа от поля ввода отображаются значения всех ячеек диапазона. Результат функции СУММ (935) отображается под списком аргументов. Результат всей формулы отображается в нижней части окна; поскольку сумма значений диапазона K10:K19 меньше или равна 5000, результат равен 0,05 или 5%.