Сравнение двух таблиц с выделением отсутствующих значений

Страница 1 из 1 1
Модератор форума: китин, _Boroda_, DrMini

Сравнение двух таблиц с выделением отсутствующих значений (Формулы/Formulas)

vovin_fm

Дата: Понедельник, 27.02.2017, 17:22 | Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 3

Репутация: 0 ±

Замечаний: 0% ±

2016

Добрый день!
Необходимо сравнить колонки в двух таблицах в одной книге. Ячейки в колонках содержат ip-адреса. Нужно выделить цветом те ячейки в таблице на листе 1 которых нет в таблице на листе 2.
Я выделил диапазон на листе 1, создал правило и для форматирования ячеек использовал формулу =СЧЁТЕСЛИ(Лист2!$G:$G;E3)=0 но у меня почему-то выделился цветом весь столбец, хотя совпадения в двух таблицах есть. В чём моя ошибка?
Файл прилагается.

К сообщению приложен файл: 7400271.xlsx (56.5 Kb)

Ответить

bmv98rus

Дата: Понедельник, 27.02.2017, 17:29 | Сообщение № 2

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4147

Репутация: 772 ±

Замечаний: 0% ±

Excel 2013/2016

Может по тому что на листе 2 перед IP пробел имеется?

Код

=СЧЁТЕСЛИ(Лист2!$G:$G;" " & E3)=0

~~Замечательный~~ Временно просто медведь ~~, процентов на 20~~.

Ответить

vovin_fm

Дата: Понедельник, 27.02.2017, 17:38 | Сообщение № 3

Группа: Пользователи

Ранг: Прохожий

Сообщений: 3

Репутация: 0 ±

Замечаний: 0% ±

2016

bmv98rus, Это универсальный код? Даже если там будет несколько пробелов перед и после значения в ячейке.
P.S. Не знал что через "найти и заменить" можно искать не зависимо от того сколько пробелов в ячейке

Ответить

bmv98rus

Дата: Понедельник, 27.02.2017, 17:46 | Сообщение № 4

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4147

Репутация: 772 ±

Замечаний: 0% ±

Excel 2013/2016

Цитата vovin_fm, 27.02.2017 в 17:38, в сообщении № 3 ()

Это универсальный код?

нет. Не зна. как по универсальности в конкретном случае ,ибо может найтись не то что надо, нпримере 127.0.0.1 вместо 27.0.0.1 но вместо " " можете использовать "*".
[p.s.]найти и заменить[/p.s.] так если указали часть значения ячейки при поиске, так и искал.

~~Замечательный~~ Временно просто медведь ~~, процентов на 20~~.

Ответить

Светлый

Дата: Понедельник, 27.02.2017, 17:49 | Сообщение № 5

Группа: Друзья

Ранг: Старожил

Сообщений: 1867

Репутация: 536 ±

Замечаний: 0% ±

Excel 2013, 2016

Или такой вариант (массивка):

Код

=СЧЁТ(ПОИСК(E3;Лист2!G$3:G$922))=0

Но правильнее будет:

Код

=СЧЁТ(ПОИСК(" "&E3&" ";" "&Лист2!G$3:G$922&" "))=0

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Понедельник, 27.02.2017, 18:02

Ответить

TimSha

Дата: Понедельник, 27.02.2017, 18:00 | Сообщение № 6

Группа: Проверенные

Ранг: Ветеран

Сообщений: 627

Репутация: 94 ±

Замечаний: 0% ±

Excel 2013 Pro +

Цитата vovin_fm, 27.02.2017 в 17:22, в сообщении № 1 ()

В чём моя ошибка?

Данные надо готовить к употреблению...

К сообщению приложен файл: 7501309.xlsx (56.5 Kb)

"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)

Ответить

_Boroda_

Дата: Понедельник, 27.02.2017, 18:03 | Сообщение № 7

Группа: Админы

Ранг: Местный житель

Сообщений: 16885

Репутация: 6599 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Еще вариант, отлавливающий похожести типа

Цитата bmv98rus, 27.02.2017 в 17:46, в сообщении № 4 ()

127.0.0.1 вместо 27.0.0.1

Код

=ИЛИ(E3=СЖПРОБЕЛЫ(Лист2!$G$3:$G$999))

Только на весь столбец формулу не пишите

СЖПРОБЕЛЫ можно применить и к Е3, чтобы уж совсем точно было точно было

Код

=ИЛИ(СЖПРОБЕЛЫ(E3)=СЖПРОБЕЛЫ(Лист2!$G$3:$G$999))

К сообщению приложен файл: 7400271_1.xlsx (56.5 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

Сравнение двух таблиц с выделением отсутствующих значений (Формулы/Formulas)

Страница 1 из 1
1