Це проблема бюджету пожежника. Люди в пожежному будинку погано робили свій бюджет у Excel. Дивовижне перетворення Power Query пропонує рішення.
Переглянути відео
- Стів повинен підсумувати числа, які були введені в текстовий стовпець
- У кожній комірці є кілька рядків, розділених alt = "" + Enter
- Потрібно розділити ці рядки на рядки, а потім проаналізувати доларову суму з середини кожної комірки
- Підсумуйте за Центром витрат
- Складіть таблицю пошуку
- Отримайте підсумки з таблиці пошуку, використовуючи IFNA, щоб ігнорувати помилки в порожньому рядку
- Бонус: додайте макрос події, щоб оновити аркуш, коли вони змінюють комірку.
Стенограма відео
Дізнайтеся Excel з, Підкаст Епізод 2160: Дані SUM, які було змінено + Введено.
Привіт Ласкаво просимо до мережі. Я Білл Джелен. Я цього не вигадую. Я отримав запитання від когось, хто має дані - бюджетні дані - і виглядає так. Зараз я вкладаю тут фальшиві слова, щоб ми не мали їхньої бюджетної інформації, але людина, новачок у бухгалтерії, пішов до компанії, і ця компанія роками робила їхні бюджети так. Вони не бухгалтери, які роблять бюджет, це люди підпорядковані, але так вони робили, і він не може змусити їх змінитись. Отже, ось наша мета. Він каже, що це так само погано, як введення бюджету в Word.
Ну, майже, але на щастя, завдяки енергозапиту це врятує нашу проблему. Ось наша мета. Для кожного ЦЕНТРУ ВАРТІСТЬ тут, ми хочемо повідомити загальну кількість усіх цих цифр. Отже, є назва витрат, a -, зазвичай a -, потім знак $, а потім, просто щоб зробити життя цікавим, раз у раз, випадкова нота після; не всі часи, лише деякі випадки. Порожній ряд між кожним. Тонни і тонни даних.
Отже, ось що я збираюся робити. Я спускаюся до самого низу, до останньої комірки, я збираюся виділити всі ці речі, включаючи заголовки. Я збираюся створити ІМЯ. Я збираюся назвати це MyData. MyData, ось так, добре? Гаразд Тепер ми будемо використовувати енергетичний запит, який є безкоштовним у 2010 або 2013 роках, вбудований у 2016 та 2016 Office 365. Це буде надходити з ТАБЛИЦІ АБО ДИСПЛЕУ. Гаразд По-перше, щоразу, коли ми маємо ці пробіли у Стовпці А, усі НУЛИ, від яких ми хочемо позбутися. Тож я збираюся зняти позначку NULL. Приголомшливо Добре. Дійсно, у цих даних, у цій версії даних, оскільки я збираюся побудувати VLOOKUP, нам не потрібен цей стовпець. Отже, я збираюся клацнути правою кнопкою миші та позбутися цього стовпця, тож ВИДАЛИТИ стовпець.
Гаразд Ось тут, де відбудеться дивовижна магія. Виберіть цей стовпець, РОЗДІЛІТЬ СТОЛБОК НА РОЗМІНИК, і ми точно підемо на РОЗШИРЕНИЙ. Розділювач буде особливим символом, і ми поділимо кожне входження роздільника. Отже, тут, я думаю, вони насправді вже це зрозуміли, тому що я розширив його, але я збираюся показати вам. ВСТАВИТИ СПЕЦІАЛЬНИЙ ХАРАКТЕР. Я збираюся сказати, що це ЛІНІЙНА КОРМИНА, добре, отже, при кожному появі ЛІНІЙНОЇ ФІДИ, і я збираюся РОЗДІЛИТИСЯ НА РЯДКИ. Гаразд, і саме те, що тут станеться, це 1, 2, 3, 4, 5, я отримаю 5 рядків або скажу 1001, але в кожному рядку це буде мати інший лінія від цієї комірки. Це дивно. Є 1, 2, 3, 4, 5, 1001. Добре. Тепер нам просто потрібно проаналізувати цього поганого хлопчика. Добре,отже, виберіть цей стовпець, РОЗДІЛІТЬ СТОЛБКУ НА РОЗМІНИК. Цього разу роздільник буде знаком $. Це ідеально, одного разу, при першому знаку $, який ми знайдемо, на випадок, якщо там буде знак $ у майбутній частині. Ми збираємось РОЗДІЛИТИСЯ НА СТОЛБЦИ. Клацніть OK. Гаразд Отже, є деталі. Ось наші гроші.
Зараз я поділю це на ПРОСТОРІ. Отже, виберіть цей стовпець, РОЗДІЛІТЬ СТОЛБОК РОЗРІЗНИКОМ, і роздільником буде ПРОБІЛ, так, раз у ВЕРХУ НАЙБІЛЬШИЙ РОЗМІНИТЕЛЬ натисніть ОК, і мені не потрібні ці коментарі, тому ці коментарі ми ' знову збираєтеся ВИДАЛИТИ. Власне, це мені теж не потрібно, бо я просто намагаюся отримати загальну кількість всього цього, тому я збираюся ВИДАЛИТИ.
Тепер перетворіть. ГРУПА ЗА ЦЕНТРОМ ВАРТІСТІ, НАЗВА НОВОЇ СТОЛБКИ буде називатися ВСЬОГО, ОПЕРАЦІЯ - СУММА, а який стовпець ми будемо СУММУВАТИ? ДЕТАЛІ 2.1. Гарний. Клацніть ОК, добре, і в результаті ми отримаємо один рядок на ЦЕНТР ВАРТІСТІ із ВСЬОГО всіх цих позицій. ДОМА, ЗАКРИТИ І ЗАВАНТАЖИТИ. Ймовірно, він збирається вставити новий аркуш. Я сподіваюся, що він вставляє новий аркуш, і він це робить, і цей аркуш називається MYDATA_1. MYDATA_1.
Гаразд Тепер ми повернемось сюди до вихідних даних і виконаємо ці кроки. На першому, = VLOOKUP 1001 в наші результати. Це як би встановлення кругової довідки, але вона не дасть нам кругової довідки. , 2, FALSE. Я хочу точну відповідність. Гаразд, але ми не хочемо робити це для порожніх клітинок. Отже, я збираюся сказати, ну насправді, давайте просто скопіюємо його до кінця. CONTROL + C, пройдіться донизу, щоб побачити, що ми отримуємо. Можливо, ми отримуємо N / As, і я можу позбутися цього за допомогою IFNA. Так, гарно, добре. Отже, давайте просто позбудемося N / As. Якщо ні, то ми просто хочемо “”. Ми там нічого не хочемо. 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. Дивись, дивись, дивись, дивись. ОСВІЖИТИ ВСЕ. Дивовижний дивовижний.
Я люблю запити щодо енергії. Енергетичний запит - це найдивовижніша річ. Ці дані, які за своєю суттю подібні до даних слів у комірці, тепер ми оновлюємо. Можливо, ви навіть можете створити якийсь макрос, який говорить, що кожного разу, коли хтось щось змінює у Стовпці С, ми продовжуємо і натискаємо ОСВІЖИТИ ВСЕ за допомогою макросу, і ці результати постійно постійно оновлюються.
Яке жахливе запитання було надіслано. Мені погано для Стіва, якому доводиться з цим боротися, але тепер, використовуючи запит живлення в Office 365 або завантажений на 2010 або 2013 рік, у вас є дуже, дуже простий спосіб вирішити це.
Чекай. Гаразд, додаток: давайте зробимо це ще кращим. Цей аркуш називається DATA, і я зберегла книгу як макрос, тому xlsm. Якщо ви xlsx, не пропускайте збереження як xlsm. ALT + F11. Знайдіть книгу під назвою DATA, двічі клацніть ліворуч, WORKSHEET, а потім ЗМІНИТИ будь-коли, коли ми змінимо аркуш, і ми скажемо ACTIVEWORKBOOK.REFRESHALL, а потім закриємо, гаразд, а тепер давайте спробуємо. Давайте щось відредагуємо. Отже, ми візьмемо ту малину, яка наразі становить 8000, і ми змінимо її на 1000, тому зменшуємо на 7000. Коли я натискаю ENTER, я хочу бачити, що 42000 знижуються до 35000. Ага. Приголомшливо
Ну, привіт. Тут я зазвичай благаю вас придбати мою книгу, але сьогодні я попрошу вас придбати книгу моїх друзів - Кен Пулс та Мігель Ескобар - М призначений для (ДАНИХ) МАЙМУН. Все, що я дізнався про енергетичні запити, я дізнався з цієї книги. Це дивовижна книга. Перевірте це.
Підсумок епізодів: Стів має цифри для підсумовування, які були введені в текстовий стовпець; кілька рядків у кожній комірці, розділених ALT + ENTER; потрібно розділити ці рядки на рядки, а потім проаналізувати доларову суму з середини кожної комірки; узагальнити за ЦЕНТРОМ ВАРТІСТЬ; побудувати таблицю пошуку; отримувати підсумки з таблиці пошуку, використовуючи IFNA, щоб ігнорувати помилки в порожньому рядку; а потім, бонус, макрос в кінці, макрос події для оновлення робочого аркуша при зміні комірки.
Я хочу подякувати Стіву за те, що він надіслав це запитання, і я дуже радий, що маю відповідь - до запиту живлення це було б дуже, дуже важко - і я хочу подякувати вам за заїзд. Ми побачимось наступного разу для чергової трансляції від.
Завантажити файл
Завантажте зразок файлу тут: Podcast2160.xlsm
