☰

Excel 2013 - 데이터 유효성 검사 - 두 번째 기준이 일치하는 경우 값이 포함된 드롭다운 목록 만들기

tag-icon • tag-icon microsoft-excel vba
Excel 2013 - 데이터 유효성 검사 - 두 번째 기준이 일치하는 경우 값이 포함된 드롭다운 목록 만들기

문제를 최대한 단순화하겠습니다. Excel에 두 개의 시트가 있습니다. 첫 번째 시트에서는 위치 목록을 만듭니다. B열에 위치를 입력하기 전에 해당 위치가 A열에 있는 지역을 선택해야 합니다. 다음 열(C)에는 자동으로 생성되는 ID가 있습니다(첫 번째 위치 ID-001의 경우, 두 번째는 ID 002 등을 얻습니다.)

두 번째 시트에서는 자산 목록을 만듭니다. 따라서 다시 A열에서 영역을 선택하고 B열에 자산 이름을 입력합니다. 이제 C열에 해당 영역이 있는 첫 번째 테이블의 ID만 표시하는 드롭다운 목록을 갖고 싶습니다. 똑같다. OFFSET 기능을 사용하려고 시도했지만 누락된 점은 ID 목록을 동일한 영역을 가진 항목으로 제한하기 위한 일종의 "select if"(countif 또는 sumif와 같은)입니다.

각 영역에는 많은 위치와 자산이 있을 수 있지만 모든 위치와 모든 자산은 하나의 위치에만 매핑될 수 있습니다.

여러분의 답변을 기대합니다! Excel 솔루션이나 VBA 솔루션이 훌륭할 것입니다.

답변1

VLOOKUP에서 사용할 고유 값을 포함하는 도우미 열을 만드는 것이 좋습니다. 예를 들어; Area1, Area2, Area3 및 Area1이 (다시) 있다고 가정하고 계속 진행합니다. 이 값을 증분 값(예: 01, 02, 03, ...)과 병합하고 01Area1, 01Area2, 01Area3 및02지역1. 이제 두 번째 시트의 각 목록 항목에 대해 무엇을 호출해야 하는지 알았습니다. 선택하면지역1A 열에서 검색이 필요합니다.증분 숫자&지역1. 값에 따른 증분 숫자는 다음과 같이 생성할 수 있습니다.카운티확장 범위를 사용하는 함수:

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

범위를 확장하면 수식은 해당 행까지의 값만 계산합니다. 이 방법의 유일한 단점은 목록 항목을 생성하는 데 필요합니다.다른 곳에서

유사한 사용법을 설명하는 두 개의 링크는 다음과 같습니다.

  1. https://www.spreadsheetweb.com/how-to-get-nth-match-with-vlookup/
  2. https://www.spreadsheetweb.com/pricing-list-quoting-tool-part-1/

답변2

Locations 테이블을 정렬하는 것이 허용된다고 가정하면 가장 간단한 솔루션은 상대적으로 짧은 데이터 유효성 검사 수식과 몇 가지 명명된 범위를 수반합니다. 도우미 열이나 행은 필요하지 않습니다.

다음과 같이 두 개의 예제 워크시트 Locations및 를 설정합니다 .Assets

위치 워크시트 스크린샷  자산 워크시트 스크린샷

두 개의 정의된 이름을 추가합니다.

  1. Areas→=Locations!$A:$A
  2. IDs→=Locations!$C:$C

마지막으로 다음 수식을 사용하여 워크시트 C2에 셀 내 드롭다운 목록 데이터 유효성 검사를 추가한 다음 셀을 아래쪽으로 채우거나 복사하여 붙여넣습니다.Assets

=INDEX(IDs,MATCH(A2,Areas,0)):INDEX(IDs,MATCH(A2,Areas,0)+COUNTIF(Areas,A2)-1)

이 방법의 단점은 다음과 같습니다.

  • 정의된 이름을 사용해야 합니다.
  • 위치 테이블을 정렬해야 합니다.
  • 위치 시트의 다른 테이블은 Area위치 테이블의 값과 일치하는 값을 포함하지 않는다는 것이 보장되지 않는 한 열과 안전하게 교차할 수 없습니다 Area.

다음 대체 솔루션은 다른 테이블을 제외한 단점을 극복합니다. 그러나 자산 워크시트의 셀을 사용하여 드롭다운 목록의 항목을 저장합니다.

자산 워크시트는 다음과 같은 추가 열로 설정됩니다.

자산 워크시트 스크린샷

이번에는 C2데이터 검증 공식은 다음과 같습니다.

=IF(SUMPRODUCT(--ISNA(C2:INDEX(2:2,COLUMN(C2)-1+COUNTA(C2:INDEX(2:2,COLUMNS(2:2))))))=0,$I$1,C2:INDEX(2:2,COLUMN(C2)-1+COUNTA(C2:INDEX(2:2,COLUMNS(2:2)))-SUMPRODUCT(--ISNA(C2:INDEX(2:2,COLUMN(C2)-1+COUNTA(C2:INDEX(2:2,COLUMNS(2:2))))))))

이 수식을 입력 D2하고 작성하십시오.

=
IF(
  SUMPRODUCT(
    --ISNA(
      E2:INDEX(
        (2:2),
        COLUMN(E2)-1+MAX(1,COUNTA(E2:INDEX(2:2,COLUMNS(2:2))))
      )
    )
  )=0,
  "ERROR",
  "ok"
)

다중 셀 배열 - 한 영역에서 예상되는 최대 위치 수에서 시작하여 가장 오른쪽으로 가는 셀에 이 마지막 공식을 입력합니다 E2(예를 들어 내가 사용함 E2:I2).

=
INDEX(
  Locations!$C:$C,
  IFERROR(
    SMALL(
      IFERROR(1/(1/(
        (Locations!$A$2:INDEX(Locations!$A:$A,COUNTA(Locations!$A:$A))=IF($A2="",NA(),$A2))
        *ROW($A$2:INDEX($A:$A,COUNTA(Locations!$A:$A)))
      )),FALSE ),
      COLUMN(INDEX(2:2,1):INDEX(2:2,MAX(2,COUNTIF(Locations!$A$2:INDEX(Locations!$A:$A,COUNTA(Locations!$A:$A)),$A2))))
    ),
    NA()
  )
)

관련 정보