드롭다운 목록

Excel의 드롭 다운 목록은 사용자가 자신의 값을 입력하는 대신 목록에서 항목을 선택하도록하려는 경우 유용합니다.

드롭다운 목록 만들기

Excel에서 드롭 다운 목록을 만들려면 다음 단계를 실행하십시오.

1. 두 번째 시트에서 드롭 다운 목록에 표시 할 항목을 입력합니다.

참고 : 사용자가 Sheet2의 항목에 액세스하지 못하도록하려면 Sheet2를 숨길 수 있습니다. 이를 위해 Sheet2의 시트 탭을 마우스 오른쪽 버튼으로 클릭하고 숨기기를 클릭합니다.

2. 첫 번째 시트에서 B1 셀을 선택합니다.

3. 데이터 탭의 데이터 도구 그룹에서 데이터 유효성 검사를 클릭합니다.

'데이터 유효성 검사'대화 상자가 나타납니다.

4. 허용 상자에서 목록을 클릭합니다.

5. 원본 상자를 클릭하고 Sheet2에서 A1 : A3 범위를 선택합니다.

6. 확인을 클릭합니다.

결과:

참고 : 드롭 다운 목록을 복사 / 붙여 넣기하려면 드롭 다운 목록이있는 셀을 선택하고 CTRL + c를 누르고 다른 셀을 선택한 다음 CTRL + v를 누릅니다.

7. 범위 참조를 사용하는 대신 소스 상자에 직접 항목을 입력 할 수도 있습니다.

참고 : 이렇게하면 드롭 다운 목록이 대소 문자를 구분합니다. 예를 들어 사용자가 yes를 입력하면 오류 경고가 표시됩니다.

기타 항목 허용

Excel에서 다른 항목을 허용하는 드롭 다운 목록을 만들 수도 있습니다.

1. 먼저 목록에없는 값을 입력하면 Excel에서 오류 경고를 표시합니다.

다른 항목을 허용하려면 다음 단계를 실행하십시오.

2. 데이터 탭의 데이터 도구 그룹에서 데이터 유효성 검사를 클릭합니다. '데이터 유효성 검사'대화 상자가 나타납니다.

3. 오류 경고 탭에서 '유효하지 않은 데이터를 입력 한 후 오류 경고 표시'를 선택 취소합니다.

4. 확인을 클릭합니다.

5. 이제 목록에 없는 값을 입력 할 수 있습니다.

항목 추가 / 제거

'데이터 유효성 검사'대화 상자를 열고 범위 참조를 변경하지 않고도 Excel의 드롭 다운 목록에서 항목을 추가하거나 제거 할 수 있습니다. 이것은 시간을 절약합니다.

1. 드롭 다운 목록에 항목을 추가하려면 항목으로 이동하여 항목을 선택합니다.

2. 마우스 오른쪽 단추를 클릭 한 다음 삽입을 클릭합니다. (Alt + H, I, I)

3. "셀 아래로 이동"을 선택하고 확인을 클릭합니다.

결과:

참고 : Excel에서는 범위 참조가 Sheet2!$A$1:$A$3에서 Sheet2!$A$1:$A$4로 자동 변경되었습니다. '데이터 유효성 검사'대화 상자를 열어 확인할 수 있습니다.

4. 새 항목을 입력합니다

결과:

5. 드롭 다운 목록에서 항목을 제거하려면 2 단계에서 삭제를 클릭하고 "셀 위로 이동"을 선택한 다음 확인을 클릭합니다.

동적 드롭 다운 목록

목록 끝에 항목을 추가 할 때 드롭 다운 목록을 자동으로 업데이트하는 수식을 사용할 수도 있습니다.

1. 첫 번째 시트에서 B1 셀을 선택합니다.

2. 데이터 탭의 데이터 도구 그룹에서 데이터 유효성 검사를 클릭합니다.

'데이터 유효성 검사'대화 상자가 나타납니다.

3. 허용 상자에서 목록을 클릭합니다.

4. 원본 상자를 클릭하고 수식을 입력합니다. =OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)

설명 : OFFSET 함수는 5 개의 인수를 사용합니다. 참조 : Sheet2!$A$1, 오프셋 할 행 : 0, 오프셋 할 열 : 0, 높이 : COUNTA(Sheet2!$A:$A) 및 너비 : 1. COUNTA(Sheet2!$A:$A)는 숫자를 계산합니다.

비어 있지 않은 Sheet2의 A 열에있는 값의 개수입니다.

Sheet2의 목록에 항목을 추가하면 COUNTA(Sheet2!$A:$A)가 증가합니다.

결과적으로 OFFSET 함수에서 반환 된 범위가 확장되고 드롭 다운 목록이 업데이트됩니다.

5. 확인을 클릭합니다.

6. 두 번째 시트에서 목록 끝에 새 항목을 추가하기 만하면됩니다.

결과:

드롭 다운 목록 제거

Excel에서 드롭 다운 목록을 제거하려면 다음 단계를 실행하십시오.

1. 드롭 다운 목록에서 셀을 선택합니다.

2. 데이터 탭의 데이터 도구 그룹에서 데이터 유효성 검사를 클릭합니다.

'데이터 유효성 검사'대화 상자가 나타납니다.

3. 모두 지우기를 클릭합니다.

참고 : 설정이 동일한 다른 모든 드롭 다운 목록을 제거하려면 모두 지우기를 클릭하기 전에 "이 변경 사항을 동일한 설정을 가진 다른 모든 셀에 적용"을 선택하십시오.

4. 확인을 클릭합니다.

제품 코드

이 예에서는 데이터 유효성 검사를 사용하여 사용자가 잘못된 제품 코드를 입력하지 못하도록하는 방법을 알려줍니다.

1. 범위 A2 : A7을 선택합니다.

2. 데이터 탭의 데이터 도구 그룹에서 데이터 유효성 검사를 클릭합니다.

3. 허용 목록에서 사용자 지정을 클릭합니다.

4. 수식 상자에 아래 표시된 수식을 입력하고 확인을 클릭합니다.

=AND(LEFT(A2)="C",LEN(A2)=4,ISNUMBER(VALUE(RIGHT(A2,3))))

설명 : 이 AND 함수에는 세 개의 인수가 있습니다.

 LEFT (A2) = "C"는 사용자가 문자 C로 시작하도록합니다.

 LEN (A2) = 4는 사용자가 4 자 길이의 문자열을 입력하도록합니다.

 ISNUMBER (VALUE (RIGHT (A2,3)))은 사용자가 3 개의 숫자로 끝나도록합니다.

 RIGHT (A2,3)는 텍스트 문자열에서 가장 오른쪽에있는 3 개의 문자를 추출합니다.

 VALUE 함수는이 텍스트 문자열을 숫자로 변환합니다.

 ISNUMBER는이 값이 숫자인지 확인합니다.

 AND 함수는 모든 조건이 참이면 TRUE를 반환합니다.

 데이터 유효성 검사를 클릭하기 전에 A2 : A7 범위를 선택했기 때문에 Excel은 수식을 다른 셀에 자동으로 복사합니다.

5.이를 확인하려면 A3 셀을 선택하고 데이터 유효성 검사를 클릭합니다

보시다시피 셀에는 올바른 수식도 포함되어 있습니다.

6. 잘못된 제품 코드를 입력하십시오.

결과. Excel에 오류 경고가 표시됩니다.

참고 : 입력 메시지 및 오류 경고 메시지를 입력하려면 입력 메시지 및 오류 경고 탭으로 이동합니다.

판매액범위내에서 판촉비용 최대 찾기

(질의) 위의 표를 "조건부서식"에서 색상으로 표현하려 합니다.

"판촉비용" 칼럼의 색상을 표현하기 위한 조건을 질문드립니다.

조건은, "판매액"이 18,000,000~20,000,000 범위내에서 "판촉비용"이 가장 많은 곳에 색상을 표현하는 것입니다.

1. DMAX함수로 조건에 맞는 판촉비용을 찾는다.

=DMAX($C$4:$D$15,"판촉비용",$C$23:$D$24)

조건이 같은 행에 있으면 AND, 다른 행에 있으면 OR

2. 조건부 서식에서 E24셀의 값과 같은 경우 판촉비용에 셀 배경색 채우기를 한다.

여기서 조금 문제점이 있는데 판촉비용이 9,900,000원이 또 다른셀에 있으면 같이 표현된다.

네이버 지식인에서 어떤분이 물어온 질문에 대한 답변을 다운 받았다.

kin.naver.com/qna/detail.nhn?d1id=1&dirId=102020101&docId=367281215&mode=answer

답변주신분의 파일을 공부겸 글로 남긴다.

=INDEX($B$1:$B$26,ROUND(1/MOD(LARGE(INDEX($D$3:$D$26+1/ROW($D$3:$D$26),),ROW(A1)),1),))

함수를 하나 하나 뜯어보자.

1. INDEX($D$3:$D$26+1/ROW($D$3:$D$26),) 

방문자 숫자에 대한 배열로 만드는데 방문자 숫자가 같은 경우가 있으니 1/ROW($D$3:$D$26)로 배열값에 차이를 줄 수 있도록 했다.

{0.333333333333333;1.25;2.2;3.16666666666667;0.142857142857143;0.125;5.11111111111111;1.1;5.09090909090909;0.0833333333333333;7.07692307692308;8.07142857142857;9.06666666666667;10.0625;1.05882352941176;4.05555555555556;8.05263157894737;5.05;5.04761904761905;1.04545454545455;1.04347826086957;5.04166666666667;5.04;2.03846153846154}

2. LARGE(INDEX($D$3:$D$26+1/ROW($D$3:$D$26),),ROW(A1))

위에서 정의한 배열을 숫자가 ROW(A1) 번째 큰 숫자를 찾는다

{10.0625}

3. MOD({10.0625},1)

1로 나눈 나머지를 찾는다.

{0.0625}

4. ROUND(1/{0.0625},)

여기서 0.0625는 1/ROW(D3)과 같다. 따라서 역수를 취하면 ROW(D16)이다.

5. =INDEX($B$1:$B$26,{16})

시간배열 중에서 16행에 있는 값을 반환한다.

중복 항목 입력 방지

이 예에서는 사용자가 중복 값을 입력하지 못하도록 데이터 유효성 검사를 사용하는 방법을 설명합니다.

1. 범위 A2 : A20을 선택합니다.

2. 데이터 탭의 데이터 도구 그룹에서 데이터 유효성 검사를 클릭합니다.(Alt + A, V, V)

3. 허용 목록에서 사용자 지정을 클릭합니다.

4. 수식 상자에 아래 표시된 수식을 입력하고 확인을 클릭합니다.

=COUNTIF($A$2:$A$20,A2)=1

설명 : COUNTIF 함수는 두 개의 인수를 사용합니다.

=COUNTIF($A$2:$A$20, A2)는 A2:A20 범위에서 A2 셀의 값과 같은 값의 수를 계산합니다.

이 값은 중복 항목을 원하지 않으므로 한 번만 (= 1) 나타날 수 있습니다.

데이터 유효성 검사를 클릭하기 전에 A2 : A20 범위를 선택했기 때문에 Excel은 수식을 다른 셀에 자동으로 복사합니다.

이 참조를 수정하기 위해 절대 참조 ($A$2 : $A$20)를 생성 한 방법에 주목하십시오.

5.이를 확인하려면 A3 셀을 선택하고 데이터 유효성 검사를 클릭합니다.

보시다시피 함수는 A3 셀의 값과 동일한 A2:A20 범위의 값 수를 계산합니다.

다시 말하지만,이 값은 중복 항목을 원하지 않으므로 한 번만 (= 1) 발생할 수 있습니다.

6. 중복 송장 번호를 입력합니다.

결과. Excel에 오류 경고가 표시됩니다. 이미 입력 한 송장 번호입니다.

참고 : 입력 메시지 및 오류 경고 메시지를 입력하려면 입력 메시지 및 오류 경고 탭으로 이동합니다.

Q. A1셀에 다음 주소가 입력되어 있습니다.

주소 (서울특별시 종로구 청와대로) 1번지

여기서 ( ) 밖의 1번지를 추출하고 싶습니다.

다음 순서로 찾으시면 됩니다.

1. LEN(A1)으로 전체 문자열 수를 구한다.

2. FIND( )함수를 이용해서 ")" 문자열의 위치를 찾는다.

3. MID( ) 함수를 이용해서 찾고자 하는 문자열을 찾는다.

예산한도 설정

이 예에서는 데이터 유효성 검사를 사용하여 사용자가 예산 한도를 초과하지 않도록하는 방법을 알려줍니다.

1. B2 : B8 범위를 선택합니다.

참고 : 셀 B10에는 범위 B2 : B8의 합계를 계산하는 SUM 함수가 포함되어 있습니다.

2. 데이터 탭의 데이터 도구 그룹에서 데이터 유효성 검사를 클릭합니다.(Alt + A, V, V)

3. 허용 목록에서 사용자 지정을 클릭합니다.

4. 수식 상자에 아래 표시된 수식을 입력하고 확인을 클릭합니다.

=SUM($B$2:$B$8)<=100000

설명 : B2:B8 범위의 합계는 예산 한도인 \100,000을 초과 할 수 없습니다.

      따라서 값이 입력되는 B2:B8 (셀 B10이 아님!) 범위에 데이터 유효성 검사를 적용합니다.

      데이터 유효성 검사를 클릭하기 전에 B2:B8 범위를 선택했기 때문에 Excel은 수식을 다른 셀에 자동으로 복사합니다.

이 참조를 수정하기 위해 절대 참조 ($B$2:$B$8)를 생성 한 방법에 주목하십시오.

5.이를 확인하려면 B3 셀을 선택하고 데이터 유효성 검사를 클릭합니다.

보시다시피 셀에는 올바른 수식도 포함되어 있습니다.

6. B7 셀에 값 30을 입력합니다.

결과. Excel에 오류 경고가 표시됩니다. 예산 한도 인 \100,000을 초과 할 수 없습니다.

참고 : 오류 경고 메시지를 입력하려면 오류 경고 탭으로 이동하십시오.

화면에 보이는 셀만 복사

기본적으로 Excel은 보이는 셀과 숨겨진 셀을 모두 복사합니다. 그러나 보이는 셀만 복사 할 수 있습니다.

이 예에서는 행 2가 숨겨져 있습니다.

이 범위를 복사 / 붙여 넣기하면 다음과 같은 결과가 나타납니다.

보이는 셀만 복사하기

1. 범위 A1 : A4를 선택합니다.

2. 홈 탭의 편집 그룹에서 찾기 및 선택을 클릭합니다. (Alt + H, FD, S)

3. 이동 옵션(S)를 클릭한다.

4. 화면에 보이는 셀만(Y)를 클릭하고, 확인을 클릭합니다.

Excel은 보이는 셀을 선택합니다.

5. Ctrl + c를 눌러 범위를 복사합니다.

6. A6 셀을 선택하고 Ctrl + v를 눌러 범위를 붙여 넣으십시오.

결과:

유효하지 않은 날짜 거부

이 예에서는 데이터 유효성 검사를 사용하여 잘못된 날짜를 거부하는 방법을 설명합니다.

1. 범위 A2 : A4를 선택합니다.

2. 데이터 탭의 데이터 도구 그룹에서 데이터 유효성 검사를 클릭합니다. (Alt + A, V, V)

날짜 영역 이외

3. 허용 목록에서 날짜를 클릭합니다.

4. 데이터 목록에서 사이를 클릭합니다.

5. 아래 표시된 시작 날짜와 종료 날짜를 입력하고 확인을 클릭합니다.

설명 : 2020년 9월 6일부터 오늘날짜 + 5 일 사이의 모든 날짜가 허용됩니다. 이 날짜 범위를 벗어나는 모든 날짜는 거부됩니다.

6. A2 셀에 2020년 9월 6일 날짜를 입력합니다.

   A3 셀에 2020년 9월 20일 날자를 입력합니다.(오늘은 2020년 9월 6일 입니다.)

결과. Excel에 오류 경고가 표시됩니다.

참고 : 입력 메시지 및 오류 경고 메시지를 입력하려면 입력 메시지 및 오류 경고 탭으로 이동합니다.

일요일과 토요일

3. 허용 목록에서 사용자 지정을 클릭합니다.

4. 수식 상자에 아래 표시된 수식을 입력하고 확인을 클릭합니다.

=AND(WEEKDAY(A2)<>1,WEEKDAY(A2)<>7)

설명 : WEEKDAY 함수는 날짜의 요일을 나타내는 1 (일요일)에서 7 (토요일) 사이의 숫자를 리턴합니다. 날짜의 요일이 1 (일요일)이 아니고 7 (토요일)이 아닌 경우 날짜가 허용됩니다 (<>는 같지 않음을 의미).

즉, 월요일, 화요일, 수요일, 목요일 및 금요일이 허용됩니다.

일요일과 토요일은 거부됩니다.

데이터 유효성 검사를 클릭하기 전에 A2 : A4 범위를 선택했기 때문에 Excel은 수식을 다른 셀에 자동으로 복사합니다.

5. 이를 확인하려면 A3 셀을 선택하고 데이터 유효성 검사를 클릭합니다.

보시다시피 셀에는 올바른 수식도 포함되어 있습니다.

6. A2 셀에 2020 년 9 월 12일 (토) 날짜를 입력합니다.

결과. Excel에 오류 경고가 표시됩니다.

참고 : 입력 메시지 및 오류 경고 메시지를 입력하려면 입력 메시지 및 오류 경고 탭으로 이동합니다.

동일한 행에서 값이 다른 셀

이 예제에서는 각 행의 비교 셀과 내용이 다른 셀을 빠르게 강조 표시하는 방법을 설명합니다.

1. A1 : C15 범위를 선택하려면 A1 셀을 클릭하고 C15 셀로 끕니다.

여기서 왼쪽 셀값을 바로 입력 할때는 Ctrl + R을 이용하면 편리합니다.

참고 : A1 셀을 먼저 클릭하여 A1 : C15 범위를 선택 했으므로 A1 셀이 활성 셀입니다 (활성 셀을 변경하려면 ENTER 및 TAB 사용). 결과적으로 비교 셀은 A 열에 있습니다.

ENTER을 누르면 다음행으로 넘어갑니다. TAB는 다음 열로 넘어갑니다.

2. 홈 탭의 편집 그룹에서 찾기 및 선택을 클릭합니다.(Alt + H, FD, S)

3. 이동 옵션(S) 클릭

4. 동일 행에서 값이 다른 셀(W) 클릭

행 2의 경우 523이 셀 A2의 값 (522)과 같지 않기 때문에 Excel에서 B2 셀을 선택합니다.

행 4의 경우 723가 셀 B4 (725)와 C4(725)의 값과 같지 않기 때문에 Excel에서 A4을 선택합니다.

5. 홈 탭의 글꼴 그룹에서 선택한 셀의 배경색을 변경합니다.(Alt + H, H)

결과: