Work often encounters a variety of data classification and summary problems, in Excel we can easily solve these problems with shortcut keys, subtotal menus, pivot tables, functions, formulas and even VBA. Target task: A summary of the production of various products by group. Implementation method: first sort the data table by group, put the data of the same group together, insert blank lines under the same group; then, hold down the Ctrl key to select each subtotal cell, and press “Alt” The key and the “=” key will count the output of various products. (Figure 1) Tip: (1) Do not have empty cells, or the subtotal column will calculate the sum will be suspended in the empty cell; (2) Do not have a formula, or the subtotal will only calculate the cell with the summation formula. Menu summary method Target task: Monthly sales of three major electrical appliances (TV, refrigerator, washing machine) in a shopping mall. Case analysis: Since the classified items are only a single “month”, the statistics on the sales of the three major appliances can be easily completed with the menu of “sorting”. Implementation method: First, first sort the data by the “month” field, the purpose is to put the data of the same month together; then, select the required data, select “Data & Rarr; Subtotal” menu, pop up In the window, select the category field “month”, the summary method selects "summary", select the summary item as "amount"; and check the following three options, and then form a press The distribution of the subtotals for the month distribution is printed. (Figure 2) Tip: You can click the 1, 2, 3 button at the top left of the subtotal to hide or display the specific month data. Pivot Table Summary Method Objective Task: Calculate the sales of three major electrical appliances (television, refrigerator, washing machine) in a shopping mall by month and item category. Case analysis: Since the classified project is no longer just a single “month”, this field needs to use the “sort and summarize” menu to make the data table a bit messy and not very beautiful. So for the statistics of multiple classification fields, you can easily use the "PivotTable". Implementation: After selecting the required data area, click on the “Data & Rarr; PivotTable and PivotChart” menu, and click on the “Done” button in the pop-up window; then, in the new worksheet “ In the PivotTable Field List, drag the corresponding field to the corresponding position of the PivotTable according to the style of the required summary table. For example, drag 'month” as the row field to the corresponding position, and “Items” will be “Items” Drag the column field to the appropriate location and drag the "Amount" as a data item to the appropriate location. (Fig. 3) Function summary method Target task: Calculate the purchase volume of the air conditioner and the total purchase amount of the air conditioner of a certain store on a monthly basis. Case analysis: The purchase time in this data table is specific to the date. If you use the subtotal menu or PivotTable, you can't count the purchases by month, so you can use the SUMPRODUCT function plus the wildcard to complete the statistics of such data. Implementation method: In the cell corresponding to the “number” field in the statistical summary table, enter I==SUMPRODUCT((ISNUMBER(FIND(G3,A$3:A$14)*(FIND(H3,B$3)) :B$14)))*C$3:C$14)), and drag down to fill, correspondingly enter in J3 cell: =SUMPRODUCT((ISNUMBER(FIND(G3,A$3:A$14)*(FIND (H3, B$3: B$14))) *D$3: D$14)), also fill down. (Figure 4) Tip: SUMPRODUCT is a multi-conditional statistical function, and since it can't use wildcards, it needs to be used with FIND and ISNUMBER to implement fuzzy query in cells. Formula Summary Method Objective Task: Record the time and details of each sale in the factory in the sales record table. It is required to be implemented in the business assessment form. After entering the “start date” and “end date”, the form automatically submits relevant data from the sales record table and summarizes it to get each salesperson during this time. Total sales and bonuses. Implementation method: first switch to the business sales assessment form, enter the formula in the B5 cell: = SUM (IF ((sales record! A3: A8> = business assessment! B2) * (sales record! A3: A8 < = business assessment !D2)*(sales record!G3:G8=business assessment!B3), sales record!F3:F8,0)), after the formula input is completed, you can't click the mouse, you can't do anything else, just press Ctrl+Shift +Enter, the formula entered at this time will automatically be added on both sides “{}”. Please note: braces must be generated by the system itself, invalid by self-input. Similarly, enter the formula in the D5 cell: = SUM (IF ((sales record! A3: A8> = business assessment! B2) * (sales record! A3: A8 < = business assessment! D2) * (sales record! G3: G8 = business assessment! D3), sales record! F3: F8, 0)), the formula for the sales commission is equally available (Figure 5). Tip: In the above formula, SUM is the sum and IF is the condition. The entire formula is calculated in the sales record tables A3 to A8 to satisfy the following three conditions: 1 is the date from the start date (B2 in the business assessment table); 2 is the end date (D2 in the business assessment table) ; 3 is the same as the name of the salesperson in the table. VBA statistical summary method target task: in the thousand data, the product output is classified by group. Implementation method: First, select “Tools & Rarr; Macro & Rarr; Visual Basic Editor & rdquo; menu, in the pop-up window, select the "Insert & Rarr; Module" menu, and enter the corresponding code in the code editing area; then, will Place the cursor at the name of the process, click the “Run Sub-Process/User Form” button, and the data will be counted according to the group in the data table. (Figure 6) Tip: In order for VBA to work properly, you also need to select the “Tools & Rarr; Macro & Rarr; Security” menu to set the security of the macro to “Low”. This article comes from [System Home] www.xp85.com