Jean stilte ukens Excel-spørsmål:
Jeg prøver å konvertere en leverandørprisbok i Excel for å matche UPC-kodene deres til UPC-kodesystemet vårt. Prisboken deres har en kolonne i Excel med en rekke tall, f.eks. 000004560007 ELLER cel000612004. Det jeg må gjøre er to ganger. Jeg må ta ut de tre første nullene uten å slette noen andre 0-er i cellen. Deretter må jeg legge til en 3-sifret kode "upc" før det første numeriske sifferet i cellen. Dette vil være et kontinuerlig behov. Jeg må trene flere personer til å bruke Excel-systemet.
Det høres ut som Jean allerede vurderte å bruke Edit-Replace for å kvitte seg med de tre nuller. Dette fungerer ikke fordi å gjøre en redigering erstattet "000004560007" vil føre til at begge forekomster av 000 forsvinner.
Først vil jeg foreslå at Jean setter inn en midlertidig kolonne ved siden av gjeldende priskoder, skriver en formel for å gjøre transformasjonen, og deretter bruker Rediger-Kopier, Rediger-Lim inn spesielle verdier for å kopiere formelen tilbake over de opprinnelige priskodene.
Den dårlig dokumenterte REPLACE () -funksjonen vil gjøre susen i dette tilfellet. Jeg var skuffet over implementeringen av REPLACE (). Jeg hadde trodd det ville be om en bestemt tekst å erstatte, men i stedet ber den brukeren finne ut tegnposisjonene som skal erstattes. REPLACE erstatter en del av en tekststreng med en ny streng. De fire argumentene du sender til funksjonen er cellen som inneholder den gamle teksten, posisjonen til tegnet i gammel tekst du vil erstatte, antall tegn som skal erstattes og den nye teksten du skal bruke.
Min forenkling antagelse er at de tre nullene representerer begynnelsen av UPC-koden i strengen. Dermed er det ikke nødvendig å søke etter det første numeriske tegnet i cellen. Når formelen har funnet de tre nullene, kan den erstatte de tre nuller med strengen "upc".
For å finne plasseringen for den første forekomsten av "000" i teksten, bruker du denne formelen:
=FIND(A2,"000")
Formelen som Jean trenger å legge inn i celle B2 vil være:
=REPLACE(A2,FIND("000",A2),3,"upc")
Min tanke er å la prisanalytikerne fremheve en rekke celler og deretter påberope seg denne makroen. Makroen bruker en sløyfe med "For hver celle i utvalg" i begynnelsen. Med denne sløyfen blir "celle" en spesiell rekkevidde. Du kan bruke cell.address eller cell.value eller cell.row for å finne adressen, verdien eller raden til den gjeldende cellen i sløyfen. Her er makroen:
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 SubMerk at dette er en destruktiv makro. Når brukeren fremhever et område med celler og kjører makroen, vil disse cellene endres. Det sier seg selv at du vil teste makroen din grundig på testdata før du setter den løs på ekte produksjonsdata. Når du har en situasjon som Jean, hvor du hele tiden vil bli pålagt å transformere en kolonne ved hjelp av en kompleks formel, kan det være et effektivt og tidsbesparende verktøy å skrive en enkel makro som illustrert her.
