Как писать формулы в openpyxl в немецкой версии Excel

Содержание

Вопрос или проблема
Ответ или решение
1. Используйте правильный синтаксис для функций Excel
2. Убедитесь, что Excel обновляет формулы
3. Обратите внимание на параметры региональных настроек
Заключение

Вопрос или проблема

У меня есть следующая немецкая формула в Excel, которая работает прекрасно:

=WENN(UND(SVERWEIS(B2; '\\path\path\path\path\[filename.xlsx]sheetname'!$A:$C; 3; FALSCH) = 0,01; SVERWEIS(B2; '\\path\path\path\path\[filename.xlsx]sheetname'!$A:$D; 4; FALSCH) = DATUM(9999; 12; 31); XODER(RECHTS(LINKS(SVERWEIS(B2; '\\path\path\path\path\[filename.xlsx]sheetname'!$A:$E; 5; FALSCH); 8); 3) = "150"; RECHTS(LINKS(SVERWEIS(B2; '\\path\path\path\path\[filename.xlsx]sheetname'!$A:$E; 5; FALSCH); 8); 3) = "020")); "Muss neuen Preis bekommen!"; "")

Теперь я попытался автоматизировать процесс для всех имеющихся у меня данных с помощью Python, и моя английская формула выглядит следующим образом:

formula = (
   f'=IF(AND(VLOOKUP(B{row_num}, \'\\\\path\\path\\path\\path\\[filename.xlsx]sheetname\'!$A:$C, 3, FALSE) = 0.01, '
   f'VLOOKUP(B{row_num}, \'\\\\path\\path\\path\\path\\[filename.xlsx]sheetname\'!$A:$D, 4, FALSE) = DATE(9999, 12, 31), '
   f'XOR(RIGHT(LEFT(VLOOKUP(B{row_num}, \'\\\\path\\path\\path\\path\\[filename.xlsx]sheetname\'!$A:$E, 5, FALSE), 8), 3) = "150", '
   f'RIGHT(LEFT(VLOOKUP(B{row_num}, \'\\\\path\\path\\path\\path\\[filename.xlsx]sheetname\'!$A:$E, 5, FALSE), 8), 3) = "020")), '
   '"Muss neuen Preis bekommen!", "")'
)

При записи формулы в ячейки она правильно переводит все, кроме условия XOR. После этого я попытался просто вставить немецкую команду для XOR (XODER) в мой код Python, что привело к ошибке “#VALUE” в Excel. Мне просто нужно зайти в ячейку и нажать Enter, и формула рассчитывается правильно. Я пытался заставить Excel пересчитать все формулы на моем листе с помощью win32 или изменить способ записи формулы на моем листе Excel. Например, я попробовал это:

formulager1 = (
     f'=WENN(UND('
     f'SVERWEIS(B{row_num}; '
     f'\'\\\\path\\path\\path\\path\\[filename.xlsx]sheetname\'!$A:$C; 3; FALSCH) = 0,01; '
     f'SVERWEIS(B{row_num}; '
     f'\'\\\\path\\path\\path\\path\\[filename.xlsx]sheetname\'!$A:$D; 4; FALSCH) = DATUM(9999; 12; 31); '
     f'XODER('
     f'RECHTS(LINKS(SVERWEIS(B{row_num}; '
     f\'\\\\path\\path\\path\\path\\[filename.xlsx]sheetname\'!$A:$E; 5; FALSCH); 8); 3) = \"150\"; '
     f'RECHTS(LINKS(SVERWEIS(B{row_num}; '
     f'\'\\\\path\\path\\path\\path\\[filename.xlsx]sheetname\'!$A:$E; 5; FALSCH); 8); 3) = \"020\"'
     f')); \"Muss neuen Preis bekommen!\"; \"\")'
)

Я также пытался добавить префикс “_xlfn.”, который упоминается в этом вопросе:

Ничто из того, что я пробовал, не работает, поэтому мой вопрос таков:

Есть ли у кого-нибудь из вас идея, как я мог бы правильно записать свою формулу в Excel? Либо изменив способ записи формулы через Python-скрипт, либо настроив код так, чтобы все формулы пересчитывались, или даже способ, которым Excel правильно переводит условие XOR, что, на мой взгляд, было бы самым простым решением.

Спасибо заранее!

Ответ или решение

Ваш вопрос касается написания формул в OpenPyXL для немецкой версии Excel, особенно в контексте некорректной обработки функции XOR. Давайте рассмотрим несколько шагов и рекомендаций, которые могут помочь вам решить эту проблему.

1. Используйте правильный синтаксис для функций Excel

Как вы уже указали, Excel использует разные аргументы для функций в зависимости от локализации. В немецкой версии Excel функцией, эквивалентной XOR, является XODER, но вы должны быть уверены, что используете правильный синтаксис. Ваши попытки использовать XODER в формуле выглядят правильными, но стоит проверить правильность необходимых аргументов.

Пример правильной формулы будет выглядеть так:

formulager1 = (
    f'=WENN(UND('
    f'SVERWEIS(B{row_num}; '
    f'\'\\\\path\\path\\path\\path\\[filename.xlsx]sheetname\'!$A:$C; 3; FALSCH) = 0,01; '
    f'SVERWEIS(B{row_num}; '
    f'\'\\\\path\\path\\path\\path\\[filename.xlsx]sheetname\'!$A:$D; 4; FALSCH) = DATUM(9999; 12; 31); '
    f'XODER('
    f'RECHTS(LINKS(SVERWEIS(B{row_num}; '
    f'\'\\\\path\\path\\path\\path\\[filename.xlsx]sheetname\'!$A:$E; 5; FALSCH); 8); 3) = "150"; '
    f'RECHTS(LINKS(SVERWEIS(B{row_num}; '
    f'\'\\\\path\\path\\path\\path\\[filename.xlsx]sheetname\'!$A:$E; 5; FALSCH); 8); 3) = "020"'
    f')); "Muss neuen Preis bekommen!"; "")'
)

2. Убедитесь, что Excel обновляет формулы

Если после ввода формулы вы все еще получаете ошибку #VALUE, это может быть связано с тем, что Excel не распознает формулу как таковую. Попробуйте воспользоваться следующим подходом для принудительного обновления формулы:

Стремитесь добавить волшебные слова _xlfn. перед XODER, которое используется для совместимости с более старыми версиями Excel.
Или, если вы создаете файл, сохраните его, а затем откройте файл при помощи библиотеки win32, используя методы для принудительного обновления формул.

Пример кода для обновления:

import win32com.client as win32

excel = win32.Dispatch('Excel.Application')
workbook = excel.Workbooks.Open(r'\\path\path\path\path\[filename.xlsx]')
excel.CalculateUntilAsyncQueriesDone()  # Стараемся реформулировать все
workbook.Save()
workbook.Close()
excel.Quit()

3. Обратите внимание на параметры региональных настроек

Обязательно убедитесь, что Excel открыт с правильными региональными настройками, которые соответствуют вашей формуле. Иногда изменение языка Excel или системы влияет на то, как формулы обрабатываются.

Заключение

Если приведенные шаги не решают вашу проблему, я рекомендую вам создать минимальный рабочий пример, который может быть протестирован, чтобы выявить источник проблемы — может быть, это связано с самим файлом или данными, которые использует ваша формула. Также не забывайте проверять вашу версию OpenPyXL, чтобы убедиться, что вы используете последние функции и исправления.

Удачи с вашими формулами в OpenPyXL! Если у вас возникнут другие вопросы, не стесняйтесь обращаться.