Microsoft Word - excel2007上級 doc

Size: px

Start display at page:

Download "Microsoft Word - excel2007上級 doc"

とらふみてっちがわら
5 years ago
Views:

1 (Vista Version) Excel2007 上級初歩からの PC テキスト

2 第章ゴールシーク... 4 準備と手動シミュレーションゴールシークの使用ゴールシーク検証まとめ練習問題... 7 第 2 章ピボットテーブル... 9 準備ピボットテーブルとは? ピボットテーブルの利用ピボットテーブルの調整ピボットテーブルのデザイン日付フィールドのグループ化集計の方法を変える ( レコード数を調べる ) 計算の種類を変更する (% 割合であらわす) 数値フィールドに一括で表示形式を設定するピボットテーブルの更新抽出状態からの分析詳細の表示ページを分けるピボットグラフの作成まとめ練習問題練習問題第 3 章ソルバー... 4 準備ソルバーアドインの登録ソルバーからゴールシークを使う最大値最小値を使用した調査制約条件制約条件にて整数のみを使用させる複数のセルを同時に変化させる目的セルの優先順序まとめ練習問題第 4 章統合準備統合とは? 統合の操作その他の集計 ( 平均 ) 統合表の性質を確認する [ リンクされない ] 自動リンクする統合結果表の作成まとめ練習問題練習問題第 5 章特殊な並べ替え連続データ作成の復習ユーザー設定リストへの追加

3 3 セルからユーザー設定リストに登録するユーザー設定リストからの削除アイテムリストの作成重複の削除ユーザー設定リスト順に並べ替える列方向の並べ替えまとめ練習問題第 6 章フィルタオプション準備通常のオートフィルタの復習 OR 条件のフィルタオプションの利用 AND 条件のフィルタオプション絞り込み条件 (AND 条件 ) の追加不等号の使用 ~を含む OR 条件と AND 条件の組み合わせまとめ練習問題練習問題第 7 章マクロマクロとは? マクロの登録と実行マクロ記録の練習選択済みの範囲に対して処理をするマクロクイックアクセスツールバーにマクロを割り当てるショートカットキーにマクロを割り当てる相対参照マクロクイックツールバーにマクロを割り当てるマクロ付きファイルの保存まとめ練習問題記載されている会社名製品名は各社の商標および登録商標です本書の例題や画面などに登場する企業名や製品名人名キャラクターその他のデータは架空のものです現実の個人名や企業製品イベントを表すものではありません本文中には, マークは明記しておりません本書は著作権法上の保護を受けております本書の一部あるいは全部について合資会社アルファから文書による許諾を得ずにいかなる方法においても無断で複写複製することを禁じますただし合資会社アルファから文書による許諾を得た期間は除きます無断複製転載は損害賠償著作権法の罰則の対象になることがありますこの教材は Microsoft Corporation のガイドラインに従って画面写真を使用しています著作製作合資会社アルファ発行人三橋信彦発行神奈川県横浜市戸塚区戸塚町 8-2 中山 NS ビル 6F 定価 \5,040 円 3

$第章ゴールシーク準備と手動シミュレーション () 以下のような計算表を作成して下さいなお [ 販売価格 ] 欄には単価 (00%+ 消費税 ) の計算式を入力します [ 合計金額 ] 欄には販売価格数量の計算式を入力します A B C D E F G 2 単価 \,250 単価 2 3 消費税 5% 消費税 5% 4 販売価格 \,33 販売価格 2 \0 5 数量 6 合計金額$

4 第章ゴールシーク準備と手動シミュレーション () 以下のような計算表を作成して下さいなお [ 販売価格 ] 欄には単価 (00%+ 消費税 ) の計算式を入力します [ 合計金額 ] 欄には販売価格数量の計算式を入力します A B C D E F G 2 単価 \,250 単価 2 3 消費税 5% 消費税 5% 4 販売価格 \,33 販売価格 2 \0 5 数量 6 合計金額 \0 7 (C4,F4,F6 は数式 ) (2) さてここではセル C4 の [ 販売価格 ] が 200 になるようセル C2 に様々な値を入力してみます最初に 50 を入れてみましょうすると C4 は 208 になりました. セル C2 を 50 に変更すると 2. セル C4 は 208 になった (3) 予定より 8 円オーバーしてしまった ( 販売価格を 200 円にしたかった ) ので単価を 8 円下げ 42 にしてみましょうするとだいぶ近づき 99 になりました. セル C2 を 42 に変更すると 2. セル C4 は 99 になった 4

(4) では単価を円上げてみます 43 にしましょうこれで販売価格が 200 円になりますこのように計算式の結果を目的の値にするためには参照セル ( 使用セル ) の値をいくつにすればよいのかを調べるならば試行錯誤しながら実験的に変化させていけばよいのです (

セル C4 は 200 になった 2 ゴールシークの使用 () 今度は C4 の値を 00 にするためには C2 の値をいくつに設定すればよいのかを調べます自分で試行錯誤しながら調べてもよいのですが PC に試行錯誤させて調べさせる機能が存在しますゴールシークです

ます 2. データタブから [What-IF 分析 ] ゴールシークをクリック.

5 (4) では単価を円上げてみます 43 にしましょうこれで販売価格が 200 円になりますこのように計算式の結果を目的の値にするためには参照セル ( 使用セル ) の値をいくつにすればよいのかを調べるならば試行錯誤しながら実験的に変化させていけばよいのです ( この手法をシミュレーションと呼ぶ ) ただこの手法だと何回か実験する必要があるので相応の時間が必要です ( 今回は比較的単純でしたが ) 計算式の結果を目的の値にしたいのならば参照セルの値を色々と変化させる実験をすればよい. セル C2 を 43 に変更すると 2. セル C4 は 200 になった 2 ゴールシークの使用 () 今度は C4 の値を 00 にするためには C2 の値をいくつに設定すればよいのかを調べます自分で試行錯誤しながら調べてもよいのですが PC に試行錯誤させて調べさせる機能が存在しますゴールシークです最終目的結果が算出される計算式が入力されたセル ( ここでは C4) をアクティブにしデータタブから [What-IF 分析 ] ゴールシークをクリックします 2. データタブから [What-IF 分析 ] ゴールシークをクリック. 計算式が入ったセル C4 をアクティブセル C4 の計算結果を 00 にしたいという動機なので C4 をアクティブにしておきます (2) 最終目的値が算出される数式セルが [ 数式入力セル ] ですアクティブセルであった C4 が自動でセットされています [ 数式入力セル ] をいくつにしたいのかを入力する欄が [ 目標値 ] です 00 として下さい [ 変化させるセル ] は PC に実験入力をしてもらうセルを指定します C2 を指定して下さい設定後は OK します. [ 数式入力セル ] は C4 [ 目標値 ] は 00 に 2. [ 変化させるセル ] は C2 に 5

(3) すると PC が C4 の計算結果を 00 とするためには C2 にどんな値を入れればよいのかを試行錯誤により調べてくれます OK しましょう (4) C4 の計算結果を 00 にするためには C2

今度は右側の表を使ってゴールシークの研究をしますセル F6 の [ 合計金額 ] 欄を 9500 円とするには単価をいくらにすればよいのかを調べてもらいますなお [ 数量 ] は 8 としておきます

6 (3) すると PC が C4 の計算結果を 00 とするためには C2 にどんな値を入れればよいのかを試行錯誤により調べてくれます OK しましょう (4) C4 の計算結果を 00 にするためには C2 を 048 にすればよいということを PC が調べてくれました C4 の計算結果を 00 にするためには C2 を 048 にすればよいということを PC が調べてくれた 3 ゴールシーク検証 () 今度は右側の表を使ってゴールシークの研究をしますセル F6 の [ 合計金額 ] 欄を 9500 円とするには単価をいくらにすればよいのかを調べてもらいますなお [ 数量 ] は 8 としておきます F6 でゴールシークを使用して下さいなお入力実験の対象となる F2 には何が入力されていてもかまいませんし今回のように空白でもよいことに注目しておきましょう 2. データタブから [What-IF 分析 ] ゴールシークをクリック. セル F5 の [ 数量 ] を 8 にしてから計算式が入ったセル F6 をアクティブに (2) [ 数式入力セル ] は F6 であり [ 目標値 ] は 9500 とします PC に実験入力してもらうセル [ 変化させるセル ] は F2 です設定後は OK しましょう. [ 数式入力セル ] は F6 [ 目標値 ] は 9500 に 2. [ 変化させるセル ] は F2 に 6

(3) 調べ終わったら OK します (4) 8 個販売する場合セル F6 の計算結果を 9500 にするためには F2 を 3 にすればよいということを PC が調べてくれました完成後はこのファイルを閉じましょう計算式の結果を指定の値にするためには参照セルの値をいくつにしておけばよいかを調べてくれる機能がゴールシーク 4 まとめ計算式の結果を指定の値にするためには

7 (3) 調べ終わったら OK します (4) 8 個販売する場合セル F6 の計算結果を 9500 にするためには F2 を 3 にすればよいということを PC が調べてくれました完成後はこのファイルを閉じましょう計算式の結果を指定の値にするためには参照セルの値をいくつにしておけばよいかを調べてくれる機能がゴールシーク 4 まとめ計算式の結果を指定の値にするためには参照セルの値をいくつにしておけばよいかを調べてくれる機能がゴールシークですゴールシークを使わずに手入力を繰り返すことでも調べることができますただしこの方法では時間がかかりますゴールシークは計算式が使われているセルで使用します 5 練習問題 () 現在久保さんを除いた一人あたりの平均売上は下図の通りです久保さんの売上がいくらならば平均売上額が 4 万円になりますか? A B C D E F 2 営業担当今月の売上平均売上額 3 和田 \70,000 \35,833 4 秋川 \78,000 5 大野 \95,000 6 木野下 \58,000 7 久保 8 中村 \3,000 9 加藤 \0,000 0 ( 解 :65,000 円 ) (2) 明日は8 月 3 日です明日は何になれば一週間の平均気温が 30 を超えますか? A B C D E F 2 気温週間平均気温 3 8 月 25 日月 26 日月 27 日月 28 日月 29 日月 30 日月 3 日 0 ( 解 :33. ) 7

8 (3) ある商品を 85 個 340 円で仕入れました現在はこれを 430 円で売ろうとしています全部売れると仮定して計算したところこの価格だと 7,650 円の利益が出そうですでは 2,000 円の利益を出すには価格をいくらに設定すればよいでしょうか? A B C D 2 商品原価 \340 3 販売価格 \430 4 一個あたり利益 \90 5 販売数量 85 6 利益 \7,650 7 ( 解 :48 円 ) (4) ある商品を 65 個 450 円で仕入れましたお店でこの商品を売るのですが全部売れると仮定して 8,000 円の利益 ( 最終利益 ) を得るには価格をいくらに設定すればよいでしょうか? なお一旦算出した利益より 5% のテナント代金 ( 支出 ) が発生しますゴールシークの実行前に変化させるセルに仮の値を入力しておくとわかりやすくなります A B C D A B C D 2 商品原価 \450 3 販売価格 4 一個あたり利益 \ 販売数量 65 6 利益 \-29,250 7 手数料 (5%) \-4,388 8 最終利益 \-24, 商品原価 \450 3 販売価格 \700 4 一個あたり利益 \250 仮の数値 5 販売数量 65 を入れる 6 利益 \6,250 7 手数料 (5%) \2,438 8 最終利益 \3,83 9 ( 解 :776 円 ) (5) あなたは土地を 300 坪持っていますその土地を販売して 850 万円にしたいのですが坪単価はいくらにすれば良いのでしょうか? ゴールシークを使って求めてください ( 解 : 約円 ) (6) あなたは土地を 300 坪持っていますその土地を販売して 900 万円を手にしたいのですただし販売時に手数料として 8% 経費がかかります坪単価をいくらにすれば良いのでしょうか? ゴールシークを使って求めてください ( 解 : 約円 ) (7) リットル当たり単価が 8 円のガソリンスタンドにて 3000 円を使ってできるだけガソリンを入れるつもりですまたあなたはガソリンが 3% 引きになるカードを持っています何リットル入れることができますか?( 解 : 約 26.2 リットル ) (8) あなたは商品 5800 円の商品を 6 個 4300 円の商品を 7 個 6500 円の商品を 5 個仕入れました全て売った場合に円の利益を得るためには商品に何 % の利益を上乗せして販売すればよいのでしょうか?( 解 : 約 30.8%) (9) あなたは 4 日間でそれぞれ 2300kcal,2800kcal,2600kcal,2200kcal 摂取しました 5 日目に何 kcal 摂取すれば 5 日間の平均エネルギー摂取量が 2500kcal になるでしょうか?( 解 : 2600kcal) 8

$第 2 章ピボットテーブル準備以下のような表を作成しましょうこの表はリスト形式になっています一番上の行が項目名となっており情報レコードが下に展開している表の形式をリストと呼びました A B C D E F G H 2 商品販売日営業担当者販売区分商品種類販売台数売上金額 3 990/5/ 遠山訪問販売大型 25 \57,000 4 990/5/6 栗田店舗販売$

9 第 2 章ピボットテーブル準備以下のような表を作成しましょうこの表はリスト形式になっています一番上の行が項目名となっており情報レコードが下に展開している表の形式をリストと呼びました A B C D E F G H 2 商品販売日営業担当者販売区分商品種類販売台数売上金額 3 990/5/ 遠山訪問販売大型 25 \57, /5/6 栗田店舗販売小型 \2, /5/9 高橋店舗販売中型 9 \24, /5/24 井上訪問販売中型 \, /5/3 高橋通信販売小型 5 \0, /6/8 高橋通信販売中型 \3, /6/2 井上店舗販売特殊形状 7 \24, /6/23 栗田訪問販売大型 \2, /6/27 井上訪問販売大型 2 \3, /7/0 井上通信販売中型 \, /0/9 栗田店舗販売小型 8 \3, /0/24 栗田通信販売大型 5 \3, //25 井上店舗販売小型 \3, /2/5 遠山通信販売大型 23 \65, /2/9 栗田訪問販売大型 \, /2/2 井上店舗販売小型 \25, /2/20 高橋通信販売特殊形状 6 \24, /2/20 井上訪問販売小型 3 \5, /2/22 栗田店舗販売大型 \, /2/23 高橋店舗販売小型 24 \9, /2/23 高橋訪問販売大型 9 \42, /2/26 遠山店舗販売小型 9 \65, /3/8 井上通信販売中型 23 \63, /3/29 遠山店舗販売大型 7 \8, /3/30 栗田訪問販売小型 5 \34, /4/4 井上訪問販売中型 7 \23,

$このリストを様々な観点から分析するつもりですここで学習するのはピボットテーブルというリスト分析機能ですこれはリストを項目単位で集計する機能です各 [ 営業担当者 ][ 商品種類 ] の販売台数合計リストや各月ごとの売上金額合計リスト販売件数リストなどの形式で集計することができます販売区分集計通信販売 \8,400 店舗販売$ $\97,500 訪問販売 \72,900 総計 \55,800 3 ピボットテーブルの利用年商品販売日集計 990 年 5 月 \96,00 6 月 \33,300 7 月 \,500 0 月 \26,600 月 \3,400 2 月 \22,600 99 年 2 月 \28,500 3 月 \6,200 4 月 \23,600 総計$

10 2 ピボットテーブルとは? このリストを様々な観点から分析するつもりですここで学習するのはピボットテーブルというリスト分析機能ですこれはリストを項目単位で集計する機能です各 [ 営業担当者 ][ 商品種類 ] の販売台数合計リストや各月ごとの売上金額合計リスト販売件数リストなどの形式で集計することができます販売区分集計通信販売 \8,400 店舗販売 \97,500 訪問販売 \72,900 総計 \55,800 3 ピボットテーブルの利用年商品販売日集計 990 年 5 月 \96,00 6 月 \33,300 7 月 \,500 0 月 \26,600 月 \3,400 2 月 \22, 年 2 月 \28,500 3 月 \6,200 4 月 \23,600 総計 \55,800 () 今から各 [ 営業担当者 ] の販売台数合計リストを作成します誰が何台売ったのかがわかる表を作成するのですピボットテーブルで表しますリスト内のセルをひとつアクティブにした状態で挿入タブから [ ピボットテーブル ] ピボットテーブルをクリックします 2. 挿入タブから [ ピボットテーブル ] ピボットテーブルをクリック元の表を様々な角度から分析できる分析集計関係の命令をする際はリスト内のセルをひとつだけアクティブにしておくのがコツです. リスト内のセルをひとつアクティブに (2) 自動的に選択していたリストが分析対象範囲に指定されます分析結果ピボットテーブルは新しいワークシートに作成します以下の設定のまま OK して下さい 0

Microsoft Word - excel2003実践 doc

Microsoft Word - excel2003実践 doc Microsoft Office Excel2003 実践初歩からの PC テキスト第章ユーザー定義の表示形式... 5 準備... 5 2 表示形式で文字を追加... 5 3 余分な 0( ゼロ ) の表示... 7 4 文字列にデータを追加して表示する... 8 5 小数点以下の桁数の指定... 9 6 練習問題...0 7 分数パーセント... 8 練習問題...5 9 数値その他...5