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

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

Способ 1: применение расширенного автофильтра

Наиболее простым способом произвести отбор является применение расширенного автофильтра. Рассмотрим, как это сделать на конкретном примере.

1. Выделяем область на листе, среди данных которой нужно произвести выборку. Во вкладке «Главная» щелкаем по кнопке «Сортировка и фильтр» . Она размещается в блоке настроек «Редактирование» . В открывшемся после этого списка выполняем щелчок по кнопке «Фильтр» .

   

   Есть возможность поступить и по-другому. Для этого после выделения области на листе перемещаемся во вкладку «Данные» . Щелкаем по кнопке «Фильтр» , которая размещена на ленте в группе «Сортировка и фильтр» .



2. После этого действия в шапке таблицы появляются пиктограммы для запуска фильтрования в виде перевернутых острием вниз небольших треугольников на правом краю ячеек. Кликаем по данному значку в заглавии того столбца, по которому желаем произвести выборку. В запустившемся меню переходим по пункту «Текстовые фильтры» . Далее выбираем позицию «Настраиваемый фильтр…» .



3. Активируется окно пользовательской фильтрации. В нем можно задать ограничение, по которому будет производиться отбор. В выпадающем списке для столбца содержащего ячейки числового формата, который мы используем для примера, можно выбрать одно из пяти видов условий:
   • равно;
   • не равно;
   • больше;
   • больше или равно;
   • меньше.

   Давайте в качестве примера зададим условие так, чтобы отобрать только значения, по которым сумма выручки превышает 10000 рублей. Устанавливаем переключатель в позицию «Больше» . В правое поле вписываем значение «10000» . Чтобы произвести выполнение действия, щелкаем по кнопке «OK» .



4. Как видим, после фильтрации остались только строчки, в которых сумма выручки превышает 10000 рублей.



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

   Кроме того, существует ещё переключатель условий. У него два положения «И» и «ИЛИ» . По умолчанию он установлен в первом положении. Это означает, что в выборке останутся только строчки, которые удовлетворяют обоим ограничениям. Если он будет выставлен в положение «ИЛИ» , то тогда останутся значения, которые подходят под любое из двух условий. В нашем случае нужно выставить переключатель в положение «И» , то есть, оставить данную настройку по умолчанию. После того, как все значения введены, щелкаем по кнопке «OK» .



6. Теперь в таблице остались только строчки, в которых сумма выручки не меньше 10000 рублей, но не превышает 15000 рублей.



7. Аналогично можно настраивать фильтры и в других столбцах. При этом имеется возможность сохранять также фильтрацию и по предыдущим условиям, которые были заданы в колонках. Итак, посмотрим, как производится отбор с помощью фильтра для ячеек в формате даты. Кликаем по значку фильтрации в соответствующем столбце. Последовательно кликаем по пунктам списка «Фильтр по дате» и «Настраиваемый фильтр» .



8. Снова запускается окно пользовательского автофильтра. Выполним отбор результатов в таблице с 4 по 6 мая 2016 года включительно. В переключателе выбора условий, как видим, ещё больше вариантов, чем для числового формата. Выбираем позицию «После или равно» . В поле справа устанавливаем значение «04.05.2016» . В нижнем блоке устанавливаем переключатель в позицию «До или равно» . В правом поле вписываем значение «06.05.2016» . Переключатель совместимости условий оставляем в положении по умолчанию – «И» . Для того, чтобы применить фильтрацию в действии, жмем на кнопку «OK» .



9. Как видим, наш список ещё больше сократился. Теперь в нем оставлены только строчки, в которых сумма выручки варьируется от 10000 до 15000 рублей за период с 04.05 по 06.05.2016 включительно.



10. Мы можем сбросить фильтрацию в одном из столбцов. Сделаем это для значений выручки. Кликаем по значку автофильтра в соответствующем столбце. В выпадающем списке щелкаем по пункту «Удалить фильтр» .



11. Как видим, после этих действий, выборка по сумме выручки будет отключена, а останется только отбор по датам (с 04.05.2016 по 06.05.2016).



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

    Кликаем по значку фильтра в наименовании столбца. Последовательно переходим по наименованиям списка «Текстовые фильтры» и «Настраиваемый фильтр…» .



13. Опять открывается окно пользовательского автофильтра. Давайте сделаем выборку по наименованиям «Картофель» и «Мясо» . В первом блоке переключатель условий устанавливаем в позицию «Равно» . В поле справа от него вписываем слово «Картофель» . Переключатель нижнего блока так же ставим в позицию «Равно» . В поле напротив него делаем запись – «Мясо» . И вот далее мы выполняем то, чего ранее не делали: устанавливаем переключатель совместимости условий в позицию «ИЛИ» . Теперь строчка, содержащая любое из указанных условий, будет выводиться на экран. Щелкаем по кнопке «OK» .



14. Как видим, в новой выборке существуют ограничения по дате (с 04.05.2016 по 06.05.2016) и по наименованию (картофель и мясо). По сумме выручки ограничений нет.



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

    

    Второй вариант предполагает переход во вкладку «Главная» . Там выполняем щелчок на ленте по кнопке «Сортировка и фильтр» в блоке «Редактирование» . В активировавшемся списке нажимаем на кнопку «Фильтр» .

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

Способ 2: применение формулы массива

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

1. На том же листе создаем пустую таблицу с такими же наименованиями столбцов в шапке, что и у исходника.



2. Выделяем все пустые ячейки первой колонки новой таблицы. Устанавливаем курсор в строку формул. Как раз сюда будет заноситься формула, производящая выборку по указанным критериям. Отберем строчки, сумма выручки в которых превышает 15000 рублей. В нашем конкретном примере, вводимая формула будет выглядеть следующим образом:

   ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

   Естественно, в каждом конкретном случае адрес ячеек и диапазонов будет свой. На данном примере можно сопоставить формулу с координатами на иллюстрации и приспособить её для своих нужд.



3. Так как это формула массива, то для того, чтобы применить её в действии, нужно нажимать не кнопку Enter , а сочетание клавиш Ctrl+Shift+Enter . Делаем это.



4. Выделив второй столбец с датами и установив курсор в строку формул, вводим следующее выражение:

   ИНДЕКС(B2:B29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

   Жмем сочетание клавиш Ctrl+Shift+Enter .



5. Аналогичным образом в столбец с выручкой вписываем формулу следующего содержания:

   ИНДЕКС(C2:C29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

   Опять набираем сочетание клавиш Ctrl+Shift+Enter .

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



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



7. В открывшемся окне форматирования открываем вкладку «Число» . В блоке «Числовые форматы» выделяем значение «Дата» . В правой части окна можно выбрать желаемый тип отображения даты. После того, как настройки выставлены, жмем на кнопку «OK» .



8. Теперь дата отображается корректно. Но, как видим, вся нижняя часть таблицы заполнена ячейками, которые содержат ошибочное значение «#ЧИСЛО!» . По сути, это те ячейки, данных из выборки для которых не хватило. Более привлекательно было бы, если бы они отображались вообще пустыми. Для этих целей воспользуемся условным форматированием. Выделяем все ячейки таблицы, кроме шапки. Находясь во вкладке «Главная» кликаем по кнопке «Условное форматирование» , которая находится в блоке инструментов «Стили» . В появившемся списке выбираем пункт «Создать правило…» .



9. В открывшемся окне выбираем тип правила «Форматировать только ячейки, которые содержат» . В первом поле под надписью «Форматировать только ячейки, для которых выполняется следующее условие» выбираем позицию «Ошибки» . Далее жмем по кнопке «Формат…» .



10. В запустившемся окне форматирования переходим во вкладку «Шрифт» и в соответствующем поле выбираем белый цвет. После этих действий щелкаем по кнопке «OK» .



11. На кнопку с точно таким же названием жмем после возвращения в окно создания условий.

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

Способ 3: выборка по нескольким условиям с помощью формулы

Так же, как и при использовании фильтра, с помощью формулы можно осуществлять выборку по нескольким условиям. Для примера возьмем всю ту же исходную таблицу, а также пустую таблицу, где будут выводиться результаты, с уже выполненным числовым и условным форматированием. Установим первым ограничением нижнюю границу отбора по выручке в 15000 рублей, а вторым условием верхнюю границу в 20000 рублей.

Способ 4: случайная выборка

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

Как видим, выборку в таблице Excel можно произвести, как с помощью автофильтра, так и применив специальные формулы. В первом случае результат будет выводиться в исходную таблицу, а во втором – в отдельную область. Имеется возможность производить отбор, как по одному условию, так и по нескольким. Кроме того, можно осуществлять случайную выборку, использовав функцию СЛЧИС .

Выборка данных

Создать отчёт по выборке с Листа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 для этой цели можно успешно использовать формулу в массиве.

Как сделать выборку в Excel по условию

Чтобы определить соответствующие значение первому наименьшему числу нужна выборка из таблицы по условию. Допустим мы хотим узнать первый самый дешевый товар на рынке из данного прайса:

Автоматическую выборку реализует нам формула, которая будет обладать следующей структурой:

ИНДЕКС(диапазон_данных_для_выборки;МИН(ЕСЛИ(диапазон=МИН(диапазон);СТРОКА(диапазон)-СТРОКА(заголовок_столбца);””)))

В месте «диапазон_данных_для_выборки» следует указать область значений A6:A18 для выборки из таблицы (например, текстовых), из которых функция ИНДЕКС выберет одно результирующие значение. Аргумент «диапазон» означает область ячеек с числовыми значениями, из которых следует выбрать первое наименьшее число. В аргументе «заголовок_столбца» для второй функции СТРОКА, следует указать ссылку на ячейку с заголовком столбца, который содержит диапазон числовых значений.

Естественно эту формулу следует выполнять в массиве. Поэтому для подтверждения ее ввода следует нажимать не просто клавишу Enter, а целую комбинацию клавиш CTRL+SHIFT+Enter. Если все сделано правильно в строке формул появятся фигурные скобки.

Обратите внимание ниже на рисунок, где в ячейку B3 была введена данная формула в массиве:

Выборка соответственного значения с первым наименьшим числом:

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



Как работает выборка по условию

Ключевую роль здесь играет функция ИНДЕКС. Ее номинальное задание – это выбирать из исходной таблицы (указывается в первом аргументе – A6:A18) значения соответственные определенным числам. ИНДЕКС работает с учетом критериев определённых во втором (номер строки внутри таблицы) и третьем (номер столбца в таблице) аргументах. Так как наша исходная таблица A6:A18 имеет только 1 столбец, то третий аргумент в функции ИНДЕКС мы не указываем.

Чтобы вычислить номер строки таблицы напротив наименьшего числа в смежном диапазоне B6:B18 и использовать его в качестве значения для второго аргумента, применяется несколько вычислительных функций.

Функция ЕСЛИ позволяет выбрать значение из списка по условию. В ее первом аргументе указано где проверяется каждая ячейка в диапазоне B6:B18 на наличие наименьшего числового значения: ЕСЛИB6:B18=МИНB6:B18. Таким способом в памяти программы создается массив из логических значений ИСТИНА и ЛОЖЬ. В нашем случаи 3 элемента массива будут содержат значение ИСТИНА, так как минимальное значение 8 содержит еще 2 дубликата в столбце B6:B18.

Следующий шаг – это определение в каких именно строках диапазона находится каждое минимальное значение. Это нам необходимо по причине определения именно первого наименьшего значения. Реализовывается данная задача с помощью функции СТРОКА, она заполняет элементы массива в памяти программы номерами строк листа. Но сначала от всех этих номеров вычитается номер на против первой строки таблицы – B5, то есть число 5. Это делается потому, что функция ИНДЕКС работает с номерами внутри таблицы, а не с номерами рабочего листа Excel. В тоже время функция СТРОКА умеет возвращать только номера строк листа. Чтобы не получилось смещение необходимо сопоставить порядок номеров строк листа и таблицы с помощи вычитанием разницы. Например, если таблица находится на 5-ой строке листа значит каждая строка таблицы будет на 5 меньше чем соответственная строка листа.

После того как будут отобраны все минимальные значения и сопоставлены все номера строк таблицы функция МИН выберет наименьший номер строки. Эта же строка будет содержать первое наименьшее число, которое встречается в столбце B6:B18. На основании этого номера строки функции ИНДЕКС выберет соответствующее значение из таблицы A6:A18. В итоге формула возвращает это значение в ячейку B3 в качестве результата вычисления.

Как выбрать значение с наибольшим числом в Excel

Поняв принцип действия формулы, теперь можно легко ее модифицировать и настраивать под другие условия. Например, формулу можно изменить так, чтобы выбрать первое максимальное значение в Excel:

Если необходимо изменить условия формулы так, чтобы можно было в Excel выбрать первое максимальное, но меньше чем 70:

Как в Excel выбрать первое минимальное значение кроме нуля:

Как легко заметить, эти формулы отличаются между собой только функциями МИН и МАКС и их аргументами.

Теперь Вас ни что не ограничивает. Один раз разобравшись с принципами действия формул в массиве Вы сможете легко модифицировать их под множество условий и быстро решать много вычислительных задач.

Макрос представляет собой последовательность действий, которая записана и сохранена для дальнейшего использования. Сохраненный макрос можно воспроизвести по специальной команде. Другими словами, вы можете записать свои действия в макросе, сохранить его, а затем разрешить другим пользователям воспроизводить сохраненные в макросе действия простым нажатием клавиши. Это особенно удобно при распространении отчетов сводных таблиц.

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

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

Скачать заметку в формате или , скачать с примерами (внутри файл 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.

Суть запроса на выборку – выбрать из исходной таблицы строки, удовлетворяющие определенным критериям (подобно применению стандартного ). Произведем отбор значений из исходной таблицы с помощью . В отличие от применения (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)))

Остальная часть формулы аналогична рассмотренным выше.