Facebookページで【書いてみた】ワンポイントです。
11月22日
Excel。small関数は○番目に小さい値を算出関数です。
11月23日
Excel。precentile.exc関数は上位下位○%の位置にある値を算出関数です。
11月24日
Excel。precentile.inc関数は上位下位○%の位置にある値を算出関数です。
ちなみに引数に1を指定すると最大値になります。
11月25日
Excel。product関数は掛け算関数です。
11月26日
Excel。sumproduct関数は複数の範囲の積を算出関数です。
11月27日
Excel。quotient関数は割り算関数です。
11月28日
Excel。mod関数は割り算のあまり関数です。
Excelだと、データタブの「並べ替え」をつかうことで、複数条件であっても、視覚的にもわかりやすく設定することができるようになっています。
ではAccessではどうなのでしょうか？
次のテーブルをつかって確認していきましょう。
「得点を降順で並べ替えを行います。もし、得点が同じだったら、ふりがなを昇順で並べ替えを行います。」
という処理をしたいわけです。
つまり、
優先順位 第一位が 得点を降順
優先順位 第二位が ふりがなを昇順
ということです。
並べ替えを行うにも、クエリをつくりますので、作成タブの「クエリデザイン」をクリックします。
並べ替えの設定をしますので、ふりがなフィールドには昇順を、得点フィールドには降順の設定をしました。
実行して、どのように表示されるのかを確認してみましょう。
よくみると、ふりがなフィールドは五十音順の昇順になっていることが確認できます。
要するに、リクエスト通りになっていません。
原因は、クエリ。
並べ替えの昇順・降順設定そのものは問題ないのですが、左側にあるフィールドの並べ替えを優先させるようになっています。
なので、ふりがなフィールドのほうが、左側にあるので、ふりがなフィールドが、優先順位 第一位で処理されてしまったというわけです。
そこで、クエリを次のように修正します。
そのかわり、一番右側に、新しくふりがなフィールドを追加します。
追加したふりがなフィールドの並べ替えに昇順とします。
なぜ、新しくふりがなフィールドを設定したのかというと、並べ替えの優先順位が左側という原則があるので、得点フィールドの左側にふりがなフィールドがあると、うまく並べ替えができません。
そこで、右側にダミーとして設定し、そのフィールドに並べ替えの設定をしています。
では、改めて実行して確認してみましょう。
このように、単純な並べ替えかもしれませんが、少し注意が必要かもしれませんね。
今回は、NOW関数～NUMBERVALUE関数までをご紹介しております。
OCT2BIN関数
オクトトゥビン
8進数を2進数に変換する
OCT2BIN(数値,[桁数])
OCT2DEC関数
オクトトゥデック
8進数を10進数に変換する
OCT2DEC(数値)
OCT2HEX関数
オクトトゥヘックス
8進数を16進数に変換する
OCT2HEX(数値,[桁数])
ODD関数
オッド
数値を奇数に切り上げる
ODD(数値)
明日は雨だから…など、売上予測を直感で頼るのではなく、チョットしたデータがあれば、関数をつかうだけで、算出することができます。
また、関数をつかった計算式なので、散布図をつかった、近似曲線に数式を表示しなくても、大丈夫です。
では、次のデータを用意しました。
単回帰分析の式である、「y=ax+b」の傾きと切片を算出するところからおこなっていきましょう。
最初は「a」の傾きを算出するには、SLOPE関数をつかいます。
グラフを移動させて、F2にSLOPE関数の数式を設定して、傾きを算出します。
=SLOPE(C2:C11,B2:B11)
縦軸のYが売上高で、横軸のXが来店数なので、その範囲を設定してあげるだけで、aの傾きを算出することができました。
算出結果は、152.2238979。
グラフで表示した単回帰式の傾きと一緒であることが確認できます。
続いて、「b」の切片を算出してきます。
切片を算出するために使用する関数は、INTERCEPT関数をつかいます。
F3にINTERCEPT関数の数式を設定します。
=INTERCEPT(C2:C11,B2:B11)
算出結果は、-24.19721578。
散布図の近似曲線で表示した式の切片と、先程の傾きと同様に合致していることがわかります。
このように、わざわざ、グラフを用意する必要はありません。
これで、傾きと切片が算出できましたので、来店数を「20」だとした場合の、単回帰式にあてはめることで、売上予測を算出することができます。
=F2*F5+F3
と設定しています。
算出結果は、3020.2。
という予測値を算出することができました。
このように、比較的、簡単な数式をつかうことで、予測値を算出することができました。
しかし、この予測値は、SLOPE関数で算出した傾きやINTERCEPT関数で切片を算出しなくても、FORECAST.LINEAR関数で、一発算出することができます。
=FORECAST.LINEAR(F5,C2:C11,B2:B11)
単回帰式のxであるF5をつかって、あとは、Y軸とX軸を設定します。
その算出結果が、
3020.28
先程、SLOPE関数やINTERCEPT関数をつかって、算出した結果と同じ値が算出されていることが確認できました。
このように、FORECAST.LINEAR関数だけでも、知っているだけで、データがあれば、直感に頼らずに、予測値を算出することができます。
VLOOKUP関数のように、検索値に合致したデータを検索抽出してくれる関数というのがいくつかあります。
ちょっとしたリストの時には、VLOOKUP関数をつかわなくても、手早く設定できる関数があります。
そのひとつが「CHOOSE関数」
Excel VBAでは、「CHOOSE」を作業処理の分岐でよくつかうので、プログラミングになれている人には、VLOOKUP関数よりも理解しやすいかもしれませんね。
VLOOKUP関数は、検索結果を表示するだけですが、CHOOSE関数は、数式を使って、状況に応じた値を算出することもできます。
CHOOSE関数の読み方は「チューズ」です。
所属は、「検索・行列」です。
CHOOSE関数の引数も確認しておきましょう。
CHOOSE(インデックス,値1,[値2]…)
引数の値の数ですが、254個設定することができます。
今回は、シンプルな使い方を確認しましょう。
=CHOOSE(C2,"庭掃除","窓掃除","掃き掃除")
という数式が設定されています。オートフィルで数式をコピーした状態です。
VLOOKUP関数でもリストをつくらなくても、抽出処理ができますが、CHOOSE関数の方がスマートに処理することができます。
CHOOSE関数がどのような動きをしているのかというと、引数のインデックスの値と合致するものを値1以降から抽出するという仕組みです。
インデックスが「1」ならば、値1のもの。「2」ならば、値2といった具合です。
なので、今回は、C2に、「2」と入力されているので、インデックスの2に該当する「窓掃除」が表示されたというわけです。
大きなリストである必要がない時などは、CHOOSE関数という方法もありますので、状況によっては、VLOOKUP関数の代わりにつかってみると、数式をコンパクトにすることができるかもしれませんね。
簡単そうに思う作業程、結構面倒ということがあります。
例えば、次のような表があります。
このカ行という行単位のデータというのが、厄介なんですね。
作業を具体的に考えるとしたら、フリガナを表示させて、その左一文字がカ～コなのか判定させて、合致したデータを抽出先に、コピーするという感じでしょうか。
かなり面倒です。
しかし、Excel VBAには、フリガナを行単位で比較的簡単に抽出する方法があるのです。
その方法は、ワイルドカードを使う方法です。
ワイルドカードをつかったら、抽出できるのでしょうか？
そもそも、最初の一文字目が異なりますので、例えば「カ*」としても、「キ～」のデータは合致しませんので、抽出対象にはなりません。
どうしてもLEFT関数をつかい、フリガナを表示するPHONETIC関数をつかうことになるわけです。
ところが、Excel VBAのワイルドカードは、Accessのように、フリガナの行選択で抽出することができるのです。
では、プログラム文を作っていきます。
Sub カ行抽出()
Dim i As Long
Dim lastrow As Long
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
For i = 2 To lastrow
If Cells(i, "b").Phonetic.Text Like "[カ-コ]*" Then
Cells(i, "b").Copy Cells(lastrow, "d").End(xlUp).Offset(1, 0)
End If
Next i
End Sub
これを実行すると、カ行に該当するデータを抽出することができました。
お馴染み、変数宣言です。
Dim i As Long
Dim lastrow As Long
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
Lastrowは、データの最終行番号です。
この数値をつかって、このあとのFor文の繰り返し数で使用します。
For i = 2 To lastrow ～ Next i
For文です。見出しを除くので、2から、最終レコード行数までを繰り返し処理をします。
If Cells(i, "b").Phonetic.Text Like "[カ-コ]*" Then End If
ここが、今回のカ行を抽出するところです。
「Phonetic.Text」で、該当するセルのフリガナ情報をGetします。
そして、Like "[カ-コ]*"。
Like演算子をつかった、ワイルドカードで行を抽出できます。
「[カ-コ]*」とすると、「カ*」「キ*」…「コ*」のように、カ～コで始まるデータという意味になります。
Excel VBAでは、可能なのですが、通常のExcelのワイルドカードでは、"[カ-コ]*"という設定を行うことができません。
Excelではできないけど、Excel VBAならできるということが、ありますので、Excel VBAの知識も増やしていくと、効率の良い解決方法が、見つかるかもしれません。
If文の中の処理は、
Cells(i, "b").Copy Cells(lastrow, "d").End(xlUp).Offset(1, 0)
該当するデータがあったら、D列にコピーします。
なお、.End(xlUp).Offset(1, 0)で、最終データのさらに下のセルと定義することができますので、データを上書きすることはありません。
Facebookページで【書いてみた】ワンポイントです。
11月15日
Excel。average関数は平均値を求める関数です。
11月16日
Excel。averagea関数は文字列を0として平均値を求める関数です。
11月17日
Excel。averageif関数は単一条件に合うデータの平均値関数です。
11月18日
Excel。averageifs関数は複数条件に合うデータの平均値関数です。
11月19日
Excel。max関数は最大値関数です。
11月20日
Excel。min関数は最小値関数です。
11月21日
Excel。large関数は○番目に大きい値を算出関数です。
Excelの条件付き書式で、日付を条件で使用する時には、少し注意する必要があります。
セル参照をつかわないで、条件を直接入力して設定すると、設定した書式が反映されないで悩むことがあります。
次の表を使って説明していきます。
事前の確認として、行全体が対象となるので、複合参照を使う必要があります。
このことから、数式を使った設定をする必要があります。
また、データをみると、空白セルがあることから、AND関数をつかった数式を設定する必要があります。
A2:C11を範囲選択します。
ホームタブの条件付き書式の「新しいルール」をクリックします。
新しい書式ルールダイアログボックスが表示されます。
=and($C2<>"",$C2<2021/10/1)
あとは、書式ボタンをクリックして、塗りつぶす色を選択します。
OKボタンをクリックしてみると、残念ながら、反映されていません。
複数の条件が合致する場合に書式を反映させたいので、AND関数には問題はありません。
C2を複合参照で設定しているには、行全体を塗りつぶすためなので、問題はありません。
原因は、「日付」なのです。ここが、直接数式で設定する場合のポイントです。
数式の日付をみてみると、
$C2<2021/10/1
となっています。
一見問題ないように見えますね。
ここで「”（ダブルコーテーション）」で囲っていないからだと思うかもしれませんが、
$C2<”2021/10/1”
としたところで、状況に変化はありません。
なぜならば、今回の原因が、「シリアル値」にあるからです。
「”（ダブルコーテーション）」をつければ、文字になってしまいます。
どのようにしたら、シリアル値にすることができるのか？
わざわざ、条件の日付のシリアル値を調べて、その数値を入力するというのは、面倒です。
そこで、DATE関数をつかいます。
条件を次のように変更してみます。
=AND($C2<>"",$C2<date(2021,10,1))
実行して確認してみましょう。
条件で日付をつかうことは、アチラコチラで発生しますが、その時に、うまくリアクションしないときには、シリアル値のことを思い浮かべることができるといいかもしれませんね。
データ内の数値の大小を、わかりやすく視覚的に表示することができる、条件付き書式のデータバー。
せっかくなので、わかりやすくしたいわけです。
そこで、データバーをとなりの列に表示することで、数値とデータバーが重ならない資料を作ることができます。
C列に、セル参照の数式を設定します。
=B2
という数式を設定したら、オートフィルで数式をコピーします。
C2:C7を範囲選択して、条件付き書式のデータバーを設定します。
データバーの設定をアレンジしていきますので、C2:C7を改めて範囲選択します。
条件付き書式ルールの管理ダイアログボックスが表示されます。
書式ルールの編集ダイアログボックスが表示されます。
その後、OKボタンをクリックします。
条件付き書式ルールの管理ダイアログボックスに戻りますので、OKボタンをクリック
条件付き書式のデータバーは、簡単な数値の強弱を確認するのに長けていますので、つかってみると、資料がより一層、わかりやすくなるかもしれませんね。
売上データを四半期別に集計する場合、売上日がどの四半期に所属しているのかがわからないと、四半期別に集計することができません。
4-6月だったら、「1」それ以外は…というように、IF関数のネストを繰り返す方法も悪くはありませんが、面倒です。
CHOOSE関数を使う方法もありますが、せっかくIFS関数という新しい関数が追加されたわけですから、使わないのはもったいない。
IF関数でネストを繰り返すよりもシンプルに、つくることができる、IFS関数をつかって、今回は、四半期別集計をおこなっていきます。
事前の準備として、D2:D5。第１四半期と表示されていますが、セルの値自体は「1」～「4」としています。
表示形式のユーザー定義をつかって、「第１四半期」と表示させています。
これは、どの四半期に所属しているのかを「1」～「4」の数値で算出するため、SUMIF関数をつかって集計する時に、効率よく数式を作るために行っています。
C2に次の数式を設定します。
=IFS(MONTH(A2)>=10,3,MONTH(A2)>=7,2,MONTH(A2)>=4,1,TRUE,4)
数式を説明します。
A2の月をMONTH関数で算出します。
その値が10以上。
つまり、10～12月だったら、第3四半期なので「3」と算出させています。
次の条件は、「7以上なのか」とします。
すでに、10以上は除外されますので、7～9月だったら、第２四半期なので「2」と算出させます。
最後のTRUEは、該当しないものはということなので、1～3月なので、第４四半期に当たりますから「4」と算出させることができます。
IF関数のネストで数式を作成するよりも、コンパクトで、しかも他の関数をつかうよりも、わかりやすい数式です。
この数式をオートフィルでコピーします。
E2にSUMIF関数をつかって、集計します。
E2に設定した数式は、
=SUMIF($C$2:$C$11,D2,$B$2:$B$11)
これで、四半期別に集計することができました。
今回は、IFS関数をつかって、四半期がどの四半期に所属しているのか判別しましたが、IFS関数が搭載されていないExcelのバージョンだと当然、IFS関数をつかうことはできませんので、他の関数をつかった方法も知っておくといいかもしれませんね。
さて、最後に、C列。
算出結果が表示されたままでは、カッコ悪いので、算出結果が見えないようにしておきましょう。
C2:C11を範囲選択して、セルの書式設定ダイアログボックスを表示します。
表示形式のユーザー定義にあわせます。
これで、完成です。
Facebookページで【書いてみた】ワンポイントです。
11月8日
Excel。sumifs関数は複数条件での合計値関数です。
11月9日
Excel。subtotal関数は抽出データの集計関数です。
11月10日
Excel。count関数は数値件数関数です。
11月11日
Excel。counta関数は空白以外のセル個数件数関数です。
11月12日
Excel。countif関数は単一条件に合うデータ件数関数です。
11月13日
Excel。countifs関数は複数条件に合うデータ件数関数です。
11月14日
Excel。countblank関数は空白のセル個数件数関数です。
折角のデータ。棒グラフや折れ線グラフで留めるのはもったいないですよね。
そこで、散布図をつくることで、データがどのように散布されているのか、さらに近似曲線をグラフに表示することで、予測もたてることができるかもしれません。
用意したデータです。
B7:C11を範囲選択します。
挿入タブの「散布図またはバブルチャートの挿入」にある「散布図」をクリックします。
近似曲線の書式設定作業ウィンドウが表示されますので、「直線近似」を選択して、予測にある「グラフに数式を表示する」と「グラフにR-2乗値を表示する」にチェックします。
なお、見やすいようにプロットエリアを塗りつぶし、数式のフォントサイズもアップしています。
R2乗の数値は何を意味しているのかというと、数式の精度を表していて、0～1の間の数値を表示しますが、「1」に近いほど、予測の精度は高いことを意味しています。
今回は、0.8805ということで、「1」に近いので、数式の精度は高いことを意味しています。
「y = 152.22x - 24.197」
この数式は、回帰式と呼ばれています。
y=ax-bという式で、「a」を「回帰係数」と呼び、「b」を「切片」と呼びます。
学生時代の一次方程式は、このために学んだのかもしれませんね。
横軸の「x」ですが、データをみると、来店数なので、例えば、来店数が「30」になった場合、売上高の予測値は、「y = 152.22×30 - 24.197」ですから、「4542.403」と算出された結果が予測されるとわかるわけですね。
このようなことから、データをしっかりそろえることで、いままで直感だったりした、予測よりも、数値として、ある程度予測した値を把握することが、比較的容易にできるようになります。
今回は、NOW関数～NUMBERVALUE関数までをご紹介しております。
NOW関数
ナウ
現在の日付と時刻を算出します。
NOW()
NPER関数
エヌパー：ナンバー・オブ・ピリオド
元利均等返済における支払回数を算出します。
NPER(利率,定期支払額,現在価値,[将来価値],[支払期間])
NPV関数
エヌピーヴイ： ネット・プレズント・バリュー
キャッシュフローに基づいた正味現在価値を算出します。
NPV(割引率,値1,[値2],…)
NUMBERSTRING関数
ナンバーストリング
数値を漢数字に変換します。
NUMBERSTRING(数値,書式)
NUMBERVALUE関数
ナンバーバリュー
特定の地域に依存しない方法で文字列を数値に変換します。
NUMBERVALUE(文字列,[小数点記号],[桁区切り記号])
Excelには、簡単にできそうと思うと、そう簡単にできないものというのが結構あります。
たとえば、次のような場合。
簡単なのではと思ったら、ちょっと厄介なんですね。
まずは、行全体を塗りつぶしたいわけなので、数式による条件を作る必要があります。
そして、次に問題になるのが、空白セルの問題です。
とりあえず、E1よりも前の日だったらという条件式をつくって、該当するセルが塗りつぶされるように設定していきます。
A2:C11を範囲選択します。
ホームタブの「条件付き書式」にある「新しいルール」をクリックします。
ルールの種類を「数式を使用して、書式設定するセルを決定」を選択したら、「次の数式を満たす場合に値を書式設定」のボックスに、
=$C2<$E$1
そして、書式ボタンで、塗りつぶしの色を設定します。
OKボタンをクリックします。
行全体を塗りつぶすことはできました。
セル参照を、「$C2」のように、列固定の複合参照で設定することで、行全体を塗りつぶすことができます。
しかし、よくみると、空白セルになっている行も塗りつぶしの対象になってしまっています。
日付はシリアル値で管理されています。
そのため、指定の日付より前という条件だと、空白セルは「0（ゼロ）」として認識されるために、塗りつぶしの対象になってしまうのです。
そのため、「0（ゼロ）」を除く、すなわち、「空白セルではない」という条件を先程の条件式に追加する必要があります。
=AND($C2<$E$1,$C2<>"")
と今度は、AND関数をつかった数式を設定します。
まずは、結果を確認してみましょう。
では、設定した数式を確認しておきます。
=AND($C2<$E$1,$C2<>"")
AND関数をつかうことで、複数条件が成立したもの以外は対象外にすることができます。
そして、「$C2<>""」を追加することで、空白セルを除くことができます。
このように、条件付き書式で日付を使うときには、ちょっと注意する必要がありますね。
Facebookページで【書いてみた】ワンポイントです。
11月1日
Excel。シートを作業グループにしておくと、特定の複数シートが一度に印刷することが出来ちゃうんです。
11月2日
Excel。さて質問。「余白の表示」ってボタン。どこにあるか知っていますか？
11月3日
Excel。「余白の表示」ボタンの正解は、ファイルタブ→印刷→右下の２つあるボタンの左側です。
11月4日
Excel。印刷。【先頭ページのみ別指定】にチェックをすると、先頭ページのヘッダーとフッターを他のページと違う設定に出来ますね。
11月5日
Excel。ワークシートに規定で設定されているフォントを【標準フォント】といったりします。時々本に出ていて、ご質問を受ける事があります。
11月6日
Excel。sum関数は合計値を求める関数です。ちなみにアイディアで累計も出せます。
11月7日
Excel。sumif関数は単一条件での合計値関数です。
データを読み込んだ後に、抽出だけを行って作業終了というよりも、その抽出したデータを別シートにコピーするなど、抽出後の作業が伴うことになるかと思います。
抽出したデータをコピーすること自体は、厄介ではないのですが、「見出し行」を除いてデータのみを別シートにコピーしたい場合は、どのようにしたらいいのでしょうか。
コピーする範囲をOffsetなどつかって、見出し行を除いた範囲を設定する方法もあるかもしれませんが、CurrentRegionプロパティをつかうことで、見出し行も含めてコピーすることができます。
そのため、コピーした後に見出し行を削除するほうが、簡単にデータのみにすることができます。
次のデータを使います。
Sub 見出し行除き()
Range("a1").AutoFilter field:=2, Criteria1:=3
If WorksheetFunction.Subtotal(3, Range("b:b")) > 1 Then
Worksheets.Add after:=Sheets(Sheets.Count)
Worksheets("data").Range("a1").CurrentRegion.Copy Range("a1")
Range("a1").EntireRow.Delete
Worksheets(Sheets.Count).Name = "copy"
Worksheets("data").Range("a1").AutoFilter
End If
End Sub
とてもシンプルなプログラム文で、見出し行を除いてコピーすることができます。
それでは、実行してみましょう。
このように、別シートに見出し行を除いたデータのみをコピーすることができました。
プログラム文を見ていきましょう。
Range("a1").AutoFilter field:=2, Criteria1:=3
この行は、オートフィルターをつかって抽出作業を行っています。
AutoFilter で、オートフィルターを設定します。
field:=2 は、左から2列目のフィールドを指しますので、「店舗番号」の列が該当します。
Criteria1:=3 の「Criteria」は条件。
つまり抽出条件のことなので、「3」の品川店を抽出する設定のオートフィルターを設定することができます。
If WorksheetFunction.Subtotal(3, Range("b:b")) > 1 Then ～End If
このIf文は、何をやっているのかというと、該当するデータがあれば、それ以降を実行して、抽出データがなければ、実行しないためのプログラム文です。
WorksheetFunction.Subtotal は、ワークシート関数のSubtotal関数をつかって、データのあるなしを確認しています。
Worksheets.Add after:=Sheets(Sheets.Count)
データがあることが判明したので、コピーを行うわけですが、コピー先のシートがないので、Worksheets.Add でシートを追加します。
なお、after:=Sheets(Sheets.Count)で、最終シートの後ろにシートを追加させます。Sheets.Count でブック内のシートの枚数を確認することができます。
Worksheets("data").Range("a1").CurrentRegion.Copy Range("a1")
追加したシートのA1に、抽出したデータをコピーします。
Range("a1").EntireRow.Delete
コピーしたデータの、1行目を削除します。これで、見出し行を除いたデータにすることができました。
Worksheets(Sheets.Count).Name = "copy"
追加したシート名が設定されていませんので、「copy」というシート名で設定します。
Worksheets("data").Range("a1").AutoFilter
コピー作業が終了したので、抽出するためのオートフィルターを解除します。
これ以外にも、様々な方法で作ることができますので、色々考えてみるのもいいかもしれませんね。