您好,我有个EXCEL的问题想问您,请查看附加的图片,我想要在方框中填入相应的数字。
\u600e\u4e48\u5728excel\u4e2d\u7684\u65b9\u6846\u4e2d\u586b\u5165\u6570\u5b57\u30001\u3001\u53ef\u4ee5\u5728excel\u4e2d\u501f\u52a9\u8f85\u52a9\u5355\u5143\u683c\u8f93\u5165\uff08\u5982\u4e0b\u56fe\u52a8\u753b\u6f14\u793a\uff0c\u5982\u753b\u9762\u672a\u52a8\u8bf7\u70b9\u51fb\u56fe\u7247\u83b7\u5f97\u8be6\u7ec6\u52a8\u753b\u6f14\u793a\u8fc7\u7a0b\uff09\uff1a
\u3000\u30002\u3001\u63d2\u5165"\u5e26\u5708\u5b57\u7b26"\u66f4\u65b9\u4fbf\uff08\u5982\u4e0b\u56fe\u52a8\u753b\u6f14\u793a\uff0c\u5982\u753b\u9762\u672a\u52a8\u8bf7\u70b9\u51fb\u56fe\u7247\u83b7\u5f97\u8be6\u7ec6\u52a8\u753b\u6f14\u793a\u8fc7\u7a0b\uff09\uff1a
1\u3001\u6f14\u793a\u4f7f\u7528\u7684\u8f6f\u4ef6\u4e3aExcel\u7535\u5b50\u8868\u683c\uff0c\u8f6f\u4ef6\u7248\u672c\u4e3aoffice\u5bb6\u5ead\u548c\u5b66\u751f\u72482016\u3002
2\u3001\u9996\u5148\u6253\u5f00Excel\u7535\u5b50\u8868\u683c\uff0c\u5c06\u9f20\u6807\u79fb\u52a8\u5230\u4e0a\u65b9\u83dc\u5355\u680f\u4e2d\u7684\u63d2\u5165\u9009\u62e9\u63d2\u5165\u63d2\u56fe\uff0c\u5e76\u9009\u62e9\u5f62\u72b6\u3002
3\u3001\u9009\u62e9\u77e9\u5f62\u540e\u62d6\u62fd\u5373\u53ef\u5f97\u5230\u4e00\u4e2a\u77e9\u5f62\u7684\u65b9\u6846\uff0c\u7136\u540e\u53f3\u952e\u9009\u62e9\u7f16\u8f91\u6587\u5b57\uff0c\u8f93\u5165\u6570\u5b57\u5373\u53ef\u3002
4\u3001\u6587\u5b57\u7f16\u8f91\u5b8c\u4e4b\u540e\u53ef\u4ee5\u70b9\u51fb\u4e3b\u9898\u6837\u5f0f\u5bf9\u65b9\u6846\u6837\u5f0f\u8fdb\u884c\u7f16\u8f91\u3002
5\u3001\u5982\u679c\u4e0d\u9700\u8981\u5e95\u7eb9\u989c\u8272\u586b\u5145\uff0c\u53ef\u4ee5\u4ec5\u8bbe\u7f6e\u5f62\u72b6\u8f6e\u5ed3\u5373\u53ef\u3002
6\u3001\u53ef\u4ee5\u770b\u5230\u6211\u4eec\u5df2\u7ecf\u5b9e\u73b0\u4e86Excel\u4e2d\u6570\u5b57\u5916\u52a0\u65b9\u6846\u7684\u64cd\u4f5c\u3002
向右向下拉
G11输入=SUMPRODUCT(($A$2:$A$7=$E11)*($B$2:$B$7=$F11)*($C$2:$C$7=G$10),($D$2:$D$7))先向右填充到I11再向下填充。
绛旓細涓嶇敤鍏紡锛岀敤鏅氭搷浣滐細澶嶅埗+鎺掑簭涓ゆ灏辫В鍐充簡銆傛搷浣滄柟娉曪細澶嶅埗鏁版嵁鍖哄煙鍒版柊鐨勪綅缃傝鐐瑰嚮杈撳叆鍥剧墖鎻忚堪 閫夊畾鏂板尯鍩熲啋鑿滃崟鏍忊啋鏁版嵁鈫掓帓搴 璇风偣鍑昏緭鍏ュ浘鐗囨弿杩 閲嶈鐐瑰湪杩欓噷锛岄夋嫨鎺掑簭瀵硅瘽妗嗕腑鐨勯夐」鈫掓寜琛屾帓搴忊啋闄嶅簭锛岃鐐瑰嚮杈撳叆鍥剧墖鎻忚堪 鏈鍚庢煡鐪嬬粨鏋滐紝OK浜嗐
绛旓細1锛 鐢ㄥ畾浣嶅~鍏 鍚庢眰鍜岋紝 杩欐秹鍙婂埌鎿嶄綔锛 涓嶆兂璁茶繖涓 涓嶈繃涔熷緢绠鍗曪紝1鍒嗛挓鎼炲畾 2锛 涓涓叕寮忕洿鎺ユ悶瀹氾細G2 杈撳叆 =IF(A3="","",SUM(F$1:F2)-SUM(G$1:G1))榧犳爣绉诲埌G2 鍙充笅瑙 鍙樺疄蹇冨崄瀛楀弻鍑
绛旓細濡傛灉鏄『搴忎骇鐢,閭i搴撲腑寰堝棰樺皢娴垂,鍥犱负姣忔浜х敓鐨勯鐩兘涓鏍,鍙樺寲鐨勫彧鏄暟閲忎笉鍚屼骇鐢熺殑鍙樺寲,姣斿棰樺瀷閫夋嫨"鍗曢",鏁伴噺涓2,閭d箞灏嗕骇鐢熼搴撲腑鐨勫墠2棰,濡傛灉涓嬫閫夋嫨"鍗曢",鏁伴噺涓3,灏嗕骇鐢熼搴撲腑鐨勫墠3棰,鑰3棰樹腑鐨勫墠2棰樺拰绗竴娆$殑鐩稿悓...杩樻湜妤间富鏄庣ず ...
绛旓細閭f槸鍥犱负浣犱笉灏忓績鎵撳紑鐨勫綍鍏ョ殑瀹忕殑浠g爜!鎶婅繃绋嬬粨鏉熻End Sub鍒犻櫎浜! 浣犳寜ALT+F11鎵撳紑VBE缂栬緫鍣,鎵撳紑鍙宠竟妯″潡,鎵撳紑浣犲綍鍒剁殑瀹忕殑鍚嶅瓧.閲岄潰鏈変唬鐮,鍦ㄦ墍鏈変唬鐮佸悗鍔犱笂End Sub灏辫浜!
绛旓細棣栧厛鎴戜滑浠嬬粛涓嬩娇鐢ㄧ殑鍑芥暟 vlookup 鐨勫嚑涓弬鏁帮紝vlookup鏄垽鏂紩鐢ㄦ暟鎹殑鍑芥暟锛屽畠鎬诲叡鏈夊洓涓弬鏁帮紝渚濇鏄細1銆佸垽鏂殑鏉′欢 2銆佽窡韪暟鎹殑鍖哄煙 3銆佽繑鍥炵鍑犲垪鐨勬暟鎹 4銆佹槸鍚︾簿纭尮閰 鏍规嵁闂鐨勯渶姹傦紝杩欎釜鍏紡搴旇鏄細=vlookup(a2,sheet1!$a$2:$f$100,6,true)璇︾粏璇存槑涓涓嬪湪姝lookup鍑芥暟渚嬪瓙涓悇涓...
绛旓細Function Fill(ByVal rng As Range) Dim str$, st1$, st2$, st3$, FinX%, i%, L%, k%, j%, ar() str = rng If str = "" Then Fill = "": Exit Function L = Len(rng) FinX = InStr(str, "-") Do Until IsNumeric(Mid(str, FinX - 1 - k, 1...
绛旓細=IF(A1>=12,3锛孖F(A1>=6,2,1))銆傚亣璁炬暟鎹湪A1鏍笺傚叕寮忕洿璇戯細濡傛灉A1澶т簬绛変簬12灏辨樉绀3锛屽鏋淎1澶т簬绛変簬6灏辨樉绀2锛屽惁鍒欙紝灏1銆
绛旓細璁惧師鏁版嵁鎵鍦ㄥ崟鍏冩牸涓篴1锛屽垯鍦ㄩ渶瑕佽繘琛屽鐞嗙殑鍗曞厓鏍艰緭鍏ワ細=if(a1<6,1,if(a1<12,2,3))褰揳1灏忎簬6鏃讹紝杩斿洖1锛屼笉鐒讹紝鍒ゆ柇a1鏄惁灏忎簬12锛屼负鐪熸椂杩斿洖2锛屼笉鐒惰繑鍥3銆
绛旓細绗竴姝ワ紝鎵撳紑瀵煎嚭Excel琛ㄥ唽锛屾寜ALT+F11杩涘叆VBE妯″紡锛岀偣鍑绘彃鍏ユā鍧楋紝灏嗕互涓嬩唬鐮佸鍒跺埌閲岄潰锛岀偣淇濆瓨骞跺叧闂璙BE妯″紡銆侳unction union(MyRange As Range) As String Dim strTmp As String, cellsText As String, isFirst As Boolean strTmp = "" isFirst = True For Each Mycells In MyRang...
绛旓細1.鍦ㄦ柊鐨勮〃鏍间粠A2璧峰線涓,鍒楀嚭浣犵殑鎵鏈夎彍鍚,濡侫2涓哄皬鐧借彍,A3涓虹┖蹇冭彍.2.鍦˙1杈"琛ㄤ竴",琛ㄧず,B鍒楁槸鏄剧ず琛ㄤ竴涓殑鑿滅殑閲(搴旇鏄挶鍚??),鍦–1杈"琛ㄤ簩",浠ユ绫绘帹.3.鍦˙2杈撳叕寮:=IF(ISNA(INDEX([Book1]Sheet1!$A:$Z,MATCH($A2,[Book1]Sheet1!$A:$A,0),2)),0,INDEX([Book1]Sheet...