Ян из Нэшвилла получает данные каждый день после загрузки системы. Столбец даты содержит дату + время. Это заставляет сводную таблицу иметь несколько строк в день вместо сводной одной ячейки в день.
В этой статье показаны три способа решения проблемы.
На рисунке ниже показан столбец «Дата» с «Дата плюс время».
Одно из решений - исправить это в сводной таблице.
- Перетащите поле «Дата» в область «Строки». Он отображается с датой + время.
- Выделите любую ячейку, содержащую дату и время.
- На вкладке Анализировать инструменты сводной таблицы выберите Поле группы.
- В диалоговом окне «Группировка» выберите «Дни». Снимите флажок "Месяцы". Когда вы нажмете ОК, в сводной таблице будет отображаться одна строка в день.
Еще одно утомительное решение - добавить новый столбец после загрузки. Как показано на этом рисунке, формула в столбце J имеет вид =INT(D2).
Дата + время - это целое число, за которым следует десятичное время. Используя функцию INT для получения целой части даты + времени, вы изолируете дату.
Это будет работать нормально, но теперь вам нужно добавлять новый столбец каждый раз при загрузке данных.
Я предпочитаю использовать Power Query. Power Query работает только в версиях Excel для Windows. Вы не можете использовать Power Query на Android, IOS или Mac.
Power Query встроен в Excel 2016. Для Excel 2010 и 2013 вы можете загрузить Power Query от Microsoft.
Сначала преобразуйте загруженные данные в таблицу с помощью Ctrl + T. Затем на вкладке «Данные» выберите «Из таблицы / диапазона».
В сетке Power Query выберите столбец Дата. Перейдите на вкладку Transform. Откройте раскрывающийся список Тип даты и выберите Дата. Если вы получили сообщение «Заменить шаг» или «Новый шаг», выберите «Заменить шаг».
На вкладке «Главная» Power Query выберите «Закрыть и загрузить».
Ваш результат появится на новом листе. Создайте сводную таблицу из этого листа.
В следующий раз, когда вы получите новые данные, выполните следующие действия:
- Вставьте новые данные поверх исходного набора данных.
- Перейдите на лист с результатом Power Query. Расширьте панель запросов, чтобы увидеть Обновить. Щелкните Обновить.
- Обновите сводную таблицу.
Смотреть видео
Стенограмма видео
Изучите Excel из подкаста, эпизод 2203: «Обрезать дату и время до даты».
Привет, добро пожаловать обратно в сеть, я Билл Джелен. Сегодняшний вопрос от Яна на моем семинаре по Power Excel в Нэшвилле. Посмотрите на это: Ян получает загрузку из своей системы каждый день, и у нее - в столбце «Дата» - есть дата и время, и это мешает, потому что, когда Ян создает сводную таблицу и помещает даты в левой части, вместо того, чтобы просто получать одну строку для каждой даты, он получает одну строку для каждой даты и времени.
Теперь можно было бы подойти сюда и сказать «Групповое поле» и сгруппировать это в «День», нажмите «ОК». И, знаете, это лишние несколько щелчков мышью, которые, я думаю, нам не нужно делать. Итак, для этого подкаста, следующих нескольких подкастов мы собираемся взглянуть на Power Query. Теперь он встроен в Excel 2016 здесь, в разделе «Получить и преобразовать данные». Если у вас 2010 или 2013 работает в Windows, а не на Mac, вы можете бесплатно загрузить Power Query из Excel. И Power Query хочет работать с таблицей или диапазоном.
Итак, я собираюсь выбрать одну ячейку данных, нажмите Ctrl + T для таблицы, отлично. А затем на моей вкладке Power Query, Data, From Table / Range, я собираюсь выбрать столбец Date и Transform и сказать, что я хочу, чтобы это были не дата и время, а просто дата. Я выберу «Заменить текущий», а затем «Главная», «Закрыть и загрузить», и мы получим новую форму листа, в которой преобразованы данные. Теперь, отсюда, конечно, мы можем подвести итог с помощью поворота, и это будет идеально.
Я думаю, что преимущество этого метода, Power Query, заключается в том, что Ян получает больше данных … Итак, у меня есть новые данные, я собираюсь взять эти новые данные, скопировать данные и просто перейду к своим старым данным и вставьте чуть ниже. И обратите внимание, что маркер конца таблицы прямо сейчас находится в строке 474. Когда я вставляю, маркер конца таблицы перемещается вниз к нижней части новых данных. Хорошо? И этот запрос написан на основе таблицы. Хорошо? Итак, по мере роста таблицы.
Теперь одна проблема заключается в том, что если вы впервые используете Power Query, Power Query начинается с такой ширины - только половину ширины - и вы не видите значок обновления. Итак, вы должны перетащить его сюда вот так, получить этот отличный значок «Обновить» и просто нажать «Обновить». Прямо сейчас у нас загружено 473 строки, а теперь загружено 563 строки. Создайте свою сводную таблицу, и жизнь прекрасна.
Теперь послушайте, я знаю, что можно было бы добавить сюда новый столбец = INT (D2) - - даты - скопируйте его, скопируйте и вставьте специальные значения. Но тогда вы будете на крючке за то, что делаете это каждый божий день. Хорошо? Итак, хотя в этом видео мы говорили как минимум о трех различных способах решения этой проблемы, для меня Power Query и просто возможность нажать «Обновить» - это лучший способ.
Power Query, я говорю об этом в своей новой книге MrExcel LIVe, 54 величайших совета по работе с Excel всех времен.
Подведение итогов сегодняшнего эпизода: Ян из Нэшвилла получает данные из системы каждый день, в столбце «Дата» досадно указаны как дата, так и время - это портит его сводные таблицы, поэтому он должен… Ну, три возможных вещи: Первое, использовать функция INT, копирование, вставка значений; во-вторых, просто оставьте это как Дата и время, а затем сгруппируйте сводную таблицу по дате; или, что лучше, Power Query, превращает загруженный набор данных в таблицу с помощью Ctrl + T, а затем на вкладке «Данные» выберите «Из таблицы», выберите столбец «Дата и время», перейдите на вкладку «Преобразование» в Power Query, выполните преобразование это в Date, Close & Load, построить сводную таблицу из этого. В следующий раз, когда вы получите данные, вставьте исходную таблицу, перейдите на панель запросов, нажмите «Обновить», и все готово.
Эй, чтобы загрузить книгу из сегодняшнего видео, перейдите по URL-адресу в описании YouTube.
Я хочу поблагодарить Яна за то, что он появился на моем семинаре в Нэшвилле, я хочу поблагодарить вас за то, что заглянули. Увидимся в следующий раз для другого сетевого вещания от.
Скачать файл Excel
Чтобы загрузить файл Excel: date-time-to-date.xlsx
Чтобы еще больше улучшить процесс Power Query:
- Убедитесь, что загружаемая книга всегда сохраняется в одной папке с тем же именем.
- Начните с пустой книги. Сохранить как с именем, например DownloadedFileTransformed.xlsx
- Из пустой книги, Данные, Из файла, Из книги. Укажите на файл из шага 1.
- Выберите «Очистить данные». Преобразуйте дату / время в дату. Закрыть и загрузить.
- Щелкните правой кнопкой мыши панель запросов и выберите «Свойства». Выберите «Обновить данные при открытии файла».
- Каждый день ходите на работу. Принеси свой кофе. Убедитесь, что в папке находится новый файл для №1.
- Откройте книгу DownloadFileTransformed.xlsx. В нем будут новые данные, и даты будут правильными.
Идея дня в Excel
Я попросил совета у моих друзей-мастеров Excel по поводу Excel. Сегодняшняя мысль задуматься:
«Валидация - ваш друг».
Дуэйн Обин
