【Excel2010】表にリンクさせたい

質問

C列のデータを表F2～I6へリンクさせたく、INDEX関数とMATCH関数を使用してみたのですが、エラーになってしまいました。
SUMIFS関数でしたらきちんと表示されましたが、同じ日付・記号のデータは絶対にありませんので、SUMIFS関数本来の使用方法ではありません。

どのような関数を使用したらよろしいのでしょうか？

※実際のデータは並び順が順不同で数ヶ月分あるため、A～Cは列全体を指定

回答No.2

msMike · ベストアンサー

F2: =SUMPRODUCT(($A$2:$A$21=F$1)*($B$2:$B$21=$E2)*($C$2:$C$21))

お礼

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

A～C列は行数指定をしない(A:A、B:B、C:C)とエラーになってしまいますね。
試しに「=SUMPRODUCT(($A:$A=F$1)*($B:$B=$E2),($C:$C))」にしてみたらエラーにならず、きちんと表示されました。
SUMIFS関数より数式の構造が分かりやすいので、SUMPRODUCT関数を使用したいと思います。

回答No.11

山田 太郎（@f_a_007）

【最終回答】クロス集計クエリ―を作成できる社員がいれば・・・

　質問の案件は、（SheetCopy()を利用すれば）１行のマクロで達成できます。一応、行数は1万でも2万でもOKかと思います。

１、クロス集計クエリ―を作成できる。
２、クロス集計クエリ―をテストする環境がある。
３、万一の場合、関数の不具合を修正できる。

　この３条件さえクリアすれば、１行のマクロでの達成も夢ではありません。

　しかし、クロス集計クエリは複雑。それを、ツラツラと宙で書くのは至難。だから、私は、Accessのウイザードが生成したものをコピペして回答しています。まあ、一度、作れば、後は、それを使い回せばいい訳ですが。そこまでもっていくのが七面倒臭い。そういう意味での、３条件です。

一応、クロス集計クエリ―の結果をシートに反映できる環境が整ったので締めの回答をしておきます。

お礼

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

>クロス集計クエリ―を作成できる社員がいれば
当方以上にExcelに詳しい部員が２人(ピボットを作った人間)しかいないのです。

マクロも「マクロの作成」からしか作れないので、編集は無理です。

回答No.10

HohoPapa

・INDEX関数とMATCH関数の組み合わせでやりたい
・A列、F1:I1は日付シリアル値
・作業列を使ってもよい
ということでよければ
添付画像のように、作業列を設け
処理するというのはいかがでしょうか。

D2=TEXT(A2,"YYYYMMDD")&B2
以下、必要数、下方向に複写

F2=IFERROR(INDEX($C:$C,MATCH(TEXT(F$1,"YYYYMMDD")&$E2,$D:$D,0),1,1),"")
これを集計先エリアに複写

お礼

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

MATCH関数を１つにしようとすると、別途作業列が必要になるのですね。
当方以外の人間が担当することになった場合に、おそらく理解してもらえないと思います。

回答No.9

山田 太郎（@f_a_007）

【お節介ついでに】現実的なクロス集計とは・・・

　添付図のようなのではと推察する。

１、集計期間をユーザーが指定できる。
２、集計タイプも選べる。
３、金額合計、件数合計等も指定できる。
４、以上を決めたら[コマンドボタン]をクリックする。

《コマンドボタンのVBAが行うこと》

　指示に従った、クロス集計を適当なセルの式にセットする。つまり、先に示した、クロス集計文を選択・完成して適当なセルにセットして実行を促す。

《シートで行うこと》

　クロス集計結果を各セルに表示する。これは予め、クロス集計結果を参照する式を書いておけば済むこと。だから、ユーザーのやることは、冒頭の１、２、３、４だけ。

　質問文をツラツラと眺めていると、後出しジャンケン大会の感じがする。質問の冒頭に、かかる構想全体を示すべきじゃーないのかな。「設計先にありき！」だと思うが・・・。なお、これ位だと、エクセル超初心者でも出来るんじゃーないのかな？コマンドボタンの配置要領も知らなかった爺だが、ネット検索すれば分かった。つまり、誰だって出来るんじゃーないのかな？

お礼

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

>後出しジャンケン大会の感じがする。質問の冒頭に、かかる構想全体を示すべきじゃーないのかな。
おっしゃる通りですが、決まったレイアウトがあるわけではなく、いろいろな資料のデータとして使用するので、とりあえず一覧表に出す方法だけでも知りたかったのです。

回答No.8

山田 太郎（@f_a_007）

【続：確認】実際のクロス集計は、週次、月次、四半期で行うのでは？

　つまり、

１、WHERE 節には、Between-And もいる。
２、GROUP BY 句もいる。
３、ORDER BY 句もいる。
４、PIVOTでは、週次、月次、四半期を指定する。

の４つは必須要件なのでは。でないと、実用的ではない。となると、これはもう、完全な《クロス集計》って考えをしないと駄目なのではと思いますよ。

《月次クロス集計》

TRANSFORM Sum(金額) AS 金額の合計
　　SELECT 記号, Sum(金額) AS [合計 金額]
　　FROM [Sheet8$A1:C100]
　　WHERE 日付 Between #1/1/2019# And #31/1/2019#
　　GROUP BY 記号 PIVOT
　　ORDER BY 記号
　　Format(, 'yyyy/mm')[Sheet8$A1:C100].日付;",,Chr(13))
PIVOT Format([Sheet8$A1:C100].日付, 'yyyy/mm');

《四半期クロス集計》

TRANSFORM Sum(金額) AS 金額の合計
　　SELECT 記号, Sum(金額) AS [合計 金額]
　　FROM [Sheet8$A1:C100]
　　WHERE 日付 Between #1/1/2019# And #31/1/2019#
　　GROUP BY 記号 PIVOT
　　ORDER BY 記号
　　Format(, 'yyyy/mm')[Sheet8$A1:C100].日付;",,Chr(13))
PIVOT "第 " & Format([日付],"q") & " 四半期";

PS、もしかしたらセル限界を越えないかも・・・

　週次、月次、四半期でのクロス集計に限定すれば、セル限界を越えないかもです。その場合は、VBAでシコシコは必要ないでしょう。

お礼

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

回答No.7

bunjii

>SUMIFS関数でしたらきちんと表示されましたが、同じ日付・記号のデータは絶対にありませんので、SUMIFS関数本来の使用方法ではありません。
SUMIFS関数を否定する理由が分かりません。
添付画像のF2:I6の集計はSUMIFS関数で十分です。（本来の使い方です）
B列の記号を単一化してE列へ関数で自動的に抽出するには作業用の列を使わないと難解な数式になりますのでお勧めできません。
また、A列の日付もF1から右へ抽出することも同様に複雑な数式になります。
A列、B列、C列の行数が多い場合は関数を使うと再計算の負荷が大きくなり実用にならないかも知れません。
レジの売り上げデータを集計するような業務と推測します。
集計作業の効率化は人件費の削減に繋がりますので専用のアプリを有償で作成することが妥当かと思います。（無償のボランティアをあてにしない）

お礼

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

>SUMIFS関数を否定する理由が分かりません
SUMIFS関数は指定した条件の金額合計を出す関数だと思っているので、今回のような単に表に当てはめるような目的に使用する関数ではないと考えたのです。

SUMPRODUCT関数でエラーになったらSUMIFS関数を使用します。

回答No.6

山田 太郎（@f_a_007）

【疑問】集計は月単位なのでは？

　だとすると、回答者が考える式も相当に違ってくるのでは？

TRANSFORM Sum(金額) AS 金額の合計
　　SELECT 記号, Sum(金額) AS [合計 金額]
　　FROM [Sheet8$A1:C20000]
　　WHERE 日付 Between #1/1/2019# And #31/1/2019#))
　　GROUP BY 記号
PIVOT 日付;

お礼

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

>集計は月単位なのでは？
基本はそうですが、日付指定や期間指定もあります。
ですので、日付を自由に変えられるようにピボットではなく関数で作成したかったのです。

回答No.5

山田 太郎（@f_a_007）

【補足】ピボットを利用しないクロス集計の問題点。

　添付図を見れば、確かに、成功しているかのように見える。が、これは、テストデータが少ないからです。ある程度の量のデータを処理すると、確実にセルの限界を越えます。ですから、クロス集計結果を一発で受け取る場合は、VBAで表を更新することになります。

１、受け取ったデータを行単位で配列変数に取り込む。
２、その配列変数の大きさを取得する。
３、For-Nextで１行づつ該当するセルに代入する。

　それに、１万行のクロス集計結果を表に反映すると、随分と時間を要すると思います。表の更新を一旦止めて代入作業。全部が終わったら表を表示。このようにしないと、「遅いなー！」なんてことになるかもです。

　テストをして見て、「ウン、な、何なんだ。この１クッションは？」と感じたので、チョイと補足しておきます。もちろん、ピボットが、この問題点をクリアしているかどうかは不明。でも、Access 流の集計を実現するには、VBAは必須とだけ補足しておきます。

お礼

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

>Access 流の集計を実現するには、VBAは必須
本当はAccessを使いたいです・・・
でも、当方以上にAccessに詳しい部員がいないのでExcelでやるしかないのです。

回答No.4

imogasi

私も＃１のご回答に賛成します。
その、「お礼」欄に挙げている理由はおかしい。メモ１枚作っておけば、初心者でも、ピボットテーブルは作れる。
関数でやると、いろんな関数の知識、カラクリの思い付きが要り、式の理解なども、普通の人には、できません。なんと式が複雑になることか。
＞表にリンクさせたい
という表題は、質問内容を予想できない。
他のことを予想してしまう。
特別な設定をしない場合は、エクセルワークシート関数は、すべてデータの変動にリンクするのだ。
内容は、画像を見れば一目でわかるけれども。
得ってみれば、クロス集計とでもいう考えだろう。
この質問の仕方を見ても、質問者は、まだエクセル関数や、エクセル利用経験が少ないのだろうと思う。
もう少し仕事や、エクセルを勉強して、ピボットの利用が、ビジネスで如何に有用か感じてほしい。

お礼

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

>エクセル関数や、エクセル利用経験が少ないのだろうと思う
関数に関しては、確かに簡単な関数しか使用してきませんでした。2～3年ぐらい前にINDEX＆MATCH関数やSUMIFS関数を知ったぐらいです。
ですが、ExcelはWin3.1時代から使用しています。今までこのような業務に関わったことが無かったので、難しい関数を必要としなかっただけです。

回答No.3

山田 太郎（@f_a_007）

【考え方だけ】

　質問者がやろうとしていることは、《クロス集計クエリ》ってやつです。Excel の場合には、次のように書きます。

TRANSFORM Sum(金額) AS 金額の合計
SELECT 記号, Sum(金額) AS [合計 金額]
FROM [Sheet8$A1:C13]
GROUP BY 記号
PIVOT [Sheet8$A1:C13].日付;

この実行結果は、

A;4;1;1;1;1|B;8;2;2;2;2|C;12;3;3;3;3

となります。もちろん、次のよう列名有り（先頭行）も可能です。

記号;合計金額;2019/01/01;2019/01/02;2019/01/03|A;4;1;1;1;1|B;8;2;2;2;2|C;12;3;3;3;3

どうしても、式一発で作表を完成したいんであれば、《クロス集計クエリ》を利用することになります。

お礼

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

Excelにもクロス集計があるのですね。
ただ、提示していただいた内容はさっぱり理解できません・・・申し訳ありません。

回答No.1

aokii

ピボットテーブルを使ってみてください。

お礼

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

実は元々ピボットテーブルで作成してありました。
しかし、ピボットテーブルを作成した社員以外は当方を含め誰も作成及び編集方法を知らないのです。
ですので、関数で自動的に表示できればと思った次第です。