Управление базами данных в Excel

Базы данных в Excel

Microsoft Excel содержит широкий набор средств для создания и эффективного управления базами данных или списками.

Базами данных или списками в Excel принято называть любую совокупность связанной информации, объединенной по некоторому признаку и структурированной специальным образом в форме списка. Списки Microsoft Excel предоставляют средства, упрощающие управление и анализ групп связанных данных на листе Excel. Для создания списка нужно выделить диапазон данных, из которых необходимо создать список, и выполнить команду Данные / Список / Создать список. Если у выбранных данных есть заголовки, то можно установить флажок Список с заголовками. Если имя списка не задано, Excel автоматически присвоит ему имя Столбец1, Столбец2 и т.д. После создания списка он будет выделен синей границей. Кроме того, в каждый столбец списка автоматически добавляется раскрывающийся автофильтр, а вниз списка помещается строка ввода, отмеченная â (рис. 19).

 

Рисунок 19. Пример базы данных в Excel

 

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

 

Одним из основных средств управления списками является одно- или многоуровневая сортировка с учетом одного или нескольких условий. Чтобы выполнить сортировку списка, необходимо установить курсор в любой ячейке списка, в меню Данные выбрать пункт Сортировка и установить соответствующие параметры сортировки (рис. 20).

 

Рисунок 20. Диалоговое окно Сортировка диапазона

 

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

 

Фильтрация данных

 

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

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

 

Рисунок 21. Выбор способа фильтрации списка

 

При выборе пункта Условие… появляется окно Пользовательский автофильтр (рис. 22). В верхнем правом списке следует выбрать один из операторов (равно, больше, меньше и др.), в поле справа – выбрать значение. В нижнем правом списке можно выбрать другой оператор и значение. При включении переключателя И отбираются только записи, удовлетворяющие обоим условиям. При включенном переключателе ИЛИ будут выводиться записи, удовлетворяющие хотя бы одному из условий.

 

Рисунок 22. Диалоговое окно Пользовательский автофильтр

Для вывода нескольких записей с самым большим или самым маленьким значением по любому полю следует в скрытом списке поля выбрать пункт Первые 10. В диалоговом окне Наложение условия по списку в первом поле со счетчиком необходимо выбрать количество записей, а в поле справа выбрать – наибольших или наименьших (рис. 23).

 

Рисунок 23. Диалоговое окно Наложение условия по списку

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

Расширенный фильтр используется для создания более сложных условий отбора. В качестве условия отбора можно использовать вычисляемые значения, являющиеся результатом выполнения формулы. Для того чтобы применить расширенный фильтр, нужно скопировать заголовки списка в любое свободное место (на рис. 24 - диапазон А10:Е10) и под заголовками ввести в соответствующие ячейки условия отбора.

 

Рисунок 24. Пример создания расширенного фильтра

 

Затем нужно выполнить команду Данные/ Фильтр/ Расширенный фильтр и в диалоговом окне Расширенный фильтр (рис. 25) указать диапазон для фильтрации (в данном примере - А1:Е6) и диапазон условий отбора (А10:Е11).

 

Рисунок 25. Диалоговое окно Расширенный фильтр

Вставка итогов

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

 

Рисунок 26. Пример вставки промежуточных итогов

 

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

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

 

Рисунок 27. Диалоговое окно Промежуточные итоги

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

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

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

 

Рисунок 28. Создание макета сводной таблицы с помощью мастера

ЛАБОРАТОРНАЯ РАБОТА № 8

 

ЦЕЛЬ: изучить способы анализа и обработки данных в Microsoft Excel. Научиться использовать списки Microsoft Excel в качестве базы данных, изучить основные средства управления базами данных (сортировка и фильтрация данных, вставка промежуточных итогов, построение сводных таблиц и диаграмм).

 

СОДЕРЖАНИЕ РАБОТЫ

Списки. Управление списками

1. Создайте новую рабочую книгу Договоры. xls.

2. Создайте таблицу по приведенному ниже образцу:

 

 

3. Для столбца Сумма договора установите денежный формат в рублях.

4. Столбцы Сумма договора и Адрес предприятия заполните самостоятельно.

5. На основе таблицы создайте список с имеющимися заголовками.

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

6. Отсортируйте записи в таблице по дате. Отмените сортировку.

7. Отсортируйте записи по полю Юрист в обратном алфавитном порядке. Отмените сортировку.

8. Отсортируйте записи сначала по полю Наименование предприятия по возрастанию, затем подате по возрастанию.

Фильтрация данных

9. Отберите из таблицы сведения о договорах, заключенных юристом Бураком. Отмените фильтрацию.

10. Отберите договоры, заключенные с АО "Спартак". Отмените фильтрацию.

11. С помощью пользовательского автофильтра Условие… отберите данные о договорах, заключенных юристами Бураком и Жданок. Отмените фильтрацию.

12. Отберите данные о договорах, заключенных в мае или июне. Отмените фильтрацию.

13. Отберите договоры, заключенные на сумму больше 1000000р. и меньше 3000000 р. Отмените фильтрацию.

14. Введите в базу данных еще 5 записей о неоплаченных договорах.

15. С помощью автофильтра Первые 10 … отберите три договора, по которым имеется наибольшая задолженность. Отмените фильтрацию.

16. Отберите два договора с наименьшей суммой задолженности. Отмените фильтрацию.

17. Используя расширенный фильтр, отберите договоры, по которым сумма задолженности превышает 1000000 р. Отмените фильтрацию.

18. Отберите все договоры, заключенные 13,14 и 15 мая. Отмените фильтрацию.

Вставка итогов

19. Отберите договоры, по которым еще не произведена оплата.

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

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

22. Отмените фильтрацию и проверьте изменение общего итога и итогов для каждого предприятия.

23. Скройте все записи о договорах с предприятием АО "Спартак", кроме строки итогов. Отобразите записи.

24. Используя знаки структуры , , , расположенные в левой части окна программы, скройте/ отобразите все промежуточные итоги.

25. Скопируйте полученную таблицу на лист Итоги.

26. В исходной таблице удалите общий и промежуточные итоги.

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

27. На основе списка постройте сводную таблицу и разместите ее на этом же листе:

- в область страницы поместите поле Юрист;

- в область столбцов – Дата;

- в область строк – Наименование предприятия;

- в область данных – Сумма договора.

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

29. В сводной таблице отберите договоры, заключенные Бураком. Отмените фильтрацию.

30. Добавьте в сводную таблицу в область Страница поле Отметка об оплате: на панели инструментов Сводная таблица в списке Сводная таблица выберите Мастер сводных таблиц / Макет / перетащите мышью поле Отметка об оплате в область Страница.

31. В сводной таблице отберите договоры, по которым еще не была произведена оплата.

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

33. Измените размещение сводной таблицы: поместите сводную таблицу на другой лист рабочей книги Сводная таблица.

34. Постройте сводную диаграмму с параметрами по выбору пользователя.

35. Сохраните изменения в рабочей книге.

КОНТРОЛЬНЫЕ ВОПРОСЫ

1. Перечислите способы фильтрации данных.

2. Как отменить фильтрацию данных?

3. Можно ли отсортировать список по нескольким полям?

4. Что такое сводная таблица?

5. Как выполнить фильтрацию данных в сводной таблице?

6. Что такое промежуточные итоги?

ДОМАШНЕЕ ЗАДАНИЕ № 8

1. В таблицу на листе Сведения рабочей книги Домашние задания добавьте несколько записей. Скопируйте полученную таблицу на лист 2, лист 3, лист 4.

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

3. С помощью расширенного фильтра в таблице на листе 3 отберите налогоплательщиков, у которых сумма налога на начало года не превышает 100000.

4. В таблице на листе 4 отберите сведения о задолженностях налогоплательщиков - АО "Крыница" и АО "Купалинка".

5. Сохраните изменения в рабочей книге.