wps表格怎样用函数按名称和所对应的月份统计数据? Excel(或WPS表格)中,如何按照人名汇总成绩?
wps\u600e\u4e48\u7528\u51fd\u6570\u6c42\u4e00\u4e2a\u6708\u5206\u7c7b\u7684\u6c47\u603b\u3000\u3000\u670b\u53cb\u4eec\u4e2d\u5348\u7684\u8ba2\u9910\u4efb\u52a1\u7531\u6211\u6765\u627f\u62c5\uff0c\u4e3a\u4e86\u4ed8\u6b3e\u7b80\u5355\uff0c\u9910\u8d39\u91c7\u53d6\u5468\u7ed3\u7684\u65b9\u5f0f\uff0c\u6bcf\u5468\u4e94\u6211\u7edf\u8ba1\u51fa\u6765\u5411\u5927\u5bb6\u6536\u53d6\uff0c\u4e00\u4e2a\u8bb0\u5f55\u6bcf\u5929\u8ba2\u9910\u4fe1\u606f\u7684\u8868\u683c\u9010\u6e10\u5448\u73b0\u5728\u6211\u9762\u524d\u3002\u5e94\u8be5\u8bf4\uff0c\u8fd9\u4e2a\u8868\u683c\u8fd8\u662f\u5f88\u6e05\u695a\u7684\uff0c\u81f3\u5c11\u9700\u8981\u53cd\u6620\u7684\u57fa\u7840\u4fe1\u606f\u90fd\u5728\u4e0a\u9762\u4e86\u3002
\u3000\u3000\u4e4b\u6240\u4ee5\u7528WPS\u7535\u5b50\u8868\u683c\u6765\u8bb0\u5f55\u5b83\uff0c\u5c31\u662f\u56e0\u4e3a\u6211\u53ef\u4ee5\u5728\u6570\u636e\u7684\u6700\u4e0b\u65b9\u76f4\u63a5\u770b\u5230\u6211\u672c\u5468\u5148\u884c\u57ab\u4ed8\u4e86\u591a\u5c11\u91d1\u989d\uff0c\u65b9\u6cd5\u662f\u5728\u6700\u540e\u7684\u90e8\u5206\u4f7f\u7528\u51fd\u6570\uff0c\u5bf9\u4e8e\u6b64\u79cd\u4e00\u6b21\u6027\u7684\u51fd\u6570\u5927\u591a\u6570\u670b\u53cb\u5df2\u7ecf\u5e94\u7528\u7684\u5f88\u719f\u7ec3\u3002
\u3000\u3000\u95ee\u9898\u5206\u6790
\u3000\u3000\u4f46\u662f\uff0c\u8fd9\u8fd8\u5f88\u4e0d\u65b9\u4fbf\uff0c\u6211\u8be5\u5411\u6bcf\u4e00\u4e2a\u4eba\u6536\u591a\u5c11\u94b1\u5462?\u8fd9\u4e2aWPS\u8868\u683c\u7684\u5f88\u5927\u7279\u70b9\u5c31\u662f\u6bcf\u5929\u7684\u4eba\u90fd\u4e0d\u4e00\u6837\uff0c\u6211\u6ca1\u6709\u6309\u7167\u6240\u6709\u7684\u4eba\u6765\u6392\u5e03\u8868\u683c\u3002\u6216\u8005\u8bf4\uff0c\u7535\u5b50\u8868\u683c\u80fd\u4e0d\u80fd\u5e2e\u6211\u4e2a\u5fd9\uff0c\u628a\u51fa\u73b0\u7684\u6bcf\u4e2a\u4eba\u5e94\u8be5\u7ed9\u6211\u7684\u91d1\u989d\u4e5f\u76f4\u63a5\u544a\u8bc9\u6211\u3002\u5f53\u6211\u4eec\u5728\u201c\u6570\u636e\u201d\u4e2d\u627e\u5230\u201c\u5206\u7c7b\u6c47\u603b\u201d\u540e\uff0c\u9996\u5148\u78b0\u5230\u8fd9\u4e2a\u5bf9\u8bdd\u6846\u3002
\u3000\u3000\u7ecf\u8fc7\u4e0d\u65ad\u7684\u4fee\u6b63\u3001\u5b8c\u5584\u3001\u601d\u8003\u3001\u5c1d\u8bd5\uff0c\u4e00\u4efd\u6211\u671f\u5f85\u7684\u6570\u636e\u6c47\u603b\u8868\u683c\u7ec8\u4e8e\u5448\u73b0\u5728\u9762\u524d\u3002
\u60a8\u597d\uff0c\u5f88\u9ad8\u5174\u4e3a\u60a8\u89e3\u7b54\uff01
\u590d\u5236\u4e00\u4e2a\u5de5\u4f5c\u8868\uff0c\u6309\u4eba\u540d\u8fdb\u884c\u6392\u5e8f\uff0c\u7136\u540e\u9009\u4e2d\u8fd9\u4e9b\u6570\u636e\uff0c\u9009\u62e9\u6570\u636e\u9009\u9879\u5361\u4e2d\u7684\u5206\u7c7b\u6c47\u603b\u3002
\u5728\u5f39\u51fa\u7684\u5bf9\u8bdd\u6846\uff0c\u9009\u62e9\u201c\u6c47\u603b\u65b9\u5f0f\u201d\u201c\u6c47\u603b\u9879\u201d\u786e\u5b9a\u3002
\u8bba\u6587\u6392\u7248\u3001\u6a21\u7248\u4e0b\u8f7d\u3001\u8bba\u6587\u9047\u5230\u96be\u9898\uff0c\u89e3\u51b3\u65b9\u6cd5\uff1ahttp://docer.wps.cn/zt/lunwen?from=qyzd\u66f4\u591aWPS\u529e\u516c\u8f6f\u4ef6\u6559\u7a0b\uff0c\u8bf7\u8bbf\u95ee\uff1ahttp://bbs.wps.cn\u6216\u8005http://e.weibo.com/wpswfw
\u5982\u6709\u7591\u95ee\uff0c\u8bf7\u70b9\u51fb\u6211\u7684\u5934\u50cf\u63d0\u95ee\u3002\u795d\u60a8\u751f\u6d3b\u6109\u5feb\uff01
多条件求和sumifs()可以用,看你日期像文本的,如果是数字型日期,可以用下图公式:
G2公式:=SUMIFS($D:$D,$B:$B,">="&DATE(2019,G$1,1),$B:$B,"<"&DATE(2019,G$1+1,0),$C:$C,$F2)
复制B列粘贴到E列,选择E列,删除重复值。
在F2中输入或复制粘贴下列公式
=SUMIFS($C:$C,$B:$B,$E2,$A:$A,">="&DATE(2019,COLUMN(A1),1),$A:$A,"<"&DATE(2019,COLUMN(B1),1))
或
=SUMIFS($C:$C,$B:$B,$E2,$A:$A,">="&DATE(2019,COLUMN(A1),1),$A:$A,"<="&EOMONTH(DATE(2019,COLUMN(A1),1),0))
下拉填充,右拉填充
绛旓細姝ラ1锛氬湪鍙充晶鐨勫皬琛ㄦ牸涓緭鍏ュ搴斿乏渚уぇ琛ㄦ牸鐨勭鍚嶄汉鐨勫悕瀛銆傛楠2锛氬湪鍙充晶灏忚〃鏍间腑杈撳叆鍏紡锛岄夋嫨瀵瑰簲鐨勫垪鍜岃銆備緥濡傦紝濡傛灉浣犺缁熻鐨勬槸绗1鍒楀拰绗1琛岀殑鏁板硷紝閭d箞浣犲彲浠ュ湪鍙充晶灏忚〃鏍肩殑绗簩琛岃緭鍏ヤ互涓嬪叕寮忥細=SUMIF(A1:A100,"鎸囧畾鐨勪汉鍚",B1:B100)姝ラ3锛氬皢鍏紡涓殑"鎸囧畾鐨勪汉鍚"鏇挎崲鎴愪綘鎯宠缁熻...
绛旓細棣栧厛锛屾垜浠潵鐪嬬涓涓寚绀烘锛屽彨鎴戜滑杈撳叆鏌ユ壘鍊硷紝鏌ユ壘鍊煎氨鏄緭鍏ユ垜浠鏌ユ壘鐨勫肩殑鍚嶇О锛屾墍浠ユ垜浠厛鐐瑰嚮杩欎釜鎸囩ず妗嗗彸渚х殑鎸夐挳銆傚啀鐐瑰嚮閭d釜鎸夐挳鍚庣郴缁熶細璁╂垜浠夋嫨杈撳叆鍊硷紝鍦ㄨ繖涓緥瀛愪腑鎴戜滑灏遍夋嫨A2妗嗐傜劧鍚庢垜浠啀绗簩涓锛屾槸璁╂垜浠緭鍏ユ暟鎹〃锛屾暟鎹〃灏辨槸閫夊畾鍖呭惈鎵鏈夎鏌ユ壘鐨勬暟鎹爮鍏ㄩ儴锛岃繖涓緥瀛愪腑鍚屾牱...
绛旓細鎴戜滑鍥炲綊鍘熷琛ㄦ牸锛屽湪B2鍗曞厓鏍煎綋涓緭鍏ュ叕寮=IF锛圓2>鈥濃,B1+1锛夛紝鍗曞嚮鍥炶溅銆傜劧鍚庢垜浠湅鍒癇2灏辨樉绀轰簡1锛屼笅鎷夊~鍏呮锛屽苟涓嶄細涓鐩存帓搴忥紝鑰屾槸褰㈡垚鎴戜滑鍒氭墠鐪嬪埌鐨勶紝鏍规嵁鍓嶉潰绫诲瀷鑰岀紪鍙风殑缂栧彿鏂瑰紡銆傞偅涔堬紝鍦╓PS琛ㄦ牸鍜Excel琛ㄦ牸褰撲腑锛屽埄鐢↖F鍑芥暟鎸绫诲埆杩涜缂栧彿鐨勬柟娉曪紝鏄笉鏄潪甯哥畝鍗曞憿锛屼綘瀛︿細浜嗗悧锛
绛旓細浣跨敤VLOOKUP鍑芥暟 濡傛灉浣犻渶瑕佹牴鎹壒瀹氱殑鏉′欢锛堜緥濡傜紪鍙凤級浠庝竴鍒椾腑鏌ユ壘骞惰繑鍥炵浉搴旂殑鏁版嵁锛屼綘鍙互浣跨敤`VLOOKUP`鍑芥暟銆傚亣璁句綘闇瑕佹牴鎹紪鍙锋煡鎵惧苟杩斿洖瀵瑰簲鐨勫悕绉帮紝浣犲彲浠ュ湪E4鍗曞厓鏍硷紙鎴栦换浣曢渶瑕佹樉绀虹粨鏋滅殑鍗曞厓鏍硷級浣跨敤浠ヤ笅鍏紡锛=VLOOKUP(D4, A:B, 2, FALSE)杩欓噷鐨刞D4`鏄綘瑕佹煡鎵剧殑缂栧彿锛宍A:B`鏄寘鍚紪...
绛旓細姝ラ1:閫夋嫨瑕佹彃鍏ュ嚱鏁扮殑鍗曞厓鏍糃7銆傜浜屾锛氱偣鍑昏彍鍗曟彃鍏-鍔熻兘銆傛墦寮鎻掑叆鍑芥暟瀵硅瘽妗嗭紝閫夋嫨鎵闇鐨勫钩鍧囧銆傜涓夋锛氱偣鍑荤‘瀹氾紝寮瑰嚭鎻掑叆骞冲潎鍊煎悜瀵笺傝鍥5-3-2銆傜鍥涙锛氶夋嫨C4: C6鍦板尯銆傝鍥5-3-3銆532 533 绗簲姝ワ細鐐瑰嚮鍥炶溅閿‘璁よ鍔熻兘鐨勮緭鍏ャ傛寜鍥炶溅閿绠楃粨鏋溿傝鍥5-3-4銆534 2.姹傛渶澶...
绛旓細澶氭潯浠舵眰鍜宻umifs()鍙互鐢紝鐪嬩綘鏃ユ湡鍍忔枃鏈殑锛屽鏋滄槸鏁板瓧鍨嬫棩鏈燂紝鍙互鐢ㄤ笅鍥惧叕寮忥細G2鍏紡锛=SUMIFS($D:$D,$B:$B,">="&DATE(2019,G$1,1),$B:$B,"<"&DATE(2019,G$1+1,0),$C:$C,$F2)
绛旓細鍦╓PS琛ㄦ牸涓紝鍑芥暟鍜鍏紡鏄暟鎹鐞嗙殑鐏甸瓊锛屽畠浠皢澶嶆潅鐨勮绠楃畝鍖栦负鐩磋鐨勬搷浣溿傚嚱鏁版槸棰勫厛璁捐鐨勮绠楀伐鍏凤紝濡 SUM(鏁板1,鏁板2)锛岀敱鍚嶇О銆佸弬鏁板拰缁撴潫鎷彿鏋勬垚銆傚叕寮忓垯鐢辩敤鎴疯嚜瀹氫箟锛屽 =SUM(E1:H1)*A1+26锛岀粨鍚堜簡鍑芥暟銆佸紩鐢ㄥ拰杩愮畻銆傚弬鏁扮殑鑹烘湳 鍑芥暟鐨勫弬鏁版槸鍏惰繍绠楁牳蹇冿紝鍖呮嫭甯搁噺銆侀昏緫鍊笺佹暟缁勫拰...
绛旓細鎺屾彙WPS琛ㄦ牸鐨勬眰鍜屾妧宸э紝杞绘澗瀹屾垚鏁版嵁姹囨伙紒</ 棣栧厛锛屾墦寮WPS锛岃鎴戜滑閫氳繃鍑犱釜绠鍗曟楠ゅ疄鐜版暟鎹眰鍜屻傛柟娉曚竴锛氱洿鎺ヨ緭鍏</ 鍦ㄥ寘鍚暟瀛楃殑鍒椾笅锛岄敭鍏"=sum"锛屼笅鎷夎彍鍗曢殢鍗冲嚭鐜般傞夋嫨"SUM"閫夐」锛屾寜涓嬪洖杞﹂敭锛岀劧鍚庡皢榧犳爣绉诲姩鍒版嫭鍙蜂腑澶紝杈撳叆浣犳墍闇姹傚拰鐨勮寖鍥达紝濡侫1:A10</锛屾寜鍥炶溅锛屽嵆鍙湅鍒扮粨鏋溿傛柟娉曚簩锛...
绛旓細鐩殑:鏍规嵁鈥滃憳宸ュ鍚嶁濇煡璇㈠搴旂殑鈥滄湀钖濄傛柟娉:1銆佷互鈥滃憳宸ュ鍚嶁濅负涓昏鍏抽敭瀛楄繘琛屽崌搴忔帓搴忋2銆佸湪鐩爣鍗曞厓鏍间腑杈撳叆鍏紡:=LOOKUP(J3,B3:B12,G3:G12)銆傝В璇:鍦ㄤ娇鐢↙ookup鍑芥暟鏌ヨ鏁版嵁鏃,棣栨瑕佷互鈥滄煡璇㈠尖濅负涓昏鍏抽敭瀛楄繘琛屽崌搴忔帓搴,鍚﹀垯鏃犳硶寰楀埌姝g‘鐨勭粨鏋溿2銆佹暟缁勫舰寮忋傚姛鑳:浠庢寚瀹氱殑鑼冨洿绗竴鍒楁垨绗竴琛屼腑...
绛旓細鍏紡锛=TEXT(A2,"h:mm")&"-"&TEXT(B2,"h:mm")&IF(A2<TIME(17,30,0),"璁′欢","鍔犵彮璁′欢")