Підрахуйте всі збіги / дублікати між двома стовпцями в 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 ви також можете отримати кількість відповідностей між двома стовпцями, загальний синтаксис:
Array formula, should press Ctrl + Shift + Enter keys together.
- 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.
Використана відносна функція:
- 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 функцій, Переконайтеся, що те, що вам потрібно, знаходиться лише на відстані одного кліка...
Вкладка Office - увімкніть читання та редагування вкладок у Microsoft Office (включаючи Excel)
- Одна секунда для перемикання між десятками відкритих документів!
- Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
- Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
- Додає ефективні вкладки в Office (включно з Excel), як у Chrome, Edge та Firefox.
