Выбор строки в excel по условию

Поиск и выделение ячеек, соответствующих определенным условиям

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

Выполните указанные ниже действия.

Для начала выполните одно из указанных ниже действий.

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

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

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

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

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

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

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

Ячейки, содержащие примечания.

Ячейки, содержащие константы.

Ячейки, содержащие формулы.

Примечание: С помощью флажков под формулами определяется тип формулы.

текущая область, например весь список.

массив целиком, если активная ячейка содержится в массиве.

Графические объекты, включая диаграммы и кнопки, на листе и в текстовых полях.

Различия между строками

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

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

Различия между столбцами

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

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

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

Щелкните только прямые , чтобы найти только ячейки, на которые ссылается формула.

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

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

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

Щелкните все уровни , чтобы найти все ячейки, которые прямо или косвенно ссылаются на активную ячейку.

Последняя ячейка на листе, содержащая данные или форматирование.

Только видимые ячейки

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

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

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

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

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

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

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

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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Выборка значений из таблицы Excel по условию

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

Как сделать выборку в Excel по условию

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

Автоматическую выборку реализует нам формула, которая будет обладать следующей структурой:

В месте «диапазон_данных_для_выборки» следует указать область значений A6:A18 для выборки из таблицы (например, текстовых), из которых функция ИНДЕКС выберет одно результирующие значение. Аргумент «диапазон» означает область ячеек с числовыми значениями, из которых следует выбрать первое наименьшее число. В аргументе «заголовок_столбца» для второй функции СТРОКА, следует указать ссылку на ячейку с заголовком столбца, который содержит диапазон числовых значений.

Естественно эту формулу следует выполнять в массиве. Поэтому для подтверждения ее ввода следует нажимать не просто клавишу Enter, а целую комбинацию клавиш CTRL+SHIFT+Enter. Если все сделано правильно в строке формул появятся фигурные скобки.

Обратите внимание ниже на рисунок, где в ячейку B3 была введена данная формула в массиве:

Выборка соответственного значения с первым наименьшим числом:

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

Как работает выборка по условию

Ключевую роль здесь играет функция ИНДЕКС. Ее номинальное задание – это выбирать из исходной таблицы (указывается в первом аргументе – A6:A18) значения соответственные определенным числам. ИНДЕКС работает с учетом критериев определённых во втором (номер строки внутри таблицы) и третьем (номер столбца в таблице) аргументах. Так как наша исходная таблица A6:A18 имеет только 1 столбец, то третий аргумент в функции ИНДЕКС мы не указываем.

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

Функция ЕСЛИ позволяет выбрать значение из списка по условию. В ее первом аргументе указано где проверяется каждая ячейка в диапазоне B6:B18 на наличие наименьшего числового значения: ЕСЛИB6:B18=МИНB6:B18. Таким способом в памяти программы создается массив из логических значений ИСТИНА и ЛОЖЬ. В нашем случаи 3 элемента массива будут содержат значение ИСТИНА, так как минимальное значение 8 содержит еще 2 дубликата в столбце B6:B18.

Следующий шаг – это определение в каких именно строках диапазона находится каждое минимальное значение. Это нам необходимо по причине определения именно первого наименьшего значения. Реализовывается данная задача с помощью функции СТРОКА, она заполняет элементы массива в памяти программы номерами строк листа. Но сначала от всех этих номеров вычитается номер на против первой строки таблицы – B5, то есть число 5. Это делается потому, что функция ИНДЕКС работает с номерами внутри таблицы, а не с номерами рабочего листа Excel. В тоже время функция СТРОКА умеет возвращать только номера строк листа. Чтобы не получилось смещение необходимо сопоставить порядок номеров строк листа и таблицы с помощи вычитанием разницы. Например, если таблица находится на 5-ой строке листа значит каждая строка таблицы будет на 5 меньше чем соответственная строка листа.

После того как будут отобраны все минимальные значения и сопоставлены все номера строк таблицы функция МИН выберет наименьший номер строки. Эта же строка будет содержать первое наименьшее число, которое встречается в столбце B6:B18. На основании этого номера строки функции ИНДЕКС выберет соответствующее значение из таблицы A6:A18. В итоге формула возвращает это значение в ячейку B3 в качестве результата вычисления.

Как выбрать значение с наибольшим числом в Excel

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

Если необходимо изменить условия формулы так, чтобы можно было в Excel выбрать первое максимальное, но меньше чем 70:

=70;»»;B6:B18));СТРОКА(B6:B18)-СТРОКА(B5);»»)))’ >

Как в Excel выбрать первое минимальное значение кроме нуля:

Как легко заметить, эти формулы отличаются между собой только функциями МИН и МАКС и их аргументами.

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

Работа со строками/столбцами по условию

Данная функция является частью надстройки MulTEx

Вызов команды:
MulTEx -группа Ячейки/Диапазоны —Работа со строками/Столбцами —Работа со строками/столбцами по условию

Данная команда позволяет удалить, переместить или скопировать строки/столбцы по различным условиям:

Операция — выбор действия над строками/столбцами. Возможно Удаление, Перемещение и Копирование. Перемещение и Копирование возможно на новый лист или в указанный диапазон того же листа.
Удалить/Переместить/Скопировать — в данном разделе можно выбрать над чем производить Удаление/Копирование/Перемещение: над строками или столбцами, а так же выбрать диапазон для выполнения действий.

Строки — действия будут произведены со строками
Столбцы — действия будут произведены со столбцами
На всем листе — действия над строками/столбцами будут произведены на всем листе
В диапазоне — действия над строками/столбцами будут произведены только внутри указанного диапазона. На картинке выше указан диапазон Лист1!$A$1:$E$25. Это значит, что если выбрано Удаление Строк, то строки будут удалены только в этом диапазоне. Даже если строка 27 листа будет удовлетворять условиям, она не будет удалена, т.к. выходит за пределены указанного диапазона.

Переместить — указывается место назначения для копируемых или перемещаемых строк/столбцов.

На новый лист — в данном случае строки/столбцы будут вставлены на новый лист, который будут создан в ходе выполнения команды. Вставка отобранных строк/столбцов начинается с ячейки А1 нового листа.
Начиная с указанной ячейки — в данном случае строки/столбцы будут вставлены на тот же лист, в котором производится Копирование/Перемещение. Вставка отобранных строк/столбцов начинается именно с указанной ячейки. Примечание: Если указать диапазон для вывода результата внутри диапазона с данными для отбора строк/столбцов, то появится сообщение:

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

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

Переместить строки — указываются критерии для отбора строк/столбцов к удалению/перемещению/копированию.

• Все пустые — будут удалены все пустые строки/столбцы в указанном диапазоне.
• Если значение ячейки: Ниже в примерах применения данных параметров описаны операции по удалению строк, но все это применимо для любой выбранной операции.
  • Равно — будут удалены те строки в ячейках которых есть значение, равное указанному. В критериях можно применять символы подстановки(звездочка( * ), вопр.знак( ? ) ). Например на картинке указан критерий: «*С.Петербург*». Это означает, что если в ячейке будет записано «Произведено в г. С.Петербург 12.03.2008», то данная строка будет удалена. Если же указать «С?Петербург», то будут удалены строки, значение в которых равно либо «С.Петербург», либо «С-Петербург», либо «С Петербург» и т.п. Если же указать «С.Петербург», то будут удалены только те строки, значение которых в точности совпадает с «С.Петербург».
  • Не равно — будут удалены те строки в ячейках которых нет значения, равного указанному. Все работает так же, как и при выборе Равно, но в обратном направлении. Т.е. если указать «С.Петербург», то будут удалены все строки, значение которых не равно «С.Петербург».
  • Учитывать регистр — при отборе значений будет учитываться регистр критерия или нет. Если не установлено, то при указании равно «С.Петербург» будут удалены строки равные и значению «С.Петербург» и значению «с.петербург».
  • В поле ниже указывается номер столбца, в котором просматривать эти критерии. Например, если в этом поле указать 2 и На всем листе, то критерии будут просматриваться во втором столбце листа — $B . Если указать 2 и диапазон $B$1:$E$25, то критерии будут просматриваться во втором столбце указанного диапазона, т.е. в третьем столбце листа — $C . Если данное поле оставить пустым, то значения будут просматриваться во всех столбцах диапазона.

Закрыть форму после выполнения операции — по умолчанию включен. Если снять, то после Удаления/Перемещения/Копирования строк/столбцов форма не закроется и можно будет указать следующий критерий.

Как выбрать в Экселе все значения по нескольким условиям

Здравствуйте, друзья. Известные нам функции поиска данных, такие, как ВПР и ПОИСКПОЗ, ищут в массиве первое совпадение и возвращают его. Во многих случаях это и требуется, но что делать, когда следует получить все результаты, а не только первый, и искать их по нескольким критериям, а не по одному?

В этой статье мы решим такую задачу. Описываю пример. Есть ведомость школьных оценок 1-3 классов. Мы хотим выбрать класс, оценку, и увидеть учеников класса, которые получили этот балл:

Задача интересная, и достаточно простая. Однако, нам понадобится сделать дополнительный столбец. Действуем в несколько этапов.

Создание вспомогательной колонки

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

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

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

Формулу укажу на примере ячейки D3:

Функция СУММ для каждой строки сложит значение из предыдущей ячейки, а так же, результат сравнения класса в ведомости с искомым, оценки в ведомости с искомыми. Два уточнения:

• В ячейке D2 – текст, и функция СУММ его проигнорирует. В остальных строчках будет браться число из ячейки сверху;
• ФОРМУЛА И(A3=$L$2;C3=$L$3) вернёт ИСТИНУ (единицу), когда оба условия внутри неё выполнятся, или ЛОЖЬ (ноль), если хотя бы одно из условий не выполнится. Таким образом, когда функция И найдет совпадение класса и оценки, СУММ увеличит порядковый номер на единицу, что нам и требовалось

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

Вывод отобранных строк в таблицу

Теперь осталось вывести на экран выборку учеников класса с нужной оценкой. Для этого сопоставим номера в столбце F с номерами в технической колонке D. Удобнее всего это сделать с помощью комбинации функций ИНДЕКС и ПОИСКПОЗ:

Алгоритм работы на примере ячейки G3:

1. Функция ПОИСКПОЗ получает числовой индекс строки из ячейки F3 и ищет такое же значение в техническом столбце D. Возвращает либо порядковый номер найденной ячейки в массиве, либо ошибку #Н/Д
2. Функция ИНДЕКС получает из ведомости класс, находящийся в ячейке, порядковый номер которой мы получили в первом пункте
3. Далее функция ЕСЛИОШИБКА выводит на экран класс, если он найден, или пустую строку, если ПОИСКПОЗ вернула ошибку

Такой порядок работает и для остальных столбцов: «Фамилия», «Баллы». Теперь если изменить искомый класс или оценку, формулы сами всё пересчитают и переформируют список. Можно скрыть дополнительный столбец, чтобы не мешал.

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

Мы получили полностью автоматизированный интерактивный проект, способный формировать списки «налету», сразу после того, как вы сделаете свой выбор. На этом всё, и я готов отвечать на ваши вопросы!