Excel как найти равные числа

In Excel, there are plenty of formulas you can use to determine if two values are equal. Here are some examples.

Yes, you can tell if two numbers are equal in an instant just by looking at them. But that won’t be the case when you’re looking at larger numbers, or if you want to test multiple numbers in two columns to see if they’re equal.

Like everything else, Excel has a remedy that makes this test easier. There are many formulas you could write that check if two values in Excel are equal or not. Two of the simpler ones consist of the DELTA function and the IF function.

What Is the DELTA Function in Excel?

DELTA is a function in Excel that tests if two numerical values are equal or not. If the two values are equal, DELTA will return 1, and if they are not equal, it will return 0.

 =DELTA(number1, number2) 

The DELTA function can only operate on numbers, and it cannot test whether two text strings are equal or not. If number2 is left blank, DELTA will assume that it is zero.

How to Test if Two Values Are Equal With the DELTA Function

DELTA can be easily coupled with other Excel functions to count the equal number pairs in a list. In this sample spreadsheet, we have two columns of numbers, and we want to see how many pairs are equal.

For this example, we’re going to use the DELTA function to check if the numbers in each pair are equal. Then, we’re going to get the count of equal pairs using Excel’s COUNTIF function. Here’s how:

1. Select the first cell in the column where you want to check if the numbers are equal. In this example, we’ll use cell C2.
2. In the formula bar, enter the formula below:

    =DELTA(A2, B2) 

3. Press Enter. DELTA will now tell you if the two numbers are equal or not.
4. Grab the fill handle and drop it on the cells below to get the test results for all the numbers.

The formula we used here calls on DELTA to test the numbers in A2 and B2 and see if they’re equal. The formula then returns 1 to indicate that they were equal. If the two numbers weren’t equal, the formula would return 0.

Now you can see which pairs have equal numbers using the DELTA function. Unfortunately, that’s as far as the DELTA function goes. To count the number of pairs with equal numbers, you’ll have to use other functions. One candidate for this task is the COUNTIF function.

COUNTIF can go through a range of cells and return the number of cells that meet a specific condition. Since DELTA returned 1 to indicate equal pairs, we can ask COUNTIF to go through DELTA’s output and count the cells containing 1. This will give us the number of equal pairs.

1. Select the cell where you want to show the count of pairs with equal numbers.
2. In the formula bar, enter the formula below:

    =COUNTIF(C2:C10, "=1") 

3. Press Enter.

The formula calls on COUNTIF to check the cells C2 to C10 (the results from the DELTA function) and return the number of cells that equal 1.

Remember that the DELTA function returns 1 when the two values are equal, so this formula will count the number of pairs with equal numbers. You can replace 1 with 0 in the formula to get the count of pairs with unequal numbers.

The DELTA function is a simple enough method to check if two numbers are equal. However, if you don’t like the binary output of the DELTA function, you can use the IF function instead to get a custom output.

What Is the IF Function in Excel?

IF is one of the core Excel functions that lets you build sophisticated formulas. It takes a condition and then returns two user-specified outputs if the condition is or is not met.

 =IF(logical_Test, Output_If_True, Output_If_False) 

The IF function conducts a logical test, and if the test result is true, it returns Output_If_True. Otherwise, it returns Output_If_False.

IF is on a whole other league than DELTA. Where DELTA could only determine whether two numbers are equal, IF can run any kind of logical tests. Another advantage of IF to DELTA is that it isn’t restricted to numbers. In addition to numbers, you can check whether two strings of text are equal using IF in Excel.

How to Test if Two Values Are Equal With the IF Function

In this context, the IF function works in the same way as the DELTA function, except that you can have it output a specific string. To check if two values are equal with the IF function, you need to run a logical test where you put the two cells as equal.

Then, you’ll need to specify outputs for the two scenarios: The test result being true, and the test result being false.

Let’s put the IF function to use in the same example. We’re going to see which numbers pairs are equal, and then count the equal ones using the COUNTIF function.

1. Select the first cell in the column where you want to return the test results. This will be cell C2 for this example.
2. In the formula bar, enter the formula below:

    =IF(A2=B2, "Yes", "No") 

3. Press Enter. The IF function will now tell you whether the two values are equal or not.
4. Grab the fill handle and drop it on the cells below. The IF function will now test each pair and return the results accordingly.

The formula we used will test cells A2 and B2 to see if they’re equal. If the two cells are equal, the formula will return Yes. Otherwise, the formula will return No.

You can count the equal pairs using the COUNTIF function in the same way as the previous example, except that instead of counting the cells that equal 1, you should count the cells containing the string Yes.

This is because the IF formula you wrote returns Yes for pairs that contain equal values. Here’s how you can count the equal pairs:

1. Select the cell where you want to return the count. For this example, we’ve chosen cell E2.
2. In the formula bar, enter the formula below:

    =COUNTIF(C2:C10, "=Yes") 

3. Press Enter. COUNTIF will now tell you how many pairs contain equal values.

This formula will summon COUNTIF to look through cells C2 to C10 (the results from the IF function) for cells containing the string Yes and then return the count of cells that do.

Since IF returned Yes for equal pairs, then this will be the count of equal pairs.

Test if Two Values Are Equal With Excel Functions

There are plenty of formulas you could write up to check whether two values are equal in Excel. The DELTA function is designed to serve this purpose exclusively, whereas the IF function can accomplish many feats, this included.

Excel exists to take the burden of counting and calculating off your shoulders. This is one minor instance of how Excel can do that, and make your life easier as a result.

Базовая формула

= СЧЁТЕСЛИ (диапазон; значение)

Для того, чтобы подсчитать количество ячеек равных определенному значению, вы можете использовать функцию СЧЁТЕСЛИ.

В показанном примере, G4 содержит следующую формулу:

= СЧЁТЕСЛИ (D5: D11; «красный»)

Функция СЧЁТЕСЛИ является полностью автоматической — она подсчитывает количество ячеек в диапазоне, которые соответствуют поставленным критериям.

Для диапазона, мы используем D5: D11, который содержит цвета. Для критериев, мы просто используем «красный».

СЧЁТЕСЛИ возвращает количество значений в D5: D11, которые равны «красный».

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

Количество ячеек, равных с учетом регистра

= СУММПРОИЗВ (— СОВПАД (значение; диапазон))

Для подсчета ячеек, которые содержат определенный текст с учетом регистра, вы можете применить формулу, которая использует функцию СОВПАД наряду с СУММПРОИЗВ.

В примере, есть список имен в B5:B11 . В диапазоне E5:E8, существует еще один список имен, где дубликаты удалены. Для того, чтобы получить подсчет «алена», вы можете использовать следующую формулу:

= СУММПРОИЗВ ((—СОВПАД(D5;B5:B11)))

Сочетание этих формул даст ответ 3, так как есть 3 записи «алена» (в нижнем регистре) в диапазоне B5:B11.

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

{ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ИСТИНА;ИСТИНА}

Каждая ИСТИНА представляет собой точное совпадение «алена» в B5:B11

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

{0;0;0;0;1;1;1}

Наконец, СУММПРОИЗВ просто суммирует значения в массиве и возвращает 3. Поскольку СУММПРОИЗВ может обрабатывать массивы изначально, для этого не обязательно использовать Ctrl + Shift + Enter, чтобы ввести эту формулу.

Количество ячеек, равных x или y

=СЧЁТЕСЛИ (диапазон;значение1) + СЧЁТЕСЛИ (диапазон;значение2)

Для того, чтобы подсчитать количество ячеек, равное либо одному значению, либо другому, можно использовать формулу, которая содержит функцию СЧЁТЕСЛИ дважды.

В примере, активная ячейка содержит эту формулу:

=СЧЁТЕСЛИ(B5:B10; «яблоки»)+СЧЁТЕСЛИ(B5:B10; «груши»)

СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые соответствуют критериям, входящие в комплект поставки. В этом случае критерием первого СЧЁТЕСЛИ является «яблоки» и критерием второго СЧЁТЕСЛИ является «груши». Первый СЧЁТЕСЛИ считает количество ячеек в B5: B10 равным «яблоки». Второй СЧЁТЕСЛИ считает количество ячеек в B5: B10 равным «груши». Эти два результата суммируются и сумма возвращается как результат формулы.

Обратите внимание, что текстовые значения в критериях СЧЁТЕСЛИ должны быть заключены в кавычки (» «). Также отметим, что СЧЁТЕСЛИ не чувствительна к регистру. В примере, будут учитываться слова «яблоки» и «груши» в любой комбинации букв верхнего и нижнего регистра.

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

=  СЧЁТЕСЛИ  (rng; 0) +  СЧЁТЕСЛИ  (rng; 1)

Количество ячеек, равных одному из многих значений

= СУММПРОИЗВ ( СЧЁТЕСЛИ (диапазон; значение))

Для того, чтобы подсчитать количество ячеек, равное одному из многих значений, вы можете использовать функцию СЧЁТЕСЛИ внутри СУММПРОИЗВ. В общей форме формулы (выше) rng представляет собой диапазон ячеек, и значение представляют значениями для подсчета.

В показанном примере, ячейка G4 содержит следующую формулу:

=СУММПРОИЗВ(СЧЁТЕСЛИ( B5:B10; D5:D7))

Примечание СЧЁТЕСЛИ не чувствительна к регистру.

СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые отвечают критериям. Когда вы даете СЧЁТЕСЛИ диапазон ячеек в качестве критериев, он возвращает массив чисел как результат, где каждое число представляет собой количество одной вещи в диапазоне. В этом случае диапазон ( D5:D7) содержит 3 значения, поэтому СЧЁТЕСЛИ возвращает 3 результаты в массиве как:

= СУММПРОИЗВ ({1;1;1})

так как значения «ручка», «пульт» и «собака» все появляются один раз в диапазоне B5:B10. Для обработки этого массива, мы используем функцию СУММПРОИЗВ, которая предназначена для работы с массивами. СУММПРОИЗВ просто суммирует элементы в массиве и возвращает результат, 3.

С массивом констант

С ограниченным числом значений, вы можете использовать константу массива в формуле с СУММ, как:

= СУММ (СЧЁТЕСЛИ (B5: B10, { «красный», «зеленый», «синий»}))

Но если вы используете ссылки на ячейки в критериях, вам необходимо ввести как формулу массива, либо перейти к СУММПРОИЗВ.

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

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

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

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

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

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

Формула без учета регистра.

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

= A1 = B1

Где A1 и B1 — объединяемые объекты. Результатом являются логические значения ИСТИНА и ЛОЖЬ.

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

= ЕСЛИ (LA2 ​​= B2; «Соответствует»; «НЕ СООТВЕТСТВУЕТ»)

Как видно на скриншоте ниже, обе формулы одинаково хорошо сравнивают текст, даты и числа:

Формула с учетом регистра.

В некоторых ситуациях может быть важно не только сравнить текст, но и различать верхний и нижний регистр. Сопоставление с учетом регистра может быть выполнено с помощью функции EXACT EXACT):

СОВЕТ (текст1; текст2)

Где text1 и text2 — две сравниваемые ячейки.

Предполагая, что ваши значения находятся в A2 и B2, расчет выглядит следующим образом:

= ТОЧНЫЙ (A2; B2)

Это вернет ИСТИНА для текстовых данных, которые точно соответствуют регистру каждого символа, в противном случае ЛОЖЬ.

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

= ЕСЛИ (ПОИСКПОЗ (A2; B2); «Соответствует»; «НЕ СООТВЕТСТВУЕТ»)

На следующем снимке экрана показаны результаты с учетом регистра:

Как сравнить несколько ячеек в Excel

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

Формула без учета регистра для более чем двух ячеек

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

= И (A2 = B2; A2 = C2)

или

= ЕСЛИ (И (LA2 ​​= B2; A2 = C2), «Соответствует»; «НЕ совпадает»)

Оператор AND возвращает TRUE, если все ячейки в таблице содержат одно и то же значение, FALSE, если одно значение отличается. Функция ЕСЛИ отображает введенные вами метки: «Соответствует» и «Не совпадает».

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

Сравниваем несколько ячеек с учетом регистра букв.

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

= И (ТОЧНЫЙ (A2; B2); ТОЧНЫЙ (A2; C2))

или

= ЕСЛИ (И (ПОИСКПОЗ (A2; B2); ТОЧНОЕ (A2; C2)); «Точное совпадение»; «НЕ совпадает»)

Как и в предыдущем примере, первая формула возвращает значения ИСТИНА и ЛОЖЬ, а вторая отображает собственные сообщения о совпадениях и различиях:

Если нам достаточно хотя бы двух совпадений значений, то сделаем это так:

= ЕСЛИ (OR (TRAVEL (A2; B2); MATCH (A2; C2); MATCH (B2; C2)); «Как минимум 2 точных совпадения»; «НЕ СОВПАДАЕТ»)

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

Но если в нашей таблице есть много ячеек, которые нужно проверить, приведенное выше выражение с условием ИЛИ станет очень громоздким. Следовательно, вы можете использовать функцию СЧЁТЕСЛИ.

Мы можем вычислить количество совпадений в нашем диапазоне ячеек, используя эту формулу массива:

{= СЧЁТЕСЛИ (A2: D2; A2: D2)}

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

= ЕСЛИ (СЧЁТЕСЛИ (A2: D2; A2: D2) = СЧЁТ (A2: D2), «Точное совпадение»;»»)

Как видите, к сожалению, здесь не рассматривается случай.

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

Сравните диапазон ячеек с образцом.

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

Совпадает ли ячейка с образцом?

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

LINES (диапазон) * NUMBERCOLUMN (диапазон) = COUNTIF (диапазон; ячейка модели)

В логическом тесте функции ЕСЛИ вы проверяете равенство двух чисел:

1. Общее количество ячеек в указанном диапазоне (количество строк, умноженное на количество столбцов) e
2. Количество ячеек, содержащих одно и то же значение шаблона (возвращаемое функцией СЧЁТЕСЛИ).

Предполагая, что образец текста находится в C2, а данные находятся в диапазоне A2: B6, формула выглядит следующим образом:

(СТРОКИ (A2: B6) * НОМЕР КОЛОНКИ (A2: B6)) = СЧЁТЕСЛИ (A2: B6; C2)

Чтобы сделать результаты более интуитивно понятными, то есть для отображения значимого текста вместо ИСТИНА и ЛОЖЬ, используйте функцию ЕСЛИ, как мы это делали в предыдущих примерах:

= ЕСЛИ ((СТРОКИ (A2: B6) * NUMBERCOLUMNS (A2: B6)) = COUNTIF (A2: B6, C2), «Все совпадает»; «Есть расхождения»)

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

Проверяем совпадение с образцом с учётом регистра букв.

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

IF ((ROWS (диапазон) * NUMBERCOLUMN (диапазон)) = (SUM (- MATCH (шаблон; диапазон))), «text_if_matches»; «text_if_NOT_matches»)

Поскольку исходный диапазон находится в области A2: B6, а образец текста находится в области C2, мы получаем следующее:

{= IF ((LINES (B2: C6) * NUMBERCOLUMNS (B2: C6)) = (SUM (- MATCH (C2; B2: C6))), «Все совпадения»; «НЕ совпадает»)}

В отличие от обычных формул Excel, формулы массива заполняются нажатием Ctrl + Shift + Enter. При правильном вводе Excel заключает формулу массива в {фигурные скобки}, как показано ниже:

Как соотнести две ячейки по длине.

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

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

= DLSTR (A2) = DLSTR (B2)

или

= ЕСЛИ (DLSTR (A2) = DLSTR (B2), «Одинаковая длина», «Различная длина»)

Как вы понимаете, первый возвращает логические значения ИСТИНА или ЛОЖЬ, а второй возвращает ваши результаты:

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

Примечание. Если две явно идентичные текстовые ячейки возвращают разную длину, проблема, скорее всего, связана с начальными или конечными пробелами в одной или обоих. В этом случае удалите лишние пробелы с помощью функции ОБРЕЗАТЬ. Подробные объяснения и примеры можно найти здесь: Как удалить пробелы в Excel .

Сравниваем две ячейки по вхождению определенного символа

Это последний пример в нашем Руководстве по сравнению ячеек Excel, и он показывает решение довольно конкретной задачи. Допустим, у вас есть 2 столбца текстовых значений, которые содержат важный для вас символ. Ваша цель — проверить, содержат ли две ячейки в каждой строке одинаковое количество вхождений данного символа.

Чтобы было понятнее, рассмотрим следующий пример. Допустим, у вас есть два списка отправленных заказов (столбец B) и полученных заказов (столбец C). Каждая запись в таблице содержит заказы на определенный продукт, уникальный идентификатор которого включен во все идентификаторы заказа и указан в той же строке в столбце A (см. Снимок экрана ниже). Вы хотите убедиться, что каждая строка содержит равное количество отправленных и полученных элементов с этим конкретным идентификатором.

Чтобы решить эту проблему, поступим следующим образом:

• Сначала замените код заказа ничем с помощью функции ЗАМЕНИТЬ:

ЗАМЕНА (A1; код;»»)

• Таким образом, он считает, сколько раз код появляется в каждой ячейке. Для этого получите длину его содержимого без кода и вычтите ее из общей длины исходной ячейки. Это нужно делать отдельно для ячеек 1 и 2, например:

DLSTR (B2) -LSTR (ПОДСТАВИТЬ (B2; $ A2;»»))

а также

DLSTR (C2) -LSTR (ПОДСТАВИТЬ (C2, $ A2;»»))

• Теперь идентифицируйте эти 2 числа, вставив знак равенства (=) между указанными выше частями.

DLSTR (B2) -LSTR (ПОДСТАВИТЬ (B2, $ A2; «»)) = DLSTR (C2) -LSTR (ПОДСТАВИТЬ (C2, $ A2;»»))

В нашем примере код находится в A2, а значения — в B2 и C2. 

Выражение возвращает TRUE, если B2 и C2 содержат такое же количество вхождений символа, что и A2, в противном случае — FALSE. Чтобы сделать результаты более понятными для ваших пользователей, вы можете встроить его в функцию SE:

= IF (DLSTR (B2) -LSTR (SUBSTITUTE (B2, $ A2; «»)) = DLSTR (C2) -LSTR (SUBSTITUTE (C2, $ A2; «»)), «Соответствует»; «НЕ совпадает»)

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

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

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

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

1 Сравнение с помощью простого поиска 

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

1. Перейти на главную вкладку табличного процессора. 
2. В группе «Редактирование» выбрать пункт поиска. 
3. Выделить столбец, в котором будет выполняться поиск совпадений — например, второй. 
4. Вручную задавать значения из основного столбца (в данном случае — первого) и искать совпадения. 

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

Поиск
одинаковых значений в Excel

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

На
рисунке – списки писателей. Алгоритм
действий следующий:

• Выбрать
  ячейку I3
  с записью «С. А. Есенин».
• Поставить
  задачу – выделить цветом ячейки с
  такими же записями.
• Выделить
  область поисков.
• Нажать
  вкладку «Главная».
• Далее
  группа «Стили».
• Затем
  «Условное форматирование»;
• Нажать
  команду «Равно».

• Появится
  диалоговое окно:

• В
  левом поле указать ячейку с I2,
  в которой записано «С. А. Есенин».
• В
  правом поле можно выбрать цвет шрифта.
• Нажать
  «ОК».

В
таблицах отмечены цветом ячейки, значение
которых равно заданному.

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

Ищем в таблицах Excel
все повторяющиеся значения

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

• Зайти
  в группу «Стили».
• Далее
  «Условное форматирование».
• Теперь
  в выпадающем меню выбрать «Правила
  выделения ячеек».
• Затем
  «Повторяющиеся значения».

• Появится
  диалоговое окно:

• Нажать
  «ОК».

Программа
ищет повторения во всех столбцах.

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

Удаление одинаковых значений
из таблицы Excel

Способ
удаления неуникальных записей:

1. Зайти
   во вкладку «Данные».
2. Выделить
   столбец, в котором следует искать
   дублирующиеся строки.
3. Опция
   «Удалить дубликаты».

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

Список
с уникальными значениями:

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

Расширенный
фильтр – это инструмент для получения
упорядоченного списка с уникальными
записями.

• Выбрать
  вкладку «Данные».
• Перейти
  в раздел «Сортировка и фильтр».
• Нажать
  команду «Дополнительно»:

• В
  появившемся диалоговом окне ставим
  флажок «Только уникальные записи».
• Нажать
  «OK»
  – уникальный список готов.

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

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

Вкладка
«Вставка».

Пункт
«Сводная таблица».

В
диалоговом окне выбрать размещение
сводной таблицы на новом листе.

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

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

Методы сравнения таблиц в Microsoft Excel

​Смотрите также​: счетесли() не поможет?​Guest​ значение =$F5 (обратите​ Delete — Delete​ОК​ противном случае все​ перед ними знак​ нумерацией, который мы​«Значение если истина»​СЧЁТЕСЛИ​ значений.​ данный вариант от​ блоке​ групп ячеек. С​«Математические»​ которых размещены фамилии.​Довольно часто перед пользователями​Или можно UDF​: В моем случае,​ внимание, что в​ Rows)​.Результат: Команд «Denver Broncos»,​ значения второго столбца​ доллара уже ранее​ недавно добавили. Адрес​получилось следующее выражение:​

​, и после преобразования​Аргумент​

​ ранее описанных.​«Стили»​ его помощью также​

Способы сравнения

​выделяем наименование​Для этого нам понадобится​ Excel стоит задача​ написать — будет​ значение из «I3″​ строке не абсолютная​

• ​и т.д.​ «Arizona Cardinals», «Minnesota​
• ​ будут сравниваться только​ описанным нами способом.​
• ​ оставляем относительным. Щелкаем​СТРОКА(D2)​

​ его в маркер​«Критерий»​Производим выделение областей, которые​. Из выпадающего списка​ можно сравнивать только​СУММПРОИЗВ​ дополнительный столбец на​ сравнения двух таблиц​ шерстить usedrange всех​ ищется в диапазоне​ ссылка)​

​Если списки разного размера​ Vikings» и «Pittsburgh​ со значением из​Жмем на кнопку​ по кнопке​Теперь оператор​

Способ 1: простая формула

​ заполнения зажимаем левую​задает условие совпадения.​ нужно сравнить.​ переходим по пункту​ синхронизированные и упорядоченные​. Щелкаем по кнопке​ листе. Вписываем туда​ или списков для​ листов и считать​ «E4:E5», если совпадает,​jordann​ и не отсортированы​ Steelers» нет в​ ячейки​«OK»​«OK»​СТРОКА​ кнопку мыши и​ В нашем случае​Выполняем переход во вкладку​«Управление правилами»​ списки. Кроме того,​«OK»​ знак​ выявления в них​ повторы.​ то идет сравнение​

​: МВТ​ (элементы идут в​ первом списке.​А6​.​.​будет сообщать функции​ тянем курсор вниз.​

1. ​ он будет представлять​ под названием​.​ в этом случае​​.​​«=»​ отличий или недостающих​dr.ZmeY​ ячейки «G4» со​Да, все получилось,​​ разном порядке), то​​Урок подготовлен для Вас​.​После вывода результат на​Оператор выводит результат –​ЕСЛИ​Как видим, программа произвела​

   ​ собой координаты конкретных​

   

   ​«Главная»​Активируется окошко диспетчера правил.​ списки должны располагаться​Активируется окно аргументов функции​

2. ​. Затем кликаем по​​ элементов. Каждый юзер​​: счетесли() не поможет…​ значением ячейки столбца​ спасибо !​ придется идти другим​ командой сайта office-guru.ru​​Аналогично создайте правило для​​ экран протягиваем функцию​ число​

   

3. ​номер строки, в​ вычисление совпадений, сравнив​ ячеек первой табличной​. Делаем щелчок по​ Жмем в нем​ рядом друг с​СУММПРОИЗВ​ первому наименованию, которое​ справляется с этой​ ((( Число страниц​ «B» найденной строки.​dmitryt​ путем.​

   ​Источник: http://www.excel-easy.com/examples/compare-two-lists.html​ значений, которые больше.​ с помощью маркера​3​ которой расположена конкретная​ каждую ячейку первой​ области.​​ кнопке​​ на кнопку​ другом на одном​, главной задачей которой​ нужно сравнить в​ задачей по своему,​ может быть оооочень​ Если оно тоже​: Здравствуйте.​Самое простое и быстрое​Перевел: Антон Андронов​

   

4. ​Этот урок описывает, как​ заполнения до конца​. Именно оно наименьшее​ фамилия, и в​ таблицы с данными,​Выделяем первый элемент дополнительного​«Условное форматирование»​«Создать правило»​ листе.​ является вычисление суммы​ первом списке. Опять​​ но чаще всего​​ большое, ексель -​ совпадает, то в​Как сравнить ячейку​ решение: включить цветовое​​Автор: Антон Андронов​​ сравнить два списка​

   

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

   

6. ​ на решение указанного​​ захлебнётся.. Хотя… если​​ ячейке «K4» указывается​​ А1 с диапазоном​​ выделение отличий, используя​​Типовая задача, возникающая периодически​​ в Excel с​​ видим, обе фамилии,​​ строк табличных массивов.​

   

7. ​ заданное в первом​​ втором табличном диапазоне.​​ будет производиться подсчет​ выбираем позицию​В запустившемся окне производим​ во вкладку​ Но данную функцию​«=»​ вопроса тратится довольно​ через ДВССЫЛ, и​

   ​ значение ячейки "D"​

   ​ С0:С25, и если​ условное форматирование. Выделите​ перед каждым пользователем​ помощью условного форматирования.​ которые присутствуют во​ С помощью маркера​ поле, будет выполняться,​ В четырех случаях​ количества совпадений. Далее​

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

   ​ для наших целей.​

   ​ кликаем по первой​​ так как далеко​​ листов… Не знаю,​

   

8. ​ в ячейке «L4″​ в ячейку В1​ данными и выберите​ между собой два​ два списка команд​​ отсутствуют в первой,​​ до самого низа.​ЕСЛИ​«1»​«Вставить функцию»​ делаем выбор позиции​. В поле​ значку​ Синтаксис у неё​​ ячейке колонки, которую​​ не все подходы​

​ будет ли такая​ значение из ячейки​ вывести значение ячейки​ на вкладке​ диапазона с данными​ НФЛ (Национальная футбольная​ выведены в отдельный​Теперь, зная номера строк​будет выводить этот​, а в двух​.​«Повторяющиеся значения»​«Форматировать ячейки»​«Найти и выделить»​ довольно простой:​ мы сравниваем, во​ к данной проблеме​ конструкция работать, т.к.​

​ столбца "С".​

​ Dx?​Главная — Условное форматирование​ и найти различия​ лига).​ диапазон.​ несовпадающих элементов, мы​ номер в ячейку.​ случаях –​

Способ 2: выделение групп ячеек

​Происходит запуск​.​записываем формулу, содержащую​, который располагается на​=СУММПРОИЗВ(массив1;массив2;…)​ второй таблице. Получилось​ являются рациональными. В​ он не будет​vikttur​Условно говоря если​ — Правила выделения​ между ними. Способ​

1. ​Чтобы выделить команды в​При сравнении диапазонов в​​ можем вставить в​​ Жмем на кнопку​«0»​​Мастера функций​​Запускается окно настройки выделения​ адреса первых ячеек​ ленте в блоке​​Всего в качестве аргументов​​ выражение следующего типа:​ то же время,​ охватывать сразу весь​​: =ЕСЛИ(ЕЧИСЛО(ПОИСКПОЗ(I4;$E$4:$E$10;));ЕСЛИ(G4=B4;D4;»»);»»)​​ содержимое ячейки А1​

   

   ​ ячеек — Повторяющиеся​ решения, в данном​ первом списке (не​ разных книгах можно​ ячейку и их​«OK»​. То есть, программа​. Переходим в категорию​ повторяющихся значений. Если​ диапазонов сравниваемых столбцов,​ инструментов​​ можно использовать адреса​​=A2=D2​ существует несколько проверенных​ диапазон листов, а​Zombarian​ совпало с содержимом​​ значения (Home -​​ случае, определяется типом​

2. ​ во втором!), выполните​ использовать перечисленные выше​​ значения с помощью​​.​ не смогла отыскать​

   

3. ​«Статистические»​ вы все сделали​ разделенные знаком «не​«Редактирование»​ до 255 массивов.​Хотя, конечно, в каждом​ алгоритмов действий, которые​​ перебирать их по​​: Имеется ввиду, если​​ ячейки C15, то​​ Conditional formatting -​

   

4. ​ исходных данных.​ следующие действия:​ способы, исключая те​ функции​Как видим, первый результат​ во второй таблице​. Находим в перечне​ правильно, то в​ равно» (​. Открывается список, в​ Но в нашем​

Способ 3: условное форматирование

​ конкретном случае координаты​ позволят сравнить списки​ очереди, а Эксель​ при первом условии,​ в ячейку В1​ Highlight cell rules​Если списки синхронизированы (отсортированы),​Сперва выделите диапазон​ варианты, где требуется​

1. ​ИНДЕКС​ отображается, как​ два значения, которые​ наименование​ данном окне остается​<>​ котором следует выбрать​ случае мы будем​ будут отличаться, но​ или табличные массивы​​ очень упрямый с​​ значение нашлось, например,​​ подставляем значение из​​ — Duplicate Values)​ то все делается​A1:A18​​ размещение обоих табличных​​. Выделяем первый элемент​«ЛОЖЬ»​​ имеются в первом​​«СЧЁТЕСЛИ»​

   

2. ​ только нажать на​). Только перед данным​ позицию​​ использовать всего два​​ суть останется одинаковой.​

   

3. ​ в довольно сжатые​ циклами… Т.е. дело​​ в ячейке E7,​​ ячейки D15.​​:​​ весьма несложно, т.к.​и дайте ему​ областей на одном​ листа, содержащий формулу​. Это означает, что​​ табличном массиве.​​. После его выделения​ кнопку​ выражением на этот​«Выделение группы ячеек…»​​ массива, к тому​​Щелкаем по клавише​ сроки с минимальной​ тут не столько​ то и ячейка​Спасибо.​Если выбрать опцию​ надо, по сути,​ имя «firstList».​​ листе. Главное условие​​НАИМЕНЬШИЙ​ значение не удовлетворяет​Конечно, данное выражение для​ щелкаем по кнопке​«OK»​ раз будет стоять​.​ же, как один​

   ​Enter​

   ​ затратой усилий. Давайте​ в «сравнить», сколько​ G4 должна сравниваться​Pelena​​Повторяющиеся​​ сравнить значения в​

   

4. ​Затем выделите диапазон​​ для проведения процедуры​​. После этого переходим​​ условиям оператора​​ того, чтобы сравнить​«OK»​. Хотя при желании​ знак​Кроме того, в нужное​ аргумент.​, чтобы получить результаты​​ подробно рассмотрим данные​​ посчитать. Вероятно без​

   

5. ​ с ячейкой B7,​: Здравствуйте.​, то Excel выделит​​ соседних ячейках каждой​​B1:B20​

   

6. ​ сравнения в этом​ в строку формул​​ЕСЛИ​​ табличные показатели, можно​​.​​ в соответствующем поле​

   

7. ​«=»​ нам окно выделения​Ставим курсор в поле​ сравнения. Как видим,​ варианты.​ скриптов не обойтись…​

​ и, в случае​Читаем про функцию​ цветом совпадения в​ строки. Как самый​и назовите его​ случае – это​ и перед наименованием​. То есть, первая​ применять и в​Происходит запуск окна аргументов​ данного окошка можно​. Кроме того, ко​ группы ячеек можно​«Массив1»​ при сравнении первых​Скачать последнюю версию​

1. ​kim​ совпадения, отображаться значение​

   

2. ​ ВПР()​ наших списках, если​​ простой вариант -​​ «secondList».​ открытие окон обоих​​«НАИМЕНЬШИЙ»​​ фамилия присутствует в​ существующем виде, но​​ оператора​​ выбрать другой цвет​ всем к координатам​​ попасть и другим​​и выделяем на​

   

3. ​ ячеек обоих списков​ Excel​: Работать будет, но​ ячейки D7.​Если не получится​ опцию​ используем формулу для​​Теперь выделите диапазон​​ файлов одновременно. Для​дописываем название​ обоих списках.​ есть возможность его​СЧЁТЕСЛИ​

   

4. ​ выделения.​ столбцов в данной​ способом. Данный вариант​ листе сравниваемый диапазон​ программа указала показатель​Читайте также: Сравнение двух​ медленно.​vikttur​ применить, прикладывайте файл​Уникальные​ сравнения значений, выдающую​A1:A18​ версий Excel 2013​

​«ИНДЕКС»​С помощью маркера заполнения,​ усовершенствовать.​. Как видим, наименования​После того, как мы​ формуле нужно применить​ особенно будет полезен​ данных в первой​«ИСТИНА»​ документов в MS​Какое предполагаемое максимальное​: …ЕСЛИ(G4=ИНДЕКС($B$4:$B$10;ПОИСКПОЗ(I4;$E$4:$E$10;));…​​ с примером в​​- различия.​​ на выходе логические​​.​ и позже, а​​без кавычек, тут​​ уже привычным способом​

​Сделаем так, чтобы те​ полей в этом​ произведем указанное действие,​

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

Способ 4: комплексная формула

​ области. После этого​, что означает совпадение​ Word​ количество листов и​​Zombarian​​ соответствии с Правилами​Цветовое выделение, однако, не​ значения​На вкладке​ также для версий​ же открываем скобку​ копируем выражение оператора​

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

​ форума​

​ всегда удобно, особенно​​ИСТИНА (TRUE)​​Главная​ до Excel 2007​ и ставим точку​ЕСЛИ​

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

1. ​ на каждом листе?​ 0.​dmitryt​ для больших таблиц.​или​​(Home) выберите команду​​ с выполнением этого​

   

2. ​ с запятой (​​на весь столбец.​​ но отсутствуют в​​Устанавливаем курсор в поле​​ цветом. Те элементы,​ жмем на клавишу​​ 2007, так как​​«не равно»​ аналогичную операцию и​​ в Excel, но​​Guest​

   

3. ​vikttur​: Спасибо огромное, все​​ Также, если внутри​​ЛОЖЬ (FALSE)​Условное форматирование​ условия нет никаких​;​

   ​ Как видим, по​​ первой, выводились отдельным​​«Диапазон»​ которые не совпадают,​F4​ метод через кнопку​(​ с остальными ячейками​ все их можно​: При активации листа​: Я Вам показал​ получилось.​ самих списков элементы​:​>​ проблем. Но в​). Затем выделяем в​​ двум позициям, которые​​ списком.​

   ​. После этого, зажав​ останутся окрашенными в​. Как видим, около​«Найти и выделить»​

   ​<>​​ обеих таблиц в​​ разделить на три​ ‘ЭТАЛОН’ :​ только часть формулы,​ЕвгенийОрск​ могут повторяться, то​Число несовпадений можно посчитать​Создать правило​ Excel 2007 и​ строке формул наименование​ присутствуют во второй​​Прежде всего, немного переработаем​​ левую кнопку мыши,​

   

4. ​ свой изначальный цвет​ всех адресов столбцов​эти приложения не​​) и выделяем сравниваемый​​ тех колонках, которые​ большие группы:​Guest​​ поиск в столбце​​: Необходимо сравнить значение​ этот способ не​ формулой:​(Conditional Formatting >​

   

5. ​ Excel 2010 для​«ИНДЕКС»​ таблице, но отсутствуют​ нашу формулу​ выделяем все значения​ (по умолчанию белый).​ появился знак доллара,​ поддерживают. Выделяем массивы,​ диапазон второй области.​ мы сравниваем. Но​сравнение списков, находящихся на​: Не посмотрел на​​ В, в надежде,​​ ячейки А1 со​ подойдет.​=СУММПРОИЗВ(—(A2:A20<>B2:B20))​ New rule).​ того, чтобы открыть​

   

6. ​и кликаем по​ в первой, формула​СЧЁТЕСЛИ​ столбца с фамилиями​ Таким образом, можно​ что и означает​ которые желаем сравнить,​ Далее обворачиваем полученное​​ можно просто провести​​ одном листе;​ размер файла (не​​ что вы сами​​ значениями ячеек В1:В20​В качестве альтернативы можно​или в английском варианте​Выберите​ оба окна одновременно,​ пиктограмме​

​ выдает номера строк.​, а именно сделаем​ второй таблицы. Как​ сразу визуально увидеть,​ превращение ссылок в​ и жмем на​ выражение скобками, перед​

​ копирование формулы, что​сравнение таблиц, расположенных на​ прикрепился)​ попробуете разобраться и​ и в зависимости​ использовать функцию​

1. ​ =SUMPRODUCT(—(A2:A20<>B2:B20))​Использовать формулу для определения​​ требуется провести дополнительные​​«Вставить функцию»​Отступаем от табличной области​ её одним из​​ видим, координаты тут​​ в чем отличие​ абсолютные. Для нашего​ клавишу​​ которыми ставим два​​ позволит существенно сэкономить​ разных листах;​Guest​​ дополнить формулу поиском​​ от результата присвоить​СЧЁТЕСЛИ​Если в результате получаем​ форматируемых ячеек​ манипуляции. Как это​.​​ вправо и заполняем​​ аргументов оператора​ же попадают в​​ между массивами.​​ конкретного случая формула​

   

2. ​F5​​ знака​​ время. Особенно данный​сравнение табличных диапазонов в​: Вот:​​ в столбце D.​​ значение ячейки С1.​(COUNTIF)​ ноль — списки​(Use a formula​ сделать рассказывается в​После этого открывается небольшое​​ колонку номерами по​​ЕСЛИ​

   ​ указанное поле. Но​При желании можно, наоборот,​​ примет следующий вид:​​.​«-»​ фактор важен при​​ разных файлах.​​dr.ZmeY​​…ЕСЛИ(G4=ИНДЕКС($B$4:$B$10;ПОИСКПОЗ(I4;$E$4:$E$10;));…​​ Т.е. если значение​из категории​ идентичны. В противном​ to determine which​ отдельном уроке.​ окошко, в котором​ порядку, начиная от​. Для этого выделяем​ для наших целей​​ окрасить несовпадающие элементы,​​=$A2<>$D2​

   ​Активируется небольшое окошко перехода.​

   ​. В нашем случае​​ сравнивании списков с​​Именно исходя из этой​​: Количество листов может​​т.е. проверили уже​ ячеки А1 совпадает​Статистические​ случае — в​ cells to format).​Урок: Как открыть Эксель​ нужно определить, ссылочный​​1​​ первую ячейку, в​ следует сделать данный​ а те показатели,​​Данное выражение мы и​​ Щелкаем по кнопке​

   

3. ​ получилось такое выражение:​ большим количеством строк.​​ классификации, прежде всего,​​ быть разное.. они​ предыдущим ЕСЛИ, что​ с одним из​​, которая подсчитывает сколько​​ них есть различия.​Введите следующую формулу:​ в разных окнах​

   

4. ​ вид должна иметь​. Количество номеров должно​ которой расположен оператор​​ адрес абсолютным. Для​​ которые совпадают, оставить​ записываем в вышеуказанное​«Выделить…»​—(A2:A7<>D2:D7)​Процедуру копирования легче всего​ подбираются методы сравнения,​ генерируются по условию,​

   

5. ​ I4 есть в​ значений В1:В10, то​ раз каждый элемент​ Формулу надо вводить​​=СЧЕТЕСЛИ(secondList;А1)=0​​Как видим, существует целый​ функция​ совпадать с количеством​СЧЁТЕСЛИ​ этого выделяем данные​ с заливкой прежним​ поле. После этого​

   

6. ​в его нижнем​Щелкаем по кнопке​ выполнить при помощи​ а также определяются​ и число строк​​ диапазоне $E$4:$E$10 и​​ в С1 одно​

   

7. ​ из второго списка​​ как формулу массива,​​=COUNTIF(secondList,А1)=0​​ ряд возможностей сравнить​​ИНДЕКС​ строк во второй​​. В строке формул​​ координаты в поле​​ цветом. При этом​​ щёлкаем по кнопке​

   

8. ​ левом углу.​​«OK»​​ маркера заполнения. Наводим​ конкретные действия и​ так же генерируется​ теперь сравниваем G4​ значение, если А1​

   ​ встречался в первом:​​ т.е. после ввода​​Задайте стиль форматирования и​ таблицы между собой.​​или предназначенный для​​ сравниваемой таблице. Чтобы​ перед ней дописываем​ и жмем на​​ алгоритм действий практически​​«Формат…»​После этого, какой бы​

   ​.​​ курсор на правый​​ алгоритмы для выполнения​ по условию, но,​ с данными ячейки​ совпадает с В11:В20​Полученный в результате ноль​ формулы в ячейку​ нажмите​ Какой именно вариант​ работы с массивами.​​ ускорить процедуру нумерации,​​ выражение​

   

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

   

10. ​ то в С1​ и говорит об​ жать не на​ОК​ использовать зависит от​ Нам нужен второй​​ можно также воспользоваться​​«ЕСЛИ»​F4​​ в окне настройки​​Активируется окно​ вариантов вы не​ выводит результат. Как​​ где мы получили​​ проведении сравнения в​​ 365 (строк)…​​ (ИНДЕКС($B$4:$B$10;…) с строке,​ другое значение. ВАЖНО!!!​ отличиях.​Enter​​.​​ того, где именно​ вариант. Он установлен​​ маркером заполнения.​​без кавычек и​.​​ выделения повторяющихся значений​​«Формат ячеек»​

    

11. ​ избрали, запускается окно​ видим, в нашем​ показатель​ разных книгах требуется​dr.ZmeY​​ где находится близнец​​ данное задание необходимо​И, наконец, «высший пилотаж»​, а на​Результат: Команд «Miami Dolphins»​ расположены табличные данные​ по умолчанию, так​После этого выделяем первую​ открываем скобку. Далее,​​Как видим, ссылка приняла​​ в первом поле​

    

12. ​. Идем во вкладку​​ выделения групп ячеек.​​ случае результат равен​«ИСТИНА»​ одновременно открыть два​: хмммм… При попытке​ I4 (ПОИСКПОЗ(I4;$E$4:$E$10;)).​

    ​ выполнить без использования​​ — можно вывести​​Ctrl+Shift+Enter​ и «Tennessee Titans»​​ относительно друг друга​​ что в данном​ ячейку справа от​ чтобы нам легче​ абсолютную форму, что​ вместо параметра​«Заливка»​ Устанавливаем переключатель в​ числу​. При этом он​ файла Excel.​ сделать ещё один​Аналогично для поиска​ макросов. Помогите плиз​​ отличия отдельным списком.​​.​​ нет во втором​​ (на одном листе,​

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

    ​ лист, ексель завис​​ по диапазону D.​​ уже всю голову​

    

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

Способ 5: сравнение массивов в разных книгах

​, то есть, это​ черный крестик. Это​ что сравнивать табличные​ :(((​Zombarian​ сломал :(​ использовать формулу массива:​ надо что сделать,​Пояснение:​ на разных листах),​«OK»​ значку​ значение​Затем переходим к полю​«Уникальные»​ на цвете, которым​. Жмем по кнопке​ означает, что в​ и есть маркер​ области имеет смысл​Guest​: vikttur, спасибо Вам​MCH​Выглядит страшновато, но свою​ то подойдет другой​Формула =СЧЕТЕСЛИ (secondList;А1) подсчитывает​ а также от​.​

​«Вставить функцию»​«ЕСЛИ»​

​«Критерий»​. После этого нажать​ хотим окрашивать те​«OK»​ сравниваемых списках было​ заполнения. Жмем левую​ только тогда, когда​: Так эта, …​ за помощь, разобрался!​: =ЕСЛИ(ЕЧИСЛО(ПОИСКПОЗ(A1;B1:B10;0));»одно значение»;ЕСЛИ(ЕЧИСЛО(ПОИСКПОЗ(A1;B11:B20;0));»другое значение»;»значение​ работу выполняет отлично​ быстрый способ: выделите​ количество команд во​ того, как именно​Запускается окно аргументов функции​.​

​и жмем по​

lumpics.ru

Сравнение числовых столбцов

​, установив туда курсор.​ на кнопку​ элементы, где данные​.​ найдено одно несовпадение.​ кнопку мыши и​ они имеют похожую​ а чем Вы​dr.ZmeY​ не найдено»))​ ;)​

​ оба столбца и​ втором списке, которые​ пользователь желает, чтобы​ИНДЕКС​Открывается​

​ иконке​ Щелкаем по первому​«OK»​ не будут совпадать.​Как видим, после этого​

​ Если бы списки​ тянем курсор вниз​ структуру.​ его делали ?​: Похожая проблемка…​Юрий М​jordann​ нажмите клавишу​ идентичны команде из​ это сравнение выводилось​. Данный оператор предназначен​Мастер функций​«Вставить функцию»​ элементу с фамилиями​.​ Жмем на кнопку​​ несовпадающие значения строк​​ были полностью идентичными,​

​ на количество строчек​Самый простой способ сравнения​

excel2.ru

Сравнение двух списков в Excel с помощью условного форматирования

​dr.ZmeY​Есть эталонный массив​: =ЕСЛИ(СЧЁТЕСЛИ(B1:B20;A1)>0;»Одно значение»;»Другое значение»)​: Всем привет !​F5​ ячейки​ на экран.​ для вывода значения,​

​. Переходим в категорию​.​ в первом табличном​Таким образом, будут выделены​

1. ​«OK»​​ будут подсвечены отличающимся​​ то результат бы​ в сравниваемых табличных​
2. ​ данных в двух​​: Хмммм… не получается​​ (столбец) на ЛИСТ1,​Это для первой​
3. ​Возникла такая задача.​​, затем в открывшемся​​A1​
4. ​Автор: Максим Тютюшев​​ которое расположено в​​«Статистические»​​Открывается окно аргументов функции​​ диапазоне. В данном​​ именно те показатели,​​.​ оттенком. Кроме того,​
5. ​ был равен числу​​ массивах.​ таблицах – это​​ прикрутить скрипт… :(((​ Есть массивы на​ части вопроса :-)​
6. ​Нужно построчно сравнить​

   ​ окне кнопку​
​. Если СЧЕТЕСЛИ(secondList;A1)=0, значит​

7. ​Сравним 2 столбца значений.​ определенном массиве в​​и производим выбор​​ЕСЛИ​​ случае оставляем ссылку​ которые не совпадают.​Вернувшись в окно создания​ как можно судить​

   

   ​«0»​

   • ​Как видим, теперь в​ использование простой формулы​Форум не поддерживает​ листах ЛИСТ2, ЛИСТ3​MCH​​ значения между двумя​​Выделить (Special)​ команды из ячейки​​ Сравнение будем производить​​ указанной строке.​ наименования​. Как видим, первое​ относительной. После того,​
   • ​Урок: Условное форматирование в​ правила форматирования, жмем​​ из содержимого строки​​.​ дополнительном столбце отобразились​ равенства. Если данные​ большие файлы, тогда​
   • ​ и т.д….​​: =ВЫБОР((ПОИСКПОЗ(A1;B1:B20;0)-1)/10+1;»одно значение»;»другое значение»)​​ столбцами и выбрать​-​

     ​A1​

8. ​ построчно: если значение​Как видим, поле​«НАИМЕНЬШИЙ»​ поле окна уже​​ как она отобразилась​​ Экселе​ на кнопку​ формул, программа сделает​

   ​Таким же образом можно​
​ все результаты сравнения​

9. ​ совпадают, то она​ залью и дам​Рядом с эталонным​​=ПРОСМОТР(ПОИСКПОЗ(A1;B1:B20;0);{1;11};{«одно значение»;»другое значение»})​​ в третий столбец​Отличия по строкам (Row​нет во втором​ во втором столбце​«Номер строки»​

   

​. Щелкаем по кнопке​ заполнено значением оператора​
​ в поле, можно​
​Также сравнить данные можно​

​«OK»​

office-guru.ru

Поиск отличий в двух списках

​ активной одну из​ производить сравнение данных​ данных в двух​ выдает показатель ИСТИНА,​ ссылку:​ массивом нужно сделать​Если значение не​ наибольшее значение.​ differences)​ списке. В результате​

Вариант 1. Синхронные списки

​ больше, чем в​уже заполнено значениями​«OK»​СЧЁТЕСЛИ​ щелкать по кнопке​ при помощи сложной​.​ ячеек, находящуюся в​ в таблицах, которые​ колонках табличных массивов.​ а если нет,​В файле есть​​ массив чисел, который​​ найдено, то будет​​При этом, те​​. В последних версиях​

​ Excel окрашивает ячейку​ первом, то оно​

​ функции​

​.​. Но нам нужно​

​«OK»​ формулы, основой которой​После автоматического перемещения в​ указанных не совпавших​ расположены на разных​ В нашем случае​ то – ЛОЖЬ.​ эталонный столбец I​ будет делать следующее:​ ошибка #Н/Д​​ значения что окажутся​​ Excel 2007/2010 можно​​ голубым цветом.​​ будет выделено красным,​

​НАИМЕНЬШИЙ​Функция​ дописать кое-что ещё​.​ является функция​ окно​​ строках.​​ листах. Но в​ не совпали данные​​ Сравнивать можно, как​​ на листе «График».​​Если какая-то ячейка​Владимир​​ больше в столбцах​ также воспользоваться кнопкой​Так как мы выбрали​​ если меньше -​. От уже существующего​НАИМЕНЬШИЙ​ в это поле.​​В элемент листа выводится​​СЧЁТЕСЛИ​

​«Диспетчера правил»​Произвести сравнение можно, применив​ этом случае желательно,​ только в одной​

• ​ числовые данные, так​ В столбец J​
• ​ в эталоне совпадает​​: =ЕСЛИ(ИНДЕКС(ЧАСТОТА(B1:B20;A1);1)​
• ​ при сравнении, должны​Найти и выделить (Find​ диапазон​​ то зеленым. Выделять​
• ​ там значения следует​, окно аргументов которой​ Устанавливаем туда курсор​​ результат. Он равен​. С помощью данного​щелкаем по кнопке​ метод условного форматирования.​ чтобы строки в​
• ​ строке. При их​

Вариант 2. Перемешанные списки

​ и текстовые. Недостаток​ выводятся значения суммы​ с ячейкой на​МСН научил, за​ быть выделены.​ & Select) -​

​A1:A18​ ячейки будем Условным​ отнять разность между​ было раскрыто, предназначена​ и к уже​ числу​ инструмента можно произвести​​«OK»​ Как и в​ них были пронумерованы.​ сравнении формула выдала​ данного способа состоит​ повторений дат, напротив​ одном из листов,​​ что ему низкий​

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

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

​ поклон.​ правильно это сделать​​ (Go to Special)​ ​ условное форматирование, Excel​​Пусть имеется 2 столбца​​ и внутренней нумерацией​​ по счету наименьшего​«=0»​. Это означает, что​ каждый элемент из​

​Теперь во второй таблице​ области должны находиться​ сравнения практически точно​

​«ЛОЖЬ»​ ним можно пользоваться​Контрольные массивы формируются​ соседней с эталонной​:)​

​ ?​на вкладке​ автоматически скопирует формулы​

planetaexcel.ru

Как сравнить значения в ячейках и выбрать большее (Формулы/Formulas)

​ с числами. Сравним​​ табличной области. Как​
​ значения.​
​без кавычек.​ в перечне имен​ выбранного столбца второй​ элементы, которые имеют​ на одном рабочем​
​ такая, как была​. По всем остальным​ только в том​ исходя из данных​ ячейкой увеличивается на​
​vikttur​МВТ​Главная (Home)​

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

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

​ 1, если не​​: Автор продолжает просить…​: Как-то так​Excel выделит ячейки, отличающиеся​Таким образом, ячейка​ с первым (см.​ значениями у нас​«Массив»​ полю​«Гринев В. П.»​

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

excelworld.ru

Сравнение ячейки со столбцом и вывод результата (Формулы/Formulas)

​ формула сравнения выдала​​ в таблице упорядочены​
​ согласно дата начала​ совпадает, то остаётся​Serge​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=МАКС(D5:E5)​ содержанием (по строкам).​A2​ файл примера).​
​ только шапка. Это​следует указать координаты​«Значение если истина»​, которая является первой​Оператор​ табличной области, будут​ собой.​
​ при внесении формулы​

​ показатель​​ или отсортированы одинаково,​
​ — столбец B.​ прежним.​
​: Ага.​jordann​ Затем их можно​содержит следующую формулу:=СЧЕТЕСЛИ(secondList;А2)=0,ячейка​Выделите все значения из​

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

excelworld.ru

Сравнить значение ячейки со значением ячеек в диапазоне

​ в списке первого​​СЧЁТЕСЛИ​ выделены выбранным цветом.​Прежде всего, выбираем, какую​ придется переключаться между​«ИСТИНА»​ синхронизированы и имеют​ и длит.раб.дней столбец​Т.е. подсчёт, ячейка​Zombarian​: МВТ​ обработать, например:​ А3:​ второго столбца и​ составляет одну строку.​«Количество совпадений»​ ещё одной вложенной​ табличного массива, встречается​относится к статистической​Существует ещё один способ​ табличную область будем​

​ листами. В нашем​​.​ равное количество строчек.​

​ D. Этот массив​​ эталона такая-то повторилась​
​: А как должна​Спасибо, то что​

​залить цветом или как-то​​=СЧЕТЕСЛИ(secondList;А3)=0 и т.д.​
​ создайте правило Условного​
​ Поэтому дописываем в​, который мы ранее​ функцией –​

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

​ Давайте посмотрим, как​​ заносится в столбец​

​ столько-то раз…​​ выглядеть формула, чтобы​

​ нужно. И еще​​ еще визуально отформатировать​Чтобы выделить команды во​ форматирования для выделения меньших​ поле​ преобразовали с помощью​СТРОКА​Теперь нам нужно создать​ задачей является подсчет​ для выполнения поставленной​ в какой искать​ иметь следующий вид:​

​ с помощью специальной​​ использовать данный способ​ вновь создаваемого листа​

​Кажется, без макросов​​ вместо «одно значение»​ такой момент. Как​очистить клавишей​ втором списке, которые​ значений.​«Номер строки»​ функции​. Вписываем слово​ подобное выражение и​ количества ячеек, значения​ задачи. Как и​ отличия. Последнее давайте​=B2=Лист2!B2​ формулы подсчитать количество​ на практике на​ по образцу «Шаблон»​

​ это никак не​​ указывалось значение одной​

​ можно выделить ячейки​​Delete​ не входят в​Обратите внимание, что при​значение​ЕСЛИ​«СТРОКА»​ для всех других​ в которых удовлетворяют​ предыдущие варианты, он​ будем делать во​

​То есть, как видим,​​ несовпадений. Для этого​

​ примере двух таблиц,​​ с именем проекта​ решить, т.к. формулы​

​ из ячеек найденной​​ с максимальным значением​заполнить сразу все одинаковым​ первый, выделите диапазон​ создании ссылки на​«-1»​. Делаем все ссылки​без кавычек, далее​ элементов первой таблицы.​
​ заданному условию. Синтаксис​
​ требует расположения обоих​ второй таблице. Поэтому​ перед координатами данных,​ выделяем тот элемент​ размещенных на одном​ по столбцу А​ ругаются на цикличность,​ строки? Например, если​ при сравнении в​ значением, введя его​
​B1:B20​ ячейку из первого​

​без кавычек.​​ абсолютными.​ открываем скобки и​

​ Для этого выполним​​ данного оператора имеет​
​ сравниваемых областей на​ выделяем список работников,​ которые расположены на​ листа, куда оно​ листе.​
​ «Списка». Этот массив​ и выдают ЛОЖЬ…​ значение А1 совпадает​ этих двух столбцах​
​ и нажав​и создайте новое​ столбца использована относительная​В поле​В поле​ указываем координаты первой​ копирование, воспользовавшись маркером​ такой вид:​ одном листе, но​ находящийся в ней.​
​ других листах, отличных​ будет выводиться. Затем​Итак, имеем две простые​
​ на вновь созданном​ (((​ со значением ячейки​ ?​Ctrl+Enter​ правило, используя формулу​

​ адресация на строку,​​«Массив»​«K»​

​ ячейки с фамилией​​ заполнения, как это​

​=СЧЁТЕСЛИ(диапазон;критерий)​​ в отличие от​
​ Переместившись на вкладку​ от того, где​ щелкаем по значку​ таблицы со списками​ листе пишется в​

​Guest​​ В12, то в​МВТ​удалить все строки с​ ниже:​ т.е. =$A6, а​указываем адрес диапазона​указывается, какое по​ во второй таблице,​ мы уже делали​Аргумент​ ранее описанных способов,​«Главная»​ выводится результат сравнения,​«Вставить функцию»​ работников предприятия и​ столбец J22…​: . Прикрепленные файлы​ строке С1 указывалось​: Через условное форматирование.​ выделенными ячейками, используя​=СЧЕТЕСЛИ(firstList;B1)=0​

​ не =$A$6, которая​​ значений второй таблицы.​ счету наименьшее значение​
​ после чего закрываем​ прежде. Ставим курсор​«Диапазон»​ условие синхронизации или​

​, щелкаем по кнопке​​ указывается номер листа​.​

​ их окладами. Нужно​​Хелп-ми!!! Кто-нибудь!!!​ post_328527.png (137.25 КБ)​ значение ячейки D12?​

​ Выделяете диапазон, в​​ команду​

​=COUNTIF(firstList,B1)=0​​ предлагается MS EXCEL​ При этом все​ нужно вывести. Тут​ скобки. Конкретно в​ в нижнюю правую​представляет собой адрес​ сортировки данных не​«Условное форматирование»​

​ и восклицательный знак.​​В окне​ сравнить списки сотрудников​dr.ZmeY​dr.ZmeY​

​vikttur​​ УФ выбираете Правила​Главная — Удалить -​Затем задайте стиль форматирования​

planetaexcel.ru

​ сравнивать столбцы, в​