Рубрики
Формулы

Как читать сложные формулы

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

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

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

Возьмем формулу как пример из статьи Как перевести дату из текстового формата в формат даты и рассмотрим ее подробнее.

Дано: дата в текстовом формате в виде «M/D/YYY», например, «8/5/2011» или пятое августа 2011 года. Необходимо: перевести ее в формат даты, который может участвовать в расчетах.

Ячейке с входными данными присвоено имя «date_input_4».

Формула:

=IFERROR(DATE(RIGHT(date_input_4;4); LEFT(date_input_4; SEARCH(«/»;date_input_4)-1); MID(date_input_4; SEARCH(«/»;date_input_4)+1; SEARCH(«/»;date_input_4; SEARCH(«/»;date_input_4)+1)-SEARCH(«/»;date_input_4)-1)); «Input format error»)

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

Первое, говорим большое спасибо разработчикам офиса (начиная с 2007 версии; если у вас более ранняя версия – я вам очень сочувствую). В офисе 2007 и дальше, когда мы входим в редактирование формулы (F2), он автоматически подсвечивает ссылки на ячейки (или имена) на этом листе разными цветами. В нашем случае ссылка всего на одну ячейку, но ее выделение синим цветом очень поможет нам отделить ее визуально от функций. Формула будет выглядеть так:

=IFERROR(DATE(RIGHT(date_input_4;4); LEFT(date_input_4;SEARCH(«/»;date_input_4)-1); MID(date_input_4; SEARCH(«/»;date_input_4)+1; SEARCH(«/»;date_input_4; SEARCH(«/»;date_input_4)+1)-SEARCH(«/»;date_input_4)-1)); «Input format error»)

Второе, не все об этом знают (на мой взгляд это очень полезное знание), текст внутри ячейки можно разделить на несколько абзацев. Для этого необходимо поставить курсор в то место, где надо поставить абзац, и нажать Alt + Enter. И что самое приятное, если такие абзацы не разделяют название функций, ссылки и имена, то они никак не влияют на расчеты. И это отлично!

Предлагаю разделить нашу формулу на несколько составных частей. Для этого сначала необходимо бегло разобрать структуру формулы. Итак, что мы в ней видим. IFERROR (ЕСЛИОШИБКА) — проверка на ошибку. Это мой бзик, в принципе, если бы ее здесь не стояло, то в случае ошибки формула бы выдавала ошибку #VALUE!. Здесь же ее можно удалить в целях отпимизации формул.

Дальше стоит формула DATE (ДАТА), которая собирает дату (в формате даты) из трех переменных: год, месяц, день (именно в таком порядке). Вот на них мы формулу и разобьем. Несколько раз нажимаем Alt + Enter, растягиваем строку формул (Formulabar), чтобы вся формула была видна одновременно, и получаем следующее (кстати, именно в таком виде она была оригинально написана в статье Как перевести дату из текстового формата в формат даты).

=IFERROR(DATE(
RIGHT(date_input_4;4);
LEFT(date_input_4;SEARCH(«/»;date_input_4)-1);
MID(date_input_4;SEARCH(«/»;date_input_4)+1;SEARCH(«/»;date_input_4;SEARCH(«/»;date_input_4)+1)-SEARCH(«/»;date_input_4)-1));
«Input format error»)

Если мы нажмем Enterи применим изменения формулы, ее значение не должно измениться.

Третье, разберемся в том, что получилось, поблочно.
RIGHT(date_input_4;4);
Год. Функция RIGHT (ПРАВСИМВ) просто берет последние четыре символа из даты (хотелось бы верить, что в дате не будет пробела в конце, иначе ошибка в расчете нам обеспечена – отсюда вопрос, хотим ли мы инкорпорировать проверку на пробелы в формулу или решим потенциальную проблему простой заменой пробела на пустую строку? – решать вам, я решил этого не делать).

LEFT(date_input_4;SEARCH(«/»;date_input_4)-1);
Месяц. Когда я вижу вложенные формулы, я читаю их изнутри, называя каждую часть как-нибудь понятно для себя. В данном случае, SEARCH(«/»;date_input_4) (ПОИСК) выдает номер символа “/” в строке (2, в нашем случае). SEARCH(«/»;date_input_4)-1 – то же, но на один символ раньше. Этот блок я назову для себя «Номер последнего символа до “/”».

Дальше, формула LEFT (ЛЕВСИМВ), берет из входных данных слева все символы до Номера последнего символа до “/”, включительно. В нашем случае, она берет один символ слева, то есть 8.

MID(date_input_4;SEARCH(«/»;date_input_4)+1;SEARCH(«/»;date_input_4;SEARCH(«/»;date_input_4)+1)-SEARCH(«/»;date_input_4)-1));
День. Вот это уже поинтереснее. Ее бы уже и саму стоит разложить на несколько частей, но мы этого делать не станем. Сама по себе формула MID (ПСТР) берет (1) из некоторой строки, (2) начиная с некоторого символа, (3) необходимое нам количество символов. Остальное читаем изнутри.

Со строкой, из которой брать символы, все понятно – это наши входные данные.
SEARCH(«/»;date_input_4) – это номер символа “/”. Соответственно, SEARCH(«/»;date_input_4)+1 – это номер первого символа после “/”.
SEARCH(«/»;date_input_4;SEARCH(«/»;date_input_4)+1) – ищет в нашей строке “/”, начиная с первого симвода после “/” или, другими словами, ищет второй “/”. Назовем эту часть формулы номер второго символа “/” (что важно, от начала строки). Если вычесть из нее SEARCH(«/»;date_input_4) (то есть номер первого символа “/”), то получится количество символов от первого симвода “/” до второго, включая один из них и исключая другой. Вычитаем из этого количества еще 1, и получаем количество символов между первым и вторым символом “/”.
То есть день получился равен (1) выбранным из строки с датой, начиная (2) с первого символа после “/”, (3) нескольким символам между первым и вторым символом “/”. То есть 5, в нашем примере.

Что мы имеем в итоге. Формула проверяет на ошибку, в случае ошибки выдает «Inputformaterror». Если ошибки нет, то она формирует дату (в формате даты) из года, месяца и дня, которые она выбирает из текста строки с помощью текстовых формул RIGHT, LEFT, MID и SEARCH. День месяц и год выбираются корректно. Получается 05.08.2011, что и требовалось.

Резюмируя вышесказанное, я рекомендую, читая сложные формулы, делать это в несколько шагов:

поблагодарить Мелкософт за хорошо следанную работу (можно пропустить, принимая во внимание, сколько они за эту работу хотят);
определить основную структуру формулы и разделить ее на блоки построчно;
разобраться в каждом блоке, читая формулы наизнанку и давая блокам понятные себе имена; и
озвучить себе, что получилось в итоге.
По оценкам экспертов только десять процентов пользователей MS Excel умеют граммотно пользоваться формулами рабочего листа. Если вы чувствуете, что вы не среди этих десяти процентов, книга Формулы в Microsoft Excel 2010 для вас. Не пожалейте времени на ее прочтение — она того стоит!

Рубрики
Отчетность

Как сделать профессиональный отчет как в крупных компаниях

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

Итак, как сделать отчет как в крупной компании.

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

Читайте также как оптимально распечатать документ, как сделать перенос по словам и для чего нужны текстовые поля, а также о том, как распечатать документ с комментариями.

Рубрики
Отчетность

Как вставить связанную таблицу Excel в MS Word

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

Итак, мы подготовили таблицы, которые пойдут в документ в Microsoft Excel. Скажем, это отчет о прибылях и убытках. Скажем, такой.

Как вы видите — самый что ни на есть Excel’евский. У него есть куча преимуществ. Прежде всего, с ним удобно работать, он может содержать необходимые вычисления, формулы, удобно отслеживать, откуда берутся данные (качественный audit trail) и т.п. Проблема в том, что он не в вордовском файле. И эта проблема — серьезная.

Есть пара способов вставить его в Microsoft Word:

(1) вставить его картинкой (преимущества — невозможно изменить, сохраняется точное форматирование; недостатки — опять-таки, невозможно изменить, много весит, если вставить 30-40 таких таблиц, могут быть проблемы с отправкой по электронной почте, особенно с корпоративного ящика), и

(2) вставить его текстом (преимущества и недостатки — с точностью до наоборот).

У обоих способов есть существенная проблема. При изменении исходников с таблицами, их надо вставлять заново. При подготовки отчетности, о которой я писал в начале, не исключено обновление таблиц с завидной регулярностью, скажем 5-10 раз за подготовку. Чтобы избежать пустой траты времени на обновление таблиц, я предлагаю вставлять связанные таблицы.

Как вставить связанную таблицу из Excel в Word

(1) Выделите таблицу Excel, которую хотите вставить в документ Word, и нажмите копировать,

(2) перейдите в Word, поставьте курсор на место, куда хотите вставить таблицу и нажмите Связать и сохранить исходное форматирование.

Если вы пользуетесь более ранней версией офиса (скриншот сделан из Microsoft Office 2010), нажмите Специальная вставка… И выберите в появившемся окне опцию Связать, вместо Вставить, которая стоит по умолчанию.

Результат выглядит так.

Вы наверняка заметили, что связать можно в разных форматах. У каждого из них есть свои преимущества и недостатки. Выбирайте исходя из ваших личных предпочтений. Главное, что вставленная таблица будет обновляться вместе с обновлением таблиц в Excel (при запуске Word будет просить обновить внешние данные — смело жмите Обновить). Теперь вы не забудете обновить одну таблицу из 40, вставленных в документ!

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

Читайте также о том, как составить профессионально выглядящий отчет в Microsoft Excel, как составить таблицы, которые будут удобно читаться, а также о том, как перевести все числа в тысячи, миллионы и т.д. с помощью форматирования.

Рубрики
Отчетность

Как сделать профессионально выглядящий отчет в Excel

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

В работе каждый день мы видим множество отчетов, которые выглядят по-детски или некачественно. Чтобы ваши отчеты выглядели профессионально, я предлагаю вам один из вариантов подготовки таких отчетов. Изначально он был разработан для анализа финансовой отчетности крупного предприятия — получилось около 30 страниц текста, схем и диаграмм. Отчет очень понравился менеджменту.

Я предлагаю вам посмотреть пример такого отчета в приложенном файле по этой ссылке. О том, как его подготовить в Microsoft Excel, не прибегая к дорогостоящему программному обеспечению, такому как Adobe InDesign.

Для этого нам надо взять на вооружение несколько вещей: (1) вид в режиме «Разметка страницы» (включить его можно, нажав на ленте View на кнопку Page layout из секции Workbook views, об этом виде и о том, как оптимизировать отчет для печати смотрите в этой статье), (2) научный подход.

Если с первым пунктом все понятно, то второй требует определенного пояснения.

Общие советы

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

Шаблон не должен быть аляпистым. Я предлагаю вам использовать цвета стандартной темы Microsoft Excel 2007-2010. Они очень комфортные для восприятие пользователем вашего отчета: будь то ваш менеджер, финансовый директор, исполнительный директор, акционеры или клиенты компании. Для примера я использовал синий цвет. Зеленый также выглядит достаточно хорошо. Если есть какой-то раздел, на который вы хотели бы обратить особое внимание, вы можете сделать его в красных тонах, главное выбирайте не электрический красный, а комфортный красный.

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

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

Если вы собираетесь распространять его в электронном виде, ОБЯЗАТЕЛЬНО переведите его в формат pdf. Он гораздо удобнее для чтения, хоть и теряет некоторые функции. На мой взгляд, самый удобный способ перевода его в этот формат — это не использование pdf-ных принтеров, а встроенная функциональность Microsoft Excel 2007-2010 (File => Save as => выбрать формат pdf).

Специфика подготовки

Далее я предлагаю посмотреть на исходник данного отчета в формате Excel.

Есть несколько моментов, на которые я хотел бы обратить ваше внимание.

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

(2) «Притягивайте» текстовые поля и графики к краям ячеек. Тогда они все будут находиться строго вертикально друг под другом. Размер графика и параллельного с ним поля можно будет менять одновременно, растягивая строку, к которой они привязаны. Для того чтобы «привязать» размер объекта к краю ячейки, в момент изменения размера этого объекта нажмите на Alt на клавиатуре. Кстати, если нажать Shift, то его размеры будут меняться строго в одном направлении — вертикально или горизонтально.

(3) При подготовке графиков постарайтесь не перегружать их, а также использовать один цвет разной насыщенности для схожих по природе элементов графика. Подробнее об этом в статье Типовые ошибки при построении графиков.

Послесловие

Разработку данного отчета во многом вдохновил формат публикаций, подготавливаемых компанией KPMG. Вы можете посмотреть их на их официальном сайте. Уж не знаю в чем они их делают, но из всей G4 у них самые современные и профессионально выглядящие отчеты.

Читайте также как оптимально распечатать документ, как сделать перенос по словам и для чего нужны текстовые поля, а также о том, как распечатать документ с комментариями.

Рубрики
Отчетность

Удобные форматы представления таблиц

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

Создавая каждую конкретную таблицу, я всегда думаю о тех, кто ей будет пользоваться. Как сделать таблицу удобной для чтения. В зависимости от типа таблицы, я предлагаю вам пользоваться несколькими видами представления. Описанные ниже типы представления таблиц – это лишь предложение подумать в этом направлении, совсем не абсолютная истина. Попробуйте сделать несколько таблиц в таком виде и посмотрите на реакцию пользователей. Я был приятно удивлен, когда услышал, что коллеги мои таблицы у «за спиной» называют «табличками по фен-шую». Я расцениваю это как комплимент.

Общие советы:

Постарайтесь отказаться от границ настолько насколько это возможно (в разумных пределах). Так например, вертикальные границы, как правило не нужны, за ООООЧЕНЬ редким исключением, когда вам необходимо подчеркнуть принципиальное разделение двух блоков данных в таблице (или даже противопоставить их). Старайтесь не использовать горизонтальные границы между ячейками одного типа (например, формирующими список одного уровня) – такие границы совсем не помогают удобно читать таблицу. Почему-то бытуем мнение, что если стоят такие границы, то взгляду будет проще бегать по строкам, и он не будет перескакивать на соседние. На мой взгляд, это совсем не так. Лучше сделать побольше расстояние между строками – вот это точно поможет.

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

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

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

Вариант первый. Хорошо подходит для небольших таблиц аналитического характера. Что интересно, компания Microsoftсовсем не видит за ними будущего и не делает встроеной функциональности для этого типа представления. Именно его называли «по фен-шую». Этот тип очень хорошо знаком (порой кажется, что даже слишком хорошо) студентам и членам CIMA, потому что экзаменаторы настоятельно рекомендуют использовать его при написании экзаменов. Это очень международный формат таблиц. Он выстрадан временем и берет начало в те времена, когда таблицы еще писали от руки (не удивлюсь, если пером), одними чернилами. Позже они получили не меньшее распространение, так как их можно было легко воспроизвести и на машинке и на матричном принтере. Они же как нельзя лучше подходят и для черно-белых лазерных принтеров.

Основная идея заключается в том, что в таблице практически нет границ. Они используются только для отделения шапки таблицы, промежуточных итогов и просто итогов таблицы. Секции таблицы разделяются друг от друга визуально отступом. Линии не проводятся на названия параметров, а только делаются в блоке с цифрами. Также наименования параметров, которые позже подбиваются итогами делаются с отступом. Очень удобно!

Вот пара примеров таких таблиц:

Экселевский файл можно посмотреть здесь.

При построении таблицы я призываю не создавать лишних строк, а «играть» с увеличением высоты и выравниванием текста в них (по нижнему краю, по верхнему краю или по центру). Для чисел как нельзя лучше подходит бухгалтерский формат (“comma style”, или “000” в русской версии) – он делает отступ справа. При этом ширину столбцов лучше делать немного больше максимального по ширине числа – тогда столбцы не будут сливаться и выглядеть очень прижатыми друг к другу.

Остальное – дело нескольких минут использования границ, полужирного шрифта и отступов строк.

Второй вариант. Отлично подходит для больших массивов данных, представленных в так называемых «классических таблицах»: когда в верхней строке таблицы находится шапка, а данные представлены ниже.

Шапка обычно выделяется насыщенным цветом заливки (текст при этом делается белым), а массив данных заливается через одну строчку тем же цветом минимальной насыщенности. Я люблю синий, но если ваш корпоративный цвет – зеленый, почему бы не использовать его?

Выглядеть таблица будет приблизительно так.

Или так.

Экселевский файл можно посмотреть здесь.

Что особенно приятно, сделать такое форматирование таблицы можно буквально в пару кликов.

(1) выделить таблицу с данными,
(2) нажать на ленте Home в блоке Styles на кнопку Format as table и выбрать подходящий вариант,
(3) в появившемся окне подтвердить диапазон таблицы и наличие строки с заголовками.

Все! Таблица готова.

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

Читайте также о том, как использовать формат для представления чисел в тысячах, миллионах и т.д.

Рубрики
Отчетность

Типовые ошибки при построении графиков

Как часто нам попадаются абсолюно нечитаемые графики! Сколько времени приходится потратить на то, чтобы разобраться в деталях! Давайте упростим жизнь своим коллегам и менеджменту, не будем совершать ошибки, которых так лего избежать.

Основные ошибки перечислены ниже.

График получился аляпистым

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

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

Фон может сильно отвлекать от содержимого графика, особенно, если он яркий. Лично меня сильно раздражает серый фон. А вот едва заметный градиент от белого к светло-голубому создает с одной стороны чувство заполненности пространства (нет ощущения пустоты), а с другой стороны он не отвлекает от деталей. Почему именно градиент – он как бы показывает где плотность графика выше, а где ниже. Чем плотнее цвет, тем больше показатель.

График перегружен

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

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

На графике мало информации

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

Структура слишком раздроблена

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

Выход из этой ситуации простой – не делите структуру больше, чем на 4-5 частей. Если их больше по определению, попробуйте объединить наиболее маленькие из них в группы. Никто не отменял группу «Прочие», в конце концов.

Нет контекста

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

Читайте также о том, с чего начинается построение любого графика, а также о круговых диаграммах, графиках-водопадах (waterfalls), графиках-торнадо (tornado).

Рубрики
Отчетность

С чего начинается любой график

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

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

Для кого мы делаем этот график?
Кто собственно пользователь отчета: менеджер среднего звена, коллега и директор? Каков уровень его технических знаний? Насколько хорошо он знает контекст?

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

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

Что мы пытаемся донести этим графиком?
Для чего этот график собственно нужен здесь? Да и, если честно, то нужен ли он вообще? Очень часто бывает так, что мы вставляем график, потому что так принято. «В этом отчете всегда был такой график!» Нет никакого смысла повторять один и тот же график из месяца в месяц в одном и том же отчете. Обычно цель графика в том, чтобы обратить внимание на необычный тренд, подчеркнуть долю чего-либо, провести наглядный факторный анализ или анализ чувствительности по факторам. В этой связи редко бывает так, что график остается актуальным в течение долгого промежутка времени.

Выбор вида графика будет очень сильно зависеть от того, что мы пытаемся им донести. Структуру – здравствуй, круговой график (pie), нужна структура в динамике – выбираем накопленные колонки (stackedcolumns), нужен анализ чувствительности от разных факторов – выбираем накопленные бары (stackedbars) и т.п.

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

График будет на бумаге? – отлично! А на каком принтере мы будем его печатать: цветном или черно-белом. Цветной принтер позволяет использовать нам разные цвета одинаковой насыщенности, черно-белый нам такой роскоши не простит – разные части графика сольются между собой. Собираетесь показывать график в презентации – проследите, чтобы он был большим и на нем не было мелких деталей, потому что далеко не все, кто плохо видят, носят очки. Если график будет в презентации, то на нем должны быть только основные идеи. Детали придется рассказывать самому. Собираетесь распространять документ по электронной почте – можете делать с ним все, что угодно.

Читайте также о том, как избежать основных ошибок при построении графиков, а также о круговых диаграммах, графиках-водопадах (waterfalls), графиках-торнадо (tornado).

Рубрики
Отчетность

Использование формата для перевода в другие единицы измерения (например, в тысячи) и для форматирования

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

Есть два способа добиться нужного результата: простой, но медленный – назовем его методом топора, и посложнее, но гораздо более быстрый и удобный в последующих вычислениях – метод изящной резьбы.

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

Метод изящной резьбы. Для целей вычислений все цифры оставляем в рублях. А форматом убираем лишние разряды.

Во-первых, пожалуйста, посмотрите на приложенный файл. Это набросок упрощенного отчета о финансовом положении компании ОАО «Рога и копыта». Он строится на основе данных из оборотки на листе «ОСВ». Обратите внимание, что данные на листе «ОСВ» представлены в рублях, в то время как на листе «ОоФП» они уже представлены в тысячах рублей. При этом деления нет.

Это было достигнуто с помощью создания специального числового формата, а именно такого, который прячет ненужные мне три последних разряда (они просто не отражаются), а последний отражаемый округляется. Чтобы настроить формат числа, выделите необходимые ячейки (желательно, чтобы в первой из них было число, тогда вы будете видеть настраиваемый формат на пример) и перейдите в настройку формата числа (есть несколько способов (1) Ctrl + 1, затем вкладка Number, (2) нажать на ячейку правой кнопкой, выбрать Format cells…, затем вкладка Number, (3) На ленте выбрать Home, в секции Number в выпадающем списке выбратьMorenumberformats… Слева выберите тип Customи введите необходимый формат в строку Type. Описание формата приведено ниже.

Немного о форматах вообще


То, каким образом записываются форматы сильно зависит от настроек системы, а именно региональных настроек системы (см. Пуск => Панель управления => Язык и региональные стандарты, вкладка Форматы, кнопка Дополнительные параметры…).

По-умолчанию в русской версии стоят немного другие параметры: разделитель целой и дробной части – запятая, а не точка; разделитель групп разрядов – пробел, а не запятая. Мои настройки повторяют стандартные настройки английской версии операционной системы. Почему это важно? Потому что прописывать формат ячейки в экселе мы будем именно символами, присвоенными операционной системой.

Числовые форматы


Для того чтобы прописать тот или иной числовой формат нам необходимо знать следующие обозначения:

0 — любой числовой символ, важно обратить внимание, что если он стоит, а такого разряда в числе нет, то в начале числа будет стоять 0.

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

Например, в ячейке стоит число 23. Если мы укажем формат 000, то это число будет отражаться как 023. Если же мы укажем формат ##0, то число будет отражаться просто как 23. Прошу обратить ваше внимание, что первый формат не является неправильным, просто им редко пользуются. Для некоторых конкретных случаев он может быть очень полезен. Например, программа SAPBWотражает центры затрат в формате 00001XXXXXпо-умолчанию, в то время как при выгрузке данных из SAP просто, центры затрат отражаются в формате 1XXXXX. Формат с нулями в начале может быть очень полезен для унификации.
Разделитель групп разрядов (каким бы он ни был) – отражает разделение числа на группы по три цифры (например на калькуляторе для этих целей обычно используется апостроф).
Разделитель целой и дробной части числа (опять-таки, будь то точка или запятая – не важно). При записи формата поможет нам определить, сколько дробных разрядов будет в прописываемом формате.
Кавычки – указатель строки. Так например, если нам понадобится прописать в формате какой-то конкретный текст, который в себе использует какой-нибудь из указанных выше символов, то его понадобится взять в кавычки.

Примеры

Далее представлено шесть наиболее ходовых примеров форматов одного и того же числа.

Первые пять форматов представляют собой унифицированную запить вне зависимости от того, положительное ли число или отрицательное. Шестой формат, напротив, представляет собой запись, состоящую из трех частей: (1) положительное число, (2) отрицательное число, (3) ноль. Эти части разделяются между собой точкой с запятой. Что касается нуля, то для того, чтобы он был заменен на прочерк, число должно быть точно равно нулю, если оно отличается от нуля хотя бы и на 0.000000001, такое число будет представлено в соответствии с настройками для положительного или отрицательно числа. Чтобы избежать такой записи, может потребоваться округление.

Подробнее я хотел бы остановиться на четвертой записи. В формате указано, что необходимо разделить число на группы по три разряда, и не показывать последний разряд (после последней запятой не стоят ни 000, ни ###, ни ##0. Это означает, что они будут спрятаны. Таким же образом можно показать число в миллионах, соответственно необходимо будет в конце формата поставить две запятых (или пробела, если пробел выбран разделителем групп разрядов в глобальных настройках системы).

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

Файл с данными настройками приложен.

Рубрики
Анализ

Как быстро построить график Waterfall (водопад)

В Excel отсутствует стандартные диаграммы типа Waterfall. Поэтому для создания диаграмм этого типа обычно используют гистограмму с накоплением (Staked columns или Staked bars). Об этом способе я писал раньше здесь. Он требует определенного умения, подготовки целой таблицы, которая ляжет в основу диаграммы и кучу времени. Можно, конечно, заранее набросать себе шаблон для диаграммы, но и его часто надо адаптировать под конкретные нужды. Если же вам нужно вынести подписи данных за столбцы, то придется двигать их руками – каждую подпись. А если вдруг данные изменятся – придется еще раз двигать подписи. Есть быстрый простой способ построить график Waterfall (также известный как водопад, бруски, Bridge) для факторного анализа в два клика.

Создание диаграммы waterfall за два клика
Надстройка Waterfall Chart Studio расширяет функциональные возможности Microsoft Excel, предоставляя Вам возможность работать с диаграммами waterfall, как со стандартными диаграммами Excel. Скачать бесплатно пробную версию надстройки можно здесь.
Ниже вы можете посмотреть короткое видео, описывающее процесс создания диаграммы Waterfall.

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

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

Существует возможность построения диаграмм с промежуточными итогами.

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

Примеры диаграмм

Автоматическое выравнивание и форматирование подписей данных
Если вы хоть раз строили график Waterfall с небольшими значениями факторов, то вы знаете, что оставлять подписи данных в положении по-умолчанию нельзя, так как они частично остаются на столбце, а частично «вылазят» за его пределы. Выглядить это, в лучшем случае, непрофессионально. Приходтся руками сдвигать подписи так, чтобы они были над или под «брусками». Если значения меняются, приходится повторять эту процедуру.
Надстройка Waterfall Chart Studio может выполнить выравнивание автоматически, при этом значения окрашиваются в цвет столбцов (в зависимости от знаков). При обновлении данных эта процедура выполняется автоматически – никакой ручной работы.

В заключение

Если у вас есть много свободного времени и нервов, вы можете рисовать графики Waterfall руками с помощью стандартной функциональности Excel. Есть быстрый и простой способ строить диаграмму Waterfall (водопад, бруски, Bridge) – надстройка Waterfall Chart Studio. Цена вопроса — $19.95. Подробнее о продукте – здесь.

Рубрики
Анализ

Bullet chart для сравнения планируемых показателей и фактического исполнения

Часто возникает необходимость сравнить основные финансово-экономические показатели с планируемыми, например, для целей определения исполнения организацией поставленных целей и задач. Существует множество способов презентации: таблицы, помесячное сравнение факт/бюджет с помощью графиков или столбчатых графиков и т.п. Но сегодня я хотел бы остановиться на более простом и в то же время показательном варианте визуализации – BulletCharts. Он как будто был специально разработан для визуализации исполнения оценочной карты компании (BalancedScorecard) по Каплану и Нортону.

Как выглядит этот график

Чем хорош этого график

  1. Этот график очень прост. Его легко объяснить даже менеджменту. Скорее всего, это не понадобится делать больше, чем один раз
  2. Но в то же время он показывает, не только план/факт, но и границы допустимого отклонения
  3. Занимает мало места на бумаге
  4. Высокий показатель data/ink
  5. Выглядит ничуть не хуже при черно-белой печати, чем при цветной

Чем плох этот график

  1. Этот график показывает сравнение только одного показателя
  2. Этот график показывает сравнение этого показателя только целиком – нет возможности показать помесячное/поквартальное исполнение показателя

Как его сделать

Способ, который предлагаю я, — не единственный, но он кажется мне самым естественным. Не надо выдумывать кучу «обходных маневров». В то же время, он дает наибольшие возможности для оформления, так как он основан на стандартных диаграммах Excel. О других способах вы можете почитать у Чанду.

  1. Составьте таблицу с данными. Для того чтобы построить график, достаточно данных: факт, нижняя граница допустимого диапазона и верхняя граница диапазона, после которого показатель считается перевыполненным. На практике я бы также добавил целевой показатель, который хоть и не будет визуализирован, может добавить полезности презентации данной таблицы, если вы решите показывать ее вместе с графиками. В таблицу вы можете добавить и другие колонки, такие как отклонение от целевого показателя в процентах, вес данного показателя в оценочной карте (BalanceScorecard) или очки за его достижение.
  1. Для диаграммы мы будем использовать тип диаграммы StackedBars (Линейчатые диаграммы с накоплением). Составим таблицу, на основе которой будет строиться диаграмма. Для этого нам понадобится определить размеры будущих линейчатых блоков (bars). Обратите особое внимание на тот факт, что некоторые показатели могут быть лучше, если они больше целевого (например, выручка), а другие – наоборот, лучше, если они меньше целевого (например, затраты)
  1. Вставьте линейчатую диаграмму с накоплением (StackedBars)
  1. Теперь делаем настройки диаграммы, чтобы она стала похожа на ожидаемый результат. Во-первых, необходимо перенести факт на вторичную ось (выделите блок с фактом, нажмите Ctrl+1 (свойства) и выберите Вторичную ось (Secondaryaxis)). В этом же окне укажите ширину отступа 75% (GapWidth). Во-вторых, выделите любой из других блоков и установите ширину отступа 0% (GapWidth). Уберите вертикальную ось, вторичную горизонтальную ось, линии сетки и легенду (для этого просто выделяйте соответствующий элемент на диаграмме и нажимайте Del). В-третьих, (необязательно) уберите обводку графика – она, на мой взгляд, совершенно не нужна. В-четвертых, подберите необходимые размеры графика (я бы порекомендовал встроить его в ячейку рядом с таблицей, тогда все диаграммы, которые вы построите будут одного размера и будут стоять идеально ровно – для этого необходимо изменять размеры графика, удерживая клавишу Alt). И последнее – поставьте необходимые цвета.
  1. Теперь копируйте график, встраивайте его в следующую ячейку и меняйте диапазон с исходными данными на следующий. В результате получится приблизительно такая картина.
  1. Если необходимо привлечь внимание к какому-то одному графику, где требуются действия руководства, вы можете покрасить его в другие цвета, скажем, красные. В моем случае, это административно-хозяйственные расходы.
  2. Теперь устанавливаем границу печати для документа и несем руководству.

Скачать файл с результатом можно по ссылке ниже.

Читайте также, о том, как построить график Водопад (WaterfallChart) для факторного анализа и график Торнадо (TornadoChart) для анализа чувствительности.