英文:
Check off tabularized lotto numbers automatically
问题
我们当地的酒吧有一个筹款彩票活动，人们选择6个彩票号码，每个星期六开奖后，任何匹配的号码都会被标记出来。这个过程每周重复一次，直到有人的6个号码都被标记出来，他们就赢得了奖金。
我有一个包含50个人的彩票号码的电子表格。另外，我有每周的彩票号码在另一个表格中。
我目前手动逐个进行标记。我知道我可以编写一个VBA脚本，通过嵌套循环来检查每个人的号码与每个开奖号码是否匹配，但这似乎很笨重。我无论如何都无法找到一个适用于每个单独号码的条件格式公式。
对于自动化的任何建议将不胜感激。
英文:
There is a fundraising lotto in our local pub whereby people pick 6 lotto numbers, and after each Saturday draw any matching numbers are highlighted. This is repeated each week until someone has all 6 of their numbers highlighted and they win the prizemoney.
I have a list of 50 people's lotto numbers in a spreadsheet. Separately, I have each week's lotto numbers in a different table.
I currently go through highlighting them manually. I know I could write a vba script with a loop within a loop within a loop checking each person's number with each result number but that seems very clunky. I can't for the life of me relate a conditional formatting formula applying to each individual number.
Any advice for automation would be gratefully received.
答案1
得分: 3
我对你的问题有稍微不同的解释。为了看看它可能如何工作，我将周数放入一个单元格，并在条件格式中使用了以下公式，以查看在n周后结果会如何显示：
=COUNTIF(INDIRECT("J2:O"&$H$2+1),A2)
所以在4周后，没有人赢得比赛，但在5周后，第19行的人赢得了比赛：
如果你是逐周添加结果，你可以使用足够大的范围来包括所有的结果，例如：
=COUNTIF($J$2:$O$20,A2)
英文:
I had a slightly different interpretation of your question. To see how it might work out, I put the week number into a cell and used this formula in conditional formatting to see how the results would look after n weeks:
=COUNTIF(INDIRECT("J2:O"&$H$2+1),A2)
So after 4 weeks nobody has won, but after 5 weeks the person in row 19 has won:
If you were adding results week by week, you could just use a big enough range to include all the results e.g.
=COUNTIF($J$2:$O$20,A2)
答案2
得分: 2
B2单元格的条件格式公式是：
=ISNUMBER(MATCH(B2,$I$2:$I$7,0))
将引用$I$2:$I$7更改为您所列出的选号位置。
然后将该格式应用于所有人选择的数字。
英文:
Conditional formatting formula in B2 is:
=ISNUMBER(MATCH(B2,$I$2:$I$7,0))
Change the reference $I$2:$I$7 to wherever your drawn numbers are listed.
Then apply the formatting to all the numbers people have picked.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论