エクセルのデータ処理

質問

お世話になっております。

　各時間帯、例えば、pm 0:00　から　pm 3:00まで等の合計損益（各損益はE列に載せられています）を求める方法におきまして、簡便な方法はございますでしょうか？

　ご教示いただければ幸いです。

回答No.4

KURUMITO · ベストアンサー

関数が複雑になってしまいますのでA列とB列の入力と表示は次のようになっているとします。
例えば2012年7月10日の22時15分ならば例えばA1セルでは2012/7/10 22:15 のように入力し、A2セルには2012/7/10 22:20のように入力します。A1セルとA2セルを選択してから右下のフィルハンドルにカーソルを置き下方にドラッグします。
その後にA列を選択してセルの表示形式で yyyy.mm.dd のすることで示されたような日付が表示されますね。直接2012.07.10のように入力されているのでしたら単なる文字列になっていますのでその後の対応が難しくなります。
B列についてはB1セルに次の式を入力して下方にドラッグコピーします。

=IF(A1="","",TIME(HOUR(A1),MINUTE(A1),0))

このようにすることでその後に処理を比較的簡単にすることができますね。
すでにA列のデータがお示しのように文字列として入力されており変更ができないとのことでしたら、全く別に作業列を作って対応することになります。
例えばH1セルに次の式を入力して下方にドラッグコピーします。

=IF(A1="","",SUBSTITUTE(A1,".","/")*1+B1

とことでお求めの表ですがシート2に作るとしてA1セルには開始日付、B1セルには開始時刻、C1セルには終了時刻とそれぞれ文字列を入力します。また、D1セルにはD列BUY件数、E1セルにはD列SELL件数、F1セルには合計損益とそれぞれ文字列を入力します。
そこで時間帯の指定ですが例えば2012/2/10 22:00 から翌日の3:00までならば、A2セルには2012/2/10と入力し、B2セルには22:00、C2セルには3:00とそれぞれ入力します。
D2セルには次の式を入力して下方にドラッグコピーします。

=IF($A2="","",COUNTIF(INDEX(Sheet1!$C:$C,MATCH($A2+$B2+"0:00:01",Sheet1!$A:$A,1)):INDEX(Sheet1!$C:$C,MATCH(IF($B2<$C2,$A2+$C2,$A2+1+$C2)+"0:00:01",Sheet1!$A:$A,1)),"BUY"))

E2セルには次の式を入力して下方にドラッグコピーします。

=IF($A2="","",COUNTIF(INDEX(Sheet1!$C:$C,MATCH($A2+$B2+"0:00:01",Sheet1!$A:$A,1)):INDEX(Sheet1!$C:$C,MATCH(IF($B2<$C2,$A2+$C2,$A2+1+$C2)+"0:00:01",Sheet1!$A:$A,1)),"SELL"))

F2セルには次の式を入力して下方にドラッグコピーします。

=IF($A2="","",SUM(INDEX(Sheet1!$E:$E,MATCH($A2+$B2+"0:00:01",Sheet1!$A:$A,1)):INDEX(Sheet1!$E:$E,MATCH(IF($B2<$C2,$A2+$C2,$A2+1+$C2)+"0:00:01",Sheet1!$A:$A,1))))

なお、シート1でH列に作業列を設けた場合には上の式でSheet1!$A:$AをSheet1!$H:$Hに変更して入力します。

お礼

ご回答どうもありがとうございました。

回答No.5

keithin

要するに、最初のグループが０時－３時で次のグループが３時－６時なら、３時はいったいどっちに数えるのですか？という事です。両方に入れちゃダメでしょ、判りますね？

で。
＞H4: 2012年度の当時間帯の利益

「２０１２年度の」とわざわざ書いたのはどうしてですか。
リストには２０１２年度の１年分のデータだけ既に並べてあるのか、それとも日付を見て２０１２年度をわざわざ抽出しないといけないのか、どういう事ですか。
そういう具体的なデータの姿が目に見えて来ないという事です。

いずれにしても既に回答したSUMIFS関数のただのバリエーションなので、そろそろご自分でも数式が書けるように勉強してください。

Ａ列に日付
Ｂ列に時刻
Ｅ列に金額

日付は問わずにとりあえず０時－３時
=SUMIFS(E:E,B:B,">=0:00",B:B,"<3:0")

日付を必要とする３時－６時
=SUMIFS(E:E,A:A,">="&DATE(2012,4,1),A:A,"<"&DATE(2013,4,1),B:B,">=3:0",B:B,"<6:0")

数式中の日付（年度）の数字や、「０：００」とか「３：００」「６：００」などの部分は、必要に応じて隣のセルに記入した内容を参照するなどして数式を作成してください。

この方法では、日付列と時刻列を「別に」並べておく必要があるので、他に寄せられている方法と混同しないように十分注意してください。

回答No.3

keithin

どこにどう結果を並べたいのか相変わらずナイショのままなので

エクセル２００７以降を使っているなら
Ｉ１セル以下に日付（2012/7/10）を記入
Ｊ１セルに
=SUMIFS(E:E,A:A,H2,B:B,">=12:0",B:B,"<=15:0")
と記入，以下コピー

するのが簡単です。

＃参考：
ピボットテーブルが使いたいなら，使ってみればそれなりの結果はでます。
ただし，pm0:00からpm2:59:59までの集計とかはすぐにできますが，15時まで含むみたいなのはあまり簡単にはできません。
何を計算したいのかよく考えてから行って下さい。

お礼

ご回答ありがとうございます。

補足

ナイショというわけではないのですが、例えば、

G4: 0:00-3:00（2:59でも構いません）; H4: 2012年度の当時間帯の利益

G5: 3:00-6:00（5:59でも構いません）; H5: 2012年度の当時間帯の利益

G6: 6:00-9:00（8:59でも構いません）; H6: 2012年度の当時間帯の利益

・
・
・

　といった風にまとめたく思っています。

回答No.2

KURUMITO

合計損益はF列に乗せられている損益を合計すればよいのでしょうか？
また、時間帯によってはF列に損益が載っていないケースもあるのですがどのように処理すればよいのでしょう。

お礼

どうもありがとうござました。

　可能でしたら、C列の”BUY” or "SELL"も一緒に載せたいのですが・・・

　はい。もしその時間帯が0でも一向に構いません。

　ご回答、感謝致します。

補足

補足失礼致します。

　ピボットテーブル等は、ふさわしくないのでしょうか？

　勝手なことを申し上げてしまい、ごめんなさい。

回答No.1

keithin

＞簡便な方法

最も簡単な方法は、
Ｆ１に
=A1+B1
以下コピー

どこにどう結果を並べたいのか不明なので

Ｉ１セルに日付（2012/7/10）を記入
Ｊ１セルに
=SUMIF(F:F,">="&(I1+"12:0"),E:E)-SUMIF(F:F,">"&(I1+"15:0"),E:E)

＃参考
ご利用のエクセルのバージョンが不明ですが、エクセル２００７以降を使っているならSUMIFS関数で一発で求めるのもありです。
ご相談投稿では、普段あなたが使っているソフトのバージョンまでキチンと明記することを憶えて下さい。

お礼

ご解凍どうもありがとうございました。

　申し訳ございませんでした。エクセルのバージョンは2007でございます。

　どうも申し訳ございませんでした。

補足

誠に説明不足で申し訳ございませんでした。

G4: 0:00-3:00（2:59でも構いません）; H4: 2012年度の当時間帯の利益

G5: 3:00-6:00（5:59でも構いません）; H5: 2012年度の当時間帯の利益

G6: 6:00-9:00（8:59でも構いません）; H6: 2012年度の当時間帯の利益

・
・
・

　といった風にまとめたく思っています。