Excel INDEX Function

The Excel INDEX function returns the value at a given location in a range or array. You can use INDEX to retrieve individual values, or entire rows and columns. The MATCH function is often used together with INDEX to provide row and column numbers.

• array — A range of cells, or an array constant.
• row_num — The row position in the reference or array.
• col_num — [optional] The column position in the reference or array.
• area_num — [optional] The range in reference that should be used.

The INDEX function returns the value at a given location in a range or array. INDEX is a powerful and versatile function. You can use INDEX to retrieve individual values, or entire rows and columns. INDEX is frequently used together with the MATCH function. In this scenario, the MATCH function locates and feeds a position to the INDEX function, and INDEX returns the value at that position.

In the most common usage, INDEX takes three arguments: array, row_num, and col_num. Array is the range or array from which to retrieve values. Row_num is the row number from which to retrieve a value, and col_num is the column number at which to retrieve a value. Col_num is optional and not needed when array is one-dimensional.

In the example shown above, the goal is to get the diameter of the planet Jupiter. Because Jupiter is the fifth planet in the list, and Diameter is the third column, the formula in G7 is:

The formula above is of limited value because the row number and column number have been hard-coded. Typically, the MATCH function would be used inside INDEX to provide these numbers. For a detailed explanation with many examples, see: How to use INDEX and MATCH.

Basic usage

INDEX gets a value at a given location in a range of cells based on numeric position. When the range is one-dimensional, you only need to supply a row number. When the range is two-dimensional, you’ll need to supply both the row and column number. For example, to get the third item from the one-dimensional range A1:A5:

The formulas below show how INDEX can be used to get a value from a two-dimensional range:

INDEX and MATCH

In the examples above, the position is «hardcoded». Typically, the MATCH function is used to find positions for INDEX. For example, in the screen below, the MATCH function is used to locate «Mars» (G6) in row 3 and feed that position to INDEX. The formula in G7 is:

MATCH provides the row number (4) to INDEX. The column number is still hardcoded as 3.

INDEX and MATCH with horizontal table

In the screen below, the table above has been transposed horizontally. The MATCH function returns the column number (4) and the row number is hardcoded as 2. The formula in C10 is:

For a detailed explanation with many examples, see: How to use INDEX and MATCH

Entire row / column

INDEX can be used to return entire columns or rows like this:

where n represents the number of the column or row to return. This example shows a practical application of this idea.

Reference as result

It’s important to note that the INDEX function returns a reference as a result. For example, in the following formula, INDEX returns A2:

In a typical formula, you’ll see the value in cell A2 as the result, so it’s not obvious that INDEX is returning a reference. However, this is a useful feature in formulas like this one, which uses INDEX to create a dynamic named range. You can use the CELL function to report the reference returned by INDEX.

Two forms

The INDEX function has two forms: array and reference. Both forms have the same behavior – INDEX returns a reference in an array based on a given row and column location. The difference is that the reference form of INDEX allows more than one array, along with an optional argument to select which array should be used. Most formulas use the array form of INDEX, but both forms are discussed below.

Array form

In the array form of INDEX, the first parameter is an array, which is supplied as a range of cells or an array constant. The syntax for the array form of INDEX is:

• If both row_num and col_num are supplied, INDEX returns the value in the cell at the intersection of row_num and col_num.
• If row_num is set to zero, INDEX returns an array of values for an entire column. To use these array values, you can enter the INDEX function as an array formula in horizontal range, or feed the array into another function.
• If col_num is set to zero, INDEX returns an array of values for an entire row. To use these array values, you can enter the INDEX function as an array formula in vertical range, or feed the array into another function.

Reference form

In the reference form of INDEX, the first parameter is a reference to one or more ranges, and a fourth optional argument, area_num, is provided to select the appropriate range. The syntax for the reference form of INDEX is:

Just like the array form of INDEX, the reference form of INDEX returns the reference of the cell at the intersection row_num and col_num. The difference is that the reference argument contains more than one range, and area_num selects which range should be used. The area_num is argument is supplied as a number that acts like a numeric index. The first array inside reference is 1, the second array is 2, and so on.

For example, in the formula below, area_num is supplied as 2, which refers to the range A7:C10:

In the above formula, INDEX will return the value at row 1 and column 3 of A7:C10.

Функция ИНДЕКС в Excel: короткое описание, применение и примеры

Сейчас программка Excel по собственной популярности уступает лишь Word. Она дозволяет с легкостью производить самые различные экономико-статистические расчеты над огромным количеством данных. Для данной нам цели в ней предвидено огромное количество интегрированных функций, в том числе вспомогательных. Некие из их способны производить деяния, в том числе над массивами данных. К ним относится и функция «ИНДЕКС». В Excel она употребляется как раздельно, так и с «ПОИСКПОЗ», о которой будет поведано ниже.

Описание

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

Ее синтаксис несложен и смотрится последующим образом: ИНДЕКС (массив, № строчки, № столбца).

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

Функция «ИНДЕКС» в Excel время от времени выдает значение «#ССЫЛ!». Почаще всего это происходит, если ячейка, расположенная на пересечении обозначенных строчки и столбца, находится вне обозначенного спектра.

Примеры внедрения

Разглядим несколько случаев использования функции «ИНДЕКС» на практике.

Представим, имеется массив, состоящий из 4 столбцов и 4 строк (см. таблицу). Если ввести в одну из ячеек таблицы расположенное вне спектра А1:Е5 выражение «=ИНДЕКС (В2:Е5, 2, 3)» (без кавычек) и надавить на «Ввод», то в ответ будет выдано значение «бегония».

Если требуется выяснить, сколько учащихся Группы 2 получили оценку «неудовлетворительно», то в подобающую ячейку следует ввести выражение: ИНДЕКС (С2:С5, 1).

Функция «ПОИСКПОЗ» в Excel

Оба примера, приведенные выше, не будут работать с большенными массивами данных. Дело в том, что внедрение функции «ИНДЕКС» в Excel подразумевает ввод номера строчки и столбца не самой таблицы, а массива данных. Это довольно проблемно создать, когда идет речь о большенном числе частей. Решить делему может посодействовать еще одна экселевская функция.

Разглядим вариант, когда массив состоит из единственной строчки.

Спектр значений в этом случае В3:В6.

Избираем ячейку в иной строке, к примеру D1. Вводим в нее заглавие фрукта, позицию которого желаем отыскать, в данном случае «апельсины». В ячейке (Е1), куда желаем записать номер соответственной строчки, вводим «= ПОИСКПОЗ(D1;В3:В6;0)» (см. таблицу). В итоге там возникает число 3. Конкретно таковой номер в спектре В3:В6 у выражения «апельсины».

Крайний 0 значит, что требуется отыскать четкое совпадение со значением D1.

Как отыскать все текстовые значения, удовлетворяющие некоторому аспекту

В виде, представленном выше, функция «ПОИСКПОЗ» возвращает лишь одно значение (самое 1-ое, т. е. верхнее). Но что созодать, если в перечне есть повторения. В таком случае помогают формулы массива. Для их использования следует выделить весь спектр данных и применять сочетание кнопок «Ctrl+Shift+Enter». Но ее рассмотрение не является предметом данной статьи.

Функция «ИНДЕКС» и «ПОИСКПОЗ» в Excel: примеры

Представьте, что для вас необходимо избрать из довольно огромного массива лишь определенные данные. Разглядим для простоты вариант с маленьким числом частей. К примеру, у вас есть отчет о успеваемости нескольких групп студентов и их оценки. Представим, вы желаете, чтоб в ячейке H2 возникло число студентов, получивших оценку «неуд».

Для этого идеальнее всего вместе применять обе функции. Чтоб выяснить, что нужно ввести в H2, поначалу разглядим самое обычное выражение, которое можно применять для данной нам цели. А именно, разыскиваемое значение можно получить, если записать в эту ячейку «=ИНДЕКС(А2:Е5;1;2)». Тут мы употребляли вариант из прошлых примеров, когда номер строчки и столбца высчитывался вручную. Но наша цель — заавтоматизировать этот процесс. Для этого следует заместо двойки и единицы, которые указывают на разыскиваемые строчку и столбец, в массиве записать надлежащие функции «ПОИСКПОЗ», выдающие эти номера. Направьте внимание, что мы отыскиваем выражение «уд», расположенное в ячейке G2 и «гр. 2» из H2. Не считая того, нам необходимы четкие совпадения, потому в качестве крайнего, третьего, аргумента в обоих вариантах указывается 0.

Тогда заместо 1 в формуле ИНДЕКС(А2:Е5;1;2) следует записать: ПОИСКПОЗ(G2;A2:A5;0), а заместо 2 — ПОИСКПОЗ(H2; А2:Е2;0).

Опосля подстановки имеем: ИНДЕКС(А2:E5; ПОИСКПОЗ(G2;A2:A5;0); ПОИСКПОЗ(H2; А2:Е2;0)). В итоге, нажав «Ввод», имеем в данной нам ячейке значение «10».

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

Как понятно, функция «ИНДЕКС» в Excel быть может «вытянута» на некоторый спектр. В примере, рассматриваемом выше, это все 4 ячейки из H2:J3. В связи с сиим нужно узнать, как создать так, чтоб, «вытянув» эту формулы на право и вниз, получить правильные значения.

Основная сложность заключается в том, что массив А2:Е5 имеет относительный адресок. Чтоб поправить это, следует перевоплотить его в абсолютный. Для этого массив записывается в виде $А$2:$Е$5. То же следует создать и для обеих интегрированных функций, т. е. они должны смотреться как ПОИСКПОЗ($G$2;$A$2:$A$5;0) и ПОИСКПОЗ($H$2; А$2:$Е2;0).

Окончательный вид формулы будет: ИНДЕКС($А$2:$Е$5; ПОИСКПОЗ($G$2;$А$2:А$5;0); ПОИСКПОЗ($H$2; $А$2:$Е$2;0)).

Индекс доходности (рентабельности) инвестиций — PI. Формула. Пример расчета в Excel

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

Индекс доходности (англ. PI, DPI, Present value index, Profitability Index, benefit cost ratio) – показатель эффективности инвестиции, представляющий из себя отношение дисконтированных доходов к размеру вкладывательного капитала. Остальные синонимы индекса доходности, которые несут аналогичный экономический смысл: индекс прибыльности и индекс рентабельности.

Инфографика: Индекс доходности (рентабельности) инвестиций

Оценка цены бизнеса	Денежный анализ по МСФО	Денежный анализ по РСБУ
Расчет NPV, IRR в Excel	Оценка акций и облигаций

Индекс доходности инвестиции. Формула расчета

PI (Profitability Index) – индекс доходности вкладывательного проекта;

NPV (Net Present Value) – незапятнанный дисконтированный доход;

n – срок реализации (в годах, месяцах);

r – ставка дисконтирования (%);

CF (Cash Flow) – валютный поток;

IC (Invest Capital) – начальный затраченный вкладывательный капитал.

★ Программка InvestRatio — расчет всех вкладывательных коэффициентов в Excel за 5 минут (расчет коэффициентов Шарпа, Сортино, Трейнора, Калмара, Модильянки бета, VaR) + прогнозирование движения курса

Дисконтированный индекс доходности инвестиций. Формула расчета

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

где:

DPI (Discounted Profitability Index) –дисконтированный индекс доходности; NPV – незапятнанный дисконтированный доход; n – срок реализации (в годах, месяцах); r – ставка дисконтирования (%) инвестиции; IC – начальный затраченный вкладывательный капитал.

Трудности оценки индекса доходности на практике

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

На устойчивость будущих валютных потоков оказывают воздействие огромное количество макро-, микроэкономических причин: сезонность спроса и предложения, процентные ставки ЦБ РФ (Российская Федерация — государство в Восточной Европе и Северной Азии, наша Родина), стоимость сырья и материалов, размер продаж и т.д. В истинное время на размер будущих валютных потоков ключевое значение оказывает уровень продаж, на который влияет рекламная стратегия конторы.

Существует огромное количество разных подходов оценки ставки дисконтирования. Сама по для себя ставка дисконтирования отражает временную стоимость средств и дозволяет привести будущие валютные платежи к истинному времени. Так если проект финансируется лишь на базе собственных средств, то за ставку дисконтирования принимают доходности по другим инвестициям, которая быть может рассчитана как доходность по банковскому вкладу, доходность ценных бумаг (CAPM), доходность от вложения в недвижимость и т.д. При финансировании проекта за счет собственных и заемных средств употребляют способ WACC. Наиболее тщательно способы оценки ставки дисконтирования рассмотрены в статье «Ставка дисконтирования. 10 современных способов расчета».

Что указывает индекс доходности?

Показатель индекс доходности указывает эффективность использования капитала в вкладывательном проекте либо бизнес плане. Оценка подобна как для индекса доходности (PI) так и для дисконтированного индекса доходности (DPI). В таблице ниже приводится оценка вкладывательного проекта в зависимости от значения показателя DPI.

Значение показателя	Оценка вкладывательного проекта
DPI<1	Вкладывательный проект исключается из предстоящего рассмотрения
DPI=1	Доходы вкладывательного проекта равны затратам, проект не приносит ни прибыли ни убытков. Нужна его модификация
DPI>1	Вкладывательный проект принимается для предстоящего вкладывательного анализа
DPI1>DPI2	Уровень эффективности управления капиталом в первом проекте выше, нежели во 2-м. 1-ый проект имеет огромную вкладывательную привлекательность

Мастер-класс: «Как высчитать индекс доходности для бизнес плана»

Оценка индекса доходности инвестиции в Excel

Разглядим пример оценки индекса доходности при помощи программки Excel. Для этого нужно высчитать две составляющие показателя: незапятнанный дисконтированных доход и незапятнанные дисконтированные издержки (если они присутствовали в течение срока реализации проекта). Разглядим два варианта расчета в Excel индекса доходности.

1-ый вариант расчета индекса доходности последующий:

1. Валютный потокCF (CashFlow) =C8-D8
2. Дисконтированный валютный поток =E8/(1+$C$4)^A8
3. Незапятнанный дисконтированный валютный поток (NPV) =СУММ(F8:F16)-B7
4. Индекс доходности (PI)=F17/B7

На рисунке ниже показан итоговый итог расчета PI в Excel.

Расчет в Excel индекса доходности (PI) инвестиции

2-ой вариант расчета индекса доходности вкладывательного проекта заключается в использовании интегрированной денежной формулы в Excel — ЧПС (незапятнанная приведенная стоимость) для расчета незапятнанного дисконтированного дохода (NPV). В итоге формулы расчета будут иметь последующий вид:

1. Дисконтированный валютный поток (NPV) =ЧПС(C4;E7:E16)-B7
2. Индекс прибыльности (PI) =E17/B7

2-ой вариант расчета индекса доходности (PI) в Excel

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

★ Программка InvestRatio — расчет всех вкладывательных коэффициентов в Excel за 5 минут (расчет коэффициентов Шарпа, Сортино, Трейнора, Калмара, Модильянки бета, VaR) + прогнозирование движения курса

Как произвести экспресс-оценку хоть какого бизнес плана?

Все бизнес-планы включают в себя денежный план, который оценивают при помощи вкладывательных характеристик эффективность вложения для инвестора. Денежный план и его характеристики являются самыми важными для принятия решения о финансировании проекта. Чтоб стремительно оценить хоть какой бизнес-проект на уровень вкладывательной привлекательности следует разглядеть четыре показателя: незапятнанный дисконтированный доход, внутренняя норма прибыли, индекс доходности и дисконтированный период окупаемости. Если производятся условия по данным показателям, то вкладывательный проект быть может уже наиболее детально проанализирован на нрав и природу получения валютных потоков, систему менеджмента, маркетинга и продаж.

Характеристики экспресс оценки

Значения характеристик

Индекс доходности заходит в четыре главных показателя, которые оценивает хоть какой инвестор при вложении в проект. Кроме данных характеристик есть остальные коэффициенты оценки эффективности инвестиций, которые наиболее тщательно рассмотрены в статье: «6 способов оценки эффективности инвестиций в Excel. Пример расчета NPV, PP, DPP, IRR, ARR, PI» .

Достоинства и недочеты индекса доходности вкладывательного проекта

Достоинства индекса доходности последующие:

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

К недочетам индекса доходности можно отнести:

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

Резюме

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