ExcelでSQLを使う

エクセルでSQLを使う必須の関数を紹介しします。

ExcelでSQLを使う-012: SQL WHERE節の活用-1 関係演算子とAND、OR

 6月でも北海道は寒い。なにしろ、ストーブで、”しるこドリンク”を温めて飲む漆原教授の姿も見られる日もあるのである。研究室に秘書はいないし、想像を絶する教授の奇行に、学生たちも患畜たちも大弱り・・・。向かうところ敵なしの彼に挑むのは、乱暴者ヒヨちゃんか、おしゃべりな畜主か、はたまた昔の恩師なのか?そんな環境のなかで、5年生になっても吹き矢作りにいそしむハムテルに、突然与えられた使命は、漆原教授に代わって獣医学会で研究発表することだったー。(同書帯より)

◇◇◇◇◇

 本稿からは、[イミディエイトウインドウ]でのテストに加えて、シートの式にSQLツール関数を書いた運用例も示していきます。

WHERE 節の高度な活用-1

 WHERE 節では検索条件を指定します。SELECT文のなかでも重要な節です。

関係演算子

 

 

 

 

 

 

 WHERE 節には、次の関係演算子を使って、列と定数との比較条件を記述することができます。

    --------------------------------------------------
      記号        意味
    --------------------------------------------------
       =          等しい
       <>        等しくない
       !=         等しくない
       >          大きい
       >=        大きいか、もしくは等しい
       <          小さい
       <=        小さいか、もしくは等しい
    ---------------------------------------------------

  例えば、関係演算子を使って平均販売単価を算出する商品区分を指定することだできます。

 

 SELECT AVG(販売単価) FROM [商品台帳明細$A1:H1000] WHERE 区分=1

 

? DLookup("SELECT AVG(販売単価) FROM [商品台帳明細$A1:H1000] WHERE 区分=1")

1756.6667

複数の検索条件の設定: AND と OR

<売上伝票>

 

 

 

 

 

 

 

 

 

 

 

 

 

 
  WHERE節に複数の検索条件を設定する時は、検索条件をキーワード AND もしくは OR で結合します。

 

    SELECT * FROM 売上伝票 WHERE 顧客名='佐野 順次' AND 売上日 >= #2019/03/05#

 

 上記の例は、<顧客名>が '佐野 順次'さんで<売上日>が 2019年03月05日以後の<売上伝票>を選択します。下記の例は、同じ検索を DSelect関数で行っています。

 

? DSelect("SELECT * FROM [売上伝票$A1:E100] 

                                     WHERE 

                                     顧客名='佐野 順次' 

                                     AND 

                                     FormatDateTime(売上日) >= #2019/03/05#")

7;20190007;2019/03/07;7;佐野 順次

 

《売上伝票検索》

 DSelect関数を利用すれば、左図のような<売上伝票>を参照するシートを簡単に制作することができます。

 

列C07の式=DSelect(SQL文)


 列C07の式には、イミディエイトウインドウで確認したDSelect(SQL文)を書いています。ただし、'佐野 順次' の部分は、E05と置き換えています。また、#2019/03/05#もE06と置き換えています。


 ちょっと、面倒な置き換え作業ですが、一度置き換えてしまえば、次からはユーザーがE05とE06を入力すれば、<売上伝票>の参照情報は自動更新されます。しかし、言うは易く行うは難しの、この置き換えです。まあ、そこを行うのが、エクセルシステム開発者の意地と根性の見せ所。コツは、3つのステップで置き換えることです。


=DSelect("SELECT * FROM [売上伝票$A1:E100]

    WHERE 顧客名='佐野 順次'
    AND
    FormatDateTime(売上日) > #2019/03/05#")

 ↓
=DSelect("SELECT * FROM [売上伝票$A1:E100]
    WHERE 顧客名='" & XXXXX & "'
    AND
    FormatDateTime(売上日) > #" & Text(XXXXX ,"yyyy/mm/dd")& "#")

 ↓ 

=DSelect("SELECT * FROM [売上伝票$A1:E100]
    WHERE 顧客名='" & E5 & "'
    AND
    FormatDateTime(売上日) > #" & TEXT(E6,"yyyy/mm/dd") & "#")

 

 DSelect関数は、列区切り子と行区切り子を引数で指定いないと”;”と”|”を採用します。ですから1行目のデータは、CutStr関数で容易に取り出せます。

 

? DSelect("SELECT * FROM [売上伝票$A1:E100] WHERE ID<3")

1;20190001;2019/03/01;1;鈴木 一郎|2;20190002;2019/03/02;2;中村 主水

 

? CutStr(DSelect("SELECT * FROM [売上伝票$A1:E100] WHERE ID<3"), "|",1)

1;20190001;2019/03/01;1;鈴木 一郎

 

? CutStr(DSelect("SELECT * FROM [売上伝票$A1:E100] WHERE ID<3"), "|",2)

2;20190002;2019/03/02;2;中村 主水

 

 それぞれの列データを取り出すには、CutStr関数をネストで書きます。

 

列E08の式=CutStr(CutStr(C7,"|",1),";",1)
列E09の式=CutStr(CutStr(C7,"|",1),";",2)
列E10の式=CutStr(CutStr(C7,"|",1),";",3)
列E11の式=CutStr(CutStr(C7,"|",1),";",4)
列E12の式=CutStr(CutStr(C7,"|",1),";",5)

 

Public Function CutStr(ByVal Text As String, _

                                     ByVal Separator As String, _
                                     ByVal N As Integer) As String
    Dim strDatas() As String
   
    If N > 0 Then
        strDatas = Split("" & Separator & Text, Separator, , 0)
        CutStr = strDatas(N * Abs(N <= UBound(strDatas)))
    End If
End Function

日付をStr関数で扱う

? DSelect("SELECT * FROM [売上伝票$A1:E100] WHERE 顧客名='佐野 順次' AND Str(売上日)>'2019/03/05'")

7;20190007;2019/03/07;7;佐野 順次

 

=DSelect("SELECT * FROM [売上伝票$A1:E100]

     WHERE 顧客名='" & E5 & "'

     AND
     Str(売上日) > '" & E6 "'")


と、日付をStr関数で扱うこともできます。


 テストは、十分とは言えませんが、(現時点では)日付型と時刻型を文字列として扱う簡便法での検索ミスは起きていません。Str関数の利用は、一つの有力な選択肢かもしれません。

 

 以上が、上図の《売上伝票検索》の仕組みの全てです。

 

◇◇◇◇◇

 

 ハスキー流「SQLリファレンス」の「第3章2項:WHERE節の高度な活用」は、


関係演算子
複数の検索条件の設定: AND と OR
範囲: BETWEEN と AND
集合: IN
パターン照合: LIKE


の5つの小見出しを持ちます。範囲、集合、パターン照合については、稿を改めて紹介します。

 

 VBAでのプログラミングゼロでSQL言語を使うってことは、SQLツールを開発することも課題ですが、その活用ノウハウを確立することも重要な課題です。今暫く、その確立を目指すハスキー流「エクセルでSQLを活用するためのリファレンス」にお付き合いください。

 

🔶 🔶 🔶

 

 それにしても、split関数の登場は、CutStr関数の書き方を一変させました。その昔は、次のように書いたものです。自分で書いたにも関わらず、目で見ただけでそのアルゴリズムを理解することは不可能です。split関数の登場は、一種の革命でした。

 

 Public Function CutStr(ByVal Text As String, _

                                        ByVal Separator As String, _
                                        ByVal N As Integer) As String
     Dim I As Integer
     Dim J As Integer
     Dim K AS Integer
     Dim L AS Integer
    
     Text = Text & Separator
     L = Len(Separator) - 1
     For I = 1 To N
         J = K + 1
         K = Instr(J. Text, Separator, vbTextCompare) + L
     Next I
     IF K > J Then
         CutStr = Mid$(Text, J, K - J - L)
     End If
 End Function