我正在努力组合两个 excel 工作表。在我开始之前,我想提一下我也有mysql工作台,所以我愿意在sql或vba中解决这个问题(我应该同时学习)。我正在使用.bed文件,它们是基因组坐标列表。简而言之,数据按染色体编号(即:chr2)索引,然后在染色体上具有数字开始和停止位置。这些数字位置可以跨越很大的范围(即:100-10,000)或单个位置(即:999-1000)。我有一个覆盖大范围的坐标列表,在一个单独的文件中,我有一个单个位置的列表。
包含范围的文件示例:
chromosome start stop
chr1 4561 6321
chr3 9842 11253
具有单个位置的文件示例:
chromosome start stop
chr1 5213 5214
chr3 10254 10255
我想合并这些工作表,以便在我的单个位置列表中找到一个位置在我的范围列表中,则两者的位置将在同一行中列出。列表有 1000 个位置长,所以我也希望这个程序遍历每一行。使用上面列出的示例数据,我希望我的输出如下所示:
所需输出示例:
chromosome start stop chromosome start stop
chr1 4561 6321 chr1 5213 5214
chr3 9842 11253 chr3 10254 10255
多个单个仓位很有可能落在一个范围内,我希望这些仓位被列为单独的行。
我很感激我能得到的任何帮助!提前谢谢你。我渴望学习!
这是一个基本大纲,它查询名为"范围"和"位置"的工作表上的两个表,并在名为"结果"的工作表上输出结果
输入表应具有标题,并从左上角单元格 (A1) 开始
Sub SqlJoin()
Dim oConn As New ADODB.Connection
Dim oRS As New ADODB.Recordset
Dim sPath
Dim sSQL As String, wb As Workbook
Set wb = ThisWorkbook
sSQL = " select a.chromosome, a.start, a stop," & _
" b.chromosome, b.start, b.stop " & _
" from <ranges_table> a, <positions_table> b" & _
" where b.start >= a.start and b.stop <= a.stop"
sSQL = Replace(sSQL, "<ranges_table>", _
Rangename(wb.Worksheets("Ranges").Range("A1").CurrentRegion))
sSQL = Replace(sSQL, "<positions_table>", _
Rangename(wb.Worksheets("Positions").Range("A1").CurrentRegion))
If wb.Path <> "" Then
sPath = wb.FullName
Else
MsgBox "The workbook must be saved first!"
Exit Sub
End If
oConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & sPath & "';" & _
"Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';"
oRS.Open sSQL, oConn
If Not oRS.EOF Then
wb.Worksheets("Results").Range("A2").CopyFromRecordset oRS
Else
MsgBox "No records found"
End If
oRS.Close
oConn.Close
End Sub
Function Rangename(r As Range) As String
Rangename = "[" & r.Parent.Name & "$" & _
r.Address(False, False) & "]"
End Function