Как узнать где используется ячейка excel
Как узнать, где в Excel используется определенный именованный диапазон?
После создания именованного диапазона вы можете использовать этот именованный диапазон во многих ячейках и формулах. Но как узнать эти ячейки и формулы в текущей книге? В этой статье представлены три хитрых способа решить эту проблему.
Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу .
- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Найдите, где используется определенный именованный диапазон, с помощью функции поиска и замены
Мы можем легко применить Excel Найти и заменить функция, чтобы узнать все ячейки, применяющие определенный именованный диапазон. Пожалуйста, сделайте следующее:
1. нажмите Ctrl + F одновременно клавиши, чтобы открыть диалоговое окно «Найти и заменить».
Внимание: Вы также можете открыть это диалоговое окно «Найти и заменить», щелкнув значок Главная > Найти и выбрать > Найдите.
2. В открывшемся диалоговом окне «Найти и заменить» выполните следующие действия:
(1) Введите имя определенного именованного диапазона в поле Найти то, что коробка;
(2) Выберите Workbook из В раскрывающийся список;
(3) Щелкните значок Найти все кнопку.
Внимание: Если раскрывающийся список "Внутри" не отображается, щелкните значок Доступные опции кнопку, чтобы развернуть параметры поиска.
Теперь вы увидите, что все ячейки, содержащие имя указанного именованного диапазона, перечислены в нижней части диалогового окна «Найти и заменить». Смотрите скриншот:
Внимание: Метод «Найти и заменить» не только обнаруживает все ячейки, использующие этот определенный именованный диапазон, но также обнаруживает все ячейки, покрывающие этот именованный диапазон.
Найдите, где определенный именованный диапазон используется с VBA
Этот метод представит макрос VBA, чтобы узнать все ячейки, которые используют определенный именованный диапазон в Excel. Пожалуйста, сделайте следующее:
1. нажмите другой + F11 одновременно клавиши, чтобы открыть окно Microsoft Visual Basic для приложений.
2. Нажмите Вставить > Модули, скопируйте и вставьте следующий код в открывающееся окно модуля.
VBA: найти, где используется определенный именованный диапазон
3. Нажмите Run или нажмите F5 Ключ для запуска этого VBA.
4. Теперь в первом открывшемся диалоговом окне Kutools for Excel введите имя рабочего листа и нажмите OK кнопка; а затем во втором диалоговом окне открытия введите в него имя определенного именованного диапазона и щелкните значок OK кнопка. Смотрите скриншоты:
5. Теперь появляется третье диалоговое окно Kutools for Excel, в котором перечислены ячейки с использованием определенного именованного диапазона, как показано ниже.
После нажатия OK кнопку, чтобы закрыть это диалоговое окно, эти найденные ячейки сразу выбираются на указанном листе.
Внимание: Этот VBA может искать только ячейки, используя определенный именованный диапазон на одном листе за раз.
Найдите, где используется определенный именованный диапазон с Kutools for Excel
Если у вас установлен Kutools for Excel, его Заменить имена диапазонов утилита может помочь вам найти и перечислить все ячейки и формулы, которые используют определенный именованный диапазон в Excel.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!
Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel
1. Нажмите Кутулс > Еще > Заменить имена диапазонов , чтобы открыть диалоговое окно «Заменить имена диапазонов».
2. В открывшемся диалоговом окне «Заменить имена диапазонов» перейдите к Имя и фамилия и нажмите Базовое имя раскрывающийся список и выберите из него определенный именованный диапазон, как показано ниже:
Теперь все ячейки и соответствующие формулы, которые используют определенный именованный диапазон, сразу отображаются в диалоговом окне.
3. Закройте диалоговое окно «Заменить имена диапазонов».
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Демонстрация: найдите, где в Excel используется определенный именованный диапазон
Если в формуле используются влиятельные или зависимые ячейки, может быть сложно проверить формулы на точность или найти источник ошибки.
Ячейки- ячейки, на которые ссылается формула в другой ячейке. Например, если ячейка D10 содержит формулу =B5,ячейка B5 является влияемой на ячейку D10.
Зависимые ячейки — это ячейки, содержащие формулы, которые ссылаются на другие ячейки. Например, если ячейка D10 содержит формулу =B5, ячейка D10 является зависимой от ячейки B5.
Для проверки формул можно использовать команды "Ячейки трассировки" и "Зависимые ячейки" для графического отображения и трассировки связей между этими ячейками и формулами с помощью стрелки трассировки, как показано на рисунке.
Чтобы отобразить связи между ячейками, выполните указанные здесь действия.
Щелкните Файл > параметры > Дополнительные параметры.
Примечание: Если вы используете Excel 2007; нажмите кнопку Microsoft Office , Excel параметры, а затем выберите категорию Дополнительные параметры.
В разделе Показать параметры для этой книги выберите книгу и убедитесь, что выбрано все в разделе Для объектов, показать.
Чтобы указать ссылки на ячейки в другой книге, эта книга должна быть открыта. Microsoft Office Excel не может перейти к ячейке книги, если она не открыта.
Выполните одно из указанных ниже действий.
Укажите ячейку, содержащую формулу, для которой следует найти влияющие ячейки.
Чтобы отобразить стрелку трассировки для каждой ячейки, которая непосредственно предоставляет данные активной ячейке, на вкладке Формулы в группе Зависимости формул нажмите кнопку Ячейки трассировки .
Синие стрелки показывают ячейки, не вызывающие ошибок. Красные стрелки показывают ячейки, вызывающие ошибки. Если на выбранную ячейку имеется ссылка из другого рабочего листа или книги, путь от выбранной ячейки к значку рабочего листа будет обозначен черной стрелкой . Другую книгу необходимо открыть до того, как Excel начнет отслеживать эти зависимости.
Чтобы определить следующий уровень ячеек, которые предоставляют данные для активной ячейки, снова нажмите кнопку ячейки.
Чтобы удалить стрелки трассировки по одному уровню, начните с влияемой ячейки, которая дальше от активной ячейки. Затем на вкладке Формулы в группе Зависимости формул щелкните стрелку рядом с кнопкой Удалить стрелки ивыберите удалить стрелки . Для удаления другого уровня стрелок зависимости вновь нажмите эту кнопку.
Укажите ячейку, для которой следует найти зависимые ячейки.
Чтобы отобразить стрелку трассировки для каждой ячейки, зависимой от активной ячейки, на вкладке Формулы в группе Зависимости формул нажмите кнопку Зависимые ячейки .
Синие стрелки показывают ячейки, не вызывающие ошибок. Красные стрелки показывают ячейки, вызывающие ошибки. Если на выбранную ячейку ссылается ячейка на другом месте или в другой книге, при наконечнике выбранной ячейки на значок . Другую книгу необходимо открыть до того, как Excel начнет отслеживать эти зависимости.
Чтобы определить следующий уровень ячеек, зависящих от активной ячейки, снова нажмите кнопку Зависимые ячейки .
Чтобы удалить стрелки зависимости по одному уровню, начиная с зависимой ячейки, наиболее далекой от активной ячейки, на вкладке Формулы в группе Зависимости формул щелкните стрелку рядом с кнопкой Удалить стрелки ивыберите удалить зависимые стрелки . Для удаления другого уровня стрелок зависимости вновь нажмите эту кнопку.
В пустой ячейке введите = (знак равно).
Вы выберите ячейку, а затем на вкладке Формулы в группе Зависимости формул дважды нажмите кнопку трассировки.
Чтобы удалить все стрелки трассировки, на вкладке Формулы в группе Зависимости формул нажмите кнопку Удалить стрелки .
Проблема: Microsoft Excel издает звуковой сигнал при выборе команды Зависимые ячейки или Влияющие ячейки.
Если Excel при нажатии кнопки Зависимые зависимые или Зависимые зависимые , Excel отследил все уровни формулы или пытается отследить элемент, который невозможно найти. Следующие элементы на этих таблицах, на которые могут ссылаться формулы, невозможно отследить с помощью средств аудита:
Ссылки на текстовые поля, внедренные диаграммы или рисунки на таблицах.
Отчеты для отчетов в отчетах.
Ссылки на именуемые константы.
Формулы, расположенные в другой книге, которые ссылаются на активную ячейку, если другая книга закрыта.
Чтобы увидеть выделение цветом влияющих ячеек для аргументов формулы, выделите ячейку и нажмите клавишу F2.
Для выбора ячейки на другом конце стрелки дважды щелкните эту стрелку. Если ячейка находится на другом листе или в другой книге, дважды щелкните черную стрелку для отображения диалогового окна Переход, а затем дважды щелкните требуемую ссылку в списке Переход.
Все стрелки трассировки исчезают, если изменить формулу, на которую указывают стрелки, вставить или удалить столбцы или строки либо удалить или переместить ячейки. Для восстановления стрелок зависимости после сделанных изменений, необходимо снова использовать на этом листе команды аудита. Для отслеживания первоначальных стрелок зависимости следует до внесения изменений распечатать лист с отображенными стрелками зависимости.
Связывание с другими книгами — очень распространенная задача в Excel, но иногда вы можете найти книгу, которая ссылок не может найти, хотя Excel сообщает о том, что она существует. Нет способа автоматического поиска всех внешних ссылок, используемых в книге, однако существует несколько способов, используемых вручную. Вам нужно искать формулы, определенные имена, объекты (например, текстовые поля или фигуры), названия диаграмм и ряды данных диаграммы.
Имя любой Excel книги, с помощью ссылки на которую вы ссылались, будет связана с расширением XL* (например, .xls, .xlsx, XLSM), поэтому рекомендуемый способ — найти все ссылки на частичное расширение XL. Если вы ссылались на другой источник, необходимо определить оптимальный поисковый запрос.
Поиск ссылок, используемых в формулах
Нажмите CTRL+F, чтобы запустить диалоговое окно Найти и заменить.
В поле Найти введите.xl.
В поле В пределах выберите книга.
В поле Искать в выберите формулы.
В отображемом списке наймем в столбце Формула формул, содержащих XL. В этом случае Excel найдено несколько экземпляров функции бюджетного Master.xlsx.
Чтобы выбрать ячейку с внешней ссылкой, щелкните ссылку на эту строку в списке.
Совет: Щелкните любой за колонок, чтобы отсортировать столбец и сгруппировать все внешние ссылки.
На вкладке Формулы в группе Определенные имена выберите пункт Диспетчер имен.
Проверьте каждую запись в списке и проверьте, нет ли в столбце Ссылка внешних ссылок. Внешние ссылки содержат ссылку на другую книгу, например [Budget.xlsx].
Щелкните любой за колонок, чтобы отсортировать столбец и сгруппировать все внешние ссылки.
Если вы хотите удалить сразу несколько элементов, можно сгруппнуть несколько элементов, нажав клавишу SHIFT или CTRL и щелкнув левой кнопкой мыши.
Нажмите клавиши CTRL+G, нажмите клавиши CTRL+G, чтобы перейти в диалоговое окно Перейти, а затем выберите специальные > объекты > ОК. При этом будут выбраны все объекты на активном сайте.
Нажимая клавишу TAB, переходить между выбранными объектами, а затем искать в строка формул ссылку на другую книгу, например [Budget.xlsx].
Щелкните название диаграммы, которую вы хотите проверить.
В строка формул наймем ссылку на другую книгу, например [Budget.xls].
Выберите диаграмму, которую нужно проверить.
На вкладке Макет в группе Текущий выделение щелкните стрелку рядом с полем Элементы диаграммы и выберите ряд данных, которые нужно проверить.
На строка формул , наймем ссылку на другую книгу, например [Budget.xls] в функции РЯД.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
В большинстве случаев Excel отображает в ячейках результаты вычисления формулы, а не саму формулу. Поэтому внешне ячейка с введенным значением 5 и ячейка с формулой =КОРЕНЬ(25) выглядят одинаково. Есть несколько способов найти на листе именно те ячейки, которые содержат формулы.
Способ 1. Выделение ячеек с формулами
С помощью встроенной команды "Найти и выделить" Вы всегда можете найти в означенном диапазоне те ячейки, которые содержат формулы.
Выделите нужный диапазон и выберите команду "Найти и выделить" - "Формулы".
После выбора этой команды в выделенном перед ее нажатием диапазоне будут выделены все ячейки, в которых есть формулы. Если же до выбора команды Вы не выделите какой-нибудь конкретный диапазон (выделена будет одна ячейка), то данная команда сработает на весь активный лист.
Помимо вызова с ленты, воспользоваться этой командой можно и с помощью горячих клавиш. Комбинация Ctrl+G вызовет окно "Переход" .
После появления окна, нужно нажать кнопку "Выделить. ". Откроется окно "Выделить группу ячеек". В нем нужно выбрать вариант "формулы" и нажать ОК. В отличие от предыдущего способа, это окно позволяет указать, какие именно формулы надо выделить:
- те, которые возвращают числа;
- те, которые возвращают текст;
- те, которые возвращают булево значение (ИСТИНА/ЛОЖЬ);
- те, которые возвращают ошибки.
Способ 2. Отображение формул в ячейках
Пользователь может переключить режим отображения формул на листе. Вместо установленного по умолчанию отображения значений можно попросить Excel показывать сами формулы, введенные в ячейки. Команда для переключения находится на вкладке "Формулы" и называется "Показать формулы" (активируется также сочетанием горячих клавиш Ctrl+' ).
Это команда переключатель (то есть кнопка может находиться в одном из двух состояний: включено или отключено). Результат ее включения - на рисунке ниже.
Обратите внимание, что команда действует только для того листа, на котором была активирована. Также при ее включении происходит автоматическое увеличение ширины столбцов, которые возвращаются к исходным значениям после отключения команды.
Видеоверсию данной статьи смотрите на нашем канале на YouTube
Чтобы не пропустить новые уроки и постоянно повышать свое мастерство владения Excel - подписывайтесь на наш канал в Telegram Excel Everyday
Много интересного по другим офисным приложениям от Microsoft (Word, Outlook, Power Point, Visio и т.д.) - на нашем канале в Telegram Office Killer
Вопросы по Excel можно задать нашему боту обратной связи в Telegram @ExEvFeedbackBot
Вопросы по другому ПО (кроме Excel) задавайте второму боту - @KillOfBot
Как проверить, содержит ли / найти ячейки формулы в Excel?
Если у вас есть большой рабочий лист, содержащий несколько формул, и теперь вы хотите найти или проверить, содержат ли формулы формулы. Есть ли у вас какие-нибудь хорошие способы сразу найти или проверить ячейки с формулами в Excel?
Проверьте, содержат ли ячейки формулы с функцией
По умолчанию есть простая функция, позволяющая проверить, содержит ли ячейка формулу в Excel, сделайте следующее:
1. Введите эту формулу: = ISFORMULA (A1) (A1 это ячейка, которую вы хотите проверить, является ли это формулой) в пустую ячейку, чтобы проверить, содержит ли конкретная ячейка формулу или нет, затем нажмите Enter ключ к результату, НЕПРАВДА указывает, что это не формула, ИСТИНА обозначает формулу. Смотрите скриншот:
2. Затем вы можете перетащить дескриптор заполнения в диапазон ячеек, в которых вы хотите проверить соответствующие ячейки, если это формулы, см. Снимок экрана:
Найдите и выделите все ячейки с формулами с помощью команды Перейти к специальной
Если вы хотите найти и выделить ячейки формулы, вы также можете применить Перейти к специальному команда для решения этой задачи.
1. Выберите диапазон, в котором вы хотите выделить ячейки формулы.
2. Нажмите Главная > Найти и выбрать > Перейти к специальному, см. снимок экрана:
3. В Перейти к специальному диалоговое окно, выберите Формулы и проверьте все параметры под Формулы, см. снимок экрана:
4. Затем нажмите OK, выделены сразу все ячейки с формулами. Смотрите скриншот:
Выделите все ячейки с формулами с кодом VBA
Следующий код VBA может помочь вам выделить все ячейки формулы, чтобы выделить их.
1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модулии вставьте следующий код в Модули Окно.
Код VBA: выделить все ячейки формулами
3, Затем нажмите F5 ключ для запуска этого кода, а затем выберите диапазон данных, в котором вы хотите найти формулы из окна подсказки, см. снимок экрана:
4. Нажмите OK кнопку, и все ячейки формулы были выделены желтым цветом, как показано на следующем снимке экрана:
Внимание: В приведенном выше коде xRg.Interior.Color = vbYellow , вы можете изменить цвет выделения - желтый на свой вкус, например красный, зеленый, синий и т. д.
Автоматически выделять все формулы во всей книге с помощью Kutools for Excel
Kutools for ExcelАвтора Выделить формулы Эта функция может помочь вам автоматически выделить формулы во всей книге, а выделенные ячейки будут изменяться по мере динамического изменения ячейки.
После установки Kutools for Excel, пожалуйста, сделайте следующее :( Бесплатная загрузка Kutools для Excel сейчас )
1. Нажмите Кутулс Плюс > Дизайн рабочего листа, И новый Дизайн вкладка будет отображаться на ленте, щелкните Выделить формулы под Дизайн вкладку см. скриншоты:
2. И тогда все ячейки формулы на всех листах были выделены сразу.
Читайте также: