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

2.  Для графы “Всего начислено” установите формат вывода значений, превышающих 12 млн., - синим цветом, значений от 1 млн. до 10 млн - зеленым цветом. Измените несколько раз зарплату и убедитесь в том, что условное форматирование работает.

Для проверки положительных, отрицательных и текстовых значений используется формат

[положительные]; [отрицательные]; [нулевые];[текст]

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

[синий] [>12000]## ###; [зеленый][>1000]## ###; [черный]0,00

В данном случае числовые значения, большие 12000, выводятся синим цветом, меньшие 12000, но большие 1000, - зеленым, а остальные - черным. Обратите внимание, что текст при этом выводится обычным форматом.

3.  Защитите листы рабочей книги. Сохраните рабочую книгу под именем ZP7.XLS.

4.  Внесите несколько поправок в ведомости начисления заработной платы. Проследите, как изменяются при этом итоговые данные.

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

Задание 8

ЦЕЛЬ : освоение приемов построения диаграмм.

1.Откройте таблицу, созданную в лабораторной работе 7. Снимите защиту с рабочего листа.

2. Вставьте новый лист для будущей диаграммы и сделайте его активным.

Создание диаграммы

3. Щелкните левой кнопкой мыши по инструменту Мастер диаграмм, затем, нажав левую кнопку мыши, выделите пространство для построения диаграммы не менее 2/3 экрана.

4. Перейдите к следующему шагу, на котором задайте данные для построения диаграммы. Вы можете ввести их в виде диапазонов, отделяя точкой с запятой диапазоны, а можете выделить в таблице. При этом, если диапазоны не являются смежными, при выделении нужно удерживать клавишу Ctrl. Выделите на листе, содержащем таблицу, два диапазона: фамилии 5-6 сотрудников и их заработную плату.

5. Перейдите к следующему шагу: выбору типа диаграммы. Для этого щелкните по кнопке со знаком “Шаг>“. Выберите тип -Гистограмма. На следующем шаге выберите формат диаграммы из числа предложенных. Затем задайте:

название диаграммы - “Январь 1996 г.”

название оси Х - “Сотрудники”

название оси Y - “Зарплата”

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

Редактирование диаграммы

7. Дважды щелкните по диаграмме для начала ее редактирования. Отредактируйте название диаграммы - установите шрифт Times New Roman Cyr, полужирный, размером 12 пунктов. Для этого выделите название, нажмите правую кнопку мыши и выберите режим Форматирование. Научитесь перемещать название диаграммы.

8. Аналогично отредактируйте названия осей Х, Y и легенду.

9. Затем форматируйте ось Х и ось Y. Опробуйте различные виды засечек на шкалах, стили, цвета и толщину осей. Проведите несколько экспериментов с расположением меток.

10.Отформатируйте область построения диаграммы, придав ей наиболее понравившиеся вам цвет и рамку.

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

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

13.Вставьте вторую ось Y и установите для нее шкалу, в 5 раз меньшую шкалы на главной оси. Выделите значения подоходного налога и постройте второй график в виде линии. Обратите внимание на расположение графиков относительно друг друга.

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

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

16.Скопируйте созданную диаграмму на другой лист через буфер обмена.

Примерный вид диаграммы представлен ниже:

17. Задайте для листов диаграмм подходящие по смыслу названия.

18. Сохраните рабочую книгу с листами - диаграммами.

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

Задание 9

Цель: изучение дополнительных возможностей по форматированию таблиц.

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

Можно использовать автоматическое структурирование выделенной области (Данные - Структура - Автоструктура). В этом случае MS Excel выполнит группировку без участия пользователя, что редко приводит к желаемым результатам. Поэтому рекомендуется использовать ручное структурирование.

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

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

2.  После колонки “Фамилия” вставьте колонку “Наименование подразделения” и заполните ее произвольными данными; например: “Бухгалтерия”, “Транспортный отдел”, “Отдел кадров” и т. п.

3.  Дополните таблицу колонками “Прочие начисления”, “Прочие удержания”. Заполните их произвольными данными.

4.  Дополните таблицу колонкой “Всего удержано”.

5.  При необходимости исправьте формулу для “Выплатить”:

Выплатить= Всего начислено - Всего удержано.

6.  Выделите всю область таблицы и выполните автоматическое структурирование (команда Данные-Структура-Автоструктура). Научитесь сворачивать и разворачивать полученную структуру до разных уровней (кнопки “+” и “-”).

7.  Удалите созданную структуру ( Данные-Структура-Отменить структуру).

8.  Удалите строку “Итого”.

9.  Отсортируйте таблицу по подразделениям, а внутри подразделений - по фамилиям.

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

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

·  Вызовите Вид - Панели инструментов.

·  Введите новое имя панели Структурирование и нажмите кнопку Создать.

·  Выберите Настройка - Категории-Утилиты.

·  В разделе Кнопки выберите Символы структуры документа и перетащите на новую панель.

·  Выберите категорию Данные.

·  Перетащите на новую панель кнопки Разгруппировка, Группировка, Показать детали, Скрыть детали.

·  Нажмите на кнопку Закрыть.

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

13. Сохраните отформатированную таблицу в своем каталоге.

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

Задание 10

ЦЕЛЬ: графическое отображение зависимостей ячеек друг от друга. Решение обратной задачи. Построение таблиц зависимости от одного и двух аргументов.

  Откройте таблицу, созданную в лабораторной работе 7. Снимите защиту с рабочего листа.

Отображение зависимостей

  Проверьте правильность вашей таблицы. Для этой цели удобно использовать панель инструментов Зависимости (пункт меню Сервис - Зависимости- Панель инструментов). Для определения назначения инструмента задерживайте на нем указатель мыши.

  Установите курсор на ячейку, содержащую процент премии и щелкните по инструменту Трассировка зависимых ячеек - появятся стрелки к ячейкам, которые зависят от процента премии. Уберите стрелки.

  Установите курсор на итоговую сумму, выдаваемую всем сотрудникам, и щелкните по инструменту Трассировка влияющих ячеек - появятся стрелки, показывающие зависимость данной ячейки от других.

  Самостоятельно отобразите все зависимости в вашей таблице и внимательно рассмотрите их. При необходимости внесите в таблицу исправления и сохраните исправленную таблицу.

  Уберите все стрелки. Отключите панель инструментов Зависимости.

Решение обратной задачи

Исходные данные содержат ячейки, куда информация вводится. В рассматриваемом примере исходной информацией являются: «Зарплата сотрудников» (данные колонки С) и «Процент премии» (условно взятый одинаковым для всех сотрудников). Результатом вычислений в электронных таблицах являются ячейки, содержащие формулы. Изменение исходных данных ведет к изменению результатов. Однако бывают случаи, когда нужно получить нужный результат в зависимости от некоторого параметра. Например, нужно определить: при каком проценте премии на руки всем сотрудникам будет выдаваться заданная сумма? Такая задача называется обратной. Здесь в качестве аргумента выступает процент премии, а в качестве функции - итоговая сумма, выдаваемая на руки. Решим поставленную задачу.

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