Това е проблемът с бюджета на пожарникарите. Хората в пожарната са грешили бюджетите си в Excel. Удивителната трансформация на Power Query предоставя решението.
Гледам видео
- Стив трябва да сумира числата, които са въведени в текстова колона
- Във всяка клетка има няколко реда, разделени с alt = "" + Enter
- Трябва да разделите тези редове на редове, след което да анализирате доларовата сума от средата на всяка клетка
- Обобщете по Център на разходите
- Изградете справочна таблица
- Вземете общо от справочната таблица, като използвате IFNA, за да игнорирате грешките в празния ред
- Бонус: Добавете макрос на събитие, за да актуализирате работния лист, когато променят клетка.
Видео транскрипция
Научете Excel от, Подкаст Епизод 2160: SUM данни, които са били Alt + въведени.
Хей. Добре дошли отново в netcast. Аз съм Бил Джелен. Не си го измислям. Получих въпрос от някой, който разполага с данни - данни за бюджета -, който изглежда така. Сега влагам фалшиви думи тук, за да нямаме тяхната бюджетна информация, но човекът е нов в счетоводния отдел, отиде при компания и тази компания от години прави бюджетите си по този начин. Те не са счетоводители, които правят бюджета, те са редови хора, но това е начинът, по който го правят и той не може да ги накара да се сменят. И така, ето нашата цел. Той казва, че това е толкова лошо, колкото да въведете бюджета в Word.
Е, почти, но за щастие, благодарение на заявката за захранване ще спаси проблема ни. Ето нашата цел. За всеки ЦЕНТЪР НА РАЗХОДИТЕ тук искаме да докладваме общата сума на всички тези числа. И така, има име на разходи, a -, рутинно a -, след това знак за $ и след това, просто за да направим живота интересен, от време на време, случайна бележка след това; не през всички времена, а само през част от времето. Празен ред между всеки един. Тонове и тонове данни.
И така, ето какво ще направя. Ще сляза до дъното, последната клетка, ще избера всички тези неща, включително заглавията. Ще създам ИМЕ. Ще го нарека MyData. MyData, така, нали? Добре. Сега ще използваме заявка за захранване, която е безплатна през 2010 или 2013, вградена в 2016 и 2016 Office 365. Това ще дойде от МАСА ИЛИ ОБХВАТ. Добре. Първото нещо, по всяко време, когато имаме тези заготовки в КОЛОНА А, всички НУЛИ, от които искаме да се отървем. Така че ще премахна отметката от NULL. Страхотно. Добре. Наистина, в тези данни, в тази версия на данните, тъй като ще изградя VLOOKUP, нямаме нужда от тази колона. И така, ще щракна с десния бутон на мишката и ще се отърва от тази колона, така че ПРЕМАХНЕТЕ колоната.
Добре. Сега, ето къде ще се случи страшната магия. Изберете тази колона, РАЗДЕЛЕТЕ КОЛОНА ОТ РАЗРЕЗНИК и определено ще преминем към НАПРЕД. Разделителят ще бъде специален знак и ще разделим всяко появяване на разделителя. И така, тук, мисля, че всъщност вече са го разбрали, защото го разширих, но ще ви покажа. ПОСТАВЕТЕ СПЕЦИАЛЕН ХАРАКТЕР. Ще кажа, че това е LINE FEED, добре, така че при всяко появяване на LINE FEED и ще РАЗДЕЛЯ В РЯДОВЕ. Добре и точно това, което ще се случи тук, е 1, 2, 3, 4, 5, ще получа 5 реда или ще кажа 1001, но във всеки ред ще има различен линия от тази клетка. Това е невероятно. Има 1, 2, 3, 4, 5, 1001. Добре. Сега просто трябва да анализираме това лошо момче. Добре,така че изберете тази колона, РАЗДЕЛЕТЕ КОЛОНА ОТ ДЕЛИМЕТЪР. Този път разделителят ще бъде знак $. Това е перфектно, веднъж, при първия знак $, който открием, само в случай, че там има знак $ в бъдещата част. Ще се РАЗДЕЛИМ НА КОЛОНИ. Щракнете върху OK. Добре. И така, има подробности. Ето нашите пари.
Сега ще разделя това на ПРОСТРАНСТВОТО. И така, изберете тази колона, РАЗДЕЛЕТЕ КОЛОНА С РАЗДЕЛИТЕЛ и разделителят ще бъде ПРОСТРАНСТВО, да, веднъж в НАЙ-ДОЛНИЯ РАЗДЕЛИТЕЛ щракнете върху OK и нямам нужда от тези коментари там, така че тези коментари ние ' отново ще премахнете. Всъщност и това не ми трябва, защото просто се опитвам да взема общо всички тези неща, така че ще ОТСТРАНЯ.
Сега, трансформирайте се. ГРУПИРАНЕ ПО ЦЕНТЪР НА ЦЕНТРИТЕ, ИМЕТО НА НОВАТА КОЛОНА ще се нарича ОБЩО, ОПЕРАЦИЯТА ще бъде СБОРА и коя колона ще обобщим? ПОДРОБНОСТИ 2.1. Красив. Щракнете върху OK, добре и това, което завършваме, е един ред на ЦЕНТЪР НА РАЗХОДИТЕ с ОБЩО от всички тези договорени покупки НАЧАЛО, ЗАТВОРИ И ТОВАР. Вероятно ще вмъкне нов работен лист. Надявам се да вмъкне нов работен лист и го прави, и този работен лист се нарича MYDATA_1. MYDATA_1.
Добре. Сега ще се върнем тук в първоначалните данни и ще направим тези стъпки. На първия, = VLOOKUP от 1001 в нашите резултати. Това е нещо като настройване на кръгова справка, но няма да ни даде кръгова справка. , 2, FALSE. Искам точно съвпадение. Добре, но няма да искаме да правим това за празните клетки. Така че, ще кажа, добре, всъщност, нека просто го копираме докрай. CONTROL + C, отидете докрай, за да видите какво получаваме. Може би получаваме N / As и мога да се отърва с IFNA. Да, красиво, добре. Така че, нека просто се отървем от N / As. Ако N / A, тогава ние просто искаме "". Не искаме нищо там. CONTROL + ENTER. Добре. Това трябва да е ОБЩОТО. Нека да видим дали можем да намерим кратък и просто да сметнем. = 627,37 + 7264,25 + 6066.01 + 4010,66 + 9773,94 и ОБЩО, 27742,23 е това. Страшен страхотен. (= IFNA (VLOOKUP (A2, MyData_1,2, FALSE), “”))
Сега ето сделката. И така, имаме онези хора, които са тук, променят нещата, добре, така че нека кажем, че те преминават и променят бюджета, 40294.48, и идват тук и променят този на 6000, така, и те добавят нов, ALT + ENTER, НЕЩО - знак $, $ 1000 току-що добавен. Добре. Сега, разбира се, когато натисна ENTER, този номер, 40294.48, няма да се актуализира, добре, но това, което трябва да направим, е да отидем в раздела ДАННИ и искаме да ОНОВИТЕ ВСИЧКИ. И така, 40294.48. Гледай, гледай, гледай, гледай. ОСВЕТЕТЕ ВСИЧКИ. Невероятно.
Обичам заявката за мощност. Запитването за захранване е най-удивителното нещо. Тези данни, които по същество са точно като данни от думи в клетка, сега ги актуализираме. Вероятно бихте могли дори да направите някакъв макрос, който казва, че всеки път, когато някой промени нещо в КОЛОНА C, ние продължаваме напред и кликваме REFRESH ALL с помощта на макроса и просто имаме тези резултати постоянно, постоянно освежаващи.
Какъв ужасен въпрос беше изпратен. Чувствам се зле за Стив, който трябва да се справи с това, но сега, използвайки заявка за захранване в Office 365 или изтеглена за 2010 или 2013, имате много, много лесен начин да разрешите това.
Изчакайте. Добре, допълнение: нека го направим още по-добро. Този лист се нарича DATA и запазих работната книга като активирана с макроси, така че xlsm. Ако сте xlsx, не пропускайте записването като xlsm. ALT + F11. Намерете работната книга, наречена ДАННИ, щракнете двукратно, горе вляво, РАБОТЕН ЛИСТ и след това ПРОМЕНЕТЕ по всяко време, когато сменим работния лист, и ще кажем ACTIVEWORKBOOK.REFRESHALL и след това затворете, добре, и сега нека опитаме. Да редактираме нещо. И така, ще вземем онези малини, които в момента са 8000, и ще го променим на 1000, така че намаляваме със 7000. Когато натисна ENTER, искам да видя, че 42 000 намаляват до 35 000. Ах. Страхотно.
Е, хей. Тук обикновено ви моля да купите моята книга, но днес ще ви помоля да купите книгата на моите приятели - Кен Пулс и Мигел Ескобар - М е за (ДАННИ) МАЙМУНА. Всичко, което научих за заявката за мощност, научих от тази книга. Това е невероятна книга. Вижте това.
Обобщение на епизодите: Стив има цифри за сумиране, които са въведени в текстова колона; множество редове във всяка клетка, разделени с ALT + ENTER; трябва да разделите тези редове на редове, след което да анализирате доларовата сума от средата на всяка клетка; обобщение по ЦЕНТЪР НА РАЗХОДИТЕ; изграждане на справочна таблица; вземете суми от справочната таблица, като използвате IFNA, за да игнорирате грешките в празния ред; и след това, бонус, макрос в края, макрос за събитие за актуализиране на работния лист, когато те променят клетка.
Искам да благодаря на Стив за изпращането на този въпрос и се радвам, че имам отговор - преди заявка за захранване би било наистина, наистина трудно - и искам да ви благодаря, че се отбихте. Ще се видим следващия път за поредното излъчване от.
Свали файл
Изтеглете примерния файл тук: Podcast2160.xlsm
