Как перевернуть дату в Excel
Обновлено: 04.07.2024
Когда мы установим английский язык в Microsoft Excel, вы увидите ошибку даты на английском языке, предыдущий месяц, а затем дату и год. Итак, как мне вернуться к правильному формату дня, месяца и года в Excel?
Во многих случаях при вводе данных о дате в Excel данные о дате будут перевернуты сначала на предыдущий месяц, а затем на день и год. Даже если пользователь вводит дату в правильном порядке, результаты все равно возвращают месяцы, дни и годы. Это может быть связано с тем, что вы используете англоязычную версию Office, поэтому, когда мы вводим даты, все будет в английском стиле. Итак, как я могу вернуться к правильной дате, месяцу и году в Excel?
Вы можете изменить язык Windows на вьетнамский, чтобы исправить указанную выше ошибку. В это время при вводе даты Excel будет автоматически вводить дату и год. Если вы хотите ограничить статус этого острова, мы также можем переформатировать столбец или строку даты в Excel.
Шаг 1:
Вы открываете документ Excek, чтобы изменить формат ввода даты. Как и в примере, моя дата вводится в формате месяц, день и год.
Шаг 2:
Далее мы выделим область, в которой нужно изменить формат ввода даты, щелкните правой кнопкой мыши и выберите «Формат ячеек».
Шаг 3:
Внешний вид интерфейса ячеек формата. На вкладке "Число" просмотрите раздел "Категория" и выберите "Дата".
Глядя на интерфейс справа, Местоположение , выберите вьетнамский (Вьетнам), чтобы вернуться к вводу на вьетнамском языке. Вскоре раздел «Тип» выше также изменит способ набора текста на вьетнамском языке. Мы нажимаем на тип даты, который вы хотите использовать. Нажмите OK, чтобы сохранить новые изменения.
Прямо здесь формат даты в данных Excel изменится в соответствии с выбранным вами форматом, вот дата, месяц и год, как показано ниже.
Таким образом, вы можете исправить формат ежемесячного отслеживания в Excel, изменив формат ввода даты прямо над настройками Excel. Кроме того, чтобы избежать этой ошибки, пользователям также следует оставить режим даты на компьютере в стиле предыдущего дня, месяца и года или переключить перкуссию клавиатуры компьютера на вьетнамский для использования.
Иногда может возникнуть необходимость перевернуть данные в Excel, т. е. изменить порядок данных вверх ногами в вертикальном наборе данных и слева направо в горизонтальном наборе данных.
Теперь, если вы думаете, что для этого в Excel должна быть встроенная функция, боюсь, вы будете разочарованы.
Несмотря на то, что в Excel можно переворачивать данные несколькими способами, встроенной функции нет. Но вы можете легко сделать это, используя простой прием сортировки, формулы или VBA.
В этом руководстве я покажу вам, как переворачивать данные в строках, столбцах и таблицах в Excel.
Итак, приступим!
Это руководство охватывает:
Обратить данные с помощью SORT и вспомогательного столбца
Один из самых простых способов изменить порядок данных в Excel на обратный — использовать вспомогательный столбец, а затем использовать этот вспомогательный столбец для сортировки данных.
Отразить данные по вертикали (обратный порядок вверх ногами)
Предположим, у вас есть набор данных с именами в столбце, как показано ниже, и вы хотите перевернуть эти данные:
Ниже приведены шаги, чтобы отразить данные по вертикали:
Вышеуказанные шаги отсортируют данные на основе значений вспомогательного столбца, что также приведет к изменению порядка имен в данных.
После этого можно удалить вспомогательный столбец.
В этом примере я показал вам, как перевернуть данные, когда у вас есть только один столбец, но вы также можете использовать тот же метод, если у вас есть целая таблица. Просто убедитесь, что вы выбрали всю таблицу, а затем используйте вспомогательный столбец для сортировки данных в порядке убывания.
Отразить данные по горизонтали
Той же методикой можно воспользоваться, чтобы отразить данные по горизонтали в Excel.
Excel позволяет сортировать данные по горизонтали с помощью диалогового окна "Сортировка" (функция "Сортировка слева направо").
Предположим, у вас есть таблица, как показано ниже, и вы хотите отразить эти данные по горизонтали.
Вышеуказанные шаги перевернут всю таблицу по горизонтали.
Отразить данные с помощью формул
В Microsoft 365 появилось несколько новых формул, которые позволяют легко изменить порядок столбцов или таблиц в Excel на противоположный.
В этом разделе я покажу вам, как это сделать с помощью формулы SORTBY (если вы используете Microsoft 365) или формулы INDEX (если вы не используете Microsoft 365)
Использование функции SORTBY (доступно в Microsoft 365)
Предположим, у вас есть таблица, показанная ниже, и вы хотите перевернуть данные в этой таблице:
Для этого сначала скопируйте заголовки и поместите их туда, где должна быть перевернутая таблица
Теперь используйте следующую формулу под ячейкой в крайнем левом заголовке:
Приведенная выше формула сортирует данные и использует результат функции ROW в качестве основы для сортировки.
В этом случае функция ROW вернет массив чисел, представляющий номера строк между указанным диапазоном (который в этом примере будет рядом чисел, таких как 2, 3, 4 и т. д.).< /p>
А поскольку третий аргумент этой формулы равен -1, это заставит формулу сортировать данные в порядке убывания.
Запись с наибольшим номером строки будет располагаться вверху, а запись с наименьшим номером правила — внизу, что существенно изменит порядок данных.
После этого вы можете преобразовать формулу в значения, чтобы получить статическую таблицу.
Использование функции ИНДЕКС
Если у вас нет доступа к функции SORTBY, не беспокойтесь — вы можете использовать замечательную функцию INDEX.
Предположим, у вас есть набор данных с именами, как показано ниже, и вы хотите перевернуть эти данные.
Ниже приведена формула для этого:
Как работает эта формула?
В приведенной выше формуле используется функция ИНДЕКС, которая возвращает значение из ячейки на основе числа, указанного во втором аргументе.
Настоящая магия происходит во втором аргументе, где я использовал функцию ROWS.
Поскольку я заблокировал вторую часть ссылки в функции ROWS, в первой ячейке будет возвращено количество строк между A2 и A12, что равно 11.
Но когда он идет вниз по строкам, первая ссылка изменится на A3, затем на A4 и т. д., а вторая ссылка останется без изменений, потому что я заблокировал ее и сделал абсолютной.
По мере продвижения вниз по строкам результат функции СТРОКИ будет уменьшаться на 1, с 11 до 10, затем до 9 и т. д.
А поскольку функция ИНДЕКС возвращает нам значение на основе числа во втором аргументе, это в конечном итоге даст нам данные в обратном порядке.
Вы можете использовать одну и ту же формулу, даже если в наборе данных есть несколько столбцов. однако вам придется указать второй аргумент, указывающий номер столбца, из которого необходимо извлечь данные.
Предположим, у вас есть набор данных, как показано ниже, и вы хотите изменить порядок всей таблицы на противоположный:
Ниже приведена формула, которая сделает это за вас:
Это аналогичная формула, в которую я также добавил третий аргумент, указывающий номер столбца, из которого должно быть извлечено значение.
Чтобы сделать эту формулу динамической, я использовал функцию СТОЛБЦЫ, которая продолжала бы изменять значение столбца с 1 на 2 и на 3 при копировании вправо.
После этого вы можете преобразовать формулы в значения, чтобы получить статический результат.
Примечание. Когда вы используете формулу для изменения порядка набора данных в Excel, исходное форматирование не сохраняется. Если вам также нужно исходное форматирование отсортированных данных, вы можете либо применить его вручную, либо скопировать и вставить форматирование из исходного набора данных в новый отсортированный набор данных
Обратить данные с помощью VBA
Если вам приходится часто переворачивать данные в Excel, вы также можете попробовать метод VBA.
С помощью кода макроса VBA вы можете один раз скопировать и вставить его в книгу в редакторе VBA, а затем повторно использовать его снова и снова в одной и той же книге.
Вы также можете сохранить код в личной книге макросов или в качестве надстройки Excel и иметь возможность использовать его в любой книге в вашей системе.
Ниже приведен код VBA, который переворачивает выбранные данные на листе по вертикали.
Чтобы использовать этот код, вам сначала нужно выбрать набор данных, который вы хотите инвертировать (исключая заголовки), а затем запустить этот код.
Как работает этот код?
Приведенный выше код сначала подсчитывает общее количество строк в наборе данных и присваивает его переменной EndNum.
Затем используется цикл Do While, в котором происходит сортировка данных.
Он сортирует эти данные, беря первую и последнюю строку и меняя их местами. Затем он переходит на 2-й ряд в предпоследнем ряду, а затем меняет их местами. Затем он перемещается на третью строку в предпоследней строке и так далее.
Цикл заканчивается, когда вся сортировка завершена.
Он также использует свойство Application.ScreenUpdating и устанавливает для него значение FALSE во время выполнения кода, а затем возвращает его в значение TRUE после завершения выполнения кода.
Это гарантирует, что вы не будете видеть изменения, происходящие в режиме реального времени на вашем экране, а также ускорит процесс.
Как использовать код?
Выполните следующие действия, чтобы скопировать и вставить этот код в редактор VB:
Чтобы запустить код макроса VBA, сначала выберите набор данных, который вы хотите перевернуть (исключая заголовки).
Выбрав данные, перейдите в редактор VB и нажмите зеленую кнопку воспроизведения на панели инструментов или выберите любую строку в коде, а затем нажмите клавишу F5
Итак, вот некоторые из методов, которые вы можете использовать, чтобы перевернуть данные в Excel (т. е. изменить порядок набора данных на обратный).
Все методы, описанные в этом руководстве (формулы, функция SORT и VBA), можно использовать для отражения данных по вертикали и горизонтали (вам придется соответствующим образом настроить формулу и код VBA для отражения данных по горизонтали). ).
Здравствуйте, у меня возникла следующая проблема с датой, например, мне нужно перевернуть следующую дату:
02.08.2003 (что считается датой) по:
08.02.2003 (настоящая дата).
Есть ли простой способ сделать это с помощью формулы?
Мы будем очень признательны за любую помощь.
Факты об Excel
Введите 1Q-2023 в ячейку. Возьмите ручку заполнения и перетащите вниз или вправо. После 4 квартала 2023 года Excel перейдет на 1 квартал 2024 года. Тире может быть любым символом.
Exceluser2007
Активный участник
Если A1 = 02.08.2003
Затем перейдите в «Формат» > «Ячейки» > «Пользовательский» и введите "m/dd/yyyy", и вы получите 08.02.2003.
Надеюсь, это поможет
Датсмарт
Известный участник
Выберите столбец текстовых дат, которые вы хотите преобразовать.
Нажмите Данные | Текст в столбцы….
Нажмите «Далее», чтобы перейти на 3-ю страницу мастера.
В разделе «Формат данных столбца» выберите «Дата», а затем щелкните стрелку раскрывающегося списка.
Выберите формат текста, из которого вы конвертируете. то есть: "DMY" для День Месяц Год Месяц.
Нажмите "Готово"
Йоги Ананд
MrExcel MVP
Здравствуйте, у меня возникла следующая проблема с датой, например, мне нужно перевернуть следующую дату:
02.08.2003 (что считается датой) по:
08.02.2003 (настоящая дата).
Есть ли простой способ сделать это с помощью формулы?
Мы будем очень признательны за любую помощь.
Для примера, который вы опубликовали
Это будет работать, если дата в ячейке A1 является действительной датой. Сообщите нам полный объем того, что вы пытаетесь сделать, а затем позвольте нам продолжить.
НельсонX
Новый участник
Я пытаюсь использовать реальную дату в расчетах/поиске для получения финансовой информации, поэтому мне нужно правильное значение даты. На самом деле это не проблема форматирования, поэтому я не использовал пользовательский формат или текст для столбцов (поскольку мои даты проходят через столбцы).
Функция даты — это то, что я ищу (но я думал, что пробовал это раньше).
Шонив
Обычная доска
Метод "тупого парня в Excel", который я бы использовал, будет выглядеть примерно так:
Используйте "текст в столбцы" с разделителем "/", чтобы создать 3 отдельных столбца для каждой части значения даты.
Затем используйте дату, соедините или &, чтобы соединить их вместе.
НельсонX
Новый участник
Я согласен, иногда решение за два доллара является лучшим подходом, но в этом случае мне нужна была реализация формулы.
Ричард Шоллар
MrExcel MVP
в зависимости от того, как в настоящее время отображаются ваши даты, возможно, потребуется изменить это на:
Это кажется странным запросом — вы берете одну дату и меняете местами месяц/день. Могу я спросить, почему?
НельсонX
Новый участник
Привет, Ричард, только что увидел твой пост..
Мне нужно внести изменения в «некоторые» ячейки из веб-запроса Reuters. Большинство дат загружаются в формате даты США (но не распознаются как значение). Я использую формулу для преобразования в значение даты в австралийском формате.
Как ни странно, я сталкивался со случаями, когда дата неверна (но распознается как значение даты в веб-запросе). Для этого мне нужно поменять местами месяц и день, чтобы получить правильную дату, как указано в финансовых отчетах.
С помощью этого метода вы должны извлечь каждую часть даты с помощью текстовых функций.
Итак, если у вас есть формат ГГГГММДД для преобразования, выполните следующие действия.
< /p>
Шаг 1. Извлеките год
< /p>
Шаг 2. Извлеките день
Шаг 3. Извлеките месяц
Этот шаг немного сложнее, потому что вы должны извлечь 2 символа из середины строки. В этом случае вы используете функцию MID.
Вы извлекаете 2 символа из 5-й позиции
Шаг 4. Преобразуйте каждую часть в дату
Теперь мы собираемся собрать каждую часть даты, чтобы получить "настоящую" дату. Для этого мы используем функцию DATE.
Конвертировать ГГГГММДД без формулы
Но вы можете преобразовать дату ГГГГММДД или любой другой формат даты в локальный формат даты с помощью инструмента «Текст в столбцы». Обычно этот инструмент используется для разделения текста разделителем.
<р>1. Выберите даты для преобразования
Итак, в нашем примере мы собираемся выбрать ГМД, потому что наши даты записываются ГГГГММДД.
Формула или нет?
Ну, это зависит от того, что вы хотите сделать. Конечно, техника с инструментом "Текст в столбцы" проще, чем с формулами.
Но если ваш список будет часто обновляться, создание формул для выполнения преобразования является хорошим решением, чтобы избежать повторения 5 шагов.
Правильное значение номера недели
Диаграмма солнечных лучей
Фредерик ЛЕ ГЕН
Похожие записи
Формат времени в Excel
Вычислить дату по номеру недели
Как сделать автоматический календарь в Excel
12 комментариев
Я думаю, что самой простой формулой для преобразования этих значений будет вот эта.
Примечание. Это возвращает порядковый номер даты, поэтому вам придется применить формат ячейки, чтобы он отображался так, как вы хотите.
Если дата в формате ггггммдд является результатом формулы (например, извлечения ее из более длинного поля с помощью MID(. )), метод преобразования текста в столбец не работает, поскольку Excel пытается разделить саму формулу, а не стоимость. Конечно, попытка разделить, например,
= mid(a1;10;8)
используя формат dmy, не работает.
Я обнаружил, что троекратное преобразование числа в текст в число (поскольку параметрами DATE() являются числа) довольно утомительно при работе с большими наборами данных.
Поэтому я потратил некоторое время, чтобы попытаться оптимизировать его; и я закончил (на данный момент) с
= DATE(A1/10000, MOD(A1/100, 100), MOD(A1,100))
(я добавил пробелы только для представления.)< /p>
Это основано на том факте, что Excel округляет аргументы до целого числа при вызове DATE или MOD; в некоторой степени предполагается, что число в A1 находится между 19000000 и 20 с чем-то. Это даже работает нормально, когда ячейка в A1 на самом деле содержит текстовое значение, которое выглядит как дата ггггммдд (как в ваших предложениях), что может оказаться полезным.
Большое спасибо за это. Это сэкономило мне много времени. Очень признателен. Лестер
Большое спасибо за решение. очень быстро и легко
У меня есть программа для записи функции =date(2018,1,30) при создании листа. Когда я открываю лист, он отображается как 43130, серийный номер. Как заставить его отображаться как 30.01.2018 без необходимости открывать лист, дважды щелкнуть, чтобы изменить ячейку, а затем ввести, чтобы отобразить формат даты?
Это потому, что ваша ячейка имеет номер формата "Общий". Ознакомьтесь с этой статьей, чтобы применить нужный формат
=DATEVALUE(RIGHT([дата],2)&"/"&MID([дата],5,2)&"/"&LEFT([дата],4)). делает все это в одной ячейке.
Читайте также: