如果在表格里把货号男女款分开
\u7528WPS\u505a\u9500\u552e\u62a5\u8868\uff0c\u60f3\u628a\u5404\u4e2a\u8d27\u53f7\u7684\u7537\u5973\u6b3e\u5206\u522b\u6807\u6ce8\u51fa\u6765\u3002\u5047\u8bbe\u4f60\u7684\u6570\u636e\u5728A\u5217\uff0c\u5e76\u4eceA1\u5f00\u59cb\uff0c\u5728B1\u8f93\u5165\u516c\u5f0f
=IF(A1"",IF(MOD(LOOKUP(9^9,--MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)),2),"\u7537","\u5973"),"")
\u4e0b\u62c9\u590d\u5236\u5373\u53ef\u3002
\u6b65\u9aa4\uff1a
\u4e00\u3001\u53cc\u51fbexcell\uff0c\u6253\u5f00\u4f60\u8981\u505a\u5de5\u4f5c\u7684excell\u6587\u4ef6\u3002
\u4e8c\u3001\u8f93\u5165\u540d\u5355\uff0c\u628a\u4f60\u8981\u8f93\u5165\u7684\u5185\u5bb9\u586b\u4e0a\u53bb\uff0c\u91cc\u9762\u51fa\u73b0\u4e86\u4ee5\u4e0b\u5185\u5bb9\u3002
\u4e09\u3001\u627e\u89c1\u6570\u636e\u91cc\u7684\u6392\u5e8f\uff0c\u53ef\u4ee5\u9009\u4e2d\u6027\u522b\u4e0a\u7684B\uff0c\u627e\u5230\u83dc\u5355\u4e0a\u7684\u6570\u636e\uff0c\u70b9\u4e00\u4e0b\uff0c\u51fa\u73b0\u6392\u5e8f\u3002
\u56db\u3001\u6392\u5e8f\u5e94\u7528\uff0c\u70b9\u51fb\u6392\u5e8f\uff0c\u51fa\u73b0\u4ee5\u4e0b\u7a97\u53e3\uff0c\u9009\u62e9\u5347\u5e8f\u8fd8\u662f\u964d\u5e8f\uff0c\u4e00\u822c\u5c31\u4f1a\u81ea\u52a8\u6392\u5217\u987a\u5e8f\uff0c\u5305\u62ec\u6027\u522b\u3001\u5e74\u9f84\u3001\u5927\u5c0f\u7b49\u4e00\u822c\u5e38\u89c4\u6027\u7684\u6392\u5217\uff0c\u8fd9\u4e9b\u529f\u80fd\u90fd\u53ef\u4ee5\u5b9e\u73b0\uff0c\u6700\u540e\u70b9\u51fb\u786e\u5b9a\u3002
绛旓細鍋囪浣犵殑鏁版嵁鍦ˋ鍒楋紝浠嶢1寮濮嬶紝閭d箞鍦˙1杈撳叆鍏紡=IF(MID(A1,4,1)="1","鐢","濂")锛岀劧鍚庡悜涓嬫嫋鍔˙1鏍艰繘琛屽~鍏咃紝鐢峰コ涓鐩簡鐒讹紝鎺掑簭鐨勮瘽浠鍒椾负涓荤储寮
绛旓細鑳芥弿杩颁竴涓嬶紝鎴栬 璇存埅涓浘锛岀湅涓涓嬩綘鐨琛ㄦ牸涔堬紵
绛旓細浣犻変腑琛ㄧず璐у彿鐨勯偅涓鏁村垪锛屽啀鎼滅储锛濡傛灉閲岄潰娌℃湁浣犺鎵剧殑鏁版嵁灏变細鏄剧ず褰撳墠瀵硅瘽妗
绛旓細妤间富璇曡瘯濡傚浘鐨勫叕寮
绛旓細鏈绠鍗曞拰鏂瑰紡鏄細鍦╞ook1琛ㄤ笂鍔犱竴鍒椻滅紪鐮佲濓紝鐒跺悗鍦ㄥ搴旂殑鍗曞厓鏍肩洿鎺ヨ緭鍏モ=鈥濆彿锛屽啀鐢ㄩ紶鏍囩偣涓涓媌ook2瀵瑰簲鏍硷紝纭畾锛屽洖鍒拌〃1锛屽叾瀹冪殑鍚戜笅鎷夈
绛旓細鍏鍦ㄨ〃涓涓缓绔嬩竴涓揣鐗╁熀鏈俊鎭 鐒跺悗鍦ㄨ〃浜屼腑寤虹珛涓涓繖鏍风殑琛 鎶奅鍒璐у彿璁剧疆涓烘湁鏁堟э紝鐒跺悗鍦‵鍒楄緭鍏=VLOOKUP(E2,璐х墿鍩烘湰淇℃伅!A:C,2,0)锛屽叕寮忓悜涓嬫媺澶嶅埗锛氬啀鍦℅鍒楄緭鍏=VLOOKUP(E2,璐х墿鍩烘湰淇℃伅!A:C,3,0)锛屽鍥撅細褰撲粠鍚戜笅鐨勯粦涓夎涓夋嫨璐у彿鍚庯紝绫诲瀷鍜屽崟浠疯嚜鍔ㄥ嚭鏉 ...
绛旓細1銆佸缓绔嬪師濮嬫暟鎹〃鏍硷紝鏈鍘熷鐨勫熀鏈暟鎹湪姝ゆ墜宸ヨ緭鍏ワ紱2銆佸缓绔嬫偍闇瑕佺殑涓鍒囪〃鏍硷紱3銆佷綘闇瑕佺殑琛ㄦ牸鍐鐨勬暟鎹敤鍏紡銆佸畯銆佸嚱鏁扮瓑鏂规硶浠庡師濮嬭〃鏍兼垨涓棿琛ㄦ牸銆佽浆鎹㈣〃鏍煎紩鐢ㄨ绠椼3銆佷竴鍒囨姤琛ㄣ佷腑闂寸粨鏋滅瓑鍏ㄩ儴鐢卞師濮嬫暟鎹〃鍜屾潯浠堕夋嫨绛夋柟娉曡嚜鍔ㄨ绠楃敓鎴愩傜粨鏋滄槸浣犲彧闇瑕佽緭鍏ュ師濮嬫暟鎹紝鎵鏈夎〃鏍奸兘宸茶嚜宸卞姩鐢熸垚 杩欐牱...
绛旓細鍚堝苟鐩稿悓鐨璐у彿 鏂规硶姝ラ锛1銆佸厛鎶婄洰鏍囪揣鍙蜂滑閫変腑锛屾寜闄嶅箓鎴栬呮槸鍗囧箓鎺掑簭銆2銆佽繖鏍凤紝鐩稿悓鐨勮揣鍙峰氨浼氳仛鍦ㄥ湪涓璧枫3銆佹妸鐩稿悓璐у彿 瀵瑰簲鐨勬暟閲忥紝浠庝笂寰涓嬮変腑锛屽悎璁 4銆佽繖鏍凤紝鐩稿悓璐у彿鐨勬绘暟閲忓氨鍑烘潵浜嗐
绛旓細浣犺繖绫绘槸灞炰簬杩為攣杩涢攢瀛樼鐞嗙殑搴旂敤浜嗭紝闈炰竴涓琛鑳借В鍐崇殑闂銆濡傛灉涓涓〃鑳借В鍐充綘鐨勬暟鎹綍鍏ュ伐浣滈噺涔熸槸寰堝彲瑙傜殑銆傚彧鏈夎繛閿佽繘閿瀛樿兘鍦ㄤ綘涓氬姟鍙戠敓鏃剁敱鍚勫簵褰曞叆鍚勭偣鐨勬暟鎹渶缁堟眹鎬绘垚涓轰綘鎯冲緱鍒扮殑鎶ヨ〃銆
绛旓細鍙互閲囩敤鍒嗙被姹囨荤殑鍔炴硶锛岃繖鏍风浉鍚 璐у彿鐨勬暟閲忚繕鍙互姹囨绘眰鍜 姝ラ锛1銆侀夊畾鑼冨洿---鏁版嵁--鍒嗙被姹囨---锛2銆佸垎绫诲瓧娈碉細璐у彿 3銆佹眰鍜屽瓧娈碉細鏁伴噺 缁撴灉锛