Jak okiełznać kolory w arkuszach?

Problem pojawia się jak robimy katalog i chcemy zilustrować produkty kolorami. Np. żeby pokazać cały zestaw kolorów nadwozia, albo kolorów oprawek do piór, albo koszulek klubowych. Możemy do tego używać zdjęć, ale nie zawsze jest to odpowiednie. No i nie zawsze zdjęcia mamy pod ręką.

Wpis warto czytać razem z plikiem roboczym Google Sheets. w którym znajdziecie zamieszczone tu przykłady.

Zakładam, że czytając tego bloga wiesz jak standardowo ustawić kolory tła lub tekstu w menu Arkuszy Google (dla zapominalskich przypominam – trzeba kliknąć na jedną z ikon).

W ten sposób możemy zmienić tło lub kolor tekstu w bieżącej komórce lub zakresie. W wielu przypadkach to wystarczy, ale chcemy się zająć tymi sytuacjami, kiedy warto sięgnąć po bardziej zaawansowane narzędzia. Kiedy wierszy są grube dziesiątki lub setki.

Jak wypełnić komórkę kolorem na podstawie tabeli kolorów?

Zacznę od tego jak zrobić to na podstawie kodu Hex. To taki kod w systemie szesnastkowym, którego używa się do opisania kolorów w sieci. Listę prezentuję powyżej. Możecie ją znaleźć w wikipedii.

Jeśli szukamy możliwości pokolorowania komórki na podstawie formuły i kodu, dobrym sposobem wydaje mi się użycie formuły SPARKLINE. To formuła służąca do tworzenia małych wykresów w komórkach. Tutaj zbudujemy wykres słupkowy zajmujący całą szerokość kolumny. Ważne, że SPARKLINE pozwala wybrać kolor słupka.

Idąc dalej – za pomocą funkcji VLOOKUP możemy czytać zawartość kolumny C i wewnątrz funkcji SPARKLINE przypisywać odpowiednie wartości kodu. Wygląda to tak:

Tworzenie roboczej listy kolorów

Na początek poradzimy sobie z tym blondem. Żeby został rozpoznany przez formułę, musi się pojawić w tabeli G2:H, wraz z kodem Hex. W tym celu możemy poszukać najbardziej pasującego nam odpowiednika. Krótkie grzebnięcie w sieci pokazało, że istnieje taki kolor w jęz. angielskim i jego kodem jest #faf0be.

Dopisuję więc go na szczycie listy.

Jak wyświetlić 2 kolory w jednej komórce?

Tu już musimy nieco namieszać. Funkcja SPARKLINE pozwala na wyświetlanie maksymalnie dwóch kolorów słupków, więc możemy jej użyć, ale musimy sobie poradzić tak by jedna formuła obsługiwała nam zarówno sytuacje, gdy mamy jeden kolor jak i dwa kolory.

Zaczniemy od tego, że rozdzielimy te dwa kolory przy pomocy funkcji SPLIT

Ponieważ będziemy musieli podstawić te wartości jako color1 i color2 do formuły SPARKLINE – nie możemy utrzymać tego w obecnej postaci. Musimy wyodrębniać najpierw sam pierwszy kolor do podstawienia, a następnie kolor drugi (ecru).

Pozostaje elementy powstałej tabelki podstawić do funkcji VLOOKUP wyszukującej kodów i do funkcji SPARKLINE budującej wykres słupkowy. Funkcja będzie wyglądać tak.

=sparkline({1;1}; {"charttype"\"bar"; "color1"\vlookup(index(split(C14;"/");1;1);$G$2:$H;2;false); "color2"\vlookup(index(split(C14;"/");1;2);$G$2:$H;2;false)})

Jak widać na obrazku – wyświetliło nam zarówno kolor kasztanowy jak i ecru. Ale jeśli podstawimy ją do wiersza tabeli, gdzie będzie tylko jeden kolor – wysypie się. Musimy ją uodpornić.

Postanowiłem, że najlepiej będzie jak w przypadku spotkania jednego koloru, funkcja wyświetli oba słupki w tym samym kolorze. Za pierwszym razem podstawimy pierwszą wartość z tabelki zrobionej przez split, a za drugim razem ostatnią wartość (równocześnie ilość kolumn). W przypadku gdy tabela będzie miała tylko jedną kolumnę, wartość pierwsza i ostatnia będzie tą samą.

W tym celu muszę nauczyć moją funkcję liczyć kolumny w tabelce robionej przez SPLIT. Używam do tego funkcji COLUMNS:

Pozostaje do funkcji INDEX jako numer drugiej kolumny podstawić wynik działania funkcji COLUMNS i uzyskujemy uniwersalne narzędzie tworzące ilustrację graficzną nazw kolorów. Uniwersalne to mocno powiedziane, ale radzi sobie z dwoma 🙂

Kto się dobrze przyjrzy tabeli, zobaczy, że w wierszu 10 mamy brązowy/złoty/srebrny i środkowy kolor został pominięty. Dlatego zbudujemy inne narzędzie. Już nie oparte o SPARKLINE.

Ale najpierw…

Budujemy uniwersalną tabelę kolorów!

Zaczniemy od tego, że przygotujemy sobie rozbudowaną tabelę kolorów, działającą zarówno dla nazw polskich, angielskich jak i nazw własnych, które będziemy wprowadzać sami według potrzeb.

Przyznaję, że potrzebne mi to było przy tworzeniu cennika skarpet. W cenniku są malutkie zdjęcia, a na nich ledwo co widać, więc ikony wydały mi się lepsze, a nazwy po angielsku słabiej działają niż ilustracja odcienia. Problem w tym, że producent używał czasem fantazyjnych nazw barw lub zdarzało się też, że materiał nie był gładki, tylko z tzw. melanżem (mieszanie 2 odcieni) i z tym też należało sobie poradzić.

Sięgnąłem więc do wikipedii i oprócz listy polskich barw, pociągnąłem listę w jęz. angielskim. Dodałem też kolumny do dopisywania własnych nazw i kolumnę w której wrzucam ikony barw melanżowych.

Tabelę znajdziecie w pliku roboczym – w zakładce ‘tabela z obrazkami’

Jak można uzyskać próbki kolorów?

Rozejrzałem się po internecie w poszukiwaniu jakiegoś serwera, który by udostępniał tabele barwne z obrazkami kolorów, w których nazwach będą ich kody HEX. Okazało się, że serwis colorhexa.com ma takie pliki. Obejrzałem ich adresy i okazały się bardzo proste. Każdy obrazek ma adres w postaci https://www.colorhexa.com/[kod hex tutaj].png

W ten sposób udało mi się zaciągnąć obrazki do mojego pliku. Skorzystałem z funkcji IMAGE.

Po ściągnięciu próbek z serwera najlepiej jeśli skopiujemy próbki do pliku – nie chcemy nadużywać gościnności 🙂 . W tym celu należy zaznaczyć kolumny i zrobić Kopiuj, a potem Wklej specjalnie –> Tylko wartości. Teraz próbki będą już w naszym pliku. Odwołania do serwera warto zostawić w tabelach, które planujemy jeszcze uzupełniać.

Łączenie kilku tabel kolorów

Jak widzicie, tabel jest kilka i część z nich jest już stała – wygenerowana raz i nie będą już ruszane (tabela angielska i polska), natomiast do tabeli własnej i do tabeli z melanżami będę jeszcze dodawał próbki. Ważne dla mnie jest, żeby w czasie przeszukiwania najpierw szukać w tabeli własnej, a potem dopiero w tabelach stałych. Chodzi o to, że jak nie będę zadowolony ze standardowego wyglądu np. koloru turusowego to mogę dodać własny, ale nie muszę nic kasować czy modyfikować w tabeli z wikipedii.

W tym celu łączę wszystkie tabele w jedną. W takiej kolejności, by najważniejsze były na górze, a mniej ważne na dole. Ta połączona tabela będzie moją wzorcową paletą barw, której użyję do wszystkich moich plików.

Żeby łatwiej się odwoływać do mojej tabeli kolorów, nadaję jej nazwę: tabelakolow – od tej pory tak będzie widoczna w formułach.

Pobranie próbki koloru z tabeli barw

Teraz jak mamy już zdefiniowaną tabelę, możemy sięgać po próbki kolorów na podstawie nazw własnych, polskich, angielskich .

Dostęp do tabeli kolorów z innego pliku

Pamiętajmy, że do tabeli możemy sięgnąć korzystając z innego pliku. W tym celu należy poszerzyć nazwę przeszukiwanej tabeli o jej url i umieścić w funkcji IMPORTRANGE.

=vlookup(C19; importrange("[tu adres pliku z tabelą]";"tabelakolorow");3;false)

Uzbrojeni w uniwersalną tabelę wracamy do naszych przykładów:

Jak sobie poradzić, gdy w komórce jest kilka kolorów? Np. czerwony/zielony/niebieski/żółty?

Tym razem zajmiemy się tworzeniem obrazków, które będą reprezentować dany kolor. I nie będziemy pakować po kilka w jedną komórkę. Jeśli będzie jeden kolor – zajmiemy jedną komórkę, a jak 4 to 4.

Pobieramy kilka próbek kolorów

Jak widzimy część przedmiotów jest jednokolorowych, ale część ma po 2 a nawet 3 kolory. Dlatego żeby ogarnąć wszystkie, najpierw musimy podzielić komórki zawierające spis kolorów przy pomocy funkcji SPLIT

Skoro mamy wyprodukowane tabele, to możemy przy pomocy ARRAYFORMULA i VLOOKUP odwołać się do każdego elementu po kolei.

W ten sposób możemy stworzyć dowolnie długi zestaw kolorów. A wszystko zamyka się w jednym poleceniu.

Jak widać, formuła działa nawet gdy mamy tylko jeden kolor.

Podmieńmy kolor na ładniejszy

Zauważyliście, że kolor ‘złoty’ napisany po polsku jest taki żywo żółty, a z angielskiej tabeli ‘gold’ jest jakiś taki ciemny? Jeśli nam się nie podoba, możemy we własnej tabeli kolorów podmienić go.

Teraz jest już lepiej:

Jak poradzić sobie gdy kolory są w rodzaju żeńskim lub nijakim. Albo generalnie pomieszane rodzajami?

W języku polskim większość przymiotników w zależności od rodzaju różni się tylko ostatnią literą. czerwona/czerwony/czerwone. Zatem możemy spróbować do funkcji VLOOKUP podstawiać nazwy kolorów bez ostatniej litery i z tabeli brać też kolumnę identyfikującą kolor – bez ostatniego znaku.

Jeśli lista jest krótka, możemy oczywiście ręcznie dopisać odpowiedniki.

Jak wziąć wszystkie znaki prócz ostatniego?

Ponieważ za moment będziemy wyszukiwać tych skróconych przymiotników w tabeli kolorów, powinniśmy dorobić je do tabeli w języku polskim.

Dodaję więc drugą polską tabelę, tylko z lekko okaleczonymi nazwami. Jest ona wrzucona poniżej pierwszej.

Od tej pory możemy wyszukiwać nazwy kolorów niezależnie od rodzaju.

Jak wyciągnąć nazwy kolorów z nazw produktów?

Jeśli widzimy, że nazwy kolorów czasem pojawiają się na początku, czasem w środku nazw, a naszym zadaniem jest zrobienie ikon kolorystycznych, możemy zrobić wyszukiwarkę, która zignoruje wszystko co nie jest nazwą koloru.

Połączymy tu wszystkie wcześniejsze metody, czyli usuwanie ostatniego znaku, krojenie na kawałki funkcją split, sięganie do uniwersalnej tabeli.

Niestety nie wszystko się udało. Jeśli kolor jest drugim słowem, to przed nim pojawia się druga komórka. Podobnie jest z dłuższymi nazwami – robią się niepotrzebne puste komórki. Musimy je wyfiltrować funkcją FILTER. UWAGA – zrobi się długo. Ale to będzie tylko zagmatwana forma funkcji mówiącej =FILTER(funkcja robiąca dużo komórek;funkcja robiąca dużo komórek <>"")

Dlaczego w tym wszystkim ominąłem Formatowanie Warunkowe i skrypty?

Formatowanie warunkowe pozwala ciekawie określać to która komórka ma zostać sformatowana, ale kontrola nad tym JAK ją sformatować jest możliwa jedynie poprzez menu. Zatem jeśli byśmy chcieli formatować tło komórki na bazie zawartości, musielibyśmy wpisać tyle reguł ile jest kolorów. A to ani eleganckie, ani zimowe wieczory nie są tak długie.

Możemy to zrobić przy pomocy skryptu, tylko w tym blogu postanowiłem omijać skrypty ze względu na to, że wymagają one przejścia przez wszystkie autoryzacje Google Sheets, a to odstrasza wielu użytkowników.