Применение функции ВЫБОР в Microsoft Excel
При работе в Excel пользователи иногда сталкиваются с задачей произвести выбор из списка определенного элемента и на основании его индекса присвоить ему указанное значение. С данной задачей прекрасно справляется функция, которая так и называется «ВЫБОР». Давайте узнаем подробно, как работать с данным оператором, и с какими поставленными проблемами он может справиться.
Использование оператора ВЫБОР
Функция ВЫБОР относится к категории операторов «Ссылки и массивы». Её целью является выведение определенного значения в указанную ячейку, которому соответствует номер индекса в другом элементе на листе. Синтаксис этого оператора следующий:
Аргумент «Номер индекса» содержит ссылку на ячейку, где находится порядковый номер элемента, которому следующей группой операторов присваивается определенное значение. Этот порядковый номер может варьироваться от 1 до 254. Если задать индекс, превышающий данное число, то оператор выведет в ячейку ошибку. Если в качестве данного аргумента ввести дробное значение, то функция воспримет его, как ближайшее к данному числу меньшее целое значение. Если задать «Номер индекса», для которого нет соответствующего аргумента «Значение», то оператор будет возвращать в ячейку ошибку.
Следующая группа аргументов «Значение». Она может достигать количества 254 элементов. При этом обязательным является аргумент «Значение1». В данной группе аргументов указываются те значения, которым будет соответствовать номер индекса предыдущего аргумента. То есть, если в качестве аргумента «Номер индекса» выступает число «3», то ему будет соответствовать значение, которое внесено, как аргумент «Значение3».
В качестве значений могут выступать разнообразные виды данных:
- Ссылки;
- Числа;
- Текст;
- Формулы;
- Функции и т. д.
Теперь давайте рассмотрим конкретные примеры применения данного оператора.
Пример 1: последовательный порядок расположения элементов
Давайте посмотрим, как действует данная функция на простейшем примере. У нас имеется таблица с нумерацией от 1 до 12. Нужно согласно данным порядковым номерам с помощью функции ВЫБОР указать наименование соответствующего месяца во второй колонке таблицы.
- Выделяем первую пустую ячейку столбца «Наименование месяца». Кликаем по значку «Вставить функцию» около строки формул.
- Производится запуск Мастера функций. Переходим в категорию «Ссылки и массивы». Выбираем из перечня наименование «ВЫБОР» и щелкаем по кнопке «OK».
После этого нам предстоит вручную вбить в группу полей «Значение» наименование месяцев. Причем каждому полю должен соответствовать отдельный месяц, то есть, в поле «Значение1» записываем «Январь», в поле «Значение2» — «Февраль» и т. д.
Пример 2: произвольный порядок расположения элементов
В предыдущем случае мы применили формулу ВЫБОР, когда все значения номеров индекса были расставлены по порядку. Но как работает данный оператор в случае, если указанные значения перемешаны и повторяются? Давайте рассмотрим это на примере таблицы с успеваемостью школьников. В первом столбце таблицы указана фамилия ученика, во втором оценка (от 1 до 5 баллов), а в третьем нам предстоит с помощью функции ВЫБОР дать данной оценке соответствующую характеристику («очень плохо», «плохо», «удовлетворительно», «хорошо», «отлично»).
-
Выделяем первую ячейку в колонке «Описание» и переходим при помощи того способа, о котором уже шел разговор выше, в окно аргументов оператора ВЫБОР.
В поле «Номер индекса» указываем ссылку на первую ячейку столбца «Оценка», в которой содержится балл.
Группу полей «Значение» заполняем следующим образом:
- «Значение1» — «Очень плохо»;
- «Значение2» — «Плохо»;
- «Значение3» — «Удовлетворительно»;
- «Значение4» — «Хорошо»;
- «Значение5» — «Отлично».
Пример 3: использование в комбинации с другими операторами
Но гораздо продуктивнее оператор ВЫБОР можно использовать в комбинации с другими функциями. Посмотрим, как это делается на примере применения операторов ВЫБОР и СУММ.
Имеется таблица реализации продукции по торговым точкам. Она разбита на четыре столбца, каждый из которых соответствует определенной торговой точке. Выручка указана отдельно за определенную дату построчно. Наша задача — сделать так, чтобы после ввода номера торговой точки в определенную ячейку листа отображалась сумма выручки за все дни работы указанного магазина. Для этого мы и будем использовать комбинацию операторов СУММ и ВЫБОР.
- Выделяем ячейку, в которой будет выводиться результат в виде суммы. После этого щелкаем по уже знакомому нам значку «Вставить функцию».
- Активируется окошко Мастера функций. На этот раз перемещаемся в категорию «Математические». Находим и выделяем наименование «СУММ». После этого щелкаем по кнопке «OK».
- Происходит запуск окошка аргументов функции СУММ. Данный оператор используется для подсчета суммы чисел в ячейках листа. Его синтаксис довольно прост и понятен:
То есть, аргументами данного оператора обычно являются либо числа, либо, еще чаще, ссылки на ячейки, где содержаться числа, которые необходимо просуммировать. Но в нашем случае в виде единственного аргумента выступит не число и не ссылка, а содержимое функции ВЫБОР.
В поле «Значение1» нужно вписать координаты столбца «1 торговая точка». Сделать это довольно просто. Устанавливаем курсор в указанное поле. Затем, зажав левую кнопку мыши, выделяем весь диапазон ячеек столбца «1 торговая точка». Адрес тут же отобразится в окне аргументов.
Аналогичным образом в поле «Значение2» добавляем координаты столбца «2 торговая точка», в поле «Значение3» — «3 торговая точка», а в поле «Значение4» — «4 торговая точка».
Важно учесть, что вводить можно только числа от 1 до 4, которые будут соответствовать номеру торговой точки. Если вы введете любое другое число, то формула опять выдаст ошибку.
Как видим, функция ВЫБОР при правильном её применении, может стать очень хорошим помощником для выполнения поставленных задач. При использовании её в комбинации с другими операторами возможности существенно увеличиваются.
Как в excel выбрать значение из таблицы соответствующее условию
Вывод отобранных значений в отдельный диапазон в Excel. Бесплатные примеры и статьи.
Смотрите также из тех, гдекарандаши=ИНДЕКС(A1:G13;ПОИСКПОЗ(C16;D1:D13;0);2) точках: же присваивает новый (постоянный)не тот, буду: Вторая лучше в данные – «Хлеб». дням.. Производим клик по«OK» указанной области будутследует ввести адресСЧЁТЕСЛИМН( диапазон поиска содержит
Поиск позиции ТЕКСТа в MS EXCEL с выводом значения из соседнего столбца
Суть запроса на выборку товар был,ФункцияФормула рассчитывает выручку в формат для соответствующих разбираться, а что таком виде:А в ячейкеПередвигаемся во вкладку кнопке.
отражены в окне. строки, в которой. Она также относится«равно» повторяющиеся значения, то – выбрать из
Задача
БумагаручкиПОИСКПОЗ магазине, заданном пользователем. ячеек. Новый формат такое 100000 -=ИНДЕКС(C6:C101;B3+ЕСЛИ(B2=»м»;48)-17)
Решение
- «Главная»«OK»Запускается окно аргументов функции
- Далее устанавливаем курсор в расположены данные по
- к статистической группе), второй столбец из
исходной таблицы строки,, т.к. ЛОЖЬ функцией), а диапазоном дляищет в столбце В ячейке А8 заранее определен пользователем значение в формуле?SerjVorotilov формулу, которая посчитает. Клацаем по пиктограмме.СУММЕСЛИ поле выручке Магазина 1
операторов. Задачей
«<>» таблицы выше поясняет
удовлетворяющие определенным критериям
МИН выборки — столбецD1:D13
можно изменить номер
в параметрах правила подскажите. плиз…: Смотри прикрепленный файл.
все заказы Иванова
«Условное форматирование»Производится запуск окошка аргументов. В нём имеется
за неделю. ДляСЧЁТЕСЛИМН(
какое значение будет
(подобно применению стандартногоигнорируется. При желании, с ценами.значение артикула из торговой точки –ВЫБОР условного форматирования (зеленая
на хлеб. Формула
, размещенной в блоке
оператора, наименование которого
три поля, соответствующих
. Тут нам нужно этого ставим курсорявляется подсчет ячеек«не равно» выведено (обычно возвращается Фильтра). Произведем отбор значений можно выделить мышьюДля будущего удобства, конвертируем ячейки вернет для функции заливка).: Все заработало и
1. В таблице такая.«Стили» было указано выше. аргументам указанного оператора. указать нижнюю границу в поле и в указанном массиве,). Например, если задать первое значение, удовлетворяющее из исходной таблицы всю функцию ЕСЛИ(…) исходный диапазон сC16 СУММ ссылку наФункция ВЫБОР находит и протянулось, большое спасибо 1 столбец B1:B5
=СУММ((A2:A9=F2)*(B2:B9=F1)*C2:C9)на ленте. ОткрываетсяУстанавливаем курсор в полеВ поле значений в ячейках, выделяем соответствующую строку которые удовлетворяют определенному выражение
критерию). с помощью формул в строке формул ценами в «умную. Последний аргумент функции другой интервал. Если возвращает значение из вам. и строка C3:G3В первых скобках список действий. Клацаем«Диапазон суммирования»«Диапазон»
которые будут принимать в таблице. Координаты набору условий. Именно«, то при подсчетеЕсли диапазон поиска содержит
Использование критериев в 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) не повторяются. заказчиков, из которого
написанного в ячейке содержат» область строки ячейки, в которых в поле. аргументу предыдущего оператора. других вариантов, о поиска постоянно вводятся в столбце, сЭтот вариант использует малоизвестнуюТаблица1ИНДЕКС результат подсчета 2,
знач. 1; знач. дату в строке позволяет из раскрывающегося F1 (Иванов).. В первом поле
«Диапазон условия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 и
- оператор позволяет задать оператор на практике.
- проверки наличия дубликатов использовать также и
- и требует небольшогоили
представлена выручка по форматирование (см. статью задачу в случаеКак видите, зеленые ячейки. При желании, стандартное нужен регион, т.е.Данная функция хорошо обрабатывает ячейку с числом нужная дата. Вот значению I4 строго Вокруг этой формулы указываем само значение,
будет отобразить координаты. отбора: же алгоритму, что«Условие» пяти магазинам за Выделение повторяющихся значений). текстовых значений. с результатами транспонированы
имя второй столбец). в качестве значений от 1 до здесь приходит на соответствует J4, I5 появятся фигурные скобки. больше которого требуется строки с датами.В поле«. и для первого
представляет собой критерий, неделю. Нам нужноДля организации динамической сортировкиПусть в диапазоне из столбца вТаблица1enzo простые списки чисел. 254, массивом или помощь ПРОСМОТР(). — J5, и Так обозначаются формулы отформатировать элементы таблицы. Производим зажим левой«Диапазон суммирования»После того, как все аргумента данной группы. который определяет, какие узнать количество дней пополняемого диапазона поискаА4:В15
строку и надможно подкорректировать на: Уважаемые, добрый день! Поэтому с ее формулой.vikttur т.д.
вкладке Есть у меня помощью можно вычислитьЗнач. 1; знач. 2;: Это я выше.Вопрос: о формулах массива, 14000. Чтобы выбрать
-
выделяем все даты значения которой, отвечающие клацаем по кнопке«Условие2» массива данных войдут в которых в из статьи Сортированный перечнем сотрудников и
щелкнуть в любую выбираю определенное условиеТаблица с номерами месяцев до 254, из по продажам отсортированные1. В ячейку Получилось так.«Формат…»«Условие1» случае это значенияПрограмма выдает результат расчета.
, Каждой заданной области 15000 рублей. не просто табличнымТребуется, введя в ячейкуБаза_данных ячейку нашей «умной» (столбец постоянный к и кварталов: которого выбирается значение по возрастанию относительно К4 — фамилию
Так можно выбирать и.. Первым условием является выручки строки Как видим, итоговое
«Условие4» данных нужно указыватьВыделяем элемент листа, в редактором, а ещёD4
СУММЕСЛИ
«Магазин1» значение равно 5.и условие отдельно, даже который оператор будет и мощнейшим приложениемфамилию сотрудника, вывести таблица вместе с таких таблицах и = 2) , начался в апреле,
номеру индекса. Первое
определить в какие получающуюся на пересечении из таблицы, любого во вкладку будут суммироваться данные. Выделяем соответствующий массив Это значит, что«Условие5» в том случае, выводить результат вычисления. для различных вычислений. в другой ячейке
заголовками. их скрытых возможностях и вывожу список месяцы 4, 5 значение – обязательный дни сумма транзакции даты, выбираемой пользователем диапазона.«Заливка» не ранее 09 элементов листа. в 5 днях
вносим соответственно значения если оно совпадает. После этого щелкаем Не в последнюю его зарплату. РешениеПоле можно почитать здесь. для распечатки. Делал и 6 попали аргумент. Последующие – превысила 5000. Следует в ячейке J3,В Excel есть. Из предложенных вариантов
марта. Поэтому вводимПосле того, как произведено из исследуемых семи«>15000 Обязательно требуется, чтобы по пиктограмме очередь такая возможность приведено в файле- название столбцаНачиная с версии Excel
-
это фильтром , в первый квартал. нет. Список аргументов-значений выбрать все суммы и значения ячейки функции, которые считают
I4, находящегося в не все ячейки, синий, щелкая по«>08.03.2017» данных, жмем на магазине была в«>24000» в качестве областей. функциям. С помощьюАлгоритм решения задачи следующий: из которого выбирается функции Microsoft Excel
удалял столбцы. Попросили функции, номера кварталов на ячейки, имена, выделить их цветом. Таблице 1 в а выборочно, по нему левой кнопкой. кнопку
интервале от 14000, условия, имели одинаковоеПроизводится запуск некоторых функций (операторов)находим в списке кодов минимальное значение. наконец появились функции, автоматизировать)) Хотят чтобы необходимо вводить в формулы, функции или
Вместо того чтобы тратить столбце выбранной даты; условию, которое мы мыши. После того,Перемещаемся к аргументу«OK»
СУММЕСЛИМН
«Диапазон условия2».Ещё одним оператором, которыйи столбцов.. Совершаем перемещение в условия вычисления, которые критерием;- таблица с нашу задачу -
крови», куда напишут
каком они находятсяЕсли указать номер индекса анализ данных таблицы, К4 — фамилию Например, посчитать сумму отобразился в области
. Тут нужно внестиПосле этого в предварительно использует критерии, является«>32000»Для того, чтобы задать
блок принято называть критериями.определяем номер позиции (строку) условиями отбора, состоящая
это функции цифру 2 и в таблице.
1, то функция будем использовать правило из диапазона B1:B5, заказа определенного покупателя,«Образец» те же координаты, указанный элемент рабочегоСУММЕСЛИ. Как нетрудно догадаться, несколько параметров одной«Статистические» Давайте подробнее узнаем, найденного значения; (минимально) из двухМИНЕСЛИ (MINIFS) из таблицы будетВ ячейку D8 пользователь
-
вернет первое значение условного форматирования, которое получающуюся на пересечении итоги конкретного ученика,, клацаем по кнопке
посчитать данные за«OK» в поле результат обработки данных предыдущих функций, он интервалу выручки, превышающую области данных, например, выделяем наименование их использовать при столбца той же по которому идетМАКСЕСЛИ (MAXIFS) с группой 2, В ячейке D9 индекс равен 2 выделит цветом все
в ячейке J3, определенную дату, т.д..«Диапазон условия1» функцией относится к математическому норму для соответствующего чтобы подсчитать количество«СЧЁТЕСЛИ» работе в Экселе. строки.
проверка (. Синтаксис этих функции но с меньшим функция ВЫБОР вычисляет – второе значение. необходимые суммы. Пример и значения ячейки Про одну изАвтоматически происходит возврат к. Делаем это тем
СУММЕСЛИ блоку операторов. Его магазина. ячеек, в которых. Затем следует клацнутьСкачать последнюю версиюРешение практически аналогично поискуТовар очень похож на определенными столбцами. номер финансового квартала.
И так далее. таблицы отображен ниже I5, находящегося в таких функций читайте окну генерации правила же способом, то. В нашем случае задачей является суммированиеПосле того, как был расположены величины больше
по кнопке Excel числового значения из) и критерия (СУММЕСЛИМН (SUMIFS)Пока ломаю головуМожно так же вычислять Если список аргументов
на рисунке: Таблице 1 в в статье «Функция форматирования. В нём есть, путем выделения он равен 47921,53. данных в ячейках, произведен ввод всех определенного числа, но«OK»Критерии представляют собой условия, статьи Поиск позицииБумага, Карандаши, Ручки
: как можно сделать грядущие даты. Эту состоит из конкретных
Условное форматирование
).=МИНЕСЛИ(Диапазон_чисел; Диапазон_проверки1; Условие1; Диапазон_проверки2; это. задачу она решает значений, то формула дат транзакций.и т.д.В Excel можно«Образец»Устанавливаем курсор в поле
начиная с 11.03.2017, условию. Синтаксис таков: 10 полей), жмем следует в качестве
-
Происходит активация окна аргументов выполняет определенные действия. соответствующего значения изЭто обычная формула (не Условие2 . )
Udik работе и сдает на ячейки, то
«ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило». котором ищется значение
отчетов, расположенных на кнопке суммироваться выручка, должны рубля. ячеек, которые будут выводит результат на же массив. Но ячеек, среди которых присутствует выражение но для ее в той же выбирается минимальное или: без файла сложно их начальнику каждый функция вернет ссылки.В появившемся окне «Создание ячеек диапазона I4:I8, разных листах. Подробнее,«OK» быть не позже
Завершим изучение операторов, которые
Сложить выборочно данные из таблицы Excel.
проверяться на соблюдение экран. Как видим, при этом в будет производиться подсчет.«ЕСЛИ» решения можно использовать категории можно найти максимальное отвечать. А так вторник. Можно рассчитатьВЫБОР возвращает ссылку на правила форматирования» выберите нужное для возвращения смотриет в статье. 
13 марта. Поэтому используют критерии, остановившись условия. По сути, он равен числу
качестве соответствующих аргументов В нашем случае. К данной группе и другие функции функцииДиапазон_проверки — например, расширенный
дату следующего вторника. интервал В1:В7. А опцию: «Использовать формулу конкретной фамилии в «Ссылки в ExcelПосле выполнения последнего действия,
записываем следующее выражение:
на функции он задается по 3. Это означает,«Условие» следует выделить содержимое операторов, прежде всего,
(про функцию ВПР()БДСУММ (DSUM)- диапазон, который фильтр.В первом столбце вспомогательной функция СУММ использует
для определения форматированных соответствующий диапазон К4:К8
на несколько листов все ячейки выделенного«.СУММЕСЛИМН тому же принципу, что в трехследует указывать разные строки нужно отнести см. эту статью)., проверяется на выполнениеenzo таблицы – номера 
этот результат в ячеек».Guest сразу».
массива, где содержитсяПереходим в поле. Задачей данной математической что и одноименный днях из анализируемой критерии.«Магазин 2»СЧЁТЕСЛИРешениеДМАКС (DMAX) условия: Извиняюсь))) прикладываю! дней недели. В качестве аргумента.В поле ввода введите
:PerineiKa число большее, чем«Диапазон условия2» функции является суммирование аргумент функции недели выручка воНа примере все той, в которой расположены,Если несколько значений удовл.
автоматический выбор значения из таблицы по двум критериям
,Условиеsboy
третьем столбце –Аргументы-значения могут быть представлены
формулу: 5000′ >Микки: Доброго времени, уважаемые 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как раз нужно инструментами данного табличногопервое не часто, то
вас (или тех, макросом А в ячейку
Как выбрать суммы по условию в 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»— аргумент, представляющий на конкретную область
Функция ВЫБОР в 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)
Функция ВЫБОР в 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сложить выборочно данные
Поиск нужных данных в диапазоне
года сумма выручки нужно будет подсчитать применение данного оператора категории. выводит результат в«Критерий»возвращается ошибка #ЧИСЛО! перетащите поле G5:G6.СУММЕСЛИ (SUMIF) не пожалейте пятиПо такому же принципу
что в первом строки, в которой дня рыла по из ячеек при сложении её доход, который принес на практике. На«10 недавно использовавшихся»Перейдя в элемент листа, который— это аргумент,Для функции ВПР() требуется,Товар
Давайте разберем логику работыи минут, чтобы сэкономить можно выводить отметки,
и третьем аргументах в параллельном столбце
сайтам и литературе!
. Так можно посчитать, за дни, в Магазин 1 за основе той же. Выделяем его иМастер функций был выделен перед который задаёт условие, чтобы столбец, пов область строк, этой формулы поподробнее.СРЗНАЧЕСЛИ (AVERAGEIF) себе потом несколько баллы, времена года мы используем абсолютные и находится фамилия, Стыдно было за
например, сумму товарного которых она превышает период с 09 таблицы перед нами щелкаем по кнопке, снова перемещаемся в активацией что именно должны которому производится поиск, а Функция ЕСЛИ проверяети их аналоги,
Как вывести список из таблицы согласно определенному условию (Формулы/Formulas)
часов. прописью. адреса ссылок. В которая нам нужна. свою неграмотность, тут чека, т. д. 14000 рублей, составила по 13 марта стоит задача подсчитать«OK» блокМастера функций содержать ячейки указанной был левее столбца,Цену каждую ячейку массива позволяющие искать количество,Если же вы знакомыТеперь рассмотрим можно склонять то время какПостарался написать максимально почитала и решилась У нас есть
62491,38 рубля. 2017 года. При сумму выручки в
.«Статистические». Как видим, в области, чтобы быть
который используется дляв область значений. из столбца сумму и среднее с ВПР, то
слова с помощью во-втором аргументе применяется
понятно) задать вопрос. список товаров, вПоследним, описанным нами, инструментом, этом при суммировании Магазине 1 заОткрывается уже знакомое нам
. В перечне следует данном случае результат включенными в подсчет.
вывода. Обойти это Чтобы заставить своднуюТовар
в таблице по — вдогон - Excel. Например, слово смешанная ссылка наСпасибо .
дай Вам Бог котором указаны заказчики. при работе с дохода должны учитываться период, начиная с окошко аргументов оператора отыскать наименование
Поиск минимального или максимального значения по условию
равен числу 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 нас есть такая которые отвечают заданным вывода итога и пиктограмме
и, произведя зажим произвести нажатие на
- критерий содержится. При о функции ВПР(). выберите в контекстном ему значение из или максимум по
- , владение которыми весьмаС помощью функции ВЫБОР берет из нее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 которому хотим посчитать торговых точек по
Функция ВЫБОР в Excel ее синтаксис и примеры использования
Функция ВЫБОР находит и возвращает значение из списка аргументов, используя номер индекса. Может обработать до 254 значений. Имеет простой синтаксис, но достаточно широкие возможности. Рассмотрим лучшие из них на конкретных практических примерах.
Аргументы и особенности синтаксиса
Синтаксис функции: =ВЫБОР( номер индекса; знач. 1; знач. 2; … ).
- Номер индекса – порядковый номер выбираемого аргумента из списка значений. Может быть числом от 1 до 254, ссылкой на ячейку с числом от 1 до 254, массивом или формулой.
- Знач. 1; знач. 2; … — список аргументов от 1 до 254, из которого выбирается значение или действие, соответствующее номеру индекса. Первое значение – обязательный аргумент. Последующие – нет. Список аргументов-значений – числа, ссылки на ячейки, имена, формулы, функции или текст.
Если указать номер индекса 1, то функция вернет первое значение их перечня. Если индекс равен 2 – второе значение. И так далее. Если список аргументов состоит из конкретных значений, то формула ВЫБОР возвращает одно из значений согласно индексу.
Если аргументы – ссылки на ячейки, то функция вернет ссылки.
ВЫБОР возвращает ссылку на интервал В1:В7. А функция СУММ использует этот результат в качестве аргумента.
Аргументы-значения могут быть представлены отдельными значениями:
Особенности использования функции:
- Если индекс представлен дробью, то функция возвращает меньшее целое значение.
- Если индекс – массив значений, то функция ВЫБОР вычисляет каждый аргумент.
- Если индекс не совпадает с номером аргумента в списке (меньше 1 или больше последнего значения), то функция выдает ошибку #ЗНАЧ!.
Функция ВЫБОР в Excel: примеры
Функция ВЫБОР решает задачи по представлению значений из списка в Excel. Например, диапазон А2:А8 содержит номера недели от 1 до 7. Необходимо отобразить день недели прописью, то есть «понедельник», «вторник», «среда», «четверг», «пятница», «суббота», «воскресенье».
По такому же принципу можно выводить отметки, баллы, времена года прописью.
Теперь рассмотрим можно склонять слова с помощью Excel. Например, слово «рубль»: «0 рублей», «1 рубль», «2 рубля», «3 рубля», «4 рубля», «5 рублей» и т.д.
С помощью функции ВЫБОР можно вернуть ссылку на диапазон. Это позволяет делать вычисления над массивами данных по заданному пользователем критерию. Рассмотрим пример суммирования выручки в заданном пользователем магазине.
Имеются данные по выручке в нескольких торговых точках:
Формула рассчитывает выручку в магазине, заданном пользователем. В ячейке А8 можно изменить номер торговой точки –ВЫБОР вернет для функции СУММ ссылку на другой интервал. Если поставить в ячейке А8 цифру 2, формула подсчитает выручку для второго магазина (результат СУММ для диапазона В2:В5).
С помощью функции ВЫБОР можно задать аргумент для функции СУММ так, чтобы получить результат подсчета 2, 3, 4 и т.д. первых значений диапазона:
Формула суммирует диапазон А1:А4. Вторая часть диапазона функции СУММ задана с помощью функции ВЫБОР.
Данная функция хорошо обрабатывает в качестве значений простые списки чисел. Поэтому с ее помощью можно вычислить по номеру месяца финансовый квартал.
Таблица с номерами месяцев и кварталов:
Так как финансовый год начался в апреле, месяцы 4, 5 и 6 попали в первый квартал. При введении аргументов функции, номера кварталов необходимо вводить в том порядке, в каком они находятся в таблице.
В ячейку D8 пользователь вводит номер месяца. В ячейке D9 функция ВЫБОР вычисляет номер финансового квартала.
Можно так же вычислять грядущие даты. Эту задачу она решает в совокупности с функцией ДЕНЬНЕД. Например, пользователь делает небольшие отчеты о проделанной работе и сдает их начальнику каждый вторник. Можно рассчитать дату следующего вторника.
В первом столбце вспомогательной таблицы – номера дней недели. В третьем столбце – количество дней, которое нужно прибавить к текущей дате, чтобы получить следующий вторник. Например, к понедельнику необходимо добавить 1 день, ко вторнику – 7 дней (до следующего вторника).
В ячейку F2 запишем текущую дату (СЕГОДНЯ()). А в ячейку F3 – формулу для расчета даты следующего вторника:
Индекс определяется с помощью функции ДЕНЬНЕД, которая возвращает для заданной даты соответствующего дня недели.
Поиск нужных данных в диапазоне
Как использовать функцию ВПР (VLOOKUP) для поиска и выборки нужных значений из списка мы недавно разбирали. Если вы еще с ней не знакомы — загляните сюда, не пожалейте пяти минут, чтобы сэкономить себе потом несколько часов.
Если же вы знакомы с ВПР, то — вдогон — стоит разобраться с похожими функциями: ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) , владение которыми весьма облегчит жизнь любому опытному пользователю Excel. Гляньте на следующий пример:
Необходимо определить регион поставки по артикулу товара, набранному в ячейку C16.
Задача решается при помощи двух функций:
=ИНДЕКС( A1:G13 ;ПОИСКПОЗ( C16 ; D1:D13 ;0);2)
Функция ПОИСКПОЗ ищет в столбце D1:D13 значение артикула из ячейки C16 . Последний аргумент функции 0 — означает поиск точного (а не приблизительного) соответствия. Функция выдает порядковый номер найденного значения в диапазоне, т.е. фактически номер строки, где найден требуемыый артикул.
Функция ИНДЕКС выбирает из диапазона A1:G13 значение, находящееся на пересечении заданной строки (номер строки с артикулом выдает функция ПОИСКПОЗ) и столбца (нам нужен регион, т.е. второй столбец).
Ссылки по теме
Не за что!
А скажите, пожалуйста, можно ли чтобы эта чудесная формула искала бы значения на разных листах? Я поробовала сделать вот так:
=ЕСЛИОШИБКА(ИНДЕКС(Лист2!C700:F900;$C$700:$F$900;ПОИСКПОЗ($A700;$C$700:$C$900;0);1);0), но ничего не получается.
Спасибо.
Без файла сказать трудно. Но у вас с ходу в формуле выделенное красным — это что?
=ЕСЛИОШИБКА(ИНДЕКС(Лист2!C700:F900; $C$700:$F$900; ПОИСКПОЗ($A700;$C$700:$C$900;0);1);0)
У функции ИНДЕКС три аргумента, а у вас — четыре. Что-то лишнее
При поиске ближайшего наименьшего (последний аргумент функции ПОИСКПОЗ равен 1) таблица, где ищем, должна быть обязательно отсортирована по возрастанию.
При поиске ближайшего наибольшего — по убыванию.
Спасибо Большое Вам, Николай! Не только за этот пример, а в общем — за весь Сайт.
Николай, спасибо за урок!
А не лучше ли сделать то же самое с помощью функции ПРОСМОТР?
=ПРОСМОТР(C16;D2:D13;B2:B13)
Помоему проще и для понимания и для реализации.
Добрый день всем!
Это мой первый комментарий.
В первую очередь хотел бы поблагодарить Николая, за его труд, за этот сайт. Все очень доходчиво, структурировано и очень полезно в повседневной работе. Данный ресурс у меня на первом месте во вкладках по эксель!
Ну, а теперь по вопросу если можно, касаемо функции индекс, которая применяется в данном примере.
Скажите пожалуйста, а как быть в случае нахождения таблицы в другом соседнем листе.
Метод указанный выше работает ровно до 3-го поля функции индекс, где надо указать искомый столбец в виде цифры, откуда мы забираем значение (имя клиента, регион и т.д.)
Как корректно выполнить этот этап, чтобы забрать эти значения из соседнего листа?
Заранее благодарю за помощь!
Не всегда помогает. Сегодня весь день убил на реализацию этого метода. Все в толк не возьму — или криво офис на комп встал. или одно из двух. то #ссылку возвращает то #н/д .
=ИНДЕКС(Диллеры!$A$4:$B$103;C3;2)
на одном листе заработало после милионной попытки на другом листе вообще не пашет. почему на первом заработало — непонятно. Просто в какой то момент выдало нужный результат и все. Хотя ничего не трогал в формуле.
Если вставлять ПОИСКПОЗ вообще никак не отрабатывает.
Функция по потенциалу понравилась, но как отрабатывает конкретно у меня — нет.
ВПР отрабатывает на отлично, но только на одном листе. С другого тоже не хочет хоть разбейся.
Очень понравился Ваш ресурс. Подчерпнул. Спасибо Вам.
Благодарю Николая за невероятную комбинацию функций ВПР; СМЕЩ; ПОИСКПОЗ; СЧЁТЕСЛИ,
которая подарила мне уйму свободного времени. Очень грамотно.8)
Николай, большое спасибо за Ваши уроки!
Просмотрев этот урок и скачав Ваш пример, нашёл решение своих задач. В частности вместо указания номера столбца вставил ПОИСКПОЗ
Ещё раз благодарю Вас!
С уважением, Вячеслав!
Ну да, хорошее решение, чтобы не считать номер колонки вручную
Здравствуйте, а если шапка таблицы многослойная решение есть?
Многослойная шапка — например в строке 2 условия в столбце 2 условия а не по одному.
т.е.
=ИНДЕКС(Диапазон значений таблицы;
ПОИСКПОЗ(значение шапки столбца А;диапазон шапки столбца А;0);
ПОИСКПОЗ(значение шапки строки 1;диапазон шапки столбца 1;0);
а мне надо еще 2 условия добавить
ПОИСКПОЗ(значение шапки столбца B;диапазон шапки столбца B;0);
ПОИСКПОЗ(значение шапки строки 2;диапазон шапки столбца 2;0);
т.е. значение готовое выберется не по 2-м условиям а по четырем
Подскажите пожалуйста как это реализовать в одной формуле.
Спасибо!
Вы так быстро ответили , что я таблицу неуспел нарисовать ))
=ИНДЕКС(Диапазон значений таблицы;
ПОИСКПОЗ(значение шапки столбца А;диапазон шапки столбца А;0);
ПОИСКПОЗ(значение шапки строки 1;диапазон шапки столбца 1;0);
| Усл 2 | q | q | q | h | h | h | |||
| Усл 4 | x | y | z | x | y | z | |||
| список выбора условия 1 | k | Усл 1 | Усл 3 | ||||||
| список выбора условия 2 | q | j | b | а | б | в | г | д | е |
| список выбора условия 3 | j | s | е | ж | з | и | к | л | |
| список выбора условия 4 | j | f | м | н | о | п | р | с | |
| решение | т | k | b | т | у | ф | х | ц | ч |
| k | s | ш | щ | ъ | ы | ь | э | ||
| k | f | ю | я | — | — | — | — |
а мне надо еще 2 условия добавить
ПОИСКПОЗ(значение шапки столбца B;диапазон шапки столбца B;0);
ПОИСКПОЗ(значение шапки строки 2;диапазон шапки столбца 2;0);
т.е. значение готовое выберется не по 2-м условиям а по четырем
| Усл 2 | q | q | q | h | h | h | |||
| Усл 4 | x | y | z | x | y | z | |||
| список выбора условия 1 | k | Усл 1 | Усл 3 | ||||||
| список выбора условия 2 | h | j | b | а | б | в | г | д | е |
| список выбора условия 3 | s | j | s | е | ж | з | и | к | л |
| список выбора условия 4 | j | f | м | н | о | п | р | с | |
| решение | #ССЫЛ! | k | b | т | у | ф | х | ц | ч |
| должно быть ы | k | s | ш | щ | ъ | ы | ь | э | |
| k | f | ю | я | — | — | — | — |
Антон, по такой картинке качественно ответить нереально. Я бы склеил условия из шапки попарно с помощью функции СЦЕПИТЬ и получил бы в итоге одно условие, по которому бы и делал обычный поиск. Лучше сделайте тему на форуме и приложите нормальный файл с примером, тогда ответ будет точнее
Сам себе и отвечу . =ИНДЕКС( A1:G13 ;ПОИСКПОЗ( C16 ; D1:D13 ;0);2)
(Последний аргумент функции 0 — означает поиск точного (а не приблизительного) соответствия. )
Искомое значение в ячейке C16 (авс)
0 заменил на 1 и почистил казалось-бы пустые ячейки (раньше формулой было записано «»;) в строке.
И тут появляется «НО» — если в строке D1:D13 пустые ячейки появляются пару раз (например:
D1 D2 D3 D4 D5 D6 D7 D8 . D13
(авс) (авс) (авс) ( ) ( ) (авс) (авс) ( ) . (авс), то формула ПОИСКПОЗ выдаст значение D7, хотя должно быть D13.
Встречал на каком-то форуме ПОИСК (Ctrl+F) — значение (авс) — ВВОД (Shift+Enter). А как это записать формулой?
| если в строке D1:D13 |
D1:D13 — это столбец, а не строка
Если вы имели ввиду вопрос «как сделать так, чтобы формула находила не первое встретившееся, а последнее значение», то тут проще всего макросом, наверное — писать на VBA функцию аналогичную ВПР.
Николай, во-первых, хочу сказать Вам огромное спасибо за ваш труд и за ту бесценную информацию, которую Вы двигаете в массы.
Думаю несколько универсализировал формулу в примере (не понимаю как можно прикладывать файлы к сообщению. ):
яч. E16 =ИНДЕКС($A$2:$G$13; ПОИСКПОЗ($C$15; $D$2:$D$13; 0); ПОИСКПОЗ(D16; $A$1:$G$1; 0))
Потом просто растягиваем. Но, чтобы это работало нужно предварительно задать списки данных для массива D15:D18 — это тоже делает отчет удобнее. Теперь можно «играться» с разными значениями, просто выбирая их из выпадающего списка.
Афтар молодец! Куплю электронную книгу дабы поддержать энтузиазм
Добрый день, Николай. Функция ПОИСКПОЗ просматривает массив сверху вниз и, соответственно, возвращает первый порядковый номер аргумента:
| Вася | Миша | 2 |
| Миша | ||
| Маша | ||
| Жора | ||
| Миша | ||
| Валя |
Пример (скрин) прилагается. Бьюсь несколько дней, но никак не получается, чтобы найти функцию, указывающую последний порядковый номер соответствующего аргумента в массиве. В нашем примере это «5». Подскажите, пожалуйста, функцию для решения этой задачи. Заранее спасибо!
Как реализовать функции ИНДЕКС и ПОИСКПОЗ в VBA ?
При использовании этой формулы в работе с датами, выдает результат 0.1.1900 ( при пустой исходной ячейки) и #Н/Д(в случаях пустых всех заданных диапазонов)
Какую формулу можно дописать, чтобы при отсутствии исходных данных выдавал пусто, вместо самой первой даты в экселе?
P/S На функцию еслиошибка реагирует только #Н/Д , а пустую ячейку все равно выдает как 0.1.1900
СПАСИБО:<>
Добрый день!
Помогите пожалуйста по стоить формулу.
— есть таблица с данными: список товаров и столбцы магазинов с оборотами по ним
-среднее выводиться с отдельную ячейку С69
задача выводить рядом со средним какой товар = среднему значению и рядом какой магазин
B3:B61= это товары , С69 искомое значение , C3:C61= столбец магазинов где ищет.(НО ИХ 20)
Проблема в том, что выводиться только по одной колонке в формуле, а необходимо искать по всем 20.
Добрый день! Простите, если глупость спрашиваю, но как из столбца цифр выбрать (просуммировать) только те, которые одновременно больше например, 10, но меньше 20.
Т.е. как-то так: СУММЕСЛИ(А2:А30; И(«>=10»; «<20») )
Понимаю, что написанное красным неправильно. подскажите как одновременно учесть оба условия. СУММЕСЛИМН тоже лишь перечисляет критерии, выбирая сначала все числа больше 10, а потом ещё все меньше 20, а нужно только числа от 10 до 20.
Большое спасибо! Очень помогли!
Большое вам человеческое СПАСИБО!
Давно пользуюсь индексом и поиском позиции, но просто скопировав из чужого примера, меняя ссылки на ячейки, т.к. вообще не понимала как эти функции работают, а встроенная справка в Excel не дает понятной информации.
С помощью вашего примера разобралась с этой функцией: это так легко, просто и безумно полезно. 
Доброго времени суток!
А как быть, если нужно найти максимальное значение?
| Дата | А | В | Г |
| 01.02 | 1 | 1 | 1 |
| 02.02 | 3 | 3 | 1 |
| 03.02 | 1 | 1 | 2 |
| 04.02 | 1 | 1 | 1 |
<=ПОИСКПОЗ("1"&"1"&"1";&[А]&[В]&[Г];0)>— ПОИСКПОЗ находит первое значение
Николай доброго времени суток.
Прошу подсказать как найти все уникальные значения и объединить их как текст в одной ячейке по типу изделия.
| уникальный тип изделия | результат |
| С1 | 785; 786; 787; 788; 789; 790 |
| С2 | 791; 792; 793; 794; 795; 796; 797; 798; 799; 800 |
| С3 | 801; 802; 803; 804; 805; 806; 807; 808; 809; 810; 811; 812 |
| С4 | 813; 814; 815; 816; 817; 818; 819; 820; 821; 822; 823; 824; 825; 826 |
| С5 | 827; 828; 829; 830; 831; 832; 833; 834; 835; 836; 837; 838; 839; 840 |
| С6 | 841; 842; 843; 844; 845; 846; 847; 848; 849; 850 |
| С7 | 851; 852; 853; 854; 855; 856; 857; 858; 859; 860; 861; 862 |
| С8 | 863; 864; 865; 866; 867; 868 |
исходная таблица
| 785 | С1 |
| 786 | С1 |
| 787 | С1 |
| 788 | С1 |
| 789 | С1 |
| 790 | С1 |
| 791 | С2 |
| 792 | С2 |
| 793 | С2 |
| 794 | С2 |
| 795 | С2 |
| 796 | С2 |
| 797 | С2 |
| 798 | С2 |
| 799 | С2 |
| 800 | С2 |
| 801 | С3 |
| 802 | С3 |
| 803 | С3 |
| 804 | С3 |
| 805 | С3 |
| 806 | С3 |
| 807 | С3 |
и т.д.
Добрый день. вопрос
не получается найти значение через эти формулы.
может тогда подскажете решение?
Есть строка (не столбец) значений: 2030, 2000, 2050, 2100, 2000.
среднее значение их 2036
мне нужно рядом с ними сделать выборку значения максимально приближенного к среднему значению — и этим значением является 2030
ИНДЕКС+ПОИСКПОЗ — находит почему-то только 2000 (при значении»+1″)
при замене на «-1» — выдает Н/Д
0 — даже не ставлю, т.к. точного значения в строке нет