Operationen mit Microsoft Excel-Objekten. Formularsteuerelemente in MS EXCEL

Formular-Steuerelemente (Combobox, Checkbox, Counter usw.) helfen Ihnen, die Daten auf dem Blatt in einem bestimmten Bereich schnell zu ändern, Optionen ein- und auszuschalten, Auswahlen zu treffen usw. Im Prinzip können Sie darauf verzichten, aber sie steuern Die Daten auf dem Blatt sind besser sichtbar und verringern die Möglichkeit der Eingabe falscher Daten.

Um Steuerelemente auf einem Blatt einzufügen, muss eine Registerkarte angezeigt werden Entwickler.

In MS EXCEL 2007 kann dies über das Menü erfolgen Office-Schaltfläche/Excel-Optionen/Allgemein/Entwicklerregisterkarte in der Multifunktionsleiste anzeigen .
In MS EXCEL 2010 geht das so: Öffnen Sie die Registerkarte Datei; Drück den Knopf Optionen; Drück den Knopf Band anpassen; Wähle ein Team Ribbon-Anpassung und Hauptregisterkarten Aktivieren Sie das Kontrollkästchen Entwickler.

Jetzt können Sie über das Menü ein Steuerelement einfügen: .

Beachten Sie, dass Sie in diesem Menü ActiveX-Steuerelemente einfügen können, die sich unterhalb der für uns interessanten Formularsteuerelemente befinden. Beide Typen haben die gleichen Elemente Schaltfläche, Liste, Kontrollkästchen usw. Der Unterschied zwischen den beiden besteht darin, dass Sie VBA verwenden müssen, um ActiveX-Steuerelemente zu verwenden, während Formularsteuerelemente direkt an eine Zelle auf einem Blatt gebunden werden können.

Für diejenigen, die bisher noch nicht mit Form Controls gearbeitet haben, empfehlen wir, sich in den folgenden Artikeln ausführlich damit vertraut zu machen:

In diesem Artikel betrachten wir ein komplexeres Beispiel für die Freigabe von Steuerelementen und .

Beispiel

Lassen Sie uns ein konkretes Beispiel für die gleichzeitige Verwendung mehrerer Steuerelemente analysieren. Die Beispieldatei zeigt, wie der Benutzer die Werte in der Tabelle mithilfe von Steuerelementen bearbeiten kann (range F9:K12 ).

Mit einem von 3 Bedienelementen Kombinationsfeld, Liste und Zähler, Benutzer kann Tabellenspalte (Jahr) auswählen . Über die Gruppe wird das gewünschte Control ausgewählt Schalter. Der Name des ausgewählten Elements wird grau hinterlegt (siehe Abb. A8:B8 in Abb. Oben). Das ausgewählte Jahr wird in der Tabelle dunkelgrau hinterlegt (siehe Abb. H9:H12 in Abb. Oben). Die Anzeige dieser Auswahl ist einstellbar Kontrollkästchen(Flaggenhintergrund ist rot). Bildlaufleisten können bearbeitet werden Preis und Menge im gewählten Jahr, aber nur innerhalb einer bestimmten Bandbreite. Jetzt - ausführlicher.

Schalter

Zuerst fügen wir ein Element in das Blatt ein. Mit diesem Element schalten wir die Auswahl in der Tabelle der Spalte des ausgewählten Jahres ein und aus. Element mit Zelle verknüpfen $G$2 . Wenn das Kontrollkästchen deaktiviert ist, ist diese Zelle FALSE (dieser Wert entspricht 0), wenn es aktiviert ist, dann TRUE (dieser Wert entspricht 1).

Wählen Sie einen Bereich für die Einstellung aus. G9:K12 . Da die Formel in der bedingten Formatierung enthalten wird, stellen wir sicher, dass nach Auswahl des Bereichs die aktive Zelle G9 ist (d. h. der Bereich muss ausgehend von ihr ausgewählt werden. Der Hinweis ist das Feld Name, befindet sich links von Formelriegel. Nachdem Sie einen Bereich ausgewählt haben, sollte er enthalten G 9 ).

Rufen Sie das Tool auf ( Startseite/ Stile/ Bedingte Formatierung/ Regel erstellen);
auswählen Formel verwenden um formatierte Zellen zu definieren;
auf dem Feld " Formatieren Sie Werte, für die die folgende Formel gilt" Eintreten =AND(COLUMN(G9)=YearOffset+6,$G$2) Die Formel wird WAHR, wenn 2 Bedingungen gleichzeitig erfüllt sind:
Ausdruckswert ( OffsetYear(ändert sich von 1 auf 5 (also 2009 auf 2013) + 6) entspricht der aktuellen Spaltennummer (7, also 2009);
Kontrollkästchen Bedingte Formatierung Eingerichtet.
Wählen Sie das gewünschte Format aus, z. B. graue Füllfarbe.
OK klicken.

Testen

Stellen Sie sicher, dass das Kontrollkästchen aktiviert ist Bedingte Formatierung Eingerichtet;
wählen Sie das Optionsfeld Aufführen;
in der Kontrolle Aufführen wählen Sie 2010;
Stellen Sie sicher, dass die Spalte 2010 grau hervorgehoben;
Scrollleisteändern Sie die Menge in Spalte 2010.

Das Ergebnis ist in der Abbildung dargestellt.

Leider Formularkontrollen Kontrollkästchen, Kombinationsfeld und Aufführen Es gibt keine Möglichkeit, die angezeigte Schriftart zu formatieren. Dies kann jedoch für ActiveX-Steuerelemente ( Entwickler/ Steuerelemente/ Einfügen). Richtig, um mit diesen Elementen zu arbeiten, müssen Sie ein Programm in VBA schreiben.

Um die Dateneingabe in eine Tabelle in Excel zu erleichtern, können Sie spezielle Formulare verwenden, die das Füllen eines Tabellenbereichs mit Informationen beschleunigen. Excel verfügt über ein integriertes Tool, mit dem Sie auf diese Weise ausfüllen können. Außerdem kann der Benutzer mithilfe eines Makros seine eigene Version des Formulars erstellen, die maximal an seine Bedürfnisse angepasst wird. Werfen wir einen Blick auf die verschiedenen Verwendungsmöglichkeiten dieser nützlichen Füllwerkzeuge in Excel.

Das Ausfüllformular ist ein Objekt mit Feldern, deren Namen den Namen der Spalten der auszufüllenden Tabellenspalten entsprechen. Sie müssen Daten in diese Felder eingeben und sie werden sofort als neue Zeile zum Tabellenbereich hinzugefügt. Das Formular kann sowohl als separates integriertes Excel-Tool fungieren als auch direkt auf dem Blatt als Bereich liegen, wenn es vom Benutzer selbst erstellt wird.

Sehen wir uns nun an, wie diese beiden Arten von Tools verwendet werden.

Methode 1: Eingebautes Objekt für die Excel-Dateneingabe

Lassen Sie uns zunächst lernen, wie Sie das integrierte Formular für die Excel-Dateneingabe verwenden.

Es sollte beachtet werden, dass das Symbol, das es startet, standardmäßig ausgeblendet ist und aktiviert werden muss. Gehen Sie dazu auf die Registerkarte "Datei", und klicken Sie dann auf das Element "Optionen".

Wechseln Sie im sich öffnenden Excel-Optionsfenster zum Abschnitt "Schnellzugriffsleiste". Der größte Teil des Fensters wird von einem umfangreichen Einstellungsbereich eingenommen. Auf der linken Seite befinden sich Werkzeuge, die der Symbolleiste für den Schnellzugriff hinzugefügt werden können, und auf der rechten Seite diejenigen, die bereits vorhanden sind.
Auf dem Feld "Befehle auswählen aus" Wert einstellen "Befehle nicht im Menüband". Außerdem finden und wählen wir aus der Liste der Befehle, die in alphabetischer Reihenfolge angeordnet sind, die Position "Die Form…". Klicken Sie dann auf die Schaltfläche "Hinzufügen".

Danach wird das benötigte Tool auf der rechten Seite des Fensters angezeigt. Klicken Sie auf die Schaltfläche OK.

Jetzt befindet sich dieses Tool im Excel-Fenster in der Symbolleiste für den Schnellzugriff und wir können es verwenden. Es ist vorhanden, wenn eine Arbeitsmappe von dieser Instanz von Excel geöffnet wird.

Damit das Tool jetzt versteht, was es genau ausfüllen muss, sollten Sie den Tabellenkopf entwerfen und einen beliebigen Wert hineinschreiben. Lassen Sie das Tabellenarray, das wir haben, aus vier Spalten bestehen, die Namen haben "Produktname", "Menge", "Preis" und "Summe". Wir geben diese Namen in einen beliebigen horizontalen Bereich des Blattes ein.

Damit das Programm genau versteht, mit welchen Bereichen es arbeiten muss, sollten Sie außerdem einen beliebigen Wert in die erste Zeile des Tabellenarrays eingeben.

Wählen Sie danach eine beliebige Zelle der leeren Tabelle aus und klicken Sie auf das Symbol in der Schnellzugriffsleiste "Die Form…", die wir zuvor aktiviert haben.

Also öffnet sich das Fenster des angegebenen Tools. Wie Sie sehen können, hat dieses Objekt Felder, die den Namen der Spalten unseres Tabellenarrays entsprechen. In diesem Fall ist das erste Feld bereits mit einem Wert gefüllt, da wir ihn manuell auf dem Blatt eingegeben haben.

Geben Sie in den restlichen Feldern die Werte ein, die wir für notwendig halten, und klicken Sie dann auf die Schaltfläche "Hinzufügen".

Danach wurden, wie wir sehen können, die eingegebenen Werte automatisch in die erste Zeile der Tabelle übertragen, und im Formular gab es einen Übergang zum nächsten Feldblock, der der zweiten Zeile des Tabellenarrays entspricht.

Wir füllen das Werkzeugfenster mit den Werten, die wir in der zweiten Zeile des Tabellenbereichs sehen möchten, und klicken erneut auf die Schaltfläche "Hinzufügen".

Wie Sie sehen, wurden auch die Werte der zweiten Zeile hinzugefügt, und wir mussten nicht einmal den Cursor in der Tabelle selbst neu anordnen.

Somit füllen wir das Tabellenarray mit allen Werten, die wir darin eintragen möchten.

Darüber hinaus können Sie, falls gewünscht, mit den Schaltflächen durch zuvor eingegebene Werte navigieren "Zurück" und "Des Weiteren" oder vertikale Bildlaufleiste.

Bei Bedarf können Sie jeden Wert im Tabellenarray korrigieren, indem Sie ihn im Formular ändern. Damit die Änderungen auf dem Blatt angezeigt werden, klicken Sie auf die Schaltfläche, nachdem Sie sie im entsprechenden Block des Tools vorgenommen haben "Hinzufügen".

Wie Sie sehen können, trat die Änderung sofort im Tabellenbereich auf.

Wenn wir eine Zeile löschen müssen, gehen wir über die Navigationsschaltflächen oder die Bildlaufleiste zu dem entsprechenden Feldblock im Formular. Klicken Sie danach auf die Schaltfläche "Löschen" im Werkzeugfenster.

Ein Warndialogfeld wird geöffnet, das Sie darüber informiert, dass die Zeile entfernt wird. Wenn Sie von Ihren Handlungen überzeugt sind, klicken Sie auf die Schaltfläche OK.

Wie Sie sehen können, wurde die Zeile aus dem Tabellenbereich extrahiert. Nachdem das Ausfüllen und Bearbeiten abgeschlossen ist, können Sie das Werkzeugfenster verlassen, indem Sie auf die Schaltfläche klicken "Nah dran".

Danach kann eine Formatierung vorgenommen werden, um dem Tabellenarray ein visuelleres Erscheinungsbild zu verleihen.

Methode 2: Erstellen Sie ein Benutzerformular

Darüber hinaus ist es mit Hilfe eines Makros und einer Reihe weiterer Tools möglich, ein eigenes Benutzerformular zum Ausfüllen des Tabellenbereichs zu erstellen. Es wird direkt auf dem Blatt erstellt und repräsentiert dessen Bereich. Mit Hilfe dieses Tools kann der Benutzer selbst diejenigen Funktionen implementieren, die er für notwendig hält. In Bezug auf die Funktionalität wird es dem eingebauten Analogon von Excel praktisch in keiner Weise nachgeben und es vielleicht in gewisser Weise übertreffen. Der einzige Nachteil ist, dass Sie für jedes Tabellenarray ein separates Formular erstellen müssen und nicht dieselbe Vorlage verwenden, wie dies bei der Standardversion möglich ist.

Wie bei der vorherigen Methode müssen Sie zunächst eine Kopfzeile für die zukünftige Tabelle auf dem Blatt erstellen. Es besteht aus fünf Zellen mit Namen: "Nr. p / p", "Produktname", "Menge", "Preis", "Summe".

Als nächstes müssen wir aus unserem Tabellenarray eine sogenannte „intelligente“ Tabelle erstellen, mit der Möglichkeit, Zeilen automatisch hinzuzufügen, wenn benachbarte Bereiche oder Zellen mit Daten gefüllt werden. Wählen Sie dazu die Kopfzeile aus und befinden Sie sich in der Registerkarte "Heim", klicken Sie auf die Schaltfläche "Als Tabelle formatieren" im Werkzeugkasten "Stile". Dies öffnet eine Liste der verfügbaren Stiloptionen. Die Wahl einer davon hat keinerlei Einfluss auf die Funktionalität, daher wählen wir einfach die Option, die wir für geeigneter halten.

Dann öffnet sich ein kleines Tabellenformatierungsfenster. Es gibt den Bereich an, den wir zuvor ausgewählt haben, dh den Bereich des Headers. In der Regel ist in diesem Feld alles richtig ausgefüllt. Aber wir sollten das Kästchen neben dem Parameter aktivieren "Tabelle mit Kopfzeilen". Danach klicken Sie auf die Schaltfläche OK.

Unser Sortiment ist also wie eine "intelligente" Tabelle formatiert, was auch durch die Änderung der visuellen Darstellung belegt wird. Wie Sie unter anderem sehen können, wurden neben jedem Spaltenkopfnamen Filtersymbole angezeigt. Sie sollten deaktiviert werden. Wählen Sie dazu eine beliebige Zelle der "intelligenten" Tabelle aus und gehen Sie zur Registerkarte "Daten". Dort auf der Multifunktionsleiste in der Toolbox "Sortieren und Filtern" klicken Sie auf das Symbol "Filter".

Es gibt eine weitere Option, um den Filter zu deaktivieren. In diesem Fall ist es nicht einmal erforderlich, auf eine andere Registerkarte zu wechseln, sondern auf der Registerkarte zu bleiben "Heim". Nach Auswahl einer Tabellenbereichszelle in der Multifunktionsleiste im Einstellungsblock "Bearbeitung" klicken Sie auf das Symbol "Sortieren und Filtern". Wählen Sie ein Element aus der angezeigten Liste aus. "Filter".

Wie Sie sehen können, sind nach dieser Aktion die Filtersymbole wie gewünscht aus der Kopfzeile der Tabelle verschwunden.

Als nächstes müssen wir das Dateneingabeformular selbst erstellen. Es wird auch eine Art tabellarisches Array sein, das aus zwei Spalten besteht. Die Namen der Zeilen dieses Objekts entsprechen den Namen der Spalten der Haupttabelle. Eine Ausnahme bilden Spalten. "Nr. p / p" und "Summe". Sie werden fehlen. Die Nummerierung des ersten von ihnen erfolgt über ein Makro, und die Berechnung der Werte im zweiten erfolgt durch Anwendung der Formel zur Multiplikation der Menge mit dem Preis.
Lassen wir die zweite Spalte des Dateneingabeobjekts vorerst leer. Darin werden später direkt Werte eingetragen, um die Zeilen des Haupttabellenbereichs zu füllen.

Danach erstellen wir eine weitere kleine Tabelle. Es wird aus einer Spalte bestehen und eine Liste von Produkten enthalten, die wir in der zweiten Spalte der Haupttabelle anzeigen werden. Der Übersichtlichkeit halber ist die Zelle mit dem Titel dieser Liste ( "Produktliste") kann mit Farbe gefüllt werden.

Dann wählen wir die erste leere Zelle des Werteingabeobjekts aus. Gehen Sie zur Registerkarte "Daten". Klicken Sie auf das Symbol "Datenvalidierung", die sich auf der Multifunktionsleiste in der Toolbox befindet „Arbeiten mit Daten“.

Das Eingabevalidierungsfenster wird geöffnet. Klicken Sie auf das Feld "Datentyp", die standardmäßig auf "Jeder Wert".

Wählen Sie eine Position aus den angezeigten Optionen aus. "Aufführen".

Wie Sie sehen können, hat das Fenster zur Überprüfung der Eingabewerte danach seine Konfiguration etwas geändert. Zusätzliches Feld hinzugefügt "Quelle". Wir klicken mit der linken Maustaste auf das Symbol rechts davon.

Dann wird das Eingabevalidierungsfenster minimiert. Wir wählen mit dem Cursor bei gedrückter linker Maustaste die Liste der Daten aus, die auf dem Blatt im zusätzlichen Tabellenbereich platziert werden "Produktliste". Klicken Sie danach erneut auf das Symbol rechts neben dem Feld, in dem die Adresse des ausgewählten Bereichs angezeigt wurde.

Es erfolgt ein Rücksprung in das Fenster zur Überprüfung der eingegebenen Werte. Wie Sie sehen können, werden die Koordinaten des ausgewählten Bereichs bereits im Feld angezeigt "Quelle". Wir klicken auf die Schaltfläche OK am unteren Rand des Fensters.

Jetzt ist rechts neben der ausgewählten leeren Zelle des Dateneingabeobjekts ein Symbol in Form eines Dreiecks erschienen. Wenn Sie darauf klicken, öffnet sich eine Dropdown-Liste, bestehend aus Namen, die aus dem Tabellenarray gezogen werden "Produktliste". Es ist jetzt nicht möglich, beliebige Daten in die angegebene Zelle einzugeben, sondern Sie können nur die gewünschte Position aus der angezeigten Liste auswählen. Wählen Sie ein Element in der Dropdown-Liste aus.

Wie Sie sehen, wird die ausgewählte Position sofort im Feld angezeigt "Produktname".

Als nächstes müssen wir den drei Eingabeformularzellen, in die wir Daten eingeben, Namen zuweisen. Wählen Sie die erste Zelle aus, in der in unserem Fall bereits der Name festgelegt ist "Kartoffel". Gehen Sie als Nächstes zum Bereichsnamensfeld. Sie befindet sich auf der linken Seite des Excel-Fensters auf der gleichen Ebene wie die Bearbeitungsleiste. Geben Sie dort einen beliebigen Namen ein. Es kann jeder lateinische Name sein, in dem es keine Leerzeichen gibt, aber es ist besser, Namen zu verwenden, die den von diesem Element gelösten Aufgaben nahe kommen. Daher rufen wir die erste Zelle auf, die den Namen des Produkts enthält Name. Schreiben Sie diesen Namen in das Feld und drücken Sie die Taste Eintreten auf Tastatur.

Auf die gleiche Weise weisen wir der Zelle, in der wir die Warenmenge eingeben, den Namen zu Volumen.

Und die Zelle mit dem Preis - Preis.

Danach geben wir auf genau die gleiche Weise dem gesamten Bereich der obigen drei Zellen den Namen. Zuerst wählen wir aus und geben ihm dann einen Namen in einem speziellen Feld. Lass das der Name sein Bereich.

Nach der letzten Aktion müssen wir das Dokument speichern, damit die von uns vergebenen Namen von dem später erstellten Makro wahrgenommen werden können. Gehen Sie zum Speichern auf die Registerkarte "Datei" und klicken Sie auf den Artikel "Speichern als…".

Im sich öffnenden Speicherfenster im Feld "Dateityp" wählen Sie einen Wert „Excel-Arbeitsmappe mit Makros (.xlsm)“. Klicken Sie anschließend auf die Schaltfläche "Speichern".

Dann sollten Sie in Ihrer Excel-Version die Registerkarte aktivieren "Entwickler" falls du es noch nicht getan hast. Tatsache ist, dass diese beiden Funktionen im Programm standardmäßig deaktiviert sind und ihre Aktivierung im Excel-Optionsfenster erzwungen werden muss.
Nachdem Sie dies getan haben, gehen Sie auf die Registerkarte "Entwickler". Klicken Sie auf das große Symbol "Visual Basic", die sich auf der Multifunktionsleiste in der Toolbox befindet "Der Code".

Die letzte Aktion bewirkt, dass der VBA-Makro-Editor gestartet wird. Im Gebiet Projekt, das sich im oberen linken Teil des Fensters befindet, wählen Sie den Namen des Blattes aus, auf dem sich unsere Tabellen befinden. In diesem Fall dies "Blatt1".

Gehen Sie danach in den unteren linken Bereich des aufgerufenen Fensters "Eigenschaften". Hier sind die Einstellungen für das ausgewählte Blatt. Auf dem Feld "(Name)" Der kyrillische Name sollte ersetzt werden ( "Blatt1") zum lateinisch geschriebenen Namen. Sie können einen beliebigen Namen angeben, der für Sie bequemer ist. Hauptsache, er enthält nur lateinische Buchstaben oder Zahlen und es gibt keine anderen Buchstaben oder Leerzeichen. Mit diesem Namen funktioniert das Makro. Lassen Sie in unserem Fall diesen Namen sein Produkt, obwohl Sie jeden anderen auswählen können, der die oben beschriebenen Bedingungen erfüllt.
Auf dem Feld Name Sie können den Namen auch in einen bequemeren ändern. Dies ist jedoch optional. Die Verwendung von Leerzeichen, Kyrillisch und anderen Zeichen ist erlaubt. Im Gegensatz zum vorherigen Parameter, der den Namen des Blatts für das Programm angibt, weist dieser Parameter dem Blatt einen Namen zu, der für den Benutzer in der Shortcut-Leiste sichtbar ist.

Wie Sie sehen, ändert sich der Name danach automatisch. Blatt1 im Gebiet Projekt, zu dem, den wir gerade in den Einstellungen festgelegt haben.

Gehen Sie dann in den zentralen Bereich des Fensters. Hier müssen wir den Makrocode selbst schreiben. Wenn das weiße Code-Editor-Feld im angegebenen Bereich nicht angezeigt wird, wie in unserem Fall, klicken Sie auf die Funktionstaste F7 und es wird erscheinen.

Für unser spezifisches Beispiel müssen wir nun den folgenden Code in das Feld schreiben:
Sub DataEntryForm()
Dim nextRow As Long
nextRow = Producty.Cells(Producty.Rows.Count, 2).End(xlUp).Offset(1, 0).Row
Mit Produktion
Wenn .Range("A2").Value = "" Und .Range("B2").Value = "" Then
nächsteReihe = nächsteReihe - 1
Ende wenn
Production.Range("Name").Copy

.Range("A2").Formula = "=IF(ISLEER(B2), """", COUNTA($B$2:B2))"
Wenn nextRow > 2 Then
Bereich ("A2").Auswählen

Ende wenn
.Range("Range").ClearContents
Ende mit
End Sub

Aber dieser Code ist nicht universell, das heißt, er ist nur unverändert für unseren Fall geeignet. Wenn Sie es an Ihre Bedürfnisse anpassen möchten, dann sollte es entsprechend modifiziert werden. Damit Sie es selbst tun können, schauen wir uns an, woraus dieser Code besteht, was darin ersetzt werden sollte und was nicht geändert werden sollte.

Also die erste Zeile:

Sub DataEntryForm()

"Dateneingabeformular" ist der Name des Makros selbst. Sie können es so lassen, wie es ist, oder Sie können es durch ein anderes ersetzen, das den allgemeinen Regeln zum Erstellen von Makronamen entspricht (keine Leerzeichen, nur lateinische Buchstaben verwenden usw.). Das Ändern des Namens hat keine Auswirkungen.

Überall dort, wo das Wort im Code vorkommt Produkt Sie müssen ihn durch den Namen ersetzen, den Sie zuvor Ihrem Blatt im Feld zugewiesen haben "(Name)" Bereiche "Eigenschaften" Makro-Editor. Dies sollte natürlich nur gemacht werden, wenn Sie das Blatt anders benannt haben.

Betrachten Sie nun diese Zeile:

nextRow = Producty.Cells(Producty.Rows.Count, 2).End(xlUp).Offset(1, 0).Row

Nummer "2" in dieser Zeile bedeutet die zweite Spalte des Blattes. In dieser Spalte befindet sich die Spalte "Produktname". Dementsprechend werden wir die Anzahl der Zeilen zählen. Wenn in Ihrem Fall eine ähnliche Spalte eine andere Reihenfolge im Konto hat, müssen Sie daher die entsprechende Nummer eingeben. Bedeutung "Ende(xlUp).Offset(1, 0).Row" in jedem Fall lassen wir es unverändert.

Wenn .Range("A2").Value = "" Und .Range("B2").Value = "" Then

"A2" sind die Koordinaten der ersten Zelle, in der die Zeilennummerierung angezeigt wird. "B2"- dies sind die Koordinaten der ersten Zelle, auf der Daten ausgegeben werden ( "Produktname"). Sollten diese bei Ihnen anders sein, dann geben Sie statt dieser Koordinaten Ihre Daten ein.

Gehen Sie zur Linie

Production.Range("Name").Copy

Es enthält den Parameter Name meinen den Namen, den wir dem Feld gegeben haben "Produktname" im Eingabeformular.

In Linien

Cells(nextRow, 2).PasteSpecial Paste:=xlPasteValues
.Cells(nextRow, 3).Value = Producty.Range("Volume").Value
.Cells(nextRow, 4).Value = Production.Range("Price").Value
.Cells(nextRow, 5).Value = Producty.Range("Volume").Value * Producty.Range("Price").Value

Konfessionen Volumen und Preis meinen die Namen, die wir den Feldern zugeordnet haben "Menge" und "Preis" in derselben Eingabemaske.

In den gleichen Zeilen, die wir oben angegeben haben, die Zahlen "2", "3", "vier", "5" bedeuten die Nummern der Spalten auf dem Excel-Blatt, die den Spalten entsprechen "Produktname", "Menge", "Preis" und "Summe". Wenn also in Ihrem Fall die Tabelle verschoben wird, müssen Sie die entsprechenden Spaltennummern angeben. Wenn es mehr Spalten gibt, müssen Sie analog ihre Zeilen zum Code hinzufügen, wenn es weniger gibt, dann entfernen Sie die zusätzlichen.

Die Linie multipliziert die Menge des Produkts mit seinem Preis:

Cells(nextRow, 5).Value = Producty.Range("Volume").Value * Producty.Range("Price").Value

Das Ergebnis wird, wie wir aus der Datensatzsyntax sehen, in der fünften Spalte der Excel-Tabelle angezeigt.

Dieser Ausdruck führt eine automatische Zeilennummerierung durch:

Wenn nextRow > 2 Then
Bereich ("A2").Auswählen
Selection.AutoFill Destination:=Range("A2:A" & nextRow)
Range("A2:A" & nextRow).Select
Ende wenn

Alle Werte "A2" bedeuten die Adresse der ersten Zelle, in der die Nummerierung durchgeführt wird, und die Koordinaten " EIN"- die Adresse der gesamten Spalte mit Nummerierung. Prüfen Sie, wo genau die Nummerierung in Ihrer Tabelle angezeigt wird und ändern Sie diese Koordinaten ggf. im Code.

Die Zeile löscht den Bereich des Dateneingabeformulars, nachdem die Informationen daraus in die Tabelle übertragen wurden:

Range("Diapason").ClearContents

Das ist nicht schwer zu erraten Bereich) bedeutet den Namen des Bereichs, den wir zuvor den Dateneingabefeldern zugewiesen haben. Wenn Sie ihnen einen anderen Namen gegeben haben, dann sollte dieser in diese Zeile eingefügt werden.

Der Rest des Codes ist universell und wird in allen Fällen unverändert gemacht.

Nachdem Sie den Makrocode in das Editorfenster geschrieben haben, sollten Sie auf das Diskettensymbol auf der linken Seite des Fensters klicken. Dann können Sie es schließen, indem Sie auf die Schaltfläche zum Schließen des Standardfensters in der oberen rechten Ecke klicken.

Danach kehren wir zum Excel-Sheet zurück. Jetzt müssen wir eine Schaltfläche platzieren, die das erstellte Makro aktiviert. Gehen Sie dazu auf die Registerkarte "Entwickler". Im Einstellungsblock "Kontrollen" Klicken Sie auf die Schaltfläche in der Multifunktionsleiste "Einfügung". Eine Liste mit Werkzeugen wird geöffnet. In der Werkzeuggruppe "Formularsteuerelemente" wählen Sie die allererste "Taste".

Dann kreisen wir mit gedrückter linker Maustaste den Bereich ein, in dem wir die Schaltfläche zum Starten des Makros platzieren möchten, das Daten aus dem Formular in die Tabelle überträgt.

Nachdem der Bereich eingekreist ist, lassen Sie die Maustaste los. Anschließend wird automatisch das Fenster zum Zuordnen eines Makros zu einem Objekt gestartet. Wenn Ihr Buch mehrere Makros verwendet, wählen Sie den Namen des Makros, das wir oben erstellt haben, aus der Liste aus. Wir nennen es "Dateneingabeformular". Aber in diesem Fall gibt es nur ein Makro, also wählen Sie es einfach aus und klicken Sie auf die Schaltfläche OK am unteren Rand des Fensters.
Sie können die Schaltfläche dann beliebig umbenennen, indem Sie einfach ihren aktuellen Namen markieren.

In unserem Fall wäre es zum Beispiel logisch, ihr einen Namen zu geben "Hinzufügen". Benennen Sie eine beliebige freie Zelle des Blatts um und klicken Sie darauf.

Unser Formular ist also vollständig fertig. Lassen Sie uns überprüfen, wie es funktioniert. Geben Sie die erforderlichen Werte in die Felder ein und klicken Sie auf die Schaltfläche "Hinzufügen".

Wie Sie sehen, werden die Werte in die Tabelle verschoben, die Zeile wird automatisch mit einer Nummer versehen, der Betrag wird berechnet, die Formularfelder werden geleert.

Füllen Sie das Formular erneut aus und klicken Sie auf die Schaltfläche "Hinzufügen".

Wie Sie sehen können, wird die zweite Zeile auch zum Tabellenarray hinzugefügt. Dies bedeutet, dass das Tool funktioniert.

Anmerkung: Die Vorlesung widmet sich der Beschreibung des Objektmodells von MS Excel und detaillierte Beschreibung Methoden, Eigenschaften und Ereignisse der Anwendung.

13.1. Merkmale der Programmierung für MS Excel

Microsoft Office Excel sind beliebte Tabellenkalkulationen. Üblicherweise verfolgen sie bei der Programmierung für dieses Programm folgende Ziele:

Automatisierung von Berechnungen.
Automatisierung der Eingabe und Verarbeitung von Informationen.
Arbeiten mit Datenbanken - Ausgabe, Eingabe, Analyse, Visualisierung von Informationen.
Analyse von Finanz- und anderen Informationen.
Erstellung von Systemen zur Organisation der automatisierten Dateneingabe
Mathematische Modellierung.

Im Algemeinen Programmierung in Excel ähnelt der Arbeit mit Microsoft Word. Einer der Hauptunterschiede besteht jedoch darin, dass in Excel der Arbeitsbereich des Arbeitsblatts in Zellen unterteilt ist, von denen jede einen eigenen Namen hat. Es gibt zwei Arten von Zellnamen.

Die erste Art (A1-Stil) ist der Name, der aus einem wörtlichen Spaltennamen und einer Zeilennummer besteht. Beispielsweise ist A1 die Zelle, die sich am Schnittpunkt von Spalte A (der ersten) und der ersten Zeile befindet.
Die andere Art sind Zellindizes (Namensstil R1C1 ). Um eine Zelle in diesem Stil anzusprechen, geben Sie die Zeilennummer (R - Zeile - Zeile) und die Spaltennummer (C - Spalte - Spalte) an, an deren Schnittpunkt sich die Zelle befindet. Die Zeilen sind zunächst nummeriert und die Spaltennummern beginnen bei 1 – die erste Spalte entspricht Spalte A, die zweite – B und so weiter. Zum Beispiel ist (2, 3) die Adresse der Zelle, die sich am Schnittpunkt der zweiten Zeile und der dritten Spalte befindet, das heißt, wenn wir dies in den Stil A1 verschieben, erhalten wir Zelle C2 (Abb. 13.1.)

Reis. 13.1.

Die folgenden Objekte werden verwendet, um die meisten Operationen in MS Excel auszuführen.

Excel.Application (Application) – Ein Objekt, das die Microsoft Excel-Anwendung darstellt, ähnlich wie Word.Application .
Arbeitsmappe(Arbeitsmappe) - stellt eine Arbeitsmappe dar - analog zu einem Microsoft Word-Dokument. In Word arbeiten wir jedoch mit Daten, die sich in einem Dokument befinden, und in Excel gibt es ein weiteres Objekt auf dem Weg zu den Daten - ein Arbeitsblatt.
Arbeitsblatt(Arbeitsblatt) - Eine Arbeitsmappe in MS Excel ist in Arbeitsblätter unterteilt. Auf dem Blatt befinden sich Zellen, die Informationen und Formeln speichern können.
Bereich (Range) - kann als einzelne Zelle oder als Gruppe von Zellen dargestellt werden. Dieses Objekt ersetzt viele Objekte für die Arbeit mit Dokumentelementen (Zeichen , Wort usw.), die in Microsoft Word verwendet werden. Dadurch wird die Arbeit mit dem Blatt sehr übersichtlich und bequem - um mit einer Zelle zu arbeiten, müssen Sie nur ihren Namen (im A1-Format) oder ihre Adresse (R1C1) kennen.
QueryTable (Abfragetabelle) - dieses Objekt wird verwendet, um Informationen aus Datenbanken in Microsoft Excel zu importieren. Datenbankverbindung, Informationsanfrage usw. werden über das Objekt erzeugt und die Abfrageergebnisse in Form einer regulären Tabelle in das MS-Excel-Sheet hochgeladen.
PivotTable (PivotTable) ist eine spezielle Art von Excel-Tabellenkalkulation - sie ermöglicht es Ihnen, große Mengen an Informationen, insbesondere aus einer Datenbank, interaktiv zusammenzufassen und zu analysieren.
Diagramm(Diagramm) - stellt ein Diagramm dar. Sie werden normalerweise zur Datenvisualisierung verwendet.

Beginnen wir mit der Überprüfung MS Excel-Objektmodell aus dem Anwendungsobjekt.

13.2. Anwendungsobjekt

MsgBox Excel.Application.Name Auflistung 13.1. Anwendungsnamen anzeigen

Wenn das Programm ausgeführt wird, zeigt ein Meldungsfeld den Namen der Anwendung an – in diesem Fall Microsoft Excel. Offensichtlich gibt die Name-Eigenschaft des Application-Objekts den Namen der Anwendung zurück.

Sehen wir uns nun die wichtigsten Methoden und Eigenschaften von Application an. Einige von ihnen ähneln denen in MS Word. Beispielsweise schließt die Quit-Methode, genau wie in Word, die Anwendung, die Visible-Eigenschaft ist für die Sichtbarkeit des Programmfensters verantwortlich usw.

13.3. Methoden

13.3.1. Berechnen - erzwungene Neuberechnung

Diese Methode wird vom Application-Objekt aufgerufen und ermöglicht Ihnen, alle Buchöffnungen neu zu berechnen. Es kann auch für einzelne Bücher aufgerufen werden (Objekt Arbeitsmappe) Blätter ( Arbeitsblatt), Zellen und ihre Bereiche (Range ). Beispielsweise der Code in Listing 13.2. ermöglicht Ihnen, alle geöffneten Arbeitsmappen neu zu berechnen.

Anwendung.Berechnen Auflistung 13.2. Alle offenen Bücher neu berechnen

13.3.2. GoTo - zu einer Zelle bewegen

13-02-Excel GoTo.xlsm - ein Beispiel für Absatz 13.3.2.

Ermöglicht es Ihnen, einen beliebigen Bereich von Zellen in einem beliebigen Buch auszuwählen, und wenn das Buch nicht aktiv ist, wird es aktiviert. Die Methode kann auch Microsoft Excel-Makros ausführen.

Der vollständige Methodenaufruf sieht folgendermaßen aus:

Gehe zu (Referenz, Blättern)

Der Reference-Parameter ist ein Verweis auf eine Zelle oder einen Zellbereich, der nach dem Methodenaufruf ausgewählt werden soll. Hier kann auch der Name des Makros verwendet werden - dann wird es ausgeführt.

Der Scroll-Parameter ist dafür verantwortlich, das Excel-Blatt zu den ausgewählten Zellen "zurückzuspulen", sodass die obere linke Ecke der Auswahl mit der oberen linken Ecke des angezeigten Blattbereichs zusammenfällt. Wenn Scroll auf True gesetzt ist, wird das Blatt zurückgespult, wenn es auf False gesetzt ist, nicht.

Mit einem solchen Aufruf (Listing 13.3.) können Sie beispielsweise die Zelle H500 auf dem aktiven Blatt auswählen.

Application.Goto _Reference:=ActiveSheet.Range("H500"), _Scroll:=True Auflistung 13.3. Markieren Sie die Zelle H500

Wie Sie sehen können, ist der Zugriff auf das aktive Blatt dem Zugriff auf das aktive Dokument in MS Word sehr ähnlich. Beachten Sie, dass wir einen vollständigen Methodenaufruf verwenden – Application.GoTo – wie Sie wissen, können Sie normalerweise die Eigenschaften und Methoden des Application-Objekts in Ihrem Code verwenden, ohne dieses Objekt anzugeben. Wenn Sie Application in diesem Fall jedoch nicht angeben, versucht das Programm anstelle der GoTo-Methode, es auszuführen Unbedingter Sprungoperator Gehe zu .

13.3.3. SendKeys - simuliert Tastenanschläge auf der Tastatur

13-03-Excel SendKeys.xlsm - ein Beispiel für Absatz 13.3.3.

Eine sehr interessante Methode - ermöglicht es Ihnen, Tastenanschläge an das aktive Anwendungsfenster zu senden. Der vollständige Methodenaufruf sieht folgendermaßen aus:

SendKeys(Tasten, warten)

Mit dem Keys-Parameter können Sie die Schlüssel angeben, die an die Anwendung übergeben werden. Die Methode unterstützt die Emulation von sowohl alphanumerischen als auch Steuertasten, für die eine spezielle Codierung angewendet wird. Alphanumerische Schlüssel werden beim Aufruf in ihrer üblichen Form angegeben, um beispielsweise das Zeichen „Ф“ zu übergeben, müssen Sie es beim Aufruf einer Methode angeben usw.

Um einen Tastendruck an eine Anwendung zu senden Rücktaste- Code (BS) verwenden. Um eine Schaltfläche zu senden, drücken Sie Eintreten benutze das ~ (

Viele Excel-Benutzer sehen den Unterschied zwischen "Zellenformat" und "Datentyp" nicht. Tatsächlich sind dies alles andere als identische Konzepte, obwohl sie natürlich verwandt sind. Lassen Sie uns herausfinden, was die Essenz von Datentypen ist, in welche Kategorien sie unterteilt sind und wie Sie mit ihnen arbeiten können.

Der Datentyp ist ein Merkmal der im Arbeitsblatt gespeicherten Informationen. Anhand dieser Eigenschaft bestimmt das Programm, wie ein bestimmter Wert verarbeitet werden soll.

Datentypen werden in zwei große Gruppen unterteilt: Konstanten und Formeln. Der Unterschied zwischen ihnen besteht darin, dass Formeln einen Wert an eine Zelle ausgeben, der sich ändern kann, je nachdem, wie sich die Argumente in anderen Zellen ändern. Konstanten sind konstante Werte, die sich nicht ändern.

Die Konstanten wiederum werden in fünf Gruppen eingeteilt:

Text;
Numerische Daten;
Datum (und Uhrzeit;
Boolesche Daten;
Fehlerhafte Werte.

Lassen Sie uns genauer herausfinden, was jeder dieser Datentypen darstellt.

Textwerte

Der Texttyp enthält Zeichendaten und wird von Excel nicht als Gegenstand mathematischer Berechnungen betrachtet. Diese Informationen sind in erster Linie für den Benutzer, nicht für das Programm. Text kann ein beliebiges Zeichen sein, einschließlich Zahlen, solange sie entsprechend formatiert sind. In DAX bezieht sich diese Art von Daten auf Zeichenfolgenwerte. Die maximale Textlänge beträgt 268435456 Zeichen in einer Zelle.

Um einen symbolischen Ausdruck einzugeben, müssen Sie die Zelle des Textes oder das allgemeine Format auswählen, in dem er gespeichert werden soll, und den Text über die Tastatur eingeben. Wenn die Länge des Textausdrucks die sichtbaren Grenzen der Zelle überschreitet, wird er den benachbarten überlagert, obwohl er physisch weiterhin in der ursprünglichen Zelle gespeichert ist.

Numerische Daten

Numerische Daten werden für direkte Berechnungen verwendet. Mit ihnen führt Excel verschiedene mathematische Operationen durch (Addition, Subtraktion, Multiplikation, Division, Potenzierung, Wurzelziehen usw.). Dieser Datentyp ist ausschließlich zum Schreiben von Zahlen gedacht, kann aber auch Hilfszeichen (%, $, etc.) enthalten. Es kann in mehreren Formaten verwendet werden:

Eigentlich numerisch;
Prozentsatz;
Geld;
Finanzen;
Bruchteil;
Exponentiell.

Darüber hinaus hat Excel die Möglichkeit, Zahlen in Ziffern aufzuteilen und die Anzahl der Nachkommastellen (in Bruchzahlen) zu bestimmen.

Numerische Daten werden auf die gleiche Weise eingegeben wie Textwerte, über die wir oben gesprochen haben.

Datum (und Uhrzeit

Ein weiterer Datentyp ist das Zeit- und Datumsformat. Genau das ist dann der Fall, wenn die Datentypen und Formate übereinstimmen. Es zeichnet sich dadurch aus, dass mit ihm Daten und Uhrzeiten auf dem Blatt angezeigt und Berechnungen durchgeführt werden können. Es ist bemerkenswert, dass dieser Datentyp in Berechnungen einen Tag als Einheit verwendet. Und das gilt nicht nur für Daten, sondern auch für die Zeit. Beispielsweise wird 12:30 vom Programm als 0,52083 Tage betrachtet und nur dann in einer Zelle in der dem Benutzer vertrauten Form angezeigt.

Es gibt mehrere Arten der Zeitformatierung:

h:mm:ss;
hmm;
h:mm:ss AM/PM;
h:mm AM/PM usw.

Ähnlich verhält es sich mit Datteln:

DD / MM / JJJJ;
TT.MMM
MMM.YY und andere.

Es gibt auch kombinierte Datums- und Zeitformate, wie z. B. TT:MM:JJJJ h:mm.

Außerdem müssen Sie berücksichtigen, dass das Programm als Datum nur Werte ab dem 01.01.1900 anzeigt.

Boolesche Daten

Sehr interessant ist der boolesche Datentyp. Es arbeitet mit nur zwei Werten: "STIMMT" und "FALSCH". Wenn wir übertreiben, dann bedeutet das „das Ereignis ist gekommen“ und „das Ereignis ist nicht gekommen“. Funktionen, die den Inhalt von Zellen verarbeiten, die logische Daten enthalten, führen bestimmte Berechnungen durch.

Fehlerhafte Werte

Fehlerwerte sind ein eigener Datentyp. In den meisten Fällen treten sie auf, wenn eine falsche Operation ausgeführt wird. Zu solchen Fehloperationen gehören beispielsweise die Division durch Null oder die Einführung einer Funktion ohne Beachtung ihrer Syntax. Zu den fehlerhaften Werten gehören die folgenden:

#WERT! – Verwendung der falschen Art von Argument für die Funktion;
#EIN GESCHÄFT! – Division durch 0;
#NUMMER! – falsche Zahlenangaben;
#NV – nicht verfügbarer Wert eingegeben;
#NAME? – falscher Name in der Formel;
#LEER! – falsche Eingabe von Bereichsadressen;
#VERKNÜPFUNG! - tritt auf, wenn Zellen gelöscht werden, auf die zuvor von der Formel verwiesen wurde.

Formeln

Formeln sind eine separate große Gruppe von Datentypen. Im Gegensatz zu Konstanten sind sie meistens nicht in den Zellen selbst sichtbar, sondern zeigen nur das Ergebnis an, das sich je nach Änderung der Argumente ändern kann. Insbesondere werden Formeln für verschiedene mathematische Berechnungen verwendet. Die Formel selbst ist in der Formelleiste sichtbar, indem die Zelle markiert wird, in der sie enthalten ist.

Voraussetzung dafür, dass das Programm den Ausdruck als Formel wahrnimmt, ist ein vorangestelltes Gleichheitszeichen (=) .

Funktionen sind eine eigene Art von Formeln. Dies sind besondere Subroutinen, die eine Reihe von Argumenten enthalten und diese gemäß einem bestimmten Algorithmus verarbeiten. Funktionen können manuell in eine Zelle eingegeben werden, indem zuerst ein Zeichen gesetzt wird «=» , aber Sie können für diesen Zweck eine spezielle grafische Shell verwenden Funktionsassistent, die die gesamte Liste der im Programm verfügbaren Operatoren enthält, unterteilt in Kategorien.

Mit Hilfe Funktionsassistenten Sie können zum Argumentfenster eines bestimmten Operators springen. In seine Felder werden Daten eingetragen oder auf Zellen verlinkt, in denen diese Daten enthalten sind. Nach dem Drücken der Taste OK Die angegebene Operation wird ausgeführt.

Wie wir sehen, in Excel-Programm Es gibt zwei Hauptgruppen von Datentypen: Konstanten und Formeln. Sie werden wiederum in viele andere Typen unterteilt. Jeder Datentyp hat seine eigenen Eigenschaften, nach denen das Programm sie verarbeitet. Die Beherrschung der Fähigkeit, verschiedene Arten von Daten zu erkennen und richtig damit zu arbeiten, ist die Hauptaufgabe jedes Benutzers, der lernen möchte, wie man Excel effektiv für den beabsichtigten Zweck verwendet.

Frizen Irina Grigorievna – Kandidatin der Pädagogischen Wissenschaften, Dozentin Informationstechnologien, Autor zahlreicher Arbeiten auf dem Gebiet der Informationstechnologie.

Dieses Lehrbuch wurde gemäß dem staatlichen Programm zum Studium der Disziplin für weiterführende Fachbildungseinrichtungen im Fachgebiet 230103 „Automatisierte Systeme für die Informationsverarbeitung und -verwaltung“ verfasst.

Office-Programmierung

Das Handbuch bietet ausreichend Material für die Durchführung praktischer Kurse in der Disziplin sowie das grundlegende theoretische Material, das für das Studium eines bestimmten Themas erforderlich ist. Auf der Grundlage des vorgeschlagenen Materials ist es möglich, das Verhalten verschiedener Arten von Klassen aufzubauen.

Das Lehrbuch soll den Schülern helfen, diese Disziplin vollständig zu studieren und die Anforderungen des staatlichen Standards für die Disziplin zu erfüllen.

Es richtet sich an Lehrkräfte und Studierende an spezialisierten Bildungseinrichtungen der Sekundarstufe und kann auch von Studierenden an Hochschulen, die dieses Fach studieren, genutzt werden.

Das Tutorial umfasst mehr als 60 Probleme, begleitet von 130 Zeichnungen und detaillierten Erklärungen.

Buch:

Abschnitte auf dieser Seite:

Verwenden der Bereichs- und Auswahlobjekte

Das wichtigste in Excel ist das Anwendungsobjekt. Das Anwendungsobjekt ist das oberste Objekt in der Excel-Objekthierarchie und repräsentiert die Excel-Anwendung selbst. Es hat über 120 Eigenschaften und 40 Methoden. Diese Eigenschaften und Methoden dienen zum Festlegen allgemeiner Einstellungen für die Excel-Anwendung. In der Excel-Hierarchie kommt das Workbook-Objekt unmittelbar nach dem Application-Objekt und repräsentiert die Arbeitsmappendatei. Die Arbeitsmappe wird entweder in Dateien im Format XLS (Standardarbeitsmappe) oder XLA (vollständig kompilierte Anwendung) gespeichert. Arbeitsmappeneigenschaften und -methoden ermöglichen es Ihnen, mit Dateien zu arbeiten. Das in der Praxis am meisten "verwendete" Objekt ist jedoch das Range-Objekt, das die Möglichkeiten der Verwendung von VBA in Excel am besten widerspiegelt (siehe Tabelle 19 für die Eigenschaften des Range-Objekts und Tabelle 20 für Methoden).

In einer Excel-Objekthierarchie Bereich(range) kommt unmittelbar nach dem Objekt Arbeitsblatt. Ein Objekt Bereich ist eines der Schlüsselobjekte von VBA. Das Auswahlobjekt (Auswahl) tritt in VBA auf zwei Arten auf – entweder als Ergebnis der Select-Methode oder wenn die Auswahleigenschaft aufgerufen wird. Der Typ des resultierenden Objekts hängt vom Typ des ausgewählten Objekts ab. Meistens gehört das Selection-Objekt zur Range-Klasse, und wenn Sie damit arbeiten, können Sie die Eigenschaften und Methoden des Range-Objekts verwenden. Ein interessantes Merkmal der Range- und Selection-Objekte ist, dass sie keiner Objektfamilie angehören.

Wenn Sie mit dem Range-Objekt arbeiten, müssen Sie daran denken, wie Excel auf eine Arbeitsblattzelle verweist.

Angeben von Zeilen- und Spaltengruppen mit dem Range-Objekt

Wenn im Bereich nur Spalten- oder Zeilennamen angegeben sind, gibt das Range-Objekt den Bereich der angegebenen Spalten oder Zeilen an. Beispielsweise gibt Range("a:c") einen Bereich an, der aus den Spalten a, b und c besteht, während Range("2:2") die zweite Zeile angibt. Eine andere Möglichkeit, mit Zeilen und Spalten zu arbeiten, sind die Methoden Rows (Zeilen) und Columns (Spalten), die Auflistungen von Zeilen und Spalten zurückgeben. Beispielsweise ist Spalte a Spalten(1) und Zeile 2 Zeilen(2).

Beziehung zwischen dem Range-Objekt und der Cells-Eigenschaft

Da eine Zelle ein Sonderfall eines Bereichs ist, der nur aus einer einzigen Zelle besteht, können Sie auch mit dem Range-Objekt damit arbeiten. Das Cells-Objekt ist eine alternative Möglichkeit, mit einer Zelle zu arbeiten. Beispielsweise wird Zelle A2 als Objekt durch Range("A2") oder Cells(l, 2) beschrieben. Das in Range verschachtelte Cells-Objekt wiederum ermöglicht es Ihnen, den Bereich auch in einer alternativen Form zu schreiben, die manchmal für die Arbeit praktisch ist, nämlich Range("A2: C3") und Range(Cells(1,2), Cells(3,3 )) definieren denselben Bereich.

Tabelle 19

Range-Objekteigenschaften

Bereichsobjektmethoden

Bereichsobjektmethoden, die Excel-Befehle verwenden

Die in Excel integrierten Befehle und Methoden ermöglichen es Ihnen, effektiv mit einem Bereich zu arbeiten: Füllen Sie ihn mit Elementen in einem Muster, sortieren, filtern und konsolidieren Sie Daten, erstellen Sie eine Übersichtstabelle und erstellen Sie Szenarien und lösen Sie eine nichtlineare Gleichung mit einer Variablen.

AutoFill-Methode

Die AutoFill-Methode füllt die Zellen in einem Bereich automatisch mit Sequenzelementen. Die AutoFill-Methode unterscheidet sich von der DataSeries-Methode dadurch, dass sie explizit den Bereich angibt, in dem sich die Progression befinden wird. Manuell entspricht diese Methode dem Platzieren des Mauszeigers auf dem Ausfüllpunkt des ausgewählten Bereichs (in den die Werte eingegeben werden, die die generierte Sequenz generieren) und dem Ziehen des Ausfüllpunkts entlang des Bereichs, in dem die zu erstellende Sequenz liegen soll liegen.

Syntax:

ein Objekt. AutoFill (Bereich, Typ)

Argumente:

Bereich Bereich, ab dem das Füllen beginnt Typ Gültige Werte: xlFillDefault, xlFillSeries, xlFillCopy, xlFillFormats, xlFillValues, xlFillDays, xlFillWeekdays, xlFillMonths, xlFillYears, xlLinearTrend, xlGrowthTrend. xlFillDefault

AutoFilter-Methode

Die AutoFilter-Methode ist eine einfache Möglichkeit, Daten in einem Arbeitsblatt abzufragen und zu filtern. Wenn AutoFilter aktiviert ist, verwandelt sich jede Feldüberschrift des ausgewählten Datenbereichs in ein Dropdown-Listenfeld. Wenn Sie eine Anforderung zum Anzeigen von Daten in einem Feld mit einer Dropdown-Liste auswählen, werden nur die Datensätze angezeigt, die die angegebenen Bedingungen erfüllen. Das Dropdown-Listenfeld enthält die folgenden Arten von Bedingungen: Alle (Alle), Top Ten (Top 10), Bedingung (Benutzerdefiniert), Spezifisches Datenelement, Leerzeichen (Leerzeichen) und Nicht-Leerzeichen (Nicht-Leerzeichen). Manuell wird die Methode durch Auswahl des Befehls Data, Filter, Autofilter (Data, Filter, AutoFilter) gestartet.

Bei Verwendung der AutoFilter-Methode sind zwei Syntaxen gültig.

Syntax 1:

Ein Objekt. Automatischer Filter

In diesem Fall wählt die AutoFilter-Methode den Befehl Data, Filter, AutoFilter aus oder bricht ihn ab, der auf den im Objektargument angegebenen Bereich angewendet wird.

Syntax 2:

Ein Objekt. AutoFilter(Feld, Kriterien1, Operator, Kriterien2)

In diesem Fall führt die AutoFilter-Methode den Befehl Data, Filter, AutoFilter gemäß den im Argument angegebenen Kriterien aus.

Argumente:

aufstellen Eine Ganzzahl, die das Feld angibt, nach dem die Daten gefiltert werden sollen

Kriterien1 Geben Sie zwei mögliche Filterbedingungen und Kriterien2-Felder an. Sie können eine Zeichenfolgenkonstante wie 101 und die Beziehungszeichen >,<,>=, <=, =, <>

Operator Gültige Werte: X1And (logische Vereinigung des ersten und zweiten Kriteriums); X1or (logische Addition des ersten und zweiten Kriteriums)

Beim Arbeiten mit Filtern sind die Methode showAllData und die Eigenschaften FilterMode und AutoFilterMode hilfreich.

ShowAllData-Methode Zeigt alle gefilterten und ungefilterten Arbeitsblattzeilen an

FilterMode-Eigenschaft Gültige Werte: True (wenn das Arbeitsblatt Daten mit ausgeblendeten Zeilen gefiltert hat), False (andernfalls)

AutoFilterMode-Eigenschaft Gültige Werte: True (wenn das Arbeitsblatt Dropdown-Menüs für AutoFilter-Methoden anzeigt), False (andernfalls)

GoalSeek-Methode

Das GoalSeek-Verfahren (Auswahl eines Parameters) wählt den Wert eines Parameters (ein unbekannter Wert) aus, der eine Lösung einer Gleichung mit einer Variablen ist. Es wird angenommen, dass die Gleichung auf die Form reduziert wird: Die rechte Seite ist konstant, unabhängig von dem Parameter, der nur in der linken Seite der Gleichung enthalten ist. Manuell wird das GoalSeek-Verfahren mit dem Befehl Service, Parameter selection (Tools, Goal Seek) durchgeführt. Das GoalSeek-Verfahren berechnet die Wurzel nach der Methode der sukzessiven Approximation, deren Ergebnis im Allgemeinen von der anfänglichen Approximation abhängt. Daher muss man für die Richtigkeit der Wurzelfindung auf die korrekte Angabe dieser ersten Näherung achten.

Syntax:

Ein Objekt. GoalSeek (Ziel, Zelle ändern)

Argumente:

Ein Objekt Eine Zelle, in die eine Formel eingegeben wird, die die rechte Seite der zu lösenden Gleichung darstellt. In dieser Formel spielt die Rolle des Parameters (unbekannter Wert) eine Referenz auf die Zelle, die im Argument ChangingCell angegeben ist

Tor Der Wert der linken Seite der zu lösenden Gleichung, die keinen Parameter enthält

Die Genauigkeit, mit der der Stamm gefunden wird, und die maximal zulässige Anzahl von Iterationen, die verwendet werden, um den Stamm zu finden, werden durch die Eigenschaften Maxchange und Maxiterations des Application-Objekts festgelegt. Beispielsweise wird die Bestimmung der Wurzel mit einer Genauigkeit von 0,0001 für maximal 1000 Iterationen durch die Anweisung festgelegt:

Mit Bewerbung

maximale Iterationen = 1000

MaxÄnderung = 0,0001

Diese Werte werden manuell auf der Registerkarte Berechnung des Dialogfelds Optionen festgelegt, das durch den Befehl Extras, Optionen aufgerufen wird.

Sortiermethode

Durch Sortieren können Sie Daten in lexikografischer Reihenfolge in aufsteigender oder absteigender Reihenfolge anordnen. Die Sortiermethode sortiert Listen- und Datenbankzeilen sowie Arbeitsblattspalten nach bis zu drei Sortierkriterien. Das manuelle Sortieren von Daten erfolgt mit dem Befehl Daten, Sortieren.

Syntax:

Ein Objekt. Sort(key1, order1, key2, order2, key3, order3, header, orderCustom, matchCase, orientaticn)

Argumente:

Ein Objekt Der zu sortierende Bereich

Bestellung1

bestellen2 Gibt die Bestellreihenfolge an. Gültige Werte: xlAscending (aufsteigende Reihenfolge); xlDescending (absteigende Reihenfolge)

Header Gültige Werte sind: xlYes (die erste Zeile des Bereichs enthält eine Kopfzeile, die nicht sortiert ist); xlNo (die erste Zeile des Bereichs enthält keine Kopfzeile, dieser Wert wird standardmäßig berücksichtigt); xlGuess (Excel entscheidet, ob es einen Titel gibt)

BestellungBenutzerdefiniert Benutzerdefinierte Sortierreihenfolge. Standard ist Normal

Streichholzschachtel Gültige Werte: True (Groß-/Kleinschreibung beachten) und False (Groß-/Kleinschreibung nicht beachten)

Orientierung Gültige Werte: xlTopToBottom (die Sortierung erfolgt von oben nach unten, d. h. nach Zeilen); xlLeftToRight (von links nach rechts, d. h. nach Spalten)

Beispielsweise wird der Bereich A1:C20 des Arbeitsblatts sheet1 durch den folgenden Befehl in aufsteigender Reihenfolge sortiert, sodass die erste Sortierung in der ersten Spalte dieses Bereichs und die sekundäre Sortierung in der zweiten erfolgt:

Arbeitsblätter("Blatt").Range("A1: C20").Sort _

key1:=Worksheets("Sheet1").Range("A1"), _

key2:=Worksheets("Sheet1").Range("B1")

Zahlen runden

Sie müssen häufig Dezimalzahlen runden, insbesondere wenn Sie mit Geldwerten arbeiten. VBA bietet keine direkte Lösung für solche Probleme, aber die unten beschriebenen Techniken helfen Ihnen, diese Probleme zu lösen.

1 Weg

Runde Funktion

X=rund(2.505, 2)

Der x-Wert beträgt 2,5, nicht 2,51.

Daher wird es nicht oft verwendet.

2-Wege

Funktion formatieren

sngRounding=Format(SngUnrounded, „#, 0.00“)

3 Wege

FormatNumber-Funktion

SngRounding= FormatNumber(sbgUnrounded, 2)

Um die Dezimalstellen zu ändern, ändern Sie die Anzahl der Nullen nach dem Dezimalkomma im Argument Format oder ändern Sie die Zahl, die den Wert des zweiten Arguments angibt, beliebig.

Notiz. Die Variable, in die der gerundete Wert eingefügt wird, muss vom Typ String, Single, Double, Decimal, Currency oder Variant sein, nicht Integer oder Long.

Datenreduzierung

Um die eingegebenen Daten in den gewünschten Typ umzuwandeln, enthält VBA eine umfangreiche Reihe von Funktionen, von denen eine CDBL ist. Syntax:

CDbl(Ausdruck)

Erforderliches Argument Ausdruck ist eine beliebige Zeichenfolge oder ein numerischer Ausdruck. Um die in das Textfeld eingegebenen Informationen im generierten Formular zu lesen, geben Sie eine Variable ein und schreiben Sie einen Ausdruck:

A = Cdbl(textBoxN.text)

Danach können Sie mit dieser Variable arbeiten.

Um Werte direkt in den Zellen einer Excel-Arbeitsmappe anzuzeigen, ist es praktisch, das Range-Objekt zu verwenden:

range("A5").value = a

Das Gegenteil von CDbl ist die CStr-Funktion - sie wandelt Zahlen in Strings um und ist praktisch, um das Ergebnis entweder in einer Zelle auf einem Blatt oder in einem bestimmten Textfeld anzuzeigen.

TextBoxN.text = CStr(.Range("A8").value)

- Lesen des Wertes aus der Zelle und Anzeigen im Textfeld.

Die Funktion Trim(string) gibt eine Kopie der Zeichenfolge zurück, wobei die Leerzeichen am Anfang und am Ende der Zeichenfolge entfernt wurden.

Erstellen von VBA-Programmen

Verwenden der GoalSeek-Methode

Beispiel 41. Entwickeln Sie ein Programm, das unter Verwendung der eingegebenen Zahlenwerte einer Gleichung diese Gleichung löst und die unbekannte Variable x findet. Das Ergebnis der Berechnung wird in einem Textfeld auf dem Formular und auf einer Excel-Tabelle angezeigt.

Reis. 92. Die entwickelte Form von Beispiel 41 in funktionsfähigem Zustand

Ausführungstechnologie

1. Starten Sie die Excel-Anwendung, speichern Sie das Dokument.

2. Wechseln Sie in den VBA-Editor.

3. Erstellen Sie ein Formular wie in Abb. 1 gezeigt. 92.

4. Platzieren Sie den erforderlichen Text (Formatierung) auf dem Excel-Blatt und stellen Sie die entsprechenden Informationsausgabezellen bereit (Abb. 93).

Reis. 93. Ausgabe der Ergebnisse in eine Excel-Tabelle nach Ausführung des Beispielformulars 41

5. Bearbeiten Sie die Schaltflächen.

Schaltfläche berechnen

Privater Unterbefehl CommandButton1_Click()

Dim a, b, c als Double

a = CDbl(TextBox1.Text)

b = cbl(TextBox2.Text)

c = CDbl(TextBox3.Text)

Mit ActiveSheet

Bereich("b3").Wert = a

Bereich("b4").Wert = b

Bereich("b5").Wert = c

Range("b6").FormulaLocal = "=b3*b7^3+b4*sin(b7)"

Range("b6").GoalSeek Goal:=c, ChangingCell:=Range("b7")

TextBox4.Text = CStr(.Bereich("b7").Wert)

TextBox4.Text = FormatNummer(TextBox4.Text, 2)

Schließen-Schaltfläche

Privat Sub CommandButton2_Click()

Verfahren zur Formularinitialisierung

Privater Sub UserForm_initialize()

Arbeitsblätter(1).Sichtbar = Falsch

Verwenden von AutoFill-Methoden beim Füllen von Tabellen

Beispiel 42 . Erstellen Sie ein Programm, das gemäß den in die entsprechenden Textfelder des Formulars eingegebenen Textdaten die Dateneingabe für Studenten einer bestimmten Fachrichtung einer Bildungseinrichtung automatisiert. Die Ergebnisse des Ausfüllens der Textfelder werden auf einer Excel-Tabelle angezeigt, die es Ihnen ermöglicht, die Daten bei Bedarf auszudrucken.