Как сделать самолетики в экселе
В процессе одновременной обработки нескольких таблиц иногда может потребоваться объединение содержащихся в них однотипных данных, что позволит существенно упростить дальнейшую работу с ними. К счастью, делать это вручную не надо, т.к. в программе Excel специально для таких случаев предусмотрена функция “Консолидация”. Давайте посмотрим, что это за инструмент и как с ним работать.
Требования к таблицам
Обращаем Ваше внимание на то, что воспользоваться “Консолидацией” получается не всегда. Чтобы это было возможно, исходные таблицы (могут быть расположены на одном листе или на разных листах/книгах) должны соответствовать следующим требованиям:
- в них не должно быть пустых строк/столбцов;
- к ним должны быть применены одни и те же шаблоны;
- названия их столбцов должны быть одинаковыми (но порядок может отличаться).
Выполняем консолидацию
В результате применения функции “Консолидация” формируется новая, так называемая, консолидированная таблица. Давайте пошагово разберем на практическом примере, как ее собрать из 3 таблиц, соответствующих требованиям, перечисленным выше. Итак, выполняем следующие действия:
- Жмем по значку создания нового листа, после чего программа автоматически добавит его справа от текущего и переключит нас на него. Подробная информация по данной операции представлена в нашей отдельной публикации – “Как добавить лист в Excel”.Примечание: Можно переместить добавленный лист в удобное для нас место (например в конец списка) с помощью зажатой левой кнопки мыши, “зацепив” его за вкладку с названием. Также лист можно переименовать при желании (режим редактирования запускается двойным щелчком по имени, завершается нажатием Enter).
- В добавленном листе встаем в ячейку, с которой планируем вставить консолидированную таблицу (в нашем случае оставляем выбранную по умолчанию). Затем переходим во вкладку “Данные”, находим группу инструментов “Работа с данным”, где щелкаем по значку “Консолидация”.
- На экране отобразится небольшое окошко с настройками инструмента.
- Здесь представлены следующие параметры:
- Функция – кликнув по текущему варианту мы откроем список возможных действий, среди которых выбираем то, которое требуется выполнить для консолидируемых данных:
- Сумма (выбрана по умолчанию; используется чаще всего, поэтому оставляем ее в качестве примера);
- Количество;
- Среднее;
- Максимум;
- Минимум;
- Произведение;
- Количество чисел;
- Стандартное отклонение;
- Несмещенное отклонение;
- Дисперсия;
- Несмещенная дисперсия.
- Функция – кликнув по текущему варианту мы откроем список возможных действий, среди которых выбираем то, которое требуется выполнить для консолидируемых данных:
- Переходим к полю “Ссылка”, щелкнув внутри него. Здесь мы поочередно выбираем диапазон ячеек исходных таблиц, которые должны быть обработаны. Для этого:
- Переключаемся на лист с первой таблицей.
Заключение
Консолидация данных в программе Excel – это крайне удобный и полезный инструмент, с помощью которого работа, в первую очередь, с большими таблицами в значительной степени будет оптимизирована и автоматизирована, что позволит не только сэкономить время, но и сведет к минимуму возможные ошибки, которые могут возникнуть в результате ручной обработки данных.
Сразу отметим, что данное понятие - бухгалтерский самолетик, в учете имеет опосредованное значение. То есть, его в больше степени используют начинающие бухгалтера, чтобы разобраться в порядке осуществления хозяйственных операций в корреспонденции бухгалтерских счетов.
Раскроем суть «самолетика» опираясь на форму бухгалтерского баланса и той закономерности, которую он в себе несёт.
Все балансовые счета (те, которые участвуют в построении балансовой ведомости) разделены на две группы: активные (где размещены средства) и пассивные (откуда пришли эти средства). Каждый счет, как активный, так и пассивный имеет две стороны своего учета – Дебет и Кредит. Эти две стороны и можно рассматривать, как два противоположных «крыла» одного счёта. По сути, такая учётная форма и именуется в бухгалтерских кругах – «самолётик».
Не будем вдаваться в подробности того, что такое Дебет, а что такое Кредит. Отметим одно и важное.
Дебет – это учетная основа активных счетов, а кредит – пассивных счетов.
То есть, те суммы, которые отображены в балансе по активной стороне – это Дебеты всех активных счетов, а по пассивной стороне – это Кредиты всех пассивных счетов. А конкретней - это остатки (или сальдо) дебетовые (активных счётов) и остатки (или сальдо) кредитовые (пассивных счетов).
На примере отобразим хозяйственную операцию с использованием бухгалтерского самолётика.
Организация взяла в банке краткосрочный кредит (1 000 руб.) и эти деньги разместила на своём расчётном счете.
Бухгалтерская проводка будет иметь такой вид:
К66 Д51
В корреспонденции учувствую пассивный и активный счёт.
Что это означает:
Счёт 66 – это пассивный счет (источник – банковский кредит, т.е. откуда пришли деньги).
У пассивных счетов приход всегда отображается по Кредиту, а списание по Дебету. Поэтому сальдо у них кредитовое.
Счёт 51 – это активный счет (направление – расчётный счет, т.е. куда пришли деньги).
У активных счетов приход всегда отображается по Дебету, а списание по Кредиту. Поэтому сальдо у них дебетовое.
Самолётик данного счета:
В целом проводка в корреспонденции двух счетов выглядит следующим образом:
Теперь предположим, что организация купила сырье и оплатила за него 1 000 руб. с расчетного счета. Бухгалтерская проводка будет иметь такой вид:
К51 Д10
В корреспонденции учувствую активные счёта.
То есть, что произошло:
С расчетного счета «ушли» деньги (были списаны, т.е. кредитуем 51 счёт) и поступило сырьё (материал) на эту же сумму (осуществляем приход, т.е. дебетуем 10 счёт).
Вот по такому принципу строится вся бухгалтерская корреспонденция, так же и с использованием других счетов.
Думаем, из простого примера понятно, каково значение бухгалтерского самолётика в корреспонденции счетов и для чего он может быть применим. Ответ - просто для наглядности проводки хозяйственной операции, которую начинающим бухгалтерам иногда сложно уяснить.
Так как в предыдущей публикации прозвучал интерес к тому, как я веду бухгалтерию в Экселе, то, как и обещала, подготовила краткий обзор.
Сразу предупрежу, что, возможно, для человека, который Эксель в глаза не видел, будет что-то не совсем понятно. Но мне кажется, большинство тут все-таки знакомы с этой программой и какие-то минимальные навыки имеют.
Создаем новую книгу. По умолчанию в ней сразу создано 3 листа.
Переименуем их в «Доходы», «Расходы» и «Годовая отчетность» (у меня еще добавлен почтовый бланк для удобства оформления посылок).
На листе «Доходы» создаем таблицу с такими столбцами: месяц, число, комментарий, сумма. верхнюю строчку можно выделить цветом и зафиксировать.
Далее вводим сразу все 12 месяцев (на каждый отводим по 5-6 строк, потом можно добавить новые по мере необходимости). То есть создаем эдакую «рыбу», которую потом будем заполнять по мере поступления наших доходов:)
Ячейкам с деньгами проставляем формат ячейки «денежный» с рублями и 2 знаками после запятой.
Изначально в таблице все по нулям, но я заполнила тестовыми цифрами, чтобы было нагляднее. В комментариях пишем имя покупателя.
Аналогичную таблицу создаем на листе «Расходы». в комментариях пишем статьи расходов.
При необходимости добавления дополнительных строк нужно выделить пустые строки, кликнуть правой кнопкой мыши и нажать вставить. Очень важно, чтобы в выделенные строки не попала строка «итого», тогда у вас ничего не собьется.
Затем на листе «Годовая отчетность» создаем вот такую таблицу. Серым закрашены те ячейки, которые нам не понадобятся.
Так как у меня ИП на УСН (доходы без учета расходов), поэтому ставка 6%.
ОПС — обязательное пенсионное страхование.
ОМС — обязательное медицинское страхование.
В 2017 году эти страховые взносы (для ИП) составили 23400 р (опс) и 4590 р (омс).
Так как налоги и взносы мы должны уплачивать поквартально, то суммы взносов мы делим на 4 (по количеству кварталов) и заполняем соответствующие ячейки. Для этого, в той ячейке, что мы хотим заполнить, мы ставим «=», потом кликаем на ту ячейку, со значением которой мы будем работать (ее рамка начнет мигать пунктиром) и потом пишем нужное нам действие (в данном случае «/4» — деление на 4).
Так как суммы взносов меняются каждый год, то в следующем году нам нужно будет только поменять общую сумму, и она сама изменится во всех кварталах.
Получаем вот такое:
Важно помнить, что эти суммы обязательны к уплате в любом случае, даже если ваше ИП ушло в минус, даже если вы вообще не вели никакой деятельности.
Если ИП открыто не с начала года, то идет перерасчет, в интернете не проблема найти соответствующие калькуляторы.
Хочу заметить для тех, кто, возможно, начал ворчать, мол «вот опять государство у нас поборами занимается, малый бизнес душит». Обязательные страховые взносы дают возможность вам вызвать, например, скорую для ваших детей, и заплатить пенсию вашим родителям, в конце концов, это ваша будущая пенсия. Так что, это все не «деньги на ветер».
Далее заполняем ячейки с приходом. Для этого ставим = в январе:
И кликаем на наш лист «Доходы», где выбираем значение «Итого» января и нажимаем enter.
И вуаля, в нашей отчетности появилось значение января.
Аналогичным образом, очень внимательно и аккуратно, проставляем значение доходов и расходов за все три месяца. Так как сделать это придется один раз, то лучше не торопиться и все проверить.
Значение прибыли находим как разницу: доходы — расходы.
Далее находим общие суммы по нашим столбцам (доходы, расходы, прибыль) за первый квартал.
Далее, считаем налог на доходы. Он равен 6% от суммы доходов.
Теперь самое интересное.
Все-таки, наше государство не зверь) И позволяет уменьшить сумму налога на сумму уже уплаченных взносов (ОПС и ОМС). Поэтому мы считаем, превышает наш налог эти взносы или нет. Формула такая: налог — опс — омс.
Получаем отрицательное значения. Значит, ура, в этом квартале мы налог не платим и спим спокойно. Напомню, что налог подлежит к уплате не позднее 25 числа месяца, следующего за отчетным кварталом.
Далее аналогичным образом заполняем значения доходов, расходов и прибыли для 2-го квартала.
Но так как декларация потом заполняется не по каждому кварталу отдельно, а нарастающим итогом за: 1 квартал, полгода, 9 месяцев и год, то мы находим итоговую сумму для полугодия. Для этого суммируем значения 1 и 2 кварталов.
Получаем цифры за полугодие. Но «налог к уплате» за полугодие находим не суммированием налогов за оба квартала, а как и раньше, по формуле: налог — опс — омс.
И снова ура, мы не должны платить государству налог.
Чтобы было интереснее, для следующих месяцев я проставила цифры побольше, чтобы дойти, наконец, до ситуации, когда налоги придется платить :)
Заполняем данные третьего квартала. Подводим итого и видим, что налог нам уже светит.
Но! Так как отчетность у нас идет, напомню, нарастающим итогом за 9 месяцев, то мы пока не паникуем и считаем итого за три квартала.
И, как видим, от налога мы снова освобождены. Это произошло потому, что в первых двух кварталах наши страховые взносы существенно превышали наш налог.
Заполняем данные 4-го квартала.
Подводим итого за год:
И находим сумму налога к уплате (напомню, «налог — опс — омс»). И вот тут получается, что, наконец, наш налог к уплате пришел в плюс, наши страховые взносы его не перекрыли, и мы должны будем его уплатить.
Но это еще не все:)
Как видно, наш суммарный годовой доход получился больше 300 тыс. рублей, поэтому мы должны будем заплатить в пенсионный фонд 1% от суммы превышения. То есть, формула такая: (наш доход — 300 000) * 0,01.
В итоге государству за год мы должны будем заплатить: 23400р (ОПС) + 4590р (ОМС) + 1416р (налог на доходы) + 1901р (ОПС) = 31307р.
Вот в целом и все:)
На первый взгляд, может показаться сложно, но на самом деле, если заранее проставить все формулы и связи между ячейками, все будет считаться автоматически. Вам нужно будет только фиксировать приход и расход денег.
Потом еще и графики можно будет строить. Как ваша прибыль растет и растет из года в год :)
Также важно помнить, что сроки уплаты всех налогов и взносов регламентированы и за просрочку — штраф, начинает «капать» пеня. Вся информация по срокам есть на сайте налоговой, да и в интернете информации много.
Еще добавлю, что так как мне, например, не очень удобно каждый квартал возиться с платежками в фонды, я плачу сразу все взносы целиком в начале года. Это не возбраняется, разделение по кварталам сделано, чтобы снизить финансовую нагрузку на плательщика (то есть на нас). Но мне так проще. Сразу в начале года заплатить крупную сумму, зато потом не думать об этом. Я и налоги до 4-го квартала не плачу, так как ранее уплаченные взносы их перекрывают. Так как в январе у меня в «казне» денег после новогодних праздников, как правило, больше, чем летом, то такая схема удобна.
И если кому интересно, почтовый бланк выглядит так:
Буду рада, если эта информация пригодится.
Удачи в творчестве и в бизнесе!
UPD: Важные дополнения к статье (спасибо читателям).
1. Страховые взносы НЕ ОБЯЗАТЕЛЬНО уплачивать поквартально, это РЕКОМЕНДАЦИЯ налоговой службы, но вы можете уплатить их не только в начале года, но и в конце, и вообще, когда и как угодно, поделив на какие угодно удобные части, главное: не позднее 31 декабря отчетного периода.
2. Не уточнила сразу, но тот самый 1% (который свыше 300 тыяч рублей), который в нашем примере получился 1901 р., тоже можно зачесть в счет уплаты налога. Если вы успели уплатить этот взнос в текущем году (до 31-го декабря), то налог за текущий год можете уменьшить на эту сумму. В моем примере налог был 1416 р. (меньше, чем 1901 р.), поэтому от налога будет полное освобождение.
Если вы не успели оплатить этот взнос до 31го декабря, то уже в следующем налоговом периоде вы уплачиваете и налог (1416р), и этот взнос (1901р), но потом пишете заявление в налоговой, чтобы этот взнос вам зачли в счет уплаты будущих налогов. Таким образом, это будет ваш налоговый вычет на будущий год.
Много полезного и интересного я черпаю из этого сообщества, и поэтому сейчас, когда у меня появилось чем поделиться, делаю это с удовольствием.
Я люблю Excel. Работа с таблицами, изучение новых (для меня) его возможностей и реализация возникающих идей доставляет мне удовольствие. Процесс превращается в решение увлекательной (или не очень) задачи/ квеста.
Недавно в разговоре с моим тренером узнала, что ей нужна для учета оплат и посещений табличка в excel, но у неё самой что-то не получается это сделать. Мне данная задача показалась интересной (она такой и была), и я приступила к её выполнению.
Сначала подумала над структурой.
- список клиентов (есть постоянные и разовые);
- несколько групп по разным направлениям;
- 4 типа абонементов (групповые, разовые групповые, индивидуальные), с параметрами по срокам действия и количеству занятий;
Что необходимо было реализовать:
- Учет клиентской базы;
- Табель учета посещений + чтобы оплаченный период в нем подсвечивался цветом;
Процесс реализации (излагаю то, что помню).
Тут хотела бы остановиться чуть подробнее.
Пару дней назад, вечером, когда я уже свернула ноутбук, убрала книгу, и пошла в ванну, вспомнила, что в сводной таблице можно делать выборку максимального значения!
Попробовала реализовать – ПОЛУЧИЛОСЬ. Я запрыгала и затанцевала!
Я была счастлива!
Простое и элегантное решение было найдено!
После этого дело оставалось за малым – оформить книгу под N-е количество групп и сделать условное форматирование.
В итоге получился вот такой табель.
С чем еще предстоит разобраться.
Даты в табель выводятся вообще все подряд. Пришлось их группировать по 10 дней.
Читайте также: