Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Поиск в столбце исключая искомую ячейку - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск в столбце исключая искомую ячейку (Формулы/Formulas)
Поиск в столбце исключая искомую ячейку
forumyurok Дата: Понедельник, 08.07.2024, 15:23 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

2021
Здравствуйте форумчане!
Есть текстовый столбец B (модели принтеров) в котором могут добавляться или меняться значения; и текстовый столбец C с соответствующими значениями (соответствующий картридж для данной модели принтера).
При добавлении/изменении принтера если он уже есть в столбце B, справа от него должен появится соответствующий картридж, иначе пусто и тогда мы вручную заполним название картриджа.
Т.е. необходима формула в столбце E, которая будет искать введёное/исправленное значение ячейки столбца B (в дальнейшем искомое) во всём столбце B, кроме самой себя.
При нахождении такой ячейки скопировать содержимое соседней с ней ячейки из столбца C и вставить на место соседней с искомой ячейки в столбец С.
Если не найдено то соседняя с искомой ячейка в столбце С очищается.

P.s. у меня всё время возникали проблеммы с поиском - формула искала только до искомой ячейки, а ведь изменения могут быть и в начале и в середине. Office 2013.


Сообщение отредактировал forumyurok - Понедельник, 08.07.2024, 15:36
 
Ответить
СообщениеЗдравствуйте форумчане!
Есть текстовый столбец B (модели принтеров) в котором могут добавляться или меняться значения; и текстовый столбец C с соответствующими значениями (соответствующий картридж для данной модели принтера).
При добавлении/изменении принтера если он уже есть в столбце B, справа от него должен появится соответствующий картридж, иначе пусто и тогда мы вручную заполним название картриджа.
Т.е. необходима формула в столбце E, которая будет искать введёное/исправленное значение ячейки столбца B (в дальнейшем искомое) во всём столбце B, кроме самой себя.
При нахождении такой ячейки скопировать содержимое соседней с ней ячейки из столбца C и вставить на место соседней с искомой ячейки в столбец С.
Если не найдено то соседняя с искомой ячейка в столбце С очищается.

P.s. у меня всё время возникали проблеммы с поиском - формула искала только до искомой ячейки, а ведь изменения могут быть и в начале и в середине. Office 2013.
Автор - forumyurok
Дата добавления - 08.07.2024 в 15:23
DrMini Дата: Понедельник, 08.07.2024, 15:41 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1869
Репутация: 266 ±
Замечаний: 0% ±

Excel LTSC 2024 RUS
Здравствуйте forumyurok.
По мне так название темы не особо вяжется с описанием.
Не уверен, что правильно понял.
Без Вашего файла так:
Код
=ЕСЛИ(B2="";"";ЕСЛИОШИБКА(ИНДЕКС(База!$B$2:$B$16;ПОИСКПОЗ(B2;База!$A$2:$A$16;));"нет в базе"))
К сообщению приложен файл: Toner.xlsx (12.8 Kb)
 
Ответить
СообщениеЗдравствуйте forumyurok.
По мне так название темы не особо вяжется с описанием.
Не уверен, что правильно понял.
Без Вашего файла так:
Код
=ЕСЛИ(B2="";"";ЕСЛИОШИБКА(ИНДЕКС(База!$B$2:$B$16;ПОИСКПОЗ(B2;База!$A$2:$A$16;));"нет в базе"))
Автор - DrMini
Дата добавления - 08.07.2024 в 15:41
forumyurok Дата: Понедельник, 08.07.2024, 17:01 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

2021
Спасибо за оперативный ответ-Вы всё правильно поняли, но возможно ли тоже самое без отдельного создания "База!"
Допустим в таблице есть один устаревший принтер, и если я его удалю из ячейки столбца B, то и в базе он мне и в дальнейшем не нужен (честно говоря если можно без неё, то и хорошо)

А если нельзя, то согласно условию если такого принтера нет в столбце B, то ячейка справа от искомой (в столбце C) должна очищаться, а там :"нет в базе" (в столбце с формулами тоже-но это результат окончания Вашей формулы (исправил на """))
 
Ответить
СообщениеСпасибо за оперативный ответ-Вы всё правильно поняли, но возможно ли тоже самое без отдельного создания "База!"
Допустим в таблице есть один устаревший принтер, и если я его удалю из ячейки столбца B, то и в базе он мне и в дальнейшем не нужен (честно говоря если можно без неё, то и хорошо)

А если нельзя, то согласно условию если такого принтера нет в столбце B, то ячейка справа от искомой (в столбце C) должна очищаться, а там :"нет в базе" (в столбце с формулами тоже-но это результат окончания Вашей формулы (исправил на """))
Автор - forumyurok
Дата добавления - 08.07.2024 в 17:01
bigor Дата: Понедельник, 08.07.2024, 17:26 | Сообщение № 4
Группа: Проверенные
Ранг: Старожил
Сообщений: 1267
Репутация: 244 ±
Замечаний: 0% ±

нет
forumyurok, "База!" это не база а название листа. можете удалять принтер, нигде он больше не останется.
 
Ответить
Сообщениеforumyurok, "База!" это не база а название листа. можете удалять принтер, нигде он больше не останется.
Автор - bigor
Дата добавления - 08.07.2024 в 17:26
DrMini Дата: Вторник, 09.07.2024, 05:51 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 1869
Репутация: 266 ±
Замечаний: 0% ±

Excel LTSC 2024 RUS
Цитата bigor, 08.07.2024 в 17:26, в сообщении № 4 ()
А если нельзя

Думаю можно, но не формулой, а с помощью макроса.
 
Ответить
Сообщение
Цитата bigor, 08.07.2024 в 17:26, в сообщении № 4 ()
А если нельзя

Думаю можно, но не формулой, а с помощью макроса.
Автор - DrMini
Дата добавления - 09.07.2024 в 05:51
forumyurok Дата: Среда, 10.07.2024, 10:19 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

2021
Я почему делаю акцент на формуле для одного листа (без листа База):
Таблица заполняется по мере появления новых принтеров, и не нужно отдельным действием заполнять ещё и второй лист База, да ещё и следить чтобы в нём не было повторения - актуально для большого объёма данных(парка принтеров/картриджей).

Код
=ЕСЛИ(B2=АДРЕС(СТРОКА(); СТОЛБЕЦ(); 4); ""; ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$50; СРАВНИТЬ(B2; $B$2:$B$50; 0)); ""))

АДРЕС(СТРОКА(); СТОЛБЕЦ(); 4) генерирует адрес текущей ячейки в текстовом формате, и если он совпадает с содержимым ячейки $B2, то результат будет пустой строкой. В противном случае выполняется исходная функция ИНДЕКС/СРАВНИТЬ. Это предотвратит поиск значения в самой ячейке, в которой находится формула.
Но походу не предотвращает, как я и упоминал Выше - т.е. ищет только до самой искомой ячейки...


Сообщение отредактировал forumyurok - Среда, 10.07.2024, 12:24
 
Ответить
СообщениеЯ почему делаю акцент на формуле для одного листа (без листа База):
Таблица заполняется по мере появления новых принтеров, и не нужно отдельным действием заполнять ещё и второй лист База, да ещё и следить чтобы в нём не было повторения - актуально для большого объёма данных(парка принтеров/картриджей).

Код
=ЕСЛИ(B2=АДРЕС(СТРОКА(); СТОЛБЕЦ(); 4); ""; ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$50; СРАВНИТЬ(B2; $B$2:$B$50; 0)); ""))

АДРЕС(СТРОКА(); СТОЛБЕЦ(); 4) генерирует адрес текущей ячейки в текстовом формате, и если он совпадает с содержимым ячейки $B2, то результат будет пустой строкой. В противном случае выполняется исходная функция ИНДЕКС/СРАВНИТЬ. Это предотвратит поиск значения в самой ячейке, в которой находится формула.
Но походу не предотвращает, как я и упоминал Выше - т.е. ищет только до самой искомой ячейки...
Автор - forumyurok
Дата добавления - 10.07.2024 в 10:19
bigor Дата: Среда, 10.07.2024, 10:28 | Сообщение № 7
Группа: Проверенные
Ранг: Старожил
Сообщений: 1267
Репутация: 244 ±
Замечаний: 0% ±

нет
forumyurok, мы не знаем как заполняется ваша таблица, файл пример вы не приложили. DrMini, предложил свой вариант, но повашим ответам что-то не угадал. Приложите свой файл и тогда уже будем плясать от него

Если имеем только Лист1, то вариант
Код
=IF(B2="";"";IFERROR(INDEX($C$2:$C2;MATCH(B2;$B$2:$B2;));""))
прописываем в C2 и протягиваем вниз. Затем заполняем значениями сверху, затирая формулу.

[offtop]и формулу приведите в порядок согласно правилам форума.[/offtop]

Выделяете формулу и жмёте на выделенный значок
К сообщению приложен файл: 2060874.png (6.9 Kb)


Сообщение отредактировал bigor - Среда, 10.07.2024, 10:42
 
Ответить
Сообщениеforumyurok, мы не знаем как заполняется ваша таблица, файл пример вы не приложили. DrMini, предложил свой вариант, но повашим ответам что-то не угадал. Приложите свой файл и тогда уже будем плясать от него

Если имеем только Лист1, то вариант
Код
=IF(B2="";"";IFERROR(INDEX($C$2:$C2;MATCH(B2;$B$2:$B2;));""))
прописываем в C2 и протягиваем вниз. Затем заполняем значениями сверху, затирая формулу.

[offtop]и формулу приведите в порядок согласно правилам форума.[/offtop]

Выделяете формулу и жмёте на выделенный значок
Автор - bigor
Дата добавления - 10.07.2024 в 10:28
forumyurok Дата: Среда, 10.07.2024, 12:07 | Сообщение № 8
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

2021
Таблица с вышеупомянутой, не до конца работающей, формулой.
К сообщению приложен файл: printera_kartridzhi.xlsm (23.3 Kb)


Сообщение отредактировал forumyurok - Среда, 10.07.2024, 12:21
 
Ответить
СообщениеТаблица с вышеупомянутой, не до конца работающей, формулой.
Автор - forumyurok
Дата добавления - 10.07.2024 в 12:07
bigor Дата: Среда, 10.07.2024, 12:27 | Сообщение № 9
Группа: Проверенные
Ранг: Старожил
Сообщений: 1267
Репутация: 244 ±
Замечаний: 0% ±

нет
Похоже я тоже не угадал
Пробуйте
Код
=IF(B2="";"";IFERROR(INDEX($C$2:$C2;MATCH(B2;$B$2:$B2;));" "))
 
Ответить
СообщениеПохоже я тоже не угадал
Пробуйте
Код
=IF(B2="";"";IFERROR(INDEX($C$2:$C2;MATCH(B2;$B$2:$B2;));" "))
Автор - bigor
Дата добавления - 10.07.2024 в 12:27
forumyurok Дата: Среда, 10.07.2024, 12:42 | Сообщение № 10
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

2021
Цитата bigor, 10.07.2024 в 12:27, в сообщении № 9 ()

Код
=ЕСЛИ(B2="";"";ЕСЛИОШИБКА(ИНДЕКС($C$2:$C2;ПОИСКПОЗ(B2;$B$2:$B2;));" "))

прописываем в C2 и протягиваем вниз. Затем заполняем значениями сверху, затирая формулу.


А эти значения сверху затирающие формулу удаляют всю суть задачи/формулы своим затиранием. Ведь если я изменяю в столбце B (в начале/середине/конце) принтер и такой нововведёный принтер у меня в этом столбце уже есть, то картридж ему соответствующий должен подтянуться по формуле (которую мы благополучно затёрли (в начале/середине/конце)...
Потому в моём варианте формула в столбце E.


Сообщение отредактировал forumyurok - Среда, 10.07.2024, 12:47
 
Ответить
Сообщение
Цитата bigor, 10.07.2024 в 12:27, в сообщении № 9 ()

Код
=ЕСЛИ(B2="";"";ЕСЛИОШИБКА(ИНДЕКС($C$2:$C2;ПОИСКПОЗ(B2;$B$2:$B2;));" "))

прописываем в C2 и протягиваем вниз. Затем заполняем значениями сверху, затирая формулу.


А эти значения сверху затирающие формулу удаляют всю суть задачи/формулы своим затиранием. Ведь если я изменяю в столбце B (в начале/середине/конце) принтер и такой нововведёный принтер у меня в этом столбце уже есть, то картридж ему соответствующий должен подтянуться по формуле (которую мы благополучно затёрли (в начале/середине/конце)...
Потому в моём варианте формула в столбце E.
Автор - forumyurok
Дата добавления - 10.07.2024 в 12:42
bigor Дата: Среда, 10.07.2024, 12:48 | Сообщение № 11
Группа: Проверенные
Ранг: Старожил
Сообщений: 1267
Репутация: 244 ±
Замечаний: 0% ±

нет
Цитата forumyurok, 10.07.2024 в 12:42, в сообщении № 10 ()
Потому в моём варианте формула в столбце E
формулу из #9 вставьте в E2 и протяните вниз.
кстати у вас для hp 1010 не введен картридж в строке 12 формула для всех этих принтеров проставит 0, т.к. ориентируется на первую модель в списке.
Блин, уже появился столбец А и добавление происходит не в конец, а в соответствии с этим столбцом. Тогда мой вариант не подходит
А что это у вас
Код
СРАВНИТЬ(B2; $B$2:$B$50; 0)
я такую функцию не знаю. Может ПОИСКПОЗ() надо?


Сообщение отредактировал bigor - Среда, 10.07.2024, 12:55
 
Ответить
Сообщение
Цитата forumyurok, 10.07.2024 в 12:42, в сообщении № 10 ()
Потому в моём варианте формула в столбце E
формулу из #9 вставьте в E2 и протяните вниз.
кстати у вас для hp 1010 не введен картридж в строке 12 формула для всех этих принтеров проставит 0, т.к. ориентируется на первую модель в списке.
Блин, уже появился столбец А и добавление происходит не в конец, а в соответствии с этим столбцом. Тогда мой вариант не подходит
А что это у вас
Код
СРАВНИТЬ(B2; $B$2:$B$50; 0)
я такую функцию не знаю. Может ПОИСКПОЗ() надо?
Автор - bigor
Дата добавления - 10.07.2024 в 12:48
forumyurok Дата: Среда, 10.07.2024, 13:32 | Сообщение № 12
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

2021
СРАВНИТЬ внутри ИНДЕКС определяет позицию из которой ИНДЕКС должен вернуть значение из диапазона $C$2:$C$50. Если значение в B2 найдено, то ИНДЕКС возвращает соответствующее значение из этого диапазона. Если СРАВНИТЬ возвращает ошибку (то есть значение не найдено), функция ЕСЛИОШИБКА заменяет ошибку пустой строкой (“”).
Можно конечно при надобности и ПОИСК'омПОЗ заменить.
Код
=ЕСЛИ(B2=АДРЕС(СТРОКА(); СТОЛБЕЦ(); 4); ""; ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$50; ПОИСКПОЗ(B2; $B$2:$B$50; 0)); ""))


Цитата bigor, 10.07.2024 в 12:48, в сообщении № 11 ()
кстати у вас для hp 1010 не введен картридж в строке 12 формула для всех этих принтеров проставит 0, т.к. ориентируется на первую модель в списке.

Я на этой строке обычно тестирую, и моя формула доходя до неё останавливается - а так как ранее такого принтера не было она затирает ячейку с картриджем, несмотря на то что далее такой принтер есть - т.е. к сожалению останавливает свою работу на искомой ячейке.


Сообщение отредактировал forumyurok - Среда, 10.07.2024, 13:49
 
Ответить
СообщениеСРАВНИТЬ внутри ИНДЕКС определяет позицию из которой ИНДЕКС должен вернуть значение из диапазона $C$2:$C$50. Если значение в B2 найдено, то ИНДЕКС возвращает соответствующее значение из этого диапазона. Если СРАВНИТЬ возвращает ошибку (то есть значение не найдено), функция ЕСЛИОШИБКА заменяет ошибку пустой строкой (“”).
Можно конечно при надобности и ПОИСК'омПОЗ заменить.
Код
=ЕСЛИ(B2=АДРЕС(СТРОКА(); СТОЛБЕЦ(); 4); ""; ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$50; ПОИСКПОЗ(B2; $B$2:$B$50; 0)); ""))


Цитата bigor, 10.07.2024 в 12:48, в сообщении № 11 ()
кстати у вас для hp 1010 не введен картридж в строке 12 формула для всех этих принтеров проставит 0, т.к. ориентируется на первую модель в списке.

Я на этой строке обычно тестирую, и моя формула доходя до неё останавливается - а так как ранее такого принтера не было она затирает ячейку с картриджем, несмотря на то что далее такой принтер есть - т.е. к сожалению останавливает свою работу на искомой ячейке.
Автор - forumyurok
Дата добавления - 10.07.2024 в 13:32
bigor Дата: Среда, 10.07.2024, 14:04 | Сообщение № 13
Группа: Проверенные
Ранг: Старожил
Сообщений: 1267
Репутация: 244 ±
Замечаний: 0% ±

нет
Так у вас офис 2021? зачем тогда всякие индексы если фильтр есть. Но у меня фильтр нет, поэтому еще вариант
Код
=IF(B2=ADDRESS(ROW(); COLUMN(); 4); ""; IFERROR(INDEX($C$2:$C$50; AGGREGATE(15;6; ROW($B$1:$B$49)/($B$2:$B$50=B2)/($C$2:$C$50<>"");1)); ""))
 
Ответить
СообщениеТак у вас офис 2021? зачем тогда всякие индексы если фильтр есть. Но у меня фильтр нет, поэтому еще вариант
Код
=IF(B2=ADDRESS(ROW(); COLUMN(); 4); ""; IFERROR(INDEX($C$2:$C$50; AGGREGATE(15;6; ROW($B$1:$B$49)/($B$2:$B$50=B2)/($C$2:$C$50<>"");1)); ""))
Автор - bigor
Дата добавления - 10.07.2024 в 14:04
bigor Дата: Среда, 10.07.2024, 14:19 | Сообщение № 14
Группа: Проверенные
Ранг: Старожил
Сообщений: 1267
Репутация: 244 ±
Замечаний: 0% ±

нет
Цитата forumyurok, 10.07.2024 в 10:19, в сообщении № 6 ()
АДРЕС(СТРОКА(); СТОЛБЕЦ(); 4) генерирует адрес текущей ячейки в текстовом формате, и если он совпадает с содержимым ячейки $B2
как это адрес совпадает со значением, с вашими данными это почти невероятно, поэтому это условие лишнее
Код
=IFERROR(INDEX($C$2:$C$50; AGGREGATE(15;6; ROW($B$1:$B$49)/($B$2:$B$50=B2)/($C$2:$C$50<>"");1)); "")
 
Ответить
Сообщение
Цитата forumyurok, 10.07.2024 в 10:19, в сообщении № 6 ()
АДРЕС(СТРОКА(); СТОЛБЕЦ(); 4) генерирует адрес текущей ячейки в текстовом формате, и если он совпадает с содержимым ячейки $B2
как это адрес совпадает со значением, с вашими данными это почти невероятно, поэтому это условие лишнее
Код
=IFERROR(INDEX($C$2:$C$50; AGGREGATE(15;6; ROW($B$1:$B$49)/($B$2:$B$50=B2)/($C$2:$C$50<>"");1)); "")
Автор - bigor
Дата добавления - 10.07.2024 в 14:19
forumyurok Дата: Среда, 10.07.2024, 14:34 | Сообщение № 15
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

2021
Ура! Заработало! ("Трое из Простоквашино" 1978)
Спасибо!!! Ещё и код оптимизировал))) - Екселище! (по аналогии с человечище).
 
Ответить
СообщениеУра! Заработало! ("Трое из Простоквашино" 1978)
Спасибо!!! Ещё и код оптимизировал))) - Екселище! (по аналогии с человечище).
Автор - forumyurok
Дата добавления - 10.07.2024 в 14:34
Nic70y Дата: Среда, 10.07.2024, 17:28 | Сообщение № 16
Группа: Друзья
Ранг: Экселист
Сообщений: 9001
Репутация: 2367 ±
Замечаний: 0% ±

Excel 2010
forumyurok, ну раз у Вас есть макрос, подсуну Вам Юзерорму,
срабатывает при стирании ячейки в столбце C
К сообщению приложен файл: 356.xlsm (26.9 Kb)

ЮMoney 41001841029809
 
Ответить
Сообщениеforumyurok, ну раз у Вас есть макрос, подсуну Вам Юзерорму,
срабатывает при стирании ячейки в столбце C
Автор - Nic70y
Дата добавления - 10.07.2024 в 17:28
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск в столбце исключая искомую ячейку (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!