エクセルで数値の一致を求め、書式設定を与える

質問

エクセルに詳しくないので教えていただけませんか？

画像の、BC51　セルに846と数字が入っています。
この数字は単体で、８、４、６と考えていただいて、

それとは別に　AA４５　セルから　AA50セルに4桁の数字が入るのですが、
例えば、AA45セル　これには先ほどの　8,4,6の数字のうち４と６の二つの数字が含まれていますよね。

続くAA４６セルには、４，５，６，８　ですから、先ほどのBC51セルの数字　８，４，６が3つ含まれています。

この様にBC51セルの数字が二つ含まれている場合と、三つ含まれている場合で画像の様に色付けを毎回自動でやる様な関数ってどのようなものになるのでしょうか？

もしくは、ABセルにその結果を求め、条件付書式で実現する事も出来るだろうなと思います。

ちなみにAセルの番号は、セル番号と考えていただいて構いません。

詳しい方、よろしくお願いいたします。

回答No.4

keithin · ベストアンサー

たとえばＡＡ４５の条件付き書式の数式で
=(LEN(AA45)=4)*(ISNUMBER(FIND(MID(AA45,1,1),$B$51))+ISNUMBER(FIND(MID(AA45,2,1),$B$51))+ISNUMBER(FIND(MID(AA45,3,1),$B$51))+ISNUMBER(FIND(MID(AA45,4,1),$B$51)))=2
あるいは
=(LEN(AA45)=4)*(ISNUMBER(FIND(MID(AA45,1,1),$B$51))+ISNUMBER(FIND(MID(AA45,2,1),$B$51))+ISNUMBER(FIND(MID(AA45,3,1),$B$51))+ISNUMBER(FIND(MID(AA45,4,1),$B$51)))=3
と設定し、それぞれ２個および３個の色塗りを設定します。
ＡＡ４５をコピー、下向けに貼り付けるなどして展開します。

あるいは、隣のAB45に
=(LEN(AA45)=4)*SUM(--ISNUMBER(FIND(MID(AA45,{1,2,3,4},1),$B$51)))
と記入して以下コピーしておくと、含まれる数字の個数の合計が１，２，３，０(ゼロは検査値が不適合の場合を含む）と現れます。条件付き書式で調べて使います。

＃参考
＞画像の、BC51　セルに846と数字が入っています。

結合したセルでは、先頭のＢ５１にデータがあります。間違えないように気を付けてください。

お礼

keithinさん　ご回答ありがとうございます。

やってみたところ、両方の方法が出来ることに気がつきました。

ありがとうございます！！

ただ、私の使っているエクセルはバージョンが2002なのですが、何回かやったり削除したりしていると数式入れても色分けできなくなりました。

アップした画像はテスト的に作ったものなので問題は無いのですが、本番になると困りますね。

今回は一発で求める結果にたどり着くことが出来ました、ありがとうございます。

回答No.9

kagakusuki

折角、回答:No.3において

>　もし、条件付き書式の設定方法が判らない場合には、この解答欄の中に表示されている「補足する」ボタンをクリックすると現れる補足欄を使用して、その旨を御知らせ下さ。
>　又、その際には、Excelのバージョンによって設定方法が異なる場合がありますので、御使いのExcelのバージョンがどれであるのかに関しましても、必ず御知らせ願います。

と書いていたにも関らず、私への補足には

>ですから、AA1に条件付き書式を与えようとしてもダイアログに数式を入れる所が無い様に見えます。

という事が書かれていなかったために気づくのが遅れ、回答:No.8で合わせて説明する事が出来ませんでした。

　Excel2002における条件付き書式の設定方法は、回答:No.8の方法を例に採りますと、次の様になります。（他の回答者の方々の方法でも、入力する数式が異なるだけで、操作方法は同様です）

AA45セルを選択
　　↓
「メニュー」バーの[書式]ボタンをクリック
　　↓
現れた選択肢の中にある[条件付き書式]をクリック
　　↓
現れた「条件付き書式の設定」ダイアログボックスの左端の欄をクリック
　　↓
現れた選択肢の中にある「数式が」をクリック
　　↓
「条件付き書式の設定」ダイアログボックスの左から2番目の欄に

=SUMPRODUCT(ISNUMBER(FIND(MID($B$51,ROW($1:$3),1),AA45))*1)=3

と入力
　　↓
「条件付き書式の設定」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[パターン]タブをクリック
　　↓
現れた色のサンプルの中にある着色したい色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式の設定」ダイアログボックスの[追加]ボタンをクリック
　　↓
「条件付き書式の設定」ダイアログボックスの「条件2(2)」内の左端の欄をクリック
　　↓
現れた選択肢の中にある「数式が」をクリック
　　↓
「条件付き書式の設定」ダイアログボックスの「条件2(2)」内の左から2番目の欄に

=SUMPRODUCT(ISNUMBER(FIND(MID($B$51,ROW($1:$3),1),AA45))*1)=2

と入力
　　↓
「条件付き書式の設定」ダイアログボックスの「条件2(2)」内の[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[パターン]タブをクリック
　　↓
現れた色のサンプルの中にある着色したい色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式の設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
AA45セルを右クリック
　　↓
現れた選択肢の中にある[コピー]をクリック
　　↓
色を着ける可能性のある全てのセル範囲（AA45～AA51）をまとめて範囲選択
　　↓
選択範囲を示す黒い太枠の内側にカーソルを合わせてから、マウスを右クリック
　　↓
現れた選択肢の中にある[形式を選択して貼付け]をクリック
　　↓
現れた「形式を選択して貼付け」ダイアログボックスの中にある[書式]と記されている箇所をクリックして、チェックを入れる
　　↓
「形式を選択して貼付け」ダイアログボックスの[OK]ボタンをクリック

或いは

AA45～AA51のセル範囲をまとめて範囲選択
　　↓
「メニュー」バーの[書式]ボタンをクリック
　　↓
現れた選択肢の中にある[条件付き書式]をクリック
　　↓
現れた「条件付き書式の設定」ダイアログボックスの左端の欄をクリック
　　↓
現れた選択肢の中にある「数式が」をクリック
　　↓
「条件付き書式の設定」ダイアログボックスの左から2番目の欄に

=SUMPRODUCT(ISNUMBER(FIND(MID($B$51,ROW($1:$3),1),AA45))*1)=3

と入力
　　↓
「条件付き書式の設定」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[パターン]タブをクリック
　　↓
現れた色のサンプルの中にある着色したい色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式の設定」ダイアログボックスの[追加]ボタンをクリック
　　↓
「条件付き書式の設定」ダイアログボックスの「条件2(2)」内の左端の欄をクリック
　　↓
現れた選択肢の中にある「数式が」をクリック
　　↓
「条件付き書式の設定」ダイアログボックスの「条件2(2)」内の左から2番目の欄に

=SUMPRODUCT(ISNUMBER(FIND(MID($B$51,ROW($1:$3),1),AA45))*1)=2

と入力
　　↓
「条件付き書式の設定」ダイアログボックスの「条件2(2)」内の[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[パターン]タブをクリック
　　↓
現れた色のサンプルの中にある着色したい色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式の設定」ダイアログボックスの[OK]ボタンをクリック

【参考URL】
　よねさんのWordとExcelの小部屋 > Excel2003 基本講座 > 条件付き書式
　　http://www.eurus.dti.ne.jp/yoneyama/Excel/jyo-syo.html

お礼

ご回答ありがとうございます。

エクセルのバージョンにつきましては、他の方からのご指摘もあり、明記するようにいたします。

ありがとうございます。

今回はご丁寧に教えていただき、ありがとうございます。

おかげさまで目的の結果にたどり着くことが出来ました。

ありがとうございます！

回答No.8

kagakusuki

回答:No.3です。

>セルは45～51セルで、数字は重複しません。

>そして、桁数は今のところ変わる予定がありません。

　それでしたらAA45～AA51のセル範囲をまとめて選択してから、セル条件付き書式で設定する2つの条件の数式を、それぞれ

=SUMPRODUCT(ISNUMBER(FIND(MID($B$51,ROW($1:$3),1),AA45))*1)=2

と

=SUMPRODUCT(ISNUMBER(FIND(MID($B$51,ROW($1:$3),1),AA45))*1)=3

として下さい。
　或いは、もし一々、どのセル（例えばAA45～AA51等の内のどのセル）に入力する数式なのかを考えるのが面倒な場合には、次の様な数式とする手もあります。

=SUMPRODUCT(ISNUMBER(FIND(MID($B$51,ROW($1:$3),1),INDIRECT("RC",)))*1)=2

と

=SUMPRODUCT(ISNUMBER(FIND(MID($B$51,ROW($1:$3),1),INDIRECT("RC",)))*1)=3

　因みに、

>　但し、この方法では、B51に入力されている数字が例えば「515」の様に数字に重複がある場合、「123467890」の様な「5」しか共通する数字が無いものに対しても、元の「515」の1番目の文字である「5」と、2番目の文字である「5」が、どちらも「123467890」に含まれているために、「2つ含まれている」と判定されます。

という点は、ここまでの他の回答者の方々の方法でも同様です。

回答No.7

MarcoRossiItaly

No.6 さん、ご説明くださり、ありがとうございました。

＞その右のAF1に…（数式）…を入れたところ、０と表示されてしまいます。

セル参照のミスが怪しいと思って、質問文を見直してみました。すると、「846」が入力されている箇所は、B7:C7 のセル範囲を結合しているようですね。

ごめんなさい、錯覚しました。この数式中に 3 箇所ある「C$7」を全て「B$7」に書き換えれば、たぶん正常に機能するのではないかと思います。お試しください。

回答No.6

MackyNo1

>ちなみに使っているエクセルのバージョンは2002です。
>ですから、AA1に条件付き書式を与えようとしてもダイアログに数式を入れる所が無い様に見えます。

Excel2003以前の古いバージョンのエクセルを使用されている場合は、「書式」「条件付き書式」で「セルの値が」の部分の右の▼をクリックして「数式が」にして私の提示した数式を入力してみてください。

＃このように、エクセルの操作に慣れていない場合は、ご使用のバージョンを明示することが重要なことがお分かりになったと思います（回答者に無駄な回答をさせることになります）。

お礼

ありがとうございます。

そうですね、せっかく時間を割いて頂いたのに無駄にしてはもったいないですね、
これからはバージョン明記するようにします。

ありがとうございます！！

回答No.5

MarcoRossiItaly

AB1　=mid($AA1,column()-column($AA1),1)
AF1　=countif(ab1:ae1,mid(C$7,1,1))+countif(ab1:ae1,mid(C$7,2,1))+countif(ab1:ae1,mid(C$7,3,1))

と入力。 AB1 セルをコピーし、 AB1:AE6 のセル範囲に貼り付け。 AA1:AA6 を選択し、 AA1 がアクティブな状態（選択している範囲のセルが青くなっている中で 1 つだけ色が異なる状態）で、条件付き書式のダイアログを起動。条件式として次のとおり入力。

●「2」と「3」で異なる色（ 2 式により 2 条件として設定）

=af1=2
=af1=3

●「2」でも「3」でも同じ色（ 1 式により 1 条件として設定）

=(af1=2)+(af1=3)

作業列を使っていいならたくさん使ったほうが、数式は当然、簡単なものに。

補足

ご回答ありがとうございます。

AA5の2456を　AB１-AE１に分割表示するところまでは出来ましたが、その右のAF1に
=countif(ab1:ae1,mid(C$7,1,1))+countif(ab1:ae1,mid(C$7,2,1))+countif(ab1:ae1,mid(C$7,3,1))

を入れたところ、０と表示されてしまいます。

これは何か原因があるのでしょうか？

ちなみに使っているエクセルのバージョンは2002です。

ですから、AA1に条件付き書式を与えようとしてもダイアログに数式を入れる所が無い様に見えます。

回答No.3

kagakusuki

確認したいのですが、B51に入力されている数字は3桁とは限らず、桁数が変化する事もあるのでしょうか？
　又、B51に入力されている数字は、「777」や「121」の様に、数字が重複している事はあり得ないと考えて宜しいのでしょうか？
　もし、そうであれば、条件付き書式において条件を2つ使用して、次の様な条件において色を変化させる様に、条件付き書式の設定を行われると良いと思います。
　もし、条件付き書式の設定方法が判らない場合には、この解答欄の中に表示されている「補足する」ボタンをクリックすると現れる補足欄を使用して、その旨を御知らせ下さ。
　又、その際には、Excelのバージョンによって設定方法が異なる場合がありますので、御使いのExcelのバージョンがどれであるのかに関しましても、必ず御知らせ願います。

●[数式が]において

=SUMPRODUCT(ISNUMBER(FIND(MID($B$51,ROW(INDIRECT("Z1:Z"&LEN($B$51))),1),$AA1))*1)=2

●[数式が]において

=SUMPRODUCT(ISNUMBER(FIND(MID($B$51,ROW(INDIRECT("Z1:Z"&LEN($B$51))),1),$AA1))*1)=3

　但し、この方法では、B51に入力されている数字が例えば「515」の様に数字に重複がある場合、「123467890」の様な「5」しか共通する数字が無いものに対しても、元の「515」の1番目の文字である「5」と、2番目の文字である「5」が、どちらも「123467890」に含まれているために、「2つ含まれている」と判定されます。

補足

説明がわかりにくくてすいません。

セルは45～51セルで、数字は重複しません。

そして、桁数は今のところ変わる予定がありません。

ご回答ありがとうございます！

回答No.2

MackyNo1

説明文のセル番地と添付画像のレイアウトが違うように思うのですが、説明文通りだとすると、AA４５　セルからAA50セルを選択して、条件付き書式で「数式を使って・・・」を選択し、以下の数式を入力してご希望の塗りつぶしをしてください。

=COUNT(FIND(MID($BC$51,ROW($1:$3),1),AA45))=2

同様に3つ一致の場合の数式を「追加」して書式設定してください。

=COUNT(FIND(MID($BC$51,ROW($1:$3),1),AA45))=3

＃ご使用のエクセルのバージョンが不明でしたので、一般的な回答をしてみましたが、Officeソフトはバージョンによって使用できる機能や操作方法が大きく異なりますので、質問の際にはか必ずバージョンを明記するようにしましょう。

お礼

ご回答ありがとうございます。

まさにこの通りの結果を求めていました。

一発で回答を頂きありがとうござぃます。

回答No.1

masatsan

それぞれのセルの文字列（数値なら文字列に変換して）にして
　Mid関数でそれぞれを端から比較する。
　文字が全部含まれていたら色を変える
色を変えたいセル.Interior.Color = RGB(255 0, 0) ' 背景を赤色