[[20161117101328]]　『請求書のデータでの付け合わせ作業での式』（n）　ページの最後に飛ぶ

『請求書のデータでの付け合わせ作業での式』（n）

お世話になっております。
エクセルのデータの請求書のつけあわせをしたいと思っています。
もらった請求書データと　内部データの二つを　画面で並べて見たい
ので、それぞれBookを分けたいです。
車名、ナンバーのハイフン以降、出品番号
これら3つの情報のどれかと金額部分が二つのデータ間で合致すれば
一致ありと表示させていきたいです。
どちらのデータにも間違いが潜んでいる可能性があるので、
一致無しと表示されたものを検証して修正していきながら
最後にはすべて一致になって合計額も合えば作業終了
というふうに考えています。
どんな式を組んだらいいでしょうか。

もらった請求書

	A	B	C	　　　　D	E
	納車日	車名	ナンバー　	出品番号	金額
1	11/17	ベンツ	EE104-1234	 	10,000　　一致あり
2	11/17		910112	　　　　13	100,000　一致なし
3	11/18	フィット	GK3-141516	1819	20,000　　一致あり
4	11/17	ベルタ	SCP92-20212223	242	50,000　　一致あり

内部データ

	A	B	C	　　　　D	E
	納車日	車名	ナンバー　	出品番号	金額
1	11/17	ベンツ	EE104-001234	5678	10,000　一致あり
2	11/17	ノア	SR50-9101112	13	90,000　一致なし
3	11/17	フィット	GK3-141516	1819	20,000　一致あり
4	11/17	ベルタ	SCP92-20211223	0242	50,000　一致あり

＜使用 Excel：unknown、使用 OS：unknown ＞

使用エクセルは2016　OSは10です。説明でわかりずらいところがあったら教えていただけますでしょうか。どうぞよろしくお願いいたします。
（ｎ） 2016/11/17(木) 13:15

丸投げされても困りますので、まずはご自身で作ってみてください。（判らないところを質問する場であって、作業依頼の場ではないですから）

2つのブックに分かれていると面倒なので、1つのブックに2シートに分けてコピーしておくのが良いでしょう。
次に、ナンバー比較がハイフンの後ろだけというのが面倒な部分なので、文字列形式の作業列を用意して、ここに数式で後ろだけ抜き出しましょう。
作業列は、以下のような式にすれば、ハイフン無しの場合にも対応できるかと思います。

 =IF(ISNUMBER(FIND("-",C1)),RIGHT(C1,LEN(C1)-FIND("-",C1)),C1)

ナンバー部分さえ単純化してしまえば、後はCOUNTIFS関数でも使って、一致する件数を調べると良いでしょう。（COUNTIFSの式は3つ必要そうですね）
または、数式ではなく、マクロで考えた方が簡単かも知れません。
（???） 2016/11/17(木) 13:19

???さん、ご返信ありがとうございます。
丸投げしてしまってすみません。
魔法のような方法があるのかなぁと思ってしまいました。

ナンバーはあらかじめ後ろだけ抜き出しておけばよいですね。
やはり、ブックに分けると面倒なんですね。　これだけ教えてもらえただけでも考えが一歩すすみました。
ありがとうございます。　

 =IF(ISNUMBER(FIND("-",C1)),RIGHT(C1,LEN(C1)-FIND("-",C1)),C1)
いただいた式も意味がよくわからないので、ちょっと調べてみます。

マクロはもっとわからないので、　COUNTIFSで考えてみます。
ありがとうございます。
（ｎ） 2016/11/17(木) 13:28

追記です。

出品番号も、前ゼロの有無が混在してますね。
もう1列作業列追加し、「=VALUE(D1)」等で前ゼロ無し統一しましょう。
（???） 2016/11/17(木) 13:30

更に追記。

最初の作業列の数式を簡単に説明しますと、ハイフンが入っていないならそのまま使用。ハイフン入りなら右から残り文字数分抜き出しています。まずは「=RIGHT(C1,LEN(C1)-FIND("-",C1))」として作業列を埋めてみると理解いただけるでしょう。

あと、前提条件で大丈夫かな？、と思った点があります。同じ日に同じ車が2台売れた場合、車名一致するので、違う車が比較対象になったりしませんかね？車名は判定条件に入れない方が良いように思います。
（???） 2016/11/17(木) 13:39

???さんありがとうございます。
ISNUNBERという式初めて見ましたが貼り付けたら簡単にハイフン以下が取り出せました。
ありがとうございます。
出品番号のゼロも取ります。
車名は除外します。

実は以前自分で作ったのは

二つのデータを１シートに並べて配置して、　それぞれのデータの３列分の情報を＆でくっつけた列同士を
重複検索する式を作りました。
= IF(COUNTIF($C$2:$C$300, F2)>=1, F2, "")
F列にあってC列にないものは空欄表示されるという式でしたが、やりにくいので
別シートに分けて、別シートのF2を選択したのですが、うまく選択されず、同じシートのF2が選択されてしまいます。なぜかわかりましたら教えていただけますでしょうか。

（ｎ） 2016/11/17(木) 15:48

???さん、すみません。上の式、車名の列で組んだらできなかったですが、ナンバーの列で
組んだら、うまくいきました。
でも、もっといい式がないかなと思っています。
COUNTIFSの式を３つ　の意味もつかめていませんし、
COUNTIFSをどのように利用すればいいのかもまだよくつかめていないみたいです。
ヒントいただけたらとてもうれしいです。

（n） 2016/11/17(木) 15:58

???さん、たびたびすみません。
= IF(COUNTIF($C$2:$C$300, F2)>=1, F2, "") この式の列名は、今回のデータの列のことではありません。
ややこしくてすみません。
（n） 2016/11/17(木) 16:28

1つのブックに2つのシートを用意したとして、2つのシート共、C,D列を整形したG,H列を作業列とし、ここに数式でハイフンや前ゼロを統一してあるものとします。

請求書シートから内部データシートをチェックする際、ナンバーと金額が一致したものをCOUNTIFSで求めるものと、出品番号と金額が一致するしたものを求めるという、2つのCOUNTIFSを使用し、どちらかが1ならば「一致あり」とすれば良いでしょう。（3つと書いたのは、車名も同様にカウントすると考えていたから）

請求書シート側のF列の数式は、以下のようになるかと思います。同じように、内部データシート側の数式を考えてみてください。（ORを使いましたが、それぞれのCOUNTIFS結果を足し算し、1以上をOKとしても構いません）

=IF(OR(COUNTIFS(内部データ!E:E,$E1,内部データ!G:G,$G1),COUNTIFS(内部データ!E:E,$E1,内部データ!H:H,$H1)),"一致あり","一致なし")
（???） 2016/11/17(木) 18:08

???さん　ご返信ありがとうございます。
今日は時間がないため、週明けに実践してみたいと思います。どうもありがとうございます！！
（ｎ） 2016/11/18(金) 15:56

コメント返信：

[ 一覧(最新更新順) ]

[[20161117101328]] 『請求書のデータでの付け合わせ作業での式』（n） ページの最後に飛ぶ

[[20161117101328]]　『請求書のデータでの付け合わせ作業での式』（n）　ページの最後に飛ぶ