To jest problem z budżetem strażaków. Ludzie w remizie źle robili swoje budżety w programie Excel. Rozwiązanie zapewnia niesamowita transformacja dodatku Power Query.
Obejrzyj wideo
- Steve musi zsumować liczby wprowadzone w kolumnie tekstowej
- W każdej komórce znajduje się wiele linii oddzielonych alt = "" + Enter
- Trzeba podzielić te wiersze na wiersze, a następnie przeanalizować kwotę w dolarach ze środka każdej komórki
- Podsumuj według miejsca powstawania kosztów
- Utwórz tabelę przeglądową
- Uzyskaj sumy z tabeli przeglądowej, używając IFNA, aby zignorować błędy w pustym wierszu
- Bonus: Dodaj makro zdarzenia, aby zaktualizować arkusz roboczy po zmianie komórki.
Transkrypcja wideo
Naucz się programu Excel z, Podcast, odcinek 2160: SUMA danych, które zostały wprowadzone Alt +.
Hej. Witamy ponownie w netcast. Jestem Bill Jelen. Nie zmyślam. Mam pytanie od kogoś, kto ma dane - dane budżetowe - wyglądające tak. Teraz umieściłem tutaj fałszywe słowa, żebyśmy nie mieli informacji o ich budżecie, ale osoba jest nowa w dziale księgowości, poszła do firmy, a ta firma od lat robi swoje budżety w ten sposób. Oni nie są księgowymi zajmującymi się budżetem, są ludźmi liniowymi, ale oni to robią, a on nie może ich zmusić do zmiany. Więc oto nasz cel. Mówi, że jest to tak samo złe, jak wpisywanie budżetu w programie Word.
Cóż, prawie, ale na szczęście dzięki Power Queret uratuje nasz problem. Oto nasz cel. Dla każdego CENTRUM KOSZTÓW tutaj chcemy podać sumę wszystkich tych liczb. Tak więc jest nazwa wydatku, a -, rutynowo - -, potem znak $, a potem, żeby życie było interesujące, co jakiś czas, po czym przypadkowa notatka; nie zawsze, tylko czasami. Pusty wiersz między każdym z nich. Tony i tony danych.
Więc oto co zrobię. Zejdę na sam dół, ostatnią komórkę, wybiorę wszystkie te rzeczy, łącznie z nagłówkami. Mam zamiar utworzyć NAME. Nazwę to MyData. MyData, tak, dobrze? W porządku. Teraz użyjemy funkcji Power Query, która jest bezpłatna w 2010 lub 2013 roku, wbudowana w 2016 i 2016 Office 365. Będzie to pochodzić z TABELI LUB ZAKRESU. W porządku. Po pierwsze, za każdym razem, gdy mamy te puste miejsca w KOLUMNIE A, wszystkie NULLS, których chcemy się pozbyć. Więc odznaczę NULL. Niesamowite. W porządku. Naprawdę, w tych danych, w tej wersji danych, ponieważ mam zamiar zbudować WYSZUKAJ.PIONOWO, nie potrzebujemy tej kolumny. Więc zamierzam kliknąć prawym przyciskiem myszy i pozbyć się tej kolumny, więc REMOVE kolumnę.
W porządku. Oto, gdzie wydarzy się ta cholerna magia. Wybierz tę kolumnę, PODZIEL KOLUMNĘ PRZEZ DELIMITER, a na pewno przejdziemy do ZAAWANSOWANYCH. Separator będzie znakiem specjalnym i zamierzamy podzielić każde wystąpienie separatora. Więc tutaj, myślę, że właściwie już to rozgryźli, ponieważ to rozszerzyłem, ale zamierzam ci pokazać. WSTAW ZNAK SPECJALNY. Powiem, że to LINE FEED, w porządku, więc przy każdym wystąpieniu LINE FEED i mam zamiar PODZIELIĆ NA WIERSZE. W porządku, a to, co się tutaj stanie, to 1, 2, 3, 4, 5, otrzymam 5 wierszy lub powiem 1001, ale w każdym rzędzie będzie mieć inny linia z tej komórki. To jest niesamowite. Jest 1, 2, 3, 4, 5, 1001. W porządku. Teraz musimy tylko przeanalizować tego złego chłopca. W porządku,więc wybierz tę kolumnę, PODZIEL KOLUMNĘ PRZEZ DELIMITER. Tym razem separatorem będzie znak $. To jest idealne, raz, przy pierwszym znalezionym znaku $, na wypadek gdyby pojawił się znak $ w przyszłej części. Podzielimy się na kolumny. Kliknij OK. W porządku. Więc są szczegóły. Oto nasze pieniądze.
Teraz podzielę to na SPACJĘ. Więc wybierz tę kolumnę, PODZIEL KOLUMNĘ PRZEZ OZNACZNIK, a separatorem będzie SPACJA, tak, raz w LEWO-NAJBARDZIEJ DELIMITER, kliknij OK i nie potrzebuję tych komentarzy, więc te komentarze ponownie USUŃ. Właściwie to też nie potrzebuję tego, ponieważ po prostu próbuję uzyskać w sumie wszystkie te rzeczy, więc zamierzam USUNĄĆ.
Teraz przekształć. GRUPA WEDŁUG CENTRUM KOSZTÓW, NOWA NAZWA KOLUMNY będzie nazywać się RAZEM, OPERACJA będzie SUMĄ, a którą kolumnę SUMA? SZCZEGÓŁY 2.1. Piękny. Kliknij OK, w porządku, a otrzymamy jeden wiersz na CENTRUM KOSZTÓW, zawierający SUMA wszystkich tych elementów zamówienia. DOM, ZAMKNIJ I ZAŁADUJ. Prawdopodobnie wstawi nowy arkusz. Mam nadzieję, że wstawia nowy arkusz roboczy i robi, a ten arkusz nosi nazwę MYDATA_1. MYDATA_1.
W porządku. Teraz wrócimy tutaj do oryginalnych danych i wykonamy następujące kroki. Przy pierwszym z nich = WYSZUKAJ.PIONOWO 1001 w naszych wynikach. Jest to trochę podobne do ustawiania odniesienia cyklicznego, ale nie da nam to odniesienia cyklicznego. , 2, FAŁSZ. Chcę dokładnego dopasowania. W porządku, ale nie zamierzamy tego robić dla pustych komórek. Więc powiem, cóż, właściwie, skopiujmy to do końca. CONTROL + C, zejdź na sam dół, żeby zobaczyć, co otrzymujemy. Może dostajemy N / A i mogę się tego pozbyć za pomocą IFNA. Tak, pięknie, w porządku. Po prostu pozbądźmy się N / A. Jeśli nie dotyczy, po prostu chcemy „”. Nic tam nie chcemy. CONTROL + ENTER. W porządku. Teraz to powinno być SUMA. Zobaczmy, czy uda nam się znaleźć krótki i po prostu policzyć. = 627,37 + 7264,25 + 6066.01 + 4010,66 + 9773,94, a TOTAL to 27742,23. Cholernie niesamowite. (= IFNA (WYSZUKAJ.PIONOWO (A2; MojeDane_1,2; FAŁSZ); „”))
A teraz sprawa. Mamy więc ludzi z linii, którzy są tutaj, zmieniając rzeczy, w porządku, więc powiedzmy, że przechodzą i zmieniają budżet, 40294.48, i przychodzą tutaj i zmieniają ten na 6000, o tak, i dodają nowy, ALT + ENTER, COŚ - znak $, właśnie dodano 1000 $. W porządku. Teraz, oczywiście, kiedy naciskam ENTER, ten numer 40294.48 nie będzie aktualizowany, w porządku, ale musimy przejść do zakładki DANE i chcemy ODŚWIEŻYĆ WSZYSTKO. Tak więc 40294,48. Oglądaj, obserwuj, obserwuj, obserwuj. ODŚWIEŻ WSZYSTKO. Cholernie niesamowite.
Uwielbiam Power Query. Zapytanie o moc jest najbardziej niesamowitą rzeczą. Te dane, które zasadniczo przypominają dane słów w komórce, są teraz aktualizowane. Prawdopodobnie mógłbyś nawet stworzyć jakieś makro, które mówi, że za każdym razem, gdy ktoś zmieni coś w KOLUMNIE C, klikamy ODŚWIEŻ WSZYSTKO używając makra i po prostu otrzymujemy te wyniki stale, ciągle odświeżając.
Co za okropne pytanie przysłane. Żal mi Steve'a, który musi sobie z tym poradzić, ale teraz, korzystając z funkcji Power Query w Office 365 lub pobranej na rok 2010 lub 2013, masz bardzo, bardzo łatwy sposób rozwiązania tego problemu.
Czekać. OK, dodatek: zróbmy to jeszcze lepiej. Ten arkusz nazywa się DANE i zapisałem skoroszyt jako z włączoną obsługą makr, czyli xlsm. Jeśli masz xlsx, nie pomijaj zapisywania jako xlsm. ALT + F11. Znajdź skoroszyt o nazwie DANE, kliknij dwukrotnie, w lewym górnym rogu, KARTA ROBOCZA, a następnie ZMIEŃ za każdym razem, gdy zmienimy arkusz, i powiemy AKTYWNA KSIĄŻKA ROBOCZA.REFRESHALL, a następnie zamknij, w porządku, a teraz spróbujmy. Zmieńmy coś. Więc weźmiemy te maliny, które są obecnie 8 000 i zmienimy je na 1000, więc zmniejszamy o 7 000. Kiedy naciskam ENTER, chcę zobaczyć, jak 42 000 zejdzie do 35 000. Ach. Niesamowite.
Więc hej. W tym miejscu zazwyczaj błagam Cię o zakup mojej książki, ale dzisiaj poproszę Cię o zakup książki moich przyjaciół - Kena Pulsa i Miguela Escobara - M jest dla (DANE) MONKEY. Wszystko, czego nauczyłem się o Power Queret, nauczyłem się z tej książki. To niesamowita książka. Sprawdź to.
Podsumowanie odcinka: Steve ma do zsumowania liczby, które zostały wprowadzone w kolumnie tekstowej; wiele linii w każdej komórce, oddzielonych ALT + ENTER; trzeba podzielić te wiersze na wiersze, a następnie przeanalizować kwotę w dolarach ze środka każdej komórki; podsumować COST CENTER; zbudować tablicę przeglądową; pobrać sumy z tabeli przeglądowej, używając IFNA do zignorowania błędów w pustym wierszu; a następnie premię, makro na końcu, makro zdarzenia do aktualizacji arkusza roboczego po zmianie komórki.
Chcę podziękować Steve'owi za wysłanie tego pytania i bardzo się cieszę, że mam odpowiedź - przed zapytaniem o moc byłoby naprawdę, bardzo trudne - i chcę podziękować za zatrzymanie się. Do zobaczenia następnym razem z kolejnym netcastem od.
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2160.xlsm
