Workplace there is such a misunderstanding: the first day to work, the latest staff is the best staff. Is the so-called no credit is also hard work right? Diligent employees are more likely to be recognized by the leadership? After get off work overtime is more efficient? Is that true? not necessarily! Three years ago, A boss inadvertently saw the "Excel efficiency manual done early, do not work overtime" books, thumbs up, turned to other employees said: "see no, early do not work overtime, fast And hurry to finish home. A year ago, B boss saw the name of Luzi, also spit out the truth: "overtime really tired, if we are high efficiency, do not work overtime, how good! In fact, most bosses are concerned with the results, not the process. He confessed to the original thing 1 hour to complete, you can get 1 minute, he will be different to you, you have the opportunity to do more important things. Here are two real cases, say what is efficiency. 1. Count the number of new customers each month. The number of new customers and the number of customers is two different concepts, such as a customer (NXX Co., Ltd.) were in the , , Business, but the first time in January business, NXX Limited is considered a new customer, if the customer follow-up business, it is no longer counted as new customers. Under normal circumstances, we count the number of monthly sales of all customers or statistics every month the number of customers appear with: = SUMPRODUCT ((TEXT ($ B $ 2: $ B $ 1418, "m month") = E2) * 1) TEXT function is to change the date into the month, on the SUMPRODUCT function syntax see the article: "Do not be afraid, in fact SUMPRODUCT function is very simple! " This will cause problems. In the above example, NXX Co., Ltd. has business on January 11, January 12, February 1, respectively. The number of new customers per month is: 1 new customer in January and 0 new customers in February. But the results of the statistics will become: 2 in January, 1 in February. That is, the result is the number of occurrences of the customer or the number of customer transactions, rather than the number of new customers per month. In order to avoid this situation, the need to ascend the date, and then the same customer's first business marked out, the same customer under the other time the business occurred directly ignored. Add an auxiliary column to judge, use the formula: = COUNTIF ($ A $ 2: A2, A2) = 1 The role of the formula is the same date in the same customer occurred in the first business show TRUE, the other show FALSE, so the subsequent statistics will not go wrong. Final formula: = SUMPRODUCT ((TEXT ($ B $ 2: $ B $ 1418, "m month") = E2) * $ C $ 2: $ C $ 1418) More than 1,400 records, the formula is probably 1 minute, and if the manual statistics, it is estimated to 1 hour. Judgment for the first time, there is one case below. 2. The following is Big cow co., Ltd , A total of five customers, query the same customer the first invoice corresponding status. Note: If the same day out of the invoice, there is invalid, not. Obsolete: refers to the operator to fill in the invoice information entry Of the invoice has been issued for the waste, the tax plate shows the status of the invoice "void". In other words, the above query to meet two conditions: 1, the buyer name for the first time 2, the state is valid If the billing date for column E is not in ascending order, the ascending operation is required in advance. Step 01 Copy the buyer name to column I and delete the duplicate. In the case of Step 02 in the J2 input formula, press the Ctrl key to enter the three key to end. = VLOOKUP ("valid"
Comments
Post a Comment