| 楼主 w83365040371 |
Q:怎么将字母评分转为具体数值 如下图所示, 数字都在字母的前面(数字不包括分数和负数)  比如A2单元格的值为AC2B0.5C, A、B和C是在实际操作中的字母打分, B、C前面的数字2、0.5是其对应的系数, 也就是说A2单元格有2个B和0.5个C, 而A前面没有系数, 就自动的认为系数为1, 即有1个A, 所以A2的转换公式为 AC2B0.5C=1*A+1*C+2*B+0.5*C=1*1+1*3+2*2+0.5*3=9.5 A:利用的经典公式的思路, 再配合sumif函数的使用(sumif函数多维引用比较关键),可以解决此问题, 公式如下
  按字母评分.zip |
| 2楼 w83365040371 |
在解析过程中, 将row($1:$99)改为row($1:$9),以便于构造维度比较小的数组进行分析, 同时以单元格A2的值""为例, 则一楼的公式改为 SUM(TEXT(0&SUBSTITUTE(MID(A2,ROW($1:$9),COLUMN(A:I)-1),"E","@"),"[>];;1;!0")*SUMIF(E:E,MID(A2,ROW($1:$9)+COLUMN(A:I)-1,1),F:F)*ISERR(-(0&MID(A2,ROW($1:$9)-1,1)))) 公式中的MID(A2,ROW($1:$9)+COLUMN(A:I)-1,1)是用来截取A2中的每个字符, 并构建成一个9*9的矩阵, 结果见下图 图7.  然后利用sumif函数, 将每个字母转换为其对应的数值, 同时将非字母字符转算为0, 用蓝色标记 图8.  公式中的MID(A2,ROW($1:$9),COLUMN(A:I)-1)是用来获得上面表格中字符所对应的系数, 比如, 在第一行中, 字母"A"所对应的系数是"", 字母"C" 对应的系数是”A"(由这两个列子,我们可以知道,mid函数截取的字母"A"、"C"的系数并不是其真实的系数,所以我们还要进一步将其转化为真实的系数), MID()函数展开结果如下图所示 图3.  然后用我们text函数, 将上面的公式mid()截取的系数转换为数值, 对于上面的列子,字母"A", 截取到得系数是"",那么要再公式上加个"0&", 使得"",转变为1；上例中的字母"C"的系数"A"转变为0 图6.  当字母的系数的字符长度大于1时, 比如0.5C, 那么我们要判断系数的起始位置, 防止该字符被重复取值同时也要正确的获得每个字母的系数,利用公式中的ISERR(-(0&MID(A2,ROW($1:$9)-1,1))), 来判断每个字母系数的起始位置 图4  那么同时满足图4、6、8的数据就是我们想要的,见下图5  最后我们将图5中,仅余的那些黄色标记的单元格数值相加,就是所求结果 |
| 3楼 w83365040371 |
二、函数变体: 我们还可用查找函数, 来替代sumif返回多维引用, 使用lookup替代sumif
 字符串中的字母的系数如果为分数，该怎么修改公式？ 即A2="AC1/2C1/3C" |
| 4楼 tzfcn |
 |
| 5楼 luckydog |
不是这样理解的吧，并非以小数点为界，我的理解是：字母前面有数字的就用数字乘以字母对应的分数取值，字母前面没有数字的就直接取其对应的分数;以第一个 AC2B0.5C 为例，前两个字母AC左侧都没有数字就取他对应的分数值1+3，第三个字母B左侧数字是2应该取2乘2，第四个字母前面为0.5，则取0.5乘3，所以 AC2B0.5C =1+3+2*2+0.5*3结果为9.5 |
| 6楼 tzfcn |
呵呵，高手就是高手！经过您的指点，还真是那么回事！ |
| 7楼 luckydog |
呵呵，相禹才是真正的高手呀，真是佩服 |
| 8楼 syh186 |
请问高手们，如果评分标准和评分结果不在同一工作表中，怎么修改那个求和的公式啊 |
| 9楼 w83365040371 |
SUMIF(E:E,MID(A2,ROW($1:$99)+COLUMN(A:I)-1,1),F:F) 这个函数中的E:E,和F:F修改下 |
| 10楼 W-E-N-D-Y |
好复杂啊 |