Введите будням пн — пт на листе с использованием VBA в Microsoft Excel
В этой статье мы создадим макрос для извлечения дней недели между двумя датами.
В этом примере мы должны указать дату начала и дату окончания перед запуском макроса. Макрос выбирает значение даты начала из ячейки J8 и значение даты окончания из ячейки J9. После указания даты начала и окончания макрос можно выполнить, нажав кнопку «Отправить» или нажав сочетания клавиш Alt + F8.
При запуске макроса он вернет дни недели между двумя датами на новом листе. Вновь вставленный рабочий лист содержит дни недели в столбце A, за которыми следует соответствующая дата в столбце B. Дата вывода в столбце B будет в формате дд.мм.гг.
Каждая неделя будет сопровождаться пустой строкой, чтобы различать две последовательные недели.
Логическое объяснение
В макросе мы использовали функцию Weekday, чтобы различать будние дни и выходные. Функция форматирования используется для отображения даты в необходимом формате.
Функция дня недели
Функция Weekday возвращает целое число, которое представляет день недели.
Синтаксис
День недели (Date_Value, [First_Day_Of_Week])
Date_Value указывает значение даты, для которого вы хотите найти день недели.
First_Day_Of_Week указывает, какой день недели следует считать первым днем недели. В качестве входных данных он принимает целочисленное значение или значение, выбранное из перечисления FirstDayOfWeek. Если значение не указано, по умолчанию используется FirstDayOfWeek.Sunday.
|
Enumeration value |
Integer value |
Remarks |
|
FirstDayOfWeek.System |
0 |
First day of week specified in system settings |
|
FirstDayOfWeek.Sunday |
1 |
Sunday (default) |
|
FirstDayOfWeek.Monday |
2 |
Monday |
|
FirstDayOfWeek.Tuesday |
3 |
Tuesday |
|
FirstDayOfWeek.Wednesday |
4 |
Wednesday |
|
FirstDayOfWeek.Thursday |
5 |
Thursday |
|
FirstDayOfWeek.Friday |
6 |
Friday |
|
FirstDayOfWeek.Saturday |
7 |
Saturday |
Как видно из кода макроса, мы использовали Weekday (i, 2), чтобы указать понедельник как первый день недели.
Функция форматирования
Функция Format принимает выражение даты в качестве входных данных и возвращает его в виде форматированной строки.
Синтаксис функции форматирования
Формат (Date_Value, формат)
Date_Value указывает значение типа данных даты.
Формат использует строковое значение, чтобы указать, какой тип формата даты требуется.
В следующей таблице определены некоторые популярные символы, которые можно использовать для создания требуемых форматов даты / времени:
|
Character |
Description |
|
d |
displays the day as a number without a leading zero (1–31) |
|
dd |
displays the day as a number with a leading zero (01–31) |
|
ddd |
displays the day as an abbreviation (Sun–Sat) |
|
dddd |
displays the day as a full name (Sunday–Saturday) |
|
w |
displays the day of the week as a number (1 for Sunday through 7 for Saturday) |
|
ww |
displays the week of the year as a number (1–54) |
|
m |
displays the month as a number without a leading zero (1–12) |
|
mm |
displays the month as a number with a leading zero (01–12) |
|
mmm |
displays the month as an abbreviation (Jan–dec) |
|
mmmm |
displays the month as a full month name (January–december) |
|
q |
displays the quarter of the year as a number (1–4) |
|
y |
displays the day of the year as a number (1–366) |
|
yy |
displays the year as a 2-digit number (00–99) |
|
yyyy |
displays the year as a 4-digit number (100–9999) |
|
h |
displays the hour as a number without leading zeros (0–23) |
|
hh |
displays the hour as a number with leading zeros (00–23) |
|
n |
displays the minute as a number without leading zeros (0–59) |
|
nn |
displays the minute as a number with leading zeros (00–59) |
|
s |
displays the second as a number without leading zeros (0–59) |
|
ss |
displays the second as a number with leading zeros (00–59) |
Чтобы оставить пустую строку в конце каждой недели, мы проверили воскресенье, используя функцию дня недели, и увеличили значение переменной «StartingRow» на 1, чтобы следующая строка оставалась пустой.
Как видно из кода макроса, мы использовали функцию Format два раза по-разному. Во-первых, мы использовали Format (i, «ddd») для получения дня недели, а во-вторых, мы использовали Format (i, «dd.mm.yy»), чтобы получить дату в формате dd.mm.yyyy.
Объяснение кода
Dim NewWorksheet As Worksheet Установить NewWorksheet = Worksheets.Add Указанный выше код используется для объявления имени объекта Worksheet как «NewWorksheet».
Метод Worksheets.Add используется для добавления нового рабочего листа в коллекцию Worksheets. Оператор Set используется для инициализации объявленного объекта вновь вставленным листом.
For i = StartDate To EndDate Цикл FOR используется для цикла от даты начала до даты окончания.
If Weekday (i, 2) <6 Тогда оператор IF используется для проверки условия и выполнения кода на основе условия. Вышеупомянутое условие проверяет значение, возвращаемое функцией дня недели. Если оно меньше 6, условие IF возвращает истину, и код внутри оператора IF будет выполнен. В противном случае он будет пропущен.
StartRow = 1 StartingRow = StartingRow + 1 Переменная StartingRow используется для перемещения по строкам на листе. Вначале переменная инициализируется первой строкой. При каждом успешном выполнении оператора IF значение переменной увеличивается на 1, перемещаясь к следующей строке на листе.
Ячейки (StartingRow, 1)
Свойство Cells используется для ссылки на конкретную ячейку на листе.
Ячейки (row_number, column_number) можно использовать для ссылки на любую ячейку на листе, введя конкретный номер строки и номер столбца в качестве параметров. В коде Cells (StartingRow, 1), 1 указывает первый столбец, а переменная «StartingRow» определяет номер строки.
Коды можно легко понять, поскольку я включил комментарии вместе с кодами в макрос.
Пожалуйста, введите код ниже
Option Explicit
Sub ExtractWeekdays()
'Declaring two variables of Date data type
Dim StartDate As Date, EndDate As Date
'Declaring worksheet variable
Dim NewWorksheet As Worksheet
Dim StartingRow, i As Long
'Getting start date and end date values from worksheet
StartDate = Sheets("Macro").Range("J8").Value
EndDate = Sheets("Macro").Range("J9").Value
'Initializing the starting row number for output
StartingRow = 1
'Inserting new worksheet
Set NewWorksheet = Worksheets.Add
For i = StartDate To EndDate
'Using Weekday method to check whether it is Weekday or weekand
If Weekday(i, 2) < 6 Then
'Inserting value on newly inserted worksheet
'Format method is used for formatting the date value
NewWorksheet.Cells(StartingRow, 2) = Format(i, "dd.mm.yy")
NewWorksheet.Cells(StartingRow, 1) = Format(i, "ddd")
'Updating StartingRow variable value for moving to next row
StartingRow = StartingRow + 1
End If
'Inserting blank row for weekend
If Weekday(i, 2) = 7 Then
StartingRow = StartingRow + 1
End If
Next i
Set NewWorksheet = Nothing
End Sub
Если вам понравился этот блог, поделитесь им с друзьями на Facebook и Facebook.
Мы будем рады услышать от вас, дайте нам знать, как мы можем улучшить нашу работу и сделать ее лучше для вас. Напишите нам на [email protected]