Примеры использования функции ПОИСКПОЗ в Excel
Ниже приведены два примера использования функции ПОИСКПОЗ для поиска нужных значений:
1. Использование функции ПОИСКПОЗ для текстовых значений
Иногда данные, с которыми вы работаете, не являются числовыми, а вместо этого включают текстовые значения. Например, вы можете искать одежду разных размеров, например рубашки, от очень маленького до очень большого. Вы можете использовать функцию ПОИСКПОЗ, чтобы найти эти значения. Сначала введите свои данные в электронную таблицу Excel:
АБ1Очень маленький342Маленький123Средний144Большой565Очень большой7Далее вы можете ввести значение, которое хотите найти, в пустую ячейку. Например, вы можете использовать функцию ПОИСКПОЗ, чтобы найти слово «маленький» в ваших данных. Для этого вы можете ввести слово «Маленький» в пустую ячейку, такую как ячейка C1:
АБС1Очень маленький34Маленький2Маленький123Средний144Большой565Очень большой7После того, как вы знаете, какое условие поиска вы хотите сопоставить, вы можете ввести функцию ПОИСКПОЗ или XПОИСКПОЗ в пустую ячейку и нажать «ВВОД» на клавиатуре. Например, вы можете поместить функцию в любую ячейку от C2 до C5 и нажать «ENTER», чтобы позволить Excel найти значение:
АБС1Очень маленький34Маленький2Маленький12=ПОИСКПОЗ(C1, A1:A5, 0)3Medium14=XMATCH(C1, A1:A5, 0)4Большой56=ПОИСКПОЗ(Маленький, A1:A5, 0)5Extra large7=XMATCH(Small, A1:A5, 0) После нажатия «ENTER» Excel возвращает значение «2», что означает, что искомое значение находится во второй ячейке массива, который вы искали. Здесь ячейка A2. Наконец, после того как Excel найдет совпадение в вашем наборе данных, вы можете нажать «CTRL+S», чтобы сохранить книгу Excel для дальнейшего использования.
2. Использование функции ПОИСКПОЗ для неточных значений
Вы можете использовать функцию ПОИСКПОЗ, чтобы найти значения в данных, которые близки к нужному значению. Для этих поисков вы можете использовать другое значение, чтобы найти следующее ближайшее значение в вашем наборе данных: 1 или -1. Во-первых, вы можете ввести свои данные в электронную таблицу Excel. Например, вы можете ввести данные {1, 3, 4, 5} в электронную таблицу как:
А11233445После того, как вы введете свои данные, вы можете использовать пустую ячейку, чтобы ввести значение, которое вы хотите найти. Например, если вы хотите найти значение «2» или меньшее значение, вы можете ввести «2» в пустую ячейку электронной таблицы, например в ячейку B1. После ввода значения вы можете ввести функцию ПОИСКПОЗ или ПОИСКПОЗ, чтобы найти следующее ближайшее значение. Чтобы найти значение, которое соответствует или является ближайшим ближайшим значением, которое меньше «2», вы можете ввести функции как:
=ПОИСКПОЗ(2, A1:A4, 1)
=XПОИСКПОЗ(2, A1:A4, -1)
Обратите внимание, что значение последнего из двух функций отличается, потому что они используют разные правила. После того, как вы введете функцию, вы можете нажать «ВВОД», чтобы Excel нашел совпадение
Здесь Excel выводит число «1», означающее, что следующее значение, близкое к «2», но меньшее, чем оно находится в первой позиции выбранного вами массива, ячейке A1, которая имеет значение 1. После того, как Excel найдет match, вы можете сохранить книгу, перейдя в верхний левый угол в разделе «Файл» и нажав «Сохранить», чтобы сохранить свою работу.
Обратите внимание, что ни один из продуктов или компаний, упомянутых в этой статье, не связан с компанией Indeed
Расширенный поиск
Предположим, что требуется найти все значения в диапазоне от 3000 до 3999. В этом случае в строке поиска следует набрать 3???. Подстановочный знак «?» заменяет собой любой другой.
Анализируя результаты произведённого поиска, можно отметить, что, наряду с правильными 9 результатами, программа также выдала неожиданные, подчёркнутые красным. Они связаны с наличием в ячейке или формуле цифры 3.
Можно удовольствоваться большинством полученных результатов, игнорируя неправильные. Но функция поиска в эксель 2010 способна работать гораздо точнее. Для этого предназначен инструмент «Параметры» в диалоговом окне.
Щёлкнув «Параметры», пользователь получает возможность осуществлять расширенный поиск
Прежде всего, обратим внимание на пункт «Область поиска», в котором по умолчанию выставлено значение «Формулы»
Это означает, что поиск производился, в том числе и в тех ячейках, где находится не значение, а формула. Наличие в них цифры 3 дало три неправильных результата. Если в качестве области поиска выбрать «Значения», то будет производиться только поиск данных и неправильные результаты, связанные с ячейками формул, исчезнут.
Для того чтобы избавиться от единственного оставшегося неправильного результата на первой строчке, в окне расширенного поиска нужно выбрать пункт «Ячейка целиком». После этого результат поиска становимся точным на 100%.
Такой результат можно было бы обеспечить, сразу выбрав пункт «Ячейка целиком» (даже оставив в «Области поиска» значение «Формулы»).
Теперь обратимся к пункту «Искать».
Если вместо установленного по умолчанию «На листе» выбрать значение «В книге», то нет необходимости находиться на листе искомых ячеек. На скриншоте видно, что пользователь инициировал поиск, находясь на пустом листе 2.
Следующий пункт окна расширенного поиска – «Просматривать», имеющий два значения. По умолчанию установлено «по строкам», что означает последовательность сканирования ячеек по строкам. Выбор другого значения – «по столбцам», поменяет только направление поиска и последовательность выдачи результатов.
При поиске в документах Microsoft Excel, можно использовать и другой подстановочный знак – «*». Если рассмотренный «?» означал любой символ, то «*» заменяет собой не один, а любое количество символов. Ниже представлен скриншот поиска по слову Louisiana.
Иногда при поиске необходимо учитывать регистр символов. Если слово louisiana будет написано с маленькой буквы, то результаты поиска не изменятся. Но если в окне расширенного поиска выбрать «Учитывать регистр», то поиск окажется безуспешным. Программа станет считать слова Louisiana и louisiana разными, и, естественно, не найдёт первое из них.
Использование функции ПОИСКПОЗ для поиска позиции ячейки с числовым значением.
Нажимаем ОК.
Функция вернула значение 8. Это значит, что число 55 находиться в восьмой по счету ячейки.
Тип_сопоставления: 1. Функция ПОИСКПОЗ в Excel.
Нажимаем ОК.
Функция вернула значение 6. Это позиция числа 32. Поскольку в анализируемом диапазоне нет числа 40, ПОИСКПОЗ вернула позицию числа, которое меньше числа 40 (Искомое_значение). При этом оно наибольшее среди чисел, которые меньше числа 40.
Нажимаем ОК.
Функция вернула значение 6. Это позиция числа 45. Поскольку в анализируемом диапазоне нет числа 40, ПОИСКПОЗ вернула позицию числа, которое больше числа 40 (Искомое_значение). При этом оно наименьшее среди чисел, которые больше числа 40.
Совпадение в столбцах Excel
Сегодня расскажу как искать совпадение в столбцах Excel. Разберем все тонкости на примерах.
Задача 1: Есть 6 текстов в 6 ячейках. Необходимо узнать, какие из них уникальные, а какие повторяются.
Использовать будем Условное форматирование.
- Выбираем ячейки, которые необходимо сравнить;
- Во вкладке Главная переходим «Условное форматирование -> Правила выделения ячеек -> Повторяющиеся значения»;
Выскакивает новое окно и в таблице начинают подсвечиваться ячейки, которые повторяются.
В этом окне вы можете выбрать две настройки: подсвечивать Повторяющиеся или Уникальные ячейки, а также какую подсветку при этом использовать — граница, текст, цвет текста, фон или своя уникальная.
Рассмотрим еще пример. Необходимо сравнить два столбца в Excel на совпадения. Есть таблица, в которой также есть совпадения, но уже числовые.
Выбираем таблицу и заходим в Повторяющиеся значения. Все совпадения будут подсвечены.
Можно, например, найти совпадения в одном столбце. Для этого достаточно перед применением опции выделить только его.
Ну и как я говорил выше, из выпадающего списка вы можете выделять не только повторяющиеся ячейки, но и уникальные.
Искать таким образом можно буквы, слова, символы, тексты и т.д.
Функция ПОИСК в формуле массива
Примеры выше, где буквы перечислены явно в строковом массиве, занимает довольно много места. Буквы при этом идут подряд, что наводит на мысль, что их можно как-то иначе выразить как диапазон.
И действительно, это возможно с помощью комбинации с функциями СТРОКА и ПОИСК:
{=СЧЁТ(ПОИСК(СИМВОЛ(СТРОКА(65:90));A1))>0}
Отличие этой формулы массива от предыдущих — ее нужно вводить без фигурных скобок, они появятся при вводе формулы сочетанием Ctrl+Shift+Enter (вместо обычного Enter). В формуле выше, где явно прописаны все буквы, фигурные скобки вводятся вручную — это явное указание строкового массива.
Что происходит в этой формуле?
- Функция СТРОКА с численным аргументом «65:90» возвращает массив чисел с 65 по 90 включительно. Как раз в этом диапазоне в таблице ASCII находятся все символы латиницы;
- Функция СИМВОЛ возвращает для каждого числового значения в этом массиве его символ, таким образом создавая массив латинских символов;
- Функция ПОИСК производит поиск каждого из этих символов в строке и возвращает либо число, либо ошибку, таким образом создавая массив чисел и ошибок
- Функция СЧЁТ считает числовые значения в полученном массиве. Если результат больше нуля, значит, хотя бы один символ латиницы был найден. Если нет (все поиски вернули ошибку), значит, не был
Аналогичная формула для кириллицы:
{=СЧЁТ(ПОИСК(СИМВОЛ(СТРОКА(192:223));A1))>0}
Подробнее о поиске и извлечении кириллицы и латиницы в Excel можно почитать тут:
Найти латиницу или кириллицу в тексте
Есть еще множество комбинаций функции ПОИСК с другими функциями Excel, смотрите разделы: Функция ИЛИ Функция И Функция ЗНАЧЕН Удалить первое слово в ячейке Excel
Несколько условий ЕСЛИ в Excel
Функция ЕСЛИ в Excel позволяет оценивать ситуацию с двух точек зрения, например, значение больше 0 или меньше, и в зависимости от ответа на этот вопрос, произведи дальнейшие расчеты по той или иной формуле. Однако, не редки ситуации, когда вам приходится работать более, чем с двумя условиями. В сегодняшней статье мы рассмотрим примеры создания формул в Excel с несколькими условиями ЕСЛИ.
Принцип создания формул с несколькими условиями ЕСЛИ заключается в том, что в одном из аргументов формулы (значение_если_ИСТИНА или значение_если_ЛОЖЬ) находится еще одна формула ЕСЛИ.
Вам также могут быть интересны следующие статьи
- Логические функции в Excel
- Изучаем формулу ЕСЛИ — 5 приемов Excel, о которых вы не знали
- Функции СЛЧИС и СЛУЧМЕЖДУ Excel на примере создания имитации игральной кости
- Функция СЖПРОБЕЛЫ в Excel с примерами использования
- Четыре способа использования ВПР с несколькими условиями
- Что такое стандартное отклонение — использование функции СТАНДОТКЛОН для расчета стандартного отклонения в Excel
- Как расчитать дисперсию в Excel с помощью функции ДИСП.В
- Функция СУММПРОИЗВ — как использовать формулу СУММПРОИЗВ в Excel
- Функции НАИБОЛЬШИЙ и НАИМЕНЬШИЙ Excel
- Функция СУММЕСЛИМН в Excel
98 комментариев
Нужно вернуть определенное значение из ячейки и посчитать балл, т.е. например в ячейке D3 может быть значение А, Б, В, Г, надо в ячейку D4 вернуть значение в зависимости от буквы, например А=1, Б=2, В=3 и так далее. Как сделать? Можно ли через формулу ЕСЛИ?
Статья хорошая, спасибо. Но.. вначале статьи планы ставят из минимального расчета 500$, а все дальнейшие расчеты исходят из 400$. Как бы надо стараться следовать тем планам, что ставите.
Данный метод хорош, если у нас немного критериев (2-3), но когда их 10, то в такой формуле потом трудно разобратся «что и откуда». В таком случае можно (и нужно) обойтись без ЕСЛИ. Для этого создаем маленькую табличку с нашими критериями: в первой строке по возрастанию заполняем критерии (в приведенном примере это будут 0, 500, 750, 1000); во второй строчке под каждым критерием заполняем соответствующий процент (7, 10, 12,5, 16). Допустим, в диапазоне A1:D1 у нас заполнены критерии, а в диапазоне A2:D2 — соответствующие проценты. В ячейке А5 имеем цифру продаж; для рассчета комиссии используем следующую формулу: =A5*ИНДЕКС($A$2:$D$2;ПОИСКПОЗ(A5;$A$1:$D$1;1)). ПОИСКПОЗ ищет расположение критерия, который меньше продаж, но наибольший в списке, а ИНДЕКС по полученному номеру выдает нам необходимый процент.
Поиск совпадений в Excel
Сегодня речь пойдет об «Условном форматировании». Оно предназначено для выделения ячеек таблицы, имеющих общие черты. Это может быть идентичный шрифт, значения, фон и т.д. Данная операция предусматривает различные конфигурации: строгость проверки, содержание совпадений, их тождественность и вариативность.
- Начнем с запуска программы Microsoft Excel, содержащую необходимую нам таблицу. Следом выделяем диапазон ячеек, нуждающихся в обработке. Речь идет об общности столбцов и ячеек, формирующих часть таблицы, либо несколько несвязанных между собой областей таблицы.
- Далее нам потребуется пройти следующий путь:
- меню Excel, работаем во вкладке «ГЛАBНАЯ».
- Находим в группе команд «CТИЛИ».
- В открывшемся списке отмечаем «УСЛOВНОЕ ФОРМАТИРОВАНИЕ». Нас интересует пункт «ПОВТОРЯЮЩИЕСЯ ЗНАЧЕНИЯ».
- В разделе «ПРАВИЛА BЫДЕЛЕНИЯ ЯЧЕЕК». При необходимости выбираем в списке «ПOВТОРЯЮЩИЕСЯ», что позволит отметить все ячейки в интересующей нас области, имеющие одно и более повторений.
- Выбрав вариант «УНИКАЛЬНЫЕ», мы увидим ячейки с уникальным значением или содержанием.
- Программа имеет широкую линейку возможностей, в частности: можно выбрать подсветку ячеек, попавших в отбор, предусмотрен вариант выбора фона заливки (программа предоставляет 6 цветовых решений), вариации шрифтов и табличных рамок. Возможен выбор «ПОЛЬЗОВАТЕЛЬСКОГО ФОРМАТА», который позволяет создать свой вариант оформления ячеек. Для отмены выборки совпадающих ячеек нажмите ОК.
Использование функции «РАВНО»
Если нужные для выделения ячейки имеют совершенно конкретное значение, воспользуйтесь пунктом «РАВНО» в списке «УСЛОВНОЕ ФОРМАТИРОВАНИЕ», находящееся в разделе «ПРАВИЛА ВЫДЕЛЕНИЯ ЯЧЕЕК». В открывшемся диалоговом окне отметьте интересующие вас ячейки, требующие выявления дубликатов, при этом их адрес появится в соседнем диалоговом окне. Овладев этими нехитрыми навыками, вы сможете значительно сократить время на обработку табличных данных и группировку общих значений.
Предназначение и синтаксис метода Range.Find
Метод Find объекта Range предназначен для поиска ячейки и сведений о ней в заданном диапазоне по ее значению, формуле и примечанию. Чаще всего этот метод используется для поиска в таблице ячейки по слову, части слова или фразе, входящей в ее значение.
Синтаксис метода Range.Find
| 1 | Expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat) |
Expression – это переменная или выражение, возвращающее объект Range, в котором будет осуществляться поиск.
В скобках перечислены параметры метода, среди них только What является обязательным.
Метод Range.Find возвращает объект Range, представляющий из себя первую ячейку, в которой найдена поисковая фраза (параметр What). Если совпадение не найдено, возвращается значение Nothing.
Параметры метода Range.Find
| Наименование | Описание |
| Обязательный параметр | |
| What | Данные для поиска, которые могут быть представлены строкой или другим типом данных Excel. Тип данных параметра – Variant. |
| Необязательные параметры | |
| After | Ячейка, после которой следует начать поиск. |
| LookIn | Уточняет область поиска. Список констант xlFindLookIn:
|
| LookAt | Поиск частичного или полного совпадения. Список констант xlLookAt:
|
| SearchOrder | Определяет способ поиска. Список констант xlSearchOrder:
|
| SearchDirection | Определяет направление поиска. Список констант xlSearchDirection:
|
| MatchCase | Определяет учет регистра:
|
| MatchByte | Условия поиска при использовании двухбайтовых кодировок:
|
| SearchFormat | Формат поиска – используется вместе со свойством Application.FindFormat. |
* Примечания имеют две константы с одним значением. Проверяется очень просто: MsgBox xlComments и MsgBox xlNotes. ** Тесты показали неработоспособность метода Range.Find с константой xlFormulas в моей версии VBA Excel.
В справке Microsoft тип данных всех параметров, кроме SearchDirection, указан как Variant.
Поиск и подстановка по нескольким условиям
Постановка задачи
=ВЫБОР(ПОИСКПОЗ(B9;B3:B7;-1);C3;C4;C5;C6;C7)match_typeФункция и функцию ГПР.
или ссылку наможет содержать подстановочные Необязательный аргумент. Число -1,: здесь вместо использования заполненную нулями (фактически сразу целые столбцы условиям. Но если сложный расчет в из отдела продаж:Формулы Известна цена в вычислениях или отображатьЧтобы придать больше гибкости(тип_сопоставления), чтобы выполнитьMATCH Функция ГПР использует
Способ 1. Дополнительный столбец с ключом поиска
ячейку, должен быть знаки: звездочку ( 0 или 1.Serge_007 функции СТОЛБЕЦ, мы значениями ЛОЖЬ, но (т.е. вместо A2:A161 в нашем списке Excel. Есть, однако,Что же делать еслив группе столбце B, но
как результаты. Существует функции поиск точного совпадения.(ПОИСКПОЗ) возвращает позицию те же аргументы, крайним левым столбцом* Аргумент,
перемножаем каждую такую через какое-то время вводить A:A и нет повторяющихся товаров одна проблема: эта нас интересует FordРешения неизвестно, сколько строк несколько способов поиска
VLOOKUP Если требуется найти значения в массиве но выполняет поиск
в диапазоне.) и вопросительный знактип_сопоставленияБольшое спасибо, очень временную таблицу на мы будем умножать т.д.) Многим непривычны внутри одного месяца, функция умеет искать из маркетингового отдела?выберите команду данных возвратит сервер,
Способ 2. Функция СУММЕСЛИМН
значений в списке(ВПР), Вы можете точное совпадение текстовой или ошибку в строках вместоТретий аргумент — это (указывает, каким образом помогло! введённый вручную номер эти значения, и формулы массива в то она просто данные только по Кроме того, мыПодстановка а первый столбец данных и отображения использовать строки, то в#N/A
столбцов. столбец в диапазоне? в Microsoft ExcelГость столбца.
Excel автоматически преобразует принципе (тогда вам выведет значение цены совпадению одного параметра. хотим использовать только. не отсортирован в результатов.
Способ 3. Формула массива
MATCH искомом значении допускается(#Н/Д), если оноЕсли вы не хотите поиска ячеек, содержащий). Звездочка соответствует любойискомое_значение: Помогите!!!!!!!!!!!!!! плиз!!!!!!!!!!!!!! ужеПример 3. Третий пример их на ноль). сюда). для заданного товара А если у функцию ПОИСПОЗ, неЕсли команда
- алфавитном порядке.Поиск значений в списке(ПОИСКПОЗ) для поиска
- использовать символы подстановки. не найдено. Массив ограничиваться поиском в
- значение, которое нужно последовательности знаков, вопросительныйсопоставляется со значениями все перепробовала не – это также Нули там, гдеФункция ИНДЕКС предназначена для
и месяца: нас их несколько?
прибегая к формуламПодстановкаC1 по вертикали по номера столбца, аВ следующем примере, чтобы может быть, как крайнем левом столбце, найти. знак — любому одиночному в аргументе получается. Нужно чтобы формула массива. значение в таблице создания массивов значенийПлюсыПредположим, что у нас с комбинациями другихнедоступна, необходимо загрузить — это левая верхняя точному совпадению не жестко вписывать найти положение месяца сортированный, так и
можно использовать сочетаниеХотя четвертый аргумент не знаку. Если нужнопросматриваемый_массив во втором ПОИСКПОЗ
Все точно так же, не равно искомому в Excel и: Не нужен дополнительный есть база данных функций ИНДЕКС и надстройка мастера подстановок. ячейка диапазона (такжеПоиск значений в списке его значение в в списке, мы не сортированный. Функция функций ИНДЕКС и является обязательным, большинство
planetaexcel.ru>
Поиск неточного совпадения с помощью ВПР
Благодаря этой опции в работе ВПР, мы можем избежать сложных формул, чтобы найти нужный результат.
В массиве В5:С12 указаны процентные ставки по кредитам в зависимости от суммы займа. В ячейке В2 Указываем сумму кредита и хотим получить в С2 ставку для такой сделки. Задача сложна тем, что сумма может быть любой и вряд ли будет совпадать с указанными в массиве, поиск по точному совпадению не подходит:
Тогда запишем формулу нестрогого поиска: =ВПР(B2;B5:C12;2;ИСТИНА). Теперь из всех представленных в столбце В данных программа будет искать ближайшее меньшее. То есть, для суммы 8 000 будет отобрано значение 5000 и выведен соответствующий процент.
Нестрогий поиск ВПР в Excel
Для корректной работы функции нужно отсортировать первый столбец таблицы по возрастанию. Иначе, она может дать ошибочный результат.
Функция ГПР имеет такой же синтаксис, как и ВПР, но ищет результат не в столбцах, а в строках. То есть, просматривает таблицы не сверху вниз, а слева направо и выводит заданный номер строки, а не столбца.
Функция ПРОСМОТР работает аналогично ВПР, но имеет другой синтаксис. Я использую её, когда таблица данных содержит несколько десятков столбцов и для использования ВПР нужно дополнительно просчитывать номер выводимой колонки. В таких случаях функция ПРОСМОТР облегчает задачу. И так, синтаксис: =ПРОСМОТР(Искомое_значение; Массив_для_поиска; Массив_для_отображения):
- Искомое значение – данные или ссылка на данные, которые нужно искать;
- Массив для поиска – одна строка или столбец, в котором ищем аналогичное значение. Данный массив обязательно сортируем по возрастанию;
- Массив для отображения – диапазон, содержащий данные для выведения результатов. Естественно, он должен одного размера с массивом для поиска.
При такой записи вы даёте не относительную ссылку массива результатов. А прямо на него указываете, т.е. не нужно предварительно просчитывать номер выводимого столбца. Используем функцию ПРОСМОТР в первом примере для функции ВПР (основные средства, инвентарные номера): =ПРОСМОТР(B2;B5:B10;D5:D10). Задача успешно решена!
Функция «ПРОСМОТР» в Microsoft Excel
Еще один способ поиска данных – комбинирование функций ПОИСКПОЗ и ИНДЕКС.
Первая из них, служит для поиска значения в массиве и получения его порядкового номера: ПОИСКПОЗ(Искомое_значение; Просматриваемый_массив; ). Аргументы функции:
- Искомое значение – обязательный аргумент
- Просматриваемый массив – одна строка или столбец, в котором ищем совпадение. Обязательный аргумент
- Тип сопоставления – укажите «0» для поиска точного совпадения, «1» — ближайшее меньшее, «-1» — ближайшее большее. Поскольку функция проводит поиск с начала списка в конец, при поиске ближайшего меньшего – отсортируйте столбец поиска по убыванию. А при поиске большего – сортируйте его по возрастанию.
Позиция необходимого значения найдена, теперь можно вывести его на экран с помощью функции ИНДЕКС(Массив; Номер_строки; ):
- Массив – аргумент указывает из какого массива ячеек нужно выбрать значение
- Номер строки – указываете порядковый номер строки (начиная с первой ячейки массива), которую нужно вывести. Здесь можно записать значение вручную, либо использовать результат вычисления другой функции. Например, ПОИСКПОЗ.
- Номер столбца – необязательный аргумент, указывается, если массив состоит из нескольких столбцов. Если аргумент упущен, формула использует первый столбец таблицы.
Теперь скомбинируем эти функции, чтобы получить результат:
Функции ПОИСКПОЗ и ИНДЕКС в Эксель
Вот такие способы поиска и выведения на экран данных существуют в Excel. Далее, Вы можете использовать их в расчетах, использовать в презентации, выполнять с ними операции, указывать в качестве аргументов других функций и др.
Чувствуете, как растут и крепчают Ваши знания и умения? Тогда не останавливайтесь, продолжайте читать ! В следующем посте мы будем рассматривать расчеты кредита в Эксель: будет сложно и интересно!
Как сравнить две книги Excel?
- Выберите инструмент «ФОРМУЛЫ»-«Определенные имена»-«Присвоить имя».
- В появившемся окне в поле «Имя:» введите значение – Таблица_1.
- Левой клавишей мышки сделайте щелчок по полю ввода «Диапазон:» и выделите диапазон: A2:A15. И нажмите ОК.
Умение сравнивать два массива данных в Excel часто пригождается для людей, обрабатывающих большие объемы данных и работающих с огромными таблицами. Например, сравнение может быть использовано в , корректности занесения данных или внесение данных в таблицу в срок. В статье ниже описаны несколько приемов сравнения двух столбцов с данными в Excel.
Сравнение с диапазоном значений в Excel-e
Подскажите, плз, как в Excele организовать сравнение какой-либо величины с диапазоном других значений: а) через формулу (должна быть какая-то короткая запись); б) то же самое, но через VBA.
А что ты хочешь на выходе получить?
при совпадении искомого значения с каким-либо значением из диапазона поиска — должно быть совершено некое действие — для простоты — присвоение третьей переменной (ячейке и др.) некоего значения.
Причем диапазон значений не последовательный. т.е. он может выглядеть например так: 1,3,126,90,5,2,87. и т.д.
Тебе формула массива нужна. Допустим, хочешь диапазон A1:A5 сравнить с ячейкой B1. 1. Выделяешь диапазон, в который поместить результат (должен быть такого же размера, как исходный диапазон). 2. В строке формул вбиваешь формулу =ЕСЛИ(A1:A5=B1;1;0) 3. Жмешь Ctrl + Shift + Enter З.Ы. Так получаешь результат сравнения с каждой ячейкой диапазона. Или ты хочешь, чтобы при сопадении хотя бы с одним из значений что-то делалось?
да — надо именно, чтоб при совпадении хотя бы одного значения. ЗЫ: а ctrl+Shift+Enter — это к чему?
С помощью Ctrl+Shift+Enter вводятся формулы массива. Имеет смысл, когда нужно проделать однотипную операцию с элементами массивов одинаковой размерности, или когда хочешь на выходе получить массив. Могу предложить вариант индикатора совпадения. Вбей в любую ячейку =СУММ(ЕСЛИ(A1:A5=B1;1;0 и нажми Ctrl+Shift+Enter Получишь количество совпадений. Был в загашнике другой вариант, но сейчас подзабылось.
комбинация ctrl+Shift+Enter должна обрамить твою формулу втакие вот <> скобочки т.е. добжно выглядеть как <=если(. )> почитай про формулы массива в справке
Спасибо большое, парни! Встретимся на просторах форума где-нибудь — за-плюсую насмерть!
А, вспомнил другой вариант — пользовать функцию ПОИСКПОЗ для поиска и ЕОШИБКА для анализа результата.
А что-то «поискпоз» у меня пермаментно ошибку выдает?!
Он ошибку выдает, если значение не найдено. Именно для этого рекомендую юзать ЕОШИБКА. Примерно так: =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;A1:A5;0;0;1)
прикол в том, что там это значение есть 100% — я для проверки делал. Ну да ладно — разберусь! =) Спасибо за помощь!
В экселе можно каким-либо образом без макросов организовать столбец с уникальными значениями?
с уникальными значениями?
допустим прайс периодически пополняется вручную. Нужно чтобы все позиции прайса были уникальными по колонке «Наименование товара»
Не совсем понял условия. Если тебе нужно из одного столбца выделить уникальные значения, то это делается с помощью расширенного фильтра. Просто копируешь столбец, Данные-Фильтр-Расширенный фильтр. Указываешь куда скопировать, ставишь галочку «только уникальные».
есть столбец, состоящий из N уникальных значений. Нужно чтобы при добавлении нового значения, если оно совпадает с одним из предыдущих, выводилось предупреждение или выделялись цветом одинаковые позиции или просто не довало ввести, вобщем механизм контроля уникальности значений в столбце.
Без макроса не знаю способа.
да.. здесь только макросить надо. пишешь макрос на событие листа Onchange — на каждое изменение будет проводится проверка и там ты сможешь вбить любое предупреждение, какое пожелаешь!
Поиск ТЕКСТовых значений в Excel. Бесплатные примеры и статьи.
Выделение ячеек c ТЕКСТом с применением Условного форматирования в MS EXCEL
Выделяем ячейки, содержащие искомый текст. Рассмотрим разные варианты: выделение ячеек, содержащих значения в точности совпадающих с искомым текстом; выделение ячеек, которые содержат искомый текст в начале, в конце или середине строки. Также научимся выделять ячейку, в случае если ее значение совпадает с одним из нескольких заданных значений.
Есть ли слово в списке MS EXCEL
Найдем слово в диапазоне ячеек, удовлетворяющее критерию: точное совпадение с критерием, совпадение с учетом регистра, совпадение лишь части символов из слова и т.д.
Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть1. Обычный поиск
Найдем текстовые значения, удовлетворяющие заданному пользователем критерию. Поиск будем осуществлять в диапазоне с повторяющимися значениями. При наличии повторов, можно ожидать, что критерию будет соответствовать несколько значений. Для их вывода в отдельный диапазон удобно использовать формулы массива.
Поиск позиции ТЕКСТа в MS EXCEL с выводом значения из соседнего столбца
Для нахождения позиции значения в столбце, с последующим выводом соответствующего значения из соседнего столбца в EXCEL, существует специальная функция ВПР() , но для ее решения можно использовать также и другие функции. Рассмотрим задачу в случае текстовых значений.
Поиск позиции ТЕКСТа с учетом РЕгиСТра в MS EXCEL и выводом значения из соседнего столбца
Здесь развиваются идеи статьи Поиск позиции ТЕКСТового значения с выводом соответствующего значения из соседнего столбца. Для нахождения позиции значения с учетом РЕгиСТра, с последующим выводом соответствующего значения из соседнего столбца, функция ВПР() применена быть не может, т.к. она не чувствительна к регистру. Задача может быть решена с помощи функций БИЗВЛЕЧЬ() , НАЙТИ() или СОВПАД() .
Поиск в MS EXCEL текстового значения первого или последнего по алфавиту
Найдем текстовое значение, которое при сортировке диапазона по возрастанию будет выведено первым, т.е. первое по алфавиту. Также найдем последнее значение по алфавиту.
Разнесение в MS EXCEL ТЕКСТовых значений и ЧИСЕЛ по разным спискам
Если исходный список, содержит и текст и числа, то с помощью формул массива можно в один список отобрать все текстовые значения, а в другой – числовые.
Поиск в MS EXCEL третьего (n-го) повтора в списках с ТЕКСТом
Рассмотрим поиск текстовых значений в списке с повторами. Задав в качестве критерия для поиска нужное текстовое значение и номер его повтора в списке, найдем номер строки, в которой содержится этот повтор, а также выделим его и другие повторы с помощью Условного форматирования.
О классификации задач Поиска в EXCEL
В этой статье изложены попытки классификации задач по поиску значений в MS EXCEL. Статья может быть интересна для продвинутых пользователей EXCEL.
Поиск нескольких значений с максимальной длиной строки в MS EXCEL
Найдем в исходном списке текстовые значения, которые имеют максимальную длину строки, и выведем их в отдельный диапазон.
Сравните два столбца на разных листах Excel с помощью ВПР
В реальной жизни столбцы, которые вам нужно сравнить, не всегда находятся на одном листе. В небольшом наборе данных вы можете попробовать обнаружить различия вручную, просмотрев два листа рядом.
Для поиска на другом листе или в книге с формулами необходимо использовать внешнюю ссылку. Лучше всего начать вводить формулу на основном листе, затем переключиться на другой рабочий лист и выбрать список с помощью мыши — соответствующая ссылка на диапазон будет добавлена в формулу автоматически.
Предполагая, что список 1 находится в столбце A на Лист1 и список 2 находится в столбце A на Лист2вы можете сравнить два столбца и найти совпадения, используя эту формулу:
=ЕСЛИНА(ВПР(A2, Лист2!$A$2:$A$9, 1, ЛОЖЬ), «»)
Для получения дополнительной информации см.: