Excel если строка удовлетворяет условию

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

​Смотрите также​ArkaIIIa​​ находится под ячейкой​​, Добрый день, у​​ работы.​​С уважением,​CTRL+SHIFT+ENTER​ до 5) (см.​И, наконец, т.к. нам​​/​​ уже знакомую нам​MAX​на производительность Excel​A1:C10​ формулы будет ошибочным.​ Мы хотим знать​​(номер_столбца) – это​​Этот учебник рассказывает о​

​, извиняюсь за этот​ с найденным словом​ меня еще маленький​excel_lamer​Александр.​​5. Формула массива =СЧЁТ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2);B2:B13)) представляет​​ файл примера), диапазон​ нужно проверить каждую​ПОИСКПОЗ​ формулу​(МАКС). Формула находит​ особенно заметно, если​​, и требуется извлечь​​Стоп, стоп… почему мы​ значение этого элемента!​ номер столбца в​ главных преимуществах функций​

​ вопрос и панибратство:​ (а так же,​ вопросик остался по​​: Ребят, помогите пожалуйста.​​Смотрите формулу выше​ еще один вариант​A7:B25​ ячейку в массиве,​может искать по​ИНДЕКС​ максимум в столбце​ рабочая книга содержит​ данные из столбца​​ не можем просто​​Позвольте напомнить, что относительное​​ массиве, из которого​​ИНДЕКС​ ты ленивый или​ под ячейкой с​ поводу строки. Смотрите​ Мне необходимо отобрать​​ (пост №2).​​ многокритериального подсчета значений.​

• ​.​ эта формула должна​
• ​ значениям в двух​/​
• ​D​ сотни сложных формул​
• ​B​ использовать функцию​
  • ​ положение искомого значения​ нужно извлечь значение.​
  • ​и​ упёртый?​
  • ​ найденным словом, но​ СТРОКА(А1) возвращает номер​
  • ​ номера предприятий удовлетворяющие​
  • ​Если возникнут вопросы,​6. Формула =БСЧЁТА(A1:B13;A1;D14:E15) требует​

Базовая информация об ИНДЕКС и ПОИСКПОЗ

​Необходимо в отдельном столбце​ быть формулой массива.​ столбцах, без необходимости​​ПОИСКПОЗ​​и возвращает значение​​ массива, таких как​​, то нужно задать​VLOOKUP​ (т.е. номер строки​ Если не указан,​ПОИСКПОЗ​

​ArkaIIIa​ через одну).​ 1, я прочитал​ интервалу. и потом​ создавайте свою тему,​ предварительного создания таблицы​​ вывести фамилии тех​​ Вы можете видеть​​ создания вспомогательного столбца!​​и добавим в​​ из столбца​​ВПР+СУММ​

ИНДЕКС – синтаксис и применение функции

​ значение​​(ВПР)? Есть ли​​ и/или столбца) –​ то обязательно требуется​в Excel, которые​:​ArkaIIIa​ что функция НАИМЕНЬШИЙ(массив,1)​

​ отобрать согласно номеру​
​ эта закрыта.​

​ с условиями. Заголовки​ учеников, кому достался,​

• ​ это по фигурным​​Предположим, у нас есть​ неё ещё одну​C​. Дело в том,​
• ​2​​ смысл тратить время,​ это как раз​ аргумент​ делают их более​Russel​: Через функцию смещения​ вернет наименьшее наименьшее​​ этого предприятия отобрать​​Как использовать функцию​
• ​ этой таблицы должны​​ например, вариант 5.​ скобкам, в которые​ список заказов, и​ функцию​той же строки:​ что проверка каждого​для аргумента​​ пытаясь разобраться в​​ то, что мы​

​row_num​ привлекательными по сравнению​​, просто, мне показалось,​​ можно реализовать, но​ число из массива,​ его характеристики. то​ВПР (VLOOKUP)​

​ в точности совпадать​​Для удобства написания формул​​ она заключена. Поэтому,​

​ мы хотим найти​
​ПОИСКПОЗ​

​=INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))​ значения в массиве​​col_index_num​​ лабиринтах​ должны указать для​​(номер_строки)​​ с​​ что «глубина трагедии»​​ что-то я туплю​ протягиванием получаем что​​ есть у меня​​для поиска и​

​ с заголовками исходной​ создадим именованные диапазоны​ когда закончите вводить​ сумму по двум​, которая будет возвращать​=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МАКС($D$2:I$10);$D$2:D$10;0))​ требует отдельного вызова​​(номер_столбца) функции​​ПОИСКПОЗ​

ПОИСКПОЗ – синтаксис и применение функции

​ аргументов​​Если указаны оба аргумента,​​ВПР​ и «суть проблемы»​ совсем с поиском​ в след. ячейке​ должно быть 4​ выборки нужных значений​

​ таблицы. Размещение условий​​ Вариант (​​ формулу, не забудьте​ критериям –​ номер столбца.​Результат: Beijing​​ функции​​ВПР​и​row_num​

​ то функция​
​. Вы увидите несколько​

​ понятны и не​​ слов в массиве.​​ будет СТРОКА(А2), которая​ номера предприятия, которые​

​ из списка мы​
​ в одной строке​

• ​A7:A25​​ нажать​имя покупателя​=INDEX(Ваша таблица,(MATCH(значение для вертикального​2.​ВПР​, вот так:​ИНДЕКС​
• ​(номер_строки) и/или​​ИНДЕКС​ примеров формул, которые​ нужно захламлять форум​
• ​ArkaIIIa​​ вернет номер строки​ удовлетворяют условию >=109​​ недавно разбирали. Если​​ соответствует Условию И.​) и Ученик (​Ctrl+Shift+Enter​
  • ​(Customer) и​​ поиска,столбец, в котором​​MIN​​. Поэтому, чем больше​=VLOOKUP(«lookup value»,A1:C10,2)​?​column_num​возвращает значение из​ помогут Вам легко​ лишними топами на​
  • ​: Есть функция, которая​​ 2, и уже​ и меньше 155,8.​ вы еще с​​Здесь есть один трюк:​​B7:B25)​​.​​продукт​ искать,0)),(MATCH(значение для горизонтального​(МИН). Формула находит​​ значений содержит массив​​=ВПР(«lookup value»;A1:C10;2)​​=VLOOKUP(«Japan»,$B$2:$D$2,3)​​(номер_столбца) функции​
  • ​ ячейки, находящейся на​​ справиться со многими​ схожие темы.​ ищет указанное значение​ получится НАИМЕНЬШИЙ(массив, 2)​excel_lamer​ ней не знакомы​ в качестве второго​.​

​Если всё сделано верно,​(Product). Дело усложняется​​ поиска,строка в которой​​ минимум в столбце​ и чем больше​Если позднее Вы вставите​=ВПР(«Japan»;$B$2:$D$2;3)​INDEX​

​ пересечении указанных строки​ сложными задачами, перед​Russel​ и возвращает не​ я правильно понимаю,​: Сводная не подойдёт?​ — загляните сюда,​ аргумента функции БСЧЁТА()​​По аналогии с решением​​ Вы получите результат​​ тем, что один​​ искать,0))​​D​​ формул массива содержит​ новый столбец между​​В данном случае –​​(ИНДЕКС). Как Вы​ и столбца.​ которыми функция​:​ номер столбца или​ что следующее наименьшее​AlexM​ не пожалейте пяти​

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

​ (поле) нужно ввести​ из статьи Поиск​ как на рисунке​ покупатель может купить​=ИНДЕКС(Ваша таблица,(MATCH(значение для вертикального​и возвращает значение​​ Ваша таблица, тем​​ столбцами​​ смысла нет! Цель​​ помните, функция​​Вот простейший пример функции​​ВПР​ArkaIIIa​ строки, а название​ число из массива?Правильно​​: а не подскажите​​ минут, чтобы сэкономить​ ссылку на заголовок​ текстовых значений в​ ниже:​

​ сразу несколько разных​ поиска,столбец, в котором​​ из столбца​​ медленнее работает Excel.​​A​​ этого примера –​

​ИНДЕКС​INDEX​бессильна.​
​, Ну и где​ ячейки, в которой​sgkorolew​

​ как вы ее​ себе потом несколько​ столбца с текстовыми​ списках. Часть1. Обычный​Как Вы, вероятно, уже​

​ продуктов, и имена​ искать,0)),(MATCH(значение для горизонтального​C​С другой стороны, формула​

​и​
​ исключительно демонстрационная, чтобы​

​может возвратить значение,​(ИНДЕКС):​В нескольких недавних статьях​

• ​ адекватный пример?​​ это значение содержится?​​: Добрый день!​ создали? просто нам​​ часов.​​ значениями, т.к. БСЧЁТА()​ поиск напишем формулу​​ заметили (и не​​ покупателей в таблице​​ поиска,строка в которой​​той же строки:​ с функциями​B​
• ​ Вы могли понять,​​ находящееся на пересечении​​=INDEX(A1:C10,2,3)​​ мы приложили все​​ArkaIIIa​​Russel​​Подскажите, пожалуйста, как​ сказали с использованием​Если же вы знакомы​ подсчитывает текстовые значения.​ массива для вывода​

  ​ раз), если вводить​
​ на листе​

  ​ искать,0))​=INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))​ПОИСКПОЗ​​, то значение аргумента​​ как функции​​ заданных строки и​​=ИНДЕКС(A1:C10;2;3)​ усилия, чтобы разъяснить​:​: Читайте правила. Новый​​ найти первую ячейку​​ именно функций всяких​ с ВПР, то​ В случае использования БСЧЁТ() нужно​

​ интересующих нас значений:​ некорректное значение, например,​

​Lookup table​Обратите внимание, что для​=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МИН($D$2:I$10);$D$2:D$10;0))​​и​​ придется изменить с​ПОИСКПОЗ​​ столбца, но она​​Формула выполняет поиск в​​ начинающим пользователям основы​​Russel​​ вопрос — новая​​ в столбце, удовлетворяющую​ логические и т.п.​

​ — вдогон -​ записать другую формулу​=ЕСЛИОШИБКА(ИНДЕКС(Ученик;​​ которого нет в​​расположены в произвольном​ двумерного поиска нужно​Результат: Lima​ИНДЕКС​​2​​и​​ не может определить,​​ диапазоне​

​ функции​
​Рус, твой пример​

​ тема + пример​ условию. Условие -​Pelena​ стоит разобраться с​ =БСЧЁТ(A1:B13;B1;D14:E15). Табличка с​НАИМЕНЬШИЙ(ЕСЛИ(D7=Вариант;СТРОКА(Вариант)-СТРОКА($A$6);9999);​​ просматриваемом массиве, формула​​ порядке.​​ указать всю таблицу​​3.​просто совершает поиск​на​ИНДЕКС​​ какие именно строка​​A1:C10​​ВПР​​ абсолютно подходит. Перенести​ (что есть, что​ текст должен начинаться​​, т.е. я допустим​​ похожими функциями:​

Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?

​ критериями не изменится.​СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Вариант)))));»»)​ИНДЕКС​Вот такая формула​​ в аргументе​​AVERAGE​​ и возвращает результат,​​3​​работают в паре.​​ и столбец нас​и возвращает значение​и показать примеры​​ его в эксель​​ надо сделать)​ с определенных символов,​ буду менять числа,​ИНДЕКС (INDEX)​Рассмотрим задачу, когда критерии​С помощью Выпадающего списка​/​​ИНДЕКС​​array​​(СРЗНАЧ). Формула вычисляет​​ выполняя аналогичную работу​​, иначе формула возвратит​​ Последующие примеры покажут​ интересуют.​ ячейки во​ более сложных формул​

​ и приложить к​ArkaIIIa​​ например, «Абвгд». Результатом​​ и у меня​​и​​ применяются к значениям​ в ячейке​ПОИСКПОЗ​​/​​(массив) функции​​ среднее в диапазоне​​ заметно быстрее.​​ результат из только​​ Вам истинную мощь​

4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:

​Теперь, когда Вам известна​​2-й​ для продвинутых пользователей.​​ сообщению?​​: Так. Еще раз.​ должен быть полный​ будут меняться числа​ПОИСКПОЗ (MATCH)​ из одного столбца.​D7​сообщает об ошибке​ПОИСКПОЗ​​INDEX​​D2:D10​​Теперь, когда Вы понимаете​​ что вставленного столбца.​ связки​ базовая информация об​строке и​ Теперь мы попытаемся,​»И совсем другая​ По-русски и с​ текст ячейки, которая​ в сводной таблице?​

​, владение которыми весьма​Найдем число партий товара​​пользователь может выбрать​​#N/A​​решает задачу:​(ИНДЕКС).​, затем находит ближайшее​ причины, из-за которых​Используя​ИНДЕКС​​ этих двух функциях,​​3-м​ если не отговорить​ задача, если» -​ приложением.​​ удовлетворяет условию, например,​​excel_lamer​ облегчит жизнь любому​ с Количеством на складе​ вариант от 1​(#Н/Д) или​

​{=INDEX(‘Lookup table’!$A$2:$C$13,MATCH(1,(A2=’Lookup table’!$A$2:$A$13)*​А теперь давайте испытаем​​ к нему и​​ стоит изучать функции​ПОИСКПОЗ​​и​​ полагаю, что уже​столбце, то есть​​ Вас от использования​​ вот этого не​​Формула должна искать​​ «Абвгдежз».​​: Вариант формулами.​​ опытному пользователю Excel.​

​ не менее минимального и​
​ до 5. Соответствующие​

​#VALUE!​(B2=’Lookup table’!$B$2:$B$13),0),3)}​ этот шаблон на​​ возвращает значение из​​ПОИСКПОЗ​​/​​ПОИСКПОЗ​ становится понятно, как​​ из ячейки​​ВПР​​ будет.​​ текст — например​SLAVICK​А чтобы обновить​

​ Гляньте на следующий​​ не более максимального​​ фамилии учеников будут​​(#ЗНАЧ!). Если Вы​​{=ИНДЕКС(‘Lookup table’!$A$2:$C$13;ПОИСКПОЗ(1;(A2=’Lookup table’!$A$2:$A$13)*​ практике. Ниже Вы​ столбца​и​ИНДЕКС​, которая легко справляется​ функции​C2​, то хотя бы​Russel​ «ТекстДляПоиска» из приложения,​: ВПР с частичным​ сводную, достаточно кликнуть​ пример:​ (Условие И -​ выведены в столбце​ хотите заменить такое​​(B2=’Lookup table’!$B$2:$B$13);0);3)}​​ видите список самых​

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

​ или добавлять столбцы​ ситуациями, когда​​и​​Очень просто, правда? Однако,​ реализации вертикального поиска​​У меня лично​​ «ТекстДляВозврата».​​Полная формула только​​ кнопкой мыши и​ по артикулу товара,​​ критерию, когда ее​​.​

​ более понятное, то​
​ которые мы обсуждали​

​ Предположим, наша задача​=INDEX($C$2:$C$10,MATCH(AVERAGE($D$2:D$10),$D$2:D$10,1))​ самому интересному и​​ к исследуемому диапазону,​​ВПР​ИНДЕКС​ на практике Вы​ в Excel.​​ сходу нет решения,​​ТекстДляПоиска может находиться​​ после файла-примера​​ выбрать Обновить​

​ набранному в ячейку​
​ поле удовлетворяет обоим​

​Произведем подсчет строк, удовлетворяющих​ можете вставить формулу​​ ранее, но вооруженные​ узнать население США​=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(СРЗНАЧ($D$2:D$10);$D$2:D$10;1))​ увидим, как можно​ не искажая результат,​оказывается в тупике.​могут работать вместе.​ далеко не всегда​Зачем нам это? –​ надо думать.​ произвольно в любой​sgkorolew​Czeslav​ C16.​​ критериям одновременно). ​​ сразу двум критериям,​​ с​​ знанием функций​​ в 2015 году.​​Результат: Moscow​ применить теоретические знания​ так как определен​​Решая, какую формулу использовать​​ПОИСКПОЗ​

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

​Задача формулы -​_Boroda_​​excel_lamer​​ двух функций:​​ задачи. Например, с​​ И.​и​и​ Когда мне нужно​

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

​СРЗНАЧ​Любой учебник по​ нужное значение. Действительно,​​ большинство гуру Excel​​ искомого значения в​​ нужны, и поэтому​​ВПР​vanka515​ найти искомый текст​: Так нужно?​:​

Как выполнить поиск с левой стороны, используя ПОИСКПОЗ и ИНДЕКС

​=ИНДЕКС(A1:G13;ПОИСКПОЗ(C16;D1:D13;0);2)​​ использованием функции СЧЁТЕСЛИМН() формула​​В качестве исходной таблицы​ПОИСКПОЗ​ПОИСКПОЗ​ создать сложную формулу​в комбинации с​ВПР​ это большое преимущество,​ считают, что​​ заданном диапазоне ячеек,​​ требуется помощь функции​

​– это не​​: Пример внизу​​ и возвратить ячейку,​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ВПР(«Заявление о назначении выплаты​​Pelena​Функция​ выглядит так (см.​ возьмем таблицу с​в функцию​Вы одолеете ее.​ в Excel с​ИНДЕКС​твердит, что эта​ особенно когда работать​ИНДЕКС​​ а​​ПОИСКПОЗ​​ единственная функция поиска​​Проблема заключается в​ которая находится под​ (ОПС) НЧ*»;A2:A19;1;)​,​

​ПОИСКПОЗ​ лист один столбец​ двумя столбцами: текстовым​ЕСЛИОШИБКА​

​ Самая сложная часть​
​ вложенными функциями, то​

​и​ функция не может​ приходится с большими​/​

• ​ИНДЕКС​​.​​ в Excel, и​ следующем:​ ячейкой с искомым​

  ​sgkorolew​
​AlexM​

• ​ищет в столбце​ в файле примера):​​ «Фрукты» и числовым​​.​ – это функция​ я сначала каждую​ПОИСКПОЗ​​ смотреть влево. Т.е.​​ объёмами данных. Вы​
• ​ПОИСКПОЗ​использует это число​

  ​Функция​
​ её многочисленные ограничения​

​Вводим искомые параметры​​ текстом.​: Boroda, спасибо!​, спасибо большое, буду​​D1:D13​​=СЧЁТЕСЛИМН(B2:B13;»>=»&D2;B2:B13;»​​ «Количество на складе»​​Синтаксис функции​ПОИСКПОЗ​ вложенную записываю отдельно.​, в качестве третьего​

Вычисления при помощи ИНДЕКС и ПОИСКПОЗ в Excel (СРЗНАЧ, МАКС, МИН)

​ если просматриваемый столбец​ можете добавлять и​​намного лучше, чем​​ (или числа) и​​MATCH​​ могут помешать Вам​ (Таблица User)​ArkaIIIa​vikttur​ разбираться в ваших​значение артикула из​Подсчитать количество строк, удовлетворяющим​

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

​(ПОИСКПОЗ) в Excel​
​ получить желаемый результат​

​и сравниваем их​

​:​​: А SLAVICK не​​ формулах. можно если​ ячейки​​ 2-м критериям (Условие​​Рассмотрим задачу, когда критерии​очень прост:​​ объяснить первой.​​ функций​

​ПОИСКПОЗ​
​ левым в диапазоне​

​ беспокоясь о том,​

​. Однако, многие пользователи​​ соответствующей ячейки.​​ ищет указанное значение​ во многих ситуациях.​​ с таблицей на​​Russel​ заслужил?​ что то непонятно​C16​​ И) можно без​​ применяются к значениям​

​IFERROR(value,value_if_error)​
​MATCH(1,(A2='Lookup table'!$A$2:$A$13),0)*(B2='Lookup table'!$B$2:$B$13)​

​ПОИСКПОЗ​

О чём нужно помнить, используя функцию СРЗНАЧ вместе с ИНДЕКС и ПОИСКПОЗ

​чаще всего нужно​​ поиска, то нет​​ что нужно будет​​ Excel по-прежнему прибегают​​Ещё не совсем понятно?​​ в диапазоне ячеек​​ С другой стороны,​ втором листе(BPN2 Mode).​​Это не совсем​​ShAM​ будет задать вам​​. Последний аргумент функции​​ применения формул с​​ из разных столбцов.​​ЕСЛИОШИБКА(значение;значение_если_ошибка)​ПОИСКПОЗ(1;(A2=’Lookup table’!$A$2:$A$13);0)*(B2=’Lookup table’!$B$2:$B$13)​, которые будут возвращать​ будет указывать​ шансов получить от​ исправлять каждую используемую​ к использованию​ Представьте функции​​ и возвращает относительную​​ функции​Если искомые параметры​

• ​ новый вопрос. Это​​: Как вариант:​​ по ним вопрос?​ 0 — означает​ помощью стандартного Автофильтра.​Найдем число партий товара​Где аргумент​В формуле, показанной выше,​
• ​ номера строки и​​1​​ВПР​ функцию​ВПР​ИНДЕКС​ позицию этого значения​ИНДЕКС​

​ меньше, либо равны​ скорее подвопрос, в​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(ЕЧИСЛО(НАЙТИ(«Заявление о назначении выплаты​​excel_lamer​ поиск точного (а​Установите автофильтр к столбцу​​ с определенным Фруктом​​value​​ искомое значение –​​ столбца для функции​​или​​желаемый результат.​​ВПР​, т.к. эта функция​и​ в диапазоне.​и​ необходимо вывести все​

Как при помощи ИНДЕКС и ПОИСКПОЗ выполнять поиск по известным строке и столбцу

​ рамках изначально заданного.​ (ОПС) НЧ»;A1));A1;»»)​​: Ещё один вариант​​ не приблизительного) соответствия.​ Количество ящиков на​И​(значение) – это​

​ это​​ИНДЕКС​​-1​​Функции​​.​ гораздо проще. Так​ПОИСКПОЗ​Например, если в диапазоне​ПОИСКПОЗ​ строки удовлетворяющие условиям.​

​Russel​sgkorolew​​ без формул массива.​​ Функция выдает порядковый​ складе, выделив заголовок​

​с Количеством на​
​ значение, проверяемое на​

​1​:​в случае, если​

​ПОИСКПОЗ​3. Нет ограничения на​ происходит, потому что​в таком виде:​B1:B3​​– более гибкие​​Если нужен макрос,​​:​​: Само собой, и​Pelena​ номер найденного значения​​ столбца и нажав​​ складе не менее​ предмет наличия ошибки​

​, а массив поиска​ПОИСКПОЗ для столбца​ Вы не уверены,​и​ размер искомого значения.​
​ очень немногие люди​=INDEX(столбец из которого извлекаем,(MATCH​содержатся значения New-York,​ и имеют ряд​ то почему тема​

​ArkaIIIa​ SLAVICKу «спасибо»!​, Можно пожалуйста вопрос?​ в диапазоне, т.е.​​CTRL+SHIFT+L. ​​ минимального (Условие И​​ (в нашем случае​​ – это результат​

​– мы ищем​ что просматриваемый диапазон​ИНДЕКС​Используя​ до конца понимают​ (искомое значение,столбец в​ Paris, London, тогда​ особенностей, которые делают​

​ в этом разделе?​, негодный пример. Я​SLAVICK, ShAM, спасибо!​ получается Код =ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$21;НАИМЕНЬШИЙ(ЕСЛИ(($B$2:$B$21>=150)*($B$2:$B$21​ фактически номер строки,​Выберите числовой фильтр Между.​ — условие при​

​ – результат формулы​ умножения. Хорошо, что​​ в столбце​​ содержит значение, равное​в Excel гораздо​ВПР​​ все преимущества перехода​​ котором ищем,0))​

• ​ следующая формула возвратит​​ их более привлекательными,​Перенесла.​​ лично ничего не​​SLAVICK​ если разбить эту​​ где найден требуемыый​​Введите критерии​ котором строка считается​​ИНДЕКС​​ же мы должны​B​

  ​ среднему. Если же​
​ более гибкие, и​

  ​, помните об ограничении​​ с​​=ИНДЕКС(столбец из которого извлекаем;(ПОИСКПОЗ(искомое​ цифру​ по сравнению с​​mathiax90​​ понял из него.​

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

  ​ на длину искомого​
​ВПР​

  ​ значение;столбец в котором​​3​​ВПР​: Так сумму параметров​

​ArkaIIIa​ArkaIIIa​​ выводим Код ЕСЛИ(($B$2:$B$21>=150)*($B$2:$B$21​​Функция​

​ же как в​
​ оба ее поля​

​ПОИСКПОЗ​​ Давайте разберем все​​ диапазоне​ такое значение есть,​ находится столбец со​ значения в 255​

​на связку​
​ ищем;0))​

​, поскольку «London» –​.​​ или значения параметров​​:​​: Добрый день!​​ номер строки того​​ИНДЕКС​​ задаче2 — т.е.​ одновременно соответствуют критериям).​​); а аргумент​​ по порядку:​

Поиск по нескольким критериям с ИНДЕКС и ПОИСКПОЗ

​B2:B11​​ – ставьте​​ значением, которое нужно​ символов, иначе рискуете​​ИНДЕКС​​Думаю, ещё проще будет​ это третий элемент​Базовая информация об ИНДЕКС​ сравнивать?​Russel​Подскажите, пожалуйста, как​​ условия в котором​​выбирает из диапазона​​ будет отобрано 7 строк​​ Например, число партий​value_if_error​Берем первое значение в​, значение, которое указано​

​0​ извлечь. Для примера,​ получить ошибку​и​ понять на примере.​​ в списке.​​ и ПОИСКПОЗ​​А то тут​​Есть текст -​ формульно сделать следующую​ это выполняется,​A1:G13​ (см. строку состояния​ персики (ячейка​(значение_если_ошибка) – это​​ столбце​​ в ячейке​для поиска точного​

​ снова вернёмся к​​#VALUE!​​ПОИСКПОЗ​​ Предположим, у Вас​​=MATCH(«London»,B1:B3,0)​

​Используем функции ИНДЕКС и​
​ пишите одно, а​
​ "ТекстДляПоиска". Он может​
​ весЧь.​

​потом Код НАИМЕНЬШИЙ(ЕСЛИ(($B$2:$B$21>=150)*($B$2:$B$21​значение, находящееся на​ в нижней части​D2​​ значение, которое нужно​​A​​H2​​ совпадения.​ таблице со столицами​(#ЗНАЧ!). Итак, если​​, а тратить время​​ есть вот такой​=ПОИСКПОЗ(«London»;B1:B3;0)​

​ ПОИСКПОЗ в Excel​
​ в экселе другое.​

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

• ​ таблица содержит длинные​ на изучение более​​ список столиц государств:​​Функция​​Преимущества ИНДЕКС и ПОИСКПОЗ​​vanka515​ ячейке B1, так​Есть произвольный набор​ да? суть второго​​ (номер строки с​​Примечание​
• ​ на складе >=5​ выдаст ошибку.​​Main table​​ выглядеть так:​1​​ На этот раз​​ строки, единственное действующее​
• ​ сложной формулы никто​Давайте найдём население одной​MATCH​​ перед ВПР​​: Ой извините) Сравнивать​
• ​ и A124, короче​ текста, разбросанного по​ аргумента СТРОКА (А1)​ артикулом выдает функция​: подсчет значений с​ (ячейка​Например, Вы можете вставить​​и сравниваем его​​=MATCH($H$2,$B$1:$B$11,0)​, значения в столбце​ запишем формулу​ решение – это​ не хочет.​​ из столиц, например,​​(ПОИСКПОЗ) имеет вот​

​ИНДЕКС и ПОИСКПОЗ –​ нужно значения​​ — в любой.​​ разным ячейкам. Нужно​ в том что​​ПОИСКПОЗ​​ множественными критерями также​Е2​ формулу из предыдущего​

​ со всеми именами​​=ПОИСКПОЗ($H$2;$B$1:$B$11;0)​ поиска должны быть​ПОИСКПОЗ​ использовать​​Далее я попробую изложить​​ Японии, используя следующую​ такой синтаксис:​ примеры формул​mathiax90​ Он — уникальный,​ где-нибудь рядышком (не​ бы упорядочивалось с​) и столбца (нам​ рассмотрен в статьях​​). Результат очевиден: 2.​​ примера в функцию​ покупателей в таблице​​Результатом этой формулы будет​​ упорядочены по возрастанию,​

​/​ИНДЕКС​ главные преимущества использования​ формулу:​MATCH(lookup_value,lookup_array,[match_type])​Как находить значения, которые​: Смотрите вложенный файл.​ т.е. такого, что​ важно где, просто​ первого? но у​ нужен регион, т.е.​ Подсчет значений с​​ Для наглядности, строки​​ЕСЛИОШИБКА​

​ на листе​4​ а формула вернёт​ИНДЕКС​

ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА в Excel

​/​ПОИСКПОЗ​=INDEX($D$2:$D$10,MATCH(«Japan»,$B$2:$B$10,0))​ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])​ находятся слева​Или спойлер.​​ в двух ячейках​​ для примера), возвращать​​ меня же в​​ второй столбец).​​ множественными критериями (Часть​​ в таблице, удовлетворяющие​​вот таким образом:​​Lookup table​, поскольку «USA» –​ максимальное значение, меньшее​, которая покажет, какое​ПОИСКПОЗ​и​​=ИНДЕКС($D$2:$D$10;ПОИСКПОЗ(«Japan»;$B$2:$B$10;0))​​lookup_value​​Вычисления при помощи ИНДЕКС​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub макрос9()​​ найдется этот текст​​ значения из ячеек,​

​ A1 не текст,​​Akuznetsov​​ 2. Условие ИЛИ),​

​ критериям, выделяются Условным​
​=IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1:$E$1,0)),​

​(A2:A13).​​ это 4-ый элемент​​ или равное среднему.​ место по населению​.​ИНДЕКС​Теперь давайте разберем, что​​(искомое_значение) – это​​ и ПОИСКПОЗ​​Dim bpnWs As​​ — не будет.​​ находящихся между «Текст1″​​ почему именно А1?​: Здравствуйте!​ Часть3, Часть4.​ форматированием с правилом =И($A2=$D$2;$B2>=$E$2)​

​»Совпадений не найдено.​Если совпадение найдено, уравнение​ списка в столбце​​Если указываете​​ занимает столица России​

​Предположим, Вы используете вот​
​в Excel, а​ делает каждый элемент​ ​ число или текст,​
​Поиск по известным строке​ Worksheet​

​Нужно, чтобы формула​ и «Текст2» (т.е.​и потом уже​Прошу помощи в​

​AlexKoul​Подсчет можно реализовать множеством​ Попробуйте еще раз!»)​ возвращает​B​-1​​ (Москва).​​ такую формулу с​

​ Вы решите –​
​ этой формулы:​

​ который Вы ищите.​ и столбцу​Dim userWs As​ его нашла, и​ то, что выделено​ идет функция индекс,​ решении следующей задачи:​: Уважаемые форумчане!​ формул, приведем несколько:​=ЕСЛИОШИБКА(ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($G$2;$B$1:$B$11;0);ПОИСКПОЗ($G$3;$A$1:$E$1;0));​1​(включая заголовок).​, значения в столбце​Как видно на рисунке​ВПР​

​ остаться с​Функция​
​ Аргумент может быть​
​Поиск по нескольким критериям​

​ Worksheet​

office-guru.ru

Поиск ЧИСЛОвых значений и вывод соответствующих значений в отдельный список в MS EXCEL

​ вернула НЕ номер​ желтым). Однако, в​ эту функцию я​В наличии имеем​Возникла такая задача:​1. =СЧЁТЕСЛИМН(A2:A13;D2;B2:B13;»>=»&E2) Это решение является​»Совпадений не найдено.​(ИСТИНА), а если​ПОИСКПОЗ для строки​ поиска должны быть​ ниже, формула отлично​, которая ищет в​

​ВПР​MATCH​ значением, в том​ИНДЕКС и ПОИСКПОЗ в​Set bpnWs =​ строки/столбца, в котором​ разное время количество​​ вроде понимаю, выводит​​ данные 6 столбцов​

Задача

​ имеется список марок​ самым простым и​ Попробуйте еще раз!»)​ нет –​

Решение

​– мы ищем​ упорядочены по убыванию,​ справляется с этой​​ ячейках от​​или переключиться на​​(ПОИСКПОЗ) ищет значение​​ числе логическим, или​

​ сочетании с ЕСЛИОШИБКА​ Worksheets(«BPN2 Mode»)​ она нашла этот​ строк между «Текст1″​ значение из массива​ 675 строк. Необходимо​ с соответствующими значениями​

​ понятным.​
​И теперь, если кто-нибудь​
​0​

​ значение ячейки​ а возвращено будет​​ задачей:​​B5​ИНДЕКС​ «Japan» в столбце​ ссылкой на ячейку.​Так как задача этого​​Set userWs =​​ текст, НЕ сам​

excel2.ru

Подсчет значений с множественными критериями (Часть 1. Условие И) в MS EXCEL

​ и «Текст2» (текст1​ данных по строке.​ на отдельный лист​ (в примере выделено​

​2. =СУММПРОИЗВ(—(A2:A13=D2);—(B2:B13>=E2)) Это решение сложнее,​ введет ошибочное значение,​(ЛОЖЬ).​H3​ минимальное значение, большее​=INDEX($A$2:$A$10,MATCH(«Russia»,$B$2:$B$10,0))​

Задача1

​до​/​B​

​lookup_array​ учебника – показать​​ Worksheets(«User»)​​ этот текст, а​ и текст2 -​И последний вопрос,​ (или книгу) вывести​ желтым). Нужно в​ но позволяет понять​ формула выдаст вот​Далее, мы делаем то​(2015) в строке​ или равное среднему.​​=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ(«Russia»;$B$2:$B$10;0))​​D10​ПОИСКПОЗ​, а конкретно –​​(просматриваемый_массив) – диапазон​​ возможности функций​Dim trx As​ содержимое ячейки, которая​ это заголовки, они​ для чего функция​

​ значения из столбца​ зеленую зону вывести​

​ работу функции СУММПРОИЗВ(),​ такой результат:​ же самое для​

​1​В нашем примере значения​Теперь у Вас не​значение, указанное в​.​ в ячейках​ ячеек, в котором​

​ИНДЕКС​ Integer​

• ​ находится ПОД ячейкой​ всегда называются одинаково)​ если ошибка, как​ B «Наименование сечения»​ список марок, значение​ которая может быть​​Если Вы предпочитаете в​​ значений столбца​
• ​, то есть в​ в столбце​ должно возникать проблем​
• ​ ячейке​1. Поиск справа налево.​B2:B10​ происходит поиск.​и​Dim ce As​ с найденным текстом.​ может быть разным.​​ я понимаю она​​ только те значения,​ которых больше или​ полезна для подсчета​ случае ошибки оставить​B​
• ​ ячейках​D​ с пониманием, как​A2​

​Как известно любому​, и возвращает число​match_type​ПОИСКПОЗ​ Integer​

​Если она нашла​ Если сейчас нужно​ если будет ошибка​ которые удовлетворяют условию:​ равно значению, вводимому​​ с множественными критериями​​ ячейку пустой, то​​(Product).​

​A1:E1​упорядочены по возрастанию,​ работает эта формула:​

​:​ грамотному пользователю Excel,​3​(тип_сопоставления) – этот​для реализации вертикального​Dim rrc As​ искомый текст в​ возвращать значения из​ какая-то выведет пустое​

​ значение столбца E​ в ячейку С2.​ в других случаях. ​ можете использовать кавычки​Затем перемножаем полученные результаты​:​ поэтому мы используем​Во-первых, задействуем функцию​=VLOOKUP(A2,B5:D10,3,FALSE)​ВПР​, поскольку «Japan» в​ аргумент сообщает функции​

Задача2

​ поиска в Excel,​ Integer​ ячейке B1, то​

​ 5 ячеек, то​ значение, то есть​ равно значению столбца​Прошу помочь с​Разберем подробнее применение функции​ («»), как значение​ (1 и 0).​=MATCH($H$3,$A$1:$E$1,0)​ тип сопоставления​

​MATCH​=ВПР(A2;B5:D10;3;ЛОЖЬ)​не может смотреть​ списке на третьем​ПОИСКПОЗ​ мы не будем​

​Dim trx1 As​

Альтернативное решение

​ вернуть она должна​ в другом случае,​ она здесь не​ F в соответствующих​ решением этой задачи.​

​ СУММПРОИЗВ():​ второго аргумента функции​ Только если совпадения​=ПОИСКПОЗ($H$3;$A$1:$E$1;0)​​1​​(ПОИСКПОЗ), которая находит​

​Формула не будет работать,​

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

​ совсем обязательна?верно​​ строках.​Заранее благодарю всех​Результатом вычисления A2:A13=D2 является​ЕСЛИОШИБКА​ найдены в обоих​Результатом этой формулы будет​. Формула​

excel2.ru

Вывод всех значений удовлетворяющих условию (Формулы/Formulas)

​ положение «Russia» в​​ если значение в​
​ значит, что искомое​Функция​ найти точное или​ синтаксисе и применении.​Dim ce1 As​ в B2 и​ же самое, но​не важно, что​Прилагаю файл, на​ откликнувшихся!​
​ массив {ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ} Значение​. Вот так:​
​ столбцах (т.е. оба​5​

​ИНДЕКС​​ списке:​​ ячейке​​ значение должно обязательно​
​INDEX​ приблизительное совпадение:​

​Приведём здесь необходимый минимум​​ Integer​
​ т.п.​
​ для двух (сверху​

​ в А1, мы​​ листе «решение» -​

​китин​​ ИСТИНА соответствует персики.​IFERROR(INDEX(массив,MATCH(искомое_значение,просматриваемый_массив,0),»»)​
​ критерия истинны), Вы​, поскольку «2015» находится​/​=MATCH(«Russia»,$B$2:$B$10,0))​
​A2​ находиться в крайнем​(ИНДЕКС) использует​
​1​ для понимания сути,​Dim rrc1 As​
​Russel​ ограничим десятью).​ используем эту ссылку​ тот результат, который​: можно так​ Результат можно увидеть,​ЕСЛИОШИБКА(ИНДЕКС(массив;ПОИСКПОЗ(искомое_значение;просматриваемый_массив;0);»»)​ получите​ в 5-ом столбце.​ПОИСКПО​
​=ПОИСКПОЗ(«Russia»;$B$2:$B$10;0))​длиннее 255 символов.​ левом столбце исследуемого​3​
​или​
​ а затем разберём​
​ Integer​:​
​И вторая ситуация​ для получения номера​ хотелось бы получить.​

excelworld.ru

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

​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ИНДЕКС($G$2:$G$7;НАИМЕНЬШИЙ(ЕСЛИ($C$2​​ выделив A2:A13=D2, а​​Надеюсь, что хотя бы​1​Теперь вставляем эти формулы​З​Далее, задаём диапазон для​ Вместо неё Вам​ диапазона. В случае​для аргумента​не указан​ подробно примеры формул,​trx = CInt(userWs.Cells(4,​

​ArkaIIIa​ — то же​ строки СТРОКА(А1) вернёт​Прошу подсказать возможно​формула массива​​ затем нажав клавишу​​ одна формула, описанная​​. Если оба критерия​​ в функцию​возвращает «Moscow», поскольку​ функции​ нужно использовать аналогичную​ с​

​row_num​– находит максимальное​ которые показывают преимущества​ 4))​

​, Должно быть так:​ самое, но со​

​ 1. Вместо А1​

​ ли решение с​​для 2003 подлиньше​​F9​​ в этом учебнике,​​ ложны, или выполняется​ИНДЕКС​​ величина населения города​​INDEX​ формулу​ПОИСКПОЗ​(номер_строки), который указывает​ значение, меньшее или​ использования​ce = CInt(userWs.Cells(4,​ТекстДляПоиска:​ сдвигом вправо на​ можно написать В1​

​ помощью формулы или​​ будет Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(ЕОШИБКА(ИНДЕКС($G$2:$G$7;НАИМЕНЬШИЙ(ЕСЛИ($C$2​​;​​ показалась Вам полезной.​​ только один из​и вуаля:​ Москва – ближайшее​(ИНДЕКС), из которого​​ИНДЕКС​​/​ из какой строки​ равное искомому. Просматриваемый​

planetaexcel.ru

Поиск и вывод значений по условию

​ИНДЕКС​​ 6))​
​помидорка​ 1 ячейку. Т.е.​
​ или С1 или​ только макрос?​_Boroda_​Двойное отрицание (—) преобразует​ Если Вы сталкивались​ них – Вы​=INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0))​ меньшее к среднему​ нужно извлечь значение.​/​ИНДЕКС​ нужно возвратить значение.​ массив должен быть​
​и​rrc = CInt(userWs.Cells(4,​Массив ячеек:​ возвращать значения, которые​
​ 1:1​Спасибо!​: Еще вариант​ вышеуказанный массив в​
​ с другими задачами​

​ получите​​=ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($H$2;$B$1:$B$11;0);ПОИСКПОЗ($H$3;$A$1:$E$1;0))​ значению (12 269​

​ В нашем случае​​ПОИСКПОЗ​, столбец поиска может​
​ Т.е. получается простая​ упорядочен по возрастанию,​ПОИСКПОЗ​ 13))​Огурчик Редиска​ находятся не строго​эта функция позволяет​Vlad999​

​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ПРОСМОТР(;-1/(H$2:H$7>=C$2)/ЕНД(ПОИСКПОЗ(G$2:G$7;C$3:C3;));G$2:G$7);»»)​​ числовой: {0:0:0:0:1:1:1:0:0:0:0:0}​ поиска, для которых​0​Если заменить функции​ 006).​
​ это​:​ быть, как в​ формула:​ то есть от​вместо​

​’Dim rn As​​Баклажан Помидорка​ между «Текст1» и​ скрыть ошибку #ЧИСЛО!,​: Код =ИНДЕКС(’06’!$B$3:$B$500;НАИМЕНЬШИЙ(ЕСЛИ((’06’!$E$3:$E$500=’06’!$F$3:$F$500)*ЕЧИСЛО(’06’!$F$3:$F$500);СТРОКА(’06’!$A$1:$A$498));СТРОКА(E1))) формула​ввод обычный, немассивный​

CyberForum.ru

Найти значения, соответствующие условиям

​Аналогично, второй массив возвращает​​ не смогли найти​.​ПОИСКПОЗ​Эта формула эквивалентна двумерному​A2:A10​=INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))​ левой, так и​=INDEX($D$2:$D$10,3)​ меньшего к большему.​ВПР​ Range​Редиска Огурчик​

​ «Текст2», а между​​ когда найденные значения​

​ массива вводится ctrl+shift+enter​​AlexKoul​ {0:1:1:1:0:1:1:0:0:1:1:1}, где 0​ подходящее решение среди​Теперь понимаете, почему мы​на значения, которые​ поиску​
​.​​=ИНДЕКС(D5:D10;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B5:B10=A2;0);0))​ в правой части​=ИНДЕКС($D$2:$D$10;3)​0​.​

​userWs.Range(userWs.Cells(1, 18), userWs.Cells(1,​​Правильный ответ, который должна​
​ ними, но правее​ закончились. Мы же​Akuznetsov​: Огромное спасибо!​ соответствует значениям B2)​

​ информации в этом​​ задали​

​ они возвращают, формула​​ВПР​​Затем соединяем обе части​​4. Более высокая скорость​​ диапазона поиска. Пример:​​Формула говорит примерно следующее:​– находит первое​Функция​ 24)).EntireColumn.Clear​ получить формула:​ на одну ячейку.​

​ не знаем, сколько​​: Спасибо за решение​AlexBer​

​ =3, которое меньше​​ уроке, смело опишите​1​ станет легкой и​и позволяет найти​ и получаем формулу:​ работы.​ Как находить значения,​ ищи в ячейках​
​ значение, равное искомому.​INDEX​paramSum = trx​Огурчик​Заранее спасибо.​ будет выведено значений​ и оперативность!​: Ребята, благодарю за​ 5 (не удовлетворяет​ свою проблему в​
​, как искомое значение?​ понятной:​ значение на пересечении​=INDEX($A$2:$A$10;MATCH(«Russia»;$B$2:$B$10;0))​Если Вы работаете​ которые находятся слева​
​ от​ Для комбинации​(ИНДЕКС) в Excel​ + ce +​И совсем другая​Russel​ при данном критерии,​Это и так​ Вашу поддержку,​
​ критерию), поэтому первое​ комментариях, и мы​ Правильно, чтобы функция​=INDEX($A$1:$E$11,4,5))​ определённой строки и​=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ(«Russia»;$B$2:$B$10;0))​ с небольшими таблицами,​ покажет эту возможность​D2​
​ИНДЕКС​ возвращает значение из​ rrc​ задача, если в​: Формула для любого​ поэтому формулу копируем​ круто, но вот​По образу и​ значение в массиве  {0:1:1:1:0:1:1:0:0:1:1:1}​ все вместе постараемся​ПОИСКПОЗ​=ИНДЕКС($A$1:$E$11;4;5))​ столбца.​Подсказка:​ то разница в​ в действии.​до​/​
​ массива по заданным​j = 11​ массиве могут быть​
​ столбца, где хотите​ с запасом. Посмотрите​ Ваша формула выводит​ подобию составил свой​ =0. Второе значение (ячейка​
​ решить её.​возвращала позицию только,​Эта формула возвращает значение​В этом примере формула​​Правильным решением будет​​ быстродействии Excel будет,​2. Безопасное добавление или​D10​ПОИСКПОЗ​ номерам строки и​For i =​ «Красная помидорка» или​ получить данные:​ в моём файле​ не все значения​ рабочий файл, очень​B3​Урок подготовлен для Вас​ когда оба критерия​ на пересечении​ИНДЕКС​ всегда использовать абсолютные​ скорее всего, не​

CyberForum.ru

Поиск ячейки, удовлетворяющей условию (Формулы/Formulas)

​ удаление столбцов.​​и извлеки значение​
​всегда нужно точное​ столбца. Функция имеет​ 3 To 563​ «Помидорка маленькая». Как​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(И(СТРОКА()>ПОИСКПОЗ(«Текст1»;B:B;0);СТРОКА()​ диапазон G13:G15. Формула​ по условию. Должно​ доволен результатом.​) =5, которое удовлетворяет​ командой сайта office-guru.ru​ выполняются.​

​4-ой​​/​ ссылки для​
​ заметная, особенно в​Формулы с функцией​

​ из третьей строки,​​ совпадение, поэтому третий​

​ вот такой синтаксис:​​trx1 = CInt(bpnWs.Cells(i,​
​ мы должны понять​В случае, если​

​ там есть, но​​ быть как минимум​

​Есть один нюанс,​​ критерию >=5, поэтому​Источник: https://www.ablebits.com/office-addins-blog/2014/08/13/excel-index-match-function-vlookup/​

​Обратите внимание:​​строки и​
​ПОИСКПОЗ​ИНДЕКС​

​ последних версиях. Если​​ВПР​ то есть из​
​ аргумент функции​

​INDEX(array,row_num,[column_num])​​ 6).Value)​

excelworld.ru

Перенос значений, удовлетворяющих условию «находится между» (Формулы/Formulas)

​ всю глубину масштаб​​ нужно брать правее,​
​ выводит пустую строку,​ ещё одно значение​ который хотелось бы​
​ второе значение в​
​Перевел: Антон Андронов​В этом случае​5-го​будет очень похожа​и​ же Вы работаете​перестают работать или​ ячейки​ПОИСКПОЗ​ИНДЕКС(массив;номер_строки;[номер_столбца])​ce1 = CInt(bpnWs.Cells(i,​ трагедии задачи?​ замените в формуле​ так как предприятий​ «Шепси-Дагомыс» 497 строка.​ доработать.​ массиве =1 и​Автор: Антон Андронов​ необходимо использовать третий​столбца в диапазоне​ на формулы, которые​ПОИСКПОЗ​ с большими таблицами,​ возвращают ошибочные значения,​D4​должен быть равен​
​Каждый аргумент имеет очень​ 7).Value)​китин​ B1 на C1​ больше нет. Без​Vlad999​В приложенном файле​ т.д.​Найдем числовые значения, равные​ не обязательный аргумент​A1:E11​
​ мы уже обсуждали​

​, чтобы диапазоны поиска​​ которые содержат тысячи​ если удалить или​, так как счёт​
​0​
​ простое объяснение:​rrc1 = CInt(bpnWs.Cells(i,​:​ArkaIIIa​

​ ЕСЛИОШИБКА в этих​​: вы что то​​ пример, с предложенной​
​Далее, функция попарно перемножает​

​ заданному пользователем критерию.​​ функции​​, то есть значение​
​ в этом уроке,​ не сбились при​ строк и сотни​ добавить столбец в​ начинается со второй​.​array​ 8).Value)​Russel​:​ ячейках было бы​ не так делаете.​ формулой​ элементы массивов и​ Поиск будем осуществлять​

​ИНДЕКС​​ ячейки​ с одним лишь​ копировании формулы в​ формул поиска, Excel​ таблицу поиска. Для​

​ строки.​​-1​(массив) – это​paramSum1 = trx1​, тогда уж баклажан,не?не!точно​Russel​ #ЧИСЛО!.​ Вводим формулу в​

​Вывод значений происходит​​ суммирует их. Получаем​ в диапазоне с​. Он необходим, т.к.​E4​ отличием. Угадайте каким?​

​ другие ячейки.​​ будет работать значительно​ функции​Вот такой результат получится​
​– находит наименьшее​ диапазон ячеек, из​ + ce1 +​ огурчик!​Спасибо большое, работает​
​Ошибки можно скрыть​ одну ячейку и​ снизу вверх ,​
​ – 2.​ повторяющимися значениями. При​ в первом аргументе​. Просто? Да!​Как Вы помните, синтаксис​Вы можете вкладывать другие​

​ быстрее, при использовании​​ВПР​​ в Excel:​
​ значение, большее или​ которого необходимо извлечь​ rrc1​ArkaIIIa​

​ArkaIIIa​​ и другими способами,​​ потом растягиваем.​​ а мне для​3. Другим вариантом использования​ наличии повторов, можно​

​ мы задаем всю​​В учебнике по​​ функции​
​ функции Excel в​ПОИСКПОЗ​любой вставленный или​Важно! Количество строк и​ равное искомому значению.​ значение.​If trx1 'If​:​:​ например, Условным форматированием.​Выдает два значения​
​ удобства, да и​ функции СУММПРОИЗВ() является​ ожидать, что критерию​ таблицу и должны​ВПР​INDEX​ИНДЕКС​и​ удалённый столбец изменит​ столбцов в массиве,​
​ Просматриваемый массив должен​row_num​ paramSum1 bpnWs.Range(bpnWs.Cells(i, 2),​Russel​Russel​Разобраться в работе​ и ошибки. От​

​ из эстетических соображений​​ формула =СУММПРОИЗВ((A2:A13=D2)*(B2:B13>=E2)). Здесь,​​ будет соответствовать несколько​​ указать функции, из​
​мы показывали пример​​(ИНДЕКС) позволяет использовать​
​и​
​ИНДЕКС​
​ результат формулы, поскольку​
​ который использует функция​
​ быть упорядочен по​(номер_строки) – это​​ bpnWs.Cells(i, 8)).copy​
​Красных помидорок в​А может быть​ сложной формулы помогает​ ошибок избавляемся ф-цией​ хотелось бы видеть​ знак Умножения (*)​ значений. Для их​ какого столбца нужно​

​ формулы с функцией​​ три аргумента:​​ПОИСКПОЗ​​вместо​ синтаксис​


​INDEX​​ убыванию, то есть​​ номер строки в​
​userWs.Cells(j, 18).PasteSpecial xlPasteValues​ массиве не будет,​ сможете подсказать, как​ клавиша F9. Выделите​ =ЕСЛИОШИБКА(формула;"") или с​ список в том​ эквивалентен Условию И.​

​ вывода в отдельный​​ извлечь значение. В​​ВПР​​INDEX(array,row_num,[column_num])​, например, чтобы найти​ВПР​ВПР​

​(ИНДЕКС), должно соответствовать​​ от большего к​​ массиве, из которой​​j = j + 1​ а если и​ сделать примерно то​ в строке формул​ помощью УФ по​ же порядке, в​4. Формула массива =СУММ((A2:A13=D2)*(B2:B13>=E2))​

​ диапазон удобно использовать​​ нашем случае это​​для поиска по​​ИНДЕКС(массив;номер_строки;[номер_столбца])​ минимальное, максимальное или​

​. В целом, такая​​требует указывать весь​​ значениям аргументов​
​ меньшему.​ нужно извлечь значение.​End If​ будут - то​ же самое, только...​
​ фрагмент​ вашему желанию.​ каком и в​ эквивалентна вышеупомянутой формуле​

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

​ замена увеличивает скорость​​ диапазон и конкретный​

Показать значения удовлетворяющие условию. Макрос (Макросы/Sub)

​row_num​​На первый взгляд, польза​
​ Если не указан,​Next i​
​ нужно будет искать​ формула должна искать​
​Код ЕСЛИ(($B$2:$B$21>=150)*($B$2:$B$21 нажмите​Казанский​ исходной таблице.​
​ =СУММПРОИЗВ((A2:A13=D2)*(B2:B13>=E2))  Единственное, после​Пусть имеется перечень учеников​C​ существенным ограничением такого​
​ из Вас, кто​ значение. Вот несколько​ работы Excel на​
​ номер столбца, из​

​(номер_строки) и​​ от функции​ то обязательно требуется​Set bpnWs =​
​ их, а не​ слово в массиве,​ F9 и наглядно​

​: Расширенным фильтром легко​​Подскажите пожалуйста если​ ее ввода нужно​

excelworld.ru

​End Sub​

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

Логические функции – это очень простой и эффективный инструмент, который часто применяется в практике. Рассмотрим подробно на примерах.

Синтаксис функции ЕСЛИ с одним условием

Синтаксис оператора в Excel – строение функции, необходимые для ее работы данные.

=ЕСЛИ (логическое_выражение;значение_если_истина;значение_если_ложь)

Разберем синтаксис функции:

Логическое_выражение – ЧТО оператор проверяет (текстовые либо числовые данные ячейки).

Значение_если_истина – ЧТО появится в ячейке, когда текст или число отвечают заданному условию (правдивы).

Значение,если_ложь – ЧТО появится в графе, когда текст или число НЕ отвечают заданному условию (лживы).

Пример:

Оператор проверяет ячейку А1 и сравнивает ее с 20. Это «логическое_выражение». Когда содержимое графы больше 20, появляется истинная надпись «больше 20». Нет – «меньше или равно 20».

Внимание! Слова в формуле необходимо брать в кавычки. Чтобы Excel понял, что нужно выводить текстовые значения.

Еще один пример. Чтобы получить допуск к экзамену, студенты группы должны успешно сдать зачет. Результаты занесем в таблицу с графами: список студентов, зачет, экзамен.

Обратите внимание: оператор ЕСЛИ должен проверить не цифровой тип данных, а текстовый. Поэтому мы прописали в формуле В2= «зач.». В кавычки берем, чтобы программа правильно распознала текст.



Функция ЕСЛИ в Excel с несколькими условиями

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

Синтаксис будет выглядеть следующим образом:

=ЕСЛИ(логическое_выражение;значение_если_истина;ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь))

Здесь оператор проверяет два параметра. Если первое условие истинно, то формула возвращает первый аргумент – истину. Ложно – оператор проверяет второе условие.

Примеры несколько условий функции ЕСЛИ в Excel:

Таблица для анализа успеваемости. Ученик получил 5 баллов – «отлично». 4 – «хорошо». 3 – «удовлетворительно». Оператор ЕСЛИ проверяет 2 условия: равенство значения в ячейке 5 и 4.

В этом примере мы добавили третье условие, подразумевающее наличие в табеле успеваемости еще и «двоек». Принцип «срабатывания» оператора ЕСЛИ тот же.

Расширение функционала с помощью операторов «И» и «ИЛИ»

Когда нужно проверить несколько истинных условий, используется функция И. Суть такова: ЕСЛИ а = 1 И а = 2 ТОГДА значение в ИНАЧЕ значение с.

Функция ИЛИ проверяет условие 1 или условие 2. Как только хотя бы одно условие истинно, то результат будет истинным. Суть такова: ЕСЛИ а = 1 ИЛИ а = 2 ТОГДА значение в ИНАЧЕ значение с.

Функции И и ИЛИ могут проверить до 30 условий.

Пример использования оператора И:

Пример использования функции ИЛИ:

Как сравнить данные в двух таблицах

Пользователям часто приходится сравнить две таблицы в Excel на совпадения. Примеры из «жизни»: сопоставить цены на товар в разные привозы, сравнить балансы (бухгалтерские отчеты) за несколько месяцев, успеваемость учеников (студентов) разных классов, в разные четверти и т.д.

Чтобы сравнить 2 таблицы в Excel, можно воспользоваться оператором СЧЕТЕСЛИ. Рассмотрим порядок применения функции.

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

Исходные данные (таблицы, с которыми будем работать):

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

В строку формул записываем: =СЧЕТЕСЛИ (сравниваемый диапазон; первая ячейка первой таблицы)=0. Сравниваемый диапазон – это вторая таблица.

Чтобы вбить в формулу диапазон, просто выделяем его первую ячейку и последнюю. «= 0» означает команду поиска точных (а не приблизительных) значений.

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

Выделяем вторую таблицу. Условное форматирование – создать правило – использовать формулу. Применяем тот же оператор (СЧЕТЕСЛИ).

Скачать все примеры функции ЕСЛИ в Excel

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

Поиск значений в списке данных

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

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

В этой статье

Поиск значений в списке по вертикали по точному совпадению

Для выполнения этой задачи можно использовать функцию ВПР или сочетание функций индекс и ПОИСКПОЗ.

Примеры использования функции ВПР

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

Примеры ИНДЕКСов и СОВПАДЕНИй

=ИНДЕКС(нужно вернуть значение из C2:C10, которое будет соответствовать ПОИСКПОЗ(первое значение «Капуста» в массиве B2:B10))

Формула ищет первое значение в ячейке C2: C10, соответствующее капусты (в B7), и возвращает значение в C7 (100) — первое значение, соответствующее капусты.

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

Поиск значений в списке по вертикали по приблизительному совпадению

Для этого используйте функцию ВПР.

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

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

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

Поиск значений по вертикали в списке неизвестного размера с точным соответствием

Для выполнения этой задачи используйте функции СМЕЩ и ПОИСКПОЗ.

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

C1 — это верхняя левая ячейка диапазона (также называемая начальной ячейкой).

Match («апельсины»; C2: C7; 0) ищет оранжевый цвет в диапазоне C2: C7. Не следует включать начальную ячейку в диапазон.

1 — количество столбцов справа от начальной ячейки, для которых должно быть возвращено возвращаемое значение. В нашем примере возвращаемое значение находится в столбце D, Sales.

Поиск значений в списке по горизонтали по точному совпадению

Для выполнения этой задачи используется функция ГПР. Ниже приведен пример.

Функция ГПР выполняет поиск по столбцу Sales и возвращает значение из строки 5 в указанном диапазоне.

Дополнительные сведения можно найти в разделе функции ГПР.

Поиск значений в списке по горизонтали с использованием приблизительного совпадения

Для выполнения этой задачи используется функция ГПР.

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

В приведенном выше примере функция ГПР ищет значение 11000 в строке 3 в указанном диапазоне. Он не находит 11000 и, следовательно, ищет следующее наибольшее значение, которое меньше 1100 и возвращает число 10543.

Дополнительные сведения можно найти в разделе функции ГПР.

Создание формулы подстановки с помощью мастера подстановок (толькоExcel 2007 )

Примечание: Надстройка «Мастер подстановок» прекращена в Excel 2010. Эти функциональные возможности заменены мастером функций и доступными функциями поиска и работы со ссылками (ссылками).

В Excel 2007 мастер подстановок создает формулу подстановки на основе данных листа, имеющих заголовки строк и столбцов. Мастер подстановок помогает находить другие значения в строке, когда вы знаете значение в одном столбце, и наоборот. Мастер подстановок использует индекс и СОВПАДЕНИе в создаваемых формулах.

Щелкните ячейку в диапазоне.

На вкладке формулы в группе решения нажмите кнопку Подстановка .

Если команда подстановка недоступна, необходимо загрузить мастер подстановок надстройка программу.

Загрузка программы-надстройки «Мастер подстановок»

Нажмите кнопку Microsoft Office , щелкните Параметры Excelи выберите категорию надстройки.

В поле Управление выберите элемент Надстройки Excel и нажмите кнопку Перейти.

В диалоговом окне надстройки установите флажок Мастер подстановоки нажмите кнопку ОК.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Пять последних значений в MS EXCEL

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

Удаляем в MS EXCEL пропуски в списке (формулы)

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

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

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

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

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

Вывод итогов столбцов по строкам в MS EXCEL

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

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

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

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

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

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

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

Динамическое разнесение данных по столбцам

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

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

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

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

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

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

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

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

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

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

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

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

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

Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть3. Поиск с учетом РЕГИСТРА

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

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

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

Поиск ЧИСЛОвых значений и вывод соответствующих значений в отдельный список в MS EXCEL

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

Поиск нескольких значений с максимальной длиной строки в MS EXCEL

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

Отбор повторяющихся значений (дубликатов) со значениями из соседнего столбца в MS EXCEL

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

ВПР с возвратом всех значений

Все, кто сталкивался с функцией ВПР знают, что она умеет возвращать исключительно одно значений — первое найденное. Но что делать, когда надо видеть все значения? К примеру имеется такая таблица:

И из этой таблицы необходимо получить фамилии(поле ФИО) всех сотрудников из отдела Сбыта:

Тут два варианта:

Вариант 1: все значения необходимо получить в одной ячейке через разделитель(скажем запятую). Такое возможно только через использование VBA(на момент написания статьи). Такую функцию я написал давно: Как сцепить несколько значений в одну ячейку по критерию? СцепитьЕсли

. В результате получим что-то вроде: Александров Иван,Александров Петр,Андреев Алексей .

Здесь надо сразу оговориться — при помощи непосредственно ВПР (VLookup) это сделать ну никак не получится. Но это можно сделать при помощи её родственников — связки ИНДЕКС(ПОИСКПОЗ) (INDEX(MATCH)) . Плюс еще пара функций. В сборе функция выглядит так:
=ИНДЕКС( $A$12:$G$111 ;НАИМЕНЬШИЙ(ЕСЛИ( $I$12 = $C$12:$C$111 ;СТРОКА( $C$12:$C$111 )-11);СТРОКА( A1 ));1)
=INDEX($A$12:$G$111;SMALL(IF($I$12=$C$12:$C$111;ROW($C$12:$C$111)-11);ROW(A1));1)
где:

• $A$12:$G$111 — таблица с ФИО и всеми данными
• $I$12 — ячейка с названием отдела
• $C$12:$C$111 — столбец с названиями отделов в таблице $A$12:$G$111

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

Tips_All_VlookupAllVals.xls (84,5 KiB, 8 651 скачиваний)

• Первое и самое главное — формула вводится в ячейку как формула массива. Это значит, что для приложенного к статье примера необходимо будет выделить ячейку J12 , записать в неё формулу и завершить ввод нажатием сразу трех клавиш: Ctrl+Shift+Enter. После этого скопировать ячейку, выделить диапазон J13:J39 и вставить туда скопированную ячейку.
• За отбор ФИО указанного отдела отвечает этот кусок: ЕСЛИ( $I$12 = $C$12:$C$111 ;СТРОКА( $C$12:$C$111 )-11)
  IF($I$12=$C$12:$C$111;ROW($C$12:$C$111)-11)
  Как видно здесь идет сравнение отдела, записанного в $I$12 со всеми отделами в таблице: $C$12:$C$111 . Если отдел совпадает, то функция возвращает номер строки СТРОКА( $C$12:$C$111 ) . Но нам нужен не номер строки на листе, а номер строки внутри таблицы(потому что в формулу ИНДЕКС (INDEX) у нас передан диапазон $A$12:$G$111 и если передать в его второй аргумент( номер_строки ) 14, то получим не Александров Иван , а Андреев Олег ). Поэтому отнимаем от номера строки 11, чтобы было возвращено число 3(а не 14). Т.к. формула записана формулой массива — ЕСЛИ (IF) возвращает массив из номеров строк и логического ЛОЖЬ (FALSE) (если отдел не совпадает):
  шаг 1: ЕСЛИ(<ЛОЖЬ:ЛОЖЬ: ИСТИНА :ЛОЖЬ:ЛОЖЬ: ИСТИНА :ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>;<12:13: 14 :15:16: 17 :18:19:20>-11)
  шаг 2: ЕСЛИ(<ЛОЖЬ:ЛОЖЬ: ИСТИНА :ЛОЖЬ:ЛОЖЬ: ИСТИНА :ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>;<1:2: 3 :4:5: 6 :7:8:9>)
  шаг 3:
• Т.к. ЕСЛИ (IF) в данном случае возвращает массив значений, включая ненужные нам ЛОЖЬ (FALSE) — применяем НАИМЕНЬШИЙ (SMALL) , которая будет брать только числа (сначала 3, затем 6 и т.д.), а логические значения ЛОЖЬ (FALSE) будет игнорировать:
  НАИМЕНЬШИЙ(<ЛОЖЬ:ЛОЖЬ: 3 :ЛОЖЬ:ЛОЖЬ: 6 :ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>;СТРОКА(A1))
  SMALL(<ЛОЖЬ:ЛОЖЬ: 3 :ЛОЖЬ:ЛОЖЬ: 6 :ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>;ROW(A1))
  Функция НАИМЕНЬШИЙ (SMALL) отбирает из указанного массива чисел наименьшее. При этом можно отобрать по рангу — первое наименьшее, второе наименьшее, третье наименьшее и т.д.

1. с первым аргументом разобрались — это уже отобранные записи, где число — строка в таблице с нужной ФИО и ненужные нам логические ЛОЖЬ (FALSE) .
2. второй аргумент СТРОКА(A1) для каждой следующей строки будет меняться. В приложенном примере первая ячейка с формулой — J12 . В ней СТРОКА(A1) равна 1, т.е. НАИМЕНЬШИЙ (SMALL) вернет первое наименьшее из массива чисел — 3 . В ячейке J13 это уже будет СТРОКА(A2) и она равна 2, а значит НАИМЕНЬШИЙ (SMALL) вернет второе наименьшее из массива чисел — 6 . И т.д. Когда все числа закончатся — НАИМЕНЬШИЙ (SMALL) вернет значение ошибки #ЧИСЛО! (#NUM!)
   ячейка J12
   шаг 1: НАИМЕНЬШИЙ(<ЛОЖЬ:ЛОЖЬ: 3 :ЛОЖЬ:ЛОЖЬ: 6 :ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>;СТРОКА(A1))
   шаг 2: НАИМЕНЬШИЙ(<ЛОЖЬ:ЛОЖЬ: 3 :ЛОЖЬ:ЛОЖЬ: 6 :ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>;1)
   шаг 3: НАИМЕНЬШИЙ( 3 )
   ячейка J13
   шаг 1: НАИМЕНЬШИЙ(<ЛОЖЬ:ЛОЖЬ: 3 :ЛОЖЬ:ЛОЖЬ: 6 :ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>;СТРОКА(A2))
   шаг 2: НАИМЕНЬШИЙ(<ЛОЖЬ:ЛОЖЬ: 3 :ЛОЖЬ:ЛОЖЬ: 6 :ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>;2)
   шаг 3: НАИМЕНЬШИЙ( 6 )

И в довершение неплохо бы убрать ошибку в случае, если ни одно значение не соответствует критерию или значений больше, чем ячеек с формулами. Более подробно подобное решение я описывал в этой статье: Как в ячейке с формулой вместо ошибки показать 0
Для всех версий Excel:
=ЕСЛИ(ЕОШ(ИНДЕКС( $A$12:$G$111 ;НАИМЕНЬШИЙ(ЕСЛИ( $I$12 = $C$12:$C$111 ;СТРОКА( $C$12:$C$111 )-11);СТРОКА( A1 ));1));»»;ИНДЕКС( $A$12:$G$111 ;НАИМЕНЬШИЙ(ЕСЛИ( $I$12 = $C$12:$C$111 ;СТРОКА( $C$12:$C$111 )-11);СТРОКА( A1 ));1))
=IF(ISERR(INDEX($A$12:$G$111;SMALL(IF($I$12=$C$12:$C$111;ROW($C$12:$C$111)-11);ROW(A1));1));»»;INDEX($A$12:$G$111;SMALL(IF($I$12=$C$12:$C$111;ROW($C$12:$C$111)-11);ROW(A1));1))
Для Excel 2007 и выше:
=ЕСЛИОШИБКА(ИНДЕКС( $A$12:$G$111 ;НАИМЕНЬШИЙ(ЕСЛИ( $I$12 = $C$12:$C$111 ;СТРОКА( $C$12:$C$111 )-11);СТРОКА( A1 ));1);»»)
=IFERROR(INDEX($A$12:$G$111;SMALL(IF($I$12=$C$12:$C$111;ROW($C$12:$C$111)-11);ROW(A1));1);»»)
Плюс к этому в приложенном к статье файле я привел формулу, которая возвращает список всех сотрудников выбранного отдела без повторений и формулу с возможностью просто поменять заголовок в результирующем столбце(в файле это столбец K, ячейка K11 ) на любой из исходной таблицы и данные будут отображены из этого столбца.
Например, если вместо столбца ФИО записать в K11 Адрес — будут отображены все адреса сотрудников выбранного отдела. Если записать Телефон — все телефоны сотрудников выбранного отдела.

Tips_All_VlookupAllVals.xls (84,5 KiB, 8 651 скачиваний)

Статья помогла? Поделись ссылкой с друзьями!

Поиск и подстановка по нескольким условиям

Постановка задачи

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

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

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

Способ 1. Дополнительный столбец с ключом поиска

Это самый очевидный и простой (хотя и не самый удобный) способ. Поскольку штатная функция ВПР (VLOOKUP) умеет искать только по одному столбцу, а не по нескольким, то нам нужно из нескольких сделать один!

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

Теперь можно использовать знакомую функцию ВПР (VLOOKUP) для поиска склеенной пары НектаринЯнварь из ячеек H3 и J3 в созданном ключевом столбце:

Плюсы : Простой способ, знакомая функция, работает с любыми данными.

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

Способ 2. Функция СУММЕСЛИМН

Если нужно найти именно число (в нашем случае цена как раз число), то вместо ВПР можно использовать функцию СУММЕСЛИМН (SUMIFS) , появившуюся начиная с Excel 2007. По идее, эта функция выбирает и суммирует числовые значения по нескольким (до 127!) условиям. Но если в нашем списке нет повторяющихся товаров внутри одного месяца, то она просто выведет значение цены для заданного товара и месяца:

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

Минусы : Работает только с числовыми данными на выходе, не применима для поиска текста, не работает в старых версиях Excel (2003 и ранее).

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

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

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

Как это на самом деле работает:

Функция ИНДЕКС выдает из диапазона цен C2:C161 содержимое N-ой ячейки по порядку. При этом порядковый номер нужной ячейки нам находит функция ПОИСКПОЗ. Она ищет связку названия товара и месяца (НектаринЯнварь) по очереди во всех ячейках склеенного из двух столбцов диапазона A2:A161&B2:B161 и выдает порядковый номер ячейки, где нашла точное совпадение. По сути, это первый способ, но ключевой столбец создается виртуально прямо внутри формулы, а не в ячейках листа.

Плюсы : Не нужен отдельный столбец, работает и с числами и с текстом.

Минусы : Ощутимо тормозит на больших таблицах (как и все формулы массива, впрочем), особенно если указывать диапазоны «с запасом» или сразу целые столбцы (т.е. вместо A2:A161 вводить A:A и т.д.) Многим непривычны формулы массива в принципе (тогда вам сюда).

Научитесь: Excel + Google Таблицы с нуля до PRO
Узнать больше

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

(

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. Актуальная цена.

Поиск и подстановка по нескольким условиям

Постановка задачи

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

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

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

Способ 1. Дополнительный столбец с ключом поиска

Это самый очевидный и простой (хотя и не самый удобный) способ. Поскольку штатная функция ВПР (VLOOKUP) умеет искать только по одному столбцу, а не по нескольким, то нам нужно из нескольких сделать один!

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

Теперь можно использовать знакомую функцию ВПР (VLOOKUP) для поиска склеенной пары НектаринЯнварь из ячеек H3 и J3 в созданном ключевом столбце:

Плюсы: Простой способ, знакомая функция, работает с любыми данными.

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

Способ 2. Функция СУММЕСЛИМН

Если нужно найти именно число (в нашем случае цена как раз число), то вместо ВПР можно использовать функцию СУММЕСЛИМН (SUMIFS), появившуюся начиная с Excel 2007. По идее, эта функция выбирает и суммирует числовые значения по нескольким (до 127!) условиям. Но если в нашем списке нет повторяющихся товаров внутри одного месяца, то она просто выведет значение цены для заданного товара и месяца:

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

Минусы: Работает только с числовыми данными на выходе, не применима для поиска текста, не работает в старых версиях Excel (2003 и ранее).

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

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

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

   

3. Нажмите в конце не Enter, а сочетание Ctrl+Shift+Enter, чтобы ввести формулу не как обычную, а как формулу массива.

Как это на самом деле работает:

Функция ИНДЕКС выдает из диапазона цен C2:C161 содержимое N-ой ячейки по порядку. При этом порядковый номер нужной ячейки нам находит функция ПОИСКПОЗ. Она ищет связку названия товара и месяца (НектаринЯнварь) по очереди во всех ячейках склеенного из двух столбцов диапазона A2:A161&B2:B161 и выдает порядковый номер ячейки, где нашла точное совпадение. По сути, это первый способ, но ключевой столбец создается виртуально прямо внутри формулы, а не в ячейках листа.

Плюсы: Не нужен отдельный столбец, работает и с числами и с текстом.

Минусы: Ощутимо тормозит на больших таблицах (как и все формулы массива, впрочем), особенно если указывать диапазоны «с запасом» или сразу целые столбцы (т.е. вместо A2:A161 вводить A:A и т.д.) Многим непривычны формулы массива в принципе (тогда вам сюда).

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

• Как искать и подставлять данные с помощью функции ВПР (VLOOKUP)
• Что такое формулы массива и как их использовать
• Как использовать связку функций ИНДЕКС и ПОИСКПОЗ вместо ВПР
• Как извлечь сразу все значения, а не только первое с помощью ВПР

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

Принцип работы очень простой: если выражение истинное, то сделать это, иначе – сделать что-то другое.

То есть, в результате проверки условия может произойти два события:

• первое – при истине;
• второе – если возвращается ложь.

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

Справка по функции «ЕСЛИ» выглядит следующим образом.

[kod]ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)[/kod]

Как вставить функцию «ЕСЛИ»

Для этого нужно выполнить следующие действия:

1. Кликните на иконку вставки формул (Fx).
2. В появившемся меню выберите пункт «ЕСЛИ».

1. В результате этого вы увидите окно, в котором нужно указать аргументы функции:
   • логическое выражение;
   • значение, если истина;
   • значение, если ложь.
2. После ввода значений в эти поля, правее их будет отображаться предварительный результат.
3. Для вставки нажмите на кнопку «OK».

Примеры

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

1. Сделайте активной ячейку «А1».
2. Повторите описанные выше действия по вызову окна вставки «Аргументов».
3. Введите в поле условие следующий код.

[kod]C1=””[/kod]

Смысл этого выражения в следующем: если ячейка C1 содержит пустоту. То есть в ней ничего не находится.

1. В поле для истины вводим следующее.

[kod]”Ячейка C1 пустая”[/kod]

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

1. В поле «если ложь» вводим следующее.

[kod]”Ячейка C1 не пустая”[/kod]

Данный текст мы увидим в случае ложного выполнения условия.

1. Для вставки нашей формулы нажимаем на кнопку «OK».

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

1. Введите любой текст в ячейку C.

1. После нажатия на кнопку [knopka]Enter[/knopka] вы увидите следующий результат.

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

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

Ниже вы видите список операторов сравнения:

Арифметические операторы

А это таблица арифметических операторов:

Более подробно об арифметических операторах и их приоритете в Экселе можно почитать в онлайн справке сайта Microsoft.

Несколько условий

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

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

Для этого необходимо выполнить следующие действия.

1. Перейдите на вкладку «Формулы». Кликните на кнопку «Вставить функцию».

1. В результате этого всё содержимое ячейки автоматически подставится в «конструктор».

1. Удалите всё, что находится в поле «Значение_если_ложь».

1. Теперь в этой строке будет написано новое логическое выражение. То есть у нас будет новое вложенное условие. Для начала введите условие «Если значение ячейки С1 больше 1000». Для этого вводим следующий код. Обязательно в конце поставьте символ точки с запятой.

[kod]ЕСЛИ(C1>1000;[/kod]

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

1. Теперь добавим сообщение, которое будет выводиться, если число в ячейке C1 больше 1000.

[kod]”Число больше 1000”;[/kod]

Все текстовые значения обязательно вводим в кавычках.

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

[kod]”Число меньше 1000”)[/kod]

1. В конце нажмите на кнопку «OK».

1. В результате этого, мы видим сообщение о том, что введенное число больше 1000.

1. Удалите содержимое ячейки C. Результатом станет следующее.

Теперь вбейте какое-нибудь число меньше 1000. Например, 500. Результат будет очевидным.

Мы проверили все три возможных результата. Всё прекрасно работает.

Копирование функции в таблицах

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

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

1. Создайте соответствующую таблицу.

1. Кликните на пустую ячейку в первой строке и выберите «Fx» в поле ввода.

1. В появившемся окне выберите функцию «ЕСЛИ» и нажмите на кнопку «OK».

1. В первом поле введите следующее условие.

[kod]C6=”М”[/kod]

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

1. В случае истины вводим какое-нибудь число. Например, 3000. В случае ложного выполнения условия вводим 0. Это значит, что девушкам премию давать не нужно. Для вставки нажмите на кнопку «OK».

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

1. Результатом будет следующее.

1. Теперь полностью скопируйте всю таблицу (при помощи сочетания клавиш [knopka]Ctrl[/knopka]+[knopka]C[/knopka] и [knopka]Ctrl[/knopka]+[knopka]V[/knopka]). Измените заголовок на 8 марта.

1. Перейдите на первую ячейку. Кликните на поле ввода формулы.

1. Измените букву «М» на «Ж».

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

1. Результатом будет следующее.

Здесь мы видим, что получилась полная противоположность. Это означает, что всё работает правильно.

Использование дополнительных операторов

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

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

Оператор И

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

1. Добавьте в условие скобку и рядом букву «И».
2. Старое условие будет первым, а второе – после точки с запятой.

[kod]=ЕСЛИ(И(C35=»М»;D35=»Основной штат»);3000;0)[/kod]

1. Продублируйте эту формулу до самого низа.

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

Оператор Или

Того же самого эффекта можно добиться и при помощи оператора «ИЛИ».

Для этого в формулу внесите следующие изменения:

1. Поменяйте букву «Ж» на «М».
2. Статус сотрудника измените на «Совместителя».
3. В поле истины поставьте 0, а для ложного события – 3000. Мы меняем их местами.

1. Продублируем формулу до самой последней строки.

1. Результат оказался точно таким же. Дело в том, что операторы «И» или «ИЛИ» являются полной противоположностью друг друга. Поэтому очень важно правильно указывать значения в поля для истины и лжи. Не ошибитесь.

1. Для проверки можете изменить статус одного сотрудника на «Основной штат».
2. Сразу после этого вы увидите, что напротив его фамилии появится число 3000.

Функция СУММЕСЛИ

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

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

Для этого используется функция «СУММЕСЛИ».

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

1. Начните вводить название функции. Справка появится автоматически.

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

1. Затем поставьте точку с запятой.
2. После этого в кавычках допишите слово «Продано» и снова ставим символ «;».

1. Теперь указываем поле, которое будем считать – также вручную.

1. Добавляем закрывающую скобку.

1. Нажимаем на кнопку [knopka]Enter[/knopka]. В результате этого вы увидите количество проданных товаров.

Функция СЧЁТЕСЛИ

Данная функция считает количество строк, которые соответствуют условию.

Считается не значение ячеек, а их количество.

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

[kod]=СЧЁТЕСЛИ(E2:E7;»Продано»)[/kod]

Результат будет следующим.

Возможные проблемы

Что делать, если возникает ошибка? Как правило, они отображаются следующим образом.

Для того чтобы попытаться исправить её, нужно сделать следующие действия.

1. Кликните на поле для ввода формулы.
2. Посмотрите внимательно – возможно, где-то не хватает скобки, кавычки или точки с запятой.
3. В нашем случае буква Ж (текстовый символ) указан не в кавычках.

1. После того как вы всё исправите, ошибка сразу исчезнет.

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

Заключение

В данной статье были рассмотрены различные способы применения функции «ЕСЛИ»:

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

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

Видеоинструкция

Если у вас остались какие-нибудь вопросы, то вы можете посмотреть видеоролик, приложенный ниже. В нем вы сможете найти дополнительные комментарии к описанной инструкции.