エクセルのデータ処理について…

質問

下記（下段テーブル）の様に商品ごとの増減率が表示されているデータがあるとします。
この増減率はリアルタイムで更新される為、その都度数値が変化します。

これを下記（上段テーブル）の様に、それぞれのグループ内で、ある一定数値以上（下記では黄枠の2.00%以上）の増減率差分ペア（増減率高の商品と増減率低の商品の差分ペア）を見つけ出し、それを上位順に6位まで表示させたいと考えています。

増減率ペアは『AとB』や『CとD』などグループ内でのみ構成される為、『AとC』や『CとG』などはグループが違うので例え増減率差分が2.00%以上でもペアで表示される事がないようにしたいのと、増減率自体がリアルタイムで更新される為、下記（上段テーブル）は上位6選がその都度変化するように作成したいのですが…
実際にはグループは30程度あり商品は100程度ありますが、各グループ毎の商品構成は変化しません。また増減率はリアルタイムで変化するので、下記（上段テーブル）では1位には高い方が『H』低い方が『G』となっていますが、状況によれば逆に高い方が『G』低い方が『H』になる場合もあります。
このようなデータ処理をエクセルにて行うには、具体的にどのような関数もしくはマクロを作成すれば良いでしょうか？

お知恵を拝借できれば幸いです。宜しくお願い致します。

回答No.11

SI299792

C28 はデータの最後、図の赤枠部分の事で、ここに１を入れておきます。

お礼

もう一つ、教えて頂いた関数はグループ内で複数のペアを抽出出来るようになっていますが、これをグループ内で最大の差分ペアを一つだけ抽出させたい場合（グループ1から最大差分ペアを1つ、グループ2からも最大差分ペアを1つ、グループ3からも最大差分ペアを1つ…のように）は大幅に関数を変えないといけませんか？

何度もすみません。

補足

お返事ありがとうございます。
最後にもう一つ、教えて頂いた関数で黄枠（D2）を1％にし、B9からE9までを下にコピーして9位までを作成したところ、9位の所のC12とD12がどちらもMになってしまうのですが、7位以降を作成する場合は、ただ単に下方向にコピーではダメなのでしょうか？

回答No.10

SI299792

グループ内に同じ値が出ることを想定してませんでした。
（同じ数字が出るようなサンプルを作ってほしかった）
G14: =IF(MATCH(1,$C15:$C24,0)+$C14>COLUMN(A1),$E14-OFFSET($E14,COLUMN(A14)-$C14,0)-ROW()/1000000000+COLUMN()/100000000000)
にして下さい。
49986 件以上データがあれば、正しく表示されませんが、そこまでデータはないと思います。

C24 は図の赤丸部分の事で、１グループの最大件数が増えた場合、変更の必要があります。

回答No.9

masnoske

[No1.] です．
増減率が計算式だと，増減率のセル値が変化したことをマクロで捕まえられません．セルに入力されている計算式は変化しませんから．
ですが，計算式の参照元のセル値は，別アプリで取り込んだ時に変化します．
なので，そのセル値が変化したことをマクロで捕まえればリアルタイム処理可能です．
もう1点，グループの列が結合されていますが，外すことは可能でしょうか．
マクロで結合セルを扱うのは面倒で，そのために余計なコードが必要になります．結合を外すというのは，B14にもB15にも1を入力するということです．
マクロ以外でも結合セルはフィルタが効かないので，不便ですよね．

回答No.8

SI299792

＞処理が速い
データ件数によるので何とも言えません。
データは何件ありますか。

書き忘れていましたが、数式が入っている場合、どんな数式なのか。計算の元になる、値の入るセルの位置が解らないと、VBA でのリアルタイムは不可能です。
関数による回答を上げます。

10件分なのでＦ～列をワークエリアに使います。目障りなら非表示にして下さい。
Ｃ列の最後（図ではC28 ）にダミーで１を入れて下さい。目障りならフォントの色を白にして下さい。
F14: =IF(B14="",F13,ROW()-14)
G14: =IF(MATCH(1,$C15:$C24,0)+$C14>COLUMN(A1),$E14-OFFSET($E14,COLUMN(A14)-$C14,0)+0.0001/ROW())
P14 までコピペ。下へコピペ。

B4～B9は数字の１～６にして、セルの書式設定で「位」を付けて下さい。
C4: =IFERROR(INDEX(D:D,SUMPRODUCT((LARGE(G$14:K$27,B4)=G14:K27)*ROW(G14:K27))),C4)
D4: =IFERROR(INDEX(D:D,SUMPRODUCT((LARGE(G$14:K$27,B4)=G14:K27)*COLUMN(G14:K27))+INDEX(F:F,SUMPRODUCT((LARGE(G$14:K$27,B4)=G14:K27)*ROW(G14:K27)))+7),D4)
E4: =IF(LARGE(G$14:K$27,B4)>=D$2,LARGE(G$14:K$27,B4),"")
下へコピペ。

※Ｅ列の数字には誤差があります。計算に使う時は
ROUNDDOWN(E4,4)
の様に切り捨てて下さい。

補足

お返事ありがとうございます。

教えて頂いた関数で、ほぼ思い通りの形になりました。
ただ、増減率の数字を変えみたところ、順位付けの商品名が左（増減率高）と右（増減率低）で左右同じものが表示される場合があったりするのはなぜでしょうか？
また、順位付けで7位以降作成するにはそのまま下方向にコピーで宜しいでしょうか？
あと、一番最初におっしゃっていた『この式は、１グループ最大10件を想定しています。もっとあるなら、C24 の数を増やして下さい。』ですが、今回の関数ではそこを変えるケースなどはありますか？因みにC28ではなくC24で合っていますでしょうか？

何度も何度もすみません。

回答No.7

bunjii

追加の回答が遅れました。
E4に次の数式を設定します。
=LARGE($H$14:$U$27,ROWS(E$4:E4))
C4に次の数式を設定します。
=INDEX($D$1:$U$27,SUMPRODUCT(($H$14:$U$27=E4)*ROW($H$14:$U$27)),1)
D4に次の数式を設定します。
=INDEX($A$1:$U$27,ROW($H$12),SUMPRODUCT(($H$14:$U$27=E4)*COLUMN($H12:$U12)))
C4:E4を選択して下へ必要数コピーすれば添付画像のような結果になります。

添付画像の差分マトリクスで値が0のセルは条件付き書式でフォントの色を白にしてあります。（ブランクではありません）
また、差分が2%以上のセルを条件付き書式で黄色に塗りつぶしました。

尚、回答No.6への補足で「処理が速い（重くならない）」については差分マトリクスが大きくなると自動再計算では処理が遅くなります。

回答No.6

SI299792

すみません。各グループの最大値だけ抽出すればいいと思っていました。

質問ですが、
(1)ワークエリアを使う方がいいかVBA がいいか。
(2)１グループ最大いくつか
　必要ワークエリアは、１グループ最大数＋１
　今回の例の場合、１グループ最大５件なのでＦ～Ｋ、６列必要です。
　VBA ならワークエリアは必要ありません。
(3)E14 以下はどのように数字が入りますか。
　（直接値が入るのか、数式が入っているのか）

補足

お返事ありがとうございます。
こちらこそいろいろご無理を言ってすみません。

まず（1）ですが、処理が速い（重くならない）のとリアルタイムに計算されるのであればどちらでも良いです。
（2）については、多くても10件程度です。
（3）については、数式が入っています。

以上、ご面倒をお掛けしますが宜しくお願い致します。

回答No.5

bunjii

回答No.4で画像添付に失敗しましたので画像のみ添付します。
順位表の数式は次回の回答で提示します。

お礼

ご回答ありがとうございます。
いろいろなアドバイス・ご指摘感謝致します。
なかなか奥が深く難しそうですね…
もう少し自分自身で勉強が必要ですね。

回答No.4

bunjii

回答No.2の追加です。
質問に添付の画像でB列のグループ1～4は夫々複数セルを結合していますので結合セルの性質をご理解頂けないと作業用テーブルの必要性が分からないと思います。
B14、B16、B20、B23に1～4までの数値が入力されており、B15、B17、B18、B19、B21、B22、B24、B25、B26、B27には値が存在しません。
「増減率ペアは『AとB』や『CとD』などグループ内でのみ構成される」と言う条件を満たすためには「グループ」と「番号」の組み合わせ要件をチェックする必要があり、B列のブランクセルを補完する必要があります。
回答No.2では作業テーブルでB列をG列で補完し、差分対象を10行、11行、12行に配置して差分マトリクスを使う方法になっています。
尚、回答No.2の添付画像では差分を小数で表示されていましたので％表示に変更した数表を添付します。
H14セルの数式 =($E14-H$13)*($G14=H$11)
H14セルを右と下へコピーすれば差分の一覧表ができます。
順位表（B3:E9)は差分一覧表からLARGE関数でE列へ抽出し、商品名はINDEX関数とSUMPRODUCT関数でマトリックス表の位置から商品名を抽出します。

回答No.3

SI299792

Ｆ～Ｈをワークエリアに使っていいですか。

Ｃ列の最後（図ではC28 ）にダミーで１を入れて下さい。目障りならフォントの色を白にして下さい。
F14: =IF(C14=1,MAX(OFFSET(E14,0,0,MATCH(1,C15:C24,0)))-MIN(OFFSET(E14,0,0,MATCH(1,C15:C24,0))),"")
G14: =OFFSET(D13,MATCH(MAX(OFFSET(E14,0,0,MATCH(1,C15:C24,0))),OFFSET(E14,0,0,MATCH(1,C15:C24,0)),0),0)
H14: =OFFSET(D13,MATCH(MIN(OFFSET(E14,0,0,MATCH(1,C15:C24,0))),OFFSET(E14,0,0,MATCH(1,C15:C24,0)),0),0)
下へコピペ。目障りなら非表示にして下さい。
この式は、１グループ最大10件を想定しています。もっとあるなら、C24 の数を増やして下さい。

B4～B9は数字の１～６にして、セルの書式設定で「位」を付けて下さい。
C4: =IFERROR(VLOOKUP(E4,F:H,2,0),"")
D4: =IFERROR(VLOOKUP(E4,F:H,3,0),"")
E4: =IF(LARGE(F:F,B4)>=D$2,LARGE(F:F,B4),"")
下へコピペ。

補足

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

増減率差分の数値によっては、1グループ内で2以上のペアが発生する場合（質問の画像でいうと、グループ4に2％以上の増減率差分ペアが3つ（2位のLとM、4位のJとM、6位のLとK（画像は手入力したので1つ記載漏れしており、2つになっています））もあるのですが、その場合はどのようにすれば良いのでしょうか？
教えて頂いたもので作成すると、1つのグループから最大値の1ペアのみしか表示されないようなのですが…

ご面倒をお掛けしますが、また御手隙の時にでも教えてやって下さい。

回答No.2

bunjii

>このようなデータ処理をエクセルにて行うには、具体的にどのような関数もしくはマクロを作成すれば良いでしょうか？
関数で処理するには作業用のテーブルを作成する必要があります。
添付画像のような方法で良ければ考え方について説明可能です。
マクロについてはVBAのコーディングが必要になりますので仕様書を作成することから始めてください。
何れにしても会社の事務処理を無料のボランティアに頼るのは甘えすぎかと過ぎかと思います。