Anteckning: Föreläsningen ägnas åt beskrivningen objektmodell MS Excel och detaljerad beskrivning metoder, egenskaper och händelser i applikationen.
13.1. Funktioner i programmering för MS Excel
Microsoft Office Excelär populära kalkylblad. Vanligtvis, programmering för detta program, strävar de efter följande mål:
- Automatisering av beräkningar.
- Automatisering av inmatning och bearbetning av information.
- Arbeta med databaser - output, input, analys, visualisering av information.
- Analys av finansiell och annan information.
- Skapande av system för att organisera automatiserad datainmatning
- Matematisk modellering.
PÅ allmän syn programmering i Excel liknar att arbeta med Microsoft Word. En av de största skillnaderna är dock att i Excel är arbetsytan i kalkylbladet indelad i celler, som var och en har förnamn. Cellnamn kan vara av två typer.
- Den första typen (A1-stil) är namnet, som består av ett bokstavligt kolumnnamn och ett radnummer. Till exempel är A1 cellen som ligger i skärningspunkten mellan kolumn A (den första) och den första raden.
- Den andra typen är cellindex (namnstil R1C1 ). För att adressera en cell i den här stilen, ange radnumret (R - Rad - linje) och kolumnnumret (C - Kolumn - kolumn), i skärningspunkten där cellen är belägen. Raderna är initialt numrerade, och kolumnnumren börjar från 1 - den första kolumnen motsvarar kolumn A, den andra - B, och så vidare. Till exempel, (2, 3) är adressen till cellen som ligger i skärningspunkten mellan den andra raden och den tredje kolumnen, det vill säga om vi flyttar detta till A1-stilen får vi cell C2 ( ris. 13.1.)
Ris. 13.1.
Följande objekt används för att utföra de flesta operationer i MS Excel.
- Excel.Application (Application) - Ett objekt som representerar Microsoft Excel-applikationen, liknande Word.Application .
- Arbetsbok(Arbetsbok) - representerar en arbetsbok - analog Microsoft dokument ord. Men i Word arbetar vi med data som finns i ett dokument, och i Excel finns det ytterligare ett objekt på vägen till datan - ett kalkylblad.
- Arbetsblad(Arbetsblad) - en arbetsbok i MS Excel är indelad i kalkylblad. Det är på arket som celler finns som kan lagra information och formler.
- Område (Range) - kan representeras som en enskild cell eller en grupp av celler. Detta objekt ersätter många objekt för att arbeta med dokumentelement (tecken, ord, etc.) som används i Microsoft Word. Som ett resultat blir arbetet med arket mycket tydligt och bekvämt - för att arbeta med vilken cell som helst behöver du bara veta dess namn (i A1-format) eller adress (R1C1).
- QueryTable (Query Table) - det här objektet används för att importera information från databaser till Microsoft Excel. Databasanslutning, informationsförfrågan osv. produceras med hjälp av objektet, och frågeresultaten laddas upp till MS Excel-arket i form av en vanlig tabell.
- PivotTable (PivotTable) är en speciell typ av Excel-kalkylblad - det låter dig interaktivt sammanfatta och analysera stora mängder information, i synnerhet hämtad från en databas.
- Diagram(Diagram) - representerar ett diagram. De används vanligtvis för datavisualisering.
Låt oss börja recensionen MS Excel objektmodell från applikationsobjektet.
13.2. Applikationsobjekt
MsgBox Excel.Application.Name Notering 13.1. Visa programmets namn
Efter att programmet har körts kommer meddelanderutan att visa namnet på programmet - in det här fallet- Microsoft excel. Uppenbarligen returnerar egenskapen Name för applikationsobjektet applikationens namn.
Tänk nu på det mesta viktiga metoder och applikationsegenskaper. Vissa av dem liknar dem i MS Word. Till exempel stänger Quit-metoden, precis som i Word, applikationen, egenskapen Visible ansvarar för synligheten av programfönstret osv.
13.3. Metoder
13.3.1. Beräkna - påtvingad omräkning
Den här metoden, anropad på Application-objektet, låter dig räkna om alla boköppningar. Det kan också kallas för enskilda böcker (objekt Arbetsbok) ark ( Arbetsblad), celler och deras intervall (Range ). Till exempel koden från Notering 13.2. låter dig räkna alla öppna böcker.
Applikation. Beräkna Notering 13.2. Beräkna om alla öppna böcker
13.3.2. GoTo - flytta till en cell
13-02-Excel GoTo.xlsm- exempel till punkt 13.3.2.
Låter dig välja valfritt cellområde i en bok, och om boken inte är aktiv kommer den att aktiveras. Metoden kan även köra Microsoft Excel-makron.
Hela metodanropet ser ut så här:
Gå till (referens, rulla)
Referensparametern är en referens till en cell eller ett cellområde som ska väljas efter metodanropet. Namnet på makrot kan också användas här - då kommer det att köras.
Scroll-parametern är ansvarig för att "spola tillbaka" Excel-arket till de markerade cellerna - så att det övre vänstra hörnet av markeringen skulle sammanfalla med det övre vänstra hörnet av det visade arkområdet. Om Scroll är satt till True spolas arket tillbaka, om det är False så är det inte det.
Till exempel, ett sådant samtal ( notering 13.3.) låter dig välja cell H500 på det aktiva arket.
Application.Goto _Reference:=ActiveSheet.Range("H500"), _Scroll:=True Notering 13.3. Välj cell H500
Som du kan se är åtkomst till det aktiva arket mycket likt att komma åt det aktiva dokumentet i MS Word. Observera att vi använder ett fullständigt metodanrop - Application.GoTo - som du vet kan du vanligtvis använda egenskaperna och metoderna för Application-objektet i din kod utan att ange detta objekt. Men om du inte anger Application i det här fallet kommer programmet istället för GoTo-metoden att försöka köra ovillkorlig hoppoperatör Gå till .
13.3.3. SendKeys - simulera tangenttryckningar på tangentbordet
13-03-Excel SendKeys.xlsm- exempel till punkt 13.3.3.
En mycket intressant metod - låter dig skicka tangenttryckningar till det aktiva programfönstret. Hela metodanropet ser ut så här:
SendKeys(Keys, Wait)
Parametern Keys låter dig ange vilka nycklar som ska skickas till applikationen. Metoden stöder emulering av både alfanumeriska och kontrollnycklar, för vilken en speciell kodning tillämpas. Alfanumeriska nycklar indikeras när de anropas i deras vanlig form Till exempel, för att skicka "F"-tecknet, måste du ange det när du anropar metoden, och så vidare.
För att skicka en tangenttryckning till en applikation backsteg- använd kod (BS) . För att skicka en knapp tryck Stiga på använd ~ (
Formulärkontroller (Combobox, Checkbox, Counter, etc.) hjälper dig att snabbt ändra data på arket i visst intervall, slå på och av alternativ, gör val osv. I princip kan du klara dig utan dem, men de gör hanteringen av data på arket mer visuell och minskar sannolikheten för att felaktiga uppgifter skrivs in.
För att infoga kontroller på ett ark måste en flik visas Utvecklaren.
- I MS EXCEL 2007 kan detta göras via menyn Office-knapp/ Excel-alternativ/ Allmänt/ Visa fliken utvecklare på menyfliksområdet .
- I MS EXCEL 2010 kan detta göras så här: Öppna fliken Fil; Klicka på knappen alternativ; Klicka på knappen Anpassa band; Välj ett lag Ribbon Customization och huvudflikar markera rutan Utvecklaren.
Nu kan du infoga en kontroll via menyn: .
Observera att i den här menyn kan du infoga ActiveX-kontroller som finns under de formulärkontroller som är intressanta för oss. Båda typerna har samma element Button, List, Checkbox, etc. Skillnaden mellan de två är att du måste använda VBA för att använda ActiveX-kontroller, medan formulärkontroller kan bindas direkt till en cell på ett ark.
För dem som inte tidigare har arbetat med formulärkontroller rekommenderar vi att du bekantar dig med dem i detalj i följande artiklar:
I den här artikeln ska vi ta en titt på mer komplext exempel dela kontroller och .
Exempel
Låt oss analysera specifikt exempel använda flera kontroller samtidigt. Exempelfilen visar hur användaren kan redigera värdena i tabellen med kontroller (intervall F9:K12 ).
Med en av 3 kontroller Kombinationsruta, lista och Disken, användaren kan välja tabellkolumn (år) . Den önskade kontrollen väljs med hjälp av gruppen växlar. Namnet på det valda elementet är markerat i grått(centimeter. A8:B8 i fig. ovan). Det valda året är markerat i tabellen i mörkgrått (se fig. H9:H12 i fig. ovan). Visningen av detta val är justerbar kryssruta(flaggans bakgrund är röd). rullningslister kan redigeras pris och Kvantitet under det valda året, men bara inom ett visst intervall. Nu - mer i detalj.
Växlar
Låt oss först infoga ett element i arket. Med detta element kommer vi att slå på och av valet i tabellen i kolumnen för det valda året. Länka element till cell $G$2 . Om kryssrutan är avmarkerad kommer denna cell att vara FALSK (det här värdet motsvarar 0), om det är markerat, då SANT (det här värdet motsvarar 1).
Välj ett område för inställning. G9:K12 . Eftersom formeln i villkorlig formatering kommer att innehålla kommer vi att se till att efter att ha valt intervallet är den aktiva cellen G9 (dvs intervallet måste väljas med början från det. Tipset är fältet namn, som ligger till vänster om Formel barer. Efter att ha valt ett intervall ska det innehålla G 9 ).
- ring verktyget ( Hem/ Stilar/ Villkorlig formatering/ Skapa regel);
- Välj Använd formel för att definiera formaterade celler;
- i fält" Formatvärden för vilka följande formelär sant" stiga på =OCH(KOLUMN(G9)=ÅrOffset+6,2$G$) Formeln blir SANT när två villkor är uppfyllda samtidigt:
- uttrycksvärde ( OffsetYear(ändrar från 1 till 5 (dvs. 2009 till 2013) + 6) matchar det aktuella kolumnnumret (7, d.v.s. 2009);
- Kryssruta Villkorlig formatering installerat.
- välj önskat format, till exempel grå fyllningsfärg;
- klicka på OK.
Testning
- se till att kryssrutan är Villkorlig formatering installerad;
- välj alternativknappen Lista;
- i kontrollen Lista välj 2010;
- se till att kolumnen 2010 markerad i grått;
- rullningsliständra kvantiteten i kolumn 2010.
Resultatet visas i figuren.
Tyvärr, formulärkontroller Kryssruta, kombinationsruta och Lista det finns inget sätt att formatera det visade teckensnittet. Men detta kan göras för ActiveX-kontroller ( Utvecklare/ Kontroller/ Klistra in). Det är sant att för att arbeta med dessa element måste du skriva ett program i VBA.
Frizen Irina Grigorievna - kandidat pedagogiska vetenskaper, lärare informationsteknik, författare till ett flertal verk inom området informationsteknologi.
Denna studiehandledning har skrivits i enlighet med statligt program studera disciplin för sekundär special läroinstitut specialitet 230103" Automatiserade system informationsbehandling och hantering".
Office-programmering
Manualen ger tillräckligt med material för att utföra praktiska övningar efter disciplin, samt det grundläggande teoretiska material som behövs för att studera en viss fråga. På basis av det föreslagna materialet är det möjligt att bygga en olika sorter klasser.
Läroboken syftar till att hjälpa elever att studera denna disciplin i till fullo som uppfyller kraven statlig standard genom disciplin.
Den är avsedd för lärare och studenter vid sekundära specialiserade utbildningsinstitutioner och kan också användas av studenter vid högre utbildningsinstitutioner som studerar denna disciplin.
PÅ studiehandledningen mer än 60 problem övervägs, åtföljda av 130 ritningar och detaljerade förklaringar.
Bok:
Avsnitt på denna sida:
Använda intervall- och urvalsobjekt
I Excel är det viktigaste Application-objektet. Applikationsobjektet är det översta objektet i Excel-objekthierarkin och representerar själva Excel-applikationen. Den har över 120 egenskaper och 40 metoder. Dessa egenskaper och metoder är till för att ställa in allmänna alternativ Excel-applikationer. I Excel-hierarkin kommer arbetsboksobjektet omedelbart efter applikationsobjektet och representerar arbetsboksfilen. Arbetsboken lagras antingen i formatet XLS (standard arbetsbok) eller XLA (fullständigt kompilerad applikation). Arbetsboksegenskaper och metoder låter dig arbeta med filer. Det mest "använda" i praktiken är dock Range-objektet, som det bästa sättet visar möjligheterna att använda VBA i Excel (för egenskaperna för Range-objektet, se Tabell 19, för metoder, se Tabell 20).
I en Excel-objekthierarki Räckvidd(intervall) kommer omedelbart efter objektet arbetsblad. Ett objekt Räckviddär ett av nyckelobjekten för VBA. Selection-objektet (selektion) sker i VBA på två sätt - antingen som ett resultat av Select-metoden, eller när select-egenskapen anropas. Typen av det resulterande objektet beror på typen av det valda objektet. Oftast hör Selection-objektet till Range-klassen och när du arbetar med det kan du använda Range-objektets egenskaper och metoder. Ett intressant inslag Intervall- och urvalsobjekt är att de inte är medlemmar i någon objektfamilj.
När du arbetar med Range-objektet måste du komma ihåg hur Excel refererar till en kalkylbladscell.
Ange rad- och kolumngrupper med intervallobjektet
Om endast kolumn- eller radnamn anges i intervallet, anger Range-objektet intervallet för de angivna kolumnerna eller raderna. Till exempel anger Range("a:c") ett intervall som består av kolumnerna a, b och c, medan Range("2:2") anger den andra raden. Ett annat sätt att arbeta med rader och kolumner är metoderna Rader (rader) och kolumner (kolumner), som returnerar samlingar av rader och kolumner. Till exempel är kolumn a kolumner(1) och rad 2 är rader(2).
Relation mellan Range-objektet och Cells-egenskapen
Eftersom en cell är ett specialfall av ett område, som bara består av en enda cell, låter Range-objektet dig också arbeta med det. Cells-objektet (celler) är alternativt sätt cellarbete. Till exempel beskrivs cell A2 som ett objekt av Range("A2") eller Cells(l, 2). I sin tur låter cellobjektet, kapslat i Range, dig också skriva området i en alternativ form, vilket ibland är praktiskt för arbete, nämligen Range("A2: C3") och Range(Cells(1,2), Celler(3,3)) definierar samma område.
Tabell 19
Egenskaper för intervallobjekt
Range Object Methods
Områdesobjektmetoder som använder Excel-kommandon
Kommandona och metoderna inbyggda i Excel låter dig arbeta effektivt med ett intervall: fyll det med element i ett mönster, sortera, filtrera och konsolidera data, bygg en sammanfattningstabell och skapa scenarier och lös en icke-linjär ekvation med en variabel.
Autofyll-metod
Autofyll-metoden fyller automatiskt cellerna i ett intervall med sekvenselement. Autofyll-metoden skiljer sig från DataSeries-metoden genom att den uttryckligen anger det intervall inom vilket progressionen kommer att placeras. Manuellt är denna metod likvärdig med att placera muspekaren på fyllningshandtaget för det valda intervallet (i vilket värdena som genererar den genererade sekvensen skrivs in) och dra fyllningshandtaget längs intervallet där sekvensen som ska skapas kommer att vara lokaliserad.
Syntax:
ett objekt. Autofyll (intervall, typ)
Argument:
Range Intervall från vilken fyllning startar typ Giltiga värden: xlFillDefault, xlFillSeries, xlFillCopy, xlFillFormats, xlFillValues,xlFillDays, xlFillWeekdays, xlFillMonths, xlFillYears, xlLinearTrendT, xlLinearTrendT. xlFillDefault
Autofiltermetod
Autofiltermetoden är ett enkelt sätt att fråga och filtrera data i ett kalkylblad. Om AutoFilter är aktiverat förvandlas varje fältrubrik i det valda dataintervallet till en listruta. Om du väljer en begäran om att visa data i ett fält med en rullgardinslista visas endast de poster som uppfyller de angivna villkoren. Listrutan innehåller följande typer av villkor: Alla (Alla), Topp tio (Topp 10), Skick (Anpassad), specifikt dataelement, Blanks (Blanks) och Non-blanks (NonBlanks). Manuellt startas metoden genom att välja kommandot Data, Filter, Autofilter (Data, Filter, AutoFilter).
Två syntaxer är giltiga när du använder AutoFilter-metoden.
Syntax 1:
Ett objekt. AutoFilter
I det här fallet väljer eller avbryter metoden AutoFilter kommandot Data, Filter, AutoFilter som tillämpas på intervallet som anges i objektargumentet.
Syntax 2:
Ett objekt. AutoFilter(fält, villkor1, operatör, villkor2)
I det här fallet utför AutoFilter-metoden kommandot Data, Filter, AutoFilter enligt de kriterier som anges i argumentet.
Argument:
fält Ett heltal som anger fältet som data ska filtreras på
Kriterier 1 Set två möjliga förhållanden fält för filtrering och kriterier2. Du kan använda en strängkonstant, till exempel 101, och relationstecknen >,<,>=, <=, =, <>
operatör Giltiga värden: X1And (logisk förening av det första och andra kriteriet); X1or (logiskt tillägg av det första och andra kriteriet)
När du arbetar med filter är metoden showAllData och egenskaperna FilterMode och AutoFilterMode användbara.
ShowAllData Method Visar alla filtrerade och ofiltrerade kalkylbladsrader
FilterMode-egenskap Giltiga värden: True (om kalkylbladet har filtrerat data med dolda rader), False (annars)
AutoFilterMode-egenskap Giltiga värden: True (om kalkylbladet visar rullgardinsmenyn AutoFilter-metoden), False (annars)
GoalSeek-metoden
GoalSeek-metoden (val av en parameter) väljer värdet på en parameter (ett okänt värde) som är en lösning på en ekvation med en variabel. Det antas att ekvationen reduceras till formen: den högra sidan är konstant, oberoende av parametern, som endast ingår i den vänstra sidan av ekvationen. Manuellt utförs GoalSeek-metoden med kommandot Service, Parameter Selection (Verktyg, Goal Seek). GoalSeek-metoden beräknar roten med metoden för successiva approximationer, vars resultat generellt sett beror på den initiala approximationen. Därför, för korrektheten att hitta roten, måste man ta hand om den korrekta indikationen av denna initiala approximation.
Syntax:
Ett objekt. GoalSeek(Goal, ChangingCell)
Argument:
Ett objekt En cell där en formel skrivs in som är den högra sidan av ekvationen som ska lösas. I den här formeln spelas parameterns roll (okänt värde) av en referens till cellen som anges i ChangingCell-argumentet
Mål Värdet på den vänstra sidan av ekvationen som löses som inte innehåller en parameter
Precisionen med vilken roten hittas och det högsta tillåtna antalet iterationer som används för att hitta roten ställs in av egenskaperna Maxchange och Maxiterations för Application-objektet. Till exempel, bestämning av roten med en noggrannhet på 0,0001 för maximalt 1000 iterationer ställs in av instruktionen:
Med applikation
maxiterationer = 1000
MaxChange = 0,0001
Dessa värden ställs in manuellt på fliken Beräkning i dialogrutan Alternativ som anropas av kommandot Verktyg, Alternativ.
Sorteringsmetod
Sortering låter dig ordna data i lexikografisk ordning i stigande eller fallande ordning. Sorteringsmetoden sorterar list- och databasrader och kalkylbladskolumner baserat på upp till tre sorteringskriterier. Att sortera data manuellt görs med kommandot Data, Sort.
Syntax:
Ett objekt. Sortera(nyckel1, order1, nyckel2, order2, nyckel3, order3, header, orderCustom, matchCase, orientaticn)
Argument:
Ett objekt Det intervall som ska sorteras
Beställning 1
order2 Anger beställningsordningen. Giltiga värden: xlStigande (stigande ordning); xlFallande (fallande ordning)
rubrik Giltiga värden är: xlYes (den första raden i intervallet innehåller en rubrik som inte är sorterad); xlNo (den första raden i intervallet innehåller ingen rubrik, detta värde anses som standard); xlGuess (Excel avgör om det finns en titel)
beställa Anpassad Anpassad sorteringsordning. Standard är Normal
liknande fall Giltiga värden: True (skiftlägeskänslig) och False (skiftlägeskänslig)
orientering Giltiga värden: xlTopToBottom (sorteringen görs uppifrån och ned, d.v.s. efter rader); xlLeftToRight (vänster till höger, d.v.s. efter kolumner)
Till exempel sorteras intervallet A1:C20 för kalkylblad 1 med följande kommando i stigande ordning, så att den initiala sorteringen är i den första kolumnen i detta intervall och den sekundära sorteringen är i den andra:
Arbetsblad("Sheet").Range("A1: C20").Sortera _
key1:=Worksheets("Sheet1").Range("A1"), _
key2:=Worksheets("Sheet1").Range("B1")
Avrundade siffror
Du behöver ofta avrunda decimaltal, särskilt när du arbetar med penningvärden. VBA erbjuder ingen direkt lösning på sådana problem, men teknikerna som diskuteras nedan hjälper dig att lösa dessa problem.
1 sätt
Rund funktion
X=runda(2,505, 2)
x-värdet blir 2,5, inte 2,51.
Därför används det inte ofta.
2 sätt
Formateringsfunktion
sngRounding=Format(SngUnrounded, “#, 0.00”)
3 sätt
Funktionen FormatNumber
SngRounding= FormatNumber(sbgUnrounded, 2)
Om du vill ändra decimalerna ändrar du antalet nollor efter decimaltecknet i argumentet Format, eller ändrar talet som anger värdet på det andra argumentet till vad du vill.
Notera. Variabeln som det avrundade värdet placeras i måste vara av typen string, singel, dubbel, decimal, valuta eller variant, inte heltal eller lång.
Datareduktion
För att casta inmatade data till önskad typ, inkluderar VBA en omfattande uppsättning funktioner, varav en är CDBL. Syntax:
CDbl(uttryck)
Krävs argument uttryckär vilken sträng eller ett numeriskt uttryck som helst. För att läsa informationen som anges i textfältet i det genererade formuläret, skriv in en variabel och skriv ett uttryck:
A = Cdbl(textBoxN.text)
Efter det kan du arbeta med denna variabel.
För att visa värden direkt i cellerna i en Excel-arbetsbok är det bekvämt att använda Range-objektet:
range("A5").värde = a
Funktionen motsatsen till CDbl är funktionen CStr - den konverterar tal till strängar och är bekväm för att visa resultatet antingen i en cell på ett ark eller i en viss textruta.
TextBoxN.text = CStr(.Range("A8").värde)
- läsa värdet från cellen och visa det i textrutan.
Trim(sträng)-funktionen returnerar en kopia av strängen med blanksteg i början och slutet av strängen borttagna.
Skapa VBA-program
Använder GoalSeek-metoden
Exempel 41. Utveckla ett program som, med hjälp av de inmatade numeriska värdena i någon ekvation, löser denna ekvation och hittar den okända variabeln x. Resultatet av beräkningen visas i en textruta på formuläret och på ett Excel-ark.
Ris. 92. Den utvecklade formen av exempel 41 i fungerande skick
Utförandeteknik
1. Starta Excel-applikationen, spara dokumentet.
2. Växla till VBA-redigeraren.
3. Skapa ett formulär enligt fig. 92.
4. Placera den önskade texten (formatering) på Excel-arket och tillhandahålla lämplig informationsutdataceller (Fig. 93).
Ris. 93. Utmatning av resultat till ett excelark efter att ha kört exempelformulär 41
5. Bearbeta knapparna.
Knappen Beräkna
Private Sub CommandButton1_Click()
Dim a, b, c Som dubbel
a = cbl(TextBox1.Text)
b = cbl(TextBox2.Text)
c = CDbl(TextBox3.Text)
Med ActiveSheet
Range("b3").Värde = a
Range("b4").Värde = b
Range("b5").Värde = c
Range("b6").FormulaLocal = "=b3*b7^3+b4*sin(b7)"
Range("b6").GoalSeek Goal:=c, changingCell:=Range("b7")
TextBox4.Text = CStr(.Range("b7").Value)
TextBox4.Text = FormatNumber(TextBox4.Text, 2)
Stäng-knapp
Private Sub CommandButton2_Click()
Forminitieringsprocedur
Private Sub UserForm_initialize()
Arbetsblad(1).Synlig = Falskt
Använda autofyllmetoder när du fyller tabeller
Exempel 42 . Skapa ett program som, enligt textdata som anges i de lämpliga textfälten i formuläret, automatiserar datainmatning för studenter från en viss specialitet vid en utbildningsinstitution. Resultaten av att fylla i textfälten visas på ett excel-ark, som låter dig skriva ut data vid behov.
