有excel中，对H列单元格求和，值等于G列单元格，H1=G1，H2=G1+G2，H3=G1+G2+G3，..........如何用公式！ excel数据无法自动求和 递推公式
EXCEL\u8868\u683c\u4e2d\u6709\u51e0\u4e2a\u516c\u5f0f\u4e0d\u61c2\uff1a =IF(SUMIF(I2:I1000,"\u53ef\u6e05",G2:G7)=0,"\u221a","\u00d7")=IF(SUMIF(I2:I1000,"\u53ef\u6e05",G2:G7)=0,"\u221a","\u00d7")
\u5982\u679cI2\u5230I1000\u5185\u201c\u53ef\u6e05\u201d\u5bf9\u5e94G2\u5230G7\u4e2d\u6570\u91cf\u7684\u548c\u4e3a0\uff0c\u5c31\u6253\u221a\uff0c\u5426\u5219\u6253\u00d7
=IF(OR(LEN(E2)=0,E2=0),-F2,E2)
\u5982\u679cE2\u5355\u5143\u683c\u5185\u7684\u503c\u4e3a0\u6216\u957f\u5ea6\u4e3a0\uff0c\u5219\u8fd4\u56de-E2\uff0c\u5426\u5219\u8fd4\u56deE2
=ABS(G2)
\u8fd4\u56deG2\u7684\u7edd\u5bf9\u503c
=IF(D2=1,"\u53ef\u6e05",IF(OR(AND(COUNTIF(H:H,H2)=2,H2=H1,G2+G1=0),AND(COUNTIF(H:H,H2)=2,H2=H3,G2+G3=0)),"\u53ef\u6e05",IF(SUMIF(H:H,H2,G:G)=0,"\u914c\u60c5\u6e05\u7406","\u4e0d\u53ef\u6e05")))
\u5982\u679cD2\u7684\u503c\u4e3a1\uff0c\u8fd4\u56de\u201c\u53ef\u6e05\u201d\uff1b\u5982\u679cH\u5217\u4e2d\u503c\u4e3aH2\u7684\u67092\u4e2a\uff0c\u4e14H2\u548cH1\u5185\u5bb9\u76f8\u540c\uff0cG1\u3001G2\u548c\u4e3a0\uff0c\u4e5f\u8fd4\u56de\u201c\u53ef\u6e05\u201d\uff1b\u5982\u679cH\u5217\u4e2d\u542bH2\u503c\u5bf9\u5e94\u7684G\u5217\u7684\u548c\u4e3a0\uff0c\u8fd4\u56de\u201c\u914c\u60c5\u6e05\u7406\u201d\uff1b\u5176\u4ed6\u7684\u8fd4\u56de\u201c\u4e0d\u53ef\u6e05\u201d\u3002
\u5e0c\u671b\u5bf9\u4f60\u6709\u5e2e\u52a9\uff01
\u5728H1\u5355\u5143\u683c\u8f93\u5165\u4ee5\u4e0b\u516c\u5f0f\uff0c\u7136\u540e\u5411\u4e0b\u586b\u5145\u516c\u5f0f
=SUM(G$1:G1)
=SUM(G$1:G1)
下拉
=sum(g$1:g1)
下拉
绛旓細=SUM(G$1:G1)涓嬫媺
绛旓細涓,淇濊瘉H鍒鏁版嵁涔嬮棿娌℃湁绌鸿.浜,鍦ㄦ渶鍚庝竴琛屾暟鎹殑涓嬩竴琛,姣斿鏈鍚庝竴琛屼负H300,閭ｅ氨閫変腑H301,鎸堿LT+=.涓,鑻ヨ缁х画澧炲姞鏁版嵁,璇峰湪璁剧疆鍏紡鐨勮涔嬩笂杩涜澧炲姞涓琛,鐒跺悗杈撳叆鏁版嵁.姣斿鍏紡璁剧疆濂藉悗,瑕佺户缁鍔犱竴琛,灏遍変腑H301,鐒跺悗鎸塁TRL+灏忛敭鐩樹笂+鍙....
绛旓細1.杈撳叆鍏紡濡備笅锛2.灏卞彲浠ュ緱鍒H鍒鎵鏈夋暟鎹殑鍜岋細
绛旓細鍦℅鍒楃殑绗竴涓渶瑕佹眰缁撴灉鐨勫崟鍏冩牸涓緭鍏モ=鈥濃滃搴旂殑H鏍肩殑閭ｄ釜閭ｄ釜鏍肩紪鍙封濓紝鍐嶄箻浠ュ簲涔樼殑鏁板瓧锛屾寜鈥滅‘璁も濓紝绗竴涓粨鏋滃氨鍑烘潵浜嗐備笅闈㈢殑鍏堥夊畾G鍗曞厓鏍煎皢榧犳爣绉昏嚦鍙充笅瑙掑緟榧犳爣鍙樻垚榛戣壊鍗佸瓧鐘跺啀渚濇寰涓嬫媺锛岀粨鏋滃氨鑷姩鍑烘潵浜嗐傦紙濡傦細H鍒楁暟瀛楀崟鍏冩牸涓篐3锛屼箻鏁颁负10锛屽垯鍦℅3鍗曞厓鏍间腑杈撳叆鍏モ=H3*...
绛旓細鍦H鍒楀崟鍏冩牸杈撳叆锛氾紳F1*G1锛屾暡鍥炶溅銆傜劧鍚庢妸榧犳爣鏀惧湪杩欎釜鍗曞厓鏍煎彸涓嬫柟锛屽綋榧犳爣鍙樻垚鍗佸瓧鏄寜浣忥紝寰涓嬫嫋鈥︹﹁澶氬皯鎷栧灏
绛旓細1銆佸鍥撅紝Excel琛ㄦ牸涓璈鍒鐨鍗曞厓鏍鏁板艰闄愬畾浜嗭紝褰撹緭鍏ュ叾浠栨暟鍊兼椂锛屼細鎻愮ず杈撳叆鍊奸潪娉曘傜偣鍑诲叧闂繖涓彁绀哄皬绐楀彛銆2銆侀変腑H鍒楃殑鎵鏈夊崟鍏冩牸锛岀偣鍑讳笂闈㈢殑鏁版嵁閫夐」銆3銆佸湪鏁版嵁涓嬮潰閫夋嫨鏁版嵁鏈夋晥鎬т腑鐨勨滄暟鎹湁鏁堟(V)...鈥濄4銆佸湪寮瑰嚭鐨勬暟鎹湁鏁堟у皬绐楀彛涓紝鐐瑰嚮宸︿晶搴曢儴鐨勫叏閮ㄦ竻闄わ紙鎴栬呭湪鏈夋晥鎬ф潯浠朵笅闈㈢殑...
绛旓細鍦‥xcel鐨勪竴涓鍗曞厓鏍间腑锛杈撳叆浠ヤ笅鍏紡锛=SUMPRODUCT(ROW(INDIRECT("1:"&H))*3)杩欎釜鍏紡浣跨敤浜 SUMPRODUCT 鍑芥暟鍜 ROW 鍑芥暟鏉ヨ绠椾箻绉眰鍜屻俁OW 鍑芥暟杩斿洖鍗曞厓鏍肩殑琛屽彿锛孖NDIRECT 鍑芥暟灏嗘枃鏈浆鎹负鑼冨洿寮曠敤銆備箻绉殑鍥犲瓙鏄 3锛岃〃绀烘偍瑕佷箻浠ョ殑甯告暟銆傚皢鍏紡涓殑 "H" 鏇挎崲涓烘偍瑕佷娇鐢ㄧ殑瀹為檯鍊笺傚湪杩欑鎯呭喌...
绛旓細鏉愭枡/宸ュ叿锛Excel2010 1銆佹墦寮涓寮犲伐浣滆〃鏍煎涓嬪浘锛屾垜浠渶瑕瀵硅〃鏍杩涜闅斿垪姹傚拰锛岀瓟妗堝垎鍒湪I鍒楀拰J鍒椼2銆佸厛绠桰鍒楃殑鏁板硷紝閫変腑I3鍗曞厓鏍硷紝鍦ㄥ崟鍏冩牸涓緭鍏モ=鈥濓紝鐐瑰嚮銆愭彃鍏ュ嚱鏁般戞寜閽3銆佸湪寮瑰嚭鐨勩愭彃鍏ュ嚱鏁般戝璇濇涓夋嫨鈥淪UMIF鈥濆嚱鏁帮紝鐒跺悗鍗曞嚮銆愮‘瀹氥戞寜閽4銆佸脊鍑恒愬嚱鏁板弬鏁般戝璇濇銆傜偣鍑荤...
绛旓細璇风湅绀轰緥锛氭敞鎰忔槸鏁扮粍鍏紡锛=SUM(IFERROR(SUBSTITUTE(A1:D1,"h","")+0,0))杈撳叆鍏紡鍚庢寜CTRL+SHIFT+ENTER鑷姩浜х敓棣栧熬鐨勮姳鎷彿
绛旓細ROW(),,ROWS(H:H)-ROW()))鎴栵細=SUM(OFFSET(H1,ROW(),,ROWS(H:H)-ROW()))杩欎釜鍏紡鍙互瀵笻鍒褰撳墠鍗曞厓鏍鍚庨潰鐨勬墍鏈夎姹傚拰 瀹為檯涓婏紝濡傛灉鐭ラ亾鑷繁鐨勮〃鏈澶ц鏁帮紙03鐗堟槸65536锛岄珮鐗堟湰鐨勮鏁版洿澶氾級锛屽彲灏嗗叕寮忎紭鍖栦负锛=SUM(OFFSET(H4,1,,65536-ROW()))鍏紡涓殑H4锛屽氨鏄緭鍏ュ叕寮忕殑鍗曞厓鏍笺
