ＩＦ関数の記述の方法について

質問

御教示頂きたく御願い致します。

１．数字は数字として表示させる（＋の場合も－の場合もあります）
２．数字以外のものは０と表示させる

数字以外とは、スペース・＃Ｎ／Ａ等のエラー表示・文字などです。

=IF(B2=****,0,B2)
**** の部分の記述の方法

回答No.6

EL-SUR · ベストアンサー

#5です。

当初の質問から、かなり複雑な方向へ展開していますね。#5の回答がかえって混乱を招いたのであれば申し訳ないです。
#5の補足にあるご質問ですが、いまひとつピンときません。
実際にそのファイルを見せていただければ素人なりに少しは具体的なアドバイスもできるかと思いますが、それも不可能ですし。
以下、お望みの回答ではないでしょうが、一応ご参考まで。

A表（推移表）が、今回のVLOOKUPで集計するファイル、
B表が、あ5.xlsなどのファイルで、これが1年分（12ファイル）ある、という理解でよろしいですか？

■> Ａ表の５月列の同じコードの行にリンクしたい。

当方、「勘定科目」「借方」「貸方」などの単語を見ただけで思考停止する体質なので、さっぱりわからないのですが、B表のA列に同じ勘定科目コードが複数あることはないんですよね？
だとすれば#1のお礼欄にある数式でいいと思います。2つめの VLOOKUPを囲っている（ 　）をとるだけで。
#5に書いたような、B34が空白かどうかとか、B34の値がB表にあるかどうかということは、この場合は考慮しなくてもいいでしょうし。

■> 0をリンク表示したい（Ａ表で縦計を計算させる為）

0ではなく、空白にしてもいいと思います。数式の 0を "" にして。
-------------------------------
=IF(ISNUMBER(VLOOKUP($B34,[あ5.xls]Sheet1!$A$1:$D$105,3,FALSE)),VLOOKUP($B34,[あ5.xls]Sheet1!$A$1:$D$105,3,FALSE),"")
-------------------------------
ただし、この件についてはいくつか疑問があります。
《 その1 》エラーが出ているということは数式が入っているのでしょうが、まず考えるべきことはその数式を変更してエラーを出さないようにすることです。
どんな数式が入っていて、どんなエラーが出ているのかわからないのでなんとも言えませんが、変更できるものならまずそれを考えてください。
そうすれば VLOOKUPの式も単純なものになります。
《 その2 》B表のA列に勘定科目コードが入っている、その同じ行のC列・D列は金額（数値）だけが入るのではないのですか？ なぜ文字列が入る場合があるのか理解できません。

ちなみに、たとえば A1からA20の範囲内に、数値・文字列・エラーが混在しているとします。これを SUM関数で合計すればエラーになりますよね、範囲内にエラーがありますから。
このエラーを無視して数値（正負混在）を合計するのは、次のような式で可能です。
------------------------------
=SUMIF(A1:A20,">0")+SUMIF(A1:A20,"<0")
------------------------------
または
------------------------------
=SUM(SUMIF(A1:A20,{">0","<0"}))
------------------------------

■>3.各月毎に変更するのは、ファイル名の数字（月）ですが、
>フィルコピー等で設定できる方法。

これもよくわからないのですが、
フィルコピーするだけで、あ5.xls の部分が、あ6.xls、あ7.xls‥と自動的に変わる方法ということですか？
できるかと言われれば、INDIRECT関数を使えば一応は可能です。
ただし、INDIRECT関数は参照ファイルが開いている状態でないと使えません。
1年分のファイルをすべて開いておくというのは無理でしょう。
それ以外の方法はたぶんないと思います、知らないだけかもしれませんが。

列番号 3、4だけのくり返しなら、先頭のセルに列番号3の式、その右のセルに列番号4の式を入れ、その2つのセルを選択した状態で右にフィルコピーすれば、3、4、3、4‥のくり返しになります。ご存知だと思いますが。
ご質問のケースで COLUMN関数を使う意味はありません。

A表（推移表）も月ごとのデータもすべて1つのファイルにまとめたらよいのではと思うのですが、分けておく理由があるのでしょうね。

以上です。冒頭に書いたようにこれ以上のアドバイスは現物を見せてもらわない限り無理です、少なくともワタクシには。文字だけのやりとりには限界があります。
今回書いたことが、少しでも参考になればいいのですが。

お礼

適切な御回答で決して混乱しておりません故、他事ながら御安心下さい。

長文にわたる適切な御回答頂き、心より感謝申し上げます。

後貴殿の御回答から色々なヒントを得る事が出来ました事、重ねて御礼申し上げます。

御陰様で、満足のいく表が完成いたしました。

御会いすることが出来れば、御覧頂き御評価を受けたいところですが・・・・。

本当に有難う御座いました。

６５歳の初心者より

回答No.5

EL-SUR

#1です。

>念のため下記数式の誤謬の検証を御願い致します。

ご質問の意味がよくわかりませんが、期待通りの結果にならないということですか？

とりあえず思ったことは、
■2つめの VLOOKUPを（ ）で囲む必要はありません。
------------------------------
（前略）,VLOOKUP($B34,[あ5.xls]Sheet1!$A$1:$D$105,3,FALSE),0)
------------------------------
ただし（　　）で囲ってあってもエラーになるとか、間違った値を返すことはないと思います。

■B34セルが空白のとき、
またはB34セルの値が あ5.xlsのSheet1のA列に存在しない場合も、
上記の式を入れたセルには 0が表示されます。
それを回避するには（ 上のようなケースで 0を非表示にするには ）
------------------------------
=IF(ISNA(MATCH($B34,[あ5.xls]Sheet1!$A$1:$A$105,0)),"",IF(ISNUMBER(VLOOKUP($B34,[あ5.xls]Sheet1!$A$1:$D$105,3,FALSE)),VLOOKUP($B34,[あ5.xls]Sheet1!$A$1:$D$105,3,FALSE),0))
------------------------------
とした方がいいでしょう。
MATCH関数で B34の値があるかどうかを調べて、なければ空白「""」にしています。
ISNA関数は参照先に値がないとき TRUEになります。
あるかないかを調べるだけなら、MATCH関数の代わりに COUNTIF関数でもいいのですが、COUNTIF関数だと あ5.xlsも同時に開いておく必要があるのが難点です。
COUNTIF関数では参照ファイルが閉じた状態だとエラーになります。

■上記の式で参照しているのは、あ5.xlsのSheet1のC列ですか？
もしD列の値を返したいのなら、VLOOKUP関数の第3引数「列番号」は 4になります。
C列なら 3のままで間違いありません。

**
■蛇足かもしれませんが、
VLOOKUPの検査値を $B34のように絶対参照にしているということは、この式を右の列にもコピーしているのでしょうか？
もしそうなら、VLOOKUPの第3引数「列番号」を COLUMN(C1)にして、
-------------------------------
=IF(ISNUMBER(VLOOKUP($B34,[あ5.xls]Sheet1!$A$1:$D$105,COLUMN(C1),FALSE)),VLOOKUP($B34,[あ5.xls]Sheet1!$A$1:$D$105,COLUMN(C1),FALSE),0)
-------------------------------
としておけば、右にフィルコピーするだけで COLUMN(C1)は COLUMN(D1)になり、そのままで D列を参照することができます。
いちいち 3を 4に変更する必要はありません。
下にフィルコピーしてもそのままでOKです。

COLUMN関数は列番号を返す関数ですから、COLUMN(B1)は2、COLUMN(C1)は 3、COLUMN(D1)は 4ということです。
C1でなくても、COLUMN(C34)でもかまいません（ C列でさえあれば ）。
まあ、2～3列ぐらいならあまりメリットは感じられないかもしれませんが、多くの列を参照する場合はこのようにした方がいいと思います。
1つの式を右に下にコピーするだけでOKですから。
**
とりあえず気づいたことは以上です。
もし何か合点がいかないことがあるのでしたら、どううまくいかないのかを具体的に書いてください。

補足

懇切丁寧な御回答心より御礼申し上げます。
下記のような事をしたいと思っていますので、再度御検証、御指導賜りたく概要を記述いたします。
■期待通りの結果にならないということですか？　→　おかげさまで、テストでは満足な結果を得る事が出来ました。
■2つめの VLOOKUPを（ ）で囲む必要はありません。
　　　→ （）は外します。

●Ａ表・・・推移表です。
Ｂ列に勘定科目コード(発生する可能性ある勘定科目コードを設定し固定です。)
Ｆ列から1月から１２月および四半期の計１６ヶ月分あります。
●Ｂ表・・・毎月発生したデータです。
Ａ列に勘定科目コードが表示されています。
Ｂ列に前月残が表示されています。
C列に借方発生金額です。
D列に貸方発生金額です。

一個の勘定科目コードの金額が表示される列は,毎月固定です。故にＡ表の行には、３または４を指定してあります。
行は、前月残+その月に新たに発生分が表示されますので月毎に行数は異なります。
前月残があり当月発生しない場合はスペースで表示されています。
毎月のファイル名
　　[あ５.xls]・・・５月分です。
[あ６.xls]・・・６月分です。
●実行したい事（ＥＸ．[あ５.xls]・・・５月分）
１・Ａ表のコード（Ｂ列）とＢ表のコード（Ａ列）が完全一致すればＢ表の３列目または４列目の金額（+・-あり）をＡ表の５月列の同じコードの行にリンクしたい。
２・Ａ表には,数字以外の＃Ｎ/Ａ等のエラー・スペース・文字等を表示させずに、0をリンク・表示したい（Ａ表で縦計を計算させる為）

３・各月毎に変更するのは、ファイル名の数字（月）ですが、フィルコピー等で設定できる方法。
　　＊貴殿の「COLUMN」使用の薦めをヒントにさせていただきました。

回答No.4

dac203

#2です。すいません「=TRUE」はいりませんですね(^^;)>。

回答No.3

char_cat

=IF(ISNUMBER(B2),B2,0)
という記入でＯＫです。
ＩＳＮＵＭＢＥＲ関数は数字であればＴＲＵＥを返し、数字以外であればＦＡＬＳＥを返します。

お礼

深夜にもかかわらず、御回答頂き深謝いたします。

下記数式で問題は解決したようですが、念のため下記数式
の誤謬の検証を御願い致します。

=IF(ISNUMBER(VLOOKUP($B34,[あ5.xls]Sheet1!$A$1:$D$105,3,FALSE)),(VLOOKUP($B34,[あ5.xls]Sheet1!$A$1:$D$105,3,FALSE)),0)

回答No.2

dac203

下記でどうでしょうか？ISNUMBERは参照先が数字の時にTRUEを返します。

=IF(ISNUMBER(B2)=TRUE,B2,0)

お礼

深夜にもかかわらず、御回答頂き深謝いたします。

下記数式で問題は解決したようですが、念のため下記数式
の誤謬の検証を御願い致します。

=IF(ISNUMBER(VLOOKUP($B34,[あ5.xls]Sheet1!$A$1:$D$105,3,FALSE)),(VLOOKUP($B34,[あ5.xls]Sheet1!$A$1:$D$105,3,FALSE)),0)

回答No.1

EL-SUR

>=IF(B2=****,0,B2)
>**** の部分の記述の方法

ちょっと違いますが、
=IF(COUNT(B2),B2,0)

または
=IF(ISNUMBER(B2),B2,0)

お礼

深夜にもかかわらず、御回答頂き深謝いたします。

下記数式で問題は解決したようですが、念のため下記数式
の誤謬の検証を御願い致します。

=IF(ISNUMBER(VLOOKUP($B34,[あ5.xls]Sheet1!$A$1:$D$105,3,FALSE)),(VLOOKUP($B34,[あ5.xls]Sheet1!$A$1:$D$105,3,FALSE)),0)