Формулы массивов в условном форматировании файлов электронных таблиц Excel XML?

Excel обычно обрабатывает формулы условного форматирования, как если бы они были формулами массива, за исключением случаев их загрузки из файла XML-таблицы Excel 2002/2003.

Эта проблема связана только с форматом XML-таблицы Excel 2002/2003... собственный формат Excel работает нормально, как и более новый формат Excel 2007 XML (xlsx).

После загрузки электронной таблицы можно заставить ее работать правильно, выбрав форматированный диапазон, перейдя в диалоговое окно "Условное форматирование" и нажав "ОК", но это только устраняет проблему для сеанса.

Прецедент:

Введите следующее в новый лист:

  A B C
1 N N N
2 x x x
3 x x x

Создайте эту формулу условного формата в ячейках A1:C1 (ваш выбор симпатичных цветов для формата):

=(SUM(($A1:$C1="N")*($A$2:$C$2=A$3))>0)

Это формула массива, которая активируется для A1, B1 и C1, когда у любого из них есть "N", а ячейка в строке 2 ниже "N" равна ячейке в строке 3 текущего столбца.

(Это было упрощено из реальной бизнес-таблицы. Извините за сложность тестового примера, я пытаюсь найти более простой тестовый пример для представления здесь.)

И это работает... вы можете изменить N или X любым удобным для вас способом, и форматирование работает просто отлично.

Сохраните это как электронную таблицу XML. Закройте Excel и заново откройте файл. Форматирование теперь не работает. Теперь вы можете активировать условное форматирование, только если A1 - это "N", а A2 такое же, как A3, B3 или C3. Значения B1, B2, C1 и C2 не влияют на форматирование.

Теперь выберите A1: C1 и посмотрите на формулу условного форматирования. Точно так же, как и раньше. Хит ОК. Условное форматирование снова начинает работать и будет работать в течение всего сеанса, когда файл открыт.

Обходные пути рассматриваются:

  1. Предоставление файла в собственном (BIFF) формате Excel. Не вариант, эти таблицы генерируются на лету веб-сервером, и это только один из десятков типов рабочих книг, генерируемых нашей системой динамически.

  2. Предоставление файла в собственном формате XML Excel 2007 (xlsx). Не вариант, текущая база пользователей не имеет Office 2007 или подключаемого модуля совместимости.

  3. Попросив пользователей выбрать диапазон, войдите в диалоговое окно "Условное форматирование" и нажмите "ОК". Не вариант в этом случае неискушенным пользователям.

  4. Попросить пользователей открыть электронную таблицу XML, сохранить ее как собственный XLS, закрыть и заново открыть файл XLS. Это не работает! Форматирование остается неработающим в собственном формате XLS, если оно было загружено неработающим из файла XML. Если (3) выше выполнить перед сохранением, файл XLS будет работать правильно.

Я переписал условное форматирование, чтобы не использовать формулы массива. Так что я думаю, что на это в некоторой степени "ответили", но это все еще недокументированная, хотя и неясная, ошибка в обработке файлов XML в Excel 2002/2003.

Источник

2 ответа

Решение

Я пытался воссоздать проблему, которую вы описываете. Вот что я нашел.

  • Может последовательно воссоздать проблему с помощью Excel 2003 в Windows XP при сохранении в виде электронной таблицы XML.

  • Не удалось воспроизвести проблему с использованием Excel 2003 в Windows XP при сохранении в виде стандартной электронной таблицы xls.

  • Не удалось воспроизвести проблему с помощью Excel 2007 в Windows Vista при сохранении файла в собственном формате xlsx.

  • Не удалось воспроизвести проблему с помощью Excel 2007 в Windows Vista при сохранении файла в формате Excel 97-2003 xls.

    (Примечание. Все экземпляры Excel и Windows актуальны для всех обновлений Windows.)

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

Таким образом, ответ, кажется, заключается в использовании стандартного формата файла Excel 2003 при сохранении файла.

Кстати, это очень странная формула форматирования. Сложно представить, как бы вы его использовали. Это должно быть очень конкретное и необычное экономическое обоснование. У меня также есть чувство, что чего-то не хватает в вашем посте. (Я не обвиняю вас в нечестности - просто задаюсь вопросом, возможно, вы сократили формулу для удобочитаемости.) Если это не та формула, которую вы используете, отредактируйте исходное сообщение с полной формулой, и я буду рад вернуться к этой проблеме.

Источник

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

Источник
Другие вопросы по тегам