시트 1에 ID 및 설명 목록이 있습니다. 이 데이터는 사용자가 상호 작용할 수 있는 두 번째 시트(시트 2)에서 사용됩니다.
시트 1의 목록에는 반복되는 ID가 있지만 설명은 다릅니다. 시트 2의 데이터 유효성 검사를 사용하여 ID 번호 4의 모든 반복을 1로 결합하고 열 A에서 선택한 후 사용자가 원하는 설명(드롭다운 상자 사용)을 선택할 수 있도록 하려면 어떻게 해야 합니까? ?
이를 위해 매크로를 사용하지 않고 내장된 기능만 사용하고 싶습니다.
도움을 주시면 감사하겠습니다!
답변1
Sheet1에서 반복을 결합합니다. 조회 수식은 처음 찾은 값만 반환하므로 동일한 ID가 여러 개 있는 경우 대신 고유 ID를 사용하여 조회를 만들어야 합니다. 원본 테이블을 변경하고 싶지 않거나 변경할 수 없는 경우 도우미 테이블에서 이를 준비하고 데이터 유효성 검사를 위해 도우미 테이블을 사용하세요.
식별자(이유로 인해 그렇게 부름)가 고유하지 않다는 것은 오해의 소지가 있습니다.
답변2
사용 중인 Excel 버전을 언급하지 않았습니다. 귀하의 질문에 대한 이해를 바탕으로 가능한 해결책을 제안합니다. 그러나 Excel 2007 이전에는 사용할 수 없었던 IFERROR & COUNTBLANK라는 함수도 사용합니다. 따라서 Excel 2003을 사용하는 경우 다른 접근 방식이 필요할 수 있습니다.
이는 도우미 열과 도우미 시트를 사용하므로 이상적인 솔루션이 아닐 수도 있습니다!
이 예에서 샘플 데이터는 이 스크린샷에 표시된 대로 Sheet1!A2:B10에 있습니다.
먼저 ID에서 고유한 목록을 가져오려면 D열에 도우미 열을 만들어야 합니다.
D2에 다음 수식을 입력한 다음 수식 표시줄 내에서 CTRL+ SHIFT+를 눌러 ENTER배열 수식을 만듭니다. 또한 이것이 올바르게 작동하려면 행(가급적 첫 번째 행)을 헤더로 남겨 두어야 합니다.
수식은 배열 수식임을 나타내기 위해 중괄호로 묶어야 합니다.
마스터 열의 예상 고유 값 수를 기준으로 원하는 행까지 아래로 드래그하세요. 결국에는 맨 아래에 공백이 반환되기 시작하고 중지할 수 있습니다. 그러면 고유한 ID 마스터 목록이 생성됩니다.
=IFERROR(INDEX($A$2:$A$10, MATCH(0,COUNTIF($D$1:D1, $A$2:$A$10), 0)),"")
이제 검증 목록을 채우기 위해 이 목록의 정확한 시작과 끝을 알아야 합니다.
이제 G3에 다음 공식을 입력하세요.
=INDEX(Sheet1!D2:D10,1)
G4에 다음 배열 수식을 입력합니다.
=MIN(IF(Sheet1!D2:D10="",ROW(Sheet1!D2:D10),9^99))-2
CTRL+ SHIFT+를 잊지 마세요 ENTER.
이제 이름 관리자로 이동하여 MyList라는 새 이름을 만듭니다.
그 안에 다음 공식을 넣어보세요
=INDEX(Sheet1!$D$2:$D$10,Sheet1!$G$3):INDEX(Sheet1!$D$2:$D$10,Sheet1!$G$4)
여기서는 INDEX를 사용하여 값 대신 셀 참조를 반환합니다.
Sheet2라는 새 시트를 삽입합니다.
이제 Col A2 이하에서 유효성 검사 목록을 만들고 =MyList거기에 넣습니다.
이제 모든 고유 값은 ID의 첫 번째 드롭다운 목록에 표시됩니다.
이것으로 1부를 마치겠습니다.
이제 다음 작업은 설명 열에서 추출된 데이터를 기반으로 두 번째 유효성 검사 목록을 채우는 것입니다.
Sheet3이라는 세 번째 도우미 시트를 삽입합니다. 이 시트를 간단히 숨길 수 있습니다.
이 예에서는 Sheet3!B2:H10에서 데이터를 가져왔습니다. 마스터 목록에서 ID의 최대 중복 수를 기준으로 열 수를 결정합니다. 또한 이것이 올바르게 작동하려면 첫 번째 열 A를 그대로 두십시오.
B2에 다음 수식을 입력하고 수식 표시줄 내에서 CTRL+ SHIFT+를 눌러 ENTER수식을 만들고 배열합니다.
의도한 대로 가로 및 아래로 드래그하세요.
=IFERROR(INDEX(Sheet1!$B$2:$B$10, SMALL(IF(Sheet2!$A2=Sheet1!$A$2:$A$10,ROW(Sheet1!$A$2:$A$10)-1,999),COLUMN()-COLUMN($A$2))),"")
이름 관리자로 돌아가서 Trimmed라는 새 이름을 만들고 그 안에 다음 수식을 입력합니다.
=OFFSET(Sheet3!$B1,0,0):OFFSET(Sheet3!$B1,0,COUNTA(Sheet3!$B1:$H1)-COUNTBLANK(Sheet3!$B1:$H1)-1)
목록이 적절하게 잘려지도록 공백을 제외한 목록이 생성됩니다.
이제 Cell B2 이하의 Sheet2에서 유효성 검사 목록을 만들고 =Trimmed거기에 넣습니다.
이제 Sheet1!A2:A10에서 데이터가 변경되면 고유 값 목록이 MyList에 자동으로 반영되고 Trimmed라는 이름은 Sheet1 설명 열에서 의도한 설명만 가져옵니다.
전체 작업이 어떻게 작동하는지 알아보려면 아래의 GIF 화면을 참조하세요.
