Excel関数式で対応出来るか教えて下さい

質問

例えば
0001　0125　2532　3286　8545　という数字がA列の１行目からB列１行目・・・というふうに、
順番に一行に入っていたとします。
（次の行も同様に、何かしらの数字が入っています。）

ひとつのセルに入った4桁の数字の下2桁が、次のセルに入っている数字の上2桁と一致していないとエラーが出るような関数式を探しています。

例では最後のセルに入っている4桁の8545の85がひとつ前のセルに入っている数字3286の下2桁86と一致していないのでエラーとなります。

数字の4桁が5桁になって、下3桁が上3桁と一致しているか確認、下2桁が上2桁と一致しているか確認というケースが混在する時もあります。

チェックする数字が大量にあるため、フィルターでチェックするのには気が遠くなります。

お知恵を拝借出来ますよう、宜しくお願い致しますm(__)m

回答No.7

MackyNo1 · ベストアンサー

同じ行に4ケタと5ケタの数字が混ざっていて、そのセルと右側のセルがいずれも5ケタ以外は2ケタ分だけの比較という条件なら、以下の数式で判定できます。
ただし、G列まで入力されているデータが最も長い行で、1列空けたI列に入力する場合の数式です。
不一致の場合はFalseが表示されます。

=SUMPRODUCT((RIGHT(TEXT(A1:G1,"0000"),2+(LEN(TEXT(A1:G1,"0000"))=5)*(LEN(TEXT(B1:H1,"0000"))=5))=LEFT(TEXT(B1:H1,"0000"),2+(LEN(TEXT(A1:G1,"0000"))=5)*(LEN(TEXT(B1:H1,"0000"))=5)))*1)=COLUMNS(A1:G1)-1

お礼

有難うございます。

試してみたのですが、エラーがあっても、すべてTRUEになってしまって、
上手く処理出来ませんでした。

お手数をお掛けしたのに、理解できず申し訳ないです。
ごめんなさいm(__)m

もう少し頑張ってみます。

補足

何度もすみませんm(__)m

教えて頂いた式を一部間違えて入力していました。

再トライして、上手く出来ました。ありがとうございましたm(__)m
助かりましたm(__)m

回答No.6

mu2011

NO4です。
Excel2000でも色付け出来無い事はないのでよかったらお試しください。
仮にA1:H1範囲とします。
■B1:H1を選択→書式→条件付き書式→「数式が」を選択、数式欄に=IF(LEN(A2)>4,(LEN(B2)>0)*(RIGHT(A2,3)<>LEFT(B2,3)),(LEN(B2)>0)*(RIGHT(A2,2)<>LEFT(B2,2)))を貼り付け→書式→パターンタブで任意の色を選択→ＯＫ
　　数式は、空白部分を考慮するように一部改訂しています。

お礼

ごめんさない。
先ほど、Excel2000では色が付かないと補足させて頂いた後で、
もう一度試しましたら色を付ける事が出来ました。

ただ、私のやり方が悪いのだと思うのですが、
結果が上手く出ません。
エラーでないのに、色が付かなかったりします。

お陰様で、今回はNo.５さんのご回答で解決出来ましたが、
色が付く方法は、結果がとても分かりやすいので、
もう少し教えて頂いた方法でも頑張ってみます。

有難うございましたm(__)m

回答No.5

imogasi

例データ　Sheet1のA1:E2
1 125 2532 3286 8545
9921 2145 4656 5643 4311
例はこういう風にデータ例を（シートの見た目のように）書くことをすすめる。
データ例は２－３行ぐらい書くべきと思う。
５桁の例も混じらせるとか。
ーー
Sheet2にA1に　=IF(RIGHT(TEXT(Sheet1!A1,"0000"),2)=LEFT(TEXT(Sheet1!B1,"0000"),2),"","ERR")
と入れて、E1まで式を複写。
B1:E1の式を下方向に複写。
ーー
結果　Sheet２　－は空白セルを示す。
- - - - ERR
- - ERR
ーー
＞数字の4桁が5桁になって、下3桁が上3桁と一致しているか確認、下2桁が上2桁と一致しているか確認というケースが混在する時もあります。
　　長さが５桁かどうか（LEN関数）IFで聞いて、４桁（以下？）の場合は上記の式、５桁の場合は、同じような式を少し変えて作ってみて。
式が長くなるが、上記の延長線の話なので（式は略）。

お礼

有難うございました。

お陰様で解決できましたm(__)m

質問文に不備が多く、申し訳ありませんでした。

回答No.4

mu2011

>上2桁と一致していないとエラーが出るような関数式
　⇒対象のセルを色付けする方法は如何でしょうか。（エクセル２００３以下での操作手順につき、相違する場合はｗｅｂ検索して下さい）
　　Ｂ列から対象列まで選択→条件付き書式設定→「数式が」を選択、
　　数式欄に=IF(LEN(A1)>4,RIGHT(A1,3)<>LEFT(B1,3),RIGHT(A1,2)<>LEFT(B1,2))、
　　書式→パターンタブで色を選択→ＯＫ

補足

ご回答有難うございます。

やり方を理解出来ていないのかもしれませんが、
私の使っているソフトがExcel2000と古いため、
対象のセルを自動で色付けは出来ないようです。

間違っていたらごめんなさいm(__)m

回答No.3

keithin

回答２の例２，例３の式で，うっかり直し忘れがありました。

例２の訂正例：
=IF(SUMPRODUCT(--(RIGHT(TEXT(A1:D1,"0000"),2)=LEFT(TEXT(B1:E1,"0000"),2))),"ERROR","OK")

例３の訂正例：文字列で記入されていて，３桁と３桁の場合
=IF(SUMPRODUCT(ABS(RIGHT(A1:D1,3)-LEFT(B1:E1,3))),"ERROR","OK")
をEnterで入力。もちろん２桁に直して使っても問題ありません。

失礼しました。

＃わざといろんな計算パターンを提示しています。興味があれば色々分析して，趣味に応じて使ってください。

補足

説明が悪くて申し訳ありません。

・まず、0001は数値の１を「表示形式で0001」にしています
・4桁と5桁の組合せはランダムです。自動で判断できる材料はありません。
・下記にように、データが入っている最後の列は行によって違います。

以下のイメージです。分かりづらくてすみません。

　　　　 A列・・・
1行目 0001 0157 57602 60233 3321 　21233 　23354
2行目 0001 0138 3842 4253 53112　11214
3行目 0012 1257 5763 6324
4行目 0001 0157 57602 60233 3321 　2125 　2523

回答No.2

keithin

＞例えば
＞0001

このたとえばの言葉通りに「文字列で0001」と記入してあるのか，それとも実際には数字の１が入っていて表示形式で「0001」と表示させているだけなのかによっても，数式は当然変わります。正しい具体的な情報提供を心がけてください。

例１：「文字列で’0001のように」記入してある場合
F1セルに
=IF(AND(RIGHT(A1:D1,2)=LEFT(B1:E1,2)),"OK","ERROR")
と記入し，かならず「コントロールキーとシフトキーを押しながら」Enterして入力する。

例２：数値の１を「表示形式で0001」にしている場合
F1セルに
=IF(SUMPRODUCT(RIGHT(TEXT(A1:D1,"0000"),2)-LEFT(TEXT(B1:E1,"0000"),2)),"ERROR","OK")
と記入し，Enterで入力する

例３：文字列で記入してあり，3桁と3桁を比較する場合
=IF(SUMPRODUCT(RIGHT(A1:D1,3)-LEFT(B1:E1,3)),"ERROR","OK")
をEnterで入力。

２桁なのか３桁なのか，何がどうならどうしたいのか詳細が不明なので，あなたが目で見て２や３と数式を直して入れる事にします。
何か自動で判断できる材料があるなら，その旨を数式に組み込んで一層高度化することも可能です。

回答No.1

某HN クロメート(Chromate)（@CoalTar）

細かな条件が分からないのでたたき台
A1:E1セルに文字列として数字が入力されているとして
F1セルに
=AND(RIGHT(A1:D1,LEN(A1)-2)=LEFT(B1:E1,LEN(A1)-2))
[Ctrl]+[Shft] ＋[Enter] で確定、配列数式。{ }で挟まれる
エラーではなく FALSEになりますが。

補足

早速にご回答頂き有難うございます。

文字列で入力していなかった為、上記式では上手く反映しませんでした。

説明が悪くてすみませんでしたm(__)m