Анализ данных в электронных таблицах

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

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

Использование функций на примере MS Exel.

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

Это можно сделать несколькими способами:

1. Перейти в ячейку и нажать на иконку функции вверху слева. Тогда откроется меню со списком всех функций для выбора. В данном списке можно осуществлять поиск и посмотреть краткое описание каждой функции.

   

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

   
2. Написать в ячейке знак «=» и воспользоваться списком последних используемых функций.

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

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

Математические и статистические функции.

Для решения заданий экзамена необходимо уметь работать со следующими математическими функциями.

СУММ - Находит сумму всех аргументов.

Например, в ячейку С5 записали сумму значений ячеек А1:C3.

СРЗНАЧ - Считает среднее арифметическое аргументов. Важно, что при расчёте среднего арифметического НЕ учитываются пустые ячейки.

Например, в ячейку С5 записали среднее значение ячеек А1:C3

СЧЁТ – Считает количество НЕПУСТЫХ ячеек в диапазоне.

Например, в ячейку С5 записали формулу =СЧЁТ(А1:C3)

Полученное значение в ячейке С5 равно именно 7, так как в выделенном диапазоне 7 заполненных ячеек, если мы запишем ещё в одну ячейку какое-то значение (даже 0), то значение в С5 увеличится на 1.

МАКС - Возвращает наибольшее значение в списке аргументов.

МИН - Возвращает наименьшее значение в списке аргументов.

Например, в ячейку С5 записали формулу =МАКС(А1:C3), а в ячейку B5 = МИН(А1:C3)

СУММЕСЛИ - Суммирует значения в ячейках, удовлетворяющие заданному условию

Например, в ячейку С5 необходимо сосчитать сумму значений, больших 1, в диапазоне А1:C3.

При вызове диалогового окна функции заполняем выбор диапазона и записываем условие.

Получаем в ячейке формулу =СУММЕСЛИ(A1:C3;">1"), и результат равный 11 (2+3+3+3)

Важно, что при использовании данной функции мы можем проверять условие по одному диапазону, а суммировать значения в другом. В таком случае поле Диапазон будет отвечать за ячейки, в которых проверяется условие, а Диапазон_суммирования – это будут ячейки, значения которых будут суммироваться.

Например, в ячейку С5 запишем формулу =СУММЕСЛИ(A1:A3;">1";B1:B4)

Условие проверялось по столбцу А, а значения брались из советующих строк столбца B.

СЧЁТЕСЛИ - Подсчитывает количество ячеек в диапазоне, удовлетворяющих заданному условию.

Например, в ячейку С5 необходимо сосчитать количество значений, не равных 2, в диапазоне А1:C3.

При вызове диалогового окна функции заполняем выбор диапазона и записываем условие.

Получаем в ячейке формулу =СЧЁТЕСЛИ(A1:C3;"<>2"), и результат равный 8. В данном случае пустые ячейки учитываются, так как значение в них не равно двум.

Логические функции.

И – логические умножение, возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА.

ИЛИ – логические сложение, возвращает значение ИСТИНА, если хотя бы один аргумент имеет значение ИСТИНА.

НЕ – логическое отрицание, меняет логическое значение своего аргумента на противоположное

Стоит отметить, что для логических функций в качестве значения ИСТИНА может выступать как непосредственно запись «ИСТИНА» в ячейке, так и любое число, не равное 0. Для значения «ЛОЖЬ» также может использоваться само слово и 0.

Пример, в ячейку C3 записали формулу =И(A1:C1). Полученное значение – ИСТИНА, так как в диапазоне только положительный числа

ЕСЛИ – выполняет проверку условия и присваивает ячейке определённое значение в случае выполнения и/или невыполнения условия.

Для её использования обязательно надо указать логическое выражение для проверки условия. Если не заполнить поля Значение_если_истина, Значение_если_ложно, то в случае истинности функция вернёт значение ИСТИНА, в случае ложности - ЛОЖЬ

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

Пример составления сложной функции на основе пункта задания экзамена.

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

Условия:

В электронную таблицу занесли данные о тестировании учеников по выбранным ими предметам.

В столбце A записан код округа, в котором учится ученик; в столбце B – фамилия; в столбце C – выбранный учеником предмет; в столбце D – тестовый балл.

Всего в электронную таблицу были занесены данные по 1000 учеников.

Найдите средний тестовый балл учеников, которые проходили тестирование по информатике. Ответ запишите в ячейку H3 таблицы с точностью не менее двух знаков после запятой.

Решение.

1 способ.

Для определения среднего балла можно использовать функцию СРЗНАЧ, но для этого надо в таблице «выделить» учеников по информатике, что возможно за счёт функции ЕСЛИ, в которой будет условие на поле предмет «=информатика», а значение, которое будет записываться в один из соседних столбцов, будет равно значению в поле балл.

Составим функцию в ячейке E2.

Получим формулу =ЕСЛИ(C2="информатика";D2)

Растянем формулу на весь диапазон. Теперь везде где предмет «информатика», в столбце E стоят баллы, в других местах ЛОЖЬ.

Теперь в ячейку Н3 осталось записать функцию для нахождения среднего значения в столбце Е - =СРЗНАЧ(E2:E1001)

2 способ.

Среднее значение – это сумма аргументов, делённая на их количество.

Для начала надо аналогично первому способу воспользоваться функцией ЕСЛИ, затем посчитать сумму значений в столбце Е и при помощи СЧЁТЕСЛИ определить количество таких значений.

Для этого в ячейку G1 запишем формулу =СУММ(E2:E1001).

В ячейку G2 составим формулу СЧЁТЕСЛИ с проверкой значений столбца Е на то, что они не ЛОЖЬ.

Получившаяся формула =СЧЁТЕСЛИ(E2:E1001;">0")

Теперь остаётся просто разделить одно значение на другое.

Построение графиков и диаграмм.

Для построения графиков и диаграмм в электронных таблицах надо воспользоваться специальным меню Вставка полем Диаграммы.

При построении диаграммы необходимо выбрать диапазон, по которому она будет составляться. Это можно сделать сразу: выделяется диапазон – затем используется вставка диаграммы или через «выбрать данные» в меню уже готовой диаграммы.

При построении диаграммы автоматически формируется легенда и название.

Для изменения любых из параметров отображения можно пользоваться меню, вызываемым через нажатие правой кнопкой мыши или конструктором.

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

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