ジェフは、列Cにかなりの量のテキストを含むワークシートを持っています。彼は、列C内の特定の文字列の出現をカウントしたいと考えていますが、データ内の奇数行(1、3、5など)のみを対象としています。彼が探している文字列は、セルの内容全体ではない可能性があり、特定のセル内で複数回発生する可能性があります。 (セル内で2回または3回発生する場合は、2回または3回の発生としてカウントする必要があります。)彼は、マクロに頼らずにこれを行う公式の方法があるかどうか疑問に思います。

これにアプローチする1つの方法は、ヘルパー列を使用することです。たとえば、列Dをヘルパー列として使用でき、データを含む最初のセルがセルC2であるとします。 (おそらく、セルC1には列見出しがあります。)セルD2では次の数式を使用できます。

=IF(MOD(ROW(),2)=1,(LEN(C2)-LEN(SUBSTITUTE(C2,"my text","")))/LEN("my text"),"")

あなたがする必要があるのはあなたが探しているもので検索文字列(「私のテキスト」)を置き換えることです。 LEN関数は2回使用されます。最初に、セルC2にあるものの長さを見つけ、次に「mytext」のすべてのインスタンスが削除されたテキストの長さを減算します。次に、この値を検索対象の長さで割ると、セル内に検索テキストのインスタンスがいくつ含まれるかがわかります。 IF関数は、行が奇数行の場合にのみ数値が返されるようにすることに注意してください。

この数式を必要な数の行にコピーしてから、列を合計できます。結果は、文字列が奇数行に表示される回数です。

ワークシートのレイアウトでヘルパー列を使用できない場合は、データの配列で機能する数式を使用できます。

これがトリックを行うものです:

=SUMPRODUCT((LEN(C:C)-LEN(SUBSTITUTE(C:C,"my text","")))/LEN("my text")*ISODD(ROW(C:C)))

この数式は、SUMPRODUCT関数が列Cの各セルに対して内部的に計算を行うことを除いて、基本的に前の数式と同じです。数式は列Cのすべてを検査するため、検索テキスト( “my text “)は、列内の非データセル(列ヘッダーなど)内で発生し、返される合計にも含まれます。

マクロを使用することにした場合は、セルの範囲を調べてカウントを決定するユーザー定義関数を簡単に作成できます。以下は、使用できるマクロのタイプの例です。

Function CountInst(rSource As Range, sSearch As String, bCaseInsensitive As Boolean) As Integer     Dim c As Range     Dim iCount As Integer     Dim sTemp1 As String     Dim sTemp2 As String

sTemp2 = sSearch     If bCaseInsensitive Then sTemp2 = LCase(sTemp2)

iCount = 0

For Each c In rSource         If c.Row Mod 2 = 1 Then             sTemp1 = c.Text             If bCaseInsensitive Then sTemp1 = LCase(sTemp1)



iCount = iCount + (Len(sTemp1) - _               Len(Replace(sTemp1, sTemp2, ""))) / Len(sTemp2)

End If     Next c

CountInst = iCount End Function

これを使用するには、範囲、検索対象、および大文字と小文字を区別しないようにするかどうかを指定するだけです。たとえば、「my text」を検索して大文字と小文字を区別しない場合は、次を使用します。

=CountInst(C2:C99,"my text",TRUE)

_ExcelTips_は、費用効果の高いMicrosoftExcelトレーニングのソースです。

このヒント(1514)は、Microsoft Excel 2007、2010、2013、および2016に適用されます。