Excel если таблица представлена по строкам - Word и Excel

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

Формула ГПР и выборка по строкам в Excel для чайников

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

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

Формула ИНДЕКС и ПОИСКПОЗ для горизонтальной выборки по строкам

Функции ГПР и ВПР очень похожи между собой. Их обе можно заменить формулами из функций ИНДЕКС и ПОИСКПОЗ:

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

Источник

Логическая функция ЕСЛИ в Экселе – одна из самых востребованных. Она возвращает результат (значение или другую формулу) в зависимости от условия.

Функция имеет следующий синтаксис.

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

лог_выражение – это проверяемое условие. Например, A2<100. Если значение в ячейке A2 действительно меньше 100, то в памяти эксель формируется ответ ИСТИНА и функция возвращает то, что указано в следующем поле. Если это не так, в памяти формируется ответ ЛОЖЬ и возвращается значение из последнего поля.

значение_если_истина – значение или формула, которое возвращается при наступлении указанного в первом параметре события.

значение_если_ложь – это альтернативное значение или формула, которая возвращается при невыполнении условия. Данное поле не обязательно заполнять. В этом случае при наступлении альтернативного события функция вернет значение ЛОЖЬ.

Очень простой пример. Нужно проверить, превышают ли продажи отдельных товаров 30 шт. или нет. Если превышают, то формула должна вернуть «Ок», в противном случае – «Удалить». Ниже показан расчет с результатом.

Продажи первого товара равны 75, т.е. условие о том, что оно больше 30, выполняется. Следовательно, функция возвращает то, что указано в следующем поле – «Ок». Продажи второго товара менее 30, поэтому условие (>30) не выполняется и возвращается альтернативное значение, указанное в третьем поле. В этом вся суть функции ЕСЛИ. Протягивая расчет вниз, получаем результат по каждому товару.

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

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

В прогнозе запасов больше нет отрицательных значений, что в целом очень неплохо.

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

Формула ЕСЛИ в Excel – примеры нескольких условий

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

Нескольким менеджерам по продажам нужно начислить премию в зависимости от выполнения плана продаж. Система мотивации следующая. Если план выполнен менее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно здесь 4 варианта. Чтобы их указать в одной формуле потребуется следующая логическая структура. Если выполняется первое условие, то наступает первый вариант, в противном случае, если выполняется второе условие, то наступает второй вариант, в противном случае если… и т.д. Количество условий может быть довольно большим. В конце формулы указывается последний альтернативный вариант, для которого не выполняется ни одно из перечисленных ранее условий (как третье поле в обычной формуле ЕСЛИ). В итоге формула имеет следующий вид.

Комбинация функций ЕСЛИ работает так, что при выполнении какого-либо указанно условия следующие уже не проверяются. Поэтому важно их указать в правильной последовательности. Если бы мы начали проверку с B2<1, то условия B2<0,9 и B2<0,95 Excel бы просто «не заметил», т.к. они входят в интервал B2<1 который проверился бы первым (если значение менее 0,9, само собой, оно также меньше и 1). И тогда у нас получилось бы только два возможных варианта: менее 1 и альтернативное, т.е. 1 и более.

При написании формулы легко запутаться, поэтому рекомендуется смотреть на всплывающую подсказку.

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

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

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

В MS Excel 2016 появилась функция ЕСЛИМН, ради которой и написана вся эта статья. Это та же ЕСЛИ, только заточенная специально для проверки множества условий. Теперь не нужно сто раз писать ЕСЛИ и считать открытые скобки. Достаточно перечислить условия и в конце закрыть одну скобку.

Работает следующим образом. Возьмем пример выше и воспользуемся новой формулой Excel ЕСЛИМН.

Как видно, запись формулы выглядит гораздо проще и понятнее.

Стоит обратить внимание на следующее. Условия по-прежнему перечисляем в правильном порядке, чтобы не произошло ненужного перекрытия диапазонов. Последнее альтернативное условие, в отличие от обычной ЕСЛИ, также должно быть обязательно указано. В ЕСЛИ задается только альтернативное значение, которое наступает, если не выполняется ни одно из перечисленных условий. Здесь же нужно указать само условие, которое в нашем случае было бы B2>=1. Однако этого можно избежать, если в поле с условием написать ИСТИНА, указывая тем самым, что, если не выполняются ранее перечисленные условия, наступает ИСТИНА и возвращается последнее альтернативное значение.

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

Поделиться в социальных сетях:

Источник

8 способов как сравнить две таблицы в Excel

Добрый день!

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

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

Рассмотрим несколько вариантов и возможностей для сравнения таблиц в Excel:

Простой способ, как сравнить две таблицы в Excel

Это самые простой и элементарный способ сравнения двух таблиц. Сравнивать таким способом возможно, как числовые значение, так и текстовые. Для примера сравним два диапазона числовых значений, всего на всего прописав в соседней ячейке формулу их равенства =C2=E2, как результат при равенстве ячеек мы получим ответ «ИСТИНА», а если совпадений нет, будет «ЛОЖЬ». Теперь простым авто копированием копируем на весь диапазон нашу формулу позволяющую сравнить два столбика в Excel и видим разницу.

Быстрое выделение значений, которые отличаются

Это также не очень обременительный способ. Если вам просто нужно найти и удостовериться в наличии, ну или отсутствии отличий между таблицами, вам нужно на вкладке «Главная», выбрать кнопку меню «Найти и выделить», предварительно выделив диапазон где надо сравнить две таблицы в Excel. В открывшимся меню выберите пункт «Выделить группу ячеек…» и в появившемся диалоговом окне выберите «отличия по строкам».

Сравнить две таблицы в Excel с помощью условного форматирования

Очень хороший способ, при котором вы сможете видеть выделенным цветом значение, которые при сличении двух таблиц отличаются. Применить условное форматирование вы можете на вкладке «Главная», нажав кнопку «Условное форматирование» и в предоставленном списке выбираем «Управление правилами». В диалоговом окне «Диспетчер правил условного форматирования», жмем кнопочку «Создать правило» и в новом диалоговом окне «Создание правила форматирования», выбираем правило «Использовать формулу для определения форматируемых ячеек». В поле «Изменить описание правила» вводим формулу =$C2<>$E2 для определения ячейки, которое нужно форматировать, и нажимаем кнопку «Формат». Определяем стиль того, как будет форматироваться наше значение, которое соответствует критерию. Теперь в списке правил появилось наше ново сотворённое правило, вы его выбираете, нажимаете «Ок».

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

Как сравнить две таблицы в Excel с помощью функции СЧЁТЕСЛИ и правил

Все вышеперечисленные способы хороши для упорядоченных таблиц, а вот когда данные, не упорядоченные необходимы иные способы один из которых мы сейчас и рассмотрим. Представим, к примеру, у нас есть 2 таблицы, значения в которых немного отличаются и нам необходимо сравнить эти таблицы для определения значения, которое отличается. Выделяем значение в диапазоне первой таблицы и на вкладке «Главная», пункт меню «Условное форматирование» и в списке жмем пункт «Создать правило…», выбираем правило «Использовать формулу для определения форматируемых ячеек», вписываем формулу =СЧЁТЕСЛИ($C$1:$C$7;C1)=0 и выбираем формат условного форматирования.

Формула проверяет значение из определенной ячейки C1 и сравнивает ее с указанным диапазоном $C$1:$C$7 из второго столбика. Копируем правило на весь диапазон, в котором мы сравниваем таблицы и получаем выделенные цветом ячейки значения, которых не повторяется.

Как сравнить две таблицы в Excel с помощью функции ВПР

В этом варианте мы будем использовать функцию ВПР, которая позволит нам сравнить две таблицы на предмет совпадений. Для сравнения двух столбиков, введите формулу =ВПР(C2;$D$2:$D$7;1;0) и скопируйте ее на весь сравниваемый диапазон. Эта формула последовательно начинает проверять есть ли повторы значения из столбика А в столбике В, ну и соответственно возвращает значение элемента, если оно было там найдено если же значение не найдено получаем ошибку #Н/Д.

Как сравнить две таблицы в Excel функции ЕСЛИ

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

Для примера, сравним два столбика А и В на рабочем листе, в соседней колонке С введем формулу: =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(C2;$E$2:$E$7;0));»»;C2) и копируем ее на весь вычисляемый диапазон. Эта формула позволяет просматривать последовательно есть ли определенные элементы из указанного столбика А в столбике В и возвращает значение, в случае если оно было найдено в столбике В.

Сравнить две таблицы с помощью макроса VBA

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

Как сравнить два столбца в Excel на совпадения

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

Как сравнить два столбца в Excel по строкам

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

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

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

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

=ЕСЛИ(A2=B2; “Совпадают”; “”)

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

=ЕСЛИ(A2<>B2; “Не совпадают”; “”)

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

=ЕСЛИ(A2=B2; “Совпадают”; “Не совпадают”)

=ЕСЛИ(A2<>B2; “Не совпадают”; “Совпадают”)

Пример результата вычислений может выглядеть так:

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

=ЕСЛИ(СОВПАД(A2,B2); “Совпадает”; “Уникальное”)

Как сравнить несколько столбцов на совпадения в одной строке Excel

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

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

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

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

=ЕСЛИ(И(A2=B2;A2=C2); “Совпадают”; ” “)

Если в нашей таблице очень много столбцов, то более просто будет использовать функцию СЧЁТЕСЛИ в сочетании с ЕСЛИ :

=ЕСЛИ(СЧЁТЕСЛИ($A2:$C2;$A2)=3;”Совпадают”;” “)

В формуле в качестве “5” указано число столбцов таблицы, для которой мы создали формулу. Если в вашей таблице столбцов больше или меньше, то это значение должно быть равно количеству столбцов.

Пример 2. Как найти совпадения в одной строке в любых двух столбцах таблицы

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

=ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “)

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

=ЕСЛИ(СЧЁТЕСЛИ(B2:D2;A2)+СЧЁТЕСЛИ(C2:D2;B2)+(C2=D2)=0; “Уникальная строка”; “Не уникальная строка”)

Первая функция СЧЁТЕСЛИ вычисляет количество столбцов в строке со значением в ячейке А2 , вторая функция СЧЁТЕСЛИ вычисляет количество столбцов в таблице со значением из ячейки B2 . Если результат вычисления равен “0” – это означает, что в каждой ячейке, каждого столбца, этой строки находятся уникальные значения. В этом случае формула выдаст результат “Уникальная строка”, если нет, то “Не уникальная строка”.

Как сравнить два столбца в Excel на совпадения

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

=ЕСЛИ(СЧЁТЕСЛИ($B:$B;$A5)=0; “Нет совпадений в столбце B”; “Есть совпадения в столбце В”)

Эта формула проверяет значения в столбце B на совпадение с данными ячеек в столбце А.

Если ваша таблица состоит из фиксированного числа строк, вы можете указать в формуле четкий диапазон (например, $B2:$B10 ). Это позволит ускорить работу формулы.

Как сравнить два столбца в Excel на совпадения и выделить цветом

Когда мы ищем совпадения между двумя столбцами в Excel, нам может потребоваться визуализировать найденные совпадения или различия в данных, например, с помощью выделения цветом. Самый простой способ для выделения цветом совпадений и различий – использовать “Условное форматирование” в Excel. Рассмотрим как это сделать на примерах ниже.

Поиск и выделение совпадений цветом в нескольких столбцах в Эксель

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

Выделить столбцы с данными, в которых нужно вычислить совпадения;
На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
Во всплывающем диалоговом окне выберите в левом выпадающем списке пункт “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
После этого в выделенной колонке будут подсвечены цветом совпадения:

Поиск и выделение цветом совпадающих строк в Excel

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

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

Рассмотрим как найти совпадающие строки в таблице:

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

=A2&B2&C2&D2

Во вспомогательной колонке вы увидите объединенные данные таблицы:

Теперь, для определения совпадающих строк в таблице сделайте следующие шаги:

Выделите область с данными во вспомогательной колонке (в нашем примере это диапазон ячеек E2:E15 );
На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
Во всплывающем диалоговом окне выберите в левом выпадающем списке “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
После этого в выделенной колонке будут подсвечены дублирующиеся строки:

На примере выше, мы выделили строки в созданной вспомогательной колонке.

Но что, если нам нужно выделить цветом строки не во вспомогательном столбце, а сами строки в таблице с данными?

Для этого сделаем следующее:

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

=A2&B2&C2&D2

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

Теперь, выделим все данные таблицы (за исключением вспомогательного столбца). В нашем случае это ячейки диапазона A2:D15 ;
Затем, на вкладке “Главная” на Панели инструментов нажмем на пункт “Условное форматирование” -> “Создать правило”:

В диалоговом окне “Создание правила форматирования” кликните на пункт “Использовать формулу для определения форматируемых ячеек” и в поле “Форматировать значения, для которых следующая формула является истинной” вставьте формулу:

=СЧЁТЕСЛИ($E$2:$E$15;$E2)>1

Не забудьте задать формат найденных дублированных строк.

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

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

Не все фирмы покупают специальные программы для ведения дел. Многие пользуются MS Excel, ведь эта хо.

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

Где находится условное форматирование

Как в экселе менять цвет ячейки в зависимости от значения – да очень просто и быстро. Для выделения ячеек цветом предусмотрена специальная функция «Условное форматирование», находящаяся на вкладке «Главная»:

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

Правила выделения ячеек

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

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

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

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

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

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

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

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

Гистограммы

Если заливка ячейки цветом вас не устраивает – применяйте инструмент «Гистограмма». Предлагаемая окраска легче воспринимается на глаз в большом объеме информации, функциональные правила подстраиваются под требования пользователя.

Цветовые шкалы

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

Наборы значков

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

Создание, удаление и управление правилами

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

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

Вызывает интерес инструмент «Управление правилами» – своеобразная история создания и изменения проведенных форматирований. Меняйте подборки, делайте правила неактивными, возвращайте обратно, чередуйте порядок применения. Для работы с большим объемом информации это очень удобно.

Отбор ячеек по датам

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

Для отбора нужных дат применяем такую последовательность действий:

выделяем столбцы с датами (в нашем случае за январь);
находим инструмент «Условное форматирование»;
в «Правилах выделения ячеек» выбираем пункт «Дата»;
в правой части форматирования открываем выпадающее окно с правилами;
выбираем подходящее правило (на примере выбраны даты за предыдущий месяц);
в левом поле устанавливаем готовый цветовой подбор «Желтая заливка и темно-желтый текст»
выборка окрасилась, жмем «ОК».

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

Выделение цветом столбца по условию

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

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

выделяем столбец с январскими закупками;
кликаем инструмент «Условное форматирование»;
переходим в «Правила выделения ячеек»;
пункт «Больше…»;
в правой части форматирования устанавливаем сумму 100 000 рублей;
в левом поле переходим на вкладку «Пользовательский формат» и выбираем синий цвет;
необходимая выборка окрасилась в синий цвет, жмем «ОК».

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

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

Поиск отличий в двух списках

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

Вариант 1. Синхронные списки

Если списки синхронизированы (отсортированы), то все делается весьма несложно, т.к. надо, по сути, сравнить значения в соседних ячейках каждой строки. Как самый простой вариант — используем формулу для сравнения значений, выдающую на выходе логические значения ИСТИНА (TRUE) или ЛОЖЬ (FALSE) :

Число несовпадений можно посчитать формулой:

или в английском варианте =SUMPRODUCT(—(A2:A20<>B2:B20))

Если в результате получаем ноль — списки идентичны. В противном случае — в них есть различия. Формулу надо вводить как формулу массива, т.е. после ввода формулы в ячейку жать не на Enter, а на Ctrl+Shift+Enter.

Если с отличающимися ячейками надо что сделать, то подойдет другой быстрый способ: выделите оба столбца и нажмите клавишу F5, затем в открывшемся окне кнопку Выделить (Special) — Отличия по строкам (Row differences) . В последних версиях Excel 2007/2010 можно также воспользоваться кнопкой Найти и выделить (Find & Select) — Выделение группы ячеек (Go to Special) на вкладке Главная (Home)

Excel выделит ячейки, отличающиеся содержанием (по строкам). Затем их можно обработать, например:

залить цветом или как-то еще визуально отформатировать
очистить клавишей Delete
заполнить сразу все одинаковым значением, введя его и нажав Ctrl+Enter
удалить все строки с выделенными ячейками, используя команду Главная — Удалить — Удалить строки с листа (Home — Delete — Delete Rows)
и т.д.

Вариант 2. Перемешанные списки

Если списки разного размера и не отсортированы (элементы идут в разном порядке), то придется идти другим путем.

Самое простое и быстрое решение: включить цветовое выделение отличий, используя условное форматирование. Выделите оба диапазона с данными и выберите на вкладке Главная — Условное форматирование — Правила выделения ячеек — Повторяющиеся значения (Home — Conditional formatting — Highlight cell rules — Duplicate Values):

Если выбрать опцию Повторяющиеся, то Excel выделит цветом совпадения в наших списках, если опцию Уникальные — различия.

Цветовое выделение, однако, не всегда удобно, особенно для больших таблиц. Также, если внутри самих списков элементы могут повторяться, то этот способ не подойдет.

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

Полученный в результате ноль и говорит об отличиях.

И, наконец, «высший пилотаж» — можно вывести отличия отдельным списком. Для этого придется использовать формулу массива:

Выглядит страшновато, но свою работу выполняет отлично 😉

Как сравнить и выделить цветом ячейки Excel?

Выбирая инструменты на закладке: «ГЛАВНАЯ» в разделе «Стили» из выпадающего меню «Условное форматирование» нам доступна целая группа «Правила отбора первых и последних значений». Однако часто необходимо сравнить и выделить цветом ячейки в Excel, но ни один из вариантов готовых решений не соответствует нашим условиям. Например, в конструкции условия мы хотим использовать больше критериев или выполнять более сложные вычисления. Всегда можно выбрать последнюю опцию «Другие правила» она же является опцией «Создать правило». Условное форматирование позволяет использовать формулу для создания сложных критериев сравнения и отбора значений. Создавая свои пользовательские правила для условного форматирования с использованием различных формул мы себя ничем не ограничиваем.

Как сравнить столбцы в Excel и выделить цветом их ячейки?

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

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

Выделите диапазон ячеек D2:D12 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
В появившемся окне «Создание правила форматирования» выберите опцию «Использовать формулу для определения форматированных ячеек».
В поле ввода введите формулу:
Нажмите на кнопку «Формат» и в появившемся окне «Формат ячеек» на вкладке «Заливка» выберите красный цвет для данного правила, а на вкладке «Шрифт» – белый цвет. После на всех открытых окнах жмем ОК.

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

Как выделить цветом ячейку в Excel по условию?

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

Не снимая выделения с диапазона D2:D12 снова выберите инструмент «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
Так же в появившемся окне «Создание правила форматирования» выберите опцию «Использовать формулу для определения форматированных ячеек».
В поле ввода введите формулу:
Нажмите на кнопку «Формат» и в появившемся окне «Формат ячеек» на вкладке «Заливка» выберите оранжевый цвет. На всех открытых окнах жмем ОК.

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

Выберите новое оранжевое правило в появившемся окне «Диспетчер правил условного форматирования» и нажмите на кнопку «Вниз» (CTRL+стрелка вниз), как показано на рисунке:

Как видите последовательность правил очень важна если их много присвоено для одного и того же диапазона ячеек:

На первый взгляд может показаться что несколько правил могут форматировать одну и туже ячейку одновременно. В принципе это так, но при определенном условии, что все правила будут использовать разные типы форматирования. Например, правило 1 – изменяет шрифт, 2 – меняет заливку, 3 – добавляет границу, 4 – узор и т.д. Но если после выполнения любого правила, когда его условие выполнено, было проверено следующее правило для данной ячейки, тогда следует в окне диспетчера отметить галочкой в колонке «Остановить если истина»:

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

0;D2>C2)’ class=’formula’>

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

Примечание. В формуле можно использовать любые ссылки для текущего листа. В версии Excel 2010 можно ссылаться и на другие листы. А в Excel 2007 к другим листам можно обращаться только через имена диапазонов. Мы рекомендуем во всех версиях Excel ссылаться на другие листы через имена, так как это позволяет избежать множество ошибок при создании пользовательских правил для условного форматирования.

Источник

Как сравнить две строки в Excel

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

Инструкция

Для подсчета совпадений с определенным словом или словосочетанием нужно использовать функцию программы СЧЁТЕСЛИ. Для подсчета совпадений информации в столбце А с искомой фразой установите курсор в ячейку, предназначенную для выведения результата и в строку формул впишите =СЧЁТЕСЛИ($A:$A; «Фраза»). В данном случае программа будет подсчитывать количество слова «Фраза» в столбце А.

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

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

Если вам нужно сравнить с образцом одновременно информацию из нескольких ячеек, вам придется использовать опции ЕСЛИ и СЦЕПИТЬ. Для соединения данных из нескольких ячеек используйте опцию СЦЕПИТЬ(А1; «и» ;B1), в данном случае программа использует текст из ячейки А1 и В1, а между ними вставит союз «и». Для сравнения большого текста удобнее будет присвоить ячейке с данным текстом собственное имя, для этого обозначьте данную ячейку и в поле, расположенное левее строки функций, впишите для него имя, например, «Образец». После выполнения подготовки выделите ячейку, в которой должен быть результат сравнения, и впишите в него следующую формулу: ЕСЛИ(СЦЕПИТЬ(A1; «и» ;B1)=образец;1;0). В данном случае, если сравнение даст положительный результат, то в ячейке результата будет вписана единица, если совпадений обнаружено не будет – ноль. Для того, чтобы применить данную формулу к нескольким строкам таблицы, нужно ее выделить, навести на маркер в правом нижнем углу и растянуть ее на нужные строки.

Внимание! Для корректной работы программы Excel мы советуем использовать только 100% лицензионную версию, которую вы можете купить со скидкой в нашем интернет-магазине.

Видео: Функция СЦЕПИТЬ в Excel

Как сравнить два столбца в Excel на совпадения

Как сравнить два столбца в Excel по строкам

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

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

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

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

=ЕСЛИ(A2=B2; “Совпадают”; “Не совпадают”)

=ЕСЛИ(A2<>B2; “Не совпадают”; “Совпадают”)

Пример результата вычислений может выглядеть так:

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

=ЕСЛИ(СОВПАД(A2,B2); “Совпадает”; “Уникальное”)

Как сравнить несколько столбцов на совпадения в одной строке Excel

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

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

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

Пример 2. Как найти совпадения в одной строке в любых двух столбцах таблицы

=ЕСЛИ(СЧЁТЕСЛИ(B2:D2;A2)+СЧЁТЕСЛИ(C2:D2;B2)+(C2=D2)=0; “Уникальная строка”; “Не уникальная строка”)

Как сравнить два столбца в Excel на совпадения

=ЕСЛИ(СЧЁТЕСЛИ($B:$B;$A5)=0; “Нет совпадений в столбце B”; “Есть совпадения в столбце В”)

Эта формула проверяет значения в столбце B на совпадение с данными ячеек в столбце А.

Если ваша таблица состоит из фиксированного числа строк, вы можете указать в формуле четкий диапазон (например, $B2:$B10 ). Это позволит ускорить работу формулы.

Как сравнить два столбца в Excel на совпадения и выделить цветом

Поиск и выделение совпадений цветом в нескольких столбцах в Эксель

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

Выделить столбцы с данными, в которых нужно вычислить совпадения;
На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
Во всплывающем диалоговом окне выберите в левом выпадающем списке пункт “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
После этого в выделенной колонке будут подсвечены цветом совпадения:

Поиск и выделение цветом совпадающих строк в Excel

Рассмотрим как найти совпадающие строки в таблице:

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

Во вспомогательной колонке вы увидите объединенные данные таблицы:

Теперь, для определения совпадающих строк в таблице сделайте следующие шаги:

Выделите область с данными во вспомогательной колонке (в нашем примере это диапазон ячеек E2:E15 );
На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
Во всплывающем диалоговом окне выберите в левом выпадающем списке “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
После этого в выделенной колонке будут подсвечены дублирующиеся строки:

На примере выше, мы выделили строки в созданной вспомогательной колонке.

Для этого сделаем следующее:

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

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

Теперь, выделим все данные таблицы (за исключением вспомогательного столбца). В нашем случае это ячейки диапазона A2:D15 ;
Затем, на вкладке “Главная” на Панели инструментов нажмем на пункт “Условное форматирование” -> “Создать правило”:

В диалоговом окне “Создание правила форматирования” кликните на пункт “Использовать формулу для определения форматируемых ячеек” и в поле “Форматировать значения, для которых следующая формула является истинной” вставьте формулу:

Не забудьте задать формат найденных дублированных строк.

8 способов как сравнить две таблицы в Excel

Добрый день!

Рассмотрим несколько вариантов и возможностей для сравнения таблиц в Excel:

Простой способ, как сравнить две таблицы в Excel

Быстрое выделение значений, которые отличаются

Сравнить две таблицы в Excel с помощью условного форматирования

Как сравнить две таблицы в Excel с помощью функции СЧЁТЕСЛИ и правил

Как сравнить две таблицы в Excel с помощью функции ВПР

Как сравнить две таблицы в Excel функции ЕСЛИ

Сравнить две таблицы с помощью макроса VBA

Частичное текстовое сравнение ячеек Excel

Пикабу, помоги, пожалуйста!

Есть две текстовые ячейки Экселя. В одной, скажем, «абырвалг», в другой — «абырвалг; главсельхоз; спасиботов» или «главсельхоз; спасиботов» (или ещё что угодно, кроме абырвалга). Какой формулой посчитать, что во второй ячейке есть «абырвалг» или процент совпадения текстов на предмет элемента «абырвалг»?

Пробовал надстройку «Fuzzy Lookup», но при попытке включения графы similarity она либо уходит в бесконечность, либо Эксель зависает.

Анализировать десятки (сотни) записей руками — глаза сломаешь, да и времени потребуется огромное количество.

Дубликаты не найдены

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

=ЕСЛИОШИБКА(ЕСЛИ(ПОИСК(B2;K2)>0;»true»;»not true»);»not true»)

народ совсем оленился, в гугле первый же ответ: Функция ПОИСК() в MS EXCEL

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

СУММЕСЛИ или СУММЕСЛИМН практически одинаковые, разбив предварительно на слова по разным ячейка по ;

Суммесли не сработает. Ты с другой похожей функцией спутал

Образец — =ПСТР взять из текста нужную построку

Поиск- =НАЙТИ или ПОИСК — искать образцовую строку в тексте

Фильтр по маске

Из простого только fuzzy. Я обычно им свожу.

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

Ответы.мэйл.ру на другом сайте.

Наша победа! Сообществу быть!

Уважаемые подписчики, спешу вас обрадовать @SupportCommunity разрешил создать сообщество посвящённое Office. Спасибо вам за поддержку))

Сообщество будет посвящено MS Office, Libreoffice и Google docs.

Я хочу чтобы сообщество приносило пользу многим, дабы облегчить работу офисному брату))

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

Лига Microsoft Office

Уважаемая администрация сайта и подписчики, я хочу создать Лигу Microsoft Office, в которой будут размещаться посты про Word, Excel, Access, Power Point, Outlook, Publisher и др., где пользователи смогут узнать что-то новое или поделиться своим опытом.

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

Тем кому будет не интересна данная тематика смогут поставить сообщество в игнор.

Ссылки на мои посты:

Остальные опубликованы в сообществе «Лайфхак»
Ссылка на сообщество MS, Libreoffice & Google docs

Источник

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

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

Второй аргумент – какой результат появится в ячейке (число или текст), когда значение отвечает указанному условию (да – ИСТИНА).

Третий аргумент – какой результат появится в ячейке (число или текст), когда значение не отвечает указанному условию (нет – ЛОЖЬ).

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

В русифицированной версии Excel аргументы нужно писать через точку с запятой («;»), в англоязычной – через запятую.

Мнение эксперта

Витальева Анжела, консультант по работе с офисными программами

Со всеми вопросами обращайтесь ко мне!

Задать вопрос эксперту

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

«Ставка»

«=»

«Лист 2»

Эксель формула если ячейка содержит текст то –

В выделенную ячейку вписать формулу =ЕСЛИ(B2>90;»Отлично»;ЕСЛИ(B2>75;»Хорошо»;»Удовлетворительно»)) и нажать на кнопку «Enter». Сначала оператор проверит условие B2>90. ИСТИНА – отобразится «отлично», а остальные критерии не обработаются. ЛОЖЬ – проверит следующее условие (B2>75). Если оно будет правдиво, то отобразится «хорошо», а ложно – «удовлетворительно».

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

Простое сложение в Excel

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

Мнение эксперта

Витальева Анжела, консультант по работе с офисными программами

Со всеми вопросами обращайтесь ко мне!

Задать вопрос эксперту

Функция ИНДЕКС также может извлекать значение из разных выделенных областей, но в данной статье я не буду останавливаться на этой возможности. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!

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

Связанные таблицы в Excel: подробная инструкция

Диапазон – обязательный аргумент, представляющий собой массив, в котором происходит проверка заданного условия;
Критерий – еще один обязательный аргумент, которое является условием для отбора значений в ячейках. При равенстве определенному числу, необходимо ввести его без кавычек, в других случаях необходимы кавычки: например, если значение больше числа 5, то его нужно прописать, как «>5» . Также работают текстовые значения: если нужно суммировать выручку продавца Иванова в таблице, то прописывается условие «Иванов» ;
Диапазон суммирования – массив значений, которые нужно сложить.

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

Excel Если Ячейки Совпадают то Вставить Значение из Другой Ячейки

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

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

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

Массив – ссылка на диапазон ячеек, в котором нам нужно осуществить поиск;

Номер_строки – строка, из которой нужно извлечь значения;

Номер_столбца – столбец, из которого нужно извлечь значение.

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

Для автоматического поиска строки/столбца функцию ИНДЕКС, как правило, дополняют двумя функциями ПОИСКПОЗ (MATCH).

=ИНДЕКС($C$7:$F$9;ПОИСКПОЗ($I$5;$B$7:$B$9;0);ПОИСКПОЗ($I$6;$C$6:$F$6;0))

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

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

Для решения нашей задачи формула будет выглядеть так:

Простыми словами синтаксис функции можно представить так:

=СУММПРОИЗВ((условие_1)*(условие_2)*(что_суммировать))

Сумма умножений 0, 1 и значений таблицы извлечёт нам искомое значение.

Третий способ, который лично мне очень нравится своей простотой. Единственное условие для его работы – названия в столбцах и строках должны быть без пробелов (их можно удалить или заменить на нижнее подчеркивание «_»).

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

Чтобы найти значение ячейки на пересечении определенных сроки и столбца нам понадобится сразу две функции ДВССЫЛ.

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

Выделяем столбцы с заголовками и нажимаем комбинацию клавиш Ctrl+Shift+F3

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

Мнение эксперта

Витальева Анжела, консультант по работе с офисными программами

Со всеми вопросами обращайтесь ко мне!

Задать вопрос эксперту

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

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

Excel – просто! Ищем значение на пересечении

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

Источник

Excel: «Если» (формула). В Excel функция «Если»

Смотрите также уровнем дохода ниже строк разделены точками классическое приложение Excel, строки выше в из ячейки A2 A и строки и всей формулы.(один пробел)

Ключевые возможности

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

Примеры использования

с запятой ( нажмите кнопку том же столбце в ячейку B3 10Чтобы упростить создание и, (запятая)Отрицание действия с данными, как отдельно, такЕсть ещё функцияЕслинужно нажать ИСТИНА или ЛОЖЬ. Рассмотрим пример для функции табличного редактора являются многодетными или;Открыть в Excel

Равенство параметров двух ячеек

R[2]C[2] она изменяется сA10 редактирование формул иОператоры ссылок3–1 на листе. Формула и в сочетании в Excel «СУММЕСЛИ».в ячейке стоит текстCTRL+SHIFT+ENTER=ЕСЛИ(A1>=100;»Бюджет превышен»;»ОК!»)

Примеры с применением условий «ИЛИ», «И»

случая, когда ситуация Excel «Если». Она отсутствует основной кормилец.). Например, чтобы представитьдля работы сОтносительная ссылка на ячейку, =A$1 на =B$1.Диапазон ячеек: столбец А, свести к минимуму––1 всегда начинается со с функцией Про эту функцию Excel)Т.е. если в является критической. Введем имеет отношение к Если число детей значения 10, 20, массивами. расположенную на двеСкопированная формула со строки 10-20. количество опечаток иЗнак «минус»* (звездочка) знака равенства (=),ЕСЛИ смотрите в статье, то формулу напишемТеперь подсчитаем количество вхождений ячейке соответствующий комментарий. В логическим возможностям приложения. свыше 5, размер 30, 40 иВ примере формулы массива строки ниже и смешанной ссылкойA10:A20 синтаксических ошибок, пользуйтесь%Умножение за которым могут. «Как посчитать в такую. чисел больше 10A1 результате формула будет Ее можно отнести субсидии – 50%. 50, 60, 70, ниже вычисляется итоговое на два столбцаУдобный способ для ссылкиДиапазон ячеек: строка 15, автозавершением формул. ПослеПроцент3*3 следовать числа, математическиеФормула Excel ячейки вНам нужно, чтобы в диапазоне ячеексодержится значение большее иметь следующий вид: к наиболее востребованным Определить, кому полагаются 80, находящиеся в значение цен на правее на несколько листов столбцы B-E ввода знака равенства^/ (косая черта) операторы (например, знакОписание определенных строках». в ячейке BA6:A11 или равное 100, =ЕСЛИ(ИЛИ(A1>=6; B1>10000); «критическая функциям во время субсидии, а кому расположенных друг под акции; строки ячеекR2C2 Трехмерные ссылки используются дляB15:E15 (=) и начальныхВозведение в степеньДеление

Задачи высокого уровня сложности

«плюс» или «минус»)=И(A2>1;A2Можно в Excel 4 было написано=СЧЁТЕСЛИ(A6:A11;»>10″). Аналогичный результат то формула вернет ситуация»; «»). В работы. – нет. другом ячейках, можно не используются приАбсолютная ссылка на ячейку, анализа данных изВсе ячейки в строке букв или триггера* и /3/3 и функции, которыеВозвращает значение ИСТИНА, если суммировать данные из «100», если в (2) можно получить ОК!, а если таком случае еслиВ Excel формула «Если»Вид исходной таблицы: создать константу массива вычислении и отображении расположенную во второй одной и той 5 показа в Excel

Скидка

Умножение и деление% (знак процента) значительно расширяют возможности число в ячейке таблицы по многим ячейке A 4 с помощью формулы массива нет, то Бюджет программа обнаружит совпадения помогает выполнять разногоДля проверки критериев согласно с размерностью 2 отдельных значений для строке второго столбца же ячейки или5:5 Online под ячейкой+ и -Процент формулы. A2 больше 1 разным условиям. Например, будет стоять слово=СЧЁТ(ЕСЛИ(A6:A11>10;A6:A11)) превышен. как минимум по рода задачи, когда условию задачи запишем на 4: {10,20,30,40;50,60,70,80}. каждой акции.R[-1] диапазона ячеек наВсе ячейки в строках появляется динамический раскрывающийсяСложение и вычитание20%Ниже приведен пример формулы,И в функции «ЕСЛИ» «Да». В ячейкеТеперь, когда принцип понятен,В качестве аргументов функции, одному из параметров требуется сравнить определенные формулу:

Логические функции предназначены дляФормула массива, вычисляющаяОтносительная ссылка на строку, нескольких листах одной с 5 по список доступных функций,&^ (крышка) умножающей 2 наменьше 100. В можно написать одно

В4 написали такую

fb.ru

Функция ЕСЛИ() в MS EXCEL

с помощью функции могут участвовать формулы, (срок, сумма задолженности), данные и получить5;50%;30%);»нет»)’ class=’formula’> проверки одного либо одно значение расположенную выше текущей книги. Трехмерная ссылка 10

аргументов и имен,Объединение двух текстовых строк

Синтаксис функции

Возведение в степень

 3 и прибавляющей противном случае возвращает условие поиска данных формулу.
ЕСЛИ() можно конструировать
например: пользователь увидит соответствующее результат. Данное решениеОписание аргументов: нескольких условий, иПри вводе формулы «={СУММ(B2:D2*B3:D3)}» ячейки содержит ссылку на5:10

которые соответствуют этим в одну3^2
к результату 5,
значение ЛОЖЬ. для суммирования. В=ЕСЛИ(A4=»Да»;100;»») и другие формулы=ЕСЛИ(A1>100;СУММ(B1:B10);СУММ(C1:C10)) примечание. В первой дает возможность применятьИ(B3 выполняют действия, предписанные в качестве формулыR

Вложенные ЕСЛИ

ячейку или диапазон,Все ячейки в столбце буквам или триггеру.=Операторы сравнения используются для чтобы получить 11.=ЕСЛИ(И(A2 функции «СУММЕСЛИМН» можно
Обратите внимание! с условиями. Например,

Т.е. если в ситуации сообщение «проблемный ветвящиеся алгоритмы, а

Вложенная функция ЕСЛИ выполняет для каждого из

массива сначала вычисляется

Абсолютная ссылка на текущую перед которой указываются H После этого элемент

<> сравнения двух значений.=2*3+5Возвращает значение ячейки A2, написать в однойВ третьем условии нахождение минимального значения ячейке

клиент» выдавалось лишь

также создавать дерево проверку на количество двух возможных результатов. значение «Акции» и строку

имена листов. В

Опущен третий аргумент [значение_если_ложь]

H:H из раскрывающегося списка>= Результатом сравнения являетсяСледующая формула использует функцию если оно меньше
формуле до 127
в формуле мы среди чисел большеA1 тогда, когда были решений.

Вместо ИСТИНА или ЛОЖЬ в первом аргументе введено число

детей в семье, Таковыми результатами могут
«Цена» для каждой
При записи макроса в Excel Online используютсяВсе ячейки в столбцах можно вставить в
<>
логическое значение: ИСТИНА либо

ПЛТ значения ячейки A3 условий. Подробнее об поставили не «нуль», 10:содержится значение >100, выполнены оба заданныхФункция выглядит таким образом: которой полагаются субсидии.

Связь функции ЕСЛИ() с другими функциями использующие условия

являться только логические биржи, а затем Excel Online для все листы, указанные с H по формулу.Сравнение ЛОЖЬ.для вычисления платежаИ этой функции, ее а две двойные

=МИН(ЕСЛИ(A6:A11>10;A6:A11)) то суммирование ведется условия. =ЕСЛИ (задача; истина;Если основное условие вернуло ИСТИНА или ЛОЖЬ. — сумма всех

некоторых команд используется между начальным и JВ некоторых случаях может

Чтобы изменить порядок вычисленияОператор сравнения по ипотеке (1не превышает 100. применении и различных кавычки – этоЧасто применяемая,
по столбцу
Функция табличного редактора Excel ложь). Первая составная результат ЛОЖЬ, главнаяВ Excel содержится несколько результатов. стиль ссылок R1C1.

конечным именами вH:J потребоваться использовать функцию формулы, заключите ееЗначение 073,64 долларов США) В противном случае
вариантах применения, читайте

значит «Пусто» (оставитьлогическая функция в ExcelB «Если» используется, чтобы часть — логическое функция ЕСЛИ вернет логических функций, такихВычисление нескольких значений
Например, если записывается

excel2.ru

Функция «ЕСЛИ» в Excel.

ссылке. Например, формулаДиапазон ячеек: столбцы А-E, в качестве одного часть, которая должнаПример с 5% ставкой возвращает сообщение «Значение в статье «Суммирование ячейку пустой). Скопировали — функция «ЕСЛИ», а если меньше, обойти встроенные ошибки выражение. Оно может текстовую строку «нет». как ЕСЛИ, ЕСЛИОШИБКА,   . Некоторые функции возвращают массивы команда щелчка элемента =СУММ(Лист2:Лист13!B5) суммирует все строки 10-20 из аргументов другой быть выполнена первой,= (знак равенства) (5% разделить на вне допустимого диапазона.». ячеек в Excel
 формулу в ячейкусчитает данные ячеек то по столбцу при делении на быть фразой илиВыполним расчет для первой СУММЕСЛИ, И, ИЛИ
 значений или требуют
Автосумма значения, содержащиеся вA10:E20 функции. Например, в в скобки. Например,Равно 12 месяцев равняется=ЕСЛИ(И(1 по условию».
В5. по условию, которое
С ноль, а также числом. К примеру, семьи и растянем и другие. Две
массив значений вдля добавления формулы, ячейке B5 наСсылка на другой лист приведенной ниже формуле результатом приведенной нижеA1=B1 ежемесячному проценту) наВозвращает значение ячейки A3,Очень много разныхТретий пример. мы напишем в
. еще в нескольких «10» или «без формулу на остальные последние на практике, качестве аргумента. Для суммирующей диапазон ячеек, всех листах в   . В приведенном ниже примере для сравнения результата формулы будет число> (знак «больше») период в 30 если оно больше применений этих функций.В следующей формуле формуле или пишет
В EXCEL 2007 в случаях. Первая ситуация НДС» — это ячейки, используя функцию как правило, самостоятельно вычисления нескольких значений в Excel Online диапазоне от листа 2 функция со значением 50 11, так какБольше лет (360 месяцев) 1Еще одна логическая
поставили в третьем нужные слова в качестве значений аргументов
обозначается редактором, как логические выражения. Данный автозаполнения. Полученные результаты:
не используют, поскольку с помощью формулы при записи формулы до листа 13 включительно.СРЗНАЧ
 используется вложенная функция
в Excel OnlineA1>B1 с займом наИ функция в Excel
условии знак «Тире». нужной ячейке. Например,значение_если_истиназначение_если_ложь «ДЕЛ/0» и встречается параметр обязательно необходимоФункция ЕСЛИ имеет следующую результатом их вычислений массива необходимо ввести будет использован стиль
При помощи трехмерных ссылок
используется для расчета
СРЗНАЧ умножение выполняется раньше< (знак «меньше») сумму 200 000не превышает 100. – это функция =ЕСЛИ(A5=»Да»;100;»-«) такие условия -можно для построения
 достаточно часто. Как
заполнить. Истина — синтаксическую запись: может являться один массив в диапазон
ссылок R1C1, а можно создавать ссылки среднего значения диапазона. сложения. В этойМеньше
 долларов:
В противном случае в Excel «ЕСЛИОШИБКА».В ячейку В6 если в ячейке более сложных проверок правило, она возникает
это значение, которое=ЕСЛИ(лог_выражение;[значение_если_истина];[значение_если_ложь]) из только двух ячеек, состоящий из не A1. на ячейки на B1:B10 на листе1. Функции СРЗНАЧ и формуле число 2A1=ПЛТ(0,05/12;360;200000) возвращает сообщение «Значение Этой функцией мы написали такую формулу.
 А стоит то-то,
использовать до 64 в тех случаях, отобразится как результат,Как видно, по умолчанию возможных вариантов (ИСТИНА, того же числаДля обозначения ячеек, диапазонов других листах, определять «Маркетинг» той же
СУММ вложены в умножается на 3,>= (знак «больше илиНиже приведены примеры формул, вне допустимого диапазона». говорим Excel, что, =ЕСЛИ(A6=»%»;1;»нет») Здесь в а в ячейке
 вложенных друг в когда подлежит копированию если выражение будет можно выполнить проверку
ЛОЖЬ). При совместном строк или столбцов, ячеек, формул, констант имена и создавать
книги.
функцию ЕСЛИ. а затем к равно») которые можно использовать Сообщения можно заменить если будет ошибка,
третьем условии написали В стоит то-то, друга функций ЕСЛИ(). формула «A/B», при
верным. Ложь —
только одного условия,
использовании с функцией что и аргументы и таблиц Excel формулы с использованиемСсылка на диапазонДопустимые типы вычисляемых значений результату прибавляется числоБольше или равно на листах. любыми другими.
не пиши ее, слово «нет» в то в ячейке=ЕСЛИ(A1>=100;»Бюджет превышен»;ЕСЛИ(A1>=90;»Крупный проект»;ЕСЛИ(A1>=50;»Средний этом показатель B данные, которые будут например, больше ли ЕСЛИ, они способны массива. Online можно создавать следующих функций: СУММ, ячеек на другом    Вложенная функция, используемая в 5.A1>=B1=A1+A2+A3Вычисление премии а оставь ячейку кавычках. Получилось так.
С напиши такое проект»;»Малый проект «))) в отдельных ячейках выданы, когда задача e3 числа 20? значительно расширить ееНапример, по заданному ряду
определенные имена. Имя — СРЗНАЧ, СРЗНАЧА, СЧЁТ, листе той же качестве аргумента, должна=5+2*3Меньше или равно    Вычисляет сумму значений вНиже приводится решение довольно пустой. Например, еслиЧетвертый пример. слово, посчитай суммуВышеуказанную формулу можно усложнять равен нулю. Избежать не будет верной. С использованием функции функционал. из трех значений это значимое краткое
СЧЁТЗ, МАКС, МАКСА, книги
возвращать соответствующий емуЕсли же с помощьюA1 ячейках A1, A2 распространенной задачи: с ячейки, указанные вВ ячейку С1 таких-то ячеек, т.д. и дальше, но этого можно посредствомДля понимания возможностей функции ЕСЛИ такую проверкуПример 1. При расчете продаж (в столбце обозначение, поясняющее предназначение МИН, МИНА, ПРОИЗВЕД,1. Ссылка на лист

excel-office.ru

Функция И

 тип данных. Например, скобок изменить синтаксис,<> (знак «не равно») и A3. помощью функций формуле, пустые, то напишем формулу, где

Пример

Технические подробности

Все это пишем есть другие подходы: возможностей рассматриваемого нами «Если» в Excel, можно выполнить так: стоимости количества потребленных B) для трех ссылки на ячейку, СТАНДОТКЛОН.Г, СТАНДОТКЛОН.В, СТАНДОТКЛОНА, «Маркетинг». если аргумент должен

Excel Online сложитНе равно=КОРЕНЬ(A1)ЕСЛИ Excel выдает ошибку. условия будут такие: в формуле. Функция=ПРОСМОТР(A1;{0;50;90;100};{«Малый проект»;»Средний проект»;»Крупный проект»;»Бюджет оператора. Итак, необходимая примеры просто необходимы,=ЕСЛИ(EXP(3)>20;»больше»;»меньше») кВт электроэнергии для месяцев (в столбце константы, формулы или СТАНДОТКЛОНПА, ДИСПР, ДИСП.В,2. Ссылка на диапазон быть логическим, т. е. 5 и 2,A1<>B1 Использует функциюи О функции «ЕСЛИОШИБКА» больше или равно

 в Excel «ЕСЛИ»

 превышен»}) формула будет иметь

и далее мыВ результате будет возвращена абонентов учитывают следующие

A) функция	таблицы, так как
ДИСПА и ДИСППА.	ячеек с B1 иметь значение ИСТИНА а затем умножитАмперсанд (
КОРЕНЬ	И читайте в статье (можно поставить меньше пригодится в таблицах,=ВПР(A1;A3:B6;2) следующий вид: =ЕСЛИ(B1=0;

 перейдем к их

текстовая строка «больше». условия:ТЕНДЕНЦИЯ понять их сутьТрехмерные ссылки нельзя использовать по B10 включительно. либо ЛОЖЬ, вложенная
результат на 3;&для возврата значенияопределяется, заработал ли «Функция «ЕСЛИОШИБКА» в
или равно). чтобы посчитать определенныеДля функции ВПР() необходимо 0; A1/B1). Отсюда рассмотрению. Вводим в

Примеры

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

) используется для объединения	квадратного корня числа
продавец премию, а	Excel».=ЕСЛИ(A1>=2;»Да»;»») Мы в данные, в анкетах, создать в диапазоне следует, что если клетку C1 показатель узнать, принадлежит ли
менее 3 человек	ряда объемов продаж. бывает непросто. НижеТрехмерные ссылки нельзя использовать отделенная от ссылки возвращать логическое значение будет число 21. (соединения) одной или в ячейке A1.
также рассчитывается ее	И формуле написали, что, в тестах, т.д.A3:B6 клетка B1 будет 8. Далее в какое-либо значение указанному или за месяц Чтобы можно было приведены примеры имен

 вместе с оператор

на диапазон значений. (ИСТИНА или ЛОЖЬ).=(5+2)*3 нескольких текстовых строк=СЕГОДНЯ() размер. — одна из логических если в ячейкеФункция Excel «ЕСЛИ»таблицу значений:

 заполнена параметром «ноль», поле по адресу интервалу, потребуется сравнить было потреблено менее отобразить все результаты и показано, как пересечения (один пробел),Относительные ссылки В противном случаеВ примере ниже скобки, в одну. Возвращает текущую дату.=ЕСЛИ(И(B14>=$B$7,C14>=$B$5),B14*$B$8,0) функций, с помощью

См. также:

А1 будет стоять
находится на закладкеЕсли требуется вывести разный
редактор выдаст «0»,
D1 вписываем следующую
это значение с
100 кВт электроэнергии, формулы, она вводится
их использование упрощает а также в
. Относительная ссылка в формуле, Excel Online выдаст
в которые заключенаТекстовый оператор
=ПРОПИСН(«привет») —
которой можно определить,
число равное или «Формулы» в разделе
текст в случае в обратном случае

support.office.com

Обзор формул

 формулу: =ЕСЛИ(C1, =, верхней и нижней ставка за 1 в три ячейки понимание формул. формулах с неявное например A1, основана ошибку «#ЗНАЧ!». первая часть формулы,Значение Преобразует текст «привет»ЕСЛИ все ли проверяемые больше «2», то «Библиотека функций» - наличия в ячейке Excel поделит показатель >, =, границей интервалов соответственно. кВт составляет 4,35 столбца C (C1:C3).Тип примера

пересечение. на относительной позицииПредельное количество уровней вложенности задают для ExcelПример в «ПРИВЕТ» собщие продажи больше условия принимают значение написать в ячейке «Логические». Встаем наА1 A1 на данныеПродолжаем практиковаться и осваивать Например, находится ли рубля.Формула массива, вычисляющаяПример использования диапазонов вместоЧто происходит при перемещении,

ячейки, содержащей формулу, функций Online такой порядок& (амперсанд) помощью функции или равны (>=) ИСТИНА. С1 слово «Да». ячейку В1, вызываемотрицательного значения, положительного B1 и отобразит логические возможности приложения. результат вычисления e3В остальных случаях ставка несколько значений имен

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

ПРОПИСН

целевым продажамФункция Если число будет функцию «ЕСЛИ». значения или 0, результат. Функцию табличного редактора в интервале от за 1кВт составляетФормула «=ТЕНДЕНЦИЯ(B1:B3;A1:A3)», введенная какПример с использованием имен удалении листов которую указывает ссылка. до семи уровней

B4+25, а полученный

знаков в одну.И

И меньше 2, тоПервый пример. то можно записать
На практике часто встречается Excel «Если» можно 20 до 25? 5,25 рубля. формула массива, возвращаетСсылка
 . Нижеследующие примеры поясняют, какие При изменении позиции
 вложенных функций. Если результат делится на последовательность=ЕСЛИ(A1>0)число договоров большевозвращает значение ИСТИНА,
 ячейка должна оставатьсяНам нужно выбрать следующую формулу: и ситуация, которая соединить с операторами

Элементы формулы

При использовании однойРассчитать сумму к оплате три значения (22=СУММ(A16:A20) изменения происходят в

ячейки, содержащей формулу,

функция Б является сумму значений вВыражение «Северный»&«ветер» дает результат

Анализирует ячейку A1 и или равно (>=)

если в результате пустой. Скопировали формулу из столбца А=ПРОСМОТР(A1;{-1E+307;0;1E-307};{«<>0»})

будет рассмотрена далее. сравнения. К ним лишь функции ЕСЛИ за месяц для 196, 17 079=СУММ(Продажи)

Использование констант в формулах

трехмерных ссылках при изменяется и ссылка. аргументом функции А, ячейках D5, E5 «Северный ветер». проверяет, превышает ли целевому, общие продажи вычисления всех аргументов по столбцу С. числа «2» иили, если требуется вместо Речь идет о относятся параметры: «ИЛИ», придется ввести следующую нескольких абонентов. и 11 962),Константа перемещении, копировании, вставке

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

При копировании или функция Б находится и F5.Для определения ссылок на значение в ней умножаются на процент получается значение ИСТИНА, Получилось так.

Типы операторов

написать в столбце текстовых значений вывести расчете скидки, исходя «И». Укажем необходимое запись:

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

Вид исходной таблицы данных: вычисленные по трем=ПРОИЗВЕД(A12,9.5%) и удалении листов, заполнении формулы вдоль на втором уровне=(B4+25)/СУММ(D5:F5) диапазоны ячеек можно

нуль.	премии. В противном	и значение ЛОЖЬ,
Пятый пример.	В то значение,	формулы, можно использовать
из общей суммы	условие в Excel: =ЕСЛИ(EXP(3)>20;ЕСЛИ(EXP(3)	Выполним расчет по формуле: объемам продаж за
=ПРОИЗВЕД(Цена,НСП)	на которые такие	строк и вдоль
вложенности. Например, в	Функции — это заранее	использовать операторы, указанные
Формула также может содержать	случае возвращается значение	если вычисление хотя
Можно функцией «ЕСЛИ»	которое мы укажем	ссылки на диапазон

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

средств, потраченных на если оценка учащегосяИмеем вложенную функцию ЕСЛИОписание аргументов: три месяца.

Формула	ссылки указывают. В	столбцов ссылка автоматически
приведенном выше примере	определенные формулы, которые	ниже.
один или несколько	0.	бы одного из
посчитать итог теста,	в формуле. Можно	ячеек (содержащих формулы)
приобретение определенного товара. равна или меньше	в качестве одного	ИЛИ(B3
	В обычную формулу можно	=ТЕКСТ(ВПР(MAX(A16,A20),A16:B20,2,FALSE),»дд.мм.гггг»)
примерах используется формула	корректируется. По умолчанию	функции

Текстовый оператор конкатенации

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

выбрать любые числа,	=ПРОСМОТР(A24;{-1E+307;0;1E-307};A27:A29) (см. файл примера)	Используемая в этом
5, но больше	из возможных результатовC3*4,35 – сумма к ввести ссылку на	=ТЕКСТ(ВПР(МАКС(Продажи),ИнформацияОПродажах,2,ЛОЖЬ),»дд.мм.гггг») =СУММ(Лист2:Лист6!A2:A5) для суммирования

Операторы ссылок

в новых формулахСРЗНАЧ заданным величинам, называемымЗначение

функции, ссылки, операторы	Использование вложенных функций	ЛОЖЬ.
ставят не слова,	слова, т.д.Третий аргумент функции не случае матрица может 3. Должен быть выполнения основной функции оплате, если функция	ячейку со значением
Таблица	значений в ячейках используются относительные ссылки.и	аргументами, и в
Пример	и константы. в формулеОбычно функция а знаками. Читайте	Появившееся диалоговое окно

Порядок выполнения действий в формулах в Excel Online

обязателен, если его иметь следующий вид: отображен комментарий: «проходит». ЕСЛИ, в связи ИЛИ вернет значение или на самоA22:B25 с A2 по Например, при копировании

Порядок вычислений

СУММ указанном порядке. Эти: (двоеточие)Элементы формулыФункция ЕСЛИИ статью «Как объединить заполнили так. опустить, то функция менее 1000 — В противном случае с чем синтаксис ИСТИНА; значение, также называемое=ПРОИЗВЕД(Price,Table1[@Tax Rate]) A5 на листах или заполнении относительнойявляются функциями второго функции позволяют выполнять

Приоритет операторов

Оператор диапазона, который образует1. Функции. Функция ПИ()Функция ИЛИиспользуется для расширения данные теста вВ формуле «ЕСЛИ» нужно вернет значение ЛОЖЬ 0%; 1001-3000 — – «нет». Итак, выглядит несколько громоздким.C3*5,25 – сумма к константой. Подобным образом

Существует несколько типов имен,	со второго по
ссылки из ячейки уровня, поскольку обе как простые, так	одну ссылку на
возвращает значение числа	Функция НЕ
возможностей других функций,	ячейках в Excel»
написать три условия.	(если условие не
3%; 3001-5000 —	проходят лишь те
Если потребуется также	оплате, если ИЛИ
в формулу массива	которые можно создавать шестой.
B2 в ячейку они являются аргументами и сложные вычисления. все ячейки, находящиеся	Пи: 3,142…

Использование круглых скобок

Полные сведения о выполняющих логическую проверку. тут. В формуле эти выполнено). 5%; более 5001 учащиеся, которые получили узнать, например, равен вернет ЛОЖЬ. можно ввести ссылку и использовать.Вставка или копирование B3 она автоматически функцииПриведенный ниже пример функции между первой и

2. Ссылки. A2 возвращает

формулах в Excel Например, функцияМожно в Excel условия напишутся через=ЕСЛИ(A1>100;»Бюджет превышен») — 7%. Рассмотрим пятерки и четверки. ли корень квадратный

Растянем формулу для остальных

на массив либоОпределенное имя Если вставить листы между изменяется с =A1ЕСЛИОКРУГЛ последней ячейками диапазона, значение ячейки A2.Рекомендации, позволяющие избежатьЕСЛИ в функцию «ЕСЛИ»

точку с запятой.

Использование функций и вложенных функций в формулах

Если в ячейке ситуацию, когда в Записать подобную задачу e3 числовому значению ячеек с помощью массив значений, содержащихся Имя, используемое для представления листами 2 и 6, на =A2.

Синтаксис функций

. Функция, вложенная в, округляющей число в включая эти ячейки.3. Константы. Числа или появления неработающих формул

выполняет логическую проверку

вписать несколько условийПервое условие –A1 Excel присутствует база в табличном редакторе из диапазона дробных функции автозаполнения. Результат в ячейках (его

ячейки, диапазона ячеек, Excel Online прибавитСкопированная формула с качестве аргумента в ячейке A10, демонстрирует

B5:B15 текстовые значения, введенныеПоиск ошибок в и возвращает одно логического выражения. Смотрите «Лог_выражение». Мы написалисодержится значение 1, данных посетителей, а можно используя специальную чисел от 4 расчета для каждого иногда называют константой формулы или константы. к сумме содержимое

относительной ссылкой функцию синтаксис функции.; (точка с запятой) непосредственно в формулу, формулах значение, если при такую формулу в А1=2 – это то вышеуказанная формула также информация о

Ввод функций

формулу. Она будет до 5, итоговая абонента: массива). Формулы массива Вы можете создавать ячеек с A2Абсолютные ссылкиСРЗНАЧСтруктура функцииОператор объединения. Объединяет несколько например 2.Сочетания клавиш в проверке получается значение статье «Функция «ЕСЛИ» значит, если в

вернет значение ЛОЖЬ. сумме потраченных ими иметь вид: =ЕСЛИ(И(A13); формула будет выглядетьИспользуя в формуле функцию принимают константы так собственные определенные имена. по A5 на . Абсолютная ссылка на ячейку, будет функцией третьего1. Структура. Структура функции ссылок в одну4. Операторы. Оператор ^ Excel 2016 для Windows ИСТИНА, и другое в Excel с ячейке А1 стоитТ.к. значение ЛОЖЬ эквивалентно на покупки средств. «проходит», «нет»). К

Вложенные функции

громоздкой и неудобочитаемой. И в первом же, как и Иногда (например, при добавленных листах. в формуле, например уровня, и т. начинается со знака ссылку. («крышка») применяется дляСочетания клавиш в

значение, если при несколькими условиями сравнения». цифра 2. Мы

 0, то формулы Теперь необходимо рассчитать более сложному примеруГораздо проще использовать в аргументе в функции другие формулы, однако настройке области печати)Удаление $A$1, всегда ссылается д. равенства (=), заСУММ(B5:B15,D5:D15) возведения числа в Excel 2016 для Mac проверке получается значение

Функция «ЕСЛИ» в Excel написали условие, по=ЕСЛИ(0;»Бюджет превышен»;»ОК!») скидку для каждого можно отнести использованием качестве условия сложное ЕСЛИ, мы проверяем константы массива необходимо Excel Online самостоятельно Если удалить листы между на ячейку, расположеннуюСсылка указывает на ячейку которым следует имя(пробел) степень, а операторЛогические функции (ссылка) ЛОЖЬ. Использование функции – примеры которому нужно искатьили (если в клиента. С этой «ИЛИ» либо «И». выражение, которое может соответствие значений сразу вводить в определенном создает определенное имя. листами 2 и 6,

Использование ссылок в формулах

в определенном месте. или диапазон ячеек функции, открывающую круглуюОператор пересечения множеств, используется * («звездочка») — дляФункции Excel (поИсмотрите в следующих ячейки в столбце ячейке целью используем следующее Итак посмотрим, как быть записано с по двум условиям. формате.Имя таблицы Excel Online не При изменении позиции на листе и скобку, аргументы функции, для ссылки на умножения. алфавиту)в качестве аргумента

Стиль ссылок A1

 статьях. А (ячейки, вA1 выражение: =ЕСЛИ(A1>=5001; B1*0,93; применять формулу в использованием функций ИКонстанты массива могут содержать Имя таблицы Excel Online, будет использовать их ячейки, содержащей формулу, сообщает Excel Online, разделенные запятыми и общие ячейки двухКонстанта представляет собой готовоеФункции Excel (полог_выражениеВ формулу можно которых стоит цифрасодержится значение 0) ЕСЛИ(А1>=3001; B1*0,95;..). Система Excel, если несколько

и ИЛИ. Например,	Пример 2. Абитуриенты, поступающие
числа, текст, логические которая представляет собой значения в вычислениях.	абсолютная ссылка не
где находятся значения закрывающую скобку.	диапазонов.
(не вычисляемое) значение, категориям)	функции
поставить несколько функций «2»).	=ЕСЛИ(A1;»Бюджет превышен»;»ОК!»)
проверяет общую сумму условий в задаче. приведенная выше функция	в университет на
значения, например ИСТИНА набор данных по	Перемещение
изменяется. При копировании или данные, которые2. Имя функции. Чтобы	B7:D7 C6:C8
которое всегда остаетсяПримечание:	ЕСЛИ

 «ЕСЛИ» в Excel.Второе условие –вернут ОК! покупок. Когда она Пример такого выражения: может быть переписана специальность «инженер-механик», обязаны или ЛОЖЬ, либо определенной теме. Эти

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

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

Мы стараемся какпозволяет проверять несколько Наприме, такая формула.

«Значение_если_истина» — здесьЕсли в ячейке превышает показатель в

Различия между абсолютными, относительными и смешанными ссылками

 =ЕСЛИ(ИЛИ(A1=5; A1=10); 100; следующим образом: сдать 3 экзамена значения ошибок, такие данные хранятся в листом 2 и листом 6, по строкам и формуле. С помощью функций, щелкните любую вычисления может повлиять 09.10.2008, число 210 можно оперативнее обеспечивать различных условий вместо=ЕСЛИ(C4=10;5;ЕСЛИ(C4>=5;3;ЕСЛИ(C4>=0;0))) мы напишем дляA1 5001 рублей, происходит 0). Из этого=ЕСЛИ(И(EXP(3)>20;EXP(3) по предметам математика, как «#Н/Д». В записях (строках) и переместить таким образом, столбцам абсолютная ссылка

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

 на возвращаемое формулой и текст «Прибыль вас актуальными справочными одного.Эта формула взята Excel, что нужнонаходится любое другое умножение на 93 следует, что еслиРезультат выполнения выражения И(EXP(3)>20;EXP(3) физика и русский одной константе массива полях (столбцах) таблицы. чтобы они оказались не корректируется. По формуле можно использовать клавиши SHIFT+F3. значение, поэтому для за квартал» являются материалами на вашемСинтаксис из примера составления написать в ячейке число кроме 0, процента стоимости товара. показатель в клетке

Иногда требуется узнать, оказалось язык. Максимальный балл

 могут присутствовать значения Каждый раз при перед листом 2 или умолчанию в новых данные, которые находятся3. Аргументы. Существуют различные получения нужных результатов константами. выражение или языке. Эта страницаИ теста, анкеты в В1, если в то формула вернет В случае преодоления А1 равен 5 ли хотя бы за каждый экзамен различных типов, например {1,3,4;ИСТИНА,ЛОЖЬ,ИСТИНА}. добавлении таблицы Excel за листом 6, Excel формулах используются относительные в разных частях типы аргументов: числа, важно понимать стандартный его значение константами переведена автоматически, поэтому(логическое_значение1;[логическое_значение2];…) Excel. Смотрите статью ячейке А1 будет

Бюджет превышен. Такой отметки в 3001

Стиль трехмерных ссылок

 либо 10, программа одно предположение верным. – 100. Средний Числа в константах Online по умолчанию Online вычтет из ссылки, а для листа, а также текст, логические значения порядок вычислений и не являются. Если ее текст можетФункция «Как сделать тест стоять цифра «2». подход удобен, когда единицу, имеет место отобразит результат 100, В этом случае проходной балл за массива могут быть Excel Online присваивает суммы содержимое ячеек использования абсолютных ссылок

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

 грамматические ошибки. Дляимеет следующие аргументы:Можно функцию «ЕСЛИ» если в ячейке нулю. уже с учетом — 0. Можно ИЛИ, которая выполняет 75, при этом иметь экспоненциальный формат. «Таблица2» и т. д.Перемещение конечного листа параметр. Например, при Вы также можете (например #Н/Д) илиФормулы вычисление значений в не ссылки на

 нас важно, чтобыАргумент в Excel вставлять А1 будет стоятьEXCEL содержит также другие 95%. Приведенную формулу использовать эти операторы проверку одного или
 минимальная оценка по Текст должен быть Эти имена можно Если переместить лист 2 или копировании или заполнении задавать ссылки на
 ссылки на ячейки. определенном порядке. Формула другие ячейки (например, эта статья былаОписание в другие формулы цифра «2», то функции, которые можно с легкостью можно и чтобы решить
 нескольких логических выражений физике должна составить заключен в двойные изменить, сделав их 6 в другое абсолютной ссылки из ячейки разных листов
 Используемый аргумент должен всегда начинается со имеет вид =30+70+110), вам полезна. ПросимЛогическое_значение1 с другими функциями.

Стиль ссылок R1C1

в ячейке В1 применять для анализа применять на практике. более сложные задачи. и возвращает логическое не менее 70 кавычки, например «Вторник». более понятными. место книги, Excel ячейки B2 в одной книги либо возвращать значение, допустимое знака равенства (=). значение в такой вас уделить паруОбязательный аргумент. Первое проверяемое

=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B11;НАИМЕНЬШИЙ(ЕСЛИ($F$2=A2:A11;СТРОКА(B2:B11)-1;»»);	нужно написать цифру
данных с использованием	Объем продаж и К примеру, в ИСТИНА, если результат баллов, а по
Константы массива не могут	Создание имени с помощью Online скорректирует сумму ячейку B3 она на ячейки из для данного аргумента.
Excel Online интерпретирует	ячейке изменяется только секунд и сообщить, условие, вычисление которого
СТРОКА()-5));»»)	«6». Можно написать условий. Например, для показатели скидок задаются
базе необходимо вычислить	вычислений хотя бы математике – 80.

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

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

должников, которым необходимо одного из них Определить абитуриентов, которые ячейку, столбцы или. Можно удобно создавать диапазона листов. обеих ячейках: =$A$1. на ячейки других также используются константы, за знаком равенстваОператоры определяют операции, которые вам, с помощью или ЛОЖЬ. примера взята из этом условии, т.д. чисел в диапазонеПрименение описанных возможностей возможно

заплатить более 10000	является логическим ИСТИНА. успешно сдали экзамены.	строки разной длины,
имена из существующих	Удаление конечного листа	Скопированная формула с
книг называются связями	формулы и другие	как формулы. После
необходимо выполнить над	кнопок внизу страницы.	Логическое_значение2;…
статьи «Выбрать сразу	Третье условие –	ячеек используется функция

Типы имен

для решения различного рублей. При этом Например, требуется узнать,

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

Необязательный аргумент. Дополнительные проверяемые много данных из «Значение_если_ложь» — здесь СЧЁТЕСЛИ(), а для рода задач. Основной они не погашали является ли e3Для определения зачисленных студентов знаки: $ (знак столбцов путем выделения 6, Excel OnlineСмешанные ссылкиСтиль ссылок по умолчанию4. Всплывающая подсказка аргумента. вычисляемые элементы, которые выполняются в стандартном приводим ссылку на

Создание и ввод имен

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

 используем формулу: доллара), круглые скобки ячеек на листе. скорректирует сумму с

. Смешанная ссылка содержит либо По умолчанию Excel Online

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

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

составление формулы, чтобы месяцев. Функция табличного числом, которое меньше=80;C4>=70;СРЗНАЧ(B4:D4)>=75);»Зачисл.»;»Не зач.»)’ class=’formula’> или % (знакПримечание: учетом изменения диапазона абсолютный столбец и использует стиль ссылок появляется всплывающая подсказка константы или ссылки правилам арифметики), однако языке) . или ЛОЖЬ. Условий

из таблицы товара в ячейке А1 функция СУММЕСЛИ(). не получить ошибочного редактора Excel «Если» 100? Для проверкиОписание аргументов: процента).

По умолчанию в именах листов.

относительную строку, либо A1, в котором с синтаксисом и на ячейки. Эти его можно изменитьЕсли вы раньше не может быть не и переносим в

 будет написано НЕФункция ЕСЛИ(), как альтернативный результата. Теперь вы позволяет в автоматическом такого условия можноИ(B4>=80;C4>=70;СРЗНАЧ(B4:D4)>=75) – проверяемые логическиеПри форматировании констант массива используются абсолютные ссылкиМожно использовать такой стиль абсолютную строку и столбцы обозначаются буквами аргументами. Например, всплывающая разделяются операторов. Excel с помощью скобок.

работали с Excel более 255. лист заказа выбранные цифра «2», а вариант, может быть знаете, как использовать режиме получить напротив использовать следующую формулу: выражения согласно условию убедитесь, что выполняются на ячейки. ссылок, при котором относительный столбец. Абсолютная (от A до

подсказка появляется после Online вычисляет формула

Приложение Microsoft Excel поддерживает Online, скоро выПримечания покупателем позиции. другая цифра. Мы также использована для оператор в Excel, соответствующих имен пометку

Использование констант массива

=ЕСЛИ(ИЛИ(ОСТАТ(EXP(3);1)<>0;EXP(3) задачи; указанные ниже требования.Имя можно ввести указанными нумеруются и строки, ссылка столбцов приобретает XFD, всего не ввода выражения слева направо, согласно четыре типа операторов: узнаете, что этоАргументы должны давать вЕщё есть функция поставили «0» (нуль). подсчета и сложения если несколько условий «проблемный клиент». Предположим,Запись «<>» означает неравенство,»Зачисл.» – результат, если

Константы заключены в фигурные ниже способами. и столбцы. Стиль вид $A1, $B1 более 16 384 столбцов),=ОКРУГЛ( определенном порядке для арифметические, текстовые, операторы не просто таблица результате логические значения «СЧЁТЕСЛИ» в Excel Это значит, если значений с использованием в задаче. в клетке A1 то есть, больше

функция И вернула скобки (Ввод ссылок R1C1 удобен и т.д. Абсолютная а строки — номерами. Всплывающие подсказки отображаются каждого оператора в сравнения и операторы

для ввода чисел. (такие как ИСТИНА — она считает

в ячейке А1 условий. Ниже приведеныАвтор: Евгений Никифоров  расположены данные, указывающие либо меньше некоторого
значение ИСТИНА (все{ . Введите имя, например, в для вычисления положения ссылка строки приобретает (от 1 до только для встроенных формуле. ссылок. Да, Excel Online или ЛОЖЬ) либо количество ячеек по
стоит НЕ цифра иллюстрирующие примеры.Функция ЕСЛИ(), английский вариант на срок задолженности значения. В данном выражения, представленные в} качестве аргумента формулы. столбцов и строк вид A$1, B$1 1 048 576). Эти буквы функций.Если в одной формулеАрифметические операторы служат для

support.office.com

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

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

).Автозавершение формул в макросах. При и т.д. При и номера называютсяДиалоговое окно используется несколько операторов, выполнения базовых арифметических поиска суммарных значений ссылками, содержащими логические статью «Функция «СЧЁТЕСЛИ должен написать в диапазоне проверке условий. Например, отображает сумму. В возвращают значение ИСТИНА, в результате вычислений

Примеры использования формул с функциями ЕСЛИ, И, ИЛИ в Excel

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

Вставить функцию Excel Online выполняет операций, таких как столбцов или строк, значения. в Excel». Эту ячйке В1 нуль.A6:A11
=ЕСЛИ(A1>100;»Бюджет превышен»;»ОК!»). В этом случае формула и результатом выполнения

вернули значение ИСТИНА);, формул, в котором

положение ячейки в

содержащей формулу, относительная

столбцов. Для ссылки

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

зависимости от значения будет иметь следующий функции ЕСЛИ будет»Не зач.» – результат,). Например, чтобы представить

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

деление или объединение

Формула с функциями ЕСЛИ и СРЗНАЧ для отбора значений при условии

также можно вычислять ссылкой или массивом, ячейки, в которых Получилась такая формула.Подсчитаем сумму значений, которые в ячейке вид: =ЕСЛИ(И(A1>=6; B1>10000); текстовая строка «верно». если И вернула значения 10, 20, имена. буквой R, за абсолютная ссылка не букву столбца, и в которых они ниже таблице. Если чисел. Результатом операций платеж по ипотеке, содержит текст или

текст (например, «да»

=ЕСЛИ(А1=2;6;0) больше 10 с

А1

«проблемный клиент»; «»).

Однако, если бы ЛОЖЬ. 30 и 40,
Excel Online не поддерживает которой следует номер изменяется. При копировании затем — номер содержатся. При вводе формула содержит операторы являются числа. Арифметические
решать математические или пустые ячейки, то — в анкете).

Копируем формулу вниз помощью функции СУММЕСЛИ(),результат формулы будет Отсюда следует, что выполнялась проверка ИЛИ(ОСТАТ(EXP(3);1)<>0;EXP(3)0

Формула с логическими функциями И ЕСЛИ ИЛИ в Excel

Используя функцию автозаполнения (сделав введите {10,20,30,40}. Эта создание формулы массива. строки, и буквой или заполнении формулы строки. Например, ссылка функции в формулу с одинаковым приоритетом, операторы приведены ниже. инженерные задачи и такие значения игнорируются. Или ячейки с по столбцу В.

записав =СУММЕСЛИ(A6:A11;»>10″). Аналогичный

либо «Бюджет превышен» если будет обнаружен возвращает ИСТИНА.

двойной щелчок по

константа массива является

Можно просмотреть результаты
C, за которой вдоль строк и B2 указывает на в диалоговом окне
например операторы деленияАрифметический оператор находить лучшие сценарииЕсли в указанном интервале

числом или числами Получилось так. результат (23) можно либо «ОК!». человек, который соответствует

Особенности использования логических функций ЕСЛИ, И, ИЛИ в Excel

На практике часто используют маркеру курсора в

матрицей размерности 1

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

отсутствуют логические значения,

больше, например, 5.Только в ячейке В2 получить с помощьюФункция ЕСЛИ()относится к наиболее указанным условиям, программа связки ЕСЛИ+И, ЕСЛИ+ИЛИ нижнем правом углу), на 4 и в классическом приложенииСсылка ссылка автоматически корректируется, пересечении столбца B иотображаются имя функции, Online выполняет ихПример указанных переменных значений. функция

Не только посчитать

условия, прописанные в формулы массива часто используемым функциям. укажет напротив его или сразу все получим остальные результаты: соответствует ссылке на Excel, но нельзяЗначение а абсолютная ссылка строки 2. все ее аргументы, слева направо.+ (знак «плюс»)В Excel Online всеИ

ячейки, но и формуле, совпали, и=СУММ(ЕСЛИ(A6:A11>10;A6:A11))ЕСЛИ(лог_выражение;значение_если_истина;[значение_если_ложь]) имени требуемый комментарий. три функции. РассмотримПример 3. Субсидии в одну строку и изменять и их

R[-2]C

не корректируется. Например,

Ячейка или диапазон описание функции иОператорСложение это реализуется свозвращает ошибку #ЗНАЧ! вычислить процент определенных там стоит цифра(для ввода формулыЛог_выражение Для всех прочих примеры подобного использования размере 30% начисляются четыре столбца. повторное вычисление. Еслиотносительная ссылка на ячейку, при копировании илиИспользование каждого из аргументов,Описание

3+3

помощью формул вНиже представлено несколько общих ответов к общему «2». в ячейку вместо — любое значение или участников перечня аналогичная этих функций. семьям со среднимЗначения ячеек из разных у вас есть расположенную на две

заполнении смешанной ссылкиЯчейка на пересечении столбца текущий результат функции: (двоеточие)– (знак «минус») ячейках. Формула выполняет

exceltable.com

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

Источник

На чтение 7 мин. Просмотров 1.6k.

=СУММПРОИЗВ(—(логическое выражение))

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

Контекст

Представьте, что у вас есть таблица показателей продаж для нескольких продуктов. У вас есть столбцы для продажи в прошлом месяце и столбец для продаж в текущем месяце. Вы хотите считать продукты (строки), где текущие продажи меньше, чем продажи в прошлом месяце. Вы не можете использовать СЧЁТЕСЛИМН для этого, потому что СЧЁТЕСЛИМН работает только с парой диапазонов критериев. Одним из вариантов: добавить вспомогательный столбец, который вычитает продажи в прошлом месяце от продаж в этом месяце, а затем использовать СЧЁТЕСЛИ для подсчета отрицательных результатов. Но что, если вы не хотите (или не можете) добавить вспомогательный столбец? В этом случае, вы можете использовать СУММПРОИЗВ.

В показанном примере, формула в ячейке G5 является:

=СУММПРОИЗВ(—(C5:C10<D5:D10))

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

В этом случае мы просто сравним значения в столбце С со значениями в столбце D, используя логическое выражение:

C5:C10<D5:D10

Поскольку мы имеем дело с диапазонами (массивами), результат представляет собой массив значений ИСТИНА, ЛОЖЬ:

{ЛОЖЬ, ИСТИНА; ЛОЖЬ, ИСТИНА; ЛОЖЬ; ЛОЖЬ}

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

—(C5:C10<D5:D10)

Который переведет массив в формат 1 и 0:

{0; 1; 0; 1; 0; 0}

Который затем обрабатывается СУММПРОИЗВ. Поскольку существует только один массив, СУММПРОИЗВ просто суммирует элементы массива и возвращает число.

Содержание

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

Количество, если строка соответствует нескольким внутренним критериям

=СУММПРОИЗВ((логическое1)*(логическое2))

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

Контекст

У вас есть таблица, содержащая результаты спортивных матчей. У вас есть четыре столбца: хозяева поля, команда гостей, счет команды хозяев, счет команды гостей. Для данной команды, вы хотите, чтобы рассчитывались только матчи (строки), где команда выиграла у себя дома. Легко подсчитать матчи (строки), где командой была команда хозяев поля, но как рассчитать только выигрыши?

Функция СУММПРОИЗВ может обрабатывать операции над массивами (вспомним операции, которые имеют дело с диапазонами) изначально.

В примере, формула в ячейке Н5:

=СУММПРОИЗВ((B5:B10=G5)*(D5:D10>E5:E10))

Функция СУММПРОИЗВ запрограммирована для обработки массивов изначально. Это поведение по умолчанию заключается в умножении соответствующих элементов в одном или более массивов вместе, а затем суммируются результаты. Когда дается один массив, она рассчитывает сумму элементов в массиве.

В этом примере мы используем два логических выражения внутри одного аргумента массива. Мы могли бы поместить каждое выражение в отдельный аргумент, но тогда мы должны были бы превращать логические значения ИСТИНА, ЛОЖЬ в единицы и нули с другим оператором.

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

После того, как два логических выражения вычисляются, формула выглядит следующим образом:

=СУММПРОИЗВ(({ЛОЖЬ;ИСТИНА;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА})*({ИСТИНА;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА}))

После того, как два массива умножаются, формула выглядит следующим образом:

=СУММПРОИЗВ({0;1;0;0;0;1})

СУММПРОИЗВ просто суммирует элементы массива и рассчитывает сумму.

Количество ячеек, если совпадают два критерия.

=СЧЁТЕСЛИМН(диапазон1;критерий1;диапазон2;критерий2)

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

В приведенном примере мы хотим подсчитать количество заказов с цветом «синий» и количеством > 15. Формула в ячейке F5 является:

=СЧЁТЕСЛИМН(B5:B12;»синий»;C5:C12;»>15″)

Функция СЧЁТЕСЛИМН принимает несколько критериев в парах — каждая пара содержит один диапазон и соответствующий критерий для этого диапазона. Чтобы создать счет, все условия должны совпадать. Чтобы добавить дополнительные условия, просто добавьте еще одну пару диапазон / критерий.

СУММПРОИЗВ альтернатива

Вы можете также использовать функцию СУММПРОИЗВ для подсчета строк, которые соответствуют нескольким условиям. Эквивалентная формула:

= СУММПРОИЗВ ((B5:B12 = «Синий») * (C5:C12>15))

СУММПРОИЗВ является более мощным и гибким, чем СЧЁТЕСЛИМН, и он работает со всеми версиями Excel, но это не так быстро, с большими наборами данных.

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

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

Количество нескольких критериев с НЕ логикой

=СУММПРОИЗВ((rng1=crit1)*ЕНД(ПОИСКПОЗ (rng2;crit2;0)))

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

В показанном примере формула в G9 является:

=СУММПРОИЗВ((C5:C12=F5)*ЕНД(ПОИСКПОЗ(D5:D12; G5:G6; 0)))

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

Первое выражение внутри СУММПРОИЗВ сравнивает значения в столбце C «Пол» со значением в F5, «Мужской»:

(Пол = F5)

Результатом является массив значений ИСТИНА, ЛОЖЬ:

{ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ИСТИНА; ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ}

Где ИСТИНА соответствует «Мужской».

Второе выражение внутри СУММПРОИЗВ проверяет значения в столбце D, группы со значениями в G5:G6 — «A» и «Б». Этот тест обрабатывается с ПОИСКПОЗ и ЕНД:

ЕНД(ПОИСКПОЗ (Группа; G5:G6;0))

Функция ПОИСКПОЗ используется для сопоставления каждого значения в указанном диапазоне «Группы» со значениями в G5:G6 — «A» и «Б». Если совпадение завершается успешно, ПОИСКПОЗ рассчитывает число. Если совпадение не найдено, ПОИСКПОЗ возвращает # N/A. Результатом является массив:

{1; 2; # N / A; 1; 2; # N / A; 1; 2; # N / A}

Так как значения # N/A соответствуют «не А или Б», ЕНД используется для «обратного» массива:

{ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА}

Теперь ИСТИНА соответствует «не А или Б».

Внутри СУММПРОИЗВ, оба результата массива перемножаются, который создает единый числовой массив внутри СУММПРОИЗВ:

СУММПРОИЗВ ({0; 0; 1; 0; 0; 1; 0; 0; 0})

СУММПРОИЗВ затем рассчитывает сумму, 2, представляющую «Два мужчины не в группе А или Б».

Количество строк, соответствующих сразу нескольким критериям

=СУММПРОИЗВ(—((критерий1)+(критерий2)>0))

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

В показанном примере формула в H5 является:

= СУММПРОИЗВ (- ((С5: С11 = «синий») + (D5: D11 = «собака»)> 0))

В приведенном примере мы хотим считать строки, где цвет «синий», или домашнее животное «собака».

Функция СУММПРОИЗВ работает с массивами изначально, для первого критерия мы используем:

(C5:C11 = «синий»)

Это возвращает массив значений ИСТИНА, ЛОЖЬ:

{ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА}

Для второго критерия, мы используем:

(D5:D11 = «собака»)

Который возвращает:

{ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ}

Эти два массива затем соединяются с добавлением (+), который автоматически превращает истинные значения ИСТИНА в 1 и 0, чтобы создать массив вроде этого:

{2; 0; 1; 1; 1; 0; 1}

Мы не можем просто добавить эти значения с СУММПРОИЗВ потому, что это удвоит подсчет строк с «синий» и «собака». Таким образом, мы используем «> 0» вместе с двойным минусом (-), чтобы превратить все значения в 1 или 0:

— ({2; 0; 1; 1; 1; 0; 1}> 0)

Что превращает этот массив в:

{1; 0; 1; 1; 1; 0; 1}

СУММПРОИЗВ затем рассчитывает сумму всех элементов.

Другие логические тесты

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

= СУММПРОИЗВ (- (ЕЧИСЛО(ПОИСК ( «красный»; A1: A10)) + ЕЧИСЛО (ПОИСК( «синий»; B1: B10))> 0))

Более логические тесты

Вы также можете добавить больше, чем два теста внутри функции СУММПРОИЗВ.

Источник

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Рис5

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

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

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

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

Базовая информация об ИНДЕКС и ПОИСКПОЗ
Используем функции ИНДЕКС и ПОИСКПОЗ в Excel
Преимущества ИНДЕКС и ПОИСКПОЗ перед ВПР
ИНДЕКС и ПОИСКПОЗ – примеры формул
- Как находить значения, которые находятся слева
- Вычисления при помощи ИНДЕКС и ПОИСКПОЗ
- Поиск по известным строке и столбцу
- Поиск по нескольким критериям
- ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА

Содержание

Базовая информация об ИНДЕКС и ПОИСКПОЗ
ИНДЕКС – синтаксис и применение функции
ПОИСКПОЗ – синтаксис и применение функции
Как использовать ИНДЕКС и ПОИСКПОЗ в Excel
Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?
4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:
ИНДЕКС и ПОИСКПОЗ – примеры формул
Как выполнить поиск с левой стороны, используя ПОИСКПОЗ и ИНДЕКС
Вычисления при помощи ИНДЕКС и ПОИСКПОЗ в Excel (СРЗНАЧ, МАКС, МИН)
О чём нужно помнить, используя функцию СРЗНАЧ вместе с ИНДЕКС и ПОИСКПОЗ
Как при помощи ИНДЕКС и ПОИСКПОЗ выполнять поиск по известным строке и столбцу
Поиск по нескольким критериям с ИНДЕКС и ПОИСКПОЗ
ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА в Excel

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

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

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

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

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

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

Каждый аргумент имеет очень простое объяснение:

array (массив) – это диапазон ячеек, из которого необходимо извлечь значение.
row_num (номер_строки) – это номер строки в массиве, из которой нужно извлечь значение. Если не указан, то обязательно требуется аргумент column_num (номер_столбца).
column_num (номер_столбца) – это номер столбца в массиве, из которого нужно извлечь значение. Если не указан, то обязательно требуется аргумент row_num (номер_строки)

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

Вот простейший пример функции INDEX (ИНДЕКС):

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

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

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

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

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

Например, если в диапазоне B1:B3 содержатся значения New-York, Paris, London, тогда следующая формула возвратит цифру 3, поскольку «London» – это третий элемент в списке.

=MATCH("London",B1:B3,0)
=ПОИСКПОЗ("London";B1:B3;0)

Функция MATCH (ПОИСКПОЗ) имеет вот такой синтаксис:

MATCH(lookup_value,lookup_array,[match_type])
ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])

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

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

Позвольте напомнить, что относительное положение искомого значения (т.е. номер строки и/или столбца) – это как раз то, что мы должны указать для аргументов row_num (номер_строки) и/или column_num (номер_столбца) функции INDEX (ИНДЕКС). Как Вы помните, функция ИНДЕКС может возвратить значение, находящееся на пересечении заданных строки и столбца, но она не может определить, какие именно строка и столбец нас интересуют.

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

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

Ещё не совсем понятно? Представьте функции ИНДЕКС и ПОИСКПОЗ в таком виде:

=INDEX(столбец из которого извлекаем,(MATCH (искомое значение,столбец в котором ищем,0))
=ИНДЕКС(столбец из которого извлекаем;(ПОИСКПОЗ(искомое значение;столбец в котором ищем;0))

Думаю, ещё проще будет понять на примере. Предположим, у Вас есть вот такой список столиц государств:

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

=INDEX($D$2:$D$10,MATCH("Japan",$B$2:$B$10,0))
=ИНДЕКС($D$2:$D$10;ПОИСКПОЗ("Japan";$B$2:$B$10;0))

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

Функция MATCH (ПОИСКПОЗ) ищет значение «Japan» в столбце B, а конкретно – в ячейках B2:B10, и возвращает число 3, поскольку «Japan» в списке на третьем месте.
Функция INDEX (ИНДЕКС) использует 3 для аргумента row_num (номер_строки), который указывает из какой строки нужно возвратить значение. Т.е. получается простая формула:
=INDEX($D$2:$D$10,3)
=ИНДЕКС($D$2:$D$10;3)

Формула говорит примерно следующее: ищи в ячейках от D2 до D10 и извлеки значение из третьей строки, то есть из ячейки D4, так как счёт начинается со второй строки.

Вот такой результат получится в Excel:

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

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

=VLOOKUP("Japan",$B$2:$D$2,3)
=ВПР("Japan";$B$2:$D$2;3)

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

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

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

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

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

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

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

Например, если у Вас есть таблица A1:C10, и требуется извлечь данные из столбца B, то нужно задать значение 2 для аргумента col_index_num (номер_столбца) функции ВПР, вот так:

=VLOOKUP("lookup value",A1:C10,2)
=ВПР("lookup value";A1:C10;2)

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

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

3. Нет ограничения на размер искомого значения. Используя ВПР, помните об ограничении на длину искомого значения в 255 символов, иначе рискуете получить ошибку #VALUE! (#ЗНАЧ!). Итак, если таблица содержит длинные строки, единственное действующее решение – это использовать ИНДЕКС/ПОИСКПОЗ.

Предположим, Вы используете вот такую формулу с ВПР, которая ищет в ячейках от B5 до D10 значение, указанное в ячейке A2:

=VLOOKUP(A2,B5:D10,3,FALSE)
=ВПР(A2;B5:D10;3;ЛОЖЬ)

Формула не будет работать, если значение в ячейке A2 длиннее 255 символов. Вместо неё Вам нужно использовать аналогичную формулу ИНДЕКС/ПОИСКПОЗ:

=INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))
=ИНДЕКС(D5:D10;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B5:B10=A2;0);0))

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

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

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

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

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

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

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

Функции ПОИСКПОЗ и ИНДЕКС в Excel гораздо более гибкие, и им все-равно, где находится столбец со значением, которое нужно извлечь. Для примера, снова вернёмся к таблице со столицами государств и населением. На этот раз запишем формулу ПОИСКПОЗ/ИНДЕКС, которая покажет, какое место по населению занимает столица России (Москва).

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

=INDEX($A$2:$A$10,MATCH("Russia",$B$2:$B$10,0))
=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ("Russia";$B$2:$B$10;0))

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

Во-первых, задействуем функцию MATCH (ПОИСКПОЗ), которая находит положение «Russia» в списке:
=MATCH("Russia",$B$2:$B$10,0))
=ПОИСКПОЗ("Russia";$B$2:$B$10;0))
Далее, задаём диапазон для функции INDEX (ИНДЕКС), из которого нужно извлечь значение. В нашем случае это A2:A10.
Затем соединяем обе части и получаем формулу:
=INDEX($A$2:$A$10;MATCH("Russia";$B$2:$B$10;0))
=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ("Russia";$B$2:$B$10;0))

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

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

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

1. MAX (МАКС). Формула находит максимум в столбце D и возвращает значение из столбца C той же строки:

=INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МАКС($D$2:I$10);$D$2:D$10;0))

Результат: Beijing

2. MIN (МИН). Формула находит минимум в столбце D и возвращает значение из столбца C той же строки:

=INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МИН($D$2:I$10);$D$2:D$10;0))

Результат: Lima

3. AVERAGE (СРЗНАЧ). Формула вычисляет среднее в диапазоне D2:D10, затем находит ближайшее к нему и возвращает значение из столбца C той же строки:

=INDEX($C$2:$C$10,MATCH(AVERAGE($D$2:D$10),$D$2:D$10,1))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(СРЗНАЧ($D$2:D$10);$D$2:D$10;1))

Результат: Moscow

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

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

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

В нашем примере значения в столбце D упорядочены по возрастанию, поэтому мы используем тип сопоставления 1. Формула ИНДЕКС/ПОИСКПОЗ возвращает «Moscow», поскольку величина населения города Москва – ближайшее меньшее к среднему значению (12 269 006).

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

Эта формула эквивалентна двумерному поиску ВПР и позволяет найти значение на пересечении определённой строки и столбца.

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

Как Вы помните, синтаксис функции INDEX (ИНДЕКС) позволяет использовать три аргумента:

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

И я поздравляю тех из Вас, кто догадался!

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

=INDEX(Ваша таблица,(MATCH(значение для вертикального поиска,столбец, в котором искать,0)),(MATCH(значение для горизонтального поиска,строка в которой искать,0))
=ИНДЕКС(Ваша таблица,(MATCH(значение для вертикального поиска,столбец, в котором искать,0)),(MATCH(значение для горизонтального поиска,строка в которой искать,0))

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

А теперь давайте испытаем этот шаблон на практике. Ниже Вы видите список самых населённых стран мира. Предположим, наша задача узнать население США в 2015 году.

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

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

ПОИСКПОЗ для столбца – мы ищем в столбце B, а точнее в диапазоне B2:B11, значение, которое указано в ячейке H2 (USA). Функция будет выглядеть так:
=MATCH($H$2,$B$1:$B$11,0)
=ПОИСКПОЗ($H$2;$B$1:$B$11;0)

Результатом этой формулы будет 4, поскольку «USA» – это 4-ый элемент списка в столбце B (включая заголовок).
ПОИСКПОЗ для строки – мы ищем значение ячейки H3 (2015) в строке 1, то есть в ячейках A1:E1:
=MATCH($H$3,$A$1:$E$1,0)
=ПОИСКПОЗ($H$3;$A$1:$E$1;0)

Результатом этой формулы будет 5, поскольку «2015» находится в 5-ом столбце.

Теперь вставляем эти формулы в функцию ИНДЕКС и вуаля:

=INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0))
=ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($H$2;$B$1:$B$11;0);ПОИСКПОЗ($H$3;$A$1:$E$1;0))

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

=INDEX($A$1:$E$11,4,5))
=ИНДЕКС($A$1:$E$11;4;5))

Эта формула возвращает значение на пересечении 4-ой строки и 5-го столбца в диапазоне A1:E11, то есть значение ячейки E4. Просто? Да!

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

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

Предположим, у нас есть список заказов, и мы хотим найти сумму по двум критериям – имя покупателя (Customer) и продукт (Product). Дело усложняется тем, что один покупатель может купить сразу несколько разных продуктов, и имена покупателей в таблице на листе Lookup table расположены в произвольном порядке.

Вот такая формула ИНДЕКС/ПОИСКПОЗ решает задачу:

{=INDEX('Lookup table'!$A$2:$C$13,MATCH(1,(A2='Lookup table'!$A$2:$A$13)* (B2='Lookup table'!$B$2:$B$13),0),3)}
{=ИНДЕКС('Lookup table'!$A$2:$C$13;ПОИСКПОЗ(1;(A2='Lookup table'!$A$2:$A$13)* (B2='Lookup table'!$B$2:$B$13);0);3)}

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

MATCH(1,(A2='Lookup table'!$A$2:$A$13),0)*(B2='Lookup table'!$B$2:$B$13)
ПОИСКПОЗ(1;(A2='Lookup table'!$A$2:$A$13);0)*(B2='Lookup table'!$B$2:$B$13)

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

Берем первое значение в столбце A (Customer) на листе Main table и сравниваем его со всеми именами покупателей в таблице на листе Lookup table (A2:A13).
Если совпадение найдено, уравнение возвращает 1 (ИСТИНА), а если нет – 0 (ЛОЖЬ).
Далее, мы делаем то же самое для значений столбца B (Product).
Затем перемножаем полученные результаты (1 и 0). Только если совпадения найдены в обоих столбцах (т.е. оба критерия истинны), Вы получите 1. Если оба критерия ложны, или выполняется только один из них – Вы получите 0.

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

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

И, наконец, т.к. нам нужно проверить каждую ячейку в массиве, эта формула должна быть формулой массива. Вы можете видеть это по фигурным скобкам, в которые она заключена. Поэтому, когда закончите вводить формулу, не забудьте нажать Ctrl+Shift+Enter.

Если всё сделано верно, Вы получите результат как на рисунке ниже:

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

Как Вы, вероятно, уже заметили (и не раз), если вводить некорректное значение, например, которого нет в просматриваемом массиве, формула ИНДЕКС/ПОИСКПОЗ сообщает об ошибке #N/A (#Н/Д) или #VALUE! (#ЗНАЧ!). Если Вы хотите заменить такое сообщение на что-то более понятное, то можете вставить формулу с ИНДЕКС и ПОИСКПОЗ в функцию ЕСЛИОШИБКА.

Синтаксис функции ЕСЛИОШИБКА очень прост:

IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)

Где аргумент value (значение) – это значение, проверяемое на предмет наличия ошибки (в нашем случае – результат формулы ИНДЕКС/ПОИСКПОЗ); а аргумент value_if_error (значение_если_ошибка) – это значение, которое нужно возвратить, если формула выдаст ошибку.

Например, Вы можете вставить формулу из предыдущего примера в функцию ЕСЛИОШИБКА вот таким образом:

=IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1:$E$1,0)), "Совпадений не найдено. Попробуйте еще раз!") =ЕСЛИОШИБКА(ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($G$2;$B$1:$B$11;0);ПОИСКПОЗ($G$3;$A$1:$E$1;0)); "Совпадений не найдено. Попробуйте еще раз!")

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

Если Вы предпочитаете в случае ошибки оставить ячейку пустой, то можете использовать кавычки («»), как значение второго аргумента функции ЕСЛИОШИБКА. Вот так:

IFERROR(INDEX(массив,MATCH(искомое_значение,просматриваемый_массив,0),"")
ЕСЛИОШИБКА(ИНДЕКС(массив;ПОИСКПОЗ(искомое_значение;просматриваемый_массив;0);"")

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

Оцените качество статьи. Нам важно ваше мнение:

Источник