The operation process of statistical data by time period in Excel

At work, we often need to perform statistical operations on data in Excel tables by time period in order to have a clearer understanding of data distribution. PHP editor Xiaoxin brings you the detailed operation process of statistical data by time period in Excel, including filtering, grouping, pivot tables and other methods to help you solve this problem.

First observe the existing data to see if the time is in a time format recognized by Excel. If it is left-aligned by default, it is likely to be in text format. Of course, set the cell format to [Normal] to see if the time is It becomes a decimal. If it becomes a decimal, it is in time format. If there is no change, it is in text format. As shown in the figure, this is the time in text format and needs to be processed first:

Select the column where the time is located, click] Data [Tab] Sort Columns [,

In the new dialog box that pops up, click directly] to complete [i.e. Yes (you can also set it step by step, but the system default is set to Normal in the third step, so you can click to complete it directly). The alignment has changed to left alignment, which is the time format recognized by the system, so that the following operations can be performed normally. Select any unit in the data area (the system will automatically select adjacent areas. If only part of the area is pivoted, be sure to select this part of the area), click ] to insert [, [Pivot Table],

Confirm whether the data area selection is correct, then click to place the pivot table in this worksheet, and select a cell to place the pivot table,

In the new interface on the right, drag the time to [Row Label] and the quantity to [Value].

At this time, select Data Pivot In any cell under the row label that has appeared in the table, click [Group selected content] in [Options],

In the pop-up [Group] dialog box, cancel Select the default [Month], select [Hour], the start time and end time will be automatically filled in by the system, or you can set it yourself according to your needs.

At this point, just use data pivot The table easily calculates the sum of the data in each hour, and the result is as shown in the figure:

The above is the detailed content of The operation process of statistical data by time period in Excel. For more information, please follow other related articles on the PHP Chinese website!