AIと戦って、あなたの人生のリスク診断 >>

こんにちは、

知り合いから聞かれたのですが、A列に出席番号、B列に生徒氏名、C列に国立文系、国立理系、私立文系、私立理系、就職・専門というように進路希望があります。

1組~8組のすべてのクラス(各40人)に、進路希望の異なる生徒がいます(単位制高校なので授業はクラス単位で行わないのでいろんな生徒がいるらしいです)。英語と国語は進路希望とは関係なく同じテストをするのですが、英語と国語の学年順位やクラス順位はすぐにRANK関数で求められますが、国立文系の中での順位などを求める関数(やり方)はあるでしょうか?

私のアドバイスとしては、進路希望順にソートして、その中でRANK関数を用いて順位を出すと答えたのですが、値複写しておかなければソートしなおしたときにおかしくなります。ただ値複写すると、採点ミスなどで点数が変わるたびに、やり直さなくてはいけません。

私ならVBAを用いて、配列を使って並べ替えて書き込むというやり方にすると思うのですが、質問者はVBAがわからないので関数で何とかできないでしょうか?

このQ&Aに関連する最新のQ&A

A 回答 (1件)

D列に英語点数として


英語進路希望順位=SUMPRODUCT(($C$2:$C$321=$C2)*($D$2:$D$321>$D2))+1

解説
SUMPRODUCT関数は配列の積の和を求める関数ですが
=SUMPRODUCT(条件1*条件2)とすることで複数条件のカウント関数として利用できます。
上記の場合=SUMPRODUCT(進路希望が同じ*自分よりいい点数)で
同じ進路の自分より上の順位の人数をカウントしますので
その人数に+1することで自分の順位になります。
    • good
    • 0
この回答へのお礼

早速の回答、ありがとうございました。思ったとおりの結果を求めることができました。配列関数はよく理解していないので、いつもVBAで処理していましたが、便利そうなので、この機会に勉強しなおしたいと思います。

お礼日時:2008/03/27 10:59

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QEXCELで複数のグループ内での順位を表示させたい。

EXCELで複数のグループ内で順位を表示させるようにしたいのですが、
どうすれば良いかわからず困っています。

たとえば、こんなデータがあるとします。
(A列)(B列)
名前  スコア
あああ10
あああ20
あああ30
あああ 40
いいい5
いいい20
いいい60
いいい65
いいい70
いいい80
いいい90
 ・   ・
 ・   ・
 ・   ・

こようなデータを名前別でスコアの降順で順位をつけたいのです。
(A列)(B列) (C列)
名前  スコア順位
あああ10 4
あああ20 3
あああ30 2
あああ 40 1
いいい5 7
いいい20 6
いいい60 5
いいい65 4
いいい70 3
いいい80 2
いいい90 1
 ・   ・
 ・   ・
 ・   ・

名前の種類の数もかなりあります。

このようにグループ内で順位を表示させるにはどのようにしたらいいでしょうか?

EXCELは初心者なため、どなたかお詳しい方、どうか宜しくお願い致します。

EXCELで複数のグループ内で順位を表示させるようにしたいのですが、
どうすれば良いかわからず困っています。

たとえば、こんなデータがあるとします。
(A列)(B列)
名前  スコア
あああ10
あああ20
あああ30
あああ 40
いいい5
いいい20
いいい60
いいい65
いいい70
いいい80
いいい90
 ・   ・
 ・   ・
 ・   ・

こようなデータを名前別でスコアの降順で順位をつけたいのです。
(A列)(B列) (C列)
名前  スコア順位
あああ10 4
あああ20 3
ああ...続きを読む

Aベストアンサー

    A      B     C
1  名前   スコア  順位
2  あああ   10    4
3  あああ   20    3
4  あああ   30    2
5  あああ   40    1
6  いいい   5    7
7  いいい   20    6
8  いいい   60    5
9  いいい   65    4
10  いいい   70    3
11  いいい   80    2
12  いいい   90    1

C2=SUMPRODUCT(($A$2:$A$20=A2)*($B$2:$B$20>B2))+1
★下にコピー

QExcel、sumifはありますが、minif、maxifはどうしたら?

sumif、countif関数はありますが、minif、maxif関数的なものはどうしたら実現できるのか分かりません。VBAならできますが、関数で実現する方法がありましたら教えてください。

Aベストアンサー

MIN/MAX関数とIF関数で配列数式にするぐらいでしょうか。
例えば、A1:A10に「○」文字列のあるB1:B10のMIN値
 =MIN(IF(A1:A10="○",B1:B10))として入力完了時に、shift+ctrl+enterキーを同時押下して下さい。

QRANK関数 同点の場合違う項目で序列を付けたい

質問願います。
EXCELにて、RANK関数を使って順位付けをする事を理解しています。
スポーツの順位を勝ち点順に並べるのですが、総勝ち点が同点の場合、次に総得点で順位を確定させます。

この場合、順位付けする数値を勝ち点を優先参照・総得点を次に参照させる事は出来ますか?
RANK以外の関数も併用すれば良いのでしょうか?

どなたか回答お願い致します。

Aベストアンサー

総勝ち点が例えばA1セルに50のときに総得点がB1セルに10であったとします。この場合には作業列を作ってC1セルには例えば次の式を入力して重みを付けた値にします。

=A1+B1/1000

C列のデータについてRANK関数を使うことにすればよいでしょう。

QMAXIFはどうすればいい?

AVERAGEIFはありますが、maxでほしいのです。どうやって作れますか?

詳細:
B列が「あ」のデータだけ抜き出し、A列のmax値を得たいのです。
A    B
1   あ
2   い
4   あ
8   え
15  い
この例ではmax値は4です。

Aベストアンサー

>MAXIFはどうすればいい?
MAX関数の括弧内(配列)を前処理しなければなりません。
前処理の数式=(B1:B5="あ")*A1:A5
従って、=MAX(B1:B5="あ")*A1:A5 → MAX({1;0;4;0;0}) → 4
Excelでは計算結果を配列で返すことを要する数式を入力時にCtrlとShiftを押しながらEnterキーを打鍵することになっています。
確定した状態を数式バーで見ると{=MAX(B1:B5="あ")*A1:A5}のように数式を大括弧で括られています。

SUMPRODUCT関数の引数としてMAX関数の返り値を設定するとEnterキーで確定しても目的通りの値が返ります。
=SUMPRODUCT(MAX((B1:B5="あ")*A1:A5))
どのような数式を使っても結果が目的に合えば良いのでお好きな方法で解決してください。

Qエクセルで打ち込んだ数字を自動で別シートに表示したい

エクセルでセルに打ち込んだ数字を自動で別シートに表示できる方法があれば、教えてください。

例えば、シート1のC1に5を打ち込んだら、シート2のD2にシート1で打ち込んだ5が自動で表示される。

また1列すべてを自動で表示させる場合、一つのセルの時と違いがありましたら教えてください。よろしくお願いします。

Aベストアンサー

こんばんは。
入力したセルの値を合計とかでなくて、
純粋に別のシートに自動的に表示したいのであれば、
以下の方法があります。

1.1つのセルだけの場合
例)シート1のC1に5を打ち込んだら、
  シート2のD2にシート1で打ち込んだ5が自動で表示される

⇒シート2のD2のセルをアクティブにして「=」を入力
 した後、シート1のC1をクリックする。
 そうするとD2のセルに「=Sheet1!C1」と表示され、値が自動的に
 表示されるようになります。

2.1列全てコピーしたい場合。
  コピー&リンク貼り付けを使うと便利です。

例)例)シート1のC1~C5に何かを入力したら、
  シート2のD2~D7にシート1で打ち込んだものが自動で表示される

  シート1にあるコピー元のセルを範囲選択して、
  シート2のD2の上で「右クリック」⇒「形式を選択して貼り付け」
  をクリックします。

  そして出てきた小さな画面の左下にある「リンク貼り付け」という
  ボタンをクリックすると完成です。
  試してみてください。。

  念のためにリンク貼り付けを図解しているURLを載せておきます。
  参考にしてみてくださいね。。
  http://www.geocities.jp/office_inoue/excel/eq21.htm

こんばんは。
入力したセルの値を合計とかでなくて、
純粋に別のシートに自動的に表示したいのであれば、
以下の方法があります。

1.1つのセルだけの場合
例)シート1のC1に5を打ち込んだら、
  シート2のD2にシート1で打ち込んだ5が自動で表示される

⇒シート2のD2のセルをアクティブにして「=」を入力
 した後、シート1のC1をクリックする。
 そうするとD2のセルに「=Sheet1!C1」と表示され、値が自動的に
 表示されるようになります。

2.1列全てコピーしたい場合。
  コ...続きを読む

Qエクセルで、条件に一致した行を別のセルに抜き出す方法

エクセルで、指定した条件に一致するセルを含む行をすべて抜き出す方法が知りたいです。

たとえば、

<A列> <B列> <C列>
7/1 りんご 100円
7/2 ぶどう 200円
7/2 すいか 300円
7/3 みかん 100円

このような表があって、100円を含む行をそのままの形で、
別のセル(同じシート内)に抜き出したいのですが。

7/1 りんご 100円
7/3 みかん 100円

抽出するだけならオートフィルターでもできますが、
抽出結果を自動的に、別の場所に、常に表示させておきたいのです。

初歩的な質問だと思いますが、検索しても分からなかったので、よろしくお願いします。

Aベストアンサー

同じ質問が結構よく出てますが、そんなに初歩的でもありません
別シートのA1セルに「100円」と入力し、そのシートの任意のセルに以下の式を貼り付けて下さい。後は、下方向、右方向にコピー。
日付のセル書式は「日付」形式に再設定してください

=IF(COUNTIF(Sheet1!$C:$C,$A$1)>=ROW(A1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(A1)+1)),"")

データ範囲は500行までとしていますが、必要に応じて変更して下さい

Qある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか?

Excelの関数について質問します。
ある範囲のせるを検索して、その隣のセルの値を取得するという関数を探しています。
なければユーザー定義で作りたいと思っています。
VLOOKUP関数では一番左端が検索されますが、
それをある範囲まで拡張して、
その右隣の値を取得できるようにしたいのです。
どうかお知恵をお貸しください。

Aベストアンサー

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場合によっては、IFをかぶせてCOUNTIFで確認した方が良いかもしれません。
 ex. =IF(COUNTIF(A1:F200,X1)=1,【上記数式】,"えらー")

ちなみに、VBAでやるならこんな感じになるかと。

動作の概要
 【検査範囲】から【検査値】を探し、
 最初にHITしたセルについて、右隣のセルの値を返す。
 ex. =Sample(X1,A1:F200)

'--------------------------↓ココカラ↓--------------------------
Function Sample(ByVal 検査値 As Variant,ByVal 検査範囲 As Range)
 For Each セル In 検査範囲
  If セル = 検査値 Then Exit For
 Next セル
 Sample = セル.Offset(0, 1)
End Function
'--------------------------↑ココマデ↑--------------------------

いずれもExcel2003で動作確認済。
以上ご参考まで。

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場...続きを読む

Qエクセル2010でRANK関数に条件を付けるとき

エクセル2010を使っている者です。
RANK関数で順位をつけても同順位だった場合、他の条件で判定することは可能でしょうか?
過去の質問も見たのですが、私のしたいこととは違う事例なんですね。


具体的には、添付画像のような表でドント式で順位をつけようと思っています。

オレンジ色にしたセルに、一つ右のセルの値について数値の高い順に順位をつけていき(全列のオレンジ色のセルの右の値の中での順位です)、値が同じであれば「総数」の値の大きい列のセルのほうが上位になるというようにしたいと思っています。

どうぞ、よろしくお願いいたします。

Aベストアンサー

作業列を使ってごまかす方法があります。
添付の図の例では、D2セルに↓と、入れてあります。
=B2+C2/1000+ROW()/100000

+C2/1000 は、第2条件である「総数」を1/1000した値を足すことで、元データが同じときには「総数」が多いほうが上になるようにしています。
+ROW()/10000 は、データも総数も同じ場合、後から登場した方を上位とするために足しています。

「/1000」や「/10000」は、実際の値やデータ量に合わせて調整してください。


人気Q&Aランキング