Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Ошибка в данных сводной таблицы при использования фильтра через макрос, использовал office 2013 x64 на разных компьютерах, с разными файлами, проблема остается.
 
#1
12.01.2015 22:25:08
Всем добрый день!
Столкнулся со следующей проблемой:
  • создал две сводные таблицы на одном листе
  • написал макрос который в зависимости от выбранной ячейки в первой, фильтры во второй (это требуется для отображения детализации значений - аналог если щелкнуть по значению два раза, для получения изначальных данных, но в нужном формате)
* по горизонтали в заголовке месяца, значения в таблице выдуманы и = месяцу (используется AVG). Номер месяца сделал специально чтобы видеть ошибку.
макрос вставлен в исходный текст листа
Код
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim numColumn&, numRow&

numColumn = ActiveCell.Column
actCell = ActiveCell.Value
numRow = ActiveCell.row


If numColumn < 14 And numRow > 5 Then

    If Not IsEmpty(actCell) Then
    
    On Error Resume Next
    ActiveSheet.PivotTables("PartnersERR").PivotFields("month").CurrentPage = numColumn - 1
    ActiveSheet.PivotTables("PartnersERR").PivotFields("name").CurrentPage = Cells(numRow, 1).Value
    
    Else
    ActiveSheet.PivotTables("PartnersERR").PivotFields("month").ClearAllFilters
    ActiveSheet.PivotTables("PartnersERR").PivotFields("name").ClearAllFilters
    End If   
End If    
End Sub
По идее, должно работать в рамках этих данных следующим образом:
  • щелкаю на любую ячейку со значением в первой, фильтр вы выводит во второй детализированные данные по партнеру и так как значение  = месяц, то во второй в качестве значения delta должен быть аналогичное значение.
  • но проблема в том, что почему-то к примеру, вместо 5-ти выводится семь
и если щелкнуть по значению, то видно, что данные правильные, но месяц не пятый, а седьмой, то есть значения фильтра не соответствую значению таблицы.
Сам файл находится по ссылке Файл на DropBox

При этом после нескольких открытий, в данных сводной появился какой-то 16-й месяц, хотя в самой таблице данных их нет.
Обновления таблицы не помогают.

Я со сводными через макрос ранее не работал , может что-то не так делаю. ( но после макроса и руками фильтр уже не работает нормально).  Аналогичная проблема была ранее замечена и в других фалах, но это были попытки сделать сложные дашборды с очень большим количеством информации и большим количеством сводных таблицами на одном листе, которые также были еще  связанны общими срезами , поэтому подразумевал, что там что-то намудрил и забросил это дело, но сейчас решил использовать макрос в простенькой задаче, и опять заметил эту ошибку с ошибкой работы фильтра. Буду признателен, если кто подскажет, где искать проблему или как ее решить.  (после удаления сводной и повторного создания, проблема опять появляется, но могут не правильно фильтроваться уже другие месяца).
Рисунки удалены: превышение допустимого размера вложения [МОДЕРАТОР]
 
 
 
#2
12.01.2015 23:12:31
Здравствуйте ozand. Не знаю как это происходит и как правильно лечить. Но при таком глюке я просто удаляю этот фильтр, обновляю таблицу. В параметрах таблицы в Данных против "Число элементов сохраняемых для каждого поля" ставлю Нет (может это и не к чему). Потом вновь ставлю этот фильтр. Мне кажется это помогает. Пробуйте. По неизвестной причине данные фильтра переименовались. В таблице нет цифр 16, а в фильтре есть.
Изменено: gling - 12.01.2015 23:14:02
 
 
 
#3
13.01.2015 11:38:59
Спасибо Вам,  я так до этого и поступал, но к сожалению почему-то каждый раз ситуация повторяется снова. И я для примера взял простой файл, как я писал, я пытался использовать макрос и сводные для создания динамичного дашборда, где сводных на одном листе с фильтрами достаточно много + сводные с форматированием и большим количеством переменных, а фильтры начинают неправильно работать очень быстро (+ там очень трудно понять что данные не корректные).  Я сутки копался в интернете, но почему-то не смог найти не в ru, ни в eng ни чего по этой теме.
 
 
 
#4
13.01.2015 22:28:52
А почему Вам "numColumn - 1" не заменить на "actCell", тогда не будет фильтр зависеть от столбца, Ведь если нет одного из данных, то макрос ищет по номеру столбца, а номер столбца не совпадает с цифрой в активной ячейке,этой цифры нет в фильтре и происходит переименование. имхо.
 
 
 
#5
13.01.2015 23:02:07
относительно этой таблицы, значение в ячейке первой сводной, в реальной таблице, это сумма разницы между двумя показателями (сток расчетный и сток заявительный), а не число совпадающее с номером месяца, для примера я просто сделал не эту дельту, а номер месяца, чтобы можно было наглядно заметить неадекватное поведение сводной (значение в первой сводной AVG , для того, чтобы если по партнеру больше чем 1 марка,  то значение не суммировалось, а показывало номер месяца - опять же для наглядности).

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

     "numColumn - 1" -  номер месяца (так как месяца начинаются со второго столбца)
"numRow" - номер строки в которой указано названия партнера Cells(numRow, 1).Value

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

У меня уже интерес появился, почему так, и как можно это исправить, с учетом того, что я ни где не смог найти информацию о таком странном поведение сводных таблиц. Поэтому  написал тут, так как был зарегистрирован. Если не найду ответа тут пойду дальше по ru форумам, потом на eng перейду :) (я на русском трудно объясняю, боюсь меня англоговорящие еще труднее поймут).

Сводные прекрасный инструмент, но мне как-то весь прошлый год, как только начинаю использовать макросы с ними, не везет. Постоянно какие-то сбои.
 
 
 
#6
14.01.2015 00:15:45
Распишу подробнее: макрос переписывает название фильтра если не находит в нем значение которое ему задано (№столбца), а в фильтре нет такого месяца. Для пробы откройте пример который выложен. В фильтре нет 7 месяца, при нажатии на 5- фильтрует 7, так как нет в фильтре 7 то при запуске макроса нажав на столбец 7 изменится фильтр с 5 переименуется в 7, теперь нет 5. Нажав на 6 фильтр сработает по 5 (потому что переименован), жмем на строку 5 - макросу деваться некуда он переименовывает 6 в 5, 6 пропала. Активируйте ячейку вне таблицы, установите в фильтре все, далее активируйте ячейку в 6 столбце. Все встало на свои места. Так как фильтр у Вас по месяцам предлагаю данные для фильтра брать из строки 4, там всегда есть те-же месяца что и в фильтре. Например так
Код
numColumn = Cells(4, ActiveCell.Column).Value
А в "numColumn-1", -1 убрать. Это чисто мои наблюдения, потому что сам на этом попадался.
Изменено: gling - 14.01.2015 00:20:24
 
 
 
#7
14.01.2015 00:56:41
спасибо вам! я сейчас вас понял, и ранее пытался искать в этом направление, но меня сбивало, возможно, уже испорченные данные в фильтре (как в изначальном примере этой таблицы был 14-й месяц и т.п. или просто не возможность найти закономерность, так как работал с реальными данными). Я на днях поэксперементирую с таблицами, и отпишусь вам с благодарностью, если все получится. хотя я уже сейчас думаю, что ваша описанная логика, единственная логичная в этом примере. в любом случае, еще раз спасибо за оказанное внимание.
 
 
 
#8
14.01.2015 22:44:41
проверил, исправилось. спасибо вам gling,
 
 
 
Страницы: 1
Наверх