Любая программа для обработки данных должна выполнять три основных функции: ввод новых данных

Вид материала

Программа

Содержание 1. Основные понятия Иванов Иван 123-55-86 2. Отличия между базами данных 3. Список условных обозначений Основные обозначения 4. Создание рабочей книги Лист1»), выполнив (Л”) Файл®Сохранить как Кол-во комнат 5. Сортировка информации 6. Поиск данных с помощью фильтров 6.1. Использование автофильтра 6.2. Расширенный фильтр А1:Е1 и скопируйте их содержимое в ячейки А23:Е23 Исходный диапазон Кол-во комнат 7. Обобщение табличных данных 7.1. Консолидация данных Сумма» разместим формулы, подсчитывающие суммарную выручку от продажи одного вида мороженого на данной точке. Например, в ячейку А1:D1 и выполните команду ПравкаКопировать ... Полное содержание

Подобный материал:

• 1. 2 Системы управления базами данных. Основные функции, 630.95kb.
• Понятия о базах данных и системах управления ими. Классификация баз данных. Основные, 222.31kb.
• Программа повышения квалификации «Использование Microsoft Excel для автоматизации бухгалтерского, 14.19kb.
• Примерная рабочая программа по дисциплине: базы данных, 104.62kb.
• Должна быть конкретной, кратко сформулированной и соответствовать современному уровню, 20.13kb.
• Программа дисциплины структуры и алгоритмы компьютерной обработки данных для специальности, 506.16kb.
• Отчеты в ms access, 447.2kb.
• Об использовании структур представления данных для решения возникающих задач; знать, 116.73kb.
• Программа дисциплины Анализ данных средствами ms excel для направления 080102. 65 Мировая, 121.98kb.
• Рабочей программы дисциплины Структуры и алгоритмы обработки данных по направлению, 21.62kb.

ВВЕДЕНИЕ


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


1. ОСНОВНЫЕ ПОНЯТИЯ


Дадим более строгие определения.

КОМПЬЮТЕРНАЯ БАЗА ДАННЫХ – это файл, который содержит совокупность данных, отображающих состояние объектов и их отношений, составленный с минимальной избыточностью.

СУБД – это совокупность языковых и программных средств, предназначенных для создания, ведения и совместного применения базы данных многими пользователями.

Любая программа для обработки данных должна выполнять три основных функции:

– ввод новых данных;

– доступ к существующим данным (поиск);

– анализ данных (составление отчетов).

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

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

Например, в записной книжке вы делаете примерно такую запись

Фамилия, имя	Телефон
Иванов Иван	123-55-86
Петров Петр	233-68-77

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

Еще два основных термина, используемых при работе с базами данных – это список и запись.

Список – это набор информации. Например, в вашей записной книжке это список ваших знакомых и их телефонов. Поле – это столбец в списке базы данных. Поле отображает какую-либо категорию информации. Например, «Иванов Иван» – это поле, а «Фамилия, имя» – это имя поля.

Запись – это строка в списке базы данных. Запись состоит из всех полей для одного объекта в базе данных. Например, строка

« Иванов Иван 123-55-86»

есть запись базы данных.

Множество хранимой информации базы данных образует файл.

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

1. Какие понадобятся поля?
   
2. Какие имена будут иметь эти поля?
   
3. Какого рода информация будет содержаться в этих полях?
   
4. Какие из полей будут выступать в качестве полей для организации запросов?
   

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

Кроме того, в ячейках своих таблиц Excel хранит не только текстовую или числовую информацию, но и формулы.

Четвертый вопрос требует решить, какое из полей определяет запись.

В записной книжке вы сначала ищете необходимую фамилию, а затем соответствующий фамилии телефон. Значит, в качестве поля для поиска используется поле «Фамилия, имя». Такое поле носит название ключевого поля или индексного поля.

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


2. ОТЛИЧИЯ МЕЖДУ БАЗАМИ ДАННЫХ

И ЭЛЕКТРОННЫМИ ТАБЛИЦАМИ


В систему электронных таблиц Excel [1–3] встроена поддержка некоторых основных возможностей, присущих системам управления базами данных. Программа Microsoft Access [5] является системой управления базами данных.

Возникает вопрос: в каких случаях использовать Excel для работы с базами данных, а в каких случаях Access?

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

В Excel мы можем использовать только базу данных плоских файлов, а Access поддерживает реляционную базу данных [4, 5]. Когда вы используете базу данных плоских файлов, то можете иметь доступ только к одному файлу данных. Мощь реляционных баз данных заключается в том, что они обеспечивают одновременный доступ к данным, размещенным в нескольких файлах.

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


3. Список условных обозначений


В табл. 1 приведены основные обозначения, используемые далее в тексте. В дальнейшем выражение «нажать на кнопку» (не на клавишу!) или «щёлкнуть по кнопке» будет означать выполнение (Л’) по изображению этой кнопки на экране. При работе с диалоговым окном слово «выбираем» (какие-либо параметры) означает, что надо сделать (Л’) на нужных параметрах и затем нажать на кнопку подтверждения выбора (обычно «OK», «Готово», «Применить», «Далее» и т.п.). Вместо мыши по кнопкам диалогового окна можно двигаться, нажимая <Tab> или <Shift>+<Tab>. Подтверждение выбора осуществляется клавишей <Enter>. Отказ от выбора – кнопка «Отмена» или клавиша <Esc>.

	Таблица 1
Основные обозначения
Обозначение	Смысл обозначения
(Л’)	Щелчок левой кнопкой мыши на объекте, указанном курсором
(Л”)	Двойной щелчок левой кнопкой мыши на объекте, указанном курсором
(П’)	Щелчок правой кнопкой мыши на объекте, указанном курсором
(Л)	Протаскивание мышью объекта, который зацеплен её курсором
<Ctrl>	Нажатие клавиши, название которой указано между < >
<Ctrl>+<Home>	Одновременное нажатие клавиш, название которых указано между < >
Вставка  Имя  Присвоить...	Движение по указанным пунктам меню для заказа нужной команды

4. СОЗДАНИЕ РАБОЧЕЙ КНИГИ


Допустим, нам надо создать базу данных агентства недвижимости.

Откройте новую рабочую книгу с помощью команды

ФайлСоздать

Присвойте первому листу имя «квартира». Для этого выделите текущее имя листа (« Лист1»), выполнив (Л”) на его ярлычке, после чего введите новое имя листа и нажмите <Enter>.

В ячейку А1 введите слово «район» и нажмите <Tab>. В ячейку В1 введите слова «кол-во комнат» и нажмите <Tab>. В ячейках С1, D1 и Е1 введите соответственно «общая площадь», «телефон» и «стоимость».

Если слово не поместилось в ячейку, то ее можно расширить, поместив указатель мыши на границу, разделяющую заголовки столбцов (например, В и С) и выполнив (Л’) или (Л) (когда указатель мыши располагается на границе, он превращается в вертикальную черту с горизонтальными стрелками). Аналогичным образом можно изменить и высоту ячейки, перемещая мышью границу, разделяющую заголовки строк.

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

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

1. Выделите область от А1 до Е12 (для этого выполните (Л’) в ячейке А1 и, не отпуская кнопку мыши, перетащите указатель на ячейку Е12).
   
2. Выберите в меню команду Вставка®Имя®Присвоить. На экране появится диалоговое окно «Присвоение имени».
   
3. Введите «База_данных» в качестве названия, а затем выполните (Л’) по «OK».
   
4. Сохраните результаты работы, выбрав команду Файл®Сохранить как. В поле «Папка» выберите «Мои документы», а в поле «Имя файла» введите имя вашей рабочей книги, например, «Моя книга», и выполните (Л’) на кнопке «Сохранить».
   

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

1. Выберите в меню команду Данные®Форма. На экране появится форма ввода данных.
   
2. Введите «Калининский» в поле «Район» и нажмите <Tab>.
   
3. Введите «2» в поле «кол-во комнат» и нажмите <Tab>.
   
4. Введите «30» в поле «Общая площадь» и нажмите <Tab>.
   
5. Введите «Да» в поле «телефон» и нажмите <Tab>.
   
6. Введите «20000» в поле «Стоимость» и нажмите <Enter>.
   
7. Таким же образом введите еще 10 записей. Нажатие <Shift>+<Tab> приводит к возврату в предыдущее поле. Вы можете также перемещаться от одной существующей записи к другой, используя клавиши «­» и «¯». Когда вы закроете форму ввода данных, Excel поместит все внесенные данные в соответствующие ячейки рабочего листа.
   

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

1. Выделите последний столбец списка (для этого выполните (Л’) по заголовку столбца E).
   
2. Выберите в меню команду Формат®Ячейки… На экране появится диалоговое окно «Формат ячеек».
   
3. Выберите вкладку «Число» и в поле «Числовые форматы» выберите из списка вариант «Денежный».
   
4. Выполните (Л’) на кнопке поля «Обозначение» и выберите из появившегося списка вариант «$Английский (США)».
   
5. В поле «Число десятичных знаков» введите «0».
   
6. Выполните (Л’) по «OK».
   
7. Для окончательного приведения полученного списка к виду, представленному в табл. 2, следует выполнить следующие действия:
   
8. Выделите первую строку (для этого выполните (Л’) по заголовку строки 1).
   
9. Выберите в меню команду Формат®Ячейки… На экране появиться диалоговое окно «Формат ячеек».
   
10. Выберите вкладку «Выравнивание» и в разделе «Отображение» установите флажок «переносить по словам».
    
11. Выполните (Л’) на кнопке поля «Выравнивание по вертикали: » и выберите из появившегося списка вариант «по центру».
    
12. Выполните (Л’) по «OK».
    
13. Подберите нужную ширину всех столбцов и высоту первой строки.
    

					Таблица 2
	A	B	C	D	E
1	РАЙОН	КОЛ-ВО КОМНАТ	ОБЩАЯ ПЛОЩАДЬ	ТЕЛЕФОН	СТОИМОСТЬ
2	КАЛИНИНСКИЙ	2	30	Да	$20000
3	МОСКОВСКИЙ	3	58	Да	$19000
4	ПЕТРОДВОРЦОВЫЙ	5	87	Нет	$60000
5	ПРИМОРСКИЙ	3	56	Да	$39000
6	КРАСНОСЕЛЬСКИЙ	2	23	Нет	$15000
7	ВАСИЛЕОСТРОВСКИЙ	2	37	Да	$21000
8	НЕВСКИЙ	4	60	Нет	$60000
9	ФРУНЗЕНСКИЙ	3	66	Да	$19000
10	СМОЛЬНЕНСКИЙ	1	22	Да	$12000
11	ВАСИЛЕОСТРОВСКИЙ	2	45	Нет	$15000
12	ЦЕНТРАЛЬНЫЙ	5	68	Да	$55000

5. СОРТИРОВКА ИНФОРМАЦИИ

Для сортировки данных Excel имеет мощные средства. При этом производится упорядочивание записей, а не отдельных полей, т. е. данные в каждой строке всегда находятся вместе. Сортировка может производиться в возрастающем или убывающем порядке. В возрастающем порядке Excel сортирует по алфавиту (от А до Я) и по цифрам от 0 до 9, а в убывающем порядке – от Я до А и от 9 до 0.

Выберите в меню команду Данные®Сортировка. На экране появится диалоговое окно «Сортировка диапазона». Выполните (Л’) на кнопке со стрелкой в поле «Сортировать по» и выберите из появившегося списка вариант «Район». В поле «Затем по» аналогичным образом выберите вариант «Кол-во комнат», а в поле «В последнюю очередь, по» выберите вариант «Общая площадь». Выполните (Л’) по «OK».

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

6. ПОИСК ДАННЫХ С ПОМОЩЬЮ ФИЛЬТРОВ

Используя фильтры Excel, вы можете отыскать данные, удовлетворяющие указанному критерию. На экране отобразятся только те данные, которые вы хотите увидеть.


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

для отображения определённых данных


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

1. Выберите команду ДанныеФильтрАвтофильтр (выделенная ячейка при этом должна находиться в пределах списка). Excel вставит кнопки со стрелками, вызывающие списки возможных условий фильтрации, в каждый столбец строки заголовков.
   
2. Выполните (Л’) на кнопке заголовка «Кол-во комнат» и выберите из списка вариант «2». Excel скроет все данные, которые не удовлетворяют критерию (не являются двухкомнатными квартирами).
   
3. Выполните (Л’) на кнопке заголовка «Стоимость» и выберите вариант «(Условие…)». На экране появится диалоговое окно «Пользовательский автофильтр». Выполните (Л’) на кнопке левого верхнего поля и выберите вариант «меньше или равно». В правое верхнее поле введите «20000» (без знака доллара!). Нажмите «OK».
   

В результате на экране отобразятся только двухкомнатные квартиры, стоимость которых <=$20000. Чтобы на экране появились все данные, выберите команду ДанныеФильтрПоказать все. Чтобы отключить автофильтр, выберите команду ДанныеФильтрАвтофильтр.


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


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

В таких случаях целесообразно применять расширенный фильтр.

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

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

1. Выделите ячейки A1, В1 и скопируйте их содержимое в буфер обмена, выбрав команду ПравкаКопировать или выполнив (Л’) по соответствующей кнопке в панели инструментов.
   
2. Выделите ячейку А20 и выберите команду ПравкаВставить или выполните (Л’) по соответствующей кнопке в панели инструментов.
   
3. В ячейку А21 введите «Василеостровский», в ячейку В21 – число «2». В ячейку А22 и В22 введите, соответственно, «Приморский» и «3». Эта таблица будет диапазоном условий.
   
4. Выделите ячейки А1:Е1 и скопируйте их содержимое в ячейки А23:Е23 , используя буфер обмена.
   
5. Выделите любую ячейку в исходной таблице и выберите команду ДанныеФильтрРасширенный Фильтр. Появится окно «Расширенный фильтр».
   
6. В пункте «Обработка» установите переключатель в положение «скопировать результат в другое место».
   
7. В строке « Исходный диапазон» необходимо указать номера ячеек, в которых находится исходная таблица. Поставьте курсор в эту строку и выделите нужный диапазон. В нашем случае это $A$1:$E$12. В строку «Диапазон условий» введите $A$20:$B$22. И, наконец, в строке «Поместить результат в диапазон» укажите $A$23:$E$30. Нажмите «OK».
   

Результат фильтрации представлен в табл. 3.

					Таблица 3
	A	B	C	D	E
23	РАЙОН	КОЛ-ВО КОМНАТ	ОБЩАЯ ПЛОЩАДЬ	ТЕЛЕФОН	СТОИМОСТЬ
24	ВАСИЛЕОСТРОВСКИЙ	2	37	Да	$21000
25	ВАСИЛЕОСТРОВСКИЙ	2	45	Нет	$15000
26	ПРИМОРСКИЙ	3	56	Да	$39000

7. ОБОБЩЕНИЕ ТАБЛИЧНЫХ ДАННЫХ


Microsoft Excel содержит большой набор средств, позволяющих обобщать данные, представленные списком, подводить какие-либо итоги: например, суммировать их, усреднять, подсчитывать число элементов, удовлетворяющих тому или иному условию, и т. п. Некоторые из функций, предназначенных для решения простейших задач такого рода, описаны в [3], однако при работе с базами данных этих средств иногда недостаточно, и приходится пользоваться более сложными приёмами получения обобщённых результатов. В данном разделе описываются два способа решения подобных задач: консолидация данных и построение сводной таблицы.


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


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

В ячейках столбца « Сумма» разместим формулы, подсчитывающие суммарную выручку от продажи одного вида мороженого на данной точке. Например, в ячейку D3 введем формулу =В3*С3, а затем скопируем её в ячейки D4:D6, D8:D11 и D12:D15, используя буфер обмена. При копировании номера ячеек, участвующие в формуле, будут автоматически изменяться: например, в ячейке D15 будет содержаться формула =B15*C15.

В столбцах B и D («Цена» и «Сумма») установите числовой формат «Денежный» (аналогично тому, как это было сделано в столбце «Стоимость» табл. 2), при этом в поле «Обозначение» оставьте установленный там по умолчанию вариант «р.» (рубль), а в поле «Число десятичных знаков» – значение «2».

В ячейку A2 вносим «ст. м. Владимирская», выделяем A2:D2 и нажимаем кнопку «a». Выделенные ячейки объединяются, текст помещается в центре объединённой ячейки. Аналогичные действия производятся с ячейками A6:D6 и A11:D11.

Таблица 4
	A	B	C	D
1	Наименование	Цена	Количество	Сумма
2	ст. м. Владимирская
3	Даша	4,50р.	95	427,50р.
4	Митя	4,50р.	78	351,00р.
5	Сах. трубочка	5,50р.	147	808,50р.
6	ст. м. Василеостровская
7	Балтийское	5,00р.	145	725,00р.
8	Даша	4,50р.	84	378,00р.
9	Митя	4,50р.	57	256,50р.
10	Сах. трубочка	5,50р.	125	687,50р.
11	ст. м. Приморская
12	Балтийское	5,00р.	113	565,00р.
13	Митя	4,50р.	98	441,00р.
14	Сах. трубочка	5,50р.	139	764,50р.

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

Процедура консолидации представляет собой диалог, выполняемый в окне «Консолидация». Оно вызывается командой

ДанныеКонсолидация…

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

Дальнейший порядок действий таков:

1. Скопируйте строку заголовков для итоговой таблицы. Для этого выделите диапазон А1:D1 и выполните команду
   

ПравкаКопировать

Переведите курсор в ячейку А29 и выполните команду

ПравкаВставить

2. Выделите ячейку, определяющую положение итоговой таблицы. В нашем примере это может быть, например, ячейка А30.
   
3. Выполните команду
   

ДанныеКонсолидация…

4. В окне «Консолидация» в списке «Функция:» укажите функцию «Сумма». Кроме суммирования, в Excel существует еще много способов подведения итогов.
   
5. Установите курсор в строку «Ссылка:». В этой строке находится кнопка, которая позволяет свернуть окно до минимального размера, оставив лишь строку ввода. Это позволит увидеть весь рабочий лист и контролировать выделение диапазона. Повторное нажатие на эту кнопку позволяет развернуть окно до его первоначального размера.
   
6. Выделите первую исходную область. В нашем примере она находится в диапазоне А3:D5 . Рамка выделения должна быть пунктирной.
   
7. Щелкните в окне «Консолидация» кнопку «Добавить».
   
8. Повторите действия, описанные в пунктах 4, 5 и 6, для диапазонов A7:D10 и A12:D14.
   
9. В разделе «Использовать в качестве имён» установите флажок «значения левого столбца» и нажмите кнопку «OK».
   

Результирующая таблица будет иметь следующий вид (табл. 5):

Таблица 5
	A	B	C	D
29	Наименование	Цена	Количество	Сумма
30	Балтийское	10,00р.	258	1290,00р.
31	Даша	9,00р.	179	805,50р.
32	Митя	13,50р.	233	1048,50р.
33	Сах. трубочка	16,50р.	411	2260,50р.

Второй столбец табл. 5 содержит ошибочные результаты: при консолидации произошло суммирование цен. Для исправления этой ошибки выполните следующее:

1. Выделите диапазон ячеек A30:B33, содержащий имена строк и ошибочные цены.
   
2. Выполните команду ДанныеКонсолидация... Снова появится диалоговое окно с прежним списком диапазонов.
   
3. Выполните (Л’) на кнопке со стрелкой в поле «Функция:» и выберите из списка функцию «Среднее».
   
4. Нажмите кнопку «OK». В результате суммирование в выделенной области заменяется усреднением, и итоговая таблица приобретает окончательный вид, представленный в табл. 6.
   

Рассмотрим оставшиеся инструменты окна «Консолидация». Кнопка «Обзор» позволяет выбрать исходные области из других рабочих листов и даже из других файлов. Группа флажков «Использовать в качестве имен» предназначена для того, чтобы сообщить программе, каким образом (в строках или в столбцах) размещены данные в исходных областях. Флажок «Создавать связи с исходными данными» нужно устанавливать в том случае, когда итоговая область размещена на другом рабочем листе.

Таблица 6
	A	B	C	D
29	Наименование	Цена	Количество	Сумма
30	Балтийское	5,00р.	258	1290,00р.
31	Даша	4,50р.	179	805,50р.
32	Митя	4,50р.	233	1048,50р.
33	Сах. трубочка	5,50р.	411	2260,50р.

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


7.2. Сводная таблица


Мастер сводных таблиц позволяет использовать еще один способ обобщения табличных данных. Особенностью этого метода является то, что он хорошо работает только с однородными табличными данными, например, как это показано в табл. 7.

Таблица 7
	A	B	C	D	E
1	Ст. метро	Наименование	Цена	Количество	Сумма
2	Владимирская	Даша	4,50р.	95	427,50р.
3	Владимирская	Митя	4,50р.	78	351,00р.
4	Владимирская	Сах. трубочка	5,50р.	147	808,50р.
5	Василеостровская	Балтийское	5,00р.	145	725,00р.
6	Василеостровская	Даша	4,50р.	84	378,00р.
7	Василеостровская	Митя	4,50р.	57	256,50р.
8	Василеостровская	Сах. трубочка	5,50р.	125	687,50р.
9	Приморская	Балтийское	5,00р.	113	565,00р.
10	Приморская	Митя	4,50р.	98	441,00р.
11	Приморская	Сах. трубочка	5,50р.	139	764,50р.

Для построения сводной таблицы выполните последовательно следующие действия:

1. Выделите вашу таблицу и вызовите мастер сводных таблиц командой ДанныеСводная таблица… Появится окно шага 1 мастера сводных таблиц. Переключатель должен указывать на источник данных «в списке или базе данных Microsoft Excel». Нажмите кнопку «Далее >» для перехода ко второму шагу.
   
2. На втором шаге укажите диапазон, в котором содержатся исходные данные. В нашем случае это А1:Е11. Если ячейки выделены заранее, то программа автоматически укажет выделенный диапазон. Нажмите кнопку «Далее >».
   
3. На третьем шаге вы сами должны определить, как будет выглядеть новая сводная таблица. Требуется создать макет будущей таблицы. Переместите мышью кнопку «Ст. метро» в область «Страница», кнопку «Наименование» – в область «Строка», кнопку «Цена» – в область «Столбец», а кнопку «Сумма» – в область «Данные». Нажмите кнопку «Далее >».
   
4. На четвертом шаге построения сводной таблицы необходимо указать, куда следует её поместить. Установите переключатель «Поместить таблицу в» в положение «Существующий лист» и в открывшемся поле укажите адрес ячейки, которая будет соответствовать левому верхнему углу таблицы – например, A20. Если эта ячейка видна на листе, то достаточно выполнить (Л’) на ней. Кнопка «Параметры» открывает доступ к окну «Параметры сводной таблицы». Оставьте параметры, заданные по умолчанию.
   
5. Нажмите кнопку «Готово».
   

Сводная таблица, созданная в результате этих действий, представлена в табл. 8.

Таблица 8
	A	B		C	D	E
20	Ст. метро	(Все)	
21
22	Сумма по полю Сумма	Цена
23	Наименование	4,50р.		5,00р.	5,50р.	Общий итог
24	Балтийское			1290		1290
25	Даша	805,5				805,5
26	Митя	1048,5				1048,5
27	Сах. трубочка				2260,5	2260,5
28	Общий итог	1854		1290	2260,5	5404,5

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


СПИСОК ЛИТЕРАТУРЫ

1. Гончаров А. Excel 97 в примерах. – СПб.: Питер, 1997. – 336 с.
   
2. Гарнаев А.Ю. Использование MS Excel и VBA в экономике и финансах. – СПб.: БХВ–Санкт-Петербург, 1999. – 336 с.
   
3. Петрунина Е. Б. Обработка статистических данных средствами табличного процессора EXCEL 7.0. – СПб.: СПбГАХПТ, 1998. – 25 с.
   
4. Петрунина Е. Б., Плаченов А. Б. Основы проектирования реляционных баз данных. – СПб.: СПбГУНиПТ, 2000. – 19 с.
   
5. Петрунина Е. Б., Плаченов А. Б. Создание реляционных баз данных средствами СУБД ACCESS 97. – СПб.: СПбГУНиПТ, 2000. – 29 с.
   

СОДЕРЖАНИЕ

Стр.

ВВЕДЕНИЕ ………………………………………………………………..	3
1. ОСНОВНЫЕ ПОНЯТИЯ……………………………………………….	3
2. ОТЛИЧИЯ МЕЖДУ БАЗАМИ ДАННЫХ И ЭЛЕКТРОННЫМИ ТАБЛИЦАМИ……………………………………………………………..	5
3. Список условных обозначений…………………………….	5
4. СОЗДАНИЕ РАБОЧЕЙ КНИГИ……………………………………….	6
5. СОРТИРОВКА ИНФОРМАЦИИ………………………………………	9
6. ПОИСК ДАННЫХ С ПОМОЩЬЮ ФИЛЬТРОВ……………………..	9
6.1. Использование автофильтра для отображения определённых данных…………………………………………………………………	9
6.2. Расширенный фильтр……………………………………………...	10
7. ОБОБЩЕНИЕ ТАБЛИЧНЫХ ДАННЫХ……………………………..	11
7.1. Консолидация данных……………………………………………..	11
7.2. Сводная таблица……………………………………………………	14
СПИСОК ЛИТЕРАТУРЫ…………………………………………………	16