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

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

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

Источник

Вывод отобранных значений в отдельный диапазон в 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 и как извлечь уникальные записи с помощью Duplicate Remover.

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

Формулы для уникальных значений в столбце.
Как извлечь уникальные + 1е вхождение дубликатов.
Если нужно игнорировать пустые ячейки.
Выбираем уникальные с учетом регистра.
Отбор уникальных значений по условию.
Как извлечь уникальные значения из диапазона.
Применяем встроенный инструмент удаления дубликатов.
Список уникальных при помощи расширенного фильтра.
Извлечение уникальных данных при помощи Duplicate Remover.

Базовые формулы для получения уникальных значений.

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

Уникальные значения — это значения, которые присутствуют в списке только один раз. Например:

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

Формула уникальных значений массива (заполняется нажатием Ctrl + Shift + Enter):

=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$10; ПОИСКПОЗ(0; СЧЁТЕСЛИ($B$1:B1;$A$2:$A$10) + (СЧЁТЕСЛИ($A$2:$A$10; $A$2:$A$10)<>1); 0)); «»)

Можно воспользоваться и обычной формулой (вводится нажатием Enter):

=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$10; ПОИСКПОЗ(0;ИНДЕКС(СЧЁТЕСЛИ($B$1:B1; $A$2:$A$10) + (СЧЁТЕСЛИ($A$2:$A$10; $A$2:$A$10)<>1);0;0); 0)); «»)

В приведенных выше формулах используются следующие ссылки:

A2: A10 – исходных перечень данных.
B1 — верхняя ячейка уникального списка минус одна строка. В этом примере мы начинаем создавать список уникальных в B2, и поэтому мы записываем B1 в формулу (B2 — 1 строка = B1). Если ваш список начинается, скажем, с ячейки C3, измените $B$1:B1 на $C$2:C2.

В этом примере мы извлекаем уникальные имена из столбца A (точнее из диапазона A2: A10), а следующий скриншот демонстрирует формулу в действии:

Вот наш порядок действий:

Измените любую из формул в соответствии с вашим диапазоном данных.
Введите ее в первую ячейку, с которой начнётся формирование списка (в данном примере B2).
Если вы используете формулу массива, нажмите Ctrl + Shift + Enter. Если вы выбрали обычную, нажмите просто клавишу Enter.
Скопируйте вниз настолько, насколько это необходимо, перетащив мышкой маркер заполнения. Поскольку обе формулы заключены в функцию ЕСЛИОШИБКА, вы можете скопировать вниз с запасом. Это не испортит ваши данные какими-либо ошибками, независимо от того, сколько уникальных значений было извлечено.

Как извлечь различные значения.

Различные значения — появляются в перечне данных хотя бы один раз. Это все уникальные и первое вхождение повторяющихся значений.

Например:

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

Формула массива (требуется нажать Ctrl + Shift + Enter):

{=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$13; ПОИСКПОЗ(0; СЧЁТЕСЛИ($B$1:B1; $A$2:$A$13); 0)); «»)}

или можно так:

{=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$13; НАИМЕНЬШИЙ(ЕСЛИ(ЕНД(ПОИСКПОЗ($A$2:$A$13;$B$1:B1;0)); СТРОКА($A$1:$A$15);»»);1));»»)}

Обычная формула:

=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$13; ПОИСКПОЗ(0; ИНДЕКС(СЧЁТЕСЛИ($B$1:B1; $A$2:$A$13); 0; 0); 0)); «»)

Где:

A2: A13 — это список источников.
B1 — это ячейка над первой ячейкой отдельного списка. В этом примере отдельный список начинается с ячейки B2 (это первая ячейка, в которую вы вводите формулу), поэтому вы ссылаетесь на B1.

Как извлечь значения, игнорируя пустые ячейки

Если исходный список содержит пустые ячейки, формула, которую мы только что обсудили, вернет ноль для каждой пустой строки, что может быть проблемой. Это вы и наблюдаете на скриншоте чуть выше. Чтобы исправить это, сделаем несколько небольших корректировок.

Формула массива для извлечения различных значений, исключая пустые ячейки:

{=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$13; ПОИСКПОЗ(0;СЧЁТЕСЛИ($C$1:C1;$A$2:$A$13&»») + ЕСЛИ($A$2:$A$13=»»;1;0); 0)); «»)}

Аналогичным образом вы можете получить список различных значений, исключая пустые ячейки и ячейки с числами:

{=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$13; ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$1:D1;$A$2:$A$13&»») + ЕСЛИ(ЕТЕКСТ($A$2:$A$13)=ЛОЖЬ;1;0); 0)); «»)}

Напоминаем, что в приведенных выше формулах A2: A13 – это исходный список, а B1 – ячейка прямо над первой позицией формируемого списка.

На этом скриншоте показан результат отбора:

Быть может, кому-то будет полезна еще одна формула –

=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$13; АГРЕГАТ(15;6;(СТРОКА($A$2:$A$13)-СТРОКА($A$2)+1) / (ПОИСКПОЗ($A$2:$A$13;$A$2:$A$13;0)=СТРОКА($A$2:$A$13)-СТРОКА($A$2)+1); ЧСТРОК($A$2:$A2)));»»)

Она работает с числами и текстом, игнорирует пустые ячейки.

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

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

Для этого используйте формулу массива, где A2: A10 — это исходный список, а B1 — это ячейка над первой ячейкой отдельного списка.

Формула массива для получения различных значений с учетом регистра (требуется нажатие Ctrl + Shift + Enter)

{=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$10; ПОИСКПОЗ(0; ЧАСТОТА(ЕСЛИ(СОВПАД($A$2:$A$10; ТРАНСП($B$1:B1)); ПОИСКПОЗ(СТРОКА($A$2:$A$10); СТРОКА($A$2:$A$10)); «»); ПОИСКПОЗ(СТРОКА($A$2:$A$10); СТРОКА($A$2:$A$10))); 0)); «»)}

Как видите, при отборе регистр здесь имеет значение.

Отбор уникальных значений по условию.

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

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

В ячейке G2 указываем нужного нам заказчика, а в H2 записываем эту формулу массива:

{=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$20; ПОИСКПОЗ(0;ЕСЛИ((($A$2:$A$20=$G$2)); СЧЁТЕСЛИ($H$1:H1;$B$2:$B$20);»»);0));»»)}

Не забудьте, что формулу массива нужно вводить в ячейку EXCEL с помощью одновременного нажатия CTRL+SHIFT+ENTER. Копируем ее по столбцу вниз при помощи маркера заполнения. Получаем список из четырех позиций.

Усложним задачу. Определим список не только для этого покупателя, но также и для определённого менеджера.

Вот наша формула массива:

{=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$20;ПОИСКПОЗ(0; ЕСЛИ((($A$2:$A$20=$G$2)+($D$2:$D$20=$H$2))=2; СЧЁТЕСЛИ($I$1:I1;$B$2:$B$20);»»);0));»»)}

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

В случае, если условий будет больше, нужно просто добавить соответствующий критерий в функцию ЕСЛИ и изменить число 2 на 3 или большее (в зависимости от количества условий).

Извлечь уникальные значения из диапазона.

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

Используем формулу массива

{=ДВССЫЛ(ТЕКСТ(МИН(ЕСЛИ(($A$2:$C$9<>»») * (СЧЁТЕСЛИ($E$1:E1;$A$2:$C$9)=0); СТРОКА($2:$9)*100 + СТОЛБЕЦ($A:$C);7^8));»R0C00″);)&»»}

Здесь A2:C9 обозначает диапазон, из которого вы хотите извлечь уникальные значения. E1 – это первая ячейка столбца, в который вы хотите поместить результат. $2:$9 указывает на строки, содержащие данные, которые вы хотите использовать. $A:$C указывает на столбцы, из которых вы берёте исходные данные. Пожалуйста, измените их на свои собственные.

Нажмите Shift + Ctrl + Enter , а затем перетащите маркер заполнения, чтобы вывести уникальные значения, пока не появятся пустые ячейки.

Как видите, извлекаются все уникальные и первые вхождения дубликатов.

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

Начиная с Excel 2007 функция удаления дубликатов является стандартной. Найти ее можно на вкладке Данные > Удаление дубликатов.

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

Использование расширенного фильтра.

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

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

Как видите, мы проверили колонку B, и затем список уникальных наименований товара, найденных в ней, поместили в столбец K.

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

Теперь немного усложним задачу.

Если требуется искать записи не по одному, а по нескольким столбцам, то можно их предварительно «склеить» при помощи функции СЦЕПИТЬ.

=СЦЕПИТЬ(A2;B2)

Записываем это в столбец F и копируем вниз. Получаем вспомогательную колонку.

В качестве исходного диапазона мы по-прежнему выбираем данные, из которых извлекаем уникальные значения. Теперь это два столбца – A и B.

Но искать уникальные мы по-прежнему можем только в одном столбце. Вот для этого нам и пригодится вспомогательная колонка F с объединенными данными. Ее то мы и указываем в поле «Диапазон условий».

Все остальное – так же, как и в предыдущем примере.

В результате мы получили все имеющиеся в таблице комбинации «Заказчик — Товар» на основе данных во вспомогательном столбце F.

Думаю, вы понимаете, что аналогичные действия можно произвести и с тремя столбцами (например Фамилия – Имя – Отчество). Главное условие – исходный диапазон должен быть непрерывным, то есть все столбцы должны находиться рядом.

Как видите формулы здесь не нужны. Однако, если исходные данные изменятся, то все манипуляции придется повторять заново.

Извлечение уникальных значений с помощью Duplicate Remover.

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

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

А теперь давайте посмотрим, как работает инструмент Duplicate Remover.

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

Выберите любую ячейку в исходной таблице и нажмите кнопку DuplicateRemover на вкладке AblebitsData в группе Dedupe.

Мастер Duplicate Remover запустится и выберет всю таблицу. Итак, просто нажмите « Далее», чтобы перейти к следующему шагу.

Выберите тип значения, который вы хотите найти, и нажмите Далее :
- Уникальные
- Уникальные + 1^е вхождения (различные)

В этом примере мы хотим извлечь различные строки, которые появляются в исходной таблице хотя бы один раз, поэтому мы выбираем опцию Unique + 1st occurences:

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

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

В нашем случае таблица имеет заголовок, поэтому отмечаем птичкой пункт My table has headers.

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

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

Также наш поиск буден нечувствителен к регистру, то есть не будем при сравнении данных различать прописные и строчные буквы. Поэтому не трогаем опцию Case-sensitive match.

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

Чтобы не менять исходные данные, выберите «Копировать в другое место» (Copy to another location), а затем укажите, где именно вы хотите видеть новую таблицу – на этом же листе (выберите параметр «Custom Location» и укажите верхнюю ячейку целевого диапазона), на новом листе (New worksheet) или в новой книге (New workbook).

В этом примере давайте выберем новый лист:

Нажмите кнопку « Готово» , и все готово!

В итоге у нас осталось всего 20 записей.

Понравился этот быстрый и простой способ получить список уникальных значений или записей в Excel? Если да, то я рекомендую вам загрузить полнофункциональную ознакомительную версию Ultimate Suite и попробовать в работе Duplicate Remover.

В Ultimate Suite for Excel также включено много других полезных инструментов, которые помогут вам сэкономить много времени. Мы о них также будем подробно рассказывать в других материалах на сайте.

Источник

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

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

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

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

=ИНДЕКС(диапазон_данных_для_выборки;МИН(ЕСЛИ(диапазон=МИН(диапазон);СТРОКА(диапазон)-СТРОКА(заголовок_столбца);””)))

В месте «диапазон_данных_для_выборки» следует указать область значений A6:A18 для выборки из таблицы (например, текстовых), из которых функция ИНДЕКС выберет одно результирующие значение. Аргумент «диапазон» означает область ячеек с числовыми значениями, из которых следует выбрать первое наименьшее число. В аргументе «заголовок_столбца» для второй функции СТРОКА, следует указать ссылку на ячейку с заголовком столбца, который содержит диапазон числовых значений.

Естественно эту формулу следует выполнять в массиве. Поэтому для подтверждения ее ввода следует нажимать не просто клавишу Enter, а целую комбинацию клавиш CTRL+SHIFT+Enter. Если все сделано правильно в строке формул появятся фигурные скобки.

Обратите внимание ниже на рисунок, где в ячейку B3 была введена данная формула в массиве:

Выборка соответственного значения с первым наименьшим числом:

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

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

Ключевую роль здесь играет функция ИНДЕКС. Ее номинальное задание – это выбирать из исходной таблицы (указывается в первом аргументе – A6:A18) значения соответственные определенным числам. ИНДЕКС работает с учетом критериев определённых во втором (номер строки внутри таблицы) и третьем (номер столбца в таблице) аргументах. Так как наша исходная таблица A6:A18 имеет только 1 столбец, то третий аргумент в функции ИНДЕКС мы не указываем.

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

Функция ЕСЛИ позволяет выбрать значение из списка по условию. В ее первом аргументе указано где проверяется каждая ячейка в диапазоне B6:B18 на наличие наименьшего числового значения: ЕСЛИB6:B18=МИНB6:B18. Таким способом в памяти программы создается массив из логических значений ИСТИНА и ЛОЖЬ. В нашем случаи 3 элемента массива будут содержат значение ИСТИНА, так как минимальное значение 8 содержит еще 2 дубликата в столбце B6:B18.

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

После того как будут отобраны все минимальные значения и сопоставлены все номера строк таблицы функция МИН выберет наименьший номер строки. Эта же строка будет содержать первое наименьшее число, которое встречается в столбце B6:B18. На основании этого номера строки функции ИНДЕКС выберет соответствующее значение из таблицы A6:A18. В итоге формула возвращает это значение в ячейку B3 в качестве результата вычисления.

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

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

Если необходимо изменить условия формулы так, чтобы можно было в Excel выбрать первое максимальное, но меньше чем 70:

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

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

Скачать пример выборки из таблицы в Excel.

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

Источник

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

Фильтра

). Произведем отбор значений из исходной таблицы с помощью

формул массива

. В отличие от применения

Фильтра

(

CTRL+SHIFT+L

или Данные/ Сортировка и фильтр/ Фильтр ) отобранные строки будут помещены в отдельную таблицу.

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

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

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

Один критерий — число

).

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

Решить эту и последующие задачи можно легко с помощью

стандартного фильтра

. Для этого выделите заголовки Исходной таблицы и нажмите

CTRL+SHIFT+L

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

Числовые фильтры…

, затем задайте необходимые условия фильтрации и нажмите ОК.

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

Другим подходом является использование

формул массива

. В отличие от

фильтра

отобранные строки будут помещены в отдельную таблицу — своеобразный

Отчет

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

Критерий (минимальную цену) разместим в ячейке

Е6

, таблицу для отфильтрованных данных — в диапазоне

D10:E19

.

Теперь выделим диапазон

D11:D19

(столбец Товар) и в

Строке формул

введем

формулу массива

=ИНДЕКС(A11:A19;НАИМЕНЬШИЙ(ЕСЛИ($E$6<=B11:B19;СТРОКА(B11:B19);»»);СТРОКА()-СТРОКА($B$10))-СТРОКА($B$10))

Вместо

ENTER

нажмите сочетание клавиш

CTRL+SHIFT+ENTER

(формула массива будет

возвращать несколько значений

Те же манипуляции произведем с диапазоном

E11:E19

(столбец Цена) куда и введем аналогичную

формулу массива

=ИНДЕКС(B11:B19;НАИМЕНЬШИЙ(ЕСЛИ($E$6<=B11:B19;СТРОКА(B11:B19);»»);СТРОКА()-СТРОКА($B$10))-СТРОКА($B$10))

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

Е6

.

Чтобы показать динамизм полученного Запроса на выборку, введем в

Е6

значение 55. В новую таблицу попадет только 2 записи.

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

Примечание

. Также для вывода отфильтрованных данных можно использовать

Расширенный фильтр

Сводные таблицы

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

Если Вам не удобно использовать

формулу массива

, которая возвращает несколько значений, то можно использовать другой подход, который рассмотрен в разделах ниже: 5.а, 7, 10 и 11. В этих случаях используются

формулы массива, возвращающие одно значение

2. Два числовых критерия (Выбрать те Товары, у которых цена попадает в диапазон)

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

Диапазон Чисел

).

Критерии (нижнюю и верхнюю границы цены) разместим в диапазоне

Е5:Е6

.

Т.е. если Цена Товара попадает в указанный интервал, то такая запись появится в новой таблице Отфильтрованные данные.

В отличие от предыдущей задачи создадим два

Динамических диапазона

: Товары и Цены (без них можно обойтись, но они удобны при написании формул). Соответствующие формулы должны выглядеть в Диспетчере имен ( Формулы/ Определенные имена/ Диспетчер имен ) следующим образом (см. рисунок ниже).

Теперь выделим диапазон

D11:D19

и в

Строке формул

введем

формулу массива

=ИНДЕКС(Товары;НАИМЕНЬШИЙ(ЕСЛИ(($E$5<=Цены)*($E$6>=Цены);СТРОКА(Цены);»»);СТРОКА(Цены)-СТРОКА($B$10))-СТРОКА($B$10))

Вместо

ENTER

нажмите сочетание клавиш

CTRL+SHIFT+ENTER

.

Те же манипуляции произведем с диапазоном

E11:E19

куда и введем аналогичную

формулу массива

=ИНДЕКС(Цены;НАИМЕНЬШИЙ(ЕСЛИ(($E$5<=Цены)*($E$6>=Цены);СТРОКА(Цены);»»);СТРОКА(Цены)-СТРОКА($B$10))-СТРОКА($B$10))

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

Е5

и

Е6

.

Чтобы показать динамизм полученного Отчета (Запроса на выборку) введем в

Е6

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

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

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

Обработка ошибок

).

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

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

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

Один критерий — Дата

).

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

= ИНДЕКС(A12:A20;НАИМЕНЬШИЙ(ЕСЛИ($E$6=B12:B20;СТРОКА(B12:B20);»»);СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))

=ИНДЕКС(B12:B20;НАИМЕНЬШИЙ(ЕСЛИ($E$6=B12:B20;СТРОКА(B12:B20);»»);СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))

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

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

Диапазон Дат

).

Обратите внимание, что столбец Дат НЕ СОРТИРОВАН.

Решение1

: Для отбора строк можно использовать

формулы массива, возвращающие одно значение

Введите в ячейку

D12

формулу массива:

=ИНДЕКС(A$12:A$20;НАИБОЛЬШИЙ(($E$6<=$B$12:$B$20)*($E$7>=$B$12:$B$20)*(СТРОКА($B$12:$B$20)-СТРОКА($B$11));$J$12-СТРОКА(A12)+СТРОКА($B$11)+1))

Примечание

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

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

скрыты с помощью Условного форматирования

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

В ячейке

J12

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

=СЧЁТЕСЛИМН(B12:B20;»>=»&$E$6;B12:B20;»<=»&$E$7)

Строки исходной таблицы, которые удовлетворяют критериям,

выделены также Условным форматированием

Решение2

: Для отбора строк можно использовать формулы массива, аналогичные Задаче2 (т.е.

формулы массива, возвращающие несколько значений

= ИНДЕКС(A12:A20;НАИМЕНЬШИЙ(ЕСЛИ(($E$6<=B12:B20)*($E$7>=B12:B20);СТРОКА(B12:B20);»»);СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))

=ИНДЕКС(B12:B20;НАИМЕНЬШИЙ(ЕСЛИ(($E$6<=B12:B20)*($E$7>=B12:B20);СТРОКА(B12:B20);»»);СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))

Для ввода первой формулы выделите диапазон ячеек

G12:G20

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

Решение3

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

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

с помощью функции СМЕЩ()

Этот пример еще раз наглядно демонстрирует насколько

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

облегчает написание формул.

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

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

Один критерий — Дата (не позже)

).

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

= ИНДЕКС(A12:A20;НАИМЕНЬШИЙ(ЕСЛИ($E$7<=B12:B20;СТРОКА(B12:B20);»»);СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))

Также в файле примера приведены формулы для условий: Не раньше (не включая); Не позже (включая); Не позже (не включая).

Эта формула введена как

формула массив

а, возвращающая множество значений (см. здесь

Формулы массива в MS EXCEL, возвращающие несколько значений

). Эту формулу можно переделать, чтобы

возвращалось только 1 значение

, см. следующую задачу 5а.

5а. Один критерий Дата (Выбрать События, которые Закончились/ не начались/ происходят на заданную дату)

Пусть имеется перечень событий и даты их начала и завершения.

Пользователю требуется найти и вывести в отдельную таблицу события, которые либо уже закончились на заданную дату, либо еще длятся, либо еще не начались.

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

= ВЫБОР($C$6;$B$7>C15;И($B$7>=B15;$B$7<=C15);$B$7

Формула возвращает ЛОЖЬ или ИСТИНА в зависимости от того удовлетворят ли Событие указанным критерием. Результат этой формулы мы будем использовать как для

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

, чтобы выделить События, так и для вывода Событий в отдельный диапазон.

Для вывода Событий в соседний диапазон используйте формулу:

= ИНДЕКС(A$15:A$21;НАИМЕНЬШИЙ(ЕСЛИ($D$15:$D$21;СТРОКА(A$15:A$21);»»);СТРОКА()-СТРОКА($B$14))-СТРОКА($B$14))

Формула делает следующее:

— если Событие удовлетворяет условию, то формула запоминает номер строки этого События: ЕСЛИ($D$15:$D$21;СТРОКА(A$15:A$21);»»)

—

Функция НАИМЕНЬШИЙ()

сортирует

полученный массив номеров строк: первыми идут номера строк Событий, которые удовлетворяют критерию;

—

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

выводит названия Событий из указанный строк.

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

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

2 критерия — Дата-Текст

).

В отличие от Задачи 5 будем отбирать строки только того Товара, который указан в критерии. Список дат должен быть

отсортирован

(для каждого из товаров), по товару сортировка не требуется.

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

= ИНДЕКС(A13:A21; НАИМЕНЬШИЙ(ЕСЛИ(($E$7=$A$13:$A$21)*($E$8>=$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 критерия должны выполняться для строки одновременно).

Примечание

. Случай, когда список несортирован, рассмотрен в статье

Поиск ДАТЫ (ЧИСЛА) ближайшей к заданной, с условием в MS EXCEL. Несортированный список

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

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

Один критерий — Текст

).

Задача решается аналогично Задачам 1 и 3. Более подробное решение см. в статье

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

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

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

2 критерия — текст (И)

).

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

= ИНДЕКС($A$11:$A$19; НАИМЕНЬШИЙ(ЕСЛИ(($F$6=$A$11:$A$19)*($F$7=$B$11:$B$19);СТРОКА($A$11:$A$19)-СТРОКА($A$10);30);СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК($A$11:$A$19)))))

Выражение ($F$6=$A$11:$A$19)*($F$7=$B$11:$B$19) задает оба условия (Товар и Месяц).

Выражение СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК($A$11:$A$19))) формирует

массив последовательных чисел

{1:2:3:4:5:6:7:8:9}, т.е. номера строк в таблице.

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

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

2 критерия — текст (ИЛИ)

).

В отличие от Задачи 7 отберем строки с товарами 2-х видов (

Условие ИЛИ

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

= ИНДЕКС(A$11:A$19; НАИБОЛЬШИЙ((($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19))*(СТРОКА($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. Отбор значений с учетом повторов

В разделе

Отбор на основании повторяемости

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

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

Отбор уникальных значений (убираем повторы из списка) в MS EXCEL

Отбор уникальных значений с суммированием по соседнему столбцу в MS EXCEL

Отбор повторяющихся значений в MS EXCEL

Отбор уникальных значений из двух диапазонов в MS EXCEL

Отбор уникальных СТРОК с помощью Расширенного фильтра в MS EXCEL

В качестве примера приведем решения следующей задачи:

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

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

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

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

=НАИМЕНЬШИЙ(СТРОКА($A$14:$A$27)*($C$14:$C$27>=$B$7)*($C$14:$C$27<=$C$7)*($D$14:$D$27>=$B$10);F14+($G$8-$G$9))

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

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

В файле примера на листе «10.Критерий — колич-во повторов» настроено

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

, которое позволяет визуально определить строки удовлетворяющие критериям, а также

скрыть ячейки, в которых формула массива возвращает ошибку #ЧИСЛО!

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

В фильтре

Сводных таблиц

MS EXCEL используется значение (Все), чтобы вывести все значения столбца. Другими словами, в

выпадающем списке

значений критерия содержится особое значение, которое отменяет сам критерий (см. статью

Отчеты в MS EXCEL

, Отчет №3).

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

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

=ЕСЛИ($C$8=»(Все)»;НАИМЕНЬШИЙ((СТРОКА($B$13:$B$26)-СТРОКА($B$12))*($D$13:$D$26>=$D$8);F13+($G$6-$G$7));НАИМЕНЬШИЙ((СТРОКА($B$13:$B$26)-СТРОКА($B$12))*($D$13:$D$26>=$D$8)*($C$13:$C$26=$C$8);F13+($G$6-$G$7)))

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

12. Актуальная цена

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

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

1) сначала сформируем перечень товаров без повторов

Отбор уникальных значений (убираем повторы из списка) в MS EXCEL

2) определяем последнюю (максимальную) дату для каждого товара с помощью формулы массива =МАКС((Таблица1[товар]=E8)*Таблица1[дата])

3) наконец, выводим актуальную цену =СУММЕСЛИМН(Таблица1[цена];Таблица1[товар];E8;Таблица1[дата];F8)

Для товара не должно быть повторов дат, иначе цены будут суммироваться (если повторяется последняя дата).

В файле примера приведено решение на листе 12. Актуальная цена.

Источник

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

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

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

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

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

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

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

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

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

Задача

Решение

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

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

СЧЁТЕСЛИ

СЧЁТЕСЛИМН

СУММЕСЛИ

СУММЕСЛИМН

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Базовые формулы для получения уникальных значений.

Как извлечь различные значения.

Как извлечь значения, игнорируя пустые ячейки

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

Отбор уникальных значений по условию.

Извлечь уникальные значения из диапазона.

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

Использование расширенного фильтра.

Извлечение уникальных значений с помощью Duplicate Remover.

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

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

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

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

2. Два числовых критерия (Выбрать те Товары, у которых цена попадает в диапазон)

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

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

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

5а. Один критерий Дата (Выбрать События, которые Закончились/ не начались/ происходят на заданную дату)

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

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

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

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

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

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

12. Актуальная цена

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