セル内のカンマ区切り値をカウントする

このチュートリアルでは、Excel でセル内のカンマ区切り値をカウントする方法として、LEN 関数、TRIM 関数、SUBSTITUTE 関数を組み合わせて活用するテクニックを学びます。

Excel でセル内のカンマ区切りの値をカウントするには？

以下のスクリーンショットのように、列 B にカンマ区切りの文字列リストがあり、各セル内のカンマ区切り値の個数をカウントしたい場合は、次の手順で実現できます。

汎用数式

=IF(ISBLANK(B3),「」,LEN(TRIM(B3))-LEN(SUBSTITUTE(TRIM(B3),",",「」))+1)

引数

この数式はどのように使いますか？

1．結果を出力する空白セル（ここではセル D3）を選択します。

2．下記の数式を入力してEnterキーを押した後、フィルハンドルを下までドラッグして他のセルにも適用してください。

=IF(ISBLANK(B3),「」,LEN(TRIM(B3))-LEN(SUBSTITUTE(TRIM(B3),",",「」))+1)

注：この数式では、セル B3 にカンマ区切りの文字列が含まれており、そのカンマの数をカウントします。必要に応じてセル参照を変更してください。

この数式の動作原理は？

=IF(ISBLANK(B3),「」,LEN(TRIM(B3))-LEN(SUBSTITUTE(TRIM(B3),",",「」))+1)

1．LEN(TRIM(B3))：TRIM 関数はカンマ区切りの文字列「AA, BB, CC, DD」から余分なスペースをすべて削除し、単語間のスペースを1 つだけに整えます。その後、LEN 関数がこの文字列の全長を計算し、結果として14 を返します。

2．LEN(SUBSTITUTE(TRIM(B3),",",「」))

• SUBSTITUTE(TRIM(B3),",",「」) 上記の説明のように、TRIM 関数は「AA, BB, CC, DD」から余分なスペースをすべて削除し、「AA, BB, CC, DD」として結果を返します。その後、SUBSTITUTE 関数が「AA、 BB、 CC、 DD」内のすべてのカンマを空文字に置き換え、「AA BB CC DD」という結果を得ます。
• LEN(「AA BB CC DD」)LEN 関数は、テキスト文字列「AA BB CC DD」の文字数を返します。その結果は11 です。

3．14-11+1：ここでは、「AA, BB, CC, DD」の全長から「AA BB CC DD」の全長を引いて、文字列内のカンマの総数を求めます（14－11＝3）。最後のカンマの後には必ず単語が続くため、この3 に1 を加えることで、カンマ区切りの単語総数が得られます。この場合の結果は4 です。

4．IF(ISBLANK(B3),「」,14)：この IF 関数は、B3 が空欄の場合には何も返さず、値が入力されている場合には数値14 を返します。ここでは B3 に値が含まれているため、IF 関数は14 を返します。

関連関数

関連数式