Введение

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

Excel учит быть умно-ленивыми! Особенно в том, что касается ввода и использования данных.

Сколько раз наблюдал, как люди вводят уйму данных в Excel, создают объемные связки документов, готовят кипы отчетов руководству, работают с банальной текучкой, но при этом совершенно не беспокоятся об архитектуре создаваемых таблиц Excel!

Проходит немного времени и, как только требуется поменять какие-то значения (изменение курса валют, метраж, условия поставки продукции и т.д.), они открывают прежние документы и начинается поиск: надо все введенные данные в каждых задействованных ячейках Excel поменять, а перед этим их, эти ячейки, еще надо найти! А если хоть одна окажется пропущенной, то весь документ не сходится или, что еще хуже, ошибка может остаться незаметной и проявит себя, например, на совещании у высокого начальства – придется краснеть.

У Вас такое было? У меня, лично, было многократно. Пока не надоело. Это было в самом начале моего знакомства с Excel и данными в ней.

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

Достигается это очень простым методом: надо всего лишь разобраться в методе «вводных данных».

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

Или скажу иначе: мы должны «вынести за скобку» все, что поддается выносу. Так мы делали и делаем в математических задачках, вспомните:

y = 2b + 3bc + 7xz – z/2

y = b(2 + 3c) + z(7x – 0,5)

Практикум

Как это выглядит на примере.

Рассмотрим кусок простой задачи: наша компания – поставщик продукции. Мы продаем изделия по ценам в рублях, зависящих от объема поставок:

При этом руководство нашей компании предпочитает вести внутренний учет в условных единицах, зависящих от курса евро.

Теперь посмотрим, как такой учет можно реализовать на практике. Рассмотрим несколько вариантов с разным уровнем сложности расчетов Excel и автоматизации.

Автоматизация нулевая: изменения требуются в трех столбцах (D, E, G)

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

Очень распространенный вариант решения задачи. Его минусы очевидны – при любом изменении условий (изменение условий отгрузки, коррекция отгруженных количеств, изменение курса евро и т.д.) человек, ведущий такой учет, будет вынужден вновь копаться в каждой формуле, изменяя вручную значения на новые. И шанс, что директор получит в итоге правильный отчет очень низкая – при большом объеме данных составитель обязательно где-нибудь допустит ошибку. Маркер автозаполнения не спасает!

Вообще, чем больше требуется совершить действий, тем выше риск ошибки и наоборот: чем выше автоматизация процесса и всех расчетов данных в Excel, тем выше КПД и тем ниже риск появления ошибки.

Автоматизация низкая: изменения требуются в двух столбцах (D, E)

Что можно сделать для упрощения и повышения автоматизации расчетов в этом документе?
Самое простое, что приходит на ум – «вынести за скобки» курс евро. Ведь, согласитесь, проще поменять курс в одной ячейке, чем в десятках разных ячеек.
Но как это сделать? Для этого нужно вспомнить, что помимо обычных относительных ссылок, в Excel существуют еще и абсолютные ссылки. Теперь задачу можно решить следующим образом:

Теперь уже проще – мы автоматизировали кусок нашей задачки и при необходимости изменения курса евро уже не должны менять его в 10 различных ячейках – достаточно поменять данные в ячейке G3, чтобы все расчеты Excel произвел автоматически. И все же, пока еще автоматизация расчетов в Excel очень низкая – шанс появления ошибки все равно непозволительно высок.

Вариант со средним уровнем автоматизации: изменения требуются в одном столбце (D)

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

И правда, вероятность, что в спешке менеджер не туда взглянет, не так посчитает, не с тем сравнит и из-за этого внесет неправильную цену в документ, высокая. А еще менеджер может быть неопытным. Или, к примеру, он банально может быть не в курсе какой-нибудь детали. Например, что в пограничном варианте цена считается в сторону меньшего количества (или большего).

То есть, теперь встала задача автоматизировать расчет цены в Excel. Она, понятно, меняется от количества. Но как ее автоматически рассчитывать? Мы ведь должны объяснить Excel правила, по которым меняются эти данные!? Готовых решений по автоматизации подобных расчетов в Excel не существует, поэтому нам придется обходиться своими силами. Надеюсь, Вы знаете, что такое мегаформулы, или вложенные формулы. Именно ими мы сейчас и воспользуемся для расчетов.

То, что выделено красным и есть мегаформула. Она введена в столбец расчета Excel’ем цены. По сути, она очень простая – всего лишь перебирает возможные варианты количества и подставляет к ним соответствующую цену.

В таком варианте, наш документ уже становится достаточно умным – ведь теперь нам достаточно изменить, например, цену за 5 000 отгруженных единиц продукции (ячейка Е6), и, автоматически во всем документе будет учтена новая цена. При этом, нам не нужно более лезть в формулы – единожды введенные, они теперь сами все производят все расчеты за нас. Нам же достаточно только управлять значениями.

Наша формула хороша еще и тем, что мы можем управлять не только ценой за отгруженный объем, но и менять эти самые объемы. Например, в таблице выше мы имели условие: за 1 000 ед. цена составляет 350 рублей. От 1 000 до 2 500 ед. цена составляет 340 и т.д. Сейчас мы можем поменять 7 500, например, на 8 500 (ячейка F5), а цену поставить не 285 руб., а 295 (ячейка F6) – и далее во всем документе Excel автоматически произведет пересчет данных: будут пересчитаны цены в зависимости от измененных объемов, как показано на скриншоте ниже.

Обратите внимание, я не меняю более формулы – я управляю только вводными данными в Excel. Поменяв объем на 8500 и цену 295, весь мой документ автоматически изменился. Красными показаны значения, которые отчет самостоятельно пересчитал (по покупателям в евро-столбце).
Можете сравнить с верхним скриншотом.

Автоматизация максимальная: изменения не требуются ни в одном столбце

Однако, даже это еще не все. Остается еще один столбец, в котором требуется ручной труд – столбец D.Человек, работающий с этим модифицированным документом, все равно рано или поздно допустит ошибку. Давайте думать: у нас много поставщиков. 10 – это только пример. Предположим, их 30. Человек должен по списку вносить отгруженные количества по каждому из 30 поставщиков. Уверен, где-нибудь он допустит ошибку. Особенно, если человек сидит на окладе и ему до фени на результат 8).Но как заставить Excel рассчитывать месячный (к примеру) объем отгрузок?
Очень просто. Нужно прежде всего сломать присущую всем нам (людям) прямолинейную логику мышления – она зачастую мешает достигнуть результата.

Будем мыслить нестандартно!
Мы введем свой собственный лист отгрузок на каждого клиента. Тогда, занося ежедневно данные по текущим отгрузкам, мы будем незаметно формировать месячный (декадный, квартальный, годовой) отчет!

Более того, подобный метод ведения учета в чем-то очень интересен, так как позволяет работать с каждым контрагентом в отдельности. Мы можем вести контрагента с разбивкой по неделям, декадам, месяцам, кварталам, полугодиям и даже годам – все данные будут аккуратно учитываться.

Но этот вариант хорош в том случае, если нас интересует полный учет по каждому контрагенту (с вводом дат, объемов, номеров и дат счет-фактур и т.д.). В случае, если Вас интересует просто учет отгружаемого количества, можно обойтись одной общей таблицей Excel под всех покупателей.

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

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

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

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

Теперь, при подобной архитектуре, мы полностью автоматизировали расчет и учет данных в Excel. Более того, мы оптимизировали наше рабочее время, так как нам более не требуется его тратить на составление всяческих отчетов по контрагентам – они составляются автоматически и обновляются автоматически по мере добавления новой информации (новых отгрузок).

То есть, мы все время имеем готовые отчеты с самыми свежими данными!

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

В заключение

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

Не забывайте менять цвет и переименовывать ярлычки листов: давайте им разумные названия – это упростит Вам же работу.При выполнении подобных действий, Вы заметите, насколько Ваши документы стали изящными и легкими в управлении. Расчеты в Excel станут не нагромождением цифр и формул, а очень легким и удобным рабочим инструментом, не сложнее калькулятора 8).

Автор: ChExOff