Это проблема бюджета пожарного. Люди в пожарной части неправильно распределяют бюджет в Excel. Великолепное преобразование Power Query предлагает решение.
Смотреть видео
- Стив должен суммировать числа, введенные в текстовый столбец.
- В каждой ячейке несколько строк, разделенных символом alt = "" + Enter
- Необходимо разбить эти строки на строки, а затем проанализировать сумму в долларах из середины каждой ячейки.
- Обобщить по МВЗ
- Создайте таблицу поиска
- Получите итоги из справочной таблицы, используя IFNA, чтобы игнорировать ошибки в пустой строке
- Бонус: добавьте макрос события, чтобы обновлять лист при изменении ячейки.
Стенограмма видео
Изучите Excel из эпизода подкаста 2160: данные SUM, которые были введены Alt + Enter.
Привет. Добро пожаловать в сетевую трансляцию. Я Билл Джелен. Я не выдумываю. Я получил вопрос от человека, у которого есть данные - данные бюджета - которые выглядят следующим образом. Я вставляю здесь фальшивые слова, чтобы у нас не было информации об их бюджете, но человек, новичок в бухгалтерии, пошел в компанию, и эта компания в течение многих лет составляла свой бюджет таким образом. Они не бухгалтеры, составляющие бюджет, они линейные люди, но они делают это так, и он не может заставить их что-то изменить. Итак, вот наша цель. Он говорит, что это так же плохо, как печатать бюджет в Word.
Ну, почти, но, к счастью, благодаря power query это спасет нашу проблему. Вот наша цель. Для каждого ЦЕНТРА СТОИМОСТИ здесь мы хотим сообщить общую сумму всех этих чисел. Итак, есть название расхода, -, обычно -, затем знак $, а затем, просто чтобы сделать жизнь интересной, время от времени случайная заметка; не всегда, а лишь иногда. Пустая строка между ними. Тонны и тонны данных.
Итак, вот что я собираюсь сделать. Я собираюсь спуститься к самому низу, к самой последней ячейке, я собираюсь выделить все это, включая заголовки. Я собираюсь создать ИМЯ. Я назову это MyData. MyData, вот так, хорошо? Хорошо. Теперь мы собираемся использовать запрос мощности, который предоставляется бесплатно в 2010 или 2013 году, встроенный в 2016 и 2016 Office 365. Он будет поступать из ТАБЛИЦЫ ИЛИ ДИАПАЗОНА. Хорошо. Во-первых, каждый раз, когда у нас есть эти пробелы в СТОЛБЕЦ А, все НУЛИ, от которых мы хотим избавиться. Так что я собираюсь снять отметку с NULL. Потрясающие. Ладно. На самом деле, в этих данных, в этой версии данных, поскольку я собираюсь создать ВПР, этот столбец нам не нужен. Итак, я собираюсь щелкнуть правой кнопкой мыши и избавиться от этого столбца, поэтому УДАЛИТЬ столбец.
Хорошо. Теперь вот где должно произойти чудовище. Выберите этот столбец, РАЗДЕЛИТЬ КОЛОНКУ НА РАЗДЕЛИТЕЛЬ, и мы определенно перейдем в РАСШИРЕННОЕ. Разделитель будет специальным символом, и мы собираемся разделить каждое вхождение разделителя. Итак, я думаю, они уже поняли это, потому что я расширил его, но я собираюсь показать вам. ВСТАВЬТЕ СПЕЦИАЛЬНЫЙ ХАРАКТЕР. Я собираюсь сказать, что это ЛИНИЯ ПОДАЧА, хорошо, поэтому при каждом появлении ЛИНИИ ПОДАЧИ, и я собираюсь РАЗДЕЛАТЬ НА РЯДЫ. Хорошо, и вот что произойдет здесь: 1, 2, 3, 4, 5, у меня будет 5 строк или я скажу 1001, но в каждой строке будет свой линия из этой ячейки. Это потрясающе. Есть 1, 2, 3, 4, 5, 1001. Хорошо. Теперь нам просто нужно разобрать этого плохого парня. Хорошо,Итак, выберите этот столбец, РАЗДЕЛИТЬ столбец разделителем. На этот раз разделителем будет знак $. Это прекрасно, когда-то при первом найденном знаке $, на случай, если в будущей части появится знак $. Мы собираемся РАЗБИВАТЬ НА КОЛОННЫ. Щелкните ОК. Хорошо. Итак, подробности. Вот наши деньги.
Теперь я собираюсь разделить это в ПРОСТРАНСТВЕ. Итак, выберите этот столбец, SPLIT COLUMN BY A DELIMITER, и разделителем будет ПРОБЕЛ, да, один раз в LEFT-MOST DELIMITER нажмите OK, и мне не нужны эти комментарии, поэтому эти комментарии мы ' собираемся УДАЛИТЬ. На самом деле, мне это тоже не нужно, потому что я просто пытаюсь собрать все это в общей сложности, поэтому я УДАЛЯЮ.
Теперь трансформируем. ГРУППА ПО ЦЕНТРАМ ЗАТРАТ, НОВОЕ НАЗВАНИЕ КОЛОНКИ будет называться ИТОГО, ОПЕРАЦИЯ будет СУММОМ, а в каком столбце мы собираемся СУММАТЬ? ДЕТАЛИ 2.1. Прекрасный. Нажмите ОК, хорошо, и в итоге мы получим по одной строке на ЦЕНТР ЗАТРАТ с ИТОГОМ всех этих позиций. ДОМ, ЗАКРЫТЬ И ЗАГРУЗИТЬ. Вероятно, он вставит новый лист. Я надеюсь, что он вставит новый лист, и он вставит, и этот лист называется MYDATA_1. MYDATA_1.
Хорошо. Теперь мы вернемся к исходным данным и проделаем эти шаги. На самом первом = ВПР 1001 в наши результаты. Это похоже на настройку круговой ссылки, но это не даст нам циклическую ссылку. , 2, ЛОЖЬ. Я хочу точное совпадение. Хорошо, но мы не собираемся делать это для пустых ячеек. Итак, я собираюсь сказать, ну, на самом деле, давайте просто скопируем это полностью. CONTROL + C, спуститесь до конца, чтобы посмотреть, что мы получим. Может быть, мы получаем н / п, и я могу избавиться от этого с помощью IFNA. Да, красиво, хорошо. Итак, давайте просто избавимся от N / As. Если N / A, то мы просто хотим «». Нам там ничего не нужно. КОНТРОЛЬ + ВВОД. Хорошо. Теперь это должно быть ВСЕГО. Посмотрим, сможем ли мы найти короткую и просто посчитать. = 627,37 + 7264,25 + 6066.01 + 4010.66 + 9773.94, и ИТОГО 27742.23. Чертовски круто. (= IFNA (ВПР (A2; MyData_1,2; FALSE); «»))
Теперь вот сделка. Итак, у нас есть те линейные люди, которые здесь меняют вещи, хорошо, и поэтому, допустим, они проходят, и они меняют бюджет, 40294,48, и они приходят сюда и меняют этот на 6000, вот так, и они добавляют новый, ALT + ENTER, ЧТО-ТО - знак $, только что добавлено 1000 $. Хорошо. Теперь, конечно, когда я нажимаю ENTER, это число, 40294.48, не обновляется, хорошо, но нам нужно перейти на вкладку ДАННЫЕ и мы хотим ОБНОВИТЬ ВСЕ. Итак, 40294.48. Смотри, смотри, смотри, смотри. ОБНОВИТЬ ВСЕ. Чертовски круто.
Я люблю вопросы власти. Power query - самая удивительная вещь. Эти данные, которые по сути похожи на данные слов в ячейке, теперь у нас обновляются. Возможно, вы даже можете создать какой-то макрос, который говорит, что каждый раз, когда кто-то что-то меняет в COLUMN C, мы нажимаем REFRESH ALL, используя макрос, и просто получаем эти результаты постоянно, постоянно обновляя.
Какой ужасный вопрос прислан. Мне жаль Стива, которому приходится иметь дело с этим, но теперь, используя запрос мощности в Office 365 или загруженный для 2010 или 2013, у вас есть очень и очень простой способ решить эту проблему.
Подождите. Хорошо, добавление: давайте сделаем это еще лучше. Этот лист называется DATA, и я сохранил книгу с поддержкой макросов, поэтому xlsm. Если вы xlsx, не пропускайте сохранение как xlsm. ALT + F11. Найдите книгу под названием ДАННЫЕ, дважды щелкните вверху слева, РАБОЧАЯ ТАБЛИЦА, а затем ИЗМЕНИТЬ каждый раз, когда мы меняем рабочий лист, и мы скажем ACTIVEWORKBOOK.REFRESHALL, а затем закройте, хорошо, а теперь давайте попробуем. Давайте что-нибудь отредактируем. Итак, мы возьмем те малины, которых сейчас 8000, и изменим их на 1000, так что мы уменьшим их на 7000. Когда я нажимаю ENTER, я хочу увидеть, что 42000 уменьшатся до 35000. Ах. Потрясающие.
Ну привет. Здесь я обычно прошу вас купить мою книгу, но сегодня я попрошу вас купить книгу моих друзей - Кена Пульса и Мигеля Эскобара - M означает (ДАННЫЕ) ОБЕЗЬЯНА. Все, что я узнал о power query, я узнал из этой книги. Это потрясающая книга. Проверь это.
Заключение эпизода: Стиву нужно суммировать числа, введенные в текстовый столбец; несколько строк в каждой ячейке, разделенных клавишами ALT + ENTER; нужно разбить эти строки на строки, а затем проанализировать сумму в долларах от середины каждой ячейки; резюмируйте ЦЕНТРОМ РАСХОДОВ; построить справочную таблицу; получить итоги из справочной таблицы, используя IFNA, чтобы игнорировать ошибки в пустой строке; а затем бонус, макрос в конце, макрос события для обновления рабочего листа при изменении ячейки.
Я хочу поблагодарить Стива за то, что он прислал этот вопрос, и я так рад, что получил ответ - до запроса питания это было бы очень, очень сложно - и я хочу поблагодарить вас за то, что заглянули. Увидимся в следующий раз на другой сетевой трансляции от.
Скачать файл
Загрузите образец файла здесь: Podcast2160.xlsm
