excel関数の質問です。

 
(例は補足で書きます)
・A列が0と1が並んだデータ
・B列に以下の条件の数値を出力
・・同じ行のA列が0の場合は、0
・・同じ行のA列が1の場合は、その上下の行で連続する1の数
 
 以上のことをexcel関数で書くことはできますか?
 
※マクロでは書けると思うのでそちらの回答は必要ありません。

回答の条件
  • 1人1回まで
  • 登録:2012/09/28 12:21:58
  • 終了:2012/09/29 18:10:16
id:Lhankor_Mhy

こんな感じです。

AB
00
00
00
00
11
00
13
13
13
00
00
00
12
12

ベストアンサー

id:gong1971 No.1

gong1971回答回数443ベストアンサー獲得回数682012/09/28 14:07:32

ポイント200pt

仮計算の列を使用すれば可能です。(条件に特に記載が無かったので…)

下記例では、A1からデータが始まっているとして、C列を仮計算に使用します。

C1: =A1
C2: =IF(A2=0,0,C1+1)
B1: =IF(C1=0,0,IF(C2=0,C1,B2))

※C2とB1の数式は、必要行数分、下方向にコピーしてください。
※仮計算に使用したC列は非表示にする事が可能です。
※数式を設定した最下行の直下の行は空白セルとしてください。

id:gong1971

kseikakuさんの回答を参考に、こんな書き方も可能でしたね。

C1: =A1
C2: =IF(A2,C1+1,0)
B1: =IF(C1,IF(C2,B2,C1),0)
※元の回答と実質的には変わってません。

2012/09/28 14:48:29
id:Lhankor_Mhy

ありがとうございます。
思った以上にすっきりした式でびっくりです。

2012/09/29 18:08:06

その他の回答(1件)

id:gong1971 No.1

gong1971回答回数443ベストアンサー獲得回数682012/09/28 14:07:32ここでベストアンサー

ポイント200pt

仮計算の列を使用すれば可能です。(条件に特に記載が無かったので…)

下記例では、A1からデータが始まっているとして、C列を仮計算に使用します。

C1: =A1
C2: =IF(A2=0,0,C1+1)
B1: =IF(C1=0,0,IF(C2=0,C1,B2))

※C2とB1の数式は、必要行数分、下方向にコピーしてください。
※仮計算に使用したC列は非表示にする事が可能です。
※数式を設定した最下行の直下の行は空白セルとしてください。

id:gong1971

kseikakuさんの回答を参考に、こんな書き方も可能でしたね。

C1: =A1
C2: =IF(A2,C1+1,0)
B1: =IF(C1,IF(C2,B2,C1),0)
※元の回答と実質的には変わってません。

2012/09/28 14:48:29
id:Lhankor_Mhy

ありがとうございます。
思った以上にすっきりした式でびっくりです。

2012/09/29 18:08:06
id:kseikaku No.2

kseikaku回答回数48ベストアンサー獲得回数162012/09/28 14:11:19

ポイント50pt

マクロは使用しないということで大分めんどくさい感じになっていますが。

B列,C列,D列,E列,F列,G列,H列に以下のような関数を書きます。(2行目に書くとして記載します)

B列 =IF(A2,SUM(C2+":"+D2),0)
C列 =ADDRESS(G2,1)
D列 =ADDRESS(H2,1)
E列 =IF(A2,E1+A2,0)
F列 =IF(A2,F3+A2,0)
G列 =ROW()-E2
H列 =ROW()-1+F2

こうすれば、B列にお望みの形で表示されるとおもいます。
E列、F列で上から連続と下から連続を計算し、それを元にSUM関数の開始/終了アドレスをとって集計しています。(工夫すれば、関数の入れ子でできるような気もしますが....)

他1件のコメントを見る
id:Lhankor_Mhy

ありがとうございます。
自分でやってた時は「INDIRECTとか使わなきゃダメかなあ?」とかいろいろいじってる内に訳が分からなくなってしまいました。あの先に正解はあったんだなあ……

2012/09/29 18:09:48
id:kseikaku

gong1971さんありがとうございます。
確かにINDIRECT抜けてました。入れ子の関数もつくってみたのですが、gong1971さんの回答のすっきりするのでアップやめてました。

2012/10/02 11:42:15
  • id:Silvanus
    VBAで独自のワークシート関数を定義する、
    というのも無しなんでしょうね?…恐らく。
  • id:Silvanus
    目的のセルより下方へのカウントは簡単にできそうですが
    上方へのカウント方法が思い付きませんでした。
    一応、VBAの標準モジュールでユーザー定義関数を作成してみました。
    何の芸も無い単純かつ最小限なものですが…。
    "=Lhankor_Mhy(A1)"の形式でお使い下さい。
    -----
    Function Lhankor_Mhy(rng As Range) As Long

    Const varChk As Variant = 1
    Const varSkp As Variant = 0

    Dim lngCol As Long
    Dim lngRowU As Long
    Dim lngRowL As Long

    If rng.Value = varSkp Then
    Lhankor_Mhy = 0
    ElseIf rng.Value = varChk Then
    lngCol = rng.Column
    lngRowU = rng.Row
    lngRowL = lngRowU
    Do While (lngRowU > 1) And _
    (Cells(lngRowU, lngCol).Value = varChk)
    lngRowU = lngRowU - 1
    Loop
    Do While Cells(lngRowL, lngCol).Value = varChk
    lngRowL = lngRowL + 1
    Loop
    Lhankor_Mhy = lngRowL - lngRowU - 1
    Else
    Lhankor_Mhy = CVErr(xlErrNA)
    End If

    End Function
  • id:Silvanus
    複数列使っても良かったのか…orz。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません