Как да сравняваме две колони в Excel - Методи за сравнение на данни в Excel

Това е глава от книгата: Майкъл Гирвин. Ctrl + Shift + Enter. Овладяване на формули за масиви в Excel.

Проби въз основа на едно или повече условия.Ред функции на Excel използвайте оператори за сравнение. Например SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF и AVERAGEIFS. Тези функции правят селекции въз основа на едно или повече условия (критерии). Проблемът е, че тези функции могат само да добавят, броят и осредняват. Какво ще стане, ако искате да наложите условия на вашето търсене, като максимална стойност или стандартно отклонение? В тези случаи, тъй като няма вградена функция, трябва да измислите формула на масив. Това често се дължи на използването на оператора за сравнение на масиви. Първият пример в тази глава показва как да се изчисли минималната стойност при едно условие.

Нека използваме функцията IF, за да изберем елементи от масив, които отговарят на условие. На фиг. 4.1 в лявата таблица има колона с имена на градове и колона с време. Изисква се да се намери минималното време за всеки град и да се постави тази стойност в съответната клетка в дясната таблица. Условието за избора е името на града. Ако използвате функцията MIN, можете да намерите минималната стойност за колона B. Но как да изберете само тези числа, които са специфични за Oakland? И как копирате формулите надолу по колоната? Тъй като Excel няма вградена функция MINESLI, трябва да напишете оригинална формула, която комбинира функциите IF и MIN.

Фигура: 4.1. Цел на формулата: изберете минималното време за всеки град

Изтеглете бележка във формат или формат

Както е показано на фиг. 4.2, трябва да започнете да въвеждате формулата в клетка E3 с функцията MIN. Но не можете да спорите номер 1 всички стойности в колона Б!? Искате да изберете само онези стойности, които са специфични за Окланд.

Както е показано на фиг. 4.3, в следващата стъпка въведете функцията IF като аргумент номер 1 за МИН. Поставяте IF в MIN.

Като поставите курсора там, където е въведен аргументът log_expression функция IF (Фиг. 4.4), избирате диапазон с имена на градове A3: A8 и след това натискате F4, за да направите абсолютните препратки към клетки (вижте например за повече подробности). След това въвеждате оператора за сравнение, знакът за равенство. И накрая, ще изберете клетката вляво от формулата - D3, запазвайки относителната препратка към нея. Формулираното условие ще ви позволи да изберете само Aucklands, когато гледате диапазона A3: A8.


Фигура: 4.4. Създайте оператор на масив върху аргумент log_expression функции АКО

Така че сте създали оператор на масив, използвайки оператора за сравнение. По всяко време по време на обработката на масив, операторът на масив е оператор за сравнение, така че резултатът от неговата работа ще бъде масив от TRUE и FALSE стойности. За да проверите това, изберете масива (за да направите това, щракнете върху подсказката върху аргумента log_expression) и натиснете F9 (фиг. 4.5). Обикновено използвате един аргумент log_expression, връщане или TRUE или FALSE; тук полученият масив ще върне множество стойности TRUE и FALSE, така че функцията MIN ще избере само минималния брой за градове, които съответстват на стойността TRUE.


Фигура: 4.5. За да видите масив от TRUE и FALSE стойности, щракнете върху аргумента в подсказката log_expression и натиснете F9

Имаме две таблици с поръчки, копирани в един работен лист. Необходимо е да сравните данните на две таблици в Excel и да проверите кои позиции са в първата таблица, но не и във втората. Няма смисъл ръчно да се сравнява стойността на всяка клетка.

Сравнете две колони за съвпадения в Excel

Как да направя сравнение на стойности в Excel на две колони? За да разрешите този проблем, препоръчваме да използвате условно форматиранеза бързо маркиране на елементи, които са само в една колона. Работен лист с таблици:

Първата стъпка е да присвоите имена на двете таблици. Това улеснява разбирането кои диапазони клетки се сравняват:

  1. Изберете инструмента ФОРМУЛИ - Дефинирани имена - Присвояване на име.
  2. В прозореца, който се появява, в полето "Име:" въведете стойността - Таблица_1.
  3. Щракнете с левия бутон върху полето за въвеждане „Обхват:“ и изберете обхвата: A2: A15. И щракнете върху OK.


За втория списък направете същото, но задайте име - Таблица_2. И посочете диапазона C2: C15 - съответно.

Полезен съвет! Имената на диапазони могат да бъдат присвоени по-бързо, като се използва полето за име. Той се намира вляво от лентата с формули. Просто изберете диапазоните от клетки и в полето за име въведете подходящото име за диапазона и натиснете Enter.

Сега нека използваме условно форматиране, за да сравним два списъка в Excel. Трябва да получим следния резултат:

Позициите, които са в Таблица_1, но не в Таблица_2, ще бъдат показани в зелено. В същото време позициите, разположени в Таблица_2, но отсъстващи в Таблица_1, ще бъдат маркирани в синьо.

Принцип на сравняване на данни от две колони в Excel

Използвахме функцията COUNTIF, за да определим условията за форматиране на клетките на колоните. В този пример тази функция проверява колко пъти стойността на втория аргумент (например А2) се появява в списъка на първия аргумент (например Таблица_2). Ако броят на пъти \u003d 0, формулата връща TRUE. В този случай на клетката се присвоява персонализиран формат, посочен в опциите за условно форматиране. Препратката във втория аргумент е относителна, което означава, че всички клетки от избрания диапазон ще бъдат проверени на свой ред (например A2: A15). Втората формула работи по подобен начин. Същият принцип може да се приложи към различни подобни задачи. Например, за сравняване на две цени в Excel, дори

Когато работите в Excel, доста често срещана задача е да сравнявате различни видове списъци със стойности. Стандартните инструменти на Excel като условно форматиране и функции могат да се използват за сравняване на диапазони от стойности като цяло и по-специално колони със стойности. Освен това можете да решавате такива задачи, като използвате VBA макроси и добавки за Excel въз основа на тях.

Необходимостта от сравняване на обхвати като цяло и по-специално колони възниква при актуализиране на различни отчети, списъци и бази данни, генерирани в Excel и изискващи промени във времето.IN интернет мрежи описани са доста примери, които използват различни функции (формули) и условно форматиране за сравняване на стойности в колони. За тези, за които такива методи не са подходящи по някаква причина, можете да използвате друг инструмент, който ви позволява да сравнявате както колони, така и всякакви диапазони от стойности, състоящи се от произволен брой редове и колони. Използвайки добавката за Excel, можете да сравните всеки два диапазона или, по-точно, да сравните всеки елемент от един диапазон с всеки елемент от другия и да намерите както съвпаденията, така и разликите между тях по ваш избор.

Добавка за сравнение на Excel за стойности в два диапазона

Добавката, която ще бъде разгледана по-долу, е предназначена да сравнява стойности в два диапазона, състоящи се от произволен брой редове и колони. Тази добавка използва процедура за сравняване на всеки елемент в един диапазон с всеки елемент в друг диапазон, определяне на кои елементи в тези диапазони са еднакви и кои са различни и запълва клетките с посочен от потребителя цвят.

Както можете да видите от диалоговия прозорец, всичко е съвсем просто, потребителят избира два диапазона за сравнение, определя какво да търси, различни стойности или едни и същи, избира цвят и стартира програмата. Крайният резултат от използването на добавката е запълване на клетки със специфичен цвят, показващ разлики или припокривания в стойностите на избраните диапазони.

Има един нюанс при сравняване на стойности в Excel. Числата могат да бъдат форматирани като текст, който не винаги е визуално разпознаваем (). Тоест, число в Excel може да бъде или числова стойност, или текстова стойност и тези две стойности не са равни една на друга. Много често това явление причинява различни видове грешки. За да се изключат такива грешки, се използва опцията „Сравняване на числата като текст“, която е активирана по подразбиране. Използването на тази опция ви позволява да сравнявате не числови стойности, а текст, преобразуван от тях.

Добавката позволява:

1. С едно щракване на мишката отворете диалоговия прозорец на макроса директно от лентата с инструменти на Excel;

2. намерете елементи от диапазон # 1, които не са в диапазон # 2;

3. намерете елементи от диапазон # 2, които не са в диапазон # 1;

4. намерете елементи от диапазон # 1, които са в диапазон # 2;

5. намерете елементи от диапазон # 2, които са в диапазон # 1;

6. изберете един от деветте цвята на запълване за клетките с желаните стойности;

7. бързо изберете диапазони, като използвате опцията "Ограничаване на диапазони", докато можете да изберете цели редове и колони, като намаляването на избрания диапазон до използвания се извършва автоматично;

8. вместо да сравнявате числови стойности, използвайте сравняване на текстови стойности, като използвате опцията „Сравняване на числата като текст“;

9. сравнява стойностите в клетките на диапазона, без да се вземат предвид допълнителните интервали;

10. Сравнете стойностите в клетки от диапазон по начин, нечувствителен към регистъра.

Как да сравня две колони с помощта на макрос (добавка) за Excel?

Сравнението на колони е специален случай на сравняване на произволни диапазони. В диапазони №1 и №2 изберете две колони и можете да изберете точно колоните, а не да плъзнете рамката за избор над диапазоните с клетки с мишката (за удобство опцията „Limit Ranges“ е активирана по подразбиране, селекцията от използвания диапазон), изберете необходимото действие, за да намерите разлики или съвпадения, изберете цвета на запълване на клетките и стартирайте програмата. По-долу можете да видите резултата от търсенето на съвпадащи стойности в две колони.

Възможността за сравняване на два набора от данни в Excel често е полезна за хората, които обработват големи количества данни и работят с огромни таблици. Например, сравнението може да се използва в, правилността на въвеждане на данни или въвеждане на данни в таблица навреме. Статията по-долу описва няколко техники за сравняване на две колони с данни в Excel.

Използване на условен оператор IF

Методът за използване на условния оператор IF се различава по това, че за сравняване на две колони се използва само частта, необходима за сравнение, а не целият масив. По-долу са стъпките за изпълнение този метод:

Поставете двете колони за сравнение в колони A и B на работния лист.

В клетка C2 въведете следната формула \u003d IF (ISERROR (ТЪРСЕНЕ (A2; $ B $ 2: $ B $ 11; 0)); ""; A2) и я разширете до клетка C11. Тази формула търси последователно всеки елемент от колона A в колона B и връща стойността на елемента, ако е намерена в колона B.


Използване на формулата за заместване на VLOOKUP

Принципът на формулата е подобен на предишния метод, разликата се крие в, вместо в ТЪРСЕНЕ. Отличителна черта на този метод е и възможността за сравняване на два хоризонтални масива, използвайки формулата HLP.

За да сравните две колони с данните в колони A и B (подобно на предишния метод), въведете следната формула \u003d VLOOKUP (A2; $ B $ 2: $ B $ 11; 1; 0) в клетка C2 и я плъзнете до клетка C11.


Тази формула сканира всеки елемент от базовия масив в сравнения масив и връща стойността му, ако е намерена в колона Б. В противен случай Excel ще върне грешка # N / A.

Използване на VBA Macro

Използването на макроси за сравняване на две колони обединява процеса и намалява времето за подготовка на данните. Решението за това кой резултат от сравнението трябва да бъде показан зависи изцяло от вашето въображение и умения за използване на макроси. По-долу е техниката, публикувана на официалния уебсайт на Microsoft.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

Sub Find_Matches ()
Dim CompareRange As Variant, x As Variant, y As Variant
"Задаване на променливата CompareRange, равна на сравнения обхват
Задайте CompareRange \u003d Range ("B1: B11")
"Ако диапазонът, който се сравнява, е на различен лист или работна книга,
"използвайте следния синтаксис
"Set SetRange \u003d Workbooks (" Book2 "). _
"Работни листове (" Sheet2 "). Обхват (" B1: B11 ")
"
Msgstr "Сравняване на всеки елемент от избрания диапазон с всеки елемент
"променлива CompareRange
За всеки x в избор
За всеки y в CompareRange
Ако x \u003d y Тогава x.Offset (0, 2) \u003d x
Следващия y
Следващ x
Крайна под

В този код на променливата CompareRange се присвоява диапазон със сравнения масив. След това изпълнява цикъл, който прелиства през всеки елемент от избрания диапазон и го сравнява с всеки елемент от диапазона, който се сравнява. Ако са намерени елементи със същите стойности, макросът въвежда стойността на елемента в колона В.

За да използвате макроса, върнете се на работния лист, изберете основния диапазон (в нашия случай това са клетки A1: A11), натиснете Alt + F8. В диалоговия прозорец, който се появява, изберете макроса Намирам_Мачовеи щракнете върху бутона за изпълнение.


След изпълнение на макроса резултатът трябва да бъде както следва:

Използване на добавката Inquire

Резултат

И така, разгледахме няколко начина за сравняване на данни в Excel, които ще ви помогнат да разрешите някои аналитични проблеми и ще улесните намирането на дублиращи се (или уникални) стойности.

При сравняване на няколко сравними елемента в Електронни таблици в Excel, данните често са организирани в колони, така че да е удобно да се сравняват характеристиките на тези обекти ред по ред. Например модели автомобили, телефони, експериментални и контролни групи, редица търговски вериги и др. При голям брой линии визуалният анализ не може да бъде надежден. Функции VLOOKUP, INDEX, SEARCH (VLOOKUP, INDEX, MATCH) са удобни за сравняване на данни между клетките и не дават цялостна картина. Как да разберете колко сходни са колоните като цяло? Идентични ли са колоните?

Добавката Map Columns ви позволява да картографирате колони и да видите голямата картина:

  • Сравнете две или повече колони помежду си
  • Сравнете колоните с референтни стойности
  • Изчислете точния процент на съвпадение
  • Представете резултата във визуална обобщена таблица

Език на видеото: английски. Субтитри: руски, английски. (Внимание: видеото може да не отразява последни актуализации... Използвайте инструкциите по-долу.)

Добавете „Колони за съвпадение“ към Excel 2016, 2013, 2010, 2007

Подходящ за: Microsoft Excel 2016 - 2007, настолен Office 365 (32-битова и 64-битова).

Как да работите с добавката:

Как да сравним две или повече колони помежду си и да изчислим процента на съвпадение

Помислете за пример за разработване на продукт. Да предположим, че трябва да сравните няколко готови прототипа помежду си и да разберете колко сходни, различни и евентуално дори идентични са те.

  1. Щракнете върху бутона Колони на картата в лентата с инструменти XLTools\u003e Избор на колони на карта един към друг.

  3. Щракнете върху OK\u003e


Съвет:
Моля изберете обобщена таблица резултат\u003e Кликнете върху иконата Експресен анализ\u003e Приложете "Цветова скала".

Четене на резултата: прототипите Type 1 и Type 3 са почти идентични, 99% процент на съвпадение означава, че 99% от техните параметри в редовете са еднакви. Тип 2 и Тип 4 са най-малко сходни - параметрите им съвпадат само с 30%.

Как да сравняваме колони с референтни стойности и да изчисляваме степента на съвпадение

Помислете за пример за разработване на продукт. Да предположим, че трябва да сравните няколко готови прототипа с определен целеви стандарт и също така да изчислите степента, в която прототипите отговарят на тези стандарти.

  1. Изберете колони за сравнение.
    Например колони с прототипни данни.
  2. Щракнете върху бутона Колони на картата в лентата с инструменти XLTools.
  3. Изберете Map to Reference Column Range\u003e Select Reference Columns.
    Например колони със стандарти.
  4. Поставете отметка „Колоните съдържат заглавия“, ако е така.
  5. Поставете отметка в „Показване на процента на съвпадението“, за да се покаже съответствието като процент.
    В противен случай резултатът се показва като 1 (пълно съвпадение) или 0 (без съвпадение).
  6. Посочете къде трябва да бъде поставен резултатът: на нов лист или на съществуващ лист.
  7. Щракнете върху OK\u003e Finish, резултатът се показва в обобщената таблица.


Съвет: За да улесните интерпретирането на резултата, приложете условно форматиране към него:
Изберете въртящата се таблица на резултата\u003e Щракнете върху иконата за бърз анализ\u003e Прилагане на "Цветова скала".

Четене на резултата: Прототипът тип 2 е на 99% съвместим със стандарт 2, т.е. 99% от техните параметри в низове са еднакви. Продукт 5 е най-близо до Стандарт 3 - 96% от техните параметри са идентични. В същото време Продукт 4 далеч не отговаря на нито един от трите стандарта. Сега можем да заключим колко всеки от прототипите се отклонява от целевите референтни стойности.

С какви задачи ще помогне добавката Match Columns?

Добавката сканира клетките линия по ред и изчислява процента на идентични стойности в колоните. XLTools Match Columns не е подходящ за нормално сравняване на стойности в клетки - не е предназначен за намиране на дубликати или уникални стойности.

Добавката Map Columns има различно предназначение. Тя основната задача - разберете как като цяло наборите от данни (колони) са подобни или различни. Добавката помага при анализа на големи количества данни, когато трябва да погледнете по-широко макро ниво, например. Отговори на следните въпроси:

  • Колко сходни са резултатите на експерименталните групи
  • Колко сходни са резултатите от експерименталната и контролната групи
  • Колко сходни / различни са няколко продукта от една и съща категория
  • Колко близо са KPI на служителите до планираните показатели?
  • Колко сходни са показателите на няколко магазина за търговия на дребно и т.н.


Препоръчваме да прочетете