Диаграммы MS Excel состоят из множества объектов. Каждый из объектов диаграммы можно выделять и изменять отдельно.
Основные объекты диаграммы:
- Область диаграммы
- Область построения диаграммы
- Ось – как правило, отображается на линейчатых диаграммах. Имеет шкалу для нанесения значений.
- Деление – деление шкалы вдоль соответствующей шкалы.
- Точка данных – один элемент данных, например квартальный объем продаж товаров определенного вида.
- Метка данных – численное значение точки данных.
- Ряд данных – совокупность точек данных. Например, поквартальный объем продажи товаров определенного вида в течение последних десяти лет.
- Легенда – условное обозначение для различных рядов данных: значок, узор или цвет. Метка легенды имеет то же название, что и соответствующий ряд данных.
Диаграммы могут быть двух типов: на отдельном листе и внедренные.
Внедренная диаграмма создается непосредственно на рабочем листе рядом с таблицами, данными и текстом.
Оба типа диаграмм связываются с данными рабочего листа; это обеспечивает их автоматическое перестроение при изменении соответствующих значений данных рабочего листа.
Использование Мастера диаграмм.
Мастер диаграмм руководит процессом создания диаграммы и позволяет просматривать ее на каждом шаге построения.
- Выделить нужные данные и названия;
- Стандартная панель инструментов à кнопка Мастер диаграмм
или: меню Вставка à команда Диаграмма;
- пройти 4 шага Мастера, устанавливая нужные параметры.
С п и с к и
Список – это набор упорядоченных данных.
Обычно список состоит из строк заголовков и строк данных.
Столбцы списка называют полями, а строки – записями.
Поля списка могут содержаться разные типы данных (текст; даты; числа; формулы).
Размер списка ограничен размерами одного рабочего листа, т.е. список может иметь не больше 256 полей и не больше 65 535 записей.
Список иногда называют табличной базой данных.
- Верхняя строка списка должна содержать заголовки, каждый из которых описывает содержимое расположенного ниже столбца.
- Каждый столбец должен содержать данные одного типа (Например, не следует смешивать в одном столбце даты и обычный текст).
- Список лучше располагать на отдельном листе. Если это невозможно, то список должен быть отделен от других данных хотя бы одной пустой строкой и одним пустым столбцом.
- Не включайте в список пустых строк и пустых столбцов. При проведении операций над списками Excel определяет его границы автоматически, при этом пустая строка и пустой столбец означает конец списка.
- Можно использовать формулы, использующие значения из других полей этой же записи. Если формула ссылается на ячейку, расположенную вне списка, сделайте ссылку на эту ячейку абсолютной.
- Если список имеет много записей, используйте команду Закрепить области из меню Окно, чтобы заголовки списка были видны при прокручивании листа списка.
Сортировка списка
Для сортировки списков предусмотрено несколько возможностей:
Сортировка списка по одному полю.
- Сделать активной любую ячейку нужного столбца;
- В зависимости от направления сортировки на Стандартной панели инструментов выбрать команду Сортировка по возрастанию или Сортировка по убыванию.
Сортировка списка по двум – трем полям
- В менюДанные выбрать команду Сортировка
- В раскрывающихся списках диалогового окна Сортировка диапазона выбрать нужные поля и указать порядок сортировки.
Сортировка списка по четырем и более полям
Предположим надо произвести сортировку по пяти полям: Поле1, Поле2, Поле3, Поле4 и Поле5. Вначале надо отсортировать список по полям Поле3, Поле4 и Поле5, а затем еще раз – по полям Поле1 и Поле2. Т.е. в начале надо отсортировать менее важные поля, тогда они сохранят свой порядок при выполнении второй сортировки.
Автофильтр
Для установки Автофильтра необходимо активизировать любую ячейку списка и в строке меню выбрать:
Данные à Фильтр à Автофильтр
Для отмены автофильтра надо повторно выбрать:
Данные à Фильтр à Автофильтр
После запуска Автофильтра в строку заголовка полей добавятся кнопки раскрывающихся списков. Если выбрать какой-либо элемент списка, то Excel спрячет все строки, кроме тех, которые включают отмеченный элемент.
Фильтрацию можно осуществлять по нескольким полям. Для этого необходимо отфильтровать данные по одному полю, затем указать критерии отбора для другого поля и т.д.
Применение фильтра по следующему полю не отменяет фильтрацию по предыдущим полям.
Кроме всех элементов столбца раскрывающийся список содержит еще пять пунктов:
1. Все – отображает все элементы столбца. Используется для отмены фильтрации столбца.
2. Первые 10 . . . – отображает первые 10 (или менее / более) наибольших или наименьших значений в поле. Можно также выбрать опцию «элементов списка» или «% от количества элементов». Например, можно отобрать 5% наименьших записей.
3. Условие – позволяет фильтровать список по нескольким условиям. Вызывает Пользовательский автофильтр для установки различных критериев отбора по одному полю.
4. Пустые – отображает только строки с пустыми ячейками в данном столбце.
5. Непустые – показывает только строки с непустыми ячейками в данном столбце.
Расширенный фильтр
Перед использованием расширенного фильтра необходимо задать диапазон критериев и в строке меню выбрать:
Данные à Фильтр à Расширенный фильтр
В появившемся диалоговом окне надо указать исходный диапазон, диапазон критериев и, если результат будет отображаться в другом месте, диапазон в который будет помещен результат.
К диапазону критериев предъявляются следующие требования:
1. Он должен состоять минимум из двух строк:
- в 1-й строке помещаются названия всех или части полей
- в остальных строках указываются критерии фильтрации
(не должно быть пустых строк).
2. Диапазон критериев должен отделяться от списка как минимум одной пустой строкой или одним пустым столбцом.
Типы критериев:
1. Числовые ( > < >= <= = <> ) Названия столбцов критериев должны совпадать с названиями столбцов списка.
2. Текстовые (например: >К <>В *К* С* л??? ) Названия столбцов критериев должны совпадать с названиями столбцов списка.
>К - слова, начинающиеся с буквы Л до Я
<>В - все слова, кроме начинающихся с буквы В
С* - слова, начинающиеся с буквы С4
*К* - слова, содержащие букву К4
Л??? - слова из четырех букв, начинающиеся с буквы Л4
=“Январь” - все слова «Январь».
3. Вычисляемые Записываются формулы. Результат должен иметь логическое значение ИСТИНА или ЛОЖЬ. Названия столбцов для критериев не должны совпадать с названиями столбцов списка.
При создании диапазона критериев можно использовать следующие способы задания логических Ии ИЛИ
Логическое И à
Логическое ИЛИ à
Пример: отобразить заказы, выполненные в апреле 1998 г.
или:
Пример: отобразить заказы, выполненные в апреле и ноябре 1998 г.
или:
или:
Промежуточные итоги
В Excel предусмотрена возможность подведения итогов по группам записей.
Для подведения итогов необходимо активизировать любую ячейку списка и в строке меню выбрать:
Данные à Итоги
В появившемся диалоговом окне надо указать:
- поле, при каждом изменении значений в котором надо подводить итоги (предварительно список должен быть отсортирован по этому полю);
- операцию, используемую для подсчета итогов; для этого Excel предоставляет 11 функций (по умолчанию используется функция СУММ);
- поля, в которых надо подвести промежуточные итоги.
Для отмены промежуточных итогов в строке меню необходимо выбрать
Данные à Итоги à Убрать все
Сводные таблицы
В сводной таблице операции сортировки, фильтрации, получения промежуточных итогов выполняются одновременно.
Перед построением сводной таблицы необходимо убрать все ранее созданные промежуточные итоги и наложенные фильтры.
Создание сводных таблиц осуществляется с помощью Мастера сводных таблиц.
Для подведения итогов необходимо активизировать любую ячейку списка, в строке меню выбрать
Данные à Сводная таблица
и пройти три шага Мастера сводных таблиц.
1-й шаг:
Необходимо ответить на вопрос «от куда берутся данные?»
- в списке или базе данных Microsoft Excel;
- во внешнем источнике данных;
- в нескольких диапазонах консолидации.
Надо также указать вид создаваемого отчета:
- сводная таблица;
- сводная диаграмма (со сводной таблицей).
2-й шаг:
Необходимо указать диапазон, содержащий исходные данные.
Если ранее была выделена какая-то ячейка списка, то диапазон автоматически охватит весь список.
3-й шаг:
Надо указать куда поместить таблицу:
- на новый лист;
- в существующий лист.
После прохождения трех шагов Мастера на экране отобразится пустая заготовка сводной таблицы и панель инструментов сводной таблицы с элементами полейисходных данных:
Идея построения сводной таблицы проста.
С помощью мыши надо поместить элементы исходных данных в нужное место заготовки сводной таблицы.
При этом:
- Элементы, помещаемые в зону Страница, становятся фильтрами. Они отображаются в стиле Автофильтра.
- Элементы, помещаемые в зону Строка, становятся метками строк.
- Любой элемент, помещенный в зону Столбец, становится меткой столбца.
- Элементы, помещенные в зону Данные, становятся объектами подведения итогов.
После создания сводной таблицы можно изменять ее формат, переименовывать поля или элементы, добавлять новые строки или столбцы, делать другие преобразования с ее внешним видом
- Изменение структуры сводной таблицы надо перетащить нужное поле сводной таблицы в нужную область данных;
- Удаление сток, столбцов и страниц надо перетащить удаляемое поле за пределы сводной таблицы;
- Добавление новых строк, столбцов и страниц надо перетащить нужное поле исходной таблицы с панели инструментовСводные таблицы в нужную область таблицы
- Группировка элементов сводной таблицы выделить нужные элементы сводной таблицы, вызвать контекстное меню, выбрать команду Группаи структура, а затем - Группировать, указать диапазон группировки.
Логическое И à
Логическое ИЛИ à
