Section1_入力用テンプレートの作成

Transcription

1 入力用テンプレートの作成 1 Excel には 効率よく かつ正確にデータを入力するための機能が用意されています このセクションでは ユーザー設定リストや入力規則 関数を利用した入力用テンプレートの作成やワークシート操作について学習します STEP 1 ユーザー設定リスト 支店名や商品名など 頻繁に利用するユーザー独自の連続データがある場合には ユーザー設定リスト に登録しておけば オートフィル機能で入力することができ便利です ユーザー設定リストの作成ユーザー設定リストは [Excel のオプション ] ダイアログボックスから登録できます 1. [ ファイル ] タブの [ オプション ] をクリックし [Excel のオプション ] ダイアログボックスを表示します 2. [ 詳細設定 ] を選択し ユーザー設定リストの編集ボタンをクリックして [ ユーザー設定リスト ] ダイアログボックスを表示します 3. [ ユーザー設定リスト ] の一覧から [ 新しいリスト ] をクリックし [ リストの項目 ] ボックスに連続データとして登録したいデータを入力します 項目と項目の間で Enter キーを押し 改行しながらリストを入力します 4. 追加ボタンをクリックし [ ユーザー設定リスト ] の一覧に連続データが追加されたことを確認して OK ボタンをクリックします 5. [Excel のオプション ] ダイアログボックスに戻り OK ボタンをクリックします リストに登録したいデータがあらかじめセル範囲に入力されている場合は [ リストの取り込み元 範囲 ] をクリックし セル範囲をドラッグで指定してインポートすることもできます 例 : ユーザー設定リストの修正と削除 [ ユーザー設定リスト ] ダイアログボックスでは 登録した連続データの修正と削除が行えます [ ユーザー設定リスト ] の一覧 で選択したデータを 削除す ることができます [ ユーザー設定リスト ] の一覧で選択したデータを [ リス トの項目 ] ボックスで直接編集することができます 2

2 STEP 2 入力規則 [ データ ] タブの [ データの入力規則 ] ボタンを使用して セルに入力できるデータを制限することができます これによりデータの入力ミスを防ぎ 正確かつ効率よく入力することができます [ データの入力規則 ] ダイアログボックス入力規則を設定するには [ データの入力規則 ] ダイアログボックスの各タブで設定を行います [ 設定 ] タブ入力値の種類を指定し 入力できるデータを整数 小数点数 リスト 日付 時刻 文字列などに制限することができます [ 入力値の種類 ] を [ リスト ] にした場合 入力規則を設定したセルを選択すると右側に ボタンが表示され クリックすると表示されるリストからデータを選択することができます リストに表示したいデータは [ 元の値 ] ボックスに直接入力するか あらかじめ同じワークシートに用意し そのセル参照を指定します ワークシートに入力したデータのセル範囲をドラッグして指定することができます [ 元の値 ] ボックスに 直接データを入力する場合は 各項目を半角のカンマ (, ) で区切ります 例 : 入力値の種類により 下に表示される ボックスは変化します [ 入力時メッセージ ] タブ入力時に操作を促すメッセージを表示するには [ 入力時メッセージ ] タブで [ タイトル ] ボックスと [ メッセージ ] ボックスに文字を入力します [ セルの選択時にメッセージを表示する ] チェックボックスがオンになっていることを確認します セルを選択すると表示されます 3

3 [ エラーメッセージ ] タブ無効なデータが入力されたときにエラーメッセージを表示するには [ エラーメッセージ ] タブで [ スタイル ] を選択し [ タイトル ] ボックスと [ エラーメッセージ ] ボックスに文字を入力します [ タイトル ] ボックスと [ エラーメッセージ ] ボックスに何も入力しない場合は スタイルごとの既定のメッセージが表示されます [ 無効なデータが入力されたらエラーメッセージを表示する ] チェックボックスがオンになっていることを確認します エラーメッセージ 既定のエラーメッセージ 停止 : 規則に合わない無効なデータは入力できません 注意 : エラーメッセージのはいボタンをクリックすると無効なデータでも入力できます 情報 : エラーメッセージの OK ボタンをクリックすると無効なデータでも入力できます [ 日本語入力 ] タブセルをアクティブにしたときの日本語入力システムの入力モードを設定します 必要に応じて入力モードを変更することができます 設定した入力モードに切り替えるだけで 入力できる文字種を制限するものではありません 入力規則の削除入力規則を削除するには 対象となるセル範囲を選択し [ データの入力規則 ] ダイアログボックスを表示して すべてクリアボタンをクリックします 選択したセル範囲のすべての入力規則が削除されます STEP 3 関数による入力サポート 関数を利用して マスターデータから情報を参照したり 数式によるエラー値を表示させないようにすることができます VLOOKUP 関数 VLOOKUP 関数 ( 検索 / 行列関数 ) は 範囲の左端列の値を検索し 一致した値がある行と指定した列とが交差する位置のセルの値を返す関数です 4

4 = VLOOKUP ( 検索値, 範囲, 列番号, 検索方法 ) 関数名引数検索値 : 範囲の左端列で検索する値を指定します 検索値には値 セル参照 または文字を指定します 範囲 : 目的のデータが含まれる表のセル範囲を絶対参照で指定します 列番号 : 目的のデータが入力されている列が 範囲の左端から何列目にあるのかを番号で指定します 範囲の左端の列から と数えます 検索方法 :FALSE または TRUE を指定します A) FALSE または 0( ゼロ ) を指定した場合検索値と完全に一致する値だけを検索し 見つからない場合はエラー値 #N/A が表示されます 一致する値が複数ある場合は 最初に検索した値を使用します B) TRUE または入力を省略した場合検索値と一致する値がない場合は 近似値 ( 検索値未満で最も大きい値 ) が検索値として使用されます 範囲の左端列のデータを昇順で並べ替えておく必要があります 使用例 : 商品 ID の値に対応する商品名を 商品一覧 シートを参照して表示する 納品書 シート 商品一覧 シート 範囲 : 商品一覧!$A$4:$C$14 検索値 :B13 =VLOOKUP(B13, 商品一覧!$A$4:$C$14,3,FALSE) 列番号 :1 列番号 :2 列番号 :3 マスターデータの構成 VLOOKUP 関数は 参照先のリストの左端列から値を検索し それに対応するデータを結果として返します そのため 参照先のリストの左端列には検索に使用するための一意の値 ( 重複しない値 ) が必要となります エラー値 #N/A Excel では 関数や数式を実行するのに必要なデータが未入力の場合に エラー値 #N/A が表示されます VLOOKUP 関数では 検索値に指定したセルが未入力の時 または範囲に一致するデータが存在しない時にエラー値 #N/A が表示されます 検索値 (B 列 ) が入力されると 商品名 (C 列 ) が表示されます セル C13 の数式 =VLOOKUP(B13, 商品一覧!$A$4:$C$14,2,FALSE) をコピー 5

5 関数のネスト関数の引数に関数を使用することを ネスト といいます IF 関数の中に IF 関数をネストして条件により分岐させる処理を 3 つ以上に分けたり IF 関数や IFERROR 関数と他の関数を組み合わせてエラー表示を回避するなど 1 つの関数ではできない複雑な処理を行うことができます IF 関数のネストの例セル A2 の値が 80 以上なら 60 以上 80 未満なら 60 未満なら と表示する 1. 計算式を入力するセルを選択します 2. [ 数式 ] タブの [ 論理 ] ボタンから [IF] を選択し [ 関数の引数 ] ダイアログボックスを表示します 3. [ 論理式 ] ボックスに A2>=80 [ 真の場合 ] ボックスに と入力します 関数ボックス 4. [ 偽の場合 ] ボックスにカーソルを移動し [ 関数ボックス ] の から [IF] を選択し 2 つ目の IF 関数の [ 関数の引数 ] ダイアログボックスを表示します 5. [ 論理式 ] ボックスに A2>=60 [ 真の場合 ] ボックスに [ 偽の場合 ] ボックスに と入力します 6. 数式バーで数式を確認し OK ボタンをクリックします 数式バーには 編集中の関数が太字で表示されます 選択されていない関数の [ 関数の引数 ] ダイアログボックスを表示したい場合は 編集したい関数の関数名や引数部分をクリックします 未入力時のエラー表示を回避する (IF 関数 ) VLOOKUP 関数などで数式が空白セルを参照する可能性がある場合に 表示されるエラー値を IF 関数を使用して表示され ないようにすることができます 数式例 = IF (B13="","",VLOOKUP(B13, 商品一覧!$A$4:$C$14,2,FALSE)) 意味 セル B13 が空白の場合は空白のままにし 空白でなければ VLOOKUP 関数の結果を表示 します 納品書 シート 正しい 商品 ID が入力されると該当する 商品名 が表示されます 商品一覧 シート 商品 ID が未入力の場合は 何も表示されません 入力ミスをした時のみエラー値 #N/A が表示されます ( 商品一覧に PS003 という商品 ID はありません ) 6