실용적인 엑셀 기술 공유: 효율적인 검색 스타일 드롭다운 메뉴 만들기

이전 글 "실용적인 엑셀 팁 공유: 동적 간트 차트 만들기"에서는 엑셀에서 동적 간트 차트를 만드는 방법을 배웠습니다. 오늘은 엑셀 데이터 드롭다운 메뉴에 대해 알아보고 효율적인 검색형 드롭다운 메뉴 만드는 방법을 소개하겠습니다.

직장에서는 데이터 입력을 표준화하고 데이터 입력 시간을 절약하기 위해 Excel 데이터 확인을 사용하여 드롭다운 메뉴를 만드는 경우가 많습니다. 하지만 드롭다운 메뉴에 데이터 옵션이 많으면 데이터를 찾기가 어려울 수 있습니다. 예를 들어, 아래 그림에는 드롭다운 메뉴에 데이터 옵션이 너무 많습니다. 옆에 있는 스크롤 막대를 드래그하여 필요한 데이터 항목을 "찾는" 데 시간이 많이 걸리므로 작업 효율성이 직접적으로 떨어집니다.

그러면 옵션이 너무 많고 데이터를 찾기 어려운 문제를 해결할 방법이 있을까요?

네, 제 방법은 검색 드롭다운 메뉴에요!

바이두 검색과 마찬가지로 키워드를 입력하면 드롭다운 메뉴가 나타나 선택할 키워드가 포함된 검색 질문이 표시됩니다. 우리가 얻고자 하는 효과는 셀에 키워드를 입력한 후 드롭다운 메뉴를 클릭하면 해당 키워드가 포함된 데이터만 메뉴에 표시되어 데이터 입력 효율성이 향상되는 것입니다.

아래 사진은 저희 튜토리얼의 데이터 소스입니다. 데이터 소스는 키워드별로 오름차순 또는 내림차순으로 정렬되어야 합니다.

E2:E6 셀 범위를 선택하고 [데이터] 탭을 클릭한 후 [데이터 유효성 검사]를 클릭하고 팝업되는 "데이터 유효성 검사"의 "설정" 탭에서 유효성 검사 조건을 "순서"로 설정합니다. 대화 상자.

소스에 수식을 입력하세요:

=OFFSET($A$1,MATCH(E2&"*",$A$2:$A$17,0),0,COUNTIF($A$2: $A$17,E2&"*"),1)=OFFSET($A,MATCH(E2&"*",$A:$A,0),0,COUNTIF($A:$A,E2&"*"),1)

公式说明：

这里主要使用OFFSET函数返回包含关键字的数据。OFFSET函数的作用是根据指定的参照单元格，通过给定的偏移量返回新的引用数据。

OFFSET（参照系，行偏移量，列偏移量，新引用区域的行数，新引用区域的列数）

• 공식 설명:
  여기서는 주로 OFFSET 함수를 사용하여 키워드가 포함된 데이터를 반환합니다. OFFSET 함수의 기능은 지정된 참조 셀을 기준으로 주어진 오프셋을 통해 새로운 참조 데이터를 반환하는 것입니다.
• OFFSET(참조 시스템, 행 오프셋, 열 오프셋, 새 참조 영역의 행 수, 새 참조 영역의 열 수)

• 1 첫 번째 매개변수는 A1을 참조합니다. 셀은 참조 프레임 역할을 합니다.

• 2. 두 번째 매개 변수는 MATCH(E2&"*",$A$2:$A$17,0)을 사용하여 행 오프셋을 결정합니다. MATCH는 검색 값 E2&"*"를 기준으로 $A$2:$A$17 영역에서 검색합니다. (*는 불확실한 문자를 나타내는 와일드카드 문자입니다.) 검색 모드는 0(정확한 검색)입니다. 셀 E2에 키워드를 입력하면 이 함수는 $A$2:$A$17 범위에서 해당 키워드가 포함된 데이터가 처음으로 나타나는 것을 찾습니다.

• 3 세 번째 매개변수는 0입니다. 데이터 소스에 열 A만 있으므로 열 오프셋은 0입니다. 이는 오프셋이 없음을 의미합니다. 간단히 말해서 오프셋 함수는 셀 A1을 참조로 사용하고 가로로 오프셋하지 않고 아래로 오프셋만 합니다.

4. 네 번째 매개변수 COUNTIF($A$2:$A$17,E2&"*")는 A2-A17 영역에서 E2&"*" 조건을 충족하는 셀, 즉 E2 셀의 키워드 발생 횟수, 즉 데이터 유효성 검사 드롭다운 메뉴에 표시되는 행 수입니다.

5. 다섯 번째 매개변수는 새 참조 영역의 열 수입니다. A열만 있으므로 1입니다.

아래와 같이 오프셋 함수는 A1을 참조 시스템으로 사용하여 아래쪽으로 검색하고 일치 함수를 사용하여 A2-A17에서 E2 키워드 "broken flowers"가 포함된 데이터를 찾습니다. .10개의 행을 찾은 다음 countif 함수를 사용하여 총 3개의 행을 찾고 마지막으로 이 3개의 행과 1개의 열의 데이터를 드롭다운 메뉴에 반환합니다.

🎜튜토리얼로 돌아갑니다. "데이터 유효성 검사" 대화 상자의 "소스"에 수식을 입력하고 "확인"을 클릭합니다. 그런데 E2 셀에 "깨진 꽃"이라는 키워드를 입력하면 즉시 경고 상자가 나타납니다. 왜 그럴까요? 🎜🎜🎜🎜🎜이유는 "broken flower"라는 키워드를 입력한 후 수식으로 얻은 드롭다운 메뉴에 "broken flower"라는 단어만 포함된 옵션이 없으므로 오류가 보고되기 때문입니다. 🎜

아래 그림과 같이 E2:E6 셀 범위를 다시 선택하고 [데이터] 탭 [데이터 검증] 버튼을 클릭하여 "데이터 검증" 대화 상자에 들어간 다음, "입력" 버튼의 선택을 취소해야 합니다. "오류 경고" 탭 잘못된 데이터일 때 오류 경고 표시] 옵션을 선택한 후 "확인"을 클릭합니다.

마지막으로 F2 셀에 =IFERROR(VLOOKUP(E2,$A$2:$B$17,2,0),"") 수식을 입력합니다. VLOOKUP 함수를 사용하여 셀 범위 A2-B17에서 E2 값의 위치를 ​​찾고 해당 인벤토리를 열 2에 반환합니다(즉, 열 B는 정확한 검색을 나타냅니다). 오류 값을 찾을 수 없고 오류 값이 반환되면 IFERROR 함수를 사용하여 오류 값을 null로 변환합니다.

이제 검색 드롭다운 메뉴가 완성되었습니다!

검색 스타일 드롭다운 메뉴는 특히 드롭다운 메뉴 옵션이 많은 경우 데이터 입력 효율성을 기하급수적으로 향상시킬 수 있습니다. 동급생 여러분, 빠르게 Excel을 열고 몇 가지 작업을 수행해 보세요.

관련 학습 권장 사항: excel 튜토리얼

위 내용은 실용적인 엑셀 기술 공유: 효율적인 검색 스타일 드롭다운 메뉴 만들기의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!