Automatiskt Lägga 20% till en post

April 9

Julie skulle vilja ha några ingångs celler på ett kalkylblad som, när någon kommer in ett värde, automatiskt lägga 20% till vad ingicks. Till exempel, om någon går in 200 i en av dessa celler, vad som faktiskt angav är 240.

Det finns en mängd olika sätt som du kan närma sig denna uppgift med hjälp av makron. Det bästa sättet är att skapa ett makro som körs automatiskt, när en cell ändras i kalkylbladet. Du kan sedan kontrollera om ändringen gjordes i en av indataceller och justera värdena därefter. Följande exempel ändrar det inmatade värdet om det gjordes i någon av tre celler: A1, C3, eller B8.

Private Sub Worksheet_Change (ByVal Target As Range)
Dim RINPUT Såsom Range
Dim rint Såsom Range
Dim rCell Såsom Range

"Ändra ingångscellområdet som önskas
Ställ RINPUT = Range ("A1, C3, B8")

Ställ rint = Intersect (Target, RINPUT)
Om inte rint är ingenting Sedan
För varje rCell I rint
Om IsNumeric (rCell) Then
Med Application
.EnableEvents = False
rCell = rCell * 1,2
.EnableEvents = True
End Med
End If
Nästa
End If
End Sub

Kom ihåg att detta är en händelsehanterare, vilket betyder att det utlöses (i det här fallet) när något förändras i kalkylbladet. För att kunna använda den här macro, högerklicka på fliken bladet och välj Visa koden från den resulterande snabbmenyn. Excel visar VB Editor och du kan sedan lägga till Worksheet_Change koden.

Observera att nyckeln till att räkna ut huruvida ändringen gjordes i en av de tre definierade indataceller är genomskärfunktionen. Den kontrollerar att se om det finns en korsning mellan målintervallet (de förändrade cellerna som utlöste Worksheet_Change handler) och RINPUT sortimentet (dina indataceller). Om det finns, då rint skulle innehålla de celler som gjorde korsar.

Makrot sedan steg genom dessa celler och, om cellerna innehåller numeriska värden, multiplicerar det de celler med 120%. (Multiplicera med 120% är samma som ökar värdet med 20%.) Observera att .EnableEvents egenskapen är inställd på False när multiplikation sker; Om denna garanti inte togs, då varje multiplikation skulle utlösa denna händelse handler igen och skulle du upprepade gånger (och för evigt) multiplicera cellvärdet med 120%.

Om du ville faktiskt göra något annat bearbetning till värden-exempel avrundning till ett visst antal decimaler eller till ett heltalsvärde-då behöver du bara göra en ändring i den enda rad som faktiskt gör multiplikation.

Om dina indataceller är i ett sammanhängande område, kanske en bättre strategi är att definiera dessa ingångsceller som en namngivet område, och sedan använda som namngivet område inom makro att bestämma skärningspunkten mellan de förändrade cellerna. På så sätt behöver du inte ändra makrot när eller om din grupp av indataceller förändras.

För att använda denna metod, låt oss säga att din sortiment av indataceller är B7: B19. Markera de celler och sedan, med hjälp av namnrutan i det övre vänstra hörnet av celler området, ange namnet "plus20pct". Denna åtgärd tilldelar namnet till området. Du kan sedan använda det namnet i makrot.

Private Sub Worksheet_Change (ByVal Target As Range)
Dim rint Såsom Range
Dim rCell Såsom Range

"Ändra ingångscellområdet som önskas

Ställ rint = Intersect (Target, Range ("plus20pct"))
Om inte rint är ingenting Sedan
För varje rCell I rint
Om IsNumeric (rCell) Then
Med Application
.EnableEvents = False
rCell = rCell * 1,2
.EnableEvents = True
End Med
End If
Nästa
End If
End Sub

Notera att den enda förändringen är i vägen att skärningen av celler bestäms-genomskärfunktionen utnyttjar den "plus20pct" intervallet som en parameter. Allt annat fungerar som tidigare.

Nu när du har sett hur man gör detta med hjälp av makron, kvarstår frågan om huruvida du verkligen borde göra det med hjälp av makron. Först finns det gränser för vad dessa makron kan göra. Till exempel, vad händer om din användare anger ett datum eller tid i en av ingångscellerna? Internt Excel hanterar datum och tider som siffror, vilket innebär att de också skulle höjas med 20%.

För det andra, måste du överväga vad som händer med din kalkylblad om någon ändrar kalkylbladsstrukturen genom att lägga till eller delting rader eller kolumner. De makron använder antingen absoluta cellreferenser (A1, C3, och B8) eller ett namngivet område (plus20pct). Medan det namngivna området kan justeras efter rad eller kolumn tillägg eller strykningar, skulle de absoluta celler referenserna inte ändra. Alltså, kan du sluta med det makro kontroll (och justera) celler som inte längre de förväntade datainmatning celler.

Tredje, låt oss säga att någon kommer in ett värde (200) i en av dina indataceller. Det automatiskt ökade med 20% och blir 240. person ser denna förändring och undrade vad som hände, så de väljer cellen och tryck på F2 för att börja redigera cellen. Innan de gör förändringen, minns de att "Oh, ja, det är tänkt att öka med 20% automatiskt." Så, de helt enkelt trycka Enter för att acceptera 240 värdet. Ser dock Excel detta som en förändring och ökar 240 med 20%, vilket resulterade i 288-inte vad du eller användaren avsett.

Denna andra övervägande användar förvirring-är det största potentiella problemet med automatiskt ändra vad en användare skriver in i kalkylbladet. En mindre förvirrande tillvägagångssätt skulle vara att ha en väl definierad inmatningsområdet för din arbetsbok. Användaren sätter siffror i inmatningsområdet och dessa siffror förbli som de indata dem. Då, i andra celler eller i dina formler, gör du justeringen med 20%.

Denna design strategi (modifiera kalkylblad design för enkel inmatning av data) är potentiellt mindre förvirrande för användaren än att automatiskt ändra vad de in i en kalkylbladscell. Det blir också bli av med en risk som är inneboende med någon makroaktiverad arbetsbok-användaren kan ladda arbetsboken utan möjliggör makron, vilket säkerställer att siffrorna inte är justerade som ni tänkt.

ExcelTips är din källa för kostnadseffektiv Microsoft Excel utbildning. Detta tips (12684) gäller Microsoft Excel 2007, 2010, och 2013.