楼主
gouweicao78 |
【题目】 提取抵消正负金额后的记录
A1:C15中，B列姓名已排序，请取出抵消正负金额后的记录
例如：张三有3笔200和2笔-200，抵消后剩下1笔200
【要求】
方法不限
使用函数与公式则不使用定义名称、辅助列
【得分】
答案正确+3技能分，视答案精彩程度酌情加1-5技能分。
【截止】 2010年11月15日前跟帖答题，写清步骤。
 提取抵消正负金额后的记录.rar
|
2楼
liuguansky |
先用字典+数组写一个：- Sub justtest()
- Dim dic, arr, i&, arr1, d, arr2, arr3, j&
- Set dic = CreateObject("scripting.dictionary")
- arr = [a1].CurrentRegion.Value
- For i = 2 To UBound(arr, 1)
- str1 = arr(i, 2) & vbTab & Abs(arr(i, 3))
- If dic.exists(str1) Then
- arr1 = Split(dic(str1), vbTab)
- dic(str1) = Application.Max(arr(i, 1), Val(arr1(0))) & vbTab & arr1(1) & vbTab & arr1(2) + arr(i, 3)
- Else: dic.Add str1, Join(Application.Index(arr, i), vbTab)
- End If
- Next i
- For Each d In dic.keys
- If Split(dic(d), vbTab)(2) = 0 Then dic.Remove d
- Next
- [f:h].ClearContents
- [f2:h2] = Application.Index(arr, 1)
- If dic.Count > 0 Then
- ReDim arr2(1 To dic.Count, 1 To 3)
- arr3 = dic.items
- For i = 1 To dic.Count
- For j = 1 To 3
- arr2(i, j) = Split(arr3(i - 1), vbTab)(j - 1)
- Next j, i
- End If
- [f3].Resize(dic.Count, 3) = arr2
- With [f2].Resize(dic.Count + 1, 3)
- .Borders.LineStyle = 1
- .EntireColumn.AutoFit
- End With
- Set dic = Nothing
- End Sub
|
3楼
水星钓鱼 |
感觉那个序号放在那好碍事阿。呵呵
方法1：
- select max(序号)as 序号,姓名,金额 from [Sheet2$] where dcount("姓名","sheet2$","姓名='"&姓名&"'and 金额="&金额)>dcount("姓名","sheet2$","姓名='"&姓名&"'and 金额="&-金额) group by 姓名,金额
|
4楼
zm0115 |
实在想用公式来做的，折腾了个把钟头，没方向，用VBA做一个，参考高人的公式
提取抵消正负金额后的记录.rar
|
5楼
mn860429 |
- select * from [Sheet2$] a where (select count(*) from [Sheet2$] where 序号 <=a.序号 and 姓名 = a.姓名 and 金额 = a.金额) > (select count(*) from [Sheet2$] where 姓名 = a.姓名 and 金额 = -a.金额)
860429.rar
|
6楼
君柳 |
辅助列数组公式：D2=SUM(N(B2:B$15&C2:C$15=B2&C2))<=SUM((B$2:B$15&C$2:C$15=B2&C2)-(B$2:B$15&C$2:C$15=B2&-C2))
过程：添加辅助列公式，将公式结果转为数值，以辅助列排序，选择区域，按CTRL键后鼠标左键拖曳数据到目标位置
 |
7楼
wqfzqgk |
函数的不懂，用VBA做了下
Sub test()
l:
For i = 2 To Cells(65536, 1).End(3).Row
For ii = 2 To Cells(65536, 1).End(3).Row
If Cells(i, 2) = Cells(ii, 2) And Cells(i, 3) = -Cells(ii, 3) Then Rows(ii).Delete: Rows(i).Delete: GoTo l
Next
Next
End Sub |
8楼
rongjun |
高级筛选- =SUMPRODUCT(($B$2:$B$15=B2)*($C$2:$C$15=-C2))<SUMPRODUCT(($B$2:B2=B2)*($C$2:C2=C2))
提取抵消正负金额后的记录.rar
|
9楼
rongjun |
多单元格数组公式- =INDEX(A:C,SMALL(IF(COUNTIFS(B1:B15,B1:B15,C1:C15,-C1:C15)<COUNTIFS(OFFSET(B1,,,ROW(1:15)),B1:B15,OFFSET(C1,,,ROW(1:15)),C1:C15),ROW(1:15),99),ROW(1:15)),{1,2,3})&""
提取抵消正负金额后的记录kk.rar
|
10楼
foodorwater |
- select * from [Sheet1$] a where (select sum(金額) from [Sheet1$] where 序號<=a.序號 and 姓名=a.姓名 and abs(金額)=a.金額)>0 and( select sum(金額) from [Sheet1$] where 姓名=a.姓名 and abs(金額)=a.金額)>0 and 金額>0
 SQL.zip
|
11楼
wjc2090742 |
 总算完成了。
提取抵消正负金额后的记录.rar
|
12楼
gouweicao78 |
本题，最初的想法是高级筛选，也就是君柳、rongjun版用的公式，目的在于考核：
1、“辅助列”思维；
2、高级筛选条件区域的设置中，混合引用的使用。具体可以参考：
数据库函数条件区域设置详解 http://www.exceltip.net/thread-8107-1-1.html
3、sql语句方面，5楼解法不错，大家可以参考，虽然我写不出来，但读起来也挺顺的，班门弄斧说一下：- select * from [Sheet2$] a where (select count(*) from [Sheet2$] where 序号 <=a.序号 and 姓名 = a.姓名 and 金额 = a.金额) > (select count(*) from [Sheet2$] where 姓名 = a.姓名 and 金额 = -a.金额)
后面有2个括号，用select count(*)计数，统计同名的人中“金额”个数>“-金额”个数的，比如200的个数与-200的个数相比，在加上“序号<=a.序号”的限定，找出相应的记录。
4、VBA方面，我就不敢妄谈了，呵呵。
此外，2003版的公式解法，我做了一个，但本人不推荐。“实用为王”，君柳的解法虽然看起来貌似粗陋，不及高级筛选之“高雅”，但实际操作时，却并不比高级筛选复杂，同时也跳开了一般做完辅助列“自动筛选”的路子，可赞，因此原本给6分，后来还是按7分评。
2003公式（未多验证）- =INDEX(A:A,SMALL(IF(MMULT(($B$2:$B$15=TRANSPOSE($B$2:$B$15))*($C$2:$C$15=-TRANSPOSE($C$2:$C$15)),ROW(1:14)^0)<MMULT(($B$2:$B$15=TRANSPOSE($B$2:$B$15))*(ROW($1:$14)>=COLUMN($A:$N))*($C$2:$C$15=TRANSPOSE($C$2:$C$15)),ROW(1:14)^0),ROW($2:$15),4^8),ROW(1:1)))&""
|
13楼
Zaezhong |
简单地验证了下草版的函数公式，将序号2的金额改为-300的时候出来的结果只有序号2对应的记录，而没有出现序号3的记录 |
14楼
foodorwater |
草版，我在10樓的SQL語句也能返回正確答案啊？為什麼沒有評分？ |
15楼
gouweicao78 |
原先3笔+200，修改其中一个为-300，那么新的情况就是：2个+200、2个-200、1个-300，当然只返回-300了。 |