ぼくLog

子持ち、車持ち、マンション持ちの僕の日常を綴ります。車と、ラクをしたい一心で覚えたエクセルVBAを中心になりそうです。

ルルドプレミアム マッサージクッション ダブルもみVW が最高に気持ちいいらしい

f:id:yt4u:20170525222545j:plain

気持ちいいらしいというのは、ぼくはこれを使っていないからです。
使っているのはヨメで、気持ちいいというのはヨメの感想なんです。ハイ。

如何せんぼくはマッサージなるものが非常に苦手。
くすぐったくなることはあっても気持ちよくなることはありません。
凝りとかいう類のものとは無縁なんです。いいでしょ。



そんな中、このマッサージクッションを買うきっかけとなったのは「母の日」。

ぼくは「母の日」の存在をすっかり忘れていたのですが、5歳の息子が「母の日だからお母さんに何か買ってあげようよ」と言うじゃありませんか。
それまで自分からそんなことを言い出すことなんてなかった息子からの突然の提案に、子ども成長を感じざるを得ませんでした。
それはそれはぼくの瞳には涙が浮かんでいたことでしょう。


早速、ヨメには「子どもと公園で遊んでくる~」と言って、息子と一緒にプレゼント探しにGO!

そしたら立ち寄ったFrancfrancにあったんですね。このマッサージクッションが。



目に入った瞬間「これだ!」と思ったわけです。
ヨメは日ごろから「腰が痛い」だの「首が凝った」だの「凝りが目に来た頭痛がしてきた」とぼくには耳タコ状態。
さすがにマッサージチェアを置くようなスペースはぼくのおうちにはありませんが、クッションならNP・・・No Problem。

そう思って、ぼくはマッサージ苦手ながらも、店頭でとりあえずマッサージクッションを腰に当ててみる。
そしたら結構グリグリくるじゃありませんか。
マッサージ苦手のぼくにも「なんか効きそ~」と思わせる力、ありました。

息子の提案から始まったプレゼント探しだったので、もう少し子ども目線のプレゼントの方がいいかな~とは思いつつ、息子に「これどう?お母さん、いつも”腰痛い”って言ってるから喜ぶと思うよ」と問うと息子も完全同意。
どうやら息子にもヨメの「腰痛い~」が耳タコだった模様。
当初の想定よりも多少値が張るものになってしまいましたが、息子の初提案から始まったプレゼント探しに気をよくしていたぼくは一念発起してお買い上げ~。


おうちに持ち帰って早速、息子経由でヨメにマッサージクッションをプレゼント。

ヨメは喜んですぐにお試しでマッサージスタート。
お試し・・・のはずが、あまりの気持ちよさにクッションから離れられないと言い始める始末。
どうやら相当気に入ってくれたらしい。


ということで、マッサージクッションを手に入れたヨメは大満足。
お母さんにプレゼントできた息子も大満足。
ふたりを見たぼくも大満足、と久々にいい買い物ができました。


エラー値を含む計算はAGGREGATEを使え!|エクセル関数

複数のセルに数値が入っていて、その合計を求めたいときはSUM関数を使いますよね。

でも、その集計対象の中にエラー値が入っていたらどうなるでしょうか?


例えば、こんな状態です。

f:id:yt4u:20170503200853j:plain

合計対象の中に「#N/A」というエラー値が含まれていますよね。


エラー値を含むセル範囲に対してSUMを実行すると、

f:id:yt4u:20170503200909j:plain

はい。エラーが返ってきます。


では、合計対象のセル範囲にエラー値が含めまれている時、そのエラーを無視して合計を求めるにはどうすればいいのでしょうか?



その答えが、このエントリのタイトルにあるAGGREGATE関数です。


では早速、AGGREGA関数を使って計算してみます。

f:id:yt4u:20170503200838j:plain


エラー値を含む範囲を指定した状態で計算を実行すると、

f:id:yt4u:20170503200846j:plain


はい。見事に計算できました。


。。。
と、AGGREGATE関数の使い方、特に引数について一切の説明を飛ばしてしまってますので、改めて説明すると、、、


まず、第一引数(上図では9となっているところ)では集計方法を指定しています。

集計方法は下表の19パターンが用意されています。


集計方法 関数
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV.S
8 STDEV.P
9 SUM
10 VAR.S
11 VAR.P
12 MEDIAN
13 MODE.SNGL
14 LARGE
15 SMALL
16 PERCENTILE.INC
17 QUARTILE.INC
18 PERCENTILE.EXC
19 QUARTILE.EXC


第一引数の9はSUMすることを表していたのです。



次に、第二引数(上図では7となっていたところ)は集計時のオプションを指定します。
ここがエラー値を除いて計算する際のポイントです。


用意されているオプションは下表のとおり。


オプション 動作
0 または省略 ネストされた SUBTOTAL 関数と AGGREGATE 関数を無視します。
1 非表示の行、ネストされた SUBTOTAL 関数と AGGREGATE 関数を無視します。
2 エラー値、ネストされた SUBTOTAL 関数と AGGREGATE 関数を無視します。
3 非表示の行、エラー値、ネストされた SUBTOTAL 関数と AGGREGATE 関数を無視します。
4 何も無視しません。
5 非表示の行を無視します。
6 エラー値を無視します。
7 非表示の行とエラー値を無視します。


上の例では7を指定して、非表示の行とエラー値を無視して計算するようにしていました。



そして、第三引数で集計対象のセルを指定しています。



今回の例ではセル範囲を合計していましたが、第一引数の表のとおり、計算方法は合計(SUM)だけではなくAVERAGE等、普段使うことの多い集計関数が網羅されています。


そして第二引数の表のとおり集計オプションが用意されていますので、それを使ってエラー値を無視して計算することが可能になるわけです。

第二引数でおおよそ使うことになるのは、5・6・7の3パターンになると思います。
(4を指定するならAGGREGATE関数使わずに、従来からある関数使いますよね。)


外資系投資銀行がやっている 最速のExcel

外資系投資銀行がやっている 最速のExcel



ということで、エラー値を含むセル範囲の計算にはAGGREGATE関数を使うのがベストチョイス。
さらに非表示行を集計対象に含めいないようにすれば、例えばオートフィルタを掛けたときに、表示されているセルだけを集計する、といったことにも対応できるわけです。


一昔前であれば、エラー値を集計対象から除くために「=SUMIF(集計範囲, “<>#N/A”)」なんてやっていました。
これだとエラーの種類が「#N/A」の時だけ集計対象から除くことになるので、そのほかのエラー値も除きたいとなると非常にややこしいことになっていました。


それがAGGREGATE関数であれば一発で解決できるワケです。


非常に便利、この上ないですねー。

セルには出来る限り名前を付けるべし!|エクセルVBA

f:id:yt4u:20170523232355j:plain

結論はタイトルの通り、セルには出来る限り名前を付けるべし!ということです。

すべてのセルに名前を付ける必要はありませんが、データ表のテッペン(特にTopLeft)やデータエリアそのもの、VBAで引数として扱われるような値、あるいはグラフのデータ範囲など、データの起点となるセルや、データのひとかたまりとなるセル範囲に名前を付けてあげると、VBAでのセルおよび値の操作が非常にラクになります。

この「名前の定義」は、数式バーが表示されているのであれば、その左側に表示されている「名前ボックス」に付けたい名前を入力することで定義可能です。 その他、リボンの「数式」→「名前の定義」でもOK。

定義済みの名前は、リボンの「数式」→「名前の管理」で確認することが可能。
ショートカットで開く場合は「Ctrl+F3」です。

ワークシートってどうやって選択すればいいの?Worksheets("Sheet1")?Worksheets(1)?Sheet1?|エクセルVAB

f:id:yt4u:20170318221759j:plain

ワークシートの特定にはCodeName(コードネーム)を使った方がいいというエントリーを書きました。

yt4u.hatenablog.com


CodeNameを使え!なんてエラそうなこと言ってますが、実はぼくはずっとCodeNameの存在を知りませんでした。
ホントにずぅーーーーっと知りませんでした。

そしてCodeNameの存在を知ったときは、あまりの感動に暫く絶句した記憶がございます。はい。


その感動の大部分は「これでワークシート名の変更に左右されずシートを扱える」というもの。

そしてもう一つが、ぼくにとって長年の謎であったことが解決したからです。


ワークシートを特定する3つの方法

とあるブックに3つのワークシートがあったとして、その一番左端のワークシート名が「Sheet1」だったとします。

このとき、「Shee1」シートを選択する方法として、次の2つの方法をよく見かけます。

  1. Worksheets(“Sheet1”).Select
  2. Worksheets(1).Select

(ワークシートをSelectすることなんてほとんどないと思いますが、分かりやすいメソッドなので以下Selectを前提に書いています。)


1は単純に「Sheet1」というワークシート名を拠り所に指定する方法です。

2はワークシートの集合体であるワークーシートコレクションの中から、各ワークシートに振られたインデックス番号を使って呼び出す方法です。
これは、ワークシートの複数枚ある場合、自動的に左端のワークシートを1として順にインデックスが割り振られていく、という特性を利用したものです。

ちなみに、2がワークシートコレクションというCollectionオブジェクトなんだという観点に立つと、実は1もCollectionオブジェクトらしい挙動だということが分かってきます。
つまり、Collectionの中から1つ取り出すときにインデックスを使って取り出すほか、Keyを使って取り出す方法があるわけですが、1の方法はまさしく後者に他ならないのです。

このときのKeyは何かというと、ワークシート名(=Sheet1)そのものですよね。


なお、Collectionについてはコチラのエントリーを参考にしてみてください。

yt4u.hatenablog.com


話が少し逸れてしまいましたが、長年の謎は上の2つではありません。

もうひとつワークシートを指定する方法で、なぜそれで指定できるのか長きに亘って謎だったのはこれです。

  Sheet1.Select


これで前述の例で状態だと「Sheet1」という名前のワークシートが選択できてしまうんですよね。

これで軽く混乱することになります。

「Sheet1」で選択できるなら、「Worksheets(1)」とかする必要ないんじゃないの?
だってその方が打ち込む文字数が少なくて済むし。、、、と。

そう思って「Sheet1」を使い始めると、さらに混乱が襲ってきます。

それは例えば、先に挙げた「Sheet1」という名前のワークシートをひとつ右に移動した場合、「Worksheets(1).Select」では「Sheet1」シートは指定できなくなります。
「Sheet1」シートを選択するためには「Worksheets(2).Select」とする必要があるんですよね。
でも相変わらず「Sheet1.Select」で「Sheet1」(という名前の)ワークシートを選択することができます。

「Sheet1」はOKだけど「Worksheets(1)」はダメで、でもそのシートが左端ならOKで、、、って何が何だか状態必至。
似たようなコードなのに挙動は全く変わってくるので混乱せずにはいられないですよね。


まさにぼくは長年に亘り「Worksheets(1)」と「Sheet1」の立ち位置の違いを理解できず混乱し続けていました。

それがCodeNameの存在を知って、一気に解決することになったのです。


そうです。「Sheet1」はCodeNameそのものだったのです。


VBEで「Sheet1」シートのプロパティを見れは一目瞭然。

f:id:yt4u:20160617234920j:plain

「ぼく」シートのCodeName(=オブジェクト名)は「Sheet1」になっていますよね。

  Sheet1.Select

はCodeNameを使ってワークシートを選択することに他ならないのですね。


試しに「Sheet1」を違う文字に書き換えてみください。

その状態で

  Sheet1.Select

とやると、そんなできるかぼけぇ、ってエクセルに怒られますよね。

エクセルのシートをデータベースのテーブルとして扱うとめちゃくちゃ簡単にデータを加工できる!|エクセルVBA

エクセルで表データを扱うときに、その表をデータベースのテーブルとして扱うとものすっごぉぉっく簡単に扱えるようになります。


例えばこういった住所録があったとします。

f:id:yt4u:20170102225905j:plain

(住所録はなんちゃって個人情報で生成したダミーデータです。)

このなかの「性別」「年齢」「婚姻」を使って、データを抽出するとします。

例えばこうです。

  • 性別=男性
  • 年齢=30歳以上50歳未満
  • 婚姻=未婚

このすべての条件を満たすデータを抽出して、別シートにその結果を出力する。

これをVBAを使って行う場合、どのような方法が考えられるか?

  1. エクセルのデータベース関数を使う
  2. シート上の表データを一行ずつチェックして指定条件に該当するデータを別シートに転記する
  3. シート上の表データにオートフィルタを掛け、オートフィルタを操作することで指定条件に該当するデータを抽出し、別シートにコピーする
  4. シート上の表データをデータベースのテーブルと見なして、指定条件を踏まえたSQLを実行しレコードセットを別シートに出力する


このエントリーのタイトルの通り今回のテーマは4です。
なので4のやり方を詳しく見ていくことになるのですが、その他の手段に関するメリット・デメリットに触れておきます。

1は正直ぼくはやったことがありません(汗

如何せんデータベース関数のヘルプを見るとすごく分かりにくい。
関数を使う前にヘルプで挫折しちゃいます。結果使ったことがないんです。

ひとつ言えることは、データベース関数の使い方を覚えるくらいなら、4の方法を覚えてしまった方が何倍も幸せです。

次に、簡単に2、3でどうやるかに触れるならば、
2は単純に「For~Next」で各行の抽出対象列の値をチェックしていくことになるでしょうし、3は抽出対象列でAutofilter Field:=aaa, Criteria1:=“bbb"」といったようにフィルタを実行していくことになると思います。

2、3だダメというわけではなく、目的は十分に果たせますので問題ありません。ただ、抽出条件をアレコレ変えたりする際に煩わしさを感じることになると思います。


一方で4の場合そういった煩わしさがありません。

その煩わしさはないのですが事前準備がちょっと必要です。
それはエクセルをデータベースとして扱えるようにするための準備です。


エクセルをデータベースとして扱うための準備

最初に示した住所録を持つエクセルのブックにADO接続をします。
ADOとは何ぞ?はWikipediaあるいはMicrosoftへGo!!(上手く説明できないので他人任せ!)

で、接続するためのコードはコチラ。

Public Function GetXLSConnection(DataSource As String) As Object

  Dim objCN             As Object
  Dim strCNString       As String
  
  
  Set objCN = CreateObject("ADODB.Connection")
  
  
  strCNString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                        & "Data Source=" & DataSource & ";" _
                        & "Extended Properties=""Excel 8.0;" _
                        & "HDR=Yes"";"


  objCN.Open strCNString
  
  
  Set GetXLSConnection = objCN
  

End Function


これでエクセルのブックにADO接続を行う準備が出来ました。

実際に接続(コネクションを確立)するサンプルは次のようになります。

Public Sub コネクションサンプル()

  Dim objCN             As Object
  
  'コネクションを確立
    Set objCN = GetXLSConnection(ThisWorkbook.FullName)

End Sub


レコードセットを取得

次に抽出条件を書いたSQLを実行し、その結果であるデータの塊(レコードセット)を受け取る準備をします。

そのコードがコチラ

Private Const adOpenDynamic       As Long = 2
Private Const adLockOptimistic    As Long = 3

Public Function GetRecordSet(strSQL As String, objCN As Object) As Object

  Dim objRS             As Objectc
  
  
  Set objRS = New ADODB.Recordset
  
  
  objRS.Open strSQL, objCN, adOpenDynamic, adLockOptimistic
  
  
  Set GetRecordSet = objRS
  
  
End Function

引数として受け取ったデータベースコネクションの下、SQLを実行し、生成されたレコードセットを返しています。


コネクションとレコードセットを破棄することも忘れずに

忘れたからといって自分ひとりで使う分には何ら問題はありませんが、複数人で使うことになった場合に何かしら不具合が生じる可能性があります。
共有ブックを複数人で弄ると不具合が発生するのに似ていますね。

コネクションの破棄とレコードセットの破棄のコードはこうです。

'==========================================================
'コネクション破棄
'==========================================================
Public Sub CloseConnection(objCN As Object)


  If objCN.State <> adStateClosed Then
  
    objCN.Close
  
  End If
  
  
  Set objCN = Nothing
  

End Sub


'==========================================================
'レコードセット破棄
'==========================================================
Public Sub CloseRecordSet(objRS As Object)


  If objRS.State <> adStateClosed Then
  
    objRS.Close
  
  End If


  Set objRS = Nothing
  
  
End Sub


ここまでで、ブックへの接続とレコードセットを生成する準備、そしてその接続(コネクション)とレコードセットを破棄する準備が整いました。
次に、実際にデータの抽出となります。


データを抽出してみよう!

まずはデータ抽出のコードを示します。 前提として、最初に載せたシート上の表に、セルの名前の定義で「rngXDB_DataBase」と名付けています。 そして抽出したデータを出力するシートのCodeNameを「wsXLSDataBase」、またそのシート上のデータを出力する一番左上のセルを「rngXDB_DataTop」を名付けています。

Public Sub DataGetSample()

  Dim objCN             As Object
  Dim objRS             As Object
  Dim strSQL            As String
  Dim lngF              As Long
  
  
  'コネクションを確立
    Set objCN = GetXLSConnection(ThisWorkbook.FullName)
  
  
  '抽出条件を作成
    strSQL = "SELECT"                                           '抽出フィールド(項目)を指定
      strSQL = strSQL & "  [名前]"
      strSQL = strSQL & ", [ふりがな]"
      strSQL = strSQL & ", [電話番号]"
      strSQL = strSQL & ", MONTH([誕生日]) AS [誕生月]"
    strSQL = strSQL & " FROM rngXDB_DataBase"                   'データテーブルを指定
    strSQL = strSQL & " WHERE 1 = 1"                            '抽出条件
      strSQL = strSQL & " AND [性別] = '男'"                    '性別=男
      strSQL = strSQL & " AND [年齢] >= 30"                     '年齢=30歳以上
      strSQL = strSQL & " AND [年齢] <  50"                     '50歳未満
      strSQL = strSQL & " AND [婚姻] = '未婚'"                  '婚姻=未婚
  
  
  '抽出実行
    Set objRS = GetRecordSet(strSQL, objCN)
  
  
  '抽出結果を出力
    With wsXLSDataBase
    
      With .Range("rngXDB_DataTop")

        
        '出力エリアにある既存データを消去
          .CurrentRegion.ClearContents
          
        
        'フィールド(項目)名を出力
          For lngF = 0 To objRS.Fields.Count - 1
          
            .Offset(, lngF).Value = objRS.Fields(lngF).Name
          
          Next lngF
          
        
        'データを出力
          .Offset(1).CopyFromRecordset objRS
          
    
      End With
    
    End With
  
  
END_PROC:

  'レコードセットを閉じる
    Call CloseRecordSet(objRS)
  
  'コネクションを閉じる
    Call CloseConnection(objCN)


End Sub

上から順を追って見てみると、

  1. データベースと見立てたエクセルブックへのコネクションを確立する
  2. データ抽出条件を書く
  3. データ抽出を実行し、レコードセットをゲット
  4. 抽出したデータを出力する場所を綺麗にする
  5. レコードセットのフィールド名をまずは書き出し
  6. レコード群を一気に出力する
  7. 最後にレコードセットとコネクションを破棄

という流れになっています。

抽出条件を変える場合は2のところ(SQL文)を書き換えればOK。 抽出条件の値をセルから拾ってくるようにすれば、非常に簡単に好きなようにデータを取ってこれるようになります。



改めてコード全文を示します。

Option Explicit

Private Const adOpenDynamic       As Long = 2
Private Const adLockOptimistic    As Long = 3
Private Const adStateClosed       As Long = 0


'==========================================================
'コネクションを返す
'==========================================================
Public Function GetXLSConnection(DataSource As String) As Object

  Dim objCN             As Object
  Dim strCNString       As String
  
  
  Set objCN = CreateObject("ADODB.Connection")
  
  
  strCNString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                        & "Data Source=" & DataSource & ";" _
                        & "Extended Properties=""Excel 8.0;" _
                        & "HDR=Yes"";"


  objCN.Open strCNString
  
  
  Set GetXLSConnection = objCN
  

End Function


'==========================================================
'レコードセットを返す
'==========================================================
Public Function GetRecordSet(strSQL As String, objCN As Object) As Object

  Dim objRS             As Object
  
  
  Set objRS = CreateObject("ADODB.Recordset")
  
  
  objRS.Open strSQL, objCN, adOpenDynamic, adLockOptimistic
  
  
  Set GetRecordSet = objRS
  
  
End Function


'==========================================================
'コネクション破棄
'==========================================================
Public Sub CloseConnection(objCN As Object)


  If objCN.State <> adStateClosed Then
  
    objCN.Close
  
  End If
  
  
  Set objCN = Nothing
  

End Sub


'==========================================================
'レコードセット破棄
'==========================================================
Public Sub CloseRecordSet(objRS As Object)


  If objRS.State <> adStateClosed Then
  
    objRS.Close
  
  End If


  Set objRS = Nothing
  
  
End Sub


'==========================================================
'データ取得サンプル
'==========================================================
Public Sub DataGetSample()

  Dim objCN             As Object
  Dim objRS             As Object
  Dim strSQL            As String
  Dim lngF              As Long
  
  
  'コネクションを確立
    Set objCN = GetXLSConnection(ThisWorkbook.FullName)
  
  
  '抽出条件を作成
    strSQL = "SELECT"                                           '抽出フィールド(項目)を指定
      strSQL = strSQL & "  [名前]"
      strSQL = strSQL & ", [ふりがな]"
      strSQL = strSQL & ", [電話番号]"
      strSQL = strSQL & ", MONTH([誕生日]) AS [誕生月]"
    strSQL = strSQL & " FROM rngXDB_DataBase"                   'データテーブルを指定
    strSQL = strSQL & " WHERE 1 = 1"                            '抽出条件
      strSQL = strSQL & " AND [性別] = '男'"                    '性別=男
      strSQL = strSQL & " AND [年齢] >= 30"                     '年齢=30歳以上
      strSQL = strSQL & " AND [年齢] <  50"                     '50歳未満
      strSQL = strSQL & " AND [婚姻] = '未婚'"                  '婚姻=未婚
  
  
  '抽出実行
    Set objRS = GetRecordSet(strSQL, objCN)
  
  
  '抽出結果を出力
    With wsXLSDataBase
    
      With .Range("rngXDB_DataTop")
     
        
        '出力エリアにある既存データを消去
          .CurrentRegion.ClearContents
          
        
        'フィールド(項目)名を出力
          For lngF = 0 To objRS.Fields.Count - 1
          
            .Offset(, lngF).Value = objRS.Fields(lngF).Name
          
          Next lngF
          
        
        'データを出力
          .Offset(1).CopyFromRecordset objRS
          
    
      End With
    
    End With
  
  
END_PROC:

  'レコードセットを閉じる
    Call CloseRecordSet(objRS)
  
  'コネクションを閉じる
    Call CloseConnection(objCN)


End Sub


データベース関連のコードはいつでも使えるようにスタンバイ

上記のデータベース関係のコード(コネクション確立、レコードセット取得、およびそれらの破棄)は一度用意しておけばいくらでも使い回しが出来ます。

使いたいときにコードを対象ブックの標準モジュールにコピペするでもいいですし、一連のコードをクラスにひとまとめにして、そのクラスをインポートすればいつでも使えるようにするでもいいと思います(クラス化については別機会に書きます)。