2.1 Работа с электронными таблицами в режиме баз данных
База данных (date base) – это совокупность хранимых в памяти компьютера данных, которые отображают состояние некоторой предметной области. Данные при этом взаимосвязаны и специальным образом организованы.
Ms Excel позволяет автоматизировать не только расчеты как таковые, но позволяет создавать и работать с разнообразными картотеками, системами учета, базами данных и т.п.
Вопросы сбора данных, их хранения, учета и обработки можно решить, имея систему управления списками. Термин список используется в Ms Excel для обозначения базы данных.
База данных – это особый тип рабочей таблицы, в которой не столько вычисляются новые значения, сколько размещаются большие объемы информации в связанном виде.
База данных представляет собой последовательность записей, содержащую однозначно определенную по категориям и последовательности информацию. Под каждую категорию данных в записи отводится отдельное поле, которому присваивается имя и отводится столбец.
Электронная таблица, оформленная в Ms Excel в виде списка, т.е. таблицы, строки которой содержат однородную информацию, представляет собой простейшую базу данных.
Программа Ms Excel включает набор средств и функций, позволяющих выполнять все основные операции, присущие базам данных.
База данных в Ms Excel состоит из строк и столбцов. Строки таблицы, оформленной в виде списка, называются записями, а столбцы – полями записей. Столбцам присваиваются уникальные имена полей, которые заносятся в первую строку списка – строку заголовка.
Создание базы данных обеспечивает интеграцию данных и возможность централизованного управления данными, снабжая информацией определенных пользователей.
Создание базы данных (БД) начинается с проектирования, т.е. с определения ее структуры: количества полей, их имен, типа каждого поля (символьный, числовой, дата и т.д.), длины каждого поля (максимального количества символов), типа данных (исходные, т.е. неизменяемые, или вычисляемые). Возможность использовать вычисляемые поля - основная особенность баз данных в Ms Excel.
Чтобы содержимое рабочего листа рассматривалось как база данных в Ms Excel, необходимо придерживаться строгих правил:
- каждому полю записи соответствует один столбец рабочего листа;
- столбцы базы данных должны идти подряд, без промежутков между ними;
- в первой строке каждого столбца должен быть указан заголовок соответствующего поля;
- заголовок поля должен занимать не более одной ячейки;
- содержимое ячейки заголовка должно быть уникально в пределах рабочего листа;
- записи базы данных должны идти непосредственно ниже строки заголовков;
- пустые строки не допускаются (признак окончания базы данных), т.е. записи должны идти подряд, без промежутков между ними.
На рабочем листе, содержащем список, выделяют следующие области:
- диапазон данных – область, где хранятся данные списка;
- диапазон критериев – область на рабочем листе, в которой задаются критерии для поиска информации. В диапазоне критериев указываются имена полей и отводится область для записи условий отбора;
- диапазон для извлечения – область, в которую Ms Excel копирует выбранные данные из списка.
Существуют следующие способы ввода данных в список:
- использование формы данных, которая автоматически создается после определения заголовка списка с помощью команды Данные → Форма;
- ввод данных во вставляемые в список пустые строки, т.е. непосредственно ввод данных;
- использование средства Автоввод из списка для ускорения работы;
- использование форм Ms Access и дальнейший перенос данных на лист Ms Excel;
- использование мастера шаблонов для преобразования рабочего листа в Ms Excel в форму;
- применение VBA – соответствующая программа будет предоставлять форму или окно диалога для ввода данных и их последующего помещения в определенные ячейки рабочего листа Ms Excel.
К наиболее часто используемым способам ввода данных в базу данных относятся первые три способа.
Работа с подготовленным списком в Ms Excel может осуществляться по следующим направлениям:
1. Сортировка.
Любая информация, в том числе и база данных, должна быть упорядочена. Каждая база данных имеет некоторый предпочтительный порядок поддержания и просмотра записей. Процесс упорядочения базы данных называется сортировкой. Порядок сортировки записей определяется конкретной задачей. При сортировке изменяется порядок следования записей в базе данных или таблице. Таким образом, происходит изменение базы данных.
Однако необходимо иметь возможность восстановить исходный порядок следования записей. Универсальным средством для этого является введение порядковых номеров записей. В сочетании со средствами Ms Excel по восстановлению данных это полностью защитит базу от потерь при случайных сбоях в работе.
Команда Сортировка устанавливает порядок строк в таблице в соответствии с содержимым конкретных столбцов.
Сортировка по возрастанию предполагает следующий порядок:
- числа
- текст, включая текст с числами (почтовые индексы, номера автомашин)
- логические значения
- значения ошибок
- пустые ячейки
Сортировка по убыванию происходит в обратном порядке. Исключением являются пустые ячейки, которые всегда располагаются в конце списка.
При сортировке по возрастанию текстовые данные упорядочиваются в алфавитном порядке от А до Я. Числовые данные упорядочиваются по возрастанию значений от минимального к максимальному. Даты упорядочиваются от наиболее ранней даты к наиболее поздней. При выборе переключателя по убыванию порядок сортировки изменяется на противоположный.
Рис.16. Диалоговое окно Сортировка
Дополнительные параметры Сортировки Затем и В последнюю очередь, позволяют определить порядок вторичной сортировки для записей, в которых имеются совпадающие значения.
Функция Параметры Сортировки позволяет:
- определить пользовательский порядок сортировки для столбца;
- сделать сортировку чувствительной к использованию прописных и строчных букв;
- изменить направление сортировки (вместо сортировки сверху вниз установить сортировку слева направо).
Пример использования функции Сортировки приведен на рисунке17.
Рис. 17. Пример использования Сортировки списка
2. Фильтрация (отбор данных).
Фильтрация – извлечение записей данных из списка в соответствии с некоторыми требованиями (критериями).
Фильтр обрабатывает весь список в электронной таблице и отображает на экране только те данные, которые требуется. Преимущество этого подхода заключается в том, что с такой выборкой можно работать автономно как с новой таблицей, не загружая экран избыточной информацией.
Для фильтрации данных в Ms Excel существует 2 средства: автофильтр и расширенный фильтр.
При использовании Автофильтра фильтрация осуществляется непосредственно на исходных данных.
С помощью Автофильтра можно создать фильтры трех типов: по списку значений, по формату или по условиям. Все они являются взаимоисключающими в пределах диапазона ячеек или столбца таблицы.
Критерии расширенной фильтрации списка можно определить, непосредственно задав их на рабочем листе. Преимущество этого способа состоит в том, что пользователь всегда имеет четкое представление о применяемых критериях и при необходимости может их изменять. При использовании расширенного фильтра критерии задаются в отдельной области.
Данные базы данных могут быть подвергнуты анализу. Ms Excel предоставляет широкие возможности для проведения анализа данных, к которым относятся:
1. Структуризация рабочих листов, цель которой заключается в разбиении данных, содержащихся на рабочем листе, на определенные уровни детализации. Используя структуру, легче проводить анализ и сравнение данных, поскольку в таком случае при необходимости группы уровня могут быть скрыты, а затем снова отображены.
На рисунке 18 приведен пример таблицы, сформированной для группировки данных.
Рис.18. Пример таблицы для группировки данных
На рисунке 17 приведена таблица данных после группировки. В левой верхней части окна можно увидеть кнопки 1 и 2, появившиеся после группировки. Они служат для группового отображения или скрытия данных.
Каждый из уровня группировки может содержать подуровень.
Ранее сгруппированные строки всегда можно разгруппировать.
Рис.19. Структура таблицы со сгруппированными строками
2. Автоматическое подведение итогов: итоги необходимы для создания разнообразных отчетов и для обобщения большого количества однотипной информации. Один из способов обработки и анализа базы данных состоит в подведении различных итогов. С помощью команды Итоги можно вставить строки итогов в список, осуществив суммирование данных нужным способом.
Команда Итоги может выполнять следующие операции:
- выбрать одну или несколько групп для автоматического подведения итогов по этим группам
- выбрать функцию для подведения итогов
- выбрать данные, по которым нужно подвести итоги
Кроме подведения итогов по одному столбцу, автоматическое подведение итогов позволяет:
- выводить одну строку итогов по нескольким столбцам
- выводить многоуровневые, вложенные строки итогов по нескольким столбцам
- выводить многоуровневые строки итогов с различными способами вычисления для каждой строки
- скрывать или показывать детальные данные в этом списке
3. Консолидация данных - предназначается для обобщения однородных данных и выполняется в том случае, когда нужно подытожить данные, хранящиеся на разных листах или в различных книгах. При помощи функции консолидации для значений из несмежных диапазонов можно выполнить те же операции, что и при автоматическом определении промежуточных итогов: вычислить сумму, минимальное, максимальное или среднее значение и т. д.
В зависимости от способа организации исходные данные для Консолидации можно задавать тремя способами: с помощью формул, содержащих ссылки, по расположению ячеек и по заголовкам строк или столбцов.
Способ расчета с помощью формул, содержащих ссылки, следует использовать в тех случаях, когда исходные данные расположены бессистемно.
В этом случае для расчета итоговых данных составляются обычные или так называемые, трехмерные формулы — ссылки вида 'Лист1:Лист3' !D3, которые содержат ссылки на диапазоны, включающие ячейки разных листов.
Методы определения исходных данных для консолидации — по расположению ячеек и по заголовкам строк или столбцов — реализуются в диалоговом окне Консолидация.
В способе, использующем расположение ячеек, исходные данные для функции консолидации задаются в диалоговом окне в виде ссылок на диапазоны. При этом требуется, чтобы исходные данные в каждом диапазоне имели одинаковую структуру.
В способе, использующем заголовки строк или столбцов, ячейки, содержащие исходные данные для функции консолидации, идентифицируются по заголовкам, а не путем указания их адресов. В этом случае не обязательно, чтобы исходные данные на разных листах были расположены одинаково.
4. Сводные таблицы - представляют собой средства для группировки, обобщения и анализа данных, находящихся в списках Ms Excel или таблицах, созданных в других приложениях.
Сводная таблица является интерактивным средством, позволяющим получить данные из некоторого источника (список, таблица, база данных) и выполнить их анализ и просмотр различными способами. С помощью сводной таблицы можно просмотреть необходимую для анализа часть имеющейся информации, отфильтровать данные и разместить результаты на различных рабочих страницах, отсортировать данные, автоматически сгенерировать итоги.
Сводную таблицу можно создать на базе области таблицы, целой таблицы или нескольких таблиц. Исходные данные для сводных таблиц должны быть организованы в виде списка, каждая строка которого содержит одни и те же категории информации, приведенные в определенном порядке. В первой строке списка должны находиться заголовки столбцов. Они используются для создания полей сводной таблицы и идентификации отдельных элементов данных, то есть уникальных значений полей, взятых из списка. Несмотря на то, что поля создаются на основе столбцов, содержащиеся в них данные можно размещать как в столбцах, так и в строках сводной таблицы.
Сводная таблица включает следующие поля:
- области столбцов и строк - содержат поля, по которым производится сравнение или анализ;
- область элементов данных - отображает поля, предназначенные для расчетов итоговых показателей;
- область страниц - используется, в том случае, когда нужно создать несколько однотипных отчетов, задаваемых полями в других областях.
Размещение полей непосредственно в рабочем листе дает определенные преимущества, обеспечивая наглядность построения при наличии небольшого объема данных. Однако при работе с большим количеством данных более удобным и быстрым является режим макета. Если при перетаскивании поля непосредственно в область данных рабочего листа туда перемещаются также все данные, связанные с этим полем, то в режиме макета выполняется лишь размещение кнопок полей, а относящиеся к ним данные не затрагиваются.
После создания сводной таблицы справа от поля страницы и в остальных полях сводной таблицы появляются кнопки co стрелками. После щелчка на такой кнопке появляется окно со списком элементов данных соответствующего поля. Устанавливая и сбрасывая флажки в этом окне, можно управлять отображением элементов поля.
Сводная таблица динамически связана с базой данных, использовавшейся при ее создании. Если значения в базе данных изменились, при выполнении функции обновления, Ms Excel обновляет сводную таблицу с учетом всех произведенных изменений.
Дата: 2019-05-29, просмотров: 163.
