«Фишки» Excel для бизнеса

Ученые уже давно доказали: физиологически мозг человека используется полностью. Это, конечно, не означает, что всеми возможностями мы пользуемся максимально продуктивно, а поэтому учиться, в том числе использовать ресурсы мозга, никогда не бывает лишним. Но вот надеяться на существование какой-то методики, которая позволит, особо не напрягаясь, подключить остальные 95% и стать в 20 раз умнее, можно уже перестать.

То же касается и Excel. Да, если изучить Visual Basic, написать кучу сложных макросов, то его возможности становятся по-настоящему безграничны. Но это можно сказать и о любой другой программе: если научиться программировать и дописать ее, то легко можно увеличить возможности и в 10, и в 20, и даже в 100 раз. Между тем все основные возможности Excel связаны с его базовыми функциями, знакомыми любому, даже «непродвинутому» пользователю.

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

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

Именованные ячейки и функция ВПР

Одним из недостатков Microsoft Excel при его использовании для автоматизации управления бизнесом является достаточно сложное разграничение доступа к данным. При желании в рамках одного и того же файла можно настроить права пользователя на просмотр и редактирование отдельных листов и даже отдельных ячеек, но для этого нужно отправиться в путешествие в «дебри» программирования, в которые я обещал вас не заводить.

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

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

Из табл. 1 данные переносятся в отчет о прибылях и убытках компании, фрагмент формы которого приведен в табл. 2.

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

• ячейка B2 табл. 2 (итоговая выручка от про­дажи автомобилей) равна ячейке B10 табл. 1;
• ячейка B3 табл. 2 (выручка по модели 1) равна ячейке B4 табл. 1 и т.д.

Но не все так просто. Представим, что ком­пания продала еще один автомобиль, и табл. 1 приняла иной вид (см. табл. 1.1).

Обратите внимание на то, что добавление строки привело к изменению индексов ячеек. Если раньше ячейка с итоговой выручкой была под индексом B10, то теперь он равен B11. Итоговые продажи по модели 2 раньше находились в ячейке B7, а теперь переместились в B8 и т.д.

А теперь я открою маленький секрет (и, возможно, сэкономлю кучу времени пользователям Excel, которые иногда не могут понять, по какой же причине их данные «поплыли»). При изменении индекса ячеек в табл. 1 формулы в табл. 2, имеющей ссылки на нее, изменятся только в том случае, если в момент изменения оба файла будут открыты. То есть если в момент, когда мы в табл. 1 добавили строку «Автомобиль 6», файл с табл. 2 у этого же пользователя был открыт, то формула в ячейке B2 поменяет ссылку с ячейки B10 табл. 1 на B11 и т.д.

Если же файл с табл. 2 не был открыт (а, скорее всего, так и будет, ведь руководитель отдела продаж, редактирующий табл. 1, даже не имеет доступа к сводному отчету о прибылях и убытках), то табл. 2 продолжит ссылаться на ячейку B10 табл. 1. И условный финансовый директор, открыв отчет, в строке выручки увидит не общий доход от продажи автомобилей, а всего лишь выручку от продаж модели 3.

Как же избавиться от этого недостатка?

Одним из вариантов является присвоение ячейкам текстовых имен вместо привычных нам буквенно-числовых индексов. Так, ячейке B10 табл. 1 мы можем присвоить имя, например, «Итого_выручка » (закладка «Формулы» на панели инструментов в Excel, «Присвоить имя»), а ячейке B9 — имя «Итого_выручка_модель3».

Что это нам даст?

Во-первых, при добавлении строк или столбцов в таблицу индекс ячейки будет меняться (как в нашем примере, когда ячейка B10 табл. 1 превратилась в табл. 1.1 в B11). Имя же собственное ячейки всегда будет оставаться неизменным, сколько бы строк или столбцов мы бы перед ней ни добавляли. То есть ячейка, содержащая общую сумму выручки от продажи автомобилей, всегда будет носить имя «Итого_выручка», что бы мы с таблицей ни делали.

Во-вторых, имена ячеек мы тоже можем использовать в формулах. То есть вместо условной формулы =B10 мы с таким же успехом можем использовать формулу =Выручка_итого.

И если ячейка B2 нашей таблицы будет ссылаться не на ячейку B10 табл. 1, а на ячейку с именем «Выручка_итого» данной таблицы, то мы всегда можем быть уверены, что, открыв отчет о прибылях и убытках, увидим там общую сумму выручки, а не какую-то загадочную величину.

Но данный способ хорош только в том случае, если количество ячеек, которым необходимо присвоить имя, невелико. В нашем случае в табл. 1 имя нужно будет дать только четырем ячейкам: суммам выручки по каждой модели и общему объему продаж. А если таких ячеек сотни? Или тысячи? На практике мне приходилось встречать и такое. Тогда простое присвоение имен может превратиться в очень нудную и трудоемкую работу.

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

Например, если в ячейке B2 табл. 2 мы укажем формулу вида:

=ВПР("ИТОГО продажи; A1:B10; 2),

где A1:B10 — массив заполненных данных в табл. 1

то данная формула найдет в табл. 1 строку, где в столбце A (первый столбец указанного нами диапазона) написано «ИТОГО продажи» (указанный нами параметр поиска3) и выведет значение, приведенное в столбце 2 (указанный нами последним порядковый номер столбца). Другими словами, необходимую нам выручку. И опять-таки, что бы мы ни делали с нашей табл. 1, формула ВПР будет искать не строку под каким-то определенным номером, а строку, где содержится текст «ИТОГО Выручка», что нам и нужно.

Суммирование по условиям

Теперь предположим, что у нас есть таблица (см. табл. 3), куда определенный сотрудник записывает движение денежных средств (по счету или кассе): от кого и за что получено, зачем и сколько потрачено.

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

Как это сделать на практике? Проще всего это осуществить, прибегнув к так называемому условному суммированию, то есть когда в определенную ячейку переносятся данные, отвечающие заданным условиям.

Посмотрим, например, какая цифра должна быть перенесена из табл. 3 в табл. 4 в ячейку, к примеру, B5 («Хозяйственные расходы за январь»). Очевидно, что это сумма всех расходов из табл. 3, по которым в столбце B указано, что они являются хозяйственными расходами, а указанные в столбце А даты относятся к январю.

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

=СУММЕСЛИМН ($D$2:$D$7;$B$2:$B$7;"Хозяйственные расходы";$A$2:$A$7;"<01.02.2015"; $A$2:$A$7;">31.12.2014"),

где $D$2:$D$7 — ссылка на диапазон ячеек табл. 3, содержащей суммируемые суммы (столбец «Расход»);

$B$2:$B$7, $A$2:$A$7 — ссылки на диапазоны ячеек табл. 3, содержащие значения, по которым будет производиться фильтрация (в нашем случае — столбцы «Статья движения денежных средств» и «Дата»);

"Хозяйственные расходы", "<01.02.2015", "> 31.12.2014" — ссылка на значения фильтра, при которых данные будут приниматься в расчет.

В нашем примере получится, что расходы денежных средств попадут в ячейку B6 отчета о движении денежных средств при соблюдении следующих условий:

• если в столбце B табл. 3 указана статья «Хозяйственные расходы»;
• если в столбце А табл. 3 дата больше 31 декабря 2014 г и эта же дата меньше 1 фев­раля 2015 г

Соответственно, если мы хотим получить выплаты по статье «Хозяйственные расходы» за февраль 2015 г, то в формуле необходимо будет ограничить суммируемые значения по столбцу А 1 марта 2015 г сверху и 31 января 2015 г — снизу.

ЕСЛИ, НАПРИМЕР,В ФИЛЬТРЕ УКАЗАНО «ХОЗЯЙ­СТВЕННЫЕ РАСХОДЫ», ТО И В ТАБЛИЦЕ ОБЯЗА­ТЕЛЬНО ДОЛЖНЫ БЫТЬ НЕ «РАСХОДЫ ХОЗЯЙ­СТВЕННЫЕ», НЕ «ХОЗРАСХОДЫ» И Т. Д., А ИМЕННО «ХОЗЯЙСТВЕННЫЕ РАСХОДЫ».

Если мы хотим суммировать расход денег по статье «Маркетинговые расходы», то соответствующим образом мы меняем применяемый фильтр к столбцу B. Если же нам необходимо суммировать не расход, а приход денежных средств, то в качестве диапазона указываем не столбец D, а столбец C.

В результате, прописав соответствующие формулы в отчете о движении денежных средств, мы получим его заполненный вариант (см. табл. 5)

Таким образом, использование функции СУММЕСЛИМН позволяет включать в расчет только те значения таблиц, которые соответ­ствуют одному либо нескольким заданным условиям.

Условные списки

Описанный ранее инструмент условного суммирования можно эффективно использовать на практике только в том случае, если статья движения денежных средств, к примеру, будет указана в таблице именно таким образом, как установлен фильтр на ее суммирование. Если, например, в фильтре указано «Хозяйственные расходы», то и в таблице обязательно должны быть не «Расходы хозяйственные», не «Хозрасходы» и т.д., а именно «Хозяйственные расходы».

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

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

Большинству пользователей Excel, наверное, знакома данная возможность программы. Мы же рассмотрим более сложный вариант — так называемые зависимые списки, то есть когда выпадающие варианты зависят от того, что именно пользователь указал в той либо иной ячейке.

Немного усложним наш пример (табл. 3), предположив, что пользователь указывает не только статью движения денежных средств, но и центр финансовой отчетности (далее — ЦФО).

При этом, например:

• все хозяйственные расходы относятся первоначально на «техническое» ЦФО «Общие расходы» (ОР), а далее уже распределяются между «реальными» ЦФО по выбранной базе распределения;

ИСПОЛЬЗОВАНИЕ ФУНКЦИИ СУММЕСЛИМН ПОЗВОЛЯЕТ ВКЛЮЧАТЬ В РАСЧЕТ ТОЛЬКО ТЕ ЗНАЧЕНИЯ ТАБЛИЦ,КОТОРЫЕ СООТВЕТСТВУЮТ ОДНОМУ ЛИБО НЕСКОЛЬКИМ ЗАДАННЫМ УСЛОВИЯМ.

• получаемая же от покупателей выручка, а также оплата приобретаемых рекламных материалов относится либо на ЦФО «Отдел про даж автомобилей» (ОПА), либо на ЦФО «Отдел продаж запасных частей» (ОПЗЧ) в зависимости от того, за что именно получены деньги и что именно мы рекламируем.

И перед нами стоит задача, чтобы:

• в столбце B пользователь не набирал статью движения денежных средств вручную, а выбирал из предложенного списка;
• то же самое и в столбце C, но при этом предлагаемые значения должны зависеть от содержания столбца B: если выбрана статья «Хозяйственные расходы», то предлагается только ЦФО «ОР», если же выбраны статьи «Поступления от покупателей» либо же «Расходы на маркетинг», то к выбору должны предлагаться значения ЦФО ОПА либо ОПЗЧ.

С первой частью нашей задачи все просто. На отдельном листе нужно перечислить все статьи движения денежных средств, выделить столбец B табл. 6 и через инструмент «Данные/ Проверка данных» в «Типе данных» выбрать список, указав в качестве источника диапазон наших перечисленных статей.

Но что делать с ЦФО? Самый простой вариант — тоже перечислить все имеющиеся ЦФО и в столбце C табл. 6 указать их в качестве возможных значений.

Однако в этом случае есть вероятность, что сотрудник может указать неверный ЦФО. Например, при поступлении денежных средств от покупателей укажет «Общие расходы». Либо же отнесет на «Отдел продаж автомобилей» платежи за хозяйственные товары.

К чему это может привести? Как мы знаем, данные из нашего файла дальше идут в отчет о движении денежных средств (при этом, раз мы используем ЦФО, то и отчет, наверняка, у нас сегментирован по ЦФО). И если в от­чете не окажется нужной строки (например, в отчете по общим расходам может не быть статьи «Поступление от покупателя», посколь­ку подобное поступление не планируется), то соответствующая выплата может просто потеряться.

Поэтому мы сделаем следующее:

• рядом с каждой указанной статьей дви­жения денежных средств укажем ЦФО, по которым она может отражаться;
• каждому из получившихся «наборов» ЦФО присвоим имя, равное имени статьи движения денежных средств (делается это также через «Присвоить имя», но только мы выбираем не одну ячейку, а их диапазон).

То, что у нас получится, примет примерный вид, представленный в таблице 6.

И теперь к столбцу С табл. 6 мы применяем инструмент «Проверка данных», в котором так­же выбираем список, но в качестве источника данных выбираем не список ЦФО, а указываем ссылку на соседнюю ячейку (в столбце B) через формулу ДВССЫЛ, которая позволит нам полу­чить не собственно значение ячейки в столбце B, а сразу ссылку на перечень значений в диапазоне с именем, которое в данной ячейке содержится.

Помните, что в имени ячеек и диапазонов нельзя использовать пробелы. И чтобы у нас все вышло красиво и правильно (то есть не было статей вида «Хозяйственные_расходы» и т.п.), нам еще нужно в статье движения де­нежных средств пробелы заменить на знак подчеркивания. В результате в поле «Источник» в проверке данных мы укажем формулу:

=ДВССЫЛ(ОДСТАВИТЬ);" ";"_")).

Теперь при заполнении столбца С табл. 6 сотруднику будет выводиться для выбора невесь перечень имеющихся в компании ЦФО, а только те из них, по которым возможна выплата по указанной им статье движения денежных средств, что существенно снизит вероятность ошибок и возможность потери данных.

Условное форматирование

В используемом нами примере осуществляемые выплаты денежных средств (учитываемые в табл. 3 или 6, но мы вернемся именно к табл. 3, чтобы не перегружать пример) в последующем формируют отчет о движении денежных средств Компании.

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

В результате табл. 5 примет вид, представленный в табл. 7

Отмечу, что в Excel очень удобно контролировать выполнение бюджета с использованием такого инструмента, как условное форматирование.

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

В EXCEL ОЧЕНЬ УДОБНО КОНТРОЛИРОВАТЬ ВЫПОЛНЕНИЕ БЮДЖЕТА С ИСПОЛЬЗОВАНИЕМ ТАКОГО ИНСТРУМЕНТА,КАК УСЛОВНОЕ ФОРМАТИРОВАНИЕ.

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

• в случае если фактический расход по статье бюджета составляет более 90% от его запланированной величины, ячейка будет выделяться желтым;
• в случае если фактический расход превысил плановый, ячейка будет выделяться красным.

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

Итак, мы выделяем интересующую нас ячейку, например C6 («Фактические платежи по хозяйственным расходам»), и выбираем инструмент «Условное форматирование/Правила выделения ячеек/Больше» и указываем:

Форматировать ячейки, которые БОЛЬШЕ: B6 х 0,9 с Желтая заливка и темно-желтый текст.4

После этого в случае, если фактический расход составит более 90% от планового, ваша ячейка окрасится в желтый цвет. Затем еще раз повторяем данную операцию (Условное форматирование/ Правила выделения ячеек/Больше) и указываем:

Форматировать ячейки, которые БОЛЬШЕ: B6 с Светло-красная заливка и темно-красный текст.

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

И если предположить, что мы сейчас «находимся» в феврале, то нам нужно усилить контроль за расходами на маркетинг, так как компания вплотную приблизилась к исчерпанию выделенного по данной статье бюджета.

Точно таким же образом (только, естественно, наоборот: меньше 90% — красное, от 90% до 100% — желтое) мы можем выделить и приходные статьи бюджета. Более того, выделение расходных статей мы можем привязать к выполнению плана приходных: если, например, степень выполнения плана по расходам превышает степень выполнения плана по приходу, то ячейка загорается красным. Что, согласитесь, просто невероятно удобно. И, главное, еще более полезно.

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