셀 내 데이터 유효성 검사 목록에서 비어 있지 않은 빈 문자열을 제외하는 매크로가 아닌 솔루션

Question 1

데이터 유효성 검사 드롭다운은 이에 대해 협조하지 않습니다. 그 안에 무엇이 있는지 생각하지 않고 정적 목록이나 지시한 셀만 보고 싶어합니다.

우리가 원하는 대로 할 수 없기 때문에 절충해 보겠습니다. 우리가 보고 싶은 값만 표시하는 연속 범위(그리고 사람들이 대부분 보지 않는 끝에 공백도 있음)를 제공하겠습니다.

내 예에서 데이터 유효성 검사 목록의 값(및 공백)이 포함된 셀은 A1:A15입니다. 다른 열로 이동하여 다음 수식을 입력하겠습니다.

=IFERROR(INDEX($A$1:$A$15,SMALL(IF($A$1:$A$15<>"",ROW($A$1:$A$15),999),ROW(1:1))),"")

이것을 다음 항목으로 입력해야 합니다.배열 수식이므로 수식 입력이 끝나면 CTRL + SHIFT + ENTER를 누르겠습니다. 다음으로 15행을 복사/붙여넣기하거나 채워보겠습니다. 결과는 A1:A15의 공백이 아닌 모든 값이 되고 마지막에는 모든 공백이 됩니다.

이제 데이터 유효성 검사 대화 상자를 열고 내 수식(A1:A15 아님)이 포함된 셀을 목록으로 지정하겠습니다. 공백은 여전히 남아 있지만 마지막에 있으므로 아무것도 보지 않기 위해 아래로 스크롤하지 않는 한 아무도 공백을 볼 필요가 없습니다.

완전히 깨끗하지는 않지만 동적이며 매크로를 피합니다.

그렇다면 그 공식은 무엇을 했나요?

=INDEX($A$1:$A$15,

값과 공백이 있는 원래 범위를 보고 보고 싶은 셀을 숫자별로 알려줍니다.

SMALL(

일련의 숫자를 보고 가장 작은 숫자부터 세어 하나를 돌려줍니다.

IF($A$1:$A$15<>"",

범위의 각 셀이 빈 문자열 이외의 항목과 일치하는지 확인합니다.

ROW($A$1:$A$15)

그렇다면 행 번호를 반환합니다.

,999),

그렇지 않으면 999를 반환합니다. 행이 1000개가 넘으면 여기에 더 큰 숫자가 필요하지만 이는 데이터 유효성 검사 선택에 충분할 것입니다. 이제 SMALL 함수에는 공백의 실제 행 번호 대신 999가 포함된 행 번호 목록이 있습니다.

ROW(1:1)

는 이 수식을 입력한 모든 셀에서 계산을 시작하는 동적 방법입니다. 수식을 복사하면 숫자가 늘어납니다. 이는 수식을 포함하는 첫 번째 행에서 첫 번째로 작은 숫자, 행 2에서 두 번째로 작은 숫자 등을 원한다는 것을 Small에게 알려줍니다.

는 SMALL행 번호를 다시 에 전달하여 INDEX해당 행의 값을 제공합니다. 999는 모두 마지막에 #REF 오류로 바뀌지만 IFERROR.

그 공식은 이해하기 까다로울 수 있습니다. 문제가 있는 경우 새 시트의 A1:A15에 샘플 데이터를 설정하고, 다른 열의 셀을 선택한 다음, 수식을 수식 입력줄에 정확하게 복사해 보세요(CTRL+SHIFT+ENTER를 잊지 마세요). 조금만 가지고 놀아 보면 익숙해질 것입니다.

행운을 빌어요!

Answer