Код для работы с excel

import numpy as np
import openpyxl
import numpy
import pylab


class RegressionEquation:

    def zeroDegreeCoef(y):
        return sum(y) / len(y)

    def oneDegreeCoef(x, y):
        mult = sum(x[i] * y[i] for i in range(len(y)))
        norma = sum(x[i] * x[i] for i in range(len(x)))
        return float(mult) / float(norma)

    def interactionCoef(x1, x2, y):
        mult = sum(x1[i] * x2[i] * y[i] for i in range(len(x1)))
        norma = sum(x1[i] * x1[i] * x2[i] * x2[i] for i in range(len(x1)))
        return mult / norma

    def twoDegreeCoef(x, y):
        a = sum(x[i] * x[i] for i in range(len(x))) / len(x)
        mult = sum(((x[i] * x[i]) - a) * y[i] for i in range(len(y)))
        norma = sum(((x[i] * x[i]) - a) ** 2 for i in range(len(x)))
        return mult / norma

    def drawIsoline(regressionEquation, levels):
        x = numpy.arange(-2, 6.5, 0.05)
        y = numpy.arange(0, 15, 0.05)
        xgrid, ygrid = numpy.meshgrid(x, y)
        zgrid = regressionEquation(x1=0, x2=xgrid, x3=ygrid, x4=0, x5=0)
        cs = pylab.contour(xgrid, ygrid, zgrid, levels)
        pylab.xlabel('x2')
        pylab.ylabel('x3')
        pylab.clabel(cs)
        pylab.show()


path = 'D:/STUDY/Диплом/расчеты/Книга с расчетами.xlsx'
wb = openpyxl.load_workbook(filename=path)

sheet = wb['Эксперимент 2']

b0 = sheet['B40'].value
b1 = sheet['B41'].value
b2 = sheet['B42'].value
b3 = sheet['B43'].value
b4 = sheet['C40'].value
b5 = sheet['C41'].value
b12 = sheet['C42'].value
b13 = sheet['C43'].value
b14 = sheet['D40'].value
b15 = sheet['D41'].value
b23 = sheet['D42'].value
b24 = sheet['D43'].value
b25 = sheet['E40'].value
b34 = sheet['E41'].value
b35 = sheet['E42'].value
b45 = sheet['E43'].value

path = 'D:/STUDY/Диплом/расчеты/Финальные расчеты.xlsx'
wb = openpyxl.load_workbook(filename=path)

sheet = wb['Лист1']

f = lambda x1, x2, x3, x4,x5: b0 + b1 * x1 + b2 * x2 + b3 * x3 + b4 * x4 + b5 * x5 + b12 * x1 * x2 + b13 * x1 * x3 + b14 * x1 * x4 + b15 * x1 * x5 + b23 * x2 * x3 + b24 * x2 * x4 + b25 * x2 * x5 + b34 * x3 * x4 + b35 * x3 * x5 + b45 * x5 * x4

x0 = [sheet.cell(i, 2).value for i in range(2, 48)]
x1 = [sheet.cell(i, 3).value for i in range(2, 48)]
x2 = [sheet.cell(i, 4).value for i in range(2, 48)]
x3 = [sheet.cell(i, 5).value for i in range(2, 48)]
x4 = [sheet.cell(i, 6).value for i in range(2, 48)]
x5 = [sheet.cell(i, 7).value for i in range(2, 48)]

y = [sheet.cell(i, 13).value for i in range(2, 48)]

print(y)

b0 = RegressionEquation.zeroDegreeCoef(y)

b1 = RegressionEquation.oneDegreeCoef(x1, y)
b2 = RegressionEquation.oneDegreeCoef(x2, y)
b3 = RegressionEquation.oneDegreeCoef(x3, y)
b4 = RegressionEquation.oneDegreeCoef(x4, y)
b5 = RegressionEquation.oneDegreeCoef(x5, y)

b12 = RegressionEquation.interactionCoef(x1, x2, y)
b13 = RegressionEquation.interactionCoef(x1, x3, y)
b14 = RegressionEquation.interactionCoef(x1, x4, y)
b15 = RegressionEquation.interactionCoef(x1, x5, y)

b23 = RegressionEquation.interactionCoef(x2, x3, y)
b24 = RegressionEquation.interactionCoef(x2, x4, y)
b25 = RegressionEquation.interactionCoef(x2, x5, y)

b34 = RegressionEquation.interactionCoef(x3, x4, y)
b35 = RegressionEquation.interactionCoef(x3, x5, y)

b45 = RegressionEquation.interactionCoef(x4, x5, y)

b11 = RegressionEquation.twoDegreeCoef(x1, y)
b22 = RegressionEquation.twoDegreeCoef(x2, y)
b33 = RegressionEquation.twoDegreeCoef(x3, y)
b44 = RegressionEquation.twoDegreeCoef(x4, y)
b55 = RegressionEquation.twoDegreeCoef(x5, y)

sheet['D49'] = b0

sheet['D50'] = b1
sheet['D51'] = b2
sheet['D52'] = b3
sheet['D53'] = b4
sheet['D54'] = b5

sheet['E50'] = b12
sheet['E51'] = b13
sheet['E52'] = b14
sheet['E53'] = b15
sheet['E54'] = b23

sheet['F50'] = b24
sheet['F51'] = b25
sheet['F52'] = b34
sheet['F53'] = b35
sheet['F54'] = b45

sheet['G50'] = b11
sheet['G51'] = b22
sheet['G52'] = b33
sheet['G53'] = b44
sheet['G54'] = b55

x11 = [sheet.cell(i, 8).value for i in range(2, 48)]
x22 = [sheet.cell(i, 9).value for i in range(2, 48)]
x33 = [sheet.cell(i, 10).value for i in range(2, 48)]
x44 = [sheet.cell(i, 11).value for i in range(2, 48)]
x55 = [sheet.cell(i, 12).value for i in range(2, 48)]

f = lambda x1, x2, x3, x4,x5: b0 + b1 * x1 + b2 * x2 + b3 * x3 + b4 * x4 + b5 * x5 + b12 * x1 * x2 + b13 * x1 * x3 + b14 * x1 * x4 + b15 * x1 * x5 + b23 * x2 * x3 + b24 * x2 * x4 + b25 * x2 * x5 + b34 * x3 * x4 + b35 * x3 * x5 + b45 * x5 * x4 + b11 * (x1 ** 2 - a) + b22 * (x2 ** 2 - a) + b33 * (x3 ** 2 - a) + b44 * (x4 ** 2 - a) + b55 * (x5 ** 2 - a)

for i in range(0, 46):
    sheet.cell(i + 2, 14).value = f(x1=x1[i], x2=x2[i], x3=x3[i], x4=x4[i], x5=x5[i])

wb.save(path)

levels = [x for x in range(0, 70, 10)]

RegressionEquation.drawIsoline(f, levels)

A = np.array([[2 * b11, b12, b13, b14, b15], [b12, 2 * b22, b23, b24, b25], [b13, b23, 2 * b33, b34, b35],
              [b14, b24, b34, 2 * b44, b45], [b15, b25, b35, b45, 2 * b55]])
b = np.array([-b1, -b2, -b3, -b4, -b5])
x = np.linalg.solve(A, b)

res = f(x[0], x[1], x[2], x[3], x[4], x[5])