Поиск числа в диапазоне в excel

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

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

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

В этой статье

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

Для выполнения этой задачи можно использовать функцию ВПР или сочетание функций индекс и ПОИСКПОЗ.

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

Дополнительные сведения можно найти в разделе функция ВПР.

Примеры ИНДЕКСов и СОВПАДЕНИй

=ИНДЕКС(нужно вернуть значение из C2:C10, которое будет соответствовать ПОИСКПОЗ(первое значение «Капуста» в массиве B2:B10))

Формула ищет первое значение в ячейке C2: C10, соответствующее капусты (в B7), и возвращает значение в C7 (100) — первое значение, соответствующее капусты.

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

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

Для этого используйте функцию ВПР.

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

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

Дополнительные сведения можно найти в разделе функция ВПР.

Поиск значений по вертикали в списке неизвестного размера с точным соответствием

Для выполнения этой задачи используйте функции СМЕЩ и ПОИСКПОЗ.

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

C1 — это верхняя левая ячейка диапазона (также называемая начальной ячейкой).

Match («апельсины»; C2: C7; 0) ищет оранжевый цвет в диапазоне C2: C7. Не следует включать начальную ячейку в диапазон.

1 — количество столбцов справа от начальной ячейки, для которых должно быть возвращено возвращаемое значение. В нашем примере возвращаемое значение находится в столбце D, Sales.

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

Для выполнения этой задачи используется функция ГПР. Ниже приведен пример.

Функция ГПР выполняет поиск по столбцу Sales и возвращает значение из строки 5 в указанном диапазоне.

Дополнительные сведения можно найти в разделе функции ГПР.

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

Для выполнения этой задачи используется функция ГПР.

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

В приведенном выше примере функция ГПР ищет значение 11000 в строке 3 в указанном диапазоне. Он не находит 11000 и, следовательно, ищет следующее наибольшее значение, которое меньше 1100 и возвращает число 10543.

Дополнительные сведения можно найти в разделе функции ГПР.

Создание формулы подстановки с помощью мастера подстановок (толькоExcel 2007 )

Примечание: Надстройка «Мастер подстановок» прекращена в Excel 2010. Эти функциональные возможности заменены мастером функций и доступными функциями поиска и работы со ссылками (ссылками).

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

Щелкните ячейку в диапазоне.

На вкладке формулы в группе решения нажмите кнопку Подстановка .

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

Загрузка программы-надстройки «Мастер подстановок»

Нажмите кнопку Microsoft Office , щелкните Параметры Excelи выберите категорию надстройки.

В поле Управление выберите элемент Надстройки Excel и нажмите кнопку Перейти.

В диалоговом окне надстройки установите флажок Мастер подстановоки нажмите кнопку ОК.

Функции поиска данных: разыскивается информация!

Привет, друзья. Как часто вам приходится для какого-то значения искать соответствие в таблице Эксель? Например, нужно в справочнике найти адрес человека, или в прайсе – цену товара. Если такие задачи встречаются – этот пост именно для вас!

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

Поиск в таблице Эксель, функции ВПР и ГПР

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

Синтаксис функции ВПР такой: =ВПР( Искомое_значение; таблица_для_поиска; номер_выводимого_столбца; [тип_сопоставления]) . Рассмотрим аргументы:

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

Поиск точного совпадения с помощью ВПР

Посмотрим на примере, как работает функция ВПР, когда выбран тип сопоставления «ЛОЖЬ», поиск точного совпадения. В массиве В5:Е10 указаны основные средства некой компании, их балансовая стоимость, инвентарный номер и место расположения. В ячейке В2 указано наименование, для которого нужно в таблице найти инвентарный номер и поместить его в ячейку С2 .

Функция ВПР в Excel

Запишем формулу: =ВПР(B2;B5:E10;3;ЛОЖЬ) .

Здесь первый аргумент указывает, что в таблице нужно искать значение из ячейки В2 , т.е. слово «Факс». Второй аргумент говорит, что таблица для поиска — в диапазоне В5:Е10 , а искать слово «Факс» нужно в первом столбце, т.е. в массиве В5:В10 . Третий аргумент сообщает программе, что результат расчета содержится в третьем столбце массива, т.е. D5:D10 . Четвёртый аргумент равен «ЛОЖЬ», т.е. требуется полное совпадение.

И так, функция получит строку «Факс» из ячейки В2 и будет искать его в массиве В5:В10 сверху вниз. Как только совпадение будет найдено (строка 8), функция вернёт соответствующее значение из столбца D , т.е. содержимое D8 . Именно это нам и требовалось, задача решена.

Если искомое значение не будет найдено, функция вернёт ошибку #Н/Д.

Поиск неточного совпадения с помощью ВПР

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

В массиве В5:С12 указаны процентные ставки по кредитам в зависимости от суммы займа. В ячейке В2 Указываем сумму кредита и хотим получить в С2 ставку для такой сделки. Задача сложна тем, что сумма может быть любой и вряд ли будет совпадать с указанными в массиве, поиск по точному совпадению не подходит:

Тогда запишем формулу нестрогого поиска: =ВПР(B2;B5:C12;2;ИСТИНА) . Теперь из всех представленных в столбце В данных программа будет искать ближайшее меньшее. То есть, для суммы 8 000 будет отобрано значение 5000 и выведен соответствующий процент.

Нестрогий поиск ВПР в Excel

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

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

Поиск данных с помощью функции ПРОСМОТР

Функция ПРОСМОТР работает аналогично ВПР, но имеет другой синтаксис. Я использую её, когда таблица данных содержит несколько десятков столбцов и для использования ВПР нужно дополнительно просчитывать номер выводимой колонки. В таких случаях функция ПРОСМОТР облегчает задачу. И так, синтаксис: =ПРОСМОТР(Искомое_значение; Массив_для_поиска; Массив_для_отображения) :

• Искомое значение – данные или ссылка на данные, которые нужно искать;
• Массив для поиска – одна строка или столбец, в котором ищем аналогичное значение. Данный массив обязательно сортируем по возрастанию;
• Массив для отображения – диапазон, содержащий данные для выведения результатов. Естественно, он должен одного размера с массивом для поиска.

При такой записи вы даёте не относительную ссылку массива результатов. А прямо на него указываете, т.е. не нужно предварительно просчитывать номер выводимого столбца. Используем функцию ПРОСМОТР в первом примере для функции ВПР (основные средства, инвентарные номера): =ПРОСМОТР(B2;B5:B10;D5:D10) . Задача успешно решена!

Функция «ПРОСМОТР» в Microsoft Excel

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

Еще один способ поиска данных – комбинирование функций ПОИСКПОЗ и ИНДЕКС.

Первая из них, служит для поиска значения в массиве и получения его порядкового номера: ПОИСКПОЗ(Искомое_значение; Просматриваемый_массив; [Тип сопоставления]). Аргументы функции:

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

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

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

Теперь скомбинируем эти функции, чтобы получить результат:

Функции ПОИСКПОЗ и ИНДЕКС в Эксель

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

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

Добрый день уважаемый читатель!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Поиск числа в диапазоне в excel

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

Функция ВПР

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

Синтаксис: =ВПР(ключ; диапазон; номер_столбца; [интервальный_просмотр]), где

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

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

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

Для цены необходимо использовать функцию ВПР с точным совпадением (интервальный просмотр ЛОЖЬ), так как данный параметр определен для всех товаров и не предусматривает использование цены другого товара, если вдруг она по случайности еще не определена.

Для определения расположения товара используется ВПР с приблизительным совпадением (интервальный просмотр ИСТИНА), так как распределение товара осуществляется по категориям. Из-за того, что наименование товара состоит из названия категории плюс дополнительный текст, по условиям сортировки от А до Я наименования товаров будут идти сразу после наименования категории, поэтому когда в таблице не обнаруживается совпадений с ключом подтягивается первое значение сверху.

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

Он подобного эффекта можно избавиться путем определения категории из наименования товара используя текстовые функции ЛЕВСИМВ(C11;ПОИСК(» «;C11)-1), которые вернут все символы до первого пробела, а также изменить интервальный просмотр на точный.

Помимо всего описанного, функция ВПР позволяет применять для текстовых значений подстановочные символы – * (звездочка – любое количество любых символов) и ? (один любой символ). Например, для искомого значения «*» & «иван» & «*» могут подойти строки Иван, Иванов, диван и т.д.

Также данная функция может искать значения в массивах – =ВПР(1;<2;»Два»:1;»Один»>;2;ЛОЖЬ) – результат выполнения строка «Два».

Функция ГПР

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

Синтаксис: =ГПР(ключ; диапазон; номер_строки; [интервальный_просмотр]).

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

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

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

Синтаксис: =СТРОКА([ссылка]), где аргумент «ссылка» не является обязательным. Если он опущен, но возвращается номер текущей строки.

=СТРОКА(D4) – результат 4.
=СТРОКА() – функция вернет номер строки, в которой она расположена.

Функция СТОЛБЕЦ

Возвращает номер столбца ячейки, указанной ссылкой.

Синтаксис: =СТОЛБЕЦ([ссылка]), где «ссылка» не обязательный аргумент. По умолчанию возвращается номер текущего столбца.

=СТОЛБЕЦ(C4) – формула вернет значение 3.
=СТОЛБЕЦ() – функция возвращает номер столбца, в котором расположена.

Функция АДРЕС

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

Синтаксис: =АДРЕС(строка; столбец; [тип_закрепления]; [стиль_ссылки]; [имя_листа]), где:

• Строка – обязательный аргумент. Число, представляющая номер строки, для которой необходимо вернуть адрес;
• Столбец – обязательный аргумент. Число, представляющее номер столбца целевой ячейки.
• тип_закрепления – необязательный аргумент. Число от 1 до 4, обозначающее закрепление индексов ссылки:
  • 1 – значение по умолчанию, когда закреплены все индексы;
  • 2 – закрепление индекса строки;
  • 3 – закрепление индекса столбца;
  • 4 – адрес без закреплений.
• стиль_ссылки – необязательный аргумент. Логическое значение:
  • ИСТИНА – формат ссылок «A1»;
  • ЛОЖЬ – формат ссылок «R1C1».
• имя_листа – необязательный аргумент. Строка, представляющая имя листа, который необходимо добавить к тексту адреса ячейки.

=АДРЕС(1;1) – возвращает $A$1.
=АДРЕС(1;1;4) – возвращает A1.
=АДРЕС(1;1;4;ЛОЖЬ) – результат R[1]C[1].
=АДРЕС(1;1;4;ЛОЖЬ;»Лист1″) – результат выполнения функции Лист1!R[1]C[1].

Функция ДВССЫЛ

Преобразует адрес ссылки, заданный текстовой строкой, в ссылку на данный адрес.

Синтаксис: =ДВССЫЛ(адрес_ссылки; [стиль_ссылки]), где

• адрес_ссылки – обязательный аргумент. Строка, представляющая адрес ссылки на ячейку или диапазон. Например, «C3», «R3C3» или «D8:D9».
• стиль_ссылки – необязательный аргумент. Логическое значение, определяющее стиль ссылки:
  • ИСТИНА – стиль A1. Является значением по умолчанию;
  • ЛОЖЬ – стиль R1C1.

=ДВССЫЛ(«a3») – возвращает ссылку на ячейку A3.
=ДВССЫЛ(«r3c3») – вернет ошибку #ССЫЛКА!, так как текст для ссылки в формате R1C1, а второй аргумент имеет значение по умолчанию.
=ДВССЫЛ(«r3c3»; ЛОЖЬ) – возвращает ссылку на ячейку C3.
=ДВССЫЛ(АДРЕС(СТРОКА(C3);СТОЛБЕЦ(C3))) – функция вернет аналогичный предыдущему примеру результат.
Вложение функции ДВССЫЛ со ссылкой на диапазон:

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

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

Синтаксис: =ЧСТРОК(ссылка), где «ссылка» обязательный аргумент, являющийся ссылкой на ячейку, диапазон либо массив.

Пример использования:
=ЧСТРОК(D1:D8) – функция возвращает результат 8.
=ЧСТРОК(<1:2:3:4:5>) – функция определят, что в массиве 5 строк.

Функция ЧИСЛСТОЛБ

Возвращает число столбцов в указанном диапазоне или массиве.
Синтаксис: =ЧИСЛСТОЛБ(ссылка), где «ссылка» обязательный аргумент, являющийся ссылкой на ячейку, диапазон либо массив.
Пример использования:
=ЧИСЛСТОЛБ(A5:D5) – результат функции 4.
=ЧИСЛСТОЛБ(<1;2;3;4;5>) – функция определят, что в массиве 5 столбцов.

Функция СМЕЩ

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

Синтаксис: =СМЕЩ(ссылка; смещ_по_строкам; смещ_по_столбцам; [высота]; [ширина]), где

ссылка – обязательный аргумент.

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

Если в результате смещения диапазон стал выходить на пределы листа, то функция возвращает ошибку #ССЫЛКА!.

Для примера будем использовать вложение функции СМЕЩ в функцию СУММ.
Изначально ссылаемся на диапазон из 10 строк и 1 столбца, где все ячейки имеют значение 2. Таким образом получает результат выполнения формулы – 20.

Теперь сместим диапазон на один столбец влево, т.е. на -1.

Снова изменим ссылку, а именно расширим до 4 столбцов. После этого возвращаемая ссылка станет A3:D12. Результат на изображении.

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

Возвращает позицию элемента, заданного по значению, в диапазоне либо массиве.

Синтаксис: =ПОИСКПОЗ(искомое_значение; массив; [тип_сопоставления]), где:

• искомое_значение – обязательный аргумент. Значение элемента, который необходимо найти в массиве.
• Массив – обязательный аргумент. Одномерный диапазон либо массив для поиска элемента.
• тип_сопоставления – необязательный аргумент. Число 1, 0 или -1, определяющее способ поиска элемента:
  • 1 – значение по умолчанию. Если совпадений не найдено, то возвращается позиция ближайшего меньшего по значению к искомому элементу. Массив или диапазон должен быть отсортирован от меньшего к большему или от А до Я.
  • 0 – функция ищет точное совпадение. Если не найдено, то возвращается ошибка #Н/Д.
  • -1 – Если совпадений не найдено, то возвращается позиция ближайшего большего по значению к искомому элементу. Массив или диапазон должен быть отсортирован по убыванию.

Пример использования:
=ПОИСКПОЗ(«Г»; <«а»;»б»;»в»;»г»;»д»>) – функция возвращает результат 4.

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

При этом регистр не учитывается.
=ПОИСКПОЗ(«е»; <«а»;»б»;»в»;»г»;»д»>; 1) – результат 5, т.к. элемента не найдено, поэтому возвращается ближайший меньший по значению элемент. Элементы массива записаны по возрастанию.
=ПОИСКПОЗ(«е»; <«а»;»б»;»в»;»г»;»д»>; 0) – возвращается ошибка, т.к. элемент не найден, а тип сопоставления указан на точное совпадение.
=ПОИСКПОЗ(«в»; <«д»;»г»;»в»;»б»;»а»>; -1) – результат 3.
=ПОИСКПОЗ(«д»; <«а»;»б»;»в»;»г»;»д»>; -1) – элемент не найден, хотя присутствует в массиве. Функция возвращает неверный результат, так как последний аргумент принимает значение -1, а элементы НЕ расположены по убыванию.

Для текстовых значений функция допускает использование подстановочных символов «*» и «?».

Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы мы могли развивать его дальше.

У Вас недостаточно прав для комментирования.

Функции поиска данных: разыскивается информация!

Привет, друзья. Как часто вам приходится для какого-то значения искать соответствие в таблице Эксель? Например, нужно в справочнике найти адрес человека, или в прайсе – цену товара. Если такие задачи встречаются – этот пост именно для вас!

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

Поиск в таблице Эксель, функции ВПР и ГПР

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

Синтаксис функции ВПР такой: =ВПР( Искомое_значение; таблица_для_поиска; номер_выводимого_столбца; [тип_сопоставления]) . Рассмотрим аргументы:

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

Поиск точного совпадения с помощью ВПР

Посмотрим на примере, как работает функция ВПР, когда выбран тип сопоставления «ЛОЖЬ», поиск точного совпадения. В массиве В5:Е10 указаны основные средства некой компании, их балансовая стоимость, инвентарный номер и место расположения. В ячейке В2 указано наименование, для которого нужно в таблице найти инвентарный номер и поместить его в ячейку С2 .

Функция ВПР в Excel

Запишем формулу: =ВПР(B2;B5:E10;3;ЛОЖЬ) .

Здесь первый аргумент указывает, что в таблице нужно искать значение из ячейки В2 , т.е. слово «Факс». Второй аргумент говорит, что таблица для поиска — в диапазоне В5:Е10 , а искать слово «Факс» нужно в первом столбце, т.е. в массиве В5:В10 . Третий аргумент сообщает программе, что результат расчета содержится в третьем столбце массива, т.е. D5:D10 . Четвёртый аргумент равен «ЛОЖЬ», т.е. требуется полное совпадение.

И так, функция получит строку «Факс» из ячейки В2 и будет искать его в массиве В5:В10 сверху вниз. Как только совпадение будет найдено (строка 8), функция вернёт соответствующее значение из столбца D , т.е. содержимое D8 . Именно это нам и требовалось, задача решена.

Если искомое значение не будет найдено, функция вернёт ошибку #Н/Д.

Поиск неточного совпадения с помощью ВПР

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

В массиве В5:С12 указаны процентные ставки по кредитам в зависимости от суммы займа. В ячейке В2 Указываем сумму кредита и хотим получить в С2 ставку для такой сделки. Задача сложна тем, что сумма может быть любой и вряд ли будет совпадать с указанными в массиве, поиск по точному совпадению не подходит:

Тогда запишем формулу нестрогого поиска: =ВПР(B2;B5:C12;2;ИСТИНА) . Теперь из всех представленных в столбце В данных программа будет искать ближайшее меньшее. То есть, для суммы 8 000 будет отобрано значение 5000 и выведен соответствующий процент.

Нестрогий поиск ВПР в Excel

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

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

Поиск данных с помощью функции ПРОСМОТР

Функция ПРОСМОТР работает аналогично ВПР, но имеет другой синтаксис. Я использую её, когда таблица данных содержит несколько десятков столбцов и для использования ВПР нужно дополнительно просчитывать номер выводимой колонки. В таких случаях функция ПРОСМОТР облегчает задачу. И так, синтаксис: =ПРОСМОТР(Искомое_значение; Массив_для_поиска; Массив_для_отображения) :

• Искомое значение – данные или ссылка на данные, которые нужно искать;
• Массив для поиска – одна строка или столбец, в котором ищем аналогичное значение. Данный массив обязательно сортируем по возрастанию;
• Массив для отображения – диапазон, содержащий данные для выведения результатов. Естественно, он должен одного размера с массивом для поиска.

При такой записи вы даёте не относительную ссылку массива результатов. А прямо на него указываете, т.е. не нужно предварительно просчитывать номер выводимого столбца. Используем функцию ПРОСМОТР в первом примере для функции ВПР (основные средства, инвентарные номера): =ПРОСМОТР(B2;B5:B10;D5:D10) . Задача успешно решена!

Функция «ПРОСМОТР» в Microsoft Excel

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

Еще один способ поиска данных – комбинирование функций ПОИСКПОЗ и ИНДЕКС.

Первая из них, служит для поиска значения в массиве и получения его порядкового номера: ПОИСКПОЗ(Искомое_значение; Просматриваемый_массив; [Тип сопоставления]). Аргументы функции:

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

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

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

Теперь скомбинируем эти функции, чтобы получить результат:

Функции ПОИСКПОЗ и ИНДЕКС в Эксель

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