Remkomplekty.ru

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

Vba excel find

Поиск на листе Excel

Поиск какого-либо значения в ячейках Excel довольно часто встречающаяся задача при программировании какого-либо макроса. Решить ее можно разными способами. Однако, в разных ситуациях использование того или иного способа может быть не оправданным. В данной статье я рассмотрю 2 наиболее распространенных способа.

Поиск перебором значений

Довольно простой в реализации способ. Например, найти в колонке «A» ячейку, содержащую «123» можно примерно так:

Минусами этого так сказать «классического» способа являются: медленная работа и громоздкость. А плюсом является его гибкость, т.к. таким способом можно реализовать сколь угодно сложные варианты поиска с различными вычислениями и т.п.

Поиск функцией Find

Гораздо быстрее обычного перебора и при этом довольно гибкий. В простейшем случае, чтобы найти в колонке A ячейку, содержащую «123» достаточно такого кода:

Вкратце опишу что делают строчки данного кода:
1-я строка: Выбираем в книге лист «Данные»;
2-я строка: Осуществляем поиск значения «123» в колонке «A», результат поиска будет в fcell;
3-я строка: Если удалось найти значение, то fcell будет содержать Range-объект, в противном случае — будет пустой, т.е. Nothing.

Полностью синтаксис оператора поиска выглядит так:

Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

What — Строка с текстом, который ищем или любой другой тип данных Excel

After — Ячейка, после которой начать поиск. Обратите внимание, что это должна быть именно единичная ячейка, а не диапазон. Поиск начинается после этой ячейки, а не с нее. Поиск в этой ячейке произойдет только когда весь диапазон будет просмотрен и поиск начнется с начала диапазона и до этой ячейки включительно.

LookIn — Тип искомых данных. Может принимать одно из значений: xlFormulas (формулы), xlValues (значения), или xlNotes (примечания).

LookAt — Одно из значений: xlWhole (полное совпадение) или xlPart (частичное совпадение).

SearchOrder — Одно из значений: xlByRows (просматривать по строкам) или xlByColumns (просматривать по столбцам)

SearchDirection — Одно из значений: xlNext (поиск вперед) или xlPrevious (поиск назад)

MatchCase — Одно из значений: True (поиск чувствительный к регистру) или False (поиск без учета регистра)

MatchByte — Применяется при использовании мультибайтных кодировок: True (найденный мультибайтный символ должен соответствовать только мультибайтному символу) или False (найденный мультибайтный символ может соответствовать однобайтному символу)

SearchFormat — Используется вместе с FindFormat. Сначала задается значение FindFormat (например, для поиска ячеек с курсивным шрифтом так: Application.FindFormat.Font.Italic = True), а потом при использовании метода Find указываем параметр SearchFormat = True. Если при поиске не нужно учитывать формат ячеек, то нужно указать SearchFormat = False.

Чтобы продолжить поиск, можно использовать FindNext (искать «далее») или FindPrevious (искать «назад»).

Примеры поиска функцией Find

Пример 1: Найти в диапазоне «A1:A50» все ячейки с текстом «asd» и поменять их все на «qwe»

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

Пример 2: Правильный поиск значения с использованием FindNext, не приводящий к зацикливанию.

В ниже следующем примере используется другой вариант продолжения поиска — с помощью той же функции Find с параметром After. Когда найдена очередная ячейка, следующий поиск будет осуществляться уже после нее. Однако, как и с FindNext, когда будет достигнут конец диапазона, Find продолжит поиск с его начала, поэтому, чтобы не произошло зацикливания, необходимо проверять совпадение с первым результатом поиска.

Пример 3: Продолжение поиска с использованием Find с параметром After.

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

Пример 4: Найти все ячейки с шрифтом «курсив» и поменять их формат на обычный (не «курсив»)

Примечание: В данном примере намеренно не используется FindNext для поиска следующей ячейки, т.к. он не учитывает формат (статья об этом: https://support.microsoft.com/ru-ru/kb/282151)

Коротко опишу алгоритм поиска Примера 4. Первые две строки определяют последнюю строку (lLastRow) на листе и последний столбец (lLastCol). 3-я строка задает формат поиска, в данном случае, будем искать ячейки с шрифтом Italic. 4-я строка определяет область ячеек с которой будет работать программа (с ячейки A1 и до последней строки и последнего столбца). 5-я строка осуществляет поиск с использованием SearchFormat. 6-я строка — цикл пока результат поиска не будет пустым. 7-я строка — меняем шрифт на обычный (не курсив), 8-я строка продолжаем поиск после найденной ячейки.

Хочу обратить внимание на то, что в этом примере я не стал использовать «защиту от зацикливания», как в Примерах 2 и 3, т.к. шрифт меняется и после «прохождения» по всем ячейкам, больше не останется ни одной ячейки с курсивом.

Свойство FindFormat можно задавать разными способами, например, так:

Следующий пример — применение функции Find для поиска последней ячейки с заполненными данными. Использованные в Примере 4 SpecialCells находит последнюю ячейку даже если она не содержит ничего, но отформатирована или в ней раньше были данные, но были удалены.

Пример 5: Найти последнюю колонку и столбец, заполненные данными

В этом примере используется UsedRange, который так же как и SpecialCells возвращает все используемые ячейки, в т.ч. и те, что были использованы ранее, а сейчас пустые. Функция Find ищет ячейку с любым значением с конца диапазона.

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

Пример 6: Выделить красным шрифтом ячейки, в которых текст начинается со слова из 4-х букв, первая и последняя буквы «т», при этом после этого слова может следовать любой текст.

Для поиска функцией Find по маске (шаблону) можно применять символы:
* — для обозначения любого количества любых символов;
? — для обозначения одного любого символа;

— для обозначения символов *, ? и

. (т.е. чтобы искать в тексте вопросительный знак, нужно написать

?, чтобы искать именно звездочку (*), нужно написать

* и наконец, чтобы найти в тексте тильду, необходимо написать

Поиск даты с помощью Find

Если необходимо найти текущую дату или какую-то другую дату на листе Excel или в диапазоне с помощью Find, необходимо учитывать несколько нюансов:

  • Тип данных Date в VBA представляется в виде #[месяц]/[день]/[год]#, соответственно, если необходимо найти фиксированную дату, например, 01 марта 2018 года, необходимо искать #3/1/2018#, а не «01.03.2018»
  • В зависимости от формата ячеек, дата может выглядеть по-разному, поэтому, чтобы искать дату независимо от формата, поиск нужно делать не в значениях, а в формулах, т.е. использовать LookIn:=xlFormulas

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

Пример 7: Найти текущую дату на листе независимо от формата отображения даты.

Пример 8: Найти 1 марта 2018 г.

Искать часть даты — сложнее. Например, чтобы найти все ячейки, где месяц «март», недостаточно искать «03» или «3». Не работает с датами так же и поиск по шаблону. Единственный вариант, который я нашел — это выбрать формат в котором месяц прописью для ячеек с датами и искать слово «март» в xlValues.

Тем не менее, можно найти, например, 1 марта независимо от года.

Пример 9: Найти 1 марта любого года.

Vba excel find

Many times as a developer you might need to find a match to a particular value in a range or sheet, and this is often done using a loop. However, VBA provides a much more efficient way of accomplishing this using the Find method. In this article we’ll have a look at how to use the Range.Find Method in your VBA code. Here is the syntax of the method:

expression.Find (What , After , LookIn , LookAt , SearchOrder , SearchDirection , MatchCase , MatchByte , SearchFormat )

What: The data to search for. It can be a string or any of the Microsoft Excel data types (the only required parameter, rest are all optional)

After: The cell after which you want the search to begin. It is a single cell which is excluded from search. Default value is the upper-left corner of the range specified.

Читать еще:  Vba excel оператор like

LookIn: Look in formulas, values or notes using constants xlFormulas, xlValues, or xlNotes respectively.

LookAt: Look at a whole value of a cell or part of it (xlWhole or xlPart)

SearchOrder: Search can be by rows or columns (xlByRows or xlByColumns)

SearchDirection: Direction of search (xlNext, xlPrevious)

MatchCase: Case sensitive or the default insensitive (True or False)

MatchByte: Used only for double byte languages (True or False)

SearchFormat: Search by format (True or False)

All these parameters correspond to the find dialog box options in Excel.

Return Value: A Range object that represents the first cell where the data was found. (Nothing if match is not found).

Let us look at some examples on how to implement this. In all the examples below, we will be using the table below to perform the search on.

Example 1: A basic search

In this example, we will look for a value in the name column.

The rest of the parameters are optional. If you don’t use them then Find will use the existing settings. We’ll see more about this shortly.

The output of this code will be the first occurrence of the search string in the specified range.

If the search item is not found then Find returns an object set to Nothing. And an error will be thrown if you try to perform any operation on this (on foundRng in the above example)

So, it is always advisable to check whether the value is found before performing any further operations.

Let us know have a look at the optional parameters. To keep it simple, we will exclude the above error handling in the subsequent examples.

Example 2: Using after

The highlighted cell will be searched for

Example 3: Using LookIn

Before seeing an example, here are few things to note with LookIn

  1. Text is considered as a value as well as a formula
  2. xlNotes is same as xlComments
  3. Once you set the value of LookIn all subsequent searches will use this setting of LookIn (using VBA or Excel)

Example 4: Using LookAt

LookAt setting too is preserved for subsequent searches.

Example 5: Using SearchOrder

SearchOrder setting is preserved for subsequent searches.

Example 6: Using SearchDirection

Example 7: Using MatchCase

Example 8: Using SearchFormat

When you search for a format it is important to note that the format settings stick until you change them. So, before you use SearchFormat, it is a good practice to always clear any previous formats that have been set.

Example 9: FindNext and FindPrevious

In all the previous examples, we have been looking for just the first occurrence of the search criteria. If you want to find multiple occurrences, we use the FindNext and FindPrevious methods. Both of these need a reference to the last cell found, so that the search continues after that cell. If this argument is dropped, it will keep returning the first occurrence.

If you are looking to find or list all files and directories in a folder / sub-folder, you can refer to the articles below:

VBA FIND

Excel VBA Find

When we use Find in a normal worksheet we press keyboard shortcut CTRL + F and type the data we need to find and if not desired value we go to the next match, if there is a lot of such matches it is a tedious task but when we use FIND in VBA it does the tasks for us and give us the exact match and it takes three arguments, one is what to find, where to find and where to look at.

Before we move to VBA and start using find function in macros we need to learn first what is a find function in excel. In normal excel in the Home tab under the editing group, we can find a find function which is used to find a string or a value in a cell range or whole worksheet.

When we click on this, we get two options;

One is simple to find,

We can see it has also a mode of options which opens up another feature.

It does the find algorithm with four constraints, Find What, Within, Search and look in.

The second option in excel is to Find and replace which is used when we find a string but what to replace it with any other value,

Find Function Syntax

We have learned above what is Find in basic excel. In VBA we write codes manually but the features are the same as normal excel. First, let us look at the syntax.

If the value we are looking is found with the excel function it returns the cell where the value is and if the value is not found then the object of the function is set to nothing.

Expressions in macros are ranges defined such as range 1 or range 2. What is a keyword for what we want to search a specific value? Lookin is a keyword for what we are trying to search is it a comment or a formula or a string. Similarly, there are other constraints in Find function which are optional. The only mandatory field required is what is a value we are trying to search.

Basically, VBA find Excel has one required argument which is What which value we want to search. The rest of the constraints are optional and there are many constraints in find function. Find function is similar to what a find function is in excel.

The parameter for find function is the range of cells. Like in which range we want to find a value. It can be a few columns or few cells or whole worksheet.

Examples

Example #1

Suppose our data has the following values

We will try to find “Aran” in the same data.

  • To write a VBA code it is necessary to have enabled the developer tab in order to be able to write VBA Codes.

  • We start writing our code by writing the following code as shown below,

  • The sample is the function name given to sub.
  • Find is the string we want the user we want to enter to search.
  • Rng is the variable we took for the range.
  • Now we ask the user to enter the value which looks like the screenshot below,

  • Now we will define our find function in the module.

  • The function finds the value entered by the user in the given range.
  • Now we close the function by the following arguments.

  • Now if we run our code first it asks for a prompt by the user for a value.

  • Once the code is completed it returns the cell to where the data was found.

Example #2

In the above example, there were four unique names but what if there were more than one names in the data, such as consider the below data,

We can see that the name Aran is repeated twice in the above data. If excel has to find the name Aran it will find it in cell A2 and stop, but there is another value similar to that of A2 in cell A6. How to fetch that value? Here comes the syntax of Find(What, After) in help.

After defines cell after which reference we want to search the data.

Let us write the code for the above data.

  • Always remember to enable developer tab from options and then from customizing ribbons to be able to write the code in VBA.
  • In VBA we get Microsoft excel objects which is a module where we write the codes.
Читать еще:  Подсчет символов в тексте excel

  • Previously we were working on sheet 1 now we are working in sheet 2 so select sheet 2 for another module and a blank page appears.

  • Now start writing the code by defining the function first as SUB Sample2() and press enter.

  • Now we have defined our function we will start getting into the main part which is defining our variables.

  • Define what does the Find variable should have,

  • Select the sheets which we are working on which is sheet 2 in this example,

  • Now we will find the text whatever the user enters after A2 cell, so we define our find function as below,

  • Now we close the code by ending the with and if conditions.

What the above code does is search the string after the cell A2 and returns the cell wherever it is found.

Things to Remember

  1. First things first we need to enable the developer tab in order to use VBA.
  2. What is the specific value we need to find?
  3. If the value is not found, the object of the function is set to nothing.

Recommended Articles

This has been a complete guide to VBA Find Function . Here we learn how to use Excel VBA find function with practical examples and downloadable excel sheet. You may also have a look at other articles related to Excel VBA –

Search and Find Using VBA

The Find function is one of the most common functions used in VBA. The method allows users to locate the first occurrence of a piece of information within a range. You can use this method to search in a sheet, or part of a Microsoft Excel spreadsheet. This tutorial will explain how to find a value in an Excel column using the Find function in VBA.

Search Data Using Find

The Find function works very similarly to a basic search function. If you’re looking to perform a simple search of all data in a workbook, you can do so by simply pressing the [CTRL] + F keys on your keyboard. This will open up a search box. Simply type in the keyword or value you’re searching for and hit Enter.

Excel will highlight all of the cells that correspond to your search.

Search Data Using the Find Method in VBA

Here is how the Find function would look in VBA. Note that the search term used for this example is Value:

Understanding the Find Method

Here is a quick breakdown of all of the parameters of the Find method.

MyRange: This expression represents the Range object, which designates in which cells you’d like to search for the value contained in What parameter.

This can be your full sheet (Sheets(1).Cells.Find(. )), a column (Sheets(1).Columns(3).Find(. )), a row (Sheets(1).Rows(7).Find(. )), or a range of cells (Sheets(1).Range(«D12:F56»).Find(. )).

What: This parameter is required. This parameter allows you to stipulate which value you want to find in your range. It can be of any data type supported by Excel.

After: This parameter is optional. This parameter indicates the starting cell for the search (note that the cell must be unique). If After is not specified, the search will begin in the upper left corner of the range.

LookIn: This parameter is optional. The parameter is used to locate a value in a range. Other variations of LookIn include xlValues, xlFormulas, and xlComments.

LookAt: This parameter is optional. LookAt indicates whether or not the value must be exactly equal to the value sought, or partially equal.

For example, if one were to search for the value «10» in a matrix including: 6210, 4105, 540, 163, 154, 132, 10, there are various methods you can use. To tell VBA that you only want the number 10, you can use the parameter LookAt:=XlWhole. In contrast, to indicate you are searching for values that contain 10 (in this case, 6210 or 4105), you can use LookAt:=XlPart.

SearchOrder: This parameter is optional. The search order is subject to two constants: xlByRows (for rows), or xlByColumns (for columns).

SearchDirection: This parameter is optional. This parameter indicates the direction of search in a particular range. The two constants are xlNext, which is used to search a subsequent value in the range, and xlPrevious, which is used to search a value stated previously.

MatchCase: This parameter is optional. The two values for this argument are True and False. Case-sensitive searches should be set to True.

SearchFormat: This parameter is optional. The parameter can be True or False depending on whether or not a format is assigned (e.g. monetary standard, number, border, fill, alignment, etc.).

Returned Value of Find Method

Examples of Find Method in VBA

N.B. If we had made a search for the 1024 number instead of the word «Find», we should have been declared Valeur_Cherchee as an integer. The What variant allows you to search for any type of data.

Multiple Searches in VBA

Using the Find_Next Variant

In the example coded here we will look for the word «mot» in the A1:A20 range:

N.B. For an entire column, simply replace:

N.B. To return the addresses of the cells rather than the line number in the Find_Next function, replace:

FindAll

This custom function returns Find and FindNext results as an array of values. It will find all the instances of a string (sText As String) and returns an array containing the line numbers.

The parameters of this function are as follows:

ByVal sText As String represents the target value.

ByRef oSht As Worksheet represents the target sheet.

ByRef sRange As String represents the range.

ByRef arMatches() As String represents the array that will store the returned values.

Методы объекта 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, т. к. будут отображаться либо туры в Афины, либо в Берлин .

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

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

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