出産前後の痔にはご注意!

エクセル2010を使っている者です。

「データ」というシートのAL5というセルに
IF(ISERROR(INDEX(AL1:AL500,$A$5)),"",INDEX(AL1:AL500,$A$5))
と関数を入れており、その結果、空白が戻り値となり、見た目上、AL5は空白となっています。

しかし、別のシートのあるセル(A1とします)に
=IF('データ'!AL5="","",DATE(YEAR('データ '!AL5)-1,MONTH('データ '!AL5),DAY('データ '!AL5)))
と関数を入力すると、AL5は空白として扱われていないようで、そのセル(A1)は空白にならずに「H1810.12.31」と表示されてしまいます。

AL5に空白が返されているのなら、それを空白として扱ってもらうようにするにはどうすれば良いですか?

このQ&Aに関連する最新のQ&A

A 回答 (2件)

まず質問文にある 1 本目の数式は、Excel 2007 以後に追加された IFERROR 関数を使って、もっと簡潔に、次式のとおり書いても同じ結果になります。

ご存じなかった場合は、今後の参考にしてください。

=iferror(index(al1:al500,$A$5),"")

AL5 セルに上式を記入し、かつ A5 セルに「5」と記入するならば、どちらの関数であれ、循環参照となります。

どちらの関数であれ、AL5 がいったん空文字列を返した後、循環参照を発生させる「5」という値を A5 に記入すると、Excel の仕様の限界により、AL5 では「0」が新たに算出されたにも関わらずその表示は変化していないという現象が起こる可能性があります。

その場合は、AL5 をダブルクリックしてください。空文字列から別の値に表示が切り替わります。別の値というのは恐らく、「693962」か、この値をシリアル値とする日付です。なぜなら、その場合に 2 本目の数式の計算結果が「H1810.12.31」となるからです。


2 本目の数式は、「データ 」→「データ」というふうに半角スペースを削除すれば、正しく機能すると思います。

ということは、上の循環参照が不具合の原因ではなかった場合において、1 本目の数式によって空文字列を返しているように見える AL5 は、実は空文字列ではなく、数値のゼロなどを非表示するセルの書式またはオプションが適用されている可能性を確認する必要がありそう。

●ゼロを非表示にするセルの書式の例
 「G/標準;-G/標準;」、「0;-0;」、「#,##0;-#,##0;」、「;;」等々

●エラー値を除くあらゆる値を非表示にするセルの書式
 「;;;」

●ゼロを非表示にするオプション(シートごとに設定)
 「Excel のオプション > 詳細設定 > 次のシートで作業するときの表示設定 > ゼロ値のセルにゼロを表示する」のチェックなし


>AL5に空白が返されているのなら、それを空白として扱ってもらうようにするにはどうすれば良いですか?

「データ!al5=""」というふうに空文字列を指定する記述のままで問題ありません。なお「'データ'!al5=""」と書いても特にエラーにはなりませんが、今回の場合、「'」は不要です。そのため数式の確定時に、Excel によって自動的に削除されます。

また、真の空白セルを指定したい場合は、同じく「データ!al5=""」と記述するか、「データ!al5=0」と書いてもいいことになっています。空白セル以外に、前者には空文字列のセル、後者には「0」のセルも該当することは、言うまでもありません。ほとんど出番はないと思いますが一応、「and(データ!al5={"",0})」と書けば、空白セルのみ該当となります。
    • good
    • 0
この回答へのお礼

遅くなりましたが、ありがとうございます。
解決しました。

お礼日時:2013/11/21 15:30

<br />  この循環参照の問題を回避するには、A5セルに5が入力された場合には、AL5セルにおいて<br /> <br /

500,$A$5)

の参照を行わない様にすれば良い訳です。
 その様な関数は次の様なものとなります。

=IF($A$5=ROWS(AL1:AL5),"",IF(ISERROR(1/(INDEX(AL1:AL500,$A$5)<>"")),"",INDEX(AL1:AL500,$A$5)))

 A5セルの値が5である場合には、

$A$5=ROWS(AL1:AL5)

の部分の判定式がTRUEとなりますから、1つ目のIF関数によって表示は空欄となるだけで、INDEX(AL1:AL500,$A$5)の参照は行われない様になっています。
 因みに、

ISERROR(INDEX(AL1:AL500,$A$5))

ではなく

ISERROR(1/(INDEX(AL1:AL500,$A$5)<>""))

となっているのは、参照先のセルが空欄の場合において、関数の戻り値を0ではなく空欄とする事で、「0値を表示しない」という設定にする事無く、空欄を参照した際に0が表示される事を防ぐ事により、もしも、参照先が空欄ではなく数値の0が入力されていた場合に、0を表示させる事が出来る様にするためです。
 因みに「0値を表示しない」という設定から「0値を表示する」という設定に切り替えるには、次の様な操作を行います。

[ファイル]タブをクリック
  ↓
現れた[オプション]アイコンをクリック
  ↓
現れた「Excelのオプション」ダイアログボックスの左側の囲みの中にある[詳細設定]をクリック
  ↓
現れた「次のシートで作業するときの表示設定」欄の中にある[ゼロ値のセルにゼロを表示する]と記された箇所をクリックして、チェックを入れる
  ↓
「Excelのオプション」ダイアログボックスの[OK]ボタンをクリック



 後それから、INDEX関数内で指定されているセル範囲が

AL1:AL500

という具合に行と列の両方ともに相対参照の形式となっていますが、これは

AL$1:AL$500

の間違いではないでしょうか?



 又、別の原因となり得る事には、計算方法のモードが自動ではなく手動となっているために、別のシートA1セルの表示が「H1810.12.31」となった後で、

INDEX(AL1:AL500,$A$5)

の計算がエラーとなる様な値がA5セルに入力され、その上でAL5セルに対して関数の編集等が行われますと、AL5セルの表示が空欄となるにもかかわらず、計算が自動的には行われないため、別のシートA1セルの表示は変化せず、「H1810.12.31」のままとなっているという事も考えられます。
 計算モードを自動にするには、次の様な操作を行って下さい。

[ファイル]タブをクリック
  ↓
現れた[オプション]アイコンをクリック
  ↓
現れた「Excelのオプション」ダイアログボックスの左側の囲みの中にある[数式]をクリック
  ↓
現れた「計算方法の設定」欄の中にある[自動]と記された箇所をクリックして、チェックを入れる
  ↓
「Excelのオプション」ダイアログボックスの[OK]ボタンをクリック
    • good
    • 0
この回答へのお礼

ありがとうございます。
解決しました。

お礼日時:2013/11/21 15:31

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Qエクセルで空白セル”” と未入力セルの違い

関数で セルA1 に=if(B1=0,"","入力済")
としました。
セルの選択を素早くするためにショートカットキー「Ctrl+↓」を
利用しています。
通常の未入力空白セルは上記のショートカットで飛ばされて選択
されません。
これに対して上記関数で ""  として入力されたものを
コピーして値だけを貼り付けたとしても
上記ショートカットキーで飛ばされず選択されてしまいます。
見た目は 全く同じ 空白セルです。(関数も値貼り付けをしているので消えています。)
未入力の空白セルと 関数で""と入力されて値貼り付けを受けたセルではどこが違うのでしょうか。
また、未入力の空白セルに 戻すには関数でどのように入力させればいいのでしょうか。もちろんDeleteKeyを使って未入力の空白セルに
戻るのですがそれではショートカットキーでとばせるメリットがなくなります。関数で大量に処理したいのです。
おわかりの方お教えください。

Aベストアンサー

>未入力の空白セルと 関数で""と入力されて値貼り付けを受けたセルではどこが違うのでしょうか。

未入力の空白セルは「ISBLANK関数」で「真」を返します。

一方、値としての""が貼り付けられたセルは「ISBLANK関数」で「偽」を返します。

>また、未入力の空白セルに 戻すには関数でどのように入力させればいいのでしょうか。
不可能です。「式」は「値」を返すので「空白セル」と言う「状態」を返す事は出来ません。

>もちろんDeleteKeyを使って未入力の空白セルに
>戻るのですがそれではショートカットキーでとばせるメリットがなくなります。関数で大量に処理したいのです。

つまり「セルを1つづつ選択してDeleteキーで1つづつ空白セルに戻すのが面倒」なので「全部を一気に空白セルに戻す良い方法は無いか?」と言う事ですね?

だったら簡単です。

すべての式を
=if(B1=0,"","入力済")
ではなく
=if(B1=0,1/0,"入力済")
にしておきましょう。

すると、未入力の所は、空白("")にならず「#DIV/0!」になります。

そう表示されたら、シート全体を選択し「CTRL+G」を押して「ジャンプ」を出します。

「ジャンプ」のダイアログが出たら「セル選択」を押し、セル選択のダイアログを出します。

セル選択のダイアログが出たら「数式」を選び、その下のチェックは「エラー値」だけにします。

「OK」を押してダイアログを閉じると「#DIV/0!」の表示になったセルのみが選択状態になります。

「#DIV/0!」の表示になったセルのみが選択状態になったら、Deleteキーを押して、それらのセルを一気に「空白セル」にして下さい。

もちろん、大量にあっても、手順は変わりません。

>未入力の空白セルと 関数で""と入力されて値貼り付けを受けたセルではどこが違うのでしょうか。

未入力の空白セルは「ISBLANK関数」で「真」を返します。

一方、値としての""が貼り付けられたセルは「ISBLANK関数」で「偽」を返します。

>また、未入力の空白セルに 戻すには関数でどのように入力させればいいのでしょうか。
不可能です。「式」は「値」を返すので「空白セル」と言う「状態」を返す事は出来ません。

>もちろんDeleteKeyを使って未入力の空白セルに
>戻るのですがそれではショ...続きを読む

QEXCEL計算式が入っているセルを空白表示に

EXCELデータで、セルに計算式が入っているのに画面上には何も数字が表示されていないというファイルをもらいました。
普通に、例えばSUM関数とかを入力したとき、結果が0だと、0が表示されますよね。もらったファイルでは、0が表示されておらず、0以外だと表示されるようになっています。
そのセルの計算式を見ても、特に変わった条件数式(IF関数など)が入ってもおらず、セルの書式設定を見ても特に変わったことがあるようには思えませんでした。
いったいどうやったら、こういうことができるのでしょうか?

Aベストアンサー

こんにちは!!
パソコンは奥が深いですよね!関数を使っていないなら、ツール→オプションの中のウィンドオプションのゼロ値のクリックをはずしてOKをクリックしてみてください!

QエクセルのIF関数で、文字が入力されていたならば~

エクセルのIF関数で文字が入力されていたならば~、という論理式を組み立てたいと思っています。

=IF(A1="『どんな文字でも』","",+B1-C1)

A1セルに『どんな文字でも』入っていたならば、空白に。
文字が入っていなければB1セルからC1セルを引く、という状態です。

この『どんな文字でも』の部分に何を入れればいいのか教えてください。

またIF関数以外でも同様のことができれば構いません。

宜しくお願いします。

Aベストアンサー

=IF(ISTEXT(A1),"",B1-C1)

でどうでしょうか?

Qcountaで空白セルもカウントされてしまう

エクセル2003で、counta関数を使って何かが入力されているセルの数を数えているのですが、
何も入力されていないセルまでカウントされてしまいます。

その何も入力されていないように見えるセルは、スペースもなにも入力されておらず、
そのセルの上でDelキーを押すと、そのセルはカウントされなくなります。
空白に見えるセルをDelキー押していけばいいのですが、空白に見えるセルが大量にあるので、1つ1つDelキーを押していくのは大変です。

空白に見えるセルをカウントしないようにする方法ありますでしょうか?

Aベストアンサー

式の結果が""を返す状態で値貼り付けなどをすると、そのセルは見た目空白ですが実は長さ0の文字列が入っていることになりCOUNTAで数えてしまいます。

式で数えるなら

=COUNTIF(範囲,"?*")

で数えるか(但し文字列データのみ。数値データが混じっているならCOUNT関数と併せて使う)、その対象列を選択し、データ→区切り位置→完了とすると空白セルに戻りますのでそれからCOUNTA関数を使って下さい。

Qエクセル 0や空白のセルをグラフに反映させない方法

以下の点でどなたかお教えください。

H18.1~H20.12までの毎月の売上高を表に記載し、その表を元にグラフを作成しています。グラフに反映させる表の範囲はH18.1~H20.12の全てです。
そのためまだ経過していない期間のセルが空白になり、そこがグラフに反映され見づらくなります。
データを入力する都度グラフの範囲を変更すればいいのですが、うまく算式や設定等で空白や0円となっているセルをグラフに反映させない方法はありますか?

お手数ですが、よろしくお願いいたします。

Aベストアンサー

売上高のセルは数式で求められているのですよね?
それなら
=IF(現在の数式=0,NA(),現在の数式)
としてみてください。
つまり、0の場合はN/Aエラーにしてしまうんです。N/Aエラーはグラフに反映されません。

Qエクセルで、条件に一致した行を別のセルに抜き出す方法

エクセルで、指定した条件に一致するセルを含む行をすべて抜き出す方法が知りたいです。

たとえば、

<A列> <B列> <C列>
7/1 りんご 100円
7/2 ぶどう 200円
7/2 すいか 300円
7/3 みかん 100円

このような表があって、100円を含む行をそのままの形で、
別のセル(同じシート内)に抜き出したいのですが。

7/1 りんご 100円
7/3 みかん 100円

抽出するだけならオートフィルターでもできますが、
抽出結果を自動的に、別の場所に、常に表示させておきたいのです。

初歩的な質問だと思いますが、検索しても分からなかったので、よろしくお願いします。

Aベストアンサー

同じ質問が結構よく出てますが、そんなに初歩的でもありません
別シートのA1セルに「100円」と入力し、そのシートの任意のセルに以下の式を貼り付けて下さい。後は、下方向、右方向にコピー。
日付のセル書式は「日付」形式に再設定してください

=IF(COUNTIF(Sheet1!$C:$C,$A$1)>=ROW(A1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(A1)+1)),"")

データ範囲は500行までとしていますが、必要に応じて変更して下さい

Qある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか?

Excelの関数について質問します。
ある範囲のせるを検索して、その隣のセルの値を取得するという関数を探しています。
なければユーザー定義で作りたいと思っています。
VLOOKUP関数では一番左端が検索されますが、
それをある範囲まで拡張して、
その右隣の値を取得できるようにしたいのです。
どうかお知恵をお貸しください。

Aベストアンサー

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場合によっては、IFをかぶせてCOUNTIFで確認した方が良いかもしれません。
 ex. =IF(COUNTIF(A1:F200,X1)=1,【上記数式】,"えらー")

ちなみに、VBAでやるならこんな感じになるかと。

動作の概要
 【検査範囲】から【検査値】を探し、
 最初にHITしたセルについて、右隣のセルの値を返す。
 ex. =Sample(X1,A1:F200)

'--------------------------↓ココカラ↓--------------------------
Function Sample(ByVal 検査値 As Variant,ByVal 検査範囲 As Range)
 For Each セル In 検査範囲
  If セル = 検査値 Then Exit For
 Next セル
 Sample = セル.Offset(0, 1)
End Function
'--------------------------↑ココマデ↑--------------------------

いずれもExcel2003で動作確認済。
以上ご参考まで。

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場...続きを読む

Qエクセルで数式の結果が反映されない(自動計算になっているのに)

すいません、教えてください。

エクセル(XP)で数式が自動計算されなくなって
しまいました。

  ※ツールのオプションはちゃんと自動計算に
   なっているのに全く反映されません。

今まで全く問題なく作業できていたので
原因が分からず困っています。

数式等のエラーだとは考えにくいです。
と言うのも数式のあるセルをダブルクリックして
選択し、そのままEnterキーを押すと計算結果が
反映されます。
ただ、今まで全て自動計算されていたのが
全く動かなくなってしまったんです。

同じようなトラブルを経験された方、又は
詳しい方、どうかよろしくお願いします。

Aベストアンサー

原因は分からずじまいになってしまいましたが
同じことがありました。

「Officeの救急隊」を試してみましょう。
ボタン一発で全ての設定が元に戻ります。
http://www.vector.co.jp/soft/win95/util/se247118.html
(ツールバーなども初期状態に戻ります)

私の場合はコレで直ってしまいました^^

参考URL:http://www.vector.co.jp/soft/win95/util/se247118.html

Q【エクセル】指定したセルに入力することで、別の特定のセルに決まった文字を表示させるには?

社用データを作る際の手間を省こうとしたのに、かえって難しくなってしまいました(-"-;)
分かりにくい質問で恐縮ですが、よろしくお願いします。

現在Excel2003を使っています。
「指定したセルに文字・数字問わず入力があった場合、特定のセルに特定の文字を表示させる数式(または方法)」は無いでしょうか。
(できれば指定したセルに入力が無い限り、特定文字を表示させるセルは非表示のままにしたいと思っています)

例えば、「A1のセルに『○田×男(人名など)』と入力した時、B1のセルに『様』と表記させる」と言った具合です。
(このとき、A1に入力が無ければB1も空白のままにしておきたいのです)

手動で入力した方が早そうな気もしますが、応用次第で他の表や作業もスッキリしそうなので、手があれば是非ご教授下さい。

Aベストアンサー

間違えました。

B1 に
=IF(A1="","","様")

でした。

Qエクセルで重複しているデータの抽出のしかたを教えてください。

エクセルで重複しているデータを抽出したいのですが・・。なにぶん初心者なもので簡単な方法があればよいのですが・・。
A列に日付 B列に企業名このデータが300件ほどあります。

 Å列      B列
1月 1日    AAAA社
1月 4日   BBBB社
1月 8日   CCCC社
2月 1日   BBBB社
2月20日    DDDD社
3月 2日   AAAA社

と、あるとしてB列の企業名が重複しているものを抽出したいのですが・・・。この場合 AAAA社とBBBB社ですが。
これを簡単に抽出する方法はないでしょうか?
教えてください お願いいたします。

Aベストアンサー

企業名がセルB2からセルB300まで入力されていると仮定します。
セルC2に IF(COUNTIF($B$2:B2,B2)>1,"*","") を入力し、セルC300までコピーして下さい。すると重複したデータの二番目以降に "*" のマークが付きます。
(注) 数式を下のセルまでコピーするため、COUNTIF($B$2 は絶対参照にして下さい。


人気Q&Aランキング

おすすめ情報