Excel. Простые примеры для автоматизации работы

Оглавление. ПРОЕКТ.

Оглавление. ПРОЕКТ - предварительный вариант.

Жирным шрифтом выделены главы, которые уже опубликованы.

Курсивом выделены главы, которые в процессе подготовки и будут опубликованы в ближайшие дни.

Добавлено новое приложение:

Приложение 4. В этом приложении будет информация для подготовки к собеседованиям с темами экономики и аналитики.

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

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

***
Введение.
Глава 1. Нелепые вопросы, которые всё ещё встречаются.
Глава 2. Работа со сводными таблицами и большим объемом информации.
Глава 3.1. Создание таблицы сложных процентов. Примеры их использования на практике.
Глава 3.2. Пример расчета точки безубыточности.
Глава 4.1. ABC анализ.
Глава 4.2. XYZ анализ.
Глава 5.1. Калькуляция грузоперевозки - общий вид.
Глава 5.2. Пошаговое описание для исходной информации.
Глава 5.3. База данных.
Глава 5.4.1. Наименование статей калькуляции. Заработная плата водителя.
Глава 5.4.2. Наименование статей калькуляции.
Глава 5.4.3.
Глава 5.4.4.
Глава 5.4.5.
Глава 6. Бюджетирование (год, квартал, месяц, неделя). План/факт, аналитическая часть.
Глава 6.1. Увязки для платёжного календаря.
Глава 6.2. Бюджет доходов и расходов.
Глава 6.3. Бюджет движения денежных средств.
Глава 6.4. Управленческий баланс.

Глава 6.5. Анализ плана и фактического исполнения в зависимости от различных факторов.

Глава 6.6. Рассматриваем прямой и косвенный методы анализа движения денежных средств.
Глава 6.7. Нам необходимо объединить несколько таблиц в одну.

Глава 7. Производство. Пример калькуляции есть по ссылке.
Глава 7.1. Расчет материалов в производстве.
Глава 7.2. Контроль закупок материалов в производстве.
Глава 7.3. Формирование базы отделом снабжения в части определения актуальной стоимости сырья и материалов.
Глава 8. Юнит (unit-экономика)
Глава 9. Использование формул на практике.
Глава 9.1. Решение задач методами исследования операций.
Глава 9.1.1. Методы оптимизации.
Глава 9.1.2. Теория вероятностей и математическая статистика.
Глава 9.1.3. Метод Монте - Карло.
Глава 9.1.4. Статистические решения.
Глава 9.1.5. Сетевое планирование.
Глава 9.2. Элементарная математика и логика.
Глава 10. Образцы аналитических таблиц для сравнения экономических показателей по объектам в строительстве.
Глава 11. Сетевые графики для определения сроков поставки сырья.
Глава 11.1. Построение технологических карт.
Глава 11.2.

Глава 11.3.
Глава 12. Дашборды.
Глава 13. Документы для тендера.
Глава 13.1. Анализ цен и тарифов.
Глава 13.2. Анализ объёмов работ.

Глава 13.3. Анализ себестоимости и рентабельности товаров и услуг.

Глава 13.4. Трансферное ценообразование.

Глава 13.5. Динамическое ценообразование.

Глава 14. Правила условного форматирования.

Глава 15. PowerPivot.

Глава 16. Power Viev.

Глава 17. Макросы в сводных таблицах.
Глава 18. Библиотеки Python в анализе.

Короткие примеры решения практических задач.

1)Создание листов для плана на год.

Приложение 1. Сочетание клавиш в Excel.

Приложение 2. Условное форматирование.

Приложение 3. Специальная вставка.

Приложение 4. В этом приложении будет информация для подготовки к собеседованиям с темами экономики и аналитики.

1-я тема: Методы цепных подстановок. Может пригодиться для экономистов и аналитиков.

2-я тема: Знание принципов бухгалтерского и управленческого учетов.

Предметный указатель

Введение.

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

Благодарю вас за подписку на эту книгу "Excel. Простые примеры для автоматизации работы"

Excel является одним из самых популярных табличных редакторов. С его помощью мы попытаемся на страницах этой книи рассмотреть вопросы организации плановой и аналитической работы.

Эта книга не является введением в Excel или экономику. Для первичного изучения такого популярного табличного редактора как Excel или экономики написано немало книг.

Содержание данной книги рассчитано на то, что вы уже работаете в Excel и являетесь экономистом, аналитиком, финансистом среднего или высокого уровня.

Эта книга не подходит для тех, кто совершенно не знаком с Excel.

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

***

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

/Но небольшое отступление: если есть возможность, используя возможности 1С работать в конфигураторе программы, то используйте эту возможность. ERP - это то, что экономисты постоянно пытаются создать с помощью Excel. К сожалению, Excel не потянет много информации. Поэтому, если есть возможность перейти на 1C ERP , делайте это. /

***
На данный момент содержание глав пока приблизительное.

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

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

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

Если в вакансии есть такие требования, какие будут указаны ниже, то вы сможете подготовиться к собеседованию и к работе в компании, если из категории Кандидата перейдёте в категорию Сотрудника компании.

Требования, по которым будут ответы:

В части Excel:

- Сложные формулы, сводные таблицы, ВПР, ПСТР, СУММЕСЛИМН, VBA и пр.

В части экономики и аналитики:

- Расчёт заказов на производство;

- Анализ данных для оценки подбора наиболее актуальных товаров;

- Анализ рентабельности выпуска продукции;

- Анализ поставщиков и заказчиков (АВС-анализ);

- Формирование платёжного календаря;

- Анализ управленческой отчётности;

- Формирование финансово-бюджетной модели и отслеживание ее выполнения;

- Поиск оптимальных решений. Система эффективного распределения ограниченных ресурсов;

- Создание базы для плановой и аналитической обработки информации;
- Разработка моделей и методики расчётов плановой себестоимости в разрезе партии/единицы продукции (действующий и новый ассортимент продукции);

- Разработка положений о скидках;

- Плановая калькуляция услуг для компаний, осуществляющих грузоперевозки;

- Технико-экономическое обоснование импорта товаров;
- Построение процесса управления материальной себестоимостью;
- Разработка и реализация модели определения плановой прибыли (плановая себестоимость, плановая прибыль);

- Разработка методики подготовки пакета документов для тендера;

- Бизнес-план;

- Разработка моделей по технологическим и финансовым показателям деятельности компании. Различные варианты сценариев.

- Анализ сильных и слабых сторон компании, оценка конкурентроспособности.

- Производственная программа;

- Построение процесса инвестиционного подхода к оценке выявляемых проблем;

- Отчёты по экономическим показателям (выручка, прибыль, маржа, себестоимость, коэффициент окупаемости вложений, срок окупаемости, точка безубыточности, оборачиваемость и пр.);

- Построение в Excel таблиц, являющихся данными для доработки аналитической части отчётов в программах типа 1С.

- Юнит (unit-экономика)/

Скриншоты примеров Excel приведены из работ на Excel 2016.

Во второй части книги будут добавлены аналитические примеры в Python с использованием PyCharm.

Глава 1. Нелепые вопросы, которые всё ещё встречаются.

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

Девушка, которой пришлось помогать в решении вопросов - Ольга.

Вопрос 1.

Ольге было необходимо сформировать отчёт по заработной плате, распределив его на две группы - оплата труда работников и стимулирующие выплаты.

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

***

В образце, как в Таблице 1.1. , строки с информацией чередуются с пустыми строками. Очень много лет назад я сталкивалась с тем, что в таком виде информация выгружалась из 1С: бухгалтерия, версия 6]

Таблица 1.1. Исходный вариант таблицы.

Я показала Ольге, как изменить вид таблицы.

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

- выделить ячейки мышкой таблицу ;

- выбираем Главная, найти и выделить, выделить группу ячеек, пустые;

- удалить, ячейки со сдвигом вверх.

Таблица 1.2. Таблица после обработки.

В Таблице 1.2. представлен новый вариант таблицы.

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

Следующим действием мы вставили в таблицу дополнительный столбец и разделили позиции по группам - оплата труда работников и стимулирующие выплаты.

Выделили таблицу и во вкладке Главная выберем Форматировать как таблицу. Образец того, что нужно сделать представлен в Таблице 1.3.

Таблица 1.3. Формируем таблицу.

Далее – Вставка, Сводная таблица.

В таблице 1.4. представлен образец, где показано какие варианты для вставки таблицы можно выбрать.

Таблица 1.4. Размещение на новый лист.

Можно разместить таблицу на новом, а можно на существующем листе с указанием диапазона для размещения.

Таблица 1.5. Размещение на существующий лист с указанием диапазона.

Нам потребовалось не более пяти минут, чтобы создать отчёт, как показано в Таблице 1.6.

Отчёт вместо данных из нескольких сотен строк суммировал информацию и отразил результаты в две строки.

Таблица 1.6. Вариант отчёта, сформированного

с использованием сводных таблиц.

Работа со сводными таблицами позволяет перестраивать отчёты в любом удобном виде. Подробнее о Сводных таблицах написано в Главе 2.

Также на примере отчёта Ольги я показала, как мы можем использовать формулу СУММЕСЛИ:

=СУММЕСЛИ(C:C;$F8;D:D)

=СУММЕСЛИ(C:C;$F9;D:D)

Глава 2. Работа со сводными таблицами и большим объемом информации.

Сводные таблицы в Excel - это очень мощный инструмент.

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

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

Для более простого понимания работы со Сводными таблицами рассмотрим сокращённый вариант Отчёта по продажам, представленный в Таблице 2.1.

Таблица 2.1. Отчёт по продажам.

У нас есть четыре столбца (Наименование товара , Группа товара, Регион продаж, Сумма продаж в условных единицах). Но даже этих четырёх столбцов вполне достаточного для того, чтобы увидеть варианты простых отчётов, которые можно создавать с помощью Сводных таблиц в Excel.

Идём вслед за образцами и смотрим, какие из возможных вариантов обработки существуют в предложенных функциях Excel.

В нашем отчёте всего несколько строк но, если вы используете для обработки свой отчёт, где несколько десятков- тысяч строк, то скорость обработки и создания отчёта с использованием Сводных таблиц не изменится.

Наши действия, которые необходимо выполнить:

Выделяем имеющуюся таблицу.

Вставка - Сводная таблица - На существующий (или новый лист).

В зависимости от выбора нужной нам информации мы можем построить различные отчёты.

Это простой отчёт: подробного описания не будет. Необходимо проявить самостоятельность и создать свой вариант отчёта. Неважно будет ли исходная таблица похожа на Таблицу 2.2.: учимся на примере, но делаем свою работу, а не работу образца.

Небольшое отступление: Нажимаем правую кнопку мышки и выбираем Параметры полей значений. Выберите, например, Количество, если необходимо анализировать не денежные показатели, а иные.

Иных несколько вариантов: количество, среднее, максимум, минимум, произведение, количество чисел, смещённое отклонение, несмещённое отклонение, смещённая диспрессия, несмещённая диспрессия).

Таблица 2.2. Отчёт с использованием Сводных таблиц.

Мы можем перенести Регион продаж в Фильтры. И у нас будет новый вид отчёта:

Таблица 2.3. Отчёт, изменяем вид.

В Сводных таблицах есть раздичные области: ФИЛЬТРЫ, СТОЛБЦЫ, СТРОКИ, ЗНАЧЕНИЯ. Попробуйте выбрать различные варианты для ваших данных. Вы можете два раза (или более) разместить в ЗНАЧЕНИЯ Сумму продаж. В таком случае, в Параметрах полей значений один из столбцов можно определить, как сумма; второй - количество (или что-то иное из Параметров полей значений).

Используя тот же отчёт, можно через Конструктор - Макет отчёта - Показать в табличной форме - Показать все подписи элементов привести отчёт к такому виду, как в Таблице 2.4.:

Таблица 2.4. Используем Конструктор.

Снова забываем, что мы делали таблицу по образцу и самостоятельно рассматриваем, что можно сделать в Конструкторе для обработки информации, которая имеется в вашей компании:

Промежуточные итоги (Не показывать промежуточные итоги, Показывать все промежуточные итоги в нижней части группы, Показывать все промежуточные итоги в заголовке группы, Включить отобранные фильтром элементы в итоги);

Общие итоги(Отключить для строк и столбцов, Включить для строк и столбцов, Включить только для строк, Включить только для столбцов);

Макет отчёта(Показать в сжатой форме, Показать в форме структуры, Показать в табличной форме, Повторять все подписи элементов, Не повторять подписи элементов);

Пустые строки(Вставить пустую строку после каждого элемента, Удалить пустую строку после каждого элемента).

Вы можете выбрать Параметры стилей Сводной таблицы: Заголовки строк, Заголовки столбцов, Чередующие строки, Чередующиеся столбцы.

Глава 3.1. Создание таблицы сложных процентов. Примеры их использования на практике.

В данной главе рассмотрим примеры, где могут использоваться сложные проценты.

Для начала создадим Таблицу сложных процентов, используя Excel.

В первом столбце по вертикали введём ставку процентов.

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

В таблице 3.1.1. приведен вариант таблицы с первым периодом для расчёта.

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

Таблица 3.1.1. Таблица сложных процентов с первым периодом.

Для периода 1 мы использовали формулу:

=$B$24+$B$24*A3

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

Таблица 3.1.2. Таблица сложных процентов с последующими периодами.

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

Для этого в ячейке С3 создаём формулу:

=B3+B3*$A$3

и протягиваем её до последнего периода по строке 3.

Когда мы протянем формулу из ячейки С3 до ячейки С22, то необходимый верный расчёт сохранится, кроме последней цифры:

=B22+B22*$A$3

Во всём столбце с С4 до С22 я вручную изменяю последнее число так, чтобы его номер соответствовал номерам двух предыдущих чисел.

После этого небольшого исправления: на образце строки 22 у нас получается:

=B22+B22*$A$22

Далее, как показано ниже, необходимо выделить ячейки С3:С23 и используя мышку протянуть, в нашем случае, до столбца К3:К23.

Таблица создана. Можно использовать её в расчётах.

Таблица 3.1.3. Таблица сложных процентов с десятью периодами.

Далее рассмотрим примеры возможных вариантов для использования Таблицы сложных процентов.

Рассмотрим на цифрах.

Необходимую сумму введём в ячейку В24, изменив нашу единицу. Пример в таблице 3.1.4. Для получения результата необходимо использовать только ячейку N2. Остальные не изменяем.

На примере этой таблицы защитим лист от изменений.

- Активируем ячейку N2;

- Нажимаем правую кнопку мышки и выбираем Формат ячеек;

- В появившемся окне переходим в Защита и снимаем галочку в окне Защищаемая ячейка;

- ОК;

- Рецензирование;

-Защитить лист;

- Снимаем галочку с Выделение заблокированных ячеек( проверьте, в открытом вариант галочки стоят на: выделение заблокированных ячеек и выделение незаблокированных ячеек;

- Если вы поставите пароль, то не забывайте его, пожалуйста;

- ОК.

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

Таблица 3.1.4. Таблица сложных процентов с расчётом на сумму 200 000 условных единиц.

Инвестор вкладывает в финансирование проекта сумму 200 000 (двести тысяч) условных единиц.

При ставке 10% годовых за 3 года сумма, которую должен получить инвестор при закрытии договора, вырастет до 266 200 условных единиц.

В расчёте мы использовали таблицу сложных процентов.

В Excel также есть вариан, где достаточно ввести простую формулу:

=C35*(1+C36)^C37

Мы можем изменять наши суммы и периоды, получая изменённые результаты.

Знак ^ находится на клавиатуре над цифрой 6. Чтобы ввести его в формулу, необходимо перейти на английскую раскладку и нажать вместе Shift+6.

Таблица 3.1.5. Расчёт, аналогичный расчёту в Таблицах сложных процентов.