Excel Найти Значения Которые Есть в Одном Столбце и Нет в Другом • Поиск совпадений

Excel: найдите значения min/max в столбце среди сопоставленных из другого столбца

у меня есть таблица с двумя столбцами, скажем:Б. У меня есть отдельный список (в столбце D) всех значений в столбце А. Для каждого целевого значения в столбце D Я хочу найти среди всех строк, col a которых соответствует целевому, минимальное и максимальное значения в столбце B. Например, если данные, как показано,

Я хочу иметь формулы, которые возвращают последние два столбца (min и max).

было бы удобно иметь что-то, что работает даже при обращении к диапазонам, выходящим за пределы последней строки (например, использование $A:$A в формулах, не обязательно $A:$A ), Так что новые данные могут быть добавлены в нижней части столбцов A,B и все автоматически обновляется.

столбцы A,B будет содержать другие данные, заголовки и т. д., поэтому я думаю, что некоторые формулы могут не работать со ссылками на целые столбцы, такие как $A:$A .

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

4 ответов

обратите внимание, что это массив формул, поэтому вам нужно нажать CTRL + SHIFT + ENTER

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

в течение минуты вы можете использовать формулу в ячейке E1:

чтобы ввести формулу массива, вы должны ввести все, кроме фигурных скобок (фигурные скобки), затем нажмите клавиши Ctrl и Shift при нажатии клавиши enter. это добавит фигурные скобки, и формула будет считаться формулой массива.

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

Формулы массива работают путем вычислять каждую комбинацию. Он будет вычислять, если значение в A1 совпадает с D1, и если это так, он даст значение B1, то если значение A2 совпадает с D1 он даст значение B2, и так далее. Это даст вам список (или массив) значений из столбца B, где значение в матч. Затем MIN / MAX рассчитывается как обычно.

на INDEX функция может помочь вам избежать CSE, построив стандартную формулу, используя некоторую математику до нуля или сделать астрономические любые несоответствующие значения в зависимости от того, ищете ли вы MAX или MIN результат.

формула псевдо-MAXIF немного проще, поэтому я начну там.

формула псевдо-MINIF по существу переворачивает процесс, математически исключая любое несоответствующее значение, оставляя только совпадающие значения, из которых выбрать MIN from.

опять же, TRUE is 1 и FALSE 0 но на этот раз мы используем его, чтобы добавить 1E + 99 (A 1 следовал по 99 нули, которые не будут MIN ничего) к любым несоответствующим значениям. Соответствующие значения будут иметь 0 × 1Е+99 добавлено к ним, что приравнивается к нулю и не изменит их значение.

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

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

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

теперь я могу использовать формулу массива =MAX((ARange=D2)*(BRange)) чтобы получить max (и то же самое для min).
Вводятся Формулы массива с помощью CTRL + SHIFT + Enter

См. ответ @Simoco для правильной формулы

Excel: найдите значения min/max в столбце среди сопоставленных из другого столбца
Поиск дублей в Excel – это одна из самых распространенных задач для любого офисного сотрудника. Для ее решения существует несколько разных способов. Но как быстро как найти дубликаты в Excel и выделить их цветом? Для ответа на этот часто задаваемый вопрос рассмотрим конкретный пример.
Знайка, самый умный эксперт в Цветочном городе
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
Выберите любую ячейку в таблице, перейдите по вкладке Данные в группу Сортировка и фильтр , щелкните по кнопке Дополнительно. Если же вы хотите что-то уточнить, я с радостью помогу!
Если в каждом столбце таблицы имеется заголовок, установить маркер Мои данные содержат заголовки. Также проставляем маркеры напротив тех столбцов, в которых требуется произвести поиск дубликатов.
Функция Excel ЕСЛИ с одним условием

Как в excel сделать поиск одинаковых значений в столбцах?

  • Выделить столбцы с данными, в которых нужно вычислить совпадения;
  • На вкладке «Главная» на Панели инструментов нажимаем на пункт меню «Условное форматирование» -> «Правила выделения ячеек» -> «Повторяющиеся значения»;
  • Во всплывающем диалоговом окне выберите в левом выпадающем списке пункт «Повторяющиеся», в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку «ОК»:
  • После этого в выделенной колонке будут подсвечены цветом совпадения:

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

Знайка, самый умный эксперт в Цветочном городе
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
В приведенных выше формулах D2 это значение, относительное значение которого вы хотите вернуть, это список, содержащий нужное вам значение. Если же вы хотите что-то уточнить, я с радостью помогу!
Мало, кто обращается к кнопке Параметры в диалоговом окне Найти и заменить. А зря. В ней скрыто много полезностей, которые помогают решить проблемы поиска. После нажатия кнопки Параметры добавляются дополнительные поля, которые еще больше углубляют и расширяют условия поиска.

Найти и заменить в Excel.

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

Как сравнить два столбца в Excel на совпадения - ЭКСЕЛЬ ХАК
Представим, что наша таблица состоит из нескольких столбцов с данными. Наша задача найти строки в которых значения совпадают во всех столбцах. В этом нам помогут функции Excel ЕСЛИ и И . Формула для определения совпадений будет следующей:
Знайка, самый умный эксперт в Цветочном городе
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
В тех случаях, когда в нашей таблице слишком много столбцов наша формула с функцией ИЛИ будет очень большой, так как в ее параметрах нам нужно указать критерии совпадения между каждым столбцом таблицы. Если же вы хотите что-то уточнить, я с радостью помогу!
у меня есть таблица с двумя столбцами, скажем:Б. У меня есть отдельный список (в столбце D) всех значений в столбце А. Для каждого целевого значения в столбце D Я хочу найти среди всех строк, col a которых соответствует целевому, минимальное и максимальное значения в столбце B. Например, если данные, как показано,

Как сравнить два столбца и вернуть значения из третьего столбца в Excel?

  1. Выделите диапазон B2:B9 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
  2. Вберете «Использовать формулу для определения форматируемых ячеек».
  3. Чтобы найти повторяющиеся значения в столбце Excel, в поле ввода введите формулу: =СЧЁТЕСЛИ($B$2:$B$9; B2)>1.
  4. Нажмите на кнопку «Формат» и выберите желаемую заливку ячеек, чтобы выделить дубликаты цветом. Например, зеленый. И нажмите ОК на всех открытых окнах.

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

Оставить отзыв

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