excel中如何实现在B表A1格中输入“201房”,B1至B3格就会显示C表中“201房”停放的三辆车的信息？
\u8ba9Excel\u8868\u683cB\u5217\u8868\u4e58\u4ee5\u76f8\u90bb\u7684A\u5217\u8868(=B1*A1+B2*A2+B3*A3)\uff0c\u62c9\u52a8\u516c\u5f0f\u65f6C\u5217\u8868\u4e58\u4ee5\u76f8\u90bb\u7684A\u5217\u8868\uff0c\u4ee5\u6b64\u7c7b\u63a8\u4f60\u62d6\u52a8\u65f6C\u5217\u600e\u4e48\u4f1a\u548cA\u5217\u76f8\u90bb\u5462\uff0c\u4f60\u62d6\u5230C\u5217\u65f6\uff0c\u4e0d\u5c31\u662f=B1*C1+B2*C2+B3*C3\u4e86\u5417
\u5982\u679c\u4f60\u662f\u8981\u56fa\u5b9aA\u5217\uff0c\u6bd4\u5982\u4f60\u62d6\u5230C\u5217\u65f6\u4e5f\u662f=C1*A1+C2*A2+C3*A3\uff0c\u90a3\u5c31\u662f=B1*$A1+B2*$A2+B3*$A3
\u5728\u4e00\u4e2a\u7a7a\u5355\u5143\u683c\u8f93\u5165D\uff0c\u590d\u5236\u8be5\u5355\u5143\u683c\uff0c\u9009\u4e2dA1\u3001B1\uff0c\u53f3\u952e\uff0c\u9009\u62e9\u6027\u7c98\u8d34\uff0c\u8fd0\u7b97\uff0c\u9664
如图例：
B1=VLOOKUP(A$1,C表!A:E,ROW(A2),)&""
下拉
不知道你的C表是怎样的，应该是用到下面这样的公式：
=INDEX(C表!a:a,SMALL(IF(C表!$A$1:$A$1000=$A$1,ROW(C表!$A$1:$A$1000),4^8),ROW(A1)))&""
绛旓細B1=VLOOKUP(A$1,C琛!A:E,ROW(A2),)&""涓嬫媺
绛旓細1锛浣跨敤鍏紡鍥哄畾鍚屾鍒扮浉鍚岀殑浣嶇疆锛氭瘮濡侫琛ㄤ腑褰曞叆鏁版嵁锛屽湪B琛ㄧ浉鍚岀殑浣嶇疆瀹炵幇鏁版嵁鍚屾锛屼娇鐢ㄦ硶鏄細鍦˙琛ㄧ殑A1涓緭鍏=A琛紒A1锛岀劧鍚庡悜鍙筹紝鍚戜笅鍏ㄩ儴濉厖銆傝繖绉嶄娇鐢ㄥ叕寮忕殑鏂瑰紡锛孉琛ㄦ暟鎹彉鍖栵紝B琛ㄥ氨浼氬彉鍖栵紝A琛ㄥ垹闄わ紝浼氬鑷碆琛ㄦ暟鎹埛鏂板悗鎶ラ敊鐨勩傝繖绉嶆柟娉曟搷浣滄瘮杈冪畝鍗曘2锛屼娇鐢ㄥ畯鎶夾琛ㄧ殑鏁版嵁娣诲姞鍒癇琛細鍦...
绛旓細鍏紡涓猴細=IF(OFFSET($A$1,INT(ROW(A2)/2)-1,)="","",OFFSET($A$1,INT(ROW(A2)/2)-1,)&MOD(ROW(A2),2)+1)鏁堟灉濡傚浘锛
绛旓細=VLOOKUP(A1,B琛!A1:B17,2,FALSE)鍏朵腑"A1"鏄鏌ユ壘鐨勫崟鍏冩牸锛"B琛!A1:B17"鏄鏌ユ壘鐨勮寖鍥达紝鈥2鈥滄槸浠庡乏鏁扮2鍒楋紝"FALSE"鏄畬鍏ㄥ尮閰嶏紝濡傛灉娌℃湁灏辩ず閿欍傚笇鏈涜兘甯埌浣犮
绛旓細瑕佸湪Excel涓疄鐜"鑻ユ暟鎹瓨鍦ㄦ樉绀篈锛屽惁鍒欐樉绀築"鐨勫姛鑳斤紝棣栧厛鍦"鑰冩牳缁撴灉"宸ヤ綔琛ㄤ腑閫夋嫨B鍗曞厓鏍硷紝杩欓噷鎴戜滑灏嗕娇鐢╒LOOKUP鍑芥暟杩涜鎿嶄綔銆傚湪C鍒椾腑锛屼粠A1鍗曞厓鏍煎紑濮嬶紝杈撳叆浠ヤ笅鍏紡锛歏LOOKUP(A1, B:B, 1, FALSE)銆傝繖閲岀殑A1浠ｈ〃浣犺鏌ユ壘鐨勫硷紝B:B鏄綘瑕佹煡鎵捐寖鍥寸殑鍒楋紝1琛ㄧず杩斿洖鍖归厤鍊兼墍鍦ㄧ殑琛岋紝FALSE浠ｈ〃...
绛旓細濡傛灉鏁版嵁澶锛岄渶瑕佸缓绔嬩竴涓暟鎹簱锛屽悓涓涓伐浣滆杽涓嬫柊寤轰竴涓〃鎴栬呯敤鍦ㄥ悓涓琛ㄧ暀涓閮ㄥ垎褰撴暟鎹簱銆傛垜杩欓噷缁欎綘涓緥瀛 鍥句腑绾㈡澶凟鍒楀拰F鍒楃敤鏉ュ瓨鍌ㄦ暟鎹紝A鍒椾负鍚嶇О锛岄氳繃鍚嶇О鏌ユ壘鍑哄搴旂殑鍗曚环 鍦ㄦ垜渚嬪瓙鐨凚1澶勮緭鍏ュ叕寮 =VLOOKUP(A2,E$2:F$6,2,) 涓嬫媺 浣犲彲浠ュ搴斾緥瀛愯嚜宸卞啓涓涓嬶紝娌℃湁澶氶毦鐨勶紝...
绛旓細sheet2鐨凚4鍗曞厓鏍煎啓鍏ュ叕寮 =VLOOKUP($B1,Sheet1!$A:B,COLUMN(B1),)鎴 =INDEX(Sheet1!B:B,MATCH($B1,Sheet1!$A:$A,))鍙虫媺濉厖鍏紡
绛旓細F2杈撳叆鍏紡 =IF(E2="","",VLOOKUP(E2,A:B,2,))鍗冲彲
绛旓細涓烘暟缁勫叕寮忥紝鍏堣緭鍏=IF(SUM(IFERROR(IF(FIND($G$1:$G$5,A1),ROW($G$1:$G$5),0),0))=0,"",INDEX($G$1:$G$5,SUM(IFERROR(IF(FIND($G$1:$G$5,A1),ROW($G$1:$G$5),0),0)))鍚庯紝鍐嶆寜ctrl+SHIFT+鍥炶溅 杩欎釜鍏紡涓紝G5鐨勪綅缃槸鏈鍚庝竴涓煄甯傜殑浣嶇疆锛屽鏋淕5涓虹┖锛岀粨鏋...
绛旓細AAA杈撳叆鍦A1,閭1鍐呭～鍐欏涓嬪叕寮忥紝璇ュ叕寮忓彧鑳界粺璁鍜B涓斾笉鍖哄垎澶у皬鍐欙紝濡傛灉杩橀渶瑕佸鍔犲叾浠栧瓧姣嶇粺璁″垯闇姹傚湪鍏紡鍚庨潰澧炲姞鍗冲彲 =IF(SUM(1*(MID(A1,ROW($A$1:$A$255),1)="A"))=0,"",SUM(1*(MID(A1,ROW($A$1:$A$255),1)="A"))&"A") &IF(SUM(1*(MID(A1,ROW($A$1:$A$...
