Пример создания сводной таблицы.
- перетащить в область Данные кнопки тех полей, по которым будут подводиться итоги.
ПРИМЕР 18 Пример создания сводной таблицы.
Для получения сводной таблицы, представленной на рис. 20, необходимо перетащить кнопку Продавец в область Столбец, а кнопку Выручка в руб. в область Данные.
Макет сводной таблицы представлен на рис. 22.
Рис. 22. Вид окна Мастера сводных таблиц
Редактирование сводной таблицы
Одним из вариантов редактирования сводной таблицы является возврат к ее макету. Для того чтобы активизировать макет сводной таблицы,
- необходимо щелкнуть в любой ячейке сводной таблицы;
- вызвать команду Данные\Сводная таблица или нажать кнопку Мастер сводных таблиц на панели инструментов Сводные таблицы.
В появившемся диалоговом окне можно изменить структуру сводной таблицы, перетащив кнопки полей.
Для изменения функции, с помощью которой подводятся итоги, достаточно два раза щелкнуть мышью на кнопке, размещенной в поле данных макета сводных таблиц. В появившемся диалоговом окне (см. рис.
23) можно выбрать новую функцию.
Удобным вариантом организации сводных таблиц является размещение одной или нескольких кнопок полей в Поле страниц (на макете сводных таблиц это окно представлено белым прямоугольником с надписью Страница.,)
Если в приведенном выше примере поместить кнопку Продавец на поле страниц, то сводная таблица примет следующий вид:
Рис. 23. Диалоговое окно Вычисление поля сводной таблицы
|
||||||||||||||||
При необходимости размещения данных по каждому продавцу на отдельных листах рабочей книги нужно:
- щелкнуть мышью в любом поле сводной таблицы;
- на панели инструментов Сводная таблица нажать одноименную кнопку. В появившемся меню выбрать команду Отобразить страницы.
В результате Excel вставит столько листов в рабочую книгу, сколько фамилий продавцов фигурировало в исходной таблице.
Если данные в исходной таблице периодически редактируются, то для обновления сводной таблицы необходимо:
- щелкнуть мышью в любом поле сводной таблицы;
- на панели инструментов Сводная таблица нажать кнопку Обновить данные!
Если в таблицу, по которой строится сводная, периодически добавляются новые строки, то рекомендуется:
- при построении сводной таблицы использовать
СОВЕТ: имя, заранее присвоенное диапазону,
включающему заголовки столбцов, и
анализируемые данные;
- новые строки вставлять внутрь исходного диапазона.
Если необходима информация, иллюстрирующая конкретное итоговое значение, то следует два раза щелкнуть на нем мышью. При этом в рабочую книгу будет вставлен новый лист с теми строками исходной таблицы, которые содержат данные, использовавшиеся для получения выбранного итогового значения.
Очень наглядными являются диаграммы, построенные на сводных таблицах.
Диаграммы для сводных таблиц.
Перед построением диаграммы удалите любые промежуточные итоги из сводной таблицы. Для этого в Мастере сводных таблиц установите указатель на кнопку поля, из которого следует удалить промежуточные итоги, и дважды щелкните на нем кнопкой мыши. В появившемся диалоговом окне (см. рис. 25) установите переключатель Итоги в положение нет. Нажмите ОК.
Для построения диаграммы щелкните в любом месте сводной таблицы и нажмите на кнопку Мастер диаграмм.
Отредактируйте появившуюся диаграмму.
Рис. 25. Диалоговое окно Вычисление поля сводной таблицы, позволяющее
Excel предлагает несколько инструментов, относящихся к категории Что-Если. К ним относятся Подбор параметров, Сценарии, Таблицы подстановки, Поиск решения.
Подбор параметров.
Инструмент Подбор параметров позволяет находить необходимый результат, изменяя при этом одну переменную. Перед применением рассматриваемого инструмента следует решить задачу средствами Excel с любыми входными параметрами.
ПРИМЕР 19. Пример использования инструмента Подбор параметра
Рассмотрим следующую задачу: Какую максимальную ссуду на год можно взять, если вы должны ограничить ежемесячные выплаты суммой 250 р. процентная ставка - простая, 8% годовых.
1. На первом шаге следует найти величину ежемесячных выплат для произвольной величины ссуды по формуле:
Платеж=(1+Процентная ставка)Величина займа/Срок в
месяцах. Для этого в ячейке В5 следует ввести формулу: =(1+В2)В1/В3. (Вариант оформления листа Excel для решения задачи приведен на рис.26).
Рис. 26.
Фрагмента рабочего листа Excel.
|
|||||||||||||||||||||||||
- Выберите команду Сервис \Подбор параметра.
- В появившемся диалоговом окне (см. рис. 27) в окне Установить в ячейке введите ссылку на ячейку, содержащую формулу (в нашем примере B5.)
- Введите искомый результат в поле Значение (в нашем примере 250р.)
Рис. 27. Диалоговое окно Подбор параметра
Изменяемые ячейки в модели - это ячейки, содержащие значения, которые требуется использовать в качестве переменных.
Для применения Сценариев следует на рабочем листе Excel смоделировать решение задачи при некотором наборе входных параметров.
ПРИМЕР 20. Пример использования сценариев при анализе поведения модели.
На рис. 28 приведен вариант расчета расходов фирмы. Требуется
составить варианты расходов на следующий год.
Заметим, что ячейка Расходы содержит формулу - сумму величин:
Арендная плата, Коммунальные услуги, Административные расходы.
1. Выберите команду Сервис\Сценарии. В появившемся диалоговом окне нажмите кнопку Добавить.
2. В окне Добавление сценария (См. рис. 29) введите имя первого сценария.
В нашем примере в качестве имени сценария можно выбрать имя Прошлый год А затем в этом же диалоговом окне следует указать адреса изменяемых ячеек ( в нашем примере - это адреса $В$1: $B$3).
Важно!
При указании несмежных диапазонов удерживайте нажатой клавишу Ctrl.
| А | В | |
| 1 | Арендная плата | 120 000,00 р. |
| 2 | Коммунальные услуги |
100 000,00 р. |
| 3 | Админ.расходы | 25 000,00 р. |
| 4 | Расходы | 245 000,00 р. |
| ? |
3. Нажмите кнопку ОК.
Рис. 29. Диалоговое окно Изменение сценария
После ввода значений параметров нажмите ОК. Рекомендуем реальные данные, например, по прошлому году сохранить как один из сценариев.
5. Чтобы создать другой сценарий снова нажмите кнопку Добавить, задайте имя нового сценария, например, Оптимистический прогноз, нажмите ОК, задайте оптимистичные прогнозные значения для арендной платы, коммунальных услуг и административных расходов и еще раз нажмите ОК.
6. После формирования сценариев можно перейти к анализу модели. Существует возможность их поочередного просмотра.
Для этого в окне Диспетчер сценариев следует выбрать имя сценария и нажать кнопку Вывести.
1. При работе со сценариями удобно ячейкам с изменяемыми значениями и ячейкам с результатами присваивать имена.
Замечания.
2. Рекомендуется исходный вариант модели сохранить как один из сценариев.
3. При сохранении рабочей книги Excel сценарии сохраняются вместе с другими данными рабочего листа.
Создание отчетов по сценариям.
При нажатии кнопки Отчет в окне Диспетчера сценариев, появляется диалоговое окно, позволяющее выбрать тип отчета. Для простых моделей задач с небольшим количеством изменяемых ячеек рекомендуется тип отчета Структура.
В этом же окне следует указать ячейки, с формулами, зависящими от изменяемых в сценариях параметров. После нажатия кнопки ОК будет вставлен новый лист с данными о поведении модели при различных сценариях поведения
| Структура сценария | Текущие значения: |
Прошлый год |
Оптими-стический прогноз |
Пессими стический прогноз |
| Изменяемые: | ||||
| Арендная_плата | 12000,00 | 12000,00 | 1200,00 | 120000,00 |
| Коммунальные_услуги | 1000,00 | 1000,00 | 100,00 | 10000,00 |
| Админ.расходы | 2500,00 | 2500,00 | 2500,00 | 2500,00 |
| Результат: | ||||
| Расходы | 15500,00 | 15500,00 | 3800,00 | 132500,00 |
При вводе повторяющихся данных в таблицу очень удобно использовать так называемые поля со списками, организовать которые позволяет инструмент Проверка данных.
Рассмотрим пример организации такого поля со списком.
Предположим, что возникает необходимость регулярно вводить в столбец одни и те же данные, например, названия преподаваемых в школе предметов.
На первом шаге следует на отдельном листе сформировать список предметы и присвоить ему имя - ПРЕДМЕТЫ.
На втором шаге нужно переключиться на лист с заполняемой таблицей.
Далее необходимо выделить диапазон, в котором будут использоваться данные из списка ПРЕДМЕТЫ и выполнить команду Данные\Проверка. В появившемся диалоговом окне (См. рис.32) на закладке Параметры ввода следует щелкнуть на стрелке в первом окне Тип данных и выбрать пункт Список. Далее нужно щелкнуть в окне Источник данных и нажать клавишу F3.
В появившемся диалоговом окне щелкните на имени ПРЕДМЕТЫ и нажать кнопку ОК.
Рис.32. Диалоговое окно Проверка вводимых значений
Рис33. Поле со списком для ввода
|
||||||||||||||||||||
значений, сообщения об ошибке и т.д.
Пример 21 создания теста с использованием ввода данных из фиксированного списка
Используя оптимальные технологии ввода данных, создайте средствами MS EXCEL тест.
В ячейку С3 введите вопрос 1 - В каком году был разработан план застройки г.Санкт-Петербург?
Поскольку текст вопроса в ячейку не поместился, измените ширину столбца.
В ячейки, F3, G3, H3, I3 соответственно введите ответы: 1716 1803 1723 1730 (рис.34).
Рис.34. Первый вопрос с ответами
Рис.35 . Проверка вводимых значений
В окне ввода Источник нажмите на кнопку 5;, после чего выделите ячейки с ответами (в нашем случае это ячейки F3, G3, ИЗ, 13).
Теперь нажмите на кнопку J^l и далее, на кнопку ОК.
В результате вышеизложенных действий, около ячейки C4 появится кнопка для выбора. Нажав ее, получим список ответов.
Выбранный ответ помещается в ячейку С4 (См, рис.36),
Рис.36. Список ответов
Мы знаем, что правильный ответ хранится в ячейке F3 (для данного вопроса). Сравним содержимое в ячейке C4 с содержимым в ячейке F3.
Если они совпадают, то выбран правильный ответ и в ячейку поместим слово верно, если не совпадают, то выбран не правильный ответ и в ячейку Е4 поместим слово неверно.
Выделим ячейку Е4 и вставим туда функцию проверки. Для этого вызовем Мастер функций.
В списке Категория выберем Логические В списке функций выберем ЕСЛИ. Нажмем на кнопку OK.
В функции ЕСЛИ укажем:
- в окошке Логическое_выражение: C4=F3
- в окошке Значение_если_истина: "верно "
- в окошке Значение_если_ложь: "неверно"
Нажмем кнопку ОК (См. рис.37).
Рис.37. Аргументы функции ЕСЛИ
=ЕСЛИ(С4=Б'3; верно неверно )
А в ячейке Е4 реакцию на ответ.
| С | D | Е |
| История Санкт -Петербурга | ||
| Б каком году Оыл раграОотап илап застройки. г. Санкт-Петербург? | ||
| SS03 | ? неверно |
Аналогично оформляем следующие вопросы.
Подсчет правильных ответов, организуем, используя функцию
СЧЁТЕСЛИ
Создание пользовательского списка.
К оптимальным технологиям ввода относится также ввод данных с помощью т.н. пользовательского списка.
Известно, что если в одну из ячеек Excel таблицы ввести название Январь и скопировать его с помощью черного креста, то в остальных ячейках появятся названия остальных месяцев: Февраль, Март и т.д. Если ввести в ячейку Понедельник и скопировать его черным крестом, то появятся остальные дни недели.
Можно также использовать сокращенные названия Янв и Пн.
По аналогии с указанными возможностями, пользователь может создать свои списки учеников, учителей, предметов и т.д., выводимые с помощью копирования первого элементы списка.
Для создания пользовательского списка следует выполнить команду Сервис\Параметры и на закладке Списки (См. рис.34) в окошке Элементы списка ввести элементы вашего списка, разделяя их нажатием клавиши Enter. Затем следует нажать кнопку Добавить, а затем кнопку ОК.
Если список уже сформирован, то его можно импортировать. Для этого следует в том же диалоговом окне щелкнуть в окошке Импорт списка из ячеек, указать диапазон, содержащий уже введенный список и нажать кнопку Импорт.
Пользовательский список должен появится в окне Списки.
Теперь для вывода полного списка элементов, на любом листе любого файла можно ввести первый элемент списка, а затем скопировать его черным крестом.
Рис.39. Вкладка Списки
Пример 22. Пример создания пользовательского списка.
Создадим пользовательский список, состоящий из наименований предметов: История средних веков, Информатика, Русская литература, Математика.
Выполните команду Сервис\Параметры и на закладке Списки (См. рис.39) в окошке Элементы списка
- введите слово Предметы, нажмите клавишу Enter;
- введите: История средних веков, нажмите клавишу Enter;
- введите Информатика, нажмите клавишу Enter.
- введите Русская литература., нажмите клавишу Enter и т.д. Указанные предметы должны располагаться в столбце окошка
Элементы списка;
- нажмите кнопку Добавить, список предметов появится в окне Списки;
- нажмите кнопку ОК.
Теперь рассмотрим, как можно использовать сформированный пользовательский список. Введите в любую ячейку слово Предметы и
скопируйте его с помощью черного креста на четыре ячейки вниз. Все элементы списка появятся на листе.
Заметим, что слово Предметы мы ввели в качестве первого элемента списка исключительно для удобства, чтобы не набирать сложный текст История средних веков.
Работа со справочниками. Использование функции ВПР.
Рассмотрим одну из функций MS Excel - ВПР, облегчающую работу по заполнению таблиц при необходимости использования справочной информации. Примером может служить заполнение отчетов, в которых при вводе номера личного дела ученика в соседних столбцах должны автоматически появляться его имя, фамилия, телефон, адрес, данные о родителях и т.д.
На первом шаге, на отдельном листе создайте справочную таблицу (См. табл.20) и присвойте ей имя Справочник, например:
|
|||||||||||||||
| Табл. 20. Справочник товаров |
|
|||||||||||||||
| Табл. 21. Фрагмент отчета |
Функция ВПР имеет следующие аргументы:
Искомое_значение: в этом окошке указывается значение ( личного дела), определяющее остальные параметры (ФИО ученика, его телефон). Это значение будет искаться в первом столбце таблицы-справочника.
Таблица: в этом окошке указывается адрес или имя справочной таблицы (в нашем примере - это имя справочник).
Номер_столбца: в этом окошке указывается номер столбца, содержащий соответствующую характеристику значения, введенного в первом окошке (в нашем случае номер 2 определяет столбец ФИО).
Интервальный_просмотр: имеет два значения 0, если таблица-справочник не отсортирована по первому столбцу, и 1, если она отсортирована по первому столбцу.
Для определения телефона ученика, личного дела которого будет введен в ячейке А2, следует ввести формулу: ВПР(А2;справочник;3;0).
Рис.40. Диалоговое окно функции ВПР
Реализовать это можно с помощью функции ЕСЛИ. В рассматриваемой ситуации функция будет иметь вид:
=ЕСЛИ(А2=0;; ВПР(А2;справочник;2;0)).
При вводе номера личного дела ученика в столбцах ФИО и Телефон появятся соответствующие значения.
Для ввода номеров личных дел мы рекомендуем использовать возможность MS Excel по вводу значений из фиксированного списка с помощью инструмента Данные\Проверка.
Отличия при использовании функций в Excel 2000
Использование функций в Excel 2000 производится по следующей схеме:
1. Установить курсор в той ячейке, где ожидается получить ответ.
2.
3.
Вызвать Мастер функций нажатием кнопки _ на панели
Стандартная.
В левой части первого диалогового окна (См. рис. 41) следует выбрать категорию функции (математическая, статистическая), а затем в правой части этого же диалогового окна щелкнуть на названии необходимой функции и нажать кнопку ОК.
Рис. 41. Окно Мастера функций
5. Нажать кнопку ОК.
Библиографический список:
1. П. Блатнер, Л. Ульрих. Использование Microsoft Excel 2000.
Москва. Вильямс.
2000г.1020с.
2. М. Додж, К.Кината, К Стинсон. Эффективная работа c Microsoft Excel.
Санкт-Петербург. 1997.1040с.
3. Разработка бинес-приложений в экономике на базе MS EXCEL. Под общ. ред.
А.И. Афоничкина. - М. ДИАЛОГ - МИФИ.
2003. -416с.
