excel表格常用公式
一、数字处理1. 取绝对值：=ABS(数字)
2. 取整：=INT(数字)
3. 四舍五入：=ROUND(数字,小数位数)
二、判断公式
1. 把公式产生的错误值显示为空公式：=IFERROR(A2/B2,"")
2. IF多条件判断返回值公式：=IF(AND(A2<500,B2="未到期"),"补款","")
三、统计公式
1. 统计两个表格重复的内容公式：=COUNTIF(Sheet15!A:A,A2)
2. 统计不重复的总人数公式：=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))
四、求和公式
1. 隔列求和公式：=SUMIF($A$2:$G$2,H$2,A3:G3)
2. 单条件求和公式：=SUMIF(A:A,E2,C:C)
3. 多条件模糊求和公式：=SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11)
4. 多表相同位置求和公式：=SUM(Sheet1:Sheet19!B2)
5. 按日期和产品求和公式：=SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2:$B$25=$E2)*$C$2:$C$25)
五、查找与引用公式
1. 单条件查找公式：=VLOOKUP(B11,B3:F7,4,FALSE)
2. 双向查找公式：=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))
3. 查找最后一条符合条件的记录。
4. 字符串查找一对多公式：=IF(COUNT(FIND({"辽宁","黑龙江","吉林"},A2))=0,"其他","东北")
六、字符串处理公式
1. 多单元格字符串合并公式：=PHONETIC(A2:A7)
2. 截取左部分公式：=LEFT(D1,LEN(D1)-3)
3. 截取字符串中任一段的公式
4. 从输入的18位身份证号的出生年月计算公式
5. 字符串查找公式：=IF(COUNT(FIND("河南",A2))=0,"否","是")
七、日期计算公式
1. 两日期相隔的年、月、天数计算
2. 扣除周末天数的工作日天数公式：=NETWORKDAYS.INTL(IF(B2<DATE(2015,1,1),DATE(2015,1,1),B2),DATE(2015,1,31),11)
八、条件求和公式
1. 条件求和：=SUMIF(B2:B56,"男",K2:K56)
2. 多条件求和：={=SUM(IF(C3:C322="男",IF(G3:G322=1,1,0))) }
3. 优秀率：=SUM(K57:K60)/55*100
4. 及格率：=SUM(K57:K62)/55*100
九、其他实用公式
1. 最高分：=MAX(K2:K56)
2. 最低分：=MIN(K2:K56)
3. 分数段人数统计
4. 优秀率：=SUM(K57:K60)/55*100
5. 及格率：=SUM(K57:K62)/55*100
6. 标准差：=STDEV(K2:K56)
7. 条件求和：={=SUMIF(B2:B56,"男",K2:K56)}
十、数据整理与分析
1. 数据有效性定义数据长度
2. 条件格式避免重复
3. 排序问题
4. 巧用函数组合进行多条件的计数统计
5. 如何判断单元格里是否包含指定文本
6. 求A1:A100范围内不重复数据的个数
7. 一个工作薄中有许多工作表如何快速整理出一个目录工作表
绛旓細1. SUM鍏紡SUM鍏紡鏄疎XCEL涓渶甯哥敤鐨勫叕寮忎箣涓锛屽畠鐢ㄤ簬璁＄畻涓绯诲垪鏁板瓧鐨勬诲拰銆備緥濡傦紝SUM(A1:A10)琛ㄧず璁＄畻A1鍒癆10杩欎簺鍗曞厓鏍间腑鐨勬暟瀛楁诲拰銆2. AVERAGE鍏紡AVERAGE鍏紡鐢ㄤ簬璁＄畻涓缁勬暟瀛楃殑骞冲潎鍊笺備緥濡傦紝AVERAGE(A1:A10)琛ㄧず璁＄畻A1鍒癆10杩欎簺鍗曞厓鏍间腑鏁板瓧鐨勫钩鍧囧笺3. COUNT鍏紡COUNT鍏紡鐢ㄤ簬璁＄畻涓缁勬暟鎹腑鍖...
绛旓細鍏紡锛欼F(AND(鍗曞厓鏍硷紙閫昏緫杩愮畻绗︼級鏁板,鎸囧畾鍗曞厓鏍=杩斿洖鍊1),杩斿洖鍊2,)璇存槑锛氭墍鏈夋潯浠跺悓鏃舵垚绔嬫椂鐢ˋND,浠讳竴涓垚绔嬬敤OR鍑芥暟銆備笁銆佸父鐢ㄧ殑缁熻鍏紡 1銆佺粺璁″湪涓や釜琛ㄦ牸涓浉鍚岀殑鍐呭 鍏紡:B2=COUNTIF(鏁版嵁婧:浣嶇疆,鎸囧畾鐨勶紝鐩爣浣嶇疆)璇存槑锛氬鏋滆繑鍥炲煎ぇ浜0璇存槑鍦ㄥ彟涓涓〃涓瓨鍦紝0鍒欎笉瀛樺湪銆傚鏋滐紝鍦...
绛旓細涓銆佸熀纭鍏紡 1. SUM姹傚拰鍏紡锛氱敤浜庤绠楀崟鍏冩牸鎴栦竴绯诲垪鍗曞厓鏍间腑鐨勬暟鍊兼诲拰銆=SUM 瑙ｉ噴锛歋UM鍏紡鏄疎xcel涓渶甯哥敤鐨勫叕寮忎箣涓锛屽畠鍙互瀵规寚瀹氬崟鍏冩牸鎴栧尯鍩熺殑鎵鏈夋暟瀛楄繘琛屾眰鍜屻備緥濡傦紝瑕佽绠桝1鍒癆10杩欏崄涓崟鍏冩牸鐨勬暟瀛楁诲拰锛屽彧闇鍦ㄧ洰鏍囧崟鍏冩牸涓緭鍏モ=SUM鈥濄2. AVERAGE姹傚钩鍧囧煎叕寮忥細璁＄畻鍗曞厓鏍兼垨涓绯诲垪鍗...
绛旓細Excel鍑芥暟鍏紡澶у叏 宸ヤ綔涓渶甯哥敤Excel鍑芥暟鍏紡澶у叏涓銆佹暟瀛楀鐞1銆佸彇缁濆鍊=ABS(鏁板瓧)2銆佸彇鏁=INT(鏁板瓧)3銆佸洓鑸嶄簲鍏=ROUND(鏁板瓧,灏忔暟浣嶆暟)浜屻佸垽鏂叕寮1銆佹妸鍏紡浜х敓鐨勯敊璇兼樉绀轰负绌哄叕寮锛欳2=IFERROR(A2/B2,)璇存槑锛氬鏋滄槸閿欒鍊煎垯鏄剧ず涓虹┖锛屽惁鍒欐甯告樉绀恒2銆両F澶氭潯浠跺垽鏂繑鍥炲煎叕寮忥細C2=IF(AND...
绛旓細鍦―2鍗曞厓鏍间腑杈撳叆鍏紡锛歚=IF(AND(C2>=200, C2<=500), 300, 0)`銆傛鍏紡鍦ㄧ浉搴旀潯浠舵弧瓒虫椂鑷姩璁＄畻浣ｉ噾銆備簩銆佸鏉′欢鏌ユ壘鍏紡 LOOKUP鍑芥暟鑳藉瀹炵幇澶氭潯浠舵煡鎵俱備緥濡傦紝鍦ㄦ煡鎵句骇鍝侀攢鍞儏鍐垫椂锛屽浜庢Υ鑾插拰棣欒晧鐨勯攢鍞紝鍙互閫氳繃鐗瑰畾鐨勫叕寮忓揩閫熸壘鍒板搴旂殑閿鍞俊鎭備緥濡傦紝姒磋幉鐨勬煡鎵惧叕寮忎负锛歚=LOOKUP(1, 0/...
绛旓細濡備笅鍥炬墍绀猴紝濡傛灉閮ㄩ棬涓虹敓浜с佸矖浣嶄负涓绘搷 鏈夐珮娓╄ˉ鍔┿傚湪D鍒椾娇鐢鍏紡锛=IF(AND(B2="鐢熶骇",C2="涓绘搷"),"鏈","鏃")3銆佹潯浠舵眰鍜 濡備笅鍥炬墍绀猴紝浣跨敤SUMIF鍑芥暟璁＄畻涓鐝殑鎬绘垚缁╋細=SUMIF(D2:D5,F2,C2:C5)銆係UMIF鐢ㄦ硶鏄細=SUMIF(鏉′欢鍖哄煙,鎸囧畾鐨勬眰鍜屾潯浠,姹傚拰鐨勫尯鍩)4銆佸鏉′欢姹傚拰 濡備笅鍥...
绛旓細excel琛ㄦ牸甯哥敤鍑芥暟鍏紡澶у叏锛1銆丼UM鍑芥暟锛歋UM鍑芥暟鐨勪綔鐢ㄦ槸姹傚拰銆傚嚱鏁板叕寮忎负锛漵um锛堬級銆備緥濡傦細缁熻涓涓崟鍏冩牸鍖哄煙锛氾紳sum锛圓1:A10锛夌粺璁″涓崟鍏冩牸鍖哄煙锛氾紳sum锛圓1:A10锛孋1:C10锛夈2銆丄VERAGE鍑芥暟锛欰verage鐨勪綔鐢ㄦ槸璁＄畻骞冲潎鏁般傚嚱鏁板叕寮忎负锛滱VERAGE锛堬級銆備緥濡傦細璁＄畻澶氫釜鍗曞厓鏍肩殑骞冲潎鍊硷紳AVERAGE锛圓1:A12锛...
绛旓細1銆佹妸鍏紡浜х敓鐨勯敊璇兼樉绀轰负绌 鍏紡锛欳2 =IFERROR(A2/B2,"")璇存槑锛氬鏋滄槸閿欒鍊煎垯鏄剧ず涓虹┖锛屽惁鍒欐甯告樉绀恒2銆両F澶氭潯浠跺垽鏂繑鍥炲 鍏紡锛欳2 =IF(AND(A2<500,B2="鏈埌鏈"),"琛ユ","")璇存槑锛氫袱涓潯浠跺悓鏃舵垚绔嬬敤AND,浠讳竴涓垚绔嬬敤OR鍑芥暟銆備笁銆佺粺璁″叕寮 1銆佺粺璁′袱涓琛ㄦ牸閲嶅鐨勫唴瀹 鍏紡:B2...
绛旓細璇硶锛歋UM(number1,[number2],...)number1 锛堝繀闇鍙傛暟锛夎鐩稿姞鐨勭涓涓暟瀛椼 鍙互鏄叿浣撴暟瀛楋紝涔熷彲浠ユ槸鍗曞厓鏍煎紩鐢ㄦ垨鑰呭崟鍏冩牸鍖哄煙銆俷umber2锛岃繖鏄鐩稿姞鐨勭浜屼釜鏁板瓧銆傦紙2锛夊崟鏉′欢姹傚拰 SUMIF鍑芥暟鏄閫変腑鑼冨洿鍐呯鍚堟寚瀹氭潯浠剁殑鍊兼眰鍜屻俿umif鍑芥暟璇硶鏄細=SUMIF(range锛宑riteria锛宻um_range)sumif鍑芥暟...
绛旓細涓銆佹彁鍙栨暟鎹腑鐨勫敮涓鍊鍏紡锛=鍞竴杩欎釜鍑芥暟鏄竴涓柊鍑芥暟锛岀敤鏉ユ彁鍙栨暟鎹尯鐨勫敮涓鍊笺傜敤娉曪細=UNIQUE浜屻佽韩浠借瘉鍙风爜鎻愬彇鏁版嵁鎻愬彇鐢熸棩锛=text 锛 quot00-00 quot锛)鎻愬彇鏃堕棿锛=datedif 锛 quot00-00 quot锛)锛屼粖澶╋紝 quoty quot)鎻愬彇鎬у埆锛=IF锛2)=1锛 quot鐢锋 quot锛 quot濂 quot)杩欎釜宸茬粡...
