エクセルで同じ構成で並ぶ４桁数字を認識する方法は？

質問

エクセルでＡ1セル以下に順列でない４桁の数字が並んでいるとします。Ｂ1セル以下にもランダムで順列でない数字が並んだ時、Ａ列と同じ構成で並んでいる４桁数字のセルを塗りつぶすような条件付き書式設定をしたいのですが、どのようにすればいいのでしょうか？

　　　　Ａ　　　　　　　　　　Ｂ
1　　2588　　　　　　　　1458　　
2　　2336　　　　　　　　5275
3　　6510　　　　　　　　4892
4　　7525（←色塗）　　9554
5　　3992　　　　　　　　7635
6　　7329　　　　　　　　5440
7　　4054（←色塗）　　1859

上記のようなイメージなのですが・・・、宜しくお願いいたします。

回答No.4

mu2011 · ベストアンサー

NO3です。
Ｂ列の範囲が誤っていましたので訂正します。
=SUMPRODUCT(((SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($B$1:$B$100,MID(A1,1,1),"0"),MID(A1,2,1),"0"),MID(A1,3,1),"0"),MID(A1,4,1),"0"))="0000")*1)

お礼

やろうと思っていたことができました！ありがとうございました！！

回答No.8

sige1701

mu2011 さんの回答そのものですが

=SUMPRODUCT(((SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($B$1:$B$100,MID(A1,1,1),"A",1),MID(A1,2,1),"A",1),MID(A1,3,1),"A",1),MID(A1,4,1),"A",1))="AAAA")*1)

SUBSTITUTE関数の第４引数を付けました
(重複する数値がある場合を考慮して)

たとえばB列に2222があった場合A列に2を含む数字が該当　を防ぐため

お礼

感謝いたします、ありがとうございます。

回答No.7

imogasi

>順列でない
>同じ構成で並んでいる
なんて難しい表現で質問しているがそんな必要がないだろう。
A,B列に、両列で
（１）数字で
（２）４桁限定
のデータがあるとき、B列でA列と同じ数字があれば、B列に色をつけたい」で、住むのでは。
ー
エクセルは、抜き出し問題は不得意なのだ。Googleででも「imogasi方式」で照会すれば、既に出ていると似た、関数を組み合わせた式の長い回答がある。しがし理解は難しい。
ーー
幸い本件は、同じデータのあるセルを抜き出さなくても、A列に同じものが１件（１セル）以上あれば、色を着ける、で良いのだろう。
例データ
A列　　B列
1 7
2 3
3 4
4 9
5 11
6 23
Bretuwo範囲指定して
書式ー条件付書式ー数式が　で数式を
=COUNTIF($A$1:$A$6,B1)>=1
ト入れてセルの色を書式で指定。
B列の　３，４のセルに色が付いた。

お礼

ありがとうございます。感謝いたします！

回答No.6

KURUMITO

分かり易い方法としては作業列を使います。
C1セルには次の式を入力してG1セルまで横にオートフィルドラッグコピーした後で下方にもオートフィルドラッグコピーします。

=IF($A1="","",IF(COLUMN(A1)<=4,MID($A1,COLUMN(A1),1)*1,IF(COLUMN(A1)=5,VALUE(SMALL($C1:$F1,1)&SMALL($C1:$F1,2)&SMALL($C1:$F1,3)&SMALL($C1:$F1,4)),"")))

H1セルには次の式を入力してL1セルまでオートフィルドラッグコピーした後で下方にもオートフィルドラッグコピーします。

=IF($B1="","",IF(COLUMN(A1)<=4,MID($B1,COLUMN(A1),1)*1,IF(COLUMN(A1)=5,VALUE(SMALL($H1:$K1,1)&SMALL($H1:$K1,2)&SMALL($H1:$K1,3)&SMALL($H1:$K1,4)),"")))

その後に条件付き書式の設定でA列を選択した上で数式には次の式を入力して、書式では塗りつぶしのタブから色を指定します。

=COUNTIF(L:L,G1)>0

お礼

出来ました！ありがとうございます。

回答No.5

mu2011

NO4です。
変換数の「０」に問題がありましたので再度改訂します。
=SUMPRODUCT(((SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($B$1:$B$100,MID(A1,1,1),"A"),MID(A1,2,1),"A"),MID(A1,3,1),"A"),MID(A1,4,1),"A"))="AAAA")*1)

お礼

やろうと思っていたことができました！ありがとうございました！！

回答No.3

mu2011

一例です。
仮にＢ列範囲をB1:B100として、A列範囲を選択→条件付き書式設定の「数式～」を選択、
数式欄に=SUMPRODUCT(((SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($B$1:$B$7,MID(A1,1,1),"0"),MID(A1,2,1),"0"),MID(A1,3,1),"0"),MID(A1,4,1),"0"))="0000")*1)

お礼

やろうと思っていたことができました！ありがとうございました！！

回答No.2

nobu_suzuki1

ANo1です。
一応イメージ図をはっておきますので参考にしてみてください。
OKWaveの仕様上画像サイズが非常に小さいのであまり役に立たないかもしれませんが・・・。

お礼

参考になります、ありがとうございます。

回答No.1

nobu_suzuki1

作業用セルを大量に使用するスマートではない形でしかできませんでしたが一応、解決策を書いてみます。
D1～G1にMid関数などとValue関数を利用してAの各桁を分離します。（Modなどで代用でも可ですが）
同様にH1～K1セルを利用してBの各桁を分離。
Lセル～から0～9の各数字を並べてその下にcountif関数で対応する数字の個数を計算
0～9の個数をA,B共に求めたらCONCATENATEで結合（A4=7525の場合　0010020100となるようにする）
Aのそれぞれの値に対してVlookupを使ってBのCONCATEATEした値から同一の値があるか検索して、あれば色を変える。
これで一応対応可能でした。

すべての数式を書くと非常に長くなってしまうので概要だけ説明いたしました。
分かりにくくてすいません。

なお、VBAを使えばよりスマートにできますが今回はVBA無しでのやり方のほうがよいかな？と思いましたので上記のようにしました。

お礼

参考になります、ありがとうございます。