求助：Excel统计某电话号码有多少人打过

eos5000 · 发表于 2006-12-9 11:42

自己突发奇想：搞个Excel统计自己的手机有多少人打过的程序，不过能力有限，无法理解网上文章中的东西。特向DX请教：

引用：。。。。。派出所的一个朋友在调查一起案件的时候，遇到了一个如题的问题，请我帮忙。由于要保密他不能提供原始数据，只是给我谈了一下要求。目前他们通过一个嫌疑犯的通话记录，从通话记录中又列入了n个新嫌疑犯，而且也得到每个嫌疑犯的通话记录。现在就要将这些通话记录进行统计，即同一个电话号码，每个嫌疑犯打了多少次，有多少个嫌疑犯同时打过同一个号码。。。。。。。。

http://soft.yesky.com/office/266/2539266.shtml

不过，我不需要把单次出现的号码删除

自己搞了个样：请看

2ddnzjHM.rar (11.83 KB, 下载次数: 196)

[此贴子已经被作者于2006-12-9 12:01:11编辑过]

eos5000 · 发表于 2006-12-9 11:43

文章程序，如有dx加注释，可能我就能理解了

Private Sub CommandButton1_Click()
    Sheets(2).Rows(2 & ":" & 65536) = ""
    Sheets(2).Columns("B:IV") = ""
    Dim Ls, i, j, Isa, k, yhs
    Isa = False
    i = 2
    If Sheets(1).Cells(1, 2) = "" Then
        MsgBox "没有用户，无法统计！", vbOKOnly + vbCritical, "错误提示"
        Exit Sub
    Else
        Do While True
            If Sheets(1).Cells(1, i) <> "" Then
                Sheets(2).Cells(1, i) = Sheets(1).Cells(1, i)
                i = i + 1
            Else
                Exit Do
            End If
        Loop
        yhs = i - 1
    End If

    Ls = 2
    Do While Sheets(1).Cells(1, Ls) <> ""
        i = 2
        Do While Sheets(1).Cells(i, Ls) <> ""
            If Sheets(2).Cells(2, 1) = "" Then
                Sheets(2).Cells(2, 1) = Sheets(1).Cells(i, Ls)
            Else
                j = 2: Isa = False
                Do While Sheets(2).Cells(j, 1) <> ""
                    If Sheets(2).Cells(j, 1) = Sheets(1).Cells(i, Ls) Then Isa = True: Exit Do
                    j = j + 1
                Loop
                If Not Isa Then Sheets(2).Cells(j, 1) = Sheets(1).Cells(i, Ls)
            End If
            i = i + 1
        Loop
        Ls = Ls + 1
    Loop

    Ls = 2
    Do While Sheets(2).Cells(1, Ls) <> ""
        i = 2
        Do While Sheets(2).Cells(i, 1) <> ""
            j = 2: k = 0
            Do While Sheets(1).Cells(j, Ls) <> ""
                If Sheets(2).Cells(i, 1) = Sheets(1).Cells(j, Ls) Then k = k + 1
                j = j + 1
            Loop
            If k <> 0 Then Sheets(2).Cells(i, Ls) = k
            i = i + 1
        Loop
        Ls = Ls + 1
    Loop

    '===========================================
    ' 删除非同一电话多个用户使用的行
    '===========================================
    i = 2
    Do While Sheets(2).Cells(i, 1) <> ""
        j = 2: k = 0
        Do While j <= yhs
            If Sheets(2).Cells(i, j) <> "" Then k = k + 1
            j = j + 1
        Loop
        If CInt(k) < 2 Then
            Sheets(2).Rows(i).Delete Shift:=xlUp '删除i行
        Else
            i = i + 1
        End If
    Loop
    '===========================================
    MsgBox "统计完毕！", vbOKOnly + vbInformation, "系统提示"
    Sheets(2).Select
End Sub

[此贴子已经被作者于2006-12-9 11:47:01编辑过]

山菊花 · 发表于 2006-12-9 12:05

提供一个函数方法：

菜单：插入—名称—定义：

原始数据：

号码：hm =OFFSET(原始数据!$B$2,,,COUNT(原始数据!$B:$B))

类型：lx =OFFSET(hm,,-1)

时长：sc =OFFSET(hm,,1)

不重复的号码筛选结果：

x =IF(FREQUENCY(hm,hm),hm)

5V9uqByn.rar (14.8 KB, 下载次数: 121)

[此贴子已经被作者于2006-12-9 18:30:28编辑过]

eos5000 · 发表于 2006-12-9 13:18

回复：（山菊花）

我操作了一下，谢谢

还有个问题，如何让统计后》》呼入次数最多的，自动排列在前面？（我用排序后，它又自动恢复原样了

[此贴子已经被作者于2006-12-9 13:52:27编辑过]

爱歌学习 · 发表于 2006-12-9 14:18

复制－选择性粘贴－数值（去掉公式），然后再排序。

eos5000 · 发表于 2006-12-9 19:57

回复：（爱歌学习:

我希望是自动的，不过还是谢谢你

eos5000 · 发表于 2006-12-9 23:07

ding!!!!!!!!!!

northwolves · 发表于 2006-12-9 23:47

ADO+SQL：

Private Sub CommandButton1_Click()
With CreateObject("ADODB.connection")
.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;hdr=no;';Data Source=" & ActiveWorkbook.FullName
[a3:C65536].ClearContents
[a3].CopyFromRecordset .Execute("select F1,COUNT(F1) from [原始数据$B2:B65536] GROUP BY F1 ORDER BY COUNT(F1) DESC")
[a3].End(xlDown).Offset(2, 0).CopyFromRecordset .Execute("select F1,COUNT(F1) from [原始数据$C2:C65536] GROUP BY F1 ORDER BY COUNT(F1) DESC,F1 DESC")
.Close
End With
End Sub

爱歌学习 · 发表于 2006-12-10 01:08

Sub total()
    Application.ScreenUpdating = False
    With CreateObject("ADODB.connection")
        .Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & ActiveWorkbook.FullName
        [a4:d65536].ClearContents
        Sql = "transform count(对方号码) select 对方号码,sum([时长(秒)]) from [原始数据$] group by 对方号码 pivot 通话类型 "
        [a4].CopyFromRecordset .Execute(Sql)
        .Close
    End With
    Range("A3:E" & [a65535].End(xlUp).Row).Sort Key1:=Range("D3"), Order1:=xlDescending
    Application.ScreenUpdating = True
End Sub

g5jAYX0u.rar (12.22 KB, 下载次数: 243)

[此贴子已经被作者于2006-12-24 18:17:14编辑过]

eos5000 · 发表于 2006-12-10 02:29

回复 northwolves
谢谢你,
回复：（爱歌学习:northwolves

谢谢你,又有问题了：

呼入次数	呼出次数
1900-2-3 0:00	1900-2-7 0:00
1900-2-19 0:00	1900-2-4 0:00
	1900-1-15 0:00

统计得的结果是时间（我知道它的值是次数），我想自己改一下，无奈水平低下，不知道是哪里定义了它是时间，感谢您的指点。

[此贴子已经被作者于2006-12-10 2:33:05编辑过]

		自动登录	找回密码
密码			免费注册