Голоскоков К.П., Филиппова. И.Г. (сост.) Информационные технологии в экономике

Подождите немного. Документ загружается.

- для вставки формулы установить селектор в заданную клетку и

щелкнуть по кнопке «=». Выбрать в окне со списком функций:

функцию просмотра и ссылки ВПР;

- щелкнуть мышью по клетке с таб. номером. В поле «Искомое

значение» появится соответствующий адрес;

- щелкнуть мышью по полю «Табл_массив», активизируя его.

Выделить мышью область клеток справочной таблицы,

перейдя на лист справочника. В активизированном поле

появятся соответствующие адреса справочной таблицы.

Последовательно устанавливать курсор на адреса клеток и

нажимать клавишу «F4» для замены адресов на абсолютные;

- занести в поле «Номер_индекса_ столбца» значение 2 (для

фамилии);

- ввести в поле «Диапазон_просмотра» текст «ЛОЖЬ» или

число 0.

4. Ввести внизу под расчетной таблицей справочные данные для

текущего месяца:

- размер минимальной оплаты труда;

- процент удержания подоходного налога;

- процент удержания профсоюзных взносов;

- процент удержания для выплат пенсионный фонд;

- текущая расчетная дата, например 01.01.1987;

- плановое количество рабочих дней в месяце.

5. Рассчитать плановое количество рабочих дней в январе,

используя стандартную функцию:

ЧИСТРАБДНИ (нач_дата;кон_дата;праздники)

Перед вводом этой функции убедиться, что она доступна,

т.[е.[имеется в списке стандартных функций категории «Дата и

время». Если этой функции в списке нет, то ввести ее в список,

выполнив команду СЕРВИС/Надстройка и включить на вкладке

флажок «Пакет анализа».

Ввести в качестве начальной даты текст: «01.01.1998».

Ввести в качестве конечной даты текст: «31.01.1998».

Ввести в качестве 3-го аргумента (праздники) диапазон ячеек

на листе «Справочник», содержащий список праздничных дней.

6. После ввода и отладки всех формул 1-ой строки скопировать их

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

отрицательных значений подоходного налога.

7. Присвоить имя «Справ» диапазону ячеек справочника. Для

этого выделить блок ячеек справочника и выполнить команду

ВСТАВКА/Имя/Присвоить.

8. Добавить столбцы для вычисления сумм удержанных

алиментов с учетом текущей даты и срока окончания

удержания.

Дополнить справочник информацией по срокам окончания

удержания.

9. Рассчитать сумму «Удержано всего» по каждому работнику.

10. Рассчитать сумму «К выдаче» как разность между начисленной

суммой и суммой «удержано всего». Предусмотреть

невозможность выдачи отрицательных сумм.

11. Подсчитать итоговые суммы по всем работникам в целом и в

т.ч. по каждому отделу. Для этого использовать функции

СУММ и СУММЕСЛИ.

ЛАБОРАТОРНАЯ РАБОТА №2

Цель работы: освоение следующих технологических приемов

работы:

- консолидация данных;

- работа со списками;

- анализ данных с использованием сценариев;

- подбор параметров.

Функция консолидации данных позволяет получать

результирующие таблицы, содержащие итоги для данных,

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

рабочих листах или даже в разных книгах. Консолидируемые данные

могут быть динамически связаны с исходными данными таким

образом, что при изменении исходных автоматически обновляется

итоговые данные.

Задание. Сформировать сводную таблицу с итогами по

начислениям и удержаниям заработной платы методом

консолидации. Необходимо создать новую таблицу, содержащую

итоги по каждому работнику за полгода и имеющую связь с

исходными данными в таблицах расчета заработной платы за период

январь-июнь.

ТЕХНОЛОГИЯ ФОРМИРОВАНИЯ СВОДНОЙ ТАБЛИЦЫ

- открыть новый лист, присвоив ему имя «Консолидация»;

- выделить на листе начальную ячейку, например А5, и выполнить

команду ДАННЫЕ/Консолидация;

- в окне «Консолидация» выбрать в качестве функции «Сумму»,

ввести ссылку на консолидируемые ячейки таблицы, находящейся

на листе «январь». Для этого щелкнуть в окне ссылки, затем по

ярлыку листа «январь» и выделить ячейки справа и вниз, начиная с

ячейки «Фамилия, имя, отчество»;

- вернуться в окно консолидации и нажать кнопку «Добавить»;

- повторить описанные выше действия для следующих месяцев

расчета;

- после добавления ссылок на данные июня в окне консолидации

включить флажки «использовать в качестве имени значения левого

столбца» и «создавать связи с исходными данными»;

- щелкнуть по кнопке «Ok». На экране появится таблица

консолидации. Добавить к таблице отсутствующие заголовки

столбцов;

- используя расположенную слева схему управления структурой

включить детальный (второй) уровень просмотра. Переключить на

первый уровень просмотра итогов;

- проверить наличие динамической связи с исходными таблицами,

изменив в них первичные данные, например количество

отработанных дней.

Списком называется таблица, содержащая однородные данные

по строкам и столбцам. Списки можно сортировать, осуществлять в

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

помощью фильтров и форм данных. Найденные в списке данные

могут в дальнейшем подвергаться дополнительной обработке:

удалению, корректировке, включению в вычисления, построению на

их основе диаграмм.

1. Использование формы данных. С помощью форм можно

просматривать и вводить данные в список, искать строки по

содержимому ячеек, а также удалять строки из списка.

Задание 1. Использовать форму данных для просмотра и

корректировки записей таблицы «Справочник работников». Форма

— это наиболее удобный способ для просмотра, изменения,

добавления и удаления записей списка, а также для поиска

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

формой необходимо задать заголовки столбцов списка. Эти

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

Технология:

- выделить область рабочего листа с таблицей «Справочник

работников», начиная со строки, содержащей названия столбцов;

- выполнить команду ДАННЫЕ/Форма и вывести данные одной

строки в форму;

- переместиться вперед и назад по таблице, используя кнопки

«Далее» и «Назад»;

- удалить последнюю строку таблицы нажатием кнопки

«Удалить»;

- добавить новую строку, используя кнопку «Добавить»;

- закрыть диалог нажатием кнопки «Закрыть»;

- используя кнопку «Критерии», найти данные работника по его

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

просмотреть сведения о работниках, имеющих только 1 льготу.

2. Использование автофильтра. В Microsoft Excel предусмотрены

различные методы анализа данных в списке. К списку можно

применить фильтр, чтобы отобрать только записи,

соответствующие определенным условиям. Для этого служит

команда ДАННЫЕ/Автофильтр.

Задание 2. Использовать автофильтр для вывода в таблице

«Справочник работников» информации только о работниках, оклад

которых меньше заданного значения.

Технология:

- отметить область рабочего листа с данными и с заголовками;

- выполнить команду ДАННЫЕ/Фильтр/Автофильтр;

- раскрыть список на поле «Оклад», выбрать пункт «Условие» и

ввести выражение «меньше <значение>»;

- щелкнуть кнопку «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 рублей.

Справка для составления расчетных формул:

Доход = Цена продукции * Количество экземпляров.

Себестоимость реализованной продукции = Себестоимость

продукции* Количество экземпляров.

Валовая прибыль = Доход – Себестоимость реализованной

продукции.

Накладные расходы = Доход * % накладных расходов.

Валовые издержки = Накладные расходы + Затраты на зарплату +

Затраты на рекламу.

Прибыль от продукции = Доход – Себестоимость реализованной

продукции.

A B

4 Количество экземпляров 20000

5 Доход =B14*B4

Себестоимость реализованной

продукции

=B15*B4

7 Валовая прибыль =B5-B6

8 % накладных расходов 30

9 Затраты на зарплату 5000000

10 Затраты на рекламу 1000000

11 Накладные расходы =B5*B8%

12 Валовые издержки =B11+B9+B10

13 Прибыль от продукции =(B14-B15)*B4-B12

14 Цена продукции 6000

15 Себестоимость продукции 2000

ПОСТРОЕНИЕ СЦЕНАРИЕВ

Сценарием в Microsoft Excel называется набор значений

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

Используя сценарии, можно одновременно манипулировать 32

переменными. Каждый сценарий отражает свой ряд предположений,

используемый для получения конечного результата. Существует

возможность создания и сохранения различных сценариев и

переключения на любой из этих сценариев для просмотра

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

сценариев может быть создан итоговый отчет, который может иметь

вид структуры или сводной таблицы.

Каждый сценарий имеет свое имя. Ячейки рабочего листа,

хранящие набор значений подстановки, называются изменяемыми

ячейками. Вычисляемые по формулам значения, зависящие от

сценария, и используемые для сравнения сценариев, размещаются в

ячейках, называемых ячейками результата.

Задание 1. Ввести таблицу с упрощенным бюджетом предприятия на

1997 год и выполнить прогнозирование бюджета на 1998, 1999 и 2000

годы, манипулируя темпами роста различных показателей.

Подготовить 4 сценария с различными прогнозами роста и создать

итоговый сравнительный отчет.

Бюджет предприятия на 1997 г. приведен в таблице:

А B C D E

1 1997 г. 1998 г. 1999 г. 2000 г.

Объем продаж

1000000

Размер прибыли в %

25%

Общая прибыль

250000

Аренда

35000

Услуги

13000

Выплаты

115000

Расход

163000

Чистая прибыль

87000

Прогнозируемый ежегодный рост отдельных показателей

приведен в таблице:

А В

13 Объем продаж 4%

14 Размер прибыли 2%

15 Аренда 5%

16 Услуги 3%

17 Выплаты 5%

Технология решения задачи:

1. Присвоить имена ячейкам В13-В17 в соответствии с названиями

показателей в столбце А. Для этого последовательно устанавливать

курсор на каждую ячейку и выполнять команду

ВСТАВКА/Имя/Присвоить, щелкая по кнопке «Ok» в окне

«Присвоение имени».

2. Присвоить имена ячейкам результата С11, D11, E11 –

«Прибыль_1998», «Прибыль_1999», «Прибыль_2000».

3. Ввести расчетные формулы для вычисления показателей в

ячейках С2:Е11.