Макрос выборки данных из таблицы. Запрос на выборку данных (формулы) в MS EXCEL. Случайная выборка в 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 скачиваний)
Статья помогла? Поделись ссылкой с друзьями! ВидеоурокиДля этого нужно открыть редактор Visual Basic Editor (комбинация клавиш «Alt+F11» или кликнуть правой кнопкой мыши на ярлык любого листа и выбрать «Исходный текст» или же в группе «Код» вкладки меню «Разработчик» щелкнуть по пункту «Visual Basic») и вставить в проект стандартный модуль. А в этот модуль добавить два кода программы (см. рисунок 8. ) – и .
Dim sheet As Worksheet Dim cell As Range With ActiveWorkbook For Each sheet In ActiveWorkbook.Worksheets Set cell = Worksheets(1).Cells(sheet.Index, 1) Worksheets(1).Hyperlinks.Add
anchor:=cell, Address:="",
cell.Formula = sheet.Name |
Как оптимизировать выборку нескольких искомых значений из разных таблиц в Excel
Оптимизировать выборку нескольких искомых значений (сумм оплаты по разным поставщикам) по одному параметру (например, по дате) из небольшого количества таблиц можно практически по той же схеме, что и с той лишь разницей, что на листе «Оглавление книги» будет отражен и список названий таблиц, участвующих в выборке и ее результаты, а кроме того, будет применяться немного другая формула поиска по таблицам:
«=ВПР($C$1;ДВССЫЛ(A2);2;ЛОЖЬ)», где:
- ячейка C1 (номер квартала) – задает значение параметра;
- «ДВССЫЛ(А2)» – определяет текстовую ссылку на именованный диапазон, название которого находится в ячейке А2;
- «2» – это номер столбца исходных таблиц поставщиков, в которых находятся необходимые нам суммы оплаты;
- «ЛОЖЬ» (можно заменить на 0) – указывает функции ВПР, на то, что требуется найти точное соответствие.
Первый способ: Применение расширенного автофильтра
На листе 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.
Если нужно сделать целый столбец случайных чисел, тогда устанавливаете курсор в нижний правый угол ячейки с содержащейся формулой. Должен появиться маркер заполнения, который следует протянуть вниз с помощью зажатия левой кнопки мыши. Делается это параллельно таблице с данными и до конца.
Диапазон ячеек будет содержать в себе формулу СЛЧИС, но работать с чистыми значениями не нужно. Копируете в пустой столбец справа и выделяете диапазон ячеек со случайными числами. Во вкладке «Главная» нажимаете на «Копировать».
Выделяете пустой столбец и нажимаете правой кнопкой мыши, чтобы вызвать контекстное меню. В группе инструментов «Параметры вставки» выбираете «Значения» (изображен в виде пиктограммы с цифрами).
Во вкладке «Главная» нажимаете на «Сортировка и фильтр», а затем «Настраиваемая сортировка».
Рядом с параметром «Мои данные содержат заголовки» ставите галочку. В строке «Сортировать по» указываете название того столбца, в котором находятся скопированные значения случайных чисел. В строке «Сортировка» настройки остаются по умолчанию. В строке «Порядок» выбираете параметр «По возрастанию» или «По убыванию». Кликаете на ОК.
Значения таблицы должны выстроиться в порядке возрастания или убывания случайных чисел. Любое количество первых строчек из таблицы можно взять и считать их результатом случайной выборки.
Если приходиться работать с большими таблицами определенно найдете в них дублирующийся суммы разбросаны вдоль целого столбца. В тоже время у вас может возникнуть необходимость выбрать данные из таблицы с первым наименьшим числовым значением, которое имеет свои дубликаты. Нужна автоматическая выборка данных по условию. В 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 можно осуществлять выборку определенных данных из диапазона в случайном порядке, по одному условию или нескольким. Для решения подобных задач используются, как правило, формулы массива или макросы. Рассмотрим на примерах.
Как сделать выборку в Excel по условию
При использовании формул массива отобранные данные показываются в отдельной таблице. В чем и состоит преимущество данного способа в сравнении с обычным фильтром.
Исходная таблица:
Сначала научимся делать выборку по одному числовому критерию. Задача – выбрать из таблицы товары с ценой выше 200 рублей. Один из способов решения – применение фильтрации. В результате в исходной таблице останутся только те товары, которые удовлетворяют запросу.
Другой способ решения – использование формулы массива. Соответствующие запросу строки поместятся в отдельный отчет-таблицу.
Сначала создаем пустую таблицу рядом с исходной: дублируем заголовки, количество строк и столбцов. Новая таблица занимает диапазон Е1:G10.Теперь выделяем Е2:Е10 (столбец «Дата») и вводим следующую формулу: {}.
Чтобы получилась формула массива, нажимаем сочетание клавиш Ctrl + Shift + Enter. В соседний столбец – «Товар» - вводим аналогичную формулу массива: {}. Изменился только первый аргумент функции ИНДЕКС.
В столбец «Цена» введем такую же формулу массива, изменив первый аргумент функции ИНДЕКС.
В результате получаем отчет по товарам с ценой больше 200 рублей.
Такая выборка является динамичной: при изменении запроса или появлении в исходной таблице новых товаров, автоматически поменяется отчет.
Задача №2 – выбрать из исходной таблицы товары, которые поступили в продажу 20.09.2015. То есть критерий отбора – дата. Для удобства искомую дату введем в отдельную ячейку, I2.
Для решения задачи используется аналогичная формула массива. Только вместо критерия }.
Подобные формулы вводятся и в другие столбцы (принцип см. выше).
Теперь используем текстовый критерий. Вместо даты в ячейку I2 введем текст «Товар 1». Немного изменим формулу массива: {}.
Такая большая функция выборки в Excel.
Выборка по нескольким условиям в Excel
Сначала возьмем два числовых критерия:
Задача – отобрать товары, которые стоят меньше 400 и больше 200 рублей. Объединим условия знаком «*». Формула массива выглядит следующим образом: {}.
Это для первого столбца таблицы-отчета. Для второго и третьего – меняем первый аргумент функции ИНДЕКС. Результат:
Чтобы сделать выборку по нескольким датам или числовым критериям, используем аналогичные формулы массива.
Случайная выборка в Excel
Когда пользователь работает с большим количеством данных, для последующего их анализа может потребоваться случайная выборка. Каждому ряду можно присвоить случайный номер, а затем применить сортировку для выборки.
Исходный набор данных:
Сначала вставим слева два пустых столбца. В ячейку А2 впишем формулу СЛЧИС (). Размножим ее на весь столбец:
Теперь копируем столбец со случайными числами и вставляем его в столбец В. Это нужно для того, чтобы эти числа не менялись при внесении новых данных в документ.
Чтобы вставились значения, а не формула, щелкаем правой кнопкой мыши по столбцу В и выбираем инструмент «Специальная вставка». В открывшемся окне ставим галочку напротив пункта «Значения»:
Теперь можно отсортировать данные в столбце В по возрастанию или убыванию. Порядок представления исходных значений тоже изменится. Выбираем любое количество строк сверху или снизу – получим случайную выборку.