Данная глава будет посвящена действиям над таблицами. Без этих знаний и навыков невозможна автоматизация учета. Многие примеры из предыдущих глав было сложно воспринимать без данных знаний и навыков.
Эта область настолько важна, что имело бы смысл начать изучение данного курса можно было с этой главы. Но так как прикладные курсы (а данный курс сугубо прикладной) чаще всего строятся по принципу "от практике к теории", то поэтому рассмотрение этого вопроса смещено в последней трети курса - в "уровень отчетов".
(!) Так одним из условий прохождения курса было наличие у вас опыта работы со "Сводными таблицами" в Excel. Эти должны были помочь понять те примеры, которые были описаны в предыдущих главах.
Представление о действиях с таблицами можно получить, сравнив их с действиями над числами. Что такое действия над числами?
3 х 2 = 6 (три умножить на два, равно шесть)
Три - число. Два - число. Шесть - число. Два числа, между ними действие - получилось третье число, которое (в общем случае) не равно ни первому, ни второму числу.
Число 1 (действие) Число 2 = Число 3
Можно привести несколько тезисов, описывающих действия над числами:
- Результат действий над числами - число и в общем случае оно не равно исходным числам (6≠3 и 6≠2)
- Есть действия результат которых не зависит от очередности чисел (2+100 = 100+2), а есть где зависит (2/100 ≠ 100/2).
- Можно совершать действия не над парой, а над большим количеством чисел, в том случае часто требуется определить очередность действий, потому что результаты могут отличаться от их действий.
Теперь можно в тексте выше поменять слово “Число” на слово “Таблица”.
Общий вид действий с таблицами будет:
Таблица 1 (действие) Таблица 2 = Таблица 3
- над Таблицами можно производить действия
- результатом будет Таблица и в общем случае она не равна исходным Таблицам.
- есть действия над Таблицами, в которых важна очередность, есть действия, в которых не важна очередность.
- можно совершать действия не над парой, а над большим количеством Таблиц, в том случае часто требуется определить очередность действий, потому что результаты могут отличаться от их действий.
(!) Здесь стоит отметить, что не все результаты действий с Таблицами являются Таблицами. Например, результатом действия "посчитать количество строк" или "посчитать сумму чисел в столбце" - будет Число, а не Таблица.
Но данные действия очевидны и не требуют отдельного рассмотрения. Мы будем изучать только действия, результатом которых будет Таблица.
Действия над таблицами изучает Реляционная алгебра. Над таблицами можно производить 8 ТИПОВ действий:
- Объединение
- Пересечение
- Вычитание
- Декартово произведение
- Выборка
- Проекция
- Соединение (4 типа)
- Деление.
Мы отступим от классической подачи материала. Во-первых, часть действий тривиальны, во-вторых, тем кто уже имеет практический опыт работы в Excel эту информацию можно объяснить это проще. В-третьих, часть действий редко применяются для решений учетных задач.
В нашем варианте действия над таблицами будут:
- Группировка
- Фильтры (4 типа)
- Объединение
- Соединение (4 типа)
Группировка (GroupBy) – это то, что делает «Сводная таблица» в Excel. Это, одно из самых часто встречающихся действий используемое в учетных задачах.
Таблица_1 Группировка = Таблица_2
Группировка уменьшает количество строк в таблице. Сгруппировывает одинаковые строки в одну, производя при этом какое-то действие с числами (чаще всего сложение) в этих строках. Например, в таблице ниже у нас три строки с Заказом 1, а в сгруппированной таблице останется одна строка с Заказом 1. См. рисунок ниже.
При этом операция производит суммирование количества (4) и суммы (51 800). С Заказом 2 система сделает тоже самое, “схлопнет” все строки с Заказом 2 до одной, и просуммирует количество (3) и сумму (42 000).
То же самое система сделает, если мы мы сгруппируем не по полю Заказ, а по полю Товар. См. рисунок ниже.
Группировать можно не по одному полю, а по нескольким полям. В этом случае система сгруппирует (схлопнет”) в отчете все строки, до уникальных комбинаций двух полей: <ЗАКАЗ_ТОВАР>. См. рисунок ниже.
(!) Если в таблице «приходы» будут с «+» а расходы с «–», то Группировка по этому полю дает остаток.
Фильтры
Мы будем использовать 4 вида Фильтров.
1) Фильтры по Полю.
Таблица_1 Фильтр (по Полю) = Таблица_2
Легче всего это продемонстрировать на примере. Сначала рассмотрим исходную таблицу. См. рисунок ниже.
Как работает фильтр? Давайте отберем все строки с мастью Черви (♥). Тогда таблица примет следующий вид (см. рисунок ниже).
В Фильтрах можно применять союзы «Логическое И» и «Логическое ИЛИ». Пример, «Логического ИЛИ». “Отобрать все строки с мастью черви (♥) или пики (♠)”. «Логическое ИЛИ» всегда увеличивает количество строк в результирующей таблице. Очевидно, что “или то или это”, всегда имеет больше вариантов, чем “только это”. См. таблицу ниже.
«Логическое ИЛИ» можно применить к нескольким столбцам. Например, нам нужны все строки, где есть пики (♠) или числа 3 (см. рисунок ниже).
(!) Такие фильтры невозможны в Excel. В Excel это можно сделать только если скопировать результаты фильтров на отдельный лист.
«Логическое И» в отличии от «Логического ИЛИ» напротив всегда уменьшает количество строк. Например, мы выбираем строки в которых черви (♥) и число 5 (см. рисунок ниже). Тогда таблица после отборов будет такой (см. рисунок ниже).
(!) Все фильтры, которые мы рассматривали были фильтры “РАВНО”. Но точно также могут применяться и фильтры НЕ РАВНО. Принципиально ничего не поменяется, за исключением того, что “НЕ РАВНО... ИЛИ НЕ РАВНО…” будет не увеличивать количества строк, а уменьшать. И наоборот “НЕ … И НЕ…” будут увеличивать количество строк в результирующей таблице.
2) Фильтр, как вычитание строк.
Таблица_2 Фильтр (-Таблица_1) = Таблица_3
В результирующую таблицу должны попасть только те строки, которых нет в другой таблице. Схематически это можно представить так как изображено на рисунке ниже.
В результирующую (зеленую) таблицу должны попасть только те строки, которых нет в красной таблице. При этом таблицы могут иметь разную структуру. Например, у нас есть две таблицы, которые изображены на рисунке ниже.
Нам требуется в Таблице 2 отобрать только те строки, которые нет в Таблице 1. То есть отобрать только те строки, в которых нет ♥ и ♦. Результатом этого действия будет следующая таблица. См. рисунок ниже.
(!) Пример из учета. Таблица 1 - таблица опозданий. Таблица 2 - таблица премий. Таблица А - Таблица В, это все сотрудники, которым была начислена премия, но у которых за определенный период не имеют опозданий.
3) Фильтр, как пересечение строк Таблиц.
Таблица_2 Фильтр (Таблица_1) = Таблица_3
В результирующую таблицу должны попасть только те строки, которых есть в обоих таблицах. Схематически это можно представить так как изображено на рисунке ниже.
В результирующую таблицу попадают только те строки, которые содержаться в обоих таблицах. При этом таблицы могут иметь разную структуру. Рассмотрим на примере уже знакомых таблиц. См. рисунок ниже.
Нам требуется в Таблице 2 отобрать только те строки, которые содержаться в Таблице 1. Результатом станет таблица, в которой есть только черви (♥) или буби (♦). См. рисунок ниже.
(!) Пример из учета. Таблица 1 - список номенклатуры на которую действует скидка. Таблица 2, таблица продаж за месяц. Нам требуется посчитать общую сумму скидок за период.
Следующее действие - это Объединение таблиц.
Таблица_1 Объединить Таблица_2 = Таблица_3
Это действие, когда мы к одной таблице, снизу “подставляем” другую таблицу и получаем одну большую таблицу, которая включает в себя все строки первой и второй таблицы. Структура таблиц должна быть одинаковой. См. рисунок ниже.
(!) Главное условие - одинаковые структуры таблиц, чтобы “буквы к буквам”, “цифры к цифрам”. Количество строк - не важно. Количество таблиц тоже не важно.
Переходим к последнему Соединения таблиц.
Таблица_1 Соединить Таблица_2 = Таблица_3
Мы рассмотрим 4 типа соединения таблиц.
(!) Мы уже касались вопроса связи между таблицами в главе 3, когда из таблиц Сотрудник, Подразделение и Назначение получали отчет о пропорции мужчин и женщин в различных филиалах . В данной главе мы рассмотрим вопросы соединения таблиц более подробно.
Соединения таблиц - это самые сложные для понимания действия над таблицами. Но при этом именно эти действия и помогают выполнить самые сложные расчеты. Попробуем сначала описать основную идею “Соединения таблиц”. “Соединения таблиц” можно описать двумя действиями:
- приставить “сбоку”
- создать “цикл” или “перебор”
Разберем по отдельности. Первое действие - приставить “сбоку”.
Предыдущее действие “Объединение таблиц” мы описывали, как “подставить снизу”.
А “Соединение таблиц” можно описать “подставить сбоку”. Для данного действия структура таблиц не важна. Важно лишь, чтобы в обеих таблицах было одинаковое поле, по которому можно "склеить" таблицы. Рассмотрим это на примере. Ниже на рисунке можно увидеть две исходные таблицы. В обоих таблицах есть одинаковое поле, по которому мы будем соединять их. См. рисунок ниже.
Как мы уже говорили, существует 4 основных разновидности Соединения таблиц. И для всех разновидностей характерна идея “подставить сбоку”. Для демонстрации “подставить сбоку” применим один из способов соединения - "Левое соединение таблиц".
См. рисунок ниже.
При этом мы получили новую таблицу (Таблицу 3). За счет того, что мы подставили таблицу “сбоку”, мы получили дополнительную информацию. В нашем случае, например, мы получили новые взаимосвязи, соответствие цифры 1 и домино 1-1, черному цвету, масти ♥ и т.д.
(!) Поле по которому происходит соединение, называется "ключом". В данном примере "ключом" является "буквы". См. рисунок ниже.
Второе идея объясняющая, что такое Соединение таблиц - это идея “цикла” или “перебора”.
В нашем примере выше, "ключ" по которому мы соединяли таблицы, встречался в таблицах один раз. Поэтому при соединении таблиц одной строке в одной таблице - соответствовала одна строка в другой. См. рисунок ниже.
А что если бы строк было бы много в обоих таблицах? Например, значение "ключа" (буква А) во всех таблицах было бы одинаково. См. рисунок ниже.
Как соединить эти две таблицы? Как раз "перебором" строк, другими словами "циклом".
(!) Как мы уже отмечали, существует 4 варианта соединений. Идею цикла можно продемонстрировать на любом из них. В рассмотрении этого примера мы используем "Левое Соединение".
Берем первую строку Таблицы 1. См. рисунок ниже.
Далее мы присоединяем к этой строке по "ключу" все, что подходит из второй таблицы. Мы пробегаем по всей таблице и выбираем из нее ("по ключу") все, строки, которые нам подходят. Сколько есть подходящих строк во второй таблице, столько и присоединяем. См. рисунок ниже.
Поэтому для описания этого действия подходит слово “цикл” или слово “перебор”. В результате получаем первую часть таблицы. См. рисунок ниже.
Далее, берется вторая строка из первой таблицы и к ней также присоединяются все имеющиеся строки с "ключом" равным букве А. См. рисунок ниже.
Получится следующий результат. См. рисунок ниже.
Теперь изобразим весь процесс. См. рисунок ниже.
(!) Здесь важно еще раз отметить, что что при соединении мы перебрали все строки с одинаковым ключом. Было две строки с А в одной таблице и три строки в другой таблице с ключом А. В итоговой таблице получилось шесть строк (2 х 3 = 6).
Мы рассмотрим четыре вида соединений: Левое, Правое, Полное и Внутренне. Левое, Правое и Полное объединяют во Внешнее, как противопоставление Внутреннему. Визуально их взаимоотношения можно представить так, как изображено на рисунке ниже.
Вот таблицы, на примере которых мы будем изучать соединения таблиц. Заказ - это "ключ", по которому мы будем соединять таблицы. См. рисунок ниже.
(!) Важно. Для простоты будем использовать таблицы, в которых каждому значению "ключа" будет соответствовать одна строка. То есть каждый Заказ будет встречаться один раз. Это упростит понимание различия в соединениях таблиц. Перейти к таблицам в которых "ключ" будет встречаться более одного раза - не сложно.
Переходим к рассмотрению Соединений.
Левое соединение (внешнее):
Таблицу слева мы делаем "основной" (отсюда и название "Левое соединение"). К "основной" таблице"притягиваем" "по ключу" таблицу справа. Если мы не нашли соответствие в таблице справа, то ставим прочерки (-//--//-).
Заказу 1 и Заказу 2, не нашлось соответствие - поэтому мы поставили прочерки (-//--//-). Заказ 5 и Заказ 6 мы просто отбросили, потому что их не было в "основной" таблице (в "левой" таблице). См. рисунок ниже.
Правое соединение (внешнее).
Технология Правого соединения точно такая же, как и Левого, но за исключением того, что "основной" таблицей мы делаем таблицу "справа" (отсюда и название "Правое соединение"). Заказу 5 и Заказу 6 соответствия в таблице мы не нашли, поэтому ставим прочерк (-//--//-). Заказ 1 и Заказ 2 мы отбросили, потому что их нет в "правой" таблице. См. рисунок ниже.
Теперь сравним результаты “Левого” и “Правого” соединения.
Как видно из рисунка, результаты этого действия зависят от изначального расположения таблиц. Результаты “Левого” и “Правого” соединения не совпадают. В обоих таблицах совпадают только строки с Заказом 3 и Заказом 4. Так как они были в обоих таблицах.
Полное соединение (внешнее).
Полное соединение не имеет "основной" таблицы. Это действие сохранит все строки в обоих таблицах. Как будто мы одновременно сделали и левое, и правое соединение. См. рисунок ниже.
Это действие не зависит от очередности расположения таблиц и сохраняет все строки из обоих таблиц.
Внутреннее соединение.
В отличии от Полного соединения (в котором мы сохранили все строки), при внутреннем соединение мы наоборот удалим все "незаполненные" строки.
Внутреннее соединение Можно представить, как Левое или Правое или Полное, с последующим отбором всех не найденных полей. В этом случае останутся только полностью заполненные строки. См. рисунок ниже.
Интересно разобраться в терминах Внешнее и Внутреннее. Понять это можно глядя на схему ниже. Все внешние соединения (Левое, Правое и Полное) в итоговой таблице могут иметь частично не заполненные строки (стоит добавить, что могут иметь, а могут и не иметь). См. примеры выше. Внутреннее соединение - это всегда таблица заполненная полностью.
До этого момента мы рассматривали таблицы в которых строки с "ключами" не повторялись в таблицах. Теперь для полной картины рассмотрим пример, в которой "ключ" встречается в таблицы больше одного раза. Заказ 1 встречается в обоих таблицах по много раз, См. рисунок ниже.
Теперь произведем "Левое соединение" двух таблиц. Принципиально действие над этими таблицами не отличается от предыдущих, ключевые слова "сбоку" и "перебор" (или цикл).
Последовательно берутся все строки из "основной" (в данном случае "левой" таблицы) и по "ключу" ищутся все строки во второй таблице. См. рисунок ниже.
Результат Левого соединения этих таблиц можно увидеть ниже на рисунке. То условие, что строки с "ключом" не уникальны - принципиально ничего не изменило. См. рисунок ниже.
Последнее для данной главы усложнение. Мы соединяли таблицы по "ключу", который состоял из одного Поля (в нашем случае "Заказа"). Но возможно и соединения по ключам, которые состоят из более, чем одного поля.
Принципиально, это ничего не изменяет, так как мы мысленно можем составить одно ”большое-единое" поле, которое сцеплено из "кусочков" полей. Рассмотрим уже знакомую ситуацию из СКВОЗНОГО ПРИМЕРА. См. рисунок ниже.
И уже это "составное поле" мы мысленно можем использовать как "единое поле" при соединении таблиц.
Очевидно, что если значение хотя бы одного поля в "составном ключе" не совпадает, то значение "составного ключа" будет уникальным. Например, строка 1 и 2 это различные Участники (в рамках модели СКВОЗНОГО ПРИМЕРА), хотя в этих строках не совпадает только номер скачек.
Мы рассмотрели действия с таблицами. Последовательное применение данного набора инструментов позволяет получить практически любой отчет.
Теперь схематически продемонстрируем, как действия над таблицами применяются для формирования отчетов.
Рассмотрим следующий пример. Мы работаем в государственной структуре, цель которой распределять социальное пособие среди малоимущих семей. Считаем, что семья считается малоимущей, если доход на одного члена семьи меньше, чем определенная величина Х. Доход на одного члена семьи рассчитывается как сумма всех доходов семьи, деленная на количество ее членов.
Данные в этой задаче разнородны, и мы не можем их поместить в одной таблице, для хранения данных нам потребуется несколько таблиц.
(!) Данная задача очень сложна, мы несколько упростим ее и продемонстрируем решение схематично.
Так как семьи могут распадаться и создаваться новые, то в первой таблице мы будем хранить информацию об актуальных семьях. В другой таблице будет находится информация о всех детях. В отдельных таблицах мы будем хранить доходы родителей и их алименты (которые также являются доходами и расходами).
Решается эта задача так:
- нам требуется наложить Фильтр Сгруппировать таблицу "Дети", чтобы получить список всех детей, младше 18 лет (так как пособие рассчитывается только на детей младше 18 лет).
- Далее нам нужно Сгруппировать эту отфильтрованную таблицу, чтобы получить количество детей "младше 18" в семьях.
- Далее к Таблице Семьи Левым соединением по "ключу" физические лица мы можем получить доходы и алименты каждого физического лица.
- Сгруппировав эту таблицу по семье - мы получим доходы и расходы каждой семьи, а также их численности родителей (так как семьи могут быть не полными, только отец или только мать).
- В эту же таблицу Левым соединением получим количество детей младше 18 лет.
Схематично решение можно представить так, как изображено на рисунке ниже.
Итоговая таблица будет следующей. См. рисунок ниже.
(!) Все описанные действия над Таблицами реализуются в A-techs.io с помощью специальных формул и выражений, которые называются "Источниками данных". Как работать с "Источниками данных" можно ознакомиться в Документации
Подведем итоги главы: В данной главе мы рассмотрели действия над таблицами. Данные инструменты позволяют автоматизировать расчеты, которые возникают в ходе автоматизации учета компаний.