Базовый анализ данных, часть 1
Короткое описание урока: На этом уроке вы научитесь основным приемам анализа данных.
Итак, перед нами таблица с инфекционными и паразитарными заболеваниями с 2012 по 2016 год. Таблица как человек: мы можем задавать ей вопросы и получать ответы. Помогут в этом формулы Excel. С помощью фильтра мы уже ответили на такие вопросы, как:
- Каким инфекционным/паразитарным заболеванием заразились больше всего человек в 2012 году? А в 2016?
- Каким инфекционным/паразитарным заболеванием страдали меньше всего человек?
Задание: Придумайте еще несколько вопросов к этой таблице.
Вот несколько вопросов, которые придумали мы:
- Сколько всего человек заразились каждым заболеванием за пять лет?
- Какое заболевание самое многочисленное?
- Сколько человек в среднем болеет в год тем или иным заболеванием?
- За последний год, заразились ли этим заболеванием больше или меньше обычного?
Теперь, когда у нас есть вопросы, попытаемся ответить на некоторые из них. Для этого мы будем использовать формулы Excel. Они позволяют делать вычисления внутри ячеек таблицы:
- Все формулы в Excel начинаются со знака равно.
- Основные вычисления в Excel можно делать базовыми математическими знаками:
+ для суммы, – для разности, / для деления и * для умножения.
Считаем суммы, используя функцию СУММ
Например, чтобы подсчитать, сколько человек заразились брюшным тифом и паратифами за 2015 и 2016 год в сумме, нам нужно встать в пустую ячейку и написать формулу:
= Е2+F2
Е2 и F2 можно написать самому, а можно просто кликнуть на эти ячейки. Нажмите Enter, и получите результат: 81.
Как подсчитать всех, кто заразился брюшным тифом и паратифами за пять лет? Можно отдельно прописать каждую ячейку, но это будет долго. Представьте, что у нас не 5 лет, а гораздо больше. Для этого в Экселе существуют функции, и сейчас мы используем одну из них.
Функции – это команды в Экселе, которые позволяют делать математические вычисления с указанными блоками. В нашем случае, функция суммы поможет посчитать общее число заболеваний тифом за пять лет.
Для подсчета суммы существует функция «СУММ» (или SUM в английской версии). Большинство функций выражаются аббревиатурами.
Запишите в ячейке: =СУММ или =SUM
Как только вы начнете набирать функцию, она появится в выпадающем списке. Нажмите на нее.
Теперь в скобках надо записать диапазон ячеек, сумму которых надо подсчитать. Нажмите на первую ячейку – B2 (заболеваемость за 2012 год) и ведите, не отпуская мышку, до F2(заболеваемость за 2016 год). У вас должна получиться формула
=СУММ(B2:F2) или =SUM(B2:F2)
Нажмите Enter, получите результат: 271. 271 человек заразился брюшным тифом и паратифами в Кыргызстане с 2012 по 2016 год.
Давайте озаглавим наш столбец как «Сумма 2012-2016».
Как подсчитать теперь такую же сумму по каждому заболеванию? Можно снова ввести формулу, а можно скопировать формулу по столбцу вниз. Для этого встаньте в правый нижний угол ячейки G2. Курсор вашей мышки должен превратиться в черный тоненький крестик. Сделайте двойной щелчок, и ваш столбец заполнится до конца таблицы.
Теперь мы можем наложить фильтр на первую строку и отсортировать данные в столбце G от максимального к минимальному. Получаем ответ на наш первый вопрос: за последние пять лет, топ 5 инфекционных и паразитарных заболеваний – инфекции дыхательных путей, кишечные инфекции, гепатит, гастроэнтериты и корь.
Сколько всего человек заразились каждым заболеванием за пять лет? Какое заболевание самое многочисленное? Получается, за 5 лет почти миллион человек –926 677 – страдал от инфекции горла и легких. Но так ли это?
На самом деле, перед нами таблица, где зафиксированы случаи заболевания. Если один человек болеет из года в год, он будет из года в год попадать в эту таблицу. Более того, если он или она заболел два раза в одном году, то он два раза попадет в статистику по одному году. И наоборот, если кто-то лечился дома и не ходил ко врачу, его в статистике не будет.
Тем не менее, мы все равно видим, что инфекции верхних дыхательных путей – самое частое заболевание в таблице. Инфекциями желудка болеют на порядок реже.
Используем функцию СРЗНАЧ
А сколько случаев в среднем в год по каждому заболеванию? Ответив на этот вопрос, мы сможем понять общую картину заболеваемости: вместо данных за отдельно взятый год мы увидим основные тенденции. Для этого нам нужно посчитать средние значения.
Сделаем это в отдельном столбце. Озаглавьте его «Среднее 2012-2016». Начните писать формулу: =СР в ячейке H2, и выберите формулу СРЗНАЧ (AVERAGE) из выпадающего списка.
Вам нужен тот же диапазон, что и при вычислении суммы. Ваша формула должна выглядеть так: =СРЗНАЧ(B2:F2) или =AVERAGE(B2:F2).
Нажмите Enter, получите результат: в среднем, за год регистрируется 185 тысяч инфекций дыхательных путей. Скопируйте формулу вниз по столбцу двойным щелчком по ячейке H2.
Как мы посчитали это значение? Формула СРЗНАЧ лишь берет сумму и делит её на число ячеек. Вы можете убедиться в этом сами: поделите число дыхательных инфекций за все годы из столбца «Сумма» на 5.
Обратите внимание, что ваши значения имеют знаки после запятой. Это потому, что при делении не всегда получаются целые числа. Тем не менее, вы можете округлить значения. Выделите их и сократите знаки после запятой кнопкой «уменьшить разрядность».
Теперь вы можете сравнить, например, показатели за 2016 год со средним значением. Это даст вам ощущение того, велик или мал показатель за последний год. Например, в 2016 году с инфекциями верхних дыхательных путей было зарегистрировано 261 366 случаев. В среднем за год, если брать последние 5 лет, регистрируются 185 тысяч. О чем вам это говорит?
На заметку: теперь у вас есть вся информация, чтобы ответить на вопросы из начала этого урока.