计算水平数据集中连续3周或更多周的次数。

huangapple go评论67阅读模式
英文:

Counting 3 or more consecutive weeks in a horizontal dataset

问题

我有一个水平排列的数据集,其中包含员工姓名。人力资源部应该为他们添加注释。这是示例数据集:

1st dataset

我有一个公式,列出了那些连续3周或更多周没有受到人力资源部评论的员工。这个公式从最右侧开始计数,然后向左计数。

现在,数据集已经发生了变化,最新的一周将始终插入在性别列之后,看起来像这样:

revised 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)))

我很难修改它以适应新的数据集。

这是示例表格:Google Sheets链接

数据选项卡包含新格式的数据集,第一个选项卡包含旧的数据和我使用的第一个公式。我还在表格内部包含了一些注释和示例期望结果。

英文:

I have a horizontal dataset which with employee names. HR should put on notes to them. This is the sample dataset:

1st 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:

revised dataset

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:

在这个帖子里稍微修改了原来的公式

计算水平数据集中连续3周或更多周的次数。

英文:

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)))

计算水平数据集中连续3周或更多周的次数。

huangapple
  • 本文由 发表于 2023年6月21日 23:17:38
  • 转载请务必保留本文链接:https://go.coder-hub.com/76524846.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定