Ta-kaku.jp Tools
Update 2010/02/06
技術メモ内容
トップページ
ハードやソフトの技術メモです。 ほとんど、個人の備忘録のレベルですので高度なことは書かれていません。 皆様に少しでもお役に立てれば幸いです。
MENU
EXCEL
サイト更新情報
2008/10/18
「Ta-kaku Tools 住所検索・郵便番号検索・事業所名検索・駅検索・電話局番検索」を Ver2.202 にリビジョンアップしました。
更新内容は以下の通りです。
・「xxx1丁目」の様に「全角の数字」と「丁目」が続いている文字列が入力されると検索できない事がある不具合を改修しました。
2008/10/01
Ta-kaku Tools Whois検索 Ver1.000をリリースしました。
2008/09/30
「Ta-kaku Tools 住所検索・郵便番号検索・駅検索・電話番号(局番)検索 」を Ver2.201 にリビジョンアップしました。
更新内容は以下の通りです。
・電話番号(局番)検索機能を正式版にしました。 ・無効な数値に対して住所の検索を行っていた処理を改善しました。 ・局番0120指定でGoogleMapがおかしな場所を示していた不具合を修正しました。 ・「東京駅駅」のように入力されると、関係の無いデータが出力される不具合を修正しました。
過去の情報
関連キーワード
技術メモ インデックス
カテゴリーを選択してください。
インストール
プログラミング言語
マークアップ言語
データベース言語
スクリプト言語
MS-Office
その他
Webアプリケーション
の開発方法
EXCEL 技術メモ
追加日時の新しい方から掲載しています。
データベース関数
登録日:2008/05/09   分類:データ管理
EXCELは、標準で多くの機能を備えています。
その中でも、ワークシート関数はとても汎用性があり重宝します。
ほとんどの関数は、直感的に使用方法を理解する事ができますが、
データベース関数は若干理解し辛い面があるように思います。
ここでは、私がEXCELのデータベース関数を調査した時のメモを掲載したいと思います。

前提として、簡単に説明しますと・・・
緑の部分はExcelのセル座標で、青の部分は、データの項目を現します。
データの項目は、「データベース部分」と「条件部分」で一致している
必要があります。

DCOUNT
データベース部分から条件に合うデータのデータ数を返します。

データベース部分
セル B C D E
5 DATA1 DATA2 DATA3 DATA4
6 aaa 357 × NG
7 bbb 450  
8 ccc 894 OK
9 ddd 930 × OK

条件部分
セル B C D E
12 DATA1 DATA2 DATA3 DATA4
13   >400 ="○"  

計算部分
計算式 計算結果 解説番号
=DCOUNT(B5:E9,C5,C12:C13) 3 ?
=DCOUNT(B5:E9,C5,C12:D13) 2 ?

解説
?DATA2 の条件に該当するデータ数です。
?DATA2とDATA3 の条件に該当するデータ数です。

DCOUNTA
データベース部分から条件に合うデータの空白で無いデータ数を返します。

データベース部分
セル B C D E
5 DATA1 DATA2 DATA3 DATA4
6 aaa 357 × NG
7 bbb 450  
8 ccc 894 OK
9 ddd 930 × OK

条件部分
セル B C D E
12 DATA1 DATA2 DATA3 DATA4
13   >400 ="○"  

計算部分
計算式 計算結果 解説番号
=DCOUNTA(B5:E9,E5,C12:C13) 2 ?
=DCOUNTA(B5:E9,E5,C12:D13) 1 ?

解説
?DATA2 の条件に該当する DATA4 の空白でないデータ数です。
?DATA2とDATA3 の条件に該当する DATA4 の空白でないデータ数です。

DSUM
データベース部分から条件に合うデータの合計を返します。

データベース部分
セル B C D E
5 DATA1 DATA2 DATA3 DATA4
6 aaa 357 × NG
7 bbb 450 OK
8 ccc 894 NG
9 ddd 930 OK

条件部分
セル B C D E
12 DATA1 DATA2 DATA3 DATA4
13   >400 ="○" OK

計算部分
計算式 計算結果 解説番号
=DSUM(B5:E9,C5,C12:C13) 2 ?
=DSUM(B5:E9,C5,D12:E13) 1 ?

解説
?DATA2 の条件に該当するデータの合計値です。
?DATA3 と DATA4 の条件に該当するデータの合計値です。

DPRODUCT
データベース部分から条件に合うデータの積(掛算の結果)を返します。

データベース部分
セル B C D E
5 DATA1 DATA2 DATA3 DATA4
6 aaa 357 × NG
7 bbb 450  
8 ccc 894 OK
9 ddd 930 × OK

条件部分
セル B C D E
12 DATA1 DATA2 DATA3 DATA4
13   >400 ="○" OK

計算部分
計算式 計算結果 解説番号
=DPRODUCT(B5:E9,C5,C12:C13) 374139000 ?
=DPRODUCT(B5:E9,C5,C12:D13) 402300 ?

解説
?DATA2 の条件に該当するデータを掛け合わせた値です。
?DATA2 と DATA3 の条件に該当するデータを掛け合わせた値です。

DAVERAGE
データベース部分から条件に合うデータの平均値を返します。

データベース部分
セル B C D E
5 DATA1 DATA2 DATA3 DATA4
6 aaa 357 × NG
7 bbb 450  
8 ccc 894 OK
9 ddd 930 × OK

条件部分
セル B C D E
12 DATA1 DATA2 DATA3 DATA4
13   >400 ="○"  

計算部分
計算式 計算結果 解説番号
=DAVERAGE(B5:E9,C5,C12:C13) 758 ?
=DAVERAGE(B5:E9,C5,C12:D13) 672 ?

解説
?DATA2 の条件に該当するデータの平均値です。
?DATA2とDATA3 の条件に該当するデータの平均値です。

DGET
データベース部分から条件に合うデータを1つ返します。

データベース部分
セル B C D E
5 DATA1 DATA2 DATA3 DATA4
6 aaa 357 × NG
7 bbb 450  
8 ccc 894 OK
9 ddd 930 × OK

条件部分
セル B C D E
12 DATA1 DATA2 DATA3 DATA4
13   >400 ="○" OK

計算部分
計算式 計算結果 解説番号
=DGET(B5:E9,B5,C12:C13) #NUM! ?
=DGET(B5:E9,B5,C12:E13) ccc ?

解説
?DATA2 の条件に該当する DATA1 のデータを1件返します。
   (複数存在しているので表示できません)
?DATA2 と DATA3 と DATA4 の条件に該当する DATA1 のデータを1件返します。

DMAX
データベース部分から条件に合うデータの最大値を返します。

データベース部分
セル B C D E
5 DATA1 DATA2 DATA3 DATA4
6 aaa 357 × NG
7 bbb 450  
8 ccc 894 OK
9 ddd 930 × OK

条件部分
セル B C D E
12 DATA1 DATA2 DATA3 DATA4
13   >400 ="○" OK

計算部分
計算式 計算結果 解説番号
=DMAX(B5:E9,C5,C12:C13) 930 ?
=DMAX(B5:E9,C5,C12:D13) 894 ?

解説
?DATA2 の条件に該当するデータの最も大きな値です。
?DATA2 と DATA3 の条件に該当するデータの最も大きな値です。

DMIN
データベース部分から条件に合うデータの最小値を返します。

データベース部分
セル B C D E
5 DATA1 DATA2 DATA3 DATA4
6 aaa 357 × NG
7 bbb 450  
8 ccc 894 OK
9 ddd 930 × OK

条件部分
セル B C D E
12 DATA1 DATA2 DATA3 DATA4
13   >400 ="○" OK

計算部分
計算式 計算結果 解説番号
=DMIN(B5:E9,C5,C12:C13) 450 ?
=DMIN(B5:E9,C5,C12:D13) 450 ?

解説
?DATA2 の条件に該当するデータの最も小さな値です。
?DATA2 と DATA3 の条件に該当するデータの最も小さな値です。

DSTDEV
データベース部分から条件に合うデータの標準偏差(データの分布状態)を返します。
この関数は、指定した条件を満たすレコードを標本と見なします。
条件が空欄の場合は、全てが対象となります。

データベース部分
セル B C D E
7 DATA1 DATA2 DATA3 DATA4
8 aaa 35 14 68
9 bbb 73 56 36
10 ccc 56 32 74
11 ddd 12 43 23
12 eee 98 82 22
13 fff 34 11 1
14 ggg 5 79 67
15 hhh 68 90 81

条件部分
セル B C D E
18 DATA1 DATA2 DATA3 DATA4
19     >20 >50

計算部分
計算式 計算結果 解説番号
=INT(DSTDEV(B7:E15,C7,C18:C19)) 31 ?
=INT(DSTDEV(B7:E15,D7,D18:D19)) 23 ?

解説
?DATA2 全体が標本された DATA2 の標準偏差です。
?DATA3 の条件に該当する標本の DATA3 の標準偏差です。

DSTDEVP
データベース部分から条件に合うデータを「対象とするデータ」と見なして
標準偏差(データの分布状態)を返します。
この関数は、「対象とするデータ」を全て標本と見なします。

データベース部分
セル B C D E
7 DATA1 DATA2 DATA3 DATA4
8 aaa 35 14 68
9 bbb 73 56 36
10 ccc 56 32 74
11 ddd 12 43 23
12 eee 98 82 22
13 fff 34 11 1
14 ggg 5 79 67
15 hhh 68 90 81

条件部分
セル B C D E
18 DATA1 DATA2 DATA3 DATA4
19     >20 >50

計算部分
計算式 計算結果 解説番号
=INT(DSTDEVP(B6:E14,C6,C18:C19)) 29 ?
=INT(DSTDEVP(B6:E14,D6,D18:D19)) 21 ?

解説
?DATA2 全体が「対象とするデータ」且つ標本と見なした
   DATA2 の標準偏差です。
?DATA3 の条件に該当するデータを「対象とするデータ」
   且つ標本と見なした DATA3 の標準偏差です。

DVAR
データベース部分から条件に合うデータの標本分散(データのばらつき範囲)を返します。
この関数は、指定した条件を満たすレコードを標本と見なします。
条件が空欄の場合は、全てが対象となります。

データベース部分
セル B C D E
7 DATA1 DATA2 DATA3 DATA4
8 aaa 35 14 68
9 bbb 73 56 36
10 ccc 56 32 74
11 ddd 12 43 23
12 eee 98 82 22
13 fff 34 11 1
14 ggg 5 79 67
15 hhh 68 90 81

条件部分
セル B C D E
18 DATA1 DATA2 DATA3 DATA4
19     >20 >50

計算部分
計算式 計算結果 解説番号
=INT(DVAR(B7:E15,C7,C18:C19)) 1013 ?
=INT(DVAR(B7:E15,D7,D18:D19)) 550 ?

解説
?DATA2 全体が標本された DATA2 の標本分散です。
?DATA3 の条件に該当する標本の DATA3 の標本分散です。

DVARP
データベース部分から条件に合うデータを「対象とするデータ」と見なして
標本分散(データのばらつき範囲)を返します。
この関数は、「対象とするデータ」を全て標本と見なします。

データベース部分
セル B C D E
7 DATA1 DATA2 DATA3 DATA4
8 aaa 35 14 68
9 bbb 73 56 36
10 ccc 56 32 74
11 ddd 12 43 23
12 eee 98 82 22
13 fff 34 11 1
14 ggg 5 79 67
15 hhh 68 90 81

条件部分
セル B C D E
18 DATA1 DATA2 DATA3 DATA4
19     >20 >50

計算部分
計算式 計算結果 解説番号
=INT(DVARP(B6:E14,C6,C18:C19)) 887 ?
=INT(DVARP(B6:E14,D6,D18:D19)) 458 ?

解説
?DATA2 全体が「対象とするデータ」且つ標本と見なした
   DATA2 の標本分散です。
?DATA3 の条件に該当するデータを「対象とするデータ」
   且つ標本と見なした DATA3 の標本分散です。

GETPIVOTDATA
ピボットテーブルのデータを返します。

データベース部分
セル B C D E
7 DATA1 DATA2 DATA3 DATA4
8 aaa 35 14 68
9 bbb 73 56 36
10 ccc 56 32 74
11 ddd 12 43 23
12 eee 98 82 22
13 fff 34 11 1
14 ggg 5 79 67
15 hhh 68 90 81

ピボットテーブルを作成します。
ここでは、ピボットテーブルの解説がメインではありませんので、
簡単に箇条書きします。

(1)[データ]-[ピボットテーブルとピボットグラフ レポート]で、
   「ピボットテーブル/ピボットグラフウィザード - 1/3」を開き、
   [Excelのリスト/データベース(M)]をONにします。
(2)[ピボットテーブル(T)]をONにして、[次へ]をクリックします。
   「ピボットテーブル/ピボットグラフウィザード - 2/3」の範囲で、
   データベース部分を範囲選択した後、[完了]ボタンをクリックします。
(3)すると、ピボットテーブルが作成されます。
   (TESTの部分はダブルクリックして[ピボットテーブル フィールド]で
   変更したイメージです。

ピボットテーブル
TEST
DATA1
aaa 35
bbb 73
ccc 56
ddd 12
eee 98
fff 34
ggg 5
hhh 68
総計 381

計算部分
計算式 計算結果 解説番号
=GETPIVOTDATA(B47,"aaa") 35 ?
=GETPIVOTDATA(B48,"総計") 381 ?

解説
?ピボットテーブルから "aaa" のデータを抜き出して返します。
?ピボットテーブルから "総計" のデータを抜き出して返します。
簡単な差分比較マクロ
登録日:2007/05/08   分類:データ管理
簡単な差分比較マクロ
似たようなデータが2つある場合、その差分がどこにあるか確かめたくなる事があります。
EXCELを使えば簡単に差分を明示することが可能です。

手順
    1.EXCELのブックを1つ用意して、3つのシートにそれぞれ以下のような名前を付けます。

          1つ目のシート ... "比較マクロ"
          2つ目のシート ... "データ1"
          3つ目のシート ... "データ2"

    2.1つ目シートにコマンドボタンを配置します。
      メニューの [表示]-[ツールバー]-[コントロール ツールボックス] にあります。

    3.配置したコマンドボタンをダブルクリックするとプログラムを編集できるエディター
      が立ち上がり、以下のようにスケルトンが用意されます。

        Private Sub CommandButton1_Click()
        End Sub

    4.このコードを以下のコードと置き換えてください。

        Private Sub CommandButton1_Click()
            Dim rowCnt As Long
            Dim colCnt As Long

            rowCnt = 0
            Do
                rowCnt = rowCnt + 1
                If Worksheets("比較マクロ").Cells(2, 1) < rowCnt Then
                    Exit Do
                End If

                colCnt = 0
                Do
                    colCnt = colCnt + 1
                    If Worksheets("比較マクロ").Cells(1, 1) < colCnt Then
                        Exit Do
                    End If

                If Worksheets("データ2").Cells(rowCnt, colCnt) <> _
                Worksheets("データ1").Cells(rowCnt, colCnt) Then
                    Worksheets("データ1").Cells(rowCnt, colCnt).Interior.Color _
                    = RGB(255, 255, 0)
                End If

                Loop
            Loop

            MsgBox "END"

        End Sub

    5.「データ1」シートと「データ2」シートに比較したいデータを貼り付けます。
    6.「比較マクロ」シートの 1行目の1列目 に比較する最大列を数字で入力します。
    7.「比較マクロ」シートの 2行目の1列目 に比較する最大行を数字で入力します。
    8.ファイルをセーブして再度読み込んでください。
    9.「比較マクロ」シートのコマンドボタンをクリックすると、「データ1」シートに
      差分が黄色表示されます。
PR