Информационные технологии. Представление Microsoft Office Backstage Текстовый редактор Word. Лабораторное занятие
 Скачать 2.01 Mb.
|
|
Тема. Консолидация. Обработка данных в пределах одной и нескольких книг. Надстройки программы MS Excel (Подбор параметра, Поиск решения, Пакет анализа). 1. Анализ данных с применением технологии консолидации Под консолидацией понимается объединение данных, хранящихся в одном или в нескольких списках, выполнение над объединенными данными необходимых вычислений и формирование результирующей таблицы. 45 Консолидацию можно выполнять по расположению или по категории. Консолидация по расположению предполагает сбор информации из ячеек исходных списков, расположенных одинаково. При консолидации по категории табличный процессор использует в качестве признака для объединения заголовки столбцов или строк списков. Такой способ консолидации предоставляет большую свободу организации данных. Консолидация выполняется с помощью инструмента Консолидация, который включается на вкладке Данные-Консолидация. Для выполнения консолидации нужно выполнить операции: —установить курсор в ячейку рабочего листа, в которой будет размещаться левая верхняя ячейка результирующей таблицы; —включить инструмент Консолидация; в диалоговом окне Консолидация в списке Функция выбрать функцию для выполнения необходимых вычислений; — в поле Ссылка указать адрес диапазона или всего списка, данные которого предполагается консолидировать, если есть еще списки для консолидации щелкнуть на кнопке Добавить, ввести следующий диапазон и т.д.; — если консолидация выполняется по категории, в группе Использовать в качестве имен установить флажок Подписи верхней строки, при консолидации по расположению — флажок Значения левого столбца; —для установления связи с исходными списками установить флажок Создавать связи с исходными данными (обычно не работает, но предполагается, что изменения в таблицах приводят к изменениям в консолидированной таблице); —щелкнуть на кнопке ОК. Пример Использовать файл «Консолидация». На листах «1-й день», «2-й день», «3-й день» дан перечень операций, проведенных продавцами магазина. В конце третьего дня руководитель (владелец магазина) используя технологию Консолидации должен подвести общий итог работы продавцов. Примечание. В отличие от консолидации по расположению, которая может применяться для данных одной таблицы, консолидация по категориям применяется только в тех случаях, когда нужно консолидировать данные нескольких таблиц. 2. Присвоение имен ячейкам и диапазонам. Ссылки на листы и книги 2.1. Присвоение имен ячейкам и диапазонам Выделить ячейку или диапазон ячеек на рабочем листе и набрать имя в поле Имя строки формул. При этом имя это одно слово возможно с применением знака подчеркивания. Имя может использоваться в формулах наравне с адресами ячеек и диапазонов в пределах всей книги. Например, имя Блок_1. 2.2. Ссылки на листы и книги Ссылка на лист Лист1!В4. Однако лист можно переименовывать как угодно пользователю и ссылаться на новое имя листа. Имя типа Лист1!В4 действует в пределах книги. (Но имя типа Лист1!Блок_1 действует только в пределах одного листа под аналогичным именем.) Объемное имя Лист1:Лист3!В5 охватывает ячейки В5 на листа с 1 по 3. Такое имя может использоваться в формулах в пределах книги. После того как на рабочем листе определено одно или несколько имен, для включения имен в формулы можно использовать команды Имя и Вставить меню Вставка. 46 3. Операции с массивами в табличном процессоре Массив — это набор данных одного типа. Массив в MS Excel хранится в диапазоне ячеек. MS Excel позволяет создавать одномерные, двумерные и трехмерные массивы, которые хранятся соответственно в одномерных, двумерных и трехмерных диапазонах. Одномерный и двумерный диапазоны создаются на одном рабочем листе. Адресная ссылка на такой диапазон имеет формат: <Адрес_первой_ячейки>: <Адрес_последней_ячейки>. Двухмерные и трехмерные диапазоны создаются в одноименных ячейках нескольких смежных рабочих листов. Адресная ссылка на такой диапазон имеет формат: <Имя_первого_рабочего_листа>:<Имя_последнего_рабочего_ листа>!<Адрес_первой_ячейки>:<Адрес_последней_ячейки>, например: ='Лист1:Лист2'!$А$1:$В$4. Если массив содержит данные арифметического типа, то с таким массивом можно выполнять арифметические операции. Примечание. Ссылка на книгу, лист и ячейку в книге имеет вид, например [Имя_файла]Лист1!D6 или для диапазона ячеек [Имя_файла]Лист1!D6:G6. 4. Обработка данных в пределах одной и нескольких книг Создать в электронной таблице Excel (и Сохранить, оставив открытыми) 3 книги: 1. Расчет_зар_пл с листами Январь, Февраль, Март, 1_квартал, Апрель, Май, Июнь, 2_квартал; Полгода. 2. Шаблоны_и_премии с листами Шаблоны, Кв_1, Кв-2, Полгода; 3. Итоги_по_зп с листом Итог_зп 1. В книге Шаблоны_и_премии: а) Создать на листе Шаблоны: Шаблоны таблицы 1 Расчетная ведомость, а также таблиц 2 и 3 Премия и Выслуга; Шаблоны таблиц 4 и 5 Квартальная премия и Полугодовая премия. б) Копировать шаблоны таблиц 4 и 5 Квартальная премия и Полугодовая премия соответственно на листы Кв_1, Кв-2 и Полгода; в) Задать самостоятельно премии в рублях за 1 квартал, 2 квартал и Полгода, поместив данные в таблицы на листах Кв_1, Кв-2 и Полгода соответственно. 2. В книге Расчет_зар_пл: 4. а) Копировать шаблоны таблиц 1 Расчетная ведомость, а также таблицы 2 и 3 Премия и Выслуга на каждый лист Январь, Февраль, Март, Апрель, Май, Июнь; б) Самостоятельно задать «Оклад» и осуществить расчет «Премии», «Выслуги, и «Зар_пл» на основании данных таблиц 2 и 3 на каждом листе Январь, Февраль, Март, Апрель, Май, Июнь; Осуществить расчет «Итого» по всем столбцам. в) Копировать шаблон таблицы 1 Расчетная ведомость на листы 1_квартал, 2_квартал и Полгода. г) Осуществить расчет таблиц Расчетная ведомость на листах 1_квартал, 2_квартал и Полгода только в столбце Премия, производя ссылки в формулах на данные листов таблицы Квартальная премия и Полугодовая премия из книги Шаблоны_и_премии с листов Кв_1, Кв-2, Полгода. 3. В книге Итоги_по_зп: а) на листе Итог_зп создать таблицу Итоговая заработная плата (таблица 6); б) произвести расчет итоговой заработной платы, вводя в формулы ссылки на данные из ячеек соответствующих книг и листов; в) построить диаграммы по данным таблицы Итоговая заработная плата книги Итоги_по_зп. 47 Таблица 1 Расчетная ведомость № Фамилия Имя Отчество Должность Оклад (руб.) Премия (руб.) Выслуга (руб.) Зар_пл 1. 2. 3. 4. 5. Петухов Скворцов Воробьев Грачев Галкин Иван Сергей Николай Семен Юрий Петрович Васильевич Иванович Андреевич Михайлович Итого: Таблица 2 Таблица 3 ПРЕМИЯ ВЫСЛУГА Процент Лет выслуги Процент 50 % >=20 50 % 20 % >=15 30 % 10 % >=10 20 % 5 % >=5 10 % 3 % >=2 5 % Таблица 4 Таблица 5 Квартальная премия Полугодовая премия Наименование Руб Наименование Руб Спец_1 Спец_1 Спец_2 Спец_2 Отлично Отлично Хорошо Хорошо Удовлетворительно Удовлетворительно Плохо Плохо Таблица 6 Итоговая заработная плата № Фамилия Имя Отчество янв фев кв1 март апр май кв2 полг июнь 1. 2. 3. 4. 5. Петухов Скворцов Воробьев Грачев Галкин Иван Сергей Николай Семен Юрий Петрович Васильевич Иванович Андреевич Михайлович Итого Примечание: 1. «Зар_пл» = «Оклад» + «Премия» + «Выслуга». 1. «Плохо» - это вычет из Заработной платы. 5. Надстройки Excel В программе Excel вы можете пользоваться надстройками — модулями, предоставляющими дополнительные возможности по выполнению финансовых, а также научных расчетов. Надстройки расширяют диапазон команд и возможностей Microsoft Excel. По умолчанию они доступны не сразу, поэтому сначала их необходимо установить и (в некоторых случаях) активировать. 48 Некоторые надстройки, такие как «Пакет анализа» и «Поиск решения», встроены в Excel. Другие доступны в центре загрузки Office.com, их необходимо предварительно загрузить и установить. Кроме того, некоторые надстройки создаются сторонними организациями, например поставщикамии программных решений или программистами. Это могут быть надстройки COM, надстройки Visual Basic для приложений (VBA) и надстройки DLL. Они также требуют установки. После установки надстройки или ее активации соответствующие команды становятся доступны в одном из указанных ниже мест. Вкладка Данные. После установки и активации надстроек «Пакет анализа» и «Поиск решения» в группе Анализ становятся доступны команды Анализ данных и Поиск решения. Вкладка Формулы . После установки и активации мастера суммирования и средства пересчета в евро в группе Решения становятся доступны команды Условная сумма, Пересчет в евро и Формат евро. Вкладка Надстройки. Другие надстройки могут быть добавлены на вкладку Надстройки. Эта вкладка добавляется на ленту после установки и активации первой надстройки, которая должна появиться на ней. Если вкладка Надстройки не появляется, следует перезапустить Excel. Другие надстройки, такие как вкладка Начало работы, доступны в Excel в других местах, например в виде вкладки на ленте, или через макросы или пользовательские меню. 5.1. Добавление или удаление надстроек. Активация надстройки Excel 1. Откройте вкладку Файл. 2. Нажмите кнопку Параметры и выберите категорию Надстройки. 3. В поле Управление выберите элемент Надстройки Excel и нажмите кнопку Перейти. Откроется диалоговое окно Надстройки. 4. В поле Доступные надстройки установите флажок той надстройки, которую необходимо активировать, а затем нажмите кнопку ОК . Если в окне Доступные надстройки не удается найти надстройку, которую требуется активировать, возможно, ее требуется установить. Что касается встроенных надстроек, то среди них можно выделить три: Подбор параметра; Поиск решения; Пакет анализа. Подбор параметра (в общий список Надстроек не входит, т.к. установлен всегда по умолчанию). Подбор параметра является частью блока задач, который иногда называют инструментами анализа "что-если". Когда желаемый результат одиночной формулы известен, но неизвестны значения, которые требуется ввести для получения этого результата, можно воспользоваться средством «Подбор параметра» выбрав команду на вкладке Данные – Анализ «что-если» – Подбор параметра. При подборе параметра Microsoft Excel изменяет значение в одной конкретной ячейке до тех пор, пока формула, зависимая от этой ячейки, не возвращает нужный результат. О надстройке «Поиск решения». (Изменения происходят в нескольких ячейках. Если изменения происходят в одной ячейке, то задача сводится к предыдущей «Подбор параметра). Поиск решений является частью блока задач, который также называют анализ "что-если". Процедура поиска решения позволяет найти оптимальное значение формулы содержащейся в ячейке, которая называется целевой. Эта процедура работает с группой ячеек, прямо или косвенно связанных с формулой в целевой ячейке. Чтобы получить по формуле, содержащейся в целевой ячейке, заданный результат, процедура изменяет значения во влияющих ячейках. Чтобы сузить множество значений, используемых в модели, применяются ограничения. Эти ограничения могут ссылаться на другие влияющие ячейки. 49 Процедуру поиска решения можно использовать для определения значения влияющей ячейки, которое соответствует экстремуму зависимой ячейки — например можно изменить объем планируемого бюджета рекламы и увидеть, как это повлияет на проектируемую сумму расходов. Другие функции. В Microsoft Excel представлено большое число статистических, финансовых, функций даты и времени и инженерных функций и находятся на вкладке Формулы. 5.2.Подбор параметра. Постановка задачи: Рассчитать минимальную оплату студента за учебный курс при условии безубыточности предлагаемой платной образовательной услуги. Решение задачи: Заполним приведенную на рис. 5.1.1. таблицу статьями дохода и расхода. На рис.5.2.1. в столбце Е приведены значения или формулы, которые необходимо ввести в ячейки. При этом Число студентов, Плата с каждого, Число лекций и Число практики - исходные данные. Всего (приход) рассчитывается по очевидной формуле: Число студентов умножается на Плату с каждого. Итого (приход) представляет собой 80% от Всего (приход). Всего (расход) рассчитывается по формуле: Плата за 1 час лекции умножается на Число лекций и складывается с произведением Платы за 1 час практики и Числа практики. Итого( расход) представляет собой увеличенное на 38% Всего (расход). Наконец, Прибыль – разность между Итого (приход) и Итого (расход). Для решения первой задачи требуется определить такую Плату с каждого студента, при которой Прибыль равняется нулю. Для вызова подпрограммы выполним Данные – Анализ «что-если» – Подбор параметра. В диалоговом окне Подбор параметра в поле Установить в ячейке: укажем ячейку D33 (назначение Прибыли), в поле Значение: укажем значение 0, а в поле Изменяя значение ячейки: укажем ячейку D24 (значение Платы с каждого) и щелкнем мышью на кнопке ОК. Задание 5.1. 1. В ячейке D24 укажите приемлемую для студентов Плату с каждого и рассчитайте минимальное Число студентов, при котором предлагаемая плата образовательная услуга будет безубыточной. 2. Для известного Числа студентов рассчитайте Плату с каждого, чтобы получить необходимую Вам Прибыль. 3. Задайте Плату за 1 час практики равной Плате за 1 час лекции, укажите известное Число студентов, установленную Плату с каждого и определите такую Плату за 1 час лекции (и практики), при которой вы получите определенную Прибыль. Рис. 5.2.1. Образец расчетной таблицы 5.3.Поиск решения при изменении нескольких параметров 50 Поиск решения при изменении нескольких параметров осуществляется с использованием команды на вкладке Данные-Поиск решения. Если эта команда отсутствует в меню сервис, смотри выше Добавление или удаление надстроек, Активация надстройки Excel. Надстройка Поиск решения позволяет решать известную в математике транспортную задачу В качестве примера задачи, которую позволяет решить Поиск решения рассмотрим задачу вложения расходов на рекламу со стороны фирмы. Вы составляете план рекламной кампании нового продукта. Общее число публикаций рекламных объявлений желательно довести по крайней мере до 1200000 читателей и слушателей. Общий бюджет на рекламу в различных видах средств массовой информации составляет 800 000 р. В качестве средств СМИ выбраны две газеты, два канала радио и два канала телевидения. Каждое средство СМИ имеет свое количество читателей и разную стоимость единичного помещения рекламы. Задача состоит в том, чтобы достичь заданного числа читателей, слушателей (зрителей), по возможности, с наименьшими затратами при следующих дополнительных ограничениях: в каждом средстве СМИ должно появиться по крайней мере шесть объявлений; вы не можете тратить больше одной трети средств на одно СМИ; общая стоимость размещения рекламы на двух каналах радио и двух каналах телевидения не должна превышать 500 000 р; задача должна иметь возможность приближаться к реальности путем введения функциональной зависимости между числом объявлений в конкретном СМИ и возрастанием числа потенциальных читателей и слушателей. На первом этапе примем простую зависимость: количество прочитавших объявление (слушателей) равно произведению количества объявлений в СМИ на значение потенциальной аудитории СМИ. Но для радиослушателей введем коэффициент 0,1, а для телезрителей ) 0,15. Осуществите набор таблицы с данными (смотри рис. далее). В столбцах F, H ввести формулы, осуществляющие перемножение данных соответственно столбцов C, E и D, E, а в столбце F рассчитать процент затрат на рекламу для конкретного СМИ. Чтобы начать работу с этим инструментом, выберите в вкладке Данные команду Поиск решения. Откроется окно диалога. В этом окне диалога Вы должны указать свою цель. Задание 5.3.1. Максимизировать число читателей и слушателей, охваченных рекламой, изменяемые ячейки (количество объявлений, помещаемых в каждом издании E2:E7) и ограничения (условия приведенные на рис.5.3.1.). Задание5.3.2. Минимизировать затраченные средства на рекламу, изменяемые ячейки (количество объявлений, помещаемых в каждом издании E2:E7) и ограничения (условия приведенные на рис.5.3.1.). Задание 5.3.3. По столбцу Общая стоимость постройте круговую диаграмму и гистограмму. Перенесите исходное и результирующее состояние таблицы в документ Word Создайте из документа Word сайт. 51 Рис.5.3.1. |