Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: Пред. 1 2
RSS
Выпадающий список с пустыми пунктами - как от них избавиться?
 
#31
25.09.2018 16:18:41
Цитата
Юрий М написал:
Ну тут два варианта
три :-)  PowerQyery и Четырe  - MS Query. Оба инструмента смогут сформировать список с разных листов, отсортированный или нет, с фильтрами или без оных …...
Более современный - первый. Единственное что нужно, это обеспечить обновление по кнопке или по событию, но это мелочи.

Только вот Кот в №10 ;-) , как то от темы в сторону увел. Исходная задача была убрать пустые строки в выпадающем списке а свели к объединению списков с разных листов на одном.
Изменено: БМВ - 25.09.2018 16:21:36
По вопросам из тем форума, личку не читаю.
 
 
 
#32
25.09.2018 16:52:06
БМВ, Кот конечно и ушел от темы но его вариант какой-то практичный. Единственное надо было уточнить что данные надо было брать с определенных листов.\

Сейчас пытаюсь разобрать весь код............... взрыв мозга. Как баран на новые ворота.

PowerQyery я так понимаю надстройка? ее у меня в таблицах нет. а раз это надстройка то всем кому отправлю файл надо будет устанавливать PowerQyery?
 
 
 
#33
25.09.2018 16:59:55
Цитата
Ярослав написал:
файл надо будет устанавливать PowerQyery?
Если MSOffice старее 2016, то да.
MS Query - встроенный инструмент, но у него есть беда, привязывается к конкретному расположению файла и либо надо менять руками, либо макросом менять путь.
По вопросам из тем форума, личку не читаю.
 
 
 
#34
25.09.2018 17:01:31
БМВ, тогда наверное удобнее будет воспользоваться только макросом? или там тоже будут нюансы?  
 
 
 
#35
25.09.2018 17:27:51
Сейчас файл положить в C:\temp

Сам запрос
Код
Select *
From (
SELECT `Список 1` as `Список` 
FROM  `'Список 1$'`
Union
SELECT `Список 2` As `Список`
FROM `'Список 2$'`
Union
SELECT `Список 3` As `Список`
FROM `'Список 3$'`)
WHERE `Список` is not null
Путь менять в  Connections
DSN=Excel Files;DBQ=C:\Temp\Выпадающий список.xlsx;DefaultDir=C:\Temp;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;

специально убрал часть значений, обновление по правой кнопке - обновить.
Изменено: БМВ - 25.09.2018 17:30:56
По вопросам из тем форума, личку не читаю.
 
 
 
#36
25.09.2018 18:32:14
Цитата
БМВ написал:
Только вот Кот в №10 ;-) , как то от темы в сторону увел
А чё сразу кот? Кот и пустые убрал, и собрал с разных листов, и на уникальность проверил ))
 
 
 
#37
25.09.2018 19:07:05
Если листов, с которых нужно сформировать список, не очень много, то можно перечислить их так, как сделано в прикреплённом файле. Если же их МНОГО, то можно перечислить их где-нибудь на листе (удобно, если время от времени их нужно менять) и проверять там.
Сам именованный диапазон можно скрыть или вообще поместить на скрытом листе.
 
 
 
#38
25.09.2018 20:34:56
Код
Select DISTINCT *
From (
SELECT `Список 1` as `Список` 
FROM  `'Список 1$'`
Union all
SELECT `Список 2` As `Список`
FROM `'Список 2$'`
Union all
SELECT `Список 3` As `Список`
FROM `'Список 3$'`)
WHERE `Список` is not null
+
Код
Private Sub Workbook_Open()
With ActiveWorkbook.Connections("Query from this File").ODBCConnection
.Connection = _
        "ODBC;DSN=Excel Files;DBQ=" & ActiveWorkbook.FullName & _
        ";DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"
End With
End Sub
+
Код
Private Sub Worksheet_Activate()
 ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
End Sub
По вопросам из тем форума, личку не читаю.
 
 
 
#39
25.09.2018 21:12:54
Привет, Михаил
Что заставило отказаться от
Цитата
БМВ написал:
Union
в пользу
Цитата
БМВ написал:
Union all
:qstn:
 
 
 
#40
25.09.2018 21:23:24
Андрей VG, Андрей, следы эсперимента остались.:-) . Не обратил внимание.
По вопросам из тем форума, личку не читаю.
 
 
 
#41
25.09.2018 21:29:33
Юрий М, заметил такую штуку. Если например добавить какой-нибудь пункт в список 3 или любой другой список то общий список Unique обновиться только тогда когда кликнешь по выпадающему списку. Почему так происходит?

БМВ, Ваш вариант не могу понять. Это тот же вариант с добавлением файла в TEMP? а для чего его именно туда надо поместить?

______________
Начал изучать VBA с нуля.. через несколько видео уроков меня вырубило.. буквально.
 
 
 
#42
25.09.2018 21:35:09
Так происходит потому, что событием, по которому формируются именованный диапазон и выпадающий список, является выделение именно этой ячейки.
А зачем Вам видеть сам диапазон? Вас ведь интересует конечный результат - выпадающий список )
 
 
 
#43
25.09.2018 21:37:45
Юрий М, сам диапазон видеть не надо. т.е. лист Unique можно вообще скрыть и не знать о нем. НО. если добавить пункт в один из списков то этот пункт не появится в выпадающих списках пока не кликну по выпадающему списку на листе Unique
 
 
 
#44
25.09.2018 21:39:08
Ярослав, нет, в этом варианте автоматически настраивается путь к книге, в которой все это работает.  
По вопросам из тем форума, личку не читаю.
 
 
 
#45
25.09.2018 21:41:06
Цитата
Ярослав написал:
если добавить пункт в один из списков то этот пункт не появится в выпадающих списках пока не кликну по выпадающему списку на листе Unique
А в какой момент появляется выпадающий список?
 
 
 
#46
25.09.2018 21:53:30
Юрий М,

Шаг 1. Список 1, Список 2, Список 3 дополняем пункты (строки)
Шаг 2. Переходим на Лист 1. Там выпадающие списки. Раскрываем любой из них и видим что из Шаг 1 новые пункты не добавились. т.е. выпадающий список не обновился.
Шаг 3. Заходим на лист Unique кликаем по выпадающему списку в C2 и список в А и всех выпадающих списках обновляется.

т.е. получается при добавлении новых пунктов в списках.. выпадающий список не обновляется пока не зайти на Unique и шаг 3

БМВ, Кстати в вашем решении также.
 
 
 
#47
25.09.2018 21:59:26
Макрос находится в модуле листа "Unique" и именно на этом листе отслеживается активация ячейки с выпадающим списком. Если нужно создавать список и на другом листе, то об этом ТОЖЕ НУЖНО БЫЛО ГОВОРИТЬ СРАЗУ.
 
 
 
#48
25.09.2018 22:07:38
Юрий М, Прошу прощения. да я и представления не имел что это имеет значение. Получается надо макрос перенести в общий модуль?
 
 
 
#49
25.09.2018 22:12:22
Нет - это событийный макрос, и он должен быть или в модуле листа или в модуле книги. Есть вариант формировать список при активации листа. Точнее - вызывать внешнюю процедуру (она как раз и будет в стандартном модуле), которая и будет это делать при наступлении события активации нужного листа.
 
 
 
#50
25.09.2018 22:21:08
Иллюстрация к теме  Выпадающий список с пустыми пунктами - как от них избавиться?

Цитата
Ярослав написал:
БМВ , Кстати в вашем решении также.
такие задачи делаются или под ключ, когда все данные и выпадающие списки на своих местах и понятно что и когда обновлять, или как прототипы, которые потом допиливаются самостоятельно.  Это касается обоих решений.
По вопросам из тем форума, личку не читаю.
 
 
 
#51
26.09.2018 08:59:46
Юрий М, БМВ, Понял. В следующий раз буду внимательнее к вопросам. Спасибо вам за помощь!
 
 
 
#52
26.09.2018 10:24:16
Добавлен код, который обновляет список Unique при переходе на Лист 1.
Неизлечимых болезней нет, есть неизлечимые люди.
 
 
 
#53
26.09.2018 14:20:07
Очередная версия.
 
 
 
#54
26.09.2018 15:12:15
Юрий М, Юрий, а следующая будет "Выпадающий список с нескольких листов 04.xls.xlsm.xlsb"? :-)
По вопросам из тем форума, личку не читаю.
 
 
 
#55
26.09.2018 15:18:32
Ага )
 
 
 
#56
26.09.2018 15:26:28
Цитата
БМВ написал:
а следующая будет
когда достанут просто .txt  :D  
Лень двигатель прогресса, доказано!!!
 
 
 
#57
26.09.2018 15:48:33
Сергей, Нее .ODS Open Document Spreadsheet и там JavaScript или Libre/Open Office Basic :-)
По вопросам из тем форума, личку не читаю.
 
 
 
Страницы: Пред. 1 2
Наверх