Legge til 20% automatisk til en oppføring

January 10  by Eliza

Julie ville gjerne ha noen innspill celler på et regneark som, når noen går inn en verdi, automatisk legge 20% til det som ble lagt inn. For eksempel, hvis noen går inn i 200 inn i en av disse cellene, hva som faktisk skrev, er 240.

Det finnes en rekke måter du kan nærme seg denne oppgaven ved hjelp av makroer. Den beste tilnærmingen er å lage en makro som kjøres automatisk når en celle er endret i regnearket. Deretter kan du sjekke for å se om endringen ble gjort i en av papir celler og justere verdier tilsvarende. Følgende eksempel modifiserer oppgitt verdi hvis det ble gjort i en av tre celler: A1, C3, eller B8.

Private Sub Worksheet_Change (ByVal Target As Range)
Dim rInput Som Range
Dim riv Som Range
Dim Rceii Som Range

'Endre inngangscelleområdet som ønsket
Satt rInput = Range ("A1, C3, B8")

Satt riv = Skjæring (Target, rInput)
Hvis Ikke riv Er Ingenting Deretter
For Hver Rceii I riv
Hvis IsNumeric (Rceii) Deretter
Med Application
.EnableEvents = False
Rceii = Rceii * 1.2
.EnableEvents = True
Avslutt med
End If
Neste
End If
End Sub

Husk at dette er en hendelse handler, som betyr at det er utløst (i dette tilfellet) når noe endres i regnearket. For å kunne bruke denne makroen, høyreklikker du på fanen regneark og velg Vis kode fra den resulterende hurtigmenyen. Excel viser VB Editor og du kan deretter legge til Worksheet_Change koden.

Legg merke til at nøkkelen til å finne ut hvorvidt endringen ble utført i en av de tre definerte inngangsceller er Skjæring funksjon. Den sjekker for å se om det er et kryss mellom målområdet (de endrede celler som utløste Worksheet_Change handler) og rInput rekkevidde (inngangsceller). Hvis det er, så riv ville inneholde de celler som fikk skjærer hverandre.

Makro og deretter går gjennom disse cellene, og hvis cellene inneholder tallverdier, multipliserer det disse cellene med 120%. (Multiplisere med 120% er det samme som å øke verdien med 20%.) Legg merke til at .EnableEvents eiendommen er satt til False når multiplikasjon er gjort; hvis dette ivareta ikke ble tatt, så hver multiplikasjon ville utløse dette hendelseshåndterer igjen, og du vil gjentatte ganger (og alltid) multiplisere celleverdien med 120%.

Hvis du ønsket å faktisk gjøre noen annen behandling til verdier, for eksempel avrunding til et bestemt antall desimaler eller til et heltall verdi så trenger du bare å gjøre en endring i den eneste linje som faktisk gjør multiplikasjon.

Hvis inngangs cellene er i et sammenhengende område, kanskje en bedre tilnærming være å definere disse input celler som et navngitt område, og deretter bruke denne navngitt område innenfor makro å bestemme skjæringspunktet mellom de endrede celler. På den måten trenger du ikke å endre makro når eller hvis gruppen av input celler endres.

For å bruke denne tilnærmingen, la oss si at omfanget av input celler er B7: B19. Merke disse cellene og deretter ved å bruke navnet boksen øverst i venstre hjørne av celler området, skriver du inn navnet "plus20pct". Denne handlingen tildeler navnet til utvalget. Du kan deretter bruke det navnet i makro.

Private Sub Worksheet_Change (ByVal Target As Range)
Dim riv Som Range
Dim Rceii Som Range

'Endre inngangscelleområdet som ønsket

Satt riv = Skjæring (Target, Range ("plus20pct"))
Hvis Ikke riv Er Ingenting Deretter
For Hver Rceii I riv
Hvis IsNumeric (Rceii) Deretter
Med Application
.EnableEvents = False
Rceii = Rceii * 1.2
.EnableEvents = True
Avslutt med
End If
Neste
End If
End Sub

Legg merke til at den eneste endringen er på den måten at skjæringspunktet mellom celler bestemmes-the Snitt funksjonen bruker "plus20pct" range som en parameter. Alt annet fungerer som før.

Nå som du har sett hvordan du gjør dette ved hjelp av makroer, gjenstår spørsmålet om hvorvidt du bør gjøre det ved hjelp av makroer. Først, det er grenser for hva disse makroene kan gjøre. For eksempel, hva hvis brukeren angir en dato eller tid til en av inngangs celler? Internt håndterer Excel datoer og klokkeslett som tall, noe som betyr at de også skulle økes med 20%.

Sekund, må du vurdere hva som skjer med regnearket hvis noen endrer regnearket strukturen ved å legge til eller delting rader eller kolonner. Makroene bruker enten absolutte cellereferanser (A1, C3, og B8) eller et navngitt område (plus20pct). Mens den navngitte området kan justeres rad eller kolonne tillegg eller sletting av, ville de absolutte celler referanser ikke endres. Dermed kan du ende opp med makrokontroll (og justering) celler som ikke lenger er de forventede dataregistrering celler.

Tredje, la oss si at noen går inn en verdi (200) inn i en av dine innspill celler. Det blir automatisk økt med 20% og blir 240. personen ser denne endringen og lurte på hva som skjedde, så de velger cellen og trykker på F2 for å begynne å redigere cellen. Før de gjør endringen, de husker at "Å, ja, det er ment å øke med 20% automatisk." Så, de bare trykke Enter for å godta 240 verdi. Imidlertid ser Excel dette som en endring og øker 240 med 20%, noe som resulterer i 288-ikke hva du eller brukeren ment.

Dette andre hensynet-bruker forvirring-er den største potensielle problemet med automatisk endre hva en bruker kommer inn i regnearket. En mindre forvirrende tilnærming ville være å ha en veldefinert innspill område for arbeidsboken. Brukeren setter tallene inn i inngangsområdet og disse tallene forbli som de innspill dem. Så, i andre celler eller i formler, gjør du justeringen med 20%.

Denne design tilnærming (modifisere regnearket design for enkel dataregistrering) er potensielt mindre forvirrende for brukeren enn automatisk endre hva de gikk inn i en regnearkcelle. Det blir også kvitt en risiko som er iboende med noen makroaktivert arbeidsbok-brukeren kan laste arbeidsboken uten å aktivere makroer, og dermed sikre at tallene ikke er justert slik du hadde tenkt.

ExcelTips er din kilde for kostnadseffektiv Microsoft Excel trening. Dette tipset (12684) gjelder for Microsoft Excel 2007, 2010, og 2013.