У меня проблема с удаленными записями в Excel с помощью Openpyxl
Сегодня на работе я решил впервые использовать Openpyxl, чтобы моим коллегам не приходилось вручную заполнять таблицы Excel.
Код, который я написал, работает нормально, но когда я пытаюсь вставить следующую формулу, он этого не может сделать.
Формула:
ws['D11'] = r'=IF(AND(AND(LEFT('диск '!B2, 4)=\'Гиг\',OR(MID('диск'!B2, 6, 3)=\' 500\', MID('диск'!B2, 6, 3)=\'800\', MID('диск'!B2, 6, 4)=\'1000\')), MEDIAN(IF('диск '!$B$2:$B$136='диск'!B2, 'диск'!$C$2:$C$136))),)'
В чем может быть ошибка? Предполагалось, что это формула массива, но с ней тоже не сработало.....
Возможно, я ошибаюсь в апострофах, но последние несколько часов пытаюсь решить эту проблему и никак не могу.
С чем работает код:
Создал документ Excel с несколькими листами. Два из них содержат информацию, по которой происходит расчет, а третий лист совершенно пуст. Туда средствами Python вставляются формулы, которые при запуске кода автоматически подсчитывают результат в своих ячейках.
Я ожидаю, что код вернет мне медианное значение, рассчитанное на основе данных на листе «диск».
Если кто-нибудь знает более простое решение, я бы хотел его иметь.
@CharlieClark Спасибо за совет, обязательно попробую, когда вернусь домой!
Имя вашего листа «диск»? в названии листа нет ни пробела, ни скобки? если да, то я думаю, что формула должна быть просто «=MID(drive!B2, 6, 3)», то есть без одинарных кавычек.
@rachel Спасибо! Я даже не задумывалась о том, что название листа может настолько изменить картинку! Действительно, это был не просто «драйв», а «драйв м». И именно поэтому моя формула не работала.
Вы можете использовать utils.quote_sheetname(wb['drive m'].title), если имя вашего листа — «диск m».
О, хорошо! Я буду иметь это в виду.
Ответы 2
Я пишу формулу
=IF(AND(AND(LEFT(drive!B2, 4) = "Gig",OR(MID(drive!B2, 6, 3) = "500", MID(drive!B2, 6, 3) = "800", MID(drive!B2, 6, 4) = "1000")), MEDIAN(IF(drive!$B$2:$B$136=drive!B2, drive!$C$2:$C$136))),)
в Экселе. сохрани это. разархивируйте его. Переходи /Book1/xl/worksheets/sheet1.xml. Я видел, что он хранится так:
Итак, приведенный ниже код должен работать:
import openpyxl
from openpyxl.worksheet.formula import ArrayFormula
from openpyxl import utils
wb = openpyxl.load_workbook("formula.xlsx")
ws = wb['Sheet']
reference_ws = wb['drive']
quote_sheetname = utils.quote_sheetname(reference_ws.title)
ws["A1"] = ArrayFormula("A1", f'=IF(AND(AND(LEFT({quote_sheetname}!B2, 4) = "Gig",OR(MID({quote_sheetname}!B2, 6, 3) = "500", MID({quote_sheetname}!B2, 6, 3) = "800", MID({quote_sheetname}!B2, 6, 4) = "1000")), MEDIAN(IF({quote_sheetname}!$B$2:$B$136 = {quote_sheetname}!B2, {quote_sheetname}!$C$2:$C$136))),)')
wb.save("formula.xlsx")
Отредактировано, я обновляю drive в формуле до utils.quote_sheetname(reference_ws.title)
Кстати, я бы посоветовал всегда использовать utitliy quote_sheetname(ws.title) в ссылках.
@CharlieClark да, так намного лучше.
Большое спасибо всем за советы! Именно благодаря вам я понял, в чем была ошибка.
При написании формулы на Python она запрашивает апострофы с обеих сторон, что нарушило мою ссылку на другой лист в этом документе (который на самом деле назывался не просто «диск», а «диск м»). Двойные кавычки ситуацию не спасли, поскольку с ними сам Python уже не считал ссылку ссылкой. Как только я вручную переименовал лист с «drive m» на «drivem», все заработало.
Окончательная формула выглядит так:
ws[‘D9’] = ArrayFormula(‘D9’, ‘=IF(AND(AND(LEFT(drivm!B2, 4)=’Giga‘, OR(MID(drivm!B2, 6, 3)=’500’, MID(drivm! B2, 6, 3)=‘800’, MID(drivm!B2, 6, 4)=‘1000’)), MEDIAN(IF(drivm!$B$2:$B$136=drivm!B2, drivm!$C$2:$C$136))),'))
При работе с такими формулами лучше всего просмотреть исходный файл XML, чтобы узнать, как Excel хранит формулы. Но необработанная строка с обратной косой чертой неверна. Просто используйте двойные и одинарные кавычки. И, если это формула массива, используйте ее.