11/30/2020

Excel。ピボットテーブルならアイテムごとの前月差や前月比も簡単に算出できます。【Pivot table】

Excel。ピボットテーブルならアイテムごとの前月差や前月比も簡単に算出できます。

<ピボットテーブル>

月ごとの集計結果が算出されているならば、簡単に前月差や前月比を算出できますが、集計前の大量なデータの場合は、集計から行う必要があります。


例えば、次のようなデータがあるとします。

 

7月から9月までの3ヶ月分のデータをアイテムごとに集計して、そのアイテムごとの前月差と前月比を算出していきます。

ピボットテーブルをつかうと、集計だけではなく、前月差や前月比も算出することができます。


最初は、ピボットテーブルでクロス集計を作っていきます。

データ内のセルをアクティブにして、挿入タブの「ピボットテーブル」をクリックします。

 

ピボットテーブルの作成ダイアログボックスが表示されます。

基本的に、そのままOKボタンをクリックして問題はないので、OKボタンをクリックします。


ピボットテーブルを使うための新しいシートが挿入されます。

ピボットテーブルのフィールド作業ウィンドウをつかって、レイアウトを次のようにしました。


行には、商品名

列には、営業月

値には、売上高を2つ設定しました。

売上高フィールドは一つでもいいのですが、売上高を残しておくとわかりやすくなります。


ピボットテーブルは次のようなレイアウトで表示されました。


行見出しが「合計/売上高」ではわかりにくいので、「合計/売上高」を売上合計、「合計/売上高2」を前月差と変更しておきます。


なお、現存しているフィールド名と同じにすることは出来ませんので、注意が必要です。


前月差を表示していきます。

前月差のデータをどれでもいいので、アクティブにしておきます。

ピボットテーブルの分析タブの「フィールドの設定」をクリックします。


値フィールドの設定ダイアログボックスが表示されます。


タブを「計算の種類」に変更します。

計算の種類を「基準値との差分」にします。

基準フィールドは、月ごとの「差」を算出したいので、「営業月」を設定します。

最後に、「基準アイテム」には、前月なので、「(前の値)」を設定します。


あとは、OKボタンをクリックします。


このように、前月差を算出することができました。

ピボットテーブルを使わないと、前月差の列を挿入する手間がかかったりしますので、ピボットテーブルをつかうほうが、多少効率的かもしれませんね。

なお、C列が空白なので、列を非表示しております。空白になる理由は、単純に、7月と比べる前月がないからです。


また、前月比を算出したい場合は、


値フィールドの設定ダイアログボックスで、計算の種類を「基準値との差分の比率」に設定すれば、算出することができます。

11/29/2020

今週のFacebookページの投稿 2020/11/23-2020/11/29【Facebook】

今週のFacebookページの投稿 2020/11/23-2020/11/29

<Facebookページ>

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

11月23日

Excel。PROPER関数。

読み方は、プロパーで、英単語の先頭文字を大文字にしてそれ以降を小文字に変換する


11月24日

Excel。PV関数。

読み方は、ピーヴィ:プレズント・バリューで、現在の価値を算出します。


11月25日

Excel。QUARTILE関数。

読み方は、クォータイルで、0%以上100%以下の データの四分位数を算出します。


11月26日

Excel。QUARTILE.EXC関数。

読み方は、クォータイル・ エクスクルーシブで、0%より大きくて100%未満のデータの四分位数を算出します。


11月27日

Excel。QUARTILE.INC関数。

読み方は、クォータイル・ インクルーシブで、0%以上100%以下のデータの四分位数を算出します。


11月28日

Excel。QUOTIENT関数。

読み方は、クオーシャントで、除算した商を算出します。


11月29日

Excel。RADIANS関数。

読み方は、ラジアンで、角度をラジアンに変換する

11/27/2020

Access。ExcelのCOUNTIF関数やCOUNTIFS関数のように条件付きで数えるには?【Conditional number】

Access。ExcelのCOUNTIF関数やCOUNTIFS関数のように条件付きで数えるには?

<Access:Dcount関数>

Excelだと、都道府県のフィールドで、神奈川県の人が何人いるのか知りたい場合は、COUNTIF関数をつかって算出させることができますが、Accessではどのようにしたらいいのでしょうか?

例えば、次テーブル。


問01フィールドで、「○」のデータは何件あるのか知りたい場合、Excelでは、COUNTIF関数を使えば、比較的簡単に算出することができます。

これを、Accessで集計したいわけです。


当然、Accessには、COUNTIF関数なんてものは、ありません。


Accessにある『数える』関数は、「Dcount関数」というのがあります。

この関数をクエリで使うことで、対応することができそうです。


では、早速クエリを作っていきましょう。


作成タブの「クエリデザイン」を使ってクエリを作成していきます。


フィールドに演算フィールドを設定します。

問01の○: DCount("問01","Tアンケート","問01='○'")

注意点があって、条件のところですが、「'○'」と条件が文字の場合「’(シングルコーテーション)」で挟んであげる必要があります。


まずは実行して確認してみましょう。

問01の○の件数は2件ありましたので、「2」と集計されていれば成功ですね。


データシートビューに切り替えましょう。


集計結果は「2」なんですが…3件表示されています?

集計はされたのですが、レコードが3件だったので、このように表示されてしまうので、修正します。


デザインビューにして、クエリツールの「デザイン」タブにある「集計」をクリックします。


集計行が表示されますので、「グループ化」します。


結果を確認しますので、データシートビューに切り替えましょう。


このように、集計することができました。

それでは、次のような「問01の回答結果が「○」で評価が80ポイント以上」という条件での集計はできるのでしょうか?


今回は、単一条件ではなくて、複数条件。

Excelだったら、COUNTIFS関数を使うところですが…


AccessのDcount関数は、

Dcount(対象フィールド名,テーブル名orクエリ名,抽出条件1)

という構造になっていて、引数に抽出条件を追加してあげるだけで、複数条件に対応することできますので、次のように演算フィールドを修正すれば、対応できます。


問01の○: DCount("問01","Tアンケート","問01='○' and 評価>=80")

と演算フィールドを修正しました。


それでは、実行して結果を確認してみましょう。


確かに1件だけが該当していますので、きちんと集計できているようです。

このように、抽出条件で「and」をつかうことで、複数条件に対応することができました。


元も子もない話ではありますが、Dcount関数がわからない時は、ExcelにエクスポートしてExcel上で、COUNTIF関数やCOUNTIFS関数をつかうほうが、わかりやすいようでしたら、それでもOKだと思います。

11/26/2020

Excel関数辞典 VOL.39。IMPOWER関数~IMSINH関数【dictionary】

Excel関数辞典 VOL.39。IMPOWER関数~IMSINH関数【dictionary】

<Excel関数>

今回は、IMPOWER関数~IMSINH関数までをご紹介しております。

IMPOWER関数

アイエムパワー

複素数のべき乗を算出する

IMPOWER(複素数,数値)



IMPRODUCT関数

アイエムプロダクト

複素数の積を算出する

IMPRODUCT(複素数1[,複素数2])



IMREAL関数

アイエムリアル

複素数の実数部分を取り出す

IMREAL(複素数)



IMSEC関数

アイエムセカント

複素数のセカント(正割)を算出する

IMSEC(複素数)



IMSECH関数

アイエムセカントハイパーポリック

複素数の双曲線正割を算出する

IMSECH(複素数)



IMSIN関数

アイエムサイン

複素数のサイン(正弦)を算出する

IMSIN(複素数)



IMSINH関数

アイエムサインハイパーポリック

複素数の双曲線正弦(ハイパーポリックサイン)を算出する

IMSINH(複素数)

11/24/2020

Excel。列を非表示にしたら合計値も連動して再計算させたい【Column total】

Excel。列を非表示にしたら合計値も連動して再計算させたい

<CELL関数・SUMIF関数・スピルストップ>

本当ならば、元データから直接ピボットテーブルで算出させたほうが楽だろうと思う資料は、概ねどの現場にもあると思います。


例えば、次のような表。


なんてことない表ですが、G列を非表示にした時に、H列の合計値も表示されているデータだけで再計算してほしいというのが、やりたいことなんですね。


なお、F2に設定されている数式は、

=SUM(B2:E2)

というお馴染みのSUM関数をつかっております。


また、念のために、G列を非表示にしても、合計値が連動しないことを確認しておきましょう。

残念ながら、連動してくれません。

SUBTOTAL関数を使えばいいのでは?と考えるかもしれませんが、SUBTOTAL関数は、『行』の非表示には対応しますが、『列』の非表示には対応してくれません。


昔から使っている帳票類なので、ピボットテーブルも使えない。

ピボットテーブルならば、オートフィルターで抽出したアイテムのみで合計値を算出することができますが、普通のExcelの表では、非表示も含めた範囲で算出してしまいます。


そこで、力技ですが、次のような方法で問題を解決することができます。


考え方ですが、「列の非表示」とはどういう状態なのかといえば、列幅が「ゼロ」ということですね。

要するに、列幅がゼロより大きければ、計算対象になるようにすればいいわけですね。


「列幅がゼロよりも大きいものを総和する」。

どうやらSUMIF関数でいけそうですね。


あと問題なのは、「列幅ゼロ」というのどうやったら算出することが出来るのでしょうか?

そこで、登場するのが、『CELL関数』です。


CELL関数は、対象のセルのステータスを確認できる関数です。

このCELL関数の引数に列幅がどのぐらいなのかを算出するものが用意されています。


B6に次の数式を設定して、列幅を算出します。

=CELL("width",B1)

ところが、上手く算出してくれません。


原因は、Excelの新機能の、「スピル」が影響したためです。

スピルがないExcelのバージョンでしたら問題ありませんが、Microsoft365のExcelだと、スピルの影響でオートフィルで数式をコピーすることができません。


もし、スピル機能があるExcelを使っている場合には、次のように式を変更すれば大丈夫です。

B6に設定した数式は、

=@CELL("width",B1)

「@」をつけることで、スピル機能を停止した数式を作ることが出来ます。

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


これで、列幅を算出することができました。

F2の数式をSUM関数から次のように変更します。

=SUMIF($B$6:$E$6,">0",B2:E2)

オートフィルで数式をF5までコピーします。


E列を非表示にしてみましょう。


あれ?合計が変わっていません。実はこのCELL関数。

再計算させないといけない関数なので、「F9キー」を押して、再計算させましょう。


これで、非表示を除いて合計値を算出することができました。

今回のように、簡単そうに見えて、なかなか面倒という場合もありますが、少しずつ改良して便利にしていけるといいですね。

11/23/2020

今週のFacebookページの投稿 2020/11/16-2020/11/22 【Excel】

今週のFacebookページの投稿 2020/11/16-2020/11/22

<Facebookページ>

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

11月16日

Excel。POWER関数。

読み方は、パワーで、数値のべき乗を算出します。


11月17日

Excel。PPMT関数。

読み方は、ピーピーエムティー:プリンシプルペイメントで、元利均等返済における指定期間の元金返済額を算出します。


11月18日

Excel。PRICE関数。

読み方は、プライスで、定期利付債の時価を算出します。


11月19日

Excel。PRICEDISC関数。

読み方は、プライスディスクで、割引債の額面100に対する価格を算出します。


11月20日

Excel。PRICEMAT関数。

読み方は、プライスマットで、満期利付債の時価を算出します。 

PRICE at MATurity の略ですね。


11月21日

Excel。PROB関数。

読み方は、プロブ(プロバビリティ)で、範囲内の確率値を算出します。


11月22日

Excel。PRODUCT関数。

読み方は、プロダクトで、複数の数値の積を算出します。

11/21/2020

Excel。ドーナツグラフと円グラフの「ドーナツ内円グラフ」をつくってみよう【Pie chart in donut】

Excel。ドーナツグラフと円グラフの「ドーナツ内円グラフ」をつくってみよう

<ドーナツ内円グラフ>

複数のデータの構成比率を比べるのに便利なドーナツグラフ。

二重三重とアレンジをすることもできますが、今回は、ドーナツグラフと円グラフをあわせた、「ドーナツ内円グラフ」を作っていきます。


どのようなグラフなのかというと、次のようなグラフです。

ドーナツ内円グラフ

 

外側が、ドーナツグラフで、内側が、円グラフで構成されいるのが「ドーナツ内円グラフ」です。


簡単そうに見えますが、一工夫しないと作ることができません。

一工夫が必要な理由は、ドーナツグラフと円グラフだけをつくると、その間に、スペースを設けることができない、ようするに、ドーナツだけの幅を細くしてもダメなんですね。


そこで、ドーナツ内円グラフをつくるには、次のような表を用意します。


 

C列のダミーがポイントです。このC列がドーナツグラフと円グラフの「間」になります。


A1:D4を範囲選択して、挿入タブの「円またはドーナツグラフの挿入」から「ドーナツ」を選択します。


 

三重ドーナツグラフが挿入されます。


 

今回の表から作成したら、行列の切り替えが必要になりましたので、グラフのデザインタブの「行/列の切り替え」をクリックします。


 

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

 

いよいよ、ドーナツグラフと円グラフを組み合わせていきます。

ドーナツグラフと円グラフを合わせるためには、ドーナツグラフを第2軸にする必要があります。

そのままは、円グラフは他のグラフと組み合わせて描くことができません。


グラフのデザインの「グラフの種類の変更」をクリックします。


 

グラフの種類の変更ダイアログボックスが表示されます。


 

すべてのグラフにある「組み合わせ」を選択し、内側のグラフを「円」に変更します。

円グラフだけ第2軸にしません。

ダミーと外側のグラフを「ドーナツ」に変更して、第2軸にチェックマークをいれます。


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


 

ここまでくれば、あとは、仕上げ作業に入ります。

最初は、ダミーを白色に塗りつぶします。


「塗りつぶしなし」ではと思うかもしれませんが、ダミーを透明にしても、内側の円グラフが見えてしまうので、意味がありません。


また、今回は説明の為、グラフを大きく見せたいので、グラフタイトルと凡例は削除しました。


 

あとは、データラベルを表示させて、内側と外側が何を指すのかわからないので、テキストボックスを使って、データ見出しを作成して完成です。

 

第2軸をうまくつかうことで、ドーナツグラフと円グラフを組み合わせた「ドーナツ内円グラフ」をつくることができます。

組み合わせによって、様々なグラフが作れそうですので、試してみるといいかもしれませんね。

11/20/2020

Excel。グラフの復習。データ増減縦棒グラフ~高低線に差のラベルを表示【graph】

Excel。グラフの復習。データ増減縦棒グラフ~高低線に差のラベルを表示

<グラフ>

Excelのグラフは、用途に合わせて様々なグラフを作ることができます。


今回は、グラフの復習ということ、4つをピックアップ

・Excel。データの増減がわかるような縦棒グラフを作るにはどうしたらいいの?

・Excel。表が悪いと、グラフの横軸が1・2・3と変わってしまうのでどうしたらいい?

・Excel。集合縦棒グラフに縦目盛り線を表示するには?

・Excel。折れ線グラフの高低線に差のラベルを表示したいけど、できないの?


Excel。データの増減がわかるような縦棒グラフを作るにはどうしたらいいの?

前のデータ比べて、増減をわかりやすくしたグラフを作るとしたらどのようにしたらいいのでしょうか?

<続きはこちら>

Excel。データの増減がわかるような縦棒グラフを作るにはどうしたらいいの?

https://infoyandssblog.blogspot.com/2018/07/excelcolumn-chart.html


Excel。表が悪いと、グラフの横軸が1・2・3と変わってしまうのでどうしたらいい?Office365対応

グラフを作ってみたら、横軸が、1・2・3と変わってしまうケースの対策方法をご紹介していきます。


<続きはこちら>

Excel。表が悪いと、グラフの横軸が1・2・3と変わってしまうのでどうしたらいい?Office365対応

https://infoyandssblog.blogspot.com/2018/07/excel123office365horizontal-axis.html


Excel。集合縦棒グラフに縦目盛り線を表示するには?

見やすくするなど、縦目盛線を描きたいということもあるかと思いますので、今回は、縦目盛線の作り方を紹介していきます。


<続きはこちら>

Excel。集合縦棒グラフに縦目盛り線を表示するには?

https://infoyandssblog.blogspot.com/2018/08/excelgraduation-line.html


Excel。折れ線グラフの高低線に差のラベルを表示したいけど、できないの?


「高低線」にデータラベルを表示するにはどのようにしたらいいのでしょうか?


<続きはこちら>

Excel。折れ線グラフの高低線に差のラベルを表示したいけど、できないの?

https://infoyandssblog.blogspot.com/2018/08/excelline-graph.html

11/18/2020

Excel。簡単にカテゴリー別累計を算出するには、ひと工夫が必要なんです。【Cumulative】

Excel。簡単にカテゴリー別累計を算出するには、ひと工夫が必要なんです。

<IF+SUM関数>

簡単そうに思えるものも、意外にどうやったら数式を作れるの?という場合があったりします。

例えば、次のような表。


今回算出したいのは、E列の累計値とF列の地区別累計です。

累計値は、該当する売上高とその前までの売上高の合計値を合算させます。

この累計値の算出方法は、結構有名なので、早速確認していくことにしましょう。


E2には、次の数式を設定します。

=SUM($D$2:D2)

範囲のスタート地点を絶対参照にして、終点は、相対参照のままにする。


あとは、オートフィルをつかって、数式をコピーすれば、累計値は簡単に算出することができます。


このように累計値を算出することができましたね。


ポイントは、片側だけを絶対参照にするところですね。

オートフィルで範囲が広がるにつれて合算したい範囲が広がるので、累計値を算出することができるわけです。


最初の数式を、

=D2

として、

次のセルに、

=E2+D3

という計算式を作成してもいいのですが、できれば数式一つで対応したいですよね。


なお、最初の数式も、=E1+D2とすると、見出し行で文字のために、エラーが発生します。


次に算出したいのが、

F列の【カテゴリー別累計】です。


先程と同じように、累計値を算出する方法では、カテゴリー(今回は、地区別)が変わった時に、累計値がクリアされません。

よって、片側だけ絶対参照にする方法では対応することができないわけです。


このようなカテゴリー別累計は、IF関数とSUM関数のネストで算出することができます。


F2に、次の数式を設定します。

=IF(B1=B2,SUM(F1,D2),D2)


該当のデータと次のデータの地区名が同じだったら、合算させて、違っていれば、その売上高を参照させるという数式です。


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


営業地区が東京の最後、F5に2738と算出されていて、関東の最初、F6には、クリアされて、1026と算出することができていますね。


意外と簡単な数式でカテゴリー別累計値を算出することができました。


ポイントは、「地区名」で並び替えをしておくことですね。

並び替えをしておかないと、この数式をつかって、算出することができませんので、注意が必要ですね。

11/17/2020

今週のFacebookページの投稿 2020/11/09-2020/11/15

今週のFacebookページの投稿 2020/11/09-2020/11/15

<Facebookページ>

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

11月9日

Excel。PHI関数。

読み方は、ファイで、標準正規分布の密度の値を算出


11月10日

Excel。PHONETIC関数。

読み方は、フォネティックで、文字列のフリガナ情報を取り出す


11月11日

Excel。PI関数。読み方は、パイで、円周率の数値を算出


11月13日

Excel。PMT関数。

読み方は、ピーエムティー:ペイメントで、ローンや投資の定期支払額を算出


11月14日

Excel。POISSON関数。

読み方は、ポワソンで、ポワソン分布の確率を算出


11月15日

Excel。POISSON.DIST関数。

読み方は、ポワソン・ディストで、ポワソン分布の確率を算出 Excel2010以降

11/15/2020

Excel。VBAで、列幅を調整するには、色々な方法があるんです。【Column width】

Excel。VBAで、列幅を調整するには、色々な方法があるんです。

<Excel VBA:列幅>

Excelに、CSVファイルやテキストファイルをインポート(読み込み)したあとに、列幅を調整したい場合、色々な方法があって、知らないと不便に感じることがあります。


次のサンプルデータを用意しました。


A列とF列は同じデータです。

説明しますと、この後の処理を実施した後と比べやすいように、列幅を「10」で設定しております。


J列とK列は同じデータです。

こちらは列幅を、「15」で設定しております。


では、次のExcel VBAプログラムを実行してみます。

Sub 列幅()

    Columns("j:k").AutoFit

    Columns("k:k").ColumnWidth = Columns("k:k").ColumnWidth + 1

    Range("a1").EntireColumn.AutoFit

    Range("f4").Columns.AutoFit

End Sub


実行した結果です。


列幅が色々と変化したことが確認できます。

では、どのExcel VBAプログラム文の結果このようになったのかを見ていきましょう。


1行目の、

Columns("j:k").AutoFit

このプログラム文を実行すると、J列とK列の列幅を自動調整することができます。

Columnsは、列のことですね。

Columns("j:k")なので、J列~K列という意味ですね。


AutoFiメソッドを使うことで、列幅を自動調整することができます。

とてもわかりやすいメソッドの一つですね。


しかし、列幅を自動調整できっちり狭めてしまうと印刷時に、見切れてしまうことがあります。

とくに、罫線を設定したりすると、発生しやすくなりますね。


そこで、2行目のプログラム文を知っていると微調整することができるようになります。


2行目

Columns("k:k").ColumnWidth = Columns("k:k").ColumnWidth + 1


Columns("k:k")はK列を指定しています。

Columns("k")ではないので、注意しましょう。


ColumnWidthプロパティを使うことで、列幅を設定することができます。


今回の、右辺。

Columns("k:k").ColumnWidth + 1

これは、今のK列の幅を、1文字分(プロポーショナルフォントの1文字分)を「+(プラス)」することができます。


また、列幅を20にしたい場合には、

Columns("k:k").ColumnWidth =20

とすることで、列幅を20で設定することもできます。


3行目のプログラム文を見ていきましょう。

Range("a1").EntireColumn.AutoFit


1行目の「Columns("j:k").AutoFit」と似ていますが、結果が異なっています。


AutoFitさせている対象が列ではなくて、Range("a1")に入力されている文字数に合わせることができます。


A1には、「東京都店舗一覧表」と文字数が多いので、列幅が見出しのNOのデータよりも広くなってしまいました。

結果的には、A列をAutoFitさせたのと変わりません。


このセルの文字数で自動調整するのは、効率的ではないのかもしれませんね。

A1の文字数が長いからといって、4行目のように、

Range("f4").Columns.AutoFit

とF4を基準とした列幅で調整したとしても、逆に列幅が狭かったりすると、F13のように、「#」と表示されてしまします。


データをインポートしたあとの列幅を調整する必要がある場合には、どの方法を使ったらいいのか、少し考える必要があるのかもしれませんね。

11/14/2020

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

Excel Technique_BLOG Categoryに追加しました。

<目次サイト>

このBLOGの記事を、

カテゴリー分けにした【Excel Technique_BLOG Category】に追加しました。


Excel。不要な通し番号を削除したり、作業で使った番号を見えなくさせる方法


途中計算で使用したセルが見えていて、削除できないけど、イマイチ綺麗とはいえないのでどうにかしたい。


<続きはこちら>

Excel。不要な通し番号を削除したり、作業で使った番号を見えなくさせる方法

https://infoyandssblog.blogspot.com/2015/08/exceldisplayformat.html


Excel。OFFSET関数を使って関数の範囲を自動的に調整してくれる方法

関数の範囲がおおむねこの範囲とかでしたら、名前の定義を使うと楽かもしれませんが、OFFSET関数を使うというテクニックもオススメなんですよ。


<続きはこちら>

Excel。OFFSET関数を使って関数の範囲を自動的に調整してくれる方法

https://infoyandssblog.blogspot.com/2015/08/exceloffset.html


Excel。複数のシートから1枚のシートに転記させたい時はVLOOKUPが使えます。

VLOOKUP関数はアイディア次第で、こんなところにも使うことが出来るんです


<続きはこちら>

Excel。複数のシートから1枚のシートに転記させたい時はVLOOKUPが使えます。

https://infoyandssblog.blogspot.com/2015/08/excelvlookup1vlookup.html