Facebookページに書いた、Excelの豆知識（Trivia）です。
6月7日
Excel。
CHITEST関数
読み方は、カイテストで、カイ二乗検定の上側確率を算出します
6月8日
Excel。
CHOOSE関数
読み方は、チューズで、引数リストの何番目かの値を抽出します
6月9日
Excel。
CLEAN関数
読み方は、クリーンで、文字列中に含まれる制御文字を取り除くします
6月10日
Excel。
CODE関数
読み方は、コードで、文字を文字コードに変換するします
6月11日
Excel。
COLUMN関数
読み方は、カラムで、セルの列番号を算出します
6月12日
Excel。
COLUMNS関数
読み方は、カラムズで、セル範囲の列数を算出します
6月13日
Excel。
COMBIN関数
読み方は、コンビネーションで、組み合わせの数を算出します
SYD関数
読み方： エスワイディー
読み方： サムオブイヤーズディジット
分類： 財務
SYD(取得価額,残存価額,耐用年数,期)
減価償却費を算術級数法で算出します（日本では利用が認められてない）
Sum of Year's Digit depreciationの略
店舗名が品川店と横浜店の合計金額を求めたい場合、SUMIF関数一つだけではもとめることができません。
もし、SUMIF関数をつかう場合で、わかりやすくという前提ならば、次のような数式をつくります。
なんで、SUMIF関数が2つあるのかというと、2つめの引数の「検索条件」がOR条件に対応していないからです。
またOR関数をつかった数式に変えてみます。
=SUMIF(D2:D169,OR("品川店","横浜店"),I2:I169)
これだと、0になってしまいました。
原因は、先ほども紹介したように、検索条件にOR関数を使ったからです。
SUMPRODUCT関数をつかうなど、対応方法はありますが、算出するのはちょっと面倒です。
では、Accessのクエリだったらどうなるのでしょうか。
Accessのクエリデザインをつかって、演算フィールドをつくってみます。
![合計金額: Sum(IIf([店舗名] In ("品川店","横浜店"),[金額],0))](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgvfKHXUuMuYHpOVgd-AGW7kXhz8WRWCkrqXnkfEC_f1Th5-UJpYk986aiX7nzyjrgIqeXVNmq8Xar347hi6qx1C2vYliJbrOSBgnBe4fza4W4qz2xw4HhlfokEddJxISOM7QIBe1MmHo90O-FNb2EMEauzZ0XqneN7zkLnn_b-xCmvmmm4A3iMeTQuWeI/s2612/003.jpg "合計金額: Sum(IIf([店舗名] In (\"品川店\",\"横浜店\"),[金額],0))")
テーブルは、Excelのでーたをインポートしています。
クエリデザインで演算フィールドをつくります。
そして、クエリデザインタブの集計ボタンをおして、集計行を表示します。
合計金額: Sum(IIf([店舗名] In ("品川店","横浜店"),[金額],0))
集計行には、演算 と設定します。
では実行してみましょう。
では、演算フィールドを確認しておきましょう。
合計金額: Sum(IIf([店舗名] In ("品川店","横浜店"),[金額],0))
Sum関数は合算する関数です。
何を合算するのかというと、IIf関数で合致しているものです。
IIf([店舗名] In ("品川店","横浜店"),[金額],0) は、
もし、店舗名が、品川店または横浜店だったら金額フィールドをつかって、そうでなければ0をつかうという条件式なので、品川店と横浜店ならば、金額を足すことができるというわけです。
単純ですが、OR条件の合計の場合、Accessのクエリという方法もアリなのかもしれません。
Excelで表を作っている時、「まだデータが全部揃っていないのに合計が出てしまう」「不完全な数字が表示されて格好悪い…」と思ったことはありませんか？
今回は、IF関数・COUNT関数・SUM関数というおなじみの基本関数だけで、「3回分のデータが揃った時だけ合計を表示する」仕組みの作り方を解説します。
特別な知識は不要！でお話ししている通り、シンプルな関数をパズルのように組み合わせるだけで、プロのような「気の利いた表」が作れるようになりますよ。
＜関数辞典：SWITCH関数＞
SWITCH関数
読み方： スイッチ
分類： 論理
SWITCH(式,値1,結果1,[既定または値2,結果2],…)
式で指定した値を比較して最初に一致する値を返す
横軸を縦書きにするだけではなくて、集合縦棒グラフの中に表示した、横項目軸内臓集合縦棒グラフをつくっていきます。
つくりたいのは、このようなグラフです。
横項目軸が、集合縦棒グラフの中に表示されているのが特徴です。
このグラフを作る表を用意します。
C列の横軸は、B列の値をコピペしてもいいですし、=B2のようにセル参照させてもOKです。
なぜ、同じ値を用意するのかというと、データラベルを表示したいからです。
データラベルを表示しないならば、不要です。
A1:C6を範囲選択して、挿入タブから集合縦棒グラフをつくります。
販売金額と横軸の両方とも同じ色の縦棒に修正してあります。
またグラフもわかりやすくするため、少し大きくしております。
 |
縦棒グラフをクリックして、グラフ要素のデータラベルにチェックマークをいれて、データラベルを表示します。
販売金額も横軸も、両方ともに表示します。
横軸側のデータラベルをクリックします。
画面右側にデータラベルの書式設定作業ウィンドウが表示されます。
ラベルオプションのセルの値にチェックマークをいれます。
ラベルの位置を「内側軸寄り」にします。
そのまま、サイズとプロパティに移動して、文字列の方向を縦書きに変更します。
横軸の縦棒グラフをクリックします。
データ系列の書式設定作業ウィンドウが表示されます。
あとは、凡例から横軸を削除して、フォントやフォントサイズなどを修正して見栄えを整えれば完成です。
＜関数辞典：SUMXMY2関数＞
SUMXMY2関数
読み方： サム オブ エックス マイナス ワイ スクエアエド
読み方： サムエックスマイナスワイジジョウ
分類： 数学/三角
SUMXMY2(配列1,配列2)
対応する組の要素の差を2乗して合計します。
Σ(x-Y)^2
年と月を入れるだけで、月末日を自動で判定、土日や祝日に合わせて行全体を自動で塗りつぶす、そんな「究極の自動カレンダー」の作り方を徹底解説します。
ポイント
・月末の処理をマスターすれば、2月の28日や29日の判定も怖くありません。
・COUNTIF関数を条件付き書式に使うテクニックは、実務の至る所で応用可能です！
縦棒グラフなどの横軸を縦書きにしたい場合には、どうしたらいいのでしょうか。
まず、グラフになる表です。
画像は、横軸に注目しています。
横書きですね。
これを縦書きにしたいので、横軸をクリックします。
書式タブのグラフ要素が「横（項目）軸」になっていることを確認して、選択対象の書式設定をクリックします。
文字列の方向を縦書きにしてみます。
縦書きになったようですが、数字がおかしいですね。
数字も縦にしたいわけです。縦書き（半角文字含む）にしてみましょう。
数字も縦書きにはなりましたが、10月から12月は、少し読みにくいですよね。
そこで、次のような方法があります。
一度、横書きに戻しておきます。
表の月を次のように変更します。
ユーザー定義に、0”月”と入力したら、0の後ろで Ctrl+J をいれます。
Ctrl+Jは、表示形式で改行をいれることができるショートカットキーです。
OKボタンをクリックします。
数式バーを確認すると1のままです。
そして、グラフの横軸を確認してみましょう。
だけど縦書きになっていますね。
表示形式を追加した方法を合わせると、このような縦書きをつくることもできます。
Facebookページに書いた、Excelの豆知識（Trivia）です。
5月31日
Excel。
CHIDIST関数
読み方は、カイディストで、カイ二乗分布の上側確率を算出します
6月1日
Excel。
CHIINV関数
読み方は、カイインバースで、上側累積確率からカイ二乗分布のパーセント点の値を逆算します
6月2日
Excel。
CHISQ.DIST関数
読み方は、カイスクウェア・ディストで、カイ二乗分布の確立を算出します
6月3日
Excel。
CHISQ.DIST.RT関数
読み方は、カイスクウェア・ディスト・ライトテールで、カイ二乗分布の上側確率を算出します
6月4日
Excel。
CHISQ.INV関数
読み方は、カイスクウェア・インバースで、カイ二乗分布の下側確率から確率変数を算出します
6月5日
Excel。
CHISQ.INV.RT関数
読み方は、カイスクウェア・インバース・ライトテールで、上側累積確率からカイ二乗分布のパーセント点の値を逆算します
6月6日
Excel。
CHISQ.TEST関数
読み方は、カイスクウェア・テストで、カイ二乗検定の上側確率を算出します
SUMX2PY2関数
読み方： サム オブ エックス スクエアエド プラスワイ スクエアエド
読み方： サムエックスジジョウプラスワイジジョウ
分類： 数学/三角
SUMX2PY2(配列1,配列2)
Σ(x^2+Y^2)。対応する組の要素の平方和の合計します
WritingとReadingの結果、どちらかが70点より大きい場合は、○。
そうでなければ×と判定したい場合は、どちらか一方ということで、「～または～」というOR条件ですね。
そこで、IF+OR関数をつかった数式を設定します。
D3には、
=IF(OR(B3>70,C3>70),"○","✕")
という数式を設定して、オートフィルで数式をコピーしました。
どちらかが70より大きければ、○なので、D4とD5は✕になっています。
まぁ、問題はありません。
ただ、Excelの関数が苦手、関数の中に関数を設定するネストは苦手という人もいます。
そこで、他の方法もありますので、今回はその一つとして、IFS関数をつかった方法をご紹介します。
=IFS(B3>70,"○",C3>70,"○",TRUE,"✕")
結果は同じになっていますね。
IFS関数は、複数の条件をまとめて設定できる関数です。
それぞれが70より大きいのかを設定してあげる。
そして、その他は、 TRUE を設定すれば、同じように求めることができます。
ただ、IF＋OR関数もIFS関数の数式も、実は欠陥があります。
C5とC6の空白セルに、「欠席」と入力してみましょう。
すると、5行目が、どちらも○に変わってしまいました。
原因は、欠席という文字は、数値よりも大きいからです。
最悪なのは、6行目のほうです。
結果はあっていますので、チェックをすり抜けます。
後日、83が70以下に変わってしまうと、トラブルが発生するというわけです。
100点が最高値ならば、100以下という条件も追加すればいいように思えますが、100以下だと、70以下も含まれてしまうため、条件を追加するだけではダメです。
このような場合、文字を入れさせないように、運用上のルールを設けるのがいいと思います。
ただ、どうしても、というのであれば、文字じゃないという条件を追加する必要があります。
D3の数式を変更しました。
=IF(OR(IF(ISNUMBER(B3),B3,0)>70,IF(ISNUMBER(C3),C3,0)>70),"○","×")
ISNUMBER関数は、数値かどうかを確認する関数です。
ISNUMBER(B3)が成立、すなわち、数値ならば、B3をつかいます。
ISNUMBER(B3)が不成立ならば、0として判断させます。
このように、対応はできますが、数式が複雑化していくことになりますから、運用上で対応というのでもいいかと思います。
作業効率もUPする、知っていると便利なショートカットキー。
Shift＋F9
作業中のワークシートのみ「再計算」する
Shift＋F10
コンテキスト メニューを開きます。
Shift＋F11
ワークシートを新規追加する
Shift＋F12
上書き保存 Ctrl+Sと同じ
SUMX2MY2関数
読み方： サム オブ エックススクエアエド マイナス ワイ スクエアエド
読み方： サムエックスジジョウマイナスワイジジョウ
分類： 数学/三角
SUMX2MY2(配列1,配列2)
Σ(x^2-Y^2)。対応する組の要素の平方差の合計します
時間を○時間○分と表示したい場合は、自分で入力してはいけなくて、表示形式を使う必要があります。
C2に
=A2
とセル参照の数式を設定したら、セルの書式設定ダイアログボックスを表示します。
ちなみに、A3は、直接4時間15分と入力しています。
C3には、
=A3
というようにセル参照しています。
さて、ここから重要なポイントで、C列の時間に＋2時間してみましょう。
=C2+"2:00"
という数式を設定します。+2hは、単純に+2ではダメです。
また「”（ダブルコーテーション）」で囲う必要があります。
D2は、6時間15分と求めることができましたが、D3のほうは、#VALUE!というエラーが表示されてしまいました。
このエラーは、文字と数値を計算しているというエラーです。
原因は、自分自身で4時間15分と入力してしまったことです。
時間ではなくて、文字扱いになっています。
なお、4時15分ならば、時刻という数値扱いになるので、エラーは表示されません。
表示形式を設定するだけで、○時間○分と表示することもできます。
Facebookページに書いた、Excelの豆知識（Trivia）です。
5月24日
Excel。
BITRSHIFT関数
読み方は、ビットライトシフトで、ビットを右シフトします
5月25日
Excel。
BITXOR関数
読み方は、ビットエクスクルーシブオアで、排他的論理和を算出します
5月26日
Excel。
CEILING関数
読み方は、シーリングで、指定した数値の倍数に切り上げます
5月27日
Excel。
CEILING.MATH関数
読み方は、シーリング・マスで、指定した方法で倍数に切り上げます
5月28日
Excel。
CEILING.PRECISE関数
読み方は、シーリング・プリサイズで、指定した数値の倍数に切り上げます
5月29日
Excel。
CELL関数
読み方は、セルで、セルの書式・位置・内容に関する情報を得ます
5月30日
Excel。
CHAR関数
読み方は、キャラクターで、文字コードを文字に変換します
SUMSQ関数
読み方： サムスクウェア
分類： 数学/三角
SUMSQ(数値1,[数値2],…)
数値の2乗の合計を算出します
複数条件での合計をExcelで求めるには、SUMIFS関数をつかいます。
そこで、SUMIFIS関数をつかった数式をつくります。
=SUMIFS(I2:I169,D2:D169,"品川店",F2:F169,"ラーメンセット")
最初の引数に合計対象範囲があって、そのあとに、条件範囲と条件を設定します。
SUMIFS関数がないAccessでは、どのようにしたらいいでしょうか。
まず、挿入タブのクエリデザインをつかって、クエリをつくります。
使うフィールドを設定します。
今回は、店舗名・商品名・金額フィールドです。
集計したいので、集計ボタンを押して、集計行を表示します。
店舗名と商品名はグループ化させます。
金額は、複数条件の合計を求めたいので、合計を選択します。
今回は、品川店のラーメンセットの合計を求めたい。
なので、店舗名フィールドの抽出条件には 品川店
商品名フィールドの抽出条件には ラーメンセット を設定したら、実行します。
結果は、Excelと同じになっていることが確認できました。
Accessのクエリで、「AかつB」のように複数の条件を満たすデータだけを抽出したいときはどうすればいいのでしょうか？
今回は「都道県が神奈川県」で、かつ「担当地域が横浜」という人をピックアップする例を使って、AND条件の作り方を実演します。
ポイントは、抽出条件を「同じ行」に書くこと！
この基本を知るだけで、複雑なデータ抽出も自由自在になりますよ。
SUMPRODUCT関数
読み方： サムプロダクト
分類： 数学/三角
SUMPRODUCT(配列1,[配列2],[配列3],…)
複数の数値の組を掛け合わせて合計を行います
行を非表示にしたときに、見えているデータだけの合計値に連動させたい場合には、SUBTOTAL関数やAGGREGATE関数をつかうことで、求めることができます。
そこで、次のような横長の表があります。
H列には、
=SUM(B2:G2)
のように、SUM関数で合計を求めています。
やりたいことは、列を非表示にしたら、その分を除いた合計値を求めたい。
例えば、1～3月の数値を除いた合計を求めたいわけです。
当然、列を非表示にしたところで、SUM関数は、非表示に対応していないので、合計値はかわりません。
また、SUBTOTAL関数やAGGREGATE関数は、行の非表示には対応しますが、列の非表示には対応できません。
そこで、少し考え方を変えてみます。
そもそも、列の非表示とはどういうことなのか。
列幅が0ということです。
つまり、列幅がわかるようなものがあれば、いいわけです。
そこでCELL関数をつかうことで、セル情報の列幅を求めることができます。
ただ、このCELL関数。スピル機能に対応したため、今までのような数式ではうまく求めることができません。
CELL関数の最初の引数は、検査の種類。
列幅の情報を知りたいので、 width を選択します。
2つ目の引数の参照は、見出しのB1を選択します。
=CELL("width",B1)
で、数式を確定すると…
これでは、オートフィルで数式をコピーすることができません。
なので、スピル機能にならないように数式を修正します。
=@CELL("width",B1)
「@（アットマーク）」をいれることで、スピル機能をとめることができます。
※@ = implicit intersection operator
あとは、横方向に、オートフィルで数式をコピーします。
F9キーをおさないと、算出結果は変わりませんが、列幅を求めることができました。
I列に列の非表示に連動した合計を求める数式を用意します。
I2にSUMIF関数を使った数式を設定しました。
=SUMIF($B$5:$G$5,">0",B2:G2)
B列を非表示にしたら、F9キーを押します。
これで、非表示を除いた合計を求めることができます。
では、数式を確認しておきましょう。
単一条件の合計を求めたいので、SUMIF関数をつかいます。
最初の引数は、「範囲」。
検索のデータ範囲です。
CELL関数の結果をつかいますので、$B$5:$G$5
オートフィルで数式をコピーしますので、絶対参照を忘れずに設定します。
2つ目の引数は、検索条件。
”>0”。
セルの幅が0より大きいならば、非表示じゃないという意味ですね。
最後の引数は、合計範囲。B2:G2
あまり列の非表示の合計をすることはないかもしれませんが、やり方の一つとして、紹介させていただきました。