Поиск значения больше заданного в excel: Поиск ближайшего значения Excel с помощью формулы

Поиск ближайшего значения Excel с помощью формулы

Каждый пользователь Excel без проблем может найти наименьшее или наибольшее значение в диапазоне чисел, используя для этого функции: =МИН(), =МАКС() или =НАИМЕНЬШИЙ() и =НАИБОЛЬШИЙ(). Так же легко найти номер позиции исходного значения в диапазоне ячеек с помощью функции =ПОИСКПОЗ(). Но в данном примере будет более интересное решение, которое позволяет выполнить поиск ближайшего значения в Excel. Создадим формулу, которая способна находить наиболее приближенное значение к соответствию запроса пользователя. Например, несли диапазон данных не содержит значений для точного совпадения с запросом пользователя то функция ПОИСКПОЗ возвращает ошибку #Н/Д. Но пользователя вполне устроил бы и приближенный результат, не зависимо от того будет ли он немного меньше или немного больше соответствовать запросу. Важным преимуществом такой формулы заключается в том, что нет необходимости использовать условную сортировку для решения такого рода задач.

Как найти ближайшее значение в Excel?

Возьмем для примера, конкретную ситуацию. Фирма переводит склад на новое место, и чтобы полностью заполнить фуру товарами с одинаковым объемом упаковок (например, офисная бумага для принтера формат A4 по 500 листов) нужно вложить еще 220 пачке. Но желательно не перемешивать ассортимент продукции. То есть нужно постараться избежать пересорта товаров при переезде на новый склад. Заполним оставшееся пустое место в фуре пачками офисной бумаги одного и того же типа стандарта качества. У нас есть остатки по товарам всех ассортиментов:

Нам нужно выполнить поиск ближайшего меньшего значения Excel. Чтобы найти ассортимент с наиболее подходящим количеством по остаткам (не более 220 шт.) создаем формулу:

  1. В ячейке E2 введите значение 220 – это количество пачек офисной бумаги, которое соответствует для заполнения свободного объема в фуре.
  2. В ячейке E3 вводим формулу:
    Подобную формулу можно использовать для поиска ближайшего значения в диапазоне Excel, а не только в одном столбце.
  3. Для подтверждения ввода формулы нажимаем комбинацию клавиш CTRL+SHIFT+Enter, так как формула должна выполняться в массиве. Если вы сделали все правильно, то в строке формул вы должны заметить фигурные скобки.

Результат вычисления формулы для поиска наиболее приближенного значения:

В результате поедет комплект одного ассортимента бумаги тип-9 (195шт.). Так как его количество на остатках наиболее приближенно соответствует к объему в 220 пачек. Фура будет максимально возможно заполнена, а на складе будет меньше пересорта по ассортиментам товаров.



Принцип поиска ближайшего значения по формуле:

Из каждого числа остатков в диапазоне ячеек B3:B12 вычитается исходное значение в ячейке E2. Таким образом создается условная таблица значений равных этой разницы, с размером соответствующему числу ячеек в диапазоне B3:B12. Функция ABS возвращает абсолютную величину числа по модулю и в этой же условной таблице заменяет все значения отрицательных чисел на положительные (без знака минус). Из полученных данных находим наименьшее значение с помощью функции =МИН(). А функция =ПОИСКПОЗ() возвращает нам позицию в условной таблице для наименьшего значения найденного функций МИН. Полученный результат вычисления функцией ПОИСКПОЗ является аргументом для функции =ИНДЕКС(), которая возвращает нам значение ячейки находящиеся под номером позиции в диапазоне B3:B12.

Другие возможности Excel для поиска значений

Очень часто работникам офиса приходится задерживаться на работе при подготовки сложных отчетов в Excel. Но некоторые сотрудники умудряются подготовить быстрее отчеты подобные по сложности. Мастерство – это получение больших результатов при приложении меньших усилий. В чем же секрет мастерства Excel? Ответ скрывается в готовых решениях, которые позволяют решать одни и те же задачи в Excel, но значительно быстрее.

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

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

Скачать пример поиска ближайшего значения в Excel

Выясним так же как быстро сортировать данные для создания комфортного визуального анализа. Правильная сортировка позволяет повысить читабельность и восприятие информации. А также организовать структуру для быстрого визуального поиска значений, что позволит принимать более эффективные решения при визуальном анализе. Excel обладает большими возможностями в этой области. Можно сортировать данные относительно многих критериев и столбцов, а также относительно формата ячеек. В следующих статьях будет представлен целый ряд эффективных способов сортировки, которые мало известные большинству пользователям 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 какую-то фразу или число используйте встроенные возможности интерфейса. Можно выбрать дополнительные параметры поиска и включить фильтр.

excel — найти все значения, большие или равные определенному значению

спросил

Изменено
8 лет, 10 месяцев назад

Просмотрено
115 тысяч раз

Допустим, у меня есть следующая таблица.

 Командный счет
АА 81
ББ 67
СС 44
ДД 1.5
JJ 279ЛЛ 49
ТТ 201
ГГ 158
ММ 32
ЧЧ 89

Я хочу, чтобы все команды, набравшие более 80 очков, попали в другую таблицу. Я попробовал функцию Index + Match следующим образом, но она дает мне только наименьшее значение больше 80.

Вот код:

 =INDEX($A$2:$A$11,MATCH(80,$B$2:$ 11 долл. США, -1))

Хотя я расположил значения аргумента lookup_array в порядке убывания, эта функция дает мне только один ответ: наименьшее значение больше 80. ты. Спасибо.

  • excel
  • excel-формула
  • excel-2007
  • excel-2010
  • excel-2013

2

Введите эту формулу в D2 и нажмите CTRL+SHIFT+ENTER , чтобы сделать ее формулой массива, затем перетащите ее вниз.

 =ИНДЕКС($A$1:$A$11,МАЛЕНЬКИЙ(ЕСЛИ($B$2:$B$11>=80,СТРОКА($B$2:$B$11)),СТРОКА(1:1)))

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

1

Как насчет использования автофильтра? Затем вы достаточно гибко фильтруете значения в оценке столбца.

0

Если вы не хотите использовать vba, в третьем столбце вы можете указать

 =if(B2 > 80, A2, "")

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

1

Создайте сводную таблицу ( Team для ROWS, Score для Sigma VALUES, затем отфильтруйте столбец Sum of Score для числовых фильтров, Greater Than… и 80 рядом с Show rows where: is больше, чем * , хорошо

* или, возможно, больше или равно

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя адрес электронной почты и пароль

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

excel — возвращаемые значения, равные или меньшие и равные или превышающие искомое значение в таблице с одним столбцом

Задавать вопрос

спросил

Изменено
1 год, 5 месяцев назад

Просмотрено
1к раз

У меня есть таблица с одним столбцом, подобная этой:

Я хочу найти значение, которое равно заданному искомому значению или сразу меньше его, и значение, равное или непосредственно превышающее заданное искомое значение . Например, предположим, что искомое значение равно 13. Тогда два выхода должны быть 10 и 15 соответственно.

Моя попытка

Мне удалось получить стандартное значение, которое равно или сразу меньше искомого значения, используя формулу

 =ВПР(D1, A2:A6, 1, ИСТИНА)

в ячейке D2, как показано на следующем рисунке, где ячейка D1 содержит значение поиска.

Как получить стандартное значение, равное или сразу превышающее искомое значение?

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

Однако одним из способов избежать сортировки данных и все же найти верхнее и нижнее значения может быть следующий:

 D2= МАЛЕНЬКИЙ(A2:A6, СЧЁТЕСЛИ(A2:A6, "<" & D1))
D3= МАЛЕНЬКИЙ(A2:A6, СЧЁТЕСЛИ(A2:A6, "<=" и D1) +1)

Однако аналогичный результат можно получить, объединив функцию МАЛЕНЬКИЙ с ИНДЕКС и ПОИСКПОЗ .

Читайте также: