Excel 項目別昇順関数

質問

皆様方の知恵をお借りに参りました。
あれこれ、サイト巡りして問題解決にあたりましたが、途方にくれています。
私のスキルは、入門者レベルで、至らない点は多くありますが、どうかお許しください。

まず、表をご覧ください。

D列からG列までの間に、データ入力をしております。実際は、200行分前後のデータあります。
D列、G列、K列、M列、O列、Q列は、「ユーザ定義」の設定において、「数値」にしてあります。
日付は、ランダムに入力しています。

これを、表のように、「車両費」など項目別に「日付」を検索して昇順させ、並べて
表示させたいのです。尚、中には、「車両費」の内訳が「空白欄」になっているものもあります。

さらに、「車両費」の内訳(ガソリン代・高速代など　---F列)を「項目別」にして、同じように
「日付」を昇順させ、表のように、「日付」「金額」のみ並べて表示させたいのです。
(データ入力の列をベースとしてください。)

お調べしましたところ、「配列数式」と呼ばれるものが必要なのかもしれません。
また関数は、INDEX関数、MATCH関数、SMALL関数などの組み合わせになるかしれません。

どなたか、表のように、表示させることのできる「関数」を教えてください。

回答No.4

web2525 · ベストアンサー

>データ入力を200行までと限定した場合、
>数式のどの部分を変更したら、よろしいのでしょうか？

列指定されている箇所

例えば
K11セルの場合だと
=IF(COUNTIF($E:$E,$L$10)>ROW()-11,OFFSET($A$1,SMALL(IF($E$11:$E$29=$L$10,ROW($E$11:$E$29),""),ROW(A1))-1,COLUMN(C:C)),"")

=IF(COUNTIF($E:$E,$L$10)>ROW()-11
この部分の
$E:$E
を
$E$11:$E$211　　（11行目から211行目）

後半部分
OFFSET($A$1,SMALL(IF($E$11:$E$29=$L$10,ROW($E$11:$E$29)

$E$11:$E$29　　（これは私のミスで本来は$E:$Eにする必要があった）
こちらも同様に
$E$11:$E$211

に変更、その他の計算式も変更箇所は同じです

お礼

web2525様、まことにありがとうございました。
とても助かりました。
後程、負荷軽減のため項目数を減らしたり、金額のみ表示させたり工夫してみるつもりです。
web2525様には心からのお礼を申し上げます。

回答No.5

matsu_jun

race77さん、こんばんわ

マクロで実行するのが楽ではありますが、どうしても数式で実現したい場合は作業列を利用して実現できなくはありません。

例の通り、11行目からデータが始まっているものとします。(別に11行目から始まっている必要はありませんが、1行目から実データが始まっていると多少厄介ではあります)
また、これも例の通り、L10セルとP10セルに、車両費だのガソリン代だのを入力するものとします。
（ここを変更すると結果が変わる）

S列からAA列までを作業列として利用します（この列には他にデータを入れないで下さい。また、この列が既に埋まっていて利用できない場合は、以下の数式を右にずらして利用してください）
実際のデータは200行前後とありますが、1000行目までデータが入っても良いように作ってあります。これ以上データが増える場合は、数式を読み込めば、増やす方法も分かるはずです。

K11セル（大費目の抽出対象の日付）
=IF(ISNA(X11),"",INDIRECT("T"&X11))
(K列全体に対して、D列の書式をコピーすること)
L11セル（大費目の抽出対象の小費目）
=IF(ISNA(X11),"",INDIRECT("U"&X11))
(L列全体に対し、条件付書式で、「セルの値が 次の値に等しい 0」の時、フォント色を白色」に設定
M11セル（大費目の抽出対象の金額）
=IF(ISNA(X11),"",INDIRECT("V"&X11))
(M列全体に対し、G列の書式をコピーすること)

O11セル（小費目の抽出対象の日付）
=IF(ISNA(AA11),"",INDIRECT("K"&AA11))
(O列全体に対して、D列の書式をコピーすること)
Q11セル（小費目の抽出対象の金額）
=IF(ISNA(AA11),"",INDIRECT("M"&AA11))
(Q列全体に対し、G列の書式をコピーすること)

S11セル（L10セル(車両費)に該当する元の表の行番号の取得）
=MATCH($L$10,INDIRECT("E"&(S10)+1):$E$1000,0)+S10
(行を増やしたい場合は、ここの$E$1000の"1000"を増やす)
T11セル（S列で求めた行番号に該当する日付の取得）
=IF(ISNA(S11),"",INDIRECT("D"&S11))
U11セル（S列で求めた行番号に該当する大費目の取得）
=INDIRECT("F"&S11)
V11セル（S列で求めた行番号に該当する金額の取得）
=INDIRECT("G"&S11)

TUV列は、DFG列から「車両費」に相当する項目を抜粋した表となります。
これをベースに、以下並び替えを行います。

W11セル（T列(日付)をRANK関数にて昇順に順位をつける）
=IF(T11="","",RANK(T11,T:T,1))
X10セルとY10セルには、「1」を入れてください。これを行わないとX列が上手く計算できません。
X11セル（T列(日付)を、値の小さい順に並べたときの行番号）
=IF(ISNA(MATCH(Y10,INDIRECT("W"&X10+1):$W$1000,0)+X10),MATCH(Z11,W:W,0),MATCH(Y10,INDIRECT("W"&X10+1):$W$1000,0)+X10)
(行を増やしたい場合は、1000 (2箇所あります) の値を増やす)
Y11セル（同じ日付で同じ項目が存在した場合を考慮した、同率○位 の表示 X列の計算に利用）
=INDIRECT("W"&X11)
Z11セル（同率を考慮しない場合の順位 X列の計算に利用）
=Z10+1

AA列は、OQ列を求めるための作業列となります。
AA11セル（P10セル(ガソリン代)に該当するKLMの表の行番号の取得
=MATCH($P$10,INDIRECT("L"&(AA10)+1):$L$1000,0)+AA10
OQ列の表は、KLMの表を参照しているため、既に並び替えができているので、これだけでOKです。

ここまで入力したら、まずはS11セルをクリックした後、S11セルの右下をつまみ、1000行目までドラッグします。T11セルについては、T11セルを選択した後、T11セル右下へカーソルを持って行き、カーソルの形状が変わったところでダブルクリック、U11以降も同様にします。
作業列の表示が邪魔であれば、S列からAA列までを非表示にするなり、フォントを背景と同じ色にすると良いでしょう。

お礼

＞実際のデータは200行前後とありますが、1000行目までデータが入っても良いように作っ
＞てあります。

ありがとうございます。将来は、データ入力200行は突破しますと思いますので
配列数式より作業列を利用した方がベターなのかもしれません。

matsu_jun様、感謝いたします。

回答No.3

web2525

No1です
では計算式で

K11セル
=IF(COUNTIF($E:$E,$L$10)>ROW()-11,OFFSET($A$1,SMALL(IF($E$11:$E$29=$L$10,ROW($E$11:$E$29),""),ROW(A1))-1,COLUMN(C:C)),"")

L11セル
=IF(COUNTIF($E:$E,$L$10)>ROW()-11,OFFSET($A$1,SMALL(IF($E$11:$E$29=$L$10,ROW($E$11:$E$29),""),ROW(B1))-1,COLUMN(E:E)),"")

M11セル
=IF(COUNTIF($E:$E,$L$10)>ROW()-11,OFFSET($A$1,SMALL(IF($E$11:$E$29=$L$10,ROW($E$11:$E$29),""),ROW(C1))-1,COLUMN(F:F)),"")

O11セル
=IF(COUNTIF($F:$F,$P$10)>ROW()-11,OFFSET($A$1,SMALL(IF($F:$F=$P$10,ROW($F:$F),""),ROW(D1))-1,COLUMN(C:C)),"")

Q11セル
=IF(COUNTIF($F:$F,$P$10)>ROW()-11,OFFSET($A$1,SMALL(IF($F:$F=$P$10,ROW($F:$F),""),ROW(G1))-1,COLUMN(F:F)),"")

微妙に計算式が違うので注意

すべて配列計算になるので、セル貼付け後に【Shift】+【Ctrl】+【Enter】で確定
各セルに貼り付けたらドラッグで下方向コピー

データがどこまで入力されるのか不明なので、計算範囲を列全体を指定しているので、とんでもなく再計算に時間がかかります

お礼

心より感謝申し上げます。

＞データがどこまで入力されるのか不明なので、計算範囲を列全体を指定している

データ入力を200行までと限定した場合、
数式のどの部分を変更したら、よろしいのでしょうか？

このような幼稚な質問をして、まことに申し訳ございません。

回答No.2

noname#192382

ツールを使ってできますが、いけないのでしょうか
DからGまで入力したところで、並べ替えを　Eを第1基準、Fを第2基準、Dをだい3基準にして並べ替えをしたらほぼ希望通りのものが出来ます。

お礼

アドバイス感謝します。

＞ツールを使ってできますが、いけないのでしょうか

実は、他にも項目があるがあるため、ツールは避けたいと思っています。

回答No.1

web2525

これはどうしても関数で処理しなければいけないのですか？

例えば：
フィルタ処理で抽出すれば関数を使わずに処理もできる

仮に：
【D～G列に入力したものを即時反映させる必要が有るため、計算式を利用して実現したい】
等の場合
配列計算式を多用した場合、データが増えると処理が重くなり作業効率が悪くなる可能性があります
（各セル入力後、最計算待ちで入力できない）

お礼

ありがとうごさいます。

＞これはどうしても関数で処理しなければいけないのですか？

はい、項目が多いため関数が必要と思っています。

＞配列計算式を多用した場合、データが増えると処理が重くなり作業効率が悪くなる可能性

これに関しては、実は心配している事でした。
本当に困まりました。