Excel таблица для учета финансов | Пикабу

Excel таблица для учета финансов | Пикабу Вклады Сбербанк

2 шаг. отредактируйте всё под себя

Заполняем кошельки во вкладке «ДДС: настройки». Кошелёк — это там, где лежат деньги. Например, расчетный счёт, касса, сейф, карман. То есть всё, откуда вы берёте деньги на бизнес и куда их кладете. Это самое важное — учёта не будет, если не будет кошельков.

Заносим направления и контрагентов во вкладке «Справочники». Направления — аспекты вашего бизнеса. Представим кондитерскую. Они пекут торты на заказ. Это одно направление. Ещё они пекут булочки, пирожные, пироги и продают в розницу. Это уже второе направление.

Контрагент — тот, кому мы платим или кто платит нам. Человек, у которого вы арендуете помещение, — контрагент. Человек, который пользуется вашими услугами или покупает ваш товар, — тоже контрагент.

Редактируем статьи и субстатьи во вкладке «ДДС: статьи». По факту эта вкладка — подсказка для всех, кто будет работать с таблицей. Давайте по порядку разберём все её сущности.

Статья ДДС — группа поступлений или выбытий. Если проще: откуда нам могут прийти деньги или куда они могут уйти. Например, «Аренда офиса», «Офисные расходы» и др.

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

Субстатья ДДС — подробное описание статьи ДДС. Например, в статье пишем: «Аренда торговых точек», а в субстатье расписываем: «Платежи за аренду и коммунальные услуги торговых точек». В статье пишем: «Офисные расходы», а в субстатье расписываем: «Покупка воды, канцелярии, оплата уборки».

Группа — тут всё просто: либо выбытие, либо поступление денежных средств.

Видов деятельности — три. Каждый опишем отдельно:

  1. Операционная деятельность — всё, что связано с основной деятельностью компании: поступления от клиентов, оплата поставщикам, выплата заработной платы и так далее.
  2. Финансовая деятельность — выплата дивидендов и привлечение денег в компанию, например, кредиты и займы.
  3. Инвестиционная деятельность — покупка и продажа основных средств, поступления от вложений в другие проекты.

Техническая операция — перевод денежных средств между кошельками компании.

Excel таблица для учета финансов

Читайте также:  Вклады Газпромбанка на 2021 год - условия и процентные ставки
Читайте также:  ТОП 7: Самые выгодные вклады в банке на сегодня (рейтинг 2021 г.)

Как сделать свое приложение на самоизоляции

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

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

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

Все задуманное удалось реализовать. Конечно же, пришлось дополнительно изучить массу документации по работе с базами данных и многопоточности на Андроиде, внедрить рекомендуемые «Гуглом» компоненты для построения архитектуры приложения, которое позже не будет мучительно больно поддерживать.

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

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

Итого на онлайн-курсы по мобильной разработке я потратил три недели, на создание базовой версии приложения — четыре. И курсами, и приложением занимался по вечерам после работы, по выходным и праздникам. На публикацию приложения в «Гугл-плее» ушла ровно одна неделя и 25 $.

Решение экономической задачи с помощью надстройки «поиск решения»

Функция «Поиск решения» позволяет найти наиболее рациональный способ решения экономической задачи математическими методами. Она может автоматически выполнить расчеты для задач с несколькими вводными данными при условии накладывания определенных ограничений на искомое решение.

Такими экономическими задачами могут быть:

  • расчет оптимального объема выпуска продукции при ограниченности сырья;
  • минимизация транспортных расходов на доставку продукции покупателям;
  • решение по оптимизации фонда оплаты труда.

Функция поиска решения является дополнительной надстройкой, поэтому в стандартном меню Excel мы ее не найдем. Чтобы использовать в своей работе функцию «Поиск решения», экономисту нужно сделать следующее:

  • в меню Excel выбрать путь: ФайлПараметрыНадстройки;
  • в появившемся списке надстроек выбрать «Поиск решения» и активировать эту надстройку;
  • вернуться в меню Excel и выбрать: ДанныеПоиск решения.

Задача № 9. Туристической компании необходимо организовать доставку 45 туристов в четыре гостиницы города с трех пунктов прибытия при минимально возможной сумме затрат. Для решения задачи составляем таблицу с исходными данными:

1. Количество прибывающих с каждого пункта — железнодорожный вокзал, аэропорт и автовокзал (ячейки Н6:Н8).

2. Количество забронированных для туристов мест в каждой из четырех гостиниц (ячейки D9:G9).

3. Стоимость доставки одного туриста с каждого пункта прибытия до каждой гостиницы размещения (диапазон ячеек D6:G8).

Исходные данные, размещенные таким образом, показаны в табл. 8.1.

Далее приступаем к подготовке поиска решения.

1. Создаем внизу исходной таблицы такую же таблицу для расчета оптимального количества доставки туристов при условии минимизации затрат на доставку с диапазоном ячеек D15:G17.

2. Выбираем на листе ячейку для расчета искомой функции минимизации затрат (J4) и прописываем в ячейке расчетную формулу: =СУММПРОИЗВ(D6:G8;D15:G17).

3. Заходим в меню Excel, вызываем диалоговое окно надстройки «Поиск решения» и указываем там требуемые параметры и ограничения (рис. 2):

  • оптимизировать целевую функцию — ячейка J4;
  • цель оптимизации — до минимума;
  • изменения ячейки переменных — диапазон ячеек второй таблицы D15:G17;
  • ограничения поиска решения:

– в диапазоне ячеек второй таблицы D15:G17 должны быть только целые значения (D15:G17=целое);

– значения диапазона ячеек второй таблицы D15:G17 должны быть только положительными (D15:G17>=0);

– количество мест для туристов в каждой гостинице таблицы для поиска решения должно быть равно количеству мест в исходной таблице (D18:G18 = D9:G9);

– количество туристов, прибывающих с каждого пункта, в таблице для поиска решения должно быть равно количеству туристов в исходной таблице (Н15:Н17 = Н6:Н8).

Далее даем команду найти решение, и надстройка рассчитывает нам результат оптимальной доставки туристов (табл. 8.2).

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

Решение экономической задачи с помощью формул впр и гпр

Формулы ВПР и ГПР используют для решения более сложных экономических задач. Они популярны среди экономистов, так как существенно облегчают поиск необходимых значений в больших массивах данных. Разница между формулами:

  • ВПР предназначена для поиска значений в вертикальных списках (по строкам) исходных данных;
  • ГПР используют для поиска значений в горизонтальных списках (по столбцам) исходных данных.

Формулы прописывают в общем виде следующим образом:

=ВПР(искомое значение, которое требуется найти; таблица и диапазон ячеек для выборки данных; номер столбца, из которого будут подставлены данные; [интервал просмотра данных]);

=ГПР(искомое значение, которое требуется найти; таблица и диапазон ячеек для выборки данных; номер строки, из которой будут подставлены данные; [интервал просмотра данных]).

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

Задача № 3. У экономиста есть данные в виде таблицы Excel о реализации продукции за сентябрь в натуральном измерении (декалитрах) и данные о реализации продукции в сумме (рублях) в другой таблице Excel. Экономисту нужно предоставить руководству отчет о реализации продукции с тремя параметрами:

  • продажи в натуральном измерении;
  • продажи в суммовом измерении;
  • средняя цена реализации единицы продукции в рублях.

Для решения этой задачи с помощью формулы ВПР нужно последовательно выполнить следующие действия.

Шаг 1. Добавляем к таблице с данными о продажах в натуральном измерении два новых столбца. Первый — для показателя продаж в рублях, второй — для показателя цены реализации единицы продукции.

Шаг 2. В первой ячейке столбца с данными о продажах в рублях прописываем расчетную формулу: =ВПР(B4:B13;Табл.4!B4:D13;3;ЛОЖЬ).

Пояснения к формуле:

В4:В13 — диапазон поиска значений по номенклатуре продукции в создаваемом отчете;

Табл.4!B4:D13 — диапазон ячеек, где будет производиться поиск, с наименованием таблицы, в которой будет организован поиск;

3 — номер столбца, по которому нужно выбрать данные;

ЛОЖЬ — значение критерия поиска, которое означает необходимость строгого соответствия отбора наименований номенклатуры таблицы с суммовыми данными наименованиям номенклатуры в таблице с натуральными показателями.

Шаг 3. Продлеваем формулу первой ячейки до конца списка номенклатуры в создаваемом нами отчете.

Шаг 4. В первой ячейке столбца с данными о цене реализации единицы продукции прописываем простую формулу деления значения ячейки столбца с суммой продаж на значение ячейки столбца с объемом продаж (=E4/D4).

Шаг 5. Продлим формулу с расчетом цены реализации до конца списка номенклатуры в создаваемом нами отчете.

В результате выполненных действий появился искомый отчет о продажах (табл. 3).

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

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

Решение экономической задачи с применением функции сортировки данных

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

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

Сортировку данных выполнить просто:

  • выделяем курсором столбцы таблицы;
  • заходим в меню редактора: Данные → Сортировка;
  • выбираем нужные параметры сортировки и получаем новый вид табличных данных.

Задача № 6. Экономист должен подготовить отчет о заработной плате, начисленной сотрудникам магазина, с последовательностью от самой высокой до самой низкой зарплаты.

Для решения этой задачи берем табл. 2 в качестве исходных данных. Выделяем в ней диапазон ячеек с показателями начисления зарплат (B4:D13).

Далее в меню редактора вызываем сортировку данных и в появившемся окне указываем, что сортировка нужна по значениям столбца D (суммы начисленной зарплаты) в порядке убывания значений.

Читайте также:  Факторный анализ выручки от продаж | АНАЛИТИКА ПЛЮС

Нажимаем кнопку «ОК», и табл. 2 преобразуется в новую табл. 5, где в первой строке идут данные о зарплате директора в 50 000 руб., в последней — данные о зарплате грузчика в 18 000 руб.

Решение экономической задачи с использованием функционала Автофильтр

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

В табличном редакторе Excel есть два вида фильтров:

  • автофильтр — используют для фильтрации данных по простым критериям;
  • расширенный фильтр — применяют при фильтрации данных по нескольким заданным параметрам.

Автофильтр работает следующим образом:

  • выделяем курсором диапазон таблицы, данные которого собираемся отфильтровать;
  • заходим в меню редактора: Данные → Фильтр → Автофильтр;
  • выбираем в таблице появившиеся значения автофильтра и получаем отфильтрованные данные.

Задача № 7. Из общих данных о реализации продукции за сентябрь 2020 г. (см. табл. 4) нужно выделить суммы продаж только по группе лимонадов.

Для решения этой задачи выделяем в таблице ячейки с данными по реализации продукции. Устанавливаем автофильтр из меню: Данные→Фильтр→Автофильтр. В появившемся меню столбца с группой продукции выбираем значение «Лимонад».

Для применения расширенного фильтра нужно предварительно подготовить «Диапазон условий» и «Диапазон, в который будут помещены результаты».

Чтобы организовать «Диапазон условий», следует выполнить следующие действия:

  • в свободную строку вне таблицы копируем заголовки столбцов, на данные которых будут наложены ограничения (заголовки несмежных столбцов могут оказаться рядом);
  • под каждым из заголовков задаем условие отбора данных.

Строка копий заголовков вместе с условиями отбора образуют «Диапазон условий».

Таблица в «экселе»: плюсы, минусы, подводные камни

Возвращаться к мобильным приложениям я не хотел, так как помнил, какие с ними были проблемы. Делать свое веб-приложение — тогда я зарабатывал на жизнь именно этим — было лень. К тому же не хотелось зависеть от наличия интернета и тратить время и деньги на поддержку сервера. И тут на помощь пришел старый добрый «Эксель».

Преимуществ у электронных таблиц масса:

  1. Независимость от платформы. Хочешь — фиксируй траты на телефоне с Андроидом, а хочешь — анализируй сводку на Макбуке или традиционном компьютере с Виндоус.
  2. Функциональность ограничена только фантазией.
  3. Формулы либо элементарны, либо хорошо задокументированы.
  4. Абсолютно бесплатно!

В итоге таблица обрела следующую структуру.

Лист 1. Операции. Ключевая часть всего учета. Одна операция — одна строка в таблице. Фиксирую сумму и дату операции, а категорию, счет и валюту выбираю из списков. Опционально можно указать название операции или магазина и заполнить еще пару полей для комментариев.

Поля с коэффициентом — знаком операции (плюс или минус — доход или расход) вычисляются автоматически. Также предусмотрен валютный коэффициент для случаев, когда валюта операции отличается от карты. Дополнительно для отчетов вычисляются год, месяц операции и валюта счета.

Звучит слишком сложно? Полностью с вами согласен! Наиболее утомительная часть всего учета — переводы между счетами — была реализована в виде пары операций: расходной с одного счета и доходной для другого.

Лист 2. Счета. Таблица со списком всех счетов, которая состоит из названия, текущего баланса и валюты. В первой версии было поле с начальным балансом счета, но позже для экономии места я заменил его на доходные операции. Позже дополнил таблицу полем с датами завершения действия вкладов.

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

Лист 4. Категории. Раньше назывался «Бюджет», но когда я понял, что по факту еще не дорос до этой темы, лист превратился в источник категорий. Одно время я делал сводные таблицы по месяцам и категориям, но особой пользы не нашел.

Лист 5. Ценные бумаги. На этот лист пришлось потратить больше всего времени: нужно было свести в одном месте данные по акциям, фондам и облигациям у четырех разных брокеров в разных валютах.

Все остальные листы — это эксперименты или сводки по выборкам данных с предыдущих листов. Накопленные данные об операциях по всем счетам позволяют за несколько минут узнать, как повлияла покупка кофемашины в офис на «кофейные» расходы или сколько ушло на свадьбу.

О плюсах таблицы достаточно, теперь расскажу о недостатках. Были мелкие неприятности вроде закончившихся вкладов и категорий расходов, которые утратили актуальность, например «Свадьба». Но самой серьезной проблемой, из-за которой пришлось отказаться от использования таблиц, оказалась низкая производительность ввиду большого количества вычисляемых полей. Приходилось копировать формулы в таблице операций в каждую новую строку.

Можно копировать сразу для сотни или тысячи строк, но это все равно неудобно и не меняет главного: таблица «тормозит» все больше и больше. Первый год этого не замечаешь, затем терпишь. К концу второго года накопилось пять тысяч строк операций и терпение закончилось.

Функции «если» и «счетесли»

Данные функции используют при установлении определенных условий или критериев.

Функция «СЧЕТЕСЛИ» предназначена для расчета количества ячеек по заданному критерию в формуле и имеет следующий вид:

=СЧЕТЕСЛИ(диапазон;критерий).

Функция «ЕСЛИ» позволяет сравнивать значения и в зависимости от результата выводить итог при верном или неверном сравнении. Формула выглядит следующим образом:

=ЕСЛИ(лог_выражение;[значение_если_истина];[значение_если_ложь]).

Рассмотрим пример применения данных функций (рис. 4).

Для рассматриваемого примера необходимо определить, опаздывал ли сотрудник Иванов И. И. на работу, при условии, что рабочий день согласно трудовому распорядку предприятия начинается в 9 утра. Для этого в графе «Примечание» нужно установить факт наличия опозданий. С этой целью применяем формулу:

=ЕСЛИ(G40>F40;”опоздание”;”-“), где необходимым условием к выполнению является превышение значения ячеек «G» (время фактического зафиксированного прибытия работника) над значением ячеек «F» (нормативное время прибытия).

Если неравенство выполняется, функция «ЕСЛИ» установит в ячейках «Н» — «опоздание»; если неравенство не выполняется, будет установлен прочерк, который показывает, что факт нарушения трудовой дисциплины не выявлен.

Для определения количества опозданий воспользуемся функцией «СЧЕТЕСЛИ»:

=СЧЕТЕСЛИ(H40:H47;”опоздание”) = 2, где функция отбирает ячейки в диапазоне H40:H47 со значением «опоздание» и выводит их количество. В нашем случае Иванов И. И. опоздал на работу дважды, что и посчитала указанная функция.

Дополнительно отметим еще несколько функций с критериями: «ЕСЛИОШИБКА», «СЧЕТЕСЛИМН» и «СЧЕТЗ».

«ЕСЛИОШИБКА» возвращает значение, если вычисление по формуле выдает ошибку, в противном случае — возвращает результат формулы:

=ЕСЛИОШИБКА(значение;значение_если_ошибка).

«СЧЕТЕСЛИМН» — функция, похожая на «СЧЕТЕСЛИ», единственное отличие заключается в возможности применения нескольких критериев. Если бы в рассматриваемом примере (рис. 4) не провели предварительный отбор по конкретному сотруднику и по графе 2 встречалось бы несколько сотрудников, то для определения количества опозданий для каждого сотрудника в отдельности нужно было применять функцию «СЧЕТЕСЛИМН».

«СЧЕТЗ» — наиболее простая функция среди рассмотренных, которая рассчитывает количество непустых ячеек в заданном для анализа диапазоне.

Функция «сумм»

Данная функция помогает суммировать значения нескольких ячеек. Рассмотрим пример использования этой функции (рис. 1).

A

B

C

D

E

F

G

H

3

№ п/п

Наименование

Ед. изм.

Стоимость ед. изм., руб.

Расход

Сумма, руб.

4

1

2

3

4

5

6

5

1

Материал № 1

шт.

50,00

2,00

100,00

6

2

Материал № 2

кг

101,54

0,50

50,77

7

3

Материал № 3

кг

120,00

4,00

480,00

8

4

Материал № 4

м

150,00

1,50

225,00

9

5

Материал № 5

л

200,00

0,75

150,00

10

n

11

Итого

1006,00

Рис. 1. Пример использования функции «СУММ»

Необходимо посчитать стоимость материальных расходов, затраченных на единицу выпущенной продукции, если известна стоимость закупки единицы измерения и фактический расход каждого вида материала на изготовление единицы продукции (графы 4 и 5 таблицы, представленной на рис. 1). Итог по каждой позиции материала выведен в графе 6 путем перемножения фактического расхода на стоимость закупки.

«Итого» рассчитывают сложением всех подытогов по каждой позиции материала. Для этого используют функцию «СУММ» и выделяют диапазон ячеек с необходимыми значениями данных (в нашем случае — графа 6, которой в MS Excel соответствует столбец «Н»). Тогда формула приобретет следующий вид:

= СУММ(H5:H9), где H5:H9 — диапазон данных по графе 6 от материала № 1 до материала № 5.

Когда пользователю нужно рассчитать сумму значений ячеек или применить иную функцию, но при этом получить результат расчетов с округлением (например, без копеек), применяют функции «ОКРУГЛ», «ОКРУГЛВВЕРХ» и «ОКРУГЛВНИЗ».

Как правило, эти функции не используют как самостоятельные, чаще их применяют в комплексе с другими функциями (например, с «СУММ»). В нашем случае по материалу № 2 сумма составляет 50,77 руб. (графа 6). Составим формулу для расчета итоговой суммы с учетом округления:

=ОКРУГЛ(СУММ(H5:H9);0), где «0» — число разрядов для округления.

Справочная информация о форматировании ячеек:

1. Чтобы установить количество знаков после запятой, нужно кликнуть правой кнопкой мыши по необходимой ячейке и выбрать «Формат ячеек», где определяется категория формата: числовой, текстовый, процентный, дата и др. (в нашем случае для граф 4–6 нужен числовой формат), а затем устанавливается количество десятичных знаков (для рассматриваемого примера — 2).

Дополнительно можно установить флажок на «Разделитель групп разрядов». Это обеспечит представление чисел, превышающих тысячу, с соответствующими пробелами для лучшей визуализации информации.

2. Чтобы применить конкретный формат одной ячейки к другим ячейкам, используют функцию «Формат по образцу», представленную на вкладке «Главная» основного меню.

3. Для выравнивания информации в ячейке можно обратиться к «Формату ячеек» и во всплывающем диалоговом окне выбрать «Выравнивание» или воспользоваться одноименной функцией во вкладке «Главная» основного меню (рис. 2). Данная функция позволяет определить направление (ориентацию) текста, его расположение в ячейке. При выборе «перенос по словам» текст ячейки не будет выходить за ее пределы.

Оцените статью
Adblock
detector