EXCEL - 通过搜索其他工作表中的关键词查找类别 [英] EXCEL - Find category by searching keyword from other worksheet

问题描述

我想按类别（州）获得销售。

I want to get sales by category (states).

在Sheet1 中，行A中有州名称（A1到A6）和列中的城市。
每个城市属于州，例如，

In Sheet1, there're state's names in row A (A1 to A6) and cities in the column. Each cities belongs to states,

悉尼属于新南威尔士州，墨尔本和Geelong属于VIC。

e.g. Sydney belongs to NSW, and Melbourne & Geelong belong to VIC.

然后 Sheet2 包含如下所示的销售数据。
我想按月份的状态计算销售额。
此刻，行D和行I之间的每个单元格都具有如下所示的公式：

Then Sheet2 contains sales data like shown below. I want to calculate sales by state each month. At the moment, each cells between row D and row I has a formula like below;

（例如F5）

= IF（AND（ISERR（SEARCH（Brisbane，B5）），ISERR（SEARCH（Gold Coast，B5）），ISERR（SEARCH（Cairns，B5））） C5）

=IF(AND(ISERR(SEARCH("Brisbane",B5)),ISERR(SEARCH("Gold Coast",B5)),ISERR(SEARCH("Cairns",B5))),"",C5)

但我相信会有更好，更简单的漂亮的方法来做到这一点。随着店铺的增加，目前的配方变得更加复杂。所以需要进行优化。

But I'm sure there would be better and more simple nifty way to do this. As shops increase, this current formula gets more complicated. So it's needed to be optimized.

我想使用vlookup或某些东西来简化这些计算，但是没有运气。
任何建议，如果可能，详细，将不胜感激！谢谢你提前：）

I would like to make these calculation simple using vlookup or something.. but no luck so far. Any advice, detailed if possible, would be greatly appreciated! Thank you in advance :)

推荐答案

你可以用数组公式做到这一点。例如，在 D3 中使用：

You can do this with an array formula. For example, in D3 use:

=IFERROR(IF(INDIRECT("Sheet1!A"&MAX(IF(ISERROR(SEARCH("*"&Sheet1!$B$1:$D$6&"*",$B3))+(Sheet1!$B$1:$D$6=""),0,ROW(Sheet1!$B$1:$D$6))))=D$1,$C3,""),"Not found")

（要输入数组公式，您需要按 Ctrl + Shift + 输入）

(To enter an array formula, you need to press Ctrl+Shift+Enter)

• SEARCH code> B3 ，并将其与 Sheet1！B1：D6 中的所有名称进行比较，并使用 * 通配符前后。如果匹配，这不会是错误。


• 添加 +（Sheet1！$ B $ 1：$ D $ 6 =）给出一个OR， ** 将匹配）。


• 如果不是错误，我们将获得相应匹配的 ROW ，并获得最高（ MAX ）行匹配。


• 然后我们使用 INDIRECT 在Sheet1（状态代码）的列A中获取单元格值，并将其与我们列的顶行


• 这包裹在一个 IFERROR 中，告诉我们我们的商店是否与任何城市不匹配。 li>
  

• The SEARCH looks at the store name in B3 and compares it to all names in Sheet1!B1:D6 with a * wildcard before and after. This won't be an error if it matches.
• Adding the +(Sheet1!$B$1:$D$6="") gives an OR the cell is blank (otherwise ** would match).
• If it isn't an error, we'll get the ROW of the corresponding match and we take the highest (MAX) row match.
• We then use INDIRECT to get the cell value in column A of Sheet1 (the State code) and compare that to the state in the top row of our column
• This is wrapped in an IFERROR to tell us if our store doesn't match any city

数组公式是一个很好的工具，但有点混乱！阵列公式不是在单个单元格上工作，而是在数组的每个单元格上工作。通常这是计数，总和，平均值等。通过包含逻辑数学，你可以在一个公式中做一些非常奇怪的事情。一个很好的资源是[链接] http://www.cpearson.com/excel/arrayformulas.aspx 永远记住使用 Ctrl + Shift + 输入或者真的很奇怪的事情会发生！

Array formulas are a great tool, but a little confusing! Rather than work on a single cell, array formulas work on each cell of an array. Typically this is for counts, sums, averages, etc. By including "logic maths", you can do some really strange things in a single formula. A good resource is [link]http://www.cpearson.com/excel/arrayformulas.aspx Always remember to use Ctrl+Shift+Enter or really strange things will happen!

这篇关于EXCEL - 通过搜索其他工作表中的关键词查找类别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！