Функция index (индекс) в excel. как использовать?

Что делать, если нужен поиск сразу по двум критериям

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

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

Таблица с ценами на модели разных цветов

И по традиции есть таблица с клиентами, которые эти модели забронировали.

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

Если идти по классическому пути ВПР, получится такая функция: fx=ВПР(A29;’каталог авто’!$A$29:$E$35;5;0). В таком виде ВПР найдёт первую совпавшую модель и подтянет её стоимость. Параметр цвета не будет учтён.

Соответственно, цены у всех Nissan Juke будут 1 850 000 рублей, у всех Subaru Forester — 3 190 000 рублей, у всех Toyota C-HR — 2 365 000 рублей.

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

Поэтому в этом варианте нужно искать стоимость авто сразу по двум критериям — модель и цвет. Для этого нужно изменить формулу вручную. В строке ссылок ставим курсор сразу после искомого значения.

Дописываем в формулу фразу ЕСЛИ(‘каталог авто’!$B$29:$B$35=B29, где:

• ‘каталог авто’!$B$29:$B$35 — закреплённый диапазон цвета автомобилей в таблице, откуда нужно перенести данные. Это весь столбец с ценами.
• B29 — искомое значение цвета автомобиля в таблице, куда мы переносим данные. Это первая ячейка в столбце с цветом — дополнительным параметром для поиска.

Итоговая функция такая: fx=ВПР(A29;ЕСЛИ(‘каталог авто’!$B$29:$B$35=B29;’каталог авто’!$A$29:$E$35);5;0). Теперь значения цен переносятся верно.

Так выглядит таблица, в которую ВПР переносит данные на основе двух совпадений

Как использовать ВПР в «Google Таблицах»? В них тоже есть функция Vlookup, но нет окна построителя формул. Поэтому придётся прописывать её вручную. Перечислите через точку с запятой все аргументы и не забудьте зафиксировать диапазон. Для фиксации поставьте перед каждым символом значок доллара. В готовой формуле это будет выглядеть так: =ВПР(A2;’Лист1′!$A$2:$C$5;3;0).

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

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

Поиск по одному критерию

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

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

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

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

1. Жмем клавишу Enter и видим готовый результат.

1. Если поменять значение в ячейке, ссылка на которую указана в начале формулы, то итоговое значение сменится.

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

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

Поиск по двум критериям

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

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

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

Теперь нужно указать два диапазона – столбцы с марками и датами. Для этого выделяем первый и после знака & выделяем второй столбец. В конце ставим 0 для поиска точного значения.

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

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

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

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

Поиск частичного совпадения в Эксель

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

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

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

• * — любое количество любых символов
• ? – один любой символ

Воспользуемся ими, чтобы сформировать нужный запрос в формуле. Искомое значение будет примерно таким: *значение*. То есть, любое количество символов до и после указанной строки. Если использовать такой подход, например, в ВПР, можно получить желаемое:

Смотрите на картинке, я ищу «pixel», и нахожу модель, у которой в названии есть это слово.

В приведенной формуле знак «&» — оператор конкатенации (объединения) строк. Больше про объединение строк — читайте здесь

Так же можно сделать, когда применяется поиск с помощью ПОИСКПОЗ и ИНДЕКС:

=ИНДЕКС( массив_для_вывода ; ПОИСКПОЗ(«*»& значение &»*»; массив_для_поиска ;0))

Результат аналогичен, всё работает, но есть и неожиданности. Если в любом из рассмотренных вариантов вообще не задать искомое значение:

Наша формула вернула не ошибку, а данные из первой строки таблицы. Это произошло, т.к. подстановочные символы командуют формуле искать любые символы. Не критично, но можно и убрать с помощью функции ЕСЛИ:

Смотрите на рисунке выше, нет искомого текста, нет и результата.

Кроме того, если совпадения не найдены, будет выведена ошибка #Н/Д. Как её обработать, я рассказывал в этой статье.

Метод 2: удаление повторений при помощи “умной таблицы”

Поиск значения в строке Excel

Связанные таблицы в Excel: подробная инструкция

Поиск ТЕКСТовых значений в Excel. Бесплатные примеры и статьи.

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

Сравнение двух таблиц в Excel на наличие несовпадений значений

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

Вид таблицы данных:

Для сравнения значений, находящихся в столбце B:B со значениями из столбца A:A используем следующую формулу массива (CTRL+SHIFT+ENTER):

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

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

Как видно, третьи элементы списков не совпадают.

5 вариантов использования функции ИНДЕКС (INDEX)

Бывает у вас такое: смотришь на человека и думаешь «что за @#$%)(*?» А потом при близком знакомстве оказывается, что он знает пять языков, прыгает с парашютом, имеет семеро детей и черный пояс в шахматах, да и, вообще, добрейшей души человек и умница?

Так и в Microsoft Excel: есть несколько похожих функций, про которых фраза «внешность обманчива» работает на 100%. Одна из наиболее многогранных и полезных — функция ИНДЕКС (INDEX) . Далеко не все пользователи Excel про нее знают, и еще меньше используют все её возможности. Давайте разберем варианты ее применения, ибо их аж целых пять.

Вариант 1. Извлечение данных из столбца по номеру ячейки

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

=ИНДЕКС( Диапазон_столбец ; Порядковый_номер_ячейки )

Этот вариант известен большинству продвинутых пользователей Excel. В таком виде функция ИНДЕКС часто используется в связке с функцией ПОИСКПОЗ (MATCH) , которая выдает номер искомого значения в диапазоне. Таким образом, эта пара заменяет легендарную ВПР (VLOOKUP) :

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

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

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

=ИНДЕКС( Диапазон ; Номер_строки ; Номер_столбца )

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

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

Вариант 3. Несколько таблиц

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

=ИНДЕКС( (Диапазон1;Диапазон2;Диапазон3) ; Номер_строки ; Номер_столбца ; Номер_диапазона )

Обратите особое внимание, что в этом случае первый аргумент – список диапазонов — заключается в скобки, а сами диапазоны перечисляются через точку с запятой

Вариант 4. Ссылка на столбец / строку

Если во втором варианте использования функции ИНДЕКС номер строки или столбца задать равным нулю (или просто не указать), то функция будет выдавать уже не значение, а ссылку на диапазон-столбец или диапазон-строку соответственно:

Обратите внимание, что поскольку ИНДЕКС выдает в этом варианте не конкретное значение ячейки, а ссылку на диапазон, то для подсчета потребуется заключить ее в дополнительную функцию, например СУММ (SUM) , СРЗНАЧ (AVERAGE) и т.п

Вариант 5. Ссылка на ячейку

Общеизвестно, что стандартная ссылка на любой диапазон ячеек в Excel выглядит как Начало-Двоеточие-Конец, например A2:B5. Хитрость в том, что если взять функцию ИНДЕКС в первом или втором варианте и подставить ее после двоеточия, то наша функция будет выдавать уже не значение, а адрес, и на выходе мы получим полноценную ссылку на диапазон от начальной ячейки до той, которую нашла ИНДЕКС:

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

Один из весьма распространенных на практике сценариев применения ИНДЕКС в таком варианте — это сочетание с функцией СЧЁТЗ (COUNTA) , чтобы получить автоматически растягивающиеся диапазоны для выпадающих списков, сводных таблиц и т.д.

Параметры поиска

ПОИСК БЛИЖАЙШЕГО ЗНАЧЕНИЯ В ДИАПАЗОНЕ EXCEL

Данная таблица все еще не совершенна. Ведь при анализе необходимо буквально знать все ее значения. Если введенное число в ячейку B1 формула не находит в таблице, тогда ворачивается ошибка – #ЗНАЧ! Совершенно было-бы чтоб формула при отсутствии в таблице начального числа сама подбирала наиблежайшее значение, которое содержит таблица. Чтоб сделать такую программку для анализа таблиц в ячейку F1 введите новейшую формулу:

Опосля что следует во всех других формулах поменять ссылку заместо B1 обязано быть F1! Так же необходимо поменять ссылку в условном форматировании. Изберите: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами»-«Поменять правило». И тут в параметрах укажите F1 заместо B1. Чтоб проверить работу программки, введите в ячейку B1 число которого нет в таблице, к примеру: 8000. Это приведет к оканчивающему результату:

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

Наша программка в Excel отыскала более близкое значение 4965 для начального – 5000. Таковая программка может понадобится для автоматического решения различных аналитических задач при бизнес-планировании, постановки целей, поиска оптимального решения и т.п. А приобретенные строчки и столбцы разрешают далее расширять вычислительные способности такового рода отчетов при помощи новейших формул Excel.

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

Функция ИНДЕКС в Excel

Функция ИНДЕКС возвращает содержимое ячейки, которая находится на пересечении заданных строки и столбца. Например, на рисунке ниже формула возвращает значение из диапазона A1:C4, которое находится на пересечении 3 строки и 2 столбца.

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

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

Например, следующая формула возвращает пятое значение из диапазона A1:A12 (вертикальный вектор):

Данная формула возвращает третье значение из диапазона A1:L1(горизонтальный вектор):

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

Функция ПОИСКПОЗ в Excel – синтаксис и использование

Функция ПОИСКПОЗ в Excel ищет заданное значение в диапазоне ячеек и возвращает относительное положение этого значения.

Синтаксис формулы ПОИСКПОЗ выглядит следующим образом:

Искомое_значение (обязательный аргумент) – значение, которое вы хотите найти. Это может быть числовое, текстовое или логическое значение, а также ссылка на ячейку.

Просматриваемый_массив (обязательный аргумент) – диапазон ячеек для поиска.

Тип_сопоставления (необязательный аргумент) – определяет тип соответствия. Это может быть одно из следующих значений: 1, 0, -1.

• 1 или отсутствует (по умолчанию) – находит наибольшее значение в массиве, которое меньше или равно значению поиска. Массив поиска должен быть отсортирован в порядке возрастания, от самого маленького до большого или от A до Z.
• 0 – находит первое значение в массиве, которое в точности равно значению поиска. Сортировка не требуется.
• -1 – находит наименьшее значение в массиве, которое больше или равно значению поиска. Массив поиска должен быть отсортирован в порядке убывания, от самого большого до самого маленького или от Z до A.

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

=ПОИСКПОЗ(E1; A2:A8; 0)

При желании вы можете поместить значение поиска в ячейку (E1 в данном примере) и сослаться на эту ячейку в формуле ПОИСКПОЗ Excel:

Функция ПОИСКПОЗ в Excel – Пример использования функции ПОИСКПОЗ в Excel

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

Особенности функции ПОИСКПОЗ Excel

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

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

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

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

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

Для удобства также приводим ссылку на оригинал (на английском языке).

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

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

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

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

Для удобства также приводим ссылку на оригинал (на английском языке).

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

Найти позицию последнего появления символа в строке в Excel

Несколько дней назад коллега столкнулся с этой проблемой.

У него был список URL-адресов, как показано ниже, и ему нужно было извлечь все символы после последней косой черты («/»).

Так, например, с https://example.com/archive/Январь ему пришлось извлечь «январь».

Это было бы очень просто, если бы в URL-адресах была только одна косая черта.

У него был огромный список из тысяч URL-адресов разной длины и с различным количеством косых черт.

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

В этом уроке я покажу вам два способа сделать это:

• Использование формулы Excel
• Использование пользовательской функции (созданной через VBA)

Получение последней позиции символа с помощью формулы Excel

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

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

Как работает эта формула?

Давайте разберем формулу и объясним, как работает каждая ее часть.

• ЗАМЕНА (A2; ”/”,“”) — Эта часть формулы заменяет косую черту пустой строкой. Так, например, если вы хотите найти вхождение любой строки, кроме косой черты, используйте ее здесь.
• LEN (A2) -LEN (ЗАМЕНА (A2; ”/”,“”)) — Эта часть сообщит вам, сколько косых черт в строке. Он просто вычитает длину строки без косой черты из длины строки с косой чертой.
• ПОДСТАВИТЬ (A2, ”/”, ”@”, LEN (A2) -LEN (ПОДСТАВИТЬ (A2, ”/”, ””))) — Эта часть формулы заменит последнюю косую черту на @. Идея состоит в том, чтобы сделать этого персонажа уникальным. Вы можете использовать любой персонаж, какой захотите. Просто убедитесь, что он уникален и его еще нет в строке.
• НАЙТИ («@», ПОДСТАВИТЬ (A2, «/», «@», LEN (A2) -LEN (ПОДСТАВИТЬ (A2, «/», »»))), 1) — Эта часть формулы даст вам позицию последней косой черты.
• LEN (A2) -FIND («@», ПОДСТАВИТЬ (A2, «/», «@», LEN (A2) -LEN (ПОДСТАВИТЬ (A2, «/», »»))), 1) — Эта часть формулы сообщает нам, сколько символов стоит после последней косой черты.
• = ПРАВО (A2; LEN (A2) -НАЙТИ («@», ПОДСТАВИТЬ (A2, «/», «@», LEN (A2) -LEN (ПОДСТАВИТЬ (A2, «/», »»))), 1 )) — Теперь это просто даст нам строку после последней косой черты.

Получение последней позиции символа с помощью пользовательской функции (VBA)

Вышеупомянутая формула прекрасна и работает как шарм, но она немного сложна.

Если вам удобно использовать VBA, вы можете использовать настраиваемую функцию (также называемую функцией, определяемой пользователем), созданную с помощью VBA. Это может упростить формулу и сэкономить время, если вам придется делать это часто.

Давайте воспользуемся тем же набором данных URL-адресов (как показано ниже):

Для этого случая я создал функцию с именем LastPosition, которая находит последнюю позицию указанного символа (в данном случае это косая черта).

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

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

Вот как это работает:

• LastPosition — наша настраиваемая функция — возвращает позицию косой черты. Эта функция принимает два аргумента — ссылку на ячейку с URL-адресом и символ, позицию которого нам нужно найти.
• Затем функция ВПРАВО дает нам все символы после косой черты.

Вот код VBA, создавший эту функцию:

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

Вот шаги, чтобы скопировать и вставить этот код в серверную часть VB:

Вот шаги, чтобы поместить этот код в редактор VB:

1. Перейдите на вкладку Разработчик.
2. Выберите вариант Visual Basic. Это откроет редактор VB в бэкэнде.
3. На панели Project Explorer в редакторе VB щелкните правой кнопкой мыши любой объект книги, в которую вы хотите вставить код. Если вы не видите Project Explorer, перейдите на вкладку View и нажмите Project Explorer.
4. Перейдите во вкладку «Вставить» и нажмите «Модуль». Это вставит объект модуля для вашей книги.
5. Скопируйте и вставьте код в окно модуля.

Теперь формула будет доступна на всех листах книги.

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

Вам также могут понравиться следующие руководства по Excel:

Как узнать, что ячейка Эксель содержит латиницу

Поиск и подстановка по нескольким условиям

Функции

Выбор похожих значений

16.11.2011, 00:22

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

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

Выбор значений таблицы по условиюВсем день добрый. Нужна помощь, в 1 столбце даны наименования, следующие 4 столбца — значения.

Выбор только числовых значенийЗдравствуйте, Уважаемые Форумчане. Помогите решить проблему. Нужно что бы из столбца А, таблицы.

16.11.2011, 01:57
2

Вложения

выбор.xls (19.5 Кб, 196 просмотров)

16.11.2011, 11:26

3

16.11.2011, 11:31

4

Вложения

похожие коды.zip (38.5 Кб, 109 просмотров)

16.11.2011, 12:48
5

Формулы массива вводятся нажатием Ctrl+Shift+Enter.

PS Maxbank, скажите, зачем выкладывать тысячи строк?! Почему не обойтись десятком? Даже десяток много. Хватило бы и ДВУХ строк.

Вложения

Maxbank.rar (45.7 Кб, 108 просмотров)

16.11.2011, 13:03

6

Спасибо. Извините, не подумал удалить лишние строки.

Добавлено через 5 минут Или я что-то не понял или все-таки нет нужного результата. В листе1 есть коды, например, 100-06319, 100-06319Р. Их надо вытянуть и вставить в Лист2 напротив кода 100-06319P, например через запятую или каждый совпадающий код в отдельном столбце.

18.11.2011, 02:12

7

Вложения

maxbank.xls (25.0 Кб, 204 просмотров)

21.11.2011, 19:19

8

25.01.2012, 21:49

9

25.01.2012, 21:59
10

25.01.2012, 22:09

11

Вложения

maxbank_1.xls (13.5 Кб, 40 просмотров)

25.01.2012, 22:24
12

25.01.2012, 23:03

13

25.01.2012, 23:12
14

25.01.2012, 23:26

15

25.01.2012, 23:29
16

Не знаю. Я вижу только тот файл, который Вы приложили. В нём будет работать. Давайте файл с ошибкой — я поправлю.

25.01.2012, 23:42

17

В файле есть код 203-05058 и 203-05058P. Формула ПОИСК(B798;B10:B1108) (где В798=203-05058) показывает, что совпадений 1. Хотя в тестовом файле maxbank_1.xls все работает правильно. Подсчитывает и результат дает 10.

Добавлено через 59 секунд Можно выложить файл со всеми строками?

Добавлено через 11 минут Отправил на мыло.

25.01.2012, 23:49
18

Нет. Только проблемные. Вес файла не более 100 Кб в xls.

ЗЫ Видимо это я косякнул. Правильная формула должна быть такая:

26.01.2012, 00:21

19

не работает в файле, котром надо.

Добавлено через 3 минуты СУММПРОИЗВ(ЕЧИСЛО(ПОИСК(B798&»*»;B10:B1108))*E10:E1104) И так тоже.

26.01.2012, 00:26
20

Пример функция ПСТР для разделения текста на части в Excel

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

Выбор уникальных и повторяющихся значений в Excel

Определения терминов

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

Вся таблица подстановки

Значение, которое будет найдено в первом столбце аргумента «инфо_таблица».

Просматриваемый_массив -или- Лукуп_вектор

Диапазон ячеек, которые содержат возможные значения подстановки.

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

3 (третий столбец в инфо_таблица)

Ресулт_аррай -или- Ресулт_вектор

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

Логическое значение (истина или ложь). Если указано значение истина или опущено, возвращается приближенное соответствие. Если задано значение FALSE, оно будет искать точное совпадение.

Это ссылка, на основе которой вы хотите основать смещение. Топ_целл должен ссылаться на ячейку или диапазон смежных ячеек. В противном случае функция СМЕЩ возвращает #VALUE! значение ошибки #ИМЯ?.

Число столбцов, находящегося слева или справа от которых должна указываться верхняя левая ячейка результата. Например, значение «5» в качестве аргумента Оффсет_кол указывает на то, что верхняя левая ячейка ссылки состоит из пяти столбцов справа от ссылки. Оффсет_кол может быть положительным (то есть справа от начальной ссылки) или отрицательным (то есть слева от начальной ссылки).

Как найти значение в массиве

Как найти значение в Excel