麻辣家族討論版版 - Powered by Discuz! Board

標題: 函數驗證並整合資料 [打印本頁]

作者: Jared 時間: 2013-7-19 09:55 標題: 把不同檔案上的欄位內容整合

想請問大大
如果我現在有A、B、C三個以上不同檔案
想要在D檔案上整合這三份檔案上相同的欄位
目前我這裡只能將資料整合
但是不能判斷有重覆的資料

我函數的方式是寫

=IF('D:\測試\[工作資料_A.xls]總表2'!F3 &'D:\測試\[工作資料_B.xls]總表2'!F3&&'D:\測試\[工作資料_C.xls]總表2'!F3="","",'D:\測試\[工作資料_A.xls]總表2'!F3 &'D:\測試\[工作資料_B.xls]總表2'!F3&&'D:\測試\[工作資料_C.xls]總表2'!F3)

複製代碼

這樣如果有重覆的資料都會一次上去
還是說有辦法用VBA去更新目前欄位上新的資料?

頭痛中>口<~
希望有大大的建議~感激不盡!!

作者: handsometrowa 時間: 2013-7-19 12:13

回復 1# Jared

這位版友您好，
討論區中很多高手會幫忙解決問題，
但是我想如何陳述問題是很重要的一環，
如果你的物件檔案能夠上傳，讓大家看一下問題在哪裡，我想大家會彼此進步的更快。

作者: Jared 時間: 2013-7-23 12:16 標題: 函數驗證並整合資料

本帖最後由 Jared 於 2013-7-23 12:17 編輯

想請問大大
我寫了一個簡單的函數驗證判斷
目前有A、B兩個Excel表
利用另外一個Excel上的函數去判別兩個不同檔案欄位的資料
並且整合起來

=IF('D:\工作總表測試2\[A.xls]Sheet1'!A2="","",IF('D:\工作總表測試2\[B.xls]Sheet1'!A2="","",IF('D:\工作總表測試2\[A.xls]Sheet1'!A2<>"",'D:\工作總表測試2\[A.xls]Sheet1'!A2,IF('D:\工作總表測試2\[B.xls]Sheet1'!A2<>"",'D:\工作總表測試2\[B.xls]Sheet1'!A2,IF('D:\工作總表測試2\[A.xls]Sheet1'!A2='D:\工作總表測試2\[B.xls]Sheet1'!A2,'D:\工作總表測試2\[A.xls]Sheet1'!A2,IF('D:\工作總表測試2\[A.xls]Sheet1'!A2<>'D:\工作總表測試2\[B.xls]Sheet1'!A2,"資料有誤","檢查一下該筆欄位資料!!"))))))

複製代碼

但是這樣的寫法
程式只會判斷其中一個檔案
並不會兩個檔案進行檢查

還是有其他更好的方式去進行資料驗證&整合呢?
感激不盡!! >//<
[attach]15530[/attach]

作者: GBKEE 時間: 2013-7-23 13:36

回復 3# Jared
如何整合起來,可在說清楚些嗎?

作者: Jared 時間: 2013-7-23 15:09

回復 4# GBKEE

所謂整合資料就是說
假設現在有A、B兩個檔案
分別由人員甲(A)、人員乙(B)使用
而整合資料這份Excel是由窗口使用

當窗口接收到一筆資訊
會將訊息填寫在A排編號1
編號1的B&C排兩個欄位的資料填寫可能就給甲人員
窗口接收到第二筆資訊
會將編號二的B&C排的資料交給乙人員填寫

而窗口就是要檢視整合資料這份檔案
利用函數去擷取A、B這兩份資料並整合起來
為了怕人員填錯欄位
所以才添加驗證兩方資料的相同性

但是目前函數驗證的部分
沒辦法同時驗證兩個檔案
不知道有沒有其他方式可以解決??

作者: stillfish00 時間: 2013-7-23 15:30

本帖最後由 stillfish00 於 2013-7-23 15:31 編輯

回復 5# Jared
這樣說會清楚點，是這樣吧？

兩檔案儲存格值       >>       整合後的值
---------------------------------------------
相同                            >>       任一值
A檔漏填(空白)          >>       取B檔值
B檔漏填(空白)          >>       取A檔值
不相同                         >>       顯示 "資料有誤"

再來就是...檔案確定只有兩個嗎?

作者: Jared 時間: 2013-7-25 09:31

回復 6# stillfish00

謝謝大大幫我整理想法
是我表達得不夠簡單，抱歉！

後來我把想法再精簡化
讓程式判斷少一些

大大幫我整理後的想法
我檔案稍微做了一點修改
檔案整合分Ａ（窗口＿資料Ａ）、Ｂ（處理人員甲）、Ｃ（處理人員乙）
處理人員甲&乙接收到資料A
會依照資料A

三檔案儲存格值       >>       整合後的值
---------------------------------------------
相同                            >>       任一值
B檔漏填(空白)          >>       取C檔值
C檔漏填(空白)          >>       取B檔值
不相同                         >>       顯示 "資料有誤"
===========================

當初會做判別程式
主要是因為如果甲人員填錯資料A的對應欄位(資料B、C)
但是函數如果是判別資料有誤
空白它也會判斷資料有出入
所以程式跑起來怪怪的
檔案修改如附檔
[attach]15551[/attach]
如果要變成VBA寫法，要怎麼修改？謝謝!!

作者: GBKEE 時間: 2013-7-25 10:36

回復 7# Jared
試試看

Option Explicit
Sub Ex()
Dim Rng As String, Ar(1 To 3), A(), i As Integer, ii As Integer, X As Integer
Rng = "A1:C10" '制定所有檔案在相同的範圍
Ar(1) = Workbooks("A.XLS").Sheets(1).Range(Rng).Value '檔案是開啟的
Ar(2) = Workbooks("B.XLS").Sheets(1).Range(Rng).Value
Ar(3) = Workbooks("C.XLS").Sheets(1).Range(Rng).Value
ReDim A(1 To UBound(Ar(1), 1), 1 To UBound(Ar(1), 2))
For X = 1 To UBound(Ar(1), 2)
For i = 1 To UBound(Ar(1), 2)
For ii = 1 To UBound(Ar(1), 1)
If ii = 1 Then
A(ii, i) = Ar(X)(ii, i)
Else
A(ii, i) = IIf(A(ii, i) <> "" And Ar(X)(ii, i) <> "", "資料有誤", A(ii, i) & Ar(X)(ii, i))
End If
Next
Next
Next
Workbooks("總表彙整.xls").Sheets(1).Range(Rng) = A
End Sub

複製代碼

作者: Jared 時間: 2013-7-25 10:49

本帖最後由 GBKEE 於 2013-7-25 10:59 編輯

回復 8# GBKEE

感謝大大!!
我馬上試看看(^^)

作者: Jared 時間: 2013-7-25 11:40

回復 8# GBKEE

謝謝GBKEE
程式部分我測試過
甚至驗證機制都有了，真的蠻方便的 :)

但是VBA的方式需要將更新的檔案都打開才能進行讀取的動作
如果檔名都必須在一樣的情況下
就沒辦法這樣做了

我這裡的檔案資料量比較大
是利用同樣檔名不同資料夾
程式是採用函數的方式
但是每次資料更新都需要再重新連結一次位置
使用上就不是那麼方便

我再試看看有沒有其他的辦法好了
再次感謝GBKEE 的協助 :)

作者: Jared 時間: 2013-8-1 16:56

本帖最後由 Jared 於 2013-8-1 17:08 編輯

回復 8# GBKEE

想請問一下大大有關您協助程式碼的問題
以下的程式是用什麼原理去寫的呢?
有點不太清楚
如果範圍不是A1:C10
而是A1:D10 或是其他範圍
要修改哪個地方呢?
希望大大能幫我解答，感激不盡><

ReDim A(1 To UBound(Ar(1), 1), 1 To UBound(Ar(1), 2))
For X = 1 To UBound(Ar(1), 2)
      For i = 1 To UBound(Ar(1), 2)
         For ii = 1 To UBound(Ar(1), 1)
            If ii = 1 Then
                  A(ii, i) = Ar(X)(ii, i)
            Else
                  A(ii, i) = IIf(A(ii, i) <> "" And Ar(X)(ii, i) <> "", "資料有誤", A(ii, i) & Ar(X)(ii, i))
            End If
         Next
      Next
Next
Workbooks("總表彙整.xls").Sheets(1).Range(Rng) = A
End Sub

作者: Jared 時間: 2013-8-2 14:50

回復 4# GBKEE

請問大大，現在有一個表單
需要統計三個人安排旅遊的時間和地點
有套用你的ＶＢＡ程式
但是有地方不曉得該怎麼修改

程式執行的流程如下：
旅遊地點先驗證是否有誤，怕有人修改到　
分別有四個時間點要統計
最後在意見彙整的部分顯示統計合時何地最高票

麻煩大大了，目前為了怎麼修改傷透腦筋＞\\＜
[attach]15672[/attach]

作者: GBKEE 時間: 2013-8-2 16:50

本帖最後由 GBKEE 於 2013-8-2 16:55 編輯

回復 12# Jared

Option Explicit
Sub Ex()
Dim Rng As String, Ar(1 To 3), A(), i As Integer, ii As Integer, X As Integer
'要合併三個檔案. -> Ar(1 To 3)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
A = Array("D:\工作總表\小明.xls", "D:\工作總表\小華.xls", "D:\工作總表\小美.xls") '路徑及檔名請依需求修改
Rng = "A1:E10" '定所有檔案在相同的範圍
For i = 0 To UBound(A)
With Workbooks.Open(A(i)).Sheets(1) 'With 陳述式在一個單一物件或一個使用者自訂型態上執行一系列的陳述式。
Ar(i + 1) = .Range(Rng).Value '二維陣列:第一維 = 工作表的列,第二維 = 工作表的欗,
.Parent.Close
End With
Next
ReDim A(1 To UBound(Ar(1), 1), 1 To UBound(Ar(1), 2)) '陣列重新配置維數及維數元素之上下限索引值-> "A1:E10" 的大小
For X = 1 To UBound(Ar)
For i = 1 To UBound(Ar(1), 2) '欄
For ii = 1 To UBound(Ar(1), 1) '列
If ii = 1 Or i = 1 Then
A(ii, i) = Ar(X)(ii, i) '第1列或第1欗
Else
If Ar(X)(ii, i) <> "" Then A(ii, i) = A(ii, i) + 1 '有資料 + 1
End If
Next
Next
Next
Workbooks("旅遊地點統計.xls").Sheets(1).Range(Rng) = A
Application.ScreenUpdating = True '結束後更新螢幕
Application.DisplayAlerts = True
End Sub

複製代碼

作者: Jared 時間: 2013-8-5 10:45

回復 13# GBKEE

感謝大大的協助
陣列索引的使用方式還需要研究研究
先試著修改看看

再次謝謝大大^^

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)