Remkomplekty.ru

IT Новости из мира ПК
5 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Autofill vba excel

Методы объекта Range, использующие команды Excel

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

Метод DataSeries (прогрессия) создает профессии. Вручную метод DataSeries выполняется с помощью команды Правка, Заполнить, Прогрессия (Edit, Fill, Series).

Объект.DataSeries(rowcol, type, date, step, stop, trend)

Диапазон с начальными данными прогрессии. Метод DataSeries позволяет одновременно строить несколько однотипных профессий с одинаковым шагом, но различными начальными элементами

Задает, вводятся профессии по строкам или столбцам. Допустимые значения:

Определяет тип прогрессии. Допустимые значения:

Определяет тип последовательности дат, если параметр type принимает значение xlChronological. Допустимые значения:

Шаг изменения прогрессии. По умолчанию 1

Предельное значение прогрессии. По умолчанию строится прогрессия во всем выделенном диапазоне

Допустимые значения: True (создается арифметическая или геометрическая прогрессия) или False (создается список)

Приведем соответствие между аргументами метода DataSeries и построением последовательности на рабочем листе командой Правка, Заполнить, Прогрессия (Edit, Fill, Series) на примере построения геометрической профессии.

О В ячейку AI вводим первый член прогрессии, например 1. В методе DataSeries за начальное значение прогрессии отвечает объект, к которому применяется метод. В данном случае метод DataSeries надо применить к диапазону Range («A1») . О Выберите команду Правка, Заполнить, Прогрессия (Edit, Fill, Series), которая приведет к появлению диалогового окна Прогрессия (Series) (рис. 3.1).

В диалоговом окне Прогрессия (Series) в группе Расположение (Series in) выберите, например, переключатель по строкам (Rows), т. к. будем строить геометрическую профессию в первой строке. В группе Тип (Туре) выберите переключатель геометрическая (Growth) В поле Шаг введите например, 1.2, а в поле Предельное значение (Stop value) — 3, т. е. геометрическая прогрессия будет строиться с шагом 1.2 до тех пор, пока ее члены не достигнут значения 3. Нажатие кнопки ОК приводит к построению требуемой профессии (рис. 3.2). В методе DataSeries за расположение профессии отвечает аргумент rowcoi. В данном случае ему надо присвоить значение xiRows. За тип прогрессии отвечает аргумент type, которому присвоим значение xiGrowth. За шаг и предельное значение отвечают аргументы step и stop, которым присвоим 1.2 и з соответственно. Таким образом, имеем:

Range ( «А1» ). DataSeries Rowcol : =xlRows , Type : =xlGrowth, Step:=1.2, Stop:=3

Рис. З.2. Результат построения геометрической прогрессии

Метод AutoFill (автозаполнение) автоматически заполняет ячейки диапазона элементами последовательности. Метод AutoFill отличается от метода DataSeries тем, что явно указывается диапазон, в котором будет располагаться прогрессия. Вручную этот метод эквивалентен расположению указателя мыши на маркере заполнения выделенного диапазона (в который введены значения, порождающие создаваемую последовательность) и протаскивании маркера заполнения вдоль диапазона, в котором будет располагаться создаваемая последовательность.

Диапазон, с которого начинается заполнение

Диапазон, который заполняется

Допустимые значения: xiFiilDefauit, xlFillSeries, xlFillCopy, xlFillFormats, xlFillValues, xlFillDays, xlFillWeekdays, xlFillMonths, xlFillYears, xlLinearTrend, xlGrowthTrend. По умолчанию xlFillDefault

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

В ячейку AI введите первый член профессии, например 5. В ячейку А2 введите второй член профессии, например 7. Выделите диапазон А1:А2, содержащий два первых члена арифметической профессии. Расположите указатель мыши над маркером заполнения выделенного диапазона так, чтобы он превратился в черный крест (рис. 3.3).

Рис. 3.3. Выделение двух первых членов прогрессии

При нажатой левой кнопке мыши, протащите маркер заполнения вниз по столбцу так, чтобы создать требуемую последовательность. В данном случае протащим маркер заполнения так, чтобы создать последовательность в диапазоне А1:А5 (рис. 3.4). Тот же результат получается, если аргументу Destination метода AutoFill присваивается Range («Ai:A5»), аргументу туре присваивается xiFiiiDefauit, а метод применяется к диапазону Range («A1:A2») . Таким образом, имеем:

Range ( «Al :A2») .AutoFill

Destination: =Range ( «Al : A5» ),_

Type : =xlFillDef ault

Метод AutoFilter (автофильтр) представляет собой простой способ запроса и фильтрации данных на рабочем листе. Если AutoFilter активизирован, то каждый заголовок поля выделенного диапазона данных превращается в поле с раскрывающимся списком. Выбирая запрос на вывод данных в поле с раскрывающимся списком, вы осуществляете вывод только тех записей, которые удовлетворяют указанным условиям. Поле с раскрывающимся списком содержит следующие типы условий: Все (АИ), Первые десять (Тор 10), Условие (Custom), конкретный элемент данных, Пустые (Blanks) и Непустые (NohBlanks). Вручную метод запускается посредством выбора команды Данные, Фильтр, Автофильтр (Data, Filter, AutoFilter).

При применении метода AutoFilter допустимы два синтаксиса.

В этом случае метод AutoFilter выбирает или отменяет команду Данные, Фильтр, Автофильтр (Data, Filter, AutoFilter), примененную к диапазону, заданному в аргументе объект.

Объект.AutoFilter(field, criterial, operator, criteria2)

В этом случае метод AutoFilter выполняет команду Данные, Фильтр, Автофильтр (Data, Filter, AutoFilter) по критериям, указанным в аргументе.

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

criterial И criteria2

Задают два возможных условия фильтрации поля. Допускается использование строковой постоянной, например 101, и знаков отношений >, =,

Допустимые значения: П xiAnd (логическое объединение первого и второго критериев) П xior (логическое сложение первого и второго критериев) П xiTopioitems (для показа первых десяти элементов поля)

При работе с фильтрами полезны метод ShowAllData и свойства FilterMode и AutoFilterMode.

Показывает все отфильтрованные и неотфильтрованные строки рабочего листа

Допустимые значения: True (если на рабочем листе имеются отфильтрованные данные со скрытыми строками), False (в противном случае)

Допустимые значения: True (если на рабочем листе выведены раскрывающиеся списки метода AutoFilter), False (в противном случае)

Приведем соответствие между аргументами метода AutoFilter и выполнением команды Данные, Фильтр, Автофильтр (Data, Filter, AutoFilter) при фильтрации базы данных регистрации туристов.

Выделяем диапазон A1 = E1, содержащий заголовки полей базы данных. Выберем команду Данные, Фильтр, Автофильтр (Data, Filter, AutoFilter). В результате в заголовках полей появятся раскрывающиеся списки (рис. 3.5). В этих раскрывающихся списках предлагаются варианты допустимой фильтрации. В методе AutoFilter за диапазон с названиями полей отвечает объект, к которому применяется метод. В данном случае метод

AutoFilter надо Применить к диапазону Range («A1: E1») .

Рис. 3.5. Раскрывающиеся списки метода AutoFilter

Отфильтруем в базе данных, например, только данные о клиентах, направляющихся в афины (рис. 3.6). С этой целью в раскрывающемся списке поля Направление тура выберем Афины. В результате на рабочем листе будут выведены только записи, соответствующие турам в Афины. В методе AutoFilter за выбор поля, в котором производится фильтрация, отвечает аргумент

Field. В данном Случае для выбора поля Направление тура

аргументу Field надо присвоить значение 4. За критерии, покоторым производится фильтрация, отвечают аргументы criteria1 и criteria2. В данном случае фильтрация производилась по одному критерию — АФИНЫ, поэтому только аргументу criterial надо присвоить значение АФИНЫ. Таким образом, имеем:

Range ( «Al : El » ) . Select Selection. AutoFilte r Selection. AutoFilter Field:=4, Criteria1 : =»Афины»

Рис. 3.6. Фильтрация списка по критерию Афины

Рис. 3.7. Диалоговое окно Пользовательский автофильтр

При фильтрации по условию появляется диалоговое окно Пользовательский автофильтр (Custom AutoFilter), позволяющее отфильтровать записи по двум критериям в одном поле (рис. 3.7). Например, отфильтруем все туры в Афины и Берлин. В методе AutoFilter это соответствует присвоению аргументам Сriterial и criteria2 значений Афины и Берлин соответственно, а аргументу operator — значения хlor, т. к. будут отображаться либо туры в Афины, либо в Берлин .

Читать еще:  Vba excel выход из цикла

Таким образом, имеем:

Range («A1:E1») .Select Select ion. AutoFilter Selection. AutoFilter Field:=4,

Criterial : =»=Афины» , Operator : =xlOr ,

Метод AdvancedFilter (расширенный фильтр) является более мощным и универсальным средством фильтрации, чем метод AutoFilter. Он позволяет использовать фильтрацию по большему числу критериев, причем допустимо применение критериев, включающих формулы. Кроме того, метод AdvancedFilter позволяет фильтровать список с выводом результата фильтрации как непосредственно на том месте, где он расположен, так и в новое специфицированное место. Вручную метод запускается посредством выбора команды Данные, Фильтр, Расширенный фильтр (Data, Filter, Advanced Filter).

Объект.AdvancedFilter(Action, CriteriaRange, CopyToRange, Unique)

Допустимые значения:: xiFilterinPiace (фильтровать список на месте) и xiFiiterCopy (скопировать результат на новое место)

Ссылка на диапазон с критериями

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

Допустимые значения True (отбирается только один вариант записи из многократно встречающихся в списке) и False (отбираются все встречающиеся записи)

Приведем соответствие между аргументами метода Advanced Filter и выполнением команды Данные, Фильтр, Расширенный фильтр (Data, Filter, Advanced Filter) при фильтрации базы данных регистрации туристов.

Выделяем диапазон AI : G13, содержащий фильтруемую базу данных. (рис. 3.8). Прежде чем выбирать команду Данные, Фильтр, Расширенный фильтр (Data, Filter, Advanced Filter), необходимовыполнить предварительные построения по созданию диапазона критериев. Верхняя строка диапазона критериев должна содержать заголовки полей фильтруемых данных. При этом нет необходимости включать все заголовки и сохранять их порядок. В диапазон критериев также должны входить строки с условиями фильтрации. Все условия в диапазоне критериев, записанные под заголовком поля, относятся к этому полю. При применении расширенного фильтра допустима запись нескольких условий в строке диапазона критериев. Условия, расположенные в одной строке, рассматриваются как условия, объединенные логической операцией и (And) , а расположенные в нескольких — логической операцией или (Or) В данном случае под диапазон критериев отведем диапазон A16:G17. В базе данных выберем записи обо всех мужчинах, которые едут в Лондон. С этой целью в ячейку С17 диапазона критериев введем значение муж, а в ячейку D17— Лондон.

Выберем команду Данные, Фильтр, Расширенный фильтр (Data, Filter, Advanced Filter). Появится диалоговое окно Расширенный фильтр (Advanced Filter) (рис. 3.9).

Range ( «A1 : G13» ) .AdvancedFiiter

CriteriaRange : =Range («A16:G17» ) ,

CopyToRange : =Range ( » Al 9 : Gl 9 » ) , Unique : =False

VBA AutoFill

What Does AutoFill Do in Excel VBA?

We have seen autofill in worksheet where the cells are automatically filled based on the values in the previous cells above it, we can use VBA so that excel does the task for us, to do this we use the Selection.Autofill method and we provide the destination i.e. up to which cells the values needs to be filled.

The best use of VBA autofill comes when we need to fill the formula of the first cell to the cell of the column. We usually apply the formula in the first cell either we copy & paste to the last cell or we just auto fill by double click on the little arrow key. Another best example of using autofill in excel is when we need to insert serial numbers. We usually type the first three numbers then we drag down till the required last cell.

In VBA too we can perform the task of AutoFill method. In this article, we will show you how we can use the autofill method and ways to write the code. Now we will see how we can use this tool in VBA coding.

How to Use AutoFill in VBA?

To use the autofill in VBA we need to understand the syntax of the auto fill method. Below is the syntax of the auto fill.

  • Range (“A1”): What are the cells to identify the patter of the fill series.
  • Destination: Till what cell you want to continue the fill series pattern. Here we need to mention the full range of cells.
  • Type as xlAutoFillType: Here we can select the series fill type. Below are the list of items in this parameter – xlFillCopy, xlFillDays, xlFillDefault, xlFillFormats, xlFillMonths, xlFillSeries, xlFillValues, xlFillWeekdays, xlFillYears, xlFlashFill, xlGrowthTrend, xlLinearTrend.

Examples of AutoFill in Excel VBA

Let’s see some simple to advanced examples of VBA AutoFill in excel.

Example #1 – xlFillDefault

First, enter 3 serial numbers in the first three cells.

In the VBA sub procedure mention the VBA range as Range (“A1: A3”)

Code:

Now access the AutoFill method.

Enter the destination as Range (“A1: A10”)

Code:

Select the Type as xlFillDefault.

Code:

Now run the code we will get the serial numbers from 1 to 10.

Since we mentioned the end destination cell as A10 it has stopped there, we can enter the destination cell as the last cell of the excel.

Example #2 – xlFillCopy

For the same numbers, we will use the type as xlFillCopy.

Code:

I have the copy of the first three cells to the remaining cells.

Example #3 – xlFillMonths

For this example, I have entered the first three months in the first 3 cells.

Change the autofill type to xlFillMonths.

Code:

This will fill the month series.

Example #4 – xlFillFormats

For this example, I have entered numbers and applied formatting to those cells.

Now I will change the type to xlFillFormats.

Code:

Run this code and see what happens.

It has filled formats of the first three cells to the next three cells and again next three cells and so on.

Example #5 – xlFlashFill

For this example, I have entered a few values from cell A1 to A10 as shown in the below image.

From this list, I want to extract the numerical part. To tell excel about the pattern, in the first cell I will manually enter the numerical part of the first cell.

Now I will write the code as usual and change the type to xlFlashFill. This time we will use the B column range.

Code:

If I run this code we will get the result like the below.

This is the overview of the VBA AutoFill method. Hope you have enjoyed it.

You can download this VBA AutoFill Excel Template from here – VBA AutoFill Excel Template

Recommended Articles

This has been a guide to VBA AutoFill in excel. Here we discuss How to Use Autofill in Excel VBA with various parameters like xlFillDefault, xlFillFormats, xlFlashFill, etc. You can learn more about VBA from following articles –

Методы объекта Range, использующие команды Excel

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

Читать еще:  Как сделать ячейку активной в excel

Метод DataSeries (прогрессия) создает профессии. Вручную метод DataSeries выполняется с помощью команды Правка, Заполнить, Прогрессия (Edit, Fill, Series).

Объект.DataSeries(rowcol, type, date, step, stop, trend)

Диапазон с начальными данными прогрессии. Метод DataSeries позволяет одновременно строить несколько однотипных профессий с одинаковым шагом, но различными начальными элементами

Задает, вводятся профессии по строкам или столбцам. Допустимые значения:

Определяет тип прогрессии. Допустимые значения:

Определяет тип последовательности дат, если параметр type принимает значение xlChronological. Допустимые значения:

Шаг изменения прогрессии. По умолчанию 1

Предельное значение прогрессии. По умолчанию строится прогрессия во всем выделенном диапазоне

Допустимые значения: True (создается арифметическая или геометрическая прогрессия) или False (создается список)

Приведем соответствие между аргументами метода DataSeries и построением последовательности на рабочем листе командой Правка, Заполнить, Прогрессия (Edit, Fill, Series) на примере построения геометрической профессии.

О В ячейку AI вводим первый член прогрессии, например 1. В методе DataSeries за начальное значение прогрессии отвечает объект, к которому применяется метод. В данном случае метод DataSeries надо применить к диапазону Range («A1») . О Выберите команду Правка, Заполнить, Прогрессия (Edit, Fill, Series), которая приведет к появлению диалогового окна Прогрессия (Series) (рис. 3.1).

В диалоговом окне Прогрессия (Series) в группе Расположение (Series in) выберите, например, переключатель по строкам (Rows), т. к. будем строить геометрическую профессию в первой строке. В группе Тип (Туре) выберите переключатель геометрическая (Growth) В поле Шаг введите например, 1.2, а в поле Предельное значение (Stop value) — 3, т. е. геометрическая прогрессия будет строиться с шагом 1.2 до тех пор, пока ее члены не достигнут значения 3. Нажатие кнопки ОК приводит к построению требуемой профессии (рис. 3.2). В методе DataSeries за расположение профессии отвечает аргумент rowcoi. В данном случае ему надо присвоить значение xiRows. За тип прогрессии отвечает аргумент type, которому присвоим значение xiGrowth. За шаг и предельное значение отвечают аргументы step и stop, которым присвоим 1.2 и з соответственно. Таким образом, имеем:

Range ( «А1» ). DataSeries Rowcol : =xlRows , Type : =xlGrowth, Step:=1.2, Stop:=3

Рис. З.2. Результат построения геометрической прогрессии

Метод AutoFill (автозаполнение) автоматически заполняет ячейки диапазона элементами последовательности. Метод AutoFill отличается от метода DataSeries тем, что явно указывается диапазон, в котором будет располагаться прогрессия. Вручную этот метод эквивалентен расположению указателя мыши на маркере заполнения выделенного диапазона (в который введены значения, порождающие создаваемую последовательность) и протаскивании маркера заполнения вдоль диапазона, в котором будет располагаться создаваемая последовательность.

Диапазон, с которого начинается заполнение

Диапазон, который заполняется

Допустимые значения: xiFiilDefauit, xlFillSeries, xlFillCopy, xlFillFormats, xlFillValues, xlFillDays, xlFillWeekdays, xlFillMonths, xlFillYears, xlLinearTrend, xlGrowthTrend. По умолчанию xlFillDefault

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

В ячейку AI введите первый член профессии, например 5. В ячейку А2 введите второй член профессии, например 7. Выделите диапазон А1:А2, содержащий два первых члена арифметической профессии. Расположите указатель мыши над маркером заполнения выделенного диапазона так, чтобы он превратился в черный крест (рис. 3.3).

Рис. 3.3. Выделение двух первых членов прогрессии

При нажатой левой кнопке мыши, протащите маркер заполнения вниз по столбцу так, чтобы создать требуемую последовательность. В данном случае протащим маркер заполнения так, чтобы создать последовательность в диапазоне А1:А5 (рис. 3.4). Тот же результат получается, если аргументу Destination метода AutoFill присваивается Range («Ai:A5»), аргументу туре присваивается xiFiiiDefauit, а метод применяется к диапазону Range («A1:A2») . Таким образом, имеем:

Range ( «Al :A2») .AutoFill

Destination: =Range ( «Al : A5» ),_

Type : =xlFillDef ault

Метод AutoFilter (автофильтр) представляет собой простой способ запроса и фильтрации данных на рабочем листе. Если AutoFilter активизирован, то каждый заголовок поля выделенного диапазона данных превращается в поле с раскрывающимся списком. Выбирая запрос на вывод данных в поле с раскрывающимся списком, вы осуществляете вывод только тех записей, которые удовлетворяют указанным условиям. Поле с раскрывающимся списком содержит следующие типы условий: Все (АИ), Первые десять (Тор 10), Условие (Custom), конкретный элемент данных, Пустые (Blanks) и Непустые (NohBlanks). Вручную метод запускается посредством выбора команды Данные, Фильтр, Автофильтр (Data, Filter, AutoFilter).

При применении метода AutoFilter допустимы два синтаксиса.

В этом случае метод AutoFilter выбирает или отменяет команду Данные, Фильтр, Автофильтр (Data, Filter, AutoFilter), примененную к диапазону, заданному в аргументе объект.

Объект.AutoFilter(field, criterial, operator, criteria2)

В этом случае метод AutoFilter выполняет команду Данные, Фильтр, Автофильтр (Data, Filter, AutoFilter) по критериям, указанным в аргументе.

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

criterial И criteria2

Задают два возможных условия фильтрации поля. Допускается использование строковой постоянной, например 101, и знаков отношений >, =,

Допустимые значения: П xiAnd (логическое объединение первого и второго критериев) П xior (логическое сложение первого и второго критериев) П xiTopioitems (для показа первых десяти элементов поля)

При работе с фильтрами полезны метод ShowAllData и свойства FilterMode и AutoFilterMode.

Показывает все отфильтрованные и неотфильтрованные строки рабочего листа

Допустимые значения: True (если на рабочем листе имеются отфильтрованные данные со скрытыми строками), False (в противном случае)

Допустимые значения: True (если на рабочем листе выведены раскрывающиеся списки метода AutoFilter), False (в противном случае)

Приведем соответствие между аргументами метода AutoFilter и выполнением команды Данные, Фильтр, Автофильтр (Data, Filter, AutoFilter) при фильтрации базы данных регистрации туристов.

Выделяем диапазон A1 = E1, содержащий заголовки полей базы данных. Выберем команду Данные, Фильтр, Автофильтр (Data, Filter, AutoFilter). В результате в заголовках полей появятся раскрывающиеся списки (рис. 3.5). В этих раскрывающихся списках предлагаются варианты допустимой фильтрации. В методе AutoFilter за диапазон с названиями полей отвечает объект, к которому применяется метод. В данном случае метод

AutoFilter надо Применить к диапазону Range («A1: E1») .

Рис. 3.5. Раскрывающиеся списки метода AutoFilter

Отфильтруем в базе данных, например, только данные о клиентах, направляющихся в афины (рис. 3.6). С этой целью в раскрывающемся списке поля Направление тура выберем Афины. В результате на рабочем листе будут выведены только записи, соответствующие турам в Афины. В методе AutoFilter за выбор поля, в котором производится фильтрация, отвечает аргумент

Field. В данном Случае для выбора поля Направление тура

аргументу Field надо присвоить значение 4. За критерии, покоторым производится фильтрация, отвечают аргументы criteria1 и criteria2. В данном случае фильтрация производилась по одному критерию — АФИНЫ, поэтому только аргументу criterial надо присвоить значение АФИНЫ. Таким образом, имеем:

Range ( «Al : El » ) . Select Selection. AutoFilte r Selection. AutoFilter Field:=4, Criteria1 : =»Афины»

Рис. 3.6. Фильтрация списка по критерию Афины

Рис. 3.7. Диалоговое окно Пользовательский автофильтр

При фильтрации по условию появляется диалоговое окно Пользовательский автофильтр (Custom AutoFilter), позволяющее отфильтровать записи по двум критериям в одном поле (рис. 3.7). Например, отфильтруем все туры в Афины и Берлин. В методе AutoFilter это соответствует присвоению аргументам Сriterial и criteria2 значений Афины и Берлин соответственно, а аргументу operator — значения хlor, т. к. будут отображаться либо туры в Афины, либо в Берлин .

Таким образом, имеем:

Читать еще:  Vba excel диапазон ячеек

Range («A1:E1») .Select Select ion. AutoFilter Selection. AutoFilter Field:=4,

Criterial : =»=Афины» , Operator : =xlOr ,

Метод AdvancedFilter (расширенный фильтр) является более мощным и универсальным средством фильтрации, чем метод AutoFilter. Он позволяет использовать фильтрацию по большему числу критериев, причем допустимо применение критериев, включающих формулы. Кроме того, метод AdvancedFilter позволяет фильтровать список с выводом результата фильтрации как непосредственно на том месте, где он расположен, так и в новое специфицированное место. Вручную метод запускается посредством выбора команды Данные, Фильтр, Расширенный фильтр (Data, Filter, Advanced Filter).

Объект.AdvancedFilter(Action, CriteriaRange, CopyToRange, Unique)

Допустимые значения:: xiFilterinPiace (фильтровать список на месте) и xiFiiterCopy (скопировать результат на новое место)

Ссылка на диапазон с критериями

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

Допустимые значения True (отбирается только один вариант записи из многократно встречающихся в списке) и False (отбираются все встречающиеся записи)

Приведем соответствие между аргументами метода Advanced Filter и выполнением команды Данные, Фильтр, Расширенный фильтр (Data, Filter, Advanced Filter) при фильтрации базы данных регистрации туристов.

Выделяем диапазон AI : G13, содержащий фильтруемую базу данных. (рис. 3.8). Прежде чем выбирать команду Данные, Фильтр, Расширенный фильтр (Data, Filter, Advanced Filter), необходимовыполнить предварительные построения по созданию диапазона критериев. Верхняя строка диапазона критериев должна содержать заголовки полей фильтруемых данных. При этом нет необходимости включать все заголовки и сохранять их порядок. В диапазон критериев также должны входить строки с условиями фильтрации. Все условия в диапазоне критериев, записанные под заголовком поля, относятся к этому полю. При применении расширенного фильтра допустима запись нескольких условий в строке диапазона критериев. Условия, расположенные в одной строке, рассматриваются как условия, объединенные логической операцией и (And) , а расположенные в нескольких — логической операцией или (Or) В данном случае под диапазон критериев отведем диапазон A16:G17. В базе данных выберем записи обо всех мужчинах, которые едут в Лондон. С этой целью в ячейку С17 диапазона критериев введем значение муж, а в ячейку D17— Лондон.

Выберем команду Данные, Фильтр, Расширенный фильтр (Data, Filter, Advanced Filter). Появится диалоговое окно Расширенный фильтр (Advanced Filter) (рис. 3.9).

Range ( «A1 : G13» ) .AdvancedFiiter

CriteriaRange : =Range («A16:G17» ) ,

CopyToRange : =Range ( » Al 9 : Gl 9 » ) , Unique : =False

Excel VBA: автозаполнение нескольких ячеек с формулами

У меня есть большой объем данных, которые я собрал из разных файлов. В этой основной книге у меня разные типы формул для каждой ячейки. В диапазоне от A до F находятся данные из других файлов. В диапазоне от H до AC у меня есть формула, которую я автоматически заполняю, перетаскивая ее вручную каждый раз, когда вводятся новые данные. Код ниже — это то, что я использовал, и у него есть только 6 разных формул, которые я хочу автозаполнять.

Однако в основной книге есть 15 различных формул, которые я хочу, чтобы она автоматически заполнялась каждый раз, когда вводились новые данные. У меня есть несколько основных книг, и формула не является постоянной. Вставка кода выше для каждой формулы — боль. Есть ли способ, который может заставить программу автоматически перетащить ее? В основной книге у меня уже есть формулы. Я пробовал много разных кодов, чтобы сделать его автозаполнением, но пока что один выше, который работает без ошибок. Я попытался использовать что-то вроде этой или аналогичной версии для этого, но никто не работает:

Я так много перепутал с кодом. Я даже не знаю, предположительно ли это так. Спасибо, что помогли!

Подход, который вы ищете, FillDown . Другой способ, чтобы вам не приходилось ударять головой каждый раз, — это хранить формулы в массиве строк. Объединение их дает вам мощный метод ввода формул множеством. Код следует:

Скриншоты:

Результат по строке: .Range(«C2:E2»).Formula = strFormulas :

Результат по строке: .Range(«C2:E11»).FillDown :

Конечно, вы можете сделать его динамическим, сохранив последнюю строку в переменной и превратив ее в нечто вроде .Range(«C2:E» & LRow).FillDown , похожее на то, что вы сделали.

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

Основываясь на моем комментарии, вот один из способов получить то, что вы хотите сделать:

Начните байт, выбирая любую ячейку в вашем диапазоне и нажмите Ctrl + T

Это даст вам всплывающее окно:

убедитесь, что текст вашей таблицы верен и нажмите «ОК», теперь у вас будет:

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

Теперь, если вы введете формулу в этот столбец:

После ввода формулы формула будет автоматически заполнена до последней строки:

Теперь, если вы добавили новую строку в следующую строку под своей таблицей:

После ввода его размер будет изменен до ширины вашей таблицы, а также будут добавлены все столбцы с формулами:

Autofill Application.Countifs.Formula VBA Excel

Я думаю, что это может быть простой вопрос, но я не могу найти способ автозавершить формулу Application.WorksheetFunction.SumProduct(wsf.CountIf, чтобы номер строки изменился по мере уменьшения формулы.

Я хочу автозаполнять эту формулу для B2 до LastRow из колонки A. Application.WorksheetFunction.SumProduct(wsf.CountIfs(Sheet2.Range(**B2 Here**), Doctors, Sheet2.Range(**B2 Here**), wsf.Transpose(Emergency)))

Поэтому давайте предположим, что последняя используемая строка для столбца A равна 6, поэтому формула должна автозаполняться от B2 до B6, например:

B3 = Application.WorksheetFunction.SumProduct(wsf.CountIfs(Sheet2.Range(**B3 Here**).

B4 = Application.WorksheetFunction.SumProduct(wsf.CountIfs(Sheet2.Range(**B4 Here**).

B5 = Application.WorksheetFunction.SumProduct(wsf.CountIfs(Sheet2.Range(**B5 Here**).

B6 = Application.WorksheetFunction.SumProduct(wsf.CountIfs(Sheet2.Range(**B6 Here**).

Я не хочу использовать подход.Formula «= SUMPRODUCT (COUNTIFS…..», чтобы я мог использовать массивы, поскольку критерии варьируются в соответствии с выбранными вариантами выпадающего списка.

AutoFill может сделать это за вас. Предполагая, что ваша формула начинается в B2 и изменяется конечная строка, синтаксис будет выглядеть следующим образом:

Другим вариантом будет использование FillDown , как AutoFill почти всегда имеют странности. Синтаксис почти такой же:

Сообщите нам, если это поможет.

ОБНОВИТЬ:

Очень похоже на Дэйва, здесь я беру на себя формулу:

ОДНАКО, здесь clincher: Если вы просто выполняете проверку COUNTIFS если Column C имеет Doctors как значение, а в Column I — значение B2 , вы должны удалить SUMPRODUCT из своей формулы, поскольку это необязательно. SUMPRODUCT когда-то был способ проверки нескольких условий, но в Excel 2007 и выше он был полностью заменен COUNTIFS для простого множественного проверки на основе условий. То, что я говорю, правильный код в значительной степени следующий:

STILL, дальнейшая формула для судебной экспертизы и оценки показывает более сложную часть: вы вводите свою формулу в B2 и ниже, но ваши COUNTIFS проверяют B2 и ниже как часть этого условия. Это не только запутывает, но также подвержено ошибкам (если они еще не ошибочны), так как это приведет к циклической ссылке.

На данный момент мой вопрос заключается в том, что именно вы пытаетесь проверить и/или подсчитать? На мой взгляд, это больше, чем просто вопрос VBA, поскольку я считаю, что вы смущены тем, что и как вы хотите получить ваши цифры. Хотя наш подход является обоснованным и в основном идеален для вашего вопроса, если формула неверна, это бесполезно.

Вот как мои данные изложены:

Этот код входит в стандартный модуль:

Этот код идет в листовом модуле комбинированного блока (Sheet1 в этом случае).

Я не уверен в ваших результатах формулы, но загружается правильно.

Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector
×
×