Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Excel 2016)
vlookupとlarge関数の組み合わせ。(続)
投稿日時: 20/11/26 21:27:37
投稿者: 高円寺の郷のもの

すみません。
以前に、
質問です。
例えば、
+−−−+−−−+−−−+
| 月 | 30 | あ |
+−−−+−−−+−−−+
| 火 | 20 | い |
+−−−+−−−+−−−+
| 水 | 50 | う |
+−−−+−−−+−−−+
| 木 | 10 | え |
+−−−+−−−+−−−+
  
とあり、
これをvlookupとlargeで2列目の数字の大きい順に並び替えたいのですが、
  
+−−−+−−−+−−−+−−−+
| 1  | 水 | 50 | う |
+−−−+−−−+−−−+−−−+
| 2  | 月 | 30 | あ |
+−−−+−−−+−−−+−−−+
| 3  | 火 | 20 | い |
+−−−+−−−+−−−+−−−+
| 4  | 木 | 10 | え |
+−−−+−−−+−−−+−−−+
  
というふうに並び替えたいのですが、
  
=VLOOKUP(LARGE($A$2:$B$10,E2),$B$2:$C$10,2,0)
  
と記述して書いても「う」は取れても「水」が取得できません。
  
どうすればよいか教えて下さい。
  
※並び替えは使えません。
 
という質問をした所、
 
「C6: =INDEX(A$1:A$4,MATCH(LARGE($B$1:$B$4,$B6),$B$1:$B$4,0))」
 
との回答が返ってきて、その場では、うまく行ったのですが、使ってみると、うまく動きません。
 
別案は無いでしょうか?
 
※もしかすると、
 
+−−−+−−−+−−−+−−−+
| 1  | 水 | 50 | う |
+−−−+−−−+−−−+−−−+
| 2  | 月 | 30 | あ |
+−−−+−−−+−−−+−−−+
| 3  | 火 | 20 | い |
+−−−+−−−+−−−+−−−+
| 4  | 木 | 50 | え |
+−−−+−−−+−−−+−−−+
 
という感じで、同じ数字が存在していると、うまく行かないのでしょうか?どなたか解決案をお願いいたします。

回答
投稿日時: 20/11/26 22:08:31
投稿者: んなっと

●OFFICE365であれば
 
   A  B   C D E   F  G   H
1 曜日 値 名前    曜日 値 名前
2  月 30  あ   1  水 50  う
3  火 20  い   2  月 30  あ
4  水 50  う   3  火 20  い
5  木 20  え   4  木 20  え
 
F2
=SORTBY(A2:C10,B2:B10,-1)
 
●SORTBYが使えない場合は少し面倒。
 
 

投稿日時: 20/11/26 22:11:55
投稿者: 高円寺の郷のもの

>んなっと様
ありがとうございます。
そうですね。使っているVerが2016でして・・・。。。

回答
投稿日時: 20/11/27 08:47:50
投稿者: んなっと

   A  B   C   D E   F  G   H
1 曜日 値 名前      曜日 値 名前
2  月 30  あ 2.002 1  水 50  う
3  火 20  い 3.003 2  月 30  あ
4  水 50  う 1.004 3  火 20  い
5  木 20  え 3.005 4  木 20  え
 
D2
=IF(B2="","",RANK(B2,B:B)+ROW()/10^3)
下方向・↓
F2
=IFERROR(INDEX(A:A,MOD(10^3*SMALL($D:$D,$E2),10^3)),"")
右方向・→下方向・↓

投稿日時: 20/11/27 10:38:08
投稿者: 高円寺の郷のもの

んなっと様
ありがとうございます。
どうもうまくいきません。。。
 
もう少し簡単な方法はございませんか?

回答
投稿日時: 20/11/27 11:13:50
投稿者: んなっと

引用:
どうもうまくいきません。。。

↑詳細を書かないのはなぜですか?
 
 
※値が正の整数だけなら
 
F2
=IFERROR(INDEX(A:A,10^3-MOD(10^3*AGGREGATE(14,6,TEXT($B$2:$B$500,"標準;;;")-ROW($2:$500)/10^3,$E2),10^3)),"")
右方向・→下方向・↓

回答
投稿日時: 20/11/27 15:26:10
投稿者: Mike

高円寺の郷のもの さんの引用:
その場では、うまく行ったのですが、使ってみると、うまく動きません。
Excelの使ひ手が、斯樣な「うまく動きません」だけの曖昧模糊にするのは止めて下さい。貴方が今回得た結果を全て(ったって大した量ではあるまいに!)書き出して下さい。

投稿日時: 20/11/27 15:51:38
投稿者: 高円寺の郷のもの

んなっと様、mike様
 
大変失礼いたしました。
失敗している状況を書いていたつもりで、そのまま送信してしまったいたようです。
不快な思いをさせてしまい、申し訳ありませんでした。
 
 
言葉で説明するのが難しいので、実際に類似したデータをアップロードいたしました。
https://dotup.org/uploda/dotup.org2318995.xlsx
 
N2には本来「あ」と出るべきどころが、なぜか「い」と表示されてしまいます。
N3には0とも表示されてしまいます。
 
こういう状況です。
 
今回は私のミスで大変ご迷惑をおかけし、申し訳ございませんでした。
 

回答
投稿日時: 20/11/27 15:52:36
投稿者: んなっと

Mikeさん、最初の式の不具合は一応書かれていますよ。

引用:
※もしかすると、
  
+−−−+−−−+−−−+−−−+
| 1  | 水 | 50 | う |
+−−−+−−−+−−−+−−−+
| 2  | 月 | 30 | あ |
+−−−+−−−+−−−+−−−+
| 3  | 火 | 20 | い |
+−−−+−−−+−−−+−−−+
| 4  | 木 | 50 | え |
+−−−+−−−+−−−+−−−+
  
という感じで、同じ数字が存在していると、うまく行かないのでしょうか?

 
   A  B   C D E   F  G   H
1 曜日 値 名前    曜日 値 名前
2  月 30  あ   1  水 50  う
3  火 20  い   2  月 30  あ
4  水 50  う   3  火 20  い
5  木 20  え   4  火 20  い ←「木」と「え」になってほしい

回答
投稿日時: 20/11/27 15:57:03
投稿者: んなっと

https://www.moug.net/faq/kiyaku.html#link4

引用:
禁止事項
ファイルを直接ダウンロードさせる
ファイルを直接リンクするURL、および同様のファイルをダウンロードさせることを目的としたフォルダやディレクトリ、サイトへのURLを、Q&A掲示板の発言内に記載することはご遠慮ください。
一般操作の説明は、操作方法または数式を記載し、マクロに関してはVBAコードを記載するようにしてください。

手を抜かないで、しっかり説明しましょう。
それができないなら、スレッドを閉じてください。

回答
投稿日時: 20/11/27 16:01:12
投稿者: んなっと

引用:
N2には本来「あ」と出るべきどころが、なぜか「い」と表示されてしまいます。
N3には0とも表示されてしまいます。

N列ではなく、H列です。
テスト用のブックを新規作成して、下のレイアウトに揃えましょう。
 
   A  B   C   D E   F  G   H
1 曜日 値 名前      曜日 値 名前
2  月 30  あ 2.002 1  水 50  う
3  火 20  い 3.003 2  月 30  あ
4  水 50  う 1.004 3  火 20  い
5  木 20  え 3.005 4  木 20  え

投稿日時: 20/11/27 16:12:51
投稿者: 高円寺の郷のもの

何度も申し訳ございません。
 
言葉で説明いたします。
 
実際のデータは、
 
「印刷用sheet」と「データー入力用sheet」があり、
 
「データ入力用Sheet」の方に
 
A列からK列まであり、
A列には連番
B〜D列には、文字列
E列に基準となってほしい数字
F列には数字
G列には文字列
H列には数字
I〜K列は文字列
 
とあります。
 
これを列の順番は狂わせないようにして、E列を降順に並び替えた状態で、「印刷用Sheet」に反映させたいのです。
 
※「データ入力用Sheet」には追記して書いていく予定なので、追記した文も同様に、「印刷用Sheet」に反映させたいのです。
 
つたない日本語になってしまいましたが、わかってもらえたでしょうか?

投稿日時: 20/11/27 16:15:06
投稿者: 高円寺の郷のもの

んなっと様
ありがとうございます。
 
数式は出来たら、N列以降に入れたいのです。
(本来は、別シートですが、、、)

回答
投稿日時: 20/11/27 16:15:57
投稿者: んなっと

繰り返します。そちらのレイアウトにあわせるつもりはありません。
まずはテスト用のブックを新規作成して、下のレイアウトに揃えましょう
そしてその結果を書き込んでください。
  
   A  B   C   D E   F  G   H
1 曜日 値 名前      曜日 値 名前
2  月 30  あ 2.002 1  水 50  う
3  火 20  い 3.003 2  月 30  あ
4  水 50  う 1.004 3  火 20  い
5  木 20  え 3.005 4  木 20  え

投稿日時: 20/11/27 16:26:31
投稿者: 高円寺の郷のもの

んなっと様
 
A列〜C列は直接手打ちで入力しました。
E2には、=IFERROR(INDEX(A:A,MOD(10^3*SMALL($D:$D,$E2),10^3)),"")を入力し↓まで持っていきました。
 
ですが、
 
F2 =IFERROR(INDEX(B:B,MOD(10^3*SMALL($D:$D,$E2),10^3)),"") を入力した所、「0」となってしまいます。
この状態で↓→へ持っていった所、同様に全て「0」になってしまいます。

投稿日時: 20/11/27 16:27:05
投稿者: 高円寺の郷のもの

この状態です。
 
曜日    値    名前            曜日    値    名前
月    30    あ    2.002    0    0    0    0
火    20    い    3.003    0    0    0    0
水    50    う    1.004    0    0    0    0
木    20    え    3.005    0    0    0    0

回答
投稿日時: 20/11/27 16:43:42
投稿者: んなっと

先にE2から下に1,2,3,4,...と連番を入力しておく前提ですよね?
 
   A  B   C   D E   F  G   H
1 曜日 値 名前      曜日 値 名前
2  月 30  あ 2.002 1  水 50  う
3  火 20  い 3.003 2  月 30  あ
4  水 50  う 1.004 3  火 20  い
5  木 20  え 3.005 4  木 20  え
 

引用:
E2には、=IFERROR(INDEX(A:A,MOD(10^3*SMALL($D:$D,$E2),10^3)),"")を入力し

E2ではないです。F2と書いたはずです。
 
F2
=IFERROR(INDEX(A:A,MOD(10^3*SMALL($D:$D,$E2),10^3)),"")

回答
投稿日時: 20/11/27 16:44:09
投稿者: んなっと

しっかりしてください。

投稿日時: 20/11/27 16:55:00
投稿者: 高円寺の郷のもの

曜日    値    名前            曜日    値    名前
月    30    あ    2.002    1    水    50    う
火    20    い    3.003    2    月    30    あ
水    50    う    1.004    3    火    20    い
木    20    え    3.005    4    木    20    え
 
出来ました!ここでは、できるんですよね、、、

回答
投稿日時: 20/11/27 16:56:22
投稿者: んなっと

おぉ、できましたか!
では、そちらの配置に揃えましょう。別シートでしたね。
もう一度複数の表形式でお願いできますか?

回答
投稿日時: 20/11/27 17:47:11
投稿者: んなっと

●「データ入力用」シート
 
   B  C  D  E  F  G  H  I  J  K   L
1 曜日 類1 類2 値 類3 類4 類5 類6 類7 類8   
2  月  C2  D2 30  F2  G2  H2  I2  J2  K2 2002
3  火  C3  D3 20  F3  G3  H3  I3  J3  K3 3003
4  水  C4  D4 50  F4  G4  H4  I4  J4  K4 1004
5  木  C5  D5 20  F5  G5  H5  I5  J5  K5 3005
 
L2
=IF(E2="","",RANK(E2,E:E)*10^3+ROW())
下方向・↓
 
●「印刷用」シート
 
   B  C  D  E  F  G  H  I  J  K
1 曜日 類1 類2 値 類3 類4 類5 類6 類7 類8
2  水  C4  D4 50  F4  G4  H4  I4  J4  K4
3  月  C2  D2 30  F2  G2  H2  I2  J2  K2
4  火  C3  D3 20  F3  G3  H3  I3  J3  K3
5  木  C5  D5 20  F5  G5  H5  I5  J5  K5
 
B2
=IFERROR(INDEX(データ入力用!B:B,MOD(SMALL(データ入力用!$L:$L,ROW(B1)),10^3)),"")
右方向・→下方向・↓

回答
投稿日時: 20/11/27 17:56:01
投稿者: Mike

んなっと さんの引用:
最初の式の不具合は一応書かれていますよ。
引用:
※もしかすると、…という感じで、同じ数字が存在していると、うまく行かないのでしょうか?
という感じ」のデータが前囘と異なって居た事に気附きませんでした。
んなっとさん、御指摘を感謝します。m(_._)m

投稿日時: 20/11/27 18:52:41
投稿者: 高円寺の郷のもの

おおお!!!!!出来ました!!!!ありがとうございました!!!助かりました!!
 

んなっと さんの引用:
●「データ入力用」シート
 
   B  C  D  E  F  G  H  I  J  K   L
1 曜日 類1 類2 値 類3 類4 類5 類6 類7 類8   
2  月  C2  D2 30  F2  G2  H2  I2  J2  K2 2002
3  火  C3  D3 20  F3  G3  H3  I3  J3  K3 3003
4  水  C4  D4 50  F4  G4  H4  I4  J4  K4 1004
5  木  C5  D5 20  F5  G5  H5  I5  J5  K5 3005
 
L2
=IF(E2="","",RANK(E2,E:E)*10^3+ROW())
下方向・↓
 
●「印刷用」シート
 
   B  C  D  E  F  G  H  I  J  K
1 曜日 類1 類2 値 類3 類4 類5 類6 類7 類8
2  水  C4  D4 50  F4  G4  H4  I4  J4  K4
3  月  C2  D2 30  F2  G2  H2  I2  J2  K2
4  火  C3  D3 20  F3  G3  H3  I3  J3  K3
5  木  C5  D5 20  F5  G5  H5  I5  J5  K5
 
B2
=IFERROR(INDEX(データ入力用!B:B,MOD(SMALL(データ入力用!$L:$L,ROW(B1)),10^3)),"")
右方向・→下方向・↓