Консолидация данных

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

Типы функций

Функции Excel сгруппированы по областям использования (категориям), что позволяет быстро найти нужную функцию с помощью панели Формулы - группа Библиотека функций - ВставитьФункцию или щелкнув на панели инструментов по пиктограмме f_{x .} Откроется диалоговое окно, в котором представлены 11 категорий функций, а также указание на 10 недавно использовавшихся и полный алфавитный перечень (сначала англ., затем русский). Внутри выбранной категории функции упорядочены в алфавитном порядке, сначала в англ., затем в русском. Рассмотрим наиболее часто используемые функции.

Математические функции.

Функция СУММ находит сумму чисел из указанного диапазона ячеек.

Синтаксис:

СУММ (число1; число2;…;число N),

где число1, число2,…- числа, которые суммируются.

Например, формула =СУММ(С22:С26) использует функцию СУММ для того, чтобы сложить значения в диапазоне ячеек С22:С26. Эта формула аналогична формуле =(С22+С23+С24+С25+С26) с перечислением адресов всех ячеек.

Чтобы автоматически просуммировать данные в блоке находящихся рядом ячеек, щелкните на кнопке Автосуммирование Формулы в группе Библиотека функций и убедитесь, что требуемый диапазон выделен правильно (выделение можно изменить с помощью мыши). Если блок выделен правильно, щелкните повторно по кнопке Автосуммирование или нажмите клавишу Enter. Просуммировать данные в любом диапазоне можно, выделив его, а затем щелкнув по кнопке Автосуммирование.

Функция СУММЕСЛИ возвращает сумму чисел из указанного диапазона ячеек по заданному критерию.

Синтаксис:

СУММЕСЛИ (диапазон; условие; диапазон суммирования)

Аргументы:

диапазон	Диапазон вычисляемых ячеек
условие	Условие для отбора данных
диапазон суммирования	Диапазон фактических ячеек для суммирования

Например, надо просуммировать все значения из диапазона ячеек D7:D11, которые меньше 100. В ячейки введены значения (100, 200, 50, 40, 78).

Для ввода этой функции выполните следующие действия:

1. Установите курсор в ту ячейку, в которой должен получиться результат.

2. Вызовите функцию СУММЕСЛИ.

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

4. Нажмите ОК.

В результате выполнения просуммируются значения, которые меньше 100. Результат равен 168. Диалоговое окно функции СУММЕСЛИ см. на рис. ниже.

Статистические функции.

Функция СРЗНАЧ находит среднее значение чисел из указанного диапазона ячеек.

Синтаксис:

СРЗНАЧ(число 1; число 2;…;число N).

Аргументы - те же, что и у функции СУММ.

Например, формула =СРЗНАЧ(С22:С26) использует функцию СРЗНАЧ для того, чтобы вычислить среднее значение данных в диапазоне ячеек С22:С26.

Функция МАКС находит максимальное значение среди чисел из указанного диапазона ячеек.

Синтаксис:

МАКС(число1; число2;…;число N).

Аргументы - те же, что и у функции СУММ.

Например, формула =МАКС(С22:С26) использует функцию МАКС для того, чтобы найти максимальное значение в диапазоне ячеек С22:С26.

Функция МИН находит минимальное значение среди чисел из указанного диапазона ячеек.

Синтаксис:Мин(число1; число2;…;число N).

Аргументы - те же, что и у функции СУММ.

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

Функция РАНГ возвращает ранг числа в списке чисел. Ранг числа – это его величина относительно других значений в списке.

Синтаксис:

РАНГ(число; ссылка; порядок).

Аргументы:

число	Число, для которого определяется ранг
ссылка	Массив или ссылка на список чисел. Нечисловые значения в ссылке игнорируются. Используется абсолютная адресация.
порядок	Число, определяющее способ упорядочения. Если порядок равен 0 или опущен, то ранг числа определяется так, как если бы ссылка была списком, отсортированным в порядке убывания, если 1, то в обратном.

Функция РАНГ присваивает повторяющимся числам одинаковый ранг.

Например, формула =РАНГ(С22;$C$22:$C$26,0) использует функцию РАНГ для того, чтобы определить ранг значений из ячейки С22 в диапазоне ячеек С22:С26 в порядке убывания.

Функция ЧАСТОТА

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

Синтаксис

ЧАСТОТА(массив_данных; массив_интервалов)

Массив_данных  — массив или ссылка на множество данных, для которых вычисляются частоты. Если аргумент «массив_данных» не содержит значений, функция ЧАСТОТА возвращает массив нулей.

Массив_интервалов  — массив или ссылка на множество интервалов, в которые группируются значения аргумента «массив_данных». Если аргумент «массив_интервалов» не содержит значений, функция ЧАСТОТА возвращает количество элементов в аргументе «массив_данных». Функция ЧАСТОТА вводится как формула массива после выделения интервала смежных ячеек, в которые требуется вернуть полученный массив распределения. Количество элементов в возвращаемом массиве на единицу больше числа элементов в массиве «массив_интервалов». Дополнительный элемент в возвращаемом массиве содержит количество значений, превышающих верхнюю границу интервала, содержащего наибольшие значения. Например, при подсчете трех диапазонов значений (интервалов), введенных в три ячейки, убедитесь в том, что функция ЧАСТОТА возвращает значения в четырех ячейках. Дополнительная ячейка возвращает число значений в аргументе «массив_данных», превышающих значение верхней границы третьего интервала. Функция ЧАСТОТА игнорирует пустые ячейки и текст. Для ввода функции нажмите клавиши CTRL+SHIFT+ВВОД.

Функция СЧЕТЕСЛИ возвращает количество чисел из указанного диапазона ячеек по заданному критерию.

Синтаксис:

СЧЕТЕСЛИ (диапазон; условие)

Аргументы:

диапазон	Диапазон, в котором подсчитывается количество непустых ячеек
условие	Условие для отбора данных

Например: надо подсчитать количество значений из диапазона ячеек D7:D11, которые меньше 100. В ячейки введены значения (100, 200, 50, 40, 78).

Для ввода этой функции выполните следующие действия:

1. Установите курсор в ту ячейку, в которой должен получиться результат.

2. Вызовите через Мастер функций функцию СЧЕТЕСЛИ.

3. Задайте в появившемся диалоговом окне диапазон, условие.

4. Нажмите ОК.

Диалоговое окно функции СЧЕТЕСЛИ после ввода диапазона и условия выглядит так:

В результате выполнения подсчитывается количество значений, которые меньше 100. Результат равен 3.

Логические функции.

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

Функция ЕСЛИ. При выборе этой функции появляется диалоговое окно.

В строку «Логическое выражение» вводится условие задачи.

В примере сравнивается содержимое ячейки С14 со значением 12.

В строку «Значение_если_истина» вводится значение при ответе истина.

В примере к содержимому ячейки С3 прибавляется 50% содержимого ячейки С15, если условие выполняется.

В строку «Значение_если_ложь» вводится значение при ответе ложь.

В примере к содержимому ячейки С3 прибавляется 40% содержимого ячейки С15, если условие не выполняется.

Есть и другие логические функции, например, логическое И (одновременное выполнение условий), логическое ИЛИ, НЕ.

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

Расчет периодических платежей ПЛТ, ПРПЛТ, ОСПЛТ.

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

Синтаксис:

ПЛТ(ставка; кпер; пс; бс; тип)

Аргументы:

ставка	Процентная ставка за период
кпер	Общее число периодов выплат
пс	Текущее значение, т.е. общая сумма, которую составят будущие платежи
бс	Будущая стоимость, или баланс наличности, который нужно достичь после последней выплаты. Если аргумент бс опущен, то он полагается равным 0
тип	Число 0 или 1, обозначающее, когда должна производиться выплата. Если тип равен 0 или опущен, то оплата производится в конце периода, если 1 – то в начале периода

Пример.

Предположим, что необходимо накопить 4000 тыс.руб. за 3 года, откладывая постоянную сумму в конце каждого месяца. Какой должна быть эта сумма, если норма процента по вкладу составляет 12% годовых.

Решение.

Общее число периодов вкладов составляет 3*12 (аргумент кпер) и ставка процента за период 12%/12 (аргумент норма). Аргумент тип=0, так как вклады будут производиться в конце месяца. Величина ежемесячных выплат:

ПЛТ(12%/12,12*3,,4000)=-92,86 тыс.руб.

2. Функция ПРПЛТ вычисляет величину выплаты по процентам за конкретный период на основе периодических, постоянных выплат и постоянной процентной ставки (например, равномерного погашения займа).

Синтаксис:

ПРПЛТ (ставка; период; кпер; пс; бс)

Аргументы:

ставка	Норма прибыли за период
период	Период, для которого требуется найти прибыль (1 до кпер)
кпер	Общее число периодов выплат годовой ренты
пс	Текущая стоимость, или общая сумма, всех будущих платежей с настоящего момента
бс	Будущая стоимость или баланс наличности, который нужно достичь после последней выплаты. Если аргумент бс опущен, то он полагается равным 0

Например, вычислите платежи по процентам за первый месяц от трехгодичного займа в 800 тыс. руб. из расчета 10% годовых.

Решение.

Определяем число периодов и ставку за период: норма=10%/12, кпер=12*3. Расчет производим за первый период: ПРПЛТ(10%/12,1,12*3,800)=-6,667 тыс. руб.

3. Функция ОСПЛТ вычисляет величину основного платежа (выплаты задолженности) по займу, который погашается равными платежами в конце или начале каждого расчетного периода, на указанный период.

Синтаксис:

ОСПЛТ (ставка; период, кпер; пс; бс)

Аргументы:

ставка	Норма прибыли за период
период	Период (от 1 до кпер)
кпер	Общее число периодов выплат годовой ренты
пс	Текущая стоимость, или общая сумма, всех будущих платежей с настоящего момента
бс	Будущая стоимость или баланс наличности, который нужно достичь после последней выплаты. Если аргумент бс опущен, то он полагается равным 0

Например, вычислите сумму основного платежа по займу за первый год, от трехгодичного займа в 70 тыс. руб. из расчета 17% годовых.

Решение.

Расчет производим за первый год: ОСПЛТ(17%,1,3,70000)= -19780,16 руб.

4. Функция БС вычисляет будущее значение вклада на основе периодических постоянных платежей и постоянной процентной ставки. Функция БС подходит для расчета итогов накоплений при периодическихх банковских взносах.

Синтаксис:

БС(ставка; кпер; плт; пс; тип)

Аргументы:

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

Пример.

Вы хотите накопить деньги для специального проекта, который будет осуществлен через год. Предположим, Вы собираетесь сначала вложить 1000 руб. при годовой ставке 14% с ежемесячным начислением сложных процентов, а далее собираетесь вкладывать по 200 руб. в конце каждого месяца в течение года. Сколько денег будет на счете в конце 12 месяцев?

=БС(14%/12; 12; -200; -1000; 0)

получаем ответ: 3709,49 руб.

5. Функция КПЕР вычисляет общее количество периодов выплаты для данного вклада на основе периодических постоянных платежей и постоянной процентной ставки.

Синтаксис:

КПЕР(ставка; плт; пс; бс; тип)

Аргументы:

ставка	Процентная ставка за период
плт	Величина постоянных периодических платежей
пс	Начальное значение, т.е. общая сумма, которую составят будущие платежи
бс	Будущая стоимость, или баланс наличности, который нужно достичь после последней выплаты. Если аргумент бс опущен, он полагается равным 0 (например, будущая стоимость займа равна 0)
тип	Число 0 или 1, обозначающее, когда должна производиться выплата. Если тип равен 0 или опущен, то оплата производится в конце периода, если 1 – в начале периода

Например, если вы берете в долг 1000 руб. при годовой ставке 1% и собираетесь выплачивать по 100 руб. в год, то число выплат вычисляется следующим образом:

=КПЕР(1%; -100; 1000)

В результате получаем ответ: 11 лет.

6. Функция СТАВКА вычисляет процентную ставку за один период, необходимую для получения определенной суммы в течение заданного срока путем постоянных взносов.

Синтаксис:

СТАВКА(кпер; плт; пс; бс; тип; предположение)

Аргументы:

кпер	Общее число периодов выплат
плт	Величина постоянных периодических платежей
пс	Текущее значение, т.е. общая сумма, которую составят будущие платежи
бс	Будущая стоимость, или баланс наличности, который нужно достичь после последней выплаты. Если аргумент бс опущен, он полагается равным 0.
тип	Число 0 или 1, обозначающее, когда должна производиться выплата. Если тип равен 0 или опущен, то оплата производится в конце периода, если 1 – в начале периода

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

=СТАВКА(48; -200; 8000)

В результате получаем: процентная ставка за месяц равна 1%.

7. Функция ПС возвращает текущий объем вклада на основе постоянных периодических платежей. Функция ПС допускает, чтобы денежные взносы производились либо в конце, либо в начале периода, денежные взносы в функции ПЗСдолжны быть постоянными на весь период инвестиции.

Синтаксис:

ПС(норма; кпер; выплата; бс; тип)

Аргументы:

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

Пример.

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

Решение.

Для расчета используется формула: ПС(12%,12,,5000)= -1283,38 тыс.руб.

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

Некоторая дополнительная информация по финансовым функциям содержится в Приложении к данной краткой справке.