Пособие по Excel-2007: Excel для трейдера.

Posted on 3 October, 2017 by

Excel-2007 предоставляет возможность вычислять функции с условиями путем оператора «если». В частности, есть статистические функции, такие как счётесли, срзначесли, суммесли. А вот функций минесли или максесли, которые бы вычисляли локальные минимумы и максимумы, увы, не предусмотрено. Что же делать, если нужно провести в программе анализ биржевых котировок и вычислить локальные максимумы и минимумы? Пособия по Excel-2007 об этом обычно молчат. Пути для того, чтобы вычислить минимумы и максимумы с условиями самостоятельно есть, об одном из них вы можете узнать из этой статьи.

Типичная ситуация. У нас есть данные, полученные в Excel путем импорта из текста, чаще всего csv или конвертированные из файла MetaStock. Обычно такие данные состоят из пяти столбцов в порядке слева направо: Date, Open, High, Low, Close. Если данные в формате интрадэй, обычно между полями Date и Open содержится поле Time, нередко для удобства работы (в чем заключается это удобство, читайте в следующих статьях рубрики) крайний левый столбец заполняется названием или тиккером валютной пары или другого инструмента. Чтобы определить локальные экстремумы, мы будем использовать три столбца: Date (в случае с интрадэй – Time, но в данном случае для удобства мы рассматриваем месячные данные), High, Low. По полю High мы определяем максимум, по полю Low, соответственно, минимум, поле Date служит условием.
Минимум и максимум определяются с помощью стандартных функций Excel-2007 МИН и МАКС. Их формат такой: МИН(число1;число2; ...), МАКС(число1;число2; ...). То есть, в скобках в конечном итоге должен оказаться диапазон, на котором мы рассчитываем экстремумы. Этот диапазон проще всего рассчитать при помощи функции СМЕЩ. Как сообщают разработчики программы, «данная функция возвращает ссылку на диапазон, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов. Возвращаемая ссылка может быть отдельной ячейкой или диапазоном ячеек». Нас интересует именно диапазон ячеек. Синтаксис следующий: СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;высота;ширина). В качестве ссылки мы берем, в зависимости от того, что будем рассчитывать, ячейку в столбце High или Low, соответствующую дате начала рассматриваемого периода. В качестве значений «смещ_по_строкам» и «смещ_по_столбцам» ставим 0, ширина столбца равняется 1. Высота рассчитываемого диапазона – это единственный показатель, который нам надо вычислить самим, автоматом его программа пока не может вычислить. Для этого нам надо знать номера строк, что в программе рассчитывается очень простой функцией: СТРОКА(). Добавляем столбец «Строка» справа от последнего столбца, во все ячейки столбца вставляем указанную формулу. В качестве примера рассчитаем годичные экстремумы. Добавляем столбец «Год», в его строки вводим формулу: =год(текущая ячейка столбца «Дата»). В первой ячейке, если все правильно, у нас должна быть формула =ГОД(B2). Заполняем столбец. Следующий столбец заполняем такой формулой (от первой ячейки после заголовка столбца): =ЕСЛИ(НЕ(H2=H1);G2;""). С помощью этой формулы будут выведены строки начала каждого года, в остальных же случаях ячейки в столбце будут оставлены пустыми. В следующей строке вычисляем локальные минимумы по такой формуле: =ЕСЛИ(НЕ(I2="");МИН(СМЕЩ(E2;0;0;СРЗНАЧЕСЛИ(H:H;H2+1;I:I)-I2;1));""). Среднее значение с условием нужно для того, чтобы вызвать строку начала следующего года. Соответственно, для максимума формула будет такая: =ЕСЛИ(НЕ(I2="");МАКС(СМЕЩ(E2;0;0;СРЗНАЧЕСЛИ(H:H;H2+1;I:I)-I2;1));""). Заполняем формулами столбцы и получаем следующий результат:

смотреть здесь