12/31/2017

今週のFacebookページの投稿 2017/12/25-2017/12/31

今週のFacebookページの投稿 2017/12/25-2017/12/31

<Facebookページ>

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

12月25日
Excel。ショートカット。
Ctrl+:で、今の時間が表示できます。コロンなんですね。

12月26日
Excel。ショートカット。
Ctrlキーとスペースキーで列選択ができますよ。

12月27日
Excel。ショートカット。
Shiftキーとスペースキーで行選択ができますよ。

12月28日
Excel。ショートカット。
CtrlキーとHomeキーで文頭に移動できますね。A1へワープ!

12月29日
Excel。ショートカット。
CtrlキーとEndキーでデータが入っている最後に移動できますね。
テーブルだと、テーブルの末に移動ですね。

12月30日
Excel。ショートカット。
F12キーで、名前を付けて保存ダイアログボックスが登場しますね。

12月31日
Excel。ショートカット。
F2キーで、アクティブセルの末にカーソルが入り修正が出来るようになりますね。

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

Excelのショートカット一覧はこちらで書いております。
http://yandss.p2.weblife.me/shortcutkey.html

12/30/2017

Excel。振込日などで使う土日祝日を除いた前日・後日を求める方法【WORKDAY】

Excel。振込日などで使う土日祝日を除いた前日・後日を求める方法

<DATE関数+WORKDAY関数 EOMONTH関数>

新年を迎えると日付関係で確認しておかないことが発生する、
ビジネスマンも多いですよね。

例えば、月末締めの翌々10日払いとか振込日など関連する日があるとは思います。
この支払日や振込日が平日ならば問題はないのですが、
土日祝日に絡んだりすると、とても厄介なんですね。

土日祝日の前に振込しておかなければいけないのか?土日祝日の後でいいのか?

資金繰りに絡んできますので、しっかり把握しておきたいところ。

そこで、今回は、2018年の祝日を確認しつつ、土日祝日を除いた前日、
あるいは後日を求める方法をご紹介してきます。

まず、次の表を用意します。

A1には、2018と入力して、表示形式で単純に、G/標準"年"として、
2018年と表示できるようにしております。

A4:A9も同じように、G/標準"月"として、表示上、1月としてあります。

このあと、これらの数値をDATE関数で日付を求めるために使うので、
数値でないとマズイわけです。

月末の列ですが、
B4には、次の数式を設定してあります。

月末を算出する関数の、EOMONTH関数を使って、
それぞれの月末を算出しております。

開始日には、DATE($A$1,$A4,1)
DATE関数は、日付を作ることができる関数ですね。
このために、先ほど表示形式を使っていたわけですね。

月には、0(ゼロ)。今月末を知りたいので、0(ゼロ)ですね。

あとは、OKボタンをクリックして、オートフィルを使って数式をコピーしましょう。

B4の数式は、
=EOMONTH(DATE($A$1,$A4,1),0)

すると、このようになりましたね。

日付ではなくて、シリアル値で算出されてきましたので、
これも表示形式を使って日付にしましょう。

また今回は、曜日が絡みますので、曜日も表示できるように変更しましょう。

表示形式を、ユーザー定義を使って、yyyy/m/d(aaa)としてみましょう。
曜日月の日付が表示されましたね。

それでは、早速…といきたいところですが、
もっとも大切なデータを事前に作っておく必要があります。

次の表を確認してみましょう。

祝日の一覧表が必要になるのです。

これがないと、Excelが祝日かどうかの判断ができないのです。

作るのは大変だと思いますので、次の行をコピーして使ってください。
祝日 内容
2018/1/1(月) 元旦
2018/1/8(月) 成人の日
2018/2/11(日) 建国記念の日
2018/2/12(月) 建国記念の日の振り替え
2018/3/21(水) 春分の日
2018/4/29(日) 昭和の日
2018/4/30(月) 昭和の日の振り替え
2018/5/3(木) 憲法記念日
2018/5/4(金) みどりの日
2018/5/5(土) こどもの日
2018/7/16(月) 海の日
2018/8/11(土) 山の日
2018/9/17(月) 敬老の日
2018/9/23(日) 秋分の日
2018/9/24(月) 秋分の日の振り替え
2018/10/8(月) 体育の日
2018/11/3(土) 文化の日
2018/11/23(金) 勤労感謝の日
2018/12/23(日) 天皇誕生日
2018/12/24(月) 天皇誕生日の振り替え

先に結果を見てみましょう。
月末の前日か後日かを算出してみるとこのようになります。

4月30日は昭和の日の振り替え休日なので、
祝日前・祝日後ともに日が変わっていますよね。

このように、土日祝日を除くことができる関数。
それが、【WORKDAY関数】です。

WORKDAY関数を使って、C4の数式を作っていきましょう。

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

開始日は、B4+1
日数には、-1
祭日は、$F$4:$F$23
あとは、OKボタンをクリックしてオートフィルで数式をコピーします。

なぜ、開始日で+1して、日数で-1するのかというと、
このWORKDAY関数なのですが日数の引数を0(ゼロ)にすることができないのです。

確認してみるとわかるのですが、

3月31日が土曜日なので、C6の数式を開始日に+1しないで、
日数を0にしてみると、3月31日が算出されてしまうのです。

つまり、日数が0だと土日でも、リアクションしないのです。

なので、+1して-1するという方法を使っています。

それでは、C4の数式は、
=WORKDAY(B4+1,-1,$F$4:$F$23)

同じように、D4の数式を確認しておきましょう。
=WORKDAY(B4-1,1,$F$4:$F$23)
こちらは、-1してから、+1するようにしております。

このように、WORKDAY関数を使うと、
土日祝日を除けることができますが、まずは、祝日一覧表を作ってからですね。

12/29/2017

Excel関数一覧表にアイテムを追加しました。【function:2017/12/29】

Excel関数一覧表にアイテムを追加しました。

<Excel関数一覧>

「Excelテクニック and MS-Office recommended by PC training」の目次サイト
「Officeソフトのスキルアップサイト」のExcel関数一覧表に次のアイテムを追加しました。


CHIDIST カイディスト
カイ二乗分布の上側確率を算出

CHIINV カイインバース
上側累積確率からカイ二乗分布のパーセント点の値を逆算

CHISQ.DIST カイスクウェア・ディスト
カイ二乗分布の確立を算出

CHISQ.DIST.RT カイスクウェア・ディスト・ライトテール
カイ二乗分布の上側確率を算出 Excel2010以降

CHISQ.INV カイスクウェア・インバース
カイ二乗分布の下側確率から確率変数を算出 Excel2010以降

CHISQ.INV.RT カイスクウェア・インバース・ライトテール
上側累積確率からカイ二乗分布のパーセント点の値を逆算 Excel2010以降

CHISQ.TEST カイスクウェア・テスト
カイ二乗検定の上側確率を算出 Excel2010以降

CHITEST カイテスト
カイ二乗検定の上側確率を算出

Officeソフトのスキルアップサイト
https://sites.google.com/view/infoyandss/ホーム

12/28/2017

Officeソフトのスキルアップに追加しました。2017/12/28

Officeソフトのスキルアップに追加しました。

<目次サイト>

このBLOGの記事を、
目次サイトの【Officeソフトのスキルアップ】に追加しました。


COUNTA関数が空白も数える?この時はCOUNTIF関数が登場します。
https://infoyandssblog.blogspot.jp/2016/09/excelcountcountacountif.html


折れ線グラフのマーカー。最高値最低値が変わったら自動的に色が変わるグラフ
https://infoyandssblog.blogspot.jp/2016/09/excelgraph.html


VLOOKUP関数の範囲をAccessのテーブルでやりたいけどどうしたらいい?
https://infoyandssblog.blogspot.jp/2016/09/excelvlookupaccess.html


無駄な印刷範囲をいちいち変えるのが面倒!自動で印刷範囲選択する方法
https://infoyandssblog.blogspot.jp/2016/09/excelprint.html


Wordでの差し込み印刷。しかし、表示形式が変?どうやったらいいの?
https://infoyandssblog.blogspot.jp/2016/09/excelword.html

12/27/2017

Excel。IF関数を使わないで、生年月日から干支を算出方法?!【Zodiac】

Excel。IF関数を使わないで、生年月日から干支を算出方法?!

<MID+MOD+YEAR関数>

面白いリクエストがありまして、
「スタッフさんの生年月日から『干支』がわかるようにしたいんだけど~」
とのこと。

IF関数を使ってみたら、わからなくなっちゃって…だそうでして。
ということで、ちょっと考えてみましょう。

次のような表があります。

今年2017年の干支は、酉。
というように表示することができればいいわけですね。

このような場合は、
どのようなルール(パターン)になっているかを見つけることが
出来るのかがカギになります。

干支のルールは、
子丑寅…と始まって12種類で元の子に戻るというルールなので、
12で除算してみて、その余りを使うことで、
どの種類なのかを判断することが出来そうですね。

とりあえず、C列に、余りを求める関数のMOD関数を使って、
生年月日を12で除算してみましょう。

C2にMOD関数ダイアログボックスを表示しましょう。

数値には、年数を入れたいので、年数を算出することができる、
YEAR関数を使って算出させますので、YEAR(A2)

除数には、干支の数である12

それでは、OKボタンをクリックして、オートフィルで数式をコピーしましょう。

D列には、ルール(パターン)確認のため、自分で干支を入力してみました。

これによると、0(ゼロ)つまり、余りがない年は、
申年だということがわかりましたね。

0ならば、申。

1ならば、酉というように算出させるには、
IF関数を使うといいように思えますが…

IF関数のネストが多くなりすぎて煩雑になってしまうので、お勧めできません。

そこで、登場するのが、MID関数を使うと便利ですね。

CHOOSE関数でもいいのですが、少し数式が長くなります。

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

このMID関数は、
文字列の指定の位置から指定の文字数分を抽出することが出来る関数ですね。

文字列には、"申酉戌亥子丑寅卯辰巳午未"

子からじゃありませんよ。ここがポイント。

0が申だったので、申からスタートさせます。

開始位置は、先程確認した、数式を使いますので、MOD(YEAR(A2),12)。

ただこれでは、0になってしまいます。
0文字目では、MID関数が機能しませんので、+1をしてあげるといいですね。

文字数は、1文字を抽出するので、1

それでは、OKボタンをクリックして、
オートフィルで数式をコピーして確認してみましょう。

このように、『干支』がちゃんと算出することが出来ましたね。

ちなみに、B2の数式は、
=MID("申酉戌亥子丑寅卯辰巳午未",MOD(YEAR(A2),12)+1,1)
となっていますね。

なお、CHOOSE関数を使ってみると…
=CHOOSE(MOD(YEAR(A2),12)+1,"申","酉","戌","亥","子","丑","寅","卯","辰","巳","午","未")

今回は、干支というお題でしたが、
文字列の抽出という方法を使うことで、
IF関数を使った時の煩雑な数式から少しは
解放されるケースもあるかもしれませんので、
色々考えてみるといいかもしれませんね。

12/26/2017

Excel関数一覧表にアイテムを追加しました。【function:2017/12/26】

Excel関数一覧表にアイテムを追加しました。

<Excel関数一覧>

「Excelテクニック and MS-Office recommended by PC training」の目次サイト
「Officeソフトのスキルアップサイト」のExcel関数一覧表に次のアイテムを追加しました。

FDIST エフディスト
F分布の右側(上側)確率を算出する

F.DIST エフ・ディスト
F分布の確立を算出する

F.DIST.RT エフ・ディスト・ライトテール
F分布の右側(上側)確率を算出する Excel2010以降

FINV         エフインバース
F分布の上側確率から確率変数を算出する

F.INV エフ・インバース
F分布の下側確率から確率変数を算出する Excel2010以降

F.INV.RF エフ・インバース・ライトテール
F分布の上側確率から確率変数を算出する Excel2010以降

FTEST エフテスト
F検定の両側確率を算出する

F.TEST エフ・テスト
F検定の両側確率を算出する Excel2010以降

Officeソフトのスキルアップサイト
https://sites.google.com/view/infoyandss/ホーム

12/25/2017

今週のFacebookページの投稿 2017/12/18-2017/12/24

今週のFacebookページの投稿 2017/12/18-2017/12/24

<Facebookページ>

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

12月18日
Excel。ショートカット。
Ctrl+fで、検索ダイアログボックスが表示されますね。

12月19日
Excel。ショートカット。
Ctrl+hで、置換ダイアログボックスが表示されますね。

12月20日
Excel。ショートカット。
Ctrl+nで、新しいブックが登場しますね。新規作成機能ですね。

12月21日
Excel。ショートカット。
Ctrl+oで、ファイルを開くダイアログボックスが登場しますね。
知っていると便利ですね。

12月22日
Excel。ショートカット。
Ctrl+sで、上書き保存ですね。これは結構有名なショートカットですね。

12月23日
Excel。ショートカット。
Ctrl+pで、印刷ダイヤログボックス。
Excel2010はバックステージビューが表示されます。

12月24日
Excel。ショートカット。
Ctrl+;で、今日の日付が表示できます。セミコロンなんですね。

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

Excelのショートカット一覧はこちらで書いております。
http://yandss.p2.weblife.me/shortcutkey.html

12/24/2017

Excel。グラフ縦軸の表示単位を”万”にしても、データラベルは”万”にしない方法【Display unit】

Excel。グラフ縦軸の表示単位を”万”にしても、データラベルは”万”にしない方法

<縦軸>

集合縦棒グラフなどの縦軸ですが、
数値の値が大きい場合、
表示単位を【単位:万円】というように表示させることは、
簡単なのですが、少し問題があるのです。

まず、下記の表から集合縦棒グラフを作っていきます。

では、A1:B6を範囲選択して、集合縦棒グラフを作ってみましょう。

縦軸の桁数が多いので、【単位:万円】にしていきます。

縦(値)軸をダブルクリックするか、
書式タブのグラフ要素を縦(値)軸にして、選択対象の書式設定をクリックします。

軸の書式設定ダイアログボックスが表示されます。

「表示単位」のボックスが『なし』になっているのを、万に変更します。

グラフの縦(値)軸が次のようになりましたね。

縦(値)軸の表示が省略された形。

今回は、【単位:万円】にすることができました。

この表示方法のやり方自体は、比較的簡単なので、
使うこともあるかと思いますが、今回の本題はこれから。

では、このグラフを引き続き使って、それぞれの売上がいくらなのか、
わかりやすいように集合縦棒グラフにデータラベルを表示していきますので、
レイアウトタブにある、「データラベル」の外側を選択してみましょう。

集合縦棒グラフにデータラベルが表示されたのはいいのですが、
なんと、縦(値)軸同様に、
こちらも、【単位:万円】と同じように表示されてしまっていて、
数値がわかりません。

データラベルの書式設定にある、表示形式をみても、
数値を省略しないで表示する方法がありません。

では、諦めるしかないのでしょうか?

そこで、次の方法を使うことで、この問題を解決することが出来ます。

最初に、表を次のようにアレンジします。

C列に、B列の売上高と同じデータを用意します。

範囲選択はA1:C6として、改めて、集合縦棒グラフを作成します。

凡例を消し、売上高2の縦棒グラフをクリックして、
書式タブの選択対象の書式設定をクリックして、
データ系列の書式設定ダイアログボックスが表示されます。

系列オプションの「使用する軸」を第2軸に変更して、
閉じるボタンをクリックします。

第2軸が表示されたことを確認して、
まだ売上高2の棒グラフを選択しているので、
デザインタブの「グラフの種類の変更」から折れ線グラフを選択して
グラフを変更します。

グラフはこのように変わりました。

左側の縦 (値) 軸を、先程の方法で、表示単位を【単位:万円】に変更します。

折れ線グラフをクリックして、
レイアウトタブの「データラベル」から”上”を選択して、
データラベルを表示しましょう。

その後、折れ線グラフの図形の枠線にある「線の色」を”なし”に変更しましょう。

これで、縦(値)軸の表示単位は省略されていても、
データラベルはそのままで表示することができましたね。

最後に、第2縦軸の表示を消して完成です。

レイアウトタブから「軸」をクリックして、”なし”を選択しましょう。

これで完成しました。
 

もし、データラベルで困ったときは、このような方法もありますので、
第2軸を使ってみるといいかもしれませんね。

12/23/2017

Officeソフトのスキルアップに追加しました。2017/12/23

Officeソフトのスキルアップに追加しました。2017/12/23

<目次サイト>

このBLOGの記事を、
目次サイトの【Officeソフトのスキルアップ】に追加しました。


セルの中の特定の文字を数えるには、どうやったらいいの?
https://infoyandssblog.blogspot.jp/2016/10/excelcount.html


FV関数は、積立金で活躍する関数です。
https://infoyandssblog.blogspot.jp/2016/10/excelfv.html


数えるは数えるでも、一番多くあるデータを数えるにはどうしたらいい?
https://infoyandssblog.blogspot.jp/2016/10/excelmodesngl.html


小計を含まずに、大量のデータから、最大値を見つける方法
https://infoyandssblog.blogspot.jp/2016/09/excelsubtotal.html


グラフの横軸。最大値・最小値で自動的に文字の色を変える方法
https://infoyandssblog.blogspot.jp/2016/09/excelgraph_27.html


別ブックのデータをSUMIF関数で算出。しかし、別のブックを閉じちゃうと!
https://infoyandssblog.blogspot.jp/2016/09/excelbooksumif.html

12/21/2017

Excel。入力規則で小数点第一位までしか入力できないようにしたい【Decimal point】

Excel。入力規則で小数点第一位までしか入力できないようにしたい

<入力規則+INT関数>

次のような出退勤管理の表があります。

出勤・退勤は、8:28に出勤ならば、8.5というように、
30分毎に0.5で区切って入力する表の場合、
間違えて、小数点第一位以降小数点を入力させないようにしたいとします。

このように、入力にルールを設けるには、入力規則を使うといいのですが、
小数点第一位以降の小数点を入力できないようにするには、
少しアイディアが必要なんですね。

では、入力規則を確認してみましょう。

B3:C7を範囲選択して、データタブの「データの入力規則」をクリックして、
データの入力規則ダイアログボックスを表示しましょう。

設定タブの条件設定にある入力値の種類を「小数点数」にしてみて、
データを「次の値以上」で最小値を「0.1」にして
OKボタンをクリックしてみたらどうなるのでしょうか?

では、B3に8.5と入力してみると、問題なく入力できます。

しかし、8.55と入力してみましょう。

小数点第二位まで入力出来てしまいましたよね。

これでは、今回やりたいこととは違います。

最小値を0.1にしても、0.11は入力出来てしまいます。
つまり、小数点第一位までという入力規則を用意されている
入力値の種類をそのまま使うことは難しいようですね。

そこで、ユーザー設定を使ってみましょう。

改めて、B3:C7に入力規則を設定していきます。

なお、入力規則はクリアしておきます。

設定タブの入力値の種類を「ユーザー設定」にすると、
数式ボックスが登場しますので、そこに、

=B3*10=INT(B3*10)

という数式を設定します。

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

では、B3に8.55と入力してみましょう。

今回は、エラーメッセージが表示されて、入力することが出来ませんね。

これで、今回の目的である、
小数点第一位までしか入力することが出来ないように
入力規則を使うことで出来ました。

では、数式に設定した、

=B3*10=INT(B3*10)

を確認してみましょう。

B3の値を10倍した値、先程の8.5を使ってみると、85。

INT関数のB3の値を10倍した値は、8.5だと、85なので等しくなります。

8.55の場合どうなるのでしょうか?
B3の値を10倍した値、8.55を使ってみると、85.5。

8.55をINT関数のB3の値を10倍した値は、85.5なのですが、
INT関数を使うことで整数化されていますから、85になり、
85.5と85を比べますので、等しくない。

つまり、小数点第一位以降の数値が入力されているということが
わかるようになっています。

なお、小数点第二位としたい場合は、100倍すれば対応可能ですよ。

簡単な数式ですが、入力規則と合わせて使うと効果抜群ですね。