表の計算
表へのデータ入力だけならワードでもできます。エクセルの特徴の一つは、特別なプログラムなしで計算ができることです。
-
数字の計算
-
式の挿入
表の項目は列と行の番号で指定します。下の図で、A2 の値は 12 です。ここで、C2に =A2*B2 と入力します。ここで、*は「掛け算」の意味です。先頭の = が、データの入力でなく、「式」の入力であることを意味します。すると、入力する文字は、=A2*B2ですが、表示は A2 と B2 を掛けた値(276)になっています。つまり、 = で始まる 「式」 を入力すると、「計算」 した値が表示されるのです。
A2 などの 「セル」の番号を直接入力する替わりに、A2 の「セル」をクリックする方法もあります。
A2 や B2 の値を変更すると、即座に 計算した値も変化します。掛け算以外に、+ − *(乗算) /(除算) が利用でき、(A2 + 10)
* B2 のように括弧も利用できます。式を変更すれば、すぐに表示される値も変化します。
-
式のコピー(相対指定)
もう一つ「すごい」ことは、式がコピーできることです。上の例で、データの連続入力の要領で C2 を選択後、枠の右下の四角をC4までドラッグします。すると、式がコピーされ、各行の計算ができます。式を見ると、3行の式は =A3*B3 になっています。コピーするとき、セルの番号が自動的に変化しています。セルの番号が自動的に増えているところが「すごい」ですね。
-
総計を求める
さらに総計を求めることも簡単です。C5 を選択し、数式タブから ΣオートSUMをクリックします。メニューで 「合計」選びます。すると、C5
にC列の合計が計算されます。B2 や A4 の値を変更すると、合計の値もすぐに更新されます。
C5 の内容は =SUM(C2:C4) となっています。これは、「C2 から C4 までを合計(SUM)しなさい」、の意味です。
-
変化しないコピー(絶対指定)
しかし、機械的に変化しては困る数式もあります。「売り上げの割合」を計算する場合を考えます。2行目の割合:D2は = C2*100/C5 です。これを
C4 までコピーすると、ちょっとおかしいことになります。
調べると、D3 は C2 と C5 が自動的に増加し =C3*100/C6 になっています。C6 は数字がないので 0 の扱いになり、0でわり算すると
DIV/0 (0で割った!)のエラー(誤り)になります。
この場合、式 = D2*100/C6 を = D2*100/C$6 にすると解決します。$ はコピーするとき変更しないことを指示する記号です。
このように、$がついているセルの指定を絶対指定と呼びます。絶対指定されたセルの番号はコピーしても変化しません。
-
関数を利用する
-
関数
エクセルではいろんな分野で、「関数」が利用できます。たとえば、最大値、データの個数(オートSUM)、利息計算(財務)、文字の結合(文字列操作)、時間や日数の計算(日付)、三角関数(数学)、偏差値(その他、統計)、などです。関数の一覧は、数式タブの「関数ライブラリー」で知ることができます。
たとえば、=AVERAGE(A2:A5) は A2からA5の範囲の平均値を計算する関数です。
-
関数式の挿入
「セル」に「関数」を挿入するには、関数を置きたい「セル」を指定し、数式タブから「関数」を選択します。下のようにデータを用意し、G1 を選択して、オートSUM
から 「平均」をクリックします。
エクセルは「左のすべての数値の平均」と考え、B1:F1 の範囲を選択し、AVERAGE(平均)関数を作成します。この範囲で良ければ、enterキーをおします。
G1 に平均値(31.4)が表示されます。範囲を変更するには、 範囲 B1:F1 を直接変更するか、マウスで範囲を選択します。
-
財務関数
ローンの返済計画を立ててみます。借入金を1千万、このうち3百万は別にボーナスで払うものとします。 PMT(利率(月当たり)、期間(月数)、借入金)
で、毎月の支払額が計算できます。
A2に年利を入れます。月当たりの利息は B2/100/12 となります。A3 に返済年数(20年)、A4 に借入金、A5 にボーナス(年2回)で支払う額を入力します。
毎月の支払い額 D2 は =PMT(B2/100/12,B3*12,B4-B5)
ボーナスからの支払い D3 は =PMT(B2/100/2,B3*2,B5)
となります。総支払額 D4 は =D2*B3*12+D3*B3*2
です。ボーナス月の支払いは D2+D3 になります。
-
日付、年齢
2005/5/24 の形式で日付(誕生日など)を入力できます。また、H10.1.1 の形式も利用できます。二つの日付の年の差は
=DATEDIF(B1,A1,"y")
で計算できます(この関数は「関数ライブラリー」のメニューにはありません)。また、今日の日付は =TODAY() で入力できます。
これを利用して、今日の日付の年齢を計算できます。C1 に =TODAY() を入力します。これで、今日の日付が表示されます。
A2から 誕生日を入力します。西暦または和暦で入力します。B2 に =DATEDIF(A2,C$1,"Y")
を入力します。 セルの番号を変化させないため、C1 には $ が必要です。この式をコピーすると、年齢が表示されます。
年齢の値から、「60歳以上の人数」を調べてみます。これには
COUNTIF(範囲、条件)
関数(その他:統計)を利用します。条件は ">60" で「60以上」の意味になります。
B6 に =COUNTIF(B2:B5,">60") を入力すると、60歳以上の人数が表示できます。
なお、年齢の表示形式を揃えるには、「ホーム」タグで、中央の「数値」の下の▼をクリックし、「表示形式」のダイアログで「標準」の横の▼をクリックします。ここで、「長い日付」か「短い日付」を選択すると、表示を統一できます。
-
時刻
時刻も 9:5 の形式で入力すると、9時間5分 の時刻として扱われます。二つの時刻の差の計算もできます。時刻は内部では、1日を 0〜1.0
の値(シリアル値)で記憶しています。時間の差から、たとえば時給を計算するには、時刻のセルに 24倍*時給 を掛けてやれば計算できます。D2 のセルは
=C2*24*D$1 とします。この計算は標準では小数点の値になりますから、「ホームタブ」の「数値」で表示形式を 「会計」にすると、小数点以下が4捨5入されます。
-
数学・統計関数
数学・三角関数には、数学のほとんどすべての初等関数がすべて含まれます。また、統計には多くの分布関数が含まれます。以下に、一部のみを示します。
数学 |
|
統計 |
|
cos |
三角関数 |
Average |
平均 |
sin |
三角関数 |
Var |
分散 |
log10 |
対数(底が10) |
count |
データ数 |
sqrt |
平方根 |
NormDist |
正規分布 |
power |
べき乗 |
Max |
最大 |
round |
整数に丸める |
Min |
最小 |
Combin |
nCk |
BinomiDist |
二項分布 |
-
度数分布(関数配列)
度数分布を調べる場合、freqeency()関数の配列を利用します。データ列(G)の値の度数(頻度)を調べるものとします。調べたい度数の表をI列に作成します。度数分布は、=FREQUENCY(G2:G8,I2:I5)
で求めることができます。結果の欄のJ2〜J5 を選択し、関数を入力してから、Ctrl+Shift+Enter を押します。これが、関数配列の入力法になります。修正する場合は、すべての関数を選択してから修正し、Ctrl+Shift+Enterで確定します。
データが0~100で、度数が10単位の場合、度数までの値の数が求まります。
-
他の表の参照
別の表の値を参照できます。下のように価格表を作成しておきます。納品表を作成するとき、単価の列には、
=VLOOKUP(B49,B43:C45,2,FALSE)
と、参照関数を設定します。ここで、B49は参照するキーの名前でこの場合下の表の「りんご」です。B43:C45 は参照する表の範囲で、参照するものは一番左に書きます。次の
2 は、表から取り出す値の番号で、これは、参照する表の内部の列番号です。
納品表の単価にこの VLOOKUP を入れておけば、名前を入れると単価が自動的に入力されます。