Краткие теоретические сведения

Промежуточные и общие итоги

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

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

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

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

Транспонирование

Транспонирование заключается в том, что первая строка таблицы становится первым столбцом новой таблицы, вторая строка становится вторым столбцом таблицы и так далее.
Чтобы строки таблицы сделать столбцами новой таблицы нужно:
1) Выделить ячейки в строке или в строках.
2) Скопировать (кнопкой или через меню).
3) Указать курсором место размещения.
4) Выбрать Главная--Вставить -- Специальная вставка, установить флажок Транспонирование.

Связывание и внедрение рабочих листов

    Существует множество различных способов совместного использования данных программами Microsoft Office. Например, MS Word предоставляет несколько способов вставки данных Microsoft Excel в документ MS Word. Можно скопировать и вставить электронную таблицу MS Excel в документ MS Word. Кроме того, в документ MS Word можно также вставить электронную таблицу в виде связанного объекта или внедренного объекта.

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

Внедренный объект – это объект (например, электронная таблица), вставленный в файл. Будучи внедренным, объект становится частью файла. При двойном щелчке внедренный объект открывается с помощью программы, в которой был создан. Все вносимые во внедренный объект изменения отображаются в содержащем его файле.

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

Изменение содержимого клетки на одном листе (листе-источнике) рабочей книги приводит к изменению связанных с ней клеток в листах-приемниках. Этот принцип отличает связывание листов от простого копирования содержимого клеток из одного листа в другой. В зависимости от техники исполнения связывание бывает “прямым“ и через команду СПЕЦИАЛЬНАЯ ВСТАВКА.

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

=[Имя_Рабочей_книги] Имя_Листа! Адрес_Ячейки

Задания и инструкции по выполнению

Задание 1 Создание итоговой таблицы.

1. Создать файл в MS Excel. Присвоить имя файлу ПЗ №5

2. Переименовать Лист 1 в Итоговая таблица и создайте таблицу по образцу:

3. В таблице представлены сведения о трех акционерах фирмы, значение в столбце Общая стоимость определите по формуле.

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

4.1. Скопировать исходную таблицу ниже.

4.2. Осуществить сортировку по столбцу ФИО.

4.3. Установить курсор в пределах таблицы и вставить итоговую таб­лицу Данные –Промежуточные итоги.

4.4. В форме установить: При каждом изменении в -- ФИО; Операция— Сумма; установить флаги полям- Кол-во и Общая стоимость. Результат выполнения задания:

Примечание. Перед применением итогов исходная таблица копируется.

Задание 2 Использ ование таблиц промежуточных итогов.

1. Используя таблицы промежуточных итогов получить:

1.1. Промежуточные итоги по Выпуску акций сумму количества акций.

         1.2. Промежуточные итоги по ФИО - среднее количество акций.

        1.3. Промежуточные итоги по Видам акций - среднюю стоимость акции.

Задание 3 Транспонирование таблицы.

1. Переименовать Лист 2 в Транспонированная таблица.

2. Скопировать исходную таблицу с Листа 1, используя команды Вставить — Специальная вставка —установить флаг Транспонировать. Строки таблицы станут столбцами.

Задание 4 Группировка.

1. Переименовать Лист 3 в Группировка.

2. Скопировать Транспонированную таблицу.

3. Осуществить группировку, используя команды Данные—Группировать--Столбцы.

Задание 5 Создание таблиц с исходными данными.

1.Переименовать рабочие листы: Лист 4 – Закупка, Лист 5 – Реализация, Лист 6 – Остаток на складе.

2.На рабочем листе " Закупка " создать таблицу по образцу:

3.Рассчитать данные столбца Всего за полугодие и Сумма.

4.На рабочем листе " Реализация " создать таблицу копированием исходной и изменить данные по образцу:

5.На рабочем листе "Остаток на складе" создать таблицу по образцу:

Задание 6 Прямое связывание листов.

1. В таблице Остаток на складе, используя прямое связывание листов, ввести формулы для расчета столбцов Количество и Сумма, для этого:

1.1.Установить курсор в ячейку В3, ввести знак равно «=»

1.2. Перейти на лист Закупка произвести щелчок мышкой по ячейке Н3, ввести знак минус «-», перейти на лист Реализация произвести щелчок мышкой по ячейке Н3.

1.3. Нажать клавишу Enter.

1.4. «Растащить» формулу в ячейки В4:В7.

1.5. Аналогично рассчитать столбец Сумма.

2.Изменить любые значения в таблице Количество закупленной продукции, Количество реализованной продукции  пронаблюдать изменения в таблице Остаток на складе.

3. Переименовать рабочие листы: Лист 7   в Лист Специальная вставка.

4. Осуществить связывание листов по средствам Специальной вставки.

ПРАКТИЧЕСКАЯ РАБОТА

« Экономические расчеты в MS Excel. Анализ экономических показателей деятельности предприятия »

          

Цель работы

1. Изучение технологии подбора параметра при обратных расчетах, технологии поиска решения для задач оптимизации.

2 Формируемые образовательные результаты

   Образовательные результаты У2, У3, З4, ОК5, ПК1.2

Обеспеченность занятия

ü Персональный компьютер с установленными программами Microsoft Office.

ü Принтер.

4 Вопросы для самоподготовки

1. В каких случаях целесообразно использовать инструмент Подбор параметра?

2. Запишите алгоритм заполнения окна Подбор параметра.

3. В каких случаяхиспользуется инструмент Поиск решения?

4. Запишите алгоритм заполнения окна Поиск решения.

5. Чем отличаются методы Подбор параметра и Поиск решения?

6. Какие виды отчетов формирует Поиск решения?

7. Как осуществить дополнительную настройку Поиска решения?

 

Литература

Мочула В.Г. Excel 2007. Расчеты и анализ: учебник / В.Г. Мочула. - Москва, ФЕНИКС, 2010.

Содержание заданий

Задание 1 Изучение постановки задачи для расчета суммы кредит для закупки деталей.

Задание 2 Расчет суммы кредит для закупки деталей.

Задание 3 Изучение постановки задачи для расчета штатного расписания.

Задание 4 Расчет штатного расписания.