Пребройте всички съвпадения / дубликати между две колони в Excel
Сравняването на две колони с данни и преброяването на всички съвпадения или дубликати в двете колони може да е обичайна задача за повечето от нас. Например, имате две колони с имена, някои имена се появяват както в първата, така и във втората колона, сега искате да преброите всички съвпадащи имена (съвпаденията, разположени навсякъде в двете колони) между две колони, както е показано на екранната снимка по-долу, този урок ще въведе някои формули за постигане на тази цел в Excel.
- Пребройте всички съвпадения между две колони с функциите SUMPRODUCT и COUNTIF
- Пребройте всички съвпадения между две колони с функциите COUNT и MATCH
- Пребройте всички съвпадения между две колони с функциите SUMPRODUCT, ISNUMBER и MATCH
Пребройте всички съвпадения между две колони с функциите SUMPRODUCT и COUNTIF
За да преброите всички съвпадения между две колони, комбинацията от функции SUMPRODUCT и COUNTIF може да ви помогне, общият синтаксис е:
- range1, range2: Двата диапазона съдържат данните, които искате да преброите всички съвпадения.
Сега, моля, въведете или копирайте формулата по-долу в празна клетка и след това натиснете Въведете ключ за получаване на резултата:
Обяснение на формулата:
=SUMPRODUCT(COUNTIF(A2:A12;C2:C12))
- COUNTIF(A2:A12;C2:C12): Тази функция COUNTIF проверява дали всяко име от колона C съществува в колона A. Ако името съществува, се показва число 1, в противен случай се показва число 0. Функцията ще върне резултата като следния: {1;1;0;0;0;1;0;0;1;0;1}.
- SUMPRODUCT(COUNTIF(A2:A12,C2:C12))=SUMPRODUCT({1;1;0;0;0;1;0;0;1;0;1}): Функцията SUMPRODUCT сумира всички елементи в този масив и получава резултата: 5.
Пребройте всички съвпадения между две колони с функциите COUNT и MATCH
С комбинацията от функции COUNT и MATCH можете също да получите броя на съвпаденията между две колони, общият синтаксис е:
Array formula, should press Ctrl + Shift + Enter keys together.
- range1, range2: Двата диапазона съдържат данните, които искате да преброите всички съвпадения.
Моля, въведете или копирайте следната формула в празна клетка и след това натиснете Ctrl + Shift + Enter ключове заедно, за да получите правилния резултат, вижте екранната снимка:
Обяснение на формулата:
=БРОЙ(МАЧ(A2:A12;C2:C12,0;XNUMX))
- MATCH(A2:A12;C2:C12,0;XNUMX): Тази функция MATCH ще търси имената от колона A в колона C и ще върне позицията на всяка съответстваща стойност. Ако стойност не бъде намерена, ще се покаже стойност за грешка. И така, ще получите списъка с масиви по следния начин: {11;2;#N/A;#N/A;#N/A;6;1;#N/A;#N/A;#N/A; 9}.
- COUNT(MATCH(A2:A12,C2:C12,0))= COUNT({11;2;#N/A;#N/A;#N/A;6;1;#N/A;#N/A;#N/A;9}): Функцията COUNT ще преброи числата в списъка с масиви, за да получи резултата: 5.
Пребройте всички съвпадения между две колони с функциите SUMPRODUCT, ISNUMBER и MATCH
В Excel можете да опитате да намерите съвпаденията в две колони и след това да преброите, като използвате функциите SUMPRODUCT, ISNUMBER и MATCH, общият синтаксис е:
- range1, range2: Двата диапазона съдържат данните, които искате да преброите всички съвпадения.
Въведете или копирайте формулата по-долу в празна клетка, за да изведете резултата, и след това натиснете Въведете ключ за връщане на изчислението, вижте екранната снимка:
Обяснение на формулата:
=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A12;C2:C12,0;XNUMX))))
- MATCH(A2:A12;C2:C12,0;XNUMX): Тази функция MATCH ще търси имената от колона A в колона C и ще върне позицията на всяка съответстваща стойност. Ако стойността не бъде намерена, ще се покаже стойност за грешка. И така, ще получите списъка с масиви по следния начин: {11;2;#N/A;#N/A;#N/A;6;1;#N/A;#N/A;#N/A; 9}.
- ISNUMBER(MATCH(A2:A12,C2:C12,0))= ISNUMBER({11;2;#N/A;#N/A;#N/A;6;1;#N/A;#N/A;#N/A;9}): Тук функцията ISNUMBER преобразува числата в TRUE и други стойности в FALSE в масива. И така, ще получите масив като този: {TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE}.
- ---(ISNUMBER(MATCH(A2:A12,C2:C12,0)))=--({TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE}): --този двоен отрицателен знак се използва за преобразуване на TRUE стойност в 1 и False стойност в 0 и връща резултата като следния: {1;1;0;0;0;1;1;0;0;0; 1}.
- SUMPRODUCT(--(ISNUMBER(MATCH(A2:A12,C2:C12,0))))=SUMPRODUCT({1;1;0;0;0;1;1;0;0;0;1}): И накрая, функцията SUMPRODUCT ще сумира всички елементи в този масив и ще получи резултата: 5.
Използвана относителна функция:
- SUMPRODUCT:
- Функцията SUMPRODUCT може да се използва за умножаване на две или повече колони или масиви заедно и след това да се получи сумата от продуктите.
- COUNTIF:
- Функцията COUNTIF е статистическа функция в Excel, която се използва за преброяване на броя клетки, които отговарят на критерий.
- COUNT:
- Функцията COUNT се използва за преброяване на броя на клетките, които съдържат числа, или за преброяване на числата в списък с аргументи.
- MATCH:
- Функцията MATCH на Microsoft Excel търси конкретна стойност в диапазон от клетки и връща относителната позиция на тази стойност.
- ISNUMBER:
- Функцията ISNUMBER връща TRUE, когато клетка съдържа число, и FALSE, ако не.
Още статии:
- Пребройте съвпаденията между две колони
- Например, имам два списъка с данни в колона A и колона C, сега искам да сравня двете колони и да преброя дали стойността в колона A е намерена в колона C в същия ред, както е показано на екранната снимка по-долу. В този случай функцията SUMPRODUCT може да е най-добрата функция за решаване на тази задача в Excel.
- Пребройте броя на клетките, които съдържат конкретен текст в Excel
- Да предположим, че имате списък с текстови низове и може да искате да намерите броя на клетките, които съдържат определен текст като част от тяхното съдържание. В този случай можете да използвате заместващите знаци (*), които представляват всички текстове или знаци във вашите критерии, когато прилагате функцията COUNTIF. Тази статия ще представя как да използвате формули за справяне с тази работа в Excel.
- Пребройте броя на клетките, които не са равни на много стойности в Excel
- В Excel можете лесно да получите броя на клетките, които не са равни на конкретна стойност, като използвате функцията COUNTIF, но опитвали ли сте някога да преброите клетките, които не са равни на много стойности? Например, искам да получа общия брой продукти в колона A, но да изключа конкретните елементи в C4:C6, както е показано на екранната снимка по-долу. В тази статия ще представя някои формули за решаване на тази задача в Excel.
Най-добрите инструменти за производителност в офиса
Kutools за Excel - помага ви да се откроите от тълпата
Kutools за Excel разполага с над 300 функции, Гарантираме, че това, от което се нуждаете, е само на един клик разстояние...
Раздел Office - Активиране на четене и редактиране с раздели в Microsoft Office (включително Excel)
- Една секунда за превключване между десетки отворени документи!
- Намалете стотиците кликвания на мишката за вас всеки ден, кажете сбогом на ръката на мишката.
- Увеличава продуктивността ви с 50% при преглеждане и редактиране на множество документи.
- Внася ефективни раздели в Office (включително Excel), точно като Chrome, Edge и Firefox.
