タグ:Excelワザ ( 162 ) タグの人気記事

Excelワザ_Excel資料作成術 グラフ編

Excelには、様々なグラフが簡単に作成できます。しかし、伝えたい内容を最も適切なグラフで視覚的に表現するためには、少しコツが必要です。数値の意味がより明確になり、伝えたい内容の説得力が増すグラフを作成しましょう。

伝えたい内容に応じてグラフを選ぶ!
3大基本グラフ
①棒グラフ   →数量:要素の大きさや量を示したい
②折れ線グラフ→推移:量や数値の推移を示したい
③円グラフ   →割合:全体における割合を示したい
そのほかのグラフ
①面積図:多数の要素における割合を示したい
②面グラフ:時間軸に沿った総量の変化を示したい
③ヒストグラム:量のバラつきを示したい
④散布図:要素同士の関係性を示したい
⑤レーダーチャート:標準値に対する差異を複数観点で示したい
⑥エラーバーグラフ:平均だけでなく最大値や最小値を示したい

まずは、3大基本グラフ
①棒グラフ→数量:要素の大きさや量を示したい
<例>店舗別商品分類別の売上一覧をグラフにする
b0186959_1456584.jpg

・セル範囲「A3:F8」を選択し、
①集合縦棒グラフにすると…
b0186959_1456251.jpg

  店舗別の売上高が表示されたが、
  5本も縦棒が並ぶと見た目が煩雑になりデータの意味がわかりづらい。 
②そこで、これを積み上げ縦棒グラフに変更すると…
b0186959_15181799.jpg

  各店舗の全体の売上も商品分類の構成もわかりやすくなった。
  さらにもう一段アップ!
  系列の棒の太さを太くし、区分線を入れると…
b0186959_1504358.jpg

  力強さと比べやすさが格段にアップした!
③これを100%積み上げ縦棒グラフにすると、
b0186959_1513215.jpg

店舗ごとに各商品分類が売上全体のどの位の割合を占めているか、を示すことができる。
縦軸が金額からパーセンテージになり、売上金額とは無関係に、各商品分類の構成比を比較できる。

横棒グラフについて
次にこのように項目の文字数が多い場合
b0186959_152412.jpg

集合縦棒にすると、
b0186959_1521664.jpg

項目名が斜めに表示され、棒グラフのスペースが狭くなる。
一般に、グラフは縦よりも横の幅が長くなることが多いため、
このような場合は、横棒グラフにすることで、収まりの良いグラフが作成できる。
b0186959_1531990.jpg

項目名が長いグラフは、横棒グラフにするのが見栄えUPですね!

②折れ線グラフ→推移:量や数値の推移を示したい
<例>5年ごとの出荷量の推移を見たいとき…
b0186959_1545835.jpg

集合縦棒グラフだと…
b0186959_1551216.jpg

見た目が煩雑になり、
    それぞれの野菜の出荷量が独立した印象になり、
    時系列の推移がわかりづらい。
 そこで、時系列を表すのに最適なのが、折れ線グラフ!
b0186959_156960.jpg

それぞれの野菜の出荷量がひとつながりのデータとして示され、
    全体を通して各野菜がどの程度の水準かが一目でわかります。
時系列の推移は折れ線グラフで!

③円グラフ→割合:全体における割合を示したい
商品分類ごとに売上高を表示したい。集合縦棒グラフなら…
b0186959_1571236.jpg

  売上高全体の中で、それぞれの分類の占める割合を見たいとき、
  縦棒グラフではわかりにくい。
  そんな時は、円グラフを使う。
b0186959_1575390.jpg

  全体の中での割合が一目瞭然!
横帯グラフもOKです。
b0186959_1583883.jpg


その他、散布図、面グラフ、レーダーチャート等ありますが、またの機会に…

データを表現するのに、最適なグラフを選びましょう!
[PR]

by school-pc | 2014-12-03 15:10 | Comments(0)

Excelワザ_「株式会社」などを取り去る

雑誌からのネタ
並べ替えたりするとき、
会社名などに、
「株式会社」や「有限会社」や
「社団法人」等々…
があると、並べ替えがうまくいかない…
「株式会社…」が続いたり…
b0186959_21235247.jpg

そこで、「株式会社」を一発で取り去るワザ
チト難しい…
配列数式を使います。
「Shift」と「Ctrl」押しながら「Enter」
を押す配列方式!

1.「会社形態」を
 名前定義しておく。
 セル範囲「D2:D7」に「会社形態」と定義。
2.セル「B2」に式
 「=INDEX(SUBSTITUTE(PHONETIC(A2),会社形態,""),MATCH(MIN(LEN(SUBSTITUTE(PHONETIC(A2),会社形態,""))),LEN(SUBSTITUTE(PHONETIC(A2),会社形態,"")),0))
3.式が立ったら
「Shift」と「Ctrl」押しながら「Enter」
を押す
b0186959_21263252.jpg

式の前と後ろに「{ }」中括弧が付きます。
これを、昇順に並べ替えると、
b0186959_2117287.jpg

うまく並べ替えられました。

SUMIFSやCOUNTIFSなど「S」
の付く関数ができたことによって、
配列数式、もう活用することはないかと
思いきや、まだまだ、活用法があるか~~~(゜.゜)
[PR]

by school-pc | 2014-11-26 21:19 | Comments(0)

Excelワザ_重複の削除は便利(^_-)-☆

「重複の削除」
リストの中で、重複するレコード(行)
を削除する機能。
で、これを使うと、
「担当者」だけ、「取引先」だけ
などを取り出すことができます。
例えば、売上表があって、
担当者の人数だけ、
何人か調べたいとき
ピボットテーブルを使うと、出ますが、
単純に担当者を抜き出したいとき、
「重複の削除」を使うと便利です。
こんな売上表から
b0186959_8541563.jpg

担当者は何人かなぁ…
1.まず担当者の列をコピーして、
 新しいシートに貼り付け、
b0186959_9413726.jpg

2.貼り付けた状態から
 データ→「重複の削除」をクリック。
b0186959_9425212.jpg

3.こんなメッセージが出て
b0186959_9435847.jpg

4.結果、102人の中から
 担当者は「9人」でした~~(^^♪
b0186959_9474057.jpg

同様に、取引先は
b0186959_950251.jpg

3件か~~。
ちょっと、便利に使えるんじゃない(^_-)-☆
[PR]

by school-pc | 2014-10-23 09:46 | Comments(0)

Excelワザ_一度に名前の定義が出来る

そうか、こんな手があったか~~
今更ですが^_^;
一度に複数の名前の定義が出来るワザを紹介。

1.名前の定義をしたい複数列の範囲を選択。
b0186959_19383719.jpg

2.数式タブ→定義された名前
 →選択範囲から作成
b0186959_19411331.jpg

3.以下に含まれる値から名前を作成
 →上端行にチェック→OK
b0186959_19414224.jpg

4.名前の管理をクリックして確認
→4つの範囲が名前定義されています!
b0186959_19432789.jpg

一度に複数の名前定義したいとき
便利かも(^_-)-☆
[PR]

by school-pc | 2014-07-23 23:36 | Comments(0)

Excelワザ_フラッシュフィルでできない場合は…

2013から便利な機能「フラッシュフィル」
2014-6-20の記事

氏名に分けるだけならいいが、
こんな場合、
会社名、支店名、部署名
に分けたいとき、
支店や、部署がない
空白の場合は、
フラッシュフィルでは
うまく取り出すことができません。
b0186959_1432206.jpg


そこで、Excelには、
良い機能があります。
「区切り位置指定ウィザード」
1.区切りたいデータを選択、
2.データ→区切り位置
3.「カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」
  にチェック→次へ
b0186959_14535828.jpg

4.「スペース」にチェック→次へ
b0186959_21162177.jpg

5.列のデータ形式「G標準」.
 表示先を表示したいセルを選択→完了
b0186959_14542698.jpg

6.「既にデータがありますが、置き換えますか?」
  と表示されるが、「OK」
b0186959_14501065.jpg

b0186959_1447414.jpg

データのないところは、空白になってますねぇ~
Goodな機能です。
[PR]

by school-pc | 2014-07-21 14:48 | Comments(0)

Excelワザ_複数データの取り出し

かなり、苦戦したので、備忘録
生徒さんからの質問で…
「日々入力するデータを1月ごとに、
事業所別に、該当するデータを集計したい!
ダイレクトに集計表に表示したい!」
マクロを使わずに…
フィルタも使わずに…
ピボットテーブルも使わずに…

簡単ではなかった…(^_^;)
ネットでググると、
同じような事をしたい人がかなりいて、
解答事例もたくさん有り。
その中に「imogasi方式」なるものを発見。
imogasi氏曰く
>「もう回答は出尽くしていますが、
いつも気になることを書きます。
この種の質問は週に3度ぐらい出ます。
タイプは「条件付き抜き出し問題」です。
しかし関数でやるのは、
1セル当たりでも非常に長い式で、
関数の5式以上の組み合わせになる式になります。
エクセルには検索に適した関数が作られてないことが原因です。
初心者には、自分で生み出せる式でなく、
式の意味もわからないと思う。
コピペして出来ましたと、それで良い人は使ったら良い。
しかし質問者の場合への修正も一苦労。
解法のタイプは
(1)A 作業列を使う
   B 使わない
(2)A 配列数式を使う
   B 使わない(通常の式)
(3)A ユーザー定義関数を使うー(一種のVBA)
   B 使わない。
(4)フィルタ
ですから、私は
(1)操作又は、フィルタ
を使うことをすすめます。
(2)VBA
(3)特別のソフト(フリーソフト)
(ただし見つけるのが難しい)
を考える人もあるでしょう。
私は長年「imogasi方式」と
いうのを書いてきました。
上記で言うと(1)のAにあたります
Googleで「imogasi方式」で照会すれば、
沢山の質問回答の例が出て、
私以外の、タイプの回答も毎回出ています。……」


ということが書いてあり、
色々ほかのやり方を試してみたが、…
データ量が増えると上手くいかなかったり…
かなり苦戦した(・へ・)

で、結局、
「imogasi方式の作業列を使ったやり方」
で作ることにした。

例題)
日々のデータを入力しているシートから
該当する講師ごとに、
レッスン日、コース名、レッスン名、時間の行
を取り出し、1か月分のレッスン時間,講師料を集計する。
講師がやめたり、入ったり、講師数の変動あり。
<入力データシート>A列~G列まで7列
b0186959_11474027.jpg

<講師別集計シート>
b0186959_1385890.jpg


1.準備として、
 ★範囲名に名前の定義
 範囲名を4つ作ります。
 まず、<講師名簿シート>
b0186959_12571647.jpg

 ここで、Excelワザ!
 講師の増減がある場合、
 いちいち講師名リストを選択するのは、面倒なので
 OFFSET関数を使います。
=OFFSET(基準セル、移動する行数、高さ(行数)、幅(列数)
=OFFSET(講師名簿!$B$2,0,0,COUNTA(講師名簿!$B:$B)-1,1)
b0186959_1343416.jpg
 
 講師名の入っているB2を基準セルとし、
 移動する行、列は、「0」
 高さはCOUNTA($B:$B)を指定します。
 これは、B列に何か値が入力されたセルの数を数え、
 見出し行分を含めないように-1引きます。
 幅は、講師列だけなので、「1」を指定します。
同様に、入力データシートの
 範囲名:講師名
 =OFFSET(入力データ!$D$2,0,0,COUNTA(入力データ!$D:$D)-1,1)
 範囲名:入力データ
 =OFFSET(入力データ!$A$2,0,0,COUNTA(入力データ!$A:$A)-1,7)
 範囲名:作業列
 =OFFSET(入力データ!$H$2,0,0,COUNTA(入力データ!$H:$H)-1,1)
これで、4つの範囲名作成。
2.講師別集計表のC1に
 リストから講師名が入るように入力規則を設定。
b0186959_13342317.jpg

 すると、リストから選択できるようになります。
b0186959_13525950.jpg

3.入力データシートの作業列,H2に
 =IF(D2=講師別集計!$C$1,COUNTIF($D$2:D2,講師別集計!$C$1),"")
 (条件と一致した行に連番を入れる式です。
  D2の講師名と講師別集計表のC2の講師名が同じなら、
  D2から数えて、何個目かを表示します。同じでないなら、空白)
 この式を下方向へコピーしておきます。
 すると、一致する講師の作業列に1,2,3、
 と連番が振られます。
b0186959_13505550.jpg

この連番を利用して、
4.講師別集計シートの
 セルB5~F5に式入力
(右方向に式をコピーし、
  INDEX関数の中の列番号を修正)
b0186959_14425845.jpg

6.B列は「m/d」(日付)に
 F列は「h:mm」(時刻)表示にする。
 時間の合計欄は「[h]:mm」にしておく。
出ました~~~!
b0186959_1422393.jpg


 
  
[PR]

by school-pc | 2014-07-07 11:48 | Comments(0)

Excelワザ_フラッシュフィル

Excel2013から追加された機能
「フラッシュフィル」
データベースとして
エクセルの表を使いたいとき
なかなか便利です。
例)
氏名を「姓」と「名」に分けたいとき
姓、名の列に1人分だけ入力、
b0186959_17193033.jpg

データ→フラッシュフィル
とするだけで、すべてのセルに入ってくれます。
b0186959_17214143.jpg

素晴らしい!!

で、これを2010までのバージョンで
するなら……
やはり、関数でしなければなりませんねぇ…
「姓」を取り出すセルには…
=LEFT(B3,FIND(" ",B3,1)-1)
(姓名の入っているセルの
左から(LEFT関数)
空白部分まで(Find関数で空白を見つけ)
空白分1文字引いた数
の文字数を取り出す。)
「名」を取り出すセルには…
=RIGHT(B3,LEN(B3)-FIND(" ",B3,1))
(姓名の入っているセルの
右から(Right関数)
全文字数(Len関数)から
空白までの文字数(Find関数)を引いた
数の文字数を取り出す。)
で、出来ます。
b0186959_1854225.jpg


バージョンによって、出来ることと
出来ないことがありまして…
良くなった点や、悪くなった点が
色々でてくるし…
そうそう、グラフも……続く…
[PR]

by school-pc | 2014-06-20 17:18 | Comments(0)

Excelワザ_円形のレーダーチャート

あまり使わないかもですが…
レーダーチャートのグラフは
ふつうはこんな感じ…
b0186959_1784760.jpg

これに、ドーナツグラフを
付け加えることにより
円形のレーダーチャートになる。
b0186959_17115150.jpg

[PR]

by school-pc | 2014-06-18 17:08 | Comments(0)

多角形-2

レーダーチャートを使うと
任意の多角形が出来ました。
(6月12日記事)

ちなみに…
360角形にすると…
ほぼ…円…(^_^;)
b0186959_1705495.jpg

[PR]

by school-pc | 2014-06-17 23:57 | Comments(0)

Excelワザ_基本図形にない多角形の作り方

オートシェイプの基本図形の中には、
四角形、五角形、六角形、
七角形、八角形、
十角形、十二角形
はあります。
b0186959_1733379.jpg

で、九角形はない。
十一角形もない。

では、Excelで作りましょう!
まず九角形
EXCELのワークシートに
1.セルA1~A9に数値「1」
 を入れます。
b0186959_1782833.jpg

2.A1~A9を選択し、
 挿入→グラフ→レーダー→塗りつぶしレーダー
b0186959_1726556.jpg

3.系列1と値軸と項目ラベルを削除。
4.すると、頂点が9つある
 九角形ができました!
b0186959_17345582.jpg

色はお好みで、
グラデーションもできます(^_-)
グラフエリア、プロットエリアの
塗りつぶしをなしにすると、
九角形だけのように見えます。
b0186959_17413074.jpg

あとは、同じ要領で
十一角形は二行分増やして、
こんな感じ
b0186959_17433079.jpg

[PR]

by school-pc | 2014-06-12 17:26 | Comments(0)