Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Формула для игнорир. фильтра сводной табл. значения в ячейке - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Формула для игнорир. фильтра сводной табл. значения в ячейке (Сводные таблицы/Pivot Table)
Формула для игнорир. фильтра сводной табл. значения в ячейке
Aleksander777 Дата: Четверг, 08.07.2021, 08:56 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 67
Репутация: 10 ±
Замечаний: 60% ±

Excel 2016
В файл примере на листе ТАБЛИЦЫ: Есть такие таблицы с годами 2019; 2020 и 2021, на основании них созданы подключения, и на основании подключений создана сводная таблица
т.к. Столбец "D" должен равняться сумме столбцов "E"+"F"+"G" (*НА ЛИСТЕ ТАБЛИЦЫ), то при перемещении суммы из столбца "D" из одного года в другой, при фильтрации (в сводной таблице) по году допустим 2019 сумма не совпадет

Вопрос возможно ли прописать формулу В ЯЧЕЙКЕ "H4" на листе "СВОДНАЯ ТАБЛИЦА", чтобы отображалось правильное значение по всем годам, но игнорируя фильтр сводной таблицы (*чтобы ячейка "H4" не учитывала фильтр только по годам)
К сообщению приложен файл: 9242453.xls (194.5 Kb)
 
Ответить
СообщениеВ файл примере на листе ТАБЛИЦЫ: Есть такие таблицы с годами 2019; 2020 и 2021, на основании них созданы подключения, и на основании подключений создана сводная таблица
т.к. Столбец "D" должен равняться сумме столбцов "E"+"F"+"G" (*НА ЛИСТЕ ТАБЛИЦЫ), то при перемещении суммы из столбца "D" из одного года в другой, при фильтрации (в сводной таблице) по году допустим 2019 сумма не совпадет

Вопрос возможно ли прописать формулу В ЯЧЕЙКЕ "H4" на листе "СВОДНАЯ ТАБЛИЦА", чтобы отображалось правильное значение по всем годам, но игнорируя фильтр сводной таблицы (*чтобы ячейка "H4" не учитывала фильтр только по годам)

Автор - Aleksander777
Дата добавления - 08.07.2021 в 08:56
Serge_007 Дата: Четверг, 08.07.2021, 09:11 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2748 ±
Замечаний: ±

Excel 2016
Цитата Aleksander777, 08.07.2021 в 08:56, в сообщении № 1 ()
возможно ли прописать формулу В ЯЧЕЙКЕ "H4" на листе "СВОДНАЯ ТАБЛИЦА", чтобы отображалось правильное значение по всем годам, но игнорируя фильтр сводной таблицы (*чтобы ячейка "H4" не учитывала фильтр только по годам)
Нет


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Цитата Aleksander777, 08.07.2021 в 08:56, в сообщении № 1 ()
возможно ли прописать формулу В ЯЧЕЙКЕ "H4" на листе "СВОДНАЯ ТАБЛИЦА", чтобы отображалось правильное значение по всем годам, но игнорируя фильтр сводной таблицы (*чтобы ячейка "H4" не учитывала фильтр только по годам)
Нет

Автор - Serge_007
Дата добавления - 08.07.2021 в 09:11
Pelena Дата: Четверг, 08.07.2021, 09:48 | Сообщение № 3
Группа: Админы
Ранг: Местный житель
Сообщений: 19161
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
Цитата Aleksander777, 08.07.2021 в 08:56, в сообщении № 1 ()
при перемещении суммы из столбца "D" из одного года в другой
если Вы объясните, как это отследить, то можно попробовать через Power Pivot


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
Цитата Aleksander777, 08.07.2021 в 08:56, в сообщении № 1 ()
при перемещении суммы из столбца "D" из одного года в другой
если Вы объясните, как это отследить, то можно попробовать через Power Pivot

Автор - Pelena
Дата добавления - 08.07.2021 в 09:48
Aleksander777 Дата: Четверг, 08.07.2021, 13:30 | Сообщение № 4
Группа: Пользователи
Ранг: Участник
Сообщений: 67
Репутация: 10 ±
Замечаний: 60% ±

Excel 2016
как это отследить

Либо по сводной таблице, либо по основным таблицам
здесь суммы относятся к каждому имени (полное имя меняться не будет и дубликатов имен тоже не будет - к Примеру Петя и Петя2 - такого не будет) поэтому можно отследить привязку по имени и в Power Pivot можно прописывать именно имя каждого например: Петя, Вася, и.т.д. - опять же если это возможно.
Еще одно дополнение: более подробно, т.е. у нас есть имена и есть столбец "D" в этом столбце числа могут между годами быть разными, но по общему итогу ячейка столбца "D" должна быть равна сумме ячеек E"+"F"+"G"


Сообщение отредактировал Aleksander777 - Четверг, 08.07.2021, 13:35
 
Ответить
Сообщение
как это отследить

Либо по сводной таблице, либо по основным таблицам
здесь суммы относятся к каждому имени (полное имя меняться не будет и дубликатов имен тоже не будет - к Примеру Петя и Петя2 - такого не будет) поэтому можно отследить привязку по имени и в Power Pivot можно прописывать именно имя каждого например: Петя, Вася, и.т.д. - опять же если это возможно.
Еще одно дополнение: более подробно, т.е. у нас есть имена и есть столбец "D" в этом столбце числа могут между годами быть разными, но по общему итогу ячейка столбца "D" должна быть равна сумме ячеек E"+"F"+"G"

Автор - Aleksander777
Дата добавления - 08.07.2021 в 13:30
Pelena Дата: Четверг, 08.07.2021, 18:58 | Сообщение № 5
Группа: Админы
Ранг: Местный житель
Сообщений: 19161
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
Не уверена, что правильно поняла. Посмотрите так
К сообщению приложен файл: 9242453.xlsm (334.5 Kb)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеНе уверена, что правильно поняла. Посмотрите так

Автор - Pelena
Дата добавления - 08.07.2021 в 18:58
Aleksander777 Дата: Пятница, 09.07.2021, 13:51 | Сообщение № 6
Группа: Пользователи
Ранг: Участник
Сообщений: 67
Репутация: 10 ±
Замечаний: 60% ±

Excel 2016
Спасибо, почти так как я хотел.
Есть несколько вопросов
Как Вы сделали данную сводную таблицу (я про подключение)?, как сделали формулы (точнее как добавили их)? Если возможно прикрепите фото последовательного создания. Сами значения формул я вижу (но вот как их добавлять не знаю)
Есть один ньюанс, когда я переключаюсь допустим на подключение Таблица2019, сводная таблица пропадает. (я так понимаю, потому что подключение 2019-2020-2021 которое Вы создали новое, оно не было основано на добавлении /объединении/ одной таблицы к другой, как я это сделал), как решить данную проблему, т.к. мне очень нужно иметь возможность переключаться между подключениями в сводной таблице
 
Ответить
СообщениеСпасибо, почти так как я хотел.
Есть несколько вопросов
Как Вы сделали данную сводную таблицу (я про подключение)?, как сделали формулы (точнее как добавили их)? Если возможно прикрепите фото последовательного создания. Сами значения формул я вижу (но вот как их добавлять не знаю)
Есть один ньюанс, когда я переключаюсь допустим на подключение Таблица2019, сводная таблица пропадает. (я так понимаю, потому что подключение 2019-2020-2021 которое Вы создали новое, оно не было основано на добавлении /объединении/ одной таблицы к другой, как я это сделал), как решить данную проблему, т.к. мне очень нужно иметь возможность переключаться между подключениями в сводной таблице

Автор - Aleksander777
Дата добавления - 09.07.2021 в 13:51
Pelena Дата: Пятница, 09.07.2021, 15:31 | Сообщение № 7
Группа: Админы
Ранг: Местный житель
Сообщений: 19161
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
Я сделала сводную на основе имеющегося подключения ИТОГО 201-220-2021. Кликнула по нему правой кнопкой мыши -- Загрузить в... -- Отчет сводной таблицы (у меня офис 365). И включила галку Добавить в модель данных

Формулы (меры) я писала в редакторе Power Pivot, но можно посмотреть и на вкладке Power Pivot -- Меры -- Управление мерами.
Не совсем поняла, зачем менять подключение. Не проще фильтровать срезами и временной шкалой (см. файл)?
К сообщению приложен файл: 8991255.jpg (22.0 Kb) · 3606670.xlsm (270.6 Kb)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеЯ сделала сводную на основе имеющегося подключения ИТОГО 201-220-2021. Кликнула по нему правой кнопкой мыши -- Загрузить в... -- Отчет сводной таблицы (у меня офис 365). И включила галку Добавить в модель данных

Формулы (меры) я писала в редакторе Power Pivot, но можно посмотреть и на вкладке Power Pivot -- Меры -- Управление мерами.
Не совсем поняла, зачем менять подключение. Не проще фильтровать срезами и временной шкалой (см. файл)?

Автор - Pelena
Дата добавления - 09.07.2021 в 15:31
Aleksander777 Дата: Суббота, 10.07.2021, 11:14 | Сообщение № 8
Группа: Пользователи
Ранг: Участник
Сообщений: 67
Репутация: 10 ±
Замечаний: 60% ±

Excel 2016
Не проще фильтровать срезами и временной шкалой

Спасибо, я сейчас как раз разбираюсь с временной шкалой, вопрос а если временная шкала не доступна? что может быть за проблема ?
я так же на дату нажимаю правой кнопкой мыши и хочу добавить временную шкалу, но Excel не дает это сделать. Я понимаю что это ошибка в таблицах какая то, потому что при создании других таблиц в этой же книге, все нормально можно создать временную шкалу, а по основным таблицам не хочет(
К сообщению приложен файл: 4869032.jpg (31.2 Kb)
 
Ответить
Сообщение
Не проще фильтровать срезами и временной шкалой

Спасибо, я сейчас как раз разбираюсь с временной шкалой, вопрос а если временная шкала не доступна? что может быть за проблема ?
я так же на дату нажимаю правой кнопкой мыши и хочу добавить временную шкалу, но Excel не дает это сделать. Я понимаю что это ошибка в таблицах какая то, потому что при создании других таблиц в этой же книге, все нормально можно создать временную шкалу, а по основным таблицам не хочет(

Автор - Aleksander777
Дата добавления - 10.07.2021 в 11:14
Aleksander777 Дата: Суббота, 10.07.2021, 11:31 | Сообщение № 9
Группа: Пользователи
Ранг: Участник
Сообщений: 67
Репутация: 10 ±
Замечаний: 60% ±

Excel 2016
Еще вопрос касаемо отображения таблицы (точнее ее Формы), Вы на сколько я понимаю сделали отображение таблицы в форме структуры, но если я нажимаю на отображение в форме структуры, у меня переносятся значения из столбца "Важное" все значения на ячейку вниз, я уже все поперепробывал в "Конструкторе" для правильного отображения, но так как Вы сделали, у меня не получается(
Как сделать правильное отображение данной таблицы, как это сделали Вы?
К сообщению приложен файл: 2290098.jpg (57.5 Kb) · 3518263.jpg (40.5 Kb)
 
Ответить
СообщениеЕще вопрос касаемо отображения таблицы (точнее ее Формы), Вы на сколько я понимаю сделали отображение таблицы в форме структуры, но если я нажимаю на отображение в форме структуры, у меня переносятся значения из столбца "Важное" все значения на ячейку вниз, я уже все поперепробывал в "Конструкторе" для правильного отображения, но так как Вы сделали, у меня не получается(
Как сделать правильное отображение данной таблицы, как это сделали Вы?

Автор - Aleksander777
Дата добавления - 10.07.2021 в 11:31
Pelena Дата: Суббота, 10.07.2021, 11:50 | Сообщение № 10
Группа: Админы
Ранг: Местный житель
Сообщений: 19161
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
Цитата Aleksander777, 10.07.2021 в 11:14, в сообщении № 8 ()
если временная шкала не доступна?
скорей всего где-то "неправильная" дата или среди дат есть пустые ячейки или все даты в текстовом формате. В общем, что-то с датами не так)
Цитата Aleksander777, 10.07.2021 в 11:31, в сообщении № 9 ()
Как сделать правильное отображение данной таблицы
отображение можно настраивать для каждого поля отдельно. Я сначала сделала для всей таблицы в форме структуры, а потом кликнула по дате правой кнопкой мыши -- Параметры поля -- вкладка Разметка и печать -- в форме таблицы


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
Цитата Aleksander777, 10.07.2021 в 11:14, в сообщении № 8 ()
если временная шкала не доступна?
скорей всего где-то "неправильная" дата или среди дат есть пустые ячейки или все даты в текстовом формате. В общем, что-то с датами не так)
Цитата Aleksander777, 10.07.2021 в 11:31, в сообщении № 9 ()
Как сделать правильное отображение данной таблицы
отображение можно настраивать для каждого поля отдельно. Я сначала сделала для всей таблицы в форме структуры, а потом кликнула по дате правой кнопкой мыши -- Параметры поля -- вкладка Разметка и печать -- в форме таблицы

Автор - Pelena
Дата добавления - 10.07.2021 в 11:50
Aleksander777 Дата: Суббота, 10.07.2021, 16:52 | Сообщение № 11
Группа: Пользователи
Ранг: Участник
Сообщений: 67
Репутация: 10 ±
Замечаний: 60% ±

Excel 2016
Спасибо, я теперь понял как настраивать правильное отображение
где-то "неправильная" дата

но вот с этим я начал разбираться и действительно не правильный формат даты я обнаружил на некоторых таблицах, после исправления (в моих основных таблицах), я обновил подключения и на этом подключении обнаружилось 8 ошибок. (Видимо из за этих ошибок и не дает поставить временную шкалу фильтра)
Вот я не знаю отдельную тему по ним создавать или Вы поможете разобраться с ними как их решить.
К сообщению приложен файл: 8051124.jpg (57.4 Kb) · 6143862.jpg (32.9 Kb)


Сообщение отредактировал Aleksander777 - Суббота, 10.07.2021, 16:55
 
Ответить
СообщениеСпасибо, я теперь понял как настраивать правильное отображение
где-то "неправильная" дата

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

Автор - Aleksander777
Дата добавления - 10.07.2021 в 16:52
Aleksander777 Дата: Суббота, 10.07.2021, 17:06 | Сообщение № 12
Группа: Пользователи
Ранг: Участник
Сообщений: 67
Репутация: 10 ±
Замечаний: 60% ±

Excel 2016
Я начал делать сводную таблицу на основе Вашего примера
Отчет сводной таблицы

и когда выставляю столбец дата у меня такой формат как на прикрепленном фото и он никак не меняется. (пробовал через формат ячеек, и там выставлял нужный формат), в чем может быть проблема или это из за ошибок?
К сообщению приложен файл: 4097831.jpg (23.6 Kb)
 
Ответить
СообщениеЯ начал делать сводную таблицу на основе Вашего примера
Отчет сводной таблицы

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

Автор - Aleksander777
Дата добавления - 10.07.2021 в 17:06
Pelena Дата: Суббота, 10.07.2021, 17:28 | Сообщение № 13
Группа: Админы
Ранг: Местный житель
Сообщений: 19161
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
Вот я не знаю отдельную тему по ним создавать или Вы поможете разобраться с ними как их решить
Да, для дат лучше отдельную тему создать. И приложите пример с ошибочными датами. Думаю эти ошибки надо убирать сразу в запросах


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
Вот я не знаю отдельную тему по ним создавать или Вы поможете разобраться с ними как их решить
Да, для дат лучше отдельную тему создать. И приложите пример с ошибочными датами. Думаю эти ошибки надо убирать сразу в запросах

Автор - Pelena
Дата добавления - 10.07.2021 в 17:28
Aleksander777 Дата: Воскресенье, 11.07.2021, 02:12 | Сообщение № 14
Группа: Пользователи
Ранг: Участник
Сообщений: 67
Репутация: 10 ±
Замечаний: 60% ±

Excel 2016
Ок, спасибо)) я так и сделаю.
Могли бы Вы мне немного подсказать про Power Pilot - "Меры"
Меня интересует формулы которые Вы добавили, хотелось бы для себя понять порядок создания логической цепочки создания самой формулы.
Если я правильно понял Вы создали формулу по сумме значений (Сумма1+Сумма2+Сумма3) - и прописали путь к листу (книги) и привязали 2-а столбца "Имя" и "Важное" - вот вопрос а почему именно их и зачем? (Повторюсь я просто не понимаю принцип действия, и хочу разобраться)
Далее Указали путь к столбцу "Значение", также привязав столбцы "Имя" и "Важное"
Ну и 3-я формула одну формулу вычесть другую (это понятно, т.к. мы этого и добивались)

И самый главный вопрос я попробовал все меры сам прописать вручную (получилось правильно прописать только "Разница" и "Сумма значений"), а вот с "Общая сумма" я замучался. вручную прописываю по разному и выбираю то что предлагает, и так прописываю, excel то не видит столбы, если выбирать то что предлагает excel ссылки на столбцы, то он говорит что SUM принимает только ссылку на столбец. Что я сделал не так?
К сообщению приложен файл: --.xlsm (332.2 Kb) · 6257581.jpg (39.4 Kb)
 
Ответить
СообщениеОк, спасибо)) я так и сделаю.
Могли бы Вы мне немного подсказать про Power Pilot - "Меры"
Меня интересует формулы которые Вы добавили, хотелось бы для себя понять порядок создания логической цепочки создания самой формулы.
Если я правильно понял Вы создали формулу по сумме значений (Сумма1+Сумма2+Сумма3) - и прописали путь к листу (книги) и привязали 2-а столбца "Имя" и "Важное" - вот вопрос а почему именно их и зачем? (Повторюсь я просто не понимаю принцип действия, и хочу разобраться)
Далее Указали путь к столбцу "Значение", также привязав столбцы "Имя" и "Важное"
Ну и 3-я формула одну формулу вычесть другую (это понятно, т.к. мы этого и добивались)

И самый главный вопрос я попробовал все меры сам прописать вручную (получилось правильно прописать только "Разница" и "Сумма значений"), а вот с "Общая сумма" я замучался. вручную прописываю по разному и выбираю то что предлагает, и так прописываю, excel то не видит столбы, если выбирать то что предлагает excel ссылки на столбцы, то он говорит что SUM принимает только ссылку на столбец. Что я сделал не так?

Автор - Aleksander777
Дата добавления - 11.07.2021 в 02:12
Pelena Дата: Воскресенье, 11.07.2021, 08:30 | Сообщение № 15
Группа: Админы
Ранг: Местный житель
Сообщений: 19161
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
привязали 2-а столбца "Имя" и "Важное"
не то чтобы привязала... Функция ALLEXCEPT задает фильтры, которые нам могут понадобиться. То есть по этим полям сводная должна группировать и фильтровать, как обычно. А поле Дата в этой функции не упомянуто, поэтому фильтр по дате игнорируется и считаются все строки независимо от даты, чего мы и добивались.
не видит столбы
да, правильно, я создала доп.столбец в редакторе Power Pivot, в котором просуммировала эти три столбца, и его уже задействовала в функции. Его можно увидеть, если войти в редактор (вкладка Power Pivot -- Управление). Если без доп. столбца, то можно так
[vba]
Код
=CALCULATE(SUMX('Итого 2019-2020-2021г';[Сумма1]+[Сумма2]+[Сумма3]); ALLEXCEPT('Итого 2019-2020-2021г'; 'Итого 2019-2020-2021г'[ИМЯ]; 'Итого 2019-2020-2021г'[Важное]))
[/vba]


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
привязали 2-а столбца "Имя" и "Важное"
не то чтобы привязала... Функция ALLEXCEPT задает фильтры, которые нам могут понадобиться. То есть по этим полям сводная должна группировать и фильтровать, как обычно. А поле Дата в этой функции не упомянуто, поэтому фильтр по дате игнорируется и считаются все строки независимо от даты, чего мы и добивались.
не видит столбы
да, правильно, я создала доп.столбец в редакторе Power Pivot, в котором просуммировала эти три столбца, и его уже задействовала в функции. Его можно увидеть, если войти в редактор (вкладка Power Pivot -- Управление). Если без доп. столбца, то можно так
[vba]
Код
=CALCULATE(SUMX('Итого 2019-2020-2021г';[Сумма1]+[Сумма2]+[Сумма3]); ALLEXCEPT('Итого 2019-2020-2021г'; 'Итого 2019-2020-2021г'[ИМЯ]; 'Итого 2019-2020-2021г'[Важное]))
[/vba]

Автор - Pelena
Дата добавления - 11.07.2021 в 08:30
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Формула для игнорир. фильтра сводной табл. значения в ячейке (Сводные таблицы/Pivot Table)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!