쉼표로 구분된 숫자 집합이 있습니다. 각 세트는 프로젝트와 관련된 일련의 고유 번호를 나타냅니다. 목록에 있는 항목 수에 관계없이 참조 시트를 검색하고 하드 코딩된 값을 반환하는 수식을 어떻게 작성합니까?
각 시트에 대해 아래에 (식별되지 않은) 예시를 포함하겠습니다(표 1-5) 및 의도/예상 출력. SUM, FREQUENCY, INDEX 또는 다른 배열 수식과 관련이 있다고 생각하지만 알 수 없습니다. 내 질문을 볼 수 있습니다여기또한, 그러나 그것은 거의 동일한 정보를 가지고 있습니다.
나는 "를 사용했습니다.피엉덩이" "에프어이" 그리고 "중issing"이지만 무엇이든 작동합니다.
논리:
수식은 다음 단계를 수행해야 합니다.
- C열의 각 셀에서 숫자 목록을 추출합니다.표 4.
- 찾다표 3(D 열) 목록에 있는 숫자의 인스턴스입니다.
2b. 목록에 숫자가 나타나면 감사가 실패했는지 확인하십시오(감사의 B열에 "실패"라는 텍스트로 표시됨).표 3).
2c. 있다면 인쇄하세요.에프.
2d. 그렇지 않은 경우 인쇄하십시오.피. - 목록에 숫자가 하나도 나타나지 않으면 인쇄하세요.중.
테이블:
1 번 테이블
이 표는 주요 참고 자료입니다. 이름, 서버 및 프로젝트에 대한 모든 UID를 나열합니다.
Unique ID Name Server Address Project
10000 Company 1 Server 1 Project 1
10001 Company 2 Server 2 Project 2
10002 Company 3 Server 3 Project 3
10003 Company 3 Server 3 Project 4
10004 Company 3 Server 3 Project 5
10005 Company 3 Server 3 Project 6
10006 Company 4 Server 4 Project 7
10007 Company 4 Server 4 Project 7
10008 Company 4 Server 4 Project 7
10009 Company 5 Server 5 Project 8
10010 Company 6 Server 6 Project 9
10011 Company 7 Server 7 Project 10
10012 Company 8 Server 8 Project 11
10013 Company 8 Server 8 Project 11
10014 Company 8 Server 8 Project 11
10015 Company 8 Server 8 Project 11
10016 Company 8 Server 8 Project 11
10017 Company 8 Server 8 Project 11
10018 Company 8 Server 8 Project 11
10019 Company 8 Server 8 Project 11
10020 Company 8 Server 8 Project 11
10021 Company 9 Server 9 Project 12
10022 Company 10 Server 10 Project 13
10023 Company 11 Server 10 Project 14
10024 Company 12 Server 10 Project 15
10025 Company 13 Server 10 Project 16
10026 Company 14 Server 10 Project 17
10027 Company 15 Server 10 Project 18
10028 Company 16 Server 10 Project 19
10029 Company 17 Server 10 Project 20
10030 Company 18 Server 10 Project 21
10032 Company 19 Server 10 Project 23
10033 Company 19 Server 10 Project 24
표 2
이 테이블에는 다음과 같은 정보가 모두 포함되어 있습니다.1 번 테이블그러나 각 UID를 해당 프로젝트로 그룹화합니다. 현재는 실제로 사용하지 않지만 도움이 될 수 있을까요? 없으면 삭제하겠습니다.
Company Trading Name Project UID UID UID UID
Company 1 Project 1 10000
Company 2 Project 2 10001
-----------------------------------------------------
Company 4 Project 7 10006 10007 10008
표 3
이 테이블에는 감사 결과가 붙여넣어지는 곳입니다. 감사가 통과되지 않은 경우 실패라는 단어가 있는 메모 열(현재 조건부 서식이 이를 강조 표시함)과 간단한 LEFT 함수로 UID를 가져오는 등 이 레이아웃을 정확하게 따릅니다. 그런 다음 이름 및 회사 열은 UID에 대해 VLOOKUP을 사용합니다.1 번 테이블이름/서버를 확보합니다.
Notes Time Rec. UID Name Server
Audit for company passed:10001 auto 10001 Company 2 Server 2
Audit for company passed:10006 auto 10006 Company 4 Server 4
Audit for company failed:10007 auto 10007 Company 4 Server 4
표 4
감사보고서입니다. 수식은 D 열에 들어가고 출력은 매일 값으로 복사/붙여넣어집니다. 나중에 어떻게 보일지에 대한 몇 가지 예를 여기에 채웠습니다.
Server Name Project (UID) Today's Date
Server 1 Company 1 Project 1 (10000) P
Server 2 Company 2 Project 2 (10001) N
Server 3 Company 3 Project 3 (10002,10003,10004,10005) F
Server 4 Company 4 Project 4 (10006,10007,10008) P
-----------------------------------------------------
Server 10 Company 10 Project 13 (10022) P
Server 10 Company 11 Project 14 (10023) M
Server 10 Company 12 Project 15 (10024) P
Server 10 Company 13 Project 16 (10025) P
Server 10 Company 15 Project 18 (10027) F
Server 10 Company 16 Project 19 (10028) P
Server 10 Company 17 Project 20 (10029) M
Server 10 Company 18 Project 21 (10030) P
Server 10 Company 19 Project 22 (100310) P
Server 10 Company 19 Project 23 (10032) P
Server 10 Company 19 Project 24 (10033) M
또는 다섯 번째 테이블이 있습니다.표 5, 누락된 ID를 자동으로 조회합니다.표 3다음 공식을 사용합니다.
=INDEX(Reference!$A$2:$A$160, SMALL(IF(ISERROR(MATCH(Reference!$A$2:$A$160, Check!$D$2:$D$350, 0)), (ROW(Reference!$A$2:$A$160)-MIN(ROW(Reference!$A$2:$A$160))+1), ""), ROWS($A$1:A1)))
언제표 3비어 있으면(매일 지워짐) 단순히 모든 프로젝트를 나열합니다. 임의 개수의 감사를 붙여넣은 경우표 3그러나 목록에는표 5누락된 항목을 표시하도록 업데이트됩니다. 아마도 이것을 최종 공식에 통합하는 방법이 있을 수 있습니다.
누구든지 작업하기를 원하는 경우 비식별 버전이 있습니다.
감사합니다.
edit1: 누군가가 추가 시스템 세부정보를 요청했습니다. Excel 365를 사용하는 Windows 10 환경입니다.
답변1
나는 확장성이 별로 없고 몇 가지 도우미 열을 사용하는 접근 방식을 조합하려고 노력했습니다. 쉼표로 구분된 값을 한 번에 조회할 수 있는 직접적인 방법이 있는지 잘 모르겠습니다. 가능할 수도 있지만 저는 Excel 전문가가 아닙니다.
게다가 Office 365 Desktop 버전은 VBA 매크로를 지원한다고 생각합니다. 데스크톱 버전을 사용하는 경우 더욱 간소화된 VBA 매크로 솔루션을 선택할 수 있습니다. 하지만 온라인 버전에서는 매크로가 지원되지 않는 것 같습니다.
아래 스냅샷을 참조하세요.
5개의 도우미 열이 있습니다. H,M,N,O,P. H는 선택 사항일 수 있지만 서버 이름의 마지막 문자가 서버 1, 서버 2와 같은 순서로 표시되지 않을 수 있으므로 간단한 숫자 시퀀스를 사용하는 것을 선호합니다.
M4에 다음 수식을 입력하고 아래로 드래그하세요.
=SUBSTITUTE(RIGHT(K4,LEN(K4)-FIND("(",K4)),")","")
그러면 쉼표로 구분된 적절한 UID 목록이 생성됩니다.
N4에 다음 수식을 입력하고 임시 셀 수까지 끌어내립니다. 표시할 내용이 없으면 셀 내용은 비어 있지만 UID 수가 다를 수 있으므로 나중에 프로비저닝할 수 있도록 확장합니다.
=VALUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(",",TRUE,$M$4:$M$22),",",REPT(" ",99)),(ROW(1:1)-1)*99+1,99)))
O4에 다음 수식을 입력하고 아래로 드래그하세요.
=IF(ISNUMBER(VALUE(N4)),MATCH("*"&N4&"*",$M$4:$M$22,0),"")
여기에는 나중에 사용될 UID의 위치가 나열됩니다.
P4에 다음 수식을 입력하고 아래로 드래그하세요.
=IF(ISNUMBER(VALUE(N4)),IF(ISNUMBER(FIND("passed",INDEX($B$4:$B$22,MATCH(VALUE(N4),$D$4:$D$22,0)))),"P",IF(ISNUMBER(FIND("failed",INDEX($B$4:$B$22,MATCH(VALUE(N4),$D$4:$D$22,0)))),"F","M")),"")
이는 표 4의 각 UID 상태를 결정합니다.
이제 내 예에서는 출력 열 L이 있습니다.
L4에 다음 수식을 입력하고 아래로 드래그합니다.
=IF(COUNTIFS($O$4:$O$22,H4,$P$4:$P$22,"M")=COUNTIF($O$4:$O$22,H4),"M",IF(COUNTIFS($O$4:$O$22,H4,$P$4:$P$22,"P")+COUNTIFS($O$4:$O$22,H4,$P$4:$P$22,"M")=COUNTIF($O$4:$O$22,H4),"P","F"))
이는 도우미 열의 전치된 목록을 기반으로 감사 상태 통과 실패 또는 누락을 결정하는 최종 논리를 적용합니다.
이러한 데이터 세트를 Excel 테이블로 변환하고 Table[Header] 참조를 사용하여 더욱 동적으로 만들 수 있습니다.
그것을 시도해보고 이것이 당신에게 유용할 수 있는지 결정하십시오. 여러 도우미 열을 사용하기 때문에 그다지 좋지는 않다는 것을 인정합니다. 아이디어는 쉼표로 구분된 목록을 인접한 셀로 바꾼 다음 조회를 적용하는 것입니다.