4. 納 品 書 の 作 成 ここからまたまたレベルアップします 今 回 は 商 品 をリストから 選 んで 納 品 書 を 作 成 するものです お 店 の 方 は リストからお 客 様 にご 注 文 いただくと その 番 号 を 選 びます 予 めコンピュータにその 番 号 書 名 価 格 が 登 録 され すぐに 表 示 されます 冊 数 を 入 れて 合 計 金 額 を 出 します また 日 付 やセルの 表 示 形 式 についても 学 習 を 進 めていきます 最 初 にシートを 確 認 します 今 回 は3 枚 のシートで 問 題 が 構 成 されています 平 成 23 年 度 学 校 開 放 パソコン 講 座 27
1. 選 択 したシートに 名 前 をつけ る エクセルでは 複 数 のセルを 選 ぶ と 選 択 された 範 囲 が 紫 色 に 変 わ ります 選 んだ 範 囲 を 何 度 か 使 う 場 合 は そこに 名 前 を 与 えておく と 関 数 を 組 む 上 でも 見 やすくな ります まず 商 品 情 報 のシートをクリ ックします 最 初 にA3からC15(R70 11のところから1344)まで を 選 択 します 次 に 列 番 号 の A の 上 に A3 と 表 示 されているところがありますので そこに 理 科 と 入 力 し 定 義 してください 同 様 にA18からC29までを 社 会 A32からC36までを 国 語 と 定 義 してください 次 にA3からA15まで 縦 一 列 に 選 択 し 理 科 リスト と 定 義 します 同 様 にA18からA29までを 社 会 リスト A32からA36までを 国 語 リスト と 定 義 します ここまでできたら 今 まで 入 力 してきた 窓 の をクリックします そうすると 定 義 して きた ものが 表 示 されます クリックするとその 範 囲 が 示 されます このことがどう 生 きてくるのかは 次 のステップです 平 成 23 年 度 学 校 開 放 パソコン 講 座 28
2.リストの 使 い 方 メインシートをクリックしてください これからやるステップでは どの 教 科 の 本 かを 指 定 し その 教 科 の 品 番 を 入 力 します すると 書 名 と 価 格 が 表 示 されるようにします 冊 数 は 手 入 力 です そうすると 合 計 がでます ここでは 教 科 と 品 番 について リストから 選 ぶようにし 書 名 価 格 は 自 動 的 に 表 示 するようにします (1)3つの 教 科 をリストから 選 ぶ B13を 選 択 します 次 にメニューから デ ータ を 選 び データ の 入 力 規 則 を 選 びま す 設 定 から 入 力 値 の 種 類 から リスト を 選 びます 元 の 値 のところに 理 科, 社 会, 国 語 と 入 力 します 教 科 と 教 科 の 間 は 半 角 のカンマ, です OKをクリ ックします B13をクリックすると が 出 て クリックすると 理 科 社 会 国 語 が 出 て 選 択 できるようになっています そこに 算 数 を 手 入 力 するとエラーがでます 平 成 23 年 度 学 校 開 放 パソコン 講 座 29
(2) 品 番 を 入 力 する 品 番 もリストから 探 せ るようにします ただ し 品 番 は 理 科 は 理 科 のみの 品 番 が 出 るよ うにします 前 半 の 手 順 は 先 ほど と 同 じです C13を 選 択 します 次 にメニューから デ ータ を 選 び データ の 入 力 規 則 を 選 びま す 設 定 から 入 力 値 の 種 類 から リスト を 選 びます ここからは 先 ほどと 少 し 違 います 元 の 値 のところに =INDIRECT(B13& リスト ) と 入 力 します B13は 理 科 が 入 っています &でつないで つまり B13& リスト は 理 科 リスト になります このままでは 単 なる 文 字 列 なので 定 義 された 文 字 である ことを 示 すのに INDIRECT 関 数 を 使 います これにより 元 の 値 は 先 ほど 商 品 情 報 で 定 義 した 理 科 リストとなり その 番 号 が 選 択 範 囲 として 表 示 されることになります OKをクリックして C13の をクリックし R のリストが 出 ることを 確 認 します また B13を 理 科 から 変 えるとその 教 科 の 品 番 が 出 ることも 確 かめてください それでは 一 度 1 番 目 の 教 科 と 品 番 を 消 してください 二 つのセルを 選 んで 右 クリック で 数 式 と 値 のクリア です 平 成 23 年 度 学 校 開 放 パソコン 講 座 30
(3) 入 力 規 則 をコピーす る もう 一 度 B13とC1 3をクリックし C13の 右 下 の 黒 プラス + を 右 クリックし C32まで 下 がり 書 式 なしコピー をクリックします これで20 冊 まで 品 番 がリストから 選 べること になります 3.VLOOKUP 関 数 ( 検 索 )の 使 い 方 ここでは 品 番 から 書 名 と 価 格 を 検 索 し 表 示 させることを 学 びます 1 冊 目 のところにデータを 入 れてもらいます 理 科 でR7011を 入 れておいてください 商 品 情 報 を 見 ると ドキドキ チョウのくらし 3920 円 となっております vlookup 関 数 の 使 い 方 は 右 の 通 りです 文 字 列 検 索 の 場 合 は false をつけます vlookup( 検 索 値, 範 囲, 列 番 号,false) vlookup(c13,indirect(b13),2,false) 今 回 の 場 合 は 検 索 値 は R7011 で 範 囲 が 理 科 なので B13から 理 科 を 取 り 出 し それを indirect 関 数 で 理 科 の 選 択 範 囲 として 認 識 させ でその 中 から R7011 を 探 し 列 番 号 がその 表 の2 列 目 ( 書 名 )を 表 示 せよという 関 数 になります D13に 上 のような 式 を 入 れ エンターを 押 すと ドキドキ チョウのくらし とでま したでしょうか 平 成 23 年 度 学 校 開 放 パソコン 講 座 31
今 度 は 価 格 です E13に vlookup(c13,indirect(b13),3,false) と 入 力 し エンターを 押 します 3920 と 表 示 されましたでしょうか ここまで 来 れば このステップは 山 越 え 完 了 です さて あとは 仕 上 げに 入 っていきまし ょう 4. 合 計 を 求 める ドキドキ チョウのくらしを2 冊 買 う こととし 冊 数 に2を 入 れます そして 金 額 のところに =E13* F13 と 入 れてエンターです エク セルでは かけ 算 は の 代 わりに * を 使 います 同 様 に の 代 わりに / を 使 いますので 覚 えて おきましょう 7840になります 平 成 23 年 度 学 校 開 放 パソコン 講 座 32
全 部 の 合 計 も 求 めてお きましょう G33をクリ ックします メニューから 数 式 オ ートSUM をクリックし ます =SUM(G13:G32) と 表 示 されていることを 確 認 し エンターを 押 します 7840 です 5. 桁 区 切 り 処 理 価 格 と 冊 数 金 額 合 計 につい て 桁 区 切 りの 書 式 を 設 定 します E13からG33までを 選 択 します 右 クリックで セルの 書 式 設 定 表 示 形 式 数 値 と 進 み 桁 区 切 り にチェックを 入 れ OKをクリックします まだ 2 行 目 以 降 は 式 も 数 値 も 入 っていませんが 入 った 時 は 桁 区 切 りで 表 示 されることになり ます 平 成 23 年 度 学 校 開 放 パソコン 講 座 33
6. 空 欄 処 理 先 ほどの 出 納 簿 でもやりましたが 未 入 力 のところに 何 も 表 示 させないようにします 今 回 は 1 行 目 に 予 めその 仕 掛 けをつけておき その 後 で 一 括 して 数 式 のコピーを 行 い ます 今 回 は 品 番 が 未 入 力 だと 書 名 価 格 金 額 が 表 示 されないようにします D13を 次 のように 直 します 旧 =VLOOKUP(C13, INDIRECT(B13),2,FALSE) 新 =IF(C13="","",VLOOKUP(C13, INDIRECT(B13),2,FALSE)) E13は 旧 =VLOOKUP(C13, INDIRECT(B13),3,FALSE) 新 =IF(C13="","",VLOOKUP(C13, INDIRECT(B13),3,FALSE)) G13は 旧 =E13*F13 新 =IF(C13="","",E13*F13) 7. 式 の 一 括 コピー それでは B13の 理 科 と C13のR7011 そして 冊 数 の2を 消 してください 書 名 価 格 金 額 も 消 え 真 っ 新 な 表 になります B13からG13までを 選 択 します G13の 右 下 の + を 右 クリックし G20ま でドラッグし 離 します サブメニューから 書 式 なしコピー をクリック 以 上 でそれ ぞれの 列 のそれぞれの 書 式 が 以 下 の19の 欄 にコピーされました 平 成 23 年 度 学 校 開 放 パソコン 講 座 34
8. 日 付 納 品 日 を 記 入 します そ の 日 の 自 動 的 に 入 れる 関 数 があります セルが 結 合 されていますが E1 に =today() と 入 力 し エンターをク リックします 初 期 値 で は 2011/9/20 などと 表 示 されます 和 暦 で 表 示 するようにし ます E1を 選 択 右 クリックで セルの 書 式 設 定 表 示 形 式 日 付 カレンダーの 種 類 を 和 暦 そして 平 成 をクリックし OKをクリック 9. 納 品 先 をリストから 選 ぶ (1) 顧 客 情 報 の 定 義 づけ 先 ほど 大 きな 山 を 越 えました ので 今 度 は 比 較 的 楽 かと 思 いま す 顧 客 情 報 のシー トをクリックし ます 最 初 に B2からE14までをクリックし 顧 客 名 簿 と 定 義 します 次 にB2からB14( 全 部 の 学 校 の 名 前 )を 選 択 し 顧 客 リスト と 定 義 します 平 成 23 年 度 学 校 開 放 パソコン 講 座 35
(2) 納 品 先 をリストから 選 ぶ C4をクリックし デー タ データの 入 力 規 則 入 力 地 の 種 類 から リ スト を 選 択 元 の 値 に =indirect(" 顧 客 リスト") と 入 力 OKをクリック (3) 納 品 先 の 住 所 を 表 示 C5を 選 択 しま す Vlookup 関 数 で 納 品 先 の 学 校 名 を 後 ろのシー トから 探 し 住 所 (3 番 目 の 列 )を 表 示 する 関 数 を 入 れます =VLOOKUP(C4, 顧 客 名 簿,3,FALSE) となります 納 入 先 が 入 っていない 時 のエラーを 回 避 するために =IF(C3="","",VLOOKUP(C4, 顧 客 名 簿,3,FALSE)) としておきましょう 平 成 23 年 度 学 校 開 放 パソコン 講 座 36
10. 納 品 金 額 を 入 れて 書 式 設 定 をかける 合 計 金 額 をC7に 入 れます こ れは G33と 同 じ 金 額 になり ます C7を 選 択 して =G33 と 入 力 し エンターをクリックしま す 今 回 は ここに 円 をつけさ せる 書 式 について 学 びます C7を 選 択 右 クリックで セ ルの 書 式 設 定 表 示 形 式 ユーザー 定 義 で 種 類 のと ころに ##,###" 円 " と 入 力 し OKをクリックしま す 11.シートの 保 護 をかける ユーザーが 入 力 するのは 色 のついた 部 分 で 教 科 品 番 冊 数 納 入 先 です これ 以 外 は 全 てコンピュータ が 自 動 的 に 計 算 してくれる 場 所 です ユーザーが 間 違 えて 式 を 消 したり いじってしまうと と んでもない 数 値 がはじき 出 さ れ コンピュータを 信 じるあま り それに 気 づかないこともあ ります そのようなことを 防 止 する ために 入 力 しないセルは 操 作 せきないようにしておきま 平 成 23 年 度 学 校 開 放 パソコン 講 座 37
す セルの 保 護 です 選 択 するところが3カ 所 に 分 かれていますが ちょっとし たコツで 一 度 で 選 択 できます まず B13からC20までを クリック 次 にキーボードのC TRL(コントロール)キーを 押 しながら F13から F32 をクリック そのままキーをお しながら 最 後 C4をクリック します コントロールキーを 離 し 今 度 は 右 クリックをして セルの 書 式 設 定 保 護 ロックのチェックマークを 外 す OKです 次 にメニューの 校 閲 から シートの 保 護 を 選 択 します サブメニューが 出 てきますので 上 から2つにチェックがついていることを 確 認 して OKです 試 しに 色 つき 以 外 のところで 入 力 しようとしても はねられることになります 完 成 です 保 存 してください 平 成 23 年 度 学 校 開 放 パソコン 講 座 38