Category: sterowanie arkuszem
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.
Related Posts:
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.
Related Posts:
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’.
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.
Related Posts:
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.