Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 10全般 : Excel 2010)
データの抽出について
投稿日時: 21/10/02 23:19:54
投稿者: まさ@まさ

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

回答
投稿日時: 21/10/02 23:38:51
投稿者: WinArrow
投稿者のウェブサイトに移動

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

投稿日時: 21/10/03 00:19:49
投稿者: まさ@まさ

お世話になります
助言頂いた通り簡単ではございますが下記にデータを入れました
 
  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
 
どんな関数を使用すればよいでしょうか?
お教え頂きたく

回答
投稿日時: 21/10/03 07:27:40
投稿者: 半平太

1.実際の表の広さは?(縦x横)
  広さは固定ですか?
 
2.数値は0以上ですか? 
  マイナスもありますか?
 
3.数値に小数点はありますか?
  ある場合、有効な下桁数は何桁ですか?
 
4.同値の場合、並び順に厳格な決まりがありますか?

回答
投稿日時: 21/10/03 14:33:18
投稿者: んなっと

間違えていたのでさらに修正します。
  
●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))
下方向・↓

回答
投稿日時: 21/10/03 15:47:24
投稿者: Mike

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 において、
1.式 =Sheet1!B2-COLUMN(A1)/1000-ROW(A1)/1000 を入力したセル B2 を右に3列&下に2行
 ̄ ̄オートフィル
2.式 =IFERROR(LARGE(B$2:E$4,ROW(A1)),"") を入力したセル G1 を下にズズーッとオートフィル
 
Sheet1 において、
3.式 =IF(ROW(A1)>ROWS(B$2:E$4)*COLUMNS(B$2:E$4),"",ROUND(Sheet2!G1,0)) を入力した
 ̄ ̄セル I1 を下にズズーッとオートフィル
4.次の各セル にそれぞれ右に示した式を何れも配列(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)))))
5.範囲 G1:I1 のフィルハンドル掴んで下方にズズズーッとドラッグ&ペースト
オ・シ・マ・イ

回答
投稿日時: 21/10/15 19:57:22
投稿者: masat1@aol.jp
メールを送信

並び替えを関数で行う場合
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での抽出計算がややこしい
 

投稿日時: 21/11/15 01:00:18
投稿者: まさ@まさ

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

回答
投稿日時: 21/11/15 12:11:31
投稿者: Mike

お教え頂いた通りにできましたが
シート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/11/15 12:22:31
投稿者: Mike

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

投稿日時: 21/11/15 20:59:35
投稿者: まさ@まさ

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

回答
投稿日時: 21/11/15 21:53:51
投稿者: Mike

まさ@まさ さんの引用:
数式を入力後、シフトとコントロールとエンターを同時押しの事かと…
そういうことなら、なぜ「G列のみどうしてもエラーが出」るのが私も解せません。
#Ref!は「数式が有効ではないセルを参照する」、を念頭に置いて該当式を凝視&チェックして、間違い箇処を見つけるしかないかと。

投稿日時: 21/11/20 02:26:15
投稿者: まさ@まさ

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

回答
投稿日時: 21/11/20 08:11:14
投稿者: Mike

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))))
【お断わり】これら2式は必ず配列(CSE)数式として入力のこと。単にEnterのみで入力したら#VALUE!エラーを呈する。
Sheet1!N2: =ROUND(Sheet2!J2,0)

トピックに返信