엑셀 / VBA / 여러 시트의 내용을 하나의 시트에 모으는 방법
동일한 형식에 내용만 다른 여러 시트의 데이터를 하나로 합치는 방법을 알아본다.
예를 들어 다음과 같은 엑셀 문서가 있다고 하자. 첫 번째 시트는 합쳐진 데이터가 모일 시트이고, 나머지 세 개는 데이터가 있는 시트이다.
[개발 도구]의 [Visual Basic]을 클릭한다.(만약 개발 도구 메뉴가 없다면 여기의 안내대로 개발 도구를 추가한다.)
[삽입]의 [모듈]을 클릭한다.
다음과 같이 코드를 입력한다.
Sub Merge()
Sheets(1).Activate '첫 번째 시트로 이동
ActiveSheet.Range("A1").CurrentRegion.Select '값이 있는 셀 선택
Selection.Delete '선택한 셀 삭제
Sheets(2).Activate '두 번째 시트로 이동
ActiveSheet.Range("A1").CurrentRegion.Select '값이 있는 셀 선택
Selection.Copy Destination:=Sheets(1).Range("A1") '첫 번째 시트에 붙여넣기
ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select '값이 있는 다음 행 첫 번째 셀 선택
Dim I As Integer
For I = 3 To 4
Sheets(I).Activate 'I 번째 시트로 이동
ActiveSheet.Range("A1").CurrentRegion.Select '값이 있는 셀 선택
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select '선택에서 1행 제외
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2) '첫 번째 시트에 붙여넣기
ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select '값이 있는 다음 행 첫 번째 셀 선택
Next
Sheets(1).Activate '첫 번째 시트로 이동
ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select '값이 있는 다음 행 첫 번째 셀 선택
End Sub
다음의 순서로 작업하는 코드이다.
- 첫 번째 시트를 선택하고 내용을 지운다.
- 두 번째 시트의 내용을 복사하여 첫 번째 시트에 붙여넣는다.
- 나머지 시트의 내용을 복사하여 첫 번째 시트에 붙여넣는다.
두 번째 시트에 대한 코드와 나머지 시트에 대한 코드가 다른 이유는 머리글 때문이다.
이제 [개발 도구]의 [매크로]를 클릭한다. 단축키는 Alt+F8이다.
Merge를 선택하고 [실행]을 클릭한다.
다음처럼 첫 번째 시트에 데이터가 다 모인다.
엑셀 / 함수 / AVERAGE, AVERAGEIF, AVERAGEIFS / 산술평균 구하는 함수
개요 AVERAGE는 산술평균을 구하는 함수이다. 특정 조건에 맞는 값들의 산술평균을 구하고 싶다면 AVERAGEIF 또는 AVERAGEIFS 함수를 사용한다. AVERAGE 구문 AVERAGEA(value1, , ...) value1은 필수 요소이고, 이후의 value는 선택 요소이다. 평균을 구하려는 셀, 셀 범위 또는 값으로, 1개에서 255개까지 지정할 수 있다. 예제 예제 1 1, 2, 3의 산술평균을 구한다. 예제 2 1, 2의 산술평균을 구한다. 범위에 문자가 포함된 경우 무시한다. AVERAGEIF 구문 AVERAGEIF(range, criteria, ) range ...
개요 IRR 함수는 주기적인 현금 흐름에 대한 내부수익률(internal rate of return)을 반환하는 함수입니다. 구문 IRR(values, ) values 필수 요소로, 셀에 대한 참조 또는 배열입니다. 양수 값과 음수 값이 각각 한 개 이상씩 포함되어야 합니다. 텍스트, 논리값 또는 빈 셀은 무시됩니다. guess 선택 요소로, IRR 계산에 처음 사용할 값입니다. guess에서 시작하여 결과가 0.00001% 이내의 오차 범위에 들어올 때까지 반복합니다. guess를 생략하면 0.1(10%)로 간주합니다. 20번 이상 반복한 후에도 ...
엑셀 / 함수 / MEDIAN / 중앙값(중간값) 구하는 함수
개요 MEDIAN 함수는 수의 집합에서 수를 크기순으로 나열했을 때 정가운데에 있는 수를 구하는 함수입니다. 그 수를 중앙값 또는 중간값이라 합니다. 구문 MEDIAN(number1, , ...) number1은 필수 요소이고, 이후의 number는 선택 요소입니다. 1개에서 255개까지 사용할 수 있습니다. 수의 개수가 홀수일 때는 크기순으로 나열했을 때 정가운데의 수를 반환합니다. 예를 들어 MEDIAN(3,4,5,6,6) 은 5입니다. 수의 개수가 짝수일 때는 크기순으로 나열했을 때 가운데에 있는 ...
엑셀에서 VBA로 만든 모듈을 다른 파일에서 사용하는 방법은, 그 모듈을 내보내고 다른 파일에서 가져오는 것입니다. 작업은 Visual Basic Editor에서 합니다. 모듈 내보내기 내보내기를 할 엑셀 파일을 열고 Visual Basic Editor를 엽니다. 단축키는 Alt+F11입니다. 내보낼 모듈을 선택하고 마우스 우클릭합니다. 팝업 메뉴에서 를 클릭하면 확장자가 bas인 파일로 저장됩니다. bas 파일은 텍스트 파일로, 메모장 등 텍스트 에디터로 ...
엑셀에 기호 또는 특수 문자를 입력하는 방법은 두 가지가 있습니다. 삽입 - 메뉴 상단에서 를 클릭합니다. 글꼴을 바꾸가면서 필요한 기호를 찾습니다. 기호를 선택한 후 을 클릭하면 기호가 셀에 삽입됩니다. 자음 + 한자 ㄱ, ㄴ 등 자음을 입력하고 한자 키를 누릅니다. 그러면 특수 문자가 나옵니다. 화살표를 클릭하면 입력할 수 있는 기호를 ...
엑셀 / 피벗 테이블 / 외부 파일 시트의 데이터로 피벗 테이블 만드는 방법
엑셀의 피벗 테이블은 데이터 분석을 위한 유용한 기능입니다. 피벗 테이블은 보통 같은 파일 내에서 같은 시트 또는 다른 시트에 만들게 되는데, 다른 파일에 있는 데이터로 피벗 테이블을 만들 수도 있습니다. 어떻게 만드는지 그 방법을 알아보겠습니다. 예제 파일 다음과 같은 내용의 Excel_1 파일이 있습니다. 데이터는 표로 지정되었습니다. 이를 Excel_2 파일에 피벗 테이블을 만들 ...
엑셀 / 함수 / TEXTJOIN / 여러 텍스트를 하나로 합치는 함수
개요 TEXTJOIN은 여러 문자열을 합하여 하나의 문자열로 만드는 함수입니다. CONCAT과 비슷하나, 구분 기호와 빈 셀 처리에 대한 설정이 가능합니다. 구문 TEXTJOIN(delimiter, ignore_empty, text1, , …) delimiter : 문자열 사이에 들어갈 구분 기호입니다. ignore_empty : 빈 셀을 무시할지 정합니다. TRUE면 무시하고, FALSE면 무시하지 않습니다. text1 : 연결할 텍스트 항목입니다. text2 : 연결할 추가 텍스트 항목입니다. 예제 예제 1 구분 기호 없이, 빈 ...
틀 고정 엑셀에 데이터를 입력할 때 보통 표 형태로 넣습니다. 첫 행에 제목을 넣고 밑으로 주욱 입력하거나, 첫 열에 제목을 넣고 오른쪽으로 주욱 입력을 하죠. 자료가 많다면 입력한 내용이 한 화면에 다 나오지 않습니다. 그 보이지 않는 부분을 볼려면 아래로 또는 오른쪽으로 스크롤해야 하는데, 그럴 경우 제목 행 또는 제목 열이 안보여서 ...
엑셀에서 정렬할 때 대부분 세로 방향으로 정렬합니다. 데이터를 세로 방향으로 정리하는 경우가 많기 때문입니다. 엑셀의 기본 정렬 방향도 위에서 아래로 향하는 세로 방향입니다. 그런데 항상 그런 건 아닙니다. 경우에 따라서 데이터를 가로 방향으로 정리하기도 있습니다. 그렇다면 정렬을 할 때에도 가로 방향으로 해야겠죠? 어떻게 해야 가로 방향 정렬을 할 수 있을까요? 해결책은 옵션에 ...
개요 PRODUCT는 곱을 구하는 함수이다. 곱은 *를 이용하여 구할 수도 있으나, 곱할 값들이 많으면 PRODUCT가 편하다. 구문 PRODUCT(number1, , ...) number1 : 필수 요소로, 곱하려는 첫 번째 숫자 또는 범위 number2, ... : 선택 요소로, 곱하려는 추가 숫자 또는 범위 최대 255개의 인수를 곱할 수 있다. 예제 예제 1 값을 지정하여 세 개의 값을 곱한다. 다음과 같은 결과를 얻는다. =A2*B2*C2 예제 2 범위를 ...