ФИНАНСОВЫЕ ФУНКЦИИ лаб работы. Лабораторная работа 1 Определение будущей стоимости на основе постоянной и переменной процентной ставки Цель работы
 Скачать 1.61 Mb.
|
ЛАБОРАТОРНАЯ РАБОТА № 7 |
| Словесное наименование операции | Номер операции | Встроенная функция – аналог операции |
| Среднее | 1 | СРЗНАЧ |
| Количество чисел | 2 | СЧЁТ |
| Количество | 3 | СЧЁТЗ |
| Максимум | 4 | МАКС |
| Минимум | 5 | МИН |
| Произведение | 6 | ПРОИЗВЕД |
| Смещенное отклонение | 7 | СТАНДОТКЛОН |
| Несмещенное отклонение | 8 | СТАНДОТКЛОНП |
| Сумма | 9 | СУММ |
| Смещенная дисперсия | 10 | ДИСП |
| Несмещенная дисперсия | 11 | ДИСПР |
Команда Данные → Структура → Промежуточные итоги создает промежуточные и общие итоги. При выводе промежуточных итогов Excel всегда создает структуру списка; с помощью символов структуры можно отобразить список с нужным уровнем детализации.
Другим инструментом обобщения и анализа данных является консолидация. Например, если нужно обобщить данные (просуммировать, подсчитать общее количество, найти среднее, максимум, минимум и др.) одной и той же ячейки (на разных листах рабочей книги или в разных книгах, в нескольких однотипных таблицах на одном и том же листе или нескольких листах книги (нескольких книг), следует консолидировать эти ячейки, и тогда заданная операция будет выполняться автоматически. Таким образом, консолидация данных позволяет собрать в одной области сведения из нескольких источников данных.
Консолидировать данные можно несколькими способами:
по расположению данных;
по категориям;
с помощью трехмерных ссылок в формулах.
Метод консолидации по расположению данных используется, если данные занимают одинаковое число ячеек с одинаковым расположением в нескольких местах одной рабочей книги.
Метод консолидации по категориям (или по подписям) используется, если требуется обобщить данные таблиц, расположенных на разных листах рабочей книги, имеющих одинаковые заголовки столбцов и рядов, но различные макеты диапазонов данных.
Метод трехмерных формул позволяет объединить ячейки с помощью формул, используя в них ссылки на ячейки, расположенные в разных листах рабочей книги или в разных книгах.
ПРАКТИКУМ
Задача 1. Необходимо подсчитать для каждого отдела предприятия сумму окладов сотрудников.
Алгоритм решения задачи
Шаг 1: исходный список нужно отсортировать по полю Отдел (рисунок 19).
Рисунок – Сортировка списка по полю «Отдел»
Шаг 2: воспользоваться командой Данные → Структура → Промежу точные итоги. Появится диалоговое окно (рисунок 20).
В списке При каждом изменении в указан Отдел. Поскольку список был отсортирован по полю Отдел, то строки с одинаковым отделом располагаются рядом друг с другом. Как только происходит изменение в поле Отдел, значит, информация о сотрудниках одного отдела закончилась и далее следуют строки, касающиеся сотрудников другого отдела.
Рисунок – Подсчет сумм окладов сотрудников для
каждого отдела
В списке Операция можно выбрать операцию, с помощью которой будут подводиться промежуточные и общие итоги.
В списке Добавить итоги по указать, по какому (каким) полю (полям) подводить итоги.
Если в списке неоднократно подводятся итоги, то установка флажка Заменить текущие итоги приведет к тому, что итоги, полученные ранее, будут заменены новыми. В том случае, если этот флажок сбросить, то каждый раз к предыдущим итогам будут добавляться новые (итоги, полученные ранее, удаляться не будут).
Пусть, например, нужно, чтобы каждая группа строк располагалась на отдельной странице для последующей печати. Для этого нужно установить флажок Конец страницы между группами.
Рисунок – Пример подведения итогов
Если установлен флажок Итоги под данными, то промежуточные и общие итоги будут расположены под данными, а если он сброшен, то над данными.
На рисунке 21 продемонстрирован результат подведения итогов.
Чтобы убрать все итоги, нужно вызвать окно Промежуточные итоги командой Данные → Структура → Промежуточные итоги и воспользоваться кнопкой Убрать все(см. рисунок 36).
Задача 2. Осуществить консолидированное суммирование числовых данных (рисунок 22) и расположить обобщенные данные на отдельном листе рабочей книги.
Рисунок – Данные для консолидированного суммирования
Алгоритм решения задачи
Для выполнения консолидированного суммирования числовых данных необходимо выполнить следующие операции:
выделить на новом листе одну или несколько ячеек;
Рисунок – Диалоговое окно
консолидации данных
выбрать команду: Данные → Работа с данными → Консолидация. На экране появится диалоговое окно Консолидация(рисунок 23);
в диалоговом окне в поле Функциявыбрать функцию для обобщения данных (в нашем примере, функцию Сумма).
в поле Ссылкаследует указать первый диапазон ячеек, который будет участвовать в консолидации. Его не следует вводить непосредственно, требуемый диапазон нужно выделять;
для включения диапазона в группу обрабатываемых данных следует нажать кнопку Добавить. Выбранный диапазон отобразится в поле Список диапазонов;
повторить ввод других диапазонов данных, участвующих в обобщении, используя поле Ссылкаи кнопку Добавить;
в зависимости от выбранного способа консолидации установить требуемые значения группы флажков Использовать в качестве имен. Если консолидация выполняется по расположению данных, то флажки не устанавливаются. Если консолидация выполняется по категориям, то устанавливаются флажки, соответствующие подписи верхней строки и значению первого столбца. Для создания связи между источником данных и обобщенными данными следует установить флажок Создавать связи с исходными данными, тогда при каждом изменении данных автоматически обновляется таблица консолидации;
Рисунок – Результат
консолидирования данных
закрыть диалоговое окно кнопкой ОК, что приведет к объединению данных в одну или несколько изначально выделенных ячеек. Консолидация будет завер шена. Результат консолидирования данных приведен на рисунке 24.
Отметим, что при консолидации по категориям, когда из больших таблиц необходимо обобщить только часть столбцов консолидированной таблицы, предварительно требуется сформировать их наименования (путем копирования их из исходной таблицы), а затем выделить.
Дальнейшие действия соответствуют ранее приведенной схеме (начиная с пункта 2).
Другой вариант консолидации – по расположению – требует полной идентичности исходных таблиц и размещения их на разных листах.
В заключение отметим, что консолидация может быть выполнена и на основе одной таблицы.