セル内改行


Excelのセル内で改行をすることをセル内改行と言います。
WordやEditorで文字を入力した際、通常はEnterキーを押下することにより、
改行されることは皆さんご存知ですよね。

Excelの場合、Enterキーは確定キーのため、Enterキーを押下すると
通常は、アクティブなセルが1つ下のセルへと移動してしまいます。

セル内改行の方法は簡単!
まずは通常通り入力をします。

 e0201.jpg
この時、まだ日本語入力をしただけで、「う」の右側にカーソルがあることを確認します。

Enterキーを押下してしまうと、アクティブなセルは下部へ移動してしまいますので、
セル内改行を行いたい場合は、「Alt」キーを押下しながら、「Enter」キーを押下します。

e0202.jpg

すると、上の図のように、改行されカーソルは最初の文字の下に表示されます。
これがセル内改行です。

セル内改行されたセル
e0203.jpg
このとき、数式バーを確認してみると・・・

e0204.jpg
このように表示されています。
少し数式バーを広げてみてみましょう。

e0204-1.jpg
改行された行が隠れていたことがわかります。

このように、セルの中でも改行ができるのでみなさんも使われてみてはいかがでしょう。

次回は、このセル内改行されたデータをAccess等でインポートする際
一度にセル内改行を削除する方法を紹介します。


ピボットテーブルの集計方法を変更することができます。

1.データフィールド内の任意のセルをクリックします。
  メニュータブの【オプション】をクリックし、【アクティブなフィールド】内の
  【フィールドの設定】をクリックしましょう。

  e0198.jpg

2.下図の【値フィールドの設定】画面が表示されますので、
  「集計の方法」から、目的の集計方法をクリックし「OK」ボタンを押下します。
  この作業だけで、集計方法を変更することができます。

  e0199.jpg

 ※上記画面を表示する方法は、データフィールド内の任意のセルで、
   右クリック【値フィールドの設定】をクリック しても同様の設定が可能です。
   e0200.jpg


列と行のフィールド入れ替え

ピボットテーブルのレイアウトは、利用目的に応じ自由自在に変更することができます。
行や列のフィールドを入れ替えると、すぐに行列逆などの集計表に変更されます。

1.列フィールド(お得意先)を行フィールドへ移動してみましょう。
  「お得意先」をポイントし、マウスポインタの形状がe0190.jpg になったことを確認しながら
  行エリアへドラッグしましょう。
  e0191.jpg
  上図の場所でマウスボタンを離します。
  すると下図のように商品のアイテムごとにお得意先のアイテムが表示されます。
  (行フィールドが2つにかわります)
  e0192.jpg

2.行フィールド(商品名)を列フィールドに移動してみましょう。
  「商品名」をクリックしマウスポインタの形状がe0190.jpgになったことを確認しながら
  列フィールドへドラッグしましょう。
  e0193.jpg
  e0194.jpg
  e0195.jpg
  このように、簡単に行列を入れ替えることができます。

フィールドを入れ替える際に、余分なフィールドが出てきたら?

1.不要になったフィールドをピボットテーブルエリア範囲外へドラッグします。
  この時、ピボットテーブルのエリア外へマウスが移動した時のマウスポインタの
  形状はe0196.jpg このようになります。
  今回は、商品名をエリア外へ移動してみました。
  e0197.jpg
  上記のようにピボットテーブルエリア外でマウスポインタの形状がe0196.jpgの時に
  マウスボタンを離すことにより、不要なフィールドを削除することができます。


ピボットテーブルを作成してみましょう。
ここでは、Excel2007での作成方法になります。
Excel2003までの方はこちらで紹介をしております。(作成中)

※今回使用しているデータファイルが必要な方はこちらからダウンロードしてください。
  使用ファイル pivot.xls(56kb)



1.リスト内の任意のセルをクリックします。
  今回はB4を選択します。
  e0181.jpg

2.挿入タブ→テーブル内のピボットテーブル→ピボットテーブルをクリック
  e0182.jpg

3.データ範囲を確認し、全てのデータ範囲が選択されていることを確認し
  OKボタンを押下します。
  すべてのデータ範囲がムービングボーダー(点線)で囲まれていることを確認しましょう。
  e0183.jpg

4.新規シートに、下図の画面が表示されます。
  e0184.jpg

5.行エリアにフィールドを設定してみましょう。
  方法は2通りあります。
  画面右上の「フィールドリスト」内の「商品名」を「ここに行のフィールドをドラッグします」に
  ドラッグしましょう。
  e0185.jpg

  または、画面右上の「フィールドリスト」内の「商品名」を画面右側の下にある
  「行ラベル」にドラッグしましょう。
  e0186.jpg

6.どちらの方法でも、行フィールドを商品名に設定した場合は、下図のように表示されます。
  e0187.jpg
  ※赤枠で囲んだ部分をよく確認してみましょう。
    画面右側「フィールドリスト」の商品名の前のチェックボックスがオンになっている。
    画面右側「行フィールド」に「商品名が表示されている。
    画面左側の「行フィールド」内に、商品名が表示されている。

7.同様に、列エリアに「お得意先」を、データエリアには「金額」を設定しましょう。
  e0188.jpg
  これでピボットテーブルが作成されました。


データ更新するには…
 ピボットテーブルのデータは、リストのデータとリンクされているため、直接編集することは
できません。
 ピボットテーブルのデータを変更する場合は、リストのデータ(元になっているデータ)を変更し、
その後、ピボットテーブルシートに戻り、データ更新ボタンをクリックして更新します。
 e0189.jpg
 ※更新ボタンが格納されている「オプション」タブは、ピボットテーブル内をクリックした
   状態であればどのセルをクリックしても表示されます。



次回は、集計方法の変更・行と列のフィールド入れ替えです。


ピボットテーブルとは
 ピボット(pivot)は回転、テーブルはという意味です。
 簡単に言うと、目的に応じ自在にできるクロス集計表です。
 ピボットと言われているのは、フィールドを自由に入れ替えて集計表を組み替えることが出来るためです。
 対応するフィールドに応じ瞬時に対応する集計値が算出されるため、ユーザーの求めたいデータ分析が瞬時に可能になります。
 ピボットテーブルを作成するためには、リスト(Excelのデータベース)の準備が必要です。
 集計値はデフォルトでは合計値が算出されますが、目的に応じデータ個数、平均値など色々な集計値を算出することができます。
 リスト別のフィールドをページという領域に取り出し、3次元の集計表を作成することができます。
 ピボットテーブルのデータは、リストデータにリンクされていますので、ピボットテーブル内のデータは直接編集することはできません。
 変更をしようとすると「できない」旨の警告メッセージが表示されますので、変更が必要な場合は、もとのリストデータを変更します。

ピボットテーブルの用途
 売上明細、経皮明細や仕入れ明細、顧客管理情報、見積もり、契約物件、
 市場調査やアンケートデータ、社員名簿、成績評価修データ、用度品管理など、あらゆる部門のデータで数値を含む情報があればピボットテーブルを利用することができます。


次回は実際にピボットテーブルを作成する方法についてご紹介いたします。

Excel2003での方はこちらから (現在作成中)

複合グラフ


複合グラフ・・・
 異なるデータ系列を別途、数値軸を設け表示するものです。
 (主軸と第2軸を使って表示する複合グラフ)
 金額と比率、金額と数値、また同じ金額同士でもデータ系列間の数値が
大きく違う場合等に利用します。



下表には、商品ごとの売上高、構成比、累計構成比が表示されています。
この評を元に、商品別売上高のABC分析が出来る表に変更し、
商品別売上高のABC分析グラフを作成してみましょう。

e0164.jpg

1.売上高の大きい順に並べ替えをしましょう。
  セルB3:F20を範囲選択し、売上高を降順で並べ替えしましょう。
  ※この時、合計を算出している部分を含まずに選択します。

2.グラフにする範囲を選択しましょう。
  セルC4:D20とE4:F20を範囲選択します。
  e0167.jpg

  Excel2003までの方は
  1.グラフウィザードボタンを押下しましょう。
    表示されたグラフウィザードの「ユーザー設定」タブをクリックします。
    e0168.jpg
   グラフの種類から「2軸上の折れ線と縦棒」を選択して「次へ」ボタンを押下します。
 2.データ範囲を確認しましょう。
   e0169.jpg
   プレビューに複合グラフ(縦棒と折れ線グラフ)が表示されていますか?
   系列は「列」単位に取り込まれていますか?
   データ範囲は選択した通りになっていますか?
   確認が完了したら「次へ」ボタンを押下します。

 3.タイトルや凡例、目盛り線の位置を設定しましょう。
   タイトルは「商品別ABC分析」としましょう。
   凡例は下に表示してみましょう。
   e0171.jpg

 4.グラフが作成されました。
  e0172.jpg


 Excel2007の方は
 1.挿入タブ→グラフ→縦棒→集合縦棒を選択します。
   e0173.jpg

 2.まずは、売上高、累計構成比の縦棒グラフが作成されます。
   e0174.jpg
 3.累計構成比を選択します。
   e0175.jpg
  選択した累計構成比の上で右クリックをし、「データ系列の書式設定」を選択します。
  e0176.jpg
 データ系列の書式設定の使用する軸2軸(上/右側)にチェックをします。
 e0177.jpg
 閉じるボタンを押下し、データ系列の書式設定を閉じます。
 すると、累計構成比も縦棒グラフで表示されました。
 e0178.jpg

4.累計構成比が選択されたままになっています。
  選択されたままの状態で、デザインタブ→グラフの種類の変更→
  折れ線グラフのマーカー付き折れ線を選択します。
  e0179.jpg
  選択が完了したらOKボタンを押下しグラフ作成終了です。
  e0180.jpg

ABC分析
 上位集中度分析手法のことです。
 今回は、商品の売上高がどのくらい上位に集中しているかを累計構成比でみます。
 累計構成比が約70%までの商品をAクラス、95%までのクラスをBクラス、それ以外を
 Cクラスといいます。

Zチャート作成

 

Zチャートとは・・・

 毎月の販売高推移を、1)月毎の販売高
           2)累積販売高
           3)月毎の移動年計
 
 以上の3つを線グラフで表示したものです。
 ちょうど、アルファベットのZという文字に似たグラフとなるので、
 Zチャートと呼ばれています。

 ※月毎の移動年計(12カ月移動和ともいう)の計算式は
  =前月の移動年計+当月販売高−前年の当月販売高 で求めます。


下図の月別販売高推移表を使ってZチャートを作成してみましょう。
e0161.jpg

1.セルG5に移動年計を求める数式を入力しましょう。
  =D16+E5−C5
  と、入力をし、まず1月の移動年計を計算しましょう。

2.次にセルG6に2月以降の移動年計を求める数式を入力しましょう。
  =G5+E6−C6
  と入力をしたら、G6の数式をG16までコピーしましょう。
  e0162.jpg

3.上記の表が作成され、Zチャートを作成する準備が整いました。
  では、折れ線グラフを作成してみましょう。
  グラフにするセル範囲である B4:B16とE4:G16を選択しましょう。

4.選択が完了したら、グラフを挿入します。
  グラフの種類は「折れ線」を選択し、形式は「データマーカー付き」を選択、
  グラフタイトルは「月別売上高推移Zチャート」とし、新しいシートに
  グラフを作成しましょう。

  下のグラフが作成されましたか?
  e0163.jpg
  12月の移動年計と累計販売高が同じ値になり、アルファベットのZのような
  かたちになることに注目をしましょう。
  
  移動年計の折れ線は、月毎の年間販売高の推移を表します。
  そのため、移動年計が
  1)右上がりの時
    販売高は前年度に比べて上昇傾向
  2)横ばいの時
    販売高も横ばい
  3)右下がりの時
    販売高は下降傾向
  このように、読み取ることができます。



前回は元データを利用し、グラフ内に移動平均を表示しました。
今回は、移動平均を計算して表示させてみましょう。

移動平均の計算式は
 当月を含む過去一定期間の数値の平均です。

e0155.jpg
1.前回使用した表に3ヶ月移動平均
  5ヶ月移動平均のフィールドを
  作成しましょう。














e0156.jpg
 2.セルD6に計算式を入力しましょう。
   「=AVERAGE(C4:C6)」と入力します。
   数式をD15までコピーしましょう。
   ※今回数式はAVERAGE関数を用いましたが、
     「=SUM(C4:C6)/3」としても同じ結果を
     えることができます。










e0157.jpg

3.同様にセルE8に5ヶ月の移動平均を
  求め、セルE15まで数式を
  コピーしましょう。
 

 ※移動平均のみのグラフを作成する場合は、
   グラフの作成範囲を、B3:B15,D3:E15とし、
   折れ線グラフを選択すればOKです








分析ツールを使用して移動平均の値を求める

 上記方法は、AVERAGE関数を用いて移動平均を求めましたが、Excelのアドイン機能である
分析ツールを使用して移動平均を求めることができます。
 ※メニューバーのツールメニューの中に「分析ツール」が表示されていない場合は、
   メニュー→アドイン→分析ツールの前のチェックをつけて利用できるようにしましょう。
   Excel2007の場合は、
   画面左上のOfficeボタン→Excelのオプション→画面左側の「アドイン」をクリック→
   画面右側の下にあるExcelアドイン右側の「設定」ボタンをクリックし、
   「分析ツール」の前にあるチェックをつけましょう。

1.月度売上高が入力されているシートを開きましょう。
  売上高の右側に3ヶ月と5ヶ月の移動平均を表示させるフィールドを作成しておきましょう。

2.メニューバーのツール→分析ツールを選択し、「データ分析」ダイアログボックスを
  開きましょう。
  ※Excel2007では、データタブ→分析→データ分析でダイアログボックスが
    開いてきます。

  e0158.jpg
  上記データ分析のダイアログボックスの中から、「移動平均」を選択しOKボタンを
  押下します。

3.移動平均ダイアログボックスでは、3ヶ月の移動平均を表示する場合には下図のように設定をします。

  e0159.jpg
  ※入力範囲は、フィールド名称を含まず、売上高のセル全てを範囲選択します
  ※区間は、ここでは3ヶ月の移動平均を求めるので「3」を入力します
    (5ヶ月の場合は、「5」を入力します)
  ※出力先は、ここでは3ヶ月の移動平均を表示したいのでD4を選択します
    (5ヶ月の場合は、「E5」を選択します)
    出力先は値を出力する範囲の先頭のセルを必ず選択しましょう

4.3ヶ月の移動平均は、1月と2月は、求める式の範囲にデータがないので
  エラー値(#N/A)が表示されます。
  3月以降の値はAVERAGE関数で求められています。
  e0160.jpg



移動平均グラフを作成すると、データの傾向が平均化され傾向を読み取りやすくすることができます。
Excelのグラフ機能の中にある近似曲線を利用し移動平均のグラフを作成することができます。

e0148.jpg
1.左図の月別売上高のデータから、
  3カ月移動平均と5カ月移動平均のグラフを
  作成してみましょう。


















e0149.jpg
2.グラフを作成する範囲(B3:C15)を
  範囲選択しましょう。

3.グラフウィザードを起動しましょう。

4.グラフの種類:縦棒
  グラフの形式:集合縦棒
  上記2点を確認し、次へ進みましょう。

5.グラフタイトルは
  「月別売上高と移動平均」としましょう。
  グラフの作成場所は「新しいシート」と
  してみましょう。
  (同一シート内でも作成することが
   可能です)

6.次に近似曲線をグラフ内に追加して
  みましょう。
  データ系列(どの棒でも構いません)を
                              右クリックしてましょう。
  
e0150.jpg
  7.左図のメニューが表示されますので、
    近似曲線の追加をクリックしましょう。

e0150-1.jpg










上の図はExcel2007のメニュー        上の図はExcel2003までのメニュー


e0152.jpg
8.左図の近似曲線の書式設定が
  表示されます。
  (左図はExcel2007の画像)















e0152-1.jpg
8−1.左図の近似曲線の追加画面は
    Excel2003までの画像です。















9.「種類」タブ(Excel2007では近似曲線のオプション)であることを確認しましょう。
  移動平均アイコンをクリックし、区間を3に変更しましょう。
  変更が完了したらOKボタン(Excel2007では閉じるボタン)をクリックしましょう。
  グラフ内に移動平均のグラフが折れ線グラフで表示されたことを確認しましょう。
  
  確認が出来たら、同様に任意のデータ系列を右クリックし、移動平均を選択後
  区間を5に変更して5カ月の移動平均を表示しましょう。
  下図のように移動平均の折れ線グラフが表示されたことを確認しましょう。
  折れ線グラフの色の変更は通常の変更方法と同様です。

  e0154.jpg

次回は、元データを表示せず、移動平均のみのグラフを表示する方法をご紹介いたします。


データテーブルを作成してみましょう。
データテーブルを利用すると、住宅ローンの借入で異なる利率に対応する
毎月の返済額を一覧表に作成し、最適値を検討することができます。

データテーブルとは
 数式に含まれる変数に種々の値を代入し、計算される結果を
 表形式に出力するものです。


今回の例題は、「利率・返済期間・借入額から月返済額」で学んだ
PMT関数により月末返済額が算出されていますので、以下にあげる
利率に対応する月返済額をデータテーブルを使用し表示していきましょう。

e0146.jpg

1.セルC2には
  =PMT(F2/12,F3*12,−F4)
  上記の計算式が入力されています。

2.B4〜B10までの利率をデータテーブルを利用し表示していきましょう。
  B2:C9を範囲選択しましょう。
  
  Excel2003までの方は・・・
  メニューバー → データ → テーブル をクリックしましょう。
  「テーブル」ダイアログボックスが表示されます。

  Excel2007の方は・・・
  データタブ → データツール → What-If分析 → データテーブルをクリックしましょう。
  「テーブル」ダイアログボックスが表示されます。

3.式の代入セルを指定しましょう。
  ここでは、「列の代入セル」に利率が表示されているF2クリックして指定しましょう。
  選択範囲の利率がセルC3の式が参照しているF2に代入され計算されます。

4.セルC3:C10に計算結果が表示されます。
  e0147.jpg



2011年10月

            1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31