When inputting data, if Excel can remind you of right or wrong, it is much more troublesome than waiting for the summary to go wrong and then returning to find the error. This depends on adding an automated review to Excel. Many well-designed Excel worksheets are often provided for more than one person to operate and enter. Because the user is familiar with the content of the cells in the table, it is inevitable that such input errors will occur. By default, the Excel system does not have the right or wrong recognition function for the data entered by the user. If we can install a pair of Excel's "eyes", let it automatically recognize the legality of the data entered by the user, the error will be minimized. This article will describe how to automate the review of data through some practical operational cases. Checking ID card input for ID card digits is often encountered in Excel spreadsheet processing and is also prone to errors. In actual work, we often see that one person's ID card is missing one or more. It is sometimes difficult to judge which one has a problem. If the system can remind you when entering, such an error will not happen. The ID number can only be 15 and 18, we can set Excel to have this ability to review. If the Excel worksheet column B enters the ID number, B2 is the starting cell of the ID number. You can select all of the columns and click the “validity" command under the “Data" menu. Open the “Settings' tab (Figure 1), select “Customize" in the "Allow" drop-down list, then enter “=AND(COUNTIF(B:B) in the "Formula" , B2) = 1, OR (LEN (B2) = 15, LEN (B2) = 18)) & rdquo;. This formula checks the number of cells in column B that are identical to the current data. If the returned result is equal to 1, then “=COUNTIF(B:B,B2)=1” returns the result TRUE, otherwise returns FALSE. Next, the LEN function in “=OR(LEN(B2)=15, LEN(B2)=18)” checks whether the data length of the input B column B2, B3, etc. is equal to 15 or 18, as long as three LEN One of the functions satisfies the condition and the OR function returns TRUE. When the COUNTIF and OR functions return TRUE at the same time, the AND function returns TRUE, and Excel allows the user to enter data. If the data in the cell is duplicated, or if the length of the input is not equal to 15 or 18, the AND function returns FALSE and Excel immediately suppresses it (Figure 2). Tip: From the example here, we can bypass the class. If the input column B is a fixed-length data (such as 8 bits), then just modify the above formula to “=AND(COUNTIF(B:B,B2) =1, LEN (B2) = 8)) & rdquo; Automatic Correction of Number Prefix Correction Sometimes we assign different area number prefixes to people or product information from different regions. In order to avoid errors in entering data that is not in the range of numbers when entering this type of data, you can set an automatic check for the prefix of the data. If the product number prefixes of the three regions are 012xxx, 017xxx, and 019xxx, you can select the column where the data is located as described above (assuming F1 is the starting cell of the input data), and then open the “Data Validity” dialog. The box's “Set” tab. Select “Customize" in the "Allow" drop-down list, then enter “=OR(LEFT(F1,3)="012",LEFT(F1,3) in the "Formula" ="017",LEFT(F1,3)="019")”. After the above functions are set, as long as the test number prefix entered is not 012, 017 or 019, an alert prompt is displayed immediately. The above formula works like this: LEFT takes three characters from the current cell (for example, F1) to see if they are equal to 012, 017, or 019. As long as one of the three LEFT function relationships holds, the OR function returns. TRUE, which allows the user to enter, otherwise the prompt is out of range immediately. If you enter a larger number of data prefixes, just add a statement like "LEFT(F1,3)="012"” in the formula. Data that is entered in the valid range is generally within a reasonable range, but the worksheet does not know if the data is overrun. For example, when entering a grade, it is required to input only data of a specified length, and the size cannot exceed a certain range. For example, only integers can be entered and less than or equal to 100. But if you enter a score system like 213, you won't think you are wrong. You can use the following methods to qualify the condition. Assuming D2 is the starting cell of the data input, you can click the “validity" command under the “Data" menu to open the “Settings' tab of the dialog box. Select “Customize" in the "Allow" drop-down list, then enter “=AND(D2<=100,OR(LEN(D2)=1, LEN(D2) in the "Formula" =2, LEN(D2)=3))”. The LEN function in the formula checks whether the data length of the cells of the input D column D2, D3, etc. is equal to 1, 2 or 3, and the OR function returns TRUE as long as any one of the conditions is satisfied. And "D2<=100” check whether the input data is less than or equal to 100. When both of the above are satisfied, the AND function returns TRUE, and Excel allows the user to complete the input. If the length of the data in the cell is not equal to 1, 2 or 3, or the input data is greater than 100, the AND function returns FALSE, and Excel stops the user from entering data. Automatically judging invalid records and suppressing the input of worksheets such as payroll tables, the existence value of the latter column of data is closely related to the main keywords. For example, if a record containing “Rooms " """""""""""" We can let the worksheet automatically make judgments about such records. Assume that the worksheet B column stores "work number", column C stores "name"; when a cell in column B is null, the cell to the right of it is not allowed to enter "name". Excel can take the following measures to stop it. Open the “Settings” tab of the “Data Validity” dialog box, select “Customize” in the “Allow” drop-down list, then enter “=COUNTA in the "Formula" box (B2)=1”. & ldquo; Determined & rdquo; If a cell in column B is empty, then the cell to the right of it can not input data, even if the input data Excel will come forward to stop, until the input of valid data in column B can be operated. Automatic reminder of input order error In some cases, we have strict requirements on the order of data entry, such as sorting by date, the previous tasks are not processed, the latter tasks are not allowed, and must be entered in order from small to large. Once the input violation occurs, the input should be stopped immediately. How to achieve? Select the area or column (such as column D) where the data will be entered, and then open the dialog as described above. Select “Date” in the "Allow" drop-down list, select “greater than or equal to “ in the "data" drop-down list, and finally enter “=MAX in the "start date" box $D$1: $D1)" (Figure 3). After closing the dialog box, if the data entered from the D1 cell is "ascending", Excel will not be alerted, otherwise the operation will be illegal. If the dates must be entered in descending order, simply select “ Less than or equal to “ in the “Data<; drop-down list and enter “==MINA($D in the "Start date" box $1:$D1)”(“$D$1” is the cell where the first data is located) (Figure 4). Repetitive data with color auto-alerts Sometimes, if duplicate data appears in the input of the same item, it may indicate that the input has an error. For example, the name of the recipient who is entering has already been entered, and the system can be reminded by the red display to remind the input. For example, if there is data duplication in column C, the user will be reminded in red. To do this, click on the “Conditions Format” menu under the “Format” menu to open the dialog box and select “Formula” in the “Condition 1” drop-down menu to enter the formula “ COUNTIF” in the dialog box. (C: C, C2) < > 1 & rdquo; (Figure 5). Then click the "format” button to open the dialog and select a suitable "reminder" method (for example, select a red font). After that, as long as the COUNTIF function finds the same data in the C column as the current cell, that is, “=COUNTIF(C:C,C2)<>1” is established (for “true”), then the current cell The data will be automatically displayed in red font. This article comes from [System Home] www.xp85.com