帖子: 219
主題: 24
精華: 0
積分: 243
點名: 0
作業系統: Windows10
軟體版本: Office2016
閱讀權限: 20
性別: 女
註冊時間: 2012-4-18
最後登錄: 2022-2-7

論壇榮譽勳章論壇贊助勳章

1^# 跳轉到 » 倒序看帖

打印

字體大小: tT

發表於 2013-11-14 19:57 | 只看該作者

比對兩個分頁資料,回饋欄位值,來源資料(可能)有兩筆以上狀況

如題!
比對兩個分頁資料,回饋欄位值,當來源資料有兩筆以上
應該如何寫?!

不知能用VBA完成?!還是只能用函數
請各位大大幫個忙
好讓我學習應用
謝謝!!

回饋欄位值.rar (22.71 KB)

樓主熱帖

stillfish00

大學生 Rank: 4

帖子: 1018
主題: 15
精華: 0
積分: 1058
點名: 0
作業系統: win7 32bit
軟體版本: Office 2016 64-bit
閱讀權限: 50
性別: 男
來自: 桃園
註冊時間: 2012-5-9
最後登錄: 2022-9-28

2^#

發表於 2013-11-15 15:28 | 只看該作者

本帖最後由 stillfish00 於 2013-11-15 15:30 編輯

回復 1# HSIEN6001
修改一下錄製Microsoft Query的巨集，Left Join結合兩表。

Sub Test()
On Error Resume Next
With Sheets("回饋數值")
   .Cells.ListObject.QueryTable.Delete
   .Cells.ClearContents
   With .ListObjects.Add(SourceType:=0, Source:=Array(Array( _
      "ODBC;DSN=Excel Files;DBQ=" & ThisWorkbook.FullName & ";DefaultDir=" & ThisWorkbook.Path & ";DriverId=1046;MaxB" _
      ), Array("ufferSize=2048;PageTimeout=5;")), Destination:=.Range("$A$1")). _
      QueryTable
      .CommandText = Array( _
      "SELECT `來源1$`.代號, `來源1$`.名稱, `來源1$`.日期, `來源1$`.漲跌, `來源1$`.收盤, `來源1$`.`成交(張)`, `來源1$`.當沖, `來源2$`.大股東名稱, `來源2$`.`異動(張)`, `來源2$`.上月持張, `來源2$`.本月持張" & Chr(13) & "" & Chr(10) & "FR" _
      , _
      "OM {oj `" & ThisWorkbook.FullName & "`.`來源1$` `來源1$` LEFT OUTER JOIN `" & ThisWorkbook.FullName & "`.`來源2$` `來源2$` ON `來源1$`.代號 = `來源2$`." _
      , "代號}")
      .RowNumbers = False
      .FillAdjacentFormulas = False
      .PreserveFormatting = True
      .RefreshOnFileOpen = False
      .BackgroundQuery = True
      .RefreshStyle = xlInsertDeleteCells
      .SavePassword = False
      .SaveData = True
      .AdjustColumnWidth = True
      .RefreshPeriod = 0
      .PreserveColumnInfo = True
      .ListObject.DisplayName = "表格_來自_Excel_Files_的查詢"
      .Refresh BackgroundQuery:=False
   End With
End With
End Sub

TOP

stillfish00

大學生 Rank: 4

帖子: 1018
主題: 15
精華: 0
積分: 1058
點名: 0
作業系統: win7 32bit
軟體版本: Office 2016 64-bit
閱讀權限: 50
性別: 男
來自: 桃園
註冊時間: 2012-5-9
最後登錄: 2022-9-28

3^#

發表於 2013-11-15 15:49 | 只看該作者

回復 2# stillfish00
小筆誤
Sub Test()
With Sheets("回饋數值")
   On Error Resume Next
   .Cells.ListObject.QueryTable.Delete
   .Cells.ClearContents
   On Error goto 0
   With .ListObjects.Add(SourceType:=0, Source:=Array(Array( _
      "ODBC;DSN=Excel Files;DBQ=" & ThisWorkbook.FullName & ";DefaultDir=" & ThisWorkbook.Path & ";DriverId=1046;MaxB" _
      ), Array("ufferSize=2048;PageTimeout=5;")), Destination:=.Range("$A$1")). _
      QueryTable
      .CommandText = Array( _
      "SELECT `來源1$`.代號, `來源1$`.名稱, `來源1$`.日期, `來源1$`.漲跌, `來源1$`.收盤, `來源1$`.`成交(張)`, `來源1$`.當沖, `來源2$`.大股東名稱, `來源2$`.`異動(張)`, `來源2$`.上月持張, `來源2$`.本月持張" & Chr(13) & "" & Chr(10) & "FR" _
      , _
      "OM {oj `" & ThisWorkbook.FullName & "`.`來源1$` `來源1$` LEFT OUTER JOIN `" & ThisWorkbook.FullName & "`.`來源2$` `來源2$` ON `來源1$`.代號 = `來源2$`." _
      , "代號}")
      .RowNumbers = False
      .FillAdjacentFormulas = False
      .PreserveFormatting = True
      .RefreshOnFileOpen = False
      .BackgroundQuery = True
      .RefreshStyle = xlInsertDeleteCells
      .SavePassword = False
      .SaveData = True
      .AdjustColumnWidth = True
      .RefreshPeriod = 0
      .PreserveColumnInfo = True
      .ListObject.DisplayName = "表格_來自_Excel_Files_的查詢"
      .Refresh BackgroundQuery:=False
   End With
End With
End Sub

TOP

HSIEN6001

中學生 Rank: 2

帖子: 219
主題: 24
精華: 0
積分: 243
點名: 0
作業系統: Windows10
軟體版本: Office2016
閱讀權限: 20
性別: 女
註冊時間: 2012-4-18
最後登錄: 2022-2-7

論壇榮譽勳章論壇贊助勳章

4^#

發表於 2013-11-15 19:58 | 只看該作者

回復 3# stillfish00

你好!謝謝回覆
剛剛執行結果如圖,不知道哪裡有問題

語法很深奧..看不懂 T_T

我也會試著用函數的方式寫寫看
稍後若還是弄不出來,再向各位前輩求助
^^

TOP

HSIEN6001

中學生 Rank: 2

帖子: 219
主題: 24
精華: 0
積分: 243
點名: 0
作業系統: Windows10
軟體版本: Office2016
閱讀權限: 20
性別: 女
註冊時間: 2012-4-18
最後登錄: 2022-2-7

論壇榮譽勳章論壇贊助勳章

5^#

發表於 2013-11-15 22:38 | 只看該作者

請問,陣列的設定應該如何寫
爬文許久,無法融會貫通

再請大大幫幫忙!!拜託~

回饋欄位值.rar (27.66 KB)

TOP

stillfish00

大學生 Rank: 4

帖子: 1018
主題: 15
精華: 0
積分: 1058
點名: 0
作業系統: win7 32bit
軟體版本: Office 2016 64-bit
閱讀權限: 50
性別: 男
來自: 桃園
註冊時間: 2012-5-9
最後登錄: 2022-9-28

6^#

發表於 2013-11-15 22:38 | 只看該作者

回復 4# HSIEN6001
DriverId=1046 改成 DriverId=790 看看
應該是EXCEL版本不同問題...

語法因為是錄的，我也沒有整理過，所以很難看懂，

TOP

HSIEN6001

中學生 Rank: 2

帖子: 219
主題: 24
精華: 0
積分: 243
點名: 0
作業系統: Windows10
軟體版本: Office2016
閱讀權限: 20
性別: 女
註冊時間: 2012-4-18
最後登錄: 2022-2-7

論壇榮譽勳章論壇贊助勳章

7^#

發表於 2013-11-15 22:52 | 只看該作者

回復 6# stillfish00

謝謝您回覆,修改了
但執行結果也是相同,是否要勾選引用項目?

TOP

HSIEN6001

中學生 Rank: 2

帖子: 219
主題: 24
精華: 0
積分: 243
點名: 0
作業系統: Windows10
軟體版本: Office2016
閱讀權限: 20
性別: 女
註冊時間: 2012-4-18
最後登錄: 2022-2-7

論壇榮譽勳章論壇贊助勳章

8^#

發表於 2013-11-15 23:18 | 只看該作者

回饋欄位值-個別欄位設定.rar (27.61 KB)
目前完成了個別欄位的設定
{=INDEX(來源1!$C$1:$C$1000,MATCH(回饋數值!A2&B2,來源1!$A$1:$A$1000&來源1!$B$1:$B$1000,0),1)}

但不知要如何應用這段語法,設定為區間
'=IF(INT((COLUMN(A$1)-1)/4)+1>COUNTIF(來源1!$A:$F,回饋數值!$A2),"",INDEX(來源1!$A:$F,SMALL(IF(來源1!$A$1:$M$1000=回饋數值!$A2,ROW($1:$1000),""),INT((COLUMN(A$1)-1)/4)+1),{2,3,4,5,6}))

紅字部分無法弄懂!

上面這段語法來源,忘了連結點
如附件

欄位比對回饋.rar (133.37 KB)

TOP