☰

Excel에서 특정 열이 반복되지 않도록 유지

tag-icon • tag-icon microsoft-excel microsoft-excel-2010
Excel에서 특정 열이 반복되지 않도록 유지

주문 세부 정보가 나열된 ERP 시스템에서 가져온 보고서가 있습니다. 주문 번호, 고객 코드, 고객 이름, 주문 날짜, 주문 상태, 주문 총액, ​​제품 코드, 제품 이름, 주문 수량, 단가 및 확장 가격이 나열됩니다. 주문에 여러 줄이 있으면 헤더 정보가 여러 번 나열됩니다.

이것이 원시 데이터의 모습입니다. 주문 보고서형식이 지정되지 않음

각 줄 세부 줄에 대해 머리글 줄이 반복되지 않도록 Excel에서 방법을 찾으려고 노력 중입니다.

작업이 완료되면 데이터가 다음과 같이 표시되는 것을 선호합니다. 기본적으로 각 줄은 해당 헤더 행 아래에서 반복됩니다. 주문보고서형식1

또 다른 허용되는 형식은 다음과 같습니다. 나는 이것이 더 간단하다고 생각합니다. 피벗 테이블을 사용하여 이 작업을 수행할 수 있었습니다. 여기에 이미지 설명을 입력하세요

VBA가 필요한지 여부는 확실하지 않습니다. 행을 강조 표시하고 중복 항목 제거를 사용해 보았지만 모든 행이 함께 이동되었습니다. 어떤 도움이라도 대단히 감사하겠습니다.

답변1

나는 이것이 눈살을 찌푸리는 것을 알고 있지만 문제가 흥미로워서 방금 VBA를 작성했습니다.

아래 코드를 시도해 보세요. 귀하가 제공한 예에서는 작동하도록 상수를 설정했지만 실제 응용 프로그램에서는 이를 변경하고 싶을 수도 있습니다.

Function CompressReport()

    'Settings for which columns are the header and details
    Const fHC As Long = 1   'First header column number
    Const lHC As Long = 6   'Last header column number
    Const fDC As Long = 7   'First detail column number
    Const lDC As Long = 11  'Last detail column number

    'Declarations
    Dim rStart&, rStop&, rNew As Long
    Dim r&, c As Long
    Dim ws As Worksheet
    Dim wsNew As Worksheet
    Dim s1$, s2 As String

    'Set the source worksheet to be compressed
    '(Here are a few methods to do this. Pick one.)
    Set ws = Sheet1
    Set ws = Worksheets(1)
    Set ws = Worksheets("Sheet1")

    'Add a new worksheet for our results
    Set wsNew = Worksheets.Add(After:=ws)

    With ws
        'Copy the first row of headers
        .Range(.Cells(1, fHC), .Cells(1, lHC)).Copy wsNew.Cells(1, 1)
        rNew = 2

        'Loop through all the rows
        For rStart = 2 To ws.UsedRange.Rows.Count

            'Copy the header information
            .Range(.Cells(rStart, fHC), .Cells(rStart, lHC)).Copy wsNew.Cells(rNew, 1)

            'Add a thick border (This wasn't in the OP but I recommend it)
            With wsNew.Range(wsNew.Cells(rNew, 1), wsNew.Cells(rNew, lHC - fHC + 1)).Borders(xlEdgeTop)
                .LineStyle = xlContinuous   'You could also try xlDouble
                .Weight = xlThick
            End With

            'Collect the header information into a single unique ID
            s1 = ""
            For c = fHC To lHC
                s1 = s1 & "|" & .Cells(rStart, c).Value
            Next

            'Find the next row with different information
            For rStop = rStart + 1 To .UsedRange.Rows.Count
                s2 = ""
                For c = fHC To lHC
                    s2 = s2 & "|" & .Cells(rStop, c).Value
                Next
                If s2 <> s1 Then Exit For
            Next
            rStop = rStop - 1

            'Copy the detail headers and information
            .Range(.Cells(1, fDC), .Cells(1, lDC)).Copy wsNew.Cells(rNew + 1, 2)
            .Range(.Cells(rStart, fDC), .Cells(rStop, lDC)).Copy wsNew.Cells(rNew + 2, 2)

            'Increase the row we're pasting in the new worksheet
            ' +1 for header data, +1 for detail headers, +n for detail information
            rNew = rNew + 1 + 1 + (rStop - rStart + 1)

            'Increase the row we're copying in the source worksheet
            rStart = rStop  'The FOR loop will iterate it +1

        Next

    End With

    'Formatting (feel free to add to this part)
    With wsNew
        .Columns.AutoFit
    End With

    'Cleanup
    Set wsNew = Nothing
    Set ws = Nothing

End Function

답변2

여기 당신의 목표를 달성하기 위한 작은 비결이 있습니다. 모든 열의 셀에 적용할 수 있습니다. 다음과 같이 시작한다고 가정해 보겠습니다.

여기에 이미지 설명을 입력하세요

그리고 우리는 추가 정보를 모두 보지 않으려고 합니다.마이크등 셀을 클릭합니다A2조건부 서식을 적용하여 셀의 값이 그 위의 셀과 동일한 경우 글꼴 색상을 셀 배경 색상과 동일하게 만듭니다.

여기에 이미지 설명을 입력하세요

그런 다음 셀을 복사합니다.A2그리고 열 아래에 PasteSpecialFormats를 입력합니다. 이는 반복되는 값을 "숨깁니다".

여기에 이미지 설명을 입력하세요

실제 데이터는 그대로 유지되고 표시만 변경됩니다!

답변3

나는 넣었다

                        마크다운의 CSV 데이터 액세스하려면 "수정"을 클릭하세요.

Sheet1에 넣고 Sheet2를 다음과 같이 만들 수 있었습니다.

두 개의 도우미 열을 사용합니다. 물론 원하는 만큼 오른쪽으로 이동할 수 있고 숨길 수도 있습니다.

  • A1(Sheet2에서)을 다음으로 설정 =Sheet1!A1하고 오른쪽으로 끌어서 여러 줄에 걸쳐 중복되는 열을 덮습니다. 귀하의 예에서는 Column 입니다  F. (제 예에서는 Column 입니다  C.)
  • Y2및 2로 설정 Z2합니다 1. 열의 값은  YSheet1 행을 나타냅니다.이것행에서 데이터를 가져오고 있습니다. 열  은 헤더 행(시트1의 왼쪽 열, 즉 키 필드에서 데이터 가져오기)인 경우, 하위 제목 행인 경우, 하위 데이터 행(오른쪽 열에서 데이터 가져오기)인 경우 Z입니다 . 시트1), 빈 행(마지막 데이터 행 아래)인 경우.1230
  • A2로 설정 =IF($Z2=1, INDEX(Sheet1!A:A, $Y2), ""). 해당하는 경우 오른쪽으로 끌어 주요 데이터에만 사용되는 열을 덮습니다. 귀하의 예에서는 Column 에서 시작하는 키가 아닌 데이터가 있기 때문에 이는 적용 가능하지 않습니다  B. (내 예에서는 Column 을 통해 이루어집니다  B.) 이는 도우미 열의 정의를 구현합니다. Z이면 1Sheet1에서 키 데이터를 가져오고, 그렇지 않으면 공백입니다.

  • 내 예에서는 C2다음과 같이 설정했습니다.

    =CHOOSE($Z2+1, "", INDEX(Sheet1!C:C, $Y2), Sheet1!D$1, INDEX(Sheet1!D:D, $Y2))

    귀하의 예에서는 다음과 같이 설정해야 합니다 B2.

    =CHOOSE($Z2+1, "", INDEX(Sheet1!B:B, $Y2), Sheet1!G$1, INDEX(Sheet1!G:G, $Y2))

    BSheet2 열이 가져올 수 있는 두 개의 Sheet1 열을 반영합니다  .

    • 열  B(“고객 코드”) 또는
    • 열  G(“제품 코드”)

    다시 말하지만, 이는 도우미 열에서 지시한 작업을 수행합니다. , , 및 , , 및 에 매핑할 값을 1추가 합니다 .  첫 번째 인수를 사용하여 다음 인수를 색인화하므로Z01231234CHOOSE

    • 이면 비어 Z있습니다 0.
    • 이면 주요 데이터를 가져오고 Z,1
    • 이면 Sheet1 Row에서 제목을 가져 Z오고21
    • 이면 키 Z가 3아닌 데이터를 가져옵니다.

  • 다음 Y3으로 설정​=IF($Z2<3, $Y2, $Y2+1)Z3

    =IF($Z2=0, 0, IF($Z2<3, $Z2+1, IF(INDEX(Sheet1!A:A,$Y2+1)="", 0,
                    IF(INDEX(Sheet1!A:A,$Y2)=INDEX(Sheet1!A:A,$Y2+1), 3, 1))))

    (모두 한 줄에). 즉, Z이전 행의 값이 1or 2(또는  0)인 경우 이 Y값을 이전 행의 값과 동일하게 설정합니다. 이는 데이터베이스 테이블의 모든 행(열의 모든 고유 값 집합 A- FSheet1)이 Sheet2에 최소 3개의 행을 생성하기 때문입니다. 그렇지 않으면 Y값을 증가시켜 Sheet1의 다음 행을 지정합니다.

    이전 Z값이 이면 0완료된 것이므로 0으로 채웁니다. 이전 Z값이 1또는 2이면 다음 값으로 올라갑니다. 그렇지 않으면 Sheet1 키 데이터를 살펴보세요. 비어 있으면 데이터의 끝에 있다고 가정하고 Z로  설정합니다 0. 이전 행과 동일하다면 를 사용하여 3진행 중인 작업을 계속하세요. 그렇지 않으면 새로운 고유 값 세트를 사용하게 되므로 1.

  • 모든 데이터를 얻을 수 있을 만큼 아래로 드래그하세요.

고유 값이 개별적으로 고유하지 않은 경우(예: A4= A5이지만 B4≠ 일 수 있는 경우 B5) 열의 테스트를 확장하여  Z 필요한 만큼 많은 열을 테스트합니다( 와 결합 AND(…)).

=$Z2=2분명히 나는 ​​부제목의 형식을 적절하게 지정하기 위해 이라는 수식과 함께 조건부 형식을 사용했습니다 .

관련 정보