Средства работы с табличными базами данных

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

Консолидация рабочих листов

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

Например, рассмотрим обработку данных об объемах продаж фирмы «BP» за первый квартал текущего года.

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

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

2. Выбрать команду Данные| Работа с данными| Консолидация. На экране отобразится диалоговое окно Консолидация.

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

4. В поле Ссылка указать исходный диапазон данных (непосредственно с помощью мыши), который должен быть консолидирован. В данном случае Январь!$A$1:$B$7. В результате диапазон будет добавлен в Список диапазонов.

5. Повторяем пп. 3, 4 для других консолидируемых диапазонов Февраль!$A$1:$B$7 и Март!$A$1:$B$7.

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

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

8. Нажимаем кнопку ОК.

В результате получаем рабочий лист Консолидация.

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

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

Рассмотрим пошаговое описание сортировки на примере зачетной ведомости студентов двух групп, которые надо объединить в один алфавитный список.

Для этого выделяем сортируемый список вместе с заголовкам и выполняем команды: Данные| Сортировка и фильтр. На экран выводится диалоговое окно Сортировка. В этом олне можно задать дополнительные прараметры (сортировать столбцы), порядок сортировки отдельно для каждого из полей, а также имеется ли в этом списке строка заголовка. При этом в списках Сортировать по и Затем по определяется, по каким полям диапазона будет идти сортировка

В результате получаем список

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

Удаление повторяющихся значений

Удалить повторяющиеся данные из выделенного диапазона можно выполнив команду Данные| Работа с данными| Удалить дубликаты. На экране отобразится диалоговое окно Предупреждение об удалении дубликатов.

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

После нажатия кнопки ОКна экран будет выведена информация о количестве удаленных повторений.

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

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

Для того чтобы применить средство автофильтрации для списка необходимо:

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

2. Выполнить команду Данные| Сортировка и фильтр| Фильтр.

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

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

Промежуточные итоги

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

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

1. Выделить диапазон, подлежащий обработке.

2. Выполнить команду Данные| Структура| Промежуточный итог.

3. В открывшемся диалоговом окне Промежуточные итоги

Установить в качестве значения поля При каждом изменении в названии нужного столбца таблицы;

В поле Операция задать производимую операцию;

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

4. Установить флажок Заменить текущие итоги.

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

6. Установите флажок Итоги под данными − в этом случае окончательные итоги будут выведены в нижней части таблицы

7. Нажать ОК.

Например, найдем средний бал в каждой группе групп.

Окончательно, будем иметь:

Применение функций базы данных

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

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

имя_функции(база_данных, поле, критерий).

Аргументы функции базы данных предоставляют следующую информацию:

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

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

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

Например, имеется таблица, содержащая данные о количестве деталей.

Используя функцию БДСУММ найдем суммы качественных и бракованных деталей каждого вида для цеха № 1. Предварительно на рабочем листе создадим критерии:

Затем, заполняем таблицу для цеха № 1.

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

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

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

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

Сводные таблицы создаются одним из следующих способов:

с помощью средства Мастер сводных таблиц и диаграмм;

командой на ленте Вставка| Таблицы| Сводная таблица| сводная таблица.

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

1. Перейти на вкладку Файл;

2. Открыть Параметры;

3. Выбрать Настройки;

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

5. Нажать кнопку Добавить

6. Нажать ОК.

Для того чтобы создать сводную таблицу:

1. На панели быстрого доступа в поле Вставка щелкнуть левой кнопкой мыши по значку Мастер сводных таблици диаграмм. На экране появится окно Мастер сводных таблици диаграмм - шаг 1 из 3. Установите переключатели так, как это показано на рисунке и нажмите кнопку Далее.

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

3.На третьем шаге выбираем расположение таблицы на новый лист

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

Замечание 1. Для создания сводной таблицы средствами ленты выполняем команды: Вставка| Таблицы| Сводная таблица| Сводная таблица. В открывшемся окне Создание сводной таблицы указываем диапозон и затем нажимаем ОК.

Замечание 2. Если структура сводной таблицы не отобразится, то следует выполнить команды: Работа со сводными таблицами| Параметры| Сводная таблица| Параметры. В диалоговом окне Параметры сводной таблицы перейти на вкладку Вывод и установить флажок Классический макет сводной таблицы (разрешено перетаскивание полей).

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

1. Перетащить поле Цех в область Названия строк. Тем самым задаем групировку по цехам.

2. Перетащить поле Квартал в область Названия столбцов (задаем группировку по кварталам);

3. Перетащить поле Деталь в область Фильтр отчета. В результате сводная таблица будет либо представлять данные по отдельной детали (выбранной в списке), либо сразу по всем деталям, если выбран вариант Все.

4. Перетащить поля Качественные, Брак и Всего в область ∑ Значения.

Полученный результат показан на рисунке.

При создании сводной таблицы в графе Общий итог подсчитывается сумма значений из поля данных.

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

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

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

изменения данных в поле данных;

появление новых или изменение существующих элементов;

удаление полей данных.