☰

피벗 테이블 - 여러 개의 독립적인 데이터 열 요약

tag-icon • tag-icon microsoft-excel
피벗 테이블 - 여러 개의 독립적인 데이터 열 요약

다음과 같은 열이 있는 시스템에 대한 일일 입력이 포함된 큰 테이블이 있습니다.

일자 품목명1 품목수량1 품목명2 품목수량2 .....

품목 이름과 수량은 서로 독립적입니다. 어느 날에는 항목 A 10개를 받을 수 있고, 다른 날에는 항목 D 5개, 항목 B 6개, 항목 C 234개를 받을 수 있습니다. 전자의 경우 2개의 열에만 데이터가 있고 후자의 경우 6개 열에 데이터가 포함됩니다. 다른 모든 열은 비어 있습니다. 예:

Day N1  Q1  N2  Q2  N3  Q3
1   A   10
2   D   5   B   6   C   234

예를 들어 1년 동안 매일 A의 수량 또는 1년 동안 A의 0이 아닌 값의 수를 표시하는 데 피벗 테이블을 어떻게 사용합니까? A는 여러 열에 나타날 수 있고 수백 가지 항목이 있을 수 있으므로 단순히 열의 각 변수가 포함된 테이블을 만드는 것은 불가능합니다.

여러 가지 방법으로 모든 열을 추가하려고 시도했지만 C의 모든 값이 주어지면 B의 모든 값이 주어지면 A의 값이 반환됩니다. 이는 값이 종속적인 경우 유용하지만 페이지를 복잡하게 만듭니다. 상관관계가 없기 때문입니다.

이것이 불가능할 경우 스프레드시트에서 필요한 정보를 얻는 좋은 방법은 무엇입니까?

답변1

데이터를 재구성하는 것이 절실히 필요합니다. 현재 직면하고 있는 이유로 불확실한 수의 열을 갖는 것은 정말 나쁜 생각입니다. 다른 사람들이 제안한 것처럼 내 조언은 데이터를 각 일일 항목에 대한 행이 있는 간단한 3열 테이블로 변환하라는 것입니다.

Day  Item  Quant
 1     A     10
 2     D      5
 2     B      6
 2     C    234

그런 다음 피벗 테이블을 사용하여 원하는 대로 데이터를 요약하고 드릴다운할 수 있습니다.

이제 문제는 이 데이터를 불확실한 수의 열로 재구성하는 방법입니다. 다행히도 매우 간단한 VBA 절차를 통해 이 작업을 수행할 수 있습니다. 아래 코드를 새 VBA 모듈에 붙여넣습니다( Alt+를 누른 다음 > F11로 이동 ).InsertModule

Sub SalvageMyTable()
Dim sOrig As Worksheet, sOut As Worksheet
Dim arrIn() As Variant
Dim rOut As Range, arrOut() As Variant
Dim i As Long, j As Long, k As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set sOrig = ActiveSheet
'Store original data in an array for faster processing
arrIn = sOrig.UsedRange.Value
'Create maximal output array. This can be resized before outputting the data.
ReDim arrOut(0 To 2, 0 To UBound(arrIn, 1) * (UBound(arrIn, 2) - 1) / 2) As Variant
'Loop through original table and copy values to output array.
For i = LBound(arrIn, 1) + 1 To UBound(arrIn, 1)
    For j = LBound(arrIn, 2) + 1 To UBound(arrIn, 2) Step 2
        If arrIn(i, j) <> "" Then
            arrOut(0, k) = arrIn(i, LBound(arrIn, 2))
            arrOut(1, k) = arrIn(i, j)
            arrOut(2, k) = arrIn(i, j + 1)
            k = k + 1
        Else
            Exit For
        End If
    Next j
Next i
'Resize output array
ReDim Preserve arrOut(0 To 2, 0 To k) As Variant
'Create output sheet and print output there.
Set sOut = Worksheets.Add
sOut.Name = "Reorganized Data"
sOut.Range("A1").Value = "Day"
sOut.Range("B1").Value = "Item"
sOut.Range("C1").Value = "Quantity"
sOut.Range("A2").Resize(k, 3).Value = Application.WorksheetFunction.Transpose(arrOut)
'Reset Excel settings
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

원본 데이터가 포함된 워크시트를 열어두세요. 그런 다음 VBA 편집기로 이동하여 이 코드를 실행합니다( 키를 눌러 F5). 이 코드는 "Reorganized Data"라는 새 워크시트의 세 열에 변환된 데이터를 출력합니다.

답변2

해당 데이터 세트에 대한 데이터 분석을 안정적으로 완료하려면 데이터 설정/구성/입력 방법을 변경해야 합니다.

Excel에서 수행할 수 있지만 설정하기에도 매우 쉬운 데이터베이스입니다.

품목표(품목번호, 이름, 설명)
차량표(차량번호, 이름, 설명, 유형, 제조사)
사용표(품목번호, 차량번호, 날짜, 사용번호)

또한 Excel에서 다양한 시트를 사용하고 데이터 유효성 검사를 사용하여 사용량 시트에 항목과 차량의 드롭다운을 제공할 수도 있습니다. 프로그래밍 경험이 없다면 이것이 최선의 선택일 수 있습니다.

귀하의 문제에 답변하지 못해 죄송합니다. 실제 문제는 데이터 구성입니다.

답변3

먼저 샘플 데이터를 사용하여 아래 형식 Day NQ의 테이블을 다음과 같이 변환합니다.

1 A 10
2 D 6
2 B 6
2 C 234

그런 다음 이 테이블을 피벗하면 결과를 얻을 수 있습니다. 위의 표로 변환하려면 새 시트를 생성해야 합니다. 기본 시트에 10개의 행과 3개의 이름 수량 쌍이 있다고 가정하면(Sheet1을 헤더 행/열이 아닌 시트 이름으로 가정) 행 1에서 30(10*3)까지 새 시트에서 아래 수식을 사용할 수 있습니다.

1    =Sheet1!A1  =Sheet1!B1  =Sheet1!C1
    ...
10   =Sheet1!A10  =Sheet1!B10  =Sheet1!C10
11   =Sheet1!A1  =Sheet1!D1  =Sheet1!E1
    ...
20   =Sheet1!A10  =Sheet1!D10  =Sheet1!E10
21   =Sheet1!A1  =Sheet1!F1  =Sheet1!G1
    ...
30   =Sheet1!A10  =Sheet1!F10  =Sheet1!G10

답변4

아래 이미지를 찾아주세요. 1은 서비스된 부품을 나타내고 0은 "변경되지 않은" 부품을 나타냅니다. 이것이 데이터가 표현되는 방식이라면 간단한 피벗 테이블을 사용하여 문제를 해결할 수 있습니다.
여기에 이미지 설명을 입력하세요

관련 정보