☰

Excel — автоматическое заполнение строки диапазоном дат, соответствующим определенному шаблону

tag-icon • tag-icon microsoft-excel
Excel — автоматическое заполнение строки диапазоном дат, соответствующим определенному шаблону

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

1. 11th - 25th
2. 26th - 10th

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

January 11th, 2012 - January 25th, 2012
January 26th, 2012 - February 10th, 2012

И пусть Excel автоматически заполнит еще много ячеек по тому же шаблону:

January 11th, 2012 - January 25th, 2012
January 26th, 2012 - February 10th, 2012
February 11th, 2012 - February 25th, 2012
....
December 11th, 2012 - December 25th, 2012

Как я могу это сделать?

решение1

Отформатируйте столбцы в предпочитаемом вами формате даты. Введите дату 1/11/2012 в ячейку A1 и дату 1/25/2012 в ячейку B2. Скопируйте следующий код в A2;

=DATE(YEAR(B1),MONTH(B1), DAY(B1)+1)

и скопируйте это в B2;

=IF(DAY(B1)=10, DATE(YEAR(B1), MONTH(B1), DAY(25)), DATE(YEAR(B1),MONTH(B1)+1, DAY(10)))

Продолжайте, выделите A2 и B2 и перетащите их вниз настолько, насколько вам нужно.

Когда все будет готово, должно получиться примерно так:

Скриншот

решение2

Вот решение для одной строки. Внимание, это гигантская формула, и она требует отдельной таблицы поиска.

Где-нибудь в вашей рабочей тетради вам нужно будет создать следующую таблицу:

0   January
1   February
2   March
3   April
4   May
5   June
6   July
7   August
8   September
9   October
10  November
11  December

В моем примере эта таблица находится в I1:J12, поэтому скорректируйте формулу соответствующим образом. В A1 введите:

=VLOOKUP(MOD(ROUNDDOWN((ROW()-0.5)/2,0),12),$I$1:$J$12,2,FALSE) & IF(ISODD(ROW())," 11th, "," 26th, ") & 2012 + INT((ROW()-0.5)/24) & " - " & VLOOKUP(MOD(ROUNDDOWN(ROW()/2,0),12),$I$1:$J$12,2, FALSE) & IF(ISODD(ROW())," 25th, "," 10th, ") & 2012 + INT(ROW()/24)

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

=VLOOKUP(MOD(ROUNDDOWN((ROW()-0.5)/2,0),12),$I$1:$J$12,2,FALSE) & 
 IF(ISODD(ROW())," 11th, "," 26th, ") &
 2012 + INT((ROW()-0.5)/24) &
 " - " &
 VLOOKUP(MOD(ROUNDDOWN(ROW()/2,0),12),$I$1:$J$12,2, FALSE) &
 IF(ISODD(ROW())," 25th, "," 10th, ") &
 2012 + INT(ROW()/24)

решение3

Метод без формулы

  1. Введите вА1: 11/1/2012
    Введите вА2: 11/2/2012
    ВыделятьА1:А2, заполнить вниз (перетаскиванием мыши), например доА12( 11/12/2012)

  2. Введите вА13: 26/1/2012
    Введите вА14: 26/2/2012
    ВыделятьА13:А14, заполнить вниз (перетаскиванием мыши), например доВ24( 26/12/2012)

  3. Сортировать столбецАчто будет датой начала периода

  4. (а) Повторите для столбцаБчто будет датой окончания периода (каждое 25-е и 10-е число)
    ИЛИ
    (b) Установите следующую начальную дату - 1 как конечную дату, т.е. введитеВ1: =A2-1, затем скопируйте вниз доВ2:В23

  5. Формат столбцаА:Бпо желанию в Формате ячеек (Ctrl-1), напримерmmmm dd, yyyy

Бонус

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

  1. Введите вС1: =TEXT(A1,"mmmm dd, yyyy") & " - " & TEXT(B1, "mmmm dd, yyyy")
    Это даст вам что-то вродеJanuary 11, 2012 - January 25, 2012

  2. Копировать внизС2:С24

Связанный контент