엑셀에서 조건에 맞는 행을 다른 시트로 복사하는 6가지 방법

Microsoft Excel을 사용하다 보면 특정 기준(조건)에 맞는 행을 다른 워크시트로 복사해야 하는 상황이 자주 발생합니다. 이 글에서는 엑셀에서 조건에 따라 한 시트의 행을 다른 시트로 복사할 수 있는 다양한 방법을 소개합니다.

연습용 워크북 다운로드

아래 워크북을 내려받아 직접 실습해 보세요.

엑셀에서 조건에 맞는 행을 다른 시트로 복사하는 6가지 방법

엑셀에서 행을 복사하는 데 활용할 수 있는 여섯 가지 간단하고 유용한 방법을 소개합니다. 작업 목적과 데이터 형태에 맞는 방법을 골라 사용하시면 됩니다. 지금부터 각 방법을 하나씩 자세히 살펴보겠습니다.

1. 엑셀 필터 옵션으로 행 복사하기

이 과정을 설명하기 위해 단가, 무게, 총액 정보를 포함한 과일 데이터 세트를 예로 들겠습니다. 이 표 전체는 워크북의 Filter Op. 시트에 저장되어 있습니다. 필터링과 복사 기능을 활용해 해당 시트의 행을 Result1 시트로 옮겨 보겠습니다.

실행 단계:

• 먼저 데이터 범위를 선택합니다.
• 리본 메뉴에서 데이터 탭으로 이동합니다.
• 정렬 및 필터 그룹 아래의 필터 옵션을 클릭합니다.

• 행을 복사할 기준이 되는 열을 선택합니다. 여기서는 Fruits(과일) 열을 선택했습니다.
• 복사할 행에 해당하는 항목을 체크합니다. 예제에서는 Mango(망고)를 선택했습니다. 선택한 항목의 행만 복사되며, 해당 열의 모든 행을 복사하려면 (모두) 옵션을 선택하면 됩니다.

• 필터링된 전체 데이터를 선택한 후 Ctrl+C로 복사합니다.

• 워크시트 하단의 +(플러스) 버튼을 클릭해 새 시트를 만들거나, 단축키 Shift+F11을 사용합니다.
• 새 워크시트 Result1에서 Ctrl+V로 복사한 데이터를 붙여넣습니다.
• 이제 선택된 모든 데이터가 Filter Op. 시트에서 Result1 시트로 복사됩니다.

2. 고급 필터 기능으로 행 복제하기

이번에는 고급 필터(Advanced Filter)를 사용해 Sheet3의 행을 Sheet4로 복사해 보겠습니다. 테스트 조건은 총액이 150 초과인 과일입니다. 즉, 총액이 150보다 큰 행만 골라서 다른 시트로 옮기는 것입니다.

실행 단계:

• 결과를 받을 시트로 이동한 뒤, 데이터 탭에서 고급을 선택합니다.

• 다른 위치에 복사(Copy to another location) 옵션을 선택합니다.

• 목록 범위(List range) 입력란을 클릭하고 원본 시트로 이동해 전체 데이터 세트를 선택합니다.

• 조건 범위(Criteria range) 셀을 지정합니다.

• 복사 위치(Copy to) 항목을 선택하면 자동으로 결과 시트로 전환되므로, 해당 워크시트의 원하는 셀을 클릭합니다.

• 확인(OK) 버튼을 누릅니다.

• 지정한 조건에 맞는 행들이 원본 시트에서 결과 시트로 복사됩니다.

3. 배열 수식으로 한 시트에서 다른 시트로 행 복사하기

배열 수식을 활용하면 행 복사 과정을 자동화할 수 있습니다. 이번 예제에서는 앞선 데이터 세트에 상점 이름(Shop Names) 열을 하나 추가했습니다. 상점 이름에 따라 행을 분류해 각각의 새 워크시트로 복사할 것이며, 모든 워크시트 이름은 해당 상점 이름과 동일하게 지정합니다.

목표는 상점 이름에 맞는 행들을 새 워크시트로 복사하는 것입니다.

실행 단계:

• 먼저 상점 이름과 같은 이름의 새 워크시트를 생성합니다.

• 예를 들어 Rooted 시트로 이동한 뒤 셀 B4를 선택하고 아래 수식을 입력한 후 Ctrl+Shift+Enter를 누릅니다. MS Excel 365를 사용 중이라면 Enter 키만 누르면 됩니다.

수식 설명

이 수식에는 여러 엑셀 함수가 사용되었습니다. 배열 수식에 포함된 각 함수를 하나씩 살펴보겠습니다.

ROWS(array)

ROWS 함수는 배열 또는 셀 범위 참조를 인수로 받아, 지정한 범위나 배열에 포함된 행의 개수를 반환합니다.

COLUMN([reference])

COLUMN 함수에 셀 참조를 인수로 전달하면, 해당 셀 참조에 대응하는 열 번호를 구할 수 있습니다.

SMALL(array, n)

SMALL 함수를 사용하면 지정한 배열에서 n번째로 작은 값을 찾을 수 있습니다. 일반적으로 첫 번째 인수에는 데이터 세트 또는 배열의 범위가 들어가고, 두 번째 인수에는 반환하고자 하는 위치 값이 들어갑니다.

INDEX(array, row_num, [column_num])

INDEX 함수도 사용되었습니다. 첫 번째 인수는 필수 항목으로, 원하는 데이터 범위의 배열을 지정합니다. 두 번째 인수는 값을 반환할 행 번호입니다. 마지막 열 번호 인수는 선택 사항으로, 배열에서 특정 열의 값을 반환받고 싶을 때 지정합니다.

IFERROR(value, value_if_error)

IFERROR 함수는 배열 수식에서 가장 바깥쪽에 위치한 조건부 함수입니다. 주어진 값이 오류인지 확인하여, 오류일 경우 두 번째 인수로 지정한 값을 반환합니다.

• 이후 수식을 아래와 오른쪽으로 드래그해 일치하는 모든 행을 추출합니다.

• 다른 워크시트에도 동일한 방식을 적용합니다.

• Array Formula 시트에서 임의의 값을 변경해 보고, 결과 시트의 값이 자동으로 바뀌는지 확인할 수 있습니다.

• Rooted 워크시트로 이동하면 변경 사항이 반영된 것을 볼 수 있습니다.

4. 여러 함수를 조합하여 행 복제하기

이번에는 다른 워크시트에 결과가 자동으로 표시되도록 하는 방법을 살펴보겠습니다. 앞선 예제와 동일한 데이터 세트를 사용하며, 과일 목록을 상점 이름순으로 정렬하고 총액이 $130 이상인지 확인합니다. 드롭다운 목록에서 상점 이름을 선택하고 Enter 키를 누르면, 조건에 맞는 모든 행이 Functions 시트에서 Result2 시트로 자동 복사됩니다.

실행 단계:

• 먼저 Functions 시트의 셀 G5를 선택하고 아래 수식을 입력합니다.

• 키보드의 Enter 키를 누릅니다.

• 채우기 핸들을 아래로 드래그해 수식을 범위 전체에 복제합니다. 또는 플러스(+) 기호를 더블클릭해 자동 채우기를 할 수 있습니다.

• 이제 결과를 확인할 수 있습니다.

수식 설명

여기서는 다양한 엑셀 함수가 사용되었습니다. 각 함수의 세부 내용은 아래와 같습니다.

MAX(number1, [number2], ...)

MAX 함수는 숫자들을 인수로 받아 그중 가장 큰 값을 반환합니다.

AND(logical1, [logical2], ...)

AND 함수는 두 개 이상의 논리 조건을 인수로 받으며, 모든 조건이 충족되면 TRUE를, 그렇지 않으면 FALSE를 반환하는 논리 함수입니다.

IF(logical_condition, [value_if_true], [value_if_false])

IF 함수는 엑셀의 대표적인 조건부 함수로, 논리 조건을 평가하여 조건이 참(True)인지 거짓(False)인지에 따라 서로 다른 값을 반환합니다.

• 이어서 Result2 시트로 이동해 셀 B7에 아래 수식을 입력합니다.

• Enter 키를 누릅니다.

수식 설명

여기서 새로 등장하는 함수는 MATCH 함수입니다.

MATCH(lookup_value, lookup_array, [match_type])

MATCH 함수에서 첫 번째 인수는 검색하려는 조회 값입니다. 두 번째 인수는 조회 값을 검색할 셀 배열 범위이며, 마지막 match_type은 -1, 0, 1 값으로 엑셀이 lookup_value와 lookup_array의 값을 어떤 방식으로 비교할지 정의합니다.

• 이후 배열을 아래와 오른쪽으로 복사합니다.
• 이제 아무 상점 이름이나 선택하면, 해당 상점의 과일 중 총액이 $130 초과인 행의 상점 이름과 과일 이름이 표시됩니다.

5. 엑셀 FILTER 함수로 행 복사하기

FILTER 함수를 사용하면 지정한 기준에 따라 다양한 데이터를 손쉽게 걸러낼 수 있습니다. 아래 절차에 따라 이 함수로 조건에 맞는 행을 다른 시트로 복사해 보겠습니다. FILTER 시트의 데이터를 Result3 시트로 가져오는 것이 목표입니다.

실행 단계:

• 먼저 결과를 표시할 셀을 선택합니다.
• 해당 셀에 수식을 입력합니다. 이 예제에서는 Result3 시트의 셀 B5입니다.

• 키보드의 Enter 키를 누릅니다.
• 완성입니다! 조건을 충족하는 모든 행이 FILTER 시트에서 Result3 시트로 복사됩니다.

6. 엑셀 VBA로 한 시트에서 다른 시트로 행 복사하기

이번에는 동일한 작업을 VBA를 활용해 수행해 보겠습니다. 명령 버튼을 클릭하면 VBA 시트에서 총액이 150보다 큰 과일 목록을 찾아 Result4 시트로 옮깁니다. 조건에 맞는 행을 복사하는 단계를 살펴보겠습니다.

실행 단계:

• 먼저 개발 도구(Developer) 탭으로 이동합니다.
• 삽입(Insert) 옵션에서 ActiveX 컨트롤의 명령 버튼을 선택합니다.

• 속성(Properties) 옵션에서 버튼의 캡션과 글꼴을 변경합니다.

• 버튼을 클릭하면 VBA 편집창으로 이동합니다. 아래와 같이 코드를 작성합니다.

코드:

Private Sub CommandButton1_Click()
a = Worksheets("VBA").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To a
If Worksheets("VBA").Cells(i, 4).Value > 150 Then
Worksheets("VBA").Rows(i).Copy
Worksheets("Result4").Activate
b = Worksheets("Result4").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Result4").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("VBA").Activate
End If
Next
Application.CutCopyMode = False
ThisWorkbook.Worksheets("VBA").Cells(1, 1).Select
End Sub

• 이후 RunSub 버튼을 클릭하거나 단축키 F5를 눌러 코드를 실행합니다. 아니면 직접 버튼을 클릭해도 됩니다.

VBA 코드 설명

코드의 주요 라인을 설명하면 다음과 같습니다.

• VBA 시트의 전체 행 개수를 계산하여 변수 a에 저장합니다.
• IF 조건문으로 각 과일 행의 총액을 확인합니다.
• 다시 Result4 시트의 행 개수를 계산하여 변수 b에 저장합니다.
• b값을 증가시켜 가며 일치하는 값을 선택해 붙여넣습니다.

• 이후 VBA 편집기의 실행(Run) 버튼을 클릭합니다.
• 마지막으로 Result4 시트로 이동하면 조건에 맞는 행들이 다른 시트로 복사된 것을 확인할 수 있습니다.

마치며

지금까지 소개한 방법들을 활용하면 엑셀에서 조건에 맞는 행을 한 시트에서 다른 시트로 손쉽게 복사할 수 있습니다. 각 방법을 실제 예제와 함께 설명했습니다. 만약 이 외에 다른 방법을 알고 계시다면 언제든 공유해 주세요.

함께 읽으면 좋은 글

• 엑셀에서 다른 셀의 텍스트를 표시하는 방법 (4가지)
• 두 엑셀 시트를 비교하고 차이점을 복사하는 VBA 코드
• 엑셀에서 VBA로 서식 없이 값만 붙여넣는 방법
• 엑셀에서 복사·붙여넣기가 되지 않을 때 (원인 9가지 & 해결책)
• 엑셀에서 매크로로 여러 행을 복사하는 방법 (예제 4가지)