英文:
How to fill cells dynamically based on the value of a cell? How to segregate values based on a particular cell?
问题
假设，我已经为特定科目获得了5个课程成果的分数（每个成果包含2分--每个科目总共10分）。
我已经获得了所有学生的总分，但不知道每个成果的分数。我想要根据总分列H来填写成果（列B至列F）。
例如- 一个总分为8的学生应该有数值- 2,2,2,1,1 或 2,2,1,2,1，等等
条件-
- 所有单元格在分数大于5的情况下应该被填满。
- 每个单元格的值应在1和2之间。在总分少于5时，值为0，因为我们没有足够的数值来填写：
我之前在B至E列使用了randbetween(1,2)（以获得1和2之间的随机值），对于F列，我使用了公式=10-SUM(B6:E6)
这个方法有效，但是在差值超过2的情况下（违反了我们的条件）不够有效。
英文:
Suppose, I have secured Marks for a particular subject which have 5 course Outcomes (each containing 2Marks--Total 10 marks for each subject).
I have the total marks obtained for all the students, but not the marks of each CO.
I want to fill the COs(Column B-F) based on the Column H(Total).
Example- A Student having total of 8 should have the value- 2,2,2,1,1 or 2,2,1,2,1, etc
Conditions-
- All the cells should be filled in case of marks with >5
- Each cell should have the value between 1 and 2. 0 in case when total is less than 5. As we don't have enough values to fill:
Previously, I used the randbetween(1,2) in Columns B to E (to get random values between 1 and 2) and for Column F, I used the formula =10-SUM(B6:E6)
It works fine, but doesn't work well if the difference is more than 2(which violates our condition)
答案1
得分: 1
这是您提供的代码的翻译部分：
对于2007版本，我们需要引用A列中的单元格，所以将其更改为空单元格或文本。将以下公式放在B2中，然后复制到下面的单元格：
=IF(SUM($A2:A2)+6-COLUMN(A$1)>=H2,1,IF(H2/(6-COLUMN(A$1))>=2,2,RANDBETWEEN(1,2)))
对于Office 365版本，不需要使A列为空或包含文本，因为我们不再需要引用它。可以使用以下公式：
=LET(
sm,H2,
rng,B2:F2,
clm,COLUMN(rng),
{1,1,1,1,1}+ISNUMBER(MATCH(clm,TAKE(UNIQUE(RANDARRAY(1,100,MIN(clm),MAX(clm),TRUE),TRUE),sm-5),0))
英文:
With 2007 we need to refer to the cell in column A, so change that to an empty cell or text. Put this in B2 and copy over and down:
=IF(SUM($A2:A2)+6-COLUMN(A$1)>=$H2,1,IF($H2/(6-COLUMN(A$1))>=2,2,RANDBETWEEN(1,2)))
with Office 365 we do not need Column A to be blank or text as we no longer need to refer to it:
=LET(
sm,H2,
rng,B2:F2,
clm,COLUMN(rng),
{1,1,1,1,1}+ISNUMBER(MATCH(clm,TAKE(UNIQUE(RANDARRAY(1,100,MIN(clm),MAX(clm),TRUE),TRUE),,sm-5),0)))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论