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 и нажмите кнопку Перейти.

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

Поиск значения в столбце и строке таблицы Excel

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

Поиск значений в таблице Excel

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

Лист с таблицей для поиска значений по вертикали и горизонтали:

Над самой таблицей расположена строка с результатами. В ячейку B1 водим критерий для поискового запроса, то есть заголовок столбца или название строки. А в ячейке D1 формула поиска должна возвращать результат вычисления соответствующего значения. После чего в ячейке F1 сработает вторая формула, которая уже будет использовать значения ячеек B1 и D1 в качестве критериев для поиска соответствующего месяца.

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

Теперь узнаем, в каком максимальном объеме и в каком месяце была максимальная продажа Товара 4.

Чтобы выполнить поиск по столбцам следует:

  1. В ячейку B1 введите значение Товара 4 – название строки, которое выступит в качестве критерия.
  2. В ячейку D1 введите следующую формулу:
  3. Для подтверждения после ввода формулы нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter, так как формула должна быть выполнена в массиве. Если все сделано правильно, в строке формул появятся фигурные скобки.
  4. В ячейку F1 введите вторую формулу:
  5. Снова Для подтверждения нажмите комбинацию клавиш CTRL+SHIFT+Enter.

Найдено в каком месяце и какая была наибольшая продажа Товара 4 на протяжении двух кварталов.

Принцип действия формулы поиска значения в строке Excel:

В первом аргументе функции ВПР (Вертикальный ПРосмотр) указывается ссылка на ячейку где находится критерий поиска. Во втором аргументе указывается диапазон ячеек для просмотра в процессе поиска. В третьем аргументе функции ВПР должен указываться номер столбца, из которого следует взять значение на против строки с именем Товар 4. Но так как нам заранее не известен этот номер мы с помощью функции СТОЛБЕЦ создаем массив номеров столбцов для диапазона B4:G15.

Это позволяет функции ВПР собрать целый массив значений. В результате в памяти хранится все соответствующие значения каждому столбцу по строке Товар 4 (а именно: 360; 958; 201; 605; 462; 832). После чего функции МАКС остается только взять из этого массива максимальное число и возвратить в качестве значения для ячейки D1, как результат вычисления формулы.

Как видно конструкция формулы проста и лаконична. На ее основе можно в похожий способ находить для определенного товара и другие показатели. Например, минимальное или среднее значение объема продаж используя для этого функции МИН или СРЗНАЧ. Вам ни что не препятствует, чтобы приведенный этот скелет формулы применить с использованием более сложных функций для реализации максимально комфортного анализа отчета по продажам.

Как получить заголовки столбцов по зачиню одной ячейки?

Например, как эффектно мы отобразили месяц, в котором была максимальная продажа, с помощью второй формулы. Не сложно заметить что во второй формуле мы использовали скелет первой формулы без функции МАКС. Главная структура формулы: ВПР(B1;A5:G14;СТОЛБЕЦ(B5:G14);0). Мы заменили функцию МАКС на ПОИСКПОЗ, которая в первом аргументе использует значение, полученное предыдущей формулой. Оно теперь выступает в качестве критерия для поиска месяца. И в результате функция ПОИСКПОЗ нам возвращает номер столбца 2 где находится максимальное значение объема продаж для товара 4. После чего в работу включается функция ИНДЕКС, которая возвращает значение по номеру сроки и столбца из определенного в ее аргументах диапазона. Так как у нас есть номер столбца 2, а номер строки в диапазоне где хранятся названия месяцев в любые случаи будет 1. Тогда нам осталось функцией ИНДЕКС получить соответственное значение из диапазона B4:G4 – Февраль (второй месяц).

Читать еще:  Как в excel сделать нумерацию строк

Поиск значения в столбце Excel

Вторым вариантом задачи будет поиск по таблице с использованием названия месяца в качестве критерия. В такие случаи мы должны изменить скелет нашей формулы: функцию ВПР заменить ГПР, а функция СТОЛБЕЦ заменяется на СТРОКА.

Это позволит нам узнать какой объем и какого товара была максимальная продажа в определенный месяц.

Чтобы найти какой товар обладал максимальным объемом продаж в определенном месяце следует:

  1. В ячейку B2 введите название месяца Июнь – это значение будет использовано в качестве поискового критерия.
  2. В ячейку D2 введите формулу:
  3. Для подтверждения после ввода формулы нажмите комбинацию клавиш CTRL+SHIFT+Enter, так как формула будет выполнена в массиве. А в строке формул появятся фигурные скобки.
  4. В ячейку F1 введите вторую формулу:
  5. Снова Для подтверждения нажмите CTRL+SHIFT+Enter.

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

В первом аргументе функции ГПР (Горизонтальный ПРосмотр) указываем ссылку на ячейку с критерием для поиска. Во втором аргументе указана ссылка на просматриваемый диапазон таблицы. Третий аргумент генерирует функция СТРОКА, которая создает в памяти массив номеров строк из 10 элементов. Так как в табличной части у нас находится 10 строк.

Далее функция ГПР поочередно используя каждый номер строки создает массив соответственных значений продаж из таблицы по определенному месяцу (Июню). Далее функции МАКС осталось только выбрать максимальное значение из этого массива.

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

ВНИМАНИЕ! При использовании скелета формулы для других задач всегда обращайте внимание на второй и третий аргумент поисковой функции ГПР. Количество охваченных строк в диапазоне указанного в аргументе, должно совпадать с количеством строк в таблице. А также нумерация должна начинаться со второй строки!

По сути содержимое диапазона нас вообще не интересует, нам нужен просто счетчик строк. То есть изменить аргументы на: СТРОКА(B2:B11) или СТРОКА(С2:С11) – это никак не повлияет на качество формулы. Главное, что в этих диапазонах по 10 строк, как и в таблице. И нумерация начинается со второй строки!

Поиск на листе Excel

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

Поиск перебором значений

Довольно простой в реализации способ. Например, найти в колонке «A» ячейку, содержащую «123» можно примерно так:

Минусами этого так сказать «классического» способа являются: медленная работа и громоздкость. А плюсом является его гибкость, т.к. таким способом можно реализовать сколь угодно сложные варианты поиска с различными вычислениями и т.п.

Поиск функцией Find

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

Вкратце опишу что делают строчки данного кода:
1-я строка: Выбираем в книге лист «Данные»;
2-я строка: Осуществляем поиск значения «123» в колонке «A», результат поиска будет в fcell;
3-я строка: Если удалось найти значение, то fcell будет содержать Range-объект, в противном случае — будет пустой, т.е. Nothing.

Полностью синтаксис оператора поиска выглядит так:

Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

What — Строка с текстом, который ищем или любой другой тип данных Excel

After — Ячейка, после которой начать поиск. Обратите внимание, что это должна быть именно единичная ячейка, а не диапазон. Поиск начинается после этой ячейки, а не с нее. Поиск в этой ячейке произойдет только когда весь диапазон будет просмотрен и поиск начнется с начала диапазона и до этой ячейки включительно.

LookIn — Тип искомых данных. Может принимать одно из значений: xlFormulas (формулы), xlValues (значения), или xlNotes (примечания).

LookAt — Одно из значений: xlWhole (полное совпадение) или xlPart (частичное совпадение).

SearchOrder — Одно из значений: xlByRows (просматривать по строкам) или xlByColumns (просматривать по столбцам)

SearchDirection — Одно из значений: xlNext (поиск вперед) или xlPrevious (поиск назад)

MatchCase — Одно из значений: True (поиск чувствительный к регистру) или False (поиск без учета регистра)

MatchByte — Применяется при использовании мультибайтных кодировок: True (найденный мультибайтный символ должен соответствовать только мультибайтному символу) или False (найденный мультибайтный символ может соответствовать однобайтному символу)

SearchFormat — Используется вместе с FindFormat. Сначала задается значение FindFormat (например, для поиска ячеек с курсивным шрифтом так: Application.FindFormat.Font.Italic = True), а потом при использовании метода Find указываем параметр SearchFormat = True. Если при поиске не нужно учитывать формат ячеек, то нужно указать SearchFormat = False.

Чтобы продолжить поиск, можно использовать FindNext (искать «далее») или FindPrevious (искать «назад»).

Примеры поиска функцией Find

Пример 1: Найти в диапазоне «A1:A50» все ячейки с текстом «asd» и поменять их все на «qwe»

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

Пример 2: Правильный поиск значения с использованием FindNext, не приводящий к зацикливанию.

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

Пример 3: Продолжение поиска с использованием Find с параметром After.

Следующий пример демонстрирует применение SearchFormat для поиска по формату ячейки. Для указания формата необходимо задать свойство FindFormat.

Пример 4: Найти все ячейки с шрифтом «курсив» и поменять их формат на обычный (не «курсив»)

Примечание: В данном примере намеренно не используется FindNext для поиска следующей ячейки, т.к. он не учитывает формат (статья об этом: https://support.microsoft.com/ru-ru/kb/282151)

Коротко опишу алгоритм поиска Примера 4. Первые две строки определяют последнюю строку (lLastRow) на листе и последний столбец (lLastCol). 3-я строка задает формат поиска, в данном случае, будем искать ячейки с шрифтом Italic. 4-я строка определяет область ячеек с которой будет работать программа (с ячейки A1 и до последней строки и последнего столбца). 5-я строка осуществляет поиск с использованием SearchFormat. 6-я строка — цикл пока результат поиска не будет пустым. 7-я строка — меняем шрифт на обычный (не курсив), 8-я строка продолжаем поиск после найденной ячейки.

Хочу обратить внимание на то, что в этом примере я не стал использовать «защиту от зацикливания», как в Примерах 2 и 3, т.к. шрифт меняется и после «прохождения» по всем ячейкам, больше не останется ни одной ячейки с курсивом.

Читать еще:  Фильтр в строке в excel

Свойство FindFormat можно задавать разными способами, например, так:

Следующий пример — применение функции Find для поиска последней ячейки с заполненными данными. Использованные в Примере 4 SpecialCells находит последнюю ячейку даже если она не содержит ничего, но отформатирована или в ней раньше были данные, но были удалены.

Пример 5: Найти последнюю колонку и столбец, заполненные данными

В этом примере используется UsedRange, который так же как и SpecialCells возвращает все используемые ячейки, в т.ч. и те, что были использованы ранее, а сейчас пустые. Функция Find ищет ячейку с любым значением с конца диапазона.

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

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

Для поиска функцией Find по маске (шаблону) можно применять символы:
* — для обозначения любого количества любых символов;
? — для обозначения одного любого символа;

— для обозначения символов *, ? и

. (т.е. чтобы искать в тексте вопросительный знак, нужно написать

?, чтобы искать именно звездочку (*), нужно написать

* и наконец, чтобы найти в тексте тильду, необходимо написать

Поиск даты с помощью Find

Если необходимо найти текущую дату или какую-то другую дату на листе Excel или в диапазоне с помощью Find, необходимо учитывать несколько нюансов:

  • Тип данных Date в VBA представляется в виде #[месяц]/[день]/[год]#, соответственно, если необходимо найти фиксированную дату, например, 01 марта 2018 года, необходимо искать #3/1/2018#, а не «01.03.2018»
  • В зависимости от формата ячеек, дата может выглядеть по-разному, поэтому, чтобы искать дату независимо от формата, поиск нужно делать не в значениях, а в формулах, т.е. использовать LookIn:=xlFormulas

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

Пример 7: Найти текущую дату на листе независимо от формата отображения даты.

Пример 8: Найти 1 марта 2018 г.

Искать часть даты — сложнее. Например, чтобы найти все ячейки, где месяц «март», недостаточно искать «03» или «3». Не работает с датами так же и поиск по шаблону. Единственный вариант, который я нашел — это выбрать формат в котором месяц прописью для ячеек с датами и искать слово «март» в xlValues.

Тем не менее, можно найти, например, 1 марта независимо от года.

Пример 9: Найти 1 марта любого года.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Функция ВПР

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

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

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

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

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

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

Читать еще:  Excel удалить отфильтрованные строки в excel

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

В принципе, данный подход устраивал бы, если для товаров, для которых отсутствует категория, не подтягивалось расположение. Обратите внимание на товар «Лук Подмосковье». Для него определено расположение «Стелаж №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 Вам помогли, то поддержите, пожалуйста, проект, чтобы мы могли развивать его дальше.

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

    Похожие статьи

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