Анализ данных с помощью Excel

Анализ данных с помощью Excel   Необходимо отметить, что большинство управленческих, аналитических, экономических и инженерных задач в Excel можно решать, используя встроенные функции и основные стандартные способы работы с электронными таблицами. Значительно упрощает решение этих задач встроенный язык программирования в Excel VBA – Visual Basic for Application, основы которого не сложно изучить, хотя и потребуется для этого некоторое время. Еще более облегчают решение такого рода задач макросы – специальные программы, содержащие перечень инструкций для компьютера и достаточно простой инструмент их написания. Мы начнем с встроенных функций и стандартных способов общения с Excel.   В Excel имеются следующие категории встроенных функций:  Арифметические и тригонометрические.  Инженерные.  Финансовые.  Функции для работы с базами данных, массивами, ссылками.  Функции работы с датой и временем.  Информационные функции.  Логические функции.  Статистические функции.  Функции обработки текста. Все эти функции используются при решении аналитических и расчетных задач в управлении, мы рассмотрим финансовые, экономические и производственно-технологические функции. Часть функций и инструментария приходится включать в работу через надстройки. Но, прежде всего, вспомним некоторые стандартные приемы работы с Excel.     1.    Импортирование данных   Прежде, чем анализировать данные, необходимо, чтобы они попали в рабочую таблицу. Есть пять основных путей импортирования данных в рабочую таблицу Excel: -        введение данных вручную: набор чисел и текста в ячейки с клавиатуры; -        генерирование данных с помощью использования формул и макросов; -        использование надстройки MS Query (или сводной таблицы) для перенесения данных из внешних баз данных; -        копирование данных из другого приложения с использованием буфера обмена Windows; -        импортирование данных из чужого (созданного не в Excel) файла.   Данные. Способ предоставления информации, которая может поступать в любой форме и из различных источников. Файлы разных программных оболочек. Lotus 1-2-3 (WKS, WK1), dBASE (DBF) и т.д. При считывании чужого файла не все формулы, функции могут быть преобразованы правильно. Для того, чтобы этого не случилось, необходимо: в Excel: Сервис – Параметры – Переход – Преобразование формул в формат Excel при вводе. Форматы текстовых файлов. Без форматирования, поэтому сохраняются данные с помощью мастера текстов. Данные импортировать можно через буфер обмена.   2.    Работа со списками.   Список – упорядоченный набор данных. Строки заголовков и строки набора данных. По сути дела база данных на рабочем листе. Поля, записи. Не более 256 полей и 16383 записей. Операции над списками. 1.    Планирование списка. -        Заголовки в первую строку списка. -        В каждом столбце однотипная информация. -        Можно использовать формулы, берущие значения из других полей этой же записи (Копирование). Если значение из ячейки вне списка, абсолютная ссылка. -        Не использовать пустых строк в списке. -        Список на отдельном листе. -        Окно – Закрепить области для заголовков. -        Форматирование всего столбца.   2.    Ввод данных в список. -        Вручную. Автозаполнение. Выбор из списка. -        Импорт или копирование данных из другого файла. -        Использование диалогового окна. Данные – Форма. Можно использовать для удаления, поиска и т.д. -        Использование формы Access. Данные – Форма Access. Затем просмотр формы Access.   3.    Фильтрация списков. -        Автоматическая фильтрация. Данные – фильтр – автофильтр. Не более 250 записей. Обратно: Данные – фильтр – показать все. Но формулы при фильтрации сохраняются (сумма). В отфильтрованных необходимо использовать функции баз данных. -        Автоматическая фильтрация по значениям в нескольких столбцах. Сначала п.1, затем по каждому столбцу условие. -        Пользовательский автофильтр. При выборе п.1 выбрать опцию Условие в раскрывающемся списке и Пользовательский автофильтр (диалоговое окно). -        Наложение условия по списку. Опция: Первые 10 – диалоговое окно. -        Построение диаграммы данных отфильтрованного списка. Параметры – Диаграмма – Отображать только видимые ячейки. -        Расширенная фильтрация. Установка диапазонов критериев. 2 строки отдельно от списка: первая – все или некоторые названия полей; вторая и следующие – критерии фильтрации. Затем Данные – Фильтр – Расширенный фильтр. Диалоговое окно. Множественный критерий отбора. 3 строки: 1 названия и 2 с критериями. Типы критериев: текстовые или числовые и вычисляемые. Другие операции расширенной фильтрации: копирование в другое место и отображение только уникальных строк.   4.    Использование функций баз данных в списках. Например, =БДСУММ(ДиапазонСписка, НазваниеПоля, Критерий). Первое ссылка на список, вторая ссылка на ячейку с заголовком того столбца, где произойдет суммирование, третья – ссылка на диапазон критериев.   5.    Сортировка списков. -        Простая сортировка. Кнопки сортировки. -        Более сложная сортировка. Данные – сортировка – сортировка по диапазонам. Опции сортировки. Особый порядок.   6.    Создание промежуточных итогов. Данные – Итоги. Диалоговое окно.     3.    Использование файлов внешних баз данных. Используется в случаях, когда базы данных большие, легче через Excel; при использовании БД двумя пользователями; необходимо лишь некоторое подмножество данных из БД; БД имеет формат, который не читает Excel. Через MS Query. Данные – получить внешние данные. Выбрали БД, создали запрос – список критериев, которые определяют нужные записи. Выбрали команду файл – вернуть данные в Excel. Файл БД – постановка задачи – запуск MS Query – выбор источника – выбор табличной БД – определение критерия отбора записей – выполнение запроса – возвращение результатов в Excel. Подробнее о MS Query. Обновление запроса. Данные обновить данные. Можно только при Сохранить определение запроса в диалоговом окне Получение внешних данных. Изменение запроса. Курсор в ячейку с выбранными данными – Данные – Получить внешние данные – Правка запроса. Создание нескольких запросов. Другие возможности MS Query. Использование диалогового окна Добавить критерий: Критерий – добавить критерий. Можно сохранить запрос – в программе файл – сохранить запрос. Можно редактировать через MS Query базы данных – опция – записи – разрешить правку. Формат – шрифт. Записи – сортировать.   4.    Сводные таблицы. Сводная таблица – это динамический итог данных, содержащихся в базе или списке данных. Сводная таблица выводит промежуточные итоги, с любым уровнем детализации, организует перекрестные табличные данные для нескольких измерений данных. Для работы со сводными таблицами необходимо знать некоторые термины. Группа – набор элементов, которые обрабатываются как один элемент. Исходные данные – данные, которые используются для создания сводной таблицы. Область данных – ячейки сводной таблицы, в которых содержатся итоговые данные. Обновить – заново вычислить сводную таблицу, после того, как были внесены изменения в исходные данные. Общие итоги – строка или столбец, в котором показаны общие итоги для всех ячеек строки или столбца сводной таблицы. Поле столбца – поле, которому в сводной таблице соответствует отдельный столбец. Каждый элемент, принадлежащий этому полю, занимает один столбец. Поле столбца может быть вложенным. Поле страницы – Поле в сводной таблице, которое имеет ориентацию страницы, чем-то напоминает часть трехмерного куба. В поле страницы может быть отображен лишь один элемент за один раз. Поле строки – Поле, которому в сводной таблице соответствует одна строка. Каждый элемент, принадлежащий этому полю, занимает одну строку. Поле строки может быть вложенным. Промежуточные итоги – Строка или столбец, в которых отображаются промежуточные итоги для текущих ячеек строки или столбца сводной таблицы. Элемент – Элемент в поле, которое является заголовком строки или столбца сводной таблицы. Меню: Данные – Сводная таблица. Включается мастер сводных таблиц. 4 шага. Настройка сводных таблиц. Параметры сводной таблицы. Работа со сводной таблицей: изменение структуры, удаление, добавление поля, обновление данных, изменение полей. Форматирование сводной таблицы. Группировка элементов сводной таблицы. Просмотр деталей. Создание диаграмм по сводной таблице. Консолидация листов рабочей книги. Примеры сводных таблиц: анализ опытных данных (результаты опросов респондентов) и т.д.   5.    Анализ электронных таблиц с помощью сценария «что-если». Используется при построении так называемых динамических моделей различных сценариев разворачивания событий, когда при изменении содержимого ячеек, формулы автоматически пересчитывают результат, и можно прогнозировать, что будет, если сделать то-то и то-то. Инструменты сценария - подбор параметров, таблицы подстановки, можно использовать диспетчер сценариев и макросы.   6.    Анализ данных с использованием процедур поиска решений.   Дает возможность указания нескольких изменяемых ячеек, ограничения на значения изменяемых ячеек, нахождение решения, при котором значение в определенной ячейке листа достигает максимума или минимума, получение нескольких решений задачи. Характерные задачи для процедуры поиска решений: -        значение в целевой ячейке зависит от других ячеек и формул, причем нужно определить все исходные параметры, при которых значение в целевой ячейке будет максимальным, минимальным или заранее определенным; -        целевая ячейка зависит от группы ячеек, называемыми изменяемыми ячейками. Их значения могут быть подобраны таким образом, чтобы повлиять на результат в целевой ячейке; -        решение должно находиться в определенных пределах или удовлетворять определенным ограничениям.