Excel statistics QQ Chat record bar number

The QQ record from QQ to export to TXT file, and then import the Excel table, you will find the message record format:

2012-06-23 10:58:34 Xiao Ming

How are you doing!

2012-06-23 10:58:34 Little Red

How are you doing!

The message record is displayed in column A of the Excel table,

Then, list the dates of all statistics listed in column B, and the names of the people to be counted in column C, for example, column C counts the number of messages that xiaoming sends each day, and column D counts the number of red days

Use formulas in column C

For example: C2 = COUNTIF (a:a, "2012-06-23* xiaoming") C3 = COUNTIF (A:a,text (B3, "Yyyy-mm-dd") & "* xiaoming")

The B3 content is the 2012-6-24 format of the time class variable, "*" function is "2012-06-23" and "xiaoming" but add any character, COUNTIF is the statistics meet if condition record sum.

D2 = COUNTIF (a:a, "2012-06-23* Little Red") C3 = COUNTIF (A:a,text (B3, "Yyyy-mm-dd") & "* Little Red")

Statistics, the number of words that each person sends messages per day

The idea of designing the statistical algorithm is to determine the starting line number of the message sent in column A each day in column A, and the total number of rows that the message takes up, including the blank cell row, to make it easier to determine what to look for later, and then, based on the range of daily messages in column A, In the scope of the progressive traversal, find a user's speaking time period, count the number of characters included in the paragraph below, plus all the number of speech segments found add, get the user that day the total number of characters to speak (of course, the voice of the picture is just as a character record).

Public Function Calculatewords (ByVal SS as String, ByVal start as Long, ByVal J as Long) as Long        Dim I, K, result as L Ong    k = 0    result = 0 for    i = start-to         -J If Cells (I, 1) like SS-then            -k = i + 1            while Len (Cells (k, 1 ) <> 0                result = result + Len (Cells (k, 1))                k = k + 1            wend         End If             Next    Quit:    Calculat Ewords = Resultend functionpublic Function totalrows (ByVal start as Long, ByVal T as Date) as Long   Dim I, J, K, Resul  T as Long    k = 0    result = 0    j = Sheet1.UsedRange.Rows.Count for       i = start to J         If Cells (i, 1) like "20?? -?? -?? ??:??:?? * then            Dim T1            as Date t1 = left (Cells (i, 1), ten)            If T1 > t then                GoTo Quit            end If         End if< C26/>next    Quit:    totalrows = Iend Function

function usage instructions, for example,

Determine the message record for the day of 2012-06-23, the number of rows in the table A column, and the E-List of index results

E2 = totalrows (1, B2) E3 = Totalrows (E2, B3)

We use F to save Xiaoming's message character number

F2 = Calculatewords (TEXT (B2, "Yyyy-mm-dd") & "* xiaoming", 1, e2-1) that is, 2012-06-23, the total number of messages sent by xiaoming

F3 = Calculatewords (TEXT (B3, "Yyyy-mm-dd") & "* xiaoming", E2, E3-1) that is, 2012-06-24, the total number of messages that Xiaoming sent

Finally, the results of the statistics can be plotted in the form of graphs.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Excel statistics QQ Chat record bar number