Excel의 그룹화된 산점도, 범주형 X축

Question

한 가지 해결책은 다음과 같습니다.Excel - 동일한 x 값 위에 다른 y 값 표시

비결은 레이블을 숫자로 변환하는 것 같습니다(아마도 ms의 명목 및 서수 데이터에 대해 들어 본 적이 없을 것입니다). 이는 정렬 및 누적 카운터를 통해 매우 쉽게 달성할 수 있습니다. 문제는 X축이 숫자이기 때문에 제안된 대로 간격을 두고 수동으로 재생하는 것이 대규모로 실행 가능하지 않을 수 있다는 것입니다.

또 다른 접근 방식은 데이터를 범주당 단일 행으로 병합하는 것입니다. 이는 수식이나 VBA를 통해 매우 간단합니다. 그런 다음 분산형 차트를 테이블에 적용합니다. 이렇게 하면 각 열에 대해 무지개 색깔의 모양을 갖는 시리즈가 생성됩니다. 이것들을 동시에 수정하는 것은 올바른 배열 속성을 사용하여 vba에서 가능해야 한다고 생각합니다(문서를 검사하지 않았습니다). 게다가 플롯을 생성하는 동안 공백이 있으면 혼란스러울 수 있으므로 처음에는 이를 자리 표시자 값(예: -412613)으로 대체한 다음 플롯이 생성되면 모든 자리 표시자를 공백으로 바꿔 차트에서 편리하게 제거했습니다. 더 이상의 결과 없이. 따라서 코딩하는 데 더 많은 일회성 비용이 소요되고 적용하는 데 2분이 소요됩니다.

아니면 더 나은 플로팅 소프트웨어를 사용할 수 있다면...

편집(30/06/19): 요청 시 기술 세부 사항이 아래에 자세히 설명됩니다. 내 무작위 데이터는 원래 A+C에 있습니다.

누적:

B1=1 ; B2=if(a2=a1,b1,b1+1)

수식을 사용하여 병합:

D1=1 ; D2 = if(a2=a1,0,1)
E1=if(index($a2:$a$999,column(a1),1)=$a1, _
      value(index($c2:$c$999,column(a1),1)),-0.413612)

모든 행과 최대 관찰 수만큼의 열에 E를 붙여넣습니다. 열의 필터에 자리 표시자만 표시되는 시기를 알 수 있습니다. D=1에 따라 필터링하고 값을 다른 시트에 붙여넣은 다음 D를 삭제합니다. 저는 이 방법이 약간 더 '크로스 플랫폼'이고 libre에서 작동하기 때문에 개인적으로 선호합니다.

대안 - vba를 사용하여 병합:

Const BASESHEET As String = "Base"
Const MERGESHEET As String = "Merge2"

Sub MergePairs()
Dim rowcnt As Long
Dim srcsht
Dim trgsht
Dim pid As String
Dim stidx As Long
Dim trgidx As Long
Set srcsht = Sheets(BASESHEET)
Set trgsht = Sheets(MERGESHEET)

' Sort the data.
srcsht.Sort.SortFields.Clear
srcsht.Sort.SortFields.Add Key:=Range("$A:$A"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With srcsht.Sort
    .SetRange Range("$A:$C")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

' Copy unique ids.
rowcnt = srcsht.Cells(srcsht.Rows.Count, 1).End(xlUp).Row
srcsht.Range(srcsht.Cells(1, 1), srcsht.Cells(rowcnt, 1)).Copy
trgsht.Range("A1").PasteSpecial
trgsht.Range("$A:$A").RemoveDuplicates Columns:=1, Header:=xlNo

' Copy each set of points.
pid = ""
For i = 1 To rowcnt + 1
    If pid = "" Then
        pid = srcsht.Cells(i, 1).Value
        stidx = i
        trgidx = 1
    ElseIf pid <> srcsht.Cells(i, 1).Value Then
        srcsht.Range(srcsht.Cells(stidx, 3), srcsht.Cells(i - 1, 3)).Copy
        trgsht.Cells(trgidx, 2).PasteSpecial Paste:=xlPasteValues, _
            Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        pid = srcsht.Cells(i, 1).Value
        stidx = i
        trgidx = trgidx + 1
    End If
Next i
End Sub

모든 값을 선택하는 차트를 만든 다음 모든 자리 표시자를 공백으로 바꾸는 것을 잊지 마십시오.

모든 마커 변경: 분산형 차트의 축 레이블은 쉽게 유연하지 않은 것 같습니다(아마도 그렇게 할 권한이 전혀 없을 수도 있음). 대조적으로, 꺾은선형 차트에서 선을 제거하는 것은 충분히 간단합니다. (이렇게 하면 시트의 모든 차트가 변경되므로 단독으로 있는지 확인하거나 루프를 parm으로 수정하십시오.)

Sub XChart()
Dim chrt As ChartObject
Dim ser As Excel.Series
For Each chrt In Worksheets("Merge1").ChartObjects
    chrt.Chart.ChartType = xlLine
    For Each ser In chrt.Chart.SeriesCollection
        ser.Format.Line.Visible = msoFalse
        ser.MarkerStyle = xlMarkerStyleX
        ser.MarkerForegroundColor = RGB(0, 0, 0)
        ser.MarkerBackgroundColor = RGB(255, 255, 255)
        ser.MarkerSize = 7
    Next ser
Next chrt
End Sub

다음 시연을 위한 몇 가지 이미지입니다.

선택 단계:

이 데이터로 생성된 차트(자리 표시자의 최종 결과에 주의):

자리표시자를 공백으로 바꾸기:

여기에 생동감이 뼛속까지 빨려 들어간 최종 결과가 있습니다.

편집(20/04/24): 내 관심을 끌었던 것처럼 Excel 2013+ 버전에서는 표면적으로는 변경할 수단 없이 꺾은선형 차트 설정을 값 유형 축으로 변경한 것으로 보입니다. 그러나 열/막대 문자는 (여전히) 범주 유형 축을 사용합니다. 가능한 낮은 기술 해킹 중 하나는 설명된 대로 분산형/선 플롯을 만든 다음 레이블 + 0 값만 있는 두 번째 막대 차트를 만드는 것입니다. 이렇게 하면 범례, 눈금 등을 정리하여 가로 축만 남길 수 있는 빈 그리드가 생성됩니다. . 그런 다음 분산의 숫자 범위(대략 0/1에서 레이블 수+까지)와 열의 너비 및 위치를 조정하여 보기 흉하기는 하지만 두 차트를 정렬할 수 있습니다. 값/범주 차이가 매우 명확한 Worksheets("Merge").ChartObjects(1).Chart.Axes(xlCategory,xlPrimary)를 통해 (excel.)축에 대한 참조를 얻을 수 있습니다. 그럼에도 불구하고 내가 시도한 속성 변경 요청에는 응답하지 않았습니다.

Answer 1

한 가지 해결책은 다음과 같습니다.Excel - 동일한 x 값 위에 다른 y 값 표시

비결은 레이블을 숫자로 변환하는 것 같습니다(아마도 ms의 명목 및 서수 데이터에 대해 들어 본 적이 없을 것입니다). 이는 정렬 및 누적 카운터를 통해 매우 쉽게 달성할 수 있습니다. 문제는 X축이 숫자이기 때문에 제안된 대로 간격을 두고 수동으로 재생하는 것이 대규모로 실행 가능하지 않을 수 있다는 것입니다.

또 다른 접근 방식은 데이터를 범주당 단일 행으로 병합하는 것입니다. 이는 수식이나 VBA를 통해 매우 간단합니다. 그런 다음 분산형 차트를 테이블에 적용합니다. 이렇게 하면 각 열에 대해 무지개 색깔의 모양을 갖는 시리즈가 생성됩니다. 이것들을 동시에 수정하는 것은 올바른 배열 속성을 사용하여 vba에서 가능해야 한다고 생각합니다(문서를 검사하지 않았습니다). 게다가 플롯을 생성하는 동안 공백이 있으면 혼란스러울 수 있으므로 처음에는 이를 자리 표시자 값(예: -412613)으로 대체한 다음 플롯이 생성되면 모든 자리 표시자를 공백으로 바꿔 차트에서 편리하게 제거했습니다. 더 이상의 결과 없이. 따라서 코딩하는 데 더 많은 일회성 비용이 소요되고 적용하는 데 2분이 소요됩니다.

아니면 더 나은 플로팅 소프트웨어를 사용할 수 있다면...

편집(30/06/19): 요청 시 기술 세부 사항이 아래에 자세히 설명됩니다. 내 무작위 데이터는 원래 A+C에 있습니다.

누적:

B1=1 ; B2=if(a2=a1,b1,b1+1)

수식을 사용하여 병합:

D1=1 ; D2 = if(a2=a1,0,1)
E1=if(index($a2:$a$999,column(a1),1)=$a1, _
      value(index($c2:$c$999,column(a1),1)),-0.413612)

모든 행과 최대 관찰 수만큼의 열에 E를 붙여넣습니다. 열의 필터에 자리 표시자만 표시되는 시기를 알 수 있습니다. D=1에 따라 필터링하고 값을 다른 시트에 붙여넣은 다음 D를 삭제합니다. 저는 이 방법이 약간 더 '크로스 플랫폼'이고 libre에서 작동하기 때문에 개인적으로 선호합니다.

대안 - vba를 사용하여 병합:

Const BASESHEET As String = "Base"
Const MERGESHEET As String = "Merge2"

Sub MergePairs()
Dim rowcnt As Long
Dim srcsht
Dim trgsht
Dim pid As String
Dim stidx As Long
Dim trgidx As Long
Set srcsht = Sheets(BASESHEET)
Set trgsht = Sheets(MERGESHEET)

' Sort the data.
srcsht.Sort.SortFields.Clear
srcsht.Sort.SortFields.Add Key:=Range("$A:$A"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With srcsht.Sort
    .SetRange Range("$A:$C")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

' Copy unique ids.
rowcnt = srcsht.Cells(srcsht.Rows.Count, 1).End(xlUp).Row
srcsht.Range(srcsht.Cells(1, 1), srcsht.Cells(rowcnt, 1)).Copy
trgsht.Range("A1").PasteSpecial
trgsht.Range("$A:$A").RemoveDuplicates Columns:=1, Header:=xlNo

' Copy each set of points.
pid = ""
For i = 1 To rowcnt + 1
    If pid = "" Then
        pid = srcsht.Cells(i, 1).Value
        stidx = i
        trgidx = 1
    ElseIf pid <> srcsht.Cells(i, 1).Value Then
        srcsht.Range(srcsht.Cells(stidx, 3), srcsht.Cells(i - 1, 3)).Copy
        trgsht.Cells(trgidx, 2).PasteSpecial Paste:=xlPasteValues, _
            Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        pid = srcsht.Cells(i, 1).Value
        stidx = i
        trgidx = trgidx + 1
    End If
Next i
End Sub

모든 값을 선택하는 차트를 만든 다음 모든 자리 표시자를 공백으로 바꾸는 것을 잊지 마십시오.

모든 마커 변경: 분산형 차트의 축 레이블은 쉽게 유연하지 않은 것 같습니다(아마도 그렇게 할 권한이 전혀 없을 수도 있음). 대조적으로, 꺾은선형 차트에서 선을 제거하는 것은 충분히 간단합니다. (이렇게 하면 시트의 모든 차트가 변경되므로 단독으로 있는지 확인하거나 루프를 parm으로 수정하십시오.)

Sub XChart()
Dim chrt As ChartObject
Dim ser As Excel.Series
For Each chrt In Worksheets("Merge1").ChartObjects
    chrt.Chart.ChartType = xlLine
    For Each ser In chrt.Chart.SeriesCollection
        ser.Format.Line.Visible = msoFalse
        ser.MarkerStyle = xlMarkerStyleX
        ser.MarkerForegroundColor = RGB(0, 0, 0)
        ser.MarkerBackgroundColor = RGB(255, 255, 255)
        ser.MarkerSize = 7
    Next ser
Next chrt
End Sub

다음 시연을 위한 몇 가지 이미지입니다.

선택 단계:

이 데이터로 생성된 차트(자리 표시자의 최종 결과에 주의):

자리표시자를 공백으로 바꾸기:

여기에 생동감이 뼛속까지 빨려 들어간 최종 결과가 있습니다.

편집(20/04/24): 내 관심을 끌었던 것처럼 Excel 2013+ 버전에서는 표면적으로는 변경할 수단 없이 꺾은선형 차트 설정을 값 유형 축으로 변경한 것으로 보입니다. 그러나 열/막대 문자는 (여전히) 범주 유형 축을 사용합니다. 가능한 낮은 기술 해킹 중 하나는 설명된 대로 분산형/선 플롯을 만든 다음 레이블 + 0 값만 있는 두 번째 막대 차트를 만드는 것입니다. 이렇게 하면 범례, 눈금 등을 정리하여 가로 축만 남길 수 있는 빈 그리드가 생성됩니다. . 그런 다음 분산의 숫자 범위(대략 0/1에서 레이블 수+까지)와 열의 너비 및 위치를 조정하여 보기 흉하기는 하지만 두 차트를 정렬할 수 있습니다. 값/범주 차이가 매우 명확한 Worksheets("Merge").ChartObjects(1).Chart.Axes(xlCategory,xlPrimary)를 통해 (excel.)축에 대한 참조를 얻을 수 있습니다. 그럼에도 불구하고 내가 시도한 속성 변경 요청에는 응답하지 않았습니다.

Excel의 그룹화된 산점도, 범주형 X축

답변1

관련 정보