Работа с умной таблицей на vba в excel

Работа с умной таблицей из кода VBA Excel. Обращение к ячейкам, строкам и столбцам умной таблицы. Добавление и удаление строк и столбцов.

Обращение к умной таблице

Все примеры кода в этой статье привязаны к таблице с именем «Таблица1», расположенной на активном листе:

Обращение к умной таблице:

ActiveSheet.ListObjects(«Таблица1»)

Обращение к диапазону умной таблицы на рабочем листе:

ActiveSheet.ListObjects(«Таблица1»).Range

Проверяем:

Debug.Print ActiveSheet.ListObjects(«Таблица1»).Range.Address  ‘Результат: $B$3:$G$9

Далее все примеры кода VBA Excel, чтобы их не дублировать, будут представлены как аргументы метода Debug.Print.

Обращение к строкам

Работа с умной таблицей — обращение к строке заголовков:

Debug.Print ActiveSheet.ListObjects(«Таблица1»).Range.Rows(1).Address  ‘Результат: $B$3:$G$3

Таким же образом можно обращаться и к остальным строкам таблицы (Строка1-Строка6), указывая индекс нужной строки от 2 до 7.

К записям таблицы (Строка1-Строка6) обращаются через коллекцию ListRows, указывая индекс записи от 1 до 6:

With ActiveSheet.ListObjects(«Таблица1»)

    Debug.Print .ListRows.Count  ‘Результат: 6

    Debug.Print .ListRows(1).Range.Address  ‘Результат: $B$4:$G$4

    Debug.Print .ListRows(2).Range.Address  ‘Результат: $B$5:$G$5

End With

Обращение к столбцам

Обращение к третьему столбцу умной таблицы из кода VBA Excel:

With ActiveSheet.ListObjects(«Таблица1»)

    ‘Обращение через диапазон умной таблицы

    Debug.Print .Range.Columns(3).Address  ‘Результат: $D$3:$D$9

    Debug.Print .Range.Columns.Count  ‘Результат: 6

    ‘Обращение через коллекцию ListColumns

    Debug.Print .ListColumns(3).Range.Address  ‘Результат: $D$3:$D$9

    Debug.Print .ListColumns.Count  ‘Результат: 6

End With

Обращение к ячейкам

Работа с умной таблицей — обращение к ячейке «E7» с отображением ее значения:

With ActiveSheet.ListObjects(«Таблица1»)

    Debug.Print .Range.Cells(5, 4)  ‘Результат: 91

    Debug.Print .ListColumns(4).Range(5)  ‘Результат: 91

    Debug.Print .ListRows(4).Range(4)  ‘Результат: 91

End With

Вставка и удаление строк

Вставка новой строки в конец умной таблицы:

ActiveSheet.ListObjects(«Таблица1»).ListRows.Add

Удаление последней строки:

With ActiveSheet.ListObjects(«Таблица1»)

    .ListRows(.ListRows.Count).Delete

End With

Вставка новой строки на место пятой записи (Строка5 в таблице) со сдвигом пятой и нижерасположенных записей вниз:

ActiveSheet.ListObjects(«Таблица1»).ListRows.Add 5

Удаление пятой строки:

ActiveSheet.ListObjects(«Таблица1»).ListRows(5).Delete

Вставка и удаление столбцов

Вставка нового столбца в конец умной таблицы из кода VBA Excel:

ActiveSheet.ListObjects(«Таблица1»).ListColumns.Add

Удаление последнего столбца:

With ActiveSheet.ListObjects(«Таблица1»)

    .ListColumns(.ListColumns.Count).Delete

End With

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

ActiveSheet.ListObjects(«Таблица1»).ListColumns.Add 4

Удаление четвертого столбца таблицы:

ActiveSheet.ListObjects(«Таблица1»).ListColumns(4).Delete

Создание и удаление умной таблицы описано в статье VBA Excel. Создание таблицы (умной, обычной)

Источник
 

KonstantinK

Пользователь

Сообщений: 53
Регистрация: 27.10.2015

#1

10.02.2019 16:43:02

Доброго времени суток!
Как обратиться к диапазону Умной таблицы. Написал код — он рабочий. Но на мой взгляд так писать не совсем верно.

Код 
Private Sub ComboBox1_Change()
Dim n As String
Dim i As Long
Dim lLastRow As Long
Dim rgResult As Range
    lLastRow = Cells(Rows.Count, 2).End(xlUp).Row - 1
    n = ComboBox1.Text
Set rgResult = Range("B7:B14").Find(n, , xlValues)

    For i = 7 To lLastRow Step 1
        If rgResult = n Then
            rgResult.Select
    End If
    Next

End Sub

Я имею ввиду диапазон Range(«B7:B14»).
Дело в том что  у нас есть объект Умная таблица «Таблица1»  внутри которой именованный диапазон/массив «Наименование», а мы обращаемся к колонке активного листа.
А если в колонке «В» над таблицей будут записи? …
Или колонка «В» не закончится на 14 строке?…. Можно, конечно написать Range(«B7:B»), но опять обращаемся к колонке листа.
А что если взять и переместить таблицу внутри листа со смещением? И такое может быть.

Справка VBA так и предлагает обращаться Range(«Test»)? в моём случае Range(«Наименование»), или Application.Goto «Test» — как бы не так??? Ни чего не выходит.
Объявлял объект, присваивал значения — ни чего и ни как?

Кто знает — подскажите.
Спасибо.
—

Прикрепленные файлы

  • Лист Microsoft Excel1.xlsm (43.35 КБ)

Кто хочет — ищет способы, кто не хочет — причины (Сократ)

 

Андрей VG

Пользователь

Сообщений: 11878
Регистрация: 22.12.2012

Excel 2016, 365

#2

10.02.2019 17:31:36

Доброе время суток.

Цитата
KonstantinK написал:
Дело в том что  у нас есть объект Умная таблица «Таблица1»  внутри которой именованный диапазон/массив «Наименование»,

А так не пробовали?

Код 
Workbooks("Лист Microsoft Excel1.xlsm").Worksheets("Накладная").ListObjects("Таблица1").ListColumns("Наименование").DataBodyRange.Address
 

KonstantinK

Пользователь

Сообщений: 53
Регистрация: 27.10.2015

#3

10.02.2019 17:48:02

Ругается: «Требуется объект»

Код 
Private Sub ComboBox1_Change()
Dim n As String
Dim i As Long
Dim lLastRow As Long
'Dim rgResult As Range
Dim rgResult As Object
    lLastRow = Cells(Rows.Count, 2).End(xlUp).Row - 1
    n = ComboBox1.Text
'Set rgResult = Range("B7:B14").Find(n, , xlValues)
Set rgResult = Workbooks("Лист Microsoft Excel1.xlsm").Worksheets("Накладная").ListObjects("Таблица1").ListColumns("Наименование").DataBodyRange.Address
    For i = 7 To lLastRow Step 1
        If rgResult = n Then
            rgResult.Select
    End If
    Next

End Sub

Изменено: KonstantinK — 10.02.2019 17:49:54

Кто хочет — ищет способы, кто не хочет — причины (Сократ)

 

Андрей VG

Пользователь

Сообщений: 11878
Регистрация: 22.12.2012

Excel 2016, 365

#4

10.02.2019 17:55:52

Цитата
KonstantinK написал: Ругается: «Требуется объект»

Это естественно, Address возвращает строку. Не стоит столь слепо копировать пример. Удалите .Address получите объект Range области данных столбца Наименование.
 

KonstantinK

Пользователь

Сообщений: 53
Регистрация: 27.10.2015

#6

10.02.2019 21:15:07

Родил. Кому будет интересно вот код (исключительно с Умной таблицей):

Код 
Private Sub ComboBox1_Change()
Dim myTable As ListObject
Dim x As Long
Dim n As String
Dim myArray As Variant
    n = ComboBox1.Text
    Set myTable = ActiveSheet.ListObjects("Таблица2")
    myArray = myTable.DataBodyRange
        For x = LBound(myArray) To UBound(myArray)
            If myArray(x, 2) = n Then
                myTable.DataBodyRange.Rows(x).Select
            End If
        Next x
End Sub

Забыл, есть нюанс:
Данный код будет обрабатывать первую строку — шапку и последнюю — итоги.
Здесь я ни чего не делал, чтобы убрать их, т.к. я их исключил в другом коде см. прикреплённый файл.

Изменено: KonstantinK — 10.02.2019 21:23:41

Кто хочет — ищет способы, кто не хочет — причины (Сократ)

 

Казанский

Пользователь

Сообщений: 8839
Регистрация: 11.01.2013

#7

10.02.2019 21:55:03

Цитата
KonstantinK написал:
Данный код будет обрабатывать первую строку — шапку и последнюю — итоги

Не будет. Сходите по ссылке, которую я дал — там на рисунках показано, что такое .Range, .DataBodyRange и другие части таблицы.
Источник

Работа с Умной таблицей на VBA в Excel. Урок 1

содержание видео

Урок 1. Работа с Умной таблицей на VBA
В данном уроке рассматриваем пример, как работать с умной таблицей кодом VBA. Учимся добавлять новые строки, столбцы. Работаем с выделением таблицы, форматированием и учимся включать дополнительные функции умной таблицы
Дата: 2022-01-11

Комментарии и отзывы: 10

Как задать имя умной таблицы не жестко. Дело в том, что лист будет копироваться. И умная таблица на скопированном листе будет иметь другое имя — автоматически добавляется числовой индекс в конце. Если сходная была Таблица, то на скопированном листе будет Таблица1 и т. д. Макрос должен удалять и добавлять строки как в вашем видео как на исходном листе так и на скопированных. Проблема — обращаться при добавлении к умн. таблице на активном листе.

Павел
Не понятно как переименовать умную таблицу!
На листе Платежи создал умную таблицу. Она сразу получила имя Таблица1
При попытке задать ей имя появился псевдоним ТабПлатежи, но Таблица1 тоже осталась.
В диспетчере имён видно. что
имени ТабПлатежи соответствует ДИАПАЗОН Таблица1
Пробую код:
Set ShPayment = ThisWorkbook. Worksheets(Платежи) — работает
Set ListObjPay = ShPayment. ListObjects(Таблица1) — работает
Set ListObjPay = ShPayment. ListObjects(ТабПлатежи) — НЕ РАБОТАЕТ!

Юлия
Спасибо за информативное и понятное видео!
Подскажите, а можно ли обратиться к столбцу не по его номеру, а по названию? Если да, то как это сделать?
Буду Вам очень благодарна, если поможете со следующей задачей: как прописать формулу в умной таблице не по индексам ячейки (А2В2, а по названиям столбцов?

Алексей
а не проще удалить вручную все и ничего не писать. До сих пор не могу понять зачем ВБА. Что в нем такого суперового? По мне так сложно все, писать на английском кучу строк что бы удалить строку. Обьясните мне тупому, зачем ВБА. Сколько не искал ответа нету(

Иван
Отличный урок.
Только 22: 58 маленькая неточность. Думаю строка должна иметь вид:
For i=1 to ListObj. ListColumns. Count
а то как-то не комильфо работать со столбцами по количеству строк)
и переменную і забыли объявить, если установлен Option Explicit VBA будет тоже ругаться.

ivan
Спасибо за Вашу работу, очень полезно, подскажите как сделать чтобы номер столбца который нужно добавить ListObj. ListColumns. Add 7 вставлялся автоматически например у меня лист товар, ячейка А15 = значение 6
ListObj. ListColumns. Add 7 (вместо 7 стало 6. спасибо

Алексей
Спасибо за урок. Очень жду продолжения.
Замечена одна неточность: при форматировании всех колонок считаешь количество строк, а форматируешь колонки. Насколько понимаю, правильным было бы записать ListObj. ListColumns. Count

сергей
В видео допущен ошибка на 22: 55 при установке ширины колонок. Количество столбцов нужно определять ListObj. ListColumns. Count, а не через свойство ListRows

Elnara
Здравствуйте! Почему у меня не получается? Выходит маленькое окошко и там написано: run-time error ‘9’: Subscript out of range. Что это означает не поняла?

Николай
Большое спасибо за урок! Очень ценно. А можно ли удалить сразу несколько смежных строк, указав диапазон? Или только по одной?

Источник

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

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

Visual Basic 
1
2
3
4
5
6
7
8
9
10
11

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Count = 1 Then    ' Изменения произошли в одной ячейке
        If Not Intersect(Target, ListObjects("Таблица1").DataBodyRange) Is Nothing Then        ' работаем с умной таблицей с именем Таблица1
            If Target.HasFormula Then
                Target.ClearContents
            End If
        End If
    End If
    Application.EnableEvents = True
End Sub

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

Можно было конечно пойти другим путем, но… много чего наделано)) переделывать времени нет

Источник

VBA Excel. Создание таблицы (умной, обычной)

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

Работа с «умной» таблицей

Создание «умной» таблицы

Создается «умная» таблица Excel с помощью следующего кода:

В данном примере:

ActiveSheet — лист, на котором создается таблица, может быть любой лист рабочей книги Excel.

Range(«$A$1:$L$15») — диапазон, который преобразуется в таблицу. Можно использовать и такую форму: Range(Cells(1, 1), Cells(15, 12)), где индексы строк и столбцов можно заменить переменными.

xlNo — указывает, что первая строка выбранного диапазона не содержит заголовки столбцов (граф) будущей таблицы, и их необходимо добавить. В этом случае будет добавлена дополнительная строка с наименованиями столбцов по умолчанию: Столбец1, Столбец2, Столбец3 и т.д., которые в дальнейшем можно переименовать (количество строк в таблице, включая строку заголовков, получится на одну больше, чем в указанном диапазоне). Если в диапазоне уже содержатся заголовки столбцов будущей таблицы, то следует указать вместо xlNo значение xlYes. В этом случае первая строка указанного диапазона будет преобразована в строку заголовков, а если она будет не заполнена, то добавятся названия столбцов по умолчанию: Столбец1, Столбец2, Столбец3 и т.д. (количество строк в таблице, включая строку заголовков, будет то же, что и в указанном диапазоне).

МояТаблица1 — имя, присваиваемое создаваемой таблице. Имя должно быть без пробелов: при указании в коде VBA названия таблицы с пробелами, во время его выполнения Excel заменит пробелы знаками подчеркивания (по крайней мере, так происходит в Excel 2016).

Таблица будет создана со стилем по умолчанию (TableStyleMedium2 в Excel 2016).

Стиль «умной» таблицы

Присвоение стиля таблице (изменение стиля) осуществляется с помощью свойства TableStyle объекта ListObjects:

Свойство TableStyle может принимать следующие значения:

  • TableStyleLight (светлый) с индексом от 1 до 21 (в Excel 2016);
  • TableStyleMedium (средний) с индексом от 1 до 28 (в Excel 2016);
  • TableStyleDark (темный) с индексом от 1 до 11 (в Excel 2016).

Например, TableStyleLight5, TableStyleMedium24, TableStyleDark8.

Чтобы отменить стиль таблицы в коде VBA, необходимо свойству TableStyle присвоить пустую строку:

Этому коду соответствует выбор в разделе «Конструктор» на ленте инструментов Excel самого первого значка стилей в разделе «Светлый».

Добавление строки итогов

Строка итогов «умной» таблицы добавляется следующим образом:

Удаляется строка итогов «умной» таблицы так:

Удаление «умной» таблицы

Удалить умную таблицу очень просто:

Создание пользовательской таблицы

Мне не приходилось на практике с помощью VBA Excel создавать «умные» таблицы, в отличие от пользовательских таблиц, которые использовались для улучшения восприятия различных отчетов или сгенерированных документов.

Для создания такой таблицы необходимо:

  • определить диапазон, если он заранее не известен (иногда для этого может понадобиться определить номер последней заполненной строки);
  • добавить границы ячеек;
  • отформатировать строку заголовков;
  • добавить строку итогов, если она необходима.

Подробнее о создании пользовательской таблицы в Примере 2.

Примеры создания таблиц

Задание для примеров

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

  • в табличной части 5 граф: № п/п, Наименование, Количество, Цена и Сумма;
  • сколько наименований добавил пользователь неизвестно.
  • определить количество строк;
  • добавить строку заголовков;
  • отобразить сетку (границы ячеек);
  • добавить строку итогов.

Таблицу будем оформлять двумя способами: путем создания «умной» и пользовательской таблиц.

Пример 1 — «умная» таблица

Упаковываем набор данных из задания в «умную» таблицу:

Результат выполнения кода Примера 1 получится такой:

«Умная» таблица из заданного набора данных

Пример 2 — «обычная» таблица

Упаковываем набор данных из задания в пользовательскую таблицу:

Результат выполнения кода Примера 2 получится такой:

Пользовательская таблица из заданного набора данных

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

VBA Excel. Создание таблицы (умной, обычной)

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

Работа с «умной» таблицей

Создание «умной» таблицы

Создается «умная» таблица Excel с помощью следующего кода:

В данном примере:

ActiveSheet — лист, на котором создается таблица, может быть любой лист рабочей книги Excel.

Range(«$A$1:$L$15») — диапазон, который преобразуется в таблицу. Можно использовать и такую форму: Range(Cells(1, 1), Cells(15, 12)), где индексы строк и столбцов можно заменить переменными.

xlNo — указывает, что первая строка выбранного диапазона не содержит заголовки столбцов (граф) будущей таблицы, и их необходимо добавить. В этом случае будет добавлена дополнительная строка с наименованиями столбцов по умолчанию: Столбец1, Столбец2, Столбец3 и т.д., которые в дальнейшем можно переименовать (количество строк в таблице, включая строку заголовков, получится на одну больше, чем в указанном диапазоне). Если в диапазоне уже содержатся заголовки столбцов будущей таблицы, то следует указать вместо xlNo значение xlYes. В этом случае первая строка указанного диапазона будет преобразована в строку заголовков, а если она будет не заполнена, то добавятся названия столбцов по умолчанию: Столбец1, Столбец2, Столбец3 и т.д. (количество строк в таблице, включая строку заголовков, будет то же, что и в указанном диапазоне).

МояТаблица1 — имя, присваиваемое создаваемой таблице. Имя должно быть без пробелов: при указании в коде VBA названия таблицы с пробелами, во время его выполнения Excel заменит пробелы знаками подчеркивания (по крайней мере, так происходит в Excel 2016).

Таблица будет создана со стилем по умолчанию (TableStyleMedium2 в Excel 2016).

Стиль «умной» таблицы

Присвоение стиля таблице (изменение стиля) осуществляется с помощью свойства TableStyle объекта ListObjects:

Свойство TableStyle может принимать следующие значения:

  • TableStyleLight (светлый) с индексом от 1 до 21 (в Excel 2016);
  • TableStyleMedium (средний) с индексом от 1 до 28 (в Excel 2016);
  • TableStyleDark (темный) с индексом от 1 до 11 (в Excel 2016).

Например, TableStyleLight5, TableStyleMedium24, TableStyleDark8.

Чтобы отменить стиль таблицы в коде VBA, необходимо свойству TableStyle присвоить пустую строку:

Этому коду соответствует выбор в разделе «Конструктор» на ленте инструментов Excel самого первого значка стилей в разделе «Светлый».

Добавление строки итогов

Строка итогов «умной» таблицы добавляется следующим образом:

Удаляется строка итогов «умной» таблицы так:

Удаление «умной» таблицы

Удалить умную таблицу очень просто:

Создание пользовательской таблицы

Мне не приходилось на практике с помощью VBA Excel создавать «умные» таблицы, в отличие от пользовательских таблиц, которые использовались для улучшения восприятия различных отчетов или сгенерированных документов.

Для создания такой таблицы необходимо:

  • определить диапазон, если он заранее не известен (иногда для этого может понадобиться определить номер последней заполненной строки);
  • добавить границы ячеек;
  • отформатировать строку заголовков;
  • добавить строку итогов, если она необходима.

Подробнее о создании пользовательской таблицы в Примере 2.

Примеры создания таблиц

Задание для примеров

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

  • в табличной части 5 граф: № п/п, Наименование, Количество, Цена и Сумма;
  • сколько наименований добавил пользователь неизвестно.
  • определить количество строк;
  • добавить строку заголовков;
  • отобразить сетку (границы ячеек);
  • добавить строку итогов.

Таблицу будем оформлять двумя способами: путем создания «умной» и пользовательской таблиц.

Пример 1 — «умная» таблица

Упаковываем набор данных из задания в «умную» таблицу:

Результат выполнения кода Примера 1 получится такой:

«Умная» таблица из заданного набора данных

Пример 2 — «обычная» таблица

Упаковываем набор данных из задания в пользовательскую таблицу:

Результат выполнения кода Примера 2 получится такой:

Пользовательская таблица из заданного набора данных

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

Программное создание формы для ввода и редактирования данных таблицы Excel

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

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

Как будет свободное время — постараюсь продолжить написание кода.
Если кто желает поучаствовать в проекте — присоединяйтесь )

  • 95326 просмотров

Комментарии

А как на 64 битной винде запустить?

В описании, первой строкой красным цветом написано:
«Сейчас реализована лишь малая часть программы.»

потому и не работает — раз программа не доделана
(раньше она мне была нужна — мне часто заказывали всякие формы ввода — я и начал делать подобную программу, а сейчас я эти формы почти не делаю, так что надобность в программе отпала)

Почему не работает то? ни чего не понимаю форму пустую отображает а создавать не создает

Все конечно хорошо но только вот почему то не реагирует на команду создать. и удалить считает попытки, в общем не работает хотя и безопасноть отключена и все такое. Кто подсскажет что к чему?

В этом случае не получится сделать программу универсальной.
Сколько каких элементов добавлять? Всех по 20 штук?
А если не хватит? К примеру, текстовых полей надо всего 5, а галочек (checkbox) — 30 штук.
Когда я начинал делать эту программу, я стремился прежде всего к универсальности, и максимальному удобству использования (чтобы пользователь нажал на кнопку, — и готовая форма ввода отобразилась на экране, причем чтобы пользователь мог передвинуть некоторые элементы, дописать код обработчиков, и т.п., и чтобы изменения сохранялись с файлом)

Форма ввода большой и не должна быть — я планировал элементы управления размещать на разных вкладках, по 5-10 элементов на вкладке MultiPage.

Увы, сейчас у меня слишком мало свободного времени, и я не знаю, когда у меня дойдут руки до завершения этой программы.

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

Форма данных

Данная функция является частью надстройки MulTEx

  • Описание, установка, удаление и обновление
  • Полный список команд и функций MulTEx
  • Часто задаваемые вопросы по MulTEx
  • Скачать MulTEx

Вызов команды:
MulTEx -группа Ячейки/Диапазоны —Диапазоны —Форма данных

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

ВВОД ДАННЫХ
На вкладке Ввод данных размещены элементы для основных операций с таблицей: навигация по записям, просмотр и изменений записей, копирование, добавление, изменение и удаление строк.

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

Работа со скрытыми ячейками ничем не отличается от работы с обычными ячейками.

Для поиска строк с конкретными значениями или по иным условиям необходимо воcпользоваться вкладкой Поиск.
Если внутри таблицы есть объединенные ячейки это не вызовет ошибки, но перемещение может происходить некорректно(в некоторых случаях объединенная ячейка будет считаться как одна строка).

Работа со строками
Значения каждой строки отображаются в полях. Левее каждого поля отображается заголовок таблицы, чтобы можно было точно понять к какому столбцу таблицы относится значение. Изменяя значения в полях можно изменить значения ячеек. Для этого необходимо изменить значения в нужных полях и нажать Изменить строку. Если в ячейке поля на листе создан выпадающий список значений — в форме он будет продублирован и можно будет выбрать значения из этого списка или ввести значение вручную.
Помимо этого есть и другие инструменты работы со строками и полями:

  • Отменить изменения — если изменения в полях необходимо отменить до записи в ячейки, то надо нажать кнопку Отменить изменения. Эта кнопка отменяет изменения только в полях формы и никак не влияет на значения ячеек таблицы. Сразу после изменения в ячейках эта кнопка становится недоступной.
  • Изменить строку — записывает в ячейки значения, заданные в полях. Записываются значения только измененных полей(если значение в поле отличается от первоначального значения в ячейке). Если изменить значение ячейки не удалось, то будет выдано сообщение:

    Поля, данные которых не удалось изменить, будут окрашены красным:

    Изменение поля будет отменено и в нем будет записано первоначальное значение.
  • Добавить строку — добавляет новую пустую строку либо после текущей строки, либо в конец таблицы. Это регулируется настройками на вкладке Настройки.
  • Удалить строку — удаляет текущую строку. Это действие невозможно отменить.
  • Дублировать строку:
      после нажатия кнопки Дублировать добавляет новую строку на основании текущей. Новая строка вставляется либо после текущей, либо в конец таблицы. Это регулируется настройками на вкладке Настройки
  • как есть — текущая строка копируется полностью, включая формулы, форматы и связи. Действие полностью совпадает с обычным копированием строки стандартными средствами.
  • только значения — текущая строка копируется, но вставляется только форматы и значения. Все формулы, которые есть в текущей строке заменяются результатом их вычислений.
  • только формулы — текущая строка копируется, но вставляется только форматы и формулы. Все ячейки, не содержащие формул очищаются.
  • значения и формулы — текущая строка копируется, но вставляется только значения и формулы. Форматы не переносятся.

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

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

    Формула отображается в точности так же, как она выглядит в ячейке(если в ячейке записана формула массива — сама формула будет отображена в фигурных скобках <=A1:A10*B1B10>, а в заголовке появится текст Формула массива ). В основном поле всегда отображается значение(результат вычисления формулы) и если изменить данные и нажать Изменить строку, то формула будет «затерта» новым значением.
    Примечание: Окно с формулой можно закрыть либо крестиком, либо нажатием кнопки Esc . Пока открыто любое окно с формулой ни Excel ни форма не доступны. Следовательно, чтобы продолжить работать с формой необходимо закрыть активное окно с формулой.

Индикатор наличия непечатных символов в ячейке

Тип данных

    Справа от каждого поля есть выпадающий список, с помощью которого можно выбрать тип данных в ячейке. Изменение типа данных не изменяет значения в ячейках, а влияет исключительно на отображение данных в полях формы. Однако, значения в этих полях могут влиять на типы данных при изменении строки кнопкой Изменить строку. Если в ячейке дата записана как текст и в списке выбран тип данных Дата и значение в поле будет изменено, то при нажатии кнопки Изменить строку форма попробует преобразовать данные в поле в дату и записать в ячейку уже не текст, а реальную дату. С числами и временем точно также(читать подробнее про то, как Excel видит данные). Но если в качестве типа данных будет выбрано значение Авто, то форма не будет пытаться изменять тип данных в ячейках.
    Примечание: Хоть по умолчанию программа сама пытается определить тип данных — в силу особенностей работы с датой и временем в Excel они могут определяться не корректно(например, может быть отображено число вместо даты или времени). Тогда следует назначить нужный тип данных принудительно. Так же, если в ячейке установлен процентный формат данных(или различные денежные и пользовательские форматы) — они не будут применены. Число, дата или время будут отображены так, как их видит Excel.

ПОИСК
На этой вкладке можно быстро найти нужные записи, задав условия поиска:

В выпадающих списках для каждого поля выбирается метод сравнения: Равно, Не равно, Содержит, Не содержит, Начинается с, Не начинается с, Заканчивается на, Не заканчивается на, Больше, Меньше, Больше или равно, Меньше или равно . В поле указывается значение, которое необходимо найти в ячейке этого столбца. Если метод не выбран, то поле игнорируются. Если выбран метод сравнения Содержит или Не содержит , то значение для поиска может содержать специальные символы подстановки: звёздочка(*) и вопросительный знак(?).
После нажатия кнопки Найти будут отобраны строки, отвечающие условиями поиска. Если хоть одна строка отвечает условиям поиска — будет активирована вкладка Ввод данных и выделена первая найденная запись. Перемещение ползунка полосы прокрутки будет выделять последовательно все найденные строки(только те, которые подходят под условия поиска). Общее количество найденных записей отображается внизу формы.

  • Сбросить поиск — отменяет перемещение только по найденным записям на вкладке Ввод данных. Условия поиска при этом остаются в полях, чтобы поиск можно было повторить или изменить.
  • Очистить условия — очищает из полей все условия поиска и полностью сбрасывает настройки поиска. Перемещение по строкам на вкладке Ввод данных происходит в обычном режиме.

    НАСТРОЙКИ

    Настройка диапазона таблицы

      Диапазон данных
      отвечает за определение границ таблицы, данные которой просматриваются через форму
    • Определять автоматически — если установить, программа сама попытается определить начало и окончание таблицы, которую просматривать. Удобно использовать, если таблицы построены правильно, начинаются с ячейки A1 и заголовок состоит только из одной строки.
    • Указать вручную — диапазон данных указывается вручную из любого доступного листа или книги. Может потребоваться, если на листе несколько таблиц и просматривать надо только конкретную.

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

    При добавлении строк

      настройка добавления строк на основной вкладке кнопкой Добавить
  • Вставлять новую строку после текущей — новая строка будет добавлена после строки, которая просматривается в данный момент
  • Вставлять новую строку в конец таблицы — независимо от того, какая строка проcматривается в текущий момент, новая пустая строка будет добавлена в конец таблицы.

    • При дублировании строк

      настройка добавления строк на основной вкладке кнопкой Дублировать
  • Вставлять новую строку после текущей — скопированная строка будет вставлена после строки, которая просматривается в данный момент
  • Вставлять новую строку в конец таблицы — независимо от того, какая строка проcматривается в текущий момент, скопированная строка будет вставлена в конец таблицы.

    • Не выделять строки таблицы при перемещении — по умолчанию при изменении строки просмотра просматриваемая строка выделяется на листе. Но если необходимо сравнить данные с другой таблицей(расположенной на другом листе или книге), то выделение постоянно будет «перекидывать» в ту таблицу, которая просматривается через форму. Чтобы можно было одновременно перемещаться по записям и просматривать другие листы, галочку с пункта необходимо снять.

    VBA-Урок 12.1. Пользовательские формы (UserForm)

    Чтобы добавить UserForm, нужно сделать то же самое, когда мы добавляем новый модуль:

    После этого появится форма (UserForm) и панель инструментов (Toolbox):

    Если вы не видите окно Свойства ( Properties ), убедитесь, что оно отображено и тогда начните с изменения названия Формы (так, чтобы вы ее легко могли найти позже):

    Вид ( UserForm ) имеет свои собственные события, подобно тому, как имеет рабочая книга или лист. Чтобы добавить событие, дважды щелкните по Форме (UserForm).

    Теперь давайте создадим два события, чтобы посмотреть как это работает. Первое событие будет определять начальный размер Формы , и второе — увеличивать ее размеры на 50 пикселей, когда пользователь щелкает.

    Событие UserForm_Initialize будет срабатывать когда Форма запускается:

    Для упрощения кода, мы можем использовать Me вместо названия формы (так как этот код есть в Форме , с которой мы работаем):

    Второе событие будет возникать когда пользователь будут кликать по Форме:

    Запуск формы (UserForm)

    Чтобы запустить Форму в процедуре, используйте Show:

    Источник

    Понравилась статья? Поделить с друзьями:

    А вот еще интересные статьи:

  • Работа с углами в excel
  • Работа с текстовым редактором word доклад
  • Работа с текстовым редактором word для начинающих
  • Работа с текстовым редактором word 2013
  • Работа с текстовым редактором ms word вставка объектов

    • 0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии