У мене постійно виникають проблеми при роботі з датами в Excel, напевно, я щось роблю не так, але не розумію що саме.
У мене є електронна таблиця, експортована з нашого біржового сервера, яка містить стовпець з датами. Вони вийшли в американському форматі, хоча я перебуваю у Великобританії.
Стовпчик, про який йде мова, виглядає так
04/08/2012
04/09/2009
04/01/2010
04/21/2011
04/05/2012
08/30/2009
08/29/2010
08/28/2011
В Excel я виділив стовпець і вибрав Формат клітинок...
. У цьому діалоговому вікні я вибрав Дата
, вибрав Англійська (США)
як локаль і вибрав відповідний формат дати зі списку. Натиснув кнопку ОК і спробував відсортувати дані за цим стовпчиком.
У діалоговому вікні сортування я обираю цей стовпець, вибираю сортування за значеннями, але порядок дає мені лише варіанти від A до Z, а не від найстарішого до найновішого, як я очікував.
Це, в свою чергу, сортує дані дати за першими двома цифрами.
Я знаю, що можу переформатувати ці дані в ISO, і тоді сортування від А до Я буде працювати, але я не повинен цього робити, я, очевидно, щось упускаю. Що саме?
Редагувати: Я зіпсував нагороду, але це повинно було піти на відповідь @r0berts, його перша пропозиція Text to Columns без роздільника і вибір 'MDY' як тип даних працює. Крім того, якщо у вас є час (наприклад, 04/21/2015 18:34:22
), вам потрібно спочатку позбутися даних про час. Однак після цього метод, запропонований @r0berts, працює нормально.
Проблема: Excel не хоче розпізнавати дати як дати, навіть якщо через "Формат ячеек - Число - Пользовательский" ви явно намагаєтеся вказати йому, що це дати за форматом "мм/дд/гггг". Як відомо, коли Excel розпізнає щось як дату, він далі зберігає це як число - наприклад, "41004", але відображає як дату відповідно до формату, який ви вказали. Щоб додати плутанини, Excel може перетворити лише частину ваших дат, наприклад, 08/04/2009, але залишити інші, наприклад, 28/07/2009, неперетвореними.
**Рішення: кроки 1, а потім 2***.
1) Виберіть стовпчик дати. У розділі Дані натисніть кнопку Текст до стовпців. На першому екрані залиште перемикач на "delimited" і натисніть кнопку Next. Зніміть прапорець з будь-якого з полів-розділювачів (будь-які поля порожні; без галочок) і натисніть кнопку Далі. У розділі Формат даних стовпця виберіть Дата, а в сусідньому комбінованому вікні виберіть Місяць і натисніть Завершити. Тепер ви отримали значення дати (тобто Excel розпізнала ваші значення як тип даних Дата
), але форматування, швидше за все, все ще буде локальною датою, а не мм/дд/ррр
, як ви бажаєте.
2) Щоб правильно відобразити бажаний формат дати США, спочатку потрібно виділити стовпець (якщо він не вибраний), а потім у розділі Формат клітинки - Число виберіть Дата і виберіть Локальна дата : Англійська (США). Це дасть вам формат на зразок "m/d/yy
". Потім ви можете вибрати Custom і там ви можете або ввести "mm/dd/yyyy
" або вибрати його зі списку користувацьких рядків.
**Альтернативний варіант: використовуйте LibreOffice Calc. Після вставки даних з допису Патріка виберіть "Спеціальна вставка" (Ctrl+Shift+V
) і виберіть "Неформатований текст". Відкриється діалогове вікно "Імпорт тексту". Набір символів залишається Unicode, але для мови виберіть English(USA), а також встановіть прапорець "Визначати спеціальні числа". Ваші дати одразу з'являться у форматі США за замовчуванням і будуть сортуватися за датою. Якщо ви бажаєте використовувати спеціальний американський формат ММ/ДД/РРРР, вам потрібно вказати це один раз через "Формат ячеек" - або до, або після вставки.
Можна сказати, що Excel повинен був розпізнавати дати, як тільки я вказав йому формат клітинок, і я не можу не погодитися з цим. На жаль, тільки завдяки кроку 1, описаному вище, я зміг змусити Excel розпізнати ці текстові рядки як дати. Очевидно, що якщо ви робите це багато разів, це може стати головним болем, і ви можете скласти візуальну базову процедуру, яка зробить це для вас одним натисканням кнопки.
**Дані | Текст у стовпці
Оновлення щодо попереднього апострофа після вставки: Ви можете побачити в рядку формул, що в комірці, де дата не розпізнається, стоїть передній апостроф. Це означає, що в комірці, відформатованій як число (або дата), знаходиться текстовий рядок. Можна сказати - початковий апостроф заважає електронній таблиці розпізнати число. Ви повинні знати, що для цього потрібно шукати в рядку формул - тому що електронна таблиця просто відображає те, що виглядає як число, вирівняне по лівому краю. Щоб вирішити цю проблему, виберіть стовпець, який потрібно виправити, виберіть у меню Дані | Текст у стовпці
і натисніть кнопку ОК. Іноді ви зможете вказати тип даних, але якщо ви попередньо встановили формат стовпця саме під ваш тип даних - вам це не знадобиться. Команда насправді призначена для розділення текстового стовпця на два або більше за допомогою роздільника, але вона працює як чарівник і для цієї проблеми. Я тестував її в Libreoffice, але такий самий пункт меню є і в Excel.
Схоже, що Excel не розпізнає ваші дати як дати, він розпізнає їх як текст, звідси і з'являються опції Сортування від А до Я. Якщо все зробити правильно, то повинно вийти щось подібне до цього:
Отже, важливо переконатися, що Excel розпізнає дату. Найпростіший спосіб зробити це - скористатися комбінацією клавіш CTRL+SHIFT+3.
Ось що я зробив з вашими даними. Я просто скопіював їх з вашого повідомлення вище і вставив в Excel. Потім я застосував ярлик вище, і я отримав необхідний варіант сортування. Перевірте зображення.
Зазвичай я просто створюю додатковий стовпець для сортування або підсумовування, тому використовую year(a1)&if len(month(a1))=1,),"")&month(a1)&day(a1)
.
Це дасть результат у вигляді yyyymmdd
, який можна сортувати. Використання len(a1)
просто дозволяє додати додатковий нуль для місяців 1-9.