[求助]如何将几个工作表的个人信息汇总到一张表

whl1976

出差回来，领导让把本年个人信息汇总，小弟是个菜鸟，请各位帮忙 bge4c4ll.rar (2.58 KB, 下载次数: 53)

2007-12-9 16:25 上传

点击文件名下载附件
[求助]如何将几个工作表的个人信息汇总到一张表

<br/>

cailc1361

wYBwQFm4.rar (4.81 KB, 下载次数: 186)

2007-12-9 17:01 上传

点击文件名下载附件
[求助]如何将几个工作表的个人信息汇总到一张表

<br/>

weiguoyin868

<p>帮你做了，看看如何。</p><p>说明：</p><p>1，添加了一个工作表，名称‘个人台帐’。</p><p>2，用VBA编个一小段程序，代码：</p><p>Public Sub 个人台帐()<br/>Application.ScreenUpdating = False '避免屏幕刷新时闪烁</p><p>'一下是定义变量<br/>Dim I As Integer, J As Integer '循环变量<br/>Dim Hrow1 As Integer, Hrow2 As Integer&nbsp; '最后行的变量</p><p>Dim Sname As String, Stname As String<br/>Dim Ggz1, Ggz2, Ggz3, Ggz4, Ggz5, Ggz6, Ggz7, Ggz8, Ggz9, Ggz10, Ggz11, Ggz12, Ggz13 As Double</p><p>'汇总表单元格定义数据类型<br/>Sheets("个人台帐").Activate<br/>Range("A2:O100").Clear '单元格清空，随人数可增加<br/>Columns("A:A").Select<br/>Selection.NumberFormatLocal = "@"&nbsp; 'A列定义为字符型<br/>Columns("B:N").Select<br/>Selection.NumberFormatLocal = "0.00_ " 'B到N列定义为数字型，保留两位小数<br/>&nbsp;&nbsp;&nbsp; <br/>For I = 1 To 5&nbsp;&nbsp;&nbsp; '汇总的工作表循环，这里需要汇表1到5<br/>&nbsp;&nbsp;&nbsp; Worksheets(I).Activate '激活I变量指定的表<br/>&nbsp;&nbsp;&nbsp; Hrow1 = Range("B65536").End(xlUp).Row&nbsp; '确定最后一个非空行的行数<br/>&nbsp;&nbsp;&nbsp; <br/>&nbsp;&nbsp; For J = 2 To Hrow1</p><p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sname = Cells(J, 1) '一下是将单元格数据赋予变量<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ggz1 = Cells(J, 2)<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ggz2 = Cells(J, 3)<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ggz3 = Cells(J, 4)<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ggz4 = Cells(J, 5)<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ggz5 = Cells(J, 6)<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ggz6 = Cells(J, 7)<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ggz7 = Cells(J, 8)<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ggz8 = Cells(J, 9)<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ggz9 = Cells(J, 10)<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ggz10 = Cells(J, 11)<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ggz11 = Cells(J, 13)<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ggz12 = Cells(J, 13)<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ggz13 = Cells(J, 14)<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Stname = Worksheets(I).Name<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Worksheets("个人台帐").Activate '激活汇总表<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Hrow2 = Range("B65536").End(xlUp).Row + 1<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(Hrow2, 1) = Sname '一下是将变量赋予汇总表单元格数据<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(Hrow2, 2) = Stname<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(Hrow2, 3) = Ggz1<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(Hrow2, 4) = Ggz2<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(Hrow2, 5) = Ggz3<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(Hrow2, 6) = Ggz4<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(Hrow2, 7) = Ggz5<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(Hrow2, 8) = Ggz6<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(Hrow2, 9) = Ggz7<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(Hrow2, 10) = Ggz8<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(Hrow2, 11) = Ggz9<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(Hrow2, 12) = Ggz10<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(Hrow2, 13) = Ggz11<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(Hrow2, 14) = Ggz12<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(Hrow2, 15) = Ggz13</p><p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sname = "" '一下是将变量清零<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ggz1 = 0<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ggz2 = 0<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ggz3 = 0<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ggz4 = 0<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ggz5 = 0<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ggz6 = 0<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ggz7 = 0<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ggz8 = 0<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ggz9 = 0<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ggz10 = 0<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ggz11 = 0<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ggz12 = 0<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ggz13 = 0<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Stname = ""<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Worksheets(I).Activate '激活I变量指定的表<br/>&nbsp;&nbsp; Next J</p><p>Next I<br/>&nbsp; '以下是排序</p><p>&nbsp; Worksheets("个人台帐").Activate<br/>&nbsp; Hrow2 = Range("B65536").End(xlUp).Row<br/>&nbsp; <br/>&nbsp;'&nbsp;&nbsp;&nbsp;&nbsp; Range("A1:O16").Select<br/>&nbsp;'&nbsp;&nbsp; Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _<br/>&nbsp;'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _<br/>&nbsp;'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :=xlPinYin, DataOption1:=xlSortNormal<br/>&nbsp; </p><p>&nbsp; Range("A1:O" &amp; Hrow2).Select<br/>&nbsp;&nbsp;&nbsp; Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _<br/>&nbsp;&nbsp;&nbsp; OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _<br/>&nbsp;&nbsp;&nbsp; :=xlPinYin, DataOption1:=xlSortNormal</p><p>End Sub<br/>3、汇总表用了函数：如：</p><p>{=INDEX(个人台帐!$O$1:$O$16,MATCH(C2&amp;A2,个人台帐!$A$1:$A$16&amp;个人台帐!$B$1:$B$16,0))}<br/>代码</p><p>改后文件：</p><p></p><p></p> stqJyxqH.rar (13.81 KB, 下载次数: 86)

2007-12-9 19:13 上传

点击文件名下载附件
[求助]如何将几个工作表的个人信息汇总到一张表

<br/>

weiguoyin868

<p>2楼写的看看，很好，简单有效。但我对公式不不明白的地方，如：</p><p>=IF($C10="","",INDEX(工资总额!$A$2:$N$60,MATCH($C10,工资总额!$A$2:$A$60,0),COLUMN(B6)))</p><p>能说明COLUMN(B6)的作用吗？</p>

whl1976

<p>楼上大哥真的好感谢你们，二楼大哥的方法简便，便小弟也看不明白，最好能给小弟个说明，小弟向你虚心学习，三楼大哥的更是高深，小弟看的是一头雾水，VBA对我来说太难了，二楼大哥的方法看似要简单些，拟采用二楼大哥的，再次谢谢楼上的几位大哥，多谢帮助。好人啊！！</p>

coolswp

<p>IF($C5="","",INDEX(工资总额!$A$2:$N$60,MATCH($C5,工资总额!$A$2:$A$60,0),COLUMN(B1)))</p><p></p><p>$C5="","", 表示c5是空的，显示空值，这样表好看，不会出现index不到的数据</p><p>MATCH($C5,工资总额!$A$2:$A$60,0), 是确定c5姓名对应工资总额里面的对应行数，简单说就是排第几行</p><p>index是返回这人的具体数据排第几列，有行有列就确定一个数值</p><p>column（b1）完全是为了产生1，2，3，4，。。。。等等等的列数字，你可以键入1，2，3，4等代替</p>

小草儿

<p>认真学习下，这个能用到。</p>