Сумма начислено (заработок),

ПЕНСИОННЫЕ ОТЧИСЛЕНИЯ

ПРОФ. ВЗНОСЫ

ПОДОХОДНЫЙ НАЛОГ

АЛИМЕНТЫ

ИТОГО УДЕРЖАНО

СУММА К ВЫДАЧЕ

3. Используя формулы, подсчитайте заработную плату сотрудников:

СУММА НАЧИСЛЕНО (ЗАРАБОТОК) =

Оклад + Оклад / %стажа + Оклад / %Доплат + Оклад / %Совмещ.

ПЕНСИОННЫЕ ОТЧИСЛЕНИЯ = Сумма начисл. / 100

ПРОФ. ВЗНОСЫ = Сумма начисл. /100

ПОДОХОДНЫЙ НАЛОГ = (Сумма начисл. – Пенс.Отч – мин)/100*12

АЛИМЕНТЫ = (Сумма начисл. – налог) / 100 * %алим

ИТОГО УДЕРЖАНО = Пенс. отчисл. + Проф. Взносы + Подох. налог + Алименты

СУММА К ВЫДАЧЕ = Сумма начисл. – Итого удерж.

Задание № 2

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

ВЫПОЛНЕНИЕ:

1. Выделить ячейку в столбце Категория.

2. Выполните команду:

ДАННЫЕ ® СОРТИРОВКА

3. В появившемся диалоговом окне «Сортировка диапазона» в поле Сортировать по выберите столбец Категория. Этим самым Вы отсортируете список по категориям.

4. Нажмите кнопку ОК.

5. Выполните команду:

ДАННЫЕ ® ИТОГИ

6. Заполните окно «ПРОМЕЖУТОЧНЫЕ ИТОГИ»

- В поле окна При каждом изменении (At Each Change in) установите Категория.

- В поле окна Операция (Use Function) установите Сумма.

- В поле окна Добавить итоги (Add Subtotal to) установите флажки для нужных столбцов.

7. Убедитесь, что установлены флажки параметров:

- Заменить Текущие Итоги (Replace Current Subtotal)

- Итоги по данными (Add Subtotal To)

8. Нажмите кнопку OK.

Вы увидите, что EXCEL создал промежуточные итоги по указанным Вами столбцам.

Примечание. Если вы хотите УДАЛИТЬ итоги, то выполните снова команду ДАННЫЕ ® ИТОГИ и в окне «Промежуточные итоги» нажмите на клавишу УБРАТЬ ВСЕ (REMOVE ALL).

Задание №3

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

Отсортируйте полученный «Свернутый» список сначала по возрастанию Сумма начислено, а затем по убыванию.

Когда Вы подсчитали промежуточные итоги, обратите внимание, что EXCEL структурировал список. Символы структуры расположены слева списка перед нумерацией строк. Самый внешний уровень – это 1 уровень. Внутренний уровень – 2 уровень, который состоит из кнопок, расположенных около итогов.

1. Щелкните по каждой кнопке второго уровня, они заменятся на. Структура будет отображать «СВЕРНУТЫЙ» список.

2. Отсортируйте его по возрастанию Сумма начислено, а затем по убыванию. При этом все строки списка, несмотря на то, что они невидимы, отсортируются вместе со своими итоговыми строками.

3. Чтобы вернуть список в прежнее состояние, прощелкните каждую кнопку.

СОВЕТ. Символы структуры занимают место на экране, сдвигая столбцы. Вы можете изменить масштаб, чтобы на экране было видно больше столбцов. Для этого выберите команду:

ВИД ® МАСШТАБ

Можно скрыть символы, выбрав команд:

СЕРВИС ® ПАРАМЕТРЫ

На вкладке ВИД снять флажок Символы структуры.

Упражнение № 1

Создайте разрывы страниц для печати. В окне «Промежуточные итоги» установите флажок Конец страницы между группами (Page break Between Groups). В этом случае EXCEL будет печатать каждую группу с новой страницы.

Упражнение № 2

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

1. Выполните команду: ФОРМАТ ® АВТОФОРМАТ.

2. В поле Список форматов выберите подходящий.

3. Нажмите кнопку ОК.

Упражнение № 3

Примените к столбцу Сумма начислено несколько формул: СУММА, СРЕДНЕЕ.

1. Выполните команду: ДАННЫЕ ® ИТОГИ

2. Снимите флажок Заменить Текущие Итоги (Replace Current Subtotal).

3. В качестве итоговой функции выберите СРЕДНЕЕ (AVERAGE).

4. Щелкните по кнопке OK.

 

Упражнение № 4

Сохранение файла

1. Выберите команду Сохранить как... (Save as...).

2. В появившемся диалоговом окне «Сохранение документа» выберите свою папку.

3. В поле Имя файла (File Name) наберите имя файла lab9.

4. Щелкните по кнопке Сохранить (Save) или нажмите клавишу Enter.

Практическая работа № 10

Диспетчер сценариев

Таблицы данных являются хорошим средством в относительно простых ситуациях. Реальные задачи обычно имеют значительное число взаимосвязанных переменных. При моделировании сложных задач, можно обращаться к диспетчеру сценариев, выбрав команду:

Сервис ® Сценарии

Прежде чем начать, определим некоторые термины:

Сценарий – это именованные комбинации значений, заданных для одной или нескольких изменяемых ячеек в модели «что – если». Модель «что – если» – это любой рабочий лист, в котором можно представлять различные значения переменных. Изменяемые ячейки – это ячейки, которые используются в качестве переменных.

Задание № 12

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

Модель данных «Что - Если»

	A	B	C	D
			Всего за неделю	Всего за год
	Доход от одного покупателя		34,78
	Расходы на одного покупателя		4,66
	Прибыль от одного покупателя
	Среднее количество покупателей
	Общая выручка
	Накладные расходы
		Отчислено на зарплату
		Оборудование
		Амортизация
		Реклама
		Снабжение
		Прочее
		Всего
		Текущая прибыль

Ячейка C4: = C2 – C3

Ячейка C6: = C4 * C5

Ячейка D6: = C6 * 52

Ячейка D14: = å D8:D13

Ячейка D15: = D6 – D14

Изменяемые ячейки: C2, C3, C5; D8:D13 (исходные ячейки)

Для моделирования ситуации и поиска вариантов решения, будет изменять данные в исходных ячейках. (Дать четыре варианта изменения, т.е. построить четыре сценария). Причем изменения могут вестись по формуле. Например:

1. Изменить D2 на 10% ® =1,1*D2

2. D3 ® 15% и т.д.

Соответственно будут меняться и значения в вычисляемых ячейках. Ели будем изменять значения непосредственно на листе, то первоначальный вариант будет безвозвратно потерян. Поэтому используют Сценарии.

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

1. Присвоить ячейкам C2; C3; C5 и D8:D15 имена, которые записаны в заголовках ячеек (столбец А и В). Тогда сценарий будет выглядеть наглядно.

2. Выбрать команду:

Сервис ® Сценарии…

3. Появиться окно диалога «Диспетчер сценариев».

4. Нажмите кнопку Добавить.

5. В окне диалога «Добавления сценария» в текстовом поле Название сценария написать: «Исходный» - под этим именем Вы сохраните исходные данные.

6. В поле Изменяемые ячейки записать диапазон изменяемых ячеек: C2; C3; C5; D8:D13. Ссылки на ячейки вводим, выделяя диапазон по отдельности и отделяя их точкой с запятой.

7. Нажмите кнопку OK.

8. Откроется окно диалога «Значение ячеек сценария».

9. Нажмите кнопку Добавить.

10. Появится диалоговое окно «Добавление сценария».

11. В поле «Название сценария» введите: «Опорный план».

12. Нажмите кнопку ОК.

13. Чтобы создать другой сценарий, в окне диалога «Значение ячеек» надо нажать кнопку Добавить.

14. Введите значения для изменения каждой ячейки.

15. Нажать кнопку ОК.

16. В появившемся диалоговом окне «Диспетчер сценариев» нажмите кнопку «Вывести» и проследить за значениями. При этом окно диалога «Диспетчер сценариев» с экрана не удаляется.

17. После создания всех сценариев, нажмите OK.

18. Для возврата в окно рабочего листа надо нажать кнопку Закрыть.

19. Сценарии можно выбирать следующим образом: в поле «Сценарии» стрелкой мыши выбрать нужный и нажмите кнопку «Вывести». Окно диалога «Диспетчер сценариев» во время всего просмотра будет открыто.

20. Таким образом создайте 4 сценария.

Моделировать сценарии могут различные эксперты. Каждый из них может предложить варианты изменений различных показателей. Сценарии могут располагаться в отдельных книгах или листах.

Задание № 2

Необходимость в объединении сценариев возникает, когда их нужно просмотреть на одном листе, а они находятся либо в разных рабочих книгах, либо на разных листах рабочей книги.

Перед выполнением задания № 2, скопируйте созданную таблицу в задании № 1 на Лист 2 и создайте там один сценарий, так как описано выше (задание № 1).

1. Выполните команду:

Сервис ® Сценарий…

2. В появившемся окне «Диспетчер сценариев» нажмите кнопку Объединить…

3. В окне диалога «Объединение сценариев» надо указать книгу или лист где находятся другие сценарии.

4. Затем нажмите кнопку ОК.

5. В появившемся диалоговом окне «Диспетчер сценариев» в поле Сценарии будут находится все выбранные вами сценарии.

6. После объединения сценариев модель «что – если» стала сложной.

Задание № 3

По созданным сценариям можно создать отчет.

1. Выполните команду:

Сервис ® Сценарии…

2. В диалоговом окне «Диспетчер сценариев» нажмите кнопку Отчет…

- В окне диалога «Отчет по сценарию» указаны два типа отчета: структура, сводная таблица

3. Выберите опцию Структура.

4. В окне «Ячейки результата» укажите ячейки, в которых будет изменяемый и анализируемый результат (С6; В15).

5. Нажмите кнопку ОК.

6. Проанализируйте отчет.

Задание № 4

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

1. Выберем команду:

Сервис ® Подбор параметра…

2. Появится окно диалога «Подбор параметра», которое имеет три строки диалога:

– установить в ячейке – здесь надо указать ячейку, в которой будет записан результат (в нашем примере D15);

– значение – значение результата.

– изменяя значения ячеек – указываем ячейки, которые необходимо изменить, чтобы получить необходимый результат (в нашем случае это ячейки: C2; С3; С5 и D8:D13)

3. Нажать кнопку OK.

4. На экране выведется ответ решения.

5. Нажмите кнопку OK если результат Вас устраивает, в противном случае – кнопку Отмена.

6. Задайте несколько вариантов текущей прибыли.

Упражнение № 1

Сохранение файла

1. Выберите команду Сохранить как... (Save as...).

2. В появившемся диалоговом окне «Сохранение документа» выберите свою папку.

3. В поле Имя файла (File Name) наберите имя файла lab10.

4. Щелкните по кнопке Сохранить (Save) или нажмите клавишу Enter.

Практическая работа № 11

Методы оптимизации в EXCEL

Этот инструмент может применяться для решения задач, которые включают много изменяемых ячеек, и помогает найти комбинации, которые максимизируют (минимизируют) значение целевой функции. В качестве примера задач, которые могут реализовать Поиск решения являются задачи Линейного программирования. Поиск решения является надстройкой.

Если выполнена полная установка EXCEL, то меню Сервис содержит команду Поиск решения. Если этой команды нет, то ее надо установить.

Упражнение № 1