Microsoft Excel 2013 – poziom średniozaawansowany

Czas trwania –3 dni – 12 godzin zegarowych

Cel szkolenia

Szkolenie ma na celu przekazanie wiedzy na temat najczęściej wykorzystywanych funkcji programu

Microsoft Excel oraz zapozna uczestników z ich praktycznym zastosowaniem. Uczestnicy szkolenia

zdobędą umiejętność edycji i formatowania danych, zapoznają się z możliwościami

wykorzystywania odwołań, oraz funkcji. Dzięki ćwiczeniom przygotowanym przez trenera każdy

uczestnik będzie umiał sortować i filtrować dane, oraz nauczy się automatyzować pracę w arkuszu

kalkulacyjnym MS Excel.

Wymagania od uczestników szkolenia

Uczestnik szkolenie na poziomie średnim powinien:

sprawnie poruszać się między arkuszami

zaznaczać wiersze, kolumny, komórki

wstawiać i usuwać wiersze, kolumny i komórki

znać okienko Formatuj komórki i potrafić korzystać z niego w podstawowym zakresie

sprawnie wprowadzać dane

rozróżniać typy danych (liczby, daty, teksty)

potrafić budować podstawowe formuły z operatorami matematycznymi (+, -, *,/)

potrafić korzystać z adresów komórek budując formuły

znać podstawowe funkcje Excela (SUMA, ŚREDNIA, MAX itp) i potrafić z nich korzystać

umieć korzystać z filtrów i sortowania w podstawowym zakresie

Zakres merytoryczny szkolenia

Realizując szkolenia w formule zamkniętej program szkolenia zostanie dostosowany do wymagań i potrzeb uczestników na podstawie wnikliwej analizy potrzeb szkoleniowych. Moduł I – 30 min

Wprowadzenie - przegląd możliwości aplikacji, organizacja programu, organizacja dokumentu – skoroszytu. Terminologia i środowisko programu Microsoft Excel – wykład wprowadzający, prezentacja Modłuł II - 60 min

Budowanie, modyfikacja, formatowanie arkuszy - operacje na komórkach ćwiczenia, wykład

Budowanie i modyfikowanie bazy danych, poprawne wpisywanie danych, wybór danych za pomocą kryteriów - ćwiczenia Moduł III – 120 min

Operacje na zakresach danych - ćwiczenia

Korzystanie z odwołań względnych, bezwzględnych i mieszanych - ćwiczenia, prezentacja Moduł IV – 210 min

Wykorzystanie funkcji Excela - ćwiczenia Moduł V – 90 min

Tworzenie formularzy elektronicznych, wykorzystywanie formularzy do typowych zastosowań. – ćwiczenia Moduł VI – 150 min

Tabele przestawne – wprowadzenie – ćwiczenia Moduł VII –60 min

Organizacja pracy z programem Excel: jednoczesna praca wielu użytkowników na jednym arkuszu, łączenie arkuszy i skoroszytów, scalanie danych z wielu arkuszy.- ćwiczenia, prezentacja, wykład Szczegółowy program szkolenia

Sortowanie

Szybkie sortowanie

Sortowanie według kilku kryteriów

Sortowanie według kolorów komórki i czcionki

Filtrowanie

Włączanie i wyłączanie filtra

Filtrowanie tekstu

Filtrowanie liczb

Filtrowanie według kolorów

Formatowanie warunkowe

Reguły wyróżniania komórek

Reguły pierwszych/ostatnich

Paski danych i skale kolorów

Graficzna prezentacja danych - wprowadzenie

Przygotowanie danych do wykresów

Wstawianie wykresów

Funkcje do analizy danych

Funkcje zaokrąglania

Funkcja SUMA.JEŻELI

Funkcja SUMA.WARUNKÓW

Funkcja LICZ.JEŻELI

Funkcja LICZ.WARUNKI

Funkcje LICZ.PUSTE i ILE.NIEPUSTYCH

Funkcje do pracy z tekstem

Funkcje LEWY i PRAWY

Funkcja FRAGMENT.TEKSTU

Funkcja ZŁĄCZ.TEKSTY

Funkcja USUŃ.ZBĘDNE.ODSTĘPY

Zagnieżdżanie funkcji

Funkcje do tworzenia inteligentnych formuł

Funkcja JEŻELI

Funkcja JEŻELI.BŁĄD

Funkcje do wyszukiwania wartości

Funkcja WYSZUKAJ.PIONOWO

Funkcja WYSZUKAJ.POZIOMO

Funkcje do pracy z datami i czasem

Funkcja DATA

Funkcje ROK, MIESIĄC i DZIEŃ

Funkcja DZIŚ

Funkcja DZIEŃ.TYG

Funkcja DNI.ROBOCZE

Analiza danych przy pomocy tabel przestawnych

Wstawianie tabeli przestawnej

Zmienianie obliczeń wykonywanych w tabeli przestawnej

Formatowanie tabeli przestawnej

Filtrowanie i sortowanie w tabeli przestawnej

Wykorzystanie fragmentatorów do filtrowania tabeli przestawnej

Skróty klawiaturowe

Ochrona arkuszy

Korzyści wynikające z ukończenia szkolenia

Uczestnictwo w szkoleniu daje praktyczne umiejętności wykorzystania arkusza kalkulacyjnego MS

Excel w codziennej pracy.

Po ukończeniu szkolenia uczestnik będzie potrafił:

Wydajnie pracować z arkuszem kalkulacyjnym.

Prawidłowo wprowadzać dane do arkusza, tworzyć i modyfikować bazy i formularze

Wykonywać operacje na komórkach

Sprawnie korzystać z funkcji

Dostosować środowisko arkusza MS Excel do indywidualnych potrzeb

Metodologia

Szkolenia prowadzone są nowoczesnymi, interaktywnymi i aktywizującymi metodami, przy pełnym

i aktywnym zaangażowaniu uczestników. Wszystkie ćwiczenia prowadzone są na podstawie

przykładów zaczerpniętych z praktyki uczestników i trenera (case studies). Zajęcia, pomimo

prezentowania złożonej wiedzy merytorycznej, prowadzone są w zrozumiały sposób, zapewniający

przyswojenie i przećwiczenie przekazywanej wiedzy przez każdego uczestnika szkolenia oraz

wspólne rozwiązanie napotykanych problemów.

Microsoft Excel 2013 poziom zaawansowany

Czas trwania – 3 dni – 12 godzin zegarowych

Cel szkolenia

Szkolenie ma na celu przekazanie zaawansowanej wiedzy na temat wykorzystania arkusza kalkulacyjnego MS Excel w codziennej pracy. Podczas szkolenia uczestnicy zdobędą wiedzę na temat zaawansowanych funkcji aplikacji m.in. umiejętność zaawansowanej obróbki danych w arkuszu, generowania wykresów. Będą potrafili pracować z wieloma arkuszami i tworzyć zaawansowane formuły. Dzięki praktycznym ćwiczeniom uczestnicy naucza się samodzielnie analizować dane przy pomocy tabel przestawnych oraz wykorzystywać inne źródła danych. Ćwiczenia zapewnią trwałe podniesienie wydajności pracy dzięki automatyzacji i wykorzystaniu zaawansowanych funkcji MS Excel.

Wymagania od uczestników szkolenia

Kandydat na szkolenie zaawansowane powinien znać zagadnienia wymienione w wymaganiach do

poziomu średniozaawansowanego oraz:

potrafić budować proste tabele przestawne wiedzieć co to jest import danych potrafić tworzyć podstawowe wykresy i wprowadzać do nich proste modyfikacje potrafić korzystać z co najmniej 20 z wymienionych funkcji:

ADR.POŚR DZIEŃ.ROBOCZY LICZ.PUSTE ORAZ ŚREDNIA

AGREGUJ DZIEŃ.TYG LICZ.WARUNKI PIONOWO TEKST

ARABSKIE DZIŚ LICZBA.KOLUMN PODAJ.POZYCJĘ TRANSPONUJ

BRAK FRAGMENT.TEKSTU LITERY.MAŁE PODSTAW USUŃ.ZBĘDNE.ODSTĘPY

CZAS FRAGMENT.TEKSTU,

ZNAJDŹ LOS PORÓWNAJ WIERSZ

CZĘSTOŚĆ HIPERŁĄCZE LOS.ZAKR POWT WYBIERZ

CZY.BŁĄD ILE.LICZB LUB POZYCJA WYST.NAJCZĘŚCIEJ

CZY.FORMUŁA ILE.NIEPUSTYCH MAX POZYCJĘ WYSZUKAJ

CZY.LICZBA ILE.WIERSZY MAX.K PRAWY WYSZUKAJ.PIONOWO

CZY.NIEPARZYSTE ILOCZYN MIESIĄC PRZESUNIĘCIE WYSZUKAJ.POZIOMO

CZY.PARZYSTE ILOCZYNÓW MIN RANDBETWEEN ZAOKR

CZY.PUSTA INDEKS MIN.K ROK ZAOKR.DO.CAŁK

DATA JEŻELI MOD SUMA ZAOKR.DO.WIELOKR

DATA.RÓŻNICA JEŻELI.BŁĄD MODUŁ.LICZBY SUMA.ILOCZYNÓW ZAOKR.GÓRA

DŁ KOD NIE SUMA.JEŻELI ZAOKR.W.GÓRĘ

DNI.ROBOCZE KOMÓRKA NR.KOLUMNY SUMA.WARUNKÓW ZNAJDŹ

DNI.ROBOCZE.

NIESTAND LEWY NR.SER.OST.DN.MIES SUMY.CZĘŚCIOWE ZNAK

DZIEŃ LICZ.JEŻELI OCZYŚĆ SZUKAJ.TEKST

Zakres merytoryczny szkolenia

Moduł I – 90 min

Dane zewnętrzne, typy danych, źródła danych, budowanie kwerend, zapisywanie i edycja kwerend, analiza danych zewnętrznych

Technologie pobierania danych ze źródeł zewnętrznych. Import i analiza danych z plików tekstowych do Excela. Moduł II – 100 min

Narzędzia filtrowania, sortowania, sum pośrednich w analizie danych. Elementy formatowania warunkowego. Sprawdzanie poprawności danych (walidacja danych) Moduł III – 130 min

Tworzenie i modyfikowanie tabel przestawnych w oparciu o różne źródła danych,

Stosowanie tabel przestawnych jako narzędzia analizy danych biznesowych

Formatowanie warunkowe w tabeli przestawnej Moduł IV – 60 min

Formatowanie warunkowe, warianty zastosowań Moduł V – 60 min

Prezentacje danych - wykresy

Funkcje w arkuszu MS Excel Moduł VI – 180 min

Funkcje macierzowe, przykłady zastosowań

Funkcje bazodanowe, przykłady zastosowań praktycznych, dobór kryteriów, alternatywa i koniunkcja kryteriów

Automatyzacja obliczeń za pomocą funkcji budowanych przez użytkownika, Moduł VII– 100 min

Optymalizacja rozwiązań złożonych problemów za pomocą dodatku Solver

Szczegółowy program

Importowanie danych

Z baz danych (Access)

Z arkuszy kalkulacyjnych (Excel, Google Docs)

Z plików tekstowych (Word, txt, csv)

Z Internetu

Korzystanie z dodatku Power Query

Zaawansowane techniki w pracy z listami

Formatowanie warunkowe z wykorzystaniem formuł

Sortowanie według kolejności listy niestandardowej

Filtr zaawansowany

Tworzenie formatów niestandardowych

Grupowanie

Tabele

Wiele filtrowanych list w jednym arkuszu

Filtrowanie tabel przy pomocy fragmentatorów

Tworzenie formuł wykorzystujących odwołania strukturalne

Zaawansowane techniki w tabelach przestawnych

Tabela na danych zewnętrznych

Grupowanie

Obliczenia procentowe i różnicowe

Formuły w tabelach przestawnych – pola i elementy obliczeniowe

Tworzenie rozbudowanych formuł z użyciem mniej znanych funkcji np:

Funkcja POWT

Funkcja SUMA.ILOCZYNÓW

Funkcje WIERSZ i WIERSZE

Funkcje NR.KOLUMNY i LICZBA.KOLUMN

Funkcja KOMÓRKA

Funkcja PRZESUNIĘCIE

Funkcja ADR.POŚR

Funkcja WYSZUKAJ

Tajemnice funkcji INDEKS

Funkcja DNI.ROBOCZE.NIESTAND

Funkcja AGREGUJ

Funkcja MOD

Funkcja TEKST

Analizowanie formuł

Formuły tablicowe - wprowadzenie

Tablice i operacje na tablicach

Wbudowane funkcje tablicowe

Tworzenie formuł tablicowych

Nazwy

Tworzenie nazw

Modyfikowanie nazw

Kiedy korzystać z nazw a kiedy nie

Tworzenie dynamicznych zakresów

Wymuszanie poprawności danych

Wymuszanie poprawnego wprowadzania danych przez użytkowników

Poprawność danych z wykorzystaniem własnych formuł

Tworzenie inteligentnych formularzy do wprowadzania danych

Zaawansowane mechanizmy

Śledzenie zmian

Konsolidacja

Scenariusze

Szukaj wyniku

Solver

Tabela danych

Zaawansowana prezentacja danych

Motywy i kolory

Tworzenie dynamicznych wykresów

Przykłady zaawansowanych wykresów

Wykresy przebiegu w czasie

Wstawianie i modyfikowanie wykresów przestawnych

Zaawansowane wykorzystanie kształtów i grafik

Korzyści wynikające z ukończenia szkolenia

Uczestnictwo w szkoleniu daje praktyczne umiejętności wykorzystania zaawansowanych

możliwości w arkuszu kalkulacyjnym MS Excel. Po ukończeniu szkolenia, uczestnicy będą w stanie

zastosować nabyte wiadomości do kreatywnej analizy działalności firmy oraz do budowy złożonych

modeli, dzięki którym podejmowanie decyzji będzie oparte o rzetelne i dokładne dane. Poznanie

zaawansowanych technik korzystania z programu Microsoft Excel uczyni codzienną pracę łatwiejszą

i bardziej efektywną.

Po ukończeniu szkolenia uczestnik będzie potrafił:

Obsługiwać zaawansowane funkcje arkusza kalkulacyjnego

Importować i formatować dane na poziomie zaawansowanym

Wykonywać skomplikowane obliczenia za pomocą formuł i funkcji

Tworzyć dostosowane do typów danych wykresy

Zakładać zaawansowane filtry

Korzystać z szablonów

Analizować dane za pomocą sum częściowych, tabel przestawnych i symulacji

Importować i eksportować dane

Korzystać z pracy online

Zarządzać zeszytami

Metodologia

Szkolenia prowadzone są nowoczesnymi, interaktywnymi i aktywizującymi metodami, przy pełnym

i aktywnym zaangażowaniu uczestników. Wszystkie ćwiczenia prowadzone są na podstawie

przykładów zaczerpniętych z praktyki uczestników i trenera (case studies). Zajęcia, pomimo

prezentowania złożonej wiedzy merytorycznej, prowadzone są w zrozumiały sposób, zapewniający

przyswojenie i przećwiczenie przekazywanej wiedzy przez każdego uczestnika szkolenia oraz

wspólne rozwiązanie napotykanych problemów. Podczas szkolenia jest tez czas na zadawanie pytań

i głębsze rozwiązywanie najbardziej uczestników interesujących zagadnień.

Visual Basic for Applications - Microsoft Excel

Czas trwania 3 dni – 12 godzin zegarowych

Cel szkolenia

Zrozumienie kodu VBA, poprzez naukę i praktyczne zastosowanie, zdobycie umiejętności tworzenia aplikacji automatyzujących zadania związane z dokumentami Microsoft Excel.

Zakres merytoryczny szkolenia

Moduł I – 90 min 1. Wprowadzenie do Visual Basic for Applications

główne elementy okna edytora VBA

zasady bezpieczeństwa

zasady tworzenie makr Moduł II – 90 min 2. Zasady tworzenia procedur

zasada dostępności procedur

zmienne - definiowanie i ich cechy

definicja pojęć: obiekt, atrybut, metoda, argument, kolekcja Moduł III – 120 min 3. Automatyzacja czynności w MS Excel

tworzenie makr przy pomocy nagrywania

edycja zarejestrowanego makra Moduł IV – 120 min 4. Rozszerzanie możliwości Visual Basic for Applications

sterowanie przebiegiem programu: konstrukcje warunkowe i pętle

przekazywanie parametrów. Moduł V – 120 min 5. Funkcje w MS Excel

tworzenie własnych funkcji Moduł VI – 90 min 6. Obiekty

biblioteka obiektów MS Excel

różne sposoby nauki obiektów

obiekt "range”, graficzne, wspierające tabele przestawne. Moduł VI – 90 min 7. Obsługa błędów

błędy składniowe, kompilacji, wykonania, logiczne

ignorowanie błędów

własna obsługa błędów.

Szczegółowy program

1.Ugruntowanie i wiedzy dotyczącej VBA Excel w następujących obszarach:

• Rejestrator makr Excela – skoroszyt Personal.xlsb – resetowanie i przerywanie makra – tryb względny i bezwzględny rejestracji

• Opcje ustawień Edytora VB – code setting – okna dokowane

• Edytor makropoleceń – paski narzędziowe – okno Immediate – okno Locals – okno Watch

• Zmienne tablicowe – deklaracja i dostęp do elementów tablicy – redefinicja tablicy – dyrektywa Option Base 1 – dyrektywa Option Explicit

• Sterowanie kodem – instrukcja warunkowa If … Else/ElseIf … End If – instrukcja Select Case – pętla For Each … Next – wcześniejsze zakończenie pętli

• Komunikacja makra z użytkownikiem – funkcja MsgBox (z opcjami) – metoda InputBox obiektu Application

• Funkcje wbudowane i operatory – wywołanie funkcji – funkcje konwertujące – zagnieżdżanie funkcji – operatory logiczne

• Funkcje użytkownika – opis argumentów funkcji – referencja do pliku z funkcją – tworzenie i instalowanie dodatku z funkcją

• Testowanie i debugowanie – kompilacja kodu – wykonanie kodu od wskazanej linii – operacja arytmetyczne i logiczne w oknie Immediate – uruchamianie funkcji i podprogramów w oknie Immediate – dodawanie Breakpoint’ów

•Obsługa błędów -obiekt Err -opcje środowiska VB związane z obsługą błędów

• Praca z obiektami – deklarowanie i tworzenie obiektów – usuwanie obiektów – aliasy do obiektów

• Praca z komórkami i zakresami – obiekt Selection – obiekt CurrentRegion – obiekty aktywne: ActiveCell, ActiveWorkbook, ActiveSheet – kolekcja arkuszy

• Praca z zeszytami – otwieranie i zamykanie zeszytów – metoda GetOpenFilename – kolekcja zeszytów

2. Import, eksport i obróbka danych zewnętrznych:

• Definiowanie połączeń z zewnętrznymi plikami tekstowymi – otwarcie pliku – metoda OpenText (rozróżnianie separatorów) – tryb dostępu – odczyt zawartości pliku – odczyt zawartości pliku liniami – zapis do pliku i jego zamknięcie

• przetwarzanie katalogów – funkcja Dir – właściwość FileDialog

• Definiowanie połączeń z zewnętrznymi plikami baz Access’a – dodawanie biblioteki ADO – obiekty: Recordset, Connection, Command

– metoda Execute – operacja na bazie: Select, Insert, Update, Delete

Korzyści wynikające z ukończenia szkolenia

Pełna automatyzacja wykonywanych czynności. Przy pomocy VBA można automatyzować wiele

powtarzalnych zadań (nie tylko w obrębie Excela, ale również powiązanych z innymi aplikacjami

pakietu Office). Uczestnicy szkolenia naucza się tworzyć rozbudowane, interaktywne skoroszyty.

Szkolenie zrealizuje dwa następujące cele: 1. Wprowadzi i ugruntuje wiedzę na temat języka VBA w MS Excel i środowiska programistycznego (Edytora Visual Basic). 2. Przedstawi sposoby pobierania (i zapisywania) danych z zewnętrznych źródeł danych przy użyciu kodu VBA oraz zaprezentuje możliwości VBA pod kątem obróbki danych dla potrzeb raportów i podsumowań.

Metodologia

Szkolenia prowadzone są aktywizującymi i ćwiczeniowymi metodami, przy pełnym i aktywnym

zaangażowaniu uczestników. Wszystkie ćwiczenia prowadzone są na podstawie przykładów

zaczerpniętych z praktyki uczestników i trenera (case studies). Cwiczenia skupiają Siena

poprawności pisania kodu VBA. Zajęcia, pomimo prezentowania złożonej wiedzy technicznej,

prowadzone są w zrozumiały sposób, zapewniający przyswojenie a przede

wszystkim przećwiczenie przekazywanej wiedzy przez każdego uczestnika szkolenia oraz wspólne

rozwiązanie napotykanych problemów.