Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
Отображение значений результатов поиска при использовании ВПР
 
#1
20.02.2021 16:09:31
Добрый день, опытные пользователи!

Просьба подсказать, каким способом можно решить одну проблему.

Есть книга, в которой используются 2 листа: Рабочий лист и Контактный лист.
В рабочий лист ежедневно вносится следующая информация: номер задания и название контрагента.
Заданий очень много.
Контрагент для разных заданий может повторяться, поэтому контактные данные контрагентов вынесены на отдельный лист (Контактный лист), из которого эти данные на Рабочий лист выгружаются при помощи функции ВПР.
Инструмент достаточно удобный, но есть одна проблема:
В ячейке с контактной информацией может содержаться большое количество информации (1-2 контактных лица, их номера телефонов, должности и пр.), которая в ячейке на Рабочем листе полностью не помещается; в строке формул отображается сама формула ВПР; двойной клик мышью на выделенной ячейке запускает редактирование формулы.
Увеличение размеров ячейки делает таблицу мега огромной и неудобной в пользовании.
Подскажите, пожалуйста, есть ли способ, не меняя размеров ячеек на Рабочем листе, сделать возможным отображение результатов поиска функцией ВПР или в строке формул, или в виде всплывающего окна (подобно примечанию), или еще каким-либо способом?
Спасибо за уделенное внимание.
 
 
 
#2
20.02.2021 17:02:56
может  так?  
чтоб дело мастера боялось, он знает много страшных слов.
 
 
 
#3
24.02.2021 08:50:27
А что поменялось кроме того, что на листе с Контактными данными появились новые столбцы? На основном, Рабочем листе просмотр контактов также остался неудобным.
 
 
 
#4
24.02.2021 10:12:48
Может, с какой-то периодичностью вставлять значения из столбца "ВПР" в столбец правее? Тогда содержимое будет видно в строке формул.
Можно макрос соорудить макрорекордером, который будет это делать по тыцу на какой-нибудь кнопочке/
 
 
 
#5
24.02.2021 16:44:31
Интересный вариант.
Столбец с формулой ВПР можно скрыть, чтобы не путаться, а по клику переносить данные.
Остается вопрос соорудить макрос, в чем я не силен. Увы...
 
 
 
#6
24.02.2021 16:57:53
Копируются только значения, если копировать форматы со скрытого столбца, то скрытие тоже будет копироваться.
 
 
 
#7
25.02.2021 12:55:58
Xel,

Круто!!!
Спасибо огромное!
Интересно, а можно ли сделать апгрейд этого макроса следующим образом:
Представим ситуацию, что в процессе работы специалист добавил, например, новый номер телефона, но не во вкладку Контактный лист, а в ячейку, в которую копируются результаты после выполнения макроса.
При следующем нажатии на кнопку эти изменения не сохранятся, т.к. макрос только копирует значения из другого столбца.
А есть ли возможность сделать так, что при нажатии этой копки контактная информация для данного контрагента синхронизировалась с данными на листе Контактный лист? Или это уже похоже на утопию?))
Еще раз огромное спасибо за помощь!
 
 
 
#8
25.02.2021 13:22:19
Возможность, конечно, есть.

Но тут философский вопрос - в ситуации, которую вы описали, такой макрос ничем не поможет   ;) Совсем. Очистит введенное значение, не найдя соответствия в таблице адресов.

Сейчас он хотя бы делает простое и понятное действие - копирует понятно, что, понятно куда.

Например, докуда он должен "прочесывать" первый столбец? До первой пустой ячейки? Если будет очищена почему-то ячейка, он на ней и остановится.
Отсчитывать, например, 10 пустых ячеек?

До конца используемого диапазона? Если кто-нибудь ухитрится активировать весь миллион строк, то макрос будет очень долго отрабатывать, эксель в это время будет в астрале.
То же самое с таблицей с адресами.

Макросами идиотизм не лечится, а усугубляется.

Если везде красиво задать имена таблиц и отрывать руки тем, кто что-то пишет не туда, то задачу лучше не макросом а  через Power Query решать.
Но он против "кто-то что-то не туда" также бессилен.  
/накатала простыню, а руками образец  с PQ сделать не могу, тут эксель доисторический, вечером, если никто не подкинет  :oops:  
Изменено: Xel - 25.02.2021 13:36:59
 
 
 
#9
25.02.2021 13:37:12
Цитата
Xel: До конца используемого диапазона? Если кто-нибудь ухитрится активировать весь миллион строк
Если нужно получить диапазон для дальнейшем с ним работы и при этом нельзя использовать .UsedRange (могут быть  закрашены столбцы и строки целиком) и/или End(xlUp) ("прыгать" по тысячам строк/столбцов - затратно по времени), то можно использовать метод .SpecialCells, чтобы получить из всей области листа именно данные/комментарии/формулы и т.д.
Изменено: Jack Famous - 25.02.2021 13:39:22
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#10
25.02.2021 13:49:01
Цитата
Jack Famous написал:
чтобы получить из всей области листа именно данные/комментарии/формулы
Если в исходной таблице могут оказаться пустые ячейки - их все равно обрабатывать, чтобы стереть соответствующую контактную информацию.
Если не могут, то достаточно ехать до первой/пятой  пустой.
Тут философский вопрос о масштабах бардака в процессе работы. С учетом приведенного примера кажется, что любой макрос только добавит веселья.
Изменено: Xel - 25.02.2021 13:50:48
 
 
 
#11
25.02.2021 13:59:30
Цитата
Xel: С учетом приведенного примера кажется, что любой макрос только добавит веселья
согласен  :D
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#12
25.02.2021 14:01:22
Xel,
Подскажите, какие правки в макрос внести, чтобы при нажатии кнопки из данные столбца "С" копировались в столбец "D", из данные столбца "F" копировались в столбец "G"?
Спасибо за ответ.
 
 
 
#13
25.02.2021 14:13:03
Там столбцы напрямую прописаны, просто буквы в тексте замените, как в формулах, и все.
Нажмите alt+F8, выпадет список макросов, в нем выберите "СкопироватьЗначения" и нажмите Изменить, увидите этот текст:


Код
Sub СкопироватьЗначения()'
' Макрос1 Макрос
'
'
    Columns("C:C").Select
    Selection.Copy
    Columns("D:D").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub
Изменено: Xel - 25.02.2021 14:29:06
 
 
 
#14
25.02.2021 14:15:42
Xel, оформляйте код, как это делают другие.
 
 
 
#15
25.02.2021 14:18:43
Цитата
Xel написал:
Нажмите F8, выпадет список
Ничего не выпадет )
 
 
 
#16
25.02.2021 14:27:30
Цитата

Да, но здесь значения одного столбца копируются в другой ("С" копируется в "D").
А как сделать, чтобы при нажатии на одну кнопку одновременно копировалось "C" в "D" и "F" в "G"?
Спасибо!
Изменено: Broonya - 25.02.2021 15:03:44
 
 
 
#17
25.02.2021 14:28:00
Alt+F8
конечно  же. Айм сорри.
Изменено: Xel - 25.02.2021 14:29:44
 
 
 
#18
25.02.2021 14:37:55
Цитата
Broonya написал:
А как сделать, чтобы при нажатии на одну кнопку одновременно копировалось "C" в "D" и "F" в "G"?

Просто  копируете кусок текста в макросе и поправляете, что надо:
Я для наглядности еще обрезала хвосты, которые автоматически записались, так совсем просто "выделить столбец - копировать, выделить столбец - вставить значения" и это два раза повторяется.

Код
Sub СкопироватьЗначения()

    Columns("C:C").Select
    Selection.Copy
    Columns("D:D").Select
     Selection.PasteSpecial Paste:=xlPasteValues
    
    Columns("F:F").Select
    Selection.Copy
    Columns("G:G").Select
    Selection.PasteSpecial Paste:=xlPasteValues

End Sub
Изменено: Xel - 25.02.2021 14:41:25
 
 
 
#19
25.02.2021 14:40:52
Xel, you simply the best ....)
 
 
 
#20
25.02.2021 14:45:10
Broonya, вернитесь в #16 и удалите то, что Вы ошибочно считаете цитатой: не цитата это, а копия сообщения.
И запомните: кнопка копирования не для ответа!
 
 
 
#21
25.02.2021 15:03:08
Xel,

Все работает, но только при условии, что не включены фильтры.
Если включить фильтр, то копируется криво (см. вложение).
Есть ли способ это устранить?
 
 
 
#22
25.02.2021 15:33:20
Оно работает, как положено работать обычной копипасте при включенном фильтре.
Вариант раз - простенький, снимать фильтр перед копированием. По идее, вроде, не так уж часто должно требоваться копирование значений.
Макрос очищает фильтр и копирует столбец.
Код

Sub СкопироватьЗначения()

    On Error Resume Next
    ActiveSheet.ShowAllData
    
    Columns("C:C").Copy
    Columns("D:D").PasteSpecial Paste:=xlPasteValues
    
End Sub


Вариант два - то, о чем вы спрашивали, обойтись без формул, макросом проезжать ячейки и тянуть контакты. Но нужны критерии, по которым будут перебираться ячейки (до первой пустой?) и какая-никакая дисциплина в работе с файлом.
Изменено: Xel - 25.02.2021 15:44:11
 
 
 
#23
25.02.2021 15:39:57
Xel, зачем всё время Вы используете Select? Сразу копируйте диапазон.
 
 
 
#24
25.02.2021 15:43:09
Цитата
Xel написал:
Вариант два - то, о чем вы спрашивали, обойтись без формул, макросом проезжать ячейки и тянуть контакты.
Т.е. я правильно понимаю, что макрос будет работать следующим образом:
1. на Рабочем листе в строке с новым названием мы вносим контрагента
2. запускаем макрос, и он ищет название данного контрагента на листе Контактный лист и переносит контакты на основной рабочий лист?
 
 
 
#25
25.02.2021 15:49:48
Цитата
Юрий М написал:
зачем всё время Вы используете Select? Сразу копируйте диапазон
навела порядок, да, спасибо.

Цитата
Broonya написал:
2. запускаем макрос, и он ищет название данного контрагента на листе Контактный лист и переносит контакты на основной рабочий лист?
Можно и так.
Я имела в виду аналог ВПР. Едет по столбцу рабочего листа (вопрос - докуда, насколько там возможны творческие полеты?) и перепахивает столбец с контактами, перезаписывая данными, найденными на листе контактов. Но мне кажется, что в вашем случае тут побочных эффектов может быть больше, чем пользы. Но в таком варианте оно поедет по всем ячейкам, игнорируя фильтр.
Если ехать от листа с контактами (макросу то по барабану, он железный), то будут игнорироваться ячейки рабочего листа, контрагенты по которым не упомянуты в контактом листе. Там может что-нибудь нерелевантное остаться навечно у контрагентов, не упомянутых в контактном листе.
Изменено: Xel - 25.02.2021 15:52:57
 
 
 
#26
25.02.2021 16:49:22
Вот такое.
 
 
 
#27
25.02.2021 17:05:57
Цитата
Xel написал:
Вот такое.
Я правильно понимаю, что это работает по сценарию:

Цитата
Xel написал:
Если ехать от листа с контактами (макросу то по барабану, он железный)
?
 
 
 
#28
25.02.2021 17:16:28
Идет по рабочему листу по столбцу Б со 2й строки до первой пустой ячейки. Чистит ячейку в столбце С.
Потом ищет соответствие в контактах. Если нашел - подставляет, не нашел - оставляет пустой и едет дальше.
Контакт находит также, как ВПР - самый верхний, если вдруг будут дубли, нижние не заметит. Если в листе контактов будет пустая ячейка, ниже этой ячейки тоже ничего смотреть не будет.
Изменено: Xel - 25.02.2021 17:19:31
 
 
 
#29
25.02.2021 17:37:16
Цитата
Xel написал:
(вопрос - докуда, насколько там возможны творческие полеты?)
А как можно указать границы: номером строки или конкретным значением ячейки?
Можно, допустим, сделать ячейку со значением, например, "STOP". И когда доезжает по столбцу до данного значения, то останавливается в своем трудовом поиске. А все новые строки в этом случае можно добавлять выше строки со значением "STOP".

Второй сценарий, в котором макрос копошится на Контактном листе и оттуда дергает значения, более удобный, т.к. в этом случае это фактически 2 базы данных: одна по задачам, вторая по контактам (здесь я могу дополнительно вносить информацию, например, адрес, e-mail и пр.).
Цитата
Xel написал:
будут игнорироваться ячейки рабочего листа, контрагенты по которым не упомянуты в контактом листе
Правильно, ибо не стоит халатно относится к своим обязанностям и нужно вовремя заполнять информацию

Цитата
Xel написал:
может что-нибудь нерелевантное остаться навечно
А вот здесь уже появляется другая проблема. Если ту же "ромашку" написать с маленькой буквы, то это значение игнорируется.
Может быть это можно исправить автоподставкой / автозаполнением (или как это правильно назвать)? Т.е. на Рабочем листе при заполнении столбца Контрагенты предлагались бы варианты со значениями из Контактного листа. В этом случае опечатки и задвоения можно избежать.
 
 
 
#30
25.02.2021 17:47:00
Игнорирует регистр.
Изменено: Xel - 25.02.2021 17:47:35
 
 
 
Страницы: 1 2 След.
Наверх