Poldet1.ru

Пул Дет №1
4 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как преобразовать числа в год / месяц / день или дату в Excel?

Исправление чисел, превратившихся в даты

При импорте данных в Excel из других приложений иногда приходится сталкиваться с неприятной проблемой — дроби преобразуются в даты:

Испорченные данные

Обычно это происходит, когда региональные настройки во внешнем приложении не совпадают с региональными настройками в Windows и Excel. Например, вы загружаете данные из американской или европейской системы учета (где между целыми и дробными частями стоит точка), но в Excel у вас стоят русские настройки (где между целыми и дробными частями стоит запятая, а точка используется как разделитель в дате).

При импорте Excel пытается распознать тип входных данных и следует простой логике: если что-то содержит точку (т.е. Она будет преобразована в дату, если она выглядит как русский разделитель дат. Текст останется для всего, что не похоже на дату.

Взяв в качестве примера поврежденные данные на рисунке выше, давайте рассмотрим все возможные сценарии:

  • В ячейке A1 исходное число 153.4182 осталось текстом, т.к. на дату совсем не похоже (не бывает 153-го месяца)
  • В ячейке A2 число 5.1067 тоже осталось текстом, т.к. в Excel не может быть даты мая 1067 года — самая ранняя дата, с которой может работать Excel — 1 января 1900 г.
  • А вот в ячейке А3 изначально было число 5.1987, которое на дату как раз очень похоже, поэтому Excel превратил его в 1 мая 1987, услужливо добавив единичку в качестве дня:

Неправильная дата

Еще одна неправильная дата

Вот такие варианты. Проблему с текстовыми числами еще можно решить заменой точки на запятую, но с датами замена точки не сработает. А попытка изменить их формат на числовой выведет не исходные значения, а внутренние коды даты Excel — количество дней от 01.01.1900 до текущей даты:

Неправильное число после изменения формата

Вся эта история представлена тремя принципиально различными способами.

Способ 1. Заранее в настройках

Если данные еще не загружены, можно заранее установить точку в качестве разделителя целых и дробных чисел через Файл — Опции — Дополнительно:

Настройка разделителей в окне параметров Excel

Снимите флажок Использовать системные разделители и введите полную остановку в поле Десятичный разделитель.

После этого у вас не возникнет проблем с импортом данных.

Способ 2. Формулой

Если данные уже загружены, можно использовать простую формулу для получения исходных чисел из испорченного беспорядка «дата-текст-число»:

Формула исправления чисел из дат

=—ЕСЛИ( ЯЧЕЙКА(«формат»;A1)=»G» ; ПОДСТАВИТЬ(A1;». «;»,») ; ТЕКСТ(A1;»М,ГГГГ») )

В английском варианте это будет :

=—IF (CELL («format «;A1)=»G»; SUBSTITUTE (A1;».»;»,»); TEXT (A1;»M ,YYYY «))

Логика проста, но здесь нет ничего удивительного:

  • Функция CELL определяет числовой формат исходной ячейки и выводит «G» для текста/чисел или «D3» для дат.
  • Если исходная ячейка содержит текст, используйте функцию SUBSTITUTE для замены полной остановки запятой.
  • Если исходная ячейка содержит дату, напечатайте ее как «номер месяца — запятая — год» с помощью функции ТЕКСТ.
  • Чтобы преобразовать полученное текстовое значение в реальное число, выполните нелепую математическую операцию — добавьте перед формулой два знака минус, имитируя двойное умножение на -1.

Способ 3. Макросом

Если такую процедуру по лечению испорченных чисел приходится делать часто, имеет смысл автоматизировать процесс с помощью макроса. Для этого нажмите Alt + F11 или кнопку Visual Basic на вкладке Developer, вставьте новый пустой модуль в нашу книгу через меню Insert — Module и скопируйте туда код:

После выделения проблемных ячеек необходимо запустить макрос, нажав Alt + F8 или команду Макросы на вкладке Разработчик (Разработчик — Макросы). Наша система немедленно исправит все испорченные числа.

Примеры использования функции DATE (ДАТА) в Excel

Пример 1: Получение числового значения даты, с использованием данных Года, Месяца, Дня

Числовые значения могут быть получены из компонентов даты Год, Месяц и День.

Как использовать функцию DATE (ДАТА) в Excel

В приведенном выше примере, функция DATE (ДАТА) использует три аргумента: значение «2016» в качестве значения года, значение «4» в качестве месяца и «1» в качестве значения дня.

Функция возвращает «42461», когда ячейка отформатирована как «General» (Общие), и возвращает «4/1/16», когда ячейка отформатирована как ‘Date’.

Обратите внимание, что Excel сохраняет дату или время как числовое значение.

В зависимости от вашего региона формат может отличаться. В России, например, формат даты DD-MM-YYYYY, а в Великобритании — MM-DD-YYYYY.

В следующем примере мы покажем, как использовать данные Year, Month и Day в разных ячейках с помощью формулы.

Как использовать функцию DATE (ДАТА) в Excel


Пример 2: Использование отрицательных значений в функции DATE (ДАТА)

Вы можете использовать отрицательные значения при указании Месяца и Дня в ячейке. При использовании отрицательных значений при указании месяца, скажем, «-1», Excel отнимет 1 месяц от Года.

Например, если вы используете формулу =DATE(2017,-1,1) или =DATE(2017;-1;1), то возвращается дата 1 ноября 2016 года. Однако вы можете использовать любое отрицательное число в значении месяца.

Как использовать функцию DATE (ДАТА) в Excel

Та же логика работает, когда вы используете значение месяца более чем «12». В этом случае, Excel прибавляет к значению Года столько месяцев, на сколько исходное значение Месяца больше 12.

Например, если вы введете формулу =DATE(2016,14,1) или =DATE(2016;14;1), она вернет дату 1 февраля 2017 года.

Как использовать функцию DATE (ДАТА) в Excel

Обратите внимание, что формат даты, показанный в примерах выше, отражает настройки моей системы. В зависимости от настроек вашей системы возможны отклонения.

Кроме того, вы можете также использовать отрицательные значения Дня, или те что больше, чем «31» — функция DATE (ДАТА) будет корректировать дату по логике описанной выше.

Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе «От новичка до мастера Excel«. Успей зарегистрироваться по ссылке!

Он рассматривает время и дату как обычное число. С помощью электронной таблицы такие данные преобразуются путем приравнивания дня к единице. Следовательно, значение времени меньше единицы. Например, 12.00 равно 0,5.

Преобразовав значение даты в число, равное количеству дней от 1 января 1900 года (как решили разработчики) до данной даты, электронная таблица может вычислить количество дней от 1 января 1900 года. Для преобразования 13 апреля 1987 года мы получим 31880. Другими словами, с 1 января 1900 года прошло 31 880 дней.

Этот принцип лежит в основе расчета временных данных. Чтобы узнать количество дней между двумя датами, просто вычтите период до и период после.

Пример функции ДАТА

Постройте значение данных, собрав их из отдельных элементов — чисел.

Синтаксис: год; месяц; день.

Все аргументы являются обязательными. Доступны следующие форматы: для года — от 1900 до 9999; для месяца — от 1 до 12; для дня — от 1 до 31.

Если аргумент «День» установлен на большее число (чем количество дней в указанном месяце), то лишние дни переносятся на следующий месяц. Например, если вы укажете 32 дня для декабря, результатом будет 1 января.

Пример использования данной функции:

ДАТА.

Установим большее количество дней для июня:

Большее количество дней.

Примеры использования ссылок на ячейки в качестве аргументов:

Ссылки в аргументах.

Функция РАЗНДАТ в Excel

Разница между двумя датами будет возвращена.

  • Дата начала;
  • Дата окончания;
  • Код, указывающий на единицы счета (дни, месяцы, годы и т. д.).

Существует несколько способов измерения интервала между двумя датами:

  • Результат отображения в днях — «d»;
  • В месяцах — «m»;
  • В годах — «y»;
  • В месяцах, не включая годы — «ym»;
  • В днях, не включая месяцы и годы — «md»;
  • В днях, не включая года — «yd».

Использование двух последних аргументов («md», «yd») может привести к неправильному значению в некоторых версиях Excel. Лучше использовать альтернативные формулы.

Примеры работы функции RANGE :

РАЗНДАТ.

Для этой функции в Excel 2007 нет справочника, но она действительно работает. Но вы должны проверить результаты, так как возможны ошибки.

Функция ГОД в Excel

Возвращает год в виде целого числа (от 1900 до 9999), который соответствует заданной дате. В функцию передается единственный аргумент — числовая дата. Аргумент должен быть введен через функцию DATE или представлять собой результат вычисления других формул.

Пример использования функции «ГОД» :

ГОД.

Функция МЕСЯЦ в Excel: пример

Месяц (1-12) возвращается в виде целого числа для даты, заданной в числовом формате. Ваш аргумент — числовая дата месяца, которую вы хотите отобразить. Даты в текстовом формате не могут быть корректно обработаны этой функцией.

Примеры использования функции MONTH:

МЕСЯЦ.

Примеры функций ДЕНЬ, ДЕНЬНЕД и НОМНЕДЕЛИ в Excel

Возвращает день как целое число (от 1 до 31) для заданной даты в числовом формате. Аргументом является дата дня, которую нужно найти, в числовом формате.

ДЕНЬ.

Для заданной даты функция DAYNED может вернуть серийный номер для этого дня недели:

ДЕНЬНЕД.

В соответствии с этой функцией воскресенье считается первым днем недели.

Функция ЧИСЛО используется для отображения номера недели конкретной даты:

НОМНЕДЕЛИ.

Дата 24 мая 2015 года приходится на 22-ю неделю года. По умолчанию неделя начинается в воскресенье.

С понедельника.

Второй аргумент задается в виде числа 2. Таким образом, формула предполагает, что неделя начинается в понедельник (второй день недели).

Функция TODAY используется для определения текущей даты (она не имеет аргументов). Функция DAY () используется для отображения текущего времени и даты.

Функции времени ГОД, МЕСЯЦ, ДЕНЬ в Excel

Function YEAR MOUNS DAY 1 Функции времени ГОД, МЕСЯЦ, ДЕНЬ в Excel

Здравствуй, дорогой пользователь.

В этой статье мы рассмотрим три наиболее важные функции даты, а именно функцию ГОД, функцию МЕСЯЦ и функцию ДЕНЬ в Excel. Это три компонента любой даты, и теперь давайте рассмотрим, как мы можем работать с ними и зачем они нам нужны. Прежде всего, эти функции используются для извлечения определенного параметра из существующей даты, мы можем отдельно извлечь год, цифровую последовательность месяца или дня. Эти функции полезно использовать, например, когда нужно разделить данные по годам, месяцам или дням. Использование этих функций часто оправдано при работе со статистическими данными, когда их применение повышает эффективность анализа.

Теперь рассмотрим каждую из функций времени по отдельности:

Функция ГОДв Excel

Основная задача функции ГОД в Excel — вернуть год, соответствующий заданной дате. Сам год определяется функцией YEAR как целое число от 1900 до 9999.

Function YEAR MOUNS DAY 2 Функции времени ГОД, МЕСЯЦ, ДЕНЬ в Excel

Функция ГОД в Excel очень проста: =ГОД(указанная_дата_в_числовом_формате), где :

  • Date_in_numeric_format, является обязательным аргументом и представляет собой дату, год которой должен быть рассчитан. Даты необходимо вводить с помощью функции DATE, указывать ссылку на ячейку, содержащую дату в соответствующем формате, иначе она будет результатом отдельных вычислений других функций и формул. Важно! Если дата вводится в виде текста, это приведет к ошибкам и проблемам.

В Excel функция ГОД возвращает значения исключительно в григорианском календаре, независимо от того, был ли введен конкретный формат даты. Независимо от того, какое летоисчисление или календарь вы укажете, функция ГОД вернет дату в григорианском календаре.

MS Excel хранит все даты как последовательные числа, что позволяет нам работать с ними и использовать их в наших расчетах. По умолчанию первая дата, номер 1, это 1 января 1900 года, но, например, 1 января 2018 года будет указано как номер 43101, поэтому разница между двумя датами будет составлять 43101 день.

Функция МЕСЯЦв Excel

Основная функция MONTH используется в Excel для возврата месячного значения из даты, заданной в числовом формате. Месяц возвращается функцией как целое число от 1 до 12, соответствующее месяцам с января по декабрь.

Function YEAR MOUNS DAY 3 Функции времени ГОД, МЕСЯЦ, ДЕНЬ в Excel

Синтаксис, используемый функцией MES в Excel, прост и понятен: = MES(ваша_дата_в_цифровом_формате), где :

  • Date_in_numeric_format, является обязательным аргументом и представляет собой дату, месяц которой должен быть вычислен. Даты необходимо вводить с помощью функции DATE, указывая ссылку на ячейку, содержащую дату в соответствующем формате, иначе она будет результатом отдельных вычислений других функций и формул. Важно! Если дата вводится в виде текста, это приведет к ошибкам и проблемам.

Как и предыдущая рассматриваемая функция, функция MONTH в Excel работает аналогично тому, как работает функция DATE, поскольку формат даты может применяться к любой функции времени.

Функция ДЕНЬв Excel

Функция D AY в Excel возвращает номер дня в указанной дате. Эта функция возвращает текущий день в виде целого числа в диапазоне от 1 до 31 в зависимости от максимального количества дней в месяце.

Function YEAR MOUNS DAY 4 Функции времени ГОД, МЕСЯЦ, ДЕНЬ в Excel

Кроме того, синтаксис, используемый функцией DAY в Excel, ничем не примечателен из-за обилия аргументов: =DAY(ваша_дата_в_числовом_формате), где:

  • Date_in_numeric_format, является обязательным аргументом и представляет собой дату, день которой должен быть рассчитан. Даты необходимо вводить с помощью функции DATE, указывая ссылку на ячейку, содержащую дату в соответствующем формате, иначе она будет результатом отдельных вычислений других функций и формул. Важно! Если дата вводится в виде текста, это приведет к ошибкам и проблемам.

Как и предыдущие функции, функция Excel DAY хранит даты как последовательные числа, а возвращаемые ею значения соответствуют датам григорианского календаря.

Function YEAR MOUNS DAY 5 Функции времени ГОД, МЕСЯЦ, ДЕНЬ в Excel

Я решил не создавать примеры их использования отдельно для каждой функции, так как они очень похожи по характеру работы, только результаты разные; разницу вы можете увидеть на скриншоте: Вы также можете посмотреть на работу этих функций в моей статье «Как посчитать количество дней в Excel» в качестве примера.

Ну, вот и все, что я хотел рассказать вам о функциях даты, таких как функция ГОД, функция МЕСЯЦ и функция ДЕНЬ. Я очень надеюсь, что моя статья помогла вам решить вашу проблему. Давайте поделимся этой статьей с нашими друзьями и коллегами, пусть нас будет много!

«Я хочу жить как бедный человек с деньгами.
»
Пабло Пикассо

Дополнительные сведения

Вы всегда можете задать вопрос в техническом сообществе Excel, попросить помощи в сообществе «Ответы» или предложить новую функцию или усовершенствование на сайте Excel User Voice.

Она может содержать неточности и грамматические ошибки, поскольку эта страница была переведена автоматически. Для нас важно, чтобы вы нашли эту статью полезной. Была ли информация полезной? Для удобства мы также предоставили ссылку на оригинал (на английском языке).

Последний день месяца в Excel

Для определения последнего месяца от текущей или произвольной даты в Excel, начиная с версии 2007 года, существует функция «КОНМЕСЯЦА» с двумя аргументами: «Начальная дата» и «Количество месяцев». Если «Количество месяцев» равно 0, то будет возвращен последний день того месяца, который содержится в аргументе «Начальная дата».

Примеры из Excel 2007-2016:

  • Последний день текущего месяца от текущей даты: =КОНМЕСЯЦА(СЕГОДНЯ();0)
  • Последний день прошлого месяца от текущей даты: =КОНМЕСЯЦА(СЕГОДНЯ();-1)
  • Последний день текущего месяца предыдущего года от текущей даты: =КОНМЕСЯЦА(СЕГОДНЯ();-12)
  • Последний день месяца от произвольной даты, записанной в ячейку «A1»: =КОНМЕСЯЦА(A1;0)

Функция «КОНМЕСЯЦА» возвращает значение в числовом виде, поэтому для правильного отображения последнего дня месяца следует к ячейке с формулой применить формат даты.

В версиях Excel по 2003 год функция «КОНМЕСЯЦА» по-умолчанию отсутствует, поэтому будем применять формулу, составленную, как и для первого дня месяца, из функций «ДАТА», «ГОД» и «МЕСЯЦ».

Примеры из Excel 2000-2003:

  • Последний день текущего месяца от текущей даты: =ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ())+1;0)
  • Последний день прошлого месяца от текущей даты: =ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());0)
  • Последний день текущего месяца предыдущего года от текущей даты: =ДАТА(ГОД(СЕГОДНЯ())-1;МЕСЯЦ(СЕГОДНЯ())+1;0)
  • Последний день месяца от произвольной даты, записанной в ячейку «A1»: =ДАТА(ГОД(A1);МЕСЯЦ(A1)+1;0)

Не перепутайте количество вычитаемых или прибавляемых месяцев в этих формулах, так как для определения последнего дня месяца, содержащегося в исходной дате, необходимо увеличить текущий месяц на единицу, чтобы вернуться к последнему дню из следующего месяца, указав количество дней, равное нулю.

В качестве альтернативы для определения последнего дня месяца можно использовать формулу, которая возвращает первый день месяца, следующего за текущим, путем вычитания единицы. =DATE(YEAR(A1);MONTH(A1)+1;1)-1;

В диалоговых формах Excel VBA определение первого и последнего дня каждого месяца имеет большое значение для автоматического заполнения интервала даты.

голоса
Рейтинг статьи
Читайте так же:
Как разбить ячейку по вертикали в листе Google?
Ссылка на основную публикацию
Adblock
detector