ここではExcelで何ができるのかを簡単に紹介するため実際に操作してもらいながら機能を体験できます。内容はセルへの数値入力、計算式とそのコピー、セルの書式設定、データの並べ換え、関数による計算、条件分岐、ヒストグラムまで含まれています。少し大変ですが、1時間程度で機能を紹介し、体験いただけます。Office365のExcel(Mac)を利用して説明しています。(2021.01.13更新)
Excelのセルの番地(A1やB12など)や基本機能の解説は「情報整理と表計算」を参考にしてください。
目次
- オートフィルによる連する数字の入力
- 左セルの定数をかける
- 左セルの平方根を計算する
- 表示を小数点第2位に設定する
- 列の幅を変えて新しい列を挿入する
- 表をコピーし、平均値と標準偏差を求める ーデータを整理する
- 平均値から棒グラフの作成し、標準偏差を誤差範囲としてグラフに追加する
- 条件分岐(IF, IFSなど)を利用して数値をABCに分類する
- COUNTIFを使ってABCの組に入るデータ数を数える
- データの並び替え
- データの度数分布表(ヒストグラム)を作る
1.オートフィルによる連続する数字の入力
表計算で新規シートを開きます。
左上の隅が「A1」セルです。セルに数字を入れ、自動入力を試してみましょう。
①A1のセルをクリックし、”1″を入力
②リターンをクリックしてA2に移る
③A2のセルに”2″を入力
④マウスでクリックしたままセルA1とA2を塗り指定する
⑤マウスのカーソルをA2の右下角に移動して、カーソルが黒十字になったらクリックしたままA17まで引く
⑥A1からA17に1から17の数字が入力されるのを確認
2. 式の入力と式のコピー
さて、次に式を入力してみましょう。1で作ったシートのB1にA1の二乗を計算します。さらにその式をB17までコピーして表を完成させてください。
①B1のセルをクリック
②”=”をクリック
③文字入力でセルA1をクリック
④A1に緑枠を確認
⑤”^”をクリック
⑥2を入力
⑦B1が”1″を確認
⑧B1のセルをクリック、マウスをセルの左隅に移動するとマウスカーソルが黒十字に変わる
⑨クリックしたままB1からB17までカーソルを移動
⑩B1からB17に1から17までのA列の二乗が計算されていることを確認
3. 式の入左セルの平方根を計算する
次は、セルにセル番地を含む式を直接入力する方法を練習します。C1のセルに平方根を計算してみましょう。
平方根はルート(√)の事です。3の2乗が9で、9の平方根が3です。べき乗は”^”の後の数字で表示しますが、平方根や3乗根は、0.5(もしくは1/2)、1/3などを使い計算します。
①C1に”=A1^0.5″と入力 ((A1)0.5はA1の平方根です)
②C1のセルの式をクリック
③右クリックして”コピー”を選択
④C2からC17をマウスで選択して左クリックで”ペースト”を選択
※ショートカットキーを覚えよう！
コピーやペーストはマウスで操作するよりショートカットキー”command+C”(windowsではcntl+C)や”command+V”(windowsではcntl+v)で行う方が早く操作ができます。是非覚えてください。その他にcommand +Aは全部指定。
MacのショートカットキーとWindowsのショットカットキーはcommandとCntlの違いはありますが、共通のキーが多いです。
4. 表示を小数点第2位に設定する
平方根を計算すると、4と9以外の筋では少数点以下に数字が長く続きます。見易くするため書式設定で小数点以下２桁までの表記にしましょう。書式設定のショートカットキーはcommand+1(Mac) / Cntl+1(Win)
①Cの列を選択(緑の枠がつく)
②フォーマット→セルをクリック
③分類から数値を選択
④小数点以下の桁数に”2″を入力
⑤右下の”OK”をクリック(もしくはreturn もしくはenter)
⑥セルの表示が小数点以下2桁であることを確認
excelのシート幅が狭く、コマンドが折りたたまれている場合
5. 列の幅を変えて新しい列を挿入する
列Aの幅を狭くし、列Aの左に列を追加してみましょう。
①Aをクリックし、AとBの境界をクリックして左にカーソルを移動して列Aの幅をせまくする
②A列が狭くなったのを確認
次はA列の左に新しい列を追加します。方法は2つあります。
方法1:マウスの右クリック
①A列をクリック
②マウス右クリックで”挿入”を選択(新しいA列が増える)
方法2：シートメニューのセルから挿入を選択
①シートの上メニューをホームをクリックA列をクリック
② セルの覧から挿入を選択
③A列に新しい空列を確認
※Macはシート以外に上にメニューがある! Windowsはアプリケーションはアプリのウインドが全てですが、Macでは画面の上部にアプリケーションのメニューバーがあります。これはWindowsではウインドを消すとアプリが終了する一方で、Macではこのアプリケーションメニューからソフトを終了しない限りソフトが終了しません。慣れると便利です。
Macでは、マウス操作でこのアプリケーションメニューの挿入から列を選択します。
6. 列の幅を変えて新しい列を挿入表をコピーし、平均値と標準偏差を求める ーデータを整理する
表計算ソフトによく利用される平均値と標準偏差(平均値の周りのデータのばらつきを示す値)を計算する関数を使ってみましょう。解説は統計の用語解説を参考にしてください。
表示されているシートの下の表データをdownloadしてexcelで開いてください。
A1からD10のセルまでにコピーしても結構です。上手くできない場合は、下の数字を同じように手入力してください。
| 比較表 | |||
| 番号 | Aクラス | Bクラス | Cクラス |
| 1番 | 165 | 177 | 177 |
| 2番 | 172 | 159 | 168 |
| 3番 | 188 | 182 | 183 |
| 4番 | 185 | 177 | 176 |
| 5番 | 174 | 181 | 168 |
| 6番 | 170 | 181 | 168 |
| 7番 | 162 | 179 | 175 |
| 8番 | 168 | 173 | 182 |
①A11に”平均値”、A12に”標準偏差”と入力してください。
②セル選択モードでB11セルをクリック
③”=average(“と入力する
④B3〜B10をマウスで選択してB11セルに「=AVERAGE(B3:B10)」を確認
B12にB11と同様に標準偏差関数(“=STDEV(“)を入力し、同じセル範囲)B3~B10をマウスで選択
⑤B12に標準偏差が計算される
⑥標準偏差は小数点以下一桁表示に(上の4項を参照)
⑦B11, B12セルをC11〜D12にコピー(式のコピー:2項参照) (ヒント：黒十字を利用したオートフィルによる式のコピーもしくはコピー&ペースト)
⑧左列の11をクリックし、行を挿入
⑨B11に”=B2″を入力
⑩B11をB12, B13にコピー
上手くいきましたか? 関数はいろいろあります。表計算のいろはやグラフの書き方にも紹介されています。統計関連の関数は基本的な検定を参考にしてください。その他多くの解説書が出版されています。
7. 平均値から棒グラフの作成し、標準偏差を誤差範囲としてグラフに追加する
表計算ソフトの醍醐味の一つに容易なグラフ作成機能があります。ここでは一番簡単な棒グラフを作成してみましょう。
棒グラフは項目軸の説明を加えるためには、表の数値の上に項目説明(項目名)が必要です。このために平均値の上に行を追加し、そこにAクラスからCクラスをコピーしておくきます(前項の⑧で行っている)。式を使った項目のコピーはセルのコピーとペーストでもできます。
①マウスでB11からD12を選択
②挿入からグラフを選択(棒グラフ)を選択。グラフタイトルにグラフの説明を記入（「グラフのタイトル」をクリックし、マウスで全部塗り(command (cntl) + A、上から文字を書く)
③シートメニューの”グラフデザインをクリック”(すでに選択されているはずです)
④シートメニューの右隅の”グラフ要素の追加”からプルダウンメニューの”誤差範囲”を選択し、さらに”その他の誤差オプション”をクリック
⑤グラフマークをクリック
⑥誤差範囲の”ユーザー設定”の左端のラジオボタンをクリック
⑦”値の指定”をクリック
⑧正の誤差の値の入力枠の右端の小さな四角い表ボタンをクリック
⑨ABCの３つの標準偏差の値をマウスで選択
⑩負の誤差の値の入力枠の右端の小さな四角い表ボタンをクリック
⑪ABCの３つの標準偏差の値をマウスで選択
⑫OKをクリック → ABCの誤差範囲(ひげ)が標準偏差(9, 10, 5)の長さに変更されていることを確認
⑬グラフ要素の追加のプルダウンメニューから軸ラベル→第１縦軸を選択し、ラベルに”身長(cm)”と記述
⑭左の身長の170をダブルクリック、左の設定ウインドのグラフマークをクリックし、最小値をゼロにしてreturn(enter)
⑮文字のフォントを見易く修正し完成(フォントはシートメニューのホームで選択できる)
8. 条件分岐(IF, IFSなど)を利用して数値をABCに分類する
表計算にはIF関数というのがあります。英語のIFは「もしも〜なら」ですね。関数にも、もし値が”>=0.5”なら1で値が”<0.5”なら0という具合に条件を設定し、その条件にあっているなら〜, 違っているなら〜。といったように場合分け処理をすることができます。IF関数の仲間にはIFS関数という複数の条件分けを行う関数があります。またIF関数の重カッッコを利用しても複数条件の場合分けを行うことが可能です。また2つの条件を満たす場合だけ実行するような複数条件もあります。これにはANDやORが利用できます。
今回は表にある記録データを大きさでABCに分ける作業をしてみましょう。先ほどダウンロードしたシートの”記録のシート”タグをクリックしてください。上手くdownloadできない場合は下の表を新しいシートに打ち込んでください。
C1とD1の欄の数値は、AとBの境界値とBとCの境界値です。この境界値をIF関数の中で参照してABCの判定をします(例ではC1に16.8、D1に19.1を入力しています)。境界値より下の記録はそれぞれAとBになります。IFSのなかで利用される$は絶対参照を示すマークです。C$1としておくと、この式をコピーしたときに常にC1の値を参照します。
表が完成したら、境界値の値を少し変えてAからCの人数が変わることを確認してください。
①C3に以下の式を入力
=IFS(B3<C$1,”A”, B3<D$1,”B”,B3>=D$1, ”C”)
※ IFSが関数の一覧に見つからない場合は、「=IF(B3<C$1, “A”, IF(B3<D$1, “B”, “C”))」を利用してください。
②C3をクリックしコピー(command+C)し、C3からC17にペースト(command+V)
これで1から15番の人を記録でクラス分けすることができます。
式をコピーしたときにC$1がどのようになっているかを下の左の図で解説しています。コピーされたC6の計算式では、B3は行が移動してB6となっていますが、C$1は同じ行が移動せずにC1を式のなかで利用しています。
$マークを付けない場合、B3をコピーしたB6などは行や列に従って相対的に位置が変わるので相対参照、C$1のように行を変えても移動しない参照を絶対参照と呼びます。$マークのショートカットキーはMacではcommand+t(alt+t)で、WindowsではF4です。
IFS関数の説明
IF関数では次のように計算されます。
(1) B3<C$1なら”A”を表示 (C$1未満)
(2) (1)の条件に当てはまらない場合で、B3<D$1なら”B”を表示
(3) (1), (2)に当てはまらず、B3>=D$1では”C”を表示 (D$1以上)
IF関数の説明
F関数では次のように計算されます。
(1) B3<C$1なら”A”を表示 (C$1未満)
(2) (1)の条件に当てはまらない場合で、B3<D$1なら”B”を表示
(3) それ以外では”C”を表示 (D$1以上)
9. COUNTIFを使ってABCの組に入るデータ数を数える
さて次は、クラス分けしたABCがそれぞれ何人いるか数えて見ましょう。もちろん15人なら手で数える方が簡単ですね。ここではCOUNTIFという条件付きCOUNT関数を利用します。
A19に”A”、A20に”B”、A21に”C”と入力します。A19の文字と同じ文字が指定範囲(C3からC17)に何回出てくるかを数えます。
B19のセルには「=COUNTIF(C$3:C$17,A19)」を入力します。。B19をB20,B21にコピーして完成です。
ここでもC$3:C$17と絶対参照の$を用いているので、B20, B21に式をコピーしても緑の指定範囲は固定しています。
どうですか？表検査の関数が便利な点を分かっていただけましたか？
(※〜以下などの条件を数字で入力する場合は””が必要です→160以下の数字の数:countif(C$3:C$17, “<=160”)
10. データの並べ替え
それでは記録データはどのようなデータかを知るために大きい順に並べて見ましょう。記録値で並べ替えをすると最小値や最大値の記録がいくつかなどがわかります。並べ替えはexcelでは非常に簡単です。
①A1からB17をマウスで塗ってコピーする
②F1をクリックしてペースト
③F3〜G17をマウスで選択
④シートメニューの”データ”をクリック
⑤シートメニューの”並べ替え”をクリック
⑥優先されるキーを”記録”を選択
⑦OKをクリック
記録と番号が対で並べ替えられ、どの番号の記録が一番小さいかがわかります。
※G列だけ選択すると記録だけ並べ替えることになります。
※最小値はMIN、最大値はMAX関数を使って記録の中の最小値、最大値を求めることもできます。
11. データの度数分布表(ヒストグラム)を作る
ヒストグラムは分布図とも呼ばれています。どのような記録が多いのかなど記録データの全体像を把握し易くなります。本来は、このヒストグラムを見て、ABCの区分基準値を決めていきます。
まず、データの最大と最小をみてそれよりも大きい値を区分値の最終値とします。このデータでは22.1が最大ですので24が最終区分にします。23から24の区分に入る人数はゼロ人となります。同様に最小値は15.9ですので15以下の区分もゼロ人になります。
今回は区分を15から24まで1区切りで区分を書きます。FREQUENCY関数を指定するセルはJ3だけです。あとは自動的に数字が入ります。このJ列にできた数字は度数分布になります。J3は15以下の人数、J4は 15以上16未満の人数などとなります。
関数はFREQUENCYを利用します。関数内には、データ範囲(B3:B17)、区間範囲(J3:J10)が入ります。
度数分布表を作成
①J3に15、J4に16といれて、J3とJ4をマウスで選択し、J12までオートフィルする（区間の作成)
②K3〜K12までをマウスで選択(頻度欄)
③K3に “=FREQUENCY(” を入力
④B3〜B17(全データ範囲)を選択し、”,”(カンマ)を入力
⑤J3〜J12(全区間データ)を選択
⑥”shift”+”control”+”return”
⑦K3〜K12に0〜4の数字を確認
⑧J3〜J12(区間)を選択し、command+1でセルの書式設定ウインドを開く
区間データの表記を変更：
⑨表示形式→ユーザー定義を選択し、”〜”を「G/標準」の前に加える→「”〜”Ｇ/標準」となる→OKをクリック→表のJ3〜J12の数字の前に”〜”が表示を確認
度数分布表からグラフを作成
⑨ K3〜K12(頻度)をマウスで選択し、シートメニューの挿入から棒グラフを選択
⑩ グラフをマウス右クリックし、「グラフデータの選択」をクリック
⑪ 項目軸ラベル入力枠の右端の表マークをクリック
⑫ J3〜J12(区間)を選択してOKをクリック
⑬ タイトルや縦軸、横軸を入力
⑭ 棒をダブルクリックして右に現れるデータ系列の書式設定の棒グラフの系列オプションをクリック、要素の間隔をゼロに設定する
*注意: これらの指定はSpreadsheetやNumbersは異なりますので注意を要します。
チュートリアルの動画1 オートフィルまで(windows10版)
- チュートリアル動画オートフィルまでMac版
- チュートリアル動画_オートフィル
- チュートリアル動画_2
- チュートリアル動画_3