- •Воронеж – 2007
- •Методическое пособие
- •Воронеж – 2007
- •Введение
- •Справочная система Excel
- •Элементы экрана программы Excel
- •Разработка электронных таблиц Создание электронных таблиц
- •Вставка строк и столбцов
- •Форматы вводимых данных
- •Автозаполнение
- •Редактирование данных ячеек
- •Создание примечаний
- •Оформление таблицы
- •Выполнение расчетов
- •Для ввода формулы, содержащей функцию:
- •Адресация ячеек Ссылки на ячейки и диапазоны ячеек При использовании информации ячеек применяют их адреса. Адресацию ячеек принято называть ссылками.
- •Использование имен ячеек и фрагментов таблиц
- •Работа с диаграммами
- •Для изменения порядка отображения рядов данных необходимо:
- •Изменение типа диаграммы
- •Добавление к диаграмме новых данных
- •Работа с большими таблицами
- •Просмотр таблицы в несколько окон
- •Разбивка таблицы на листы
- •Печать повторяющихся заголовков
- •Операции над рабочими листами
- •Связывание данных листов таблиц на одном рабочем листе
- •Консолидация данных
- •Обработка информации в таблице Работа с базами данных (списками)
- •Сортировка данных в таблице
- •Подведение промежуточных итогов в таблице
- •Анализ данных
- •Диспетчер сценариев
- •Отбор данных из списка (фильтрация)
- •Условия отбора
- •Поиск решения
- •Процесс поиска решения приводится по следующему алгоритму:
- •Кнопка Восстановить служит для очистки полей окна диалога и восстановления значений параметров поиска решения, используемых по умолчанию.
- •Сводные таблицы
- •Обмен данными
- •Вставка информации в список Ехсеl из другой программы
- •Вопросы для самопроверки
- •Упражнения по работе
- •Упражнение №2 Расчеты в электронных таблицах. Адресация
- •Упражнение №3 Сортировка и фильтрация данных электронных таблиц
- •Упражнение №4 Построение диаграмм. Форматирование диаграмм
- •Упражнение №5 Подбор параметров и подведение промежуточных итогов
- •Упражнение № 6 Сводные таблицы
- •Упражнение №7 Поиск решения. Анализ деятельности предприятия
- •Выполнение работы
- •Упражнение №8 Поиск решения. Транспортная задача
- •Постановка и решение транспортной задачи
- •Упражнение №9 Итоговое Цель работы
- •Задание на самоподготовку
Упражнение № 6 Сводные таблицы
Цель работы:
Освоить процедуру анализа информации таблицы методом построения сводных таблиц.
Получить навыки определения полей сводной таблицы,
Задание на самоподготовку:
Изучить материал по построению сводной таблицы и составить алгоритмы выполнения операций:
Построению сводной таблицы, задания ее полей;
Смены полей сводной таблицы;
Построения диаграммы по результатам сводной таблицы.
Выполнение работы:
Создать таблицу поставки стройматериалов на объект с графами: дата, поставщик, материал, стоимость единицы материала, стоимость поставки и ввести 35-40 записей поставки бетонных блоков, щебня, раствора.
ввести формулы расчета разовых поставок.
Построить по результатам созданной таблицы сводную таблицу. В качестве полей задать по строкам – поставщик, по столбцам – материалы, результатами должны быть стоимости поставок. Провести оценку поставки материалов отдельными поставщиками.
Изменить поля, введя в строки материалы, а в столбцы – поставщиков. Как измениться информация таблицы? Какая часть поставки окажется подчеркнутой?
Удалить из сводной таблицы информацию об одном из поставщиков, используя ниспадающий список в столбце.
Восстановить сводную таблицу. Добавить в столбцы даты поставок. Как изменится таблица?
Убрать из сводной таблицы информацию о поставщиках. Что теперь покажет сводная таблица?
Упражнение №7 Поиск решения. Анализ деятельности предприятия
Цель работы:
Закрепить навыки создания и заполнения таблиц по словесному описанию их содержания.
2. Освоить программу Поиска решения, задания условий поиска, сохране6ния результатов поиска в качестве сценария.
Задание на самоподготовку:
Изучить материал по теме создания таблиц, заполнения их данными, поведения расчетов в них и поиска решения. Составить алгоритмы процедур:
Создания таблицы;
Заполнения таблицы данными с использованием копирования данных;
Создание и копирование формул в таблицах с использованием относительных и абсолютных адресов;
Определение условий поиска, зависимых ячеек и ячеек результата;
Сохранение результатов поиска в качестве сценария.
Выполнение работы
1. Создать таблицу, отображающую результаты хозяйственной деятельности предприятия
В строках таблицы разместить следующие показатели: сезонный фактор, объем сбыта, доход от оборота, себестоимость реализованной продукции, валовая прибыль, затраты на зарплату, затраты на рекламу, накладные расходы, валовые издержки, прибыль, коэффициент прибыльности, цена, себестоимость.
В столбцах таблицы разместить значения этих показателей для каждого квартала, а также суммарные значения за год.
Формулы и константы для расчетов:
Объем сбыта=35*Сезонный фактор *Затраты на рекламу+3000.
Доход от оборота=Объем сбыта*Цена.
Себестоимость реализованной продукции=Объем сбыта*Себестои-мость.
Валовая прибыль=Доход от оборота*Себестоимость реализованной продукции.
Накладные расходы=15% дохода от оборота.
Валовые издержки=затраты на зарплату+затраты на рекламу+накладные расходы.
Прибыль=Валовая прибыль–Валовые издержки.
Коэффициент прибыльности=Прибыль/Доход от оборота.
Сезонный фактор: для I квартала– 0,9; для II – 1,1; для III – 0,8; для IV квартала – 1,2.
Затраты на зарплату: для I квартала – 8000 р.; для II – 8000 р.; для III – 9000 р.; для IV квартала –9000 р.
Затраты на рекламу для каждого квартала – по 10000 р.
Цена – 40 р.; себестоимость – 25 р.
2. Отформатировать таблицу: ячейкам, содержащим денежные величины, назначить денежный формат; ячейкам строки Коэффициент прибыльности назначить процентный формат; расчертить таблицу линиями.
3. С помощью программы Поиск решения определить величину затрат на рекламу, обеспечивающую максимальную прибыль в 1 квартале. Сохранить установки задачи в виде модели. После просмотра результата восстановить исходные значения. Объяснить полученный результат.
4. Определить величину затрат на рекламу для каждого квартала, обеспечивающую максимальную прибыль за год. Сохранить установки задачи в виде модели. После просмотра результата восстановить исходные значения.
5. Определить величину затрат на рекламу для каждого квартала, обеспечивающую максимальную прибыль за год при ограничении суммарной величины расходов на рекламу за год 40000 р. Объяснить полученный результат. Сохранить установки задачи в виде модели. После просмотра результата восстановить исходные значения.
6. Изменить ограничение, установив верхний предел расходов та рекламу за год 50000 р. Сохранить установки задачи в виде модели. После просмотра результата восстановить исходные значения. Объяснить причины не схождения результатов поиска в двух последних случаях.
7. Сохранить в качестве сценария первоначальные значения величин затрат на рекламу в каждом квартале. Установить сезонный фактор каждого квартал равным 1 и провести поиск решения. Почему в последнем случае решение удалось найти, а в двух предпоследних случаях решения не оказалось?
10. Восстановить первоначальные значения с помощью первого сценария.