Ovo je problem proračuna vatrogasaca. Ljudi u vatrogasnom domu pogrešno su radili svoj proračun u programu Excel. Nevjerojatna Power Query transformacija pruža rješenje.
Gledaj video
- Steve mora zbrojiti brojeve koji su uneseni u tekstualni stupac
- U svakoj ćeliji ima više redaka, odvojenih alt = "" + Enter
- Trebate razdijeliti te retke u redove, a zatim raščlaniti iznos u dolaru iz sredine svake ćelije
- Sažeti po mjestima troškova
- Izradite tablicu pretraživanja
- Uzmite ukupne iznose iz tablice pretraživanja, koristeći IFNA za ignoriranje pogrešaka u praznom retku
- Bonus: dodajte makronaredbu događaja da biste ažurirali radni list kada promijene ćeliju.
Prijepis videozapisa
Učite Excel iz, Podcast epizoda 2160: Podaci o sumama koji su već postavljeni + uneseni.
Hej. Dobrodošli natrag na netcast. Ja sam Bill Jelen. Ne izmišljam ovo. Dobio sam pitanje od nekoga tko ima podatke - podatke o proračunu - koji izgleda ovako. Sad sam ovdje stavio lažne riječi kako ne bismo imali njihove proračunske podatke, ali osoba koja je nova u računovodstvu otišla je u tvrtku i ova tvrtka već godinama radi svoje proračune poput ovog. Oni nisu računovođe koji rade proračun, oni su linijski ljudi, ali ovo je način na koji to rade i on ih ne može natjerati da se promijene. Dakle, evo našeg cilja. Kaže da je ovo jednako loše kao i unos proračuna u Word.
Pa, gotovo, ali srećom, zahvaljujući upitu napajanja spasit će naš problem. Evo našeg cilja. Za svaki COST CENTAR ovdje želimo izvijestiti o ukupnom broju svih tih brojeva. Dakle, tu je naziv troškova, a -, rutinski a -, zatim znak $, a zatim, samo da život učinim zanimljivim, svako malo, slučajna bilješka nakon; ne sva vremena, samo neka vremena. Prazan red između svakog. Tone i tone podataka.
Dakle, evo što ću učiniti. Doći ću do samog dna, do posljednje ćelije, odabrat ću sve ove stvari, uključujući naslove. Stvorit ću IME. Nazvat ću ga MyData. MyData, onako, u redu? U redu. Sada ćemo upotrijebiti energetski upit koji je besplatan u 2010. ili 2013. godini, ugrađen u 2016. i 2016. u Office 365. To će doći iz TABLE ILI RASPONA. U redu. Prva stvar, bilo kada da imamo one praznine u KOLONI A, sve NULLE kojih se želimo riješiti. Dakle, poništit ću NULL. Super. U redu. Zaista, u ovim podacima, u ovoj verziji podataka, jer ću izgraditi VLOOKUP, ovaj stupac nam nije potreban. Dakle, idem desnom tipkom miša i riješit ću se tog stupca, pa UKLONI stupac.
U redu. Evo, ovdje će se dogoditi čudna magija. Odaberite ovaj stupac, PODIJELITE KOLONU DELIMITEROM, a mi ćemo definitivno krenuti u NAPREDNO. Graničnik će biti poseban znak i podijelit ćemo svaku pojavu graničnika. Pa, evo, mislim da su to zapravo već shvatili jer sam ga proširio, ali pokazat ću vam. UMESTITE POSEBNI LIK. Reći ću da je to LINE FEED, u redu, dakle, pri svakoj pojavi LINE FEED-a, i RAZDJELIT ĆU SE U REDOVE. Dobro, i upravo ono što će se ovdje dogoditi je, 1, 2, 3, 4, 5, dobit ću 5 redaka ili ću reći 1001, ali, u svakom redu, imat će različit linija iz ove ćelije. Ovo je odlično. Postoji 1, 2, 3, 4, 5, 1001. U redu. Sad samo trebamo raščlaniti ovog lošeg dječaka. U redu,pa, odaberite taj stupac, PODIJELITE STUPAC DELIMITEROM. Ovaj put će graničnik biti znak $. To je savršeno, jednom, kod prvog znaka $ koji pronađemo, samo u slučaju da u budućem dijelu postoji znak $. RAZDJELIT ĆEMO SE NA KOLONE. Kliknite U redu. U redu. Dakle, postoje detalji. Evo našeg novca.
Sad ću ovo podijeliti u PROSTORU. Dakle, odaberite ovaj stupac, PODIJELITE KOLONU DELIMITEROM, a graničnik će biti PROSTOR, da, jednom u NAJVEĆEM DELIMITERU, kliknite U redu i ne trebaju mi ti komentari, pa ćemo te komentare ponovno ide UKLONITI. Zapravo, ni ovo mi ne treba, jer samo pokušavam dobiti sve te stvari, pa ću UKLONITI.
Sada se transformiraj. GRUPA PO CENTRU TROŠKOVA, NAZIV NOVE KOLONE zvat će se UKUPNO, OPERACIJA će biti ZBIR, a koji ćemo stupac ZUMATI? DETALJI 2.1. Lijep. Kliknite OK, u redu, i na kraju ćemo dobiti jedan redak po COST CENTRU s UKUPNO svih tih stavki. KUĆA, ZATVORI I UTOVARI. Vjerojatno će umetnuti novi radni list. Nadam se da će umetnuti novi radni list, i to čini, a taj se radni list zove MYDATA_1. MYDATA_1.
U redu. Sada ćemo se vratiti ovdje u izvorne podatke i poduzeti ove korake. Na prvom, = VLOOKUP od 1001 u naše rezultate. Ovo je poput postavljanja kružne reference, ali neće nam dati kružnu referencu. , 2, NETOČNO. Želim točno podudaranje. U redu, ali to nećemo htjeti učiniti za prazne stanice. Pa, reći ću, pa, zapravo, hajde da to samo prepisujemo do kraja. CONTROL + C, siđite dolje samo da vidite što dobivamo. Možda dobivamo N / As i mogu se riješiti s IFNA-om. Da, prekrasno, u redu. Dakle, samo se riješimo N / As. Ako nije, onda samo želimo "". Ne želimo ništa unutra. CONTROL + ENTER. U redu. Sad bi to trebalo biti UKUPNO. Pogledajmo možemo li pronaći kratki i samo izračunati. = 627,37 + 7264,25 + 6066.01 + 4010,66 + 9773,94, a UKUPNO, 27742,23 je to. Stvarno odlično. (= IFNA (VLOOKUP (A2, MyData_1,2, FALSE), “”))
Evo dogovora. Dakle, imamo one ljude koji su ovdje vani i mijenjaju stvari, u redu, pa recimo da prođu i promijene proračun, 40294,48, i dođu ovdje i promijene ovaj na 6000, onako, i dodaju novi, ALT + ENTER, NEŠTO - znak $, $ 1000 upravo dodano. U redu. Sada, naravno, kad pritisnem ENTER, ovaj se broj, 40294.48, neće ažurirati, u redu, ali ono što moramo učiniti je otići na karticu PODACI i želimo OSVJETITI SVE. Dakle, 40294,48. Pazi, gledaj, gledaj, gledaj. OSVJEŽATI SVE. Nevjerojatno nevjerojatno.
Volim upite o moći. Upit o napajanju je najnevjerojatnija stvar. Ovi podaci, koji su u osnovi slični podacima riječi u ćeliji, sada se ažuriraju. Vjerojatno biste čak mogli napraviti neku vrstu makronaredbe koja kaže da svaki put kad netko nešto promijeni u STOLPCU C, mi kliknemo OSVJEŽI SVE pomoću makronaredbe i jednostavno imamo te rezultate stalno, stalno osvježavajuće.
Kakvo je užasno pitanje poslano. Osjećam se loše za Stevea koji se mora nositi s tim, ali sada, koristeći upit za napajanje u sustavu Office 365 ili preuzet za 2010. ili 2013. godinu, imate vrlo, vrlo jednostavan način da to riješite.
Čekati. U redu, dodatak: učinimo to još boljim. Ovaj se list zove DATA i spremio sam radnu knjigu kao omogućenu makronaredbama, pa xlsm. Ako ste xlsx, nemojte preskočiti spremanje kao xlsm. ALT + F11. Pronađite radnu knjigu pod nazivom DATA, dvaput kliknite, gore lijevo, WORKSHEET, a zatim PROMIJENITE kad god promijenimo radni list, a mi ćemo reći ACTIVEWORKBOOK.REFRESHALL, a zatim zatvorimo, u redu, a sada probajmo. Uredimo nešto. Dakle, uzet ćemo one maline kojih trenutno ima 8000, a mi ćemo ih promijeniti na 1000, pa smanjujemo za 7000. Kad pritisnem ENTER, želim vidjeti da se 42.000 spusti na 35.000. Ah. Super.
Pa, hej. Tu vas obično molim da kupite moju knjigu, ali danas ću vas zamoliti da kupite knjigu mojih prijatelja - Ken Puls i Miguel Escobar - M je za (DATA) MAJMUNA. Sve što sam naučio o upitu o moći, naučio sam iz ove knjige. To je nevjerojatna knjiga. Pogledajte to.
Završetak epizoda: Steve ima brojeve za zbrajanje koji su uneseni u tekstualni stupac; više linija u svakoj ćeliji, odvojenih ALT + ENTER; treba razdijeliti te redove na redove, a zatim raščlaniti iznos dolara iz sredine svake ćelije; sažeti po COST CENTRU; izgraditi tablicu pretraživanja; dobiti ukupne iznose iz tablice pretraživanja, koristeći IFNA za ignoriranje pogrešaka u praznom retku; a zatim, bonus, makronaredba na kraju, makronaredba događaja za ažuriranje radnog lista kada promijene ćeliju.
Želim zahvaliti Steveu što je poslao to pitanje i drago mi je što imam odgovor - prije upita za napajanje to bi bilo jako, jako teško - i želim vam zahvaliti što ste navratili. Vidimo se sljedeći put za još jedan prijenos od.
Preuzmi datoteku
Preuzmite datoteku uzorka ovdje: Podcast2160.xlsm
