如何编写Excel公式以返回列标题名称

发布于 2025-01-29 05:10:47 字数 776 浏览 2 评论 0原文

我希望允许用户选择模块。深入研究，用户可以选择课程提纲上的基础。此后，它将告诉他们哪些课程（又称列标题），该课程与“ Y”相匹配的条件基础。

 示例如果选择了以下内容：
 - 数据治理（政策和过程）
 - 数据分析项目（问题声明制定）
 - 数据探索（探索性分析）
 - 数据探索（机器学习） 
 这都将返回CourseID1＆amp; CourseID2，如果选择数据分析项目（数据处理＆ENCLICS）将无法返回CourseID1。只有CourseID2将退还

我附上了Excel桌子的屏幕截图。流量如下：步骤1：用户选择模块步骤2：用户选择与模块绑定的教学大纲（步骤1的相关选择）步骤3：显示合适课程的输出！

如何编写这样的公式？

我使用的公式是错误的，

=INDEX($A$1:$E$1,SUMPRODUCT(MAX(($A$2:$E$72="Y")*($B$2:$B$72=B2)*(COLUMN($A$2:$E$72))))-COLUMN($A$1)+1)

当选择正确的标准时，它不会返回CourseID1和CourseID2。

原文

I am looking to allow users to select the modules. Diving deeper, the users can select base on syllabus. Thereafter, it will tell them which courses (aka the column header), which matches the condition base on the rows with "Y".

Example  If the following is selected:
- Data Governance (Policy and Process)
- Data Analytics Project (Problem Statement Formulation)
- Data Exploration (Exploratory Analysis)
- Data Exploration (Machine Learning) 
 It will all return both CourseID1 & CourseID2,  If Data Analytics Project (Data Handling & Ethics) selected it will not return CourseID1. Only CourseID2 will be returned

I have attached a screen shot of the Excel Table. The flow is as follows:
step 1: user selects module
step 2: user select the syllabus that's tied to the module (a dependent selection from step 1)
step3: Output for the suitable courses is shown!

How to write such formula?

The formula I used was wrong

=INDEX($A$1:$E$1,SUMPRODUCT(MAX(($A$2:$E$72="Y")*($B$2:$B$72=B2)*(COLUMN($A$2:$E$72))))-COLUMN($A$1)+1)

It does not return both CourseID1 and CourseID2 when the right criteria is being selected.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

眼前雾蒙蒙 2025-02-05 05:10:47

因此，假设您正在使用 o365

/code>使用filterxml（），xlookup（），textjoin（），index（），替换（）＆amp; column（）函数。

=TEXTJOIN(", ",,INDEX($A$1:$E$1,,INDEX(--(XLOOKUP(
FILTERXML("<p><w>"&SUBSTITUTE(SUBSTITUTE($A12,"&",""),", ","</w><w>")&"</w></p>","//w")&
FILTERXML("<p><w>"&SUBSTITUTE(SUBSTITUTE($A15,"&",""),", ","</w><w>")&"</w></p>","//w"),
SUBSTITUTE($B$2:$B$9,"&","")&SUBSTITUTE($C$2:$C$9,"&",""),$D$2:$E$9)="Y")*(COLUMN($D$1:$E$1)),1)))

或，如果您使用的是 o365 ，并且目前在 Office Insiders Beta Channel版本中，然后使用textsplit（）而不是filterxml（）

•单元格中使用的公式a18

=TEXTJOIN(", ",,INDEX($A$1:$E$1,,(TRANSPOSE(XLOOKUP(
TEXTSPLIT(A12,,", ")&TEXTSPLIT(A15,,", "),$B$2:$B$9&$C$2:$C$9,
$D$2:$E$9))="Y")*COLUMN($D$1:$E$1)))

So, this is what I have tried, assuming you are using O365

• Formula used in cell A17 using FILTERXML(), XLOOKUP() , TEXTJOIN() , INDEX() , SUBSTITUTE() & COLUMN() functions.

=TEXTJOIN(", ",,INDEX($A$1:$E$1,,INDEX(--(XLOOKUP(
FILTERXML("<p><w>"&SUBSTITUTE(SUBSTITUTE($A12,"&",""),", ","</w><w>")&"</w></p>","//w")&
FILTERXML("<p><w>"&SUBSTITUTE(SUBSTITUTE($A15,"&",""),", ","</w><w>")&"</w></p>","//w"),
SUBSTITUTE($B$2:$B$9,"&","")&SUBSTITUTE($C$2:$C$9,"&",""),$D$2:$E$9)="Y")*(COLUMN($D$1:$E$1)),1)))

Or, If you are using O365 and presently in Office Insiders Beta Channel Version then using TEXTSPLIT() instead of FILTERXML()

• Formula used in cell A18

=TEXTJOIN(", ",,INDEX($A$1:$E$1,,(TRANSPOSE(XLOOKUP(
TEXTSPLIT(A12,,", ")&TEXTSPLIT(A15,,", "),$B$2:$B$9&$C$2:$C$9,
$D$2:$E$9))="Y")*COLUMN($D$1:$E$1)))

回复收藏 0 原文

~没有更多了~