Формулы и функции Excel и их применение при решении

Экономических задач

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

Ввод формул и функций предусматривает общие правила:

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

- если формула начинается с адреса ячейки, перед первым символом необходимо набрать = (знак равенства) или знак + (плюс);

- в ячейке виден результат вычислений, а сама формула отражается в строке ввода;

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

Активизируем ячейку, например G5, введем в нее формулу =E5+F5 и нажимаем ENTER или щелкнуть символ «Ввод» в строке ввода. В результате в ячейке G5 появится результат, а в строке ввода отображается сама формула.

Копирование формул из одних ячеек в другую.

Скопируем формулы из ячейки G5 в блок ячеек G6:G11.

1) Выделите ячейку G5, содержащую формулу. Выберите Правка\ Копировать. Excel окружит ячейку G5 движущейся рамочкой. Выделите блок ячеек G6:G11 как область вставки. Выберите Правка\Вставить и затем снимите выделение блока.

При копировании Excel автоматически корректирует адреса ячеек. Убедитесь в этом, для чего переводите курсор к ячейке G6 и посмотрите на строку ввода. Там сейчас находится формула =E6+F6.

Введем формулу в ячейку H5 и скопируем ее в блок H6:H11. Для этого активизируем ячейку H5 и введем формулу расчета =B5*G5.

2) С помощью кнопки “Копировать в буфер” на панели инструментов. Для этого активизируйте ячейку H5, щелкните кнопку “Копировать в буфер” напанелиинструментов выделите блок H6/H11, щелкните кнопку Вставить на панели инструментов, нажмите ENTER и ESC, чтобы выйти из режима копирования и наконец щелкните кнопкой мыши вне выделенного блока.

Ввод функций в Excel можно осуществлять несколькими способами:

- ручным набором имени функции ее аргументов;

- выбором функции из списка функций с помощью команды Вставка/Функции;

- выбором функции щелчком по символу f_x при активной строке ввода.

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

Наиболее часто используемой функцией является функция автосуммирования, поэтому она вынесена на стандартную панель инструментов.

Простым способом подсчета итоговых сумм является щелчок кнопки «Автосуммирование» на стандартной панели инструментов, например, просуммируем значения в ячейках Е5:Е11 и F5:F11. Для этого активизируйте ячейку Е12, щелкнув по ней, а затем дважды щелкните кнопку «Автосуммирование». Значение суммы появится в ячейке Е12, а в строке формул отобразится = СУММ (Е5/Е11). Изменение содержимого ячеек данного столбца приведет к автоматическому пересчету результата в ячейке Е12.

Второй способ. Для этого выделим ячейку F12, выберем команду Вставка\Функция.

На экране появится диалоговое окно со списком встроенных функций, сгруппированных по категориям. Выберем функцию СУММ (в группе Математические). Excel поместит на экране диалоговое окно, позволяющие ввести аргументы, необходимые для этой функции. Щелкните кнопку Далее и введите аргументы F5/F11 или отметьте их кнопкой мыши в ЭТ. Щелкните кнопку Готово или нажмите Enter. В ячейке F12 появится значение суммы.

Для расчета итоговой суммы по столбцу G воспользуйтесь кнопкой Автосуммирование. Для этого, скопируйте, содержимое ячейки G12 в H12 – активизируйте G12, щелкните кнопку Копировать на панели инструментов, затем активизируйте ячейку H12 и щелкните кнопку Вставить. Снимите выделение клавишей ESC, проверьте, как Excel модифицировать формулу.

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

Вызов Мастер функций осуществляется из меню Вставка/Функции, выбрать категории Финансовые.

Финансовые функции делятся на:

· функции для анализа аннуитетов и инвестиционных проектов

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

· функции для расчета амортизационных платежей

· вспомогательные функции.

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

Функциидля анализа аннуитетов и эффективности инвестиционных проектов.

Б3 позволяет определить будущую величину потока платежей при заданных исходных данных.

Б3 (норма, число периодов, выплата, нз, тип).

П3 позволяет определить текущую (на момент начала опе­ра­­ции, величину аннуитета)

П3 (норма, число периодов, выплата, вс, тип).

КПЕР определяет общее число выплат (либо срок, через который на­чальная сумма займа достигнет заданного значения).

КПЕР (норма, выплата, нз, бс, тип).

БЭРАСПИС позволяет определить будущую ценность инвестиций (или единой суммы), если процентная ставка меняется по времени (по правилу сложного процента).

БЭРАСПИС (первичное, план).

НОРМА вычисляет процентную ставку (рентабельность, операции).

НОРМА (число периодов, выплата, нз, бс, тип).

ППЛАТ вычисляет величину периодического платежа.

ППЛАТ (норма, число периодов, нз, бс, тип).

ПЛПРОЦ вычисляет ту часть платежа, которая составляет его процентную часть.

ПЛПРОЦ (норма, период, число периодов, тс, бс).

ОБЩПЛАТ вычисляет накопленные проценты (для расчета плана погашения кредита)

ОБЩПЛАТ (ставка, число периодов, нз, начальный период, конечный период).

ОСНПЛАТ вычисляет ту часть платежа, которая составляет его основную часть. Вычисляет накопленную сумму погашенного долга (для расчета плана погашения кредита).

 

ОБЩДОХОД ОБЩДОХОД (ставка, число периодов, нз, начальный период, конечный период).

НПЗ определяет текущую (современную), приведенную к настоящему моменту времени величину потока платежей.

НПЗ (норма, значения).

ВНДОХ вычисляет внутреннюю норму рентабельности, т.е. процентную ставку, при которой капитализация регулярного дохода дает сумму, равную первоначальным инвестициям, ставку, при которой NPV=0 (критерий инвестиционных проектов).

ВНДОХ (значения, предположение).

МВСД вычисляет модифицировано внутреннюю норму рентабельности (с учетом предположения о реинвестировании).

МВСД (значение, финансовая норма, реинвестированная норма).

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

ЧИСТНЗ (ставка, значение, даты).

6.5. Работа со списками данных

 

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

Например:

 

 

 

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

Данные, организованные в списке, в терминологии Excel называют базой данных (БД) При этом строки таблицы – это записи БД, а столбцы – поля записей БД. Запись состоит из элементов, число которых равно числу полей записи.

При создании списка на рабочем листе Excel необходимо выполнить следующие правила:

· На одном рабочем листе не следует помещать более одного списка, поскольку некоторые операции, например, фильтрация, работают в определенный момент только с одним списком;

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

· Список может занимать весь рабочий лист;

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

· Для имен столбцов следует использовать шрифт, тип данных, выравнивание, формат рамки или стиль прописных букв, отличных от тех, которые использовались для данных списка;

· Чтобы отделить имена столбцов от данных, следует разместить рамку по нижнему краю ячеек строки с именами столбцов. Не рекомендуется использовать пустые строки или пунктирные линии;

Со списком можно выполнять следующие операции: сортировку, фильтрацию, вычисление итогов.

С помощью фильтра можно отобрать из списка записи, удовлетворяющие определенным условиям. Например, количество иждивенцев 1, то будут те фамилии, которые имеют одного иждивенца. Имеются два вида фильтров:

· Автофильтр;

· Расширенный фильтр.

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

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

Для создания нового списка необходимо:

· Поместить указатель мыши в левую верхнюю ячейку списка. Записать в нее имя первого поля;

· Записать в соседние ячейки этой же строки имена всех остальных полей списка;

· Изменить ширину столбцов таблицы, исходя из длины имени каждого поля;

· Выделить и присвоить ячейкам, образующим поле данных, нужные параметры форматирования (например: текстовый, числовой и др.);

· Начиная со второй строки, ввести записи списка. Элементы записей могут содержать значения и формулы.

Таблица, оформленная таким образом, автоматически распознается Excel как список.

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

Чтобы отсортировать весь список, достаточно выделить одну ячейку и выбрать в меню Данные команду Сортировка. Excel автоматически выделит весь список.

Команда Сортировка осуществляется через диалоговое окно. В трех полях ввода окна «Сортировка» можно задать ключи, по которым будет выполнена «Сортировка». В первом поле (в списке) необходимо выбрать столбец, по которому Excel должен отсортировать данные например, столбец «Количество иждивенцев»). Во втором поле диалогового окна «Сортировка» можно задать ключевой ключ сортировки, например «Ф.И.О». Аналогично третий ключ сортировки.

В диалоговом окне «Сортировка» имеется режим «Параметры». Он позволяет установить порядок сортировки по первому ключу – обычный или определяемый пользователем, задать учет строчных или прописных букв (учет регистров символов), а также направление сортировки – по возрастанию или по убыванию.

Для быстрой сортировки на панели инструментов «Стандартная» находятся кнопки:

А Я

- сортировать по возрастанию;

 

А Я

- сортировать по убыванию.

 

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

- активизировать любую ячейку списка;

- выполнить команду Форма, меню Данные.

На экране появится диалоговое окно, в котором будут представлены имена полей и их содержимое для первой записи списка. Последовательность имен полей в диалоговом окне формы данные соответствуют их последовательности в списке.

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

Кнопка «Критерий» позволяет задавать условия, которым должны отвечать выводимые на экран записи.

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

С помощью фильтров можно выводить и просматривать только те данные, которые удовлетворяют определенным условиям. Excel позволяет быстро и удобно просматривать требуемые данные из списка с помощью простого средства – автофильтра. Более сложные запросы к базе данных можно реализовать с помощью команды Расширенный фильтр.

Чтобы использовать автофильтр, надо сначала выделить область списка для поиска с заголовками полей. Затем выполнить команду Автофильтр в меню Данные.

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

Элемент столбца, который выделен в раскрывающемся списке, называется критерием фильтра.

Чтобы удалить критерии фильтра для отдельного столбца, надо выбрать в меню Данные команду Фильтр, а затем – команду Показать все. С помощью автофильтра можно для каждого столбца задать пользовательские критерии отбора записей, например, вывести на экран только те записи, значения полей которых находятся в пределах заданного интервала. Чтобы задать пользовательский критерий, надо в раскрывающемся списке выбрать параметр Настройка …, а затем в диалоговом окне «Пользовательский автофильтр» ввести нужные критерии.

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

Чтобы использовать команду Расширенный фильтр необходимо:

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

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

- выделить область поиска в исходном списке.

Ввод вычисляемого критерия поиска в таблицу критериев требует выполнение следующих правил:

1. Формула должна выводить логическое значение. «Истина» или «Ложь». После выполнения поиска на экран выводятся только те строки, для которых результатом вычисления формулы будет «Истина»;

2. Формула должна ссылаться хотя бы на один столбец в списке.

Формирование итогов в электронной таблице:

Команда Итоги из меню Данные берет фрагмент списка, применяет функцию типа «Сумм» к выделенным данным, а затем отображает результаты. Чтобы команда Итоги работала, список сначала должен быть отсортирован. После сортировки можно подсчитать итоги по группам записей. Команда Итоги позволяет подводить итоги более чем по одному полю из группы записей, а также добавит обрабатываемые поля и применять для вычисления итогов другие функции.

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

Мастер диаграмм предоставляет широкий выбор различных диаграмм.

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

· просмотра;

· перемещения;

· редактирования.

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

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

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

Добавить данные к диаграмме можно:

· Выделить данные на рабочем листе и перетащить их на диаграмму;

· Выделить данные на рабочем листе и скопировать их на диаграмму (пункты меню: Копировать/Вставить);

· Использовать команду Вставка/Новые данные

Excel облегчает работу с электронными таблицами, обеспечивая просмотр данных в следующих режимах:

- параллельный просмотр областей одной таблицы;

- параллельный просмотр нескольких файлов одновременно;

- просмотр с изменением масштаба;

- просмотр таблицы с прокруткой и фиксацией отдельных строк и столбцов.

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

В Excel реализованы механизмы автозамены, автоввода, автовычислений, автофильтра.

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

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

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

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

Вопросы для самоконтроля

1. Перечислите функции и возможности табличных процессоров.

2. Охарактеризуйте интерфейс табличного процессора Excel.

3. Приведите этапы построения таблиц в Excel.

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

5. Дать правила ввода формул и функции в Excel.

6. Какие функции в Excel Вы знаете? Приведите пример финансовой функции в Excel и дать ее описание.

7. Покажите связывание таблиц.

8. Назовите виды адресации в Excel и в чем их отличие. Как они используются?

9. Что такое консолидация?

10. Дать характеристику объекту OLE в Excel.

11. Что такое «база данных» в Excel?

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

13. Правила построения базы данных в Excel.

14. Назовите отличительные особенности команд «Расширенный фильтр» и «Автофильтр».

15. Укажите, какие Вы знаете типы диаграмм, используемых для интерпретации данных электронных таблиц.

16. Охарактеризовать режимы в Excel: автозамены, автоввода, автовычисления.