Примеры использования функции НАЙТИ в Excel формулах

Функции ЛЕВСИМВ, ПРАВСИМВ и ПСТР отлично используются для разделения строк на слова либо фрагменты текстов, но при условии, что для вас заблаговременно известны позиции знаков с которых будет разделение. Что все-таки созодать если заблаговременно для вас не понятно где в текстовой строке находится тот знак, начиная с которого необходимо вырезать фрагмент текста?

Пример формулы НАЙТИ, ДЛСТР и ПРАВСИМВ в Excel

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

• PTR-422
• CORPO-6755
• SVCCALL-56532

Функция ЛЕВСИМВ нам сходу не подступает, потому что нужно получить последнюю часть всякого кода. Функция ПРАВСИМВ так же не совладать с данной задачей, ведь в ее аргументах следует указать четкое количество знаков возвращаемого текста из всех различных кодов разной длины. Если в аргументе будет обозначено фиксированное числовое значение, то для неких кодов сработает, а для большинства будет очень много либо не много количество знаков, возвращаемых функцией ПРАВСИМВ.

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

Для реализации данной задачки следует употреблять формулу с композицией функций ПРАВСИМВ, ДЛСТР и НАЙТИ:

Благодаря функции НАЙТИ можно автоматом определять позицию в текстовой строке для обозначенного знака в ее аргументах. А опосля употреблять номер позиции в последующих операциях, к примеру, при автоматической генерации значений для второго аргумента функции ПРАВСИМВ. Реализуется генерация методом определения нужного числа при вычитании от длины строчки, которую возвращает функция ДЛСТР номера позиции знака – «-».

Пример использования НАЙТИ и ПСТР в формуле Excel

В последующем примере, изображенном на рисунке, функция НАЙТИ употребляется в формуле вкупе с функцией ПСТР для подборки средних чисел меж дефисами из кода продуктов прайс-листа.

Как видно на рисунке формула поначалу отыскивает номер позиции для знака с помощь функции НАЙТИ. А опосля отысканный номер позиции употребляет в собственных аргументах функция ПСТР.

Функция НАЙТИ просит заполнить минимум 2 из 3-х аргументов:

1. Искомый_текст – тут нужно указать текст, который следует найти и получить его порядковый номер (позицию) в начальной текстовой строке.
2. Просматриваемый_текст – здесь указываем ссылку на ячейку с начальной строчкой, которая содержит разыскиваемый знак либо текст.
3. Нач_позиция – это не неотклонимый аргумент. Тут можно указать номер позиции знака в строке, с которого следует начинать поиск. Если строчка содержит наиболее 1-го отысканного искомого знака, то при помощи данного необязательного аргумента можно указать номер знака с которого будет просматриваться остальная часть строчки. Если он не указан в этом аргументе, то по дефлоту он равен = 1, другими словами с первого, а означает целая строчка.

На пример, в примере функция находит 1-ый дефис в строке «PWR-16-Small». В итоге собственного вычисления она по дефлоту возвращает число 4. Потому что 1-ый дефис в и схожей строке находится на четвертой позиции.

Динамические формулы с внедрением функции НАЙТИ

Но если мы воспользуемся третьим необязательным аргументом и укажем в нем число 5. Другими словами просматривать первую не полностью, а начиная опосля первого дефиса, 4-ого знака. Тогда функция будет возвращать нам порядковую позицию второго «-», другими словами число – 7.

Текстовую функцию НАЙТИ почаще всего употребляют как вспомогательную указав ее в качестве аргумента для остальных текстовых функций. К примеру, если мы используем ее как 2-ой аргумент для функции ПСТР, то мы получим возможность вырезать фрагмент текста разной длины, автоматом определяя нужную позицию в строке как маркер для отделения ее части.

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

Как уже упоминалось выше по дефлоту функция НАЙТИ возвращает позицию первого отысканного искомого знака, который нашелся в начальной просматриваемой текстовой строке. Когда нам необходимо найти 2-ой таковой же знак и выяснить его позицию в строке, можем пользоваться опциональным третьим аргументом функции. В этом аргументе можно найти позицию знака начальной строчки, с которого следует начинать поиск.

К примеру, последующая формула возвращает позицию второго дефиса, потому что в ее 3-ем аргументе обозначено число позиции первого дефиса. А означает поиски будут вестись не по всей строке, а лишь по ее части начиная от первого дефиса.

Таковым образом мы сделали динамическую формулу, которая автоматом описывает где (на какой позиции) в строке находится 1-ый и 2-ой дефисы. А далее их можно употреблять как аргументы в остальных функциях.

Пример из практического опыта офисного сотрудника. Нужно получить часть кода продуктов, которая начинается от второго дефиса. Для этого создаем динамическую формулу:

Тут мы употребляли автоматический поиск первого дефиса. Номер позиции послужил третьим необязательным опциональным аргументом функции НАЙТИ для автоматического поиска всякого второго дефиса в любом коде продукта. Дальше используя функцию ДЛСТР, мы определяем длину начальной строчки и вычитаем от нее число позиции второго знака. Иными словами, от длины кода вычитаем количество знаков до второго дефиса (включительно с ним, о чем свидетельствует сложение +1). Таковым образом мы динамически определяем 2-ой аргумент для функции ПРАВСИМВ чтоб вырезать разной величины фрагмент текста из строк. К том уже все строчки с разной длиной, да к тому же в разном месте находится 2-ой дефис. Но умная формула совладала стопроцентно в автоматическом режиме.

Поиск в таблице Excel — обыкновенные методы

Основное предназначение офисной программки Excel – воплощение расчётов. Документ данной нам программки (Книжка) может содержать много листов с длинноватыми таблицами, заполненными числами, текстом либо формулами. Автоматический резвый поиск дозволяет найти в их нужные ячейки.

Обычной поиск

Чтоб произвести поиск значения в таблице Excel, нужно на вкладке «Основная» открыть выпадающий перечень инструмента «Найти и поменять» и щёлкнуть пункт «Найти». Этот же эффект можно получить, используя сочетание кнопок Ctrl + F.

В простом случае в показавшемся окне «Найти и поменять» нужно ввести разыскиваемое значение и щёлкнуть «Найти всё».

Как видно, в нижней части диалогового окна возникли поисковые результаты. Отысканные значения подчёркнуты красноватым в таблице. Если заместо «Найти все» щёлкнуть «Найти дальше», то поначалу будет произведён поиск первой ячейки с сиим значением, а при повторном щелчке – 2-ой.

Аналогично делается поиск текста. В этом случае в строке поиска набирается разыскиваемый текст.

Если данные либо текст ищется не во всей экселевской таблице, то область поиска за ранее обязана быть выделена.

Расширенный поиск

Представим, что требуется найти все значения в спектре от 3000 до 3999. В этом случае в строке поиска следует набрать 3. Подстановочный символ «?» подменяет собой хоть какой иной.

Анализируя результаты произведённого поиска, можно отметить, что, вместе с правильными 9 плодами, программка также выдала нежданные, подчёркнутые красноватым. Они соединены с наличием в ячейке либо формуле числа 3.

Можно удовольствоваться большинством приобретенных результатов, игнорируя некорректные. Но функция поиска в эксель 2010 способна работать еще поточнее. Для этого предназначен инструмент «Характеристики» в диалоговом окне.

Щёлкнув «Характеристики», юзер получает возможность производить расширенный поиск. До этого всего, обратим внимание на пункт «Область поиска», в каком по дефлоту выставлено значение «Формулы».

Это значит, что поиск выполнялся, в том числе и в тех ячейках, где находится не значение, а формула. Наличие в их числа 3 отдало три некорректных результата. Если в качестве области поиска избрать «Значения», то будет выполняться лишь поиск данных и некорректные результаты, связанные с ячейками формул, пропадут.

Для того чтоб избавиться от единственного оставшегося неверного результата на первой строке, в окне расширенного поиска необходимо избрать пункт «Ячейка полностью». Опосля этого итог поиска становимся четким на 100%.

Таковой итог можно было бы обеспечить, сходу выбрав пункт «Ячейка полностью» (даже оставив в «Области поиска» значение «Формулы»).

Сейчас обратимся к пт «Находить».

Если заместо установленного по дефлоту «На листе» избрать значение «В книжке», то нет необходимости находиться на листе разыскиваемых ячеек. На снимке экрана видно, что юзер инициировал поиск, находясь на пустом листе 2.

Последующий пункт окна расширенного поиска – «Просматривать», имеющий два значения. По дефлоту установлено «по строчкам», что значит последовательность сканирования ячеек по строчкам. Выбор другого значения – «по столбцам», поменяет лишь направление поиска и последовательность выдачи результатов.

При поиске в документах Microsoft Excel, можно употреблять и иной подстановочный символ – «*». Если рассмотренный «?» означал хоть какой знак, то «*» подменяет собой не один, а хоть какое количество знаков. Ниже представлен снимок экрана поиска по слову Louisiana.

Время от времени при поиске нужно учесть регистр знаков. Если слово louisiana будет написано с малеханькой буковкы, то поисковые результаты не поменяются. Но если в окне расширенного поиска избрать «Учесть регистр», то поиск окажется безуспешным. Программка станет считать слова Louisiana и louisiana различными, и, естественно, не найдёт 1-ое из их.

Разновидности поиска

Поиск совпадений

Время от времени бывает нужно найти в таблице повторяющиеся значения. Чтоб произвести поиск совпадений, поначалу необходимо выделить спектр поиска. Потом, на той же вкладке «Основная» в группе «Стили», открыть инструмент «Условное форматирование». Дальше поочередно избрать пункты «Правила выделения ячеек» и «Повторяющиеся значения».

Итог представлен на снимке экрана ниже.

По мере необходимости юзер может поменять цвет зрительного отображения совпавших ячеек.

Фильтрация

Иная разновидность поиска – фильтрация. Представим, что юзер желает в столбце B найти числовые значения в спектре от 3000 до 4000.

1. Выделить 1-ый столбец с заголовком.
2. На той же вкладке «Основная» в разделе «Редактирование» открыть инструмент «Сортировка и фильтр», и щёлкнуть пункт «Фильтр».
3. В верхней строке столбца B возникает треугольник – условный символ перечня. Опосля его открытия в перечне «Числовые фильтры» щёлкнуть пункт «меж».
4. В окне «Пользовательский автофильтр» следует ввести изначальное и конечное значение плюс OK.

Как видно, отображаться стали лишь строчки, удовлетворяющие введённому условию. Все другие оказались временно сокрытыми. Для возврата к исходному состоянию следует повторить шаг 2.

Разные варианты поиска были рассмотрены на примере Excel 2010. Как создать поиск в эксель остальных версий? Разница в переходе к фильтрации есть в версии 2003. В меню «Данные» следует поочередно избрать команды «Фильтр», «Автофильтр», «Условие» и «Пользовательский автофильтр».

Поиск решения в excel

Microsoft Office Excel является офисной программкой с массивным математическим аппаратом и огромным набором функций. Благодаря наличию специфичных инструментов, excel дозволяет решать сложные задачки при малых издержек времени. Сейчас познакомимся с функцией поиск решения в excel и разглядим несколько примеров.

Размещение

Немногие знают, где находится данная функция. Во вкладке Данные на основное панели можно найти одноименную клавишу. Необходимо отметить, что поиск решения является надстройкой excel и не у всех юзеров она включена. Чтоб активировать инструмент, необходимо создать последующее:

1. Нажимаете клавишу Office в верхнем левом углу экрана и перебегайте к Характеристикам.

2. Ищете строчку Надстройки и в правой части диалогового окна нажимаете клавишу Перейти. Непременно проверьте, чтоб левее была надпись Надстройки Excel.

3. Ставите галочку напротив Поиск решения и нажимаете ОК.

4.Программка выдает предупреждение о отсутствии компонента и дает его установить. Соглашаетесь.

5. Дожидаетесь окончания установки.

6. Если все изготовлено верно, то во вкладке Данные покажется блок Анализ с клавишей Поиск решения.

Структура

Разглядим подробнее главные аргументы и механизм работы функции. Основное окно содержит последующие поля:

1. 1. Пространство ввода мотивированной ячейки, в отношении которой нужно найти решение, и чему обязано быть равно.
2. 2. Строчка аргументов, которые необходимо изменять для заслуги поставленной цели.

На заметку! Excel может сам избрать ячейки, которые будут изменяться. Для этого нажимаете клавишу Представить.

1. 3. Блок прибавления ограничений.
2. 4. Клавиша характеристик, при нажатии которой, возникает новое окно, где можно настроить количество повторений, время выполнения, погрешность и отклонение, также обозначить доп настойки.

Внедрение

Чтоб нагляднее показать работу инструмента поиск решения, разглядим примеры решения задач, с которыми может столкнуться любой юзер. Допустим, в наличии имеется некоторая сумма валютных средств, которую человек готов положить на депозит в банк под фиксированный процент и раз в год пополнять счет на первоначальную сумму. При всем этом есть определенная цель, которую вкладчик желает добиться, по истечении 5 лет.

Перенесем эти сведения на рабочий лист excel.

В отдельном столбце запишем все года по порядку, а в примыкающем поле пропишем формулу и воспользуемся маркером автозаполнения. Функция, которая поможет получить начальные данные, именуется БС и состоит она из последующих главных аргументов:

1. 1. Процентная ставка.
2. 2. Период (кпер).
3. 3. Сумма платежа (плт).

Для первого года формула будет смотреться последующим образом:

Принципиально! Чтоб расчеты были правильными, нужно зафиксировать значение суммы и процента, нажав кнопку F4 либо добавив значки бакса.

Видите ли, число отрицательное – это особой функции БС. Чтоб этого избежать, ячейку с суммой средств необходимо создать отрицательной. Тогда итоговые результаты будут отображаться корректно.

Воспользуемся автозаполнением и получим сумму средств опосля 5 лет нахождения на депозите под 4 процента годичных с каждогодним пополнением.

Приобретенная цифра не удовлетворяет условию в 12000 опосля пятилетнего периода. Сейчас нужно пользоваться инвентарем Поиск решения. При всем этом изменяемыми параметрами будет процент и начальная сумма. Заполняете диалоговое окно построчно.

Нажимаете клавишу Выполнить и получаете решение задачки с условием заслуги поставленной цели за 5 лет.

Видите ли, поменялась лишь процентная ставка, хотя изменяемыми величинами были два параметра. Чтоб это поправить, в настройках нужно поставить галочки напротив строки Автоматическое масштабирование.

Повторяете решение с новейшей конфигурацией и получаете последующие данные:

Видите ли, чтоб добиться отметки в 12000$ через 5 лет, нужно найти депозит под 4,03 процента годичных и раз в год пополнять его на сумму 2214 бакса 01 цент.

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