☰

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 productRenewal

これにより、ピボットテーブルでフィルター処理するだけで、これら 3 つの列の月次レポートをより簡単に作成できるようになります。

次のような理由から、すでにいくつかの追加列を生成しています。

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?

現時点で更新のために私が持っているロジックは、まず同じ会社番号を持つ別のエントリの終了日が過去1年以内であるかどうかを確認し、次に契約の最新バージョンが今月開始され、最新バージョンであるかどうかを確認することです。

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"

しかし、どうすればいいのかわかりません。どんな助けでも大歓迎です。

どうもありがとう

編集。

1日試してみてここまで来ました。うまくいったと思いますが、100%ではありません

私はこれを、ネストされた and、countif (一意の会社番号) >1、countif(製品)>1、現在のバージョン、最新バージョンの関数を使用して、次のように if 関数として試し始めました: =IF(AND(COUNTIF(B:B,B10)>1,COUNTIF(C:C,C10)>1,(S10="Yes"),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)では値を返す数式を使用してヘルパー列を作成できます。次に、またはピボット テーブルを使用して、1) 会社がその製品ラインの新規顧客であるかどうか、2) 開始日 (有効開始日) などを確認12345Aできます。countif

関連情報