- •Воронеж – 2007
- •Методическое пособие
- •Воронеж – 2007
- •Введение
- •Справочная система Excel
- •Элементы экрана программы Excel
- •Разработка электронных таблиц Создание электронных таблиц
- •Вставка строк и столбцов
- •Форматы вводимых данных
- •Автозаполнение
- •Редактирование данных ячеек
- •Создание примечаний
- •Оформление таблицы
- •Выполнение расчетов
- •Для ввода формулы, содержащей функцию:
- •Адресация ячеек Ссылки на ячейки и диапазоны ячеек При использовании информации ячеек применяют их адреса. Адресацию ячеек принято называть ссылками.
- •Использование имен ячеек и фрагментов таблиц
- •Работа с диаграммами
- •Для изменения порядка отображения рядов данных необходимо:
- •Изменение типа диаграммы
- •Добавление к диаграмме новых данных
- •Работа с большими таблицами
- •Просмотр таблицы в несколько окон
- •Разбивка таблицы на листы
- •Печать повторяющихся заголовков
- •Операции над рабочими листами
- •Связывание данных листов таблиц на одном рабочем листе
- •Консолидация данных
- •Обработка информации в таблице Работа с базами данных (списками)
- •Сортировка данных в таблице
- •Подведение промежуточных итогов в таблице
- •Анализ данных
- •Диспетчер сценариев
- •Отбор данных из списка (фильтрация)
- •Условия отбора
- •Поиск решения
- •Процесс поиска решения приводится по следующему алгоритму:
- •Кнопка Восстановить служит для очистки полей окна диалога и восстановления значений параметров поиска решения, используемых по умолчанию.
- •Сводные таблицы
- •Обмен данными
- •Вставка информации в список Ехсеl из другой программы
- •Вопросы для самопроверки
- •Упражнения по работе
- •Упражнение №2 Расчеты в электронных таблицах. Адресация
- •Упражнение №3 Сортировка и фильтрация данных электронных таблиц
- •Упражнение №4 Построение диаграмм. Форматирование диаграмм
- •Упражнение №5 Подбор параметров и подведение промежуточных итогов
- •Упражнение № 6 Сводные таблицы
- •Упражнение №7 Поиск решения. Анализ деятельности предприятия
- •Выполнение работы
- •Упражнение №8 Поиск решения. Транспортная задача
- •Постановка и решение транспортной задачи
- •Упражнение №9 Итоговое Цель работы
- •Задание на самоподготовку
Анализ данных
Задача анализа данных состоит в подборе таких параметров (величин) данных, при которых обеспечивается достижение некоторой цели (результата). Обычно цель задается в виде формулы, тогда подобранные параметры данных должны обеспечить ее выполнение наилучшим образом.
Математически задача состоит в решении уравнения f(x)=a, где функция f(x) описывается формулой, x является исходным параметром, значение которого требуется подобрать, a – требуемый результат ее решения.
Решение проблемы ищется в соответствии с алгоритмом:
В ячейку, где должен быть получен результат подбора, записывается формула, которая требует своего решения;
В меню Сервис выбирается команда Подбор параметра.
В поле Установить в ячейке вводится ссылка на ячейку, содержащую формулу (по умолчанию, в эту ячейку вводится адрес текущей ячейки);
В поле Значение ввести значение, которое нужно получить при решении задачи;
В поле Изменяя ячейку вести ссылку на ячейку, где находится значение изменяемого параметра (аргумента);
Щелкнуть клавишу Ок.
При вводе формулы указывается адрес ячейки, где хранится значение изменяемого параметра (аргумента).
После выполнения команды в ячейке с аргументом появится его подобранное значение, а в ячейке с формулой – определенное при этом значение вычисляемой величины (функции). При этом в таблице будут пересчитаны значения всех ячеек, которые влияют на или зависят от величины изменяемого параметра.
Диспетчер сценариев
В среде Excel можно хранить наборы значений данных, которые приводят к различным результатам. Это полезно, когда есть необходимость проанализировать (сравнить) результативность нескольких разных наборов исходных данных (ситуаций). Сохранение данных производится в виде сценария.
Сценарий – это множество входных значений, которые называют изменяемыми ячейками. Их можно сохранит под некоторым именем и применить впоследствии к модели рабочего листа, чтобы проследить влияние изменения значений изменяемых ячеек на другие значения модели. Для каждого сценария можно определить для хранения данных до 32 ячеек.
Для создания сценария:
В меню Сервис выбрать команду Сценарии;
Щелкнуть по кнопке Добавить. Открывается окно Добавление сценария;
В поле Название сценария необходимо ввести имя нового сценария;
В поле Изменяемые ячейки ввести ссылки на изменяемые ячейки. Ссылки на отдельные ячейки отделяются точкой с запятой, их можно ввести с клавиатуры или путем выделения ячеек на рабочем листе. Несмежные ячейки выделяются при нажатой кнопке Ctrl;
Согласиться с введенными данными (щелчок по кнопке Ок);
В открывшемся диалоговом окне Значения ячеек сценария ввести значения каждой изменяемой ячейки;
Для создания других сценариев щелкнуть по кнопке Добавить, при этом открывается диалоговое окно Добавление сценария. Повторить процедуру ввода данных для нового сценария;
Для завершения работы с Диспетчером сценариев щелкнуть по кнопке Ок, потом – по кнопке Закрыть.
Чтобы можно было быстро восстановить значения таблицы, используемой для поиска решения, целесообразно сохранить ее исходные данные в виде сценария.
Для просмотра сценария:
Подать команду Сервис Сценарии;
В поле Сценарии выделить имя сценария, который требуется просмотреть:
Щелкнуть по кнопке Вывести.
Чтобы отредактировать сценарий (изменить исходные значения записанных в нем параметров):
Подать команду Сервис Сценарии;
В поле Сценарии выделить имя сценария для редактирования;
Щелкнуть по кнопке Изменить;
Внести изменения в сценарий (его имя, адреса изменяемых ячеек и их значения);
Для завершения работы с Диспетчером сценариев щелкнуть по кнопке Ок, потом – по кнопке Закрыть.
Для создания итогового отчета по сценариям:
Выбрать команду Сервис Сценарии;
Щелкнуть по кнопке Отчет;
Выбрать тип отчета: структура или сводная таблица;
В отчете Структура перечислены все сценарии с определенными для них значениями ячеек. Такой отчет полезен, когда каждый пользователь определяет сценарий со своими данными;
Отчет типа Сводная таблица позволяет провести анализ имеющихся сценариев. Можно проанализировать результаты проведения поиска решения по разным наборам изменяющихся ячеек, провести анализ для разных комбинаций сценариев;
В поле Ячейки результата ввести ссылки на ячейки, значения которых надо представить в отчете. В качестве разделителя ссылок используется запятая. Ссылки можно ввести с клавиатуры или выделить на рабочем листе. Несмежные ячейки вводятся при нажатой клавиш Ctrl. Итоговые таблицы создаются на отдельных листах.
.