СОВРЕМЕННЫЕ ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ

61

столбцах. Можно также выполнять сортировку по настраиваемым спискам (таким, как состоящий из элементов «Большой», «Средний» и «Маленький») или по формату, включая цвет ячеек и цвет шрифта. Большинство сортировок применяются к столбцам, но возможно также применить сортировку к строкам.

Для проведения сортировки в таблице необходимо активизировать любую ячейку внутри таблицы и на вкладке Главная в группе Редактирование выбрать пункт Сортировка и фильтр, а затем в появившемся списке один из пунктов: Сортировка от А до Я, Сортировка от Я до А или Настраиваемая сортировка.

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

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

По умолчанию, при сортировке по возрастанию MS Excel 2007 использует следующий порядок (при сортировке по убыванию этот порядок заменяется на обратный):

	Значение		Примечания
	Числа		Числа сортируются от наименьшего отрицательного до наибольшего
			положительного числа.
	Даты		Даты сортируются от самой ранней даты до самой поздней.
	Текст		При сортировке алфавитно-цифрового текста MS Excel 2007 сравнива-
			ет посимвольные значения слева направо. Например, если ячейка со-
			держит текст «A100», программа поместит ее после ячейки, содержа-
			щей запись «A1», и перед ячейкой, содержащей запись «A11».
			Текст, в том числе содержащий числа в текстовом формате, сортиру-
			ется в следующем порядке:
			0 1 2 3 4 5 6 7 8 9 (пробел) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + <
			= > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z А Б В Г Д Е
			Ё Ж З И Й К Л М Н О П Р С Т У Ф Х Ц Ч Ш Щ Ъ Ы Ь Э Ю Я
			Апострофы (') и дефисы (-) игнорируются с единственным исключени-
			ем: если две строки текста одинаковы, не считая дефиса, текст с дефи-
			сом ставится в конец.
			Примечание. Если изменить порядок сортировки по умолчанию, введя
			учет регистра в диалоговом окне Параметры сортировки, то будет
			использоваться следующий порядок сортировки символов: a A b B c C
			d D e E f F g G h H i I j J k K l L m M n N o O p P q Q r R s S t T u U v V

		62
			w W x X y Y z Z
	Логические		Логическое значение ЛОЖЬ ставится перед значением ИСТИНА.
	значения
	Значения		Все значения ошибок, таких как #ЧИСЛО! и #ССЫЛКА!, равны.
	ошибок
	Пустые		При сортировке в любом порядке пустые ячейки всегда помещаются в
	ячейки		конец списка.
			Примечание. Пустыми называются незаполненные ячейки. Они от-
			личаются от ячеек, содержащих один или несколько пробелов.

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

Промежуточные итоги подсчитываются с помощью итоговой функции, такой как СУММ, СЧЕТ и СРЕДНЕЕ и другие. Следует иметь в виду, что подведение итогов производится при каждом изменении значений в определенном столбце.

При каждом изменении столбца «Спорт»… …вычисляется промежуточный итог столбца «Продажи».

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

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

63

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

промежуточный итог столбца «Продажи» для этой области.

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

Организации запросов к большим массивам данных дружественными по отношению к пользователю способами.

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

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

Перемещения строк в столбцы или столбцов в строки (или «сведение») для просмотра различных сводных данных по исходным данным.

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

Представления кратких, наглядных и аннотированных отчетов в сети или в напечатанном виде.

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

64

Исходные данные, в данном случае — из листа

Исходные значения для итога Кв3 Гольф в отчете сводной

таблицы

Отчет сводной таблицы целиком

Итог исходных данных в ячейках C2 и C8

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

Поле значений, например, поле суммарных объемов продаж, содержит суммируемые значения. Ячейка F3 в приведенном выше примере содержит сумму значений по столбцу «Продажи» для строк исходных данных, которые содержат в столбце «Спорт» значение «Гольф», а в столбце «Квартал» – значение «Кв3». По умолчанию для получения данных в области значений исходные данные в отчете сводной таблицы суммируются следующим образом: для числовых значений используется функция СУММ, а для текстовых – СЧЁТ.

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

лица группы Таблицы на вкладке Вставка.

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

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

Функции, которые можно использовать в качестве промежуточных итогов:

		65
	Функция		Описание
Сумма			Сумма чисел. Эта операция используется по умолчанию для
			подведения итогов по числовым полям.
	Количество		Число значений. Функция сложения Количество работает так
			же, как функция книги СЧЕТЗ. Количество – это функция по
			умолчанию для нечисловых данных.
Среднее			Среднее арифметическое значение.
	Максимум		Наибольшее значение
Минимум			Наименьшее значение
	Произведение		Произведение чисел.
Количество			Число значений, которые представляют собой числа. Функция
чисел			суммирования Количество чисел работает так же, как функция
			листа СЧЁТ.
	Смещенное		Оценка стандартного отклонения совокупности, где в качестве
	отклонение		примера используется выборка данных из совокупности.
Несмещенное			Оценка стандартного отклонения совокупности, где в качестве
отклонение			примера используются все данные совокупности.
	Смещенная		Оценка дисперсии совокупности, где в качестве примера исполь-
	дисперсия		зуется выборка данных из совокупности.
Несмещенная			Оценка дисперсии совокупности, где в качестве примера исполь-
дисперсия			зуются все данные совокупности.

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

Контрольные вопросы

1.Что такое функция в MS Excel 2007?

2.Перечислить различные способы вызова Мастера функций.

3.Перечислить различные категории функций.

4.Какого типа могут быть параметры функций?

5.Что такое таблица-список?

6.Как называются строка и ячейка в таблице-список?

7.Какие способы обработки данных можно выполнять только в таблице-список?

8.Что такое фильтрация данных? Какая команда позволяет ее выполнить?

66

9.Что такое сортировка данных? Какие виды сортировки можно использовать?

10.Что такое сводная таблица? Каким образом ее можно создать?

вернуться к оглавлению

перейти к практическому занятию

Лекция 10. Электронные таблицы Microsoft Excel 2007. Графическое отображение информации

С помощью приложения MS Excel 2007 можно легко создавать профессионально оформленные диаграммы. Чтобы создать базовую диаграмму, которую впоследствии можно изменять и форматировать, необходимо выделить данные, которые предполагается отобразить в виде диаграммы, и выбрать нужный тип диаграммы в группе

Диаграммы на вкладке Вставка.

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

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

После задания диапазона данных необходимо в группе Диаграммы на вкладке Вставка выполнить одно из следующих действий:

Выбрать вид диаграммы и затем ее подвид, который предполагается использовать.

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

Открыть диалоговое окно Диаграмма с помощью кнопки вызова диалогового окна рядом с надписью Диаграмма в одноименной группе вкладки Вставка.

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

67

Диаграмма обычно размещается на том же листе, где находятся исходные данные, и поэтому называется внедренной диаграммой. Если необходимо поместить диаграмму на отдельный лист, называемый листом диаграммы, можно изменить ее размещение при помощи команды Переместить диаграмму, расположенной в группе Расположение на вкладке Конструктор. Для того чтобы быстро создать диаграмму на основе типа диаграммы по умолчанию, можно выбрать данные, которые следует использовать для ее построения, и нажать клавиши ALT+F1 или F11. В первом случае диаграмма будет отображена как внедренная диаграмма; во втором – на отдельном листе диаграммы.

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

Пример построения диаграммы:

Данные листа

Диаграмма, созданная по данным листа

MS Excel 2007 позволяет представлять данные с помощью диаграмм разнообразных типов. При создании новой или изменении существующей диаграммы, можно выбрать один из многочисленных типов диаграмм (например, гистограмму или круговую диаграмму) и их разновидностей (например, гистограмму с накоплением или объемную круговую диаграмму). Совместив на одной диаграмме диаграммы разных типов, можно создать комбинированную диаграмму. Ниже приведен пример комбинированной диаграммы, в которой совмещены гистограмма и график.

68

Следует заметить, что диаграмму из приложения MS Excel 2007 можно копировать в MS PowerPoint 2007 или в MS Word 2007. При копировании диаграмма может быть встроена как статические данные, а может быть связана с книгой. Если диаграмма связана с книгой, к которой имеется доступ, можно задать ее автоматическое обновление, согласно данным связанной книги при каждом открытии диаграммы.

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

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

В MS Excel 2007 имеются следующие возможности форматирования диаграм-

мы:

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

для выделения элементов диаграммы можно изменить их цвет, стиль или толщину линий;

для придания диаграмме завершенности, к ее элементам можно применить специальные эффекты, например, тень, отражение, свечение, сглаживание, рельеф или объемное вращение;

69

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

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

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

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

– по вертикальной.

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

70

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

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

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

Линейчатые диаграммы рекомендуется использовать, если: метки осей имеют большую длину, выводимые значения представляют собой длительности.

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

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