在另一个计算中使用公式的结果 [英] Using result of formula in another calculation

问题描述

我想使用第二个for i in range语句中计算的值，使用第四个for i in range语句计算新值;但是，我收到错误：无法将字符串转换为浮点数：'E2 * 37.5'

I would like to use the value calculated in the second, "for i in range" statement to calculate a new value using the fourth, "for i in range" statement; however, I receive the error: "could not convert string to float: 'E2*37.5'"

如何调用计算的数值， sheet ['F {}'，format（i）] ='E {} * 37.5'.format（i）而不是公式/字符串？

How do I call upon the numerical value calculated in, sheet['F{}',format(i)] ='E{}*37.5'.format(i) instead of the formula/string?

import openpyxl


wb = openpyxl.load_workbook('camdatatest.xlsx', read_only= False, data_only = True)

# Assuming you are working with Sheet1
sheet = wb['Sheet1']

for i in range(2,80):
    sheet['D{}'.format(i)] = '=C{}/3'.format(i)
for i in range(2,80):
    sheet['F{}'.format(i)] = '=E{}*37.5'.format(i)
for i in range(2,80):
    sheet['H{}'.format(i)] = '=D{}*G2*50'.format(i) 
for i in range(2,80):
    sheet['I{}'.format(i)].value = float(sheet['F{}'.format(i)].value)/float(sheet['H{}'.format(i)].value)



wb.save('camdatatestoutput.xlsx' , data_only= True)

推荐答案

不幸的是，这不太可能因为

Unfortunately that's not quite possible because

openpyxl从不评估公式

openpyxl never evaluates formula

还有其他库可以这样做。但是，您可以通过识别可以使用另一个单元格引用而不是此处的计算值来克服您的问题。

There are other libraries that may do so. However your problem can be overcome by recognizing that you can use yet another cell reference instead of the calculated value here.

for i in range(2,80):
    sheet['I{}'.format(i)] = '=E{}*37.5/H{}'.format(i,i)

请注意，您无法设置Ix单元格的值，因为您实际上没有Ex或Hx的值细胞。 （你可能有，但你的问题不清楚你是否这样做）

Note that you can't set the value for the Ix cell because you don't actually have a value for the Ex or Hx cells. (well you might have but it's not clear from your question if you do)

这篇关于在另一个计算中使用公式的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！