小伙伴們好，不知道我每天發(fā)的文章你們能看懂不，沒(méi)人留言和反饋，我也不清楚，只能按我想到的或遇到的問(wèn)題來(lái)發(fā)了。今天的問(wèn)題如下：計算合并名單的總銷(xiāo)量，左表是源表，右表是要計算答案的區域。如：顧初/左曼/肖雪，通過(guò)左表查詢(xún)銷(xiāo)量分別是1902、1963、644，匯總后得4509。
解答這個(gè)題目，有2種思路，每種思路又可以寫(xiě)好幾種函數。下面分別來(lái)說(shuō)：
思路1.在合并名單中查找每個(gè)姓名
也就是在D4單元格中查找A列的所有姓名，能找到的就是我們需要的，后面通過(guò)計算處理可以返回對應的銷(xiāo)量，最后求和就可以了。
第1個(gè)公式為=SUMPRODUCT(ISNUMBER(FIND(A$3:A$12,D4))*B$3:B$12)。先看FIND(A$3:A$12,D4)這部分，就是在D4單元格中查找A$3:A$12的每個(gè)姓名，找到的返回數字，找不到的返回錯誤值，最后的結果為{1;#VALUE!;4;#VALUE!;#VALUE!;#VALUE!;#VALUE!;7;#VALUE!;#VALUE!}。
接下來(lái)用ISNUMBER判斷上一步的結果是否是數字，是數字的返回true，不是數字的返回false，最后的結果為{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}。
然后用上一步的true與false組成的數組和B$3:B$12相乘，返回的結果為{1902;0;1963;0;0;0;0;644;0;0}。這樣就把D4單元格中每個(gè)人的銷(xiāo)量算出來(lái)了。最后用sumproduct求和就可以了。
第2個(gè)公式為=SUMPRODUCT(COUNTIF(D4,"*"&A$3:A$12&"*"),B$3:B$12)。還是在D4單元格中查找A列的每個(gè)姓名，只不過(guò)用的是countif的通配符用法。
第3個(gè)公式為=SUMPRODUCT((SUBSTITUTE(D4,A$3:A$12,"")<>D4)*B$3:B$12)。用的是substitute替換函數。舉個(gè)簡(jiǎn)單的例子，將"顧初/左曼/肖雪"(D4單元格)中的"顧初"(A3單元格)替換為空，結果為"/左曼/肖雪"，和原來(lái)D4單元格的"顧初/左曼/肖雪"不相等，說(shuō)明在D4單元格中可以找到A3單元格的值。
這3個(gè)公式的本質(zhì)是一樣的，都是在合并名單的單元格中查找A列的每個(gè)姓名。當然還可以用其他的函數來(lái)查找，比如match。
思路2.將合并名單拆分成數組
第2種思路是將合并名單中的多個(gè)姓名拆分成數組，比如將"顧初/左曼/肖雪"拆分為{"顧初","左曼","肖雪"}。然后再用sumif，sumproduct等函數計算就簡(jiǎn)單了。
這里主要介紹拆分成數組的方法，有兩種。第一種用mid+rept函數，第二種用宏表函數evaluate。
第一種公式為=TRIM(MID(SUBSTITUTE(D10,"/",REPT(" ",99)),ROW($1:$9)*99-98,99))，返回的結果為{"顧初";"左曼";"肖雪";"";"";"";"";"";""}。將D10單元格的名單拆分成上面的數組。
首先用substitute將斜杠"/"替換為99個(gè)空格REPT(" ",99)，變成一個(gè)很長(cháng)的字符串，然后用mid提取，從1,100,199......開(kāi)始提取，提取的長(cháng)度是99位。1,100,199......是一個(gè)以1開(kāi)始，99為等差的等差數列，用ROW($1:$9)*99-98表示。提取出來(lái)的字符串包含多余的空格，用trim去除多余的空格。
這個(gè)數組拆分出來(lái)后，最后的求和公式為=SUMPRODUCT(SUMIF(A$3:A$12,TRIM(MID(SUBSTITUTE(D10,"/",REPT(" ",99)),ROW($1:$9)*99-98,99)),B$3:B$12))。
第二種拆分的公式用的evaluate，所以要用到定義名稱(chēng)。在【公式】—【定義名稱(chēng)】中新建一個(gè)名稱(chēng)“人員”，在引用位置輸入公式=EVALUATE("{"""&SUBSTITUTE($D10,"/",""",""")&"""}")。注意公式中的雙引號。
在第10行的任意空白單元格輸入公式=人員，按F9看它返回的結果為{"顧初","左曼","肖雪"}。
SUBSTITUTE($D10,"/",""",""")這部分是將D10單元格的斜杠(/)替換為(",")，這樣才好讓每個(gè)名字被雙引號包裹。如下圖紅色框中的效果。公式不能寫(xiě)成這樣SUBSTITUTE($D10,"/","","")，也就是說(shuō)如果要在雙引號中還要放雙引號，雙引號要寫(xiě)2個(gè)（實(shí)際代表是1個(gè)）。這個(gè)公式SUBSTITUTE($D10,"/",""",""")中的2個(gè)紅色引號實(shí)際代表1個(gè)，只是要寫(xiě)2次，不知道有沒(méi)有說(shuō)清楚。
上一步名字左右兩側還沒(méi)有雙引號，所以左右兩側也要連接雙引號，公式為=""""&SUBSTITUTE($D10,"/",""",""")&""""，結果如下圖紅框所示。
但是還少左右大括號{}，所以把左右大括號也連接起來(lái)，公式為="{"""&SUBSTITUTE(D10,"/",""",""")&"""}"，結果如下圖紅框所示。這樣看上去雖然構成了數組，但不是真正的數組。最外面套個(gè)evaluate才能轉為真正的數組。
數組拆分好了，最后的求和公式為=SUMPRODUCT(SUMIF(A:A,人員,B:B))或者=SUMPRODUCT((A$3:A$12=人員)*B$3:B$12)。
大家還是邊看文字邊看表吧，源文件：
https://pan.baidu.com/s/18EjE-vrEo3KnhkFfbIyMUA
提取碼：swim
聯(lián)系客服
