excel表格用公式求解~
excel\u8868\u683c\u4e2d\u7684\u6c42\u89e3\u516c\u5f0f\u4f7f\u7528\u5b8f\u88684.0\u51fd\u6570EVALUATE\u5b9a\u4e49\u540d\u79f0\u3002\u6bd4\u5982\u9009\u62e9B1\u5355\u5143\u683c\uff0c\u6309CTRL+F3\uff0c\u5b9a\u4e49\u540d\u79f0x
=EVALUATE(!A1)
\u7136\u540eB1\u8f93\u5165=x
A1\u8f93\u5165\u8ba1\u7b97\u5f0f\uff0c\u5c31\u53ef\u4ee5\u5728B1\u5f97\u5230\u7ed3\u679c\u4e86\u3002
\u8bf7\u53c2\u8003\uff1a
\u5982\u4f55\u8ba1\u7b97\u5e26\u6709\u5907\u6ce8\u6587\u5b57\u7684\u6587\u672c\u516c\u5f0f\uff1f http://www.exceltip.net/thread-1974-1-1-2.html
\u628a\u5206\u6570\u7ebf\u6362\u6210/\uff0c\u518d\u628a\u5206\u6bcd\u90e8\u5206\u7528\uff08\uff09\u62ec\u8d77\u6765\uff0c\u628a[ ]\u6539\u6210\uff08\uff09\uff0c\u5373Excel\u4e2d\u53ea\u7528\u5c0f\u62ec\u53f7\uff08\uff09\uff0c\u6839\u53f7\u4e0bT\u6539\u6210sqrt(T)\uff0c\u5176\u4ed6\u7684\u4e0d\u53d8
在H29输入=SUMIFS(H15:H28,G15:G28,"平方米")在H30输入=SUMIFS(H15:H28,G15:G28,"千克")
H29=sumif($g$15:$g$28,$g29,$h15:$h28)
j29=sumif($g$15:$g$28,$g29,$j$15:$j$28)
下拉即可
h29公式 =sumif(g$14:g$28,g29,h$14:h$28)
下拉公式到H30
=sumif($G$15:$G$28,$G29,H$15:H$28)
绛旓細鍏紡:B2=COUNTIF(鏁版嵁婧:浣嶇疆,鎸囧畾鐨勶紝鐩爣浣嶇疆)璇存槑锛氬鏋滆繑鍥炲煎ぇ浜0璇存槑鍦ㄥ彟涓涓〃涓瓨鍦紝0鍒欎笉瀛樺湪銆傚鏋滐紝鍦ㄦ绀轰緥涓墍鐢ㄥ埌鐨勫叕寮忎负锛欱2=COUNTIF(Sheet15!A:A,A2)2銆佺粺璁′笉閲嶅鐨勬绘暟鎹 鍏紡锛欳2=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))璇存槑:鐢–OUNTIF鍑芥暟缁熻鍑烘簮鏁版嵁涓瘡浜虹殑鍑虹幇娆℃暟锛...
绛旓細1锛岄鍏堬紝iferror鍑芥暟鍏紡鏄崟鍏冩牸涓殑涓绯诲垪鍊笺佸崟鍏冩牸寮曠敤銆佸悕绉版垨杩愮畻绗︾殑缁勫悎锛屽彲鐢熸垚鏂扮殑鍊笺傚叕寮忔绘槸浠ョ瓑鍙 (=) 寮濮嬨2锛屾墦寮excel鏂囨。锛屽湪琛ㄦ牸涓緭鍏ュ鍥炬墍绀虹殑涓夊垪锛屾眰涓ゆ暟鐩搁櫎鐨勫晢銆3锛屽湪瑕佹眰鍟鐨勮〃鏍澶勮緭鍏ュ叕寮忊=IFERROR(A2/B2,"闄ゆ暟涓嶈兘涓0")鈥濄4锛岀偣鍑诲洖杞﹂敭锛屽嚭鐜板鍥炬墍绀虹殑缁...
绛旓細1銆侀鍏堬紝鎵撳紑鐢佃剳涓婇潰鐨別xcel锛岀劧鍚庡湪閲岄潰杈撳叆涓浜涙暟鎹紝涓鑸父鐢ㄥ叕寮忎负锛氭眰鍜屻佸钩鍧囧笺佽鏁銆2銆侀変腑鍥剧ず涓崟鍏冩牸锛岀劧鍚庣偣鍑诲叕寮忥紝涔嬪悗鐐瑰嚮鑷姩姹傚拰銆3銆佸脊鍑虹殑鐣岄潰鍚庯紝鎸夊洖杞﹂敭銆4銆佺粨鏋滃鍥炬墍绀猴紝杩欐牱灏辩畻鍑烘眰鍜屽间簡銆5銆侀変腑鍥剧ず涓殑鍗曞厓鏍硷紝涔嬪悗鐐瑰嚮鍏紡锛岀偣鍑昏嚜鍔ㄦ眰鍜岀殑涓嬫媺绠ご锛屽脊鍑虹殑鐣岄潰锛岀偣鍑...
绛旓細浣跨敤鏍煎紡锛欳ONCATENATE(Text1锛孴ext鈥︹)鍙傛暟璇存槑锛歍ext1銆乀ext2鈥︹︿负闇瑕佽繛鎺鐨瀛楃鏂囨湰鎴栧紩鐢ㄧ殑鍗曞厓鏍笺傚簲鐢ㄤ妇渚嬶細鍦–14鍗曞厓鏍间腑杈撳叆鍏紡锛=CONCATENATE(A14,"@",B14,".com")锛岀‘璁ゅ悗锛屽嵆鍙皢A14鍗曞厓鏍间腑瀛楃銆丂銆丅14鍗曞厓鏍间腑鐨勫瓧绗﹀拰.com杩炴帴鎴愪竴涓暣浣擄紝鏄剧ず鍦–14鍗曞厓鏍间腑銆傜壒鍒彁閱掞細濡傛灉鍙傛暟...
绛旓細閫夋嫨鈥濇枃浠垛滐紝鐐瑰嚮鈥濋夐」鈥滐紱鍦ㄥ脊鍑虹殑excel閫夐」妗嗕腑鐐瑰嚮鈥濆姞杞介」鈥滐紝閫夋嫨鈥漞xcel鍔犺浇椤光滐紝鐐瑰嚮鈥濊浆鍒扳溿2锛夊湪寮瑰嚭鐨勫姞杞藉畯瀵硅瘽妗嗕腑鍕鹃"瑙勫垝姹傝В鈥濓紝鐐瑰嚮鈥滅‘瀹氣濓紱浜庢槸鍦ㄦ暟鎹夐」鍗′腑灏辨坊鍔犱簡姹傝В鍣╯olver宸ュ叿銆2.鐓ч鐩殑闄愬埗鏉′欢鍦╡xcel涓緭鍏ュ涓嬬殑琛ㄦ牸鍜鍏紡銆3.A鍜孊,鐨勬暟閲忓彲浠ョ暀绌猴紝浣嗘槸...
绛旓細H29=sumif($g$15:$g$28,$g29,$h15:$h28)j29=sumif($g$15:$g$28,$g29,$j$15:$j$28)涓嬫媺鍗冲彲
绛旓細姹傚钩鍧囧肩殑鍑芥暟鏄疉VERAGE銆傚洜姝わ紝鍦ㄢ鍏紡 鈥濆璇濇涓緭鍏ュ叕寮=AVERAGE(LEFT)锛屽嵆寰楀嚭缁撴灉銆傦紙娉細杩欓噷鏄宸︿晶鏁版嵁璁＄畻骞冲潎鍊硷紝鍖呮嫭浜嗗乏渚х殑鈥滃悎璁♀濆垪鏁版嵁锛夈傛彁绀猴細Word琛ㄦ牸涓殑鍗曞厓鏍间篃鏈夎鍙峰拰鍒楁爣銆傚洜姝わ紝鍦ㄤ功鍐欏叕寮忔椂锛屽彲浠浣跨敤Excel涓鏍风殑鍐欐硶锛=AVERAGE(b2:d2)锛屽嵆姹傝〃鏍涓璪2鍒癲2鍗曞厓鏍兼暟鎹...
绛旓細excel鍗曞厓鏍艰绠鍏紡鐨浣跨敤鏁欑▼锛氳绠鍏紡浣跨敤姝ラ1锛氶鍏堟柊寤轰竴涓〃鏍硷紝鎸夐渶瑕佺‘瀹琛ㄦ牸鐨妯″紡銆傚皢琛ㄥご璁惧畾濂斤紝杈撳叆鎵闇鐨勬暟鎹傝绠楀叕寮忎娇鐢ㄦ楠2锛氬湪杩欎釜宸ヨ祫琛ㄤ腑闇瑕佺敤鍒板姞娉(鍩虹宸ヨ祫+缁╂晥宸ヨ祫)鍜屽噺娉(宸ヨ祫鎬绘暟-鐥/浜嬪亣鎵鎵ｅ伐璧)涓や釜鍏紡銆傞紶鏍囩偣鍑诲疄闄呭伐璧勫緱鏁扮殑琛ㄦ牸锛岄敭鐩樻寜=閿紝寰楁暟鐨勮〃鏍间細鏄剧ず...
绛旓細鍦╡xcel琛ㄦ牸涓敤if鍏紡瀹炵幇濡備笅锛氭潯浠跺叕寮:=IF(OR(A1="",B1=""),"",IF(AND(A1=1,B1=0),2,IF(AND(A1=0,B1=1),1,"鏃犺В")))娉:if(or(A1=鈥溾濓紝B1=鈥溾)锛屸溾濓紝璁＄畻寮)鐢ㄦ潵鎺掗櫎鍗曞厓鏍煎唴绌哄艰绠楁垚閫昏緫0锛岄犳垚璇绠 鍑芥暟:And(鏉′欢1锛屾潯浠2)杩斿洖Ture/False if(鏉′欢锛孴ure杩斿洖...
绛旓細excel琛ㄦ牸浣跨敤杩愮畻鍏紡鏂规硶锛氫娇鐢ㄨ繍绠楀叕寮忔楠1锛氬湪Excel涓绠楀叕寮忎竴瀹氭槸鍏堢敤鈥=鈥濆彿寮澶达紝閭ｄ箞鍏堟壘鍒版诲垎鐨勭涓鏍间綅缃;浣跨敤杩愮畻鍏紡姝ラ2锛氭诲垎绛変簬鍏涔嬪拰锛岄偅涔堝湪鎬诲垎鐨勭涓鏍间綅缃墦鍑虹涓鏍忓悇绉戠洰鍒嗘暟鎵鍦ㄤ綅缃殑鍧愭爣:=c7+d7+e7+f7+g7+h7;浣跨敤杩愮畻鍏紡姝ラ3锛氭寜鍥炶溅閿紝灏辫兘寰楀埌鎴戜滑绗竴鏍忕殑...
