こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

データのない所の結果を空白にしたいです

グーグルスプレッドシートを使用しています。
エクセルの関数と同じだと思いまして、こちらで質問させていただきましたことご了承くださいませ。

ABCシートとXYZシートがあります。
XYZシートにはABCシートを参照して関数を書いています。
意図していない結果が出るのは「XYZシートの計算結果」の16行目~21行目です。
ABCシートの中のデータの無い所の計算結果で問題が発生しています。
データのないところは空白としたいのですがどのようにすれば良いかアドバイス頂けないでしょうか。

以下、ファイルを共有していますがイメージも本質問に添付させていただきました。
https://docs.google.com/spreadsheets/d/1wOPzf0FNeUgPqxtZzouf23E1SlcNBO0-cDkkpidn3d0/edit?usp=sharing

●ABCシートの内容です。
A列1行目,B列1行目
1行目です,B列2行目です
2行目です,B列3行目です
3行目です,B列4行目です
4行目です,B列5行目です
5行目です,B列6行目です
6行目です,B列7行目です
7行目です,B列8行目です
8行目です,B列9行目です
9行目です,B列10行目です
10行目です,B列11行目です
11行目です,B列12行目です
12行目です,B列13行目です
13行目です,B列14行目です
14行目です,B列15行目です

●XYZシートの計算結果です。
A列1行目,B列1行目
13行目です,B列14行目です
14行目です,B列15行目です
11行目です,B列12行目です
12行目です,B列13行目です
9行目です,B列10行目です
10行目です,B列11行目です
7行目です,B列8行目です
8行目です,B列9行目です
5行目です,B列6行目です
6行目です,B列7行目です
3行目です,B列4行目です
4行目です,B列5行目です
1行目です,B列2行目です
2行目です,B列3行目です
#REF!,#REF!
A列1行目,B列1行目
#REF!,#REF!
#REF!,#REF!
#REF!,#REF!
#REF!,#REF!

●XYZシートの関数です
A2には
=OFFSET(ABC!$A$1,COUNTA(ABC!$A:$A)-ROW()+MOD(ROW(A2),2)*2,COLUMN()-1)
B2には
=OFFSET(ABC!$A$1,COUNTA(ABC!$A:$A)-ROW()+MOD(ROW(B2),2)*2,COLUMN()-1)
と記載しています。以降
A3には
=OFFSET(ABC!$A$1,COUNTA(ABC!$A:$A)-ROW()+MOD(ROW(A3),2)*2,COLUMN()-1)
B3には
=OFFSET(ABC!$A$1,COUNTA(ABC!$A:$A)-ROW()+MOD(ROW(B3),2)*2,COLUMN()-1)
以降、関数の中の
(A3)(B3)の所が
(A4)(B4)
(A5)(B5)
と変化しています。
データのないところは空白としたい場合にどのように関数を書けば良いのかアドバイス頂けないでしょうか?
恐れ入りますがよろしくお願いいたします。

投稿日時 - 2018-05-05 12:52:42

QNo.9495356

困ってます

質問者が選んだベストアンサー

まず、期待していることは次の2つではないでしょうか。

その1
XYZシートの#REF!の表示を空欄にしたい

その2
XYZシートの17行目に
ABCシートの1行目(タイトル)が表示されているので
これを空欄にしたい。

以下、これを前提に説明します。

前者は他の方のコメントで解決できますが
後者はエラーではないので、解決できません。

この2つが期待値にならないのは、
XYZシート、A2セルをもとに説明すれば、

=COUNTA(ABC!$A:$A)-ROW()+MOD(ROW(A2),2)*2

この部分が0以下になることを考慮していないからです。

また、
=OFFSET(ABC!$A$1,COUNTA(ABC!$A:$A)-ROW()+MOD(ROW(A2),2)*2,COLUMN()-1)
は、
=OFFSET(ABC!$A$1,COUNTA(ABC!$A:$A)-ROW()+MOD(ROW(),2)*2,COLUMN()-1)
と書いても結果は同じです。

私なら

A2=if(COUNTA(ABC!$A:$A)-ROW()+MOD(ROW(),2)*2>0,OFFSET(ABC!$A$1,COUNTA(ABC!$A:$A)-ROW()+MOD(ROW(),2)*2,COLUMN()-1),"")

B2=if(COUNTA(ABC!$A:$A)-ROW()+MOD(ROW(),2)*2>0,OFFSET(ABC!$A$1,COUNTA(ABC!$A:$A)-ROW()+MOD(ROW(),2)*2,COLUMN()-1),"")

を埋め、必要数、下方向に複写します。

投稿日時 - 2018-05-06 09:57:03

お礼

HohoPapaさま

こんなに短時間に完ぺきに私の問題を理解していただき解決に導いていただきまして本当にありがとうございました!
ずっとこの問題で悩んでいたので、できた時には少し涙が出てしまいました。
本当に本当にありがとうございました!
また、今回見ず知らずの私のために丁寧に回答くださった皆様へ本当にありがとうございました!

投稿日時 - 2018-05-06 10:38:26

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(3)

ANo.2

IFERRORでいけると思いますが。

例えば、A16は
=OFFSET(ABC!$A$1,COUNTA(ABC!$A:$A)-ROW()+MOD(ROW(A16),2)*2,COLUMN()-1)
ですが、これを
=IFERROR(OFFSET(ABC!$A$1,COUNTA(ABC!$A:$A)-ROW()+MOD(ROW(A16),2)*2,COLUMN()-1),"")
に変えてみてください。(上記数式をそのままコピーして貼付けで)
非表示になりませんか?

マルチメディアファイルは削除されたか見つかりません。

投稿日時 - 2018-05-06 01:34:09

お礼

Green_Treeさま

ご連絡頂きまして、本当にありがとうございます。
説明が足らず大変申し訳ありません。

shiren2さまからのご連絡で「#REF!」という問題は出なくなっていたのですが、
A17とB17になぜかABCシートの1行目が出てきてしまうのです。

仮にABCシートに23行目までデータが入っていれば
「#REF!」と「ABCシートの1行目がどこかに出てきてしまう」問題は発生しないのですが
 ※「#REF!」はshiren2さまからのご連絡で解決済みです。申し訳ありません!
23行目までデータが入っていない場合に
「ABCシートの1行目がどこかに出てきてしまう」問題が発生します。

この一行目の部分はデータではなく、項目を記載しているだけの所なので読み込んでほしくないのです。
XYZシートで書かれている関数でABCシートの1行目だけは、処理から無視してほしいのです

元々、ABCシートの23行目までにデータが書かれている前提で関数が書かれていたようで
ABCシートの23行目までにデータが書かれていない場合に、
「ABCシートの1行目がどこかに出てきてしまう」
という不具合が出てしまいます。

説明が足らず本当に申し訳ありませんでした。
また、もし上記の問題を解決できる方法が分かりますようでしたらばなにとぞご指導、アドバイス頂きたく何卒宜しくお願いいたします。

めぐみ

投稿日時 - 2018-05-06 10:01:03

ANo.1

IFERRORでどうですかね。

IFERROR(OFFSET(hogehoge), "")
みたいな感じです。

投稿日時 - 2018-05-05 14:14:41

お礼

ご連絡ありがとうございます。
元々の式の問題のようで、IFERROR構文にしてもまったく同じ問題が発生します。

投稿日時 - 2018-05-05 15:09:02

あなたにオススメの質問