Obecnie problemem są komórki scalone, ale są to komórki scalone w pionie. Spójrz na poniższy rysunek. Jeśli istnieje 7 wierszy dla regionu Midwest, słowo Midwest pojawia się tylko w pierwszym wierszu. Ktoś następnie łączy pozostałe sześć pustych komórek z komórką Midwest, aby utworzyć scaloną komórkę o wysokości 7 komórek.
To okropny sposób przesyłania danych. Jeśli ktoś jest biegły w programie Excel, może chcieć posortować dane, dodać sumy częściowe, skorzystać z tabel przestawnych. Zapobiegają temu scalone komórki.
Co gorsza, każdego dnia otrzymujesz z centrali nowy plik o takiej strukturze. Centrala wysyła podobne pliki do 500 oddziałów. Będąc tylko jedną małą placówką, nie masz zbyt wielu szans na przekonanie centrali, że jest to okropny sposób przesyłania danych.
Dzisiejszy artykuł dotyczy szybkiego rozwiązania tego problemu za pomocą dodatku Power Query. Rozwiązanie Power Query będzie łatwiejsze w dniach od 2 do 9999 niż następujące kroki w programie Excel:
Guru programu Excel mogą codziennie sugerować następujące kroki:
- Zaznacz wszystkie komórki, wybierając prostokąt powyżej i po lewej stronie A1.
- Kliknij program uruchamiający okno dialogowe w prawym dolnym rogu grupy Wyrównanie na karcie Strona główna.
- Kliknij dwukrotnie pole Scal komórki, aby je odznaczyć.
- Kliknij OK, aby zamknąć okno dialogowe Formatowanie komórek
- Wybierz koniec kolumny A z powrotem do A1.
- Strona główna, Znajdź i wybierz, Przejdź do specjalnych, Puste, OK
- Type = UpArrow. Naciśnij Ctrl + Enter
- Wybierz koniec kolumny A z powrotem do A1.
- Ctrl + C, aby skopiować. Kliknij prawym przyciskiem myszy i wklej wartości
Ale jest o wiele łatwiejszy sposób. Nowe narzędzia Power Query są wbudowane w wersje Office 365 i Excel 2016 dla systemu Windows. Jeśli masz wersję programu Excel 2010 lub Excel 2013 dla systemu Windows, możesz bezpłatnie pobrać dodatek Power Query od firmy Microsoft.
Oto strategia dotycząca dodatku Power Query:
- Zrób plan, że będziesz zapisywać skoroszyt każdego dnia z centrali w tym samym folderze o tej samej nazwie.
- Otwórz nowy pusty skoroszyt, w którym będą przechowywane stałe dane z centrali.
-
W pustym skoroszycie wybierz Dane, Pobierz dane, Z pliku, Ze skoroszytu.
Uruchom proces Power Query - Przejdź do folderu i pliku z kroku 1.
-
Po otwarciu okna Power Query zwróć uwagę, że scalone komórki zniknęły. Masz Środkowy Zachód w wierszu 1, a następnie sześć komórek zawierających „null”. Wybierz tę kolumnę, klikając nagłówek Region.
Dodatek Power Query automatycznie usuwa scalone komórki -
W dodatku Power Query wybierz kartę Przekształcanie. Wybierz Wypełnij, W dół. Słowo Midwest jest kopiowane z wiersza 1 do wierszy od 2 do 7. Podobne transformacje zachodzą w całym zbiorze danych.
Wypełnij puste komórki wartością z góry -
Strona główna, zamknij i załaduj. Dodatek Power Query zostanie zamknięty. Po około 10-20 sekundach wyczyszczone dane z centrali pojawią się w nowym arkuszu w skoroszycie.
Zwróć dane do programu Excel - Zapisz skoroszyt pod nazwą taką jak CleanedDataFromHQ.xlsx.
- Po wybraniu danych powinieneś zobaczyć panel Zapytania i połączenia po prawej stronie. Jeśli nie widzisz panelu, przejdź do sekcji Dane, zapytania i połączenia.
-
Kliknij prawym przyciskiem myszy zapytanie w panelu Zapytania i połączenia. Wybierz Właściwości.
Otwórz Właściwości dla tego zapytania -
Wybierz „Odśwież dane podczas otwierania pliku”. Kliknij OK.
Ustaw zapytanie tak, aby było uruchamiane za każdym razem, gdy otwierasz skoroszyt.
Twój przepływ pracy staje się wtedy: (a) Odbierz skoroszyt z centrali pocztą e-mail. (b) Zapisz załącznik w odpowiednim folderze pod poprawną nazwą. (c) Otwórz skoroszyt CleanedDataFromHQ.xlsx. Nowe dane zostaną załadowane do skoroszytu.
Obejrzyj wideo
Transkrypcja wideo
Naucz się programu Excel z podcastu, odcinek 2221: Wypełnij scalone komórki w dół.
Hej, witaj ponownie w netcastie. Jestem Bill Jelen, byłem w Calumet City, Elżbieta pojawia się i mówi, że dostaje ten plik - nasz plik, taki jak ten - każdego dnia i jest okropny. Tutaj, w kolumnie A, nie chodzi tylko o to, że umieścili Środkowy Zachód i zostawili kilka pustych miejsc, to scalona komórka. W porządku? Wysyłają … Centrala wysyła te pliki do miejsc na całym świecie z tymi dziwnymi połączonymi komórkami. Połączone komórki są złe.
W porządku, oto jak się tego pozbyć. Po pierwsze, mam zamiar kliknąć prawym przyciskiem myszy, przenieść lub skopiować, utworzyć kopię i przenieść ją do nowej książki. Więc udajemy, że to jak zeszyt ćwiczeń, który dostaje Elizabeth. Plik, Zapisz jako i za każdym razem, gdy Elżbieta dostaje jeden z tych skoroszytów, chcę, żeby umieszczała go dokładnie w tym samym miejscu, pod tą samą nazwą. W porządku? Dokładnie ta sama nazwa - idealnie. Więc teraz mamy tylko ten pojedynczy plik, pojedynczy arkusz, zamknę plik. A potem utworzymy zupełnie nowy skoroszyt. Zamierzam wstawić tutaj nowy arkusz roboczy i ten nowy arkusz będzie nazywać się raportem. Na tym pustym polu nasz raport wykona dane, pobierz dane, z pliku, ze skoroszytu, wskaże plik, w którym zamierzamy go zapisać - więc za każdym razem z dokładnie taką samą nazwą - kliknij Importuj , wybierz pierwszy arkusz,a następnie kliknij Edytuj. W porządku. Po pierwsze, to jest cholernie niesamowite - funkcja Power Queret nie będzie miała żadnego z tych bzdur dotyczących scalania komórek, natychmiast przekształcają scalone komórki w pojedyncze komórki i wszystkie te komórki są zerowe. Wybierz tę kolumnę, jest już wybrana tutaj, a następnie Transform, Fill, Fill Down, w ten sposób, a następnie Home, Close & Load. W porządku, więc mam mój skoroszyt.
Teraz widzę, że niektóre mają tutaj dodatkowe kolumny. Pozwól mi kliknąć prawym przyciskiem myszy, a my będziemy edytować.
I wygląda na to, że wszystko po prawej stronie kosztów musi zniknąć. Ile jest tam dodatkowych rzeczy? Kliknij prawym przyciskiem myszy i usuń te kolumny - idealnie - zamknij i załaduj. W porządku, teraz przepływ pracy staje się taki, że za każdym razem, gdy Elizabeth otrzyma nowy raport z centrali, zapisze go w tym samym miejscu pod wiarygodną nazwą lub czymkolwiek - jakkolwiek - nazywamy to, a następnie otwieramy ten skoroszyt i następnie kliknij Odśwież.
Lub, jeśli chcemy pracować automatycznie, kliknij tutaj prawym przyciskiem myszy, przejdź do Właściwości, tuż poza ekranem, na samym dole, a następnie powiedz: Za każdym razem, gdy otwieram ten plik, odśwież dane. Więc za każdym razem, gdy otworzę ten skoroszyt, trafi on do nowego skoroszytu, który otrzymaliśmy z centrali, zastąpi te puste scalone komórki wartościami, które powinny tam być, i życie jest takie wspaniałe.
Power Query jest omówione w mojej książce, ale jest również zawarte w tej książce autorstwa Kena Pulsa i Miguela Escobara, M oznacza (DANE) MONKEY.
W porządku, więc Elizabeth: Jak pozbyć się komórek scalania w pionie? Moja centrala codziennie przesyła mi te pliki. Tak więc plan: Zapiszemy skoroszyt w wiarygodnym miejscu o wiarygodnej nazwie; utwórz pusty skoroszyt raportowania, Dane, Pobierz dane, Z pliku, Ze skoroszytu, określ arkusz, BAM! Komórki scalające zniknęły. Zaznaczam tę kolumnę i Wypełnij, Zamknij i wczytaj; następnie za każdym razem, gdy otrzymasz nowy skoroszyt, zapisz ten nowy skoroszyt w niezawodnym miejscu o tej samej niezawodnej nazwie; Otwieram skoroszyt raportowania i odświeżam lub po prostu ustawiamy skoroszyt raportowania na automatyczne odświeżanie po otwarciu.
Aby pobrać skoroszyt z dzisiejszego wideo, należy podać adres URL w opisie YouTube.
Chcę podziękować Elizabeth za udział w moim seminarium w Calumet City, chcę podziękować za zatrzymanie się. Do zobaczenia następnym razem z kolejnym netcastem od.
Pobierz plik Excel
Aby pobrać plik Excela: fill-scaled-cells-down.xlsx
Power Query to niesamowite narzędzie - pozwala rozwiązywać różne problemy
saeed Alimohammadi
