10/31/2016

今週のFacebookページの投稿 2016/10/24-2016/10/30

今週のFacebookページの投稿 2016/10/24-2016/10/30

<Facebookページ>

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

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

10月25日
Excel。indirect関数は文字列をセル番地や名前として直接的に参照する関数です。

10月26日
Excel。choose関数は値のリストから指定した位置の値を参照する関数です。

10月27日
Excel。offset関数は相対的な位置関係で指定したセルを参照する関数です。

10月28日
Excel。if関数は条件分岐関数です。

10月29日
Excel。or関数はいずれか一つでも成立するかの判断関数です。

10月30日
Excel。and関数はずべての条件が成立するかの判断関数です。

Excelテクニック and  MS-Office recommended by PC training

https://www.facebook.com/exceltechniqueandmsoffice/

Excelのショートカット一覧はこちらで書いております。

http://yandss.p2.weblife.me/shortcutkey.html

10/30/2016

Excel。DBNum1。日付を西暦の年月日表示から元号表記の年月日でしかも漢数字にしたい


Excel。日付を西暦の年月日表示から元号表記の年月日でしかも漢数字にしたい

<表示形式:DBNum1>


Excelでは、日付を入力した後に、
表示形式を使って使いたい日付のタイプに表示形式を設定してあげることになりますよね。

例えば、
10/1と入力すれば、10月1日
TODAY関数を使えば、西暦の年月日で表示されます。

ちなみに、Excel2013では、西暦という表記ではなく、”グレゴリオ暦”に変わっております。

そこで、今回は、グレゴリオ暦の年月日で表示しているものを元号表示にして、
さらに、漢数字で表示してみたいと思います。

元号は問題ないのですが、漢数字は、ちょっと、レベルがアップします。

元号表記で、さらに漢数字で表示させないといけないケースも書類によってはありますので、
知っておいて損はありませんね。

では、このような表を作ります。

D2は、表示形式で元号表記にしてあります。

それでは、セルの書式設定ダイアログボックスを表示して確認してみましょう。

セルの書式設定ダイアログボックスは、Ctrl + 1のショートカットキーが便利ですね。

分類は日付で、カレンダーの種類を和暦にすれば、
簡単に日付を元号表記にすることが出来ますよね。問題は、ここから。

今元号表記にD2はなっていますが、数字を漢数字にするのにはどうしたらいいでしょうか?

表示形式ですから、自分で漢数字に入力しなおすわけではありません。
そこで、ユーザー定義を使って修正して行きます。

今、ユーザー表示にすると、
種類は、[$-411]ggge"年"m"月"d"日";@となっております。

この表示形式を修正していきます。

ところで、ggge"年"m"月"d"日"は、元号表記なのですが、

[$-411]

これは何の意味なのでしょうか?

これは、ロケール。すなわち、日本という地域を表しています。

では、どのように修正するのか?というと、
このユーザー定義の先頭に、[DBNum1]と入力します。

種類は、

[DBNum1][$-411]ggge"年"m"月"d"日";@

それでは、OKボタンをクリックしてみましょう。

このように、漢数字で表示することが出来るようになりました。

実は、
[DBNum1]は、[DBNum3]までありまして、このように表示が変わります。

[DBNum2]だと、大字で表示されます。

ユーザー定義は、

[DBNum2][$-411]ggge"年"m"月"d"日";@


[DBNum3]だと、全角で表示されます。

ユーザー定義は、

[DBNum3][$-411]ggge"年"m"月"d"日";@


これは、別に日付だから[DBNum1]とかが有効というわけではありません。
例えば、D1に1234と入力をして、表示形式のユーザー定義を、

[DBNum1]G/標準

としてみましょう。

このように、漢数字になりましたね。つまり、数値を次のように表示を変えてくれるのです。

[DBNum1]は、漢数字

[DBNum2]は、大字

[DBNum3]は、全角


知らないと、このように表示することが出来ませんので、お仕事で必要な方は、
頭の片隅にこんなものがあるということをいれておくといいかもしれませんね。

10/27/2016

PowerPoint。14回。スライド番号の場所と大きさを調整したいのですが。


PowerPoint。14回。スライド番号の場所と大きさを調整したいのですが。

<PowerPoint>


PowerPoint13回で、ページ番号というかスライド番号を表示する方法をご紹介しておりますが、
今回は、その表示した、ページ番号(スライド番号)の配置場所や大きさを調整するには、
どうしたらいいのかということをご紹介していきます。

まず、4枚のスライドがあって、

1枚目は、表紙 タイトルスライド
2枚目は、1ページ タイトルとコンテンツ
3枚目は、2ページ 2つのコンテンツ
4枚目は、3ページタイトルとコンテンツ

という4枚のスライドがあります。

1枚目のスライドは表紙なので、ページ番号(スライド番号)が表示されていません。
そして、2枚目が1ページというように設定してあるスライドでした。

では、配置場所を右下から右上に変更していきましょう。
このようにスライド全体に関して、調整する場所があります。

それが、【スライドマスター】です。

まずは、その【スライドマスター】を表示していきますので、
表示タブからスライドマスターをクリックしていきましょう。

すると、左側のサムネイルがレイアウト関係のサムネイルに変わりました。

また、リボンをみると、スライドマスター関係のタブが登場しております。

左側のサムネイルをみると、一番上が少し大きくなっていますよね。

これが全体をコントロールしているテーマのスライドマスターです。
また、それぞれのレイアウトの上にマウスカーソルを置くと、
どのスライドで使用しているのかが、ポップアップされてきますので、確認することが出来ます。

今回は、全スライドが対象になっていますので、
一番上の全体のテーマのスライドマスターをクリックします。

右下にあった、<#>が入っているボックスを右上隅に移動させましょう。

そして、<#>のフォントサイズを大きくしていきましょう。

さらに、やる必要はないのですが、動きをご紹介したいので、文字の色も変更してみましょう。

それでは、スライドマスタータブの「マスター表示を閉じる」ボタンをクリックしましょう。

ページ番号(スライド番号)は、右上隅に移動して、
文字のサイズも大きくなっていて赤色になっていますよね。

このように、スライドマスターを使ってあげることによって、
ページ番号(スライド番号)の配置場所や大きさ、色を修正することが出来ますよ。

なお、スライドマスターで、それぞれのレイアウトごとに表示方法を変えたい場合には、
そのレイアウトのスライドマスターを使って修正していきます。

10/24/2016

Excel。Asterisk。文字を伏字にしたいのですが、いい方法はありませんか?


Excel。文字を伏字にしたいのですが、いい方法はありませんか?

<LEFT関数+REPT関数+LEN関数>


ちょっとしたご質問だったのですが、あまり使わない関数の組み合わせだったので、
それを今回はご紹介していきます。

ただ、タイトルだけでは、なんだかわかりにくいので、次の表をご覧ください。

A列に氏名が入力されていて、そのままではオープンな資料として使えないので、
氏名の一文字目はそのままで、
それ以降は、残りの文字数分を【伏字】にしたいということをやりたいわけなんですね。

今回は伏字で全角の”*”を使っておりますが、
この伏字の数が決まっているならば、LEFT関数との文字結合で、完成しますが、
今回厄介なのは、文字数が違うということ。

ということは、
その最初の一文字目を除いて数えてあげる必要があります。

そして、その文字数分を伏字にしないといけないということ。
なので、REPLACE関数も使いにくいのです。

このような場合、Excelのベテランさんならともかく、Excelがもう少しと思っている方などは、
一つずつ区切って数式を作成していき、
最終的に合体させるほうがわかりやすいと思いますので、
E列以降を使って確認していきましょう。

E列には、最初の一文字目を抽出していきましょう。

左側から一文字ですから、LEFT関数を使います。

E3をクリックして、LEFT関数ダイアログボックスを表示しましょう。

文字列は、A3
文字数は、1
としてOKボタンをクリックしましょう。オートフィルで数式をコピーしましょう。
E3の数式は、

=LEFT(A3,1)


最初の一文字目を抽出できましたね。

続いて、F列。
ここは、先に文字数を数える関数を設定していきます。
文字数を数える関数は、LEN関数ですね。

F3をクリックしてLEN関数ダイアログボックスを表示しましょう。

文字列には、A3
と入力して、OKボタンをクリックしましょう。

今、F3の数式は、
=LEN(A3)となっていますね。
今回は、この文字数から-1(マイナス1)した数をふせじにするので、
数式を=LEN(A3)-1と修正します。

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

このF列で算出した数値の回数を繰り返して伏字である、”*”を作りますので、
ここは、REPT関数が登場します。

G3をクリックしてREPT関数ダイアログボックスを表示しましょう。

文字列には、”*”
繰り返し回数には、F3
として、OKボタンをクリックしましょう。オートフィルで数式をコピーします。

G3の数式は、

=REPT("*",F3)

あとは、E3とG3を結合させてあげればいいので、C3には、

=E3&G3

とすれば、完成ですね。

なお、E列~G列までの作業列を使わないで、一発で数式を作るのでしたら、
C3に、

=LEFT(A3,1)&REPT("*",LEN(A3)-1)

という数式を設定してあげれば、作成することが出来ますよ。

10/23/2016

今週のFacebookページの投稿 2016/10/17-2016/10/23

今週のFacebookページの投稿 2016/10/17-2016/10/23

<Facebookページ>

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

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

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

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

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

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

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

Excelテクニック and  MS-Office recommended by PC training

https://www.facebook.com/exceltechniqueandmsoffice/

Excelのショートカット一覧はこちらで書いております。

http://yandss.p2.weblife.me/shortcutkey.html

10/21/2016

PowerPoint。13回。スライド番号を表示設定したい。PowerPoint2010編


PowerPoint。13回。スライド番号を表示設定したい。PowerPoint2010編

<PowerPoint>

PowerPointの研修依頼であるのが、
スライドにページ番号(スライド番号)を設定したいのですが、
どうもわかりにくいので、教えてほしいのですがというものがあります。

確かに、設定する箇所が、アチコチに飛んでありあますし、
わかりにくい点でもありますので、
今回は、ページ番号の設定方法をご紹介していきましょう。

まず、次のようなスライドがあります。

1枚目は、表紙 タイトルスライド
2枚目は、1ページ タイトルとコンテンツ
3枚目は、2ページ 2つのコンテンツ
4枚目は、3ページタイトルとコンテンツ
という4枚のスライドがあります。

それでは、まず始めに、ページ番号(スライド番号)を設定していきましょう。

挿入タブにある、「スライド番号」か「ヘッダーとフッター」のどちらかをクリックしましょう。

この2つのボタン。
どっちをクリックしても、同じダイアログボックスが表示されますので、
日ごろ、WordやExcelを使っている人ならば、
「ヘッダーとフッター」のほうがわかりやすいのならば、
それでOKですし、スライド番号を設定するというイメージが強いようでしたら、
「スライド番号」と覚えておくといいかと思います。

クリックしますと、ヘッダーとフッターダイアログボックスが表示されます。

その中にある、スライド番号にチェックマークを付けましょう。

そして、本来ならば、タイトルスライドに表示しないにもチェックマークを付けるのですが、
今回は、説明をしたいので、そのまま、すべてに適用ボタンをクリックします。

適用ボタンだと、そのスライドにしか反映されませんので、
PowerPointの場合は注意が必要ですね。

すると、すべてのスライドの右下にページ番号(スライド番号)が表示されました。
なお、今回のテーマはOfficeで行っておりますので、テーマによっては、
違う場所に表示されているかもしれませんので、ご注意ください。

しかし、資料としてこれでは、イマイチ。表紙に普通、スライド番号は振りませんよね。

そして、2枚目のスライドが1ページになってもらう必要があります。
2枚目のスライドを確認してみましょう。

右下のページ番号(スライド番号)は、2になっています。

それでは、改めて、ヘッダーとフッターダイアログボックスを表示しましょう。

タイトルスライドに表示しないにチェックマークをつけて、
すべてに適用ボタンをクリックしてみましょう。

1枚目のタイトルスライドは表示されなくなりましたが、
2枚目を確認すると、2のままになっています。

では、ページ番号(スライド番号)を振り直していきましょう。

デザインタブのページ設定をクリックしましょう。

ページ設定ダイアログボックスが表示されます。

その中にある、スライド開始番号が1になっていますので、これを0(ゼロ)にします。

要するに、1枚目は見えなくなっているだけなので、0(ゼロ)にすることで、
2枚目のスライドが2-1で1となるという仕組みです。

これで、2枚目のページ番号(スライド番号)は1に変わりましたね。

このように、ページ番号(スライド番号)を表示するところと、
アレンジするところがことなっているので、わかりにくい要因の一つかもしれませんね。

10/18/2016

Excel。財務関数シリーズ。PV関数は、ローン返済でおなじみの関数です。


Excel。財務関数シリーズ。PV関数は、ローン返済でおなじみの関数です。

<PV関数>


財務系の関数で、FV関数と同様に、わかりにくい。といわれるのが今回ご紹介する、

PV関数。

確かに、仕事で関係しない限りは毎日毎日、
使うことはない関数なのは間違いありませんが、
MOS試験の範囲になっていたりするので、ご質問があります。

そこで、今回は、財務関数の中から、
毎月の返済金額から借入可能な金額を算出できる、
PV関数について、ご紹介していきます。

このPV関数の、PVとは、Present Value。つまり、現在の価値という意味ですね。

次の表を使って確認していきましょう。

財務系の関数で気を付けるコトというか、注意しないといけないのは、
年利や返済額(月)など、年なのか?月なのか?というポイントです。

では、B2をクリックして、PV関数ダイアログボックスを表示しましょう。

まずは、年率からいきましょう。
年率は年利なのでB4。ですが、月額を算出していく必要がありますので、1年。
つまり12で除算する必要が生じますので、
年利は、B4/12

期間ですが、期間(年)のB5。そしてこれは年なので12倍してあげませんと、月になりませんね。
なので、期間は、B5*12

そして、定期支払額ですが、これは、毎月の返済額ということですから、
定期返済額は、B6

あとは、OKボタンをクリックしてみましょう。

数式は、

=PV(B4/12,B5*12,B6)

となっていますね。
では、借入可能金額は、¥-942,814と表示されましたね。

ちゃんと算出はされているのですが、マイナスで表示されてしまいますと、
イマイチピンときませんね。

これは、手元からなくなるということなので、マイナスになっているのですが、
B6の返済額(月)をマイナスにしてあげると、PV関数の算出した値は、
わかりやすくなるかと思います。

このように、借入可能金額を求めることが出来ましたね。

また、算数・数学が苦手で、これ合っているの?と思い方もいらっしゃると思います。

その場合には、年利を0%にしてあげると、
ちゃんとこの関数が稼働しているかどうかを判断することが出来ると思いますよ。

では、B4を0%にしてみましょう。

どうでしょう。期間は10年なので月に直すと12倍して120。
これに返済金額が月々、10000だとしたら、120×10000=1200000 というようになりますよね。

なので、PV関数はちゃんと動作していることを確認することは出来ますね。

以前ご紹介したFV関数も今回のPV関数も、苦手だなぁ~。
MOSの練習でこれらの関数を勉強してもよく動きがわからない。
という方は、年利を0%にしてみるといいですね。

なお、このPV関数。自動的に通貨スタイルが設定されて算出されますので、
¥マークとか三桁区切りなどは設定しておりませんので、お間違いのないように。

10/17/2016

今週のFacebookページの投稿 2016/10/10-2016/10/16

今週のFacebookページの投稿 2016/10/10-2016/10/16

<Facebookページ>

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

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

10月11日
Excel。roundup関数は切り上げ関数です。

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

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

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

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

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

Excelテクニック and  MS-Office recommended by PC training

https://www.facebook.com/exceltechniqueandmsoffice/

Excelのショートカット一覧はこちらで書いております。

http://yandss.p2.weblife.me/shortcutkey.html

10/15/2016

Excel。Graph。横棒グラフの塗りつぶしを、条件で自動的に色分けしたい。


Excel。横棒グラフの塗りつぶしを、条件で自動的に色分けしたい。

<条件付き書式モドキ横棒グラフ>


横棒グラフに限りませんが、
例えば、パレート図などのグラフのように数値によって塗り分けたい場合など、
いちいち、色分けをしていたら、データが多くなると大変です。

できれば、自動的に塗り分けることが出来たらグラフを作るのも楽になるはず。

要するに、条件付き書式モドキ塗りつぶしグラフを作れれば…ということで、
今回は、次のようなグラフを作成していきましょう。

では、次の表から作っていきます。

さて、この表から自動的に色分けするようなグラフは、作ることが残念ながら出来ません。

このままグラフを作ってしまうと、自力で色分けしないといけません。

Excelは、表があって、その表を使って、グラフにする。

ということなので、グラフに合わせた表を作ってあげる必要があります。

そこで、次のような表にアレンジしていきます。

この表の作り方を説明しておきます。

C1:E1までのセル。~という文字がついていますが、
これは表示形式を使って~600というように表示させています。

つまり、600,700,800という数値がそれぞれのセルに設定されています。

そして、C2には、数式が設定されています。

=IF(B2<=$C$1,B2,0)

この数式を、C8まで、オートフィルでコピーしてあります。
D2には、次の数式が設定されています。

=IF(AND($B2>C$1,$B2<=D$1),$B2,0)

この数式を、E8まで、オートフィルでコピーしてあります。

これらの数式を使うことで、600までなのか、700までなのか、800までなのか。

というように売上高の数値を振り分けしています。

この数値を使って、グラフを描くのですが、どう描くのかというと、
【積み上げ】のグラフで作成していきます。

つまり、今回は、【積み上げ横棒グラフ】で描いていきます。

では、A2:E8を範囲選択して【積み上げ横棒グラフ】を挿入します。

積み上げ横棒グラフなのに、B列の売上高も含めていいの?と思う方もいると思いますが、
積み上げ横棒グラフは、合計値のデータラベルを表示することが出来ないので、合計した値。

つまりB列の売上高も含める必要があります。

そこで、その売上高である。系列1を選択して、
選択対象の書式設定ダイアログボックスを表示させましょう。

要素の間隔を、”なし”にします。これで、棒グラフが太くなります。

そして、使用する軸を、”第2軸”に変更して、閉じるボタンをクリックします。

系列1の横棒グラフが選択されている状態のまま、データラベルを内側で表示して、
塗りつぶしと枠線をそれぞれ”なし”に設定します。

グラフの上に表示されている、第2軸横軸を削除して、凡例も削除します。

あとは、系列4を選択して、データ系列の書式設定ダイアログボックスを表示します。

要素の間隔を、”なし”にしましょう。閉じるボタンをクリックしたあと、
色を調整してあげれば完成です。

これで、条件付き書式モドキ横棒グラフが完成しました。

アイディアによっていろんなグラフを描くことが出来ますので、
いろんなアイディアを試してみると、Excelのグラフ。なかなか面白いですよ。

10/12/2016

Excel。TEXT&DATEDIF。開始日から終了日までの期間を年ヶ月で表示するにはどうしたらいい?


Excel。開始日から終了日までの期間を年ヶ月で表示するにはどうしたらいい?

<TEXT関数とDATEDIF関数>


開始日から終了日の間の日数を、「年月日」で表示するのではなくて、
その間の期間を「年ヶ月」という形で表示したいのですが、
うまくいかないので困っているというリクエストがありました。

そこで、今回は、期間を年ヶ月で表示する方法をご紹介していきましょう。

下記のような表があります。

B列に入会日があって、今日までの入会期間を求めるというのがC列。という表です。

このような期間を求める時に登場するのが、DATEDIF関数ですね。

このDATEDIF関数は、関数挿入のダイアログボックスから見つけることが出来ない関数なので、
直接入力していく必要があります。

では、C2に次の数式を設定してみましょう。

=DATEDIF(B2,TODAY(),"y")

そして数式を入力して、オートフィルで数式をコピーしておきましょう。

=DATEDIF(B2,TODAY(),"y")

は、その間の年数を算出してくれます。
よって年数を求めるだけでしたら、この数式でいいのですが、求めたいのは「年ヶ月」。

そこで、&を使って、文字と数式を結合して、次のように数式を修正していきましょう。

=DATEDIF(B2,TODAY(),"y")&"年"&DATEDIF(B2,TODAY(),"ym")&"ヶ月"


そして、オートフィルで改めて、数式をコピーしてみましょう。

どうでしょうか?
今日までの期間を「年ヶ月」で表示することが出来ましたね。
ちなみに、数式に追加した、

DATEDIF(B2,TODAY(),"ym")

これは、年を除いた月数を求めることが出来る数式ですね。

しかし、確かに、「年ヶ月」で表示は出来たのですが、C2とC7。

“0ヶ月”と表示されていますが、これ、なんかカッコ悪いですよね。
“0ヶ月”だったら、表示しないで、何年。と表示できた方がいいですよね。

IF関数を使って判断させるようにしてもいいのでしょうけれど、0ヶ月かどうかを判断させて、
それによって、算出方法を変えられるようにするとしたら、数式が煩雑になってしまいます。

そこで、TEXT関数を使ってみると、今回の0ヶ月を非表示にするように変更していきましょう。

このTEXT関数は、表示形式を変更設定してくれる関数ですね。
でどのように使うのか?
というと、0(ゼロ)だったら、表示しないようにしたいわけです。

このBLOGでも度々登場しているテクニックですが、表示を消したいときには、
表示形式のユーザー定義を;;;(ゼミコロン×3)にすると良かったわけですよね。

となると、ユーザー定義で、”0年;;”と”0月;;”としてあげればいいわけですね。

表示形式は、正数 ; 負数 ; 零 ; 文字 の順番で表示方法を設定しておりますので、
”0年;;”とすれば、いいわけです。

では、C2の数式を次のように修正してみましょう。

=TEXT(DATEDIF(B2,TODAY(),"y"),"0年;;")&TEXT(DATEDIF(B2,TODAY(),"ym"),"0ヶ月;;")

あとは、オートフィルで数式をコピーしましょう。

これで、完成しましたね。

このように、単に、「年ヶ月」を表示させる方法をプラスアルファして、
0ヶ月などの0を非表示する方法も加えてみました。

何か機会がありましたら、
TEXT関数とDATEDIF関数の組み合わせを使ってみてはいかがでしょうか?