Ян у Нешвілі отримує дані щодня із завантаження системи. Стовпець дати містить дату + час. Це робить зведену таблицю кількома рядками на день замість зведення однієї комірки на день.
У цій статті показано три способи вирішення проблеми.
На малюнку нижче показано стовпець Дата з датою та часом.
Одним із рішень є виправлення у зведеній таблиці.
- Перетягніть поле Дата в область Рядки. Це відображається з датою + час.
- Виберіть будь-яку комірку, що містить дату та час.
- На вкладці Аналіз інструментів зведеної таблиці оберіть Поле групи.
- У діалоговому вікні Групування виберіть Дні. Скасуйте вибір місяців. Після натискання кнопки ОК зведена таблиця відображатиме один рядок на день.
Іншим нудним рішенням є додавання нової колонки після завантаження. Як показано на цьому малюнку, формула у стовпці J має вигляд =INT(D2).
Дата + час - це цілий день, за яким слідує десятковий час. Використовуючи функцію INT для отримання цілочисельної частини дати + часу, ви ізолюєте дату.
Це буде працювати нормально, але тепер вам доведеться додавати новий стовпець кожного разу, коли ви завантажуєте дані.
Моє найкраще рішення - використовувати Power Query. Power Query працює лише у виданнях Windows Excel. Ви не можете використовувати Power Query на Android, IOS або Mac.
Power Query вбудовано в Excel 2016. Для Excel 2010 та 2013 ви можете завантажити Power Query від Microsoft.
Спочатку перетворіть завантажені дані в таблицю, використовуючи Ctrl + T. Потім на вкладці Дані виберіть З таблиці / діапазону.
У сітці Power Query виберіть стовпець Date. Перейдіть на вкладку Трансформація. Відкрийте спадне меню Тип дати та виберіть Дата. Якщо ви отримали повідомлення про Замінити крок або Новий крок, виберіть Замінити крок.
На вкладці Домашня сторінка Power Query виберіть Закрити та Завантажити.
Ваш результат з’явиться на новому аркуші. Створіть зведену таблицю з цього аркуша.
Наступного разу, коли ви отримаєте нові дані, виконайте такі дії:
- Вставте нові дані у вихідний набір даних.
- Перейдіть до аркуша з результатом Power Query. Розширте панель запитів, щоб ви могли бачити Refresh. Клацніть Оновити.
- Оновіть зведену таблицю.
Переглянути відео
Стенограма відео
Дізнайтеся 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 починається настільки широко - лише наполовину в ширину - і ви не бачите піктограми Refresh. Отже, вам потрібно перетягнути його сюди ось так і отримати чудову піктограму Refresh і просто натиснути Refresh. Зараз у нас завантажено 473 рядки, а зараз завантажено 563 рядки. Створіть свою зведену таблицю, і життя прекрасне.
Тепер, привіт, дивіться, я знаю, що тут можна було б додати новий стовпець, = INT (D2) - - від дати - скопіювати це, скопіювати та вставити спеціальні значення. Але тоді ви готові робити це кожен день. Добре? Отже, хоча в цьому відео ми розглядали принаймні три різні способи вирішення, для мене це шлях Power Query і просто можливість натиснути кнопку Оновити.
Power Query, я розповідаю про це у своїй новій книзі, MrExcel LIVe, 54 найкращі поради Excel усіх часів.
Підсумок сьогоднішнього епізоду: Ян у Нешвілі отримує дані із системи щодня, у стовпці Дата надокучливо є і дата, і час - це псує його зведені таблиці, тож він повинен … Ну, три можливі речі: одна, використання функція INT, копіювати, вставляти значення; по-друге, просто залиште його як Дата та Час, а потім згрупуйте зведену таблицю за Датою; або, кращим рішенням, Power Query, робить завантажені дані набором даних у таблиці за допомогою Ctrl + T, а потім на вкладці Дані виберіть З таблиці, виберіть стовпець Дата та час, перейдіть на вкладку Перетворення в Power Query, перетворіть це до дати, закрити і завантажити, побудувати зведену таблицю з цього. Наступного разу, коли ви отримаєте дані, вставте їх у вихідну таблицю, перейдіть на панель Запит, натисніть Оновити, і все готово.
Гей, щоб завантажити книгу із сьогоднішнього відео, відвідайте URL-адресу в описі YouTube.
Я хочу подякувати Йену за те, що він з'явився на моєму семінарі в Нешвілі, я хочу подякувати вам за те, що завітали. Побачимось наступного разу для чергової трансляції від.
Завантажте файл Excel
Щоб завантажити файл Excel: date-time-to-date.xlsx
Для подальшого вдосконалення процесу Power Query:
- Переконайтеся, що книга завантаження завжди зберігається в одній папці з однаковим ім’ям.
- Почніть із порожньої книги. Зберегти як з таким іменем, як DownloadedFileTransformed.xlsx
- З порожньої книги, Дані, З файлу, З робочої книги. Вкажіть на файл із кроку 1.
- Виберіть Чисті дані. Перетворення дати / часу на дату. Закрити та завантажити.
- Клацніть правою кнопкою миші панель запитів і виберіть Властивості. Виберіть Оновити дані під час відкриття файлу.
- Кожного дня вирушайте на роботу. Отримай кави. Переконайтеся, що в папці є новий файл №1.
- Відкрийте книгу DownloadFileTransformed.xlsx. У ньому будуть нові дані, і дати будуть правильними.
Думка дня в Excel
Я попросив своїх друзів Excel Master порадити їх щодо Excel. Сьогоднішня думка поміркувати:
"Перевірка - це твій друг".
Дуейн Обен
