Moje doświadczenia z Excelem, Accesem i programowaniem w VBA

Wpisy z tagiem: rozwiązania w Excelu

poniedziałek, 28 marca 2011

Poprzednia notka dotyczyła wyliczenia w arkuszu Excel pierwiastków rzeczywistych równania kwadratowego. Wpisane tam formuly działają jeżeli wartość Δ ≥ 0. Dla wartości ujemnych Δ pierwiastki w zbiorze liczb rzeczywistych nie istnieją. Można je za to obliczyć w zbiorze liczb zespolonych. Wprawdzie nie bezpośrednio, ale wystarczy trochę pomyśleć i oszukać Excela.

Załóżmy, że szukamy pierwiastków równania:
-X2 - 2X - 5 = 0
W tym przypadku Δ = -16.
Ponieważ -1= j2 możemy zapisać, że
Δ = 16j2
√Δ = 4j

I dalej już standardowo - zgodnie ze wzorami na X1 i X2.

W arkuszu Excela wygląda to tak:

pierwiastki równania kwadratowego

 Żeby arkusz był pełny - powinien być uniwersalny i w zależności od wartości Δ pokazywać/ukrywać/zmieniać zawartość odpowiednich komórek. Wystarczy wykorzystać tu funkcję Jeżeli ... to.... .

 

wtorek, 22 marca 2011

Równanie stopnia drugiego to stały element wielu zadań matematycznych i to na różnych poziomach edukacji. Czy do ułatwienia sobie życia można tu wykorzystać również Excela? Jak najbardziej, w końcu to arkusz kalkulacyjny.

Najprostszą wersją może byc proste ustawienie kilku formuł w poszczególnych komórkach, tak jak w przykładzie:

pierwiastki równania kwadratowego

W pola zaznaczone kolorem żółtym wpisujemy wartości współczynników trójmianu: a w A1, b w C1 i c w E1. Reszta pół jest wypełniana automatycznie jako wynik działania formuł.
Komórka C4 wylicza wartość Δ równą b2 - 4ac. Wpisana formuła to:
=POTĘGA(C1;2)-4*A1*E1
Komórka C5 to pierwiastek z tej wartości czyli
=PIERWIASTEK(C4)
Komórka C7 wylicza pierwiatek X1 = (- b - √Δ)/2a. W arkuszu wygląda to tak:
=(-C1-C5)/(2*A1)
Analogicznie komórka C9 wylicza pierwiatek X2 = (- b + √Δ)/2a. Czyli
=(-C1+C5)/(2*A1)

I już. Oczywiście jest to najprostszy wzór. Gdy wyróżnik Δ < 0 - formuły pokażą błąd, gdyż w dziedzinie liczb rzeczywistych nie istnieje pierwiastek kwadratowy z liczby ujemnej. Jest to możliwe w zakresie liczb zespolonych. Poza tym dla potrzeb "szkolnych" lepiej byłoby zamiast wyniku 0,866025403... wpisać √3/2.  Jest to możliwe, ale to już tematy kolejnych notek.

 

poniedziałek, 14 lutego 2011

Formatowanie warunkowe jest jednym z ciekawszych narzędzi własnych Excela. Pisałam o tym tu:
Formatowanie warunkowe
Formatowanie warunkowe nieco bardziej zaawansowane
Przydaje się w praktycznym wykorzystaniu, gdy wyróżnienie okreśłonych danych może pomóc w analizie czy choćby uatrakcyjnić wygląd arkusza.
Problem pojawia się wtedy, gdy potrzebujemy więcej warunków formatowania niż ograniczona liczba dostępnych warunków systemowych. W takiej sytuacji możemy wykorzystać swoje własne formatowanie za pomocą kodu VBA.

Dobrym przykładem może być tu miesięczny grafik dyżurów dla 15 osób.

formatowanie arkusza Excel 

W legendzie - czyli komórkach F3 do G17 oznaczeniom L1 do L15 przypisujemy kolory wypełnienia komórki. Formatowanie to automatycznie przenosi się do samego grafika czyli komórek A1 do D32.

Zdefiniujmy najpierw konieczne funkcje i procedury


Public Function Kolorek(Symbol)

Dim Przeglad As Range 
For Each Przeglad In Range("F3:F20")
    If Przeglad.Value = Symbol Then
       Kolorek = Przeglad.Interior.Color
       Exit Function
    End If
Next
Kolorek = 0

End Function

Funkcja Kolorek wyszukuje Symbol (czyli wartość komórki będącej argumentem funkcji) w obszarze Range (w tym przypadku zdefiniowanej komórkami Legendy w arkuszu Excela). Obszar jest przeszukiwany w ramach pętli For Each...Next (zob. Pętla For Each ... Next) i gdy wynik poszukiwania jest pozytywny - funkcja Kolorek przyjmuje wartość numeru koloru tła.

Kolor mamy ustalony, potrzebna jest jeszcze procedura zmieniająca kolory w samym arkuszu.


Public Sub Pokoloruj()

Dim JakiKolor
Dim Komorka As Range
Dim JakiSymbol
 For Each Komorka In Range("B2:F40")
       JakiSymbol = Komorka.Value
       If JakiSymbol <> "" Then
              JakiKolor = Kolorek(JakiSymbol)
              If JakiKolor = 0 Then Komorka.Interior.Pattern = xlNone Else Komorka.Interior.Color = JakiKolor
             
        Else
         Komorka.Interior.Pattern = xlNone
        End If
 Next
 End Sub

Procedura Pokoloruj przechodząc przez poszczególne komórki grafika (czyli zakres B2 do F40), na podstawie ich wartości przypisanej do zmiennej Symbol w fukcji Kolorek, sprawdza i zmienia kolor komórek w grafiku.

Teraz wystarczy tylko stworzyć przycisk uruchamiający makro Pokoloruj. Prosciej jednak byłoby przypisać je do któregoś ze zdarzeń arkusza lub zdarzeń skoroszytu.  
Na przykład tak:

Private Sub Worksheet_Activate()
Pokoloruj
End Sub

 

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column < 8 And Target.Row < 33 Then Pokoloruj

End Sub

Makro działa w tym przypadku przy otwarciu arkusza oraz przy zmianie wartości komórek z określonego zakresu.  

piątek, 06 sierpnia 2010

Od kilku dni debata publiczna dużo dyskutuje się na temat wpływu zwiększonych stawek VAT. O ile więcej zapłacimy za nasze zakupy?
Spróbujmy to wyliczyć, oczywiscie wykorzystując Excela.
Do tego celu wystarczy prosta tabela z odpowiednio wpisanymi formułami.
Jako przykładu użyłam 2 paragonów z moich zakupów.

Excel - wyliczenie VAT 

(Kliknij, aby powiększyć)

 Z samego paragonu przepisałam kwoty brutto i wstawiłam je do kolumny Stara_cena_brutto. Ręcznie wpisałam także stawki VAT: stare (Stara_stawka_VAT) oraz nowe (Nowa_stawka_VAT). W pozostałych polach - są formuly.
Dla kazdego wiersza zapis wygląda tak:

Cena_netto=Stara_cena_brutto / (1+Stara_stawka_VAT)
Stara_kwota_VAT=Cena_netto
* Stara_stawka_VAT
Nowa_kwota_VAT=Cena_netto
* Nowa_stawka_VAT
Nowa_kwota_brutto=Cena_netto 
+ Nowa_kwota_VAT
Różnica= Nowa_kwota_brutto - Stara_kwota_brutto

Ostatni wiersz - to suma wierszy poszczególnych kolumn.

Paradoksalnie - wychodzi na to, że wg nowych stawek VAT - zapłaciłabym mniej. Podobny rezultat osiągnęłam także przy analizie drugiego paragonu.

Excel - wyliczenie VAT

(Kliknij, aby powiększyć)

Dlaczego tak wyszło? To już inna sprawa, nie mająca nic wspólnego z Excelem. Zainteresowanych zapraszam tu:

Zakupy codzienne czyli wyliczamy VAT

A jeżeli ktoś ma ochote na pobranie pliku z tymi wyliczeniami - to jest on tu. Nie zawiera makr.

Pobierz plik VAT.xls

 

środa, 21 lipca 2010

Ostatnio bawiłam się w korektę techniczną kilku prac w Wordzie  i prezentacji PowerPointa. Były tam też wstawiane wykresy. Podstawowa rzecz jak rzuciła mi się w oczy była nieedytowalność tych wykresów. Zasada postępowania była w takcih przypadkach prosta: wykres zrobiony w Excelu, a następnie metodą Kopiuj - Wklej przeniesiony do Worda czy PowerPointa. I wszystko jest fajnie, dopóki arkusz Excela i dane źródłowe, z którego kopiujemy wykres - pozostaje w tym samym miejscu, na tym samym komputerze. Po przeniesieniu na inny komputer - wykres pozostaje praktycznie tylko obrazkiem, w którym ewentualnie można zmienić kolory, danych nie można już edytować.
Miałam kiedyś dyrektora, któremu na narady przygotowywaliśmy prezentacje. Ponieważ nie był tylko biernym klikaczem slajdów - często próbował nanosić własne poprawki i właśnie możliwość edycji była dla niego kluczowym czynnikiem decydującym o jakości prezentacji. Od tamtej pory pozostał mi nawyk - obrazki są obrazkami tylko wtedy, gdy są takimi naprawdę. Wszystko inne to obiekty.

Co więc powinniśmy robić? Zarówno w dokumencie Worda, jak i slajdzie prezentacji wstawiamy obiekt --> wykres. Automatycznie otwiera się dodatkowe okno Excela, do którego wstawiamy dane.

 wstawianie wykresu

kliknij rysunek, aby go powiększyć

W okienku z prawej strony mamy arkusz Excela, w którym albo wpisujemy dane ręcznie, ale kopiujemy je z innego skoroszytu.
Załóżmy, że chcemy wstawić następujące dane:

wstawianie wykresu

kliknij rysunek, aby go powiększyć

Dane wklejamy do tabeli wykresu w Wordzie czy PowerPoincie. W tym konkretnym przypadku konieczna jest dodatkowa zamiana kolumn na wiersze i wierszy na kolumny. Najprościej z zastosowaniem transpozycji tabeli

wstawianie wykresu

kliknij rysunek, aby go powiększyć

 W danych źródłowych są tylko 2 serie danych. Domyślna trzecia seria nie pasuje do wykresu - konieczne jest więc jej usunięcie z obszaru danych wykresu, widocznych w zakresie komórek zaznaczonych niebieską linią. Robimy to przeciągając prawy dolny róg zakresu - miejsce zaznaczone strzałką.

wstawianie wykresu

 Teraz pozostaje już tylko ewentualnie zmienić kolory czy inne ustawienia wykresu, a następnie zamknąć Excela i obejrzeć końcowy.

wstawianie wykresu

kliknij rysunek, aby go powiększyć

 Możemy kontynuować naszą w aplikacji docelowej. W każdej jednak chwili, nawet na innym komputerze - możemy wykres edytować. Wystarczy kliknąć na nim prawym przyciskiem myszy, otwierając w ten sposób menu podręczne i wybrać opcję Edytuj dane...

wstawianie wykresu

kliknij rysunek, aby go powiększyć

 Takie wstawianie wykresów Excela do innych aplikacji z pewnością jest bardziej efektywne i w przypadku poprawek - oszczędza nam sporo czasu i trudów.

| < Wrzesień 2017 > |
Pn Wt Śr Cz Pt So N
        1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30  


Książki warte polecenia
zobacz szczegóły...


A tu oferta na dziś:





ministat liczniki.org



Napisz do mnie!