エクセルで関数を利用し、警告が出た時、文字色を変化させる方法

質問

A1に１～５、６～１０、１１～１５、１６～２０…
が入り、
B1にあ、い、う、え…
を入力した時、
C1に「１～５」と「あ」、「６～１０」と「い」、「１１～１５」と「う」、「１６～２０」と「え」…
の組み合わせで有れば、非表示で
「１～５」と「い」、「６～１０」と「あ」、「１１～１５」と「い」、「１６～２０」と「う」…
の様に一つずれた組み合わせで有れば、「警告１」を表示し、文字色は「青」
同様に
「１～５」と「う」、「６～１０」と「え」、「１１～１５」と「あ」、「１６～２０」と「い」…
の様に二つずれた組み合わせは、「警告２」を表示し、文字色は「黄」
続けて、三つずれれば、「警告３」を表示し、文字色は「赤」
四つずれれば、「警告４」を表示し、文字色は「黒地に赤文字」
五つずれれば、…

というふうに、IF関数と条件付き書式を利用して関数を作りました。
A1に入る数を５０位まで拡張したいのですが、
関数では無理があり、条件付き書式も３つ迄しか入りません。

他の関数を利用する方法、或いはマクロを使用すればもっと簡単に
出来るでしょうか？
宜しくお願いします。

回答No.3

Wendy02

こんにちは。

実際は、「あ,い,う,え,お」ではないでしょうから、以下のコードのCHARS のリストに、コンマ（,)区切りで書き入れてください。

A列,B列,C列に設定するようになっていますが、それが違うのでしたら、列番号を入れてください。マクロをよく読んで、書き加えられるところは、ご自身でおやりください。

このマクロは、シートモジュールに設定してください。

取り付け方：
マクロを実行しようとするワークシートのシートタブ(下部のSheet1,Sheet2 ...)を右クリックすると、「コードの表示(V)」というメニューがありますから、それをクリックしてください。そして、開いた画面に、以下のコードを貼り付けて、Alt + Q で閉じれば設定は完了です。

本来は、関数で、色を変えていく方法を最初に作っていましたが、それは、今の段階で、掲示板では公開をしたくないのでやめました。途中でコードを切り替えたので、少しコードにヘンなところがあるかもしれません。一応、動作試験はしています。

'シートモジュール
'----------------------------------------------------
Private Const CHARS As String = "あ,い,う,え,お"

Private Sub Worksheet_Change(ByVal Target As Range)
　　Dim arChars As Variant
　　Dim intNumber As Variant
　　Dim strText As Variant
　　Dim CondCell As Range
　　Dim i As Integer
　　Dim flg As Boolean

　　Const NUMCOL As Integer = 1 '数値を入れる列
　　Const CHRCOL As Integer = 2 '文字を入れる列
　　Const CONDCOL As Integer = 3 '条件で色を変える列

　　arChars = Split(CHARS, ",") '配列

　　If Target.Column <> NUMCOL And Target.Column <> CHRCOL Then Exit Sub
　　If Target.Count > 1 Then Exit Sub
　　If Target.Value = "" Then Exit Sub

　　Set CondCell = Target.EntireRow.Cells(CONDCOL)
　　'初期化
　　CondCell.Interior.ColorIndex = xlNone
　　CondCell.Font.ColorIndex = xlAutomatic

　　'数字 , 文字
　　intNumber = Target.EntireRow.Cells(NUMCOL).Value
　　If IsNumeric(intNumber) = False Then Exit Sub
　　strText = Target.EntireRow.Cells(CHRCOL).Value
　　If VarType(strText) <> vbString Then Exit Sub

　　If intNumber = "" Or strText = "" Then Exit Sub

　　i = 0:　flg = False
　　For i = LBound(arChars) To UBound(arChars)
　　　　If StrComp(arChars(i), strText) = 0 Then
　　　　　　flg = True
　　　　　　Exit For
　　　　End If
　　Next
　　i = Abs(i - Int((intNumber - 1) / 5)) '計算
　　Application.EnableEvents = False
　　If i = 0 And flg Then
　　　　CondCell.Value = ""
　　ElseIf i > 0 And flg Then
　　　　CondCell.Value = "警告" & CStr(i)
　　ElseIf flg = False Then
　　　　CondCell.Value = "不明"
　　　　i = 99
　　End If
　　Application.EnableEvents = True
　　Call MyColorChange(CondCell, i)
　　Set CondCell = Nothing
End Sub
Private Function MyColorChange(ByVal mArea As Range, ByVal idx As Integer)
　　On Error Resume Next
　　With mArea
　　　　.Interior.ColorIndex = xlNone: .Font.ColorIndex = 1
　　　　'ColorIndex をヘルプで参照のこと
　　　　Select Case idx
　　　　　　Case 0: .Interior.ColorIndex = xlNone: .Font.ColorIndex = xlNone
　　　　　　Case 1: .Font.ColorIndex = 5 '青色
　　　　　　Case 2: .Font.ColorIndex = 6 '黄色
　　　　　　Case 3: .Font.ColorIndex = 3 '赤色
　　　　　　Case 4: .Interior.ColorIndex = 1: .Font.ColorIndex = 3 '黒地赤文字
　　　　　　Case 5: .Interior.ColorIndex = 1: .Font.ColorIndex = 6 '黒地黄文字
　　　　　　'この後に同じように付け足す-Case Else は消さない
　　　　　　Case Else: .Interior.ColorIndex = xlNone: .Font.ColorIndex = 1
　　　　End Select
　　End With
　　On Error GoTo 0
End Function

お礼

丁寧で、詳しいご回答ありがとうございます。
やはり、これくらいのマクロが必要になりますね。
頑張って、勉強します。
実際にトライして、それからまた、ご報告、お礼をさせて頂きます。

ありがとうございます。

回答No.2

Nouble

ご存じでしょうか
文字には各々に内部処理用に数値が振り当てられています。

a → 97
b → 98
・ ・ ・
・ ・ ・
・ ・ ・
z → 122

A → 65
B → 66
・ ・ ・
・ ・ ・
・ ・ ・
Z → 90
と言う具合にです。

エクセルではこの数値を取り出すためにCODEという関数が予約されています。

又この事は全角文字についても同様のことが言えます
あ → 9250
い → 9252
う → 9254
・ ・ ・
・ ・ ・
・ ・ ・
という感じです。
まあ半角文字は1つずつ割り当てられる数値が増えるのに対し
全角では2つずつ増えるという違いがありますが、　ね。

試しに何処かのcellに=CODE(”あ”)と入れてみてください
9250と返されるはずです。

では
=CODE(”あ”)/2-4624と入れるとどうなるでしょうか？

そう
1と返されますね

同様に
=CODE(”い”)/2-4624 → ２
=CODE(”う”)/2-4624 → ３
・ ・ ・
・ ・ ・
・ ・ ・
となります。

次に
何処かのcellに
=INT((A1-1)/5)+1
とか
=SUMPRODUCT(FREQUENCY(A1,(ROW($A$1:$A$10)-1)*5)*(ROW($A$1:$A$11)-1))
とか
式を入力したうえでA1の値を様々に変えてみると
　１～　５ → １
　６～１０ → ２
１１～１５ → ３
・ ・ ・
・ ・ ・
・ ・ ・
４５～５０ → １０
と表示されます、よね。

もう此処までくるとお気づきかと思いますが
CODE(B1)/2-4624-(INT((A1-1)/5)+1)の値が
=0ならズレのない組み合わせ
=1又は=-1ならば一つずれた組み合わせ
=2又は=-2ならば二つずれた組み合わせ
=3又は=-3ならば三つずれた組み合わせ
・
・
・
となりますよね
エクセルには絶対値を返すABS関数が予約されています。

ので、
=ABS(CODE(B1)/2-4624-(INT((A1-1)/5)+1))
とか
=ABS(CODE(B1)/2-4624-SUMPRODUCT(FREQUENCY(A1,(ROW($A$1:$A$10)-1)*5)*(ROW($A$1:$A$11)-1)))
とかすると
この式の値が０の時はズレ無しで
１の時は警告１
２の時は警告２
・
・
とできると思います。

さて大詰めですが
一般的に
エクセル関数上級者たちはIF構文を使うことを嫌います
何故かというとIF構文は()を悪戯に浪費してしまうため
エクセルのネスト限界の壁に容易に引っかかってしまうからです。

ではどうするのか？

解決策には色々あります
例えばC1に先程の
=ABS(CODE(B1)/2-4624-(INT((A1-1)/5)+1))
とか
=ABS(CODE(B1)/2-4624-SUMPRODUCT(FREQUENCY(A1,(ROW($A$1:$A$10)-1)*5)*(ROW($A$1:$A$11)-1)))
とかを入力した上
このCELLにセルの書式設定の表示形式として
"警告"0;"警告"0;#
と設定するのもその一つだし

=TEXT(ABS(CODE(K11)/2-4624-(INT((J10-1)/5)+1)),"警告0;警告0;#")
としても機能するし

面白いところでは
=CHOOSE(ABS(CODE(K10)/2-4624-(INT((J10-1)/5)+1))+1,"","警告1","警告2","警告3","警告4","警告5","警告6","警告7","警告8","警告9")
とか
=INDEX({"","警告1","警告2","警告3","警告4","警告5","警告6","警告7","警告8","警告9"},ABS(CODE(K10)/2-4624-(INT((J10-1)/5)+1))+1)
と、言うのもあります。

このCHOOSE構文やINDEX構文は優れもので
SUMPRODUCT構文やFREQUENCY構文・MATCH構文
SMALL構文・LOOKUP構文・OFFSET構文・INDIRECT構文・ROW構文等と同様に
頻繁に使われるものの1つですし
エラーに対する警告処理に留まらず
発想次第で様々に使えますので是非マスターしてくださいね

後、
=INT((A1-1)/5)+1
を使うより
=SUMPRODUCT(FREQUENCY(A1,(ROW($A$1:$A$10)-1)*5)*(ROW($A$1:$A$11)-1))
を使う方が長いですが
FREQUENCY構文の機能により
予期せぬ数値入力があった場合でも
シート上にエラー表示がでないので優れものです

入力値エラーはシートエラーとして演算拒否に陥らせるより
警告情報選択肢を増やして対処する方が良いですしね

ところで最後ですが
mshr1962さんの言われているように
色は4色が限界です
(^ヘ^)v

お礼

丁寧で、詳しいご回答ありがとうございます。
大変勉強になります。
エクセル初心者ですが、CHOOSE構文・LOOKUP構文・ROW構文は
勉強して使うようにしています。

＞文字には各々に内部処理用に数値が振り当てられています
知りませんでした。
（日付に数値がある事は知っていましたが）

＞色は4色が限界です
ここが重要な事と、実際は数字や文字ではなく文章が入ります。
それで、やはり関数だけでは無理なのだと解りました。
マクロの勉強をしようと思います。

また、質問する事になりそうです。ありがとうございました！！！！

回答No.1

mshr1962

警告だけでいいなら
あ　1
い　6
う　11
え　16
お　21
のような表を作成（シート名が元表でA列に文字、B列に数値で2行目から50行）
=IF(COUNTIF(元表!$A$2:$A51,B1),TEXT(ROUNDUP(ABS(INT(VLOOKUP(B1,元表!$A$2:$B51,2,FALSE)-FLOOR(A1-1,5)-1)/5),0),"警告0;;"),"")

※条件付書式では元の色を含めて4色が限度なので、色を変える場合はマクロが必要です。

お礼

回答ありがとうございます。
やはり、関数と条件付き書式だけでは無理ですね。

どなたか、マクロではどのように作ったら良いか、教えて頂けませんか？
宜しくお願い致します。