Табличный процессор Microsoft Excel

Рис. 22

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

Рис. 23



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

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

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

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

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

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

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



Рис. 24

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

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

Таблица 2

Процент	=I18
10%
20%
30%
40%
50%

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

(Замечание: Если Вы не следовали всем указаниям пособия у Вас может быть другой адрес, следовательно самостоятельно скорректируйте задачу).

Рис. 25

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

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

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

Таблица 3

=I18	0%	1%	2%
10%			.
30%
50%

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

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

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

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

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

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

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

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

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

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

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



Рис. 26

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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



Рис. 27

Рис. 28

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

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

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

Щелкните по кнопке Мастер диаграмм Стандартной панели инструментов. На первом шаге (рис.30) задайте Вид диаграммы - Гистограмма. Нажмите кнопку Далее>.

На втором шаге в окне Источник данных проверьте правильность выбранного Диапазона данных (рис.30). Нажмите кнопку Далее>.

На третьем шаге в окне Параметры диаграммы во вкладке Заголовки задайте: название диаграммы - Зарплата за январь, название оси X - Сотрудники, название оси Y - Зарплата.

Ознакомьтесь со всеми остальными вкладками, которые будут показаны на экране. Удалите легенду. Нажмите кнопку Далее>.

На четвертом шаге в окне Размещение диаграммы укажите, что диаграмму следует разместить на отдельном листе. Нажмите кнопку Готово. Проанализируйте полученный результат.

Рис. 29



Рис. 30

Рис. 31

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

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

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

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

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

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

Опробуйте различные типы диаграмм и их разные подвиды. Щелкнете правой кнопкой мыши на области диаграммы и выберите в контекстном меню команду Тип диаграммы…. Поменяйте простую гистограмму на объемную гистограмму со столбцами в виде пирамид. Измените Поворот диаграммы на 300, выбрав в контекстном меню области диаграммы команду Объемный вид… (рис.33).

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

Рис. 32	Рис. 33

Определите по диаграмме, у кого наибольшая итоговая выдача. Удалите их из Диапазона данных диаграммы. Например, удалите Васина и Уткина. В контекстном меню Области диаграммы выберите Исходные данные… / вкладка Ряд (рис.33).

Задайте Имя ряда - К выдаче, Значения ряда - значения столбца К выдаче, кроме наибольших значений (Васина и Уткина), Подписи категорий (ось X) - Фамилии сотрудников, кроме Васина и Уткина. Нажмите кнопку ОК.

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

Рис. 34

Рис. 35



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

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

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

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

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

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

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

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

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

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

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

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

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

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



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

табличный процессор формула форматирование

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

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

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

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

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

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

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

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

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

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

Рис. 36

Рис. 37

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

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

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

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

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

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

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

Таблица 4

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Размещено на Allbest.ru