7/11/2020

Excel Technique_BLOG Categoryに追加しました。2020/7/11

Excel Technique_BLOG Categoryに追加しました。

＜目次サイト＞

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

Excel。なんじゃこりゃ！縦棒グラフを作ってみたら横軸が1・2・3ってなっている！

ちゃんと範囲選択はしたんだけど、グラフの横軸が1・2・3…ってなってしまう

＜続きはこちら＞
Excel。なんじゃこりゃ！縦棒グラフを作ってみたら横軸が1・2・3ってなっている！
https://infoyandssblog.blogspot.com/2015/06/excelverticalbargraph-123.html


Excel。氏名を苗字と名前で分割したいというリクエスト　関数編

改めて、氏名を苗字と名前に分割する方法をご紹介していきます。

＜続きはこちら＞
Excel。氏名を苗字と名前で分割したいというリクエスト　関数編
https://infoyandssblog.blogspot.com/2015/06/exceldivide.html


Excel。氏名を苗字と名前に分割。けどちょっとの事で、うまくいきません！ 置換・SUBSTITUTE関数

ちょっとした、些細な事なのですが、苗字と名前に分ける時だけではなくて、よくあるトラブルなので、そのトラブルと対応方法をご紹介してきます。

＜続きはこちら＞
Excel。氏名を苗字と名前に分割。けどちょっとの事で、うまくいきません！
置換・SUBSTITUTE関数
https://infoyandssblog.blogspot.com/2015/07/exceldivision.html


Excel。氏名を苗字と名前に分割するのに、別に関数を使う必要はないのです。

関数にこだわる必要がないならば、もっと簡単に氏名から苗字と名前をそれぞれ、抽出することができるので、今回はその方法をご紹介します。

＜続きはこちら＞
Excel。氏名を苗字と名前に分割するのに、別に関数を使う必要はないのです。
https://infoyandssblog.blogspot.com/2015/07/exceldelimiterposition.html
時刻:
ラベル: ,

7/09/2020

Excel VBA。日付から曜日を求めるにはWeekdayName関数を使うのもあり【Day of the week】

Excel VBA。日付から曜日を求めるにはWeekdayName関数を使うのもあり

＜Excel VBA：WeekdayName関数＞

ルーチンワークや大量のデータなどを高速で処理することができる、マクロ。
Excel VBA。

今回は、データ読み込んだら、曜日がないので、曜日を挿入する方法を確認していきます。

別にマクロを用意しなくても、Excel 上で、Weekday関数やText関数をつかって表示させる方法やセル参照と表示形式で、曜日を算出する方法はあります。

だけど、ちょっと面倒な数式を毎回作るというのも面倒ですし、時間もかかります。

連続している日ならば、数式を設定しなくても、オートフィルで連続コピーでも対応できますが、先頭の日付の曜日を調べなければならず、それもまた面倒です。

そこで、Excel VBAをつくっておけば、簡単に処理してくれるわけですね。

プログラム文自体も難しくありませんが、曜日を算出する方法がいくつかありますので、今回は、3つをご紹介していきます。

次の表は、読み込んだデータです。

【Weebday関数とChoose関数のコラボ】
Excelでもよく使用する、「Weebday関数とChoose関数のコラボ」のプログラム文です。

Sub 曜日()
    Dim youbi As Long
    Dim i As Long
    Dim lastrow
   
    lastrow = Cells(Rows.Count, "a").End(xlUp).Row

    For i = 2 To lastrow
        youbi = Weekday(Cells(i, "A"))
        Cells(i, "b") = Choose(n1, "日", "月", "火", "水", "木", "金", "土")
    Next
End Sub

説明しますと、
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
は、データの最終行を算出しています。
これは、次のFor To Next文で何回繰り返すのかで必要なので、算出させています。

そして、For To Next文の繰り返し処理ですが、
youbi = Weekday(Cells(i, "A"))
Cells(i, "b") = Choose(n1, "日", "月", "火", "水", "木", "金", "土")

Excel VBAにも、Weekday関数が用意されています。

ExcelのWeekday関数と同じで、日曜日が1、月曜日が2というように、曜日ごとの数値を算出します。

その算出されたものは数値なので、日～土の文字に変える必要があります。

次の行の、Choose関数で、数値に対する曜日の文字を割り当てています。

Choose関数もExcelと同じ動きをします。

では、実行してみましょう。
 
このように、曜日を算出することができました。

Excelでお馴染みの方法をExcel VBAに取り込んだ方法ですね。

【Weekday関数とWeekdayName関数】
Choose関数ではなくて、WeekdayName関数をつかう方法もあります。

このWeekdayName関数は、Excelにはありません。Excel VBAにある関数です。

Choose関数は、「"日", "月", "火", "水", "木", "金", "土"」と入力する必要がありますが、結構面倒です。
そこを省略できる関数が、WeekdayName関数です。

Sub 曜日()
    Dim youbi As Long
    Dim i As Long
    Dim lastrow
   
    lastrow = Cells(Rows.Count, "a").End(xlUp).Row

    For i = 2 To lastrow
        youbi = Weekday(Cells(i, "A"))
        Cells(i, "b") = WeekdayName(youbi, True)
    Next
End Sub

Cells(i, "b") = WeekdayName(youbi, True)
引数のTrueに設定すると、「日」「月」と表示されます。
Falseに設定すると「日曜日」と曜日が付きます。

Choose関数よりも省略した方法が、WeekdayName関数ですね。

【Format関数で表示形式をコントロール】
Format関数をつかって、曜日を算出することもできます。

Format関数は、Excelにはありません。
Format関数は、ExcelのText関数と同じように表示形式をコントロールするExcel VBAの関数です。

Sub 曜日()
    Dim i As Long
    Dim lastrow
   
    lastrow = Cells(Rows.Count, "a").End(xlUp).Row

    For i = 2 To lastrow
        Cells(i, "b") = Format(Cells(i, "a"), "aaa")
    Next
End Sub

Format関数をつかったプログラム文です。
Cells(i, "b") = Format(Cells(i, "a"), "aaa")
“aaa”をつかって、表示形式で日付を曜日に変更することができます。

曜日を算出する方法の紹介でした。
時刻:
ラベル: , ,

7/08/2020

Excel関数辞典　VOL.32。GAMMA関数～GAMMALN.PRECISE関数

Excel関数辞典　VOL.32。GAMMA関数～GAMMALN.PRECISE関数

＜Excel関数＞

今回は、GAMMA関数～GAMMALN.PRECISE関数までをご紹介しております。

GAMMA関数
ガンマ
ガンマ関数の値を算出　Excel2013から登場
GAMMA(数値)


GAMMADIST関数
ガンマディスト
ガンマ分布の確立を算出
GAMMADIST(x,α,β,関数形式)


GAMMA.DIST関数
ガンマ・ディスト
ガンマ分布の確立を算出　Excel2010以降
GAMMA.DIST(x,α,β,関数形式)


GAMMAINV関数
ガンマインバース
ガンマ累積分布関数の逆関数の値を算出
GAMMAINV(確率,α,β)


GAMMA.INV関数
ガンマ・インバース
ガンマ累積分布関数の逆関数の値を算出　Excel2010以降
GAMMA.INV(確率,α,β)


GAMMALN関数
ガンマログナチュラル
ガンマ関数の値の自然対数を算出
GAMMALN(x)


GAMMALN.PRECISE関数
ガンマログナチュラル・プリサイズ
ガンマ関数の値の自然対数を算出　Excel2010以降
GAMMALN.PRECISE(x)

時刻:
ラベル: , , , , , , , ,

7/06/2020

Excel。散布図のデータラベルに項目名を表示するのが楽になっています。【Scatter label】

Excel。散布図のデータラベルに項目名を表示するのが楽になっています。

＜散布図＞

Excelもバージョンがアップするごとに、微妙に改善されている機能がたくさんあります。

グラフにも様々な改善があり、便利になっていることがあります。

散布図のデータラベルに項目名を表示することも、改善された一つだといえます。

次の表を用意しました。
 
このデータを使って、散布図を作っていきます。

散布図を作るときのポイントは、店舗名のフィールドは含めないで作成します。

通常、横軸に該当する列を含めないと、「系列1」という項目名になってしまいます。

ここが散布図を作成する時のポイントですね。

では、B1:C7を範囲選択して、挿入タブの「散布図（X,Y）またはバブルチャートの挿入」から散布図を選択します。
 
散布図が表示されます。
今回は、説明の為大きくしたいので、グラフタイトルは削除しております。
 
散布図自体は、簡単に作成できるのですが、
横軸縦軸とも何を意味する数値なのか？

そして、
データ自体どこの店舗のものなのか？
何も表示されていないために、色々修正しないと資料としては、イマイチ使えない状態です。

最初は、データラベルに店舗名を表示する方法から確認していきます。
 
グラフを触っている状態で、グラフのデザインタブの「グラフ要素の追加」にデータラベルがあります。

その中にある「その他のデータラベルオプション」をクリックします。

データラベル書式設定作業ウィンドウが表示されます。
 
ラベルオプションのラベルの内容にある「セルの値」にチェックマークをいれます。
 
データラベル範囲ダイアログボックスが表示されますので、店舗名を表示したいので、A2:A7を範囲選択して、OKボタンをクリックします。

その後、不要になった、「Y値」のチェックマークをはずします。

細かいことかもしれませんが、先に「Y値」のチェックマークを外してしまうと、データラベル自体が消えてしまい、作業ウィンドウも変わってしまい、結果やり直しになるので、注意するといいかもしれません。

あとは、必要に応じて、データラベルの表示場所を変更します。

今回は、「上」に表示します。

グラフはこのように変わりました。
 
項目名をつかって、データラベルを表示することができました。

Excelの昔のバージョンでは、表示するのが大変だったのですが、今は、このように楽に表示することができるように変わりました。
散布図
 
データラベルは完成しましたが、あとは、縦軸ラベル・横軸ラベルを表示したり、横軸の目盛りを修正したりすれば完成ですね。
時刻:
ラベル: , ,

7/05/2020

今週のFacebookページの投稿　2020/6/29-2020/7/5

今週のFacebookページの投稿　2020/6/29-2020/7/5

＜Facebookページ＞

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

6月29日
Excel。IFS関数。
読み方は、イフエス（イフズ）で、1つまたは複数の条件で分岐して異なる計算結果を返す

6月30日
Excel。IMABS関数。
読み方は、アイエムアブスで、複素数の絶対値を算出する

7月1日
Excel。IMAGINARY関数。
読み方は、イマジナリーで、複素数の虚数係数を取り出す

7月2日
Excel。IMARGUMENT関数。
読み方は、アイエムアーギュメントで、複素数の偏角を算出する

7月3日
Excel。IMCONJUGATE関数。
読み方は、アイエムコンジュゲイトで、共益複素数を算出する

7月4日
Excel。IMCOS関数。
読み方は、アイエムコサインで、複素数のコサインを算出する

7月5日
Excel。IMCOSH関数。
読み方は、アイエムコサインハイパーポリックで、複素数の双曲線余弦を算出する

Excelテクニック and  MS-Office recommended by PC training
時刻:
ラベル: ,

7/03/2020

Excel。発注書でお馴染みのCHELING.MATH関数は希望の単位に切り上げる関数です。【Purchase order】

Excel。発注書でお馴染みのCHELING.MATH関数は希望の単位に切り上げる関数です。

＜CHELING.MATH関数＞

注文書に「10個単位でお願いします」とか「ダースでの注文」というように、個数の注文ではなくて、注文先の単位に揃えて注文をしなければいけません。

15個発注したいけど、10個単位だから5個増やして、20個で注文するというようなケースで使用する関数が、CHELING.MATH関数です。

10個や100個など区切りがいい数値ならば、CHELING.MATH関数を使う必要はあまりないかもしれませんが、ダース(12個)での注文となると、少々わかりにくくなってきます。

そのため、誤発注を生じる危険性が増してしまいます。

なお、CHELING.MATH関数は、単位を揃える時に、増加させて単位に揃える、切り上げをする関数ですが、逆に、単位を揃える時に、発注数を減らして、単位を揃える、切り下げをする関数が、FLOOR.MATH関数です。

CHELING.MATH関数とFLOOR.MATH関数は親戚みたいな関数です。

では、次の表を使って確認していきましょう。
商品はどれも、ダース（12個入り）としています。
 
D列の注文合計は、大森店と蒲田店がそれぞれ発注希望数をだしたものの合計値を算出しています。

D3には、お馴染みのSUM関数を設定しています。
=SUM(B3:C3)

そして、F列の箱数ですが、何箱で注文すればいいのか分かるようにした列です。

F3には、単純に、
=E3/12
という除算の計算式を設定しています。

E3をクリックして、CHELING.MATH関数ダイアログボックスを表示します。

手入力でも問題はありませんが、モードというあまりつかわない引数について説明したいので、ダイアログボックスで作成します。
 
数値には、注文合計のD3
基準値には、ダースなので、12

モードは省略することができます。
省略すると、0が設定された場合と同じになります。

今回は、省略します。
このモードについては、あとで説明しますが、モードという引数があるということを確認しておきましょう。

では、OKボタンをクリックして、完成です。

E3には、
=CEILING.MATH(D3,12)
という数式が設定されていますので、オートフィルで数式をコピーしましょう。
 
これで発注数を算出することができました。

ここで、引数のモードについて説明しておきます。
 
このモードは負数の時にどのようにするのかを決めることできる引数です。

省略や0にすると、0に近い数値に切り上げをします。

1と設定すると、0より遠い数値に切り上げをします。

このような違いがあります。

さて、CHELING.MATH関数を使わないで算出しようとすれば、鉛筆の注文合計が133ということは、12で除算した余りを12から減算した分を、133に加算すれば、注文数になるわけですが、不足分を補う必要がないケースもありますので、それを考慮した計算式を作ろうとするとかなり煩雑になります。

ただ、算出することは可能といえば可能です。

このように、Excelには様々な用途で活躍できる関数が用意されていますので、煩雑な計算式になりそうなときがあれば、関数を探してみると使える関数が見つかるかもしれませんね。
時刻:
ラベル: , ,

7/02/2020

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

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

＜TOP10＞

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

1位
Excel。折れ線グラフを交点0からスタートさせるには？



2位
Excel。折れ線グラフの間を塗りつぶしたいけど、どうしたらいいの？



3位
Excel。料金量がわかりやすい階段グラフの作り方



4位
Excel。一日のタイムスケジュールを管理する24時間横棒グラフを作ってみる



5位
Excel。空欄のある行全体を塗りつぶししたいけど、どうやるの？



6位
Excel。SUMPRODUCT関数は便利と聞くけど、どんな時につかうの？



7位
Excel。時間経過の折れ線グラフ。実は散布図で作るとより綺麗に描けるのです。



8位
Excel。y=2x。一次元方程式のグラフの作り方。



9位
Excel。事務職のデータ分析その10。散布図に平均値を表示する方法



10位
Access。「固有の値」で、重複は簡単に除外できます

時刻:
ラベル: , ,
登録: 投稿 (Atom)