Типы диаграмм

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

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

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

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

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

Круговая – строится только для одного ряда (одномерный массив значений вектор-строки или вектор-столбца), т.е. отображает относительное соотношения между частями целого.

Площадь круга S = pR² , поэтому радиусы отдельных кругов вычисляются как корень квадратный из значений изображаемых величин (). Круг принимается за целое (100%) и разбивается на сектора, дуги которых пропорциональны значениям отдельных частей изображаемых величин. Дуга каждого сектора круга рассчитывается по формуле , где 360⁰ – весь круг (100%), а d – величина изображаемого значения в процентах. Такие диаграммы называют секторными. Секторные диаграммы следует применять при отображении не более 4-5 категорий и при значительных различиях сравниваемых структур, иначе они теряют свою выразительность.

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

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

Кольцевая – является аналогом диаграммы Круговая, но для нескольких рядов данных.

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

Поверхность – отображает изменение значений по двум измерениям в виде поверхности.

Для построения диаграммы листе Excel необходимо выполнить последовательность действий:

1. Выделить диапазон ячеек, содержащих исходные данные.

2. Щелкнуть на кнопке Диаграмма (панель инструментов Стандартная) или выполнить команду Вставка->Диаграмма, а затем следуя инструкциям мастера диаграмм: выбрать тип и вид диаграммы, ввести название и т.д.

3. Для редактирования элементов диаграммы дважды щелкните на ней.

Массивы. Функции для работы с массивами и матрицами.

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

Два типа массивов Microsoft Excel:

– диапазон массива – непрерывный диапазон ячеек, использующих общую формулу (Рисунок 8);

Рисунок 8 – Формулы массива

– диапазон констант – набор констант, используемых в качестве аргументов функций (Рисунок 9).

Рисунок 9 – Функция массива

Массив констант может включать: числа (целые, с десятичной точкой или в экспоненциальном формате); текст (должен быть взят в двойные кавычки); логические значения (ИСТИНА, ЛОЖЬ или значения ошибок например, #Н/Д); элементы разного типа {1, 3, 4;ИСТИНА, ЛОЖЬ, ИСТИНА}.

Массив констант не может содержать: Формулы, знаки $ и %; скобки, ссылки на ячейки; столбцы или строки разной длины.

Формула массива обрабатывает несколько наборов значений (аргументов массива). Каждый аргумент массива должен включать одинаковое число строк и столбцов.

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

При вводе формулы массива MS Excel автоматически заключает ее в фигурные скобки. Привязка формул возвращающих массив производится следующим образом:

1. В командную строку вводится формула;

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

3. Для ввода формулы массива нажимается комбинация клавиш
SHIFT+ CTRL+ENTER

Пример умножение/деление массива на число (Рисунок 10)

1. Выделите диапазон ячеек того же размера;

2. Введите в первую ячейку диапазона формулу =Е1:G3*100 и нажмите SHIFT+ CTRL+ENTER

Рисунок 10 – Результат умножения массива на число

Если в формуле используется ссылка на ячейку в которой хранится число, то ссылка на эту ячейку должна быть абсолютной (Рисунок 22)

Рисунок 5 – Формула умножения массива на число с использованием ссылки

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

Для операций с массивами в имеется ряд встроенных функций: =СУММКВРАЗН(),=СУММПРОИЗВ(),=СУММРАЗНКВ(), СУММСУММКВ(), =СРГЕОМ() и др.

Например, функция =СРГЕОМ() (число1;число2; ...) – возвращает среднее геометрическое значений массива или интервала положительных чисел, используется для вычисления средних темпов роста, если задан составной доход с переменными ставками.

Функция массива СУММСУММКВ(массив_x;массив_y) – возвращает сумму сумм квадратов соответствующих элементов двух массивов (рисунок 11).

Рисунок 11 – Окно функции СУММСУММКВ()

Сумма сумм квадратов – термин, распространенный во многих статистических вычислениях

Массив_x – первый массив или интервал значений.

Массив_y – второй массив или интервал значений.

Рисунок 12 – Результат вычисления функции СУММСУММКВ()

Списки данных. Сортировка. Сводные таблицы Промежуточные итоги

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

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

Правила формирования списка

1. На листе размещать один список

2. Список должен быть отделен от других данных как минимум одной пустой строкой и одним пустым столбцом.

3. Список не должен содержать пустых строк и столбцов.

4. Каждый столбец списка должен содержать однотипные данные

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

6. К заголовку списка (первая строка, содержащая имена полей) рекомендуется применить форматирование.

7. Нельзя отделять заголовок от списка пустой строкой и использовать в качестве обрамления пунктирную линию.

Основные приемы работы со списками

1. Сортировка

2. Использование фильтров

3. Агрегирование данных: Промежуточные итоги; Сводная таблица; Консолидация; Структура данных.

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

Данные можно сортировать: по алфавиту, по числу, по дате.

Порядок сортировки может быть: возрастающим (от 1 до 9, от A до Z), убывающим (от 9 до 1, от Z до A.).

Фильтры

Фильтры могут быть использованы только для одного списка на листе. Чтобы отобрать только записи, соответствующие определенным условиям к списку можно применить команду меню Данные –>Автофильтр или Расширенный фильтр.

Расширенный фильтр применяется для фильтрации списка со сложными критериями отбора, включая вычисляемые критерии (с использование функций и формул)

Пример формирования критериев отбора информации из списка

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

2. Диапазон условий должен содержать, по крайней мере, 2 строки.

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

4. Выполните ->Данные->Фильтр->Расширенный фильтр.

5. В полях Исходный диапазон и Диапазон критериев укажите соответствующие блоки ячеек.

Промежуточные итоги

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

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

Тип итоговой операции выбирается с учетом типа данных

1. Поля числового типа – операции Сумма, Макс., Мин., кол-во чисел, значений, произведение, дисперсия и отклонение

2. Поля типа дата/время - операции Макс., Мин., Количество значений

3. Текстовые поля - операция Количество значений

Сводные таблицы

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

Источники формирования сводной таблицы:

1. Список Excel.

2. Внешний источник: текстовые файлы, содержащие табличные данные, реляционная БД.

3. Другие сводные таблицы Excel.

4. Массивы консолидированных данных .

Формирование сводной таблицы выполняется с помощью пошагового руководства – Мастер сводных таблиц.

Шаг 1. Выбор источника

– В списке или базе данных Microsoft Excel – если данные берутся с одного рабочего листа.

– Во внешнем источнике данных - если данные берутся из внешней базы данных.

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

– В другой сводной таблице – если сводная таблица создается на основе другой сводной таблицы.

– Шаг 2. Выбор диапазона данных

– Указать ссылку на диапазон

Шаг 3. Формирование макета сводной таблицы.

Поля базы данных, на основе которой строится сводная таблица, представлены в виде кнопок с названиями этих полей. Для формирования структуры кнопки перетаскивают в соответствующие области (Рисунок 13).

Рисунок 13 – Макет Сводной таблицы

Назначение структурных элементов макета сводной таблицы:

1. Страница – для размещения полей фильтрации (отбора) записей отображаемых в сводной таблице

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

3. Данные – для размещения полей итогов.

Правила построения сводной таблицы

1. Любое поле размещается в области группирования однократно (страница, строка, столбец).

2. В области Данные размещаются только те поля, которые не вошли в область группировки.

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

Число сводных таблиц, полей или столбцов и формул вычисляемых элементов на одном листе ограничивается объемом доступной оперативной памяти.

Консолидация данных

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

С помощью команды Консолидация можно объединять информацию с исходных листов (до 255) в одном итоговом. Исходные листы могут быть. расположены в одной книге с итоговым листом или в других книгах.

Способы консолидации данных:

1. По расположению–для одинаково организованных листов (фиксированное расположение).

2. По категориям–для различающиеся по расположению данных.

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

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