EXCELの集計

質問

ピボットテーブルや「集計」メニューを使わないで、関数のみで集計する方法をお教えください。

・Ａ列に支払予定日、Ｂ列に金額が記載されている表があります。
・Ａ列の支払予定日は、カレンダー順ではなくランダムに出てきます。また、同じ日が何回も出てきます。
・この表は日々更新され、支払予定日は何日がでてくるか予想はできません。

支払予定日毎に合計金額が表示される表に展開したいと思っています。

ご回答よろしくお願いいたします。

回答No.5

AloneAgain · ベストアンサー

No.3 ＆ No.4です。

>=IF(COUNT(J:J)<ROW(A5),"",SMALL(J:J,ROW(A5)))
>で、十分なような気がするのですが、

はい、もう、おっしゃるとおりでございます（恥）。
作業列で日付を抽出しているのですから、単にその日付を SMALL関数で昇順に並べ替えればいいだけですよね。
No.4の LARGE関数で同じような例を出しておきながら気づかないところがさらに恥の上塗り・・。しかも 「自信あり」 って・・。
まったくもって意味のない数式でした。顔を洗って出直してまいります。

以下、見苦しい言い訳。
こちらの↓ご質問にも回答しましたのでご覧ください。
http://oshiete1.goo.ne.jp/kotaeru.php3?q=1455335
（ EXCEL　数式・関数でソートしたい ）

こちらの場合は、重複した日付もすべて表示しなければならないので、
作業列で日付（シリアル値）に10000を掛け、行番号をプラスして、同じ日付を区別しています。
こういうケースでは、（作業列の数値はA列の日付ではないので）SMALL関数だけでは日付を表示することはできません・・・できませんよね？ すっかり凹みきって断言できないワタシです(-_-;)
しかも「日付」だけではなく「品名」「個数」も表示しなければならないので、INDEX関数とMATCH関数を組み合わせています。

こういう「公式」が頭にあったのでついついこの質問でも INDEX関数！！と、もう条件反射で何も考えずに回答してしまいました。頭、硬直しています。
*
ついでと言ってはナンですが、こちらの↓ご質問
http://oshiete1.goo.ne.jp/kotaeru.php3?q=1454811
（ EXCELで表を抽出する ）

これも先ほどのソートと同じ方法でできると思います。
ただ、よくわからなかったのですが、抽出するのは年に関係なく（今年であろうが、去年であろうが）7月分すべてということなのでしょうか？
もしかすると今年分のデータしかないのかもしれませんが・・。

そうであれば、たとえば D1 に 7 と入力すれば、7月分だけが抽出されるようにするには、作業列に↓のような数式を入れ、
-------------------------------------
=IF(MONTH(A2)=$D$1,A2*10000+ROW(),"")
-------------------------------------
あとはソートのご質問に回答している INDEX関数を使えばできるのではないかと思います。
こちらは抽出する元データが A列とB列だけですから、
INDEX($A:$C の部分を INDEX($A:$B に変えて、右に下にコピーしてみてください。
（ 同じシートに抽出するのでしたら 数式の中の Sheet1! の部分も不要ですが ）
これまたトンデモないカン違いをしているかもしれませんが、以上、ご参考まで。
　　　◇
最後に、今回のご質問に関しては、ほんとうに申し訳ありませんでした。

お礼

何度もご回答いただき、誠にありがとうございます。
さらに複数の質問にお答えいただき、大変感謝しております。

色々と試行錯誤していたため、お礼が遅くなってしまいました。申し訳ございません。

今回は本当に勉強になりました。
EXCELって、スキルさえあれば、やろうと思ったことが、何でもできるんですね。
これからも精進していきたいと思います。今後もよろしくご指導ください。

（早速ですが、新しい質問をしています。QNo.1463149）

回答No.4

AloneAgain

No.3です。

え～っと、結局、元のご質問の件は解決したのでしょうか？
とりあえずそう理解しまして、ROW(A1)の意味だけをお答えします。
（と言っても、なぜ #VALUE!エラーが出たのかよくわからないのですが・・）
　　◇
ためしにどのセルでもいいので　=ROW(A1) と入れてみてください。
1 と表示されますよね？
ROW関数はお調べになったとおり 「行番号を返す」 関数です。
A1 の行番号は 1ですから 1が返るわけです。
次に、オートフィルを使って、その数式を下にコピーしてみてください。
数式は、=ROW(A2)、=ROW(A3)、=ROW(A4) ・・と変化し、セルに表示される値も 2、3、4・・と変わります。

これを数式で利用すれば、次のようなことが可能になるのです。
たとえば A1：A50 に数字が（重複なしに）ランダムに入っているとします。
これを関数を使って数字の大きい順に並べ替えるにはどうすればいいと思いますか？

いちばん大きな数字は =LARGE(A1:A50,1) という数式で求められます。
では C1 に =LARGE($A$1:$A$50,1) と入れて C50 までコピーするとどうなるでしょうか？
すべてのセルに同じ数式が入り、当然表示される値もすべて同じ（最大の数字）です。
これだと、いったん数式をコピーしたあと、最後の 1 の部分を 2、3、4、・・50 と一つずつ手で変更しなければなりませんよね？
こういう場合は C1に↓を入れ、下にフィルコピーすればいいのです。
---------------------------
=LARGE($A$1:$A$50,ROW(A1))
---------------------------
ROW(A1) の部分は、下にコピーすれば ROW(A2)、ROW(A3)・・と変化して、順に2番目に大きな数字、3番目に大きな数字・・が自動的に表示されます。

>それなら1としてはいけないのでしょうか？

これでおわかりになったでしょうか？
1のままの数式をコピーしても、1の部分は 2、3、4・・とは変わってくれないのです。コピーしたすべての数式を手で変更しなければなりません。
ROW関数を使えばその手間がなくなります。

ちなみに ROW関数と対にして覚えておくと便利なのが COLUMN関数です。
これは 「列番号返す」関数です。
先ほどの数式を C1、D1、E1・・と右方向にコピーするときは
C1に↓を入れ、右にコピーすれば同様の結果が得られます。
------------------------------
=LARGE($A$1:$A$50,COLUMN(A1))
------------------------------
要は、=ROW(A1)を下方向にコピーすれば、値が 1、2、3、・・と変わり、
=COLUMN(A1) を右方向にコピーすれば、値が 1、2、3、・・に変わる、ということです。

まとめ：
数式を下方向や右方向にコピーするだけで、数式の中の引数などを 1、2、3 ・・と変えたいときに、ROW関数や COLUMN関数を使います。

お礼

何度もご丁寧に説明いただき、本当にありがとうございます。

元の質問の件はばっちり解決しました。そしてROWの意味も、よく理解できました。
実は、こんなことは多分できないんだろうなと思っていたのですが、できてしまうなんて、非常に驚くと同時に感激しています。

最後にもうひとつだけお教えください。
No.3で回答いただいたE列に入力する式ですが、
=IF(COUNT(J:J)<ROW(A5),"",SMALL(J:J,ROW(A5)))
で、十分なような気がするのですが、なぜ再度A列まで読みに行く式にされているのでしょうか？

本当に何度もすみません。よろしくお願いいたします。

回答No.3

AloneAgain

こんにちは。

>自動的に、この日付にはこの金額、ということができないか

要するに、A列から（重複なしで）日付を自動的に抽出し、その日付ごとの合計金額を出せばいいのでしょうか？

以下のようにすれば、
いちいちＡ列から1個ずつ日付を拾い出して手入力するような作業は必要ありません。
自動的に日付が抽出されます。

■作業列を使う方法です。
作業列はどの列を使用してもかまいません。
ここでは例として J列を使います。

A列の日付が 2行目から始まっているとすれば、
J2に↓
---------------------------------
=IF(COUNTIF($A$2:A2,A2)=1,A2,"")
---------------------------------
◆上の数式を、今後A列に日付が入るであろうと予測される最大行までコピーしてください。
（ A500 が最大行だと思えば、J500 までコピー）
作業列が目障りなら非表示にしてください。

◆E列に A列の日付を抽出し、F列にその日付ごとの合計金額を出します。
E2に↓ （E列の表示形式を 「日付」 にしておいてください）
---------------------------------------------------
=IF(COUNT(J:J)<ROW(A1),"",INDEX(A:A,MATCH(SMALL(J:J,ROW(A1)),J:J,0)))
---------------------------------------------------
作業列を J列と仮定しています。
J列以外の列を作業列に使う場合は、数式の J:J （3ヶ所）をその列記号に変更してください。
ROW(A1) の A1（2ヶ所）は、この数式をどのセルに入れるかに関係なく、A1 のままにしておいてください。

F2に↓
-------------------------------
=IF(E2="","",SUMIF(A:A,E2,B:B))
-------------------------------
◆E2 と F2 の数式を、予測されるA列の最大行まで（500行と思えばＥ500、F500まで）オートフィルでコピーしてください。
正しくは、重複する日付を1個としてその最大数分ですが、まあ、念のため・・。
　　　◇
以上で、A列の日付が（重複なしに）E列に昇順に自動抽出され、F列にその日付ごとの
合計金額が表示されます。

お礼

こんなに詳しくご回答いただき、誠にありがとうございます。
=IF(COUNTIF($A$2:A2,A2)=1,A2,"")
は、見事にうまくいきました。
しかし、
=IF(COUNT(J:J)<ROW(A1),"",INDEX(A:A,MATCH(SMALL(J:J,ROW(A1)),J:J,0)))
のところが、#VALUE!になってしまうんです。
ためしに、=SMALL(J:J,ROW(A1)だけにしてみましたが、同様にエラーになります。
お手数ですが、お返事いただきたくお願いいたします。

ところで、ROW(A1)には何の意味があるのですか？ヘルプをみてみましたがROWとは行番号を返す関数とのこと、それなら1としてはいけないのでしょうか？

補足

大変失礼いたしました。Ａ列で日付が入っていないセルがあり、そのためＪ列に#VALUE!が出てしまっているために、
E列にもエラーが出ていたようです。
J列の#VALUE!をなくしたら、うまくいきました。

ROWの意味は、教えてください。

回答No.2

onepanchan

(日付)　(合計金額)
　　A　　　　B　　
1　6/3　　\5,000
2　6/4　　\800
3　6/6　　\10,000
4　6/3　　\4,000

説明が分かりにくく失礼しました。
上記のようにA列にランダムに日付が現れて、
6/3の合計\9,000、6/4の合計\800、6/6の合計\10,000を各々自動で計算したい、
ということですよね？

これらを計算するには通常、以下のようにすれば計算できます。
=SUMIF(A:B,"6/3",B:B)
=SUMIF(A:B,"6/4",B:B)
=SUMIF(A:B,"6/6",B:B)

但し、これだと"6/3"の日付の箇所は、手動で日数分入力する必要があります。

そこで、集計表にある日付を使って数式を作ると、
以下のようになります。
これだとセルをコピーして貼り付けるだけで、"D1","D2", ・・・のように
自動でエクセルが数式を修正してくれます。
「日付がＤ２に」というのは集計表での日付のことでした。

　(日付)　(合計金額)　　　　 (結果)
　　D　　　　E　　
1　6/3　 =SUMIF(A:B,D1,B:B)　\9,000
2　6/4　 =SUMIF(A:B,D2,B:B)　\800
3　6/5　 =SUMIF(A:B,D3,B:B)　\0
4　6/6　 =SUMIF(A:B,D4,B:B)　\10,000

補足

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

D列には、あらかじめ日付が入っているという前提ですよね？
この日付も自動で入れたいと思っているのですが、無理な相談でしょうか？

回答No.1

onepanchan

Ａ列に支払予定日、Ｂ列に金額が記載されている表で
支払予定日毎の合計金額を計算するには以下のようにします。
(例：日付がD2セルにある場合)

=SUMIF(A:B,D2,B:B)

補足

ご回答ありがとうございます。
説明不足だったようで、すみません。
「日付がＤ２に」とのことですが、日付はランダムなのであり、何が出てくるのか想定できないのです。
自動的に、この日付にはこの金額、ということができないかと思っています。