エクセル 関数

質問

りんご　　　8
みかん　　　13
ぶどう　　　5
スイカ　　　2
いちご　　　4 の場合

みかん　　　13
りんご　　　8
ぶどう　　　5
いちご　　　4
スイカ　　　2 にできますか？

=LARGE(*　*)で数字は大→小に並べますが隣の文字（果物）も
一緒に動かしたいのです
よろしくお願いします

回答No.7

134 · ベストアンサー

同じ数字がある場合＃N/Aが出てしまいます
良い方法はありますか？

　　　Ａ　　|　　Ｂ　　|　　Ｃ
１　　　　　|りんご 　|　　８
２　　　　　|みかん　|　１３

さらに１列追加させて頂きます

　　　Ａ　　|　　Ｂ　　|　　Ｃ　　|　　Ｄ　　|　　Ｅ
１　　　　　|　　　　　|りんご 　 |　　８
２　　　　　|　　　　　|みかん　 |　１３

Ｂ１の関数を
=rank(D1,$D$1:$D$5,0)
(１行挿入したので、C→D　に変化しています）
という関数から
=rank(D1,$D$1:$D$5,0)*100+row()
に変えます。

Ａ１に
=rank(B1,$B$1:$B$5,0)
としますと、ダブりはなくなります。

vlookup関数を
=vlookup(row()-6,$a$1:$D$5,3,0)
=vlookup(row()-6,$a$1:$D$5,4,0)
のように範囲を変えると　対応可能かもしれません

お礼

ありがとうございます
いろいろ仕組みを考えながら四苦八苦　やっと出来ました
時間を割いて頂いてありがとうございました

そして大変申し訳ないのですが　もし可能なら
下記の回答にあります
=INDEX($B$1:$B$5,RANK(B1,$B$1:$B$5))
=INDEX($A$1:$A$5,MATCH(E1,$B$1:$B$5,0))
について何か私にアドバイスはありますでしょうか？
まるでパズルの様です
本当に時間のある時お願いします
ダメならダメで構いませんのでよろしくお願いします

回答No.11

134

>実行をすると「End Sub必要です」と出てしまいます
>一番上の方の「End Sub」が黄色になっています

^^;;;;)

Ｅｎｄ　Ｓｕｂ　は、そのプログラムの最後を宣言したものですので、一番上ということは、何か、貼り付け方でミスを犯しているように思います。

Private Sub Worksheet_Change(ByVal Target As Range)

（中略）　　←　実際のプログラムが入ります。

End Sub

あるいは、

Sub プログラム名()

(中略）　

End sub

というふうな構文になります。

お礼

時間をかけて頂いてすいません
もたもたしてましたが　いろいろ試して何とか出来ました！
マクロはまだ意味を理解出来ませんが今読み始めています
長い間付き合って頂いてありがとうございました
先生が出来た感じです

それで・・もう一つ
質問があるのですが付き合って頂けますか？
新しく質問するルールーでしょうか？
時間のある時に観て下さい
マクロはちょっ・・と内容を理解するのが難しいです
出来れば関数でお願いします

回答No.10

134

ごめんなさい。　イベントプロシージャの例示は、突然すぎたようですね。

エクセルが開いた状態で

1.　Ａｌｔ＋Ｆ１１　で、　エディターを開く
2.　Ｆ７で、コードを開く
3.　下のプロシージャをコピペする

という手順で、操作可能かと思います。

>こういった応用（？）の問題があった場合は
>どんな本を参考にすれば良いのでしょうか？

EXCL(エクセル）技道場
エクセルの部屋
エクセルの関数テクニック集
などのＨＰもときどき眺めてもいます。

ＶＢＡに関しては、ステートメントの辞典とプログラミングの解説書、目的別のサンプル集などを眺めながら、実際には、エクセルの機能である「マクロの記録」も多用しているのが、私の現状（限界？）です。
　プロシージャは、より熟練した人が書くと、より無駄のないものが書けるのだろうなと、覚えの悪さばかり感じる毎日です。

お礼

いつもありがというございます
ＶＢＡ
ステートメントの辞典
プログラミングの解説書
目的別のサンプル集・・・・　　き、きほんの言葉から調べます

そして何とかマクロを作る事が出来ました！
のですが実行をすると「End Sub必要です」と出てしまいます
一番上の方の「End Sub」が黄色になっています
たびたびたびたびすいませんが解決策はありますか？

そして今回の質問ですが他の人にも解り易かったのでNo7の方法にしました
マクロは今後の為に勉強したいと思います
ありがとうございました

回答No.9

134

イベントプロシージャは、入力後にＡ１セルに戻ってしまうので、以下のものの方がよいかもしれません。

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyRow As Single

MyRow = Target.Row

If Target.Column = 2 Then

Range("A1", Range("B65535").End(xlUp)).Copy
Range("E1").PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False

Range("E1", Range("F65535").End(xlUp)).Sort , key1:=Range("F1"), order1:=xlDescending, header:=xlNo

End If

Cells(MyRow + 1, 2).Select

End Sub

回答No.8

134

=INDEX($B$1:$B$5,RANK(B1,$B$1:$B$5))
=INDEX($A$1:$A$5,MATCH(E1,$B$1:$B$5,0))

という関数を、ためさせていただきました。

　私の提示した式と違って、余計なセルを使用しないというリテンがありますね。
　ただ、データ数が６行、７行と増えたり、数字のダブりがあるとすると…

　ダブり方も、ポーカーのワンペア、ツーペアなども考えられ、どの行でダブりが生じるかわからないので、if文で回避することも難しいと、感じました。

例示されたデーターのうち、りんごを「１」に変えてしまうと降順でなくなる気もします。

別途
　入力シートのイベントモジュールで、数字を入力したら、転記範囲に転記して、並び替えるようにマクロプログラムを書いてしまう方法も可能かもしれませんね。

例えばこんな感じ？（ＡＢ列にあるデータをＥＦ列に転記して、並び替えます。

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then

Range("A1", Range("B65535").End(xlUp)).Copy
Range("E1").PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False

Range("E1", Range("F65535").End(xlUp)).Sort , key1:=Range("F1"), order1:=xlDescending, header:=xlNo

End If

Range("A1").Select

End Sub

お礼

・・・・・・・かなり難解な為　宿題として3日程待って下さい
がんばってみます

こういった応用（？）の問題があった場合は
どんな本を参考にすれば良いのでしょうか？
こんな感じで分からない事が沢山あるんです（クエリ等）
参考書も読んでいるのですが私の限界がすぐそこで
詳しい人もいないので困っています
パズルっぽくって解ければうれしくなるんですけど・・

ありがとうございました

回答No.6

134

=vlookup(row(　　)-6,$a$1:$c$5,2,0)
　　　　　　　 　↑カッコの中に何か入りますか？

カッコの中に、何も入れなくて構いません

row()　関数は、セルの行番号を返すもので、Ｂ７やＣ７セルなら「７」を、Ｂ９やＣ９セルなら「９」を返します。

=vlookup(　　row()-6　　,$a$1:$c$5,2,0)
　　　　　　　_______

Ｂ７に上の関数を入力したとしますと、
row()-6　＝　７－１　＝　１
となります。

つまり、　Ｂ７においては、
=vlookup(　　1　　,$a$1:$c$5,2,0)
と同じ　になります。

rank関数で、一番大きい数字のものから順に　「１、２，３…」と振りますので、それを　vlookup関数で探している次第です。

お礼

ありがとうございます

何とか理解する事が出来ました！が、同じ数字がある場合＃N/Aが出てしまいます
良い方法はありますか？
すいませんどうかよろしくお願いします

回答No.5

noname#63022

元データがA1:B5まであります。
E1 INDEX($B$1:$B$5,RANK(B1,$B$1:$B$5))と入力し
E5まで↓
D1 INDEX($A$1:$A$5,MATCH(E1,$B$1:$B$5,0))同じく
　D5まで↓

お礼

ありがとうございます

=INDEX($A$1:$A$10,MATCH(E1,$B$1:$B$10,0))はいただき！です
が、=INDEX($B$1:$B$10,RANK(B1,$B$1:$B$10))だと数字が大→小にならないんです
またこれを　=LARGE($B$1:$B$10,1)にすると
同じ数字がある場合同じ文字が続けて出てしまいます
奥が深いです・・

回答No.4

134

１列挿入させていただいて…

　　　Ａ　　|　　Ｂ　　|　　Ｃ
１　　　　　|りんご　　|　　８
２　　　　　|みかん　　|　１３

とさせていただきます

Ａ１に
=rank(c1,$C$1:$C$5,0)
と入力して、データの最後の行までコピーします。

一覧表にしたい範囲　（たとえば、Ｂ７～Ｃ１１）としますにおいて

　たとえば　Ｂ７に
=vlookup(row()-6,$a$1:$c$5,2,0)
　　Ｃ７に
=vlookup(row()-6,$a$1:$c$5,3,0)

と入力して、下まで関数をコピーします。

データの範囲、シート構成により、パラメーターが変わりますので、補足が必要になるかもしれませんけど。

お礼

ありがとうございます
わかる範囲でいろんな事をしてみてるんですが初心者すぎてよくわかりません
=vlookup(row(　　)-6,$a$1:$c$5,2,0)
　　　　　　　 　↑カッコの中に何か入りますか？

がんばってみます

回答No.3

marbin

＃1です。
表が↓のようになっているとします。
※図表モードでの投稿の仕方が分からなかったので、
"_"で別セルを表す形としました。

A1=りんご_B1=8
A2=みかん_B2=13
・
・
A5=いちご_B5=4

A7から並び替えた表を作成する、とします。
A7=INDEX($A$1:$A$5,MATCH(B7,$B$1:$B$5,0))_B7=LARGE($B$1:$B$5,1)
A8=INDEX($A$1:$A$5,MATCH(B8,$B$1:$B$5,0))_B8=LARGE($B$1:$B$5,2)
・
・
A11=INDEX($A$1:$A$5,MATCH(B11,$B$1:$B$5,0))_B11=LARGE($B$1:$B$5,5)

これでいけるはずです。

ただ、この式ではB列の順位の部分が1,2,・・・・と手入力になっています。
この部分を
Row(B5)-4
のように関数で置き換えると7行目の式だけ手入力し、atoは式のコピーだけ
でいけるようになります。

お礼

ありがとうございます
ずーっとやっているのですが　なかなか出来るような出来ないような・・
この時　同じ数字がある場合同じ文字が続けて出てしまいます
みかん　３　　　りんご　５
りんご　５　　　みかん　３
いちご　３　が　みかん　３の様に

りんご　５
みかん　３
いちご　３　とならないんです・・

回答No.2

tgn1013

関数じゃないとダメなんですか？
「並べ替え」で一発で並び変わりますが。
(データ＞並び替え)

お礼

そーなんですよね
今まで「並べ替え」でやっていたんですがsheetを１と２に分ける事になって
私以外に新人がやる事になって
sheet１にある元の打ち込みグラフに出たものが自動的にsheet２で
大→小（グラフ）になり文字＋数字（セルは隣）に同時になれば簡単だなぁ・・って
えーん(T_Tやっぱ私には無理？

回答No.1

marbin

INDEXとMATCHの合わせ技はいかがでしょう？

=INDEX($A$1:$A$5,MATCH(B7,$B$1:$B$5,0))

補足

ありがとうございます
この場合　やはり自動的（？？）に大→小に並び尚且つ
隣に有る文字も同時に代わってくれるのでしょうか？
今やってみてるんですが初心者で訳わからないです(T_T)

がんばります