Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
Функция НАИБОЛЬШИЙ в сводной таблице
 
#1
15.01.2018 14:53:01
Доброго времени суток!
Подскажите как в сводной таблице использовать функцию НАИБОЛЬШИЙ.

Есть значения количества товаров по месяцам, цель найти 3 наибольших значения и получить из них среднее.
 
 
 
#2
15.01.2018 14:59:26
Пож-та изучите п.2,3 Правил
Неизлечимых болезней нет, есть неизлечимые люди.
 
 
 
#3
15.01.2018 17:03:15
думал хватит и совета, но с примером конечно будет лучше - во вложении пример книги

PS список позиций номенклатыры и недель постоянно обновляется
 
 
 
#4
15.01.2018 17:37:31
Цитата
muxey написал: но с примером конечно будет лучше
Это вы так думаете, а на деле - ясности нет. Покажите в файле в каком виде, исходя из данных, вы желаете видеть КОНЕЧНЫЙ результат. Потому как одно накладывается на другое, а в сводной свои заморочки и, может оказаться, что обойтись можно и без нее... ;)
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
 
 
#5
15.01.2018 17:50:14
muxey,  как понял...
Код
=СРЗНАЧ(НАИБОЛЬШИЙ("ДИАПАЗОН В КОТОРОМ ИСКАТЬ";{1;2;3}))
Изменено: a.i.mershik - 15.01.2018 17:50:25
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#6
15.01.2018 18:07:58
Цитата
muxey написал:
найти 3 наибольших значения и получить из них среднее
за какой период?
Неизлечимых болезней нет, есть неизлечимые люди.
 
 
 
#7
15.01.2018 20:17:46
дополнил приблизительно как это должно выглядеть

в том то и дело, что эти данные должны пересчитываться при изменении сводной таблицы: изменились периоды и/или номенклатура в сводной таблице - вычисляемые поля пересчитались
 
 
 
#8
16.01.2018 11:59:30
Форумчане, есть какие то идеи/предложения, уже всю голову сломал...
 
 
 
#9
16.01.2018 13:35:35
Вариант на Power Pivot. Потестируйте - на вскидку, вроде работает.
Вот горшок пустой, он предмет простой...
 
 
 
#10
16.01.2018 14:04:46
Доброе время суток.
Цитата
PooHkrd написал:
вроде работает.
Да, вроде. Но могут быть проблемы.
 
 
 
#11
16.01.2018 14:16:21
На всякий случай, для помогающих, кросс темы
http://www.excelworld.ru/forum/2-36874-1#242656
 
 
 
#12
16.01.2018 14:19:36
при помощи Power Pivot и DAX:
Код
=
AVERAGEX (
    TOPN (
        3;
        VALUES ( 'Таблица1'[Неделя] );
        CALCULATE ( SUM ( 'Таблица1'[Количество] ) )
    );
    CALCULATE ( SUM ( 'Таблица1'[Количество] ) )
)
F1 творит чудеса
 
 
 
#13
16.01.2018 14:33:20
Che79, спасибо за внимательность, отчаялся тут и пошел в другой форум (

Андрей VG, PooHkrd, спасибо, пока не совсем понял как...пытаюсь осмыслить

Максим Зеленский, этот код необходимо ввести через вставку функций в  PP или это добавление столбца в исходную таблицу (ссори, если вопрос глупый, я только начинаю изучать PP)
 
 
 
#14
16.01.2018 14:35:08
Цитата
Максим Зеленский написал:
при помощи Power Pivot и DAX:
Максим, честно говоря, не увидел разницы. Положим, что в примере rowNum - Неделя.
 
 
 
#15
16.01.2018 14:48:46
Цитата
Андрей VG написал:
в примере rowNum - Неделя.
в исходных одной неделе соответствует несколько строк для одной и той же номенклатуры. Если я правильно понял задачу, нужно посчитать топ-3 недели, и уже для недельных агрегированных сумм считать среднее.
согласен, добавление второго ранга - совершенно правильно, поправил код:
Код
=
AVERAGEX (
    TOPN (
        3;
        VALUES ( 'Таблица1'[Неделя] );
        CALCULATE ( SUM ( 'Таблица1'[Количество] ) ); 0;
        'Таблица1'[Неделя]; 1
    );
    CALCULATE ( SUM ( 'Таблица1'[Количество] ) )
)

Впрочем, не знаю, почему, но даже без добавления второго ранга по id у меня в исходном примере посчитало нормально... DAX иногда удивляет :)

muxey, это код для меры (вычисляемого поля) Power Pivot, не для вычисляемого столбца.
Смотрите в примере
F1 творит чудеса
 
 
 
#16
16.01.2018 14:56:17
Цитата
Максим Зеленский написал:
Если я правильно понял задачу, нужно посчитать топ-3 недели, и уже для недельных агрегированных сумм считать среднее.
Все верно, но еще раз уточню условие:
если выбраны данные по неделям, с 1 по N, то необходимо найти 3 наибольших значения во всем диапазоне выбранных недель и из них рассчитать среднее значение
аналогично и по месяцам, если выбраны месяцы в качестве группировки
 
 
 
#17
16.01.2018 15:06:39
Цитата
muxey написал: ... еще раз уточню условие...
off На колу мочало - начинай сначала... ;)
См. вариант по 5 наибольшим.
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
 
 
#18
16.01.2018 15:17:08
Цитата
Z написал:
off На колу мочало - начинай сначала...
См. вариант по 5 наибольшим.
не то слово, решил облегчить себе труд, создал кучу проблем соседям по форуму)

на вашем скрине вроде значения сходятся, но с чем их есть - т.е. как они из чего получаются...
я тут реально взвесил, вариант, когда нужно будет использовать в расчетах Неделю будут единичны, основная масса вычислений будет по Месяцу
 
 
 
#19
16.01.2018 15:20:05
Цитата
Андрей VG написал:
Да, вроде. Но могут быть проблемы.
А что не так? Почему так не сработает? Вроде как сводная передает выражению фильтры, и для каждой ячейки независимо от группировки в столбцах по  неделе/месяцу сформируется выборка, из которой остается отобрать 3 максимальных и уже посчитать по ним среднее.
Код
Среднее_от_ТОП_3:=
CALCULATE(
    AVERAGE([Количество]);
    TOPN(3;
                'Таблица1';
                'Таблица1'[Количество]))
Изменено: PooHkrd - 16.01.2018 15:48:26
Вот горшок пустой, он предмет простой...
 
 
 
#20
16.01.2018 15:51:09
Цитата
PooHkrd написал:
А что не так? Почему так не сработает?
во-первых, вот здесь у вас будет среднее по первым трем строкам, а не по первым трем неделям/месяцам:
Цитата
PooHkrd написал:
AVERAGE([Количество])
во-вторых Андрей VG имел ввиду, что если у вас первые строки такие: 5,4,4,1,1,1 то TOPN(3,......) выберет их все - так как они будут ties
F1 творит чудеса
 
 
 
#21
16.01.2018 15:53:24
Цитата
muxey написал:
я тут реально взвесил, вариант, когда нужно будет использовать в расчетах Неделю будут единичны, основная масса вычислений будет по Месяцу
тогда нужен индикатор месяца в виде столбца (например, =год*100+номер_месяца) и вместо столбца Неделя в формуле использовать этот столбец Месяц.
F1 творит чудеса
 
 
 
#22
16.01.2018 16:01:31
Максим Зеленский,
я правильно понимаю, что теперь простым способом через вкладку Анализ вычисляемые поля я не создам и все через PP?
какую из 2-х функций (мер) Average или One Rank лучше использовать?
 
 
 
#23
16.01.2018 16:07:00
muxey, да, всё через PP. Обычная сводная так не умеет.
Используйте Average (не One Rank)
F1 творит чудеса
 
 
 
#24
16.01.2018 16:18:39
Максим Зеленский, ну, про первые три строки, это я так задачу понял из описания в первом посте, что ТС нужно посчитать среднее из ТОП-3 для каждой ячейки в сводной.
А вот про то, что TOPN собирает строки именно по значениям, а не по количеству - это упустил (надо лучше читать примечания). Вот только не очень согласен с вами по вашему примеру, т.к. исходя из Rermarks на сайте MS:
Цитата
If there is a tie, in order_by values, at the N-th row of the  table, then all tied rows are returned. Then, when there are ties at the  N-th row the function might return more than n rows.
Т.е. для вашего примера 5,4,4,1,1,1 как раз выберется 3 значения 5,4,4. А вот если пример будет такой 5,4,1,1,1 то тогда выберутся всезначения, т.к. единички оказались на границе указанной в первом аргументе TOPN. Или я как-то не так понял басурманский?
Спасибо.
Изменено: PooHkrd - 16.01.2018 16:24:16
Вот горшок пустой, он предмет простой...
 
 
 
#25
16.01.2018 16:35:17
Цитата
PooHkrd написал:
А вот если пример будет такой 5,4,1,1,1 то тогда выберутся всезначения
Коллега, бывает, Максим в спешке описался. Суть-то проблемы вы уловили правильно.
Цитата
Максим Зеленский написал:
у меня в исходном примере посчитало нормально... DAX иногда удивляет
Просто не было последних равных - свезло :)  Но наедятся на везение...
Изменено: Андрей VG - 16.01.2018 16:35:43
 
 
 
#26
16.01.2018 16:44:04
Андрей VG, а напишите, плиз, вашу формулу. Нету у меня PBI чтобы ваш пример открыть.
Изменено: PooHkrd - 16.01.2018 16:44:15
Вот горшок пустой, он предмет простой...
 
 
 
#27
16.01.2018 17:01:31
Цитата
Андрей VG написал:
Просто не было последних равных
в том и беда, что есть :)  вот тут я уже и DAX Studio крутил, но так и не понял, а времени разобраться детально сейчас нет. У меня в примере это видно четко.

PS А, не, точно. Я не туда посмотрел :) вот балда
Изменено: Максим Зеленский - 16.01.2018 17:02:55
F1 творит чудеса
 
 
 
#28
16.01.2018 17:03:49
Цитата
PooHkrd написал:
ли я как-то не так понял басурманский?
правильно. у меня ошибк
F1 творит чудеса
 
 
 
#29
17.01.2018 08:38:27
так, вы меня запутали...значит частный случай показал верное значение, но формула не верна?

дополню, как в рамках одной недели, так и соответственно месяца, встреча идентичных позиций с одной датой крайне велика (вернее такое есть постоянно)

правильный алгоритм расчета должен быть следующий
1. значения по каждой уникальной позиции в рамках недели/месяца суммируются (н1: 5,4,5,1,6 = 21; н2: 3,2,2,1,5 = 13; н3=17; н4=19; н5=17).
2. поиск 3 мах значений в уже получившихся суммах по п.1 - это н1,н4 и н3
3. из получившихся 3 максимальных значений по п.2 рассчитывается среднее значение - это (21+19+17)/3=19
 
 
 
#30
17.01.2018 15:04:03
форумчане, кокой будет ваш вердикт?
 
 
 
Страницы: 1 2 След.
Наверх