月別アーカイブ: 2017年5月

フィルター SUBTOTAL関数

フィルターを使ってデータを抽出したときに抽出されて行についてのみ集計してみます。

41行目セルE41にオートサムで合計を表示してみます。

オートサムで合計が表示されました。

次にこの表にフィルタを設定します。表の中をクリックしておきます。ホームタブの編集の並べ替えとフィルターからフィルターをクリックします。

店名で本店だけを表示します。店名の右のドロップダウンのボタンをクリックします。本店だけにチェックを入れます。

なぜか空白セルなるものがありますが、合計のセルのある最下行(41行目)もフィルターが有効になっています。本店だけ表示されましたが、本店だけの売り上げの合計が欲しいです。このまま最下行である42行目に本店だけの合計を表示する式を入力してもいいのですが、フィルターを解除したときに合計の行が2行になってしまいます。

いったん、フィルターを終了して合計のセルを削除します。再度フィルターを設定し、本店だけ表示します。

今回は空白のセルの選択肢がありません。この状態で最下行に合計を表示させてみます。セルE41を選択し、オートサムのボタンをクリックします。

数式バーを見ていただくと=sum()ではなく=SUBTOTAL(9,E2:E40)当数式が入力されています。とりあえずエンターキーを叩いて確定しておきます。

フィルターで店名を変更しても表示されている売り上げの合計だけが計算されているのが分かります。

もちろんフィルターを解除してすべてのデータを表示した状態でもきちんとすべてを合計してくれます。

フィルタをかけた状態でオートサムのボタンをクリックすると自動でSUBTOTAL関数の合計が選択されますので手入力する必要はありません。

ちなみにあまり使わないかもしれませんが、SUBTOTAL関数の第1引数を変更すると表示されている行数を表示してくれたりもします。

以下は少し複雑なことになっていますので飛ばしていただいても構わないと思います。

まず第1引数の一覧

1   AVERAGE
2   COUNT
3   COUNTA
4   MAX
5   MIN
6   PRODUCT
7   STDEV
8   STDEVP
9   SUM
10  VAR
11  VARP

第1引数を1とすればフィルタにかけられた数値の平均になります。9がsumなので合計ですね。

SUBTOTAL関数ふるまいですが、通常(第1引数の書き方による)ではフィルターに対して非表示の行は計算対象になりませんが、手動で行を非表示にした場合(フィルターによらない非表示行)は計算対象になります。

手動で非表示にした行も計算対象から外したい場合は第1引数に100を足した数値を設定します。

101  AVERAGE
102  COUNT
103  COUNTA
104  MAX
105  MIN
106  PRODUCT
107  STDEV
108  STDEVP
109  SUM
110  VAR
111  VARP

SUBTOTAL関数の第1引数を109にするとフィルタで除外されている行と手動で非表示にされている行は計算対象にはなりません。

詳しくはMicrosoftのSUBTOTAL 関数を参照してみてください。

フィルター (データの抽出)

フィルターをかける

エクセルは表計算ソフトですが、グラフを作成したり、データベースのようにデータを並べ替えたり抽出したりすることが可能です。

フィルターをかけるとデータをいろいろな方法で抽出することが可能です。

以下のような住所録があります。この住所録から女性だけの住所録を作成する場合、一行一行女性であることを性別の列を目視で確認してコピーして別のシートに張り付けるような面倒くさいことはしませんよね。

性別の列で並べ替えをして女性のひと塊をコピーして別のシートに張り付けたりしてますか。全然いいのですが、そうなると電話番号の変更があったり、住所変更があったりすると2つのシートのデータを変更しないとおかしなことになってきます。

実は、これはよくある現象で、社内で見ているデータが違うというようなことが起こってきます。フィルターを使えば新たにシートにコピーするわけではないのでこのようなことは起きにくくなります。

フィルターを設定します。表の中の任意のセルを選択します。ホームタブの編集の並べ替えとフィルターをクリックし、フィルターをクリックします。

各列の見出しの右にドロップダウンのボタンが表示されます。

性別の右のドロップダウンのボタンをクリックします。今回情勢だけの名簿が欲しいので男のチェックをクリックしチェックを外した状態にしてOKボタンをクリックします。

これで女性だけの名簿になりました。

女性は8名だけですが、行番号を見ていただくと8番目の方は42行になっています。これはフィルターに引っかからなかった男の分の行が折りたたまれて非表示になっている状態です。男の名簿が消えてなくなったわけではありません。

名簿にフィルターをかけて女性だけになっている状態から男性だけの名簿にする場合は再度性別の見出しの右のドロップダウンのボタンをクリックして今度は男だけがチェックの入っている状態でOKボタンをクリックします。

元の男女とも表示したい場合はすべて選択を何度かクリックして男女ともにチェックが入っている状態にしてOKボタンをクリックするか、ホームタブの編集の並べ替えとフィルターをクリックし、クリアをクリックします。そうすると男女ともに表示されるようになります。

さらに、フィルター自体を終了する場合は再度ホームタブの編集の並べ替えとフィルターをクリックし、フィルターをクリックします。これでフィルターは自動で解除されて、ドロップダウンのボタンも消えて元の通りになります。

棒グラフ 人口ピラミッド

棒グラフをいろいろと変更して人口ピラミッドと呼ばれるグラフを作成してみます。

まず元データとなる表を作成します。表を選択し横棒グラフを作成します。

表示の仕方だけの問題ですので必須ではありませんが元の表が10から19才が上になっているのに横棒グラフは90から99才が上になっていますので縦軸を反転させます。

軸の書式設定の軸のオプションの軸位置の軸を反転するにチェックを入れます。

 

横軸(数値軸)を選択します。軸の書式設定の軸のオプションの最小値を0から-60に変更します。現状の最大値をそのままマイナス値に変更するとちょうど半分の位置から横棒が描かれます。

次に女性のグラフを反転させます。まず女性のグラフの系列を選択し、データ系列の書式設定の系列のオプションの使用する軸から第2軸を選択します。

女性のグラフがまたもとの位置に戻ってきました。女性の横軸が下に追加されました。この軸を選択し軸を反転させることで棒の伸びる方向を反転させます。

男性の時と同じように最小値を-60に設定します。

これで大まかなところは出来ました。縦軸(項目軸)がグラフと被って読みにくいので移動させます。縦軸を選択します。軸の書式設定の軸のオプションのラベルのラベルの位置のプルダウンから下端/左端を選択します。

横軸が上下1つずつあるので今回は上にある第1横軸を非表示にします。

下の横軸は右側がマイナスになっていますので表示形式でマイナスの場合につくマイナスの記号をつかないように変更します。横軸を選択します。軸の書式設定の軸のオプションの表示形式の表示形式コードを#,##0;#,##0に打ち直し追加のボタンをクリックします。表示形式 (数値)を参照してください。

 

レーダーチャート

元々グラフは数値データの視覚化に使われますが、レーダーチャートは属性の比較などによく利用されます。ゲームキャラクタの特徴などでよく見ますね。

今回5科目のテスト成績からレーダーチャートを作成します。この時に元データの表を少しだけ工夫しておくとグラフにした時に特徴が分かりよいです。

5科目のうち理系の数学と理科を隣り合わせにします。文系の国語、社会、英語を並べます。そうすると下図の青色であらわされている田中さんは理系が強く文系が少し弱いといった特徴が読み取れます。灰色の山本さんは文系の国語、社会、英語は3名のうちトップですが、理系が極端に弱いようです。オレンジの吉田さんはトップの科目はありませんが、まんべんなく点数を獲得しています。このようにレーダーチャートの場合は、元も表の並び順が意味を持つ場合がありますので、グラフ作成の技術よりもどのような物を視覚化したいかが重要かもしれません。

作り方は元の表を選択し、挿入タブのグラフの等高線グラフまたはレーダーチャートの挿入をクリックして適切なグラフ種類を選択するだけです。

各頂点にマーカがないか、あるか、面を塗りつぶしかの3種類のレーダーチャートがあります。

レーダーチャートの目盛り線(同心円状の五角形の線)の色がちょっと薄いのと頂点から中心に向かう線が引かれていないのでちょっとわかりづらいかもしれません。

目盛り線をクリックして選択し、更に右クリックして目盛り線の書式設定をクリックします。

塗りつぶしと線の線から線(単色)を選択し輪郭の色のプルダウンから任意の色を選択します。

問題は各科目の頂点から中心へ向かう直線です。エクセル2013と2016は従来の方法ではうまく引けません。軸の書式設定の線で線(単色)を選択し輪郭の色のプルダウンで設定できるはずなのですが。選択した色が反映されません。

エクセルの2010では同じ手順で表示されます。そもそも作成した時点から表示されています。(画像ではちょっと薄くて見えにくいですが)

ネットで解決策を提示されておられるところがありました。オリジナルはどこかわかりませんが、調べた範囲内で初出かなと思われるサイトはこちらでした。
http://ohtsuka.cocolog-nifty.com/blog/2014/02/exel2013-e21b.html

とりあえず先ほどの手順で何らかの色を設定しておきます。その後グラフツールのデザインの種類のグラフの種類の変更から違うグラフに変更します。折れ線でも棒グラフでも違うレーダーチャートでも構いません。そしてさらに元のレーダーチャートに種類を変更して戻します。

そうすると線が表示されるようになります。

あとはVBAを利用して線を表示する方法もありました。http://www4.synapse.ne.jp/yone/excel2013/excel2013_graph_radarchart.html明示的にレーダーチャートのグラフであると指定するようです。

レーダーチャートには塗りつぶしレーダーがあります。塗りつぶしレーダにすると面積がよくわかりますが、複数の系列を表示した場合重なった部分が見えなくなってしまいます。塗りつぶしの色に透過をかけて下の面を見られるように設定します。

系列を選択しデータ系列の書式設定の塗りつぶしと線からマーカーを選択します。面の塗を変更したいのですが、マーカーをクリックします。

マーカーのオプションのぬりつぶしで単色で適当な色を選択し(勝手に色が変わります。)透明度を適切な値に変更してください。

3系列もあるとちょっとわかりづらいですね。

レーダーチャートにすること自体は簡単ですのでうまく利用してください。

 

 

棒グラフ 応用編

エクセルのグラフの機能はたくさんあります。すべての機能は把握していませんし、事細かにご説明するのはなかなか難しいです。棒グラフでご紹介していないものをまとめてご紹介します。

3D棒グラフ

円グラフでは円グラフ4(3D円グラフ 回転と切り離し)をご紹介しましたが、棒グラフでも3-Dグラフがあります。ポイントは3Dの傾きの調整かと思います。グラフのプロットエリアを選択して書式設定の効果の3-D回転から変更することが可能です。

ラベルと軸の表示形式

下のような棒グラフを作成しましたが、縦軸(数値軸)に0がたくさん並んでいて少し目障りかもしれません。単位を千に変更して0を3つ減らした方が見やすい場合があります。元データを変更せずに軸の単位を変更したいと思います。

縦軸をクリックして選択し、右クリックして軸の書式設定を開きます。

表示形式コードには#,##0;[赤]-#,##0が設定されています。正の数と負の数について設定されています。今回は負の数はありませんので正の数だけ設定しておきます。詳しくは表示形式 (数値)を参照してみてください。

参考リンク先にはありませんが、#,##0の後ろにカンマ(,)を1つつけると3桁表示が省略されま千単位になります。カンマカンマと2つつけると百万単位になります。今回は千単位で0値は表示、負の場合の設定なしにしますので

#,##0,

と入力しなおします。追加のボタンをクリックします。

これでグラフの縦軸の単位が千単位になり0が3つずつ減りました。このままではグラフの数値の単位が見た人に伝わりませんので『単位:千円』みたいな表記を付けておきたいです。テキストボックスでも構いませんが、軸ラベルというものがありますので軸ラベルを挿入して調整します。グラフツールのデザインタブのグラフのレイアウトのグラフ要素の追加をクリックします。軸ラベルの第1縦軸をクリックします。縦軸の左に軸ラベルが挿入されました。

軸ラベルが縦になっていますので調整します。サイズとプロパティの配置の文字列の方向から横書きを選択します。軸ラベルの文字を変更し任意の位置にドラッグして移動します。

これで単位の表示もできました。見やすいかどうかケースバイケースで使い分けてください。

実は軸の単位変更はもっと簡単方法がありまして。

軸の書式設定の軸のオプションの表示単位のプルダウンから千を選択します。自動的に0が3つ減りまして、グラフ上に千という文字が表示されます。この線の文字を選択し文字列の方向を変更し位置を移動すれば同じことです。

棒グラフ反転

これは縦軸を反転させただけです。使い道は怪しいです。

マイナスの値のある棒グラフ

自動で0を基準に上下に分かれるようです。横軸(項目軸)を下に移動します。

軸の書式設定の軸のオプションのラベルのラベルの位置を下端/左端に変更します。

これで見やすくなりました。

この棒グラフが上向きと下向きに切り替わる基準は0になっていますが、任意の数値に変更することができます。軸の書式設定の軸のオプションの横軸との交差で軸の値に任意の数値を入力します。

自動に戻すと0を境に戻ります。軸の最大値にするとすべての棒が下向きになります。先ほどの反転は縦軸も反転しましたがこれは軸は反転しません。マイナス値のものが一番棒が長くなります。

他にもいろいろありますのであちらこちらと弄り回してみてください。

ドーナツグラフと100%積み上げグラフ

円グラフと似た感じのものにドーナツグラフがあります。文字通り円グラフがドーナツのように中心部が空白になっています。

それ以外の違いは円グラフは複数の系列を円であらわすことが可能です。

作り方は円グラフの代わりにドーナツを選択するだけです。

表を選択し挿入タブのグラフの円またはドーナツグラフの挿入からドーナツを選択します。ドーナツグラフが挿入されました。

41thと45thの2系列がグラフになっています。

凡例を非表示にしてグラフタイトルも非表示にします。、ラベルを表示します。ラベルの表示は2系列あるのでどちらか1つだけ分類名を表示するようにします。更にドーナツの円の部分を小さくしてグラフの部分を広く見せます。データ系列の書式設定の系列のオプションのドーナツの穴の大きさを変更します。

ラベル個々に選択して外側に引っ張り出したら一応出来上がりです。

ドーナツの穴の部分にテキストボックスを配置してグラフのタイトルでも書いておきましょう。また、どちらの円が41thか45thかわかりませんのでこれも別のテキストボックスに書いておきましょう。グラフを選択した状態でテキストボックスを挿入すればグラフと一体化されてグラフを移動すればテキストボックスも一緒に移動するようになります。

円グラフもそうですがドーナツグラフも数値の比較にはあまり適さないようですし、2重のドーナツグラフは前回(内側と外側)との比較も意外とわかりづらいです。

100%積み上げ横棒グラフというのがあります。ドーナツグラフを切ってそれぞれを棒状にしたようなものです。

グラフ作成時に100%積み上げ横棒を選択します。

出来たグラフは軸が反対なので入れ替えます。個人の2回の比率が欲しければこれでいいですが。グラフを選択した状態でグラウツールのデザインのデータの行列の切り替えをクリックします。

行と列が入れ替わり横棒が2本になりました。ここにラベルを表示します。今回は凡例をそのまま置いておきますので値だけ表示しておきます。

最後にグラフタイトルと、41thと45thの間に区分線を引きます。グラフタイトルはダブルクリックで書き換えます。区分線はグラフ右上のグラフ要素にありません。グラフツールのデザインのグラフのレイアウトのグラフ要素を追加をクリックします。線をポイントして区分線をクリックします。

おそらく、ドーナツグラフの方がかっこよさそうですが、データを視認するという点では100%積み上げ横棒の方が分かりよいと思います。

100%積み上げ縦棒も同じですが、項目が多いと縦は見にくくなりますのでどちらがいいかはグラフの種類の変更などで見比べてみてください。

エクセル おすすめショートカット10個(本当の基本編)

エクセルを使うときに覚えておけば便利なキーボードショートカットを選んでみました。独断で選びましたがよろしくお願いします。すべてのエクセルのバージョンでは試していませんが、昔から使っているものばかりなのでwindows版のエクセルなら大丈夫だと思います。

ぜひとも覚えていただきたいショートカットトップ10

1位 元に戻す Ctrl + z
操作を失敗したときや、前の状態の方がよかった場合などに使ってください。クイックアクセスルールバーにあるボタンと同じです。

2位 やり直す Ctrl + y
元に戻すとペアで覚えてください。元に戻しすぎた場合やり直すでそのまえのじょうたいにもどります。

3位 コピー Ctrl + c
エクセルに限らずワードでも使えますし、コピーのボタンがないところでも使えることが多いです。いろいろなソフトでもCtrl + cがコピーに割り当てられているようです。

4位 貼り付け Ctrl + v
コピーしたものを張り付けます。コピーとワンセットで覚えてください。

5位 切り取り Ctrl + x
これもセットに入れておいてください。ちょうどキーが3つ並んでいますのでcopyのcを中心に3つです。

6位 すべて選択 CTRL+ a
すべてのセルを選択します。データをすべて削除するとかコピーするとかの場合便利です。

7位 直前のコマンドの繰り返し F4
直前と同じ操作をする際に使います。例えば離れたセルを同じ色で塗りつぶす場合などセルを選択しては塗りつぶしボタンをクリックするのは面倒ですね。そんな時、2回目以降はセルを選択してからF4キーをたたきます。使えないコマンドもあるようです。

8位 保存 Ctrl + s
何かの事情で強制終了してしまうと前回保存したところまで作業が戻ってしまいます。こまめに保存してください。新規保存時以外は上書き保存になります。

9位 書式設定 Ctrl + 1
右クリックからセルの書式設定でも構いませんが、マウスを持っていない場合便利です。

10位 右クリック Ctrl + F10 またはアプリケーションキー
キーボード操作中右クリックしたい場合、マウスを持ちなおさなければなりません。これを覚えておくと便利です。アプリケーションキーはキーボード右のCtrlキーの左隣にあるはずですが、ノートパソコンなどアプリケーションキーがないものもあります。

いかがでしたでしょうか。すでに全部知っているし使っているという方も多かったのではないでしょうか。7位の直前のコマンドの繰り返しと9位の書式設定以外はほとんどのアプリケーションで使えると思います。パソコンの共通ショートカットに近いものですね。

またの機会に別のショートカットもご紹介する予定です。

棒グラフ 途中を波線で省略

棒グラフで1系列のみ値が大きい場合、特定の項目だけ値が大きい場合、途中を波線で区切った棒グラフを作りたい場合があります。エクセル自体にはそのような棒グラフを作る機能はないようです。

いつごろからか、元の表を工夫してメモリを表示形式を利用してずらす方法がネットで散見されるようになりました。私も知らなかったので試してみましたが、よく考えられています。

http://www.tschoolbank.com/excel/graph/omit-bar/
http://www.officepro.jp/excelgraph/bar_graph/index5.html
http://knowledgecreat.blog.fc2.com/blog-entry-37.html?sp
などがあります。

残念ながら、元の表を作り直す必要があるのと、データが変わったときにグラフのメモリが合わない可能性があるのでここでは、昔ながらのグラフの2枚重ねで対応してみます。

まず普通に棒グラフを作ります。東京本店が桁違いなのでほかの支店がよくわかりません。グラフの途中を省略したグラフを作ってみます。

もう一つ同じグラフを1から作成するか、今あるグラフをコピーします。

2つのグラフをそれぞれの値が分かるように軸の最大値と最小値を変更します。1つのグラフは少ない方に合わせて最大値を120000に設定します。もう一つは多き数値に合わせて最小値を300000に変更します。

あとは二つのグラフを重ねて見栄えを整えたら出来上がりです。

上のグラフの凡例と横軸を非表示にしプロットエリアをドラッグして下に移動します。

図形から小波をいくつか並べて出来上がりです。

あとは出来上がりを見ながら見栄えを調整してみてください。

上のグラフ全体を縦方向に縮めました。波線を少し細くしました。それぞれのグラフの外側の枠線を消しました。代わりに四角の図形を最背面に持ってきました。

円グラフ6(その他の項目にまとめる)

円グラフの中に入れたい項目がたくさんあるときは円グラフ5(補助円付きグラフ)にすることで見やすさを保った円グラフが作成できますが、補助円のグラフの部分はその他1項目にしてあるグラフなど見かけます。エクセルの円グラフではどうすればいいでしょうか。

ちょうどそれに該当するグラフがありませんのでちょっと細工します。円グラフ5(補助円付きグラフ)にして補助のグラフをうまく見えなくすればそのように見えます。

通常の補助円付きグラフを作成します。

データラベルを表示して凡例を非表示にします。補助円のグラフをメインの円グラフと補助の円グラフを結んでいる区分線を非表示にします。

区分線の書式設定の区分線のオプションから線の線なしを選択します。

補助円側を消していきます。データラベルは個別にクリックしてチェックが入っている分類名、値、パーセントのチェックを外します。

補助円のラベルは消えました。次に補助円を消したいのですが消すことができませんのでグラフエリアと同じ白色に設定し見えなくします。

補助の円グラフを小さくしてグラフエリアをドラッグして出来上がりです。

補助円付きグラフにすると円グラフの回転ができなくなります。3Dもできません。

これで7位以下をその他とする円グラフが完成しました。

邪道ですがこのようなことも可能です。何が邪道かといいますとその他のラベルが手書きです。図形(テキストボックス)に書いていますのでグラフの数値が変更されてもラベルの値が変わりません。その他の合計のセルを作成してセル参照も可能ですが、その他の数(補助のプロットの値)が変わったときに対応できないので今回はやめておきました。

その他の4名分の系列の色を同じにしてありますので1つの系列に見えているだけです。

1位のさっしーのラベルだけ右クリックしてデータラベル図形の変更から形状を変更しました。あとは絵文字とか、文字色、背景色、文字サイズをごにょごにょしました。

さっしー推し円グラフ完成です。

円グラフ5(補助円付きグラフ)

円グラフ1で書いていますように円グラフは項目が多いと煩雑に見えますので系列を7つに絞ろうと思います。ここで元データを1位から7位までにしてしまえば話は簡単なのですが、9位から10位の人のデータが除かれてしまいますので割合も変わってしまいます。

1位から10位までの円グラフと1位から7位までの円グラフ

誰が何位かわかりづらいですが、1位の指原莉乃さん(右上の青)の割合が増えていますね。もちろん元データのhttp://www.akb48.co.jp/sousenkyo_41th/result.phpでは80位まで掲載されていますのでベストテンの中の割合としておいてください。

全体はそのままで7位から10位までを1つにまとめて別グラフにしてみます。すでに作成しているグラフの種類を変更しても構いませんし、表を選択してグラフの挿入でも構いません。補助円グラフ付き円グラフを選択します。補助縦棒付き円でも構いません。補助棒は積み上げ棒グラフになります。

上位6名と下位4名の円グラフになりました。補助円、補助棒のグラフには3Dはありません。あとは、ラベルの設定をしたり、凡例の非表示、塗りつぶしの色など変更して見栄えを整えます。今回、補助のグラフは4名分だったのですが、ここは自由に変更できます。

データ系列の書式設定の系列のオプションの補助プロットの値が補助グラフの系列数になります。今回は7に増やしてみます。

補助プロットの数を7にして、さらに補助プロットのサイズを30パーセントにしてみました。

こうすると上位3位までで半数近くの票があり大きく表示されています。補助円グラフは小さいので目が行きづらいですね。

さらに指原さんだけ赤にして切り出していますので注目度は上がりますので指原推し円グラフが完成しました。

データの改ざんは一切ありません。さっしーのみ系列の色と同じ太い罫線を設定して、ラベルの文字色と文字サイズを変更し、1位という文字列を足しています😋。ページ最初のグラフと見比べてくださいね。