Автоматически выбирать серии на диаграмме рассеяния

Question

Использование Excel 365

Вот пример, над которым я работал и который довольно близок к тому, что я хочу:https://docs.google.com/spreadsheets/d/1zZiFDaUhuZAhyHm3HZkcZrUl9KEoV97N/edit?usp=sharing&ouid=108210493177158563166&rtpof=true&sd=true

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

У меня получилось довольно близко к этому, за исключением нескольких проблем, которые я не смог исправить:

1. Мне нужно выделить достаточно места для всех возможных значений A и B в таблицах. Это не такая уж большая проблема, наборы данных, с которыми я сейчас работаю, довольно малы, но было бы неплохо, если бы они были полностью динамическими и могли обрабатывать любое количество значений.

2. Легенда показывает пустые записи для всех неиспользуемых столбцов таблицы. Я хочу, чтобы она показывала только те записи, для которых у меня есть данные.

3. Линия не связана для несмежных значений B (см. пример в строке 3). Я бы хотел, чтобы она была связана внутри каждой серии.

Надеюсь, это понятно. Не стесняйтесь задавать любые уточняющие вопросы.

Заранее спасибо.

Answer 1

Вот что вам нужно сделать, сделать каждую мелочь динамичной (см. анимацию):

---

• Во-первых, вместо использования пустых диапазонов для итерации используйте Structured Referencesaka Tables--> Таблица с именемTable1
• Формулы, используемые для столбцов F3:G16, J2:O8которые будут постепенно расширяться.

---

• Формула, используемая в ячейкеF3

=LET(
     _Data, Table1,
     _A, TAKE(_Data,,1),
     _Uniq, UNIQUE(_A),
     HSTACK(_Uniq, COUNTIF(_A,_Uniq)))

---

• Формула, используемая в ячейкеJ2

=LET(
     _Data, Table1,
     _A, TAKE(_Data,,1),
     _B, INDEX(_Data,,2),
     _C, TAKE(_Data,,-1),
     _UniqA, TOROW(UNIQUE(_A)),
     _UniqB, UNIQUE(_B),
     _ArrayB, VSTACK("",_UniqB),
     _DataBody, VSTACK(_UniqA&"A", MAKEARRAY(ROWS(_UniqB), COLUMNS(_UniqA), LAMBDA(r,c, 
                FILTER(_C, (INDEX(_UniqA,1,c)=_A)*(INDEX(_UniqB,r)=_B),NA())))),
     HSTACK(_ArrayB, _DataBody))

---

Вышеуказанную формулу можно также определить с помощью LAMBDA()--> Поэтому вам нужно обернуть вышеприведенную формулу в LAMBDA()-->parameter_or_calculationкак arrayи заменить Table1как array. Так что это становится и быть введенным вrefers toпри определении имени как

=LAMBDA(array, LET(_Data, array, the rest continues as is)

---

---

В Excelлисте введите как:

=SCATTER_PLOT(Table1)

---

---

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

---

• При вставке Scatter with smooth lines and markersобязательно выберите диапазон данных J2:O8и не забудьте Connect data points with lineустановить Hidden andфлажок «Пустые ячейки».
• Я также использовал Conditional Formattingдля границ. ВотЭксель.

---

Answer 2

Возможные модификации:

1. Чтобы освободить место для дополнительных данных:

   F3: =UNIQUE(FILTER(B:B,ISNUMBER(B:B)))
G3: =COUNTIF(B:B,F3#)
K3: =TRANSPOSE(F3#)
K4: =K3# & " A"
J5: =UNIQUE(FILTER(C:C,ISNUMBER(C:C)))
K5: =FILTER($D:$D,($B:$B=K$3)*($C:$C=$J5),#N/A)копируем в нужный регион.
   Лучше использовать здесь действительно нужные диапазоны, например
   =FILTER($D$1:$D$100,($B$1:$B$100=K$3)*($C$1:$C$100=$J5),#N/A)

2. Это зависит от Chart data range. Я не знаю способа сделать его динамическим (без VBA). Мы можем определить формулу, которая определяет этот диапазон данных динамически, однако, если мы используем ее здесь, она будет рассчитана сразу и сохранена как статический адрес.

3. Чтобы установить связь между точками, необходимо изменить один параметр диаграммы:
   Select Data Source > Hidden and Empty Cells > Connect data points with line