Jean položila tento týden Excel otázku:
Snažím se převést knihu cen dodavatelů v aplikaci Excel tak, aby odpovídala jejich kódům UPC našemu systému kódů UPC. Jejich ceník má sloupec v aplikaci Excel s řadou čísel, např. 000004560007 NEBO cel000612004. To, co musím udělat, je dvojí. Musím vyndat první tři nuly, aniž bych smazal další 0 v buňce. Dále musím před první číselnou číslici v buňce připojit 3místný kód „upc“. To bude trvalá potřeba. Budu muset vyškolit několik lidí, aby používali systém Excel.
Zní to, jako by Jean již uvažovala o použití funkce Edit-Replace, jak se zbavit tří nul. To by nefungovalo, protože provedení nahrazení úprav na „000004560007“ by způsobilo, že oba výskyty 000 zmizí.
Nejprve chci navrhnout, aby Jean vložil dočasný sloupec vedle aktuálních cenových kódů, napsal vzorec pro provedení transformace, pak pomocí Edit-Copy, Edit-PasteSpecial-Values zkopíroval vzorec zpět přes původní cenové kódy.
Špatně zdokumentovaná funkce REPLACE () v tomto případě provede trik. Byl jsem zklamán implementací REPLACE (). Myslel bych si, že požádá o nahrazení konkrétního textu, ale místo toho požádá uživatele, aby zjistil pozice znaků, které se mají nahradit. REPLACE nahradí část textového řetězce novým řetězcem. Čtyři argumenty, které předáte funkci, jsou buňka obsahující starý text, pozice znaku ve starém textu, který chcete nahradit, počet znaků, které se mají nahradit, a nový text, který se má použít.
Můj zjednodušující předpoklad je, že tři nuly představují začátek kódu UPC v řetězci. Není tedy nutné hledat první číselný znak v buňce. Jakmile vzorec najde tři nuly, může tyto tři nuly nahradit řetězcem „upc“.
K vyhledání umístění prvního výskytu „000“ v textu použijete tento vzorec:
=FIND(A2,"000")
Vzorec, který by Jean musela zadat do buňky B2, by byl:
=REPLACE(A2,FIND("000",A2),3,"upc")
Moje myšlenka je nechat analytiky stanovení cen zvýraznit řadu buněk a poté vyvolat toto makro. Makro použije na začátku smyčku s textem „Pro každou buňku ve výběru“. S touto smyčkou se „buňka“ stává speciální proměnnou rozsahu. K vyhledání adresy, hodnoty nebo řádku aktuální buňky ve smyčce můžete použít cell.address nebo cell.value nebo cell.row. Tady je makro:
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 SubVšimněte si, že se jedná o makro destruktivního typu. Když uživatel zvýrazní řadu buněk a spustí makro, tyto buňky se změní. Je samozřejmé, že chcete své makro důkladně otestovat na testovacích datech, než jej uvolníte na skutečných produkčních datech. Pokud máte situaci, jako je Jean, kde budete neustále povinni transformovat sloupec pomocí složitého vzorce, psaní jednoduchého makra, jako je ten, který je zde znázorněn, může být efektivním a časově úsporným nástrojem.
