У Trev есть таблица прогнозов продаж по продуктам, которую несколько пользователей просматривают и обновляют. Прогнозы изначально устанавливаются с помощью различных формул, но пользователи могут переопределить формулы, введя значение в любую ячейку, содержащую одну из формул. Если пользователь делает это, Треву было бы полезно, чтобы Excel каким-то образом выделял эту ячейку.

Вы можете использовать несколько подходов. Во-первых, вы можете использовать условное форматирование для выделения. Установите тип правила условного форматирования «Форматировать только ячейки, которые содержат», «Значение ячейки»

«Не равно», а затем введите формулу для сравнения. Это подскажет вам, когда значение в ячейке не равно той, что есть в формуле, но потенциальная «неприятность» возникает, если человек заменяет формулу результатом этой формулы. Например, если формула выдала бы результат «27» и пользователь вводит «27» в ячейку. Другая возможность — определить формулу в именованной константе, а затем использовать эту именованную константу в условном формате. Выполните следующие действия:

  1. Откройте вкладку «Формулы» на ленте.

  2. Щелкните Определить имя в группе Определенные имена. Excel отображает диалоговое окно «Новое имя». (См. Рис. 1.)

  3. В поле Имя введите имя, которое вы хотите присвоить этой формуле. В этом примере используйте CellHasNoFormula.

  4. Выберите все, что находится в поле «Ссылается на», в нижней части диалогового окна, и нажмите «Del». Это избавит от всего, что было в Excel раньше.

  5. Введите следующую формулу в поле «Относится к»:

  6. Щелкните ОК.

Теперь вы можете настроить некоторые условные форматы и использовать эту именованную формулу в формате. Просто установите тип правила условного форматирования «Использовать формулу для определения ячеек для форматирования» и введите следующую формулу в условие:

=CellHasNoFormula

Формула возвращает True или False, в зависимости от того, есть ли формула в ячейке или нет. Если формулы нет, возвращается значение True, и к ячейке применяется любой указанный вами формат. Другой подход — использовать определяемую пользователем функцию для возврата True или False, а затем настроить условный формат. Вы можете использовать очень простой макрос, например следующий:

Function IsFormula(Check_Cell As Range) As Boolean     Application.Volatile     IsFormula = Check_Cell.HasFormula End Function

Затем вы можете указать тип правила условного форматирования как Использовать формулу для определения ячеек для форматирования и ввести следующую формулу в условие, если, например, вы условно форматируете ячейку C1:

=NOT(IsFormula(C1))

Формула возвращает True, если в ячейке нет формулы, поэтому применяется условный формат. Единственным недостатком использования любой из этих формул для определения того, находится ли формула в ячейке, является то, что она не может определить, была ли формула в ячейке заменена другой формулой. Это применимо как к макро-подходу, так и к подходу с использованием определенных формул. Совершенно другой подход — это немного переосмыслить свой рабочий лист. Вы можете отделить ячейки для пользовательского ввода от ячеек, в которых используются формулы. Формула может использовать функцию ЕСЛИ, чтобы узнать, ввел ли пользователь что-либо в ячейку ввода. Если нет, ваша формула будет использоваться для определения значения; если да, то вводимые пользователем данные используются вместо вашей формулы. Такой подход позволяет сохранить нужные формулы без их перезаписи пользователем. Это обеспечивает большую целостность формул и результатов рабочего листа.

_Примечание: _

Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу, содержащую полезную информацию.

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (9270) применим к Microsoft Excel 2007, 2010 и 2013. Вы можете найти версию этого совета для старого интерфейса меню Excel здесь:

link: / excel-Highlighting_Values_in_a_Cell [Выделение значений в ячейке].