9/30/2019

今週のFacebookページの投稿 2019/9/23-2019/9/29

今週のFacebookページの投稿 2019/9/23-2019/9/29

<Facebookページ>

Facebookページで【書いてみた】ワンポイントです。

9月23日
Excel。vlookup関数はデータ抽出関数です。
ちなみに検索値が縦に並んだ表です。

9月24日
Excel。hlookup関数はデータ抽出関数です。
ちなみに検索値が横に並んだ表です。

9月25日
Excel。lookup関数は対応範囲にあるデータ抽出関数です。

9月26日
Excel。row関数は参照した行番号を算出関数です。

9月27日
Excel。column関数は参照した列番号を算出関数です。

9月28日
Excel。index関数は行・列位置を指定してデータを抽出関数です。

9月29日
Excel。match関数は指定したデータが範囲の何番目にあるかを算出関数です。

Excelテクニック and  MS-Office recommended by PC training
https://www.facebook.com/exceltechniqueandmsoffice/

9/28/2019

Excel。初心者さんの落とし穴。COUNTA関数は、数式の結果が空白だと数えちゃう。【COUNT】

Excel。初心者さんの落とし穴。COUNTA関数は、数式の結果が空白だと数えちゃう。

<COUNT関数・COUNTA関数>

件数を算出する関数には、COUNT○○関数という、様々な関数が用意されています。

基本的には、どの件数を算出する関数も、わかりにくいことはないのですが、意外と落とし穴があるというか、気を付けないといけない関数があります。

それが、COUNTA関数です。

落とし穴を確認する前に、基本中の基本である、COUNT関数から確認しておきましょう。
次のような表があります。

B5には、何名いるのか確認したので、
=COUNT(B2:B4)
という数式を設定して算出しています。

COUNT関数は、オートSUMボタンにある、数値の個数を使うとCOUNT関数で算出します。

このCOUNT関数は、「範囲内の、数値が含まれるセルの個数を返します。」ということなので、数値の個数を算出する関数です。

よって、A2:A4を範囲選択してしまうと、件数は、0(ゼロ)を算出することになります。
なぜ、0(ゼロ)なのかというと、数値ではなくて文字だからです。

さて、事前に確認しておきますが、C2:C4には、得点が80点以上だったら○、そうでなければ空白を判断させるようにIF関数を使って算出しています。

C2の数式は、
=IF(B2>=80,"○","")
この数式はオートフィルを使って数式をコピーしています。

結果C4以外は80点以上なので、○が表示されています。

それでは、C7に80点以上の件数を算出したいので、COUNTIF関数で算出してもいいのですが、今回は、【落とし穴】を紹介したいので、C列の○の数を数えることで、80点以上の件数がわかるはずです。

そこで、先程は、数値を数える関数であるCOUNT関数をしようしましたが、今回は、空白でないセルの個数を数える、COUNTA関数を使うことで算出できるはずです。

C7をクリックして、COUNTA関数の数式を作成します。
=COUNTA(C2:C4)

算出した結果を確認すると「3」。おかしいですよね。範囲内の、空白でないセルの個数を返すはずです。「2」ではなく「3」と算出されています。

ここに、COUNTA関数の落とし穴があるのです。

市販のテキストなどにも、空白でないセルということを書いています。
間違えではないのですが、今回のような『数式の結果、空白の場合』は、数える対象になってしまいます。
イメージとしては、『空白という文字』が設定されている感じです。

なので、C4のIF関数を削除してみましょう。

数式を削除したことで、セルは空白になり、C7の結果は、「2」と変わったことが確認できます。

このように、日常的に使う関数でも、落とし穴があったりしますので、注意する必要があります。

また、このようなことから、ケースによって、使う関数が異なってくるわけです。

本来ならば、80点以上の件数を数えたいので、COUNTIF関数を使うといいですし、空白を数えたい場合は、COUNTBLANK関数が用意されていますので、ケースバイケースで関数を使っていくといいですね。

9/27/2019

Excel Technique_BLOG Categoryに追加しました。2019/09/27

Excel Technique_BLOG Categoryに追加しました。

<目次サイト>

このBLOGの記事を、
カテゴリー分けにした【Excel Technique_BLOG Category】に追加しました。

Excel。IFERROR関数。仕事でよく使うExcel関数 第8位 エラーだったらどうする。


IFERROR関数は結構使い勝手がいいというか、エラーが出たら、どうする?
という関数なので、比較的わかりやすい関数ですね。

<続きはこちら>
Excel。IFERROR関数。仕事でよく使うExcel関数 第8位 エラーだったらどうする。
https://infoyandssblog.blogspot.com/2014/11/exceliferrorexcel-8.html


Excel。SUMIFS関数。仕事でよく使うExcel関数 第7位 複数条件での合計を求める。


SUMIFS関数の特長は、単発条件での合計値を算出するSUMIF関数の単発条件が複数条件での合計値を算出できるようになっている点ですね。

<続きはこちら>
Excel。SUMIFS関数。仕事でよく使うExcel関数 第7位 複数条件での合計を求める。
https://infoyandssblog.blogspot.com/2014/11/excelsumifsexcel-7.html


Excel。SUMIF関数。仕事でよく使うExcel関数 第5位タイ 条件付での合計を求める。

SUMIFS関数は、複数条件での合計値を算出する関数でしたが、今回のSUMIF関数は、シングル条件の合計値を算出する関数ですね。

<続きはこちら>
Excel。SUMIF関数。仕事でよく使うExcel関数 第5位タイ 条件付での合計を求める。


Excel。AVERAGE関数。仕事でよく使うExcel関数 第5位タイ 平均を求める。

AVERAGE関数だけではもったいないので、MEDIAN関数も覚えておくといいですよね。

<続きはこちら>
Excel。AVERAGE関数。仕事でよく使うExcel関数 第5位タイ 平均を求める。


9/25/2019

Access。ランキングでSを先頭として並び替えるにはどうしたらいいの?【RANK&SORT】

Access。ランキングでSを先頭として並び替えるにはどうしたらいいの?

<Access>

数値で表した順位は、昇順にすれば、順位で並べることがAccessでもExcel同様に、簡単に並び替えることができますが、自分で設定した順位、例えば、スペシャルなので、Sが一番良くて、次がAで、続いてBというように、ABC…というアルファベット順でなくて、Sが先頭のランキング順に並び替えをしたい場合、Accessではどのようにしたらいいのでしょうか?

アルファベットも、ABC…という順番でしたら、昇順でクエリを作成すればいいだけなので、問題はありませんが、今回のようなケースでは、アイデアが必要です。

それでは、次のテーブルを、Sから始まる順番で並び替えしたクエリを作成していきましょう。

ランキングフィールドを並び替えしたいわけです。
作成タブのクエリデザインを使って、クエリを作成していきましょう。
T店舗売上のフィールドは、今回全部使用しております。

【Sを1、Aを2と設定したいので、Switch関数】

数字だったら、昇順で簡単に並び替えができますので、考え方として、Sが1位で、Aが2位なわけです。
ということは、Sを1と設定することができればいいわけです。

そこで、登場するのが、『Switch関数』です。Excelでは、Office365のExcelやExcel2019から追加された関数なので、あまりなじみがないかもしれませんが、Accessには、以前から搭載されている関数です。

演算フィールドを作成します。
ダミー: Switch([ランキング]="S",1,[ランキング]="A",2,[ランキング]="B",3,[ランキング]="C",4)

それでは、演算フィールドを説明します。

演算フィールド名は、最終的に表示することはありませんので、なんでもかまいません、今回は、ダミーとしておきます。

そして、Switch関数を使います。
引数の中は、一目瞭然ですが、Sだったら、1というように算出する演算式です。

では、実行して確認してみましょう。

演算フィールドのダミーには、ランキングのアルファベットに対応した数値が算出されていることが確認できました。

ここまでできれば、あとは簡単ですね。

デザインビューに表示を戻して、設定してきましょう。

並び替えの条件に、「昇順」を設定します。

そして、この演算フィールドの結果を表示する必要がないので、表示の行のチェックマークをオフにしましょう。

では、実行して、データシートビューで確認してみましょう。

このように、ランキングをSから先頭として、A・Bと続く順番で並び替えをすることができました。

Switch関数は、このような使い方ができますので、Excelでも機会があれば使ってみると業務が改善できるところがあるかもしれませんね。

9/24/2019

今週のFacebookページの投稿 2019/9/16-2019/9/22

今週のFacebookページの投稿 2019/9/16-2019/9/22

<Facebookページ>

Facebookページで【書いてみた】ワンポイントです。

9月16日
Excel。rounddown関数は切り捨て関数です。

9月17日
Excel。roundup関数は切り上げ関数です。

9月18日
Excel。floor関数は数値を特定値の倍数にして切り捨て関数です。

9月19日
Excel。floor.math関数は数値を特定値の倍数にして切り捨て関数です。
ちなみにver2013からです。

9月20日
Excel。ceiling関数は数値を特定値の倍数にして切りあげる関数です。

9月21日
Excel。ceiling.math関数は数値を特定値の倍数にして切りあげる関数です。
ちなみにver2013からです。

9月22日
Excel。mround関数は最も近い基準値の倍数にして切り上げ切り捨て関数です。

Excelテクニック and  MS-Office recommended by PC training
https://www.facebook.com/exceltechniqueandmsoffice/

9/22/2019

Office。図形やSmartArtに挿入した画像を、大きくしたり、動かしたりしたい。【Trimming】

Office。図形やSmartArtに挿入した画像を、大きくしたり、動かしたりしたい。

<Word・Excel・PowerPoint>

写真などを挿入したあとに、図のスタイルの「楕円、ぼかし」を設定すると、スタイルを簡単に変更することが可能です。

今回は、シロクマ君が中央に写っているので、修正する必要はないのですが、例えば、シロクマ君をもっと中央に配置したいとか、もっと大きくしたいとか、やりたい場合はどのようにしたらいいのでしょうか?

スタイルをはじめ、図形の塗りつぶしのような機能は、ある機能を使って形を変更しているだけなので、ある機能を使ってあげれば、解決しちゃいます。

そのある機能とは、【トリミング】です。

トリミングは、不要なところをカットする機能のように認識されていますが、Word・Excel・PowerPointのどれでも共通ですが、トリミング機能をアレコレ使って、表現しています。

それでは、確認してみましょう。
挿入した写真や画像をクリックして、図の形式にある「トリミング」をクリックします。

写真の四隅・上下左右の外周中央に、太線が表示されます。

それがトリミングなのですが、その太線は無視して、写真そのもののサイズを調整する、白丸「○」も表示されているのが確認できます。

いつものように、この白丸をつかって、写真のサイズを大きくしてみましょう。

また、写真の移動も通常通りできますので、中心からズレた場合など、移動して調整します。

大きさや場所を調整したら、トリミング作業を完了します。

このように、修正することができました。

基本的に、画像の挿入関係は同じ仕組みになっています。

【図形の塗りつぶし】

図形を挿入したあとに、写真で塗りつぶした場合でも同じ方法で対応します。

今回は、ハートの図形を挿入してみます。

挿入した図形をクリックして、図形の書式の「図形の塗りつぶし」から図を選択します。

写真を選択すると、図形を写真で塗りつぶすことができます。

先程と同じように、大きさを変えたいとか、場所を動かしたいという時には、トリミングを使えばいいわけです。

基本的に作業としては同じです。

【SmartArtも同じ】

SmartArtに写真を挿入できるスタイルがありますが、これも同じ方法で、写真を大きくしたり、ずらしたりすることができますので、確認してみましょう。

今回は、「矢印付き画像リスト」というSmartArtを使ってみます。

○のところに、写真を挿入しますので、まず挿入します。

右のオットセイ君の可愛い顔が見切れちゃっています。
可哀想なので、見えるようにしていきます。

やり方は、全くで同じです。トリミングで対応しましょう。

調整してあげれば完成ですね。

このように、図形で塗りつぶしのような場合は、トリミングでアレンジができるかもしれません。

9/21/2019

Excel関数辞典 VOL.18。CUMIPMT関数~DATEVALUE関数

Excel関数辞典 VOL.18。CUMIPMT関数~DATEVALUE関数

<Excel関数>

今回は、CUMIPMT関数~DATEVALUE関数までをご紹介しております。

DATEDIF関数・DATESTRING関数は直接入力でしか作れない関数ですね。

CUMIPMT関数
キュムアイピーエムティー:キュミュラティブ・イントレスト・ペイメント
元利均等返済における指定期間の金利累計を算出
CUMIPMT(利率,期間,現在価値,開始期,終了期,支払期日)


CUMPRINC関数
キュムプリンク:キュミュラティブ・プリンシプル
元利均等返済における指定期間の元金返済額累計を算出
CUMPRINC(利率,期間,現在価値,開始期,終了期,支払期日)


DATE関数
デイト
指定した日付を算出
DATE(年,月,日)


DATEDIF関数
デイトディフ
2つの日付の間の年・月・日数を算出する
DATEDIF(開始日,終了日,単位)


DATESTRING関数
デイトストリング
西暦の日付を和暦の日付に変換する
DATESTRING(シリアル値または日付文字列)


DATEVALUE関数
デイトヴァリュー
日付を表す文字列をシリアル値に変換する
DATEVALUE(日付文字列)

9/19/2019

Excel。4月-6月を第1四半期として、四半期別集計を算出したい。

Excel。4月-6月を第1四半期として、四半期別集計を算出したい。

<INT+MOD+MONTH関数,SUMIF関数>

ピボットテーブルを使えば、”秒殺”で四半期別集計は算出することができるのですが、表が出来上がっていて、該当のセルに結果を表示したい場合など、ピボットテーブルを使用しないで、算出させたいというケースもあります。

そこで、今回は、4月-6月を第1四半期として、四半期別集計を算出していきます。

次の表があります。

E列の四半期ですが、表示形式をつかって、第○四半期と表示させています。

E2:E5までは、1~4の数値のみがセルに入力されています。

さて、どのようにしたら、4月から6月を第1四半期と判断することができるのでしょうか?

SWITCH関数など4ならば1というような設定をしてもいいのですが、これだと多くの引数が必要になってしまい、わかりやすい反面、効率がわるいです。

そこで、次の手順を踏むことで4月から6月を第1四半期と判断する計算手順があります。

H列には、1月から12月までの数値が入力されています。
H2の1は1月を意味しています。

最初に行うのは、I列のマイナス4を行います。
マイナス3では?と思うかもしれませんが、マイナス3にすると、ズレが生じてしまい、上手くいきません。

I2の数式は、
=H2-4

2番目は、
1年は12カ月なので、12で除算しますが、必要なのは、除算した結果の”余り”なので、MOD関数を使います。
J2の数式は、
=MOD(I2,12)

3番目は、四半期が3カ月分で構成されているので、3で除算します。
K2の数式は、
=J2/3

4番目は、誤差を調整しますので、+1をします。
L2の数式は、
=K2+1

最後は、整数部分だけが必要なので、INT関数で整数部分を取り出します。
M2の数式は、
=INT(L2)

このような手順によって、4月から6月を第1四半期と判断することができます。
これをまとめて数式化しています。

C2をクリックして、次の数式を作ります。
=INT(MOD(MONTH(A2)-4,12)/3+1)
手順とは逆で、INT関数から作成しますので、注意しましょう。

算出出来たら、オートフィルを使って数式をコピーします。

あとは、この結果を使って、集計します。

F2をクリックして、SUMIF関数ダイアログボックスを表示します。

範囲には、$C$2:$C$20
検索条件には、E2
合計範囲には、$B$2:$B$20

F2の数式は、
=SUMIF($C$2:$C$20,E2,$B$2:$B$20)
当然、手入力で数式を作成してもOKです。

それでは、オートフィルを使って数式をコピーします。

これで、4月から6月を第1四半期として集計することができました。

簡単なようですが、これもクセがありますので、パズルのような手順を踏んで数式を作っていくといいですね。

面倒だと感じたら、ピボットテーブルを使って算出してみると、いいかもしれません。

9/18/2019

Excel。グラフの復習。棒グラフの並び順~パレート図【Graph】

Excel。グラフの復習。棒グラフの並び順~パレート図【Graph】

<グラフ>

Excelのグラフは、用途に合わせて様々なグラフを作ることができます。
今回は、グラフの復習ということ、5つをピックアップ

・Excel2010。棒グラフの並び順を表の順番にしたい。軸反転
・Excel。棒グラフを絵にして表現してみませんか?
・Excel。パレート図の作成にはグラフノウハウてんこ盛り! その1
・Excel。パレート図の作成にはグラフノウハウてんこ盛り! その2
・Excel。パレート図の作成にはグラフノウハウてんこ盛り! その3

Excel2010。棒グラフの並び順を表の順番にしたい。軸反転
横棒グラフの軸反転

表と同じ並び順にしたいわけですね。
そこで、今回は、軸を反転する方法で、やってみることにしましょう。

<続きはこちら>
Excel2010。棒グラフの並び順を表の順番にしたい。軸反転
https://infoyandssblog.blogspot.com/2014/03/excel2010.html


Excel。棒グラフを絵にして表現してみませんか?
絵グラフ

単なる棒グラフよりも、棒を絵にしたほうが、表現としていい場合があります。
作り方は非常に簡単なので、今回は、棒グラフを絵に変更するテクニックをご紹介しましょう。

<続きはこちら>
Excel。棒グラフを絵にして表現してみませんか?
https://infoyandssblog.blogspot.com/2014/04/excel_22.html


Excel。パレート図の作成にはグラフノウハウてんこ盛り! 
パレート図

パレート図を作ってみようという訳です。
このパレート図を作ることはないなぁ~という方も多いと思いますが、この作成手順の中に、グラフに関するノウハウが多く詰まっていますので、実は、作れるようになると、ググッとExcelグラフのテクニックが上昇しちゃうわけですね。

<続きはこちら>
Excel。パレート図の作成にはグラフノウハウてんこ盛り! その1
https://infoyandssblog.blogspot.com/2014/05/excel_5.html

Excel。パレート図の作成にはグラフノウハウてんこ盛り! その2
https://infoyandssblog.blogspot.com/2014/05/excel-2.html

Excel。パレート図の作成にはグラフノウハウてんこ盛り! その3
https://infoyandssblog.blogspot.com/2014/05/excel-3.html

9/16/2019

Excel。積み上げ面グラフの間に境界線を描きたいけど、うまくいきません。

Excel。積み上げ面グラフの間に境界線を描きたいけど、うまくいきません。

<境界線付き積み上げ面グラフ>

簡単そうに作れると思うグラフほど、意外と手の込んだことをしないといけないことがExcelのグラフにはあります。

今回は次のグラフ。
境界線付き積み上げ面グラフ

積み上げ面グラフの間をわかりやすくしたいので、「境界線付き積み上げ面グラフ」というグラフを作りたいのですが、この境界線が曲者。

【面グラフを積み上げればいいわけではない】

単純に考えると、積み上げ面グラフなわけですから、幅分のデータを考慮した表から積み上げ面グラフを作ればいいように、思いますが、実際に作ってみると、境界線が綺麗ではないことがわかります。

次の表から実際に作って確認してみましょう。

C列は面の幅を50としています。D2は
=1000-B2-C2
として残りの面の高さを算出した表です。

A1:D10を範囲選択して、積み上げ面グラフを作ります。

挿入タブの「折れ線/面グラフの挿入」から「積み上げ面」をクリックします。

挿入されたグラフを、今回は説明のため大きくしておきます。

基本的には、問題はないのですが、よく見ると、境界線がデコボコしていて、2月-3月などの境界線が、綺麗ではないし、太さもデータによって、異なって見えます。

これはExcelのグラフの描き方が影響しているからです。

Excelは「線」ではなくて「面グラフ」を描いているからです。

そこで、綺麗に描くには、テクニックが必要になるのです。

【境界線は折れ線グラフで描く】

綺麗な境界線を描くには、「線」でなければいけません。

そこで「折れ線グラフ」で境界線を描いていきます。

表を次のようにしてから、作成します。

C列は、1000からB列の値を減算した数値で、D列は、B列の売上高と同じ値です。

A1:D10を範囲選択して、挿入タブの「すべてのグラフを表示」をクリックします。

グラフの挿入ダイアログボックスが表示されますので、組み合わせを選択します。
売上高を積み上げ面
残りを積み上げ面
境界線を折れ線にして、第2軸

と設定してOKボタンをクリックしてグラフを挿入します。

第1縦軸と第2縦軸を最大値1000で固定します。
説明のためグラフを大きくしますので、グラフタイトルを削除して、凡例は、売上高のみを残します。

グラフが軸から離れて表示されていますので、引き延ばす作業をします。

第2横軸を使いますので、表示をします。グラフのデザインタブのグラフ要素を追加」から軸の「第2横軸」をクリックします。

第1横軸の軸の書式設定作業ウィンドウを表示させます。

軸のオプションから軸位置を「目盛」に変更すると、グラフが引き伸ばされます。
同じように、第2横軸も軸位置を「目盛」に変更します。

第2横軸を選択して、削除したら、第2縦軸を選択して削除したら、あとは見やすいように、境界線の折れ線を太くしたり、フォントサイズを変更して完成ですね。
境界線を付き積み上げ面グラフ