作者:乔山办公网日期:
返回目录:excel表格制作
如:AAA表,为编抄码、名称、收入 ; BBB表,为编码、名称、支出
CCC表,为编码、名称、上期、收入、支出、期未
可将AAA表与百BBB表分别以编码排序;(假设为200条记录)
在CCC表D2,D2=VLOOKUP($A$2,AAA表!度$A$1:$C$200,3)
在CCC表E2,E2=VLOOKUP($A$2,BBB表!$A$1:$C$200,3)
如果对应不到编码,可能系统知会自动乱放一个,则还公式可以写得复杂点,即:
D2=IF(D2=VLOOKUP($A$2,AAA表!$A$1:$C$200,1),VLOOKUP($A$2,AAA表!$A$1:$C$200,3),0)
E2=IF(E2=VLOOKUP($A$2,BBB表!$A$1:$C$200,1),VLOOKUP($A$2,BBB表!$A$1:$C$200,3),0)
其他单元格公式下拉即可!道加减公式略!
'我也遇到过类似问题,是通过VBA解决的
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)'在Sheet2中设置这个鼠标右键函数
If ActiveCell.Column = 1 And ActiveCell.Value <> "" Then
Dim CmdBar0, CmdBar1, CmdBar2 As CommandBarControl
For Each CmdBar0 In Application.CommandBars("cell").Controls
CmdBar0.Delete
Next
Set CmdBar1 = Application.CommandBars("cell").Controls.Add(Type:=msoControlButton)
Set CmdBar2 = Application.CommandBars("cell").Controls.Add(Type:=msoControlButton)
CmdBar1.Caption = "材料编码7a686964616fe78988e69d83361→报价单"
CmdBar1.OnAction = "AddCodeToBjd"
Else
Application.CommandBars("cell").Reset
End If
End Sub
Sub AddCodeToBjd()'这是被调用的程序,可以存在一个块里
Dim CodeCount, m As Integer, BlankRow As Long, RCode As Range, sCode(20) As String
For Each RCode In Application.Selection.Cells
If RCode.Column = 1 And RCode.Value <> "" And CodeCount <= 19 Then
CodeCount = CodeCount + 1
sCode(CodeCount) = RCode.Value
End If
Next RCode
With Sheets("sheet2")
BlankRow = Application.CountA(.Range("A:A")) + 1
If BlankRow <> .Range("A65536").End(xlUp).Row + 1 Then
MsgBox "Sheet2Column1(编码)列有空行或者表处于筛选状态!", vbCritical, "温馨提示:"
Exit Sub
End If
m = 1
Do While sCode(m) <> "" And m <= CodeCount
.Cells(BlankRow, 3) = sCode(m)
BlankRow = BlankRow + 1
m = m + 1
Loop
End With
End Sub
C2公式=IFNA(INDEX(Sheet2!度A:A,MATCH(A2,Sheet2!B:B,0),0),INDEX(Sheet2!D:D,MATCH(A2,Sheet2!C:C,0),0)),下拉填问充。
哦,上答面公式里的sheet2要改成检测版编号。所以最终的公式应该是权=IFNA(INDEX(检测编号!A:A,MATCH(A2,检测编号!B:B,0),0),INDEX(检测编号!D:D,MATCH(A2,检测编号!C:C,0),0))