Поиск значения в диапазоне ячеек excel - Учим Эксель

4 метода поиска данных в таблице Excel

Function VPR s uslovismi 1 4 способа поиска данных в таблице Excel Хороший денек почетаемый читатель!

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

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

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

  1. Композиции с функцией СУММПРОИЗВ;
  2. Работа с функцией ВЫБОР;
  3. Через создание доп столбика;
  4. Совместная работа с функциями ПОИСКПОЗ и ИНДЕКС.

Используем функцию СУММПРОИЗВ

Как я уже описывал ранее в собственной статье о функции СУММПРОИЗВ, она является одной из мощнейших в арсенале Excel. И конкретно 1-ый метод мы создадим при помощи способностей формулы при использовании функции СУММПРОИЗВ. Для наших целей формула будет смотреться так:

Function VPR s uslovismi 2 4 способа поиска данных в таблице Excel

=СУММПРОИЗВ((C2:C11=G2)*(B2:B11=G3);D2:D11) Механизм работы формулы последующий: создается условная таблица, в которой значения ячеек «G2» сравнивается с спектром «C2:C11» и ячейка «G3» с спектром «B2:B11». Опосля этого сравниваются и сопоставляются все эти два массива и переводятся в единицы и нули, где значение единицы ставится строке, где все условия формулы выполнены. Последующая операция – это умножения приобретенного условного массива на спектр «D2:D11», а так как в массиве всего одна единичка то формула получит итог 146.

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

Применение функции ВЫБОР

Я описывал уже функцию ВЫБОР, но в таком выполнении еще не упоминал. В нашем случае необходимо сделать новейшую таблицу, в которой будут совместными столбики «Период» и «Месяц», всё это виртуально создаст функция ВЫБОР. Формула для работы будет смотреться так:

Function VPR s uslovismi 3 4 способа поиска данных в таблице Excel

<=ВПР(G2&G3;ВЫБОР(<1;2>;C2:C11&B2:B11;D2:D11);2;0)> Основная работа, которую делает функция ВЫБОР в собственной части «ВЫБОР(<1;2>;C2:C11&B2:B11;D2:D11)» это объединение значений столбиков «Период» и «Город» в общий массив, значения в котором будут прописаны как: «МоскваЯнварь», «БрянскФевраль», …. и т.д. Получив такое объединённое значения столбиков мы сможем просто создать просмотр и отбор подходящего значения, вот сейчас я думаю, формула стала поближе.

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

Создаем доп столбики

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

Разглядим на обычном примере, когда нужно найти реализации по двум показателям: «Период» и «Город». В этом случае обычное внедрение функции ВПР не будет нам подступать, потому что функция может возвращать значение по одному условию. В таком случае нам нужно сделать доп столбик, в котором произойдёт объединение 2-ух критериев в один, потому в сделанном столбике приписываем формулу слияния значений: =B2&C2. А вот сейчас итог из столбика D, мы сможем употреблять в ячейке H4 нашу формулу:

=ВПР(H2&H3;D2:E11;2;0)

Function VPR s uslovismi 4 4 способа поиска данных в таблице Excel

Видите ли, наши отдельные условия отбора значений также соединяются воединыжды аргументом H2&H3 в один аспект. Опосля поиска в обозначенном диапазоне D2:E11, формула вернёт отысканное значение со столбика 2.

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

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

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

Что все-таки она делает, таковая большая и непонятная…. Разглядим ее в разрезе нескольких блоков либо шагов. Формула для функции имеет таковой вид ПОИСКПОЗ (1;(B2:B11=G3)*(C2:C11=G2);0) и происходит последующее, со значением в ячейке G3, поочередно сравниваются значения из спектра B2:B11 и в случае совпадения критерий получаем итог ИСТИНА, а если есть отличия получаем ЛОЖЬ. Таковой же процесс происходит для значения G2 и спектра C2:C11. Опосля сопоставления этих массивов, которые состоят из аргументов ИСТИНА и ЛОЖЬ, делается сопоставления на соответствие значению 1, это ИСТИНА*ИСТИНА, все другие композиции будут проигнорированы.

Function VPR s uslovismi 5 4 способа поиска данных в таблице Excel

Сейчас, когда функция ПОИСКПОЗ отыскала в массиве значение, которое соответствует «1» и указала его позицию в 6-ой строке, а означает, в функцию ИНДЕКС был передан аргумент «6» для спектра D2:D11.

Ну, подведя результат можно ответить на закономерный вопросец: «а что все-таки созодать?» и «какой метод употреблять?». Употреблять вы сможете полностью хоть какой метод, но я бы рекомендовал избрать для вас более удачный, обычной и понятный. Я, например, люблю употреблять таблицы, которые просто изменять и ординарны для работы и осознания, чего же советую и для вас.

Интересно почитать:  Excel как вставить таблицу в ячейку

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

Функция ПОИСКПОЗ() в EXCEL

Функция ПОИСКПОЗ( ) , британский вариант MATCH(), возвращает позицию значения в диапазоне ячеек. К примеру, если в ячейке А10 содержится значение «яблоки», то формула =ПОИСКПОЗ («яблоки»;A9:A20;0) возвратит 2, т.е. разыскиваемое значение «яблоки» содержится во 2-ой ячейке спектра A9:A20 : А9 — 1-ая ячейка (предполагается, что в ней не содержится значение «яблоки»), А10 — 2-ая, А11 — 3-я и т.д. (подсчет позиции делается от верхней ячейки) .

Функция ПОИСКПОЗ() возвращает позицию искомого значения, а не само значение. К примеру: ПОИСКПОЗ(«б»;<"а";"б";"в";"б">;0) возвращает число 2 — относительную позицию буковкы «б» в массиве <"а";"б";"в";"б">. Позиция 2-ой буковкы «б» будет проигнорирована, функция возвратит позицию лишь первой буковкы. О том как возвратить ВСЕ позиции искомого значения читайте ниже в разделе Поиск позиций ВСЕХ текстовых значений, удовлетворяющих аспекту .

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

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

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

Просматриваемый_массив — непрерывный спектр ячеек, может быть, содержащих разыскиваемые значения. Просматриваемый_массив быть может лишь одностолбцовым спектром ячеек, к примеру А9:А20 либо спектром, размещенным в одной строке, к примеру, А2:Е2 . Таковым образом формула =ПОИСКПОЗ(«слива»;A30:B33;0) работать не будет (выдаст ошибку #Н/Д), потому что Просматриваемый_массив представляет собой спектр ячеек размещенный сразу в нескольких столбцах и нескольких ячейках.

Тип_сопоставления — число -1, 0 либо 1. Тип_сопоставления показывает, как MS EXCEL сопоставляет искомое_значение со значениями в аргументе просматриваемый_массив.

  • Если тип_сопоставления равен 0, то функция ПОИСКПОЗ() находит 1-ое значение, которое в точности равно аргументу искомое_значение . Просматриваемый_массив быть может не упорядочен.
  • Если тип_сопоставления равен 1, то функция ПОИСКПОЗ() находит наибольшее значение, которое меньше или равно, чем искомое_значение . Просматриваемый_массив должен быть упорядочен по возрастанию: . -2, -1, 0, 1, 2, . A-Z, ЛОЖЬ, ИСТИНА. Если тип_сопоставления опущен, то предполагается, что он равен 1.
  • Если тип_сопоставления равен -1, то функция ПОИСКПОЗ() находит меньшее значение, которое больше или равно чем искомое_значение . Просматриваемый_массив должен быть упорядочен по убыванию: ИСТИНА, ЛОЖЬ, Z-A, . 2, 1, 0, -1, -2, . и так дальше.

Функция ПОИСКПОЗ() не различает РеГИстры при сравнении текстов.

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

Поиск позиции в массивах с текстовыми значениями

Произведем поиск позиции в НЕ сортированном перечне текстовых значений (спектр B7:B13 )

Столбец Позиция приведен для наглядности и не влияет на вычисления.

Формула для поиска позиции значения Груши: =ПОИСКПОЗ(«груши»;B7:B13;0)

Формула находит 1-ое значение сверху и выводит его позицию в диапазоне, 2-ое значение Груши учтено не будет.

Чтоб отыскать номер строчки, а не позиции в разыскиваемом диапазоне, можно записать последующую формулу: =ПОИСКПОЗ(«груши»;B7:B13;0)+СТРОКА($B$6)

Если разыскиваемое значение не найдено в перечне, то будет возвращено значение ошибки #Н/Д. К примеру, формула =ПОИСКПОЗ(«грейпфрут»;B7:B13;0) возвратит ошибку, т.к. значения «грейпфрут» в диапазоне ячеек B7:B13 нет.

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

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

Поиск позиции можно создавать не только лишь в спектрах ячеек, да и в массивах констант . К примеру, формула =ПОИСКПОЗ(«груши»;<"яблоки";"ГРУШИ";"мандарины">;0) возвратит значение 2.

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

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

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

В качестве аспекта можно задать «яблок*» и формула =ПОИСКПОЗ(«яблок*»;B53:B62;0) возвратит позицию текстового значения, начинающегося со слова яблок (если она есть в перечне).

Подстановочные знаки следует употреблять лишь для поиска позиции текстовых значений и Типом сравнения = 0 (3-ий аргумент функции).

Поиск позиций ВСЕХ текстовых значений, удовлетворяющих аспекту

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

Разглядим перечень с циклическими значениями в диапазоне B66:B72 . Найдем все позиции значения Груши .

Значение Груши находятся в позициях 2 и 5 перечня. При помощи формулы массива

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

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

Поиск позиции в массивах с Числами

1. Произведем поиск позиции в НЕ сортированном перечне числовых значений (спектр B8:B14 )

Столбец Позиция приведен для наглядности и не влияет на вычисления.

Найдем позицию значения 30 при помощи формулы =ПОИСКПОЗ(30;B8:B14;0)

Формула отыскивает четкое значение 30. Если в перечне его нет, то будет возвращена ошибка #Н/Д.

2. Произведем поиск позиции в отсортированном по возрастанию перечне числовых значений (спектр B31:B37 )

Интересно почитать:  Удалить пробел в начале ячейки в excel

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

Это можно создать при помощи формулы =ПОИСКПОЗ(45;B31:B37;1)

Направьте внимание, что тип сравнения =1 (3-ий аргумент функции).

3. Поиск позиции в перечне отсортированном по убыванию производится аналогично, но с типом сравнения = -1. В этом случае функция ПОИСКПОЗ() находит меньшее значение, которое больше или равно чем разыскиваемое значение.

Функции ПОИСКПОЗ() и ИНДЕКС()

Функции ПОИСКПОЗ() и ИНДЕКС() нередко употребляются совместно, т.к. разрешают по отысканной позиции в одном диапазоне вывести соответственное значение из другого спектра. Разглядим пример.

Найдем количество данного продукта на определенном складе. Для этого используем формулу

В файле примера , соответственный столбец и строчка выделены при помощи Условного форматирования .

СОВЕТ: Подробнее о поиске позиций можно прочесть в соответственном разделе веб-сайта: Поиск позиции .

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

Поиск значений в таблице «Excel» . Макрос.

Поиск в Эксель

Поиск в Эксель

Дальше описаны несколько вариантов поиска и фильтрации данных в таблице «Эксель».

Традиционный поиск «MS Office».
Условное форматирование (выделение подходящих ячеек цветом)
Настройка фильтров по одному либо нескольким значениям.
Фрагмент макроса для перебора ячеек в диапазоне и поиска подходящего значения.

1) Традиционный поиск (обычный).

Вызвать панель (меню) поиска можно сочетанием жарких кнопок ctrl+F. (Просто уяснить: F- Found).

Окно поиска состоит из поля, в которое вводится разыскиваемый фрагмент текста либо разыскиваемое число, вкладки с доп опциями («Характеристики») и клавиши «Отыскать».

Классический поиск в Excel

Традиционный поиск в Excel

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

Условное форматирование для разыскиваемых ячеек.

2) Очередной вариант поиска заключается в выделении всех ячеек содержащих разыскиваемое слово каким-либо цветом.

Таковой поиск реализуется через опцию условное форматирование.

Для форматирования ячеек следует выделить спектр ячеек, которых необходимо отыскать слово. Дальше на вкладке «Основная» надавить клавишу «Условное форматирование».

Настройка форматирования для выделения искомых слов

Настройка форматирования для выделения разыскиваемых слов

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

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

3) 3-ий метод поиска слов в таблице «Excel» — это внедрение фильтров.

Фильтр устанавливается во вкладке «Данные» либо сочетанием кнопок ctrl+shift+L.

Настройка фильтра для поиска слов

Настройка фильтра для поиска слов

Кликнув по треугольнику фильтра можно в контекстном меню избрать пункт «Текстовые фильтры», дальше «содержит…» и указать разыскиваемое слово.

Опосля нажатия клавиши «Ок» на Экране останутся лишь ячейки столбца, содержащие разыскиваемое слово.

4) Метод поиска номер четыре — это макрос VBA для поиска (перебора значений).

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

Sub Poisk()

ruexcel.ru макрос проверки значений (поиска)

Dim keyword As String

keyword = «Разыскиваемое слово» ‘присвоить переменной разыскиваемое слово

On Error Resume Next ‘при ошибке пропустить

For Each cell In Selection ‘для всх ячеек в выделении (выделенном диапазоне)

If cell.Value = «» Then GoTo Line1 ‘если ячейка пустая перейти на «Line1″

If InStr(StrConv(cell.Value, vbLowerCase), keyword) > 0 Then cell.Interior.Color = vbRed ‘если в ячейке содержится слово окрасить ее в красноватый цвет (поиск)

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

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

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

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

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

Синтаксис оператора ПОИСКПОЗ смотрится так:

Сейчас разглядим любой из 3-х этих аргументов в отдельности.

«Разыскиваемое значение» – это тот элемент, который следует найти. Он может иметь текстовую, числовую форму, также принимать логическое значение. В качестве данного аргумента может выступать также ссылка на ячейку, которая содержит хоть какое из перечисленных выше значений.

«Просматриваемый массив» – это адресок спектра, в котором размещено разыскиваемое значение. Конкретно позицию данного элемента в этом массиве и должен найти оператор ПОИСКПОЗ.

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

Интересно почитать:  Как в excel уместить текст в одной ячейке excel

Аргумент «Тип сравнения» не является неотклонимым. Он быть может пропущенным, если в нем нет надобности. В этом случае его значение по дефлоту равно «1». Использовать аргумент «Тип сравнения», до этого всего, имеет смысл лишь тогда, когда обрабатываются числовые значения, а не текстовые.

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

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

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

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

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

Переход в Мастер функций в Microsoft Excel

Переход к аргументам функции ПОИСКПОЗ в Microsoft Excel

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

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

В 3-ем поле «Тип сравнения» ставим число «0», потому что будем работать с текстовыми данными, и потому нам нужен четкий итог.

Аргументы функции ПОИСКПОЗ в Microsoft Excel

Результат вычисления функции ПОИСКПОЗ в Microsoft Excel

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

Выше мы разглядели самый простой вариант внедрения оператора ПОИСКПОЗ, но даже его можно заавтоматизировать.

    Для удобства на листе добавляем ещё два доп поля: «Данное значение» и «Номер». В поле «Данное значение» вбиваем то наименование, которое необходимо отыскать. Пусть сейчас это будет «Мясо». В поле «Номер» устанавливаем курсор и перебегаем к окну аргументов оператора этим же методом, о котором шел разговор выше.

Переход к аргументам функции в Microsoft Excel

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

Результаты обработки функции ПОИСКПОЗ в Microsoft Excel

Изменение искомого слова в Microsoft Excel

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

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

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

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

Сортировка в Microsoft Excel

Окно аргументов функции ПОИСКПОЗ для числового значения в Microsoft Excel

Результаты функции ПОИСКПОЗ для числового значения в Microsoft Excel

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

Метод 4: внедрение в сочетании с иными операторами

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

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

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

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

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

Сортировка от минимального к максимальному в Microsoft Excel

Вызов мастера функций в Microsoft Excel

Переход к аргументам функции ИНДЕКС в Microsoft Excel

Выбор типа функции ИНДЕКС в Microsoft Excel

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

Аргументы функции ИНДЕКС в Microsoft Excel

Результат функции ИНДЕКС в Microsoft Excel

Изменение приблизительной суммы в Microsoft Excel

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

Мы рады, что смогли посодействовать Для вас в решении препядствия.

Кроме данной для нас статьи, на веб-сайте еще 12327 инструкций.
Добавьте веб-сайт Lumpics.ru в закладки (CTRL+D) и мы буквально еще пригодимся для вас.

Отблагодарите создателя, поделитесь статьей в соц сетях.

Опишите, что у вас не вышло. Наши спецы постараются ответить очень стремительно.

Ссылка на основную публикацию
Adblock
detector