Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Вхождение данных из одной колонки в другой.
 
#1
07.02.2019 12:07:13
Всем привет. Нужна помощь в фильтрации данных.

Имеется таблица с тремя колонками. Первая и вторая колонка это данные артикул товара и объём. Третья колонка это тоже артикула которые нужно найти в первой и получить объём из второй.

Количество данных в третьей колонке постоянно меняются. Можно конечно получить данные протягивая функцию ВПР, но в виду некоторых причин это очень хлопотно.
Данные в первой колонке(артикула) более 75000 тысяч строк. Данные в третьей колонке(артикула) от 500 и до 5000 тысяч. Так что перебор в массиве займёт очень много времени. Я побывал использовать  условное форматирование и менял цвет текста в совпавших ячейках, далее я хотел с помощью автофильтра скрыть все кроме нужных, и используя SpecialCells получить нужные мне данные. Но это тоже долго. Можете подсказать способ по быстрее?

Заранее спасибо.
 
 
 
#2
07.02.2019 12:12:14
Третью колонку сначала в массив потом в словарь.
Первую колонку со второй в массив
Потом пробежать один раз по массиву и проверять по словарю
Или наоборот: первую колонку в словарь(ключ) и вторую в словарь (значения)
 
 
 
#3
07.02.2019 12:26:28
Александр Моторин,

Я со словарями ещё не работал. Не могли бы вы ответить, в чём будет разница по сравнению с массивами. Разве это поможет избежать кучи итераций в цикле?

PS. Я так понимаю их использование надо включать отдельно, то есть это не встроена возможность VBA
Изменено: Tipok - 07.02.2019 12:27:38
 
 
 
#4
07.02.2019 13:22:00
pq
 
 
 
#5
07.02.2019 13:26:38
artyrH, Вы использовали автофильтр. Но когда у вас более 75000 уникальных артикулов, даже просто открыть автофильтр уже проблема. Всё очень надолго зависает.
 
 
 
#6
07.02.2019 13:28:39
Tipok, я использовал Power Query)
 
 
 
#7
07.02.2019 14:55:56
artyrH, прошу прошения, я сделал не верные выводы.
Но как это мне поможет, я могу с помощью Power Query вытянуть столбец целиком но не определённые строки, так ведь?
 
 
 
#8
07.02.2019 15:11:03
смотрите. я не знаю как  будет работать на 75000 строк.  если есть PQ, то на пробу скопируйте 5000 строк в  Таблица1 файла pq.xlsx и обновите запрос. и в тему на пробу тоже закиньте
или покажите про какие строки говорите
Изменено: artyrH - 07.02.2019 15:12:01
 
 
 
#9
07.02.2019 15:30:54
Выкладываю файл, он слегка поменьше чем рабочий, разница только в количестве строк.
https://yadi.sk/i/n_6ZOOTkVnSSCw
Нужно найти объём для строк что в колонке "сравнение"
Изменено: Tipok - 07.02.2019 22:16:50
 
 
 
#10
07.02.2019 15:47:16
Tipok, за десять сек отработало.
измените имена столбцов на артикул , объем , артикул2. затем контрол т. открываете пустой запрос в Power Query и в расширенном редакторе вставляете
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Добавлен индекс" = Table.AddIndexColumn(Источник, "Индекс", 0, 1),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Добавлен индекс",{"артикул2"}),
    #"Объединенные запросы" = Table.NestedJoin(#"Добавлен индекс",{"артикул2"},#"Удаленные столбцы",{"артикул"},"Удаленные столб",JoinKind.FullOuter),
    #"Развернутый элемент Удаленные столб" = Table.ExpandTableColumn(#"Объединенные запросы", "Удаленные столб", {"объем"}, {"объем.1"}),
    #"Строки с примененным фильтром" = Table.SelectRows(#"Развернутый элемент Удаленные столб", each [артикул] <> null and [артикул] <> ""),
    #"Удаленные дубликаты" = Table.Distinct(#"Строки с примененным фильтром", {"Индекс"}),
    #"Сортированные строки" = Table.Sort(#"Удаленные дубликаты",{{"Индекс", Order.Ascending}}),
    #"Удаленные столбцы1" = Table.RemoveColumns(#"Сортированные строки",{"Индекс"})
in
    #"Удаленные столбцы1"
имя таблицы должно совпадать с именем в коде. и все жмете закрыть и загрузить
 
 
 
#11
07.02.2019 15:51:17
artyrH, Вы используете язык запросов SQL ведь так? Я не знал что можно обратиться к данным в той же книге.
Я знал что эксель поддерживает работу с базами данных, но не знал что можно обратится к открытой книге.
Изменено: Tipok - 07.02.2019 22:17:03
 
 
 
#12
07.02.2019 15:58:58
Цитата
Tipok написал:
используете язык запросов SQL
блин! я не знаю что это.. вернее знаю что есть SQL, а что это я не знаю) вот здесь про PQ. погуглите как установить PQ на Excel . больше мне нечем помочь.
добавьте  остальные данные и обновите.
у вас какая версия excel?
Изменено: artyrH - 07.02.2019 16:11:37
 
 
 
#13
07.02.2019 16:15:06
artyrH, огромное спасибо. Хоть это и не SQL но довольно мощная штука, пошёл набираться ума.

PS. Макрос действительно отработал очень быстро.
Изменено: Tipok - 07.02.2019 22:17:34
 
 
 
#14
07.02.2019 16:17:54
какой макрос? )
 
 
 
#15
07.02.2019 16:20:14
Tipok, вариант на массивах в Вашем случае отработает примерно за секунду или меньше.  
 
 
 
#16
07.02.2019 16:28:48
Anchoret, да Вы что? А автор утверждает обратное:
Цитата
Tipok написал:
перебор в массиве займёт очень много времени
:)  
 
 
 
#17
07.02.2019 16:30:23
Tipok, не нужно жать на Enter по 2-3 раза. Зачем в Ваших сообщениях столько пустых строк?
 
 
 
#18
07.02.2019 16:32:11
Цитата
artyrH написал:
какой макрос? )
Извиняюсь запрос.
 
 
 
#19
07.02.2019 16:34:56
Anchoret, если использовать массив то нужно будет перебрать 75000x5000. У меня эксель завис на минут 10, далее я не стал ждать, и принудительно завершил его работу.
 
 
 
#20
07.02.2019 16:39:45
Tipok, Вы бы хоть посмотрели как это реализовано... Не нужно делать столько сравнений. Сортируются два списка и потом сравниваются. По ссылке сравнивались два списка по миллиону значений в каждом ... за 5 сек.
 
 
 
#21
07.02.2019 17:27:18
Anchoret, Вы имеете ввиду сортировать сначала средствами эксель отсортировать колонку а потом загнать в массив?

Цитата
artyrH написал: у вас какая версия excel?
Извиняюсь забыл ответить 2016 x32
 
 
 
#22
07.02.2019 20:10:25
Tipok,кликните на ссылку выше.
 
 
 
Страницы: 1
Наверх