Данные листа Excel должны быть в формате списка с заголовком столбца в первой строке. Остальные строки должны содержать аналогичные объекты в одном столбце, без пустых строк и столбцов внутри диапазона данных.
Если необходимо изменить диапазон, то выделите на листе новый диапазон, не закрывая при этом окно «Создание сводной таблицы». Диапазон ячеек, который находится на другом листе книги или же в другой книге, вводится следующим образом: ([имякниги]имялиста!диапазон) .
При выборе данного места размещения необходимо в пункте «Диапазон» самостоятельно указать на листе ячейку для отчета.
Чтобы скрыть окно «Список полей», щелкните по любой свободной ячейке листа. Или же на вкладке «Параметры» в группе «Показать или скрыть» отключите пункт «Список полей».
Ренат уже не в первый раз выступает гостевым автором на Лайфхакере. Ранее мы публиковали отличный материал от него о том, как составить план тренировок: и онлайн-ресурсы, а также создания тренировочного плана.
В этой статье собраны несложные приёмы, позволяющие . Особенно они пригодятся тем, кто занимается управленческой отчётностью, готовит разнообразные аналитические отчёты, основанные на выгрузках из 1С и других отчётах, формирует из них презентации и диаграммы для руководства. Не претендую на абсолютную новизну - в том или ином виде эти приёмы наверняка обсуждались на форумах или .
Результат:
В данном случае, кроме функции СИМВОЛ (CHAR) (для отображения кавычек) используется функция ЕСЛИ (IF), позволяющая изменять текст в зависимости от того, наблюдается ли положительная динамика продаж, и функция ТЕКСТ (TEXT), позволяющая отобразить число в любом формате. Её синтаксис описан ниже:
ТЕКСТ (значение; формат )
Формат указывается в кавычках точно так же, как если бы вы вводили пользовательский формат в окне «Формат ячеек».
Автоматизировать можно и более сложные тексты. В моей практике была автоматизация длинных, но рутинных комментариев к управленческой отчётности в формате «ПОКАЗАТЕЛЬ упал/вырос на XX относительно плана в основном из-за роста/снижения ФАКТОРА1 на XX, роста/снижения ФАКТОРА2 на YY…» с меняющимся списком факторов. Если вы пишете такие комментарии часто и процесс их написания можно алгоритмизировать - стоит один раз озадачиться созданием формулы или макроса, которые избавят вас хотя бы от части работы.
При объединении ячеек сохраняется только одно значение. Excel предупреждает об этом при попытке объединить ячейки:
Соответственно, если у вас была формула, зависящая от каждой ячейки, она перестанет работать после их объединения (ошибка #Н/Д в строках 3–4 примера):
Чтобы объединить ячейки и при этом сохранить данные в каждой из них (возможно, у вас есть формула, как в этом абстрактном примере; возможно, вы хотите объединить ячейки, но сохранить все данные на будущее или скрыть их намеренно), объедините любые ячейки на листе, выделите их, а затем с помощью команды «Формат по образцу» перенесите форматирование на те ячейки, которые вам и нужно объединить:
Если вам нужно построить сводную сразу из нескольких источников данных, придётся добавить на ленту или панель быстрого доступа «Мастер сводных таблиц и диаграмм», в котором есть такая опция.
Сделать это можно следующим образом: «Файл» → «Параметры» → «Панель быстрого доступа» → «Все команды» → «Мастер сводных таблиц и диаграмм» → «Добавить»:
После этого на ленте появится соответствующая иконка, нажатие на которую вызывает того самого мастера:
При щелчке на неё появляется диалоговое окно:
В нём вам необходимо выбрать пункт «В нескольких диапазонах консолидации» и нажать «Далее». В следующем пункте можно выбрать «Создать одно поле страницы» или «Создать поля страницы». Если вы хотите самостоятельно придумать имя для каждого из источников данных - выберите второй пункт:
В следующем окне добавьте все диапазоны, на основании которых будет строиться сводная, и задайте им наименования:
После этого в последнем диалоговом окне укажите, где будет размещаться отчёт сводной таблицы - на существующем или новом листе:
Отчёт сводной таблицы готов. В фильтре «Страница 1» вы можете выбрать только один из источников данных, если это необходимо:
В данном примере в столбце A есть несколько текстовых строк, и наша задача - выяснить, сколько раз в каждой из них встречается искомый текст, расположенный в ячейке E1:
Для решения этой задачи можно воспользоваться сложной формулой, состоящей из следующих функций:
Чтобы найти вхождение определённой текстовой строки в другую, нужно удалить все её вхождения в исходную и сравнить длину полученной строки с исходной:
ДЛСТР(“Тариф МТС Супер МТС”) – ДЛСТР(“Тариф Супер”) = 6
А затем разделить эту разницу на длину той строки, которую мы искали:
6 / ДЛСТР (“МТС”) = 2
Именно два раза строка «МТС» входит в исходную.
Осталось записать этот алгоритм на языке формул (обозначим «текстом» тот текст, в котором мы ищем вхождения, а «искомым» - тот, число вхождений которого нас интересует):
=(ДЛСТР(текст )-ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН(текст );ПРОПИСН(искомый );“”)))/ДЛСТР(искомый )
В нашем примере формула выглядит следующим образом:
=(ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН(A2);ПРОПИСН($E$1);“”)))/ДЛСТР($E$1)
Отчетность перед руководством и собственниками компании — важная составляющая в работе планово-экономического отдела. Отчеты должны быть понятными и лаконичными, а контрольные показатели необходимо представлять в подробных аналитиках. Если вам нужно подготовить полугодовую отчетность с учетом таких требований, рекомендуем ознакомиться с материалом данной статьи. Вы узнаете, как в Excel сделать отчеты за полугодие в графической форме, выделить в них ключевые показатели.
При подготовке полугодовой отчетности специалисты сталкиваются со следующей проблематикой:
Чтобы успешно справиться с указанными проблемами, подготовить для руководства четкие и понятные отчеты за полугодие, предлагаем использовать графическую форму. Для объемных таблиц, подробных аналитик информативно применить спарклайны и условное форматирование, для компактных таблиц и выбранных ключевых показателей оптимально использовать диаграммы.
Спарклайны в Excel
В полугодовом отчете принято демонстрировать помесячную динамику показателей. Речь идет о таких показателях, как выручка, объем реализации, себестоимость, объем закупок и прибыли, рентабельность. Перечень номенклатуры на практике всегда широкий. В таком случае удобно непосредственно в таблицу с показателями вставить графики, что позволит оценить не только отдельные показатели, но и их динамику, сопоставить результаты. Для указанной группы показателей целесообразно применить спарклайны (рис. 1).
Визуализируем данные табл. 1 «Анализ продаж с помощью спарклайнов», выполнив следующие действия:
2. Выполняем команду: Вставка → Спарклайны → График (см. рис. 1). В окне «Создание спарклайнов » задаем диапазон данных и диапазон расположения (рис. 2).
3. Копируем графический элемент в нужное количество ячеек. При необходимости редактируем график с помощью Конструктора (рис. 3). Например, отмечаем флажком отметку максимальных точек или маркеров, изменяем цвет графика.
4. Для «Анализа выручки по продуктовым группам» и «Анализа рентабельности по продуктовым группам» используем тип спарклайна «Столбец».
Дополненная спарклайнами табл. 1 представлена в «Сервисе форм» (код доступа — на с. 119). Из таблицы видно, что только выручка по красителям снижается, имеет нисходящую динамику графика-спарклайна. По остальной продукции и в целом по компании выручка растет (графа «Динамика за первое полугодие»).
Строка «Анализ выручки по продуктовым группам» показывает, что наибольший объем выручки компания получает от реализации кирпича стандартного гладкого — 4003,9 из 42 068,82 тыс. руб.
Столбцовый спраклайн в строке «Анализ рентабельности по продуктовым группам» показывает, что максимальную рентабельность имеет кирпич фактурный — от 40 до 42,9 %. Скачек рентабельности произошел в апреле (видно на графике в столбце «Динамика за I полугодие»). В апреле зафиксирована отрицательная рентабельность по кирпичу стандартному и сниженная (11,1 %) по кирпичу гладкому с повышенным содержанием пигментов.
Важный момент: в отчете нужно указать причину визуализированных изменений. Возможная причина изменений в рассматриваемом случае: в начале сезона продаж (весна) предоставили акционные скидки для стимулирования объемов продаж, привлечения новых клиентов.
ОБРАТИТЕ ВНИМАНИЕ
При копировании таблицы со спарклайнами из Excel в документ Word графики могут быть не видны. В таком случае используют вставку в формате рисунка.
В отношении расходов руководство часто требует подробную аналитику. Это могут быть бюджеты расходов по разным направлениям, подробный отчет по фонду оплаты труда, платежи поставщикам. В отчетах нужно выделять важную информацию и нестандартные значения. Восприятие данных улучшится, если использовать гистограммы, цветовые шкалы, наборы значков.
Подготовим табл. 2 «Бюджет общезаводских расходов за I полугодие (факт)» к сдаче руководству с помощью функций условного форматирования:
Высокие расходы окрасились в привлекающий внимание зеленый цвет:
Чем выше сумма расходов, тем насыщеннее цвет в ячейке.
Выбираем «Форматировать все ячейки на основании их значений», а затем подходящий «Значок». В нашем случае это гистограммы. Определяем «Значение» и «Тип»:
Параметры экономист задает исходя из их разброса, средних значений и правил, принятых в компании.
Бюджет общезаводских расходов для отправки руководству выглядит следующим образом (табл. 2).
На примере таблицы «Кредиторская задолженность на конец периода» рассмотрим еще один способ удобно представить показатели.
Е. С. Панченко, бизнес-консультант
Материал публикуется частично. Полностью его можно прочитать в журнале
Данная статья немного устарела. Предлагаю Вам ознакомиться с ее новой версией, заключающей в себе новый опыт, полученный за последний год работы. Новая статья .
В работе каждый день мы видим множество отчетов, которые выглядят по-детски или некачественно. Чтобы ваши отчеты выглядели профессионально, я предлагаю вам один из вариантов подготовки таких отчетов. Изначально он был разработан для анализа финансовой отчетности крупного предприятия - получилось около 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).
Есть несколько моментов, на которые я хотел бы обратить ваше внимание.
(1) На листе шаблона самая правая колонка - очень узкая (буквально пара пикселей). Она там не просто так. Если ее не добавить и вставлять графики и текстовые поля "притягивая" их к краю ячеек, то те из них, что находятся справа, своей границей (даже если она прозрачная) будут "залазить" на соседнюю страницу, заставляя Excel печатать пустую страницу.
(2) "Притягивайте" текстовые поля и графики к краям ячеек. Тогда они все будут находиться строго вертикально друг под другом. Размер графика и параллельного с ним поля можно будет менять одновременно, растягивая строку, к которой они привязаны. Для того чтобы "привязать" размер объекта к краю ячейки, в момент изменения размера этого объекта нажмите на Alt на клавиатуре. Кстати, если нажать Shift , то его размеры будут меняться строго в одном направлении - вертикально или горизонтально.
Послесловие
Разработку данного отчета во многом вдохновил формат публикаций, подготавливаемых компанией KPMG. Вы можете посмотреть их на их официальном сайте. Уж не знаю в чем они их делают, но из всей G4 у них самые современные и профессионально выглядящие отчеты.
Ренат уже не в первый раз выступает гостевым автором на Лайфхакере. Ранее мы публиковали отличный материал от него о том, как составить план тренировок: и онлайн-ресурсы, а также создания тренировочного плана.
В этой статье собраны несложные приёмы, позволяющие . Особенно они пригодятся тем, кто занимается управленческой отчётностью, готовит разнообразные аналитические отчёты, основанные на выгрузках из 1С и других отчётах, формирует из них презентации и диаграммы для руководства. Не претендую на абсолютную новизну - в том или ином виде эти приёмы наверняка обсуждались на форумах или .
Результат:
В данном случае, кроме функции СИМВОЛ (CHAR) (для отображения кавычек) используется функция ЕСЛИ (IF), позволяющая изменять текст в зависимости от того, наблюдается ли положительная динамика продаж, и функция ТЕКСТ (TEXT), позволяющая отобразить число в любом формате. Её синтаксис описан ниже:
ТЕКСТ (значение; формат )
Формат указывается в кавычках точно так же, как если бы вы вводили пользовательский формат в окне «Формат ячеек».
Автоматизировать можно и более сложные тексты. В моей практике была автоматизация длинных, но рутинных комментариев к управленческой отчётности в формате «ПОКАЗАТЕЛЬ упал/вырос на XX относительно плана в основном из-за роста/снижения ФАКТОРА1 на XX, роста/снижения ФАКТОРА2 на YY…» с меняющимся списком факторов. Если вы пишете такие комментарии часто и процесс их написания можно алгоритмизировать - стоит один раз озадачиться созданием формулы или макроса, которые избавят вас хотя бы от части работы.
При объединении ячеек сохраняется только одно значение. Excel предупреждает об этом при попытке объединить ячейки:
Соответственно, если у вас была формула, зависящая от каждой ячейки, она перестанет работать после их объединения (ошибка #Н/Д в строках 3–4 примера):
Чтобы объединить ячейки и при этом сохранить данные в каждой из них (возможно, у вас есть формула, как в этом абстрактном примере; возможно, вы хотите объединить ячейки, но сохранить все данные на будущее или скрыть их намеренно), объедините любые ячейки на листе, выделите их, а затем с помощью команды «Формат по образцу» перенесите форматирование на те ячейки, которые вам и нужно объединить:
Если вам нужно построить сводную сразу из нескольких источников данных, придётся добавить на ленту или панель быстрого доступа «Мастер сводных таблиц и диаграмм», в котором есть такая опция.
Сделать это можно следующим образом: «Файл» → «Параметры» → «Панель быстрого доступа» → «Все команды» → «Мастер сводных таблиц и диаграмм» → «Добавить»:
После этого на ленте появится соответствующая иконка, нажатие на которую вызывает того самого мастера:
При щелчке на неё появляется диалоговое окно:
В нём вам необходимо выбрать пункт «В нескольких диапазонах консолидации» и нажать «Далее». В следующем пункте можно выбрать «Создать одно поле страницы» или «Создать поля страницы». Если вы хотите самостоятельно придумать имя для каждого из источников данных - выберите второй пункт:
В следующем окне добавьте все диапазоны, на основании которых будет строиться сводная, и задайте им наименования:
После этого в последнем диалоговом окне укажите, где будет размещаться отчёт сводной таблицы - на существующем или новом листе:
Отчёт сводной таблицы готов. В фильтре «Страница 1» вы можете выбрать только один из источников данных, если это необходимо:
В данном примере в столбце A есть несколько текстовых строк, и наша задача - выяснить, сколько раз в каждой из них встречается искомый текст, расположенный в ячейке E1:
Для решения этой задачи можно воспользоваться сложной формулой, состоящей из следующих функций:
Чтобы найти вхождение определённой текстовой строки в другую, нужно удалить все её вхождения в исходную и сравнить длину полученной строки с исходной:
ДЛСТР(“Тариф МТС Супер МТС”) – ДЛСТР(“Тариф Супер”) = 6
А затем разделить эту разницу на длину той строки, которую мы искали:
6 / ДЛСТР (“МТС”) = 2
Именно два раза строка «МТС» входит в исходную.
Осталось записать этот алгоритм на языке формул (обозначим «текстом» тот текст, в котором мы ищем вхождения, а «искомым» - тот, число вхождений которого нас интересует):
=(ДЛСТР(текст )-ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН(текст );ПРОПИСН(искомый );“”)))/ДЛСТР(искомый )
В нашем примере формула выглядит следующим образом:
=(ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН(A2);ПРОПИСН($E$1);“”)))/ДЛСТР($E$1)
