Nietypowy wykres w Excelu

by Marcin

Dzisiaj o tym, w jaki sposób zrobić dosyć nietypowy, ale całkiem zgrabny typ wykresu (który tak naprawdę wykresem nie jest). Poniżej zobaczyć możesz, wykonany przed chwilą przeze mnie, przykład, o którym będę pisał w dalszej części.

Nietypowy wykres w Excelu

Ten typ wykresu wpadał mi w oczy już kilkakrotnie (i podobał się), dopiero jednak dzisiaj pomyślałem o tym, aby spróbować go zrobić w Excelu. Wynik moich prób – poniżej.

Wykres w Excelu i formatowanie warunkowe

Cała idea opiera się na wykorzystaniu formatowania warunkowego. Zanim jednak zacząłem zastanawiać się nad odpowiednią formułą sformatowałem komórki skoroszytu.

Potrzebnych jest w sumie 100 komórek (10 wierszy i 10 kolumn) i najlepiej, aby były one kwadratami (no chyba, że masz ciekawszy pomysł za wygląd całości). Po dostosowaniu wysokości i szerokości komórek wszystko wyglądała u mnie mniej więcej tak jak na rysunku (dla ułatwienia, tymczasowo, zaznaczyłem ramką obszar przeznaczony pod wykres).

Nietypowy wykres złożony z kwadracików Odpowiednio sformatowane „tło” naszego wykresu

Po ustawieniu wymiarów wierszy i kolumn zmieniłem także kolor tła na „szaro-niebieski”, włączyłem obramowanie komórek (kolor biały) oraz wyłączyłem linie siatki w skoroszycie. Gotowe do użycia miejsce pod wykres zaczęło wyglądać nieco lepiej.

Nietypowy wykres złożony z kwadracików Nasz nietypowy wykres czeka juz na wprowadzenie „akcji kolorującej”

Wszystko, co pozostało to napisać formułę dla Formatowania warunkowego, która w zależności od wartości komórki sterującej (znajdować się w niej będzie liczba określająca ile komórek należy zaznaczyć na wykresie) pokoloruje odpowiednią ilość kwadracików.

Formuła sterująca formatowaniem warunkowym

Po zaznaczeniu wszystkich 100 komórek wybrałem z menu formatowanie warunkowe. Jako warunek wybrałem „Formuła jest…” i wpisałem nastepującą formułę (będziesz ją musiał nieco dostosować do swoich potrzeb):

=((WIERSZ($L$13)-WIERSZ())*10+NR.KOLUMNY($L$13)-NR.KOLUMNY()+1)<=$M$5

W komórce $M$5 znajduje się liczba kwadracików do zaznaczenia, $L$13 to prawa dolna komórka zakresu. Po wpisaniu formuły trzeba jeszcze dobrać odpowiedni kolor dla komórek (ja wybrałem sobie teraz pomarańczowy) i  kliknąć  OK.  To wszystko. Powinno działać.

W widocznym przykładzie powyżej widać, że wykres „zapełnia” się od lewego dolnego rogu. Co jednak, gdybyśmy chcieli to odwrócić i zacząć od prawego dolnego rogu? Nic prostszego.

Dla wygodny dodamy sobie listę rozwijaną, przy pomocy której będziemy zmieniać kierunek. Listę wstawiłem przy pomocy sprawdzania poprawności.

Efekt widoczny jest poniżej (przepraszam za angielskie wtrącenia, ale robiłem tę animację z przeznaczeniem  dla angielskiej wersji mojego bloga. Przyznam, że nie bardzo chce mi się to wszystko teraz poprawiać.

Nietypowy wykres w Excelu Zmiana kierunku zapełniania się wykresu

Formuła odpowiedzialna za sterowanie całością wygląda teraz tak:

=((JEŻELI($A$10,WIERSZ($B$16),WIERSZ($K$16))-WIERSZ())*10+MODUŁ.LICZBY(JEŻELI($A$10,0,NR.KOLUMNY($K$16))-NR.KOLUMNY())-JEŻELI($A$10,NR.KOLUMNY($B$16),0)+1)<=$M$8

Dla jasności zamieszczam obrazek pokazujący rozmieszczenie komórek wykresu w arkuszu. Od razu widać, do których komórek odwołują się funkcje pokazanej formuły.

Nietypowy wykres w Excelu Położenie wykresu w arkuszu

Komórka A10 zwraca wartośc FAŁSZ jeżeli wybrano zapełnianie wykresu od prawej do lewej i PRAWDA jeżeli od lewej do prawej.

Na koniec jeszcze plik z przykładami.

Download

Jak zwykle – jeżeli ktoś ma lepszy pomysł na formułę – proszę o maila lub zostawienie komentarza na blogu.

Marcin

{ 12 comments… read them below or add one }

Quasi

Bardzo dobra formuła, inteligentnie pomyślane :-).

Marcin

W moim pierwszym podejściu do problemu formuła wyglądała tak:

=LUB(WIERSZ($C$13)-WIERSZ()<=ZAOKR.DO.CAŁK($N$6)-1;ORAZ(WIERSZ($C$13)-WIERSZ()=
ZAOKR.DO.CAŁK($N$6);NR.KOLUMNY()-NR.KOLUMNY($C$13)<($N$6-ZAOKR.DO.CAŁK($N$6))*10))

gdzie:

$C$13 to lewa dolna komórka zakresu z wykresem, a
$N$6 to komórka, w której miałem ilość komórek do zaznaczenia podzieloną przez 10.

W sumie też działa 🙂 Tyle tylko, że wygląd tej formuły przerażał mnie od samego początku. Stąd dalsze „kombinowanie” i ostatecznie formuła, którą opublikowałem. Oczywiście jeżeli ktoś ma lepszy pomysł – czekam na kontakt.

Kecaj

Kiedy przeczytałm twój post zacząłem się zastanawiać jak w taki sam sposób zrobić wykrs słupkowy, no i doszedłem do wniosku że mozna by to zrobić za pomocą tkiej formułu: =WIERSZ()>(50-W51K), zakłądając że formatowaniu warunkowemu poddajemy prostokąt o wymiarach 50 wierszy na powidzmy 6 kolumn możemy wpisując w wiersz W51K dowolną wartość tworzyć słupki (o wielkości równej wartości podnej w wierszu 51) tak jak w wykresie słupkowum. Pozdrawiam Kecaj.

Kecaj

Aby wykres który omówiłem powyżej był bradziej czytelny można zastosować oddzielne kolory kolumn w wykresie np. w zależności od tego czy nr kolumny jest parzysty czy nie, w formatowaniu warunkowym zastosowałem dwie formuły: =ORAZ(MOD(NR.KOLUMNY();2)0;WIERSZ()>(50-W51K)) i =ORAZ(NIE(MOD(NR.KOLUMNY();2)0);WIERSZ()>(50-W51K)).

Marcin

Kecaj – mógłbyś gdzieś zostawić do ściągnięcia przykładowy plik ? Jak to mówią – obraz jest wart tysiąca słów 🙂

Kecaj

Wyślij mi na mojego maila adres na jaki mam Ci przesłać ten plik a napewno prześlę pozdrawiam Kecaj

Kecaj

Plik przesłany pozdrawiam.

Benhamin

Przygotowałem taki sam kwadrat jak na początku, ponumerowałem kwadraciki od 1 do 100 (kolor cyferek „szaro-niebieski”) zaczynając od prawego dolnego kwadracika i w formatowaniu warunkowym ustawiłem „jeżeli wartość w komórce $M$5 jest mniejsza lub równa wartości w komórce kolorowej to jej kolor i kolor napisu zmienia się na pomarańczowy”

Edward

Witam !!
Mam prośbę – nie mogę w Excelu 2007 dojść do tego jak wykonać wykres z dwoma osiami Y, w poprzedniej wersji nie było z tym problemu jabyś mi mógł wyjaśnić czy istnieje taka możliwość w najnowszej wersji byłbym wdzięczny. Pozdrawiam Edward

Edward

Już wiem jak się wykonuje wykres z dwoma osiami Y tak że proszę się nie spieszyć z odpowiedzią Dzięki

kaśka

Hej, pytanie, na które chciałabym uzyskać odpowiedź brzmi: czy może mi ktoś dokładnie objaśnić skąd taka formuła =((WIERSZ($L$13)-WIERSZ())*10+NR.KOLUMNY($L$13)-NR.KOLUMNY()+1)<=$M$5
i dlaczego pod koniec jest tam +1

vilipo

Moim zdaniem mozna wprowadzic do naszego Kwadratowego ‚diagramu’ kolejne liczby (od 1 do 100) po czym formatowanie warunkowe mozna zawezic do –> formatuj jezeli wartosc mniejsza lub rowna… daje ten sam efekt (trzeba pamietac zeby formatowane bylo nie tylko tlo, ale takze kolor tekstu oraz aby kolor tekstu w samym arkuszu byl ustawiony na bialy…) – oczywiscie dziala tylko przy zalozeniu ze nie wykorzystujemy naszego ‚diagramu’ do wprowadzania jakichkolwiek danych.

pozdrawiam

Leave a Comment

Previous post:

Next post: