Знакомство с плоскими многомерными таблицами: (аналитические разрезы-данные). Простейший навык работы с таблицами Группировка и фильтрация. Инструмент сводная таблица в Excel.
Автоматизация учета предполагает знакомство с тем, как устроены плоские многомерные таблицы. На основании прикладного инструмента «Сводная таблица» в Excel– мы знакомимся с одним из самых важных действий с таблицами - Группировкой таблиц.
Это база для автоматизации учета. Все умеют делать действия с числами (+-*/ и т.д.). Но не многие умеют действовать с таблицами. Между тем, есть набор действий с таблицами, который позволяет производить различные вычисления и получать различные отчеты. Без этого автоматизировать учет невозможно, так как автоматические расчеты базируются на действиях с таблицами (как обычные вычисления базируются на действиях с числами).
Автоматизация учета предполагает знакомство с тем, как устроены плоские многомерные таблицы. На основании прикладного инструмента «Сводная таблица» в Excel– мы знакомимся с одним из самых важных действий с таблицами - Группировкой таблиц.
Шаг1. Посмотрите видео урок "Действия с таблицами"
Временные метки:
00:00 - Часть 1. Плоская многомерная таблица.
08:15 - Часть 2. Действия с плоскими таблицами. Решение учетных задач.
26:24 - Часть 3. Пример решения задачи "Ресторан"
Общая продолжительность урока 43:29
Шаг 2. Прочтите статьи.
1. Что может сводная таблица (часть 1)
2. Что может сводная таблица (часть 2)
5. ER-модель – первый шаг к учетной системе
6. Правила перехода от ER-модели к таблицам
7. «Уровень отчетов», инструменты (действия с таблицами)
Шаг 3. Разберите пример.
Шаг 4. Выполните самостоятельные задания
Работа с учетными данными - это всегда работа с плоскими таблицами.
Эта прикладная область знаний, которую изучает Реляционная алгебра. Все области знаний, можно разделить на две части.
В одних важнее теория, в других важен скорее навык.
Например, иностранный язык - это навык. Программирование - это тоже навык. Практика работы с данными - это тоже навык. Но в отличии от языка, этот навык можно приобрести в течении 1-2 недель, и в отличии от языка - этот навык требует очень небольшой набор инструментов.
90% из необходимых инструментов, это:
1) Сводная таблица
2) ВПР
3) СУММЕСЛИМН
4) ЕСЛИ
Все задачи решаются последовательным применением этих инструментов.
Не переживайте, если у вас не будет получаться сразу... поиграйте этими инструментами, отнеситесь к этому, как к интересной головоломке...
Если вы поймете, что вы не можете решить задачу, то обращайтесь к нам. Возможно, что и мы бы раньше не могли бы решить эти задачи.
Помните, это навык, и навык, который приобретается в течении 1-2 недель.
Задача 1.1 (Премия менеджеру)
Компания позаказное производство. Каждый заказ назначается менеджеру. Менеджер ведет одновременно много заказов. Премия расчитывается в зависимости от прибыли Заказа. Существует две ставки: 10% и 5%. Ставка зависит от общей суммы доходов менедежра по всем-всем заказам. Если сумма всех доходов менеджера - больше, чем 450, то 10%, а если меньше 450 - то 5%.
Требуется: Рассчитать сумму премии менеджера.
Методика:
- В таблицу Доходы-расходы по заказу нужно добавить менеджера (ВПР) и поле Доходы/расходы (ЕСЛИ).
- Делаем сводную таблицу: менеджер/заказ и Доходы/расходы
- Считаем доход менеджера по всем Заказам (СУММЕСЛИМН)
- Считаем ставку (ЕСЛИ)
- Считаем Премию по каждому Заказу (ставка * прибыль по заказу)
- Делаем сводную таблицу: менеджер/премия
Задача 1.2 (Рейтинг товара)
Эта задача давалась при собеседовании в крупную западную компанию.
Есть таблица Товар//Филиал//Рейтинг товара (1-15). 1 - самый продаваемый, 2-второе место в продажах. К сожалению абстолютных цифр по продажам - нет.
Требуется:
По таким относительным данным требуется определить список из 6-ти (предположительно) самых продоваемых товаров.
Методика:
Раз мы не можем просто подсчитать сумму продаж (у нас нет абсолютных цифр), то нам нужно подсчитать количество мест которую получил каждый товар. Сколько товар получил мест №1, мест №2 … и т.д. Распределить по рейтингу кличества оценок. Если так не получиться решить задачу, то придумать какой-то коэффициент для приведения к одномерной шкале.
Задача 1.3 (Прибыль проекта)
Компания выполнила три проекта за месяц силами трех сотрудников
Каждый сотрудник выполняет различные функции на проектах.
Каждый сотрудник работал на всех трех проектах.
Над каждым проектом работали все трое сотрудников.
Других затрат, кроме как зарплаты - нет.
Требуется:
Нужно подсчитать прибыль каждого проекта, для этого нужно распределить ЗП сотрудников на проекты по трудоемкости (то есть по часам работы). В месяце было 160 часов (20 рабочих дней). Вопрос: какой проект самый прибыльный?
Методика:
- Считаем, сколько каждый сотрудник работал над каждым проектом.
- Считаем, сколько часов сотрудник работал ИТОГО (всего в месяце 160 ч)
- Считаем % времени, сколько сотруник потратил на каждый проект.
- ВПР-ом подтягиваем в таблицу, ЗП каждого сотрудника.
- Умножаем % времени (затраченное на проект) на сумму ЗП сотрудника.
- Считаем итого затраты ЗП на каждый проект (сводная таблица или СУММЕСЛИ).
- Сводим все данные по проекту в одну таблицу.
Задача 1.4 (ТОП-500)
Имеем данные (из открытых источников):
ТОП-500 РБК (2015)
ТОП-500 РБК (2018)
Города (из ТОП-500)-Регионы РФ
Регион (из ТОП-500) - ФО
Все регионы РФ
Требуется:
- Построить таблицу "Новичок в ТОП-500 в 2018"
- Построить таблицу "Изчез из ТОП-500 2015"
- Построить список самых богатых субьектов РФ (регионы РФ) в 2015 и 2018 и сравнить динамику
- Построить список самых богатых Федеральных округов РФ 2015 и 2018 и сравнить динамику
- Список Регионов, у который вообще нет доходов из компаний ТОП-500
- Найти компании из (списка ТОП-500, 2015, 2018), которые находятся в городах менее 30 000 населения
- Построить кросс-таблицу: город-рынок (на пересечении сумма) и построить диаграмму (по желанию) (за 2015)
Задача 1.5 (ДДС)
В плоской таблице есть все записи о движении денежных средств (ДДС)
Требуется:
- Построить стандартный ДДС компании: группа статей/статья ДДС в разрезе месяцев
- Расчитать остатки для каждого счета или кассы, на каждую дату из таблицы
- Найти ошибку ( когда остатки меньше 0)
- Рассчитать остаки на календарную дату, а не на дату из таблицы. (факультативно)
Задача 1.6 (Спорт)
Есть таблица по всем соревнованиям в РФ
Требуется:
Ответить на вопросы:
- Во всех ли видах спорта принимают участие "слепые" спортсмены?
- Слепые спортсмены принимают участие в региональных соревнованиях?
- Наиболее популярные виды спорта у взрослых (основной состав) и у молодежи совпадают?
- Меняется ли мода (% соотношение) на виды спорта у обычных и особых спортсменов?
- Для кого больше организуются соревнований, для молодежного (молодежный состав) или взрослого спорта (обычный состав)?
- 5 самых популярных групп видов спорта для обычных спортсменов и для глухих?
- Спортсмены с ментальными нарушениями участвуют в Интелектуальных видах спорта?
- Сколько всего соревнования российского уровня по группе "Борьба" и настольному тенисув 2010 году? Сколько в них приняло участие?
- В РФ активнее зимние или летние виды спорта?
- По скольким видам спорта проводятся соревнования в РФ?
- Где наиболее часто проводятся соревнования Всероссийского уровня?
- Можно увидеть всю картину по годам, группам видов спорта и видам спорта (по взрослым обычным спортсменам)?
Задача 1.7 (Кредитная линия)
Это задача, оносятся к классу задач "партионного учета".
ВКЛ - это возобновляемая кредитная линия, самый распростренный тип банковского кредита. Кредит состоит из набора траншей. Транш это единичное получение денег - микродоговор внутри одного большого договора. Транши даются на более короткое время, нежели сам кредитный договор. Например, договор на 2 года, а транши на 90 дней. Транши должны быть погашены не позднее, чем 90 дней после их получения. Погасить и взять транш можно в течении одного дня.
То есть, когда компания имеет свободные (хотя бы на один день) ДС - то она старается погасить как можно больше траншей, чтобы отодвинуть границу 90 дней.
Тогда требуется произвести расчет, а какие транши погашены, а какие нет.
В Excel такие расчеты автоматически произвести не возможно. Некая учетная система произвела данные расчеты и поместила их в таблицу.
Требуется:
- Построить таблицу, в которой понять, номера платежек, которыми сформирован и погашен транш
- Остатки каждого из траншей к погашению по датам (то есть построить календарь необходимых оплат)
- Построить такую же таблицу для следующих операций
- По договору транши - 90 дней. 1, 10, 20 марта берем по 10 млн. 1,10,20 апреля гасим по 4 млн
- Вручную построить таблицу, из которой можно получить отчет - "остатки траншей к погашению" в разрезе дат
Задача 1.8 (Аренда)
Существует компания, сдающая помещения в аренду .
Требуется:
- Начислить выручку по числу дней аренды за июль 2019 (3 договора)
- Начислить выручку по числу дней аренды за авг 2019 (1 дог)
- Вычислить вакантность площадей за июль
После выполнения всех заданий отправьте нам файл с решениями.