エラー値を含む計算はAGGREGATEを使え！｜エクセル関数
複数のセルに数値が入っていて、その合計を求めたいときはSUM関数を使いますよね。
でも、その集計対象の中にエラー値が入っていたらどうなるでしょうか？
例えば、こんな状態です。
合計対象の中に「#N/A」というエラー値が含まれていますよね。
エラー値を含むセル範囲に対してSUMを実行すると、
はい。エラーが返ってきます。
では、合計対象のセル範囲にエラー値が含めまれている時、そのエラーを無視して合計を求めるにはどうすればいいのでしょうか？
ビジネスエリートの「これはすごい！」を集めた 外資系投資銀行のエクセル仕事術???数字力が一気に高まる基本スキル
- 作者: 熊野整
- 出版社/メーカー: ダイヤモンド社
- 発売日: 2015/02/20
- メディア: 単行本（ソフトカバー）
- この商品を含むブログ (3件) を見る
その答えが、このエントリのタイトルにあるAGGREGATE関数です。
では早速、AGGREGA関数を使って計算してみます。
エラー値を含む範囲を指定した状態で計算を実行すると、
はい。見事に計算できました。
。。。
と、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関数使わずに、従来からある関数使いますよね。)
- 作者: 熊野整
- 出版社/メーカー: KADOKAWA
- 発売日: 2017/04/28
- メディア: 単行本
- この商品を含むブログを見る
ということで、エラー値を含むセル範囲の計算にはAGGREGATE関数を使うのがベストチョイス。
さらに非表示行を集計対象に含めいないようにすれば、例えばオートフィルタを掛けたときに、表示されているセルだけを集計する、といったことにも対応できるわけです。
一昔前であれば、エラー値を集計対象から除くために「=SUMIF(集計範囲, "<>#N/A")」なんてやっていました。
これだとエラーの種類が「#N/A」の時だけ集計対象から除くことになるので、そのほかのエラー値も除きたいとなると非常にややこしいことになっていました。
それがAGGREGATE関数であれば一発で解決できるワケです。
非常に便利、この上ないですねー。
セルには出来る限り名前を付けるべし！｜エクセルVBA
結論はタイトルの通り、セルには出来る限り名前を付けるべし！ということです。
すべてのセルに名前を付ける必要はありませんが、データ表のテッペン（特にTopLeft）やデータエリアそのもの、VBAで引数として扱われるような値、あるいはグラフのデータ範囲など、データの起点となるセルや、データのひとかたまりとなるセル範囲に名前を付けてあげると、VBAでのセルおよび値の操作が非常にラクになります。
この「名前の定義」は、数式バーが表示されているのであれば、その左側に表示されている「名前ボックス」に付けたい名前を入力することで定義可能です。 その他、リボンの「数式」→「名前の定義」でもOK。
定義済みの名前は、リボンの「数式」→「名前の管理」で確認することが可能。
ショートカットで開く場合は「Ctrl+F3」です。
ワークシートってどうやって選択すればいいの？Worksheets("Sheet1")？Worksheets(1)？Sheet1？｜エクセルVAB
ワークシートの特定にはCodeName(コードネーム)を使った方がいいというエントリーを書きました。
CodeNameを使え！なんてエラそうなこと言ってますが、実はぼくはずっとCodeNameの存在を知りませんでした。
ホントにずぅーーーーっと知りませんでした。
そしてCodeNameの存在を知ったときは、あまりの感動に暫く絶句した記憶がございます。はい。
その感動の大部分は「これでワークシート名の変更に左右されずシートを扱える」というもの。
そしてもう一つが、ぼくにとって長年の謎であったことが解決したからです。
ワークシートを特定する3つの方法
とあるブックに3つのワークシートがあったとして、その一番左端のワークシート名が「Sheet1」だったとします。
このとき、「Shee1」シートを選択する方法として、次の2つの方法をよく見かけます。
- Worksheets(“Sheet1”).Select
- Worksheets(1).Select
（ワークシートをSelectすることなんてほとんどないと思いますが、分かりやすいメソッドなので以下Selectを前提に書いています。）
1は単純に「Sheet1」というワークシート名を拠り所に指定する方法です。
2はワークシートの集合体であるワークーシートコレクションの中から、各ワークシートに振られたインデックス番号を使って呼び出す方法です。
これは、ワークシートの複数枚ある場合、自動的に左端のワークシートを1として順にインデックスが割り振られていく、という特性を利用したものです。
ちなみに、2がワークシートコレクションというCollectionオブジェクトなんだという観点に立つと、実は1もCollectionオブジェクトらしい挙動だということが分かってきます。
つまり、Collectionの中から1つ取り出すときにインデックスを使って取り出すほか、Keyを使って取り出す方法があるわけですが、1の方法はまさしく後者に他ならないのです。
このときのKeyは何かというと、ワークシート名(=Sheet1)そのものですよね。
なお、Collectionについてはコチラのエントリーを参考にしてみてください。
話が少し逸れてしまいましたが、長年の謎は上の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」シートのプロパティを見れは一目瞭然。
「ぼく」シートのCodeName(=オブジェクト名)は「Sheet1」になっていますよね。
Sheet1.Select
はCodeNameを使ってワークシートを選択することに他ならないのですね。
試しに「Sheet1」を違う文字に書き換えてみください。
その状態で
Sheet1.Select
とやると、そんなできるかぼけぇ、ってエクセルに怒られますよね。
エクセルのシートをデータベースのテーブルとして扱うとめちゃくちゃ簡単にデータを加工できる！｜エクセルVBA
エクセルで表データを扱うときに、その表をデータベースのテーブルとして扱うとものすっごぉぉっく簡単に扱えるようになります。
データ追加編はこちら！ yt4u.hatenablog.com
例えばこういった住所録があったとします。
（住所録はなんちゃって個人情報で生成したダミーデータです。）
このなかの「性別」「年齢」「婚姻」を使って、データを抽出するとします。
例えばこうです。
- 性別＝男性
- 年齢＝30歳以上50歳未満
- 婚姻＝未婚
このすべての条件を満たすデータを抽出して、別シートにその結果を出力する。
これをVBAを使って行う場合、どのような方法が考えられるか？
- エクセルのデータベース関数を使う
- シート上の表データを一行ずつチェックして指定条件に該当するデータを別シートに転記する
- シート上の表データにオートフィルタを掛け、オートフィルタを操作することで指定条件に該当するデータを抽出し、別シートにコピーする
- シート上の表データをデータベースのテーブルと見なして、指定条件を踏まえたSQLを実行しレコードセットを別シートに出力する
このエントリーのタイトルの通り今回のテーマは4です。
なので4のやり方を詳しく見ていくことになるのですが、その他の手段に関するメリット・デメリットに触れておきます。
1は正直ぼくはやったことがありません（汗
如何せんデータベース関数のヘルプを見るとすごく分かりにくい。
関数を使う前にヘルプで挫折しちゃいます。結果使ったことがないんです。
ひとつ言えることは、データベース関数の使い方を覚えるくらいなら、4の方法を覚えてしまった方が何倍も幸せです。
次に、簡単に2、3でどうやるかに触れるならば、
2は単純に「For~Next」で各行の抽出対象列の値をチェックしていくことになるでしょうし、3は抽出対象列でAutofilter Field:=aaa, Criteria1:="bbb"」といったようにフィルタを実行していくことになると思います。
2、3だダメというわけではなく、目的は十分に果たせますので問題ありません。ただ、抽出条件をアレコレ変えたりする際に煩わしさを感じることになると思います。
一方で4の場合そういった煩わしさがありません。
その煩わしさはないのですが事前準備がちょっと必要です。
それはエクセルをデータベースとして扱えるようにするための準備です。
![Excel 最強の教科書[完全版]――すぐに使えて、一生役立つ「成果を生み出す」超エクセル仕事術](https://images-fe.ssl-images-amazon.com/images/I/51K5WOXW7yL._SL160_.jpg "Excel 最強の教科書[完全版]――すぐに使えて、一生役立つ「成果を生み出す」超エクセル仕事術")
エクセルをデータベースとして扱うための準備
最初に示した住所録を持つエクセルのブックに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.ACE.OLEDB.12.0;" _ & "Data Source=" & DataSource & ";" _ '接続 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 Private mstrErrDescription As String '========================================================== 'GetRecordset '========================================================== Public Function getRecordset( _ ByRef objCN As Object, _ ByVal strSQL As String, _ ByRef objRS As Object _ ) As Boolean getRecordset = False On Error GoTo ERR_PROC Set objRS = CreateObject("ADODB.Recordset") objRS.Open strSQL, objCN, adOpenDynamic, adLockOptimistic getRecordset = True GoTo END_PROC ERR_PROC: MsgBox mstrErrDescription END_PROC: End Function
引数として受け取ったデータベースコネクションの下、SQLを実行し、引数のobjRSにレコードセットを格納しています。
コネクションとレコードセットを破棄することも忘れずに
忘れたからといって自分ひとりで使う分には何ら問題はありませんが、複数人で使うことになった場合に何かしら不具合が生じる可能性があります。
共有ブックを複数人で弄ると不具合が発生するのに似ていますね。
コネクションの破棄とレコードセットの破棄のコードはこうです。
'========================================================== 'コネクション破棄 '========================================================== 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 GetDataSample() 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 " & mcDataRangeName 'データテーブルを指定 strSQL = strSQL & " WHERE 1 = 1" '抽出条件 strSQL = strSQL & " AND [性別] = '男'" '性別=男 strSQL = strSQL & " AND [年齢] >= 30" '年齢=30歳以上 strSQL = strSQL & " AND [年齢] < 50" '50歳未満 strSQL = strSQL & " AND [婚姻] = '未婚'" '婚姻=未婚 '抽出実行 If getRecordset(objCN, strSQL, objRS) = False Then GoTo ERR_PROC End If '抽出結果を出力 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 GoTo END_PROC ERR_PROC: MsgBox mstrErrDescription END_PROC: 'レコードセットを閉じる Call CloseRecordSet(objRS) 'コネクションを閉じる Call CloseConnection(objCN) End Sub
上から順を追って見てみると、
- データベースと見立てたエクセルブックへのコネクションを確立する
- データ抽出条件を書く
- データ抽出を実行し、レコードセットをゲット
- 抽出したデータを出力する場所を綺麗にする
- レコードセットのフィールド名をまずは書き出し
- レコード群を一気に出力する
- 最後にレコードセットとコネクションを破棄
という流れになっています。
抽出条件を変える場合は2のところ（SQL文）を書き換えればOK。 抽出条件の値をセルから拾ってくるようにすれば、非常に簡単に好きなようにデータを取ってこれるようになります。
改めてコード全文を示します。
Option Explicit Private Const adOpenDynamic As Long = 2 Private Const adLockOptimistic As Long = 3 Private Const adStateClosed As Long = 0 Private mstrErrDescription As String Private Const mcDataRangeName As String = "rngXDB_DataBase" '========================================================== 'コネクションを返す '========================================================== Public Function GetXLSConnection(DataSource As String) As Object Dim objCN As Object Dim strCNString As String Set objCN = CreateObject("ADODB.Connection") strCNString = "Provider=Microsoft.ACE.OLEDB.12.0;" _ & "Data Source=" & DataSource & ";" _ objCN.Open strCNString Set GetXLSConnection = objCN End Function '========================================================== 'GetRecordset '========================================================== Public Function getRecordset( _ ByRef objCN As Object, _ ByVal strSQL As String, _ ByRef objRS As Object _ ) As Boolean getRecordset = False On Error GoTo ERR_PROC Set objRS = CreateObject("ADODB.Recordset") objRS.Open strSQL, objCN, adOpenDynamic, adLockOptimistic getRecordset = True GoTo END_PROC ERR_PROC: mstrErrDescription = "レコードセット生成エラー" END_PROC: 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 GetDataSample() 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 " & mcDataRangeName 'データテーブルを指定 strSQL = strSQL & " WHERE 1 = 1" '抽出条件 strSQL = strSQL & " AND [性別] = '男'" '性別=男 strSQL = strSQL & " AND [年齢] >= 30" '年齢=30歳以上 strSQL = strSQL & " AND [年齢] < 50" '50歳未満 strSQL = strSQL & " AND [婚姻] = '未婚'" '婚姻=未婚 '抽出実行 If getRecordset(objCN, strSQL, objRS) = False Then GoTo ERR_PROC End If '抽出結果を出力 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 GoTo END_PROC ERR_PROC: MsgBox mstrErrDescription END_PROC: 'レコードセットを閉じる Call CloseRecordSet(objRS) 'コネクションを閉じる Call CloseConnection(objCN) End Sub
データベース関連のコードはいつでも使えるようにスタンバイ
上記のデータベース関係のコード（コネクション確立、レコードセット取得、およびそれらの破棄）は一度用意しておけばいくらでも使い回しが出来ます。
使いたいときにコードを対象ブックの標準モジュールにコピペするでもいいですし、一連のコードをクラスにひとまとめにして、そのクラスをインポートすればいつでも使えるようにするでもいいと思います（クラス化については別機会に書きます）。
[2019/03/10追記]
エクセルデータベースにSQLを使ってデータを追加します！
[2018/12/19追記]
データベースで使われるレコードセット・オブジェクトを配列の代わりに使っちゃいます！
[2018/02/10追記]
データベース処理用のクラスについてはこちらもご参照ください。
もはや試食の域を超えている！藤堂プランニングのケーキは試食もすごいが美味しさも抜群！！
清澄白河にある藤堂プランニングに行ってきました。
少し前にはテレビなどのメディアで取り上げられていて結構な行列になっていたみたいですね。
正直言うとぼくは藤堂プランニングの存在を全く知りませんでした。
そんな中、ヨメが友達から聞きつけてきてきたわけです。
曰く、ホテルにケーキを卸しているケーキ工場で、美味しいのは当然ながら、店頭でこれでもかというくらい試食させてくれるそうな。
そりゃー行かないわけにはいかないですよね。
で、場所はコチラ。
最寄駅の清澄白河からは徒歩6分ほどの距離。清澄庭園にほど近く、寺院が立ち並ぶエリアの東端に位置します。それもかなりひっそりと。
当日、ぼくは自転車で藤堂プランニングに向かったのですが、通りから少し路地に入ることもあり、かつ全く目立たないところにあるので、軽く迷子になっちゃいました（汗
で、店頭の様子がコチラ。
これまた結構殺風景です。周りに住宅に溶け込んでいて、看板がなければ間違いなく見逃してしまいます。
看板の奥に少し進むと工場への出入り口があります。
ぼくが行ったときは丁度ほかのお客さんがいませんでした。藤堂プランニングでの作法が全く分からない中、いきなり工場出入り口に辿り着き、流石に工場に入っていくわけにもいかず、どこに進めばいいのか分からなくなってしまいました。
軽く右往左往し始めたところで、工場の奥から若い人が登場。
そしていきなりティッシュをシュっと一枚手渡され「何にします？」のひとこと。
ぽっかーんです。
いきなりティッシュ差し出して何を言うんだこの人は状態てす。
ウワサに聞く試食の大盤振る舞いだと気づくのに数秒要しましたよ。
そこからは試食を堪能です。
ヨメと子どもの3人で行ったので、各々3個、都合9個のケーキを試食。
感動巻き起こる系の美味しさ、とまでは行かないかな。
例えるなら、ブュッフェで出てくるケーキとかスーパーとかで売られてるケーキくらいの美味しさです。
藤堂プランニングの醍醐味は、試食でここまで食べていいの！？という驚き、ということに尽きますね。
こういう驚きって不思議ですね。
記憶に鮮明に残っていて、このエントリーを書いてるうちにまた行きたくなってきました（汗
