以前に、どこかのサイトに載っていた「Excelのスキルを図る企業の入社テスト」を解いてみて、それを以下の記事に書いていました。
もう8年程前になる懐かしい連載記事となります。
ただ説明する筆者の文章がもの凄く下手なのと、しっかりと解答方法を提示できていないというお粗末な記事でもあったので、今回改めて書き直してみようと思いました。
一応以前の記事を参照リンクとして上に貼っておきましたが、ご覧にならなくてもまったく問題ありません😊
今回は、前回と同じように8回の連載ですが、以前とは違い、解答方法までをきっちりと記事化していきます。
Excelのバージョン
一般法人ライセンス「Microsoft 365 Business Standard」のバージョン「2606」となります
入社テストとして出題されたエクセルの問題なだけあって、実務で必要となる操作がスムーズにできるかが問われるお題ばかりとなっています。
ぜひ、最後まで一緒に解いていきましょう！
スポンサーリンク
スポンサーリンク
問題1を確認
出題されていたエクセルの画面と同じものを作成し、以下からダウンロードできるようにしてあります。
まずは基本的な関数などを使った問題ですね。
足し算による合計や構成比を求める割り算、そして最大値と最小値を求めます。
ポイントは以下の2つです。
- 解答を効率よく求める
- 黄色い枠(セル)だけを変更できる
では、まず合計から求めてみましょう。
合計を求める
合計(C列)には、AとBの合計を求めます。
足し算には「オートSUM」を使いましょう。
合計を求める
「セルA5」と「セルB5」、さらに合計値を表示する「セルC5」を範囲選択します。
「ホーム」タブから「オートSUM」ボタンをクリックします。
すると、「1099＋39」が計算されて「セルC5」には「1138」が入力されます。
さらに、「セルC5」のフィルハンドルを「セルC12」までドラッグして、各行の合計値を表示します。
これで5行目から6行目、7行目とA列とB列の参照するセルだけが変化して、自動的に合計が計算されます。
つまり、効率よく合計値を求めるためには、フィルハンドルで一気に合計値を求めるわけですね。
「セルC6」にオートSUM、「セルC7」にオートSUM・・・と1個ずつオートSUMを指定しくのは効率が悪いので、間違いとなります。
これで5行目から12行目までの合計値がすべて出そろいました。
では、引き続きD列の「構成比」を求めてみましょう。
構成比は全合計に対する割合
例えば5行目の合計値「1138」は、全合計値の何％になるかを求めます。
全合計値はシート上には表示されていません。
先ほどポイントとしても上げたように、「黄色いセル以外を変更できない」ため、すべての合計を計算しながら構成比を算出するようにします。
まず構成比を求める計算式を確認しておきましょう。
各行の合計÷総合計＝構成比(%)
それでは5行目の構成比を求めてみます。
全合計値は「セルC5」から「セルC12」までの合計となります。
つまり、「セルC5」の構成比を求めるには、以下のような計算式になります。
「セルC5」÷「セルC5からセルC12までの合計」＝「セルC5」の構成比
先に「C5」をセットします。
「セルD5」で「=」を入力し、C5と入力するか「セルC5」をクリックするか、やりやすい方でまずは「=C5」と入力されるようにします。
次に「/」を入力して、さらに「SUM(C5:C12)」と入力します。
割り算は関数ではなく、/(スラッシュ)を使って計算式を作るので、上のようになります。
これで、「セルC5」を「全合計値(セル5からセル12までの合計)」で割り算する計算式が入力できました。
関数は補助機能が使える
先ほど、「直接SUM(C5:C12)と入力して」と書きました。
実は、エクセルは関数名を直接入力すると補助機能が働くようになっています。
補助リストの「SUM」をダブルクリックすると、引数の指定ができます。
セルC5からセルC12までを範囲選択すると、SUM関数の引数に「(C5:C12)」と自動で入力されます。
すると、「セルD5」には「24%」という構成比が求められました。
「セルD5」に入力した計算式も、しっかり確認できますね。
それでは、合計値(C列)と同じように構成比(D列)も同じようにフィルハンドルをコピーして一気に計算しちゃおう・・・というのは間違いです。
なぜだか分かりますか？
そう、全合計を計算する「セルC5からセルC12」までの位置は変わらないからです。
これが、セルの「相対参照」か「絶対参照」かの違いとなります。
今さら・・という人ばかりだとは思いますが、少し説明しておきましょう。
相対参照と絶対参照
そもそも「フィルハンドルをコピーする」という操作は、「セルに入力された計算式をコピーする」と言う意味になります。
先ほどD5の値を最初に求めました。
この計算式を1つ下の行の「セルD6」にフィルハンドルでコピーすると、「セルD5」に入力されている計算式で参照されているセルも1つ下の行に下がります。
「セルD6」の構成比を求めるためには、本来は以下の計算式がいいのです。
「セルC6」÷「セルC5からセルC12までの合計」＝「セルD6」の構成比
ただし、「セルD5」に入力されている計算式は以下のようになります。
「セルC5」÷「セルC5からセルC12までの合計」＝「セルD5」の構成比
これを1行下にフィルハンドルをコピーすると、上記計算式で参照されているセルは、すべて1行下がってしまうわけです。
「セルC6」÷「セルC6からセルC13までの合計」＝「セルD6」の構成比
各行の合計値だけは「セルC5からセルC12まで」と固定したままがいいのに、1行下にずれてしまうわけですね。
そこで、フィルハンドルをコピーした際に「動かしたくないセル参照がある場合」は、それを「絶対参照」に切り替えるのです。
「絶対参照」とは、数式をコピーしても、その数式内のセルが一緒に動かないようにする参照方法です。
今回は、D列の5行目の数式を下の行に向かってコピーしていきます。
しかし、C列の「5行目から12行目までの足し算」は常に固定しておきたいわけです。
したがって、「行番号の5の前」と「行番号の12の前」に絶対参照を意味する「$」を付けるようにします。
上の画面は、6行目と7行目の結果を比べてみたところです。
もちろん「セルD5」のフィルハンドルを12行目までコピーしています。
「セルD6」を見てみましょう。
6行目の構成比は、6行目の合計(セルC6)を「5行目から12行目までの全合計値」で割ればいいわけですから、上の画面のような数式になります。
C6/SUM(C$5:C$12)
次に「セルD7」です。
7行目の構成比は、7行目の合計(セルC7)を「5行目から12行目までの全合計値」で割ればいいわけですから、上の画面のような数式になります。
C7/SUM(C$5:C$12)
同様に12行目まで同じような数式が入力され、構成比が一気に表示できました。
％表示になっていない場合は・・
問題文は、構成比を「全体の何％か」と書かれているので、％表記にします。
以下のように％表記になっていない場合は、％表記に切り替えましょう。
「セルD5」から「セルD12」までを範囲選択し、「ホーム」タブの「％」をクリックします。
半分終わりましたね。続いて最大値と最小値を求めてみます。
最大値を一編に求める
合計と構成比のそれぞれの最大値を求めてみたいと思います。
これは「MAX関数」を使うのですが、先ほど使った「オートSUM」ボタンは他の関数でも使えますので、「オートMAX」で計算してみましょう。
合計の最大値を求める「セルC15」をクリックします。
「オートSUM」ボタンの下向き矢印をクリックすると、一覧に「最大値」があるのでこれをクリックします。
「セルC15」に「MAX関数」が入り、引数に「セルC5からセルC12」までを指定します。
これでエンターキーを押すと、5行目の1138が最大値として表示されます。
さて、隣のD列の「構成比」はもちろんフィルハンドルをコピーして・・と言いたいところですが、今回はちょっと違うやり方をやってみます。
実はオートSUM(ここではオートMAX)ボタンは、一編に複数の行や列を計算してくれるのです。
ここでは、合計の「C列」と構成比の「D列」の最大値を一気に表示してみたいと思います。
「セルC5」から「セルD15」までをすべて範囲選択します。
「オートMAX」ボタンをクリックしてみましょう。
同じように結果がきちんと表示されました。
しかし数式をよく見てみると、参照範囲が14行目までになっていますね。
「オートMAXボタン」は、セル参照を自動で行ってくれるのはいいとしても、直前のセルまで参照してしまいます。
13行目と14行目はデータがないため「0」として認識してくれるので、結果的には最大値は間違っていません。
しかし何だか気持ち悪い、と思ってしまうかたは、オートMAX関数が参照したセル「C5:C14」を「C5:C12」と手動で直す必要があるでしょう。
もちろん参照セル範囲を直さなくても最大値の結果は同じです。ただし参照している範囲は本来のデータ部分ではないのが微妙なところです。
フィルハンドルでコピーするか、オートMAXボタンを使うか、これはどちらでもいいと思います。
合計の最大値(セルC15)をMAX関数で求め、さらに構成比の最大値(セルD15)をMAX関数で求め、と同じ操作を2度行うのは効率が悪いので、それは間違いと言えるでしょう。
最小値を求める
最後に「最小値」を求めてみましょう。
これは「MIN」関数を使います。
「最大値」とやり方はほぼ同じです。
ただし「オートMIN」は、その2行上(15行目)の「最大値」が邪魔をしてうまく指定できないと思います。
したがって、この場合の「最小値」は、まずC列の合計列の「最小値」を求めて、フィルハンドルでD列にコピーするのが一番効率が良くなります。
「セルC17」に、MIN関数を使い引数に「セルC5からセルC12」までを指定します。
「セルC17」には355が表示されました。
フィルハンドルを隣のD列にドラッグすると「セルD17」には構成比の最小値が表示されます。
これで黄色いセルすべてに値が表示されました。
しかし・・最大値と最小値については、実際にフィルハンドルでD列にコピーするとパーセンテージ表記ではなく、小数点が表示されませんでしたか？
再度「セルD15」や「セルD17」を選択して、「パーセンテージ」ボタンをクリックしてパーセンテージ表記に戻してもいいのですが、それだとやはり効率が悪いですよね。
最後に、フィルハンドルの特殊なコピー方法についてやってみたいと思います。
フィルハンドルの右ドラッグ
実はフィルハンドルで数式をコピーすると、セルの表示形式も一緒にコピーされてしまうのです。
合計の最大値、「セルC15」をアクティブにすると、表示形式が標準となっています。
このままフィルハンドルで「セルD15」へ数式をコピーすると、表示形式も一緒にコピーされます。
つまり、「セルD15」の表示形式をあらかじめ「パーセンテージ」に指定していても、「セルC15」の「標準」がコピーされてしまうというわけです。
この場合、フィルハンドルをコピーする際に、マウスをドラッグするわけですが、右クリックでドラッグするようにします。
右ボタンを離したタイミングで、メニューが表示されます。
一覧から「書式なしコピー」をクリックします。
この操作の前に、「セルD15」の表示形式が「パーセンテージ」に設定されているのを確認しておきます。
同じように「セルD17(パーセンテージ表記を設定済み)」の「最小値」もフィルハンドルを右ドラッグでコピーして、「書式なしコピー」をクリックしてください。
完成形は以下のようになります。
問題1まとめ
さて、最初の問題1では、以下の点を操作しました。
- 合計(SUM関数)
- 割り算の数式の作成
- 相対参照と絶対参照
- 最大値(MAX関数)
- 最小値(MIN関数)
- フィルハンドルの右ドラッグ
実務としては、このあたりはまず簡単にクリアしたいところですね。
入社できた人もたくさんいると思います(笑)
次回は、問題2を一緒にやっていきましょう。