Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ. Как пользоваться функцией индекс в excel
Исправление ошибки #Н/Д в функциях ИНДЕКС и ПОИСКПОЗ
Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке) .
В разделе описаны наиболее распространенные причины «Ошибка # н/д» должна отображаться, являющееся результатом функции индексили ПОИСКПОЗ .
Примечание: Если необходимо ИНДЕКСУ или ПОИСКПОЗ для возврата осмысленные значения вместо # н/д, используйте функцию IFERROR и затем вложить функций индекс и ПОИСКПОЗ в эту функцию. Только заменив собственное значение # н/д идентифицирует ошибку, но не решило. Итак, очень важно, прежде чем использовать ЕСЛИОШИБКА, убедитесь, что формула работает неправильно данное.
Проблема: Нет соответствий
Если функция ПОИСКПОЗ не находит искомое значение в массиве подстановки, возвращается ошибка # н/д.
Если вы считаете, что данные не содержится данных в электронной таблице, но не удалось найти его СООТВЕТСТВИЕ , возможно, так как:
-
Ячейка содержит непредвиденные символы или скрытые пробелы.
-
Ячейка не отформатированное правильный тип данных. Например ячейка содержит числовые значения, но он может быть отформатированные как текст.
Решение: чтобы удалить непредвиденные символы или скрытых пробелов, используйте функцию ПЕЧСИМВ или СЖПРОБЕЛЫ соответственно. Кроме того проверьте, если ячейки, отформатированные как типы данных.
Вы использовали формулу массива, но не нажали клавиши CTRL+SHIFT+ВВОД
При использовании массива в индекс, ПОИСКПОЗили сочетание этих двух функций необходим на клавиатуре нажмите клавиши Ctrl-клавиши Shift + Ввод. Excel автоматически будет размещать формулы в фигурные скобки {}. Если вы пытаетесь ввести их, Excel отобразит формулу как текст.
Проблема: Несоответствие типа сопоставления и порядка сортировки данных
При использовании СООТВЕТСТВИЕмежду значениями в качестве аргумента тип_сопоставления и порядок сортировки значений в массиве подстановки должно быть согласованность. Если синтаксис отличается от перечисленных ниже правил, появляется ошибка # н/д.
-
Если тип_сопоставления равен 1 или не указан, значения в просматриваемый_массив должен быть в порядке возрастания. Например, -2, -1, 0, 1, 2..., A, B и C..., ЛОЖЬ, ИСТИНА лишь некоторые из них.
-
Еслитип_сопоставления -1, значения в просматриваемый_массив должен быть в порядке убывания.
В приведенном ниже примере — функция ПОИСКПОЗ
=MATCH(40,B2:B10,-1)
Аргумент тип_сопоставления в синтаксисе присваивается значение -1, это означает, что порядок значений в B2: B10 должен быть в порядке убывания формулы для работы. Но значения в порядке возрастания, и, которая приводит к ошибке # н/д.
У вас есть вопрос об определенной функции?
Задать вопрос на форуме сообщества, посвященном Excel
Помогите нам улучшить Excel
У вас есть предложения по улучшению следующей версии Excel? Если да, ознакомьтесь с темами на портале пользовательских предложений для Excel.
См. также
Как исправить ошибку # н/д
Способы использования функций индекс и ПОИСКПОЗ с несколькими условиями в Excel
Функция ИНДЕКС
Общие сведения о формулах в Excel
Рекомендации, позволяющие избежать появления неработающих формул
Обнаружение ошибок в формулах с помощью функции проверки ошибок
Все функции Excel (по алфавиту)
Все функции Excel (по категориям)
Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ
Для поиска значения в большом списке можно использовать функцию просмотра. Функция ВПР часто используется, но можно задействовать и функции ГПР, ИНДЕКС и ПОИСКПОЗ.
Общий вид функции ВПР и ее аргументов:
=ВПР(<искомое значение>;<диапазон поиска>;<столбец>;<приблизительное соответствие>)
Например, =ВПР(21500;C2:E7;3;ЛОЖЬ).
-
Первый аргумент (часть, необходимая для работы функции) — это искомое значение. Это может быть ссылка на ячейку, например B2, или значение, например "кузьмина" или 21500.
-
Второй аргумент — это диапазон ячеек, который, как вы предполагаете, содержит искомое значение.
Важно: В функции ВПР столбец, содержащий искомое значение или ссылку на ячейку, должен быть крайним левым столбцом в диапазоне.
-
Третий аргумент — это столбец в диапазоне поиска ячеек, содержащий значение, которое нужно найти.
Хотя четвертый аргумент не является обязательным, большинство пользователей вводят аргумент ЛОЖЬ (или 0). Почему? Потому что в этом случае функция будет искать точное совпадение. Можно ввести аргумент ИСТИНА или вообще не вводить аргумент, но если точное совпадение не будет найдено, функция вернет наиболее близкое приблизительное совпадение, а большинство людей приблизительное совпадение не устраивает.
Чтобы убедиться в том, что использование приблизительного совпадения может иметь серьезные последствия, предположим, что ищется цена детали с идентификатором 2345768, но вы перепутали две цифры и ввели их в формулу следующим образом: =ВПР(2345678;A1:E7;5). Формула возвращает цену на другую деталь, потому что функция ВПР нашла ближайшее число, меньшее или равное указанному (2345678). Эта ошибка может привести к неправильному выставлению счета клиенту.
Если для аргумента "приблизительное соответствие" указано значение ЛОЖЬ или 0, а точного совпадения нет, вместо неправильного значения формула возвращает в ячейку строку "#Н/Д". Это наилучшее решение. В данном случае "#Н/Д" не означает, что формула введена неправильно (за исключением неправильно введенного номера). Это означает, что номер 2345678 не был найден, потому что вы искали значение 2345768.
В этом примере показано, как работает функция. Если ввести значение в ячейку B2 (первый аргумент), функция ВПР выполняет поиск в ячейках C2:E7 (второй аргумент) и возвращает наиболее близкое приблизительное совпадение из третьего столбца в диапазоне — столбца E (третий аргумент).
В данном примере четвертый аргумент оставлен пустым, поэтому функция возвращает приблизительное совпадение.
Использование функции ГПР
Разобравшись с функцией ВПР, несложно будет освоить и функцию ГПР. Функция ГПР использует те же аргументы, но выполняет поиск в строках вместо столбцов.
Одновременное использование функций ИНДЕКС и ПОИСКПОЗ
Если вы не хотите ограничиваться поиском в крайнем левом столбце, можно использовать сочетание функций ИНДЕКС и ПОИСКПОЗ. Формула, использующая эти функции вместе, немного сложнее формулы с функцией ВПР, но она открывает больше возможностей. Поэтому некоторые пользователи предпочитают применять сочетание функций ИНДЕКС и ПОИСКПОЗ, а не функцию ВПР.
Еще о функциях поиска
К началу страницы
Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ
Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке) .
Предположим, что у вас есть списка номеров офисов расположение и вам нужно знать, какие сотрудники являются в каждой программы office. Электронную таблицу огромный, поэтому вы думаете, что он является довольно сложной задачи. Это задача несложная делать с помощью функции поиска.
Функции ВПР и ГПР вместе с функций индекс и ПОИСКПОЗ,описаны некоторые из наиболее полезных функций в Microsoft Excel.
Примечание: Функция мастер подстановок больше не доступен в Microsoft Excel.
Вот пример того, как использовать функцию ВПР.
=ВПР(B2;C2:E7,3,ИСТИНА)
В этом примере B2 — это первый аргумент— элемент данных, функция должна работать. Функции ВПР это первый аргумент — значение, которое требуется найти. Этот аргумент может быть ссылка на ячейку или фиксированным значением, например «строфа» или 21,000. Второй аргумент — это диапазон ячеек, C2-:E7, в котором выполняется поиск значения, которые нужно найти. Третий аргумент — это столбец в диапазон ячеек, содержащий значение, которое вы поиска.
Четвертый аргумент не является обязательным. Введите TRUE или FALSE. Если ввести значение ИСТИНА или аргумент оставлен пустым, функция возвращает приблизительное значение, указать в качестве первого аргумента. Если ввести значение FALSE, функция будут соответствовать значение в первом аргументе предоставить. Другими словами, оставив четвертый аргумент пустым, или ввести значение ИСТИНА — обеспечивает гибкость.
В этом примере показано, как работает функция. При вводе значения в ячейке B2 (первый аргумент) функция ВПР ищет ячейки в диапазоне C2:E7 (2-й аргумент) и возвращает ближайший Приблизительное совпадение с третьего столбца в диапазоне, столбец E (3-й аргумент).
Четвертый аргумент пуст, поэтому функция возвращает Приблизительное совпадение. Если это не так, вам придется введите одно из значений в столбцах C и D, чтобы получить результат вообще.
Когда вы будете довольны ВПР, ГПР одинаково удобно использовать. Введите те же аргументы, но он осуществляет поиск в строках вместо столбцов. "
Попробуйте попрактиковаться
Если вы хотите поэкспериментировать с функциями подстановки, прежде чем применять их к собственным данным, то некоторые образцы данных. Некоторые пользователи Excel, такие как с помощью функции ВПР и ГПР; другие пользователи предпочитают с помощью функций индекс и ПОИСКПОЗ вместе. Попробуйте каждый из методов и посмотрите, какие из них подходящий вариант.
Пример функции ВПР в действии
Скопируйте следующие данные в пустой лист.
Совет: Прежде чем вставлять данные в Excel, установите для столбцов A – С ширину в 250 пикселей и нажмите кнопку Перенос текста (вкладка "Главная ", группа " Выравнивание ").
|
Плотность |
Вязкость |
Температура |
|
0,457 |
3,55 |
500 |
|
0,525 |
3,25 |
400 |
|
0,606 |
2,93 |
300 |
|
0,675 |
2,75 |
250 |
|
0,746 |
2,57 |
200 |
|
0,835 |
2,38 |
150 |
|
0,946 |
2,17 |
100 |
|
1,09 |
1,95 |
50 |
|
1,29 |
1,71 |
0 |
|
Формула |
Описание |
Результат |
|
=ВПР(1,A2:C10,2) |
Используя приблизительное соответствие, функция ищет в столбце A значение 1, находит наибольшее значение, которое меньше или равняется 1 и составляет 0,946, а затем возвращает значение из столбца B в той же строке. |
2,17 |
|
=ВПР(1,A2:C10,3,ИСТИНА) |
Используя приблизительное соответствие, функция ищет в столбце A значение 1, находит наибольшее значение, которое меньше или равняется 1 и составляет 0,946, а затем возвращает значение из столбца C в той же строке. |
100 |
|
=ВПР(0,7,A2:C10,3,ЛОЖЬ) |
Используя точное соответствие, функция ищет в столбце A значение 0,7. Поскольку точного соответствия нет, возвращается сообщение об ошибке. |
#Н/Д |
|
=ВПР(0,1,A2:C10,2,ИСТИНА) |
Используя приблизительное соответствие, функция ищет в столбце A значение 0,1. Поскольку 0,1 меньше наименьшего значения в столбце A, возвращается сообщение об ошибке. |
#Н/Д |
|
=ВПР(2,A2:C10,2,ИСТИНА) |
Используя приблизительное соответствие, функция ищет в столбце A значение 2, находит наибольшее значение, которое меньше или равняется 2 и составляет 1,29, а затем возвращает значение из столбца B в той же строке. |
1,71 |
Пример функции ГПР
Скопируйте всю таблицу и вставьте ее в ячейку A1 пустого листа Excel.
Совет: Прежде чем вставлять данные в Excel, установите для столбцов A – С ширину в 250 пикселей и нажмите кнопку Перенос текста (вкладка "Главная ", группа " Выравнивание ").
|
Оси |
Подшипники |
Болты |
|
4 |
4 |
9 |
|
5 |
7 |
10 |
|
6 |
8 |
11 |
|
Формула |
Описание |
Результат |
|
=ГПР("Оси";A1:C4;2;ИСТИНА) |
Поиск слова "Оси" в строке 1 и возврат значения из строки 2, находящейся в том же столбце (столбец A). |
4 |
|
=ГПР("Подшипники";A1:C4;3;ЛОЖЬ) |
Поиск слова "Подшипники" в строке 1 и возврат значения из строки 3, находящейся в том же столбце (столбец B). |
7 |
|
=ГПР("П";A1:C4;3;ИСТИНА) |
Поиск буквы "П" в строке 1 и возврат значения из строки 3, находящейся в том же столбце. Так как "П" найти не удалось, возвращается ближайшее из меньших значений: "Оси" (в столбце A). |
5 |
|
=ГПР("Болты";A1:C4;4) |
Поиск слова "Болты" в строке 1 и возврат значения из строки 4, находящейся в том же столбце (столбец C). |
11 |
|
=ГПР(3;{1;2;3:"a";"b";"c";"d";"e";"f"};2;ИСТИНА) |
Поиск числа 3 в трех строках константы массива и возврат значения из строки 2 того же (в данном случае — третьего) столбца. Константа массива содержит три строки значений, разделенных точкой с запятой (;). Так как "c" было найдено в строке 2 того же столбца, что и 3, возвращается "c". |
c |
ИНДЕКС и ПОИСКПОЗ примеры
В этом примере последней использует функций индекс и ПОИСКПОЗ вместе для возвращения раннюю номер счета-фактуры и его соответствующих даты для каждого из пяти городов. Так как дата возвращаются в виде числа, мы используем функцию текст отформатировать его как дату. Результат функции ПОИСКПОЗ фактически используется функция индекс аргументом. Сочетание функций индекс и ПОИСКПОЗ используются два раза в каждой формуле — сначала получить номер счета-фактуры, а затем для возврата даты.
Скопируйте всю таблицу и вставьте ее в ячейку A1 пустого листа Excel.
Совет: Прежде чем вставлять данные в Excel, установите для столбцов A – D ширину в 250 пикселей и нажмите кнопку Перенос текста (вкладка "Главная ", группа " Выравнивание ").
|
Счет |
Город |
Дата выставления счета |
Самая ранняя счет по городу, с датой |
|
3115 |
Казань |
07.04.12 |
="Казань = "&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ("Казань",$B$2:$B$33,0),1)& ", Дата выставления счета: " & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ("Казань",$B$2:$B$33,0),3),"m/d/yy") |
|
3137 |
Казань |
09.04.12 |
="Орел = "&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ("Орел",$B$2:$B$33,0),1)& ", Дата выставления счета: " & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ("Орел",$B$2:$B$33,0),3),"m/d/yy") |
|
3154 |
Казань |
11.04.12 |
="Челябинск = "&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ("Челябинск",$B$2:$B$33,0),1)& ", Дата выставления счета: " & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ("Челябинск",$B$2:$B$33,0),3),"m/d/yy") |
|
3191 |
Казань |
21.04.12 |
="Нижний Новгород = "&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ("Нижний Новгород",$B$2:$B$33,0),1)& ", Дата выставления счета: " & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ("Нижний Новгород",$B$2:$B$33,0),3),"m/d/yy") |
|
3293 |
Казань |
25.04.12 |
="Москва = "&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ("Москва",$B$2:$B$33,0),1)& ", Дата выставления счета: " & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ("Москва",$B$2:$B$33,0),3),"m/d/yy") |
|
3331 |
Казань |
27.04.12 |
|
|
3350 |
Казань |
28.04.12 |
|
|
3390 |
Казань |
01.05.12 |
|
|
3441 |
Казань |
02.05.12 |
|
|
3517 |
Казань |
08.05.12 |
|
|
3124 |
Орел |
09.04.12 |
|
|
3155 |
Орел |
11.04.12 |
|
|
3177 |
Орел |
19.04.12 |
|
|
3357 |
Орел |
28.04.12 |
|
|
3492 |
Орел |
06.05.12 |
|
|
3316 |
Челябинск |
25.04.12 |
|
|
3346 |
Челябинск |
28.04.12 |
|
|
3372 |
Челябинск |
01.05.12 |
|
|
3414 |
Челябинск |
01.05.12 |
|
|
3451 |
Челябинск |
02.05.12 |
|
|
3467 |
Челябинск |
02.05.12 |
|
|
3474 |
Челябинск |
04.05.12 |
|
|
3490 |
Челябинск |
05.05.12 |
|
|
3503 |
Челябинск |
08.05.12 |
|
|
3151 |
Нижний Новгород |
09.04.12 |
|
|
3438 |
Нижний Новгород |
02.05.12 |
|
|
3471 |
Нижний Новгород |
04.05.12 |
|
|
3160 |
Москва |
18.04.12 |
|
|
3328 |
Москва |
26.04.12 |
|
|
3368 |
Москва |
29.04.12 |
|
|
3420 |
Москва |
01.05.12 |
|
|
3501 |
Москва |
06.05.12 |
Дополнительные сведения о функциях поиска
support.office.com
10 функций Excel, которые должен знать любой SEO-оптимизатор
Многие владельцы веб-сайтов используют Excel практически ежедневно. С его помощью они могут хранить список обратных ссылок, которые необходимо отслеживать, или вести мониторинг статистики ключевых слов. Но настоящие профессионалы SEO знают, как использовать Excel для максимизации эффективности SEO-стратегии. Они настолько искусно манипулируют данными, что появляется возможность по-новому взглянуть на всю кампанию по продвижению ресурса в поисковых системах. В этой статье я постараюсь описать свои любимые трюки, позволяющие добиться такого эффекта.
Для начала мы изучим основы, а затем перейдем к более сложным вещам. Некоторые из этих базовых функций могут быть использованы в тандеме с другими, чтобы сформировать более мощные команды.ПОИСК и НАЙТИ
Эти простые функции позволяют определить расположение искомой текстовой строки внутри другой текстовой строки. Функции идентичны, за исключением того, что в случае НАЙТИ учитывается регистр, а в случае ПОИСК – нет. ПОИСК также поддерживает символы обобщения. ПОИСК и НАЙТИ возвращают позицию первого символа в строке.
Синтаксис:
=ПОИСК(искомый_текст,просматриваемый_текст,[начальная_позиция])
=НАЙТИ(искомый_текст,просматриваемый_текст,[нач_позиция])
Функция ПОИСК может быть очень полезна, когда вы хотите отсортировать длинный список доменов по ключевым словам. В случае доменов, которые не содержат искомые ключевые слова, будет возвращено «#ЗНАЧЕНИЕ», тогда как в случае содержащих ключевое слово доменов будет возвращено число. Пример может быть таким:
=ПОИСК(“dog”;A1)
В этом примере мы ищем ключевое слово «dog» в URL. Эта функция возвращает число 12, потому что слово «dog» начинается с 12-го символа в строке с URL.
ЛЕВСИМВ, ПСТР и ПРАВСИМВ
ЛЕВСИМВ возвращает указанное количество первых символов текстовой строки.
Пример: = ЛЕВСИМВ (A1, 7) возвращает первые семь символов слева в строке из ячейки A1.
ПСТР возвращает заданное число знаков из текстовой строки, начиная с указанной позиции.
Пример: =ПСТР(A1,12,3) вернет три символа в строке, начиная с 12-го символа.
ПРАВСИМВ работает аналогичным ЛЕВСИМВ образом, но возвращает символы справа, то есть функция возвращает последний символ или несколько последних символов текстовой строки на основе заданного числа символов. Эта функция может оказаться полезной, например, при удалении блока «http://www.» из каждого доменного имени в списке доменов.
Пример: =ПРАВСИМВ(A1,14) возвращает 14 последних символов в строке.
Эти функции могут быть полезны для вычленения отдельных ключевых слов, доменных имен, данных LSI и другой информации из строки. Например, вы можете скопировать и вставить длинный список обратных ссылок в Excel, а затем создать новую функцию, которая ведет поиск по определенному ключевому слову. Затем следует скопировать и вставить функцию во все ячейки свободного столбца, чтобы задействовать каждый URL. Так вы сможете узнать количество сайтов, в доменных именах которых присутствует определенное ключевое слово.
СЦЕПИТЬ
СЦЕПИТЬ позволит вам объединять строки. Это может быть полезно при объединении отдельных частей URL в один адрес. Данная функция также может быть использована для создания запросов на построение ссылок, карт сайтов и списков ключевых слов.
Пример: = СЦЕПИТЬ(A1,B1) объединит ячейки A1 и B1.
СЛЧИС
Функция СЛЧИС создает случайное число. Она существенно упрощает рандомную сортировку списков информации. Рандомизация является одним из важнейших способов придания результатам вашей работы естественности, а это ключ к успешной кампании по построению ссылок.
Пример: поместив =СЛЧИС() в каждой строке в столбце мы сможем генерировать случайные числа.
После того как вы создали свой список случайных чисел, нужно будет скопировать список и вставить значения в новый столбец таблицы, используя опцию «значение» в параметрах вставки (см. рисунок ниже), потому что случайные числа будут генерироваться каждый раз поле сортировки поля. Путем вставки цифр в виде значений вы гарантируете, что цифры не изменятся.
Ниже приведен пример из пяти URL-адресов, отсортированных случайным образом по соответствующим сгенерированным рандомно цифрам в левом столбце.
Рандомная сортировка списка URL-адресов / доменов может быть полезна при сплит-тестирования различных шаблонов с минимизацией смещений, которые могут быть вызваны отсутствием рандомизации списка.
СМЕЩ
Вот отличный видео туториал (на английском) касательно использования функции «СМЕЩ». Согласно справке Microsoft, СМЕЩ «возвращает ссылку на диапазон, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов». Эта функция может оказаться полезной с точки зрения SEO-оптимизатора, к примеру, при анализе флуктуаций ключевых слов в экспортированных файлах Google Analytics CSV.
Синтаксис: СМЕЩ (ссылка, смещ_по_строкам, смещ_по_столбцам, [высота], [ширина])
Продолжим работать с ключевой фразой “dog food” и экспортируем данные из Google Analytics (см. ниже):
В данном примере мы анализируем источники трафика по ключевым словам за неделю в сравнении с предыдущей неделей. Если бы нам требовалось сравнить данные за несколько недель, то проблем бы не было. Тем не менее, когда просматриваешь данные по нескольким тысячам ключевых слов в разрезе множества промежутков времени, то задача существенно усложняется.
С помощью команды СМЕЩ мы можем придать этому списку ключевых слов и диапазонов времени следующий вид:
Группировка данных для создания первой строк сложностей не представляет. После этого мы можем использовать команду СМЕЩ, чтобы переместить ключевые слова и данные по ним за обе недели с помощью следующих формул:
Обратите внимание на то, что команда СТРОКА (ROW на рисунке выше) возвращает номер ячейки, в которой она находится. Это позволяет нам переходить к другим строкам и перемещать данные.
ИНДЕКС и ПОИСКПОЗ
Многие пользователи Excel охотно работают с командой ВПР. Но эта функция ограничена тем, что искомая информация должна находиться в самой первой (слева) колонке массива данных. Команда ИНДЕКС не имеют такого ограничения. Она эффективно возвращает значение из указанной строки и колонки в пределах заданного диапазона.
Синтаксис: ИНДЕКС(массив, номер_строки, [номер_столбца])
Для примера используем формулу =ИНДЕКС(A1:A5,3). С ее помощью мы можем извлечь текст из третьей строки массива в столбце A.
ПОИСКПОЗ повышает полезность ИНДЕКС. В отличие от ИНДЕКС, которая возвращает слово, функция ПОИСКПОЗ возвращает число.
Синтаксис: ПОИСКПОЗ(искомое_значение, просматриваемый_массив, [тип_сопоставления])
- Искомое_значение – буква или фраза, которую вы хотите найти.
- Просматриваемый_массив – массив данных, который вы хотите найти.
- Тип_сопоставления определяет тип сопоставления.
К числу опций Тип_сопоставления относятся:
- «1» Меньше чем – Это значение по умолчанию. В данном случае функция найдет соответствие меньшее или равное искомому значению при условии, что список упорядочен в порядке возрастания.
- «-1» Больше чем – Находит наименьшее значение, которое больше или равно искомому значению, при условии, что список упорядочен в порядке убывания.
- «0» возвращает первое найденное значение, равное вашему искомому значению. Это рекомендуемая опция для целей SEO.
Используем пример выше с массивом данных по кормам для питомцев (pet food). Следующая формула вернет расположение (номер строки) фразы «guinea pig food» в массиве данных.
=ПОИСКПОЗ(“guinea pig food”,A1:A5,0)
В этом случае функция возвращает число 4, так как ключевая фраза «guinea pig food» проходит в нашем списке под четвертым номером.
Комбинируем ИНДЕКС и ПОИСКПОЗ
Вы можете комбинировать ИНДЕКС и ПОИСКПОЗ для решения более сложных задач. Комбинируйте их, используя следующий метод:
=ИНДЕКС(массив, формула ПОИСКПОЗ)
В качества примера давайте снова возьмем наш список ключевых слов о корме для собак (dog food) с несуществующего сайта dogfoodrus.com. В приведенной ниже таблице имеются разные ключевые слова, которые являются источником органического трафика для определенных URL-адресов целевой страницы. В каждой строке приводится количество посещений и переходов, связанных с конкретным ключевым словом.
Используя команды ИНДЕКС и ПОИСКПОЗ вместе, можно создать механизм поиска ключевых слов, который позволит вам получить информацию о количестве посетителей и переходов по определенному ключевому слову. Если у вас есть только четыре или пять ключевых слов (как в примере выше), искать нужные данные вручную несложно. Однако если вы скачали файлы Analytics или Google Webmaster Tools с данными о тысячах ключевых слов, то использование приведенных ниже формул существенно упрощает поиск определенных ключевых слов и создание определенного списка.
=ИНДЕКС($B$3:$E$7,ПОИСКПОЗ(B11,$B$3:$B$7,0),3) – число посещений
=ИНДЕКС($B$3:$E$7,ПОИСКПОЗ(B11,$B$3:$B$7,0),4) – число переходов
В этом примере функция ИНДЕКС распространяется на диапазон от B3 до E7, который охватывает все импортированные данные. Функция использует команду ПОИСКПОЗ, чтобы определить строку в массиве, где найдено совпадение. Цифры 3 и 4 в конце формулы относятся к столбцам, где находятся данные, которые вам нужно скопировать.
Есть много вариаций на тему применения ИНДЕКС и ПОИСКПОЗ вместе. С их помощью можно на порядок упростить поиск информации в огромной таблице. В отличие от ВПР, для эффективной работы с ИНДЕКС и ПОИСКПОЗ не требуется, чтобы поле поиска находилось в крайнем левом столбце массива.
Заключение
Это наиболее востребованные функции Excel при работе над кампаниями по SEO-продвижению. Excel можно использовать для многих SEO-задач, в том числе для работы с обратными ссылками, для отслеживания ключевых слов, просмотров, переходов и многого другого.
Может быть, вам известны другие полезные советы по использованию Excel для SEO? Буду признателен, если вы поделитесь данной информацией в комментариях к материалу!
yavbloge.ru
Функция ПОИСКПОЗ в Excel | SirExcel
Сама по себе функция = ПОИСКПОЗ () используется редко, но в связке с другими функциями просто не заменима. Например, данную функцию очень часто используют вместе с ИНДЕКС, которую мы недавно рассматривали.
ПОИСКПОЗ расшифровывается как «Поиск по значению». Название говорит само за себя. Функция ищет заданное значение в строке или в столбце и возвращает ее порядковый номер (по строкам или столбцам). Отсчет номера начинается с первой позиции заданного диапазона.
Синтаксис функции выглядит следующим образом:
ПОИСКПОЗ(искомое_значение, просматриваемый_массив, [тип_сопоставления])
Чтобы было понятнее, давайте рассмотрим на конкретном примере. Предположим у нас есть список городов (диапазон B2:B6). Наша задача состоит в том, чтобы находить порядковый номер указанного нами города.
Наглядно это должно выглядеть следующим образом. Ввели мы в ячейку D2 слово Киев, а в ячейке E2 автоматически появился порядковый номер данного города из диапазона городов B2:B6. Так как Киев находится третьим по порядку, то функция ПОИСКПОЗ должна вернуть нам цифру 3
Итак, чтобы решить задачу в рассматриваемом примере, нам необходимо в ячейку E2 ввести следующую формулу:
=ПОИСКПОЗ(D2;B2:B6;0)
где D2 – искомое_значение (т.е слово “Киев”)
B2:B6 – просматриваемый_массив (т.е список наших городов)
0 – тип_сопоставления, означает, что нам необходимо искать точное значение города.
Теперь в зависимости от того, какой город будет указан в ячейке D2 будет отображаться его порядковый номер из диапазона городов.
В следующих статьях мы рассмотрим с вами действие ПОИСКПОЗ совместно с другими функциями Excel.
Спасибо за внимание.
SirExcel.ru – Безграничные возможности Excel
sirexcel.ru
