Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
Расчет доли затрат от выручки в сводной таблице, сделать автоматический расчет доли затрат от выручки в сводной таблице
 
#1
03.08.2017 12:37:28
Добрый день!

Прошу помочь в решении проблемы.

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

Попытался сделать расчет дополнительным вычислением, но по статьям "нижнего уровня" выдает ошибку. Считает долю только в итогах.
Способ добавить доп колонку в базе тоже не помогает т.к:
- Если смотреть свод по концепциям (отдельный лист), то он складывает суммы по точкам и получается неверно;
- Общий итог за год тоже просто суммируется, а не рассчитывается.

В реальной базе уровней статей и концепций еще больше.
 
 
 
#2
03.08.2017 12:46:24
Сделал еще вариант вычисляемым полем.
Но он не верно считает итоги, т.к. делит на сумму чисел из графы выручка.
Пример в приложении.

Как побороть это все?)
 
 
 
#3
03.08.2017 12:51:49
Возможно, в случае с вычисляемым полем поможет способ, чтобы при расчете сумма выручки делилась на кол-во записей (СЧЁТ, СЧЕТЗ):

Сумма/(Выручка/СЧЁТ(Выручка))

Но почему-то эта формула не срабатывает в сводной.
 
 
 
#4
03.08.2017 13:23:53
Какой версией Excel пользуетесь?
Рассматривается вариант использования формул Power Pivot?
Вот горшок пустой, он предмет простой...
 
 
 
#5
03.08.2017 13:45:15
Excel 2010. Он их поддерживает?
 
 
 
#6
03.08.2017 13:53:59
Если вот это установить, Установка PowerPivot для Excel то работает.
Вот горшок пустой, он предмет простой...
 
 
 
#7
03.08.2017 14:13:18
Короче говоря, если установите, то вам потребуется затащить исходные данные в модель данных, после чего в модели данных создать меру с вот такой простой формулой:
Код
Доля затрат от выручки:=DIVIDE(SUM([Сумма]);SUM([Выручка]))
Эту меру тащите в поля значений сводной таблицы и будет вам счастье. Только сразу предупреждаю, что я это все проделал на Excel 2016. 2010-го с PP под рукой нема.
Изменено: PooHkrd - 03.08.2017 14:14:21
Вот горшок пустой, он предмет простой...
 
 
 
#8
03.08.2017 14:44:59
PooHkrd, спасибо.

Я не знаком с PP. Разобраться, впринципе, не сложно.
Но есть ряд вопросов:
1. в случае использования PP получится ли у меня обновлять эту сводную макросом - просто "обновить" по сводной? Или надо каждый раз заходить и обновлять ее через PP? Файлов с подобными таблицами около 100 и заходить в каждый не вариант.
2. Будет ли работать этот расчет в сводной из PP у других пользователей? Они могут работать и через 2007 офис.
 
 
 
#9
03.08.2017 16:03:22
Все сделал, но в excel 2010 нет добавления "Меры".

https://support.office.com/ru-ru/article/%D0%9C%D0%B5%D1%80%D1%8B-%D0%B2-Power-Pivot-86484821-a324-4...
 
 
 
#10
03.08.2017 16:31:45
Цитата
Di-Kiy написал:
но в excel 2010 нет добавления "Меры".
да ладно.
вон она, вторая, справа от большой зеленой кнопки
F1 творит чудеса
 
 
 
#11
03.08.2017 17:02:11
Да,нашел, настроил, но все-равно криво все считается. Если развернуть до статей - там ошибки.
 
 
 
#12
03.08.2017 17:14:14
Ну тогда выкладывайте пример с моделькой. Я вроде как в 2016-ом вашу модельку смогу открыть, вот только потом, вы мою в 2010-ом не откроете.
Вот горшок пустой, он предмет простой...
 
 
 
#13
03.08.2017 17:23:47
Добавил лист Pivot с Вашей формулой.
Все нормально считает, кроме итогов по статьям.
 
 
 
#14
03.08.2017 17:41:34
Стоп. Это я, значит условие не совсем понял.
Что означает значение в столбце Выручка для строки по статье затраты?
Как бы формулу Сумма делить на Выручку вы предложили самостоятельно, я вам лишь сказал, как это можно реализовать.
Опишите подробнее какой результат нужно получить в полях значений в зависимости от значения в столбцах Статья1 и Статья2?
Вот горшок пустой, он предмет простой...
 
 
 
#15
03.08.2017 18:14:05
Смысл: поделить затраты на выручку. В зависимости от среза - по точкам/по концепциям - это значение меняется.
Если рассматривать Статья1 и Статья2, то в Статье1 должна получаться сумма процентов по Статье2. Сейчас не так.
Графу "Выручка" я добавил для удобства расчета. Возможно и без нее можно обойтись. В сводной есть дополнительный расчет "Доля". Он делит затрыты на выручка, но для Статья2 выдает ошибку. Это можно увидеть в первом файле, который я прикладывал.
 
 
 
#16
03.08.2017 18:37:02
Т.е. нужно поделить сумму значений по столбцу Сумма и Статьям Затраты1 или Затраты2 на сумму значений по столбцу Сумма и Статье Выручка?
Вот горшок пустой, он предмет простой...
 
 
 
#17
04.08.2017 09:12:55
Цитата
PooHkrd написал:
Т.е. нужно поделить сумму значений по столбцу Сумма и Статьям Затраты1 или Затраты2 на сумму значений по столбцу Сумма и Статье Выручка?
Именно так. PP это умеет?
 
 
 
#18
04.08.2017 09:24:13
Да, но я смогу помочь только ближе к вечеру, пока не выходит каменный цветок...
Возможно кто-то из местных зубров подключится по-раньше.
Пока, что у меня вышло вот такое
Код
Мера 1:=DIVIDE(SUMX(FILTER('Проба';[Статья1]<>"Выручка");[Сумма Сумма]);SUMX(ADDCOLUMNS(FILTER('Проба';[Статья1]="Выручка");"Выр";[Сумма Сумма]);[Выр]))
Но это не то, тут нужно по-другому.
Есть у меня подозрение, что если Максим Зеленский или АндрейВГ придут, то поржут, и напишут формулу в15 символов.
Изменено: PooHkrd - 04.08.2017 09:28:27
Вот горшок пустой, он предмет простой...
 
 
 
#19
04.08.2017 09:30:45
PooHkrd, спасибо, что помогаете.
Вроде, задача стандартная для финансов, но в сводную таблицу ее зашить довольно проблематично.
 
 
 
#20
04.08.2017 09:51:36
Да мне самому что-то подобное скоро потребуется считать, так что я не совсем уж бескорыстен  8)  
Изменено: PooHkrd - 20.12.2022 19:26:56
Вот горшок пустой, он предмет простой...
 
 
 
#21
04.08.2017 15:29:24
ну, на 15 символов не получилось  :D
Код
=
DIVIDE (
    CALCULATE (
        SUM ( 'Проба'[Сумма] );
        FILTER (
            'Проба';
            'Проба'[Статья1] <> "Выручка"
                || 'Проба'[Статья2] <> "Выручка"
        )
    );
    CALCULATE (
        SUM ( 'Проба'[Сумма] );
        'Проба'[Статья1] = "Выручка";
        'Проба'[Статья2] = "Выручка"
    )
)

но, конечно, располагать так в одном столбце и выручку, и затраты нехорошо, тем более, что у вас там выручку нужно фильтровать в двух столбцах. Сделали бы доп.столбец, который относил бы строку либо к затратам, либо к выручке, и всё было бы проще.
Изменено: Максим Зеленский - 04.08.2017 15:35:47
F1 творит чудеса
 
 
 
#22
04.08.2017 16:01:50
Максим, спасибо огромное!

Работает!

Подскажите, пожалуйста, как правильно написать формулу если выручка в отдельном столбце.
Пример прилагаю.
 
 
 
#23
04.08.2017 17:29:33
Столкнулся еще с одной проблемой.
В настоящем файле расходы стоят с минусом - чтобы итоги правильно считались.

Получается, что формула считает % от выручки по итоговым строкам не верно. Выделил желтым.
Может это можно как-то починить?

Пример прикладываю
Изменено: Di-Kiy - 04.08.2017 17:33:23
 
 
 
#24
04.08.2017 18:17:46
Формула считает как раз корректно:
Сумма затрат: 5 550 000
Выручка: 13 050 000
Доля 42,5%
Вы же своей формулой посчитали соотношение разности между выручкой и затратами к выручке.

З.Ы. А чтобы проценты были положительными, просто поставьте перед формулой знак "-".
Изменено: PooHkrd - 04.08.2017 18:20:23
Вот горшок пустой, он предмет простой...
 
 
 
#25
04.08.2017 18:22:27
Цитата
Di-Kiy написал:
В настоящем файле
ну и зачем давать ненастоящий? чье время экономите так?
F1 творит чудеса
 
 
 
#26
04.08.2017 18:32:17
Цитата
Di-Kiy написал:
если выручка в отдельном столбце.
в вашем случае надо в моей формуле в 12 строке (вот тут: SUM ( 'Проба'[Сумма] ); ) поменять название столбца, с Сумма на Выручка
F1 творит чудеса
 
 
 
#27
04.08.2017 18:36:21
Цитата
PooHkrd написал:
Формула считает как раз корректно:
Сумма затрат: 5 550 000
Выручка: 13 050 000
Доля 42,5%
Вы же своей формулой посчитали соотношение разности между выручкой и затратами к выручке.

З.Ы. А чтобы проценты были положительными, просто поставьте перед формулой знак "-".
Да, верно, затраты именно так и выходят - 42,5%. Но итого прибыль "Точка1 Итог" составляет 7500000 или 57% от выручки.
Понимаю, что в теме стоит только "доля затрат от выручки", но для отчета нужно чтобы она все подитоги считала верно.
 
 
 
#28
04.08.2017 18:39:03
Цитата
Максим Зеленский написал:
ну и зачем давать ненастоящий? чье время экономите так?
Настоящий весит 500 мб =) и содержит намного больше уровней статей.
Я формулу в нем повторил - дописал недостающие уровни. Но вот подитоги все испортили.
Долю итоговой прибыли от выручки она считает не верно, к сожалению.
Может есть смысл добавить графу "Выручка"?
 
 
 
#29
04.08.2017 18:46:07
Di-Kiy, так какой файл последний-правильный? У меня всё считает как надо, вроде бы. Просто у вас данные повторяются
Цитата
Di-Kiy написал:
Настоящий весит 500 мб =)
не нужно все данные, нужны данные в том виде, в каком они в реальности. :)
F1 творит чудеса
 
 
 
#30
04.08.2017 18:49:59
сделайте две отдельные меры:
Код
Сумма затрат = CALCULATE (
        SUM ( 'Проба'[Сумма] );
        FILTER (
            'Проба';
            'Проба'[Статья1] <> "Выручка"
                || 'Проба'[Статья2] <> "Выручка"
        )
    )

и
Код
Сумма выручки = CALCULATE (
        SUM ( 'Проба'[Сумма] );
        'Проба'[Статья1] = "Выручка";
        'Проба'[Статья2] = "Выручка"
    )

и играйтесь с ними:
Код
Доля затрат = DIVIDE([Сумма затрат];[Сумма выручки])

Доля прибыли в выручке = 1-DIVIDE([Сумма затрат];[Сумма выручки])

и так далее
F1 творит чудеса
 
 
 
Страницы: 1 2 След.
Читают тему
Наверх