Порівняйте два стовпці щодо збігів та відмінностей у Excel
Цей підручник розповідає про те, як порівняти два стовпці в Excel, що є звичайною роботою Excel у нашій щоденній роботі. Порівняння двох стовпців можна виконати різними способами в Excel, наприклад, порівняння двох стовпців рядком за рядком або клітинки за клітинками, порівняння двох стовпців для виділення збігів чи відмінностей тощо. Тут ця стаття висвітлює найбільш можливі сценарії порівняння двох стовпців, які ви могли б зустрітися і сподіватися, що це може вам допомогти.
примітки
У цьому посібнику він містить деякі приклади даних для кращого пояснення методів порівняння двох стовпців у різних випадках. Залежно від вашого набору даних, можливо, вам доведеться змінити або відкоригувати деякий вміст (посилання), однак основні принципи залишаться незмінними. Або завантажте зразки кожного випадку, якщо ви хочете лише перевірити, чи працюють методи чи ні.
Нижче наведено набір даних, де мені потрібно перевірити в одному рядку, чи імена в стовпці A однакові з іменами в стовпці B чи ні.
Клацніть, щоб завантажити зразок файлу
1.1 Порівняйте клітинки в одному рядку для точного збігу
Як правило, якщо ви хочете порівняти два стовпці рядком за рядком для точного збігу, ви можете використовувати формулу нижче:
прес вводити клавішу та перетягніть маркер заповнення до комірки D8. Якщо формула повертає TRUE, значення двох стовпців абсолютно однакові, якщо повертає FALSE, вони різні.
1.2 Порівняйте клітинки в одному рядку для точного збігу чи регістру (за формулою IF)
Якщо ви хочете порівняти два стовпці рядок за рядком для чутливих до регістру або отримати більше опису, наприклад Match, Mismatch, ви можете використовувати функцію IF.
Порівнюючи клітинки в одному рядку для точного збігу
Якщо ви хочете використати тексти «Відповідність» та «Невідповідність» для опису результатів порівняння, використовуйте формулу нижче:
прес вводити , щоб отримати перший результат, а потім перетягніть маркер автоматичного заповнення до комірки D8.
Порівняння клітинок у тому самому рядку для збігу регістру, що не чутливий
Якщо ви хочете порівняти клітинки з урахуванням регістру, ви можете використати формулу нижче:
прес вводити , щоб отримати перший результат, а потім перетягніть маркер автоматичного заповнення до комірки E8.
Зауваження
У наведених вище формулах ви можете змінити тексти “Match” та “Mathatch” на власний опис.
Якщо ви хочете виділити відповідність або різні значення, Умовне форматування ця функція може вам допомогти.
1. Виберіть два стовпці, які використовуються для порівняння з (B2: C8, за винятком заголовків стовпців), а потім натисніть Головна > Умовне форматування > Нове правило.
2. У вискакуванні Нове правило форматування натисніть, щоб вибрати За допомогою формули визначте, які клітинки потрібно форматувати в Виберіть тип правила розділ, а потім введіть = $ B2 = $ C2 в текстове поле Форматувати значення, де ця формула відповідає дійсності.
3. Тепер натисніть сформований щоб відобразити Формат ячеек діалогове вікно, потім під Заповнювати на вкладці виберіть один колір, який вам потрібен, щоб виділити збіги.
Або ви можете змінити розмір шрифту, розмір шрифту, межі комірок або формат цифр, щоб вирівняти збіги, як це потрібно на інших вкладках.
4. клацання OK > OK щоб закрити діалогові вікна, тоді клітинки в тому самому рядку будуть виділені, якщо вони однакові.
Якщо ви хочете виділити значення невідповідності, ви можете використовувати це в = $ B2 <> $ C2 в Форматувати значення, де ця формула відповідає дійсності текстове поле в Змінити правило форматування діалог
Тоді відмінності двох стовпців в одному рядку будуть виділені вказаним кольором.
1.4 Порівняйте два стовпці рядок за рядком та виділіть значення невідповідності (за допомогою VBA)
Якщо ви хочете порівняти два стовпці рядок за рядком із кодом VBA, цей посібник вас задовольнить.
1. Увімкніть аркуш, що містить два стовпці, що використовуються для порівняння, натисніть Alt + F11 клавіші, щоб увімкнути Microsoft Visual Basic для додатків вікна.
2. У діалоговому вікні, що з’являється, натисніть Insert > Модулі.
3. Потім скопіюйте та вставте нижче макрос у новий сценарій модуля.
VBA: Порівняйте два стовпці рядок за рядком та виділіть відмінності
Sub ExtendOffice_HighlightColumnDifferences()
'UpdatebyKutools20201016
Dim xRg As Range
Dim xWs As Worksheet
Dim xFI As Integer
On Error Resume Next
SRg:
Set xRg = Application.InputBox("Select two columns:", "Kutools for Excel", , , , , , 8)
If xRg Is Nothing Then Exit Sub
If xRg.Columns.Count <> 2 Then
MsgBox "Please select two columns"
GoTo SRg
End If
Set xWs = xRg.Worksheet
For xFI = 1 To xRg.Rows.Count
If Not StrComp(xRg.Cells(xFI, 1), xRg.Cells(xFI, 2), vbBinaryCompare) = 0 Then
xWs.Range(xRg.Cells(xFI, 1), xRg.Cells(xFI, 2)).Interior.ColorIndex = 7 'you can change the color index as you need.
End If
Next xFI
End Sub
4. натисніть F5 клавішу для запуску коду, тоді з'явиться діалогове вікно для вибору двох стовпців.
5. клацання OK. Потім відмінності двох стовпців були виділені кольором тла.
Зауваження
Ви можете змінити колір виділення відповідно до власних потреб, змінивши індекс кольору в коді, посилання на індекс кольору:
У цій частині набір даних показаний, як показано нижче, і ви хочете знайти всі значення, що знаходяться як у стовпці B, так і в стовпці C одночасно, або знайти значення лише у стовпці B.
Клацніть, щоб завантажити зразок файлу
Тут ви можете використовувати формулу, яка поєднується з функцією IF та COUNTIF, щоб порівняти два стовпці та знайти значення, які є у стовпці B, але не в стовпці C.
прес вводити клавішу та перетягніть маркер автозаповнення до комірки D8.
Зауваження
1. Ця формула порівнює два стовпці без урахування регістру.
2. Ви можете змінити опис "Ні в С" та "Так в С" на інші.
3. Якщо ви хочете порівняти цілі два стовпці, змініть фіксований діапазон $ C $ 2: $ C $ 8 на $ C: $ C.
2.2 Порівняння та виділення повторюваних або унікальних даних (за допомогою умовного форматування)
повне г, повне г,, показали, від, номер, XNUMX Умовне форматування функція в Excel потужна, тут ви можете використовувати її для порівняння двох клітинок стовпців за клітинками, а потім виділити відмінності або збіги, як вам потрібно.
Виділіть усі дублікати або унікальні значення у двох стовпцях
1. Виберіть два стовпці, з якими ви будете порівнювати, і натисніть Головна > Умовне форматування > Виділіть правила клітин > Повторювані значення.
2. У вискакуванні Повторювані значення діалоговому вікні, виберіть потрібний формат виділення зі спадного списку значень за допомогою.
3. клацання OK. Потім були виділені дублікати у двох стовпцях.
Зауваження
Якщо ви хочете виділити унікальні значення (відмінності) у двох стовпцях, також клацніть Головна > Умовне форматування > Виділіть правила клітин > Повторювані значення щоб відобразити Повторювані значення діалогове вікно, змініть дублювати до Унікальний у розкривному списку ліворуч, потім виберіть інший формат зі значень зі спадним списком, натисніть OK.
Унікальні значення будуть виділені.
Знайдіть і виділіть значення у стовпці B, якщо це також у стовпці C
Якщо ви хочете виділити значення у стовпці B, які також є у стовпці C, Cумовне форматування функція також може зробити вам послугу.
1. Виберіть стовпець B2: B8, натисніть Головна > Умовне форматування > Нове правило.
2 В Нове правило форматування діалогове вікно, виберіть За допомогою формули визначте, які клітинки потрібно форматувати від Виберіть тип правила розділ, а потім введіть = COUNTIF ($ C $ 2: $ C $ 8, $ B2)> 0 в текстове поле Форматувати значення, де ця формула відповідає дійсності.
3. клацання сформований йти до Формат ячеек діалогове вікно, під Заповнювати виберіть один колір, щоб виділити збіги.
Ви можете використовувати інші формати для виділення значень на вкладках Шрифт, Число, Межа.
4. клацання OK > OK. Тоді значення у стовпці B, які також існують у стовпці C, були виділені вказаним кольором.
Якщо ви хочете виділити значення лише у стовпці B, але не в стовпці C, повторіть вищевказані кроки, але змініть формулу на кроці 2 на = COUNTIF ($ C $ 2: $ C $ 8, $ B2) = 0 в Нове правило форматування діалог
Потім виберіть інший колір для форматування значень.
Зауваження
Тут умовне форматування порівнює два стовпці без чутливих регістрів.
Іноді, порівнявши дві колонки, ви можете вжити інші дії щодо збігів чи різниці, наприклад, виділення, видалення, копіювання тощо. У цьому випадку зручний інструмент - Виберіть однакові та різні клітини of Kutools для Excel може безпосередньо вибрати збіги або різницю для кращого виконання наступної операції, а також може безпосередньо виділити значення.
після безкоштовна установка Kutools для Excel, будь-ласка, виконайте наведені нижче дії:
1. клацання Кутулс > вибрати > Виберіть однакові та різні клітини.
2. Потім у Виберіть однакові та різні клітини діалогове вікно, будь-ласка, зробіть, як показано нижче:
1) У Знайдіть значення в і Відповідно до розділи, виберіть два стовпці, з якими потрібно порівняти;
2) Виберіть Кожен ряд варіант;
3) Виберіть Ті самі значення or Різні цінності як вам потрібно;
4) Якщо ви хочете виділити значення, виберіть потрібний колір.
3. клацання Ok, спливає діалогове вікно, щоб нагадати вам кількість знайдених значень, натисніть OK щоб закрити діалогове вікно. І в той же час були вибрані значення, тепер ви можете видалити, скопіювати або виконати інші операції.
Якщо ви встановите прапорець Заливка кольором і Колір шрифту заливки прапорці, результат відображається так:
Зауваження
Якщо ви хочете порівняти з урахуванням регістру, перевірте Чутливий до справи варіант.
Цей інструмент підтримує порівняння двох стовпців на різних робочих аркушах.
2.4 Порівняйте два стовпці та перелічіть точні дублікати в іншому стовпці (використовуючи код VBA)
Якщо ви хочете перерахувати відповідні значення в іншому стовпці після порівняння двох стовпців по клітинках, тут вам може допомогти наведений нижче код макросу.
1. Увімкніть аркуш, для якого потрібно порівняти два стовпці, а потім натисніть Alt + F11 клавіші для відображення Microsoft Visual Basic для додатків вікна.
2. клацання Insert > Модулі в Microsoft Visual Basic для додатків вікна.
3. Потім скопіюйте та вставте наведений нижче код у новий порожній сценарій модуля.
VBA: Перерахуйте дублікати поруч із стовпцем після порівняння двох стовпців
Sub ExtendOffice_FindMatches()
'UpdatebyKutools20201019
Dim xRg, xRgC1, xRgC2, xRgF1, xRgF2 As Range
Dim xIntSR, xIntER, xIntSC, xIntEC As Integer
On Error Resume Next
SRg:
Set xRgC1 = Application.InputBox("Select first column:", "Kutools for Excel", , , , , , 8)
If xRgC1 Is Nothing Then Exit Sub
If xRgC1.Columns.Count <> 1 Then
MsgBox "Please select single column"
GoTo SRg
End If
SsRg:
Set xRgC2 = Application.InputBox("Select the second column:", "Kutools for Excel", , , , , , 8)
If xRgC2 Is Nothing Then Exit Sub
If xRgC2.Columns.Count <> 1 Then
MsgBox "Please select single column"
GoTo SsRg
End If
Set xWs = xRg.Worksheet
For Each xRgF1 In xRgC1
For Each xRgF2 In xRgC2
If xRgF1.Value = xRgF2.Value Then xRgF2.Offset(0, 1) = xRgF1.Value
Next xRgF2
Next xRgF1
End Sub
4. прес F5 для запуску коду, є два діалогових вікна, що вискакують одне за іншим, щоб ви могли вибрати два стовпці окремо.
примітки: Будь ласка, спочатку виберіть лівий стовпець, потім виберіть правий стовпець у другому діалоговому вікні, інакше дублікати замінять вихідні дані у другому стовпці.
5. клацання OK > OK, тоді збіги були автоматично перераховані у правій колонці двох стовпців.
Зауваження
Код VBA порівнює два стовпці з урахуванням регістру.
2.5 Порівняйте два стовпці та виділіть дублікати (за допомогою коду VBA)
Якщо ви хочете порівняти два стовпці клітинку за коміркою, а потім виділити збіги, ви можете спробувати код нижче.
1. Увімкніть аркуш, для якого потрібно порівняти два стовпці, а потім натисніть Alt + F11 клавіші для відображення Microsoft Visual Basic для додатків вікна.
2. клацання Insert > Модулі в Microsoft Visual Basic для додатків вікна.
3. Скопіюйте код макросу нижче та вставте їх у нове порожнє місце Модулі сценарій
VBA: Порівняйте два стовпці та виділіть дублікати
Sub ExtendOffice_CompareTwoRanges()
'UpdatebyKutools20201019
Dim xRg, xRgC1, xRgC2, xRgF1, xRgF2 As Range
SRg:
Set xRgC1 = Application.InputBox("Select the column you want compare according to", "Kutools for Excel", , , , , , 8)
If xRgC1 Is Nothing Then Exit Sub
If xRgC1.Columns.Count <> 1 Then
MsgBox "Please select a single column"
GoTo SRg
End If
SsRg:
Set xRgC2 = Application.InputBox("Select the column you want to highlight duplicates in:", "Kutools for Excel", , , , , , 8)
If xRgC2 Is Nothing Then Exit Sub
If xRgC2.Columns.Count <> 1 Then
MsgBox "Please select a single column"
GoTo SsRg
End If
For Each xRgF1 In xRgC1
For Each xRgF2 In xRgC2
If xRgF1.Value = xRgF2.Value Then
xRgF2.Interior.ColorIndex = 38 '(you can change the color index as you need)
End If
Next
Next
End Sub
4. прес F5 клавіша для запуску коду. У першому діалоговому вікні, що вискакує, виберіть стовпець, за яким потрібно порівняти повторювані значення.
5. клацання OK. У другому діалоговому вікні виберіть стовпець, у якому потрібно виділити повторювані значення.
6. клацання OK. Тепер дублікати у другому стовпці були виділені кольором тла після порівняння з першим стовпцем.
Зауваження
1. Код порівнює стовпці з урахуванням регістру.
2. Ви можете змінити колір виділення відповідно до власних потреб, змінивши індекс кольору в коді, посилання на індекс кольору:
Іноді вам може знадобитися порівняти більше двох стовпців в одному рядку, наприклад, набір даних, як показано нижче. Тут, у цьому розділі, він перелічує різні методи порівняння кількох стовпців.
Клацніть, щоб завантажити зразок файлу
3.1 Знайти повні збіги у всіх клітинках одного рядка (використовуючи формулу IF)
Щоб знайти повні збіги між стовпцями в одному рядку, нижче допоможе формула IF.
Якщо комірки в одному рядку збігаються між собою, відображається “Повна відповідність” або “Ні”.
прес вводити , щоб отримати перший результат порівняння, а потім перетягніть маркер автоматичного заповнення до комірки E7.
Зауваження
1. Формула порівнює стовпці без урахування регістру.
2. Якщо вам потрібно порівняти більше або дорівнювати трьом стовпцям, ви можете використати формулу нижче:
У формулі 3 - це кількість стовпців, ви можете змінити її відповідно до своїх потреб.
3.2 Знайти відповідне в будь-яких двох клітинках одного рядка (за формулою IF)
Іноді ви хочете дізнатись, чи відповідають будь-які два стовпці в одному рядку, ви можете використовувати формулу IF.
У цій формулі вам потрібно порівняти будь-яку пару клітинок того самого рядка. “Збіг” означає, що є відповідні дві клітинки, якщо немає відповідних комірок, формула відображає “Не”, ви можете змінювати тексти, як вам потрібно.
прес вводити і перетягніть маркер заповнення до комірки E7.
Зауваження
1. Ця формула не підтримує нечутливий регістр.
2. Якщо для порівняння потрібно багато стовпців, порівняння будь-якої пари клітинок у формулі може бути занадто довгим. У цьому випадку ви можете використати формулу нижче, яка поєднує функції IF та COUNTIF.
Якщо ви хочете виділити рядки, які всі клітинки збігаються між собою, ви можете використовувати Умовне форматування функція в Excel.
1. Виберіть діапазон, який ви використовуєте, і натисніть Головна > Умовне форматування > Нове правило.
2 В Нове правило форматування діалогове вікно, виберіть За допомогою формули визначте, які клітинки потрібно форматувати від Виберіть тип правила розділу, тоді ви можете використовувати одну з наведених нижче формул у Форматувати значення, де ця формула відповідає дійсності текстове вікно.
Or
примітки: Якщо кількість стовпців більше трьох, припустимо, 5, формули слід змінити на:
Or
3. клацання сформований , щоб перейти до діалогового вікна "Форматувати комірки", а потім виберіть один колір заливки або інше форматування комірок, щоб перевершити рядки.
4. клацання OK > OK, тепер будуть виділені лише рядки, в яких всі клітинки збігаються.
Зауваження
Наведені вище формули не підтримують регістр.
3.4 Порівняйте кілька стовпців та виділіть відмінності між рядками
Якщо ви хочете виділити відмінності між рядками, це означає, що він порівнює комірки стовпців одну за одною та знаходить різні комірки відповідно до першого стовпця, ви можете скористатися вбудованою функцією Excel -Перейти до спеціального.
1. Виберіть діапазон, для якого потрібно виділити різницю рядків, і натисніть Головна > Знайти та вибрати > Перейти до спеціального.
2. У вискакуванні Перейти до спеціального діалогове вікно, перевірте Відмінності рядків варіант.
3. клацання OK. Тепер вибрано відмінності між рядками.
4. Тепер тримайте клітинки виділеними, клацніть Головна > Колір заливки щоб вибрати один колір зі спадного меню.
Зауваження
Цей метод порівнює клітини без урахування регістру.
Припустимо, що є два стовпці, стовпець B довший, а стовпець C коротший, як показано нижче. Як порівняти зі стовпцем B, як дізнатись відсутні дані у стовпці C?
Клацніть, щоб завантажити зразок файлу
4.1 Порівняйте та знайдіть відсутні точки даних (за допомогою формули VLOOKUP або MATCH)
Якщо вам потрібно лише визначити, яких даних не вистачає після порівняння двох стовпців, ви можете скористатися однією з наведених нижче формул:
Or
прес вводити клавішу, а потім перетягніть маркер автоматичного заповнення над комірку D10. Тепер, якщо дані знаходяться як у стовпці B, так і в стовпці C, формула повертає FALSE, якщо дані знаходяться лише у стовпці B, але в стовпці C відсутні, формула повертає TRUE.
Зауваження
Вище двох формул порівнюють дані без урахування регістру.
4.2 Порівняйте два стовпці та перелічіть відсутні дані нижче (за формулою INDEX)
Якщо після порівняння двох стовпців ви хочете вказати відсутні дані під коротшим стовпцем, вам може допомогти формула масиву INDEX.
У нижченаведеній комірці коротшого стовпця, припускаючи клітинку C7, введіть нижче формулу:
прес Shift + Ctrl + Enter , щоб отримати перші відсутні дані, а потім перетягніть маркер автоматичного заповнення вниз, доки не поверне значення помилки # N / A.
Тоді ви можете видалити значення помилки, і всі відсутні дані були перераховані нижче коротшого стовпця.
Зауваження
Ця формула порівнює клітини без урахування регістру.
Якщо ви хочете виконати деякі подальші дії щодо відсутніх даних після порівняння двох стовпців, наприклад, перерахування відсутніх даних в іншому стовпці або доповнення відсутніх даних нижче коротшого стовпця, ви можете спробувати зручний інструмент-Виберіть однакові та різні клітини of Kutools для Excel.
1. клацання Кутулс > вибрати > Виберіть однакові та різні клітини.
2 В Виберіть однакові та різні клітини діалогове вікно, виконайте вказані нижче дії
1) В Знайти значення в виберіть довший стовпець, що містить повний список.
In Відповідно до розділ, виберіть коротший стовпець, в якому відсутні деякі дані.
2) Виберіть Кожен ряд варіант.
3) Виберіть Різні цінності варіант.
3. клацання Ok, спливає діалогове вікно, щоб нагадати вам кількість відсутніх даних, натисніть OK щоб закрити його. Тоді вибрані відсутні дані.
Тепер ти можеш натиснути Ctrl + C клавіші, щоб скопіювати вибрані відсутні дані та вставити їх, натиснувши Ctrl + V клавіші під коротшим стовпцем або інший новий стовпець, як вам потрібно.
Зауваження
Позначаючи Не враховує регістр параметр у діалоговому вікні «Вибрати однакові та різні клітинки» порівнює два стовпці з урахуванням регістру.
4.4 Порівняйте два стовпці та перелічіть відсутні дані нижче (за допомогою VBA)
Ось макрокод, який може заповнити відсутні дані під двома стовпцями.
1. Відкрийте аркуш, який ви використовуєте, натисніть Alt + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.
2. клацання Insert > Модулі створити новий модуль для розміщення нижче коду VBA в.
VBA: Порівняйте два стовпці та заповніть відсутні дані
Sub ExtendOffice_PullUniques()
'UpdatebyKutools20201021
Dim xRg, xRgC1, xRgC2, xFRg1, xFRg2 As Range
Dim xIntR, xIntSR, xIntER, xIntSC, xIntEC As Integer
Dim xWs As Worksheet
On Error Resume Next
SRg:
Set xRg = Application.InputBox("Select two columns:", "Kutools for Excel", , , , , , 8)
If xRg Is Nothing Then Exit Sub
If xRg.Columns.Count <> 2 Then
MsgBox "Please select two columns as a range"
GoTo SRg
End If
Set xWs = xRg.Worksheet
xIntSC = xRg.Column
xIntEC = xRg.Columns.Count + xIntSC - 1
xIntSR = xRg.Row
xIntER = xRg.Rows.Count + xIntSR - 1
Set xRg = xRg.Columns
Set xRgC1 = xWs.Range(xWs.Cells(xIntSR, xIntSC), xWs.Cells(xIntER, xIntSC))
Set xRgC2 = xWs.Range(xWs.Cells(xIntSR, xIntEC), xWs.Cells(xIntER, xIntEC))
xIntR = 1
For Each xFRg In xRgC1
If WorksheetFunction.CountIf(xRgC2, xFRg.Value) = 0 Then
xWs.Cells(xIntER, xIntEC).Offset(xIntR) = xFRg
xIntR = xIntR + 1
End If
Next
xIntR = 1
For Each xFRg In xRgC2
If WorksheetFunction.CountIf(xRgC1, xFRg) = 0 Then
xWs.Cells(xIntER, xIntSC).Offset(xIntR) = xFRg
xIntR = xIntR + 1
End If
Next
End Sub
3. Потім натисніть F5 для запуску коду, вискакує діалогове вікно для вибору двох стовпців, що порівнюють.
4. клацання OK. Тепер відсутні дані вказані під двома стовпцями.
Зауваження
Код порівнює клітинки без чутливих до регістру.
Якщо є два стовпці дат, як показано на знімку екрана, можливо, ви захочете порівняти, яка дата пізніше в тому ж рядку.
Клацніть, щоб завантажити зразок файлу
5.1 Порівняйте два стовпці, якщо вони більші або менші (за формулою)
За допомогою простої формули можна швидко визначити, чи дата 1 перевищує дату 2 у кожному рядку.
прес вводити , щоб отримати перший порівняний результат, а потім перетягніть маркер автоматичного заповнення до комірки C6, щоб отримати всі результати.
Зауваження
1. В Excel дати зберігаються як числові ряди, насправді вони є числами. Тому ви застосовуєте формулу для безпосереднього порівняння дат.
2. Якщо ви хочете порівняти, якщо дата 1 передує даті 2 у кожному рядку, змініть символ > до < у формулі.
5.2 Порівняйте два стовпці, якщо вони більше або менше форматування (за умови умовного форматування)
Якщо ви хочете виділити клітинки у стовпці Дата 1, якщо вони перевищують Дату 2, ви можете використовувати Умовне форматування функція в Excel.
1. Виберіть дати у стовпці B (Date1), а потім натисніть Головна > Умовне форматування > Нове правило.
2 В Нове правило форматування діалог, виберіть За допомогою формули визначте, які клітинки потрібно форматувати в Виберіть тип правила розділ, потім введіть формулу = $ B2> $ C2 в текстове поле Форматувати значення, де ця формула відповідає дійсності.
Якщо ви хочете виділити комірки в стовпці B, які є меншими, ніж у стовпці C, використовуйте формулу = $ B2 <$ C2.
3. клацання сформований кнопка, щоб відкрити Формат ячеек діалоговому вікні, а потім виберіть потрібний тип формату.
4. клацання OK > OK. Потім були виділені клітинки у стовпці Date1, які перевищують клітинки у стовпці Date2.
Наприклад, є дві таблиці, тепер потрібно порівняти стовпець B і стовпець E, потім знайти відносні ціни зі стовпця C і повернути їх у стовпець F.
Клацніть, щоб завантажити зразок файлу
6.1 Витягніть точні дані відповідності (за допомогою формули або зручного інструменту)
Тут він представляє деякі корисні формули та інструмент для вирішення цієї роботи.
Метод формули
У комірці F2 (комірці, куди потрібно розмістити повернене значення), використовуйте одну з наведених нижче формул:
Or
прес вводити ключ, і перше значення знайдено. Потім перетягніть маркер автоматичного заповнення до комірки F6, усі значення були витягнуті.
Зауваження
1. Формули не підтримують регістр.
2. Число 2 у формулі означає, що ви знайдете відповідні значення у другому стовпці масиву таблиці.
3. Якщо формули не можуть знайти відносне значення, воно повертає значення помилки # N / A.
Зручний метод інструменту
Якщо вас плутають з формулами, ви можете спробувати зручний інструмент - Помічник формули of Kutools для Excel, який містить декілька формул для вирішення більшості завдань у програмі Excel. За допомогою нього потрібно лише вибрати діапазон, але не потрібно пам’ятати, як використовуються формули.
після безкоштовна установка Kutools для Excel, будь-ласка, виконайте наведені нижче дії:
1. Виділіть клітинку F2 (клітинку, в яку потрібно розмістити повернене значення), і натисніть Кутулс > Помічник формули > Пошук та довідкові матеріали > Шукайте значення у списку.
2 В Помічник формул праворуч Введення аргументів розділ, виберіть діапазон табличного масиву, значення пошуку та вкажіть, який стовпець ви хочете шукати для поверненого значення.
Чайові: Не забудьте змінити абсолютне посилання в Пошук_значення поле відносно, або ви можете знайти лише перше значення.
3. клацання Ok, було знайдено перше значення, а потім перетягніть маркер автоматичного заповнення до комірки F6.
6.2 Витягніть дані часткового збігу (за формулою)
Якщо між двома порівняними стовпцями є незначна різниця, як показано на знімку екрана, вищезазначені методи не можуть працювати.
Будь ласка, виберіть одну з наведених нижче формул для вирішення цієї роботи:
Or
прес вводити клавішу, а потім перетягніть маркер автоматичного заповнення до комірки F5, всі значення знайдено.
Зауваження
1. Формули не підтримують регістр.
2. Число 2 у формулі означає, що ви знайдете значення повернення у другому стовпці масиву таблиці.
3. Якщо формули не можуть знайти відносне значення, воно повертає значення помилки # N / A.
4. * у формулі - узагальнюючий знак, який використовується для позначення будь-якого символу або рядків.
Нижче набір даних - приклад порівняння та підрахунку збігів чи різниці.
Клацніть, щоб завантажити зразок файлу
7.1 Порівняйте два стовпці та підрахуйте збіги (за допомогою формули SUMPRODUCT)
Формула SUMPRODUCT дозволяє швидко підрахувати збіги у дві колонки.
прес вводити ключ, щоб отримати результат.
Зауваження
Формула підраховує клітини, не враховуючи регістр.
7.2 Порівняйте два стовпці та підрахуйте збіги або відмінності (за допомогою зручного інструменту)
Якщо ви хочете підрахувати збіги або різниці між двома стовпцями, зручний інструмент - Виберіть однакові та різні клітини.
після безкоштовна установка Kutools для Excel, будь-ласка, виконайте наведені нижче дії:
1. клацання Кутулс > вибрати > Виберіть однакові та різні клітини.
2 В Виберіть Одинакові та різницькі клітини , виберіть два діапазони стовпців у Знайти значення в і Відповідно до розділи окремо, потім виберіть Кожен ряд, і вибрати Ті самі значення or Різні цінності варіант, як вам потрібно.
3. клацання Ok. Вискакує діалогове вікно, щоб повідомити, скільки вибраних або різних клітинок вибрано.
Відповідні клітинки
Різні клітини
Припустимо, ось список даних у стовпці B, і ви хочете порахувати клітинки, які містять "Apple" або "Candy" у стовпці D, як показано нижче:
Клацніть, щоб завантажити зразок файлу
Щоб підрахувати, чи містить клітинка одне або кілька значень, для вирішення цієї проблеми можна скористатися формулою із символами підстановки.
прес Shift + Ctrl + Enter , щоб отримати першу перевірку, а потім перетягніть маркер автозаповнення до комірки F8.
Тепер, якщо відповідна клітинка містить одне або більше значень у стовпці D, результат відображає число більше 0, якщо воно не містить жодного значення в стовпці D, повертає 0.
Якщо ви хочете підрахувати загальну кількість комірок, що містять значення у стовпці D, скористайтеся формулою внизу комірки F8:
Зауваження
1. Також ви можете використовувати формулу для підрахунку, якщо комірка містить значення в іншому стовпці
Цю формулу потрібно лише натиснути вводити , а потім перетягніть маркер автоматичного заповнення.
2. У формулах * є символом підстановки, який вказує будь-який символ або рядок.
Якщо ви хочете видалити відмінності або збіги після порівняння двох стовпців, як показано нижче:
Клацніть, щоб завантажити зразок файлу
Ви можете використовувати формулу, щоб знайти різницю або спочатку збіги:
прес вводити клавішу та перетягніть маркер автоматичного заповнення до комірки D8.
Потім застосуйте фільтр функцію в перевершувати щоб відфільтрувати відмінності або значення відповідності.
Виберіть стовпець формули, а потім клацніть дані > фільтр.
Потім у стовпці C з'являється кнопка фільтра, клацніть на кнопці фільтра, щоб розгорнути спадне меню, виберіть Різниця or матч як вам потрібно. Потім клацніть OK закінчувати.
Зараз відфільтровано лише відмінності або збіги. Ви можете вибрати їх і натиснути видаляти щоб видалити їх.
Тепер натисніть дані > фільтр ще раз, щоб очистити фільтр.
Видаліть стовпець формули, якщо він вам більше не потрібен.
Є дві колонки, одна містить вихідні ціни, а інша - ціни продажу. Тепер ця частина вводить формулу для порівняння цих двох стовпців, а потім обчислює процентну зміну між двома стовпцями.
Клацніть, щоб завантажити зразок файлу
Ви можете використовувати формулу нижче, щоб визначити процентну зміну між двома цінами в одному рядку.
прес вводити клавішу, щоб отримати число, а потім перетягніть маркер автоматичного заповнення до комірки D7.
Потім відформатуйте результат формули у відсотках. Виберіть результати, натисніть Головна та перейдіть до Номер групу, яку потрібно натиснути Процентний стиль.
Результати формули були відформатовані у відсотках.
Тепер ви знаєте, як порівняти два стовпці, прочитавши наведені вище методи. Однак у деяких випадках вам може знадобитися порівняти два діапазони (дві серії з кількома стовпцями). Ви можете використовувати вищезазначені методи (формули або умовне форматування), щоб порівняти їх стовпчик за стовпцем, але тут представлений зручний інструмент - Kutools для Excel може швидко вирішити цю роботу в різних випадках за допомогою безкоштовних формул.
Клацніть, щоб завантажити зразок файлу
11.1 Порівняйте два діапазони за клітинками
Ось два діапазони, які потрібно порівняти за клітинками, ви можете використовувати Виберіть однакові та різні клітини утиліта Kutools для Excel для обробки.
після безкоштовна установка Kutools для Excel, будь-ласка, виконайте наведені нижче дії:
1. клацання Кутулс > вибрати > Виберіть однакові та різні клітини.
2. У вискакуванні Виберіть однакові та різні клітини діалогове вікно, виконайте вказані нижче дії
1) У Знайти значення в розділу, виберіть діапазон, який ви хочете дізнатись, збіги чи відмінності після порівняння двох діапазонів.
2) У Відповідно до розділ, виберіть інший діапазон, що використовується для порівняння діапазону.
3) В На основі розділ, вибрати Одиночна комірка.
4) Потім у знайти розділ, виберіть тип комірок, які потрібно виділити або виділити.
5) У Обробка результатів розділу, ви можете виділити комірки кольором фона заливки або кольором шрифту, якщо виділення не потрібно, не встановлюйте прапорці.
3. клацання Ok. Вискакує діалогове вікно і нагадує, скільки клітинок / рядків було вибрано, натисніть OK щоб закрити його. Тепер комірки, які відрізняються від тих, що знаходяться в іншому діапазоні, були виділені та виділені.
Виділення тих самих значень
Зауваження
Якщо ви хочете порівняти два діапазони за рядками, ви також можете застосувати Виберіть однакові та різні клітини функцію, але в цьому випадку виберіть Кожен ряд варіант.
11.2 Порівняйте два діапазони, якщо дані в одному порядку
Якщо ви хочете порівняти два діапазони за рядками, то Функція порівняння клітин Kutools для Excel може вам допомогти.
після безкоштовна установка Kutools для Excel, будь-ласка, виконайте наведені нижче дії:
Припустимо, діапазон F2: H7 є моделлю, тепер ви хочете знайти, чи дані в діапазоні B2: D7 знаходяться в правильному порядку відповідно до діапазону F2: H7.
1. клацання Кутулс > Порівняйте клітини.
2 В Порівняйте клітини діалогове вікно, встановлене, як показано нижче:
1) Виберіть два діапазони в Знайти значення в і Відповідно до коробки окремо.
2) Виберіть тип комірки, який потрібно виділити в знайти .
3) Виберіть тип підсвічування в Обробка результатів .
3. клацання Ok. Вискакує діалогове вікно і нагадує, скільки клітинок було вибрано, клацніть OK щоб закрити його. Тепер комірки, які відрізняються від тих, що знаходяться в іншому діапазоні, були виділені та виділені.
Вас також може зацікавити |
|
Порівняйте дати, більші за інші дати в Excel Порівняйте два аркуші поруч Vlookup для порівняння двох списків на окремих робочих аркушах Знайти, виділити, відфільтрувати, порахувати, видалити дублікати в Excel |
Найкращі інструменти для підвищення продуктивності офісу
Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%
- Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
- Об’єднати клітинки / рядки / стовпці та Ведення даних; Вміст розділених комірок; Поєднуйте повторювані рядки та суму / середнє... Запобігання дублюючим клітинам; Порівняйте діапазони...
- Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
- Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
- Улюблені та швидко вставлені формули, Діапазони, діаграми та зображення; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
- Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
- Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
- Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
- Групування зведеної таблиці за номер тижня, день тижня та багато іншого ... Показати розблоковані, заблоковані клітини за різними кольорами; Виділіть клітини, які мають формулу / назву...
- Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
- Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
- Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
