エクセルでのソートがうまくいきません

質問

エクセル2000を使っています。
VLOOKUP関数を利用して見積書を作っていますが、時たま上手くいかない事があります。

見積書は、
見積書のページに
Ａ列：数字４桁の商品コード
Ｂ列：商品名
Ｃ列：単価
（例：1001　アイスクリーム　1,500）
商品コードのページも全く同じで、Ａ列でソートしてあります。
VLOOKUPを使って、見積書のＡ列の４桁の数字に対応した商品コードのＢ列Ｃ列からデータを持ってくるようにして、見積もり書のＢ列とＣ列をうめる・・・という簡単な仕組みです。

ところが、なぜかＢ列とＣ列にうまくデータを持ってこられない行がいくつか出てくる事があります。（＃N/Aと表示される）
ちなみに、見積書も商品コードもＡ列のセルは文字列に設定しています。
また、変換できなかったＡ列の数字を上書きして入力すると半分くらいはＢ列Ｃ列が＃N/Aから本来のデータになるのですが、相変わらず変化なしの場合もあります。
そのような場合は、見積書のＡ列に商品コードのＡ列をコピーしたものを貼り付けて上書きするとちゃんと表示できるようになります。

この場合の見積書は、過去に作ったいろんな店舗への見積書を行追加して新たに作った「年間の見積書の全て」といった物なので、時によってＡ列のセルの性格が違っている場合があるかも・・・という訳で改めてＡ列を文字列に設定し直しています。
が、これをやってもＡ列での並べ替えもうまく昇順にならない場合がありますので、やはりセルによっては統一されていない部分があるのかと調べてみましたが全て同じく文字列になっています。
どうにも訳が判りません。
長い文章で申し訳ありませんが、御判りの方がおいででしたら後教授ください。

回答No.4

inspec · ベストアンサー

セルの設定が文字列でも中身のデータの型が数値の場合があります。
数値を入力してから、後から書式設定で文字列に変えるとそうなったりします。
VLOOKUPで検索するキーと参照する表のデータの型が一致していなければ表から取得できません。
セルの設定が文字列なら、データの型が数値の入っているデータでF2を押しEnterで再確定するとデータの型も文字になります。
データが多くてこのやり方では厳しい場合は、式でデータの型を修正することもできます。A1セルが修正対象のセルだとしたら、別のセルに=A1&""とし、これをA1に値の貼り付けすれば、文字列に変換できます。
検索するキー、検索する表ともにデータの型を文字列にしてみてください。

補足

ご回答ありがとうございます。
いろいろ考えてというか、結局のところ理由はまだ理解していませんがおっしゃる通り「セルの設定は文字列だが、中身のデータは違っている」という状態のようです。

>データの型が数値の入っているデータでF2を押しEnterで再確定するとデータの型も文字になります。
複数の見積もりを行追加して作った「年間の見積書の全て」（１２０行あります）をＡ列でソートしたものを見ると、１行目から８５行目までと８６行目から１２０行までの２つグループに分かれます。
この第２グループがうまくデータを持ってこられないので、Ｆ２＋エンターで変換したところ形式が一致してデータを表示できました。
さして多くの行数ではなかったので、全行このやり方で変換したところきれいに揃いました。
どうもありがとうございました。

回答No.5

imogasi

両方の文字列に違いがあることは確実です。
疑わしいセル同士で=EXACT(Sheet1!A1,Sheet2!D1)
のようにしてFALSEが出れば、何か違うのです。
スペース（TRIM関数で取り除ける場合もあります）や見えない文字コードが入っている可能性かあります（CLEAN関数で取り除ける場合もあります）。
後は全角か半角です。（JIS関数などで統一できる場合があります）
色々な原因タイプを学ぶことはできますが、１度で訂正は難しいでしょう。テーブルを作った方のせいです。
エクセル（システムや関数）がおかしい、なんて方にぐらつかないこと。
VLOOKUP関数の第４引数は、質問の場合FALSEを略さないこと。
FALSE型は検索表を並べ替えしなくて良い。ならべ変えの話題が出ることが、VLOOKUP関数FALSE型の勉強不足です。
＞御判りの方がおいででしたら
質問者のパソコンデータが見てテストできないのですから、誰にもはっきりと答えられません。

補足

ご回答有り難うございます。
見積書と商品コード表が一緒になったBOOKは私が作り、複数の営業担当者に渡して使ってもらっています。
商品コードを入力する際は「直接入力で入れるよう」に指示しています。

>エクセル（システムや関数）がおかしい、なんて方にぐらつかないこと。
そんな大それた事は思ってもいませんし、言ってもいませんが。

>質問者のパソコンデータが見てテストできないのですから、誰にもはっきりと答えられません。
そういう意味ではなく、「文字列に設定したセルに入力したデータが文字列以外に判断されるような事があるのだろうか？またそのような経験をお持ちの方がおいででしたら」という意味で書いたつもりでしたが・・・。

回答No.3

hallo-2007

原因は、わかりませんが、
商品コードを入れるセルに、入力規則　設定していますか。
別シートなので、A列の商品コードに名前をつけます。
例えば、商品コード
商品コードを入れるセルに、入力規則、リストで、範囲には=商品コード
これで、商品コードに登録したコードしか入力できなくなります。
（入力ミスはなくなる）
もうひとつは、VLOOKUP関数の最後の引数に　,FALSEを入れておく
並び替えが、昇順でなくても、エラーが出ないように
当方、エクセル２００３のため、試しが出来ません。

補足

ご回答有り難うございます。
>商品コードを入れるセルに、入力規則、リストで、範囲には=商品コード
これをやってみたのですが、「範囲に商品コード」というところが良く解からず、「元の値（S)」というボックスに手入力で「商品ＣＤ」（実際のシートの名前です）といれてみました。
しかしそれで見積書に４桁の数字を入力しても（直接入力でもそれ以外でも）エラーになってしまいます。
どうもそこのところが理解できていないようです。

>もうひとつは、VLOOKUP関数の最後の引数に　,FALSEを入れておく
ちなみに「=IF(ISBLANK(A2)," ",VLOOKUP(A2,商品CD!$A$1:$D$9999,2,FALSE))」という式を設定しています。

見積書のＡ列（商品コード）に数字が入っていない場合は、商品名欄を空白にし、FALSEも用いています。

回答No.2

pbforce

A列は、文字列ではなく、数値で管理したほうが良いような気がします。

数字だけなのですから、数値としたほうが、間違いが出ないでしょう。

補足

ご回答有り難うございます。
会社で使っています商品コードは「0001」から「9999」までの、全て4桁で出来ています。
Ａ列を数値にした場合、「0001」は「1」と表示されてしまいますので、それですといろいろと具合が悪いので文字列にして４桁表示をさせています。

回答No.1

n-jun

補足ポイントその3：引数「検索の型」について
http://allabout.co.jp/computer/msexcel/closeup/CU20060805A/index3.htm

ご参考になるかもです。

補足

ご回答有り難うございます。
休みの日に最初から読んでみます。