Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Как сделать фильтр в сводной таблице?
 
#1
18.03.2018 19:05:45
Всех приветствую!

Помогите, пож-та! Как в сводной таблице оставить только те значения поля, которые больше нуля?
В приведенном примере (файл вложен) нужно оставить значения
55, 112, 31 и т. д.
а в тех строках где значение поля меньше нуля, те или оставить пустыми, или не отображать.

С уважением
Евгений
Скрытый текст
 
 
 
#2
18.03.2018 21:29:51
Цитата
EvgenyExcell написал:
В приведенном примере (файл вложен) нужно оставить значения 55, 112, 31 и т. д.
Я не поняла: в каком столбе Таблицы находятся эти цифры?
Цитата
EvgenyExcell написал:
Как в сводной таблице оставить только те значения поля, которые больше нуля?
Думаю, имеет смысл:
1. воспользоваться элементарной функцией ЕСЛИ для определения знака числа,
2. выполнить фильтр положительных чисел, выполнить копирование строк на новый лист,
3. построить Сводную Таблицу, используя этот лист.
 
 
 
#3
18.03.2018 22:29:57
да, понятно, благодарю.
Вы написали об одном варианте решения - отфильтровать на исходном листе и уже из отфильтрованного на новом листе сделать сводную таблицу.
Однако, в этом случае для каждого варианта фильтра нужно создавать новый лист. А если есть цель посмотреть много вариантов, то тогда и новых исходных листов нужно создавать тоже много.
Вот если бы можно было фильтровать уже на листе самой сводной таблицы, то тогда это было и гораздо быстрее,и гораздо эффективнее!
Нет ли мыслей,как фильтровать на листе сводной таблицы?

Суважением
Евгений
Изменено: EvgenyExcell - 19.03.2018 15:25:09 (Замечание модератора относительно корректности цитирования)
 
 
 
#4
18.03.2018 22:59:34
Доброе время суток
Можно использовать Power Pivot, написав меру, которая не выводит отрицательные значения. Если вся строка будет пустой, то сводная её не будет показывать.
 
 
 
#5
18.03.2018 23:15:39
Цитата
EvgenyExcell написал:
Как в сводной таблице оставить только те значения поля, которые больше нуля?
Цитата
Мотя написал:
воспользоваться элементарной функцией ЕСЛИ для определения знака числа
доброго вечера) собсна делаете 1 доп столбец в "умной", в котором, с помощью ЕСЛИ определяете знак. Это поле (с "если") выводите как срез и фильтруете им только положительные… Если я вас правильно понял, конечно))) см. файл  ;)
Изменено: Jack Famous - 18.03.2018 23:22:34
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#6
18.03.2018 23:16:42
Напрашивается только условно пригодный вариант.
доп. столбец и фильтр по нему.
По вопросам из тем форума, личку не читаю.
 
 
 
#7
19.03.2018 00:44:14
БМВ, да, спасибо, с дополнительным столбцом понятно.
Следуя той же логике, можно скопировать и всю Сводную Таблицу, но не как Сводную Таблицу, а как 2-ю бычную таблицу, а уже 2-ю обычную таблицу отфильтровать в 3й таблицк через формулы ЕСЛИ.
Это показано на том листе, где Сводная Таблица в приложенном файле.
Но это долго и не напрямую.

Вот если бы можно сразу в Сводной Таблице фильтровать, это было бы эффективнее!..
Изменено: EvgenyExcell - 19.03.2018 15:30:47
 
 
 
#8
19.03.2018 00:57:42
Jack Famous, здОрово!
Но я не умею пользоваться  "Умной".  Даже не смог скопировать Вашу формулу в полную прилагаемую таблицу. Не поможете?

Суважением
Евгений
 
 
 
#9
19.03.2018 07:57:08
Jack Famous,  В данном случае первичная обработка таким образом коверкает результат. Не учтено , что в течении одного дня может быть + и -. Просто "-" нельзя отбрасывать, но вот сложить (одн раз) в пределах одного периода отображенного в сводной таблице ( Групировка) -  можно. Но это делает её не такой гибкой.
По вопросам из тем форума, личку не читаю.
 
 
 
#10
19.03.2018 11:06:20
Вариант на Power Pivot 2010, только положительные суммы.
 
 
 
#11
19.03.2018 11:18:28
Цитата
EvgenyExcell написал: не умею пользоваться  "Умной"
изучайте

Цитата
EvgenyExcell написал: не смог скопировать Вашу формулу в полную прилагаемую таблицу
пример в файле (этот ваш пример отличается от того, что в шапке хотя бы тем, что в новом разделитель разряда точка и нужны дополнительные преобразования — повнимательнее), но настоятельно рекомендую делать сводную на основе "умной" таблицы (приёмы).

БМВ, не понял, что там, собственно не так - простите… В рамках задачи из шапки вроде всё так (я уже не говорю о названии темы). Особо разбираться не хочется, а ТС молчит об этом… Посмотрим, что дальше будет  ;)
Изменено: Jack Famous - 19.03.2018 14:32:29
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#12
19.03.2018 11:29:26
Андрей VG, а почему в формуле
Код
Полож суммы только:=IF(SUM('factTable'[Profit/Loss in pips]) >= 0; SUM('factTable'[Profit/Loss in pips]); BLANK())

>= ? Вроде ТС просил
Цитата
EvgenyExcell написал:
оставить только те значения поля, которые больше нуля
Вот горшок пустой, он предмет простой...
 
 
 
#13
19.03.2018 11:56:33
Цитата
PooHkrd написал:
которые больше нуля
Коллега, вы правы. Спасибо за замечание. Думаю, ТС сможет заменить >= на > в функции меры, если будет пользоваться.
 
 
 
#14
19.03.2018 12:01:05
В Вашем файле есть столб P с непонятным форматом...
Можно вовсе не использовать функцию ЕСЛИ.
Можно в макросе в фильтре столба G (Profit/Loss in pips) воспользоваться режимом "Числовые фильтры".
Макрос строит лист "Tranzit" с положительными данными.
Далее: строить Сводную Таблицу на их основе.
 
 
 
#15
19.03.2018 12:06:11
Цитата
Jack Famous написал:
не понял, что там, собственно не так - простите…
В сводной суммы, если убрать на этапе источника, отрицательные значения, то положительных итоговых сумм будет больше, а сами суммы изменятся посмотрите 3е число например.
По вопросам из тем форума, личку не читаю.
 
 
 
#16
19.03.2018 12:15:17
Цитата
БМВ написал:
сами суммы изменятся
действительно - в каждом дне может быть несколько записей и сводная, как ей и положено,  суммирует их. От этого можно избавится, например, выведением дополнительно ID в строки. Остальное (если я не всё учёл) только "хитрыми" методами, наверное — как Андрей VG предложил, например  :)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#17
19.03.2018 12:45:03
Jack Famous,  'я ж вроде тут чуть скорректировал ваш метод.
Изменено: БМВ - 19.03.2018 12:45:19
По вопросам из тем форума, личку не читаю.
 
 
 
#18
19.03.2018 13:00:30
БМВ, не заглянул (сейчас посмотрел). Действительно с гибкостью проблемы), а ТС молчит по-прежнему…
Я пока пас, а вам спасибо за коррекцию  ;)
Изменено: Jack Famous - 19.03.2018 13:00:42
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#19
19.03.2018 14:25:13
Огромное спасибо всем откликнувшимся!

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

2. Что я понял.
БМВ, я понял, что Вы добавили в исходную инфо-таблицу две колонки. Первая добавленная колонка - она считает сумму транзакций каждого дня внутри самой исходной таблице. Вторая добавленная колонка - фильтрует дни, где эта сумма положительна. А далее, на листе Сводной Таблицы этот фильтр сумм положительных дней и спользуется
Это круто:)) .

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

Файл прилагаю

С уважением
Евгений
 
 
 
#20
19.03.2018 14:47:51

EvgenyExcell, попробую пояснить, с поправкой что использую ENG версию формул, но там все очень просто и разобравшись даже с нуля все сделаете.
=IF(COUNTIF($O$1:O2;O2)=1;SUMIF(O:O;O2;G:G);0)
COUNTIF($O$1:O2;O2)=1 - считаем, сколько с первой строки до текущей было значений равных значению O в текущей строке . и сраниваем с 1 для того чтоб посчитать сумму за день только один  раз.
Далее получаем сумму из столбца G строк где день совпадает с днем из текущей строки.
Вторая совсем простая =IF(Q2>0;"S";"H")  Show и Hide соотв.

Что может пойти не так при переносе?
1 - конечно соответвие столбцов и естесвенно O2 для формулы во второй строке
2. пример был приведен на один месяц. Если месяцев несколько, то надо менять и сравнивать конкретную дату или использовать
=IF(COUNTIFS($O$1:O2;O2;$L$1:L2;L2)=1;SUMIFS(G:G;O:O;O2;L:L;L2);0)
или
=IF(COUNTIFS($H$1:H2;">="&INT(H2);$H$1:H2;"<"&INT(H2)+1)=1;SUMIFS(G:G;H:H;">="&INT(H2);H:H;"<"&INT(H2)+1);0)не использую предварительные  преобразования

Изменено: БМВ - 19.03.2018 15:55:08
По вопросам из тем форума, личку не читаю.
 
 
 
#21
19.03.2018 15:59:43
БМВ, благодарю за подробные объяснения.
Сначала для понимания разберусь с одним месяцем. Потом перейду к нескольким месяцам. Потом напишу по результату!

Цитата
Мотя написал: Можно в макросе в фильтре столба G (Profit/Loss in pips) воспользоваться режимом "Числовые фильтры".Макрос строит лист "Tranzit" с положительными данными.Далее: строить Сводную Таблицу на их основе.
Мотя, да, благодарю за идею инструмента.
Но для этой задачи инструмент не подходит, так как выбирает только положительные числа в каждом дне, а нужно в каждом дне выбрать и положительные, и отрицательные, а уже потом выбрать те суммы тех дней, которые положительны.

С уважением
Евгений
 
 
 
Страницы: 1
Наверх