Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Еще…Меньше
Предположим, вам нужно быстро изменить некоторые формулы, но вы еще не работали с книгой и не знаете, где находятся формулы. Вот как их найти.
-
Вы можете выбрать ячейку или диапазон ячеек.
Если выбрать одну ячейку, поиск будет происходить на всем. При выборе диапазона поиск будет только в этом диапазоне.
-
Щелкните Главная > найти & Выберите >Перейти к специальным.
-
Щелкните Формулыи при необходимости счистите любой из флажков под кнопкой Формулы.
4. Нажмите кнопку ОК.
Подробнее о поиске в Excel
-
Поиск или замена текста и чисел на листе
-
Поиск объединенных ячеек
-
Поиск диапазонов с использованием определенных имен
-
Удаление или разрешение циклической ссылки
-
Поиск скрытых ячеек на листе
Нужна дополнительная помощь?
Достаточно часто в таблицах бывает такая ошибка, когда формула случайно заменяется значением. Этот тип ошибок обычно трудно определяется, особенно если лист содержит большое количество формул. В этом приеме описывается два способа быстро идентифицировать формулы в листе, выделяя их. Затем при уменьшении масштаба вы могли бы определить пробелы в группах формул.
Использование окна Выделение группы ячеек
Этот метод выявления ячеек с формулами легкий, но не динамический. Другими словами, он хорош для единичной проверки.
- Выберите одну ячейку в листе.
- Выберите Главная ► Редактирование ► Найти и выделить ► Выделение группы ячеек для открытия диалогового окна Выделение группы ячеек.
- В окне Выделение группы ячеек установите переключатель в положение формулы и убедитесь, что все флажки ниже установлены.
- Нажмите кнопку ОК. Excel выберет все ячейки с формулами.
- Нажмите кнопку Цвет заливки в группе Шрифт вкладки Главная. Выберите любой цвет, который еще не используется.
- Используйте элемент управления Масштаб и задайте для своего листа небольшой масштаб (например, 25%).
- Внимательно проверьте лист и посмотрите, какие ячейки остались невыделенными. Возможно, это формула, которая была перезаписана значением.
Если вы не делали никаких изменений, то можете нажать кнопку Отменить (или нажать Ctrl+Z) для отмены цветовой заливки, которую применили в шаге 6.
Использование условного форматирования
Этот метод определения ячеек с формулами потребует небольшой настройки, но он имеет явное преимущество по сравнению с предыдущим, так как является динамическим. Ячейки с формулами определяются сразу, как только заполняются.
Чтобы настроить условное форматирование, выполните следующие действия.
- Выберите Формулы ► Определенные имена ► Присвоить имя для открытия диалогового окна Создание имени.
- В окне Создание имени введите следующую строку в поле Имя:
CellHasFormula. - Введите такую формулу в поле Диапазон:
=ПОЛУЧИТЬ.ЯЧЕЙКУ(48;ДВССЫЛ("rc";ЛОЖЬ)). - Нажмите кнопку ОК, чтобы закрыть диалоговое окно Создание имени.
- Выделите все ячейки, к которым хотите применить условное форматирование. Как правило, они составляют диапазон от А1 до правого нижнего угла используемой области листа.
- Выберите Главная ► Стили ► Условное форматирование ► Создать правило для открытия диалогового окна Создание правила форматирования.
- В верхней части окна выберите пункт Использовать формулу для определения форматируемых ячеек.
- Введите следующую формулу в поле диалогового окна (рис. 196.1):
=CellHasFormula. - Нажмите кнопку Формат для открытия диалогового окна Формат ячеек и выберите тип форматирования для ячеек, содержащих формулу.
- Нажмите кнопку OК, чтобы закрыть окно Формат ячеек, и снова нажмите OК, чтобы закрыть окно Создание правила форматирования.
Рис. 196.1. Окно для установки условного форматирования для выделения ячеек с формулами
После выполнения этих шагов каждая ячейка, которая содержит формулу и находится в пределах выбранного в шаге 5 диапазона, будет отформатирована по вашему выбору. Кроме того, при вводе формулы в диапазон, для которого задано условное форматирование, ячейка немедленно поменяет вид. Это форматирование позволяет вам легко определить ячейку, которая должна содержать формулу, но не содержит.
Формула, которую вы вводили в шаге 3, — макрос XLM. Следовательно, вам необходимо сохранить книгу с расширением с поддержкой макросов (используя расширение XLSM). Если вы сохраните книгу в виде XLSX-файла, Excel удалит имя CellHasFormula.
На листе с множеством формул часто бывает трудно ориентироваться. Например, чтобы удалить ненужные формулы нужно их сначала найти. Для поиска формул на листе в EXCEL есть ряд интересных инструментов
.
Для того, чтобы посмотреть какие ячейки на листе содержат формулы можно воспользоваться пунктом меню кнопкой
(маленькая кнопочка).
Если нажать в том же меню кнопку
Влияющие ячейки
или
Зависимые ячейки
, то появятся синие стрелки, которые также могут быть полезными при нахождении формул.
Также можно выделить ячейки с формулами другим способом:
. Затем, выделяем ячейки путем изменения фона или цвета шрифта.
Также можно прочитать статью
Выделение с помощью Условного форматирования ячеек, содержащих и НЕ содержащих формулы
.
Когда вы пишете новую формулу в Microsoft Excel, половина дела — найти правильную функцию для использования. К счастью, Excel дает вам возможность быстро найти функцию, которая вам нужна в вашей ситуации.
Доступ к инструменту «Вставить функцию»
Откройте электронную таблицу в Excel и выберите ячейку. Если вы планируете вставить функцию, как только найдете ее, выбор ячейки даст вам фору. Затем вы можете открыть функцию вставки одним из двух способов.
- Щелкните значок «Вставить функцию» (fx) в левой части строки формул.
- Перейдите на вкладку «Формулы» и нажмите «Вставить функцию» в левой части ленты.
Когда откроется окно «Вставить функцию», вы увидите параметр поиска вместе с раскрывающимся списком. Это дает вам два простых метода поиска нужной функции.
Чтобы воспользоваться поиском, введите ключевое слово или фразу в поле «Поиск функции» в верхней части окна и нажмите «Перейти». Например, если вы ищете функцию, которая поможет вам в вычислениях, связанных со временем и датой, попробуйте выполнить поиск по запросу «время».
Выпадающий список также поможет вам быстро найти полезную функцию. Щелкните его рядом со словами «Или выберите категорию» и выберите категорию. Вы увидите общие параметры, такие как Финансовые, Логические, Текстовые и другие. Выбрав категорию, вы увидите все доступные в ней функции.
Еще одна категория, на которую следует обратить внимание, — это «Последние использованные». Как следует из названия, здесь вы найдете те функции, которые использовали в последнее время. Это может быть полезно, если вы использовали функцию, скажем, на прошлой неделе, но не можете вспомнить, что это было. Отметьте эту категорию, и функция может появиться у вас.
В большинстве случаев, независимо от того, выполняете ли вы поиск или выбираете категорию, это просто сужает для вас выбор. Итак, следующий шаг в поиске нужной функции — проверка ее описаний. Щелкните функцию в списке, и вы увидите ее описание и синтаксис в нижней части окна.
Например, вам может понадобиться функция квартиля. Вы можете видеть, что Excel предлагает несколько разных вариантов. Выбрав каждый из них и просмотрев их описания, вы сможете найти то, что вам нужно.
Если после того, как вы сузите выбор функций и просмотрите описания, вы все еще не уверены, подходит ли эта функция для вас, вы можете получить дополнительную помощь. Выберите функцию в списке и нажмите «Справка по этой функции» в нижнем левом углу окна.
Это приведет вас на веб-сайт поддержки Microsoft, где вы найдете функцию, которая предоставляет дополнительную информацию и примеры использования.
Используйте найденную функцию
Когда вы найдете функцию, которую хотите использовать, убедитесь, что у вас выделена ячейка, в которую вы хотите ее вставить. Затем либо дважды щелкните функцию в списке, либо выберите ее и нажмите «ОК».
Затем вы увидите инструмент «Аргументы функции», открытый для настройки формулы с помощью функции.
Вверху введите набор данных, число, массив или то, что вызывает функция.
Внизу вы еще раз увидите это краткое описание функции, но вы также увидите дополнительные детали и результат окончательной формулы.
Давайте посмотрим на простой пример. Здесь мы выбрали функцию СУММ. В окне «Аргументы функции» мы ввели диапазон ячеек в поле «Число1». Вы можете видеть справа от поля значения в этих ячейках, и вы можете просмотреть результат формулы в двух местах в окне.
Когда вы закончите добавлять все детали для формулы, нажмите «ОК», и формула появится в вашей ячейке со своими результатами.
Попробуйте автозаполнение формул
Еще один способ найти функцию в Excel — использовать автозаполнение формул. Это полезно, если вы знаете, какая функция вам нужна, но не уверены в том, какой вариант вам нужен.
Когда вы вводите знак равенства (=) в ячейку и начинаете вводить первые буквы функции, вы увидите раскрывающийся список совпадений. Вы можете щелкнуть каждый параметр в списке, чтобы рядом с ним отобразилось его описание. Затем, чтобы использовать функцию, дважды щелкните ее, и вы увидите необходимые аргументы.
Например, мы знаем, что нам нужна функция COUNT, но не уверены, какая именно. Вводим = COUNT и видим выпадающий список опций. Описание COUNTIF дает нам понять, что это именно то, что нам нужно.
Мы дважды щелкаем «СЧЁТЕСЛИ» в списке и видим аргументы, необходимые для формулы. Это позволяет нам просто ввести то, что просят, и заполнить формулу.
Работа с функциями и формулами в Excel может быть немного пугающей, если вы не делаете это часто. Но с такими полезными функциями поиск и использование нужной функции может быть намного проще.
Функции ЛЕВСИМВ, ПРАВСИМВ и ПСТР прекрасно применяются для разделения строк на слова или фрагменты текстов, но при условии, что вам заранее известны позиции символов с которых будет разделение. Что же делать если заранее вам не известно где в текстовой строке находится тот символ, начиная с которого нужно вырезать фрагмент текста?
Пример формулы НАЙТИ, ДЛСТР и ПРАВСИМВ в Excel
Допустим у нас имеется прайс-лист с кодами товаров, как получить часть символов после дефиса с каждого кода, если дефис каждый раз находиться на новой позиции символов?
- PTR-422
- CORPO-6755
- SVCCALL-56532
Функция ЛЕВСИМВ нам сразу не подходит, так как необходимо получить последнюю часть каждого кода. Функция ПРАВСИМВ так же не справиться с данной задачей, ведь в ее аргументах следует указать точное количество символов возвращаемого текста из всех разных кодов разной длины. Если в аргументе будет указано фиксированное числовое значение, то для некоторых кодов сработает, а для большинства будет слишком много или мало количество символов, возвращаемых функцией ПРАВСИМВ.
На практике очень часто приходится автоматически находить определенный символ, чтобы функция сама находила начальную позицию для отделения фрагмента текста из исходной строки.
Для реализации данной задачи следует использовать формулу с комбинацией функций ПРАВСИМВ, ДЛСТР и НАЙТИ:
Благодаря функции НАЙТИ можно автоматически определять позицию в текстовой строке для указанного символа в ее аргументах. А после использовать номер позиции в следующих операциях, например, при автоматической генерации значений для второго аргумента функции ПРАВСИМВ. Реализуется генерация путем определения необходимого числа при вычитании от длины строки, которую возвращает функция ДЛСТР номера позиции символа – «-».
Пример использования НАЙТИ и ПСТР в формуле Excel
В следующем примере, изображенном на рисунке, функция НАЙТИ используется в формуле вместе с функцией ПСТР для выборки средних чисел между дефисами из кода товаров прайс-листа.
Как видно на рисунке формула сначала ищет номер позиции для символа с помощь функции НАЙТИ. А после найденный номер позиции использует в своих аргументах функция ПСТР.
Функция НАЙТИ требует заполнить минимум 2 из 3-х аргументов:
- Искомый_текст – здесь необходимо указать текст, который следует найти и получить его порядковый номер (позицию) в исходной текстовой строке.
- Просматриваемый_текст – тут указываем ссылку на ячейку с исходной строкой, которая содержит искомый символ или текст.
- Нач_позиция – это не обязательный аргумент. Здесь можно указать номер позиции символа в строке, с которого следует начинать поиск. Если строка содержит более одного найденного искомого символа, то с помощью данного необязательного аргумента можно указать номер символа с которого будет просматриваться остальная часть строки. Если он не указан в этом аргументе, то по умолчанию он равен = 1, то есть с первого, а значит целая строка.
На пример, в примере функция находит первый дефис в строке «PWR-16-Small». В результате своего вычисления она по умолчанию возвращает число 4. Так как первый дефис в и сходной строке находится на четвертой позиции.
Динамические формулы с использованием функции НАЙТИ
Но если мы воспользуемся третьим необязательным аргументом и укажем в нем число 5. То есть просматривать первую не целиком, а начиная после первого дефиса, четвертого символа. Тогда функция будет возвращать нам порядковую позицию второго «-», то есть число – 7.
Текстовую функцию НАЙТИ чаще всего используют как вспомогательную указав ее в качестве аргумента для других текстовых функций. Например, если мы используем ее как второй аргумент для функции ПСТР, то мы получим возможность вырезать фрагмент текста разной длины, автоматически определяя необходимую позицию в строке как маркер для отделения ее части.
Если использовать формулу, указанную в примере мы вырезаем 2 числовых символа, которые находиться после первого дефиса из каждой строки кодов товаров. Обратите внимание на сложение в формуле +1, благодаря которому мы смещаем фокус функции на один символ, чтобы определить его позицию на символы после дефиса (минуя его).
Как уже упоминалось выше по умолчанию функция НАЙТИ возвращает позицию первого найденного искомого символа, который нашелся в исходной просматриваемой текстовой строке. Когда нам нужно найти второй такой же символ и узнать его позицию в строке, можем воспользоваться опциональным третьим аргументом функции. В этом аргументе можно определить позицию символа исходной строки, с которого следует начинать поиск.
Например, следующая формула возвращает позицию второго дефиса, так как в ее третьем аргументе указано число позиции первого дефиса. А значит поиски будут вестись не по всей строке, а только по ее части начиная от первого дефиса.
Таким образом мы создали динамическую формулу, которая автоматически определяет где (на какой позиции) в строке находится первый и второй дефисы. А дальше их можно использовать как аргументы в других функциях.
Пример из практического опыта офисного сотрудника. Необходимо получить часть кода товаров, которая начинается от второго дефиса. Для этого создаем динамическую формулу:
Здесь мы использовали автоматический поиск первого дефиса. Номер позиции послужил третьим необязательным опциональным аргументом функции НАЙТИ для автоматического поиска каждого второго дефиса в каждом коде товара. Далее используя функцию ДЛСТР, мы определяем длину исходной строки и вычитаем от нее число позиции второго символа. Другими словами, от длины кода вычитаем количество символов до второго дефиса (включительно с ним, о чем свидетельствует сложение +1). Таким образом мы динамически определяем второй аргумент для функции ПРАВСИМВ чтобы вырезать разной величины фрагмент текста из строк. К том уже все строки с разной длиной, да еще и в разном месте находится второй дефис. Но умная формула справилась полностью в автоматическом режиме.