Жан зададе въпроса за Excel тази седмица:
Опитвам се да преобразувам ценовата книга на доставчика в Excel, за да съвпадат техните UPC кодове с нашата UPC кодова система. Ценовата книга има колона в Excel с поредица от числа, напр. 000004560007 ИЛИ cel000612004. Това, което трябва да направя, е два пъти. Трябва да извадя първите три нули, без да изтривам други 0 в клетката. След това трябва да добавя 3-цифрен код "upc" преди първата цифрова цифра в клетката. Това ще бъде постоянна нужда. Ще трябва да обуча няколко души да използват системата на Excel.
Звучи, че Джин вече е обмислял да използва Edit-Replace, за да се отърве от трите нули. Това няма да работи, тъй като извършването на замяна на редактиране на "000004560007" ще доведе до изчезването на двете случаи на 000.
Първо искам да предложа на Жан да вмъкне временна колона до текущите кодове на цените, да напише формула, за да извърши трансформацията, след това да използва Edit-Copy, Edit-PasteSpecial-Values, за да копира формулата обратно върху оригиналните кодове на цените.
Лошо документираната функция REPLACE () ще свърши работа в този случай. Бях разочарован от изпълнението на REPLACE (). Бих си помислил, че ще поиска специфичен текст, който да замени, но вместо това иска потребителят да измисли позициите на символите, които да замени. REPLACE ще замени част от текстов низ с нов низ. Четирите аргумента, които предавате на функцията, са клетката, съдържаща стария текст, позицията на знака в стария текст, който искате да замените, броят на символите, които трябва да замените, и новият текст, който да използвате.
Моето опростяващо предположение е, че трите нули представляват началото на UPC кода в низа. По този начин не е необходимо да се търси първият цифров знак в клетката. След като формулата намери трите нули, тя може да замени тези три нули с низа "upc".
За да намерите местоположението на първата поява на "000" в текста, бихте използвали тази формула:
=FIND(A2,"000")
Формулата, която Жан ще трябва да въведе в клетка В2, ще бъде:
=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Имайте предвид, че това е макрос от разрушителен тип. Когато потребителят маркира диапазон от клетки и стартира макроса, тези клетки ще бъдат променени. От само себе си се разбира, че искате да тествате изцяло макроса си върху тестови данни, преди да го освободите от реални производствени данни. Когато имате ситуация като тази на Жан, при която непрекъснато ще се изисква да преобразувате колона, използвайки сложна формула, писането на прост макрос като този, показан тук, може да бъде ефективен и спестяващ време инструмент.
