☰

他の 2 つの列の条件に基づいて、1 つの列の一意の値をカウントするにはどうすればよいですか?

tag-icon • tag-icon microsoft-excel-2010
他の 2 つの列の条件に基づいて、1 つの列の一意の値をカウントするにはどうすればよいですか?

列 C が NO で列 D が YES である列 A の一意のエントリの数をカウントしようとしています。ただし、2 つの異なる基準に基づいてこれを行う数式を作成することができません。どうすればこのようなことができるでしょうか?

たとえば、イベントをオンデマンドで視聴したがライブでは視聴しなかった国の数を知りたいのですが、次の例では 4 になります。

country  |  Preview  |  Live  |  On Demand
GB       |  NO       |  NO    |  YES
GB       |  NO       |  YES   |  YES
ES       |  NO       |  YES   |  YES
DE       |  NO       |  NO    |  YES
FR       |  NO       |  NO    |  YES
US       |  NO       |  NO    |  YES

以下の回答で提案されている数式から、Excel で次の数式を受け入れるようにできましたが、値は返されません。この目的は、E が 1 に等しい場合に列 F に 1 を入力することです。どなたか助けていただければ幸いです。

=IF(E=1, IF(FREQUENCY(MATCH(A2:A214, A2:A214, 0), MATCH(A2:A214, A2:A214, 0)) > 0, 1, 0))

答え1

両方の列の値を単純に組み合わせる数式と、新しい/一意の値をカウントする別の数式を追加することを検討しますか?

Eの式=$C:$C&$D:$D

Fの式=IF(COUNTIF(E$1:E2,E2)=1,1,0)（これはセル（F2）の例です）

country  |  Preview  |  Live  |  On Demand | Combined | New/unique
GB       |  NO       |  NO    |  YES       | NOYES    | 1
GB       |  NO       |  YES   |  YES       | YESYES   | 1
ES       |  NO       |  YES   |  YES       | YESYES   | 0
DE       |  NO       |  NO    |  YES       | NOYES    | 0
FR       |  NO       |  NO    |  YES       | NOYES    | 0
US       |  NO       |  NO    |  YES       | NOYES    | 0

答え2

次の配列数式を使用して、すべての条件を満たす一意のレコードの数を取得できます。次のコードを数式バーに貼り付けて、Ctrl+ Shift+を押しますEnter。

=SUMPRODUCT((C2:C8="NO")*(D2:D8="YES")/IF(COUNTIFS(A2:A8,A2:A8,C2:C8,"NO",D2:D8,"YES")=0,1,COUNTIFS(A2:A8,A2:A8&"",C2:C8,"NO",D2:D8,"YES")))

長くて少し繰り返しになりますが、うまくいくはずです。この式は、1/COUNTIF(...)上で示したトリックを使用しています多くの 回このサイトでは、以前、一意のレコードをカウントするために、エラーがIF発生しないように繰り返し条件を導入する必要がありました#DIV/0!。

この数式が行うこと:

SUMPRODUCT配列引数が 1 つの場合、配列の要素を追加するだけです。乗算項は、どちらかの条件が満たされない場合は 0 になり、両方の条件が満たされた場合は 1 になります。 で割ることは、COUNTIFSこの値をスケーリングして一意のカウントを取得する方法です。たとえば、On Demand で Live ではない のレコードが 2 つある場合、それらの各レコードは合計で レコードとしてGBカウントされるように縮小されます。そのようなレコードが 3 つある場合、それぞれが としてカウントされます。このようにして、合計でこれらのレコードを追加すると、On Demand で Live ではない の合計は1 になります (例: + = ) 。これが一意性を実現する方法です。1/21/3GB1/21/21

答え3

Makro を作成し、「CountMyUniqueEntries」という名前を付け、VBA エディターを開いて次のコード スニペットを Module1 にコピーします。

Sub CountMyUniqueEntries()
    Dim cells As Range: Set cells = Excel.Selection
    'the selection does only contain data, no column-headers
    Dim i
    Dim uniqueCountries As New Collection
    For i = 1 To cells.Rows.Count
        Dim Live As Boolean: Live = (UCase(cells(i, 3)) = "YES")
        Dim OnDemand As Boolean: OnDemand = (UCase(cells(i, 4)) = "YES")
        Dim country As String: country = UCase(cells(i, 1))
        If Not Live And OnDemand Then
            If Not Contains(uniqueCountries, country) Then
                uniqueCountries.Add country, country
            End If
        End If
    Next
    MsgBox "The number of unique countries is: " & uniqueCountries.Count 
End Sub
Private Function Contains(col As Collection, ByVal entry As String) As Boolean
    On Error Resume Next
    If IsEmpty(col(entry)) Then: 'DoNothing
    Contains = (Err.Number = 0)
    On Error GoTo 0
End Function

おそらく最初に「オプション」の「開発者ツール」メニューをアクティブにする必要があるでしょう

答え4

これはかなり複雑な数式になる傾向があります。これをいくつかの部分に分割して、これがどのように機能するかをより明確かつ明白にします。
すでに 4 つの列 A、B、C、D があります。さらに 4 つの列 E、F、G、H が必要になります。

列 E で、この数式をすべてのセルにコピーします (最初のセルの右下の + 記号を使用してコピーします)。

=IF(C2="NO", IF(D2="YES", 1, 0), 0)

列 F の最初のセルに次の数式をコピーします。

=IF(FREQUENCY(MATCH(A2:A8, A2:A8, 0), MATCH(A2:A8, A2:A8, 0)) > 0, 1, 0)

F列のすべてのセルを選択し、「F2」を押してから、「Ctrl」+「Shift」+「Enter」を押します。

G列のすべてのセルに次の数式をコピーします。

=IF(E2=1, IF(F2=1, 1, 0), 0)

追加のセル（おそらく列 H など）を使用して、G の 1 を合計します。

=SUM(G:G)

楽しむ

関連情報