Как посчитать осадки в экселе

Обновлено: 01.07.2024

Минимальное количество осадков, выпавшее за три года.

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

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

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

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

Количество дождливых месяцев за все 3 года, в которые выпало больше 100 мм осадков.

Результаты представьте в виде дополнительной таблицы. Для нахождения указанных значений создайте макросы. Привяжите созданные макросы к заранее подготовленным графическим объектам.

Данные за 2003-2005 годы

Макс. кол-во осадков за 3 года (мм)

Мин. кол-во осадков за 3 года (мм)

Суммарное кол-во осадков за 3 года (мм)

Среднемесячное кол-во осадков за 3 года (мм)

Кол-во засушливых месяцев за 3 года (< 10 мм)

Кол-во дождливых месяцев (>100 мм)

Создайте макрос для очистки ячеек с полученными результатами. Привяжите макрос к графическому объекту.

7. Ваш лист должен выглядеть так:

Основная таблица располагается в диапазоне B5:E18


Ячейка C18 содержит формулу: =СУММ(C6:C17);

Ячейка D18 содержит формулу: =СУММ(D6:D17);

Ячейка E18 содержит формулу: =СУММ(E6:E17);

Подготовьте макрос для нахождения максимального и минимального количества осадков за 3 года. Привяжите созданный макрос к графическому объекту.

Ячейка H6 содержит формулу: =МАКС(C6:E17);

Ячейка H7 содержит формулу: =МИН (C6:E17);

Подготовьте макрос для нахождения суммарного и среднемесячного количества осадков за 3 года. Привяжите созданный макрос к графическому объекту.

Ячейка H8 содержит формулу: =СУММ(C6:E17);

Ячейка H9 содержит формулу: =СРЗНАЧ(C6:E17);

Подготовьте макрос для нахождения количества засушливых и дождливых месяцев за 3 года. Привяжите созданный макрос к графическому объекту.

Ячейка H10 содержит формулу: =СЧЕТЕСЛИ(B3:D14;<10);

Ячейка H11 содержит формулу: = СЧЕТЕСЛИ(B3:D14;>10);

Подготовьте макрос для очистки диапазона ячеек H5;H11. Привяжите созданный макрос к графическому объекту.

Создайте в Excel две таблицы «Количество осадков, выпавших за 3 года» и «Расчет показателей», как показано ниже:

Таблица 1. Количество осадков, выпавших за 3 года (в мм.)

Месяц Год
Январь 41,10 43,20 35,10
Февраль 11,40 42,10 20,50
Март 16,20 40,50 39,30
Апрель 19,50 41,30 35,10
Май 11,40 55,10 41,10
Июнь 87,10 78,20 79,10
Июль 79,10 81,50 48,40
Август 55,10 38,10 34,80
Сентябрь 36,80 44,80 60,10
Октябрь 85,10 80,10 73,80
Ноябрь 83,10 55,20 75,20
Декабрь 75,20 60,50 35,30

Таблица 2. Расчет показателей

Показатели Значение
Максимальное количество осадков за 3 года (мм) - МАКС
Среднемесячное кол-во осадков за 3 года (мм) - СРЗНАЧ
Число засушливых месяцев за 3 года (мм)
Кол-во осадков, выпавших в засушливые месяцы за 2006 и 2007 годы (мм)
Кол-во месяцев в пределах >40 и <80
Кол-во месяцев вне нормы <40 и >80

2. Статистические и математические функции;

· Введите в ячейки таблицы №2 формулы для расчета максимального и среднемесячного количества осадков, выпавших за 3 года. Функции МАКС и СРЗНАЧ содержатся в разделе статистических, либо в меню Формулы/Автосумма выбираем Максимумили Среднее:


Проверьте правильность проведенных расчетов, используя возможности строки состояния.

· Подсчитайте число засушливых месяцев за 3 года (допустим, засушливым считается месяц, в котором количество выпавших осадков меньше 40 мм). Для расчета числа засушливых месяцев за 3 года используйте функцию СЧЕТЕСЛИ. Критерием является условие: <40. Функция СЧЁТЕСЛИ содержится в разделе статистических функций (Эта функция подсчитывает количество ячеек внутри диапазона, удовлетворяющих заданному критерию). Синтаксис: СЧЁТЕСЛИ (диапазон; критерий).

· Подсчитайте общее количество осадков, выпавших в засушливые месяцы за 2006 и 2007 годы, используя функцию СУММЕСЛИ. Функция СУММЕСЛИ содержится в разделе математических функций (Эта функция используется для подсчета суммы показателей, выбранных из диапазона ячеек по заданному критерию. Критерий может быть в форме числа, выражения или текста). Синтаксис: СУММЕСЛИ (диапазон; критерий; диапазон суммирования).

Построим диаграмму распределения в Excel. А также рассмотрим подробнее функции круговых диаграмм, их создание.

Как построить диаграмму распределения в Excel

График нормального распределения имеет форму колокола и симметричен относительно среднего значения. Получить такое графическое изображение можно только при огромном количестве измерений. В Excel для конечного числа измерений принято строить гистограмму.

Внешне столбчатая диаграмма похожа на график нормального распределения. Построим столбчатую диаграмму распределения осадков в Excel и рассмотрим 2 способа ее построения.

Имеются следующие данные о количестве выпавших осадков:

Осадки.

Первый способ. Открываем меню инструмента «Анализ данных» на вкладке «Данные» (если у Вас не подключен данный аналитический инструмент, тогда читайте как его подключить в настройках Excel):

Анализ данных.

Гистограмма.

Задаем входной интервал (столбец с числовыми значениями). Поле «Интервалы карманов» оставляем пустым: Excel сгенерирует автоматически. Ставим птичку около записи «Вывод графика»:

После нажатия ОК получаем такой график с таблицей:

Пример.

В интервалах не очень много значений, поэтому столбики гистограммы получились низкими.

Теперь необходимо сделать так, чтобы по вертикальной оси отображались относительные частоты.

Найдем сумму всех абсолютных частот (с помощью функции СУММ). Сделаем дополнительный столбец «Относительная частота». В первую ячейку введем формулу:

Частота.

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

Минимальное значение.

Чтобы найти интервал карманов, нужно разность максимального и минимального значений массива разделить на количество интервалов. Получим «ширину кармана».

Ширина кармана.

Представим интервалы карманов в виде столбца значений. Сначала ширину кармана прибавляем к минимальному значению массива данных. В следующей ячейке – к полученной сумме. И так далее, пока не дойдем до максимального значения.

Максимальное значение.

Для определения частоты делаем столбец рядом с интервалами карманов. Вводим функцию массива:

Функция в массиве.

Вычислим относительные частоты (как в предыдущем способе).

Относительные частоты.

Построим столбчатую диаграмму распределения осадков в Excel с помощью стандартного инструмента «Диаграммы».

Пример1.

Частота распределения заданных значений:

Частота распределения.

Круговые диаграммы для иллюстрации распределения

С помощью круговой диаграммы можно иллюстрировать данные, которые находятся в одном столбце или одной строке. Сегмент круга – это доля каждого элемента массива в сумме всех элементов.

С помощью любой круговой диаграммы можно показать распределение в том случае, если

  • имеется только один ряд данных;
  • все значения положительные;
  • практически все значения выше нуля;
  • не более семи категорий;
  • каждая категория соответствует сегменту круга.

На основании имеющихся данных о количестве осадков построим круговую диаграмму.

Количество осадков.

Доля «каждого месяца» в общем количестве осадков за год:

Доли.

Круговая диаграмма распределения осадков по сезонам года лучше смотрится, если данных меньше. Найдем среднее количество осадков в каждом сезоне, используя функцию СРЗНАЧ. На основании полученных данных построим диаграмму:

В прошлом посте мы разобрались как Как получить курсы валют из интернета в Excel или Таблицы Гугл. Теперь мы попробуем получить прогноз погоды в Excel. У меня предпоследняя версия Excel, но работать будет и в ближайших. Смотрите, что у меня получилось. Если нравится, то начнем.

прогноз погоды в Excel

Находим источник данных о погоде для Excel

Запускаем Excel. Я покажу на данных с двух сайтов погоды. Вы можете выбрать из них или найти свой. Эти ссылки нам понадобятся. Обратите внимание, что у меня выбран Санкт-Петербург. Если вы хотите выбрать другой город, то откройте сайт, выберите свой город и скопируйте ссылку из строки браузера.

Ничего сложного делать не будем. Обрабатывать данные в Power Query тоже не будем, иначе материал будет сложноват для новичков.

Запускаем браузер по ссылке, находим город и копируем ссылку из адресной строки. Вот так это будет выглядеть.

прогноз погоды в Excel

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

И то же самое для второго сайта. Нам нужна адресная строка с вашим городом.

прогноз погоды в Excel

Импортируем данные о погоде в Excel

Запускаем Excel. Переходим на вкладку Данные и в блоке Получить и преобразовать данные выбираем Из Интернета. В окошке вставляем ссылку и жмём ОК.

прогноз погоды в Excel

Данные начнут загружаться и откроется таблица Навигатор. Если всё сделали правильно, то мы увидим слева дерево таблиц. В нашем примере будем использовать таблицу 1. В дальнейшем вы можете использовать любые таблицы, которые вам будут больше подходить.


Часто удобнее (но сложнее новичку) сразу преобразовать данные в Power Query, но мы решили, что делать этого не будем, так что нажимаем Загрузить, а не Преобразовать данные.

Таблица загрузится. Сразу обновим свойства подключения. Настроим обновления данных. При открытии, через промежутки времени и по команде. Таким образом Excel будет автоматически обновлять сводку погоды.


Формулы, форматирование прогноза погоды

На отдельном листе размещаем необходимые данные из таблицы. Копируем значения нужных ячеек.


Температуру, теплоощущение, влажность мы скопировали. Как я преобразовал англоязычные подписи?

С помощью служебной таблички. Левый столбик так как в оригинале, справа перевод, а третий столбик занял значками.


Как вставить изображения в ячейки Excel

Дело в том, что Excel не позволяет использовать в формулах изображения. Поэтому я использовал символьный шрифт Webdings. Сайт эти значки отобразить корректно не может, вот что получается.

ÕÖ×ØÙÚÛÜÝ

Но если скопировать эту строчку в Word или Excel и переключиться на шрифт Webdings, то мы увидим изображения.

Но так как это по сути шрифт, то все операции в ячейке с ним можно проводить так же как с текстом.

С помощью этой таблицы я осуществил перевод. В ячейке куда выводятся осадки, я ввел формулу:

Функция ВПР берет значение ячейки F3 в Таблице (Table 1) на английском (cloudy) и ищет соответствие в моей табличке E34:F39. Как только находит в 1 столбце выделенного E34:F39 диапазона слово cloudy, то копирует в ячейку Осадки соответствующее значение из 2 второго столбца диапазона ячейки правее (облачно).


Точно так же работает и вывод туч и облаков. Формула:

=ВПР(F11;F34:G39;2;)

Если в ячейке F11 слева появилось слово, то функция ВПР ищет в таблице F34:G39 соответствия и копирует в свою ячейку (G12) данные из столбца 2 совпавшей ячейки в столбце 1.


Надеюсь, что у вас получилось. Даты можно указать как функцию =СЕГОДНЯ(), =СЕГОДНЯ()+1 для завтрашней даты и т.д.

Условное форматирование ячеек

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

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


Таким образом мы имеем правила для одной ячейки по количеству ситуаций с облаками. Ниже видно, что цвет шрифта для каждого правила свой.


В принципе и всё. Погода загружается, данные меняются, облачка сменяются тучками. На этой основе вы можете сделать любой другой вывод прогноза погоды в Excel.

Только обратите внимание, что прогноз идет по часам и столбцы в исходном файле как бы смещаются влево. То есть прогноз на утро прошлого дня уже не отображается на сайте, а ваши формулы всё еще пытаются вытащить эти данные. Нужно продумывать все для каждого конкретного случая. Например, сравнивая даты или другим способом.

Как получилось на другом сайте по ссылке в начале поста:


Я не смог придумать как бы необычнее это сделать, так что просто сложил выборку рядом.

При том, что Excel может тащить из интернета различные табличные данные, то при должном старании и необходимости можно автоматически получать и обрабатывать большое количество данных.

Читайте также: