Функция поискпоз в excel примеры

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

Синтаксис этой функции следующий:

=ПОИСКПОЗ( Что_ищем ; Где_ищем ; Режим_поиска )

  • Что_ищем — это значение, которое надо найти
  • Где_ищем — это одномерный диапазон или массив (строка или столбец), где производится поиск
  • Режим_поиска — как мы ищем: точно (0), с округлением в большую строну (-1) или в меньшую сторону (1)

Давайте рассмотрим несколько полезных вариантов ее применения на практике.

Точный поиск

Классический сценарий — поиск точного текстового совпадения для нахождения позиции нужного нам текста или числа в списке:

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

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

Числа и пустые ячейки в этом случае игнорируются.

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

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

Например, нам нужно выбрать генератор из прайс-листа для расчетной мощности в 47 кВт. Если последний аргумент задать равным 1 и отсортировать таблицу по возрастанию, то мы найдем ближайшую наименьшую по мощности модель (Зверь):

Если же третий аргумент равен -1 и таблица отсортирована по убыванию, то мы найдем ближайшую более мощную модель (Бомба):

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

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

Так, в предыдущем примере получить не номер, а название модели генератора можно очень легко:

Ну, и поскольку Excel внутри хранит и обрабатывает даты как числа, то подобный подход на 100% работает и с датами. Например, мы можем легко определить на каком этапе сейчас находится наш проект:

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

Поиск в Excel.

​Смотрите также​​ для анализа таблиц​​ дубликатов формула для​​ найти по значению​
​ столбцов таблицы по​ инструментом: «ГЛАВНАЯ»-«Редактирование»-«Найти» CTRL+F,​ по этой таблице,​ находится 10 строк.​ в диапазоне где​
​ чего функции МАКС​​ максимальном объеме и​.​ ​ ГПР.​ поэтому функция ВПР​ несколько способов поиска​​ на строке «найти».​ такое слово. Затем​Часто возникает вопрос​ в ячейку F1​ заголовков берет заголовок​ 5277 вместо D9​ ​ значению. Для этого​ чтобы вызвать окно​ набрав конкретную фамилию.​​Далее функция ГПР поочередно​ хранятся названия месяцев​ остается только взять​ в каком месяце​В поле​К началу страницы​ ищет первую запись​ ​ значений в списке​​Для более расширенного​ нажимаете «найти далее»​​«​ введите новую формулу:​ с первого дубликата​ получить заголовки:​ выполните следующие действия:​ ​ поиска значений на​Рубероид​ ​ используя каждый номер​​ в любые случаи​ из этого массива​ была максимальная продажа​ ​Управление​Для выполнения этой задачи​ со следующим максимальным​​ данных и отображения​​ поиска нажмите кнопку​ и поиск перейдет​Как найти в Excel​После чего следует во​ ​ по горизонтали (с​для столбца таблицы –​В ячейку B1 введите​ листе Excel. Или​: CTRL + F​ ​ строки создает массив​ будет 1. Тогда​ максимальное число и​ Товара 4.​выберите значение​ используется функция ГПР.​ значением, не превышающим​ результатов.​ «Параметры» и выберите​ на второе такое​»?​


​ всех остальных формулах​ лева на право).​ Март;​ значение взятое из​ же создать для​ все найдется сразу​ соответственных значений продаж​ нам осталось функцией​ возвратить в качестве​Чтобы выполнить поиск по​Надстройки Excel​Важно:​

Читайте также:  Как округлять числа в большую и меньшую сторону функциями Excel


​ 6. Она находит​Поиск значений в списке​ нужный параметр поиска.​ слово.​В Excel можно​ изменить ссылку вместо​ А формула для​для строки – Товар4.​ таблицы 5277 и​ таблицы правило условного​ ​Еще можешь выбрать​​ из таблицы по​ ИНДЕКС получить соответственное​ значения для ячейки​​ столбцам следует:​​и нажмите кнопку​ ​ Значения в первой​ ​ значение 5 и возвращает​ по вертикали по​Например, выберем — «Значение».​А если надо показать​ найти любую информацию:​ B1 должно быть​ получения названия (номера)​Чтобы решить данную задачу​ выделите ее фон​ форматирования. Но тогда​ автофильтр по заголовкам​ определенному месяцу (Июню).​ значение из диапазона​ D1, как результат​В ячейку B1 введите​Перейти​ строке должны быть​ связанное с ним​ ​ точному совпадению​ Тогда будет искать​ сразу все такие​ текст, часть текста,​ F1! Так же​ строки берет номер​ будем использовать формулу​ синим цветом для​ нельзя будет выполнить​ колонок, и уже​ Далее функции МАКС​ B4:G4 – Февраль​ вычисления формулы.​ ​ значение Товара 4​.​ отсортированы по возрастанию.​ имя​


​Поиск значений в списке​ и числа, и​ слова, то нажимаем​ цифру, номер телефона,​ ​ нужно изменить ссылку​ с первого дубликата​ с уже полученными​ читабельности поля ввода​ дальнейших вычислений с​ затем в списке​ осталось только выбрать​ (второй месяц).​Как видно конструкция формулы​ ​ – название строки,​В области​​В приведенном выше примере​Алексей​ по вертикали по​ номер телефона, т.д.​ кнопку «найти все»​ эл. адрес​ в условном форматировании.​ ​ по вертикали (сверху​ значениями в ячейках​ (далее будем вводить​ полученными результатами. Поэтому​ фильтровать необходимых тебе​ максимальное значение из​​ ​ проста и лаконична.​ которое выступит в​Доступные надстройки​ функция ГПР ищет​.​ приблизительному совпадению​Если нужно найти​ и внизу поискового​,​ ​ Выберите: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление​ вниз). Для исправления​ C2 и C3.​ в ячейку B1​ необходимо создать и​ excel-office.ru>

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

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

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

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

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

    Синтаксис функции IF в Google Таблицах:

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

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

    Пример 1: проверка отдельного условия с помощью функции IF

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

    Вы можете легко узнать это, используя функцию IF в Google Таблицах.

    Вот формула, которая сделает это:

    Она проверяет, больше ли баллов 35 или нет. Если это так, то возвращается Pass, иначе возвращается Fail.

    Пример 2: проверка нескольких условий с помощью функции IF

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

    Например, ученик получает оценку F, если он / она набирает меньше 35, D для оценок от 35 до 50, C для оценок от 50 до 70, B для оценок от 70 до 90 и A выше 90 (как показано ниже ):

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

    Вот формула, которая даст вам результат в этом случае:

    Эта формула сначала проверяет, меньше ли результат 35 или нет. Если да, то она возвращает F, иначе переходит к следующему условию и так далее.

    Пример 3: Расчет комиссионных с использованием функции IF в Google Таблицах

    Функция Google Таблиц If позволяет выполнять вычисления в разделе значений. Хорошим примером этого является расчет комиссии по продажам для торгового представителя с помощью функции IF.

    В приведенном ниже примере торговый представитель не получает комиссию, если продажи меньше 50 тысяч, получает комиссию 4%, если продажи составляют от 50 до 80 тысяч, и 10% комиссии, если продажи превышают 80 тысяч.

    Вот используемая формула:

    В формуле, использованной в приведенном выше примере, расчет выполняется внутри самой функции IF. Когда значение продаж находится между 50-100K, возвращается B2 * 4%, что составляет комиссию в размере 4%, основанную на стоимости продажи.

    Пример 4: Использование операторов AND/OR в функции IF

    В функции IF вы можете использовать операторы AND/OR для одновременной проверки нескольких условий.

    Например, предположим, что у вас есть список студентов с их оценками и посещаемостью. Стипендию получают только те студенты, которые набрали более 80 баллов и посещаемость более 80%. Вы можете быстро идентифицировать таких студентов, используя условие AND в функции IF.

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

    Она работает, проверяя оба условия в функции AND. Если функция AND возвращает ИСТИНА, функция IF возвращает «Да», иначе она возвращает «Нет».

    Все ссылки и дополнительные соберем в одном месте.

    Специальные предложения

    Electronic Software Distribution

    Маркировка 488-ФЗ

    Интеграция 1С с системой Меркурий

    Алкогольная декларация

    Готовые переносы данных

    54-ФЗ

    Управление проектом на Инфостарте

    Траектория обучения 1С-разработчика

    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти

    2. Ну запускаю. Ну работает. 1-2 секунды на соединение. Потом все так, как будто это и не com.
    1.Понятно, что веб и http сервисы современнее и универсальнее. Но если специально для передачи 1 документа между двумя базами надо на сервере запускать IIS, то ну его на фиг. Тем более, что не всегда есть на это права. И не всегда сисадмин поведется на такое безобразие.
    (5)

    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти

    Если позволишь я тоже немного прикопаюсь к COM-соединению. Мой текущий проект на COM работает обмен между самописной базой Управленческие табеля и типовыми ЗУПами 2.5; 3.1 + Беларусь и Казахстан. Довольно удобно всё получилось. само чтение данных не так уж долго идёт. Учитывая 6000 сотрудников на выгрузку в самописную базу (именно чтение!) занимает 20 секунд, на выгрузку в ЗУП тоже меньше минуты секунд 50. Данных очень много в обе стороны гуляет. Назад вообще данные об отработанном времени, там главная таблица больше 100 тыс записей.

    Ну и собственно клиент настоял на COM технологии. Я им предлагал веб сервис.

    Также я успешно её использую сейчас при переводе 2.5 на 3.1. Проверяю и корректирую данные через COM обработками.

    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти

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

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

    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти

    В этом запросе внутреннее соединение. Его нужно оставить.
    Я имею ввиду конструкцию (РегистрНакопления.РасчетыСКлиентами.Обороты(, , Регистратор,
    //****
    Регистратор В (Выбрать Таб.Регистратор ИЗ РегистрСведений.РеестрДокументов КАК Таб ГДЕ (Таб .ДополнительнаяЗапись = ЛОЖЬ))
    //*****
    ) )

    Внизу условие И (РД.ДополнительнаяЗапись = ЛОЖЬ)» — надо убрать.
    Тогда левая таблица будет с меньшим количеством строк (к ней уже применено это условие), а правая будет соединяться по хорошо проиндексированному полю с большой селективностью.

    Это в теории ( :-). Как я понимаю рекомендации 1с.). На практике может оптимизатор запутаться именно в виртуальной таблице и зависнуть. :-)))

    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти

    Не видно использования функции ОбщегоНазначения.

    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти

    (35)Конструктив будет? Может поделитесь каким-нибудь полезным life hack?

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

    • Скопировать ссылку
    • Перейти

    (36) хз, что добавить. обычная средняя статья для новичков.
    по поводу использования НаКлиентеНаСервереБезКонтекста для избежания дублирования кода не соглашусь.
    в типовых там еще передают всю форму, что нивелирует суффикс «БезКонтекста» по сути.
    читайте макконнелла «совершенный код», применяйте и будет вам счастье.
    советую еще почитать «Элегантные объекты. Java Edition», мне зашло.

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

    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти
    • Скопировать ссылку
    • Перейти

    Если честно, всё это уже обсуждалось столько раз, что аж на зубах скрипит. Уже больше 50 точно. Уже просто такой баян, что сил нет. Да, аккуратно и грамотно оформлено, но и только. Всё свалено в кучу, ошибки разнородные, разноплановые, просто собраны вместе по малопонятному принципу. И как всегда, оживлённые баталии в комментах, «а как же всё-таки правильно». Честное слово, надоело. Кроме как очередной раз плюсики собрать, ни малейшей пользы — пионэры как косячили, так и косячат, БСП как писали хрень, не лезущую в их же стандарты, так и пишут. Осточертело уже.

    А если кому нужен конструктив, то вот, из личного опыта. Никогда не называйте переменные, реквизиты и параметры зарезервированными именами. Г-внокодеров из 1С-центральной поубивать надо за переменные с именем «запрос» или «строка». Потому что существует ряд случаев, когда это аукается большими и неочевидными проблемами. И я сейчас даже не про сом-соединение или работу в «Выполнить» или вызываемое из функций СКД. Я про глюки платформы. В своё время один реквизит формы, названный «ТабличныйДокумент», стоил нашей фирме проваленных сроков по проекту, сорванного контракта, кучи скандалов, судебного преследования и попадания лично РП на 5 лямов. Потому что 8.3.7 начинала ооочень своеобразно себя вести в работе с такой формой. Она спонтанно её переобновляла, кэшировала фрагментами какие-то данные, порой не отрабатывала расположение элементов или запоминание настроек, а в тонком клиенте падала.
    Багов в платформе много, подобное ещё может всплыть где угодно. Желающие встать на грабли могут продолжать писать «Структура = Новый Структура».

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