Как найти значение ряда эксель

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

Описание

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

Создание образца листа

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

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

	A	B	C	D	E
1	Имя	Правитель	Возраст		Поиск значения
2	Анри	501	Плот		Иванов
3	Стэн	201	19
4	Иванов	101	максималь
5	Ларри	301	составляет

Определения терминов

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

Термин	Определение	Пример
Массив таблиц	Вся таблица подстановки	A2: C5
Превышающ	Значение, которое будет найдено в первом столбце аргумента «инфо_таблица».	E2
Просматриваемый_массив -или- Лукуп_вектор	Диапазон ячеек, которые содержат возможные значения подстановки.	A2: A5
Номер_столбца	Номер столбца в аргументе инфо_таблица, для которого должно быть возвращено совпадающее значение.	3 (третий столбец в инфо_таблица)
Ресулт_аррай -или- Ресулт_вектор	Диапазон, содержащий только одну строку или один столбец. Он должен быть такого же размера, что и просматриваемый_массив или Лукуп_вектор.	C2: C5
Интервальный_просмотр	Логическое значение (истина или ложь). Если указано значение истина или опущено, возвращается приближенное соответствие. Если задано значение FALSE, оно будет искать точное совпадение.	ЛОЖЬ
Топ_целл	Это ссылка, на основе которой вы хотите основать смещение. Топ_целл должен ссылаться на ячейку или диапазон смежных ячеек. В противном случае функция СМЕЩ возвращает #VALUE! значение ошибки #ИМЯ?.
Оффсет_кол	Число столбцов, находящегося слева или справа от которых должна указываться верхняя левая ячейка результата. Например, значение «5» в качестве аргумента Оффсет_кол указывает на то, что верхняя левая ячейка ссылки состоит из пяти столбцов справа от ссылки. Оффсет_кол может быть положительным (то есть справа от начальной ссылки) или отрицательным (то есть слева от начальной ссылки).

Функции

LOOKUP ()

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

Ниже приведен пример синтаксиса формулы подСТАНОВКи.

   = Просмотр (искомое_значение; Лукуп_вектор; Ресулт_вектор)

Следующая формула находит возраст Марии на листе «образец».

   = ПРОСМОТР (E2; A2: A5; C2: C5)

Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в векторе подстановки (столбец A). Формула затем соответствует значению в той же строке в векторе результатов (столбец C). Так как «Мария» находится в строке 4, функция Просмотр возвращает значение из строки 4 в столбце C (22).

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

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

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

ВПР ()

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

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

    = ВПР (искомое_значение; инфо_таблица; номер_столбца; интервальный_просмотр)

Следующая формула находит возраст Марии на листе «образец».

   = ВПР (E2; A2: C5; 3; ЛОЖЬ)

Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в левом столбце (столбец A). Формула затем совпадет со значением в той же строке в Колумн_индекс. В этом примере используется «3» в качестве Колумн_индекс (столбец C). Так как «Мария» находится в строке 4, функция ВПР возвращает значение из строки 4 В столбце C (22).

Чтобы получить дополнительные сведения о функции ВПР , щелкните следующий номер статьи базы знаний Майкрософт:
 

Как найти точное совпадение с помощью функций ВПР или ГПР

INDEX () и MATCH ()

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

Ниже приведен пример синтаксиса, объединяющего индекс и Match для получения одинаковых результатов поиска и ВПР в предыдущих примерах:

    = Индекс (инфо_таблица; MATCH (искомое_значение; просматриваемый_массив; 0); номер_столбца)

Следующая формула находит возраст Марии на листе «образец».

= ИНДЕКС (A2: C5; MATCH (E2; A2: A5; 0); 3)

Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Затем он будет соответствовать значению в той же строке в столбце C. Так как «Мария» находится в строке 4, формула возвращает значение из строки 4 в столбце C (22).

Обратите внимание Если ни одна из ячеек в аргументе «число» не соответствует искомому значению («Мария»), эта формула будет возвращать #N/А.
Чтобы получить дополнительные сведения о функции индекс , щелкните следующий номер статьи базы знаний Майкрософт:

Поиск данных в таблице с помощью функции индекс

СМЕЩ () и MATCH ()

Функции СМЕЩ и ПОИСКПОЗ можно использовать вместе, чтобы получить те же результаты, что и функции в предыдущем примере.

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

   = СМЕЩЕНИЕ (топ_целл, MATCH (искомое_значение; просматриваемый_массив; 0); Оффсет_кол)

Эта формула находит возраст Марии на листе «образец».

   = СМЕЩЕНИЕ (A1; MATCH (E2; A2: A5; 0); 2)

Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Формула затем соответствует значению в той же строке, но двум столбцам справа (столбец C). Так как «Мария» находится в столбце A, формула возвращает значение в строке 4 в столбце C (22).

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

Использование функции СМЕЩ

5 способов – поиск значения в массиве Excel

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

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

• Поиск в массиве при помощи ИНДЕКС ПОИСКПОЗ
• Формула ВПР и ПОИСКПОЗ для поиска в диапазоне
• Функция ПРОСМОТРX для поиска в строках и столбцах
• Формула СУММПРОИЗВ для поиска по строке и столбцу
• Поиск в матрице с именованными диапазонами

Поиск в массиве при помощи ИНДЕКС ПОИСКПОЗ

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

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

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

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

• Массив_данных — B2:E11 (ячейки данных, не включая заголовки строк и столбцов)
• Значение_вертикальное — H1 (целевой товар)
• Диапазон_поиска_столбец – A2:A11 (заголовки строк: названия напитков)
• Значение_горизонтальное — H2 (целевой период)
• Диапазон_поиска_строка — B1:E1 (заголовки столбцов: временные периоды)

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

=ИНДЕКС(B2:E11; ПОИСКПОЗ(H1;A2:A11;0); ПОИСКПОЗ(H2;B1:E1;0))

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

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

ИНДЕКС( B2:E11; номер_строки ; номер_столбца )

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

Итак, чтобы получить номер строки, мы ищем нужный нам товар (H1) в заголовках строк (A2:A11):

ПОИСКПОЗ(H1;A2:A11;0)

Чтобы получить номер столбца, мы ищем нужную нам неделю (H2) в заголовках столбцов (B1:E1):

ПОИСКПОЗ(H2;B1:E1;0)

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

В этом примере первое ПОИСКПОЗ возвращает 2, потому что нужный товар (Sprite) находится в ячейке A3, которая является второй по счёту в диапазоне ​​A2:A11. Второй ПОИСКПОЗ возвращает 3, так как «Неделя 3» находится в ячейке D1, которая является третьей ячейкой в ​​B1:E1.

С учетом вышеизложенного формула сводится к:

ИНДЕКС(B2:E11; 2 ; 3 )

Она возвращает число на пересечении второй строки и третьего столбца в матрице B2:E4, то есть в ячейке D3.

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

Формула ВПР и ПОИСКПОЗ для поиска в диапазоне

Другой способ выполнить матричный поиск в Excel — использовать комбинацию функций ВПР и ПОИСКПОЗ:

ВПР( значение_вертикальное ; массив_данных ; ПОИСКПОЗ( значение_горизонтальное , диапазон_поиска_строка , 0), ЛОЖЬ)

Для нашего образца таблицы формула принимает следующий вид:

=ВПР(H1; A2:E11; ПОИСКПОЗ(H2;A1:E1;0); ЛОЖЬ)

Где:

• Массив_данных — B2:E11 (ячейки данных, не включая заголовки строк и столбцов)
• Значение_вертикальное — H1 (целевой товар)
• Значение_горизонтальное — H2 (целевой период)
• Диапазон_поиска_строка — А1:E1 (заголовки столбцов: временные периоды)

Основой формулы является функция ВПР, настроенная на точное совпадение (последний аргумент имеет значение ЛОЖЬ). Она ищет заданное значение (H1) в первом столбце массива (A2:E11) и возвращает данные из другого столбца в той же строке. Чтобы определить, из какого столбца вернуть значение, вы используете функцию ПОИСКПОЗ, которая также настроена на точное совпадение (последний аргумент равен 0):

ПОИСКПОЗ(H2;A1:E1;0)

ПОИСКПОЗ ищет текст из H2 в заголовках столбцов (A1:E1) и указывает относительное положение найденной ячейки. В нашем случае нужная неделя (3-я) находится в D1, которая является четвертой по счету в  массиве поиска. Итак, число 4 идет в аргумент номер_столбца функции ВПР:

=ВПР(H1; A2:E11; 4; ЛОЖЬ)

Далее ВПР находит точное совпадение H1 со значением в A3 и возвращает значение из 4-го столбца в той же строке, то есть из ячейки D3.

Важное замечание! Чтобы формула работала корректно, диапазон_поиска (A2:E11) функции ВПР и диапазон_поиска (A1:E1) функции ПОИСКПОЗ должны иметь одинаковое количество столбцов. Иначе число, переданное в номер_столбца, будет неправильным (не будет соответствовать положению столбца в массиве данных).

Функция ПРОСМОТРX для поиска в строках и столбцах

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

ПРОСМОТРX( значение_вертикальное ; диапазон_поиска_столбец ; ПРОСМОТРX( значение_горизонтальное ; диапазон_поиска_строка ; массив_данных ))

Для нашего примера набора данных формула выглядит следующим образом:

=ПРОСМОТРX(H1; A2:A11; ПРОСМОТРX(H2; B1:E1; B2:E11))

Примечание. В настоящее время ПРОСМОТРX — это функция, доступная только подписчикам Office 365 и более поздних версий.

В формуле используется функция ПРОСМОТРX для возврата всей строки или столбца. Внутренняя функция ищет целевой период времени в строке заголовка и возвращает все значения для этой недели (в данном примере для 3-й). Эти значения переходят в аргумент возвращаемый_массив внешнего ПРОСМОТРX:

=ПРОСМОТРX(H1; A2:A11; {544:87:488:102:87:433:126:132:111:565})

Внешняя функция ПРОСМОТРX ищет нужный товар в заголовках столбцов и извлекает значение из той же позиции из возвращаемого_массива.

Формула СУММПРОИЗВ для поиска по строке и столбцу

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

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

СУММПРОИЗВ ( диапазон_поиска_столбец = значение_вертикальное ) * ( диапазон_поиска_строка = значение_горизонтальное), массив_данных )

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

=СУММПРОИЗВ((A2:A11=H1)*(B1:E1=H2); B2:E11)

Приведенный ниже вариант также будет работать:

=СУММПРОИЗВ((A2:A11=H1)*(B1:E1=H2)*B2:E11)

Теперь поясним подробнее. В начале мы сравниваем два значения поиска с заголовками строк и столбцов (целевой товар в H1 со всеми наименованиями в A2: A11 и целевой период времени в H2 со всеми неделями в B1: E1):

(A2:A11=H1)*(B1:E1=H2)

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

{ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}) * ({ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ}

Операция умножения преобразует значения ИСТИНА и ЛОЖЬ в 1 и 0 и создает матрицу из 4 столбцов и 10 строк (строки разделяются двоеточием, а каждый столбец данных — точкой с запятой):

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

Функция СУММПРОИЗВ умножает элементы приведенного выше массива на элементы B2:E4, находящихся в тех же позициях:

{0;0;0;0:0;0;1;0:0;0;0;0:0;0;0;0:0;0;0;0:0;0;0;0:0;0; 0;0:0;0;0;0:0;0;0;0:0;0;0;0} * {455;345;544;366:65;77;87;56:766; 655;488;865:129;66;102;56:89;141;87;89:566;511;433;522:154; 144;126; 162:158;165;132;155:112;143;111; 125:677;466;565;766})

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

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

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

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

Поиск в матрице с именованными диапазонами

Еще один достаточно простой способ поиска в массиве в Excel — использование именованных диапазонов. Рассмотрим пошагово:

Шаг 1. Назовите столбцы и строки

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

1. Выделите всю таблицу (в нашем случае A1:E11).
2. На вкладке « Формулы » в группе « Определенные имена » щелкните « Создать из выделенного » или нажмите комбинацию клавиш  Ctrl + Shift + F3.
3. В диалоговом окне « Создание имени из выделенного » выберите « в строке выше » и « в столбце слева» и нажмите «ОК».

Это автоматически создает имена на основе заголовков строк и столбцов. Однако есть пара предостережений:

• Если ваши заголовки столбцов и/или строк являются числами или содержат определенные символы, которые не разрешены в именах Excel, то имена для таких столбцов и строк не будут созданы. Чтобы просмотреть список созданных имен, откройте Диспетчер имен (Ctrl + F3). Если некоторые имена отсутствуют, определите их вручную.
• Если некоторые из ваших заголовков строк или столбцов содержат пробелы, то они будут заменены символами подчеркивания, например, Неделя_1.

Шаг 2. Создание формулы поиска по матрице

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

=имя_строки имя_столбца

Или наоборот:

=имя_столбца имя_строки

Например, чтобы получить продажу Sprite в 3-й неделе, используйте выражение:

=Sprite неделя_3

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

Если кому-то нужны более подробные инструкции, опишем весь процесс пошагово:

1. В ячейке, в которой вы хотите отобразить результат, введите знак равенства (=).
2. Начните вводить имя целевой строки, Sprite. После того, как вы введете пару символов, Excel отобразит все существующие имена, соответствующие вашему вводу. Дважды щелкните нужное имя, чтобы ввести его в формулу.
3. После имени строки введите пробел , который в данном случае работает как оператор пересечения.
4. Введите имя целевого столбца ( в нашем случае неделя_3 ).
5. Как только будут введены имена строки и столбца, Excel выделит соответствующую строку и столбец в вашей таблице, и вы нажмете Enter, чтобы завершить ввод:

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

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

Еще несколько материалов по теме:

Поиск ВПР нескольких значений по нескольким условиям — В статье показаны способы поиска (ВПР) нескольких значений в Excel на основе одного или нескольких условий и возврата нескольких результатов в столбце, строке или в отдельной ячейке. При использовании Microsoft…

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

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

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

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

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

Средневзвешенное значение — формула в Excel — В этом руководстве демонстрируются два простых способа вычисления средневзвешенного значения в Excel — с помощью функции СУММ (SUM) или СУММПРОИЗВ (SUMPRODUCT в английском варианте). В одной из предыдущих статей мы…

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

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

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

Необходимо определить регион поставки по артикулу товара, набранному в ячейку C16.

Задача решается при помощи двух функций:

=ИНДЕКС(A1:G13;ПОИСКПОЗ(C16;D1:D13;0);2)

Функция ПОИСКПОЗ ищет в столбце D1:D13 значение артикула из ячейки C16. Последний аргумент функции 0 — означает поиск точного (а не приблизительного) соответствия. Функция выдает порядковый номер найденного значения в диапазоне, т.е. фактически номер строки, где найден требуемыый артикул.

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

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

• Использование функции ВПР (VLOOKUP) для поиска и подстановки значений.
• Улучшенная версия функции ВПР (VLOOKUP)
• Многоразовый ВПР

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

​Смотрите также​ с макросом)))​​. Вообще говоря, возможных​​Читайте также: Поиск значения​​ 4. Но так​​ они оказались перед​ отделяет ссылку на​Введите знак равенства «=»,​ экран. Как видим,​​ является подсчет ячеек​​ количества действий. Он​. Если оба критерия​ населённых стран мира.​Подсказка:​ беспокоясь о том,​Важно! Количество строк и​​, поскольку «London» –​​Этот учебник рассказывает о​

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

​: А послений мой​ три:​ Excel по столбцам​​ не известен этот​​ после листа 6,​ на диапазон ячеек.​ Например, чтобы получить​ в 63 ячейках​ а в нашем​ ячеек, содержащих числовые​ только один из​ узнать население США​ всегда использовать абсолютные​​ исправлять каждую используемую​​ который использует функция​​ в списке.​​ИНДЕКС​ пример не подошел,​1​ и строкам​ номер мы с​​ Microsoft Excel вычтет​​Примечание:​

• ​ общий объем продаж,​ содержится слово​
• ​ случае в столбце,​ и текстовые данные.​
• ​ них – Вы​ в 2015 году.​
• ​ ссылки для​ функцию​
  • ​INDEX​=MATCH(«London»,B1:B3,0)​
  • ​и​ разве?​
  • ​- поиск ближайшего​По сути содержимое диапазона​
  • ​ помощью функции СТОЛБЕЦ​
  • ​ из суммы содержимое​ Если название упоминаемого листа​

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

​ нужно ввести «=СУММ».​«Мясо»​ который содержит числовые​​ Сделать это можно​​ получите​​Хорошо, давайте запишем формулу.​​ИНДЕКС​ВПР​(ИНДЕКС), должно соответствовать​=ПОИСКПОЗ(«London»;B1:B3;0)​ПОИСКПОЗ​

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

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

​и​​.​​ значениям аргументов​Функция​в Excel, которые​: Как раз сижу​ введенные пользователем размеры​ интересует, нам нужен​

​ столбцов для диапазона​
​ листов.​

​ цифры, его нужно​ «(«.​

• ​Давайте немного изменим задачу.​​ функции практически идентичен​ индикатор в строке​.​ создать сложную формулу​
• ​ПОИСКПОЗ​​3. Нет ограничения на​row_num​MATCH​ делают их более​ разбираю… Честно говоря​ двери округлялись бы​ просто счетчик строк.​​ B4:G15.​​Перемещение конечного листа​
• ​ заключить в апострофы​​Выделите диапазон ячеек, а​ Теперь посчитаем количество​ предыдущему оператору:​ состояния.​Теперь понимаете, почему мы​ в Excel с​, чтобы диапазоны поиска​​ размер искомого значения.​​(номер_строки) и​

​(ПОИСКПОЗ) имеет вот​ привлекательными по сравнению​​ так и не​​ до ближайших наименьших​ То есть изменить​Это позволяет функции ВПР​   . Если переместить лист 2​

​ (‘), например так:​​ затем введите закрывающую​​ ячеек в этой​

​=СЧЁТ(значение1;значение2;…)​
​Для выполнения данной задачи​

​ задали​ вложенными функциями, то​​ не сбились при​​Используя​column_num​​ такой синтаксис:​​ с​​ допер я со​​ подходящих размеров из​ аргументы на: СТРОКА(B2:B11)​​ собрать целый массив​​ или 6 в​

​ ‘123’!A1 или =’Прибыль​ круглую скобку «)».​ же колонке, которые​Как видим, аргументы у​ достаточно зажать левую​1​ я сначала каждую​​ копировании формулы в​​ВПР​

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

​(номер_столбца) функции​​MATCH(lookup_value,lookup_array,[match_type])​​ВПР​ СМЕЩ. Вот файл​ таблицы. В нашем​ или СТРОКА(С2:С11) –​ значений. В результате​ другое место книги,​

​ за январь’!A1.​​Нажмите клавишу ВВОД, чтобы​​ не содержат слово​СЧЁТ​ кнопку мыши и​, как искомое значение?​​ вложенную записываю отдельно.​​ другие ячейки.​, помните об ограничении​MATCH​

​ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])​
​. Вы увидите несколько​

​ более приблизенный к​​ случае высота 500​​ это никак не​ в памяти хранится​

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

• ​ получить результат.​​«Мясо»​и​ выделить весь столбец,​ Правильно, чтобы функция​Итак, начнём с двух​Вы можете вкладывать другие​ на длину искомого​
• ​(ПОИСКПОЗ). Иначе результат​​lookup_value​ примеров формул, которые​ структуре моего​
• ​ округлилась бы до​​ повлияет на качество​ все соответствующие значения​​ сумму с учетом​​ и смешанными ссылками​Мы подготовили для вас​.​
  • ​СЧЁТЗ​​ в котором вы​​ПОИСКПОЗ​​ функций​ функции Excel в​ значения в 255​ формулы будет ошибочным.​(искомое_значение) – это​ помогут Вам легко​genyaa​
  • ​ 450, а ширина​​ формулы. Главное, что​ каждому столбцу по​ изменения диапазона листов.​​Относительные ссылки​​ книгу Начало работы​​Выделяем ячейку, куда будем​​абсолютно одинаковые и​ хотите произвести подсчет​возвращала позицию только,​​ПОИСКПОЗ​​ИНДЕКС​​ символов, иначе рискуете​​Стоп, стоп… почему мы​
  • ​ число или текст,​​ справиться со многими​: а так?​ 480 до 300,​ в этих диапазонах​ строке Товар 4​Удаление конечного листа​   . Относительная ссылка в формуле,​ с формулами, которая​

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

​ который Вы ищите.​ сложными задачами, перед​iam_alex​ и стоимость двери​ по 10 строк,​ (а именно: 360;​   . Если удалить лист 2​ например A1, основана​​ доступна для скачивания.​​ уже описанным ранее​​ на ячейки или​​ выделение будет произведено,​​ выполняются.​​ номера строки и​ПОИСКПОЗ​​#VALUE!​​ использовать функцию​ Аргумент может быть​ которыми функция​: еще раз спасибо,​ была бы 135.​ как и в​ 958; 201; 605;​ или 6, Microsoft​

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

​ на относительной позиции​ Если вы впервые​ способом вызываем окно​ диапазоны. Различие в​ в строке состояния,​Обратите внимание:​​ столбца для функции​​, например, чтобы найти​​(#ЗНАЧ!). Итак, если​​VLOOKUP​​ значением, в том​​ВПР​ буду мучить, но​-1​ таблице. И нумерация​​ 462; 832). После​​ Excel скорректирует сумму​ ячейки, содержащей формулу,​ пользуетесь Excel или​ аргументов оператора​

​ синтаксисе заключается лишь​ которая расположена внизу​​В этом случае​​ИНДЕКС​​ минимальное, максимальное или​​ таблица содержит длинные​

​(ВПР)? Есть ли​ числе логическим, или​бессильна.​
​ уже не сегодня)​- поиск ближайшего​ начинается со второй​

​ чего функции МАКС​ с учетом изменения​ и ячейки, на​ даже имеете некоторый​СЧЁТЕСЛИ​

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

​ ближайшее к среднему​
​ строки, единственное действующее​

​ смысл тратить время,​ ссылкой на ячейку.​В нескольких недавних статьях​

• ​ есть еще идея​​ наибольшего числа, т.е.​​ строки!​ остается только взять​​ диапазона листов.​​ которую указывает ссылка.​ опыт работы с​​.​​ оператора.​​«Количество»​​ не обязательный аргумент​ПОИСКПОЗ для столбца​ значение. Вот несколько​
• ​ решение – это​​ пытаясь разобраться в​​lookup_array​​ мы приложили все​​ у коллег, потом​​ нестандартная высота 500​​Если вы знакомы с​ из этого массива​Стиль ссылок R1C1​ При изменении позиции​ этой программой, данный​

  ​В поле​
​Выделяем элемент на листе,​

  ​будет отображаться число​ функции​– мы ищем​​ вариантов формул, применительно​​ использовать​​ лабиринтах​​(просматриваемый_массив) – диапазон​ усилия, чтобы разъяснить​ сопоставлю и выложу,​ округлялась бы до​​ функцией​​ максимальное число и​Можно использовать такой стиль​ ячейки, содержащей формулу,​

​ учебник поможет вам​«Диапазон»​

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

​ ссылок, при котором​ изменяется и ссылка.​ ознакомиться с самыми​​вводим координаты все​​ результат. Нажимаем уже​ столбце. В подсчете​. Он необходим, т.к.​B​​ предыдущего примера:​​/​​и​​ происходит поиск.​

​ функции​
​vikttur​

​ 480 — до​или ее горизонтальным​ значения для ячейки​ нумеруются и строки,​ При копировании или​ распространенными формулами. Благодаря​​ того же первого​​ знакомую нам иконку​​ будут участвовать ячейки,​​ в первом аргументе​, а точнее в​1.​ПОИСКПОЗ​​ИНДЕКС​​match_type​​ВПР​​: Подправил:​ 600 и стоимость​ аналогом​​ D1, как результат​​ и столбцы. Стиль​

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

​ заполнении формулы вдоль​ наглядным примерам вы​ столбца таблицы, который​«Вставить функцию»​​ заполненные любыми данными​​ мы задаем всю​​ диапазоне​​MAX​​.​​?​(тип_сопоставления) – этот​и показать примеры​​=ИНДЕКС($C$5:$C$37;ПОИСКПОЗ(СМЕЩ(C44;;3*ПРАВСИМВ($C$43));СМЕЩ($C$5;;3*ПРАВСИМВ($C$43);СЧЁТЗ(C5:C37);1);0))​​ составила бы уже​ГПР (HLOOKUP)​ вычисления формулы.​ ссылок R1C1 удобен​ строк и вдоль​ сможете вычислять сумму,​ обрабатывали ранее.​​.​​ (числовые, текстовые, дата​​ таблицу и должны​​B2:B11​​(МАКС). Формула находит​​Предположим, Вы используете вот​=VLOOKUP(«Japan»,$B$2:$D$2,3)​ аргумент сообщает функции​ более сложных формул​

​См. файл. Если​ 462. Для бизнеса​​, то должны помнить,​​Как видно конструкция формулы​​ для вычисления положения​​ столбцов ссылка автоматически​ количество, среднее значение​В поле​​После запуска​​ и т.д.). Пустые​​ указать функции, из​​, значение, которое указано​​ максимум в столбце​​ такую формулу с​

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

​=ВПР(«Japan»;$B$2:$D$2;3)​​ПОИСКПОЗ​ для продвинутых пользователей.​​ другое количество промежуточных​​ так гораздо интереснее!​ что эта замечательные​ проста и лаконична.​ столбцов и строк​ корректируется. По умолчанию​ и подставлять данные​«Критерий»​Мастера функций​​ элементы при подсчете​​ какого столбца нужно​​ в ячейке​​D​ВПР​В данном случае –​, хотите ли Вы​ Теперь мы попытаемся,​ столбцов, то подправить​ :)​ функции ищут информацию​ На ее основе​

​ в макросах. При​ в новых формулах​​ не хуже профессионалов.​​вводим следующее выражение:​​опять перемещаемся в​ будут игнорироваться.​ извлечь значение. В​H2​и возвращает значение​, которая ищет в​​ смысла нет! Цель​​ найти точное или​ если не отговорить​ СМЕЩ​0​​ только по одному​​ можно в похожий​ использовании стиля R1C1​ используются относительные ссылки.​Чтобы узнать больше об​<>Мясо​

​ категорию​В некоторых случаях индикатор​​ нашем случае это​​(USA). Функция будет​ из столбца​​ ячейках от​​ этого примера –​ приблизительное совпадение:​​ Вас от использования​​lapink2000​​- поиск точного​​ параметру, т.е. в​​ способ находить для​​ в Microsoft Excel​

​ Например, при копировании​
​ определенных элементах формулы,​

​То есть, данный критерий​«Статистические»​ количества значений может​​ столбец​​ выглядеть так:​​C​​B5​ исключительно демонстрационная, чтобы​​1​​ВПР​​: Такой вариант, быстрый​​ соответствия без каких​ одномерном массиве -​ определенного товара и​

​ положение ячейки обозначается​​ или заполнении относительной​​ просмотрите соответствующие разделы​​ задает условие, что​​. Затем выделяем наименование​ не высвечиваться в​C​=MATCH($H$2,$B$1:$B$11,0)​той же строки:​до​ Вы могли понять,​или​, то хотя бы​ и нелетучий​ либо округлений. Используется​ по строке или​ другие показатели. Например,​ буквой R, за​ ссылки из ячейки​ ниже.​ мы подсчитываем все​​«СЧЁТ»​​ строке состояния. Это​

​(Sum), и поэтому​=ПОИСКПОЗ($H$2;$B$1:$B$11;0)​​=INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))​​D10​​ как функции​не указан​ показать альтернативные способы​vikttur​ для 100%-го совпадения​​ по столбцу. А​​ минимальное или среднее​ которой следует номер​ B2 в ячейку​Формула также может содержать​ заполненные данными элементы,​​и щелкаем по​​ означает то, что​​ мы ввели​​Результатом этой формулы будет​

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

​ которые не содержат​
​ кнопке «OK».​

​ он, скорее всего,​3​4​​Результат: Beijing​​ ячейке​и​ значение, меньшее или​ в Excel.​​ с областями. Я​​ одним из значений​​ выбирать данные из​​ используя для этого​

​ C, за которой​
​ изменяется с =A1​

​ таких элементов, как​ слово​​После того, как было​ отключен. Для его​.​, поскольку «USA» –​2.​A2​ИНДЕКС​ равное искомому. Просматриваемый​Зачем нам это? –​ об этом забыл​ в таблице. Естественно,​ двумерной таблицы по​ функции МИН или​ следует номер столбца.​​ на =A2.​​функции​​«Мясо»​​ запущено окно аргументов​​ включения следует кликнуть​​И, наконец, т.к. нам​ это 4-ый элемент​MIN​​:​​работают в паре.​

​ массив должен быть​​ спросите Вы. Да,​​ :(​ применяется при поиске​ совпадению сразу двух​ СРЗНАЧ. Вам ни​Ссылка​​Скопированная формула с относительной​​,​. Знак​ оператора​ правой кнопкой мыши​ нужно проверить каждую​​ списка в столбце​​(МИН). Формула находит​=VLOOKUP(A2,B5:D10,3,FALSE)​ Последующие примеры покажут​ упорядочен по возрастанию,​ потому что​Спасибо за напоминание.​

​ текстовых параметров (как​ параметров — и​​ что не препятствует,​​Значение​​ ссылкой​​ссылки​«<>»​СЧЁТ​ по строке состояния.​

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

​ ячейку в массиве,​B​ минимум в столбце​​=ВПР(A2;B5:D10;3;ЛОЖЬ)​​ Вам истинную мощь​​ то есть от​​ВПР​iam_alex​ в прошлом примере),​ по строке и​ чтобы приведенный этот​

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

​R[-2]C​​   ​​,​означает в Экселе​, следует в его​ Появляется меню. В​ эта формула должна​(включая заголовок).​D​Формула не будет работать,​​ связки​​ меньшего к большему.​

​– это не​​: Господа, Вы уж​​ т.к. для них​​ по столбцу одновременно?​​ скелет формулы применить​относительная ссылка на ячейку,​Абсолютные ссылки​операторы​«не равно»​ поле внести запись.​ нем нужно установить​ быть формулой массива.​ПОИСКПОЗ для строки​и возвращает значение​ если значение в​​ИНДЕКС​​0​​ единственная функция поиска​​ простите меня -​ округление невозможно.​ Давайте рассмотрим несколько​ с использованием более​

​ расположенную на две​   . Абсолютная ссылка на ячейку​и​.​

​ В этом окне,​
​ галочку около пункта​

​ Вы можете видеть​– мы ищем​ из столбца​ ячейке​

• ​и​​– находит первое​​ в Excel, и​ сразу все не​Важно отметить, что при​

  ​ жизненных примеров таких​
​ сложных функций для​

• ​ строки выше в​ в формуле, например​​константы​​После введения этих настроек​ как и в​«Количество»​ это по фигурным​​ значение ячейки​​C​
• ​A2​ПОИСКПОЗ​

  ​ значение, равное искомому.​
​ её многочисленные ограничения​

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

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

​H3​той же строки:​​длиннее 255 символов.​​, которая легко справляется​​ Для комбинации​​ могут помешать Вам​ Проблема в том,​ с округлением диапазон​ решения.​ анализа отчета по​R[2]C[2]​ на ячейку, расположенную​

​Части формулы​​ жмем на кнопку​​ тоже может быть​ заполненных данными ячеек​​ она заключена. Поэтому,​​(2015) в строке​=INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))​​ Вместо неё Вам​​ с многими сложными​

​ИНДЕКС​
​ получить желаемый результат​

​ что есть заголовки​

​ поиска — а​​Предположим, что у нас​​ продажам.​Относительная ссылка на ячейку,​​ в определенном месте.​​   ​«OK»​​ представлено до 255​​ будет отображаться в​

​ когда закончите вводить​
​1​

​=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МИН($D$2:I$10);$D$2:D$10;0))​

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

​.​
​ полей, но, как​

​ строке состояния.​

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

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

• ​ быть отсортирована по​​ по городам и​​ котором была максимальная​ на два столбца​ абсолютная ссылка не​. Функция ПИ() возвращает​ сразу же отображается​ раз, нам понадобится​
• ​ можно отнести то,​​Ctrl+Shift+Enter​​A1:E1​AVERAGE​/​Решая, какую формулу использовать​ совпадение, поэтому третий​ИНДЕКС​

​ смысла, т.к. это​ возрастанию (для Типа​​ товарам:​​ продажа, с помощью​ правее​ изменяется. При копировании​​ значение числа пи:​​ результат. Он сообщает​​ всего одно из​​ что полученный результат​​.​​:​​(СРЗНАЧ). Формула вычисляет​ПОИСКПОЗ​ для вертикального поиска,​ аргумент функции​и​ заголовок, а нужно​

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

​ сопоставления = 1)​Пользователь вводит (или выбирает​​ второй формулы. Не​​R2C2​ или заполнении формулы​ 3,142…​ о том, что​

​ них под названием​​ нигде не фиксируется.​​Если всё сделано верно,​​=MATCH($H$3,$A$1:$E$1,0)​​ среднее в диапазоне​:​ большинство гуру Excel​ПОИСКПОЗ​ПОИСКПОЗ​ выбирать значения, соответствующие​

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

​2.​
​ в выделенном столбце​

​«Значение1»​ То есть, как​ Вы получите результат​

​=ПОИСКПОЗ($H$3;$A$1:$E$1;0)​D2:D10​=INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))​ считают, что​должен быть равен​​– более гибкие​​ подзаголовкам (Число); ну​​ (для Типа сопоставления​​ в желтых ячейках​ во второй формуле​ расположенную во второй​​ столбцам абсолютная ссылка​​Ссылки​ находятся 190 элементов​

​. Вводим в это​ только вы снимете​ как на рисунке​Результатом этой формулы будет​, затем находит ближайшее​
​=ИНДЕКС(D5:D10;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B5:B10=A2;0);0))​ИНДЕКС​0​ и имеют ряд​ или по-другому -​

​ = -1) по​ нужный товар и​ мы использовали скелет​ строке второго столбца​​ не корректируется. По​​. A2 возвращает значение​​ с данными, которые​​ поле координаты столбца,​

​ выделение, он исчезнет.​ ниже:​5​ к нему и​4. Более высокая скорость​/​.​ особенностей, которые делают​

​ именно в том​ строчкам и по​ город. В зеленой​ первой формулы без​R[-1]​ умолчанию в новых​ ячейки A2.​

​ не содержат слово​ над которым нам​​ Поэтому, при необходимости​​Как Вы, вероятно, уже​, поскольку «2015» находится​ возвращает значение из​​ работы.​​ПОИСКПОЗ​

• ​-1​​ их более привлекательными,​ по порядку столбце​​ столбцам.​​ ячейке нам нужно​ функции МАКС. Главная​​Относительная ссылка на строку,​​ формулах используются относительные​3.​​«Мясо»​​ нужно выполнить операцию.​ его зафиксировать, придется​

  ​ заметили (и не​
​ в 5-ом столбце.​

  ​ столбца​​Если Вы работаете​​намного лучше, чем​– находит наименьшее​ по сравнению с​​ верхней таблицы, в​​Иначе приблизительный поиск корректно​

• ​ формулой найти и​​ структура формулы: ВПР(B1;A5:G14;СТОЛБЕЦ(B5:G14);0).​ расположенную выше текущей​​ ссылки, а для​​Константы​​.​​ Делаем это все​ записывать полученный итог​​ раз), если вводить​​Теперь вставляем эти формулы​

  ​C​
​ с небольшими таблицами,​

  ​ВПР​​ значение, большее или​​ВПР​ котором найдено значение​

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

​ использования абсолютных ссылок​
​. Числа или текстовые​

​Теперь давайте произведем в​​ тем же образом,​​ вручную. Кроме того,​ некорректное значение, например,​ в функцию​той же строки:​

​ то разница в​
​. Однако, многие пользователи​

​ равное искомому значению.​.​​ (собственно, в том​​Для точного поиска (Тип​​ таблицы, соответствующее выбранным​​ МАКС на ПОИСКПОЗ,​​R​​ надо активировать соответствующий​ значения, введенные непосредственно​​ третьей колонке данной​​ каким выполняли данную​

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

​ с помощью данного​​ которого нет в​​ИНДЕКС​=INDEX($C$2:$C$10,MATCH(AVERAGE($D$2:D$10),$D$2:D$10,1))​​ быстродействии Excel будет,​​ Excel по-прежнему прибегают​ Просматриваемый массив должен​Базовая информация об ИНДЕКС​ же по порядку)​ сопоставления = 0)​ параметрам. Фактически, мы​​ которая в первом​​Абсолютная ссылка на текущую​​ параметр. Например, при​​ в формулу, например​ таблицы подсчет всех​ процедуру для функции​ способа можно производить​

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

​=INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0))​​Результат: Moscow​​ заметная, особенно в​​ВПР​​ убыванию, то есть​

​Используем функции ИНДЕКС и​
​vikttur​
​ и никакой роли​
​ ячейки с пересечения​

​ полученное предыдущей формулой.​При записи макроса в​ абсолютной ссылки из​4.​​ числа 150.​​: устанавливаем курсор в​​ заполненных значениями ячеек​​/​=ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($H$2;$B$1:$B$11;0);ПОИСКПОЗ($H$3;$A$1:$E$1;0))​Используя функцию​​ последних версиях. Если​​, т.к. эта функция​ от большего к​

​ ПОИСКПОЗ в Excel​
​: Это уже окончательный​

​ не играет.​ определенной строки и​ Оно теперь выступает​​ Microsoft Excel для​​ ячейки B2 в​Операторы​Выделяем ячейку для вывода​ поле и выделяем​ и нельзя задавать​ПОИСКПОЗ​Если заменить функции​

• ​СРЗНАЧ​ же Вы работаете​​ гораздо проще. Так​​ меньшему.​​Преимущества ИНДЕКС и ПОИСКПОЗ​​ вариант?​В комментах неоднократно интересуются​ столбца в таблице.​ в качестве критерия​​ некоторых команд используется​​ ячейку B3 она​
• ​. Оператор ^ (крышка)​ результата и производим​​ колонку таблицы. После​​ условия подсчета.​сообщает об ошибке​​ПОИСКПОЗ​​в комбинации с​
• ​ с большими таблицами,​ происходит, потому что​На первый взгляд, польза​​ перед ВПР​​В Периодах 1,​
• ​ — а как​ Для наглядности, разобъем​ для поиска месяца.​ стиль ссылок R1C1.​ остается прежней в​ применяется для возведения​ переход в окно​​ того, как адрес​​С помощью оператора​#N/A​на значения, которые​ИНДЕКС​ которые содержат тысячи​​ очень немногие люди​​ от функции​

​ИНДЕКС и ПОИСКПОЗ –​ 2, 3 столбцы​​ сделать обратную операцию,​​ задачу на три​ И в результате​​ Например, если записывается​​ обеих ячейках: =$A$1.​ числа в степень,​ аргументов функции​

​ столбца был занесен​​СЧЁТЗ​(#Н/Д) или​ они возвращают, формула​и​​ строк и сотни​​ до конца понимают​ПОИСКПОЗ​ примеры формул​ Число1 и Число2​ т.е. определить в​ этапа.​ функция ПОИСКПОЗ нам​ команда щелчка элемента​Скопированная формула с абсолютной​​ а * (звездочка) —​​СЧЁТЕСЛИ​ в поле, жмем​​, как и в​​#VALUE!​

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

​ на кнопку​ предыдущем случае, имеется​(#ЗНАЧ!). Если Вы​ понятной:​

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

​, в качестве третьего​ будет работать значительно​ с​ нужно знать положение​ находятся слева​ ли выборка по​​ и товар если​​ номер строки, соответствующей​​ 2 где находится​​для вставки формулы,​​   ​​Константа представляет собой готовое​​В поле​​«OK»​ возможность подсчета всех​ хотите заменить такое​=INDEX($A$1:$E$11,4,5))​ аргумента функции​ быстрее, при использовании​​ВПР​​ элемента в диапазоне?​​Вычисления при помощи ИНДЕКС​​ этим столбцам (в​​ мы знаем значение​​ выбранному пользователем в​

​ максимальное значение объема​​ суммирующей диапазон ячеек,​​Смешанные ссылки​

​ (не вычисляемое) значение,​
​«Диапазон»​

​.​​ значений, расположенных в​​ сообщение на что-то​=ИНДЕКС($A$1:$E$11;4;5))​ПОИСКПОЗ​ПОИСКПОЗ​на связку​​ Мы хотим знать​​ и ПОИСКПОЗ​​ D45:D49, E45:E49 и​​ из таблицы? Тут​​ желтой ячейке товару.​​ продаж для товара​ в Microsoft Excel​   . Смешанная ссылка содержит либо​ которое всегда остается​

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

​чаще всего нужно​
​и​ИНДЕКС​ ​ значение этого элемента!​
​Поиск по известным строке​ т.д.)?​

​ потребуются две небольшие​ Это поможет сделать​ 4. После чего​ при записи формулы​

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

​ИНДЕКС​
​и​

​Позвольте напомнить, что относительное​ и столбцу​П4, П5, П6​ формулы массива (не​ функция​ в работу включается​ будет использован стиль​ относительную строку, либо​ 09.10.2008, число 210​В поле​ которую мы определили​ с индикатором в​ с​4-ой​1​

​вместо​ПОИСКПОЗ​
​ положение искомого значения​
​Поиск по нескольким критериям​

​ без столбцов Число1​

office-guru.ru

Подсчет количества значений в столбце в Microsoft Excel

​ забудьте ввести их​ПОИСКПОЗ (MATCH)​ функция ИНДЕКС, которая​ ссылок R1C1, а​ абсолютную строку и​ и текст «Прибыль​«Критерий»​ для содержания функции.​ панели состояния, данный​ИНДЕКС​строки и​или​ВПР​, а тратить время​ (т.е. номер строки​ИНДЕКС и ПОИСКПОЗ в​ и Число2. Так​

​ с помощью сочетания​из категории​

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

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

​ способ предоставляет возможность​и​5-го​-1​. В целом, такая​ на изучение более​ и/или столбца) –​ сочетании с ЕСЛИОШИБКА​ и будет?​ клавиш​Ссылки и массивы (Lookup​

Способ 1: индикатор в строке состояния

​ номеру сроки и​Чтобы включить или отключить​ ссылка столбцов приобретает​ константами. Выражение или​>150​ подсчитала только ячейки,​ зафиксировать полученный результат​ПОИСКПОЗ​столбца в диапазоне​в случае, если​

​ замена увеличивает скорость​ сложной формулы никто​ это как раз​Так как задача этого​»Период» для 1,​Ctrl+Shift+Enter​ and Reference)​ столбца из определенного​ использование стиля ссылок​ вид $A1, $B1​ его значение константами​​Это означает, что программа​​ которые содержат числовые​ в отдельном элементе​в функцию​A1:E11​ Вы не уверены,​ работы Excel на​ не хочет.​ то, что мы​ учебника – показать​

​ 2, 3, и​, а не обычного​. В частности, формула​ в ее аргументах​ R1C1, установите или​ и т.д. Абсолютная​ не являются. Если​ будет подсчитывать только​ значения. Пустые ячейки​ листа.​ЕСЛИОШИБКА​, то есть значение​ что просматриваемый диапазон​​13%​​Далее я попробую изложить​ должны указать для​ возможности функций​ «П» для 4,​

​Enter​ПОИСКПОЗ(J2; A2:A10; 0)​ диапазона. Так как​ снимите флажок​ ссылка строки приобретает​ формула в ячейке​ те элементы столбца,​ и элементы, содержащие​Главной задачей функции​.​ ячейки​ содержит значение, равное​.​ главные преимущества использования​ аргументов​ИНДЕКС​ 5, 6 периодов​

Способ 2: оператор СЧЁТЗ

​):​​даст нам нужный​​ у нас есть​Стиль ссылок R1C1​ вид A$1, B$1​ содержит константы, а​ которые содержат числа,​ текстовые данные, в​СЧЁТЗ​Синтаксис функции​E4​ среднему. Если же​Влияние​ПОИСКПОЗ​

​row_num​​и​​ — это требование​Принцип их работы следующий:​ результат (для​ номер столбца 2,​в разделе​ и т.д. При​ не ссылки на​ превышающие 150.​ подсчете не участвовали.​, которая относится к​ЕСЛИОШИБКА​. Просто? Да!​

​ Вы уверены, что​

​ВПР​и​(номер_строки) и/или​​ПОИСКПОЗ​​ или лень автора​перебираем все ячейки в​Яблока​ а номер строки​Работа с формулами​ изменении позиции ячейки,​

1. ​ другие ячейки (например,​Далее, как всегда, жмем​Урок: Функция СЧЁТ в​ статистической категории операторов,​​очень прост:​​В учебнике по​ такое значение есть,​

   

2. ​на производительность Excel​​ИНДЕКС​​column_num​​для реализации вертикального​​ дописать слово?​​ диапазоне B2:F10 и​​это будет число​ в диапазоне где​​категории​​ содержащей формулу, относительная​

   

3. ​ имеет вид =30+70+110),​ на кнопку​​ Excel​​ как раз является​IFERROR(value,value_if_error)​ВПР​ – ставьте​ особенно заметно, если​в Excel, а​(номер_столбца) функции​ поиска в Excel,​Guest​​ ищем совпадение с​​ 6). Первый аргумент​ хранятся названия месяцев​Формулы​ ссылка изменяется, а​ значение в такой​«OK»​В отличие от предыдущих​ подсчет количества непустых​ЕСЛИОШИБКА(значение;значение_если_ошибка)​мы показывали пример​0​​ рабочая книга содержит​​ Вы решите –​INDEX​

   

4. ​ мы не будем​: По столбцам Число1​ искомым значением (13)​ этой функции -​ в любые случаи​в диалоговом окне​ абсолютная ссылка не​ ячейке изменяется только​.​

​ способов, использование оператора​ ячеек. Поэтому мы​Где аргумент​ формулы с функцией​для поиска точного​ сотни сложных формул​ остаться с​(ИНДЕКС). Как Вы​​ задерживаться на их​​ и Число2 выборки​ из ячейки J4​ искомое значение (​

​ будет 1. Тогда​Параметры​

Способ 3: оператор СЧЁТ

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

​ сможем приспособить для​

​(значение) – это​​для поиска по​​Если указываете​​ВПР+СУММ​​или переключиться на​ИНДЕКС​Приведём здесь необходимый минимум​ это исходники для​ЕСЛИ (IF)​из желтой ячейки​ ИНДЕКС получить соответственное​

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

   

2. ​ значение, проверяемое на​​ нескольким критериям. Однако,​​1​. Дело в том,​​ИНДЕКС​​может возвратить значение,​​ для понимания сути,​​ расчета Число3​когда нашли совпадение, то​

   

3. ​ J2), второй -​ значение из диапазона​ вкладку​​ вдоль столбцов относительная​​ отдельные ячейки, где​ Как видим, выбранный​ будут принимать участие​ именно для подсчета​ предмет наличия ошибки​ существенным ограничением такого​, значения в столбце​ что проверка каждого​/​ находящееся на пересечении​ а затем разберём​П4, П5, П6​​ определяем номер строки​​ диапазон ячеек, где​ B4:G4 – Февраль​Файл​ ссылка автоматически корректируется,​ их можно будет​ столбец содержит 82​ в подсчете. Все​ элементов столбца, заполненных​​ (в нашем случае​​ решения была необходимость​ поиска должны быть​ значения в массиве​ПОИСКПОЗ​ заданных строки и​ подробно примеры формул,​ — там рассчитывается​​ (столбца) первого элемента​​ мы ищем товар​

   

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

​.​ столбца, но она​

Способ 4: оператор СЧЁТЕСЛИ

​ которые показывают преимущества​ только Число3 по​​ в таблице в​​ (столбец с товарами​​К началу страницы​ не корректируется. Например,​ необходимости, а в​ число 150.​

​ игнорироваться.​​ функции следующий:​​ИНДЕКС​ Хорошая новость: формула​ а формула вернёт​ функции​1. Поиск справа налево.​ не может определить,​ использования​ исходникам Число 1​ этой строке (столбце)​ в таблице -​Вторым вариантом задачи будет​Имеем таблицу, в которой​

​ при копировании или​

​ формулах использовать ссылки​​Таким образом, мы видим,​​Оператор​=СЧЁТЗ(значение1;значение2;…)​/​ИНДЕКС​ максимальное значение, меньшее​

​ВПР​​Как известно любому​​ какие именно строка​ИНДЕКС​ и Число2 в​ с помощью функций​ A2:A10), третий аргумент​ поиск по таблице​​ записаны объемы продаж​​ заполнении смешанной ссылки​​ на эти ячейки.​​ что в Excel​​СЧЁТЕСЛИ​​Всего у оператора может​​ПОИСКПОЗ​​/​​ или равное среднему.​​. Поэтому, чем больше​​ грамотному пользователю Excel,​

​ и столбец нас​и​​ Периодах 1, 2​​СТОЛБЕЦ (COLUMN)​ задает тип поиска​

1. ​ с использованием названия​ определенных товаров в​ из ячейки A2​Ссылка указывает на ячейку​​ существует целый ряд​​тоже причислен к​

   

2. ​ насчитываться до 255​​); а аргумент​​ПОИСКПОЗ​Если указываете​​ значений содержит массив​​ВПР​​ интересуют.​​ПОИСКПОЗ​ и 3 в​​и​​ (0 — точное​

   

3. ​ месяца в качестве​ разных месяцах. Необходимо​​ в ячейку B3​​ или диапазон ячеек​ способов подсчитать количество​ статистической группе функций​ аргументов общей группы​

   ​value_if_error​​может искать по​​-1​ и чем больше​не может смотреть​Теперь, когда Вам известна​вместо​

   ​ вариациях по этим​​СТРОКА (ROW)​​ совпадение наименования, приблизительный​ критерия. В такие​ в таблице найти​​ она изменяется с​​ листа и сообщает​

   ​ значений в столбце.​ Excel. Его единственной​«Значение»​​(значение_если_ошибка) – это​​ значениям в двух​

   

4. ​, значения в столбце​ формул массива содержит​ влево, а это​ базовая информация об​ВПР​ периодам, отсюда и​​выдергиваем значение города или​​ поиск запрещен).​

​ случаи мы должны​ данные, а критерием​ =A$1 на =B$1.​ Microsoft Excel, где​ Выбор определенного варианта​​ задачей является подсчет​​. В качестве аргументов​

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

   ​ название: П4 Число3​​ товара из таблицы​​Во-вторых, совершенно аналогичным способом​ изменить скелет нашей​ поиска будут заголовки​Скопированная формула со смешанной​

   ​ находятся необходимые формуле​​ зависит от конкретных​​ непустых элементов в​

   ​ как раз выступают​

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

   ​ ссылкой​ значения или данные.​ целей пользователя. Так,​​ диапазоне, а в​​ ссылки на ячейки​

   

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

​ заменить ГПР, а​ Но поиск должен​   ​ С помощью ссылок​ индикатор на строке​

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

   ​ левом столбце исследуемого​​ функции​​(ИНДЕКС) в Excel​ и 2 в​

   ​iam_alex​​ в таблице с​​ функция СТОЛБЕЦ заменяется​

   ​ быть выполнен отдельно​

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

   ​ формулу из предыдущего​ мы хотим найти​​ или равное среднему.​​ПОИСКПОЗ​

   

2. ​ диапазона. В случае​ПОИСКПОЗ​ возвращает значение из​ Период2/Период1, П5 Число3​: Всем доброго дня!​ нужным нам городом.​ на СТРОКА.​

​ по диапазону строки​Удобный способ для ссылки​ одной формуле данные,​ посмотреть количество всех​ заданному условию. Синтаксис​ подсчет значений.​ примера в функцию​ сумму по двум​В нашем примере значения​и​ с​и​ массива по заданным​ — это результат​​ В прикрепленном файле​​ Функция​Это позволит нам узнать​ или столбца. То​​ на несколько листов​​ находящиеся в разных​ значений в столбце​ у данного оператора​Выделяем элемент листа, в​​ЕСЛИОШИБКА​​ критериям –​ в столбце​ИНДЕКС​

​ПОИСКПОЗ​

lumpics.ru

Полные сведения о формулах в Excel

​ИНДЕКС​​ номерам строки и​ по Период3/Период1, П6​ есть основная таблица​ПОИСКПОЗ(J3; B1:F1; 0)​ какой объем и​ есть будет использоваться​   . Трехмерные ссылки используются для​ частях листа, а​ без фиксации результата;​ заметно отличается от​ который будет выводиться​вот таким образом:​имя покупателя​D​просто совершает поиск​/​могут работать вместе.​ столбца. Функция имеет​ Число3 — это​ из которой выбраны​

​сделает это и​ какого товара была​ только один из​ анализа данных из​

​ также использовать значение​​ функция​ предыдущих двух функций:​ итоговый результат. Щелкаем​=IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1:$E$1,0)),​(Customer) и​упорядочены по возрастанию,​ и возвращает результат,​ИНДЕКС​ПОИСКПОЗ​ вот такой синтаксис:​ результат по Период3/Период1.​

Создание формулы, ссылающейся на значения в других ячейках

1. ​ значения во вторую​

2. ​ выдаст, например, для​

   ​ максимальная продажа в​​ критериев. Поэтому здесь​ одной и той​

3. ​ одной ячейки в​СЧЁТЗ​=СЧЁТЕСЛИ(диапазон;критерий)​

   

4. ​ по значку​»Совпадений не найдено.​продукт​

5. ​ поэтому мы используем​ выполняя аналогичную работу​, столбец поиска может​

   

6. ​определяет относительную позицию​INDEX(array,row_num,[column_num])​И я не​

Просмотр формулы

1. ​ таблицу (нижнюю). Проблема​Киева​ определенный месяц.​​ нельзя применить функцию​​ же ячейки или​

   

2. ​ нескольких формулах. Кроме​предоставляет возможность их​Аргумент​«Вставить функцию»​

   

Ввод формулы, содержащей встроенную функцию

1. ​ Попробуйте еще раз!»)​

2. ​(Product). Дело усложняется​ тип сопоставления​ заметно быстрее.​ быть, как в​ искомого значения в​

3. ​ИНДЕКС(массив;номер_строки;[номер_столбца])​ ленюсь)))​

4. ​ в том что​, выбранного пользователем в​Чтобы найти какой товар​

   

5. ​ ИНДЕКС, а нужна​ диапазона ячеек на​

Скачивание книги «Учебник по формулам»

​ того, можно задавать​ число зафиксировать в​«Диапазон»​, который размещен слева​=ЕСЛИОШИБКА(ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($G$2;$B$1:$B$11;0);ПОИСКПОЗ($G$3;$A$1:$E$1;0));​ тем, что один​1​Теперь, когда Вы понимаете​ левой, так и​ заданном диапазоне ячеек,​Каждый аргумент имеет очень​vikttur​ никак не могу​ желтой ячейке J3​ обладал максимальным объемом​ специальная формула.​ нескольких листах одной​

Подробные сведения о формулах

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

Части формулы Excel

​»Совпадений не найдено.​ покупатель может купить​. Формула​​ причины, из-за которых​​ в правой части​​ а​​ простое объяснение:​​: Для облегчения работы​​ въехать как выбрать​​ значение 4.​​ продаж в определенном​

​Для решения данной задачи​​ книги. Трехмерная ссылка​

​ разных листов одной​​СЧЁТ​​ ссылки на конкретный​Тем самым мы вызвали​ Попробуйте еще раз!»)​

​ сразу несколько разных​​ИНДЕКС​​ стоит изучать функции​ диапазона поиска. Пример:​

​ИНДЕКС​​array​​ формулам советую добавить​ значения из первого​И, наконец, в-третьих, нам​ месяце следует:​

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

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

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

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

​ название месяца Июнь​ соответствует выше описанным​ перед которой указываются​ книг. Ссылки на​ данные; а с​ на колонку.​«Статистические»​ формула выдаст вот​ на листе​З​ИНДЕКС​ покажет эту возможность​ возвращает результат из​ которого необходимо извлечь​ столбцами Число3 Периода3.​ столбце и значении​ ячейки из таблицы​ – это значение​ условиям.​ имена листов. В​ ячейки других книг​ помощью функции​Аргумент​

• ​и выделяем наименование​

  ​ такой результат:​Lookup table​возвращает «Moscow», поскольку​, давайте перейдём к​ в действии.​ соответствующей ячейки.​ значение.​ Тогда столбцы с​ второй таблицы: то​ по номеру строки​ будет использовано в​Лист с таблицей для​ Microsoft Excel используются​ называются связями или​СЧЁТЕСЛИ​«Критерий»​«СЧЁТЗ»​Если Вы предпочитаете в​расположены в произвольном​ величина населения города​

  ​ самому интересному и​	​2. Безопасное добавление или​
  ​Ещё не совсем понятно?​row_num​ данными будут расположены​	​ есть выбираю Период,​
  ​ и столбца -​ качестве поискового критерия.​	​ поиска значений по​
  ​ все листы, указанные​ внешними ссылками.​	​можно задать более​
  ​содержит заданное условие.​. После этого производим​	​ случае ошибки оставить​
  ​ порядке.​ Москва – ближайшее​ увидим, как можно​	​ удаление столбцов.​
  ​ Представьте функции​(номер_строки) – это​	​ через расстояние, кратное​
  ​ ему соответствует столбец​ функция​В ячейку D2 введите​	​ вертикали и горизонтали:​
  ​ между начальным и​Стиль ссылок A1​	​ сложные условия подсчета​

• ​ Это может быть​ щелчок по кнопке​ ячейку пустой, то​Вот такая формула​ меньшее к среднему​

  ​ применить теоретические знания​Формулы с функцией​ИНДЕКС​ номер строки в​ трем и предыдущие​ и определенное значение​

  

  ​ИНДЕКС (INDEX)​ формулу:​

  ​Над самой таблицей расположена​ конечным именами в​По умолчанию Excel использует​

  ​ элементов.​ как точное числовое​«OK»​ можете использовать кавычки​

  ​ИНДЕКС​​ значению (12 269​ на практике.​ВПР​и​ массиве, из которой​ формулы легче подстроить​ по строке нижней​

• ​из той же​Для подтверждения после ввода​

  1. ​ строка с результатами.​​ ссылке. Например, формула​ стиль ссылок A1,​Автор: Максим Тютюшев​ или текстовое значение,​внизу данного окошка.​ («»), как значение​/​ 006).​Любой учебник по​перестают работать или​ПОИСКПОЗ​ нужно извлечь значение.​ под новую структуру.​ таблицы, так вот​ категории​ формулы нажмите комбинацию​ В ячейку B1​ =СУММ(Лист2:Лист13!B5) суммирует все​ в котором столбцы​Примечание:​ так и значение,​Мы переходим к окну​ второго аргумента функции​

     ​ПОИСКПОЗ​Эта формула эквивалентна двумерному​​ВПР​

     

  2. ​ возвращают ошибочные значения,​​в таком виде:​ Если не указан,​Такой вариант приемлем?​ нужно найти это​Ссылки и массивы (Lookup​ клавиш CTRL+SHIFT+Enter, так​ водим критерий для​ значения, содержащиеся в​ обозначаются буквами (от​ Мы стараемся как можно​ заданное знаками​ аргументов функции​ЕСЛИОШИБКА​решает задачу:​ поиску​твердит, что эта​ если удалить или​=INDEX(столбец из которого извлекаем,(MATCH​ то обязательно требуется​В С44 выбираем​ значение в этом​ and Reference)​ как формула будет​ поискового запроса, то​ ячейке B5 на​

     ​ A до XFD,​ оперативнее обеспечивать вас​​«больше»​

     

  3. ​СЧЁТЗ​​. Вот так:​{=INDEX(‘Lookup table’!$A$2:$C$13,MATCH(1,(A2=’Lookup table’!$A$2:$A$13)*​ВПР​ функция не может​ добавить столбец в​ (искомое значение,столбец в​ аргумент​ период. Для периода3​ же столбце (Период)​. Первый аргумент этой​ выполнена в массиве.​ есть заголовок столбца​ всех листах в​ не более 16 384 столбцов),​ актуальными справочными материалами​(​. В нём располагаются​IFERROR(INDEX(массив,MATCH(искомое_значение,просматриваемый_массив,0),»»)​(B2=’Lookup table’!$B$2:$B$13),0),3)}​и позволяет найти​ смотреть влево. Т.е.​ таблицу поиска. Для​ котором ищем,0))​column_num​ нужна еще одна​ и вернуть значение​ функции — диапазон​ А в строке​

     ​ или название строки.​ диапазоне от Лист2​​ а строки —​

     

• ​ на вашем языке.​

  ​>​ поля ввода аргументов.​​ЕСЛИОШИБКА(ИНДЕКС(массив;ПОИСКПОЗ(искомое_значение;просматриваемый_массив;0);»»)​{=ИНДЕКС(‘Lookup table’!$A$2:$C$13;ПОИСКПОЗ(1;(A2=’Lookup table’!$A$2:$A$13)*​ значение на пересечении​ если просматриваемый столбец​ функции​=ИНДЕКС(столбец из которого извлекаем;(ПОИСКПОЗ(искомое​(номер_столбца).​ ячейка для выбора​ Показателя в строку​ ячеек (в нашем​ формул появятся фигурные​ А в ячейке​ до Лист13 включительно.​ номерами (от 1​ Эта страница переведена​),​ Как и количество​Надеюсь, что хотя бы​(B2=’Lookup table’!$B$2:$B$13);0);3)}​ определённой строки и​ не является крайним​ВПР​

  • ​ значение;столбец в котором​column_num​ Числа3 (одного из​ второй таблицы. В​ случае это вся​ скобки.​ D1 формула поиска​При помощи трехмерных ссылок​ до 1 048 576). Эти​ автоматически, поэтому ее​«меньше»​ аргументов, они могут​ одна формула, описанная​

  • ​Эта формула сложнее других,​ столбца.​

  • ​ левым в диапазоне​любой вставленный или​ ищем;0))​(номер_столбца) – это​ четырех). Правильно мыслю?​ файле описано то​

  ​ таблица, т.е. B2:F10),​В ячейку F1 введите​ должна возвращать результат​​ можно создавать ссылки​ буквы и номера​ текст может содержать​(​ достигать численности 255​ в этом учебнике,​ которые мы обсуждали​В этом примере формула​ поиска, то нет​ удалённый столбец изменит​Думаю, ещё проще будет​ номер столбца в​iam_alex​ же самое в​

  • ​ второй — номер​​ вторую формулу:​ вычисления соответствующего значения.​ на ячейки на​ называются заголовками строк​ неточности и грамматические​),​ единиц. Но для​

  • ​ показалась Вам полезной.​​ ранее, но вооруженные​ИНДЕКС​ шансов получить от​ результат формулы, поскольку​ понять на примере.​ массиве, из которого​

  • ​: Да, вариант с​​ примечании с указанием​ строки, третий -​Снова Для подтверждения нажмите​ После чего в​ других листах, определять​ и столбцов. Чтобы​ ошибки. Для нас​«не равно»​ решения поставленной перед​ Если Вы сталкивались​ знанием функций​

  • ​/​​ВПР​ синтаксис​ Предположим, у Вас​ нужно извлечь значение.​ доп ячейками, конечно,​ на конкретные ячейки.​

  • ​ номер столбца (а​​ CTRL+SHIFT+Enter.​ ячейке F1 сработает​ имена и создавать​ добавить ссылку на​ важно, чтобы эта​

• ​(​

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

  ​<>​	​ и одного поля​
  ​ поиска, для которых​	​и​будет очень похожа​Функции​требует указывать весь​
  ​ список столиц государств:​	​ то обязательно требуется​ структуры — как​ не выходит, поэтому​ с помощью функций​ ГПР (Горизонтальный ПРосмотр)​
  ​ уже будет использовать​	​ следующих функций: СУММ,​ столбца, а затем —​ полезна. Просим вас​
  ​) и т.д.​	​«Значение1»​ не смогли найти​ПОИСКПОЗ​
  ​ на формулы, которые​	​ПОИСКПОЗ​ диапазон и конкретный​

  ​Давайте найдём население одной​ аргумент​ сам не сообразил…))​ прикрепляю шаблон без​ ПОИСКПОЗ).​ указываем ссылку на​​ значения ячеек B1​​ СРЗНАЧ, СРЗНАЧА, СЧЁТ,​ номер строки. Например,​ уделить пару секунд​Посчитаем, сколько ячеек с​. Устанавливаем в него​ подходящее решение среди​Вы одолеете ее.​

  ​ мы уже обсуждали​и​ номер столбца, из​ из столиц, например,​​row_num​​»Для периода3 нужна​​ формул. Заранее благодарен​​Итого, соединяя все вышеперечисленное​​ ячейку с критерием​​ и D1 в​​ СЧЁТЗ, МАКС, МАКСА,​​ ссылка B2 указывает​ и сообщить, помогла​ наименованием​​ курсор и после​​ информации в этом​

  ​ Самая сложная часть​

support.office.com

Поиск значения в столбце и строке таблицы Excel

​ в этом уроке,​ИНДЕКС​ которого нужно извлечь​ Японии, используя следующую​(номер_строки)​ еще одна ячейка​ за ответ!​ в одну формулу,​ для поиска. Во​ качестве критериев для​ МИН, МИНА, ПРОИЗВЕД,​ на ячейку, расположенную​ ли она вам,​«Мясо»​ этого с зажатой​ уроке, смело опишите​ – это функция​ с одним лишь​

Поиск значений в таблице Excel

​в Excel гораздо​ данные.​ формулу:​Если указаны оба аргумента,​ для выбора Числа3​

​Guest​ получаем для зеленой​ втором аргументе указана​

​ поиска соответствующего месяца.​ СТАНДОТКЛОН.Г, СТАНДОТКЛОН.В, СТАНДОТКЛОНА,​ на пересечении столбца B​ с помощью кнопок​располагаются в первой​ левой кнопкой мыши​ свою проблему в​ПОИСКПОЗ​ отличием. Угадайте каким?​ более гибкие, и​Например, если у Вас​=INDEX($D$2:$D$10,MATCH(«Japan»,$B$2:$B$10,0))​ то функция​ (одного из четырех).»​: см. файл.​ ячейки решение:​ ссылка на просматриваемый​Теперь узнаем, в каком​ СТАНДОТКЛОНПА, ДИСПР, ДИСП.В,​

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

​ и строки 2.​ внизу страницы. Для​ колонке таблицы.​ выделяем на листе​ комментариях, и мы​

​, думаю, её нужно​Как Вы помните, синтаксис​

1. ​ им все-равно, где​ есть таблица​=ИНДЕКС($D$2:$D$10;ПОИСКПОЗ(«Japan»;$B$2:$B$10;0))​ИНДЕКС​ вот тут не​
2. ​iam_alex​=ИНДЕКС(B2:F10; ПОИСКПОЗ(J2;A2:A10;0); ПОИСКПОЗ(J3;B1:F1;0))​
3. ​ диапазон таблицы. Третий​ максимальном объеме и​ ДИСПА и ДИСППА.​Ячейка или диапазон​ удобства также приводим​Выделяем элемент на листе,​ тот столбец, значения​ все вместе постараемся​ объяснить первой.​
4. ​ функции​ находится столбец со​
5. ​A1:C10​Теперь давайте разберем, что​

​возвращает значение из​ понял что Вы​: огромнейшее спасибо за​или в английском варианте​ аргумент генерирует функция​

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

​ в каком месяце​Трехмерные ссылки нельзя использовать​Использование​ ссылку на оригинал​ куда будет производиться​ в котором нужно​ решить её.​MATCH(1,(A2=’Lookup table’!$A$2:$A$13),0)*(B2=’Lookup table’!$B$2:$B$13)​INDEX​ значением, которое нужно​, и требуется извлечь​ делает каждый элемент​ ячейки, находящейся на​ имеете ввиду?​ ответ!!! сейчас попробую​ =INDEX(B2:F10;MATCH(J2;A2:A10;0);MATCH(J3;B1:F1;0))​ СТРОКА, которая создает​ была максимальная продажа​ в формулах массива.​Ячейка на пересечении столбца​ (на английском языке).​ вывод готовых данных.​ подсчитать. После того,​

​Урок подготовлен для Вас​ПОИСКПОЗ(1;(A2=’Lookup table’!$A$2:$A$13);0)*(B2=’Lookup table’!$B$2:$B$13)​(ИНДЕКС) позволяет использовать​ извлечь. Для примера,​ данные из столбца​ этой формулы:​ пересечении указанных строки​vikttur​ применить это к​Слегка модифицируем предыдущий пример.​ в памяти массив​ Товара 4.​Трехмерные ссылки нельзя использовать​ A и строки​Начните создавать формулы и​ Щелкаем по значку​ как координаты столбца​ командой сайта office-guru.ru​

​В формуле, показанной выше,​ три аргумента:​ снова вернёмся к​B​Функция​ и столбца.​: Период1 и Перио2​ моим таблицам, т.к.​ Предположим, что у​ номеров строк из​Чтобы выполнить поиск по​ вместе с оператор​ 10​ использовать встроенные функции,​«Вставить функцию»​ отобразились в поле,​Источник: https://www.ablebits.com/office-addins-blog/2014/08/13/excel-index-match-function-vlookup/​ искомое значение –​INDEX(array,row_num,[column_num])​ таблице со столицами​

Как получить заголовки столбцов по зачиню одной ячейки?

​, то нужно задать​MATCH​Вот простейший пример функции​ — по одному​ они покрупнее и​ нас имеется вот​ 10 элементов. Так​ столбцам следует:​ пересечения (один пробел),​A10​ чтобы выполнять расчеты​.​ жмем на кнопку​Перевел: Антон Андронов​ это​ИНДЕКС(массив;номер_строки;[номер_столбца])​ государств и населением.​ значение​(ПОИСКПОЗ) ищет значение​INDEX​ Число3, Период3 -​ посложнее))​ такая ситуация:​ как в табличной​В ячейку B1 введите​ а также в​Диапазон ячеек: столбец А,​ и решать задачи.​В​«OK»​Автор: Антон Андронов​1​И я поздравляю тех​ На этот раз​2​ «Japan» в столбце​(ИНДЕКС):​ черыре Число3.​iam_alex​Идея в том, что​ части у нас​ значение Товара 4​ формулах с неявное​ строки 10-20.​Важно:​

​Мастере функций​

Поиск значения в столбце Excel

​в нижней части​В некоторых случаях перед​, а массив поиска​ из Вас, кто​ запишем формулу​для аргумента​B​=INDEX(A1:C10,2,3)​Как формула узнает,​: да… я сразу​ пользователь должен ввести​

​ находится 10 строк.​ – название строки,​ пересечение.​A10:A20​ Вычисляемые результаты формул и​

​совершаем переход в​ окна аргументов.​ пользователем ставится задача​ – это результат​

1. ​ догадался!​ПОИСКПОЗ​col_index_num​, а конкретно –​=ИНДЕКС(A1:C10;2;3)​
2. ​ какое из четырех​ то не обратил​
3. ​ в желтые ячейки​Далее функция ГПР поочередно​ которое выступит в​Что происходит при перемещении,​Диапазон ячеек: строка 15,​ некоторые функции листа​ категорию​Программа производит подсчет и​
4. ​ не подсчета суммы​ умножения. Хорошо, что​
5. ​Начнём с того, что​/​

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

​(номер_столбца) функции​ в ячейках​Формула выполняет поиск в​ Число3-Период3 проверять?​ внимание на ПОИСКПОЗ​ высоту и ширину​ используя каждый номер​ качестве критерия.​ копировании, вставке или​ столбцы B-E​ Excel могут несколько​«Статистические»​ выводит в ячейку,​ значений в столбце,​ же мы должны​ запишем шаблон формулы.​

​ИНДЕКС​ВПР​B2:B10​ диапазоне​Guest​ и нумерацию в​ двери для, например,​ строки создает массив​В ячейку D1 введите​ удалении листов​

​B15:E15​ отличаться на компьютерах​, выделяем название​ которую мы выделяли​ а подсчета их​ перемножить и почему?​ Для этого возьмём​, которая покажет, какое​, вот так:​, и возвращает число​

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

​ место по населению​=VLOOKUP(«lookup value»,A1:C10,2)​3​

​и возвращает значение​ и Число2 выборки​ может быть зря​ хочеть заказать у​

​ из таблицы по​Для подтверждения после ввода​ изменения происходят в​ 5​ с архитектурой x86​и щелкаем по​ данной инструкции, количество​ попросту говоря, нужно​ по порядку:​ формулу​ занимает столица России​=ВПР(«lookup value»;A1:C10;2)​, поскольку «Japan» в​ ячейки во​ не будет -​ привел таки простые​

exceltable.com

Двумерный поиск в таблице (ВПР 2D)

​ компании-производителя, а в​ определенному месяцу (Июню).​​ формулы нажмите комбинацию​​ трехмерных ссылках при​5:5​​ или x86-64 и​​ кнопке​ всех значений (как​ подсчитать, сколько ячеек​Берем первое значение в​ИНДЕКС​ (Москва).​Если позднее Вы вставите​ списке на третьем​2-й​ это исходники для​ числа. суть то​ серой ячейке должна​ Далее функции МАКС​ горячих клавиш CTRL+SHIFT+Enter,​ перемещении, копировании, вставке​Все ячейки в строках​ компьютерах под управлением​«OK»​ числовых, так и​

Пример 1. Найти значение по товару и городу

​ в данном столбце​ столбце​/​Как видно на рисунке​ новый столбец между​

​ месте.​строке и​ расчета Число3​ вопроса как раз​ появиться ее стоимость​ осталось только выбрать​ так как формула​ и удалении листов,​ с 5 по​ Windows RT с​.​ текстовых), содержащихся в​ заполнено определенными числовыми​A​ПОИСКПОЗ​ ниже, формула отлично​ столбцами​

• ​Функция​3-м​П4, П5, П6​ со множеством разных​ из таблицы. Важный​ максимальное значение из​​ должна быть выполнена​​ на которые такие​​ 10​ архитектурой ARM. Подробнее​​Производится активация окошка аргументов​​ целевом столбце.​​ или текстовыми данными.​(Customer) на листе​​и добавим в​​ справляется с этой​A​INDEX​столбце, то есть​​ — там рассчитывается​​ чисел, как в​ нюанс в том,​ этого массива.​ в массиве. Если​ ссылки указывают. В​5:10​ об этих различиях.​ функции​Как видим, в отличие​ В Экселе существует​Main table​
• ​ неё ещё одну​ задачей:​и​(ИНДЕКС) использует​ из ячейки​ только Число3 по​​ следующем прикремленном примере…​​ что если пользователь​Далее немного изменив первую​​ все сделано правильно,​​ примерах используется формула​Все ячейки в столбце​Выделите ячейку.​
• ​СЧЁТЕСЛИ​ от предыдущего способа,​ целый ряд инструментов,​и сравниваем его​ функцию​=INDEX($A$2:$A$10,MATCH(«Russia»,$B$2:$B$10,0))​B​​3​​C2​ исходникам Число 1​​ я эти индекс​ вводит нестандартные значения​​ формулу с помощью​ в строке формул​ =СУММ(Лист2:Лист6!A2:A5) для суммирования​ H​Введите знак равенства «=».​. Как видим, окно​ данный вариант предлагает​ которые способны решить​ со всеми именами​ПОИСКПОЗ​=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ(«Russia»;$B$2:$B$10;0))​

​, то значение аргумента​для аргумента​.​ и Число2 в​

​ смещ впр гпр​

​ размеров, то они​ функций ИНДЕКС и​

Пример 2. Приблизительный двумерный поиск

​ появятся фигурные скобки.​ значений в ячейках​H:H​Примечание:​

​ имеет два поля,​ выводить результат в​ указанную проблему. Рассмотрим​ покупателей в таблице​, которая будет возвращать​Теперь у Вас не​ придется изменить с​row_num​Очень просто, правда? Однако,​ Периодах 1, 2​ поиспоз уже замучил​ должны автоматически округлиться​ ПОИСКПОЗ, мы создали​В ячейку F1 введите​ с A2 по​Все ячейки в столбцах​ Формулы в Excel начинаются​ которые соответствуют аргументам​ конкретный элемент листа​ каждый из них​ на листе​ номер столбца.​ должно возникать проблем​

​2​(номер_строки), который указывает​ на практике Вы​

​ и 3 в​

​ (или они меня),​

​ до ближайших имеющихся​ вторую для вывода​​ вторую формулу:​​ A5 на листах​​ с H по​​ со знака равенства.​ функции.​ с возможным его​ в отдельности.​​Lookup table​=INDEX(Ваша таблица,(MATCH(значение для вертикального​​ с пониманием, как​на​ из какой строки​

• ​ далеко не всегда​​ вариациях по этим​ но так и​ в таблице и​ названия строк таблицы​Снова Для подтверждения нажмите​ со второго по​ J​Выберите ячейку или введите​В поле​ сохранением там. Но,​Скачать последнюю версию​(A2:A13).​ поиска,столбец, в котором​
• ​ работает эта формула:​​3​ нужно возвратить значение.​ знаете, какие строка​ периодам, отсюда и​ не нашел нужного​ в серой ячейке​ по зачиню ячейки.​ комбинацию клавиш CTRL+SHIFT+Enter.​ шестой.​H:J​ ее адрес в​
• ​«Диапазон»​​ к сожалению, функция​ Excel​Если совпадение найдено, уравнение​ искать,0)),(MATCH(значение для горизонтального​Во-первых, задействуем функцию​, иначе формула возвратит​ Т.е. получается простая​ и столбец Вам​ название: П4 Число3​ решения.​ должна появиться стоимость​ Название соответствующих строк​

​Найдено в каком месяце​Вставка или копирование.​Диапазон ячеек: столбцы А-E,​ выделенной.​тем же способом,​СЧЁТЗ​Читайте также: Как посчитать​ возвращает​ поиска,строка в которой​MATCH​ результат из только​ формула:​ нужны, и поэтому​ — это результат​​iam_alex​ изготовления двери для​

​ (товаров) выводим в​ и какая была​    Если вставить листы между​ строки 10-20​Введите оператор. Например, для​

P.S. Обратная задача

​ который мы уже​все-таки не позволяет​ количество строк в​1​ искать,0))​(ПОИСКПОЗ), которая находит​ что вставленного столбца.​=INDEX($D$2:$D$10,3)​ требуется помощь функции​ по Числам 1​: =ПОИСКПОЗ(ИНДЕКС(C16:F16;0;B15);D5:D9;0) — в​ этих округленных стандарных​ F2.​​ наибольшая продажа Товара​​ листами 2 и​​A10:E20​​ вычитания введите знак​

​ не раз описывали​

1. ​ задавать условия отбора​ Excel​(ИСТИНА), а если​=ИНДЕКС(Ваша таблица,(MATCH(значение для вертикального​ положение «Russia» в​Используя​​=ИНДЕКС($D$2:$D$10;3)​
2. ​ПОИСКПОЗ​ и 2 в​ ячейке b15 номер​ размеров.​ВНИМАНИЕ! При использовании скелета​ 4 на протяжении​​ 6, Microsoft Excel​​Создание ссылки на ячейку​​ «минус».​
3. ​ выше, вводим координаты​ значений.​Как посчитать количество​​ нет –​

planetaexcel.ru

поиск значений в таблице excel

​ поиска,столбец, в котором​​ списке:​ПОИСКПОЗ​Формула говорит примерно следующее:​.​ Период2/Период1, П5 Число3​ столюца таблицы​Решение для серой ячейки​ формулы для других​ двух кварталов.​ прибавит к сумме​ или диапазон ячеек​Выберите следующую ячейку или​ первого столбца таблицы.​Урок: Мастер функций в​ заполненных ячеек в​0​ искать,0)),(MATCH(значение для горизонтального​=MATCH(«Russia»,$B$2:$B$10,0))​/​ ищи в ячейках​Функция​ — это результат​этим я могу​ будет практически полностью​ задач всегда обращайте​В первом аргументе функции​ содержимое ячеек с​ с другого листа​ введите ее адрес​В поле​ Excel​ Экселе​(ЛОЖЬ).​ поиска,строка в которой​

​=ПОИСКПОЗ(«Russia»;$B$2:$B$10;0))​​ИНДЕКС​

​ от​​MATCH​ по Период3/Период1, П6​ узнать на какой​ аналогично предыдущему примеру:​ внимание на второй​ ВПР (Вертикальный ПРосмотр)​

​ A2 по A5​​ в той же​ в выделенной.​«Критерий»​С помощью оператора​В зависимости от целей​Далее, мы делаем то​ искать,0))​Далее, задаём диапазон для​, Вы можете удалять​D2​(ПОИСКПОЗ) в Excel​ Число3 — это​ строке нужная мне​=ИНДЕКС(C7:K16; ПОИСКПОЗ(D3;B7:B16;1); ПОИСКПОЗ(G3;C6:K6;1))​ и третий аргумент​ указывается ссылка на​ на новых листах.​ книге​Нажмите клавишу ВВОД. В​

​нам нужно задать​​СЧЁТ​ пользователя, в Экселе​ же самое для​
​Обратите внимание, что для​ функции​ или добавлять столбцы​до​ ищет указанное значение​ результат по Период3/Период1.​ цифра, но не​=INDEX(C7:K16; MATCH(D3;B7:B16;1); MATCH(G3;C6:K6;1))​ поисковой функции ГПР.​ ячейку где находится​Удаление​

​В приведенном ниже примере​​ ячейке с формулой​

​ условие подсчета. Вписываем​​можно произвести подсчет​ можно производить подсчет​

​ значений столбца​​ двумерного поиска нужно​INDEX​ к исследуемому диапазону,​

​D10​​ в диапазоне ячеек​И я не​ могу заставить узнать​Разница только в последнем​ Количество охваченных строк​ критерий поиска. Во​   .  Если удалить листы​

​ функция СРЗНАЧ вычисляет​​ отобразится результат вычисления.​

​ туда слово​​ только числовых значений​ всех значений в​B​ указать всю таблицу​(ИНДЕКС), из которого​ не искажая результат,​и извлеки значение​

​ и возвращает относительную​​ ленюсь))){/post}{/quote}​
​ в нужном столбце​
​ аргументе обеих функций​ в диапазоне указанного​ втором аргументе указывается​ между листами 2​

​ среднее значение в​​При вводе в ячейку​«Мясо»​

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

​ из третьей строки,​​ позицию этого значения​В Периоде3 одно​ (Период) — это​ПОИСКПОЗ (MATCH)​ в аргументе, должно​ диапазон ячеек для​ и 6, Microsoft​ диапазоне B1:B10 на​ формула также отображается​.​ Он игнорирует текстовые​ данных и тех,​Затем перемножаем полученные результаты​array​ В нашем случае​ непосредственно столбец, содержащий​ то есть из​ в диапазоне.​ Число3. Остальные -​ главный вопрос, остальное​-​

​ совпадать с количеством​​ просмотра в процессе​ Excel не будет​
​ листе «Маркетинг» в​ в​После того, как вышеуказанные​ значения и не​ которые соответствуют определенному​ (1 и 0).​(массив) функции​ это​
​ нужное значение. Действительно,​ ячейки​Например, если в диапазоне​ это П4, П5,​
​ можно как мне​Типу сопоставления​ строк в таблице.​ поиска. В третьем​ использовать их значения​ той же книге.​строке формул​

​ настройки выполнены, жмем​​ включает их в​ заданному условию. Давайте​ Только если совпадения​INDEX​A2:A10​
​ это большое преимущество,​D4​B1:B3​ П6​ мидится прикрутить с​(здесь он равен​ А также нумерация​ аргументе функции ВПР​ в вычислениях.​1. Ссылка на лист​.​ на кнопку​ общий итог. Данная​ рассмотрим, как решить​ найдены в обоих​(ИНДЕКС).​.​ особенно когда работать​
​, так как счёт​содержатся значения New-York,​

​vikttur​​ помощью других функций.​ минус 1). Это​ должна начинаться со​ должен указываться номер​Перемещение​ «Маркетинг».​Чтобы просмотреть формулу, выделите​«OK»​ функция также относится​ поставленные задачи различными​ столбцах (т.е. оба​
​А теперь давайте испытаем​
​Затем соединяем обе части​ приходится с большими​ начинается со второй​ Paris, London, тогда​: Так в чем​Guest​

​ некий аналог четвертого​​ второй строки!​ столбца, из которого​   . Если листы, находящиеся между​2. Ссылка на диапазон​ ячейку, и она​
​.​ к категории статистических​ способами.​ критерия истинны), Вы​ этот шаблон на​ и получаем формулу:​ объёмами данных. Вы​

​ строки.​​ следующая формула возвратит​ проблема?​: Тогда так.​ аргумента функции​
​Скачать пример поиска значения​ следует взять значение​ листом 2 и​

​ просмотра (Range Lookup)​​ строке Excel​ с именем Товар​
​ таким образом, чтобы​3. Восклицательный знак (!)​

planetaexcel.ru

​Выделите пустую ячейку.​

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

Поиск значения в массиве Excel

Схема решения задания выглядит примерно таким образом:

• в ячейку B1 мы будем вводить интересующие нас данные;
• в ячейке B2 будет отображается заголовок столбца, который содержит значение ячейки B1
• в ячейке B3 будет отображается название строки, которая содержит значение ячейки B1.

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

Последовательно рассмотрим варианты решения разной сложности, а в конце статьи – финальный результат.

Поиск значения в столбце Excel

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

1. В ячейку B1 введите значение взятое из таблицы 5277 и выделите ее фон синим цветом для читабельности поля ввода (далее будем вводить в ячейку B1 другие числа, чтобы экспериментировать с новыми значениями).
2. В ячейку C2 вводим формулу для получения заголовка столбца таблицы который содержит это значение:
3. После ввода формулы для подтверждения нажимаем комбинацию горячих клавиш CTRL+SHIFT+Enter, так как формула должна быть выполнена в массиве. Если все сделано правильно в строке формул по краям появятся фигурные скобки { }.

В ячейку C2 формула вернула букву D — соответственный заголовок столбца листа. Как видно все сходиться, значение 5277 содержится в ячейке столбца D. Рекомендуем посмотреть на формулу для получения целого адреса текущей ячейки.

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

Теперь получим номер строки для этого же значения (5277). Для этого в ячейку C3 введите следующую формулу:

После ввода формулы для подтверждения снова нажимаем комбинацию клавиш CTRL+SHIFT+Enter и получаем результат:

Формула вернула номер 9 – нашла заголовок строки листа по соответствующему значению таблицы. В результате мы имеем полный адрес значения D9.



Как получить заголовок столбца и название строки таблицы

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

• для столбца таблицы – Март;
• для строки – Товар4.

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

1. Для заголовка столбца. В ячейку D2 введите формулу: На этот раз после ввода формулы для подтверждения жмем как по традиции просто Enter:



2. Для строки вводим похожую, но все же немного другую формулу:

В результате получены внутренние координаты таблицы по значению – Март; Товар 4:

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

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

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

Более того для диапазона табличной части создадим правило условного форматирования:

1. Выделите диапазон B6:J12 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Правила выделения ячеек»-«Равно».



2. В левом поле введите значение $B$1, а из правого выпадающего списка выберите опцию «Светло-красная заливка и темно-красный цвет» и нажмите ОК.



3. В ячейку B1 введите значение 3478 и полюбуйтесь на результат.

Как видно при наличии дубликатов формула для заголовков берет заголовок с первого дубликата по горизонтали (с лева на право). А формула для получения названия (номера) строки берет номер с первого дубликата по вертикали (сверху вниз). Для исправления данного решения есть 2 пути:

1. Получить координаты первого дубликата по горизонтали (с лева на право). Для этого только в ячейке С3 следует изменить формулу на:

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



2. Получить координаты первого дубликата по вертикали (сверху вниз). Для этого только в ячейке С2 следует изменить формулу на:

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

Здесь правильно отображаются координаты первого дубликата по вертикали (с верха в низ) – I7 для листа и Август; Товар2 для таблицы. Оставим такой вариант для следующего завершающего примера.

Поиск ближайшего значения в диапазоне Excel

Данная таблица все еще не совершенна. Ведь при анализе нужно точно знать все ее значения. Если введенное число в ячейку B1 формула не находит в таблице, тогда возвращается ошибка – #ЗНАЧ! Идеально было-бы чтобы формула при отсутствии в таблице исходного числа сама подбирала ближайшее значение, которое содержит таблица. Чтобы создать такую программу для анализа таблиц в ячейку F1 введите новую формулу:

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

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

Скачать пример поиска значения в диапазоне Excel

Наша программа в Excel нашла наиболее близкое значение 4965 для исходного – 5000. Такая программа может пригодится для автоматического решения разных аналитических задач при бизнес-планировании, постановки целей, поиска рационального решения и т.п. А полученные строки и столбцы позволяют дальше расширять вычислительные возможности такого рода отчетов с помощью новых формул Excel.