Excel 2016：如何从用户已安装软件表中提取非标准软件

Hey there! Let's walk through exactly how to tackle this in Excel 2016—super straightforward once you know the right tricks, and your simplified examples will make it even easier to follow.

First, let’s align on how your data is organized. There are two common setups, each with a tailored solution:

• Layout 1: One Software Per Row (e.g., Column A = Username, Column B = Single Installed Software)
• Layout 2: Multiple Softwares in One Cell (e.g., Column A = Username, Column B = Comma-separated list of all installed softwares)

Assume your user-installed software data lives in Sheet1 (columns A:B), and your standard software list is in Sheet2 (column A, no headers):

1. Add an auxiliary column (say Column C) in Sheet1. In cell C2, paste this formula:
   =COUNTIF(Sheet2!$A:$A, B2)
   • What this does: Checks if the software in B2 exists in your standard list. It returns 1 if it’s a standard software, and 0 if it’s an extra one we want to keep.
2. Drag the fill handle down from C2 to apply the formula to all rows.
3. Filter Sheet1 to show only rows where Column C equals 0. This gives you every user’s extra software—copy these filtered rows to a new sheet to create your final "Results" table.

This needs a bit more formula magic, but it’ll automatically split, check, and recombine your software lists:

1. Same setup: Sheet1 (A:B) has your user data, Sheet2 (A:A) has standard softwares.
2. In cell C2 of Sheet1, paste this array formula—make sure to press Ctrl+Shift+Enter after typing it (Excel 2016 requires this to run array formulas properly):

   =TEXTJOIN(", ", TRUE, IF(ISERROR(MATCH(TRIM(MID(SUBSTITUTE(B2,",",REPT(" ",100)),ROW(INDIRECT("1:"&LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1))*100-99,100)),Sheet2!$A:$A,0)),TRIM(MID(SUBSTITUTE(B2,",",REPT(" ",100)),ROW(INDIRECT("1:"&LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1))*100-99,100)),""))
   

   • Quick breakdown:
     • Splits your comma-separated list into individual software items
     • Trims extra spaces from each software name
     • Checks if each software is in the standard list (flags it as extra if no match is found)
     • Joins all extra softwares back into a clean comma-separated list
3. Drag the fill handle down to apply this formula to all users. Column C will now show exactly the extra softwares for each person.

Let’s validate this with your sample scenario:

• Installed Software: User1 has A,B,C; User2 has B,D,E
• Standard Software: B,C
• Expected Results: User1 gets A; User2 gets D,E

For Layout 1:

• Your Sheet1 rows would be (User1,A), (User1,B), (User1,C), (User2,B), (User2,D), (User2,E)
• Column C returns 0 for (User1,A), (User2,D), (User2,E). Filtering these gives your perfect results.

For Layout 2:

• Sheet1 B2 = "A,B,C", B3 = "B,D,E"
• Column C2 returns "A", C3 returns "D,E"—exactly what you need!

内容的提问来源于stack exchange，提问作者Thimi11