Excel 2016中含指定子串的单词查找公式优化技术问询

Hey there, let's break down your problem and figure out how to speed up that formula.

First, let's recap what you're doing: you have status text in columns H-M, and you want to search each row (based on the column selected in AP1) for substrings in AQ1-BF1, returning the full word that contains each substring in AQ2-BF60000. Your current formula works, but with millions of cells calculating, it's dragging things down—totally understandable.

Your Current Setup & Formula #

You're using this formula right now (formatted for readability):

=MID(INDIRECT($AP$1&ROW()),
    IFERROR(FIND("|",
        SUBSTITUTE(LEFT(INDIRECT($AP$1&ROW()),SEARCH(AQ$1,INDIRECT($AP$1&ROW()))),
        " ","|",
        LEN(LEFT(INDIRECT($AP$1&ROW()),SEARCH(AQ$1,INDIRECT($AP$1&ROW())))) - LEN(SUBSTITUTE(LEFT(INDIRECT($AP$1&ROW()),SEARCH(AQ$1,INDIRECT($AP$1&ROW())))," ",""))
    )),0)+1,
    SEARCH(" ",INDIRECT($AP$1&ROW()),SEARCH(AQ$1,INDIRECT($AP$1&ROW()))) - IFERROR(FIND("|",
        SUBSTITUTE(LEFT(INDIRECT($AP$1&ROW()),SEARCH(AQ$1,INDIRECT($AP$1&ROW()))),
        " ","|",
        LEN(LEFT(INDIRECT($AP$1&ROW()),SEARCH(AQ$1,INDIRECT($AP$1&ROW())))) - LEN(SUBSTITUTE(LEFT(INDIRECT($AP$1&ROW()),SEARCH(AQ$1,INDIRECT($AP$1&ROW())))," ",""))
    )),0)-1
)

The main issue here is that INDIRECT is a volatile function—every time any cell in your workbook changes, all these formulas recalculate from scratch. Plus, you're repeating INDIRECT($AP$1&ROW()) and SEARCH(AQ$1, INDIRECT(...)) multiple times per formula, which multiplies the number of calculations.

Optimization Strategies for Excel 2016 #

Since you can't upgrade, let's focus on non-volatile functions and reducing redundant calculations:

1. Use Helper Columns to Cut Down on Repeated Work

Helper columns are your best friend here because they let you calculate a value once per row instead of millions of times. Let's set this up:

• Helper Column (e.g., AO): Store the selected column's value for each row. In AO2, enter:

  =INDEX($H:$M, ROW(), COLUMN(INDIRECT($AP$1&1)) - COLUMN($H$1) + 1)
  

  Drag this down to AO60000. This uses INDEX (non-volatile) instead of repeating INDIRECT everywhere. The COLUMN(INDIRECT($AP$1&1)) gets the column number of the selected column (H=8, I=9, etc.), then we adjust it to match the position in H-M.

2. Simplify the Word-Finding Formula with FILTERXML

Excel 2016 has FILTERXML, which lets you split text into words easily. Using this can replace your complex MID/SUBSTITUTE logic with something cleaner and faster.

In AQ2, use this formula (referencing the helper column AO):

=IFERROR(LOOKUP(2, 1/(ISNUMBER(SEARCH(AQ$1, FILTERXML("<t><s>"&SUBSTITUTE(AO2, " ", "</s><s>")&"</s></t>", "//s"))), FILTERXML("<t><s>"&SUBSTITUTE(AO2, " ", "</s><s>")&"</s></t>", "//s")), "#VALUE!")

Let's break this down:

• SUBSTITUTE(AO2, " ", "</s><s>") turns your text into an XML string (e.g., "Will be retiring" becomes "Willberetiring").
• FILTERXML(..., "//s") extracts each word as a separate item.
• ISNUMBER(SEARCH(AQ$1, ...)) checks which word contains the substring (case-insensitive, since SEARCH is case-insensitive).
• LOOKUP(2,1/...) finds the first matching word (or any matching word—if multiple words have the substring, it picks the last one; adjust if needed).

3. Additional Speed Tips

• Turn off automatic recalculation temporarily: Go to File > Options > Formulas, select "Manual" calculation. Calculate only when you need to by pressing F9.
• Avoid full column references: If your data only goes to row 60000, use $H$2:$M$60000 instead of $H:$M in the INDEX formula—this reduces the range Excel has to check.
• Remove unnecessary formatting: Conditional formatting or heavy cell styles can slow down recalculation too.

Note on Punctuation

If your text includes punctuation (like commas or periods attached to words), the formula will return the word with the punctuation (e.g., "returned," instead of "returned"). To fix this, add SUBSTITUTE calls to strip common punctuation before splitting:

=IFERROR(LOOKUP(2, 1/(ISNUMBER(SEARCH(AQ$1, FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(AO2, ",", ""), ".", ""), " ", "</s><s>")&"</s></t>", "//s"))), FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(AO2, ",", ""), ".", ""), " ", "</s><s>")&"</s></t>", "//s")), "#VALUE!")

Add more SUBSTITUTE calls for other punctuation marks (like !, ?, etc.) as needed.

Testing the Optimized Formula #

Let's verify with your example:

• If AO2 is "Will be retiring in June" and AQ1 is "ret", the formula returns "retiring".
• When you change AP1 to I, the helper column AO automatically updates to pull from column I, so all the AQ-BF cells refresh with the correct values.

This should cut down on calculation time significantly because we've eliminated repeated volatile function calls and simplified the core logic.

备注：内容来源于stack exchange，提问作者Michael Cooney