找回密码
 立即注册

QQ登录

只需一步,快速开始

查看: 1085|回复: 0

[其它] 惊讶!!很多人都不会而又非常实用的Excel技巧,不看真的会后悔死!

[复制链接]
发表于 2015-7-8 21:32:00 | 显示全部楼层 |阅读模式
大家都会Excel,但很少能将它用到Excel-lent。其实,Excel绝不只是一个表格工具。

. ^5 L8 z. D& P3 v9 v# U
领英小编今天要带给你的是Microsoft Excel中尚少有人知的一些最强大、最实用的快捷键和函数。
' c3 i+ X0 A( \1 S; O6 ~
学会这些快准狠的技巧,你一定也能完胜“表哥”、“表姐”们。
1那些神奇的快捷键Shortcuts6 s* y, }/ Y! X$ V4 l; j
- d2 m6 D3 k" N) L; x& O

一次输入一串文字

7 d8 v9 a& N# j* K2 B

若要在某个单元格范围内快速输入相同的文本或公式,请执行下列步骤:0 N  j9 |- W; r$ O  J4 s2 E

  • 选择要填充的单元格范围。
  • 键入文本或公式,但是不要按 Enter 键,而是按 Ctrl+Enter
  • 数据将显示在您所选择的范围中。
    0 @* Z. D  B0 [8 K* G( ?2 j
    ; _  }4 e& [3 m$ `* a- F

0.gif


1 z( k6 G9 ?  F" s$ X& U7 _

一键插入日期和时间

7 M% |  c0 U# M! I+ b- w& s

如果希望在单元格中快速输入当前日期,请按 Ctrl+;,然后按 Enter 键。


% ~. Q5 z4 i% B2 E

要在单元格中快速输入当前时间,请按 Ctrl+:,然后按 Enter 键。


" }7 q4 W5 E/ S1 ]: ?; ~

1.gif

一键为上文的数值加和

. ?' u& v) D1 ~* E- n! k4 t+ n

求和的时候你是否有还是用函数?其实,只要在要求和的区域同时按住“Alt”和“=”,就可以快速求和。

& }6 p' z  K; f2 j/ V

2.png


5 _' w3 I4 U7 m! k8 t

2.gif


4 b. G, n) P3 K$ l! G( ?

调整数字格式

$ y1 r9 J6 @. j2 A6 |; z

使用Ctrl + Shift + 数字键,可以将数值转换为所需要的格式:" E3 u/ S, U3 I( X5 ?


1 u; N' ?( D( R8 e! X( K! w: ]( b! y
  • CRTL + SHIFT + `  常规格式
  • CTRL + SHIFT + 1  整数
  • CTRL + SHIFT + 2  0:00AM/PM 时间格式
    ) ?1 A) W6 E, q4 ~
  • CTRL + SHIFT + 3  1-JAN-00 日期格式
      m0 e. A4 a6 b2 N& g5 N
  • CTRL + SHIFT + 4  货币格式
    4 h5 g$ N+ \8 S2 m  Y9 B+ J) c; e
  • CTRL + SHIFT + 5  百分比格式
    ) ?7 ^8 X2 |7 d$ G- N# f9 [$ S
  • CTRL + SHIFT + 6  科学记数格式

    $ J; M1 ?4 j! w) u8 W

7 b3 R9 a/ i" w6 p5 e$ G8 x

3.gif

将文字变为图片,还能随时更新
, k( a9 c- U6 C( @+ Q

' T! x) k) D7 [# E6 n4 [- i* f; D+ L

你可以复制某个单元格范围,然后将结果图片粘贴到工作表中。图片会与单元格范围相链接,并会根据内容更改和格式更改进行更新。

8 N6 q$ S$ E! l5 c

4.gif


' T0 g: r- F; Y+ C* @. Q3 y

步骤如下:

  • 选择单元格范围。
  • 在“编辑”菜单上,单击“复制”。
  • 选择要在其中显示图片的单元格。
  • 按住 Shift 键,同时在“编辑”菜单上单击“粘贴图片链接”。
  • 结果会得到一个快照,当源单元格发生更改或格式变化时,该快照会随之更新。

    . D, {: k3 y+ S6 h+ {% i

" o( B" A! d, u1 W; h. G' U

F4键7 l. a  F, \  M1 p5 X


' i9 s+ }& p. @

在Excel中,F4 能发挥神奇的“重复上一步操作“功能。什么意思呢?


: u; V( M8 j( y/ J* I

比如我刚刚为某一段文本设置好字体、颜色、缩进、格式。选择另一个表格,再按F4,就自动把刚刚设置的动作再重复一遍,应用到现在新选择;


0 s# n4 D  d6 k$ U+ p! y

还可以干什么?做表格时候,“在下方添加新行”这样的命令,全部都可以用F4重复!


  n/ z3 D* q! V, G

5.gif

一键隐藏行、列

在Excel中,按下Ctrl+9,可以隐藏选定的行。

- |! ]  @: D  ?$ C6 k$ Y% |" t, z

按下Ctrl+0,可以隐藏选定的列。

# w  O% U7 ~8 `# ~. [( s7 @# u

6.gif

) c( p+ C) `4 X4 ~2 k

自动填充数据3 n4 w  \( t4 d  z" @6 s+ X. p- ?

. d2 K' n, P3 {- l$ y

您可以双击选定单元格的填充柄,沿某一列向下填充单元格内容,填充行数与相邻列的行数相同。

( g& p  @9 t( M' V

例如,如果在单元格 A1:A16 中键入了数据,在单元格 B1 中键入了一个公式或文本,按 Enter 键,然后双击填充柄(就是那个小小的黑点),则 Excel 将沿着该列,从单元格 B1 到单元格 B16 自上而下填充数据。

+ F7 E' e5 b! D, a1 O

7.gif

一键创建图表
6 j' q0 T* Q6 g% |

4 B% ]' L9 m& V

要快速创建图表,请选择图表数据,然后按 F11。要创建新的工作表,请按 Shift+F11。

' L  P7 a% }$ w( s' L. S* U

8.gif

0 z( K& \7 Q  `* q: x

双击分屏幕6 X5 K& d( G+ n  a6 I, d

5 P% O4 B/ z. U: s4 D

双击Excel右上角的小横杠,轻松将页面分为两屏!数据太多时同时看前面的内容,完全无压力。

3 c0 D, Y( H+ W$ [/ [# _! g

分屏后再双击分隔线,即可回到单屏模式。


1 o) Q4 L; `. E1 c7 \8 H0 h+ k

9.gif

8 }6 [9 A# z/ ~0 Q4 Z

冻结行列3 `5 x" p" A: u6 u% y

; o1 A1 d) M! P6 G" ?7 s) y8 X1 U8 {

冻结行列:或者由于数据太多,下拉后就不知道数据栏对应的项目是什么了,因此冻结第一列或第一行的很重要。


9 ^  |' z- n7 A/ U7 m: E- r

具体操作相当简单:视图→冻结窗口,如下图:

1 P$ n$ V0 l, K! h2 n2 E

3.png

. G" k- |* h" x3 e8 C! A/ J% Z

其他Excel快捷键大全

, L  D4 v: v4 @7 |/ N( |

请注意后面的英文单词。
& H, [7 e, u2 J


; e7 \) y& D" t2 z, M
  • CTRL + F 查找 find
  • CTRL + H 替换
  • CTRL + G 定位/转到 go to
  • CTRL + Y 重复上一次操作
  • CTRL + 1 设置单元格格式对话框
    ' u& x2 o, F* V1 e0 w: ~/ ?
  • CTRL + B / CTRL + 2 加粗 bold
  • CTRL + I / CTRL + 3 斜体 italic
  • CTRL + U / CTRL + 4 下划线 underline
  • CTRL + SHIFT + 7 添加细实线外边框
    ( E9 w$ v1 _  @. y  Y4 o
  • CTRL + SHIFT + - 清除区域内所有边框
  • ……
    & y1 G( ^0 U9 T
    ; {" R% Z! B- {! C
* I* u; [8 ^: K/ p7 R

( J. {. l$ Q, H4 w7 e  |% m/ M. E& m* u3 E
2那些神奇的函数和公式Formulas% i$ m% Y5 B& S% d

: _1 H+ N7 C3 }3 C7 H  W( w! m. y+ ?' |  a

插入当前的日期和时间+ ~( C8 [2 w# @; Y8 O4 R' `" w

0 `6 B, I9 l& _& l# p7 f# x4 A

想要知道当前的日期和时间,可以使用“Today”函数和“Now”函数。

2 `6 z( s' M) s# S! v

当输入“=TODAY()”时,返回当前日期。当输入“=NOW()”时,返回当前时间。

2 i, I, O9 M7 t  U, ~  G

10.gif

/ J: c$ |1 ]6 c& a. T- Z

6 B1 q. Z& l- Q' q8 G1 {' J2 b! V9 H

不管您何时打开工作薄,当需要在工作表上显示当前日期时,TODAY 函数非常有用。 它还可用于计算时间间隔。 例如,如果您知道某人出生于 1963 年,您可使用以下公式计算对方到其今年生日为止的年龄:

: ~0 o. }) O+ q

= YEAR( TODAY())-1963


0 q* [9 N) v) o

此外,假如你想知道今天是本年度的第几周,也可以通过将Today函数与Weeknum函数一起来使用。

4 l5 s2 B3 D/ q) w; i% u2 ~

=WEEKNUM(TODAY())
/ _4 n' j( a  h  G- T, H

; A  R# _" e" z3 j& x5 z: W

11.gif


! K, n) _/ L: g5 b% C$ @) v0 Q

插入随机数字. u* ?9 D6 I0 q

# i1 a4 G% q6 U, U

有时我们需要在表格中插入一些随机的数字和字符。这时可以使用RAND函数。


& z% K  e4 D4 o/ _/ U! B" F3 u+ P$ M

RAND函数返回大于等于0及小于1的均匀分布随机实数,其语法是:


. l. [! \" o% h) ?8 k3 {

=RAND()

12.gif

( O0 z+ k: D4 v4 |) r; Q3 L# t

如果要生成指定范围内的随机数字,可以使用RANDBETWEEN函数。


" h( z0 H# x, l6 l7 n5 z

RANDBETWEEN函数可以直接返回位于指定的两个数之间的一个随机整数,其语法是:

3 i, H( U$ _. K8 o; g8 D. [) W# z  ]7 G

RANDBETWEEN(bottom,top)

% d: J6 p" @' @* e# i

参数“bottom”为指定的最小整数,参数“top”为指定的最大整数。如公式:

1 H# `# I. A$ w$ H1 P+ ^

=RANDBETWEEN(1,100)

. a, o/ Q2 x& N1 k6 }" Y2 Z

返回1-100之间的随机整数,可能包含1或100。

% H% n  L0 \4 H- g# e. Y  y

或者你可以和领英小编一起来学学如何插入随机电话号码,比如138开头的吧:& A+ M0 v: f* M# }* \; l+ y( \. l

" w! F; T- q/ Q" J

13.gif


( m# w' n& Z5 L9 t: U2 p

将多个列中的文本联接起来
9 `0 d  E! G  P* a; J  V

. g- Q6 |# s8 ]; C

最简单而且最好用的一个“&”:可以合并单元格。


' m$ e# P4 {/ @

或者你还可以使用 & 运算符或 CONCATENATE 函数,将多个列中的文本连缀或合并起来。

  V& a6 u) M# u3 K; y$ G

例如,如果在单元格 A1:B14 中键入以下数据。要获得公司全名,请在单元格 C1 中,键入以下公式之一:


' G& l. g$ Q" t5 c7 g

=CONCATENATE(A1," ",B1)

或者

=A1&" "&B1

; R# S1 b7 g4 f+ k2 T

注意:单元格间的空格 (" ") 用于在显示文本间插入空格。

& h- U8 Z9 Z7 k! \

14.gif


6 Z5 b' P- Y6 G6 N  Y. X9 ?

快速录入的技巧


3 J5 k2 q+ t, o4 `: _2 F' h

比如要录入性别时,设置单元格格式-自定义:指定数字为特定代表,比如1=男,2=女。


, h; K( Z3 a) u9 x9 K

需注意的是,条件必须放在“[]”里。如下图。


6 X7 e, ^8 C' w

设置好之后,在录入数据时直接录入1和2,就会自动显示男和女了。

& L' w" ~) K& \7 l* t* D" y4 I

4.png

# c, L, m  o- W6 D

15.gif

3 w) O, G4 p4 N1 S) V( m

在Excel表格中加入斜线,这个可以有!! u8 _  F: e; X6 v5 b+ q+ L

4 d& u& |6 {, k0 L+ Q. n1 ]  }

先设置单元格格式,加入斜线。

3 {# u5 ^& B: {1 o& |1 X9 B

16.png

. e) I5 m$ Z9 K2 c, R

把国家和GDP放在两行,两行之间用Alt + enter 强制换行。先对单元格设置左对齐,将订购日期放在最左侧;然后在项目前加几个空格。

) _  Y  T7 w; P: Z3 W

17.gif


  B8 h+ r) g6 }2 |/ `

Vlookup大杀器!& Z) F, L1 m* ?' O3 J


3 d4 `2 H/ J5 a

Excel中功能超级强大的函数有很多,但Vlookup必须是其中最强悍的那个。


( s# p: f  }) f5 Y% F# w9 v

Vlookup相当于数据工作的四六级测试,而且问得深一些很容易测出学习能力。


6 M6 @( y/ H8 U7 [+ P1 V

Vlookup函数和数据透视表,必熟练掌握的技能而很多人不会,这两招可提高效率不知多少倍。但可惜太多的人还是用Ctrl+F的慢慢算…

7 }8 z: P/ h% m& y; @

VLOOKUP是一个查找和引用函数。例如,按员工号查找某位员工的姓氏,或通过查找员工的姓氏查找该员工的电话号码(就像使用电话簿)。
. }5 a3 m% z  L3 v1 {  p; z3 k

5 B3 r0 d) w  Q" v. p' ^) J

语法:VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

' M2 P) L: @% |5 p, E  J

18.png


3 p7 I; }/ @. @/ Q& D9 u2 Q

别着急,仔细看看这些参数都是什么概念:


' j8 b4 W9 P9 `6 v; X# H; k; C( i. G


  s2 D4 v6 J/ C' B


6 t7 d! g5 U& P; u+ Z4 T0 H  R

Vlookup可以用来在一个茫茫的数据源中,自动让Excel找出你要的某个数据的相关资料,并由此返回该值相对应当前行中指定列处的数值。

: }7 g2 L0 P9 M6 r- e; r8 v6 z0 g  @1 E

比如你要在这个表格中找到公司姓袁的员工,可以使用下面这个函数:0 T  ^8 U; Y! b  o5 T5 M/ C$ r# a


/ O  O) s# u4 c; E" P; Z$ C

=VLOOKUP("袁",B2:E7,2,FALSE)


( i0 M6 S+ H( U% }

19.gif

' z3 ^8 C' x7 O) Y8 I/ s

学习函数本身很容易,但是放在应用场景就会有很多问题,很多时候你会碰到,为什么取不到数,为什么出错,为什么取数错误。这些都能解决,你才算真正掌握Vlookup。# W+ @" Z# g( O. S0 x8 S

4 K- F6 k! `: V+ K; C5 g1 O- K

一些其他常用的函数


' B- A1 R5 ^. I) k0 N' k
  • SUM和SUMIF:求和/条件求和函数
  • AVERAGE和AVERAGEIF:求均值/条件均值函数
  • COUNT和COUNTIF:计量/条件计量函数
  • ROUND函数:四舍五入
  • TRUNC函数:取整
  • LEN函数:求字符数
  • MODE函数:出现频率最多值
  • FREQUENCY:数据频率分布函数
  • IF函数:按照条件返回相应的值
    : @: y& M2 P+ F0 j* D6 ]


+ `7 ~9 ]6 U9 i4 ?+ _8 O- C 20.png

# g! _; |* |: ^0 l5 {

  • DATEDIF函数:计算两个日期之间相隔的年/月/日。公式为=DATEDIF(起始日期,结束日期,"返回值") 返回值:y=年,m=月,d=天。
    ( F1 ^9 a/ Y1 m9 o, M' U( j


! b7 L) H4 Y: x" z6 [  y

21.png


1 I- n/ c% g: i1 {, _

拿DateIf函数计算各种纪念日,再合适不过啦!

4 {7 |6 X  i/ a+ A, z
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|站长QQ:22646427|Archiver|手机版|小黑屋|查规范网 ( 粤ICP备2024184902号-1 )|网站地图

GMT+8, 2026-2-2 19:21 , Processed in 0.161148 second(s), 33 queries .

Powered by Discuz! X3.5

© 2001-2025 Discuz! Team.

快速回复 返回顶部 返回列表