Tämä on palomiehen budjetti-ongelma. Tulipalon ihmiset ovat tehneet budjetinsa väärin Excelissä. Hämmästyttävä Power Query -muunnos tarjoaa ratkaisun.
Katso video
- Steve joutuu laskemaan yhteen tekstisarakkeeseen syötetyt numerot
- Kussakin solussa on useita rivejä, jotka on erotettu alt = "" + Enter-näppäimillä
- Sinun on jaettava nämä rivit riveihin ja jäsennettävä sitten dollarin määrä kunkin solun keskeltä
- Yhteenveto kustannuskeskuksen mukaan
- Rakenna hakutaulukko
- Hae summat hakutaulukosta IFNA: n avulla ohittamaan tyhjän rivin virheet
- Bonus: Lisää tapahtumamakro päivittääksesi laskentataulukon, kun he muuttavat solua.
Videon transkriptio
Opi Excel, Podcast Episode 2160: SUM-tiedot, jotka ovat olleet Alt + syötetyt.
Hei. Tervetuloa takaisin netcastiin. Olen Bill Jelen. En keksi tätä. Sain kysymyksen joltakin, jolla on tietoja - budjettitietoja - joka näyttää tältä. Laitoin nyt väärennettyjä sanoja tähän, jotta meillä ei olisi heidän budjettitietojaan, mutta henkilön uusi kirjanpito-osasto meni yritykseen, ja tämä yritys on jo vuosia tehnyt budjettinsa näin. He eivät ole kirjanpitäjiä, vaan budjettirahoja, mutta näin he ovat tehneet, eikä hän voi saada heitä muuttumaan. Joten, tässä on tavoitteemme. Hän sanoo, että tämä on yhtä huono kuin kirjoittaa budjetti Wordiin.
No, melkein, mutta onneksi virtakyselyn ansiosta se säästää ongelmamme. Tässä on tavoitteemme. Jokaisesta täällä olevasta COST CENTERistä haluamme ilmoittaa kaikkien näiden numeroiden kokonaismäärän. Joten, siellä on kustannusnimi, -, rutiininomaisesti a -, sitten $ -merkki ja sitten, vain jotta elämä olisi mielenkiintoista, satunnaisesti satunnainen muistiinpano sen jälkeen; ei aina, vain osan ajasta. Tyhjä rivi niiden välillä. Tonnia ja tonnia tietoa.
Joten tässä aion tehdä. Aion tulla alas, aivan viimeiseen soluun, aion valita kaikki nämä asiat, mukaan lukien otsikot. Aion luoda nimen. Aion kutsua sitä MyDataksi. MyData, sellainen, okei? Hyvä on. Nyt aiomme käyttää virtakyselyä, joka on ilmainen vuosina 2010 tai 2013 ja joka on rakennettu 2016 ja 2016 Office 365: een. Se tulee taulusta tai alueesta. Hyvä on. Ensinnäkin, milloin tahansa ne tyhjät sarakkeessa A, kaikki NULLIT, joista haluamme päästä eroon. Joten aion poistaa NULL-valinnan. Mahtava. Okei. Todellakin, koska näissä tiedoissa, tässä tietojen versiossa, koska aion rakentaa VLOOKUPin, emme tarvitse tätä saraketta. Joten aion napsauttaa hiiren kakkospainikkeella ja päästä eroon siitä sarakkeesta, joten POISTA sarake.
Hyvä on. Nyt tässä tapahtuu, kun friikkiä taika tapahtuu. Valitse tämä sarake, SPLIT COLUMN BY A RIMITER, ja aiomme ehdottomasti siirtyä ADVANCED-tilaan. Rajoitin tulee olemaan erikoismerkki, ja aiomme jakaa jokaisen rajaajan esiintymän. Joten tässä, luulen, että he ovat tosiasiallisesti jo tajunnut sen, koska laajensin sitä, mutta näytän sinulle. ASENNA ERIKOISOMINAISUUS. Aion sanoa, että se on RIVISYÖTTÖ, okei, joten jokaisella rivinsyötöllä, ja aion jakautua riveihin. Selvä, ja mitä täällä tapahtuu, on 1, 2, 3, 4, 5, saan 5 riviä tai sanon 1001, mutta jokaisella rivillä sillä on erilainen linja tästä solusta. Tämä on hämmästyttävä. On 1, 2, 3, 4, 5, 1001. Selvä. Nyt meidän on vain jäsennettävä tämä paha poika. Hyvä on,Joten valitse kyseinen sarake, SPLIT COLUMN BY RESIMITER. Tällä kertaa erotin tulee olemaan $ -merkki. Se on täydellinen, kun löydämme ensimmäisen $ -merkin, siltä varalta, että tulevassa osassa on $ -merkki. Aiomme siirtyä sarakkeisiin. Napsauta OK. Hyvä on. Joten on yksityiskohtia. Tässä on rahamme.
Jaan tämän nyt SPACE: ssä. Joten, valitse tämä sarake, SPLIT COLUMN BY A RIMITER, ja erottimeksi tulee välilyönti, kyllä, kerran VASEN-PISIN RIMITTIMESSÄ, napsauta OK, enkä tarvitse niitä kommentteja siellä, joten kommentit me menen POISTA. En todellakaan tarvitse tätä, koska yritän vain saada kaiken kaiken, joten aion POISTAA.
Muunna nyt. RYHMÄ KUSTANNUSKESKUKSEN JÄLKEEN UUSI SARAKKEEN NIMI kutsutaan YHTEENSÄ, TOIMINNOSTA tulee SUMMA, ja minkä sarakkeen aiomme summaan? YKSITYISKOHDAT 2.1. Kaunis. Napsauta OK, okei, ja lopputuloksemme on yksi rivi COST CENTER -ohjelmaa kohden ja kaikkien näiden rivikohtien YHTEENSÄ. KOTI, SULJE JA LATAA. Se todennäköisesti lisää uuden laskentataulukon. Toivon, että se lisää uuden laskentataulukon ja tekee niin, ja sitä lasketaan nimellä MYDATA_1. MYDATA_1.
Hyvä on. Palataan nyt alkuperäisiin tietoihin ja teemme nämä vaiheet. Ensimmäisessä = VLOOKUP 1001 tulokseemme. Tämä on eräänlainen kuin pyöreän viitteen asettaminen, mutta se ei anna meille pyöreää viittausta. , 2, EPÄTOSI. Haluan tarkan ottelun. Selvä, mutta emme aio tehdä sitä tyhjille soluille. Joten aion sanoa, no, oikeastaan, kopioidaan se vain koko matkan ajan. CONTROL + C, mene kokonaan alas vain nähdäksesi, mitä saamme. Ehkä saamme N / As: n ja voin päästä eroon IFNA: lla. Joo, kaunis, okei. Joten päästään vain eroon N / As: sta. Jos ei ole, niin haluamme vain “”. Emme halua mitään siellä. CONTROL + ENTER. Hyvä on. Sen pitäisi olla YHTEENSÄ. Katsotaanpa, löydämmekö lyhyen ja vain matematiikan. = 627,37 + 7264,25 + 6066.01 + 4010,66 + 9773,94 ja YHTEENSÄ 27742,23 on se. Aivan mahtavaa. (= IFNA (VLOOKUP (A2, MyData_1,2, FALSE), ""))
Tässä on kauppa. Joten, meillä on niitä linja-ihmisiä, jotka ovat täällä muuttamassa juttuja, okei, ja sanotaanpa, että he käyvät läpi ja muuttavat budjettia, 40294,48, ja he tulevat tänne ja muuttavat tämän 6000: ksi, ja lisää uusi, ALT + ENTER, JOTKA - $ -merkki, 1000 dollaria juuri lisätty. Hyvä on. Tietenkin, kun painan ENTER-näppäintä, tämä numero, 40294.48, ei aio päivittää, okei, mutta meidän on siirryttävä DATA-välilehdelle ja haluamme päivittää kaikki. Joten, 40294.48. Katso, katso, katso, katso. TUOTTEEN KAIKKI. Hämmästyttävä hämmästyttävä.
Rakastan tehokyselyä. Tehokysely on upein asia. Nämä tiedot, jotka ovat pohjimmiltaan aivan kuin sanatiedot solussa, päivitetään ne nyt. Voisit todennäköisesti jopa tehdä jonkinlaisen makron, joka sanoo, että joka kerta kun joku muuttaa jotain sarakkeessa C, menemme eteenpäin ja napsautamme VÄRITÄ KAIKKI makrolla ja saamme vain nämä tulokset jatkuvasti päivittyvinä.
Mikä kamala kysymys lähetettiin. Minusta tuntuu pahalta Stevelle, jonka on käsiteltävä tätä, mutta nyt, kun käytät virhekyselyä Office 365: ssä tai ladattu vuodelle 2010 tai 2013, sinulla on erittäin, erittäin helppo tapa ratkaista tämä.
Odota. Okei, lisäys: Tehdään siitä vielä parempi. Tätä taulukkoa kutsutaan nimellä DATA, ja olen tallentanut työkirjan makrokäytössä, joten xlsm. Jos olet xlsx, älä ohita tallennusta xlsm: nä. ALT + F11. Etsi DATA-niminen työkirja, kaksoisnapsauta, vasen yläosa, TYÖLOMAKE ja sitten MUUTA milloin tahansa, kun muutamme laskentataulukkoa, ja sanomme ACTIVEWORKBOOK.REFRESHALL ja sulje sitten, kunnossa, ja kokeillaan nyt. Muokkaamme jotain. Joten otamme ne vadelmat, jotka ovat tällä hetkellä 8000, ja vaihdamme sen arvoon 1000, joten pienennämme 7000: lla. Kun painan ENTER, haluan nähdä, että 42 000 laskee 35 000: een. Ah. Mahtava.
No hei. Täällä pyydän yleensä ostamaan kirjani, mutta tänään pyydän sinua ostamaan ystäväni kirjan - Ken Puls ja Miguel Escobar - M on (DATA) MONKEY. Kaikki, mitä opin tehokyselystä, opin tästä kirjasta. Se on upea kirja. Tarkista se.
Jaksoiden kääriminen: Stevellä on yhteenvetoluvut, jotka on syötetty tekstisarakkeeseen; useita rivejä kussakin solussa, erotettu ALT + ENTER; täytyy jakaa nämä rivit riveihin, sitten jäsentää dollarin määrä kunkin solun keskeltä; yhteenvedon COST CENTER; rakentaa hakutaulukko; hae summat hakutaulukosta IFNA: n avulla ohittamaan tyhjän rivin virheet; ja sitten bonus, makro lopussa, tapahtumakro päivittää laskentataulukko, kun he muuttavat solua.
Haluan kiittää Stevea tämän kysymyksen lähettämisestä ja olen niin iloinen, että minulla on vastaus - ennen virtakyselyä se olisi ollut todella, todella kova - ja haluan kiittää sinua pysähtymisestä. Nähdään ensi kerralla toisesta netcastista.
Lataa tiedosto
Lataa esimerkkitiedosto täältä: Podcast2160.xlsm
