[EXCEL]LOOKUP関数の戻り値とセルの書式の関係

質問

現在(たぶん同じ理由で)2点ほど困っていることがあります。

以下のようにLOOKUP関数を使ってセルに値をセットしています。

<Sheet1のレイアウト>
|Ａ列|Ｂ列　　|Ｃ列
|－|－－－－|－－－－
|ａ　|あああ　|・・・
|ｂ　|いいい　|・・・
|ｃ　|　　　　|
|ｄ　|かかか　|・・・

<Sheet2のレイアウト>
|Ａ列|Ｂ列　　　　　　　　　　　　　　　　　|Ｃ列
|－－|－－－－－－－－－－－－－－－－－－－|－
|ａ　|　=LOOKUP(A1,Sheet1!A:A,Sheet1!B:B)　|
|ｂ　|　=LOOKUP(A2,Sheet1!A:A,Sheet1!B:B)　|
|ｃ　|　=LOOKUP(A3,Sheet1!A:A,Sheet1!B:B)　|

まず1つめです。
今まで、Sheet1は別ブックのマクロで出力された値を[コピー]→[形式を選択してはりつけ]→[値]ではりつけていました。このときはSheet2のB3のセルにはブランクが入っていたのですが、誤ってSheet1のセルに直接値を入力したり、[F2キー]→[Enter]をしてしまったためSheet2のB3のセルにはブランクをセットしたいのに0(ゼロ)が入るようになってしまいました。
B3のセルにブランクがセットされるように戻したいのですがどうしたらよいでしょうか？

次に2つめは、
Sheet2は他の列もB列と同様の式によって値がセットされていますが、列によって右詰で表示されたり中央揃えで表示されています。
Sheet1、Sheet2ともにすべて書式は「文字列」で、
Sheet2には「右詰」「左詰」などの書式は設定されていません。これはなぜでしょうか？

1つめは、IF文でLOOKUP関数の戻り値が0だったらブランクをセットするなどに式を変更すれば解決するとは思いますが、このような式が至るところで使ってありますので式を変更するにも時間がかかるため、また今後のためにも原因と対策をしりたいと思います。どなたかわかる方がいらっしゃいましたら回答よろしくお願いします。

回答No.5

miwaki

＃４訂正
D列かE列かどこか空いている列の1行目のセルに次の関数を入れます。
=IF(LEN(B1)=0,"",B1)

回答No.4

miwaki

＃３のお礼欄の記事にお答えします。
D列かE列かどこか空いている列に次の関数を入れます。
=IF(LEN(B1)=0,"",B1)
そして、これをコピーしてデータのある限り下のセルに貼り付けます。その後この列を選択してコピーし、B列に「形式を選択して貼り付け」で値を選択して貼り付けてください。すると空白のセルがすべて""となり、参照したときに空白になります。

お礼

回答ありがとうございます。
なるほど！と納得しました。私はセルの書式設定をすることばかり考えていて知恵がそこまで回りませんでした。
この方法で問題は解決されました！！とても困っていたので助かりました。

回答No.3

miwaki

シート1のB列が、どういう方式で入力されたかわからないので、自信がありませんが、一般的に見かけ上空白でも ="" とした場合の空白と当該セルを選んで
「数値と値のクリア」としたときの空白とはまるで違います。
前者の場合、参照結果も空白になりますが、後者の場合は、０となります。
元は参照結果が空白だったのにいつの間にか０になったというのはこの辺りの変化があったものと考えられませんか。

お礼

回答ありがとうございます。
言われる通りSheet1のB列のセルに文字を直接入力したので見かけ上空白でも「0」の意味になってしまったのだと思います。それを=""とした場合の空白に戻したいのですが。。。

回答No.2

mshr1962

1つめ
ブランクのセルの参照すると0を返します。
参照するセルにスペースを入力するか
=IF(LOOKUP()="","",LOOKUP())
のようにしてブランクにしてください。
#1の方の方法で0を非表示も一つの方法です。
表示書式で行う場合は、ユーザー定義で
#,##0_ ;[赤]-#,##0_ ;"";@
のようになります。
2つめ
右と中央ですか？確かに数値は標準が右詰めですが、配置で変更できます。
どちらかというとセルの配置が揃ってないのだと思いますが？
念のため、書式の配置を設定してみてください。
上記以外だと参照元のデータにスペース等余分なものがありませんか？

お礼

回答ありがとうございます。
１つめは式を変えることなくブランクを返すようにしたいのです。参照元のSheet1に直接入力したらブランクが返っていたのがゼロが返るようになってしまった根本的な原因の解決にはならないので、直接入力する前のSheet1の状態に戻したいのです。説明不足で申し訳ございません。

２つめは#１の方にも書かせていただきましたが、私の勘違いで書式設定をし直したら解決いたしました。ありがとうございました。

回答No.1

miwaki

１つめ
ツール＞オプション＞表示で中央下にある「ゼロ値」のチェックマークをはずせば、０が表示されなくなります。
2つめ
この説明ではその原因の特定はできませんが、書式を再設定すれば揃うのではないでしょうか。

お礼

回答ありがとうございます。
１つめの
ツール＞オプション＞表示・・・はゼロが見えなくなるだけで、実際にはセルにゼロが入ってしまっています。
このEXCELの値を更にマクロで取得し別システムに登録しているので、他の列で同じ式を使っていてゼロが入ってないといけない所、ゼロが入っていると都合が悪いところがあるのです。説明不足で申し訳ございません。

２つめは同じ書式を設定しているセルをコピーしても解決しなかったので、Sheet2は書式設定されていないと思っていましたが私の勘違いでした。
こちらは解決しました。ありがとうございました。