办公软件 Excel技巧

zwrjt

办公软件 Excel技巧
常用办公软件 excel技巧的整理、讲解，在这里给读者们看一看，给大家一些提示，希望在你在平时能用得上。　　1、两列数据查找相同值对应的位置
　　=MATCH(B1，A：A，0)
　　2、已知公式得结果
4 Q' G4 i5 N" e　　定义名称=EVALUATE(Sheet1!C1)
　　已知结果得公式
　　定义名称=GET.CELL(6，Sheet1!C1)
* L- b8 M$ Y9 ]3 w　　3、强制换行
  z1 ^8 A8 J5 Z0 L& i! ~　　用Alt+Enter
/ @  B& ^0 I" |7 i3 n" O　　4、超过15位数字输入
　　这个问题问的人太多了，也收起来吧。一、单元格设置为文本;二、在输入数字前先输入'
6 T! o7 H& b  W　　5、如果隐藏了B列，如果让它显示出来?
# `2 M6 b# W% g% Y& }& |1 Y6 B　　选中A到C列，点击右键，取消隐藏
. v, q# X3 g/ K+ ?$ h+ F　　选中A到C列，双击选中任一列宽线或改变任一列宽
) N' B- f  B$ ~( q( z　　将鼠标移到到AC列之间，等鼠标变为双竖线时拖动之。
% \1 q2 C: S8 f( w/ X　　6excel中行列互换
: s0 n5 s5 @. ~+ {0 f9 i　　复制，选择性粘贴，选中转置，确定即可
　　7excel是怎么加密的
　　(1)、保存时可以的另存为>>右上角的"工具">>常规>>设置
　　(2)、工具>>选项>>安全性
　　8、关于COUNTIF
　　COUNTIF函数只能有一个条件，如大于90，为=COUNTIF(A1：A10，">=90")
　　介于80与90之间需用减，为 =COUNTIF(A1：A10，">80")-COUNTIF(A1：A10，">90")
　　9、根据身份证号提取出生日期
( L, A# H. W0 n& G　　(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))，"错误身份证号"))
　　(2)、=TEXT(MID(A2，7，6+(LEN(A2)=18)*2)，"#-00-00")*1
8 x: [# u4 ^' T; u2 `. H　　10、想在SHEET2中完全引用SHEET1输入的数据
0 p) o/ K2 `; P! J　　工作组，按住Shift或Ctrl键，同时选定Sheet1、Sheet2。
　　11、一列中不输入重复数字
* k/ _7 M$ @" D7 G/ n3 g% ~4 V　　[数据]--[有效性]--[自定义]--[公式]
　　输入=COUNTIF(A：A，A1)=1
# f* ]4 A1 `2 u3 ]+ ]　　如果要查找重复输入的数字
　　条件格式》公式》=COUNTIF(A：A，A5)>1》格式选红色
12、直接打开一个电子表格文件的时候打不开
( c# I5 q1 t- A. A, B1 q% J
　　“文件夹选项”-“文件类型”中找到.XLS文件，并在“高级”中确认是否有参数1%，如果没有，请手工加上
) J9 p  W" v  [( N6 `
　　13、excel下拉菜单的实现
' I: G  ?* }" G( V7 |1 d
: _+ j/ ]& x6 t& s) V# V& O　　[数据]-[有效性]-[序列]
8 ~. F+ k0 a, f8 N! ~# n
! l1 M7 h2 W3 m　　14、 10列数据合计成一列

　　=SUM(OFFSET($A，(ROW()-2)*10+1，，10，1))
3 k, J8 g$ y3 Z$ t) Q0 u
　　15、查找数据公式两个(基本查找函数为VLOOKUP，MATCH)
0 _" a% i$ k% \) ^# D, N
　　(1)、根据符合行列两个条件查找对应结果

) h% Q, t/ d; g: D/ g1 s　　=VLOOKUP(H1，A1：E7，MATCH(I1，A1：E1，0)，FALSE)
) r3 j3 c% P% b' v4 c8 Y
　　(2)、根据符合两列数据查找对应结果(为数组公式)

0 j' T/ d# d( V4 [' _+ i　　=INDEX(C1：C7，MATCH(H1&I1，A1：A7&B1：B7，0))
( W% F* X6 h8 i+ |# R
; G. P: ^: R& `& m  `. ?" }　　16、如何隐藏单元格中的0
  M, O" F  k& a$ i% f- d& j
& J0 W! Q& ^, ?8 d2 {2 w　　单元格格式自定义0;-0;;@ 或 选项》视图》零值去勾。呵呵，如果用公式就要看情况了。

　　17、多个工作表的单元格合并计算

　　=Sheet1!D4+Sheet2!D4+Sheet3!D4，更好的=SUM(Sheet1：Sheet3!D4)
( y; I* A4 @( M: w2 R
) y1 p+ Z2 v! \/ L. ~1 F　　18、获得工作表名称
7 l8 ]8 X- h+ v% g' P
　　(1)、定义名称：Name
7 H6 e( R* z# B6 |$ x8 L- q
　　=GET.DOCUMENT(88)
0 e3 _: w9 M; j1 G
　　(2)、定义名称：Path

　　=GET.DOCUMENT(2)
6 r7 O7 ^. _7 ?5 T2 @
) C6 ]7 x# p4 `2 Y2 v, @　　(3)、在A1中输入=CELL("filename")得到路径级文件名

　　在需要得到文件名的单元格输入
0 `" A/ u1 k/ G  z
$ z9 W! w2 P4 k+ w: t9 ]9 l　　=MID(A1，FIND("*"，SUBSTITUTE(A1，""，"*"，LEN(A1)-LEN(SUBSTITUTE(A1，""，""))))+1，LEN(A1))

　　(4)、自定义函数
. T+ s1 f& B! i9 c- ~6 j
( \; g+ M2 z/ Q2 x; e+ A! Y　　Public Function name()

- Y; b; M* R2 s　　Dim filename As String

　　filename = ActiveWorkbook.name

　　name = filename

& t& C" K; [' Q' u1 E! V* d　　End Function
" Z# ^! B' e. |$ ]( z( z1 ~1 x
( n+ Y- x0 \% s7 x" L5 _* p　　19、如何获取一个月的最大天数
7 C. f4 z# z$ B/ @
　　："=DAY(DATE(2002，3，1)-1)"或"=DAY(B1-1)"，B1为"2001-03-01

　　数据区包含某一字符的项的总和，该用什么公式

  O# ?. |, O3 ]　　=sumif(a：a，"*"&"某一字符"&"*"，数据区)
& @* E0 [0 L9 Y" J
　　最后一行为文本：

　　=offset($b，MATCH(CHAR(65535)，b：b)-1，)

) [* a0 _# H0 [: d2 g( O0 [/ c( Q　　最后一行为数字：
, L# s4 z$ ?) e. g/ [3 ^  S# P3 M$ a
7 K3 ?. c, N0 F3 P: S　　=offset($b，MATCH(9.9999E+307，b：b)-1，)

% X. y( o% N0 P　　或者：=lookup(2，1/(b1：b1000<>"")，b1：b1000)
3 h0 V% @: j, m
4 o4 k* b8 I2 B; N+ C7 j　　评委打分中，如何去掉两个以上最高分，两个以上最底分，求剩余人员的平均分?同时显示出被去掉的分数。
0 G5 U  S$ [& @; o: |( d0 J7 m
+ d( H2 {1 d, }" P" H+ T　　看看trimmean()函数帮助。

　　被去掉的分数：

　　最大两个：=large(data，)
" z, v1 f. z  n0 I4 k7 Z% S
　　最小两个：=small(data，)
. Z# h- a1 Y" S9 y# k; H
" u4 O4 S4 H; }/ s1 u( t　　怎样很简单的判断最后一位是字母

9 l" \# R3 W( P　　right(a1)*1

　　出错的字母
/ Q1 U- V7 g0 r  T& {
: [, H  @* j. P0 Z$ J' S. S2 n) b　　=IF(ISNUMBER(--RIGHT(A1，1))，"数字"，"字母")
# H6 K2 y7 f2 [  H7 ?; g
　　=IF(ISERR(RIGHT(A1)*1)，"字母"，"数字")
9 S$ V$ h" `$ d7 }3 ]
: R% ?' Y% k! I/ s　　如何 设置单元格，令其不接受包含空格的字符

4 ~; C  v5 W( s　　选定A列

　　数据——有效性——自定义——公式
, s$ T3 ~, b2 t
2 L1 ~: L& y. w. d/ X　　=iserror(find(" "，a1))
" }0 Y' Y: k9 _1 J
　　数据--有效性--自定义--公式
4 z2 \# e5 \: p) @  S+ }; z
　　=len(a1)=len(trim(a1))
% ?  A1 a) }( j% l& z+ T# I+ {
　　原来的函数是=a1+a4+a7+a10+a13+a16+a19+a22……
/ C; k' O/ c5 {% a$ m. K) v5 A
　　现在是=sum(n(offset(a1，(row(1：10)-1)*3，)))

/ d4 n% K$ o/ ]) i. W) }* Z. v　　在一个工作表中引用其他工作表中的数据，但是被引用的工作表不是固定的，根据我输入的工作表名自动选择相应的工作表中的数据，请问在公式里怎样引用?

4 ~; n& C: q+ `2 x1 T9 R　　=INDIRECT("A1"&"!"&"E1") A1为工作表名
1 r( ^) A$ K4 `6 R8 Q
* W; W3 A3 o0 W. }7 ~% p$ {　　奇数行求和 =SUMPRODUCT((A1：A1000)*MOD(ROW(A1：A1000)，2))

0 b/ C6 l% K2 Z( S3 A  L! k　　偶数行求和 =SUMPRODUCT((A1：A1000)*NOT(MOD(ROW(A1：A1000)，2)))

　　查看字符串字数

! y7 R8 U& d" u' |( s& g　　=LEN(A1)

　　求非空单元格数量

　　公式计算出来的数据，COUNTA不能用的(否则空字符也计算进去了)

　　=COUNTIF($E：$E536，"?*")

/ z9 s/ N( E0 z. M- g) h1 ]0 Q4 F$ P　　动态求和公式，自A列A1单元格到当前行前面一行的单元格求和.

　　=SUM(INDIRECT("A1：A"&ROW()-1))

　　20、比较好用的excel文档修复工具
3 e1 R+ K: k. ~1 e8 x9 B
" o# c) s7 j* ~4 a1 ^　　excelRecovery
. X/ J) \3 }0 F+ }) q  R
5 K# V8 c7 O7 l0 X( d/ w　　21、 excel开方运算
  X3 V5 `; F2 G
$ k  }% ~; b% d/ q1 w! }, T# {' H+ G　　将8开3次方，可以用这个公式，在单元格中输入　　=8^(1/3)
* p, V) P( \2 G% g
　　22、单元格中的数据分散对齐
& U" y, o6 T6 O3 q% U
, z; M. u9 g% @$ R　　文本格式》全角输入

% }! u) p; I6 Y5 k* _　　23、查找工作表中的链接
6 e% K* z$ d2 ]8 u. \
0 v, y" L# m' V　　Ctrl+~ 或编辑》链接
# R  q- T% o! q3 x5 U
, |8 X4 u3 S+ i8 X　　24、如何让空单元格自动填为0

　　选中需更改的区域》查找》空》替换》0
. D0 P' V! W  ^. S4 `2 v" o- n0 V
% e2 K( }2 i, C9 j' Z* h　　25、把word里的数字转换到excel
% t5 x9 ~) ^! ]7 {& h' z- v& m
9 C( @0 L. M* u      方法有多种，选中》复制》设置输入单元格为文本》选择性粘贴》值
2 u7 s- N7 H; q. f0 ~2 J
7 c: j0 l8 U) ~, c
　　选中》表格转换为文本》粘贴》分列》对分列选项设置为文本

5 E3 @' ]8 p6 T  A" w: v6 Q& D' G; K　　另存为文本文件》excel中打开文本文件》对导入文本对话框进行对应设置

dwcslg

学习了 ！！！！！！！！！！！！！！！！！！！！！

jiang_tao1535

谢谢搂住，学习一下！~

baintidy

非常非常有用，大家多学习～～～

takyva

先保存下来，慢慢学习！！！！！