Разделы сайта
Выбор редакции:
- Вывод части изображения html спрайт
- Настройка дополнительных реквизитов и дополнительных сведений номенклатуры 1с дополнительные реквизиты и сведения отличия
- Что делать, когда нет регистрационных данных
- Запрос на выборку данных (формулы) в MS EXCEL Эксель выборка по условию макрос
- Временная временная одноразовая электронная почта Temp Email, mail sites, регистрация в социальных сетях
- Что делать, если компьютер не видит телефон через USB-порт
- Как установить Windows на Mac?
- Asus rt n16 настройка прошивка
- Как узнать разрядность операционной системы и процессора в Windows
- Как выключить брандмауэр Windows: полная деактивация и отключение для отдельных программ Как полностью отключить брандмауэр windows 7
Реклама
Выборка данных в Microsoft Excel. Запрос на выборку данных (формулы) в MS 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 впишем формулу СЛЧИС (). Размножим ее на весь столбец: Теперь копируем столбец со случайными числами и вставляем его в столбец В. Это нужно для того, чтобы эти числа не менялись при внесении новых данных в документ. Чтобы вставились значения, а не формула, щелкаем правой кнопкой мыши по столбцу В и выбираем инструмент «Специальная вставка». В открывшемся окне ставим галочку напротив пункта «Значения»: Теперь можно отсортировать данные в столбце В по возрастанию или убыванию. Порядок представления исходных значений тоже изменится. Выбираем любое количество строк сверху или снизу – получим случайную выборку. Первый способ: Применение расширенного автофильтра На листе 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; Вместо ENTER нажмите сочетание клавиш CTRL+SHIFT+ENTER (формула массива будет ). E11:E19 (столбец Цена) куда и введем аналогичную : ИНДЕКС(B11:B19; В результате получим новую таблицу, которая будет содержать только товары, у которых цены не меньше, указанной в ячейке Е6 . Чтобы показать динамизм полученного Запроса на выборку, введем в Е6 значение 55. В новую таблицу попадет только 2 записи. Если в Исходную таблицу добавить новый товар с Ценой 80, то в новую таблицу автоматически будет добавлена новая запись. Примечание . Также для вывода отфильтрованных данных можно использовать и . Выбор конкретного инструмента зависит от стоящей перед пользователем задачи. Если Вам не удобно использовать формулу массива , которая возвращает несколько значений, то можно использовать другой подход, который рассмотрен в разделах ниже: 5.а, 7, 10 и 11. В этих случаях используются . 2. Два числовых критерия (Выбрать те Товары, у которых цена попадает в диапазон)Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист Диапазон Чисел ). Критерии (нижнюю и верхнюю границы цены) разместим в диапазоне Е5:Е6 . Т.е. если Цена Товара попадает в указанный интервал, то такая запись появится в новой таблице Отфильтрованные данные. В отличие от предыдущей задачи создадим два : Товары и Цены (без них можно обойтись, но они удобны при написании формул). Соответствующие формулы должны выглядеть в Диспетчере имен (Формулы/ Определенные имена/ Диспетчер имен ) следующим образом (см. рисунок ниже). Теперь выделим диапазон D11:D19 и в введем : ИНДЕКС(Товары; Вместо ENTER нажмите сочетание клавиш CTRL+SHIFT+ENTER . Те же манипуляции произведем с диапазоном E11:E19 куда и введем аналогичную : ИНДЕКС(Цены; В результате получим новую таблицу, которая будет содержать только товары, у которых цены попадают в интервал, указанный в ячейках Е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; Примечание : После ввода формулы вместо клавиши 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) задает оба условия (Товар и Месяц). Выражение СТРОКА(ДВССЫЛ("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) гарантирует, что будут отобраны товары только заданных видов из желтых ячеек (Товар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="(Все)"; Остальная часть формулы аналогична рассмотренным выше. При работе с таблицами Excel довольно часто приходится проводить отбор в них по определенному критерию или по нескольким условиям. В программе сделать это можно различными способами при помощи ряда инструментов. Давайте выясним, как произвести выборку в Экселе, используя разнообразные варианты. Выборка данных состоит в процедуре отбора из общего массива тех результатов, которые удовлетворяют заданным условиям, с последующим выводом их на листе отдельным списком или в исходном диапазоне. Способ 1: применение расширенного автофильтраНаиболее простым способом произвести отбор является применение расширенного автофильтра. Рассмотрим, как это сделать на конкретном примере.
При использовании любого из двух вышеуказанных методов фильтрация будет удалена, а результаты выборки – очищены. То есть, в таблице будет показан весь массив данных, которыми она располагает. Способ 2: применение формулы массиваСделать отбор можно также применив сложную формулу массива. В отличие от предыдущего варианта, данный метод предусматривает вывод результата в отдельную таблицу.
Теперь у нас имеется готовая выборка по указанному ограничению в отдельной надлежащим образом оформленной таблице. Способ 3: выборка по нескольким условиям с помощью формулыТак же, как и при использовании фильтра, с помощью формулы можно осуществлять выборку по нескольким условиям. Для примера возьмем всю ту же исходную таблицу, а также пустую таблицу, где будут выводиться результаты, с уже выполненным числовым и условным форматированием. Установим первым ограничением нижнюю границу отбора по выручке в 15000 рублей, а вторым условием верхнюю границу в 20000 рублей. Способ 4: случайная выборкаВ Экселе с помощью специальной формулы СЛЧИС можно также применять случайный отбор. Его требуется производить в некоторых случаях при работе с большим объемом данных, когда нужно представить общую картину без комплексного анализа всех данных массива. Как видим, выборку в таблице Excel можно произвести, как с помощью автофильтра, так и применив специальные формулы. В первом случае результат будет выводиться в исходную таблицу, а во втором – в отдельную область. Имеется возможность производить отбор, как по одному условию, так и по нескольким. Кроме того, можно осуществлять случайную выборку, использовав функцию СЛЧИС . Если приходиться работать с большими таблицами определенно найдете в них дублирующийся суммы разбросаны вдоль целого столбца. В тоже время у вас может возникнуть необходимость выбрать данные из таблицы с первым наименьшим числовым значением, которое имеет свои дубликаты. Нужна автоматическая выборка данных по условию. В 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 выбрать первое минимальное значение кроме нуля: Как легко заметить, эти формулы отличаются между собой только функциями МИН и МАКС и их аргументами. Теперь Вас ни что не ограничивает. Один раз разобравшись с принципами действия формул в массиве Вы сможете легко модифицировать их под множество условий и быстро решать много вычислительных задач. |
Популярное:
Новое
- Настройка дополнительных реквизитов и дополнительных сведений номенклатуры 1с дополнительные реквизиты и сведения отличия
- Что делать, когда нет регистрационных данных
- Запрос на выборку данных (формулы) в MS EXCEL Эксель выборка по условию макрос
- Временная временная одноразовая электронная почта Temp Email, mail sites, регистрация в социальных сетях
- Что делать, если компьютер не видит телефон через USB-порт
- Как установить Windows на Mac?
- Asus rt n16 настройка прошивка
- Как узнать разрядность операционной системы и процессора в Windows
- Как выключить брандмауэр Windows: полная деактивация и отключение для отдельных программ Как полностью отключить брандмауэр windows 7
- Мощный конвертер HTML файлов в Doc, PDF, Excel, JPEG, Text Использование программы Total HTML Converter