Как найти вариант в excel

Хитрости »

14 Февраль 2017              25759 просмотров

---

Как быстро подобрать оптимальный вариант решения

Практически в любой компании в определенные периоды могут “высвобождаться” из оборота временно свободные денежные средства(ВСДС). Оставлять эти деньги просто так на счетах компании весьма нецелесообразно. Деньги должны делать деньги. Конечно, вложение денег в банковские депозиты может показаться не самым лучшим вариантом для инвестиций, но все же это хоть какой-то доход, который если и не будет колоссальным, то хотя бы частично покроет инфляционные потери.
И самое сложное это выбрать банк, программу депозита и срок для вложений таким образом, чтобы получить максимальную выгоду. Сделать это поможет один из самых мощных, но в тоже время малоиспользуемых инструментов Excel – надстройка Поиск решения(Solver).

Чтобы пошагово выполнять дальнейшие действия, описанные в статье рекомендую сразу скачать файл с моделью для Поиска решения:

  Модель_расчета_ВСДС.xls (44,5 KiB, 1 470 скачиваний)

Надстройка Поиск решения хоть и устанавливается автоматически вместе с Excel(начиная с версий 2007 и выше), но по умолчанию отключена. Чтобы включить надстройку необходимо перейти в Файл(File) –Параметры(Options). В появившемся диалоговом окне выбрать слева пункт Надстройки(Add-ins). Далее справа внизу в выпадающем списке Управление выбрать –Надстройки Excel(Excel Add-ins) и нажать Перейти(Go):

В окне Надстройки(Add-ins) устанавливаем галочку напротив пункта Поиск решения(Solver), жмем ОК.

Поиск решения теперь будет доступен с вкладки Данные(Data) -группа Анализ(Analize):

Исходные данные
Для начала нам потребуется определить сумму временно свободных денежных средств и сроки, на которые мы хотим эти средства разместить в банке. Далее конечно же потребуется выбрать несколько банков (или конкретные предложения отдельного банка) с различными процентами годовых за размещение ДС. Но так же придется учесть и реалии: не стоит все средства размещать исключительно в одном банке и поэтому надо для банков/предложений определить лимит ДС, который нельзя превышать.
Т.е. наши исходные данные выглядят примерно так:

• период размещения ДС на депозите(скажем 14 дней, 28 дней, 62 дня и 91 день)
• сумма ДС, доступная на каждый период размещения
• процент за размещение ДС на каждый период для каждого типа размещения(срочный депозит, до востребования, овернайт и т.д.) или банка
• лимит на размещение средств по каждому типу депозита или для каждого банка(лимит определяется самой компанией)

Как работает Поиск решения
Поиск решения хорош тем, что он может быть применен практически к любой задаче. Что он делает? Он на основании заданных условий и ограничений перебирает все возможные варианты, которые подходят под условия и не выходят за рамки заданных ограничений, если они есть. И из всех подобранных вариантов выбирает самый оптимальный. В нашем случае будем подбирать наиболее выгодный для нас вариант размещения ВСДС.
А что считать наиболее выгодным? Конечно то, что принесет наибольший доход. При этом наша цель не просто выбрать депозит с самым большим процентом (это было бы слишком просто и для этого не нужен Поиск решения), а может даже совместить несколько вариантов размещения ВСДС на разных депозитах с разными ставками и разными периодами. Ведь для различных сумм или сроков и ставки могут быть разными.
И теперь останется определить какие у нас могут быть ограничения. По сути их два основных:

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

Исходные данные определены, ограничения тоже. Осталось записать все это в удобную таблицу(приложена к статье), в которой при необходимости сможем изменять эти исходные данные и ограничения:

Для большей наглядности блоки таблицы разделены цветами:

• Синий и голубой – заполняемые вручную данные: суммы ВСДС, сроки ВСДС, лимиты размещения ВСДС в банках, процент по каждому банку/размещению
• Зеленый – расчетные поля. Их не надо изменять вручную
• Красный – поля, заполняемые автоматически Поиском решения или формулами. Их не надо изменять вручную
• Так же серый шрифт – это “служебные” ячейки, которые не влияют на расчеты непосредственно в таблице, но которые потребуются нам для корректной работы Поиска решения для учета ограничений

После заполнения таблицы исходными данными можно приступать к определению максимального выгодного вложения ВСДС.

Переходим на вкладку Данные(Data) -группа Анализ(Analize) –Поиск решения(Solver). В появившемся окне указываем следующие данные:

• Оптимизировать целевую функцию(Set Objective) – указываем ячейку H18, в которой у нас подводится сумма общего дохода от вложений
• До(To) – выбираем Максимум(Max), т.к. нам нужен максимально возможный доход
• Изменяя ячейки переменных(By Changing Variable Cells) – указываем H9:K14. В эти ячейки Поиск решения будет подставлять суммы к размещению и вычисляя от этого возможный доход. Собственно, заполненные здесь данные нам и нужны в итоге
• В соответствии с ограничениями(Subject to the Constraints) – здесь мы сами добавляем ограничения, которые необходимо учитывать при расчете дохода. Нам потребуется добавить два ограничения(на скрине выше они уже добавлены, но в любом случае необходимо знать как их создавать). Нажимаем справа кнопку Добавить(Add), появится окно добавления ограничения:
  
  В данном случае я хочу добавить ограничение, что суммы в ячейках с лимитом размещения в банке должны быть больше или равны общей сумме размещенных ВСДС. Эта сумма у нас подводится в ячейках L9:L14. Таким образом нам в левой части надо выбрать ячейки с суммами заданных лимитов (C9:C14), а в правой суммы всех вложений – L9:L14. В выпадающем списке между двумя этими полями можно выбрать тип сравнения. В нашем случае ячейки слева (лимиты ДС) должны быть больше или равны(>=) общей сумме вложений по данному типу – ячейки справа.
  Аналогично добавляем второе ограничение – суммы доступных ВСДС не должны превышать суммы, которые Поиск решения предложит разместить. Доступные суммы у нас указаны в ячейках D7:G7, а общие суммы предложенных к размещению Поиском решения – в ячейках H16:K16(в этих ячейках записаны формулы, суммирующие данные сумм по каждому периоду в ячейках H9:K14)
• Так же лучше установить галочку Сделать переменные без ограничений неотрицательными(Make Unconstrained Variables Non-Negative), чтобы Поиск решения не стал подбирать отрицательные суммы для выполнения условий
  В рассматриваемой задаче это маловероятно, но при использовании Поиска решения в других задачах этому пункту советую уделять особое внимание, т.к. иногда оптимальным решением для достижения заданного результата с точки зрения Поиска решения будет добавление отрицательного значения среди заполняемых ячеек

Нажимаем Найти решение(Solve). Если все условия заданы правильно и ограничения выполнимы, то Поиск решения заполнит ячейки суммами и выдаст сообщение о том, что решение найдено и предложит сохранить найденные значения или восстановить предыдущие. В нашем случае надо оставить пункт Сохранить найденное решение(Keep Solver Solution) и нажать Ок.

После этого мы сможем более детально изучить предложенное решение:

При необходимости изменить какие-то исходные данные и запустить поиск решения заново. Все ранее указанные ограничения и условия сохраняются и создавать их заново не придется.
В приложенном к статье файле все ограничения и условия уже созданы и для их просмотра и правки достаточно просто запустить Поиск решения
Осталось понять Как работает вся эта таблица в Поиске решения
В блоке Доход в зависимости от срока размещения, руб(M9:P14) записаны формулы, которые определяют сумму дохода в зависимости от вложенной суммы и срока размещения. При этом рассчитываются они из сумм, записанных в ячейках красного блока (Суммы к размещению на соответствующие сроки – H9:K14) и от сроков, указанных в исходных данных(D8:G8). В ячейке Итого доходность(H18) подводится сумма этих ячеек. Т.е. мы определяем общий доход от вложений. Все, что остается делать Поиску решения – это изменять значения ячеек Суммы к размещению на соответствующие сроки (H9:K14) до тех пор, пока сумма всех доходов (Итого доходность – H18) не достигнет максимального значения из всех возможных вариантов при всех существующих ограничениях. Суммы в ячейках H9:K14 и будут являться оптимальным решением.
При этом если мы захотим исключить какой-либо банк/тип депозита из просчета, достаточно будет установить в ячейках C7:C14 для этого типа значение 0. Тогда он не будет учитываться для размещения Поиском решения и не надо будет удалять/добавлять строки и переопределять ограничения.

Проверка результатов
Надстройка Поиск решения реализована при помощи весьма сложных алгоритмов и, пожалуй, является самой непредсказуемой надстройкой в Excel. Поэтому рекомендуется тщательно перепроверять результаты вручную, прежде чем полностью на них положиться. Сверяйте полученные результаты, чтобы убедиться, что ограничения не нарушены и главное, что результат отвечает ожиданиям (хотя бы примерно).
Так же следует учитывать, что надстройка может при одинаковых условиях и исходных данных выдавать различные результаты при многократном запуске. Это так же обусловлено сложностью заложенных алгоритмов. Т.е. теоретически, запустив Поиск решения пять раз есть вероятность, что все пять раз решение будет разным. Хотя в большинстве случаев я наблюдал ситуации, когда при одинаковых исходных данных решение было одинаковым.

Скачать пример:

  Модель_расчета_ВСДС.xls (44,5 KiB, 1 470 скачиваний)

---

Подбор под сумму через Поиск решения

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

Скачать файл из видео:

  Подбор под сумму.xls (60,5 KiB, 879 скачиваний)

Так же см.:
План-фактный анализ в Excel при помощи Power Query
Автообновляемая сводная таблица

---

Статья помогла? Поделись ссылкой с друзьями!

    Видеоуроки

---

Поиск по меткам



Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика

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

Сделайте следующее:

1. Для начала с одного из следующих начните:

   • Чтобы найти конкретные ячейки на всем ячеек, щелкните любую из них.

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

     Совет: Чтобы отменить выделение ячеек, щелкните любую ячейку на листе.

2. На вкладке Главная нажмите кнопку & выберите > Перейти (в группе Редактирование).

   

   Сочетания клавиш: Нажмите CTRL+G.

3. Нажмите кнопку Дополнительный.

4. В диалоговом окне Перейти к специальным выберите один из следующих параметров:

Команда	Чтобы выделить
Примечания	Ячейки с прикомментами.
Константы	Ячейки, содержащие константы.
Формулы	Ячейки, содержащие формулы. Примечание: Тип формулы определяется флажками под формулами.
Пробелы	Пустые ячейки.
Текущая область	Список текущая область, например весь список.
Текущий массив	Если массив массив содержится активная ячейка массиве, это может быть целая активная ячейка.
Объекты	Графические объекты, в том числе диаграммы и кнопки, на месте и в текстовых полях.
Различия между строками	Все ячейки, отличающиеся от активной ячейки в выбранной строке. В области выделения всегда есть одна активная ячейка , будь то диапазон, строка или столбец. Нажимая клавишу ВВОД или TAB, можно изменить расположение активной ячейки, которая по умолчанию является первой ячейкой в строке. Если выбрано несколько строк, для каждой отдельной строки этого выделения будет сравнено сравнение, а ячейка, используемая для сравнения для каждой дополнительной строки, будет расположена в том же столбце, что и активная ячейка.
Различия между столбцами	Все ячейки, отличающиеся от активной ячейки в выбранном столбце. В области выделения всегда есть одна активная ячейка, будь то диапазон, строка или столбец. Нажимая клавишу ВВОД или TAB, можно изменить расположение активной ячейки , которая по умолчанию является первой ячейкой в столбце. Если выбрано несколько столбцов, сравнение делается для каждого отдельного столбца в этом столбце. Ячейка, используемая для сравнения для каждого дополнительного столбца, находится в той же строке, что и активная ячейка.
Прецеденты	Ячейки, на которые ссылается формула в активной ячейке. В области Зависимыесделайте следующее: Чтобы найти только ячейки, на которые ссылается формула, выберите прямая. Щелкните Все уровни, чтобы найти все ячейки, на которые прямо или косвенно ссылается выделение.
Иждивенцев	Ячейки с формулами, которые ссылаются на активную ячейку. Выполните одно из указанных ниже действий. Чтобы найти только ячейки с формулами, которые ссылаются непосредственно на активную ячейку, выберите прямая. Щелкните Все уровни, чтобы найти все ячейки, которые прямо или косвенно ссылаются на активную ячейку.
Последняя ячейка	Последняя ячейка на этом сайте с данными или форматированием.
Только видимые ячейки	Только ячейки, видимые в диапазоне, который пересекает скрытые строки или столбцы.
Условные форматы	Применяются только ячейки с условным форматированием. В области Проверка данныхсделайте следующее: Нажмите кнопку Все, чтобы найти все ячейки с примененными условными форматами. Нажмите кнопку Те же, чтобы найти ячейки с тем же условным форматированием, что и в выбранной ячейке.
Проверка данных	Только ячейки с примененными правилами проверки данных. Выполните одно из указанных ниже действий. Нажмите кнопку Все, чтобы найти все ячейки с примененной проверкой данных. Нажмите кнопку Те же, чтобы найти ячейки с той же проверкой данных, что и в выбранной ячейке.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Поиск в таблице Excel

Также статьи о работе с таблицами в Экселе:

Среди тысяч строк и десятков столбцов данных вручную в таблице Эксель найти что-то практически невозможно. Единственный вариант, это воспользоваться какой-то функцией поиска, и далее мы рассмотрим, как осуществляется поиск в таблице Excel.

Для осуществления поиска данных в таблице Excel необходимо использовать пункт меню «Найти и выделить» на вкладке «Главная», в котором нужно выбирать вариант «Найти» или воспользоваться для вызова комбинацией клавиш «Ctrl + F».

Для примера попробуем найти необходимое число среди данных нашей таблицы, так как именно при поиске чисел необходимо учитывать некоторые тонкости поиска. Будем искать в таблице Excel число «10».

После выбора необходимого пункта меню в появившемся окошке поиска вводим искомое значение. У нас два варианта поиска значений в таблице Эксель, это найти сразу все совпадения нажав кнопку «Найти все» или сразу же просматривать каждую найденную ячейку, нажимая каждый раз кнопку «Найти далее». При использовании кнопки «Найти далее» следует также учитывать текущее расположение активной ячейки, так как поиск начнется именно с этой позиции.

Попробуем найти сразу все значения, при этом все найденное будет перечислено в окошке под настройкой поиска. Если оставить все настройки по умолчанию, то результат поиска будет не совсем такой, как мы ожидали.

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

При поиске слов в таблице Excel следует также учитывать все эти тонкости и к примеру, можно учитывать даже регистр букв.

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

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

Microsoft Excel

трюки • приёмы • решения

Как правильно искать в таблицах Excel

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

Выполните команду Главная ► Редактирование ► Найти и выделить ► Найти (или нажмите Ctrl+F), чтобы открыть диалоговое окно Найти и заменить. Если вам нужно заменить данные, то выберите команду Главная ► Редактирование ► Найти и выделить ► Заменить (или нажмите Ctrl+H). От того, какую именно команду вы выполните, зависит, на какой из двух вкладок откроется диалоговое окно.

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

Рис. 21.1. Вкладка Найти диалогового окна Найти и заменить

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

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

При поиске с помощью диалогового окна Найти и заменить можно использовать два подстановочных знака:

• ? — соответствует любому символу;
• * — соответствует любому количеству символов.

Кроме того, данные подстановочные символы можно также применять при поиске числовых значений. Например, если в строке поиска задать 3*, то в результате отобразятся все ячейки, которые содержат значение, начинающееся с 3, а если вы введете 1?9, то получите все трехзначные записи, которые начинаются с 1 и заканчиваются 9.

Для поиска вопросительного знака или звездочки поставьте перед ними символ тильды (

).
Например, следующая строка поиска находит текст *NONE*: -*N0NE

*
Чтобы найти символ тильды, поставьте в строке поиска две тильды.

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

• Флажок Учитывать регистр — установите его, чтобы регистр искомого текста совпадал с регистром заданного текста. Например, если вы зададите в поиске слово иван и установите указанный флажок, то слово Иван в результатах поиска не отобразится.
• Флажок Ячейка целиком — установите его, чтобы найти ячейку, которая содержит в точности тот текст, который указан в строке поиска. Например, набрав в строке поиска слово Excel и установив указанный флажок, вы не найдете ячейку, содержащую словосочетание Microsoft Excel.
• Раскрывающийся список Область поиска — список содержит три пункта: значения, формулы и примечания. Например, если в строке поиска вы зададите число 900 и в раскрывающемся списке Область поиска выберете пункт значения, то в результатах поиска вы не увидите ячейку, содержащую значение 900, если оно получено при использовании формулы.

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

Кроме того, учтите, что с помощью окна Найти и заменить нельзя найти отформатированные числовые значения. Например, если в строку поиска вы введете $5*, то значение, к которому применено денежное форматирование и которое выглядит как $54.00, не будет найдено.

Работа с датами может оказаться непростой, поскольку Excel поддерживает очень много форматов дат. Если вы ищете дату, к которой применено форматирование по умолчанию, Excel находит даты, даже если они отформатированы различными способами. Например, если ваша система использует формат даты m/d/y, строка поиска 10/*/2010 находит все даты в октябре 2010 года, независимо от того, как они отформатированы.

Используйте пустое поле Заменить на, чтобы быстро удалить какую-нибудь информацию на рабочем листе. Например, введите — * в поле Найти и оставьте поле Заменить на пустым. Затем нажмите кнопку Заменить все, чтобы Excel нашел и убрал все звездочки на листе.

Поиск слов в Excel

В Microsoft Excel часто работают с большими объёмами информации. В нём создаются огромные таблицы на тысячи строк, столбцов и позиций. Отыскать какие-то конкретные данные в таком массиве бывает трудно. А иногда — вообще невозможно. Эту задачу можно упростить. Разберитесь, как в Экселе найти нужное слово. Так вам легче будет ориентироваться в документе. И вы сможете быстро перейти к искомой информации.

В редакторе таблиц существует несколько способов поиска

Поиск ячеек

Чтобы отобразились адреса всех ячеек, в которых есть то, что вы ищите, сделайте следующее:

1. Если у вас Office 2010, перейдите в Меню — Правка — Найти.
2. Откроется окно с полем для ввода. Напишите в нём фразу для поиска.
3. В Excel 2007 эта кнопка есть в меню «Главная» на панели «Редактирование». Она находится справа.
4. Аналогичного результата во всех версиях можно добиться, если нажать клавиши Ctrl+F.
5. В поле напечатайте слово, фразу или цифры, которые надо отыскать.
6. Нажмите «Найти всё», чтобы запустить поиск по целому документу. Если кликните «Далее», программа будет по одной выделять клетки, которые находятся ниже курсора-ячейки Excel.
7. Подождите, пока процесс закончится. Чем больше документ, тем дольше система будет искать.
8. Появится список с результатами: именами и адресами клеток, в которых есть совпадения с заданной фразой, и текстом, который в них написан.
9. При клике на каждую строчку будет выделяться соответствующая ячейка.
10. Для удобства можете «растянуть» окно. Так в нём будет видно больше строчек.
11. Чтобы отсортировать данные, кликайте на названия столбцов над результатами поиска. Если нажмёте на «Лист», строчки выстроятся по алфавиту в зависимости от названия листа, если выберите «Значения» — встанут по значению.
12. Эти столбцы также «растягиваются».

Параметры поиска

Можете задать свои условия. Например, запустить поиск по нескольким знакам. Вот как в Экселе найти слово, которое вы не помните целиком:

1. Введите только часть надписи. Можно хоть одну букву — будут выделены все места, в которых она есть.
2. Используйте символы * (звёздочка) и ? (вопросительный знак). Они замещают пропущенные знаки.
3. Вопрос обозначает одну отсутствующую позицию. Если вы напишите, к примеру, «П. », отобразятся ячейки, в которых есть слово из четырёх символов, начинающееся на «П»: «Плуг», «Поле», «Пара» и так далее.
4. Звезда (*) замещает любое количество знаков. Чтобы отыскать все значения, в которых содержится корень «раст», начните поиск по ключу «*раст*».

Также вы можете зайти в настройки:

1. В окне «Найти» нажмите «Параметры».
2. В разделах «Просматривать» и «Область поиска», укажите, где и по каким критериям надо искать совпадения. Можно выбрать формулы, примечания или значения.
3. Чтобы система различала строчные и прописные буквы, поставьте галочку в «Учитывать регистр».
4. Если вы отметите пункт «Ячейка целиком», в результатах появятся клетки, в которых есть только заданная поисковая фраза и ничего больше.

Параметры формата ячеек

Чтобы отыскать значения с определённой заливкой или начертанием, используйте настройки. Вот как найти в Excel слово, если оно имеет отличный от остального текста вид:

1. В окне поиска нажмите «Параметры» и кликните на кнопку «Формат». Откроется меню с несколькими вкладками.
2. Можете указать определённый шрифт, вид рамки, цвет фона, формат данных. Система будет просматривать места, которые подходят к заданным критериям.
3. Чтобы взять информацию из текущей клетки (выделенной в этот момент), нажмите «Использовать формат этой ячейки». Тогда программа отыщет все значения, у которых тот же размер и вид символов, тот же цвет, те же границы и тому подобное.

Поиск нескольких слов

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

Чтобы в Экселе найти не одно слово, а сразу несколько, сделайте следующее:

1. Напишите их в строке поиска.
2. Поставьте между ними звёздочки. Получится «*Текст* *Текст2* *Текст3*». Так отыщутся все значения, содержащие указанные надписи. Вне зависимости от того, есть ли между ними какие-то символы или нет.
3. Этим способом можно задать ключ даже с отдельными буквами.

Вот как искать в Экселе, используя фильтр:

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

Этот способ не подойдёт, если вы не знаете, в каком ряду нужное вам значение.

Чтобы найти в Excel какую-то фразу или число используйте встроенные возможности интерфейса. Можно выбрать дополнительные параметры поиска и включить фильтр.

Поиск в программе Microsoft Excel

В документах Microsoft Excel, которые состоят из большого количества полей, часто требуется найти определенные данные, наименование строки, и т.д. Очень неудобно, когда приходится просматривать огромное количество строк, чтобы найти нужное слово или выражение. Сэкономить время и нервы поможет встроенный поиск Microsoft Excel. Давайте разберемся, как он работает, и как им пользоваться.

Поисковая функция в Excel

Поисковая функция в программе Microsoft Excel предлагает возможность найти нужные текстовые или числовые значения через окно «Найти и заменить». Кроме того, в приложении имеется возможность расширенного поиска данных.

Способ 1: простой поиск

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

1. Находясь во вкладке «Главная», кликаем по кнопке «Найти и выделить», которая расположена на ленте в блоке инструментов «Редактирование». В появившемся меню выбираем пункт «Найти…». Вместо этих действий можно просто набрать на клавиатуре сочетание клавиш Ctrl+F.

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

При нажатии на кнопку «Найти далее» мы перемещаемся к первой же ячейке, где содержатся введенные группы символов. Сама ячейка становится активной.

Поиск и выдача результатов производится построчно. Сначала обрабатываются все ячейки первой строки. Если данные отвечающие условию найдены не были, программа начинает искать во второй строке, и так далее, пока не отыщет удовлетворительный результат.

Поисковые символы не обязательно должны быть самостоятельными элементами. Так, если в качестве запроса будет задано выражение «прав», то в выдаче будут представлены все ячейки, которые содержат данный последовательный набор символов даже внутри слова. Например, релевантным запросу в этом случае будет считаться слово «Направо». Если вы зададите в поисковике цифру «1», то в ответ попадут ячейки, которые содержат, например, число «516».

Для того, чтобы перейти к следующему результату, опять нажмите кнопку «Найти далее».

Так можно продолжать до тех, пор, пока отображение результатов не начнется по новому кругу.

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

Способ 2: поиск по указанному интервалу ячеек

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

Выделяем область ячеек, в которой хотим произвести поиск.

Набираем на клавиатуре комбинацию клавиш Ctrl+F, после чего запуститься знакомое нам уже окно «Найти и заменить». Дальнейшие действия точно такие же, что и при предыдущем способе. Единственное отличие будет состоять в том, что поиск выполняется только в указанном интервале ячеек.

Способ 3: Расширенный поиск

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

К тому же, в выдачу может попасть не только содержимое конкретной ячейки, но и адрес элемента, на который она ссылается. Например, в ячейке E2 содержится формула, которая представляет собой сумму ячеек A4 и C3. Эта сумма равна 10, и именно это число отображается в ячейке E2. Но, если мы зададим в поиске цифру «4», то среди результатов выдачи будет все та же ячейка E2. Как такое могло получиться? Просто в ячейке E2 в качестве формулы содержится адрес на ячейку A4, который как раз включает в себя искомую цифру 4.

Но, как отсечь такие, и другие заведомо неприемлемые результаты выдачи поиска? Именно для этих целей существует расширенный поиск Excel.

После открытия окна «Найти и заменить» любым вышеописанным способом, жмем на кнопку «Параметры».

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

По умолчанию, функции «Учитывать регистр» и «Ячейки целиком» отключены, но, если мы поставим галочки около соответствующих пунктов, то в таком случае, при формировании результата будет учитываться введенный регистр, и точное совпадение. Если вы введете слово с маленькой буквы, то в поисковую выдачу, ячейки содержащие написание этого слова с большой буквы, как это было бы по умолчанию, уже не попадут. Кроме того, если включена функция «Ячейки целиком», то в выдачу будут добавляться только элементы, содержащие точное наименование. Например, если вы зададите поисковый запрос «Николаев», то ячейки, содержащие текст «Николаев А. Д.», в выдачу уже добавлены не будут.

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

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

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

Ещё более точно поиск можно задать, нажав на кнопку «Формат».

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

Если вы хотите использовать формат какой-то конкретной ячейки, то в нижней части окна нажмите на кнопку «Использовать формат этой ячейки…».

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

После того, как формат поиска настроен, жмем на кнопку «OK».

Бывают случаи, когда нужно произвести поиск не по конкретному словосочетанию, а найти ячейки, в которых находятся поисковые слова в любом порядке, даже, если их разделяют другие слова и символы. Тогда данные слова нужно выделить с обеих сторон знаком «*». Теперь в поисковой выдаче будут отображены все ячейки, в которых находятся данные слова в любом порядке.

Как только настройки поиска установлены, следует нажать на кнопку «Найти всё» или «Найти далее», чтобы перейти к поисковой выдаче.

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

Как в Excel найти слово или фразу?

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

Самый простой способ — выполнить поиск. Для этого можно нажать клавиатурную комбинацию CTRL + F (от англ. Find), откроется окно поиска слов.

Для нажатия клавиатурной комбинации, нажмите клавишу клавиатуры CTRL и, удерживая ее, нажмите клавишу F (на английский язык переходить не нужно).

Вместо клавиатурной комбинации можно использовать кнопку поиска на панели Главная — Найти и выделить — Найти.

По умолчанию открывается маленькое окно, в которое нужно вписать искомое слово и нажать клавишу Найти все или Найти далее.

• Найти все — выполнит поиск всех совпадений с указанной фразой. В окне ниже появится список, в котором будет указана фраза, содержащая искомые символы, а также место в документе, где символы были найдены.

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

Также можно сделать шире столбцы: Книга, Лист, Имя и т.д., потянув за маркеры между названиями столбцов.

В столбце Значение можно видеть полный текст ячейки, в котором есть искомые символы (в нашем примере — excel). Чтобы перейти к этому месту в таблице просто нажмите левой кнопкой мыши на нужную строку, и курсор автоматически переместится в выбранную ячейку таблицы.

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

Дополнительные параметры поиска слов и фраз

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

Здесь можно указать дополнительные параметры поиска.

• на листе — только на текущем листе;
• в книге — искать во всем документе Excel, если он состоит из нескольких листов.

• по строкам — искомая фраза будет искаться слева направо от одной строки к другой;
• по столбцам — искомая фраза будет искаться сверху вниз от одного столбца к другому.

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

Область поиска — определяет, где именно нужно искать совпадения:

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

А также дополнительные параметры:

• Учитывать регистр — означает, что заглавные и маленькие буквы будут считаться как разные.

Например, если не учитывать регистр, то по запросу «excel» будет найдены все вариации этого слова, например, Excel, EXCEL, ExCeL и т.д.

Если поставить галочку учитывать регистр, то по запросу «excel» будет найдено только такое написание слова и не будет найдено слово «Excel».

• Ячейка целиком — галочку нужно ставить в том случае, если нужно найти те ячейки, в которых искомая фраза находится целиком и нет других символов. Например, есть таблица со множеством ячеек, содержащих различные числа. Поисковый запрос: «200». Если не ставить галочку ячейка целиком, то будут найдены все числа, содержащие 200, например: 2000, 1200, 11200 и т.д. Чтобы найти ячейки только с «200», нужно поставить галочку ячейка целиком. Тогда будут показаны только те, где точное совпадение с «200».
• Формат… — если задать формат, то будут найдены только те ячейки, в которых есть искомый набор символов и ячейки имеют заданный формат (границы ячейки, выравнивание в ячейке и т.д.). Например, можно найти все желтые ячейки, содержащие искомые символы.

Формат для поиска можно задать самому, а можно выбрать из ячейки-образца — Выбрать формат из ячейки.

Чтобы сбросить настройки формата для поиска нужно нажать Очистить формат поиска.

Это меню вызывается, если нажать на стрелочку в правой части кнопки Формат.

Второй способ, который позволяет найти искомое слово в таблице Excel, не является именно поиском, но также может быть удобен для работы. Это фильтр по фразе (символам), который позволяет вывести на экране только те строки, которые содержат искомые символы.

Для этого нужно щелкнуть мышкой по любой ячейке, среди которых нужно искать, нажать на вкладке Главная — Сортировка и фильтры — Фильтр.

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

Нужно нажать на стрелочку в том столбце, в котором будет выполняться фильтр. В нашем случае нажимаем стрелочку в столбце Слова и пишем символы, которые мы будем искать — «замок». То есть мы выведем только те строки, в которых есть слово «замок».

Результат будет таков.

Таблица до применения фильтра и таблица после применения фильтра.

Фильтрация не изменяет таблицу и не удаляет строки, она просто показывает искомые строки, скрывая не нужны. Чтобы удалить фильтр, нужно нажать на стрелочку в заголовке — Удалить фильтр с слова…

Также можно нажать на стрелочку и выбрать Текстовые фильтры — Содержит и указать искомые символы.

И далее ввести искомую фразу, например «Мюнхен».

Результат будет таков — только строки, содержащие слово «Мюнхен».

Этот фильтр сбрасывается также, как и предыдущий.

Таким образом, у пользователя есть варианты поиска слова в Excel — собственно сам поиск и фильтр.

Видеоурок по теме

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

Есть мнение?
Оставьте комментарий

Понравился материал?
Хотите прочитать позже?
Сохраните на своей стене и
поделитесь с друзьями

Вы можете разместить на своём сайте анонс статьи со ссылкой на её полный текст

Microsoft Excel – это универсальный инструмент, который можно использовать для широкого круга задач. Управление данными и информацией о выставлении счетов, ведение финансовой отчетности, что угодно. Однако вам придется научиться использовать формулы, управлять листами и многое другое для всего этого.

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

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

Не волнуйся. Функция поиска в Microsoft Excel поможет вам. Чтобы найти определенное значение ячейки:

1. Сначала выберите диапазон ячеек, в котором вы хотите выполнить поиск. Если вы хотите выполнить поиск по всей таблице, просто щелкните случайную ячейку.
2. Перейдите на главную> Найти и выбрать> Найти . Как вариант, вы также можете использовать комбинацию клавиш Ctrl + F.
3. Рядом с меткой « Найти» введите значение, которое вы хотите найти.
4. Excel выделит первое появление значения в электронной таблице. Нажмите кнопку « Найти далее» , чтобы перейти ко второй ячейке.

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

Используйте подстановочные знаки для поиска ячеек

Excel также позволяет использовать подстановочные знаки при поиске значений в электронной таблице.

1. Звездочка (*) : соответствует строке символов. Ab * будет соответствовать Abbott, Abandon, Absolutely и т. Д.
2. Вопросительный знак (?) : Соответствует одному символу. Ab? будет соответствовать Abc, Abd, Abz и т. д.

Например, для поиска значений, начинающихся с J в электронной таблице, введите «J *» в поле « Найти» .

Найти ячейки с определенным форматированием

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

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

Щелкните ОК . Затем выберите вариант « Найти далее» или « Найти все» . Excel перечислит все ячейки с тем же стилем форматирования, который вы указали.

Найти ячейки с формулами

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

Excel отобразит список параметров с флажками. Проверьте формулы и выберите подпункты, которые соответствуют вашим потребностям. Параметры « Числа» выделяют формулы, возвращающие числа, « Текст» – формулы, возвращающие текстовое значение, и т. Д.

После этого нажмите OK , и Excel выделит все ячейки с формулами, соответствующими критериям.

Как заменить значения в Excel

После поиска нужных ячеек функция замены Excel поможет вам изменить значение ячеек. К счастью, в Excel заменить сотни значений можно всего за несколько щелчков мышью.

Чтобы найти и заменить значения ячеек в Microsoft Excel:

1. Щелкните Главная> Найти и выбрать> Заменить .
2. Введите текст, который вы хотите найти, в поле рядом с меткой « Найти» .
3. Введите значение, которым вы хотите заменить ячейки, в поле « Заменить на» . Например, давайте найдем имена, начинающиеся с буквы J, и заменим их словом «Jade».
4. Excel выделит первую ячейку, соответствующую критериям поиска. Вы можете нажать « Заменить», чтобы заменить его значение.
5. Затем нажмите « Найти далее», чтобы перейти к следующей ячейке, и снова выберите « Заменить», чтобы изменить значение ячейки.
6. Вы также можете нажать « Найти все» и « Заменить все», чтобы сразу изменить значения. Excel уведомит вас об успешном завершении процесса, отобразив подсказку.

Дополнительные опции

Помимо базового поиска и замены, Excel также предлагает некоторые дополнительные параметры, которые вы можете использовать при работе с электронной таблицей.

Искать во всей книге

По умолчанию Excel ищет поисковый запрос только в текущей электронной таблице. Чтобы убедиться, что вы получаете результаты поиска по всей книге:

1. В диалоговом окне « Найти и заменить » нажмите « Параметры» .
2. Рядом с меткой « Внутри» щелкните раскрывающееся меню и выберите « Книга» вместо « Лист» .
3. Теперь, когда вы выбираете опцию « Найти все» , Excel будет отображать результаты ячеек из всей книги, а не из одного листа.

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

Учитывать регистр при поиске

Чтобы выполнить поиск с учетом регистра в Excel, перейдите в окно « Найти и заменить» и нажмите кнопку « Параметры» .

Теперь установите флажок рядом с меткой Учитывать регистр, чтобы включить поиск с учетом регистра.

Изменить форматирование значений

Хотя Excel позволяет находить и заменять значения ячеек, вы также можете легко изменить форматирование ячеек. Начать:

1. Нажмите « Главная»> «Найти и выбрать», а затем выберите « Заменить» в раскрывающемся меню.
2. Введите значение ячейки, которую вы хотите найти, в поле Найти .
3. Щелкните параметр « Форматировать» рядом с полем « Заменить меткой».
4. Измените стиль форматирования и цвет фона в соответствии с вашими потребностями.
5. Нажмите « Найти все», чтобы найти ячейки, соответствующие вашему поисковому запросу.
6. Чтобы изменить форматирование ячеек, нажмите « Заменить» или « Заменить все» .
7. Excel автоматически применит стиль форматирования к выделенным ячейкам.

Excel упрощает вашу работу

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

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