準備問題下の表の売上高合計と市場占有率のセルに適切な計算式を入れて表を完成させなさい。市場占有率は、各社の売上高÷全社の売上高合計で計算できる。ただし、同じような計算式は可能な限りコピーでラクをして入力すること（絶対参照をうまく使うとラク）
| A | B | C | |
|---|---|---|---|
| 1 | 会社名 | 売上高 | 市場占有率 |
| 2 | きつね食品 | 192 | ? |
| 3 | たぬき物産 | 168 | ? |
| 4 | いなり商事 | 224 | ? |
| 5 | 合計 | ? | ? |
準備問題| A | B | C | |
|---|---|---|---|
| 1 | 会社名 | 売上高 | 市場占有率 |
| 2 | きつね食品 | 192 | ? |
| 3 | たぬき物産 | 168 | ? |
| 4 | いなり商事 | 224 | ? |
| 5 | 合計 | ? | ? |
例題1. 消費税の計算で1円未満の端数が出た場合の切り捨て処理を行う
消費税が5%のとき、150円のカップラーメンを3個購入した。消費税額と、消費税込みの支払い総額は幾らになるかを求めてみよう。(*1)
Step 1. データを入力する: データは(data04.xlsx の シート例題1）
まず、計算に必要な基礎データと説明用の文字列を入力する。Excelは単に 5% と入力すると、自動的に0.05と解釈してくれるので便利。
Step 2. 計算式をとりあえず入力する
ここまで学んだ計算式の作り方を思い出して、
を入力すればよい。計算式が入ると、こんな感じになる。しかし、このままだと消費税額に1円未満の端数がついてしまうので、切り捨て処理を行いたい。
Step 3. 切り捨て処理を INT 関数で行う
切り捨て処理のような操作は、ここまでに出てきた四則演算の範囲ではできない(*2)。Excelでは、このような処理を行うためにいくつかの関数があるが、ここでは一番単純な INT 関数を用いる。INT関数は、数値を一つ与えると、戻り値としては引数を超えない最大の整数を戻す関数である。INT関数に与えられるものは、
のいずれでもよい。もちろん、参照先のセルに入っている値や計算式の結果が数値でなければエラーとなる。この例題では、セル D6 の消費税額の1円未満を切り捨てたいので、セル D6 を以下のように書き換える
すると、小数点以下が切り捨てられて下のような計算結果となる。
(*1)現在は税込価格での表示が原則であり、税込価格に含まれる消費税相当額は「税込価格×5/105」と考えるのが原則。税抜価格から消費税額を求めて表示するという扱いは適切な操作ではない。詳細は国税庁web（http://www.nta.go.jp/）などを確認のこと。
(*2)セルの表示形式の設定や
(小数点表示桁下げボタン）を使って小数点以下を表示しないようにすると、表示上は小数点以下が消えてスッキリするが、データとして小数点以下の値がなくなるわけではないので注意。たとえば、0.3, 0.3, 0.4が入っているセルの表示桁数を変更して、表示上は全部0にしたとしても、この3つのセルの合計を計算するとちゃんと1になる。INT関数で処理した場合とは全く異なる。
練習問題1. INT関数に以下の数値を与えたとき、関数の戻り値はいくつになるか。まず自分で予想してから、実際に Excelを使って実験してみなさい。データは(data04.xlsx の シート練習問題1）
例題2. 関数名が分かっている場合にヘルプを調べる
ヘルプの読み方練習として、まず関数名が分かっている場合を試す。
Step 1. 「関数の挿入」ダイアログを開く
数式ボックスの左側にある
をクリックすると、関数の挿入ダイアログが開く。
Step 2. 「関数の検索」ボックスに INT と入力して、
をクリックする
この操作で、「関数名」の欄にINT関数が出てくる
Step 3. 「この関数のヘルプ」をクリックすると、ヘルプが表示される。
ヘルプの文章を引用すると、こんな感じ。
INT
指定した数値を超えない最大の整数を返します。
書式
INT(数値）
数値 切り捨てて整数にする実数を指定します。
まず、最初に関数名が入っていて、次に概要が書いてある。「指定した数値を超えない最大の整数」ということは、正の数に対しては小数点以下を取り除いた数値が戻り値になるが、負の値についてはちょっと異なる。たとえば -0.3を超えない最大の整数は 0ではなく -1であるから、INT(-0.3)は0ではなく -1 となる。このへんは「切り捨て」という言葉のイメージにだまされてしまうとはまる可能性がある。
練習問題2. オートSUMツールを使っていままで数式に入れてきた SUM関数についてヘルプで調べてみなさい
例題3. 「やりたいこと」から適切な関数を探す
Excelには300個以上の関数があるため、関数名からヘルプを調べるというのはあまり現実的ではない。実際にはなにかやりたい処理がまずあり、そのために必要な関数を探す場合の方が多い。ここでは、実数の小数点以下を四捨五入して整数化する方法をしらべてみよう。
やりたいことから関数を探すためにも、例題2で利用した「関数の検索」が使える。
Step 1例題2と同じように、「関数の挿入」ダイアログを開き、「関数の検索」に「四捨五入」と入力して
Step 2. ROUND関数のヘルプを見てみる。
練習問題3. 数値の切り捨てをする関数を検索してみなさい。
※ROUNDDOWN、TRUNC、FLOOR、INTなどが出てくるはず
練習問題4.INT関数、ROUNDDOWN関数、FLOOR関数、TRUNC関数を実際に使って、以下の表を完成させてみる
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | ROUNDDOWN関数 | TRUNC関数 | FLOOR関数 | INT関数 | |
| 2 | -4.1 | =ROUNDDOWN(A2,0) | =TRUNC(A2) | =FLOOR(A2,1) | =INT(A2) |
| 3 | -0.5 | ? | ? | ? | ? |
| 4 | 0 | ? | ? | ? | ? |
| 5 | 0.5 | ? | ? | ? | ? |
| 6 | 4.1 | ? | ? | ? | ? |
練習問題5. 以下の作業をできそうな関数を、ヘルプを使って探してみなさい。また、指示された計算を探した関数を使って行ってみなさい。 データは(data04.xlsx の シート練習問題5）
例題4. 試験成績の国語の最高、最低、平均、標準偏差を関数を使って求め、個人成績の偏差値を計算しなさい
Step.1 仮想的な試験結果のデータは(data04.xlsx の シート試験結果）
Step.2 ヘルプを使って最高点を求める関数を探す。検索語としては最高点ではなく「最大値」と使うとよい
関数に関するヘルプを見ると、以下の通り。
MAX
引数リストに含まれる最大の数値を返します。
書式
MAX(数値 1,数値 2,...)
数値 1 ,数値 2,... 最大の数値を見つけるため、1 ? 30 個までの数値を指定することができます。
MIN
引数リストに含まれる最小の数値を返します。
書式
MIN(数値 1,数値 2,...)
数値 1,数値 2,... 最小の数値を見つけるため、1 ? 30 個までの数値を指定することができます。
最大と最小を求めるだけなら、MAX()関数と MIN()関数でOKらしい。これを使って、まず国語の最高点と最小点を計算してから、あとは計算式のコピーで残りの科目も簡単に計算できる。
Step. 3 科目平均点を求める
練習問題3でやった通り。AVERAGE()関数を使えば平均は計算できる。
Step. 4 科目標準偏差を求める
標準偏差を計算する関数を検索するといくつか出てくるが、ここでは STDEV()という関数を使っておく
Step. 5 偏差値を計算する
偏差値の計算式は以下の通り
科目偏差値 = 10×（科目得点−科目平均点）÷科目標準偏差＋50
数式コピーでできるだけ簡単に計算式を入れるためには、絶対参照をうまく使う必要がある。
これをまとめると、学籍番号20066696の学生の国語の偏差値を計算する式は以下のようになる。
=10*(B3-$B$34)/$B$35+50
あとはこれをコピーすればおわり。
練習問題4. 例題4のデータをつかって、残りの4科目についても同じような計算を行いなさい
ヒント： 列方向にコピーしていくことを考えると、例題4で入れた計算式は絶対参照で固定している部分が多すぎる。$をつけて固定している部分を必要最小限にすれば、残りも単純なコピーで解決するので工夫してみること。データは(data04.xlsx の シート試験結果）
例題5. 日経平均の終値を、移動平均を使って平滑化してみる
Step1. 日経平均株価（2008/8/1〜2009/11/30）の データは(data04.xlsx の シート日経平均）
Step2. 終値を折れ線グラフにしてみる（あとで別系列のデータを追加する都合から、ここでは散布図を使って折れ線グラフを描く）
できあがりはこんな感じ。
Step3. 軸の目盛範囲を変更する
終値の変化している範囲が7000〜14000円くらいの範囲なのに縦軸が0円からの目盛になっているので変化が分かりづらい。最小値を6000、最大値を14000に変更してみる。
目盛の最小値、最大値を変えるには？ → Excel編 Q42 目盛間隔の変更のあたり
できあがりはこんな感じ。
Step4. 8月1日〜8月7日の5日間移動平均を計算してみる
縦に変化を大きく出したら、今度は凸凹が沢山あって全体の見通しが悪くなった。このような時には、着目している日の前後何日かの範囲で平均をとっていく「移動平均」という方法を使うと近い範囲での凸凹が均されてわかりやすくなる。
株では、5日移動平均（着目している日の前々日、前日、当日、翌日、翌々日の平均）や25日移動平均（前12日＋当日＋後12日）などが短期の移動平均としてはよく使われる。当然のことながら、移動平均を取る場合はデータ期間の最初と最後は値が計算できない部分がでるため、あまり数が少ないデータでは使いづらい手法。
1. アクティブセルを、データ期間の最初から3日目の8月5日の移動平均欄（セルF4）に移動する
2. ヘルプで「平均値」を検索してみると、AVERAGEという関数が平均を計算する関数らしいので、それを使う。
引数の (数学的な) 平均値を返します。
書式
AVERAGE(数値 1,数値 2,...)
数値 1 ,数値 2,... 平均を求める数値データを指定します。引数は 1 〜 30 個まで指定できます。
解説
- 引数には、数値、数値配列、または数値を含む範囲を参照する名前かセル参照を指定します。
- 引数として指定した配列またはセル範囲に文字列、論理値、空白セルが含まれている場合、これらは無視されます。ただし、数値として 0 (ゼロ) を含むセルは計算の対象となります。
3. F4セルに =average( と入力する。すると、関数に必要な引数を Excelが自動的に教えてくれる。
4. データを計算したい範囲E2:E6を指定して、カッコを閉じる
4, Enter を押すと、計算式の入力が終わり、計算結果が表示される。
Step 5. 計算式をコピーして、8月6日〜翌年の11月30日の間の日について5日間移動平均を計算する
※最後の2日については、後の日のデータがないため移動平均が計算できない
Step 6. 終値のグラフに5日移動平均のデータを追加する
Step 3で作成したグラフに、Step 5で計算した5日移動平均のデータを追加する
グラフにデータを追加するには？ → Excel編 Q37
練習問題5. 例題5で作成したグラフに、25日移動平均のデータも追加しなさい
Tips: Excelで範囲を指定するには？
練習問題6.
data04.xlsx の シート試験結果に入っているのは仮想的な試験の得点を示したデータである。このデータを使って、5科目の合計得点を計算し、さらに合計点でクラス内の順位をつけなさい（ヒント： 順位は Excelの関数で計算できるので、適切な関数を使って求めると簡単。）
©2016, Hiroshi Santa OGAWA
このページにアダルトコンテンツ、XXXコンテンツ類は一切含まれていません。暴力反対.