課題
| 商品番号 | 品名 | 量 | 価格 |
| 048 | ピーマン | 150g | 98 |
| 064 | キャベツ | 1玉 | 198 |
| 065 | キュウリ | 3本 | 148 |
| 075 | ニンジン | 1kg | 198 |
| 079 | オクラ | 10本 | 158 |
表1を元に「商品番号」と「数量」を埋めるだけで、表2で示すような納品書ができるようにする。
「納品書」の枠組みを作成する。 (説明のために、セルに色がついているが実際につける必要はない。)
緑の欄には VLOOKUP関数を、グレーの欄には「数量」×「単価」の式を入れる。 また小計欄には、各品目別の価格の合計を求める式(SUM)を、消費税、請求金額にも適切な式を入れる。
商品番号の先頭の 0 をきちんと表示するためには「書式」を「文字列」とする。
| 商品番号 | 品名 | 数量 | 単価 | 価格 |
| 064 | キャベツ | 3 | 198 | 594 |
| 小計 | 594 | |||
|---|---|---|---|---|
| 消費税(5%) | 29 | |||
| 御請求金額 | 623 | |||
たとえば、品名欄には表1から与えられた商品番号(この場合は 064)に該当する行を検索し、2列目を表示すれば良いことがわかる。 したがって、VLOOKUP関数を呼び出し、
を入れることになる。
しかしながら、商品番号が入力されていない場合、「品名」と「単価」が得られないため、納品書として使えない。
| #N/A | #N/A | #N/A | ||
| 023 | #N/A | #N/A | #N/A |
これに対応するためには、IFを用いて場合分けを行う必要がある。
したがって、たとえば単価欄の場合は、次の図のようになる。
なお、空欄に対する 0 を表示させないためには「ツール」→「オプション」→「表示」の中の「ゼロ値」に対するチェックを外せば良い。
上記の方法では、商品番号が入力されていない場合にはうまくいくが、間違った商品番号を入力された場合は、「#N/A」となる。 このような場合に対応する方法を考えよ。 「#N/A」かどうかは関数ISNAを使って判定できる。
ヒント(品名欄)
領域について名前をつけることができる。 たとえば、上の表1を Internet Explorer からコピーした場合には「TABLE」という名前になっている(はずである)。 このように名前をつけると、式で参照する範囲を名前で決めることができるので、うまく使うと便利である。 名前付けの追加/削除には[Ctrl]-[F3] キーで「名前の定義」ウィンドウを呼び出して行う。
以下はオムニバス方式で6人の教員が採点した30名の成績である。 各生徒が獲得した6つの点数について最高点と最低点を除外した 4つの点数の平均を計算し、平均点の高い順に並べ替えよ。
ヒント
最高点や最低点の抽出には関数 LARGE を使え。
| A先生 | B先生 | C先生 | D先生 | E先生 | F先生 | |
| 生徒01 | 75 | 65 | 75 | 85 | 80 | 90 |
| 生徒02 | 75 | 65 | 85 | 85 | 55 | 80 |
| 生徒03 | 70 | 85 | 80 | 85 | 85 | 85 |
| 生徒04 | 80 | 60 | 80 | 85 | 90 | 60 |
| 生徒05 | 90 | 70 | 75 | 65 | 90 | 60 |
| 生徒06 | 75 | 65 | 80 | 85 | 75 | 90 |
| 生徒07 | 80 | 70 | 80 | 85 | 80 | 90 |
| 生徒08 | 80 | 85 | 85 | 85 | 85 | 60 |
| 生徒09 | 85 | 85 | 80 | 85 | 95 | 90 |
| 生徒10 | 85 | 85 | 80 | 85 | 90 | 60 |
| 生徒11 | 85 | 70 | 85 | 85 | 90 | 60 |
| 生徒12 | 80 | 85 | 85 | 85 | 90 | 90 |
| 生徒13 | 75 | 65 | 80 | 65 | 40 | 60 |
| 生徒14 | 75 | 85 | 85 | 85 | 85 | 60 |
| 生徒15 | 75 | 85 | 80 | 85 | 60 | 90 |
| 生徒16 | 70 | 85 | 80 | 85 | 85 | 85 |
| 生徒17 | 70 | 85 | 85 | 85 | 60 | 86 |
| 生徒18 | 80 | 85 | 80 | 85 | 65 | 86 |
| 生徒19 | 75 | 0 | 75 | 0 | 65 | 90 |
| 生徒20 | 0 | 0 | 75 | 65 | 35 | 90 |
大量のデータを入力している場合にはフォームを利用した入力を用いた方が便利な場合がある。
フォームを用いた入力を行う場合には、いずれかのデータセルを選択後、「データ」→「フォーム」を実行する。 なお、フォームを利用する場合オートフィルは利用できないので、入力の手間を減らすためにはあらかじめ埋められる範囲の入力はしておいたほうが良い。
フォーム入力ウィンドウの右上にでている数字は「現在編集中のデータ／全データ数」である。
次のデータに移動する際には「次を検索」、前に戻る場合は「前を検索」、最後にデータを追加する際には「新規」を押す。 必要ないデータを削除することもできる。フォーム入力を終える場合には「閉じる」を押す。
なお、エクセルがデータ範囲を正しく推測できない場合は、範囲全体を選択した後、フォームを起動する。
複数の採点官による得点を評価する際に、最高点および最低点を除いた残りの平均点が用いられることがある。
(例. オリンピックの体操競技等)
エクセルを用いてこのような採点を行うようにというのが、課題20である。
このためには、エクセルで用意されている LARGEやSMALL関数を用いる。
LARGEおよびSMALL関数は、与えられた領域中(範囲)から n番目(順位)に 大きな(小さな)値を返す。 なお、最大値および最小値は MAX関数やMIN関数でも得ることができる。
入力した表の右側に 「最高」、「2番目」、「3番目」、「4番目」、「5番目」、「最低」の点数を参照する領域を作成し、 「最高」は MAX関数を、「2番目」から「4番目」までは LARGE関数を、 「5番目」には SMALL関数を「最低」については MIN関数を用いて値を表示するようにせよ。
コピーをして領域を埋めるためには参照方法を適切にする必要があることに注意せよ。 ($ はどこにつけるのが正しいか?)
表が出来上がれば、最高点および最低点を除いた平均値を一番右に表示するように設定せよ。
なお、エクセルには与えられたデータの中から、指定した割合の範囲に含まれるデータを除いて平均を求める TRIMMEAN関数があるので、各自でどのような振る舞いをするのか調べてみよ。
エクセルでデータを並べ替えることを「ソート」と呼ぶ。 行でソートする場合は、並べ替えたいデータが入力されているセルを選択した後、「データ」→「並べ替え」を実行する。
ソートする領域をエクセルが正しく見つけられなかった場合は「キャンセル」を押して取りやめ、正しい範囲全体を選択した後、「データ」→「並べ替え」を実行する。
並べ替えの際に優先する順位を選択した後、「OK」を押すとソートが実行される。
エクセルがソートする領域を「正しく推定できる」ような単純なデータ構造であるときは、
キーとなる列の一つのセルを選択後、メニューバーにある
を押すことにより容易にソートを実行できる。
なお、ソートする際にはまとめて並べ替えを行いたい列(行)を含めた範囲を指定する必要がある。
データベース用のフォームを使って15名分の名簿(住所録や電話番号など任意)を作成し、並び替えや抽出を行え。
データを入力する時には、セルを選択した状態で「データ」→「フォーム」を選ぶ。 並び替えや抽出も「データ」→「並べ替え」、「データ」→「フィルタ」を選んで簡単にできます。
フォームを用いた入力、および並べ替えは課題20を参照のこと。
データの抽出には「フィルタ」を用いる。
「データ」→「フィルタ」→「オートフィルタ」を実行すると 各列の見出しに▼ボタンが表示され、設定されたことが判る。
見出しの▼ボタンを押すことによりフィルタ条件を選択できる。 デフォルト以外のフィルタを設定するために、次の２つが用意されている。
フィルタが選択されている列は▼の色が異なる。 フィルタを解除するには設定されている列で「（すべて)」を選択する。
オートフィルタより詳細な条件設定でデータを抽出できる。
条件を設定するための領域として見出し語と同じ内容を入力したセルを準備する。
条件を列記する。1行に記述された複数条件は and が取られ、 異なる行に記述された条件は or となる。
「データ」→「フィルタ」→「フィルタオプションの設定」
条件の入力。「電話番号がフリーダイアルである」 or 「福井市内」。
フィルタオプションの起動
実行結果。選択範囲内を選んだため、条件に一致しないものが隠されている。
フィルタを解除するには「データ」→「フィルタ」→「すべて表示」を実行する。
課題20で作成したシートをコピーし、シート名を課題22とする。 データベースの抽出機能を用いて以下のレコードを抽出しなさい。
課題21 の復習であるので、課題21を参照のこと。