отчет в ексел. Как направих управленско счетоводство в Excel

Тази статия е малко остаряла. Предлагам ви да го разгледате нова версия, което включва нов опит, придобит за Миналата годинаработа. Нова статия.

На работа всеки ден виждаме много доклади, които изглеждат детски или с лошо качество. За да изглеждат вашите отчети професионално, ви предлагам един от вариантите за изготвяне на такива. Първоначално беше разработен за анализ на финансовите отчети на голямо предприятие - излязоха около 30 страници текст, диаграми и схеми. Ръководството беше много доволно от доклада.

Предлагам ви да разгледате примерен такъв отчет в прикачения файл на този линк. Как да го приготвите в Microsoft Excelбез да се прибягва до скъпо софтуер, като Adobe InDesign.

За да направим това, трябва да използваме няколко неща: (1) изгледа в режим "Оформление на страницата" (можете да го включите, като щракнете върху лентата изгледна бутона Оформление на страницаот раздел изгледи на работна книга, за този изглед и как да оптимизирате отчет за печат вижте тази статия), (2) научен подход.

Ако всичко е ясно с първата точка, тогава втората изисква известно обяснение.

Общи съвети

Изготвянето на доклад трябва да започне не с обработка на информация и не с очертания, а от съвсем друга страна - а именно с разработването на шаблон за представяне на доклад. Докладът ще изглежда професионално само ако е от един и същи тип във всички раздели. Ето защо ви трябва шаблон, който ще копирате като нов лист за нов раздел и ще го попълните.

Шаблонът не трябва да е крещящ. Предлагам ви да използвате цветовете на стандартната тема на Microsoft Excel 2007-2010. Те са много удобни за потребителското възприемане на вашия отчет: независимо дали е вашият мениджър, финансов директор, изпълнителен директор, акционери или клиенти на компанията. Например използвах синьо. Зеленото също изглежда достатъчно добре. Ако има някакъв раздел, към който искате да се обърнете Специално внимание, можете да го направите в червено, основното е да не изберете електрическо червено, а удобно червено.

Градиентните диаграми се възприемат много положително от ръководството. Обикновено за управление това е първокласно, но не изисква почти никакви усилия от вас.

Такъв доклад (особено когато се състои от петдесет страници) ще изглежда много добре отпечатан на цветен принтер от двете страни и подвързан. Нашият контрольор разполагаше с дузина от тези доклади, които раздаваше на заинтересованите лица на срещи.

Ако възнамерявате да го разпространявате по електронен път, ТРЯБВА да го преведете на pdf формат. Той е много по-четлив, въпреки че губи някои функции. Според мен най-удобният начин да го конвертирате в този формат е да не използвате pdf принтери, а вградената функционалност на Microsoft Excel 2007-2010 (Файл => Запиши като => изберете pdf формат).

Има няколко момента, на които бих искал да обърна внимание.

(1) В листа с шаблони най-дясната колона е много тясна (буквално няколко пиксела). Тя не е просто там. Ако не го добавите и вмъкнете графични и текстови полета, като ги "дърпате" към ръба на клетките, тогава тези отдясно с границата си (дори и да е прозрачна) ще се "изкачат" на съседната страница, принуждавайки Excel да отпечатайте празна страница.

(2) „Издърпване“ на текстови полета и графики към краищата на клетката. Тогава всички те ще бъдат строго вертикално един под друг. Размерът на диаграмата и успоредното на нея поле могат да се променят едновременно чрез разтягане на линията, към която са прикрепени. За да "прикрепите" размера на обект към ръба на клетка, докато преоразмерявате този обект, щракнете върху алтна клавиатурата. Между другото, ако натиснете Shift, тогава размерите му ще се променят строго в една посока - вертикално или хоризонтално.

Послеслов

Разработването на този доклад до голяма степен е вдъхновено от формата на публикациите, изготвени от KPMG. Можете да ги видите на официалния им сайт. Не знам в какво ги правят, но имат най-актуалните и професионално изглеждащи отчети от всички G4.

Данните в работния лист на Excel трябва да са във формат на списък със заглавието на колоната на първия ред. Останалите редове трябва да съдържат подобни обекти в една колона, без празни редове или колони в диапазона от данни.

В прозореца на отворения лист изберете произволна клетка в диапазона от данни.
Отидете в раздела „Вмъкване“ и в групата „Таблици“ разгънете менюто на бутона „Обърна таблица“.
В списъка с команди изберете елемента "Обърната таблица" (фиг. 5.59).
В прозореца "Създаване на обобщена таблица" (фиг. 5.60), в колоната "Таблица или диапазон", ще се покаже адресът на избрания диапазон от анализирани данни.

Ако трябва да промените диапазона, изберете нов диапазон на листа, без да затваряте прозореца „Създаване на обобщена таблица“. Диапазон от клетки, който е на друг лист от работна книга или в друга работна книга, се въвежда, както следва: ([име на книга]име на лист! диапазон) .

В групата „Посочете къде да поставите отчета с обобщена таблица“ изберете:
- „На нов лист“ – за поставяне на отчета с обобщена таблица на допълнителен лист в книгата.
- „Към съществуващ лист“ – за да поставите отчета с обобщена таблица в същия лист като изходните данни.

Когато избирате това разположение, трябва самостоятелно да посочите клетката за отчета в елемента "Диапазон" на листа.

Затворете прозореца с бутона OK.
На листа (нова или съществуваща) ще се появи празна обобщена таблица под формата на контур на оформление и областта „Списък с полета на обобщена таблица“ ще се отвори вдясно на листа, за да създадете отчет (фиг. 5.61). Може да се вмъкне в осева таблицанови полета, създайте оформление и персонализирайте отчета.

Проверете в прозореца "Списък с полета" имената на колоните, за да създадете обобщена таблица.

За да скриете прозореца "Списък с полета", щракнете върху която и да е свободна клетка в работния лист. Като алтернатива, в раздела Опции, в групата Показване/Скриване, изключете Списък с полета.

Отчитането пред ръководството и собствениците на фирмата е важен компонент в работата на планово-икономическия отдел. Докладите трябва да бъдат ясни и кратки, а показателите трябва да бъдат представени в подробен анализ. Ако трябва да изготвите шестмесечни отчети, като вземете предвид такива изисквания, препоръчваме ви да се запознаете с материала в тази статия. Ще научите как да правите шестмесечни отчети в Excel в графичен вид, да подчертавате ключови показатели в тях.

ИЗГОТВЯНЕ НА ПОЛУГОДИШНА ОТЧЕТНОСТ В EXCEL

При изготвянето на шестмесечни отчети специалистите се сблъскват със следните проблеми:

голям брой позиции в отчета;
обемни отчети - всеки показател трябва да бъде представен в месечна динамика;
разсейване на стойностите на индикатора, различен ред на числата.

За да се справите успешно с тези проблеми, да изготвите ясни и разбираеми шестмесечни отчети за ръководството, предлагаме да използвате графична форма. За обемни таблици, подробни анализи е информативно да се прилагат искрящи линии и условно форматиране; за компактни таблици и избрани ключови индикатори е оптимално да се използват диаграми.

Блестящи линии впревъзходен

В полугодишния отчет е обичайно да се показва месечната динамика на показателите. Говорим за такива показатели като приходи, обем на продажбите, себестойност, обем на покупките и печалбите, рентабилност. Списъкът от номенклатура на практика винаги е широк. В този случай е удобно да вмъкнете графики директно в таблицата с индикатори, което ще ви позволи да оцените не само отделните индикатори, но и тяхната динамика и да сравните резултатите. За тази група показатели е препоръчително да се прилагат искрящи линии(Фиг. 1).

Ние визуализираме данните в таблицата. 1 „Анализирайте продажбите със Sparklines“, като направите следното:

В таблица 1 създаваме колона или ред, за да поберем следната графична информация:

„Динамика за първото полугодие”;
„Анализ на приходите по продуктови групи”;
Анализ на рентабилността по продуктови групи.

2. Изпълняваме командата: Поставете → Блестящи линии → График(виж фиг. 1). В прозореца" Създаване на искри» задайте обхвата на данните и обхвата на местоположението (фиг. 2).

3. Копирайте графичния елемент до желания брой клетки. Ако е необходимо, редактирайте диаграмата с помощта на конструктора (фиг. 3). Например, маркираме знака на максималните точки или маркери с флаг, променяме цвета на графиката.

4. За „Анализ на приходите по продуктови групи“ и „Анализ на рентабилността по продуктови групи“ използваме тип искряща линия „Колона“.

Раздел Допълнен с искрящи линии. 1 е наличен в Услугата за формуляри (код за достъп на стр. 119). От таблицата може да се види, че само приходите от багрила намаляват, има низходяща тенденция в диаграмата на искра. За останалите продукти и за компанията като цяло приходите растат (колона „Динамика за първото полугодие“).

Редът „Анализ на приходите по групи продукти“ показва, че компанията получава най-голям размер на приходите от продажбата на стандартни гладки тухли - 4003,9 от 42 068,82 хиляди рубли.

Колоната sprakline в реда „Анализ на рентабилността по групи продукти“ показва, че текстурираната тухла има максимална рентабилност - от 40 до 42,9%. Скокът в доходността се случи през април (може да се види на графиката в колоната "Динамика за първото полугодие"). През април е регистрирана отрицателна доходност при стандартните тухли и намалена (11,1%) при гладките тухли с високо съдържание на пигменти.

Важен момент:в отчета трябва да посочите причината за визуализираните промени. Възможна причинапромени в разглеждания случай: в началото на сезона на продажбите (пролет) бяха предоставени промоционални отстъпки за стимулиране на обема на продажбите и привличане на нови клиенти.

ЗАБЕЛЕЖКА

Когато копирате таблица с искрящи линии от Excel в word документграфиките може да не се виждат. В този случай използвайте вложката във формата на фигурата.

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

Когато става въпрос за разходи, управлението често изисква подробен анализ. Това могат да бъдат разходни бюджети в различни области, подробен отчет за фонда за заплати, плащания към доставчици. Докладите трябва да подчертават важна информацияи нестандартни стойности. Възприемането на данните ще се подобри, ако използвате хистограми, цветови скали, набори от икони.

Да подготвим маса. 2 „Бюджет на общите фабрични разходи за първата половина на годината (действителен)“, който трябва да бъде предаден на ръководството с помощта на функции за условно форматиране:

Избираме диапазона в таблицата, чиято информация трябва да бъде представена по-ясно (в примера колоните януари-март). Ние задаваме: У дома → Условно форматиране → Цветни везни. Да изберем информативна цветова скала "зелено-бяло" (фиг. 4). Потребителите могат да избират цветовата гама по свой избор.

Високите разходи станаха привлекателно зелени:

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

Колкото по-голям е разходът, толкова по-наситен е цветът в клетката.

Допълваме таблицата. 2 визуализация на крайни показатели по разходни позиции. Нека изберем числата в колоната факти за полугодието и изпълним следните действия: У дома → Условно форматиране → Комплекти икони → Други правила. Да направим индивидуални настройки (фиг. 5).

Изберете „Форматиране на всички клетки въз основа на техните стойности“ и след това съответната „Икона“. В нашия случай това са хистограми. Дефинирайте „Стойност“ и „Тип“:

попълнена хистограма (четири деления) - за стойности над 500 хиляди рубли;
хистограми с три деления - от 100 до 500 000 рубли;
хистограма с едно разделение - всички стойности под 100 хиляди рубли.

Икономистът задава параметрите въз основа на тяхната дисперсия, средни стойности и правилата, приети в компанията.

Бюджетът за общи фабрични разходи, който трябва да бъде изпратен на ръководството, е както следва (Таблица 2).

Използвайки примера на таблицата „Задължения в края на периода“, ще разгледаме друг начин за удобно представяне на показатели.

Е. С. Панченко, бизнес консултант

Материалът е публикуван частично. Можете да го прочетете изцяло в списанието.

Вижте видеото към статията:

Стандартни отчети, включени в нашия модули за бизнес разузнаване (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 изисква да бъдат изпълнени определени условия:

Данните се вписват в таблицата, където има колони и списъци с имена.
Без празни формуляри.
Няма скрити предмети.

Как да направите обобщена таблица в Excel: инструкции стъпка по стъпка

За да създадете обобщена таблица, трябва:

Създаден е празен лист, където можете да видите списъците с области и полета. Заглавията са станали полета в нашата нова таблица. Обобщената таблица ще се формира чрез плъзгане на полетата.

Те ще бъдат маркирани с отметка и за удобство на анализа ще ги размените в областите на таблицата.

Реших да направя анализ на данните чрез филтър по продавачи, за да е ясно от кого и за каква сума всеки месец се продава и кой продукт.

Избираме конкретен продавач. Задържаме мишката и преместваме полето "Продавач" във "Филтър за отчет". Новото поле е маркирано с отметка и изгледът на таблицата се променя леко.

Ще поставим категорията "Продукти" под формата на низове. В "Имена на редове" прехвърляме полето, от което се нуждаем.

За показване на падащ списък има значение в какъв ред ще посочим името. Ако първоначално в редовете направим избор в полза на продукта и след това посочим цената, тогава продуктите ще бъдат просто падащи списъци и обратно.

Колоната "Единици", намираща се в основната таблица, показва количеството стоки, продадени от определен продавач на определена цена.

За да показвате продажбите, например, за всеки месец, трябва да поставите полето "Дата" на мястото на "Имена на колони". Изберете командата "Групиране", като щракнете върху датата.

Посочете периоди от дати и стъпка. Потвърждаваме избора.

Виждаме такава таблица.

Нека преместим полето "Сума" в областта "Стойности".

Показването на числата стана видимо и имаме нужда точно от числовия формат

За да коригирате, изберете клетките, като извикате прозореца с мишката, изберете "Числен формат".

Избираме цифровия формат за следващия прозорец и поставяме отметка на "Разделител на цифри". Потвърдете с бутона "ОК".

Дизайн на обобщена таблица

Ако поставим отметка в квадратчето, което потвърждава избора на няколко обекта наведнъж, ще можем да обработваме данни за няколко продавача наведнъж.

Прилагането на филтър е възможно за колони и редове. Отбелязвайки една от разновидностите на продукта, можете да разберете какво количество от него се продава от един или повече продавачи.

Параметрите на полето също се конфигурират отделно. В примера виждаме, че определен ромски продавач е продал ризи за определена сума през определен месец. Щраквайки с мишката, ние в реда „Сума по поле ...“ извикваме менюто и избираме „Параметри на стойностни полета“.

След това, за да обобщите данните в полето, изберете "Количество". Потвърждаваме избора.

Погледнете масата. От него ясно се вижда, че в един от месеците продавачът е продал ризи в количество от 2 бр.

Сега променяме таблицата и правим филтъра да работи по месеци. Прехвърляме полето „Дата“ във „Филтър за отчет“ и там, където е „Имена на колони“, ще има „Продавач“. Таблицата показва целия период на продажбите или за определен месец.

Избирането на клетки в обобщена таблица ще покаже раздел, наречен Инструменти за обобщена таблица с още два раздела, Опции и Дизайн.

Всъщност можете да говорите за настройките на обобщените таблици много дълго време. Направете промени по ваш вкус, постигайки удобна употреба за вас. Не се страхувайте да настоявате и да експериментирате. Винаги можете да промените всяко действие, като натиснете клавишната комбинация Ctrl + Z.

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