Как в excel выбрать строки по условию - Учим Эксель

Как в кабинете

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

Условное форматирование в Excel

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

Как же быть, если нужно выделить остальные ячейки в зависимости от значения некий одной? На снимке экрана, расположенном чуток выше, видно таблицу с кодовыми наименованиями разных версий Ubuntu. Один из их — придуманный. Когда я ввёл No в столбце Really?, вся строчка изменила цвет фона и шрифта. Читайте далее, и Вы узнаете, как это делается.

Создаём таблицу

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

Условное форматирование в Excel

Придаём таблице наиболее приятный вид

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

Условное форматирование в Excel

Создаём правила условного форматирования в Excel

Изберите исходную ячейку в первой из тех строк, которые Вы планируете форматировать. Кликните Conditional Formatting (Условное Форматирование) на вкладке Home (Основная) и изберите Manage Rules (Управление Правилами).

Условное форматирование в Excel

В открывшемся диалоговом окне Conditional Formatting Rules Manager (Диспетчер правил условного форматирования) нажмите New Rule (Сделать правило).

Условное форматирование в Excel

В диалоговом окне New Formatting Rule (Создание правила форматирования) изберите крайний вариант из перечня — Use a formula to determine which cells to format (Применять формулу для определения форматируемых ячеек). А на данный момент — основной секрет! Ваша формула обязана выдавать значение TRUE (ИСТИНА), чтоб правило сработало, и обязана быть довольно гибкой, чтоб Вы могли применять эту же формулу для предстоящей работы с Вашей таблицей.

Давайте проанализируем формулу, которую я сделал в своём примере:

G — это столбец, который управляет работой правила (столбец Really? в таблице). Увидели символ бакса перед G? Если не поставить этот знак и скопировать правило в последующую ячейку, то в правиле адресок ячейки двинется. Таковым образом, правило будет находить значение Yes, в некий иной ячейке, к примеру, H15 заместо G15. В нашем же случае нужно зафиксировать в формуле ссылку на столбец ($G), при всем этом позволив изменяться строке (15), так как мы собираемся применить это правило для нескольких строк.

Интересно почитать:  Excel удалить пробелы в начале строк

=»Yes» — это значение ячейки, которое мы отыскиваем. В нашем случае условие проще не придумаешь, ячейка обязана гласить Yes. Условия можно создавать любые, какие даст подсказку Для вас Ваша фантазия!

Говоря человечьим языком, выражение, записанное в нашей формуле, воспринимает значение TRUE (ИСТИНА), если ячейка, расположенная на пересечении данной строки и столбца G, содержит слово Yes.

Условное форматирование в Excel

Сейчас давайте займёмся форматированием. Нажмите клавишу Format (Формат). В открывшемся окне Format Cells (Формат ячеек) полистайте вкладки и настройте все характеристики так, как Вы желаете. Мы в своём примере просто изменим цвет фона ячеек.

Условное форматирование в Excel

Когда Вы настроили хотимый вид ячейки, нажмите ОК. То, как будет смотреться отформатированная ячейка, можно узреть в окошке Preview (Эталон) диалогового окна New Formatting Rule (Создание правила форматирования).

Условное форматирование в Excel

Нажмите ОК опять, чтоб возвратиться в диалоговое окно Conditional Formatting Rules Manager (Диспетчер правил условного форматирования), и нажмите Apply (Применить). Если избранная ячейка изменила собственный формат, означает Ваша формула верна. Если форматирование не поменялось, вернитесь на пару шажков вспять и проверьте опции формулы.

Условное форматирование в Excel

Сейчас, когда у нас есть работающая формула в одной ячейке, давайте применим её ко всей таблице. Как Вы увидели, форматирование поменялось лишь в той ячейке, с которой мы начали работу. Нажмите на иконку справа от поля Applies to (Применяется к), чтоб свернуть диалоговое окно, и, нажав левую клавишу мыши, протяните выделение на всю Вашу таблицу.

Условное форматирование в Excel

Когда сделаете это, нажмите иконку справа от поля с адресом, чтоб возвратиться к диалоговому окну. Область, которую Вы выделили, обязана остаться обозначенной пунктиром, а в поле Applies to (Применяется к) сейчас содержится адресок не одной ячейки, а целого спектра. Нажмите Apply (Применить).

Условное форматирование в Excel

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

Условное форматирование в Excel

Вот и всё! Сейчас осталось таковым же образом сделать правило форматирования для строк, в которых содержится ячейка со значением No (ведь версии Ubuntu с кодовым именованием Chipper Chameleon по сути никогда не было). Если же в Вашей таблице данные труднее, чем в этом примере, то возможно придётся сделать большее количество правил. Пользуясь сиим способом, Вы просто будете создавать сложные приятные таблицы, информация в которых практически кидается в глаза.

Интересно почитать:  Как в excel перемешать строки

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

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

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

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

Прайс продуктов.

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

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

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

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

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

Условие выбрать первое минимальное.

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

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

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

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

Интересно почитать:  Удалить надстройку в excel

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

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

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

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

Осознав принцип деяния формулы, сейчас можно просто ее видоизменять и настраивать под остальные условия. К примеру, формулу можно поменять так, чтоб выбрать 1-ое наибольшее значение в Excel:

Первое максимальное значение.

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

=70;»»;B6:B18));СТРОКА(B6:B18)-СТРОКА(B5);»»)))’ > Максимальное значение по условию.

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

Больше чем ноль.

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

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

Ссылка на основную публикацию
Adblock
detector