39. Функция поиска данных в некотором диапазоне. Пример финансовых функций в табличном процессе.

ФУНКЦИЯ ПОИСКА ДАННЫХ В НЕКОТОРОМ ДИАПАЗОНЕ

ПРОСМОТР(ИСКОМОЕ ЗНАЧЕНИЕ; ДИАПАЗОН) группа функций ссылки

и массивы (1-й вариант — векторный просмотр; 2-й вариант — массив).

Диапазон представляет из себя блок, состоящий из двух колонок. Поиск ведется по

искомому значению в первой колонке диапазона, а в текущую ячейку выбирается соответствующее значение из второй колонки диапазона. Если точное соответствие не обнаружено, то выбирается наибольшее значение в диапазоне, меньшее или равное искомому.

Финансовые функции

НАКОПДОХОД Возвращает накопленный процент по ценным бумагам с периодической выплатой процентов.

НАКОПДОХОДПОГАШ Возвращает накопленный процент по ценным бумагам, проценты по которым выплачиваются в срок погашения.

АМОРУМ Возвращает величину амортизации для каждого периода, используя коэффициент амортизации.

АМОРУВ Возвращает величину амортизации для каждого периода.

ДНЕЙКУПОНДО Возвращает количество дней от начала действия купона до даты соглашения.

ДНЕЙКУПОН Возвращает число дней в периоде купона, содержащем дату соглашения.

ДНЕЙКУПОНПОСЛЕ Возвращает число дней от даты соглашения до срока следующего купона.

ДАТАКУПОНПОСЛЕ Возвращает следующую дату купона после даты соглашения.

ЧИСЛКУПОН Возвращает количество купонов, которые могут быть оплачены между датой соглашения и сроком вступления в силу.

ДАТАКУПОНДО Возвращает предыдущую дату купона перед датой соглашения.

ОБЩПЛАТ Возвращает общую выплату, произведенную между двумя периодическими выплатами.

ОБЩДОХОД Возвращает общую выплату по займу между двумя периодами.

ФУО Возвращает величину амортизации актива для заданного периода, рассчитанную методом фиксированного уменьшения остатка.

ДДОБ Возвращает величину амортизации актива за данный период, используя метод двойного уменьшения остатка или иной явно указанный метод.

СКИДКА Возвращает норму скидки для ценных бумаг.

РУБЛЬ.ДЕС Преобразует цену в рублях, выраженную в виде дроби, в цену в рублях, выраженную десятичным числом.

РУБЛЬ.ДРОБЬ Преобразует цену в рублях, выраженную десятичным числом, в цену в рублях, выраженную в виде дроби.

ДЛИТ Возвращает ежегодную продолжительность действия ценных бумаг с периодическими выплатами по процентам.

ЭФФЕКТ Возвращает действующие ежегодные процентные ставки.

БС Возвращает будущую стоимость инвестиции.

БЗРАСПИС Возвращает будущую стоимость первоначальной основной суммы после начисления ряда сложных процентов.

ИНОРМА Возвращает процентную ставку для полностью инвестированных ценных бумаг.

ПРПЛТ Возвращает величину выплаты прибыли на вложения за данный период.

ВСД Возвращает внутреннюю ставку доходности для ряда потоков денежных средств.

ПРОЦПЛАТ Вычисляет выплаты за указанный период инвестиции.

МДЛИТ Возвращает модифицированную длительность Маколея для ценных бумаг с предполагаемой номинальной стоимостью 100 рублей.

МВСД Возвращает внутреннюю ставку доходности, при которой положительные и отрицательные денежные потоки имеют разные значения ставки.

НОМИНАЛ Возвращает номинальную годовую процентную ставку.

КПЕР Возвращает общее количество периодов выплаты для данного вклада.

ЧПС Возвращает чистую приведенную стоимость инвестиции, основанной на серии периодических денежных потоков и ставке дисконтирования.

ЦЕНАПЕРВНЕРЕГ Возвращает цену за 100 рублей нарицательной стоимости ценных бумаг с нерегулярным первым периодом.

ДОХОДПЕРВНЕРЕГ Возвращает доход по ценным бумагам с нерегулярным первым периодом.

ЦЕНАПОСЛНЕРЕГ Возвращает цену за 100 рублей нарицательной стоимости ценных бумаг с нерегулярным последним периодом.

ДОХОДПОСЛНЕРЕГ Возвращает доход по ценным бумагам с нерегулярным последним периодом.

ПЛТ Возвращает величину выплаты за один период аннуитета.

ОСПЛТ Возвращает величину выплат в погашение основной суммы по инвестиции за заданный период.

ЦЕНА Возвращает цену за 100 рублей нарицательной стоимости ценных бумаг, по которым производится периодическая выплата процентов.

ЦЕНАСКИДКА Возвращает цену за 100 рублей номинальной стоимости ценных бумаг, на которые сделана скидка.

ЦЕНАПОГАШ Возвращает цену за 100 рублей номинальной стоимости ценных бумаг, проценты по которым выплачиваются в срок погашения.

ПС Возвращает приведенную (к текущему моменту) стоимость инвестиции.

СТАВКА Возвращает процентную ставку по аннуитету за один период.

ПОЛУЧЕНО Возвращает сумму, полученную к сроку погашения полностью обеспеченных ценных бумаг.

АПЛ Возвращает величину линейной амортизации актива за один период.

АСЧ Возвращает величину амортизации актива за данный период, рассчитанную методом суммы годовых чисел.

РАВНОКЧЕК Возвращает эквивалентный облигации доход по казначейскому чеку.

ЦЕНАКЧЕК Возвращает цену за 100 рублей нарицательной стоимости для казначейского чека.

ДОХОДКЧЕК Возвращает доход по казначейскому чеку.

ПУО Возвращает величину амортизации актива для указанного или частичного периода при использовании метода сокращающегося баланса.

ЧИСТВНДОХ Возвращает внутреннюю ставку доходности для графика денежных потоков, которые не обязательно носят периодический характер.

ЧИСТНЗ Возвращает чистую приведенную стоимость для денежных потоков, которые не обязательно являются периодическими.

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

ДОХОДСКИДКА Возвращает годовой доход по ценным бумагам, на которые сделана скидка (пример — казначейские чеки).

ДОХОДПОГАШ Возвращает годовой доход от ценных бумаг, проценты по которым выплачиваются в срок погашения.

40 вопрос

Листы Excel

Ярлыки листов Excel

Ярлыки листов Excel располагаются внизу слева в области рабочей книги. Имя каждого рабочего листа появляется на соответствующем ярлыке. Однако любому ярлыку может быть дано другое имя, длиной не более 31 символа.

Это имя может быть использовано при адресации листа в формулах Excel.

Адресация листа

Для использования в формуле данных, расположенных на определенных листах, необходимо после названия листа поставить восклицательный знак, затем адрес ячейки. Диапазон листов в Excel задается через двоеточие.

Примеры адресации листов:

Лист1!А1  Sheet12!G23  Квартал1!В4:Е7  Лист1!:ЛистЗ!А2

Во вновь создаваемую книгу по умолчанию Excel вставляет три листа.   

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

Рис. 1. Контекстное меню

Имя рабочего листа Excel можно ввести не используя контекстное меню, для этого:

1. Щелкните дважды по ярлыку рабочего листа.

2. Введите имя листа в область ярлыка и нажмите Enter.

Для перемещения между листами Excel щелкните ярлык необходимого листа. Имя текущего листа выделится жирным шрифтом.

Кнопки прокрутки ярлыков листов могут быть использованы для перехода к другим листам. Для перемещения по списку листов щелкните кнопки прокрутки ярлыков.

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

Рис. 2. Ярлыки листов Excel

Операции удаления, перемещения и переименования листа Excel отменить нельзя.

Добавление нового листа Excel

Существует несколько способов добавления новых листов в рабочую книгу Excel.

Команда Вставка, Лист используется для добавления нового листа в текущую рабочую книгу. Новый лист будет вставлен перед текущим листом.

Любые формулы, использующие ссылки на листы Excel, будут автоматически обновляться при добавлении нового листа.

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

1. Щелкните правой кнопкой мыши по ярлыку текущего рабочего листа для открытия контекстного меню.

2. Выберите Добавить. Появится диалоговое окно Вставка.

3. Выберите Лист из диалогового окна и нажмите ОК.

Удаление листов Excel

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

Копирование и перемещение листов Excel

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

1. Выделите лист для перемещения или копирования.

2. Выберите Правка, Переместить или скопировать лист или вызовите контекстное меню и выберите Переместить или скопировать (рис. 3).

Появится диалоговое окно Переместить или скопировать.

3. В поле В книгу выберите книгу, в которую листы будут скопированы или перемещены. Книга должна быть открыта, чтобы ее можно было выбрать,

4. Выберите лист, перед которым лист(ы) будет вставлен в поле Перед листом.

5. Включите опцию Создавать копию для копирования. В противном случае данные будут перемещены.

6. Нажмите ОК после завершения.

Рис. 3. Диалоговое окно Переместить или скопировать

Как и ячейки таблицы Excel, листы также могут быть перемещены или скопированы с помощью перетаскивания. Этот метод может быть использован для перемещения или копирования листов в пределах одной рабочей книги или для открытых книг из одной в другую.

Для перемещения листа перетащите ярлык выделенного листа на новую позицию. Указатель (треугольник) отмечает положение, куда будут помещены листы, когда кнопка мыши будет отпущена.

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

Связывание листов

Например, вы разместили на одном рабочем листе ассортимент товаров и цены, на другом - продажи за определенный срок (указав при этом название, цену, количество проданных единиц). Для того, чтобы не вводить повторно первые две колонки, их можно скопировать. Сделать это очень просто: необходимо выделить на первом рабочем листе нужный диапазон, выбрать "Правка -> Копировать", перейти на другой лист и выбрать "Правка -> Вставить". Но если содержимое первого листа изменится, например, изменится цена, эту операцию придется повторить. Для того, чтобы все изменения листа-источника отображались в листе, куда были вставлены данные, необходимо, перейдя на место вставки, выбрать "Правка -> Специальная вставка".

С помощью "Специальной вставки" можно обеспечить связь между рабочими листами.

Для того чтобы обеспечить изменения содержимого вставленного фрагмента одновременно с изменением данных на листе-источнике, необходимо нажать кнопку "Вставить связь". Итого, мы получим связанные рабочие листы, изменения в одном из них приводят к изменению в другом.

Теперь можно убедиться, что все изменения на листе-источнике, приведут к изменениям на листе вставки. Единственная проблема: Excel вставит только содержимое ячеек, а не форматы - они будут просто проигнорированы. Для сохранения форматов после того, как вставлены значения, необходимо повторно выбрать "Правка -> Специальная вставка", в окне "Специальная вставка" отметить переключатель "Форматы" и нажать "ОК".

Таким же образом можно связывать рабочие книги: скопировать фрагмент данных в одной рабочей книге, и перейдя в другую, вставить связь и скопировать форматы.

41.Консолидация данных - это объединение данных из нескольких исходных областей-источников. В результате консолидации данных получается консолидированная таблица (область-назначения).

Консолидация по категориям. Не обязательным является одинаковое расположение данных в сводимых областях, но обязательным должны быть имена-категории сводимых диапазонов. Для консолидации данных используются имена строк и/или столбцов, которые должны совпадать с учетом регистра букв.

Технология консолидации:

1) Выделить начальную ячейку области-назначения.

2) Выполнить команду Данные→Консолидация.

3) В появившемся диалоговом окне выбрать функцию объединения в поле Функция.

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

5) Выделить область-источник, которую нужно добавить в консолидированную таблицу (при выделении диапазонов названия столбцов/строк нужно включать в выделенные области-источники, они будут перенесены автоматически в область назначения. Ссылка будет добавлена в поле Ссылка.

6) С помощью кнопки Добавить повторить п.5 для всех областей-источников.

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

Технология консолидации:

1) Выделить начальную ячейку области-назначения.

2) Выполнить команду Данные→Консолидация.

3) В появившемся диалоговом окне выбрать функцию объединения в поле Функция.

4) Выделить область-источник, которую нужно добавить в консолидированную таблицу (без имен-категорий!). Ссылка будет добавлена в поле Ссылка.

5) С помощью кнопки Добавить повторить п.4 для всех областей-источников.

Обмен данными в MS Excel

Обмен данными позволяет пользователю Excel импортировать в свои таблицы объекты из других прикладных программ и передавать (экспортировать) свои таблицы для встраивания в другие объекты.

Концепция обмена данными является одной из основных концепций среды Windows. Между объектами, которые обрабатываются различными прикладными программами, создаются информационные связи, например, между таблицами и текстами. Эти информационные связи реализованы динамически, например, копия таблицы, встроенная в текст, будет обновляться (актуализироваться) всякий раз, когда в ее оригинал вносятся изменения.

К сожалению, не все прикладные программы поддерживают механизм обмена данными, но программа Excel относится здесь к лидерам. Она поддерживает новейший стандарт в обмене данными, который называется OLE 2.0 (Object Linking and Embedding).

42 вопрос

Подбор параметра

Подбор параметра является частью блока задач, который иногда называют инструментами анализа "что-если". Когда желаемый результат одиночной формулы известен, но неизвестны значения, которые требуется ввести для получения этого результата, можно воспользоваться средством «Подбор параметра» выбрав команду Подбор параметра в меню Сервис. При подборе параметра Microsoft Excel изменяет значение в одной конкретной ячейке до тех пор, пока формула, зависимая от этой ячейки, не возвращает нужный результат.

Например, средство «Подбор параметра» используется для изменения процентной ставки в ячейке B3 в сторону увеличения до тех пор, пока размер платежа в ячейке B4 не станет равен 900,00р.

Поиск решения

Процедура поиска решения  1. Создайте таблицу с формулами, которые устанавливают связи между ячейками.

2. Выделите целевую ячейку, которая должна принять необходимое значение, и выберите команду: - В Excel 2007 Данные/Анализ/Поиск решения; - В Excel 2003 и ниже Tools >Solver (Сервис > Поиск решения). Поле Set Target Cell (Установить целевую ячейку) открывшегося диалогового окна надстройки Solver (Поиск решения) будет содержать адрес целевой ячейки. 3. Установите переключатели Equal To (Равной), задающие значение целевой ячейки, — Мах (максимальному значению), Min (минимальному значению) или Value of (значению). В последнем случае введите значение в поле справа. 4. Укажите в поле By Changing Cells (Изменяя ячейки), в каких ячейках программа должна изменять значения в поисках оптимального результата. 5. Создайте ограничения в списке Subject to the Constraints (Ограничения). Для этого щелкните на кнопке Add (Добавить) и в диалоговом окне Add Constraint (Добавление ограничения) определите ограничение.

6. Щелкните на кнопке на кнопке Options (Параметры), и в появившемся окне установите переключатель Неотрицательные значения (если переменные должны быть позитивными числами), Линейная модель (если задача, которую вы решаете, относится к линейным моделям)

7. Щелкнув на кнопке Solver (Выполнить), запустите процесс поиска решения.

8 . Когда появится диалоговое окно Solver Results (Результаты поиска решения), выберите переключатель Keep Solve Solution (Сохранить найденное решение) или Restore Original Values (Восстановить исходные значения). 9. Щелкните на кнопке ОК.

 

Параметры средства Поиск решения Максимальное время -  служит для ограничения времени, отпущенного на поиск решения задачи. В этом поле можно ввести время в секундах, не превышающее 32 767 (примерно девять часов); значение 100, используемое по умолчанию, вполне приемлемо для решения большинства простых задач.

Предельное число итераций - управляет временем решения задачи путем ограничения числа вычислительных циклов (итераций). Относительная погрешность - определяет точность вычислений. Чем меньше значение этого параметра, тем выше точность вычислений. Допустимое отклонение - предназначен для задания допуска на отклонение от оптимального решения, если множество значений влияющей ячейки ограничено множеством целых чисел. Чем больше значение допуска, тем меньше времени требуется на поиск решения. Сходимость - применяется только к нелинейным задачам. Когда относительное изменение значения в целевой ячейке за последние пять итераций становится меньше числа, указанного в поле Сходимость, поиск прекращается. Линейная модель - служит для ускорения поиска решения путем применения к задаче оптимизации линейной модели. Нелинейные модели предполагают использование нелинейных функций, фактора роста и экспоненциального сглаживания, что замедляет вычисления. Неотрицательные значения - позволяет установить нулевую нижнюю границу для тех влияющих ячеек, для которых не было задано соответствующее ограничение в диалоговом окне Добавить ограничение. Автоматическое масштабирование - используется, когда числа в изменяемых ячейках и в целевой ячейке существенно различаются. Показывать результаты итераций - приостанавливает поиск решения для просмотра результатов отдельных итераций. Загрузить модель - после щелчка на этой кнопке отрывается одноименное диалоговое окно, в котором можно ввести ссылку на диапазон ячеек, содержащих модель оптимизации. Сохранить модель - служит для отображения на экране одноименного диалогового окна, в котором можно ввести ссылку на диапазон ячеек, предназначенный для хранения модели оптимизации. Оценка линейная - выберите этот переключатель для работы с линейной моделью.  Оценка квадратичная - выберите этот переключатель для работы с нелинейной моделью. Разности прямые - используется в большинстве задач, где скорость изменения ограничений относительно невысока. Увеличивает скорость работы средства Поиск решения. Разности центральные - используется для функций, имеющих разрывную производную. Данный способ требует больше вычислений, однако его применение может быть оправданным, если выдано сообщение о том, что получить более точное решение не удается. Метод поиска Ньютона - требует больше памяти, но выполняет меньше итераций, чем в методе сопряженных градиентов. Метод поиска сопряженных градиентов - реализует метод сопряженных градиентов, для которого требуется меньше памяти, но выполняется больше итераций, чем в методе Ньютона. Данный метод следует использовать, если задача достаточно большая и необходимо экономить память или если итерации дают слишком малое отличие в последовательных приближениях.

43 вопрос

Access является одной из самых популярных настольных систем управления базами данных (СУБД), которая может работать с текстовыми файлами, с электронными таблицами и базами данных наиболее популярных форматов.

База данных хранит совокупность данных и объектов, относящихся к определенной задаче. Она помогает нам упорядочить информацию по различным признакам и позволяет быстро делать выборку с произвольным сочетанием признаков. В качестве данных может использоваться любая информация, хранящаяся на диске компьютера: сочетания символов, слова, файлы, рисунки, объекты и т.д. Методы ввода и хранения данных определяют структуру базы данных.

Access позволяет создавать реляционные базы данных, в которых данные хранятся в виде таблицы. Его можно использовать для анализа данных, для создания динамических веб-страниц, в которых автоматически будут отображаться изменения данных. С его помощью можно создавать приложения баз данных, например приложения клиент/сервер.

• Access может работать в качестве:

• автономной СУБД для настольных систем;

• СУБД для файлового сервера;

• как интерфейсный клиент для связи с серверными СУБД масштаба предприятия, например, с Microsoft SQL Server.

Access 2003 представляет собой мощную платформу для создания настольных приложений и приложений клиент/сервер уровня подразделения или целого предприятия.

Access 2003 позволяет преобразовывать базы данных Microsoft Access в формат Microsoft SQL Server, предоставляет двухстороннюю поддержку XML. Для интеграции инструментария Access с базами данных MS SQL используются быстродействующие средства доступа к базам данных на основе интерфейса OLE-DB.