Прил. 4 к занятию 1. Ключевые функции и формулы

Приложение №4 к занятию 1 курса обучения финансовому моделированию в Excel.

Обзор ключевых функций и формул Excel занятия 1.

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

----ожидайте видео – скоро выложим----

Начнем с разминки – решим задачку об отображении в ячейке названия столбца. Такую формулу можно прописать разными способами, мы же предложим свой, для которого нам понадобятся следующие функции Excel: СТОЛБЕЦ; АДРЕС; ДЛСТР; ЛЕВСИМВ.

Функция СТОЛБЕЦ(), записанная в любой ячейке EXCEL-листа, возвращает значение равное порядковому номеру столбца, в котором находится эта ячейка. В случае если в скобках находится ссылка на другую ячейку, тогда функция СТОЛБЕЦ([ссылка]) со ссылкой в качестве аргумента возвращает значение равное порядковому номеру того столбца, в котором находится ячейка, на которую указывает ссылка в аргументе функции СТОЛБЕЦ.

Функция АДРЕС, возвращающая в качестве значения текст ссылки на ячейку в различных представлениях, имеет в общем случае пять аргументов: номер строки; номер столбца; тип закрепления ячейки или тип ссылки (выбор из четырех комбинаций расстановки «$» перед названием столбца и номером строки); стиль отображения адреса ячейки (R1C1 или A1); имя листа, где находится ячейка. Причем последние три аргумента являются необязательными и по умолчанию в качестве третьего аргумента используется абсолютная ссылка, т.е. когда доллар «$» стоит как перед названием столбца, так и перед номером строки, четвертый аргумент принимает значение «1» по умолчанию, что значит, что ссылка отображается в обычном стиле «A1», и, наконец, 5-ый аргумент в своем значении «по умолчанию» означает, что отображается ссылка на ячейку из текущего листа.

Нас же будет интересовать формула, составленная из функций СТОЛБЕЦ и АДРЕС, имеющая следующий вид:

=АДРЕС(1;СТОЛБЕЦ();4)

Первые два аргумента функции АДРЕС здесь означают то, что мы желаем получить на выходе адрес ячейки, находящейся в первой строке столбца, в котором находится ячейка, содержащая эту формулу, причем в виде относительной ссылки (цифра «4» в качестве третьего аргумента), т.е. без закрепления столбца и строки, чтобы в тексте записи адреса ячейки не фигурировали «лишние» символы – речь идет о значках доллара «$». Проиллюстрируем, как это может выглядеть в Excel.

То есть теперь для того, чтобы получить название столбца, нам необходимо «избавиться» от единички справа у полученного текста с записью ссылки на адрес ячейки из первой строки. Например, это можно сделать, взяв в рассмотрение весь текст ссылки без последнего символа, который как раз-таки и является этой самой единичкой, для чего можно использовать функцию Excel с названием ЛЕВСИМВ, у которой два аргумента, один из которых ссылка на интересующий нас текст, а второй – количество символов слева, которое мы желаем отделить от текста. Следовательно, для применения этой функции нам необходимо рассчитать количество символов, которые необходимо взять слева от текста ссылки, очевидно оно равно длине текста ссылки минус один.

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

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

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

Запишем здесь итоговую формулу отображения названия столбца в EXCEL:

=ЛЕВСИМВ(АДРЕС(1;СТОЛБЕЦ();4);ДЛСТР(АДРЕС(1;СТОЛБЕЦ();4))-1)

Продолжение следует

Следующие уроки: Занятие 2 по финмоделированию в EXCEL. Маркетинг