Январь!B6 -для первой фамилиина листе Февраль

Чтобы создать такую формулу, установите курсор в ячейку А6 листа Февраль, введите знак "=", перейдите на лист Январь, щелкните по ячейке А6 и нажмите клавишу Enter. Можно ввести всю формулу с клавиатуры.

27. Растиражируйте формулы в столбцах Табельный номер и Фамилии для всех сотрудников.

28. Скопируйте (в этом пункте мы уже не используем передачу данных между л истами) значения из столбцов таблицы Зарплата, Премия и т.д., включая итоговые значения, на лист Февраль и Март.

29. Заполните на основе листа Февраль лист Март.

30. Сохраните рабочую книгу под именем Задача 6.xls.

Контрольные вопросы:

1. Как разделить окно на 2, 4 части?

2. Для чего проводиться закрепление нескольких областей в таблице?

3. Как задать сквозные строки, столбцы при печати?

4. Как осуществить передачу данных между листами? Чем отличается передача данных от копирования и перемещения?

 

Практическая работа № 5.
Средства консолидации.

Цели работы: изучение средств консолидации данных.

1. Откроите таблицу Задача6.xlsx.

2. Сразу сохраните таблицу под именем Задача7.xlsx. Снимите защиту с листа, если она установлена. Замените фамилии в задаче на свои(ю) фамилии(ю) и фамилии рядом сидящих сокурсников.

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

4. Вставьте новый лист и дайте ему имя Консолидация по расположению.

5. Консолидация по расположению применяется к таблицам I типа: таблицам жесткой структуры, состоящие из одинаковых столбцов и строк,например формы каких-либо отчетов. При этом выделяют только ту часть таблиц, которая должна быть объединена. Заголовки столбцов и названия строк в лист консолидируемых данных добавляют путем копирования. То есть предполагается, что каждый месяц состав сотрудников не меняется, а в таблице их фамилии располагаются в строках с одинаковыми номерами.

Рис. 16

6. Проведем консолидацию по расположению. Установите курсор на заработную плату первого сотрудника листа Январь и запомните его координаты это С6. Сделайте активным лист Консолидация по расположению и установите курсор в ячейку с теми же координатами (С6).

7. Выполните команду вкладка Данные / раздел Работа с данными/ кнопка . В открывшемся окне Консолидация (рис.16) в поле Функция выберите операцию Сумма.

8. Затем установите курсор в поле Ссылка, где будет задаваться диапазон ячеек: перейдите на лист Январь, выделите только цифровую часть таблицы(без заголовков и фамилий) и нажмите кнопку Добавить в окне Консолидация. Выделенный диапазон перенесется в поле Список диапазонов окна Консолидация.

9. Затем активизируйте лист Февраль. На этом листе вам будет автоматически предложен тот же диапазон ячеек для консолидации. Нажмите кнопку Добавить. В результате этого уже два диапазона будут включены в список консолидируемых диапазонов.

10. Аналогичные действия проделайте для листа Март.

11. Таким образом, вы определяете адреса ячеек, подлежащих консолидации. В нижней части окна консолидации есть три переключателя, которые для данного примера должны быть сброшены. Если это не так, сбросьте их щелчком мыши.

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

13. Путем копирования через буфер обмена скопируйте заголовки и фамилии. Отформатируйте итоговую таблицу.

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

15. Проведите подготовительную работу с таблицами перед консолидацией. Сделайте активным лист Февраль и добавьте двух новых сотрудников (111 - Агафонова и 112 - Степанову) в середину списка. Установите им заработную плату (3300 и 2550 рублей) и скопируйте все формулы. Отсортируйте таблицу по фамилиям сотрудников.

16. Сделайте активным лист Март и внесите в таблицу данные о сотрудниках, принятых на работу в феврале, и добавьте еще сотрудника 113 - Котова с зарплатой 4100 рублей. Затем удалите сотрудников Петрова и Светина. Отсортируйте таблицу по фамилиям сотрудников.

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

18. Добавьте новый лист Консолидация по категориям. Установите курсор в позицию, соответствующую фамилии первого сотрудника – В6. (Замечание: при консолидации по расположению курсор устанавливался в позицию зарплаты первого сотрудника – С6)

19. Выполните команду вкладка Данные / раздел Работа с данными / кнопка Консолидация. В открывшемся окне Консолидация в поле Функция выберите операцию Сумма.

20. Установите курсор в строку Ссылка, затем перейдите на лист Январь, выделите диапазон, начиная с фамилии и включая всю цифровую часть таблицы (без заголовков) и нажмите кнопку Добавить в окне Консолидация. Выделенный диапазон перенесется в поле Список диапазонов окна Консолидация. Внесите в список диапазоны с листов Февраль и Март. Обратите внимание, что они будут разные, так как количество сотрудников на этих листах неодинаковое.

21. Включите флажки: Использовать в качестве имен значения левого столбца и Создавать связь с исходными данными.

22. Нажмите кнопку ОК для выполнения операции. На новом листе будет создана таблица без заголовков, но с фамилиями.

23. Путем копирования через буфер обмена скопируйте заголовки. Отформатируйте итоговую таблицу.

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

25. Сохраните таблицу под именем Задача 7.xls.

26. Обратите внимание на знаки структуры документа: + - они расположенны слева от номеров строк. Нажмите на один из них - Вы увидите все записи, из которых были образованы консолидированные суммы. Проанализируйте полученные результаты.

Контрольные вопросы:

1. Что такое Консолидация по расположению?

2. Что такое Консолидация по категориям?

3. Какие функции можно использовать при консолидации данных?

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

Цели работы: изучение средств структурирования таблиц и основ построения сводных таблиц.

1. Откройте таблицу Задача 5.xlsx. Замените фамилии в задаче на свои(ю) фамилии(ю) и фамилии рядом сидящих сокурсников.

2. Перед столбцом Фамилия вставьте столбец Отдел.

3. Заполните данный столбец з

4. Произвольно значениями Бухгалтерия; Отдел кадров; Транспортный отдел.

5. Заполните столбец Отдел, предполагая, что каждый сотрудник работает в одном из отделов: Бухгалтерия (например, первые 8 человек), Отдел кадров (следующие 8 человек), Транспортный отдел (остальные).

6. Удалите строку Итого. Перед «шапкой» таблицы вставьте одну пустую строку. Пустые строки и столбцы позволяют табличному редактору MSExcel самостоятельно выделять таблицу при работе с командой Данные (при условии, что курсор позиционирован в таблице).

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

8. Выполните сортировку сначала по отделам, внутри отделов - по фамилиям: выделите всю таблицу, выполните команду командой Главная / Редактирование / сортировка и филтр/ настраиваемая сортировка, укажите столбцы: сначала по - Отделам, затем по - Фамилиям.

Рис. 17

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

10. Рассмотрите полученную структуру и формулы для подведения промежуточных итогов (рис.17). Структурирование рабочего листа позволяет анализировать и обрабатывать таблицы достаточно сложного строения. Обратите внимание на элементы управления структурой, которые находятся слева от таблицы: кнопки É, - и соответствующие им квадратные скобки. Документ имеет несколько уровней детализации. Нажимая на соответствующие кнопки, исследуйте их влияние на форму представления информации в таблице.

11. Чтобы убрать итоги в таблице выполните команду вкладка Данные / раздел Структура / кнопка . / Убрать все.

12. Подводите итоги не под данными, а над ними.

13. Подведите промежуточные итоги по трем столбцам: Начислено, Удержано и К выдаче при каждом изменении значения Отдела (выберите команду вкладка Данные / раздел Структура / кнопка . ). Удалите итоги (команда вкладка Данные / раздел Структура / кнопка . / Убрать все).

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

15. Сохраните таблицу под именем Задача 8.xls.

16. Удалите все элементы структуры и все итоговые строки и сохраните таблицу под новым именем Задача 9.xls.

17. Перед столбцом Отдел вставьте столбец Регион.

18. Создадим выпадающий список в Excel. Выделите образовавшийся диапазон пустых ячеек и выполните команду вкладка Данные / раздел Работа с данными / кнопка в открывшемся окне (вкладка параметры) выберете в пункте Тип данных = Список, в пункте Источник наберите Москва; Рязань; Калуга. Данная операция позволит в выделенном диапазоне выбирать только эти три значения. В каждом регионе должны быть одноименные подразделения.

19. Отсортируйте таблицу по регионам, внутри региона – по отделам, внутри отделов – по фамилиям.

20. Выполните команду вкладка Вставка / раздел Таблицы/ Кнопка (сводная таблица)(Курсор при этом должен быть позиционирован в таблице). Рис. 19. Если диапазон выделился не корректно, измените диапазон по которому строится сводная таблица, и нажмите кнопку ОК..

Рис. 19
Рис. 18	Рис. 20

21. Рядом с листом (справа), на котором будет отражаться сводная таблица вы увидите меню (рис.18) установите курсор мыши на кнопку Регион, нажмите и, не отпуская левую кнопку мыши, тащите его в раздел Название строк, затем аналогично перетащите кнопку Отдел в раздел Название столбцов. После этого переместите кнопку Всего в раздел Значения и щелкните левой кнопкой мыши по этой кнопке, в открывшемся меню выберете пункт Параметры полей Значений. Будет выведен список функций, которые можно использовать (рис.20). Выберите операцию Сумма и нажмите кнопку ОК. Проверьте правильность выполненных действий по рисунку 18 и 20.

22. Обдумайте полученный результат.

23. Обратите внимание на появление кнопки 6 рядом с полем Отдел и Регион. Нажимайте на эту стрелку и поочередно убирайте данные о разных отделах и регионах.

Рис. 21

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

25. Используйте операции Среднее, Максимум, Кол-во чисел для поля Операция. Проанализируйте как изменится сводная таблица.

26. Выбрав вкладку Дополнительные вычисления в окне Параметры поля значений (рис.21) можно производить дополнительные вычисления. Например, если необходимо вычислить «Какую долю от общей суммы получит каждый регион?», то для этого в поле Дополнительные вычисления надо выбрать Доля от суммы по столбцу.

27. Используя команду вкладка Вставка / раздел Диаграммы/ постройте любую диаграмму по сводной таблице.

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

Контрольные вопросы:

1. Как создать выпадающий список?

2. В каком случае MsExcel самостоятельно выделит таблицу при работе с командой Данные?

3. Как провести Автоматическое подведение итогов над данными?

4. Как создать Сводную таблицу?

5. Какие дополнительные вычисления можно провести в Сводной таблице?

6. По какому полю данной таблицы не рационально строить сводную таблицу?

Практическая работа № 7.
Знакомство с таблицами подстановки и сценариями.

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

1. Откройте рабочую книгу Задача4.xls. Снимите защиту с рабочего листа, если это необходимо. Замените фамилии в задаче на свои(ю) фамилии(ю) и фамилии рядом сидящих сокурсников.

Рис. 22

2. Проверьте правильность вашей таблицы. Для этой цели удобно использовать раздел Зависимость формул во вкладке Формулы (рис.22). Чтобы определить назначение кнопки, задерживайте на нем указатель мыши.

Рис. 23

3. Установите курсор на ячейку С4, содержащую значение процента премии и щелкните по кнопке панели Зависимости. Появятся стрелки к ячейкам, которые зависят от процента премии (рис.23). Уберите стрелки, щелкнув по кнопке .

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

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

6. Сохраните исправленную таблицу под именем Задача10.xls.

7. Уберите все стрелки.

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

9. Необходимо определить, при каком проценте премии общая сумма, выдаваемая работникам, будет составлять величину 100000? (Замечание: Если итоговая выплата, выдаваемая всем сотрудникам больше 100 000 рублей, то в поле Значение окна Подбор параметра требуется поставить число большее, чем итоговая выплата, в Вашей таблице.)

Рис. 24

10. Установите курсор на ячейку I16, в которой вычисляется итоговая выплата, выдаваемая всем работникам. Выполните командувкладка Данные/ раздел Работа с данными / выпадающий список / пункт Подбор параметра. Вокне Подбор параметра (рис.24) заполните все поля данными, необходимыми для решения поставленной задачи (п.9).

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

Таблица 3
Процент	=I16
10%
20%
30%
40%
50%

12. Решением этой задачи является фрагмент таблицы, состоящий из двух столбцов: процентов премии и итоговых сумм. Под основной таблицей (например, начиная с ячейки В24) введите слово Процент и пять вариантов премии (10%, 20%, 30%, 40%, 50%) как это показано в таблице 3. Над столбцом, где должны формироваться значения, запишите формулу, ссылающуюся на итоговую сумму в Вашей таблице: в ячейку С24 запишите формулу: =I16. (Замечание: Если Вы не следовали всем указаниям пособия у Вас может быть другой адрес, следовательно самостоятельно скорректируйте задачу).

Рис. 25

13. Выделите оба столбца таблицы вместе с заголовком и формулой - это получится диапазон В24:С29. Выполните команду вкладка Данные/ раздел Работа с данными / выпадающий список / пункт Таблица данных.. В окне Таблица данных в поле Подставлять значения по строкам в: укажите адрес ячейки, в которой расположен в процент премии (рис.25).

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

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

Таблица 4
=I18	0%	1%	2%
10%			.
30%
50%

16. Следует подготовить на свободном месте рабочего листа таблицу 4. Выделите построенную таблицу. Выберите команду вкладка Данные/ раздел Работа с данными / выпадающий список / пункт Таблица данных... В окне Таблица данных в поле Подставлять значения по строкам в: укажите адрес ячейки основной таблицы, где расположено значение процента премии (С4), в поле Подставлять значения по столбцам укажите адрес ячейки, где расположен значение процента удержания в пенсионный фонд (С3). Проанализируйте полученный результат.

17. Сохраните таблицу под именем Задача10.xls.

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

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

· сформировать отчет в виде структурированной итоговой таблицы;

· сформировать отчет в виде сводной таблицы.

19. Задание: Проанализируйте, как изменится итоговая выплата (ячейка I16) при различных вариантах:

· процента премии;

· отчислений в пенсионный фонд;

· ставки подоходного налога.

20. Дайте имена ячейкам, в которых записаны значения Процента премии (С4), удержание в ПФ (С3) и ставки подоходного налога (С2). Ячейке I16 также присвойте имя Итоговая_выплата (о том, как дать имя ячейки см. Практическую работу №3). Если не задать имена исходным и результирующим ячейкам, то в итоговой таблице сценария будут выведены адреса ячеек, что ухудшает ее понимание и внешний вид.

21. Создайте 3 сценария: Оптимистический, Пессимистический и Наиболее вероятный. Для создания сценария выполните команду Выберите команду вкладка Данные/ раздел Работа с данными / выпадающий список / пункт Диспетчер сценариев. В окне Диспетчер сценариев (рис.26) щелкнете по кнопке Добавить…. В окне Добавление сценария (рис.27) в поле Название сценария запишите имя Оптимистический и укажите изменяемые ячейки. В данном случае это будут ставка подоходного налога ($C$2), ставка отчислений в пенсионный фонд($C$3) и процент премии ($C$4). Эти ячейки следует выбирать мышкой при нажатой клавише Ctrl или ввести их адреса с клавиатуры, разделяя символом ";". Нажмите ОК. В окне Значения ячеек сценария (рис.28) укажите конкретные числовые значения для каждого изменяемого параметра (ставка подоходного налога – 12%), ставка отчислений в пенсионный фонд – 0% и процент премии – 50%,

Рис.26

Рис.27

Рис.28

Рис.29

22. Создайте Пессимистический сценарий. Значения для каждого изменяемого параметра: ставка подоходного налога – 15%, ставка отчислений в пенсионный фонд – 2% и процент премии – 10%.

23. Создайте Наиболее вероятный сценарий. Значения для каждого изменяемого параметра: ставка подоходного налога – 12%, ставка отчислений в пенсионный фонд – 1% и процент премии – 25%.

24. Когда последний сценарий будет сформирован, проанализируйте состояние вашей таблицы в трех случаях, которые вы определили. Для этого, находясь в окне Диспетчер сценариев, установите курсор на оптимистический сценарий и нажмите кнопку Вывести. Обратите внимание, что в таблицу подставлены значения, которые вы определили для этого варианта развития событий, и сделан пересчет.

25. Научитесь изменять и удалять сценарии. Проведите самостоятельно несколько экспериментов.

26. Сформируйте итоговый отчет в виде структурированной итоговой таблицы. Для этого в окне Диспетчер сценариев щелкните по кнопке Отчет…. В окне Отчет по сценарию (рис.29) выберите тип отчета - Структура, а также задайте те ячейки, результаты расчета которых вас интересуют. В данном случае – это ячейка I16 – «Итоговая_выплата». Отчет будет расположен на новом рабочем листе Структура сценария.

27. Перейдите на Лист 1. Сформируйте отчет в виде сводной таблицы. Для этого в окне Диспетчер сценариев выберите Отчет…, тип отчета - Сводная таблица. Сравните полученный результат с итоговой таблицей.

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

Контрольные вопросы:

1. Как просмотреть какие ячейки зависимы от основного параметра?

2. Как выполнить задачу о Подборе параметра?

3. Для чего используется Таблица подстановки?

4. Что такое сценарий?

5. Как наглядно сравнить эффективность решения задачи по различным сценариям?

 

Практическая работа № 8.
Знакомство с диаграммами.

Цели работы: Освоение приемов построения диаграмм.

Рис. 30

Рис. 31

1. Известно, что графическая информация воспринимается лучше, чем табличная, и намного лучше, чем словесная. Поэтому графика широко используется для анализа экономических данных. Процессор электронных таблиц MsExcel позволяет строить на основе табличных данных разнообразные диаграммы. Для этой цели предназначен специальный раздел Диаграммы /во вкладке Вставка (рис. 30).

2. Откройте рабочую книгу Задача4.xlsх. Снимите защиту с рабочего листа, если это необходимо. Замените фамилии в задаче на свои(ю) фамилии(ю) и фамилии рядом сидящих сокурсников. Изучите приемы построения диаграмм.

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

4. Выберите вкладку Вставка / раздел Диаграммы / выберете - Гистограмма.

5. Перенесите Диаграмму на новый лист.

6. У Вас появится гистограмма Зарплаты по сотрудникам, Заметьте что при активации диаграммы становиться доступным три новые вкладки Конструктор, Макет, Формат (рис. 31).

Рис. 32

 

7. Проведите редактирование диаграммы. Щелчок правой кнопкой мыши на любом элементе диаграммы (рис.32) активизирует контекстное меню этого элемента. Используя команды такого меню можно отформатировать элемент диаграммы.

8. Щелчком кнопки мыши выделите диаграмму для начала ее редактирования. Отредактируйте Заголовок диаграммы - установите шрифт Times New Roman, полужирный, размером 14 пунктов. Для этого выделите название, выберете вкладку Главная / раздел Шрифт и выберете необходимые параметры.

9. Аналогично отредактируйте Названия осей и Легенду. Затем отформатируйте ось X и ось Y.

10. Отформатируйте Область диаграммы. Щелкните левой кнопкой мыши в Области диаграммы, затем щелкните правой кнопкой и выберите соответствующий пункт из появившегося контекстного меню. Измените заливку диаграммы, а также в пункте Свойства выберете Не перемещать и не изменять размеры. Проанализируйте полученный результат.

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

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

13. Опробуйте различные типы диаграмм и их разные подвиды. Для этого выберете вкладку Конструктор / раздел Тип / кнопка Изменить тип диаграммы. Выберете гистограмму со столбцами в виде пирамид.

14. Измените макет диаграммы с помощью команды: вкладка Конструктор / раздел Макет. Посмотрите как меняется диаграмма.

15. Постройте круговую объемную диаграмму по столбцам К выдаче и Фамилия. Отредактируйте ее.

16. Удалите из Диапазона данных диаграммы свою фамилию. Для этого выполните вкладка Конструктор / раздел Данные / кнопка Изменить данные.

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

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

19. Сохраните рабочую книгу под именем Задача11.xls.

 

Рис. 32.

Контрольные вопросы:

1. Для чего используется диаграммы в работе пользователя?

2. Можно ли построить диаграмму при помощи Мастера Диаграмм, используя менее 4 шагов?

3. Как создать диаграмму для смежных, несмежных диапазонов?

4. Как выделить диаграмму для ее редактирования?

5. Как можно изменить Тип диаграммы?

6. Каким образом можно войти в режим форматирования столбца на диаграмме?

7. Как удалить легенду диаграммы?

8. Как можно вращать и переворачивать объемное изображение?

9. Как можно изменить количество значений использованных для построения диаграммы?

10. Можно ли создать объемную круговую диаграмму с помощью Мастера Диаграмм?

11. Как “вырезать” сектор из объемной круговой диаграммы?

Практическая работа № 9.
Изучение средств фильтрации данных.

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

1. Не все реальные задачи сводятся к электронным таблицам. Иногда не нужно выполнять никаких вычислений, а требуется вести небольшие базы данных, например, телефонных номеров, клиентов, заказов и т.п. Созданы специальные средства, которые позволяют успешно решать такие задачи. К их числу можно отнести СУБД MsAccess, MsFoxPro и др. Однако зачастую привлечение СУБД не является оправданным. В этом случае можно воспользоваться списками MsExcel (иногда говорят БД MsExcel). Для того, чтобы таблица могла быть представлена в виде списка необходимо чтобы:

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

· каждый столбец содержал данные одного типа (поле записи);

· каждая строка представляла собой запись;

· не было пустых строк и столбцов (пустая строка/столбец отделяет список от остальной таблицы).

2. К средствам работы со списками относятся: Формы, Сортировка, Итоги и Фильтры.

3. Откройте рабочую книгу Задача 8.xls (структуризация таблиц), снимите защиту, если она установлена. Замените фамилии в задаче на свои(ю) фамилии(ю) и фамилии рядом сидящих сокурсников. Выполните сортировку списка по отделам, а внутри отделов по фамилиям. Данная таблица полностью соответствует описанию списка Excel.

4. Сохраните рабочую книгу под именем Задача 12.xls.

5. Рассмотрите способы фильтрации данных. Фильтрация таблицы выводит на экран только те строки, которые удовлетворяют заданным условиям. Выделите «шапку» Вашей таблицы. Выполните команду вкладка Данные / раздел Сортировка и фильтр / кнопка Фильтр. В заголовке каждого столбца таблицы появится кнопка ▼ - кнопка раскрывающегося списка. Нажимая на эту кнопку, можно задать условие отбора записей в данном столбце. Если нужно задать сложное условие по столбцу, то в предложенном списке следует выбрать позицию Текстовый фильтр или Числовой фильт (в зависимости от типа данных в столбце) / Настраиваемый фильтр …. Затем в окне Пользовательский автофильтр (рис.39) можно сформулировать достаточно сложное условие отбора записей. В условиях отбора можно использовать шаблоны символов (* - любое количество любых символов,? – один любой символ) и знаки отношений (=, <, >, <=, >=, <>). Условия, заданные в нескольких столбцах, будут объединяться по И.

Рис. 33

Рис. 40

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

7. Найдите пятерых сотрудников с наибольшей зарплатой. Если нужно выбрать несколько наибольших/ наименьших значений в столбце, то в предложенном списке следует выбрать позицию Первые 10… и окне Наложение условия по списку выбрать необходимые условия.

8. Получите сведения о сотрудниках, фамилии которых начинаются с «И» или с «С».

9. Самостоятельно сформулируйте несколько запросов.

10. Отмените фильтр, выполнив команду вкладка Данные / раздел Сортировка и фильтр / кнопка Фильтр.

11. Допустим, что с рассматриваемой таблицей работает несколько пользователей и каждый имеет возможность заполнять ее данными, кроме того, каждый обладает своими особенностями внесения данных. Например, столбец Отдел может быть заполнен следующими вариантами слова «Бухгалтерия»: Бухгалтер, Бух, бухгалтерия, бухучет и т.д. В данной ситуации довольно сложно пользоваться автофильтром, трудоемкой будет работа по приведению всех схожих вариантов к одному виду Бухгалтерия. В таких случаях можно воспользоваться встроенной функцией MsExcel -Расширенный фильтр.

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

Таблица 5
Отдел	Фамилия	Всего	К выдаче
Бухгалтерия			>3000

13. Создайте таблицу условий поиска: скопируйте на свободное пространство текущего листа имена полей нашего списка («шапку»таблицы). Оставьте в новой таблице только те ячейки, которые показаны в Таблице 5, а остальные удалите (команда вкладка Главная / раздел Ячейки / список / пункт Ячейки, со сдвигом влево). В следующей строке запишите условие отбора сотрудников Бухгалтерии, которым выплачено больше 3000 рублей.

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

15. Копирования отфильтрованных данных. Пусть отфильтрованные записи нужно скопировать на другой лист текущей рабочей книги. На текущем листе создайте таблицу условий поиска для отбора записей о сотрудниках, работающих в бухгалтерии ИЛИ о тех, которым выплачено больше 3000 рублей. Перейдите на новый лист и выполните вкладка Данные / раздел Сортировка и фильтр / кнопка . В окне Расширенный фильтр выберите режим копирования в другое место, в первом поле укажите диапазон ячеек, содержащий список (исходная таблица данных на листе), во втором поле - диапазон ячеек, содержащий условие фильтра (таблицу условий поиска, которая также находится на первом рабочем листе), в третьем поле укажите место размещения результата фильтрации – адрес левой верхней ячейки нового рабочего листа (на который осуществляется копирование) и нажмите кнопку ОК. Проанализируйте полученный результат.

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

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

 

Контрольные вопросы:

1. Как задать автофильтр?

2. Можно ли пользоваться? (знаком вопроса) и * (звездочкой) для задания шаблона поиска записи в БД?

3. Как создать собственный фильтр для фильтрации данных?

4. Как снова отобразить все записи БД после фильтрации?

5. При каких условиях удобнее использовать Расширенный фильтр?

6. На какие три области опирается Расширенный фильтр?

7. Как задается условие ИЛИ в автофильтре, расширенном фильтре?

8. Можно ли скопировать полученное после фильтрации подмножество данных в другое место для дальнейшего анализа или вырезать и вставить отфильтрованные данные на другой рабочий лист?