[[20140822090340]]　『2つのファイルの重複と差分を埋めたい』（エクセル初心者）　ページの最後に飛ぶ

『2つのファイルの重複と差分を埋めたい』（エクセル初心者）

はじめまして
業務でデータの整理をしないとならなくなりましたが大量のデータに手作業で限界を
感じています。
効率的な方法をアドバスしていただけないでしょうか。

関数でもVBAでも良いので用方法があれば教えて下さい。
2つのファイルを内容を比較と更新をしたいのです。
Aファイルが原本です。
Bファイルは今後新原本となるファイルです。
A列には共通の現在の新コードが文字列として数字で入っています。
AファイルのA列の商品コード(仮にA2）がBファイルのA列のどこか(仮にA2）にあった場合に、該当のD列
（D2)のﾊﾞｰｺｰﾄﾞ欄にAファイルのバーコードの数値を転記したいのですが良い方法はありませんか。
A列の商品コードは重複している場合もありますが、商品コードは正しいので、そのまま、バーコード数を
入れたいのです。
現在は手作業でAファイルの商品コードをBファイルで検索し、あった場合、Dの列を書き換える作業をして
います。
このファイルが600くらいあり簡略化したいのですが方法が分かりません。
良い方法があれば教えて下さい。

宜しくお願い致します。

Aファイル

	A    　 B    　 C 　 　　  D 　 　　　 E　　　　・・・・・AG1
１ 商品コード　品名　製造日　　　期限　　　ﾊﾞｰｺｰﾄﾞ数　
２　24525　　りんご　2014/2/3　2014/5/6　　154526422
３　12456　　みかん　2014/2/2　2014/6/1　　254685622
４　254　　　もも　　2014/2/5　2014/6/5　　145254556
・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・
・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・
4680 ・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・

Bファイル

	A    　 B    　 C 　 　　  D 　 　　　 E　　　　・・・・・AH1
１ 商品コード　品名　出荷先　　ﾊﾞｰｺｰﾄﾞ数　　新商品コード
２　12456　　みかん　東京　　　　　　　　　　A14524455
３　3545　　　電球　 横浜　　　　　　　　　　B14555884A
４　2554　　　CD　　 栃木　　 　　　　　   　A142251122　
・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・
・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・
・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・

＜使用 Excel：Excel2003、使用 OS：WindowsXP ＞

 VLOOKUP関数を使ってみるのはどうですか？

 ライブラリ「VLOOKUP (e1tw)」
http://www.excel.studio-kazu.jp/lib/e1tw/e1tw.html
 　
（HANA） 2014/08/22(金) 10:20

HANA様
貴重な情報ありがとうございます。
拝見しました。VLOOKUPのテーブルが別のシートにある場合 [sheet]の代わりにブックを指定すれば
良いのですね。
参考にして試してみます。

（エクセル初心者） 2014/08/22(金) 10:48

すいませんもう少し力を貸して下さい。
VLOOKUPで出来るものと出来ないものが有ります。

これを試す前から気になっていたのですがやはりここでも引っ掛かっています・
Aファイルの商品コードとBファイルの商品コードの書式が違うのでうまくいかないようです。
共に文字列にしているのですが、Aファイルの方は、セルの左上に緑色の△が付きます。
Bファイルには付いていませんが文字列です。
Bファイルの文字列のセルで緑△が付いていないセルをクリックしてエンターをすると緑△が付きます。
この時には先のVLOOKUPでもちゃんと値を返してくれます。

見た目には数字の前には　’　ついていないのですが、この辺りが問題になっているみたいです。

仮に同じシートのA1とB1に同じ数字を入れて文字列にして、緑△を付けたA1　緑△の無いB1　ともに文字列ですが、=IF(A1=B1,"A","B")　とするとBが返ってきます。
つまり合致してないと言う事になります。
しかし、=IF(EXACT(A1,B1),"A","B")ならばAとなります。
何千行も有るセルをしかも複数のシートやファイルも多数ありすべてクリックする訳にない来ません。

良い方法は無いでしょうか。

宜しくお願い致します。

（エクセル初心者） 2014/08/22(金) 12:24

過去ログで、VLOOKUPとEXACT併用をみましたが、[[20070427203222]]　『不思議なVLOOKUP』が良くわかりませんでした。
これを活用したら判別できる気がするのです
テスト用に1枚のシートに必要な行だけ移動して試した時のVLOOKUPの式は
＝VLOOKUP（A2,H2;j25,3FALSE)　です。

A列が緑△が付いた文字列数字　J列が緑△が付かない文字列数字　J列が新バーコード数字になります。

宜しくお願い致します。

（エクセル初心者） 2014/08/22(金) 12:34

たびたびで申し訳りません。
、[[20070427203222]]　『不思議なVLOOKUP』はうまく出来ていたようです。
ただし、やはりセルの上の緑△(エクセル2003　文字列として保存されている数値）が何か関係しているようですが対処方法はありますか。

宜しくお願い致します。

（エクセル初心者） 2014/08/22(金) 12:54

不思議なVLOOKUPの話の延長ではありませんが、アポストロフィ付の文字列からアポストロフィを取り除く
方法が紹介されています。

↓
http://officetanaka.net/excel/function/tips/tips15.htm

1.任意のセルに数字の0を入力します。
2.その0をコピーします。
3.「'」を削除したいセルを選択します。
4.[編集]-[形式を選択して貼り付け]を実行します。
5.[演算]グループの[加算]チェックボックスをオンにします。
6.[OK]ボタンをクリックします。
7.Escキーを押してカットコピーモードを解除します。

すべてのファイルの商品コード内のアポストロフィーをセル単位で取り除くよりは早いですがファイルが
600もあると気が遠くなりそうですね。

まぁデータをクリーンアップするいい機会と捉えれば・・・(^^;

（ryopo^2） 2014/08/22(金) 13:05

 Aファイルの方が確実に文字列になっているなら
 今、検索値に　B2　と入れていると思いますが
 B2&"" にしてみるとどうですか？
 　
（HANA） 2014/08/22(金) 13:09

ryopo^2様

ありがとうございます。
そうなんです、断念していて、なんとか出来ないか昨夜から模索中でした。
このデータ修正後違う機械に取り込むため書式を変えられずにいます。
このまま出来る方法は無いものでしょうか。
書式で数値に戻せば解決もするのですが、その後文字列に変換しても’つかずに困っています。

過去ログ探しているところでした。

よい方法が有ればご教授お願い致します。
（エクセル初心者） 2014/08/22(金) 13:41

HANA様
ありがとうございます。
すいません。ご教授頂いたのに理解出来ませんでした。

Aファイルをエクセルの機能で検索している、24525を　24525&""　として検索すると言う事でしょうか。
もしそうなら見つかりませんでしたと表示されます。

（エクセル初心者） 2014/08/22(金) 13:49

ありがとうございます。

出来ました。=VALUEで指定すればVLOOKUPと併用でできました。
ありがとうございました。

感謝です。

（エクセル初心者） 2014/08/22(金) 14:04

 解決された様ですが、書きかけだったので載せておきます。

 使っている数式を載せてもらえると良いのですが。

 =VLOOKUP(B2,Aブックの範囲,○,FALSE)
 って式だったら
 =VLOOKUP(B2&"",Aブックの範囲,○,FALSE)
 と言う事なのですが。

 Aブックの範囲に、数値と文字列が混ざっているなら。。。二段構えかな？

 ＞このデータ修正後違う機械に取り込むため書式を変えられずにいます。 
 今回更新するのはBブックですよね？

 Bブックは「文字列」と言っているけど実際は値。
 Aブックは本当に文字列。
 AブックのA列を変更するのは関係なさそうに思いますが？

 ＞=VALUEで指定すれば
 Bブックの方が文字列で、Aブックの方が数値だったのかな。。。？
 　 　
（HANA） 2014/08/22(金) 14:07

HANA様
ありがとうございます。
初心者のため説明が上手く出来ておらず申し訳ありません。
それでもご丁寧に教えて頂き感謝です。

おそらくどちらも文字列です。
数値にすると、セルの右寄せに数値が並び、文字列にすると数値は左寄せに並ぶのでＡのファイルもＢのファイルも
文字列で間違いないと思います。

エクセル2007でも試しましたが、A1に2045と入力すると数値として右寄せに並び、そのセルを、プロパティー
から文字列にすると左寄せになります。
ただしこの時点では、左上の緑▽マークは出ません。
この状態では、先の、=IF(A1=B1,"A","B")　だとBと出たので同じ文字列と認識していない様に思えました。

このセルを再度クリックし、入力出来る状態でエンターすると緑▽が出ます。

この時は、=IF(A1=B1,"A","B")　Aとでました。
数字か文字か混在？書式は変わっているが反映しない？ので違う値だと認識していると思い=VALUEで括って
みたら緑▽が有っても無くても、両方に緑▽が有っても結果が変わらなくなりました。

Ｂファイルの商品コードが緑▽付きの文字列で数値でした。
Ａファイルが商品コードは緑▽なし、プロパディー上は文字列状の数値でした。

=VLOOKUP(B2&"",Aブックの範囲,○,FALSE) と言う事なのですが。試すと、#NAと表示されました。

Ａファイルが商品コードは緑▽なしを緑▽有りの状態にするときちんとバーコード数が出ていました。

奥が深すぎてなかなか理解出来ず毎回苦しんでいます。

以上、宜しくお願い致します。

（エクセル初心者） 2014/08/22(金) 14:34

 ＞エクセル2007でも試しましたが、A1に2045と入力すると数値として右寄せに並び、そのセルを、プロパティー 
 ＞から文字列にすると左寄せになります。 
 ＞ただしこの時点では、左上の緑▽マークは出ません。 
 その時点ではまだ数値だからです。

 TYPE関数で確認してみて下さい。

 ＞=VALUEで指定すれば
 ってのは、結局どんな式にしたのですか？
 　
（HANA） 2014/08/22(金) 14:51

HANA様
遅くなりました。
どうもうまく動いてないみたいで模索していました。(ぬか喜びだったようです）

TYPE関数で確認してみて下さい。確認しました。
緑▽では2が返って来て、緑▽が無いものは１が返ってきました。
書式上文字列だが、実際は数値だったと言う事ですね。

テストで並べ替えながら少ない数で検証していたので、最初の配置数が合っていない事をご理解ください。
=VLOOKUP(VALUE($A2),IF({TRUE,FALSE},VALUE($H$2:$H$11),VALUE($J$2:$J$11)),2,FALSE)
先にＨ列を文字に変換する必要が有るの解りましたの先に続けて考えてみます。

（エクセル初心者） 2014/08/22(金) 15:36

 ＞Bファイルの文字列のセルで緑△が付いていないセルをクリックしてエンターをすると緑△が付きます。 
 ＞この時には先のVLOOKUPでもちゃんと値を返してくれます。 
 この時の数式は、どうなっていましたか？

 簡単なテストを行ってみて下さい。

 A列には「'」を付けて数値を入力します。
 B列には適当に何か文字を
 D列は、そのまま数値を入力します。
	↓緑▽有り					
 	[A]	[B]	[C]	[D]	[E]	[F]
[1]	1	A		1	#N/A	A
[2]	2	B		2	#N/A	B
[3]	3	C		3	#N/A	C
				↑緑▽無し		
 E1=VLOOKUP(D1,$A$1:$B$3,2,FALSE)
 F1=VLOOKUP(D1&"",$A$1:$B$3,2,FALSE)

 そちらではどの様になりますか？

（HANA） 2014/08/22(金) 16:01

HANA様
色々ありがとうございます。
理解不足でお手間を取らせています。

E1=VLOOKUP(D1,$A$1:$B$3,2,FALSE) こちらは、d列の緑▽が有りものはきちんと出て、緑▽が無いものは#N/Aが出ます。

F1=VLOOKUP(D1&"",$A$1:$B$3,2,FALSE) こちらは大丈夫です。思惑通りの動きになっています。
d列のコードに該当する物が無い時だけ#N/A、A列とＤ列に該当がいるがＢ列に値が無く空白のセルが有った時に0が出ていました。

F1の式を加工したら#N/Aと0の処理が出来そうなので、トライしてみます。

（エクセル初心者） 2014/08/22(金) 17:25

HANA様、ryopo^2様

色々ご教授ありがとうございました。
こんな感じで修正しました。
=IF(ISERROR(VLOOKUP(D2&"",$A$1:$B$11,2,FALSE)),"",VLOOKUP(D2&"",$A$1:$B$11,2,FALSE))&""
これでもう少し検証を進めて、その後元々の2つのファイルできちんと動くか確認します。

大変勉強になりました。
感謝感謝です。
（エクセル初心者） 2014/08/22(金) 17:59

 B列でなく、D列だったんですね。

 「&""」が必要なのは　VLOOKUP関数がエラーでないとき
 VLOOKUP(D2&"",$A$1:$B$11,2,FALSE)が計算され、その結果に入力がなかった時ですよね？

 つける場所をもう一度考えてみられてはどうですか？

 現在の式でもご希望の結果になると思いますが
 VLOOKUP関数がエラーの時、「""」が返されますので
 全体として「""&""」になります。

 それから「&""」を付けると　未入力の場合「0」が表示されるのは回避できますが
 文字になってしまいます。
 　検索値を　D2&""　にして、文字にしたのと同じです。
 そのあたりは大丈夫でしょうか？
 　
（HANA） 2014/08/22(金) 22:17

HANAさま
おはようございます。
昨日はありがとうございました。

検索値を　D2&""　にして、文字にしたのと同じです。そのあたりは大丈夫でしょうか？大丈夫です。
逆にこのレスを見て再度結果を見ていたら
=IF(ISERROR(VLOOKUP(D2&"",$A$1:$B$11,2,FALSE)),"",VLOOKUP(D2&"",$A$1:$B$11,2,FALSE))&"" を入れた
セルの結果に緑▽がないので数字として認識していると言う事ですよね？
該当のセルをType関数で見ると２が返ってきているので文字として見ているのかなっと思っています。
逆に表示されている数字は文字として認識して欲しいので、良いのですがこの辺りが少し気になっています。

また、VLOOKUPで計算されたセルを手動でコピーを取り、別のセルに形式を選択して貼り付けにて「値」だ
けを張り付けると、VLOOKUPで処理された数字が表示され、セルの上には緑▽が付きますので、最悪違うセルに値だけ貼り付けて、最後に、本来のセルに値だけを貼り付けようと考えています。

エラーも０も空白で大丈夫です。
逆に、エラーや０が付いていると別の機械の専用ソフトに取り込めなくなるので、取り込んだ後に
機械側で空白の物を手入力で再設定しようと考えています。

（エクセル初心者） 2014/08/23(土) 07:33

 ＞セルの結果に緑▽がないので数字として認識していると言う事ですよね？ 
 「数字」と書いてありますが、「数値として認識しているか？」と言う事であれば、違うと思います。
 ご自身でTYPE関数で確認された様に
 ＞該当のセルをType関数で見ると２が返ってきているので文字として見ているのかなっと思っています。 
 返される値は、~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ですね。

 緑▽をそこまで気にするのなら、どんな時に表示されるか
 調べておかれると良いと思います。
 　　私は調べた事が無いので、そういうページがあるのかどうかもわかりませんが。

 経験上のお話になりますが、緑▽のセルをアクティブにすると <!>が表示されますよね？
 それをクリックした時表示されるメニューの一番下に
 [エラーチェックオプション]がありますので、クリックして下さい。

 「Excelのオプション」が開き、一番下に　エラーチェックルール　が有ります。
 今回、緑▽が表示されているのは、左側の下から２番目にある
 　文字列形式の数値、またはアポストロフィで始まる数値(H)
 のルールに従っていると思います。

 数式が入っているセルは　計算結果が返されているだけで　数値が入っている訳ではないので
 このエラーチェックの対象外になるのではないでしょうか。

 ＞エラーも０も空白で大丈夫です。 
 と言う意味ではなく
 =IF(ISERROR(VLOOKUP(D2&"",$A$1:$B$11,2,FALSE)),"",VLOOKUP(D2&"",$A$1:$B$11,2,FALSE)&"")
 の方がすっきりしない？
 と言うつもりでしたが。
 　 
（HANA） 2014/08/23(土) 15:14

コメント返信：

[ 一覧(最新更新順) ]

[[20140822090340]] 『2つのファイルの重複と差分を埋めたい』（エクセル初心者） ページの最後に飛ぶ

[[20140822090340]]　『2つのファイルの重複と差分を埋めたい』（エクセル初心者）　ページの最後に飛ぶ