楼主
gouweicao78 |
如图：如何在E列获取对方科目名称。
【说明】例如E2：因为G2借方有金额，因此对方为“贷方”，同一笔凭证号“记62”下共4笔贷方有金额的记录，3个“主营业务收入”和1个“应交税金”，把这4个名称去重复、合并，即E2想要得到的结果。
【解决方案】
1、启用迭代运算，设置迭代计算次数为100
2、插入“复选框”控件并链接到J1单元格，作为启动开关；
3、在K1输入=IF(J1,K1+1,1) 作为计数器
4、在E2输入公式并向下复制：- =IF(J$1,E2&IF((INDEX(IF(G2="",G:G,H:H),K$1)<>"")*(INDEX(B:B,K$1)=B2)*ISERR(FIND(","&INDEX(D:D,K$1),","&E2)),IF(E2="","",",")&INDEX(D:D,K$1),""),"")
5、勾选J1启动开关，即可获得结果。
 【循环引用】获取对方科目名称.rar
|
2楼
peakchu |
跟草版学迭代
 |
3楼
LOGO |
向草版学习.
|
4楼
嘉昆2011 |
学习迭代
 |
5楼
kevinchengcw |
凑热闹，来个vba的- Sub test()
- Dim Arr, Arrt, N&, I&, Result, Str$, Str2$, Dic As Object, Dic2 As Object
- With Worksheets("sheet1")
- Arr = .[a1].Resize(.Cells(.Rows.Count, 2).End(3).Row, .Cells(1, .Columns.Count).End(1).Column).Value
- End With
- Set Dic = CreateObject("scripting.dictionary")
- Set Dic2 = CreateObject("scripting.dictionary")
- For N = LBound(Arr) + 1 To UBound(Arr)
- Arr(N, 2) = Trim(Arr(N, 2))
- Arr(N, 4) = Trim(Arr(N, 4))
- If Arr(N, 7) = "" Then Str = Arr(N, 2) & vbTab & "贷" Else Str = Arr(N, 2) & vbTab & "借"
- Str2 = Arr(N, 2) & vbTab & Trim(Arr(N, 4))
- If Dic.exists(Str) And Not Dic2.exists(Str2) Then
- Dic(Str) = Dic(Str) & "," & Arr(N, 4)
- Dic2(Str2) = ""
- ElseIf Not Dic.exists(Str) Then
- Dic(Str) = Arr(N, 4)
- Dic2(Str2) = ""
- End If
- Next N
- ReDim Result(LBound(Arr) + 1 To UBound(Arr), 1 To 1)
- With CreateObject("vbscript.regexp")
- .Global = True
- For N = LBound(Arr) + 1 To UBound(Arr)
- If Arr(N, 7) = "" Then Str = Arr(N, 2) & vbTab & "借" Else Str = Arr(N, 2) & vbTab & "贷"
- .Pattern = "," & Arr(N, 4) & "|" & Arr(N, 4) & ",|" & Arr(N, 4)
- Result(N, 1) = .Replace(Dic(Str), "")
- Next N
- End With
- With Worksheets("sheet1")
- .[e2].Resize(UBound(Result) - 1).Value = Result
- End With
- Set Dic = Nothing
- Set Dic2 = Nothing
- End Sub
|
6楼
滴水兴波 |
在微博上发了个图片感谢草版的解答，现在把遇到的问题再细说一下，请草版和各位老师请多多指教：
1、目的：根据从财务软件导出的下列清单，对每一行记录取对方科目
2、我理解的最优返回值的思路：
（1）最外层次的IF函数思路：IF（相同A&B对应的数字全在G或H，返回所有A&B相同的其他行的D列值，第二层次IF函数）
（2）第二层次的IF函数思路：IF（IF(G*<>0,取（所有A&B=G*对应的日期&凭证号的，发生在H列的数字对应的D列的值），（所有A&B=G*对应的A&B的发生额在H列的数字对应的D列的值））
（3）最后：对上述步骤对取得的值去除重复项，并用“，”隔开
3、我理解的次优返回值的思路：
（1）返回所有A&B相同的其他行的D列值，去除重复项，并用“，”隔开
 对方科目求值.zip
|
7楼
gouweicao78 |
看来，理解还是有困难。
能否像1楼的【说明】那样描述，比如你的E4单元格返回“应收账款”是怎样来的、E10……等几个又是怎样来的。你这么IF、这么A&B=G*……看不明白啊，呵呵。
1楼2楼解决的，找同一个凭证下、贷方有金额的就找借方（反之亦然）有金额的所有记录对应的科目名称，然后去重、合并。
没有考虑日期、金额多少的因素。 |
8楼
滴水兴波 |
再补充一下，请草版再研究研究1111.jpg
对方科目求值.zip
|
9楼
gouweicao78 |
 又来IF、SUMIF，这些看了人就混乱了
从举例说的E4、E7、E10看：
1、金额多少没关系，比如E10这个凭证里，借方共3.7万，而贷方多多了，不必管——对吗？
2、E7的例子，说明不只是在借方里找，还可能在同一个方向找正负号不同的金额（也不管金额多少？）——对吗？ |
10楼
滴水兴波 |
1、不好意思，一时疏忽E10这边未修改完，一个凭证的借方合计贷方合计总是相等，但是其中一条记录并不要求取相等金额
2、使用IF、SUMIF只是表达取值的原理，并非要使用IF、SUMIF计算
3、对方科目可能在反方向，也可能是同方向的正负号反方向1111.jpg
对方科目求值.zip
|
11楼
gouweicao78 |
如图：如何在E列获取对方科目名称。
【说明】例如E2：因为G2借方有金额，因此对方为“贷方”，同一笔凭证号“记62”下共4笔贷方有金额的记录，3个“主营业务收入”和1个“应交税金”，把这4个名称去重复、合并，即E2想要得到的结果。
【解决方案】
1、启用迭代运算，设置迭代计算次数为100
2、插入“复选框”控件并链接到J1单元格，作为启动开关；
3、在K1输入=IF(J1,K1+1,1) 作为计数器
4、在E2输入公式并向下复制：- =IF(J$1,E2&IF((INDEX(IF(G2="",G:G,H:H),K$1)<>"")*(INDEX(B:B,K$1)=B2)*ISERR(FIND(","&INDEX(D:D,K$1),","&E2)),IF(E2="","",",")&INDEX(D:D,K$1),""),"")
5、勾选J1启动开关，即可获得结果。
【循环引用】获取对方科目名称.rar
|
12楼
peakchu |
跟草版学迭代
|
13楼
LOGO |
向草版学习.
|
14楼
嘉昆2011 |
学习迭代
|
15楼
kevinchengcw |
凑热闹，来个vba的- Sub test()
- Dim Arr, Arrt, N&, I&, Result, Str$, Str2$, Dic As Object, Dic2 As Object
- With Worksheets("sheet1")
- Arr = .[a1].Resize(.Cells(.Rows.Count, 2).End(3).Row, .Cells(1, .Columns.Count).End(1).Column).Value
- End With
- Set Dic = CreateObject("scripting.dictionary")
- Set Dic2 = CreateObject("scripting.dictionary")
- For N = LBound(Arr) + 1 To UBound(Arr)
- Arr(N, 2) = Trim(Arr(N, 2))
- Arr(N, 4) = Trim(Arr(N, 4))
- If Arr(N, 7) = "" Then Str = Arr(N, 2) & vbTab & "贷" Else Str = Arr(N, 2) & vbTab & "借"
- Str2 = Arr(N, 2) & vbTab & Trim(Arr(N, 4))
- If Dic.exists(Str) And Not Dic2.exists(Str2) Then
- Dic(Str) = Dic(Str) & "," & Arr(N, 4)
- Dic2(Str2) = ""
- ElseIf Not Dic.exists(Str) Then
- Dic(Str) = Arr(N, 4)
- Dic2(Str2) = ""
- End If
- Next N
- ReDim Result(LBound(Arr) + 1 To UBound(Arr), 1 To 1)
- With CreateObject("vbscript.regexp")
- .Global = True
- For N = LBound(Arr) + 1 To UBound(Arr)
- If Arr(N, 7) = "" Then Str = Arr(N, 2) & vbTab & "借" Else Str = Arr(N, 2) & vbTab & "贷"
- .Pattern = "," & Arr(N, 4) & "|" & Arr(N, 4) & ",|" & Arr(N, 4)
- Result(N, 1) = .Replace(Dic(Str), "")
- Next N
- End With
- With Worksheets("sheet1")
- .[e2].Resize(UBound(Result) - 1).Value = Result
- End With
- Set Dic = Nothing
- Set Dic2 = Nothing
- End Sub
|
16楼
滴水兴波 |
在微博上发了个图片感谢草版的解答，现在把遇到的问题再细说一下，请草版和各位老师请多多指教：
1、目的：根据从财务软件导出的下列清单，对每一行记录取对方科目
2、我理解的最优返回值的思路：
（1）最外层次的IF函数思路：IF（相同A&B对应的数字全在G或H，返回所有A&B相同的其他行的D列值，第二层次IF函数）
（2）第二层次的IF函数思路：IF（IF(G*<>0,取（所有A&B=G*对应的日期&凭证号的，发生在H列的数字对应的D列的值），（所有A&B=G*对应的A&B的发生额在H列的数字对应的D列的值））
（3）最后：对上述步骤对取得的值去除重复项，并用“，”隔开
3、我理解的次优返回值的思路：
（1）返回所有A&B相同的其他行的D列值，去除重复项，并用“，”隔开
对方科目求值.zip
|
17楼
gouweicao78 |
看来，理解还是有困难。
能否像1楼的【说明】那样描述，比如你的E4单元格返回“应收账款”是怎样来的、E10……等几个又是怎样来的。你这么IF、这么A&B=G*……看不明白啊，呵呵。
1楼2楼解决的，找同一个凭证下、贷方有金额的就找借方（反之亦然）有金额的所有记录对应的科目名称，然后去重、合并。
没有考虑日期、金额多少的因素。 |
18楼
滴水兴波 |
再补充一下，请草版再研究研究1111.jpg
对方科目求值.zip
|
19楼
gouweicao78 |
又来IF、SUMIF，这些看了人就混乱了
从举例说的E4、E7、E10看：
1、金额多少没关系，比如E10这个凭证里，借方共3.7万，而贷方多多了，不必管——对吗？
2、E7的例子，说明不只是在借方里找，还可能在同一个方向找正负号不同的金额（也不管金额多少？）——对吗？ |
20楼
滴水兴波 |
1、不好意思，一时疏忽E10这边未修改完，一个凭证的借方合计贷方合计总是相等，但是其中一条记录并不要求取相等金额
2、使用IF、SUMIF只是表达取值的原理，并非要使用IF、SUMIF计算
3、对方科目可能在反方向，也可能是同方向的正负号反方向1111.jpg
对方科目求值.zip
|