Тема 1. Создание и редактирование базы данных

Тема 1. Создание и редактирование базы данных

Access – система управления базами данных (БД).

Объекты БД:

1) таблица (содержит сами данные);

2) запросы (поиск, сортировка и извлечение необходимых данных);

3) форма (ввод данных в указанном формате);

4) отчет (вывод данных с итогами);

5) макросы и модули (элементы, позволяющие производить более сложные операции).

Структура меню зависит от режима работы Access. Программа имеет три основных режима:

1) режим конструктора, в котором создаются и модифицируются объекты БД;

2) режим запуска, в котором можно выполнять некоторые операции, не открывая БД;

3) режим выполнения, в котором отображаются окна объектов БД.

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

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

При создании таблиц при помощи Конструктора задаются поля таблицы и их характеристики (свойства) (рис. 1.1).

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

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

Рис.1.1

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

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

Технология работы

1. Для запуска программы нажмите кнопку Пуск – Все программы – Microsoft Office – Microsoft Office Access 2007.

2. Выберите Новая база данных. Справа дается имя «Клиенты». Нажмите кнопку Создать.

3. В окне БД перейдите на вкладку Главная – Режим – Конструктор.

4. Дайте имя таблице – «Клиенты»

5. Щелкните по кнопке Создать.

6. Введите следующие данные в область Проекта таблицы (рис. 1.2).

Рис. 1.2

7. Установите курсор на поле, которое будет объявлено полем первичного ключа – «Код клиента».

8. Щелкните по кнопке с изображением ключа (Ключевое поле).

9. Перейдите в режим таблицы на вкладке Главная.

Рис. 1.3

10. Заполните таблицу своими данными (не менее семи записей). Поле «Сведения» пока оставьте пустым (рис. 1.3).

Типы связей

1. Один ко многим (1–¥, 1:М). Когда каждому значению поля А таблицы 1 соответствует несколько значений связанного поля таблицы 2,
а обратное неверно.

Тип Товар

*Код типа

Код типа

2. Многие ко многим (М:М, ¥-¥). Когда каждому значению поля А таблицы 1 соответствует несколько значений связанного поля таблицы 2 и наоборот.

Таблица Заказы	Таблица Заказано
*Код заказа	*Код заказа
*Код клиента	*Код товара

3. Один к одному (1:1). Когда одному значению поля А таблицы 1 соответствует единственное значение связанного поля таблицы 2 и наоборот.

Таблица Клиент	Таблица Образование
(ФИО, адрес, пол…)
*Код клиента	*Код клиента

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

Эти правила сохраняют неприкосновенность связей между таблицами в СУБД. Целостность данных предохраняет данные от изменений, которые нарушают связи между таблицами.

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

Виды целостности данных:

1) каскадное обновление связанных полей: дает возможность изменять содержимое поля связи;

2) каскадное удаление связанных полей: при удалении записи в основной таблице автоматически удаляется запись и в связанной таблице.

Технология работы

1. Откройте созданную БД.

2. Создайте последовательно таблицы: Типы, Товар, Заказано и Заказы при помощи Конструктора. Размеры и типы полей выберите самостоятельно (рис. 1.5).

Рис. 1.5

Рекомендации:

· в таблице Товар можно использовать только те коды типов, которые вы описали в таблице Типы. Аналогично в таблице Заказано можно использовать только те товары, которые описаны в таблице Товары, в таблице Заказы используются только заказы и клиенты, которые описаны соответственно в таблицах Заказано и Клиенты;

· поле «Скидка»: тип – числовой, формат – процентный, размер – с плавающей точкой;

· в таблице Товары используйте разные (2–3) единицы измерения.

3. Задайте ключевые поля: таблица Клиенты – Код клиента, таблица Типы – Код типа, таблица Заказы – Код заказа, таблица Товар – Код товара, таблица Заказано – Код заказа и Код товара (сцепленный ключ). Для объявления полями первичного ключа нескольких полей маркируйте их, удерживая клавишу Ctrl.

4. Заполните таблицы данными – не менее 7 записей. В таблице Типы достаточно 3–4 записей. Тип определяет группу товаров. Например, зимняя одежда, демисезонная, летняя. В таблице Заказано введите такие ситуации: в одном заказе заказывается несколько товаров; один и тот же товар заказывается в разных заказах.

5. Закройте все таблицы.

6. На вкладке Работа с базами данных выберите команду Схема данных.

7. В окне Добавление таблицы добавьте таблицы Клиенты, Товар, Заказы, Заказано, Типы.

8. Щелкните по кнопке Закрыть.

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

10. В появившемся окне Связи установите флажок «Обеспечение целостности данных».

11. Нажмите кнопку Создать (рис. 1.6).

Рис. 1.6

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

13. В появившемся окне Связи установите флажок «Обеспечение целостности данных».

14. Нажмите Готово.

15. Аналогично свяжите поля «Код товара» таблиц Заказано и Товар. И поля «Код типа» таблиц Товар и Типы.

Лабораторная работа № 2

Тема 5. Создание форм

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

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

Технология работы

Создание составной формы

1. Откройте вкладку Создание – Другие формы – Мастер форм.

2. В диалоговом окне Создание форм выберите в списке Таблицы/запросы таблицу Товар.

3. В списке Доступные поля выберите все поля таблицы при помощи кнопки с двойной стрелкой.

4. В списке Таблицы/запросы выберите таблицу Заказано.

5. В списке Доступные поля выберите все поля таблицы при помощи кнопки с двойной стрелкой.

6. Нажмите кнопку Далее.

7. В следующем окне установите опцию Подчиненные формы и нажмите кнопку Далее.

8. В третьем диалоговом окне выберите вид подчиненной формы табличный и нажмите кнопку Далее.

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

10. В последнем окне Мастера введите заголовок формы « Заказанный товар» и нажмите кнопку Готово.

Тема 6. Создание отчетов

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

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

Редактирование созданного отчета происходит в режиме Конструктора.

Технология работы

Тема 1. Проверка данных

Команда Проверка данных защищает ячейки от ввода неверных данных. Чтобы задать условия проверки данных, выделите диапазон ячеек, к которому должны применяться эти условия. Затем в меню Данные, группа Работа с данными, выберите команду Проверка данных, и Excel откроет окно диалога Проверка вводимых значений. На вкладке Параметры этого окна диалога укажите тип и интервал значений, которые разрешается вводить. Вкладка Сообщение для ввода позволяет создать подсказку пользователю о том, какие данные можно вводить. На вкладке Сообщение об ошибке можно указать сообщение, которое будет появляться на экране при вводе некорректных данных.

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

Задание типа допустимых значений

Чтобы задать тип разрешенных значений, раскройте список Тип данных на вкладке Параметры окна диалога Проверка вводимых значений. Вы можете выбрать один из следующих типов: Любое значение, Целое число, Действительное, Список, Дата, Время, Длина текста и Другой. После выбора типа данных Excel изменит окно диалога так, чтобы вы могли ввести дополнительную информацию для выбранного типа. Например, для целых чисел Excel предложит ввести минимальное и максимальное значения.

Задание списка допустимых значений

Чтобы задать список допустимых значений, выберите пункт Список в раскрывающемся списке Тип данных и затем укажите диапазон рабочего листа, где находится ваш список. Например, чтобы пользователь мог ввести в ячейку А1 только слово Север, Запад, Юг или Восток, введите эти четыре значения в какой-нибудь части листа, скажем в диапазоне Z1:Z4. После этого выделите ячейку А1, выберите команду Проверка, в раскрывающемся списке Тип данных выберите пункт Список и затем в поле «Источник» укажите диапазон Z1:Z4.

Использование формулы для проверки данных

Чтобы данные проверялись с помощью формулы, в раскрывающемся списке Тип данных выберите пункт Другой и затем введите нужное выражение в поле «Формула». Например, чтобы значение в ячейке А1 было больше значения в ячейке В1, выделите ячейку А1, выберите команду Проверка, в раскрывающемся списке Тип данных выберите пункт Другой и затем введите выражение =А1>В1 в поле «Формула».

Если перед выбором команды Проверка выделен диапазон ячеек, то в вашей формуле должны быть соответствующим образом указаны относительные и абсолютные ссылки. Предположим, что вы хотите обеспечить правильный ввод данных в ячейки А1:А10 и для проверки значений используете формулу =A1>B1. В этом случае Excel проверяет условия А1>В1, А2>В2 и т. д. Но если вы зададите формулу =А1>$В$1, то Excel будет проверять выполнение А1>В1, А2>В1, АЗ>В1 и т. д.

Задание сообщения для ввода

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

Задание сообщения об ошибке

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

Кроме того, в раскрывающемся списке Вид вы можете выбрать тип сообщения об ошибке: Останов, Предупреждение или Сообщение. Эти три варианта отличаются значками, которые выводятся рядом с текстом сообщения, а также набором кнопок. Сообщение типа Останов (по умолчанию) позволяет пользователю повторить либо отменить ввод. Сообщение типа Предупреждение позволяет пользователю наряду с этими возможностями сохранить введенное некорректное значение. Если в списке Вид вы выберете пункт Сообщение, то пользователь сможет сохранить введенное значение или отменить ввод, но ему не будет разрешено повторить ввод.

Технология работы

1. Создайте новую Книгу в Excel.

2. Переименуйте «Лист1» в «Справочник поставщика».

3. Введите заголовок таблицы «Справочник поставщика».

4. Отформатируйте заголовок (объедините и поместите в центре ячеек А1:D1).

5. Отформатируйте шапку таблицы (перенос по словам + выравнивание по центру).

6. Введите информацию так, как это показано на рис.2.1.

Рис. 2.1

7. Присвойте имя группе ячеек А3:D7 – «Код_поставщика» (Формулы – Присвоить имя).

8. Переименуйте «Лист2» в «Справочник товаров».

9. Аналогично создайте таблицу Справочник товаров (рис. 2.2).

Рис. 2.2

10. Присвойте имя группе ячеек А3: С8 – «Код_товара».

11. Переименуйте «Лист3» в «Приходная накладная».

12. Создайте таблицу «Приходная накладная» (рис. 2.3). Отформатируйте заголовок и шапку.

Рис. 2.3

13. Организуйте контроль вводимых данных в поле «Код поставщика» через Проверку данных:

Тип данных – Целое число, Минимум – 100, Максимум – 105. Сообщение об ошибке – «Код поставщика может принимать значения от 100 до 105».

14. Аналогично организуйте контроль вводимых данных в поле «Код товара» на значения от 1001 до 1006.

15. Введите все исходные данные, кроме полей «Наименование поставщика» и «Наименование товара» (рис. 2.4).

Рис. 2.4

Тема 2. Связывание таблиц

Excel позволяет имитировать связи Access путем использования функции ВПР, которая берет значение из связанной таблицы-справочника и возвращает его в оперативную таблицу. Функция ВПР ищет значение в первом столбце массива таблицы и возвращает значение в той же строке из другого столбца массива таблицы.

Синтаксис функции:

ВПР (искомое_значение;таблица;номер_столбца;интервальный_просмотр)

Искомое_значение – значение, которое должно быть найдено в первом столбце табличного массива.. Если искомое значение меньше, чем наименьшее значение в первом столбце табличного массива, функция ВПР возвращает значение ошибки #Н/Д.

Таблица – два или более столбцов данных. Можно использовать ссылку на диапазон или имя диапазона. Значения в первом столбце аргумента «таблица» – это значения, в которых выполняется поиск аргумента «искомое_значение».

Номер_столбца – номер столбца в аргументе «таблица», из которого возвращается соответствующее значение. Если номер_столбца = 1,
то возвращается значение из первого столбца таблицы; если номер_столбца = 2 – значение из второго столбца таблицы и т. д.

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

Технология работы

1. Задайте заполнение графы «Наименование поставщика» в соответствии с Кодом поставщика (ключевое поле) через функцию ВПР:

а) встаньте в ячейку В3;

б) выберите функцию ВПР из категории «Ссылки и массивы»;

в) введите аргументы, как это указано на рис. 2.5 (синтаксис функции ВПР применительно к данному заданию описан в табл. 2.1);

г) скопируйте формулу в ячейки В4:В13.

Таблица 2.1

Наименование аргумента	Пояснения к заполнению
Искомое_значение	Значение из первого столбца таблицы, по которому будем искать Наименование поставщика, в данном случае это «Код поставщика»
Таблица	Таблица, в которой будем искать Наименование поставщика по полю «Код поставщика». В данном случае это таблица, которую мы назвали Код_поставщика
Номер_столбца	Номер столбца в таблице Код_поставщика, из которого нужно вернуть значение Наименования поставщика. В данном случае это столбец № 2 из таблицы Код_поставщика

Рис. 2.5

2. Аналогично задайте заполнение поля Наименование товара.

Тема 3. Сводные таблицы

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

Команда Сводная таблица вызывается из меню Вставка, группа Таблицы. Для построения отчета необходимо:

1) выбрать таблицу или диапазон источника данных;

2) указать, куда следует поместить отчет сводной таблицы – на новый лист или существующий (с указанием диапазона);

3) построить макет сводной таблицы.

Структура сводной таблицы состоит из следующих областей, которые вы определяете в макете (рис. 2.6):

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

· поля столбцов – поля размещаются слева направо, обеспечивая группирование данных сводной таблицы по иерархии полей; при условии существования области страницы или строки определять столбец не обязательно;

· поля строк – поля размещаются сверху вниз, обеспечивая группирование данных таблицы по иерархии полей; при условии существования области страницы или столбцов определять строку не обязательно;

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

Рис. 2.6

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

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

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

Технология работы

1. Создайте ведомость «Фактическое выполнение поставок» (рис. 2.7):

а) установите курсор в поле таблицы «Приходная накладная»;

б) воспользуйтесь командой Сводная таблица из меню Вставка;

в) перенесите в область «Названия строк» поле «Наименование товара»;

г) перенесите в область «Значения» столбец Сумма поставки фактическая;

д) переименуйте лист со сводной таблицей в Фактическое выполнение поставок.

Рис. 2.7

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

е) сделайте активной страницу Фактическое выполнение поставок;

ж) нажмите стрелку правее поля «Название строк»;

з) отметьте товар Пальто.

2. Создайте ведомость «Сумма поставок по дням» (рис. 2.8):

а) установите курсор в поле таблицы «Приходная накладная»;

б) воспользуйтесь командой Сводная таблица из меню Вставка;

в) перенесите в область «Названия строк» поле «Дата поставки»;

г) перенесите в область «Названия строк» надпись «Наименование поставщика»;

д) перенесите в область «Фильтр отчета» надпись «Наименование товара»;

е) перенесите в область «Значения» поле «Сумма поставки фактическая»;

ж) переименуйте лист со сводной таблицей в «Поставки по дням».

Лабораторная работа № 4

Тема 4. Фильтрация списка

Фильтры обеспечивают выборку подмножества данных из списка. Имеются две разновидности этой команды: Автофильтр и Расширенный фильтр.

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

Рис. 2.8

Если условие фильтрации для значений в столбце более сложное, выбирается режим Настраиваемый фильтр, который выводит диалоговое окно Пользовательский автофильтр (рис. 2.9).

Рис. 2.9

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

· оператор отношения: равно, не равно, больше, больше или равно, меньше;

· значение, которое может выбираться из списка или содержать шаблонные символы «*» и «?».

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

Расширенный фильтр используется для более сложных критериев отбора. И по сравнению с командой Автофильтр имеет некоторые дополнительные возможности:

· допускается задавать условия, соединенные логическим оператором ИЛИ, для нескольких столбцов (оператор И расширенного фильтра аналогичен тому, если дважды применить Автофильтр);

· допускается задавать три и более условий для конкретного столбца;

· допускается задавать вычисляемые условия;

· позволяет сразу копировать отобранные строки без применения команд Копировать и Вставить.

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

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

Расширенный фильтр обеспечивает использование двух типов критериев для фильтрации записей списка:

· критерий сравнения;

· вычисляемый критерий.

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

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

· оператор отношения: равно, не равно, больше, больше или равно, меньше;

· значение, которое может выбираться из списка или содержать шаблонные символы «*» «и»?.

Технология использования расширенного фильтра включает два этапа:

1-й этап – формирование интервала критериев;

2-й этап – фильтрация записей списка.

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

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

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

3. Ниже меток располагаются критерии сравнения типа:

· точного значения;

· значения, формируемого с помощью операторов отношения;

· шаблона значения, включающего символы «*» и «?».

Правила формирования множественного критерия:

1) если критерии (условия) указываются в каждом столбце на одной строке, то они считаются связанными условием И;

2) если условия записаны в нескольких строках, то они считаются связанными условием ИЛИ.

Пример

1. Условие выбора записей о поставщике Аврора за 06.09.2008:

Наименование поставщика	Дата поставки
Аврора	06.09.2008

2. Условие выбора записей о поставщике ВосходИЛИ по товару Ткань:

Наименование поставщика	Наименование товара
Восход
	Ткань

3. Условие выбора записей о поставке товара Шуба за 07.09.2008 ИЛИ 08.09.2008:

Наименование товара	Дата поставки
Шуба	07.09.2008
Шуба	08.09.2008

Вычисляемый критерий. Вычисляемые условия отличаются от обычных условий сравнения тем, что позволяют использовать значения, возвращаемые формулой. Вычисляемый критерий представляет собой формулу, записанную в строке условий интервала критериев, которая возвращает логическое значение ИСТИНА или ЛОЖЬ. Формула строится с использованием: адресов ячеек, встроенных функций, констант различных типов (число, текст, логическая константа), операторов отношения.

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

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

· Ссылки на ячейки, находящиеся вне списка, должны быть абсолютными ($).

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

Пример

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

2. Выбрать записи о поставщике Аврора с суммой поставки фактической выше средней:

После завершения ввода вычисляемого критерия в ячейке должна появиться логическая константа ИСТИНА или ЛОЖЬ – результат применения сформированного вычисляемого критерия по отношению к первой записи списка.

2 этап. Фильтрация записей расширенным фильтром. После подготовки интервала критериев курсор устанавливается в список и на вкладке Данные, группа Сортировка и фильтрация, выбирается команда Дополнительно (рис. 2.10).

Рис. 2.10

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

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

Тема 5. Промежуточные итоги

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

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

Рис. 2.11

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

Рис. 2.12

Группообразующий столбец выбирается из списка посредством параметра При каждом изменении в. Перечень прочих столбцов, по которым подводятся итоги выбранного вида функции, указывается в окне Добавить итоги по:. Итоги выводятся либо под данными, либо над данными (параметр Итоги под данными). Принудительно группы могут размещаться на отдельных печатных страницах (параметр Конец страницы между группами). Кнопка Убрать все удаляет все итоги из текущего списка. При нажатии кнопки ОК автоматически создаются промежуточные итоги (рис. 2.13).

Слева от таблицы на служебном поле появляются символы структуры: показа деталей (кнопка Плюс); скрытия (кнопка Минус); уровней структуры (кнопка Номера уровней 1, 2, 3 и т.д.); полос уровней структуры (объединение по принадлежности одному иерархическому уровню). Для открытия (закрытия) определенного уровня иерархии необходимо щелкнуть на номере. Для открытия определенной иерархической ветви нажимаются Плюс, Минус определенного элемента структуры, которые находятся слева от таблицы.

Рис. 2.13

Технология работы

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

1. Отсортировать таблицу по полям «Наименование поставщика» и «Дата поставки».

2. Построить итог первого уровня: