[EXCEL] 엑셀 함수로 상세히 페이지 기능 구현(시트의 문자로 다른 시트 문자로 이동하는 기능)

기능설명

실무라는 것이 항상 필요한 사람한테는 필요한 기능인데, 한 마디로, 한 문장으로 표현하기가 애매한 경우가 있습니다. 상세히 페이지 기능 구현이라고 제목을 달았지만, 결국 같은 글자 찾아가는 링크를 의미합니다.

 

sheet1의 A열에는 도면1, 도면2, ... 가 있습니다.

Sheet2 A열에는 도면1, 도면2, ... 등의 제목이 있고, 옆으로 상세 내용(예를 들어 설명서, 사양 등)을 적을 수 있습니다. 활용하면 파랗게 링크된 참고를 눌러 상세한 내용을 볼 수 있는 페이지를 만든 것이죠.

핵심 기능은, Sheet1의 A열의 문자열을 찾아서 Sheet2 A열의 동일한 문자로 이동하는 것입니다.

 

사용된 함수

 

사용된 함수는 IFERROR, HYPERLINK, MATCH, TEXT입니다. 활용에 따라서 IF 등도 사용할 수 있겠지요.

IFERROR(Value, Value_if_error)
- 식이나 식 자체의 값(Value)이 오류인 경우 value_if_error를 반환하는 함수
HYPERLINK(Link_location, Friendly_name)
- 저장된 문서, sheet로 이동하는 함수
MATCH(Lookup_value, Lookup_array, Match_type)
- 범위(Lookup_array)에 지정된 값(Lookup_value)의 상대 위치를 찾아주는 함수
TEXT(Value, Format_text)
- 셀의 참조 수식(Value)을 문자로 변환하는 함수

수식

 

B2셀에 아래의 수식을 넣고, 아래로 끌면 완성입니다.

=IFERROR(HYPERLINK("#"&"Sheet2!"&"$A$"&MATCH(TEXT(A2, 0),Sheet2!$A:$A,0),"참고"), "도면 값이 없습니다.")

숫자만 늘려야된다라는 오해를 방지하기 위해다른 제목을 넣어봤습니다.

링크 에러가 나면, #N/A 같은 에러 문구 대신 안내 문구를 넣어주기 위해 IFERROR를 사용하였습니다.

원하는 시트로 이동하기 위해서는 샾(#)과 느낌표(!)를 넣어서 참조해줍니다. 문구를 찾기 원하는 시트는 Sheet2의 A열 이므로, #Sheet2!$A를 시작으로 엔퍼샌드(&)로 문자를 연결해주면 됩니다.

 

Sheet2의 A열까지는 지정할 수 있는데, 위치(행)는 어떻게 아느냐? MATCH 함수를 사용하면 알 수 있습니다.

MATCH(TEXT(A2, 0),Sheet2!$A:$A,0)

A2의 문자 값, "도면1"을 Sheet2의 A열 전체($A:$A)에서 찾고, 몇 번째 행인지 알려줄 수 있죠. 그 행 위치를 &로 연결하면, 무슨 시트의 몇 번째 열, 몇 번째 행인지 알 수 있게 됩니다.

기능의 활용

Sheet1의 도면3옆 참고(B3)를 마우스로 눌러볼게요.

Sheet2의 도면3이 있는 위치로 이동하였습니다.

마찬가지로, 다른 제목 옆의 참고(B5)를 누르면, Sheet2 A열의 다른 제목이 있는 위치로 이동합니다.

하이퍼링크 함수이다 보니, 링크 위치가 없는 링크를 누르면 파일을 열 수 없다는 오류가 발생하네요.

엑셀의 수식 기능을 활용하기 위해 PowerPoint 대용으로 엑셀을 사용하는 회사들도 꽤 있는 것 같습니다. 사양서 등을 만들 때 활용하면, 유용할 수 있는 수식이었습니다.

fosterahope_excel_0_2.xlsx

0.01MB