Excel как выбрать строку по значениям в столбцах - Word и Excel

ИНДЕКС ПОИСКПОЗ как лучшая альтернатива ВПР

В этом руководстве показано, как использовать ИНДЕКС и ПОИСКПОЗ в Excel и чем они лучше ВПР.

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

Краткий обзор функций ИНДЕКС и ПОИСКПОЗ
Как использовать формулу ИНДЕКС ПОИСКПОЗ
ИНДЕКС+ПОИСКПОЗ вместо ВПР?
Поиск справа налево
Двусторонний поиск в строках и столбцах
ИНДЕКС ПОИСКПОЗ для поиска по нескольким условиям
Как найти среднее, максимальное и минимальное значение
Что делать с ошибками поиска?

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

Функции Excel ИНДЕКС и ПОИСКПОЗ — основы

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

Функция ИНДЕКС

Функция ИНДЕКС (в английском варианте – INDEX) возвращает значение в массиве на основе указанных вами номеров строк и столбцов. Синтаксис функции ИНДЕКС прост:

ИНДЕКС(массив,номер_строки,[номер_столбца])

Вот простое объяснение каждого параметра:

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

Дополнительные сведения см. в статье Функция ИНДЕКС в Excel .

А вот пример формулы ИНДЕКС в самом простом виде:

=ИНДЕКС(A1:C10;2;3)

Формула выполняет поиск в ячейках с A1 по C10 и возвращает значение ячейки во 2-й строке и 3-м столбце, т. е. в ячейке C2.

Очень легко, правда? Однако при работе с реальными данными вы вряд ли когда-нибудь будете заранее знать, какие строки и столбцы вам нужны. Здесь вам пригодится ПОИСКПОЗ.

Функция ПОИСКПОЗ

Она ищет нужное значение в диапазоне ячеек и возвращает относительное положение этого значения в диапазоне.

Синтаксис функции ПОИСКПОЗ следующий:

ПОИСКПОЗ(искомое_значение, искомый_массив, [тип_совпадения])

искомое_значение — числовое или текстовое значение, которое вы ищете.
диапазон_поиска — диапазон ячеек, в которых будем искать.
тип_совпадения — указывает, следует ли искать точное соответствие или наиболее близкое совпадение:
- 1 или опущено — находит наибольшее значение, которое меньше или равно искомому значению. Требуется сортировка массива поиска в порядке возрастания.
- 0 — находит первое значение, точно равное искомому значению. В комбинации ИНДЕКС/ПОИСКПОЗ вам почти всегда нужно точное совпадение, поэтому вы чаще всего устанавливаете третий аргумент вашей функции в 0.
- -1 — находит наименьшее значение, которое больше или равно искомому значению. Требуется сортировка массива поиска в порядке убывания.

Например, если диапазон B1:B3 содержит значения «яблоки», «апельсины», «лимоны», приведенная ниже формула возвращает число 3, поскольку «лимоны» — это третья по счету запись в этом диапазоне:

=ПОИСКПОЗ(«лимоны»;B1:B3;0)

Дополнительные сведения см . в статье Функция ПОИСКПОЗ в Excel .

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

Однако, относительная позиция искомого значения (т. е. номера строки и столбца, в которых оно находится) — это именно то, что нам нужно указать для аргументов номер_строки и номер_столбца функции ИНДЕКС. Как вы помните, ИНДЕКС может найти значение на пересечении заданной строки и столбца, но сама не может определить, какую именно строку и столбец ей нужно выбрать.

Вот поэтому совместное использование ИНДЕКС и ПОИСКПОЗ открывает перед нами массу возможностей для поиска в Excel.

Как использовать формулу ИНДЕКС ПОИСКПОЗ в Excel

Теперь, когда вы знаете основы, я считаю, что вы уже начали понимать, как ПОИСКПОЗ и ИНДЕКС работают вместе. Короче говоря, ИНДЕКС извлекает нужное значение по номерам столбцов и строк, а ПОИСКПОЗ предоставляет ей эти номера. Вот и все!

Для вертикального поиска вы используете функцию ПОИСКПОЗ только для определения номера строки, указывая диапазон столбцов непосредственно в самой формуле:

ИНДЕКС ( столбец для возврата значения ; ПОИСКПОЗ ( искомое значение ; столбец для поиска ; 0))

Все еще не совсем понимаете эту логику? Возможно, будет проще разобрать на примере. Предположим, у вас есть список национальных столиц и их население:

Чтобы найти население определенной столицы, скажем, Индии, используйте следующую формулу ПОИСКПОЗ ИНДЕКС:

=ИНДЕКС(C2:C10; ПОИСКПОЗ(“Индия”;A2:A10;0))

Теперь давайте проанализируем, что на самом деле делает каждый компонент этой формулы:

Функция ПОИСКПОЗ ищет искомое значение «Индия» в диапазоне A2:A10 и возвращает число 2, поскольку это слово занимает второе место в массиве поиска.
Этот номер поступает непосредственно в аргумент номер_строки функции ИНДЕКС, предписывая вернуть значение из этой строки.

Таким образом, приведенная выше формула превращается в ИНДЕКС(C2:C10;2), которая означает, что нужно искать в ячейках от C2 до C10 и извлекать значение из второй ячейки в этом диапазоне, то есть из C3, потому что мы начинаем отсчет со второй строки.

Но указывать название города в формуле не совсем правильно, так как для каждого нового поиска придется корректировать эту формулу. Введите его в какую-нибудь отдельную ячейку, скажем, F1, укажите ссылку на ячейку для ПОИСКПОЗ, и вы получите формулу динамического поиска:

=ИНДЕКС(C2:C10;ПОИСКПОЗ(F1;A2:A10;0))

Важное замечание! Количество строк в аргументе массив функции ИНДЕКС должно совпадать с количеством строк в аргументе просматриваемый_массив в ПОИСКПОЗ, иначе формула выдаст неверный результат.

Вы спросите: «А почему бы нам просто не использовать обычную формулу ВПР? Какой смысл тратить время на то, чтобы разобраться в хитросплетениях ИНДЕКС ПОИСКПОЗ в Excel?»

Вот как это будет выглядеть:

=ВПР(F1; A2:C10; 3; 0)

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

ИНДЕКС+ПОИСКПОЗ вместо ВПР?

Решая, какую функцию использовать для вертикального поиска, большинство знатоков Excel сходятся во мнении, что ПОИСКПОЗ+ИНДЕКС намного лучше, чем ВПР. Однако многие до сих пор остаются с ВПР, во-первых, потому что это проще, а, во-вторых, потому что они не до конца понимают все преимущества использования формулы ПОИСКПОЗ ИНДЕКС в Excel. Без такого понимания никто не захочет тратить свое время на изучение более сложного синтаксиса.

Ниже я укажу на ключевые преимущества ИНДЕКС ПОИСКПОЗ перед ВПР, а уж вам решать, является ли это достойным дополнением к вашему арсеналу знаний в Excel.

4 основные причины использовать ИНДЕКС ПОИСКПОЗ вместо ВПР

Поиск справа налево. Как известно любому образованному пользователю, ВПР не может искать влево. Это означает, что искомое значение всегда должно находиться в крайнем левом столбце таблицы. А извлекать нужное значение мы будем из столбца, который находится правее. ИНДЕКС+ПОИСКПОЗ может легко выполнять поиск влево! Здесь это показано в действии: Как выполнить поиск значения слева в Excel .
Можно безопасно вставлять или удалять столбцы. Формулы ВПР не работают или выдают неверные результаты, когда новый столбец удаляется из таблицы поиска или добавляется в нее, поскольку синтаксис ВПР требует указания порядкового номера столбца, из которого вы хотите извлечь данные. Естественно, когда вы добавляете или удаляете столбцы, этот номер в формуле автоматически не меняется, а нужный столбец уже оказывается на новом месте.

С функциями ИНДЕКС и ПОИСКПОЗ вы указываете диапазон возвращаемых столбцов, а не номер одного из них. В результате вы можете вставлять и удалять столько столбцов, сколько хотите, не беспокоясь об обновлении каждой связанной с ними формулы.

Нет ограничений на размер искомого значения. При использовании функции ВПР общая длина ваших критериев поиска не может превышать 255 символов, иначе вы получите ошибку #ЗНАЧ!. Таким образом, если ваш набор данных содержит длинные строки, ИНДЕКС ПОИСКПОЗ — единственное работающее решение.
Более высокая скорость обработки. Если ваши таблицы относительно небольшие, вряд ли будет какая-то существенная разница в производительности Excel. Но если ваши рабочие листы содержат сотни или тысячи строк и, следовательно, сотни или тысячи формул, ИНДЕКС ПОИСКПОЗ будет работать намного быстрее, чем ВПР. Причина в том, что Excel будет обрабатывать только столбцы поиска и возврата, а не весь массив таблицы.

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

ИНДЕКС ПОИСКПОЗ в Excel – примеры формул

Уяснив, почему все же стоит изучать ИНДЕКС ПОИСКПОЗ, давайте перейдем к самому интересному и посмотрим, как можно применить теоретические знания на практике.

Формула для поиска справа налево

Как уже упоминалось, ВПР не может получать значения слева от столбца поиска. Таким образом, если ваши значения поиска не находятся в самом левом столбце, нет никаких шансов, что формула ВПР принесет вам желаемый результат. Функция ПОИСКПОЗ ИНДЕКС в Excel более универсальна и не имеет особого значения, где расположены столбцы поиска и возврата.

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

Записав искомое значение в G1, используйте следующую формулу для поиска в C2:C10 и возврата соответствующего значения из A2:A10:

=ИНДЕКС(A2:A10; ПОИСКПОЗ(G1;C2:C10;0))

Совет. Если вы планируете использовать формулу ПОИСКПОЗ ИНДЕКС более чем для одной ячейки, обязательно зафиксируйте оба диапазона абсолютными ссылками (например, $A$2:$A$10 и $C$2:$C$10), чтобы они не изменялись при копировании формулы.

Двусторонний поиск в строках и столбцах

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

Это может показаться сложным, но формула очень похожа на базовую функцию ПОИСКПОЗ ИНДЕКС в Excel, но с одним отличием.

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

ИНДЕКС(массив; ПОИСКПОЗ(значение_поиска1 ; столбец_поиска ; 0); ПОИСКПОЗ(значение_поиска2 ; столбец_поиска ; 0))

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

С целевой страной в G1 (значение_поиска1) и целевым годом в G2 (значение_поиска2) формула принимает следующий вид:

=ИНДЕКС(B2:D11; ПОИСКПОЗ(G1;A2:A11;0); ПОИСКПОЗ(G2;B1:D1;0))

Как работает эта формула?

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

ПОИСКПОЗ(G1;A2:A11;0); – ищет в A2:A11 значение из ячейки G1 («США») и возвращает его позицию, которая равна 3.

ПОИСКПОЗ(G2;B1:D1;0) – просматривает диапазон B1:D1, чтобы получить позицию значения из ячейки G2 («2015»), которая равна 3.

Найденные выше номера строк и столбцов становятся соответствующими аргументами функции ИНДЕКС:

ИНДЕКС(B2:D11, 3, 3)

В результате вы получите значение на пересечении 3-й строки и 3-го столбца в диапазоне B2:D11, то есть из D4. Несложно?

ИНДЕКС ПОИСКПОЗ для поиска по нескольким условиям

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

Вот общая формула ИНДЕКС ПОИСКПОЗ с несколькими критериями:

{=ИНДЕКС( диапазон_возврата; ПОИСКПОЗ (1; ( критерий1 = диапазон1 ) * ( критерий2 = диапазон2 ); 0))}

Примечание. Это формула массива , которую необходимо вводить с помощью сочетания клавиш Ctrl + Shift + Enter.

Предположим, что в таблице ниже вы хотите найти значение на основе двух критериев: Покупатель и Товар.

Следующая формула ИНДЕКС ПОИСКПОЗ отлично работает:

=ИНДЕКС(C2:C10; ПОИСКПОЗ(1; (F1=A2:A10) * (F2=B2:B10); 0))

Где C2:C10 — это диапазон, из которого возвращается значение, F1 — это критерий1, A2:A10 — это диапазон для сравнения с критерием 1, F2 — это критерий 2, а B2:B10 — это диапазон для сравнения с критерием 2.

Не забудьте правильно ввести формулу, нажав Ctrl + Shift + Enter, и Excel автоматически заключит ее в фигурные скобки, как показано на скриншоте ниже:

Рис5

Если вы не хотите использовать формулы массива, добавьте в формулу в F4 еще одну функцию ИНДЕКС и завершите ее ввод обычным нажатием Enter:

=ИНДЕКС(C2:C10; ПОИСКПОЗ(1; ИНДЕКС((F1=A2:A10) * (F2=B2:B10); 0; 1); 0))

Разберем пошагово, как это работает.

Здесь используется тот же подход, что и в обычном сочетании ИНДЕКС ПОИСКПОЗ, где просматривается один столбец. Чтобы оценить несколько критериев, вы создаете два или более массива значений ИСТИНА и ЛОЖЬ, которые представляют совпадения и несовпадения для каждого отдельного критерия, а затем перемножаете соответствующие элементы этих массивов. Операция умножения преобразует ИСТИНА и ЛОЖЬ в 1 и 0 соответственно и создает массив, в котором единицы соответствуют строкам, которые удовлетворяют всем условиям. Функция ПОИСКПОЗ со значением поиска 1 находит первую «1» в массиве и передает ее позицию в ИНДЕКС, которая возвращает значение в этой позиции из указанного столбца.

Вторая формула без массива основана на способности функции ИНДЕКС работать с массивами. Второй вложенный ИНДЕКС имеет 0 в номер_строки , так что он будет передавать весь массив столбцов в ПОИСКПОЗ.

Среднее, максимальное и минимальное значение при помощи ИНДЕКС ПОИСКПОЗ

Microsoft Excel имеет специальные функции для поиска минимального, максимального и среднего значения в диапазоне. Но что, если вам нужно получить значение из другой ячейки, связанной с этими значениями? Например, получить название города с максимальным населением или узнать товар с минимальными продажами? В этом случае используйте функцию МАКС , МИН или СРЗНАЧ вместе с ИНДЕКС ПОИСКПОЗ.

Максимальное значение.

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

=ИНДЕКС(B2:B10; ПОИСКПОЗ(МАКС(C2:C10); C2:C10; 0))

Скриншот с примером находится чуть ниже.

Минимальное значение

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

=ИНДЕКС(B2:B10; ПОИСКПОЗ(МИН(C2:C10); C2:C10; 0))

Ближайшее к среднему

Теперь мы находим город, население которого наиболее близко к среднему значению. Чтобы вычислить позицию, наиболее близкую к среднему значению показателя, рассчитанному из D2:D10, и получить соответствующее значение из столбца C, используйте следующую формулу:

=ИНДЕКС(B2:B10; ПОИСКПОЗ(СРЗНАЧ(C2:C10); C2:C10; -1 ))

В зависимости от того, как организованы ваши данные, укажите 1 или -1 для третьего аргумента (тип_совпадения) функции ПОИСКПОЗ:

Если ваш столбец поиска (столбец D в нашем случае) отсортирован по возрастанию , поставьте 1. Формула вычислит наибольшее значение, которое меньше или равно среднему значению.
Если ваш столбец поиска отсортирован по убыванию , введите -1. Формула вычислит наименьшее значение, которое больше или равно среднему значению.
Если ваш массив поиска содержит значение , точно равное среднему, вы можете ввести 0 для точного совпадения. Никакой сортировки не требуется.

В нашем примере данные в столбце D отсортированы в порядке убывания, поэтому мы используем -1 для типа соответствия. В результате мы получаем «Токио», так как его население (13 189 000) является ближайшим, превышающим среднее значение (12 269 006).

Что делать с ошибками поиска?

Как вы, наверное, заметили, если формула ИНДЕКС ПОИСКПОЗ в Excel не может найти искомое значение, она выдает ошибку #Н/Д. Если вы хотите заменить это стандартное сообщение чем-то более информативным, оберните формулу ПОИСКПОЗ ИНДЕКС в функцию ЕСНД . Например:

=ЕСНД(ИНДЕКС(C2:C10; ПОИСКПОЗ(F1;A2:A10;0)); «Не найдено»)

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

Если вы хотите перехватывать все ошибки, а не только #Н/Д, используйте функцию ЕСЛИОШИБКА вместо ЕСНД:

=ЕСЛИОШИБКА(ИНДЕКС(C2:C10; ПОИСКПОЗ(F1;A2:A10;0)); «Что-то пошло не так!»)

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

Итак, еще раз об основных преимуществах формулы ИНДЕКС ПОИСКПОЗ.

Возможен ли «левый» поиск?
Повлияет ли на результат вставка и удаление столбцов?

Вы можете вставлять и удалять столько столбцов, сколько хотите. На результат ИНДЕКС ПОИСКПОЗ это не повлияет.
Возможен ли поиск по строкам и столбцам?

Можно сначала найти подходящий столбец, а уж потом извлечь из него значение. Общий вид формулы:
ИНДЕКС(массив; ПОИСКПОЗ(значение_поиска1 ; столбец_поиска ; 0); ПОИСКПОЗ(значение_поиска2 ; столбец_поиска ; 0))
Подробную инструкцию смотрите здесь.
Как сделать поиск ИНДЕКС ПОИСКПОЗ по нескольким условиям?

Можно выполнять поиск по двум или более условиям без добавления дополнительных столбцов. Вот формула массива, которая решит проблему:
{=ИНДЕКС( диапазон_возврата; ПОИСКПОЗ (1; ( критерий1 = диапазон1 ) * ( критерий2 = диапазон2 ); 0))}

Вот как можно использовать ИНДЕКС и ПОИСКПОЗ в Excel. Я надеюсь, что наши примеры формул окажутся полезными для вас.

Вот еще несколько статей по этой теме:

Источник

Вывод отобранных значений в отдельный диапазон в Excel. Бесплатные примеры и статьи.

Смотрите также из тех, гдекарандаши=ИНДЕКС(A1:G13;ПОИСКПОЗ(C16;D1:D13;0);2) точках: же присваивает новый (постоянный)не тот, буду: Вторая лучше в данные – «Хлеб». дням.. Производим клик по«OK» указанной области будутследует ввести адресСЧЁТЕСЛИМН( диапазон поиска содержит

excel2.ru

Поиск позиции ТЕКСТа в MS EXCEL с выводом значения из соседнего столбца

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

отражены в окне. строки, в которой. Она также относится«равно» повторяющиеся значения, то – выбрать из

Задача

БумагаручкиПОИСКПОЗ магазине, заданном пользователем. ячеек. Новый формат такое 100000 -=ИНДЕКС(C6:C101;B3+ЕСЛИ(B2=»м»;48)-17)

Решение

F3 (сумма) пишем

«Главная»«OK»Запускается окно аргументов функции
Далее устанавливаем курсор в расположены данные по
к статистической группе), второй столбец из

исходной таблицы строки,, т.к. ЛОЖЬ функцией), а диапазоном дляищет в столбце В ячейке А8 заранее определен пользователем значение в формуле?SerjVorotilov формулу, которая посчитает. Клацаем по пиктограмме.СУММЕСЛИ поле выручке Магазина 1

операторов. Задачей	«<>» таблицы выше поясняет
удовлетворяющие определенным критериям	МИН выборки — столбецD1:D13
можно изменить номер	в параметрах правила подскажите…плиз…: Смотри прикрепленный файл.
все заказы Иванова	«Условное форматирование»Производится запуск окошка аргументов. В нём имеется
«Условие1»	за неделю. ДляСЧЁТЕСЛИМН(
какое значение будет	(подобно применению стандартногоигнорируется. При желании, с ценами.значение артикула из торговой точки –ВЫБОР условного форматирования (зеленая
davaispoem	Условия задачи:
на хлеб. Формула	, размещенной в блоке
оператора, наименование которого	три поля, соответствующих

. Тут нам нужно этого ставим курсорявляется подсчет ячеек«не равно» выведено (обычно возвращается Фильтра). Произведем отбор значений можно выделить мышьюДля будущего удобства, конвертируем ячейки вернет для функции заливка).: Все заработало и

1. В таблице такая.«Стили» было указано выше. аргументам указанного оператора. указать нижнюю границу в поле и в указанном массиве,). Например, если задать первое значение, удовлетворяющее из исходной таблицы всю функцию ЕСЛИ(…) исходный диапазон сC16 СУММ ссылку наФункция ВЫБОР находит и протянулось, большое спасибо 1 столбец B1:B5

=СУММ((A2:A9=F2)*(B2:B9=F1)*C2:C9)на ленте. ОткрываетсяУстанавливаем курсор в полеВ поле значений в ячейках, выделяем соответствующую строку которые удовлетворяют определенному выражение

 критерию). с помощью формул в строке формул ценами в «умную. Последний аргумент функции другой интервал. Если возвращает значение из вам. и строка C3:G3В первых скобках список действий. Клацаем«Диапазон суммирования»«Диапазон»

которые будут принимать в таблице. Координаты набору условий. Именно«, то при подсчетеЕсли диапазон поиска содержит

excel2.ru

Использование критериев в Microsoft Excel

массива. В отличие… и нажать на таблицу». Для этого 0 — означает поставить в ячейке списка аргументов, используяGuest = const, а формулы указан диапазон в нём по. В отличие отвводим область таблицы, участие в подсчете. отображаются в окне. тот факт, что будут учитываться только повторяющиеся значения и

от применения Фильтра клавиатуре

Применение критериев

выделите его и поиск точного (а А8 цифру 2, номер индекса. Может: Максимально возможное число. диапазон C4:G8 заполняется всех товаров, из позиции последующих аргументов, этот в которой будут Указываем выражениеУчитывая, что для Магазина можно задать не элементы, заданные аргументом требуется вернуть не (F9 выберите на вкладке не приблизительного) соответствия. формула подсчитает выручку обработать до 254 Для поиска последней пользователем, при этом которого нужно выбрать«Создать правило…» единственный в своем располагаться значения, проверяемые

СЧЁТЕСЛИ

«>14000» 1 дневная норма один, а несколько«Диапазон» одно, а всеCTRL+SHIFT+L, чтобы наглядно увидетьГлавная — Форматировать как

Функция выдает порядковый

для второго магазина значений. Имеет простой заполненной ячейки. в каждом отдельном товар, написанный в. роде и указывает

 на соблюдение условий.. выручки составляет 14000 параметров, и отличает, в которых находятся значения, удовлетворяющие критерию,или Данные/ Сортировка тот самый результирующий таблицу (Home - номер найденного значения (результат СУММ для синтаксис, но достаточноПочему 100000? Дата столбце значения (в ячейке F2 (хлеб).Активируется окошко генерации правила на тот массив В нашем случаеВ поле рублей, то в этого оператора от числовые значения менее то читайте статью и фильтр/ Фильтр) массив, из которого Format as Table) в диапазоне, т.е. диапазона В2:В5). широкие возможности. Рассмотрим в числовом выражении данном примере -Во вторых скобках форматирования. В области значений, где будет это будет строка«Диапазон условия2» поле предыдущего. Синтаксис следующий: 50. Использование данных Запрос на основе отобранные строки будут потом функцияили нажмите фактически номер строки,С помощью функции ВЫБОР

лучшие из них пятизначна (например, 01.01.12=40909), от 1 до указан диапазон всех

выбора типа правила производиться суммирование подошедших дат. Ставим курсорвводим тот же«Условие 1»=СЧЁТЕСЛИМН(диапазон_условия1;условие1;диапазон_условия2;условие2;…) знаков для указания Элементов управления формы. помещены в отдельнуюМИН

Ctrl+T где найден требуемыый можно задать аргумент на конкретных практических а задача функции 5) не повторяются. заказчиков, из которого
выделяем наименование под указанные критерии в данное поле адрес тем жевписываем выражение«Диапазон условия» параметров будут актуальнымиСовет таблицу.и выбирает минимальное. Наша «поумневшая» таблица артикул.
для функции СУММ примерах. ПРОСМОТР() в данном2. В таблице нужно выбрать заказчика,«Форматировать только ячейки, которые данных. Затем выделяем и выделяем все способом, который вводили«>14000»является идентичным первому и для всех: Если в диапазонДля нахождения позиции значения значение: автоматически получит имяФункция так, чтобы получитьСинтаксис функции: =ВЫБОР(номер индекса; случае — найти 2 ячейка J3
написанного в ячейке содержат» область строки ячейки, в которых в поле. аргументу предыдущего оператора. других вариантов, о поиска постоянно вводятся в столбце, сЭтот вариант использует малоизвестнуюТаблица1ИНДЕКС результат подсчета 2,

знач. 1; знач. дату в строке позволяет из раскрывающегося F1 (Иванов).. В первом поле
«Магазин1» содержатся даты.«Диапазон условия1»В поля То есть, он которых пойдет речь новые значения, то последующим выводом соответствующего (и многими, к, а к столбцамвыбирает из диапазона 3, 4 и 2; …). 5. списка выбрать определеннуюВ третьих скобках блока условий из, в которой размещеныТак как нам нужно, то есть, опять

«Диапазон условия2 (3,4,5)» представляет собой ссылку

СЧЁТЕСЛИМН

в данном уроке для исключения ввода значения из соседнего сожалению, недооцененную) функцию можно будет, соответственно,A1:G13 т.д. первых значенийАргументы:Например, для столбца дату, лежащую строго суммируются выбранные данные. списка возможных вариантов значения выручки по сложить только суммы вносим координаты ячеекследует внести координаты на область, в

ниже.

 дубликатов следует наложить столбца в EXCEL,ДМИН (DMIN) обращаться по ихзначение, находящееся на диапазона:Номер индекса – порядковый G можно было в интервале значенийНО, нажимаем не выбираем соответствующей торговой точке.

 выручки, начиная с со значениями выручки строк с недельной которой будет производитьсяА теперь давайте на определенные ограничения (см. существует специальная функцияиз категории именам, используя выражения пересечении заданной строкиФормула суммирует диапазон А1:А4. номер выбираемого аргумента дать прямую ссылку строки C3:G3. просто «Enter», а«Значение ячейки»После того, как адрес 11 марта, то

по первой торговой выручкой соответственно Магазина подсчет ячеек, удовлетворяющих конкретном примере посмотрим, статью Ввод неповторяющихся ВПР(), но дляРабота с базой данных типа (номер строки с Вторая часть диапазона из списка значений. на G4. Для3. В таблице ТРИ кнопки: «Ctrl». В следующем поле отобразился в окне, в поле точке. 2, Магазина 3, указанным условиям. Данный

как работает данный значений). Для визуальной ее решения можно (Database)Таблица1[Товар] артикулом выдает функция функции СУММ задана Может быть числом следующего столбца Н4=»», 2 диапазон I4:I8 + «Shift» + выбираем позицию

переходим к полю«Критерий»
В поле Магазина 4 и
оператор позволяет задать оператор на практике.
проверки наличия дубликатов использовать также и
и требует небольшогоили

ПОИСКПОЗ с помощью функции от 1 до поэтому нужно найти строго соответствует диапазону «Enter». Только тогда«Больше»«Диапазон условия1»вбиваем значение
«Условие2» Магазина 5. Действие сразу несколько такихИтак, имеется таблица, где можно использовать Условное другие функции. Рассмотрим изменения результирующей таблицы:Таблица1[Цена]) и столбца (нам ВЫБОР. 254, ссылкой на ячейку, в которой J4:J8, то есть будет формула считать.. В последнем —
. Тут нам нужно«>10.03.2017»указываем верхнюю границу выполняем по тому областей.
представлена выручка по форматирование (см. статью задачу в случаеКак видите, зеленые ячейки. При желании, стандартное нужен регион, т.е.Данная функция хорошо обрабатывает ячейку с числом нужная дата. Вот значению I4 строго Вокруг этой формулы указываем само значение,

будет отобразить координаты. отбора: же алгоритму, что«Условие» пяти магазинам за Выделение повторяющихся значений). текстовых значений. с результатами транспонированы

имя второй столбец). в качестве значений от 1 до здесь приходит на соответствует J4, I5 появятся фигурные скобки. больше которого требуется строки с датами.В поле«. и для первого

представляет собой критерий, неделю. Нам нужноДля организации динамической сортировкиПусть в диапазоне из столбца вТаблица1enzo простые списки чисел. 254, массивом или помощь ПРОСМОТР(). — J5, и Так обозначаются формулы отформатировать элементы таблицы. Производим зажим левой«Диапазон суммирования»После того, как все аргумента данной группы. который определяет, какие узнать количество дней пополняемого диапазона поискаА4:В15

строку и надможно подкорректировать на: Уважаемые, добрый день! Поэтому с ее формулой.vikttur т.д.
массива Excel. Подробнее У нас это кнопки мыши инужно указать область, указанные действия произведены,В поля элементы из соответствующего за этот период, можно использовать идеиимеется таблица с ними добавлена мини-таблица

вкладке Есть у меня помощью можно вычислитьЗнач. 1; знач. 2;: Это я выше.Вопрос: о формулах массива, 14000. Чтобы выбрать

выделяем все даты значения которой, отвечающие клацаем по кнопке«Условие2» массива данных войдут в которых в из статьи Сортированный перечнем сотрудников и
(F4:H5) с условиями.Конструктор (Design) таблица перечня позиций. по номеру месяца … — список Выпал.Подскажите, пожалуйста, формулу,  читайье в статье тип форматирования, клацаем в таблице. указанным критериям, будут«OK», в подсчет, а Магазине 2 доход список.
их зарплат (фамилии Логика работы этой, которая появляется, если Я беру фильтром финансовый квартал. аргументов от 1В таблице находятся данные возвращающую: «Формулы массива Excel». по кнопкеСтавим курсор в поле суммироваться. В нашем.«Условие3» какие не войдут. от реализации превысилПрограмма Microsoft Excel является сотрудников не повторяются). функции следующая:
щелкнуть в любую выбираю определенное условиеТаблица с номерами месяцев до 254, из по продажам отсортированные1. В ячейку Получилось так.«Формат…»«Условие1» случае это значенияПрограмма выдает результат расчета.

, Каждой заданной области 15000 рублей. не просто табличнымТребуется, введя в ячейкуБаза_данных ячейку нашей «умной» (столбец постоянный к и кварталов: которого выбирается значение по возрастанию относительно К4 — фамилию

Так можно выбирать и.. Первым условием является выручки строки Как видим, итоговое

«Условие4» данных нужно указыватьВыделяем элемент листа, в редактором, а ещёD4
- вся наша таблицы. Подробнее о примеру «Группа крови»Так как финансовый год или действие, соответствующее даты транзакции. Необходимо из диапазона B1:B5, складывать любые данныеАктивируется окно форматирования. Передвигаемся то, что нами

СУММЕСЛИ

«Магазин1» значение равно 5.и условие отдельно, даже который оператор будет и мощнейшим приложениемфамилию сотрудника, вывести таблица вместе с таких таблицах и = 2) , начался в апреле,

номеру индекса. Первое

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

 заголовками. их скрытых возможностях и вывожу список месяцы 4, 5 значение – обязательный дни сумма транзакции даты, выбираемой пользователем диапазона.«Заливка» не ранее 09 элементов листа. в 5 днях

вносим соответственно значения если оно совпадает. После этого щелкаем Не в последнюю его зарплату. РешениеПоле можно почитать здесь. для распечатки. Делал и 6 попали аргумент. Последующие – превысила 5000. Следует в ячейке J3,В Excel есть. Из предложенных вариантов

марта. Поэтому вводимПосле того, как произведено из исследуемых семи«>15000 Обязательно требуется, чтобы по пиктограмме очередь такая возможность приведено в файле- название столбцаНачиная с версии Excel

это фильтром , в первый квартал. нет. Список аргументов-значений выбрать все суммы и значения ячейки функции, которые считают
цветов заливки выбираем значение введение всех указанных выручка в первом», все массивы, используемые«Вставить функцию» появилась благодаря встроенным примера. из шапки таблицы,
2016 в наборе потом копировал значения При введении аргументов – числа, ссылки по условию и
I4, находящегося в не все ячейки, синий, щелкая по«>08.03.2017» данных, жмем на магазине была в«>24000» в качестве областей. функциям. С помощьюАлгоритм решения задачи следующий: из которого выбирается функции Microsoft Excel

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

интервале от 14000, условия, имели одинаковоеПроизводится запуск некоторых функций (операторов)находим в списке кодов минимальное значение. наконец появились функции, автоматизировать)) Хотят чтобы необходимо вводить в формулы, функции или

Вместо того чтобы тратить столбце выбранной даты; условию, которое мы мыши. После того,Перемещаемся к аргументу«OK»
до 17000 рублей.«>11000» количество строк иМастера функций можно задавать даже значение, совпадающее сКритерий которые легко решают была ячейка «Группа том порядке, в текст. время на визуальный2. В ячейку напишем в формуле. как выбранный цвет

СУММЕСЛИМН

«Диапазон условия2».Ещё одним оператором, которыйи столбцов.. Совершаем перемещение в условия вычисления, которые критерием;- таблица с нашу задачу -

крови", куда напишут

 каком они находятсяЕсли указать номер индекса анализ данных таблицы, К4 — фамилию Например, посчитать сумму отобразился в области

. Тут нужно внестиПосле этого в предварительно использует критерии, является«>32000»Для того, чтобы задать

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

это функции цифру 2 и в таблице.

1, то функция будем использовать правило из диапазона B1:B5, заказа определенного покупателя,«Образец» те же координаты, указанный элемент рабочегоСУММЕСЛИ. Как нетрудно догадаться, несколько параметров одной«Статистические» Давайте подробнее узнаем, найденного значения; (минимально) из двухМИНЕСЛИ (MINIFS) из таблицы будетВ ячейку D8 пользователь

вернет первое значение условного форматирования, которое получающуюся на пересечении итоги конкретного ученика,, клацаем по кнопке
которые были записаны листа будет выведен. В отличие от эти значения соответствуют и той же. Там находим и каким образом можновыводим значение из соседнего ячеек: названия столбца,и вытягиваться все позиции вводит номер месяца.
их перечня. Если автоматически определит и даты, выбираемой пользователем
посчитать данные за«OK» в поле результат обработки данных предыдущих функций, он интервалу выручки, превышающую области данных, например, выделяем наименование их использовать при столбца той же по которому идетМАКСЕСЛИ (MAXIFS) с группой 2, В ячейке D9 индекс равен 2 выделит цветом все

в ячейке J3, определенную дату, т.д..«Диапазон условия1» функцией относится к математическому норму для соответствующего чтобы подсчитать количество«СЧЁТЕСЛИ» работе в Экселе. строки.

проверка (. Синтаксис этих функции но с меньшим функция ВЫБОР вычисляет – второе значение. необходимые суммы. Пример и значения ячейки Про одну изАвтоматически происходит возврат к. Делаем это тем

СУММЕСЛИ блоку операторов. Его магазина. ячеек, в которых. Затем следует клацнутьСкачать последнюю версиюРешение практически аналогично поискуТовар очень похож на определенными столбцами. номер финансового квартала.

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

по кнопке Excel числового значения из) и критерия (СУММЕСЛИМН (SUMIFS)Пока ломаю головуМожно так же вычислять Если список аргументов

на рисунке: Таблице 1 в в статье «Функция форматирования. В нём есть, путем выделения он равен 47921,53. данных в ячейках, произведен ввод всех определенного числа, но«OK»Критерии представляют собой условия, статьи Поиск позицииБумага, Карандаши, Ручки

: как можно сделать грядущие даты. Эту состоит из конкретных
Все фактуры отсортированные относительно столбце выбранной даты; «СУММЕСЛИ» в Excel». также в области строчки с датами. Это означает, что которые соответствуют определенному необходимых данных (всего меньше другого числа,. при которых программа ЧИСЛА с выводом

Условное форматирование

).=МИНЕСЛИ(Диапазон_чисел; Диапазон_проверки1; Условие1; Диапазон_проверки2; это… задачу она решает значений, то формула дат транзакций.и т.д.В Excel можно«Образец»Устанавливаем курсор в поле

начиная с 11.03.2017, условию. Синтаксис таков: 10 полей), жмем следует в качестве

Происходит активация окна аргументов выполняет определенные действия. соответствующего значения изЭто обычная формула (не Условие2 … )
китин в совокупности с ВЫБОР возвращает одноЧтобы автоматически экспонировать цветомТо есть, дата, написать ссылки наотображается синий цвет.«Условие2» и до конца=СУММЕСЛИ(диапазон;критерий;[диапазон_суммирования]) на кнопку аргумента
вышеуказанного оператора. В Они применяются в соседнего столбца. Для формула массива), т.е.где: а файл с функцией ДЕНЬНЕД. Например, из значений согласно необходимые суммы, необходимо: выбираемая пользователем в несколько листов в Тут нам нужно. Вторым условием является анализируемого периода, общаяАргумент«OK»«Диапазон условия» поле целом ряде встроенных этого типа задач можно вводить иДиапазон_чисел группой крови на пользователь делает небольшие
индексу.Выделите диапазон ячеек A2:C14 ячейке J3, определяет большой формуле быстро. произвести одно единственное то, что дни, выручка по Магазину«Диапазон».несколько раз указать«Диапазон» функций. В их в EXCEL существует использовать ее привычным
- диапазон с рукаве? отчеты о проделаннойЕсли аргументы – ссылки и выберите инструмент: номер столбца, в Например, сложить данные действие: клацнуть по за которые будет 1 составила 47921,53указывает на областьПрограмма производит подсчет и
один и тотследует указать область названии чаще всего специальная функция ВПР(), образом. Кроме того, числами, из которых

Udik работе и сдает на ячейки, то

«ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило». котором ищется значение

отчетов, расположенных на кнопке суммироваться выручка, должны рубля. ячеек, которые будут выводит результат на же массив. Но ячеек, среди которых присутствует выражение но для ее в той же выбирается минимальное или: без файла сложно их начальнику каждый функция вернет ссылки.В появившемся окне «Создание ячеек диапазона I4:I8, разных листах. Подробнее,«OK» быть не позже

Завершим изучение операторов, которые

lumpics.ru

Сложить выборочно данные из таблицы Excel.

проверяться на соблюдение экран. Как видим, при этом в будет производиться подсчет.«ЕСЛИ» решения можно использовать категории можно найти максимальное отвечать. А так вторник. Можно рассчитатьВЫБОР возвращает ссылку на правила форматирования» выберите нужное для возвращения смотриет в статье.
13 марта. Поэтому используют критерии, остановившись условия. По сути, он равен числу
качестве соответствующих аргументов В нашем случае. К данной группе и другие функции функцииДиапазон_проверки — например, расширенный
дату следующего вторника. интервал В1:В7. А опцию: «Использовать формулу конкретной фамилии в «Ссылки в ExcelПосле выполнения последнего действия,
записываем следующее выражение:
на функции он задается по 3. Это означает,«Условие» следует выделить содержимое операторов, прежде всего,
(про функцию ВПР()БДСУММ (DSUM)- диапазон, который фильтр.В первом столбце вспомогательной функция СУММ использует
для определения форматированных соответствующий диапазон К4:К8
на несколько листов все ячейки выделенного«.СУММЕСЛИМН тому же принципу, что в трехследует указывать разные строки нужно отнести см. эту статью)., проверяется на выполнениеenzo таблицы – номера
этот результат в ячеек».Guest сразу».
массива, где содержитсяПереходим в поле. Задачей данной математической что и одноименный днях из анализируемой критерии.«Магазин 2»СЧЁТЕСЛИРешениеДМАКС (DMAX) условия: Извиняюсь))) прикладываю! дней недели. В качестве аргумента.В поле ввода введите
:PerineiKa число большее, чем«Диапазон условия2» функции является суммирование аргумент функции недели выручка воНа примере все той, в которой расположены,Если несколько значений удовл.

excel-office.ru

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

,Условиеsboy
третьем столбце –Аргументы-значения могут быть представлены
формулу: 5000′ class=’formula’>Микки: Доброго времени, уважаемые 14000, будут залиты. В данном случае значений указанных областейСЧЁТЕСЛИ всех торговых точках
же таблицы с значения выручки поСЧЁТЕСЛИМН критериюБСЧЁТ (DCOUNT)- критерий отбора: количество дней, которое отдельными значениями:Нажмите на кнопку формат,
: Вам задали Вы Форумчане! синим цветом.

 нам нужно выделить таблицы, отобранных по

. превышала установленную для
недельной выручкой магазинов дням. Ставим курсор,=ВПР($D$4;A4:B15;2;ЛОЖЬ), которые используются совершенноНапример, в нашемenzo
нужно прибавить кОсобенности использования функции: чтобы задать цвет

 и решайте ВыПрошу неучу в

Более подробно о возможностях тот самый массив, нескольким параметрам. Синтаксис«Критерий» них норму. посмотрим, как это в указанное полеСУММЕСЛИберется аналогично, но умеют случае:

, Очень творческий пример)))) текущей дате, чтобыЕсли индекс представлен дробью,
заливки для ячеек,

 же не 35 проблеме:
условного форматирования рассказывается
адрес которого был указанного оператора таков:— является обязательнымТеперь несколько изменим задачу. работает. Нам нужно и, зажав левую,первое находить не толькоПросто, красиво, изящно. Одна улыбался от души)
получить следующий вторник. то функция возвращает например – зеленый. девчонок файл неиз приложенной таблицы в отдельной статье. внесен, как массив
=СУММЕСЛИМН(диапазон_суммирования;диапазон_условия1;условие1;диапазон_условия2;условие2;…) аргументом, задающим параметр Нам следует посчитать узнать количество дней кнопку мыши, выделяемСУММЕСЛИМНсверху минимум, но и
проблема — функции
почему у всех Например, к понедельнику
меньшее целое значение. И нажмите на открывал но похоже по 2 признакамУрок: Условное форматирование в суммирования.«Диапазон суммирования» отбора ячеек из количество дней, в недели, когда доход
соответствующий массив в. Кроме встроенных операторов,=ИНДЕКС(B4:B15; ПОИСКПОЗ($D$4;A4:A15;0);1) сумму, максимум иМИНЕСЛИ телка Маша? необходимо добавить 1Если индекс – массив всех открытых окнах на ИНДЕКС(ПОИКПОЗ();ПОИСКПОЗ();;) Ройте
(в моем случае

программе ЭксельПосле того, как адрес— это аргумент, указанной области данных, которых Магазин 1 во всех указанных таблице. Адрес выделенного критерии в Excelберется

 количество значений пои

китин день, ко вторнику значений, то функция кнопку ОК.SerjVorotilov по возрасту иКак видим, с помощью

 указанного массива отобразился являющийся адресом того которые будут суммироваться. получил выручку, превышающую торговых точках достигал массива отобразится в
используются также припервое условию.МАКСЕСЛИ
: формула массива – 7 дней ВЫБОР вычисляет каждый
В результате мы получили: И, все же, полу)найти значение тариф инструментов, использующих при в окне, переходим массива, ячейки в Принципы указания те 14000 рублей, но
установленной для них окне. условном форматировании. РассмотримсверхуЕсли в исходной таблицепоявились только начиная
200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ИНДЕКС(Таблица3[имя];НАИМЕНЬШИЙ(ЕСЛИ(Таблица3[группа крови]=$R$1;СТРОКА(Таблица3[имя])-2);СТРОКА(A1)));»») (до следующего вторника).
аргумент.

 желаемый эффект. Все хотелось бы поподробнее, и перенести в своей работе критерии, к полю

 котором, отвечающие определенному же, что и меньшую, чем 17000 нормы. Нормы выручкиВ следующем поле

 их применение при=СМЕЩ($B$3; ПОИСКПОЗ($D$4;$A$4:$A$15;0);0;1;1) очень много строк, с 2016 версииUdikВ ячейку F2 запишемЕсли индекс не совпадает дни, в которых т.к. своего опыта заглавную (ячейка выделена в Экселе можно

«Условие3» критерию, будут складываться. у аналогичных аргументов рублей.

 следующие:«Критерий» работе с различнымиберется
но данные меняются Excel. Если у: Расширеный фильтр с текущую дату (СЕГОДНЯ()). с номером аргумента сумма транзакции превышает для решения вопроса красным красным).
решать довольно разноплановые. Учитывая, что в«Диапазон условия» предыдущих операторов, которыеСтавим курсор в элемент,Магазин 1 – 14000как раз нужно инструментами данного табличногопервое не часто, то

 вас (или тех, макросом А в ячейку

planetaexcel.ru

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

в списке (меньше 5000 выделились зеленым не хватает.Перечитала почти весь задачи. Это может суммировании будут принимать— аргумент, представляющий были рассмотрены нами где будет произведен рублей;

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

задать непосредственный параметр процессора более подробно.сверху удобнее будет использовать кто будет потомenzo F3 – формулу 1 или больше цветом.Алгоритм действий понимаю,

форум, но только быть, как подсчет

участие только значения, собой массив данных,

выше. вывод на листМагазин 2 – 15000
отбора. В нашемГлавной задачей оператора=ДВССЫЛ(АДРЕС(НАИБОЛЬШИЙ( ЕСЛИ(($A$4:$A$15=$D$4); СТРОКА($A$4:$A$15));1);2)) сводную таблицу, т.к. работать с вашим
: для расчета даты
последнего значения), тоВ формуле основную задачу а вот как это нашла: я сумм и значений, величина которых превышает проверяемый на соответствие

«Диапазон суммирования» результатов подсчета. Клацаем рублей; случае нужно подсчитатьСЧЁТЕСЛИберется

формула массива и файлом) более старыекитин следующего вторника: функция выдает ошибку берет на себя его представить формулой или не до так и форматирование, 14000 рублей, вносим условию;— это необязательный по пиктограммеМагазин 3 – 24000 только те элементы, относящегося к статистическойпоследнее функция ДМИН могут версии, то придется, Спасибо то чтоИндекс определяется с помощью #ЗНАЧ!. функция =СУММЕСЛИ(). Она — нет.

конца понимаю тонкости,

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

а также выполнение запись следующего характера:«Условие» аргумент. Он указывает«Вставить функцию» рублей; таблицы, в которых группе, является подсчетсверху сильно тормозить Excel. шаманить другими способами. нужно)! функции ДЕНЬНЕД, которая суммирует только теТ.е., формула изначально ну или просто многих других задач.«>14000»— аргумент, представляющий на конкретную область

exceltable.com

Функция ВЫБОР в Excel ее синтаксис и примеры использования

над рабочей площадьюМагазин 4 – 11000 значение превышает 15000. занятых различными значениями=ПРОСМОТР($D$4;$A$4:$A$15;$B$4:$B$15)Установите активную ячейку вВ английской версии этоenzo возвращает для заданнойФункция ВЫБОР решает задачи значения, которые соответствуют

Аргументы и особенности синтаксиса

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

собой критерий отбора массива, в которой листа. рублей; Поэтому с помощью ячеек, которые удовлетворяютесли столбец отсортирован по любое место нашей будет, соответственно =MIN(IF(Table1[Товар]=F4;Table1[Цена])):
даты соответствующего дня по представлению значений определенным критериям в дат C3:G3. для додумать. Простите в данной программеПосле выполнения последнего действия для сложения. будет производиться суммирование.Так как мы совсемМагазин 5 – 32000 клавиатуры вбиваем в определенному заданному условию. возрастанию, то берется

умной таблицы иНе забудьте после вводаsboy недели. из списка в ее аргументах. ВПри обнаружении нужной ели что-то не с критериями, то клацаем по кнопкеДанная функция подразумевает операции Если его опустить недавно применяли формулу

рублей. указанное поле выражение Его синтаксис следующий:

последнее выберите на вкладке этой формулы в, не знаю ,Как использовать функцию

Excel. Например, диапазон первом аргументе указывается

даты (равной дате

внятно сформулировала!!! есть, с определенными«OK»
сразу с несколькими и не указывать,СЧЁТЕСЛИМНДля выполнения вышеуказанной задачи,
«>15000»=СЧЁТЕСЛИ(диапазон;критерий)сверху, если нет,Вставка — Сводная таблица первую зеленую ячейку друг отправил, чтобВПР (VLOOKUP)

А2:А8 содержит номера

Функция ВЫБОР в Excel: примеры

диапазон ячеек где из ячейки J3)Помогите пожалуйста-уж очень условиями, при выполнении. наборами подобных операторов. то по умолчанию, то теперь не выделяем курсором элемент.Как видим, у данного то результат

(Insert — Pivot G4 нажать не сделал я, видимодля поиска и

недели от 1 находятся данные для она, в столбце хочется облегчить работу которых активируется указанноеПрограмма выводит результат наПосмотрим, как данный оператор считается, что он

обязательно переходить в рабочего листа, кудаПосле того, как все оператора два аргумента.непредсказуем Table) Enter, а Ctrl+Shift+Enter, Маша его обидела) выборки нужных значений

до 7. Необходимо сравнения со значением под этой датой,

35 девчёнок!!! действие, является набор лист. Он равен применим для решения равен значению обязательного группу будет выводиться итог вышеуказанные манипуляции произведены,«Диапазон»=СУММПРОИЗВ((A4:A15=D4)*(B4:B15)). В появившемся окне чтобы ввести ееВ Microsoft Excel давно из списка мы

отобразить день недели указанном во втором должна искать значениеGIG_ant встроенных функций, а 62491,38. Это означает, задач в контексте аргумента

«Статистические» обработки данных клацаем по кнопкепредставляет собой адрессоответствующие значения суммируются

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

: =СУММПРОИЗВ((A6:A101=B3)*(B6:B101=B2)*C6:C101) также условное форматирование.

что за период нашей таблицы выручки«Диапазон»Мастера функцийСЧЁТЕСЛИМН«OK» массива элементов на=СУММЕСЛИ(A4:A15;D4;B4:B15)ОК Затем формулу можно наборе функции

вы еще с «понедельник», «вторник», «среда», аргументе указываем суммы I4.PerineiKa

Автор: Максим Тютюшев с 09 по от реализации в.. Наименование данного оператора. Клацаем по иконке. листе, в которыхсоответствующие значения суммируются: скопировать на остальные

СЧЁТЕСЛИ (COUNTIF) ней не знакомы «четверг», «пятница», «суббота», для суммирования. ОбратитеНайдя это значение,: ОГРОМНОЕ СПАСИБО!!!!!Можно 13 марта 2017 торговых точках. НамТеперь, как всегда, рассмотрим можно найти в«Вставить функцию»Программа производит подсчет и

следует произвести подсчет.=БИЗВЛЕЧЬ(A3:B15;2;F15:F16)В конструкторе сводной таблицы товары в ячейки, — загляните сюда,

«воскресенье». внимание на то, мы получим номеря реально 2в Excelсложить выборочно данные

exceltable.com

Поиск нужных данных в диапазоне

года сумма выручки нужно будет подсчитать применение данного оператора категории. выводит результат в«Критерий»возвращается ошибка #ЧИСЛО! перетащите поле G5:G6.СУММЕСЛИ (SUMIF) не пожалейте пятиПо такому же принципу

что в первом строки, в которой дня рыла по из ячеек при сложении её доход, который принес на практике. На«10 недавно использовавшихся»Перейдя в элемент листа, который— это аргумент,Для функции ВПР() требуется,Товар

Давайте разберем логику работыи минут, чтобы сэкономить можно выводить отметки,

и третьем аргументах в параллельном столбце

 сайтам и литературе!

. Так можно посчитать, за дни, в Магазин 1 за основе той же. Выделяем его иМастер функций был выделен перед который задаёт условие, чтобы столбец, пов область строк, этой формулы поподробнее.СРЗНАЧЕСЛИ (AVERAGEIF) себе потом несколько баллы, времена года мы используем абсолютные и находится фамилия, Стыдно было за

например, сумму товарного которых она превышает период с 09 таблицы перед нами щелкаем по кнопке, снова перемещаемся в активацией что именно должны которому производится поиск, а Функция ЕСЛИ проверяети их аналоги,

planetaexcel.ru

Как вывести список из таблицы согласно определенному условию (Формулы/Formulas)

 часов. прописью. адреса ссылок. В которая нам нужна. свою неграмотность, тут чека, т. д. 14000 рублей, составила по 13 марта стоит задача подсчитать«OK» блокМастера функций содержать ячейки указанной был левее столбца,Цену каждую ячейку массива позволяющие искать количество,Если же вы знакомыТеперь рассмотрим можно склонять то время какПостарался написать максимально почитала и решилась У нас есть
62491,38 рубля. 2017 года. При сумму выручки в

.«Статистические». Как видим, в области, чтобы быть

 который используется дляв область значений. из столбца сумму и среднее с ВПР, то

 слова с помощью во-втором аргументе применяется

 понятно) задать вопрос….. список товаров, вПоследним, описанным нами, инструментом, этом при суммировании Магазине 1 заОткрывается уже знакомое нам

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

 вывода. Обойти это Чтобы заставить своднуюТовар

 в таблице по — вдогон - Excel. Например, слово смешанная ссылка наСпасибо .

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

excelworld.ru

Поиск минимального или максимального значения по условию

равен числу 5. В качестве параметра ограничение позволяет, например, вычислять не суммуна предмет равенства одному или нескольким стоит разобраться с «рубль»: «0 рублей», ячейку.davaispoem всего доброго в Нам нужно узнать, которым используются критерии, только те дни, 11.03.2017.СЧЁТЕСЛИМНСЧЁТЕСЛИМН Это означает, что может быть использовано

вариант с использованием (или количество), а текущему товару ( условиям. Но что похожими функциями:

«1 рубль», «2: Пожалуйста, подскажите как жизни!!!! на какую сумму является условное форматирование. выручка в которыхВыделяем ячейку, в которой. Ставим курсор ви произвести его

в выделенном массиве числовое выражение, текст функций ИНДЕКС() и минимум щелкните правойБумага если нужно найтиИНДЕКС (INDEX) рубля», «3 рубля»,Таким образом Excel проверяет раскидать даты поvikttur конкретный заказчик купил Он выполняет указанный превысила 14000 рублей. будет производиться вывод поле выделение. После выполнения в пяти ячейках или ссылка на ПОИСКПОЗ(). Эквивалентная формула кнопкой мыши по). Если это так, не сумму илии «4 рубля», «5 каждую ячейку в месяцам, в диапазон: =ИНДЕКС((C6:C53;C54:C101);B3-17;;(B2=»м»)+1) конкретный товар. У вид форматирования ячеек,Снова выделяем ячейку для результата. Щелкаем по«Диапазон условия1» указанного действия требуется находятся значения превышающие

Способ 1. Функции МИНЕСЛИ и МАКСЕСЛИ в Excel 2016

ячейку, в которой приведена в статье любому числу и то выдается соответствующее среднее, а минимумПОИСКПОЗ (MATCH) рублей» и т.д. столбце A и которых они входят?PerineiKa нас есть такая которые отвечают заданным вывода итога и пиктограмме

и, произведя зажим произвести нажатие на

15000. То есть,

 критерий содержится. При о функции ВПР(). выберите в контекстном ему значение из или максимум по
, владение которыми весьмаС помощью функции ВЫБОР берет из нееvikttur
: ОГРОМНОЕ СПАСИБО ЗА таблица.

условиям. Взглянем на клацаем по пиктограмме

«Вставить функцию» левой кнопки мыши, кнопку можно сделать вывод, этом, для указанияЗадача подразумевает, что диапазон меню команду столбца условию(ям)? облегчит жизнь любому можно вернуть ссылку критерии для вычисления: Обратите внимание на ПОМОЩЬ!!!! и первая

Способ 2. Формула массива

Мы хотим узнать, сколько пример работы с

«Вставить функцию». выделяем все ячейки,«OK» что в Магазине критерия можно использовать поиска содержит неповторяющиесяИтоги по — МинимумЦенаПредположим, нам нужно найти опытному пользователю Excel.

на диапазон. Это (даты транзакций). Нам диапазон G4:U4 - и вторая формула хлеба заказывал Иванов. условным форматированием..Перейдя в в которых содержится. 2 в пяти следующие знаки: значения. В самом:. В противном случае минимальную цену для Гляньте на следующий

позволяет делать вычисления нужна сумма по форматирование и отсутствие работают!!! Можно снахальничать В пустых ячейкахВыделим те ячейки таблицыВМастер функций выручка по днямВслед за выполнением вышеуказанного днях из анализируемых« ( деле, если критериюВытаскивать данные из сводной

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

Способ 3. Функция баз данных ДМИН

в блоке Магазина 1. Они алгоритма действий открывается семи выручка превысила«меньше»), удовлетворяет сразу несколько в дальнейшие расчеты ЛОЖЬ (FALSE). базе данных по

Необходимо определить регион поставки по заданному пользователем возвращаемый результат функциейdavaispoem совета- не поделитесьВ ячейке F1 значения за день, прежде всего, выполняем

«Математические» расположены в строке, окно аргументов 15000 рублей.
«>» значений, то из теперь можно сТаким образом внешняя функция поставщикам:
 по артикулу товара, критерию. Рассмотрим пример (касается конкретного дня): Спасибо, даты начала ссылкой, где для пишем фамилию заказчика превышают 14000 рублей. перемещение в блокнаходим и выделяем которая так иСЧЁТЕСЛИМН

Урок: Мастер функций в( какой строки выводить помощью функцииМИН (MIN)Таким образом, условием будет набранному в ячейку суммирования выручки в является больше чем месяца увидела, формула, «даунов» можно поподробнее – «Иванов», вВыделяем весь массив элементов«Математические» наименование называется. программе Эксель«больше» соответствующее ему значение

Способ 4. Сводная таблица

ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GET.PIVOT.DATA)выбирает минимальное не наименование товара ( C16. заданном пользователем магазине. 5000, то целая работает, но не почитать на тему ячейке F2 пишем

в таблице, в, а там выделяем«СУММЕСЛИ»«Магазин 1»В полеСледующей функцией, которая оперирует), из соседнего столбца?, которую мы подробно из всех значенийбумага

Задача решается при помощиИмеются данные по выручке формула возвращает значение протягивается для следующих создания подобных формул??? наименование товара, по котором указана выручка пункт под названием. Клацаем по кнопке. После этого координаты«Диапазон условия1» критериями, является«=» Если все же разбирали ранее: цен, а только,

двух функций: в нескольких торговых ИСТИНА и сразу ячеек, возможно гдетоvikttur которому хотим посчитать торговых точек по

planetaexcel.ru

«СУММЕСЛИМН»

Источник

Как выделить строки в Excel по условию? Выделение строк с заданными значениями

Каждый лист рабочей книги Excel разбит на строки и столбцы, количество которых зависит от версии приложения. Строки и столбцы можно удалять, добавлять, объединять и перемещать. Ранее уже рассматривалось удаление строк по условию, удаление столбцов по условию и добавление пустых строк в рабочие книги Excel. Ниже будет рассмотрено еще одно действие, совершаемое со строками — выделение.

Как выделить одну строку?

Существует несколько способов для того чтобы выделить строку:

1. Можно в окне открытого листа кликнуть левой кнопкой мыши курсором-стрелкой один раз по номеру нужной строки ;

2. также, в окне открытого листа, можно установить маркер выделения в любую ячейку нужной строки и использовать сочетание клавиш Shift+Space (пробел);

3. и наконец, можно установить маркер выделения в первую ячейку нужной строки и использовать сочетание клавиш Ctrl+Shift+Стрелка вправо. При этом, если в строке есть данные, то первое нажатие этого сочетания клавиш выделит часть строки с даными, а второе — уже всю строку.

Как выделить несколько строк подряд?

1. Выделить первую строку нужного диапазона и не отпуская левую кнопку мыши протащить курсор-стрелку по номерам нужных строк;

2. выделить первую строку нужного диапазона и при нажатой клавише Shift кликнуть по номеру последней строки нужного диапазона;

3. кликнуть курсором ввода текста в адресном окне строки формул, вписать диапазон строк (например 10:20) и нажать клавишу Enter.

Как выделить несколько строк не подряд?

Для выборочного выделения нескольких строк необходимо кликать по номерам нужных строк курсором-стрелкой при нажатой клавише Ctrl.

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

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

Надстройка позволяет:

1. Одним кликом мыши вызывать диалоговое окно макроса прямо из панели инструментов Excel;

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

возможен ввод сразу нескольких текстовых значений разделенных знаком «;» (точка с запятой);

3. Выбирать одно из восьми условий для строк с искомым значением:

— совпадает с искомым значением;

— не совпадает с искомым значением;

— содержит мскомое значение;

— не содержит искомое значение;

— начинается с искомого значения;

— не начинается с искомого значения;

— заканчивается искомым значением;

— не заканчивается искомым значением.

4. При поиске текста учитывать либо не учитывать регистр (различать заглавные и строчные буквы);

5. При нахождении строки предусмотрена возможность выделять:

а) всю строку по ширине листа;

б) часть строки, ограниченную используемым диапазоном (от первой заполненной до последней заполненной ячейки);

в) часть строки, ограниченную выделенным (выбранным) диапазоном поиска.

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

Поиск и выделение ячеек, соответствующих определенным условиям

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

Чтобы быстро найти и выделить все ячейки, содержащие определенных типов данных, таких как формулы с помощью команды Перейти. Кроме того, с помощью Перейти к найти только ячейки, которые соответствуют определенным критериям, — например последнюю ячейку на лист, содержащую данные или форматирование.

Начинать, выполнив одно из указанных ниже действий.

Чтобы выполнить поиск определенных ячеек на всем листе, щелкните любую ячейку.

Чтобы выполнить поиск определенных ячеек в пределах области, определенной, выберите диапазон, строк или столбцов, которые должны. Дополнительные сведения читайте в статье Выбор ячеек, диапазонов, строк или столбцов на листе.

Совет: Чтобы отменить выделение ячеек, щелкните любую ячейку на листе.

На вкладке » Главная » нажмите кнопку Найти и выделить > Перейти (в группе » Редактирование «).

Сочетание клавиш: Нажмите клавиши CTRL + G.

Нажмите кнопку Дополнительный.

В диалоговом окне Выделить группу ячеек выберите один из указанных ниже вариантов.

Запрос на выборку данных (формулы) в MS EXCEL

Суть запроса на выборку – выбрать из исходной таблицы строки, удовлетворяющие определенным критериям (подобно применению стандартного Фильтра). Произведем отбор значений из исходной таблицы с помощью формул массива. В отличие от применения Фильтра (CTRL+SHIFT+L или Данные/ Сортировка и фильтр/ Фильтр ) отобранные строки будут помещены в отдельную таблицу.

В этой статье рассмотрим наиболее часто встречающиеся запросы, например: отбор строк таблицы, у которых значение из числового столбца попадает в заданный диапазон (интервал); отбор строк, у которых дата принаждежит определенному периоду; задачи с 2-мя текстовыми критериями и другие. Начнем с простых запросов.

1. Один числовой критерий (Выбрать те Товары, у которых цена выше минимальной)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист Один критерий — число ).

Необходимо отобразить в отдельной таблице только те записи (строки) из Исходной таблицы, у которых цена выше 25.

Решить эту и последующие задачи можно легко с помощью стандартного фильтра. Для этого выделите заголовки Исходной таблицы и нажмите CTRL+SHIFT+L. Через выпадающий список у заголовка Цены выберите Числовые фильтры. , затем задайте необходимые условия фильтрации и нажмите ОК.

Будут отображены записи удовлетворяющие условиям отбора.

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

Критерий (минимальную цену) разместим в ячейке Е6, таблицу для отфильтрованных данных — в диапазоне D10:E19.

Теперь выделим диапазон D11:D19 (столбец Товар) и в Строке формул введем формулу массива:

Вместо ENTER нажмите сочетание клавиш CTRL+SHIFT+ENTER.

Те же манипуляции произведем с диапазоном E11:E19 куда и введем аналогичную формулу массива:

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

Чтобы показать динамизм полученного Отчета (Запроса на выборку) введем в Е6 значение 65. В новую таблицу будет добавлена еще одна запись из Исходной таблицы, удовлетворяющая новому критерию.

Если в Исходную таблицу добавить новый товар с Ценой в диапазоне от 25 до 65, то в новую таблицу будет добавлена новая запись.

В файле примера также содержатся формулы массива с обработкой ошибок, когда в столбце Цена содержится значение ошибки, например #ДЕЛ/0! (см. лист Обработка ошибок).

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

3. Один критерий Дата (Выбрать те Товары, у которых Дата поставки совпадает заданной)

Пусть имеется Исходная таблица с перечнем Товаров и Датами поставки (см. файл примера, лист Один критерий — Дата).

Для отбора строк используются формулы массива, аналогичные Задаче1 (вместо критерия =$B$12:$B$20)*(СТРОКА($B$12:$B$20)-СТРОКА($B$11));
$J$12-СТРОКА(A12)+СТРОКА($B$11)+1))

Примечание: После ввода формулы вместо клавиши ENTER (ВВОД) нужно нажать сочетание клавиш CTRL+SHIFT+ENTER. Это сочетание клавиш используется для ввода формул массива.

Скопируйте формулу массива вниз на нужное количество ячеек. Формула вернет только те значения Товаров, которые были поставлены в диапазоне указанных дат. В остальных ячейках будут содержаться ошибки #ЧИСЛО! Ошибки в файле примера (Лист 4.Диапазон Дат) скрыты с помощью Условного форматирования.

Аналогичную формулу нужно ввести и для дат в столбец E.

В ячейке J12 вычислено количество строк исходной таблицы, удовлетворяющих критериям:

Для ввода первой формулы выделите диапазон ячеек G12:G20. После ввода формулы вместо клавиши ENTER (ВВОД) нужно нажать сочетание клавиш CTRL+SHIFT+ENTER.

Решение3: Если столбец Дат СОРТИРОВАН, то можно не использовать формулы массива.

Сначала необходимо вычислить первую и последнюю позиции строк, которые удовлетворяют критериям. Затем вывести строки с помощью функции СМЕЩ().

Этот пример еще раз наглядно демонстрирует насколько предварительная сортировка данных облегчает написание формул.

5. Один критерий Дата (Выбрать те Товары, у которых Дата поставки не раньше/ не позже заданной)

Пусть имеется Исходная таблица с перечнем Товаров и Датами поставки (см. файл примера, лист Один критерий — Дата (не позже) ).

Для отбора строк, дата которых не раньше (включая саму дату), используется формула массива:

= ИНДЕКС(A12:A20;НАИМЕНЬШИЙ(ЕСЛИ($E$7 C15;И($B$7>=B15;$B$7 =$B$13:$B$21)*($B$13:$B$21>0);СТРОКА($B$13:$B$21);»»);СТРОКА($B$13:$B$21)-СТРОКА($B$12))
-СТРОКА($B$12))

Условие $E$7=$A$13:$A$21 гарантирует, что будут отобраны товары только определенного типа. Условие $E$8>=$B$13:$B$21 гарантирует, что будут отобраны даты не позже заданной (включая). Условие $B$13:$B$21>0 необходимо, если в диапазоне дат имеются пустые ячейки. Знак * (умножение) используется для задания Условия И (все 3 критерия должны выполняться для строки одновременно).

7. Один Текстовый критерий (Выбрать Товары определенного вида)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист Один критерий — Текст).

Задача решается аналогично Задачам 1 и 3. Более подробное решение см. в статье Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть1. Обычный поиск.

8. Два Текстовых критерия (Выбрать Товары определенного вида, поставленные в заданный месяц)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист 2 критерия — текст (И) ).

Для отбора строк используется формула массива:

Выражение ($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>.

С помощью функции НАИБОЛЬШИЙ() выведем 3 значения из позиции 5 (строка 15 листа), 6 (16) и 7 (17), т.е. значения Товар2, Товар2 и Товар3. Для этого используем выражение СЧЁТЕСЛИ($A$11:$A$19;$E$6)+СЧЁТЕСЛИ($A$11:$A$19;$E$7)-ЧСТРОК($A$11:A11)+1 , которое последовательно (начиная со строки 11) будет возвращать числа 3; 2; 1; 0; -1; -2; . Формула НАИБОЛЬШИЙ(. ;3) вернет число 5, НАИБОЛЬШИЙ(. ;2) вернет число 6, НАИБОЛЬШИЙ(. ;1) вернет число 7, а НАИБОЛЬШИЙ(. ;0) и далее вернет ошибку, которую мы скроем условным форматированием.

И наконец, с помощью функции ИНДЕКС() последовательно выведем наши значения из соответствующих позиций: = ИНДЕКС(A$11:A$19;5) вернет Товар2, = ИНДЕКС(A$11:A$19;6) вернет Товар2, = ИНДЕКС(A$11:A$19;7) вернет Товар3.

10. Отбор значений с учетом повторов

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

Наиболее популярные статьи из этого раздела:

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

В качестве исходной возьмем таблицу партий товаров.

Предположим, что нас интересует сколько и каких партий товаров поставлялось по цене от 1000р. до 2000р. (критерий 1). Причем, партий с одинаковой ценой должно быть минимум 3 (критерий 2).

Решением является формула массива:

Эта формула возвращает номера строк, которые удовлетворяют обоим критериям.

Формула =СУММПРОИЗВ(($C$14:$C$27>=$B$7)*($C$14:$C$27 =$B$10)) подсчитывает количество строк, которые удовлетворяют критериям.

В файле примера на листе «10.Критерий — колич-во повторов» настроено Условное форматирование, которое позволяет визуально определить строки удовлетворяющие критериям, а также скрыть ячейки, в которых формула массива возвращает ошибку #ЧИСЛО!

11. Используем значение критерия (Любой) или (Все)

В фильтре Сводных таблиц MS EXCEL используется значение (Все), чтобы вывести все значения столбца. Другими словами, в выпадающем списке значений критерия содержится особое значение, которое отменяет сам критерий (см. статью Отчеты в MS EXCEL, Отчет №3).

В файле примера на листе «11. Критерий Любой или (Все)» реализован данный вариант критерия.

Формула в этом случае должна содержать функцию ЕСЛИ() . Если выбрано значение (Все), то используется формула для вывода значений без учета данного критерия. Если выбрано любое другое значение, то критерий работает обычным образом.

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

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

Условное форматирование в Excel позволяет делать формат ячеек меняющимся в зависимости от их содержания. Например, Вы можете заставить ячейку изменять цвет, если в ней содержится значение меньше 100, или, если в этой ячейке находится определённый текст. Но как сделать так, чтобы выделенной стала не только одна ячейка, а вся строка, которая ее содержит?

Как же быть, если необходимо выделить другие ячейки в зависимости от значения какой-то одной? На скриншоте, расположенном чуть выше, видно таблицу с кодовыми наименованиями различных версий Ubuntu. Один из них — выдуманный. Когда я ввёл No в столбце Really?, вся строка изменила цвет фона и шрифта. Читайте дальше, и Вы узнаете, как это делается.

Создаём таблицу

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

Придаём таблице более приятный вид

Следующий этап — делаем формат таблицы более лёгким для восприятия информации, используя простые инструменты форматирования Excel. Настройте формат только тех областей таблицы, которые не будут затронуты условным форматированием. В нашем случае мы нарисуем рамку таблицы и отформатируем строку, содержащую заголовки. Думаю, с этой частью Вы справитесь самостоятельно. В итоге у Вас должно получиться что-то подобное (или, возможно, немного посимпатичнее):

Создаём правила условного форматирования в Excel

Выберите начальную ячейку в первой из тех строк, которые Вы планируете форматировать. Кликните Conditional Formatting (Условное Форматирование) на вкладке Home (Главная) и выберите Manage Rules (Управление Правилами).

В открывшемся диалоговом окне Conditional Formatting Rules Manager (Диспетчер правил условного форматирования) нажмите New Rule (Создать правило).

В диалоговом окне New Formatting Rule (Создание правила форматирования) выберите последний вариант из списка — Use a formula to determine which cells to format (Использовать формулу для определения форматируемых ячеек). А сейчас — главный секрет! Ваша формула должна выдавать значение TRUE (ИСТИНА), чтобы правило сработало, и должна быть достаточно гибкой, чтобы Вы могли использовать эту же формулу для дальнейшей работы с Вашей таблицей.

Давайте проанализируем формулу, которую я сделал в своём примере:

G — это столбец, который управляет работой правила (столбец Really? в таблице). Заметили знак доллара перед G? Если не поставить этот символ и скопировать правило в следующую ячейку, то в правиле адрес ячейки сдвинется. Таким образом, правило будет искать значение Yes, в какой-то другой ячейке, например, H15 вместо G15. В нашем же случае надо зафиксировать в формуле ссылку на столбец ($G), при этом позволив изменяться строке (15), поскольку мы собираемся применить это правило для нескольких строк.

=»Yes» — это значение ячейки, которое мы ищем. В нашем случае условие проще не придумаешь, ячейка должна говорить Yes. Условия можно создавать любые, какие подскажет Вам Ваша фантазия!

Говоря человеческим языком, выражение, записанное в нашей формуле, принимает значение TRUE (ИСТИНА), если ячейка, расположенная на пересечении заданной строки и столбца G, содержит слово Yes.

Теперь давайте займёмся форматированием. Нажмите кнопку Format (Формат). В открывшемся окне Format Cells (Формат ячеек) полистайте вкладки и настройте все параметры так, как Вы желаете. Мы в своём примере просто изменим цвет фона ячеек.

Когда Вы настроили желаемый вид ячейки, нажмите ОК. То, как будет выглядеть отформатированная ячейка, можно увидеть в окошке Preview (Образец) диалогового окна New Formatting Rule (Создание правила форматирования).

Нажмите ОК снова, чтобы вернуться в диалоговое окно Conditional Formatting Rules Manager (Диспетчер правил условного форматирования), и нажмите Apply (Применить). Если выбранная ячейка изменила свой формат, значит Ваша формула верна. Если форматирование не изменилось, вернитесь на несколько шагов назад и проверьте настройки формулы.

Теперь, когда у нас есть работающая формула в одной ячейке, давайте применим её ко всей таблице. Как Вы заметили, форматирование изменилось только в той ячейке, с которой мы начали работу. Нажмите на иконку справа от поля Applies to (Применяется к), чтобы свернуть диалоговое окно, и, нажав левую кнопку мыши, протяните выделение на всю Вашу таблицу.

Когда сделаете это, нажмите иконку справа от поля с адресом, чтобы вернуться к диалоговому окну. Область, которую Вы выделили, должна остаться обозначенной пунктиром, а в поле Applies to (Применяется к) теперь содержится адрес не одной ячейки, а целого диапазона. Нажмите Apply (Применить).

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

Вот и всё! Теперь осталось таким же образом создать правило форматирования для строк, в которых содержится ячейка со значением No (ведь версии Ubuntu с кодовым именем Chipper Chameleon на самом деле никогда не существовало). Если же в Вашей таблице данные сложнее, чем в этом примере, то вероятно придётся создать большее количество правил. Пользуясь этим методом, Вы легко будете создавать сложные наглядные таблицы, информация в которых буквально бросается в глаза.

Урок подготовлен для Вас командой сайта office-guru.ru Источник: http://www.howtogeek.com/howto/45670/how-to-highlight-a-row-in-excel-using-conditional-formatting/ Перевел: Андрей Антонов Правила перепечатки Еще больше уроков по Microsoft Excel

Выборка значений из таблицы 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);»»)))’ class=’formula’>

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

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

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

Источник

Содержание

Выполнение выборки
- Способ 1: применение расширенного автофильтра
- Способ 2: применение формулы массива
- Способ 3: выборка по нескольким условиям с помощью формулы
- Способ 4: случайная выборка
Вопросы и ответы

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

Выполнение выборки

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Урок: Функция автофильтр в Excel

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

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

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

Выделяем все пустые ячейки первой колонки новой таблицы. Устанавливаем курсор в строку формул. Как раз сюда будет заноситься формула, производящая выборку по указанным критериям. Отберем строчки, сумма выручки в которых превышает 15000 рублей. В нашем конкретном примере, вводимая формула будет выглядеть следующим образом:
=ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

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

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

Выделив второй столбец с датами и установив курсор в строку формул, вводим следующее выражение:
=ИНДЕКС(B2:B29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

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

Аналогичным образом в столбец с выручкой вписываем формулу следующего содержания:
=ИНДЕКС(C2:C29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

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

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

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

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

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

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

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

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

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

Урок: Условное форматирование в Excel

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

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

Вписываем в отдельном столбце граничные условия для выборки.

Как и в предыдущем способе, поочередно выделяем пустые столбцы новой таблицы и вписываем в них соответствующие три формулы. В первый столбец вносим следующее выражение:
=ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(($D$2=C2:C29);СТРОКА(C2:C29);"");СТРОКА(C2:C29)-СТРОКА($C$1))-СТРОКА($C$1))

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

Каждый раз после ввода не забываем набирать сочетание клавиш Ctrl+Shift+Enter.

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

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

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

Слева от таблицы пропускаем один столбец. В ячейке следующего столбца, которая находится напротив первой ячейки с данными таблицы, вписываем формулу:
=СЛЧИС()

Эта функция выводит на экран случайное число. Для того, чтобы её активировать, жмем на кнопку ENTER.

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

Теперь у нас имеется диапазон ячеек, заполненный случайными числами. Но, он содержит в себе формулу СЛЧИС. Нам же нужно работать с чистыми значениями. Для этого следует выполнить копирование в пустой столбец справа. Выделяем диапазон ячеек со случайными числами. Расположившись во вкладке «Главная», щелкаем по иконке «Копировать» на ленте.

Выделяем пустой столбец и кликаем правой кнопкой мыши, вызывая контекстное меню. В группе инструментов «Параметры вставки» выбираем пункт «Значения», изображенный в виде пиктограммы с цифрами.

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

Активируется окно настройки сортировки. Обязательно устанавливаем галочку напротив параметра «Мои данные содержат заголовки», если шапка имеется, а галочки нет. В поле «Сортировать по» указываем наименование того столбца, в котором содержатся скопированные значения случайных чисел. В поле «Сортировка» оставляем настройки по умолчанию. В поле «Порядок» можно выбрать параметр как «По возрастанию», так и «По убыванию». Для случайной выборки это значения не имеет. После того, как настройки произведены, жмем на кнопку «OK».

После этого все значения таблицы выстраиваются в порядке возрастания или убывания случайных чисел. Можно взять любое количество первых строчек из таблицы (5, 10, 12, 15 и т.п.) и их можно будет считать результатом случайной выборки.

Урок: Сортировка и фильтрация данных в Excel

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

Еще статьи по данной теме:

Помогла ли Вам статья?

Источник

Поиск нужных данных в диапазоне

Как использовать функцию ВПР (VLOOKUP) для поиска и выборки нужных значений из списка мы недавно разбирали. Если вы еще с ней не знакомы — загляните сюда, не пожалейте пяти минут, чтобы сэкономить себе потом несколько часов.

Если же вы знакомы с ВПР, то — вдогон — стоит разобраться с похожими функциями: ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH), владение которыми весьма облегчит жизнь любому опытному пользователю Excel. Гляньте на следующий пример:

Необходимо определить регион поставки по артикулу товара, набранному в ячейку C16.

Задача решается при помощи двух функций:

=ИНДЕКС(A1:G13;ПОИСКПОЗ(C16;D1:D13;0);2)

Функция ПОИСКПОЗ ищет в столбце D1:D13 значение артикула из ячейки C16. Последний аргумент функции 0 — означает поиск точного (а не приблизительного) соответствия. Функция выдает порядковый номер найденного значения в диапазоне, т.е. фактически номер строки, где найден требуемыый артикул.

Функция ИНДЕКС выбирает из диапазона A1:G13 значение, находящееся на пересечении заданной строки (номер строки с артикулом выдает функция ПОИСКПОЗ) и столбца (нам нужен регион, т.е. второй столбец).

Ссылки по теме

Использование функции ВПР (VLOOKUP) для поиска и подстановки значений.
Улучшенная версия функции ВПР (VLOOKUP)
Многоразовый ВПР

Источник

Функции Excel ИНДЕКС и ПОИСКПОЗ — основы

Функция ИНДЕКС

Функция ПОИСКПОЗ

Как использовать формулу ИНДЕКС ПОИСКПОЗ в Excel

ИНДЕКС+ПОИСКПОЗ вместо ВПР?

4 основные причины использовать ИНДЕКС ПОИСКПОЗ вместо ВПР

ИНДЕКС ПОИСКПОЗ в Excel – примеры формул

Формула для поиска справа налево

Двусторонний поиск в строках и столбцах

ИНДЕКС ПОИСКПОЗ для поиска по нескольким условиям

Среднее, максимальное и минимальное значение при помощи ИНДЕКС ПОИСКПОЗ

Максимальное значение.

Минимальное значение

Ближайшее к среднему

Что делать с ошибками поиска?

Возможен ли «левый» поиск?

Повлияет ли на результат вставка и удаление столбцов?

Возможен ли поиск по строкам и столбцам?

Как сделать поиск ИНДЕКС ПОИСКПОЗ по нескольким условиям?

Вывод отобранных значений в отдельный диапазон в Excel. Бесплатные примеры и статьи.

Поиск позиции ТЕКСТа в MS EXCEL с выводом значения из соседнего столбца

Задача

Решение

Использование критериев в Microsoft Excel

Применение критериев

СЧЁТЕСЛИ

СЧЁТЕСЛИМН

СУММЕСЛИ

СУММЕСЛИМН

Условное форматирование

Сложить выборочно данные из таблицы Excel.

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

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

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

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

Функция ВЫБОР в Excel ее синтаксис и примеры использования

Аргументы и особенности синтаксиса

Функция ВЫБОР в Excel: примеры

Поиск нужных данных в диапазоне

Как вывести список из таблицы согласно определенному условию (Формулы/Formulas)

Поиск минимального или максимального значения по условию

Способ 1. Функции МИНЕСЛИ и МАКСЕСЛИ в Excel 2016

Способ 2. Формула массива

Способ 3. Функция баз данных ДМИН

Способ 4. Сводная таблица

Как выделить строки в Excel по условию? Выделение строк с заданными значениями

Как выделить одну строку?

Как выделить несколько строк подряд?

Как выделить несколько строк не подряд?

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

Поиск и выделение ячеек, соответствующих определенным условиям

Запрос на выборку данных (формулы) в MS EXCEL

1. Один числовой критерий (Выбрать те Товары, у которых цена выше минимальной)

3. Один критерий Дата (Выбрать те Товары, у которых Дата поставки совпадает заданной)

5. Один критерий Дата (Выбрать те Товары, у которых Дата поставки не раньше/ не позже заданной)

7. Один Текстовый критерий (Выбрать Товары определенного вида)

8. Два Текстовых критерия (Выбрать Товары определенного вида, поставленные в заданный месяц)

9. Два Текстовых критерия (Выбрать Товары определенных видов)

10. Отбор значений с учетом повторов

11. Используем значение критерия (Любой) или (Все)

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

Создаём таблицу

Придаём таблице более приятный вид

Создаём правила условного форматирования в Excel

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

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

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

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

Выполнение выборки

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

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

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

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

Еще статьи по данной теме:

Помогла ли Вам статья?

Поиск нужных данных в диапазоне

Ссылки по теме

А вот еще интересные статьи: