Excel Maniacs

千葉県情報ネットワーク・成績一覧表対応サポートシステム （第５弾）

千葉県情報ネットワークの成績一覧表（CSVファイル）のデータを利用して各生徒の

備考欄に「皆勤」の文字を書き込むプログラムを作りました。

備考欄に既に何か書き込んである場合は文言の最後に「・皆勤」の文字が書き加えられます。

例えば既に「祖父死亡忌引１日」と入力されていると「祖父死亡忌引１日・皆勤」となります。

 

★皆勤の判定条件

１．各教科の欠時０

１．欠席・遅刻・早退全て０

１．総合・LHR欠時０

 

★使い方

１．千葉県の成績処理システムから成績一覧表.csvをダウンロードする。

２．エクセルで読み込む。

３．KAIKIN01.vbsファイルをダブルクリックする。

　　　　　　　　　　　　　　　　　　　　　　以上

 

KAIKIN01をダウンロード

 

欠時数オーバー（授業時数の1/3超）の生徒を抽出する

質問：下のような成績一覧から、授業時数の1/3を超える欠時数を持つ生徒名を拾い出すにはどうしたらいいでしょうか。当方、VBAは苦手です。関数でできませんか？

例）A組の国語の授業数は36回、A組1番の曹操　孟徳の欠課数は0（空欄）だが、A組3番の劉備　玄徳の欠時数は16なので、劉備　玄徳は拾い出しの対象となります。

 

(●`ε´●)：尚、表の特徴として以下のことがわかっている。

1. 各クラスに９行使われている。
2. 科目と授業数の列は対応するものが交互に並んでいる。
3. クラスによって科目数は異なる。
4. おなじ列に異なる科目が並ぶこともある。
5. 科目欄の数値は該当科目の得点である。

【´･ω･`】：科目欄の数値はいらないのでこの欄を利用して下のような表を作る。

【´･ω･`】：こうなっていれば左に授業数、右に各生徒の欠時数がならんでいるので比較しやすい。

(●`ε´●)：それは分かるがいちいち入力するのはかったるいぞ！

 【´･ω･`】：各クラス9行使われている事を利用してC3に・・・

=OFFSET(D$2,INT(ROW(C2)/9)*9,0)

・・・と入力すればいい。

                もしも、１クラスに６２行使われているなら(千葉県情報ネットワーク成績一覧表）

=OFFSET(D$2,INT(ROW(C2)/62)*62,0)

・・・と入力すればいい。

これをまず、C27までコピーし、次にC3からC27を選択コピーし、それをE3,G3,I3,K3,M3にペーストすればいい。（手順１）

 

【´･ω･`】：今は基本方針を説明したわけだ。

(●`ε´●)：なるほど。では授業数の1/3と欠時数を比べるのだからC3に入れる式を・・・

=IF(OFFSET(D$2,INT(ROW(C2)/9)*9,0)<D3*3,1,0)

・・・とすれば欠時数が授業数の1/3をこえていれば１、そうでなければ０になる。（手順２）

【´･ω･`】：科目欄の数値の合計をCheck欄に表示しよう。SumIf関数を使ってO3に・・・

=SUMIF($C$1:$N$1,"科目",C3:N3)

・・・すると下のようになる。（手順３）

(●`ε´●)：Check欄には生徒以外の数値もあるぞ！

【´･ω･`】：なるほど。それでは生徒の欄には必ずA列に番号があるのでO3の数式を・・・

 =SUMIF($C$1:$N$1,"科目",C3:N3)*ISNUMBER(A3)

・・・に変更する。（手順４）

 (●`ε´●)：Check欄にある数値が欠時オーバーの数というわけか。

【´･ω･`】：そう。あとはフィルターを使って#VALUEと０を除けば終わりだ。

2015_12_21_Sampleをダウンロード

【´･ω･`】：流れをいちいち書いたから時間がかかりそうだが、実際には一瞬だな。

質問：1枚に複数名分の帳票を印刷したい

（要約）VlookUp関数を使って、１枚の用紙に１人分の成績表を印刷することはできるのですが

１枚に２名分を印刷するにはどうすればよいでしょう？

 

【´･ω･`】：１枚の用紙に１人分ならば、例えばセルA1に生徒の出席番号を入力するとして

                =Vlookup(A1,範囲,列番号)なんてやればいいんだけど、１枚に２名分ならば

                生徒１の出席番号をB1、 生徒２の出席番号をB２として・・・・　

                B1に=A1*2-1,  B2に=A1*2 　と入力する。そうすればA1が1の時

                B1,B2はそれぞれ１，２またA1が２の時 B1,B2はそれぞれ3，4となるので

　　　　　　生徒１の資料に対しては=Vlookup(B1,範囲,列番号),生徒2の資料に対しては

                =Vlookup(B2,範囲,列番号)とすればいい。あとはA1の数値を1,2,3・・・・と変えて

　　　　　　印刷するだけだ。

(●`ε´●)：1枚に3人分なら？

【´･ω･`】：考え方は全く同じだ。生徒１の出席番号をB1、 生徒２の出席番号をB２、 

　　　　　　生徒３の出席番号をB２として・・・・　

                B1に=A1*３－２,  B2に=A1*３－１, B3に=A1*３　と入力する。そうすればA1が1の時

                B1,B2, B3はそれぞれ１，２,、３またA1が２の時 B1,B2, B3はそれぞれ４，５，６となるので

　　　　　　生徒１の資料に対しては=Vlookup(B1,範囲,列番号),生徒2の資料に対しては

                =Vlookup(B2,範囲,列番号)と),生徒3の資料に対しては  =Vlookup(B3,範囲,列番号)

　　　　　　とすればいい。あとはA1の数値を1,2,3・・・・と変えて印刷するだけだ。

 

 

　　　　　　　連続印刷に関してはこちらをどうぞ。