如何根据工作表填充人员名单

quanyuchan

统计表内人数统计倒可以用公式，但是人员名单。。。还得请教高手了。
BY THE WAY,LZ以后设计表格时能否尽量保持对应字段一致，不然会很复杂。。。

linbaoman

谢谢quanyuchan ，因为是给别人提供名单所有无法让他们自己去看，但还是十分感谢。
其实在发帖前我用了一个公式：IF(E$27=F2,B$27,"")&IF(E$27=F2,"  ","")&IF(E$28=F2,B$28,"")&IF(E$28=F2,"  ","")&IF(E$29=F2,B$29,"")&IF(E$29=F2,"  ","")&IF(E$30=F2,B$30,"")&IF(E$30=F2,"  ","")&IF(E$31=F2,B$31,"")&IF(E$31=F2,"  ","")&IF(E$32=F2,B$32,"")&IF(E$32=F2,"  ","")&IF(E$33=F2,B$33,"")&IF(E$33=F2,"  ","")&IF(E$34=F2,B$34,"")&IF(E$34=F2,"  ","")&IF(E$35=F2,B$35,"")&IF(E$35=F2,"  ","")&IF(E$36=F2,B$36,"")&IF(E$36=F2,"  ","")&IF(E$37=F2,B$37,"")&IF(E$37=F2,"  ","")&IF(E$38=F2,B$38,"")&IF(E$38=F2,"  ","")&IF(E$39=F2,B$39,"")&IF(E$39=F2,"  ","")&IF(E$40=F2,B$40,"")&IF(E$40=F2,"  ","")&IF(E$41=F2,B$41,"")&IF(E$41=F2,"  ","")&IF(E$42=F2,B$42,"")&IF(E$42=F2,"  ","")&IF(E$43=F2,B$43,"")&IF(E$43=F2,"  ","")&IF(E$44=F2,B$44,"")&IF(E$44=F2,"  ","")&IF(E$45=F2,B$45,"")&IF(E$45=F2,"  ","")&IF(E$46=F2,B$46,"")&IF(E$46=F2,"  ","")&IF(E$47=F2,B$47,"")&IF(E$47=F2,"  ","")&IF(E$48=F2,B$48,"")&IF(E$48=F2,"  ","")&IF(E$49=F2,B$49,"")&IF(E$49=F2,"  ","")&IF(E$50=F2,B$50,"")&IF(E$50=F2,"  ","")&IF(E$51=F2,B$51,"")&IF(E$51=F2,"  ","")

不过由于if公式的长度受限制，每次用只能是25个，但如果把这个公式反复用20回就能解决500个人员的数字，由于用到这个表的二级单位的人员一般不超过500人，所以也能勉强用，但毕竟麻烦。
还是请高手请教。

兜里有糖糖

强！学习了．谢谢．

leonty123

透视还是比较好的办法！

wx486

提供一个思路！供参考。
先按名单表的E、F、G列排序。
=IF(SUMPRODUCT((名单!$E$1:$E$1000=B5)*(名单!$F$1:$F$1000=$A$1)*(名单!$G$1:$G$1000=$C$3))>0,MID(PHONETIC(OFFSET(名单!$C$1,MATCH(B5,名单!E:E,)-1,,SUMPRODUCT((名单!$E$1:$E$1000=B5)*(名单!$F$1:$F$1000=$A$1)*(名单!$G$1:$G$1000=$C$3)),2)),2,999),"")

phz1234

原帖由 linbaoman 于 2011-1-26 13:08 发表
在excel2007中，如有100行数据，经筛选后选择了第5-17行和32100行，在删除筛选结果后，（有时）17-31行也被删除了，这是EXCEL2007的漏洞吗？怎么解决？谢谢！

筛选后需要“重新应用”，再删除

eqzh

VBA做的，楼主按一下“填写”按钮，看一下结果是否符合要求
Sub fb()
Dim a%, b%, c%, d%, i%
i = 1
a = Sheet1.Range("a65536").End(xlUp).Row
b = Sheet2.Range("a65536").End(xlUp).Row
For c = 2 To a
  For d = 2 To b
    If Sheet1.Cells(c, 4) = Sheet2.Cells(d, 2) Then
      Select Case Sheet1.Cells(c, 5)
         Case "国内"
            Select Case Sheet1.Cells(c, 6)
              Case "工作"
                Sheet2.Cells(d, 3) = Sheet2.Cells(d, 3) & " " & Sheet1.Cells(c, 3)
                Sheet2.Cells(d, 4) = Sheet2.Cells(d, 4) + i
              Case "待岗"
                Sheet2.Cells(d, 5) = Sheet1.Cells(c, 3)
              Case "新入职"
                Sheet2.Cells(d, 6) = Sheet2.Cells(d, 6) & " " & Sheet1.Cells(c, 3)
                Sheet2.Cells(d, 7) = Sheet2.Cells(d, 7) + i
              Case "事假"
                Sheet2.Cells(d, 8) = Sheet2.Cells(d, 8) & " " & Sheet1.Cells(c, 3)
              Case "修年休假"
                Sheet2.Cells(d, 9) = Sheet2.Cells(d, 9) & " " & Sheet1.Cells(c, 3)
             End Select
         Case "国外"
            Select Case Sheet1.Cells(c, 6)
              Case "工作"
                Sheet2.Cells(d, 10) = Sheet2.Cells(d, 10) & " " & Sheet1.Cells(c, 3)
              Case "待岗"
                Sheet2.Cells(d, 11) = Sheet2.Cells(d, 11) & " " & Sheet1.Cells(c, 3)
              Case "新入职"
                Sheet2.Cells(d, 12) = Sheet2.Cells(d, 12) & " " & Sheet1.Cells(c, 3)
              Case "事假"
                Sheet2.Cells(d, 13) = Sheet2.Cells(d, 13) & " " & Sheet1.Cells(c, 3)
              Case "修年休假"
                Sheet2.Cells(d, 14) = Sheet2.Cells(d, 14) & " " & Sheet1.Cells(c, 3)
             End Select
    End Select
End If
Next
Next
End Sub

linbaoman

谢谢wx486   ，您的回答几乎满足了我的要求，也看懂了，还是十分感谢！

linbaoman

谢谢 phz1234 ，我下次应用的时候会这么做，但不知道完全是这个原因。

linbaoman

谢谢eqzh   十分完美，我让我们分公司填写时候采用两种方式，不会用宏的就用15楼 wx486 的帖子，会用宏的就用您这个，十分感谢！