Excel에서 여러 열을 빠르게 하나의 열로 쌓는 방법은 무엇입니까?

Excel에서 텍스트를 열로 분할 기능은 지정된 구분 기호를 사용하여 단일 열의 데이터를 여러 열로 분할하도록 설계되었습니다. 하지만 여러 열의 값을 하나의 열로 결합하거나 쌓아야 하는 상황이라면 어떻게 해야 할까요? 아래 예제와 같이, 이 시나리오는 데이터 세트를 통합하거나 분석을 위해 정보를 준비하거나 추가 처리를 위해 보고서를 형식화할 때 자주 발생합니다. 불행히도 Excel에는 열을 수직으로 쌓는 내장 함수가 없지만 이 작업을 효율적으로 수행할 수 있는 몇 가지 실용적인 솔루션이 있습니다.

수식을 사용하여 여러 열을 하나로 쌓기

VBA를 사용하여 여러 열을 하나로 쌓기

범위 변환을 사용하여 여러 열을 하나로 쌓기

Power Query를 사용하여 여러 열을 하나로 쌓기

수식을 사용하여 여러 열을 하나로 쌓기

매크로 또는 추가 기능을 사용하지 않으려는 경우 INDEX 함수를 사용하는 배열 수식으로 여러 열을 하나의 열로 쌓을 수 있습니다. 이 방법은 동적 데이터 세트에 적합하거나 수동 작업을 피하고자 하는 경우에 유용합니다. 한 가지 장점은 소스 데이터가 변경되면 수식이 결과를 자동으로 업데이트한다는 것입니다. 하지만 범위 이름을 정확하게 사용해야 하며 참조된 범위 내에서 셀을 삽입하거나 삭제하지 않도록 주의해야 합니다.

1. 쌓고자 하는 데이터 범위를 선택합니다(예: A1:C4). 그런 다음 이름 상자 (수식 표시줄 왼쪽에 있음)에 의미 있는 이름을 입력합니다. 예를 들어 MyData를 입력하고 Enter키를 누릅니다. 이렇게 하면 범위에 이름이 지정되어 쉽게 참조할 수 있습니다.

2. 하나의 열이 시작될 위치에 빈 셀을 클릭하세요—보통 데이터 아래쪽이나 다른 시트에 해당됩니다. 선택한 셀에 다음 수식을 붙여넣습니다.

=INDEX(MyData,1+INT((ROW(A1)-1)/COLUMNS(MyData)),MOD(ROW(A1)-1+COLUMNS(MyData),COLUMNS(MyData))+1)

3. Enter 키를 눌러 확인한 후 모든 데이터가 나열될 때까지 채우기 핸들을 아래로 드래그합니다. #REF! #REF! 또는 비슷한 오류가 표시되면 모든 데이터가 나열된 것입니다. 필요하다면 오류 셀을 삭제하세요.

이 수식에서 MyData는 1단계에서 정의한 범위를 나타냅니다. COLUMNS(MyData)는 데이터의 열 개수에 따라 자동으로 조정됩니다. 수식을 입력한 후 MyData 내부에 열을 삽입하거나 삭제하지 마십시오. 그렇게 하면 결과에 영향을 미칠 수 있습니다. 데이터에 빈 셀이 포함되어 있는 경우 이러한 빈 셀도 쌓입니다. 필요한 경우 이후에 필터링할 수 있습니다. 큰 범위의 경우 채우기 핸들을 드래그하는 데 시간이 걸릴 수 있으므로 왼쪽 열에 데이터가 모두 있는 경우 채우기 핸들을 두 번 클릭해 보세요.

사용 중인 Excel 버전이 동적 배열을 지원하는 경우(Excel365 또는 Excel2021 이상) 다음을 시도해 볼 수 있습니다.

=TOCOL(MyData,1)

이 수식은 최신 Excel 버전에서만 사용 가능하며 즉시 여러 열을 하나의 열로 쌓습니다.

VBA를 사용하여 여러 열을 하나로 쌓기

열을 자주 쌓거나 더 많은 제어를 원한다면 VBA 매크로로 이 과정을 몇 번의 클릭만으로 자동화할 수 있습니다. VBA는 대규모 데이터 세트 또는 반복 작업을 처리하는 고급 사용자에게 이상적입니다. 그러나 매크로의 출처가 확실하지 않은 경우 매크로를 활성화하면 보안 위험이 발생할 수 있습니다. 항상 파일을 먼저 저장하고 익숙하지 않은 코드를 실행할 때는 주의하십시오.

1. Alt + F11을 눌러 Microsoft Visual Basic for Applications 창을 엽니다.

2. VBA 창에서 Insert > Module을 클릭합니다. 새 모듈에 다음 코드를 복사하여 붙여넣습니다.

VBA: 열을 하나로 쌓기

Sub ConvertRangeToColumn()
'UpdatebyExtendoffice
Dim Range1 As Range, Range2 As Range, Rng As Range
Dim rowIndex As Integer
xTitleId = "KutoolsforExcel"
Set Range1 = Application.Selection
Set Range1 = Application.InputBox("Source Ranges:", xTitleId, Range1.Address, Type:=8)
Set Range2 = Application.InputBox("Convert to (single cell):", xTitleId, Type:=8)
rowIndex = 0
Application.ScreenUpdating = False
For Each Rng In Range1.Rows
    Rng.Copy
    Range2.Offset(rowIndex, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
    rowIndex = rowIndex + Rng.Columns.Count
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

3. 매크로를 실행하려면 F5 키를 누르거나 실행 버튼을 클릭합니다. 실행 후 프롬프트가 나타납니다. 쌓고자 하는 데이터 범위를 선택하고 확인을 클릭합니다.

4. 결과를 표시할 대상 셀을 선택하라는 메시지가 표시됩니다. 확인 을 다시 클릭합니다. 그러면 열이 선택한 대상 셀에서부터 하나의 열로 쌓입니다.

오류가 발생하면 선택한 범위를 다시 확인하고 보호된 시트나 병합된 셀이 없는지 확인하십시오. 공백을 건너뛰거나 특정 열만 쌓는 등 고급 사용자 정의가 필요한 경우 코드를 추가로 수정할 수 있습니다. 원치 않는 데이터 손실을 방지하기 위해 VBA 코드를 실행하기 전에 작업을 저장했는지 꼭 기억하십시오.

범위 변환을 사용하여 여러 열을 하나로 쌓기

수식이나 코드를 작성하지 않고 빠르고 사용자 친화적인 솔루션을 선호하는 경우 Kutools for Excel의 Transform Range 기능을 사용할 수 있습니다. 이 유틸리티는 직관적인 인터페이스를 통해 데이터를 빠르게 처리하려는 사용자(예: 사무 관리자 또는 다차원 데이터를 루틴적으로 처리하는 사용자)에게 특히 유용합니다. Kutools를 사용하면 수식 오류의 위험을 줄이고 상당한 시간을 절약할 수 있지만 추가 기능을 설치해야 합니다.

무료로 설치한 후 Kutools for Excel 다음 단계를 따르십시오.

1. 하나의 열로 쌓고 싶은 열이나 데이터 범위를 선택합니다. 그런 다음 Kutools > 범위 > 변환 범위 를 클릭하여 대화 상자를 엽니다.

2. 변환 범위 대화 상자에서 범위를 단일 열로 변환 옵션을 선택하고 확인을 클릭합니다. 그다음 결과를 표시하고자 하는 셀을 선택합니다.

3. 마지막으로 확인을 클릭합니다. 선택한 열은 선택한 위치에서 즉시 하나의 열로 쌓입니다.

이 방법은 간단하고 오류가 없습니다. 결과가 마음에 들지 않으면 Ctrl + Z를 눌러 쉽게 실행 취소하거나 필요에 따라 Transform Range를 다시 실행할 수 있습니다. 데이터에 병합된 셀이 포함되어 있다면 예상치 못한 결과를 방지하기 위해 이 기능을 사용하기 전에 병합을 해제하는 것이 가장 좋습니다.

Power Query를 사용하여 여러 열을 하나로 쌓기

Power Query(일부 Excel 버전에서는 Get & Transform라고도 함)는 특히 대량의 데이터를 다루거나 반복되는 데이터 변환 작업을 자동화해야 하는 사용자에게 유용한 여러 열을 하나로 쌓는 또 다른 실용적인 방법입니다. Power Query는 Excel 2016 이상에 내장되어 있으며 Excel 2010 및 2013에서는 무료 추가 기능으로 제공됩니다. 이 도구는 고급 데이터 준비에 가장 유용하며 간단한 사용자 인터페이스로 복잡한 재구성 작업을 쉽게 처리할 수 있습니다. 그러나 초보자는 짧은 학습 곡선이 필요할 수 있습니다.

소스 테이블을 Power Query로 로드합니다. Excel에서 데이터를 선택합니다. Data > From Table/Range로 이동하여 Power Query Editor를 엽니다.

2. 쿼리 미리 보기에서 모든 열을 선택합니다. 변환 탭으로 이동하여 테이블 그룹을 찾고 행과 열을 전환하는 Transpose를 클릭합니다.

3. 다시 한번 전환된 모든 열을 선택합니다. 변환 탭에서 Any Column 그룹 아래에 있는 Unpivot Columns를 클릭합니다.

4. "Attribute" 열이 필요 없다면 제거합니다. "Attribute" 열 머리글을 마우스 오른쪽 버튼으로 클릭하고 Remove를 선택합니다.

5. Close & Load를 클릭하여 결과를 새로운 시트 또는 테이블로 Excel로 보내세요. 언제든지 소스 데이터가 변경되면 변환을 새로 고치거나 다시 적용할 수 있습니다.

Power Query를 사용하면 이 스태킹 프로세스를 재사용 가능한 쿼리로 저장하고 미래에 데이터가 변경되더라도 결과를 신속하게 업데이트할 수 있습니다.

참고: 이 방법은 소스 데이터의 원래 열 헤더 정보를 유지하지 않을 수 있습니다.

요약하자면, 각각의 방법은 다음과 같은 장점이 있습니다. 수식은 동적으로 데이터를 업데이트하는 데 적합하며, VBA는 반복 작업을 자동화하는 데 탁월하고, Kutools for Excel은 거의 기술적 지식 없이도 포인트 앤 클릭 작업이 가능하며, Power Query는 고급 또는 반복적인 변환에 이상적입니다. 항상 처리 후 남아 있는 공백, 병합된 셀 또는 서식 오류를 검토하십시오. 오류나 예상치 못한 결과가 나타나면 정의된 범위 또는 단계 선택을 다시 확인하십시오. 이러한 방법을 조합하여 사용하면 보고 및 분석 요구를 위해 Excel 데이터를 효율적으로 관리하고 변환하는 데 도움이 됩니다.