Author: Marcin

Funkcje tablicowe #2

2012-03-25

Posted by Marcin under funkcje, funkcje tablicowe, Polskie blogi IT, sterowanie arkuszem, zaawansowane

W poprzednim wpisie pokazałam podstawy funkcji tablicowych. Dzisiaj pokażę ci bardziej zaawansowane zastosowanie.

Stworzyłem przykładową tabelę z kwartalnymi wynikami sprzedaży.

Utworzyłem też pomocnicze listy z produktami i kwartałami. Przydadzą się za chwilę.

Pokażę teraz, jak obliczyć sumę w zależności od wybranego produktu.

Stwórzmy najpierw listę w komórce B10 za pomocą sprawdzania poprawności. Na wstążce Dane wybierz przycisk Poprawność danych a następnie Poprawność danych … Otworzy się okno dialogowe. Wybierz w nim sprawdzenie poprawności z listy i zaznacz komórki z listą produktów.

W komórce B10 wybierz któryś produkt.

Do komórki obok wpiszemy funkcję tablicową obliczającą sumę sprzedaży dla wybranego produktu. Pamiętaj, żeby formułę zatwierdzić klawiszami Shift+Ctlr+Enter.

Operator * w tym wypadku nie jest mnożeniem wprost. Należy patrzeć na ten zapis jak na mnożenie dwóch macierzy, w tym jednej z warunkiem.

Upraszczając. Funkcja sprawdza kolejne pozycje z listy komórek B3:B6 i porównuje je z komórką B10. Dla spełnionego warunku, czyli dla wiersza, w którym znajduje się wartość z komórki B10 obliczana jest suma z zakresu C3:F6.

W komórce B11 stwórz sprawdzanie poprawności zawierającą listę kwartałów z zakresu J3:J6. Do komórki obok wpiszemy analogiczną formułę tablicową pozwalającą obliczać sprzedaż w wybranym kwartale.

Działa ona analogicznie do poprzedniej, z tym, że oblicza sumę dla kolumny spełniającej warunek.

W formule tablicowej może być klika warunków. Są one połączone ze sobą oraz z zakresem sumowania operatorem *.

W tym przykładzie, wybierana jest wartość na przecięciu wiersza (produktu) i kolumny (kwartału).

Warunek nie koniecznie musi dotyczyć nagłówków wierszy i kolumn. Możesz również dodawać warunki dla sumowanego zakresu.

Wpisana formuła sumuje jedynie wartości większe od podanego w warunku.

W ostatnim przykładzie wykorzystam funkcję MIN.K, podającą minimalną k-tą wartość w zakresie. Funkcja ma dwa argumenty: Tablica określający zakres danych oraz K określający zwracaną pozycje licząc od najmniejszej liczby.

Wpisana funkcja podaje sumę wszystkich elementów, które są mniejsze lub równe 4 najmniejszemu elementowi w zakresie.

W przykładach wykorzystywaliśmy funkcje SUMA, jako funkcję agregującą. Możesz oczywiście wykorzystać inne funkcje agregujące: ŚREDNIA, funkcje liczące odchylenia i jeszcze kilka innych.

Plik z przykładami, jest do pobrania tutaj.

Funkcje tablicowe #1

2012-03-10

Posted by Marcin under funkcje, funkcje tablicowe, Polskie blogi IT, sprytne, zaawansowane

Excel poza standardowymi funkcjami posiada również „tajemnicze” funkcje tablicowe. Zacznijmy jednak od krótkiej definicji tablicy.

Tablica jest obszarem/zakresem, w którym znajdują się dane, przeważnie liczbowe. Tablica może być jednowymiarowa (w matematyce zwany wektorem) lub wielowymiarowa. W Excelu możliwe do zastosowania są jedynie tablice do dwóch wymiarów.

Prostym przykładem tablicy jest zamieszone poniżej zestawienie ceny i wielkości sprzedaży produktów.

Mając takie dane, aż chciałoby się obliczyć wartość sprzedaży. Podchodząc w sposób najprostszy, policzylibyśmy dla każdego produktu iloczyn ceny i wielkości sprzedaży. A następnie sumę wartości sprzedaży poszczególnych produktów.

Pełną sumę można obliczyć za pomocą jednej formuły.

Pierwszą rzeczą, jaka rzuca się w oczy jest objęcie całej funkcji klamrami. Jeżeli zobaczysz taką funkcję, od razu wiesz, że jest to funkcja tablicowa.

Druga sprawa, to mnożenie zakresów a nie pojedynczych komórek.

Tak zapisana funkcja mnoży pierwszy element z pierwszego zakresu z pierwszym elementem drugiego zakresu. Następnie to samo robi z drugim, trzecim i tak dalej. Następnie te iloczyny sumuje.

Funkcję tablicową nie zatwierdza się klawiszem Enter. Aby poprawnie ją zatwierdzić, musisz nacisnąć Ctrl+Shift+Enter.

W funkcjach tablicowych, tak jak i w zwykłych, można stosować nazwy. Stworzyłem dwie: Ceny obejmująca komórki C3:C6, oraz Sprzedaż – D3:D6.

Funkcja działa identycznie jak z wpisanymi zakresami.

Plik z przykładem możesz pobrać tutaj.

W kolejnym wpisie pokażę bardziej rozbudowane zastosowania funkcji tablicowych.

Nowa wersja Tableau Public

2012-03-03

Posted by Marcin under nowa wersja, Polskie blogi IT, Tableau Public

Od kilku tygodni można pobrać nową wersję Tableau Public o numerze 7.0.

Zostało zmienionych kilka funkcji. Dwie najważniejsze to:

- wypełnianie obszarów map

- wykresy warstwowe.

Dodatkowo ulepszona została prezentacja danych na wykresach i obsługa nazw geograficznych.

Nową wersję można pobrać tutaj.

Tableau Public

Unikatowe wartości #2

2012-03-01

Posted by Marcin under funkcje, Polskie blogi IT, sprytne, zaawansowane

W poście Unikatowe wartości, pokazałem jak za pomocą tabeli przestawnej uzyskać unikatowe wartości z listy powtarzających się pozycji. Dzisiaj pokażę, jak zrobić to samo za pomocą funkcji.

Wykorzystam tą samą listę, co poprzednio.

Dodaj nową kolumnę przed listą nazwisk. Ponumerujemy w niej nowe nazwiska. Przy pierwszym nazwisku wstaw 1. Jako, że jest to pierwsze nowe nazwisko na liście, patrząc od góry.

Następnie wpisz poniższą formułę.

Funkcja LICZ.JEŻELI sprawdza ile razy występuje nazwisko na liście zaczynającej się od góry a kończącej się na poziomie sprawdzanego nazwiska. Musisz pamiętać, żeby zablokować znakami $, początek adresu, tak, aby przeciągając funkcje w dół, pozostał na swoim miejscu.

Funkcja JEŻELI, sprawdza, czy nazwisko występuje pierwszy raz, przyrównuje wynik LICZ.JEZELI do 1. Jeżeli nazwisko występuje po raz pierwszy, zwiększa wartość z poprzedniej komórki o 1 (co oznacza, że zostało znalezione nowe nazwisko).

W naszym przykładzie, unikatowych nazwisk jest 12.

W kolumnie obok wpisz poniższą formułę i przeciągnij ją do końca listy nazwisk.

Funkcja WYSZUKAJ.PIONOWO wyszukuje, w tabeli obok, nazwisko z kolejnym numerem. Różnica pomiędzy numerem aktualnego wiersza i wiersza nagłówka podaje numer nazwiska.

Funkcja JEŻELI sprawdza, czy różnica pomiędzy numerem aktualnego wiersza i numerem wiersza nagłówka większa od numeru w ostatnim wierszu. Jeżeli tak, to oznacza to, że nie ma więcej unikatowych nazwisk na liście i wpisywany jest ciąg pusty do komórki.

Plik z przykładem do ściągnięcia tutaj.

Tableau Public

2012-01-07

Posted by Marcin under Polskie blogi IT, Tableau Public, wizualizacja

Ostatnio ponownie odkryłem Tableau Public, aktualnie w wersji 6.1. Jest to świetne narzędzie dla osób, które nie są analitykami a chcą korzystać z możliwości BI.

Wersja Public jest darmowa i można ją pobrać tutaj.

Na plus aplikacji można zapisać łatwość użycia za pomocą przeciągnij i puść. A także możliwość wstawiania obliczeń. Dodatkowo można stworzyć bardzo rozbudowaną strukturę za pomocą standardowych arkuszy oraz arkuszy dashboardów.

Program ma też kilka minusów. Wizualizacje można zapisać jedynie na serwerze. Powoduje to, że dane są publicznie dostępne. Nie ma możliwości zapisania lokalnie kopii roboczej lokalnie.

Zamierzam poznać dokładnej Tableau Public. Będę na bieżąco przedstawiał wam ciekawe rozwiązania.

1% podatku

2012-01-03

Posted by Marcin under 1% podatku, Bez kategorii, Polskie blogi IT

Miałem chwilę przerwy w pisaniu wiadomości. Poświęciłem się innej, prywatnej sprawie a teraz chciałbym Ciebie prosić o pomoc.

Mam dwóch synów. 5-letniego Mateusza i 3-letniego Tymoteusza.

Tymoteusz jest chory na chorobę genetyczną. Jest w trakcie diagnozowania w Centrum Zdrowia Dziecka. Choroba objawia się niskim napięciem mięśniowym oraz utratą słuchu. Tymoteusz jeszcze nie chodzi samodzielnie. Ma duży niedosłuch i nosi aparaty słuchowe. Wymaga codziennej rehabilitacji. Niezbędne są również turnusy rehabilitacyjne, których koszt dochodzi do 5.000 zł.

Jeżeli jeszcze nie zdecydowałeś, na jaki cel przekażesz swój 1% podatku zajrzyj na stronę Tymoteusza.

Dziękuje bardzo za zainteresowanie.

Marcin

No Related Posts

Szybkie wstawianie serii na wykresie

2011-11-27

Posted by Marcin under Polskie blogi IT, serie danych, wykres

Jak szybko dodać serię do wykresu? Zaraz to opiszę.

Załóżmy, że mamy dane dotyczące sprzedaży, kosztów i wyników sprzedaży. Na wykresie pokazujemy wielkość sprzedaży. Jak wstawić wynik sprzedaży?

Zaznacz zakres E2:E9 i skopiuj go. Następnie zaznacz wykres i wklej dane.

Proste i skuteczne.

Unikatowe wartości

2011-11-20

Posted by Marcin under Polskie blogi IT, sprytne, tabele przestawne

Dzisiaj opiszę szybki sposób uzyskania unikatowych wartości z listy.

Znalazłem w sieci przykładową listę znanych amerykanów. Jest nieposortowana i nazwiska powtarzają się.

W prosty sposób znajdziemy unikatowe nazwiska za pomocą tabeli przestawnych.

Zaznacz nagłówek listy, komórkę A1. Na wstążce Wstawianie, wybierz Tabela przestawna.

Pojawi się okno konfigurujące tabelę przestawną.

Zmień opcje na Istniejący arkusz i w Lokalizacji zaznacz komórkę, od której ma zaczynać się tabela przestawna.

Pojawi się okno do tworzenia tabeli przestawnej.

Zaznacz pole wyboru przy nazwie pola. Przeniesie się ono do okna Etykiety wierszy.

W efekcie uzyskasz listę z unikatowymi pozycjami z pierwotnych danych.

Plik z przykładem do ściągnięcia tutaj.

Świetnie sformatowany wykres

2011-11-05

Posted by Marcin under formatowanie, Polskie blogi IT, wykres, zaawansowane

Mam nadzieję, że tytuł przykuł uwagę

Domyślne wykresy w Excelu są delikatnie mówiąc, niezbyt dobrze sformatowane. Zwłaszcza, jeżeli chodzi o niższe wersje, do 2003 włącznie. Zespół programistów MicroSoftu zajmujący się tą częścią programu nie bardzo nadążał zamianami w prezentacji danych. Wielu użytkowników nie chce albo nie umie nic z tym zrobić. W tym wpisie pokażę Ci jak zrobić coś z niczego.

Na rysunku A jest standardowa wersja wykresu słupkowego wykonana Excelu 2003. Szare tło męczy, niepotrzebna legenda zajmuje miejsce i słupki otoczone linią cofają nas do standardów z ubiegłego tysiąclecia.

image001 Świetnie sformatowany wykres

Jeżeli chcesz poprawić, chociaż trochę, wygląd wykresu, to usuń legendę. Dla jednej serii danych, jest zbędna. Najprostszym sposobem, żeby usunąć legendę, jest jej zaznaczenia i naciśnięcie klawisza Del.

Usuń szare tło lub zmień na kolor biały. Analogicznie, jak w przypadku legendy, zaznacz tło wykresu i naciśnij klawisz Del. Od razu wykres jest czytelniejszy. Przykład nasz na rysunku B.

Ja zmieniam wykresy jeszcze bardziej. Jak klikniesz na słupek prawym przyciskiem myszy, otworzy się menu, z którego wybierz Formatuj serię danych.

Otworzy się okno, w którym wybierz zakładkę Opcje serii.

Suwakiem znajdującym się w części Szerokość przerwy regulujesz odstęp pomiędzy słupkami. 100% oznacza, że przerwa jest takiej samej szerokości jak słupek. Szerokość przerwy dobierz sobie według uznania. Ja zwykle ustawiam na wartość nie większą niż 100%.

Przejdź teraz na zakładkę Wypełnienie.

W górnej części wybierasz sposób wypełnienia słupka. Możesz wybrać brak wypełnienia, wypełnienie gradientowe lub wzorem. W tym wypadku wybieram wypełnienie pełne, czyli jednobarwne.

Poniżej możesz wybrać kolor słupka. Obok jest suwak umożliwiający zmianę przezroczystości. Dzięki niemu możesz rozjaśnić kolor lub też częściowo uwidocznić to, co jest pod spodem.

Przejdź do zakładki Kolor krawędzi. Tam wybierz Brak linii. Nie tylko moim zdaniem, brak linii otaczających słupki daje bardziej profesjonalny efekt.

Na końcu, kliknij prawym przyciskiem na słupek i z menu wybierz Dodaj etykiety danych.

Nad słupkami pojawią się wartości prezentowane przez wykres.

I tak stworzyliśmy wykres C, pokazywany na początku wpisu.

Zapraszam do eksperymentowania z innym od standardowego formatowaniem wykresów.

Sterowanie dwoma wykresami

2011-10-29

Posted by Marcin under funkcje, Polskie blogi IT, sprytne, sterowanie arkuszem, Sterowanie zakresem danych, wykres

Czasami jest konieczność pokazania danych szczegółowych w porównaniu z innymi okresami. Coś takiego można zrobić za pomocą sterowanego paskiem przewijania podwójnego wykresu.

Przygotowałem przykładowe dane składające się z 15 lat sprzedaży 5 produktów. Na podstawie tych danych zbuduję wykresy przedstawiające te informacje.

Obok danych wstawiłem pasek przewijania. Sformatowałem go następująco.

Wartość minimalną ustawiłem na 1 a maksymalna na 15 – liczbę obserwowanych lat.

Dodałem następnie dodatkową kolumnę, która pokazuje sumę sprzedaży dla danego wybranego suwakiem roku.

Pierwsza funkcja PRZESUNIĘCIE wybiera rok, do którego odwołuje się pozycja paska przewijania i porównuje go z numerem roku z aktualnego wiersza. Jeżeli obydwie wartości są równe, wówczas druga funkcja PRZESUNIĘCIE wpisuje wynik rocznej sprzedaży.

Na podstawie kolumn A, B i H tworzę wykres kolumnowy z dwoma seriami.

Następnie zmieniam jego formatowane. Usuwam legendę, poziome linie oraz oś Y. Zmieniam kolor serii Razem na szary a Sprzedaż na czerwony.

Dalej, ustawiam nakładanie serii na 100%. Oznacza to, że seria Sprzedaż będzie dokładnie na serii Razem. Zmniejszam również szerokość przerwy pomiędzy słupkami na 50%. Będą wówczas szersze i lepiej widoczne.

Na koniec, dodaję nad słupkami serii Sprzedaż wartość, którą reprezentuje czerwony słupek. Formatuję go niestandardowo formatem # ##0;-# ##0;; co oznacza, że wartości dodatnie i ujemne będą pokazywane, a zero i tekst nie będą. Pozwala to pokazać wartość jedynie nad czerwonym słupkiem.

W dalszej kolejności, na podstawie pozycji suwaka, używając funkcji PRZESUNIĘCIE wybieram wiersz z danego roku.

W komórkę B19 wpisuję formułę będącą tytułem wykresu.

W tytuł wykresu wpisuję odwołanie do tej komórki: =Wykres!$B$19.

Na podstawie danych rocznych wybranych za pomocą paska przewijania, tworzę wykres pierścieniowy.

Zmieniam kolory poszczególnych produktów na odcienie szarości. Następnie dodaję etykiety danych pokazujące wartości oraz udział procentowy w całości roku. Dla całego wykresu oraz dla legendy, ustawiam brak koloru dla tła i obramowania. Wykres nałożę na poprzedni, więc nie może go przesłaniać.

Na koniec, nakładam wykres pierścieniowy na poprzednio utworzony tak, aby zmieścił się pomiędzy słupkami a tytułem wykresu.

Cały plik możesz pobrać tutaj.

Funkcje daty

2011-10-23

Posted by Marcin under adresy, daty, funkcje, Polskie blogi IT

Wiele osób ma problemy z poprawnym operowaniem datami w Excelu. Data w Excelu, to nic innego jak liczba sformatowana jako data. Świat w Excelu powstał 1 stycznia 1900. Tak wygląda liczba 1 sformatowana jako data.

Jeszcze kilka podstawowych informacji i przejdziemy do działań na datach. Jeden dzień (jedna doba) ma wartość jeden. Co za tym idzie, jedna godzina to 1/24, jedna minuta to 1/(24*60) a jedna sekunda – 1/(24*60*60).

Są dwie funkcje podające aktualną datę. Funkcja DZIŚ i TERAZ. Obydwie nie mają argumentów. Funkcja DZIŚ podaje samą datę, natomiast funkcja TERAZ łącznie z godziną, minutami i sekundami.

Są funkcje pozwalające „wyciąć” z daty liczbę określającą numer roku, miesiąca, dnia miesiąca, godziny, minuty i sekundy. Argumentami tych funkcji jest data.

Funkcja DZIEŃ.TYG pozwala określić numer dnia tygodnia dla danej daty.

Funkcja ma dwa parametry: Liczba kolejna oraz Zwracany typ. Liczba kolejna jest datą dla której chcemy określić numer dnia tygodnia. W parametrze Zwracany typ określamy, który dzień jest pierwszym dniem tygodnia oraz jaki ma numer:

wartość parametru równa 1 określa niedzielę jako dzień numer 1 a sobotę jako 7
wartość parametru równa 2 określa poniedziałek jako dzień numer 1 a niedzielę jako 7
wartość parametru równa 3 określa poniedziałek jako dzień numer 0 a niedzielę jako 6.

Następnymi ciekawymi funkcjami są funkcje pozwalające określić inna datę względem daty odniesienia.

Pierwszą jest NR.SER.DATY, która podaje w wyniku datę, w której dzień miesiąca jest analogiczny do daty odniesienia miesiąc i rok jest uzależniony od liczby miesięcy przesunięcia. Funkcja posiada dwa parametry: Data początkowa i Miesiące. Data początkowa określa datę, do której się odnosimy. Parametr Miesiące określa przesunięcie o liczbę miesięcy względem daty odniesienia. Wartości dodatnie przesuwają w przód, ujemne w tył.

Drugą funkcją jest NR.SER.OST.DN.MIES, która daje w wyniku datę ostatniego dnia miesiąca przesuniętego względem daty odniesienia. Funkcja posiada, podobnie do poprzedniej, dwa parametry: Data początkowa i Miesiące. Data początkowa określa datę, do której się odnosimy. Parametr Miesiące określa przesunięcie o liczbę miesięcy względem daty odniesienia.

Na koniec uwaga. W operacjach na datach można używać podstawowych działań typu dodawanie i odejmowanie. Można więc uzyskać dowolne wyniki.

Plik z przykładami można pobrać tutaj.

Funkcja WYSZUKAJ.PIONOWO +

2011-10-16

Posted by Marcin under funkcje, Polskie blogi IT, poprawność danych, sprytne, sterowanie arkuszem, zaawansowane

Czasami zdarza się, że musimy wyszukać dane w tabeli, które są uzależnione od kilku parametrów. Na przykład daty, regionu, sprzedawcy i produktu. Funkcja WYSZUKAJ.PIONOWO potrafi to zrobić dla jednego parametru. Dzisiaj pokaże, jak pomimo wszystko zaprzęgną ją do bardziej skomplikowanych działań.

Znalazłem w sieci tabelę z danymi dotyczącą sprzedaży. Są tam pola z datą, imieniem sprzedawcy, regionem sprzedaży, produktem, wielkością sprzedaży, wartością sprzedaży, zyskiem ze sprzedaży.

Funkcja WYSZUKAJ.PIONOWO ma cztery parametry: Szukana Wartość, Tabela, Nr Indeksu Kolumny i Przeszukiwany Zakres. Szukana wartość jest wartością łączącą dwie tabele. Wyszukiwana jest w pierwszej kolumnie Tabeli. Nr indeksu kolumny określa, z której kolumny Tabeli pobierania jest wartość. Przeszukiwany zakres przyjmuje wartości PRAWDA lub FAŁSZ. Jeżeli pisana jest wartość FAŁSZ, to funkcja wyszuka wynik dla dokładnego dopasowania Szukanej wartości, jeżeli go nie znajdzie, to zwróci błąd #N/D. Jeżeli jest wybrana wartość PRAWDA, to funkcja najdzie pozycję najbardziej dopasowaną do Szukanej wartości.

Aby móc zastosować tą funkcję, trzeba mieć jedną szukaną wartość. Najprościej jest to zrobić, łącząc wszystkie pola z kolumn wyboru w jedną komórkę łącznikiem &.

Następnie dodałem drugi arkusz ze słownikami dla danych z kolumn Miesiąc, Sprzedawca, Region i Produkt.

Teraz stworzymy cztery nazwy zawierające zakresy słowników.

Zaznacz zakres słownika i naciśnij Menedżer nazw na wstążce Formuły.

W pole nazwy wpisz ‘Daty’.

Analogicznie zrób dla Sprzedawców – wpisz nazwę ‘Sprzedawcy’, dla Regionu – ‘Regiony’ i dla Produktu – ‘Produkty’.

W ostatnim arkuszu stworzymy mechanizm wybierania danych. Nagłówki są analogiczne jak w poprzednim arkuszu. Wyboru parametrów wejściowych dokonywać będziemy za pomocą listy stworzonej w sprawdzaniu poprawności danych. Naciśnij przycisk Poprawność danych na wstążce Dane.

W otwartym oknie z listy dozwolone wybierz ‘Lista’. Następnie w źródle wpisz po znaku ‘=’ nazwę ‘Daty’.

image0111 Funkcja WYSZUKAJ.PIONOWO +

To samo zrób dla pozostałych trzech pól wyboru.

Następnie, stwórz z tych komórek, kod pozwalający wyszukać odpowiedni wiersz w arkuszu z danymi.

Mając te dane, możemy już przenosić za pomocą funkcji WYSZUKAJ.PIONOWO, dane z arkusza z danymi.

Przeciągnij funkcję na kolejne dwie komórki i zmień numer kolumny na 7 a następnie na 8.

Teraz zmieniając dane w czterech pierwszych komórkach, możesz sterować pozostałymi danymi.

Gotowy plik możesz pobrać tutaj.

Jak uniknąć powielania wprowadzonych danych

2011-10-12

Posted by Marcin under funkcje, Polskie blogi IT, poprawność danych, sprytne

Pisałem już wcześniej o sposobach kontroli jakości wprowadzanych danych. Używałem do tego Poprawności danych na wstążce Dane. Dzisiaj napiszę o kolejnym sposobie kontroli wpisów.

Pomyśl, że chciałbyś kontrolować listę wpisywanych pozycji, tak aby były unikatowe. Żeby to zrobić, zaznacz interesujący cię zakres komórek. Następnie kliknij na Poprawność danych na wstążce Dane.

Z listy dozwolonych wpisów, wybierz Niestandardowe. W okno formuły wpisz funkcję LICZ.JEZELI.

Funkcja podaje liczbę wystąpień danego ciągu znaków w kontrolowanym zakresie. Posiada dwa parametry: Zakres i Kryteria. Zakres określa komórki, które są sprawdzane, a Kryteria porównywany ciąg znaków.

Wpisana formuła zlicza liczbę wystąpień wprowadzanego ciągu znaków we wskazanym zakresie i sprawdza, czy jest on mniejszy od 1. Czyli, czy nie występuje Jeżeli już jest taki wpis to pojawia się informacja o błędzie.

Oczywiście, pojawiający się komunikat, możesz dostosować do swoich potrzeb w zakładce Alert o błędzie.

Zapraszam do opisywania możliwych zastosowań tego rozwiązania.

Plik z przykładem możesz pobrać tutaj.

Formanty formularza

2011-10-09

Posted by Marcin under lista, Polskie blogi IT, sprytne, sterowanie arkuszem, zaawansowane

We wcześniejszych wpisach pokazywałem w jaki sposób można sterować zachowaniem arkusza. Oprócz opisanych przeze mnie elementów sterujących jest jeszcze kilka innych. Dzisiaj opiszę kolejne.

Przejdź do wstążki Deweloper i naciśnij przycisk Wstaw.

Na karcie jest sześć przycisków, które będą nas interesować.

Pasek przewijania

Opisywałem go już wcześniej. Aby umieścić pasek przewijania w arkuszu, wybierz jego przycisk a następnie narysuj kształt w arkuszu. Następnie najedź na niego i kliknij prawym przyciskiem i wybierz Formatuj formant. Otworzy się okno dialogowe.

Można w nim określić wartość startową paska, jego wartość minimalną i maksymalną oraz komórkę w której będzie podawana jego pozycja. Po wybraniu parametrów paska, przesuń go i zobacz jak zmienia się wartość w komórce docelowej.

Pole kombi

Pole kombi jest rozwijalną listą z możliwością jednokrotnego wyboru. Bardzo często występuje w programach, więc myślę, że jego zasada funkcjonowania jest Ci znana .

W oknie formatowania są dwa główne parametry. Zakres wejściowy, w którym określasz z jakiego zakresu komórek pobierana jest lista pozycji. Możesz podać bezpośrednie adresy lub też stworzyć Nazwę i przypisać do niej funkcje wybierającą zakres. Na przykład funkcję PRZESUNIĘCIE.

W efekcie końcowym, otrzymasz to.

Pole wyboru

Pole wyboru pozwala zaznaczać lub odznaczać opcje. Wynikiem jest wartość PRAWDA lub FAŁSZ w zależności od stanu pola wyboru.

Pierwszym parametrem jest stan początkowy pola wyboru. Drugim, jak zwykle, komórka, w której podawany jest stan.

Pokrętło

Pokrętło jest podobnym formantem jak pasek przewijania. Z założenia jednak ma służyć jako element zmieniający wartość w komórce.

Analogicznie jak w przypadku paska przewijania, pierwszy parametr określa wartość startową pokrętła. Jego wartość minimalną i maksymalną określają dwa kolejne parametry a ostatni – komórkę, w której będzie podawana jego pozycja.

Pole listy

Pole listy jest analogicznym formantem jak pole kombi. Jedyną różnicą jest wyświetlanie listy bez konieczności jej rozwijania.

Parametry są również analogiczne od pola kombi.

Przycisk opcji

Umieszczając klika tych formantów w arkuszu masz możliwość wybory jednej opcji z kilku. Po wybraniu innej, poprzednio zaznaczona, wyłączy się. Wynikiem działania tych formantów jest numer aktualnie zaznaczonej opcji.

Parametry są analogiczne jak w przypadku pola wyboru.

Plik z przykładami możesz pobrać tutaj.

Przeszukiwanie tabel w Excelu – dwa wyrazy w jednym wierszu (kolumnie)

2011-08-02

Posted by Marcin under Formuła tablicowa, Formuły, Formuły tablicowe, Jeżeli, Licz.Jeżeli, Podaj.Pozycję, Polskie blogi IT, Tabele, Wasze pytania, Wiersz

Andrzej, jeden z czytelników bloga zadał następujące pytanie: “W 2 kolumnach mam wiele różnych zmiennych np. Imiona, samochody. są one także rozmieszczone w tablicy, skąd trzeba pobrac dane. Zadanie polega na tym, że jeśli w kolumnie1 jest wyraz „Maria”, w kolumnie2 „opel”, to excel ma przeszukać całą tablice i sprawdzić, czy oba wyrazy występują w [...]

Przeszukiwanie tabelki i nagłówek kolumny ze znalezioną wartością.

2011-08-01

Posted by Marcin under Formuła tablicowa, Formuły, Formuły tablicowe, Indeks, Podaj.Pozycję, Polskie blogi IT, suma.iloczynów

Dzisiaj małe ćwiczenie, które pokazuje jak w Excelu ten sam efekt można osiągnąć przy wykorzystaniu zupełnie różnych funkcji. Mamy więc sobie tabelkę i chcielibyśmy, aby formuła Excela odpowiedziała nam na pytanie, w której kolumnie (lub wierszu, w naszym przykładzie używam jednak kolumn) znajduje się poszukiwane słowo. Myślę, że animacja powyżej dokładnie pokazuje problem. Pierwsze co [...]

Solver i kombinacja liczb dających określoną sumę

2011-07-20

Posted by Marcin under Dodatki, Formuła tablicowa, Formuły tablicowe, Makro, Polskie blogi IT, Solver

Kilka dni temu stanąłem (mam dziwne wrażenie, że nie ja pierwszy) przed następującym problemem: w jaki sposób z listy liczb wybrać wszystkie kombinacje dające określoną sumę ? Problem tylko pozornie wydaje się prosty (w każdym razie wydawał mi się taki kilka dni temu, kiedy pełen zapału postanowiłem pomóc współpracownikowi, który musi ręcznie wyłuskiwać z bardzo [...]

Excel „timestamp” (czyli o datowaniu zmian w komórce)

2011-07-03

Posted by Marcin under Czas, Czas i data, Data, Dziś(), Formuły, Odwołania cykliczne, Polskie blogi IT, Teraz(), timestamp

Od czasu do czasu zachodzi potrzeba zapisania w Excelu daty i godziny wprowadzenia nowej wartości (lub jej zamiany) do komórki. Przykładem może tu być lista, którą będzie wypełniał pracownik, a my chcielibyśmy dokładnie wiedzieć kiedy każda wartość została wpisana. Załóżmy sobie także na dzisiaj (tak dla podniesienia ciśnienia), że edytor VBA ma dzisiaj wolne, poszedł [...]

Lista rozwijana, tabelki i aparat fotograficzny

2011-01-12

Posted by Marcin under Adr.pośr, dynamiczne zakresy, formatowanie, Formuła tablicowa, Formuły, Formuły tablicowe, Indeks, lista rozwijana, Podaj.Pozycję, Polskie blogi IT, Przesunięcie, Wiersz, Wygląd skoroszytu

Dzisiaj o tym, w jaki sposób dodać do swojego arkusza możliwość wyboru i wyświetlenia jednej z wielu tabel (zakresów) znajdujących się w innym skoroszycie. Zadanie niezbyt skomplikowane, ma jednak tę zaletę, że będziemy korzystać z kilku ciekawych i bardzo użytecznych technik (funkcji Excela). Będzię to wspomniane już w tytule narzędzie Aparat Fotograficzny, o którym pisałem [...]

VSTO i Add-in Express 2010

2011-01-06

Posted by Marcin under Dodatki, Polskie blogi IT, VSTO

Od pewnego czasu mam okazję pracować z Visual Studio oraz dodatkiem do niego – Add-in Express™ 2010 (http://www.add-in-express.com). Ponieważ moje wrażenia związane z dodatkiem są bardzo pozytywne, chciałbym podzielić się z Wami kilkoma wrażeniami z użytkowania całości. Od razu zaznaczyć muszę, że jestem nowicjuszem jeśli idzie o programowanie VSTO i Visual Studio. Wyjście poza VBA [...]

Excelowa animacja Noworoczna

2011-01-01

Posted by Marcin under Makro, Polskie blogi IT, Wykresy

Całość to tylko kilka linijek kodu VBA (plus sprytne – no w sumie sprytny chłopak jestem – wykorzystanie Excelowych wykresów) . Dla zainteresowanych, jak zwykle zresztą, plik Excela do ściągnięcia. Marcin

Interaktywna tabelka i formatowanie warunkowe

2010-12-31

Posted by Marcin under formatowanie, formatowanie warunkowe, Formuły, Polskie blogi IT, Tabelka, Wygląd skoroszytu

Korzystając z kilku (raczej prostych) formuł oraz formatowania warunkowego (bez konieczności użycia makr) można w bardzo prosty sposób utworzyć interaktywną tabelkę. Ilość zakładek może być w zasadzie dowolna (o ile oczywiście chce Wam się przygotowywać odpowiednie formuły i warunki dla formatowania warunkowego). Kilka uwag: Nie korzystam z makr. Dane źródłowe znajdują się w trzech roboczych [...]

Zaznaczanie różnymi kolorami powtarzających się wartości

2010-02-15

Posted by Marcin under formatowanie warunkowe, Makro, Polskie blogi IT, Zdarzenia

Potrzebujemy, aby makro VBA (niekoniecznie makro, ale jakoś nie wymyśliłem jeszcze innego sposobu) zaznaczalo w kolumnie, przy pomocy róznych kolorów, powtarzajęce się wartości. Mniej więcej tak: Gdyby chodziło tylko o zaznaczanie komórek, których wartość się po prostu powtarza skorzystałbym z Formatowania warunkowego. W omawianym przypadku sprawa jest jednak nieco bardziej skomplikowana, ponieważ chciałem, aby różne [...]

Nietypowy wykres w Excelu – uaktualnienie wpisu

2010-02-13

Posted by Marcin under Polskie blogi IT

Uaktualniłem dzisiaj mój wpis opisujący sposób wykonania nietypowego wykresu w Excelu. Nowy (uaktualniony) wpis jest lepszy od starego ponieważ: dodałem kilka animacji (zamiast statycznych obrazków), dodałem nową formułę sterującą formatowaniem warunkowym, która umożliwia wybór kierunku „zapełniania się” wykresu. Do tej pory rozpoczynaliśmy w lewym dolnym rogu, teraz możemy „zaszaleć” i rozpocząć w rogu prawym, wgrałem [...]

Author: Marcin

Related Posts:

Related Posts:

Related Posts:

Related Posts:

Related Posts:

Related Posts:

Related Posts:

Related Posts:

Related Posts:

Related Posts:

Related Posts:

Related Posts:

Related Posts:

Related Posts:

Archiwa

Kategorie

Tagi

Autorzy