Страницы: 1
RSS
Обработка и фильтрация массива постоянно обновляемых данных с возможностью добавления новых данных для фильтра
 
Есть таблица (PR Tracing - заявки), куда систематически добавляются новые данные по новым заявкам. В каждой заявке (столбец PR NUMBER) может быть более  одной единицы товара (столбец ITEM NUMBER). Сама таблица содержит более 50 тысяч позиций и постоянно увеличивается + там присутствуют другие столбы (все лишние данные удалил).

Есть таблица (PR & RFQ2021112) по контролю приходов материала на склад для отдельного участка существует отдельный файл, и каждый начальник участка в своём файле указывает свои номера заявок (столбец PR #) и товар (столбец Item #). Я прописал формулу с массивом данных для каждого последующего столбца по данным первых столбцов, чтобы создать фильтр, но с увеличением таблицы - выгрузка/ сохранение/ обновление и другие операции начинают занимать всё больше времени. Даже перезапись выполненных позиций в виде текста (чтобы удалить формулы), всё равно занимает много времени (уже более 1 минуты).

Ссылка на архив с двумя файлами

Сейчас вспомнил ещё одну "маленькую" проблему, которую ярко видно в заявке PFD12116373-21 по товару 273931 в файле PR Tracing - заявки -  там три строки с идентичными критериями, вот только корректными являются данные второй строки у которой заполнен столбец PO NUMBER. Все текущие формулы подтягивают первую строку, а это не корректно, так как в заявке эта строка отменена. В исходном файле PR Tracing - заявки есть столбец, который показывает статус строки Отменено ("Y" - товар не будет закупаться, "N" - товар актуален к закупке, "пусто" - нет информации о необходимости закупа товара или столбец PO NUMBER не заполнен) - иногда заявки или товары в заявке могут быть отменены и не закупаться.

Но эта проблема была мной проигнорирована в моих формулах, так как еще больше усложнила бы формулу, и потребовала бы проверять по ещё дополнительным критериям.
Изменено: Dukalion - 27.11.2021 10:39:20 (Вспомнил ещё один момент по созданию формулы)
 
Вариант оптимизации (1 и 2 пункты - см.ссылку, перевод конечно так себе, но смысл понятен):

1. В Excel 2007 и более поздних версиях формулы массива могут обрабатывать ссылки на целые столбцы, но это заставляет вычисления для всех ячеек в столбце, включая пустые ячейки. Для крупных таблиц, содержащих миллионы строк, это может привести к существенному снижению производительности.
2. ...VLOOKUP (ВПР) немного быстрее (примерно на 5 процентов быстрее), проще и использует меньше памяти, чем сочетание MATCH (ПОИСКПОЗ) и INDEX (ИНДЕКС) или OFFSET (СМЕЩ)...
3. скрытие "0" в результатах расчета формул лучше делать не дополнительными формулами, а стандартным скрытием нулевых значений в настройках листа Excel'я (см.скрин)

Применение ВПР с дополнительным скрытым столбцом [A] в исходных данных - см. приложенные файлы Excel

Ну и еще про оптимизацию формул с массивами (по той же ссылке сверху):
Скрытый текст

ps Тему лучше Вам переименовать...
Изменено: Ёк-Мок - 27.11.2021 03:18:37
Удивление есть начало познания © Surprise me!
И да пребудет с нами сила ВПР.
 
Как я понял функция COLUMN() привязана к тому же столбцу в обоих файлах, но файл  таблица (PR Tracing - заявки) в оригинале содержит на текущий момен 78 столбцов и нужные мне столбцы находятся на больших расстояниях, поэтому в формулах у меня используется привязка к определенным столбцам, чтобы в случае добавления нового произвести сдвиг через замену во всех формулах.
 
Цитата
Dukalion написал:
чтобы в случае добавления нового произвести сдвиг через замену во всех формулах.
сделайте еще строку служебную над шапкой, в которой ищите индекс столбца в исходной таблице через MATCH и этот индекс использовать, ну и аналогично уже с поиском нужной строки.
Сделайте умную таблицу и формулы будут сами размножатся при вводе
По вопросам из тем форума, личку не читаю.
 
Сейчас вспомнил ещё одну "маленькую" проблему, которую ярко видно в заявке PFD12116373-21 по товару 273931 в файле PR Tracing - заявки -  там три строки с идентичными критериями, вот только корректными являются данные второй строки у которой заполнен столбец PO NUMBER. Все текущие формулы подтягивают первую строку, а это не корректно, так как в заявке эта строка отменена. В исходном файле PR Tracing - заявки есть столбец, который показывает статус строки Отменено ("Y" - товар не будет закупаться, "N" - товар актуален к закупке, "пусто" - нет информации о необходимости закупа товара или столбец PO NUMBER не заполнен) - иногда заявки или товары в заявке могут быть отменены и не закупаться.

Но эта проблема была мной проигнорирована в моих формулах, так как еще больше усложнила бы формулу, и потребовала бы проверять по ещё дополнительным критериям.

Вот, мне интересно, а через Query всю таблицу как-то реализовать можно?
Изменено: Dukalion - 27.11.2021 10:46:04
 
Цитата
написал:
2. ...VLOOKUP (ВПР) немного быстрее (примерно на 5 процентов быстрее), проще и использует меньше памяти, чем сочетание MATCH (ПОИСКПОЗ) и INDEX (ИНДЕКС) или OFFSET (СМЕЩ)...
Когда вставил формулу - вспомнил почему я её не использовал - она выдаёт ошибку и отказывается давать значения по формуле:
Код
=VLOOKUP($B3&$E3,'[PR Tracing - заявки.xlsx]PR and PO lines'!$A:$BW,COLUMN(),0) 

Только когда указан один критерий. Может у меня какие-то настройки отключены/отсутствуют (Excel 2016)?

 
Цитата
написал:
сделайте еще строку служебную над шапкой, в которой ищите индекс столбца в исходной таблице через MATCH и этот индекс использовать, ну и аналогично уже с поиском нужной строки.
Правильно понял - нужно добавить нумерацию в исходный файл PR Tracing - заявки по столбцам и строкам? Так как без добавления нумерации - результата от формул нет. Так не вариант, так как файл выгружается каждый день, а следовательно работать я могу только с файлом PR & RFQ2021112.  
 
обратите внимание , что индексы ищутся сами, просто они делают это один раз для строки и один раз для столбца, что минимизирует расчеты. Если ограничить разумный диапазон, то даже формулы массива будут считаться достаточно быстро, что позволит решить и другие хотелки фильтрации.
По вопросам из тем форума, личку не читаю.
 
В общем, решение было такое:
1. Создал таблицу через Power Query на оба файла.
2. Сделал форматирование PR NUMBER и ITEM NUMBER, чтобы был формат General (по умолчанию)
3. Объединил по двум столбцам с указанием параметра объединения - Только совпадающие.
4. Проставил автообновление данных при открытии файла, теперь время открытия секунд 10-20. Лучше чем пара минут.
5. Потом добавлю заливку по строкам, если количество поставки совпадает с количеством поставленных материалов, а для пустых позиций проставлю заливку на критерий наличия номера контракта поставки и/или схожих строк с идентичными PR NUMBER и ITEM NUMBER.

В конечном итоге файл с 7 Мб уменшился на 200 кб.

Но за помочь всем спасибо.
Можно закрыть тему.
Изменено: Dukalion - 09.12.2021 06:38:26
 
Dukalion, сообщение можно дополнять, незачем очереди создавать
Страницы: 1
Наверх