Това wikiHow ви учи как да използвате условно форматиране, за да сравните всички клетки в два отделни списъка в електронна таблица на Excel и да маркирате клетките, които се появяват в двата списъка. Тази функция е налична само в настолната версия на Excel и мобилното приложение не я поддържа.
Стъпки
Метод 1 от 2: Използване на формулата CountIf
Стъпка 1. Отворете електронната таблица на Excel, която искате да редактирате
Намерете файла с електронни таблици със списъците, които искате да сравните, и щракнете двукратно върху файла, за да го отворите в Microsoft Excel.
Стъпка 2. Изберете първия си списък
Щракнете върху първата клетка в първия си списък и плъзнете мишката чак до последната клетка на списъка, за да изберете целия диапазон от данни.
Стъпка 3. Щракнете върху раздела Формули на лентата с инструменти
Можете да намерите този раздел над лентата с инструменти в горната част на електронната таблица. Той ще отвори вашите инструменти за формули на лентата с инструменти.
Стъпка 4. Щракнете върху Дефиниране на име в лентата с инструменти
Можете да намерите тази опция в средата на лентата „Формули“. Той ще отвори нов изскачащ прозорец и ще ви позволи да дадете име на вашия списък.
Стъпка 5. Въведете List1 в полето Име
Щракнете върху текстовото поле в горната част на изскачащия прозорец и въведете име на списък тук.
- По -късно можете да използвате това име, за да вмъкнете списъка си във формулата за сравнение.
- Като алтернатива можете да дадете на списъка си различно име тук. Например, ако това е списък с местоположения, можете да го кръстите „locations1“или „locationList“.
Стъпка 6. Щракнете върху OK в изскачащия прозорец
Това ще потвърди действието ви и ще даде име на вашия списък.
Стъпка 7. Назовете втория си списък като List2
Следвайте същите стъпки като първия списък и дайте име на втория списък. Това ще ви позволи бързо да използвате този втори списък във формулата за сравнение по -късно.
Можете да дадете на списъка всяко име, което искате. Не забравяйте да запомните или запишете името, което давате на всеки от вашите списъци тук
Стъпка 8. Изберете първия си списък
Щракнете върху първата клетка в първия списък и плъзнете надолу, за да изберете целия диапазон от данни.
Уверете се, че първият ви списък е избран, преди да започнете да настройвате условното си форматиране
Стъпка 9. Щракнете върху раздела Начало в лентата с инструменти
Това е първият раздел в горния ляв ъгъл на лентата с инструменти. Той ще отвори основните ви инструменти за електронни таблици в лентата с инструменти.
Стъпка 10. Щракнете върху Условно форматиране в лентата с инструменти
Тази опция изглежда като малка икона на електронна таблица с някои клетки, подчертани в червено и синьо. Ще се отвори падащо меню с всички опции за форматиране.
Стъпка 11. Щракнете върху Ново правило в падащото меню
Той ще отвори нов изскачащ прозорец и ще ви позволи ръчно да настроите ново правило за форматиране за избрания диапазон.
Стъпка 12. Изберете опцията „Използвайте формула, за да определите кои клетки да форматирате“
Тази опция ще ви позволи да въведете ръчно формула за форматиране, за да сравните двата ви списъка.
- На Windows, ще го намерите в долната част на списъка с правила в полето „Избор на тип правило“.
- На Mac, изберете Класически в падащото меню „Стил“в горната част на изскачащия прозорец. След това намерете тази опция във второто падащо меню под менюто Стил.
Стъпка 13. Щракнете върху полето за формула в изскачащия прозорец
Можете да въведете всяка валидна формула на Excel тук, за да зададете правило за условно форматиране.
Стъпка 14. Въведете = countif (List2, A1) = 1 в лентата с формули
Тази формула ще сканира двата ви списъка и ще маркира всички клетки в първия ви списък, които също се появяват във втория списък.
- Заменете A1 във формулата с номера на първата клетка от вашия първи списък.
- Например, ако първата клетка от първия ви списък е клетка D5, тогава формулата ви ще изглежда като = coutif (List2, D5) = 1.
- Ако сте дали друго име на втория си списък, не забравяйте да замените List2 във формулата с действителното име на вашия собствен списък.
- Друга възможност е да промените формулата на = countif (List2, A1) = 0, ако искате да маркирате клетките, които Недей се появяват във втория списък.
Стъпка 15. Въведете = countif (List1, B1) = 1 във формулната лента (по избор)
Ако искате да намерите и маркирате клетките във вашия второ списък, който също се появява в първия списък, използвайте тази формула вместо първата.
Заменете List1 с името на вашия първи списък и B1 с първата клетка на вашия втори списък
Стъпка 16. Изберете персонализиран формат, за да маркирате клетките (по избор)
Можете да изберете персонализиран цвят на запълване на фона и различни стилове на шрифта, за да маркирате клетките, които вашата формула намира.
- На Windows, щракнете върху Формат бутон в долния десен ъгъл на изскачащия прозорец. Можете да изберете цвят на фона в раздела „Запълване“и стилове на шрифта в раздела „Шрифт“.
- На Mac, изберете предварително зададен формат в падащото меню „Форматиране с“в долната част. Можете също да изберете персонализиран формат тук, за да изберете ръчно стилове за запълване на фон и шрифт.
Стъпка 17. Щракнете върху OK в изскачащия прозорец
Това ще потвърди и приложи формулата ви за сравнение. Всички клетки в първия ви списък, които също се появяват във втория списък, ще бъдат маркирани с избрания от вас цвят и шрифт.
- Например, ако изберете светлочервено запълване с тъмночервен текст, всички повтарящи се клетки ще се обърнат към този цвят в първия ви списък.
- Ако използвате втората формула по -горе, условното форматиране ще маркира повтарящите се клетки във вашия втори списък вместо първия.
Метод 2 от 2: Използване на формулата VLookup
Стъпка 1. Отворете електронната си таблица в Excel
Намерете файла Excel със списъците, които искате да сравните, и щракнете двукратно върху името на файла или иконата, за да отворите електронната таблица в Microsoft Excel.
Стъпка 2. Щракнете върху празната клетка до първия елемент от вашия втори списък
Намерете втория си списък в електронната таблица и щракнете върху празната клетка до първия елемент от списъка в горната част.
- Можете да вмъкнете формулата си VLookup тук.
- Като алтернатива можете да изберете всяка празна клетка в електронната си таблица. Тази клетка само ще ви направи по -удобно да видите сравнението си до втория списък.
Стъпка 3. Въведете = vlookup (в празната клетка
Формулата VLookup ще ви позволи да сравните всички елементи в два отделни списъка и да видите дали дадена стойност е повтаряща се или нова стойност.
Не затваряйте скобите на формулата, докато формулата ви не бъде завършена
Стъпка 4. Изберете първия елемент от вашия втори списък
Без да затваряте скобите на формулата, щракнете върху първия елемент от вашия втори списък. Това ще добави първата клетка на вашия втори списък във формулата.
Стъпка 5. Въведете а, запетая във формулата
След като изберете първата клетка от вашия втори списък, въведете запетая във формулата. След това ще можете да изберете диапазона за сравнение.
Стъпка 6. Задръжте и изберете целия си първи списък
Това ще вмъкне диапазона от клетки на първия ви списък във втората част на формулата VLookup.
Това ще ви позволи да търсите в първия списък за избрания елемент от вашия втори списък (първият елемент в горната част на втория списък) и да върнете, ако това е повтаряща се или нова стойност
Стъпка 7. Въведете а, запетая във формулата
Това ще заключи диапазона за сравнение във вашата формула.
Стъпка 8. Въведете 1 във формулата след запетаята
Това число представлява индексния номер на колоната. Той ще подкани формулата VLookup да търси в колоната на действителния списък вместо в друга колона до нея.
Ако искате формулата ви да върне стойността от колоната точно до първия ви списък, въведете 2 тук
Стъпка 9. Въведете а, запетая във формулата
Това ще заключи индексния номер на колоната (1) във формулата VLookup.
Стъпка 10. Въведете FALSE във формулата
Това ще търси в списъка за точно съвпадение на избрания елемент за търсене (първият елемент в горната част на втория списък) вместо приблизителни съвпадения.
- Вместо FALSE можете да използвате 0, това е абсолютно същото.
- Като алтернатива можете да въведете TRUE или 1, ако искате да търсите приблизително съвпадение.
Стъпка 11. Въведете) в края, за да затворите формулата
Вече можете да стартирате формулата си и да видите дали избраният елемент за търсене във втория ви списък е повтаряща се или нова стойност.
Например, ако вторият ви списък започва от В1 и първият ви списък преминава от клетки А1 до А5, формулата ви ще изглежда така = vlookup (B1, $ A $ 1: $ A $ 5, 1, невярно).
Стъпка 12. Натиснете ↵ Enter или Върнете се на клавиатурата си.
Това ще стартира формулата и ще търси в първия ви списък за първия елемент от вашия втори списък.
- Ако това е повтаряща се стойност, ще видите същата стойност, отпечатана отново във клетката с формулата.
- Ако това е нова стойност, ще видите „ #Н/Д"отпечатано тук.
- Например, ако търсите „Джон“в първия списък и сега виждате „Джон“във клетката с формула, това е повтаряща се стойност, която се появява в двата списъка. Ако видите „#N/A“, това е нова стойност във втория списък.
Стъпка 13. Изберете клетката с формула
След като стартирате формулата си и видите резултатите си за първия елемент от списъка, щракнете върху клетката с формулата, за да я изберете.
Стъпка 14. Щракнете и плъзнете надолу зелената точка в долния десен ъгъл на клетката
Това ще разшири клетката ви с формула по списъка и ще приложи формулата към всеки елемент от списъка във вашия втори списък.
- По този начин можете да сравните всеки елемент от вашия втори списък с целия си първи списък.
- Това ще търси в първия ви списък за всеки елемент от вашия втори списък поотделно и ще покаже резултата до всяка клетка поотделно.
- Ако искате да видите различен маркер за нови стойности вместо "#N/A", използвайте тази формула: = iferror (vlookup (B1, $ A $ 1: $ A $ 5, 1, false), "Нова стойност"). Това ще отпечата „Нова стойност“за нови стойности вместо „#N/A“.
