Бюджетирование и управленческий учет в Excel: Тренинг

Чему конкретно вы научитесь за 2 дня тернинга?
Программа курса состоит из 7 последовательных блоков.

1. Организация, ввод и хранение исходных данных
Как организовать данные в файле, чтобы ничего не "ползло", не исчезало и не разваливалось. Быстрый ввод и контроль вводимых данных.
Ни для кого не секрет, что при сборе данных и составлении отчётов пользователям Excel приходится проделывать множество "ручных" операций: скопировать данные из одного файла и вставить их в другой или изменить формулы, если что-то поменялось в исходных данных.
Однако 70% ручной работы возникает не из-за того, что данных много или они слишком разнородные, а из-за того, что они неправильно организованы.
И количество ручной работы можно сократить минимум наполовину, просто построив правильную структуру данных.

Из этого раздела вы узнаете:

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

Практикум "Создание файлов для ввода и хранения исходных данных":

1. Правильная работа с диапазоном данных.
2. Как в десятки раз ускорить и облегчить обработку информации при помощи правильно построенной структуры данных.
3. Какие типы ссылок бывают в Excel и как правильно с ними работать.
4. Использование комбинированных ссылок для быстрого построения отчетов.
5. Что такое имена в Excel, зачем они нужны и как с ними работать.
6. Создание файлов и таблиц для быстрого и правильного ввода данных.

2. Базовые технологии компоновки данных и создания отчетов
Несколько стандартных функций Excel, которые в разы упрощают обработку информации и на 70% закрывают задачи составления управленческой отчетности.
В прошлом модуле мы с вами научились создавать формы для ввода информации. Но собрать информацию - это только начало дела. Исходная информация сама по себе совершенно неинтересна.
Поэтому мы должны научиться эти данные компоновать в удобном нам виде и создавать на основании исходных данных понятные управленческие отчеты.
При этом нужно максимально исключить ручной труд и добиться, чтобы данные обрабатывались либо полностью в автоматическом режиме, либо с минимальным участием человека.
Поэтому в этом разделе мы изучим несколько самых важных и нужных функций, которые понадобятся для правильной обработки данных, составления отчетов и перекомпоновки исходной информации.
Уже при помощи всего нескольких функций вы можете создать работоспособную систему управления финансами, которая будет не очень красивой и не очень удобной, но будет стабильно и предсказуемо работать.

Из этого раздела вы узнаете:

• Что такое пирамида управленческой информации и как понимание принципов построения пирамиды помогает в автоматической обработке данных.
• Главный принцип автоматической обработки данных, который лежит в основе правильной автоматизации.
• Что такое "статические" и "динамические" функции и почему система управления финансами, построенная на динамических функциях стабильно работает, а система, построенная на статических функциях - постоянно "разваливается".
• Важнейшие функции Excel для системы управления финансами. Сходства и различия функций.
• Какие функции, где, как и для чего их использовать.

Практикум "Создание простых управленческих отчетов":

1. Как автоматически выбрать и просуммировать нужные данные в таблице.
2. Как при помощи функции СуммЕсли скомпоновать данные из большого массива в короткий и понятный отчет.
3. Как подставить текстовые данные из одной таблицы в другую при помощи функции ВПР.
4. Автоматическая подстановка данных из других таблиц при помощи функций ВПР и СуммЕсли.
5. Какую функцию правильнее использовать для подстановки данных - ВПР или СуммЕсли.
6. Получение данных по двум измерениям при помощи функций Индекс и Поиск Позиции.
7. Сравнительный анализ работы изученных функций.

3. Комбинирование функций и продвинутые управленческие отчеты
Комбинирование функций. Использование комбинированных функций для обработки сложных структур данных и создания мощных автообновляемых отчетов, которые при изменении исходных данных будут сами себя дополнять и изменять.
Во втором модуле мы изучили основные функции, которые потребуются нам для обработки информации и создания управленческой отчетности.
Однако, если использовать изученные ранее функции по отдельности, можно будет делать только простые незатейливые отчеты. Это лучше, чем ничего, но совершенно не то, что хотелось бы.
Поэтому для создания по-настоящему мощных, гибких и стабильных отчетов нам нужно будет научиться комбинировать функции между собой и научиться использовать в одной ячейке сразу несколько функций.
Это именно тот случай, когда 1+1 равно не двум, а, как минимум, пяти и даже десяти.
Будет изучено буквально 5–6 дополнительных функций, и научимся комбинировать их с функциями, изученными в предыдущем разделе.
В результате функционал изученных ранее функций расширится в разы и взлетит просто на недосягаемую высоту.

Из этого раздела вы узнаете:

• Как правильно скомбинировать несколько функций, чтобы они стабильно и предсказуемо работали.
• Как создавать отчёты, в которых никогда ничего не "ползет", не разваливается и не "слетает".
• Как автоматически сформировать отчет из данных, которые в обычных случаях приходится выбирать исключительно вручную.
• Как создать многометровый отчет, в котором выводимые строки, столбцы и данные меняются одним кликом мыши.
• Как создать автообновляемый отчет, которые, в случае изменения исходных данных, будут сами себя дополнять и перестраивать без перенастройки формул.
• Как создавать отчеты, который при добавлении в книгу новых листов, автоматически добавляет эти данные в отчет без перенастройки формул.

Практикум "Комбинирование функций и продвинутые отчёты":

1. Как существенно расширить функционал стандартных формул, совместив несколько формул в одной функции.
2. Как подставить нужные данные в таблицу, когда в качестве критерия выступает не текст, а число.
3. Автоматическая перекомпоновка данных и структурирование "неудобных" таблиц.
4. Автоматическая компоновка сложных данных.
5. Двухмерный самособирающийся отчет.
6. Как превратить текстовые данные в ссылку на ячейку или диапазон и зачем это нужно.
7. Как создать мощный, гибкий и легко настраиваемый многомерный "самособирающийся" отчет.
8. Как создать несколько одинаковых имён в одной книге Excel.
9. Составление отчета по данным с разных листов при помощи двойных ссылок.
10. Автоматическое подключение новых данных к отчету при помощи двойных ссылок.
11. Как за несколько минут создать удобный и понятный отчет из совершенно запутанных исходных данных.

4. Настройка системы автоматического обмена данными между множеством файлов
Настройка автоматического обмена между множеством файлов и создание практически неразрушимой системы ссылок. Создание многопользовательских многофайловых систем.
При организации системы бюджетирования и управленческого учета в Excel часто получается достаточно сложная система, состоящая из множества взаимосвязанных файлов.
Поэтому нередко происходят ситуации, когда один сотрудник вносит изменения в свои файлы, а в остальных файлах "летят" ссылки или "ползут" связанные с этим файлом данные.
Как правило, в многофайловых мультипользовательских системах ошибки возникают часто, а искать их тяжело.
Тем не менее, есть несколько довольно простых правил организации системы файлов и ссылок, которые позволяют создать по-настоящему надёжную и стабильную систему файлов для многопользовательской работы. Увы, этих правил почти никто не знает…
В процессе изучения этого модуля будет создана систему файлов, которая позволит обмениваться данными между множеством файлов в полностью автоматическом режиме и будет лишена своих "традиционных" недостатков:

1. Все файлы будут автоматически обмениваться данными в режиме реального времени.
2. Данные не будут "исчезать", "ползти" или теряться по дороге из одного файла в другой.
3. Даже очень существенное изменение исходных файлов не будет приводить к появлению ошибок и разрушению связанных файлов.

Из этого раздела вы узнаете:

• Какие типы ссылок бывают в Excel и какой из них использовать для связывания файлов.
• Как правильно связывать файлы, чтобы они обменивались информацией в режиме реального времени, ссылки не слетали, а данные не терялись.
• Почему предлагаемая на тренинге система связывания файлов на практике оказывается в десятки раз надёжнее знакомых и привычных способов связывания файлов.
• Как добиться, чтобы при редактировании одного файла (даже очень существенном), остальные файлы сохраняли работоспособность и продолжали получать правильные данные.
• Что такое прямые и обратные связи, какие из них, как и где использовать.
• Как "таскать" между файлами массивы в сотни тысяч строк без "глюков" и "зависаний".
• Как наладить полностью автоматический обмен данными между множеством файлов в режиме реального времени и собирать итоговые отчёты из множества файлов.
• 10 правил создания системы файлов, которые позволят вам создать неразрушимую систему файлов и ссылок.

Практикум "Настройка системы автоматического обмена данными между множеством файлов":

1. Как настроить стабильные и надежные ссылки внутри одного файла.
2. Как правильно связать между собой ячейки, которые находятся в разных файлах.
3. Как настроить файл, чтобы обновление данных из внешних файлов всегда выполнялось в автоматическом режиме.
4. Правильная технология связывания файлов и передача массивов данных между файлами.
5. Как создать стабильную и надежную систему мгновенного обмена большими массивами данных между множеством файлов.

5. Быстрая и лёгкая обработка больших массивов данных
Как легко и непринужденно обрабатывать таблицы, которые содержат десятки столбцов и сотни тысяч строк данных.
Когда у компании появляются большие объемы данных, требующие обработки - объем работы, которую приходится выполнять сотрудникам, как правило, резко увеличивается.
Очень много времени уходит на то, чтобы, например, выбрать нужные данные в одном файле, а потом скопировать их в другой. А если исходный файл изменился, то всю работу, увы, приходится проделывать заново.
Или, например, нужные данные содержатся в разных файлах (пускай это будут данные из разных географически удаленных филиалов) и для того, чтобы получить общую картинку по предприятию - приходится вручную копировать эти данные в общий файл, где они будут обрабатываться.
Такого рода работа не просто скучная и надоедливая, - это преступное разбазаривание времени высокооплачиваемых сотрудников.
Однако все эти задачи очень легко автоматизировать.
Вы научитесь эффективно работать с таблицами, которые содержат огромные объемы данных - сотни столбцов и тысячи строк. И будете делать это быстро и непринужденно.

Из этого раздела вы узнаете:

• Как за 2 минуты на основании массива данных из десятков столбцов и нескольких тысяч строк создать информативный и понятный отчёт, который можно дополнить или изменить буквально двумя-тремя щелчками мыши.
• Как автоматически выбрать из большой таблицы данные, отвечающие нескольким определённым критериям и поместить их в нужное вам место (на отдельный лист или в отдельный файл).
• Как автоматически "разобрать" большую неудобную таблицу на несколько маленьких и удобных, каждая из которых будет содержать только нужные вам данные и может находиться как в этом же, так и в отдельном файле.
• Как за 15 секунд собрать данные с разных листов, содержащих исходные данные (даже если этих листов 100) на один итоговый лист внутри одного файла, если все листы имеют одинаковый формат.
• Как за 2–3 минуты собрать правильный итоговый отчёт из нескольких таблиц, которые содержат разные по содержанию и по размеру диапазоны данных и находятся в разных файлах.
• Как автоматически свести однотипные диапазоны данных, которые находятся в разных файлах, на один лист отдельного файла, чтобы их можно было легко обработать.

Практикум "Работа с большими массивами данных":

1. Как при помощи сводных таблиц за 2 минуты создать понятный, информативный и гибкий отчёт.
2. Как быстро выбрать данные из другого файла по одному или нескольким условиям.
3. Как за несколько секунд собрать данные с нескольких одинаковых листов одного файла.
4. Как автоматически собрать данные из нескольких файлов, когда состав и структура данных не совподают.

6. Защита данных и многопользовательская работа
Как надежно защитить ваши данные, организовать многопользовательскую работу в файле и начать четко отслеживать 0 кто, когда и какие изменения вносил в ваши файлы.
Ваша система бюджетирования и управленческого учета будет работать гораздо надежнее и стабильнее, если защитить ваши файлы.
Конечно, это не защитит её от профессиональных хакеров, но вполне надёжно защитит от неквалифицированных пользователей и неосторожного обращения.

Из этого раздела вы узнаете:

• Как работают механизмы защиты в Excel, от чего защита спасает, а от чего - нет.
• Как настроить защиту ячейки, диапазона, листа, книги и файла.
• Как избежать "самодеятельности" сотрудников при вводе данных и защитить файл от ввода неправильных данных.
• Как правильно организовать и наладить многопользовательскую работу.
• Как установить разным людям разные права и ограничения на работу с файлом.
• Как правильно разграничивать полномочия сотрудников при работе с данными и наилучшим образом построить защиту файлов.
• Как узнать - кто последний менял файл и как восстановить изменённые данные.
• Как авоматически востановить изменённые данные, если кто-то из сотрудников внес неправильные изменения в файл.

Видеоуроки раздела "Защита данных и многопользовательская работа":

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

7. Быстрое получение и обработка данных из 1С
Как за 3-4 минуты получить и обработать огромный масив данных из 1С или любой другой информационоой системы.
Как выглядит типичный процесс обработки данных из 1С при помощи Excel?
Сотрудник запускает 1С и выводит нужные ему отчет. Потом сохраняет этот отчет в Excel. После этого в полученном файле настраивает формулы и ссылки, которые нужны для обработки данных.
Казалось бы - все не так сложно. Но есть одна загвоздка, которая превращает эту работу в пытку.
Дело в том, что от периода к периоду объем и состав данных, которые вводятся в 1С обязательно меняется. И поэтому в 1С меняется структура отчета.
Если вывести в 1С один и тот же отчет за разные периоды, то, скорее всего, это будет два разных отчета - изменится количество сторок, столбцов, состав (или порядок представления в отчете) статей, контрагентов и т. д. и т. п.
Естественно, что при таком изменении исходны данных - всю работу по настройке ссылок и функций в Excel придётся делать заново.
Поэтому в большенстве компаний традиционно считается, что 1С и Excel несовместимы на генетическом уровне и многие компании давно плюнули на возможность обработки данных из 1С в Excel.
Однако проблема решается при помощи всего двух простых формул.
И вы навсегда забудете про сложности, которые до этого испытывали при обработке данных из 1С в Excel.

Из этого раздела вы узнаете:

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

Практикум "Получение данных из 1С":

1. Как легко получить из 1С любые нужные данные, и за 2–3 минуты трансформировать их в формат, удобный для Excel.
2. Что делать, когда даты и числа выгружаются из 1С в форматах, которые Excel не может обработать.

С какими еще трудностями вы сталкиваетесь:

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

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

• Вы сможете за 2–3 минуты в автоматическом режиме свести данные из нескольких таблиц, даже если формат и содержимое таблиц не совпадает, а сами таблицы находятся в разных файлах.
• Вы научитесь создавать многопользовательские системы файлов, которые не будут рушиться от каждого "чиха" и будут правильно работать, даже если кто-то из пользователей внес серьезные правки в исходные файлы.
• Вы научитесь составлять и решать задачи экономической оптимизации, что позволит вам моментально находить оптимальный вариант решения самых сложных задач. (Например, вы сможете за 5–10 минут составить планы продаж и производства, которые принесут максимальную прибыль в условиях жесткого ограничения ресурсов - денежных, сырьевых, временных, трудовых и т. д.)
• Вы освоите приемы автоматической работы с большими объемами данных, которые избавят вас от множества однообразных операций и сможете за три-четыре минуты строить сложные отчеты по таблицами из сотен тысяч строк.
• Вы изучите множество возможностей Excel, которые позволят вам практически всю скучную и надоедливую работу "перепоручить" компьютеру и позволят исключить множество ошибок, возникающих по вине персонала.
• Вы научитесь делать такие формы ввода данных, которые сотрудники при всём желании не смогут испортить. И сотрудники будут вынуждены вводить информацию так, как нужно вам, а не так, как они сами захотели.
• Вы сможете мгновенно выбирать данные из огромных таблиц по одному или нескольким критериям. Причем сможете делать это внутри одного файла, или автоматически "раскидывать" нужные данные по разным файлам.
• Вы научитесь создавать "самособирающиеся" отчёты, которые при изменении исходных данных смогут менять не только своё содержание, но и форму. Поэтому их никогда не нужно будет менять вручную.
• Вы освоите методы составления простых и удобночитаемых отчётов, которые смогут собирать информацию одновременно из нескольких файлов, и которые при необходимости можно будет быстро проверить и легко расшифровать.
• И еще многое, много другое…