☰

산점도에서 계열 자동 선택

tag-icon • tag-icon microsoft-excel charts
산점도에서 계열 자동 선택

엑셀 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_calculationas array및 Table1as를 교체합니다 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 lineHidden 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. 포인트를 연결하려면 차트 옵션 하나를 변경해야 합니다.
    Select Data Source > Hidden and Empty Cells > Connect data points with line

관련 정보