例如有一張桌子，
Example data:
ColumnA | ColumnB |
ROW1 Code1 | There is an apple, and there wasn't an apple. |
ROW2 Code2 | The applepie is tasty. |
ROW3 Code3 | Apple is good for people. |
ROW4 Code4 | We buy the apple, and they sell apples! |
--------
The result I hope to show:
ColumnA | ColumnB | ColumnC | ColumnD | ColumnE | ColumnF |
ROW5 apple | 4 | Code1 | Code3 | Code4 | |
ROW6 apples | 1 | Code4 | | | |
ROW7 the | 2 | Code2 | Code4 | | |
我希望寫一些公式來...
(1) B5、B6的公式怎么寫？
A5,A6 有兩個確切的詞。B5~B7 將顯示 B1:B4 中的句子與確切單詞匹配的數字（A5~A7）。
(2) C5~F5,C6~F6的公式怎么寫？
因為確切的單詞“蘋果”在 B1、B3、B4 中結束。我想顯示 A1、A3、A4 的值。
因為確切的單詞“the”在 B2、B4 中結束。我想顯示 A2、A4 的值。
我嘗試了一些公式，但無法完美作業。
你能幫我解決這個問題嗎？謝謝你的幫助。
uj5u.com熱心網友回復：
對于Excel 2013，假設與螢屏截圖的布局相同JvdV，并且您將標點符號放在C1,C2和C3, 公式中E1：
=SUMPRODUCT(2 LEN(B$1:B$4)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(" "&B$1:B$4&" "),C$1," "),C$2," "),C$3," ")," "&D1&" ","")))/(2 LEN(D1))
并抄下來。
SUBSTITUTE中添加的每個附加標點符號都需要一個附加C3條款 例如，使用 中的新條目C4，上述內容變為：
=SUMPRODUCT(2 LEN(B$1:B$4)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(" "&B$1:B$4&" "),C$1," "),C$2," "),C$3," "),C$4," ")," "&D1&" ","")))/(2 LEN(D1))
然后，對于串列，在F1：
=IFERROR(INDEX($A$1:$A$4,AGGREGATE(15,6,(ROW($A$1:$A$4)-MIN(ROW($A$1:$A$4)) 1)/ISNUMBER(SEARCH(" "&$D1&" ",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(" "&$B$1:$B$4&" "),$C$1," "),$C$2," "),$C$3," "))),COLUMNS($F1:F1))),"")
并正確復制，直到您開始得到結果的空白。這個公式也可以復制到F2,F3等。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/419971.html
標籤：
上一篇：查找和替換列名的VBA宏