Спасибо, разобрался, буду изучать код и как его модерировать под требования детей. Ранее смотрел в разделе ВИД-Макросы, а там ничего небыло, поэтому подумал, что это что-то другое. Не знал про то, что VBA-редактор можно вызывать через комбинацию Alt+F11.
Можете подсказать как это работает? Не могу найти информацию про эту команду в гугле. Это не макросы, а как работает не понимаю. Либо дайте ссылку на метриал, где об этом написано. Хочется понять на какие параметры эта формула опирается и как их корректировать, так понимаю сам алгоритм именно в файле. Как с ним можно работать или как он создан? Это, ведь, ручное добавление формулы в библиотеку?
Дочка играет D&D, и они решили создать персонажа со своим языком, для этого накидал небольшой шифратор. Файл во вложении. Первый лист "Код" содержит Слог (строка 1) и Букву (строка 2, которую он будет прятать, слог составляем через рандомную комбинацию двух букв (исходной и ещё одной) - тут ничего не надо делать, так как это будут делать другие игроки для персонажа, который будет говорить на этом языке для усложнения чтения своего текста. Второй лист "Текст" содержит область с формулами, где прописывается шифр букв по порядку (столбцы A-AD) - "одна строка-одно слово", потом с столбце AE собраное слово, а в столбце AF русское исходное слово (ну, плюс, сверху все слова одной строкой в ячейке): 1. Для первой буквы:
Теперь суть проблемы/вопроса/просьбы (выбрать желаемое): 1. Мои формулы работают только со слогами, т.е. если окажется, что будет символ (точка, запятая и т.п.) или букв шифрации будет больше двух или вообще одна - вся схема рушится. 2. Неудобно печатать по слову в ячейке, как-нибудь возможно сделать строчную шифровку по аналогии того, что я сделал или может можно это сделать лучше?
P.S. Смотрел на форуме что-то подобное или подходящее, но как-то не очень успешно.
В общем, решение было такое: 1. Создал таблицу через Power Query на оба файла. 2. Сделал форматирование PR NUMBER и ITEM NUMBER, чтобы был формат General (по умолчанию) 3. Объединил по двум столбцам с указанием параметра объединения - Только совпадающие. 4. Проставил автообновление данных при открытии файла, теперь время открытия секунд 10-20. Лучше чем пара минут. 5. Потом добавлю заливку по строкам, если количество поставки совпадает с количеством поставленных материалов, а для пустых позиций проставлю заливку на критерий наличия номера контракта поставки и/или схожих строк с идентичными PR NUMBER и ITEM NUMBER.
написал: сделайте еще строку служебную над шапкой, в которой ищите индекс столбца в исходной таблице через MATCH и этот индекс использовать, ну и аналогично уже с поиском нужной строки.
Правильно понял - нужно добавить нумерацию в исходный файлPR Tracing - заявки по столбцам и строкам? Так как без добавления нумерации - результата от формул нет. Так не вариант, так как файл выгружается каждый день, а следовательно работать я могу только с файлом PR & RFQ2021112.
написал: 2. ...VLOOKUP (ВПР) немного быстрее (примерно на 5 процентов быстрее), проще и использует меньше памяти, чем сочетание MATCH (ПОИСКПОЗ) и INDEX (ИНДЕКС) или OFFSET (СМЕЩ)...
Когда вставил формулу - вспомнил почему я её не использовал - она выдаёт ошибку и отказывается давать значения по формуле:
Код
=VLOOKUP($B3&$E3,'[PR Tracing - заявки.xlsx]PR and PO lines'!$A:$BW,COLUMN(),0)
Только когда указан один критерий. Может у меня какие-то настройки отключены/отсутствуют (Excel 2016)?
Сейчас вспомнил ещё одну "маленькую" проблему, которую ярко видно в заявке PFD12116373-21 по товару 273931 в файле PR Tracing - заявки - там три строки с идентичными критериями, вот только корректными являются данные второй строки у которой заполнен столбец PO NUMBER. Все текущие формулы подтягивают первую строку, а это не корректно, так как в заявке эта строка отменена. В исходном файле PR Tracing - заявки есть столбец, который показывает статус строки Отменено ("Y" - товар не будет закупаться, "N" - товар актуален к закупке, "пусто" - нет информации о необходимости закупа товара или столбец PO NUMBER не заполнен) - иногда заявки или товары в заявке могут быть отменены и не закупаться.
Но эта проблема была мной проигнорирована в моих формулах, так как еще больше усложнила бы формулу, и потребовала бы проверять по ещё дополнительным критериям.
Вот, мне интересно, а через Query всю таблицу как-то реализовать можно?
Как я понял функция COLUMN() привязана к тому же столбцу в обоих файлах, но файл таблица (PR Tracing - заявки) в оригинале содержит на текущий момен 78 столбцов и нужные мне столбцы находятся на больших расстояниях, поэтому в формулах у меня используется привязка к определенным столбцам, чтобы в случае добавления нового произвести сдвиг через замену во всех формулах.
Есть таблица (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(Вспомнил ещё один момент по созданию формулы)
Разобрался практически во всем, а в чем не разобрался - пока не требуется к ознакомлению. Благодаря Вам сегодня я стал сегодня немного умнее. Расширенный режим прояснил многие непонятные вопросы, которые по началу хотел задать. Благодарю!
Андрей VG написал: Только на период обновления запроса. И это чем-то мешает?
Механики ежедневно в течении дня заполняют таблицу в файле "Ноябрь", за тот день, в который происходит работа. В конце дня эти данные они разносят по файлам (Экскаватор и т.д.) для каждой техники, чтобы в будущем их сменщики могли видеть, что делалось на технике в течении года не заходя в каждый файл месяца и не листая каждую вкладку.
Если механику, чтобы изучить историю по файлу требуется нажать только одну-две кнопки, то тут проблемы нет, а вот если ему будет требоваться производить "перезагрузку" данных - будет сложно. Как я говорил ранее - для них Excel - это Word ввиде таблички (это стало причиной почему они ведут сводку по технике в Excel - чтобы не рисовать таблички в Word), поэтому нажать на всплывающих окнах "Обновить", "Включить" и т.п. они могут, а вот какие-то другие махинации... Мне тогда проще им совсем не помогать.
Вся кутерьма вокруг того, что механики по часу сидят в компьютере и занимаются внесением данных. Хочу помочь им, чтобы этот момент с разнесением данных по другим файлам убрать из их объема работы.
Вложил архив. 1. Исходные данные в файлах - Oktyabr и Noyabr 2. В файлах - Погрузчик и Экскаватор - внес все данные как они в идеале должны подгружаться.
artyrH написал: Dukalion , в Power Query попробуйте сделать
Можно побольше конкретики, так как то, что я умею делать в Power Query, никак не отражает требуемых действий и, как я понял, требует постоянного взаимодействия с файлом Ноябрь.
Например, я мог бы использовать в файле Экскаватор формулу в каждой строке =если('[Noyabr.xlsx]05'!$B$9={тут я не знаю пока какое условие попроще можно поставить в качестве фильтра, возможно ВПР из общего списка допустимых уникальных номеров экскаваторов - громоздко, но реализуемо};'[Noyabr.xlsx]05'!$B$9;""), но в конечном итоге такую формулу придется применять по всем вкладкам файла Ноябрь (а это 31 штука), в которых содержится более 30 строк и в конечном итоге у меня получится очень увесистый (за счет формул) файл "Экскаватор". А работа с файлом для анализа поломок и проведенных работ потребует использования фильтра (для скрытия пустых ячеек) и будет выглядеть не особо красиво.
А если учесть, что файл должен будет в себе содержать данные за целый календарный год... это более 10950 строк с формулами.
Во вложении пример таких данных, где KL642 и KL640 - экскаваторы, а LP1225 - погрузчик.
Вопрос - как реализовать это все, по возможности без примения макросов, так как будут заполнять данные люди далекие от таких вещей, а Excel для них тот же Word, только в виде таблицы.