Excel，从相同的行中获取不同的值

I'm trying to write a GPA calculator in Excel (LibreOffice really, but it works the same) and what I need help with is calculating quality points. I've only ever coded in C and similar languages, so spreadsheets and their notation are incredibly foreign to me.

我试着在Excel里写一个GPA计算器(LibreOffice真的，但是它是一样的)，我需要帮助的是计算质量分数。我只用C语言和类似的语言编写过代码，所以电子表格和它们的符号对我来说是非常陌生的。

In case you don't know, GPA is calculated by dividing your total quality points by your total credit hours, quality points being your grade set to a 4-scale in a particular class multiplied by the class's credit value. So, for example, if I got an B in a 4-hour class, I would get 3*4 = 12 quality points for that class. If I took a 17-hour semester and earned 63 quality points, my GPA for that semester is 63/17 = 3.706.

如果你不知道的话，GPA是用你的总质量分数除以你的总学时来计算的，质量分数是你在某一门课上的4分制分数乘以这个班的学分值。举个例子，如果我在一个4小时的课程中得了B，我会得到3*4 = 12个质量分数。如果我修了17个小时的课程，并且获得了63个质量分数，那么这个学期的GPA是63/17 = 3.706。

Getting to the point, my spread sheet is set up something like this

说到点子上，我的扩展表是这样设置的

         A          B           C
       GRADE     CREDITS     QUALITY
1        B          3           9
2        A          4          16
3        B          1           3
...

so my formula would look something like this

公式是这样的

IF(A1="A",4*B1,
IF(A1="B",3*B1,
IF(A1="C",2*B1,
IF(A1="D",  B1,0))))

The problem is, this code will only work for row one. For any other row, I'd have to replace all the 1s with the row number being calculated. There must be a better way to write this formula. How would I go about generalizing this so I can just copy and paste the formula without editing it?

问题是，这段代码只适用于第一行。对于任何其他行，我必须用正在计算的行号替换所有的1s。必须有更好的方法来写这个公式。我该如何推广它，这样我就可以复制粘贴公式而不用编辑它?

1 个解决方案

#1

In Excel you can copy and paste a formula, or fill-down a formula and it will automatically update the references for you. Have you tried copying the formula down? Otherwise you can do indirect formulas that use commands like INDIRECT and ROW, but I cannot guarantee those will convert to LibreOffice.

在Excel中，你可以复制粘贴一个公式，或者填写一个公式，它会自动更新你的参考资料。你试过把公式抄下来吗?否则，您可以使用间接公式，使用间接和行之类的命令，但我不能保证它们会转换为LibreOffice。

If you need to use INDIRECT formulas, it'd look something like this:

如果你需要使用间接公式，它看起来是这样的:

=IF(INDIRECT("A" & ROW())="A",4*INDIRECT("B" & ROW()),
IF(INDIRECT("A" & ROW())="B",3*INDIRECT("B" & ROW()),
IF(INDIRECT("A" & ROW())="C",2*INDIRECT("B" & ROW()),
IF(INDIRECT("A" & ROW())="D",  INDIRECT("B" & ROW()),0))))