診断士１次試験向け：過去問完全マスター10年分の問題リスト(Excel)
この記事は何のために書かれているか
①同友館『過去問完全マスター』の掲載問題をExcelでリスト化。書籍の「取組状況チェックリスト」は３回分の余白のところ、10回まで書けるようにした
②取組状況を蓄積していくことで、正誤をもとに今時点の科目ごとの獲得できる得点率期待値を自動計算するシートをつけた。また、どの分野に取り組むべきか、可視化に挑戦
⇒ 困っていたこと・Excelで実現していることをこのnote記事で説明
⇒ (追記) 効果：9周分の過去マス周回の管理に活用。360時間・約540点で１次試験通過見込み
※本記事、有料設定していますが、全文無料で読むことができます。 こちらのExcelを活用された方のなかで、いいねと思っていただけた場合に、カンパいただけると大変うれしいです。もちろん任意です。 子供のおむつ代にしたいと思います。
※23/8/10追記 文章の一部更新
何に困っていたか
※『過去問マスター』を使ったことがある方はざっと見か、次の見出し「どんなExcelを作ったか」までスキップで大丈夫です
ざっくりまとめると以下２点。
書籍には「取組状況チェックリスト」が論点ごとに付されているが、３回までしか管理できない。
また、アナログでしか管理できないので、集計や分析に使えない
資格試験では過去問演習が勉強の王道といえるが、中小企業診断士の１次試験であれば使う教材として同友館の『過去問完全マスター』が有力候補にあがると思う。
この書籍は各科目10か年の過去問題が（年度別ではなく）問題のテーマ（論点）別にまとめられており、少なくとも勉強初期の学習にはうってつけだと思う。
10か年ともなると収録問題数は数百にのぼり、演習によって苦手分野や間違った問題をつぶしこんでいくにあたっての「管理」が非常に膨大になってくる。
ただ、「いつ、どの問題に取り組み、正答したか間違えたか」を管理するためのツールに乏しい。もちろん書籍では論点テーマごとに「取組状況チェックリスト」という星取表があるものの、紙面の都合上３回までしか管理できない。
過去問演習は苦手なテーマ・問題によっては3回以上解くこともあるだろう。
（23/8/10追記） 結局、多い科目で9回解きました＝9周しました。
また、電子版（PDF版）はＣランクの問題の掲載にとどまり、取組状況チェックリストはPDFに載っていない。
そのため、例えばテーマ間・科目間での苦手箇所を見つけたり、そこまでいかずとも今どれくらい進んだか、といった進捗管理もやりづらい。取組状況のデータ化をしたかった。
どんなExcelを作ったか
こちらはざっくりまとめると以下３点。
ちなみに、こちらは2022年度版の『完全マスター』で作成している。
※科目Gの中小企業経営・政策は2023年度版に更新
取組状況チェックリストの電子化（Excelへの打ち込み）
縦：問題 × 横：取組状況（1～10回目までの日付・正誤）という構成のシートを７科目分作成各科目10年分の全問題のランク別・章別の問題数や割合を集計
さらに、取組状況を集計することで章別の周回回数や、正誤をもとに科目ごとの得点率期待値（≒今の自分の実力値）を算出できる機能
１つずつ簡単に説明していきたい。
1. 取組状況チェックリストの電子化（Excelへの打ち込み）
10年分×7科目の全掲載問題をExcelでリスト化した。
ここは単純作業で膨大な時間がかかる・・・つもりだったが、幸いにも過去年度版の『完全マスター』で同様のことを実施されていた方から当時のデータをご提供いただいたことで、差分を更新するだけで済んだ。ありがたい。
（改めて、ご提供者さま、ありがとうございます。）
キャプチャのように、問題情報は編 ＞ 章 ＞論点 ＞論点内の小カテゴリ の情報とともに、問題の年度・問題番号・問題内の設問番号・ランクを一覧化している。
※左側の、「編」や「章」は行数分だけ繰り返しが並んでしまう。ここは次の集計上必要なため、削ることができなかった。
なお、この後出てくる集計機能では、「○」は「〇」ではなく「○」が集計される。
つまり、「まる」と打つと「〇[漢数字]」と「○[記号]」の２つが変換候補に出てくるが、記号の○を使わないと集計されないので注意してもらいたい。
実際に記入するときは、１問ごとに○×△を打つのは面倒なので、私は１・２・３と打っておいて、あとからまとめて置換している。
①正解した場合 ⇒ ○の代わりに「１」と打っておく
②正解したが自信なかった場合 ⇒ △の代わりに「２」と打っておく
③間違えた場合 ⇒ ×の代わりに「３」と打っておく
④一息つくところで、Excelの置換機能を使って、
・１⇒○
・２⇒△
・３⇒×
と変換する
また、かなり細かい機能だが３回連続で○が付いた問題は色付けされる。（23/8/10追記）
３回連続で間違いなければ、少なくとも当分は勉強しなくていいはず。
というわけで次回以降の「n回目」のセルに緑色の網掛けすることで目印・フィルタリングできるようにした。
使わなかったため、機能削除している。
周回の進め方として、なるべく△×だった問題に集中するようにしたので、
一度○だった問題を３回解くことがほとんどなかったため。
（条件付き書式を削除しただけで、必要な関数は埋め込まれているので、
必要であれば問い合わせください）
こんな感じ↓
・H24第１問は演習１～３回目まで、３回連続正解だった
⇒ 次回以降（当分の間は）勉強しなくていいよう、緑色でお知らせ
・H24第２問は直近２回連続で正解
⇒ 次回５回目も○なら、６回目以降セルが緑色に
・H24第４問は３回○だったが、４回目・５回目で間違い・正答も自信なし
⇒ 緑色を消去する機能を作るのが面倒
⇒ 過去に１度でも３連続正答があったことを残すために緑色のままです
2. 全問題のランク別・章別・論点別の問題数や割合集計
タイトルそのままだが、各科目やその中の各章単位で、ランク別の問題数や割合を集計・算出している。
それほど意味のある情報に思えないかもしれないが、各科目別にAランク・Bランク・Cランクの割合も集計しているので、自分の目標点数に対してどのランクをどれくらい解ければよいのか、ざっくりとではあるが計画を立てられるようになればいいなと思う。
ここは勉強開始２か月でしかない私も試行錯誤中。。
3. 取組状況の可視化・集計
現状持っている機能（関数を使ったただの集計）を書いておく。
※長いので実際のExcelを使いながら見てもらえるとわかりやすいと思います
まずシート内には科目別に集計テーブルとグラフが備わっている。
これからの説明の便宜上、番号を振っている
① 章別の取組回数
各章のなかでの取組回数の最大・最小値を集計している。
・MAX：最も取り組まれた問題の取組回数
・MIN：最も取り組まれていない問題の取組回数
例えばキャプチャの「第1章 簿記の基礎」であれば
・MAX：2回 ＝ ２回（２周）取り組んだ問題が存在する
・MIN：0回 ＝ 一度も取り組んでいない問題が存在する
となる。
この時点で、私はA・Bランクのみ２周し、Cランクの問題は未着手なため、こういった集計結果になっている。
たくさん回数をこなしている章は、苦手分野だと言える。し、単純に科目ごとの差が見えたりして意外と面白い。
② 得点率期待値
ややこしいので、詳しめに書いておく。
得点率期待値（ネーミングおかしい？）は、各科目（・各章）の現状の実力値、つまり100点満点のうち何パーセント取れるかを概算している。
特に科目トータルの数値について言えば、１次試験は60%以上で合格のため、単純に考えてこの期待値が60%を超えるように勉強しないといけない。
本来は単年の過去問演習を通じて60点を超える実力がついたか確かめることになるが、ここでは10か年分の全問題を100としたときに、どれくらい得点できるか、という考え方で計算をしていることになる。
キャプチャの例で言えば、
・トータル：64%（②の枠内の、右下）
⇒ （設問ごとの配点の違いを無視すれば）100点満点で64点が期待できる
・第１章 簿記の基礎：65%
⇒ （１章だけから100点出題されるわけではないが、）１章の内容に関して言えば、出題されたときに65％の得点が期待できる
得点率期待値の計算式は以下の通り。
得点率期待値 ＝ b. みなし点数 ÷ a. 取組問題数
全問題数のうち正解(○△)した問題数を、全問題数で割り算してその割合を出している
a. 取組問題数
集計時点で取り組んだことのある問題数をカウントしている。
単純に科目・章ごとの問題数を用いても良いのだが、取り組んだことがない問題を分母にカウントすると、全問題を解き切っていない状況では得点率期待値が低く出てしまう。
これを避けるため、これまでに取り組んだことのある問題だけをカウントするようにしている。
例えば、Cランクの問題は後回しにして、A/Bランクだけを先に数周するケース。Cランクに着手する前に、Cランクの問題数を分母にカウントすると、実力値が見えにくい。
解いたことない問題はみなし点数は当然0点カウントなので、Cランクの問題数を分母にすると、点数がかなり低く計算されてしまうというわけだ。
勉強が進み、Cランクも含めまんべんなく演習するようになれば、結局母数は全問題数になってくる。より正確な得点率になってくるはず。
※ココは読み飛ばしてもらっても大丈夫です
この計算方法では、上のケースでは、厳密には計算されるのは「A/Bランクのなかで何パーセント取れているか」となってしまう。
Cランクが必ず高難易度、ではない（出題頻度でランク付けされているので）ものの、出題頻度が低い＝重要度が低く、テキスト内でも紙面は割かれない＝結果的に難易度が高くなる、と思われる。
そのためA/Bランクだけでの得点期待値が60%だったとしてもそれをCランクにも拡大推計できるとは限らない。
そのため、所詮は簡易的な集計に過ぎない。先ほど図の説明で書いたように、そもそも問題ごとの配点の違いも無視している。
また、本当に点数として60%を満たしているか、厳密に測りたいのであれば、繰り返しになるが単年別での過去問演習のほうが確実だろう。
あくまでざっくりと、章ごとの苦手分野がわかる、くらいに思ってもらえればと。
b. みなし点数
みなし点数 ＝ 最新の演習で「○」の問題数 × １ ＋ 「△」の問題数 × 0.5
問題ごとの最新の取組結果（○ / △ / ×）をもとに、単純計算で過去問１０年分の総得点を算出している。繰り返しになるが、問題ごとの実際の配点は考慮せず、１問１点で計算している。
「正解した＝○」なら１点だし、「△＝正解できたが、迷った/各選択肢の説明ができない」であれば0.5点としている。
試験では4/5択から2択にしぼって、、、という状況に近いので、正解の確率50%だろう、という仮定。
ちなみに問題ごとの最新の演習結果は、当然だが各科目の取組状況のシートから集計している。
このとき、
問題１：１回解いたのみ、○だった
問題２：３回解いた。×→×→△
問題３：２回解いた。△→×
といった場合には、それぞれ一番最後の○・△・×を取得するように計算している。
裏を返せば、これまでの演習すべての累計での点数・得点率とはしていない。
例えば３問ともに×→○ だった場合、累計で計算すると得点率は50%になる（全体で6回解いて、全体で3回○なので）
しかしこの場合、果たして実力値として50%かというとそうではないだろう。むしろ100%あるいは100%に近い、と考えるのが妥当ではないか。
そういったことから、問題ごとに最新の取組結果を取得・集計するようにしている。
ちなみに、得点率期待値は60%を下回る数値については赤色文字、
80%以上の数値は青色文字になるように設定している。
閾値を変えたい・科目別に設定したいなどあれば、該当セルの条件付き書式をいじってもらえれば大丈夫です。
あと、これも使う人いないと思いますが、
デフォルトでは非表示になっているS列からAA列には、みなし正答の詳細があるので、必要に応じて参照いただきたい。
これらの全合計が取組問題数になる（R列）。
③グラフ：優先して勉強すべき分野の可視化
もう正直誰が使うんだこれ、という代物だが一応説明。
（23/8/10追記）意外と使った。
「パッと見で（時間制約があるなかで）どこを優先して勉強すればいいのかわかるといいなぁ・・・」という思いから、それぞれの科目ごとにこういったグラフを載せている。
どういった数値を用いているかというと、
・縦軸：各章の得点率期待値（②で説明）
・横軸：各章の全問題数に占める、A＋Bランクの問題数の割合
・円の大きさ：各章の全問題数（A＋B＋Cランク）
となっている。
縦軸は、②で説明したように、章ごとに今の実力で何％取れそうかを示している。詳しい説明は②を改めて参照されたい。
次に横軸は、ABCの問題のうち、頻出とは言えないCランクを除いた問題数が章全体のうちどれくらいを占めるのか、を示している。
例えば、
Aランク＋Bランクの問題数：15問
A＋B＋Cランクの問題数＝章全体の問題数：20問 → つまり、Cが5問
のとき、
15 ÷ 20 × 100 = 75%
となる。
ここでは、
割合が高い ＝ ABランクの問題が大勢を占める ＝ 出題される論点がばらけていない = 勉強のコスパがよい
という前提で、よりABランクの割合が高い章を優先して取り組むべき、と考える。
最後に円の大きさ。
冒頭の説明の通りだが、円が大きければそれだけ試験での出題数も多いはずなので、しっかり取り組まないといけない分野を示している。
※あくまで10か年の合計問題数なので、例えば昔は頻出だったけどここ３年はパッタリ・・・というケースなどは考慮していない
というわけで（長かった）、改めてグラフを見てみる。
これは各バブルの位置がすでにバラけているので、「ABランクについて１周した時点」、といったタイミングだと考えてもらいたい。
現状はどうなっているか。これから注力すべき章はどこか。
・一番得点が期待できるのは「第３章 原価計算」。ただ円が小さめ＝出題問題数は少なそうなので、これ以上は時間をかけてもしょうがない？
・「第７章 資金調達と配当政策」は、右端＝ABランクの割合が一番大きく、つまりは論点が絞られているので勉強のコスパが良さそう。かつ、円も大きいので、しっかり得点源にしたい（ので、もっと得点率を上げたい）
・一番円が大きいのは「第２章の企業会計の基礎」だが、ABランクの割合は低め。取りこぼしは一定許容したい。現状６割取れているのでヨシ？
→ 今の状態から手堅く・効率的に点数を上げたいなら、７章や９章、１０章を勉強していくべきでは
（ABランクの割合大きく、問題数もそれなりにある）
グラフの見方・解釈は人それぞれ（目標得点率も人によりけり）だが、ざっくり方向性としてはこんな感じだろうか↓
実際に使ってみた
取組状況の可視化・集計は、結局のところ「優先的に取組む分野はどこか」を見るために作成していた。
このnoteの初版は23年1月で、その後も実際にExcelを使用。
その際にグラフをどう使ったか、もう少し書いておきたい。
（今後更新予定）
ここから先は
¥ 500
この記事が気に入ったらサポートをしてみませんか？