МУ-лаб-Информ_№1-6-готов

80

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

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

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

Каждая формула должна начинаться со знака равенства! В адресах ячеек и диапазонов используются латинские символы!

Применение операторов в формулах. В Microsoft Excel включено четыре вида операторов: арифметические, текстовые, а также операторы сравнения и адресные операторы.

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

Арифметический	Значение	Пример использования
оператор
+ (знак плюс)	Сложение	=A1+A2
– (знак минус)	Вычитание	=A1­A2
* (звездочка)	Умножение	=A1*A2
/ (косая черта)	Деление	=A1/A2
^ (крышка)	Возведение в степень	=A1^3

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

Оператор сравнения	Значение	Пример
		использования
= (знак равенства)	Равно	A1=B1
> (знак больше)	Больше	A1>B1

81

< (знак меньше)	Меньше		A1<B1
>= (знак больше и знак равенства)	Больше или равно		A1>=B1
<= (знак меньше и знак равенства)	Меньше	или	A1<=B1
	равно
<> (знак больше и знак меньше)	Не равно		A1<>B1

Адресные операт оры объединяют диапазоны ячеек для осуществления вычислений.

Адресный	Значение			Пример использования
оператор
: (двоеточие)	Оператор диапазона, который ссылается			B5:B15
	на все ячейки между границами
	диапазона включительно
, (запятая)	Оператор	объединения,	который	СУММ(B5:B15,D5:D15)
	ссылается	на объединение	ячеек
	диапазонов.
(пробел)	Оператор	пересечения,	который	СУММ(B5:B15 A7:D7) (В
	ссылается на общие ячейки диапазонов.			этом примере ячейка B7
				является общей для двух
				диапазонов)

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

Порядок	Оператор		Описание
1	:	(двоеточие),	(запятая) Операторы ссылок
	(пробел)
2	–		Унарный минус (например –1)
3	%		Процент
4	^		Возведение в степень
5	* и /		Умножение и деление
6	+ и –		Сложение и вычитание
7	&		Объединение последовательностей
			символов в одну последовательность
8	= < > <= >= <>		Операторы сравнения

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

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

1.Щелкнуть ячейку, в которую необходимо ввести формулу

2.Набрать знак равенства (=)

3.Набрать формулу. Она появится в строке формул.

4.Нажать Enter или щелкнуть «галочку» в строке формул. Excel вычислит результат. Копирование формул. Если необходимо скопировать формулу в диапазон смежных ячеек, то

надо сделать следующее:

1.Щелкнуть ячейку, которую надо скопировать.

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

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

82

От носит ельные ссылки изменяются при копировании формулы в другое место.

Функции. Мастер Функций. Функции ­ это стандартные формулы, которые обеспечивают выполнение определенного набора операций над какими­либо данными. Например, для определения суммы величин в ячейках от A1 до H1 можно задать функцию =СУММ(A1:H1) вместо формулы =A1+B1+C1+…

Каждая функция состоит из следующих 3­х элементов:

1.Знака равенства;

2.Названия функции;

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

разделителя списков Windows. (Для русского языка это обычно точка с запятой).

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

1.Щелкнуть ячейку, в которую необходимо вставить функцию.

2.Нажать кнопку «Мастер функций» на стандартной панели инструментов.

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

4.На экране появится 2­е окно мастера функций. В этом окне размещены поля для ввода аргументов выбранной функции. Для перемещения по полям аргументов можно использовать кл. Tab либо щелкать мышью по соответствующему полю. Если значение аргумента находится в ячейках рабочего листа, то можно просто провести мышью по необходимому диапазону ячеек и адреса ячеек появятся автоматически в соответствующем поле. (Если окно мастера функций закрывает необходимые ячейки, то можно использовать кнопки свертывания). Заметим, что при перемещении по полям ввода аргументов в нижней части окна можно увидеть краткую подсказку о возможных значениях аргумента. Если по мере ввода аргументов Excel уже в состоянии подсчитать конечный результат, то его можно увидеть в правой нижней части окна.

5.Нажать Ok для ввода функции в ячейку.

Автосуммирование и автовычисления. Так как одной из наиболее часто употребляющихся функций является СУММ, в Excel предусмотрен быстрый способ ее ввода:

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

2.Щелкнуть кнопку «Автосумма» на стандартной панели инструментов. В выделенную ячейку будет введена функция СУММ и адрес диапазона левее или выше ячейки.

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

4.Нажать клавишу Enter либо щелкнуть кнопку Enter в строке формул. Финансовые функции. Методы финансовой математики делятся на:

Базовые				Прикладные
1.	Простые	и	сложные проценты	1.	Планирование и оценка эффективности
(наращение		и	диконтирование	финансово­кредитных операций
платежей)
2.	Расчет	последовательностей		2.	Планирование погашения долгосрочной
(потоков) платежей (финансовые				задолженности
ренты)				3.	Финансовые расчеты по ценным бумагам
				4.	Планирование и анализ инвестиций
				……………………………………………….

83

Excel содержит большой набор различных финансовых функций. Для их глубокого изучения можно порекомендовать книгу Овчаренко Е.К., Ильина О.П., Балыбердин Е.В., «Финансово­экономические расчеты в EXCEL», Москва, ИИД «Филинъ», 1999 г. Мы познакомися с некоторыми из финансовых функций и основными приемами решения задач с их применением.

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

Логические функции. Логические функции предназначены для проверки выполнения условия или для проверки нескольких условий. Так, функция ЕСЛИ позволяет определить, выполняется ли указанное условие, и возвращает одно значение если условие истинно, и другое — если оно ложно.

К логическим функциям относятся ЕСЛИ, И, ИЛИ, НЕ. Функция ЕСЛИ.

Возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.

Функция ЕСЛИ используется для условной проверки значений и формул. Синт аксис:

ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь) Лог_выражение ­ это любое значение или выражение, которое при вычислении дает значение

ИСТИНА или ЛОЖЬ.

Значение_если_истина ­ это значение, которое возвращается, если лог_выражение имеет значение ИСТИНА. Если лог_выражение имеет значение ИСТИНА и значение_если_истина опущено, то возвращается значение ИСТИНА. Значение_если_истина может быть другой формулой.

Значение_если_ложь ­ это значение, которое возвращается, если лог_выражение имеет значение ЛОЖЬ. Если лог_выражение имеет значение ЛОЖЬ и значение_если_ложь опущено, то возвращается значение ЛОЖЬ. Значение_если_ложь может быть другой формулой.

Замечание:

До 7 функций ЕСЛИ могут быть вложены друг в друга в качестве значений аргументов значение_если_истина и значение_если_ложь, чтобы конструировать более сложные проверки, см. последний из приведенных ниже примеров.

В следующем примере, если значение ячейки A10 = 100, то лог_выражение имеет значение ИСТИНА и вычисляется сумма для ячеек B5:B15. В противном случае лог_выражение имеет значение ЛОЖЬ и возвращается пустой текст (""), очищающий ячейку, которая содержит функцию ЕСЛИ:

ЕСЛИ(A10=100;СУММ(B5:B15);"")

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

СреднийБалл	Кат егория
Больше 89	A
От 80 до 89	B
От 70 до 79	C
От 60 до 69	D
Меньше 60	F

Тогда можно использовать вложенные функции ЕСЛИ: ЕСЛИ(СреднийБалл>89;"A";ЕСЛИ(СреднийБалл>79;"B"; ЕСЛИ(СреднийБалл>69;"C";ЕСЛИ(СреднийБалл>59;"D";"F"))))

84

В этом примере второе предложение ЕСЛИ является в то же время аргументом значение_если_ложь для первого предложения ЕСЛИ. Аналогично, третье предложение ЕСЛИ является аргументом значение_если_ложь для второго предложения ЕСЛИ. Например, если первое лог_выражение (Среднее>89) имеет значение ИСТИНА, то возвращается значение "A". Если первое лог_выражение имеет значение ЛОЖЬ, то вычисляется второе предложение ЕСЛИ и так далее.

Функция И.

Возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.

Синт аксис:

И(логическое_значение1; логическое_значение2; ...) Логическое_значение1, логическое_значение2, ... ­ это от 1 до 30 проверяемых условий,

которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.

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

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

Если указанный интервал не содержит логических значений, то И возвращает значение ошибки #ЗНАЧ!.

Примеры.

И(ИСТИНА; ИСТИНА) равняется ИСТИНА И(ИСТИНА; ЛОЖЬ) равняется ЛОЖЬ И(2+2=4; 2+3=5) равняется ИСТИНА

Если интервал B1:B3 содержит значения ИСТИНА, ЛОЖЬ, и ИСТИНА, то: И(B1:B3) равняется ЛОЖЬ

Если ячейка B4 содержит число между 1 и 100, то: И(1<B4; B4<100) равняется ИСТИНА

Предположим, что нужно вывести на экран содержимое ячейки B4, если она содержит число строго между 1 и 100 и сообщение "Значение вне интервала" в противном случае. Тогда, если ячейка B4 содержит число 104, то выражение:

ЕСЛИ(И(1<B4; B4<100); B4; "Значение вне интервала") равняется "Значение вне интервала", а если ячейка B4 содержит число 50, то выражение:

ЕСЛИ(И(1<B4; B4<100); B4; "Значение вне интервала") равняется 50 Функция ИЛИ.

Возвращает ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.

Синт аксис:

ИЛИ(логическое_значение1;логическое_значение2; ...) Логическое_значение1, логическое_значение2, ... ­ это от 1 до 30 проверяемых условий,

которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ. Примеры:

ИЛИ(ИСТИНА) равняется ИСТИНА ИЛИ(1+1=1;2+2=5) равняется ЛОЖЬ

Если ячейки A1:A3 содержат значения ИСТИНА, ЛОЖЬ и ИСТИНА, то: ИЛИ(A1:A3) равняется ИСТИНА

Функция НЕ.

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

Синт аксис:

НЕ(логическое_значение)

Логическое_значение ­ это значение или выражение, которое при вычислении дает ИСТИНА или ЛОЖЬ. Если логическое_значение имеет значение ЛОЖЬ, то функция НЕ возвращает значение ИСТИНА; Если логическое_значение имеет значение ИСТИНА, то функция НЕ возвращает значение ЛОЖЬ.

Примеры:

НЕ(ЛОЖЬ) равняется ИСТИНА

85

НЕ(1+1=2) равняется ЛОЖЬ Базы данных. База данных ­ это средство, использующееся для хранения, организации и

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

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

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

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

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

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

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

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

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

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

Не следует помещать пустую строку между заголовками и первой строкой данных. Желательно также придерживаться следующих советов:

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

фильтра они могут оказаться скрытыми.

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

При сортировке текста, значения сравниваются посимвольно слева направо. Например, если в ячейке содержится текст «A100», то после сортировки она будет находиться после ячейки, содержащей «A1» и перед ячейкой, содержащей «A11». При сортировке по возрастанию в Microsoft Excel используется следующий порядок (при сортировке по убыванию этот порядок заменяется на обратный за исключением пустых ячеек, которые всегда помещаются в конец списка):

­Числа сортируются от наименьшего отрицательного до наибольшего положительного числа.

­Текст, в том числе содержащий числа, сортируется в следующем порядке:

­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 А Б В Г Д Е Ё Ж З И Й К Л М Н О П Р С Т У Ф Х Ц Ч Ш Щ Ъ Ы Ь Э Ю Я

­Логическое значение ЛОЖЬ предшествует значению ИСТИНА.

­Все ошибочные значения равны.

­Пустые ячейки всегда помещаются в конец списка.

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

Строки, столбцы или отдельные ячейки в процессе сортировки переупорядочиваются в соответствии с заданным пользователем порядком сортировки. Списки можно сортировать в возрастающем (от 1 до 9, от А до Я) или убывающем (от 9 до 1, от Я до А) порядке.

86

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

Для сортировки списков в одном столбце следует использовать кнопки «По возрастанию» и «По убыванию».

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

1.Указать любую ячейку в сортируемом списке.

2.Выбрать Данные, Сортировка.

3.Указать столбцы сортировки в полях Сортировать по и Затем по.

4.Чтобы отсортировать данные по более, чем трем столбцам одновременно, отсортируйте список сначала по трем наименее значимым столбцам. Например, если список содержит сведения о сотрудниках и его требуется отсортировать по полям «Отдел», «Должность», «Фамилия», «Имя» и «Отчество», выберите сначала «Имя» в поле Сортировать по, «Отчество» в поле Затем по и отсортируйте список. Затем выберите «Отдел» в поле Сортировать по, «Должность» в поле Затем по, «Фамилия» в поле В последнюю очередь, по и отсортируйте список.

5.Выбрать другие параметры сортировки и нажать кнопку OK.

6.Повторить шаги 2 ­ 4 для следующих более значимых столбцов.

Автофильтр. Для применения автофильтра необходимо:

1.Щелкнуть любую ячейку внутри списка.

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

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

4.Выбрать значение в списке.

5.Повторить шаги 3 и 4, чтобы ввести дополнительные ограничения для значений в других

столбцах.

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

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

Вправом верхнем поле ввода ввести соответствующее значение.

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

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

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

1.Скопируйте из списка заголовки фильтруемых столбцов.

2.Вставьте скопированные заголовки столбцов в первой пустой строке диапазона условий отбора.

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

4.Укажите ячейку в списке.

5.Выберите пункт Фильтр в меню Данные, а затем — команду Расширенный фильтр.

6.Чтобы показать результат фильтрации, скрыв ненужные строки, установите переключатель Обработка в положение Фильтровать список на месте.

87

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

8.Введите в поле Диапазон критериев ссылку на диапазон условий отбора, включающий заголовки столбцов.

Чтобы убрать диалоговое окно Расширенный фильтр на время выделения диапазона условий

Продавец Месяц		Продукция Доход		Регион
Белов	март	Напитки	4300	Южный
Батурин	март	Продукты	1252	Северный
Батурин	апрель	Продукты	1862	Южный
Белов	май	Напитки	3887	Южный
Белов	март	Напитки	4195	Северный
Батурин	март	Продукты	3023	Северный
Батурин	апрель	Напитки	3231	Южный
Белов	май	Продукты	3161	Северный
Белов	март	Продукты	2192	Южный
Батурин	март	Напитки	4813	Южный
Батурин	апрель	Напитки	3781	Северный
Белов	май	Продукты	4653	Северный
Белов	март	Напитки	2346	Южный
Батурин	март	Продукты	1137	Северный
Батурин	апрель	Продукты	1718	Северный
Белов	май	Напитки	2440	Южный
Белов	март	Продукты	1260	Южный
Батурин	март	Продукты	4362	Северный
Батурин	апрель	Напитки	1582	Северный
Белов	май	Продукты	2864	Южный

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

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

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

созданные в Microsoft Access)

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

Сводная таблица создается с помощью мастера сводных таблиц, используемого для размещения и объединения анализируемых данных. Чтобы начать создание сводной таблицы, надо выбрать команду «Сводная таблица» в меню «Данные». (Если сводная таблица создается на основе списка на листе Excel, то предварительно можно отметить любую ячейку внутри списка. Это позволит Excel автоматически определить область, которую занимает список)

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

Пример. Внесем на рабочий лист Excel следующие данные (см таблицу). После этого отметим любую ячейку внутри базы данных и вызовем Данные, Сводная таблица. На экране появится окно мастера сводных таблиц. Мастер сводных таблиц работает в 4 шага:

На 1­м шаге выбираем источник данных для сводной таблицы. Это будет список или база данных Excel.

На 2­м шаге указываем диапазон, содержащий исходные данные. Если перед вызовом мастера сводных таблиц мы отметили ячейку внутри списка, то этот диапазон установится автоматически.

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

88

Перетащим поле «Регион» в область страницы, поля «Месяц» и «Продавец» в область строки, поле «Продукция» в область «Столбец», поле «Доход» в область «Данные». Заметим, что в данный момент можно дважды щелкнуть по любой кнопке с наименованием поля и установить какие­либо дополнительные параметры.

На 4 ­ м шаге выбираем, поместить ли таблицу в какой­либо имеющийся в данной книге лист, либо поместить ее в новый лист. В итоге мы получим следующую сводную таблицу:

Регион	(Все)
Сумма по полю Доход		Продукция
Месяц	Продавец	Напитки		Продукты	Общий итог
март	Батурин	4813		9774	14587
	Белов	10841		3452	14293
март Всего		15654		13226	28880
апрель	Батурин	8594		3580	12174
апрель Всего		8594		3580	12174
май	Белов	6327		10678	17005
май Всего		6327		10678	17005
Общий итог		30575		27484	58059

Части сводной таблицы.

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

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

Поля ст роки – это поля исходного списка или таблицы, помещенные в область строчной ориентации сводной таблицы. В нашем примере «Месяц» и «Продавец» являются полями строки. Внешние поля строки группируют внутренние. В нашем примере поле «Месяц» группирует поле «Продавец».

Элемент ы поля – это подкатегории поля сводной таблицы. В нашем примере «Март», «Апрель» и «Май» являются элементами поля в поле «Месяц». Элементы поля представляют собой записи в поле или столбце исходных данных. Элементы поля появляются в виде заголовков строк и столбцов, а также в раскрывающемся списке полей страниц.

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

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

89

«Продукты». Если полей столбца будет несколько, то внешние поля будут располагаться выше внутренних.

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

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

На этой панели располагаются следующие кнопки:

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

Кнопка «Мастер сводных таблиц» ­ вызов мастера Кнопка «Поле сводной таблицы» – в зависимости от того, какая ячейка является текущей в

сводной таблице, будут изменены частичные суммы, значения вычисляемых полей или свойства области данных.

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

Кнопка «Разгруппировать» ­ разделяет группы на отдельные элементы, например, квартал на отдельные даты.

Кнопка «Группировать» – группирует элементы по категориям для сведения нескольких элементов в один. Например, можно сгруппировать дни, недели или месяцы в кварталы для анализа, построения диаграмм и печати поквартальных результатов.

Кнопка «Скрыть детали» – скрытие подчиненных данных внешнего элемента поля строки или столбца в сводной таблице.

Кнопка «Отобразить детали» – отображение данных в скрытых строках или столбцах сводной таблицы.

Кнопка «Обновить данные» ­ обновление данных в сводной таблице, если произошли изменения в источнике данных.

Кнопка «Заголовки» ­ выделение только заголовка при выборе поля или заголовка в сводной таблице. Сами поля и связанные с ними данные при этом не выделяются.

Кнопка «Данные» ­ выделение только связанных данных при выборе поля или заголовка в сводной таблице. Сами поля и заголовки при этом не выделяются.

Кнопка «Заголовки и данные» ­ выделение как связанных данных, так и заголовка при выборе поля или заголовка в сводной таблице.

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

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

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

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

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

1.На панели инструментов Сводные таблицы выберать команду Выделить в меню Сводная таблица. Проверить, что кнопка Разрешить выделение не нажата.

2.Удалить любые промежуточные итоги из сводной таблицы.

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

4.Нажмите кнопку Мастер диаграмм.

5.Следуйте инструкциям мастера диаграмм.