Инструменты анализа данных

§ 4. Инструменты анализа данных

Информатика. 11 класса. Босова Л.Л. Оглавление

4.1. Диаграммы

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

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

Табличные процессоры позволяют строить диаграммы следующих типов:

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

Чтобы в Microsoft Excel просмотреть все доступные типы диаграмм, изучите группу Диаграммы на вкладке Вставка (рис. 1.8).

Рис. 1.8. Типы диаграмм в Microsoft Excel

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

В диаграмме любого типа можно выделить следующие объекты (рис. 1.9):

1 — область диаграммы (в ней размещаются все объекты диаграммы);
2 — название диаграммы, чётко описывающее то, что представлено на диаграмме;
3 — область построения диаграммы (непосредственно в ней располагается сама диаграмма);
4 — ось значений (вертикальная, ось Y). На ней находится шкала с определённым шагом, устанавливаемым автоматически, в зависимости от наименьшего и наибольшего значений данных, изображённых на диаграмме. Именно по этой шкале можно оценить данные, представленные на диаграмме;
5 — ряды данных — наборы числовых данных, некоторым образом связанных между собой и размещённых в электронной таблице в одной строке или столбце. На диаграмме ряд данных изображается геометрическими фигурами одного вида и цвета;
6 — ось категорий (горизонтальная, ось X). На ней отображаются значения определённого свойства данных;
7 — легенда, поясняющая соответствие между названиями рядов и используемыми на диаграмме цветами. По умолчанию названия рядов являются названиями строк (или столбцов) диапазона данных, по которым построена диаграмма;
8 — названия осей.

Рис. 1.9. Основные элементы диаграммы

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

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

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

Вспомните основные приёмы построения диаграмм, известные вам из курса информатики основной школы.

Рассмотрим самые распространённые типы диаграмм.

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

Различают следующие виды гистограмм:

• гистограмма с группировкой;
• гистограмма с накоплением;
• нормированная гистограмма с накоплением;
• объёмная гистограмма.

В гистограмме с группировкой прямоугольники, которые являются графическими изображениями числовых данных из разных наборов, располагаются рядом друг с другом (см. рис. 1.9). В гистограмме с накоплением прямоугольники, изображающие числовые данные, располагаются друг над другом (рис. 1.10). Это даёт возможность оценить суммарные данные и вклад каждой составляющей в общую сумму.

В нормированной гистограмме с накоплением вертикальная ось имеет шкалу в процентах. Это даёт возможность оценить долю (процентную часть) данных в общей сумме (рис. 1.11).

Подумайте, по какой из трёх диаграмм проще всего определить:

1) продажа каких напитков неуклонно возрастала;
2) продажа каких напитков принесла наибольшую прибыль в июле;
3) динамику изменений суммарной выручки от продажи всех трёх напитков;
4) вклад от продажи каждого напитка в общую выручку.

Рис. 1.10. Пример гистограммы с накоплением

Рис. 1.11. Нормированная гистограмма с накоплением

Линейчатые диаграммы аналогичны гистограммам и отличаются от них лишь горизонтальным расположением геометрических фигур.

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

Круговые диаграммы позволяют отобразить только один ряд данных. Они теряют наглядность, если содержат много элементов данных. Несколько круговых диаграмм можно заменить, например, одной нормированной гистограммой с накоплением.

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

Сколько круговых диаграмм потребуется для того, чтобы изобразить информацию, представленную на гистограмме с накоплением (см. рис. 1.10)?

Диаграммы типа График целесообразно использовать, если количество данных в наборе достаточно большое, если нужно отобразить динамику изменения данных во времени, сравнить изменения нескольких рядов данных (рис. 1.12).

Рис. 1.12. Пример диаграммы График с маркерами

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

Пример. Найдём на отрезке [0; 1,2] корень уравнения

, построив в табличном процессоре графики функций, соответствующих левой и правой частям равенства.

Для этого:

1) используя стандартные функции COS и КОРЕНЬ, построим таблицу значений функций для х, изменяющегося с шагом 0,1:

Рис. 1.13. Графики функций

2) по значениям диапазона B2:N3 построим графики функций COS(x) и КОРЕНЬ(х) (рис. 1.13);

3) заменим номера точек, проставленные по горизонтальной оси, на значения аргумента х рассматриваемых функций. Для этого вызовем контекстное меню горизонтальной оси и выберем пункт Выбрать данные. Появится окно Выбор источника данных (рис. 1.14).

Рис. 1.14. Окно Выбор источника данных

В открывшемся окне нажмём на кнопку изменения подписей горизонтальной оси и выберем диапазон со значениями аргумента (рис. 1.15).

Рис. 1.15. Окно Подписи оси

После редактирования (совмещения) точки пересечения осей и добавления вертикальных линий сетки график приобретёт вид, представленный на рисунке 1.16.

Рис. 1.16. График после редактирования

В результате построения графиков функций видно, что корень уравнения приблизительно равен 0,64.

Построенную диаграмму можно редактировать:

• изменять способ формирования ряда данных: из данных строки или из данных столбца;
• изменять диапазон ячеек, по которым строится диаграмма;
• изменять тип, вид или макет диаграммы;
• вставлять, перемещать, удалять или изменять название диаграммы, легенды, подписей данных;
• изменять отображение осей и линий сетки и др. Построенную диаграмму можно форматировать. При этом можно применить стилевое форматирование сразу ко всей диаграмме, воспользовавшись одним из стилей оформления диаграмм. Кроме того, можно форматировать отдельные объекты диаграммы, которые предварительно надо выделить.

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

4.2. Сортировка данных

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

Сортировка — это упорядочение данных в таблице.

Сортировка данных может проводиться по возрастанию (от наименьшего к наибольшему) или по убыванию (от наибольшего к наименьшему). В Microsoft Excel соответствующие инструменты размещены на вкладке Данные в группе Сортировка и фильтр.

В Microsoft Excel сортировка данных по возрастанию заключается в следующем:

• символы упорядочиваются в порядке размещения их кодов в кодовой таблице Unicode;
• числа и даты упорядочиваются от наименьшего значения к наибольшему и располагаются перед текстовыми данными, причём сначала располагаются числа;
• текстовые данные сначала упорядочиваются по их первым символам; если первые символы в текстах совпали, то они упорядочиваются по вторым символам; тексты, в которых совпали первые два символа, упорядочиваются по их третьим символам и т. д.;
• логическое значение ЛОЖЬ размещается перед значением ИСТИНА;
• пустые ячейки всегда располагаются последними.

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

Далее представлены введённые данные (диапазон А1:А15) и результаты их сортировки. Определите, где представлены данные, отсортированные по возрастанию, где — по убыванию. Объясните полученные результаты.

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

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

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

В произвольном выделенном диапазоне ячеек отсортировать данные можно по значениям не одного, а нескольких столбцов (по нескольким уровням сортировки). Сортировка данных по значениям нескольких столбцов выполняется так:

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

Уровни такой сортировки и её порядок задаются в окне Сортировка (Данные ? Сортировка и фильтр ? Сортировка). На рисунке 1.17 приведены введённые данные и результат их сортировки по значениям двух столбцов, а соответствующие настройки окна Сортировка приведены на рисунке 1.18.

Рис. 1.17. Пример сортировки по значениям двух столбцов

Рис. 1.18. Фрагмент окна Сортировка

В окне Сортировка можно:

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

В этом же окне можно установить параметры сортировки (кнопка Параметры…): сортировать по столбцам (по умолчанию) или по строкам; сортировать без учёта регистра (по умолчанию) или с учётом регистра.

4.3. Фильтрация данных

Ещё одним инструментом эффективного анализа данных в электронных таблицах является фильтрация.

Фильтрация — это выбор в электронной таблице данных, соответствующих определённым условиям.

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

Если установить табличный курсор в произвольную ячейку заполненного данными диапазона (причём некоторые ячейки этого диапазона могут быть пустыми) и вызвать инструмент Фильтр (Данные ? Сортировка и фильтр ? Фильтр), то около правой границы каждой ячейки первой строки этого диапазона появятся кнопки открытия списков, в которых находятся:

• команды сортировки данных по значениям данного столбца;
• команда Фильтр по цвету;
• команда Снять фильтр с;
• команда открытия меню команд для установки условий фильтрации:
      — числовые фильтры (если в столбце числовые данные);
      — текстовые фильтры (если в столбце текстовые данные);
      — фильтры по дате (если в столбце даты).

Результаты выбора с помощью фильтрации из списка (см. рис. 1.17) всех учеников 11 класса представлены на рисунке 1.19.

Рис. 1.19. Пример фильтрации с помощью числового фильтра

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

4.4. Условное форматирование

Ещё одним способом выбора в таблице данных, удовлетворяющих определённым условиям, является так называемое условное форматирование.

Условное форматирование автоматически изменяет формат ячейки на заданный, если для значения в данной ячейке выполняется определённое условие.

Для установки условного форматирования необходимо:

1) выделить нужный диапазон ячеек;
2) вызвать инструмент Условное форматирование (Главная ? Стили ? Условное форматирование);
3) выбрать в списке кнопки Условное форматирование необходимый тип правил (Правила выделения ячеек, Правила отбора первых и последних значений, Гистограммы, Цветовые шкалы, Наборы значков);
4) в списке правил указанного типа выбрать нужное правило;
5) в открывшемся окне задать условие и выбрать из списка форматов тот, который будет установлен;
6) завершить операцию щелчком по кнопке ОК.

Результат применения операции условного форматирования к диапазону С2:С12 списка учеников (см. рис. 1.17) представлен на рисунке 1.20 (тип правил — Правила выделения ячеек; условие выбора — Больше 8; формат — Зелёная заливка и тёмно-зелёный текст (рис. 1.21)).

Рис. 1.20. Пример условного форматирования

Рис. 1.21. Пример условного форматирования

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

4.5. Подбор параметра

Если известны параметры (исходные данные) и формула, по которой они должны быть преобразованы, то пользователь вводит их в ячейки электронной таблицы и получает некоторый результат. В электронных таблицах есть и обратная возможность: подобрать такие параметры, которые при подстановке их в известную формулу будут приводить к желаемому заранее известному результату. В Microsoft Excel это можно сделать с помощью одной из функций специального инструмента Анализ «что-если».

Рассмотрим эту возможность на примере решения квадратного уравнения х² — 5х + 6 = 0.

Нам известна формула для вычислений (х² — 5х + 6) и желаемый результат (0). Внесём эту информацию в ячейки таблицы:

При подборе параметра в Microsoft Excel используется итерационный (циклический) процесс. Количество итераций и точность вычислений можно установить в окне Параметры Excel (Файл ? Параметры ? Формулы ? Параметры вычислений).

Вызовем окно подбора параметра (Данные ? Анализ «что-если» ? Подбор параметра) и заполним в нём поля ввода (рис. 1.22).

Рис. 1.22. Окно Подбор параметра

Ниже представлен результат подбора параметра:

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

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

САМОЕ ГЛАВНОЕ

Проводить анализ большого количества числовых данных значительно легче, если изобразить их графически (визуализировать). Для графического представления числовых данных используются диаграммы.

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

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

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

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

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

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