2010年04月29日

生年月日を加味した五十音順出席番号

このところExcelから離れていたので、リハビリの意味も込めて、ちょっと面倒な数式クイズを考えてみました。

【数式クイズ】難易度:★★★★☆
以前に出題した問題と同じく、カタカナの名簿から五十音順の出席番号を自動表示する数式です。
同姓同名の生徒がいた場合、前回の問題では先(上の行)に登場するほど小さい番号にしましたが、今回は生まれた日が早いほど小さい番号、ということにします。

quiz41.jpg

生徒の名前の読みがなはA2:A8、その生年月日はB2:B8のセル範囲に入力されています。
これらをもとに、五十音順の出席番号を同じ行のC列のセルに表示させます。

まずC2セルに数式を入力し、この数式をC8セルまでコピーします。
C2セルにはどのような数式を入力すればよいでしょうか?


【解答例】
=COUNTIF(A$2:A$8,"<"&A2)+SUM((A2=A$2:A$8)*(B2>=B$2:B$8))

上の数式をC2セルに入力し、単にEnterキーではなく、Ctrl+Shift+Enterキーで配列数式として確定します。

この数式では、まず「COUNTIF」の部分で、自分よりも「前」になる名前の数を求めます。
また、「SUM」の部分では、「A列のセルが自分のセルと同じ名前」かつ「B列のセルが自分のセルの生年月日以前」である行の数を求め、それを「COUNTIF」の結果に加えています。

SUMにCOUNTIFの部分まで含めてしまう手もありますが、上記の数式のほうが何をやっているかがわかりやすいと思います。

少し言い訳をさせてもらうと、久しぶりのExcelなので、結構力技というか、エレガントさが欠けているような気もします。
もっとスマートな方法を思いつかれた方は、ぜひお教えください。
posted by 土屋和人 at 10:28| Comment(0) | TrackBack(0) | Excelクイズ
この記事へのコメント
コメントを書く
お名前: [必須入力]

メールアドレス:

ホームページアドレス:

コメント: [必須入力]

認証コード: [必須入力]


※画像の中の文字を半角で入力してください。
この記事へのトラックバックURL
http://blog.sakura.ne.jp/tb/37443732
※言及リンクのないトラックバックは受信されません。

この記事へのトラックバック