Данные/Группа и структура/Разгруппировать/строки

Консолидация

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

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

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

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

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

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

Решение. Чтобы освоить некоторые полезные приемы, нач­нем решать задачу "с нуля".

1) Создать новую рабочую книгу. Переименовать листы: задать им названия "Январь", "Февраль", "Март",

"1 квартал".

2) Ввести заголовки одновременно в несколько листов.

Для этого вы­делить все листы с названиями месяцев:

· перейти на лист "Январь", нажать клавишу Shift и, не отпуская ее, щёлк­нуть по ярлычку листа "Март". Будут выделены все листы рабо­чей книги, при этом активным листом останется "Январь".

GПримечание. Если нужно выделить только некоторые листы, следует удерживать нажатой клавишу Ctrl.

· Ввести в ячейку А1 "Наименование товара.", в ячейку В1 - "Количество продаж, шт.", в С1 - "Объем продаж, т.руб".

· Щёлкнуть по ярлычку лис­та "Февраль", выделение с нескольких листов будет снято. Убедиться, что в ранее выделенные листы внесен один и тот же текст в ячейки А1, В1, С1.

GДля иллюстративных целей поме­нять на листе "Февраль" содержимое ячеек: в В1 "Объем продаж, т.руб", а в С1 " Количество продаж, шт. ".

3) Ввод информации в листы.

Ввести в листы с названиями месяцев информацию в соответствии с таблицами (рис. 1)

G Примечания.

1. Названия месяцев вводить не нужно, они на ярлычках листов.

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

Январь:

 

Февраль:

 

 

Март:

 

 

Рис. 1. Данные для примера 1.1

4) Консолидация.

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

· Выбрать в меню " Данные/ Консолидация ".

· Внимательно рассмотреть диало­говое окно "Консолидация" (рис. 2).

· В выпадающем списке " Функция:" выставлено "Сумма", т.е. выбранные данные будут суммиро­ваться.

G Примечание. Можно выбрать и другую итоговую функцию. Самостоятельно по­смотрите список.

 

Рис. 2. Диало­говое окно "Консолидация".

· Следующее поле: " Ссылка ".

Порядок действий при задании диапазонов консолидации:

- установить курсор для ввода в поле " Ссылка ",

- выделить диапазон для консолидации;

- когда в этом поле поя­вится адрес диапазона, щёлкнуть кнопку " Добавить " — адрес диапазона переместится в окно "Список диапазонов";

- повторить эти действия для всех объединяемых диапазонов.

В данном примере действия таковы:

- щёлкнуть мышью в поле "Ссылка".

- щёлкнуть по ярлычку листа "Январь" (в поле ввода появится "Январь!" — фор­мируется адрес). Выделить блок А1:С4 (в поле ввода "Январь!$А$1:$С$4") — вокруг блока бегущая пунктирная рамка.

- Щёлкнуть кнопку "Добавить" — адрес диапазона окажется в по­ле "Список диапазонов:".

- Аналогично добавить диапазоны "Февраль!$А$1:$С$3" и "Март!$А$1:$С$5". Список диапазо­нов консолидации сформирован.

· Установить флажки в блоке " Использовать в качестве имен ". Эти флажки ("подписи верхней строки" и "значения левого столбца") нужно ус­тановить, потому что информация в таблице будет определяться по названиям строк и столбцов.

G Примечания.

1) Если бы таблицы по месяцам имели одинаковую структуру, но разные названия столбцов, например, на одном листе столбец называется "Количество", а на другом — "Количество продаж", но их расположение в таблице одинаково, тогда следовало снять флажок "подписи верхней строки".

2) Флажок "создавать связи с исходными данными" устанавли­вать пока не будем.

· Щелчок по кнопке "ОК" - на рабочем листе появится таблица (рис. 3):

 

Рис. 3. Результат

консолидации данных

(пример 1.1).

G Внимание! Заголовок "Наименование товара" отсутствует. Верхняя строка дает подписи столбцов, в левом столбце — названия строк.

Самостоятельно!

1) Строку " Наименование товара." ввести в ячейку А1.

2) Проверить, целесообразно ли при вводе заголовков в рабо­чие листы с месяцами выделить и лист "1 квартал"?

На рис. 3 приведен результат консолидации данных без установления связи. Если мы внесем изменения в один из диапазонов консолидации, например, увеличим количество сделок, записанных за Ивановым, то таблица на листе "1 квартал " не изменится.

Чтобы изменить консолидированные данные в этом случае, нужно ещё раз выполнить команду "Данные/ Консолида­ция", в диалоговом окне ничего не менять, только щелкнуть "ОК". Тогда произойдет обновление таблицы.

Установление связей

1) Добавить лист и переименовать его "1 кв связь".

2) Выделить на листе "1 кв связь" ячейку А1.

3) Открыть диалоговое окно "Консолидация" и установить флажок " создавать связи с исходными данными ".

Таблица изме­нилась: столбец В пус­той, столбцы "Количество продаж" и "Объем продаж" переместились в столбцы С и D. Слева появились символы структуры - данные в таблице структурировались Вверху — номера с уровнями структуры: 1 — обобщен­ный, верхний уровень, 2 — детальный, нижний уровень.

 

 

 

Рис. 4а. Результат консолидации с установлением связи (закрытая по 1 уровню таблица).

4) Раскрыть второй уро­вень структуры.

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

5) Изменить количество сделок на листе "Март" - итоговые данные обновляются автоматически.

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

G Внимание! Данные в группе располагаются в том же порядке, что и в списке диапазонов окна "Консолидация".

7) Закрыть структуру (уровень структуры - 1), с помощью структурных значков (Ê) рассмотреть данные отдельных групп.

 

 

Рис. 4б. Результат консолидации с установлением связи

(раскрытая по 2-му уровню таблица).

Можно консолидировать данные не толь­ко с листов одной рабочей книги, но и из разных рабочих книг

Отменить структурирование в таблице:

Сортировка данных

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

 

Заголовки этих выбранных для сортировки столбцов таблицы называются ключами сортировки.

Сортировка - традиционный способ обработки данных в таблице после любых сделанных изменений и дополнений.

Существует несколько способов сортировки в Excel. Рассмотрим их на примере.

Пример 2.1. Дана таблица:

 

Рис. 5. Данные к примеру 2.1.

 

 

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

Отсортировать данные по факультетам:

· поставить курсор на любую ячейку таблицы;

· выполнить команду Данные/Сортировка

· в диалоговом окне команды задать параметры:

- в поле ввода "Сортировать по" указать "Факультет" (выбрать это название в выпадающем списке)

- выбрать переключатель "по воз­растанию"; ОК.

 

 

Рис. 6. Диалоговое окно команды Данные/Сортировка

 

G Внимание! Автоматически уста­новлен флажок "Идентифицировать поля по подписям", т.е. строка заголовков останется при сортировке на месте.

Задача 2.1. Отсортировать данные таблицы (рис.5) по фамилиям студентов.

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

- сортировка по возрастанию значений;

- сортировка по убыванию.

Ключом сортировки в этом случае является столбец с текущей ячейкой.

Самостоятельно проверить работу быстрой сортировки.

 

 

3) Отсортировать значения выделенногодиапазона таблицы (рис.5):

- выделить заданный участок таблицы, например С6:D8;

- щёлкнуть по соответствующей кнопке на стандартной панели инструментов: - по возрастанию значений, - по убыванию значений;

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

4) Сортировка по двум или трем ключам.

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

Рис.7. Пример многоуровневой сортировки

Правило многоуровневой сортировки:

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

Задача 2.2. К таблице примера 5.1. добавить столбец со средним баллом по результатам сессии каждого студента. Вывести рейтинг успеваемости студентов каждого факультета.

Задача 2.3. Восстановить исходный порядок данных в таблице после сортировки.

5) Сортировка по списку.

В Excel существует изящное решение: можно за­дать пользовательский список и сортировать в соответствии с порядком элементов в этом списке.

Для этого следует:

a) выбрать в меню: "Сервис/ Параметры/ Списки"

(списки дней недели и месяцев);

b) выбрать элемент "НОВЫЙ СПИСОК";

c) справа ввести свой, пользовательский список;

d) щёлкнуть кнопку "Добавить" (к стандартным спискам до­бавится и пользовательский список), ОК;

e) сортировать по ключу заданного списка:

- в диалоговом окне щёлкнуть кнопку "Параметры"

- в окне "Сортировка по пер­вому ключу" выбрать с помощью выпадающего списка пользовательский порядок сортировки.

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

сортировки по первому ключу.

Задача 2.4. Составить собственный список факультетов и

отсортировать по нему данные о группах и студентах.

Самостоятельно!

Разобраться самостоятельно, как импортировать пользова­тельский список из диапазона рабочего листа на вкладку "Спи­ски", как удалить пользовательский список.

 

Транспонирование таблицы

Для транспонирования (превращения строк в столбцы, а столбцов в строки) таблицы нужно:

s выделить таблицу;

s выполнить команду Правка - Копировать;

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

s выполнить команду Правка – Специальная вставка. В появившемся окне установить флажок «транспонирование», нажать ОК.

Самостоятельно!

1. Транспонировать таблицу примера 2.1.

2. Проверить работу функции ТРАНСП

(Ссылки и массивы, формула массива!)

Итоги

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

Порядок действий для подсчётов итогов:

1) отсортировать таблицу по столбцу, в котором нужно получить итоги;

2) поставить табличный курсор на любую ячейку таблицы (таблица выделяется);

3) выполнить команду Данные - Итоги;

4) задать параметры в диалоговом окне команды:

· в поле «При каждом изменении в» - выбрать имя поля, по которому нужен итоговый результат;

· в поле «Операция» - выбрать нужную функцию из списка для подведения итогов;

· в поле «Добавить итоги по» - указать флажком все поля, по которым нужны итоговые данные по выбранной функции;

· отметить необходимые флажки в диалоговом окне;

- «Заменить текущие итоги» - если возникла необходимость исправить предыдущие итоги;

убрать этот флажок, если нужно добавить итоги с другой функцией к существующим итогам;

- «Конец страницы между группами» следует указать, если таблица занимает несколько страниц;

- «Итоги под данными» - итоговые данные располагаются в том же столбце, что и данные,

· нажать кнопку OK.

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

Пример 5.1. Сколько студентов на каждом факультете? Каков средний балл студентов всего факультета?

Решение.

1) К таблице примера 2.1. добавить столбец со средним баллом по результатам сессии каждого студента.

2) Отсортировать данные по факультетам.

3) Выполнить команду Данные/Итоги (курсор в таблице!).

4) В диалоговом окне задать параметры как на рис.8., ОК

 

Рис.8. Диалог команды Данные/ Итоги

 

Рис. 9. Результаты решения примера 5.1., п. 4)

5) Добавить итоги - подсчитать средний балл по факультету - в диалоговом окне задать операцию -Среднее, Добавить итоги по - средний балл (флажок).

Рис. 10. Результаты решения примера 5.1., п. 5)

G Примечания к решению примера 5.1., п.5):

1) Дополнительная операция по подсчёту итогов ведёт к появлению дополнительного структурного уровня:

1-й уровень – общие итоги по двум операциям;

2-й уровень – промежуточные итоги по одной операции;

3-й уровень – промежуточные итоги по двум операциям;

4-й уровень – полное раскрытие списка.

2) Восстановить таблицу – кнопка «Убрать все» в диалоге команды Данные/Итоги

Задача 5.1. Подвести итоги по группам (данные примера 5.1). Сколько студентов в каждой группе, каков средний балл по группе?

Сводные таблицы

В Excel существует ещё одно очень мощное средство получения обоб­щенной информации из таблицы — сводные таблицы. Для построения сводной таблицы необходимо использовать однородные табличные данные.

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

Рис. 11. Данные к примерам главы 6.

Пример 6.1. Вывести суммарную оплату по факультетам.

Решение.

1) Диалог с Мастером сводных таблиц.

· Выделить одну из ячеек таблицы.

· Выполнить команду: "Данные/ Сводные таб­лицы ".

Запускается Мастер сводных таблиц.

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

В Excel 2000 предлагается также выбрать вид создаваемого отчета - "сводная таблица".

Нажать кнопку "Далее>".

· На втором шаге предлагается указать диапазон, содержащий исходные данные. Но Excel сам "догадался", что это наша таблица (мы предвари­тельно выделили в ней ячейку). Нажать кнопку "Далее>".

· Третий шаг - самый ответственный – определение структуры таблицы.

- Внимательно рассмотреть диалоговое окно: (рис. 12).

G В Excel 2000 нужно сначала щелкнуть кнопку "Ма­кет".

 

Рис.12. Диалоговое окно макета сводной таблицы.

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

- Перета­щить мышью кнопку Факультет в область строк, а кнопку Оплата за учёбу в об­ласть данных (в этой области обязательно что-то должно при­сутствовать).

G Примечание. Вместо Оплата за учёбу в поле теперь написано "Сумма по по­лю Оплата за учёбу ". Мастер сам вы­брал итоговую функцию "Сум­ма", потому что Оплата за учёбу явля­ется числовым полем.

· Четвертый шаг Мастера.. Предлагается поместить свод­ную таблицу на новый лист или на существующий лист. Указать расположение таблицы: на существующем листе - для наглядности и проверки результата

G В Excel 2000 возвращаемся к диалого­вому окну третьего шага.

Нажать кнопку Готово.

Результат – сводная таблица:

 

 

Отформатированная таблица:

 

 

· Изменение вида таблицы.

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

G Обратить внимание на вид курсора мыши при перемещении по сводной таблице и за ее пределами.

В результате получится горизонтальная таблица:

 

 

· Обновление сводной таблицы

Изменить одну из сумм в исходной таблице. Сводная таблица не изменится. Чтобы изме­нения в исходных данных отразились на сводной таблице, нужно выделить одну из ячеек таблицы и выбрать в меню команду Данные/Обновить данные.

· Изменение итоговой функции

Мастер сводных таблиц вы­брал по умолчанию суммирование оплат по факультетам. Но мож­но выбрать и другие итоговые функции таким образом:

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

- щёлкнуть кнопку "Поле сводной таблицы"

G в Excel 2000 — "Параметры поля"

- рассмотреть диалоговое окно " Вычисление поля сводной таблицы":

в окне "Опе­рация" перечислены итоговые функции: "Сумма", "Количество значений", "Среднее",...

- Выбрать "Максимум".

Получится таб­лица (заголовок отредактирован):

 

 

Задача 6.1. Вывести средние оплаты за учёбу по всем группам. С помощью кнопки списка (€) вывести только указанные группы.

Задача 6.2. Определить успеваемость по группам (средний балл по группе).

 

Пример 6.2. Сколько студентов заочной и очной формы обучения по группам и факультетам?

Решение.

- Вызвать Мастер сводных таблиц (см. пример 6.1.);

- на третьем шаге сформировать макет:

В область страниц помес­тить кнопку Факультет, в область строк перетащить кнопку Группа, в область столбцов — Форма обучения, в области данных мож­но разместить любое текстовое поле, например Фамилия. Появится строка "Количество по полю Фамилия".

G Примечание. Если поместить числовое поле в область данных, то получится суммирование по этому полю (умолчание!) и придётся менять "сумму" на "количество".

- На четвертом шаге указать размещение сводной таблицы. Полученная таблица:

 

G Пояснения.

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

2) С помощью выпадающего списка в ячейке «Группа» и «Форма обучения» можно вывести информацию по определённой группе и форме обучения.

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

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

Базы данных

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

База данных - логически взаимосвязанные данные конкретной предметной области.

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

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

С помощью запросов (критериев) из базы данных выводится необходимая пользователю информация.

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

База данных (БД) в Excel (список) - прямоугольная таблица, состоящая из строк и столбцов с однотипными данными.

 

Строки - записи БД,

Столбцы - поля

записей БД.

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

Весь инструмент работы с БД в Excel сосредоточен в пункте меню Данные.

G При вызове команды из меню Данные курсор должен находиться в одной из ячеек БД

Создание базы данных

При создании базы данных в Excel необходимо соблюдать правила:

1) На одном рабочем листе не рекомендуется помещать более одной базы данных. Если это не так, отделять данные хотя бы одной строкой или одним столбцом.

2) Необходимо присвоить столбцам имена - имена полей записи, которые могут состоять из нескольких строк заголовков, размещенных в одной строке таблицы.

3) Имена столбцов должны располагаться в первой строке базы данных (заглавная строка).

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

5) Не рекомендуется:

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

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

- объединять ячейки в заглавной строке.

6) В списках можно использовать формулы.

Порядок созданиябазы данных:

§ сформировать заглавную строку по приведенным выше правилам;

§ ввести данные в соответствующие поля записи одним из двух способов: обычным или с помощью формы.

§ отформатировать таблицу (стили, границы, заливка).

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

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

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

Достоинством ввода данных с помощью формы является автоматическое копирование формул и автоматическая поддержка форматов данных.

Пример 7.1. Создать базу данных движения товара в магазине «Ашамлыклар».

 

 

Рис. 13. Пример создания базы данных «Магазин»

Порядок созданиябазы данных:

1) оформить заголовок таблицы, учесть при этом:

- несколько строк в имени поля, размещенных в одной строке таблицы Excel (правило 2);

- перенос слов в строке;

- вертикальное выравнивание по центру в ячейках;

2) задать обрамление всей таблицы;

3) ввести нумерацию строк при помощи автозаполнения;

4) вставить формулы для количества остатка («Кол-во прихода» - «Кол-во расхода») и суммы остатка («Кол-во остатка» * «Цену расхода») в первую строку таблицы;

5) далее заполнить таблицу двумя способами:

· обычным - две строки, с копированием формул и заданием форматов;

· с помощью формы базы данных:

- выделить таблицу без первой строки шапки, но захватив последнюю, незаполненную строку данных таблицы;

- выполнить команду Данные - Форма;

- заполнить все поля записи БД, нажать Enter, затем следующую и т.д.

- завершить работу – кнопка Закрыть.

 

 

Рис. 14. Диалоговое окно формы (пример 7.1)

 

G Примечание к рис.14:

Командные кнопки диалогового окна Форма:

Добавить – добавить запись;

Удалить – удалить выбранную запись;

Вернуть – отменить любое изменение, сделанное в выбранной записи;

Назад – возврат к предыдущей записи;

Далее – переход к следующей записи;

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

Автофильтр

Чтобы использовать автофильтр в БД, необходимо:

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

· выполнить команду Данные - Фильтр - Автофильтр.

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

Список фильтра включает в себя:

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

<поле> = значение.

· (Все) – показать все записи таблицы

· (Первые 10) -выбор заданного числа наибольших

или наименьших значений поля.

· (Условие) -собственное условие пользователя.

· (Пустые) -ячейки, в которых нет данных.

· (Непустые) -ячейки, в которых есть данные.

¨ Для отмены фильтрации в одном поле нужно в списке фильтра выбрать (Все).

¨ Для отмены фильтров во всех полях:

Уникальное значение

a) Отбор записей по одному полю

Вывести данные о товарах кондитерского отдела.

Выполнение:

- Поставить курсор в БД «Магазин», включить автофильтр (Данные - Фильтр – Автофильтр);

- открыть список фильтра в поле «Отдел» (щёлчок по кнопке списка в указанном поле);

- выбрать уникальное значение – «Кондитерский»;

- обратить внимание на строку состояния в нижней части экрана: «Найдено записей: 4 из 13».

b) Отбор записей по нескольким полям – объединение критериев по принципу логического И.

Задание 2. Вывести данные об упакованных товарах кондитерского отдела.

Выполнение:

В дополнение к действиям примера а) открыть список фильтра в поле «Единица измерения» и выбрать уникальное значение – «упак.».

Проанализировать результаты и строку состояния, затем отменить фильтрацию:

Данные/Фильтр/ Показать все.

Первые 10)

Задание 3. Выбрать три самых дорогих товара.

Выполнение:

- Включить автофильтр в БД «Магазин»;

- открыть список фильтра в поле «Цена расхода»;

- выбрать пункт (Первые 10);

- в окне диалога задать параметры:

 

Рис. 16.

Окно диалога

(Первые 10)

G Примечания к примеру 2), задание 3, рис. 16.

· В первом списке окна можно задать любое число от 1 до 500;

· во 2-м списке выбирается одно из двух: «наибольших» или «наименьших»;

· в 3-м списке выбирается либо «элементов списка» либо, «% от количества элементов».

3) (Условие) -собственное условие пользователя для отбора записей задаётся в диалоговом окне «Пользовательский автофильтр».

 

 

Рис. 17. Диалоговое окно команды (Условие)

Пояснения к рис.17:

1 - название столбца, по которому формируется фильтр;

2 - поля со списками, которые содержат операторы сравнения;

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

4 - переключатели для объединения двух условий отбора одного и того же столбца по принципу логических операций И (конъюнкции) и ИЛИ (дизъюнкция)

В условиях поиска можно использовать шаблоны - символы * и ?, иногда их успешно заменяютспециальныеоператоры всписке 2 типа: «начинается с» и т.д..

Задание 4. Вывести список оставшихся товаров.

Выполнение:

Задание 5. Вывести список товаров, сумма остатка которых в интервале от 20 до 100 руб.

Выполнение: см. рис.19.

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

Выполнение:

4) (Пустые) – критерий отбора, по которому выводятся только записи с пустым значением поля.

(Непустые) - критерий отбора, по которому выводятся записи с непустым значением поля.

Самостоятельно!

Проверить работу пунктов 4) списка фильтра.

Задача 7.3.1. Вывести самый дешевый товар кондитерского отдела.

Задача 7.3.2. Выбрать в один список самые дешёвые (менее 20 руб.) и самые дорогие (более 100 руб.) товары.

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

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

Расширенный фильтр позволяет:

ü сразу копировать отфильтрованные записи в другое место рабочего листа;

ü сохранять критерий отбора для дальнейшего использования при изменяемых данных в БД;

ü выводить отфильтрованные записи только с нужными столбцами;

ü использовать логические операции

- ИЛИ для разных столбцов,

- И и ИЛИ для одного столбца, объединяя более двух условий;

ü создавать вычисляемые критерии;

ü выводить только уникальные значения

Для работы расширенного фильтра обязательны две области: область данных и область критериев поиска.

Иногда возникает потребность в дополнительной области - области вывода результатов поиска

Область данных - собственно баз