表格如何分析提取数据

02030507 · 发表于 2012-9-19 17:45

求助：
最近遇到新系统自动生成的一个表格，有大量的数据（2万条甚至更多，一个sheet里有多个连续表格）。由于数据量太大，用查找不可能在短时间解决（有几个月的数据），特来求助。

附件中：

现在的做法是先用查找已知F列的内容，找到在F列的位置（因个一个sheet里有n个表格，所以无法直接用筛选），再移动到所在位置的标题行，筛选，看H列合计金额，金额按会计原理分借贷，所以一般是合计金额的一半为单据的实际金额。无误后记录所在标题行上G列的凭证号。

其实目标就是找到F列内容所在的凭证号，麻烦高手有没有简单点的办法……

02030507 · 发表于 2012-9-19 17:48

忘记说了表格行数不固定，有多又少，每个表格不一样，所以更加麻烦。

suwenkai · 发表于 2012-9-19 18:08

看不明白，你不如做一下模拟的结果。这样大家都明白了。

02030507 · 发表于 2012-9-20 09:12

谢谢答复。那说个操作实例附件中F列有SEYA201231090500005721 这个号码（这个是已知的），因为提供的表样只是整个表的一部分，整个表中有多个表格，数据2万多行。目前的做法是先查找SEYA201231090500005721，确定其所在行。再根据所在行向上移动，到所在行的那个表格的标题行，接着进行筛选，将SEYA201231090500005721筛选出来。看H列的合计金额，因为金额是根据会计有借贷方向的，所以一般来说合计金额的一半就是这个号也就是这个单据的实际金额。当然也可以看C列的内容，比如这个号下系统往来就是211.63，而合计是423.26。所以大多是看合计金额的一半。确认金额正确后在筛选的行上有个凭证号，这就是我需要的。

也就是说根据SEYA201231090500005721找到所在的行，确认金额，找出凭证号。

由于sheet行数很多，同时有很多个表，而且每个表的行数都不同，同时SEYA201231090500005721类似这个号也不一定是连续几行出现，但在同一个表内。而且各个列位置的内容不会发生变化。

不知道有没有高手知道如何通过工具简便查找方式，谢谢。

suwenkai · 发表于 2012-9-20 12:29

还是没有看明白。猜的。

Sub 按钮1_Click()
Set d = CreateObject("scripting.dictionary")
lastrow = Sheet1.Cells(Rows.Count, 2).End(xlUp).Row
arr = Sheet1.Range("b1:h" & lastrow)
brr = Array("凭证号", "业务号", "类型", "备注一", "金额")
ReDim arrss(1 To UBound(arr), 1 To 5)
n = 1
For a = 0 To 4
arrss(1, a + 1) = brr(a)
Next
For a = 1 To UBound(arr)
If InStr(arr(a, 6), "凭证号") Then
凭证号 = arr(a, 6)
End If
If arr(a, 1) <> "" And InStr(arr(a, 1), "业务号") = False Then
s = arr(a, 5) & arr(a, 2) & 凭证号
If Not d.exists(s) Then
n = n + 1
d(s) = n
arrss(n, 1) = 凭证号
arrss(n, 2) = arr(a, 1)
arrss(n, 3) = arr(a, 2)
arrss(n, 4) = arr(a, 5)
arrss(n, 5) = arr(a, 7)
Else
arrss(d(s), 5) = arrss(d(s), 5) + arr(a, 7)
End If
End If
Next
Sheets("结果").Range("a1").Resize(n, 5) = arrss
End Sub

复制代码

02030507 · 发表于 2012-9-20 14:11

似乎可以，麻烦告知如何操作，我弄了一下宏出错，

02030507 · 发表于 2012-9-20 14:14

现在已经简单很多了，起码可以减少很多移动鼠标的次数。

最好是可以弄成，结果放在第一个工作表，后面的表设置好后，直接把数据复制进去，就可以在结果里看到了。

suwenkai · 发表于 2012-9-20 18:12

02030507 发表于 2012-9-20 14:14
现在已经简单很多了，起码可以减少很多移动鼠标的次数。

最好是可以弄成，结果放在第一个工作表，后面的 ...

能不能模拟一下结果？总不能让别人猜你想的结果吧？

02030507 · 发表于 2012-9-21 15:23

首先感谢你写的代码，看了你给的，觉得已经方便很多了。

可能我说的还不是很清楚，下面做个演示，麻烦了。
1.首先已知F列中的一个代码比如：SEYA201231090500005721，因为表格行很多，不清楚在什么位置，所以现在的做法是先用查找，找到SEYA201231090500005721所在行（同样因为有很多表格，无法一次性用筛选）。

2.接着就是向上移动到做个表的标题行，因为有时候找到的位置在表格的中下部，所以上移，上移后筛选这个号。

3.这时候看H列金额，金额是已借贷归集的，所以合计金额就是实际金额的2倍。当然你现在写的代码看的也很清楚，因为你加入的类型方便了查看借贷关系。

4.这个单据的实际金额是211.63，合计金额是423.26，在C类类型中，冲预收款和系统往来都是211.63，这就是借贷。

5.金额对了还是返回标题行，在标题行之上的那个凭证号1431090000002012610678就是需要的。也就是通过已知的号码，确定位置，筛选金额确认，最后确定凭证号。

说说希望的样子：

数据在sheet2里，sheet1里输入已知号码，点击查找结果，显示结果内容，下面有合计和1/2合计，方便核对。凭证号就是需要的了。

可能吗？谢谢。

suwenkai · 发表于 2012-9-21 18:10

是不是这样子

Sub 按钮1_Click()
Set d = CreateObject("scripting.dictionary")
te = Sheets("结果").Range("b1").Value
lastrow = Sheet1.Cells(Rows.Count, 2).End(xlUp).Row
arr = Sheet1.Range("b1:h" & lastrow)
brr = Array("凭证号", "业务号", "类型", "备注一", "金额")
ReDim arrss(1 To UBound(arr), 1 To 5)
n = 1
For a = 0 To 4
      arrss(1, a + 1) = brr(a)
Next
For a = 1 To UBound(arr)
      If InStr(arr(a, 6), "凭证号") Then
         凭证号 = arr(a, 6)
      End If
      If arr(a, 1) <> "" And InStr(arr(a, 1), "业务号") = False Then
         If arr(a, 5) = te Then
            s = arr(a, 5) & arr(a, 2) & 凭证号
            d(te & "合计") = d(te & "合计") + arr(a, 7)
            If Not d.exists(s) Then
                  n = n + 1
                  d(s) = n
                  arrss(n, 1) = 凭证号
                  arrss(n, 2) = arr(a, 1)
                  arrss(n, 3) = arr(a, 2)
                  arrss(n, 4) = arr(a, 5)
                  arrss(n, 5) = arr(a, 7)
            Else
                  arrss(d(s), 5) = arrss(d(s), 5) + arr(a, 7)
            End If
         End If
      End If
Next
If d.Count = 0 Then
      MsgBox "没有找到数据"
Else
arrss(n + 2, 4) = "合计："
arrss(n + 3, 4) = "1/2合计："
arrss(n + 2, 5) = d(te & "合计")
arrss(n + 3, 5) = d(te & "合计") / 2
Sheets("结果").Range("a6").Resize(n + 3, 5) = arrss
End If
End Sub

		自动登录	找回密码
密码			免费注册

[求助] 表格如何分析提取数据