Как в excel найти пары чисел

=СЧЁТЕСЛИМН(A:A;A1;B:B;B1)

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

=СЧЁТЕСЛИМН($B$5:$B$11; B5; $C$5:$C$11; C5)

Функция СЧЁТЕСЛИМН принимает диапазон/критерий пар, и обеспечивает подсчет, когда все критерии соответствуют. В этом примере, содержит две пары диапазон/критериев.

В паре 1, диапазон B:B (вводится в полной ссылки на колонке) и критерием является B5. Сама по себе эта пара будет рассчитывать счетчик каждого значения в столбце B.

В паре 2, диапазон C:C, а критерием является C5. Сама по себе эта пара будет возвращать счетчик каждого значения в столбце C.

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

Количество длинных чисел без СЧЁТЕСЛИ

=СУММПРОИЗВ(—(A:A=A1))

Предисловие

Это досадно длинное введение, но контекст имеет важное значение, извините!

Если вы пытаетесь подсчитать очень длинные числа (16+ цифр) в диапазоне с СЧЁТЕСЛИ, вы можете увидеть неправильные результаты, из-за ошибки в том, как определенные функции обрабатывают длинные числа, даже если эти числа сохраняются в виде текста. Рассмотрим приведенное ниже изображение. Все счетчики в колонке D являются неправильными — Хотя каждое число в столбце B является уникальным, количество, рассчитанное СЧЁТЕСЛИ предлагает эти цифры, являющиеся дубликатами.

=СЧЁТЕСЛИ(данные;B5)

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

Как правило, вы можете избежать этого ограничения, введя длинные числа в виде текста, либо путем запуска числа с одиночной кавычки (‘999999999999999999) или при форматировании ячейки в виде текста перед входом. До тех пор, пока вам не нужно выполнять математические операции над числом, это хорошее решение позволяет вводить дополнительные длинные числа для таких вещей, как номера кредитных карт и серийных номеров, без потери каких-либо цифр.

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

Решение

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

=СУММПРОИЗВ(—(данные=B5))

Формула использует диапазон B5: B9 и генерирует правильное количество для каждого числа с СУММПРОИЗВ.

Во-первых, выражение внутри СУММПРОИЗВ сравнивает все значения в диапазоне со значением из столбца B в текущей строке. Результатом будет массив ИСТИНА/ЛОЖЬ.

=СУММПРОИЗВ(—(данные=B5))

=СУММПРОИЗВ(—({ИСТИНА;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ}))

Далее, двойное отрицание превращает значения ИСТИНА / ЛОЖЬ в значения 1/0.

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

Наконец, СУММПРОИЗВ просто суммирует элементы в массиве и рассчитывает результат.

Формула массива

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

{=СУММ(—(B:B=B5))}

Другие функции с этой проблемой

Я не проверял это сам, но кажется, что некоторые функции имеют те же проблемы, в том числе СУММЕСЛИ, СУММЕСЛИМН, СЧЁТЕСЛИ, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН.

Количество проданных и оставшихся

= СЧЁТЗ(диапазон1) — СЧЁТЗ(диапазон2)

Если у вас есть список пунктов, и нужно подсчитать, общее количество, сколько продано, сколько осталось и т.д., вы можете использовать функцию СЧЁТЗ. Это может быть полезно, если вы продаете билеты, количество мест, записей или что-нибудь, где вы поддерживаете и отслеживаете перечень проданных товаров.

В примере, формула в F7 является:

=СЧЁТЗ(B5:B11) — СЧЁТЗ(C5:C11)

Функция СЧЁТЗ подсчитывает непустые ячейки, которые содержат числа или текст. Первый СЧЁТЗ подсчитывает непустые ячейки в диапазоне B5: B11 и рассчитывает число 7:

СЧЁТЗ (B5: B11) // рассчитывает 7

Вторая функция СЧЁТЗ делает то же самое с диапазоном C5:C11 и рассчитывает 3, так как есть 3 непустые ячейки в этом диапазоне:

СЧЁТЗ (C5:C11) // рассчитывает 3

Таким образом, вся формула сводится к 7 — 3 и рассчитывает 4.

Обратите внимание, что в этом случае значения, которые появляются в колонке C не имеет значения. Они могут быть кодами из столбца B , словом «да», или просто «х».

Тест совпадений

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

= СУММПРОИЗВ(- (B5: B11 = C5: C11))

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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