EXCELについての質問です。

VBAを使わずに解決したいです。
A列には日付が1000行入っており今後も3000行ぐらいまで増えていきます。
B列には金額が入っており1行おきから3行おきとランダムです。今後もランダムに入力されます。

B列の空白になっている行を削除して別シートで月ごとに1年分集計したいのですが
あらかじめセルに関数を埋め込んでおいて入力するだけで集計することはできないでしょうか?

よろしくお願いします。

回答の条件
  • 1人2回まで
  • 13歳以上
  • 登録:2011/05/22 06:54:08
  • 終了:2011/05/22 19:05:27

ベストアンサー

id:Lhankor_Mhy No.4

Lhankor_Mhy回答回数779ベストアンサー獲得回数2312011/05/22 09:52:39

ポイント50pt

力技ですのであまりオススメできませんが、ここではC,D列を作業列にしてE,F列に表示させています。

 

A B C D E F
4/1 3000 =IF(B1>0,1,0) =MATCH(1,$C$1:$C$8,0) =INDIRECT(ADDRESS(D1,1)) =INDIRECT(ADDRESS(D1,2))
4/2   =IF(B2>0,1,0) =MATCH(1,OFFSET($C$1:$C$8,D1,0),0)+D1 =INDIRECT(ADDRESS(D2,1)) =INDIRECT(ADDRESS(D2,2))
4/2 2000 =IF(B3>0,1,0) =MATCH(1,OFFSET($C$1:$C$8,D2,0),0)+D2 =INDIRECT(ADDRESS(D3,1)) =INDIRECT(ADDRESS(D3,2))
4/2 1000 =IF(B4>0,1,0) =MATCH(1,OFFSET($C$1:$C$8,D3,0),0)+D3 =INDIRECT(ADDRESS(D4,1)) =INDIRECT(ADDRESS(D4,2))
4/5   =IF(B5>0,1,0) =MATCH(1,OFFSET($C$1:$C$8,D4,0),0)+D4 =INDIRECT(ADDRESS(D5,1)) =INDIRECT(ADDRESS(D5,2))
4/5   =IF(B6>0,1,0) =MATCH(1,OFFSET($C$1:$C$8,D5,0),0)+D5 =INDIRECT(ADDRESS(D6,1)) =INDIRECT(ADDRESS(D6,2))
4/6   =IF(B7>0,1,0) =MATCH(1,OFFSET($C$1:$C$8,D6,0),0)+D6 =INDIRECT(ADDRESS(D7,1)) =INDIRECT(ADDRESS(D7,2))
4/7 5000 =IF(B8>0,1,0) =MATCH(1,OFFSET($C$1:$C$8,D7,0),0)+D7 =INDIRECT(ADDRESS(D8,1)) =INDIRECT(ADDRESS(D8,2))

 

余った行にエラー値#N/Aがでます。必要に応じてiserrorなどで回避して下さい。

id:breakthrough1

出来ました ありがとうございます

ちなみにお勧めできない理由を教えていただけないでしょうか

2011/05/22 15:40:03

その他の回答(3件)

id:spyglass No.1

spyglass回答回数455ベストアンサー獲得回数292011/05/22 08:54:12

ポイント20pt

他の方から回答が出れば私はポイント不要です。

結論から言うとVBA以外(シート関数だけ)では無理です。


なぜならシート関数は計算や検索する関数はあるのですが

B列の文字がなければ行間を詰める、という動的な関数はありません。

しかも今後もランダムに入力される、とういう条件なので計算式で予知する事も難しいと思われます。


かろうじて、オートフィルタからB列に対して「空白以外のセル」を指定すれば空欄を詰めて集計らしき表を表示する事は可能です。

この場合は別シートではなくデータシートページその物になりますが。


それとExcelの場合はバージョンを書かれた方が良いかも知れませんね。

バージョンが違うと関数が変わってきますので。

ご参考までに。

id:breakthrough1

ありがとうございます。

バージョンはEXCEL2007を使っています。

2011/05/22 09:00:43
id:takashi_m17 No.2

たか回答回数104ベストアンサー獲得回数122011/05/22 09:08:23

ポイント20pt

空白行の削除

フィルタを掛け、B列の空白のみを表示します。

空白行を選択し右クリック、「行の削除」を行えば空白行のみ削除できます。

「行の削除」でなく「削除」にならないように注意が必要です。

「削除」だとフィルタで隠れている金額有りの部分も削除してしまいます。



集計

DSUMを使えば月毎集計は関数で可能です。

http://www.eurus.dti.ne.jp/~yoneyama/Excel/kansu/dsum.htm

id:breakthrough1

ありがとうございます

DSUMで日ごとに集計していくとだいぶイメージに近いです

2011/05/22 13:59:05
id:Mook No.3

Mook回答回数1312ベストアンサー獲得回数3912011/05/22 09:37:39

ポイント20pt

パフォーマンスは保証できませんが、

Sheet2 の A1 に

=IF(COUNTA(Sheet1!$B$1:$B$3000)<ROW(A1),"",INDEX(Sheet1!A$1:A$3000,SMALL(IF(Sheet1!$B$1:$B$3000<>"",ROW($A$1:$A$3000),""),ROW(A1))))

と入力して、Ctl+Shift+Enter を押します。(うまくいけば前後に{ } が表示されます。)

これをA1:B3000 にコピーし、A列の書式を日付に、B列の書式を数値にします。


これでSheet1 にデータを入れていったら、B列があるものだけが表示されると思いますが、

どうでしょうか。


3000 は実際の運用の上限に合わせて変更してください。

id:breakthrough1

ありがとうございます

日付は出るのですが・・・

もうすこし試してみます

2011/05/22 14:33:02
id:Lhankor_Mhy No.4

Lhankor_Mhy回答回数779ベストアンサー獲得回数2312011/05/22 09:52:39ここでベストアンサー

ポイント50pt

力技ですのであまりオススメできませんが、ここではC,D列を作業列にしてE,F列に表示させています。

 

A B C D E F
4/1 3000 =IF(B1>0,1,0) =MATCH(1,$C$1:$C$8,0) =INDIRECT(ADDRESS(D1,1)) =INDIRECT(ADDRESS(D1,2))
4/2   =IF(B2>0,1,0) =MATCH(1,OFFSET($C$1:$C$8,D1,0),0)+D1 =INDIRECT(ADDRESS(D2,1)) =INDIRECT(ADDRESS(D2,2))
4/2 2000 =IF(B3>0,1,0) =MATCH(1,OFFSET($C$1:$C$8,D2,0),0)+D2 =INDIRECT(ADDRESS(D3,1)) =INDIRECT(ADDRESS(D3,2))
4/2 1000 =IF(B4>0,1,0) =MATCH(1,OFFSET($C$1:$C$8,D3,0),0)+D3 =INDIRECT(ADDRESS(D4,1)) =INDIRECT(ADDRESS(D4,2))
4/5   =IF(B5>0,1,0) =MATCH(1,OFFSET($C$1:$C$8,D4,0),0)+D4 =INDIRECT(ADDRESS(D5,1)) =INDIRECT(ADDRESS(D5,2))
4/5   =IF(B6>0,1,0) =MATCH(1,OFFSET($C$1:$C$8,D5,0),0)+D5 =INDIRECT(ADDRESS(D6,1)) =INDIRECT(ADDRESS(D6,2))
4/6   =IF(B7>0,1,0) =MATCH(1,OFFSET($C$1:$C$8,D6,0),0)+D6 =INDIRECT(ADDRESS(D7,1)) =INDIRECT(ADDRESS(D7,2))
4/7 5000 =IF(B8>0,1,0) =MATCH(1,OFFSET($C$1:$C$8,D7,0),0)+D7 =INDIRECT(ADDRESS(D8,1)) =INDIRECT(ADDRESS(D8,2))

 

余った行にエラー値#N/Aがでます。必要に応じてiserrorなどで回避して下さい。

id:breakthrough1

出来ました ありがとうございます

ちなみにお勧めできない理由を教えていただけないでしょうか

2011/05/22 15:40:03
  • id:breakthrough1
    A B
    1 4/1 3000
    2 4/2
    3 4/2 2000
    4 4/2 1000
    5 4/5
    6 4/5
    7 4/6
    8 4/7 5000

        ↓


    A B
    1 4/1 3000
    2 4/2 2000
    3 4/2 1000
    4 4/7 5000

    このようにしたいです。
  • id:Mook
    同じシートでやったのでちょっと抜けてたかもしれません。
    =IF(COUNTA(Sheet1!$B$1:$B$3000)<ROW(A1),"",INDEX(Sheet1!A$1:A$3000,SMALL(IF(Sheet1!$B$1:$B$3000<>"",ROW(Sheet1!$A$1:$A$3000),""),ROW(A1))))
    にしてください。

    ちょっととっつきにくいかもしれませんが、配列数式を覚えるとこのような時に応用が
    広がります。
  • id:Lhankor_Mhy
    >ちなみにお勧めできない理由を教えていただけないでしょうか
     
    計算量が多いのと、セルを1ヶ所でも変更すると再計算がかかるので、行が多くなると重そうなんですよね……
    マクロを書いて必要な時に実行したほうが効率が良さそうな気がしてます。
     
    あと、Mookさんのやり方のほうがスマートだと思います。SMALLと配列数式の組み合わせが美しいです。私も勉強になりました。
  • id:Lhankor_Mhy
    え、僕がいるかなんですか。なんか申し訳ないな……
  • id:breakthrough1
    Mook さん Lhankor_Mhy さん
    ありがとうございます
    おかげさまで解決しました
  • id:Mook
    自分で回答しておいて何ですが、すこし混乱していたようです。
    ROW の中身は単なるインデックスなので Sheet1 である必要はないですね。

    ただ式の入力範囲が自分に重なってしまっていたので、これをうまくコピーしないと
    エラーになってしまいます。
    A1に数式を入れ 「A2」以降にコピーして、A列をB列にコピーする、という手順でやれば回避できた
    かと思います。

    配列数式の入門的な説明は、下記が参考になると思います。
    http://pc.nikkeibp.co.jp/pc21/special/hr/
    もし作業中に「配列の一部を変更できません」が出ていたなら
    http://pc.nikkeibp.co.jp/pc21/special/hr/hr5.shtml
    のあたりが参考になると思います。
  • id:airplant
    既に回答受付終了していますが、、、
    >B列の空白になっている行を削除して別シートで月ごとに1年分集計したいのですが
    目的が月ごとの集計であれば、次のピボットテーブルにすれば、簡単に月ごとで集計できますが、データを整形することが目的でしょうか?

       金額の集計 
    日付

    日付を月ごとにグループ化
    もし、ブランクを完全に消したいときには、金額をページフィールドに持っていって、フィールドの設定で「ブランクは表示しない」を選べば、リストには出てこなくなります。

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

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

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

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