1. Find the position corresponding to the same value in two columns of data
=MATCH(B1,A:A,0)
2. Know the formula to get the result
define_name=EVALUATE(Sheet1!C1)
Formula with known result
Define Name=GET.CELL(6,Sheet1!C1)
3. Force a newline
with Alt+Enter
4. More than 15 digits input
Too many people have asked this question, so let's put it away. 1. The cell is set to text; 2. Enter ' before entering the number
5. If column B is hidden, how can it be displayed?
Select columns A to C, right-click and unhide
Select columns A to C, double-click to select any column width line or change any column width
Move the mouse between columns AC and drag it when the mouse becomes a double vertical bar.
6. Swap ranks in EXCEL
Copy, Paste Special, select Transpose, OK
7. How is Excel encrypted?
(1), you can save as >> "Tools" >> General >> Settings in the upper right corner
(2), Tools >> Options >> Security
8. About COUNTIF
The COUNTIF function can only have one condition, such as greater than 90, it is =COUNTIF(A1:A10,">=90")
Subtract between 80 and 90 is =COUNTIF(A1:A10,">80")-COUNTIF(A1:A10,">90")
9. Extract date of birth based on ID number
(1), =IF(LEN(A1)=18,DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),IF(LEN(A1) =15,DATE(MID(A1,7,2),MID(A1,9,2),MID(A1,11,2)),"wrong ID number"))
(2), =TEXT(MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")*1
10. Want to fully reference the data input by SHEET1 in SHEET2
Workgroup, hold down the Shift or Ctrl key while selecting Sheet1, Sheet2
11. Do not enter duplicate numbers in a column
[Data]--[Validity]--[Custom]--[Formula]
input=COUNTIF(A:A,A1)=1
If you want to find repeated numbers
Conditional Formatting "Formula" =COUNTIF(A:A,A5)>1"The format is selected in red
12. When opening a spreadsheet file directly, it cannot be opened
Find the .XLS file in "Folder Options" - "File Type", and confirm whether there is a parameter 1% in "Advanced", if not, please add it manually
13. Implementation of Excel drop-down menu
[data]-[validity]-[sequence]
14. 10 columns of data are combined into one column
=SUM(OFFSET($A$1,(ROW()-2)*10+1,,10,1))
15. Find two data formulas (basic lookup functions are VLOOKUP, MATCH)
(1), find the corresponding result according to the two conditions of the row and column
=VLOOKUP(H1, A1: E7, MATCH(I1, A1: E1, 0), FALSE)
(2), find the corresponding result according to the data matching two columns (for the array formula)
=INDEX(C1:C7, MATCH(H1&I1,A1:A7&B1:B7,0))
16. How to hide 0 in cells
Cell format custom 0;-0;;@ or Options>View>Zero value to tick. Oh, if you use a formula, it depends on the situation.
17. Combined calculation of cells in multiple worksheets
=Sheet1!D4+Sheet2!D4+Sheet3!D4, better =SUM(Sheet1:Sheet3!D4)
18. Get the worksheet name
(1), the definition name: Name
=GET.DOCUMENT(88)
(2), the definition name: Path
=GET.DOCUMENT(2)
(3), enter =CELL("filename") in A1 to get the path-level file name
Type in the cell where you want the filename
=MID(A1,FIND("*",SUBSTITUTE(A1,"","*",LEN(A1)-LEN(SUBSTITUTE(A1,"",""))))+1,LEN(A1))
(4), custom function
Public Function name()
Dim filename As String
filename = ActiveWorkbook.name
name = filename
End Function
19. How to get the maximum number of days in a month
: "=DAY(DATE(2002,3,1)-1)" or "=DAY(B1-1)", B1 is "2001-03-01"
The sum of the items in the data area containing a certain character, what formula should I use
=sumif(a:a,"*"&"a character"&"*",data area)
The last line of text:
=offset($b$1,MATCH(CHAR(65535),b:b)-1,)
The last row is a number:
=offset($b$1,MATCH(9.9999E+307,b:b)-1,)
Or: =lookup(2,1/(b1:b1000<>""),b1:b1000)
In the judges' scoring, how to remove more than two highest scores and two or more lowest scores, and find the average score of the remaining staff? At the same time, the removed scores are displayed.
See trimmean() function for help.
Dropped fractions:
Maximum two: =large(data,{1;2})
Minimum two: =small(data,{1;2})
How to easily determine the last digit is a letter
right(a1)*1
wrong letter
=IF(ISNUMBER(--RIGHT(A1,1)),"Number","Letter")
=IF(ISERR(RIGHT(A1)*1),"Letter","Number")
How to set a cell to not accept characters containing spaces
Select column A
data - validity - custom - formula
=iserror(find(" ",a1))
data--validity--custom--formula
=len(a1)=len(trim(a1))
The original function is =a1+a4+a7+a10+a13+a16+a19+a22.....
Now it is =sum(n(offset(a1,(row(1:10)-1)*3,)))
I refer to data in other worksheets in one worksheet, but the referenced worksheet is not fixed. According to the worksheet name I enter, the data in the corresponding worksheet is automatically selected. How can I quote it in the formula?
=INDIRECT("A1"&"!"&"E1") A1 is the worksheet name
Sum of odd rows = SUMPRODUCT((A1:A1000)*MOD(ROW(A1:A1000),2))
Summation of even rows =SUMPRODUCT((A1:A1000)*NOT(MOD(ROW(A1:A1000),2)))
View string word count
=LEN(A1)
Find the number of non-empty cells
The data calculated by the formula, COUNTA cannot be used (otherwise the null characters are also calculated)
=COUNTIF($E$3:$E$65536,"?*")
A dynamic sum formula that sums from cell A1 in column A to the cells in the row before the current row.
=SUM(INDIRECT("A1:A"&ROW()-1))
20. A relatively easy-to-use EXCEL document repair tool
ExcelRecovery
21. EXCEL square root operation
To raise 8 to the third power, you can use this formula, enter =8^(1/3) in the cell
22. The data in the cells is scattered and aligned
Text Format > Full-width Input
23. Find links in worksheets
Ctrl+~ or Edit >> link
24. How to automatically fill empty cells with 0
Select the area to be changed > Find > Empty > Replace > 0
25. Convert numbers in Word to Excel
There are many methods, select "Copy" Set Input Cell as Text "Paste Special" Value
Select "Convert Table to Text" Paste "Columns" and set the column options to text
Save as text file, open text file in EXCEL, and set the corresponding settings in the import text dialog box
from the web
Baidu search time is the earliest http://www.szit.com.cn/news/?232654.html
---END---