OFFSET式での返り値

行 A列
1 A1=OFFSET(A2,COUNTBLANK(A2:A12),0)
2
3
4
5 800
6 0
7 5000
8
9 7000
10
11 9000
12 10000
‥

このA1式ですと、5000(A7の値）が返ってきます。
空白セル("0"含む)があっても、800（A5セルの値）を返させるにはどうすれば良いのでしょうか？。
ご教示お願いします。

No.1 回答者： mike_g 回答日時： 2017/03/31 09:03

=OFFSET(A2,3,)

この回答へのお礼

皆さんありがとうございます。

単純に最上位置の数値（空白＆０以外）を取得させたいのです。
私の式では、5000が返ってきます。A2:A12の範囲の空白数をcount(結果=3)し、offsetで3番目の5000を返しています。

=OFFSET(A2,3,)の3では800が返りますが、空白ｾﾙ数が変わると欲しい答にはなりませんね。

enunokokoroさんの
>基準セルから空白以外の最初の値を出したいのでしょうか？
=OFFSET(A2,MATCH(0,INDEX(0/(A2:A12<>""),),0)-1,0)===>OKです。
=OFFSET(A2,MATCH(FALSE,INDEX(ISBLANK(A2:A12),,),0)-1,0)は式が長いので‥‥。

yuji3690さんの
>A2~A12の中で一番上にある0以外の数値を取得したいわけですね？=SUMPRODUCT((COUNTIF(INDIRECT("A$2:A"&ROW(A2:A12)),">0")=1)*(A2:A12))===>OKです。
ただ、式が長くなっているので、enunokokoroさんの式を採用させて頂きます。ありがとうございました。

お礼日時：2017/03/31 11:09

No.2 回答者： mike_g 回答日時： 2017/03/31 09:12

素朴な疑問。

「空白セル("0"含む)があっても、800（A5セルの値）を返させる」質問に、
「A1=OFFSET(A2,COUNTBLANK(A2:A12),0)」をなぜ提示したのですか？
参考までに教えてください。
その提示で回答者を混乱させます。

この回答へのお礼

皆さんありがとうございます。
誤解を与えたようで申し訳ありません。
単純に最上位置の数値（空白＆０以外）を取得させたいのです。
私の式では、5000が返ってきます。A2:A12の範囲の空白数をcount(結果=3)し、offsetで数値のある3番目の5000を返しています。

enunokokoroさんの
>基準セルから空白以外の最初の値を出したいのでしょうか？
=OFFSET(A2,MATCH(0,INDEX(0/(A2:A12<>""),),0)-1,0)===>OKです。
=OFFSET(A2,MATCH(FALSE,INDEX(ISBLANK(A2:A12),,),0)-1,0)は式が長いので‥‥。

yuji3690さんの
>A2~A12の中で一番上にある0以外の数値を取得したいわけですね？=SUMPRODUCT((COUNTIF(INDIRECT("A$2:A"&ROW(A2:A12)),">0")=1)*(A2:A12))===>OKです。
ただ、式が長くなっているので、enunokokoroさんの式を採用させて頂きます。ありがとうございました。

お礼日時：2017/03/31 11:09

No.3 回答者： yuji3690 回答日時： 2017/03/31 09:25

A2~A12の中で一番上にある0以外の数値を取得したいわけですね？

=SUMPRODUCT((COUNTIF(INDIRECT("A$2:A"&ROW(A2:A12)),">0")=1)*(A2:A12))
というのはどうでしょうか？
A2から(A2~A12)に0より大きい数が1つの場合
（この例ならA2からA5とA2からA6の場合が該当します）
そのセルのデータを取得、それ以外の場合は0とし、合計を表示します。
（この例だとA5は800、A6は0、それ以外は0、の合計となるので、800が表示されます）

ただ配列計算ですので、データが多くなったり、もっと複雑な形の計算に利用するとなると、重くなってしまう可能性があります。
なので下記の方法の方をお勧めします。

B列を作業列とし、
B2=IF(A2>0,IF(COUNT(A$2:&A2)=1,A2,""),"")
をB3~B12にコピー
A列が0より大きく、A列の2行目からその行までに数値が1つしかない場合に、A2のデータを、それ以外は空白を表示する。となっています。
A1=MAX(B:B)
A1にB列の最大値を表示させると、B列には先ほどの該当したデータのみしか表示されていないので、そのデータを表示させることになります。
表示が邪魔でしたらB列を非表示としてください。

この回答へのお礼

ありがとうございます。
単純に最上位置の数値（空白＆０以外）を取得させたいのです。

>A2~A12の中で一番上にある0以外の数値を取得したいわけですね？
=SUMPRODUCT((COUNTIF(INDIRECT("A$2:A"&ROW(A2:A12)),">0")=1)*(A2:A12))===>OKです。
ただ、式が長いので、enunokokoroさんの式を採用させて頂きます。ありがとうございました。

お礼日時：2017/03/31 11:07

No.4 回答者： yuji3690 回答日時： 2017/03/31 09:29

考えてる間に簡単な回答が出てたようですね。

流してください。
(普段OFFSET使うことが無いので、自分が同じ事をやろうとしたらどうするかを書きました)

No.5 回答者： enunokokoro 回答日時： 2017/03/31 10:03

実際に取得したい条件がよく分からないのですが。

OFFSET関数を使いたいのですよね。
基準セルから空白以外の最初の値を出したいのでしょうか？

こちら↓のような数式でもよいのでしょうか？
　=OFFSET(A2,MATCH(FALSE,INDEX(ISBLANK(A2:A12),,),0)-1,0)
または
　=OFFSET(A2,MATCH(0,INDEX(0/(A2:A12<>""),),0)-1,0)
などで、空白セル以外の最初のセルの値を表示することができます。

この回答へのお礼

ありがとうございます。
単純に最上位置の数値（空白＆０以外）を取得させたいのです。

enunokokoroさんの
>基準セルから空白以外の最初の値を出したいのでしょうか？
=OFFSET(A2,MATCH(0,INDEX(0/(A2:A12<>""),),0)-1,0)===>最適です。
=OFFSET(A2,MATCH(FALSE,INDEX(ISBLANK(A2:A12),,),0)-1,0)は式が長いので‥‥。

お礼日時：2017/03/31 11:09

No.6 ベストアンサー 回答者： CoalTar 回答日時： 2017/03/31 12:45

>単純に最上位置の数値（空白＆０以外）を取得させたいのです。

と
>=OFFSET(A2,MATCH(0,INDEX(0/(A2:A12<>""),),0)-1,0)===>最適です。
がかみ合いません
この数式は0が考慮されていませんよ？
だから説明に
>>基準セルから空白以外の最初の値を出したいのでしょうか？
に対応する数式が書かれています。

また、
>A2:A12の範囲の空白数
例題では5です。

杞憂ならよいのですが、
=INDEX(A2:A12,MATCH(1,(A2:A12<>"")*(A2:A12<>0),0))
[Ctrl]+[Shift]＋[Enter]配列数式、{}で囲まれる。

この回答へのお礼

CoalTarさん

ありがとうございます。
そうですね、欲しい答え(800)の上位置に0があると、０が返りますね。検証不足でした。
>空白セル("0"含む)があっても‥‥、は「空白＆０」の意味です。もっとも、このsheetでは、上位に0が無いのでenunokokoroさんの式を最適としました。
{=INDEX(A2:A12,MATCH(1,(A2:A12<>"")*(A2:A12<>0),0))} 式が本当の最適ですね。
皆さんご親切＆ご丁寧に教えて頂きありがとうございました。。
このsheetにはもう一つの教えて頂きたい式があります。後程、続きとして投稿しますのでご指導宜しくお願い致します。

お礼日時：2017/03/31 18:45

No.7 回答者： mike_g 回答日時： 2017/03/31 13:43

》 単純に最上位置の数値（空白＆０以外）を取得させたい

そういうことでしたか。
配列数式を厭わなければ、次の配列数式は如何かと。
=INDEX(A1:A12,MIN(IF(A2:A12<>0,ROW(A2:A12),"")))
「A2:A12<>0」は正に「空白＆０以外」のことですよね。

この回答へのお礼

mike_gさん
ありがとうございます。
ご指導の{=INDEX(A1:A12,MIN(IF(A2:A12<>0,ROW(A2:A12),"")))}
ですと、空白が返ってきます。最上位の空白＆０以外の数値が返って来て欲しいのですが‥‥。
よって、CoalTarさんの数式をBestとさせて頂きました。

お礼日時：2017/04/05 00:06

No.8 回答者： CoalTar 回答日時： 2017/03/31 16:53

さすが！ mike_gさん

ということは
=INDEX(A2:A12,MATCH(TRUE,A2:A12<>0,0))
[Ctrl]+[Shift]+[Enter]配列数式、{}で囲まれる。
でいけるってことですね。
ただし、A2:A12の空白とは、計算結果の空白でないこと。