Як керувати даними в Excel за допомогою VBA
Макроси обробляють код VBA для керування великими наборами даних, які в іншому випадку потребують багато часу для модифікації. Наприклад, за допомогою VBA можна створити макрос для автоматичного форматування певних полів, що відповідають вашим визначеним критеріям.
Нижче наведено приклад сценарію VBA, який використовується в Excel:
Sub ConfigureLogic ()
Дим qstEntries
Дим
Дим qstCnt, dqstCnt
qstEntries = Діапазон ("QualifiedEntry")
qst = qstEntries - WorksheetFunction.CountIf (Діапазон ("QualifiedEntry"), "")
ReDim QualifiedEntryText (qst)
'MsgBox (qst)
dqstEntries = Діапазон ("DisQualifiedEntry")
dqst = dqstEntries - WorksheetFunction.CountIf (діапазон ("DisQualifiedEntry"), "")
ReDim DisqualifiedEntryText (dqst)
'MsgBox (dqst)
Для qstCnt = 1 до qst
QualifiedEntryText (qstCnt) = ThisWorkbook.Worksheets ("Кваліфікатори"). Діапазон ("J" & 8 + qstCnt) .value
'MsgBox (QualifiedEntryText (qstCnt))
ведення журналу ("Налаштування запису кваліфікованого запису #" & qstCnt & "як {" & QualifiedEntryText (qstCnt) & "}")
Далі
Для dqstCnt = 1 До dqst
DisqualifiedEntryText (dqstCnt) = ThisWorkbook.Worksheets ("Кваліфікатори"). Діапазон ("M" & 8 + dqstCnt) .value
'MsgBox (DisqualifiedEntryText (dqstCnt))
ведення журналу ("Налаштування запису" Дискваліфікований запис "" & qstCnt & "як {" & DisqualifiedEntryText (dqstCnt) & "}")
Далі
includeEntry = ThisWorkbook.Worksheets ("Кваліфікатори"). Діапазон ("IncludeSibling").
'MsgBox (includeEntry)
ведення журналу ("Entrys включений в пошук -" & includeEntry)
End Sub
Як аналізувати та керувати записами в електронній таблиці
Для того, щоб використовувати VBA для аналізу даних, потрібно перевірити налаштування в програмі Excel для інструмента розробника. Щоб знайти його, знайдіть стрічку Excel і знайдіть вкладку Розробник . Якщо вона не відображається, її потрібно активувати в меню налаштувань Excel.Далі створіть новий аркуш і назвіть його "Кваліфікатори". Ми використаємо цей аркуш, щоб перевірити всі речі, які відповідають вибору.
Далі налаштуйте класифікатори на аркуші відповідно до коду. Його потрібно ввести вручну; вирізати і вставити не буде працювати.
Діапазон ("J" & 8 + qstCnt) .value
Як знайти діапазон і побудувати масив
Діапазон у функції вище є осередком J9. Функція діапазону відзначає 8; проте фактичний діапазон становить 9, оскільки:Для qstCnt = 1 до qst
Вищенаведене твердження починається з 1, а не з 0. Отже, список починається з 9. У цьому випадку зверніть увагу (qstCnt = 1).
Щоб побудувати масив із записів на робочій таблиці Qualifiers, розмістіть випадкові слова в клітинах J9-J13. Після завершення рядків ми можемо рухатися вперед з пошуком та маніпулюванням даними в Excel.
Приватні таблиці лічильників ()Літній аркуш
Dim WS As Worksheet
лічильник = 0
ведення журналу ("***** Початок Scrub *********")
Для кожного WS в ThisWorkbook.Worksheets
лічильник = кількість листів + 1
Якщо WS.Name = "Вибрано", то
потрібно ввести дату й час у аркуш "Журнал"
ActionCnt = ActionCnt + 1
ведення журналу ("Лист виклику:" & WS.Name)
листи (кількість аркушів)
Ще
ActionCnt = ActionCnt + 1
ведення журналу ("Пропущено над листом:" & WS.Name)
End If
Далі WS
'MsgBox ("закінчення")
ActionCnt = ActionCnt + 1
реєстрації ("**** Scrub DONE!")
Application.ScreenUpdating = True
End Sub
Є приклад робочого лічильника вкладок.
Літній аркуш
Dim WS As Worksheet
лічильник = 0
ведення журналу ("***** Початок Scrub *********")
Для кожного WS в ThisWorkbook.Worksheets
лічильник = кількість листів + 1
Після ініціалізації підрахунку аркушів встановіть значення 0, щоб перезапустити лічильник.
Logging () - це інша підпрограма, яка відстежує всі дії для перевірки вибору.
Наступний цикл For встановлює активну книгу для підрахунку. WS є ініціалізованим і ThisWorkbook. Робочі листи є активною вкладкою у книзі. Оскільки ми не назвали книгу, цей модуль буде працювати на будь-якій активній книзі. Якщо ви працюєте над кількома робочими книгами та маєте неправильну активацію, вона спробує запустити на ній. Щоб уникнути помилок, вживайте запобіжні заходи, щоб назвати вашу конкретну книгу або працювати лише по одній за раз.
Кожен раз, коли цикл запускається, він додає одну змінну до лічильника, щоб відстежувати кількість вкладок. Потім ми переходимо до:
Якщо WS.Name = "Вибрано", топотрібно ввести дату й час у аркуш "Журнал"
ActionCnt = ActionCnt + 1
ведення журналу ("Лист виклику:" & WS.Name)
листи (кількість аркушів)
Ще
ActionCnt = ActionCnt + 1
ведення журналу ("Пропущено над листом:" & WS.Name)
End If
Тут ми шукаємо вкладку "Вибрані".
Якщо змінна WS дорівнює вибраному, то ми реєструємо його і запускаємо підпрограму Scrub Sheet. Якщо змінна WS не дорівнює вибраному, то реєструється, що цей аркуш був пропущений, і дія підраховується. Наведений вище код є прикладом підрахунку кількості та пошуку певної вкладки.
Нижче наведено список різних методів, які можна використовувати для маніпулювання даними!
ЗАБУВАЄТЬСЯ!
Як підрахувати кількість аркушів у робочій книжці
Dim TABДля кожного TAB в ThisWorkbook.Worksheets
- Деякі рутини тут
далі
Знайдіть останній рядок, стовпець або клітинку на аркуші
Кількість комірокcellcount = Cells (ThisWorkbook.Worksheets ("робочий лист"). Rows.Count, 1) .End (xlUp) .Row
Фільтруйте за допомогою розширених критеріїв
Діапазон ("A2: Z99") Клавіша сортування1: = Діапазон ("A5"), порядок 1: = xlAscending, Заголовок: = xlNo Застосувати властивість "Автоматична підстановка" до стовпця
Стовпці ("A: A"). EntireColumn.AutoFit Отримання значень з іншого аркуша
тьмяне новезначенняnewvalue = ThisWorkbook.Worksheets ("робочий аркуш"). Діапазон ("F1").
Вставлення стовпця в аркуші
Рядок, стовпецьКлітини (рядок, стовпець) .EntireColumn.Select
Встановіть
Вставте кілька стовпців у лист
Дим вставитиCntРядок, стовпець
Для insertCnt = 1 до N
ThisWorkbook.Worksheets ("робочий лист"). Виберіть
Клітини (рядок, стовпець) .EntireColumn.Select
Встановіть
Далі
Додавання іменного діапазону до окремого аркуша
Name.Add Ім'я: = "Статус", RefersToR1C1: = "= робочий аркуш! C2"
Вставте весь рядок у аркуш
Рядок, стовпецьКлітинки (рядок, стовпець) .EntireRow.Select
Встановіть
Скопіюйте весь рядок для вставки
ActiveSheet.Range ("A1")Виділення
Видалити весь рядок
ActiveSheet.Range ("A1")Виділення
Виберіть окремий аркуш
ThisWorkbook.Worksheets ("робочий лист"). Виберіть Порівняйте значення діапазону
Dim firstrangeDim Logictest
Logictest = "якесь слово або значення"
Якщо (Діапазон (firstrange) .value = Logictest) тоді
- Деякі рутини тут
End If
Зображення: © Microsoft.