Comments
Description
Transcript
表 計 算 練 習 ① - CKパソコンスクール
CK パソコンスクール Ver2.00 表 ※ 計 算 練 習 ① データ入力と計算式の入力を練習しましょう。 １）データを入力しましょう。 ２）セル【Ｃ６】に、商品計（Ｔシャツ＋ハンカチ＋くつ下）を求めましょう｡ ３）セル【Ｃ７】に、消費税「商品計×０．０５」求めましょう。 ４）セル【Ｃ８】に、合計「商品計＋消費税」を求めましょう｡ ５）セル【Ｃ１０】に、販売価格「合計－値引」を求めましょう｡ ６）Ｔシャツの金額を 3000 に変更しましょう｡ ７）マイドキュメントフォルダに「Excel 練習」というフォルダを作成し、「Excel 練習」 フォルダの中に「表計算練習 1.xls」と名前を付け保存します。 ※ Hint 計算式は＝（半角）で開始し表します。 演算記号 ※参考 ＋ － ＊ ／ ∧ プラス マイナス アスタリスク スラッシュ ハットマーク 文字列を結合する ＆ =”ABC”&”DEF”→ABCDEF アンパサント 1 CK パソコンスクール Ver2.00 表 ※ 計 算 練 習 ② 合計を求める関数を練習しましょう。 １）データを入力しましょう。 ２）セル【Ｆ４】に、1990 年度の合計を関数を使って求めましょう｡ ３）セル【Ｆ５】～【Ｆ９】に、【Ｆ４】の式をコピーして各年度の合計を求めましょう｡ ４）セル【Ｃ１０】に、東京の合計を関数を使って求めましょう｡ ５）セル【Ｄ１０】～【Ｆ１０】に、【Ｃ１０】の式をコピーして大阪、九州の合計およ び総合計を求めましょう｡ ６）セル【Ｂ３】～【Ｆ１０】の範囲に格子状に罫線を引きましょう。 7）「Excel 練習」フォルダの中に「表計算練習 2.xls」と名前を付け保存します。 ※Hint SUM 関数 AutoΣボタンを使いましょう。 オートフィルを使って年度を効率よく入力しましょう。 2 CK パソコンスクール Ver2.00 表 ※ 計 算 練 習 ③ 効率的なデータ入力と修正、列幅変更を練習しましょう。 １）データを入力しましょう。 ２）上の表を参考に各項目の列幅を変更しましょう．Ｂ列からＫ列までは列幅を最適値に しましょう。B3:K23 に罫線（格子）を引きましょう。 ３）セル【Ｃ１６】の「磯野マリナーズ」を「高岡マリナーズ」に、またセル【Ｂ６】の 「野島信也」を「星野信也」に変更しましょう。 ４）チームの空欄に「スーパーズ」、守備の空欄に「外野手」、出身の空欄に「大阪」を効 率よく入力しましょう。 ５）「Excel 練習」フォルダの中に「表計算練習３.xls」と名前を付け保存します。 ※Hint 連続して貼り付けましょう。 Ctrl キー Shift キー 非連続な複数の範囲を選択します。 「開始セル」と「終了セル」に囲まれた範囲を選択します。 ・「'96 草野球…は、''96 草野球…と入力します。 最初の'は文字符号として 次の'は文字として必要。 ・オートコンプリート機能を確認しましょう。「しみ」と入力すると「清水インパルス」と表示されます。 ・リストから選択 C23 セルをアクティブセルとし、右クリック→「ドロップダウンリストから選択」を クリックしリストから「清水インパルス」を選択します。 3 CK パソコンスクール Ver2.00 表 ※ 計 算 練 習 ④ 表示形式の変更と計算式の設定を練習しましょう。 １）データを入力しましょう。 ２）「品名」「単価」「在庫数」「金額」をセルの中で中央揃えしましょう。 罫線を参考表の通り引きましょう。 ３）「合計」を【Ｂ１６】から【Ｄ１６】の範囲でセルを結合し中央揃えしましょう。 ４）Ｃ列、Ｄ列に３桁区切りカンマを設定しましょう。 ５）【Ｅ４】に「ボールペン」の金額（単価×在庫数）を求め、他の商品の金額は式のみ 複写して求めましょう。 ６）【Ｅ１６】に金額の合計を関数を使って求めましょう。 ７）Ｅ列の金額と金額の合計の数値を通貨（￥）表示しましょう。 ８）「Excel 練習」フォルダの中に「表計算練習４.xls」と名前を付け保存します。 ※Hint 書式設定ツールバーのアイコンを上手に使いましょう 4 CK パソコンスクール Ver2.00 表 ※ 計 算 練 習 ⑤ 表示形式の変更（％表示）を練習しましょう。 １）データを入力しましょう。 図表の通り罫線を引きましょう。 ２）各コースの申込者合計を求めましょう。 ３）各コースの目標達成率（申込者合計÷３ヶ月目標）を求めましょう。 ４）目標達成率を、整数でパーセント（％）表示にしましょう。 ５）G4 セルの「申込者合計」を申込者の位置でセル内で改行しましょう。 H4 セルの「目標達成率」を目標の位置でセル内改行しましょう。 B 列から H 列の列幅を最適値の列幅に設定しましょう。 ６）「Excel 練習」フォルダの中に「表計算練習５.xls」と名前を付け保存します。 ※ Hint ・「3 桁区切りカンマ」「％」「通貨\」表示は、指定された桁位置で表示のみが四捨五入されます。 実際に値が四捨五入されるわけではありません。 ・セル内の改行は、セルの編集状態で改行位置にカーソルを置き→Alt＋Enter （セル内の文字列の配置の確認 書式→セル→配置→文字列の制御が☑されています。 ） 5 CK パソコンスクール Ver2.00 表 ※ 計 算 練 習 ⑥ 平均を求める関数を練習しましょう。 １）データを入力しましょう。 図表のとおり罫線を引きましょう。 セル C2:E2 を結合し、列幅を最適値に自動調整をしましょう。 ２）各店の数量合計【Ｆ４】～【Ｆ１３】、売上数量の総合計【Ｃ１４】～【Ｆ１４】を 一度に求めましょう。 ３）各商品の売上数量、数量合計の平均【Ｃ１５】～【Ｆ１５】を求めましょう。 表示は整数単位とします。 ４）各支店の数量平均【Ｇ４】～【Ｇ１３】を求めましょう。 表示は小数点第１位までとします。 ５）「Excel 練習」フォルダの中に「表計算練習６.xls」と名前を付け保存します。 Hint SUM 関数 AVERAGE 関数 6 CK パソコンスクール Ver2.00 表 ※ 計 算 練 習 ⑦ 表示形式の変更（小数点以下の桁数）を練習しましょう。 １）データを入力しましょう。図表のとおり書式を設定しましょう。 範囲 B8:B17 の横位置配置を、「均等割付（インデント）」 インデントを１とします。 ２）各人の理想体重「（身長－100）×0.9」と差「体重－理想体重」を求めましょう。 ３）計算結果は、小数点以下第２位まで表示しましょう。 ４）各項目の平均値を求めましょう。表示は小数点第２位までとする。 ５）「Excel 練習」フォルダの中に「表計算練習７.xls」と名前を付け保存します。 Hint 「均等割付（インデント）」 「書式」メニュー→「セル」→「配置」タグ→「横位置」 7 CK パソコンスクール Ver2.00 表 ※ 計 算 練 習 ⑧ 合計を求める関数と平均を求める関数を練習しましょう。 １）データを入力しましょう。図表のとおり書式を設定しましょう。 ２）個人別合計を求めましょう。 ３）教科別の平均点を求めましょう。 ４）「Excel 練習」フォルダの中に「表計算練習８.xls」と名前を付け保存します。 Hint・効率よい数値データの入力 Enter で下に D4:I23 を範囲選択し入力を開始する。 Shift＋Enter で上に Tab で右に 8 Shift＋Tab で左に移動。 CK パソコンスクール Ver2.00 表 ※ 計 算 練 習 ⑨ 最大値・最小値を求める関数を練習しましょう。 １）練習⑧のデータを呼び出して、修正しましょう。図表のとおり書式を設定しましょう。 ２）教科別の最高点・最低点を求めましょう。 ３）「Excel 練習」フォルダの中に「表計算練習９.xls」と名前を付け保存します。 ※Hint MAX 関数 MIN 関数 ・オートΣの選択範囲に注意しましょう。範囲を再指定しましょう。 9 CK パソコンスクール Ver2.00 表 ※ 計 算 練 習 ⑩ データを数える関数を練習しましょう。 １）データを入力しましょう。図表のとおり書式を設定しましょう。 ２）各コースの開催回数を求めましょう。 （空白のセルは未開催日です。） ３）各コースの参加者数を求めましょう。 ４）「Excel 練習」フォルダの中に「表計算練習 10.xls」と名前を付け保存します。 ※Hint COUNT 関数 COUNT COUNTA COUNTA 関数の使い分けをしっかり学習しましょう。 数値が入力されているセルを数える。 数値と文字が入力されているセルを数える。 10 CK パソコンスクール Ver2.00 表 ※ 計 算 練 習 ⑪ いろいろな関数の指定方法を練習しましょう。 １）データを入力しましょう。図表のとおり書式を設定しましょう。 ２）ジャンル別売上合計と週別売上合計を求めましょう。 一括して縦横の合計を求めましょう。 ３）ジャンル別売上平均・週別売上平均を求めましょう。 ４）週別売上最高枚数を求めましょう。 ５）「Excel 練習」フォルダの中に「表計算練習 11.xls」と名前を付け保存します。 11 CK パソコンスクール Ver2.00 表 ※ 計 算 練 習 ⑫ 行挿入を練習しましょう。 １）練習⑪のブックを呼び出して、１５行目行を挿入し、最低値の欄を作成しましょう。 図表のとおり書式を設定しましょう。 ２）各週の最低売上枚数を求めましょう。 ３）「Excel 練習」フォルダの中に「表計算練習 12.xls」と名前を付け保存します。 12 CK パソコンスクール Ver2.00 表 ※ 計 算 練 習 ⑬ 列挿入とセルの絶対参照指定を練習しましょう。 １）練習⑫のブックを呼び出して、Ｈ列に売上比の欄を作成しましょう。図表のとおり書 式を設定しましょう。 ２）１８行目に週別売上比の欄を作成しましょう。 ３）ジャンル別売上比「ジャンル別売上合計÷全体の合計」 を求めましょう。 ４）週別売上比「週別売上合計÷全体の合計」を求めましょう。 ５）３）と４）で求めた数値を小数点以下第１位までのパーセント表示（％）表示にしま しょう。 ６）「Excel 練習」フォルダの中に「表計算練習 13.xls」と名前を付け保存します。 ※Hint Ｆ４キーの使い方をしっかり身に着けましょう。 絶対参照、相対参照、複合参照 13 CK パソコンスクール Ver2.00 表 ※ 計 算 練 習 ⑭ 数値を四捨五入する関数を練習しましょう。 １）データを入力しましょう。図表のとおり書式を設定しましょう。 ２）各商品の５８．８％増しの定価「原価× 原価×1.588 原価×1.588」 1.588」を求めましょう。 ３）定価をそれぞれの位で四捨五入した数値を関数を使って求めましょう。 例）セル【Ｅ４】には、【Ｄ４】の値を小数点以下第１位で四捨五入する関数を入力 する。 ４）「Excel 練習」フォルダの中に「表計算練習 14.xls」と名前を付け保存します。 Hint ・ROUND 関数 ROUNDUP 関数 ・罫線のスタイルを破線に変更 ※参考 ROUNDDOWN 関数を身に着けましょう。 「書式」→「セル」→「罫線」→ INT 関数と ROUNDDOWN 関数の違い 14 CK パソコンスクール Ver2.00 表 ※ 計 算 練 習 ⑮ 計算結果を四捨五入しましょう。 １）データを入力しましょう。図表のとおり書式を設定しましょう。 ２）全チームの勝率 「勝数÷（試合数－引き分け数）×1000」 を求めましょう。 計算結果は、関数を使って小数点以下第１位で四捨五入しましょう。 ３）差「１位チームの勝数－そのチームの勝数」を求めましょう。 ４）「Excel 練習」フォルダの中に「表計算練習 15.xls」と名前を付け保存します。 ※Hint Excel は、文字と数値の違いをどう区別しているのでしょう？。 文字は必ず「’」で始まります。画面表示されないだけです。 「'96 草野球…」と表示させるには 「''96 草野球…」と入力しなければなりません。 １個目は文字としての符号 =round 関数 ２個目が表示させる文字として必要です。 絶対参照 15 CK パソコンスクール Ver2.00 表 ※ 計 算 練 習 ⑯ いろいろな関数を復習しましょう。 １）データを入力しましょう。図表のとおり書式を設定しましょう。 ２）行き先別合計と月別合計を求めましょう。 ３）各行き先の月平均、最少人数、最多人数、構成比（行き先別合計÷全体の合計）を求 めましょう。 ４）構成比は、小数点以下第１位までのパーセント（％）表示にしましょう。 ５）構成比以外のすべての数値に３桁区切りカンマを設定しましょう。 ６）「Excel 練習」フォルダの中に「表計算練習 16.xls」と名前を付け保存します。 ※ Hint 絶対参照の使い方を復習しておきましょう。 SUM AVERAGE MAX MIN 関数 16 CK パソコンスクール Ver2.00 表 ※ 計 算 練 習 ⑰ 順位をつける関数を練習しましょう。 １）練習③のブックを開き、F,G,I 列のデータを削除し図表のとおり書式を設定しましょ う。 ２）打席数と安打数から全員の打率（安打数÷打席数×1000）を求めましょう。 計算結果は小数点以下第 1 位で四捨五入しましょう。 ３）打率、本塁打のデータをもとに、それぞれの順位をつけましょう。 ４）「長山茂雄」の本塁打数【Ｈ４】を「１０」に変更しましょう。 ５）「Excel 練習」フォルダの中に「表計算練習 17.xls」と名前を付け保存します。 Hint RANK 関数 true false を学びましょう。 絶対参照 17 CK パソコンスクール Ver2.00 表 ※ 計 算 練 習 ⑱ 棒グラフの作成を練習しましょう。 １）データを入力しましょう。図表のとおり書式を設定しましょう。 ２）各デパートの食品、衣類、生活雑貨の売上を示す棒グラフを作成しましょう。 作成した棒グラフは売上比較表の下に配置しましょう。 ３）「Excel 練習」フォルダの中に「表計算練習 18.xls」と名前を付け保存します。 ４）ページ設定を 上下左右余白 2cm ページ中央（水平）とします。 印刷プレビュー画面で確認しましょう。 ※Hint グラフは範囲の選択が重要です。作成の手順をしっかり学びましょう。 棒グラフは、行系列もグラフ範囲に選択しましょう。 18 CK パソコンスクール Ver2.00 表 ※ 計 算 練 習 ⑲ 折れ線グラフの作成を練習しましょう。 １）データを入力しましょう。図表のとおり書式を設定しましょう。 ２）各都市の１～12 月までの気温のマーカー付折れ線グラフを次のように作成し、表の 下に配置しましょう。 ①各都市名を凡例にしましょう。 ②タイトルは、★主要都市月別平均気温★としましょう。 ③目盛線はＸ軸Ｙ軸ともに付けましょう。 ④フォントサイズは、項目軸は９ポイントとし、それ以外はすべて１１ポイントにしま しょう。 ３）「Excel 練習」フォルダの中に「表計算練習 19.xls」と名前を付け保存します。 19 CK パソコンスクール Ver2.00 表 ※ 計 算 練 習 ⑳ 円グラフの作成を練習しましょう。 １）データを入力しましょう。図表のとおり書式を設定しましょう。 ２）火災発生状況のデータをもとに次のように棒グラフを作成しましょう。 ①「市内」「区内」を凡例にしましょう。 ②タイトルは、「火災発生状況（平成７年度）」としましょう。 ③フォントサイズは、項目軸は９ポイントとし、それ以外はすべて１１ポイントにしま しょう。 ３）区内の火災発生のデータをもとに次のように円グラフを作成しましょう。 ①タイトル、凡例は付けない。 ②データラベルは、ラベルとパーセンテージを表示しましょう。 ③フォントサイズは、すべて１０ポイントにしましょう。 ４）２）の棒グラフ、３）の円グラフは表の下に縦に並べて配置しましょう。 ５）「Excel 練習」フォルダの中に「表計算練習 19.xls」と名前を付け保存します。 ６）「Excel 練習」フォルダを削除しましょう。 「ゴミ箱」の中から「Excel 練習」フォルダを消去しましょう。 Hint 円グラフは、行系列を範囲に含めなくて良い。 フォルダの作成・移動 ファイルの移動・コピー・削除を練習しましょう。 20 CK パソコンスクール Ver2.00 表 ※ 計 算 練 習 21 ○ 条件判断の関数を練習しましょう。 １）データを入力しましょう。書式を図表の通り設定しましょう。 ２）次の条件によって、滑走の欄に「○」かまたは「×」を表示しましょう。 条件：積雪量が 100cm 以上ならば「○」、そうでなければ「×」を表示する ３）「Excel 練習」というフォルダを作成し、表計算練習 21.xls というブック名で保存し ましよう。 《Point》 ・条件判断をする場合→ ・ 論理式： ＜ IF 関数： =IF(論理式，真の場合，偽の場合) ，＞，＞＝，＜＝，＜＞，＝で条件を示す式。 （EX. ＜ 未満 ＞ より大きい ＞＝ 以上 ＜＝ 以下 ＝ 同じ 21 A1>=70･･･A1 が 70 以上の時） CK パソコンスクール Ver2.00 表 ※ 計 算 練 習 22 ○ ＩＦ関数の練習をしましょう。 １）データを入力しましょう。書式を図表のとおり設定しましょう。 ２）セル【Ｅ９】にＩＦ関数を使った式を入力し、料金を求めましょう。その後、【Ｅ１ ０】～【Ｅ１８】へ複写のこと。会員№が入力されている人はメンバー料金で、それ 以外はビジター料金です。 ３）【Ｅ１９】に合計金額を求めましょう。 ４）使用日の日付の表示形式を、“mm/dd”に変更しましょう。 ５）表計算練習 22.xls というブック名で 「Excel 練習」フォルダに保存します。 《Point》 ・空欄の処理→ ””と連続入力すると、空欄の意味になる。 ・絶対参照 22 CK パソコンスクール Ver2.00 表 ※ 計 算 練 習 23 ○ 出力を文字に変えて曜日を求める関数を練習しましょう。 １）データを入力しましょう。書式を図表のとおり設定しましょう。 ２）セル C4:C10 に 日付から曜日を求めましょう。 ３）表計算練習 23.xls というブック名で「Excel 練習」フォルダに保存します。 《Point》 ・シリアル値に含まれる情報→ ・ 年、月、日、曜日、 （小数点以下は時間） 数値を表示形式を指定された文字列に変換出力する関数→ TEXT 関数： =TEXT(値,表示形式) 《参考》 ・ 曜日をあらわす整数に変換する関数→ セルの表示形式を aaaa とすると aaa とすると =WEEKDAY(シリアル値,[種類]) “○曜日” “○”と表示されます。 23 ※[種類]は省略可 CK パソコンスクール Ver2.00 表 ※ 計 算 練 習 24 ○ 見積書を作成してみましょう。 １）データを入力しましょう。 罫線、塗り潰しの色、列幅、行高などの書式を図表の通 り設定しましょう。 ２）セル【Ｆ２】の日付を関数 で 今 日 の 日 を 求め 、 yy/mm/dd の 書 式 に 変更 し ましょう。 ３）金額、小計、消費税(5%)、 合計をそれぞれ求めましょ う。 品名が空白のときは、金額を計算しないようにしましょう。 顧客名、品名のセルの入力規則を「ひらがな」数値と単価のセルを「日本語入力オ フ」に設定しましょう。 ４）セル【Ｅ２５】の合計が、セル【Ｅ１５】に表示されるように計算式を入れましょう。 その際、表示形式は桁区切りスタイルに設定しましょう。 ５）セル【Ｅ１８】～セル【Ｅ２５】のセルに、通貨スタイルの書式を設定しましょう。 ６）セル【Ｅ１５】の書式を、セル【Ｄ１５】・【Ｆ１５】と同じにしましょう。 ７）表計算練習 24.xls というブック名で「Excel 練習」フォルダに保存します。 ※Hint ・TODAY 関数 ・入力規則 IF 関数 「データ」→「入力規則」→「日本語入力」→ 24 CK パソコンスクール Ver2.00 表 ※ 計 算 練 習 25 ○ 並べ替えの練習をしましょう。 １）データを入力しましょう。図表の通り書式を設定しましょう。 ２）「氏名」を５０音順に並べ替えましょう。 ３）「年齢」を降順に並べ替えましょう。 ４）「〒」を昇順に並べ替えましょう。ただし同じ郵便番号ならば「年齢」の若い人順に しましょう。 ５）男女別に分類し、それぞれの中で年齢の高い人順に並べ替えましょう。 ６）表計算練習 25.xls というブック名で「Excel 練習」フォルダに保存します。 ※参考 フリガナを求める関数を使ってみましょう。 PHONETIC 関数 フリガナの編集で修正しましょう。 書式→フリガナ→編集 25 CK パソコンスクール Ver2.00 表 ※ 計 算 練 習 26 ○ オートフィルタの練習をしましょう。 １）データを入力しましょう。図表の通り書式を設定しましょう。 ２）開店時間、閉店時間を AM/PM 付きの表示にしましょう。 ３）所在地が「北区」のお店を抽出しましょう。抽出後は元に戻しましょう。 ４）予算が「3,000 円以上 5,000 円以内」のお店を抽出しましょう。抽出後は元に戻しま しょう。 ５）所在地が中央区で、22:00 もやっているお店を抽出しましょう。抽出後は元に戻しま しょう。 ６）日曜日と祝日にやっているお店を抽出しましょう。 ７）表計算練習 26.xls というブック名で「Excel 練習」フォルダに保存します。 ※ Hint オートフィルタ 「データ」→「フィルタ」→「オートフィルタ」 ※ 参考 リストを構成する条件を確認しておきましょう。 26 CK パソコンスクール Ver2.00 表 ※ 計 算 練 習 27 ○ 表の印刷の練習をしましょう。 １）表計算練習 26.xls のブックを開きましょう。 オートフィルタを解除して、範囲 B4:I23 を B24 及び B44 にコピーしましょう。 ２）以下のように印刷の指定をしましょう。 ・印刷範囲 範囲 B2:I63 ・ 上下余白 2cm ・ ヘッダー 右揃えで「CK パソコンスクール」 ・ フッダー 中央揃えで「ページ番号」 ・ 行のタイトル ３行目 ・ ページ中央 水平 ・ 改ページ位置 59 行目の下に変更 ・ 印刷プレビューで確認しましょう。 ３）拡大／縮小印刷を横・縦 1 ページに設定しましょう。 印刷プレビューで 1 ページに縮小されたことを確認しましょう。 ４）表計算練習 27.xls というブック名で「Excel 練習」フォルダに保存します。 27 CK パソコンスクール Ver2.00 表 ※ 計 算 練 習 28 ○ ビジネス用表計算を作ってみましょう。（入力規則・シートの保護） 1）データを入力し、図表の通り罫線を引き書式を設定しましょう。 28 CK パソコンスクール Ver2.00 2）列幅 6.25 文字→ ABCDEHKL列 5.25 文字→ F 列 6.75 文字→G 列 4.25 文字→I 列 8.25 文字→J 列 3）行高 22.5pt→ 11.25pt→ 7.5pt→ 27.75pt→ 17.25pt→ 1,19～33 行 2行 14,17,34,41,46 行 16 行 18 行 4)L19:L33 に「金額」を求めましょう。 但し、商品名が未記入のとき空白とします。 E16 に「今回お買上高」 G16 に「消費税」 L16 に「今回ご請求額」を求めましょう。 5）入力規則を設定しましょう。 A19:A33 K4 入力値の種類 日付 データ 次の値以上 次の日付から 2006/1/1 日本語入力 オフ（英語モード） A2:A6 J12 B19:B33 A34 日本語入力 ひらがな H19:I33 J13 A16 C16 I16 日本語入力 オフ（英語モード） ６）シートの保護を設定しましょう。 保護ロックを解除するセル A2:A6 K4:K5 J12:J13 A16 C16 I16 A19:I33 A36 ユーザに許可する操作は ☑「ロックされたセルの選択」とします。 ７）商品名・数量。単価を任意に入力し印刷してみましょう。 ８）表計算練習 28.xls というブック名で「Excel 練習」フォルダに保存します。 Point Hint ・列幅の規定値は 8.39（72 ピクセル）です。→半角英数字 11 ポイントで 8.39 文字を表しています。 印刷時約 18ｍｍの列幅になります。 列高の規定値は 13.5（18 ピクセル）です。→13.5 ポイントで約 4.7 ｍｍの行高になります。1pt≒0.3514mm ピクセル→画素数をあらわします。通常画面のサイズは 1024×718 ピクセル ・入力規則 「データ」→「入力規則」 ・シートの保護 「書式」→「セル」→「保護」→「ロック」で入力可セルを解除する。 「ツール」→「保護」→「シートの保護」でシートを保護する。 29 CK パソコンスクール Ver2.00 表 ※ 計 算 練 習 29 ○ ワークシートを連携し、別のシートを参照しましょう。 １） シート Sheet1 にデータを入力し書式を設定しまし ょう。 ２） シート Sheet1 のシートコピーを 2 回しましょう。 シート Sheet1、Sheet1(2)、Sheet1（３）のシート名を それぞれ富山本店、高岡支店、魚津支店と変更しましょ う。 シート見出しの色を黄色に変更しましょう。 シートを移動し、富山本店、高岡支店、魚津支店、Sheet2、Sheet３の順に並べ替えましょう。 ３） シート Sheet2 にデータを入力し書式を設定しましょう。 シート名を全社、シート見出しの色を「青」に変更しましょう。 ４） シート Sheet3 を削除しましょう。 ５） 高岡、魚津支店の各シートのセル E１を高岡支店、魚津支店に変更しましょう。 セル C5:D8 のデータを以下のように変更しましょう。 ６） シート富山本店から魚津支店までの３枚のシートを作業グループに指定しましょう。 セル B1:D1 を結合し中央揃えに設定しましょう。 セル B1 のフォントを HG 丸ゴシック M-PRO にフォントサイズを 14pt に変更しましょう。 セル B4:E4 を中央揃えに変更し、セル C5:D9 を桁区切りスタイルに変更しましょう。 合計と達成率を計算し、セル E5:E9 を少数点以下 1 桁の％表示に設定しましょう。 作業グループを解除しておきましょう。 ７） シート「全体」を開きましょう。 セル C5：D7 の各セルを別のシートを参照し表示しましょう。 合計と達成率を計算し、達成率を少数点以下 1 桁の％表示に設定しましょう。 セル B1:D1 を結合し中央揃えに設定しましょう。 セル B1 のフォントを HG 丸ゴシック M-PRO にフォントサイズを 14pt に変更しましょう。 セル B4:E4 を中央揃えに変更し、セル C5:D8 を桁区切りスタイルに変更しましょう。 ８） Point 表計算練習 2９.xls というブック名で「Excel 練習」フォルダに保存します。 セル参照とリンク貼り付けを確認しておきましょう。 シートのコピーは コピー元のシートのシートタブを、Ctrl を押しながらドラッグします。 30 CK パソコンスクール Ver2.00 表 ※ １） 計 算 練 習 30 ○ 複合参照を練習しましょう。 データを入力し、図のとおり罫線等の書式を設定しましょう。 ２）セル D5:F8 に商品別の割引価格を求めましょう。 割引率は、D4:F4 を参照しましょう。 計算式 「メーカー希望価格×（１－割引率）」 ３）表計算練習 30.xls というブック名で「Excel 練習」フォルダに保存します。 Hint ・「10％」と入力すること 「0.1」と入力し書式を％表示させることとは同一です。 D4：F4 は数値として入力されています。 ・複合参照 絶対参照キーF４を続けて押すと、1 回押すごとに切り替わります。 列の固定 行の固定 A1 → ＄A＄1 → ＄A1 → A$1 31 CK パソコンスクール Ver2.00 表 ※ 計 算 関 1 数 ○ 複数条件の条件式を練習しましょう。 １）データを入力しましょう。 ２）論文と筆記の合計を求めましょう。 ３）次の条件によって、判定 A と判定 B の欄に「合格」か「不合格」を表示しましょう。 判定 A の条件：合計が 140 点以上は「合格」それ以外は「不合格」 判定 B の条件：論文と筆記の両科目とも 70 点以上は「合格」それ以外は「不合格」 ４）次の条件によって、ランクの欄に「Ａ」「Ｂ」「Ｃ」を表示しましょう。 条件：判定 A と判定 B が両方とも「合格」であれば「Ａ」 判定 A と判定 B のどちらかが「合格」であれば「Ｂ」 判定 A と判定 B のどちらも「不合格」であれば「Ｃ」 《Point》 ・どの条件も満たす→ ＡＮＤ関数（EX. AND(A1>=70,B1>=70)） ・いずれかの条件１つでも満たせばよい ・関数のネスト： →ＯＲ関数（EX. OR(A1>=70,B1>=70)） 関数の中に関数が含まれるような式 （EX. =IF(AND(A1>=70,B1>=70),”合格”,”不合格”)） ※Hint 複雑な if 関数を制覇するには、フローチャートをまず図き、処理の流れを図式化することです。 フローチャートの描き方を学びましょう。 32 CK パソコンスクール Ver2.00 表 ※ 計 算 関 2 数 ○ 日付計算を練習しましょう。 １）データを入力しましょう。 ２）【Ｂ１】に今日の日付を「yyyy/mm/dd」の形式で表示しましょう。 ３）生年月日と今日の日付から年齢を求めましょう。 ４）年齢が 30 歳以上の人は、受診欄に「要」と表示しましょう。 《Point》 ・日数や年数を計算する→ ・シリアル値： DATEDIF 関数： =DATEDIF(開始日，終了日，単位) Excel では、日付はシリアル値として連続番号で格納されて計算が行われる。 33 CK パソコンスクール Ver2.00 表 ※ 計 算 関 3 数 ○ 経過期間を求める関数を練習しましょう。 １）データを入力しましょう。 ２）入社年月日と退職年月日から勤続年月日を求めましょう。 《Point》 ・DATEDIF 関数を利用して年月日をそれぞれ求める→ 単位を変える（”Y”,”YM”,”MD”） 34 CK パソコンスクール Ver2.00 表 ※ 計 算 関 4 数 ○ データを参照する関数を練習しましょう。 １）データを入力しましょう。 ２）Ｂ列に郵便番号を入力したら、Ｃ列に住所（区名）が表示されるように関数を設定し ましょう。 キーワード：郵便番号（Ｂ列） 参照先 ：住所リスト 《Point》 ・データを参照する→VLOOKUP 関数： =VLOOKUP(検索値,範囲,列番号,検索の型) 検索の型→ TRUE または FALSE を入力する 35 CK パソコンスクール Ver2.00 表 ※ 計 算 関 5 数 ○ ふりがなを表示する関数を練習しましょう。 １）データを入力しましょう。 ２）フリガナ欄に、フリガナを表示する関数を入力しましょう。 《Point》 ・ふりがなを表示する→PHONETIC 関数： =PHONETIC (値) 36 CK パソコンスクール Ver2.00 表 ※ 計 算 関 6 数 ○ 住所録を完成させましょう。 １）データを入力しましょう。 ２）セル【Ｄ２０】にふりがなが表示されるように、関数を使って求め、３１行目までコ ピーしましょう。 ３）セル【Ｃ４】とセル【Ｄ４】を氏名データ一覧（セル【Ｂ２０】～【Ｄ３１】を利用 して１６行目までコピーしましょう。ただし、該当するデータがない場合は、空欄に すること。 《Point》 ・エラー値を空欄にする→ 「該当なしの場合は空欄にする」という意味の条件を加える。 37 CK パソコンスクール Ver2.00 表 ※ 計 算 関 7 数 ○ データベース関数を使ってみましょう。 １）データを入力しましょう。 ２）関数を利用して、講習マスターから講習名を表示させましょう。 ３）日付の表示形式を、m/d(aaa) の形式で表示しましょう。 ４）ＤＣＯＵＮＴＡ関数を使って、セル【Ｄ２５】に“東田”さんの担当した講習の回数 を、セル【Ｅ２５】に“インターネット”の講習が開かれた回数を、セル【Ｆ２５】 に会場が“魚津”の回数をそれぞれ表示させましょう。 《Point》 ・データベース関数： データベースに格納されているデータを分析するワークシート関数で、 DCOUNTA,DSUM などがある。 =DCOUNTA(データベース,フィールド名,条件) 38 CK パソコンスクール Ver2.00 表 ※ 計 算 関 8 数 ○ 電車料金を計算しましょう。 １）データを入力しましょう。 ２）セル【Ｅ１１】に電車種類「２」（急行＋普通席）、セル【Ｇ１１】キロ数に「５」 （201km～）をそれぞれ入力したとき、セル【Ｅ１３】に料金が表示されるようにし ましょう。 ３）ファイル名「電車料金」で保存しましょう。 《Point》 ・INDEX 関数： =INDEX(配列,行番号,列番号) 39 CK パソコンスクール Ver2.00 表 ※ 計 算 関 9 数 ○ 電車料金を計算しましょう。 １）ファイル名「電車料金」を呼び出し、以下のように編集しましょう。 ２）セル【Ｄ１４】に、電車種類の「特急＋普通席」にあたる番号を表示させましょう。 ３）セル【Ｅ１４】に、キロ数の「～100km」にあたる番号を表示させましょう。 ４）セル【Ｄ１４】と【Ｅ１４】の計算結果を元に、セル【Ｈ１４】の料金を求めましょ う。 ５）上書き保存しましょう。 《Point》 ・MATCH 関数： =MATCH(検査値,検査範囲,照合の型) （照合の型⇒ 40 ０:完全一致） CK パソコンスクール Ver2.00 表 ※ 計 算 関 10 数 ○ 電車料金を計算しましょう。 １）ファイル名「電車料金」を呼び出し、以下のように編集しましょう。 ２）セル【Ｈ１３】に、電車種類が「普通乗車券」、キロ数が「～200km」の料金を求めま しょう。 《Point》 ・INDEX 関数と MATCH 関数を同時に使用する→ 関数のネスト 41 CK パソコンスクール Ver2.00 表 ※ 計 算 関 11 数 ○ 文字列操作関数の練習しましょう。 １）データ入力しましょう。 ２）管理ＩＤは、次のような７桁の文字列で構成されています。列Ｃ～Ｅのそれぞれの欄 に、管理ＩＤのデータを利用して、関数を使って求めましょう。その際、便名は数値 データとしておくこと。 先頭３文字･･･会社ＩＤ、次の３文字･･･便名、末尾１文字･･･地域ＩＤ 《Point》 ・RIGHT、LEFT 関数： =RIGHT(又は LEFT)(文字列,文字数)・・・文字列の先頭（末尾）から指定した数の文字数 を返す。 ・MID 関数： ・VALUE 関数： =MID(文字列,開始位置,文字数)・・・文字列の指定した位置から指定した数の文字数を返す。 =VALUE(文字列)・・・文字列の数字を数値に変換して返す。 42 CK パソコンスクール Ver2.00 表 ※ 計 算 応 用 1 ○ フィルタオプションの練習をしましょう。 １）データを入力しましょう。 ２）所在地が「港区」のホテルを、セル【Ｂ２５】以降に抽出しましょう。 ３）所在地が「港区」で、区分が「ビ（ビジネスホテル）」のホテルをセル【Ｂ２５】以 降に抽出しましょう。 ４）区分が「ホ（ホテル）」で、ツインの料金が「30,000 円以内」のホテルをセル【Ｂ２ ５】以降に抽出しましょう。 ５）所在地が「港区」か「新宿区」で、シングルの料金が「15,000 円より高い」ホテル をセル【Ｂ２５】以降に抽出しましょう。 43 CK パソコンスクール Ver2.00 表 ※ 計 算 応 用 2 ○ グラフの練習をしましょう。 １）データを入力しましょう。計算が必要な箇所は計算しましょう。 ２）支店別毎の各月の予算実績を表すグラフを、以降の設定により作成ましょう。 ①グラフはおおむねセル【Ｂ１３】から【Ｉ３１】の範囲 ②各月の実績および予算額を棒グラフ、合計の額を折れ線グラフとする複合グラフ ③項目軸は、月名（データのみで、項目軸ラベルなし） ④グラフタイトルは、「予算管理」とし、ＭＳＰゴシック、１８ポイントで太字と斜 体の設定をする。 ⑤データラベルは合計の値を表示する。 ⑥第二軸の最大値は、25000 とする。 ⑦凡例は、プロットエリアの右側に表示する。 ⑧グラフエリアの輪郭はなし、影付きに設定し、テクスチャのブーケで塗りつぶす。 ⑨折れ線グラフは、目立つように太線にすること。 ⑩上記の指示以外の文字はすべてＭＳＰゴシックの１０ポイントとする。 ２）ファイル名「売上予算１」で保存しましょう。 44 CK パソコンスクール Ver2.00 表 ※ 計 算 応 用 3 ○ グラフの練習をしましょう。 １）ファイル名「売上予算１」を呼び出し、グラフを以下のように編集しましょう。（グ ラフタイトルと凡例） ２）グラフタイトルを、「売上予算管理表」に変更しましょう。 ３）グラフを４月～７月までのデータに変更しましょう。 ４）横浜支店の４月のデータが修正になりました。表中データを 6,300 → 7,500 に変更 しましょう。 ５）第一軸の最大値を 10,000 に、目盛間隔を 2,000 ごとに変更しましょう。 ６）プロットエリアをグラデーション「夜明け」に設定しましょう。ただし種類は「横」 で、バリエーションは左上とする。 ７）ファイル名「売上予算２」で保存しましょう。 45 CK パソコンスクール Ver2.00 表 ※ 計 算 応 用 4 ○ グラフの練習をしましょう。 １）ファイル名「売上予算１」を呼び出し、以下のように表を編集しましょう。グラフは 削除しておきましょう。 ２）月ごとの各支店の割合がわかるような 100%積み上げ横棒グラフを、次の設問に従っ て作成しましょう。 ①グラフは概ねセル【Ｂ１４】から【Ｈ２９】の範囲とする。 ②グラフタイトルは「月別売上割合」とし、ＭＳゴシック・１４ポイントに設定す る。 ③凡例は、プロットエリアの下端に配置する。 ④各グラフの系列には、区分線を引く。 ⑤横浜支店を表す系列には、データラベルの値を設定する。 ⑥上から４月、５月となるように軸を変更する。 ⑦グラフエリアは、テクスチャ「キャンバス」で塗りつぶす。 ⑧プロットエリアは、グラデーション「ゴールド」で塗りつぶす。ただし、種類は 「横」で、バリエーションは左上とする。 ⑨グラフエリアの右上に、以下のオートシェイプを配置しましょう。 ただし、中の文字はＭＳゴシック１２ポイントで、黄色で塗りつぶしましょう。 ３）ファイル名「売上予算３」で保存しましょう。 46 横浜支店好調！ CK パソコンスクール Ver2.00 表 ※ 計 算 応 用 5 ○ グラフの練習をしましょう。 １）データを入力しましょう。計算が必要な箇所は計算しましょう。 ２）「ＲＶ・４ＷＤ」で、各店の売上が棒グラフ、「ＲＶ・４ＷＤ」の各月の合計が折れ線 グラフになるような月別の複合グラフを以下の条件に従って作成しましょう。 ①グラフはおおむねセル【Ｉ４】から【Ｏ２６】の範囲に作成する。 ②凡例は表示し、下端に配置する。 ③合計の折れ線グラフは第二軸とし、最大値を 1,000,000 とする。 ④第一軸の最大値を 500,000 とし、第一軸上部に「単位：千円」という軸ラベルを 作成する。 ⑤グラフタイトルは「上半期ＲＶ・４ＷＤ売上実績」とし、ＭＳゴシック・１４ポ イント・輪郭は影付きに設定する。 ⑥プロットエリアは、グラデーション「凪」で塗りつぶす。ただし、種類は「横」 で、バリエーションは左上とする。 ⑦グラフエリアは、テクスチャ「青い画用紙」で塗りつぶし、輪郭は影付きに設定 する。 ⑧上記の指示以外の文字はすべてＭＳゴシックの８ポイントとする。 ３）ファイル名「車種別売上１」で保存しましょう。 47 CK パソコンスクール Ver2.00 表 ※ 計 算 応 用 6 ○ グラフの練習をしましょう。 １）ファイル「車種別売上１」を呼び出しましょう。 ２）本店の棒グラフをピンク、駅前北店を黄色、駅前南店を水色に塗り替えましょう。 ３）合計の折れ線グラフの線を現在より太く、マーカーを「▲（中を塗りつぶす）」に変 更し、色はすべて濃い青にしましょう。また、データラベルの値を表示し、数字がみ えるよう、場所を変更しましょう。 ４）一番ＲＶ・４ＷＤを売り上げた月の店の棒グラフの色を赤色に塗りつぶしましょう。 ５）グラフのみを、ページ中央の水平・上下左右の余白は２・ヘッダ左には自分の氏名 （ひらがな）・右には今日の日付・フッタ中央にはグラフタイトルと同じタイトル名 を入力し、Ａ４縦１枚におさまるように印刷しましょう。必ず印刷範囲の設定を行う こと。 ６）ファイル「車種別売上２」で保存しましょう。 48 CK パソコンスクール Ver2.00 表 ※ 計 算 応 7 用 ○ 配列数式を活用しましょう。 １）データを入力し表のとおり書式を設定しましょう。 ２）配列数式を用いて F4：F8 に生徒別の平均値を求めましょう。 ３）ファイル「配列数式１」で保存しましょう。 ※Hint 配列とは複数のデータをひとまとめのデータとしてあつかうためのものです。 配列を利用した数式入力の手順 ① 数式を入力する範囲 F4:F8 を選択する。 ② 数式を入力する。 （各教科の配列を加算し 4 で除算）します。 ③ Ctrl+Shift+Enter をおす。 数式が｛ ｝で囲まれ F4:F8 に計算される。 ●配列数式を削除するには、配列範囲を選択し Delete をおす。 ●配列範囲全体を選択するには、配列範囲の任意のセルをクリックし、Ctrl+/をおす。 ●配列数式の修正 配列書式のいずれかのセルを修正し、Ctrl+Shift+Enter をおす。 配列数式は、数式の部分的修正が出来ないため、実務面で改ざん防止策として重宝 配列数式は、数式の部分的修正が出来ないため、実務面で改ざん防止策として重宝されて 、数式の部分的修正が出来ないため、実務面で改ざん防止策として重宝されて います。 49 CK パソコンスクール Ver2.00 表 ※ 計 算 応 8 用 ○ 配列数式と IF 関数を使ってみましょう。 １） データを入力し表のとおり書式を設定しましょう。 ２） 各教科の男女別の平均点を配列数式を用いて求めましょう。 ３）配列数式 2 として保存しましょう。 ※Hint 配列数式を関数の引数として入力します。 ①G5 をクリックする。 ②AVERAGE 関数と IF 関数をネストします。 性別の配列を IF 関数の「論理式」に入力し、国語の配列を「真の場合」に入力する。 「偽の場合」は省略したとき、０と認識される。 IF(B4:B13=”男”,E4:E13) ③Ctrl+Shift+Enter をおす。 50