태그 보관물: formulas

formulas

Google 스프레드 시트에서 두 목록을 비교하고 누락 된 항목 표시 목록 (초대-참석)을 비교하고 세 번째 목록

두 개의 별도 목록 (초대-참석)을 비교하고 세 번째 목록 (결석)을 만드는 솔루션을 찾고 있습니다.

이 기능은 “유인 목록 (D, E, F)”의 키를보고 “초대 목록 (A, B, C)”의 키와 비교 한 다음 “유인 목록 (G, H)에 누락 된 값을 표시해야합니다. ,나는)”.

데이터에 대하여

  • 키는 초대 된 각 사람에게 할당 된 ‘고유 값’입니다.
  • “초대 목록 (A, B, C)”의 데이터는 QUERY 데이터로 구성됩니다.
  • “Attended List (D, E, F)”의 데이터는 셀의 값을 복사 / 붙여 넣기합니다.

원하는 결과의 예

Invitded List (A,B,C)      Attended List (D,E,F)       Absent List (G,H,I)
 A      B       C            D      E       F            G      H        I
Key | Fname | Lname       | Key | Fname | Lname       | Key  | Fname | Lname
-------------------------------------------------------------------
001   Tim     Smith         002  Mike     Jones         001    Tim     Smith
002   Mike    Jones         004  Jenny    Johnson       003    Amy     Wilson
003   Amy     Wilson
004   Jenny   Johnson

도움을 주시면 감사하겠습니다.

답변

다음과 같이 시도해보십시오. 

=if(countif(D:D,A2)=0,A2,"")

그런 다음에 맞게 위아래로 복사하십시오.

COUNTIF

답변

공식

=ArrayFormula(FILTER(A4:C7,ISERROR(match(A4:A7,D4:D5,0))))

설명

초대받은 사람이 참석 한 목록에 나타나지 않으면 MATCH에서 오류를 반환합니다. ISERROR는 오류를 TRUE로 변환하고 값을 FALSE로 변환합니다.이 결과는 필터링 기준으로 사용됩니다. 결과는 결석 목록입니다.

|   001 |Tim       |Smith
|   003 |Amy       |Wilson

수식 위에 적절한 헤더를 추가하십시오.

답변

VLOOKUP 은 원하는 답변을 얻을 수있는 또 다른 방법이지만 원하는 방식으로 제공되는지 잘 모르겠습니다.

Google 스프레드 시트 도움말 (VLOOKUP)에서

키의 범위의 첫 번째 열을 검색하고 찾은 행에서 지정된 셀의 값을 반환합니다.

따라서 Status참석자 목록에 열 을 추가 VLOOKUP하면 초대 된 목록에서 열을 실행 하여 Attended상태 를 반환하도록 요청할 수 있습니다.

데이터는 다음과 같습니다.

VLOOKUP 전

셀 D2에서 VLOOKUP수식은 다음과 같습니다.

=VLOOKUP(A2, E:H, 4, FALSE)

  • A2 열쇠입니다
  • E:H 범위 (참가자 목록)
  • 4반환하려는 값 범위 의 열 번호 를 나타냅니다.
  • FALSE 키에서 정확히 일치하는 것을 찾고 있음을 나타냅니다.

셀 D2에서 D5로 수식을 채우면 다음과 같은 데이터가 나타납니다.

VLOOKUP 후

일치하는 부분이 있으면 Status값을 가져옵니다. 일치하지 않는 경우가 표시 #N/A됩니다.

답변

하나의 세포 솔루션 :

FILTER기능과 기능을 결합 COUNTIF하면 원하는 목록이 제공됩니다.

=FILTER(A:C,COUNTIF(D:D,A:A)=0))

이 예에서는 키를 열 A와 D의 일치 항목을 비교하는 일치 필드로 사용했지만 다른 열과도 작동합니다.

답변