データベースのレコードの合算

質問

エクセルの編集をしています。
表1.のような日付と商品の組み合わせごとのレコードからなる表を、表2.のように日付ごとに合算したいと思っています。

表1.
日付　商品A　商品B　商品C
1/2 　 2F
1/2 　　　　　3F
1/3　　　　　6G
1/3　5G
1/3 　　　　　　　　　-3F
　　　　　　↓
表2.
日付　商品A　商品B　商品C
1/2　　2F　　 3F
1/3　　5G　　6G　　 -3F

表1.の条件は、
・同じ日付で同じ商品の組み合わせはありません
・項目は「F」「G」の記号とマイナスの存在する数字の組み合わせです
・空欄になっているセルは空欄です

データベース関数、小計などを使ったのですがうまくいきませんでした。
何かアイデアをおねがいします。

回答No.6

kagakusuki · ベストアンサー

回答No.2です。
　もし表1の日付が順不同ではなく日付順(昇順でも、降順でもどちらでも可)に並んでいる場合には、作業列を使用しない以下の様な方法を使う事が出来ます。

　まず、Sheet2のA2セルに次の関数を入力して下さい。

=IF(A1="","",IF(COUNTIF(Sheet1!$A:$A,">"&SUM(A1)),LARGE(Sheet1!$A:$A,COUNTIF(Sheet1!$A:$A,">"&SUM(A1))),""))

　次に、Sheet2のA2セルの書式の表示形式を[日付]に設定して下さい。
　次に、Sheet2のB2セルに次の関数を入力して下さい。

=IF(OR($A2="",B$1=""),"",IFERROR(VLOOKUP("*?",OFFSET(INDEX(Sheet1!$B:$D,MATCH($A2,Sheet1!$A:$A,0),MATCH(B$1,Sheet1!$B$1:$D$1,0)),,,COUNTIF(Sheet1!$A:$A,$A2)),1,FALSE),""))

　次に、Sheet2のB2セルをコピーして、Sheet2のC2～D2のセル範囲に貼り付けて下さい。
　次に、Sheet2のA2～D2のセル範囲をコピーして、Sheet2のA列～D列の3行目以下に貼り付けて下さい。

　以上です。

お礼

ありがとうございます。
両アイデアとも正しく表示できました。
大変うまくいっております。

回答No.9

kagakusuki

回答No.2、6です。

　回答No.6の方法が成り立つための前提条件であった

>もし表1の日付が順不同ではなく日付順(昇順でも、降順でもどちらでも可)に並んでいる場合

という条件を無くし、表1の日付が順不同に並んでいる場合においても使う事が出来る方法です。
　尚、今回の方法の場合、もしも

>・同じ日付で同じ商品の組み合わせはありません

という条件が守られておらず、同じ日付で同じ商品の組み合わせが複数存在していた場合には「(重複あり)」と表示される様にしております。

　まず、Sheet2のA2セルに次の関数を入力して下さい。

=IF(A1="","",IF(COUNTIF(Sheet1!$A:$A,">"&SUM(A1)),LARGE(Sheet1!$A:$A,COUNTIF(Sheet1!$A:$A,">"&SUM(A1))),""))

　次に、Sheet2のA2セルの書式の表示形式を[日付]に設定して下さい。
　ここまでは回答No.2及び6と共通です。
　次に、Sheet2のB2セルに次の関数を入力して下さい。

=IF(OR($A2="",B$1=""),"",CHOOSE(MATCH(COUNTIFS(Sheet1!$A:$A,$A2,INDEX(Sheet1!$B:$D,,COLUMNS($B:B)),"*?"),{0,1,2}),"",INDEX(Sheet1!$B:$D,SUMPRODUCT((Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(9E+307,Sheet1!$A:$A))=$A2)*(INDEX(Sheet1!$B$1:$D$1,COLUMNS($B:B)):INDEX(Sheet1!$B:$D,MATCH(9E+307,Sheet1!$A:$A),COLUMNS($B:B))<>"")*ROW(Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(9E+307,Sheet1!$A:$A)))),COLUMNS($B:B)),"(重複あり)"))

　次に、Sheet2のB2セルをコピーして、Sheet2のC2～D2のセル範囲に貼り付けて下さい。
　次に、Sheet2のA2～D2のセル範囲をコピーして、Sheet2のA列～D列の3行目以下に貼り付けて下さい。

　以上です。

回答No.8

bunjii

>私の関数入力ミスかもしれませんが・・・。
大変申し訳ありませんでした。
数式のチェック漏れがありました。
貼付画像は再検証して数式を修正した結果です。
H2セルに次の数式を入力し、右と下へコピーしました。
=IF(SUM(INDEX(($A$1:$A$9=$G2)*(B$1:B$9<>""),0)),INDEX($A$1:$E$9,MAX(INDEX(($A$1:$A$9=$G2)*(INDEX($A$1:$E$9,0,MATCH(H$1,$A$1:$E$1,0))<>"")*ROW(G$1:G$9),0,0)),MATCH(H$1,$A$1:$E$1,0))&"","")

お礼

ありがとうございます。
完璧！だと思われます。
どこにも穴はございません。

回答No.7

bunjii

>実際の表1.にあたるデータは1000行30列くらいの量で、おおむね正しく表示されたのですが、一部うまくいかないところがありました。
「一部うまくいかない」と言う情報では対策案が見つかりません。
目的と異なる結果を具体的に示してください。

>使用で注意することがありますでしょうか。
INDEX関数の引数には配列データの扱いになりますので、自動再計算で動作が緩慢になるかも知れません。
データ量として1000行×30列は処理可能な範囲と思います。
数式の解説が必要でしたら理解できない点を明示してください。
=INDEX([範囲],[行番号],[列番号])
外側のINDEX関数は説明の必要が無いと思います。
[行番号]=MAX(INDEX(($A$1:$A$6=$F2)*(INDEX($A$1:$D$6,0,MATCH(G$1,$A$1:$D$1,0))<>"")*ROW(F$1:F$6),0,0))
この部分が分かり難いかも知れません。
「日付と商品の組み合わせで重複が無い」と言う条件から表2.の日付と商品の交点となるセルには該当するデータ数は1または0となります。
従って、目的の行番号は表1.の日付と表2.の日付が一致し、然も、表1.の商品が一致する列の空白でないセルの行番号を抽出すれば良いことになります。
従って、MAX関数の引数は次の数式で配列を返せば良いでしょう。
($A$1:$A$6=$F2)*(INDEX($A$1:$D$6,0,MATCH(G$1,$A$1:$D$1,0))<>"")*ROW(F$1:F$6)
しかし、配列値をMAX関数へ返すには数式を確定時にCtrl+Shift+Enterの打鍵が必要になりますので、それを回避するためにINDEX関数を介しています。
MAX(INDEX([配列],0,0))とすることでEnterキーのみで確定してもMAX関数へ配列値を返せます。
[列番号]=MATCH(G$1,$A$1:$D$1,0)
これは理解できますよね？

お礼

ご説明ありがとうございます。
式の意図は理解できました。
不具合の例として以下のようなのものがあります。
(本当は画像を添付したいのですが・・)

表1.
日付　商品A　商品B　商品C　商品D・・・
1/2 　　　　　1F　 　　　　
1/3　　　　　　　　　　　　　5G
1/3 　　　　　　　　　-3F
1/4　　　　　　　　　　　　　3F
1/5
　　　　　　↓
表2.
日付　商品A　商品B　商品C　商品D
1/2　　　　　1F
1/3　　　　　1F　　 -3F　　5G
1/4　　　　　　　　　　　　3F
1/5　　　　　　　　　　　　5G　

(商品、日付共に記載外にもデータがあります)

1/3の商品Bは存在しないのに「1F」が表示されてしまいます。
同じ列の1/2の1Fを拾っているのかなと思います。
1/5の商品Dは1/4を飛ばして1/3？・・・とういう感じです。
いずれも列内の問題のようです、同行に同文字はありません。

私の関数入力ミスかもしれませんが・・・。

回答No.5

bunjii

表1のデータは文字列として解釈します。
同一シートで検証してみました。
貼付画像のG2セルへ次の数式を設定して右と下へコピーします。
=INDEX($A$1:$D$6,MAX(INDEX(($A$1:$A$6=$F2)*(INDEX($A$1:$D$6,0,MATCH(G$1,$A$1:$D$1,0))<>"")*ROW(F$1:F$6),0,0)),MATCH(G$1,$A$1:$D$1,0))&""

表1のデータ範囲が大きくなったときは数式のセル範囲を広げてください。

お礼

ありがとうございます
一つの式でシンプルですごいと思いました。
実際の表1.にあたるデータは1000行30列くらいの量で、おおむね正しく表示されたのですが、一部うまくいかないところがありました。
式を理解できていないので解明できていませんが、使用で注意することがありますでしょうか。

回答No.4

chie65536（@chie65535）

追記。

作成したSheet2の表は、汎用性を重視し、行（日付）を増やしたり、列（商品）を増やしたり出来ます。

行を増やす場合は、単純に、最終行を１つ下の新規の行にコピーすれば良いです。

列を増やす場合は、単純に、D列をE列にコピー、E列をF列にコピー、のように、コピーすれば良いです。

お礼

ありがとうございます。
実際やってみますと、完璧なレコードと空のレコードが出現してしまいました。
出現パターンは・・・・さっぱりわかりません。
日付順に、2日間は表示されていて、３日間空欄。１日表示あり、２日空欄、・・・のような感じです。
項目が表示されている日付のレコードの内容は完璧で、日付ごとで必要な項目がすべて合算されています。一方で空のレコードはその日付けの項目はすべて空欄です。

回答No.3

chie65536（@chie65535）

Sheet1に「表１」があると仮定し、日付は昇順に並んでいると仮定します。

Sheet2の１行目を「表２」として、見出しにして、２行目から表を作ります。

１．Sheet2のA2セルに、以下の式を入力します。
=IF(OR(A1="",A1=MAX(Sheet1!A:A)),"",SMALL(Sheet1!A:A,COUNTIF(Sheet1!A:A,"<="&A1)+1))

２．Sheet2のB2セルに、以下の式を入力します。
=IF(ISNA(MATCH("",OFFSET(Sheet1!B$1,MATCH($A2,Sheet1!$A:$A,0)-1,0,MATCH($A2,Sheet1!$A:$A,1)-MATCH($A2,Sheet1!$A:$A,0)+1,1),-1)),"",INDEX(Sheet1!B:B,MATCH($A2,Sheet1!$A:$A,0)+MATCH("",OFFSET(Sheet1!B$1,MATCH($A2,Sheet1!$A:$A,0)-1,0,MATCH($A2,Sheet1!$A:$A,1)-MATCH($A2,Sheet1!$A:$A,0)+1,1),-1)-1,0))

３．Sheet2のB2セルを、C2、D2にコピーします。

４．Sheet2のA2～D2セルを、必要なだけ下方向にコピーします。

５．添付画像のように、表がまとまります。

回答No.2

kagakusuki

表1がどのシートのどのセル範囲に作表されているのかという事が不明ですので、今仮に、表1が存在しているシートがSheet1であり、その表1において「日付」と入力されているセルがA1セルであるものとし、Sheet2のA列～D列の1行目以下に表2を表示させるものとします。
　又、Sheet3のA列を作業列として使用するものとします。

　まず、Sheet3のA2セルに次の関数を入力して下さい。

=IF(AND(ISNUMBER(INDEX(Sheet1!$A:$A,ROW())),COUNTIF(INDEX(Sheet1!$B:$D,ROW(),),"*?")),INDEX(Sheet1!$A:$A,ROW())&"◇◆"&MATCH("*?",INDEX(Sheet1!$B:$D,ROW(),),0)&"◇"&ROW()&IFERROR(REPLACE(VLOOKUP(INDEX(Sheet1!$A:$A,ROW())&"◇◆*?",A3:INDEX(A:A,MATCH(9E+307,Sheet1!$A:$A)+1),1,FALSE),1,LEN(INDEX(Sheet1!$A:$A,ROW()))+1,),"◆"),"")

　次に、Sheet3のA2セルをコピーして、Sheet3のA3以下に貼り付けて下さい。

　次に、Sheet1のA1～D1のセル範囲をコピーして、Sheet2のA1～D1のセル範囲に貼り付けて下さい。
　次に、Sheet2のA2セルに次の関数を入力して下さい。

=IF(A1="","",IF(COUNTIF(Sheet1!$A:$A,">"&SUM(A1)),LARGE(Sheet1!$A:$A,COUNTIF(Sheet1!$A:$A,">"&SUM(A1))),""))

　次に、Sheet2のA2セルの書式の表示形式を[日付]に設定して下さい。
　次に、Sheet2のB2セルに次の関数を入力して下さい。

=IF($A2="","",IFERROR(INDEX(Sheet1!$B:$D,REPLACE(LEFT(VLOOKUP($A2&"◇*◆"&COLUMNS($B:B)&"◇*",Sheet3!$A:$A,1,FALSE),FIND("◆",VLOOKUP($A2&"◇*◆"&COLUMNS($B:B)&"◇*",Sheet3!$A:$A,1,FALSE),FIND("◆"&COLUMNS($B:B)&"◇",VLOOKUP($A2&"◇*◆"&COLUMNS($B:B)&"◇*",Sheet3!$A:$A,1,FALSE))+1)-1),1,FIND("◆"&COLUMNS($B:B)&"◇",VLOOKUP($A2&"◇*◆"&COLUMNS($B:B)&"◇*",Sheet3!$A:$A,1,FALSE))+LEN(COLUMNS($B:B))+1,),COLUMNS($B:B)),""))

　次に、Sheet2のB2セルをコピーして、Sheet2のC2～D2のセル範囲に貼り付けて下さい。
　次に、Sheet2のA2～D2のセル範囲をコピーして、Sheet2のA列～D列の3行目以下に貼り付けて下さい。

　以上です。

回答No.1

aokii

以下の方法でいかがでしょう。
F2セル：=IF(OR($A3=$A2,$A2<>$A1),"",A2)
G2セル：=IF($A2=$A1,G1&B2,TEXT(B2,""))
G2セルをI6セルまでドラッグコピー
K2セル：1/2
K3セル：1/3
L2セル：=VLOOKUP($K2,$F:G,COLUMN(G1)-COLUMN($F1)+1,FALSE)
L2セルをN3セルまでドラッグコピー

お礼

ありがとうございます。
すばらしいです、うまくいきました。
(L2セルの最後はFALSEではなくTRUEだと思われます)
なるほどこう考えるのですね。