Excelテクニック and MS-Office recommended by PC training : 6月 2017

6/30/2017

Excel。企業業績で使う数式はバラしてみると意味が見えてきます。第2弾【formula】

Excel。企業業績で使う数式はバラしてみると意味が見えてきます。第2弾

＜計算式＞

先日、企業業績の判断などをExcel使ってご紹介することがありまして、
その講義のために専門家さんから教えていただいた、
数式をご紹介していきます。

Excelは使えても、計算式がわからないと、資料を作れませんので、
計算式を知っていて損はないようです。

ということで、量が多かったので、残りの計算式をご紹介する計算式の第2弾です。

【売上高総利益率】
売上高総利益率は、売上高に対する売上総利益の割合を示す指標ですね。
売上高総利益率　＝　売上総利益（粗利益）/　売上高

売上総利益（粗利益）　＝　販売数量　×　商品単価　×　粗利益率

【販売数量】
販売数量を増加させるためには、次の3つの要素が重要になりますよね。
販売数量　＝　顧客数　×　購入数　×　リピート数

先日教えていただいたものの中に、
粗利益率が高い場合には、『数量を増やす戦略』
粗利益率が低い場合には、『売価を上げる戦略』か
『仕入原価を下げる戦略』だそうです。

【売上高営業利益率】
売上高営業利益率は、売上高に対して、
本業からの利益をどの程度生み出すことが出来たのかという指標ですね。

売上高営業利益率　＝営業利益　/　売上高　×100%
なので、
売上高営業利益率　＝営業利益　／粗利益　×　粗利益　/　売上高　×100%

【総資本回転率】
総資本回転率は、総資本の回収速度を示し、
総資本の運用効率を示す指標ですね。

総資本回転率　＝　目標売上高　/　総資本

なお、目標売上高はどのように算出するのかというと、
目標売上高　＝　総資本　/　粗利益率
で算出できます。

【流動比率】
流動比率は、
短期的な負債を支払う資金がどのぐらいあるのかを示す指標ですね。

流動比率　＝　流動資産　/　流動負債　×100%
で算出することができますよ。

【自己資本比率】
自己資本比率は、企業が使用する総資本のうち
自己資本の占める割合がどの程度あるのかがわかる指標ですね。

自己資本比率　＝　自己資本　/　総資本　×100%

【固定長期適合率】
長期に使える資金のうち、
どれだけ固定資産に運用しているのかを示す指標ですね。

数値が低いほうが優良なんですね。

固定長期適合率　＝　固定資産　/　（自己資本　+　固定負債）　×100%
また、固定比率というのもあります。

固定比率　＝　固定資産　×　自己資本　×100%

このように、様々な計算式があるそうですので、
これらの計算式をExcelに落とし込んで、使っていけば、
様々な数値を導き出し、
分析などに使える資料が作れますので、
今までの資料をさらに、パワーアップしていけるといいですね。

6/27/2017

Excel。全角文字しか入力させない入力規則の設定方法【Input rule】

Excel。全角文字しか入力させない入力規則の設定方法

＜LEN関数・LENB関数・JIS関数・入力規則＞

Excelには様々な関数が用意されているのですが、
意味はわかるけど、どんな時に使ったらいいの？という関数の一つに、
LENB関数というのがあります。

このLENB関数は、文字列のバイト数を算出する関数なのですが、
イマイチ使い道がないというか、わかりにくいということなので、
入力規則と組み合わせて説明をしております。

そこで、今回は、LEN関数とLENB関数と入力規則を使って、
『全角文字』しかセルに入力できないようにするテクニックをご紹介していきます。

まずは、LEN関数とLENB関数のそれぞれを確認してみましょう。

A2は、1-1が半角で入力されています。
A3は、１－１と全角で入力されています。
B2をクリックして、LEN関数ダイアログボックスを表示しましょう。

文字列には、A2をクリックして、OKボタンをクリックしてみましょう。

また、オートフィルで数式をコピーB3にしておきましょう。

B2もB3も5と算出されましたね。

LEN関数はセル内の文字数をカウントする関数なので、
半角全角を区別することなく算出します。

今度は、C列のLENB関数を使って、算出してみましょう。

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

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

B3にも、オートフィルで数式をコピーしておきましょう。

今回は、算出された結果がかわりましたね。

これは、半角全角の違いによるものなのですが、
全角は半角2文字分なので、各文字が2バイトなので、
それが5個で、C3には10と算出されたわけです。

C2は、半角なので、半角の文字は1文字なので、本町までは2文字×2で、4。

1-1は半角なので、3。4+3で7。という結果になったわけです。

このように、LENB関数は、
半角か全角かの判断をつけるのに都合がいい関数なのです。

それでは、
A4に全角しか入力することができない入力規則を
設定していくことにしましょう。

A4をクリックして、データタブの入力規則をクリックして、
データの入力規則ダイアログボックスを表示しましょう。

設定の入力値の種類を「ユーザー設定」にして、

数式には、
=LEN(A4)*2=LENB(A4)
と入力しましょう。その後、OKボタンをクリックします。

これは、A4の文字数の2倍が、
バイト数で算出した結果と同じならば、全角。

そうでなければ、どこかに半角が紛れているということがわかるわけです。

それでは、
本町1-1
と入力してみましょう。

半角の文字が入っているので、入力は出来ないはずです。

このように、入力することは出来ませんでした。

このような時に、LENB関数を使ってみるといいかもしれませんね。

ちなみに、『全角文字』しかセルに入力できないようにする方法は、
JIS関数を使う方法もあります。

A5に入力規則を次のように設定してみましょう。

入力値の種類を「ユーザー設定」にして、
数式には、
=A5=JIS(A5)

このように、JIS関数を使っても全角のみの入力規則設定をすることが出来ます。

JIS関数は、半角英数カナ文字を全角英数カナ文字に変換する関数なので、
JIS関数を使って算出された文字と入力された文字が合致しなければ、
半角があるという判断になるので、入力できないという仕組みですね。

6/25/2017

今週のFacebookページの投稿　2017/06/19-2017/06/25

＜Facebookページ＞

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

6月19日
Wordショートカット。
Ctrl + 5 キーで行間を 1.5 行に設定します。

6月20日
Wordショートカット。
Ctrl + 0 (ゼロ) キーで段落前の行間を 1 行または 0 行にします。

6月21日
Wordショートカット。
Ctrl + E キーで段落の中央揃えと左揃えを切り替えます。

6月22日
Wordショートカット。
Ctrl + J キーで段落の両端揃えと左揃えを切り替えます。

6月23日
Wordショートカット。
Ctrl + R キーで段落の左揃えと右揃えを切り替えます。

6月24日
Wordショートカット。
Ctrl + L キーで段落を左揃えで配置します。

6月25日
Wordショートカット。
Ctrl + M キーで段落を左からインデントします。

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

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

6/24/2017

Excel。ヒストグラムを作るために、度数分布表を作ってみよう。【Histogram】

Excel。ヒストグラムを作るために、度数分布表を作ってみよう

＜COUNTIFS関数　SUM関数＞

平均値・最大値や中央値のデータも重要ですが、
それ以外でも、度数分布表を作ってみると、
データに含まれる偏りを把握することができますので、
今回は、度数・相対度数・累積度数・累積相対度数の4つを算出してみましょう。

次の表があります。

E列の階級（年齢）は、10歳ごとにわけて0～9のように入力しておきます。

まずはF列の度数を求めていきます。

度数は、階級に所属しているデータの個数のことです。

F3は、年齢が0歳から9歳までの人数を求めます。

このような期間などの間を算出するには、
COUNTIFS関数を使いますので、
F3をクリックして、COUNTIFS関数ダイアログボックスを表示します。

検索条件範囲1には、$C$3:$C$22を入力します。
検索条件1には、">=0"

比較演算子+数値の場合には、ダブルコーテーションで囲む必要があります。
検索条件範囲2には、$C$3:$C$22を入力します。

検索条件2には、"<=9"

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

F3の数式は、
=COUNTIFS($C$3:$C$22,">=0",$C$3:$C$22,"<=9")

次に、相対度数を求めていきます。

相対度数とは、
データ全体から見た度数の相対的な割合を示す数値のことです。

わかりやすくいえば、『構成比』のことです。

どこかに、度数の合計値を算出しておいて、
その数値を使って、除算すればいいのですが、
今回は、合計値を算出していませんので、G3に、次の数式を設定していきます。

=F3/SUM($F$3:$F$9)

F3を合計値で除算するわけですから、
SUM関数を使ってあげればいいわけですね。

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

これで、相対度数を求めることができましたね。
H列は累積度数を算出していきます。

累積度数とは、「度数の累計」のことを指しますので、
SUM関数を絶対参照の小レボレーションテクニックで、
累計を算出していきましょう。

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

数値1。つまり範囲選択ですが、$F$3:F3
と入力します。

累計を求めるときには、
範囲選択のスタートを絶対参照にすることによって、
範囲が伸びても、
それに合わせて、合計値を求めることが出来る事によって、
累計を算出することが出来ます。

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

=SUM($F$3:F3)

今の所表は、このようになっていますね。

あとは、「累積相対度数」を算出しましょう。

先ほどの、累積度数と同じ、累計の算出方法で求めることが出来ますので、
I3の数式は、

=SUM($G$3:G3)

あとは、オートフィルを使って数式をコピーすれば、
度数分布表が完成しましたね。

このように、データから、色んな項目のデータを算出出来ますので、
分析するための資料の一つとして、作成してみてはいかがでしょうか？

6/21/2017

Excel。Excel2013で上下対称グラフを作ってみよう【Up and down】

Excel。Excel2013で上下対称グラフを作ってみよう。

＜上下対称グラフ＞

プラスとマイナスの数値を一つの棒グラフで表す【上下対称グラフ】。

そのつくり方を確認してみましょう。

作りたい【上下対称グラフ】は、このようなグラフです。

作り方自体はとても簡単なんですよ。

では、次の表から集合縦棒グラフを作って行きましょう。

A1:C7を範囲選択して、
挿入タブの縦棒グラフから2-D縦棒グラフの「集合縦棒」を
クリックしましょう。

まずは、次のようなグラフが表示されてきました。

グラフタイトルには、「上期売上経費関係」など入力しておきます。

最初に、プラスの棒グラフとマイナスの棒グラフを
一つに合体させる作業から取り掛かります。

マイナスの経費の棒グラフをクリックしましょう。

書式タブのグラフ要素が、系列 "経費"になっていることを確認したら、
「選択対象の書式設定」をクリックします。

左側に、データ系列の書式設定作業ウインドウが表示されてきます。

系列のオプションにある、「系列の重なり」を100%にすることで、
プラスの棒グラフとマイナスの棒グラフの間隔がなくなり、
ひとつの棒グラフのようになります。

これで、合体しましたね。

なお、この一つにする「系列の重なり」ですが、2-Dしかありません。
3-Dには、奥行きに関するコントロールはあるのですが、
重なりを変化させることは出来ないようです。

次の作業は、横（項目）軸が、棒グラフの上に乗ってしまっていますので、
棒グラフの下端に移動させます。

横（項目）軸をクリックすると、
作業ウインドウが、軸の書式設定に変わりますので、

軸のオプションのラベルにある、
「ラベルの位置」を下端/左端に変更しましょう。

これで、横（項目）軸をグラフの下側に移動させることが出来ましたね。

ついでに、縦 (値) 軸も、見やすいように修正しておきましょう。

縦 (値) 軸をクリックして、軸の書式設定作業ウインドウの軸のオプションにある、

境界値の最小値と最大値を、
最小値-100
最大値350
と入力します。

これで、縦 (値) 軸がかわり、棒グラフ全体が大きくなり見やすくなりました。

あとは、データラベルを表示すれば完成ですね。

プラスの売上の棒グラフをクリックしておいて、
デザインタブの「グラフ要素を追加」をクリックして、
データラベルから、表示したい場所をクリックしましょう。

今回は、「内側」にしてみました。

マイナスの経費の棒グラフをクリックして、
同じようにデータラベルを表示させていきましょう。

これで完成しましたね。

グラフはパーツで構成されていますので、
色々確認して試すことによって、様々なグラフを作ることが出来ますので、
アレコレ作ってみてください。

6/19/2017

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

＜Facebookページ＞

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

6月12日
Wordショートカット。
Ctrl + Shift + Q キーで選択範囲を Symbol フォントに変更します。

6月13日
Wordショートカット。
Ctrl + Shift + * キー (テンキーのアスタリスクは使用できません)
で印刷されない文字を表示します。

6月14日
Wordショートカット。
Shift + F1 キー (次に確認する書式が設定されたテキストをクリックします)
でテキストの書式を確認します。

6月15日
Wordショートカット。
Ctrl + Shift + C キーで書式をコピーします。

6月16日
Wordショートカット。
Ctrl + Shift + V キーで書式を貼り付けます。

6月17日
Wordショートカット。
Ctrl + 数字 1 キーで行間を 1 行に設定します。

6月18日
Wordショートカット。
Ctrl + 2 キーで行間を 2 行に設定します。

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

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

6/18/2017

Access。添付ファイルの種類で抽出するクエリ・添付ファイルを数えるクエリ【Attachment】

Access。添付ファイルの種類で抽出するクエリ・添付ファイルを数えるクエリ

＜Access 添付ファイル＞

次のようなテーブルがあります。

会議で使用した資料ファイルを管理しているテーブル「T会議資料一覧」で、
会議資料が添付ファイルとして管理されているわけです。

デザインビューで確認してみると、

当然のことながら、
テーブルではどのようなファイルが添付されているのかが、
わかりません。

そして、Wordファイルだけを抽出したいと思っても、
テーブルではそのリクエストをかなえる事は出来ません。

そこで、クエリを使うことで、
添付ファイルの種類で抽出することもでき、
そして、添付してあるファイル名も確認することが出来ます。

意外と知られていないようなので、
今回は、『添付ファイルの種類で抽出』する方法をご紹介していきます。

では、T会議資料一覧を使用して、
クエリを作成して行きますので、作成タブのクエリデザインをクリックします。

フィールドに、登録ID・会議名・会議資料.FileName・会議資料.FileType　
の4つを設定します。

そして、今回は、Wordファイルがあれば抽出してみたいので、
会議資料.FileTypeフィールドに”docx”という抽出条件が設定されています。

たった、これだけで、
資料で使われたWordファイルを抽出確認することが出来るのです。

Q会議資料一覧という名前で保存しておきます。

それでは、確認してみましょう。

データシートビューに変えてみると、

このように、Wordファイルの資料を確認することが出来ました。

添付ファイル型には、
会議資料.FileData・会議資料.FileName・会議資料.FileType
といった情報が含まれているわけですね。

今回は、Wordファイルを抽出したかったので、
会議資料.FileTypeフィールドを使ってあげる事で、

まずは、ファイルの種類を抽出することが出来るのです。

よって、会議資料.FileTypeの抽出条件に、”docx”と設定したわけです。

なので、JPEGならば、JPG。

Excelならば、xlsxというように拡張子を抽出条件に
設定してあげれば簡単に抽出することが出来ます。

そして、会議資料.FileNameフィールドで使うことによって、
添付してあるファイル名も確認することが容易になります。

そして、添付ファイル数を確認したいのであれば、
次のようなクエリを作成するといいですね。

会議資料.FileTypeを2つ設定します。

そして、集計行を表示させて、
ひとつを「グループ化」もうひとつを「カウント」にします。

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

拡張子ごとの件数が集計されていますよね。

これで、このテーブルの添付ファイル数や、
どの種類のファイルが、いくつあるのかも、把握することが出来ましたね。

このように、添付ファイルの管理もクエリで行うことが出来ますよ。

6/15/2017

Excel。条件付き累計（項目別累計）の算出方法をご紹介【SUMIF】

Excel。条件付き累計（項目別累計）の算出方法をご紹介

＜SUMIF関数・SUM関数＞

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

C列に消費備品数というフィールドがありまして、
その支店別累計をD列に算出したいという表なのですが、

さて、どうやったら、
支店別累計を算出できるのでしょうか？
というのが、今回の目的。

累計自体は、
SUM関数と絶対参照をコラボレーションすれば、
簡単に数式を作成することが出来ましたね。

ということで、

まずは「おさらい」で、
D列に、消費備品数の累計を算出してみましょう。

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

数値1には、
$C$3:C3
と設定します。

範囲選択の始点だけに、絶対参照を設定するのは、始点を固定して、
終点の範囲は移動することによって、
合計したい範囲をずらしていくことによって、累計を求めることが出来ます。

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

これで累計を算出することが出来ました。

では、本題の条件付き累計といいますか、
項目別に累計を算出するにはどうしたらいいのでしょうか？

基本的な考え方は、変わりません。

変わるのは、関数。

すなわち、SUM関数をSUMIF関数に変更してあげればいいわけです。

それでは、確認していきましょう。

D列の累計は削除しておいて、
D3にSUMIF関数ダイアログボックスを表示しましょう。

範囲には、$B$3:B3を設定します。

これは、検索条件のデータが入っている範囲という意味の、
範囲ですので、
今回は、支店名別の累計を算出したいので、
B列の支店名が該当範囲になりますね。

検索条件には、B3
下方向にオートフィルで数式をコピーしていきますので、
この検索条件には、セル番地を指定していきます。

間違っても、「新宿」という文字を入力してはいけません。

合計範囲には、$C$3:C3

これは、先程の累計で説明しましたが、始点を固定しておく方法で、
合計範囲を伸ばしていくことが出来ますので、
始点のみを絶対参照を設定しておきます。

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

ちなみに、D3の数式は、
=SUMIF($B$3:B3,B3,$C$3:C3)

これで、

条件付き累計（項目別集計）

を求めることが出来ましたね。

念のため確認しておくと、

新宿は、70+39+40+40なので、
新宿のD12に算出された累計値は、189で合致しています。

このように、
基本は、SUM関数を使用した累計の算出方法と同じですので、
範囲において、
始点のみに絶対参照を設定しておく方法を知っているだけでも、
作業の効率化が出来るのではないかと思いますので、

機会がありましたら、
SUMIF関数を使った累計の算出をやってみてはいかがでしょうか？

6/12/2017

Excel。マクロ008。可視セルだけを別シートにコピーする【xlCellTypeVisible】

Excel。マクロ008。可視セルだけを別シートにコピーする

＜VBA＞

事務職でも、少しExcel VBAを知っていると、
確かに作業効率は改善されるようですが、
難しいと感じている人も多いようです。

とはいえ、少しずつでいいので、慣れていくといいですね。

ということで、
今回は、

【可視セルだけを別シートにコピーする】

というケースをご紹介していきます。

可視セルだけを別シートにコピーするという作業は、
発生する頻度が高い処理ですよね。

そこで、この作業を効率化したいというわけですね。

まず、次の表があります。

2行目から10行目のデータが非表示になっている表です。

この可視セルを、『貼り付け』というシートにコピーしたいとします。

データの大きさが異なることが予想されますので、
範囲指定をすると効率が悪化しますので、

Range("a1").CurrentRegion.SpecialCells(xlCellTypeVisible).Select

という構文を使ってあげるといいですね。

お馴染みの、CurrentRegion は、A1から連続してあるセル範囲。

すなわち表ということですね。

そして、引数が、xlCellTypeVisible　としております。

このxlCellTypeVisibleは、Visible。
すなわち、『可視セル』を意味します。

なので、
「この文書で、A1からの表の可視セルを選択」
という意味になります。

では、次のようにVBAを作ってみました。

Sub 可視セル選択01()
Range("a1").CurrentRegion.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Worksheets("貼り付け").Select
Range("a1").Select
ActiveSheet.Paste
End Sub

範囲選択して、コピーして、貼り付けシートを選択して、
そのA1を選択して、貼り付け

という作業をしている構文。

これで、問題はなく、
貼り付けシートにコピーして貼り付けを行うことが出来るのですが、
マクロの記録で作ったような構文で、
Selectがアチコチにあって、逆にわかりにくい印象になっています。

そこで、構文を作っていくときに、COPYメソッドを入力すると、

COPY([Destination])とヒントが表示されてきます。

このように、何かプラスアルファが出来る場合には、
効率的に出来るヒントが潜んでいることがあります。

このDestinationは、
『コピー先のセル範囲を指定』することができる引数なのです。

つまり、このDestinationを使えば、わざわざ、
「シートを選択」する行を作らなくても、まとめて、構文を作れるわけです。

ということで、Destinationを使って、作り直してみましょう。

Sub 可視セル選択02()
Range("a1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy Destination _
:=Worksheets("貼り付け").Range("a1")
End Sub

このように、たった一行だけのマクロで、
別シートに可視セルのデータをコピーすることが出来るのです。

こちらのほうが、わかりやすいですし、見やすいしので、
修正が合ったりした場合の変更もしやすいですよね。

さらに、Destinationは、省略することができますので、

Range("a1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy Worksheets("貼り付け").Range("a1")

としてもOKですね。

【可視セルを別シートにコピー】を紹介しましたが、
可視セルに限らず、
別シートにコピーするということはスタンダードな処理なので、
機会がありましたら、
是非【別シートにコピー】のマクロを作ってみてはいかがでしょうか？

6/11/2017

今週のFacebookページの投稿　2017/06/05-2017/06/11

＜Facebookページ＞

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

6月05日
Wordショートカット。
Ctrl + Shift + D キーでテキストに二重下線を付けます。

6月06日
Wordショートカット。
Ctrl + Shift + H キーで隠し文字書式を適用します。

6月07日
Wordショートカット。
Ctrl + I キーで斜体の書式を適用します。

6月08日
Wordショートカット。
Ctrl + Shift + K キーで英字を小型英大文字に設定します。

6月09日
Wordショートカット。
Ctrl + 等号 (=) キーで下付き文字を適用します (間隔は自動調整)。。

6月10日
Wordショートカット。
Ctrl + Shift + 正符号 (+) キーで上付き文字を適用します (間隔は自動調整)。

6月11日
Wordショートカット。
Ctrl + Space キーで手動で設定された文字書式を解除します。

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

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