Как использовать условное форматирование для отметки ячеек, которые ссылаются на другой лист или книгу.
Смотреть видео
- Использование условного форматирования для отметки ячеек формулы
- Новое правило, используйте формулу,
=ISFORMULA(A1) - Спасибо Крейгу Броуди: отметьте ячейки формулы, которые являются ссылками на другие листы
- Новое правило, используйте формулу,
=ISNUMBER(FIND("!"),FormulaText(A1))) - Расширение: чтобы пометить ячейки, указывающие на внешние книги, используйте
- эта формула:
=ISNUMBER(FIND(")",FORMULATEXT(A1))) - Вы можете использовать автосумму в нескольких ячейках одновременно
- Когда вы редактируете формулу в поле условного форматирования и нажимаете стрелку для перемещения,
- вы получите ссылки на ячейки. Чтобы предотвратить это, используйте F2, чтобы изменить режим ввода на режим редактирования.
Стенограмма видео
Учитесь Excel из, Эпизод 12154: Отметьте связанные формулы.
Привет. Добро пожаловать в сетевую трансляцию. Я Билл Джелен. Сегодня есть способ узнать, какие ячейки являются ячейками формулы, а (неразборчиво - 00:12) ячейками связаны ячейки формулы.
Хорошо. Итак, вот что мы собираемся делать. У нас есть некоторые данные, и я просто поместил сюда много 1234. Q1 и Q2, это ячейки SUM, которые пересекаются, и затем у меня также есть просто набор случайных формул, которые я ввел здесь, и на самом деле давайте, внизу, давайте поместим строку TOTAL. Итак, вот мы ' Я выберу все эти ячейки, и мы сможем ввести AUTOSUM одним махом.
Хорошо. Итак, вот что мы собираемся делать. Мы собираемся подойти к A1, выбрать все наши данные и отметить, что A1 является активной ячейкой. Это действительно важно, когда мы это настраиваем. УСЛОВНОЕ ФОРМАТИРОВАНИЕ, создайте НОВОЕ ПРАВИЛО, ИСПОЛЬЗУЙТЕ ФОРМУЛУ, и мы скажем = ISFORMULA of A1. Теперь это новинка ISFORMULA в Excel 2013. Если вы все еще используете Excel 2010 или Excel 2007, это вам не поможет. Пора перейти на Office 365. Мы выберем синий ШРИФТ для всего, что имеет подобную формулу. Хорошо. Итак, все ячейки формулы подсвечиваются. (= ISFORMULA (A1))
Но этот совет от Крейга Броуди: Крейг - частый участник, и он говорит: «Эй, есть способ пометить только ячейки, которые связаны между собой; другими словами, получаем ли мы данные из другого листа или другой книги. Итак, мы сделаем УСЛОВНОЕ ФОРМАТИРОВАНИЕ, НОВОЕ ПРАВИЛО, ИСПОЛЬЗУЕМ ФОРМУЛУ ДЛЯ ОПРЕДЕЛЕНИЯ, КАКИЕ ЯЧЕЙКИ ФОРМАТИРУЕМ, и вот формула, которую мы собираемся использовать. =, ну собственно, начнем с изнанки. Мы собираемся сказать, что найдем! в формуле - FORMULATEXT - еще один новый элемент в Excel 2013 - из A1, и он будет просматривать формулу, искать! и сообщать нам, где он находится. Если он там, это будет число, числовая позиция, например, восклицательные знаки на 7-м, 14-м или 15-м месте, но если его там нет,он возвращает ошибку. (= НАЙТИ («!», FORMULATEXT (A1)))
Итак, теперь нам нужно обнаружить ошибку, и поэтому мы могли бы сказать = is error, а затем поместить все это в NOT или, прямо здесь, предложение Крейга, ISNUMBER (а затем щелкните здесь и) вот так , а затем сделаем их оранжевым ЦВЕТОМ ШРИФТА. Щелкните ОК и щелкните ОК. Между прочим, эй, я собирался нажать клавишу СТРЕЛКА ВПРАВО, чтобы перейти сюда, и когда вы это сделаете, он начнет вставлять ссылки на ячейки. Просто нажмите F2, прежде чем мы это сделаем, а затем ГОТОВО изменится на РЕДАКТИРОВАТЬ, и вы можно двигаться влево и вправо. Нажмите ОК, хорошо, и теперь все ячейки, которые имеют внешние ссылки на другой лист или другую книгу, отмечены оранжевым цветом. Все ячейки с обычными формулами отмечены синим цветом. Классный, крутой трюк есть. (= ЕЧИСЛО (НАЙТИ («!», ФОРМУЛАТЕКСТ (A1))))
Эй, давайте возьмем трюк Крейга и немного расширим его. Итак, уловка Крейга рассчитана на то, что каждая ссылка на внешний рабочий лист имеет знак!. А как насчет ссылок на внешние книги? Ну у них всегда есть). Хорошо, давайте выберем наши данные, КОНТРОЛЬ *, и мы сделаем УСЛОВНОЕ ФОРМАТИРОВАНИЕ, НОВОЕ ПРАВИЛО, ИСПОЛЬЗУЕМ ФОРМУЛУ, и я вставлю последнюю формулу, хорошо? Видишь ли, теперь мне нужно это изменить! к а). Итак, прямо сейчас я нахожусь в режиме ENTER, и если я нажимаю клавишу СТРЕЛКА ВЛЕВО, видите ли, он начинает выдавать мне ссылки на ячейки, что действительно, очень раздражает. Поэтому вместо этого нажмите клавишу F2, и здесь внизу ENTER изменится на EDIT. Теперь я могу использовать клавиши со стрелками влево и вправо столько, сколько хочу. Ищите). Мы изменим их на белый ШРИФТ с красной ЗАЛИВКОЙ, вот так. Нажмите ОК, нажмите ОК,и есть внешние ссылки на внешние книги, которые будут отображаться красным цветом. (= ЕЧИСЛО (НАЙТИ («)»; ФОРМУЛАТЕКСТ (A1))))
Хорошо. Ну, привет, моя новая книга Power Excel With, издание 2017 г., 617 разгаданных тайн Excel; щелкните значок i в правом верхнем углу, чтобы узнать, как можно купить эту книгу.
Заканчиваю сегодня. Итак, мы используем УСЛОВНОЕ ФОРМАТИРОВАНИЕ, чтобы пометить ячейки формулы, просто чтобы отметить любую ячейку, в которой есть формула. Мы используем НОВОЕ ПРАВИЛО, ИСПОЛЬЗУЕМ ФОРМУЛУ, = ISFORMULA (A1), но, благодаря Крейгу Броуди, помечаем ячейки формулы, которые являются ссылками на другие листы, мы используем ISNUMBER, ищите это !, FORMULATEXT of A1, а затем я расширил это чтобы указать на внешние книги, ISNUMBER, найдите). Хорошо, еще несколько советов. Вы можете использовать AUTOSUM в нескольких ячейках одновременно, или когда вы редактируете формулу в диалоговом окне УСЛОВНОЕ ФОРМАТИРОВАНИЕ или в диспетчере имен и нажимаете клавиши со стрелками для перемещения, он будет вставлять ссылки на ячейки. Чтобы предотвратить это, используйте F2 для перехода из режима ENTER в режим EDIT.
Хорошо, привет. Я хочу поблагодарить вас за то, что заглянули. Увидимся в следующий раз на другой сетевой трансляции от.
Скачать файл
Загрузите образец файла здесь: Podcast2154.xlsm
