エクセルⅡ（中級）
福岡市私立幼稚園連盟 Microsoft Excel 2010
Ver,1.0
1
関数とは
関 数 と は 、 結 果 を 得 る た め に 「 処 理 を 行 う 仕 組 み 」 で す 。
Excel2010 に は 、 あ ら か じ め 関 数 が 数 式 と し て 組 み 込 ま れ て い ま す 。
た と え ば 、 「 SUM 関 数 」 は 、 「 指 定 し た 値 を す べ て 合 計 す る 」 仕 組 み で す 。 長 い 計 算 式 や 複 雑 な 計 算 式 を 作 成 せ ず に 、 簡 単 に 結 果 を 求 め る こ と が で き ま す 。
例 合 計 =A1+A2+A3+A4+A5+A6+A7+A8+A9 ⇒ =SUM(A1:A9) 平 均 =(A1+A2+A3+A4+A5+A6+A7+A8+A9)/9 ⇒ =AVERAGE(A1:A9)
関 数 を 使 っ た 数 式 の 作 成 手 順
1．結 果 を 表 示 す る セ ル を 選 択 し 、 [数 式 ] タ ブ の [関 数 ラ イ ブ ラ リ ] の コ マ ン ド ボ タ ン か ら 関 数 を 指 定 し ま す 。
・ [関 数 の 挿 入 ] ボ タ ン か ら
・ [合 計 ] ボ タ ン か ら ▼ を ク リ ッ ク す る と 5 種 類 の 関 数 一 覧 か ら 選 択 で き る
・ セ ル に 関 数 を 直 接 入 力 す る な ど
2．引 数 を 指 定 し ま す 。
SUM(A1:A9)
↑ ↑
引 数 と は 、関 数 名 （ 引 数 ）
計 算 に 必 要 な 情 報 で 数 値 、 セ ル 番 地 、 文 字 列 な ど2
便 利 な 関 数
VLOOKUP・ HLOOKUP… 表 の 縦 と 横 の 位 置 を 指 定 し て 特 定 の 値 を 検 索 す る こ と が で き る COUNTIF… 特 定 の 条 件 の 値 の 個 数 を 集 計
COUNTA… 数 字 だ け で な く 文 字 も カ ウ ン ト で き る COUNTBLANK… 空 白 を 数 え る
SUMIF… 特 定 の 条 件 の 値 を 合 計
ROUND・ ROUNDUP・ ROUNDDOWN・ INT… 四 捨 五 入 や 切 り 上 げ ・ 切 り 捨 て の 指 定 If… 条 件 を 設 定 し 複 数 の 計 算 結 果 を 求 め る こ と が で き る
SUBSTITUTE・ TRIM… セ ル 内 の ス ペ ー ス を 削 除 で き る ASC・ JIS… 全 角 を 半 角 に し た り 、 半 角 を 全 角 に で き る
今回使用する関数：
VLOOKUP、 COUNTIF、 SUMIF、 ROUND、 If、 SUBSTITUTE
初級編で使用した関数：
Sum、Date、Month、Text、Phonetic、
3
1 記念グッズ注文管理表作成
1- 1 ．完成例
● [注文管理表] シートを作成しましょう。
[注文管理表]シートでは、番号のみ入力してあります。
● それ以外の項目は、下記のような計算式を作成して表を完成させます。
保護者名：番号をもとに［名簿］シートから保護者名を表示させます。
注文回数：［注文状況］シートから注文した回数をカウントします。
注文個数：［注文状況］シートから注文した個数を合計します。
消費税：1円未満を四捨五入します。
合計：注文金額に消費税を足した結果を表示します。
参照シート
4
1- 2．計算式の作成（VLOOKUP 関数）
① ［ファイル］ボタンから［開く］を選択
② ［エクセル関数編_練習データ］ファイルを選択し［開く］ボタンをクリック 各シートの内容を確認します。
③ [注文管理表] シートの[B3]をクリックします。
※計算式を作成する場合は、半角英数字を使います。日本語入力をオフにしておくと入力ミスによる エラーを防ぐことができます。
④ [数式] タブの [関数ライブラリ] のコマンドボタンから[関数の挿入]ボタンをクリックします。
5
⑤ [関数の挿入]ダイアログボックスで[関数の分類]の ▼ボタンから「すべて表示」をクリックします。
⑥ 関数名の一覧から「VLOOKUP」関数を探します。
関数名の一覧の任意の関数をクリックした状態で、キーボードから「VL」と入力するとジャンプします。
⑦ [関数の引数]ダイアログボックスが表示されます。
作 成 す る 関 数 に よ っ て 、 引 数 の 数 と 種 類 が 変 化 し ま す 。
VLOOKUP 関 数 の 場 合 は 、 引 数 の 数 が ４ つ あ り 省 略 可 能 な も の も あ り ま す 。
6
⑧ [関数の引数]ダイアログボックスで必要な引数を入力もしくは該当するセルをクリックします。
検索値：範囲の先頭列で検索する値を指定・・・・ここではA3をクリックします。
範囲：目的のデータ([名簿]シート)が含まれる文字列、数値などの範囲または範囲名を指定 ・・・・ここでは、［名簿］シートをクリックしA2からE11のセルを指定します。
※下のセルにも計算式をコピーするので、範囲は絶対参照（範囲選択後、F4キー）にしておきます。
列番号：範囲の先頭列を1として数えた列番号を指定します。・・・・ここでは2番目の列
検索方法：完全に一致する値「0」なのか近似値を含めるかを指定、省略すると近似値を含めます。
・・・・ここでは完全一致としますので、「0」か「false」と入力します。
福岡純一郎さんの名前が表示されました。
確認：違う番号を入力して保護者名が変化するのを 確認してみましょう。
変化を確認できたら「1001」に戻しておきます。
⑨ 計算式をコピーして完成例のようになっている ことを確認します。
7
1- 3．計算式の作成（COUNTIF 関数）
① [注文管理表] シートの[C3]をクリックします。
② [数式] タブの [関数ライブラリ] のコマンドボタンから[関数の挿入]ボタンをクリックします。
③ 関数の分類の▼ボタンをクリックし「統計」を選択し、関数名の一覧から「COUNTIF」関数を探します。
関数の検索：キーワードを入力して探すことができます（例：「数を数える」など）
関数の分類：よく使う関数は、関数の分類を「最近使用した関数」で表示されます。
「文字列操作」や「日付/時刻」など、分類を絞って探すこともできます。
この関数のヘルプ：クリックしてその関数の使用例などを確認することができます。
8
④ [注文状況]シートを開き範囲を指定します。
[関数の引数]ダイアログボックスで必要な引数を入力もしくは該当するセルをクリックします。
範囲：セルの個数を求める範囲を指定・・・・ここでは[注文状況] シートのB2からC19を指定します。
※下のセルにも計算式をコピーするので、範囲は絶対参照（範囲選択後、F4キー）にしておきます。
検索条件：計算の対象となるセルを定義する条件を数値、式または文字列で指定 ・・・・ここでは、[注文管理表] シートのA3のセルを指定します。
1001の福岡純一郎さんの注文回数は「2」と表示されました。
⑤ 計算式をコピーして下記のようになっていることを確認します。
9
1- 4．計算式の作成（SUMIF 関数）
① [注文管理表] シートの[D3]をクリックします。
② [数式] タブの [関数ライブラリ] のコマンドボタンから[関数の挿入]ボタンをクリックします。
③ 関数名の一覧から「SUMIF」関数を探します。
複数の条件を指定したい場合は、下記の関数を使います。
COUNTIFS(条件範囲 1, 検索条件 1, [条件範囲 2, 検索条件 2]…)
SUMIFS(合計範囲, 条件_範囲 1, 条件 1, [条件範囲 2, 条件 2], ...)
10
④ [関数の引数]ダイアログボックスで必要な引数を入力もしくは該当するセルをクリックします。
範囲：対象となる範囲を指定・・・・ここでは[注文状況] シートのB3からC19を指定します。
※下のセルにも計算式をコピーするので、範囲は絶対参照（範囲選択後、F4キー）にしておきます。
検索条件：計算の対象となるセルを定義する条件を数値、式または文字列で指定 ・・・・ここでは[注文管理表] シートのA3のセルを指定します。
合計範囲：計算の対象となるセル範囲を指定
・・・・ここでは［注文状況］シートのC3からC19を指定します。
※下のセルにも計算式をコピーするので、範囲は絶対参照（範囲選択後、F4キー）にしておきます。
1001の福岡 純一郎さんの注文個数は「13」と表示されました。
⑤ 計算式をコピーします。
⑥ E列の注文金額のセルに、
D列に単価の「555」円を掛け算する 計算式を入力し、
下のセルにもコピーします。
※単価：555円
11
1- 5．計算式の作成（ROUND 関数）
① [注文管理表] シートの[F3]をクリックします。
② [数式] タブの [関数ライブラリ] のコマンドボタンから[関数の挿入]ボタンをクリックします。
③ 関数名の一覧から「ROUND」関数を探します。
④ [関数の引数]ダイアログボックスで必要な引数を入力もしくは該当するセルをクリックします。
数値：四捨五入の対象となる範囲や数値を指定・・ここではE3のセルをクリックして＊0.08を入力して、
掛ける式を指定します。
桁数：四捨五入する桁数を指定 ・・・・ここでは円単位で四捨五入するので「0」を指定します。
12
1001の福岡純一郎さんの消費税は「577」と表示されました。
⑤ 計算式をコピーして完成例のようになっていることを確認します。
⑥ G列（合計）には、E列（金額）とF列（消費税）を足し算する計算式を入力し、下のセルにも コピーします。
【完成】
桁数に正の数を指定すると、数値の小数点以下について、指定した桁数分が四捨五入されます。
桁数に 0 を指定すると、数値は最も近い整数として四捨五入されます。
桁数に負の数を指定すると、数値の小数点の左側 (整数部分) が四捨五入されます。
常に切り上げるには、ROUNDUP 関数を使用します。
常に切り下げるには、ROUNDDOWN 関数を使用します。
13
2 バザー参加リストの作成
2- 1．完成例
● ［バザー参加リスト］を作成しましょう。
[バザー参加リスト]シートでは、番号のみ入力してあります。
● それ以外の項目は、下記のような計算式を作成して表を完成させます。
保護者名：［注文管理表］シートから[VLOOKUP関数]を使って保護者名が表示されるよう設定します。
参加人数：完成例を参考に数値を入力します。
14
2- 2．入力規則の設定
データの入力規則を使用すると、セルに入力するデータまたは値の種類を制御できるので、入力ミスを防いだり 入力を助けたりすることができます。
・データ入力を一定の範囲内の日付に制限する。
・リストを使用して選択肢を制限する。（例：男・女 ○・× 東京・大阪・福岡など）
・整数のみを入力できるように制限する。
・日本語入力のオン／オフ
ここでは、「お弁当」と「ドリンク」の○×を条件に判断し、金額を自動で計算するための準備をします。
① ［バザー参加リスト］シートの[D4]から[F12]を範囲選択し、
[データ] タブの [データツール] のコマンドボタンから[データの入力規則]ボタンをクリックします。
※[C2]参加人数はあらかじめ入力しておきましょう。
15
② [データの入力規則]ダイアログ[設定]タブの[入力値の種類] ▼ボタンから「リスト」をクリックします。
③ [元の値]に「○,×」と入力します。（リストにしたい要素を「,」半角カンマで区切ります）
セルをクリックすると▼ボタンが表示され、クリックすると○と×が選択できるようになりました。
④ 完成例を参考に○,×を選択します。 完成例
16 参考）
[データの入力規則]ダイアログの [エラーメッセージ]タブ
[データの入力規則]ダイアログの [日本語入力]タブ
英数半角文字だけを入力するセルであった場合は、入力規則で日本語入力をオフに設定しておくことに よって、そのセルをクリックしたときに自動で日本語入力がオフになります。
種 類 用 途
停 止 無 効 な デ ー タ は 入 力 で き な い 。
注 意 無 効 で あ る こ と を 警 告 す る が 、 入 力 は 許 可
情 報 無 効 で あ る こ と を 通 知 す る が 、 入 力 は 許 可
17
2- 3．計算式の作成（IF 関数）
① ［バザー参加リスト］シートの[G4]をクリックします。
② [数式] タブの [関数ライブラリ] コマンドボタンから[関数の挿入]ボタンをクリックします。
③ 関数名の一覧から「IF」関数を探します。
18
① お弁当の合計金額を計算していきます。
[関数の引数]ダイアログボックスで必要な引数を入力もしくは該当するセルをクリックします。
論理式：結果をわけたい条件となる数式または値を指定します。
・・・・ここでは[バザー参加リスト] シートのお弁当のセル[E4]が○の場合を指定します。
真の場合：論理式の結果がその条件に合致する場合に返される値を指定 ・・・・ここではお弁当代（500円）のセル[G1]に
「○」の場合、参加人数[C4]を掛け算します。
※下のセルにも計算式をコピーするので、G1は絶対参照（セル選択後、F4キー）にしておきます。
偽の場合：論理式の結果がその条件に合致しない場合に返される値を指定 ・・・・ここでは「○」以外の場合、0とします。
「福岡 純一郎」さんは参加するがお弁当とドリンクが「×」なので、「0」と表示されます。
② 計算式をコピーして完成例のようになっていることを確認します。
「熊本 京子」さんは2人参加で、お弁当が「○」なので、「1000」と表示されます。
※ここでは、まだお弁当のみの条件しか入っていないので、次にドリンクの条件も追加します。
完成例
19
⑦ お弁当とドリンクの合計金額を計算していきます。
[G4]をクリックし、新たに数式を追加していきます。
数式バーに表示されている計算式の最後をクリックし、「+」を入力します。
⑧ 数式バーの左に表示されている「IF」をクリックします。
他の関数が表示されていた場合、▼ボタンをクリックし、他の関数を選択することができます。
③ [関数の引数]ダイアログボックスで必要な引数を入力もしくは該当するセルをクリックします。
論理式：結果をわけたい条件となる数式または値を指定します。
・・・・ここでは[バザー参加リスト] シートのドリンクのセル[F4]が○の場合を指定します。
真の場合：論理式の結果がその条件に合致する場合に返される値を指定
・・・・ここではドリンク代（100円）のセル[G2]に「○」の場合は参加人数[C4]を掛け算します。
※下のセルにも計算式をコピーするので、G2は絶対参照（セル選択後、F4キー）にしておきます。
偽の場合：論理式の結果がその条件に合致しない場合に返される値を指定 ・・・・ここでは「○」以外の場合、0とします。
「福岡 純一郎」さんは参加するがお弁当とドリンクが「×」なので、「0」と表示されます。
20
④ 計算式をコピーして完成例のようになっていることを確認します。
「熊本 京子」さんは2人参加で、お弁当とドリンクが「○」なので、「1200」と表示されます。
⑤ [G4] から[G12]までのセルを範囲選択し、 [ホーム] タブの [数値] グループのコマンドボタンから
[通貨表示形式]ボタンをクリックし、¥マークとカンマ区切りの書式を設定します。
完成例
関数は、長い計算式や複雑な計算式を短く簡単な計算式にすることができます。
該当する関数がない場合は、自分で関数を作成することもできます。
21
3- 1．SUBSTITUTE 関数（名簿シートにて編集）
SUBSTITUTE関数は、文字列を検索して置き換えを行うことができます。
● 保護者氏名1のB列からスペースを削除しましょう。
① B列とC列の間に1列追加し、保護者氏名2と入力します。
② [名簿]シートの[C4]をクリックします。
③ [数式]タブの[関数ライブラリ]コマンドボタンから[関数の挿入]ボタンをクリックします。
④ [関数の挿入]ダイアログボックスで[関数の分類]の▼ボタンから[文字列操作]をクリックし、
関数名の一覧から[SUBSTITUTE]関数を探します。
22
⑤ [関数の引数]ダイアログボックスで必要な引数を入力もしくは該当するセルをクリックします。
文字列：置き換える文字を含む文字列、またはセルを指定します。
・・・・ここでは[注文管理表]シートのB3 セルを指定します。
検索文字列：置き換え前の文字列を指定
・・・・ここでは空白を置き換えたいので「” “」（”スペース”）と入力します。
※スペースに関しては置換元のデータが全角か半角かを確認 置換文字列：置き換え後の文字列を指定
・・・・ここでは空白を削除したいので「””」（長さ0の文字列）と入力します。
置換対象：文字列に含まれるどの検索文字列を置き換えるかを指定（省略するとすべて対象となる）
・・・・ここではすべての空白を削除したいので省略します。
1001の「福岡 純一郎」さんの空白が削除され 「福岡純一郎」さんと表示されました。
⑥ 計算式をコピーして完成例のようになっている ことを確認します。
完成例
文字列操作関数には、他にも下記のような関数があります。
LEFT関数：文字列の先頭から指定された数の文字を表示します。
LEN関数：文字列に含まれる文字数を返します。
UPPER関数：文字列の含まれる英字をすべて大文字に変換します。
