☰

Excel - 여러 다른 열에 있는 여러 데이터 조각을 한 번에 찾기

tag-icon • tag-icon microsoft-excel worksheet-function
Excel - 여러 다른 열에 있는 여러 데이터 조각을 한 번에 찾기

현재 이동 중에도 계약 목록이 표시된 스프레드시트가 있습니다. 계약이 갱신인지, 제품에 대한 새로운 것인지, 회사에 새로운 것인지 알아야 합니다. 나는 현재 그 논리에 어려움을 겪고 있으며 도움을 주시면 감사하겠습니다!

보고 소프트웨어가 다양한 유형의 계약에 대해 묻지 않기 때문에 내가 원하는 것보다 적은 정보가 남습니다.

시작 날짜와 종료 날짜뿐만 아니라 각 고객은 고유 식별자를 갖고, 각 제품에는 지정된 이름이 있으며, 각 계약에는 고유 번호와 현재 "상태"가 있습니다. 여러 버전의 계약에 대해서는 "최신 버전" 열도 추가했습니다.

견본:

CompanyNumber   Product     Contract Number     Version     Status   Latest version?   Valid From      Valid To
12345              A            9876A-BA           1        Active        Yes             1/1/14       31/12/15

내가 해야 할 일은 New Customer, New to product, And 에 대한 추가 "예/아니요" 열을 생성하는 것입니다 Renewal.

그러면 피벗 테이블에서 필터링만 하면 해당 세 열에 대한 월별 보고서를 훨씬 더 쉽게 만들 수 있습니다.

다음과 같은 다른 이유로 이미 여러 개의 추가 열을 생성했습니다.

Start date in this month? - End Date In This Month - Expires in next three months?  - More than one version? - Existing Client > 365 days - Latest version?

현재 갱신에 대한 논리는 동일한 회사 번호를 가진 다른 항목의 종료 날짜가 작년 내에 있는지 먼저 확인한 다음 최신 버전의 계약이 이번 달에 시작되고 현재 계약이 유효한지 확인하는 것입니다. 최신 버전은 그렇죠

If
   unique company number = no, find if any duplicate result ended in the last year
                              AND
   Contract Starts this month AND this entry is the latest version 
= True, "YES"
  False = "NO"

그래도 어떻게 해야 할지 막막해요! 어떤 도움이라도 대단히 감사하겠습니다.

많은 감사

편집하다.

며칠 동안 노력한 끝에 여기까지 도달했습니다. 효과가 있다고 생각하지만 100%는 아닙니다.

나는 다음과 같이 중첩된 countif(고유 회사 번호) >1, countif(product)>1, 현재 및 최신 버전 함수를 사용하여 if 함수로 이것을 시도하기 시작했습니다. )>1,COUNTIF(C:C,C10)>1,(S10="예"),AA10=1),1,0)

답변은 1 또는 0으로 제공되므로 결과를 피벗 테이블에서 쉽게 사용할 수 있습니다.

누구든지 더 나은 답변을 생각한다면 알려주세요 :) -

두 번째 편집

그게 작동하지 않아서 좀 더 조사해본 결과 제대로 작동하지 않는 이 공식을 찾았습니다.

나는 회사 번호별로 모든 제품의 인스턴스를 계산할 수 있도록 "도우미" 열을 만들기로 결정했습니다. 그러나 이는 제품을 상호 참조하는 항목뿐만 아니라 회사 번호의 모든 인스턴스를 계산합니다.

=SUM(IF(C:C=C28,IF(B:B=B28,1,0),0))

제품 번호를 저장하는 열은 어디에 C:C있으며 B고유한 회사 참조입니다.

답변1

많은 경우 연결을 사용하여 데이터 요소를 결합한 다음 피벗 테이블을 사용하여 결합된 데이터 요소가 발생하는 횟수를 계산하거나 count if를 사용할 수 있습니다. 예를 들어, 회사(회사 번호)가 제품에 대한 새로운 것인지 확인하려는 경우 =Concatenate(Company Number, Product)예제에서 값을 제공하는 수식을 사용하여 도우미 열을 만들 수 있습니다 12345A. 그런 다음 또는 피벗 테이블을 사용하여 countif1) 회사가 해당 제품 라인을 처음 사용하는지 여부 2) 시작 날짜(유효 기간) 등을 확인할 수 있습니다.

관련 정보