VBA 一意なリストの高速作成

一意なリストとは、列に重複したレコードが存在した場合、重複を排除したレコードの一覧のこと。
SQLでいえば、distinct のことです。

Excelの画面操作では、以下の手順で作成（わかりやすく１列で操作）

１）重複したレコードを含む範囲を選択
２）データ　→　フィルタ　→　フィルタオプションの設定
３）"重複するレコードは無視する"にチェックを入れる
４）OK　ボタンをクリック

Excelのフィルタ機能で、リストが一意になるように、重複したレコードは非表示になります。
しかし、大量データでは遅いのと、あくまで非表示にしているだけなので、使い勝手が悪いです。
そこで、VBAで一意なリストを作成します。


＜前提＞
重複したリスト Sheet1 A列にA1から格納されている
一意なリストはSheet2のA列に作成する

Dim myDic As Object, myKey As Variant
Dim c As Variant, varData As Variant

Set myDic = CreateObject("Scripting.Dictionary")
'                                ↓重複したリストのシート名
With Worksheets("Sheet1")
'                   ↓重複したリストの範囲
  varData = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value
End With

For Each c In varData
  If Not c = Empty Then
    If Not myDic.Exists(c) Then
      myDic.Add c, Null
    End If
  End If
Next

myKey = myDic.Keys
'                                  ↓一意なリストを作成するシート名
With Worksheets("Sheet2")
'                ↓A列をクリア
  .Range("A:A").ClearContents
'                ↓一意なリストを作成
  .Range("A1").Resize(myDic.Count) = Application.WorksheetFunction.Transpose(myKey)
End With

Set myDic = Nothing

Netezza(ネティーザ) Oracleでいうところの dual表

OracleでダミーのSQLを実行する時によく使う dual テーブルがあります。

当然、Netezzaには dual テーブルはありませんが、ダミーのSQLを実行する方法はあります。

・Oracle　の場合
select to_char(sysdate,'YYYY-MM-DD') from dual;

・Netezza　の場合
select to_char(now(),'YYYY/MM/DD') ;

from 句を省略することで、同じ動きをします。

VBA 必要な行にだけ関数を設定

何行データがあるかわからないシートに、データのある行にだけ、関数をセットしたいことはよくある。

簡単なのはあらかじめ適用な行数分、関数をセットしておくことですが、ちゃんと必要な行数分関数がセットされているか確認しないと漏れてしまう。

それをマクロで自動設定する。

'最終行を取得
checkcol = 4
lastrow = Cells(Rows.Count , checkcol ).End(xlUp).row

'先頭行のセルに関数をセット
startrow = 3
setcol = 3
Cells(startrow,setcol).Value = "row() - " & startcol + 1

'関数を必要な行数分コピー
Cells(startrow,setcol).Copy
Range(Cells(startrow,setcol),Cells(lastrow,setcol).Select
ActiveSheet.Paste

VBA 最後の行取得とループ処理

値の入っている表に対して、値の入っている一番最後の行を取得して処理を行う。

・値の入っている最後の行 
例）B列に値が設定されている一番最後の行番号
'値が入っていることを評価する列番号 ２＝B列
col = 2 
lastrow = Cells(Rows.Count , col ).End(xlUp).row

・最後の行までループ処理
例）３行目からD列に値が設定されている一番最後の行までループして、A列に番号をセットする

startrow = 3
checkcol = 4
rownum = 1
lastrow = Cells(Rows.Count , checkcol ).End(xlUp).row

for row = startrow to lastrow
    Cells(row , 1).Value = rownum
    rownum = rownum + 1

Next row

Netezza(ネティーザ) 項目一覧

IBMのDB、Netezzaで、Oracleの user_tab_columns のように、テーブル一覧を取得する。

・_V_RELATION_COLUMN
OWNER　：　オーナー
NAME　：　テーブル名
ATTNUM　：　項目番号
ATTNAME　：　項目名
FORMAT_TYPE　：　項目型（桁、小数桁）
ATTCOLLENG　：　項目長
ATTNOTNULL　：　Null（'t'=NotNull / 'f'=Null）

・_V_DOTNET_PRIMARYKEYS1
TABLE_SCHEMA　：　テーブルスキーマ
TABLE_NAME　：　テーブル名
COLUMN_NAME　：　項目名
KEY_SEQ　：　プライマリーキー項目順
PK_NAME 　：　プライマリーキー名

＊　プライマリーキーの情報がどこに格納されているか不明だったが、とりあえずデータを見つけられたので採用。他環境でも同様に取得できるかは不明。

select
A.OWNER
,A.NAME
,A.ATTNUM
,A.ATTNAME
,A.FORMAT_TYPE
,A.ATTCOLLENG
,A.ATTNOTNULL
,B.KEY_SEQ
 from _V_RELATION_COLUMN A
left outer join _V_DOTNET_PRIMARYKEYS1 B
on A.NAME = B.TABLE_NAME
and A.ATTNAME = B.COLUMN_NAME
;

このままでは、桁と小数桁が項目型に混じってしまっているため切りだす。

select
A.OWNER
,A.NAME
,A.ATTNUM
,A.ATTNAME
,case
when position('(' in A.FORMAT_TYPE) != 0 
  then substr(A.FORMAT_TYPE,1,position('(' in A.FORMAT_TYPE)-1)
 else A.FORMAT_TYPE
end as ATTFORMAT  
,case 
when substr(A.FORMAT_TYPE,1,4) = 'NUME'
  then to_number(translate(substr(A.FORMAT_TYPE,9,2),',',''),'99')
else A.ATTCOLLENG
end as ATTLENGTH
,case 
when substr(A.FORMAT_TYPE,1,4) = 'NUME'
  then to_number(substr(A.FORMAT_TYPE,position(',' in A.FORMAT_TYPE) + 1 ,1),'9')
else 0
end as ATTSCALE
,case
when A.ATTNOTNULL = 't' then 'NotNull'
else 'Null'
end as ATTNULL
,B.KEY_SEQ
 from _V_RELATION_COLUMN A
left outer join _V_DOTNET_PRIMARYKEYS1 B
on A.NAME = B.TABLE_NAME
and A.ATTNAME = B.COLUMN_NAME
;

Netezza(ネティーザ) テーブル一覧

IBMのDB、Netezzaで、Oracleの user_tables のように、テーブル一覧を取得する。

・_v_table
OWNER　：　オーナー
TABLENAME　：　テーブル名
CREATEDATE　：　作成日時

select
OWNER
,TABLENAME
,to_char(cast(CREATEDATE as timestamp),'YYYY/MM/DD')
from
_v_table
;

補足）
・Createされた日しか取得できません。Alterされた日はもっていませんでした。
・Createされた日をCREATEDATEに持っていますが、この項目はABSTIMEという特殊なタイムスタンプ型の項目で、to_char関数が使えません。
一度、cast関数で"TIMESTAMP"型に変換する必要があります。

VBA 基本編

・シートの選択
Sheets(“シート名”).Select

・１つのセルの選択
Sheets(“シート名”).Range(“A2”).Select
Sheets(“シート名”).Cells(2,1).Select
↑　２つの指定方法は同じセルを選択している

・Cellsの使い方
Cells(行数 , 列数)

・１つのセルを選択する場合のRangeとCellsの使い分け
Range は、excelのシート上で、指定するセルの場所がわかっている場合、使いやすいCellsは、行や列のループ処理や計算式でセルの位置を指定する場合に使いやすい

・Cellsを使う時の行数確認方法
Excelは通常、列は行と区別するため、アルファベット表示で使っているが、列の表示を数字に変更する事ができる。


・古いExcel
オプション　＞　"全般"タブ　＞　"R1C1参照形式を使用する"
　チェックなし(デフォルト)　：　A1形式で表示
　チェックあり　　　　　　　：　11形式で表示


 ・新しいExcel

チェックをつけた状態だと、セルの参照方法が、相対参照になってしまうので、確認が終わったら、チェックを外すことをおすすめします。