Задание 1 (стр. 4 )

  Используя режим работы Сервис-Подбор параметра, определите: при каком проценте премии общая сумма, выдаваемая работникам, будет составлять некоторую круглую величину (например, 10000000)? Проведите самостоятельно несколько подобных расчетов.

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

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

Процент	=x25

Ниже основной части таблицы введите 5-8 вариантов премии (5%, 10%, 15% и т. д.), как это показано выше. Над колонкой, где должны формироваться значения, запишите формулу, ссылающуюся на итоговую сумму в вашей таблице. В примере в этом месте стоит формула: =Х25. Выделите обе колонки. Войдите в пункт Данные – Таблица подстановки. В появившемся окне в пункте Подставлять значения по столбцам в укажите адрес ячейки, где расположен процент премии. Проанализируйте полученный результат.

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

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

Необходимо подготовить в свободном месте таблицы следующий фрагмент (ссылка на итоговую формулу записывается в левом верхнем углу).

=Х25

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

Задание 11

ЦЕЛЬ: применение сценариев и отчетов

При моделировании той или иной ситуации иногда желательно изменять не один или два показателя, а несколько, при этом сохраняя промежуточные результаты и варианты поиска решения. Это позволяет делать Диспетчер сценариев.

1.  Загрузите таблицу, созданную в лабораторной работе 8 (структурирование и итоги).

2.  С помощью Диспетчера сценариев проанализируйте, как изменятся общие начисления, общие удержания и итоговые выплаты при различных вариантах изменения процентов премии, отчисления в пенсионный фонд и подоходного налога. Для этого создайте 3 сценария (“Оптимистический”, “Пессимистический” и “Наиболее вероятный”).

2.1. Для создания сценария следует вызвать Диспетчер сценариев, выбрав команду Сервис- Сценарии, и щелкнуть по кнопке Добавить.

2.2. После этого сценарию следует присвоить имя и указать изменяемые ячейки. Обратите внимание, что несмежные ячейки можно выбрать мышью при нажатой клавише Ctrl или ввести их адреса с клавиатуры, разделяя символом “;”.

2.3. Укажите набор значений изменяемых параметров для первого сценария и щелкните на кнопке Добавить для формирования следующего сценария.

2.4. Когда последний сценарий будет сформирован, щелкните на ОК и вернитесь в окно Диспетчера сценариев

2.5. Просмотрите итоги сценариев. Для этого щелкните по кнопке Итоги и выберите тип отчета, а также задайте те ячейки, результаты расчета которых вас интересуют. Итоговый сценарий будет расположен на новом рабочем листе. Вид итогового сценария представлен на рисунке.

1.  С помощью Диспетчера сценариев научитесь редактировать и удалять созданные сценарии.

2.  Создайте еще несколько сценариев и оцените их, используя кнопку Показать в окне Диспетчера сценариев.

3.  Оцените итоги сценариев с помощью сводной таблицы. Для этого в окне Диспетчера сценариев выберите Итоги, тип отчета - Сводная таблица сценария. Сравните итоговую таблицу сценария со сводной таблицей.

4.  Сохраните рабочую книгу.

5.  Сдайте работу преподавателю.

Задание 12

ЦЕЛЬ: основные приемы построения сводных таблиц.

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

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

· продумать форму вывода результатов;

· выполнить команду Данные-Сводная таблица для вызова Мастера сводных таблиц;.

· указать источник данных и интервал, содержащий данные (область вашей таблицы с данными целесообразно выделить до начала работы с Мастером);

· сформировать структуру сводной таблицы. Для этого из предлагаемого списка полей следует мышью выбирать требуемые поля и "перетаскивать” их в нужное место схематически изображенной структуры. Если поле установлено ошибочно, это легко исправить, “вытащив” его за пределы схемы;

· указать поля данных и функции, с помощью которых эти поля будут обрабатываться. По умолчанию будет установлена функция СЧЕТ. Для изменения функции следует щелкнуть дважды на поле данных и выбрать из предлагаемого списка нужную функцию. Чаще всего используется функция суммирования;

· нажать кнопку Закончить для построения сводной таблицы. Она будет выведена на новом рабочем листе.

Если необходимо вернуться к Мастеру сводных таблиц, можно воспользоваться появившейся панелью инструментов (Запрос и сведение).

1.  Загрузите таблицу, созданную в лабораторной работе 8 (структурирование и итоги).

2.  Дополните таблицу колонкой “Филиал” и заполните эту колонку, исходя из предположения, что у данной фирмы имеются отделения в Москве и С.-Петербурге.

3.  Введите в таблицу еще 5-10 строк, содержащих произвольные данные по работникам.

1.  Постройте сводную таблицу суммарных выплат по филиалам, внутри филиалов - по подразделениям, внутри подразделения - по фамилиям. Представьте результаты первоначально в абсолютных значениях, а затем в виде процента от итогового значения. Для перехода к процентному представлению щелкните дважды на “поле данных” и установите в появившемся окне Суммирующая функция - СУММА, Параметры - Показать данные в виде - ПРОЦЕНТ ИТОГА.

2.  Научитесь изменять формат представления данных в сводной таблице. Для изменения формата следует в диалоге Мастера сводных таблиц дважды щелкнуть мышью на “поле данных” и нажать кнопку Число. В открывшемся окне можно установить любой известный формат.

3.  Меняя расположение полей в структуре сводной таблицы (Страница, Строка, Столбец), добейтесь, чтобы данные по каждому филиалу выводились на отдельной странице и содержали списки подразделений, а подразделения - фамилии сотрудников и данные о зарплате.

1.  Незначительные изменения сводной таблицы можно производить без вызова Мастера сводных таблиц. Любое поле (они условно показаны серым цветом) можно “ухватить и переместить” мышью. Научитесь менять иерархию уровней в вашей сводной таблице. Измените структуру так, чтобы на отдельной странице выводились бы данные по различным подразделениям.

2.  Самостоятельно на отдельных листах составьте:

*  сводную таблицу, в которой для каждого работника фиксируется “Всего начислено”, “Всего удержано”, “Выплатить”;

*  сводную таблицу, в которой подсчитывается количество сотрудников в каждом подразделении;

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

3.  Сохраните рабочую книгу вместе с полученными сводными таблицами.

4.  Сдайте работу преподавателю.

Задание 13

ЦЕЛЬ: работа со списками. Поиск и выборка записей по заданному критерию, сортировка записей.

Одной из наиболее часто решаемых с помощью электронной таблицы задач является обработка списков, которые могут содержать разную информацию: телефонные списки, списки клиентов и др. MS Excel имеет набор средств, который позволяет упростить обработку таких данных. К этим средствам относятся: Формы, Сортировка и Фильтры.

1.  Загрузите таблицу, созданную в предыдущих лабораторных работах.

2.  Придайте таблице статус списка. Для этого должно соблюдаться несколько условий:

а) каждый столбец должен содержать однородную информацию;

б) одна или две верхние строки должны содержать заголовки столбцов;

в) в списке нет пустых строк и столбцов;

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

3.  Постройте форму данных и введите несколько новых записей. Для этого щелкните мышью в области списка и выполните команду Данные - Форма.

4.  С помощью формы данных просмотрите все строки, значение зарплаты в которых превышает 10 000 (Критерии). Задайте еще несколько условий просмотра строк.

5.  Выполните сортировку списка по нескольким полям. Обратите внимание, правильно ли MS Excel интерпретировал ваш список. Для этого нужно щелкнуть мышью на любой ячейке внутри списка и выполнить команду Данные-Сортировка.

6.  С помощью пользовательского автофильтра (Данные - Фильтр - Автофильтр) извлечь все данные:

·  по бухгалтерии;

·  по лицам, чьи фамилии, начинаются с буквы “Б”, до тех, чьи фамилии начинаются с “М”;

·  по лицам, в фамилиях которых вторая буква “А”;

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

·  по сотрудникам, чей возраст от 20 до 25 лет, а зарплата меньше 10000 или больше 100 000.

7.  Скопируйте отфильтрованные по одному из вышеперечисленных критериев данные в новый рабочий лист.

8.  Удалите все пользовательские автофильтры.

9.  С помощью усиленного фильтра (Данные - Фильтр - Усиленный фильтр) осуществите поиск данных по критериям, перечисленным в пункте 6.

10. Самостоятельно сформулируйте несколько критериев и осуществите по ним поиск данных в списке. Каждый критерий записывайте отдельно.

11. Найдите и извлеките в свободное пространство таблицы фамилии и возраст сотрудников, зарплата которых превышает среднее значение по всему списку.

12. Сохраните рабочую книгу со всеми критериями поиска.

13. Сдайте работу преподавателю.

Задание 14

ЦЕЛЬ: применение макросов.

При использовании часто повторяющихся операций для удобства пользователя их можно оформить в макрос и вызывать нажатием одной кнопки. Создать макрос можно двумя способами: написать программу на языке программирования VISUAL BASIC или записать последовательность действий пользователя с помощью макрорекордера. Для вызова макрорекордера используется пункт меню Сервис-Запись макро, в которой следует указать имя макроса и при необходимости назначить ему «горячие» клавиши или пункт меню. После нажатия кнопки ОК на экране появится панель Останов и все дальнейшие действия пользователя будут записаны в макрос. Для прекращения записи макроса воспользуйтесь кнопкой Остановить макрос или Сервис-Запись макроса - Остановить запись. .Для запуска макроса можно использовать «горячие» клавиши (если они были установлены) или назначить макросу кнопку панели инструментов, кнопку на поле таблицы (диаграммы) или рисованный объект.

1.  Загрузите рабочую книгу, в которой содержится ведомость заработной платы за январь, февраль, март 1996 г.

2.  С помощью пункта меню Правка - Переместить/Скопировать скопируйте эту рабочую книгу в новые рабочие книги. Создайте таким образом “Ведомость заработной платы за январь, февраль, март 1995 г.” и “Ведомость заработной платы за январь, февраль, март 1994 г.“

3.  Сохраните все рабочие книги и закройте их.

4.  В новой рабочей книге (menu. xls) с помощью больших кнопок на экране (Вид- Панели инструментов - Формы - Кнопка) создайте меню, позволяющее реализовать следующие функции.

·  загрузить таблицу ведомостей заработной платы за 1994 г.;

·  загрузить таблицу ведомостей заработной платы за 1995 г.;

·  загрузить таблицу ведомостей заработной платы за 1996 г.;

·  сохранить соответствующую таблицу и выйти;

·  выйти без сохранения;

·  скопировать соответствующую таблицу на дискету;

·  загрузить соответствующую таблицу с дискеты;

·  защитить рабочую книгу (кроме окладов и процента премии);

·  обнулить зарплату;

·  быстро переместиться в начало (верхний левый угол) таблицы.

Каждая функция должна быть реализована с помощью макроса, назначенного соответствующей кнопке. Кнопки должны содержать понятные надписи и располагаться на цветном экране. Каждую рабочую книгу следует снабдить кнопкой для перехода в книгу menu.xls.

5.  Сохраните рабочую книгу menu. xls.

6.  “Уберите” лишние панели инструментов, строку формул, строку состояния и сохраните новую рабочую область menu. xlw.

7.  Установите режим автоматической загрузки вашего меню при запуске MS Excel.

8.  Сдайте работу преподавателю.

9.  Обязательно снимите п.9.