Note: The other languages of the website are Google-translated. Back to English

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

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


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

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

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

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

=SUMPRODUCT(COUNTIF(A2:A12,C2:C12))


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

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

{=COUNT(MATCH(range1,range2,0))}
Array formula, should press Ctrl + Shift + Enter keys together.
  • range1, range2: Два діапазони містять дані, за якими потрібно підрахувати всі збіги.

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

=COUNT(MATCH(A2:A12,C2:C12,0))


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

= 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 загальний синтаксис виглядає так:

=SUMPRODUCT(--(ISNUMBER(MATCH(range1,range2,0))))
  • range1, range2: Два діапазони містять дані, за якими потрібно підрахувати всі збіги.

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

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A12,C2:C12,0))))


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

= ПІДПРИЄМСТВО (- (ІСНОВА (МАТЧ) (АРХІВ (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.

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

  • SUMPRODUCT:
  • Функцію SUMPRODUCT можна використовувати для множення двох або більше стовпців або масивів разом, а потім отримати суму добутків.
  • COUNTIF:
  • Функція COUNTIF - це статистична функція в Excel, яка використовується для підрахунку кількості клітинок, які відповідають критерію.
  • COUNT:
  • Функція COUNT використовується для підрахунку кількості клітинок, які містять числа, або підрахунку чисел у списку аргументів.
  • MATCH:
  • Функція Microsoft Excel MATCH шукає певне значення в діапазоні комірок і повертає відносне положення цього значення.
  • ISNUMBER:
  • Функція ISNUMBER повертає TRUE, коли комірка містить число, і FALSE, якщо ні.

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

  • Порахуйте відповідність між двома стовпцями
  • Наприклад, у мене є два списки даних у стовпці А та С, тепер я хочу порівняти ці два стовпці та порахувати, чи значення у стовпці А знайдено у стовпці С у тому ж рядку, що показано на скріншоті нижче. У цьому випадку функція SUMPRODUCT може бути найкращою функцією для вирішення цього завдання в Excel.
  • Підрахувати кількість клітинок, що містять певний текст у Excel
  • Припустимо, у вас є список текстових рядків, і ви можете знайти кількість клітинок, які містять певний текст, як частину їх вмісту. У цьому випадку ви можете використовувати символи підстановки (*), які представляють будь -які тексти або символи у ваших критеріях під час застосування функції COUNTIF. У цій статті я розповім, як використовувати формули для роботи з цією роботою в Excel.
  • Підрахуйте кількість клітинок, що не дорівнює багатьом значенням у Excel
  • В Excel можна легко отримати кількість клітинок, що не дорівнює певному значенню, за допомогою функції COUNTIF, але ви коли -небудь пробували порахувати клітинки, які не рівні багатьом значенням? Наприклад, я хочу отримати загальну кількість продуктів у стовпці А, але виключити конкретні пункти в С4: С6, як показано на скріншоті нижче. У цій статті я представлю деякі формули для вирішення цієї роботи в Excel.

Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel - допомагає виділитися з натовпу

Бажаєте виконувати щоденну роботу швидко та якісно? Kutools для Excel надає 300 потужних розширених функцій (об’єднання робочих книг, підсумовування за кольором, розділення вмісту клітинок, перетворення дати тощо) і заощаджує 80% вашого часу.

  • Розроблений для 1500 сценаріїв роботи, допомагає вирішити 80% проблем Excel.
  • Щодня зменшуйте тисячі клацань клавіатури та миші, знімайте втомлені очі та руки.
  • Станьте експертом Excel за 3 хвилини. Більше не потрібно згадувати будь-які болісні формули та коди VBA.
  • Безкоштовна 30-денна безкоштовна пробна версія. 60-денна гарантія повернення грошей. Безкоштовне оновлення та підтримка протягом 2 років.
Стрічка Excel (із встановленим Kutools для Excel)

Вкладка Office - Увімкнути читання та редагування з вкладками в Microsoft Office (включаючи Excel)

  • Одна секунда для перемикання між десятками відкритих документів!
  • Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
  • Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
  • Приносить ефективні вкладки в Office (включаючи Excel), як і Chrome, Firefox та New Internet Explorer.
Знімок екрана Excel (із встановленою вкладкою Office)
Коментарі (1)
Оцінено 5 з 5 · рейтинги 1
Цей коментар був мінімізований модератором на сайті
Great tip, saved me a lot of eye strain!
Оцінено 5 з 5
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця

Слідуй за нами

Copyright © 2009 - WWW.extendoffice.com. | Всі права захищені. На основі ExtendOffice. | Карта сайту
Microsoft та логотип Office є товарними знаками або зареєстрованими товарними знаками Microsoft Corporation у США та / або інших країнах.
Захищений Sectigo SSL