Как прибавлять в Excel числа, дату (день, месяц, год), проценты? – Офисные программы

Прибавляем месяцы к дате в MS EXCEL. Найдем дату, отстоящую от начальной даты на заданное количество месяцев.

Если необходимо к последнему дню месяца прибавить 1 месяц таким образом, чтобы новая дата была также последним днем месяца, то нужно использовать формулу =КОНМЕСЯЦА(A6;1)

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

Как перевести дни в месяцы Эксель? Лучшие ответы на вопросы

1. Дата, для которой нужно определить день недели – обязательный аргумент
2. Тип – необязательный параметр, который указывает какой день недели считать первым. Например, в странах восточной Европы первый день недели – понедельник, в США – воскресенье. В любом случае, формула может считать первым днем любой день недели. Если аргумент не указан – первым днем считается воскресенье. При записи формулы – Excel выведет подсказку с перечнем возможных параметров

Главная особенность, как используется функция МЕСЯЦ в Excel, это возврат значения месяца из даты, который заданный как числовой формат. Месяц будет возвращен функцией как целое число в диапазоне чисел от 1 до 12, что соответствует месяцам от января до декабря.

Форматирование даты в Excel

Итог: узнаете, как изменить форматирование даты для сгруппированного поля в сводной таблице.

Когда мы группируем поле «Дата» в сводной таблице с помощью функции «Группировать», форматирование чисел для поля «День» фиксируется. Он имеет следующий формат «день-месяц» или «d-ммм».

Если мы попытаемся изменить числовой формат поля День/Дата, это не сработает. Ничего не меняется, когда мы заходим в Настройки поля> Числовой формат и меняем числовой формат на пользовательский или формат даты.

Форматирование чисел не работает, потому что элемент сводки — это фактически текст, а НЕ дата.

Когда мы группируем поля, функция группирования создает элемент Дни для каждого дня одного года. Он сохраняет название месяца в именах полей Day, и фактически это группа номеров дней (1-31) для каждого месяца.

На самом деле можно увидеть этот список текстовых элементов в файле pivotCacheDefinition.xml. Чтобы увидеть, что вы можете изменить расширение файла Excel на .zip и перейти к папке PivotCache.

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

Решение № 1 — Не используйте группы дат

Первым решением является создание полей (столбцов) в диапазоне исходных данных с различными группами по году, кварталу, месяцу, дням и т.д

Я подробно объясняю это в своей статье «Группировка дат в сводной таблице». Источник данных.

Использование собственных полей из исходных данных для разных групп дат даст вам контроль над форматированием чисел поля в сводной таблице. Вы также можете создать таблицу календаря с группировками, если вы используете Power Pivot.

Автоматическая группировка полей даты

Если вы используете Excel 2016 (Office 365), то поле даты автоматически группируется при добавлении его в сводную таблицу.

Автоматическая группировка является настройкой по умолчанию, которую можно изменить. См. мою статью «Даты группирования в сводной таблице». Даты группирования VERSUS приведены в исходных данных, чтобы узнать больше.

Как только поле даты будет разгруппировано, вы можете изменить форматирование поля.

Чтобы изменить форматирование чисел в несгруппированном поле Дата:

1. Щелкните правой кнопкой мыши ячейку в поле даты сводной таблицы.
2. Выберите настройки поля …
3. Нажмите кнопку «Числовой формат».
4. Измените форматирование даты в окне «Формат ячеек».
5. Нажмите ОК и ОК.

Опять же, это работает только для полей, которые НЕ сгруппированы. Если вы снова сгруппируете поле после изменения форматирования, форматирование элементов в поле «Дни» изменится на «1 января».

Решение №2. Изменение имен элементов сводки с помощью VBA

Если вы действительно хотите использовать функцию Group Field, то мы можем использовать макрос для изменения имен элементов сводки. Создается впечатление, что изменилось форматирование даты, но на самом деле меняется текст в каждом названии элемента сводки.

Следующий макрос перебирает все сводные элементы сгруппированного поля «Дни» и изменяет форматирование чисел на пользовательский формат. По умолчанию я установил «m/d», но вы можете изменить его на любой формат даты для месяца и дня. Просто помните, что элемент НЕ будет содержать год, так как элемент не является фактической датой.

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

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

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

Макрос форматирования сгруппированных элементов

Был задан большой вопрос в комментариях об изменении форматирования для сгруппированных чисел. Это та же проблема, что и для групп дат. Имена групп — это текст, а не цифры. Тем не менее, мы можем использовать макрос, чтобы изменить их.

Вот код макроса. Вам просто нужно изменить значение константы sGroupField вверху на имя вашего сгруппированного поля. При необходимости вы также можете изменить формат чисел в sNumberFormat.

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

Пожалуйста, оставьте комментарий ниже с любыми вопросами или предложениями о том, как мы можем улучшить это. Спасибо! 🙂

Если ячейка А1 содержит 4 июля. 2010, а ячейка А2 содержит б (что обозначает пятницу), то формула возвращает 9 июля, 2010. Это первая пятница после 4 июля 2010 года (дня, который приходится на воскресенье).

Как форматировать даты в Excel для сводных таблиц

Следующая формула возвращает указанный день недели, который наступает после определенной даты. Например, вы можете применять эту формулу для определения даты первой пятницы после 4 июля 2010 года. Формула предполагает, что ячейка А1 содержит дату, а ячейка А2 — число от 1 до 7 (1 соответствует воскресенью, 2 — понедельнику и т. д.): =A1+A2-ДЕНЬНЕД(A1)+(А2 .