結合セルを含む表からの集計結果を、別表に反映させたい

MATCH関数で対象を検索し、 ADDRESS関数とINDIRECT関数の組み合わせで、 SUM関数による集計を行う方法があります MATCH関数を使うと、指定セル範囲から対象を検索できます。その結果を使って、 ADDRESS関数で集計対象の開始と終了のセル番地を特定します。 INDIRECT関数でそれをセル範囲化して、 SUM関数で集計します。

	①左図が集計対象の表です A列の商品名が、結合セルに入力されているので、一般的な数式では集計することができません。集計するのは、各商品の入庫数と出庫数です。それぞれの合計を、もう一つの表に転記します。
	②商品名の検索には、MATCH関数を使います列固定のセル番地で指定することで、横方向への数式コピーでの流用を容易にします。
	③集計対象表の商品名のセル範囲を「検査範囲」として絶対参照で指定します
	④MATCH関数で取得できるのは、「検査範囲」での位置なので、それに1を足して、行番号にします作例では、2行分がセル結合されているので、MATCH検索の結果は1，3，5…のような奇数となります。項目行分の1を足すことにより、行番号を表すことができます。
	⑤ADDRESS関数を使って、集計範囲の始点セル番地にします集計範囲の始点セルはC列なので、「列番号」として3を指定して、セル番地を作ります。同様に、集計範囲の終点セル番地を、月の最大日数が31であることを考慮して、列番号33で作ります。
	⑥別シートでのセル範囲の指定は、シート名！始点セル番地：終点セル番地、の形で行うので、それを文字列の結合で作ります文字列の結合は、＆演算子で行えます。
	⑦INDIRECT関数を使って、セル範囲指定の文字列を数式で使えるように変換して、それをSUM関数の引数として指定しますこのSUM関数式で、MATCH関数で合致した商品の入庫数の合計を得られます。この数式を、出庫数のセルにコピーして使います。
	⑧コピー先でずれてしまうMATCH関数の「検査値」を修正し、さらに集計対照表の出庫数の行番号に合うようにADDRESS関数の行番号を+2に変えますこれで、MATCH関数で合致した商品の出庫数の合計を得られるようになります
	⑨在庫数を計算する数式を含めて選択し、フィルハンドルでまとめてコピーします選択中の3つの数式が、下方向に順次コピーされ、それぞれの商品の入庫数、出庫数、在庫数を集計できるようになります。
	⑩作例では、入出庫表は月別に用意されているので、他の月の集計を行う数式では、該当するシート名に変更します









	●結合セルを含む表から、必要なデータを集計して転記するための数式の作り方を、動画で解説します結合したセルを含む表は、一般的な数式では集計できません。本来は、集計方法を考えたうえで、表を設計するべきですが、そうでない場合でも関数を駆使すれば集計は可能です。数式を簡易にしたい場合には、表は単純にする必要があります。レイアウトに凝った表では、数式はどうしても複雑になります。

	関連する他のページ
	セルを結合しながら、効率的にレイアウトしたい結合セルはフィルハンドルでコピーできます他のシートのデータを転記して使いたいあらかじめデータ範囲に適当な名前をつけておけば、配列数式で簡単にできます毎月の日にちと曜日を自動入力したい数式で日にちを、表示形式で曜日を表示できます今日の日付と曜日を表示したい TODAY関数で得た日時を、表示形式を変更して、曜日付きの日付にします計算結果の0を非表示にしたい表示形式を「＃」にします計算結果が0になるとき、それが表示されないようにしたい表示形式で、値が0のときだけ白色で表示されるようにできます土曜日の日にちを青色で、日曜日の日にちを赤色で表示したい WEEKDAY関数を使った、条件付き書式を設定します日付と、それに対応する曜日を表示させたい年月日形式で日付を入力しておけば、表示形式でその曜日も表示できます条件に応じて数値を色分けしたい表示形式では、複数の条件による色分けも行えます 60を超える数値を赤色で強調したい単純な数値との比較による色分けくらいなら、条件付き書式を使わなくても、表示形式でも行えます郵便番号をハイフン付きで表示したい表示形式でユーザー定義することで、入力データに手を加えることなく、表示だけを変えられます日付と一緒に曜日を表示したい表示形式を変えることで、曜日も表示できるようになります年月日の日付データを、月日だけで表示したいユーザー定義で表示形式を変更しますセル値に文字列を追加したい表示形式を使えば、複数のセル値にまとめて文字列を追加できます翌日の時刻を『28:02』などと表記したい表示形式を「[h]:mm」とユーザー定義します 10桁の商品番号をハイフン付きで表示したい「セルの書式設定」→「表示形式」タブで、「000-000-0000」のようなユーザー定義を設定すれば、10桁の数値をハイフン付きで表示させることができます郵便番号や会員番号、日付の表示形式を整えたいユーザー定義すれば、ハイフン付きの表示や、表示桁位置の固定などを、データの修正なしに行えます金額の大小に応じて色分けしたい表示形式でも設定できます金額を千円単位で表示したい表示形式で桁区切りカンマの位置を変えると、千単位での数値表示に変えられます No.0～No.30の連番を正しく並べ替えたい表示形式で「No.」を付加した、数値の連番にしておけば、正しく並べ替えることができます 24時以上の時刻を表示したい表示形式を「[h]:mm」とユーザー定義しておきます日付を曜日付きで表示したい表示形式を変えれば、その日付の曜日も表示できます 300以上の数値を緑色で、300未満を紫色で表示したい単純な条件での色分けくらいなら、ユーザー定義の表示形式で可能です “0”を付加しないで、小数点の位置を揃えたいユーザー定義の表示形式で、桁プレースホルダの「?」を使えば、「0」が付加されないで、小数点の位置がそろうようになります小数点の位置を揃えたいユーザー定義の表示形式で、桁プレースホルダの「0」を使えば、小数点以下の桁数が統一されます計算結果の0を表示したくないユーザー定義の表示形式で、「ゼロの値」として、桁プレースホルダの「#」を指定すれば、0が表示されなくなります日付データを、月日と曜日の表示にしたい表示形式をユーザー定義することで行えます 1/1 をそのまま入力したいあらかじめ表示形式を「文字列」にしておくと、「1/1」が日付にならずそのまま入力されるようになります合計を求めるSUM関数式の結果に、「時間」を付けて表示したいユーザー定義の表示形式をセルに割り当てれば、「時間」付きで表示されるようになります日付を分解して表示したい同じ日付データでも、表示形式を変えれば、月だけや日だけの表示にできます月ごとにシートを用意してるから、日にちと曜日だけをセルに表示したいセルの表示形式をユーザー定義すれば、日にちと曜日だけの表示にできます特集「条件付き書式」特に要望の多い「条件付き書式」について解説します。直前の選択内容に応じた選択肢を表示したい」 INDIRECT関数で、直前の選択肢を『名前』として使います逆順の並べ替えを自動的に行いたい INDIRECT関数を使って、逆順の並べ替えを行う方法もあります別のシートに入力してあるデータを、隔行で表示しなおす INDIRECT関数を使って、偶数行もしくは奇数行にのみデータが表示されるようにします最大値の左側に記されている番号を知りたい連番になっているなら、MATCH関数だけで該当番号がわかりますプランに応じた規定料金を求めたい MATCH関数を利用すれば、プラン別の料金をVLOOKUP関数で求めることができます A列の同一データ間で、B列で前後30日間に該当する日付を調べるシリアル値で使われていない桁で、A列のデータを識別し、2つのデータをまとめたもので前後30日を判定します重複しない個数を求めたい COUNTIF関数を使った配列数式にします複数の検索データに合致するセル数を調べたい配列数式を使えば、COUNTIF関数で調べられます

結合セルを含む表からの集計結果を、 別表に反映させたい

MATCH関数で対象を検索し、 ADDRESS関数とINDIRECT関数の組み合わせで、 SUM関数による集計を行う方法があります

①左図が集計対象の表です

②商品名の検索には、MATCH関数を使います

③集計対象表の商品名のセル範囲を「検査範囲」として絶対参照で指定します

④MATCH関数で取得できるのは、「検査範囲」での位置なので、それに1を足して、行番号にします

⑤ADDRESS関数を使って、集計範囲の始点セル番地にします

⑥別シートでのセル範囲の指定は、シート名！始点セル番地：終点セル番地、の形で行うので、それを文字列の結合で作ります

⑦INDIRECT関数を使って、セル範囲指定の文字列を数式で使えるように変換して、それをSUM関数の引数として指定します

⑧コピー先でずれてしまうMATCH関数の「検査値」を修正し、さらに集計対照表の出庫数の行番号に合うようにADDRESS関数の行番号を+2に変えます

⑨在庫数を計算する数式を含めて選択し、フィルハンドルでまとめてコピーします

⑩作例では、入出庫表は月別に用意されているので、他の月の集計を行う数式では、該当するシート名に変更します

●結合セルを含む表から、必要なデータを集計して転記するための数式の作り方を、動画で解説します

関連する他のページ

結合セルを含む表からの集計結果を、別表に反映させたい