- •Министерство образования и науки российской федерации
- •Оглавление
- •Введение
- •Цели и задачи дисциплины
- •Цели и задачи лабораторных работ
- •Лабораторная работа №1
- •Разрядная сетка
- •Чистрабдни (нач_дата;кон_дата;праздники)
- •Лабораторная работа №2
- •Технология формирования сводной таблицы
- •Технология:
- •Технология:
- •Технология:
- •Подбор параметра
- •Технология решения:
- •Справка для составления расчетных формул:
- •Построение сценариев
- •Технология решения задачи:
- •Лабораторная работа №3
- •Поиск решений Задача 1
- •Технология:
- •Задача 2
- •Постановка задачи:
- •Исходные данные:
- •Задача 3
- •Задача 4
- •Работа с макросами
- •Задание 1
- •Технология:
- •Задание 2
- •Технология:
- •Лабораторная работа №4
- •Проект отраслевого бюджета социальной сферы региона на 1998 – 2000 гг. В млрд. Руб.
- •Задание 1
- •Технология:
- •Задание 2
- •Технология:
- •Задание 3
- •Лабораторная работа №5
- •Часть I. Технология работы с формами вExcel Краткие сведения
- •Технология работы:
- •Создание макроса записи сведений в таблицу:
- •Часть II. Освоение технологии работы с экономико-географическими картами Основные понятия
- •Рекомендации по настройке данных для создания карты
- •Задание 1
- •Технология работы:
- •Задание 2
- •192171, Г. Санкт-Петербург, ул. Седова, 55/1
Технология:
отметить область рабочего листа с данными и с заголовками;
выполнить команду ДАННЫЕ/Фильтр/Автофильтр;
раскрыть список на поле «Оклад», выбрать пункт «Условие» и ввести выражение «меньше <значение>»;
щелкнуть кнопку «Ok»;
отменить автофильтр, для этого выполнить команду ДАННЫЕ/Фильтр и выбрать из списка пункт «все» для поля «Оклад».
Задание 3. Использовать автофильтр для вывода в таблице «Справочник работников» информации о работниках:
фамилии которых начинается на заданную букву;
1-ого и 3-его отделов.
3. Использование расширенного фильтра. Расширенный фильтр позволяет более гибко настроить фильтр для просмотра сведений из списка данных. Просмотр осуществляется на основании условий отбора. В условия отбора расширенного фильтра может входить несколько условий, накладываемых на один столбец, несколько условий, накладываемых одновременно на несколько столбцов, а также условия, накладываемые на возвращаемое формулой значение. Порядок составления условий отбора смотрите в разделе помощи Excel «Примеры условий отбора расширенного фильтра».
Задание 4. Использовать расширенный фильтр для получения данных о работниках 1-го отдела с окладом меньшим 5000000 руб. и не являющихся членами профсоюза.
Технология:
скопировать имена столбцов «Отдел», «Оклад» и «Член профсоюза» в ту часть рабочего листа, которая не содержит данных для поиска, например в ячейки H1,I1,J1;
в ячейки H2,I2,J2 ввести соответственно критерии поиска (1, <50000,0);
выполнить команду меню ДАННЫЕ/Фильтр/ Расширенный фильтр;
в диалоге «Расширенный фильтр» задать область, где находятся исходные данные и область, в которой заданы критерии поиска (диапазон условий);
в группе «Обработка» указать, что фильтрация будет выполняться на месте;
щелкнуть кнопку «Ok»;
восстановить список, выполнив команду ДАННЫЕ/Фильтр/Восстановить все.
Задание 5. Использовать расширенный фильтр для получения данных о работниках 1-го и 3-его отделов, с окладом меньшим 500000 руб. и не являющихся членами профсоюза.
Подбор параметра
Подбор параметра является способом прогнозирования значений с помощью анализа «что-если». При подборе параметра значение влияющей ячейки (параметра) изменяется до тех пор, пока формула, зависящая от этой ячейки, не возвратит заданное значение.
Задание 1. Подобрать цену на продукцию и величину накладных расходов для получения заданной прибыли, используя метод подбора параметров.
Технология решения:
1. На Лист 1 новой книги ввести данные калькуляции цены книги, приведенные в таблице 1. Константами должны быть: количество экземпляров, % накладных расходов, затраты на зарплату, затраты на рекламу, цена продукции и себестоимость продукции (в таблице эти значения показаны на сером фоне). Остальные данные должны быть вычислены на основании расчетных формул. Введите формулы и сверьте результаты расчета по ним с данными, приведенными в таблице.
Таблица 1
Количество экземпляров |
20 000 |
Доход |
120 000 000 р. |
Себестоимость реализованной продукции |
40 000 000 р. |
Валовая прибыль |
80 000 000 р. |
% накладных расходов |
30 |
Затраты на зарплату |
5 000 000 р. |
Затраты на рекламу |
1 000 000 р. |
Накладные расходы |
36 000 000 р. |
Валовые издержки |
42 000 000 р. |
Прибыль от продукции |
38 000 000 р. |
Цена продукции |
6 000 р. |
Себестоимость продукции |
2 000 р. |
2. Переименовать Лист 1 в Калькуляция и скопировать отлаженную таблицу с формулами в Лист 2. Исследуйте информацию, представленную на таблице листа Калькуляция. Увеличение прибыли может быть достигнуто за счет изменения количества экземпляров, себестоимости, валовых издержек и уровня накладных расходов.
3. Подобрать такую цену книги, чтобы прибыль от продукции составила 50 000 000 руб. Для этого:
выбрать команду меню СЕРВИС/Подбор параметра;
в диалоге «Подбор параметра» в окне «Установить в ячейке» указать целевую ячейку, содержащую значение прибыли от продукции (абсолютную ссылку), в окне «Значение» указать то значение, которое должно быть достигнуто (50 000 000) и в окне «Изменяя ячейку» ввести абсолютную ссылку на ячейку, содержащую значение цены;
щелкнуть кнопку «Ok».
4. Ознакомиться с результатами выполнения операции подбора параметра в окне «Состояние подбора параметра» и щелкнуть кнопку «Ok» для изменения значений ячеек таблицы в соответствии с найденным решением.
5. Вернуться к исходному состоянию таблицы, используя описанный в пунктах 3, 4 способ подбора параметра.
6. Самостоятельно определить, каков должен быть показатель % накладных расходов, чтобы прибыль за продукцию составила 40 000 000 рублей.