Facebookページで【書いてみた】ワンポイントです。
10月4日
Excel。置換で条件に書式を設定できますね。
10月5日
Excel。検索の条件にある書式設定を削除するには書式検索のクリアをクリックしましょう。
10月6日
Excel。置換の条件にある書式設定を削除するには書式検索のクリアをクリックしましょう。
10月7日
Excel。日付データをもとにしてオートフィルを行うと、連続データの単位が日・週・月・年で選択できますね
10月8日
Excel。表示形式を解除するには、標準に設定する必要がありますね。
10月9日
Excel。表示形式を設定していないセルにはG/標準が設定されていますね。
10月10日
Excel。表示形式のG/標準「G」とはGeneral（一般の、普遍的な、全般の）頭文字の「G」の略です。
英語版のExcelでは「G/標準」は「General」となっています。
帳票と同じようにExcelで表を作ってしまうと、意外と面倒な処理が発生することがあります。
例えば次のような表。
最大値を算出しているB14。
おかしいですよね。
合計値の値を算出しています。
範囲選択をB3:B11までにすると、今度は、1782という小計値を算出してしまいます。
要するに、範囲選択内の最大値を算出してしまうわけです。
となると、範囲選択は、「B3:B5,B7:B8,B10:B11」というように、何度も区切らなければいけません。
これでは、件数が増えた場合、かなり面倒な作業をしないと、数式を作ることができないということになってしまいます。
では、どうしたらいいのでしょうか？
まずは、合計値の場合からみてみましょう。
B6・B9・B12には、SUM関数をつかった数式で小計値を算出しています。
B13もSUM関数をつかっていますが、オートSUMボタンをつかって、合計をつかうと、その範囲選択内にあるデータ内でSUM関数の算出結果のみを合算してくれます。
B13の数式は、自動的に、
=SUM(B12,B9,B6)
と生成してくれます。
では、最大値はどうなのでしょうか？
オートSUMボタンにある、最大値をつかって、B3:B13までを範囲選択してみると、データであろうが、小計値であろうが、合計値だろうが、関係なく範囲選択内の、最大値を算出してしまいます。
SUM関数をつかったところを除外して算出してくれることはありません。
実は、SUM関数やMAX関数だと、このような帳票には不向きな関数なのです。
今回のような場合は、SUBTOTAL関数かAGGREGATE関数をつかうことで、一気に問題を解決することができ、合理的に数式を作ることができます。
SUBTOTAL関数をつかってみた場合をみてみましょう。
まずは、小計の数式を確認してみます。
C6の数式は、
=SUBTOTAL(109,C3:C5)
と設定しています。
引数の109をつかうことで、合計を算出することができます。
残りも同じようにSUBTOTAL関数をつかっています。
C13の数式は、
=SUBTOTAL(109,C3:C12)
と設定しています。
範囲選択も小計のセルのみを選択するわけではなく、全体を選択してもSUM関数どうように、SUBTOTAL関数で算出した値のところだけを合算してくれいます。
問題の最大値を算出しているC14の数式は、
=SUBTOTAL(104,C3:C13)
引数の104は、最大値を算出する番号です。
範囲選択は、C3:C13とデータも小計値も合計値も関係なく範囲選択していますが、最大値がきちんと算出されています。
このように、途中に小計や合計が含まれる帳票のような表の場合には、SUM関数やMAX関数ではなくて、SUBTOTAL関数やAGGREGATE関数をつかうことで、結果的に効率よく数式をつくることができます。
ケースバイケースで、どの関数をつかうのが効率的なのかを考えながら帳票をつくるといいかもしれませんね。
皆様に閲覧していただいた項目の2021年9月TOP10をご紹介
1位
Excel。いつもの資料にデータの特徴を把握する2つの関数を追加してみよう。
https://infoyandssblog.blogspot.com/2021/09/excel2data-features.html
簡単な関数をつかって、把握するための数値を算出してみましょう。
2位
Excel。一日のタイムスケジュールを管理する24時間横棒グラフを作ってみる
https://infoyandssblog.blogspot.com/2016/03/excel24hour-schedule24.html
スケジュールというと、様々なものがありますが、今回作成してみようと思うのが、一日24時間のタイムスケジュールが把握できる、【24時間横棒グラフ】です。
3位
Excel。氏名を苗字で分割したいが区切りがないので困っています。
https://infoyandssblog.blogspot.com/2021/09/excelstring-split.html
このようなデータを分割するには、区切りになる場所は、自分自身で用意しなければいけないわけです。
4位
Excel。折れ線グラフを交点0からスタートさせるには？
https://infoyandssblog.blogspot.com/2013/07/excel0.html
5位
Excel。VBA。表から特定の列（フィールド）だけを手早くコピーしたい
https://infoyandssblog.blogspot.com/2021/09/excelvbacopy.html
6位
Excel。料金量がわかりやすい階段グラフの作り方
https://infoyandssblog.blogspot.com/2017/09/excelstaircase-graph.html
7位
Excel。折れ線グラフの間を塗りつぶしたいけど、どうしたらいいの？
https://infoyandssblog.blogspot.com/2015/12/excelgraph.html
8位
Excel。セル内にある○×それぞれの文字を数えるにはどうしたらいいの？
https://infoyandssblog.blogspot.com/2021/05/excelcount-letters.html
9位
Excel。2列1組でセルを塗りつぶししたいけど、効率よく設定するにはどうしたらいい。
https://infoyandssblog.blogspot.com/2021/09/excel21conditional-formatting.html
10位
Excel。最大値のデータが複数あっても、行全体で塗りつぶしをしたい
https://infoyandssblog.blogspot.com/2021/04/excelmultiple-maximums.html
帳票のようなデータから、例えば2列置きごとに必要なデータのみを手早く抽出するとしたら、どのようにしたらいいのでしょうか？
例えば、次の表。
全店舗だとしても、範囲選択してコピーするだけでも、面倒ですし、それが決まった店舗のみとなると、範囲選択するだけでも、面倒です。
コピー&ペーストの作業しか方法がないのでしょうか？
ピボットテーブルをつかってもいいですが、実はこのようなケースの場合、VLOOKUP関数をつかうことで、手早くデータを抽出することが出来ます。
B2には、次の数式を設定しています。
=VLOOKUP($A2,$A$6:$G$10,COLUMN(A1)*2+1,FALSE)
とてもシンプルなVLOOKUP関数の数式であることが見てわかりますね。
VLOOKUP関数は、関数の特性とアイディアで、現場で大いに活躍してくれる関数の一つだといえます。
考えるポイントは、全データではなく、新宿店と品川店のみであるということ。
それと、2列置きのデータであるということ。
そこで、該当のデータのみということから、VLOOKUP関数をつかってみたらどうだろうと考えてみます。
オートフィルで数式をコピーするのが、手早く処理するためには必要です。
オートフィルでVLOOKUP関数をコピーする時にネックになるのが、引数の列番号です。
「列番号」は、数値である必要があります。
そこで、オートフィルで列方向にずらしたときに、列番号を連動させるために使用するお馴染みの関数があります。それが「COLUMN関数」です。
COLUMN関数は、列番号を算出することができる関数です。
今回は、引数の範囲。$A$6:$G$10の3・5・7列目に抽出したいデータがありますので、列番号をどうにかして、3・5・7という数字になるように考える必要があります。
「COLUMN(A1)*2+1」とすることで、2列置きのデータを抽出できることに気づきました。
それでは改めてVLOOKUP+,COLUMN関数の数式を確認してみます。
検索値は、$A2。
列方向にオートフィルで数式をコピーするので、列を固定した複合参照で設定します。
範囲は、$A$6:$G$10。絶対参照を設定しておきます。
列番号は、先程紹介したように、COLUMN(A1)*2+1
検索方法は、完全一致なので、FALSE
あとは、オートフィルで数式をコピーしたら完成です。
このように、VLOOKUP関数は、請求書や納品書などの帳票だけではなくて、アイディアによって、現場で色々使える関数です。
ある規則性がある場合には、VLOOKUP関数で考えてみるというのもいいかもしれませんね。
Facebookページで【書いてみた】ワンポイントです。
9月27日
Excel。セルや範囲に名前を付けておくと、数式に使用することも出来ますね。
9月28日
Excel。定義した名前は、F3キーを押すと、【名前の貼り付け】ダイアログボックスが表示されますね。
9月29日
Excel。エラーの可能性があると、セルの右上角に緑色の三角が表示されますが、これは、エラーインジケーターといいます。
9月30日
Excel。名前ボックスにセル番地を入力してEnterキーを押すとそのセル番地にジャンプできますね。
10月1日
Excel。検索をした後に続けて実行すると直前の設定が残っているので、削除してから使いましょう。
10月2日
Excel。置換をした後に続けて実行すると直前の設定が残っているので、削除してから使いましょう。
10月3日
Excel。検索で条件に書式を設定できますね。
Accessのテーブルを用途に合わせて並べ替えをしたいときに、Excelと違って、そのフィールドの「型」を考えて実行しないと、きちんと並べ替えができません。
用意したのは次のテーブルです。
件数フィールドは、「数値型」。
というように設定されています。
それでは、クエリをつかって、件数フィールドを降順に並べてみましょう。
作成タブの「クエリデザイン」をつかって、クエリをつくっていきます。
件数フィールドの並べ替えに「降順」と設定したら、実行して確認してみましょう。
これは、文字型でない数値型フィールドだったので、並べ替えを実行した場合、すんなり並べ変わったわけですが、担当NOフィールドを昇順で並べ替えをしようとしたら、うまく並べ替えができません。
実のところ、昇順といえば、昇順で並べ替えされてはいるのです。
この担当NOフィールドは、「短いテキスト型」ということで、文字なのが、希望のように並ばない原因です。
原因が、文字型だからとわかったところで、元のフィールドの「型」を文字型から数値型に変換していいのでしょうか？
意味があって文字型にしているはずです。
そこで、元のデータの「型」を変更するのではなく、クエリ上で、文字型を数値型に変換することができる「関数」があります。
それが、CLng関数。
CLng関数は、長整数型に変換してくれる関数です。
注意点は、整数だけを扱うフィールドの時に使用することができます。
小数点の場合は、CDbl関数を使います。
なお、金額の場合は、CCur関数を使います。
CLng関数をつかった演算フィールドを作っていきます。
担当NO_C: CLng([担当NO])
と設定しました。
それでは、実行して確認してみましょう。
このように、昇順で並べ替えができました。
算出するようなことがある場合は、数値型をつかって、算出することがなければ、文字型でいいように考えてしまいますが、並べ替えのことも考慮して、算出はしないけれども、並べ替えはおこなうことが、想定される場合は、数値型のフィールドで設定しておくと、このような変換系の関数をつかわなくても済みます。
Excel以上に、Accessでは、「型」というのが重要になりますので、Excelのときから「型」という意識をもって作成や処理をしていくといいかもしれませんね。
今回は、NOMINAL関数～NORM.INV関数までをご紹介しております。
NOMINAL関数
ノミナル
名目年利率を算出します。
NOMINAL(実効利率,複利計算期間)
NORMDIST関数
ノーマルディスト（ノーマルディストリビューション）
平均と標準偏差に対する正規分布の確率を算出します。
NORMDIST(x,平均,標準偏差,関数形式)
NORM.DIST関数
ノーマル・ディスト（ノーマル・ディストリビューション）
平均と標準偏差に対する正規分布の確率を算出 Excel2010以降
NORM.DIST(x,平均,標準偏差,関数形式)
NORMINV関数
ノーマルインヴァース
累積確立から正規分布の数値を逆算する
NORMINV(確率,平均,標準偏差)
NORM.INV関数
ノーマル・インヴァース
累積確立から正規分布の数値を逆算する Excel2010以降
NORM.INV(確率,平均,標準偏差)