Функция поискпоз в excel примеры с несколькими условиями

4 комментариев

А как быть, если в поле «Наименование» вперемешку числовые и текстовые значения? У меня эксель числовые значения игнорирует, т.к. они числятся у него, как текстовые. К слову сказать, в таблице 56 тыс. записей и вручную всё пройти довольно сложно.

Не понял Вашего вопроса. Судя по всему, у Вас все данные хранятся, как текст. А что не работает?

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

Здравствуйте. Смотря что Вы называете однотипными данными. Опишите, пожалуйста, подробнее.

Поиск неточного совпадения с помощью ВПР

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

В массиве В5:С12 указаны процентные ставки по кредитам в зависимости от суммы займа. В ячейке В2 Указываем сумму кредита и хотим получить в С2 ставку для такой сделки. Задача сложна тем, что сумма может быть любой и вряд ли будет совпадать с указанными в массиве, поиск по точному совпадению не подходит:

Тогда запишем формулу нестрогого поиска: =ВПР(B2;B5:C12;2;ИСТИНА). Теперь из всех представленных в столбце В данных программа будет искать ближайшее меньшее. То есть, для суммы 8 000 будет отобрано значение 5000 и выведен соответствующий процент.

Нестрогий поиск ВПР в Excel

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

Функция ГПР имеет такой же синтаксис, как и ВПР, но ищет результат не в столбцах, а в строках. То есть, просматривает таблицы не сверху вниз, а слева направо и выводит заданный номер строки, а не столбца.

Поиск данных с помощью функции ПРОСМОТР

Функция ПРОСМОТР работает аналогично ВПР, но имеет другой синтаксис. Я использую её, когда таблица данных содержит несколько десятков столбцов и для использования ВПР нужно дополнительно просчитывать номер выводимой колонки. В таких случаях функция ПРОСМОТР облегчает задачу. И так, синтаксис: =ПРОСМОТР(Искомое_значение; Массив_для_поиска; Массив_для_отображения):

• Искомое значение – данные или ссылка на данные, которые нужно искать;
• Массив для поиска – одна строка или столбец, в котором ищем аналогичное значение. Данный массив обязательно сортируем по возрастанию;
• Массив для отображения – диапазон, содержащий данные для выведения результатов. Естественно, он должен одного размера с массивом для поиска.

При такой записи вы даёте не относительную ссылку массива результатов. А прямо на него указываете, т.е. не нужно предварительно просчитывать номер выводимого столбца. Используем функцию ПРОСМОТР в первом примере для функции ВПР (основные средства, инвентарные номера): =ПРОСМОТР(B2;B5:B10;D5:D10). Задача успешно решена!

Функция «ПРОСМОТР» в Microsoft Excel

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

Еще один способ поиска данных – комбинирование функций ПОИСКПОЗ и ИНДЕКС.

Первая из них, служит для поиска значения в массиве и получения его порядкового номера: ПОИСКПОЗ(Искомое_значение; Просматриваемый_массив; [Тип сопоставления). Аргументы функции:

• Искомое значение – обязательный аргумент
• Просматриваемый массив – одна строка или столбец, в котором ищем совпадение. Обязательный аргумент
• Тип сопоставления – укажите «0» для поиска точного совпадения, «1» — ближайшее меньшее, «-1» — ближайшее большее. Поскольку функция проводит поиск с начала списка в конец, при поиске ближайшего меньшего – отсортируйте столбец поиска по убыванию. А при поиске большего – сортируйте его по возрастанию.

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

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

Теперь скомбинируем эти функции, чтобы получить результат:

Функции ПОИСКПОЗ и ИНДЕКС в Эксель

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

Чувствуете, как растут и крепчают Ваши знания и умения? Тогда не останавливайтесь, продолжайте читать Officelegko.com! В следующем посте мы будем рассматривать расчеты кредита в Эксель: будет сложно и интересно!

Как работает функция?

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

С одним условием

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

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

2. В ячейке рядом ввести функцию ВПР через знак «=», либо же использовать опцию «Вставить функцию» в разделе «Формулы». Проще использовать именно опцию – там есть подсказки, какие именно данные нужны.

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

4. Нажать «Enter» – и появится результат поиска.

Когда таблица не отсортирована и данные введены в хаотичном порядке, результат будет неправильный – программа найдет ближайшее соответствие («ИСТИНА»). Но можно не сортировать таблицу, а указать интервальный просмотр «ЛОЖЬ».

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

С несколькими условиями

Часто на практике требуется сравнить данные нескольких диапазонов и выбрать значение с учетом 2-х и более критериев. Здесь задействована также функция ЕСЛИ, которая отвечает как раз за условия.

Из таблицы нужно найти показатель выручки по конкретному менеджеру в определенный день:

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

2. Кликнуть по пустой ячейке и ввести формулу =ВПР(G1;ЕСЛИ(C2:C12=G2;A2:D12;»»);4;0).

3. Для подтверждения действия зажать комбинацию клавиш Ctrl+Shift+Enter. Нажатие на «Enter» не сработает в этом случае, потому что формула должна быть выполнена в массиве (об этом свидетельствуют скобки «{}», в которые взята вся формула).

Поиск по нескольким столбцам

Объем данных расширен, и нужно найти конкретное значение среди нескольких столбцов, просуммировав данные с помощью функции СУММ.

1. Кликнуть по ячейке и ввести формулу =СУММ(ВПР(G1;A1:D12;{2;3;4};ЛОЖЬ)). Для третьего аргумента перечисление столбцов происходит в скобках «{}».

2. Одновременно зажать клавиши Ctrl+Shift+Enter. В результате формула будет взята в фигурные скобки «{}».

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

Таким же способом можно найти среднее значение с помощью СРЗНАЧ: =СРЗНАЧ(ВПР(G1;A1:D12;{2;3;4};ЛОЖЬ)).

Сравнение двух таблиц

ВПР помогает сопоставить значения в таблицах.

Необходимо сравнить зарплату сотрудников за 2 месяца, для этого:

1. В таблице с зарплатой за март добавить еще один столбец.

2. Клацнуть по первой ячейке в столбце и написать функцию ВПР со следующими аргументами: =ВПР($A$2:$A$12;ссылка_на_новый_лист!$A$2:$B$12;2;ЛОЖЬ). То есть нужно выделить диапазон с фамилиями менеджеров и сделать ссылки (строки и столбца) неизменными с помощью знака «$», посмотреть его в таблице с новой зарплатой, взять данные из второго столбца новой зарплаты и подставить их в ячейку С2. В результате отобразится первый результат.

3. При помощи маркера заполнения протянуть полученное значение вниз.

По желанию теперь можно найти численную и процентную разницу.

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

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

Для создания раскрывающегося списка:

1. Поставить курсор в ячейку, где он будет располагаться.

2. Перейти в раздел книги «Данные» – «Проверка данных».

3. В «Типе данных» выбрать «Список», задать диапазон (в нашем случае – фамилии менеджеров).

4. Нажать «Ок». Отобразится список.

5. В следующую ячейку вписать функцию ВПР. Первый аргумент – ссылка на раскрывающийся список, второй – диапазон таблицы, третий – номер столбца, четвертый – «ЛОЖЬ». В итоге получится следующее: =ВПР(E1;A1:B12;2;ЛОЖЬ). Нажать «Enter».

Меняется фамилия в списке – меняется и зарплата.

Перенос данных

Есть таблица с менеджерами и объемом их продаж. Во второй таблице значится сумма премии за продажу для каждого менеджера. Необходимо перенести данные в левую таблицу, чтобы подсчитать общую выручку (произведение объема продаж и премии за 1 продажу: =ПРОИЗВЕД(C2*D2)).

1. Выделить первую ячейку с премией в левой таблице. Написать функцию с аргументами, сделать неизменными значения из второй таблицы, указать в третьем аргументе столбец 2, вместо «ЛОЖЬ» можно вписать 0: =ВПР(B2;$G$2:$H$12;2;0)

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

Функции ИНДЕКС и ПОИСКПОЗ в Excel на простых примерах

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

Более подробно о функциях ВПР и ПРОСМОТР.

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

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

Например, на рисунке ниже формула вернет число 5, поскольку имя “Дарья” находится в пятой строке диапазона A1:A9.

В следующем примере формула вернет 3, поскольку число 300 находится в третьем столбце диапазона B1:I1.

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

• – функция ПОИСКПОЗ ищет первое значение в точности равное заданному. Сортировка не требуется.
• 1 или вовсе опущено – функция ПОИСКПОЗ ищет самое большое значение, которое меньше или равно заданному. Требуется сортировка в порядке возрастания.
• -1 – функция ПОИСКПОЗ ищет самое маленькое значение, которое больше или равно заданному. Требуется сортировка в порядке убывания.

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

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

Функция ИНДЕКС возвращает содержимое ячейки, которая находится на пересечении заданных строки и столбца. Например, на рисунке ниже формула возвращает значение из диапазона A1:C4, которое находится на пересечении 3 строки и 2 столбца.

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

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

Например, следующая формула возвращает пятое значение из диапазона A1:A12 (вертикальный вектор):

Данная формула возвращает третье значение из диапазона A1:L1(горизонтальный вектор):

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

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

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

Пускай ячейка C15 содержит указанный нами месяц, например, Май. А ячейка C16 – тип товара, например, Овощи. Введем в ячейку C17 следующую формулу и нажмем Enter:

=ИНДЕКС(B2:E13; ПОИСКПОЗ(C15;A2:A13;0); ПОИСКПОЗ(C16;B1:E1;0))

Как видите, мы получили верный результат. Если поменять месяц и тип товара, формула снова вернет правильный результат:

В данной формуле функция ИНДЕКС принимает все 3 аргумента:

1. Первый аргумент – это диапазон B2:E13, в котором мы осуществляем поиск.
2. Вторым аргументом функции ИНДЕКС является номер строки. Номер мы получаем с помощью функции ПОИСКПОЗ(C15;A2:A13;0). Для наглядности вычислим, что же возвращает нам данная формула:
3. Третьим аргументом функции ИНДЕКС является номер столбца. Этот номер мы получаем с помощью функции ПОИСКПОЗ(C16;B1:E1;0). Для наглядности вычислим и это значение:

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

=ИНДЕКС(B2:E13;D15;D16)

Как видите, все достаточно просто!

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

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

Еще один способ поиска данных – комбинирование функций ПОИСКПОЗ и ИНДЕКС.

Первая из них, служит для поиска значения в массиве и получения его порядкового номера: ПОИСКПОЗ(Искомое_значение; Просматриваемый_массив; [Тип сопоставления). Аргументы функции:

• Искомое значение – обязательный аргумент
• Просматриваемый массив – одна строка или столбец, в котором ищем совпадение. Обязательный аргумент
• Тип сопоставления – укажите «0» для поиска точного совпадения, «1» — ближайшее меньшее, «-1» — ближайшее большее. Поскольку функция проводит поиск с начала списка в конец, при поиске ближайшего меньшего – отсортируйте столбец поиска по убыванию. А при поиске большего – сортируйте его по возрастанию.

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

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

Теперь скомбинируем эти функции, чтобы получить результат:

Функции ПОИСКПОЗ и ИНДЕКС в Эксель

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

Чувствуете, как растут и крепчают Ваши знания и умения? Тогда не останавливайтесь, продолжайте читать Officelegko.com! В следующем посте мы будем рассматривать расчеты кредита в Эксель: будет сложно и интересно!

Как найти абсолютное максимальное значение в Excel

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

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

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

Не нравится это решение? Тогда давайте построим что-нибудь более жизнеспособное 🙂

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

С исходными данными в A1: B5 расчёты выглядят следующим образом.

Формула массива (заполняется с помощью Ctrl + Shift + Enter):

Обычная формула (заполняется клавишей Enter):

=МАКС(МАКС(A1:B5); -МИН(A1:B5))

или же

=МАКС(МАКС(A1:B5); ABS(МИН(A1:B5)))

На скриншоте ниже показаны результаты:

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

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

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

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

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

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

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

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

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

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

Функция ЕСЛИ в Excel (общие сведения)

Любая программа пусть это даже небольшая, обязательно содержит последовательность действий, которая называется алгоритмом. Выглядеть он может так:

1. Проверить всю колонку А на предмет наличия четных чисел.
2. Если четное число обнаружено, сложить такие-то значения.
3. Если четное число не обнаружено, то выдать надпись «не обнаружено».
4. Проверить получившееся число, является ли оно четным. 
5. Если да, то сложить его со всеми четными числами, отобранными в пункте 1.

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

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

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

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

Давайте рассмотрим синтаксис более подробно:

1. Логическое выражение. Это непосредственно условие, соответствие или несоответствие которому проверяет Excel. Проверяться могут как числовые, так и текстовая информация.
2. Значение_если_истина. Результат, который отобразится в ячейке в случае, если проверяемые данные соответствуют заданному критерию.
3. Значение_если_ложь. Результат, который выводится в ячейку, если проверяемые данные не соответствуют условию.

Вот пример для наглядности.

1

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

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

2

Поскольку нам нужно проверить текст, а не число, в первом аргументе указывается B2=”зач.”.

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

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

Чтобы было более наглядно, вот синтаксис.

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

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

Вот пример.

3

А с помощью такой формулы (показана на скриншоте ниже) можно сделать анализ успеваемости каждого студента.

4

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

Использование СЧЕТЕСЛИ для подсчета дубликатов.

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

1. Ищем дубликаты в одном столбце

Эта простое выражение СЧЁТЕСЛИ($A$2:$A$24;A2)>1 найдет все одинаковые записи в A2: A24.

А другая формула СЧЁТЕСЛИ(B2:B24;ИСТИНА) сообщит вам, сколько существует дубликатов:

Для более наглядного представления найденных совпадений я использовал условное форматирование значения ИСТИНА.

2. Сколько совпадений между двумя столбцами?

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

Выражение  =СЧЁТЕСЛИ($A$2:$A$24;C2) копируем вниз по столбцу Е.

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

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

Подсчитаем количество уникальных значений в списке2:

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

Полезное. Если вы хотите выделить дублирующиеся позиции или целые строки, содержащие повторяющиеся записи, вы можете создать правила условного форматирования на основе формул СЧЕТЕСЛИ, как показано в этом руководстве — правила условного форматирования Excel.

3. Сколько дубликатов и уникальных значений в строке?

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

Считаем количество дубликатов:

Видим, что 13 выпадало 2 раза.

Подсчитать уникальные значения:

Использование функций ИНДЕКС и ПОИСКПОЗ

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

Формула будет выглядеть следующим образом.

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

Сначала рассмотрим функцию ПОИСКПОЗ(1;(B2:B13=G1)*(C2:C13=G2);0). В данном случае последовательно сравнивается значение ячейки G1 с каждым значением ячеек диапазона B2:B13 и возвращается ИСТИНА, если значения совпадают и ЛОЖЬ, если нет. Такое же сравнение производится со значением ячейки G2 и диапазоном C2:C13. Далее мы сравниваем оба эти массива, состоящих из ИСТИНА и ЛОЖЬ. Комбинация ИСТИНА * ИСТИНА дает нам результат 1 (ИСТИНА). Давайте посмотрим на картинку ниже, которая поможет объяснить принцип работы более наглядно.

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

Сравнение двух таблиц в Excel на наличие несовпадений значений

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

Вид таблицы данных:

Для сравнения значений, находящихся в столбце B:B со значениями из столбца A:A используем следующую формулу массива (CTRL+SHIFT+ENTER):

Функция ПОИСКПОЗ выполняет поиск логического значения ИСТИНА в массиве логических значений, возвращаемых функцией СОВПАД (сравнивает каждый элемент диапазона A2:A12 со значением, хранящимся в ячейке B2, и возвращает массив результатов сравнения). Если функция ПОИСКПОЗ нашла значение ИСТИНА, будет возвращена позиция его первого вхождения в массив. Функция ЕНД возвратит значение ЛОЖЬ, если она не принимает значение ошибки #Н/Д в качестве аргумента. В этом случае функция ЕСЛИ вернет текстовую строку «есть», иначе – «нет».

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

Как видно, третьи элементы списков не совпадают.

Часто задаваемые вопросы и проблемы.

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

1. Возможен ли подсчет в несмежном диапазоне клеток?

Вопрос: Как я могу использовать СЧЕТЕСЛИ для несмежного диапазона или ячеек?

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

Неправильно: =СЧЕТЕСЛИ(A2;B3;C4;»>0″)

Правильно: = СЧЕТЕСЛИ (A2;»>0″) + СЧЕТЕСЛИ (B3;»>0″) + СЧЕТЕСЛИ (C4;»>0″)

Альтернативный способ — использовать функцию ДВССЫЛ (INDIRECT) для создания массива из несмежных клеток. Например, оба приведенных ниже варианта дают одинаковый результат, который вы видите на картинке:

=СУММ(СЧЁТЕСЛИ(ДВССЫЛ({«B2:B11″;»D2:D11″});»=0»))

Или же

=СЧЕТЕСЛИ($B2:$B11;0) + СЧЕТЕСЛИ($D2:$D11;0)

1. Амперсанд и кавычки в формулах СЧЕТЕСЛИ

Вопрос: когда мне нужно использовать амперсанд?

Ответ: Это, пожалуй, самая сложная часть функции СЧЕТЕСЛИ, что лично меня тоже смущает. Хотя, если вы подумаете об этом, вы увидите — амперсанд и кавычки необходимы для построения текстовой строки для аргумента.

Итак, вы можете придерживаться этих правил:

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

= СЧЕТЕСЛИ(A1:A10;10) или = СЧЕТЕСЛИ(A1:A10;C1)

Если ваши условия содержат текст, подстановочный знак или логический оператор с числом, заключите его в кавычки. Например:

= СЧЕТЕСЛИ(A2:A10;»яблоко») или = СЧЕТЕСЛИ(A2:A10;»*») или = СЧЕТЕСЛИ(A2:A10;»>5″)

Если ваши критерии — это выражение со ссылкой или же какая-то другая функция Excel, вы должны использовать кавычки («») для начала текстовой строки и амперсанд (&) для конкатенации (объединения) и завершения строки. Например:

= СЧЕТЕСЛИ(A2:A10;»>»&D2) или = СЧЕТЕСЛИ(A2:A10;»<=»&СЕГОДНЯ())

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

Например, = СЧЕТЕСЛИ(C2: C8;»<=5″) и = СЧЕТЕСЛИ(C2: C8;»<=»&5) работают одинаково хорошо.

1. Как сосчитать ячейки по цвету?

Вопрос: Как подсчитать клетки по цвету заливки или шрифта, а не по значениям?

Ответ: К сожалению, синтаксис функции не позволяет использовать форматы в качестве условия. Единственный возможный способ суммирования ячеек на основе их цвета — использование макроса или, точнее, пользовательской функции Excel VBA.

1. Ошибка #ИМЯ?

Проблема: все время получаю ошибку #ИМЯ? Как я могу это исправить?

Ответ: Скорее всего, вы указали неверный диапазон. Пожалуйста, проверьте пункт 1 выше.

1. Формула не работает

Проблема: моя формула не работает! Что я сделал не так?

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

Будьте очень осторожны с использованием пробелов. При создании одной из формул для этой статьи я был уже готов рвать волосы, потому что правильная конструкция (я точно знал, что это правильно!) не срабатывала. Как оказалось, проблема была на самом виду… Например, посмотрите на это: =СЧЁТЕСЛИ(A4:A13;» Лимонад»). На первый взгляд, нет ничего плохого, кроме дополнительного пробела после открывающей кавычки. Программа отлично проглотит всё без сообщения об ошибке, предупреждения или каких-либо других указаний. Но если вы действительно хотите посчитать товары, содержащие слово «Лимонад» и начальный пробел, то будете очень разочарованы….

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

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

Ещё примеры расчета суммы:

5
1
голос

Рейтинг статьи

Функция ПОИСКПОЗ в Excel – синтаксис и использование

Функция ПОИСКПОЗ в Excel ищет заданное значение в диапазоне ячеек и возвращает относительное положение этого значения.

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

ПОИСКПОЗ(искомое_значение;просматриваемый_массив;)

Искомое_значение (обязательный аргумент) – значение, которое вы хотите найти. Это может быть числовое, текстовое или логическое значение, а также ссылка на ячейку.

Просматриваемый_массив (обязательный аргумент) – диапазон ячеек для поиска.

Тип_сопоставления (необязательный аргумент) – определяет тип соответствия. Это может быть одно из следующих значений: 1, 0, -1.

• 1 или отсутствует (по умолчанию) – находит наибольшее значение в массиве, которое меньше или равно значению поиска. Массив поиска должен быть отсортирован в порядке возрастания, от самого маленького до большого или от A до Z.
• 0 – находит первое значение в массиве, которое в точности равно значению поиска. Сортировка не требуется.
• -1 – находит наименьшее значение в массиве, которое больше или равно значению поиска. Массив поиска должен быть отсортирован в порядке убывания, от самого большого до самого маленького или от Z до A.

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

=ПОИСКПОЗ(E1; A2:A8; 0)

При желании вы можете поместить значение поиска в ячейку (E1 в данном примере) и сослаться на эту ячейку в формуле ПОИСКПОЗ Excel:

Функция ПОИСКПОЗ в Excel – Пример использования функции ПОИСКПОЗ в Excel

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

Особенности функции ПОИСКПОЗ Excel

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

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

Совпадение в столбцах Excel

Сегодня расскажу как искать совпадение в столбцах Excel. Разберем все тонкости на примерах.

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

Использовать будем Условное форматирование.

• Выбираем ячейки, которые необходимо сравнить;
• Во вкладке Главная переходим «Условное форматирование -> Правила выделения ячеек -> Повторяющиеся значения»;

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

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

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

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

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

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

Искать таким образом можно буквы, слова, символы, тексты и т.д.

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

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

Например, на рисунке ниже формула вернет число 5, поскольку имя “Дарья” находится в пятой строке диапазона A1:A9.

В следующем примере формула вернет 3, поскольку число 300 находится в третьем столбце диапазона B1:I1.

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

• – функция ПОИСКПОЗ ищет первое значение в точности равное заданному. Сортировка не требуется.
• 1 или вовсе опущено – функция ПОИСКПОЗ ищет самое большое значение, которое меньше или равно заданному. Требуется сортировка в порядке возрастания.
• -1 – функция ПОИСКПОЗ ищет самое маленькое значение, которое больше или равно заданному. Требуется сортировка в порядке убывания.

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