Excel表格里面这么快速根据不同日期统计不同门店不同水果的数据呢？表格已附上，期待各位大神的解答 EXCEL如何将同一工作簿下的不同工作表根据日期和商品名进行...
excel\u91cc,\u5982\u4f55\u5c06\u4e0d\u540c\u65e5\u671f\u4e0d\u540c\u4ef7\u683c\u8fdb\u884c\u5206\u522b\u6c42\u548c\u4e3a\u5927\u5bb6\u4ecb\u7ecd10\u4e2a\u6c42\u548c\u516c\u5f0f\uff0c\u6bcf\u4e2a\u516c\u5f0f\u90fd\u6709\u81ea\u5df1\u7684\u4f18\u52bf\u548c\u7f3a\u9677\uff0c\u4e0d\u8981\u6267\u8ff7\u4e8e\u67d0\u4e00\u4e2a\u516c\u5f0f\uff0c\u53ea\u8981\u662f\u80fd\u89e3\u51b3\u95ee\u9898\u7684\u5c31\u662f\u597d\u65b9\u6cd5\u3002
1.\u6700\u5feb\u6c42\u548c
\u9009\u62e9\u533a\u57df\uff0c\u6309\u5feb\u6377\u952eALT+=\uff0c\u77ac\u95f4\u5b8c\u6210\u3002
2.\u7d2f\u8ba1\u6c42\u548c
\u7d2f\u8ba1\u9500\u552e\u989d
=SUM(B$2:B2)
=N(C1)+B2
N\u662f\u6700\u7b80\u5355\u7684\u51fd\u6570\uff0c\u5c31\u662f\u5c06\u6587\u672c\u8f6c\u6362\u62100\u3002\u8ddf\u8fd9\u4e2a\u7c7b\u4f3c\u7684\u662fT\uff0c\u5c06\u6570\u5b57\u8f6c\u6362\u6210\u7a7a\u6587\u672c\u3002
=N(C1)
=T(C1)
3.\u8de8\u5217\u6c42\u548c
SUM\u51fd\u6570\u5728\u6c42\u548c\u7684\u65f6\u5019\uff0c\u5ffd\u7565\u6587\u672c\uff0c\u4e5f\u5c31\u662f\u53ef\u4ee5\u76f4\u63a5\u5199\u6210\u4e00\u4e2a\u533a\u57df\u3002
=SUM(A2:F7)
4.\u5355\u6761\u4ef6\u6c42\u548c
\u7edf\u8ba1\u9500\u552e\u989d\u5927\u4e8e500\u7684\u4eba\u7684\u603b\u9500\u552e\u989d\u3002
=SUMIF(B:B,">500",B:B)
=SUMIF(B:B,">500")
SUMIF\u51fd\u6570\u5f53\u6761\u4ef6\u533a\u57df\u8ddf\u6c42\u548c\u533a\u57df\u4e00\u6837\u65f6\uff0c\u6c42\u548c\u533a\u57df\u53ef\u4ee5\u7701\u7565\u3002
5.\u907f\u5f00\u9519\u8bef\u503c\u6c42\u548c
A\u5217\u7684\u6570\u5b57\u4e2d\u5305\u542b\u9519\u8bef\u503c\uff0c\u5982\u4f55\u907f\u5f00\u8fd9\u4e9b\u9519\u8bef\u503c\u5bf9\u6570\u5b57\u6c42\u548c\u3002
=SUMIF(A:A,"<9E+307")
9E+307\u662f\u63a5\u8fd1\u6700\u5927\u7684\u6570\u5b57\uff0c\u800c\u9519\u8bef\u503c\u6bd4\u6700\u5927\u7684\u6570\u5b57\u8fd8\u5927\uff0c\u5c0f\u4e8e\u6700\u5927\u503c\u7684\u6570\u5b57\uff0c\u5c31\u80fd\u5bf9\u6570\u5b57\u8fdb\u884c\u6c42\u548c\u3002
6.\u901a\u914d\u7b26\u6c42\u548c
\u7edf\u8ba1\u6700\u540e\u5b57\u7b26\u4e3a\u6cb3\u8425\u4e1a\u90e8\u7684\u603b\u91d1\u989d\u3002
=SUMIF(A2:A11,"*\u6cb3",F2:F11)
\u901a\u914d\u7b26\u67092\u4e2a\uff0c*\u4ee3\u8868\u5168\u90e8\u5b57\u7b26\uff0c?\u4ee3\u8868\u4e00\u4e2a\u5b57\u7b26\u3002\u5982\u679c\u662f2\u4e2a\u5b57\u7b26\uff0c\u6700\u540e\u4e00\u4e2a\u5b57\u662f\u6cb3\uff0c\u53ef\u7528"?\u6cb3"\u3002
7.\u591a\u533a\u57df\u6761\u4ef6\u6c42\u548c
\u591a\u4e2a\u76f8\u540c\u7684\u533a\u57df\uff0c\u7edf\u8ba1\u578b\u53f7\u4e3aB03\u7684\u6570\u91cf\u3002
=SUMIF(A2:G10,"B03",B2:H10)
SUMIF\u51fd\u6570\u662f\u4e2a\u5f88\u795e\u5947\u7684\u51fd\u6570\uff0c\u533a\u57df\u4f1a\u81ea\u52a8\u6269\u5c55\uff0c\u6240\u4ee5\u5199\u4e00\u4e2a\u533a\u57df\u5c31\u884c\u3002
8.\u4e58\u79ef\u6c42\u548c
\u83b7\u53d6\u6570\u91cf*\u5355\u4ef7\u7684\u603b\u91d1\u989d\u3002
=SUMPRODUCT(B2:B4,C2:C4)
9.\u591a\u6761\u4ef6\u6c42\u548c
\u7edf\u8ba1\u8425\u4e1a\u90e8\u4e3a\u5929\u6cb3\u6570\u91cf\u5927\u4e8e20\u7684\u603b\u91d1\u989d\u3002
=SUMIFS(F2:F11,A2:A11,"\u5929\u6cb3",D2:D11,">20")
10.\u7edf\u8ba1\u6bcf\u4e2a\u6708\u7684\u91d1\u989d
\u6839\u636e\u6bcf\u5929\u7684\u9500\u552e\u660e\u7ec6\uff0c\u7edf\u8ba1\u6bcf\u4e2a\u6708\u7684\u91d1\u989d\u3002
=SUMPRODUCT((MONTH($A$2:$A$11)=F2)*$D$2:$D$11)
MONTH\u51fd\u6570\u5c31\u662f\u83b7\u53d6\u6708\u4efd\u3002
\u8868\u683c\u6570\u91cf\u4e0d\u591a\u7684\u8bdd\uff0c\u53ef\u4ee5\u7528sumifs \u6216 sumproduct\u8fdb\u884c\u7edf\u8ba1\uff0c\u6570\u636e\u591a\u3001\u8868\u683c\u591a\u7684\u8bdd\u53ef\u4ee5\u7528VBA\u81ea\u52a8\u7edf\u8ba1
B3=SUMPRODUCT(($K$2:$K$100=LOOKUP(9E+307,$B$1:B$1))*($L$2:$L$100=$A3)*($M$1:$O$1=B$2)*($M$2:$O$100))
公式右拉，下拉
左边第1行为 标准日期格式
K列也是标准日期格式
=SUMPRODUCT(($L$2:$L$10=$A3)*($K$2:$K$10=B$1)*($M$1:$O$1=B$2)*($M$2:$O$10))
绛旓細excel涓鎸夌収濮撳悕鑷姩鍖归厤鏁版嵁锛屽彲浠ユ寜鐓т互涓嬫楠よ繘琛屾搷浣滐細1銆侀鍏堟墦寮excel琛ㄦ牸锛屽湪闇瑕佸尮閰嶆暟鎹殑绌虹櫧鍗曞厓鏍间腑杈撳叆鈥=vlookup鈥濆苟璋冪敤鍑芥暟銆2銆佺劧鍚庨夋嫨闇瑕佸尮閰嶇殑濮撳悕瀛楁銆3銆佽嫳鏂囬楀彿闅斿紑鍚庯紝閫夋嫨瑕佸尮閰嶇殑鏁版嵁鑼冨洿銆4銆佽嫳鏂囬楀彿闅斿紑锛岃緭鍏ユ暟鎹寖鍥翠腑闇瑕佸尮閰嶆彁鍙栫殑鏁版嵁鎵鍦ㄧ殑鍒楁暟銆5銆佺劧鍚庨夋嫨绮剧‘鍖归厤銆6...
绛旓細鏂规硶涓锛氫娇鐢ㄢ滄煡鎵惧拰鏇挎崲鈥濆姛鑳 1. 鎵撳紑Excel琛ㄦ牸锛岄変腑瑕佸尮閰嶇殑鏁版嵁鎵鍦ㄧ殑鍒銆2. 鐐瑰嚮鈥滃紑濮嬧濋夐」鍗★紝閫夋嫨鈥滄煡鎵惧拰閫夋嫨鈥濅笅鐨勨滄煡鎵锯濄3. 鍦ㄢ滄煡鎵惧拰鏇挎崲鈥濆璇濇涓紝杈撳叆瑕佹煡鎵剧殑鍏抽敭瀛楋紝鐐瑰嚮鈥滄煡鎵句笅涓涓濄4. Excel浼氳嚜鍔ㄥ畾浣嶅埌绗竴涓尮閰嶅叧閿瓧鐨勫崟鍏冩牸锛屽彲浠ヤ娇鐢ㄢ滄煡鎵句笅涓涓濇寜閽潵鏌...
绛旓細绗竴姝ワ細鎵撳紑涓涓excel琛锛岄渶瑕佸湪鍙︿竴涓琛ㄤ腑鍖归厤鍑虹浉搴斿悓瀛︾殑鐝骇淇℃伅銆傜浜屾锛氭妸鍏夋爣瀹氫綅鍦ㄩ渶瑕佸睍绀烘暟鎹殑鍗曞厓鏍间腑锛屽涓嬪浘鎵绀恒傜涓夋锛氬湪鍗曞厓鏍间腑杈撳叆鈥=vl鈥濓紝鐒跺悗浼氳嚜鍔ㄦ彁绀哄嚭VLOOKUP鍑芥暟锛屽弻鍑昏摑鑹茬殑鍑芥暟閮ㄥ垎銆傜鍥涙锛氬彲浠ョ湅鍒帮紝鍗曞厓鏍间腑鏄剧ず鍑哄畬鏁寸殑VLOOKUP鍑芥暟銆傜浜旀锛氶夋嫨绗竴鍒椾腑闇瑕...
绛旓細1锛浣跨敤銆怌trl+G銆戠粍鍚堥敭锛屽湪銆愭煡鎵句笌瀹氫綅銆戝脊鍑烘涓垏鎹㈣嚦銆愬畾浣嶃戠晫闈紝閫夋嫨銆愮壒娈婂畾浣嶃;2锛屽湪銆愮壒娈婂畾浣嶃戜腑閫夋嫨銆愮┖鍊笺戝苟纭畾锛岀劧鍚庡湪绌哄崟鍏冩牸杈撳叆銆=B3銆戝苟浣跨敤銆怌trl+E銆戠粍鍚堥敭纭銆傚湪Excel琛ㄦ牸涓紝鍙互浣跨敤蹇熷～鍏呭姛鑳芥潵蹇熷～鍏呭搴旀暟鎹紝鎿嶄綔鏂规硶濡備笅锛1. 鍦ㄥ崟鍏冩牸涓緭鍏ョ浉搴旀暟鎹紝渚嬪...
绛旓細鍦‥xcel涓紝浣犲彲浠ヤ娇鐢↖F鍑芥暟鍜屼箻娉曡繍绠楃鏉ユ牴鎹竴鍒楁暟瀛楃殑澶у皬鍒嗗埆涔樹互涓嶅悓鐨勭櫨鍒嗘瘮銆傞鍏堬紝浣跨敤IF鍑芥暟鏉ュ垽鏂暟瀛楃殑澶у皬锛岀劧鍚庢牴鎹潯浠堕夋嫨鐩稿簲鐨勭櫨鍒嗘瘮杩涜涔樻硶杩愮畻銆備緥濡傦紝鍋囪浣犵殑鏁板瓧鍦ˋ鍒楋紝浣犳兂鏍规嵁鏁板瓧鐨勫ぇ灏忓垎鍒箻浠10%銆20%鍜30%鐨勭櫨鍒嗘瘮锛屼綘鍙互浣跨敤浠ヤ笅鍏紡锛=IF(A1<10,A1*0.1,IF(A1< 20...
绛旓細鍙互閫氳繃鍑芥暟鍏崇郴寮忔潵蹇鏌ユ壘閲嶅椤广備笅闈㈡垜灏辨紨绀轰竴涓嬭缁嗙殑鎿嶄綔姝ラ: (鐢佃剳鍨嬪彿:鑱旀兂鎷晳鑰呭垉7000K,绯荤粺鐗堟湰:win10,鐗堟湰:Excel 2016) 涓銆佹墦寮绗竴涓EXCEL琛ㄦ牸,杈撳叆=if(A2=) 浜屻佸垏鎹㈠埌绗簩涓伐浣滆〃,閫変腑鍖哄煙骞朵笖鍥炶溅, 涓夈佽繑鍥炲埌宸ヤ綔琛1骞惰ˉ鍏ㄥ叕寮=IF(A2=Sheet2!A2:A9,"鏄","鍚") 鍥涖佸皢鍏夋爣绉诲姩...
绛旓細1銆侀鍏堝湪鐢佃剳妗岄潰涓婃壘鍒Excel琛ㄦ牸锛岀偣鍑绘墦寮瀹冦2銆佸彧瑕佽韩浠借瘉鍙风爜灏辨煡鍒板勾榫勭殑銆傚鍥炬墍绀猴紝杈撳叆韬唤璇佸彿鐮併3銆佺劧鍚庡彲浠ラ殢渚挎壘涓涓〃鏍兼爮鐩緭鍏ワ細=YEAR(TODAY())-MID(A2,7,4)杈撳畬涔嬪悗锛岀偣鍑籈NTER閿4銆佽繖鏍峰氨浼氳嚜鍔鏍规嵁韬唤璇佺畻骞撮緞浜嗐侲xcel閮ㄥ垎瀹炵敤鎶宸э細1銆丆trl+閿洏鈫戔啌鈫愨啋鏂瑰悜閿細鎸変綇Ctrl...
绛旓細1銆佹墦寮鐢佃剳涓鐨excel琛ㄦ牸鏂囨。锛屾壘鍒般怌trl+H銆戦敭骞舵寜涓嬨2銆佸湪寮瑰嚭鐨勫璇濇涓紝杈撳叆闇瑕佹煡鎵剧殑鍐呭杩涜瀹氫綅鍗冲彲銆
绛旓細鍦Excel涓揩閫鏌ユ壘鏁版嵁锛屽彲浠ヤ娇鐢ㄤ互涓嬫柟娉曪細1. 浣跨敤绛涢夊姛鑳斤細閫夋嫨闇瑕佹煡璇㈢殑鏁版嵁鑼冨洿锛岀劧鍚庣偣鍑"鏁版嵁"閫夐」鍗′笂鐨"绛涢"鎸夐挳銆傚湪寮瑰嚭鐨勭瓫閫夎彍鍗曚腑锛岃緭鍏ヨ鏌ユ壘鐨勫硷紝Excel浼氳嚜鍔ㄧ瓫閫夊嚭鍖呭惈璇ュ肩殑鏁版嵁銆2. 浣跨敤鏌ユ壘鍔熻兘锛氱偣鍑"寮濮"閫夐」鍗′笂鐨"鏌ユ壘鍜岄夋嫨"鎸夐挳锛屽湪寮瑰嚭鐨勮彍鍗曚腑閫夋嫨"鏌ユ壘"銆傚湪鎵撳紑鐨勬煡鎵...
