Выборка в Excel из списка: как ее сделать, настроить и использовать

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

На листе Excel необходимо выделить область, среди данных, которых и нужно осуществить выборку. Во вкладке «Главная» нажимаете «Сортировка и фильтр» (находится в блоке настроек «Редактирование»). Далее нажимаете на фильтр для того, чтобы сделать подходящую выборку в Excel.

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

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

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

Как использовать функцию

Пример №1

На скриншотах ниже показан самый просто вариант использования. Мы сначала проверяем заданное условие А1>25. ЕСЛИ это требование выполняется, тогда выводим в ячейку «больше 25», иначе «меньше или равно 25».

Пример №2

Теперь более сложное задание. Ниже мы рассмотрим пример переаттестации сотрудников предприятия. Изначально таблица выглядит так:

Нам необходимо в столбце С вывести результаты переаттестации, которые могут принимать бинарное значение: СДАЛ или НЕ СДАЛ. Критерии у нас будут такими: кто набрал более 45 балов, тот считается сдавшим экзамен, ну а все остальные нет.

Читайте так же:

Как вернуть ленту новостей в яндекс браузере

Для выполнения задачи необходимо:

Выбрать ту ячейку, в которой будем в водить формулу. У нас это С3.
Затем определяемся с необходимыми параметрами:
- Логическое_условие – B3>45
- Значение_в_случае_ИСТИНА – Сдал
- Значение_в_случае_ЛОЖЬ – не сдал
Таким образом, получаем формулу =ЕСЛИ (В3>45; «Сдал»; «Не сдал»)
Формула будет сравнивать число в ячейке В3 и 45. Если наше требование выполняется и Баллы больше 45, то в ячейке результата мы увидим «Сдал», иначе «Не сдал».
Копируем формулу в каждую ячейку удобным способом, и смотрим результат.

Распространенный вопрос по Excel «Как записывать несколько условий в одной формуле?». Особенно часто применяется два и более условий при использовании функции ЕСЛИ. Сделать несколько условий в формуле ЕСЛИ довольно просто, главное знать основные принципы. Их и обсудим ниже.

На мой взгляд, показательно рассмотреть пример с решением системы условий. Такие задания часто задают в институтах, на парах по Excel.

Например, есть вот такая довольно нагроможденная формула:

Разберем на примере, как перенести ее в Excel

Как записать несколько условий в формуле ЕСЛИ?

Понятно, что эта формула будет состоять из 3 частей, как минимум:

Об экспоненте можно подробно прочитать здесь .

Но как записать несколько этих функций в одну, еще и по условию? Для того чтобы разобраться, подробно посмотрим на функцию ЕСЛИ.

Ее состав следующий:

=ЕСЛИ(Условие;если условие = ДА (ИСТИНА);если условие = НЕТ (ЛОЖЬ))

Т.е. если мы запишем простую формулу, что мы получим в итоге в ячейке B2?

Верно — отобразится 100. Если же в А1 будет стоять любое другое значение кроме 1, то в B2 отобразится бы 0.

Вернемся к нашей системе условий. Теперь нам надо понимать, как записать сразу два условия до первой точки с запятой. У нас в B1 пусто, а значит = 0, и только при выполнении обоих условий А1=1 и B1=0 (знак *) значение формулы будет равно 100.

Читайте так же:

Как в excel строку разбить на столбцы

Особо разберем * между скобками

Оператор И он же * означает, что должно выполняться оба условия одновременно, А1=1 и B1=0.
Если между скобками поставить + (или), то достаточно будет одного из условий. Например, только если А1=1, то уже будет отображаться 100.

Мы готовы к написанию формулы, будем это делать по частям

Запишем первое условие

Если условие выполняется, то выполняется первая формула с синусом
Если нет, второе условие

Во всех же остальных случаях будет выполнятся формула =EXP(1/B1)
Итого получается:

Запись нескольких формул в одной

Если в ячейки B1 будет текст, то формула выдаст ошибку. Поэтому я часто применяю формулу =ЕСЛИОШИБКА() .

Представим, что вся наша формула из предыдущего пункта — это один условный аргумент А

Или для нашего примера

Пример можно скачать здесь

Как красиво записать несколько формул

Иногда бывает более 20 Если, тут легко и запутаться… Поэтому существует удобный способ, как записать много формул в одну и удобно отобразить ее в несколько строк. И по прошествии времени разбираться в этой формуле будет легче. С описанием этой отличной возможности мы написали целую статью. Подробно можно прочитать здесь

Функция ДВССЫЛ

Преобразует адрес ссылки, заданный текстовой строкой, в ссылку на данный адрес.

Синтаксис: =ДВССЫЛ(адрес_ссылки; [стиль_ссылки]), где

адрес_ссылки – обязательный аргумент. Строка, представляющая адрес ссылки на ячейку или диапазон. Например, «C3», «R3C3» или «D8:D9».
стиль_ссылки – необязательный аргумент. Логическое значение, определяющее стиль ссылки:
- ИСТИНА – стиль A1. Является значением по умолчанию;
- ЛОЖЬ – стиль R1C1.
=ДВССЫЛ(«a3») – возвращает ссылку на ячейку A3.
=ДВССЫЛ(«r3c3») – вернет ошибку #ССЫЛКА!, так как текст для ссылки в формате R1C1, а второй аргумент имеет значение по умолчанию.
=ДВССЫЛ(«r3c3»; ЛОЖЬ) – возвращает ссылку на ячейку C3.
=ДВССЫЛ(АДРЕС(СТРОКА(C3);СТОЛБЕЦ(C3))) – функция вернет аналогичный предыдущему примеру результат.
Вложение функции ДВССЫЛ со ссылкой на диапазон:
Как посчитать процент от суммы
Тоже довольно распространенный вопрос, с которым часто сталкиваюсь. Вообще, насколько я себе представляю, возникает он чаще всего — из-за того, что люди путаются и не знают, что от чего ищут процент (да и вообще, плохо понимают тему процентов (хотя я и сам не большой математик, и все таки. ☝) ).
👉 В помощь!
Как посчитать проценты: от числа, от суммы чисел и др. [в уме, на калькуляторе и с помощью Excel] — заметка для начинающих
Самый простой способ, в котором просто невозможно запутаться — это использовать правило «квадрата», или пропорции.
Вся суть приведена на скрине ниже: если у вас есть общая сумма, допустим в моем примере это число 3060 — ячейка F8 (т.е. это 100% прибыль, и какую то ее часть сделал «Саша», нужно найти какую. ).
По пропорции формула будет выглядеть так: =F10*G8/F8 (т.е. крест на крест: сначала перемножаем два известных числа по диагонали, а затем делим на оставшееся третье число).
В принципе, используя это правило, запутаться в процентах практически невозможно 👌.
Пример решения задач с процентами
PS
Собственно, на этом я завершаю данную статью. Не побоюсь сказать, что освоив все, что написано выше (а приведено здесь всего лишь «пяток» формул) — Вы дальше сможете самостоятельно обучаться Excel, листать справку, смотреть, экспериментировать, и анализировать. 👌
Скажу даже больше, все что я описал выше, покроет многие задачи, и позволит решать всё самое распространенное, над которым часто ломаешь голову (если не знаешь возможности Excel) , и даже не догадывается как быстро это можно сделать. ✔
Синтаксис функции и принцип работы
О возможностях сложения известно каждому пользователю, хоть раз запускавшего Excel. СУММЕСЛИ – логичное продолжение базовой СУММ, отличие которой заключается в суммировании по условию.
Синтаксис СУММЕСЛИ выглядит следующим образом:
=СУММЕСЛИ(диапазон; критерий; [диапазон суммирования]), где:
- диапазон – список значений, к которым будет применено ограничение;
- критерий – конкретное условие;
- диапазон суммирования – список значений, которые будут суммироваться.
Итак, имеем функцию с тремя компонентами. Стоит отметить, что последний аргумент можно не использовать – СУММЕСЛИ способен работать без диапазона суммирования.
Примеры применения
Пример 1
Как правило, если слово состоит из только гласных или только согласных букв — это аббревиатура
Воспользуемся комбинацией — функция ИЛИ + функция ЕЧИСЛО + функция ПОИСК, чтобы определить на основе этого признака, что слово является аббревиатурой
Это поможет нам далее исправить регистр слова функцией ПРОПИСН, если оно изначально написано в нижнем.
Составим формулы массива для поиска гласных и согласных букв.
ИЛИ с формулой массива
Теперь составим общую формулу проверки.
Для того, чтобы соблюдалось условие аббревиатуры, условия наличия гласных и согласных должны противоречить друг другу
Т.е. есть гласные и нет согласных, или, наоборот, есть согласные, но нет гласных.
Самым коротким способом записать это можно функцией НЕ.
Составная формула для поиска аббревиатур
Пример 2
Студенты получают оценки в виде букв A-F, каждая из букв может быть с плюсом. «F» и «F+» — неудовлетворительные оценки. Задача с помощью одной формулы узнать, нужна ли студенту пересдача.
Еще одна формула массива с функцией ИЛИ.
Почему фигурные скобки в формуле присутствуют дважды?
Используется массив внутри формулы массива — таблица оценок студента сравнивается с массивом строковых констант «F» и «F+».
Столбец оценок — вертикальный, а массив строковых констант — горизонтальный. При сравнении этих двух массивов (в скобках) , возвращается массив булевых значений размерностью 5 на 2.
Функция ИЛИ возвращает ИСТИНА, если в этом массиве есть хотя бы одна неудовлетворительная оценка.
голоса
Рейтинг статьи