Вопрос 28. Консолидация данных.

Средство поиск решения. Транспортная задача.

Можно решать задачи, Вопросоптимальное решение задачи:

1) задача о составлении оптимального меню

2) задача о назначениях

3) задача линейной алгебры; решение систем линейных уравнений

4) задача о выборе портфеля ценных бумаг (формирование оптимального портфеля ценных бумаг, который обеспечивал бы МАХ доход при МИН риске)

5) транспортная задача

6) задача о рюкзаке, ранце

Особенности:

1) поиск решения позволяет использовать одновременно большое количество переменных

2) позволяет задавать ограничения для искомых величин

3) обеспечивает поиск наилучшего решения

4) для сложных задач может быть множество решений, которые можно сохранить как соответствующий сценарии

Свойства задач:

1) есть единственная целевая ячейка, содержащая формулу, значение которой может принимать МАХ и МИН значение

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

3) может быть задано некоторое кол-во ограничений

Примерное решение подобной задачи:

1) ввести в таблицу данные

2) записать математическое выражение условий

Меню Сервис Поиск решения

Устанавливаем целевую ячейку, мах или мин значение, изменяемые ячейки и ограничения.

Транспортная задача: Поставщики и потребители, затраты на перевозку заданы в виде матрицы, необходимо составить план перевозок, при котором затраты на перевозку будут минимальными, поставки должны быть целыми и иметь не отрицательные значения. Ограничения: использование всех запасов поставщиков; удовлетворение спроса потребителей. Решение: заносим матрицу затрат, пропускаем одну строчку и записываем спрос потребителей, в столбец пишем поставщиков, написать ограничения, установить целевую ячейку, Сервис Поиск решения.

Вопрос 25. Финансовые функции

Большинство финансовых функций включены в «пакет анализа» (Сервис надстройки). Среди финансовых функций различают:

- функции для анализа инвестиций

- функции для вычисления скорости оборота денежных средств

- функции для вычисления амортизации

- функции для анализа ценных бумаг

Многие функции имеют одинаковые аргументы.

ПЛТ (ставка, Кпер, ПС, БС, тип) вычисляет размер периодической выплаты необходимой для погашения ссуды за указанное число периодов при постоянной % ставке. Ставка - % ставка по ссуде. Кпер общее число выплат по ссуде. ПС текущая стоимость вложения. БС будущий объем вложения. Тип тип выплаты.

Определить сумму ежемесячного платежа для получения 50000$ через 18 лет при 6% годовой ставке. = ПЛТ(6%/12; 18 лет*12; 0; 50000$).

ПС (ставка, Кпер, ПЛТ, БС, тип) определяет функцию приведенную к текущему моменту стоимость инвестиции. Определяет настоящее, текущее значение вклада в зависимости от дохода в будущем.

Надо накопить 20000$ за 5 лет, положили сумму в банк, при условии начисления 14% в год. Найти величину начального вклада. = ПС(14%; 5, 20000)

БС(ставка, Кпер, ПЛТ, ПС, тип) будущая стоимость.

Мы хотим накопить некоторую сумму денег и положили в банк 1000 руб. под 6% годовых, а затем целый год каждый месяц клали по 100 руб. Какая сумма будет на счету через год. = БС (6%/12; 1*12; -100: -1000; 1) = БС (6/12; 12; ; -1000)

Вопрос 26, 27. Таблица подстановки с одним, двумя параметром.

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

В случае 1ой переменной можно табулировать сразу несколько функций, зависящих от этой переменной. Составить таблицу соответствия между значениями температур в разных шкалах. В 1ый столбец начиная со второй ячейки ввести значение варьируемой переменной. Левую верхнюю ячейку блока оставить пустой. = ПРЕОБР (число; исходная единица измерения; конечная единица измерения) преобразует значение из одной мер измерения в другую. Если входные значения расположены в столбец, то формулу надо ввести в ячейку, которая располагается на 1ну строку выше и на 1ин столбец правее. Если исходные данные располагаются в строку, то формулу надо ввести на 1ну строку ниже и на 1ин столбец левее. Выделить диапазон входных значений и ячейку с формулой. Меню Данные таблица подстановки ДО (подстановка значения по столбцу или подстановка значения по строкам). Выделить весь диапазон и открыть ДО таблица подстановки.

Таблица подстановки с 2мя переменными: Мы взяли ссуду в банке 80000 руб. под 11% годовых, определить ежемесячные выплаты по ссуде при различных процентных ставках и сроках по займу. Занести значение варьируемой переменной = ПЛТ(…….) Выделить диапазон ячеек А5:Д10. Данные Таблица подстановки ДО (Подстановка по строкам $В$2, подстановка по столбцам $В$1).

АВСД

 1% ставка11%

2период3

3сумма80000

4Таб подстанов с 2 мя переменными

5345

69%

79,5%

810%

910,5%

1011%

 Х2-У2  

Подстановка по столбцам $В$1

Подстановка по строкам $А$1

АВС

1-1-1

2-1-0,9

3-1

4-0,9

А2=А1^2-B1^2

Вопрос 28. Консолидация данных.

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

Виды консолидации:

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

2) по категории подбор, объединение данных. По заголовкам строк и столбцов исходные данные могут быть размещены по разному и содержать разное колво строк и столбцов.

3) по категориям и расположению

4) со связями между исходными данными. В ДО отметить «связи с исходными данными»

Вопрос 29. Обмен данными. Связь и внедрение объектов.

1) простое копирование: выделить таблицу в Excel Правка Копировать Перейти в Word установить курсор в нужном месте Правка Вставить. Можно редактировать в Excel и в Word ничего не изменится.

2) перетаскивание (для диаграмм) чтобы выделить отдельные столбцы, надо держать Ctrl. Для перетаскивания должны быть открыты оба окна (на панели задач ПКМ выбрать нужное упорядочивание окон) и воспользоваться командой перетащить или копировать. Диаграмму из Excel выделяем и мышью перетаскиваем, чтобы скопировать нажимаем клавишу Ctrl и перетаскиваем. Можно форматировать.

3) технология OLE предполагает наличие: OLE сервера где разрабатывается объект и OLE клиента приложение в которое вставляется объект. Внедрение таблицы и диаграмм: выделить и скопировать таблицу из Excel, установить курсор в мечте вставки таблицы в Word. Меню Правка Специальная вставка, как лист Microsoft Excel (объект диаграмма) вставить ОК. Если потом в диаграмму или таблицу внести изменения, то в Excel ничего не изменится. Внедренная таблица или диаграмма увеличивает размер Word на величину внедренных объектов, но не нужно поддерживать связь между объектами.

4) связанная таблица или диаграмма. Нужно выделить и скопировать таблицу Excel установить курсор в нужном месте Word правка Специальная вставка, как лист Microsoft Excel (объект диаграмма) связать ОК.

При изменении данных в Excel в таблице или диаграмме Word произойдут изменения. Размер файла в данном случае изменится незначительно, тк в них вставляются только указатели на них. Каждый пользователь может загрузить не полный набор объектов, а только то что необходимо. При переносе данных с 1ого ПК на другой, надо переносить все связанные объекты. Возникает необходимость в обслуживании связи OLE, тк при изменении их места положения их связи разрываются, их надо восстанавливать.