品名の出てきた順番で品名ごとの集計をする方法

Question

こんにちは

別のシートで入力したデータを、入力順に転記した集計表があります。

この表より

①　セルB　「品名」　の昇順ではなく、出てきた品名の順番

②　更に品名の中における　セルC　「サイズ」　ごと

で　セルI　「計」　の集計を出したいのですが、いろいろ調べても品名の昇順ごとの集計となってしまいます。

パソコン操作にあまり得意でない方が入力していくので、出来ればピボットテーブルではなく、関数で作成したいと思っています。

品名は固定されず、何がどういう順番で出てくるか分りません。

なので検索値には入れることが出来ないので困っています。

DSUM関数やCOUNTIFS関数と配列数式をつかったクロス集計等、試してみたのですが途中でつまづきました。

セルH以外には全て、リンク貼り付け等その他何らかの数式が入っています。

集計は出来れば別シートを考えています。

すみません。

どなたかご教授いただけると助かります。

宜しくお願い致します。

Answer 1

こんにちは。

最初に確認で申し訳ありませんが・・・。

①について：提示されている表は罫線でいくつかのブロックに分けられていますが、この罫線は無視して（空きの行も無視して）全ての品名をそのままの順番で処理するということで良いでしょうか？。

②について：品名とサイズの組み合わせが重複しない形にして、別シートに I 列（計）の集計を取りたいということですよね？。

その前提で、提示されている表のデータで集計を取ってみた結果が図１です。

＜図１＞

この表で良いとしてですが、ワークシート上で関数を使って処理したいとのご希望ですので、提示されている表の右側の J 列に「作業列」を設ける形で考えてみました。

• 以下、Sheet1 に提示されている表があり、Sheet2 に集計のための表（図１）を作ると仮定しています。
• Sheet2 には、最初にまず表の項目名（品名、サイズ、集計）を図１のように入力しておきます。

1. Sheet1 の作業列 J5 セルの数式。

=IF(B5&C5="","",IF(COUNTIFS(B$5:B5,B5,C$5:C5,C5)=1,ROW(A1),""))

この数式を下方向に必要なだけコピーしておきます。

最大で 1000 行を処理する場合には 1004 行目までコピーが必要になります。

以降の数式中のセル範囲（行方向）は、仮に 1004 としています。

＜動作概要＞

最初の IF 関数で、対象の B 列および C 列の値が両方とも空白（""）の場合に該当セルに空白（""）を表示します。

そうでない場合には、COUNTIFS 関数によって、対象の B 列および C 列の値と同一のセルの個数をカウントし、２番目の IF 関数と ROW 関数で個数が１個の行位置（ J5 セルを基準とした相対位置）を列挙していきます。

尚、個数が２個以上になる場合には空白（""）を表示していきます。

ここでは、COUNTIFS 関数のセル範囲の参照の仕方に注意してください。

これにより、B 列と C 列の組み合わせが最初に現れる行位置のみ列挙することが可能になります。

※ Sheet2 の表に表示されるデータの行数は、この作業列に列挙される数値の個数により決まります。

図２で、作業列に列挙される数値を確認してみてください。

今回の場合、図２に表示されていない作業列には全て空白（""）が表示されます。

＜図２＞

＜参考サイト＞

http://you-zou.com/pc/excel/unique-of-data/http://you-zou.com/pc/excel/unique-of-data/Sheet2 2. Sheet2 の A2 セルの数式

=IFERROR(INDEX(Sheet1!$B$5:$C$1004,SMALL(Sheet1!$J$5:$J$1004,ROW(A1)),COLUMN(A1)),"")

この数式を B2 セルにコピーし、A2 と B2 セルを選択したまま下方向に必要なだけコピーします。

＜動作概要＞

SMALL 関数と ROW 関数で、Sheet1 の作業列（ J5 以降）の内容を数値の小さい順に取り出して INDEX 関数に行番号として渡し、該当セルの値を表示していきます。

尚、COLUMN 関数で列番号を指定することで、数式の右方向へのコピーを可能にしています。

IFERROR 関数は余分なエラー表示を空白（""）に変えています。 3. Sheet2 の C2 セルの数式

=IF(A2&B2="","",SUMIFS(Sheet1!I$5:I$1004,Sheet1!B$5:B$1004,A2,Sheet1!C$5:C$1004,B2))

この数式を下方向に必要なだけコピーします。

（A 列および B 列と同一行数コピー。）

＜動作概要＞

IF 関数で、対象の A 列および B 列の値が両方とも空白（""）の場合に該当セルに空白（""）を表示します。

そうでない場合には、SUMIFS 関数で Sheet1 の B 列のセル範囲の値と Sheet2 の A 列の値を比較し、更に Sheet1 の C 列のセル範囲の値と Sheet2 の B 列の値を比較して、両方とも同一になる行位置を求め、Sheet1 の I 列の該当行の値を加算して表示します。

尚、３種類の数式が連携して動作しますので、手元の PC 環境では、1000 行程度でしたら１秒前後で処理が終わりましたが、10000 行の処理には最大８秒前後かかりました。

作業列の数式がネックになっていると思われます。

この点にご留意の上、よろしければお試しになってみてください。

余談ですが、処理する行数が 1000 行を超えるような場合に限って言えば、ここでご紹介した数式を使うよりもマクロ（連想配列）を使ったほうが処理速度は速いかと思います。

（1000 行と 10000 行の処理時間を比べてみただけですが・・・。）

Windows10Pro、Excel2016 の組み合わせで動作確認しています。

ご参考になれば幸いです。

Answer 2

お早うございます。

返信有難うございました。

こちらの返信も遅くなってすみません。

自分では関数では対処しきれず、結局ピボットテーブルと更新ボタンを作り操作するようにしていたのですが、内容を変更した時に入力担当者が更新ボタンを押すのを忘れたりして、どうしようかと思っていたところでした。

実は集計表（明細）とは違った書式の入力シートが複数あり、その集計だったので、ピボットテーブルだと全てが集計され、入力が間違っていたとしてもそれを特定するのが大変でした。

昨日、早速ご教授いただいた関数を使い作成したところ、まさに希望通りの集計でしたし、実際、入力ミスによりおなしな数値が出てきた時も、すぐに入力箇所を特定することが出来ました。

凄いです！

関数ではなかなか難しくVBAでないとダメなのかと諦めていました。

早速、運用開始しています。

有難うございます。

＜動作概要＞で解説していただいているので、それぞれの関数の使い方が分りやすく勉強になります。

今回一番ネックになったのは検索値の部分で、例えば品名のIEを入れて検索するといったものでなく、何という品名がどれだけでてくるかわからない為、検索値の入れ方がわからないというものでした。

検索値にいれた値で検索するといった概念しかなかったので、今回のような場合は、検索範囲・検索値共に固定するのではなく、各行のセルそのものを毎行検索値に入れ、更に検索範囲も検索する行までの範囲で指定することにより、品名を重複せずに出てきた順番でカウントすることが出来るという、自力で調べても絶対にわからない凄い素晴らしい使い方を教えていただき本当に感謝しています。

しかも品名の中の更にサイズごとという問題も、 'B5＆C5' とし複数条件で設定すればクリア出来るとは、なるほどです。

それ以降の検索/行列関数の組み合わせ方もとても勉強になりました。

臨機応変に関数を選ぶには、まだ経験も浅く、迷う部分が大半です。

なので、複数の関数を使用する場合には入れ子で入れていくのですが、上手くいかないことが多々あります。

実行が先で解析が後になり、返信も遅くなりまして大変失礼致しました。

まだ頭の中から溢れそうになっていますので、もう少し時間をかけてじっくりと解析し、これからに活かしていきたいと思います。

本当に感動・感謝しています。

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