【スプレッドシート】SUMPRODUCT関数を使って、行・列で合致した値を取得する。
今回は、SUMPRODUCT関数を使って、行方向・列方向で合致した交点の値を取得していきたいと思います。
SUMPRODUCT関数とは、2つの同サイズの配列または範囲にある対応する要素の積を計算し、その合計を返してくれる関数です。この記事では、少し違った使い方をするので、より詳しい使い方については省かせていただきます。
具体的にやりたいことは、
- 行方向(左右方向)に「銘柄」および「合計」から検索
- 列方向(上下方向)に「日付」から検索
- 合致した値を取得し表示する
です。
完成イメージ
それでは作っていきましょう！
銘柄および合計のプルダウンリスト
まずは銘柄と合計から選択できるようにプルダウンリストを作ります。
- セルD2を選択した状態で、
- 「データ」タブを選択して、
- 「データの入力規制」を選択
前回カレンダーを作った時は、「リストを直接指定」に変更しましたが、今回は「リストを範囲で指定」のまま、テキストボックスの中の「データ範囲を選択」をクリック。
すると、次のような画面が出てくると思うので、
ここで、「データ」のシートに移動し、銘柄および合計を選択し、「OK」、「保存」。
(画像ではセルD1とD2を結合しているので２行選択されているように見えますが、１行目だけ選択されていれば大丈夫です。)
これでプルダウンリストができました。
SUMPRODUCT関数でデータを取得する
準備ができましたので、SUMPRODUCT関数を使って、指定の年月・銘柄の値を取得し表示していきましょう。
まずは説明の前に形だけ作ってしまいます。
セルB6に次のように入力し、H6までドラッグしてコピーします。
- B6 → =sumproduct( ('データ'!$A$3:$A$300=date($B$2,$C$2,day(B5)))*('データ'$B$1:$D$1=$D$2),'データ'!$B$3:$D$300)
B6〜H6をコピーして、8、10、12、14、16行目にも入力します。
……あれ？ 思った通りの数字が表示されていませんね。
これは前回カレンダーを作った時に、日付の下に新しく行を挿入していました。
すると、表示形式まで同様に適用されるので、日付として出力されてしまいます。
なので、６行目を全て選択した状態で、「表示形式」、「数字」、を選択し、「自動」もしくは「数値」などに変更します。
これで正しく表示されたので、他の行も同じく変更しておきます。
それでは、SUMPRODUCT関数でどのような計算がされているかを見ていきます。
今セルB6には次のように入力されています。
=sumproduct(
('データ'!$A$3:$A$300=date($B$2,$C$2,day(B5))) *
('データ'$B$1:$D$1=$D$2) ,
'データ'!$B$3:$D$300
)
分かりやすくすると、
=sumproduct( (列方向に検索) * (行方向に検索) , 検索範囲 )
このような構成になっています。
また、それぞれ検索の内容として、
(列方向に検索) → 「データ」シートの日付＝そのセルの日付
(行方向に検索) → 「データ」シートの銘柄および合計＝プルダウンリストで選択したもの
になっています。
よって、列方向に検索して合致した日付、行方向に検索して合致した銘柄を、検索範囲の中の交点から取得します。
前後の月の時は空白にする
最後に、カレンダーを作った時のように、前後の月の損益については空白になるようにします。
先ほど入力したものにIF関数を追加します。SUMPRODUCT関数の中身は変わらないので省略します。
- B6 → =if(B5="","",sumproduct(省略))
このように日付が空白(前後の月)の時は、損益も空白になっていればOKです。
お疲れ様でした！
SUMPRODUCT関数を使った値の取得は以上になります。
あとはお好みの見た目にレイアウトを変更してみてください。
私はこれに加えて、週ごと、曜日ごとの小計を追加しましたので、完成したものを載せておきます。
次回は、データを集計して見やすくまとめるシートを作っていきたいと思います。
第１回 データ
第２回 カレンダー１
第３回 カレンダー２
第４回 集計
第５回 グラフ
