Я CPA с большим опытом работы с Excel, но, похоже, не могу найти ответ на этот вопрос (собственный). Есть ли способ применить условное форматирование для следующего примера:
ПРИМЕР: У меня есть формула =sum(B4,C28,A32,B40)---> Есть ли способ применить правило условного форматирования к ячейкам B4, C28, A32 и B40, которое автоматически выделяет ячейки, используемые в формуле?
^ это очень помогло бы мне с банковской сверкой, которую я выполняю. Вместо ручного выделения. Есть много ячеек, которые мне пришлось бы делать вручную. Я твердо верю в то, что сверки должны быть максимально автоматизированы (на самом деле большая часть моей разведки — это просто перетаскиваемый шаблон, который я сделал, и который автоматически определяет большинство элементов разведки).
ПРИМЕЧАНИЕ: В настоящее время я использую =isformula(xxxxx) в ячейках, которые мне нужно вручную связать. Затем выделяю, чтобы убедиться, что все захвачено/ничего не пропущено.
решение1
То, что вы описываете, похоже на аудит электронных таблиц. В Excel есть несколько встроенных инструментов, которые помогут в этом.
Вы можете использовать «Trace Precedents» на ленте «Формулы», чтобы рисовать линии к ячейкам, от которых зависит текущая ячейка. Используйте «Remove arrows», чтобы удалить стрелки.
Или вы можете использовать представление формул, нажав «Показать формулы» на ленте формул (или использовать Ctrl+` — это обратная галочка слева от цифры 1 на американской клавиатуре). Это отобразит формулы в ячейках вместо результатов, и любая ячейка, которая используется в формуле, будет выделена.
Условное форматирование — не лучший подход, потому что вам придется применять условные форматы ко всем ячейкам в таблице. Это сильно затормозит работу.
решение2
Да, вы можете использовать FORMULATEXT()в качестве основы для этого:
=IFERROR(FIND(SUBSTITUTE(CELL("ADDRESS",INDIRECT(ADDRESS(ROW(),COLUMN()))),"$",""),FORMULATEXT($C$1)),0)<>0
Выше предполагается, что вы знаете ячейку, в которой находится формула, которую вы изучаете. Она использует $C$1, но, конечно, вы можете изменить ее на любую ячейку, которую пожелаете.
Вы можете создать правило условного форматирования («CF») для любой отдельной ячейки.НО сделайте это, пока вы В этой ячейке: пока эта ячейка выбрана, затем скопируйте ячейку и вставьте форматы в любую другую ячейку, которую вы хотите включить в CF.
Я представляю, что у вас есть ячейка с формулой, SUM()и вы заполняете ячейки, на которые она ссылается напрямую. Если у вас есть список, который вы печатаете где-то, вы можете ссылаться на него напрямую при создании формулы CF.
Формула используется ADDRESS()для создания текстовой ссылки на ячейку для любой выбранной ячейки и, таким образом, для любых ячеек, на которые в конечном итоге накладывается CF. Затем она используется INDIRECT()для создания "реальной" ссылки на ячейку. Наконец, она используется SUBSTITUTE()для удаления знаков $ (дает вам "A1", а не $A$1").
Это становится материалом для FIND()поиска в SUM()функции, к которой вы добавляете ячейки. FORMULATEXT()используется для получения текста этой формулы, и поскольку "SUM(" и ")" не являются частью какой-либо приемлемой ссылки на ячейку, с ними ничего не нужно делать, и FIND()будет просто выполняться поиск среди ячеек, на которые ссылаются в скобках. IFERRORпроверяет, не потерпела ли она неудачу, выдавая 0 в качестве результата, если это так, потому что FIND()не может дать результат 0. Это означает, что вы либо получаете некоторое положительное целое число, либо 0 на этом шаге.
Наконец, CF проверяет, равно ли это 0, и если это НЕ 0, он выделяет ячейку нужным вам образом.
Это часть более широкой возможности, которую, похоже, никто не исследует. Я могу создавать именованные диапазоны, к которым я могу добавлять ()и вводить все, что мне нравится, в скобках так же, как аргументы в «реальной» функции. Пара поддерживающих именованных диапазонов затем дает мне именованный диапазон, который выглядит и действует как функция, запрограммированная в Excel, например, SUM()is, с любыми выбранными мной аргументами. Избранное здесь для коллег включает ourLOOKUP()(название компании, а не «наша») взятие аргументов ИНДЕКС/ПОИСКПОЗ и помещение их в функцию, построенную как VLOOKUP().
По сути, это UDF, без особых знаний VBA и без столкновений с местами, где использование VBA не допускается.
Немного неловко делать это в первый раз, и трогательно, но приятно.
Но вышесказанное будет обрабатывать вашу подсветку, если вы заполните ()в вашем SUM(), как я предполагаю. Не расширяет ответ на решение вашей более крупной задачи, только подсветку, о которой вы спрашиваете.