Прятки с формулами

Пример из жизни. Есть большая таблица с кучей формул и значений. Известно, что при вводе данных в таблицу оператор ошибся (с кем не бывает) и кое-где ввел в ячейки вместо формулы значения с клавиатуры. Задача - найти все ячейки с формулами и проверить - нет ли константы там, где должно быть вычисление.

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

Нажмите клавишу F5, далее в окне - кнопка Выделить (Special). Откроется очень полезное (жаль, что так глубоко "зарыто") диалоговое окно, при помощи которого можно выделять ячейки по определенному признаку:

select-const.png

Например:

  • только ячейки с константами (т.е. с введенными с клавиатуры значениями)
  • только ячейки с формулами и функциями
  • только видимые ячейки (без скрытых, например, при фильтрации или в свернутых итогах)
  • только пустые ячейки и т.д.

В последних версиях Excel 2007/2010 это окно доступно на вкладке Главная (Home) в группе Редактирование (Edit) - в выпадающем списке Найти и выделить (Find & Select). Команда называется Выделение группы ячеек (Go to special):

select_const2.png

Ссылки по теме:



Вадим
11.11.2012 01:04:25
Ctrl+~ . В ячейках отображаются формулы и становится видно, что вычислено, а что введено вручную.
Ольга
11.11.2012 01:05:08
Также сочетание Ctrl+G открывает это окно.
Витка
11.11.2012 01:05:39
Очень хорошая опция, спасибо.
формула или число стоит в ячейке еще можно увидеть в меню Сервис-Параметры- вкладка "Вид" и в "параметры окна" поставить галочку напротив "формулы". Но это не даст выделения ячеек. Будут видны все ячейки с содержимым.
Дмитрий
11.11.2012 01:06:17
Каким образом можно создать формулу, чтобы она проверяла в ячейке константа или формула?
ikki
11.11.2012 01:06:42
можно путем создания пользовательской функции:
Public Function ЕФОРМУЛА(target As Range) As Boolean
ЕФОРМУЛА = target.HasFormula
End Function
использование - обычное
=ЕФОРМУЛА(адрес_ячейки)
результат ИСТИНА означает, что ячейка содержит формулу, ЛОЖЬ - нет
21.06.2013 15:42:31
Отличная вещь. Теперь можно и через условное отслеживать где формулы.
25.11.2013 18:11:03
У Вас получилось использовать данную функцию в условном форматировании?
У меня нет.
Я на листе могу использовать функцию только в таком виде: =PERSONAL.XLSB!ЕФОРМУЛА(A1).
При попытке ввести данную формулу в условное форматирование - выдает ошибку, что нельзя использовать такую ссылку.
26.11.2013 01:43:09
Да получилось. Используйте следующие конструкции:
http://tinypic.com/view.php?pic=35kpv12&s=5#.UpPEENJdVEo      
http://tinypic.com/view.php?pic=2ev7v6d&s=5#.UpPERNJdVEo
26.11.2013 09:57:17
Спасибо, но это решение предполагает, что модуль должен находиться в каждой книге, где надо использовать эту функцию... я же хотел бы решение универсальное, чтобы вызывать функцию из личной книги макросов, т.е. для любой книги.
27.11.2013 07:34:30
О! Я сам интересуюсь такого рода личной книгой макросов - было бы весьма удобно каждый раз не копировать это добро. Если найдете решение раньше меня  - пожалуйста, дайте знать )
06.02.2015 18:59:16
Помещайте код не в личную книгу, а в личную надстройку. Т.е. в любую надстройку :)
26.11.2013 09:27:14
В Excel 2013 это встроенная функция, кстати - не надо ничего программировать :)
26.11.2013 09:57:46
Не всем работодатель устанавливает 2013-й офис.;)
05.02.2017 22:28:54
Николай, часто встречаю в файлах сотрудников ссылки на другие файлы, что создает ряд проблем. Как можно быстро найти ячейки, ссылающиеся на данные в других файлах excel.
14.12.2017 19:39:35
Меню Данные - Изменить связи
16.12.2018 13:15:02
Можно искать в формулах открывающую квадратную скобку [ через Ctrl+F
30.01.2018 04:54:25
В Excel 2013 это встроенная функция, кстати - не надо ничего программировать
Николай, это с учетом условного форматирования, или просто выделение ячеек содержащих формулы?!!!
У меня не получается поставить условное форматирование на ячейки содержащие формулы, помогите если эта проблема уже решена  
20.04.2018 11:30:49
А у вас получилось решить данную проблему, или нет?
16.12.2018 13:17:53
Выделить ячейки, затем Главная - Условное форматирование - Создать правило - Использовать формулу для определения форматируемых ячеек - ввести =ЕФОРМУЛА(A1), где вместо А1 вписать адрес первой ячейки из выделенного диапазона без долларов.
Наверх