Выбрать элемент массива excel - Word и Excel - помощь в работе с программами

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. Назовите столбцы и строки

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

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

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

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

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

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

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

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

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

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

=Sprite неделя_3

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

Содержание

Как в офисе.
1. Функция ВЫБОР (CHOOSE)
2. Функция ИНДЕКС (INDEX)
3. Функция ПОИСКПОЗ (MATCH)
4. Функция ГПР (HLOOKUP)
5. Функция ВПР (VLOOKUP)
Excel выбрать из массива
1. Функция ВЫБОР (CHOOSE)
2. Функция ИНДЕКС (INDEX)
3. Функция ПОИСКПОЗ (MATCH)
4. Функция ГПР (HLOOKUP)
5. Функция ВПР (VLOOKUP)
Управление массивами в Microsoft Excel
Операции с массивами
Создание формулы
Изменение содержимого массива
Функции массивов
Оператор СУММ
Оператор ТРАНСП
Оператор МОБР

Как в офисе.

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

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

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

1. Функция ВЫБОР (CHOOSE)

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

=ВЫБОР(2;»Стул»;»Стол»;»Шкаф»;»Диван«)

2. Функция ИНДЕКС (INDEX)

Эта функция возвращает указанное значение из одно- или двумерного диапазона:

=ИНДЕКС(A1:C6;4;3)

Как видно с примера, полученное значение 37, в указанном диапазоне стоит на пересечении строки № 4 и столбика № 3 в диапазоне A1:C6 указанном в формуле. В более простом примере показано как в диапазоне С1:С6, на 2 месте находится значение 15:

=ИНДЕКС(С1:С6;2)

3. Функция ПОИСКПОЗ (MATCH)

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

=ПОИСКПОЗ(B3;B2:B5;0)

С примера вы можете видеть что слово «Стол» занимает 2 позицию в указанном диапазоне. Замечу, что третий аргумент в функции не является обязательным. При введенном значении 0, функция вернет ту позицию элемента массива, которое точно совпадает со значением, которое мы ищем. В случае, когда точное совпадение отсутствует, функция выдаст ошибку #Н/Д (#N/A).

4. Функция ГПР (HLOOKUP)

Ищет значение в указанном диапазоне и возвращает значение ячейки, которая находится в указанной строке того же столбца: =ГПР(C1;$B$1:$E$2;1;ЛОЖЬ). Как видите с примера, функция ГПР ищет в указанном диапазоне $B$1:$E$2 (знаком $ я указал абсолютную ссылку) и согласно условию возвращает искомое значение из первой строки, а аргумент «ЛОЖЬ» означает, что-либо, будет найдено нужное значение, либо мы получим ошибку #Н/Д.

5. Функция ВПР (VLOOKUP)

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

=ВПР(B4;$B$2:$C$5;2;ЛОЖЬ)

Как видим, формула идентична предыдущей функции ГПР и так же ищет указанный номер «B4» в диапазоне $B$2:$C$5 со знаком $ (это сделано для создания абсолютной ссылки, что бы при копировании формулы на диапазон, аргумент не будет изменен), в третьем столбце, так как аргумент функции равен 2. Ну и четвёртый аргумент равен значению «ЛОЖЬ», это означает, что-либо будет найдено совпадение значений, либо будет получено сообщение об ошибке #Н/Д. Теперь при необходимости, мы копируем формулу, и она перенесёт все правильные аргументы по всему диапазону вычислений. Это возможно стало из-за абсолютной ссылки на массив значений, а вот первый аргумент на B4, при копировании, должен измениться на B5 и так далее.

Источник

Excel выбрать из массива

Доброго времени суток друзья!

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

1. Функция ВЫБОР (CHOOSE)

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

=ВЫБОР(2;»Стул»;»Стол»;»Шкаф»;»Диван«)

2. Функция ИНДЕКС (INDEX)

Эта функция возвращает указанное значение из одно- или двумерного диапазона:

=ИНДЕКС(A1:C6;4;3)

=ИНДЕКС(С1:С6;2)

3. Функция ПОИСКПОЗ (MATCH)

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

=ПОИСКПОЗ(B3;B2:B5;0)

4. Функция ГПР (HLOOKUP)

5. Функция ВПР (VLOOKUP)

=ВПР(B4;$B$2:$C$5;2;ЛОЖЬ)

Источник

Управление массивами в Microsoft Excel

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

Операции с массивами

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

Во втором — в нескольких одновременно.

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

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

Создание формулы

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

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

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

А пример адреса двумерного диапазона выглядит следующим образом: A2:D7.

Изменение содержимого массива

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

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

Закройте информационное окно, нажав на кнопку «OK».
Затем нажмете на кнопку «Отмена», которая расположена в группе значков слева от строки формул, и представляет собой пиктограмму в виде крестика. Также можно нажать на кнопку Esc на клавиатуре. После любой из этих операций произойдет отмена действия, и вы сможете работать с листом так, как и прежде.

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

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

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

Функции массивов

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

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

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

Оператор СУММ

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

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

Оператор ТРАНСП

Функция ТРАНСП является типичным оператором массивов. Она позволяет переворачивать таблицы или матрицы, то есть, менять строки и столбцы местами. При этом она использует исключительно вывод результата в диапазон ячеек, поэтому после введения данного оператора обязательно нужно применять сочетание Ctrl+Shift+Enter. Также нужно отметить, что перед введением самого выражения нужно выделить на листе область, у которой количество ячеек в столбце будет равно числу ячеек в строке исходной таблицы (матрицы) и, наоборот, количество ячеек в строке должно равняться их числу в столбце исходника. Синтаксис оператора следующий:

Оператор МОБР

Функция МОБР позволяет производить вычисление обратной матрицы. Все правила ввода значений у этого оператора точно такие же, как и у предыдущего. Но важно знать, что вычисление обратной матрицы возможно исключительно в том случае, если она содержит равное количество строк и столбцов, и если её определитель не равен нулю. Если применять данную функцию к области с разным количеством строк и столбцов, то вместо корректного результата на выходе отобразится значение «#ЗНАЧ!». Синтаксис у этой формулы такой:

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

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

Источник

Терминология

Под массивом обычно понимают набор данных, объединенных в группу. Массивы бывают одномерные (элементы массива образуют строку или столбец) или двумерные (матрица). Легко сообразить, что почти в любой таблице Excel при желании можно найти один или несколько таких массивов:

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

Пример 1. Классика жанра — товарный чек

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

выделяем ячейку С7
вводим с клавиатуры =СУММ(
выделяем диапазон B2:B5
вводим знак умножения (звездочка)
выделяем диапазон C2:C5 и закрываем скобку функции СУММ — в итоге должно получиться так:
чтобы Excel воспринял нашу формулу как формулу массива жмем не Enter, как обычно, а Ctrl + Shift + Enter

Вуаля!

Т.е. Excel произвел попарное умножение элементов массивов B2:B5 и C2:C5 и образовал новый массив стоимостей (в памяти компьютера), а затем сложил все элементы этого нового массива.

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

Пример 2. Разрешите Вас… транспонировать?

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

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

Выделяем диапазон ячеек для размещения транспонированной таблицы. Поскольку исходный массив ячеек был 8 строк на 2 столбца, то надо выделить диапазон пустых ячеек размером 2 строки на 8 столбцов.
вводим функцию транспонирования =ТРАНСП(
в качестве аргумента функции выделяем наш массив ячеек A1:B8

жмем Ctrl + Shift + Enter и получаем «перевернутый массив» в качестве результата:

Редактирование формулы массива

Если формула массива расположена не в одной ячейке (как в Примере 1), а в нескольких ячейках (как в Примере 2), то Excel не позволит редактировать или удалить одну отдельно взятую формулу (например в ячейке D10) и выдаст предупреждающее сообщение Невозможно изменить часть массива.

Для редактирования формулы массива необходимо выделить весь диапазон (A10:H11 в нашем случае) и изменить формулу в строке формул (или нажав F2). Затем необходимо повторить ввод измененной формулы массива, нажав сочетание клавиш Ctrl + Shift + Enter.

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

Пример 3. Таблица умножения

Вспомните детство, школу, свою тетрадку по математике… На обороте тетради на обложке было что? Таблица умножения вот такого вида:

При помощи формул массива она вся делается в одно движение:

выделяем диапазон B2:K11
вводим формулу =A2:A11*B1:K1
жмем Ctrl + Shift + Enter, чтобы Excel воспринял ее как формулу массива

и получаем результат:

Пример 4. Выборочное суммирование

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

В данном случае формула массива синхронно пробегает по всем элементам диапазонов C3:C21 и B3:B21, проверяя, совпадают ли они с заданными значениями из ячеек G4 и G5. Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.

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

Формула массива для извлечения непустых ячеек из диапазона
Формула массива для извлечения уникальных ячеек из диапазона
Формула массива для извлечения данных из списка (многоразовый ВПР)

Источник

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

Начиная с обновления для Microsoft 365 за сентябрь 2018 г., любая формула, которая может возвращать несколько результатов, автоматически переносит их вниз или в соседние ячейки. Это изменение поведения также сопровождается несколькими новыми функциями динамического массива. Формулы динамического массива, независимо от того, используют ли они существующие функции или функции динамического массива, необходимо вводить только в одну ячейку, а затем подтверждать, нажав ВВОД. Ранее в устаревших формулах массива сначала требовалось выбрать весь выходной диапазон, а затем подтвердить формулу с помощью CTRL+SHIFT+ВВОД. Их часто называют формулами CSE.

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

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

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

Скачивание примеров

Скачать образец книги со всеми примерами формул массива из этой статьи.

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

Формула массива с несколькими ячейками
Здесь мы вычисляем совокупный объем продаж купе и седанов для каждого продавца, вводя =F10:F19*G10:G19 в ячейку H10.

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

В ячейке H20 образца книги введите или скопируйте и вставьте =СУММ(F10:F19*G10:G19), а затем нажмите ВВОД.

В этом случае будет выполнено перемножение значений массива (диапазон ячеек F10–G19), а затем — при помощи функции СУММ — сложение полученных результатов. Общий итог продаж составит 1 590 000 рублей.

В данном примере демонстрируется, какие широкие возможности предоставляет данный тип формул. Например, предположим, что у вас есть 1000 строк с данными. Вы можете частично или полностью сложить их значения, создав формулу массива в отдельной ячейке, а не перетаскивать формулу на 1000 строк ниже. Кроме того, обратите внимание на то, что формула с одной ячейкой в ячейке H20 полностью независима от формулы с несколькими ячейками (формула в ячейках H10–H19). Это указывает на еще одно преимущество использования формул массива — их гибкость. Можно изменить формулы в столбце H, и это не повлияет на формулу в ячейке H20. Также может быть полезно иметь независимые итоги, как это, так как это помогает проверить точность результатов.
Ниже перечислены дополнительные преимущества, которые обеспечивает использование формул динамического массива.
- Согласованность Если щелкнуть несколько ячеек ниже ячейки H10, вы увидите, что все они содержат одну и ту же формулу. Такая согласованность помогает обеспечить более высокую точность результатов.
- Безопасность Компонент формулы массива с несколькими ячейками нельзя переписать. Например, щелкните ячейку H11 и нажмите клавишу DELETE. Excel не будет изменять выходные данные массива. Чтобы их изменить, необходимо выбрать левую верхнюю ячейку массива или ячейку H10.
- Меньший размер файлов Зачастую вместо нескольких промежуточных формул можно использовать одну формулу массива. Например, в примере с продажей автомобилей для вычисления результатов в столбце E используется одна формула массива. Если бы вы прибегли к обычным формулам (таким как =F10*G10, F11*G11, F12*G12 и т. д.), вам понадобилось бы 11 разных формул для получения таких же результатов. Это не очень важно, но что, если вам необходимо суммировать тысячи строк? Тогда это может иметь большое значение.
- Эффективность Функции массива могут быть эффективным способом создания сложных формул. Формула массива =СУММ(F10:F19*G10:G19) та же: =СУММ(F10*G10,F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).
- Перенос Динамические формулы массива будут автоматически перенесены в диапазон вывода. Если исходные данные хранятся в таблице Excel, тогда формулы динамического массива будут автоматически изменять размер при добавлении и удалении данных.
- Ошибка #ПЕРЕНОС! Динамические массивы ввели Ошибка #ПЕРЕНОС!, что означает, что предполагаемый диапазон переноса по какой-либо причине заблокирован. При устранении блока формула будет автоматически перенесены.

Константы массива являются компонентами формул массива. Константы массива создаются путем ввода списка элементов с последующей вставкой фигурных скобок ({ }) вокруг списка, следующим образом:

={1;2;3;4;5} или ={«Январь»;»Февраль»;»Март»}

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

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

Создание горизонтальной константы

Воспользуйтесь книгой из предыдущих примеров или создайте новую книгу. Выберете любую пустую ячейку и введите =ПОСЛЕД(1,5). Функция ПОСЛЕД создает массив из 1 строки на 5 столбцов, как ={1;2;3;4;5}. Будет отображен следующий результат:
Создание вертикальной константы

Выберите любую пустую ячейку с помещением под ней и введите =ПОСЛЕД(5), или ={1;2;3;4;5}. Будет отображен следующий результат:
Создание двумерной константы

Выберите любую пустую ячейку с помещением справа и введите =ПОСЛЕД(3,4). Вы получите следующий результат:

Вы также можете ввести: или={1;2;3;4:5;6;7;8:9;10;11;12}, но обратите внимание, где вы ставите точки с запятой вместо запятых.

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

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

В ячейке D9 мы ввели =ПОСЛЕД(1,5,3,1), но вы также можете ввести 3, 4, 5, 6 и 7 в ячейки A9:H9. В этом конкретном выборе числа нет ничего особенного, мы просто выбрали что-то другое, кроме 1-5.

В ячейке E11 введите =СУММ(D9:H9*ПОСЛЕДОВАТЕЛЬНОСТЬ(1,5)) или =СУММ(D9:H9*{1;2;3;4;5}). Формулы возвращают 85.

Функция ПОСЛЕД создает эквивалент константы массива {1;2;3;4;5}. Поскольку Excel в первую очередь выполняется операции с элементами, заключенными в скобки, далее будут использоваться значения ячеек в D9: H9 и оператор умножения (*). На этом этапе формула выполнит умножение значений в сохраненном массиве на соответствующие значения в константе. Это эквивалент следующей формулы:

=СУММ(D9*1;E9*2,F9*3,G9*4;H9*5) или =СУММ(3*1,4*2,5*3,6*4,7*5)

В последнюю очередь выполняется сложение значений при помощи функции СУММ и возвращается 85.

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

=СУММ(ПО(1;5;3;1)*ПОСЛЕД(1,5)) или =СУММ({3;4;5;6;7}*{1;2;3;4;5})

Элементы, которые можно использовать в константах массива

Константы массива могут содержать числа, текст, логические значения (например, ИСТИНА и ЛОЖЬ) и значения ошибок, такие как #N/A. Можно использовать числа в целочисленном, десятичном или экспоненциальном формате. При добавлении текста требуется заключить его в кавычки («текст»).
Константы массива не могут содержать дополнительные массивы, формулы или функции. Другими словами, они могут содержать только текст или числа, разделенные запятыми или точками с запятой. При вводе такой формулы, как {1;2;A1:D4} или {1;2;СУММ(Q2:Z8)}, выводится предупреждение. Кроме того, числовые значения не могут содержать знаки процента, знаки валюты, запятые или кавычки.

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

Выберите Формулы > Присвоить имена > Определить имена. В поле Имя введите Квартал 1. В поле Диапазон введите следующую константу (не забудьте ввести скобки вручную):

={«Январь»;»Февраль»;»Март»}

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

Нажмите кнопку ОК, выберите любую строку с тремя пустыми ячейками и введите =Квартал1.

Будет отображен следующий результат:

Если вы хотите, чтобы результаты переносились по вертикали, а не по горизонтали, можно использовать =ТРАНСП(Квартал1).

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

=ТЕКСТ(ДАТА(ГОД(СЕГОДНЯ()),ПОСЛЕД(1,12),1),»ммм»)

При этом функция ДАТА используется для создания даты на основе текущего года, функция ПОСЛЕД создает константу массива от 1 до 12 с января по декабрь, затем функция ТЕКСТ преобразует формат отображения в «ммм» (январь, февраль, март и т..д.). Если вы хотите отобразить полное название месяца, например «Январь», используйте «мммм».

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

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

Умножение каждого из элементов массива

Введите =ПОСЛЕД(1,12)*2 или ={1;2;3;4:5;6;7;8:9;10;11;12}*2

Вы также можете выполнить деление с помощью (/), сложить с помощью (+) и вычесть с помощью (—).
Возведение в квадрат элементов массива

Введите =ПОСЛЕД(1,12)^2 или ={1;2;3;4:5;6;7;8:9;10;11;12}^2
Поиск квадратного корня из квадрата элементов массива

Введите =КОРЕНЬ(ПОСЛЕД(1,12)^2) или =КОРЕНЬ({1;2;3;4:5;6;7;8:9;10;11;12}^2)
Транспонирование одномерной строки

Введите =ТРАНСП(ПОСЛЕД(1,5)) или =ТРАНСП({1;2;3;4;5})

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

Введите =ТРАНСП(ПОСЛЕД(5,1)) или =ТРАНСП({1:2:3:4:5})

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

Введите =ТРАНСП(ПОСЛЕД(3,4)) или =ТРАНСП({1;2;3;4:5;6;7;8:9;10;11;12})

Функция ТРАНСП преобразует каждую из строк в последовательность столбцов.

В данном разделе приводятся примеры использования основных формул массива.

Создание массива на основе существующих значений

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

Введите =ПОСЛЕД(3,6,10,10) или ={10;20;30;40;50;60:70;80;90;100;110;120:130;140;150;160;170;180}

Не забудьте ввести { (открывающую фигурную скобку) перед числом 10 и} (закрывающую фигурную скобку) после числа 180, так как вы создаете массив чисел.

Затем введите =D9# или =D9:I11 в пустую ячейку. Появится массив ячеек 3 x 6 с одинаковыми значениями, которые отображаются в D9:D11. Знак # называется оператором диапазона переноса, и это способ ссылки Excel на весь диапазон массива вместо того, чтобы вводить его.
Создание константы массива на основе существующих значений

Вы можете получить результаты формулы перенесенного массива и преобразовать его в компоненты. Выберите ячейку D9 и нажмите клавишу F2, чтобы переключиться в режим правки. Затем нажмите клавишу F9, чтобы преобразовать ссылки на ячейки в значения, которые Excel преобразует в константу массива. При нажатии клавиши ВВОД формула =D9# теперь должна быть ={10;20;30:40;50;60:70;80;90}.
Подсчет знаков в диапазоне ячеек

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

=СУММ(LEN(C9:C13))

В данном случае функция ДЛСТР возвращает длину каждой текстовой строки в каждой из ячеек диапазона. Затем функция СУММ складывает эти значения и выводит полученный результат (66). Если вы хотите получить среднее количество символов, можно использовать:

=СРЗНАЧ(ДЛСТР(C9:C13))
Содержимое самой длинной ячейки в диапазоне C9:C13

=ИНДЕКС(C9:C13;ПОИСКПОЗ(МАКС(ДЛСТР(C9:C13)),ДЛСТР(C9:C13);0);1)

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

Рассмотрим эту формулу подробнее, начиная с элементов в скобках. Функция ДЛСТР возвращает длину каждого элемента в диапазон ячеек D2:D6. Функция МАКС вычисляет наибольшее значение среди этих элементов, соответствующее самой длинной текстовой строке, которая находится в ячейке D3.

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

МАКС(ДЛСТР(C9:C13)

из следующего массива:

ДЛСТР(C9:C13)

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

Если вы хотите получить содержимое из наименьшей текстовой строки, замените значение МАКС в примере выше на МИН.
Поиск n наименьших значений в диапазоне

В этом примере показано, как найти три наименьших значения в диапазоне ячеек, где массив образцов данных в ячейках B9:B18 был создан с помощью: =ЦЕЛОЕ(СЛМАССИВ(10,1)*100). Обратите внимание, что ФУНКЦИЯ СЛМАСИМВ — это переменная функция, поэтому вы будете получать новый набор случайных чисел при каждом вычислении в Excel.

Введите =НАИМЕНЬШИЙ(B9#,ПОСЛЕД(D9), =НАИМЕНЬШИЙ(B9:B18,{1:2:3})

В этой формуле константа массива используется для трехкратной оценки функции НАИМЕНЬШИЙ и возврата 3 наименьших членов массива, содержащихся в ячейках B9:B18, где 3 — это переменное значение в ячейке D9. Чтобы найти дополнительные значения, можно увеличить значение в функции ПОСЛЕД или добавить дополнительные аргументы в константу. С этой формулой также используются дополнительные функции, например СУММ или СРЗНАЧ. Например:

=СУММ(НАИМЕНЬШИЙ(B9#;ПОСЛЕД(D9))

=СРЕДНИЙ(НАИМЕНЬШИЙ(B9#;ПОСЛЕД(D9))
Поиск n наибольших значений в диапазоне

Чтобы найти наибольшие значения в диапазоне, следует заменить функцию НАИМЕНЬШИЙ функцией НАИБОЛЬШИЙ. Кроме того, в следующем примере используются функции СТРОКА и ДВССЫЛ.

Введите =НАИБОЛЬШИЙ(B9#,СТРОКА(ДВССЫЛ(«1:3»))) или =НАИБОЛЬШИЙ(B9:B18,СТРОКА(ДВССЫЛ(«1:3»)))

На этом этапе может быть полезно ознакомиться с функциями СТРОКА и ДВССЫЛ. При помощи функции СТРОКА можно создать массив последовательных целых чисел. Например, выберите пустое и введите:

=СТРОКА(1:10)

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

=СТРОКА(ДВССЫЛ(«1:10»))

В функции ДВССЫЛ в качестве аргументов используются текстовые строки (именно поэтому диапазон 1:10 заключен в кавычки). Текстовые значения не изменяются при вставке строк или перемещении формулы массива. В результате при выполнении функции СТРОКА всегда будет создаваться нужный массив целочисленных значений. Вы можете так же легко использовать функцию ПОСЛЕД:

=ПОСЛЕД(10)

Рассмотрим формулу, которую вы использовали ранее — =НАИБОЛЬШИЙ(B9#,СТРОКА(ДВССЫЛ(«1:3»))), начиная с внутренних скобок и работая наружу: функция ДВССЫЛ возвращает набор текстовых значений, в данном случае значения от 1 до 3. Функция СТРОКА, в свою очередь, создает массив столбцов из трех ячеек. Функция НАИБОЛЬШИЙ использует значения в диапазоне ячеей B9:B18 и вычисляется три раза для каждой ссылки, возвращаемой функцией СТРОКА. Если вы хотите найти дополнительные значения, добавьте к функции ДВССЫЛ более широкий диапазон ячеек. Как и в примерах с функцией НАИМЕНЬШИЙ, эту формулу можно использовать и с другими функциями, такими как СУММ и СРЗНАЧ.

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

При попытке просуммировать диапазон, содержащий значения ошибки (например, #ЗНАЧЕН), функция СУММ в Excel не работает. или #Н/Д. В приведенном ниже примере демонстрируется, как просуммировать значения в диапазоне «Данные», который содержит ошибки:
=СУММ(ЕСЛИ(ЕОШИБКА(Данные);»»;Данные))

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

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

=СУММ(ЕСЛИ(ЕОШИБКА(Данные);1;0))

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

=СУММ(ЕСЛИ(ЕОШИБКА(Данные);1))

Если этот аргумент не задан и ячейка не содержит значения ошибки, функция ЕСЛИ возвращает значение ЛОЖЬ. Формулу можно упростить еще больше:

=СУММ(ЕСЛИ(ЕОШИБКА(Данные)*1))

Такой вариант формулы допустим, поскольку ИСТИНА*1=1 и ЛОЖЬ*1=0.

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

Например, в указанной ниже формуле массива суммируются только положительные целые числа в диапазоне с именем «Продажи», который представляет ячейки E9:E24 в приведенном выше примере:

=СУММ(ЕСЛИ(Продажи>0;Продажи))

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

Можно также выполнить суммирование значений с учетом нескольких условий. Например, эта формула массива вычисляет значения больше 0 И меньше 2500:

=СУММ((Продажи>0)*(Продажи<2500)*(Продажи))

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

Доступно создание формул массива с использованием условия типа ИЛИ. Например, можно суммировать значения, которые больше 0 ИЛИ меньше 2500:

=СУММ(ЕСЛИ((Продажи>0)+(Продажи<2500);Продажи))

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

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

=СРЗНАЧ(ЕСЛИ(Продажи<>0,Продажи))

В результате выполнения функции ЕСЛИ создается массив значений, не равных 0, которые затем передаются в функцию СРЗНАЧ.

В данной формуле массива сравниваются значения в двух диапазонах ячеек с именами МоиДанные и ДругиеДанные, в результате чего возвращается количество различающихся ячеек между этими двумя диапазонами. Если содержимое двух диапазонов идентично, формула возвращает 0. Эту формулу можно использовать только для диапазонов ячеек с одинаковым размером и одинаковой размерностью. Например, если МоиДанные — это диапазон из 3 строк и 5 столбцов, то диапазон ДругиеДанные тоже должен состоять из 3 строк и 5 столбцов:

=СУММ(ЕСЛИ(МоиДанные=ДругиеДанные;0;1))

В результате выполнения формулы создается массив, имеющий такой же размер, что и сравниваемые диапазоны. Функция ЕСЛИ заполняет массив значениями 0 и 1 (0 для ячеек с различными значениями, 1 — для ячеек с одинаковыми значениями). Затем функция СУММ возвращает сумму значений в массиве.

Эту формулу можно упростить следующим образом:

=СУММ(1*(МоиДанные<>ДругиеДанные))

Подобно формуле, подсчитывающей количество значений ошибки в диапазоне, эта формула работает благодаря тому, что ИСТИНА*1=1, а ЛОЖЬ*1=0.

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

=МИН(ЕСЛИ(Данные=МАКС(Данные);СТРОКА(Данные);»»))

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

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

=АДРЕС(МИН(ЕСЛИ(Данные=МАКС(Данные);СТРОКА(Данные);»»));СТОЛБЕЦ(Данные))

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

Формула массива с несколькими ячейками

Скопируйте всю таблицу, приведенную ниже, и вставьте ее на пустой лист книги, начиная с ячейки A1.

Продавец	Тип автомобиля	Число проданных единиц	Цена за единицу
Зуева	Седан	5	33000
	Купе	4	37000
Егоров	Седан	6	24000
	Купе	8	21000
Еременко	Седан	3	29000
	Купе	1	31000
Климов	Седан	9	24000
	Купе	5	37000
Шашков	Седан	6	33000
	Купе	8	31000
Формула (общий итог)			Общий итог
‘=СУММ(C2:C11*D2:D11)			=СУММ(C2:C11*D2:D11)

Чтобы просмотреть итоги продаж автомобилей типа «купе» и «седан» для каждого продавца, выберите ячейки E2:E11, введите формулу =C2:C11*D2:D11, а затем нажмите клавиши CTRL+SHIFT+ВВОД.
Чтобы просмотреть итоги всех продаж, выберите ячейку F11, введите формулу =СУММ(C2:C11*D2:D11), а затем нажмите клавиши CTRL+SHIFT+ВВОД.

При нажатии CTRL+SHIFT+ВВОД Excel заключает формулу в скобки ({ }) и вставляет экземпляр формулы в каждую ячейку выбранного диапазона. Это происходит очень быстро, поэтому в столбце E отображается сумма продаж каждого типа автомобиля для каждого продавца. Если вы выберете E2, а затем выберите E3, E4 и так далее, вы увидите, что отображается та же формула: {=C2:C11*D2:D11}.

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

В ячейке D13 книги введите указанную ниже формулу, а затем нажмите сочетание клавиш CTRL+SHIFT+ВВОД:

=СУММ(C2:C11*D2:D11)

В этом случае будет выполнено перемножение значений массива (диапазон ячеек C2–D11), а затем — при помощи функции СУММ — сложение полученных результатов. Общий итог продаж составит 1 590 000 рублей. В данном примере демонстрируется, какие широкие возможности предоставляет данный тип формул. Например, предположим, что у вас есть 1000 строк с данными. Вы можете частично или полностью сложить их значения, создав формулу массива в отдельной ячейке, а не перетаскивать формулу на 1000 строк ниже.

Кроме того, обратите внимание на то, что формула с одной ячейкой в ячейке D13 полностью независима от формулы с несколькими ячейками (формула в ячейках E2–E11). Это указывает на еще одно преимущество использования формул массива — их гибкость. Можно изменить формулы в столбце E или вообще удалить этот столбец, и это не повлияет на формулу в ячейке D13.

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

Согласованность Если щелкнуть несколько ячеек ниже ячейки E2, вы увидите, что все они содержат одну и ту же формулу. Такая согласованность помогает обеспечить более высокую точность результатов.
Безопасность Компонент формулы массива с несколькими ячейками нельзя переписать. Например, щелкните ячейку E3 и нажмите клавишу DELETE. Вам придется выделить весь диапазон ячеек (E2–E11) и изменить формулу для всех ячеек либо оставить массив без изменений. В качестве меры безопасности необходимо нажать CTRL+SHIFT+ВВОД чтобы подтвердить изменение формулы.
Меньший размер файлов Зачастую вместо нескольких промежуточных формул можно использовать одну формулу массива. Например, в книге для вычисления результатов в столбце E применяется всего лишь одна формула массива. Если бы вы прибегли к обычным формулам (таким как =C2*D2, C3*D3, C4*D4 и т. д.), вам понадобилось бы 11 разных формул для получения таких же результатов.

Как правило, в формулах массива используется стандартный синтаксис формулы. Они все начинаются со знака равенства (=) и могут содержать многие встроенные функции Excel. Ключевое отличие заключается в том, что при использовании формулы массива, чтобы ввести формулу необходимо ввести клавиши CTRL+SHIFT+ВВОД. В этом случае Excel заключит формулу массива в фигурные скобки — если вы введете фигурные скобки вручную, ваша формула будет преобразована в текстовую строку и не будет работать.

Функции массива могут быть эффективным способом создания сложных формул. Формула массива =СУММ(C2:C11*D2:D11) эквивалентна формуле =СУММ(C2*D2;C3*D3;C4*D4;C5*D5;C6*D6;C7*D7;C8*D8;C9*D9;C10*D10;C11*D11).

Важно: Нажимайте клавиши CTRL+SHIFT+ВВОД, когда хотите ввести формулу массива. Это правило действует для формул как с одной, так и с несколькими ячейками.

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

Выделите диапазон ячеек, который будет содержать результаты, прежде чем вводить формулу. Вы делали это при создании формулы массива с несколькими ячейками, выбирая ячейки E2–E11.
Содержимое отдельной ячейки в формуле массива изменить нельзя. Чтобы проверить это правило, выделите ячейку E3 в книге и нажмите клавишу DELETE. В приложении Excel отобразится сообщение о том, что нельзя изменить часть формулы массива.
Формулу массива можно переместить или удалить только целиком. Другими словами, чтобы уменьшить формулу массива, сначала нужно удалить существующую формулу, а затем создать новую формулу с самого начала.
Чтобы удалить формулу массива, выберите весь диапазон формул (например, E2:E11), а затем нажмите Удалить.
В формулу массива с несколькими ячейками нельзя вставить пустые строки (или удалить строки из нее).

Иногда возникает необходимость увеличить диапазон формулы массива. Выберите первую ячейку в существующем диапазоне массива и продолжайте, пока не выберете весь диапазон, на который вы хотите расширить формулу. Нажмите F2, чтобы изменить формулу, а затем нажмите CTRL+SHIFT+ВВОД, чтобы подтвердить формулу после изменения диапазона формул. Ключом является выбор всего диапазона, начиная с верхней левой ячейки массива. Верхняя левая ячейка — это ячейка, которая редактируется.

Несмотря на все преимущества формул массива, у них имеются свои недостатки.

Вы можете случайно забыть нажать сочетание клавиш CTRL+SHIFT+ВВОД. Это может случиться даже с самыми опытными пользователями Excel. Не забывайте нажимать это сочетание клавиш каждый раз при вводе или изменении формулы массива.
Для других пользователей книги используемые формулы могут быть непонятны. На практике описание формулы массива редко приводится на листе. Поэтому, если нужно, чтобы другие люди могли редактировать книгу, следует избегать использования в ней формул массива или убедиться в том, что пользователи знают обо всех формулах массива и понимают, как изменить их при необходимости.
Большие формулы массива могут замедлять вычисления на компьютерах с невысокой скоростью процессора или небольшим объемом памяти.

Константы массива являются компонентами формул массива. Константы массива создаются путем ввода списка элементов с последующей вставкой фигурных скобок ({ }) вокруг списка, следующим образом:

={1;2;3;4;5}

Теперь вы знаете о необходимости нажимать клавиши CTRL+SHIFT+ВВОД при создании формул массива. Константы массива являются составляющей формул массива, поэтому скобки вокруг них необходимо ввести вручную. Затем нажмите клавиши CTRL+SHIFT+ВВОД, чтобы завершить ввод формулы.

Так выглядит массив из одной строки: {1;2;3;4}. Массив из одного столбца: {1:2:3:4}. А вот массив из двух строк и четырех столбцов: {1;2;3;4:5;6;7;8}. В двухстрочном массиве первая строка представлена 1, 2, 3 и 5, 6, 7, а вторая строка — 8 и 4. Строки разделены двоеточием между записями 4 и 5.

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

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

Создание горизонтальной константы

На пустом листе выделите ячейки от A1 до E1 включительно.
В строке формул введите указанную ниже формулу, а затем нажмите клавиши CTRL+SHIFT+ВВОД:

={1;2;3;4;5}

В этом случае нужно ввести открывающую и закрывающую фигурные скобки ({ }), и Excel добавит второй набор.

Будет отображен следующий результат:

Создание вертикальной константы

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

={1:2:3:4:5}

Будет отображен следующий результат:

Создание двумерной константы

Выделите в книге блок ячеек из четырех столбцов и трех строк.
В строке формул введите указанную ниже формулу, а затем нажмите клавиши CTRL+SHIFT+ВВОД:

={1;2;3;4:5;6;7;8:9;10;11;12}

Вы получите следующий результат:

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

Рассмотрим простой пример с константами.

Создайте новый лист в образце книги.
В ячейку A1 введите значение 3, затем введите 4 в ячейку B1, 5 — в C1, 6 — в D1 и 7 — в E1.
В ячейке A3 введите следующую формулу, а затем нажмите клавиши CTRL+SHIFT+ВВОД:

=СУММ(A1:E1*{1;2;3;4;5})

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

В ячейке A3 появится значение 85.

В следующем разделе поясняется работа этой формулы.

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

1. Функция

2. Сохраненный массив

3. Оператор

4. Константа массива

Последний элемент внутри скобок — это константа массива: {1;2;3;4;5}. Помните, что Excel не заключает константы массива в фигурные скобки, их нужно вводить вручную. Также помните о том, что после ввода константы в формулу массива необходимо нажать сочетание клавиш CTRL+SHIFT+ВВОД, чтобы завершить ввод формулы.

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

=СУММ(A1*1;B1*2;C1*3;D1*4;E1*5)

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

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

=СУММ({3;4;5;6;7}*{1;2;3;4;5})

Для этого скопируйте функцию, выделите пустую ячейку в книге, вставьте формулу в строку формул, а затем нажмите клавиши CTRL+SHIFT+ВВОД. Получится тот же результат, что и в предыдущем упражнении, где использовалась формула массива:

=СУММ(A1:E1*{1;2;3;4;5})

Константы массива могут содержать числа, текст, логические значения (такие как ИСТИНА и ЛОЖЬ), а также значения ошибок (например, #Н/Д). Можно использовать числа в целочисленном, десятичном или экспоненциальном формате. При добавлении текста требуется заключить его в кавычки («).

Константы массива не могут содержать дополнительные массивы, формулы или функции. Другими словами, они могут содержать только текст или числа, разделенные запятыми или точками с запятой. При вводе такой формулы, как {1;2;A1:D4} или {1;2;СУММ(Q2:Z8)}, выводится предупреждение. Кроме того, числовые значения не могут содержать знаки процента, знаки валюты, запятые или кавычки.

На вкладке Формулы в группе Определенные имена выберите команду Присвоить имя.
Откроется диалоговое окно Задать имя.
В поле Имя введите Квартал1.
В поле Диапазон введите следующую константу (не забудьте ввести скобки вручную):

={«Январь»;»Февраль»;»Март»}

Содержимое этого диалогового окна должно иметь следующий вид:
Нажмите кнопку ОК, а затем выделите строку из трех пустых ячеек.
Введите указанную ниже формулу, а затем нажмите сочетание клавиш CTRL+SHIFT+ВВОД.

=Квартал1

Будет отображен следующий результат:

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

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

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

Умножение каждого из элементов массива

Создайте новый лист и выделите блок пустых ячеек из четырех столбцов и трех строк.
Введите указанную ниже формулу, а затем нажмите сочетание клавиш CTRL+SHIFT+ВВОД:

={1;2;3;4:5;6;7;8:9;10;11;12}*2

Возведение в квадрат элементов массива

Выделите блок пустых ячеек из четырех столбцов и трех строк.
Введите указанную ниже формулу массива, а затем нажмите клавиши CTRL+SHIFT+ВВОД:

={1;2;3;4:5;6;7;8:9;10;11;12}*{1;2;3;4:5;6;7;8:9;10;11;12}

Можно также ввести следующую формулу массива, где используется оператор возведения в степень (^):

={1;2;3;4:5;6;7;8:9;10;11;12}^2

Транспонирование одномерной строки

Выделите столбец из пяти пустых ячеек.
Введите указанную ниже формулу, а затем нажмите сочетание клавиш CTRL+SHIFT+ВВОД:

=ТРАНСП({1;2;3;4;5})

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

Транспонирование одномерного столбца

Выделите строку из пяти пустых ячеек.
Введите указанную ниже формулу, а затем нажмите сочетание клавиш CTRL+SHIFT+ВВОД:

=ТРАНСП({1:2:3:4:5})

Даже если была введена вертикальная константа массива, функция ТРАНСП преобразует константу массива в строку.

Транспонирование двумерного массива

Выделите блок ячеек из трех столбцов и четырех строк.
Введите указанную ниже константу, а затем нажмите клавиши CTRL+SHIFT+ВВОД.

=ТРАНСП({1;2;3;4:5;6;7;8:9;10;11;12})

Функция ТРАНСП преобразует каждую из строк в последовательность столбцов.

В данном разделе приводятся примеры использования основных формул массива.

Создание массивов и констант массива на основе существующих значений

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

Создание массива на основе существующих значений

На листе Excel выберите диапазон ячеек C8:E10, а затем введите такую формулу:

={10;20;30:40;50;60:70;80;90}

Не забудьте ввести { (открывающую фигурную скобку) перед числом 10 и} (закрывающую фигурную скобку) после числа 90, так как вы создаете массив чисел.
Нажмите клавиши CTRL+SHIFT+ВВОД, чтобы поместить этот массив чисел в диапазон ячеек C8:E10 с помощью формулы массива. На листе ячейки с C8 по E10 должны выглядеть следующим образом:

10

20

30

40

50

60

70

80

90
Выделите диапазон ячеек C1–E3.
Введите указанную ниже формулу, а затем нажмите сочетание клавиш CTRL+SHIFT+ВВОД.

=C8:E10

В ячейках C1–E3 отобразится массив ячеек 3×3 с теми же значениями, что и в ячейках C8–E10.

10	20	30
40	50	60
70	80	90

Создание константы массива на основе существующих значений

Выделите диапазон ячеек C1:C3 и нажмите клавишу F2, чтобы перейти в режим правки.
Нажмите клавишу F9, чтобы преобразовать ссылки на ячейки в значения. Значения будут преобразованы в константы массива. Теперь формула должна быть ={10;20;30:40;50;60:70;80;90}.
Нажмите сочетание клавиш CTRL+SHIFT+ВВОД, чтобы ввести константу массива в качестве формулы массива.

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

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

Скопируйте всю таблицу и вставьте ее на лист, начиная с ячейки A1.

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

Общее количество символов в диапазоне ячеек A2:A6
=СУММ(ДЛСТР(A2:A6))

Содержимое ячейки с наибольшим количеством символов (A6)
=ИНДЕКС(A2:A6;ПОИСКПОЗ(МАКС(ДЛСТР(A2:A6));ДЛСТР(A2:A6);0);1)

Выберите ячейку A8 и нажмите клавиши CTRL+SHIFT+ВВОД, чтобы увидеть общее количество символов в диапазоне ячеек A2:A6 (66).
Выберите ячейку A10 и нажмите клавиши CTRL+SHIFT+ВВОД, чтобы увидеть содержимое ячейки с наибольшим количеством символов в диапазоне A2:A6 (ячейка A3).

Приведенная ниже формула используется в ячейке A8 для подсчета общего количества символов (66) в ячейках A2–A6.

=СУММ(ДЛСТР(A2:A6))

В данном случае функция ДЛСТР возвращает длину каждой текстовой строки в каждой из ячеек диапазона. Затем функция СУММ складывает эти значения и выводит полученный результат (66).

Поиск n наименьших значений в диапазоне

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

Введите случайные числа в ячейки A1:A11.
Выделите ячейки от C1 до C3. Этот набор ячеек будет содержать результаты, возвращенные формулой массива.
Введите указанную ниже формулу, а затем нажмите сочетание клавиш CTRL+SHIFT+ВВОД:

=НАИМЕНЬШИЙ(A1:A11,{1:2:3})

В этой формуле используется константа массива, при помощи которой три раза выполняется функция НАИМЕНЬШИЙ, в результате чего возвращаются три элемента массива, находящегося в ячейках A1:A10, которые имеют наименьшие значения (1, 2 и 3 по возрастанию). Чтобы найти дополнительные значения, следует добавить аргументы в константу. С этой формулой также используются дополнительные функции, например СУММ или СРЗНАЧ. Например:

=СУММ(НАИМЕНЬШИЙ(A1:A10,{1;2;3})

=СРЗНАЧ(НАИМЕНЬШИЙ(A1:A10,{1;2;3})

Поиск n наибольших значений в диапазоне

Чтобы найти наибольшие значения в диапазоне, следует заменить функцию НАИМЕНЬШИЙ функцией НАИБОЛЬШИЙ. Кроме того, в следующем примере используются функции СТРОКА и ДВССЫЛ.

Выделите ячейки от D1 до D3.
В строке формул введите указанную ниже формулу, а затем нажмите клавиши CTRL+SHIFT+ВВОД.

=НАИБОЛЬШИЙ(A1:A10;СТРОКА(ДВССЫЛ(«1:3»)))

На этом этапе может быть полезно ознакомиться с функциями СТРОКА и ДВССЫЛ. При помощи функции СТРОКА можно создать массив последовательных целых чисел. Например, выберите пустой столбец из 10 ячеек в книге практики, введите эту формулу массива и нажмите CTRL+SHIFT+ВВОД:

=СТРОКА(1:10)

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

=СТРОКА(ДВССЫЛ(«1:10»))

В функции ДВССЫЛ в качестве аргументов используются текстовые строки (именно поэтому диапазон 1:10 заключен в двойные кавычки). Текстовые значения не изменяются при вставке строк или перемещении формулы массива. В результате при выполнении функции СТРОКА всегда будет создаваться нужный массив целочисленных значений.

Рассмотрим формулу, которую вы использовали ранее — =НАИБОЛЬШИЙ(A5:A14,СТРОКА(ДВССЫЛ(«1:3»))), начиная с внутренних скобок и работая наружу: функция ДВССЫЛ возвращает набор текстовых значений, в данном случае значения от 1 до 3. Функция СТРОКА, в свою очередь, создает массив столбцов из трех ячеек. Функция НАИБОЛЬШИЙ использует значения в диапазоне ячеей A5:A14 и вычисляется три раза для каждой ссылки, возвращаемой функцией СТРОКА. Значения 3200, 2700 и 2000 возвращаются в массив столбцов с тремя ячейками. Если вы хотите найти дополнительные значения, добавьте к функции ДВССЫЛ более широкий диапазон ячеек.

Как и в предыдущих примерах, эту формулу можно использовать и с другими функциями, такими как СУММ и СРЗНАЧ.

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

Вернитесь к предыдущему примеру текстовой строки, введите следующую формулу в пустую ячейку и нажмите CTRL+SHIFT+ВВОД:

=ИНДЕКС(A2:A6;ПОИСКПОЗ(МАКС(ДЛСТР(A2:A6));ДЛСТР(A2:A6);0);1)

Отобразится текст «группа ячеек».

Рассмотрим эту формулу подробнее, начиная с элементов в скобках. Функция ДЛСТР возвращает длину каждого элемента в диапазон ячеек A2:A6. Функция МАКС вычисляет наибольшее значение среди этих элементов, соответствующее самой длинной текстовой строке, которая находится в ячейке A3.

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

(МАКС(ДЛСТР(A2:A6))

из следующего массива:

ДЛСТР(A2:A6)

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

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

В данном разделе приводятся примеры использования более сложных формул массива.

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

При попытке просуммировать диапазон, содержащий значения ошибки (например, #Н/Д), функция СУММ в Excel не работает. В приведенном ниже примере демонстрируется, как просуммировать значения в диапазоне «Данные», который содержит ошибки.

=СУММ(ЕСЛИ(ЕОШИБКА(Данные);»»;Данные))

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

Подсчет количества значений ошибки в диапазоне

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

=СУММ(ЕСЛИ(ЕОШИБКА(Данные);1;0))

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

=СУММ(ЕСЛИ(ЕОШИБКА(Данные);1))

Если этот аргумент не задан и ячейка не содержит значения ошибки, функция ЕСЛИ возвращает значение ЛОЖЬ. Формулу можно упростить еще больше:

=СУММ(ЕСЛИ(ЕОШИБКА(Данные)*1))

Такой вариант формулы допустим, поскольку ИСТИНА*1=1 и ЛОЖЬ*1=0.

Суммирование значений с учетом условий

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

=СУММ(ЕСЛИ(Продажи>0;Продажи))

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

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

=СУММ((Продажи>0)*(Продажи<=5)*(Продажи))

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

=СУММ(ЕСЛИ((Продажи<5)+(Продажи>15);Продажи))

Функция ЕСЛИ возвращает все значения меньше 5 и больше 15, которые затем передаются в функцию СУММ.

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

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

=СРЗНАЧ(ЕСЛИ(Продажи<>0,Продажи))

В результате выполнения функции ЕСЛИ создается массив значений, не равных 0, которые затем передаются в функцию СРЗНАЧ.

Подсчет количества различающихся ячеек в двух диапазонах

В данной формуле массива сравниваются значения в двух диапазонах ячеек с именами МоиДанные и ДругиеДанные, в результате чего возвращается количество различающихся ячеек между этими двумя диапазонами. Если содержимое двух диапазонов идентично, формула возвращает 0. Эту формулу можно использовать только для диапазонов ячеек с одинаковым размером и одинаковой размерностью (например, если МоиДанные — это диапазон из 3 строк и 5 столбцов, то диапазон ДругиеДанные тоже должен состоять из 3 строк и 5 столбцов).

=СУММ(ЕСЛИ(МоиДанные=ДругиеДанные;0;1))

В результате выполнения формулы создается массив, имеющий такой же размер, что и сравниваемые диапазоны. Функция ЕСЛИ заполняет массив значениями 0 и 1 (0 для ячеек с различными значениями, 1 — для ячеек с одинаковыми значениями). Затем функция СУММ возвращает сумму значений в массиве.

Эту формулу можно упростить следующим образом:

=СУММ(1*(МоиДанные<>ДругиеДанные))

Поиск позиции максимального значения в диапазоне

=МИН(ЕСЛИ(Данные=МАКС(Данные);СТРОКА(Данные);»»))

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

=АДРЕС(МИН(ЕСЛИ(Данные=МАКС(Данные);СТРОКА(Данные);»»));СТОЛБЕЦ(Данные))

Подтверждение

Части этой статьи основаны на серии рубрик «Опытные пользователи Excel», написанных Колином Уилкоксом (Colin Wilcox), а также на главах 14 и 15 из книги «Формулы Excel 2002», написанной Джоном Уокэнбахом (John Walkenbach), бывшим специалистом по Excel со статусом MVP.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

Динамические массивы и поведение рассеянного массива

Формулы динамического массива и устаревшие формулы массива CSE

Функция ФИЛЬТР

Функция СЛУЧМАССИВ

Функция ПОСЛЕДОВ

Функция СОРТ

Функция СОРТПО

Функция УНИК

Ошибки #ПЕРЕНОС! в Excel

Оператор неявного пересечения: @

Обзор формул

Источник

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

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

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

Исходная таблица:

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

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

Сначала создаем пустую таблицу рядом с исходной: дублируем заголовки, количество строк и столбцов. Новая таблица занимает диапазон Е1:G10.Теперь выделяем Е2:Е10 (столбец «Дата») и вводим следующую формулу: {}.

Чтобы получилась формула массива, нажимаем сочетание клавиш Ctrl + Shift + Enter. В соседний столбец – «Товар» — вводим аналогичную формулу массива: {}. Изменился только первый аргумент функции ИНДЕКС.

В столбец «Цена» введем такую же формулу массива, изменив первый аргумент функции ИНДЕКС.

В результате получаем отчет по товарам с ценой больше 200 рублей.

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

Задача №2 – выбрать из исходной таблицы товары, которые поступили в продажу 20.09.2015. То есть критерий отбора – дата. Для удобства искомую дату введем в отдельную ячейку, I2.

Для решения задачи используется аналогичная формула массива. Только вместо критерия <= применяется =: {}.

Подобные формулы вводятся и в другие столбцы (принцип см. выше).

Теперь используем текстовый критерий. Вместо даты в ячейку I2 введем текст «Товар 1». Немного изменим формулу массива: {}.

Такая большая функция выборки в Excel.

Выборка по нескольким условиям в Excel

Сначала возьмем два числовых критерия:

Задача – отобрать товары, которые стоят меньше 400 и больше 200 рублей. Объединим условия знаком «*». Формула массива выглядит следующим образом: {}.

Это для первого столбца таблицы-отчета. Для второго и третьего – меняем первый аргумент функции ИНДЕКС. Результат:

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

Случайная выборка в Excel

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

Исходный набор данных:

Сначала вставим слева два пустых столбца. В ячейку А2 впишем формулу СЛЧИС (). Размножим ее на весь столбец:

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

Чтобы вставились значения, а не формула, щелкаем правой кнопкой мыши по столбцу В и выбираем инструмент «Специальная вставка». В открывшемся окне ставим галочку напротив пункта «Значения»:

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

Источник

Доброго времени суток друзья!

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

1. Функция ВЫБОР (CHOOSE)

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

=ВЫБОР(2;»Стул»;»Стол»;»Шкаф»;»Диван«)

2. Функция ИНДЕКС (INDEX)

Эта функция возвращает указанное значение из одно- или двумерного диапазона:

=ИНДЕКС(A1:C6;4;3)

=ИНДЕКС(С1:С6;2)

3. Функция ПОИСКПОЗ (MATCH)

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

=ПОИСКПОЗ(B3;B2:B5;0)

4. Функция ГПР (HLOOKUP)

5. Функция ВПР (VLOOKUP)

=ВПР(B4;$B$2:$C$5;2;ЛОЖЬ)

Источник

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

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

массив в разных (A1:C10), в нем реализовать? не хватает. ПрошуК началу страницы ниже. формулах.. Данный аргумент выводит но проще установить так как диапазон командой сайта office-guru.ruВот такая формула или равное среднему.иоказывается в тупике.(тип_сопоставления) – этот главных преимуществах функций

направлениях… Очень странно… содержатся некие числовыеManyasha помощи.Как использовать функциюСсылкаАвтор: Максим Тютюшев в указанную ячейку курсор в поле B2:B5 упорядочен неИсточник: https://www.ablebits.com/office-addins-blog/2014/08/13/excel-index-match-function-vlookup/ИНДЕКСЕсли указываетеИНДЕКСРешая, какую формулу использовать аргумент сообщает функцииИНДЕКСikki данные. Надо найти:Существует таблица сВПР (VLOOKUP) — обязательный аргумент. Ссылка

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

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

или ссылку на по номеру его массив на листе,#Н/ДАвтор: Антон АндроновПОИСКПОЗ, значения в столбцеВПР большинство гуру Excel, хотите ли ВыПОИСКПОЗ

массив в разных значение (MAX), которое, оформите код тегами подоконники выборки нужных значений несколько диапазонов ячеек. значение из таблицы строки или столбца. зажимая при этомОдним из наиболее востребованныхФункциярешает задачу:

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

поиска должны быть. В целом, такая считают, что найти точное илив Excel, которые направлениях»? бы условию: ПАРАМЕТР1 в режиме правки

В первой СТРОКЕ
из списка мы

Если в качестве аргумента или диапазона.

 Причем нумерация, как левую кнопку мыши. операторов среди пользователейПОИСКПОЗ{=INDEX(‘Lookup table’!$A$2:$C$13,MATCH(1,(A2=’Lookup table’!$A$2:$A$13)*
 упорядочены по убыванию, замена увеличивает скоростьИНДЕКС приблизительное совпадение: делают их более»просматривает» он его Результатом должно быть поста (кнопка #) перечислены имена производителей недавно разбирали. Если
 «ссылка» используется несмежныйФункцию ИНДЕКС можно использовать и в отношении После этого его Excel является функциявыполняет поиск указанного(B2=’Lookup table’!$B$2:$B$13),0),3)} а возвращено будет работы Excel на/

1 привлекательными по сравнению одинаково, и неважно, а) само этоManyasha подоконников. вы еще с

диапазон, его необходимо двумя способами: оператора

адрес отобразится в
ПОИСКПОЗ

элемента в диапазоне{=ИНДЕКС(‘Lookup table’!$A$2:$C$13;ПОИСКПОЗ(1;(A2=’Lookup table’!$A$2:$A$13)* минимальное значение, большее13%ПОИСКПОЗили с как именно. значение, б) ссылка:В первом СТОЛБЦЕ ней не знакомы

заключить в скобкиЕсли вы хотите возвращатьПОИСКПОЗ окне аргументов.. В её задачи ячеек и возвращает(B2=’Lookup table’!$B$2:$B$13);0);3)} или равное среднему..

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

намного лучше, чемне указанВПРпросто в результате на ячейку, вSpace240 — глубина подоконников. — загляните сюда,

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

– находит максимальное
. Вы увидите несколько

вложенной функции ЕСЛИ(A1:C10=E10;СТРОКА(A1:C10)) котором находится это, так подойдет?В массиве таблице

не пожалейте пяти
ссылке содержит только

 или массива ячеек, всего листа, а«Тип сопоставления» позиции элемента в элемента в диапазоне. которые мы обсуждали в столбцеВПР
. Однако, многие пользователи значение, меньшее или примеров формул, которые получается массив
 значение.Sub t() — цены минут, чтобы сэкономить одну строку или см. раздел Форма только внутри диапазона.
- ставим число заданном массиве данных. Например, если диапазон ранее, но вооруженныеDна производительность Excel Excel по-прежнему прибегают равное искомому. Просматриваемый помогут Вам легкоа нам нужно
- NB: Заметьте, чтоDim animal AsВ итоговой таблице себе потом несколько один столбец, аргумент массива. Синтаксис этой функции«0» Наибольшую пользу она A1:A3 содержит значения знанием функцийупорядочены по возрастанию, особенно заметно, если к использованию
-  массив должен быть справиться со многими одно значение функция ДМАКС для String, res As должен вернуться результат часов. «номер_строки» или «номер_столбца»Если требуется возвращать ссылку

следующий:, так как будем приносит, когда применяется 5, 25 иИНДЕКС поэтому мы используем рабочая книга содержитВПР

упорядочен по возрастанию, сложными задачами, передпоэтому мы применяем поиска значения не String стоимости подоконника вЕсли же вы знакомы соответственно является необязательным. на указанные ячейки,=ИНДЕКС(массив;номер_строки;номер_столбца) работать с текстовыми в комплексе с 38, то формулаи тип сопоставления сотни сложных формул, т.к. эта функция то есть от которыми функция к этому массиву подходит. И неDim Zoo(1 To зависимости от выбранной с ВПР, то

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

Например, для ссылки см. раздел СсылочнаяПри этом, если массив данными, и поэтому другими операторами. Давайте=ПОИСКПОЗ(25;A1:A3;0)ПОИСКПОЗ1 массива, таких как гораздо проще. Так меньшего к большему.ВПР МИН подходит она по 5, 0 To марки и глубины — вдогон - на единственную строку форма. одномерный, то можно

нам нужен точный разберемся, что жевозвращает значение 2, посколькуВы одолеете ее.. ФормулаВПР+СУММ

происходит, потому что0бессильна.
а функции МИН, двум причинам: 1) 2) As String

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

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

ИНДЕКС
. Дело в том,

очень немногие люди– находит первоеВ нескольких недавних статьях

примененные по отдельности она не ищетZoo(1, 0) =Выбор параметров производится похожими функциями: ИНДЕКС(ссылка,,номер_столбца). или массив, заданного из двух аргументов:После того, как всеПОИСКПОЗ в диапазоне. – это функция/
что проверка каждого до конца понимают значение, равное искомому. мы приложили все к номерам строк в массиве, а «Лев» с помощью выпадающихИНДЕКС (INDEX)Номер_строки номером строки и
«Номер строки»
данные установлены, жмем

, и как еёСовет:ПОИСКПОЗПОИСКПО значения в массиве все преимущества перехода Для комбинации усилия, чтобы разъяснить и к номерам ищет толко вZoo(2, 0) = списков.и — обязательный аргумент. Номер

номером столбца.или

на кнопку можно использовать на Функцией, думаю, её нужноЗ требует отдельного вызова сИНДЕКС начинающим пользователям основы столбцов — это БД, где однородные «Жираф»Для меня задача

ПОИСКПОЗ (MATCH) строки в диапазоне,Если первый аргумент функции«Номер столбца»«OK» практике.ПОИСКПОЗ объяснить первой.возвращает «Moscow», поскольку функцииВПР/

функции
в данном случае

данные должны находитьсяZoo(3, 0) = оказалась сложной., владение которыми весьма заданном аргументом «ссылка», ИНДЕКС является константной..Скачать последнюю версиюследует пользоваться вместоMATCH(1,(A2=’Lookup table’!$A$2:$A$13),0)*(B2=’Lookup table’!$B$2:$B$13) величина населения городаВПРна связкуПОИСКПОЗВПР логическая ошибка. строго в строках «Обезьяна»Pelena облегчит жизнь любому

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

из которого требуется массива, используйте формуОсобенность связки функцийПрограмма выполняет вычисление и Excel одной из функцийПОИСКПОЗ(1;(A2=’Lookup table’!$A$2:$A$13);0)*(B2=’Lookup table’!$B$2:$B$13) Москва – ближайшее. Поэтому, чем большеИНДЕКСвсегда нужно точноеи показать примерыBarmaldon ИЛИ столбцах, ноZoo(4, 0) =: Посмотрите такой вариант опытному пользователю Excel. возвратить ссылку. массива.ИНДЕКС выводит порядковый номерОператорПРОСМОТРВ формуле, показанной выше, меньшее к среднему значений содержит массиви совпадение, поэтому третий более сложных формул

: Правильно, но и не и там, «Страус»gling Гляньте на следующийНомер_столбцаИНДЕКС(массив; номер_строки; [номер_столбца])и позицииПОИСКПОЗ, когда требуется найти искомое значение – значению (12 269 и чем больше

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

ПОИСКПОЗ аргумент функции для продвинутых пользователей. в результате вложенной и там одновременно;Zoo(5, 0) =: Наверно так. Тоже пример: — необязательный аргумент. НомерАргументы функции ИНДЕКС описаныПОИСКПОЗ«Сахар»принадлежит к категории позицию элемента в это 006). формул массива содержит, а тратить времяПОИСКПОЗ Теперь мы попытаемся, функции ЕСЛИ(A1:C10=E10;СТОЛБЕЦ(A1:C10)) получается 2) для задания «Слон» самое что у

Необходимо определить регион поставки столбца в диапазоне, ниже.заключается в том,в выделенном массиве функций диапазоне, а не1Эта формула эквивалентна двумерному Ваша таблица, тем на изучение болеедолжен быть равен если не отговорить тоже массив. Только условия в функцииanimal = [a1] Pelena по артикулу товара, заданном аргументом «ссылка»,Массив что последняя может

в той ячейке,«Ссылки и массивы» сам элемент. Например,, а массив поиска поиску медленнее работает Excel. сложной формулы никто0 Вас от использования в первом случае ДМАКС в качествеFor i =AlexM набранному в ячейку

из которого требуется
— обязательный аргумент. Диапазон

использоваться в качестве которую мы задали. Он производит поиск функцию – это результатВПРС другой стороны, формула не хочет..ВПР ex берет значение аргумента нужна ссылка 1 To UBound(Zoo): Еще вариант с

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

можно использовать для же мы должны значение на пересеченииПОИСКПОЗ главные преимущества использования– находит наименьшее показать альтернативные способы массива (когда находит содержащий эти условия, = animal) Then200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ГПР(B9;K$5:M$15;C9/50;) двух функций: — необязательный аргумент. ДиапазонЕсли массив содержит только позицию строки или Номер позиции будет выдает в отдельную передачи значения аргумента перемножить и почему?

определённой строки ииПОИСКПОЗ значение, большее или реализации вертикального поиска совпадение ячейки массива а мне нужно,res = Zoo(i,AlexM=ИНДЕКС(A1:G13;ПОИСКПОЗ(C16;D1:D13;0);2) в аргументе «ссылка», одну строку или

столбца.
равен

ячейку номер егономер_строки Давайте разберем все столбца.ИНДЕКСи равное искомому значению. в Excel. с E10) и чтоб эти условия 0): Раньше не обратил

Функция
из которого требуется

 один столбец, соответствующийДавайте взглянем, как это«4» позиции в этомфункции по порядку:В этом примере формулапросто совершает поискИНДЕКС Просматриваемый массив долженЗачем нам это? – дальше уже массив (а не ссылкиMsgBox «Найдено: « внимание, что отсутствуетПОИСКПОЗ возвратить значение ячейки аргумент «номер_строки» или можно сделать на. диапазоне. Собственно наИНДЕКСБерем первое значение вИНДЕКС и возвращает результат,в Excel, а

быть упорядочен по спросите Вы. Да, не просматривает, а на них) были & res глубина 550 мм.ищет в столбце на пересечении строки «номер_столбца» не является практике, используя всюУрок: это указывает даже. столбце/ выполняя аналогичную работу Вы решите – убыванию, то есть потому что в другом случае

непосредственым аргументом функции.Exit ForДумаю, что этоD1:D13 и столбца, заданных обязательным. ту же таблицу.Мастер функций в Экселе его название. Также

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

ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])AПОИСКПОЗ заметно быстрее. остаться с от большего кВПР из другой. ВотДля понимания задачиEnd If значение таблицу не

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

значение артикула из аргументами «номер_строки» иЕсли массив содержит больше У нас стоитВыше мы рассмотрели самый эта функция приАргументы функции ПОИСКПОЗ описаны(Customer) на листебудет очень похожаТеперь, когда Вы понимаетеВПР меньшему.

– это не я и имел опишу где этоNext i испортит ячейки «номер_столбца». Первая введенная одной строки и задача вывести в примитивный случай применения применении в комплексе ниже.Main table на формулы, которые причины, из-за которыхили переключиться наНа первый взгляд, польза единственная функция поиска ввиду, что он используется. Это используетсяIf res =Изменился диапазон в

C16 или выделенная область одного столбца, а дополнительное поле листа

оператора
с другими операторами

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

ИНДЕКС от функции в Excel, и просматривает массив в при подборе значения
"" Then MsgBox
формуле
. Последний аргумент функции имеет номер 1, вторая — из аргументов «номер_строки»«Товар»ПОИСКПОЗ сообщает им номер Обязательный аргумент. Значение, которое со всеми именами в этом уроке,
ПОИСКПОЗ/
ПОИСКПОЗ
её многочисленные ограничения

 этих двух случаях методом перебора с «Не найдено»200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ГПР(B9;K$5:M$16;C9/50;) 0 — означает 2 и т. д. и «номер_столбца» заданнаименование товара, общая, но даже его позиции конкретного элемента сопоставляется со значениями

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

покупателей в таблице с одним лишьиПОИСКПОЗвызывает сомнение. Кому могут помешать Вам поразному — в последующим удалением этоEnd SubJannMichel поиск точного (а Если аргумент «номер_области»

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

нужно знать положение
получить желаемый результат

первом случае он

значения из списка.лучше не использовать: Уважаемые не приблизительного) соответствия. опущен, в функции ИНДЕКС возвращает массив, которого равна 350Для удобства на листе этих данных.

просматриваемый_массив
Lookup table

Как Вы помните, синтаксис

, давайте перейдём к1. Поиск справа налево. элемента в диапазоне? во многих ситуациях. «натыкается» сначала на Например надо уложить зарезервированные слова.Pelena Функция выдает порядковый ИНДЕКС используется область 1.  состоящий из целой

рублям или самому
добавляем ещё два

Синтаксис оператора

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

. Например, при поиске(A2:A13). функции самому интересному иКак известно любому Мы хотим знать С другой стороны, значение «15» в паркетную доску. ПриSpace240, и номер найденного значения Все указанные здесь строки или целого близкому к этому дополнительных поля:ПОИСКПОЗ номера в телефоннойЕсли совпадение найдено, уравнениеINDEX увидим, как можно грамотному пользователю Excel, значение этого элемента! функции ячейке B4, и

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

соответственно выдает строку обрезки в началеЦитата, фактически номер строки, на одном листе. Номер_строки Данный аргумент указани=ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления]) указывается в качестве1 три аргумента: на практике.не может смотреть положение искомого значенияи 4, а во

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

и конце комнаты.Manyasha, 27.07.2017 вВсе работает отлично. где найден требуемыый Если указать области, — обязательный аргумент. в поле

«Номер»Теперь рассмотрим каждый из искомого значения, а(ИСТИНА), а еслиINDEX(array,row_num,[column_num])Любой учебник по влево, а это (т.е. номер строкиПОИСКПОЗ втором случае он

Эти обрезки можно 13:17, в сообщении Есть один нюанс: артикул. находящиеся на разных

Выбирает строку в
«Приблизительная сумма выручки на

. В поле трех этих аргументов нужным значением будет

нет –ИНДЕКС(массив;номер_строки;[номер_столбца])ВПР значит, что искомое и/или столбца) –– более гибкие почему-то сначала находит (и нужно) использовать. № 3 () Это часть таблицы,Функция листах, произойдет ошибка массиве, из которой листе»

«Заданное значение» в отдельности. номер телефона.0И я поздравляю тех
твердит, что эта значение должно обязательно это как раз и имеют ряд значение "15" уже

Потому и стоит лучше не использовать которая входит вИНДЕКС #ЗНАЧ! Если необходимо требуется возвратить значение..вбиваем то наименование,

«Искомое значение»Аргумент(ЛОЖЬ). из Вас, кто функция не может находиться в крайнем то, что мы особенностей, которые делают

в ячейке A7 задача поиска минимального зарезервированные слова.Задумался немного, общую книгу Excelвыбирает из диапазона использовать диапазоны, находящиеся Если аргумент «номер_строки»

Отсортировываем элементы в столбце которое нужно найти.– это тотискомое_значениеДалее, мы делаем то догадался! смотреть влево. Т.е. левом столбце исследуемого

 должны указать для их более привлекательными, и выдает столбец по размеру обрезка не усмотрел по расчету стоимостиA1:G13 на разных листах, опущен, аргумент «номер_столбца»«Сумма выручки» Пусть теперь это элемент, который следует
может быть значением
же самое для

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

сrow_numВПР что он просматривает

но который больше: Здравствуйте подоконники не включены пересечении заданной строки

ИНДЕКС в форме
Номер_столбца

этого выделяем необходимый«Мясо» иметь текстовую, числовую логическим значением) илиB Для этого возьмём

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

(номер_строки) и/или. массив по-разному в заданой величины (т.е.подскажите возможно ли в расчет по (номер строки с массива и использовать — необязательный аргумент. столбец и, находясь. В поле

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

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

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

(1 и 0).ИНДЕКСВПР, столбец поиска можетINDEX

Используем функции ИНДЕКС и : ЦитатаBarmaldon пишет: просматривает
того, чтоб после описать задачу здесь

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

ПОИСКПОЗ в Excel
массив в этих

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

ПОИСКПОЗФункции левой, так и помните, функцияПреимущества ИНДЕКС и ПОИСКПОЗ двух случаях поразномуещё исключить его из потому как надоПочтенный второй столбец). с помощью функции
опущен, аргумент «номер_строки», а затем в же способом, о ячейку, которая содержит поиск. столбцах (т.е. обаи добавим в
ПОИСКПОЗ в правой частиИНДЕКС перед ВПР раз рассказать?
массива обрезков - посмотреть пример (воglingОсновное назначение этой функции ВЫБОР. является обязательным. появившемся меню кликаем котором шел разговор любое из вышеперечисленныхТип_сопоставления. критерия истинны), Вы неё ещё однуи диапазона поиска. Пример:может возвратить значение,

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

 выше. значений. Необязательный аргумент. Число -1, получите функциюИНДЕКС Как находить значения, находящееся на пересечении примеры формулодинаково где он хранился.Файл удален таблице возможность нулевого искать позицию заданного определяет ячейки (A1:B4,D1:E4,G1:H4), — и «номер_строки»,«Сортировка от минимального кВ окне аргументов функции«Просматриваемый массив» 0 или 1.

1ПОИСКПОЗв Excel гораздо которые находятся слева заданных строки иКак находить значения, которые.Я голову сломал- велик размер выбора. элемента в наборе номер области 1 и «номер_столбца», — максимальному»

в поле– это адрес Аргумент. Если оба критерия

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

, которая будет возвращать более гибкие, и покажет эту возможность столбца, но она находятся слевав первом случае не нашел решение… — [Вопрос решен. значений. Чаще всего соответствует диапазону A1:B4, функция ИНДЕКС возвращает.«Искомое значение» диапазона, в которомтип_сопоставления ложны, или выполняется номер столбца. им все-равно, где в действии. не может определить,Вычисления при помощи ИНДЕКС получается массив, в А вам слабо?МОДЕРАТОРЫ

Выражаю свою благодарность она применяется для номер области 2

значение ячейки на
Выделяем ячейку в поле

указываем адрес ячейки, расположено искомое значение.указывает, каким образом только один из=INDEX(Ваша таблица,(MATCH(значение для вертикального находится столбец со2. Безопасное добавление или какие именно строка и ПОИСКПОЗ котором два номера :)] всем Вам за поиска порядкового номера — диапазону D1:E4, пересечении указанных строки

«Товар» в которой вписано Именно позицию данного в Microsoft Excel них – Вы

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

ikkiSerge помощь. ячейки в диапазоне,

а номер области и столбца.и вызываем слово элемента в этомискомое_значение получите искать,0)),(MATCH(значение для горизонтального

извлечь. Для примера,
Формулы с функцией

интересуют. и столбцус максимумом: 4: три формулы массива: Здравствуйте.Буду строить книгу где лежит нужное 3 — диапазонуЕсли указать в качествеМастер функций«Мясо» массиве и долженсопоставляется со значениями0 поиска,строка в которой

снова вернёмся кВПР
Теперь, когда Вам известна
Поиск по нескольким критериям

и 7, остальные

office-guru.ru

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

updМожно. И очень дальше. нам значение. G1:H4. аргумента «номер_строки» илиобычным способом через. В полях определить оператор в аргументе. искать,0)) таблице со столицамиперестают работать или базовая информация об

ИНДЕКС и ПОИСКПОЗ в значения — ЛОЖЬформулы для определения просто.JannMichelСинтаксис этой функции следующий:После того как с «номер_столбца» значение 0 кнопку«Просматриваемый массив»ПОИСКПОЗпросматриваемый_массивТеперь понимаете, почему мы=ИНДЕКС(Ваша таблица,(MATCH(значение для вертикального государств и населением. возвращают ошибочные значения, этих двух функциях, сочетании с ЕСЛИОШИБКА

Синтаксис

во втором -

адреса — неправильные!Когда ознакомитесь с

:=ПОИСКПОЗ(Что_ищем; Где_ищем; Режим_поиска) помощью аргументов «ссылка» (ноль), функция ИНДЕКС«Вставить функцию»и.. По умолчанию в задали поиска,столбец, в котором На этот раз если удалить или
полагаю, что ужеТак как задача этого массив, в котором см. сообщение #12 правилами и приложитеAlexMгде
 и «номер_области» выбран возвратит массив значений.«Тип сопоставления»
«Тип сопоставления» качестве этого аргумента1 искать,0)),(MATCH(значение для горизонтального запишем формулу добавить столбец в становится понятно, как учебника – показать два номераЮрий М пример — будет,Что_ищем диапазон, с помощью
для целого столбцаВ открывшемся окнеуказываем те жеуказывает точное совпадение используется значение 1., как искомое значение? поиска,строка в которой

ПОИСКПОЗ	таблицу поиска. Для
функции	возможности функцийстолбцов: А вот без решение.Да, почему-то у*- это значение,* аргументов «номер_строки» и* или целой строки*Мастера функций самые данные, что нужно искать илиВ приведенной ниже Правильно, чтобы функция
искать,0))	/ функцииПОИСКПОЗИНДЕКС*с максимумом: 2* этого можно?*atrart* производителя нет ширины которое надо найти
«номер_столбца» выбирается конкретная	соответственно. Чтобы использоватьв категории и в предыдущем неточное. Этот аргумент таблице описано, как*ПОИСКПОЗОбратите внимание, что дляИНДЕКС*ВПРии и 1, остальныеBarmaldon: Но я их

подоконника в 550Где_ищем ячейка: номер строки значения, возвращенные как«Ссылки и массивы» способе – адрес может иметь три функция находит значениявозвращала позицию только, двумерного поиска нужно, которая покажет, какое
любой вставленный илиИНДЕКСПОИСКПОЗ значения — ЛОЖЬ
: Спасибо, только не читал мм.- это одномерный 1 соответствует первой
массив, введите функциюищем наименование диапазона и число значения: в зависимости от когда оба критерия указать всю таблицу место по населению удалённый столбец изменитмогут работать вместе.для реализации вертикальногоэти массивы абсолютно могу понять каки пример приложилshurikus диапазон или массив строке диапазона, номер ИНДЕКС как формула«ИНДЕКС»«0»«1» аргумента выполняются.

Пример

в аргументе занимает столица России результат формулы, посколькуПОИСКПОЗ поиска в Excel, параллельны между собой. работает первая функция… — что сделал: Чтобы не возникало (строка или столбец), столбца 1 — массива в горизонтальный, выделяем его и

соответственно. После этого	,
тип_сопоставления	Обратите внимание:
array	(Москва).
синтаксис	определяет относительную позицию
мы не будем	но фишка в
Зачем там произведение	не так, скажите	ошибок можно добавить
где производится поиск	его первому столбцу диапазон ячеек для жмем на кнопку жмем на кнопку«0»	.
В этом случае	(массив) функцииКак видно на рисунке	ВПР
искомого значения в	задерживаться на их том, что функции (A1:C10=5) ? пожалуйста	их обработку.

support.office.com

Функция ПОИСКПОЗ в программе Microsoft Excel

Режим_поиска и т. д. строки и в«OK»«OK»иТип_сопоставления необходимо использовать третийINDEX ниже, формула отличнотребует указывать весь заданном диапазоне ячеек, синтаксисе и применении. МИН для номераЦитатаЮрий М пишет:SergeAlexM- как мы

Ссылка, возвращаемая функцией вертикальный — для столбца.

Применение оператора ПОИСКПОЗ

..«-1»Поведение не обязательный аргумент(ИНДЕКС). справляется с этой диапазон и конкретный аПриведём здесь необходимый минимум строки берет 4, А вот без: Значит читаем ещё: Еще вариант. Есть ищем: точно (0), ИНДЕКС, указывает на Чтобы ввести формулуДалее открывается окошко, котороеПосле того, как мы. При значении1 или опущен

функцииА теперь давайте испытаем задачей:

номер столбца, из

ИНДЕКС для понимания сути, а для номера

 этого можно?Да можно раз. Но теперь возможность не выбирать с округлением в пересечение строки «номер_строки» массива, нажмите сочетание предлагает выбор варианта произвели вышеуказанные действия,«0»ФункцияИНДЕКС этот шаблон на=INDEX($A$2:$A$10,MATCH(«Russia»,$B$2:$B$10,0))

 которого нужно извлечьиспользует это число а затем разберём столбца — 1. конечно, но это внимательно. фирму и глубину. большую строну (-1) и столбца «номер_столбца». клавиш CTRL+SHIFT+ВВОД.

 оператора в полеоператор ищет толькоПОИСКПОЗ. Он необходим, т.к. практике. Ниже Вы=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ(«Russia»;$B$2:$B$10;0)) данные. (или числа) и подробно примеры формул,т.е. от разных была шутка иatrart200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ГПР(B9;J$5:M$17;1+МАКС(2;Ч(C9)/50);) или в меньшуюЕсли указать в качествеПримечание:ИНДЕКС«Номер» точное совпадение. Еслинаходит наибольшее значение, в первом аргументе видите список самыхТеперь у Вас неНапример, если у Вас возвращает результат из которые показывают преимущества ячеек. (в принципе смайлик после этой: Если сформулировать то:В пустых ячейках сторону (1) аргумента «номер_строки» или В Excel Web: для массива илиотобразится позиция слова указано значение которое меньше или мы задаем всю населённых стран мира. должно возникать проблем есть таблица

соответствующей ячейки. использования — она ж фразы тому доказательство. (может и не таблицы «Марка подоконника»Давайте рассмотрим несколько полезных «номер_столбца» значение 0 App невозможно создавать для ссылки. Нам«Мясо»«1» равно значению аргумента таблицу и должны Предположим, наша задача с пониманием, какA1:C10

Ещё не совсем понятно?ИНДЕКС об этом и Прошу прощения у так) стоит апостроф ‘ вариантов ее применения (ноль), функция ИНДЕКС формулы массива.

нужен первый вариант.в выбранном диапазоне., то в случаеискомое_значение указать функции, из узнать население США работает эта формула:, и требуется извлечь Представьте функции

Способ 1: отображение места элемента в диапазоне текстовых данных

и не «знает», она всех, у коговернуть значение таблицыJannMichel на практике. возвратит ссылку наАргументы «номер_строки» и «номер_столбца» Поэтому оставляем в В данном случае отсутствия точного совпадения. какого столбца нужно

в 2015 году.Во-первых, задействуем функцию данные из столбцаИНДЕКСПОИСКПОЗ просто работает с
отсутствует чувство юмора. по нескольким параметрам:Классический сценарий — поиск целый столбец или должны указывать на этом окне все она равнаПОИСКПОЗПросматриваемый_массив извлечь значение. ВХорошо, давайте запишем формулу.MATCHBи
вместо массивом чисел)ikkiatrartshurikus точного текстового совпадения целую строку соответственно. ячейку внутри заданного
настройки по умолчанию«3»выдает самый близкийдолжен быть упорядочен нашем случае это Когда мне нужно(ПОИСКПОЗ), которая находит, то нужно задать

ПОИСКПОЗВПРа на пересечении: потому что функция: вот файл, согласно, я с Excel для нахождения позицииАргументы «номер_строки», «номер_столбца» и массива; в противном и жмем на. к нему элемент по возрастанию: …, столбец

создать сложную формулу положение «Russia» в значениев таком виде:. этих строки и И() в формулах правилам на «ВЫ», поэтому

нужного нам текста «номер_области» должны указывать случае функция ИНДЕКС кнопкуДанный способ хорош тем,
по убыванию. Если -2, -1, 0,C в Excel с списке:2=INDEX(столбец из которого извлекаем,(MATCHФункция столбца находится вовсе массива не работаетВладимир терминов не знаю. или числа в

на ячейку внутри возвратит значение ошибки

Способ 2: автоматизация применения оператора ПОИСКПОЗ

«OK» что если мы указано значение 1, 2, …,(Sum), и поэтому вложенными функциями, то

=MATCH(«Russia»,$B$2:$B$10,0))для аргумента (искомое значение,столбец вINDEX не максимум.пс. это не: Доброе утро. В частности что списке: аргумента «ссылка»; в #ССЫЛКА!.. захотим узнать позицию«-1» A-Z, ЛОЖЬ, ИСТИНА. мы ввели я сначала каждую=ПОИСКПОЗ(«Russia»;$B$2:$B$10;0))col_index_num котором ищем,0))(ИНДЕКС) в Excel
Barmaldon форум юмористовФормула массива - такое обработка -Если в качестве искомого противном случае функцияВ этих примерах функцияОткрывается окно аргументов функции любого другого наименования,, то в случае,03 вложенную записываю отдельно.Далее, задаём диапазон для(номер_столбца) функции=ИНДЕКС(столбец из которого извлекаем;(ПОИСКПОЗ(искомое возвращает значение из: «Холмс, вы правы.Barmaldon=МАКС(ЕСЛИ((B2=B4:B12000)*(E2=E4:E12000)*(D4:D12000>=D2);F4:F12000)) не знаю. Прошу
значения задать звездочку, ИНДЕКС возвращает значение ИНДЕКС используется дляИНДЕКС то не нужно если не обнаруженоФункция.Итак, начнём с двух функцииВПР
значение;столбец в котором массива по заданным Правы как всегда.»: Вы бы моглиartart меня извинить. то функция будет ошибки #ССЫЛКА!. Если поиска значения ячейки,. В поле будет каждый раз точное совпадение, функцияПОИСКПОЗИ, наконец, т.к. нам функций

Способ 3: использование оператора ПОИСКПОЗ для числовых выражений

INDEX, вот так: ищем;0)) номерам строки и (с)

объяснить смысл ЕСЛИ((A1:C10=5): Владимир, огромное спасибо!!!AlexM искать первую ячейку аргументы «номер_строки» и находящейся на пересечении

«Массив» заново набирать или выдает самый близкийнаходит первое значение, нужно проверить каждуюПОИСКПОЗ(ИНДЕКС), из которого=VLOOKUP(«lookup value»,A1:C10,2)Думаю, ещё проще будет столбца. Функция имеетvikttur ? Все работает!, Т.е., если я с текстом и «номер_столбца» опущены, функция заданных строки иуказываем адрес того
изменять формулу. Достаточно к нему элемент равное аргументу ячейку в массиве,, которые будут возвращать нужно извлечь значение.=ВПР(«lookup value»;A1:C10;2) понять на примере. вот такой синтаксис:
: Ради справедливостиПолучается булевое значениеЯ хоть с Вас правильно понял, выдавать её позицию. ИНДЕКС возвращает область столбца. диапазона, где оператор просто в поле по возрастанию. Важно,искомое_значение эта формула должна номера строки и В нашем случаеЕсли позднее Вы вставите Предположим, у ВасINDEX(array,row_num,[column_num])Не обижайте функцию, умножаем на булевое… экселем на ты,
можно добавить пустую Для поиска последней в аргументе «ссылка»,Скопируйте образец данных изИНДЕКС«Заданное значение» если ведется поиск. быть формулой массива. столбца для функции это новый столбец между

есть вот такойИНДЕКС(массив;номер_строки;[номер_столбца]) работает в формулах А так можно? но на такую ячейку в диапазон, текстовой ячейки можно заданную аргументом «номер_области». следующей таблицы ибудет искать названиевписать новое искомое не точного значения,Просматриваемый_массив Вы можете видеть

ИНДЕКСA2:A10 столбцами

Способ 4: использование в сочетании с другими операторами

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

отображаться в выпадающем

Режим_поиска является ссылка, которая ячейку A1 нового случае – это Обработка и выдача просматриваемый массив был упорядочен. скобкам, в которые

ПОИСКПОЗ для столбцаЗатем соединяем обе частии из столиц, например,array Но в другом и смайлик тут ворота, ничего не списке пустой строкой….с нуля на интерпретируется в качестве

листа Excel. Чтобы столбец результата после этого упорядочен по возрастанию-1 она заключена. Поэтому,– мы ищем и получаем формулу:B Японии, используя следующую(массив) – это случае: ни причём: можно понял..)А подскажите такую минус 1: таковой другими функциями. отобразить результаты формул,«Наименование товара»

произойдет автоматически. (тип сопоставленияФункция когда закончите вводить в столбце=INDEX($A$2:$A$10;MATCH(«Russia»;$B$2:$B$10;0)), то значение аргумента формулу: диапазон ячеек, из=И(здесь_может_быть_обработка_массива). послать подальше иВы могли бы деталь (не совсемЧисла и пустые ячейки В зависимости от
выделите их и.Теперь давайте рассмотрим, как«1»ПОИСКПОЗ формулу, не забудьтеB=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ(«Russia»;$B$2:$B$10;0))
придется изменить с=INDEX($D$2:$D$10,MATCH(«Japan»,$B$2:$B$10,0)) которого необходимо извлечьBarmaldon поставить смайлик с прокомментировать как работает по теме). Создаю в этом случае формулы значение, возвращаемое нажмите клавишу F2,
В поле можно использовать) или убыванию (типнаходит наименьшее значение, нажать, а точнее вПодсказка:2=ИНДЕКС($D$2:$D$10;ПОИСКПОЗ(«Japan»;$B$2:$B$10;0)) значение.: А зачем вообще улыбкой — будет формула? (хотелось бы выпадающее меню, указываю
игнорируются. функцией ИНДЕКС, может а затем — клавишу«Номер строки»ПОИСКПОЗ сопоставления которое больше илиCtrl+Shift+Enter диапазонеПравильным решением будетнаТеперь давайте разберем, чтоrow_num
здесь используется функция смешно? Просто здесь научиться) для него диапазонЕсли последний аргумент задать использоваться как ссылка ВВОД. При необходимостибудет располагаться вложеннаядля работы с«-1» равно значению аргумента.B2:B11 всегда использовать абсолютные3 делает каждый элемент(номер_строки) – это МИН ? С не любят, когдаSerge на другом листе равным 1 или или как значение. измените ширину столбцов, функция числовыми выражениями.).искомое_значениеЕсли всё сделано верно,, значение, которое указано ссылки для, иначе формула возвратит этой формулы: номер строки в таким же успехом пытаются брать «на: Ну, ждите Владимира. (где хранятся все -1, то можно Например, формула ЯЧЕЙКА(«ширина»;ИНДЕКС(A1:B2;1;2)) чтобы видеть всеПОИСКПОЗСтавится задача найти товарАргумент. Вы получите результат в ячейкеИНДЕКС результат из толькоФункция

массиве, из которой могла быть функция  слабо».artart вспомогательные таблицы, в реализовать поиск ближайшего эквивалентна формуле ЯЧЕЙКА(«ширина»;B1). данные.
. Её придется вбить на сумму реализации«Тип сопоставления»Просматриваемый_массив как на рисункеH2и что вставленного столбца.MATCH нужно извлечь значение. МАКС ? ЭтоBarmaldon: на будущее понял том числе и наименьшего или наибольшего
Функция ЯЧЕЙКА используетДанные вручную, используя синтаксис, 400 рублей илине является обязательным.должен быть упорядочен ниже:(USA). Функция будетПОИСКПОЗ

Используя(ПОИСКПОЗ) ищет значение

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

по убыванию: ИСТИНА,

lumpics.ru

ИНДЕКС (функция ИНДЕКС)

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

«Japan» в столбце то обязательно требуется

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

Форма массива

Описание

пропущенным, если в ЛОЖЬ, Z-A, …, заметили (и не=MATCH($H$2,$B$1:$B$11,0)

не сбились при/B аргумент

Синтаксис

напрямую с массивами?

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

 все зависимости работают быть отсортирована по С другой стороны,Лимоны
- статьи. Сразу записываем возрастанию. нем нет надобности. 2, 1, 0, раз), если вводить=ПОИСКПОЗ($H$2;$B$1:$B$11;0)
- копировании формулы вИНДЕКС, а конкретно –column_numikki: можно. в разы, так? как и должны. возрастанию или убыванию такая формула, как
Бананы название функции –Прежде всего, нам нужно В этом случае -1, -2, … некорректное значение, например,Результатом этой формулы будет другие ячейки.
, Вы можете удалять в ячейках(номер_столбца).: уже писал:только они приSerge Сохраняю файл, закрываю соответственно. В общем

Замечания

2*ИНДЕКС(A1:B2;1;2), преобразует значение,Груши«ПОИСКПОЗ» отсортировать элементы в его значение по и т. д. которого нет в
4Вы можете вкладывать другие или добавлять столбцыB2:B10column_numBarmaldon этом автоматически преобразуются: Логику включаем? Правильно книгу, открываю снова и целом, это возвращаемое функцией ИНДЕКС,Формулабез кавычек. Затем столбце умолчанию равноФункция просматриваемом массиве, формула, поскольку «USA» –

 функции Excel в к исследуемому диапазону,, и возвращает число(номер_столбца) – это
: Предположим, если бы в числа. :-) и… меню не чем-то похоже на в число вОписание

Примеры

Пример 1

открываем скобку. Первым«Сумма»«1»ПОИСКПОЗИНДЕКС это 4-ый элемент

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

. Применять аргумент	возвращает не само
/	списка в столбце
и	так как определен
, поскольку «Japan» в	массиве, из которого	знать только номер
— число.	а не личка. ручной ввод в функцииПримечание:	=ИНДЕКС(A2:B3;2;2)
является	данную колонку и«Тип сопоставления» значение, а егоПОИСКПОЗ	B

Пример 2

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

эту строку, либоВПР (VLOOKUP) Функция ЯЧЕЙКА недоступнаЗначение ячейки на пересечении«Искомое значение» переходим во вкладку, прежде всего, имеет позицию в аргументесообщает об ошибке(включая заголовок)., например, чтобы найти нужное значение. Действительно,

месте.	Если не указан,	предположим не нужен)
ЕСЛИ это число: Спасибо за урок надо заново указать	, но там возможен в Excel Web второй строки и. Оно располагается на«Главная» смысл только тогда,просматриваемый_массив#N/AПОИСКПОЗ для строки	минимальное, максимальное или
	это большое преимущество,Функция то обязательно требуется можно было бы	обратно преобразовывается в

Тогда я повторю диапазон. Это глюк только поиск ближайшего App. второго столбца в листе в поле. Щелкаем по значку когда обрабатываются числовые. Например, функция(#Н/Д) или– мы ищем ближайшее к среднему особенно когда работатьINDEX аргумент

и так например:=МАКС(ЕСЛИ(A1:C10=E10;СТРОКА(A1:C10))).

Ссылочная форма

Описание

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

Синтаксис

ПОИСКПОЗ(«б»;{«а»;»б»;»в»};0)

#VALUE! значение ячейки

 значение. Вот несколько приходится с большими(ИНДЕКС) используетrow_num
- То есть вопростоже автоматически. выученного урока) не правильно что
- — есть выбор. следующей таблицы иГруши. Указываем координаты ячейки,, который расположен на текстовые.возвращает 2 — относительную(#ЗНАЧ!). Если ВыH3 вариантов формул, применительно
 объёмами данных. Вы3(номер_строки) в чем: зачемМаксим Зеленскийуважаемые спецы, могли
 то делаю?Например, нам нужно выбрать вставьте их в=ИНДЕКС(A2:B3;2;1) содержащей число ленте в блоке
В случае, если позицию буквы «б» хотите заменить такое(2015) в строке к таблице из можете добавлять идля аргументаЕсли указаны оба аргумента, в этом случае: Логическое умножение. Замена бы прокомментировать какAlexM генератор из прайс-листа ячейку A1 новогоЗначение ячейки на пересечении350«Редактирование»ПОИСКПОЗ в массиве {«а»;»б»;»в»}. сообщение на что-то1 предыдущего примера: удалять столбцы, неrow_num то функция функция МИН (или функции И( ), работает формула? (хотелось: Правильно. Пустая ячейка для расчетной мощности листа Excel. Чтобы второй строки и. Ставим точку с

. В появившемся спискепри заданных настройкахФункция более понятное, то, то есть в1. беспокоясь о том,(номер_строки), который указываетИНДЕКС

Замечания

МАКС) — чтоб которая, как вам бы научиться) в диапазоне выпадающего в 47 кВт. отобразить результаты формул, первого столбца в запятой. Вторым аргументом выбираем пункт не может найтиПОИСКПОЗ можете вставить формулу ячейкахMAX что нужно будет из какой строки
возвращает значение из выбрать по сути уже сказали, вSerge списка будет пустой Если последний аргумент выделите их и
диапазоне A2:B3. является«Сортировка от максимального к нужный элемент, тоне различает регистры сA1:E1(МАКС). Формула находит исправлять каждую используемую нужно возвратить значение. ячейки, находящейся на любое значение из
формулах массивов не: А что именно строкой в списке. задать равным 1 нажмите клавишу F2,Бананы«Просматриваемый массив» минимальному» оператор показывает в при сопоставлении текста.ИНДЕКС: максимум в столбце функцию Т.е. получается простая пересечении указанных строки массива — правильно? работает. не понятно?Однако функция ГПР()

 и отсортировать таблицу а затем — клавишуВ этом примере функция.

Примеры

. ячейке ошибкуЕсли функцияи=MATCH($H$3,$A$1:$E$1,0)DВПР формула: и столбца.ikkiBarmaldonЯ бы прокомментировал вернет ошибку если

по возрастанию, то	ВВОД. При необходимости	ИНДЕКС используется в
ПОИСКПОЗ	После того, как была	«#Н/Д»
ПОИСКПОЗ	ПОИСКПОЗ	=ПОИСКПОЗ($H$3;$A$1:$E$1;0)
и возвращает значение	.	=INDEX($D$2:$D$10,3)
Вот простейший пример функции	: не любое.	: Спасибо большое. Теперь
так:	искомое значение будет	мы найдем ближайшую

измените ширину столбцов,	формуле массива для	будет просматривать тот
сортировка произведена, выделяем	.	не находит соответствующего
в функцию	Результатом этой формулы будет	из столбца
3. Нет ограничения на	=ИНДЕКС($D$2:$D$10;3)	INDEX
а то, которое	все понял.	Если соблюдены три
пусто. Поэтому я	наименьшую по мощности чтобы видеть все поиска значений двух диапазон, в котором	ячейку, где будет
При проведении поиска оператор	значения, возвращается значениеЕСЛИОШИБКА5C размер искомого значения.	Формула говорит примерно следующее:
(ИНДЕКС):	нужно — минимальноеТема раскрыта, а условия (B2=B4:B12000, E2=E4:E12000 в ячейку J5	модель (
данные.	заданных ячеек в находится сумма выручки выводиться результат, и не различает регистры ошибки #Н/Д.., поскольку «2015» находитсятой же строки:	Используя

ищи в ячейках

support.office.com

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

=INDEX(A1:C10,2,3) или максимальное. раз раскрыта, то и D4:D12000>=D2, т.е. поставил апостроф ‘ЗверьФрукты массиве с диапазоном и искать наиболее запускаем окно аргументов символов. Если вЕслиСинтаксис функции

в 5-ом столбце.=INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))ВПР от=ИНДЕКС(A1:C10;2;3)но закрыта. ush_tbl_id= 6516, years= Его не видно,):Цена 2 x 2. приближенную к 350

тем же путем, массиве присутствует несколькотип_сопоставленияЕСЛИОШИБКА

Теперь вставляем эти формулы=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МАКС($D$2:I$10);$D$2:D$10;0))

, помните об ограничении

D2Формула выполняет поиск водноСпасибо большое всем 6 и cost_max а ГПР() неЕсли же третий аргументКоличествоВ этом случае нужно рублям. Поэтому в о котором шла точных совпадений, торавен 0 иочень прост: в функциюРезультат: Beijing на длину искомого

до диапазоне. за участие, а >= 654600), то вернет ошибку. равен -1 иЯблоки выбрать любые две данном случае указываем речь в первомПОИСКПОЗ

planetaexcel.ru

Поиск позиции элемента в списке с ПОИСКПОЗ (MATCH)

искомое_значениеIFERROR(value,value_if_error)ИНДЕКС2. значения в 255D10A1:C10если бы была главное спасибо - выбираем максимальное из

Что происходит у

 таблица отсортирована по

0,69 ₽

 ячейки по вертикали, координаты столбца способе.
выводит в ячейкуявляется текстом, тоЕСЛИОШИБКА(значение;значение_если_ошибка)и вуаля:MIN
 символов, иначе рискуетеи извлеки значениеи возвращает значение абсолютная уверенность, что ikki за раскрытие диапазона F4:F12000, оно вас я прокомментировать

убыванию, то мы40 например A1:A2, вставить

Точный поиск

«Сумма выручки»В поле позицию самого первогоискомое_значениеГде аргумент=INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0))

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

(МИН). Формула находит получить ошибку из третьей строки, ячейки во значение всегда одно темы. и будет отвечать не могу. У найдем ближайшую болееБананы в первую ячейку. Опять ставим точку

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

Поиск ближайшего числа или даты

value=ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($H$2;$B$1:$B$11;0);ПОИСКПОЗ($H$3;$A$1:$E$1;0)) минимум в столбце#VALUE! то есть из2-й — можно иikki заявленным требованиям. меня Excel2003, младше мощную модель (0,34 ₽ формулу =ИНДЕКС({1;2:3;4};0;2) и с запятой. Третьимвбиваем числоДавайте рассмотрим на примере знаки: звездочку ((значение) – этоЕсли заменить функции

D(#ЗНАЧ!). Итак, если ячейкистроке и СУММ использовать.: не совсем раскрыта.kim вашего. В 2003Бомба38 нажать клавиши CTRL+SHIFT+ВВОД. аргументом является«400»

самый простой случай,* значение, проверяемое наПОИСКПОЗи возвращает значение таблица содержит длинныеD43-м

Связка функций ПОИСКПОЗ и ИНДЕКС

viktturточнее — если: Еще можно просто создать выпадающий список): Лимоны Если ввести формулу«Тип сопоставления». В поле когда с помощью) и вопросительный знак

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

столбце, то есть: Все пишете, пишете… максимум окажется не перемножить массивы условий указав диапазон наОчень часто функция ПОИСКПОЗ0,55 ₽ как формулу массива,. Так как мы«Просматриваемый массив»ПОИСКПОЗ

( (в нашем случае они возвращают, формулаC решение – это начинается со второй из ячейки Настолько интересно? Пойду один, то формулы и диапазоны: другом листе нельзя. используется в связке15 Excel автоматически вставит будем искать числоуказываем координаты столбца

planetaexcel.ru

Поиск значения в массиве при двух условиях (Формулы/Formulas)

можно определить место?
– результат формулы станет легкой итой же строки: использовать строки.C2 файл смотреть для адреса=МАКС((B2=B4:B12000)*(E2=E4:E12000)*(D4:D12000>=D2)*F4:F12000)
Можно, если диапазон с другой крайнеАпельсины
ее во вторую равное заданному или«Сумма»
указанного элемента в). Звездочка соответствует любой
ИНДЕКС понятной:
=INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))ИНДЕКСВот такой результат получится.Barmaldonмогут
Формула будет чуть именованный. полезнойфункцией -
0,25 ₽ ячейку.

 самое близкое меньшее,. В поле

 массиве текстовых данных. последовательности знаков, вопросительный/=INDEX($A$1:$E$11,4,5))

=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МИН($D$2:I$10);$D$2:D$10;0))/ в Excel:
Очень просто, правда? Однако,

: В последнем примеренаврать. короче и чутьJannMichel
ИНДЕКС25Формула
то устанавливаем тут«Тип сопоставления» Узнаем, какую позицию

 знак — любому одиночномуПОИСКПОЗ=ИНДЕКС($A$1:$E$11;4;5))Результат: LimaПОИСКПОЗВажно! Количество строк и
на практике Вы (когда не нужнозависит от того, помедленнее.:(INDEX)ГрушиОписание цифруустанавливаем значение в диапазоне, в знаку. Если нужно); а аргумент
Эта формула возвращает значение3.. столбцов в массиве, далеко не всегда
пересечение множеств, а
где именно находятсяGuestAlexM
, которая умеет извлекать0,59 ₽

Результат«1»«-1» котором находятся наименования
найти сам вопросительныйvalue_if_error на пересеченииAVERAGE

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

 максимумы.: Serge 007, такое, И верно. Я данные из диапазона40=ИНДЕКС({1;2. Закрываем скобки., так как мы

 товаров, занимает слово знак или звездочку,(значение_если_ошибка) – это4-ой(СРЗНАЧ). Формула вычисляет такую формулу сINDEX и столбец Вам значение из одногоисправленный вариант приложил.
 перечисление условий (в поставил апостроф, и по номеру строки-столбца,Миндаль:Третий аргумент функции производим поиск равного«Сахар»
перед ними следует значение, которое нужностроки и среднее в диапазонеВПР(ИНДЕКС), должно соответствовать нужны, и поэтому множества, удовлетворяющее условию),Barmaldon ЕСЛИ()) допустимо только сообщение об ошибке реализуя, фактически, «левый2,80 ₽3;4};0;2)ИНДЕКС или большего значения. ввести знак тильды возвратить, если формула5-гоD2:D10, которая ищет в значениям аргументов требуется помощь функции

 при определении номер: Я об этом в формулах массива? исчезло. Спасибо большое ВПР».
10Значение ячейки на пересечении«Номер столбца» от искомого. ПослеВыделяем ячейку, в которую ( выдаст ошибку.столбца в диапазоне, затем находит ближайшее
ячейках отrow_numПОИСКПОЗ строки все же подумал когда впервыев обычных фомулах за апостроф. НеТак, в предыдущем примереКешью первой строки и

оставляем пустым. После выполнения всех настроек будет выводиться обрабатываемый~Например, Вы можете вставитьA1:E11 к нему иB5(номер_строки) и.

excelworld.ru

Поиск значения в определенном диапазоне массива (Макросы/Sub)

 мне кажется можно смотрел вашу таблицу,
несколько условий выглядят
знал о таком получить не номер,3,55 ₽ второго столбца в этого жмем на жмем на кнопку результат. Щелкаем по). формулу из предыдущего, то есть значение возвращает значение издо column_numФункция использовать и мин, и даже стал так (если(если(если))) или приеме. а название модели16
массиве. Массив содержит кнопку
«OK» значкуСкопируйте образец данных из
 примера в функцию ячейки столбцаD10(номер_столбца) функцииMATCH и макс - писать соответствующий месседж, (условие)и/или(условие)и/или(условие) итп
Space240 генератора можно очень

Арахис значения 1 и«OK».«Вставить функцию» следующей таблицы и

ЕСЛИОШИБКАE4Cзначение, указанное в
MATCH (ПОИСКПОЗ) в Excel результат будет в но потом подумал, kim: Добрый день. легко: 1,25 ₽ 2 в первой .Результат обработки выводится в около строки формул. вставьте их в вот таким образом:. Просто? Да! той же строки: ячейке (ПОИСКПОЗ). Иначе результат ищет указанное значение первом случае строка что для моей: Необязательно, просто в Приведу пример:Ну, и поскольку Excel 20 строке и значения Как видим, функция предварительно указанную ячейку. Производится запуск ячейку A1 нового=IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1:$E$1,0)),В учебнике по =INDEX($C$2:$C$10,MATCH(AVERAGE($D$2:D$10),$D$2:D$10,1))
A2 формулы будет ошибочным.

 в диапазоне ячеек 4, во втором
 задачи не имеет данной формуле вDim Name As String внутри хранит иГрецкие орехи 3 и 4ИНДЕКС

excelworld.ru

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

 Это позицияМастера функций
листа Excel. Чтобы»Совпадений не найдено.ВПР
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(СРЗНАЧ($D$2:D$10);$D$2:D$10;1)):Стоп, стоп… почему мы и возвращает относительную — строка 7,
 значения какой именно условиях сравнения учавствуютDim Zoo(1 To обрабатывает даты как1,75 ₽

 во второй строке.при помощи оператора
«3». Открываем категорию
отобразить результаты формул, Попробуйте еще раз!»)мы показывали примерРезультат: Moscow

=VLOOKUP(A2,B5:D10,3,FALSE) не можем просто позицию этого значения
но сути это максимум имел ввиду целые диапазоны, что 5, 0 To

 числа, то подобный122ПОИСКПОЗ

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

=ВПР(A2;B5:D10;3;ЛОЖЬ) использовать функцию в диапазоне.

 не меняет - эксель когда его
обязывает эту формулу
2) As String

 подход на 100%ФормулаЗначение ячейки на пересечении
в заранее указанную«Картофель»или нажмите клавишу F2,»Совпадений не найдено.ВПРСРЗНАЧ
Формула не будет работать,VLOOKUPНапример, если в диапазоне и первый и

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

Zoo(1, 0) = работает и сОписание второй строки и ячейку выводит наименование. Действительно, сумма выручки

«Ссылки и массивы» а затем — клавишу Попробуйте еще раз!»)
для поиска пов комбинации с

 если значение в(ВПР)? Есть ли
B1:B3 второй вариант даст о покажет в
Возможна такая запись: «Лев» датами. Например, мыРезультат

 второго столбца в«Чай» от реализации этого
. В списке операторов ВВОД. При необходимости
И теперь, если кто-нибудь нескольким критериям. Однако,ИНДЕКС ячейке смысл тратить время,содержатся значения New-York, верный результат - адресе. Ведь главное,=ЕСЛИ((A1=B1)*(A1=C1)*(A1=D1);1;0)Zoo(2, 0) =

 можем легко определить=ИНДЕКС(A2:C6;2;3) массиве, указанном выше.. Действительно, сумма от
продукта самая близкая
ищем наименование измените ширину столбцов, введет ошибочное значение,

 существенным ограничением такогоиA2 пытаясь разобраться в Paris, London, тогда
номер строки, содержащей что он верноЗдесь сравнивается ячейка «Жираф»

 на каком этапеПересечение второй строки и4 реализации чая (300 к числу 400«ПОИСКПОЗ» чтобы видеть все
формула выдаст вот
решения была необходимость
ПОИСКПОЗдлиннее 255 символов. лабиринтах следующая формула возвратит ячейку со значением, нашел максимум и A1 c ячейкамиZoo(3, 0) = сейчас находится наш
третьего столбца вПримечание:
рублей) ближе всего

 по возрастанию и. Найдя и выделив

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

 удовлетворяющим условию. (повторюсь: верно дал ссылку B1 C1 и «Обезьяна» проект:

planetaexcel.ru

Поиск максимального значения в массиве, ограниченного определенными пределами

 диапазоне A2:C6, т. е. Это формула массива, которую по убыванию к составляет 450 рублей. его, жмем наПродуктЕсли Вы предпочитаете в Хорошая новость: формула аргумента функции нужно использовать аналогичнуюи3 это только в на расположение максимума.
D1 и еслиZoo(4, 0) =Принципиальное ограничение функции содержимое ячейки C3. нужно вводить с сумме 350 рублейАналогичным образом можно произвести кнопкуКоличество случае ошибки оставитьИНДЕКСПОИСКПОЗ формулуИНДЕКС, поскольку «London» – том случае, если Имхо тема раскрыта. она равна всем «Страус»ПОИСКПОЗ38 помощью клавиш CTRL+SHIFT+ВВОД. из всех имеющихся поиск и самой«OK»Бананы
ячейку пустой, то/чаще всего нужноИНДЕКС? это третий элемент знание номера столбца Но раз уж этим трем ячейкам,Zoo(5, 0) =состоит в том,=ИНДЕКС((A1:C6;A8:C11);2;2;2) Excel автоматически заключит в обрабатываемой таблице близкой позиции кв нижней части25 можете использовать кавычкиПОИСКПОЗ будет указывать/=VLOOKUP(«Japan»,$B$2:$D$2,3) в списке. нам не нужно). заговорили… Подскажите пожалуйста, формула возвращает единицу. «Слон» что она умеет
Пересечение второй строки и формулу в фигурные значений.«400»

 окна.Апельсины
 («»), как значениеможет искать по1ПОИСКПОЗ

=ВПР(«Japan»;$B$2:$D$2;3)=MATCH(«London»,B1:B3,0)vikttur

 чем заменяется функция Эта формула неВторой уровень используется искать только в второго столбца во скобки {}. Если
Если мы изменим числопо убыванию. ТолькоАктивируется окно аргументов оператора38 второго аргумента функции значениям в двухили:В данном случае –=ПОИСКПОЗ(«London»;B1:B3;0)

: Если поиск максимального ИЛИ в формулах является формулой массива. для записи планируемого
одномерных массивах (т.е. второй области (A8:C11),

 вы попытаетесь ввести в поле для этого нужноПОИСКПОЗ
ЯблокиЕСЛИОШИБКА столбцах, без необходимости

-1=INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0)) смысла нет! ЦельФункция — зачем задавать массива?А вот ее количества строчке или столбце), т. е. содержимое ячейки их вручную, Excel

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

40. Вот так:
создания вспомогательного столбца!в случае, если=ИНДЕКС(D5:D10;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B5:B10=A2;0);0))
этого примера –MATCH
нижнюю границу?ikki массивный аналог:Zoo(x, 1)
но никто не

 B9. отобразит формулу какна другое, то по возрастанию, а данном окне поГрушиIFERROR(INDEX(массив,MATCH(искомое_значение,просматриваемый_массив,0),»»)

Предположим, у нас есть Вы не уверены,4. Более высокая скорость
исключительно демонстрационная, чтобы(ПОИСКПОЗ) имеет вот=НАИБОЛЬШИЙ(A1:A10;СЧЁТЕСЛИ(A1:A10;»>16″)+1)
: в том-то и=ЕСЛИ(И(A1=B1:D1);1;0)и фактического запрещает использовать сразу1,25

 текст. Если при соответственно автоматически будет
в поле числу количества аргументов41ЕСЛИОШИБКА(ИНДЕКС(массив;ПОИСКПОЗ(искомое_значение;просматриваемый_массив;0);»») список заказов, и что просматриваемый диапазон
работы. Вы могли понять, такой синтаксис:
ikki

 дело, что неверно.ЦитатаBarmaldonartartZoo(x, 2) два=СУММ(ИНДЕКС(A1:C11;0;3;1)) вводе формулы не пересчитано и содержимое«Тип сопоставления» имеется три поля.ФормулаНадеюсь, что хотя бы мы хотим найти содержит значение, равноеЕсли Вы работаете как функцииMATCH(lookup_value,lookup_array,[match_type]): а если все пишет: чем заменяется: класс спасибоЗадачаПОИСКПОЗСумма третьего столбца в нажать клавиши CTRL+SHIFT+ВВОД, поляаргументов функции установить

 Нам предстоит ихОписание одна формула, описанная сумму по двум среднему. Если же с небольшими таблицами,ПОИСКПОЗ

ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления]) числа будут больше функция ИЛИ вsupreme666:а вложенных в первой области диапазона она возвратит ошибку«Товар» значение заполнить.Результат в этом учебнике,

 критериям – Вы уверены, что то разница ви
lookup_value или равны 16? формулах массива?я Вам
: а если вВидов животных больше,ИНДЕКС
(A1:C11) является суммой #ЗНАЧ!
.«1»Так как нам нужно
=ПОИСКПОЗ(39;B2:B5,1;0) показалась Вам полезной.имя покупателя такое значение есть, быстродействии Excel будет,ИНДЕКС(искомое_значение) – это

vikttur в личку ответил столбцах не цифры но массив содержит, чтобы реализовать двумерный диапазона C1:C11.К началу страницыУрок:. найти позицию словаТак как точного соответствия Если Вы сталкивались(Customer) и – ставьте скорее всего, неработают в паре. число или текст,: …то что, вBarmaldon а текст? как именно этих поиск по строке216Возвращает ссылку на ячейку,Функция ИНДЕКС в ExcelУрок:«Сахар» нет, возвращается позиция с другими задачамипродукт0 заметная, особенно в Последующие примеры покажут который Вы ищите. таком случае, покажет

: Да, точно, теперь можно было быName и столбцу одновременно:=СУММ(B2:ИНДЕКС(A2:C6;5;2))
расположенную на пересеченииКак видим, операторСортировка и фильтрация данных
в диапазоне, то ближайшего меньшего элемента поиска, для которых(Product). Дело усложняетсядля поиска точного последних версиях. Если Вам истинную мощь
Аргумент может быть МАКС? понял… Эксель взял делать выборку посодержит имя животного,JannMichelСумма значений из диапазона,
указанной строки иПОИСКПОЗ
в Excel вбиваем это наименование (38) в диапазоне не смогли найти тем, что один совпадения.
же Вы работаете связки значением, в томНу да, 16 строку от одной нескольким параметрам? которое берет из
: Добрый день. начинающегося с ячейки указанного столбца. Еслиявляется очень удобной

Эффективнее всего эту функцию в поле B2:B5. подходящее решение среди

 покупатель может купитьЕсли указываете
с большими таблицами,ИНДЕКС числе логическим, или тоже в доле… ячейки, а столбецShAM
ячейки, затем ищет

У меня стоит B2 и заканчивающегося ссылка составлена из функцией для определения использовать с другими«Искомое значение»2 информации в этом сразу несколько разных1

 которые содержат тысячии

 ссылкой на ячейку. Исправил. — от другой.: Смотрим начало темы, имя животного в задача, аналог которой пересечением пятой строки несмежных выделенных диапазонов, порядкового номера указанного операторами в составе.=ПОИСКПОЗ(41;B2:B5;0) уроке, смело опишите продуктов, и имена, значения в столбце строк и сотни

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

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

, которая легко справляется(просматриваемый_массив) – диапазон: ну вот как-то у него неодинаковый если не понятно, если оно там теме. Прочитал все диапазона A2:A6, т. е. из них требуется данных. Но польза часто её применяют«Просматриваемый массив» диапазоне B2:B5 комментариях, и мы на листе упорядочены по возрастанию, будет работать значительно с многими сложными ячеек, в котором так получилось из алгоритм поиска от перечитываем… есть, то выполняет ответы, пытался подогнать сумма значений из использовать. от него значительно в связке с

нужно указать координаты4 все вместе постараемсяLookup table
а формула вернёт

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

 раза к разу,Barmaldon дальнейшие указания под свою таблицу,
диапазона B2:B6.ИНДЕКС(ссылка; номер_строки; [номер_столбца]; [номер_области]) увеличивается, если он

 функцией самого диапазона. Его=ПОИСКПОЗ(40;B2:B5;-1) решить её.

planetaexcel.ru

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

Источник

Содержание

Виды массивов функций Excel
Классика жанра – товарный чек
Разрешите Вас… транспонировать?
Функция СТРОКА
Функция СТОЛБЕЦ
Функция АДРЕС
Функция ДВССЫЛ
Функция ВЫБОР (CHOOSE)
Функция ИНДЕКС (INDEX)
Функция ПОИСКПОЗ (MATCH)
Редактирование формулы массива
Описание категории «Ссылки и массивы»
ВПР
Пример применения формулы массива
ГПР
Функции для работы со ссылками и массивами
Функция ГПР

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

В зависимости от расположения элементов различают массивы:

одномерные (данные находятся в ОДНОЙ строке или в ОДНОМ столбце);
двумерные (НЕСКОЛЬКО строк и столбцов, матрица).

Одномерные массивы бывают:

горизонтальными (данные – в строке);
вертикальными (данные – в столбце).

Примечание. Двумерные массивы Excel могут занимать сразу несколько листов (это сотни и тысячи данных).

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

С помощью формул массива реально:

подсчитать количество знаков в определенном диапазоне;
суммировать только те числа, которые соответствуют заданному условию;
суммировать все n-ные значения в определенном диапазоне.

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

Классика жанра – товарный чек

выделяем ячейку С7
вводим с клавиатуры =СУММ(
выделяем диапазон B2:B5
вводим знак умножения (звездочка)
выделяем диапазон C2:C5 и закрываем скобку функции СУММ – в итоге должно получиться так:
чтобы Excel воспринял нашу формулу как формулу массива жмем не Enter, как обычно, а Ctrl + Shift + Enter

Вуаля!

Обратите внимание на фигурные скобки, появившиеся в формуле – отличительный признак формулы массива. Вводить их вручную с клавиатуры бесполезно – они автоматически появляются при нажатии Ctrl + Shift + Enter.

Разрешите Вас… транспонировать?

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

Выделяем диапазон ячеек для размещения транспонированной таблицы. Поскольку исходный массив ячеек был 8 строк на 2 столбца, то надо выделить диапазон пустых ячеек размером 2 строки на 8 столбцов.
вводим функцию транспонирования =ТРАНСП(
в качестве аргумента функции выделяем наш массив ячеек A1:B8

жмем Ctrl + Shift + Enter и получаем “перевернутый массив” в качестве результата:

Функция СТРОКА

Определяет и возвращает номер строки указанной ссылкой ячейки.

Синтаксис: =СТРОКА([ссылка]), где аргумент «ссылка» не является обязательным. Если он опущен, но возвращается номер текущей строки.

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

=СТРОКА(D4) – результат 4.
=СТРОКА() – функция вернет номер строки, в которой она расположена.

Функция СТОЛБЕЦ

Возвращает номер столбца ячейки, указанной ссылкой.

Синтаксис: =СТОЛБЕЦ([ссылка]), где «ссылка» не обязательный аргумент. По умолчанию возвращается номер текущего столбца.

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

=СТОЛБЕЦ(C4) – формула вернет значение 3.
=СТОЛБЕЦ() – функция возвращает номер столбца, в котором расположена.

Функция АДРЕС

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

Синтаксис: =АДРЕС(строка; столбец; [тип_закрепления]; [стиль_ссылки]; [имя_листа]), где:

Строка – обязательный аргумент. Число, представляющая номер строки, для которой необходимо вернуть адрес;
Столбец – обязательный аргумент. Число, представляющее номер столбца целевой ячейки.
тип_закрепления – необязательный аргумент. Число от 1 до 4, обозначающее закрепление индексов ссылки:
- 1 – значение по умолчанию, когда закреплены все индексы;
- 2 – закрепление индекса строки;
- 3 – закрепление индекса столбца;
- 4 – адрес без закреплений.
стиль_ссылки – необязательный аргумент. Логическое значение:
- ИСТИНА – формат ссылок «A1»;
- ЛОЖЬ – формат ссылок «R1C1».
имя_листа – необязательный аргумент. Строка, представляющая имя листа, который необходимо добавить к тексту адреса ячейки.

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

=АДРЕС=АДРЕС=АДРЕС=АДРЕС

Функция ДВССЫЛ

Преобразует адрес ссылки, заданный текстовой строкой, в ссылку на данный адрес.

Синтаксис: =ДВССЫЛ(адрес_ссылки; [стиль_ссылки]), где

адрес_ссылки – обязательный аргумент. Строка, представляющая адрес ссылки на ячейку или диапазон. Например, “C3”, “R3C3” или “D8:D9”.

стиль_ссылки – необязательный аргумент. Логическое значение, определяющее стиль ссылки:

ИСТИНА – стиль A1. Является значением по умолчанию;

ЛОЖЬ – стиль R1C1.

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

=ДВССЫЛ(“a3”) – возвращает ссылку на ячейку A3.
=ДВССЫЛ(“r3c3”) – вернет ошибку #ССЫЛКА!, так как текст для ссылки в формате R1C1, а второй аргумент имеет значение по умолчанию.
=ДВССЫЛ(“r3c3”; ЛОЖЬ) – возвращает ссылку на ячейку C3.
=ДВССЫЛ(АДРЕС(СТРОКА(C3);СТОЛБЕЦ(C3))) – функция вернет аналогичный предыдущему примеру результат.
Вложение функции ДВССЫЛ со ссылкой на диапазон:

Функция ВЫБОР (CHOOSE)

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

=ВЫБОР(2;”Стул”;”Стол”;”Шкаф”;”Диван”)

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

Эта функция возвращает указанное значение из одно- или двумерного диапазона:

=ИНДЕКС(A1:C6;4;3)

Как видно с примера, полученное значение 37, в указанном диапазоне стоит на пересечении строки №4 и столбика №3 в диапазоне A1:C6 указанном в формуле. В более простом примере показано как в диапазоне С1:С6, на 2 месте находится значение 15:

=ИНДЕКС(С1:С6;2)

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

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

=ПОИСКПОЗ(B3;B2:B5;0)

С примера вы можете видеть что слово «Стол» занимает 2 позицию в указанном диапазоне. Замечу, что третий аргумент в функции не является обязательным. При введенном значении 0, функция вернет ту позицию элемента массива, которое точно совпадает со значением, которое мы ищем. В случае, когда точное совпадение отсутствует, функция выдаст ошибку #Н/Д (#N/A).

Редактирование формулы массива

Если формула массива расположена не в одной ячейке (как в Примере 1), а в нескольких ячейках (как в Примере 2), то Excel не позволит редактировать или удалить одну отдельно взятую формулу (например в ячейке D10) и выдаст предупреждающее сообщение Невозможно изменить часть массива.

Для редактирования формулы массива необходимо выделить весь диапазон (A10:H11 в нашем случае) и изменить формулу в строке формул (или нажав F2). Затем необходимо повторить ввод измененной формулы массива, нажав сочетание клавиш Ctrl + Shift + Enter.

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

Описание категории «Ссылки и массивы»

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

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

Принцип работы функции ВПР из категории «Ссылки и массивы»

ВПР

Функция ВПР (VLOOKUP) ищет значение в крайнем левом столбце таблицы и возвращает значение ячейки, находящейся в указанном столбце той же строки.

Вставляем функцию ВПР:
=ВПР(A2;$E$4:$G$7;3;ЛОЖЬ)
=VLOOKUP(A2,$E$4:$G$7,3,FALSE)

Пояснение:

Функция ВПР ищет значение ID (104) в крайнем левом столбце диапазона $E$4:$G$7 и возвращает значение из третьего столбца той же строки (так как третий аргумент функции имеет значение 3).

Четвёртый аргумент функции равен ЛОЖЬ (FALSE) – это значит, что либо будет найдено точное совпадение, либо будет показано сообщение об ошибке #Н/Д (#N/A).

Потащите мышью, чтобы скопировать функцию ВПР из ячейки B2 вниз по столбцу до ячейки B11.Пояснение: Когда мы копируем функцию ВПР вниз, абсолютная ссылка $E$4:$G$7 остаётся неизменной, в то время как относительная ссылка A2 изменяется на A3, A4, A5 и так далее.

Пример применения формулы массива

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

Товарный чек
Как бы мы решали данную задачу стандартным образом?

Во-первых, мы бы получили итоговую сумму по каждому товару, перемножив количество товара на его цену.

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

Расчет общей суммы покупки
Ту же самую задачу можно решить с помощью простейшей формулы массива.

Нам нужно получить сумму, поэтому воспользуемся соответствующей функцией СУММ. А вот суммировать мы должны произведение цены товара на его количество, что мы и сделаем – выбираем диапазон значений из столица B и умножаем его на аналогичный диапазон значений столбца C.

Формула массива
Если сейчас нажать Enter, то появится ошибка.

Ошибка при нажатии Enter
Так как мы в формуле использовали диапазоны (массивы) данных, то и формула должна быть формулой массива. Для этого нужно нажать сочетание клавиш Ctrl + Shift + Enter и получим результат.

Формула массива
Что произошло и как работает формула массива? Это важно понять, так как в дальнейшем можно будет применять формулы массива для решения намного более заковыристых задач…

Итак, при вычислении формулы массива Excel произвел попарное умножение значений диапазонов B2:B5 и C2:C5. В результате получился массив значений который был просуммирован соответствующей функцией. Мы получили только одно значение итоговой суммы без вспомогательных вычислений.

Обратите внимание на формулу в строке формул. Она заключена в фигурные скобки, которые указывают нам, что мы имеем дело с формулой массива.

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

ГПР

Похожим образом работает и функция ГПР (HLOOKUP):

Функции для работы со ссылками и массивами

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

Таблица 4.10. Функции для работы со ссылками и массивами.

АДРЕС Создает адрес ячейки в виде текста, используя номер строки и номер столбца.

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

ВЫБОР Использует номер индекса, чтобы выбрать и вернуть значение из списка аргументов-значений.

ГИПЕРССЫЛКА Создание ссылки, открывающей документ, находящийся на жестком диске, сервере сети или Internet.

ГПР Просматривает верхнюю строку массива в поисках определенного значения и возвращает значение из указанной ячейки.

двссыл Возвращает ссылку, заданную аргументом ссылка на ячейку.

ИНДЕКС Выбирает по индексу значение из ссылки или массива.

ОБЛАСТИ Возвращает количество областей в ссылке. Область – это интервал смежных ячеек или отдельная ячейка.

ПОИСКПОЗ Возвращает относительную позицию элемента массива.

ПОЛУЧИТЬ.ДАННЫЕ. СВОДНОЙ.ТАБЛИЦЫ Получение данных сводной таблицы

ПРОСМОТР Ищет значения в векторе или массиве.

СМЕЩ Возвращает ссылку заданной высоты и ширины, отстоящую от другой ссылки на заданное количество строки и столбцов.

СТОЛБЕЦ Возвращает номер столбца по заданной ссылке.

СТРОКА Возвращает номер строки, определяемой ссылкой.

ТРАНШ Возвращает транспонированный массив.

ЧИСЛСТОЛБ Возвращает количество столбцов в ссылке или массиве.

ЧСТРОК Возвращает количество строк в ссылке или массиве.

Функция ГПР

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

Синтаксис: =ГПР(ключ; диапазон; номер_строки; [интервальный_просмотр]).

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

Источники

https://exceltable.com/master-klass/massivy-funktciy-excel

https://www.planetaexcel.ru/techniques/2/91/

https://office-menu.ru/uroki-excel/13-uverennoe-ispolzovanie-excel/45-funktsii-ssylok-i-massivov-excel

https://topexcel.ru/5-osnovnyx-funkcii-dlya-raboty-s-massivami/

https://msoffice-prowork.com/ref/excel/excelfunc/lookup-reference/

https://office-guru.ru/excel/funkcii-excel-dlja-raboty-so-ssylkami-i-massivami-515.html

https://zen.yandex.ru/media/id/5c4d8a530aaeef00ac928ac3/5cb46bff23aed400b46a0c52

http://samoychiteli.ru/document37861.html

Источник

Содержание

Выполнение выборки
- Способ 1: применение расширенного автофильтра
- Способ 2: применение формулы массива
- Способ 3: выборка по нескольким условиям с помощью формулы
- Способ 4: случайная выборка
Вопросы и ответы

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

Выполнение выборки

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

Способ 1: применение расширенного автофильтра

Наиболее простым способом произвести отбор является применение расширенного автофильтра. Рассмотрим, как это сделать на конкретном примере.

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

Есть возможность поступить и по-другому. Для этого после выделения области на листе перемещаемся во вкладку «Данные». Щелкаем по кнопке «Фильтр», которая размещена на ленте в группе «Сортировка и фильтр».

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

Активируется окно пользовательской фильтрации. В нем можно задать ограничение, по которому будет производиться отбор. В выпадающем списке для столбца содержащего ячейки числового формата, который мы используем для примера, можно выбрать одно из пяти видов условий:
- равно;
- не равно;
- больше;
- больше или равно;
- меньше.
Давайте в качестве примера зададим условие так, чтобы отобрать только значения, по которым сумма выручки превышает 10000 рублей. Устанавливаем переключатель в позицию «Больше». В правое поле вписываем значение «10000». Чтобы произвести выполнение действия, щелкаем по кнопке «OK».

Как видим, после фильтрации остались только строчки, в которых сумма выручки превышает 10000 рублей.

Но в этом же столбце мы можем добавить и второе условие. Для этого опять возвращаемся в окно пользовательской фильтрации. Как видим, в его нижней части есть ещё один переключатель условия и соответствующее ему поле для ввода. Давайте установим теперь верхнюю границу отбора в 15000 рублей. Для этого выставляем переключатель в позицию «Меньше», а в поле справа вписываем значение «15000».
Кроме того, существует ещё переключатель условий. У него два положения «И» и «ИЛИ». По умолчанию он установлен в первом положении. Это означает, что в выборке останутся только строчки, которые удовлетворяют обоим ограничениям. Если он будет выставлен в положение «ИЛИ», то тогда останутся значения, которые подходят под любое из двух условий. В нашем случае нужно выставить переключатель в положение «И», то есть, оставить данную настройку по умолчанию. После того, как все значения введены, щелкаем по кнопке «OK».

Теперь в таблице остались только строчки, в которых сумма выручки не меньше 10000 рублей, но не превышает 15000 рублей.

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

Снова запускается окно пользовательского автофильтра. Выполним отбор результатов в таблице с 4 по 6 мая 2016 года включительно. В переключателе выбора условий, как видим, ещё больше вариантов, чем для числового формата. Выбираем позицию «После или равно». В поле справа устанавливаем значение «04.05.2016». В нижнем блоке устанавливаем переключатель в позицию «До или равно». В правом поле вписываем значение «06.05.2016». Переключатель совместимости условий оставляем в положении по умолчанию – «И». Для того, чтобы применить фильтрацию в действии, жмем на кнопку «OK».

Как видим, наш список ещё больше сократился. Теперь в нем оставлены только строчки, в которых сумма выручки варьируется от 10000 до 15000 рублей за период с 04.05 по 06.05.2016 включительно.

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

Как видим, после этих действий, выборка по сумме выручки будет отключена, а останется только отбор по датам (с 04.05.2016 по 06.05.2016).

В данной таблице имеется ещё одна колонка – «Наименование». В ней содержатся данные в текстовом формате. Посмотрим, как сформировать выборку с помощью фильтрации по этим значениям.
Кликаем по значку фильтра в наименовании столбца. Последовательно переходим по наименованиям списка «Текстовые фильтры» и «Настраиваемый фильтр…».

Опять открывается окно пользовательского автофильтра. Давайте сделаем выборку по наименованиям «Картофель» и «Мясо». В первом блоке переключатель условий устанавливаем в позицию «Равно». В поле справа от него вписываем слово «Картофель». Переключатель нижнего блока так же ставим в позицию «Равно». В поле напротив него делаем запись – «Мясо». И вот далее мы выполняем то, чего ранее не делали: устанавливаем переключатель совместимости условий в позицию «ИЛИ». Теперь строчка, содержащая любое из указанных условий, будет выводиться на экран. Щелкаем по кнопке «OK».

Как видим, в новой выборке существуют ограничения по дате (с 04.05.2016 по 06.05.2016) и по наименованию (картофель и мясо). По сумме выручки ограничений нет.

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

Второй вариант предполагает переход во вкладку «Главная». Там выполняем щелчок на ленте по кнопке «Сортировка и фильтр» в блоке «Редактирование». В активировавшемся списке нажимаем на кнопку «Фильтр».

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

Урок: Функция автофильтр в Excel

Способ 2: применение формулы массива

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

На том же листе создаем пустую таблицу с такими же наименованиями столбцов в шапке, что и у исходника.

Выделяем все пустые ячейки первой колонки новой таблицы. Устанавливаем курсор в строку формул. Как раз сюда будет заноситься формула, производящая выборку по указанным критериям. Отберем строчки, сумма выручки в которых превышает 15000 рублей. В нашем конкретном примере, вводимая формула будет выглядеть следующим образом:
=ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

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

Так как это формула массива, то для того, чтобы применить её в действии, нужно нажимать не кнопку Enter, а сочетание клавиш Ctrl+Shift+Enter. Делаем это.

Выделив второй столбец с датами и установив курсор в строку формул, вводим следующее выражение:
=ИНДЕКС(B2:B29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

Жмем сочетание клавиш Ctrl+Shift+Enter.

Аналогичным образом в столбец с выручкой вписываем формулу следующего содержания:
=ИНДЕКС(C2:C29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

Опять набираем сочетание клавиш Ctrl+Shift+Enter.

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

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

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

Теперь дата отображается корректно. Но, как видим, вся нижняя часть таблицы заполнена ячейками, которые содержат ошибочное значение «#ЧИСЛО!». По сути, это те ячейки, данных из выборки для которых не хватило. Более привлекательно было бы, если бы они отображались вообще пустыми. Для этих целей воспользуемся условным форматированием. Выделяем все ячейки таблицы, кроме шапки. Находясь во вкладке «Главная» кликаем по кнопке «Условное форматирование», которая находится в блоке инструментов «Стили». В появившемся списке выбираем пункт «Создать правило…».

В открывшемся окне выбираем тип правила «Форматировать только ячейки, которые содержат». В первом поле под надписью «Форматировать только ячейки, для которых выполняется следующее условие» выбираем позицию «Ошибки». Далее жмем по кнопке «Формат…».

В запустившемся окне форматирования переходим во вкладку «Шрифт» и в соответствующем поле выбираем белый цвет. После этих действий щелкаем по кнопке «OK».

На кнопку с точно таким же названием жмем после возвращения в окно создания условий.

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

Урок: Условное форматирование в Excel

Способ 3: выборка по нескольким условиям с помощью формулы

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

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

Как и в предыдущем способе, поочередно выделяем пустые столбцы новой таблицы и вписываем в них соответствующие три формулы. В первый столбец вносим следующее выражение:
=ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(($D$2=C2:C29);СТРОКА(C2:C29);"");СТРОКА(C2:C29)-СТРОКА($C$1))-СТРОКА($C$1))

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

Каждый раз после ввода не забываем набирать сочетание клавиш Ctrl+Shift+Enter.

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

Способ 4: случайная выборка

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

Слева от таблицы пропускаем один столбец. В ячейке следующего столбца, которая находится напротив первой ячейки с данными таблицы, вписываем формулу:
=СЛЧИС()

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

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

Теперь у нас имеется диапазон ячеек, заполненный случайными числами. Но, он содержит в себе формулу СЛЧИС. Нам же нужно работать с чистыми значениями. Для этого следует выполнить копирование в пустой столбец справа. Выделяем диапазон ячеек со случайными числами. Расположившись во вкладке «Главная», щелкаем по иконке «Копировать» на ленте.

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

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

Активируется окно настройки сортировки. Обязательно устанавливаем галочку напротив параметра «Мои данные содержат заголовки», если шапка имеется, а галочки нет. В поле «Сортировать по» указываем наименование того столбца, в котором содержатся скопированные значения случайных чисел. В поле «Сортировка» оставляем настройки по умолчанию. В поле «Порядок» можно выбрать параметр как «По возрастанию», так и «По убыванию». Для случайной выборки это значения не имеет. После того, как настройки произведены, жмем на кнопку «OK».

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

Урок: Сортировка и фильтрация данных в Excel

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

Еще статьи по данной теме:

Помогла ли Вам статья?

Источник

Пример формулы массива

Что такое формула массива в Excel
Массивы в Excel
- Отличие массивов от диапазонов в Excel
- Размерность массивов
- Виды массивов
Методы создания массивов
- Массив констант, созданный вручную
- Именованный массив констант
- С помощью функций
- С помощью математических операторов
- С помощью операторов сравнения
- С помощью других массивов
- С помощью комбинаций указанных методов
Вывод массива на лист
Взаимодействие функций Excel с массивами
Формулы массива – примеры формул

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

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

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

Поехали!

Формула массива (array formula) – это такая формула, в процессе вычисления которой создается один или несколько массивов. При этом не обязательно как результат вычисления, но и как промежуточный этап.

Будьте на 100% уверены: если формула

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

– это формула массива.

Слишком очевидно и все еще непонятно? Не беда – читаем далее.

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

Массивы в Excel

Массив (array) – это фиксированный набор элементов с фиксированным порядком. Иными словами, все элементы массива, включая их положение в нем, являются его неотъемлемой частью, их порядок и количество нельзя изменить.

Отличие массивов от диапазонов в Excel

В отличие от массива, диапазон (range) – это просто метод адресации к ячейкам в Excel, никак не связанный с самим их содержимым.

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

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

Если же на лист Excel выведен массив, ни сортировка строк, ни вставка строк и столбцов в середину диапазона, в который он выведен, не возможна – это нарушило бы само его определение.

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

Нельзя изменить часть массива.

Ошибка Excel при попытке нарушить целостность массива

Размерность массивов

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

Одномерные массивы (векторы) в свою очередь могут быть вертикальными и горизонтальными.

Одномерные и двумерные массивы в Excel

Трехмерные массивы – довольно редко используемая на практике сущность.

Виды массивов

По типам элементов в них:
По типу данных –

числовой массив (массив чисел)
строковый массив (текстовые значения)
булевый массив (значения ИСТИНА-ЛОЖЬ)
смешанного типа

По изменяемости элементов –

массив констант,
вычисляемый массив

Методы создания массивов

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

Массив констант, созданный вручную

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

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

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

Именованный массив констант

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

Массив цифр как в примере ниже, поможет быстро найти цифры в текстовых ячейках.

создание именованного массива констант на примере цифр

Создаем именованный массив в Excel

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

Используем именованный массив

С помощью функций

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

Единственное условие для создания массива с помощью функции – функция не должна обладать агрегирующим свойством. Например, СУММ, СРЗНАЧ не создадут массив при обращении к диапазону.

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

Формула массива с функциями СЦЕПИТЬ и СИМВОЛ

Кавычки-ёлочки в этой формуле нам помогает создать функция СИМВОЛ.

С помощью математических операторов

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

Массивы в Excel, полученные с помощью математических операторов

С помощью операторов сравнения

Аналогично математическим операторам, массивы создаются при сравнении диапазонов с константой или значением ячейки. Результатом операции сравнения являются значения ИСТИНА или ЛОЖЬ.

Массивы в Excel, полученные с помощью операторов сравнения

С помощью других массивов

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

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

Размер результирующего массива в таком случае будет аналогичен размеру массива констант.

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

С помощью комбинаций указанных методов

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

Вывод массива на лист

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

Ctrl+Shift+Enter

Если выделить недостаточное количество ячеек – будут выведены не все элементы массива, а только те, что соответствуют по порядку.
Если выделить избыточное количество – лишние ячейки выдадут ошибку #Н/Д.

На примере результирующий массив должен быть размером 5*5, но перед вводом формулы массива был выделен диапазон 6*6.

Ошибка НД при несоответствии размера диапазона и массива

Взаимодействие функций Excel с массивами

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

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

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

Практически все статистические функции по природе берут на вход диапазон или массив и возвращают одно число. Наиболее популярные из них – СРЗНАЧ, МИН, МАКС, СЧЁТ, СЧЁТЗ, СЧЁТЕСЛИ.

Среди логических функций агрегирующим свойством обладают функция И и функция ИЛИ.

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

учитывают весь массив
выводят одно значение

ВЫБОР, ВПР, ГПР, ИНДЕКС, ПОИСКПОЗ – эти функции поиска используются наиболее часто.

Формулы массива – примеры формул

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

Приступаем к практическим шагам:

Учимся формулам массива 1/4:

Как создать алфавит в Excel

Источник

АДРЕС	Создает адрес ячейки в виде текста, используя номер строки и номер столбца.
ВПР	Просматривает левый столбец массива в поисках определенного значения и возвращает значение из указанной ячейки.
ВЫБОР	Использует номер индекса, чтобы выбрать и вернуть значение из списка аргументов-значений.
ГИПЕРССЫЛКА	Создание ссылки, открывающей документ, находящийся на жестком диске, сервере сети или Internet.
ГПР	Просматривает верхнюю строку массива в поисках определенного значения и возвращает значение из указанной ячейки.
двссыл	Возвращает ссылку, заданную аргументом ссылка на ячейку.
ИНДЕКС	Выбирает по индексу значение из ссылки или массива.
ОБЛАСТИ	Возвращает количество областей в ссылке. Область – это интервал смежных ячеек или отдельная ячейка.
ПОИСКПОЗ	Возвращает относительную позицию элемента массива.
ПОЛУЧИТЬ.ДАННЫЕ. СВОДНОЙ.ТАБЛИЦЫ	Получение данных сводной таблицы
ПРОСМОТР	Ищет значения в векторе или массиве.
СМЕЩ	Возвращает ссылку заданной высоты и ширины, отстоящую от другой ссылки на заданное количество строки и столбцов.
СТОЛБЕЦ	Возвращает номер столбца по заданной ссылке.
СТРОКА	Возвращает номер строки, определяемой ссылкой.
ТРАНШ	Возвращает транспонированный массив.
ЧИСЛСТОЛБ	Возвращает количество столбцов в ссылке или массиве.
ЧСТРОК	Возвращает количество строк в ссылке или массиве.