カンマ区切りの文字数チェック

質問

セル内にカンマ区切りの文字列が複数あるのですが、その文字列の文字数（バイト数）が一定数を超えたセルのみ、背景色を変更することは可能でしょうか？

例えば、各セルに以下の文字列があるとして全角10文字以上（半角20文字以上）の商品が存在する場合に限り、目立つようにしたいです。
-------------------------------------------------------------------------
（A1セル）
みかん,グレープフルーツ,長袖シャツ,めがね,アイスクリーム

（A2セル）
りんご,大根,ネクタイ,やかん,小麦粉,トイレットペーパー,はんこ,ジャージ,豆腐,シャンプー

（A3セル）
うどん,ラムネ,サラダ用スライスチーズ,鉛筆
-------------------------------------------------------------------------
※それぞれのセル内の商品数はバラバラです。

この場合、A3セルに全角11文字の「サラダ用スライスチーズ」が存在するので、A3セルの背景を変更したいということです。

宜しくお願いします。

回答No.7

keithin · ベストアンサー

簡単に関数を作って条件付き書式で色を塗った方が使いやすいと思います。

手順：
ＡＬＴ＋Ｆ１１を押す
現れた画面で挿入メニューから標準モジュールを挿入する
現れたシートに下記をコピー貼り付ける

public function MaxLenB(byval Target as variant) as variant
　dim ax
　for each ax in split(target(1), ",")
　　maxlenb = application.max(maxlenb, lenb(strconv(ax, vbfromunicode)))
　next
end function

ファイルメニューから終了してエクセルに戻る

＞全角10文字以上（半角20文字以上）の商品が存在する

Ａ列を列選択して（必ず行う）条件付き書式を開始
数式を使用して…で
=maxlenb(A1)>=20
として書式ボタンで色を付ける。

＃条件付き書式の使い方が判らないときは、ご利用のエクセルのバージョンをキチンとご相談に明記する事を憶えて下さい。次回以降のご参考に。

お礼

望んでいた理想通りの動きを確認しました。

マクロと条件付き書式の両方を使う方法なんですね！

これだとマクロをその都度実行することなく、入力直後に確認することができるので非常に助かります。

有難うございました！

回答No.6

MackyNo1

ご使用のExcelのバージョンが明記されていないので、詳細な操作方法は省略しますが、条件付き書式で対応するなら、以下の数式を使った条件付き書式を設定します。

=SUM((((LARGE((MIDB(A1,ROW($1:$200),1)=",")*ROW($1:$200),ROW($1:$10))-LARGE((MIDB(A1,ROW($1:$200),1)=",")*ROW($1:$200),ROW($2:$11)))>19)*1))+AND(ISERROR(FIND(",",RIGHTB(A1,19))),A1<>"")

上記の数式は1つのセルに入力されている文字数が半角で200文字までで、カンマで区切られた数が最大11件までの数式になっていますので、それよりも文字数や組み合わせの数が多い場合は適宜数字を大きくしてください。

お礼

教えて頂いた条件付き書式で希望通りの動きを確認することができました！

非常に複雑な数式になるんですね。

ただ、セルによっては商品数が膨大な数になる場合もあるので、これで半角200文字やカンマ数11の各制限値がないと助かります。

ご丁寧に有難うございました。

回答No.5

classical

＃１です。
すいません。ひとつ勘違いしてました。
列かと思ったら行でしたね・・・
ということで訂正
＿＿＿＿＿＿＿＿＿＿＿

Sub sample()

Dim tmp As Variant '配列用
Dim MaxRow As Integer
MaxRow = Cells(Rows.Count, 1).End(xlup).Row '最終行取得
Dim i, j As Integer

For i = 1 To MaxRow
tmp = Split(Cells(i , 1), ",")
For j = LBound(tmp) To UBound(tmp)
If Len(tmp(j)) > 10 Then
Cells(1, i).Interior.ColorIndex = 6
Exit For
End If
Next j
Next i

End Sub

お礼

ありがとうございます。

せっかく、再度ご回答頂きましたのに、今回のマクロもなぜか希望通りの動きになりませんでした。

こちらのエクセルバージョン等をお知らせしていなかったのが原因ですね。。
本当に申し訳ございません。

回答No.4

WindFaller

こんにちは。

>セル内にカンマ区切りの文字列が複数あるのですが、その文字列の文字数（バイト数）が一定数を超えたセルのみ、背景色を変更することは可能でしょうか？

関数では、配列を使うしかないでしょうね。
ただ、関数の得意な人にお任せですね。こちらが下手に作って、マナーの悪い人によけいなチャチャ入れられてもつまらないですから。たぶん、数式を条件付き書式に入れるのだと思います。

本来、カンマ区切りなら、一旦、区切り位置ウィザードで、カンマ区切りして、セルそれぞれに入れて、

条件付き書式の数式で、　=LENB(A1)>=20

として、同じ条件付き書式を必要な場所全体に、書式コピーした方が楽かもしれませんね。

VBAでは、少しややこしいです。半角扱いですと、一旦JISに戻さないといけません。以下は、おまけを付けてみました。A列から探していきます。

'//　'標準モジュール
Sub TestSample1()
　Dim c As Range
　Dim ar As Variant
　Dim i As Long
　Dim j As Long
　Const BYT As Integer = 20　'バイト数
　Application.ScreenUpdating = False '画面のチラツキを抑える
　For Each c In Cells(Rows.Count, "A").End(xlUp)
　　If c.Value <> "" Then
　　　ar = Split(c.Text, ",", , 1)
　　　For i = 0 To UBound(ar)
　　　　If LenB(StrConv(ar(i), vbFromUnicode)) >= 20 Then
　　　　　j = InStr(1, c.Value, ar(i), 0)
　　　　　c.Characters(Start:=j, Length:=Len(ar(i))).Font.Color = vbRed
　　　　　c.Interior.ColorIndex = 34 '背景・淡い水色
　　　　　Exit For
　　　　Else
　　　　　c.Font.ColorIndex = xlAutomatic 'フォントの色を戻す
　　　　　c.Interior.ColorIndex = xlColorIndexNone '色を消す
　　　　End If
　　　Next i
　　End If
　Next c
　Application.ScreenUpdating = True
End Sub

'///

お礼

教えて頂いたマクロの実行で希望通りの動きを確認することができました！

しかも、該当商品の文字色まで目立つようにして頂いているとは！

ただ、商品を入力するものが複数人おりまして、マクロの実行自体を忘れる可能性が高いため、これが条件付き書式の設定で実現できれば助かります。

ご丁寧に「おまけ」を付けて頂いてありがとうございました！

回答No.3

tom04

こんにちは！
＞全角10文字以上（半角20文字以上）の・・・
とありますので、バイトすで判断してみました。
VBAでの一例です。

Sub Sample1()
Dim i As Long, k As Long, myArray
Range("A:A").Interior.ColorIndex = xlNone
For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
myArray = Split(Cells(i, "A"), ",")
For k = 0 To UBound(myArray)
If LenB(StrConv(myArray(k), vbFromUnicode)) > 20 Then
Cells(i, "A").Interior.ColorIndex = 36 '←薄い黄色
Exit For
End If
Next k
Next i
End Sub

※　条件付き書式が設定しれある場合は反応しませんので、
条件付き書式は設定していない状態でマクロを実行してみてください。m(_ _)m

お礼

教えて頂いたマクロの実行で希望通りの動きを確認することができました！

これが条件付き書式の設定で実現できれば最高なので、もう少し回答を待って無理なら使わせて頂きます。

ありがとううございました！

回答No.2

bunjii

>セル内にカンマ区切りの文字列が複数あるのですが、その文字列の文字数（バイト数）が一定数を超えたセルのみ、背景色を変更することは可能でしょうか？
条件付き書式で以下の数式を使えば可能です。

カンマを含めた半角相当文字数（バイト数）で判断するとき
=LENB(A1)>20

カンマを除く半角相当文字数で判断するとき
=LENB(SUBSTITUTE(A1,",",""))>20

貼付画像はExcel 2013で検証したものです。

お礼

ありがとうございます。

この数式の場合はカンマ間の個々の商品の文字数ではなく、セル内全体の文字数（バイト数）が判断基準となってしまい、希望通りの動きになりませんでした。

これまではカンマ区切りの商品をセル毎に分け、教えて頂いた数式で分別していました。
その作業に不便を感じていましたので、今回質問させて頂きました。

回答No.1

classical

Option Explicit

Sub sample()

Dim tmp As Variant '配列用
Dim MaxRight As Integer
MaxRight = Cells(1, Columns.Count).End(xlToLeft).Column '最終列取得
Dim i, j As Integer

For i = 1 To MaxRight
tmp = Split(Cells(1, i), ",")
For j = LBound(tmp) To UBound(tmp)
If Len(tmp(j)) > 10 Then
Cells(1, i).Interior.ColorIndex = 6
Exit For
End If
Next j
Next i

End Sub

こんな感じでどうでしょう？

points
(1)　split関数で文字列を分割し、配列tmpに格納
(2)　Lbound = 配列の最初を調べる　UBound = 配列の最後を調べる
(3)　Len = 文字列の長さを調べる

お礼

ありがとうございます。
マクロを実行しましたが、なぜか希望通りの動きになりませんでした。
申し訳ございません。