Как установить подстановку даты в зависимости от месяца и дня недели в excel

Обновлено: 25.06.2024

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

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

Как вычесть даты в Excel

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

Пример 1. Прямое вычитание одной даты из другой

Как вы, наверное, знаете, Microsoft Excel хранит каждую дату в виде уникального порядкового номера, начинающегося с 1, который представляет 1 января 1900 года. Таким образом, вы на самом деле вычитаете два числа, и обычная арифметическая операция работает без сучка и задоринки:

Пример 2. Вычитание дат с помощью функции Excel DATEDIF

Если вышеприведенная формула выглядит слишком простой, вы можете добиться того же результата гуру-подобным способом, используя функцию РАЗНДАТ в Excel:

Когда вы вычитаете более позднюю дату (6 мая 2015 г.) из более ранней даты (1 мая 2015 г.), операция вычитания возвращает отрицательное число (-5), как и должно быть. Однако синтаксис функции РАЗНДАТ в Excel не позволяет дате начала быть больше даты окончания, поэтому она возвращает ошибку.

Пример 3. Вычитание даты из текущей даты

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

Как и в предыдущем примере, обе формулы работают нормально, если сегодняшняя дата больше, чем дата, которую вы из нее вычитаете, в противном случае DATEDIF завершается ошибкой:

Пример 4. Вычитание дат с помощью функции Excel DATE

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

Например, следующая формула вычитает 15 мая 2015 года из 20 мая 2015 года и возвращает разницу в 5 дней:

=ДАТА(2015, 5, 20) - ДАТА(2015, 5, 15)

В заключение, когда дело доходит до вычитания дат в Excel и вы хотите узнать, сколько дней находится между двумя датами, имеет смысл выбрать самый простой и очевидный вариант — просто вычесть одну дату непосредственно из другой.< /p>

Если вам нужно подсчитать количество месяцев или лет между двумя датами, единственным возможным решением будет функция РАЗНДАТ. В следующей статье вы найдете несколько примеров формул, в которых эта функция будет подробно описана.< /p>

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

Как вычесть или прибавить дни к дате в Excel

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

Пример 1. Добавление дней к дате в Excel

Общая формула добавления указанного количества дней к дате выглядит следующим образом:

Дату можно ввести несколькими способами:

В качестве ссылки на ячейку, например. =A2 + 10
Использование функции ДАТА(год, месяц, день), например =ДАТА(2015, 5, 6) + 10
В результате другой функции. Например, чтобы добавить заданное количество дней к текущей дате, используйте функцию СЕГОДНЯ(): =СЕГОДНЯ()+10

На следующем снимке экрана показаны приведенные выше формулы в действии. Текущая дата на момент написания: 6 мая 2015 г.:

Примечание. Результатом приведенных выше формул является порядковый номер, представляющий дату.Чтобы отобразить его в виде даты, выберите ячейку (ячейки) и нажмите Ctrl+1, чтобы открыть диалоговое окно Формат ячеек. На вкладке Число выберите Дата в списке Категория, а затем выберите нужный формат даты. Подробные инструкции см. в разделе Как изменить формат даты в Excel.

Пример 2. Вычитание дней из даты в Excel

Чтобы вычесть заданное количество дней из определенной даты, вы снова выполняете обычную арифметическую операцию. Единственное отличие от предыдущего примера в том, что вместо плюса вы набираете минус :)

Вот несколько примеров формул:

=A2-10
=ДАТА(2015, 5, 6)-10
=СЕГОДНЯ()-10

Как прибавить или вычесть недели из даты

Если вы хотите прибавить или вычесть целые недели к определенной дате, вы можете использовать те же формулы, что и для прибавления/вычитания дней, и просто умножить количество недель на 7:

Добавление недель к дате в Excel:

Например, если вы добавляете 3 недели к дате в A2, используйте следующую формулу: =A2+3*7 .

Вычитание недель из даты в Excel:

Чтобы вычесть 2 недели из сегодняшней даты, введите =TODAY()-2*7 .

Как прибавить/вычесть месяцы к дате в Excel

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

Пример 1. Добавление месяцев к дате с помощью функции ДАТА в Excel

Возьмем, например, список дат в столбце A. Введите количество дат, которое вы хотите добавить (положительное число) или вычесть (отрицательное число) в какой-либо ячейке, например C2.

Введите следующую формулу в ячейку B2, а затем перетащите угол ячейки до конца вниз, чтобы скопировать формулу в другие ячейки:

=ДАТА(ГОД(A2), МЕСЯЦ(A2) + $C$2, ДЕНЬ(A2))

Теперь посмотрим, что на самом деле делает функция. Логика формулы очевидна и проста. Функция ДАТА(год, месяц, день) принимает следующие аргументы:

год даты в ячейке A2;
месяц даты в A2 + количество месяцев, указанное в ячейке C2, и
день даты в A2.

Да, это так просто :) Если вы введете отрицательное число в C2, формула будет вычитать месяцы, а не добавлять их:

Естественно, ничто не мешает ввести минус прямо в формулу, чтобы вычесть месяцы из даты:

=ДАТА(ГОД(A2), МЕСЯЦ(A2) - $C$2, ДЕНЬ(A2))

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

Настоящие формулы могут выглядеть примерно так:

Добавить месяцы к дате: =ДАТА(ГОД(A2), МЕСЯЦ(A2) + 2, ДЕНЬ(A2))
Вычесть месяцы из даты: =ДАТА(ГОД(A2), МЕСЯЦ(A2) - 2, ДЕНЬ(A2))

Пример 2. Добавление или вычитание месяцев из даты с помощью Excel EDATE

Microsoft Excel предоставляет специальную функцию, которая возвращает дату, которая является заданным числом месяцев до или после даты начала — функция ДАТАМЕС. Он доступен в современных версиях Excel 2007, 2010, 2013 и готовящейся к выпуску Excel 2016.

В ваших формулах EDATE(дата_начала, месяцы) вы указываете следующие 2 аргумента:

Start_date — начальная дата, от которой нужно отсчитывать количество месяцев.
Месяцы — количество месяцев, которое нужно добавить (положительное значение) или вычесть (отрицательное значение).

Следующая формула, используемая для нашего столбца дат, дает точно такие же результаты, как и функция ДАТА в предыдущем примере:

При использовании функции ДАТАМЕС вы также можете указать дату начала и номер месяца для прибавления/вычитания непосредственно в формуле. Даты следует вводить с помощью функции ДАТА или в качестве результатов других формул. Например:

Примечание. Функция Excel ДАТАМЕС возвращает порядковый номер, представляющий дату. Чтобы заставить Excel отображать его как дату, вы должны применить формат даты к ячейкам с вашими формулами EDATE. Подробные инструкции см. в разделе Изменение формата даты в Excel.

Как вычесть или прибавить годы к дате в Excel

Добавление лет к дате в Excel выполняется аналогично добавлению месяцев. Вы снова используете функцию ДАТА(год, месяц, день), но на этот раз указываете, сколько лет вы хотите добавить:

В листе Excel формулы могут выглядеть следующим образом:

=ДАТА(ГОД(A2) + 5, МЕСЯЦ(A2), ДЕНЬ(A2))

=ДАТА(ГОД(A2) - 5, МЕСЯЦ(A2), ДЕНЬ(A2))

Если вы введете число года для прибавления (положительное число) или вычитания (отрицательное число) в какой-либо ячейке, а затем обратитесь к этой ячейке в функции ДАТА, вы получите универсальную формулу:

Добавить/вычесть дни, месяцы и годы до даты

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

Чтобы добавить годы, месяцы, дни:

Чтобы вычесть годы, месяцы, дни:

Например, следующая формула добавляет 2 года 3 месяца и вычитает 15 дней из даты в ячейке A2:

=ДАТА(ГОД(A2) + 2, МЕСЯЦ(A2) + 3, ДЕНЬ(A2) - 15)

Применительно к нашему столбцу дат формула принимает следующий вид:

=ДАТА(ГОД(A2) + $C$2, МЕСЯЦ(A2) + $D$2, ДЕНЬ(A2) + $E$2)

Как складывать и вычитать время в Excel

В Microsoft Excel вы можете добавлять или вычитать время с помощью функции ВРЕМЯ. Она позволяет работать с единицами времени (часами, минутами и секундами) точно так же, как вы работаете с годами, месяцами и днями с помощью функции ДАТА.

Чтобы добавить время в Excel:

Чтобы вычесть время в Excel:

Где A2 содержит значение времени, которое вы хотите изменить.

Например, чтобы добавить 2 часа 30 минут и 15 секунд ко времени в ячейке A2, вы можете использовать следующую формулу:

=A2 + ВРЕМЯ(2, 30, 15)

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

=A2 + ВРЕМЯ(2, 30, -15)

Приведенная выше формула добавляет 2 часа 30 минут ко времени в ячейке A2 и вычитает 15 секунд.

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

=A2 + ВРЕМЯ($C$2, $D$2, $E$2)

Если исходные ячейки содержат и дату, и время, приведенная выше формула также работает идеально:

Мастер формул даты и времени – быстрый способ добавления и вычитания дат в Excel

Теперь, когда вы знаете множество различных формул для вычисления дат в Excel, не хотели бы вы иметь только одну, которая может делать все это? Конечно, такой формулы никогда не может быть. Однако существует Мастер даты и времени, который может создать любую формулу для вас на лету, при условии, что в вашем Excel установлен наш Ultimate Suite. Вот как:

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

Для примера добавим несколько месяцев к дате в ячейке A2. Для этого перейдите на вкладку «Добавить», введите A2 в поле «Введите дату» (или щелкните поле и выберите ячейку на листе) и введите количество месяцев, которое нужно добавить, в поле «Месяц».

Мастер создает формулу и показывает ее предварительный просмотр в ячейке. Он также показывает расчетную дату в разделе Результат формулы:

Если вас устраивает результат, нажмите кнопку Вставить формулу. После добавления формулы ее можно скопировать в любое количество ячеек:

Это была довольно простая формула, не так ли? Давайте дадим мастеру что-то более сложное для работы. Например, давайте отнимем несколько лет, месяцев, недель и дней от даты в A2. Для этого перейдите на вкладку «Вычесть» и введите числа в соответствующие поля. Или вы можете ввести единицы измерения в отдельные ячейки и предоставить ссылки на эти ячейки, как показано на снимке экрана ниже:

При нажатии кнопки Вставить формулу в A2 вводится следующая формула:

Если вы планируете копировать формулу в другие ячейки, необходимо изменить все ссылки на ячейки, кроме A2, на абсолютные ссылки, чтобы формула копировалась правильно (по умолчанию мастер всегда использует относительные ссылки). Чтобы исправить ссылку, просто введите знак $ перед координатами строки и столбца, например:

И получите следующие результаты:

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

Если вы хотите поиграть с мастером формул даты и времени в своих рабочих листах, вы можете загрузить 14-дневную пробную версию Ultimate Suite.

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

Вас также может заинтересовать

238 комментариев к статье "Как складывать и вычитать даты, дни, недели, месяцы и годы в Excel"

Я рассчитал разницу между двумя датами, используя формулу datedif,
но у меня возникли проблемы с вычислением разницы по результату, который я получил по приведенной выше формуле, например, например. (дд/мм/гггг)

Дата начала = 20.04.1983 Дата окончания = 31.03.2017
результат, который я получил: 33 года, 11 месяцев, 11 дней. Теперь я хочу вычесть 49 дней из результата, который я не могу сделать. Пожалуйста, помогите мне решить расчет.

Попробуйте следующую формулу:

=РАЗНДАТ("20.04.1983",("31.03.2017"-49),"Г") & "лет", " & РАЗНДАТ("20.04.1983",("31/ 03/2017"-49),"ГМ") & "месяцы, " & DATEDIF("20/04/1983",("31/03/2017"-49),"MD") & "дни"

Надеюсь, это поможет вам.

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

У меня возникли проблемы с перечислением дат через функцию.
я пытаюсь получить следующее:
A1= Текущая дата в формате ГГ Ммм <= ТЕКСТ(СЕГОДНЯ(),"ГГ Ммм")>скажем так: "17 октября"
A2= Месяц спустя: "17 ноября"
A3= Через месяц после этого: "17 декабря"
и вот здесь возникают проблемы A4= Еще один месяц: "18 января" "
Вот как это должно читаться, но следующий, A5, читает "17 февраля", когда он должен читать "18 февраля"
и затем он должен продолжаться оттуда, обновляя год каждый январь проходит. И каждый раз, когда текущий месяц изменяется, это будет отражаться во всех месяцах во всем столбце A.

Я хочу показать только последние 4 недели игры для каждого игрока со скользящей суммой. Я пробовал несколько формул, и у меня все еще есть проблемы: (СУММЕСЛИ и как вычесть недели).
Всего 149 на 5 недель. 20 17 и 21 июня должны скрываться или исчезать и заменяться 27 17 и 33 июня и так далее. Итого должно быть 128.

П1 П2 П3 П4 П5 Тотал

21 33 33 30 32 149

Спасибо за помощь. Использование Excel 2007.

Как добавить 3 года 4 месяца 5 дней к 2 годам 4 месяца 5 дней
Чтобы узнать общий опыт

Вкладка называется «Открытая проблема». На ней есть столбец с датой отчета,
у меня есть столбец статуса, и
мне нужно, чтобы в столбце статуса отображалось «Открыто менее 30 дней», «между 30 днями». до 60 дней», «открыто и более 90 дней».

Мне удалось получить =TODAY()-A5 =, чтобы получить количество открытых дней, но я не знаю, как двигаться дальше

Чтобы суммировать набор данных по месяцам, можно использовать формулу, основанную на функции СУММЕСЛИМН с помощью функции КОНМЕСЯЦА. В показанном примере формула в F5 выглядит так:

где сумма (C5:C16) и дата (B5:B16) являются именованными диапазонами.

В этом примере цель состоит в том, чтобы просуммировать суммы, показанные в столбце C, по месяцам, используя даты в столбце B. В этом случае мы также учитываем год, поэтому промежуточные итоги будут за январь 2021 г. , февраль 2021 г. и т. д. Для выполнения этой задачи мы используем функцию СУММЕСЛИМН, которая может суммировать числовые значения на основе нескольких критериев. Ниже мы рассмотрим альтернативу, основанную на функции СУММПРОИЗВ, которая немного более гибкая.

В обоих приведенных ниже решениях сумма именованных диапазонов (C5:C16) и дата (B5:B16) используются только для удобства. Вместо этого вы также можете использовать абсолютные ссылки. Обратите внимание, что даты в E5:E10 являются датами первого месяца для каждого месяца, по которому мы хотим суммировать. Если вы предпочитаете отображать только названия месяцев, вы можете сделать это с помощью пользовательского числового формата, как описано ниже.

РЕШЕНИЕ СУММЕСЛИМН

Функция СУММЕСЛИМН может суммировать значения в диапазонах на основе нескольких критериев.

В этом случае нам нужно настроить СУММЕСЛИМН для суммирования сумм по месяцам, используя два критерия: один для соответствия датам, большим или равным первому дню месяца, и один, чтобы соответствовать датам, меньшим или равным равно последнему дню месяца. СУММЕСЛИМН входит в группу функций, которые разбивают критерии на две части, поэтому нам также необходимо объединить даты с логическими операторами. Общая структура показана ниже, где "первый" представляет первый день месяца, а "последний" – последний день месяца:

Поскольку у нас уже есть первый день каждого месяца в столбце E, мы можем взять это значение непосредственно из ячейки E5. Чтобы получить последний день месяца, мы используем функцию КОНМЕСЯЦА. После этих замен у нас есть окончательная формула:

Обратите внимание, что КОНМЕСЯЦА настроена с нулевым значением для аргумента months, так как мы хотим остаться в том же месяце.

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

Примечание: если даты, с которыми вы работаете, на самом деле являются datetime (датами со временем), вам потребуется немного изменить логику в формуле, чтобы поймать даты в последний день месяца, которые имеют значения времени:

В этой версии мы добавляем день к последнему дню месяца, чтобы получить первый день следующего месяца, и меняем оператор на больше (>) вместо больше или равно (>=).

С жестко заданными датами

Для суммирования по месяцам с жестко заданным месяцем можно использовать следующую формулу:

Эта формула использует функцию ДАТА для создания первого и последнего дня месяца. Использование DATE таким образом безопаснее, чем ввод даты в виде текста, поскольку это гарантирует, что Excel правильно интерпретирует дату.

Опция СУММПРОИЗВ

Еще один удобный способ суммирования по месяцам — использовать функцию СУММПРОИЗВ вместе с функцией ТЕКСТ, например:

В этой версии мы используем функцию ТЕКСТ для преобразования дат в текстовые строки «ММГГ». Поскольку в списке 12 дат, в результате получается массив с 12 значениями, подобным этому:

, которая затем сравнивается с текстовой строкой того же типа для месяца в E5 ("0121"). Результатом является массив значений TRUE и FALSE. Затем этот массив умножается на значения суммы. Математическая операция приводит значения ИСТИНА и ЛОЖЬ к единицам и нулям, поэтому окончательный результат в СУММПРОИЗВ выглядит следующим образом:

Функция СУММЕСЛИМН может выполнять такую операцию в автономной формуле, поскольку для СУММЕСЛИМН требуется диапазон дат; вы не можете заменить массив. Одна приятная особенность этой формулы заключается в том, что она автоматически игнорирует значения времени, которые могут быть связаны с датами, поэтому нет необходимости беспокоиться о логике дат и времени, которые могут иметь место в последний день месяца, как в случае с СУММЕСЛИМН выше.< /p>

Отображать даты как имена

Чтобы отображать даты в формате E5:E10 только как имена, вы можете применить пользовательский числовой формат "мммм". Выберите даты, затем используйте Control + 1, чтобы открыть диалоговое окно «Формат ячеек», и примените формат даты, как показано ниже:

Это позволяет вам использовать действительные даты Excel в столбце E (необходимом для формулы) и отображать их так, как вам нравится.

Решение сводной таблицы

Сводная таблица — еще одно отличное решение, когда вам нужно суммировать данные по годам, месяцам, кварталам и т. д., потому что она может сделать такую группировку за вас вообще без каких-либо формул. Для параллельного сравнения формул vs.сводные таблицы, см. это видео: Почему сводные таблицы.

Говорит, что вы вводите дату в одну ячейку, и она отображается как 13.12.2015. Есть ли способ показать только месяц или день недели, или текст названия месяца или дня недели, например, декабрь или воскресенье? Следующие методы могут помочь вам легко преобразовать или отформатировать любые типы дат, чтобы отображать только название дня недели или название месяца в Excel.

Преобразование даты в название или число дня недели/месяца/года с помощью формата ячейки

Мы можем легко настроить формат даты и отображать даты в виде названий дней недели или месяцев только в Excel.

Kutools для Excel

Выделиться из толпы

Попрощайтесь с ужасным VBA и формулами!

ddd

Примечание: "ddd" будет отображать дату как название дня недели, например "Sat". Возьмем, к примеру, дату 7/03/2019. В следующей таблице показаны другие настраиваемые форматы даты:

Повышение эффективности работы на 80 % благодаря одновременному просмотру и редактированию нескольких документов
Сократите количество кликов каждый день, попрощайтесь с мышью.
Красочные вкладки делают утомительную работу интереснее и создают живое и яркое рабочее пространство.
Выбор из более чем 90 000 высокоэффективных людей и более 300 известных компаний.

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

Один щелчок, чтобы преобразовать несколько дат в названия недель/месяцев/годов или числа в Excel

Вы когда-нибудь преобразовывали дату в день недели, щелкнув правой кнопкой мыши и указав код форматирования в диалоговом окне "Формат ячейки"? Здесь, с Kutools for Excel Функция «Применить форматирование даты», вы можете быстро показать ряд дат как только названия месяцев или дней недели одним щелчком мыши в Excel! Полнофункциональная бесплатная пробная версия на 30 дней!

Kutools for Excel — включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия на 30 дней, кредитная карта не требуется! Получить сейчас

Преобразование даты в название или номер дня недели/месяца с помощью функции ТЕКСТ

Функция ТЕКСТ Microsoft Excel может помочь вам легко преобразовать дату в соответствующее название месяца или дня недели.

В пустой ячейке введите формулу =ТЕКСТ(A2,"мммм"), в данном случае в ячейку C2. и нажмите клавишу Enter. Затем перетащите маркер автозаполнения этой ячейки в нужный диапазон.

И дата была преобразована в название месяца. Смотрите скриншот:

Примечание. Вы можете изменить «мммм» на другой код формата в соответствии с приведенной выше таблицей. Например, вы также можете преобразовать дату в название дня недели с помощью формулы =ТЕКСТ(A2,"дддд").

Хотите сейчас выделиться из толпы? Более 30 функций для свиданий повысят ваш уровень мастерства!

Благодаря более чем 30 функциям даты в Kutools for Excel вы получите практические навыки о датах за 3 минуты и будете работать быстрее и лучше, чем другие, легко получите прибавку к зарплате и продвижение по службе!

Чтобы понравиться

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

Позаботьтесь о своей семье

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

Наслаждайтесь здоровой жизнью

Групповая вставка, изменение или вычисление дат, сокращение сотен кликов каждый день, прощайте, рука мыши.

Никогда не беспокойтесь об увольнениях

Повысьте эффективность работы на 91 %, решите 95 % проблем с датами в Excel, закончите работу раньше срока.

Освободите память

12 формул Kutools о датах, перестаньте запоминать болезненные формулы и коды VBA, работайте с легкостью.

Kutools for Excel предлагает 300 удобных инструментов для 1500 рабочих сценариев, всего за 39,0 долларов США, но стоит более 4000,0 долларов США. Обучение Excel других, экономьте каждую копейку для себя!

Выбор из 110 000+ высокоэффективных людей и 300+ известных компаний. Полнофункциональная бесплатная пробная версия на 30 дней, кредитная карта не требуется! Подробнее Загрузить сейчас

Преобразование даты в название дня недели/месяца с помощью функции ВЫБОР

Если эти специальные коды форматирования трудно запомнить и применять в формулах, вы также можете применить функцию ВЫБОР для преобразования даты в название месяца или дня недели в Excel. Пожалуйста, сделайте следующее:

В пустой ячейке введите формулу =ВЫБОР(ДЕНЬНЕД(B1),"Вс","Пн","Вт","Ср","Чт","Пт","Сб"), и нажмите клавишу ввода. Эта формула преобразует дату в день недели, как показано ниже.
Примечание. Для преобразования даты в название месяца используйте следующую формулу =ВЫБОР(МЕСЯЦ(B1),"Янв","Фев","Мар","Апр","Май","Июнь" ","июль","август","сен","октябрь","ноябрь","декабрь")

Хотите улучшить свои навыки работы в Excel прямо сейчас, сделать жизнь своей семьи лучше?

Повысьте эффективность работы на 80 %, решите 80 % проблем с Excel, сэкономьте 2 часа каждый день, чтобы улучшить себя и сопровождать семью.
Попрощайтесь с утомительными формулами и кодами VBA, освободите свою память! Сократите тысячи кликов мышью, прощайте руку мыши.
300 удобных инструментов для 1500 рабочих сценариев, которые помогут вам работать лучше и выделиться из толпы, никогда не беспокойтесь о сокращении рабочих мест.
Потратьте 39 долларов США на обучение других, а это более 4 000 долларов США, и сэкономьте каждую копейку, чтобы наслаждаться жизнью.
Выбор из 110 000+ высокоэффективных людей и 300+ известных компаний. Полнофункциональная бесплатная пробная версия на 30 дней, кредитная карта не требуется.

Преобразование дат в день недели/месяц/год или число с помощью Kutools for Excel

Функция ТЕКСТ легко работает с несколькими датами, а если их много, это займет много времени. Kutools for Excel Инструмент «Применить форматирование даты» может помочь вам легко преобразовать все даты в выбор в название месяца или название дня недели.

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

Примечание. Инструмент «Применить форматирование даты» не изменяет фактические значения.

Эта функция «Применить форматирование даты» поможет пользователям Excel одним щелчком мыши преобразовать все выбранные даты в названия месяцев, дней недели, года и т. д. Получите бесплатную пробную версию!

300 инструментов помогут вам выделиться из толпы

с Kutools for Excel - никогда не беспокойтесь о сокращении рабочих мест

Kutools for Excel предлагает 300 удобных инструментов для 1500 рабочих сценариев, помогает вам работать быстрее и лучше, чем ваши коллеги, и легко завоевываете доверие своего начальника. Вы будете последним в списке на увольнение и легко обеспечите стабильную и лучшую жизнь для своей семьи!

Функция ПОДСТАВИТЬ Находит и заменяет существующий текст новой текстовой строкой. С учетом регистра.

Чтобы использовать функцию рабочего листа Excel ПОДСТАВИТЬ, выберите ячейку и введите:

ЗАМЕНА просматривает строку в ячейке C2, находит «быстрый коричневый» и заменяет его на «медленный белый».

Как использовать ЗАМЕНУ

Функция ПОДСТАВИТЬ принимает 3 обязательных аргумента и 1 необязательный:

Текст: текст, который вы хотите заменить символами.
Old_text: текст, который вы хотите заменить
Новый_текст: текст, которым вы хотите заменить Старый_текст.
Instance_num (необязательно): указывает вхождение Old_text, которое вы хотите заменить на New_text. По умолчанию все вхождения Old_text будут заменены, если экземпляр не указан.

Вложенность SUBSTITUTE

Что, если бы нам пришлось заменить две части одной и той же строки? Давайте посмотрим, как можно объединить две функции ПОДСТАВИТЬ.

Обратите внимание, как результат ПОДСТАВИТЬ(C2,C3,C4) используется в качестве первого аргумента для второго ПОДСТАВИТЬ. Если разбить формулу на части:

=SUBSTITUTE(C2,C3,C4) оценивается как "медленная белая лиса перепрыгивает через ленивую собаку".

Затем ПОДСТАВЬТЕ("Медленный белый лис прыгает через ленивую собаку", C5,C6), что дает результат "Медленный белый лев прыгает через ленивую собаку".

Разница между SUBSTITUTE и REPLACE

Функция, аналогичная функции ЗАМЕНИТЬ, — ЗАМЕНИТЬ. В большинстве случаев их можно использовать взаимозаменяемо, однако применяется следующее эмпирическое правило.

Функция ПОДСТАВИТЬ должна использоваться, когда известна заменяемая строка текста или заменяется целое слово или слова, как в нашем примере с лисой, использованном ранее.

Функция ЗАМЕНА должна использоваться, когда известна позиция заменяемых символов в строке или когда заменяется часть слова или строки. Например, удаление дефиса из строки чисел.

Функция REPLACE также может быть вложенной, как и SUBSTITUTE, однако важно отметить, что относительное положение начального символа может измениться в зависимости от длины заменяемой строки на первом шаге вложенной формулы.

Например, замена "быстро коричневого" на "медленно белого" означает замену 11 символов на 10 символов. Поэтому, если он вложен, начальный символ для второго REPLACE будет на 1 символ от начальных позиций символов.

ЗАМЕНИТЬ советы

Обратите внимание, что число 3030 неверно, так как «20» и «20» в «2020» были заменены на «30».

Заинтересованы в дополнительных текстовых функциях?

Подробнее о ЗАМЕНЕ или о том, как используются другие текстовые функции в Excel, такие как ВЛЕВО, СРЕДН и ВПРАВО, см. в других наших статьях.

ЗАМЕНИТЬ в Google Таблицах

Функция ПОДСТАВИТЬ работает в Google Таблицах точно так же, как и в Excel:

Дополнительные примечания

Функция ЗАМЕНИТЬ чувствительна к регистру!. Вы можете использовать функции LOWER или UPPER для преобразования строк текста в согласованный регистр перед использованием функции SUBSTITUTE. Затем, при желании, вы можете использовать функцию PROPER, чтобы сделать первую букву каждого слова прописной.

Кроме того, вы можете использовать функции ПОИСК и ЗАМЕНА для имитации ЗАМЕНЫ без учета регистра. Используйте функцию ПОИСК, чтобы найти начальную позицию текстовой строки. Функция ПОИСК не чувствительна к регистру. Затем используйте функцию REPLACE, чтобы заменить текст. Вам нужно будет использовать результат функции SEARCH в качестве ввода start_num в REPLACE. Вам также нужно будет определить количество символов для замены ( num_chars ). Вы можете подсчитать их вручную или использовать функцию ДЛСТР для подсчета количества символов. Пример:

Заменить — удалить дефисы

Распространенный вопрос: как удалить дефисы из текста. Хитрость заключается в том, чтобы заменить дефис ничем.

Для этого примера предположим, что в ячейке A1 есть текст с дефисами. Вот как их удалить

Еще 3 примера замены:

Глядя на ячейку B3, мы хотим заменить слово «больной» на «большой». Мы можем использовать следующее выражение:

Если текст, который мы хотим заменить, встречается более одного раза, нам нужно указать экземпляр, который мы имеем в виду. Во втором примере у нас есть два вхождения слова «старый». Итак, если мы хотим изменить второй экземпляр на слово «серый», мы должны использовать:

Обратите внимание: если вхождение не указано, заменяются все вхождения. Итак:

Избавится от всех экземпляров слова "старый" и заменит их словом "серый". Обратите внимание, что если старый текст не может быть найден, строка остается неизменной. Итак, взглянем на последний пример:

Означает, что мы пытаемся заменить слово "черный" словом "серый". Однако, поскольку «черный» не встречается, исходная строка левитра, поставляемая в США, осталась неизменной:

ЗАМЕНИТЬ примеры в VBA

Вы также можете использовать функцию ПОДСТАВИТЬ в VBA. Тип:
application.worksheetfunction.substitute(text,old_text,new_text,instance_num)
Для аргументов функции (текст и т. д.) вы можете либо ввести их непосредственно в функцию, либо определить переменные для использования вместо этого.

При выполнении следующего кода

Содержимое ячейки A1 примет следующий вид:

Итак, слово «использование» было заменено пустой строкой. Последний параметр (1) указывает, какой экземпляр текста следует заменить, если текст присутствует более одного раза. Так, например, если мы выполним следующий оператор

Ячейка A1 будет выглядеть так

Обратите внимание, что первое вхождение "the" было заменено на "an", а второе "the" не было затронуто. Если бы мы опустили последний параметр, оба слова «the» были бы заменены

Читайте также: