Макрос выборки данных из таблицы. Запрос на выборку данных (формулы) в MS EXCEL
Разное (39)
Баги и глюки Excel (3)
Как получить список уникальных(не повторяющихся) значений?
Представим себе большой список различных наименований, ФИО, табельных номеров и т.п. А необходимо из этого списка оставить список все тех же наименований, но чтобы они не повторялись - т.е. удалить из этого списка все дублирующие записи. Как это иначе называют: создать список уникальных элементов, список неповторяющихся, без дубликатов. Для этого существует несколько способов: встроенными средствами Excel, встроенными формулами и, наконец, при помощи кода Visual Basic for Application(VBA) и сводных таблиц. В этой статье рассмотрим каждый из вариантов.
При помощи встроенных возможностей Excel 2007 и выше
В Excel 2007 и 2010 это сделать проще простого - есть специальная команда, которая так и называется - . Расположена она на вкладке Данные (Data)
подраздел Работа с данными (Data tools)
Как использовать данную команду. Выделяете столбец(или несколько) с теми данными, в которых надо удалить дублирующие записи. Идете на вкладку Данные (Data) -Удалить дубликаты (Remove Duplicates) .
Если выделить один столбец, но рядом с ним будут еще столбцы с данными(или хотя бы один столбец), то Excel предложит выбрать: расширить диапазон выборки этим столбцом или оставить выделение как есть и удалить данные только в выделенном диапазоне. Важно помнить, что если не расширить диапазон, то данные будут изменены лишь в одном столбце, а данные в прилегающем столбце останутся без малейших изменений.
Появится окно с параметрами удаления дубликатов
Ставите галочки напротив тех столбцов, дубликаты в которых надо удалить и жмете Ок. Если в выделенном диапазоне так же расположены заголовки данных, то лучше поставить флаг Мои данные содержат заголовки , чтобы случайно не удалить данные в таблице(если они вдруг полностью совпадают со значением в заголовке) .
Способ 1: Расширенный фильтр
В случае с Excel 2003 все посложнее. Там нет такого инструмента, как Удалить дубликаты
. Но зато есть такой замечательный инструмент, как Расширенный фильтр
. В 2003 этот инструмент можно найти в Данные
-Фильтр
-Расширенный фильтр
. Прелесть этого метода в том, с его помощью можно не портить исходные данные, а создать список в другом диапазоне. В 2007-2010 Excel, он тоже есть, но немного запрятан. Расположен на вкладке Данные (Data)
, группа Сортировка и фильтр (Sort & Filter)
- Дополнительно (Advanced)
Как его использовать: запускаем указанный инструмент - появляется диалоговое окно:
- Обработка: Выбираем Скопировать результат в другое место (Copy to another location) .
- Исходный диапазон (List range) : Выбираем диапазон с данными(в нашем случае это А1:А51) .
- Диапазон критериев (Criteria range) : в данном случае оставляем пустым.
- Поместить результат в диапазон (Copy to) : указываем первую ячейку для вывода данных - любую пустую(на картинке - E2) .
- Ставим галочку Только уникальные записи (Unique records only) .
- Жмем Ок .
Примечание: если вы хотите поместить результат на другой лист, то просто так указать другой лист не получится. Вы сможете указать ячейку на другом листе, но...Увы и ах...Excel выдаст сообщение, что не может скопировать данные на другие листы. Но и это можно обойти, причем довольно просто. Надо всего лишь запустить Расширенный фильтр с того листа, на который хотим поместить результат. А в качестве исходных данных выбираем данные с любого листа - это дозволено.
Так же можно не выносить результат в другие ячейки, а отфильтровать данные на месте. Данные от этого никак не пострадают - это будет обычная фильтрация данных.
Для этого надо просто в пункте Обработка выбрать Фильтровать список на месте (Filter the list, in-place) .
Способ 2: Формулы
Этот способ сложнее в понимании для неопытных пользователей, но зато он создает список уникальных значений, не изменяя при этом исходные данные. Ну и он более динамичен: если изменить данные в исходной таблице, то изменится и результат. Иногда это бывает полезно. Попытаюсь объяснить на пальцах что и к чему: допустим, список с данными у Вас расположен в столбце А
(А1:А51
, где А1
- заголовок). Выводить список мы будем в столбец С
, начиная с ячейки С2
. Формула в C2
будет следующая:
{=ИНДЕКС($A$2:$A$51 ;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C$1:C1 ; $A$2:$A$51)=0;СТРОКА($A$1:$A$50));1))}
{=INDEX($A$2:$A$51 ;SMALL(IF(COUNTIF($C$1:C1 ; $A$2:$A$51)=0;ROW($A$1:$A$50));1))}
Детальный разбор работы данной формулы приведен в статье:
Надо отметить, что эта формула является формулой массива . Об этом могут сказать фигурные скобки, в которые заключена данная формула. А вводится такая формула в ячейку сочетанием клавиш - Ctrl
+Shift
+Enter
. После того, как мы ввели эту формулу в C2
мы её должны скопировать и вставить в несколько строк так, чтобы точно отобразить все уникальные элементы. Как только формула в нижних ячейках вернет #ЧИСЛО!
- это значит все элементы отображены и ниже протягивать формулу нет смысла. Чтобы ошибку избежать и сделать формулу более универсальной(не протягивая каждый раз до появления ошибки) можно использовать нехитрую проверку:
для Excel 2007 и выше:
{=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$51 ;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C$1:C1 ; $A$2:$A$51)=0;СТРОКА($A$1:$A$50));1));"")}
{=IFERROR(INDEX($A$2:$A$51 ;SMALL(IF(COUNTIF($C$1:C1 ; $A$2:$A$51)=0;ROW($A$1:$A$50));1));"")}
для Excel 2003:
{=ЕСЛИ(ЕОШ(НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C$1:C1 ; $A$2:$A$51)=0;СТРОКА($A$1:$A$50));1));"";ИНДЕКС($A$2:$A$51 ;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C$1:C1 ; $A$2:$A$51)=0;СТРОКА($A$1:$A$50));1)))}
{=IF(ISERR(SMALL(IF(COUNTIF($C$1:C1 ; $A$2:$A$51)=0;ROW($A$1:$A$50));1));"";INDEX($A$2:$A$51 ;SMALL(IF(COUNTIF($C$1:C1 ; $A$2:$A$51)=0;ROW($A$1:$A$50));1)))}
Тогда вместо ошибки #ЧИСЛО! (#NUM!)
у вас будут пустые ячейки(не совсем пустые, конечно - с формулами:-))
.
Чуть подробнее про отличия и нюансы формул ЕСЛИОШИБКА и ЕСЛИ(ЕОШ можно прочесть в этой статье: Как в ячейке с формулой вместо ошибки показать 0
Способ 3: код VBA
Данный подход потребует разрешения макросов и базовых знаний о работе с ними. Если не уверены в своих знаниях для начала рекомендую прочитать эти статьи:
- Что такое макрос и где его искать? к статье приложен видеоурок
- Что такое модуль? Какие бывают модули? потребуется, чтобы понять куда вставлять приведенные ниже коды
Оба приведенных ниже кода следует помещать в стандартный модуль . Макросы должны быть разрешены.
Исходные данные оставим в том же порядке - список с данными расположен в столбце "А "(А1:А51 , где А1 - заголовок) . Только выводить список мы будем не в столбец С , а в столбец Е , начиная с ячейки Е2 :
Sub Extract_Unique() Dim vItem, avArr, li As Long ReDim avArr(1 To Rows.Count, 1 To 1) With New Collection On Error Resume Next For Each vItem In Range("A2", Cells(Rows.Count, 1).End(xlUp)).Value "Cells(Rows.Count, 1).End(xlUp) – определяет последнюю заполненную ячейку в столбце А.Add vItem, CStr(vItem) If Err = 0 Then li = li + 1: avArr(li, 1) = vItem Else: Err.Clear End If Next End With If li Then .Resize(li).Value = avArr End Sub
С помощью данного кода можно извлечь уникальные не только из одного столбца, но и из любого диапазона столбцов и строк. Если вместо строки
Range("A2", Cells(Rows.Count, 1).End(xlUp)).Value
указать Selection.Value , то результатом работы кода будет список уникальных элементов из выделенного на активном листе диапазона. Только тогда неплохо бы и ячейку вывода значений изменить - вместо
поставить ту, в которой данных нет.
Так же можно указать конкретный диапазон:
Range("C2", Cells(Rows.Count, 3).End(xlUp)).Value
Универсальный код выбора уникальных значений
Код ниже можно применять для любых диапазонов. Достаточно запустить его, указать диапазон со значениями для отбора только неповторяющихся(допускается выделение более одного столбца) и ячейку для вывода результата. Указанные ячейки будут просмотрены, из них будут отобраны только уникальные значения(пустые ячейки при этом пропускаются) и результирующий список будет записан, начиная с указанной ячейки.
| Sub Extract_Unique() Dim x, avArr, li As Long Dim avVals Dim rVals As Range, rResultCell As Range On Error Resume Next "запрашиваем адрес ячеек для выбора уникальных значений Set rVals = Application.InputBox("Укажите диапазон ячеек для выборки уникальных значений" , "Запрос данных" , "A2:A51" , Type :=8) If rVals Is Nothing Then "если нажата кнопка Отмена Exit Sub End If "если указана только одна ячейка - нет смысла выбирать If rVals.Count = 1 Then MsgBox "Для отбора уникальных значений требуется указать более одной ячейки" , vbInformation, "www.сайт" Exit Sub End If "отсекаем пустые строки и столбцы вне рабочего диапазона Set rVals = Intersect(rVals, rVals.Parent.UsedRange) "если указаны только пустые ячейки вне рабочего диапазона If rVals Is Nothing Then MsgBox "Недостаточно данных для выбора значений" , vbInformation, "www.сайт" Exit Sub End If avVals = rVals.Value "запрашиваем ячейку для вывода результата Set rResultCell = Application.InputBox("Укажите ячейку для вставки отобранных уникальных значений" , "Запрос данных" , "E2" , Type :=8) If rResultCell Is Nothing Then "если нажата кнопка Отмена Exit Sub End If "определяем максимально возможную размерность массива для результата ReDim avArr(1 To Rows.Count, 1 To 1) "при помощи объекта Коллекции(Collection) "отбираем только уникальные записи, "т.к. Коллекции не могут содержать повторяющиеся значения With New Collection On Error Resume Next For Each x In avVals If Len(CStr(x)) Then "пропускаем пустые ячейки .Add x, CStr(x) "если добавляемый элемент уже есть в Коллекции - возникнет ошибка "если же ошибки нет - такое значение еще не внесено, "добавляем в результирующий массив If Err = 0 Then li = li + 1 avArr(li, 1) = x Else "обязательно очищаем объект Ошибки Err.Clear End If End If Next End With "записываем результат на лист, начиная с указанной ячейки If li Then rResultCell.Cells(1, 1).Resize(li).Value = avArr End Sub |
Sub Extract_Unique() Dim x, avArr, li As Long Dim avVals Dim rVals As Range, rResultCell As Range On Error Resume Next "запрашиваем адрес ячеек для выбора уникальных значений Set rVals = Application.InputBox("Укажите диапазон ячеек для выборки уникальных значений", "Запрос данных", "A2:A51", Type:=8) If rVals Is Nothing Then "если нажата кнопка Отмена Exit Sub End If "если указана только одна ячейка - нет смысла выбирать If rVals.Count = 1 Then MsgBox "Для отбора уникальных значений требуется указать более одной ячейки", vbInformation, "www.сайт" Exit Sub End If "отсекаем пустые строки и столбцы вне рабочего диапазона Set rVals = Intersect(rVals, rVals.Parent.UsedRange) "если указаны только пустые ячейки вне рабочего диапазона If rVals Is Nothing Then MsgBox "Недостаточно данных для выбора значений", vbInformation, "www..Value "запрашиваем ячейку для вывода результата Set rResultCell = Application.InputBox("Укажите ячейку для вставки отобранных уникальных значений", "Запрос данных", "E2", Type:=8) If rResultCell Is Nothing Then "если нажата кнопка Отмена Exit Sub End If "определяем максимально возможную размерность массива для результата ReDim avArr(1 To Rows.Count, 1 To 1) "при помощи объекта Коллекции(Collection) "отбираем только уникальные записи, "т.к. Коллекции не могут содержать повторяющиеся значения With New Collection On Error Resume Next For Each x In avVals If Len(CStr(x)) Then "пропускаем пустые ячейки.Add x, CStr(x) "если добавляемый элемент уже есть в Коллекции - возникнет ошибка "если же ошибки нет - такое значение еще не внесено, "добавляем в результирующий массив If Err = 0 Then li = li + 1 avArr(li, 1) = x Else "обязательно очищаем объект Ошибки Err.Clear End If End If Next End With "записываем результат на лист, начиная с указанной ячейки If li Then rResultCell.Cells(1, 1).Resize(li).Value = avArr End Sub
Способ 4: Сводные таблицы
Несколько нестандартный способ извлечения уникальных значений.
- Выделяем один или несколько столбцов в таблице, переходим на вкладку Вставка (Insert) -группа Таблица (Table) -Сводная таблица (PivotTable)
- В диалоговом окне Создание сводной таблицы (Create PivotTable) проверяем правильность выделения диапазона данных (или установить новый источник данных)
- указываем место размещения Сводной таблицы:
- На новый лист (New Worksheet)
- На существующий лист (Existing Worksheet)
- подтверждаем создание нажатием кнопки OK
Т.к. сводные таблицы при обработке данных, которые помещаются в область строк или столбцов, отбирают из них только уникальные значения для последующего анализа, то от нас ровным счетом ничего не требуется, кроме как создать сводную таблицу и поместить в область строк или столбцов данные нужного столбца.
На примере приложенного к статье файла я:
В чем неудобство работы со сводными в данном случае: при изменении в исходных данных сводную таблицу придется обновлять вручную: Выделить любую ячейку сводной таблицы -Правая кнопка мыши -Обновить (Refresh) или вкладка Данные (Data) -Обновить все (Refresh all) -Обновить (Refresh) . А если исходные данные пополняются динамически и того хуже - надо будет заново указывать диапазон исходных данных. И еще один минус - данные внутри сводной таблицы нельзя менять. Поэтому если с полученным списком необходимо будет работать в дальнейшем, то после создания нужного списка при помощи сводной его надо скопировать и вставить на нужный лист.
Чтобы лучше понимать все действия и научиться обращаться со сводными таблицами настоятельно рекомендую ознакомиться со статьей Общие сведения о сводных таблицах - к ней приложен видеоурок, в котором я наглядно демонстрирую простоту и удобство работы с основными возможностями сводных таблиц.
В приложенном примере помимо описанных приемов, записана чуть более сложная вариация извлечения уникальных элементов формулой и кодом, а именно: извлечение уникальных элементов по критерию . О чем речь: если в одном столбце фамилии, а во втором(В) некие данные(в файле это месяцы) и требуется извлечь уникальные значения столбца В только для выбранной фамилии. Примеры подобных извлечений уникальных расположены на листе Извлечение по критерию .
Скачать пример:
(108,0 KiB, 14 152 скачиваний)
Статья помогла? Поделись ссылкой с друзьями! ВидеоурокиВыборка данных
Создать отчёт по выборке с Листа5 по столбцу "Качественная успеваемость, проц." (с Листа 8, табл.7)
Для того чтобы произвести выборку данных необходимо выполнить следующие действия:
Определить количество элементов нового массива по заданному условию введя переменную с помощью оператора InputBox
Объявить и переобъявить новый массив
Сформировать новый массив. Для этого необходимо задать номер первого элемента нового массива u=1. Затем выполняется цикл, в котором записывается условие выборки по столбцу "Качественная успеваемость, проц. ". Если результат проверки истина, то элемент анализируемого массива становится элементом нового массива.
Вывести новый элемент на Лист 8
Sub ОтчётВыборка ()
Sheets ("Лист8"). Select
Dim A () As Variant
ReDim A (1 To n1, 1 To m)
VVOD "Лист5", A, n1, m, 4
C = InputBox ("Введите условие ")
Sheets ("Лист8"). Cells (5,11) = C
For i = 1 To n1
If A (i,
8) >
d = d + 1
Sheets ("Лист8"). Cells (5,10) = d
Dim B () As Variant
ReDim B (1 To d, 1 To m)
For i = 1 To n1
If A (i,
8) > Sheets ("Лист8"). Cells (5,11) Then
For j = 1 To m
B (u, j) = A (i, j)
u = u + 1
For i = 1 To d
For j = 1 To m
Sheets ("Лист8"). Cells (i + 4, j) = B (i, j)
Рис.6. Данные таблицы после выборки
Создание автоматического макроса по выборке
Включаем запись макроса. Сервис>Макрос>Начать запись>ОК. Появится квадрат, где кнопка остановить запись. На Листе5 (отчет) выделяем таблицу без заголовков и итогов, копируем на Лист10 (автовыборка). Выделяем таблицу без заголовков и в элементе меню, выбираем Данные>Фильтр>Автофильтр>выбираем условие>ОК. Отмечаем столбец по которому будем сортировать. Завершаем работу макроса.
Sub Макрос2Выборка ()
" Макрос2Выборка Макрос
Sheets ("Лист5"). Select
Selection. Copy
Sheets ("Лист9"). Select
ActiveSheet. Paste
Range ("H5: H17"). Select
Application. CutCopyMode = False
Selection. AutoFilter
ActiveSheet. Range ("$H$5: $H$17"). AutoFilter Field: =1, Criteria1: =">80", _
Operator: =xlAnd
Range ("G22"). Select
Рис.7. Данные таблицы после авто-выборки
Определение максимального и минимального значения
Определить max и min значения по столбцам "Итого", "Абсолютная успеваемость, проц. ", "Качественная успеваемость" (таблица 9, лист 10)
Для определения max и min значений необходимо выполнить следующие действия:
Задать эталоновую переменную, которая будет текущем минимумом (максимумом)
Поочерёдно сравнивается каждый элемент совокупности с текущем минимумом (максимумом), и если этот элемент не удовлетворяет условиям поиска (в случаи минимума он больше, а в случае максимума - меньше), то происходит замена значения эталона на значение сравниваемого элемента
После полного просмотра всех элементов в переменной текущего минимума (максимума) находится действительный минимум (максимум)
Значение минимума (максимума) выводятся в соответствующие ячейки
Sub minmax ()
Dim A () As Variant
n1 = Sheets ("Лист4"). Cells (5,12)
m = Sheets ("Лист2"). Cells (5,12)
ReDim A (1 To n1, 1 To m)
VVOD "Лист5", A, n1, m, 4
VIVOD "Лист10", A, n1, m, 4
VVOD "Лист10", A, n1, m, 4
For j = 3 To m
maxA = 0.00001
minA = 1000000
For i = 1 To n1
If A (i, j) > maxA Then
maxA = A (i, j)
If A (i, j) < minA Then
minA = A (i, j)
Sheets ("Лист10"). Cells (i + 4 + 2, j) = maxA
Sheets ("Лист10"). Cells (i + 4 + 3, j) = minA
Макрос представляет собой последовательность действий, которая записана и сохранена для дальнейшего использования. Сохраненный макрос можно воспроизвести по специальной команде. Другими словами, вы можете записать свои действия в макросе, сохранить его, а затем разрешить другим пользователям воспроизводить сохраненные в макросе действия простым нажатием клавиши. Это особенно удобно при распространении отчетов сводных таблиц.
Предположим, вы хотите предоставить своим клиентам возможность группировать отчеты сводных таблиц по месяцам, кварталам и годам. Технически процесс группирования может выполнить любой пользователь, но некоторые из ваших клиентов не сочтут нужным разбираться в этом. В подобном случае можно записать один макрос группирования по месяцам, другой - по кварталам и третий - по годам. Затем создайте три кнопки - по одной на каждый макрос. Тогда вашим клиентам, не имеющим опыта работы со сводными таблицами, потребуется лишь щелкнуть на кнопке, чтобы должным образом сгруппировать отчет сводной таблицы.
Главное преимущество использования макросов в отчетах сводных таблиц состоит в предоставлении клиентам возможности быстро выполнять в сводных таблицах такие операции, которые они не могут выполнить в обычной ситуации. Благодаря этому существенно повышается эффективность анализа предоставляемых данных.
Скачать заметку в формате или , скачать с примерами (внутри файл Excel с макросами; политика провайдера не позволяет напрямую загрузить файл такого формата на сайт).
Запись макроса
Взгляните на сводную таблицу, показанную на рис. 1. Можно обновить эту сводную таблицу, щелкнув внутри нее правой кнопкой мыши и выбрав команду Обновить . Если во время обновления сводной таблицы вы записывали действия в виде макроса, то вы или любой другой пользователь сможете воспроизвести эти действия и обновить сводную таблицу в результате запуска макроса.
Рис. 1. Запись действий во время обновления этой сводной таблицы позволит в дальнейшем обновлять данные в результате запуска макроса
Первый этап в записи макроса - это вызов диалогового окна Запись макроса . Перейдите на вкладку Разработчик ленты и щелкните на кнопке Запись макроса . (Если вы не можете отыскать на ленте вкладку Разработчик , выберите вкладку Файл , и щелкните на кнопке Параметры . В появившемся диалоговом окне Параметры Excel выберите категорию Настройка ленты и в расположенном справа списке установите флажок Разработчик . В результате на ленте появится вкладка Разработчик .) Альтернативный способ начать записывать макрос – щелкнуть на кнопке (рис. 2).
В диалоговом окне Запись макроса введите следующую информацию о макросе (рис. 3):
Имя макроса . Имя должно описывать действия, выполняемые макросом. Имя должно начинаться с буквы или знака подчеркивания; не должно содержать пробел и другие недопустимые знаки; не должно совпадать со встроенным именем Excel или именем другого объекта в книге.
Сочетание клавиш . В это поле можно ввести любую букву. Она станет частью комбинации клавиш, которая будет использоваться для воспроизведения макроса. Комбинацию клавиш задавать необязательно. По умолчанию в качестве начала комбинации предлагается только Ctrl. Если вы хотите, чтобы комбинация включала также Shift, набирайте букву в окне удерживая нажатой клавишу Shift
Сохранить в . Здесь указывается место хранения макроса. Если вы собираетесь распространять отчет сводной таблицы среди других пользователей, выберите параметр Эта книга . Excelтакже позволяет сохранить макрос в Новой книге или в Личной книге макросов .
Описание . В это поле вводится описание создаваемого макроса.
Рис. 3. Настройка окна Запись макроса
Поскольку макрос обновляет сводную таблицу, выберите имя ОбновлениеДанных . Можно также назначить макросу комбинацию клавиш Ctrl+Shift+Q. Помните, что после создания макроса вы будете использовать эту комбинацию клавиш для его запуска. В качестве места хранения макроса выберите параметр Эта книга и щелкните ОК .
После щелчка в диалоговом окне Запись макроса на кнопке ОК начинается запись макроса. На этом этапе все выполняемые вами действия в Excel будут регистрироваться.
Щелкните правой кнопкой мыши в области сводной таблицы и выберите команду Обновить (как на рис. 1, но уже в режиме записи макроса). После обновления сводной таблицы можно остановить процесс записи макроса с помощью кнопки Остановить запись вкладки Разработчик . Или повторно щелкните на кнопке, изображенной на рис. 2.
Итак, вы только что записали свой первый макрос. Теперь можете выполнить макрос с помощью комбинации клавиш Ctrl+Shift+Q.
Предупреждение о безопасности макросов. Следует отметить, что в случае записи макросов пользователем они будут выполняться без каких-либо ограничений со стороны подсистемы безопасности. Тем не менее при распространении рабочей книги, содержащей макросы, необходимо предоставить другим пользователям возможность удостовериться, что никакого риска в открытии рабочих файлов нет, а выполнение макросов не приведет к заражению системы вирусами. В частности, вы сразу же заметите, что файл примера, используемый в этой главе, не будет полноценно функционировать, если специально не разрешить Excel выполнять в нем макросы.
Самый простой способ обеспечить безопасность макросов - создать надежное расположение - папку, в которую будут помещаться только «надежные» рабочие книги, не содержащие вирусов. Надежное расположение позволяет вам и вашим клиентам выполнять макросы в рабочих книгах без каких-либо ограничений со стороны подсистемы безопасности (такое поведение сохраняется до тех пор, пока рабочие книги находятся в доверительной папке).
Чтобы настроить надежное расположение, выполните следующие действия.
Выберите вкладку ленты Разработчик и щелкните на кнопке Безопасность макросов . На экране появится диалоговое окно Центр управления безопасностью .
Щелкните на кнопке Добавить новое расположение .
Щелкните на кнопке Обзор , чтобы указать папку для рабочих файлов, которым вы доверяете.
После указания надежного расположения для всех находящихся в нем рабочих книг будут по умолчанию выполняться произвольные макросы.
В Excel 2013 модель обеспечения безопасности была усовершенствована. Теперь файлы рабочих книг, которым ранее было «оказано доверие», запоминаются, т.е. после открытия книги Excel и щелчка на кнопке Включить содержимое Excel запоминает это состояние. В результате эта книга попадает в разряд доверенных, и лишние вопросы при ее последующем открытии не задаются.
Создание пользовательского интерфейса с помощью элементов управления формы
Запуск макроса с помощью комбинации клавиш Ctrl+Shift+Q поможет в том случае, когда в отчете сводной таблицы имеется лишь один макрос. (К тому же пользователи должны знать эту комбинацию.) Но предположим, что вы хотите предоставить своим клиентам несколько макросов, выполняющих разные действия. В таком случае нужно обеспечить клиентов понятным и простым способом запуска каждого макроса, не прибегая к запоминанию комбинаций клавиш. Идеальное решение - это простой пользовательский интерфейс в виде набора таких элементов управления, как кнопки, полосы прокрутки и другие средства, позволяющие выполнять макросы щелчками мышью.
Excel предлагает в ваше распоряжение набор инструментов, предназначенных для создания пользовательского интерфейса непосредственно в электронной таблице. Эти инструменты называются элементами управления формы. Основная идея заключается в том, что можно поместить элемент управления формы в электронную таблицу и назначить ему макрос, который был записан ранее. После назначения элементу управления макрос будет запускаться щелчком на этом элементе.
Элементы управления формы можно найти в группе Элементы управления формы вкладки ленты Разработчик . Чтобы открыть палитру элементов управления, щелкните в этой группе на кнопке Вставить (рис. 4).
Рис. 4. Элемент управления формы Кнопка
Обратите внимание: кроме элементов управления формы, на палитре также присутствуют Элементы ActiveX . Хоть они и похожи, программно это совершенно разные объекты. Элементы управления формы со своими ограниченными возможностями и простыми настройками специально разрабатывались для размещения на рабочих листах. В то же время Элементы ActiveX применяются преимущественно в пользовательских формах. Возьмите за правило размещать на рабочих листах исключительно элементы управления формы.
Следует выбрать элементы управления, которые лучше всего подходят для решения поставленной задачи. В рассматриваемом примере клиентам необходимо предоставить возможность обновления сводной таблицы щелчком на кнопке. Щелкните на элементе управления Кнопка , переместите указатель мыши в то место рабочего листа, где должна располагаться кнопка, и щелкните мышью.
После того как вы поместите кнопку в таблицу, откроется диалоговое окно Назначить макрос объекту (рис. 5). Выберите требуемый макрос (в нашем случае - ОбновлениеДанных , записанный ранее) и щелкните на кнопке ОК .
Рис. 5. Выберите макрос, который нужно присвоить кнопке, и щелкните на кнопке ОК . В данном случае следует применять макрос ОбновлениеДанных
После помещения всех необходимых элементов управления в отчет сводной таблицы можно отформатировать таблицу для создания базового интерфейса. На рис. 6 показан отчет сводной таблицы после форматирования.
Изменение записанного макроса
В результате записи макроса программа Excel создает модуль, который хранит выполненные вами действия. Все записанные действия представляются строками VBA-кода, из которых состоит макрос. Можно добавлять в отчеты сводной таблицы различные функциональные возможности, настраивая VBA-код для получения требуемых результатов. Чтобы было легче понять, как все это работает, создадим новый макрос, выводящий пять первых записей о клиентах. Перейдите на вкладку Разработчик и щелкните на кнопке Запись макроса . Откроется диалоговое окно, показанное на рис. 7. Назовите создаваемый макрос Первые Nзаказчиков и укажите место сохранения Эта книга . Щелкните ОК , чтобы начать запись макроса.
После того как начнете запись, щелкните на стрелке рядом с полем Имя заказчика , выберите Фильтр по значению и опцию Первые 10 (рис. 8а). В появившемся диалоговом окне задайте настройки, как показано на рис. 8б. Эти настройки указывают вывести данные пяти клиентов, лучших по объемам продаж. Щелкните ОК .
Рис. 8. Выберите фильтр (а) и настройте параметры (б), чтобы отобразить пятерку лучших клиентов по продажам
После успешной записи всех действий, требуемых для извлечения пятерки лучших клиентов по продажам, перейдите на вкладку Разработчик и щелкните на кнопке Остановить запись .
Теперь у вас имеется макрос, который будет фильтровать сводную таблицу для извлечения пятерки лучших клиентов по продажам. Необходимо сделать так, чтобы макрос реагировал на состояние полосы прокрутки, т.е. с помощью полосы прокрутки нужно иметь возможность указывать макросу количество клиентов, данные которых будут отображаться в отчете сводной таблицы. Таким образом, с помощью полосы прокрутки пользователь сможет извлекать пять лучших, восемь лучших или тридцать два лучших клиента по своему усмотрению.
Чтобы добавить в электронную таблицу полосу прокрутки, перейдите на вкладку Разработчик , щелкните на кнопке Вставить , выберите на палитре элемент управления Полоса прокрутки и расположите его на рабочем листе. Щелкните правой кнопкой мыши на элементе управления Полоса прокрутки Формат объекта . Откроется диалоговое окно Формат элемента управления (рис. 9). В нем внесите следующие изменения в настройки: параметру Минимальное значение присвойте значение 1, параметру Максимальное значение - значение 200, а в поле Связь с ячейкой введите значение $М$2, чтобы в ячейке М2 отображалось значение полосы прокрутки. Щелкните на кнопке ОК , чтобы применить указанные ранее настройки.
Теперь нужно сопоставить недавно записанный макрос Первые Nзаказчиков с элементом управления Полоса прокрутки , находящимся на рабочем листе. Щелкните правой кнопкой мыши на элементе управления Полоса прокрутки и в контекстном меню выберите команду Назначить макрос , чтобы открыть диалоговое окно назначения макроса. Назначьте полосе прокрутки записанный макрос ПервыеNзаказчиков . Макрос будет выполняться каждый раз после щелчка на полосе прокрутки. Протестируйте созданную полосу прокрутки. После щелчка на полосе запустится макрос ПервыеNзаказчиков и изменится число в ячейке М2 для отображения состояния полосы прокрутки. Число в ячейке М2 играет важную роль, поскольку оно используется для привязки макроса к полосе прокрутки.
Единственное, что осталось сделать, - это заставить макрос обрабатывать число в ячейке М2, связывая ее с полосой прокрутки. Для этого нужно перейти к VBA-коду макроса. Для этого перейдите на вкладку Разработчик и щелкнуть на кнопке Макросы . Откроется диалоговое окно Макрос (рис. 10). В нем можно запускать, удалять и редактировать выбранный макрос. Чтобы отобразить VBA-код макроса на экране, выберите макрос и щелкните на кнопке Изменить .
Рис. 10. Чтобы получить доступ к VBA-коду макроса Первые Nзаказчиков , выберите макрос и щелкните на кнопке Изменить
На экране появится окно редактора Visual Basic с VBA-кодом макроса (рис. 11). Ваша цель заключается в том, чтобы заменить жестко заданное в коде число 5, устанавливаемое во время записи макроса, значением в ячейке М2, которое привязано к полосе прокрутки. Изначально был записан макрос, предназначенный для отображения первых пяти заказчиков, имеющих наибольший доход.
Удалите из кода число 5 и введите вместо него следующее выражение:
ActiveSheet.Range(" М2 ").Value
Добавьте в начало макроса две строки для очистки фильтров:
Range(" A4 ").Select
ActiveSheet.PivotTables(" PivotTable1 ").PivotFields(" Имя заказчика ").ClearAllFilters
Теперь код макроса должен выглядеть так, как показано на рис. 12.
Закройте редактор Visual Basic и вернитесь к отчету сводной таблицы. Протестируйте полосу прокрутки, перетащив ползунок до значения 11. Макрос должен запуститься и отфильтровать 11 записей о лучших клиентах по продажам.
Синхронизация двух сводных таблиц с помощью одного раскрывающегося списка
Отчет, показанный на рис. 13, содержит две сводные таблицы. Каждая из них имеет поле страниц, позволяющее выбирать рынок сбыта. Проблема в том, что каждый раз при выборе рынка в поле страниц одной сводной таблицы вам приходится выбирать тот же рынок в поле страниц другой сводной таблицы. Синхронизация фильтров двух таблиц на этапе анализа данных не представляет большой проблемы, но существует вероятность, что вы или ваши клиенты все же забудут ее выполнить.
Рис. 13. Две сводные таблицы содержат поля страниц, которые выполняют фильтрацию данных по рынкам. Для анализа данных отдельного рынка нужно синхронизировать обе сводные таблицы
Один из способов синхронизации этих сводных таблиц состоит в использовании раскрывающегося списка. Идея заключается в записи макроса, который выбирает нужный рынок из поля Рынок сбыта в обеих таблицах. Затем нужно создать раскрывающийся список и заполнить его названиями рынков сбыта из двух сводных таблиц. И наконец, записанный макрос необходимо изменить для фильтрации обеих сводных таблиц, используя значения из раскрывающегося списка. Для решения этой задачи нужно выполнить следующие действия.
1. Создайте новый макрос и присвойте ему имя SynchMarkets . Когда начнется запись, выберите в поле Рынок сбыта обеих сводных таблиц рынок сбыта Калифорния и остановите запись макроса.
2. Отобразите на экране палитру элементов управления формы и добавьте на рабочий лист раскрывающийся список.
3. Создайте жестко заданный список всех рынков сводной таблицы. Обратите внимание на то, что первым элементом списка указывается значение (Все). Следует включить этот элемент, если хотите иметь возможность выбирать в раскрывающемся списке все рынки.
4. На этом этапе отчет сводной таблицы должен выглядеть так, как показано на рис. 14.
Рис. 14. В вашем распоряжении есть все требуемые средства: макрос, изменяющий поле Рынок сбыта обеих сводных таблиц, раскрывающийся список и список всех рынков сбыта, содержащихся в сводной таблице
5. Щелкните правой кнопкой мыши на раскрывающемся списке и в контекстном меню выберите команду Формат объекта , чтобы выполнить настройку элемента управления.
6. Вначале задайте исходный диапазон значений, используемый для заполнения раскрывающегося списка, как показано на рис. 15. В данном случае речь идет о списке рынков сбыта, созданном вами в п. 3. Затем укажите ячейку, отображающую порядковый номер выбранного элемента (в данном примере таковой является ячейка Н1). Параметр Количество строк списка определяет, сколько строк будет одновременно отражаться в ниспадающем списке. Щелкните на кнопке ОК .
Рис. 15. Настройки раскрывающегося списка должны указывать на список рынков сбыта как на исходный диапазон значений, а в качестве точки привязки - определять ячейку Н1
7. Теперь у вас появилась возможность выбирать в раскрывающемся списке рынок сбыта, а также определять связанный с ним порядковый номер в ячейке Н1 (рис. 16). Возникает вопрос: зачем вместо реального имени рынка используется его индексное значение? Потому что раскрывающийся список возвращает не имя, а номер. Например, при выборе в раскрывающемся списке имени Калифорния в ячейке Н1 появляется значение 5. Это означает, что Калифорния является пятым элементом списка.
Рис. 16. Раскрывающийся список теперь заполняется названиями рынков, и в ячейке Н1 выводится порядковый номер выбранного рынка
8. Чтобы использовать порядковый номер вместо имени рынка, вам следует передать его с помощью функции ИНДЕКС.
9. Введите функцию ИНДЕКС, которая преобразует порядковый номер из ячейки Н1 в понятное значение.
10. Функция ИНДЕКС принимает два аргумента. Первый аргумент представляет диапазон значений списка. В большинстве случаев вы будете использовать тот же диапазон, которым заполняется раскрывающееся меню. Второй аргумент - это порядковый номер. Если порядковый номер вводится в ячейке (например, в ячейке Н1, как на рис. 17), то можете просто сослаться на эту ячейку.
Рис. 17. Функция ИНДЕКС в ячейке I1 преобразует порядковый номер, хранящийся в ячейке Н1, в значение. Вы будете использовать значение в ячейке I1 для изменения макроса
11. Отредактируйте макрос SynchMarkets , используя значение в ячейке I1 вместо жестко заданного значения. Перейдите на вкладку Разработчик и щелкните на кнопке Макросы . На экране появится диалоговое окно, показанное на рис. 18. Выберите в нем макрос SynchMarkets и щелкните на кнопке Изменить.
Рис. 18. Чтобы получить доступ к VBA-коду макроса, выберите макрос SynchMarkets и щелкните Изменить
12. При записи макроса вы выбрали в обеих сводных таблицах рынок сбыта Калифорния из поля Рынок сбыта . Как видно из рис. 19, рынок Калифорния теперь жестко задан в VBA-коде макроса.
13. Замените значение " Калифорния " выражением Activesheet.Range(" I1 ").Value, которое ссылается на значение в ячейке I1. На этом этапе код макроса должен выглядеть так, как показано на рис. 20. После изменения макроса закройте редактор Visual Basic и вернитесь к электронной таблице.
Рис. 20. Замените значение " Калифорния " выражением ActiveSheet.Range(" I1 ").Value и закройте редактор Visual Basic
14. Осталось только обеспечить выполнение макроса при выборе рынка сбыта в раскрывающемся списке. Щелкните правой кнопкой мыши на раскрывающемся списке и выберите параметр Назначить макрос . Выберите макрос SynchMarket и щелкните на кнопке ОК .
15. Скройте строки и столбцы с полями страниц в сводных таблицах, а также созданный вами список рынков и формулы индекса.
На рис. 21 показан окончательный результат. Вы получили пользовательский интерфейс, позволяющий клиентам выбирать рынок сбыта в обеих сводных таблицах с помощью единственного раскрывающегося списка.
При выборе в раскрывающемся списке нового элемента размеры столбцов автоматически изменяются, чтобы вместить все отображаемые в них данные. Подобное поведение программы порядком надоедает при форматировании шаблона рабочего листа. Можно предотвратить его, если щелкнуть на сводной таблице правой кнопкой мыши и выбрать команду Параметры сводной таблицы . На экране появится одноименное диалоговое окно, в котором необходимо сбросить флажок Автоматически изменять ширину столбцов при обновлении .
Заметка написана на основе книги Джелен, Александер. . Глава 12.
Первый способ: Применение расширенного автофильтра
На листе Excel необходимо выделить область, среди данных, которых и нужно осуществить выборку. Во вкладке «Главная» нажимаете «Сортировка и фильтр» (находится в блоке настроек «Редактирование»). Далее нажимаете на фильтр.
Можно сделать иначе, после выделения области переходите во вкладку «Данные» и нажимаете на «Фильтр», размещенной в группе «Сортировка и фильтр».
Когда эти действия выполнены, в шапке таблицы должны появиться пиктограммы для запуска фильтрования. Они будут отображены острием вниз небольшими треугольниками в правом крае ячеек. Нажимаете на этот значок в начале того столбца, по которому и собираетесь сделать выборку. Запуститься меню, в нем переходите в «Текстовые фильтры» и выбираете «Настраиваемый фильтр…».
Теперь должно активироваться окно пользовательской фильтрации. В нем задаете ограничение, по которому и будет осуществляться отбор. Можно выбрать одно из пяти предложенных видов условий: равно, не равно, больше, больше или равно, меньше.
После фильтрации остаются только те строчки, в которых сумма выручки превышает значение 10000 (как пример).
В этом же столбце есть возможность добавить и второе условие. Нужно снова вернуться в окно пользовательской фильтрации, а в его нижней части установить другую границу отбора. Переключатель выставляете в позицию «Меньше», а в поле справа вписываете «15000».
В таблице останутся только те строки, в которых сумма выручки не меньше 10000, но и не больше 15000.
В других столбцах выборка настраивается по аналогии. В нужном столбце нажимаете по значку фильтрации, а дальше последовательно кликаете по пунктам списка «Фильтр по дате» и «Настраиваемый фильтр».
Должно запуститься окно пользовательского автофильтра. Выполните, к примеру, отбор результатов в таблице с 4 по 6 мая 2016 года включительно. Нажимаете «После или равно», а в поле справа выставляете значение «04.05.2016». В нижнем блоке переключатель ставите в позицию «До или равно», а в правом поле вносите «06.05.2016». Переключатель совместимости условий оставляете в положении по умолчанию, то есть «И». Для применения фильтрации кликаете на ОК.
Список теперь должен сократиться еще больше, потому что останутся только строки, в которых сумма выручки варьируется от 10000 до 15000 и это за период с 04.05 по 06.05.2016 включительно.
В одном из столбцов при желании можно сбросить фильтрацию. К примеру, можно сделать это для значений выручки. Нажимаете на значок автофильтра в соответствующем столбце. Выбираете «Удалить фильтр».
Выборка по сумме выручки отключится и останется только отбор по датам (с 04.05.2016 по 06.05.2016).
В таблице есть и еще одна колонка под названием «Наименование». В ней расположенные данные в текстовом формате. По этим значениям тоже можно сформировать выборку. В наименовании столбца нажмите на значок фильтра. Переходите на «Текстовые фильтры», а затем «Настраиваемый фильтр…».
Снова откроется окно пользовательского фильтра, в котором можно сделать выборку, к примеру, по наименованиям «Мясо» и «Картофель». В первом блоке нужно установить переключатель в позиции «Равно» а в поле справа от него внести «Картофель». Переключатель нижнего блока поставить в позицию «Равно», а в поле напротив – «Мясо». Теперь следует установить переключатель совместимости условий в позиции «ИЛИ». Нажимаете ОК.
В новой выборке выставлены ограничения по дате (с 04.05.2016 по 06.05.2016) и по наименованию (Мясо и Картофель). Ограничений нет только по сумме выручки.
Можно фильтр удалить полностью и делается это теми же способами, которые применялись для его выставления. Для того чтобы сбросить фильтрацию во вкладке «Данные» нажмите на «Фильтр» в группе «Сортировка и фильтр».
Во втором варианте можно перейти во вкладку «Главная» и нажать там на «Сортировка и фильтр» в «Редактирование». Далее кликаете на «Фильтр».
Если использовать любой из указанных методов, то таблица удалится, а результаты выборки очистятся. То есть таблица будет отображать все ранее внесенные в нее данные.
Второй способ: Применение формулы массива
На том же листе Excel создаете пустую таблицу с теми же наименованиями столбцов в шапке, которые имеются у исходника.
Все пустые ячейки необходимо выделить в первой колонке новой таблицы. В строку формул устанавливаете курсор, чтобы занести формулу - =ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(15000
Для применения формулы нужно нажать на клавиши Ctrl+Shift+Enter.
Выделяете второй столбец с датами и ставите курсор в строку формул, чтобы занести - =ИНДЕКС(B2:B29;НАИМЕНЬШИЙ(ЕСЛИ(15000
Таким же способ в столбец с выручкой вносите такую формулу - =ИНДЕКС(C2:C29;НАИМЕНЬШИЙ(ЕСЛИ(15000
Откроется окно форматирования, в котором нужно выбрать вкладку «Число». В «Числовые форматы» выбираете «Дата». В правой части окна при желании можно выбрать тип отображаемой даты, а когда все настройки будут внесены, то кликнуть на ОК.
Теперь все будет красиво, и дата отобразится корректно. Если в ячейках отображается значение «#ЧИСЛО!», то нужно применить условное форматирование. Все ячейки таблицы следует выделить (кроме шапки) и, находясь во вкладке «Главная» нажать на «Условное форматирование» (в блоке инструментов «Стили»). Появится список, в котором следует выбрать «Создать правило…».
Выбираете правила «Форматировать только ячейки, которые содержат», а в первом поле, находящемся под строкой «Форматировать только ячейки, для которых выполняется следующее условие» выбрать «Ошибки» и нажать «Формат…».
Запустится окно форматирования, в котором переходите на «Шрифт» и выбираете белый цвет. Кликаете на ОК.
Перед вами появится, готовая выборка по указанному ограничению, и будет это все в отдельной таблице.
Третий способ: Выборка по нескольким условиям с помощью формулы
В отдельном столбце следует внести граничные условия для выборки.
По очереди выделяете пустые столбцы новой таблицы, чтобы внести в них необходимые три формулы. В первый столбец вносите - =ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(($D$2=C2:C29);СТРОКА(C2:C29);"");СТРОКА(C2:C29)-СТРОКА($C$1))-СТРОКА($C$1)). Далее в колонки вбиваете такие же формулы, только изменяете координаты после наименования оператора ИНДЕКС на те, которые нужны и соответствуют определенным столбцам. Все по аналогии с предыдущим способом. Каждый раз после того как делаете ввод, не забывайте нажимать сочетание клавиш Ctrl+Shift+Enter.
Если нужно будет поменять границы выборки, то можно просто в колонке условий изменить граничные числа и тогда результат отбора будет автоматически изменен.
Четвертый способ: Случайная выборка
С левой стороны от таблицы нужно пропустить один столбец, а в ячейке следующего внести формулу - =СЛЧИС(), чтобы вывести на экран случайное число. Для ее активации нажимаете ENTER.
Если нужно сделать целый столбец случайных чисел, тогда устанавливаете курсор в нижний правый угол ячейки с содержащейся формулой. Должен появиться маркер заполнения, который следует протянуть вниз с помощью зажатия левой кнопки мыши. Делается это параллельно таблице с данными и до конца.
Диапазон ячеек будет содержать в себе формулу СЛЧИС, но работать с чистыми значениями не нужно. Копируете в пустой столбец справа и выделяете диапазон ячеек со случайными числами. Во вкладке «Главная» нажимаете на «Копировать».
Выделяете пустой столбец и нажимаете правой кнопкой мыши, чтобы вызвать контекстное меню. В группе инструментов «Параметры вставки» выбираете «Значения» (изображен в виде пиктограммы с цифрами).
Во вкладке «Главная» нажимаете на «Сортировка и фильтр», а затем «Настраиваемая сортировка».
Рядом с параметром «Мои данные содержат заголовки» ставите галочку. В строке «Сортировать по» указываете название того столбца, в котором находятся скопированные значения случайных чисел. В строке «Сортировка» настройки остаются по умолчанию. В строке «Порядок» выбираете параметр «По возрастанию» или «По убыванию». Кликаете на ОК.
Значения таблицы должны выстроиться в порядке возрастания или убывания случайных чисел. Любое количество первых строчек из таблицы можно взять и считать их результатом случайной выборки.
Суть запроса на выборку – выбрать из исходной таблицы строки, удовлетворяющие определенным критериям (подобно применению стандартного ). Произведем отбор значений из исходной таблицы с помощью . В отличие от применения (CTRL+SHIFT+L или Данные/ Сортировка и фильтр/ Фильтр ) отобранные строки будут помещены в отдельную таблицу.
В этой статье рассмотрим наиболее часто встречающиеся запросы, например: отбор строк таблицы, у которых значение из числового столбца попадает в заданный диапазон (интервал); отбор строк, у которых дата принаждежит определенному периоду; задачи с 2-мя текстовыми критериями и другие. Начнем с простых запросов.
1. Один числовой критерий (Выбрать те Товары, у которых цена выше минимальной)
файл примера, лист Один критерий - число ).
Необходимо отобразить в отдельной таблице только те записи (строки) из Исходной таблицы, у которых цена выше 25.
Решить эту и последующие задачи можно легко с помощью . Для этого выделите заголовки Исходной таблицы и нажмите CTRL+SHIFT+L . Через выпадающий список у заголовка Цены выберите Числовые фильтры... , затем задайте необходимые условия фильтрации и нажмите ОК.
Будут отображены записи удовлетворяющие условиям отбора.
Другим подходом является использование . В отличие от отобранные строки будут помещены в отдельную таблицу - своеобразный , который, например, можно отформатировать в стиль отличный от Исходной таблицы или производить другие ее модификации.
Критерий (минимальную цену) разместим в ячейке Е6 , таблицу для отфильтрованных данных - в диапазоне D10:E19 .
Теперь выделим диапазон D11:D19 (столбец Товар) и в введем :
ИНДЕКС(A11:A19;
НАИМЕНЬШИЙ(ЕСЛИ($E$6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
-СТРОКА($B$10))
Вместо ENTER нажмите сочетание клавиш CTRL+SHIFT+ENTER (формула массива будет ).
E11:E19 (столбец Цена) куда и введем аналогичную :
ИНДЕКС(B11:B19;
НАИМЕНЬШИЙ(ЕСЛИ($E$6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
-СТРОКА($B$10))
В результате получим новую таблицу, которая будет содержать только товары, у которых цены не меньше, указанной в ячейке Е6 .
Чтобы показать динамизм полученного Запроса на выборку, введем в Е6 значение 55. В новую таблицу попадет только 2 записи.
Если в Исходную таблицу добавить новый товар с Ценой 80, то в новую таблицу автоматически будет добавлена новая запись.
Примечание . Также для вывода отфильтрованных данных можно использовать и . Выбор конкретного инструмента зависит от стоящей перед пользователем задачи.
Если Вам не удобно использовать формулу массива , которая возвращает несколько значений, то можно использовать другой подход, который рассмотрен в разделах ниже: 5.а, 7, 10 и 11. В этих случаях используются .
2. Два числовых критерия (Выбрать те Товары, у которых цена попадает в диапазон)
Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист Диапазон Чисел ).
Критерии (нижнюю и верхнюю границы цены) разместим в диапазоне Е5:Е6 .
Т.е. если Цена Товара попадает в указанный интервал, то такая запись появится в новой таблице Отфильтрованные данные.
В отличие от предыдущей задачи создадим два : Товары и Цены (без них можно обойтись, но они удобны при написании формул). Соответствующие формулы должны выглядеть в Диспетчере имен (Формулы/ Определенные имена/ Диспетчер имен ) следующим образом (см. рисунок ниже).
Теперь выделим диапазон D11:D19 и в введем :
ИНДЕКС(Товары;
НАИМЕНЬШИЙ(
ЕСЛИ(($E$5<=Цены)*($E$6>=Цены);СТРОКА(Цены);"");
Вместо ENTER нажмите сочетание клавиш CTRL+SHIFT+ENTER .
Те же манипуляции произведем с диапазоном E11:E19 куда и введем аналогичную :
ИНДЕКС(Цены;
НАИМЕНЬШИЙ(
ЕСЛИ(($E$5<=Цены)*($E$6>=Цены);СТРОКА(Цены);"");
СТРОКА(Цены)-СТРОКА($B$10))-СТРОКА($B$10))
В результате получим новую таблицу, которая будет содержать только товары, у которых цены попадают в интервал, указанный в ячейках Е5 и Е6 .
Чтобы показать динамизм полученного Отчета (Запроса на выборку) введем в Е6 значение 65. В новую таблицу будет добавлена еще одна запись из Исходной таблицы, удовлетворяющая новому критерию.
Если в Исходную таблицу добавить новый товар с Ценой в диапазоне от 25 до 65, то в новую таблицу будет добавлена новая запись.
В файле примера также содержатся формулы массива с обработкой ошибок, когда в столбце Цена содержится значение ошибки, например #ДЕЛ/0! (см. лист Обработка ошибок ).
Следующие задачи решаются аналогичным образом, поэтому не будем их рассматривать так детально.
3. Один критерий Дата (Выбрать те Товары, у которых Дата поставки совпадает заданной)
файл примера, лист Один критерий - Дата ).
Для отбора строк используются формулы массива, аналогичные Задаче1 (вместо критерия <= используется =):
=ИНДЕКС(A12:A20;НАИМЕНЬШИЙ(ЕСЛИ($E$6=B12:B20;СТРОКА(B12:B20);"");СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))
ИНДЕКС(B12:B20;НАИМЕНЬШИЙ(ЕСЛИ($E$6=B12:B20;СТРОКА(B12:B20);"");СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))
4. Два критерия Дата (Выбрать те Товары, у которых Дата поставки попадает в диапазон)
Пусть имеется Исходная таблица с перечнем Товаров и Датами поставки (см. файл примера, лист Диапазон Дат ).
Обратите внимание, что столбец Дат НЕ СОРТИРОВАН.
Решение1 : Для отбора строк можно использовать .
Введите в ячейку D12 формулу массива:
ИНДЕКС(A$12:A$20;
НАИБОЛЬШИЙ(($E$6<=$B$12:$B$20)*($E$7>=$B$12:$B$20)*(СТРОКА($B$12:$B$20)-СТРОКА($B$11));
$J$12-СТРОКА(A12)+СТРОКА($B$11)+1))
Примечание : После ввода формулы вместо клавиши ENTER (ВВОД) нужно нажать сочетание клавиш CTRL+SHIFT+ENTER. Это сочетание клавиш используется для ввода формул массива.
Скопируйте формулу массива вниз на нужное количество ячеек. Формула вернет только те значения Товаров, которые были поставлены в диапазоне указанных дат. В остальных ячейках будут содержаться ошибки #ЧИСЛО! Ошибки в файле примера (Лист 4.Диапазон Дат) .
Аналогичную формулу нужно ввести и для дат в столбец E.
В ячейке J12 вычислено количество строк исходной таблицы, удовлетворяющих критериям:
СЧЁТЕСЛИМН(B12:B20;">="&$E$6;B12:B20;"<="&$E$7)
Строки исходной таблицы, которые удовлетворяют критериям, .
Решение2 : Для отбора строк можно использовать формулы массива, аналогичные Задаче2 (т.е. ):
=ИНДЕКС(A12:A20;НАИМЕНЬШИЙ(ЕСЛИ(($E$6<=B12:B20)*($E$7>=B12:B20);СТРОКА(B12:B20);"");СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))
ИНДЕКС(B12:B20;НАИМЕНЬШИЙ(ЕСЛИ(($E$6<=B12:B20)*($E$7>=B12:B20);СТРОКА(B12:B20);"");СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))
Для ввода первой формулы выделите диапазон ячеек G12:G20 . После ввода формулы вместо клавиши ENTER (ВВОД) нужно нажать сочетание клавиш CTRL+SHIFT+ENTER.
Решение3 : Если столбец Дат СОРТИРОВАН, то можно не использовать формулы массива.
Сначала необходимо вычислить первую и последнюю позиции строк, которые удовлетворяют критериям. Затем вывести строки .
Этот пример еще раз наглядно демонстрирует насколько облегчает написание формул.
5. Один критерий Дата (Выбрать те Товары, у которых Дата поставки не раньше/ не позже заданной)
Пусть имеется Исходная таблица с перечнем Товаров и Датами поставки (см. файл примера, лист Один критерий - Дата (не позже) ).
Для отбора строк, дата которых не раньше (включая саму дату), используется формула массива:
=ИНДЕКС(A12:A20;НАИМЕНЬШИЙ(ЕСЛИ($E$7<=B12:B20;СТРОКА(B12:B20);"");СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))
Также в файле примера приведены формулы для условий: Не раньше (не включая); Не позже (включая); Не позже (не включая).
7. Один Текстовый критерий (Выбрать Товары определенного вида)
Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист Один критерий - Текст ).
8. Два Текстовых критерия (Выбрать Товары определенного вида, поставленные в заданный месяц)
Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист 2 критерия - текст (И) ).
ИНДЕКС($A$11:$A$19;
НАИМЕНЬШИЙ(ЕСЛИ(($F$6=$A$11:$A$19)*($F$7=$B$11:$B$19);СТРОКА($A$11:$A$19)-СТРОКА($A$10);30);СТРОКА(ДВССЫЛ("A1:A"&ЧСТРОК($A$11:$A$19)))))
Выражение ($F$6=$A$11:$A$19)*($F$7=$B$11:$B$19) задает оба условия (Товар и Месяц).
Выражение СТРОКА(ДВССЫЛ("A1:A"&ЧСТРОК($A$11:$A$19))) формирует {1:2:3:4:5:6:7:8:9}, т.е. номера строк в таблице.
9. Два Текстовых критерия (Выбрать Товары определенных видов)
Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист 2 критерия - текст (ИЛИ) ).
В отличие от Задачи 7 отберем строки с товарами 2-х видов ().
Для отбора строк используется формула массива:
ИНДЕКС(A$11:A$19;
НАИБОЛЬШИЙ((($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19))*(СТРОКА($A$11:$A$19)-СТРОКА($A$10)); СЧЁТЕСЛИ($A$11:$A$19;$E$6)+СЧЁТЕСЛИ($A$11:$A$19;$E$7)-ЧСТРОК($A$11:A11)+1))
Условие ($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19) гарантирует, что будут отобраны товары только заданных видов из желтых ячеек (Товар2 и Товар3). Знак + (сложение) используется для задания (должен быть выполнен хотя бы 1 критерий).
Вышеуказанное выражение вернет массив {0:0:0:0:1:1:1:0:0}. Умножив его на выражение СТРОКА($A$11:$A$19)-СТРОКА($A$10) , т.е. на {1:2:3:4:5:6:7:8:9}, получим массив позиций (номеров строк таблицы), удовлетворяющих критериям. В нашем случае это будет массив {0:0:0:0:5:6:7:0:0}.
В качестве примера приведем решения следующей задачи: Выбрать Товары, цена которых лежит в определенном диапазоне и повторяется заданное количество раз или более.
В качестве исходной возьмем таблицу партий товаров.
Предположим, что нас интересует сколько и каких партий товаров поставлялось по цене от 1000р. до 2000р. (критерий 1). Причем, партий с одинаковой ценой должно быть минимум 3 (критерий 2).
Решением является формула массива:
НАИМЕНЬШИЙ(СТРОКА($A$14:$A$27)*($C$14:$C$27>=$B$7)*($C$14:$C$27<=$C$7)*($D$14:$D$27>=$B$10);F14+($G$8-$G$9))
Эта формула возвращает номера строк, которые удовлетворяют обоим критериям.
Формула =СУММПРОИЗВ(($C$14:$C$27>=$B$7)*($C$14:$C$27<=$C$7)*($D$14:$D$27>=$B$10)) подсчитывает количество строк, которые удовлетворяют критериям.
11. Используем значение критерия (Любой) или (Все)
В файле примера на листе "11. Критерий Любой или (Все)" реализован данный вариант критерия.
Формула в этом случае должна содержать функцию ЕСЛИ() . Если выбрано значение (Все), то используется формула для вывода значений без учета данного критерия. Если выбрано любое другое значение, то критерий работает обычным образом.
ЕСЛИ($C$8="(Все)";
НАИМЕНЬШИЙ((СТРОКА($B$13:$B$26)-СТРОКА($B$12))*($D$13:$D$26>=$D$8);F13+($G$6-$G$7));
НАИМЕНЬШИЙ((СТРОКА($B$13:$B$26)-СТРОКА($B$12))*($D$13:$D$26>=$D$8)*($C$13:$C$26=$C$8);F13+($G$6-$G$7)))
Остальная часть формулы аналогична рассмотренным выше.
