Excel Technique_BLOG Categoryに追加しました。2020/3/13

Excel Technique_BLOG Categoryに追加しました。

＜目次サイト＞

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

Excel。セル内で改行するにはAlt + Enterだけど沢山あるときはCHAR(10)がお勧め

CHAR関数は、コンピューターの文字セットから、そのコード番号に対応する文字を算出します。そこで、CHAR(10)とすると、これがAlt + Enterと同じセル内の改行の文字コードなのです。

＜続きはこちら＞
Excel。セル内で改行するにはAlt + Enterだけど沢山あるときはCHAR(10)がお勧め
https://infoyandssblog.blogspot.com/2015/03/excelchar10alt-enterchar10.html


Excel。お客の年齢階層別ピラミッドグラフを条件付き書式でつくってみる。

ピラミッドグラフの作り方ですが、グラフで作る必要が無いとした場合、条件付き書式のデータバーを使うともっと簡単に作ることが出来るのです。

＜続きはこちら＞
Excel。お客の年齢階層別ピラミッドグラフを条件付き書式でつくってみる。
https://infoyandssblog.blogspot.com/2015/03/excelpyramidgraph.html

おすすめグラフで、グラフを作るのが簡単になりすぎ。ピラミッドはこう作ります。

おすすめグラフ】で縦棒グラフ作成して、縦棒グラフから消えてしまった、ピラミッドグラフの作り方を紹介しようと思います。

＜続きはこちら＞
おすすめグラフで、グラフを作るのが簡単になりすぎ。ピラミッドはこう作ります。
https://infoyandssblog.blogspot.com/2015/04/excel2013graph.html


ExcelでABC分析パレート図をつくってみよう

ABC分析の表をつかって、パレート図を作ってみようと思います。

＜続きはこちら＞
ExcelでABC分析パレート図をつくってみよう
https://infoyandssblog.blogspot.com/2015/04/excel2013excel2013abc.html

Excel。鍵穴円グラフをつくるには、円グラフとドーナツグラフの合わせ技【Keyhole pie chart】

Excel。鍵穴円グラフをつくるには、円グラフとドーナツグラフの合わせ技

＜鍵穴円グラフ＞

円グラフはアイディア次第で、様々な用途に合わせたグラフをつくることができます。

今回は、Yes・Noで表現する時につかう、『鍵穴円グラフ』の作り方を紹介します。
次のようなグラフです。

ドーナツグラフの中央を塗りつぶしているだけでしょう？と思われるかもしれませんが、Excelのドーナツグラフは、ドーナツの穴を塗りつぶすことはできません。

そりゃ～穴ですから。

このグラフを作るための表を用意しました。

ドーナツグラフをつくることからスタートします。

ドーナツグラフは円グラフよりも汎用性があり、アイディアグラフでは重宝します。

A1:C3を範囲選択して、ドーナツグラフを作ります。

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

今回は、凡例とグラフタイトルを削除します。

またグラフエリアが白だとわかりにくいので、グレーに塗りつぶした状態で説明を続けます。

今回の表の場合、表としてはわかりやすいのですが、グラフを作る時には、系列が逆になってしまっているので、グラフのデザインタブの「行/列の切り替え」をクリックします。

内側のドーナツグラフを円グラフに変更してきますので、内側・外側どちらでも結構ですので、クリックして、ドーナツグラフをアクティブにします。

グラフのデザインタブにある「グラフの種類の変更」をクリックして、グラフの種類の変更ダイアログボックスを表示します。

内側を「ドーナツ」から「円」に変更します。

外側の外は、「ドーナツ」のままですが、第2軸にチェックをいれて、第2軸に変更しOKをクリックします。

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

外周のドーナツグラフをクリックして、書式タブのグラフ要素が、「系列”外”」になっていることを確認して、「選択対象の書式設定」をクリックします。

画面の右側にデータ系列の書式設定作業ウィンドウが表示されてきます。

系列のオプションをつかって変更していきます。

グラフの基線位置を変更して、回転させます。

ドーナツの穴の大きさを変更して、穴を少し小さくします。

グラフのデザインタブにある「グラフスタイル」のスタイル1を使うと、円を縁取っている白色の枠線をいっぺんに消すことができます。

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

内側の円グラフと外周のドーナツグラフのNoにあたるパーツを同じ色で塗りつぶします。

また外周のドーナツグラフのYesにあたるパーツも別の色で塗りつぶしをします。

内側の円グラフにラベルを表示させます。

内側の円グラフをクリックして、グラフのデザインタブの「グラフ要素の追加」にあるデータラベルから「その他のデータラベルオプション」をクリックします。

作業ウィンドウが、データラベルの書式設定作業ウィンドウに変わりましたので、ラベルオプションから「セルの値」を選択し、データラベル範囲ダイアログボックスが表示されます。

A1をクリックして、OKボタンをクリックします。
データラベルが表示されますので、円中央に移動させます。

続いて、外周のドーナツグラフをクリックして、同じようにデータラベルを表示させたら、ラベル内容を、分類名とパーセンテージに変更します。

最後にデータラベルが小さいので、フォントサイズを大きくして完成です。

今週のFacebookページの投稿 2020/3/2-2020/3/8

今週のFacebookページの投稿　2020/3/2-2020/3/8

＜Facebookページ＞

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

3月2日
Excel。COUPNUM関数。
読み方は、クーポンナンバーで、購入日後の利払回数を算出します

3月3日
Excel。COUPPCD関数。
読み方は、クーポンピーシーディーで、購入日より前の直近の利払日を算出します

3月4日
Excel。COVAR関数。
読み方は、コバリアンスで、2組のデータの母共分散を算出します

3月5日
Excel。COVARIANCE.P関数。
読み方は、コバリアンス・ピーで、2組のデータの母共分散を算出します

3月6日
Excel。COVARIANCE.S関数。
読み方は、コバリアンス・エスで、2組のデータの共分散を算出します

3月7日
Excel。CRITBINOM関数。
読み方は、クリテリアバイノムで、累計二項分布が基準値以上になる最小値を算出します

3月8日
Excel。CSC関数。
読み方は、コセカントで、角度の余割を算出します

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

Excel VBA。VBAでOr条件はどう作るの？【Excel VBA】

Excel VBA。VBAでOr条件はどう作るの？

＜Excel VBA：Or条件＞

Excelでは、様々な条件分岐を行って資料を作ります。
例えば、次の表。

A列の店舗名が、新宿または、横浜だったらC列に「関東」と入力したい場合、ExcelではOR関数を使うわけですね。

C2に数式を設定する場合は、
=IF(OR(A2="新宿",A2="横浜"),"関東","")
という数式を設定するわけですね。

オートフィルで数式をコピーすれば、新宿または横浜ならば関東と表示されていることが確認できます。

データを読み込んできたあとに、これと同じように算出したい場合は、Excel VBAをつかうことで、データを読み込みから連動で処理すること可能になりますので、作業効率は改善できる感じがします。

そこで、Excel VBAでは、どのように作ったらいいのでしょうか？
OR関数と同じようにプログラム文を作ってみましょう。

Sub or_part1()
    Dim i As Integer
   For i = 2 To 11
        If Cells(i, "a").Value = "新宿" Or "横浜" Then
            Cells(i, "c").Value = "関東"
        End If
   Next
End Sub

実行してみると、Excelの数式のようにはいきません。エラーが発生します。

急に「型」っていわれても、文字は「”（ダブルコーテーション）」で囲っているし？
実は、Or以降の書き方が間違っているというか、不足しているのです。

次のようにプログラム文を書きなおしてみましょう。
Sub or_part1()
    Dim i As Integer
   For i = 2 To 11
        If Cells(i, "a").Value = "新宿" Or Cells(i, "a").Value = "横浜" Then            Cells(i, "c").Value = "関東"
        End If
   Next
End Sub

では実行してみましょう。

このようにExcel VBAのプログラム文でIF文での条件分岐でOrを使う場合には、面倒ですが、再度、Cells(i, "a").Value = "横浜"と「どこの何が」をExcel VBAに指示してあげないといけません。

しかし、Cells(i, "a").Value = "横浜"と入力する必要があるとしたら、Orの条件がもっと増えた場合プログラム文が、どんどん長くなってしまいますよね。

例えば、A5の梅田を大宮に変更して、新宿または横浜または大宮だったら「関東」としたい場合のプログラム文は、

If Cells(i, "a").Value = "新宿" Or Cells(i, "a").Value = "横浜" Or Cells(i, "a").Value = "大宮" Then

と煩雑になりますし、プログラム文をつくるもの大変です。

そこで、If文を使うよりもSelect Case文を使ったプログラム文ならば、短くなりますし、その分、わかりやすくなります。

では、Select Case文で作ってみましょう。
Sub or_part2()
    Dim i As Integer
    For i = 2 To 11
        Select Case Cells(i, "a").Value
            Case "新宿", "横浜", "大宮"
                Cells(i, "c").Value = "関東"
            Case Else
                Cells(i, "c").Value = ""
        End Select
    Next
End Sub

では実行してみましょう。

A5を大宮に変更して実行しましたので、先程空欄だったC5にも関東という結果が表示されています。

今回のように、Excel と Excel VBAは似ているようで似ていないというものが、ちょこちょこありますので、Excelと同じようにして、上手くいかない場合は、Excel VBAでは、どのようになるのかを調べるといいようですね。

Excel関数辞典 VOL.26。EVEN関数～EXPON.DIST関数

Excel関数辞典　VOL.26。EVEN関数～EXPON.DIST関数

＜Excel関数＞

今回は、ERF関数～ERROR.TYPE関数までをご紹介しております。

EVEN関数
イーブン
数値を偶数に切り上げる
EVEN(数値)

EXACT関数
イグザクト
英字の大文字と小文字を区別して文字列が一致するか比較する
EXACT(文字列1,文字列2)

EXP関数
イクスポネンシャル
オイラー数eのべき乗を算出
EXP(数値)

EXPONDIST関数
エクスポンディスト
指数分布の確率密度関数と累積分布関数を計算する
EXPONDIST(x,λ,関数形式)

EXPON.DIST関数
エクスポン・ディスト
指数分布の確率密度関数と累積分布関数を計算する　Excel2010以降
EXPON.DIST(x,λ,関数形式)

Excel。A～E評価で一番いい評価を抽出するのは意外と大変です。【Alphabet extraction】

Excel。A～E評価で一番いい評価を抽出するのは意外と大変です。

＜CHAR+MIN+CODE関数＞

下記の表があります。

試験1回目から5回目まで行って、それぞれの評価に基づいたA～Eまでの文字が入力されていて、G列には、担当者ごとの評価の中で一番いい評価を抽出しているという表です。

一件目の試験評価が、E・B・D・D・A・Aなので、A評価がG列に抽出されているわけです。

簡単そうに思えますが、意外とG列の一番いい評価を抽出するのが大変なので、確認をしていくことにしましょう。

【MIN関数では対応できない】

最初に考えるとしたら、A～EなのでAとEを比べる、例えば昇順にすれば、Aが一番最初にありますから、MIN関数を使えばいいように思えます。

ではG2にMIN関数で算出してみましょう。

算出された結果は、なんと「0（ゼロ）」。想像していたように算出してくれませんでした。

なぜ、こうなってしまったのかというと、MIN関数。

この関数は、対象が数値でないといけない関数なのです。

ただ、MIN関数を使うというアイディアは悪くないのです。

問題は、数値でないとダメということ。

【CODE関数で文字を文字コードに変換する】

コンピューターの文字というのは、アスキーコードとかUTF8など文字コードを持っていているという特徴があります。

幸い文字コードは数値です。

その文字コードを使えば、A～Eまでのアルファベットであっても数値化することができます。

文字を文字コードに変換することができる関数があります。

それが『CODE関数』
H2にF2の「A」の文字コードが、いくつなのか確認してみましょう。

H2の数式は、
=CODE(F2)
算出された結果は、65です。
Eは69という文字コードをもっています。

このCODE関数を使えばどうにかなりそうですね。

それでは、G2の数式を次のように設定してみましょう。

=MIN(CODE(B2:F2))
MIN関数とCODE関数をネストにしています。

CODE関数の引数は、範囲なので、B2:F2という範囲でも設定することが可能です。

しかし算出された結果をみると…

65と算出されてしまいました。

たしかに、１件目のデータは、Aが算出してほしいので、そのAの文字コードである65が算出されたところまではいいのですが、65という数値ではなく、「A」という文字を表示してほしいわけです。

原因は、CODE関数をつかったことで、文字が数値になってしまったからです。

なので、今度は、文字コードになった数値を文字に変換する「CHAR関数」を使う必要があります。

【CHAR関数で文字にもどす】

G2の数式をさらにアレンジします。

=CHAR(MIN(CODE(B2:F2)))

算出結果を確認して、オートフィルで数式をコピーしてみましょう。

これで、アルファベットの評価を算出することができました。

文字だけでコントロールできない場合は、文字コードとつかうという方法もありそうですね。

2020年2月の閲覧数TOP10をご紹介

2020年2月の閲覧数TOP10をご紹介

＜TOP10＞

2020年1月。
皆様に閲覧していただいた項目のTOP10をご紹介させていただきます。

1位
Excel。一日のタイムスケジュールを管理する24時間横棒グラフを作ってみる
https://infoyandssblog.blogspot.jp/2016/03/excel24hour-schedule24.html

2位
Excel。折れ線グラフを交点0からスタートさせるには？
https://infoyandssblog.blogspot.jp/2013/07/excel0.html

3位
Excel。折れ線グラフの間を塗りつぶしたいけど、どうしたらいいの？
https://infoyandssblog.blogspot.jp/2015/12/excelgraph.html

4位
Excel。料金量がわかりやすい階段グラフの作り方
https://infoyandssblog.blogspot.com/2017/09/excelstaircase-graph.html

5位
Excel。時間経過の折れ線グラフ。実は散布図で作るとより綺麗に描けるのです。
https://infoyandssblog.blogspot.com/2016/08/excelgraph.html

6位
Excel。出勤時間を自動で塗りつぶす日勤シフト表を作ってみる。
https://infoyandssblog.blogspot.com/2015/05/excelroster.html

7位
Excel。あれれ！グラフが表示されない！！そんな時は、第2軸で表示しましょう。
https://infoyandssblog.blogspot.com/2015/10/excelgraph2.html

8位
Excel。横棒グラフの塗りつぶしを、条件で自動的に色分けしたい。
https://infoyandssblog.blogspot.com/2016/10/excelgraph.html

9位
Excel。縦棒グラフに自動的に平均値の線を引くにはどうしたらいい？
https://infoyandssblog.blogspot.com/2015/03/excelgraph.html

10位
Excel。ピボットテーブルで条件付き書式を使ってレコードに反映したいけど…
https://infoyandssblog.blogspot.com/2016/04/excelpivot-table.html