☰

自動選擇散佈圖上的系列

tag-icon • tag-icon microsoft-excel charts
自動選擇散佈圖上的系列

使用 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 行的範例）。我希望它在每個系列中都有聯繫。

希望這是有道理的，請隨時提出任何澄清問題。

提前致謝。

答案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_calculationasarray並替換Table1as array。所以它變成並被輸入refers to將名稱定義為

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

在此輸入影像描述

在Excel工作表中輸入：

=SCATTER_PLOT(Table1)

在此輸入影像描述

  • 請注意，我使用了輔助MAKEARRAY()函數LAMBDA()而不是 using REDUCE()，也可以使用，但它是重資源函數，並且在堆疊資料時REDUCE()效率不高。MAKEARRAY()
  • 插入時，請Scatter with smooth lines and markers確保選擇資料範圍J2:O8，並記住Connect data points with line選擇Hidden and“空白單元格”框。
  • 我也用過Conditional Formatting邊框。這裡是Excel。

答案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

相關內容