95个EXCEL常用技巧(快捷键+常用函数)速查版
95 Excel Tips & TricksT O M A K E Y O U A W E S O M E A T W O R KbyP UR N A D UG G I R A L A95 Excel Tips & TricksTo make you awesome at workby Purna Duggirala/wp© 2012 by You have permission to post this, email this, print this and pass it along for free to anyone you like, as long as you make no changes or edits to its contents or digital format. In fact, I’d love it if you’d make lots and lots of copies. Don’t just go and bind this book and start selling it; that right is still with me.You can find all these tips and hundreds more tricks at /wpWhat is in this book?Excel Keyboard Shortcuts (5)Excel Formulas for Everyday Situations (9)Day to Day Excel Usage (6)Excel Charting Tips & Tweaks (7)Follow These 6 Steps for Better Chart Formatting (8)Know these 15 Powerful Excel Formulas (9)15 Ways to Have Fun with Excel (17)Know How to Paste Your Data – 7 Tricks (23)1 During formula typing, adjusts the reference type, abs to relative, otherwise repeats last action2 Inserts current date3 Copies value from cell above to current cell4 Edits a cell comment5 Opens macro dialog box6 Auto sum selected cells and places value in cell beneath7 Currency formats current cell8 Applies outline border to selected cells9 Comma formats current cell10 Activates font drop down list11 Activates font point size drop down list More: Comprehensive list of Keyboard Shortcuts in Excel1.To format a number as SSN, use the custom format code “000-00-0000″… Get FullTip2.To format a phone number, use the custom format code “000-000-0000″… Get FullTip3.To show values after decimal point only when number is less than one, use[<1]_($#,##0.00_);_($#,##0_) as formatting code… Get Full Tip4.To hide a worksheet, go to menu > format > sheet > hide… Get Full Tip5.To align multiple objects, like charts, drawings, pictures use drawing toolbar > alignand select alignment option… Get Full Tip6.To disable annoying formula errors, go to menu > tools > options > error checkingtab and disable errors you don’t want to see… Get Full Tip7.To transpose a range of cells, copy the cells, go to empty area, and press a lt+e+s+e…Get Full Tip8.To save data filter settings so that you can reuse them again, use custom views… GetFull Tip9.To select all formulas, press CTRL+G, select “special” and check “formulas”10.To select all constants, press CTRL+G, select “special” and check “constants”11.To clear formats from a range, select menu > edit > clear > “formats”12.To move a chart and align it with cells, hold down ALT key while moving the chart1.To create an instant micro-chart from your normal chart, use camera tool… GetFull Tip2.Understand data to ink ratio to reduce chart junk, using even a pixel more of inkthan what is needed can reduce your chart’s effectivenessbine two different types of charts when one is not enough, to use, add anotherseries of data to your sheet and then right click on it and change the chart type… Get Full Tip4.To reverse the order of items in a bar / column chart, just click on y-axis, pressctrl+1, and check “categories in reverse order” and “x-axis crosses at maximumcategory” options5.To change the marker symbol or bubble in a chart to your own favorite shape,just draw any shape in worksheet using drawing toolbar, then copy it by pressing ctrl+c, now go to the chart and select markers (or bubbles) and press ctrl+v6.To create partially overlapped column / bar charts just use overlap and gapsettings in the format data series area. A overlap of 100 will completely overlap one series on another, while 0 separates them completely.… Get Full Tip7.To increase the contrast of your chart, just remove grayish background color thatexcel adds to the chart (in versions excel 2003 and prior)8.To save yourself some trouble, always try to avoid charts like - 3D area charts (un-stacked), radar charts, 3D Lines, 3D Columns with multiple series of data, Donut charts with more than 2 series of data… Get Full Tip9.To improve comparison, replace your radar charts with tables… Get Full Tip More: Excel Charting Tips & Tutorials1.Remove any vertical grid-lines2.Change horizontal grid-line color from black to a very light shade of gray3.Adjust chart series colors to get better contrast4.Adjust font scaling (for versions excel 2003 and prior)5.Add data labels and remove any axis (axis labels) if needed6.Remove chart background colors1.To get the first name of a person, use =left(name, find(“ “,name)-1)2.To calculate mortgage payments, use =PMT(interest-rate, number-of-payments, how-much-loan)3.To get nth largest number in a range, use =large(range, n)… Get Full Tip4.To get nth smallest number in a range, use = small(range, n)… Get FullTip5.To generate a random phone number, use=randbetween(1000000000,9999999999), needs analysis tool pack if you are using excel 2003 or earlier… Get Full Tip6.To count number of words in a cell, use =len(trim(text))-len(SUBSTITUTE(trim(text),” “,”"))… Get Full Tip7.To count positive values in a range, use =countif(range,”>0″)… Get FullTip8.To calculate weighted average, use SUMPRODUCT() function9.To remove unnecessary spaces, use =trim(text)10.To format a number as SSN using formulas, use =text(ssn-text,”000-00-0000″)… Get Full Tip11.To find age of a person based on DOB, use =TEXT((NOW()-birth_date)&”",”yy “”years”" m “”months”" dd “”days”"”), output will be like 27 years 7 months 29 days12.To get name from initials from a name, use IF(), FIND(), LEN() andSUBSTITUTE()formulas… Get Full Tip13.To get proper fraction from a number (for eg 1/3 from 6/18), use=text(fraction, “?/?”)14.To get partial matches in vlookup, use * operator like this:=vlookup(”abc*”,lookup_range,return_column)15.To simulate averageif() in earlier versions of excel, use =sumif(range,criteria)/countif(range, criteria)16.To debug your formulas, select the portions of formula and press F9 to see the resultof that portion… Get Full Tip17.To get the file extension from a file name, use =right(filename,3)(doesn’twork for files that have weird extensions like .docx, .htaccess etc.)18.To quickly insert an in cell micro-chart, use REPT()function… Get Full Tip19.COUNT() only counts number of cells with numbers in them, if you want to countnumber of cells with anything in them, use COUNTA()ing named ranges in formulas saves you a lot of time. To define one, just selectsome cells, and go to menu > insert > named ranges > defineMore: Excel formula tips & tutorialsExcel formulas can always be very handy, especially when you are stuck with data and need to get something done fast. But how well do you know the spreadsheet formulas? Discover these 15 extremely powerful excel formulas and save a ton of time next time you open that spreadsheet.1.Change the case of cell contents - to UPPER, lower, ProperBoss wants a report of top 100 customers, thankfully you have the data, but thecustomer names are all in lower cases. Fret not, you can Proper Case cell contents with proper() formula.Example: Use proper("become awesome in excel") to get Become Awesome In ExcelAlso try lower() and upper() as well to change excel cell value to lower andUPPER case2.Clean up textual data with trim, remove trailing spacesOften when you copy data from other sources, you are bound to get lots of empty spaces next to each cell value. You can clean up cell contents with trim() spreadsheet function.Example: Use trim(" copied data ") to get copied data3.Extract characters from left, right or center of a given textNeed the first 5 numbers of that SSN or area code from that phone number? You can command excel to do that with left() function.Example: Use left("Hi Beautiful!",2) to get HiAlso try right(text, no. of chars) and mid(text, start, no. of chars) to get rightmost or middle characters. You can use right(filename,3) to get theextension of a file name4.Find second, third, fourth element in a list without sortingWe all know that you can use min(), max() to find the smallest and largest numbers ina list. But what if you needed the second smallest number or 3rd largest number in thelist? You are right, there is a spreadsheet function to exactly that.Example: Use SMALL({10,9,12,14,26,13,4,6,8},3) to get 8Also try large(list, n) to get the nth largest number in a list.5.Find out current date, time with a snapYou have a list of customer orders and you want to findout which ones are due forshipping after today. The funny thing is you do this everyday. So instead of enteringthe date every single day you can use today()Example: Use today() to get 08/13/2008or whatever is today’s dateAlso try now() to get current time in date time format. Remember, you can always format these date and times to see them the way you like (for eg. Aug-13, August 13,2008 instead of 08/13/2008)6.Convert those lengthy nested if functions to one simple formulawith Choose()Planning to create a grade book or something using excel, you are bound to writesome if() functions, but do you know that you can use choose() when you have more than 2 outcomes for a given condition? As you all know, if(condition, fetch this, or this)returns “fetch this” if the condition is TRUE or “or this” if the condition is FALSE. Learn more about spreadsheet if functions like countif, sumifetc.Where as choose(m, value1, value2, value3, value4 ...) can return any of the value1,2.., based on the parameter m.Example: Use CHOOSE(3,"when","in","doubt","just","choose")to get doubtRemember, you can always write another formula for each of the n parameters of choose() so that based on input condition (in this case 3), another formula isevaluated.7.Repetitively print a character in a cell n number of timesYou have the ZIP codes of all your customers in a list and planning to upload it to an address label generation tool. The sad part is for some reason, excel thinks zip codes are numbers, so it removed all the trailing zeros on the leftside of the zip code, thus making the 01001 as 1001. Worry not, you can use rept() the extra needed zeros.You can also custom format cell contents to display zip codes, phone numbers, ssn etc.Example: Use zipcode & REPT("0",5-LEN(zipcode)) to convert zipcode 1001 to 01001You can use REPT("|",n) to generate micro bar charts in your sheet. Learn more about incell charting.8.Find out the data type of cell contentsThis can be handy when you are working off the data that someoneelse has created. For example you may want to capitalize if thecontents are text, make it 5 characters if its a number and leave itas it is otherwise for certain cell value. Type() does just that, it tellswhat type of data a cell is containing.Example: Use TYPE("Chandoo") to get 2See the various type return values in the diagram shown right.9.Round a number to nearest even, odd numberWhen you are working with data that has fractions / decimals, often you may need to find the nearest integer, even or odd number to the given decimal number. Thankfully excel has the right function for this.Example: Use ODD(63.4) to get 65Also try even() to nearest even number and int() to round given fraction to integer just below it.Example: Use EVEN(62.4) to get 64Use INT(62.99) to get 62If you need to round off a given fraction to nearest integer you can useround(62.65,0) to get 63.10.Generate random number between any 2 given numbersWhen you need a random number between any two numbers, try randbetween(), it is very useful in cases where you may need random numbers to simulate some behavior in your spreadsheets.Example: Use RANDBETWEEN(10,100) may return 47 if you keep trying11.Convert pounds to KGs, meters to yards and tsps to table spoonsYou need not ask Google if you need to convert 156 lbs to kilograms or find out how much 12 tea spoons of olive oil actually means. The hidden convert() function is really versatile and can convert many things to so many other things, except one currency to another, of course.Example: Use CONVERT(150,"lbm","kg") to convert 150 lbs to 68.03 kgs.Use CONVERT(12,"tsp","oz") to findout that 12 tsps is actually 2 ounces.12.Instantly calculate loan installments using spreadsheet formulaYou have your eyes on that beautiful car or beach property, but before visiting the seller / banker to findout of the monthly payment details, you would like to see how much your monthly / biweekly loan payments would be. Thankfully excel has the right formula to divide an amount to equal payment installments over given time period, the pmt() function.If your loan amount is $125,000,APR (interest rate per year) is 6%,loan tenure is 5 years andpayments are made every month, then,Use PMT(6%/12,5*12,-125000) which tells us that monthly payment is $ 2,416 if you keep tryingAlso, if you want to find out how much of each payment is going for principle and how much for the interest component, try using ppmt() and ipmt() functions. As you can guess, even though EMIs or loan installments remain constant, the amountcontributed to principle and interest vary each month.13.What is this we ek’s number in the current year?Often you may need to find out if the current week is 25th week of this year. This is not so difficult to find as it may seem. Again, excel has the right function to do just that.Example: Use WEEKNUM(TODAY()) will get 3314.Find out what is the date after 30 working days from today?Finding out a future date after 30 days from today is easy, just change the month. But what if you need to know the date thirty working days from now. Don’t use your fingers to do that counting, save them for typing a comment here and use theworkday() excel function instead.Example: Use WORKDAY(TODAY(),30) tells that Sep 24, 2008 is 30 working days away from today.If you want to find out number of working days between 2 dates you can usenetworkdays() function, find out this and a 14 other fun things you can do with excel.15.With so many functions, how to handle errorsOnce you get to the powerful domain of excel functions to simplify your work, you are bound to have incorrect data, missing cells etc. that can make your formulas go kaput. If only there is a way to find out when a formula throws up error, you can handle it. Well, you know what, there is a way to find out if a cell has an error or a proper value. iserror() MS Excel function tells you when a cell has error.Example: Use ISERROR(43/0) returns TRUE since 43 divided by zero throws divide by zero error.Also try ISNA() to find out if a cell has NA error (Not applicable). Give these functions a try, simplify your work and enjoyMore: Advanced Formula Examples & ExplanationsWho said Excel takes lot of time / steps do something? Here is a list of 15 incredibly fun things you can do to your spreadsheets and each takes no more than 5 seconds to do.1. Change the shape / color of cell commentsJust select the cell comment, go to draw menu in bottom left corner of the screen, and choose change auto shape option, select a 32 pointed star or heart symbol or a smiley face, just wow everyone2. Filter unique items from a listSelect the data, go to data > filter > advanced filter and check the “unique items” option.3. Sort from Left to RightWhat if your data flows from left to right instead of top to bottom? Just change the sort orientation from “sort options” in the data > sort menu.4. Hide the grid lines from your sheetsGo to Options dialog in tools menu, uncheck the “grid lines” option to remove gridlines from your worksheets. You can also change the color of grid line from here (not recommended)5. Add rounded border to your charts, make them look smoothJust right click on the chart, select format chart option, in the dialog, check the “rounded borders”. You can even add a shadow effect from here.6. Fetch live stock quotes / company research with one clickJust enter the stock symbol (MSFT, GOOG, AAPL etc.) in a cell, alt+click on the cell to launch “research pane”, select stock quotes to see MSN Money quotes for the selected symbol. You can fetch company profiles in the same way. Learn more.7. Repeat rows on top when printing, show table headers on every pageWhen you are on the sheet view, just hit menu > file > page setup, go to the last tab, specify “rows to repeat”. You can “repeat columns while printing” as well from the same menu.8. Remove conditional formatting / all formatting with one clickJust go to Menu > Edit > Clear > All to remove all the formatting from selected cell / range.9. Auto sum cells with one clickSelect a bunch of cells and click on the Sigma symbol on the standard tool bar. Alternatively you can use Alt+= keyboard shortcut.10. Find width of a column with formula, really!Just use =cell("width") to find the width of the column to which that formula cell belongs. Width is returned as the nearest integer.11. Find total working days between any two dates, including holidaysIf you work on project plans, gantt charts alot, this can be totally handy. Just type=networkdays(start date, end date, list of holidays) to fetch the number of working days. In the above sample you can see the number of working days between New years day and September first of this year (labor day).12. Freeze Rows / Columns in your sheet, Show important info even when scrollingSelect the cell diagonally beneath the row / columns you want to freeze (for eg. if you wan to freeze row 1&2 and columns A&B, click in C3), go to menu > window and click on freeze panes.13. Split sheets in to two, compare side by side to be more productiveJust click on this little vertical bar on the bottom right corner of the sheet (see below) and drag it to create a vertical split. You can do the same way for a horizontal split as well14. Change the color of various sheet name tabsRight click on sheet and select “Tab color” option to change the worksheet tab colors. Group them with similar colors if you have lot of sheets, it looks nice.15. Insert a quick organization chartClick on menu > insert > diagram to open the above dialog, just selectthe organization chart option, enter node values and you have a pretty organization chart. Alternatively learn how to create org charts inexcel.1. Paste Formats (or Format painter)Like that sleek table format your colleague has made? Butdon’t have the time to redo it yourself, worry not, you canpaste formatting (including any conditional formats) fromany copied cells to new cells, just hit ALT+E S T.2. Paste FormulasIf you want to copy a bunch of formulas to a new range of cells - this is very useful. Just copy the cells containing the formulas, hit ALT+E S F. You can achieve the same effect by dragging the formula cell to new range if the new range is adjacent.3. Paste ValidationsLove copy those input validations you have createdbut not the cell contents or anything, just pressALT+E S N. This is very useful when you created aform and would like to replicate some of the cells toanother area.4. Adjust column widths of some cells based on other cellsYou have created a table for tracking purchases and your boss liked it. So he wanted you to create another table to track sales and you want to maintain the column widths in the new table. You dont have to move back and forth looking for column widths or anything. Instead just paste column widths from your selection. Use ALT+E S W.5. Grab comments only and paste them elsewhereIf you want to copy comments alone from certain cellsto a new set of cells, just use ALT + E S C. This willreduce the amount of retyping you need to do.6. Add while pastingFor example, if you have in Row 1 - 1 2 3 asvalues and in Row 2 - 7 8 9 as values and youwould like to add row 1 values to row 2 values toget - 8 10 12, you can do this using paste special.Just copy row 1 values and use ALT + E S D.7. Paste links to other cellsIf you want to create references to a bulk of cells instead of copy-pasting all the values this is the option for you. Just use ALT+E S L to create an automatic reference to copied range of cells.Want to learn more?Consider joining our Excel training programsBecome awesome in Excel & Dashboards with online classes from .Click here to know more Use VBA & Macros like a pro by joining our online trainingprogram.Click here to know moresomethingThank you for reading 95 Excel Tips & TricksFor more tips, visit。
Excel操作技巧速查手册个让你轻松操作的快捷键
Excel操作技巧速查手册个让你轻松操作的快捷键Excel操作技巧速查手册：个人让你轻松操作的快捷键Excel是一款广泛应用于各行各业的强大电子表格软件。
无论你是初学者还是经验丰富的用户，学会使用一些Excel操作技巧和快捷键，可以帮助你提高工作效率和数据处理能力。
本速查手册将为你介绍一些常用的Excel操作技巧及其对应的快捷键，帮助你轻松操作Excel。
一、常用的快捷键1. 新建工作簿：Ctrl+N2. 打开工作簿：Ctrl+O3. 保存工作簿：Ctrl+S4. 另存为：F125. 关闭工作簿：Ctrl+W6. 退出Excel：Alt+F47. 撤销：Ctrl+Z8. 重做：Ctrl+Y9. 剪切：Ctrl+X10. 复制：Ctrl+C11. 粘贴：Ctrl+V12. 选择全部：Ctrl+A13. 查找：Ctrl+F14. 替换：Ctrl+H15. 删除单元格内容：Delete16. 删除整行或整列：Ctrl+-17. 插入单元格：Ctrl+Shift+=18. 插入整行或整列：Ctrl+Shift++(加号)19. 移动到下一个工作表：Ctrl+PgDn20. 移动到上一个工作表：Ctrl+PgUp二、编辑和格式化技巧1. 快速编辑单元格：双击单元格2. 填充单元格序列：选中需要填充的单元格，将鼠标移到单元格右下角，出现黑色十字，向下拖动鼠标即可自动填充序列3. 设置文本自动换行：选中需要自动换行的单元格，右键点击单元格，选择“格式单元格”，进入对话框，选择“对齐”选项卡中的“自动换行”4. 合并单元格：选中需要合并的单元格，右键点击单元格，选择“格式单元格”，进入对话框，选择“对齐”选项卡中的“合并单元格”5. 设置数字格式：选中需要设置格式的单元格，右键点击单元格，选择“格式单元格”，进入对话框，选择“数字”选项卡，选择相应的数字格式6. 设置边框：选中需要设置边框的单元格或区域，右键点击单元格，选择“格式单元格”，进入对话框，选择“边框”选项卡，选择相应的边框样式7. 设置自动筛选：选中包含数据的区域，点击“数据”选项卡上的“排序和筛选”，选择“筛选”三、公式和函数技巧1. 输入公式：在单元格中输入“=”后，输入相应的公式2. 快速求和：选中一列或一行数据，查看状态栏上的求和值3. 平均值：选中一列或一行数据，查看状态栏上的平均值4. 最大值和最小值：选中一列或一行数据，查看状态栏上的最大值和最小值5. 查找特定数值：使用“查找与选择”功能，通过指定条件查找特定数值6. 合并单元格求和：使用“合并单元格”时，使用SUM函数求和合并区域的数值7. 计算百分比：使用百分比格式，或用数值除以总数后乘以100得到百分比值四、数据操作技巧1. 排序：选中需要排序的区域，点击“数据”选项卡上的“排序”2. 筛选：选中需要筛选的区域，点击“数据”选项卡上的“排序和筛选”，选择“筛选”3. 数据透视表：选中需要创建透视表的区域，点击“插入”选项卡上的“透视表”4. 数据验证：选中需要设置验证的区域，右键点击单元格，选择“数据验证”，进入对话框，设置验证条件5. 文本拆分：选中需要拆分的文本列，点击“数据”选项卡上的“文本到列”6. 去重：选中需要去重的区域，点击“数据”选项卡上的“删除重复项”7. 填充日期和时间：输入开始日期或时间，选中需要填充的单元格区域，点击右键选择“填充”，选择“序列”，选择需要的序列类型，点击确定填充五、其它常用技巧1. 快速切换工作表：Ctrl+PageUp和Ctrl+PageDown2. 分屏显示：点击“视图”选项卡上的“新建窗口”，然后在“视图”选项卡上点击“拆分”3. 设置打印区域：选中需要打印的区域，点击“页面布局”选项卡上的“打印区域”，选择“设置打印区域”4. 创建图表：选中需要创建图表的数据区域，点击“插入”选项卡上的“图表”，选择所需的图表类型5. 设置打印标题：选中需要设置的打印行或列，点击“页面布局”选项卡上的“打印标题”6. 设置批注：选中需要添加批注的单元格，右键点击单元格，选择“插入批注”7. 使用快速分析工具：选中需要分析的数据区域，点击“数据”选项卡上的“快速分析”六、总结通过掌握这些Excel操作技巧和快捷键，你可以在使用Excel时更加高效和便捷。
Excel函数、公式、快捷方式操作大全(动画版)
Excel函数、公式、快捷方式(注：需要另存为网页模式查看) 一、职场必用的10个Excel函数- 01 -IF函数用途：根据逻辑真假返回不同结果。
作为表格逻辑判断函数，处处用得到。
函数公式：=IF（测试条件，真值，[假值]）函数解释：当第1个参数“测试条件”成立时，返回第2个参数，不成立时返回第3个参数。
IF函数可以层层嵌套，来解决多个分枝逻辑。
- 02 -SUMIF和SUMIFS函数用途：对一个数据表按设定条件进行数据求和。
- SUMIF函数 -函数公式：=SUMIF（区域，条件，[求和区域]）函数解释：参数1：区域，为条件判断的单元格区域；参数2：条件，可以是数字、表达式、文本；参数3：[求和区域]，实际求和的数值区域，如省略则参数1“区域”作为求和区域。
- SUMIFS函数 -函数公式：=SUMIFS(求和区域,区域1,条件1,[区域2],[条件2],……)函数解释：第1个参数是固定求和区域。
区别SUMIF函数的判断一个条件，SUMIFS函数后面可以增加多个区域的多个条件判断。
- 03 -VLOOKUP函数用途：最常用的查找函数，用于在某区域内查找关键字返回后面指定列对应的值。
函数公式：=VLOOKUP（查找值，数据表，列序数，[匹配条件]）函数解释：相当于=VLOOKUP（找什么，在哪找，第几列，精确找还是大概找一找）最后一个参数[匹配条件]为0时执行精确查找，为1（或缺省）时模糊查找，模糊查找时如果找不到则返回小于第1个参数“查找值”的最大值。
- 04 -MID函数用途：截取一个字符串中的部分字符。
有的字符串中部分字符有特殊意义，可以将其截取出来，或对截取的字符做二次运算得到我们想要的结果。
函数公式：=MID（字符串，开始位置，字符个数）函数解释：将参数1的字符串，从参数2表示的位置开始，截取参数3表示的长度，作为函数返回的结果。
- 05 -DATEDIF函数用途：计算日期差，有多种比较方式，可以计算相差年数、月数、天数，还可以计算每年或每月固定日期间的相差天数、以及任意日期间的计算等，灵活多样。
常用Excel函数和快捷键说明
常用Excel函数说明1、自动编号函数：＝MAX($A$1:A1)+1含义：如果A1编号为1，那么A2＝MAX($A$1:A1)+1。
假设A1为1，那么在A2中输入“＝MAX($A$1:A1)+1”，回车。
然后用填充柄将公式复制到其他单元格即可。
2、统计某一区域某一数据出现的次数函数：＝COUNTIF(K4:K60,”新开”)含义：统计K4到K60单元格中“新开”出现的次数。
假设在K61中输入“新开”，在K62中输入“＝COUNTIF(K4:K60,”新开”)”后回车，即可统计出K4至K60单元格中“新开”出现的次数。
3、避免输入相同的数据函数：＝COUNTIF(A:A,A2)=1含义：在选定的单元格区域中保证输入的数据的唯一性。
假设在A列中避免输入重复的姓名，先选中相应的单元格区域，如A2至A500，依次选中“数据-有效性-设置”，在“允许”下拉栏中选“自定义”，在“公式”栏中输入“＝COUNTIF(A:A,A2)=1”。
单击“确定”完成。
4、在单元格中快速输入当前日期函数：＝today()含义：快速输入当前日期，并可随系统时间自动更新。
方法：在选中单元格中输入“＝today()”回车，即可输入当前日期。
5、在单元格中快速输入当前日期及时间函数：＝now()含义：快速输入当前日期及时间，并可随系统时间自动更新。
方法：在选中单元格中输入“＝now()”回车，即可输入当前日期及时间。
6、缩减小数点后的位数函数：＝TRUNC(A1,1)含义：将两位小数点变为一位小数点。
方法：例如，A1的数值为“36.99”，需要在B1中显示A1数值小数点后的一位，则在B1中直接输入函数表达式“=TRUNC(A1,1)”，而后将在B1中显示“36.9”。
7、将数据合二为一函数：＝CONCATENATE(A1,B1)含义：将Excel中的两列数据合并至一列中。
方法：如果要将Excel中的两列数据合并至一列中，可以使用文本合并函数。
excel快捷键函数公式表
excel快捷键函数公式表Excel是一款非常强大的电子表格软件，它提供了许多功能，如数据统计、分析、展示等。
为了更高效地使用Excel，了解其快捷键、函数和公式是非常重要的。
本文将为您介绍一些常用的Excel快捷键、函数和公式，以帮助您更好地完成工作。
一、快捷键1. 常用快捷键Ctrl+C：复制Ctrl+V：粘贴Ctrl+A：全选Ctrl+Z：撤销操作Ctrl+N：新建工作簿Ctrl+S：保存工作簿2. 单元格操作快捷键Delete：删除单元格内容Backspace：删除上一单元格内容Alt+Enter：显示单元格内容格式Alt+Shift+方向键：快速调整单元格格式3. 窗口操作快捷键F5：定位对话框F8：逐个选择单元格Ctrl+Tab：快速切换工作表Alt+PageDown/PageUp：切换窗口二、常用函数1. SUM函数：用于求和。
2. AVERAGE函数：用于求平均值。
3. MAX函数：用于求最大值。
4. MIN函数：用于求最小值。
5. IF函数：用于根据条件返回不同的值。
6. COUNT函数：用于统计单元格个数。
7. COUNTIF函数：用于统计满足条件的单元格个数。
8. VLOOKUP函数：用于查找与目标单元格匹配的值。
9. HLOOKUP函数：用于在表格中查找值。
10. CONCATENATE函数：用于将多个单元格函数或值合并成一个表达式。
三、公式使用规则1. 在单元格中输入公式时，必须以“=”开头。
2. 单元格引用必须用方括号括起来，否则Excel将自动识别正确的引用类型。
例如，“A1”和“A1:B2”是有效的引用，而“A-1”和“A+B”则不是。
3. 在输入公式时，可以使用鼠标或键盘输入单元格引用，并使用“$”符号对列进行锁定，以提高准确性。
例如，“=$A$1”表示引用A 列第一行的单元格。
4. 当更改公式所在单元格中的数据时，Excel会自动更新公式中引用的单元格。
但如果引用涉及其他公式或数据源，则需要手动更新。
EXCEL的快捷键大全及使用技巧
EXCEL的快捷键大全及使用技巧Excel是一款非常强大的电子表格软件，几乎所有的行业和职业都需要使用它来处理数据和进行分析。
了解和熟练掌握Excel的快捷键和使用技巧可以提高工作效率和准确性。
下面是一些常用的Excel快捷键和使用技巧，帮助您更好地利用Excel进行工作。
一、基本快捷键：1. Ctrl+C：复制选中的单元格或对象。
2. Ctrl+X：剪切选中的单元格或对象。
3. Ctrl+V：粘贴已复制或剪切的单元格或对象。
4. Ctrl+Z：撤销上一步操作。
5. Ctrl+S：保存当前工作簿。
6. Ctrl+A：选择整个工作表。
7. Ctrl+F：查找特定的内容。
8. Ctrl+H：替换特定的内容。
9. Ctrl+B：将选中的单元格设置为粗体字体。
10. Ctrl+I：将选中的单元格设置为斜体字体。
2.F4：在公式中切换相对引用和绝对引用。
3.F9：计算当前工作表的所有公式。
4. Alt+=：在选中的区域中自动求和。
5. Ctrl+’：复制选中的单元格中的公式。
三、格式化快捷键：1. Ctrl+1：打开格式单元格对话框。
2. Ctrl+Shift+1：设置选中的单元格为“常规”格式。
4. Ctrl+Shift+$：设置选中的单元格为“货币”格式。
5. Ctrl+Shift+%：设置选中的单元格为“百分比”格式。
四、导航快捷键：1. Ctrl+Page Up：切换到前一个工作表。
2. Ctrl+Page Down：切换到后一个工作表。
3. Ctrl+Home：跳转到工作表的顶部。
4. Ctrl+End：跳转到工作表的底部。
5. Ctrl+Arrow Up：跳转到当前列的顶部。
6. Ctrl+Arrow Down：跳转到当前列的底部。
五、筛选和排序快捷键：1. Ctrl+Shift+L：打开筛选器。
2. Alt+Down Arrow：打开筛选器的下拉列表。
3. Ctrl+Shift+L：在筛选器的下拉列表中选择一个值。
Excel快捷键大全个不可错过的操作技巧
Excel快捷键大全个不可错过的操作技巧在日常的工作和学习中，Excel是一个非常常用的电子表格软件。
熟练掌握Excel的快捷键是提高工作效率和操作便捷性的关键。
本文将为大家介绍一些Excel中不可错过的操作技巧及相应的快捷键大全。
通过学习掌握这些技巧和快捷键，相信能够为大家提高Excel的使用效率，提升工作和学习的效果。
一、插入和删除操作快捷键1. 在当前单元格上方插入一行：Ctrl + Shift + "+"；2. 在当前单元格左侧插入一列：Ctrl + Shift + "="；3. 删除当前行：Ctrl + "-"；4. 删除当前列：Ctrl + Shift + "-"；二、选择和移动操作快捷键1. 选择整列：Ctrl + Space；2. 选择整行：Shift + Space；3. 选择当前区域：Ctrl + A；4. 移动到下一个工作表：Ctrl + Page Down；5. 移动到上一个工作表：Ctrl + Page Up；6. 移动到最左侧的单元格：Ctrl + Home；7. 移动到最右侧的单元格：Ctrl + End；三、编辑和填充操作快捷键1. 复制选定的单元格：Ctrl + C；2. 剪切选定的单元格：Ctrl + X；3. 粘贴复制或剪切的单元格：Ctrl + V；4. 自动填充选定单元格序列：Ctrl + R（向右填充）；Ctrl + D（向下填充）；四、格式操作快捷键1. 设置粗体字：Ctrl + B；2. 设置斜体字：Ctrl + I；3. 设置下划线：Ctrl + U；4. 设置字体大小增大：Ctrl + Shift + ">"；5. 设置字体大小减小：Ctrl + Shift + "<"；6. 设置文本居中：Ctrl + Shift + "C"；五、公式和函数操作快捷键1. 插入函数：Shift + F3；2. 选择函数参数框内所有的单元格：Ctrl + Shift + *；3. 打开函数库：Shift + F10；4. 快速输入函数名：Ctrl + K；六、其他常用操作快捷键1. 打开“文件”菜单：Alt + F；2. 保存当前工作簿：Ctrl + S；3. 打印当前工作簿：Ctrl + P；4. 关闭Excel：Alt + F4；除了以上介绍的快捷键外，Excel还有许多其他的快捷键操作，读者可以根据自己的需要进行进一步的学习和研究。
Excel快捷键速查指南帮你迅速掌握各种技巧
Excel快捷键速查指南帮你迅速掌握各种技巧Excel是一款功能强大的电子表格程序，广泛应用于商务、财务和数据分析等领域。
熟练掌握Excel的快捷键可以显著提高工作效率，本文将为您提供一份简洁明了的Excel快捷键速查指南，帮助您迅速掌握各种技巧。
一、基本操作类快捷键1. 新建工作簿：Ctrl+N2. 打开工作簿：Ctrl+O3. 保存工作簿：Ctrl+S4. 关闭工作簿：Ctrl+W5. 退出Excel：Alt+F4二、单元格操作类快捷键1. 选中整列或整行：Ctrl+Space/Shift+Space2. 选中连续区域：Ctrl+Shift+↑/↓/←/→3. 选中当前单元格到最左侧非空单元格：Ctrl+Shift+←4. 选中当前单元格到最右侧非空单元格：Ctrl+Shift+→5. 插入新行或新列：Ctrl++（加号）6. 删除单元格、行或列：Ctrl+-（减号）7. 复制单元格：Ctrl+C8. 剪切单元格：Ctrl+X9. 粘贴单元格：Ctrl+V三、编辑类快捷键1. 进入编辑模式：F22. 取消编辑：Esc3. 公式填充：Ctrl+Enter4. 剪切选中区域：Ctrl+Shift+X5. 复制选中区域：Ctrl+Shift+C6. 清除内容：Delete7. 插入当前日期：Ctrl+;8. 插入当前时间：Ctrl+Shift+;四、格式设置类快捷键1. 数字格式：Ctrl+Shift+~2. 日期格式：Ctrl+Shift+#3. 百分比格式：Ctrl+Shift+%4. 文本加粗：Ctrl+B5. 文本斜体：Ctrl+I6. 文本下划线：Ctrl+U7. 字体大小增加：Ctrl+Shift+>8. 字体大小减小：Ctrl+Shift+<五、公式类快捷键1. 插入函数：Shift+F32. 插入函数参数提示：Ctrl+A3. 自动求和：Alt+=4. 复制公式：Ctrl+D5. 插入函数当前单元格的地址：F46. 跳转到前一个工作表：Ctrl+Page Up7. 跳转到后一个工作表：Ctrl+Page Down六、筛选和排序类快捷键1. 自动筛选：Ctrl+Shift+L2. 清除筛选：Alt+Shift+C3. 升序排序：Alt+A+S4. 降序排序：Alt+A+D七、其他实用快捷键1. 打开格式设置对话框：Ctrl+12. 快速插入当前日期：Ctrl+;+Space+Enter3. 刷新数据：Ctrl+Alt+F54. 快速填充：Ctrl+E5. 重复上一次操作：Ctrl+Y通过本文提供的Excel快捷键速查指南，相信您已经掌握了各种技巧，能够更加高效地使用Excel进行数据处理和分析。
Excel表格实用技巧大全及全部快捷键
Excel表格实用技巧大全及全部快捷键一、让数据显示不一致颜色在学生成绩分析表中，假如想让总分大于等于500分的分数以蓝色显示，小于500分的分数以红色显示。
操作的步骤如下：首先，选中总分所在列，执行“格式→条件格式”，在弹出的“条件格式”对话框中，将第一个框中设为“单元格数值”、第二个框中设为“大于或者等于”，然后在第三个框中输入500，单击[格式]按钮，在“单元格格式”对话框中，将“字体”的颜色设置为蓝色，然后再单击[添加]按钮，并以同样方法设置小于500，字体设置为红色，最后单击[确定]按钮。
这时候，只要你的总分大于或者等于500分，就会以蓝色数字显示，否则以红色显示。
二、将成绩合理排序假如需要将学生成绩按着学生的总分进行从高到低排序，当遇到总分一样的则按姓氏排序。
操作步骤如下：先选中所有的数据列，选择“数据→排序”，然后在弹出“排序”窗口的“要紧关键字”下拉列表中选择“总分”，并选中“递减”单选框，在“次要关键字” 下拉列表中选择“姓名”，最后单击[确定]按钮。
三.分数排行:假如需要将学生成绩按着学生的总分进行从高到低排序，当遇到总分一样的则按姓氏排序。
操作步骤如下：先选中所有的数据列，选择“数据→排序”，然后在弹出“排序”窗口的“要紧关键字”下拉列表中选择“总分”，并选中“递减”单选框，在“次要关键字” 下拉列表中选择“姓名”，最后单击[确定]按钮四、操纵数据类型在输入工作表的时候，需要在单元格中只输入整数而不能输入小数，或者者只能输入日期型的数据。
幸好Excel 2003具有自动推断、即时分析并弹出警告的功能。
先选择某些特定单元格，然后选择“数据→有效性”，在“数据有效性”对话框中，选择“设置”选项卡，然后在“同意”框中选择特定的数据类型，当然还要给这个类型加上一些特定的要求，如整数务必是介于某一数之间等等。
另外你能够选择“出错警告”选项卡，设置输入类型出错后以什么方式出现警告提示信息。
Excel快捷键大全个不容错过的实用技巧
Excel快捷键大全个不容错过的实用技巧Excel快捷键大全-个不容错过的实用技巧Excel 是一款广泛应用于日常办公和数据处理的电子表格软件。
熟练掌握 Excel 快捷键可以提高我们的工作效率，让操作更加便捷。
本文将为你详细介绍一些 Excel 快捷键技巧，帮助你更好地利用这些实用技巧。
一、基础操作快捷键1. Ctrl+C：复制选中的单元格或区域，可以在其他位置粘贴。
2. Ctrl+V：粘贴剪贴板上的内容到选定的单元格或区域。
3. Ctrl+X：剪切选中的单元格或区域，可以在其他位置粘贴。
4. Ctrl+Z：撤销上一步操作。
5. Ctrl+A：选择整个工作表或选定的区域。
6. Ctrl+S：保存当前工作表。
7. Ctrl+B：给选中的单元格设置粗体。
8. Ctrl+I：给选中的单元格设置斜体。
9. Ctrl+U：给选中的单元格添加下划线。
10. Ctrl+Y：重复上一步操作。
11. Ctrl+P：打开打印设置对话框。
12. Ctrl+F：打开查找对话框。
二、编辑操作快捷键1. F2：在选中的单元格中编辑内容。
2. F4：重复上一步操作的动作。
3. Ctrl+D：将上方单元格的内容复制到选中的单元格中，可用于填充。
4. Ctrl+R：将左侧单元格的内容复制到选中的单元格中。
5. Ctrl+H：打开替换对话框。
6. Alt+Enter：在单元格内换行。
7. F11：直接插入一个新的工作表。
三、格式化操作快捷键1. Ctrl+1：打开“格式单元格”对话框，可以进行各种格式设置。
2. Ctrl+Shift+#：将选中的单元格设置为日期格式。
3. Ctrl+Shift+$：将选中的单元格设置为货币格式。
4. Ctrl+Shift+%：将选中的单元格设置为百分比格式。
5. Ctrl+Shift+!：将选中的单元格设置为常规格式。
6. Ctrl+Shift+@：将选中的单元格设置为时间格式。
7. Ctrl+Shift+&：设置选中的单元格边框。
Excel快捷键大全掌握这些技巧让你的工作事半功倍
Excel快捷键大全掌握这些技巧让你的工作事半功倍Excel快捷键大全：掌握这些技巧让你的工作事半功倍Microsoft Excel是一款功能强大的电子表格软件，被广泛应用于数据分析、财务管理、项目计划等领域。
熟练掌握Excel的快捷键可以大幅度提高工作效率，将重复劳动自动化，让你的工作事半功倍。
本文将为你介绍一些常用的Excel快捷键，帮助你成为Excel的高手。
1. 单元格操作类快捷键1.1 移动单元格：- 上移一行：Ctrl+↑- 下移一行：Ctrl+↓- 左移一列：Ctrl+←- 右移一列：Ctrl+→1.2 选择单元格：- 选择整列：Ctrl+Space- 选择整行：Shift+Space- 选择当前区域：Ctrl+Shift+→（连续按下←或→可扩大区域）1.3 插入/删除操作：- 插入单元格：Ctrl+Shift+=- 删除单元格：Ctrl+-- 清除内容：Ctrl+Delete2. 常用功能类快捷键2.1 常用剪切/复制/粘贴：- 剪切：Ctrl+X- 复制：Ctrl+C- 粘贴：Ctrl+V- 复制以上单元格的格式：Ctrl+D2.2 撤销和重做：- 撤销操作：Ctrl+Z- 重做操作：Ctrl+Y2.3 自动填充：- 横向填充：选中一组单元格，按住Ctrl键拖动填充手柄- 纵向填充：选中一组单元格，按住Ctrl+Shift键拖动填充手柄3. 公式操作类快捷键3.1 输入公式：- 快速输入等号：直接按下=- 确认公式输入：按下Enter键3.2 常用函数快捷键：- 插入函数：Shift+F3- 快速选择函数：按住Ctrl键，按下A键，然后可以通过首字母进行函数选择- 插入函数参数：按住Ctrl+Shift+U键3.3 计算快捷键：- 重新计算工作表：F9- 打开/关闭公式计算追踪：Ctrl+[4. 数据操作类快捷键4.1 排序和筛选：- 快速排序：Ctrl+Shift+→（连续按下←或→可扩大区域）后按下Alt+D键，再按下S键- 自动筛选：Ctrl+Shift+L4.2 批量操作：- 填充数据：Ctrl+Enter（在选中的单元格中输入数据后按下）- 删除重复项：Alt+D键，再按下R键5. 格式操作类快捷键5.1 数字格式化：- 数字千分位：Ctrl+Shift+1- 带两位小数的百分比：Ctrl+Shift+5 5.2 文本格式化：- 设置斜体：Ctrl+I- 设置下划线：Ctrl+U- 设置粗体：Ctrl+B5.3 其他格式化：- 单元格对齐：Ctrl+1- 设置边框：Ctrl+Shift+76. 其他实用快捷键6.1 快速访问功能：- 进入编辑状态：F2- 快速访问Excel所有功能：按下Alt键6.2 单元格格式调整：- 自动调整行高：双击行号边界- 自动调整列宽：双击列字母边界掌握了以上的Excel快捷键，你将能够更加高效地处理数据、制作报表和分析图表。