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

Вход

Регистрация

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

 

= Мир MS Excel/Динамическое сравнение диапазонов - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Динамическое сравнение диапазонов (Формулы/Formulas)
Динамическое сравнение диапазонов
Кравченко Дата: Среда, 27.01.2016, 18:32 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Добрый день всем.
Помогите решить проблему.
Суть следующая: на разных компах в разное время берутся в работу коды - номера заявок, после они тасуются в отделах и постепенно определяются на свое место.
Чтобы разные люди видели какие заявки уже поступили в работу у них должна появляться подсказка.

Проблема: необходимо сравнить табл.А и табл.Б, коды из таблБ недостающие в таблА надо отобразить в таблА (добавить) с меткой как напоминание. После введения в той же ячейке кода вручную (без метки) действие формулы прекращается, и код далее обрабатывается таблицей.
Здесь на форуме нашел подходящую формулу ссылка, но довести ее до ума не получается :( ума-то на массивы и не хватает.
Сейчас вроде бы срабатывает хорошо, но после ручного заполнения ячейки список подсказок меняется. И с каждым разом становится на одну позицию меньше.

Помогите подправить формулу!
К сообщению приложен файл: 5802071.xlsx(11Kb)
 
Ответить
СообщениеДобрый день всем.
Помогите решить проблему.
Суть следующая: на разных компах в разное время берутся в работу коды - номера заявок, после они тасуются в отделах и постепенно определяются на свое место.
Чтобы разные люди видели какие заявки уже поступили в работу у них должна появляться подсказка.

Проблема: необходимо сравнить табл.А и табл.Б, коды из таблБ недостающие в таблА надо отобразить в таблА (добавить) с меткой как напоминание. После введения в той же ячейке кода вручную (без метки) действие формулы прекращается, и код далее обрабатывается таблицей.
Здесь на форуме нашел подходящую формулу ссылка, но довести ее до ума не получается :( ума-то на массивы и не хватает.
Сейчас вроде бы срабатывает хорошо, но после ручного заполнения ячейки список подсказок меняется. И с каждым разом становится на одну позицию меньше.

Помогите подправить формулу!

Автор - Кравченко
Дата добавления - 27.01.2016 в 18:32
Pelena Дата: Среда, 27.01.2016, 20:07 | Сообщение № 2
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11493
Репутация: 2557 ±
Замечаний: 0% ±

Excel 2010, 2016 & Mac Excel
Так правильно?
Код
=ЕСЛИОШИБКА("_"&ИНДЕКС($D$1:$D$125;НАИМЕНЬШИЙ(ЕСЛИ((СЧЁТЕСЛИ($A$1:A23;$D$1:$D$125)=0)*($D$1:$D$125<>0);СТРОКА($D$1:$D$125));СЧЁТЕСЛИ($A$1:A23;"_*")+1));"")
К сообщению приложен файл: 3031363.xlsx(11Kb)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеТак правильно?
Код
=ЕСЛИОШИБКА("_"&ИНДЕКС($D$1:$D$125;НАИМЕНЬШИЙ(ЕСЛИ((СЧЁТЕСЛИ($A$1:A23;$D$1:$D$125)=0)*($D$1:$D$125<>0);СТРОКА($D$1:$D$125));СЧЁТЕСЛИ($A$1:A23;"_*")+1));"")

Автор - Pelena
Дата добавления - 27.01.2016 в 20:07
_Boroda_ Дата: Среда, 27.01.2016, 21:39 | Сообщение № 3
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11337
Репутация: 4677 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Еще вариант
Код
=ЕСЛИОШИБКА("_"&НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ(A$1:A23;ЕСЛИ(D$1:D$49;D$1:D$49;A$1))=0;D$1:D$49);СЧЁТЕСЛИ(A$1:A23;"_*")+1);"")


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЕще вариант
Код
=ЕСЛИОШИБКА("_"&НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ(A$1:A23;ЕСЛИ(D$1:D$49;D$1:D$49;A$1))=0;D$1:D$49);СЧЁТЕСЛИ(A$1:A23;"_*")+1);"")

Автор - _Boroda_
Дата добавления - 27.01.2016 в 21:39
Кравченко Дата: Среда, 27.01.2016, 22:10 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Pelena, спасибо. Пока правильно.
:( попытался разобраться... я и так не понимал как работает эта формула... а теперь (СЧЁТЕСЛИ($A$1:A23;$D$1:$D$125)=0)*($D$1:$D$125<>0) %) ... проверка на число с подчеркиванием... тяжко мне с массивами без поэлементного просмотра.
Ладно, работает это уже хорошо. Помогите пожалуйста еще и предусмотреть возможные глюки.
1. Обязательно ли жестко задавать весь столбец А? Через пару лет в этой таблице будет несколько тысяч строк и т.д. Можно ли ограничиться, например, тридцатью строками до текущей строки?
2. Тот же вопрос и по столбцу Б, но его я могу фильтровать до передачи на комп и вопрос уже не столь критичен. Да - да, нет - нет.
3. Как вы думаете, если оставить формулу в таком виде, то на каком кол-ве строк стобца А начнутся тормоза?

Потестировал еще разок. :) допускается даже вариант внесения данных и внутри, и за пределами "подсказок". Похоже, что это полностью рабочий вариант.
 
Ответить
СообщениеPelena, спасибо. Пока правильно.
:( попытался разобраться... я и так не понимал как работает эта формула... а теперь (СЧЁТЕСЛИ($A$1:A23;$D$1:$D$125)=0)*($D$1:$D$125<>0) %) ... проверка на число с подчеркиванием... тяжко мне с массивами без поэлементного просмотра.
Ладно, работает это уже хорошо. Помогите пожалуйста еще и предусмотреть возможные глюки.
1. Обязательно ли жестко задавать весь столбец А? Через пару лет в этой таблице будет несколько тысяч строк и т.д. Можно ли ограничиться, например, тридцатью строками до текущей строки?
2. Тот же вопрос и по столбцу Б, но его я могу фильтровать до передачи на комп и вопрос уже не столь критичен. Да - да, нет - нет.
3. Как вы думаете, если оставить формулу в таком виде, то на каком кол-ве строк стобца А начнутся тормоза?

Потестировал еще разок. :) допускается даже вариант внесения данных и внутри, и за пределами "подсказок". Похоже, что это полностью рабочий вариант.

Автор - Кравченко
Дата добавления - 27.01.2016 в 22:10
Кравченко Дата: Среда, 27.01.2016, 22:18 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
_Boroda_, спасибо.
Как говорил молодой Хазанов "...спинным мозгом чувствую что" изящнее, но головы не хватает оценить это полностью. :(
То же работает... и те же опасения о быстродействии.
 
Ответить
Сообщение_Boroda_, спасибо.
Как говорил молодой Хазанов "...спинным мозгом чувствую что" изящнее, но головы не хватает оценить это полностью. :(
То же работает... и те же опасения о быстродействии.

Автор - Кравченко
Дата добавления - 27.01.2016 в 22:18
Pelena Дата: Четверг, 28.01.2016, 10:32 | Сообщение № 6
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11493
Репутация: 2557 ±
Замечаний: 0% ±

Excel 2010, 2016 & Mac Excel
Цитата Кравченко, 27.01.2016 в 22:10, в сообщении № 4
без поэлементного просмотра
Для просмотра работы формулы по частям используйте клавишу F9. В строке формул выделяете фрагмент -- F9 -- смотрите, как он работает -- ESC

Цитата Кравченко, 27.01.2016 в 22:10, в сообщении № 4
будет несколько тысяч строк
для таких формул - это тяжеловато


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
Сообщение
Цитата Кравченко, 27.01.2016 в 22:10, в сообщении № 4
без поэлементного просмотра
Для просмотра работы формулы по частям используйте клавишу F9. В строке формул выделяете фрагмент -- F9 -- смотрите, как он работает -- ESC

Цитата Кравченко, 27.01.2016 в 22:10, в сообщении № 4
будет несколько тысяч строк
для таких формул - это тяжеловато

Автор - Pelena
Дата добавления - 28.01.2016 в 10:32
Кравченко Дата: Четверг, 28.01.2016, 13:33 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Pelena, спасибо.
И всё же, есть ли возможность сделать изменяемый диапазон проверки?
Например, ограничиться тридцатью строками до текущей строки? Или взять ограничивающие данные из третьей колонки?..
 
Ответить
СообщениеPelena, спасибо.
И всё же, есть ли возможность сделать изменяемый диапазон проверки?
Например, ограничиться тридцатью строками до текущей строки? Или взять ограничивающие данные из третьей колонки?..

Автор - Кравченко
Дата добавления - 28.01.2016 в 13:33
Pelena Дата: Суббота, 30.01.2016, 19:48 | Сообщение № 8
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11493
Репутация: 2557 ±
Замечаний: 0% ±

Excel 2010, 2016 & Mac Excel
Цитата Кравченко, 27.01.2016 в 22:18, в сообщении № 5
опасения о быстродействии

Может, посмотреть в сторону макросов?


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
Сообщение
Цитата Кравченко, 27.01.2016 в 22:18, в сообщении № 5
опасения о быстродействии

Может, посмотреть в сторону макросов?

Автор - Pelena
Дата добавления - 30.01.2016 в 19:48
Кравченко Дата: Суббота, 30.01.2016, 20:45 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Pelena, :( у меня с этим как-то не очень - буквы понимаю все, даже в Кореле программировал, а освоить вижуал... наверно времени нет. :)
Если бы я делал прогу лишь для себя, то взял бы чужое и не парился. А тут будут участвовать еще те "блондинки". поэтому мне надо знать все мелочи, что бы в любой момент подправить.
Конец месяца пройдёт, освобожусь, попробую поиграть с изменением верхнего диапазона "вручную".
 
Ответить
СообщениеPelena, :( у меня с этим как-то не очень - буквы понимаю все, даже в Кореле программировал, а освоить вижуал... наверно времени нет. :)
Если бы я делал прогу лишь для себя, то взял бы чужое и не парился. А тут будут участвовать еще те "блондинки". поэтому мне надо знать все мелочи, что бы в любой момент подправить.
Конец месяца пройдёт, освобожусь, попробую поиграть с изменением верхнего диапазона "вручную".

Автор - Кравченко
Дата добавления - 30.01.2016 в 20:45
Pelena Дата: Суббота, 30.01.2016, 22:43 | Сообщение № 10
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11493
Репутация: 2557 ±
Замечаний: 0% ±

Excel 2010, 2016 & Mac Excel
А если такой вариант: указывать в отдельной ячейке, с какой строки начинать проверку
К сообщению приложен файл: 3031363-1-.xlsx(11Kb)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеА если такой вариант: указывать в отдельной ячейке, с какой строки начинать проверку

Автор - Pelena
Дата добавления - 30.01.2016 в 22:43
Кравченко Дата: Воскресенье, 31.01.2016, 00:24 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Pelena, вы наверно добрая фея?!!
Именно этот вариант я и хотел попробовать. Но к стыду своему, как сейчас вижу, вряд ли смог бы его осилить.
Осталась сущая мелочь - определить первую строку содержащую "_" и отнять нужное количество. Это я уже смогу и сам.
Огромное спасибо!
 
Ответить
СообщениеPelena, вы наверно добрая фея?!!
Именно этот вариант я и хотел попробовать. Но к стыду своему, как сейчас вижу, вряд ли смог бы его осилить.
Осталась сущая мелочь - определить первую строку содержащую "_" и отнять нужное количество. Это я уже смогу и сам.
Огромное спасибо!

Автор - Кравченко
Дата добавления - 31.01.2016 в 00:24
Кравченко Дата: Суббота, 13.02.2016, 20:41 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
После долгих испытаний и барахтанья в непонятных формулах у меня что-то получилось.
К сожалению вариант с отдельной ячейкой для автоматического управления диапазоном не получился - там образовались такие перекрестные ссылки, что даже сам Эксель не мог их понять. :)
А без ограничения диапазона таблицы начинали тупить примерно в районе тысячи строк.

В общем за основу я взял вариант Бороды, как максимально лаконичный и вот что у меня получилось:


Где ДВССЫЛ(АДРЕС(UP;1;2)) - верхний предел поиска в рабочей колонке
А48 динамический адрес нижнего предела поиска в рабочей колонке (предыдущая строка)
Т_01[прх] - вся колонка для сравнения (в моем случае это не будет более 50-10 строк)
Ячейка UP для ручного управления диапазоном имеет УФ завязанный на последнюю заполненную строку. И при превышении предела сигнализирует красным.
Таким образом пользователю таблицы с любым уровнем с надлежащей памяткой будет несложно регулярно "передвигать" диапазон.

Таким же образом можно управлять и пределами в колонке для сравнения, выведя для ручного управления соответствующие ячейки.

Еще раз всем спасибо за помощь. Но в связи с этим решением образовалась еще одна проблемка эргономического характера. Надеюсь админы не будут возражать против продолжения разговора в этой теме и отдельным постом я ее "озвучу".
[moder]Формулы нужно в специальные теги засовывать. Об это в Правилах форума есть. Также там есть и про "продолжения разговора в этой теме".
К сообщению приложен файл: 5650302.xlsx(43Kb)


Сообщение отредактировал _Boroda_ - Воскресенье, 14.02.2016, 16:20
 
Ответить
СообщениеПосле долгих испытаний и барахтанья в непонятных формулах у меня что-то получилось.
К сожалению вариант с отдельной ячейкой для автоматического управления диапазоном не получился - там образовались такие перекрестные ссылки, что даже сам Эксель не мог их понять. :)
А без ограничения диапазона таблицы начинали тупить примерно в районе тысячи строк.

В общем за основу я взял вариант Бороды, как максимально лаконичный и вот что у меня получилось:


Где ДВССЫЛ(АДРЕС(UP;1;2)) - верхний предел поиска в рабочей колонке
А48 динамический адрес нижнего предела поиска в рабочей колонке (предыдущая строка)
Т_01[прх] - вся колонка для сравнения (в моем случае это не будет более 50-10 строк)
Ячейка UP для ручного управления диапазоном имеет УФ завязанный на последнюю заполненную строку. И при превышении предела сигнализирует красным.
Таким образом пользователю таблицы с любым уровнем с надлежащей памяткой будет несложно регулярно "передвигать" диапазон.

Таким же образом можно управлять и пределами в колонке для сравнения, выведя для ручного управления соответствующие ячейки.

Еще раз всем спасибо за помощь. Но в связи с этим решением образовалась еще одна проблемка эргономического характера. Надеюсь админы не будут возражать против продолжения разговора в этой теме и отдельным постом я ее "озвучу".
[moder]Формулы нужно в специальные теги засовывать. Об это в Правилах форума есть. Также там есть и про "продолжения разговора в этой теме".

Автор - Кравченко
Дата добавления - 13.02.2016 в 20:41
Кравченко Дата: Суббота, 13.02.2016, 21:10 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Надеюсь админы не будут сильно возражать.

Следующая проблемка по этой работе касается выпадающего списка.

В колонке А (см. пред. пример) возможны два варианта вводимых значений: номер общего изделия на текущий момент от 20000 (эти номера присваиваются на стадии расчета и к моменту заключения договора значительно прорежаются) и номер нашего внутреннего изделия для них диапазон до 10000.

Не сложно сделать выпадающий список, на основании значения в ячейке, содержащий в себе или номер общего изделия (если есть "подсказка" _ххххх), или следующий порядковый внутренний номер.

А можно ли при этом еще позволить и самостоятельно вводить номера общих изделий?
Как говорится "и выпадающий список поиметь и свободу сохранить". :)
[moder]Нет, так не пойдёт. Этот вопрос к данной теме не относится. Создайте новую[/moder]


Сообщение отредактировал Pelena - Суббота, 13.02.2016, 22:38
 
Ответить
СообщениеНадеюсь админы не будут сильно возражать.

Следующая проблемка по этой работе касается выпадающего списка.

В колонке А (см. пред. пример) возможны два варианта вводимых значений: номер общего изделия на текущий момент от 20000 (эти номера присваиваются на стадии расчета и к моменту заключения договора значительно прорежаются) и номер нашего внутреннего изделия для них диапазон до 10000.

Не сложно сделать выпадающий список, на основании значения в ячейке, содержащий в себе или номер общего изделия (если есть "подсказка" _ххххх), или следующий порядковый внутренний номер.

А можно ли при этом еще позволить и самостоятельно вводить номера общих изделий?
Как говорится "и выпадающий список поиметь и свободу сохранить". :)
[moder]Нет, так не пойдёт. Этот вопрос к данной теме не относится. Создайте новую[/moder]

Автор - Кравченко
Дата добавления - 13.02.2016 в 21:10
Кравченко Дата: Воскресенье, 14.02.2016, 01:13 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
:) Админ-программист не только соблюдает букву закона, но и каждый знак и даже кол-во пробелов. :D

Если это возможно, то я без проблем начну новую тему. А если невозможно?..
Ответьте, пожалуйста, "да" или "нет".
[moder]В любом случае начинайте новую тему[/moder]


Сообщение отредактировал Pelena - Воскресенье, 14.02.2016, 07:29
 
Ответить
Сообщение:) Админ-программист не только соблюдает букву закона, но и каждый знак и даже кол-во пробелов. :D

Если это возможно, то я без проблем начну новую тему. А если невозможно?..
Ответьте, пожалуйста, "да" или "нет".
[moder]В любом случае начинайте новую тему[/moder]

Автор - Кравченко
Дата добавления - 14.02.2016 в 01:13
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Динамическое сравнение диапазонов (Формулы/Formulas)
Страница 1 из 11
Поиск:

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