Построение диаграмм

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

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

Создание диаграммы

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

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

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

Изменение подписей, заголовков и других текстов диаграммы

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

Изменение имен рядов данных или текста легенды

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

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

Чтобы изменить текст легенды или имя ряда данных на диаграмме, выберите нужную диаграмму, а затем выберите команду Диаграмма – Исходные данные. На вкладке Ряды выберите изменяемые имена рядов данных. В поле Имя укажите ячейку листа, которую следует использовать как легенду или имя ряда. Также можно просто ввести нужное имя. Если в поле Имя ввести имя, то текст легенды или имя ряда потеряют связь с ячейкой листа.

Изменение подписей значений

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

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

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

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

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

2. Формат – Ряды – вкладка Ось.

3. Установите переключатель в положение По вспомогательной оси.

Изменение типа диаграммы

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

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

Диаграмма – Тип диаграммы – на вкладках Стандартные или Нестандартные выберите необходимый тип.

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

Изменение цвета, узора, линий и рамок на диаграмме

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

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

2. При необходимости выберите вкладку Узор и укажите нужные параметры.

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

Списки

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

Размер списка ограничен размерами одного рабочего листа, т.е. список может иметь не более 256 полей и не более 65 535 записей. Полями принято называть столбцы списка, а записями – строки.

Excel будет считать таблицу списком, если ее формат удовлетворяет следующим условиям.

• Список обязательно должен содержать строку заголовков.

• В каждом столбце должна содержаться однотипная информация. Например, не следует смешивать в одном столбце даты и обычный текст.

• В списке не должно быть пустых строк.

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

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

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

Excel обладает мощными средствами для работы со списками. Это:

• пополнение списка с помощью формы;

• фильтрация списка;

• сортировка списка;

• подведение промежуточных итогов;

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

Ввод данных

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

Если вы выбрали первый способ, не забывайте пользоваться командой контекстного меню Выбратьиз списка, Excel избавит от необходимости много раз набирать один и тот же текст.

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

Индикатор в правом верхнем углу формы показывает номер выбранной записи и общее число записей в форме.

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

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

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

Фильтрация списков

В Excel существует два типа фильтров: Автофильтр и Расширенный фильтр.

Перед тем как использовать Автофильтр, выделите любую ячейку списка. Затем выберите команду Данные – Фильтр – Автофильтр. При включении Автофильтра возле имен полей списка появляются кнопки со стрелками.

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

Рис. 9.9. Пример использования Автофильтра

Обратите внимание на цвет стрелок на кнопках Автофильтра: если Автофильтр включен, кнопки окрашиваются в синий цвет.

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

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

Кроме команды Все, в раскрывающемся меню кнопок Автофильтра есть еще одна полезная команда Первые 10..., которая используется для полей числового типа или дат. Эта команда покажет «горячую десятку» вашего списка.

Пусть необходимо узнать расходы за последние три дня. Щелкните по кнопке Автофильтра в столбце Дата, выберите в раскрывшемся меню команду Первые 10..., в диалоговом окне сделайте установки как на следующем рисунке.

В окне «Наложение условия по списку» можно установить любое количество наибольших (или наименьших) элементов, которое вы хотите отобразить. Если вы хотите оставить процент записей (например, 10% наименьших значений), в третьем окне вместо «элементов списка» установите «% от количества элементов». При создании сложного условия отбора команда Первые 10... всегда применяется ко всему списку.

Расширенный фильтр

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

С помощью расширенного фильтра можно:

• Определить более сложный критерий фильтрации.

• Помещать результат отбора данных на другое место и даже на новый лист рабочей книги.

• Устанавливать вычисляемый критерий отбора.

Рис. 9.10. Расширенный фильтр

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

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

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

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

Область ячеек, содержащих критерии, должна отделяться oт списка, по крайней мере, одной пустой строкой.

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

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

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

Если отфильтрованный список должен быть помещен на другой лист рабочей книги, сначала перейдите на этот лист и только потом обращайтесь к команде Данные – Фильтр – Расширенный фильтр.

Сортировка

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

Изменение порядка строк в списке называется сортировкой.

Чтобы отсортировать список:

• поместите курсор в тот столбец таблицы, по которому нужно выполнить сортировку;

• затем щелкните по кнопке Сортировка по возрастанию или Сортировка по убыванию.

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

Сортировка отфильтрованного списка выполняется только для видимых строк.

Если необходимо выполнить сортировку по двум или нескольким столбцам:

• выберите команду Данные – Сортировка;

• в появившемся окне «Сортировка диапазона» в поле Сортировать по выберите название столбца и укажите порядок сортировки;

• затем сделайте то же самое для второго и третьего поля;

• нажмите ОК.

Упорядочивание текстовых строк чаще всего выполняют по алфавиту. Но это не всегда удобно. Для того чтобы установить особый порядок сортировки, например по дням недели или месяцам, выберите команду Данные – Сортировка. Затем нажмите кнопку Параметры. В открывшемся диалоговом окне Параметры сортировки из раскрывающегося списка «Сортировка по первому ключу» выберите полные названия месяцев. Теперь при сортировке по полю «Год» и (или) «Месяц» названия месяцев будут располагаться в привычном порядке.

Создание промежуточных итогов

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

Для получения промежуточных итогов выполните ряд действий:

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

• Выберите команду Данные – Итоги.

• В диалоговом окне «Промежуточные итоги» установите критерии, функцию и другие параметры, которые будут использованы при расчете итогов.

Рис. 9.11. Промежуточные итоги

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

В раскрывающемся списке Операция на выбор даются 11 функций, которые могут быть использованы для расчета итогов. Чате всего используются Сумма и Кол-во значений.

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

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

Для того чтобы удалить все рассчитанные промежуточные итоги, выберите команду Данные – Итоги и нажмите кнопку Убрать все.