回复：Excel可以做什么，Excel工作笔记[全员乱入]

好久没进贴吧了，没想到这个贴还在继续更新呢

高手云集啊，菜鸟一枚进来学习，我现在的工作基本用不到excel这么多功能，但是我想学习，有的时候网上的方法看懂了 ，但遇到实际的恐怕还是不会，达人们有没有什么办法

@青水蛙鸣 @Will__Cheung @xzk235
自定义函数完成版
单元格输出结果如下
2 found:
1-A:1,4-B:3,3-C:2,2-D:1,Total=7;
2-A:1,4-B:3,3-C:2,1-D:1,Total=7;
Public aResult As Variant
Function UO(rMatrix As Variant) As Variant
Dim iSize As Integer
Dim aMatrix As Variant
Dim iResult As Integer, iColumn As Integer, iLineTotal As Integer, iMax As Integer, iSenorioCount As Integer
Dim sResult As String, sLineResult As String
ReDim aResult(0)
aMatrix = rMatrix
iSize = UBound(aMatrix, 2)
Call Pick("", iSize)
For iResult = 1 To UBound(aResult)
iLineTotal = 0
sLineResult = ""
For iColumn = 1 To iSize
iLineTotal = iLineTotal + aMatrix(Mid(aResult(iResult), iColumn, 1), iColumn)
sLineResult = sLineResult & rMatrix.Cells(1, 1).Offset(Mid(aResult(iResult), iColumn, 1) - 1, -1) & "-" & rMatrix.Cells(1, 1).Offset(-1, iColumn - 1) & ":" & aMatrix(Mid(aResult(iResult), iColumn, 1), iColumn) & ","
Next iColumn
sLineResult = sLineResult & "Total=" & iLineTotal & ";" & vbCrLf
If iLineTotal > iMax Then
iSenorioCount = 1
iMax = iLineTotal
sResult = sLineResult
ElseIf iLineTotal = iMax Then
iSenorioCount = iSenorioCount + 1
sResult = sResult & sLineResult
End If
Next iResult
sResult = iSenorioCount & " found: " & vbCrLf & sResult
UO = sResult
End Function
Sub Pick(ByVal s As String, iSize As Integer)
Dim i As Integer
For i = 1 To iSize
If InStr(1, s, i) Then
ElseIf Len(s) + 1 = iSize Then
ReDim Preserve aResult(UBound(aResult) + 1)
aResult(UBound(aResult)) = s & i
Else
Call Pick(s & i, iSize)
End If
Next i
End Sub

问题见http://tieba.baidu.com/p/2623292186
代码如下
查询第一张表下３个关键字的行，然后复制，无限制
Sub test()
Dim aKeys(1 To 3) As String
Dim i As Integer, iSheetNo As Integer
Dim rResult As Range, rSearchArea As Range, cFirstCell As Range, cFoundCell As Range
iSheetNo = 1
aKeys(1) = "90D"
aKeys(2) = " "
aKeys(3) = "SFE"
For i = LBound(aKeys) To UBound(aKeys)
If i = LBound(aKeys) Then Set rSearchArea = Sheets(iSheetNo).Cells
If cFirstCell Is Nothing Then Set cFirstCell = rSearchArea.Find(aKeys(i), , xlValues)
If cFirstCell Is Nothing Then GoTo lNoResult
Set cFoundCell = cFirstCell
Set rResult = cFirstCell.EntireRow
Do
Set cFoundCell = rSearchArea.Find(aKeys(i), cFoundCell, xlValues)
If cFoundCell.Address = cFirstCell.Address Then GoTo lNextKey
Set rResult = Union(rResult, cFoundCell.EntireRow)
Loop Until cFoundCell Is Nothing
lNextKey:
Set cFirstCell = Nothing
Set cFoundCell = Nothing
Set rSearchArea = rResult
Next i
If rResult Is Nothing Then
lNoResult:
MsgBox "No result found"
Else
rResult.Copy
End If
End Sub

分享一个才学到的小技巧
可以用INDEX(a:a,N(IF(1,{1;1;2;2;3;3}))来返回一个数组

楼主啊 本人是excel菜鸟一枚 弄了半天我也没弄懂clean函数怎么用。。。。。。也百度过了 但是还是不会用啊。。。。clean函数一次只能用于一个单元格 那么一整个表格要怎么办呢？？？求解

你有题目么，新手菜哦，求大们分享当初进步的工具

Excel是一个工具, 很多时候你需要有对于复杂事务处理的思路, 他才能够帮到你. 在这里分享一下昨天刚刚完成的一个工作.
工作任务: 配置已经上线系统的产品信息.
主要难度: 要配置9万多条产品信息, 确保每一类产品的信息准确, 每一类都使用统一的配置方案(10大类,6级目录+不同币种), 因为系统已上线, 在我拿到资料以后, 同时有一组人对系统进行更新, 我需要不定期的导入新的资料. 而且产品ID不变的情况下本身内容可能改变. 最终会以我的文件为准更新系统. 而且好像... 没人检查...
大家有思路么?