Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще…Меньше
Функция СТОЛБЕЦ возвращает номер столбца заданного ссылка на ячейку. Например, формула =СТОЛБЕЦ(D10) возвращает 4, поскольку столбец D — четвертый столбец.
Синтаксис
СТОЛБЕЦ([ссылка])
аргумент функции СТОЛБЕЦ описаны ниже.
-
Ссылка — необязательный аргумент. Ячейка или диапазон ячеек, для которого требуется возвратить номер столбца.
-
Если аргумент «ссылка» не указан или ссылается на диапазон ячеек, а функция СТОЛБЕЦ введена как формула горизонтального массива, то функция СТОЛБЕЦ возвращает номера столбцов в ссылке в виде горизонтального массива.
Примечания:
-
Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
-
-
Если аргумент ссылается на диапазон ячеек, а функция СТОЛБЕЦ не введена как формула горизонтального массива, то функция СТОЛБЕЦ возвращает номер самого левого столбца.
-
Если аргумент ссылки опущен, то предполагается, что это ссылка на ячейку, в которой находится сама функция СТОЛБЕЦ.
-
Аргумент ссылки не может ссылаться на несколько областей.
-
Пример
Нужна дополнительная помощь?
Функция СТОЛБЕЦ в Excel возвращает номер столбца на листе по заданным условиям. Синтаксис элементарный: всего один аргумент. Но с ее помощью можно эффективно решать разнообразные задачи.
Описание и синтаксис функции
Функция с параметром: = СТОЛБЕЦ (С3) возвращает значение 3, т.к. (C) является третьим по счету.
Аргумент «ссылка» необязательный. Это может быть ячейка или диапазон, для которого нужно получить номер столбца.
Аргумент – ссылка на ячейку:
Функция выдала номер колонки для этой ячейки.
Аргумент опущен:
Функция вернула номер столбца, в котором находится.
Аргумент – вертикальный диапазон ячеек:
Функция вернула номер столбца, в котором расположен диапазон.
Аргумент – горизонтальный диапазон ячеек:
Функция СТОЛБЕЦ вернула номер крайнего левого столбца (А) в указанном диапазоне. Если выделить формулу в строке формул и нажать кнопку F9, то программа выдаст все номера столбцов заданного диапазона.
Но при нажатии кнопки Enter в ячейке с формулой отобразится только номер крайнего левого столбца.
Чтобы на листе появились номера всех столбцов диапазона, который является аргументом функции СТОЛБЕЦ, нужно использовать формулу массива. Выделяем такое количество ячеек, сколько элементов входит в горизонтальный диапазон. Вводим формулу и нажимаем сочетание кнопок Ctrl + Shift + Enter.
Аргумент – ссылка на горизонтальный массив:
Формула вернула номера столбцов в виде горизонтального массива.
В качестве аргумента нельзя применять ссылки на несколько областей.
Полезные примеры функции СТОЛБЕЦ в Excel
Формула с использованием функции выдает массив последовательных чисел. Эту особенность можно применить для решения других задач.
Например, рассчитаем значение выражения 1 + ½ + 1/3. Используем формулу: =СУММПРОИЗВ(1/СТОЛБЕЦ(A2:C2)).
Выполним более сложные манипуляции с числовым рядом: найдем сумму значений от 1 до 1/n^3, где n = 6. Формула расчета: =СУММПРОИЗВ(1/СТОЛБЕЦ(A9:F9)^3).
Чаще всего данную функцию используют совместно с функцией ВПР. Задача первой функции – указать номер столбца возвращаемых значений. Такое совмещение удобно при работе с огромными таблицами. Например, пользователь помещает возвращаемые данные в табличку с такой же, как в исходной таблице, последовательностью столбцов. Причем обе таблицы достаточно широкие.
Напомним, что ВПР ищет заданное значение в крайнем левом столбце диапазона и возвращает значение из другого столбца в той же строке. Говоря техническим языком, ВПР находит в базе данных уникальный идентификатор и извлекает связанную с ним информацию.
Аргументы функции ВПР: искомое значение, массив данных для анализа, номер столбца, интервальный просмотр (точный или приблизительный поиск). Сам номер можно задать с помощью такой формулы: =ВПР(8;A1:C10;СТОЛБЕЦ(C1);ИСТИНА).
При работе с широкими таблицами можно просто копировать функцию ВПР по горизонтали. В этом случае номера столбцов автоматически пересчитываются – табличка заполняется.
Нужна корректировка номера– прибавляем или отнимаем определенную цифру или рассчитанное с помощью какой-либо функции значение. Например,
Функция СТОЛБЕЦ должна вычесть 1 из номера колонки C. Поэтому функция ВПР возвращает значение не из третьего, а из второго столбца девятой строки.
Теперь проиллюстрируем, как работает многоразовое копирование без необходимости в ручной правке. Сначала в формуле закрепим ссылки на таблицу (кнопка F4). Скопируем формулу ВПР поперек столбцов – номер меняется автоматически (функция СТОЛБЕЦ сдвигается вместе с другими ссылками).
Достаточно элегантное решение, позволяющее править формулы в автоматическом режиме.
Skip to content
В этом руководстве показано, как использовать ИНДЕКС и ПОИСКПОЗ в Excel и чем они лучше ВПР.
В нескольких недавних статьях мы приложили немало усилий, чтобы объяснить основы функции ВПР новичкам и предоставить более сложные примеры формул ВПР опытным пользователям. А теперь я постараюсь если не отговорить вас от использования ВПР, то хотя бы показать вам альтернативный способ поиска нужных значений в Excel.
- Краткий обзор функций ИНДЕКС и ПОИСКПОЗ
- Как использовать формулу ИНДЕКС ПОИСКПОЗ
- ИНДЕКС+ПОИСКПОЗ вместо ВПР?
- Поиск справа налево
- Двусторонний поиск в строках и столбцах
- ИНДЕКС ПОИСКПОЗ для поиска по нескольким условиям
- Как найти среднее, максимальное и минимальное значение
- Что делать с ошибками поиска?
Для чего это нужно? Потому что функция ВПР имеет множество ограничений, которые могут помешать вам получить желаемый результат во многих ситуациях. С другой стороны, комбинация ПОИСКПОЗ ИНДЕКС более гибкая и имеет много замечательных возможностей, которые во многих отношениях превосходят ВПР.
Функции Excel ИНДЕКС и ПОИСКПОЗ — основы
Поскольку целью этого руководства является демонстрация альтернативного способа выполнения поиска в Excel с использованием комбинации функций ИНДЕКС и ПОИСКПОЗ, мы не будем подробно останавливаться на их синтаксисе и использовании. Тем более, что это подробно рассмотрено в других статьях, ссылки на которые вы можете найти в конце этого руководства. Мы рассмотрим лишь минимум, необходимый для понимания общей идеи, а затем подробно рассмотрим примеры формул, раскрывающие все преимущества использования ПОИСКПОЗ и ИНДЕКС вместо ВПР.
Функция ИНДЕКС
Функция ИНДЕКС (в английском варианте – INDEX) возвращает значение в массиве на основе указанных вами номеров строк и столбцов. Синтаксис функции ИНДЕКС прост:
ИНДЕКС(массив,номер_строки,[номер_столбца])
Вот простое объяснение каждого параметра:
- массив — это диапазон ячеек, именованный диапазон или таблица.
- номер_строки — это номер строки в массиве, из которого нужно вернуть значение. Если этот аргумент опущен, требуется следующий – номер_столбца.
- номер_столбца — это номер столбца, из которого нужно вернуть значение. Если он опущен, требуется номер_строки.
Дополнительные сведения см. в статье Функция ИНДЕКС в Excel .
А вот пример формулы ИНДЕКС в самом простом виде:
=ИНДЕКС(A1:C10;2;3)
Формула выполняет поиск в ячейках с A1 по C10 и возвращает значение ячейки во 2-й строке и 3-м столбце, т. е. в ячейке C2.
Очень легко, правда? Однако при работе с реальными данными вы вряд ли когда-нибудь будете заранее знать, какие строки и столбцы вам нужны. Здесь вам пригодится ПОИСКПОЗ.
Функция ПОИСКПОЗ
Она ищет нужное значение в диапазоне ячеек и возвращает относительное положение этого значения в диапазоне.
Синтаксис функции ПОИСКПОЗ следующий:
ПОИСКПОЗ(искомое_значение, искомый_массив, [тип_совпадения])
- искомое_значение — числовое или текстовое значение, которое вы ищете.
- диапазон_поиска — диапазон ячеек, в которых будем искать.
- тип_совпадения — указывает, следует ли искать точное соответствие или наиболее близкое совпадение:
- 1 или опущено — находит наибольшее значение, которое меньше или равно искомому значению. Требуется сортировка массива поиска в порядке возрастания.
- 0 — находит первое значение, точно равное искомому значению. В комбинации ИНДЕКС/ПОИСКПОЗ вам почти всегда нужно точное совпадение, поэтому вы чаще всего устанавливаете третий аргумент вашей функции в 0.
- -1 — находит наименьшее значение, которое больше или равно искомому значению. Требуется сортировка массива поиска в порядке убывания.
Например, если диапазон B1:B3 содержит значения «яблоки», «апельсины», «лимоны», приведенная ниже формула возвращает число 3, поскольку «лимоны» — это третья по счету запись в этом диапазоне:
=ПОИСКПОЗ(«лимоны»;B1:B3;0)
Дополнительные сведения см . в статье Функция ПОИСКПОЗ в Excel .
На первый взгляд полезность функции ПОИСКПОЗ может показаться сомнительной. Кого волнует положение значения в диапазоне? Что мы действительно хотим определить, так это само значение.
Однако, относительная позиция искомого значения (т. е. номера строки и столбца, в которых оно находится) — это именно то, что нам нужно указать для аргументов номер_строки и номер_столбца функции ИНДЕКС. Как вы помните, ИНДЕКС может найти значение на пересечении заданной строки и столбца, но сама не может определить, какую именно строку и столбец ей нужно выбрать.
Вот поэтому совместное использование ИНДЕКС и ПОИСКПОЗ открывает перед нами массу возможностей для поиска в Excel.
Как использовать формулу ИНДЕКС ПОИСКПОЗ в Excel
Теперь, когда вы знаете основы, я считаю, что вы уже начали понимать, как ПОИСКПОЗ и ИНДЕКС работают вместе. Короче говоря, ИНДЕКС извлекает нужное значение по номерам столбцов и строк, а ПОИСКПОЗ предоставляет ей эти номера. Вот и все!
Для вертикального поиска вы используете функцию ПОИСКПОЗ только для определения номера строки, указывая диапазон столбцов непосредственно в самой формуле:
ИНДЕКС ( столбец для возврата значения ; ПОИСКПОЗ ( искомое значение ; столбец для поиска ; 0))
Все еще не совсем понимаете эту логику? Возможно, будет проще разобрать на примере. Предположим, у вас есть список национальных столиц и их население:
Чтобы найти население определенной столицы, скажем, Индии, используйте следующую формулу ПОИСКПОЗ ИНДЕКС:
=ИНДЕКС(C2:C10; ПОИСКПОЗ(“Индия”;A2:A10;0))
Теперь давайте проанализируем, что на самом деле делает каждый компонент этой формулы:
- Функция ПОИСКПОЗ ищет искомое значение «Индия» в диапазоне A2:A10 и возвращает число 2, поскольку это слово занимает второе место в массиве поиска.
- Этот номер поступает непосредственно в аргумент номер_строки функции ИНДЕКС, предписывая вернуть значение из этой строки.
Таким образом, приведенная выше формула превращается в ИНДЕКС(C2:C10;2), которая означает, что нужно искать в ячейках от C2 до C10 и извлекать значение из второй ячейки в этом диапазоне, то есть из C3, потому что мы начинаем отсчет со второй строки.
Но указывать название города в формуле не совсем правильно, так как для каждого нового поиска придется корректировать эту формулу. Введите его в какую-нибудь отдельную ячейку, скажем, F1, укажите ссылку на ячейку для ПОИСКПОЗ, и вы получите формулу динамического поиска:
=ИНДЕКС(C2:C10;ПОИСКПОЗ(F1;A2:A10;0))
Важное замечание! Количество строк в аргументе массив функции ИНДЕКС должно совпадать с количеством строк в аргументе просматриваемый_массив в ПОИСКПОЗ, иначе формула выдаст неверный результат.
Вы спросите: «А почему бы нам просто не использовать обычную формулу ВПР? Какой смысл тратить время на то, чтобы разобраться в хитросплетениях ИНДЕКС ПОИСКПОЗ в Excel?»
Вот как это будет выглядеть:
=ВПР(F1; A2:C10; 3; 0)
Конечно, так проще. Но этот наш элементарный пример предназначен только для демонстрационных целей, чтобы вы поняли, как именно функции ИНДЕКС и ПОИСКПОЗ работают вместе. Действительно, ВПР была бы здесь более уместна. Другие примеры, которые вы найдёте ниже, покажут вам реальную силу этой комбинации, которая легко справляется со многими сложными задачами, когда ВПР будет бессильна.
ИНДЕКС+ПОИСКПОЗ вместо ВПР?
Решая, какую функцию использовать для вертикального поиска, большинство знатоков Excel сходятся во мнении, что ПОИСКПОЗ+ИНДЕКС намного лучше, чем ВПР. Однако многие до сих пор остаются с ВПР, во-первых, потому что это проще, а, во-вторых, потому что они не до конца понимают все преимущества использования формулы ПОИСКПОЗ ИНДЕКС в Excel. Без такого понимания никто не захочет тратить свое время на изучение более сложного синтаксиса.
Ниже я укажу на ключевые преимущества ИНДЕКС ПОИСКПОЗ перед ВПР, а уж вам решать, является ли это достойным дополнением к вашему арсеналу знаний в Excel.
4 основные причины использовать ИНДЕКС ПОИСКПОЗ вместо ВПР
- Поиск справа налево. Как известно любому образованному пользователю, ВПР не может искать влево. Это означает, что искомое значение всегда должно находиться в крайнем левом столбце таблицы. А извлекать нужное значение мы будем из столбца, который находится правее. ИНДЕКС+ПОИСКПОЗ может легко выполнять поиск влево! Здесь это показано в действии: Как выполнить поиск значения слева в Excel .
- Можно безопасно вставлять или удалять столбцы. Формулы ВПР не работают или выдают неверные результаты, когда новый столбец удаляется из таблицы поиска или добавляется в нее, поскольку синтаксис ВПР требует указания порядкового номера столбца, из которого вы хотите извлечь данные. Естественно, когда вы добавляете или удаляете столбцы, этот номер в формуле автоматически не меняется, а нужный столбец уже оказывается на новом месте.
С функциями ИНДЕКС и ПОИСКПОЗ вы указываете диапазон возвращаемых столбцов, а не номер одного из них. В результате вы можете вставлять и удалять столько столбцов, сколько хотите, не беспокоясь об обновлении каждой связанной с ними формулы.
- Нет ограничений на размер искомого значения. При использовании функции ВПР общая длина ваших критериев поиска не может превышать 255 символов, иначе вы получите ошибку #ЗНАЧ!. Таким образом, если ваш набор данных содержит длинные строки, ИНДЕКС ПОИСКПОЗ — единственное работающее решение.
- Более высокая скорость обработки. Если ваши таблицы относительно небольшие, вряд ли будет какая-то существенная разница в производительности Excel. Но если ваши рабочие листы содержат сотни или тысячи строк и, следовательно, сотни или тысячи формул, ИНДЕКС ПОИСКПОЗ будет работать намного быстрее, чем ВПР. Причина в том, что Excel будет обрабатывать только столбцы поиска и возврата, а не весь массив таблицы.
Влияние ВПР на производительность Excel может быть особенно заметным, если ваша книга содержит сложные формулы массива. Чем больше значений содержит ваш массив и чем больше формул массива содержится в книге, тем медленнее работает Excel.
ИНДЕКС ПОИСКПОЗ в Excel – примеры формул
Уяснив, почему все же стоит изучать ИНДЕКС ПОИСКПОЗ, давайте перейдем к самому интересному и посмотрим, как можно применить теоретические знания на практике.
Формула для поиска справа налево
Как уже упоминалось, ВПР не может получать значения слева от столбца поиска. Таким образом, если ваши значения поиска не находятся в самом левом столбце, нет никаких шансов, что формула ВПР принесет вам желаемый результат. Функция ПОИСКПОЗ ИНДЕКС в Excel более универсальна и не имеет особого значения, где расположены столбцы поиска и возврата.
Для этого примера мы добавим столбец «Ранг» слева от нашей основной таблицы и попытаемся выяснить, какое место занимает столица России по численности населения среди других перечисленных столиц.
Записав искомое значение в G1, используйте следующую формулу для поиска в C2:C10 и возврата соответствующего значения из A2:A10:
=ИНДЕКС(A2:A10; ПОИСКПОЗ(G1;C2:C10;0))
Совет. Если вы планируете использовать формулу ПОИСКПОЗ ИНДЕКС более чем для одной ячейки, обязательно зафиксируйте оба диапазона абсолютными ссылками (например, $A$2:$A$10 и $C$2:$C$10), чтобы они не изменялись при копировании формулы.
Двусторонний поиск в строках и столбцах
В приведенных выше примерах мы использовали ИНДЕКС ПОИСКПОЗ вместо классической функции ВПР, чтобы вернуть значение из точно указанного столбца. Но что, если вам нужно искать в нескольких строках и столбцах? То есть, сначала нужно найти подходящий столбец, а уж потом извлечь из него значение? Другими словами, что, если вы хотите выполнить так называемый матричный или двусторонний поиск?
Это может показаться сложным, но формула очень похожа на базовую функцию ПОИСКПОЗ ИНДЕКС в Excel, но с одним отличием.
Просто используйте две функции ПОИСКПОЗ, вложенных друг в друга: одну – для получения номера строки, а другую – для получения номера столбца.
ИНДЕКС(массив; ПОИСКПОЗ(значение_поиска1 ; столбец_поиска ; 0); ПОИСКПОЗ(значение_поиска2 ; столбец_поиска ; 0))
А теперь, пожалуйста, взгляните на приведенную ниже таблицу и давайте составим формулу двумерного поиска, чтобы найти население (в миллионах) в данной стране за данный год.
С целевой страной в G1 (значение_поиска1) и целевым годом в G2 (значение_поиска2) формула принимает следующий вид:
=ИНДЕКС(B2:D11; ПОИСКПОЗ(G1;A2:A11;0); ПОИСКПОЗ(G2;B1:D1;0))
Как работает эта формула?
Всякий раз, когда вам нужно понять сложную формулу Excel, разделите ее на более мелкие части и посмотрите, что делает каждая отдельная функция:
ПОИСКПОЗ(G1;A2:A11;0); – ищет в A2:A11 значение из ячейки G1 («США») и возвращает его позицию, которая равна 3.
ПОИСКПОЗ(G2;B1:D1;0) – просматривает диапазон B1:D1, чтобы получить позицию значения из ячейки G2 («2015»), которая равна 3.
Найденные выше номера строк и столбцов становятся соответствующими аргументами функции ИНДЕКС:
ИНДЕКС(B2:D11, 3, 3)
В результате вы получите значение на пересечении 3-й строки и 3-го столбца в диапазоне B2:D11, то есть из D4. Несложно?
ИНДЕКС ПОИСКПОЗ для поиска по нескольким условиям
Если у вас была возможность прочитать наши материалы по ВПР в Excel, вы, вероятно, уже протестировали формулу для ВПР с несколькими условиями . Однако существенным недостатком этого подхода является необходимость добавления вспомогательного столбца. Хорошей новостью является то, что функция ПОИСКПОЗ ИНДЕКС в Excel также может выполнять поиск по нескольким условиям без изменения или реструктуризации исходных данных!
Вот общая формула ИНДЕКС ПОИСКПОЗ с несколькими критериями:
{=ИНДЕКС( диапазон_возврата; ПОИСКПОЗ (1; ( критерий1 = диапазон1 ) * ( критерий2 = диапазон2 ); 0))}
Примечание. Это формула массива , которую необходимо вводить с помощью сочетания клавиш Ctrl + Shift + Enter.
Предположим, что в таблице ниже вы хотите найти значение на основе двух критериев: Покупатель и Товар.
Следующая формула ИНДЕКС ПОИСКПОЗ отлично работает:
=ИНДЕКС(C2:C10; ПОИСКПОЗ(1; (F1=A2:A10) * (F2=B2:B10); 0))
Где C2:C10 — это диапазон, из которого возвращается значение, F1 — это критерий1, A2:A10 — это диапазон для сравнения с критерием 1, F2 — это критерий 2, а B2:B10 — это диапазон для сравнения с критерием 2.
Не забудьте правильно ввести формулу, нажав Ctrl + Shift + Enter, и Excel автоматически заключит ее в фигурные скобки, как показано на скриншоте ниже:
Рис5
Если вы не хотите использовать формулы массива, добавьте в формулу в F4 еще одну функцию ИНДЕКС и завершите ее ввод обычным нажатием Enter:
=ИНДЕКС(C2:C10; ПОИСКПОЗ(1; ИНДЕКС((F1=A2:A10) * (F2=B2:B10); 0; 1); 0))
Разберем пошагово, как это работает.
Здесь используется тот же подход, что и в обычном сочетании ИНДЕКС ПОИСКПОЗ, где просматривается один столбец. Чтобы оценить несколько критериев, вы создаете два или более массива значений ИСТИНА и ЛОЖЬ, которые представляют совпадения и несовпадения для каждого отдельного критерия, а затем перемножаете соответствующие элементы этих массивов. Операция умножения преобразует ИСТИНА и ЛОЖЬ в 1 и 0 соответственно и создает массив, в котором единицы соответствуют строкам, которые удовлетворяют всем условиям. Функция ПОИСКПОЗ со значением поиска 1 находит первую «1» в массиве и передает ее позицию в ИНДЕКС, которая возвращает значение в этой позиции из указанного столбца.
Вторая формула без массива основана на способности функции ИНДЕКС работать с массивами. Второй вложенный ИНДЕКС имеет 0 в номер_строки , так что он будет передавать весь массив столбцов в ПОИСКПОЗ.
Среднее, максимальное и минимальное значение при помощи ИНДЕКС ПОИСКПОЗ
Microsoft Excel имеет специальные функции для поиска минимального, максимального и среднего значения в диапазоне. Но что, если вам нужно получить значение из другой ячейки, связанной с этими значениями? Например, получить название города с максимальным населением или узнать товар с минимальными продажами? В этом случае используйте функцию МАКС , МИН или СРЗНАЧ вместе с ИНДЕКС ПОИСКПОЗ.
Максимальное значение.
Предположим, нам нужно в списке городов найти столицу с самым большим населением. Чтобы найти наибольшее значение в столбце С и вернуть соответствующее ему значение из столбца В, находящееся в той же строке, используйте эту формулу:
=ИНДЕКС(B2:B10; ПОИСКПОЗ(МАКС(C2:C10); C2:C10; 0))
Скриншот с примером находится чуть ниже.
Минимальное значение
Теперь найдём город с самым маленьким населением в списке. Чтобы найти наименьшее число в столбце С и получить соответствующее ему значение из столбца В:
=ИНДЕКС(B2:B10; ПОИСКПОЗ(МИН(C2:C10); C2:C10; 0))
Ближайшее к среднему
Теперь мы находим город, население которого наиболее близко к среднему значению. Чтобы вычислить позицию, наиболее близкую к среднему значению показателя, рассчитанному из D2:D10, и получить соответствующее значение из столбца C, используйте следующую формулу:
=ИНДЕКС(B2:B10; ПОИСКПОЗ(СРЗНАЧ(C2:C10); C2:C10; -1 ))
В зависимости от того, как организованы ваши данные, укажите 1 или -1 для третьего аргумента (тип_совпадения) функции ПОИСКПОЗ:
- Если ваш столбец поиска (столбец D в нашем случае) отсортирован по возрастанию , поставьте 1. Формула вычислит наибольшее значение, которое меньше или равно среднему значению.
- Если ваш столбец поиска отсортирован по убыванию , введите -1. Формула вычислит наименьшее значение, которое больше или равно среднему значению.
- Если ваш массив поиска содержит значение , точно равное среднему, вы можете ввести 0 для точного совпадения. Никакой сортировки не требуется.
В нашем примере данные в столбце D отсортированы в порядке убывания, поэтому мы используем -1 для типа соответствия. В результате мы получаем «Токио», так как его население (13 189 000) является ближайшим, превышающим среднее значение (12 269 006).
Что делать с ошибками поиска?
Как вы, наверное, заметили, если формула ИНДЕКС ПОИСКПОЗ в Excel не может найти искомое значение, она выдает ошибку #Н/Д. Если вы хотите заменить это стандартное сообщение чем-то более информативным, оберните формулу ПОИСКПОЗ ИНДЕКС в функцию ЕСНД . Например:
=ЕСНД(ИНДЕКС(C2:C10; ПОИСКПОЗ(F1;A2:A10;0)); «Не найдено»)
И теперь, если кто-то вводит значение, которое не существует в диапазоне поиска, формула явно сообщит пользователю, что совпадений не найдено:
Если вы хотите перехватывать все ошибки, а не только #Н/Д, используйте функцию ЕСЛИОШИБКА вместо ЕСНД:
=ЕСЛИОШИБКА(ИНДЕКС(C2:C10; ПОИСКПОЗ(F1;A2:A10;0)); «Что-то пошло не так!»)
Пожалуйста, имейте в виду, что во многих ситуациях было бы не совсем правильно скрывать все такие ошибки, потому что они предупреждают вас о возможных проблемах в вашей формуле.
Итак, еще раз об основных преимуществах формулы ИНДЕКС ПОИСКПОЗ.
-
Возможен ли «левый» поиск?
-
Повлияет ли на результат вставка и удаление столбцов?
Вы можете вставлять и удалять столько столбцов, сколько хотите. На результат ИНДЕКС ПОИСКПОЗ это не повлияет.
-
Возможен ли поиск по строкам и столбцам?
Можно сначала найти подходящий столбец, а уж потом извлечь из него значение. Общий вид формулы:
ИНДЕКС(массив; ПОИСКПОЗ(значение_поиска1 ; столбец_поиска ; 0); ПОИСКПОЗ(значение_поиска2 ; столбец_поиска ; 0))
Подробную инструкцию смотрите здесь. -
Как сделать поиск ИНДЕКС ПОИСКПОЗ по нескольким условиям?
Можно выполнять поиск по двум или более условиям без добавления дополнительных столбцов. Вот формула массива, которая решит проблему:
{=ИНДЕКС( диапазон_возврата; ПОИСКПОЗ (1; ( критерий1 = диапазон1 ) * ( критерий2 = диапазон2 ); 0))}
Вот как можно использовать ИНДЕКС и ПОИСКПОЗ в Excel. Я надеюсь, что наши примеры формул окажутся полезными для вас.
Вот еще несколько статей по этой теме:
В этом руководстве вы познакомитесь с новой функцией динамического массива Excel 365 под названием CHOOSECOLS и покажете, как вы можете использовать ее для извлечения любых определенных столбцов из массива.
Представьте, что вы работаете с набором данных из сотен или тысяч столбцов. Очевидно, что некоторые столбцы более важны, чем другие, и, естественно, вы можете сначала прочитать их данные. Excel 365 предлагает идеальную функцию для работы, которая может мгновенно извлекать определенные данные из массива, чтобы вы могли сосредоточиться на наиболее важной информации.
Функция CHOOSECOLS в Excel предназначена для возврата указанных столбцов из массива или диапазона.
Синтаксис включает следующие аргументы:
CHOOSECOLS(массив, col_num1, [col_num2]…)
Где:
Массив (обязательно) — исходный массив или диапазон.
Col_num1 (обязательно) — целое число, определяющее первый возвращаемый столбец.
Col_num2, … (необязательно) — порядковые номера дополнительных столбцов для возврата.
А вот так может выглядеть функция ВЫБОР РАСЧЕТОВ в вашем Excel:
Наличие функции ВЫБОР
В настоящее время функция CHOOSECOLS доступна в Excel для Microsoft 365 (Windows и Mac) и Excel для Интернета.
Кончик. Чтобы извлечь несколько строк из диапазона или массива, может пригодиться функция CHOOSEROWS.
Как использовать функцию ВЫБОР РАСЦЕН в Excel
CHOOSECOLS — это функция динамического массива, поэтому она изначально обрабатывает массивы. Формула должна быть введена только в одну ячейку — верхнюю левую ячейку целевого диапазона — и она автоматически разливается на столько столбцов, сколько указано в ее аргументах, и столько строк, сколько есть в исходном массиве. В результате получается единый динамический массив, который называется диапазоном разлива.
Чтобы создать формулу CHOOSECOLS в Excel, вам нужно сделать следующее:
- Для массива укажите диапазон ячеек или массив значений.
- Для col_num укажите положительное или отрицательное целое число, указывающее, какой столбец следует вернуть. Положительное число подтягивает соответствующий столбец из левой части массива, отрицательное число — из правой части массива. Чтобы получить несколько столбцов, можно определить их номера в отдельных аргументах или в одном аргументе в виде константы массива.
Например, чтобы получить столбцы 2, 3 и 4 из диапазона A4:E19, используйте следующую формулу:
=ВЫБОРЦВЕТ(A4:E19, 2, 3, 4)
Кроме того, вы можете использовать константу горизонтального массива, например {2,3,4}, или константу вертикального массива, например {2;3;4}, чтобы указать номера столбцов:
=ВЫБОРЦВЕТ(A4:E19, {2,3,4})
=ВЫБОРЦВЕТ(A4:E19, {2;3;4})
Все три приведенные выше формулы дадут один и тот же результат:
В некоторых ситуациях может оказаться более удобным ввести номера столбцов в некоторые ячейки, а затем ссылаться на эти ячейки по отдельности или предоставить ссылку на один диапазон. Например:
=ВЫБОРЦВЕТ(A4:E19, G4, H4, I4)
=ВЫБОРЦВЕТ(A4:E19, G4:I4)
Этот подход дает вам больше гибкости — для извлечения любых других столбцов вы просто вводите разные числа в предопределенные ячейки, не изменяя саму формулу.
Теперь, когда вы знаете основы, давайте углубимся в дополнительные возможности и рассмотрим несколько более сложные формулы CHOOSECOLS для обработки конкретных сценариев.
Получить последние столбцы из диапазона
Чтобы вернуть один или несколько столбцов из конца диапазона, укажите отрицательные числа в качестве аргументов col_num. Это заставит функцию начать подсчет столбцов с правой стороны массива.
Например, чтобы получить последний столбец из диапазона A4:D13, используйте следующую формулу:
=ВЫБОРЦВЕТ(A4:E19, -1)
Чтобы извлечь последние два столбца, используйте этот:
=ВЫБОРЦВЕТ(A4:E19, -2, -1)
Чтобы вернуть последние 2 столбца в обратном порядке, измените порядок аргументов col_num следующим образом:
=ВЫБОРЦВЕТ(A4:E19, -1, -2)
Получить каждый второй столбец в Excel
Чтобы извлечь каждый второй столбец из заданного диапазона, вы можете использовать CHOOSECOLS вместе с несколькими другими функциями. Ниже приведены две версии формулы для извлечения нечетных и четных столбцов.
Чтобы получить нечетные столбцы (например, 1, 3, 5 и т. д.), используйте следующую формулу:
=ВЫБОРЦВЕТ(A4:E19, ПОСЛЕДОВАТЕЛЬНОСТЬ(ОКРУГЛВВЕРХ(СТОЛБЦЫ(A4:E19)/2, 0), 1, 1, 2))
Чтобы вернуть четные столбцы (например, 2, 4, 6 и т. д.), формула принимает следующий вид:
=ВЫБОРСЕКТОРОВ(A4:E19, ПОСЛЕДОВАТЕЛЬНОСТЬ(ОКРУГЛЕНИЕВНИЗ(СТОЛБЦЫ(A4:E19)/2, 0), 1, 2, 2))
На скриншоте ниже показана первая формула в действии:
Как работает эта формула:
Краткое пояснение: функция ВЫБОРЦВЕТ возвращает каждый второй столбец на основе массива последовательных нечетных или четных чисел, созданного функцией ПОСЛЕДОВАТЕЛЬНОСТЬ.
Подробная расшифровка формулы:
Первый шаг — вычислить, сколько столбцов нужно вернуть. Для этого воспользуемся одной из следующих формул:
ОКРУГЛВВЕРХ(СТОЛБЦЫ(A4:E19)/2, 0)
или
ОКРУГЛВНИЗ(СТОЛБЦЫ(A4:E19)/2, 0)
COLUMNS подсчитывает общее количество столбцов в исходном диапазоне. Вы делите это число на 2, а затем, в зависимости от того, извлекаете ли вы нечетные или четные столбцы, округляете частное вверх или вниз до целого числа с помощью ОКРУГЛВВЕРХ или ОКРУГЛВНИЗ. Округление необходимо, если исходный диапазон содержит нечетное количество столбцов, что дает остаток при делении на 2.
Наш исходный диапазон имеет 5 столбцов. Таким образом, для нечетных столбцов ОКРУГЛВВЕРХ(5/2, 0) возвращает 3, а для четных столбцов ОКРУГЛВНИЗ(5/2, 0) возвращает 2.
Возвращенное число передается в первый аргумент (строки) функции ПОСЛЕДОВАТЕЛЬНОСТЬ.
Для нечетных столбцов получаем:
ПОСЛЕДОВАТЕЛЬНОСТЬ(3, 1, 1, 2)
Эта формула ПОСЛЕДОВАТЕЛЬНОСТЬ создает массив чисел, состоящий из 3 строк и 1 столбца, начиная с 1 и увеличивая на 2, что равно {1;3;5}.
Для четных столбцов имеем:
ПОСЛЕДОВАТЕЛЬНОСТЬ(2, 1, 2, 2)
В этом случае ПОСЛЕДОВАТЕЛЬНОСТЬ создает массив чисел, состоящий из 2 строк и 1 столбца, начиная с 2 и увеличивая на 2, что равно {2;4}.
Приведенный выше массив переходит к аргументу col_num1 команды CHOOSECOLS, и вы получаете желаемый результат.
Отразить массив по горизонтали в Excel
Чтобы изменить порядок столбцов в массиве слева направо, вы можете использовать функции CHOOSECOLS, SEQUENCE и COLUMNS вместе следующим образом:
=ВЫБОРСЕКТОРОВ(A4:D19, ПОСЛЕДОВАТЕЛЬНОСТЬ(СТОЛБЦЫ(A4:D19)) *-1)
В результате исходный диапазон переворачивается по горизонтали, как показано на изображении ниже:
Как работает эта формула:
Здесь мы используем функцию ПОСЛЕДОВАТЕЛЬНОСТЬ для создания массива, содержащего столько порядковых номеров, сколько столбцов в исходном массиве. Для этого мы вкладываем COLUMNS(A4:D13) в аргумент rows:
ПОСЛЕДОВАТЕЛЬНОСТЬ(СТОЛБЦЫ(A4:D19))
Другие аргументы (столбцы, начало, шаг) опущены, поэтому по умолчанию они равны 1. В результате ПОСЛЕДОВАТЕЛЬНОСТЬ создает массив последовательных чисел, таких как 1, 2, 3, …, n, где n — индекс последнего столбец в массиве. Чтобы функция CHOOSECOLS считала столбцы справа налево, мы умножаем каждый элемент сгенерированной последовательности на -1. В результате мы получаем массив отрицательных чисел, таких как {-1;-2;-3}, которые переходят к аргументу col_num функции CHOOSECOLS, указывая ей вернуть соответствующие столбцы с правой стороны массива:
ВЫБОРЦВЕТ(A4:D19, {-1;-2;-3;-4})
Извлечь столбцы на основе строки с числами
В ситуации, когда номера индексов целевых столбцов представлены в виде текстовой строки, вы можете использовать функцию TEXTSPLIT, чтобы разбить строку по заданному разделителю, а затем передать полученный массив чисел в CHOOSECOLS.
Допустим, номера столбцов перечислены в ячейке H3, разделенные запятой и пробелом. Чтобы получить интересующие столбцы, используйте эту формулу:
=ВЫБРАТЬСЕКЦИИ(A4:E19, РАЗДЕЛЕНИЕ ТЕКСТА(H3, «, «) *1)
Как работает эта формула:
Во-первых, вы разделяете строку заданным разделителем (в нашем случае это запятая и пробел):
РАЗДЕЛЕНИЕ ТЕКСТА(H3,» «»)
Промежуточным результатом является массив текстовых значений, таких как {«1″,»4″,»5»}. Чтобы преобразовать текст в числа, умножьте элементы массива на 1 или выполните любую другую математическую операцию, которая не изменяет исходные значения.
ТЕКСТРАЗДЕЛИТЬ(H3,» «») *1
Это создает массив числовых значений {1,4,5}, которые может обработать функция CHOOSECOLS, и вы получите результат, который ищете:
ВЫБОРЦВЕТ(A4:E19, {1,4,5})
Извлечение столбцов из нескольких диапазонов
Чтобы получить определенные столбцы из нескольких несмежных диапазонов, вы сначала объединяете все диапазоны в один с помощью функции VSTACK, а затем обрабатываете объединенный диапазон с помощью CHOOSECOLS.
Например, чтобы вернуть столбцы 1 и 3 из диапазонов A4:D8, A12:D15 и A19:D21, используйте следующую формулу:
=ВЫБОРСЕКТОРОВ(ВССТЕК(A4:D8, A12:D15, A19:D21), 1, 3)
Функция CHOOSECOLS не работает
Если формула CHOOSECOLS выдает ошибку, она, скорее всего, будет одной из следующих.
#ЦЕНИТЬ! ошибка
Происходит, если абсолютное значение любого аргумента col_num равно нулю или превышает общее количество столбцов в указанном массиве.
#НАЗВАНИЕ? ошибка
Происходит, если имя функции написано с ошибкой или функция недоступна в вашей версии Excel. В настоящее время CHOOSECOLS поддерживается только в Excel 365 и Excel для Интернета.
#ИГРА! ошибка
Происходит, когда что-то мешает формуле распространить результаты в соседние ячейки. Чтобы это исправить, просто очистите мешающие ячейки. Дополнительные сведения см. в разделе Как исправить ошибку #SPILL! ошибка в экселе.
Вот как можно использовать функцию CHOOSECOLS в Excel для возврата определенных столбцов из диапазона или массива. Спасибо за внимание и до встречи в нашем блоге на следующей неделе!
Практическая рабочая тетрадь для скачивания
Формула Excel CHOOSECOLS — примеры (файл .xlsx)
Вас также могут заинтересовать
Задача функции
СТОЛБЕЦ(
)
, английский вариант COLUMN(),
— возвращать номер столбца. Формула
=
СТОЛБЕЦ(B1)
вернет 2, т.к. столбец B — второй столбец на листе.
Синтаксис функции
СТОЛБЕЦ()
СТОЛБЕЦ
(
ссылка
)
Ссылка
— необязательный аргумент. Ячейка или диапазон ячеек, для которого требуется возвратить номер столбца.
В качестве ссылки указан адрес одной ячейки
Если в качестве ссылки указан адрес одной ячейки, то функция вернет номер столбца этой ячейки
=СТОЛБЕЦ(B1)
вернет 2,
=СТОЛБЕЦ(F2)
вернет 6. Чтобы убедиться, что номер столбца F действительно 6 включите стиль ссылок R1C1 (
)
Если требуется задать название столбца через буквенное обозначение, то используйте формулу
=СТОЛБЕЦ(ДВССЫЛ(«F»&3))
Аргумент ссылки опущен
Если аргумент ссылки опущен, то предполагается, что это ссылка на ячейку, в которой находится сама функция
СТОЛБЕЦ()
Т.е. если формула =
СТОЛБЕЦ()
введена в ячейку
С5
, то формула вернет 3.
В качестве ссылки указан диапазон ячеек
Если в качестве ссылки указан диапазон ячеек, и формула введена как обычно (не как
формула массива
), то функция вернет номер столбца левой верхней ячейки диапазона.
=СТОЛБЕЦ(D26:G26)
вернет 4, т.е. номер столбца
D
.
Если в
Строке формул
выделить формулу и нажать
клавишу F9
, то мы увидим, что результатом формулы является не одно значение, а четыре {4;5;6;7} Так как в ячейке можно вывести только одно значение, то выводится, только первое, т.е. 4.
Чтобы вывести все 4 значения (см. статью
Формулы массива, возвращающие несколько значений
), выделите четыре ячейки, расположенные по горизонтали (
A27:D27
), в
Строке формул
введите формулу
=СТОЛБЕЦ(D26:G26)
и нажмите
CTRL+SHIFT+ENTER
. см.
файл примера
.
Формирование массива последовательных чисел
Записав формулу
=СТОЛБЕЦ(A26:D26)
мы сформируем
массив последовательных чисел
, который можно использовать в других задачах. Например, найдем сумму числового ряда 1+1/2+1/3+1/4+1/5+1/6+1/7 с помощью формулы
=СУММПРОИЗВ(1/СТОЛБЕЦ(A1:G1))
Сумму ряда от 1 до 1/n^2, где n = 7 можно вычислить так:
=СУММПРОИЗВ(1/(СТОЛБЕЦ(A1:G1)^2))