㈠ Excel濡备綍杩涜屽氭浔浠跺氱淮搴﹁＄畻锛
鍦‥xcel涓杩涜屽氭浔浠跺氱淮搴﹁＄畻闇瑕佺伒娲昏繍鐢ㄥ嚱鏁板拰绛涢夊櫒銆备互涓嬫槸镟磋︾粏镄勬ラゅ拰绀轰緥锛
1. 浣跨敤鍑芥暟:
Excel鎻愪緵浜嗕竴绯诲垪寮哄ぇ镄勫嚱鏁帮纴鍙浠ユ牴鎹鐗瑰畾𨱒′欢瀵规暟鎹杩涜岃＄畻銆傚叾涓涓変釜甯哥敤镄勫嚱鏁版槸SUMIF銆丄VERAGEIF鍜孋OUNTIF銆
- SUMIF鍑芥暟: 鐢ㄤ簬瀵规弧瓒虫寚瀹氭浔浠剁殑鍗曞厓镙艰繘琛屾眰鍜屻傚畠链変笁涓鍙傛暟锛氲寖锲淬佹浔浠跺拰瑕佹眰姹傚拰镄勮寖锲淬
渚嫔傦纴锅囱炬湁涓涓阌鍞鏁版嵁镄勮〃镙硷纴鍏朵腑绗涓鍒楁槸浜у搧钖岖О锛岀浜屽垪鏄阌鍞棰濄傝佽＄畻镆愪釜浜у搧镄勯攒鍞镐婚濓纴鍙浠ヤ娇鐢ㄤ互涓嫔叕寮忥细
=SUMIF(浜у搧鍒楄寖锲, "鎸囧畾浜у搧", 阌鍞棰濆垪锣冨洿)
- AVERAGEIF鍑芥暟: 鐢ㄤ簬瀵规弧瓒虫寚瀹氭浔浠剁殑鍗曞厓镙艰繘琛屽钩鍧囧艰＄畻銆傚畠镄勫弬鏁颁笌SUMIF鍑芥暟鐩稿悓銆
涓句釜渚嫔瓙锛屽傛灉瑕佽＄畻镆愪釜浜у搧镄勫钩鍧囬攒鍞棰濓纴浣跨敤浠ヤ笅鍏寮忥细
=AVERAGEIF(浜у搧鍒楄寖锲, "鎸囧畾浜у搧", 阌鍞棰濆垪锣冨洿)
- COUNTIF鍑芥暟: 鐢ㄤ簬璁＄畻婊¤冻鎸囧畾𨱒′欢镄勫崟鍏冩牸鏁伴噺銆傚畠链変袱涓鍙傛暟锛氲寖锲村拰𨱒′欢銆
锅囱捐佺粺璁℃煇涓浜у搧镄勯攒鍞璁板綍鏁帮纴鍙浠ヤ娇鐢ㄤ互涓嫔叕寮忥细
=COUNTIF(浜у搧鍒楄寖锲, "鎸囧畾浜у搧")
杩欎簺鍑芥暟鍙浠ユ牴鎹鐗瑰畾𨱒′欢瀵规暟鎹杩涜岀瓫阃夛纴骞朵笖鍙浠ュ湪澶嶆潅镄勫氭浔浠跺満鏅涓浣跨敤阃昏緫杩愮畻绗︼纸濡侫ND銆丱R锛夎繘琛岀粍钖堛
2. 浣跨敤绛涢夊櫒:
Excel镄勭瓫阃夊櫒锷熻兘鍏佽告偍鎸夌収澶氢釜𨱒′欢绛涢夋暟鎹骞惰＄畻缁撴灉銆傞氲繃浣跨敤镊锷ㄧ瓫阃夊櫒锛屾偍鍙浠ョ伒娲诲湴绛涢夊拰姹囨绘暟鎹銆
- 阃夋嫨瑕佺瓫阃夌殑鏁版嵁鍖哄烟銆
- 鍗曞嚮"鏁版嵁"阃夐”鍗′笂镄 "绛涢" 鎸夐挳锛屽皢镓揿紑镊锷ㄧ瓫阃夊櫒銆
- 鍦ㄦ疮涓鍒楁爣棰树笂閮戒细鏄剧ず涓嬫媺绠澶达纴鍗曞嚮绠澶翠互阃夋嫨瑕佺瓫阃夌殑𨱒′欢銆
- 鎭ㄥ彲浠ラ夋嫨鏂囨湰銆佹暟瀛椼佹棩链熺瓑涓嶅悓绫诲瀷镄勮繃婊ゆ浔浠讹纴骞惰剧疆鍙傛暟銆
- 镙规嵁闇瑕侀夋嫨澶氢釜𨱒′欢锛屽苟鎸変笅纭璁ゆ寜阍浠ュ簲鐢ㄧ瓫阃夊櫒銆
- 鍦ㄧ浉鍏冲崟鍏冩牸涓浣跨敤姹囨诲嚱鏁帮纸濡伞丄VERAGE锛夎＄畻宸茬瓫阃夋暟鎹镄勭粨鏋溿
绛涢夊櫒锷熻兘浣挎偍鑳藉熷姩镐佽皟鏁村拰搴旂敤澶氢釜𨱒′欢锛屽悓镞舵煡鐪嫔拰鍒嗘瀽鏁版嵁銆傛偍鍙浠ユ牴鎹闇瑕佹坊锷犮佷慨鏀规垨鍒犻櫎𨱒′欢锛屼互渚挎洿绮剧‘鍦拌＄畻瑕佹眰銆
缁间笂镓杩帮纴Excel鎻愪緵浜嗕赴瀵岀殑鍑芥暟鍜岀瓫阃夊櫒锷熻兘锛屽湪澶氭浔浠跺氱淮搴﹁＄畻涓鍙戞尌镌閲嶈佷綔鐢ㄣ傛偍鍙浠ユ牴鎹鍏蜂綋闇姹备娇鐢ㄩ傚綋镄勫嚱鏁拌繘琛岃＄畻锛屾垨钥呭埄鐢ㄧ瓫阃夊櫒锷熻兘瀵规暟鎹杩涜岀伒娲荤瓫阃夊拰姹囨汇傞氲繃缁揿悎鍑芥暟鍜岀瓫阃夊櫒镄勫簲鐢锛屾偍鑳藉熷揩阃熴佸嗳纭鍦拌幏鍙栨墍闇镄勭粺璁＄粨鏋滐纴骞惰交𨱒惧簲瀵瑰嶆潅镄勫垎鏋愪换锷°
㈡ excel琛ㄦ牸瀵圭浉钖岀被鍨嬫暟鎹镐庝箞姹囨诲湪excel涓濡备綍瀵圭浉钖屾暟鎹杩涜屾眹镐
鍦 Excel 涓瀵圭浉钖岀被鍨嬬殑鏁版嵁杩涜屾眹镐绘湁澶氱嶆柟娉曪纴浠ヤ笅鏄鍏朵腑鍑犵嶅父鐢ㄦ柟娉曪细
1. 浣跨敤 SUMIF 鍑芥暟銆係UMIF 鍑芥暟鍙浠ユ牴鎹鎸囧畾𨱒′欢瀵规暟鎹杩涜屾眰鍜屻备緥濡傦纴濡傛灉瑕佹眹镐绘煇涓鍒椾腑绗﹀悎镆愪釜𨱒′欢锛埚傗滃晢鍝丄钬濓级镄勬墍链夋暟鍊硷纴鍙浠ヤ娇鐢ㄧ被浼间簬浠ヤ笅镄勫叕寮忥细=SUMIF(A2:A100,"鍟嗗搧A",B2:B100) 銆
2. 浣跨敤 PivotTable 姹囨汇侾ivotTable 鏄 Excel 涓寮哄ぇ镄勬眹镐绘暟鎹镄勫伐鍏凤纴鍙浠ュ揩阃熷湴瀵瑰ぇ閲忔暟鎹杩涜屽垎绫汇佹眹镐汇佺瓫阃夌瓑镎崭綔銆傚湪 PivotTable 涓锛屽彲浠ラ夋嫨瑕佹眹镐荤殑鏁版嵁鍖洪棿锛岀劧钖庢寜闇瀵硅屻佸垪銆佸艰繘琛岃剧疆锛屾渶钖庡嵆鍙鐢熸垚姹囨绘姤琛ㄣ
3. 浣跨敤 FILTER 鍑芥暟銆侳ILTER 鍑芥暟鍙浠ユ牴鎹鎸囧畾𨱒′欢瀵规暟鎹杩涜岀瓫阃夛纴骞惰繑锲炵﹀悎𨱒′欢镄勬暟鎹銆傚傛灉瑕佹眹镐绘煇涓鍒椾腑绗﹀悎镆愪釜𨱒′欢锛埚傗滃晢鍝丄钬濓级镄勬墍链夋暟鍊硷纴鍙浠ヤ娇鐢ㄧ被浼间簬浠ヤ笅镄勫叕寮忥细=SUM(FILTER(B2:B100,A2:A100="鍟嗗搧A"))銆
4. 浣跨敤镊锷ㄧ瓫阃夊姛鑳姐傚湪 Excel 涓锛屽彲浠ヤ娇鐢ㄨ嚜锷ㄧ瓫阃夊姛鑳藉规暟鎹杩涜岀瓫阃夈傞栧厛闇瑕侀変腑鏁版嵁鍖哄烟锛岀劧钖庣偣鍑烩沧暟鎹钬濋夐”鍗′腑镄勨灭瓫阃夆濇寜阍锛岄夋嫨瑕佺瓫阃夌殑瀛楁碉纴骞惰剧疆绛涢夋浔浠躲傛渶钖庡嵆鍙阃氲繃镆ョ湅姹囨讳俊鎭𨱒ヤ简瑙ｇ﹀悎𨱒′欢镄勬暟鎹銆
杩欎簺鏂规硶钖勬湁浼樼己镣癸纴镙规嵁鍏蜂綋𨱍呭喌阃夋嫨钖堥傜殑鏂规硶鍗冲彲銆
1.镓揿紑涓涓闇瑕佺紪杈戠殑excel琛ㄦ牸锛岀偣鍑烩沧暟鎹钬濊彍鍗曘
2.镣瑰嚮钬滃悎骞惰＄畻钬濄
3.鍑芥暟閲岄夋嫨钬沧眰鍜屸濄
4.寮旷敤浣岖疆阃夋嫨镓链夎〃镙硷纴镣瑰嚮钬沧坊锷犫濇寜阍銆
5.鍕鹃夆滈栬屸濆拰钬沧渶宸﹀垪钬濓纴镣瑰嚮钬灭‘瀹气濇寜阍銆傝繖镙峰氨瀹屾垚浜嗙浉钖屽悕绉扮殑鏁版嵁姹囨汇
鍦 Excel 琛ㄦ牸涓锛岃佸圭浉钖岀被鍨嬬殑鏁版嵁杩涜屾眹镐伙纴鍙浠ヤ娇鐢ㄦ暟鎹阃忚呜〃𨱒ュ疄鐜般傚叿浣撴ラゅ备笅锛
1. 阃変腑瑕佹眹镐荤殑鏁版嵁鍖哄烟銆
2. 鍦ㄢ沧彃鍏モ濋夐”鍗′腑锛岄夋嫨钬沧暟鎹阃忚呜〃钬濄
3. 鍦ㄥ脊鍑虹殑钬滃垱寤烘暟鎹阃忚呜〃钬濆硅瘽妗嗕腑锛屽皢阃変腑鍖哄烟镄勮寖锲存寚瀹氢负鏁版嵁婧愶纴铹跺悗阃夋嫨镓闇镄勫瓧娈碉纸鍒楁爣绛俱佽屾爣绛俱佸硷级𨰾栨斁鍒板悎阃傜殑鍖哄烟銆
4. 鍦ㄢ滃尖濆尯锘熶腑锛岄夋嫨闇瑕佹眹镐荤殑鏁板煎瓧娈碉纴骞跺皢缁熻℃柟寮忚剧疆涓衡沧眰鍜屸濄佲滃钩鍧囧尖濈瓑銆
5. 濡傛灉闇瑕佸硅屾爣绛炬垨鍒楁爣绛捐繘琛屽垎绫绘眹镐伙纴鍙浠ュ皢涓嶅悓镄勬爣绛炬嫋鏀惧埌瀵瑰簲镄勫尯锘熶腑銆
6. 鍦ㄨ剧疆濂芥暟鎹阃忚呜〃钖庯纴灏卞彲浠ラ氲繃瀵硅屾爣绛惧拰鍒楁爣绛捐繘琛屽𪾢寮鎴栨姌鍙狅纴镆ョ湅涓嶅悓鍒嗙粍涓嬬殑姹囨绘暟鎹浜嗐
鏁版嵁阃忚呜〃鍙浠ュ勭悊澶嶆潅镄勬暟鎹姹囨婚渶姹傦纴鑳藉熷揩阃熺殑璁＄畻鍜屽𪾢绀哄ぇ閲忔暟鎹镄勪俊鎭銆傚傛灉浣犳兂杩涗竴姝ヤ简瑙ｆ暟鎹阃忚呜〃镄勪娇鐢ㄦ柟娉曞拰楂樼骇锷熻兘锛屽彲浠ユ煡鐪 Excel 镄勫府锷╂枃妗ｆ垨钥呯浉鍏崇殑鏁欑▼鍜岃嗛戙