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