엑셀에서 조건에 따라 행을 열로 변환하는 2가지 방법

일반적으로 행을 열로 변환할 때는 TRANSPOSE 함수를 가장 많이 사용합니다. 하지만 이 함수만으로는 고유값처럼 특정 조건에 맞는 결과를 얻을 수 없습니다. 이번 튜토리얼에서는 엑셀에서 조건에 따라 행을 열로 변환하는 방법을 단계별로 살펴보겠습니다.

엑셀에서 조건에 맞춰 행을 열로 변환하는 2가지 실용적인 방법

아래 그림에는 여러 제품과 수량으로 구성된 데이터 세트가 준비되어 있습니다. 여기서 행을 열로 변환해야 하는데, 데이터에 중복 항목이 포함되어 있으므로 고유값을 기준으로 행을 열로 변환해 보겠습니다. 먼저 INDEX, MATCH, COUNTIF, IF, IFERROR 함수를 조합한 수식을 만들고, 이어서 동일한 작업을 수행하는 VBA 코드까지 실행해 보겠습니다.

1. INDEX, MATCH, COUNTIF 함수를 활용한 수식으로 조건에 따라 행을 열로 변환하기

먼저 INDEX, MATCH, COUNTIF, IF, IFERROR 함수를 배열 형태로 결합한 수식을 적용해 보겠습니다.

1단계: INDEX, MATCH, COUNTIF 함수 입력

• 고유한 제품명을 추출하기 위해 E5 셀에 아래 수식을 입력합니다.

2단계: 배열 수식 적용

• 수식을 배열로 적용하려면 Ctrl + Shift + Enter 키를 누릅니다.
  ※ 참고: Microsoft 365 등 최신 버전의 엑셀에서는 Enter 키만 눌러도 배열 수식이 자동으로 처리됩니다.

• 그러면 첫 번째 고유값이 표시됩니다.

3단계: 셀 자동 채우기

• 모든 고유값을 가져오려면 채우기 핸들(AutoFill) 도구를 이용해 열을 자동으로 채웁니다.

4단계: IFERROR 함수 입력

• 수량의 행 값을 열로 변환하기 위해 다음 수식을 입력합니다.

5단계: 배열 수식 적용

• 배열로 삽입하려면 Ctrl + Shift + Enter 키를 누릅니다.

• 결과적으로 아래 이미지와 같이 F5 셀에 첫 번째 변환 값이 나타납니다.

• 채우기 핸들로 열을 아래쪽으로 드래그해 자동 채웁니다.

• 마지막으로 채우기 핸들을 이용해 행 방향으로도 자동 채웁니다.
• 그러면 아래 이미지처럼 모든 행이 열로 변환됩니다.

더 읽어보기: 엑셀에서 그룹별 여러 행을 열로 변환하기

함께 읽으면 좋은 글

• 엑셀에서 중복 행을 열로 변환하는 방법 (4가지)
• 엑셀 VBA: 그룹별 여러 행을 열로 변환하기
• 엑셀에서 여러 열을 하나의 열로 변환하는 방법 (3가지 유용한 방법)
• 엑셀에서 역전치(Reverse Transpose)하는 방법 (3가지 간단한 방법)

2. VBA 코드를 실행해 조건에 따라 행을 열로 변환하기

1단계: 모듈 생성

• 먼저 Alt + F11 키를 눌러 VBA 편집기를 실행합니다.
• 삽입(Insert) 메뉴를 클릭합니다.
• 모듈(Module) 옵션을 선택해 새 모듈을 만듭니다.

2단계: VBA 코드 입력

• 아래 VBA 코드를 붙여넣습니다.

Sub TransposeRows()
'Define Variables
    Dim RowsNumber As Long
    Dim p As Long
    Dim xColCr As String
    Dim xColumn  As New Collection
    Dim InputRng As Range
    Dim OutputRng As Range
    Dim RngText As String
    Dim CountRow As Long
    Dim xRowRg As Range
    On Error Resume Next
'Set values and Input Box fot Input Range
    RngText = ActiveWindow.RangeSelection.Address
    Set InputRng = Application.InputBox("Select Your Input Range for 2 columns:", "ExcelDemy", RngText, , , , , 8)
    Set InputRng = Application.Intersect(InputRng, InputRng.Worksheet.UsedRange)
'Apply Condition to count only 2 columns and to show msg box for alerting to select only two columns
    If InputRng Is Nothing Then Exit Sub
    If (InputRng.Columns.Count <> 2) Or _
       (InputRng.Areas.Count > 1) Then
        MsgBox "The specified range is only area for 2 columns ", , "ExcelDemy"
        Exit Sub
    End If
'Set values and Input Box fot Input Range
    Set OutputRng = Application.InputBox("Select Your Output Range (one cell):", "ExcelDemy", RngText, , , , , 8)
    If OutputRng Is Nothing Then Exit Sub
    Set OutputRng = OutputRng.Range(1)
'Count Rows
    RowsNumber = InputRng.Rows.Count
'Apply For loop
    For p = 2 To RowsNumber
        xColumn.Add InputRng.Cells(p, 1).Value, InputRng.Cells(p, 1).Value
    Next
    Application.ScreenUpdating = False
    For p = 1 To xColumn.Count
        xColCr = xColumn.Item(p)
        OutputRng.Offset(p, 0) = xColCr
        InputRng.AutoFilter Field:=1, Criteria1:=xColCr
        Set xRowRg = InputRng.Range("B2:B" & RowsNumber).SpecialCells(xlCellTypeVisible)
        If xRowRg.Count > CountRow Then CountRow = xRowRg.Count
'Copy and paste with transpose
        InputRng.Range("B2:B" & RowsNumber).SpecialCells(xlCellTypeVisible).Copy
        OutputRng.Offset(p, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        Application.CutCopyMode = False
    Next
    OutputRng = InputRng.Cells(1, 1)
    OutputRng.Offset(0, 1).Resize(1, CountRow) = InputRng.Cells(1, 2)
    InputRng.Rows(1).Copy
    OutputRng.Resize(1, CountRow + 1).PasteSpecial Paste:=xlPasteFormats
    InputRng.AutoFilter
    Application.ScreenUpdating = True
End Sub

3단계: 프로그램 실행

• 먼저 코드를 저장한 후 F5 키를 눌러 프로그램을 실행합니다.
• 제목 행을 포함한 데이터 세트를 선택합니다.
• OK(확인)를 클릭합니다.

• 결과를 출력할 셀 하나를 선택합니다.
• 그런 다음 OK(확인)를 클릭합니다.

• 아래 이미지와 같이 행이 열로 변환된 결과를 확인할 수 있습니다.

더 읽어보기: 엑셀 VBA로 행을 열로 변환하는 방법 (4가지 예시)

맺음말

이 글이 엑셀에서 조건에 따라 행을 열로 변환하는 방법을 익히는 데 도움이 되었기를 바랍니다. 소개한 방법들을 직접 연습 데이터에 적용해 보며 실력을 다져 보세요. 여러분의 소중한 관심과 지원이 이런 유용한 튜토리얼을 계속 만들어가는 원동력이 됩니다.

궁금한 점이 있다면 언제든지 문의해 주세요. 아래 댓글란에 의견을 남겨주셔도 환영합니다.

ExcelDemy 팀은 항상 여러분의 질문에 빠르게 답변드리겠습니다.

저희와 함께 꾸준히 배워나가세요.

관련 글

• 파워 쿼리를 사용해 엑셀에서 열을 행으로 변환하는 방법
• 셀 값을 기준으로 엑셀에서 열을 행으로 변환하는 방법
• 엑셀 VBA로 여러 열을 행으로 변환하기 (2가지 방법)
• 작은따옴표가 포함된 쉼표 구분 목록으로 열을 변환하는 방법
• 엑셀 붙여넣기 전치 단축키: 4가지 쉬운 사용법
• 참조를 변경하지 않고 엑셀에서 수식 전치하기 (4가지 쉬운 방법)
• 엑셀 VBA에서 전치(Transpose)하는 방법 (3가지)