Wcześniej w Podcastu 2093 pokazałem proste sortowanie VBA, które działa, jeśli nie sortujesz według kolorów. Dzisiaj Neeta prosi VBA o sortowanie danych Excela według kolorów.
Najtrafniejszą rzeczą w sortowaniu według VBA jest ustalenie, których kodów kolorów RGB używasz. W 99% przypadków nie wybrałeś koloru wprowadzając wartości RGB. Wybrałeś kolor za pomocą tego menu rozwijanego w programie Excel.
I chociaż możesz użyć Fill, More Colors, Custom, aby dowiedzieć się, że wybrany kolor to RGB (112,48,160), jest to kłopotliwe, jeśli masz dużo kolorów.
Tak więc - wolę włączyć rejestrator makr i pozwolić rejestratorowi makr odgadnąć kod. Kod generowany przez rejestrator makr nigdy nie jest doskonały. Oto film pokazujący, jak korzystać z rejestratora makr podczas sortowania według kolorów.
Transkrypcja wideo
Naucz się programu Excel z podcastu, odcinek 2186: sortowanie VBA według koloru.
Hej, witaj z powrotem w netcastie, jestem Bill Jelen. Dzisiejsze pytanie wysłane na YouTube. Miałem film pokazujący, jak sortować za pomocą VBA, a oni chcieli sortować według kolorów za pomocą VBA, co jest znacznie bardziej skomplikowane. Powiedziałem: „Dlaczego po prostu nie włączysz rejestratora makr i nie zobaczysz, co się stanie?” I, niestety, rejestrator makr, wiecie, zbliża nas do siebie, ale nie prowadzi nas do końca.
Zobacz, makra, zarejestruj makro, „HowToSortByColor”, przechowuj makro w tym skoroszycie - doskonale. Kliknij OK. W porządku, więc teraz rejestrator makr jest uruchomiony, przejdziemy do karty Dane i powiemy Sortuj. Będziemy używać okna dialogowego Sortowanie i zbudujemy to, dobrze? Więc powiemy, że chcemy dodać poziom, Sortuj według Cherry, ale nie Sortuj według wartości komórek; zamierzamy posortować według koloru komórki - Kolor komórki to kolor wypełnienia - i chcemy umieścić czerwony na górze, a następnie skopiować ten poziom i umieścić żółty jako drugi; a potem dodamy nowy poziom - przejdziemy do kolumny D, kolumny z datami - Sortuj według koloru komórki, najpierw czerwony, skopiuj ten poziom, żółty, a potem tutaj; a potem, tutaj, w Elderberry, kolumna E, jest kilka niebieskich czcionek, nie chcę widzieć, jak to wyglądało,więc dodamy to jako kolor Sortuj według czcionki z niebieskim na górze; a jeśli to wszystko jest remisem bez żadnych kolorów, dodamy ostatni poziom tylko w kolumnie A - Wartości komórek, od największych do najmniejszych; i kliknij OK.
W porządku, teraz kilka rzeczy - nie pomijaj tego następnego kroku - twój plik, teraz, gwarantuję, że jest przechowywany jako xlsx. To świetny moment, aby zrobić Plik, Zapisz jako i zapisać jako xlsm lub xlsb. Jeśli tego nie zrobisz, cała Twoja praca do tego momentu zostanie utracona po zapisaniu tego pliku. Będą usuwać makra wszystkiego, co jest zapisane w xlsx. W porządku?
Więc przestaliśmy tam nagrywać, a potem chcemy spojrzeć na nasze makra. Możesz więc to zrobić za pomocą View, Macros - View, Macros - i znaleźć makro, które właśnie zarejestrowaliśmy - HowToSortByColor - i kliknąć Edit. W porządku, więc oto nasze makro, a kiedy na to patrzę, problem polega na tym, że dzisiaj mamy 25 wierszy plus nagłówek. Tak więc sprowadza się do wiersza 26. I zakodowali na stałe, że zawsze będą patrzeć w dół do wiersza 26.
Ale kiedy o tym myślę, zwłaszcza w porównaniu ze starym VBA do sortowania, nie musimy określać całego zakresu - tylko jedną komórkę w kolumnie. Więc gdziekolwiek mają kolumnę C26, zamierzam zredukować ją do zwykłego stwierdzenia: „Hej, nie, spójrz na pierwszą komórkę w tej kolumnie”. Więc E2, a potem tutaj A2. Więc w moim przypadku miałem 1, 2, 3, 4, 5, 6 poziomów sortowania - 6 rzeczy do zmiany.
I to jest ta część, w której rejestrator makr robi się naprawdę, bardzo zły, polega na tym, że będą sortować tylko wiersze 26 przez cały czas. Więc zamierzam to zmienić. Powiem: „Patrz, zacznij od zakresu A21 i rozszerz go na .CurrentRegion”. Rzućmy okiem na program Excel i zobaczmy, co to robi. Tak więc, gdybym po prostu wybrał jedną komórkę - A1 lub cokolwiek - i wcisnął Ctrl + *, zaznaczył bieżący region. OK, zróbmy to. Tutaj, od środka, Ctrl + *, i to, co to robi, to rozciąga się we wszystkich kierunkach, aż dotknie krawędzi arkusza kalkulacyjnego, na górze arkusza kalkulacyjnego lub na prawej krawędzi danych lub dolnej krawędzi danych . Mówiąc więc A1 .CurrentRegion, przypomina to przejście do A1 i naciśnięcie Ctrl + *. W porządku? Więc tutaj musisz to zmienić. Teraz wszystko inne w makro jest w porządku; to'wszystko idzie do pracy. Dostali SortOnCellColor i SortOnFontColor i xlSortOn. Nie muszę się o to martwić; wszystko, co muszę zrobić, to zajrzeć tutaj i zobaczyć, że zakodowali na stałe region, którego zamierzali użyć dla zakresu, zakodowali na stałe, jak daleko zaszły, i nie musi to być zakodowane na stałe. Dzięki temu prostemu krokowi, zmianie tych sześciu pozycji i siódmej pozycji, mamy coś, co powinno działać.
Teraz zróbmy test. Wróćmy tutaj do Excela i dodamy kilka nowych wierszy na dole. Po prostu wstawię tam 11 i dodamy kilka czerwonych - czerwony, żółty, a następnie niebieski. W porządku. Więc jeśli uruchomimy ten kod - uruchommy ten kod, więc klikam tutaj i klikam przycisk Uruchom - a potem wrócimy, powinniśmy zobaczyć, że ta 11 stała się pierwszym elementem na czerwono, pokazała się tam w żółte i pojawia się w bluesie, więc wszystko działa idealnie. Dlaczego trafił na szczyt? Ponieważ zdarzyło się, że ostatni rodzaj to kolumna A, więc kiedy jest remis, wygląda na to, że kolumna A jest rozstrzygająca. Więc ten kod działa.
Aby nauczyć się pisać w języku VBA, wraz z Tracy Syrstad napisałem serię książek, Excel VBA i MACROS. Jest już wydanie dla 2003, 2007, 2010, 2013 i 2016; wkrótce 2019. W porządku, więc znajdź wersję, która pasuje do twojej wersji programu Excel, a to przyspieszy naukę.
Podsumowanie: Dzisiejszy odcinek to, jak używać VBA do sortowania według koloru. Najłatwiej to zrobić, zwłaszcza że nie wiesz, jakie kody RGB zostały użyte dla każdego z kolorów - po prostu wybrałeś czerwony, nie wiesz, jaki jest kod RGB i nie chcesz patrzeć to up - włącz rejestrator makr za pomocą View, Macros, Record New Macro. Po zakończeniu sortowania kliknij Zatrzymaj nagrywanie - znajduje się w lewym dolnym rogu - Alt + F8, aby wyświetlić listę makr lub Widok, Makra, Wyświetl makro - zakładka Widok, Makra i następnie Wyświetl makra - to jest mylące. PSWybierz makro i kliknij Edytuj, a za każdym razem, gdy zobaczysz C2 do jakichś numerów zakresów, po prostu zmień je tak, aby wskazywały na wiersz 2. Następnie, gdy określają zakres do sortowania, Zakres („A1”), BieżącyRegion, rozszerzy się. W porządku.
Cóż, hej, chcę ci podziękować za zatrzymanie się, do zobaczenia następnym razem z kolejnym netcastem od.
W filmie ustawiłem sortowanie na sześciu poziomach. Końcowe okno dialogowe jest pokazane tutaj:
W dniu, w którym nagrałem makro, miałem 23 wiersze danych plus nagłówek. W makrze było siedem miejsc, które na stałe zakodowały liczbę wierszy. Trzeba je dostosować.
Dla każdego poziomu sortowania istnieje następujący kod:
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2:C24"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 0, 0) To głupie, że rejestrator makr określa C2: C24. Musisz tylko określić jedną komórkę w kolumnie, więc zmień pierwszą linię powyżej na:
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2"), _Wprowadź podobną zmianę dla każdego poziomu sortowania.
Pod koniec nagranego makra masz nagrany kod, aby wykonać sortowanie. Zaczyna się tak:
With ActiveWorkbook.Worksheets("Sheet2").Sort .SetRange Range("A1:E24") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End WithZamiast sortować tylko A1: E24, zmień kod tak, aby rozpoczynał się w A1 i rozszerzał na bieżący region. (Bieżący region jest tym, co otrzymasz, jeśli naciśniesz Ctrl + * z komórki).
.SetRange Range("A1").CurrentRegionOstateczny kod pokazany na filmie to:
Sub HowToSortByColor() HowToSortByColor Macro ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear ' Sort column C by Red ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 0, 0) ' Sort Column C by Yellow ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 255, 0) ' Sort column D by Red ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("D2"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 0, 0) ' Sort column D by Yellow ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("D2"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 255, 0) ' Sort column E by blue font ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("E2"), _ xlSortOnFontColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(0, 176, 240) ' Sort Column A by Values descending ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("A2"), _ SortOn:=xlSortOnValues, _ Order:=xlDescending, _ DataOption:=xlSortNormal ' Perform the Sort With ActiveWorkbook.Worksheets("Sheet2").Sort .SetRange Range("A1").CurrentRegion .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End SubUwaga
Prawdopodobnie skoroszyt został zapisany z rozszerzeniem XLSX. Zapisz jako, aby zmienić na rozszerzenie XLSM lub XLSB. Wszelkie makra zapisane w XLSX są usuwane.
Myśl dnia Excela
Poprosiłem moich znajomych z programu Excel Master o radę dotyczącą programu Excel. Dzisiejsza myśl do rozważenia:
„Jabłko na dzień nie pozwala VBA”.
Tom Urtis
