Джин задала вопрос Excel на этой неделе:
Я пытаюсь преобразовать книгу цен поставщиков в Excel, чтобы их коды UPC соответствовали нашей системе кодов UPC. В их прейскуранте есть столбец в Excel с рядом чисел, например. 000004560007 ИЛИ cel000612004. Я должен сделать два раза. Мне нужно убрать первые три нуля, не удаляя другие 0 в ячейке. Затем я должен добавить трехзначный код «upc» перед первой цифрой в ячейке. Это будет постоянная потребность. Мне придется обучить несколько человек пользоваться системой Excel.
Похоже, Жан уже рассматривал возможность использования Edit-Replace, чтобы избавиться от трех нулей. Это не сработает, потому что при замене редактирования на «000004560007» оба вхождения 000 исчезнут.
Сначала я хочу предложить Жану вставить временный столбец рядом с текущими кодами цен, написать формулу для преобразования, а затем использовать Edit-Copy, Edit-PasteSpecial-Values, чтобы скопировать формулу обратно поверх исходных кодов цен.
Плохо документированная функция REPLACE () в этом случае поможет. Меня разочаровала реализация REPLACE (). Я бы подумал, что он попросит заменить конкретный текст, но вместо этого он просит пользователя определить позиции символов для замены. REPLACE заменит часть текстовой строки новой строкой. Четыре аргумента, которые вы передаете функции, - это ячейка, содержащая старый текст, позиция символа в старом тексте, который вы хотите заменить, количество заменяемых символов и новый текст, который нужно использовать.
Мое упрощающее предположение состоит в том, что три нуля представляют начало кода UPC в строке. Таким образом, нет необходимости искать первый числовой символ в ячейке. Как только формула найдет три нуля, она может заменить эти три нуля строкой «upc».
Чтобы найти первое вхождение «000» в текст, вы должны использовать эту формулу:
=FIND(A2,"000")
Формула, которую Жану нужно будет ввести в ячейку B2, будет следующей:
=REPLACE(A2,FIND("000",A2),3,"upc")
Я считаю, что аналитики по ценообразованию выделяют диапазон ячеек, а затем вызывают этот макрос. Макрос будет использовать цикл с «Для каждой ячейки в выделении» в начале. В этом цикле «ячейка» становится специальной переменной диапазона. Вы можете использовать cell.address, cell.value или cell.row, чтобы найти адрес, значение или строку текущей ячейки в цикле. Вот макрос:
Public Sub Jean() ' This macro will replace the first occurrence of "000" ' in each selected cell with the string "upc" ' copyright 1999 www.MrExcel.com For Each cell In Selection cell.Value = Replace(cell.Value, "000", "upc", 1, 1, vbTextCompare) Next cell End SubОбратите внимание, что это макрос деструктивного типа. Когда пользователь выделяет диапазон ячеек и запускает макрос, эти ячейки будут изменены. Само собой разумеется, что вы хотите тщательно протестировать свой макрос на тестовых данных, прежде чем запускать его на реальных производственных данных. Когда у вас есть ситуация, подобная ситуации Джин, когда вам постоянно нужно преобразовывать столбец с помощью сложной формулы, написание простого макроса, подобного показанному здесь, может быть эффективным и экономящим время инструментом.
