수식을 사용한 시트 이름 나열 – Excel 및 Google 스프레드시트

이 튜토리얼에서는 Excel에서 수식을 사용하여 통합 문서의 시트 이름을 나열하는 방법을 보여 줍니다

명명된 범위 및 수식을 사용하여 시트 이름 나열하기

Excel에는 통합 문서의 모든 워크시트를 나열할 수 있는 기본 제공 함수가 없습니다. 대신 두 가지 옵션이 있습니다

1. VBA 매크로를 사용하여 통합 문서의 모든 시트 나열하기
2. 모든 시트를 나열하는 수식 만들기

수식을 사용하려면 다음 단계를 따르세요

1. 명명된 범위 “워크시트” 만들기
2. 수식을 사용하여 모든 시트 이름을 나열합니다.

시트 이름에 대한 이름 범위 만들기

시트 이름에 대한 명명된 범위를 만들려면 Excel 리본에서다음과 같이 하세요: 수식 > 이름 관리자 > 새로만들기

이름 상자에 “워크시트”를 입력합니다

대화 상자의 “참조” 섹션에 다음 수식을 작성해야 합니다

=get.workbook(1) & t(now())"

이 수식은 통합 문서에 있는 모든 시트의 이름(이 형식의 배열: “[workbook.xlsm].Overview”)을 명명된 범위인 “Worksheets”에 저장합니다

“GET.WORKBOOK” 함수는 매크로 함수이므로 통합 문서를 열 때마다 시트 이름이 업데이트되도록 하려면 통합 문서를 매크로 사용 통합 문서(파일 형식: .xlsm)로 저장해야 합니다

참고: 이름 편집 대화 상자를 채울 때 통합 문서를 이름 범위의 범위로 선택해야 합니다

수식을 사용하여 시트 이름 나열

이제 수식을 사용하여 시트 이름을 나열합니다. 여기에는 INDEX, MID, FIND 및 ROWS 함수가 필요합니다

=INDEX(MID(Worksheets,FIND("]",Worksheets) 1,255),ROWS($B$5:B5))

• 위의 수식은 “Worksheets” 배열을 가져와 위치에 따라 각 시트 이름을 표시합니다.
• MID 및 FIND 함수는 배열에서 시트 이름을 추출합니다(통합 문서 이름은 제거).
• 그런 다음 INDEX 및 ROW 함수는 해당 배열의 각 값을 표시합니다.
• 여기서 ‘개요’는 통합 문서의 첫 번째 시트이고 ‘정리’는 마지막 시트입니다.

MID 및 FIND 함수가 시트 이름을 가져오는 방법에 대한 자세한 내용을 보려면 링크를 클릭합니다

대체 방법

이름 관리자에서 시트 이름 목록을 만드는 옵션도 있습니다. 대신

=get.workbook(1) & t(now())

“참조” 필드를 다음과 같이 설정합니다

=replace(get.workbook(1),1,find("]",get.workbook(1)),"")

이제 수식에 MID, FIND 및 ROWS가 필요하지 않습니다. 명명된 범위는 이미 시트 이름으로만 구성되어 있습니다

이 간단한 INDEX 수식을 사용하여 시트를 나열합니다

=INDEX(SheetName,B3)