Excel2010でのHLOOKUP関数

質問

先日、同内容の質問をさせていただきましたが、私の説明がまずかったため、エクセル画面を添付し、再度の質問とさせていただきます。
列ごとに個人データを入力し、１個人には200個（行）のデータがあります。現在１４０名分のデータがあります。作成した票の各欄には行番号を変えたHLOOKUP関数を入れ、A1に入力した検索値に該当する人のそれぞれのデータを表示するようにしています。個人のデータは200以上増えることはないのですが、人数は増えてくるので、列方向は200列ほど範囲指定しています。
これまで、検索値を入力すれば、うまく票の中に該当データを表示してくれていたのですが、この度一名分のデータを一番右列に追加し、割り当てた検索値をA1に入力したのですが、票には何も表示がありません。
以上よろしくお願いします。
ｐｓ　これまでデータの添付をしたことがないので、画像がうまく載っているか心配です。うまくいっていなかったら再度挑戦します。

回答No.8

bunjii · ベストアンサー

>ご指示の＝CELL（　関数を使い確認しましたところ、すべて『ｖ』が表示されました。念のため１行目をオートフィルで入れなおしました。
>結果は同じでした。
あり得ないことなのでExcel 2010のプログラムが部分的に壊れているかも知れません。
回答No.5の補足にある(1)の数式で範囲が$D$1:$EP$222となっていますが
列数に誤りはありませんか？
DBシートのD1が1、E1が2、F1が3・・・でEP1が143になりますよね？
中間は連番の数値と言うことであれば検索値が何番以降が検出できないのかを提示すべきではないでしょうか？
また、DBシートの2行目以下が全て未入力の列が中間に存在するのでしょうか？
何れにしても正常なExcelブックでExcelのプログラムに損傷が無ければ有り得ない現象です。
Excelのプログラムの一部に損傷が起こっている場合はOfficeの修復またはOfficeの入れ直しをすれば直ります。
Excelブック（対象のExcelファイル）の一部が壊れているときはExcelブックの作り直しが必要でしょう。

補足

>DBシートのD1が1、E1が2、F1が3・・・でEP1が143になりますよね？
中間は連番の数値と言うことであれば検索値が何番以降が検出できないのかを提示すべきではないでしょうか？

はい、現在140列までの使用で、その右隣り（141列）からが右がダメなのです。142列も同じでした。
＞Excelのプログラムの一部に損傷が起こっている場合はOfficeの修復またはOfficeの入れ直しをすれば直ります。
仰せのとおり、この部分も考えています。とりあえずはマイクロソフト　サポートに連絡、そして同ファイルの別シートや新規ファイルにSheet”DB"の内容をコピーしたり、また、サーバー経由で同僚のPCで開いてもらい確認してみます。

回答No.7

bunjii

>>私が確認したいのはDBシートのD1～EP1の値が文字列か数値かを明確にして頂きたいのです。
>これについては、通し番号（数値です。）
>A1に入力は『数値です。』
HLOOKUP関数の検索値が数値で範囲の1行目が数値の場合は検索方法を省略したとき検索値以下の値の最大値が抽出対象の列になります。
従って、エラーは起こりませんが検索値と同じ値が見付からないとき予想外の結果になります。

>とにかく同じ関数で処理しているのに、ある列を境に表示が無くなったことが理解できないのです。
データベース側の1行目の連番に問題が隠れているかも知れません。
「ある列を境に」と言うことはその列の前後についてセルの値のタイプを確認されると原因が見えるかもしれません。
=CELL("type",[検査対象]) → 数値のとき"v"（ブイ）、文字列のとき"l"（エル）が戻ります。未入力のときは"b"（ビー）です。
尚、DBシートの1行目に連番の数値では無く数字の文字列が混じっていると目的通りの値が戻りませんのでデータベースの値を精査してください。

補足

色々ありがとうございます。
この状態でとどまっているわけにもいきませんので、別シートもしくは、新規ファイルにコピー等移植作業も考えていますが、どうも合点がいかないので、よろしくです。
>>私が確認したいのはDBシートのD1～EP1の値が文字列か数値かを明確にして頂きたいのです。
ご指示の＝CELL（　関数を使い確認しましたところ、すべて『ｖ』が表示されました。念のため１行目をオートフィルで入れなおしました。
結果は同じでした。

回答No.6

bunjii

>また、添付の一番上の行の数値が左寄せになっていることで、文字列と思われたかもしれませんが、セル内の関数を表示するために、『数式の表示』を指定すると、このように左寄せの表示になってしまいました。
意味不明です。
私が確認したいのはDBシートのD1～EP1の値が文字列か数値かを明確にして頂きたいのです。

>ちなみに上記(1)の関数は、別シートで票を作成している中での、あるセル内の関数です。
ここで言っている『票』とは印刷された結果が『票』であってデータを処理しているシートは『表』と表現して頂きたいと思います。
問題のHLOOKUP関数が設定されているシートのA1セルの値についてもDBシートのD1～EP1セルと同じで文字列か数値かを明確にして頂かないと不具合の原因を究明できないでしょう。
(1)の数式 =HLOOKUP(DB!$A$1,DB!$D$1:$EP$222,2) は氏名を抽出するセルに設定されたものでしょうか？
また(2)の数式 =IF(HLOOKUP(DB!$A$1,DB!$D$1:$EP$222,21)="","",HLOOKUP(DB!$A$1,DB!$D$1:$EP$222,21)) は不自然な数式であり無意味なように見受けられます。
憶測ですがDBシートから抽出する値が19種（2行目～20行目）であり21行目以下はブランクと言うことでしょうか？
一般的には =IFERROR(HLOOKUP($A$1,DB!$D$1:$EP$222,ROW(),0),"") のような数式を使い21行目以降は数式を設定しないようにします。
または =IFERROR(HLOOKUP($A$1,DB!$D$1:$EP$222,ROW(),0),"")&”” のようにして抽出されたセルがブランクのときは文字数0の文字列（""）を代入するようにします。

補足

何度もすいません。
＞意味不明です。
私もこの度初めて経験したのですが、セルに数値（セル内右寄せ）を入れていても、セル内の関数を表示するため、数式タブ内の数式の表示をクリックすると、入力した数値が左寄せになり、数値が文字列のようになるのです。

＞私が確認したいのはDBシートのD1～EP1の値が文字列か数値かを明確にして頂きたいのです。
これについては、通し番号（数値です。）
A1に入力は『数値です。』
（１）については、そんな感じです。A1で指定の列の２行目のデータを指定しています。
（２）については、データの入力されていないセルを指定したとき、空白表示をさせるようにしたのですが、上手な方法がわからなかったので、昔のBasic言語でのプログラミングの方法でやってしまいました。

＞憶測ですがDBシートから抽出する値が19種（2行目～20行目）であり21行目以下はブランクと言うことでしょうか？

１列には200程度のデータがありますが、20行目までは必ずデータはあるのですが、それ以下はデータの有る無しの行があるので、無い場合は空白表示をするようにしました。

とにかく同じ関数で処理しているのに、ある列を境に表示が無くなったことが理解できないのです。
以上よろしくお願いします。

回答No.5

bunjii

>まずは、データを基に『票』を作成しています。
言葉を正しく理解できないと折角の回答が無意味になります。
『票』と『表』の違いから学習し直す必要がありそうです。
Excelは『表計算ソフト』です。従って、『票』を作らずに『表』を作って計算やデータ処理を行います。

>本物のファイルには、指定の列の指定の行にデータがなければ、空白表示するように設定しています。
質問に添付の画像の数式と異なる数式でしょうか？
例えば下記のような数式ではエラーが発生して空白に見えます。
=IFERROR(HLOOKUP($A$1,$D$1:$GU$200,ROW()),"")
A1セルの検査値が数字の文字列で範囲の1行目が数値の場合はエラーが発生してIFERROR関数で指定された ""（文字数0の文字列） が返り何も無いように見えます。
質問の内容に不備があると正しい回答になりません。

>本物ファイルは、個人情報満載ですので、表示できなくてすいません。
本物ファイルの中身を提示せよとは1度も言っていません。
しかし、本物のデータと同じ形式の模擬データを提示して頂ければ確実な回答を提示できると思います。
数式は本物をそのまま提示して頂けないとあなたの疑問を解くことは困難になります。
肝心なことは検索範囲の1行目についてデータの仕様を提示して頂くことです。
添付画像の1行目は説明が無ければ数字の文字列（数値では無い）と解釈します。（数値の場合は右寄せで表示されている）

補足

(1) =HLOOKUP(DB!$A$1,DB!$D$1:$EP$222,2)

(2) =IF(HLOOKUP(DB!$A$1,DB!$D$1:$EP$222,21)="","",HLOOKUP(DB!$A$1,DB!$D$1:$EP$222,21))

色々な方に指導いただきまして感謝しています。
代表してbunjii様に報告させていただきます。
皆さんから指摘いただきました件について、本日確認してみました。
・FALSEの設定
・該当データがない時に空白を表示する設定を解除
・文字列、数値の確認
等々について確認しまた。問題や変わったことはありませんでした。ある列までは正常に表示してくれるのですが、今回入力した最後の列だけ表示がありませんでした。さらに右の列も試したのですが同じ結果でした。
また、添付の一番上の行の数値が左寄せになっていることで、文字列と思われたかもしれませんが、セル内の関数を表示するために、『数式の表示』を指定すると、このように左寄せの表示になってしまいました。すいません。
ちなみに上記(1)の関数は、別シートで票を作成している中での、あるセル内の関数です。『DB』はデータベースを作成しているSheet名が『DB』で、指定した範囲はSheet『DB』のD1からEP222です。この関数の部分は必ずデータが存在するので、空白表示指定はしていません。
上記(2)の関数は、データがないことがありますので、票の中でエラー表示されると後の使用上、うれしくないので、データが入力されていない時は空白表示をするようにしました。（もっと上手なやり方があるかもしれませんが・・・）
これらを踏まえて、考えられることをご教示いただきたいと思います。
ｐｓ
Excelについては、表・計算が得意なことは重々存じています。これまでも計算・分析・表、グラフ作成に活用させていただきました。ただ、VlookupやHlookup関数を利用した票（例えば住民票のようなもの）作成は非常に重宝します。実際、私の知っている自治会でも、高齢者の方の緊急連絡用登録票を作成し、重宝しているようです。WORDの代わりに、Excelで文書作成している人も知っています。封筒の表書きにも利用しています。私には到底できませんが、エクセルですばらしい絵を描いている人がテレビで紹介されていました。
このようにExcelは色々なことに使えるソフトだと思っています。

回答No.4

bunjii

>検索値を入力すれば、うまく票の中に該当データを表示してくれていたのですが、この度一名分のデータを一番右列に追加し、割り当てた検索値をA1に入力したのですが、票には何も表示がありません。
些細なことですが「票」は「表」の誤りではないでしょうか？
検索範囲の中に些細な誤りがあると目的の値を検出できなかったり目的以外の値が帰ってきます。
例えば、検索値が添付図のように数字の文字列で検索範囲が従来の値が数字の文字列で追加された値が数値の場合は正しい検索結果を得られません。
また、「何も表示がありません」と言う結果にはならず「エラーが表示される」か「0が表示される」や「目的外の値が表示される」と言う結果になるはずです。
尚、HLOOKUP関数の[検査方法]を省略していますが省略せずに FALSE または 0 とすることをお薦めします。（省略すると TRUE を指定し近似値として検索されます）
データの入力や数式の入力で些細な誤りが起こり易いことなので見直しを念入りにする習慣を身に着けてください。

補足

ありがとうございます。
まずは、データを基に『票』を作成しています。
数値文字列については確認してみます。
本物のファイルには、指定の列の指定の行にデータがなければ、空白表示するように設定しています。
左隣のデータと同じ形式で入力しているのに、1列違っただけでエラー表示等も何も表示されないのです。（データは入っているのに・・・）
FALSE等については、明日本物ファイルで確認してみます。
本物ファイルは、個人情報満載ですので、表示できなくてすいません。

回答No.3

msMike

》 票には何も表示がありません
ホント？！
入力した式が添付図に示されたものと同じなら、
#N/A
とか
0
が表示されるはずなンですがぁ。チェックしてみてください。
それから、200人用の「個人データ」と「作成した票」は別シートなンでしょ？
もし同一シートにあるのなら、「作成した票」は範囲 $D$1:$GU$201 の外側、つまり、GU列より右側、かつ、200行目より下側になければならないことぐらいは合点承知之助ですよね？

補足

ありがとうございます。
本物ファイルについては、指定の行にデータがなければ、空白表示するよう設定しています。
また、本物は別シートに票を作成しています。ですので、HLOOKUP関数のそれぞれの番地にはデータベースを作成しているsheet1がついています。

回答No.2

mt2015

正常な場合の例しかないので良く解りませんが、第4パラメータにFALSEを設定してみてください。
例：
＝HLOOKUP($A$1,$D$1:$GU$200,2)
↓
＝HLOOKUP($A$1,$D$1:$GU$200,2,FALSE)

これで解決しない場合、以下の内容を補足願います。

・列は常に後ろに追加であっていますか。
・既存の列の削除は行わない？必ず検索値は連番になっている？
・「何も表示がありません」と言うのはHLOOKUPの結果、何も表示しないと言うこと？
　それはあり得ないので、条件付き書式で0やN/Aの時に文字色を白にしたりしていませんか？
・正常なデータだけでなく、異常なデータのサンプルも提示してください。

補足

FALSEについては、明日本物ファイルで確認してみます。
列追加は挿入ではなく、常に一番右に追加しています。
列の削除はなく連番も確認しました。
指定した行にデータがない時は、票のセルに空白表示を設定しています。
今回の添付資料は、形式を確認してもらうために、新たに作ったもので、本物はたくさんの個人情報なので・・・。
以上です。本当に不思議な状態なのです。

回答No.1

9don

追加入力した「検査値」が「数値」だったとか。

補足

追加したデータは、それまでのものと全く同じ形式です。