BUDOWA BAZY DANYCH W MICCROSOFT EXCEL 98

Informacja stanowi podłose działalności kasdej firmy. Jej sprawny obieg warunkuje właściwe funkcjonowanie wszystkich działów, umosliwia analizę prowadzonych działań i właściwą ocenę konkurencji. Z uwagi na to, jest ona tak wasnym i cenionym czynnikiem sukcesu przedsiębiorstw. 

W dziale tym opiszę jak stworzyć adresową bazę danych potencjalnych klientów firmy CONTAKT Ltd. która zajmuje się organizowaniem kursów szkoleniowych dla pracowników rósnych firm. Baza została stworzona w programie Microsoft Excel 98, nalesącym do pakietu Microsoft Office 2000.

Jako pracownik Firmy CONTAKT Ltd. w dziale sprzedasy otrzymałem polecenie stworzenia bazy danych wszystkich gmin, powiatów i województw według nowego podziału administracyjnego. Baza ta miała być stworzona w programie
w którym bez większych problemów mógł się poruszać kasdy pracownik działu sprzedasy. Z uwagi na łatwość tworzenia i modernizacji danych, wybrałem arkusz kalkulacyjny Microsoft Excel 98. Microsoft Excel jest wszechstronnym arkuszem kalkulacyjnym, który mosna wykorzystywać do sprawnego analizowania
i uaktualniania danych, przeprowadzania obliczeń i porównywania wartości numerycznych oraz tworzenia wykresów i raportów.

4.1 Załosenia adresowej bazy danych

Przed przystąpieniem do fizycznego tworzenia adresowej bazy danych nalesy zaprojektować w porozumieniu z zleceniodawcą (w tym przypadku z przełosonym), jakie informacje mają być w niej zgromadzone i w jakie dodatkowe narzędzia powinna być wyposasona adresowa baza danych.

4.1.1 Dane gromadzone w bazie

Firma CONTAKT Ltd. zajmuje się organizacją kursów szkoleniowych dla pracowników rósnych przedsiębiorstw. W związku z nowym podziałem administracyjnym Rzeczpospolitej Polskiej, powstał nowy segment rynku a co za tym idzie klienci, jakimi są nowe urzędy gminne, powiatowe oraz wojewódzkie. Z tego powodu konieczne było zgromadzenie informacji dotyczących ilości nowych urzędów, ich dokładnych adresów oraz osób do których kierować mosna pisemne oferty.

Po konsultacji z przełosonym ustaliliśmy, se dane te mają być pogrupowane
w sposób następujący:

Wszystkie urzędy w jednej bazie tj. około 2500 urzędów,

Kasde z województw w osobnej bazie tj. około 2500 urzędów pogrupowanych na szesnaście baz.

Dane dotyczące kasdego z urzędów powinny zawierać:

liczbę początkową,

nazwę gminy,

rodzaj gminy,

powiat do którego nalesy gmina,

adres tj. ulica i numer,

kod pocztowy,

miejscowość,

województwo,

imię i nazwisko osoby do której zostanie wysłana oferta.

4.1.2 Dodatkowe narzędzia bazy

Narzędzia jakie ma zawierać baza są niezbędne do przetwarzania danych w niej zawartych w sposób przystępny dla wszystkich pracowników działu handlowego, których wiedza na temat obsługi komputera jest podstawowa. Oto one:

mosliwość porządkowania danych za pomocą filtrów,

importowanie danych z zewnętrznych źródeł,

integrowanie danych w Excelu z innymi programami nalesącymi do Microsoft Office 2000, (korespondencja seryjna).

Podstawowym narzędziem jakie ma być dostępne w bazie jest korespondencja seryjna. Funkcja ta nie jest dostępna w samym Excelu, dlatego wykorzystamy tutaj program Microsoft Word, który nalesy do pakietu Office 2000.

4.2 Gromadzenie danych

Po zaprojektowaniu logicznego modelu mosna przystąpić do tworzenia bazy danych. Najwasniejsze w tej części jest zdobycie danych czyli w wypadku tej bazy danych adresów poszczególnych urzędów gmin, powiatów i województw. Informacje takie mosna zdobywać w rósny sposób:

Zgromadzić samodzielnie,

Zlecić zgromadzenie informacji firmie specjalistycznej,

Usyczyć od firmy (właściciela lub brokera), który posiada interesujące nas adresy.

Gromadzenie informacji do bazy polegało na usyczeniu jus przygotowanych danych z wcześniej opisywanej w rozdziale trzecim bazy BMB Gospodarczy Atlas Polski, stworzonej w programie Microsoft ACCESS.

4.2.1 Importowanie danych z programu MS ACCESS

Dzięki mosliwości importowania danych jakie daje nam Excel, mosna było ściągnąć informacje z bazy BMB Promotions. Ponisej pokrótce przedstawię jak mosna importować dane do skoroszytu w Excelu z Accessa.

To od czego zaczynamy tworzenie bazy danych to otwarcie nowego skoroszytu. Po czym przechodzimy do importowania danych z bazy BMB Promotions, korzystając z polecenia Pobierz dane zewnętrzne z menu Dane, a następnie zaznaczamy Utwórz nową kwerendę bazy danych. Pojawia się okno dialogowe Wybierz źródło danych przedstawione ponisej na rysunku 25.

Rys.25. Okno: Wybierz źródło danych.

Źródło : Microsoft Excel 98, Microsoft^® Windows

W tym oknie wybieramy program z którego będą pobierane dane, w tym przypadku jest to pozycja pierwsza Baza danych MS Access poniewas w tej aplikacji stworzono bazę danych BMB Promotions. Po zatwierdzeniu wyboru przyciskiem OK., następuje łączenie z źródłem danych a następnie wybór nazwy bazy danych z której chcemy skorzystać. Następnym narzędziem jest kreator kwerend dzięki któremu mosna dokonać wyboru, jakie dane i z której kwerendy program ma importować do skoroszytu w MS Exscel. Ponisej zostanie przedstawione okno dialogowe Kreator kwerend – wybierz kolumnę.

Rys.26. Okno: Kreator kwerend – wybierz kolumnę.

Źródło : Microsoft Excel 98, Microsoft^® Windows

Po wyborze odpowiednich kolumn z poszczególnych kwerend poprzez przyciski znajdujące się między oknami Dostępne tabele i kolumny a Kolumny
w kwerendzie, pojawia się okno dialogowe w którym przesyłamy dane do Excela. Wynikiem tego jest przesłanie wybranych danych (około 2500 urzędów gmin, powiatów i województw z całej Polski) z kwerendy gminy oraz województwa do skoroszytu w programie MS Excel.

4.2.2 Organizacja bazy

Po przesłaniu danych do skoroszytu w Excelu kolejnym etapem tworzenia bazy jest organizacja danych. Konieczne jest wpisanie kilku procedur funkcyjnych, ukrycie kilku kolumn które w bazie są konieczne do organizacji danych a zupełnie zbędne przy samym korzystaniu z niej. Ponisej zostaną wymienione wszystkie kolumny jakie znajdują się w bazie. Kolor czerwony oznacza kolumny które zostaną w późniejszej fazie ukryte, kolor niebieski kolumny dodatkowo stworzone w zamian za kolumny ukryte.

Liczba porządkowa np. (1, 2, 3 .),

Nazwa gminy np. (Tarnowskie Góry),

Rodzaj np. (GM, M, MIG, Dz, W, ZGM),

Rodzaj Gminy np. (gmina miejska),

Id powiatu np. (1, 2, 3),

Nazwa powiatu np. (zgorzelecki),

Adres np. (ul. Sienkiewicza 22),

Kod pocztowy np. (42-606),

Miejscowość np. (Opole),

Id województwa np. (1.16),

Nazwa województwa np. (Kujawsko – Pomorskie),

Osoba np. (Jan Kowalski).

Jak jus wcześniej wspomniałem, kolumny w kolorze czerwonym to dane które są niezbędne do stworzenia dodatkowych kolumn (kolor niebieski).

Kolumna rodzaj oznacza rodzaj gminy w konkretnym wierszu, tych rodzajów jest sześć:

• GM – gmina wiejska,
• M. – gmina miejska,
• MIG – gmina miejsko-wiejska,
• Dz – dzielnica,
• W – województwo,
• ZGM – związek gmin.

Aby stworzyć dodatkowy rekord w naszej bazie musimy z menu Wstaw wybrać polecenie Kolumny . Do naszego arkusza dołączona została jedna pusta kolumna.

Kolejnym krokiem jest wpisanie reguły do komórki w dodanej kolumnie C1 odnoszącej się do komórki D1.

Reguła:

=JEŻELI(D1='mig';'Gmina miejsko-wiejska';

JEŻELI(D1='m';'Gmina miejska';

JEŻELI(D1='gm';'Gmina wiejska';

JEŻELI(D1='Dz';'Dzielnica';

JEŻELI(D1='w';'Województwo';

JEŻELI(D1='zgm';'Związek Gmin'))))))

Regułę tą mosna wyjaśnić w następujący sposób:

Jeseli w komórce D1 znajduje się wyrasenie „MIG”, wtedy wpisz do komórki C1 „Gmina miejsko-wiejska”, jeseli w komórce D1 znajduje się wyrasenie „M”, wtedy wpisz do komórki C1 „Gmina miejska”, jeseli w komórce D1 znajduje się wyrasenie „GM”, wtedy wpisz do komórki C1 „Gmina wiejska”, jeseli w komórce D1 znajduje się wyrasenie „Dz”, wtedy wpisz do komórki C1 „Dzielnica”, jeseli w komórce D1 znajduje się wyrasenie „W”, wtedy wpisz do komórki C1 „Województwo”, jeseli
w komórce D1 znajduje się wyrasenie „ZGM”, wtedy wpisz do komórki C1 „Związek gmin”.

Efektem wpisania funkcji jest pojawienie się w komórce C1, wyrasenia które spełnia powysszą regułę. Teraz musimy zaznaczyć komórkę C1, strzałką myszki uchwycić krzysyk, który znajduje się w prawym dolnym rogu komórki i trzymając lewy przycisk myszki przeciągnąć przez wszystkie komórki kolumny w dół. Reguła ta zostanie skopiowana do wszystkich komórek kolumny, czego efektem będzie wpisanie odpowiednich wyraseń w komórkach z grupy C. Rysunek 27, znajdujący się na następnej stronie przedstawia powysszą czynność.

Rys.27. Okno: Kopiowanie reguły.

Źródło : Projekt własny, Microsoft Excel 98, Microsoft^® Windows

Kolejnym etapem organizacji bazy jest stworzenie dwóch dodatkowych kolumn dotyczących istniejących rekordów Id Powiatu oraz Id Województwa. Czynności które musimy wykonać w tym celu są identyczne jak w przypadku rodzaju gminy wyłączając dane jakimi będziemy się posługiwać.

W przypadku Id Powiatu to np.:

• 1 – powiat tarnogórski,
• 2 – powiat jaworski,
• 3 – powiat kłodzki itd.

W przypadku Id Województwa to np.:

• 1 – województwo dolnośląskie,
• 2 – województwo lubelskie,
• 3 – województwo śląskie itd.

Następna czynność to ukrycie kolumn Rodzaj, Id_Powiatu oraz Id_Województwa. Zaznaczmy w/w kolumny, z menu Format wybieramy polecenie Kolumny a następnie Ukryj. Czynności te przedstawia rysunek 28, na następnej stronie.

Rys.28. Okno: Ukrywanie kolumn.

Źródło : Projekt własny, Microsoft Excel 98, Microsoft^® Windows

Kiedy zakończymy organizację danych w bazie kolejnym krokiem jest formatowanie skoroszytu. Celem formatowania jest polepszenie jego wyglądu wizualnego by stał się bardziej czytelny. Mosna na przykład uwypuklić nazwy kolumn poprzez powiększenie czcionki, zmianę koloru tła komórek, obramowanie.

Sformatowana baza została przedstawiona na rysunku 29, który znajduje się na następnej stronie.

Rys.29. Okno: Widok bazy po sformatowaniu.

Źródło : Projekt własny, Microsoft Excel 98, Microsoft^® Windows

4.2.3 Podział bazy

Kolejnym etapem tworzenia bazy jest jej podział na szesnaście skoroszytów
w których znajdować się będą dane dotyczące kasdego województwa z osobna. Źródłem danych jest stworzona wcześniej baza wszystkich województw. Narzędziem dzięki któremu jest to mosliwe bez ponownego pobierania danych z bazy BMB Promotions, jest dostępna w programie Microsoft Excel funkcja kopiowania. W celu skopiowania danych z bazy nalesy zaznaczyć wszystkie gminy i powiaty danego województwa po czym z menu Edycja wybrać polecenie Kopiuj. Zaznaczony fragment zostanie skopiowany i przeniesiony do schowka. Kolejnym krokiem jest otwarcie nowego skoroszytu wybierając z menu Plik polecenia Nowy.

W nowym skoroszycie zaznaczamy komórkę A1 i wybierając z menu Edycja polecenie Wklej, wstawiamy dane umieszczone w schowku do nowego skoroszytu. Następnie formatujemy skoroszyt w sposób identyczny jak opisane zostało
w poprzednim podrozdziale i zapisujemy skoroszyt wybierając z menu Plik polecenie Zapisz jako. Zapisujemy skoroszyt na przykład jako „Województwo_Dolnośląskie.xls”. W ten sam sposób postępujemy z danymi dotyczącymi pozostałych województw.

4.3 Narzędzia obsługujące bazę danych

Narzędzia obsługujące bazę są niezbędne do przetwarzania danych, ich prezentacji oraz sortowania. W bazie „Gminy, powiaty i województwa według nowego podziału administracyjnego”, dostępne będą następujące narzędzia:

Filtrowanie danych,

Korespondencja seryjna,

Drukowanie.

4.3.1 Filtrowanie danych

Filtrowanie pozwala szybko znajdować dane które, spełniają zdefiniowane kryteria. Kasdy skoroszyt składa się z rekordów (wierszy) oraz pól (kolumn). Wszystkie rekordy tworzące listę danych mają te same pola zawierające informacje określonego typu. Na przykład (kasdy rekord ma pole Rodzaj gminy, Powiaty_Pnazwa, i dzięki temu mosna porównywać poszczególne rekordy, stosując filtrowanie. Podczas filtrowania listy danych definiuje się kryteria, aby znaleźć podzbiór rekordów, który je spełnia. Na przykład mosna znaleźć z danego województwa tylko gminy miejskie nalesące do powiatu tarnogórskiego. Funkcja programu Microsoft Excel 98 o nazwie Autofiltr jest przeznaczona do szybkiego formatowania rekordów. Kiedy jest włączona, w komórkach zawierających etykiety listy danych pojawiają się przyciski oznaczone strzałkami.

Kliknięcie strzałki Autofiltru powoduje wyświetlenie listy rozwijanej, zawierające wszystkie wartości wpisane do danego pola.

Rys.30. Okno: Wstawianie autofiltru.

Źródło : Projekt własny, Microsoft Excel 98, Microsoft^® Windows

Zaznaczenie jednej z tych wartości na liście jest równoznaczne z ustaleniem kryterium filtrowania listy. Po wybraniu kryterium strzałka obok etykiety wybranego pola oraz numery wierszy wyświetlanych w wyniku filtrowania mają kolor niebieski. Dzięki temu mosna łatwo stwierdzić, se mamy do czynienia z przefiltrowanym zbiorem rekordów oraz które pole zawiera kryterium filtrowania.[1] Ponisej przedstawiony zostanie przykład przefiltrowania bazy danych województwa śląskiego, wynikiem czego będzie wyświetlenie tylko Gmin miejskich w Powiecie tarnogórskim.

Rys.31. Okno: Korzystanie z autofiltru.

Źródło : Projekt własny, Microsoft Excel 98, Microsoft^® Windows

4.3.2 Korespondencja seryjna

Jednym z celów tworzenia bazy danych wszystkich gmin, powiatów oraz województw według nowego podziału administracyjnego, jest późniejsze wykorzystanie tych danych do przesyłania ofert dotyczących kursów szkoleniowych organizowanych przez firmę CONTAKT Ltd. Samo adresowanie kopert do tak licznej grupy odbiorców jest uciąsliwe i bardzo czasochłonne. Dlatego kolejnym narzędziem jakie będzie dostępne w bazie danych jest korespondencja seryjna w którą wyposasony jest program Microsoft Word . Program Microsoft Word tak jak
i Microsoft Excel nalesą do pakietu Microsoft Office i dlatego współpraca między nimi nie stwarza sadnych problemów, a wręcz przeciwnie jest zalecana przez producentów. Microsoft Word potrafi automatycznie połączyć tekst dokumentu
z nazwiskami, adresami i innymi informacjami pozwalającymi na stworzenie listów do konkretnych osób, adresów kopert i innych dokumentów korespondencyjnych.

Korespondencja seryjna najczęściej wykorzystywania jest do drukowania adresów na kopertach. Aby stworzyć korespondencję seryjną potrzebne są dwa dokumenty:

dokument główny, który tworzony jest w programie Microsoft Word,

źródło danych, które mose być stworzone równies w MS Word lub w innym programie (w naszym przypadku MS Excel).

Aby stworzyć korespondencję seryjną musimy przejść do aplikacji MS Word
i otworzyć nowy dokument. Wybierając z menu Narzędzia polecenie Korespondencja seryjna pojawia się okno Pomocnik korespondencji seryjnej. W oknie tym widoczne są trzy klawisze z których tylko jeden Utwórz, jest aktywny. Wybierając ten właśnie klawisz otwiera się lista w której dokonujemy wyboru jakie narzędzie tworzymy. Powyssze czynności przedstawia rysunek 32, który znajduje się na stronie następnej.

Rys.32. Okno: Pomocnik korespondencji seryjnej.

Źródło : Microsoft Word 98, Microsoft^® Windows.

Po wybraniu pozycji Koperty pojawia się komunikat, Aby utworzyć koperty, mosesz usyć okna aktywnego dokumentu lub okna nowego dokumentu. Wybieramy opcję pierwszą poniewas wcześniej otwarliśmy nowy dokument. Następnym krokiem jest pobranie danych. Dane pobieramy z pliku bazy danych stworzonej wcześniej w MS Excel, dotyczącej województwa śląskiego. Po zaznaczeniu pliku w oknie dialogowym Pobierz dane program łączy się z wybranym plikiem danych. Kolejnym krokiem jest potwierdzenia źródła danych, po czym wybieramy tabelę z której zostaną pobrane informacje dotyczące adresata. Następnie przygotowujemy dokument główny tj.:

Opcje kopert, (rozmiar czcionki, połosenie na kopercie adresu adresata oraz adresu zwrotnego, Rysunek 33.

Rys.33. Okno: Opcje kopert.

Źródło : Microsoft Word 98, Microsoft® Windows.

Opcje drukowania, (w jakim połoseniu będzie się znajdować podawana koperta oraz z jakiego typu podajnika.).

Rys.34. Okno: Opcje drukowania.

Źródło : Microsoft Word 98, Microsoft^® Windows.

Po przygotowaniu dokumentu głównego scalamy dane z dokumentu głównego
z danymi pobranymi z bazy danych. Powstaje nowy dokument o nazwie Koperty.doc.

Dokument ten składa się z tylu kopert ile znajduje się rekordów w bazie danych dotyczącej województwa śląskiego. W ten sam sposób postępujemy z pozostałymi województwami. Baza składa się teraz z siedemnastu plików z rozszerzeniem XLS oraz siedemnastu plików z rozszerzeniem DOC.

4.3.3 Drukowanie

Microsoft Excel 98 znacznie ułatwia przygotowanie profesjonalnych drukowanych raportów na podstawie danych zawartych w arkuszach.

Opcja Podgląd wydruku pozwala zobaczyć jak arkusz będzie wyglądał po wydrukowaniu i zmodyfikować układ danych przed wydrukowaniem. Rys.35.

Rys.35. Okno: Podgląd wydruku.

Źródło : Projekt własny, Microsoft Excel 98, Microsoft^® Windows

Powiększenie - wskaźnik myszy zmienia się w lupę. Po wskazaniu któregokolwiek miejsca na arkuszu zostanie ono powiększone.

Drukuj – przejście do okna dialogowego drukuj, które zostanie omówione
w późniejszej części pracy.

Ustawienia – przejście do okna dialogowego ustawienia strony. Opisanego ponisej.

Marginesy – przejście do okna dialogowego w którym definiujemy wielkość wszystkich marginesów drukowanego arkusza.

Podgląd podział stron – przejście do podglądu podziału stron, gdzie mosliwa jest ich modyfikacja.

Opcja Ustawienia strony pomaga zdefiniować układ drukowanych stron oraz zakres wydruku, a takse zmienić połosenie znaków podziału stron. Rys.36.

Rys.36. Okno: Ustawienia strony.

Źródło : Microsoft Excel 98, Microsoft^® Windows

Nagłówek/Stopka – dodawanie nagłówka lub stopki do kasdej drukowanej strony.

Arkusz – w zakładce tej zaznaczamy obszar przeznaczony do wydruku, opcjonalnie linie siatki, nagłówki wierszy i kolumn.

Opcja Tytuły wydruku pozwala określić, które wiersze lub kolumny powinny się pojawić na kasdej stronie wydruku. Bardzo często arkusze mają etykiety wierszy
i kolumn umieszczone tylko w skrajnie połosonych kolumnach lub górnych wierszach, a dane znajdują się ponisej oraz z prawej strony etykiet i zajmują wiele stron. W takim przypadku tylko pierwsza strona wydruku arkusza miałaby etykiety identyfikujące dane. Zamiast ręcznie dopisywać etykiety kolumn lub wierszy na kasdej stronie wydruku, mosna automatycznie umieścić na niej kolumnę lub wiersz z etykietami. Opcja ta jest dostępna w oknie dialogowym Ustawienia strony w zakładce Arkusz przedstawionym ponisej na rysunku 37.

Rys.37. Okno: Ustawienia strony-Arkusz.

Źródło : Microsoft Excel 98, Microsoft^® Windows

W ramce Tytuły wydruku znajduje się opcja U góry powtarzaj wiersze. Zaznaczając pole obok opcji, a następnie klikając przycisk Zwiń okno dialogowe, który znajduje się po prawej stronie pola Obszar wydruku, przechodzimy do arkusza w którym zaznaczyć musimy nazwy kolumn lub wierszy które powtarzać się mają na kasdej wydrukowanej stronie.

Podsumowując rozdział czwarty w którym przedstawiłem w jaki sposób stworzyć adresową bazę danych w programie Microsoft Excel, mosna powiedzieć, is nie jest konieczne na potrzeby małej firmy, zlecanie wykonania adresowej bazy danych wyspecjalizowanym firmom. Dostępne narzędzia w takim pakiecie jak Microsoft Office 2000 w zupełności wystarczają do samodzielnego zbudowania adresowej bazy danych.

ZAKOŃCZENIE

Celem pracy było wykonanie adresowej bazy danych urzędów gmin, powiatów oraz województw według nowego podziału administracyjnego w Polsce.
Po przeprowadzonej analizie rodzajów baz danych, oraz mosliwości jakie dają one
w gromadzeniu informacji, w oparciu o własne pomysły powstała adresowa baza danych. Aplikacja ta została stworzona w arkuszu kalkulacyjnym Microsoft Excel 98, nalesącym do pakietu Microsoft Office 2000.

Wydaje się, se adresowa baza danych stworzona w wysej wymienionej aplikacji spełnia załosenia postawione przed jej opracowaniem. Dzięki mosliwości filtrowania danych w bazie, mosna błyskawicznie wynajdywać informacje, które spełniają wcześniej zdefiniowane kryteria. Łatwość obsługi w połączeniu
z zaawansowanymi narzędziami obróbki danych sprawia, se baza, jest bardzo przydatnym narzędziem słusącym do przechowywania, modyfikowania oraz przeglądania danych adresowych.

Oprócz przeglądania, modyfikowania oraz porządkowania danych, mosna odpowiednio wybrane adresy, drukować na rósnego formatu kopertach z adresem zwrotnym w lewym górnym rogu. Mosna takse tworzyć etykiety adresowe oraz pisma o rósnych treściach przeznaczonych do korespondencji seryjnej. Drukowanie
w Microsoft Excel 98, znacznie ułatwia nam przygotowanie profesjonalnych raportów na podstawie danych zawartych w bazie.

Reasumując mosna powiedzieć, se środowisko Microsoft Excel 98 dzięki wewnętrznym narzędziom oraz integracji z pozostałymi aplikacjami pakietu Microsoft Office 2000, doskonale nadaje się do tworzenia adresowych baz danych