Работа с таблицей Excel как с базой данных

Оглавление 

1. Обработка  информации в электронных таблицах Excel или списках. Основные понятия  и требования к спискам

2. Экономико-математические  приложения Excel

3. Решение  уравнений и задач оптимизации

3.1 Подбор  параметров

3.2 Команда  «Поиск решения»

3.3 Диспетчер  сценариев «что-если» 

 

      1. Обработка информации  в электронных  таблицах Excel или  списках. Основные  понятия и требования  к спискам 

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

       

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

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

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

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

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

     При создании списка необходимо выполнить  определенные требования:

• Чтобы редактор автоматически распознавал список как базу данных и обрабатывал данные при выполнении команд обработки необходимо на рабочем листе располагать один список;
• Формат шрифта заголовков (подписей) столбцов или имен полей в списках должен отличаться от формата шрифта записей. Обычно шрифту заголовкам столбцов назначается полужирный шрифт, а ячейкам для заголовков присваивается текстовый формат;
• Ячейки под заголовками столбцов необходимо отформатировать в соответствии с данными, которые будут вводиться в эти ячейки (например, установить денежный формат, выбрать выравнивание и т.д.);
• Для обеспечения автоматического форматирования введенных данных в список целесообразно активизировать команду "Расширение форматов и формул". Для этого необходимо установить флажок "Расширять форматы и формулы в диапазонах данных" в окне диалога "Параметры" на вкладке "Правка", которое открывается командой "Параметры" в меню Сервис;
• В списке не должно быть пустых записей (строк) и полей (столбцов), даже для отделения имен полей от записей следует использовать границы ячеек, а не пустые строки.

     После выполнения подготовительных работ  по созданию списка можно переходить к введению данных в список. 

2. Экономико-математические  приложения Excel 

     К типичным экономико-математическим приложениям Excel относятся:

• структуризация и первичная логическая обработка данных;
• статистическая обработка данных, анализ и прогнозирование;
• проведение финансово-экономических расчетов;
• решение уравнений и оптимизационных задач.

     Структуризация  и первичная логическая обработка  данных

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

     Функции, реализующие статистические методы обработки и анализа данных, в Excel реализованы в виде специальных  программных средств - надстройки Пакета анализа, которая входит в поставку Microsoft Office и может устанавливаться  по желанию пользователей. Установка  надстройки Пакет анализа осуществляется так же, как и установка других надстроек с помощью команды  Сервис/Надстройка. Далее необходимо установить флажок перед пунктом Пакет анализа и нажать ОК. 

 

     После успешной установки надстройки в  меню Сервис появится пункт: Анализ данных, а в окне мастера функций становится доступной категория функций - Статистические. 

       

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

• описательный стстистический анализ (описательная статистика);
• ранжирование данных (Ранг и персентиль);
• графический анализ (Гистограмма);
• прогнозирование данных (Скользящее среднее. Экспоненциальное сглаживание);
• регрессионный анализ (Регрессия) и т.д.

     Статистические  функции для регрессионного анализа  из категории Статистические в окне мастера функций:

• ЛИНЕЙН(знач.У; знач.Х; константа;стат.) - Определяет параметры линейного тренда для заданного массива;
• ТЕНДЕНЦИЯ(знач.У;знач.Х; новые знач.Х; константа;) - Определяет предсказанные значения в соответствии с линейным трендомдля заданного массива (метод наименьших квадратов) и многие другие.

 

       

     Проведение  финансово-экономических расчетов

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

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

     По  типу решаемых задач все финансовые функции Excel можно разделить на условные группы:

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

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

       

3. Решение уравнений  и задач оптимизации 

     Для решения задач оптимизации широкое  променение находят различные средства Excel:

• Подбор параметров для нахождения значения, приводящего к требуемому результату.
• Надстройку Поиск решения для расчета оптимальной величины по нескольким переменным и ограничениям;
• Диспетчер сценариев для создания и оценки наборов сценариев «что – если» с несколькими вариантами исходных данных.

 

     3.1 Подбор параметров 

     Основной  командой для решения оптимизационных  задач в Excel является команда Сервис/Подбор параметра. Эта команда определяет неизвестную величину, приводящую к  требуемому результату.

     Если  команда Подбор параметра отсутствует  в меню Сервис, выполните команду  Сервис/Надстройка и установите флажок Пакет анализа в окне диалога  Надстройка

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

• формула для расчета;
• пустая ячейка для искомого значения;
• другие величины, которые используются в формуле.

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

     Такой процесс называется итерацией, и  продолжается он до тех пор, пока редактор не выполнит 100 попыток или не найдет решения, лежащее в пределах точности 0,001 от точного значения (настройка  этих параметров осуществляется с помощью  команды Сервис/Параметры, вкладка  Вычисления)

     Оптимизация с помощью команды Подбор параметров выполняется так:

     1. Создайте лист, например, с формулой =B1*B2 в ячейке B3, пустой (переменной) ячейкой (B2) и другими данными  (B1), которые могут понадобиться  при вычислениях. Например, необходимо  определить количество книг по  цене 23,75 грн., которые необходимо  продать, чтобы объем продаж  составил 10000,00 грн.

     2. Выделите ячейку листа (B3), в  которой содержится формула (эта  ячейка появится в поле "Установить в ячейке" в окне диалога Подбор параметра). Выполните команду Сервис/Подбор параметра. Открывается окно диалога Подбор параметра.

     3. Введите в текстовое поле Значение  число, соответствующее объему  продаж - 10000. Переместите курсор  в текстовом поле Изменяя значения  ячейки. Выделите ту ячейку, в  которой должен содержаться ответ  (переменная ячейка). Ее содержимое  будет подобрано и подставлено  в формулу командой Подбор  параметра. Выделенная ячейка (B2) выделяется на листе рамкой. Нажмите  кнопку ОК, чтобы найти решение.

     После завершения итерационного цикла  в окне диалога Результат подбора  параметра появляется сообщение, а  результат заносится в ячейку листа. Решение показывает, что для  достижения объема продаж 10000 грн. необходимо продать 421 книгу по цене 23,75 грн. Для  закрытия окна диалога Результат  подбора параметра щелкните на кнопке ОК. 

     3.2 Команда «Поиск  решения» 

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

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