Добрый день всем добрым людям,
Позвольте мне поделиться с вами тем, с чем я уже некоторое время борюсь. Я использую сводные таблицы для построения различных точек данных рядом друг с другом на комбинированных диаграммах, где значение для конкретных инструментов представлено столбцом, а среднее значение для данной категории представлено прямой горизонтальной линией. Эти опорные точки (после нескольких преобразований) предоставляются Power Query из более крупного «плоского» файла. Каждая сводная таблица представляет одну точку данных (я знаю, что это не лучший способ, но это была не моя идея), и к этим опорным точкам прикреплены срезы для изменения категории. Когда выбрана определенная категория, диаграммы должны отображать инструменты вместе со средним значением по их категории.
Чтобы добиться этого, я использую формулы «Смещение» для создания как рядов данных, так и меток, например: =СМЕЩ('Fund Level Comparison Data'!$B$6,,,COUNTIF('Fund Level Comparison Data'!$B$6:$B$500,"<>")). Вот как это выглядит вкратце. Я не могу предоставить полные значения для столбца A, но вы получите общее представление:
В целом это работает хорошо, но главная проблема, с которой я сталкиваюсь, заключается в том, что эти диаграммы постоянно зависают и меняют весь макет на случайный (??), как только нет ни одного элемента метки для построения, как здесь ниже. Это может произойти, потому что не каждый фонд сообщает все значения, иногда в исходном файле есть пробелы, и они действительно должны быть, иначе «нули» повлияли бы на среднее значение, которое я беру. Вот как это выглядит под Pivot.
Видимо, Excel не настолько умен, чтобы игнорировать отсутствующие метки и просто ничего не выводить на график, сохраняя формат шаблона, или:
- Есть еще один трюк, о котором я не знаю?
- Это выходит за рамки стандартных возможностей построения диаграмм Excel, и я должен предложить перейти на Power BI?
А вот ошибки, которые выдает Excel:
Буду очень признателен за любые подсказки.
решение1
Согласно моему комментарию выше, об отказе от сводных таблиц, вот пример. AE генерируется с помощью=RANDARRAY(10,5)
Формула для среднего значения выглядит следующим образом =SEQUENCE(ROWS(A2#),1,AVERAGE($A2#),0)(обратите внимание, что функция ПОСЛЕДОВАТЕЛЬНОСТЬ используется для повторения среднего значения для каждой строки данных):
Вы можете включать и выключать ряды на диаграмме, чтобы изолировать один или несколько столбцов:
А если ваши данные табличные:
Заголовки столбцов =TRANSPOSE(SORT(UNIQUE(Table3[Attribute])))
и даты просто опускают ТРАНСПОЗИЦИЮ.