Как посчитать сальдо в экселе

Как посчитать сальдо в экселе

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

Расчет сальдо

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

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

1. Настройка таблицы, как в примере ниже. Пример данных:

A	B	C
1	Вклады	Выплаты	Остаток на счете
2	1000	625	=СУММ(A2-B2)
3	1245	740	=СУММ(C2;A3-B3)

1. Введите сумму депозитов и средств на счет в пустые строки непосредственно под существующими данными. Например, если вы использовали пример выше, введите вклады в A4, A5 и так далее, а затем средства будут сдвегались в B4, B5 и так далее.
2. Разместив формулу с накладным балансом в новые строки, выберем последнюю ячейку в столбце «Баланс» и дважды щелкните маркер заполнения. Например, если вы использовали пример выше, нужно выбрать ячейку C3, а затем дважды щелкнуть ее ручку заполнения, чтобы распространить формулу на все новые строки, содержащие значения депозита и вывода средств.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Вычисление выражений на последнюю / первую даты периода в Power BI и Power Pivot: DAX функции группы CLOSINGBALANCE и OPENINGBALANCE (YEAR, QUARTER, MONTH)

до конца новогодней распродажи осталось:

» Вычисление выражений на последнюю / первую даты периода в Power BI и Power Pivot: DAX функции группы CLOSINGBALANCE и OPENINGBALANCE (YEAR, QUARTER, MONTH)

Содержание статьи: (кликните, чтобы перейти к соответствующей части статьи):

• DAX функции CLOSINGBALANCEYEAR, CLOSINGBALANCEQUARTER и CLOSINGBALANCEMONTH в Power BI и Power Pivot
• DAX функции OPENINGBALANCEYEAR, OPENINGBALANCEQUARTER и OPENINGBALANCEMONTH в Power BI и Power Pivot
• Пример формул на основе функций группы CLOSINGBALANCE (YEAR, QUARTER и MONTH)

Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В этой статье мы продолжаем изучать функции категории time intelligence (логики операций с датами и временем) в DAX, а конкретно, разберем функции CLOSINGBALANCEYEAR, CLOSINGBALANCEQUARTER, CLOSINGBALANCEMONTH и OPENINGBALANCEYEAR, OPENINGBALANCEQUARTER, OPENINGBALANCEMONTH, которые позволяют вычислить выражение на последнюю / первую даты года, квартала и месяца в Power BI и Power Pivot.

Данные функции очень хорошо подходят для того, чтобы в отчетах Power BI рассчитать сальдо (по балансу), остатки (запасов, товаров) на конец или начало нужных периодов (год, квартал, месяц). Как еще говорят, сделать снимки какого-либо показателя или измерения (например, баланса счета) на определенную дату.

Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.

Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».

Да, и еще один момент, в рамках новогодней распродажи до 8 января 2024 г. у Вас имеется возможность приобрести большой, пошаговый видеокурс «DAX — это просто» со скидкой 60% (вместо 10000, всего за 4000 руб.)

В этом видеокурсе язык DAX преподнесен как простой конструктор, состоящий из нескольких блоков, которые имеют свое определенное, конкретное предназначение. Сочетая различными способами эти блоки, Вы, при помощи конструктора формул DAX, с легкостью сможете решать любые (простые или сложные) аналитические задачи.

Итак, пользуйтесь этой возможностью, заказывайте курс «DAX — это просто» со скидкой 60% (до 8 января 2024 г.): узнать подробнее

до конца новогодней распродажи осталось:

DAX функции CLOSINGBALANCEYEAR, CLOSINGBALANCEQUARTER и CLOSINGBALANCEMONTH в Power BI и Power Pivot

CLOSINGBALANCEYEAR () — вычисляет заданное выражение на последнюю дату года текущего контекста.
CLOSINGBALANCEQUARTER () — вычисляет заданное выражение на последнюю дату квартала текущего контекста.
CLOSINGBALANCEMONTH () — вычисляет заданное выражение на последнюю дату месяца текущего контекста.

CLOSINGBALANCEYEAR (Выражение; [Дата]; Фильтр; "Конец Года") CLOSINGBALANCEQUARTER (Выражение; [Дата]; Фильтр) CLOSINGBALANCEMONTH (Выражение; [Дата]; Фильтр)

• Выражение — выражение, возвращающее единственное скалярное значение
• [Дата] — столбец из дат или выражений, возвращающих даты
• Фильтр — (необязательный параметр) необходимые фильтры для вычисления выражения
• «Конец Года» — (необязательный параметр) текстовая дата, записанная в виде «01/06» («день/месяц»). Определяет дату окончания года (по умолчанию «31/12»)

DAX функции OPENINGBALANCEYEAR, OPENINGBALANCEQUARTER и OPENINGBALANCEMONTH в Power BI и Power Pivot

OPENINGBALANCEYEAR () — вычисляет заданное выражение на первую дату года текущего контекста.
OPENINGBALANCEQUARTER () — вычисляет заданное выражение на первую дату квартала текущего контекста.
OPENINGBALANCEMONTH () — вычисляет заданное выражение на первую дату месяца текущего контекста.

OPENINGBALANCEYEAR (Выражение; [Дата]; Фильтр; "Конец Года") OPENINGBALANCEQUARTER (Выражение; [Дата]; Фильтр) OPENINGBALANCEMONTH (Выражение; [Дата]; Фильтр)

• Выражение — выражение, возвращающее единственное скалярное значение
• [Дата] — столбец из дат или выражений, возвращающих даты
• Фильтр — (необязательный параметр) необходимые фильтры для вычисления выражения
• «Конец Года» — (необязательный параметр) текстовая дата, записанная в виде «01/06» («день/месяц»). Определяет дату окончания года (по умолчанию «31/12»)

Пример формул на основе функций группы CLOSINGBALANCE (YEAR, QUARTER и MONTH)

Рассмотрим пример формул на основе DAX функций категории time intelligence CLOSINGBALANCEYEAR и CLOSINGBALANCEQUARTER.

В модели данных Power BI Desktop имеется исходная таблица «Сальдо счета», в которой на последнюю дату каждого месяца (с января 2018 по ноябрь 2018) прописан остаток баланса на счете организации. При чем, последняя дата баланса 20 ноября 2018 года, то есть, месяц еще не закончился и, соответственно, 4 квартал и год, также, еще не закончены:

Задача — создать отчет в Power BI, который бы показывал остаток (сальдо) средств на счете организации на конец каждого квартала и года, а также, на последнее текущее число (в данном примере, последнее текущее число — 20 ноября 2018).

Для этого, воспользуемся нашими DAX функциями, которые мы рассматриваем в этой статье — CLOSINGBALANCEYEAR, CLOSINGBALANCEQUARTER, так как они способны вычислить выражение на последнюю известную дату года и квартала. Составим на основе них соответствующие формулы расчета сальдо баланса счета на последние даты периодов (года и квартала):

Сальдо Год = CLOSINGBALANCEYEAR ( SUM ('СальдоСчета'[БалансСчета]); 'СальдоСчета'[Месяц] ) Сальдо Квартал = CLOSINGBALANCEQUARTER ( SUM ('СальдоСчета'[БалансСчета]); 'СальдоСчета'[Месяц] )

В первом параметре этих формул мы прописали само выражение, которое нам нужно вычислить — в данном случае, это сумма по столбцу [Баланс Счета], которую мы вычислили при помощи DAX функции SUM. Но, на самом деле, здесь сумма не высчитывается по всему столбцу, а только по одной его строчке — соответствующей последней известной дате квартала и года. И это ограничение на выражение накладывают уже сами функции CLOSINGBALANCEYEAR, CLOSINGBALANCEQUARTER на основе тех дат, которые поданы во втором параметре этих функций.

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

В итоге, в Power BI мы можем наблюдать следующие визуализации сальдо баланса счета на конец квартала и года:

Теперь, рассмотрим еще один пример формулы на основе DAX функции CLOSINGBALANCEMONTH.

В модели данных Power BI имеется таблица «Товары», в которой отображены по определенным датам остатки товара на складе за февраль, март и начало апреля:

Задача — создать отчет в Power BI, показывающий в реальном времени текущие остатки товара за месяц, а также, остатки товара на конец прошлых месяцев.

По сути, формула на основе функции CLOSINGBALANCEMONTH языка DAX будет идентична тем формулам, которые мы рассматривали выше:

Остаток На Конец Месяца = CLOSINGBALANCEMONTH ( SUM ('Товары'[ОстатокТовара]); 'Товары'[Дата] )

Результатом выполнения этой формулы на основе CLOSINGBALANCEMONTH, в Power BI Desktop будет следующая визуализация остатков товара на складе на конец месяца:

На этом, с разбором DAX функций CLOSINGBALANCEYEAR, CLOSINGBALANCEQUARTER, CLOSINGBALANCEMONTH и OPENINGBALANCEYEAR, OPENINGBALANCEQUARTER, OPENINGBALANCEMONTH, вычисляющих выражение на последнюю / первую даты года, квартала, месяца в Power BI и Power Pivot, в этой статье все.

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

Также, напоминаю Вам, в рамках новогодней распродажи до 8 января 2024 г. у Вас имеется шикарная возможность приобрести большой, пошаговый видеокурс «DAX — это просто» со скидкой 60% (вместо 10000, всего за 4000 руб.)

В этом видеокурсе язык DAX преподнесен как простой конструктор, состоящий из нескольких блоков, которые имеют свое определенное, конкретное предназначение. Сочетая различными способами эти блоки, Вы, при помощи конструктора формул DAX, с легкостью сможете решать любые (простые или сложные) аналитические задачи.

Итак, пользуйтесь этой возможностью, заказывайте курс «DAX — это просто» со скидкой 60% (до 8 января 2024 г.): узнать подробнее

До конца распродажи осталось:

Пожалуйста, оцените статью:

1. 5
2. 4
3. 3
4. 2
5. 1

Успехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»

Если у Вас появились какие-то вопросы по материалу данной статьи, задавайте их в комментариях ниже. Я Вам обязательно отвечу. Да и вообще, просто оставляйте там Вашу обратную связь, я буду очень рад.

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

Понравился материал статьи?
Добавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D

до конца новогодней распродажи осталось:

Что еще посмотреть / почитать?

Создание сводной таблицы в DAX: функция SUMMARIZE в Power BI и Power Pivot

Логические функции в DAX: IF, SWITCH, TRUE, FALSE, AND, OR, NOT в Power BI (Power Pivot)

Формулы и функции DAX в Power BI и Power Pivot: правила форматирования

Добавить комментарий

до конца новогодней распродажи осталось:

Связаться с нами: info@biprosto.ru Copyright © Проект «BI — это просто» , 2017 — 2024 ИП Будуев Антон Сергеевич. ОГРНИП 315745600033176

Оставляя персональные данные (email, имя, логин) в формах на страницах данного сайта «BI — это просто», Вы автоматически подтверждаете свое согласие на обработку своих персональных данных

Данный сайт «BI — это просто» при своей работе использует файлы cookie. Продолжая использовать сайт, Вы даете свое согласие на работу с этими файлами.

Справочник DAX функций для Power BI и Power Pivot

на русском языке с подробными примерами формул на практике

• ищете подробное описание DAX функций для Power BI или Power Pivot на русском языке
• нуждаетесь в примерах формул и их демонстрации на практике
• устали разбираться с функциями самостоятельно
• тратите огромное количество времени на создание формул методом «тыка»

то, справочник DAX функций для Power BI и Power Pivot — это то, что Вам нужно!

+ БОНУС (видеокурс по DAX)

Справочник DAX функций для Power BI и Power Pivot

на русском языке с подробными примерами формул на практике

+ БОНУС: [экспресс-видеокурс] Быстрый старт в языке формул DAX для Power BI и Power Pivot

Как посчитать сальдо в экселе

Все еще используете устаревший браузер? Пора обновить его, чтобы использовать все возможности Осинки! Обновить браузер

Фев 14, 2008 11:04 | Формулы Excel

Девочки нужна помощь в составлении формулы.
Формула- это сальдо на начало по Дебету минус по кредиту плюс обороты по дебету минус обороты по кредиту). Но так ,чтобы положительное значение формулы находилось в одном столбце, а отрицательное значение этой же формулы- в другом столбце. Я говорю о Сальдо на конец периода- дебет и кредит. Как составить такую формулу?

Я попыталась, но увы ничего не получилось:

В ячейке по Дт сальдо на конец по дебету

В ячейке по Кт сальдо на конец по кредиту

=ЕСЛИ((B3-C3+E3-G3)понимаю, что во второй формуле чего не хватает, но не могу додумкать.
Если кто сможет помогите.

Фев 14, 2008 11:41

Irinas, я проверила Вашу формулу — все нормально, она работает.
Может Вас смущает сообщение «ЛОЖЬ» в той ячейке, где нет цифрового результата?
Тогда можете доделать формулы, чтобы вместо сообщения «ЛОЖЬ» получать например «0»
Вот, так:
=ЕСЛИ((B3-C3+E3-G3)>=0; (B3-C3+E3-G3);0)

Фев 14, 2008 11:45

Леля_Киев точно смущала ЛОЖЬ. сейчас попробую. СПАСИБО ОГРОМНОЕ!

Фев 14, 2008 11:52

Или Вам необходимо, чтобы во второй ячейке отображался отрицательный результат, но со знаком «+».
Тогда измените вторую формулу так:
=ЕСЛИ((B3-C3+E3-G3)(-B3+C3-E3+G3))
или же с «О» вместо «ЛОЖЬ»
=ЕСЛИ((B3-C3+E3-G3)(-B3+C3-E3+G3);0)

Фев 14, 2008 11:54

Пока я набирала для Вас второе сообщение, у Вас все получилось
Удачи!

Фев 14, 2008 11:55

Лёлечка спасибо, я всё поняла. подарок есть подруге на 8 марта виде
таблиц.
Пошла изучать про перенос сальдо из месяца в месяц.