СОЗДАНИЕ БАЗ ДАННЫХ, ИЛИ РАБОТА СО СПИСКАМИ

В Microsoft Excel в качестве базы данных можно использовать список.

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

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

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

Рекомендации по созданию списка на листе книги

1. Размер и расположение списка

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

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

·                    В самом списке не должно быть пустых строк и столбцов. Это упрощает идентификацию и выделение списка.

·                    Важные данные не следует помещать у левого или правого края списка; после применения фильтра они могут оказаться скрытыми.

2. Заголовки столбцов

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

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

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

3. Содержание строк и столбцов

·                    Список должен быть организован так, чтобы во всех строках в одинаковых столбцах находились однотипные данные.

·                    Перед данными в ячейке не следует вводить лишние пробелы, так как они влияют на сортировку.

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

Все действия со списками (базой данных) выполняет команда главного меню ДАННЫЕ.

Команда ДАННЫЕ/ФОРМА

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

С помощью формы можно:

1.              заносить данные в таблицу;

2.              просматривать или корректировать данные;

3.              удалять данные;

4.              отбирать записи по критерию.

Введите данные для изучения команды:

Реализация продуктов
дата	продукт	цена закупки	кол	цена продажи	продано	прибыль
05.10.2008	молоко	45	12	90	10
05.10.2008	хлеб	25	45	50	30
05.10.2008	конфеты	21	25	42	25
06.10.2008	пирожки	56	36	112	30
06.10.2008	хлеб	25	23	50	20
06.10.2008	молоко	45	23	85	20
07.10.2008	молоко	45	15	95	15
07.10.2008	хлеб	20	45	40	44
07.10.2008	конфеты	55	50	110	50

 

Рис. 1. Окно формы для занесения, просмотра, удаления и поиска записей

1.   Вставка записей с помощью формы

При использовании диалогового окна команды Форма вновь создаваемые записи заносятся в конец списка.

1.   Выберите команду Форма в меню Данные.

2.   Нажмите кнопку Добавить.

3.   Введите поля новой записи, используя клавишу TAB для перемещения к следующему полю. Для перемещения к предыдущему полю используйте сочетание клавиш SHIFT+TAB.

4.   Чтобы добавить запись в список, нажмите клавишу ENTER. По завершении набора последней записи нажмите кнопку Закрыть, чтобы добавить набранную запись и выйти из формы.

Примечание

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

2. Поиск записей в списке с помощью формы

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

Нажмите кнопку Далее для перехода к следующей записи списка и Назад — для перехода к предыдущей.

1.Чтобы задать условия поиска или условия сравнения, нажмите кнопку Критерии. Введите критерии в форме. Так, для поиска всех продуктов, начинающихся на букву Х, достаточно в поле фамилии набрать Х. Для поиска записей с ценой продажи продукта, большей 25, в поле цена продажи следует ввести >25.

Рис 2. Выбор критерия для поиска записи с помощью формы.

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

3. Редактирование записей с помощью формы

Для исправления значений (но не формул) в любом поле записи:

1.              Установите курсор в любую ячейку списка и выберите команду Данные > Форма.

2.              Найдите требуемую запись с помощью кнопок Назад и Далее.

3.              Отредактируйте запись.

4.              Нажмите кнопку Закрыть.

4. Удаление записей с помощью формы

Для удаления записи:

1.Установите курсор в любую ячейку списка и выберите команду Данные >■ Форма.

2.Найдите требуемую запись и нажмите кнопку Удалить.

3.Подтвердите удаление нажав ОК.

4.Нажмите кнопку Закрыть.

Команда ДАННЫЕ – ФИЛЬТР

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

1. Выбор элементов списка с помощью автофильтра

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

•  Установить курсор в любую ячейку списка, задать команду Данные >■ Фильтр, а затем выбрать пункт Автофильтр.

Рис.3.  Окно включения режима  Автофильтра

•  Нажать на кнопку со стрелкой в том столбце, по которому надо фильтровать данные.

•  Выбрать любой элемент из списка.

Рис 4. Окно настройки простого Автофильтра

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

Рис 5. Окно настройки  составного условия Автофильтра

Рис 6. Результат фильтрации

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

Для восстановления всех записей списка необходимо задать команду Данные > Фильтр >Отобразить все или же в раскрываю­щемся списке автофильтра выбрать пункт Все. Для отмены фильт­рации необходимо повторно выбрать команду Данные > Фильтр >■ Автофильтр.

2.  Фильтрация списка с использованием сложных критериев

В данном случае должна быть выполнена следующая процедура:

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

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

Если на экран надо вывести записи, удовлетворяющие одному из критериев (логическое ИЛИ), то ввод условий производится в разные строки одного столбца.

2.     Установить курсор в любую ячейку списка и задать команду Данные >■ Фильтр, а затем выбрать пункт Расширенный фильтр.

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

•  В поле Диапазон критериев указать диапазон тех ячеек, где размещается область критериев. В нашем примере А15:Н17.

Например, из списка Продукты требуется отобрать Молоко с ценой продажи >90, но <100 и конфеты дороже 40 руб, но дешевле 100руб за кг. Тогда область критериев и окно вызова расширенного фильтра будет выглядеть так:

Рис.7. Формирование области критериев для расширенного фильтра

Рис. 8. Результат действия расширенного фильтра на месте

3.   Если же результат нужно поместить в целевую область, то следует выбрать  параметр Скопировать результат в другое место.

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

Рис.9. Формирование параметров расширенного фильтра для целевой области

•   Нажать кнопку ОК.

 

 

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

Рис. 10. Размещение результата расширенного фильтра в другое место

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

Рис. 11. Формирование задания для расширенного фильтра с избранными полями

•   Нажать кнопку ОК.

Рис. 12. Результат расширенного фильтра для избранных полей

Для восстановления списка следует выбрать команду Данные >■ Фильтр >• Показать все.

Команда ДАННЫЕ – ИТОГИ

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

Рис. 13. Результат вычисления суммы прибыли за реализацию молока

Чтобы получить данные за реализацию, например, конфет следует перенастроить фильтр:

Рис. 14. Подведение итогов по реализации конфет с использованием автофильтра.

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

Команда ДАННЫЕ – СОРТИРОВКА

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

Чтобы выполнить сортировку списка, делаем активной любую ячейку внутри списка (установить курсор в любую ячейку внутри списка), затем выбираем команду “Данные – сортировка”. В открывшемся окне  определяем поле для сортировки и ее порядок. Возможны два варианта сортировки – по возрастанию и по убыванию. Для текстового поля это означает в алфавитном порядке и наоборот.

Упражнение 1

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

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

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

Рис.15. Формирование заказа на подведение итогов по реализации продуктов за каждый день

 

Рис 14. Страничка с подведенными итогами за каждый день реализации.

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

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

Алгоритм выполнения работы:

Выполнить сортировку по полю Продукт, Затем по полю Дата

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

Еще раз активизировать инструмент Итоги, установив новые параметры:

По полю дата, операция Сумма, значение поля Прибыль

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

Рис.15. Настройка режима подведения вложенных итогов

В итоге получим:

Рис.16. Вложенные итоги. Просмотр группировки

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

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

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

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

1.Установите курсор в любую ячейку списка и выберите команду Данные >■ Сводная таблица.

2.В открывшемся диалоговом окне Мастер сводных таблиц отметьте опцию «в списке или базе данных Microsoft Excel».

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

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

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

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

Используя панель инструментов Сводные таблицы можно изменять вид сводной таблицы.