Jean položila tento týždeň otázku programu Excel:
Snažím sa previesť knihu cien dodávateľov v programe Excel tak, aby zodpovedala ich kódom UPC nášmu systému kódov UPC. Ich cenová kniha má v Exceli stĺpec so sériou čísel, napr. 000004560007 ALEBO cel000612004. Čo musím urobiť, je dvojité. Potrebujem vyradiť prvé tri nuly bez toho, aby som vymazal akékoľvek ďalšie nuly v bunke. Ďalej musím pred prvú číselnú číslicu v bunke pripojiť trojmiestny kód „upc“. Bude to pretrvávajúca potreba. Na používanie systému Excel budem musieť vyškoliť niekoľko ľudí.
Znie to, že Jean už uvažovala o použití funkcie Edit-Replace na zbavenie sa troch núl. To by nefungovalo, pretože vykonanie nahradenia úprav na čísle „000004560007“ by spôsobilo zmiznutie obidvoch výskytov 000.
Najprv chcem navrhnúť, aby Jean vložil dočasný stĺpec vedľa aktuálnych cenových kódov, napísal vzorec na vykonanie transformácie a potom pomocou príkazov Edit-Copy, Edit-PasteSpecial-Values skopíroval vzorec späť na pôvodné cenové kódy.
V tomto prípade bude stačiť zle zdokumentovaná funkcia REPLACE (). Bol som sklamaný z implementácie REPLACE (). Myslel by som si, že požiada o nahradenie konkrétneho textu, ale namiesto toho požiada používateľa, aby zistil polohy znakov, ktoré má nahradiť. REPLACE nahradí časť textového reťazca novým reťazcom. Štyri argumenty, ktoré odovzdáte funkcii, sú bunka obsahujúca starý text, pozícia znaku v starom texte, ktorý chcete nahradiť, počet znakov, ktoré sa majú nahradiť, a nový text, ktorý sa má použiť.
Môj zjednodušujúci predpoklad je, že tri nuly predstavujú začiatok UPC kódu v reťazci. Nie je teda potrebné hľadať prvý číselný znak v bunke. Keď vzorec nájde tri nuly, môže tieto tri nuly nahradiť reťazcom „upc“.
Na vyhľadanie umiestnenia prvého výskytu „000“ v texte by ste použili tento vzorec:
=FIND(A2,"000")
Vzorec, ktorý by Jean musela zadať do bunky B2, by bol:
=REPLACE(A2,FIND("000",A2),3,"upc")
Mojou myšlienkou je, aby cenoví analytici zvýraznili celý rad buniek a potom vyvolali toto makro. Makro použije na začiatku slučku s textom „Pre každú bunku vo výbere“. S touto slučkou sa „bunka“ stáva premennou špeciálneho rozsahu. Môžete použiť cell.address alebo cell.value alebo cell.row na vyhľadanie adresy, hodnoty alebo riadku aktuálnej bunky v cykle. Toto 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 SubUpozorňujeme, že toto je makro deštruktívneho typu. Keď používateľ zvýrazní rozsah buniek a spustí makro, tieto bunky sa zmenia. Je samozrejmé, že chcete svoje makro dôkladne otestovať na testovacích dátach skôr, ako ho uvoľníte na skutočných produkčných údajoch. Ak sa nachádzate v situácii ako Jean, kde od vás bude neustále vyžadovať transformáciu stĺpca pomocou zložitého vzorca, môže byť efektívne a časovo nenáročné napísanie jednoduchého makra, ako je ten, ktorý je tu znázornený.
