スキルアップ・問題解決なら、モーグにおまかせ！

お世話になります
 
よくa列に名前1行目に項目といったオーソドックスなデータ表がありますが
項目別にとか名前別にデータを抽出する方法はわかるのですが
 
データの中で数値が大きい順に名前と項目、数値を抽出する関数とかってあるのですか？

サンプル的な表を掲示するなど、具体的なデータで質問したほうが
回答しやすいと思います。

お世話になります
助言頂いた通り簡単ではございますが下記にデータを入れました
 
　　1　　　2　　　3　　4　　　　5
A名前　　国語　　数学　理科　　社会
B鈴木　　　78　　89　　56　　　66
C佐藤　　　69　　75　　91　　　88
C山田　　　70　　72　　49　　　78
 
上記データを元に逆抽出をしたいです
 
例えばこの中で最高数値順に抽出したいとします
抽出結果を下記のようにしたいです
 
佐藤　理科　91
鈴木　数学　89
佐藤　社会　88
鈴木　国語　78
山田　社会　78
 
どんな関数を使用すればよいでしょうか？
お教え頂きたく

１．実際の表の広さは？(縦ｘ横）
　　広さは固定ですか？
 
２．数値は0以上ですか？　
　　マイナスもありますか？
 
３．数値に小数点はありますか？
　　ある場合、有効な下桁数は何桁ですか？
 
４．同値の場合、並び順に厳格な決まりがありますか？

間違えていたのでさらに修正します。
  
●PowerQueryアドインをインストールして使えるようにして、
ピボット解除と並べ替えをする方法があります。
   
PowerQueryアドインについては、
   
Office2010 PowerQuery アドイン
   
でGoogle検索してみてください。
   
   
ピボット解除については
   
PowerQuery 横に並んだ列を縦に　その他の列
   
でGoogle検索。
   
●関数の例。
  
　　　A　　 B　　 C　　 D　　 E　F　G　　　 H　　 I　　 J　　 K
1　名前　国語　数学　理科　社会　　　　　　　　名前　教科　得点
2　鈴木　　78　　89　　56　　66　　　　000304　佐藤　理科　　91
3　佐藤　　69　　75　　91　　88　　　　000203　鈴木　数学　　89
4　山田　　70　　72　　49　　78　　　　000305　佐藤　社会　　88
5　　　　　　　　　　　　　　　　　　　000405　山田　社会　　78
6　　　　　　　　　　　　　　　　　　　000202　鈴木　国語　　78
7　　　　　　　　　　　　　　　　　　　000303　佐藤　数学　　75
8　　　　　　　　　　　　　　　　　　　000403　山田　数学　　72
  
H2
=IFERROR(TEXT(MOD(AGGREGATE(14,6,($B$2:$E$5000&"")+ROW($B$2:$E$5000)/10000+COLUMN($B$2:$E$5000)/1000000,ROW(H1))*1000000,1000000),"000000"),"")
下方向・↓
I2
=IF(H2="","",INDEX(A:A,LEFT(H2,4)))
下方向・↓
J2
=IF(H2="","",INDEX($1:$1,RIGHT(H2,2)))
下方向・↓
K2
=IF(H2="","",INDIRECT(TEXT(H2,"!R0C00"),0))
下方向・↓

Sheet1
　　A　　B　　C　　D　　E　 F　G　　H　 I
1　名前 国語 数学 理科 社会　 佐藤 理科 91
2　鈴木　 78　 89　 56　 66　 鈴木 数学 89
3　佐藤　 69　 75　 91　 88　 佐藤 社会 88
4　山田　 70　 72　 49　 78　 鈴木 国語 78
5　　　　　　　　　　　　　　 山田 社会 78
6　　　　　　　　　　　　　　 佐藤 数学 75
7　　　　　　　　　　　　　　 山田 数学 72
8　　　　　　　　　　　　　　 山田 国語 70
9　　　　　　　　　　　　　　 佐藤 国語 69
10　　　　　　　　　　　　　　鈴木 社会 66
11　　　　　　　　　　　　　　鈴木 理科 56
12　　　　　　　　　　　　　　山田 理科 49
 
Sheet2
　　A　　 B　　　C　　　D　　　E　　F　 G
1　名前 国語　 数学　 理科　 社会　　 90.995
2　鈴木 77.998 88.997 55.996 65.995　 88.997
3　佐藤 68.997 74.996 90.995 87.994　 87.994
4　山田 69.996 71.995 48.994 77.993　 77.998
5　　　　　　　　　　　　　　　　　　 77.993
6　　　　　　　　　　　　　　　　　　 74.996
7　　　　　　　　　　　　　　　　　　 71.995
8　　　　　　　　　　　　　　　　　　 69.996
9　　　　　　　　　　　　　　　　　　 68.997
10　　　　　　　　　　　　　　　　　　65.995
11　　　　　　　　　　　　　　　　　　55.996
12　　　　　　　　　　　　　　　　　　48.994
 
Sheet2 において、
１．式 =Sheet1!B2-COLUMN(A1)/1000-ROW(A1)/1000 を入力したセル B2 を右に３列＆下に２行
￣￣オートフィル
２．式 =IFERROR(LARGE(B$2:E$4,ROW(A1)),"") を入力したセル G1 を下にズズーッとオートフィル
 
Sheet1 において、
３．式 =IF(ROW(A1)>ROWS(B$2:E$4)*COLUMNS(B$2:E$4),"",ROUND(Sheet2!G1,0)) を入力した
￣￣セル I1 を下にズズーッとオートフィル
４．次の各セル にそれぞれ右に示した式を何れも配列(CSE)数式としてパシーッと入力
￣￣G1: =IF(I1="","",INDEX(Sheet2!A$1:A$4,MAX(IF(Sheet2!B$2:E$4=Sheet2!G1,ROW(Sheet2!B$2:E$4)))))
￣￣H1: =IF(I1="","",INDEX(Sheet2!A$1:E$1,MAX(IF(Sheet2!B$2:E$4=Sheet2!G1,COLUMN(Sheet2!B$2:E$4)))))
５．範囲 G1:I1 のフィルハンドル掴んで下方にズズズーッとドラッグ＆ペースト
オ・シ・マ・イ

並び替えを関数で行う場合
EXCEL2021，Microsoft365なら
SORT関数
 
ただ
 
　　1　　　2　　　3　　4　　　　5
A名前　　国語　　数学　理科　　社会
B鈴木　　　78　　89　　56　　　66
C佐藤　　　69　　75　　91　　　88
C山田　　　70　　72　　49　　　78
 
⇑
この形式のままでは使えないので
一旦
鈴木    国語    78
鈴木    数学    89
鈴木    理科    56
鈴木    社会    66
佐藤    国語    100
佐藤    数学    75
佐藤    理科    8
佐藤    社会    88
山田    国語    70
山田    数学    72
山田    理科    49
山田    社会    78
佐々木    国語    25
佐々木    数学    30
佐々木    理科    50
佐々木    社会    89
山口    国語    45
山口    数学    25
山口    理科    98
山口    社会    30
⇑
リスト形式の表に変換が必要
 
※元データはテーブル化して
  
=LET(データ,SUBSTITUTE(" "&FILTERXML("<a><b>"&TEXTJOIN(" </b><b> ",,テーブル1[名前]&" "&テーブル1[[#見出し],[国語]:[社会]]&" "&テーブル1[[国語]:[社会]])&" </b></a>","//b")&" "," ","s",SEQUENCE(,3)),文字,LEFT(MID(データ,FIND("s",データ)+1,99),FIND(" ",MID(データ,FIND("s",データ)+1,99))-1),SORT(IFERROR(--文字,文字),3,-1))
  
  
LET関数での変数定義が殆どで、実際の計算式の部分は
  
SORT(IFERROR(--文字,文字),3,-1)
⇑
これだけです
 
こんな形でも
 
=LET(Tn,テーブル[名前],Tm,テーブル[[#見出し],[国語]:[社会]],Hi,SEQUENCE(ROWS(Tn)*COLUMNS(Tm),3),SORT(CHOOSE(MOD(Hi,3)+1,INDEX(テーブル[[国語]:[社会]],INT((Hi-2)/12)+1,4-MOD(Hi,4)),INDEX(Tn,INT(Hi/12)+1),INDEX(Tm,,4-MOD(Hi+1,4))),3,-1))
 
計算式自体は最初のより単純だけど、INDEXでの抽出計算がややこしい
 

お世話になっております。
時間がとれなくて少しずつトライしていました。
結果からいうと
パワークエリアドインは使えないみたいなのでMikeさんの計算式を試してみました。
masat1さんのはわからなかったので試していません。すんません。
 
お教え頂いた通りにできましたが
Mikeさんのシート1A1:e5が9月分のデータだとして
一行空いて10月分がその下に入ることになります。
シート2も同様です
 
当然、参照範囲も置き換えました。
しかしG列のみどうしてもエラーが出ます
#Ref!
 
本来、参照範囲を置き換えるだけで問題ないとは思うですが
何か置き換えだけでは駄目な箇所はあるのでしょうか？

お教え頂いた通りにできましたが
シート1A1:e5が9月分のデータだとして
一行空いて10月分がその下に入ることになります。
そういうことは最初から仰るべきです。
 
下図は従前と同じ手法による結果のみ。
　　A　　B　　C　　D　　E　 F　G　H　　I　 J　K　L　　M　 N　O　P　　Q　 R
1　名前 国語 数学 理科 社会 期　　　 9月分　　　　 10月分　　　　 11月分
2　鈴木　 78　 89　 56　 66 9　　佐藤 理科 91　 山田 国語 91　 鈴木 数学 87
3　佐藤　 69　 75　 91　 88 月　 鈴木 数学 89　 山田 社会 88　 佐藤 社会 83
4　山田　 70　 72　 49　 78 分　 佐藤 社会 88　 鈴木 理科 87　 佐藤 国語 80
5　　　　　　　　　　　　　　　　鈴木 国語 78　 山田 数学 86　 山田 数学 78
6　鈴木　 75　 54　 87　 62 10　 山田 社会 78　 佐藤 社会 76　 鈴木 社会 77
7　佐藤　 62　 58　 65　 76 月　 佐藤 数学 75　 鈴木 国語 75　 鈴木 国語 69
8　山田　 91　 86　 69　 88 分　 山田 数学 72　 山田 理科 69　 鈴木 理科 67
9　　　　　　　　　　　　　　　　山田 国語 70　 佐藤 理科 65　 佐藤 理科 59
10 鈴木　 69　 87　 67　 77 11　 佐藤 国語 69　 佐藤 国語 62　 山田 社会 57
11 佐藤　 80　 55　 59　 83 月　 鈴木 社会 66　 鈴木 社会 62　 山田 国語 56
12 山田　 56　 78　 46　 57 分　 鈴木 理科 56　 佐藤 数学 58　 佐藤 数学 55
13　　　　　　　　　　　　　　　 山田 理科 49　 鈴木 数学 54　 山田 理科 46
 
Sheet2
　　A　　B　　　C　　　D　　　E　　 F G　 H　　I　 J　　K　 L
1　名前 国語　 数学　 理科　 社会　　　 9月分　　10月分　 11月分
2　鈴木 77.998 88.997 55.996 65.995　　 90.995　 90.996　 86.993
3　佐藤 68.997 74.996 90.995 87.994　　 88.997　 87.993　 82.990
4　山田 69.996 71.995 48.994 77.993　　 87.994　 86.996　 79.993
5　　　　　　　　　　　　　　　　　　　 77.998　 85.995　 77.991
6　鈴木 74.998 53.997 86.996 61.995　　 77.993　 75.994　 76.991
7　佐藤 61.997 57.996 64.995 75.994　　 74.996　 74.998　 68.994
8　山田 90.996 85.995 68.994 87.993　　 71.995　 68.994　 66.992
9　　　　　　　　　　　　　　　　　　　 69.996　 64.995　 58.991
10 鈴木 68.994 86.993 66.992 76.991　　 68.997　 61.997　 56.989
11 佐藤 79.993 54.992 58.991 82.990　　 65.995　 61.995　 55.992
12 山田 55.992 77.991 45.990 56.989　　 55.996　 57.996　 54.992
13　　　　　　　　　　　　　　　　　　　48.994　 53.997　 45.990

まさ@まさ さんへ、
 
私の以前(投稿日時:21/10/03 15:47:24)の投稿中に
》 ４．次の各セル にそれぞれ右に示した式を何れも
》 ￣￣配列(CSE)数式としてパシーッと入力
がありますが、「配列(CSE)数式」とは何のコッチャか分かっていますか？

配列数式は数式を入力後、シフトとコントロールとエンターを同時押しの事かとおもっているのですが。
{}が追加されます。

まさ@まさ さんの引用:
数式を入力後、シフトとコントロールとエンターを同時押しの事かと…

お世話になっています
 
例で数カ月分示していただきましたが、
2ヶ月目の数式をお教え頂けないでしょうか？
 
参照範囲を2ヶ月目はかえています。
恐らくそこで参照先がおかしいのかなと考えています。
2ヶ月目は前月と比べ参照範囲が違うので2ヶ月目になるように参照先を置き換えました。
例えばA1と書かれているところは参照範囲さきに置き換えたり等

Sheet2!J2: =IFERROR(LARGE(B$6:E$8,ROW(A1)),"")
 
Sheet1!L2: =INDEX($A:$A,MAX(IF(Sheet2!$B$6:$E$8=Sheet2!J2,ROW($B$6:$E$8))))
Sheet1!M2: =INDEX(Sheet2!$A$1:$E$1,MAX(IF(Sheet2!$B$6:$E$8=Sheet2!J2,COLUMN($B$6:$E$8))))
【お断わり】これら２式は必ず配列(CSE)数式として入力のこと。単にEnterのみで入力したら#VALUE!エラーを呈する。
Sheet1!N2: =ROUND(Sheet2!J2,0)

お世話になっています。
ご返信遅れすみません。
色々試しましたが
やはり
月マタギがどうしてもうまくいかずでした。
試行錯誤した結果、シート単位で抽出して
まとめシートを作ることとしました。
色々ご教授頂きありがとうございました

こんなのも。F列に「月」を追加して、
 
　　　 A　　 B　　 C　　 D　　 E　　 F　G　　H　 I　　　 J　　K　　 L　　 M　　 N　　 O
 1　名前　国語　数学　理科　社会　　月　　　　　　　　　　　　　　 月　名前　教科　得点
 2　鈴木　　78　　89　　56　　66　 9月　　 202　78　　9月4　304　 9月　佐藤　理科　　91
 3　佐藤　　69　　75　　91　　88　 9月　　 203　89　　9月2　203　 9月　鈴木　数学　　89
 4　山田　　70　　72　　49　　78　 9月　　 204　56　 9月11　305　 9月　佐藤　社会　　88
 5　　　　　　　　　　　　　　　　　　　　 205　66　 9月10　202　 9月　鈴木　国語　　78
 6　鈴木　　79　　95　　48　　99　10月　　 302　69　　9月9　405　 9月　山田　社会　　78
 7　佐藤　　95　　85　　53　　52　10月　　 303　75　　9月6　303　 9月　佐藤　数学　　75
 8　山田　　90　　79　　62　　98　10月　　 304　91　　9月1　403　 9月　山田　数学　　72
 9　加藤　　56　　88　　79　　60　10月　　 305　88　　9月3　402　 9月　山田　国語　　70
10　　　　　　　　　　　　　　　　　　　　 402　70　　9月8　302　 9月　佐藤　国語　　69
11　　　　　　　　　　　　　　　　　　　　 403　72　　9月7　205　 9月　鈴木　社会　　66
12　　　　　　　　　　　　　　　　　　　　 404　49　 9月12　204　 9月　鈴木　理科　　56
13　　　　　　　　　　　　　　　　　　　　 405　78　　9月5　404　 9月　山田　理科　　49
14　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　
15　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　
16　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　
17　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　
18　　　　　　　　　　　　　　　　　　　　 602　79　 10月8　605　10月　鈴木　社会　　99
19　　　　　　　　　　　　　　　　　　　　 603　95　 10月3　805　10月　山田　社会　　98
20　　　　　　　　　　　　　　　　　　　　 604　48　10月16　603　10月　鈴木　数学　　95
21　　　　　　　　　　　　　　　　　　　　 605　99　 10月1　702　10月　佐藤　国語　　95
 
H2
=IF(INDEX(A:A,(ROW(H2)-2)/4+2)="","",100*(INT((ROW(H2)-2)/4)+2)+MOD(ROW(H2)-2,4)+2)
下方向・↓
I2
=IF(H2="","",INDEX(A:G,H2/100,MOD(H2,100)))
下方向・↓
J2
=IF(H2="","",L2&COUNTIFS(I$2:I$900,">"&I2,$L$2:$L$900,L2)+1+COUNTIFS(I$1:I1,I2,L$1:L1,L2))
下方向・↓
K2
=IF(H2="","",INDEX(H:H,MATCH(L2&COUNTIF(L$1:L2,L2),J:J,0)))
下方向・↓
L2
=IF(H2="","",INDEX(F:F,H2/100)&"")
下方向・↓
M2
=IF(H2="","",INDEX(A:A,K2/100))
下方向・↓
N2
=IF(H2="","",INDEX($1:$1,MOD(K2,100)))
下方向・↓
O2
=IF(H2="","",INDEX(A:G,K2/100,MOD(K2,100)))
下方向・↓