Operacje na obiektach Microsoft Excel. Kontrolki formularzy w MS EXCEL

Kontrolki formularzy (Combobox, Checkbox, Counter itp.) pomagają szybko zmieniać dane w arkuszu w określonym zakresie, włączać i wyłączać opcje, dokonywać selekcji itp. W zasadzie można się bez nich obejść, ale kontrolują dane na arkuszu są bardziej wizualne i zmniejszają możliwość wprowadzenia błędnych danych.

Aby wstawić kontrolki do arkusza, musisz wyświetlić kartę Deweloper.

W MS EXCEL 2007 można to zrobić za pomocą menu Przycisk pakietu Office / Opcje programu Excel / Ogólne / Pokaż kartę Deweloper na wstążce .
W MS EXCEL 2010 można to zrobić w następujący sposób: Otwórz kartę Plik; Naciśnij przycisk Opcje; Naciśnij przycisk Dostosuj wstążkę; Wybierz drużynę Dostosowywanie wstążki i karty główne Sprawdź pudełko Deweloper.

Teraz możesz wstawić kontrolkę za pomocą menu: .

Zwróć uwagę, że to menu umożliwia wstawianie formantów ActiveX, które znajdują się poniżej interesujących nas formantów formularzy. Oba typy mają te same elementy Button, List, Checkbox itp. Różnica między nimi polega na tym, że do korzystania z kontrolek ActiveX należy użyć języka VBA, podczas gdy kontrolki formularzy można bezpośrednio powiązać z komórką w arkuszu.

Osobom, które wcześniej nie pracowały z kontrolkami formularzy, zalecamy zapoznanie się z nimi szczegółowo w następujących artykułach:

W tym artykule przyjrzymy się bardziej złożonemu przykładowi kontroli udostępniania i .

Przykład

Przeanalizujmy konkretny przykład użycia kilku kontrolek naraz. Przykładowy plik pokazuje, jak użytkownik może edytować wartości w tabeli za pomocą kontrolek (zakres F9:K12 ).

Z jednym z 3 elementów sterujących Pole kombi, lista oraz Licznik, użytkownik może wybrać kolumnę tabeli (rok) . Żądaną kontrolę wybiera się za pomocą grupy przełączniki. Nazwa wybranego elementu jest podświetlona na szaro (patrz rys. A8:B8 na ryc. nad). Wybrany rok jest wyróżniony w tabeli kolorem ciemnoszarym (patrz rys. H9:H12 na ryc. nad). Wyświetlanie tego wyboru jest regulowane pole wyboru(tło flagi jest czerwone). paski przewijania można edytować Cena £ oraz Ilość w wybranym roku, ale tylko w określonym zakresie. Teraz - bardziej szczegółowo.

Przełączniki

Najpierw wstawmy element do arkusza. Za pomocą tego elementu włączymy i wyłączymy zaznaczenie w tabeli kolumny wybranego roku. Połącz element z komórką 2 GBP . Jeśli pole wyboru nie jest zaznaczone, ta komórka będzie FALSE (ta wartość odpowiada 0), jeśli jest zaznaczona, to TRUE (ta wartość odpowiada 1).

Wybierz zakres do ustawienia. G9:K12 . Ponieważ formuła w Formatowaniu warunkowym będzie zawierała , upewnimy się, że po zaznaczeniu zakresu aktywną komórką będzie G9 (tzn. zakres musi być zaznaczony zaczynając od niej. Podpowiedź to pole Nazwa, znajduje się na lewo od Paski formuł. Po wybraniu zakresu powinien zawierać G 9 ).

wywołaj narzędzie ( Strona główna/ Style/ Formatowanie warunkowe/ Utwórz regułę);
Wybierz Użyj formuły zdefiniować sformatowane komórki;
w polu " Formatuj wartości, dla których poniższa formuła jest prawdziwa" wejść =I(KOLUMNA(G9)=Przesunięcie roku+6,$G$2) Formuła stanie się PRAWDA, gdy jednocześnie zostaną spełnione 2 warunki:
wartość wyrażenia ( PrzesunięcieRok(zmiana od 1 do 5 (tj. 2009 do 2013) + 6) odpowiada aktualnemu numerowi kolumny (7, tj. 2009);
Pole wyboru Formatowanie warunkowe zainstalowany.
wybierz żądany format, na przykład szary kolor wypełnienia;
Kliknij OK.

Testowanie

upewnij się, że pole wyboru Formatowanie warunkowe zainstalowany;
wybierz przełącznik Lista;
pod kontrolą Lista wybierz 2010;
upewnij się, że kolumna 2010 podświetlony na szaro;
pasek przewijania zmienić ilość w kolumnie 2010.

Wynik pokazano na rysunku.

Niestety, kontrolki formularzy Pole wyboru, pole kombi oraz Lista nie ma możliwości sformatowania wyświetlanej czcionki. Ale można to zrobić dla kontrolek ActiveX ( Deweloper/ Sterowanie/ Wklej). To prawda, że aby pracować z tymi elementami, musisz napisać program w VBA.

Aby ułatwić wprowadzanie danych do tabeli w Excelu, możesz skorzystać ze specjalnych formularzy, które pomogą przyspieszyć proces wypełniania zakresu tabeli informacjami. Excel ma wbudowane narzędzie, które umożliwia wypełnianie w ten sposób. Użytkownik może również stworzyć własną wersję formularza, która będzie maksymalnie dostosowana do jego potrzeb, wykorzystując do tego makro. Przyjrzyjmy się różnym zastosowaniom tych przydatnych narzędzi do wypełniania w programie Excel.

Formularz wypełniający to obiekt z polami, których nazwy odpowiadają nazwom kolumn kolumn wypełnianej tabeli. Musisz wpisać dane w te pola, a zostaną one natychmiast dodane jako nowa linia do zakresu tabeli. Formularz może pełnić funkcję zarówno samodzielnego wbudowanego narzędzia Excela, jak i znajdować się bezpośrednio na arkuszu jako jego zakres, jeśli został stworzony przez samego użytkownika.

Przyjrzyjmy się teraz, jak korzystać z tych dwóch rodzajów narzędzi.

Metoda 1: Wbudowany obiekt do wprowadzania danych w programie Excel

Przede wszystkim nauczmy się korzystać z wbudowanego formularza do wprowadzania danych w Excelu.

Należy zauważyć, że domyślnie ikona, która go uruchamia, jest ukryta i należy ją aktywować. W tym celu przejdź do zakładki "Plik", a następnie kliknij element „Opcje”.

W otwartym oknie opcji programu Excel przejdź do sekcji "Pasek szybkiego dostępu". Większość okna zajmuje obszerny obszar ustawień. Po lewej stronie znajdują się narzędzia, które można dodać do paska szybkiego dostępu, a po prawej te, które już są obecne.
W terenie "Wybierz polecenia z" ustalić wartość "Poleceń nie na wstążce". Następnie z listy poleceń, ułożonych w kolejności alfabetycznej, znajdujemy i wybieramy pozycję „Forma…”. Następnie kliknij przycisk "Dodać".

Następnie potrzebne narzędzie zostanie wyświetlone po prawej stronie okna. Kliknij przycisk OK.

Teraz to narzędzie znajduje się w oknie Excela na pasku narzędzi szybkiego dostępu i możemy z niego korzystać. Będzie obecny, gdy dowolny skoroszyt zostanie otwarty przez to wystąpienie programu Excel.

Teraz, aby narzędzie rozumiało, co dokładnie musi wypełnić, należy zaprojektować nagłówek tabeli i wpisać w nim dowolną wartość. Niech tablica tabeli, którą mamy, będzie składała się z czterech kolumn, które mają nazwy "Nazwa produktu", "Ilość", "Cena £" oraz "Suma". Nazwy te wpisujemy w dowolnym poziomym zakresie arkusza.

Ponadto, aby program dokładnie rozumiał, z jakimi zakresami będzie musiał pracować, należy wprowadzić dowolną wartość w pierwszym wierszu tablicy tabeli.

Następnie wybierz dowolną komórkę pustej tabeli i kliknij ikonę na pasku narzędzi szybkiego dostępu „Forma…”, który wcześniej aktywowaliśmy.

Otworzy się więc okno określonego narzędzia. Jak widać, obiekt ten posiada pola odpowiadające nazwom kolumn naszej tablicy tabeli. W tym przypadku pierwsze pole jest już wypełnione wartością, ponieważ wprowadziliśmy ją ręcznie w arkuszu.

W pozostałych polach wpisz wartości, które uważamy za niezbędne, a następnie kliknij przycisk "Dodać".

Następnie, jak widzimy, wprowadzone wartości zostały automatycznie przeniesione do pierwszego wiersza tabeli, a w formularzu nastąpiło przejście do kolejnego bloku pól, które odpowiadają drugiemu wierszowi tablicy tabeli.

Wypełniamy okno narzędzia wartościami, które chcemy zobaczyć w drugim rzędzie obszaru tabeli i ponownie klikamy przycisk "Dodać".

Jak widać, dodano również wartości z drugiego rzędu, a my nie musieliśmy nawet przestawiać kursora w samej tabeli.

W ten sposób wypełniamy tablicę tabeli wszystkimi wartościami, które chcemy do niej wpisać.

Ponadto w razie potrzeby można poruszać się po wcześniej wprowadzonych wartościach za pomocą przycisków "Z powrotem" oraz "Dalej" lub pionowy pasek przewijania.

W razie potrzeby możesz poprawić dowolną wartość w tablicy tabeli, zmieniając ją w formularzu. Aby zmiany zostały wyświetlone na arkuszu, po ich wprowadzeniu w odpowiednim bloku narzędzia, kliknij przycisk "Dodać".

Jak widać, zmiana nastąpiła natychmiast w obszarze tabeli.

Jeśli potrzebujemy usunąć jakąś linię, to za pomocą przycisków nawigacyjnych lub paska przewijania przechodzimy do odpowiadającego mu bloku pól w formularzu. Następnie kliknij przycisk "Usuwać" w oknie narzędzia.

Otworzy się okno dialogowe z ostrzeżeniem, informujące, że wiersz zostanie usunięty. Jeśli jesteś pewny swoich działań, kliknij przycisk OK.

Jak widać, linia została wyodrębniona z zakresu tabeli. Po zakończeniu wypełniania i edycji możesz wyjść z okna narzędzia, klikając przycisk "Blisko".

Następnie, aby nadać tablicy tabeli bardziej wizualny wygląd, można wykonać formatowanie.

Metoda 2: Utwórz formularz użytkownika

Dodatkowo za pomocą makra i szeregu innych narzędzi możliwe jest stworzenie własnego formularza użytkownika do wypełnienia obszaru tabeli. Zostanie utworzony bezpośrednio na arkuszu i będzie reprezentował jego zasięg. Za pomocą tego narzędzia sam użytkownik będzie mógł zaimplementować te funkcje, które uzna za niezbędne. Pod względem funkcjonalności praktycznie w niczym nie będzie gorszy od wbudowanego analogu Excela, a pod pewnymi względami być może go przewyższy. Jedynym minusem jest to, że będziesz musiał stworzyć osobny formularz dla każdej tablicy tabeli, a nie używać tego samego szablonu, jak jest to możliwe w wersji standardowej.

Podobnie jak w poprzedniej metodzie, przede wszystkim musisz zrobić nagłówek dla przyszłej tabeli na arkuszu. Będzie składał się z pięciu komórek z nazwami: „Nr p / p”, "Nazwa produktu", "Ilość", "Cena £", "Suma".

Następnie musimy zrobić tak zwaną „inteligentną” tabelę z naszej tablicy table, z możliwością automatycznego dodawania wierszy podczas wypełniania sąsiednich zakresów lub komórek danymi. W tym celu wybierz nagłówek i będąc w zakładce "Dom", kliknij przycisk "Formatuj jako tabelę" w przyborniku „Style”. Spowoduje to otwarcie listy dostępnych opcji stylu. Wybór jednego z nich w żaden sposób nie wpłynie na funkcjonalność, dlatego po prostu wybieramy opcję, którą uważamy za bardziej odpowiednią.

Następnie otwiera się małe okno formatowania tabeli. Określa zakres, który wcześniej wybraliśmy, czyli zakres nagłówka. Z reguły wszystko jest poprawnie wypełnione w tym polu. Ale powinniśmy zaznaczyć pole obok parametru "Tabela z nagłówkami". Następnie kliknij przycisk OK.

Tak więc nasz asortyment jest sformatowany jak „inteligentny” stół, o czym świadczy choćby zmiana w wyświetlaniu wizualnym. Jak widać między innymi obok nazwy nagłówka każdej kolumny pojawiły się ikony filtrowania. Powinny być wyłączone. Aby to zrobić, wybierz dowolną komórkę „inteligentnej” tabeli i przejdź do zakładki "Dane". Tam na wstążce w przyborniku „Sortuj i filtruj” kliknij ikonę "Filtr".

Istnieje inna opcja wyłączenia filtra. W takim przypadku nie będzie nawet konieczne przełączanie się na inną zakładkę, pozostając w zakładce "Dom". Po wybraniu komórki obszaru tabeli na wstążce w bloku ustawień "Redagowanie" kliknij ikonę „Sortuj i filtruj”. Wybierz element z wyświetlonej listy. "Filtr".

Jak widać, po tej akcji ikony filtrowania zniknęły z nagłówka tabeli, zgodnie z wymaganiami.

Następnie musimy stworzyć sam formularz wprowadzania danych. Będzie to również rodzaj tablicy tabelarycznej składającej się z dwóch kolumn. Nazwy wierszy tego obiektu będą odpowiadać nazwom kolumn tabeli głównej. Kolumny są wyjątkiem. „Nr p / p” oraz "Suma". Będą nieobecni. Numeracja pierwszego z nich zostanie wykonana za pomocą makra, a obliczenie wartości drugiego zostanie wykonane poprzez zastosowanie wzoru na pomnożenie ilości przez cenę.
Na razie zostawmy drugą kolumnę obiektu wprowadzania danych pustą. Wartości zostaną wprowadzone bezpośrednio do niego później, aby wypełnić wiersze głównego zakresu tabeli.

Następnie tworzymy kolejny mały stolik. Będzie składał się z jednej kolumny i będzie zawierał listę produktów, które wyświetlimy w drugiej kolumnie tabeli głównej. Dla jasności komórka z tytułem tej listy ( "Lista produktów") można wypełnić kolorem.

Następnie wybieramy pierwszą pustą komórkę obiektu wprowadzania wartości. Przejdź do zakładki "Dane". Kliknij ikonę "Walidacji danych", który znajduje się na wstążce w przyborniku "Praca z danymi".

Otworzy się okno weryfikacji danych wejściowych. Kliknij pole "Typ danych", domyślnie "Dowolna wartość".

Wybierz pozycję z wyświetlonych opcji. "Lista".

Jak widać, po tym oknie do sprawdzania wartości wejściowych zmieniło nieco swoją konfigurację. Dodano dodatkowe pole "Źródło". Klikamy ikonę po prawej stronie lewym przyciskiem myszy.

Następnie okno sprawdzania poprawności danych wejściowych jest minimalizowane. Wybieramy kursorem z wciśniętym lewym przyciskiem myszy listę danych, które są umieszczone na arkuszu w dodatkowym obszarze tabeli "Lista produktów". Następnie ponownie kliknij ikonę po prawej stronie pola, w którym pojawił się adres wybranego zakresu.

Następuje powrót do okna sprawdzania wprowadzonych wartości. Jak widać współrzędne wybranego zakresu są już wyświetlane w polu "Źródło". Klikamy na przycisk OK na dole okna.

Teraz po prawej stronie zaznaczonej pustej komórki obiektu wprowadzania danych pojawiła się ikona w postaci trójkąta. Po kliknięciu otworzy się lista rozwijana składająca się z nazw wyciągniętych z tablicy tabeli "Lista produktów". Nie można teraz wprowadzić dowolnych danych do określonej komórki, ale można wybrać tylko żądaną pozycję z przedstawionej listy. Wybierz pozycję z listy rozwijanej.

Jak widać, wybrana pozycja jest natychmiast wyświetlana w polu "Nazwa produktu".

Następnie będziemy musieli przypisać nazwy do trzech komórek formularza wejściowego, w których będziemy wprowadzać dane. Wybierz pierwszą komórkę, w której nazwa jest już ustawiona w naszym przypadku "Ziemniak". Następnie przejdź do pola nazwy zakresu. Znajduje się po lewej stronie okna Excela na tym samym poziomie, co pasek formuły. Wpisz tam dowolną nazwę. Może to być dowolna nazwa w języku łacińskim, w której nie ma spacji, ale lepiej używać nazw zbliżonych do zadań rozwiązywanych przez ten element. Dlatego pierwszą komórkę, która zawiera nazwę produktu, zadzwonimy Nazwa. Wpisz tę nazwę w polu i naciśnij przycisk Wchodzić na klawiaturze.

W ten sam sposób przypisujemy nazwę do komórki, w której wprowadzimy ilość towaru Tom.

A komórka z ceną - Cena £.

Następnie dokładnie w ten sam sposób nadajemy nazwę całemu zakresowi powyższych trzech komórek. Przede wszystkim wybierzemy, a następnie nadamy mu nazwę w specjalnym polu. Niech to będzie nazwa Zasięg.

Po ostatniej akcji musimy zapisać dokument, aby nadawane przez nas nazwy mogły być odebrane przez utworzone później makro. Aby zapisać, przejdź do zakładki "Plik" i kliknij przedmiot "Zapisz jako…".

W otwartym oknie zapisu, w terenie "Typ pliku" wybierz wartość „Skoroszyt z obsługą makr programu Excel (.xlsm)”. Następnie kliknij przycisk "Ratować".

Następnie powinieneś w swojej wersji Excela i włączyć kartę "Deweloper" jeśli jeszcze tego nie zrobiłeś. Faktem jest, że obie te funkcje są domyślnie wyłączone w programie, a ich aktywację należy wymusić w oknie opcji Excela.
Po wykonaniu tej czynności przejdź do zakładki "Deweloper". Kliknij dużą ikonę "Visual Basic", który znajduje się na wstążce w przyborniku "Kod".

Ostatnia akcja powoduje uruchomienie edytora makr VBA. W obszarze Projekt, który znajduje się w lewej górnej części okna, wybierz nazwę arkusza, na którym znajdują się nasze tabele. W tym przypadku to "Arkusz 1".

Następnie przejdź do lewego dolnego obszaru okna o nazwie "Nieruchomości". Oto ustawienia dla wybranego arkusza. W terenie "(Nazwa)" Należy zastąpić nazwę cyrylicą ( „Arkusz1”) do nazwy pisanej po łacinie. Możesz podać dowolną nazwę, która jest dla Ciebie wygodniejsza, najważniejsze jest to, że zawiera tylko znaki lub cyfry łacińskie i nie ma innych znaków ani spacji. To właśnie pod tą nazwą zadziała makro. Niech w naszym przypadku ta nazwa będzie produkt, chociaż możesz wybrać dowolny inny spełniający warunki opisane powyżej.
W terenie Nazwa możesz również zmienić nazwę na wygodniejszą. Ale to jest opcjonalne. Dozwolone jest użycie spacji, cyrylicy i wszelkich innych znaków. W przeciwieństwie do poprzedniego parametru, który określa nazwę arkusza dla programu, ten parametr przypisuje arkuszowi nazwę widoczną dla użytkownika na pasku skrótów.

Jak widać, po tym czasie nazwa zmieni się automatycznie. Arkusz 1 w obszarze Projekt, do tego, który właśnie ustawiliśmy w ustawieniach.

Następnie przejdź do środkowego obszaru okna. W tym miejscu będziemy musieli napisać sam kod makra. Jeśli białe pole edytora kodu w określonym obszarze nie jest wyświetlane, jak w naszym przypadku, kliknij klawisz funkcyjny F7 i to się pojawi.

Teraz, dla naszego konkretnego przykładu, musimy napisać następujący kod w polu:
Sub DataEntryForm()
Dim nextRow tak długo
nextRow = Producty.Cells(Producty.Rows.Count, 2).End(xlUp).Offset(1, 0).Row
Z produkcją
If .Range("A2").Value = "" And .Range("B2").Value = "" Then
następnyRow = następnyRow - 1
Zakończ, jeśli
Produkcja.Zakres("Nazwa").Kopiuj

.Range("A2").Formula = "=JEŻELI(CZY(NIE)PUSTY(B2)","""",ILE.LICZB ($B$2:B2))"
Jeśli następny wiersz > 2 Wtedy
Zakres ("A2"). Wybierz

Zakończ, jeśli
.Range("Zakres").ClearContents
kończyć z
napis końcowy

Ale ten kod nie jest uniwersalny, to znaczy nadaje się tylko do naszego przypadku bez zmian. Jeśli chcesz dostosować go do swoich potrzeb, należy go odpowiednio zmodyfikować. Abyś mógł to zrobić sam, spójrzmy z czego składa się ten kod, co należy w nim wymienić, a czego nie zmieniać.

Więc pierwsza linia:

Sub DataEntryForm()

"Formularz Wprowadzania Danych" to nazwa samego makra. Możesz go pozostawić bez zmian lub zastąpić innym, który spełnia ogólne zasady tworzenia nazw makr (bez spacji, używanie tylko liter łacińskich itp.). Zmiana nazwy na nic nie wpłynie.

Gdziekolwiek słowo występuje w kodzie produkt musisz go zastąpić nazwą, którą wcześniej przypisałeś do swojego arkusza w polu "(Nazwa)" obszary "Nieruchomości" edytor makr. Oczywiście powinno to być zrobione tylko wtedy, gdy nazwałeś arkusz inaczej.

Rozważmy teraz tę linię:

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

Numer „2” w tym wierszu oznacza drugą kolumnę arkusza. To w tej kolumnie znajduje się kolumna "Nazwa produktu". Zgodnie z nim policzymy liczbę rzędów. Dlatego jeśli w Twoim przypadku podobna kolumna ma inną kolejność na koncie, musisz wpisać odpowiedni numer. Oznaczający "Koniec (xlUp). Przesunięcie (1, 0). Wiersz" w każdym razie pozostawiamy to bez zmian.

If .Range("A2").Value = "" And .Range("B2").Value = "" Then

„A2” to współrzędne pierwszej komórki, w której zostanie wyświetlona numeracja wierszy. „B2”- są to współrzędne pierwszej komórki, na którą zostaną wyprowadzone dane ( "Nazwa produktu"). Jeśli są dla Ciebie różne, wprowadź swoje dane zamiast tych współrzędnych.

Przejdź do linii

Produkcja.Zakres("Nazwa").Kopiuj

Zawiera parametr Nazwa oznacza nazwę, którą nadaliśmy polu "Nazwa produktu" w formularzu wejściowym.

W liniach

Komórki(nextRow, 2). PasteSpecial Paste:=xlPasteValues
.Cells(nextRow, 3).Value = Produktywność.Range("Objętość").Value
.Cells(nextRow, 4).Value = Produkcja.Range("Cena").Value
.Cells(nextRow, 5).Value = Producty.Range("Wolumen").Value * Producty.Range("Cena").Value

denominacje Tom oraz Cena £ oznaczają nazwy, które przypisaliśmy do pól "Ilość" oraz "Cena £" w tym samym formularzu wejściowym.

W tych samych wierszach, które wskazaliśmy powyżej, liczby „2”, „3”, „cztery”, „5” oznaczają numery kolumn w arkuszu Excela odpowiadające kolumnom "Nazwa produktu", "Ilość", "Cena £" oraz "Suma". Dlatego jeśli w twoim przypadku tabela zostanie przesunięta, musisz podać odpowiednie numery kolumn. Jeśli jest więcej kolumn, to przez analogię musisz dodać jej wiersze do kodu, jeśli jest ich mniej, usuń dodatkowe.

Linia mnoży ilość produktu przez jego cenę:

Cells(nextRow, 5).Value = Producty.Range("Wolumen").Value * Producty.Range("Cena").Value

Wynik, jak widzimy ze składni rekordu, zostanie wyświetlony w piątej kolumnie arkusza Excel.

To wyrażenie wykonuje automatyczną numerację wierszy:

Jeśli następny wiersz > 2 Wtedy
Zakres ("A2"). Wybierz
Selection.AutoFill Destination:=Range("A2:A" & nextRow)
Zakres ("A2:A" i następny wiersz).Wybierz
Zakończ, jeśli

Wszystkie wartości „A2” oznaczają adres pierwszej komórki, w której zostanie wykonana numeracja, oraz współrzędne " A"- adres całej kolumny wraz z numeracją. Sprawdź, gdzie dokładnie numeracja będzie wyświetlana w Twojej tabeli i w razie potrzeby zmień te współrzędne w kodzie.

Linia czyści zakres formularza wprowadzania danych po przeniesieniu informacji z niego do tabeli:

Zakres ("Diapason"). ClearContents

Nietrudno się domyślić Zasięg) oznacza nazwę zakresu, który wcześniej przypisaliśmy do pól wprowadzania danych. Jeśli nadałeś im inną nazwę, to właśnie to powinno zostać wstawione w tej linii.

Pozostała część kodu jest uniwersalna i we wszystkich przypadkach pozostanie niezmieniona.

Po wpisaniu kodu makra do okna edytora należy kliknąć ikonę zapisywania dyskietki po lewej stronie okna. Następnie możesz je zamknąć, klikając standardowy przycisk zamykania okna w prawym górnym rogu.

Następnie wracamy do arkusza Excela. Teraz musimy umieścić przycisk, który aktywuje utworzone makro. W tym celu przejdź do zakładki "Deweloper". W bloku ustawień "Sterownica" kliknij przycisk na wstążce "Wstawić". Otworzy się lista narzędzi. W grupie narzędzi „Kontrolki formularza” wybierz pierwszy - "Przycisk".

Następnie trzymając wciśnięty lewy przycisk myszy zakreślamy obszar, w którym chcemy umieścić przycisk uruchamiania makra, który przeniesie dane z formularza do tabeli.

Po zakreśleniu obszaru zwolnij przycisk myszy. Następnie automatycznie uruchamia się okno do przypisania makra do obiektu. Jeśli Twoja książka używa kilku makr, wybierz z listy nazwę tego, który utworzyliśmy powyżej. Nazywamy to "Formularz Wprowadzania Danych". Ale w tym przypadku jest tylko jedno makro, więc po prostu wybierz je i kliknij przycisk OK na dole okna.
Następnie możesz zmienić nazwę przycisku, jak chcesz, po prostu podświetlając jego aktualną nazwę.

Na przykład w naszym przypadku logiczne byłoby nadanie jej imienia "Dodać". Zmień nazwę i kliknij dowolną wolną komórkę arkusza.

Więc nasz formularz jest całkowicie gotowy. Sprawdźmy, jak to działa. Wprowadź wymagane wartości w swoich polach i kliknij przycisk "Dodać".

Jak widać, wartości są przenoszone do tabeli, linii automatycznie przypisywana jest liczba, obliczana jest kwota, pola formularza są czyszczone.

Wypełnij formularz ponownie i kliknij przycisk "Dodać".

Jak widać, do tablicy tabeli dodawany jest również drugi wiersz. Oznacza to, że narzędzie działa.

Adnotacja: Wykład poświęcony jest opisowi modelu obiektowego MS Excel oraz szczegółowy opis metody, właściwości i zdarzenia Aplikacji.

13.1. Funkcje programowania dla MS Excel

Microsoft Office Excel to popularne arkusze kalkulacyjne. Zazwyczaj programując ten program, realizują następujące cele:

Automatyzacja obliczeń.
Automatyzacja wprowadzania i przetwarzania informacji.
Praca z bazami danych - wyjście, wejście, analiza, wizualizacja informacji.
Analiza informacji finansowych i innych.
Tworzenie systemów do organizowania automatycznego wprowadzania danych
Modelowanie matematyczne.

Ogólnie programowanie w programie Excel jest podobna do pracy z programem Microsoft Word. Jednak jedną z głównych różnic jest to, że w programie Excel obszar roboczy arkusza roboczego jest podzielony na komórki, z których każda ma własną nazwę. Nazwy komórek mogą być dwojakiego rodzaju.

Pierwszy rodzaj (styl A1) to nazwa składająca się z dosłownej nazwy kolumny i numeru wiersza. Na przykład A1 to komórka znajdująca się na przecięciu kolumny A (pierwszej) i pierwszego rzędu.
Drugi rodzaj to indeksy komórek (styl nazewnictwa R1C1 ). Aby zaadresować komórkę w tym stylu, wskaż numer wiersza (R - Wiersz - linia) i numer kolumny (C - Kolumna - kolumna), na przecięciu której znajduje się komórka. Wiersze są początkowo ponumerowane, a numery kolumn zaczynają się od 1 - pierwsza kolumna odpowiada kolumnie A, druga - B itd. Na przykład (2, 3) to adres komórki znajdującej się na przecięciu drugiego rzędu i trzeciej kolumny, to znaczy, jeśli przeniesiemy to do stylu A1, otrzymamy komórkę C2 (ryc. 13.1.)

Ryż. 13.1.

Poniższe obiekty służą do wykonywania większości operacji w MS Excel.

Excel.Application (Application) — obiekt reprezentujący aplikację Microsoft Excel, podobnie jak Word.Application .
zeszyt ćwiczeń(Skoroszyt) — reprezentuje skoroszyt — analogiczny do dokumentu Microsoft Word. Jednak w Wordzie pracujemy z danymi znajdującymi się w dokumencie, a w Excelu na drodze do danych jest jeszcze jeden obiekt - arkusz roboczy.
Arkusz roboczy(Worksheet) - skoroszyt w MS Excel podzielony jest na arkusze. To na arkuszu znajdują się komórki, które mogą przechowywać informacje i formuły.
Zakres (zakres) — może być reprezentowany jako pojedyncza komórka lub grupa komórek. Ten obiekt zastępuje wiele obiektów do pracy z elementami dokumentu (znak , słowo itp.), które są używane w programie Microsoft Word. Dzięki temu praca z arkuszem staje się bardzo przejrzysta i wygodna - do pracy z dowolną komórką wystarczy znać jej nazwę (w formacie A1) lub adres (R1C1).
QueryTable (Tabela zapytań) - ten obiekt służy do importowania informacji z baz danych do Microsoft Excel. Połączenie z bazą danych, prośba o informacje itp. są tworzone za pomocą obiektu, a wyniki zapytania są przesyłane do arkusza MS Excel w postaci zwykłej tabeli.
Tabela przestawna (PivotTable) to specjalny rodzaj arkusza kalkulacyjnego Excel - pozwala na interaktywne podsumowywanie i analizowanie dużych ilości informacji, w szczególności pobranych z bazy danych.
wykres(Schemat) - przedstawia diagram. Są one zwykle używane do wizualizacji danych.

Zacznijmy recenzję Model obiektowy MS Excel z obiektu Application.

13.2. Obiekt aplikacji

MsgBox Excel.Nazwa.aplikacji Listing 13.1. Wyświetl nazwę aplikacji

Po uruchomieniu programu w oknie komunikatu zostanie wyświetlona nazwa aplikacji — w tym przypadku Microsoft Excel. Oczywiście właściwość Name obiektu Application zwraca nazwę aplikacji.

Przyjrzyjmy się teraz najważniejszym metodom i właściwościom Application . Niektóre z nich są podobne do tych w MS Word. Np. metoda Quit, podobnie jak w Wordzie, zamyka aplikację, właściwość Visible odpowiada za widoczność okna programu itp.

13.3. Metody

13.3.1. Oblicz - wymuszone przeliczenie

Ta metoda, wywoływana w obiekcie Application, umożliwia ponowne obliczenie wszystkich otworów księgi. Można go również nazwać dla pojedynczych książek (obiekt zeszyt ćwiczeń) pościel ( Arkusz roboczy), komórki i ich zakresy (Range ). Na przykład kod z listingu 13.2. umożliwia ponowne obliczenie wszystkich otwartych skoroszytów.

Aplikacja.Oblicz Listing 13.2. Przelicz wszystkie otwarte książki

13.3.2. GoTo - przejdź do komórki

13-02-Excel GoTo.xlsm - przykład do paragrafu 13.3.2.

Pozwala wybrać dowolny zakres komórek w dowolnej księdze, a jeśli książka nie jest aktywna, zostanie aktywowana. Metoda może również uruchamiać makra Microsoft Excel.

Pełne wywołanie metody wygląda tak:

Przejdź do (odniesienie, przewijanie)

Parametr Reference jest odwołaniem do komórki lub zakresu komórek, które należy wybrać po wywołaniu metody. Można tu również użyć nazwy makra - wtedy zostanie uruchomione.

Parametr Scroll odpowiada za „przewijanie” arkusza Excela do wybranych komórek – tak, aby lewy górny róg zaznaczenia pokrywał się z lewym górnym rogiem wyświetlanego obszaru arkusza. Jeśli Scroll jest ustawiony na True, arkusz jest przewijany, jeśli jest False, nie jest.

Na przykład takie wywołanie (Listing 13.3) pozwala wybrać komórkę H500 na aktywnym arkuszu.

Application.Goto _Reference:=ActiveSheet.Range("H500"), _Scroll:=True Listing 13.3. Wybierz komórkę H500

Jak widać, dostęp do aktywnego arkusza jest bardzo podobny do uzyskiwania dostępu do aktywnego dokumentu w MS Word. Zauważ, że używamy pełnego wywołania metody — Application.GoTo — jak wiesz, zazwyczaj możesz użyć właściwości i metod obiektu Application w swoim kodzie bez określania tego obiektu. Jeśli jednak nie podasz Application w tym przypadku, to zamiast metody GoTo program spróbuje wykonać bezwarunkowy operator skoku Iść do .

13.3.3. SendKeys - symuluj naciśnięcia klawiszy na klawiaturze

13-03-Excel SendKeys.xlsm - przykład do paragrafu 13.3.3.

Bardzo ciekawa metoda - umożliwia wysyłanie naciśnięć klawiszy do aktywnego okna aplikacji. Pełne wywołanie metody wygląda tak:

WyślijKlawisze (Klucze, Czekaj)

Parametr Keys pozwala określić klucze, które zostaną przekazane do aplikacji. Metoda obsługuje emulację zarówno alfanumerycznego, jak i klawisze sterujące, dla którego stosowane jest specjalne kodowanie. Klucze alfanumeryczne są określane podczas wywoływania w ich zwykłej formie, na przykład, aby przenieść znak „Ф”, należy go podać podczas wywoływania metody itp.

Aby wysłać naciśnięcie klawisza do aplikacji Backspace- użyj kodu (BS) . Aby wysłać przycisk, naciśnij Wchodzić użyj ~ (

Wielu użytkowników programu Excel nie widzi różnicy między „formatem komórki” a „typem danych”. W rzeczywistości są to dalekie od identycznych pojęć, choć oczywiście są ze sobą powiązane. Dowiedzmy się, na czym polega istota typów danych, na jakie kategorie są podzielone i jak można z nimi pracować.

Typ danych jest cechą informacji przechowywanych w arkuszu. Na podstawie tej cechy program określa sposób przetwarzania określonej wartości.

Typy danych są podzielone na dwie duże grupy: stałe i formuły. Różnica między nimi polega na tym, że formuły wyprowadzają wartość do komórki, która może się zmieniać w zależności od tego, jak zmieniają się argumenty w innych komórkach. Stałe to stałe wartości, które się nie zmieniają.

Z kolei stałe są podzielone na pięć grup:

Tekst;
Dane liczbowe;
Data i godzina;
dane logiczne;
Błędne wartości.

Dowiedzmy się bardziej szczegółowo, co reprezentuje każdy z tych typów danych.

Wartości tekstowe

Typ tekstu zawiera dane znakowe i nie jest traktowany przez program Excel jako obiekt obliczeń matematycznych. Informacje te są przeznaczone przede wszystkim dla użytkownika, a nie dla programu. Tekst może być dowolnym znakiem, w tym cyframi, o ile są one odpowiednio sformatowane. W języku DAX ten rodzaj danych odnosi się do wartości ciągów. Maksymalna długość tekstu to 268435456 znaków w jednej komórce.

Aby wprowadzić wyrażenie symboliczne, musisz wybrać komórkę tekstu lub ogólny format, w którym będzie ono przechowywane, i wpisać tekst z klawiatury. Jeśli długość wyrażenia tekstowego wykracza poza wizualne granice komórki, jest nakładana na sąsiednie, chociaż nadal jest fizycznie przechowywana w oryginalnej komórce.

Dane liczbowe

Dane liczbowe są wykorzystywane do obliczeń bezpośrednich. To za ich pomocą Excel wykonuje różne operacje matematyczne (dodawanie, odejmowanie, mnożenie, dzielenie, potęgowanie, wydobywanie pierwiastków itp.). Ten typ danych jest przeznaczony wyłącznie do zapisywania liczb, ale może również zawierać znaki pomocnicze (%, $ itp.). Może być używany w kilku formatach:

Właściwie numeryczne;
Odsetek;
Monetarny;
Budżetowy;
Frakcyjny;
Wykładniczy.

Ponadto Excel ma możliwość dzielenia liczb na cyfry oraz określania liczby cyfr po przecinku (w liczbach ułamkowych).

Dane liczbowe wprowadza się w taki sam sposób, jak wartości tekstowe, o których mówiliśmy powyżej.

Data i godzina

Innym rodzajem danych jest format czasu i daty. Tak jest dokładnie w przypadku, gdy typy i formaty danych są zgodne. Charakteryzuje się tym, że może służyć do wskazywania na arkuszu oraz wykonywania obliczeń z datami i godzinami. Warto zauważyć, że w obliczeniach ten typ danych przyjmuje dzień jako jednostkę. I dotyczy to nie tylko dat, ale także czasu. Na przykład godzina 12:30 jest traktowana przez program jako 0,52083 dni i dopiero wtedy jest wyświetlana w komórce w formie znanej użytkownikowi.

Istnieje kilka rodzajów formatowania czasu:

h:mm:ss;
Hmm;
h:mm:ss AM/PM;
h:mm AM/PM itp.

Podobnie jest z datami:

DD / MM / RRRR;
DD.MMM
MMM.RR i inne.

Istnieją również połączone formaty daty i godziny, takie jak DD:MM:RRRR h:mm.

Trzeba też wziąć pod uwagę, że program wyświetla jako daty tylko wartości zaczynające się od 01.01.2019.

Dane logiczne

Dość interesujący jest typ danych binarnych. Działa tylko z dwiema wartościami: "PRAWDA" oraz "FAŁSZYWY". Jeśli przesadzamy, oznacza to, że „wydarzenie nadeszło” i „zdarzenie nie nadeszło”. Funkcje przetwarzające zawartość komórek zawierających dane logiczne wykonują określone obliczenia.

Błędne wartości

Wartości błędów to osobny typ danych. W większości przypadków pojawiają się one po wykonaniu nieprawidłowej operacji. Na przykład takie nieprawidłowe operacje to dzielenie przez zero lub wprowadzenie funkcji bez przestrzegania jej składni. Wśród błędnych wartości są:

#WARTOŚĆ! – użycie niewłaściwego rodzaju argumentu dla funkcji;
#BIZNES! – dzielenie przez 0;
#NUMER! – nieprawidłowe dane liczbowe;
#N/A – wprowadzona wartość niedostępna;
#NAZWA? – błędna nazwa w formule;
#PUSTY! – nieprawidłowe wprowadzenie adresów zakresów;
#POŁĄCZYĆ! - występuje podczas usuwania komórek, do których wcześniej odnosiła się formuła.

Formuły

Formuły to odrębna duża grupa typów danych. W przeciwieństwie do stałych, najczęściej nie są one widoczne w samych komórkach, a jedynie wyświetlają wynik, który może się zmieniać w zależności od zmiany argumentów. W szczególności formuły są używane do różnych obliczeń matematycznych. Samą formułę można zobaczyć na pasku formuły, podświetlając komórkę, w której jest zawarta.

Warunkiem koniecznym, aby program postrzegał wyrażenie jako formułę, jest obecność przed nim znaku równości (=) .

Funkcje to osobny typ formuł. Są to osobliwe podprogramy, które zawierają zestaw argumentów i przetwarzają je zgodnie z określonym algorytmem. Funkcje można wprowadzać ręcznie do komórki, najpierw umieszczając w niej znak «=» , ale możesz użyć do tego specjalnej powłoki graficznej Kreator funkcji, który zawiera całą listę operatorów dostępnych w programie z podziałem na kategorie.

Używając Kreatory funkcji możesz przejść do okna argumentów konkretnego operatora. W jego polach wprowadzane są dane lub linki do komórek, w których te dane się znajdują. Po naciśnięciu przycisku OK wykonywana jest określona operacja.

Jak widzimy, w Program Excel Istnieją dwie główne grupy typów danych: stałe i formuły. Te z kolei dzielą się na wiele innych typów. Każdy typ danych ma swoje właściwości, zgodnie z którymi program je przetwarza. Opanowanie umiejętności rozpoznawania i poprawnej pracy z różnymi typami danych jest podstawowym zadaniem każdego użytkownika, który chce nauczyć się efektywnie korzystać z programu Excel zgodnie z jego przeznaczeniem.

Frizen Irina Grigorievna – kandydatka nauk pedagogicznych, wykładowca Technologie informacyjne, autor licznych prac z zakresu informatyki.

Ten podręcznik został napisany zgodnie z państwowym programem studiowania dyscypliny dla średnich wyspecjalizowanych instytucji edukacyjnych w specjalności 230103 „Zautomatyzowane systemy przetwarzania i zarządzania informacjami”.

Programowanie biurowe

Podręcznik zapewnia wystarczający materiał do prowadzenia zajęć praktycznych w dyscyplinie, a także podstawowy materiał teoretyczny niezbędny do studiowania określonego zagadnienia. Na podstawie zaproponowanego materiału możliwe jest zbudowanie prowadzenia różnego rodzaju zajęć.

Podręcznik ma na celu pomóc uczniom w pełnym studiowaniu tej dyscypliny, spełniając wymagania standardu państwowego dla tej dyscypliny.

Przeznaczony jest dla nauczycieli i uczniów średnich specjalistycznych placówek edukacyjnych, może być również używany przez studentów wyższych uczelni studiujących tę dyscyplinę.

Samouczek obejmuje ponad 60 problemów wraz ze 130 rysunkami i szczegółowymi objaśnieniami.

Książka:

Sekcje na tej stronie:

Korzystanie z obiektów zakresu i wyboru

W Excelu najważniejszy jest obiekt Application. Obiekt Application jest najwyższym obiektem w hierarchii obiektów programu Excel i reprezentuje samą aplikację programu Excel. Posiada ponad 120 właściwości i 40 metod. Te właściwości i metody służą do ustawiania ogólnych preferencji dla aplikacji Excel. W hierarchii programu Excel obiekt Workbook znajduje się bezpośrednio po obiekcie Application i reprezentuje plik skoroszytu. Skoroszyt jest przechowywany w plikach formatu XLS (standardowy skoroszyt) lub XLA (w pełni skompilowana aplikacja). Właściwości i metody skoroszytu umożliwiają pracę z plikami. Jednak najczęściej „używanym” w praktyce jest obiekt Range, który najlepiej odzwierciedla możliwości użycia VBA w programie Excel (patrz Tabela 19 dla właściwości obiektu Range i Tabela 20 dla metod).

W hierarchii obiektów Excel Zasięg(zakres) pojawia się natychmiast po obiekcie arkusz. Obiekt Zasięg jest jednym z kluczowych obiektów VBA. Obiekt selekcji (selekcji) występuje w VBA na dwa sposoby - albo w wyniku metody Select, albo w wyniku wywołania właściwości selekcji. Typ wynikowego obiektu zależy od typu wybranego obiektu. Najczęściej obiekt Selection należy do klasy Range i podczas pracy z nim można korzystać z właściwości i metod obiektu Range. Interesującą cechą obiektów Range i Selection jest to, że nie należą do żadnej rodziny obiektów.

Podczas pracy z obiektem Range należy pamiętać, w jaki sposób Excel odnosi się do komórki arkusza.

Określanie grup wierszy i kolumn za pomocą obiektu zakresu

Jeśli w zakresie określone są tylko nazwy kolumn lub wierszy, obiekt Range określa zakres określonych kolumn lub wierszy. Na przykład Range("a:c") określa zakres składający się z kolumn a, b i c, podczas gdy Range("2:2") określa drugi wiersz. Innym sposobem pracy z wierszami i kolumnami są metody Wiersze (wiersze) i Kolumny (kolumny), które zwracają kolekcje wierszy i kolumn. Na przykład kolumna a to kolumny(1), a wiersz 2 to Rows(2).

Związek między obiektem Range a właściwością Cells

Ponieważ komórka jest szczególnym przypadkiem zakresu, składającego się tylko z jednej komórki, obiekt Range również umożliwia pracę z nią. Obiekt Cells to alternatywny sposób pracy z komórką. Na przykład komórka A2 jako obiekt jest opisana przez Range("A2") lub Cells(l, 2). Z kolei obiekt cell, zagnieżdżony w Range, pozwala również na zapisanie zakresu w formie alternatywnej, czasem wygodnej w pracy, a mianowicie Range("A2:C3") oraz Range(Cells(1,2), Komórki (3,3 )) definiują ten sam zakres.

Tabela 19

Właściwości obiektu zakresu

Metody obiektów zakresu

Metody obiektów zakresu korzystające z poleceń programu Excel

Polecenia i metody wbudowane w program Excel umożliwiają efektywną pracę z zakresem: wypełnianie go elementami według wzorca, sortowanie, filtrowanie i konsolidowanie danych, budowanie tabeli podsumowującej i tworzenie scenariuszy oraz rozwiązywanie równań nieliniowych za pomocą jednego zmienny.

Metoda autouzupełniania

Metoda AutoFill automatycznie wypełnia komórki w zakresie elementami sekwencji. Metoda AutoFill różni się od metody DataSeries tym, że wyraźnie określa zakres, w którym będzie się znajdowała progresja. Ręcznie ta metoda jest równoznaczna z umieszczeniem wskaźnika myszy nad uchwytem wypełniania wybranego zakresu (w którym wprowadzane są wartości generujące wygenerowaną sekwencję) i przeciągnięciem uchwytu wypełniania wzdłuż zakresu, w którym zostanie utworzona sekwencja mieścić się.

Składnia:

obiekt. Autouzupełnianie (zakres, typ)

Argumenty:

Zakres Zakres, od którego rozpoczyna się wypełnianie wpisz Prawidłowe wartości: xlFillDefault, xlFillSeries, xlFillCopy, xlFillFormats, xlFillValues,xlFillDays, xlFillWeekdays, xlFillMonths, xlFillYears, xlLinearTrend, xl.GrowthTrend xlFillDefault

Metoda automatycznego filtrowania

Metoda AutoFilter to prosty sposób wykonywania zapytań i filtrowania danych w arkuszu. Jeśli Autofiltr jest włączony, każdy nagłówek pola wybranego zakresu danych zamienia się w pole listy rozwijanej. Wybranie zapytania do wyświetlenia danych w polu z listą rozwijaną wyświetla tylko te rekordy, które spełniają określone warunki. Rozwijana lista zawiera następujące typy warunków: Wszystkie (Wszystkie), Top 10 (Top 10), Warunek (Niestandardowy), określony element danych, Puste (Puste) i Niepuste (Niepuste). Ręcznie metodę uruchamia się wybierając polecenie Dane, Filtruj, Autofiltruj (Dane, Filtruj, Autofiltr).

W przypadku korzystania z metody AutoFilter prawidłowe są dwie składnie.

Składnia 1:

Obiekt. Autofiltr

W takim przypadku metoda AutoFilter zaznacza lub anuluje polecenie Data, Filtr, Autofiltr zastosowane do zakresu określonego w argumencie obiektu.

Składnia 2:

Obiekt. Autofiltr(pole, kryteria1, operator, kryteria2)

W takim przypadku metoda AutoFilter wykonuje polecenie Data, Filter, AutoFilter zgodnie z kryteriami określonymi w argumencie.

Argumenty:

pole Liczba całkowita określająca pole, według którego dane mają być filtrowane.

Kryteria1 Określ dwa możliwe warunki filtrowania i pola kryteriów2. Możesz użyć stałej łańcuchowej, takiej jak 101, oraz znaków relacji >,<,>=, <=, =, <>

operator Prawidłowe wartości: X1And (unia logiczna pierwszego i drugiego kryterium); X1or (logiczne dodanie pierwszego i drugiego kryterium)

Podczas pracy z filtrami przydatna jest metoda showAllData oraz właściwości FilterMode i AutoFilterMode.

Metoda ShowAllData Pokazuje wszystkie filtrowane i niefiltrowane wiersze arkusza roboczego

Właściwość FilterMode Prawidłowe wartości: True (jeśli arkusz zawiera filtrowane dane z ukrytymi wierszami), False (w przeciwnym razie)

Właściwość AutoFilterMode Prawidłowe wartości: True (jeśli w arkuszu są wyświetlane listy rozwijane metod AutoFilter), False (w przeciwnym razie)

Metoda poszukiwania celu

Metoda GoalSeek (wybór parametru) wybiera wartość parametru (wartość nieznaną), która jest rozwiązaniem równania z jedną zmienną. Zakłada się, że równanie sprowadza się do postaci: prawa strona jest stała, niezależnie od parametru, który jest zawarty tylko w lewej stronie równania. Ręcznie metoda GoalSeek jest wykonywana za pomocą polecenia Service, Parameter selection (Tools, Goal Seek). Metoda GoalSeek wylicza pierwiastek metodą kolejnych przybliżeń, których wynik, ogólnie rzecz biorąc, zależy od przybliżenia początkowego. Dlatego dla poprawności znalezienia pierwiastka trzeba zadbać o poprawne wskazanie tego wstępnego przybliżenia.

Składnia:

Obiekt. Szukanie celu (cel, zmiana komórki)

Argumenty:

Obiekt Komórka, w której wprowadzono formułę, która jest prawą stroną rozwiązywanego równania. W tej formule rolę parametru (nieznana wartość) pełni odwołanie do komórki określonej w argumencie ChangingCell

Bramka Wartość lewej strony rozwiązywanego równania, która nie zawiera parametru

Dokładność, z jaką znajduje się katalog główny i maksymalna dozwolona liczba iteracji używanych do znalezienia katalogu głównego, są ustawiane przez właściwości Maxchange i Maxiterations obiektu Application. Np. wyznaczenie pierwiastka z dokładnością 0,0001 dla maksymalnie 1000 iteracji określa instrukcja:

Z aplikacją

maxiteracje = 1000

Maksymalna zmiana = 0,0001

Wartości te są ustawiane ręcznie na karcie Obliczenia w oknie dialogowym Opcje wywoływanej przez polecenie Narzędzia, Opcje.

Metoda sortowania

Sortowanie umożliwia uporządkowanie danych w porządku leksykograficznym w porządku rosnącym lub malejącym. Metoda sortowania sortuje wiersze listy i bazy danych oraz kolumny arkusza roboczego na podstawie maksymalnie trzech kryteriów sortowania. Ręczne sortowanie danych odbywa się za pomocą polecenia Dane, Sortuj.

Składnia:

Obiekt. Sort(klucz1, zamówienie1, klucz2, zamówienie2, klucz3, zamówienie3, nagłówek, zamówienieCustom, matchCase, orientaticn)

Argumenty:

Obiekt Zakres do posortowania

Zamówienie1

zamówienie2 Określa kolejność zamawiania. Prawidłowe wartości: xlAscending (kolejność rosnąco); xlDescending (kolejność malejąca)

nagłówek Prawidłowe wartości to: xlYes (pierwszy wiersz zakresu zawiera nagłówek, który nie jest posortowany); xlNo (pierwszy wiersz zakresu nie zawiera nagłówka, ta wartość jest uznawana domyślnie); xlGuess (Excel decyduje, czy istnieje nagłówek)

ZamówienieNiestandardowe Niestandardowa kolejność sortowania. Domyślnie jest to normalne

Sprawa meczu Prawidłowe wartości: True (rozróżniana jest wielkość liter) i False (wielkość liter nie jest rozróżniana)

orientacja Prawidłowe wartości: xlTopToBottom (sortowanie odbywa się od góry do dołu, czyli według wierszy); xlLeftToRight (od lewej do prawej, czyli według kolumn)

Na przykład zakres A1:C20 arkusza roboczego 1 jest sortowany za pomocą następującego polecenia w kolejności rosnącej, tak aby sortowanie początkowe znajdowało się w pierwszej kolumnie tego zakresu, a sortowanie drugorzędne znajdowało się w drugiej:

Arkusze("Arkusz").Zakres("A1: C20").Sort _

key1:=Worksheets("Arkusz1").Zakres("A1"), _

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

Zaokrąglanie liczb

Często trzeba zaokrąglać liczby dziesiętne, zwłaszcza podczas pracy z wartościami pieniężnymi. VBA nie oferuje bezpośredniego rozwiązania takich problemów, ale techniki omówione poniżej pomogą Ci rozwiązać te problemy.

1 sposób

Funkcja okrągła

X=okrągły(2,505, 2)

Wartość x wyniesie 2,5, a nie 2,51.

Dlatego nie jest często używany.

2 sposób

Funkcja formatowania

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

3 sposób

Funkcja formatu numeru

SngRounding = Numer Formatu (sbgNiezaokrąglony, 2)

Aby zmienić miejsca dziesiętne, zmień liczbę zer po przecinku w argumencie Format lub zmień liczbę określającą wartość drugiego argumentu na dowolną żądaną.

Notatka. Zmienna, w której umieszczana jest zaokrąglona wartość, musi być typu string, pojedynczy, podwójny, dziesiętny, walutowy lub wariantowy, nie może być liczbą całkowitą ani długą.

Redukcja danych

Aby oddać wprowadzone dane do pożądanego typu, VBA zawiera obszerny zestaw funkcji, z których jedną jest CDBL. Składnia:

CDbl(wyrażenie)

Wymagany argument wyrażenie to dowolny ciąg znaków lub wyrażenie liczbowe. Aby odczytać informacje wpisane w polu tekstowym w wygenerowanym formularzu, wprowadź zmienną i wpisz wyrażenie:

A = Cdbl(pole tekstoweN.tekst)

Następnie możesz pracować z tą zmienną.

Aby wyświetlić wartości bezpośrednio w komórkach skoroszytu Excel, wygodnie jest użyć obiektu Range:

zakres("A5").wartość = a

Funkcją przeciwną do CDbl jest funkcja CStr - konwertuje liczby na ciągi i jest wygodna do wyświetlania wyniku w komórce arkusza lub w określonym polu tekstowym.

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

- odczytanie wartości z komórki i wyświetlenie jej w polu tekstowym.

Funkcja Trim(string) zwraca kopię ciągu z usuniętymi spacjami na początku i końcu ciągu.

Tworzenie programów VBA

Korzystanie z metody GoalSeek

Przykład 41. Opracuj program, który przy użyciu wprowadzonych wartości liczbowych jakiegoś równania rozwiąże to równanie i znajdzie nieznaną zmienną x. Wynik obliczenia jest wyświetlany w polu tekstowym na formularzu oraz w arkuszu Excel.

Ryż. 92. Opracowana forma przykładu 41 w stanie roboczym

Technologia wykonania

1. Uruchom aplikację Excel, zapisz dokument.

2. Przejdź do edytora VBA.

3. Utwórz formularz, jak pokazano na ryc. 92.

4. Umieść wymagany tekst (formatowanie) w arkuszu Excel, podając odpowiednie komórki wyjściowe informacji (ryc. 93).

Ryż. 93. Wyprowadzanie wyników do arkusza Excela po uruchomieniu przykładowego formularza 41

5. Przetwórz przyciski.

przycisk Oblicz

Prywatna Sub CommandButton1_Click()

Dim a, b, c jak podwójne

a = CDbl(PoleTekst1.Tekst)

b = cbl(PoleTekst2.Tekst)

c = CDbl(PoleTekstowe3.Tekst)

Z ActiveSheet

Zakres("b3").Wartość = a

Zakres("b4").Wartość = b

Zakres("b5").Wartość = c

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

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

TextBox4.Text = CStr(.Range("b7").Value)

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

Przycisk zamykania

Prywatny przycisk pomocniczy CommandButton2_Click()

Procedura inicjalizacji formularza

Prywatna Sub UserForm_initialize()

Arkusze (1). Widoczne = Fałsz

Korzystanie z metod automatycznego wypełniania podczas wypełniania tabel

Przykład 42 . Stwórz program, który zgodnie z danymi tekstowymi wprowadzonymi do odpowiednich pól tekstowych formularza automatyzuje wprowadzanie danych dla studentów określonej specjalności instytucji edukacyjnej. Wyniki wypełniania pól tekstowych są wyświetlane na arkuszu Excela, który w razie potrzeby umożliwia wydrukowanie danych.