旗下网站:橡胶人才网

 找回密码
 注册
查看: 2101|回复: 4

比较常用的25条Excel技巧

[复制链接]
发表于 2007-11-23 18:30:53 | 显示全部楼层 |阅读模式

马上注册,结交更多胶友,享用更多功能!

您需要 登录 才可以下载或查看,没有账号?注册

×
1、两列数据查找相同值对应的位置 - S6 `4 y2 u1 F4 `8 k9 n) [. ?! `   =MATCH(B1,A:A,0)4 |/ ~, Y3 I# M/ h7 B; T$ }6 U $ p/ w# S5 q6 z/ M# u4 n* l2、已知公式得结果 7 W5 c( @* x( t: @( q& l# V$ y/ t9 p( j& y2 u$ {8 T, u: v   定义名称=EVALUATE(Sheet1!C1) s! t0 B9 \% U- V; z* }/ X4 I9 I0 k' j; @' f   已知结果得公式' S+ |' E0 y) }$ p9 h% v , p6 C& x' h) e2 d/ Z5 s- l  定义名称=GET.CELL(6,Sheet1!C1)& x: _7 M& C7 `3 c' j/ {1 J/ | 6 s+ D4 ~$ i& Z! ]3、强制换行/ h3 ~+ z; W$ m' |: ^ % `1 x! L0 `( F7 M3 G' c  用Alt+Enter 8 i- u) k. t) G, k% X: t B8 _3 N3 R& K* t" R2 ?% j* A8 l4、超过15位数字输入3 |- g6 Q8 H* [& y- m0 n, x. ` 1 O0 p- m) W; S. ^. M% d9 f b  这个问题问的人太多了,也收起来吧。一、单元格设置为文本;二、在输入数字前先输入' - a$ B5 ]8 a- B- Q0 K* _' }+ B9 B9 s2 g0 |- p" F3 l% o! @( Z 5、如果隐藏了B列,如果让它显示出来?* Q( ^/ |0 c9 p; ?3 E / ]6 C( s, \2 a" m   选中A到C列,点击右键,取消隐藏 * V+ Q1 f; C% L$ H; p5 a. \; W5 v. D( F$ ^: s+ o/ u   选中A到C列,双击选中任一列宽线或改变任一列宽 6 M1 r5 [3 h* Y( L. F% o* ?( ^. P, {6 l6 B4 R7 Q   将鼠标移到到AC列之间,等鼠标变为双竖线时拖动之。% n1 Z C2 U3 i+ [ J+ S8 Q+ k2 b" J6、EXCEL中行列互换 # t% ?* p' Z/ |: o5 K; h* y Y, y7 Q3 `4 W   复制,选择性粘贴,选中转置,确定即可 . O( y/ G3 X" P1 s: \# i+ w B1 t1 ^+ c5 F) v, _ 7、Excel是怎么加密的( d! |0 g- a; ` . L9 z$ {5 C+ _* H  (1)、保存时可以的另存为>>右上角的"工具">>常规>>设置! p9 [! g3 x' i* p 0 N# l3 `6 k& k1 _   (2)、工具>>选项>>安全性6 i; y a C4 X$ ]9 w - \, o9 o* A1 _9 I 8、关于COUNTIF4 t4 N' d/ _% h% n* a! L4 A$ _ 0 k- N( P: ~8 N8 \4 e' G. O  COUNTIF函数只能有一个条件,如大于90,为=COUNTIF(A1:A10,">=90") , l2 I/ J, N8 [" T- H. w" ?8 H( ]# T! ?   介于80与90之间需用减,为 =COUNTIF(A1:A10,">80")-COUNTIF(A1:A10,">90") 3 g" ?% R) i& H1 B3 a3 V/ P0 D/ ^; T 9 x/ M0 ~& m' T3 L, z9 l9、根据身份证号提取出生日期3 y* c- L9 a0 B' c # \6 v& b( |, A8 C u# ~3 Z  (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)),"错误身份证号"))' N: ~: g2 e4 A! P& K ( |% V7 Y9 q, J! X  (2)、=TEXT(MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")*1 # }' g6 W0 Z2 D* H ! D7 I2 @) C* t; O- M! [8 d10、想在SHEET2中完全引用SHEET1输入的数据5 {! {- N/ h2 P8 N & ^9 e- M: i/ T! N; T8 `  工作组,按住Shift或Ctrl键,同时选定Sheet1、Sheet2。 . r$ o$ j) S! I' P5 y$ a# N- k . I A3 {; F; N5 i) U: w/ \11、一列中不输入重复数字 [6 m5 ]# W* F0 o: u. y; ]* ?% t. I& C- u. P   [数据]--[有效性]--[自定义]--[公式] % w5 u/ ~1 U% E3 @ J6 w3 z: p1 p# M; I( ^. q6 k x   输入=COUNTIF(A:A,A1)=17 h" {/ x3 _8 |) |, ]+ M/ Q1 I0 s : L# J2 U& ~" b9 m# K7 F( S, D# ^  如果要查找重复输入的数字 ( |+ q3 w0 X1 O$ z+ E( P5 Y' |0 [5 q8 C" o B5 l! u. g   条件格式》公式》=COUNTIF(A:A,A5)>1》格式选红色: U- f, S: J9 t. ?9 T2 w7 W2 P & K b. U D: h% N( s: P12、直接打开一个电子表格文件的时候打不开 6 {$ o* `4 ]$ y" }$ _# A: ?& y T/ S7 J' z7 S5 c! j; i6 ~$ D   “文件夹选项”-“文件类型”中找到.XLS文件,并在“高级”中确认是否有参数1%,如果没有,请手工加上8 c: o9 s1 }: J8 T $ {7 `& f$ b1 l9 K+ o4 `% ? 13、excel下拉菜单的实现 3 O# w Z1 B9 E4 w2 y$ r5 B5 U! z, g3 D* S   [数据]-[有效性]-[序列] . g/ S0 B: z6 D$ w0 B( @1 ^8 g. M5 W2 X' z' h* Z( A# M. O3 G) G 14、 10列数据合计成一列7 f+ Z: e5 P7 ] ; M6 g1 h; X; R) n- d: n   =SUM(OFFSET($A$1,(ROW()-2)*10+1,,10,1))# L! p" d W' l/ O5 {5 u$ g + b; J! G9 ?) c- ]7 f15、查找数据公式两个(基本查找函数为VLOOKUP,MATCH) 4 t& i1 s8 N+ e( c( ?' W' T0 G, o% M% @1 I) O6 F# l   (1)、根据符合行列两个条件查找对应结果; R4 V# m, T0 J0 p# R 7 W7 r3 a& P2 A* v8 p+ [$ U5 Z) {  =VLOOKUP(H1,A1:E7,MATCH(I1,A1:E1,0),FALSE) ! Y; [1 w( n/ F6 B# x9 q4 s , f1 x+ s1 Q8 {4 I  (2)、根据符合两列数据查找对应结果(为数组公式) / T0 P- p6 x/ ?8 R 8 d; j1 Q6 c9 a" E( J0 Q9 i  =INDEX(C1:C7,MATCH(H1&I1,A1:A7&B1:B7,0))' m4 X6 h% ^% g% F8 @; f% y6 @& H6 p 0 Y- M+ r) x1 ]5 ^16、如何隐藏单元格中的0 7 J' {$ w2 Q( w! b % ]: |2 u+ ?7 d4 G' A! _  单元格格式自定义0;-0;;@ 或 选项》视图》零值去勾。呵呵,如果用公式就要看情况了。 ! `9 g( V; l/ ?* v( B9 y, ~% q 8 F ?+ Q5 L3 w- ~1 S- x17、多个工作表的单元格合并计算& G. p2 P2 P& ~ 0 Y1 z4 w& Q% C& X* j   =Sheet1!D4+Sheet2!D4+Sheet3!D4,更好的=SUM(Sheet1:Sheet3!D4)# K& D+ B; [7 @* A 5 Z6 `. X% k! F! ]+ g6 U j* i7 q 18、获得工作表名称% F; q( t$ {! p4 F# d4 N) [+ T 9 B) r9 X. E; U+ w. h  (1)、定义名称:Name 7 [( x9 t; Q; |5 H1 \$ O1 `; c) P6 @ & h5 N0 S/ ~6 x, x" Q; D  =GET.DOCUMENT(88)) k# z: R, H. w' J( E 3 P+ d3 w' Q" _  (2)、定义名称:Path 4 B0 |. b! ]% p/ d- Q8 m3 D! c# K/ T" w' I- o2 h4 x   =GET.DOCUMENT(2) , E7 M, E2 B' m( r/ y! p" p" p, j* k' x: k   (3)、在A1中输入=CELL("filename")得到路径级文件名 * D0 J2 q* D* B4 ?1 M! m' d* V ! ~4 V3 @: n; P  在需要得到文件名的单元格输入 ) L& Y+ s4 U9 q$ }% y' }7 V/ m$ Y1 ]$ ]; z. T   =MID(A1,FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,LEN(A1))! f p1 U1 M, y" E 5 C& W$ v% K/ o9 a   (4)、自定义函数 0 z3 I# d1 f; u# l; S# E & b) Z$ m. K) e( d7 h, Y U  Public Function name(); a: {$ o6 o3 a; L F8 F 7 y5 C7 l' ~! T" u+ n( p0 ?   Dim filename As String1 g2 r$ n; m4 A3 n8 m* f& |- Z 1 k. v. Y" r9 ^& E, M* H& l& n  filename = ActiveWorkbook.name0 k2 v. `. y% ~ - I2 [- y+ J/ |+ t  name = filename % U% @. @& `% y7 H6 S2 ]+ _4 K+ ?# K8 z- t) U0 P; j   End Function) {# i5 f. K7 {7 @' R8 B1 y! m' c4 R : S- ~5 @5 @! Q( e% M2 V" Y& V19、如何获取一个月的最大天数 4 p. b; ^- \& i9 C 9 R& k* F4 Z# J( g, O E! W  :"=DAY(DATE(2002,3,1)-1)"或"=DAY(B1-1)",B1为"2001-03-01 ! a7 d# m9 O$ c3 C8 x' K9 l# S! [6 s, G) g9 {   数据区包含某一字符的项的总和,该用什么公式5 r4 J9 y; Y6 N% r% _/ r1 d1 K ( _ ?, r$ K) \" _' j ^" h  =sumif(a:a,"*"&"某一字符"&"*",数据区)9 ]7 A0 A! L2 t* [6 u/ E " ~& n# g* e) F( b' Y$ Z8 _   最后一行为文本: . Q t w \& u6 a0 |5 Y4 R" g, n   =offset($b$1,MATCH(CHAR(65535),b:b)-1,)7 J! m8 |- Y! [- J) E% F3 A& e. r: N 4 K, H/ |2 A6 F; a7 z! r   最后一行为数字: 5 v% y: J: l& ]& r3 K! i. ^+ k 5 D( m B7 ~& | ?$ ^  =offset($b$1,MATCH(9.9999E+307,b:b)-1,) * A& |2 s/ \1 D, U" T$ K8 Q , D* ^1 k, W0 O5 `* s  或者:=lookup(2,1/(b1:b1000<>""),b1:b1000)0 |& U" m4 _+ J0 Z/ _3 w . x9 R. T l8 b l. r4 [4 F  评委打分中,如何去掉两个以上最高分,两个以上最底分,求剩余人员的平均分?同时显示出被去掉的分数。* Q% S+ R, d9 Q$ E ( y7 j' z2 u: b! z  看看trimmean()函数帮助。# U' Q' s5 ~. k2 V$ `: y, q ; q3 J8 ^/ L) F; m0 @' f. c& {6 a' v   被去掉的分数:" ]7 D s+ _6 w6 O$ p : O3 _2 R( T- [/ I   最大两个:=large(data,{1;2})( Z2 y7 m1 t+ l & P" W z% U' T) h& V5 s  最小两个:=small(data,{1;2})! w' W/ {5 v3 r' e% b' y 3 F5 u9 Q$ G. n" E" f  怎样很简单的判断最后一位是字母4 {: r9 ]) y4 ?, T. N * J! c0 J$ s x6 Y* _" n: J  right(a1)*1 3 i. p# O8 y- ?, q3 G; F, G% u+ C: c) z- [1 [$ s   出错的字母* Z; w7 d0 Z0 u 0 |5 S- x2 q( A1 r0 x7 ?+ I) w8 G   =IF(ISNUMBER(--RIGHT(A1,1)),"数字","字母") # T- W# M! p4 l# F! o9 J7 e6 t7 s0 N/ `6 D+ X8 S   =IF(ISERR(RIGHT(A1)*1),"字母","数字")6 S( T$ }4 |: n% A6 f : `% T+ z! N: m- t6 N  如何 设置单元格,令其不接受包含空格的字符 n) ~. F2 x) x5 X0 O * T* k# j9 p$ T- e  选定A列 1 g% j7 _/ n; X1 w% c; J& O # U4 v8 X4 N' C/ m5 P  数据——有效性——自定义——公式 ' T0 z" [2 j7 M+ G0 j% Q/ A& K* u/ D. ~* B7 [* a2 @3 e   =iserror(find(" ",a1)) , R+ w. C7 H* u: k 3 F) `4 Z; B3 ^/ [- T, A  数据--有效性--自定义--公式 ) J' n/ ]. z; |) P! b6 a: @- L2 N3 [7 x$ Z- W0 G- w0 N   =len(a1)=len(trim(a1))& {: I' f: @' X, O4 r, M 1 M, C( |. Z3 G! x  原来的函数是=a1+a4+a7+a10+a13+a16+a19+a22.....( m4 Q4 G9 {, U+ D+ C8 w 6 q' j7 Q$ }" N! q  现在是=sum(n(offset(a1,(row(1:10)-1)*3,))). e/ N" g2 i9 ^, ~5 {" S+ ?: @ / s* E! I* m! V/ L) t   在一个工作表中引用其他工作表中的数据,但是被引用的工作表不是固定的,根据我输入的工作表名自动选择相应的工作表中的数据,请问在公式里怎样引用? / R, u8 @& o4 P: ^1 J/ _& A8 R) z3 l( ]& F: [, q1 ?   =INDIRECT("A1"&"!"&"E1") A1为工作表名! n0 h) {" ]6 t9 B8 ]6 t - o8 L, h" _% L' n  奇数行求和 =SUMPRODUCT((A1:A1000)*MOD(ROW(A1:A1000),2)); t: [' V* t3 c( |- l) Z' E / G; G' r9 R4 x+ v9 x& @: l V  偶数行求和 =SUMPRODUCT((A1:A1000)*NOT(MOD(ROW(A1:A1000),2))) , L% W+ X% }& z8 |& _ ) N. s$ W ?# L. s/ i  查看字符串字数! m0 o+ c! `. D1 a. p, R% L9 r( } S; ?" u: i' {( Q7 S  =LEN(A1)" Z" S/ z% f% Z( q$ w* m , |8 |+ g' s4 S( m- Q0 X   求非空单元格数量# f5 C# f5 b: M5 p G& k2 K8 y) x3 j" m  公式计算出来的数据,COUNTA不能用的(否则空字符也计算进去了) 6 X: \" R2 C" o: [, L6 V: F' k$ O2 I' l+ p z7 [" l! `   =COUNTIF($E$3E$65536,"?*")- C c( ]! m2 f2 m& w, p* Y( j 0 I( I: Y7 L0 }* ^* j  动态求和公式,自A列A1单元格到当前行前面一行的单元格求和. x+ S2 |! F( P. z- ` [7 D/ H% @5 I% [0 S! k9 A" K   =SUM(INDIRECT("A1:A"&ROW()-1)) ) C9 e( }. b; O4 ~( Y+ v; |8 @& ? ) [( d- C/ D! I20、比较好用的EXCEL文档修复工具1 H7 m3 t: V' G7 i . D b, i1 [" a9 i4 o# D   ExcelRecovery) X; `) m" {8 ^6 V- P5 w8 I & x9 |8 |& h& D2 V# j8 C( `( h 21、EXCEL开方运算 q# K% O. i/ ]3 ?1 x, c5 t( B- s : B: z4 K( _1 X+ p+ x" M  将8开3次方,可以用这个公式,在单元格中输入  =8^(1/3) ' y: P! e. a. y# d1 h4 Q/ h- K- A X$ Q. u 22、单元格中的数据分散对齐 # l: b5 w* D- B8 v2 q5 g ( u: e- A% k8 ?6 z  文本格式》全角输入1 T& m k# S6 a . D# ]. G% w$ G- ~& i 23、查找工作表中的链接5 S/ L c$ u0 t0 x f" S2 u' W& l & K+ t% n& ]) `$ I. @   Ctrl+~ 或编辑》链接 : i" ^1 z. D( v$ c# O1 f) _( f- i1 { - j6 C. j% q% ~' A( }6 N24、如何让空单元格自动填为0$ e+ `" `7 |$ S 5 I4 i) [6 ?4 W8 G  选中需更改的区域》查找》空》替换》0 & c% v5 q# @+ }( c ^2 h * N' W% _. ~# d! |0 y25、把Word里的数字转换到Excel 4 ^. D* _, Y" N4 ` . T: ^: e+ G! A* x9 w- W) }  方法有多种,选中》复制》设置输入单元格为文本》选择性粘贴》值 7 V+ i! `4 b! H, i; \ . e+ T d7 m# U. u  选中》表格转换为文本》粘贴》分列》对分列选项设置为文本* t1 K5 n. a4 v- k1 i# t / w& C) I& e7 ^& B- b3 y. k   另存为文本文件》EXCEL中打开文本文件》对导入文本对话框进行对应设置( M6 V- ^- G1 {2 ?+ P; C
橡胶技术网 ,分享知识,创造价值! 一所没有围墙的大学!!
发表于 2007-11-23 19:37:48 | 显示全部楼层
不错
橡胶技术网 ,分享知识,创造价值! 一所没有围墙的大学!!
发表于 2007-11-23 23:37:39 | 显示全部楼层
非常有用,谢谢共享!~
橡胶技术网 ,分享知识,创造价值! 一所没有围墙的大学!!
发表于 2007-11-24 02:35:31 | 显示全部楼层
保存下来,以后统计就方便多了!
橡胶技术网 ,分享知识,创造价值! 一所没有围墙的大学!!
发表于 2007-12-24 23:48:13 | 显示全部楼层
好。
橡胶技术网 ,分享知识,创造价值! 一所没有围墙的大学!!
您需要登录后才可以回帖 登录 | 注册

本版积分规则

QQ|手机版|橡胶技术网. ( 沪ICP备14028905号 )

GMT+8, 2025-2-19 06:48 , Processed in 0.076526 second(s), 20 queries , Gzip On.

Powered by Discuz! X3.5 Licensed

© 2001-2025 Discuz! Team.

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