VBAを使わずに解決したいです。
A列には日付が1000行入っており今後も3000行ぐらいまで増えていきます。
B列には金額が入っており1行おきから3行おきとランダムです。今後もランダムに入力されます。
B列の空白になっている行を削除して別シートで月ごとに1年分集計したいのですが
あらかじめセルに関数を埋め込んでおいて入力するだけで集計することはできないでしょうか？
よろしくお願いします。
No.4
814
232
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などで回避して下さい。
No.1
45529
20pt
他の方から回答が出れば私はポイント不要です。
結論から言うとVBA以外(シート関数だけ)では無理です。
なぜならシート関数は計算や検索する関数はあるのですが
Ｂ列の文字がなければ行間を詰める、という動的な関数はありません。
しかも今後もランダムに入力される、とういう条件なので計算式で予知する事も難しいと思われます。
かろうじて、オートフィルタからＢ列に対して「空白以外のセル」を指定すれば空欄を詰めて集計らしき表を表示する事は可能です。
この場合は別シートではなくデータシートページその物になりますが。
それとExcelの場合はバージョンを書かれた方が良いかも知れませんね。
バージョンが違うと関数が変わってきますので。
ご参考までに。
ありがとうございます。
バージョンはEXCEL2007を使っています。
No.2
12020
20pt
空白行の削除
フィルタを掛け、B列の空白のみを表示します。
空白行を選択し右クリック、「行の削除」を行えば空白行のみ削除できます。
「行の削除」でなく「削除」にならないように注意が必要です。
「削除」だとフィルタで隠れている金額有りの部分も削除してしまいます。
集計
DSUMを使えば月毎集計は関数で可能です。
ありがとうございます
DSUMで日ごとに集計していくとだいぶイメージに近いです
No.3
1314393
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 は実際の運用の上限に合わせて変更してください。
ありがとうございます
日付は出るのですが・・・
もうすこし試してみます
No.4
814232
ここでベストアンサー
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などで回避して下さい。
出来ました ありがとうございます
ちなみにお勧めできない理由を教えていただけないでしょうか
breakthrough1
2011/05/22 07:10:35
11
10
Mook
Lhankor_Mhy
1
1
1
出来ました ありがとうございます
ちなみにお勧めできない理由を教えていただけないでしょうか