Obejrzyj wideo - wypełnianie w programie Excel
Wiele razy możesz napotkać zestaw danych, w którym tylko jedna komórka jest wypełniona danymi, a komórki poniżej są puste do następnej wartości.
Coś, jak pokazano poniżej:
Chociaż ten format działa w przypadku niektórych osób, problem z tego rodzaju danymi polega na tym, że nie można go używać do tworzenia tabel przestawnych ani używać ich w obliczeniach.
A to ma łatwe rozwiązanie!
W tym samouczku pokażę Ci, jak szybko wypełnić komórki w Excelu do następnej wypełnionej wartości.
Możesz to łatwo zrobić, używając prostej techniki specjalnego okna dialogowego Przejdź do, VBA lub Power Query.
Więc zacznijmy!
Metoda 1 - Wypełnij za pomocą Idź do Special + Formuła
Załóżmy, że masz zestaw danych, jak pokazano poniżej i chcesz wypełnić dane w kolumnie A i kolumnie B.
W kolumnie B celem jest wypełnienie „Drukarki” do ostatniej pustej komórki pod nią, a następnie, gdy uruchomi się „Skaner”, a następnie wypełnij „Skaner” w komórkach poniżej, aż komórki będą puste.
Poniżej czynności, których należy użyć, przejdź do specjalnego, aby zaznaczyć wszystkie puste komórki i wypełnić za pomocą prostej formuły:
- Wybierz dane, które chcesz wypełnić (w naszym przykładzie A1:D21)
- Przejdź do zakładki „Strona główna”
- W grupie Edytowanie kliknij ikonę „Znajdź i wybierz” (pokaże to więcej opcji w menu rozwijanym)
- Kliknij opcję „Idź do”
- W wyświetlonym oknie dialogowym Przejdź do kliknij przycisk Specjalne. Spowoduje to otwarcie okna dialogowego „Przejdź do specjalnego”
- W oknie dialogowym Przejdź do specjalnego kliknij Puste
- Kliknij OK
Powyższe kroki zaznaczyłyby wszystkie puste komórki w zestawie danych (jak pokazano poniżej).
W zaznaczonych pustych komórkach zauważysz, że jedna komórka jest jaśniejsza niż reszta zaznaczenia. Ta komórka jest aktywną komórką, w której zamierzamy wprowadzić formułę.
Nie martw się, gdzie komórka znajduje się w zaznaczeniu, ponieważ nasza metoda zadziała w każdym przypadku.
Poniżej znajdują się kroki, aby wypełnić dane w wybranych pustych komórkach:
- Naciśnij klawisz równości (=) na klawiaturze. Spowoduje to wstawienie znaku równości w aktywnej komórce
- Naciśnij klawisz strzałki w górę. Spowoduje to wstawienie odwołania do komórki powyżej aktywnej komórki. Na przykład w naszym przypadku komórką aktywną jest B3, a po wykonaniu tych dwóch kroków w komórce wpisuje się =B2
- Przytrzymaj klawisz Control i naciśnij klawisz Enter
Powyższe kroki automatycznie wstawią wszystkie puste komórki z wartością nad nimi.
Chociaż może to wyglądać na zbyt wiele kroków, gdy opanujesz tę metodę, będziesz mógł szybko wypełnić dane w programie Excel w ciągu kilku sekund.
Teraz są jeszcze dwie rzeczy, o które musisz zadbać podczas korzystania z tej metody.
Konwertuj formuły na wartości
Pierwszym z nich jest upewnienie się, że konwertujesz formuły na wartości (aby mieć wartości statyczne i nie zepsuć się w przypadku zmiany danych w przyszłości).
Poniżej znajduje się wideo, które pokaże, jak szybko konwertować formuły na wartości w programie Excel.
Zmień format daty
Jeśli używasz dat w swoich danych (tak jak ja w przykładowych danych), zauważysz, że wypełnione wartości w kolumnie daty są liczbami, a nie datami.
Jeśli dane wyjściowe w kolumnie daty są w żądanym formacie daty, jesteś dobry i nie musisz robić nic więcej.
Ale jeśli nie są one w prawidłowym formacie daty, musisz dokonać niewielkiej korekty. Chociaż masz prawidłowe wartości, wystarczy zmienić format, aby był wyświetlany jako data w komórce.
Poniżej znajdują się kroki, aby to zrobić:
- Wybierz kolumnę z datami
- Kliknij kartę Strona główna
- W grupie Numer kliknij menu rozwijane Format, a następnie wybierz format daty.
Jeśli potrzebujesz zrobić to od czasu do czasu, polecam użyć tej specjalnej techniki i formuły Go-To.
Chociaż składa się z kilku kroków, jest prosty i daje wynik bezpośrednio w zestawie danych.
Ale jeśli musisz to robić dość często, sugeruję zapoznanie się z metodami VBA i Power Query omówionymi dalej
Metoda 2 - Wypełnij za pomocą prostego kodu VBA
Możesz użyć naprawdę prostego kodu makra VBA, aby szybko wypełnić komórki w Excelu do ostatniej pustej wartości.
Wystarczy tylko raz dodać kod VBA do pliku, a następnie możesz łatwo ponownie użyć kodu wiele razy w tym samym skoroszycie lub nawet w wielu skoroszytach w swoim systemie.
Poniżej znajduje się kod VBA, który przejdzie przez każdą komórkę w zaznaczeniu i wypełni wszystkie puste komórki:
'Ten kod VBA jest tworzony przez Sumit Bansal z TrumpExcel.com Sub FillDown() For Each cell In Selection If cell = "" Then cell.FillDown End If Next End Sub
Powyższy kod używa pętli For do przejścia przez każdą komórkę w zaznaczeniu.
W pętli For użyłem warunku If-Then, który sprawdza, czy komórka jest pusta, czy nie.
W przypadku, gdy komórka jest pusta, jest wypełniana wartością z komórki powyżej, a jeśli nie jest pusta, pętla for po prostu ignoruje komórkę i przechodzi do następnej.
Teraz, gdy masz kod VBA, pokażę ci, gdzie umieścić ten kod w programie Excel:
- Wybierz dane, które chcesz wypełnić
- Kliknij kartę Deweloper
- Kliknij ikonę Visual Basic (lub możesz użyć skrótu klawiaturowego ALT + F11). Spowoduje to otwarcie Edytora Visual Basic w programie Excel
- W Edytorze Visual Basic po lewej stronie znajduje się Eksplorator projektów. Jeśli go nie widzisz, kliknij opcję „Widok” w menu, a następnie kliknij Eksplorator projektów
- Jeśli masz otwartych wiele skoroszytów programu Excel, Eksplorator projektów wyświetli wszystkie nazwy skoroszytów. Znajdź nazwę skoroszytu, w którym masz dane.
- Kliknij prawym przyciskiem myszy dowolny obiekt w skoroszycie, przejdź do Wstaw, a następnie kliknij Moduł. Spowoduje to wstawienie nowego modułu do tego skoroszytu
- Kliknij dwukrotnie „Moduł”, który właśnie wstawiłeś w powyższym kroku. Otworzy się okno kodu dla tego modułu
- Skopiuj i wklej kod VBA do okna kodu
- Umieść kursor w dowolnym miejscu w kodzie, a następnie uruchom kod makra, klikając zielony przycisk na pasku narzędzi lub używając skrótu klawiaturowego F5
Powyższe kroki uruchomią kod VBA, a Twoje dane zostaną wypełnione.
Jeśli chcesz ponownie użyć tego kodu VBA w przyszłości, musisz zapisać ten plik jako skoroszyt programu Excel z obsługą makr (z rozszerzeniem .XLSM)
Inną rzeczą, którą możesz zrobić, to dodać to makro do paska narzędzi szybkiego dostępu, który jest zawsze widoczny i będziesz mieć dostęp do tego makra za pomocą jednego kliknięcia (w skoroszycie, w którym masz kod w zapleczu).
Tak więc następnym razem, gdy będziesz mieć dane, w których musisz wypełnić, wszystko, co musisz zrobić, to dokonać wyboru, a następnie kliknąć przycisk makra na pasku narzędzi szybkiego dostępu.
Możesz również dodać to makro do skoroszytu makr osobistych, a następnie używać go w dowolnym skoroszycie w systemie.
Kliknij tutaj, aby dowiedzieć się więcej o osobistych skoroszytach makr i sposobie dodawania do nich kodu.
Metoda 3 - Wypełnij za pomocą Power Query
Dodatek Power Query ma wbudowaną funkcję, która umożliwia wypełnianie za pomocą jednego kliknięcia.
Korzystanie z dodatku Power Query jest zalecane, gdy mimo to używasz go do przekształcania danych lub łączenia danych z wielu arkuszy lub wielu skoroszytów.
W ramach istniejącego przepływu pracy możesz użyć opcji wypełniania w dodatku Power Query, aby szybko wypełnić puste komórki.
Aby korzystać z dodatku Power Query, zaleca się, aby dane były w formacie tabeli programu Excel. Jeśli nie możesz przekonwertować danych na tabelę programu Excel, musisz utworzyć nazwany zakres danych, a następnie użyć tego nazwanego zakresu w dodatku Power Query.
Poniżej mam zestaw danych, który już przekonwertowałem na tabelę Excela. Możesz to zrobić, wybierając zestaw danych, przechodząc do karty Wstaw, a następnie klikając ikonę Tabela.
Poniżej kroki, aby użyć dodatku Power Query do wypełnienia danych do następnej wartości:
- Wybierz dowolną komórkę w zestawie danych
- Kliknij kartę Dane
- W grupie Pobierz i przekształć dane kliknij „Z arkusza”. Spowoduje to otwarcie edytora Power Query. Zwróć uwagę, że puste komórki w dodatku Power Query będą zawierać wartość „null”
- W edytorze Power Query wybierz kolumny, które chcesz wypełnić. Aby to zrobić, przytrzymaj klawisz Control, a następnie kliknij nagłówek kolumn, które chcesz wybrać. W naszym przykładzie byłyby to kolumny Data i Produkt.
- Kliknij prawym przyciskiem myszy dowolny z wybranych nagłówków
- Przejdź do opcji „Wypełnij”, a następnie kliknij „W dół”. Spowoduje to wypełnienie danych we wszystkich pustych komórkach
- Kliknij kartę Plik, a następnie kliknij „Zamknij i załaduj”. Spowoduje to wstawienie nowego arkusza roboczego do skoroszytu z wynikową tabelą
Chociaż ta metoda może wydawać się nieco przesadna, jedną wielką zaletą korzystania z dodatku Power Query jest to, że umożliwia szybkie aktualizowanie danych wynikowych w przypadku zmiany oryginalnych danych.
Na przykład, jeśli dodasz więcej rekordów do oryginalnych danych, nie musisz ponownie wykonywać wszystkich powyższych czynności. Możesz po prostu kliknąć prawym przyciskiem myszy wynikową tabelę, a następnie kliknąć Odśwież.
Chociaż metoda Power Query działa dobrze, najlepiej jest używać jej, gdy używasz już dodatku Power Query w swoim przepływie pracy.
Jeśli masz tylko zestaw danych, w którym chcesz wypełnić puste komórki, wygodniejsze byłoby użycie specjalnej metody Przejdź do lub metody VBA (opisanej powyżej).
Oto trzy proste sposoby, których możesz użyć do wypełniaj puste komórki do następnej wartości w Excelu.
Mam nadzieję, że ten samouczek okazał się przydatny.
