Советы старпера юношам, обдумывающим житье.

akteon — 14.02.2010 Я по работе провожу много времени с экселевскими таблицами. То сам строю, то говорю, что нужно построить, то вожусь с тем, что кто-то построил для меня, то слушаю какие-то доклады или читаю записки, сделанные с большим количеством экселя. Потом общаюсь с людьми и говорю, что нужно переделать вот это и то. Или посмотреть вот в таком еще разрезе. Или говорю, что вот здесь у вас, братцы, косяк и нелепица. Сам, честно скажем, я уже мало моделей строю. Почему-то как только человек научится как следует модели строить, ну или не как следует, посредственно, так его сразу чем-нибудь иным занимают. Но позвольте все-таки кой-какие мысли на правах ветерана высказать. Возможно, я расскажу вам, что лошади кушают овес и сено, а Волга впадает в Каспийское море, ну да не вызщите.

Ну-с, начнем. Эксель - это, на самом деле, жутко многообразная штука и его можно очень по-разному использовать. В нем можно верстать таблички, хранить циферки и т.д. Но по большей части Эксель, вы не поверите, используют как среду программирования. Либо информационных систем, либо моделей. Это далеко не все сознают, но на самом деле, это так. В 80% случаев работа с Экселем - это программирование и есть. Так что, самая распространенная среда програмирования нынче - это не Java там какая-нибудь и не C++, а вот именно Эксель.

Про то, как надо писать программы, написаны многие тома. Но почему-то даже люди, поучившиеся на разнообразных прикладных математиках, напрочь забывают эту науку, когда садятся за Эксель, чего уж говорить о финансистах. Между тем, тома эти написаны после наступания на многочисленные грабли. Вы думаете, только с появлением Экселя возникли модели, которые ведут себя как сами того захотят, которые невозможно изменить, в которых невозможно разобраться даже автору? С этим столькнулись еще в 40-е - 50-е годы и выработали а) средства программирования, позволяющие этого избежать и б) подходы к построению программ и моделей. Беда в том, что Эксель - обманчиво легок и позволяет очень быстро склепать неаккуратное нечто, что по первости будет давать адекватный результат. В Экселе можно строить модели правильно и аккуратно, это требует чуть больше времени на начальном этапе, но сэкономит уйму времени потом. Вот вам немного советов, как строить модели в Экселе, если они занимают больше одног экрана и если вы собираетесь возвращаться к ним через месяц. Системы в них никакой нет, воспринимайте это как страничку "советы домашнему мастеру" из старой "Науки и жизни". Просто наболело за последнее время. Вы не представляете, сколько времени и краски на щеках было бы сэкономлено моими контрагентами, если бы они следовали хотя бы части этих советов.

1. Разделяйте данные и вычисления. Это еще в 40-е годы придумали. Храните отдельно и едиными массивами вводные данные, в другом едином массиве строк делайте вычисления, обращаясь к данным по ссылкам, в третьем месте еще ниже, собирайте строки результатов, которые обращаются в строки блока счета
Красьте строки, в которых происходят разные вещи, в разные цвета. Исторические данные, которые меняться не должны - в один, прогнозы - в другой, ручной изменяющийся ввод от пользователя - в третий, счетные строки в четвертый, строки вывода в пятый.

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

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

2. Минимизируйте использование адресов ячеек в формулах. В Экселе можно назвать любую ячейку и потом обращаться к ней не $GF$852, а Gadget_C_Price - такие формулы потом куда лучше переживают пертурбации с вставкой-добавлением строк, перетаскиванием кусков модели туда-сюда и куда легче читаются и проверяются.

3. Старайтесь в единых кусках листа делать единообразные формулы. В идеале лучше вообще пользоваться формулами для массивов (array formulas - не знаю, как по-русски)

4. Выучите, как работают функции VLOOKUP, INDEX, INDIRECT, ADDRESS и пользуйтесь ими. Они позволяют сделать модели сильно компактнее. Пользуйтесь вычислимыми адресами. Например, если вы делаете сводную таблицу, которая вытаскивает на единый лист несколько параметров из нескольких листов, а параметров этих у вас десятка полтора, а листов штук 30, то вы умаетесь ручками указывать что откуда взять. А потм вы будет долго материться, если начальник скажет, что лучше в эту сводную таблицу поставить другой параметр. Нет. Два других параметра.
Куда проще слева от такой исходной таблицы написать список названия листов, поверху - адреса ячеек, откуда следует брать искомое, а в теле таблицы - простую и единообразную формулу, основанную на функции Indirect

5. Многочисленные IF - это, конечно, хорошо. SUMIF - тоже, кстати, очень неплохо, рекомендую. Но иногда бывает полезнее завести несколько строк, в которых ставить "флажки", которые выставляются в 0 или 1 по одному простому условию, а потом в нужных местах вместо использования IF умножать на те самые строки с флажками.

6. Заведите в своих моделях "приборные щитки" - первые страницы многостраничной модели, на которые выведены поля для десятка-другого главных вводных параметров и десятка-другого главных выодных. Все это должно умещаться на один экран, можно ниже поставить несколько ключевых графиков, на еще один экран. По хорошему, после того, как модель построена и вы начинаете активно с ней работать, изучая, что, собственно, происходит, вы должны 99% времени проводить только на этом экране. И кстати, при наличии такого экрана вам будет куда легче показывать модель начальству.

7. Пользуйтесь выпадающими списками, Data Validation, если одно и то же число нужно куда-то вводить в модели дважды, делайте однократный ввод, а из другого места забирайте данные по ссылке - всячески ограничьте возможность ошибки при вводе, задания несовместных параметров моделирования.

8. Допустим, у вас есть много-много объектов, которые нужно обсчитать похожим образом. Обычно в таком случае делают модель для одного, потом клонируют, кидают в расклонированные закладки или файлы данные для всех, а потом выясняется, что где-то была ошибка или надо добавить новую функциональность, так что, надо вводить исправления в сотню листов. По возможности держите один счетный движок и населяйте его данными по необходимости. Например, заведите много-много листов только с данными, а в движке поставьте одно-единственное поле, в которм указывайте, с какой закладки брать данные. Если вам очень надо чтобы все это считалось одновременно, заранее подумайте о возможности раскладывать данные в клоны движка одним-другим копипейстом или макросом.

9. Стройте модель так, чтобы она была расширяемой. Закладывайте резерв на дополнительные периоды времени, на дополнительные предметы счета и т.д. Свободные строки и столбцы можно легко спрятать до поры до времени.

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

О, вот и до десятки добрался. На этом, наверное, собранье пестрых глав на сегодня закончу.

Оставить комментарий

Популярные посты:

• Ранее
• Архив