Підрахуйте всі збіги / дублікати між двома стовпцями в Excel

Порівняння двох стовпців даних і підрахунок усіх збігів або дублікатів у двох стовпцях може бути звичайним завданням для більшості з нас. Наприклад, у вас є два стовпці імен, деякі імена відображаються як у першому, так і в другому стовпцях, тепер ви хочете підрахувати всі відповідні імена (збіги, розташовані в будь -якому місці двох стовпців) між двома стовпцями, як показано нижче на скріншоті, цей підручник представить деякі формули для досягнення цієї мети в Excel.

• Підрахуйте всі збіги між двома стовпцями з функціями SUMPRODUCT та COUNTIF
• Порахуйте всі збіги між двома стовпцями з функціями COUNT та MATCH
• Підрахуйте всі збіги між двома стовпцями з функціями SUMPRODUCT, ISNUMBER та MATCH

Підрахуйте всі збіги між двома стовпцями з функціями SUMPRODUCT та COUNTIF

Щоб підрахувати всі збіги між двома стовпцями, вам може допомогти комбінація функцій SUMPRODUCT та COUNTIF, загальний синтаксис такий:

• range1, range2: Два діапазони містять дані, за якими потрібно підрахувати всі збіги.

Тепер введіть або скопіюйте формулу нижче у порожню клітинку, а потім натисніть Що натомість? Створіть віртуальну версію себе у ключ для отримання результату:

Пояснення формули:

= ПІДПРИЄМСТВО (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 ви також можете отримати кількість відповідностей між двома стовпцями, загальний синтаксис:

• range1, range2: Два діапазони містять дані, за якими потрібно підрахувати всі збіги.

Будь ласка, введіть або скопіюйте таку формулу в порожню клітинку, а потім натисніть Ctrl + Shift + Enter клавіші разом, щоб отримати правильний результат, див. знімок екрана:

Пояснення формули:

= COUNT (МАТЧ (A2: A12, C2: C12,0))

• МАТЧ (A2: A12, C2: C12,0): Ця функція 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: Два діапазони містять дані, за якими потрібно підрахувати всі збіги.

Введіть або скопіюйте наведену нижче формулу у порожню клітинку для виведення результату, а потім натисніть Що натомість? Створіть віртуальну версію себе у ключ для повернення обчислення, див. скріншот:

Пояснення формули:

= ПІДПРИЄМСТВО (- (ІСНОВА (МАТЧ) (АРХІВ (A2: A12, C2: C12,0))))

• МАТЧ (A2: A12, C2: C12,0): Ця функція 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.

Використана відносна функція:

Більше статей: