英文:
Filtering Data from different sheets located in different columns matching two Conditions
问题
目标
我试图从我的电子表格中基于两个条件检索不同工作表中的数据：年份=2023和月份=五月。目标是有一个公式，可以过滤数据并在满足两个条件时以一致的顺序（链接，公司，标题）显示结果。
问题
我构建了一个可以正确工作于一个数据集（Monoti）的公式，但对于另外两个数据集（Rumala和Arbito）却不按预期工作。
- 对于Monoti，它正确返回三个结果。
- 对于Arbito，它返回一个结果，尽管不应该返回任何结果（没有行满足两个条件）。
- 对于Rumala，它返回一个结果，但是不正确（如下图所示，数据应该是第二行，但来自第一行）。
目标
我寻求如何修改公式以正确过滤基于指定条件的不同工作表的数据的指导。我想检索相关数据并确保在满足条件的每一行中以一致的顺序（链接，公司，标题）显示它。任何建议或替代方法将不胜感激。
数据结构
每个工作表都有代表链接，公司，标题，年份和月份等的列。列的顺序可能在工作表之间有所不同，但所需的数据（链接，公司，标题）都存在于所有工作表中。每列应该有自己的标题，但应该只有一个包含所有数据的表。
我的尝试:
https://docs.google.com/spreadsheets/d/185CaPyHmg_dScbTCateG2HlmsBd85WDJnjx-_u49hnA/edit?usp=sharing
我使用的公式是
=QUERY(Rumala!A2:N, "SELECT N, J, D WHERE L='2020' AND M='May'", 1)
但它返回错误的结果。我还尝试使用=SORT(FILTER)和REGEXMATCH，但没有成功。
=SORT(FILTER(Rumala!A2:N, (REGEXMATCH(Rumala!L2:L, "^2023$")) * (REGEXMATCH(Rumala!M2:M, "^May$"))), 1)
预期结果
- 列A（公司）- 从工作表Monoti、Arbito、Rumala中检索列I、M、N。
- 列B（职位）- 从工作表Monoti、Arbito、Rumala中检索列B、I、J。
- 列C（链接）- 从所有工作表中检索列A。
列
英文:
Objective
I'm trying to retrieve data from different sheets in my spreadsheet based on two conditions: Year = 2023 and Month = May. The goal is to have one formula that filters the data and displays the results in a consistent order (LINK, COMPANY, TITLE) when both conditions are met.
Problem
I've constructed a formula that works correctly for one of the data sets (Monoti), but it's not functioning as expected for the other two (Rumala and Arbito).
- For Monoti, it correctly returns three results.
- For Arbito it returns one result even though it should return none (no rows meet the two conditions)
- For Rumala, it returns one result but it isn't correct (as shown in the picture below, the data should be the second row but it comes from the first one).
Goal
I'm looking for guidance on how to modify the formula to correctly filter data from different sheets based on the specified conditions. I want to retrieve the relevant data and ensure it is displayed in a consistent order (LINK, COMPANY, TITLE) for each row where the conditions are satisfied. Any suggestions or alternative approaches would be greatly appreciated.
Data Structure
Each sheet has columns representing LINK, COMPANY, TITLE, Year, and Month, among others. The order of the columns may differ between sheets, but the required data (LINK, COMPANY, TITLE) is present in all sheets. Each column should have its own header but there should be only one table containing all data.
My attempt:
https://docs.google.com/spreadsheets/d/185CaPyHmg_dScbTCateG2HlmsBd85WDJnjx-_u49hnA/edit?usp=sharing
The formula I have used is
=QUERY(Rumala!A2:N, "SELECT N, J, D WHERE L='2020' AND M='May'", 1)
but it yields the wrong results. I have also tried using =SORT(FILTER) and REGEXMATCH but without any success.
=SORT(FILTER(Rumala!A2:N, (REGEXMATCH(Rumala!L2:L, "^2023$")) * (REGEXMATCH(Rumala!M2:M, "^May$"))), 1)
Expected result
- Column A (Company) - retrieves columns I, M, N from sheets Monoti, Arbito, Rumala respectively.
- Column B (Post) - retrieves columns B, I, J from sheets Monoti, Arbito, Rumala respectively.
- Column C (Link) - retrieves column A from all sheets.
Column
答案1
得分: 1
以下是翻译好的内容：
这里是一个你可以测试的方法：
=let(Σ,{"公司","职位","职位链接","年份","月份"},
Λ,reduce(Σ,{"Monoti","Arbito","Rumala"},lambda(a,c,{a;choosecols(indirect("'"+c+"'!A2:Z"),index(xmatch(Σ,indirect("'"+c+"'!A1:Z1"),,-1)))})),
{"公司","职位","链接";filter(choosecols(Λ,1,2,3),choosecols(Λ,1)<>""，choosecols(Λ,4)=2023,choosecols(Λ,5)="五月")})
英文:
Here's one approach you may test out:
=let(Σ,{"Company","Title","Title_URL","Year","Month"},
Λ,reduce(Σ,{"Monoti","Arbito","Rumala"},lambda(a,c,{a;choosecols(indirect("'"&c&"'!A2:Z"),index(xmatch(Σ,indirect("'"&c&"'!A1:Z1"),,-1)))})),
{"Company","Post","Link";filter(choosecols(Λ,1,2,3),choosecols(Λ,1)<>"",choosecols(Λ,4)=2023,choosecols(Λ,5)="May")})
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论