Http://blog.tajyw.com/user1/yehuileng/archives/2008/3582.html
1. Evaluate the number of times a character in a string appears: For example, find the number of occurrences of the character "A" in cell A1: = Len (A1)-len (substitute (A1, "","")) 2. How to copy a macro between different workbooks: 1. Open the work book containing macros, click "tool/macro (m )...", Select your macro and click "edit" to bring up the VB Editor interface. 2. Click "file/export file" and enter a file name (the default file name is also available) in the "file name" box. Note that the extension is ". Bas" and click "save ". 3. Set the extension to ". copy the bas file to another computer, open the Excel file, click "tool/macro/vB Editor", bring up the VB Editor interface, and click "file/import File ", find the file you copied, click "open", and exit the VB editor. Your macro has been copied. 3. How to Set cell editing permissions in Excel (protect some cells) 1. Select All cells, and click "format"-> "cell"-> "Protect" to cancel "√" before "Lock ". 2. Select the cell you want to protect, click "format"-> "cell"-> "protection", and tick "√" before "Lock ". 3. Click "tool"-> "protection"-> "protect worksheet", enter the password twice, and click "OK" twice. 4. In Excel, when a cell meets certain conditions, how does one display a specific color in another cell, such as A1> 1? When C1 displays red 0 <A1 <1, when C1 shows green A1 <0, C1 displays yellow as follows: 1. Click cell C1, click "format"> "condition format", and set condition 1: formula = A1 = 1 2. Click "format"> "font"> "color". Click red and click "OK ". Condition 2 is set to: formula = and (A1> 0, A1 <1) 3. Click "format"> "font"> "color ", click green and click "OK ". Condition 3 is set to formula = A1 <0: "format"-> "font"-> "color". Click yellow and click "OK ". 4. After the three conditions are set, click "OK. 5. How to control the data length of each column in Excel and avoid repeated Input 1. Define the data length with data validity. Select the data range you want to enter, click "data"-> "validity"-> "set ", set "validity condition" to "allow" "text length" "to" 5 "(the specific condition can be changed according to your needs ). You can also define the prompt information, error warning information, and whether to enable the Chinese input method. Then click "OK ". 2. Avoid duplication in the condition format. Select column A, click "format"-> "condition format", and set the condition to "formula = countif ($ A: $ A, $ A1)> 1 ", click "format"-> "font"-> "color", select red and click "OK" twice ". After this setting, if the length of the input data is incorrect, a message will be displayed. If the data is duplicated, the font will change to red. 6. How to identify the differences between column B and column A in Excel? (1) If the data of the same row in column A and column B is required to be compared: assuming that the first row is in the header, click cell A2 and click "format"> "condition format ", set the condition to "cell value" "not equal to" = b2 point "format"-> "font"-> "color", select red, and click "OK" twice ". Copy the conditional format of cell A2 downward with a format brush. Column B can be set using this method. (2) overall comparison between column A and column B (that is, the same data is not in the same row): Assuming the first row header, click cell A2, click "format"-> "condition format" to set the condition to "formula" = countif ($ B: $ B, $ A2) = 0: "format"-> "font"-> "color", select red, and click "OK" twice ". Copy the conditional format of cell A2 downward with a format brush. Column B can be set using this method. After the preceding settings, all data in column AB is not colored, and columns A have Column B or B has column A without data marked as red. 7. Click the create drop-down list button in Excel to select the cell you want to set the drop-down list, Click "data"-> "validity"-> "Settings", select "sequence" under "allow", and enter your drop-down list content in the "Source" box, separate items with commas (,), for example, A, B, C, and D. Select "provide drop-down headers" and click "OK ". 8. Convert Arabic numerals to uppercase (recently collected). Assume that you want to enter the aratabase number in A1, and convert B1 to a Chinese uppercase value (including the dollar points ), enter the following formula in cell B1: = substitute (if (A1>-0.5%, "negative ") & text (INT (fixed (ABS (A1), "[dbnum2]") & text (right (fixed (A1), 2 ), "[dbnum2] RMB 0: 0; RMB" & if (ABS (A1)> 1%, "whole",), "zero angle ", if (ABS (A1) <1, "zero"), "zero yuan",), "zero point", "whole ") 9. In Excel, how can I batch process rows in order? If there is a large amount of data (numeric values), how can I sort each row in ascending order? Because both sort by row and sort by column can only have one primary key word, the primary key word can be sorted by the secondary key word at the same time. Therefore, this problem cannot be solved by sorting. The solution is as follows:
1. Assume that your data is in column A to column E. Enter the formula in cell F1:
= Large ($ A1: $ E1, column (A1 ))
Use the fill handle to copy the formula to the right to the corresponding range.
Your original data will appear in the column F to column J in ascending order of rows. If necessary, you can copy "selective paste/value" to other places.
Note: The formula in step 1 can be modified based on your actual situation (data range. To sort data in ascending order, change the formula to: = small ($ A1: $ E1, column (A1 )) 10. Use function combinations to calculate multiple conditions: In the first row header, column A is "name", column B is "class", column C is "Chinese score", and column D is "admission result ", count the number of students whose "class" is "2", whose "Chinese score" is greater than or equal to 104, and whose "admission result" is "DUPLICATE. The statistical results are stored in other columns of this worksheet. Formula: = sum (if (B2: b9999 = "2") * (C2: c9999> = 104) * (D2: d9999 = "chongben )) after entering the formula, press Ctrl + Shift + enter to automatically add the array formula symbol "{}". 11. how to adjust the text row spacing in a cell in Excel. When a cell contains a large amount of text, many people feel that it is difficult to adjust its row spacing according to their own requirements. This section describes how to adjust the row spacing of text in a cell: Right-click the cell and click "set cell format"-> "alignment ", select horizontal alignment> left, vertical alignment> distributed alignment, automatic line feed, and click OK ". You can use the mouse to adjust the line height to the appropriate height based on the line spacing you requested. Note: The Green content is the key point. Many people cannot adjust the line spacing due to this incorrect setting. 12. How to reference the current worksheet name in Excel. If your workbook has been saved, you can obtain the worksheet name of the cell by using the following formula:
= Right (cell ("FILENAME"), Len (cell ("FILENAME")-find ("]", cell ("FILENAME "))) 13. The sum of multiple worksheets in the same format is assumed that the same workbook has a total of 100 worksheets in the same format from sheet1 to sheet100, and the result is placed in the sheet101 worksheet, enter = sum in cell A1 of sheet101 (click the sheet1 tag, press shift, click the sheet100 tag, click cell A1, and then enter :) the formula looks as follows: = sum ('sheet1: sheet100 '! A1) Press enter and the formula becomes = sum (sheet1: sheet100! A1) So, the simplest and quickest way is to directly enter the formula: = sum ('sheet1: sheet100' in cell A1 of sheet101 '! A1) then press Enter. 14. How can I determine whether a cell contains specified text? If cell A1 is determined to have "specified text", any of the following formulas can be used:
= If (countif (A1, "*" & "specify text" & "*") = 1, "yes", "no ")
= If (iserror (find ("specified text", A1, 1), "NONE", "yes ") 15. How do I replace the wildcard "?" In Excel? And "*"? When searching and replacing in execl ,? Represents any single character. * represents any number of characters. If you want to add "? If you replace "and" * "with other characters, you can only enter ~~ in the search box ~?~ And ~ *~ Can be replaced correctly. In addition, if you want to replace ~ In the search box, enter ~~ Only. 16. Two Methods for ranking in Excel: (1) using the rank () function:
Assume that column E is the result column and column F is the rank column. The formula for the F2 cell is as follows:
= Rank (E2, E: E)
In this way, the scores are the same, and the subsequent rankings will be vacant.
For example, if two people score 99 and 2nd are in parallel, 3rd are vacant, followed by 4th. (2) sort by formulas (Chinese ranking ):
If the score is in column E, enter the formula in column F2:
= Sum (if (E $2: E $1000> E2, 1/countif (E $2: E $1000, e $2: E $1000) + 1
The formula ends with Ctrl + Shift + enter.
The second method also has the same scores, but the subsequent ranking will not be vacant. 17. What is relative reference, absolute reference, and hybrid reference of cells? Relative reference, absolute reference, and hybrid Reference refer to how the address changes when the formula is copied to the side when the address of the cell or cell area is used in the formula.
Example: 1. relative reference: The address changes when the formula is copied. For example, cell C1 has a formula: = A1 + B1.
When the formula is copied to cell C2, it becomes: = a2 + B2.
When the formula is copied to cell D1, it becomes: = b1 + C1.
2. Absolute reference. When the formula is copied, the address does not change. For example, cell C1 has a formula: = $ A $1 + $ B $1.
When the formula is copied to cell C2, it is still: =$ a $1 + $ B $1
When the formula is copied to cell D1, the formula is still: = $ A $1 + $ B $1
3. Hybrid reference. When the formula is copied, part of the address content changes. For example, cell C1 has a formula: = $ A1 + B $1.
When the formula is copied to cell C2, it becomes: = $ A2 + B $1.
When the formula is copied to cell D1, it becomes: = $ A1 + C $1.
Rule: the column mark and row number with the absolute address "$" are absolute addresses, which will not change when the formula is copied to the side, if the column mark and row number without the absolute address symbol are relative addresses, the formula will change as it is copied to the side. Some addresses are changed when hybrid references are made. Note: Both the workbook and the worksheet are absolutely referenced, and there is no relative reference. Tip: after entering the cell address, you can press F4 to switch the "absolute reference", "hybrid reference", and "relative reference" statuses. 18. Calculate the number of non-duplicated data in a region. For example, calculate the number of non-duplicated data in the range of a100. If a number is repeated multiple times, only one data is counted. There are two calculation methods: one is to use the array formula: = sum (1/countif (A1: a100, A1: a100), enter the formula, and press Ctrl + Shift + enter, add the array formula symbol "{}" automatically "{}". The second is to use the product summation function: = sumproduct (1/countif (A1: a100, A1: a100). 19. How can I dynamically reference the last cell of a column in Excel? Reference the value in the last cell in column A of table sheet1 in cell A1 of table sheet2 (the value of the last cell in column A of table sheet1 is uncertain and the number of rows will be increased at any time ): = offset (sheet1! A1, counta (sheet1! A:)
Or:
= Indirect ("sheet1! A "& counta (sheet1! A: ))
Note: Make sure that no space exists in column A of sheet1. 20. How to create thousands of worksheets in a workbook right-click a worksheet tag, click Insert, select worksheet, and click OK ", press and hold the Alt + enter key. If you want to hold down the number of worksheets for a long time, you will see that the number of worksheets is constantly increasing and there are no problems with thousands of worksheets. 21. How to know the number of worksheets in a workbook? Method 1:
Click "tool"-> "macro"-> "VB Editor"-> "insert"-> "module" and enter the following content:
Sub sheetcount ()
Dim num as integer
Num = thisworkbook. Sheets. Count
Sheets (1). Select
Cells (1, 1) = num
End sub
Run this macro. The number in cell A1 of the first (leftmost) worksheet is the number of sheet. Method 2:
Press Ctrl + F3 (or click "insert"-> "name"-> "Definition") to open the "definition name" dialog box.
Define an X
"Reference location" input: = Get. Workbook (4)
Click "OK ".
Then you input = x in any cell.
The result is the number of sheet. 22. How to quickly organize a directory worksheet in a workbook
1. Use macro 3.0 to retrieve the name of each worksheet. Method:
CTRL + F3 displayed custom name dialog box, named X, enter: = mid (get. workbook (1), find ("]", get. workbook (1) + 1,100)
OK 2. Use the hyperlink function to insert connections in batches. Method:
Enter the formula = hyperlink ("# '" & Index (x, row () & "' in the A2 cell of the Directory Worksheet (generally the first sheet ())&"'! A1 ", index (x, row ()))
Fill down the formula until an error occurs, and the directory is generated. |
