Звіт з 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).

Є кілька моментів, на які я хотів би звернути вашу увагу.

(1) На листі шаблону найправіша колонка - дуже вузька (буквально пара пікселів). Вона там не так просто. Якщо її не додати та вставляти графіки та текстові поля "притягуючи" їх до краю осередків, то ті з них, що знаходяться праворуч, своїм кордоном (навіть якщо вона прозора) будуть "залазити" на сусідню сторінку, змушуючи Excel друкувати порожню сторінку.

(2) "Притягуйте" текстові поля та графіки до країв осередків. Тоді всі вони будуть знаходитися строго вертикально один під одним. Розмір графіка та паралельного з ним поля можна буде змінювати одночасно, розтягуючи рядок, до якого вони прив'язані. Щоб "прив'язати" розмір об'єкта до краю комірки, в момент зміни розміру цього об'єкта натисніть на Altна клавіатурі. До речі, якщо натиснути Shift, то його розміри змінюватимуться строго в одному напрямку - вертикально або горизонтально.

Післямова

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

Дані аркуша Excel повинні бути у форматі списку із заголовком стовпця у першому рядку. Інші рядки повинні містити аналогічні об'єкти в одному стовпці, без порожніх рядків та стовпців усередині діапазону даних.

У вікні відкритого листа виділіть будь-яку комірку в діапазоні даних.
Перейдіть до вкладки «Вставка» та у групі «Таблиці» розкрийте меню кнопки «Зведена таблиця».
У списку команд виберіть «Зведена таблиця» (мал. 5.59).
У вікні «Створення зведеної таблиці» (мал. 5.60) у графі «Таблиця або діапазон» відобразиться адреса вибраного діапазону даних, що аналізуються.

Якщо необхідно змінити діапазон, то виділіть на аркуші новий діапазон, не закриваючи вікно «Створення зведеної таблиці». Діапазон осередків, який знаходиться на іншому аркуші книги або в іншій книзі, вводиться наступним чином: ([ім'яки]ім'яліста!діапазон) .

У групі "Вкажіть, куди слід помістити звіт зведеної таблиці" виберіть пункт:
- "На новий аркуш" – для розміщення звіту зведеної таблиці на додатковому аркуші в книзі.
- "На існуючий лист" - для розміщення звіту зведеної таблиці в тому ж аркуші, де знаходяться і вихідні дані.

При виборі даного розташування необхідно в пункті «Діапазон» самостійно вказати на аркуші осередок для звіту.

Закрийте вікно кнопкою "ОК".
На аркуші (новому чи існуючому) з'явиться порожня зведена таблиця у вигляді контуру макета, а праворуч аркуша відкриється область «Список полів зведеної таблиці» для створення звіту (рис. 5.61). У ньому можна вставляти в зведену таблицюнові поля, створювати макет та налаштовувати звіт.

Позначте у вікні "Список полів" імена стовпців для створення зведеної таблиці.

Щоб приховати вікно «Список полів», клацніть на будь-якому вільному осередку аркуша. Або ж на вкладці «Параметри» у групі «Показати або приховати» вимкніть пункт «Список полів».

Звітність перед керівництвом та власниками компанії — важлива складова у роботі планово-економічного відділу. Звіти мають бути зрозумілими та лаконічними, а контрольні показники необхідно подавати у докладних аналітиках. Якщо потрібно підготувати піврічну звітність з урахуванням таких вимог, рекомендуємо ознайомитися з матеріалом цієї статті. Ви дізнаєтеся, як у Excel зробити звіти за півріччя у графічній формі, виділити у них ключові показники.

ПІДГОТОВКА ПІВРІЧНОЇ ЗВІТНОСТІ В EXCEL

Під час підготовки піврічної звітності фахівці зіштовхуються з наступною проблематикою:

велика кількість позицій у звіті;
об'ємні звіти - кожен показник потрібно подати у помісячній динаміці;
розкид значень показників, різний порядок цифр.

Щоб успішно впоратися із зазначеними проблемами, підготувати для керівництва чіткі та зрозумілі звіти за півріччя, пропонуємо використати графічну форму. Для об'ємних таблиць, докладних аналітик інформативно застосувати спарклайни та умовне форматування, для компактних таблиць та вибраних ключових показників оптимально використовувати діаграми.

Спарклайни вExcel

У піврічному звіті прийнято демонструвати помісячну динаміку показників. Йдеться такі показники, як виручка, обсяг реалізації, собівартість, обсяг закупівель і прибутку, рентабельність. Перелік номенклатури практично завжди широкий. У такому випадку зручно безпосередньо в таблицю з показниками вставити графіки, що дозволить оцінити як окремі показники, а й їх динаміку, зіставити результати. Для зазначеної групи показників доцільно застосувати спарклайни(Рис. 1).

Візуалізуємо дані табл. 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 півріччя (факт)" до здачі керівництва за допомогою функцій умовного форматування:

Виділяємо в таблиці діапазон, інформацію якого потрібно подати більш наочно (у прикладі - стовпці січень-березень). Задаємо: Головна → Умовне форматування → Колірні шкали. Виберемо інформативну колірну шкалу "зелений-білий" (рис. 4). Користувачі можуть вибрати шкалу кольору на власний розсуд.

Високі витрати пофарбувалися в зелений колір, що привертає увагу:

витрати на фонд оплати праці з відрахуваннями - 250, 240 та 236 тис. руб.;
представницькі витрати у березні - 300 тис. руб.

Чим вище сума витрат, тим більш насичений колір у осередку.

Доповнюємо табл. 2 візуалізацією підсумкових показників за статтями витрат. Виділимо цифри у стовпці факту за півріччя та виконаємо дії: Головна → Умовне форматування → Набір піктограм → Інші правила. Зробимо індивідуальні налаштування (мал. 5).

Вибираємо «Форматувати всі осередки на підставі їхніх значень», а потім відповідний «Значок». У нашому випадку це гістограма. Визначаємо «Значення» та «Тип»:

заповнена гістограма (чотири поділи) - для значень понад 500 тис. руб.;
гістограми з трьома поділами - від 100 до 500 тис. руб.;
гістограма з одним розподілом - всі значення менше 100 тис. руб.

Параметри економіст задає виходячи з їхнього розкиду, середніх значень та правил, прийнятих у компанії.

Бюджет загальнозаводських витрат на відправку керівництву виглядає так (табл. 2).

На прикладі таблиці "Кредиторська заборгованість на кінець періоду" розглянемо ще один спосіб зручно подати показники.

Є. С. Панченко, бізнес-консультант

Матеріал публікується частково. Повністю його можна прочитати у журналі

Дивіться відео до статті:

Типові звіти, що входять до наших модулі готових рішень Business Intelligence (BI) дозволяють відповісти на 99% найпопулярніших запитів користувачів.

Але за бажанням, аналітики або будь-які інші користувачі можуть робити свої звіти в Excel на OLAP-кубах.

Завдяки тому що в основі кожного нашого модуля Бізнес – аналітики (BI) лежить OLAP-куб, підключившись до якого користувачі можуть побудувати будь-який звіт в Excel за допомогою Зведених таблиць.

Також побудовані в Excel звіти на OLAP-кубах можна зберегти, і при повторному відкритті вони автоматично оновлюватимуться актуальними даними. корпоративної системи Бізнес – аналітики (BI) .

Приклади звітів, побудованих на OLAP-кубах:

Опис звітів:

(Процес побудови звітів дивіться у відео до цієї статті)

1. Звіт з продажу в рублях та одиницях:

період: за поточний рік
показники: факт продажу
деталізація: стовпці/квартал, місяць
деталізація: рядки/канали продажів, клієнти, бренди, товари

2. Звіт по структурі продажів у рублях та одиницях (фільтр):

період: за поточний рік
показники: факт продаж, факт продаж у відсотках
деталізація: стовпці/квартал, місяць
деталізація: рядки/канали продажів, бренди

3. Звіт щодо виконання плану продажу в рублях та одиницях (фільтр):

період: за поточний рік
показники: план продаж, факт продажу, виконання плану
деталізація: стовпці/квартал, місяць
деталізація: рядки/канали продажів, клієнти, категорії, товари

4. Звіт з приросту продажів у рублях та одиницях (фільтр):

період
показники
деталізація: рядки/федеральні округи, регіони, категорії, товари

5. Звіт з приросту продажів за часом у рублях та одиницях (фільтр):

період: за поточний рік у порівнянні з попереднім
показники: продажі попереднього року, план продажів, факт продажів, виконання плану, приріст продажів в абсолютному та відносному вираженні до попереднього року
деталізація: рядки/квартал, місяць, категорії, товари

6. Звіт з дебіторської заборгованості:

період: на поточну дату
показники: сума документа, загальна дебіторська заборгованість, поточна дебіторська заборгованість, прострочена дебіторська заборгованість за періодами – до 15 днів, до 30 днів, до 60 днів, до 90 днів, до 180 днів, до 365 днів та більше, структура дебіторської заборгованості
деталізація: строки/канали продажів, контрагент, дата оплати документа, дата документа, документ

7. Звіт динаміка дебіторської заборгованості:

період: за поточний рік
показники: загальна дебіторська заборгованість, поточна дебіторська заборгованість, прострочена дебіторська заборгованість, структура дебіторської заборгованості
деталізація: строки/рік, тиждень, контрагент

8. Звіт платежі прихід за датою документа:

період: за поточний рік
показники: сума документа, сума оплати документа, відхилення з оплати в абсолютному та відносному вираженні
деталізація: строки/рік, місяць, контрагент, вид документа, дата документа, номер документа

9. Звіт платежі прихід за датою платежу:

період: за поточний рік
показники: сума платежу, сума розподілу платежу, відхилення по розподілу в абсолютному та відносному вираженні
деталізація: строки/рік, місяць, контрагент, вид платежу, дата платежу, номер платежу

10. Звіт щодо товарних запасів:

період: на поточну дату
показники: залишок товару, залишковий термін придатності у відсотках (по DSO), залишок товару за термінами придатності — Термін 0%, Термін 10-30%, Термін 40-60%, Термін 70-90%, Термін 99%
деталізація: рядки/категорія, товар, статус, склад, дата документа приходу

Привіт всім! Сьогоднішній матеріал для тих, хто продовжує освоювати роботу з програмами-додатками, і не знає, як зробити зведену таблицю в Excel.

Створивши загальну таблицю, у якомусь із текстових документів, можна здійснити її аналіз, зробивши в Excel зведені таблиці

Створення зведеної Ексель таблиці вимагає дотримання певних умов:

Дані вписуються в таблицю, де є стовпці та списки з назвами.
Відсутність незаповнених форм.
Відсутність прихованих об'єктів.

Як зробити зведену таблицю в excel: покрокова інструкція

Для створення зведеної таблиці необхідно:

Створився порожній лист, де видно списки областей та полів. Заголовки стали полями у нашій новій таблиці. Зведена таблиця формуватиметься шляхом перетягування полів.

Помічатись вони будуть галочкою, і для зручності аналізу ви їх мінятимете місцями в табличних областях.

Я вирішив, що аналіз даних робитиму через фільтр по продавцях, щоб було видно ким і на яку суму щомісяця було продано, і який саме товар.

Вибираємо конкретного продавця. Затискаємо мишку та переносимо поле «Продавець» у «Фільтр звіту». Нове поле відзначається галочкою, і вигляд таблиці дещо змінюється.

Категорію "Товари" ми поставимо у вигляді рядків. У «Назви рядків» ми переносимо потрібне нам поле.

Для відображення списку, що випадає, має значення, в якій послідовності ми вказуємо найменування. Якщо спочатку в рядках робимо вибір на користь товару, а потім вказуємо ціну, то товари якраз і будуть списками, що випадають, і навпаки.

Стовпець «Одиниці», будучи в головній таблиці, відображав кількість товару, проданого певним продавцем за конкретною ціною.

Для відображення продажів, наприклад, кожного місяця, потрібно поле «Дата» поставити на місце «Назви стовпців». Виберіть "Групувати", натиснувши на дату.

Вказуємо періоди дати та крок. Підтверджуємо вибір.

Бачимо таку таблицю.

Зробимо перенесення поля "Сума" до області "Значення".

Стало видно відображення чисел, а нам потрібний саме числовий формат

Для виправлення, виділимо осередки, викликавши вікно мишкою, оберемо «Числовий формат».

Числовий формат ми вибираємо для наступного вікна та відзначаємо «Розділювач груп розрядів». Підтверджуємо кнопкою "ОК".

Оформлення зведеної таблиці

Якщо ми поставимо галочку, яка підтверджує виділення одразу кількох об'єктів, то зможемо обробляти дані одразу за декількома продавцями.

Застосування фільтра можливе для стовпців та рядків. Поставивши галочку на одному з різновидів товару, можна дізнатися, скільки його реалізовано одним чи кількома продавцями.

Окремо налаштовуються параметри поля. На прикладі ми бачимо, що певний продавець Рома у конкретний місяць продав сорочок на конкретну суму. Натисканням мишки ми у рядку «Сума по полю…» викликаємо меню та вибираємо «Параметри полів значень».

Далі для зведення даних у полі вибираємо «Кількість». Підтверджуємо вибір.

Подивіться на таблицю. По ній чітко видно, що в один із місяців продавець продав сорочки в кількості 2-х штук.

Тепер міняємо таблицю та робимо так, щоб фільтр спрацьовував за місяцями. Поле «Дата» ми переносимо у «Фільтр звіту», а де «Назви стовпців», буде «Продавець». Таблиця відображає весь період продажу або за конкретний місяць.

Виділення осередків у зведеній таблиці призведе до появи такої вкладки як "Робота зі зведеними таблицями", а в ній будуть ще дві вкладки "Параметри" та "Конструктор".

Насправді, розповідати про налаштування зведених таблиць можна ще дуже довго. Проводьте зміни під свій смак, домагаючись зручного для вас користування. Не бійтеся натискати та експериментувати. Будь-яку дію ви завжди зможете змінити натисканням клавіш Ctrl+Z.

Сподіваюся, ви засвоїли весь матеріал і тепер знаєте, як зробити зведену таблицю в excel.