エクセルで順位表を別シートに並び替えたいと考えていますが、同点になった場合はどうしたらよいでしょうか?
シート1で集計した結果を参照し、シート2に上から順に1位から表示させる表を作りたいです。
試行錯誤の結果、下記まで作業しました。
=========
<シート1>
A B C
(1)商品名 (2)販売額 (3)ランキング
1 a 1000 2
2 b 1500 1
3 c 1000 2
4 d 500 4
5 e 300 5
(1)手入力
(2)SUM関数で集計した値
(3)RANK関数
<シート2>
A B C
(3) ランキング (1)商品名 (2)販売額
1
2
3
4
5
(3)手入力
(2)INDEXとMATCH関数
(3)INDEXとMATCH関数
=========
1)シート2の(3)ランキングをシート1を参照し降順で自動表示させる方法を教えてください。
今は手入力なので、同順位があった場合にずれが生じてしまいます。
2)シート2に同順位表示のまま並び替えする方法を教えてください。
INDEX関数とMATCH関数を使っていましたが、
シート1で同順位表示されていた箇所が、シート2で表示できませんでした。
どうぞ宜しくお願いいたします。
No.4ベストアンサー
- 回答日時:
順位の数字に細工をしておくだけで、簡単にできます。
添付図:
C2に
=RANK(B2,B:B)+ROW()/1000
と記入、カンマ区切りの書式設定を取り付けて以下コピーしておく
これで同順も違う数字が計算できるので、あとはINDEXとMATCHで持ってくるだけです。
E2:
簡単に1番からの連番を記入
ROW関数とか使ってもいいですが、手で記入するので十分
F2:順位の数字の小さい順に取ってくる
=INDEX(A:A,MATCH(SMALL($C:$C,$E2),$C:$C,0))
右にコピー、下にコピー。
実際に手を動かして、できることを確認してください。
keithinさん、どうもありがとうございます!
最初に自分で考えてできなかった案に一番近かったので、スムーズに直せました!
私はシート1とシート2の順位がMATCHしたときに値を引っ張ってくるようにしたのが間違っていたんですね。
順位の小さい順に値を引っ張ってくれば同じことだと、「なるほど!」と思いました。
どうもありがとうございました。
No.8
- 回答日時:
<br /> 回答No.7の続きです。<br /> <br /> <br /> 従いまして、<br /> <br /
INDEX(Sheet1!$C:$C,IF($A50=$A51,MATCH(B50,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44))):INDEX(Sheet1!$C:$C,ROWS(Sheet1!$C:$C)),0)+IF($A50=$A51,MATCH(B50,Sheet1!$A:$A,0),ROW(Sheet1!$A$44)-1))という部分は
INDEX(Sheet1!$A:$A,「Sheet1のA51セルに表示されている順位である6が、『この関数が入力されている1つ上のセルであるSheet2のB50セルに入力されている商品名"d"、と同じ商品名が入力されているSheet1の48行目』よりも下の行において初めて現れているSheet1のC50セルの行番号」)
と同じ事になりますから、
INDEX(Sheet1!$A:$A,MATCH($A51,INDEX(Sheet1!$C:$C,IF($A50=$A51,MATCH(B50,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44))):INDEX(Sheet1!$C:$C,ROWS(Sheet1!$C:$C)),0)+IF($A50=$A51,MATCH(B50,Sheet1!$A:$A,0),ROW(Sheet1!$A$44)-1))
↓
INDEX(Sheet1!$A:$A,MATCH(6,INDEX(Sheet1!$C:$C,IF(6=6,MATCH(B50,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44))):INDEX(Sheet1!$C:$C,ROWS(Sheet1!$C:$C)),0)+IF(6=6,MATCH(B50,Sheet1!$A:$A,0),ROW(Sheet1!$A$44)-1))
↓
INDEX(Sheet1!$A:$A,MATCH(6,INDEX(Sheet1!$C:$C,IF(TRUE,MATCH(B50,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44))):INDEX(Sheet1!$C:$C,ROWS(Sheet1!$C:$C)),0)+IF(TRUE,MATCH(B50,Sheet1!$A:$A,0),ROW(Sheet1!$A$44)-1))
↓
INDEX(Sheet1!$A:$A,MATCH(6,INDEX(Sheet1!$C:$C,MATCH(B50,Sheet1!$A:$A,0)+1):INDEX(Sheet1!$C:$C,ROWS(Sheet1!$C:$C)),0)+MATCH(B50,Sheet1!$A:$A,0))
↓
INDEX(Sheet1!$A:$A,MATCH(6,INDEX(Sheet1!$C:$C,MATCH("d",Sheet1!$A:$A,0)+1):INDEX(Sheet1!$C:$C,ROWS(Sheet1!$C:$C)),0)+MATCH("d",Sheet1!$A:$A,0))
↓
INDEX(Sheet1!$A:$A,MATCH(6,INDEX(Sheet1!$C:$C,48+1):INDEX(Sheet1!$C:$C,1048576),0)+48)
↓
INDEX(Sheet1!$A:$A,MATCH(6,Sheet1!$C$49:$C$1048576,0)+48)
↓
INDEX(Sheet1!$A:$A,MATCH(6,Sheet1!$C$49:$C$1048576,0)+48)
↓
INDEX(Sheet1!$A:$A,2+48)
↓
INDEX(Sheet1!$A:$A,50)
↓
Sheet1!$A$50
↓
"f"
という事になり、Sheet1おいて、「商品名」欄が「d」、「ランキング」欄が2になっている行の次に、「ランキング」欄の所に2が現れている行である、Sheet1の50行目の所に入力されている商品名「f」という値を返す事になります。
処で、御質問の内容が「同順位表示方法は?」となっておりましたので、販売額が同じものに関しては、順位を表す数字が同じになっている事を質問者様は望んでおられるものと考えていたのですが、もし、回答No.4様の方法の様な「販売額が同じであっても、順位を示す数は必ずしも同じでなくとも良い」という方法でも良いという事でしたら、Sheet1のC45セルに入力する関数を次の様なものにされた方が、順位が整数値になりますので、解りやすくなると思います。
=IF(ISNUMBER($B45),RANK($B45,$B:$B)+COUNTIF($B$44:$B44,$B45),"")
その場合、Sheet2のB列の関数は、回答No.4様の方法にある様な関数でも構いませんが、次の様な関数とする事も可能になります。(計算処理の負荷が僅かに軽くなります)
=IF(ISNUMBER($A45),IF(COUNTIF(Sheet1!$C:$C,$A45),INDEX(Sheet1!$A:$A,MATCH($A45,Sheet1!$C:$C,0)),""),"")
No.7
- 回答日時:
<br /> 回答No.6の続きです。<br /> <br /> <br /> 従いまして、<br /> <br /
5,MATCH(B44,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44)))という部分は、「Sheet2において、同じ行に表示されている順位は前にも現れた事がある」場合には、
INDEX(Sheet1!$C:$C,MATCH(B44,Sheet1!$A:$A,0)+1)
と同じ事になり、「Sheet1の中で『Sheet2の1つ上の行の所に表示されているデータ』が入力されている行の1つ下の行」の中のC列(「ランキング」欄)のセルを指定する事になります。
この処理は「同じ行内のA列に表示されている値(順位を示す数値)が、1つ上の行内のA列に表示されている値と同じである」場合においてのみ行われるのですから、結局、「Sheet1の中で、順位が『Sheet2の同じ行内に表示されている順位』と同じで、商品名が『Sheet2における1つ上の行に表示されている商品名』と同じである行の中のC列(「ランキング」欄)セル」の1つ下のセルを指定する事になります。
例えば、Sheet1に
<Sheet1>
A B C
44 商品名 販売額 ランキング
45 a 1000 2
46 b 1500 1
47 c 1000 2
48 d 500 6
49 e 300 10
50 f 500 6
51 g 600 4
52 h 500 6
53 i 400 9
54 j 500 6
55 k 800 5
の様に元データが並んでいるものとした場合には、Sheet2の表示は
<Sheet2>
A B C
44 ランキング 商品名 販売額
45 1 b 1500
46 2 a 1000
47 2 c 1000
48 4 k 800
49 5 g 600
50 6 d 500
51 6 f 500
52 6 h 500
53 6 j 500
54 10 i 400
55 11 e 300
となりますが、この場合、例えば、Sheet2のB50セルとB51セルに表示されている順位は共に6で、同じ順位となっています。
そして、Sheet2のB51セルに入力されている関数は、
=IF(ISNUMBER($A51),INDEX(Sheet1!$A:$A,MATCH($A51,INDEX(Sheet1!$C:$C,IF($A50=$A51,MATCH(B50,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44))):INDEX(Sheet1!$C:$C,ROWS(Sheet1!$C:$C)),0)+IF($A50=$A51,MATCH(B50,Sheet1!$A:$A,0),ROW(Sheet1!$A$44)-1)),"")
となっています。
その中の
IF($A50=$A51,MATCH(B50,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44))
という部分は、
IF($A50=$A51,MATCH(B50,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44))
↓
IF(6=6,MATCH(B50,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44))
↓
IF(TRUE,MATCH(B50,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44))
↓
MATCH(B50,Sheet1!$A:$A,0)+1
↓
MATCH("d",Sheet1!$A:$A,0)+1
↓
48+1
↓
49
という事で、「『Sheet2のB51セルの1つ上のセルであるSheet2のB50セル』に入力されている商品名"d"」と同じ商品名が入力されているSheet1のA48セルの行番号を求めて、それに1を加える事で、そのまた1つ下のセルであるSheet1のA49セルの行番号である49という値を求めている事になります。
つまり、
MATCH($A51,INDEX(Sheet1!$C:$C,IF($A50=$A51,MATCH(B50,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44))):INDEX(Sheet1!$C:$C,ROWS(Sheet1!$C:$C)),0)
という部分は「Sheet2において、同じ行に表示されている順位は前にも現れた事がある」場合において、
MATCH($A51,Sheet1!$C$49:$C$1048576,0)
と同じ事になり、「この関数が入力されているセルと同じ行の中のA列に表示されている順位である6が、『この関数が入力されている1つ上のセルであるSheet2のB50セルに入力されている商品名"d"、と同じ商品名が入力されているSheet1のA48セル』よりも下の行において初めて現れる位置」である2という値を求める関数となります。
但しこの「位置」を示す値である2とは、あくまで「『この関数が入力されている1つ上のセルであるSheet2のB50セルに入力されている商品名"d"、と同じ商品名が入力されているSheet1のA48セル』の1つ下の行であるSheet1の49行目」から数えた位置を示すものに過ぎず、行番号そのものではありません。
そこで、その後に付けた
IF($A50=$A51,MATCH(B50,Sheet1!$A:$A,0),ROW(Sheet1!$A$44)-1)
という部分が返す値を加える事で、返される値がSheet1のC51セルの行番号になる様にしている訳です。
※そろそろ、このサイトの回答欄における入力可能文字数の制限を超えそうですので、残りは又後で回答させて頂きます。
No.6
- 回答日時:
<br /> 回答No.5の続きです。<br /> <br /> <br /> 一方、<br /> <br />
H(B44,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44)))という部分では、INDEX関数内の行番号を指定する所にIF関数やMATCH関数が入れ子になって入っており、条件によって行番号を指定する値が変化する様になっています。
そのIF関数内の判定を行う部分には、
$A44=$A45
という判定式が入力されていますが、この関数はB45セルに入力されているのですから、「同じ行の中のA列に表示されている値(順位を示す数値)が、1つ上の行のA列に表示されている値と同じである」という場合には、IF関数の[真の場合]の処理へと進み、そうではない場合には[偽の場合]の処理へと進み事になります。
Sheet2のA列においては、順位は昇順に並べ替えられて表示されていますから、「Sheet2において、同じ行に表示されている順位は前にも現れた事がある」場合には、必ず「同じ行の中のA列に表示されている値(順位を示す数値)が、1つ上の行のA列に表示されている値と同じである」という事になりますので、
$A44=$A45
という簡単な判定式で、「Sheet2において、同じ行に表示されている順位は前にも現れた事がある」のか否かを判定する事が出来る訳です。
そして、IF関数内の[偽の場合]の所には
ROW(Sheet1!$A$44)
と入力されていますから、同じ行の中のA列に表示されている値(順位を示す数値)が、1つ上の行のA列に表示されている値とは異なっている」場合、即ち「同じ行に表示されている順位が、Sheet2において現れるのはこの行が最初である」という場合には、
INDEX(Sheet1!$C:$C,IF($A44=$A45,MATCH(B44,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44)))
↓
INDEX(Sheet1!$C:$C,IF(FALSE,MATCH(B44,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44)))
↓
INDEX(Sheet1!$C:$C,ROW(Sheet1!$A$44))
↓
INDEX(Sheet1!$C:$C,44)
↓
Sheet1!$C$44
という事になり、Sheet1!$C$44(Sheet2のにおいて、「ランキング」欄の中で最も上にあるセル)が指定されたのと同じ事になります。
処で、INDEX関数を使用してセルを指定する方法を用いた場合、「A1」とか「B2」等の様にセル番号を直接指定するのと同様に、セル範囲の指定に使用する事が可能です。
例えば、
INDEX($A$2:$D$5,3,4):INDEX(E6:Z99,2,3)
とした場合には、$D$4:G7というセル範囲を指定したのと同じ事になります。
という事は、「同じ行に表示されている順位が、Sheet2において現れるのはこの行が最初である」場合においては、
INDEX(Sheet1!$C:$C,IF($A44=$A45,MATCH(B44,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44))):INDEX(Sheet1!$C:$C,ROWS(Sheet1!$C:$C))
という部分は、Sheet1!$C$44:$C$1048576というセル範囲を表す事になります。
つまり、「同じ行に表示されている順位が、Sheet2において現れるのはこの行が最初である」場合においては、
MATCH($A45,INDEX(Sheet1!$C:$C,IF($A44=$A45,MATCH(B44,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44))):INDEX(Sheet1!$C:$C,ROWS(Sheet1!$C:$C)),0)
という部分は、
MATCH($A45,Sheet1!$C$44:$C$1048576,0)
と同じ事になり、A45セルに入っている値、即ち「この関数が入力されている行と同じ行の所に表示されている順位」と同じ値が入力されているセルが、Sheet1!$C$44:$C$1048576の範囲の中で、上から何番目の所で初めて現れるのか、という事を返す関数となります。
この値はあくまでSheet1!$C$44の所から数え始めた番号に過ぎませんから、この値を基にして「『この関数が入力されている行と同じ行の所に表示されている順位』と同じ値が入力されているSheet1のC列のセル」の行番号を求めるために、
+IF($A44=$A45,MATCH(B44,Sheet1!$A:$A,0),ROW(Sheet1!$A$44)-1)
という部分を付け加えています。
「同じ行に表示されている順位が、Sheet2において現れるのはこの行が最初である」場合においては、このIF関数内の[偽の場合]の処理である
ROW(Sheet1!$A$44)-1
という処理へと進みますから、Sheet1のA44セルの行番号から1を差し引いた値である43が返される事になります。
この値を先程の「『この関数が入力されている行と同じ行の所に表示されている順位』と同じ値が入力されているセルが、Sheet1!$C$44:$C$1048576の範囲の中で、上から何番目の所で初めて現れるのか」という事を表す数値に加えるのですから、「『この関数が入力されている行と同じ行の所に表示されている順位』と同じ値が入力されているSheet1のC列のセル」の行番号が返される事になります。
従いまして、
INDEX(Sheet1!$A:$A,MATCH($A45,INDEX(Sheet1!$C:$C,IF($A44=$A45,MATCH(B44,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44))):INDEX(Sheet1!$C:$C,ROWS(Sheet1!$C:$C)),0)+IF($A44=$A45,MATCH(B44,Sheet1!$A:$A,0),ROW(Sheet1!$A$44)-1))
↓
INDEX(Sheet1!$A:$A,「『この関数が入力されている行と同じ行の所に表示されている順位』と同じ値が入力されているSheet1のC列のセルの行番号」)
という事になり、Sheet1のC列において、その順位が初めて現れている行の所に入力されている商品名を示す値が返されるわけです。
それに対して、「同じ行内のA列に表示されている値(順位を示す数値)が、1つ上の行内のA列に表示されている値と同じである」という場合、即ち、「Sheet2において、同じ行に表示されている順位は前にも現れた事がある」という場合には、
IF($A44=$A45,MATCH(B44,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44))
というIF関数の中の[真の場合]である
MATCH(B44,Sheet1!$A:$A,0)+1
という処理へと進む事になります。
MATCH関数は
MATCH(検査値, 検査範囲, [照合の型])
という形式で記述される関数で、「照合の型」の所で0を指定しますと、「検査値」の所に入力されている値と完全に同じ値が入っているセルが、「検査範囲」の所で指定されたセル範囲の中で何番目の位置にあるのかを求める関数となります。
この時、「検査値」の所に入力されている値と完全に同じ値が入っているセルが複数存在する場合には、「検査値」の所に入力されている値と完全に同じ値が入っているセルの内、一番最初に現れたセルの位置が返されます。
B44セルとは、1つ上の行の「商品名」欄のセルですから、
MATCH(B44,Sheet1!$A:$A,0)
という部分では、Sheet2において1つ上の行の所に表示されている商品名が、Sheet1のA列の中で最初に現れているセルの行番号が返される事になります。
>Sheet1の商品名欄に、同一の商品名が複数のセルに重複して入力されている様な事は無い
という条件がありますから、「Sheet2において1つ上の行の所に表示されている商品名が、Sheet1のA列の中で最初に現れているセルの行番号」は、必ず「Sheet1の中で『Sheet2の1つ上の行の所に表示されているデータ』が入力されている行の行番号」と一致する事になりますので、
>Sheet1の商品名欄に、同一の商品名が複数のセルに重複して入力されている様な事は無い
という条件が守られている限りにおいては、「商品名」とMATCH関数を組み合わせる事で、Sheet1における特定の行を指定する事が出来る訳です。
そして、
MATCH(B44,Sheet1!$A:$A,0)+1
という具合に、後ろに「+1」が付いているという事は、「Sheet1の中で『Sheet2の1つ上の行の所に表示されているデータ』が入力されている行の1つ下の行の行番号」を求めている事になります。
※そろそろ、このサイトの回答欄における入力可能文字数の制限を超えそうですので、残りは又後で回答させて頂きます。
No.5
- 回答日時:
<br /> 回答No.2です。<br /> <br /> <br /> >お手数ですが、それぞれの関数の指示の意味を教
> との御要望でしたので、追加の回答を投稿しようとしていたのですが、丁度、回答を回答欄に入力していた最中の2月9日の12時前後に質問が締め切られてしまいましたので、サイトのサポート様に連絡を入れて、回答を追加させて頂きました。>なお、自分が今作業しているのは、ABC列の45行~63行に該当する範囲です。
45行とはずいぶん下の行から始まっていて、少々妙な気も致しますが、取り敢えずその事は置いておいて、44行目には項目名等が入力されているか、或いは空欄となっていて、実際の商品のを示す文字列データや名称や金額、順位等の数値データが入力されているのは45行~63行の範囲であるものとして回答致します。
その場合は、Sheet2のA45セルの関数は次の様なものとなります。
=IF(ROWS($45:45)>COUNT(Sheet1!$C:$C),"",SMALL(Sheet1!$C:$C,ROWS($45:45)))
又、Sheet2のB45セルの関数は次の様なものとなります。
=IF(ISNUMBER($A45),INDEX(Sheet1!$A:$A,MATCH($A45,INDEX(Sheet1!$C:$C,IF($A44=$A45,MATCH(B44,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44))):INDEX(Sheet1!$C:$C,ROWS(Sheet1!$C:$C)),0)+IF($A44=$A45,MATCH(B44,Sheet1!$A:$A,0),ROW(Sheet1!$A$44)-1)),"")
又、Sheet2のC45セルの関数は次の様なものとなります。
=IF(ROWS($45:45)>COUNT(Sheet1!$B:$B),"",LARGE(Sheet1!$B:$B,ROWS($45:45)))
>お手数ですが、それぞれの関数の指示の意味を教えていただけませんか?
まず、Sheet2のA45の関数に関してですが、
ROWS($45:45)
という部分はROWS関数を使用して、関数が入力されているセルが存在している行は「結果を表示する行の範囲において、何行目にあたる行」であるのかを求めております。(行番号が45の行の場合は1、行番号が46の行の場合は2という値が返る事になります)
ですから、もし、Sheet1とSheet2で、実際のデータが始まっている行が異なっている場合には、このROWS関数の中で指定している行範囲は、Sheet2の方に合わせる様にして下さい。
次に、
COUNT(Sheet1!$C:$C)
という部分は、Sheet1のC列の中で、数値が入力されているセルが何個あるのかをカウントする関数です。
Sheet1のA列~C列においてデータが入力されている行の所では、必ずC列の所に順位を表す数値が入っているのですから、
COUNT(Sheet1!$C:$C)
とする事で、Sheet1においてデータが入力されている行が何行あるのかを求めている訳です。
そして、IF関数の中の判定を行う箇所を
ROWS($45:45)>COUNT(Sheet1!$C:$C)
とする事で、その行が、Sheet1においてデータが入力されている行数を超えているのかどうかを判定し、データが入力されている行数を超えていない場合には、
SMALL(Sheet1!$C:$C,ROWS($45:45))
という処理を行う様にしています。
一方、データが入力されている行数を超えている場合には、その行よりも上の行の所までで既に全てのデータが表示され尽くされていて、その行の所で表示しなければならない様なデータは存在していない事になりますから、IF関数の「真の場合」の所で指定されている処理である、空欄表示が行われます。
SMALL関数は、
SMALL(範囲, 順位)
という形式で記述される関数で、「範囲」の所で指定されたセル範囲内に入っている数値の中から、「順位」番目に小さな数値を返す関数です。
SMALL(Sheet1!$C:$C,ROWS($45:45))
という箇所においては、「順位」の所には前述の「結果を表示する行の範囲において、何行目にあたる行」であるのかを求めるための
ROWS($45:45)
が入っていますから、
SMALL(Sheet1!$C:$C,ROWS($45:45))
という箇所では、Sheet1のC列、即ち「ランキング」欄において、セルに入っている「順位を表している数値」を小さい順に表示する事になる訳です。
Sheet2のC45の関数もROWS関数やCOUNT関数を使用している意味は、Sheet2のA45の関数と同様ですので、その部分に関する説明は割愛します。
Sheet2のC45の関数の構造において、Sheet2のA45の関数と異なっている部分は
LARGE(Sheet1!$B:$B,ROWS($45:45))
という部分です。
LARGE関数は、
LARGE(範囲, 順位)
という形式で記述される関数で、「範囲」の所で指定されたセル範囲内に入っている数値の中から、「順位」番目に大きな数値を返す関数です。
LARGE(Sheet1!$B:$B,ROWS($45:45))
という箇所においては、「順位」の所には前述の「結果を表示する行の範囲において、何行目にあたる行」であるのかを求めるための
ROWS($45:45)
が入っていますから、
LARGE(Sheet1!$B:$B,ROWS($45:45))
という箇所では、Sheet1のB列、即ち「販売額」欄において、セルに入っている「順位を表している数値」を大きい順(「ランキング」欄の数値が小さい順と同じ順番)に表示する事になる訳です。
次にSheet2のB45セルの関数
=IF(ISNUMBER($A45),INDEX(Sheet1!$A:$A,MATCH($A45,INDEX(Sheet1!$C:$C,IF($A44=$A45,MATCH(B44,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$44))):INDEX(Sheet1!$C:$C,ROWS(Sheet1!$C:$C)),0)+IF($A44=$A45,MATCH(B44,Sheet1!$A:$A,0),ROW(Sheet1!$A$44)-1)),"")
に関してですが、IF関数の判定を行う箇所の所にある
ISNUMBER($A45)
という部分は、Sheet2のA列に数値が表示されている場合においてTRUEを返し、A列に何も入力されていない場合や、文字列が入力されている場合には、FALSEを返す関数となっています。
ですから、Sheet2の「ランキング」欄に順位が表示されている場合には、IF関数の「真の場合」の処理へと進み、順位が表示されていない場合には、「偽の場合」の処理である空欄表示が行われます。
次に、
INDEX(Sheet1!$C:$C,ROWS(Sheet1!$C:$C))
という部分に関してですが、ROWS関数は指定されたセル範囲の高さ(セル範囲の上端から下端まで何行あるのか)を求める関数であり、
ROWS(Sheet1!$C:$C)
の部分では、範囲としてSheet1のC列全体を指定しているのですから、返される値はそのExcelbookで使用可能な行数と同じ数(Excel2007よりも前のバージョンの場合は65536行、Excel2007以降のバージョンの場合は1048576行)が返される事になりますので、結局、
INDEX(Sheet1!$C:$C,ROWS(Sheet1!$C:$C))
という部分はSheet1のC列の中で最も下にあるセルであるSheet1!$C$1048576(←EXCEL2007以降のバージョンの場合の話。Excel2007よりも前のバージョンの場合はSheet1!$C$65536)を指定している事になります。
※そろそろ、このサイトの回答欄における入力可能文字数の制限を超えそうですので、残りは又後で回答させて頂きます。
No.3
- 回答日時:
こんにちは!
一例です。
↓の画像で左側がSheet1・右側がSheet2で、両Sheetの1行目項目名は同じにしておきます。
Sheet1に作業用の列を1列設けます。
画像ではE2セルに
=IF(C2="","",COUNTIF(B:B,">"&B2)+COUNTIF(B$2:B2,B2))
という数式を入れオートフィルでずぃ~~~!っと下へコピーしておきます。
そしてSheet2のB2セルに
=IF(A2="","",INDEX(Sheet1!$A:$C,MATCH($A2,Sheet1!$E:$E,0),MATCH(B$1,Sheet1!$1:$1,0)))
という数式を入れ、列・行方向にオートフィルでコピー!
これで画像のような感じになります。
※ 同順位の場合は上側の行のデータが上位に表示されます。m(_ _)m
tom4さん、どうもありがとうございます。
countif関数でもできるんですね!
知りませんでした。
いろんな方法があるのですね。
今回は作業列をできれば避けたかったのですが、
次回はこの方法も活用させていただきますね!
No.2
- 回答日時:
もし、Sheet1の商品名欄に、同一の商品名が複数のセルに重複して入力されている様な事は無い、という事が保障されている場合には、作業列を使わずに済む方法もあります。
まず、Sheet2のA2セルに次の関数を入力して下さい。
=IF(ROWS($2:2)>COUNT(Sheet1!$C:$C),"",SMALL(Sheet1!$C:$C,ROWS($2:2)))
次に、Sheet2のB2セルに次の関数を入力して下さい。
=IF(ISNUMBER($A2),INDEX(Sheet1!$A:$A,MATCH($A2,INDEX(Sheet1!$C:$C,IF($A1=$A2,MATCH(B1,Sheet1!$A:$A,0)+1,ROW(Sheet1!$A$1))):INDEX(Sheet1!$C:$C,ROWS(Sheet1!$C:$C)),0)+IF($A1=$A2,MATCH(B1,Sheet1!$A:$A,0),ROW(Sheet1!$A$1)-1)),"")
次に、Sheet2のC2セルに次の関数を入力して下さい。
=IF(ROWS($2:2)>COUNT(Sheet1!$B:$B),"",LARGE(Sheet1!$B:$B,ROWS($2:2)))
次に、Sheet2のA2~C2の範囲をコピーして、同じ列範囲の3行目以下に貼り付けて下さい。
これで、Sheet2にランキング順に並び替えられたデータが表示されます。
kagakusukiさん、具体的にお教えいただきありがとうございます。
>もし、Sheet1の商品名欄に、同一の商品名が複数のセルに重複して入力されている様な事は無い、
>という事が保障されている場合には、作業列を使わずに済む方法もあります。
⇒こちらの方法をイメージしていたので助かりました!
教えていただいた関数を入力を自分でエクセルにコピペしたら再現することができました!
ただ、いざ自分の困っていたシートにコピペし範囲等修正するにあたり、
一部教えていただいた関数の意味が自分の知識不足でわからず修正対応がうまくできません。。。
なお、自分が今作業しているのは、ABC列の45行~63行に該当する範囲です。
お手数ですが、それぞれの関数の指示の意味を教えていただけませんか?
No.1
- 回答日時:
今仮に、Sheet3のA列を作業列として使用するものとします。
まず、Sheet3のA2セルに次の関数を入力して下さい。
=IF(ISNUMBER(INDEX(Sheet1!$C:$C,ROW())),INDEX(Sheet1!$C:$C,ROW())+COUNTIF(Sheet1!$C$1:INDEX(Sheet1!$C:$C,ROW()-1),INDEX(Sheet1!$C:$C,ROW())),"")
次に、Sheet3のA2セルをコピーして、Sheet3のA3以下に貼り付けて下さい。
次に、Sheet2のA2セルに次の関数を入力して下さい。
=IF(COUNTIF(Sheet3!$A:$A,ROWS($2:2)),INDEX(Sheet1!$A:$C,MATCH(ROWS($2:2),Sheet3!$A:$A,0),MATCH(A$1,Sheet1!$A$1:$C$1,0)),"")
次に、Sheet2のA2セルをコピーして、Sheet2のB2~C2の範囲に貼り付けて下さい。
次に、Sheet2のA2~C2の範囲をコピーして、同じ列範囲の3行目以下に貼り付けて下さい。
これで、Sheet2にランキング順に並び替えられたデータが表示されます。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルの関数について 5 2023/01/26 15:26
- Visual Basic(VBA) エクセルマクロでアニメを作る方法を教えてください。 1 2023/02/07 14:27
- Excel(エクセル) エクセルで”入力シート”の文字書式の変更を”出力シート”で同じ文字書式で印刷したいです。VBA希望 4 2023/04/24 11:07
- Excel(エクセル) ユーザー定義について質問です。 2 2023/06/28 13:21
- Excel(エクセル) エクセルで割り振りをする方法 7 2022/08/02 14:02
- その他(Microsoft Office) 1の行を固定した上でVBAを用いて日付順に自動並べ替え 2 2022/06/06 15:09
- Excel(エクセル) excelで検索した商品の画像(ネットワーク上の)を表示させたい。 3 2023/06/28 00:32
- Excel(エクセル) シート参照を含む数式を連続コピー 3 2022/12/10 11:42
- Excel(エクセル) 製品番号での整列と、検索に関して 3 2023/06/28 19:20
- Excel(エクセル) 何方か知恵をください… 下記のシート1にシート2のDATAを表示させたいです。 (シート1の2行目の 6 2022/03/28 17:27
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・【大喜利】【投稿~11/12】 急に朝起こしてきた母親に言われた一言とは?
- ・好きな和訳タイトルを教えてください
- ・うちのカレーにはこれが入ってる!って食材ありますか?
- ・好きな「お肉」は?
- ・あなたは何にトキメキますか?
- ・おすすめのモーニング・朝食メニューを教えて!
- ・「覚え間違い」を教えてください!
- ・とっておきの手土産を教えて
- ・「平成」を感じるもの
- ・秘密基地、どこに作った?
- ・【お題】NEW演歌
- ・カンパ〜イ!←最初の1杯目、なに頼む?
- ・一回も披露したことのない豆知識
- ・これ何て呼びますか
- ・チョコミントアイス
- ・初めて自分の家と他人の家が違う、と意識した時
- ・「これはヤバかったな」という遅刻エピソード
- ・これ何て呼びますか Part2
- ・許せない心理テスト
- ・この人頭いいなと思ったエピソード
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・あなたの習慣について教えてください!!
- ・ハマっている「お菓子」を教えて!
- ・高校三年生の合唱祭で何を歌いましたか?
- ・【大喜利】【投稿~11/1】 存在しそうで存在しないモノマネ芸人の名前を教えてください
- ・好きなおでんの具材ドラフト会議しましょう
- ・餃子を食べるとき、何をつけますか?
- ・あなたの「必」の書き順を教えてください
- ・ギリギリ行けるお一人様のライン
- ・10代と話して驚いたこと
- ・家の中でのこだわりスペースはどこですか?
- ・つい集めてしまうものはなんですか?
- ・自分のセンスや笑いの好みに影響を受けた作品を教えて
- ・【お題】引っかけ問題(締め切り10月27日(日)23時)
- ・大人になっても苦手な食べ物、ありますか?
- ・14歳の自分に衝撃の事実を告げてください
- ・架空の映画のネタバレレビュー
- ・「お昼の放送」の思い出
- ・昨日見た夢を教えて下さい
- ・ちょっと先の未来クイズ第4問
- ・【大喜利】【投稿~10/21(月)】買ったばかりの自転車を分解してひと言
- ・メモのコツを教えてください!
- ・CDの保有枚数を教えてください
- ・ホテルを選ぶとき、これだけは譲れない条件TOP3は?
- ・家・車以外で、人生で一番奮発した買い物
- ・人生最悪の忘れ物
- ・【コナン30周年】嘘でしょ!?と思った○○周年を教えて【ハルヒ20周年】
- ・あなたの習慣について教えてください!!
- ・都道府県穴埋めゲーム
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル数式に問題があります
-
11ケタの数字を打つと、エク...
-
Excelの警告について
-
excelVBAについて。
-
UNIQUE関数、配列数式を使わず...
-
Excelで、毎月の月曜と金曜の合...
-
エクセルの数式が分かりません
-
Excelで合計を求めたいです
-
【マクロ】メッセージボックス...
-
【マクロ】複数の日付データをY...
-
エクセル2021 範囲指定印刷をす...
-
カーソルを合わせてる時のみ行...
-
vbe でのソースコード参照(msgb...
-
エクセル初心者です 用語等まだ...
-
エクセル初心者です 用語とか良...
-
フィルター時の、別の列に書い...
-
フィルターをかけた時の、別の...
-
合計欄の文字を自動で全角文字...
-
エクセルの関数ついて
-
エクセル 別セルの2進数表示を...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelの警告について
-
エクセルの数式バーのフォント...
-
【Excel】日付に連動してプルダ...
-
【再投稿】レイアウトが異なる...
-
Excelについて教えてください ...
-
同率順位の発生しないランキン...
-
エクセルマクロについて教えて...
-
【Excel VBA】 テキストファイ...
-
Excel 標準フォントについて教...
-
Excelの計算で差分を求める場合...
-
Excelの区切り文字について質問...
-
大容量があつかえるソフトを探...
-
エクセルの計算式について(COU...
-
エクセルについて
-
今までは、 「CSVの出力先を選...
-
Excel ショートカットで列、行...
-
8:40までの出勤は全て8:30に...
-
if関数。半角文字や全角文字で...
-
エクセルの関数
-
毎週追加して行くセルの数値を...
おすすめ情報