Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
11674 / 13646 ﾂﾘｰ ←次へ　｜　前へ→
▼ 【14596】データベースイメージ（SQL) レッサーパンダ 04/6/2(水) 17:16 質問[未読]
【14601】Re:データベースイメージ（SQL) ichinose 04/6/2(水) 18:26 回答[未読]
【14621】Re:データベースイメージ（SQL) レッサーパンダ 04/6/3(木) 10:21 お礼[未読]
【14622】Re:データベースイメージ（SQL) Ｊカーター 04/6/3(木) 10:34 回答[未読]
【14642】Re:データベースイメージ（SQL) ichinose 04/6/3(木) 18:52 発言[未読]
【14798】Re:データベースイメージ（SQL) レッサーパンダ 04/6/8(火) 10:04 お礼[未読]
【14853】Re:データベースイメージ（SQL) ichinose 04/6/8(火) 21:18 発言[未読]
【14596】データベースイメージ（SQL)
質問  レッサーパンダ  - 04/6/2(水) 17:16 -
引用なし
パスワード
   こんにちは。

１つのExcelのなかで条件入力・結果シートと
データシートがあります。
この場合、どうすればデータのシートをDBとして扱えますか？
データの量が多いので、SQLとかでCOUNTやSUMが使えれば便利なのですが、
VBAの命令や記述方法が全然わかりません・・。

ご存知の方教えてください。
よろしくお願いします。

例）
　結果表示シート（Sheet1）：条件入力（年齢、性別）と
　　　　　　　　　　　　　　結果表示（人数、所持金）
　データのシート（Sheet2）：１行目に項目名。２行目以降のA列からD列がデータ部
　　　　　　　　　　　　　　年齢　　性別　　出身地　所持金
　　　　　　　　　　　　　　　25　　　男　　大阪　　　10
　　　　　　　　　　　　　　　21　　　男　　東京　　　50
　　　　　　　　　　　　　　　23　　　女　　大阪　　　30
　　　　　　　　　　　　　　　25　　　男　　東京　　 100
　　　　　　　　　　　　　　　21　　　女　　大阪　　　 5
処理）
　年齢＝（条件無し）、性別＝男・・・結果　3人　160円
　年齢＝21　　　　 、性別＝男・・・結果　1人　 50円
【14601】Re:データベースイメージ（SQL)
回答  ichinose  - 04/6/2(水) 18:26 -
引用なし
パスワード
   ▼レッサーパンダ さん：
こんにちは。

>１つのExcelのなかで条件入力・結果シートと
>データシートがあります。
>この場合、どうすればデータのシートをDBとして扱えますか？
>データの量が多いので、SQLとかでCOUNTやSUMが使えれば便利なのですが、
>VBAの命令や記述方法が全然わかりません・・。

例題、拝見しました。
Excelでこの結果を出すのにSqlもVBAも要りませんよ！！。


>例）
>　結果表示シート（Sheet1）：条件入力（年齢、性別）と
>　　　　　　　　　　　　　　結果表示（人数、所持金）
>　データのシート（Sheet2）：１行目に項目名。２行目以降のA列からD列がデータ部
>　　　　　　　　　　　　　　年齢　　性別　　出身地　所持金
>　　　　　　　　　　　　　　　25　　　男　　大阪　　　10
>　　　　　　　　　　　　　　　21　　　男　　東京　　　50
>　　　　　　　　　　　　　　　23　　　女　　大阪　　　30
>　　　　　　　　　　　　　　　25　　　男　　東京　　 100
>　　　　　　　　　　　　　　　21　　　女　　大阪　　　 5

Sheet2の内容がこのとおりだとすると、
データ記述セル範囲は、項目名も含めるとセルＡ１〜Ｄ６と言う事になりますよね？

次にSheet1です。
セルＡ１に「年齢」、Ｂ１に「性別」と条件設定したい項目名をSheet2の項目名と
同一の名前を記述して下さい。

次にセルＡ３に｢結果」と入力して下さい。
ここで、セルＡ４に人数、セルＢ４に所持金合計を求める数式を以下に示します。

セルＡ４（人数）

　=DCOUNTA(Sheet2!A1:D6,"年齢",A1:B2)

セルＢ４（所持金合計)

　=DSUM(Sheet2!A1:D6,"所持金",A1:B2)


これで、設定終了です。

Sheet1のセルＡ２に年齢条件、セルＢ２に性別条件を入力して
セルＡ４、Ｂ４に正しい結果が表示されるか　確認してみて下さい。

例．

セルＡ２----21　　Ｂ２-----男

セルＡ２----未入力（条件無し）、Ｂ２----男

詳しい設定説明は、DCOUNTやDSUMワークシート関数のHELPを
参照して下さい。
【14621】Re:データベースイメージ（SQL)
お礼  レッサーパンダ  - 04/6/3(木) 10:21 -
引用なし
パスワード
   ichinose さんへ

ありがとうございました。

実は質問の内容があまりよくなかったみたいです・・。

DCOUNTとDSUMを知らなかったのは事実ですが、
本当は結果より過程を知りたかったんです。
SQLやVBAを使用して結果を求められるのかを知りたかったんです。
確かにあの質問の内容からだと、
SQLやVBAを使用しなくてもDCOUNTとDSUMで出来てしまいますね。

DCOUNTとDSUMはHELPを見て勉強します。
【14622】Re:データベースイメージ（SQL)
回答  Ｊカーター  - 04/6/3(木) 10:34 -
引用なし
パスワード
   おじゃまします。
一応こんな方法もあります。
-------------------------------------------------------------------------
　　Dim rs As DAO.Recordset
　　With DBEngine.OpenDatabase(ThisWorkbook.FullName, 0, 0, "Excel 8.0;")
　　　　Set rs = .OpenRecordset("SELECT COUNT(年齢), SUM(所持金) " & _
　　　　　　　　　　　　　　"FROM [Sheet2$] WHERE 年齢 = 21 AND 性別 = '男';")
　　　　Sheet1.Cells(1).CopyFromRecordset rs
　　　　rs.Close
　　　　.Close
　　End With
　　Set rs = Nothing

DAOに参照設定しておいて下さい。

出来なかったらすいません。
【14642】Re:データベースイメージ（SQL)
発言  ichinose  - 04/6/3(木) 18:52 -
引用なし
パスワード
   ▼レッサーパンダ さん、Ｊカーターさん
こんばんは。


>実は質問の内容があまりよくなかったみたいです・・。
>
>DCOUNTとDSUMを知らなかったのは事実ですが、
>本当は結果より過程を知りたかったんです。
>SQLやVBAを使用して結果を求められるのかを知りたかったんです。
>確かにあの質問の内容からだと、
>SQLやVBAを使用しなくてもDCOUNTとDSUMで出来てしまいますね。

なるほど・・・。
すでにＪカーターさんからDAOでの回答が付いていますが・・・。
私は、ADOで別解です。

前回の関数のときのようなシートレイアウトだとして、
標準モジュールに
'==================================================================
Sub main()
　 Dim rs As Object
　 Dim sql As String
　 Dim cond() As String
　 Dim retcode As Long
　 If open_excel(ThisWorkbook.FullName) = 0 Then　'Excelに接続成功
　　　sql = "select count(*),sum(所持金) from [sheet2$] "
　　　idx = 0
　　　With Worksheets("sheet1")
　　　　 If .Range("a2").Value <> "" Then
　　　　　　ReDim Preserve cond(1 To idx + 1)
　　　　　　cond(idx + 1) = "年齢 = " & .Range("a2").Value
　　　　　　idx = idx + 1
　　　　　　End If
　　　　 If .Range("b2").Value <> "" Then
　　　　　　ReDim Preserve cond(1 To idx + 1)
　　　　　　cond(idx + 1) = "性別 = '" & .Range("b2").Value & "'"
　　　　　　idx = idx + 1
　　　　　　End If
　　　　 If idx > 0 Then
　　　　　　sql = sql & "where " & Join(cond(), " and ")
　　　　　　End If
'---------sqlの構文の決定
　　　　 Set rs = get_rs(sql, retcode) '人数と所持金合計の算出
　　　　 If retcode = 0 Then
　　　　　　.Range("a4").Value = rs.Fields(0).Value
　　　　　　.Range("b4").Value = rs.Fields(1).Value
　　　　　　rs.Close
　　　　　　Set rs = Nothing
　　　　 Else
　　　　　　MsgBox Error(retcode)
　　　　　　End If
　　　　 Call close_excel
　　　　 End With
　　　End If
End Sub

'別の標準モジュールに
'================================================================
Public cn As Object
'================================================================
Function open_excel(flnm) As Long
　 On Error Resume Next
　 Set cn = CreateObject("adodb.connection")
　 link_opt = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
　　　　　　　"Data Source=" & flnm & ";" & _
　　　　　　　"Extended Properties=Excel 8.0;"
　 cn.Open link_opt
　 open_excel = Err.Number
　 On Error GoTo 0
End Function
'==============================================================
Function close_excel()
　 On Error Resume Next
　 cn.Close
　 Set cn = Nothing
End Function
'==============================================================
Function get_rs(sql_str, retcode) As Object
　 On Error Resume Next
　 Set get_rs = Nothing
　 Set get_rs = cn.Execute(sql_str)
　 retcode = Err.Number
　 On Error GoTo 0
End Function


これで前回の関数のときのように
Sheet1のセルA2には、年齢条件、B2に性別条件を入力して、mainを
実行してみて下さい（未入力の場合は、条件なしとみなします）。
このコードは参照設定は不要ですが、私、普段はしています。
よかったら、確認してみて下さい。
【14798】Re:データベースイメージ（SQL)
お礼  レッサーパンダ  - 04/6/8(火) 10:04 -
引用なし
パスワード
   Ｊカーターさん、ichinoseさん

返事が遅くなりました。m(_ _)m
ありがとうございます。

やはり、ＤＡＯやＡＤＯは避けて通れないんですね。
同じEXCEL内のことだから、もっと単純に出来るのかと思ってました。

参考になりました。
【14853】Re:データベースイメージ（SQL)
発言  ichinose  - 04/6/8(火) 21:18 -
引用なし
パスワード
   ▼レッサーパンダ さん：
こんばんは。

>Ｊカーターさん、ichinoseさん
>
>返事が遅くなりました。m(_ _)m
>ありがとうございます。
>
>やはり、ＤＡＯやＡＤＯは避けて通れないんですね。
>同じEXCEL内のことだから、もっと単純に出来るのかと思ってました。
他にも、Querytableを使う方法もあります。
正直、私もほとんど（いや、まったく）使った事がないんですが、
シートレイアウトが最初のとおりだとして、Sheet1をアクティブにした状態で
'=====================================================================
Sub Macro1()
　　With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=Excel Files;DBQ=D:\My Documents\ＴＥＳＴエリア\doatest.xls;" _
　　　　　 & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;", Destination:=Range("a4"))
　　　　.CommandText = "SELECT count(*) as aaa,sum(所持金) as bbb FROM [Sheet2$] where [Sheet2$].年齢 = " & Sheet1.Range("a2").Value _
　　　　　　 & " and [Sheet2$].性別 = '" & Sheet1.Range("b2").Value & "';"
　　　　.Name = "query1"
　　　　.FieldNames = False
　　　　.AdjustColumnWidth = False
　　　　.RefreshStyle = xlOverwriteCells
　　　　.Refresh BackgroundQuery:=False
　　　　End With
End Sub

なんて方法でも可能みたいです（Sqlの組み立てはしていませんが）。
それにこれは、マクロの記録でコードが記述されますので、
それを訂正する事ができます（掲載コードは、マクロの記録で生成された
コードを訂正しただけです）。
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
11674 / 13646 ﾂﾘｰ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
2610219
(SS)C-BOARD v3.8 is Free