2日間で、Excel上の3つの関数について、理解を深めた。
IF,OFFSET,SUBTOTAL
備忘録として、日記にしておく。
まずは、SUBTOTALについて。
受験生の管理については、基本的にここ数年、校内の各部署でExcelファイルを共有しながら作業を進めている。
で、Excel上で、受験生に「フィルター」をかけた状態で合否判定資料(紙)を出力したりしている。この作業、数年前までは、私が桐を用いて作業をしていた。
桐は少しばかり操作が特殊である。Excelを使えるヒトの方が圧倒的に多く、Web出願システム(汎用品)から出力されるデータもcsvであるため、Excel主体で情報の管理が行われるようになった。
私が入試に対して関わる仕事は、圧倒的に減った。有り難いことである。
で、フィルターをかけた状態で、順位(上から何行目なのか)を出力したい、と。
(伝わりづらいかもしれないけど...)昨年までは、私が「良かれ」と思い、オリジナルの情報(単一のワークシート)から必要な情報を参照し、『印刷用レイアウト』のワークシートを設定しておいた。その「紙」の資料を用いて、職員皆で行う『合否判定資料』とするのである。
順位については、『印刷用レイアウト』上でROW関数を使ってた。
「オリジナルの情報」があるワークシート上で、フィルタリングやソート(例えば「得点順」)を行えば、ROW関数が置いてある『印刷用レイアウト』には、順位が常に記されるのであった。
しかし、似たようなシートが2つ存在していて危険ではあった。それは承知していた。
そこで本年度からは、「『印刷用のレイアウト』に、直接受験生の得点等を入力しましょう」という話になった。事務担当の方々からの発案(?)で。
で、ROW関数があるワークシートでフィルターを適用すると、どうなるのか。
連番がきちんと出ないのである。
「困るよね」ということで、検索してみた。
"Excel フィルタ 行番号"とかで、検索したのだったかしら...。
その結果、SUBTOTAL関数の存在を知った。その仕様(関数の解説ページ:Microsoftのものではない)を確認しつつ、数分間の試行錯誤の結果、無事に連番(=上から何行目なのか)が出力されるようになった。
以下の分かりやすいページは、この日記を記すにあたり、改めて発掘したものです。
オートフィルタで抽出しても崩れない連番～Excel(エクセル)2016,2013技
次に、IFとOFFSETについて。
私は、IF関数って、ほとんど使わないのですね。何か「間違い」があると怖いから。その理由の詳細は、下にリンクを貼っておきました。
昨日、同じ学年を担当している、若手の英語科の先生に質問されたのでした。
「ここ2日くらい、悩んでいるコトがあります。英検の最高スコアはMAX関数で出せるのですが、その最高スコアを「いつ、何級受験で叩きだしたのか」を、その横に表示したいのです。でも、簡単にはできないのです」と。LOOKUP関数その他にも当たってみたが、どうもうまくできない、とのこと。
単一学年生徒の「英検受験結果」について、一つのExcelのブックにまとめている最中とのことでした。確かに以前、「僕がやります」って、学年の会議で言っていたっけ...。
それぞれの英検の受験結果を、1つずつのワークシートとし、それらを参照・集約して表示する1枚のワークシートも作成、それらワークシート群から成るブックを作成。その「集約したワークシート」上にて最高スコアをMAX関数で出すまではできた、と。
それだけでも大変だと思うけど...。
で、最終的には前述の通り「最高スコア○○を、○年○回の○級試験で取得」と表示したい、と。
こんな感じにすると、「やりたいこと」ができる事が分かりました。
1時間ちょっと、格闘したのだろうか...。
セル"J2"あたりは、単なるMAX関数が入ってます。
数式そのものを、以下にお示しします。
=IF(J2=C2,OFFSET(C2,0,-1),"")&IF(J2=E2,OFFSET(E2,0,-1),"")&IF(J2=G2,OFFSET(G2,0,-1),"")
(画面に収まっていない気が...。 必要でしたら、コピペはできると思います。)
過去の関連する日記
評定を関数で出すか、データとして入力するか(2016年7月)
