A列數(shù)據(jù)兩兩組合识脆，最接近3000的組合是设联？
A列數(shù)據(jù)有重復(fù)！Ｗ莆妗离例！
數(shù)據(jù)
寫(xiě)法1，A列分為a悉稠，b兩表宫蛆，兩兩組合，減去3000的猛，等于0的就是符合條件的耀盗。
Sub 配對(duì)()
Dim cnn As Object, rs As Object, LastRow&, SQL$
Set cnn = CreateObject("ADODB.Connection")
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
cnn.Open "Provider = Microsoft.ace.Oledb.12.0;Extended Properties =Excel 12.0;Data Source =" & ThisWorkbook.FullName
SQL = "select DISTINCT * from (select a.組合,(b.組合,a.組合+b.組合-3000) as 差 from"
SQL = SQL & "[sheet1$a1:a" & LastRow & "] a,[sheet1$a1:a" & LastRow & "] b where a.組合<>b.組合) where 差=0 "
Set rs = cnn.Execute(SQL)
With Sheets("sheet1")
.Range("j1:L100000").ClearContents
.Range("j1").CopyFromRecordset rs
End With
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub
- 1、先求兩兩組合及其減去3000的差值
select a.組合,b.組合,(a.組合+b.組合-3000) as 差 from
[sheet1$a1:a" & LastRow & "] a,
[sheet1$a1:a" & LastRow & "] b
where a.組合<>b.組合
- 2卦尊、在上表提取數(shù)據(jù)袍冷，差=0，的就是兩兩組合等于3000的猫牡，再去重復(fù)。
select DISTINCT * from
(
select a.組合,b.組合,(a.組合+b.組合-3000) as 差 from
[sheet1$a1:a" & LastRow & "] a,
[sheet1$a1:a" & LastRow &"] b
where a.組合<>b.組合
)
where 差=0
寫(xiě)法2：
- 1邓线、先對(duì)原始數(shù)據(jù)用Distinct去重復(fù)
- 2淌友、兩兩配對(duì)，篩選和3000的差等于0的。
為方便引用，用SQL server寫(xiě)了個(gè)CTE的,否則要寫(xiě)2個(gè)子查詢洼怔，感覺(jué)代碼好長(zhǎng)蜈出。
with CTE_Test as
(
select distinct * from dbo.Sheet1$
)
select c.* from
(
select a.組合 as 組合1 ,b.組合 as 組合2,(a.組合+b.組合-3000) as 差
from CTE_Test as a,CTE_Test as b
where a.組合<>b.組合
) c
where c.差 = 0
order by 組合1
EXCEL示例文件下載：
鏈接: http://pan.baidu.com/s/1pKXiivD 密碼: f2qc
