P1 getting started with Excel
P2 table design & automatic summation function
Application of P3 frozen table column & split window
P4 data sorting
P5 data screening
P6 format as table & cross analysis filter table
P7 set the conditions for formatting the table
P8 worksheet setting & consolidated calculation of multiple worksheets
P9 chart making
P10 chart making
P11 PivotTable application
P12 print page setting
P13 design of page header and footer & add watermark to the table
P14 function introduction
Sum =sum(C3:C6) average =avg(C3:C6) maximum =max(C3:C6) minimum =min(C3:C6) Second largest =large(C3:C6,2) Second small =small(C3:C6,2) Data scope array: C3:C6 Sequence k: 2
P15 logic function IF
Conditions (established, not established) if Function: =IF(C7>=60,"pass","fail,") Conditional format → Highlight cell rule → Equal to (manually enter the condition content) ifs Function: version above 2016 =IFS(C7>=90,"A",C7>=80,"B",C7<80,"C") Nest shape if skill: =IF(C7>=90,"A",IF(C7>=80,"B",IF(C7<80,"C"))) or =IF(C7>=90,"A",IF(C7>=80,"B","C")) =if(Conditions,Results A,if(Conditions,Results B,Results C)) Up to 64 levels of nesting
P16 VLOOKUP function & absolute reference setting
=VLOOKUP(C3,E2:J12,2,FALSE) =VLOOKUP(Lookup_value,Table_array,Col_index_num,Range_lookup) =VLOOKUP(Keywords,Data scope,Number of columns,Query mode False Precise comparison) ※ Fuzzy query TRUE Keyword should be an incremental sequence ※ The keyword should be the first column of the data range ※ The content of the grade sheet and grade sheet can be changed, but the position cannot be moved. You can choose to hide the content Excel Default relative reference Select the data range, F4 → Lock reference as absolute =VLOOKUP(J3,$M$2:$N$7,2,TRUE) =VLOOKUP($C$3,$E$2:$J$12,2,FALSE)
Error & ifp17 data validation function
=IFERROR(VLOOKUP($C$3,$E$2:$K$12,2,FALSE),"No one was found")
=IFERROR(VLOOKUP($C$3,$E$2:$K$12,6,FALSE),"")
Is the student number column blank{ yes → The name field remains blank@ @，no → Execute student number query }
=IF(C3="","",IFERROR(VLOOKUP($C$3,$E$2:$K$12,2,FALSE),"No one was found"))
If C3 If the field is blank, the field will remain blank. If it is no, the following query formula will be executed
Verification:
Data → Data validity → Setting (setting input data range: integer allowed, between 101-110)
Input information: (input information: Please enter student number)
Error warning (error message: don't mess around, okay?)
P18 countifs & sumifs function application
Count: =COUNT(D2:D14) ※ count() The function will directly ignore the Chinese and English cells and use instead COUNTA Function =COUNTIF(C2:C14,"Mobile phone") =COUNTIF(Data scope,Conditions) =COUNTIF(C2:C14,H9) ※ Text type data is enclosed in double quotation marks to prevent errors (conditions can be placed in cells) =COUNTIFS(B2:B14,F2,C2:C14,G2) =COUNTIFS(Scope 1,Condition 1,Scope 2,Condition 2) =COUNTIFS(C2:C14,G2,D2:D14,">1000") Data size judgment =COUNTIFS(C2:C14,G2,D2:D14,">"&H2) =COUNTIFS(C2:C14,G2,A2:A14,">=2018/6/1",A2:A14,"<=2018/6/30") =COUNTIFS(C2:C14,H2,D2:D14,H3) Card swiping times in a month =SUMIFS(E2:E14,C2:C14,H2,D2:D14,H3) Total consumption amount of a month =SUMIFS(Aggregate range,Scope 1,Condition 1,Scope 2,Condition 2) COUNT() Count COUNTA() Count'Non blank cell' & Chinese and English characters COUNTIF() Calculate'Meet the conditions'Number of cells COUNTIFS() Multiple conditions =MONTH() Intercept date month
P19 definition name & indirect function & drop-down menu setting
Cell definition name: Selected data → formula → Definition name Selected data → Enter the name you want to define in the upper left corner =SUM(D3:D7) =SUM(Amount) =COUNTIF(Amount,">3000") =COUNTIF(Data scope,Judgment conditions) formula → The name manager processes names INFIRECT Functions: indirect =INDIRECT(D3) =INDIRECT(D7&D8) Drop down menu: Data → Data validity → Set (allow): sequence/List, source:=$F$3:$F$4) Select table → formula → Create from selection → Check the leftmost column → Sure Selected meal name → Data → Data validity → Set (Allowed: sequence, source:=indirect($B$3))
P20 12 tips to get twice the result with half the effort
1,Data analysis Select → Data → Breakdown → Check the separator → Semicolons: spaces → complete Separate name: selected data → Data → Breakdown → Fixed width → Drag the divider between names 2,Quickly select data Ctrl + ↑→↓← Jump to the corner of the form Ctrl + Shift + ↑→↓← Selection range 3,Insert multiple blank columns Right click → insert → F4(Repeat the previous function) Select multiple rows → Right click → Insert (select several rows to insert several rows) 4,Cell checkbox Select → Drag border → Realize the function of cutting and pasting Select → Press and hold Ctrl Key → Drag border → Realize the copy and paste function Select → Press and hold Shift Key → Drag border → Realize the function of cutting and inserting, and there will be no coverage 5,Remove duplicate data Data → Delete duplicates → deselect all → Check name 6,Table transpose Check copy → Right click → Paste special → Check transpose 7,Paste operation value Cell input 5 → Copy cell → Selected English scores → Paste special → Check add 8,Display formula Select total average → formula → Display formula 9,Target search Blank cell → Data → Simulation analysis → Univariate solution → Target cell: total average, target value: 60, variable cell: blank cell 10,Table diagonal ①Border → Drawing border ②Right click → Format Cells → Border → Diagonal type 11,Line feed in storage cell Alt+Enter 12,Insert picture annotation Text annotation: right click → Insert comment Picture annotation: right click → Insert comment → Delete text in box → Check the comment box → Right click → Format annotation → Color and line labels → Color drop-down → Filling effect → Picture → Select Picture → Resize picture
P21 table value format
Enter score: Type 0 1/2(0+Space+Score)
Start with zero value:'0026 or '1/2(Single quotation mark+Content)
Pre format text (start) → (number)
Custom numeric format:"$"#,##0_);[Red]("$"#,##0)
Select → Start → Figures( Ctrl+1)→ Custom → Type → #.#
# Pound size: represents the reserved position of one digit eg: #.# (the cell will not display extra zeros)
? Question mark: meaningless zeros are displayed as spaces, and decimal points will be aligned eg: #.?
0 Zero: each specified digit is forced to be displayed eg: #.0000
eg: 0000-000000，The arc number is displayed as: 0941-618745
Five digit employees ID: Select → 00000 or "T"00000 Or 00000"a centimeter"
@ at Symbol: represents the reserved position of text: select the equal box → @etc.
* Asterisk: the contents of the directory are filled in blank: Selected → @*.
Salary column: selected → $0 (Display as $33000)
Select → $* 0 (Display as $ 33000,*(space between and 0)
, Thousandth: 0, =Thousand 0,, =Million eg: $* 0,"K"
Composite structure: 0.00;(0.00);0.00;@ (positive;Negative value;Zero value;(text)
_ Bottom line: use the width of its continuation symbol to increase the margin
0.00_);(0.00);0.00_);@: Represents adding a closing bracket width to the right of positive and zero values
Align decimal points
[blue]0.00_);[gules](0.00);[yellow]0.00_);@ (Positive blue, negative red, zero yellow)
Eight colors in total:[Red][black][Yellow][Green][white][Blue][Cyan][magenta ]
Excel Comes with a palette of 56 colors eg: [Color 43]0.00_);[Color 5](0.00);[Color 26]0.00_);@
※ The code should be written completely
1. If you only fill in the code of one section, the code of this section will be applied to sections other than positive values eg: [blue]0.00
2. If you set codes for two sections, the first group will be applied to positive and negative values, and the second group will be applied to zero values eg: [blue]0.00_);[gules](0.00)
3. If nothing is entered in a section, the value of the section will be hidden eg: [blue]0.00_);[gules](0.00);;@
eg: ;;; (Hide all contents in the cell)
4. You can also set conditional syntax
eg: [>90][green]0.00;[>80][blue](0.00);0.00
Greater than 90 green, 89-90 Blue, the rest are preset black
5. Select phone number → Format Cells → special → Locale: Chinese(Taiwan) → General telephone number(8 Number of digits) → Custom → [<=99999999]####-####;(0#) ####-####
If: phone number≤99999999，The system judges that the area code is not included and cannot be applied####-####format
On the contrary, apply(0#) ####-####, including the format of area code
6. The system sets the format. You can set up two formats at most. If you want to apply more than three conditions, or if you want to meet the conditions, fill the cell or add an outer frame, and it is more ideal to use the conditions (conditional format) for setting the format)
Excel palette
P22 time format & length of service and man hour calculation
24:00 to 12:00: select the time by box → Custom cell format → hh:mm AM/PM Or morning/afternoon hh:mm(h Represents hours, m Representative minutes) Year Date: yyyy/mm/dd(Four digit year, two digit month and day) yyyy"year"mm"month"dd"day" ge"year"mm"month"dd"day"(The Taiwan version is displayed as the Republic of China X year X month X (day) [DBNUM1]m"month"d"day"(Chinese date: July 2) [DBNUM1]m"month"d"day"aaa(July 2 (Saturday) [DBNUM1]m"month"d"day"(aaaa)(July 2 (Saturday) yyyy/m/d h:mm:ss ((accurate to seconds)
| Combination | function | code |
|---|---|---|
| Ctrl+； | Type today's date | =TODAY() |
| Ctrl+Shift+； | Type the current time | |
| Ctrl+； Space and press Ctrl+Shift+ | Type today's date and current time | =NOW() |
F9 Manual update =now() Time
※ After the date cell is changed to normal, the date jumps to a number, indicating that it has been since 1900/1/1 Days since
1900/1/1 11900/1/2 Is 2, and so on
※ After the time cell is changed to normal, the time jumps to a number to 00 midnight:00 0, 24:00 Is 1,
The specific time is 0-1 Decimal representation between eg:
| 00:00 | 06:00 | 12:00 | 18:00 | 24:00 |
|---|---|---|---|---|
| 1 | 0.25 | 0.5 | 0.75 | 1 |
※ Therefore, the commute time can be subtracted to get the time difference, but the date difference will be ignored to get the wrong result After adjustment:① =D3-D2 ② [h]:mm The time difference including date difference can be obtained Adjust the time difference to minutes:[m] Number of days since entry: seniority (days)① =TODAY()-H3 ② Adjust format to general( H3: Entry date) Datedif: DATEDIF(Start date,End date,Unit of calculation) eg: Seniority (years) =DATEDIF(H3,TODAY(),"y") Seniority (month) =DATEDIF(H3,TODAY(),"m") Ignore day and year =DATEDIF(H3,TODAY(),"ym")
| DATEDIF unit type | Explain |
|---|---|
| Y | Calculate the number of years between two days |
| M | Calculate the number of months between two days |
| D | Calculate the number of days between two days |
| MD | Calculate the number of days between two days, ignoring month and year |
| YM | Calculate the number of months between two days, ignoring days and years |
| YD | Calculate the number of days between two days, ignoring the number of years |
Networkdays: NETWORKDAYS(Start date,End date,holiday) Weekends and designated holidays can be automatically excluded =NETWORKDAYS(M3,N3,Q3:Q4) M3: Work start date, N3: End of work day, Q3:Q4: Company vacation NETWORKDAYS.INTL: Non weekend leave NETWORKDAYS.INTL(Start date,End date,Custom weekend,holiday) =NETWORKDAYS.INTL(M4,N4,2,Q3:Q4)
| Custom weekend code | Weekend days |
|---|---|
| 1 or omitted | Saturday, Sunday |
| 2 | Sunday, Monday |
| 3 | Monday, Tuesday |
| 4 | Tuesday, Wednesday |
| 5 | Wednesday, Thursday |
| 6 | Thursday, Friday |
| 7 | Friday, Saturday |
| 11 | Sunday only |
| 12 | Monday only |
| 13 | Tuesday only |
| 14 | Wednesday only |
| 15 | Thursday only |
| 16 | Friday only |
| 17 | Saturday only |
P23 how to calculate the score ranking
RANK.EQ: Ranking by original student number, not by new student number RANK.EQ(Subject,Comparison range) =RANK.EQ(D3,$D$3:$D$10) For duplicate ranking: RANK.AVG =RANK.AVG(D3,$D$3:$D$10) Sort by: RANK.EQ(Subject,Comparison range,sort order) The default is 0: descending sorting. The larger the number, the higher the ranking
P24 how to use the LEFT function to extract text data in a table
P25 Index & match-vlookup
P26 four tips to protect forms so that strangers can't change your important documents
P27 repeated content
P28 use Excel to draw prizes for the company's annual meeting activities and allocate classes
P29 form the good habit of not delaying and make a beautiful progress
P30 how to make a good Gantt chart
P31 the function of this function is like the universal Swiss Army knife
P32 has made it super easy to write formulas since then
P33 magic chart from 2 / 8 rule, let yourself + the company
P34 VLOOKUP retired with success, and the new function XLOOKUP
P35 Excel thief appeared. I want all these web pages
P36 Excel questionnaire also needs to play a one-stop service? Don't wait for heroes,
P37 what kind of witchcraft is this? I worked on the form for half a day, and the colleague next to me turned out