VLOOKUP的COUNTIFS [英] COUNTIFS for VLOOKUP
问题描述
报告名称用户名报告类别
报告1约翰·史密斯销售
报告1杰克黑色销售
报告1康妮Rae销售
报告1大脑债券销售
报告2约翰·史密斯销售
报告2康妮Rae销售
报告3杰克黑色库存
该功能的目标是能够将它作为一个标准查找 John Smith ，并将 Sales 作为另一个标准，并记录他使用的报告。输出将在不同的表单上，如下所示：
用户报告名称报告类别
John Smith报告1销售
报告2销售
康妮Rae报告1销售
报告2销售
Brian债券报告1销售
杰克黑色报告1销售
我将输入的名称，该功能将在报告名称列中。已经玩了一段时间的功能，但没有任何运气。谢谢，
这个新手上路的这个
你在这里要求的是相当复杂的，但是如果你必须有一个公式...将这个公式放在单元格 B2 中并将其复制下来。您将需要使用 CTRL + SHIFT + ENTER 输入此公式：
= IFERROR LOOKUP（SMALL（IF（LOOKUP（REPT（ Z，255），$ A $ 2：INDEX（A：A，ROWS（A $ 1：A2）））= Sheet 1中$ B $ 2：！INDEX（Sheet 1中B：B ，COUNTA（Sheet 1中B：！B）），IF（C2 = Sheet 1中$ C $ 2：！INDEX（Sheet 1中C：C，COUNTA（Sheet 1中C：！C）），ROW（Sheet 1中$ A $ 2：INDEX（Sheet 1中！答：A，COUNTA（工作表Sheet1！A：A）！）） - ROW（工作表Sheet1！$ A $ 2）+1）），COUNTIF（INDIRECT（ADDRESS（查找（9.99999999999999E + 307，MATCH（A $ 1：A2，A $ 1：A2,0）），COLUMN（）+ 1）及 ： &安培; ADDRESS（ROWS（A $ 1：A2），COLUMN（）+ 1）），C2）），ROW（Sheet 1中$ A $ 2 ：INDEX（工作表Sheet1答：A，COUNTA（Sheet1中答：！A））！！！！） - ROW（工作表Sheet1！$ A $ 2）+ 1，工作表Sheet1！$ A $ 2：INDEX（工作表Sheet1答：A，COUNTA（Sheet1中一：A））），）
此公式假设第一个标题在两张表上在单元格 A1 和两张表格中的最后（第三个）标题位于单元格 C1 中。此外，公式也引用Sheet1，因此您需要将其更改为实际的工作表名称。您可以使用以下图片排列公式：
有几件事要指出：
-
公式在引用数据表（Sheet1）时使用动态范围，这意味着您可以继续在数据表中添加数据，但是您将需要
-
该公式考虑到用户可能有多个报告类别（销售，库存等）。
让我知道这是否适合你，如果你需要我调整任何东西。
Hopefully this headache can get resolved. I am currently trying to find a function that will do a vlookup using multiple criteria. Something similar to how a COUNTIFS function works or a function version of a Pivot Table. It may have to do with an Array Function but I can't quite figure it out. I think the best way to describe what I am trying to do is with an example:
Report Name User Name Report Category
Report 1 John Smith Sales
Report 1 Jack Black Sales
Report 1 Connie Rae Sales
Report 1 Brain Bonds Sales
Report 2 John Smith Sales
Report 2 Connie Rae Sales
Report 3 Jack Black Inventory
The goal of the function is to be able to have it look up John Smith as one criteria and Sales as another criteria and record the reports that he used. The output would be on a different sheet and would look like:
User Report Name Report Category
John Smith Report 1 Sales
Report 2 Sales
Connie Rae Report 1 Sales
Report 2 Sales
Brian Bonds Report 1 Sales
Jack Black Report 1 Sales
The name I would type in and the function would be in the Reports Name column. Been playing with functions for a while but haven't had any luck. Figured I'd try here while I keep playing around.
Thanks, THAT Newbie
What you are asking for here is pretty complex, but if you must have a formula... Place this formula in cell B2 and copy it down. You are going to need to enter this formula with CTRL + SHIFT + ENTER:
=IFERROR(LOOKUP(SMALL(IF(LOOKUP(REPT("Z",255),$A$2:INDEX(A:A,ROWS(A$1:A2)))=Sheet1!$B$2:INDEX(Sheet1!B:B,COUNTA(Sheet1!B:B)),IF(C2=Sheet1!$C$2:INDEX(Sheet1!C:C,COUNTA(Sheet1!C:C)),ROW(Sheet1!$A$2:INDEX(Sheet1!A:A,COUNTA(Sheet1!A:A)))-ROW(Sheet1!$A$2)+1)),COUNTIF(INDIRECT(ADDRESS(LOOKUP(9.99999999999999E+307,MATCH(A$1:A2,A$1:A2,0)),COLUMN()+1)&":"&ADDRESS(ROWS(A$1:A2),COLUMN()+1)),C2)),ROW(Sheet1!$A$2:INDEX(Sheet1!A:A,COUNTA(Sheet1!A:A)))-ROW(Sheet1!$A$2)+1,Sheet1!$A$2:INDEX(Sheet1!A:A,COUNTA(Sheet1!A:A))),"")
This formula assumes the first header, on both sheets, is in cell A1 and the last (third) header, on both sheets, is in cell C1. Also, the formula references "Sheet1", so you will need to change this to the actual sheet name. You can use the images below to line up the formulas:
A couple of things to point out:
The formula uses dynamic ranges when referencing the data sheet (Sheet1), which means you can just continue to add data to the table as it comes in. However, you will need to restructure your table on the user sheet (The worksheet with the formula) as data is added.
The formula takes into account that the user may have multiple report categories (Sales, Inventory,etc.).
Let me know if this works for you and if you need me to adjust anything.
这篇关于VLOOKUP的COUNTIFS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
