엑셀 동적 범위를 참조하는 콤보상자 만들기

Question

[양식] 도구 모음에 있는 콤보 상자 도구를 사용하여 고객의 이름을 선택하도록 만들려고 합니다.
콤보 상자의 입력 범위를 현재 입력된 고객 이름 범위로 지정하면 지금은 이름을 선택할 때 문제가 없지만, 이후에 고객이 추가되었을 때 콤보 상자의 입력 범위를 다시 조정해야 하는 번거로움이 있더군요
콤보상자의 입력 범위가 알아서 변하도록 할 수 있을까요?

Answer

수식을 이용하여 이름을 정의한 다음 콤보 상자의 입력 범위로 이름을 설정하여 문제를 해결할 수 잇습니다. 고객의 이름이 추가되었을 때 또는 삭제되었을 때 자동으로 범위가 늘었다 줄었다 하는, 말 그대로 동적 범위가 되도록 수식을 만들어 이름을 정의하면 됩니다.
동적 범위를 구하는 방법은 여러 가지입니다. 여기서는 COUTA 함수를 이용하는 방법을 소개합니다.

동적 범위.xlsx

0.01MB

Step by Step

아래 위크시트에서 콤보 상자의 입력 범위는 B3:B8 셀 연결은 E2로 설정되어 있습니다.
콤보 상자의 목록 단추를 누르고 이름을 선택하면 E2 셀에 몇 번째 이름이 선택되었는지 번호로 표시됩니다. 일반적인 방법입니다.

2. 수식]-이름]-정의-메뉴를 선택한 다음 이름을 고객이름으로 입력합니다.
[참조대상] 입력 상자에 수식 =OFFSET($B$3,0,0COUNTA($B$3:$B$100))을 입력하고 확인 단추를 클릭합니다.

3. OFFSET(참조,행,열,높이,너비)함수는 참조에서 지정한 행,열만큼 떨어진 위치에서 지정한 높이,너비만큼의 영역을 구합니다. 여기서 참조는 이름이 B3 셀부터 입력되어 있으므로 B3으로 지정해야 합니다.

- 수식 =OFFSET($B$3,0,0COUNTA($B$3:$B$100))은 B3 셀에서 0행 0열 떨어진 위치 즉, B3셀부터 COUNTA 함수로 구한 높이만큼의 영역을 구합니다.

• COUNTA($B$3:$B$100)은 B3:B100 영역에서 비어있지 않은 셀의 개수를 구합니다. 만약 B3:B100영역에 10개의 이름이 입력되어 있으면 10, 12개의 이름이 입력되어 있으면 12를 결과로 되돌립니다.
• 결과적으로 OFFSET 함수는 B3 셀부터 이름이 입력되어 있는 범위까지 구합니다. 여기서 B3:B100으로 COUNTA 함수의 범위를 지정한 것은 최대 100행까지 설정한 것이므로 용도에 따라 더 크거나 더 적은 범위를 지정해도 상관없습니다.

 

4. 콤보 상자 컨트롤을 마우스 오른쪽 버튼으로 누르고 컨트롤 서식 메뉴를 선택합니다. 컨트롤 서식 대화 상자에서 입력 범위를 =고객이름으로 지정한 다음 확인 단추를 클릭합니다. 고객이름 이란 이름으로 정의한 OFFSET 수식이 반환하는 범위를 콤보 상자의 컨트롤의 입력 범위로 지정하는 것입니다.

5. B9:B10 영역에 이름을 추가한 다음 콤보 상자의 목록 단추를 눌러보면 추가한 이름까지 표시되는 것을 알 수 있습니다. 이름을 더 많이 추가하거나 삭제한 다음 콤보 상자에 표시되는 목록을 확인해 보세요