Из этой статьи вы узнаете, как использовать условное форматирование для сравнения всех ячеек в двух отдельных списках в электронной таблице Excel и отметить ячейки, которые появляются в обоих списках. Эта функция доступна только в настольной версии Excel, а мобильное приложение ее не поддерживает.
Шаги
Метод 1 из 2: использование формулы CountIf
Шаг 1. Откройте электронную таблицу Excel, которую хотите отредактировать
Найдите файл электронной таблицы со списками, которые вы хотите сравнить, и дважды щелкните файл, чтобы открыть его в Microsoft Excel.
Шаг 2. Выберите свой первый список
Щелкните первую ячейку в первом списке и перетащите указатель мыши до последней ячейки списка, чтобы выбрать весь диапазон данных.
Шаг 3. Щелкните вкладку Формулы на ленте панели инструментов
Вы можете найти эту вкладку над панелью инструментов в верхней части таблицы. Он откроет ваши инструменты формул на ленте панели инструментов.
Шаг 4. Нажмите «Определить имя» на панели инструментов
Вы можете найти эту опцию посередине ленты «Формулы». Откроется новое всплывающее окно, в котором вы сможете назвать свой список.
Шаг 5. Введите List1 в поле Name
Щелкните текстовое поле в верхней части всплывающего окна и введите здесь имя списка.
- Позже вы можете использовать это имя, чтобы вставить свой список в формулу сравнения.
- В качестве альтернативы вы можете дать своему списку другое имя здесь. Например, если это список местоположений, вы можете назвать его «location1» или «locationList».
Шаг 6. Нажмите ОК во всплывающем окне
Это подтвердит ваше действие и назовет ваш список.
Шаг 7. Назовите второй список как List2
Выполните те же действия, что и в первом списке, и дайте имя второму списку. Это позволит вам позже быстро использовать второй список в формуле сравнения.
Вы можете дать списку любое имя. Обязательно запомните или запишите имя, которое вы даете каждому из ваших списков
Шаг 8. Выберите свой первый список
Щелкните первую ячейку в первом списке и потяните вниз, чтобы выбрать весь диапазон данных.
Прежде чем приступить к настройке условного форматирования, убедитесь, что выбран ваш первый список
Шаг 9. Щелкните вкладку «Главная» на панели инструментов
Это первая вкладка в верхнем левом углу ленты панели инструментов. На панели инструментов откроются ваши основные инструменты для работы с электронными таблицами.
Шаг 10. Щелкните Условное форматирование на панели инструментов
Этот параметр выглядит как крошечный значок электронной таблицы с некоторыми ячейками, выделенными красным и синим цветом. Откроется раскрывающееся меню со всеми вашими параметрами форматирования.
Шаг 11. Щелкните Новое правило в раскрывающемся меню
Откроется новое всплывающее окно, в котором можно вручную настроить новое правило форматирования для выбранного диапазона.
Шаг 12. Выберите параметр «Использовать формулу для определения ячеек для форматирования»
Эта опция позволит вам вручную ввести формулу форматирования для сравнения ваших двух списков.
- На Окна, вы найдете его внизу списка правил в поле «Выберите тип правила».
- На Mac, Выбрать Классический в раскрывающемся списке "Стиль" вверху всплывающего окна. Затем найдите этот параметр во втором раскрывающемся списке под меню «Стиль».
Шаг 13. Щелкните поле формулы во всплывающем окне
Здесь вы можете ввести любую допустимую формулу Excel, чтобы настроить правило условного форматирования.
Шаг 14. Введите = countif (List2, A1) = 1 в строку формул
Эта формула просканирует два ваших списка и отметит все ячейки в первом списке, которые также появятся во втором списке.
- Замените A1 в формуле номером первой ячейки вашего первого списка.
- Например, если первая ячейка вашего первого списка - это ячейка D5, тогда ваша формула будет иметь вид = countif (List2, D5) = 1.
- Если вы дали другое имя своему второму списку, не забудьте заменить List2 в формуле фактическим именем вашего собственного списка.
- В качестве альтернативы измените формулу на = countif (List2, A1) = 0, если вы хотите отметить ячейки, которые не надо появляются во втором списке.
Шаг 15. Введите = countif (List1, B1) = 1 в строку формул (необязательно)
Если вы хотите найти и отметить ячейки на своем второй список, который также появляется в первом списке, используйте эту формулу вместо первой.
Замените List1 именем вашего первого списка, а B1 - первой ячейкой вашего второго списка
Шаг 16. Выберите пользовательский формат для пометки ячеек (необязательно)
Вы можете выбрать собственный цвет заливки фона и разные стили шрифта, чтобы отметить ячейки, которые находит ваша формула.
- На Окна щелкните значок Формат в правом нижнем углу всплывающего окна. Вы можете выбрать цвет фона на вкладке «Заливка» и стили шрифтов на вкладке «Шрифт».
- На Mac, выберите предустановку формата в раскрывающемся списке "Форматировать с" внизу. Вы также можете выбрать нестандартный формат здесь, чтобы вручную выбрать фоновую заливку и стили шрифта.
Шаг 17. Нажмите ОК во всплывающем окне
Это подтвердит и применит вашу формулу сравнения. Все ячейки в первом списке, которые также появляются во втором списке, будут отмечены выбранным цветом и шрифтом.
- Например, если вы выберете светло-красную заливку с темно-красным текстом, все повторяющиеся ячейки станут этого цвета в вашем первом списке.
- Если вы используете вторую формулу выше, условное форматирование будет отмечать повторяющиеся ячейки во втором списке вместо первого.
Метод 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, это точно так же.
- Или вы можете ввести ИСТИНА или 1, если хотите найти приблизительное совпадение.
Шаг 11. Введите) в конце, чтобы закрыть формулу
Теперь вы можете запустить формулу и посмотреть, является ли выбранный элемент поиска во втором списке повторяющимся или новым значением.
Например, если ваш второй список начинается с B1, а ваш первый список идет от ячеек A1 до A5, ваша формула будет выглядеть так: = vlookup (B1; $ A $ 1: $ A $ 5; 1; ложь).
Шаг 12. Нажмите ↵ Enter. или ⏎ Вернитесь на клавиатуру.
Это запустит формулу и выполнит поиск в первом списке первого элемента из второго.
- Если это повторяющееся значение, вы снова увидите то же значение, напечатанное в ячейке формулы.
- Если это новое значение, вы увидите " # N / A"напечатано здесь.
- Например, если вы ищете в первом списке слово «Джон», а теперь видите «Джон» в ячейке формулы, это повторяющееся значение появляется в обоих списках. Если вы видите «# N / A», это новое значение во втором списке.
Шаг 13. Выберите ячейку формулы
После выполнения формулы и просмотра результатов для первого элемента списка щелкните ячейку формулы, чтобы выбрать ее.
Шаг 14. Щелкните и перетащите зеленую точку в правом нижнем углу ячейки
Это расширит ячейку формулы по списку и применит формулу к каждому элементу списка во втором списке.
- Таким образом, вы можете сравнить каждый элемент во втором списке со всем первым списком.
- Это будет искать в вашем первом списке каждый элемент второго списка по отдельности и показывать результат рядом с каждой ячейкой отдельно.
- Если вы хотите видеть другой маркер для новых значений вместо «# N / A», используйте эту формулу: = iferror (vlookup (B1, $ A $ 1: $ A $ 5, 1, false), «New Value»). Это напечатает «Новое значение» для новых значений вместо «# N / A».
