Geum.ru

6 Засоби структурування, аналізу та обробки даних в ms excel Табличні бази даних

Вид материалаДокументы

Содержание


Фільтрація та сортування
Зведені таблиці
Модель «что-если»
Итоговый отчет по сценариям
Побудова діаграм та оформлення документів
Подобный материал:

Тема 6.4. Засоби структурування, аналізу та обробки даних в MS Excel

Табличні бази даних.


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

В Microsoft Excel имеется набор функций, облегчающих обработку и анализ данных в списке. Для использования этих функций, необходимо выполнять рекомендации:
  • На листе не следует помещать более одного списка. Некоторые функции обработки списков, например, фильтры, не позволяют обрабатывать несколько списков одновременно.
  • Между списком и другими данными листа необходимо оставить, по меньшей мере, одну пустую строку и один пустой столбец. Это позволяет Microsoft Excel быстрее обнаружить и выделить список при выполнении сортировки, наложении фильтра или вставке вычисляемых автоматически итоговых значений.
  • В самом списке не должно быть пустых строк и столбцов. Это упрощает идентификацию и выделение списка.
  • Важные данные не следует помещать у левого или правого края списка; после применения фильтра они могут оказаться скрытыми.
  • Заголовки столбцов должны находиться в первой строке списка. Они используются Microsoft Excel при составлении отчетов, поиске и организации данных.
  • Список должен быть организован так, чтобы во всех строках в одинаковых столбцах находились однотипные данные.
  • Перед данными в ячейке не следует вводить лишние пробелы, так как они влияют на сортировку.
  • Не следует помещать пустую строку между заголовками и первой строкой данных.

Для прокрутки списка фиксируется верхняя строка: команда ОкноЗакрепить области.

Команда Доступ к книге из меню Сервис позволяет нескольким пользователям одновременно работать с одним и тем же списком.

Фільтрація та сортування


Excel предоставляет разнообразные способы сортировки диапазонов рабочего листа: сортировать строки и столбцы в возрастающем или убывающем порядке, с учетом или без учета регистра букв, задавать свой собственный порядок сортировки. Для создания нового пользовательского порядка сортировки используется команда Сервис  Параметры  Списки, выбирается Новый список и всекции Элементы списка вводится новый список в том порядке, в котором будет использоваться при сортировке. Чтобы при сортировке использовать пользовательский список выполняется команда Данные  Сортировка  Параметры и в списке Порядок сортировки по первому ключу выбирается нужный список.

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

Команда Расширенный фильтр, в отличие команды Автофильтр, позволяет:
  • Задавать условия, соединенные логическим оператором ИЛИ, для нескольких столбцов. Например, можно выбрать из списка всех сотрудников старше 30 лет или имеющих оклад больше 500 гривен.
  • Задавать вычисляемые условия. Например, выводить на экран список только тех сотрудников, у которых оклад хотя бы на 20% выше среднего.

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

Для анализа отфильтрованных строк можно использовать команду Данные  Итоги.

Зведені таблиці


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

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

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

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

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

Сценарії


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

Таблицы данных являются хорошим средством в относительно простых ситуациях, когда используется одна или две переменных. При моделировании более сложных задач. Использующих до 32 переменных, можно обратиться к команде Сервис  Диспетчер сценариев.

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

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

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

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

Побудова діаграм та оформлення документів


С помощью Excel можно создавать сложные диаграммы для данных рабочего листа. Можно выбирать их из широкого диапазоан диаграмм, объединять основные типы диаграмм. Например, можно наложить график н а гистограмму, чтобы отобразить на диаграмме цена акций и объемы их продаж. Можно создать "рисованные" диаграммы, которые для представления значений используют графические рисунки вместо обычных столбцов и линий. Перед использованием диаграммы нужно выделить любую ячейку из тех, что содержит исходные данные диаграммы и выполнить команду Вставка  Диаграмма. (можно нажать клавишу F11, по умолчанию, диаграмма будет создана на отдельном листе). Изучить на лабораторной работе.

Восприятие информации можно резко улучшить, применяя различные приемы форматирования. Можно изменить вид самих электронных таблиц и их ячейки, причем как до ввода данных, так и после.Это достигается выполнением команды Формат  Ячейки (или из контекстного меню). В Excel существует инструмент, который называется "автоформат" – набор заданных параметров форматировани таблиц. Для его выполнения выполнить команду Формат  Автоформат. Изучить на лабораторной работе.

Контрольні питання та завдання:
  1. Розробіть таблицю з назвами підприємств, їх профілем та телефонами. Забезпечите пошук та відбір у цій таблиці.
  2. Розробіть приклад таблиці продажу товарів, що належать до категорій “Обчислювальна техніка”, “Програмне забезпечення”, “Література”. Внесіть до неї по 10 екземплярів товарів кожної категорії найменувань з розкладом по датам продажу. Побудуйте зведену таблицю для трьох категорій товарів за весь період. Побудуйте зведену таблицю для всіх товарів незалежно від категорії але з розкладом по місяцях.
  3. Розробіть приклад таблиці курсів долара, марки та рубля по тижнях 1998 року. Побудуйте діаграму зміни курсів залежно від часу та графік залежності курсу рубля від курсу долара.