☰

散布図上の系列を自動的に選択する

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 References別名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置き換えます。つまり、 と を入力することになります。Table1arrayrefers 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境界線にも使用しました。こちらはエクセル。

答え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. ポイント間の接続を作成するには、チャートのオプションを 1 つ変更する必要があります。
    Select Data Source > Hidden and Empty Cells > Connect data points with line

関連情報