Top-office11.ru

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

Excel выборка из таблицы по условию

Excel выборка из таблицы по условию

Модератор форума: _Boroda_, Manyasha, SLAVICK, китин
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выборка данных из таблицы по условию и заполнение ячеек (Сводные таблицы/Pivot Table)

Выборка данных из таблицы по условию и заполнение ячеек

mettuonДата: Четверг, 23.04.2015, 09:27 | Сообщение № 1
slAvIk159Дата: Четверг, 23.04.2015, 09:43 | Сообщение № 2
mettuonДата: Четверг, 23.04.2015, 09:46 | Сообщение № 3

slAvIk159, Афигеть. ))
Спасибо большое)) Все круто.

Спасибо создателям форума!
Буду обращаться! ехуу!!

slAvIk159, Афигеть. ))
Спасибо большое)) Все круто.

Спасибо создателям форума!
Буду обращаться! ехуу!! mettuon

Сообщение slAvIk159, Афигеть. ))
Спасибо большое)) Все круто.

Спасибо создателям форума!
Буду обращаться! ехуу!! Автор — mettuon
Дата добавления — 23.04.2015 в 09:46

_Boroda_Дата: Четверг, 23.04.2015, 09:47 | Сообщение № 4

Скажи мне, кудесник, любимец ба’гов.
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

mettuonДата: Четверг, 23.04.2015, 10:00 | Сообщение № 5

Спасибо большое _Boroda_,
Для меня эта формула сложнее чем та(

кстате почему то если имена листов к примеру («1 жилое», либо «2.1 общественные») то не работает( пришет #ссылка

Спасибо большое _Boroda_,
Для меня эта формула сложнее чем та(

кстате почему то если имена листов к примеру («1 жилое», либо «2.1 общественные») то не работает( пришет #ссылка mettuon

Сообщение Спасибо большое _Boroda_,
Для меня эта формула сложнее чем та(

кстате почему то если имена листов к примеру («1 жилое», либо «2.1 общественные») то не работает( пришет #ссылка Автор — mettuon
Дата добавления — 23.04.2015 в 10:00

Выборка значений из таблицы 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:

=70;»»;B6:B18));СТРОКА(B6:B18)-СТРОКА(B5);»»)))’ >

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

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

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

Выборка данных в Excel (как сделать из таблицы массива по условию)

Первый способ: Применение расширенного автофильтра

На листе 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

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

Здравствуйте, друзья. Известные нам функции поиска данных, такие, как ВПР и ПОИСКПОЗ, ищут в массиве первое совпадение и возвращают его. Во многих случаях это и требуется, но что делать, когда следует получить все результаты, а не только первый, и искать их по нескольким критериям, а не по одному?

В этой статье мы решим такую задачу. Описываю пример. Есть ведомость школьных оценок 1-3 классов. Мы хотим выбрать класс, оценку, и увидеть учеников класса, которые получили этот балл:

Задача интересная, и достаточно простая. Однако, нам понадобится сделать дополнительный столбец. Действуем в несколько этапов.

Создание вспомогательной колонки

Сейчас необходимо добавить справа от ведомости еще один столбец. Назовём его «технический», т.к. данные в нем будут неинформативными, вспомогательными.

В этой колонке пронумеруем строки так, чтобы напротив каждой строчки, удовлетворяющей условию поиска, стоял её порядковый номер в таблице-выборке.

Смотрите на рисунке, чего мы хотим добиться. Ищем всех учеников класса, которые получили 5. Первый из списка, кто получил такую оценку – Соколов. Напротив него нужно поставить единичку. Второй в списке – Козлов, даём ему второй номер по порядку, третий – Волков, ему ставим третий номер и т.д. Под этими номерами они потом попадут в табличку с выборкой.

Формулу укажу на примере ячейки D3:

Функция СУММ для каждой строки сложит значение из предыдущей ячейки, а так же, результат сравнения класса в ведомости с искомым, оценки в ведомости с искомыми. Два уточнения:

  • В ячейке D2 – текст, и функция СУММ его проигнорирует. В остальных строчках будет браться число из ячейки сверху;
  • ФОРМУЛА И(A3=$L$2;C3=$L$3) вернёт ИСТИНУ (единицу), когда оба условия внутри неё выполнятся, или ЛОЖЬ (ноль), если хотя бы одно из условий не выполнится. Таким образом, когда функция И найдет совпадение класса и оценки, СУММ увеличит порядковый номер на единицу, что нам и требовалось

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

Вывод отобранных строк в таблицу

Теперь осталось вывести на экран выборку учеников класса с нужной оценкой. Для этого сопоставим номера в столбце F с номерами в технической колонке D. Удобнее всего это сделать с помощью комбинации функций ИНДЕКС и ПОИСКПОЗ:

Алгоритм работы на примере ячейки G3:

  1. Функция ПОИСКПОЗ получает числовой индекс строки из ячейки F3 и ищет такое же значение в техническом столбце D. Возвращает либо порядковый номер найденной ячейки в массиве, либо ошибку #Н/Д
  2. Функция ИНДЕКС получает из ведомости класс, находящийся в ячейке, порядковый номер которой мы получили в первом пункте
  3. Далее функция ЕСЛИОШИБКА выводит на экран класс, если он найден, или пустую строку, если ПОИСКПОЗ вернула ошибку

Такой порядок работает и для остальных столбцов: «Фамилия», «Баллы». Теперь если изменить искомый класс или оценку, формулы сами всё пересчитают и переформируют список. Можно скрыть дополнительный столбец, чтобы не мешал.

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

Мы получили полностью автоматизированный интерактивный проект, способный формировать списки «налету», сразу после того, как вы сделаете свой выбор. На этом всё, и я готов отвечать на ваши вопросы!

Читать еще:  Копировать лист excel в другую книгу
Ссылка на основную публикацию
Adblock
detector