英文:
Counting 3 or more consecutive weeks in a horizontal dataset
问题
我有一个水平排列的数据集,其中包含员工姓名。人力资源部应该为他们添加注释。这是示例数据集:
我有一个公式,列出了那些连续3周或更多周没有受到人力资源部评论的员工。这个公式从最右侧开始计数,然后向左计数。
现在,数据集已经发生了变化,最新的一周将始终插入在性别列之后,看起来像这样:
周数以水平递减的方式排列。
现在我的问题是,我很难修改公式以适应新的数据集。
这是我在第一个数据集中使用的公式:
=let(range_,A3:12,row_,A3:B12,headr_,A2:2, col_,filter(headr_,right(headr_,5)="Notes"),data_,filter(range_,right(headr_,5)="Notes"), Σ,choosecols(makearray(rows(row_),columns(col_),lambda(r,c,if(left(index(col_,,c),3)="HR1",if(or(len(index(data_,r,c)),len(index(data_,r,c+1))),1,),))),sequence(counta(col_)/2,1,1,2)), let(Λ,index((counta(col_)/2)-byrow(Σ,lambda(z_,ifna(xmatch(1,z_,,-1))))),filter({row_,Λ,vlookup(index(row_,,1),{let(Σ,xmatch("HR1*",headr_,2,-1),choosecols(range_,1,Σ-2,Σ-1))},{2,3},)},Λ>2)))
我很难修改它以适应新的数据集。
这是示例表格:Google Sheets链接
数据选项卡包含新格式的数据集,第一个选项卡包含旧的数据和我使用的第一个公式。我还在表格内部包含了一些注释和示例期望结果。
英文:
I have a horizontal dataset which with employee names. HR should put on notes to them. This is the sample dataset:
I have a formula that lists the employees that hasn't been touched or never had comments from HR for 3 or more consecutive weeks. And the formula starts counting from the right most part to the left.
Now, the dataset has been changed and the latest week will always be insert after the gender column and will look like this:
the weeks are in reversed in descending order horizontally.
now my problem is that I am having a hard time modifying the formula to adapt the new dataset.
This is the formula that I used in the first dataset:
=let(range_,A3:12,row_,A3:B12,headr_,A2:2, col_,filter(headr_,right(headr_,5)="Notes"),data_,filter(range_,right(headr_,5)="Notes"), Σ,choosecols(makearray(rows(row_),columns(col_),lambda(r,c,if(left(index(col_,,c),3)="HR1",if(or(len(index(data_,r,c)),len(index(data_,r,c+1))),1,),))),sequence(counta(col_)/2,1,1,2)), let(Λ,index((counta(col_)/2)-byrow(Σ,lambda(z_,ifna(xmatch(1,z_,,-1))))),filter({row_,Λ,vlookup(index(row_,,1),{let(Σ,xmatch("HR1*",headr_,2,-1),choosecols(range_,1,Σ-2,Σ-1))},{2,3},)},Λ>2)))
and I am having a hard time modifying this to adapt the new dataset.
here is the sample sheet: https://docs.google.com/spreadsheets/d/192sSrwxGR-ltuhWO3j30Rw4r9C7BvANNQlGNMlqNVCE/edit#gid=0
The Data tab contains the new format of dataset and the 1st tab contains the old and the 1st formula that I used. I have also included some notes inside the sheet and sample desired results.
答案1
得分: 0
I'll provide the translated text from your request without the code part:
在这个帖子里稍微修改了原来的公式
英文:
Slightly modifying the original formula posted in this thread here
=let(range_,A3:12,row_,A3:B12,headr_,A2:2,
col_,filter(headr_,right(headr_,5)="Notes"),data_,filter(range_,right(headr_,5)="Notes"),
Σ,choosecols(makearray(rows(row_),columns(col_),lambda(r,c,if(left(index(col_,,c),3)="HR1",if(or(len(index(data_,r,c)),len(index(data_,r,c+1))),1,),))),sequence(counta(col_)/2,1,1,2)),
let(Λ,index(byrow(Σ,lambda(z_,ifna(xmatch(1,z_),columns(Σ)+1)-1))),filter({row_,Λ,vlookup(index(row_,,1),{let(Σ,xmatch("HR1*",headr_,2),choosecols(range_,1,Σ-2,Σ-1))},{2,3},)},Λ>2)))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论