Как найти различия в двух столбцах Google Sheets и вывести их?
Есть два столбца с текстовыми списками. Нужно найти в первом столбце значения, которые не повторяются во втором, и параллельно найти значения из второго, которые не повторяются в первом. То есть найти различия в списках. Затем нужно вывести результат в отдельный диапазон ячеек — найденные уникальные значения из первого списка в один столбец, а из второго — во второй:
Пробовал сделать всё при помощи условного форматирования с формулой
=СЧЁТЕСЛИ($A:$B;A1) с последующей фильтрацией списка по цвету заливки, но на это уходит много ручной работы. Есть ли способ реализовать это как-то по-другому при помощи формулы фильтра или чего-то подобного?
Заранее благодарю!
- Вопрос задан более двух лет назад
- 2722 просмотра
2 комментария
Простой 2 комментария
Выбор уникальных: SORTN. Функция, которую нужно уложить в голове
Когда я впервые столкнулся с формулой SORTN , то у меня было ощущение какой-то недосказанности и непонятости, я старался редко ее использовать в Google Таблицах.
Но со временем все очень сильно изменилось. Я понял, что она может быть мощным инструментм в комбинации с другими функциями для решения многих задач. Считаю, что SORTN недостаточно популярна. Серьезно сомневаюсь, что большинство пользователей Google Sheets вообще знают об этой функции. Однако, она хранит в себе отличный потенциал, который раскрывается, когда узнаешь, как это работает.
Все примеры доступны в Таблице, см. раздел Ссылки .
Где может быть использована формула SORTN
Чтобы представить, что умеет SORTN необходимо взглянуть на то, чем ее можно заменить. Например, QUERY или комбинация UNIQUE и SORT часто могут дать нужный результат, но не всегда. SORTN также похожа на работу UNIQUE и ARRAY_CONSTRAIN .
SORTN предназначена для выбора основанного на сортировке значений и удаления дубликатов.
В общем смысле, SORTN предназначена для возврата первых N строк в наборе данных после выполнения сортировки. Сортировка может быть в порядке возрастания или убывания. Таким образом, можно выбирать наибольшие или наименьшие элементы из диапазона. Кроме того, если в данных есть повторяющиеся записи, можно решить, включать или исключать дубликаты.
Как использовать SORTN
Синтаксис кажется немного нагроможденным. Но к нему достаточно просто привыкнуть.
Ниже приводится немного интерпретированная SORTN — Cправка
Синтаксис
SORTN(диапазон; [n]; [режим_показа_совпадений]; [столбец_для_сортировки1, по_возрастанию]; . )
- диапазон – данные для сортировки, после которой необходимо выбрать первые n элементов.
- n – (НЕОБЯЗАТЕЛЬНО, по умолчанию 1 ) количество возвращаемых элементов. Должно быть больше 0 . Чтобы вывести весь результат, достаточно ввести очень большое число, такое как 9^9 . Этот аргумент необязательно использовать только в случае вызова с единственным аргументом диапазон , например, =SORTN(диапазон) .
- режим_показа_совпадений – (НЕОБЯЗАТЕЛЬНО, по умолчанию 0 ) число, определяющее способ показа совпадений.
- 0 : показать не более нескольких первых строк ( n ) в отсортированном диапазоне (простой вывод);
- 1 : показать не более нескольких первых строк ( n ), а также все дополнительные строки, идентичные строке с порядковым номером n (выбор всех подходящих);
- 2 : показать не более нескольких первых строк ( n ) после удаления повторяющихся строк (выбор уникальных);
- 3 : показать не более нескольких первых уникальных строк ( n ) со всеми их точными копиями (выбор всех уникальных).
Примечания
- диапазон сортируется только в области указанных столбцов, остальные столбцы возвращаются в своем изначальном порядке.
- Если аргументы столбец_для_сортировки1 и по_возрастанию1 отсутствуют, сортировка выполняется по столбцу с наименьшим порядковым номером в диапазоне, а следующие столбцы используются для поиска совпадений.
Простые примеры использования
В данном разделе все примеры отталкиваются от следующего набора данных, его легенда: равное количество участников проходят несколько туров, за которые получают очки и дополнительные баллы
Получение двух первых из сортировки по возрастанию
Формула выводит первых двух участников в первом туре в порядке вхождения в массив
=SORTN(B4:E23;2;0;1;1)Пояснение: из диапазона B4:E23 взять первые две строки, диапазон сортировать по первой колонке по возрастанию, т.е. по туру. режим_показа_совпадений не применять.
Получение двух первых из сортировки по убыванию
Формула выводит первых двух участников в последнем туре в порядке вхождения в массив
=SORTN(B4:E23;2;0;1;0)Выбрать все, но не менее определенного количества
Положим, что нам нужно взять всех участников с минимальными очками (в данном случае это 1 ), но их должно быть не менее пяти. Т.е. если участников с самыми минимальными очками 1 окажется меньше пяти, то необходимо выбрать остальных со следующим минимальным значением 2 . Всего будет выведено шесть значений.
=SORTN(B4:E23;5;1;3;1)Пояснение: из диапазона B4:E23 взять не менее пяти строк, диапазон сортировать по третьей колонке по возрастанию, т.е. по очкам. режим_показа_совпадений — получить все значения, если их окажется меньше пяти, то взять следующее исходя из сортировки.
Выбрать все равные первому, определенному сортировкой
Положим, что нам нужно взять всех участников с максимальными дополнительными баллами (в данном случае это 2 ). Найдем только первое значение сортировки и выведем все похожие на него.
=SORTN(B4:E23;1;1;4;0)Пояснение: из диапазона B4:E23 взять первую строку, диапазон сортировать по четвертой колонке по убыванию, т.е. по баллам. режим_показа_совпадений — получить первое значение и все на него похожие.
Выбрать все уникальные записи
Требуется найти все уникальные комбинации очков и баллов для каждого участника. Будет выведен результат аналогичный UNIQUE , только с сортировкой всех колонок в порядке старшинства.
=SORTN(B4:E23;9^9;2)Пояснение: из диапазона B4:E23 взять все уникальные записи, сортировать по всем колонкам в основном порядке.
Этот пример не является характерным. Следующий пример комбинирует возможности режима 1 и 2 .
Выбрать все уникальные записи по условию сортировки
Требуется найти первые три уникальные максимальные значения очков (это 11 , 9 и 8 ) и выбрать все записи с ними.
=SORTN(B4:E23;9^9;2)Пояснение: из диапазона B4:E23 взять все уникальные записи, сортировать по всем колонкам в основном порядке.
Выводы и рекомендации
Возможности SORTN при первом знакомстве сложно переоценить. Формула кажется неэффективной и запутанной. Но стоит приблизится к ней, рассмотреть, изучить внимательнее, и она становится мощным и полезным инструментом.
В примеры добавлены не рассмотренные в статье варианты реального применения формулы и вызов без аргументов, а также другие случаи из практики.
Ссылки
- Теги TRANSPOSE , ARRAYFORMULA , FLATTEN
- SORTN — Cправка — Редакторы Google Документов
- Примеры в Таблице
25+ функций Google Таблиц для работы с текстом
Google Таблицы (Google Spreadsheets) — табличный, а не текстовый редактор, тем не менее там часто приходится иметь дело именно с текстом. Рассказываем о соответствующих функциях.
ДЛСТР (LEN)
ДЛСТР (LEN) — функция для вычисления длины строки. Пригодится, если работаете в Google Sheets с контент-планами, текстами объявлений, метатегами — сразу можно проверить, соответствует текст нужным критериям или нет.
Синтаксис простой, аргумент один — текстовая строка (можно указать прямо в формуле или взять из ячейки). Например, =ДЛСТР(«Блог CyberMarketing про интернет-маркетинг») вернет значение 42. Учитываются все символы, включая пробелы и переводы строки.
Если нужно проверить тайтлы страниц, а сами они пока неизвестны, можно вложить в LEN функцию IMPORTXML: =ДЛСТР(IMPORTXML(B2;»//title»)) В данном случае результатом станет длина метатега TITLE, полученного для URL, который лежит в ячейке B2.
Так работает IMPORTXML, вложенный в LEN
Но вообще решать такие задачи с помощью парсера Click.ru гораздо проще и удобнее.
СТРОЧН (LOWER), ПРОПИСН (UPPER)
СТРОЧН (LOWER) приводит все символы к нижнему регистру, а ПРОПИСН (UPPER) — наоборот, к верхнему. Обе текстовые функции принимают один аргумент — строку в кавычках или ячейку с текстом. Получается, конструкция =ПРОПИСН(СТРОЧН(«CYBERMARKETING»)) выведет ту же самую строку — «CYBERMARKETING».
Но на практике часто можно обойтись без этих функций. Для поиска без учета регистра есть ПОИСК (SEARCH) — о ней чуть позже. А, например, у QUERY, которую мы разбирали в предыдущей статье, есть свои встроенные функции — upper() и lower() — для приведения строк к единому регистру.
СЦЕПИТЬ (CONCATENATE), JOIN, TEXTJOIN
Все три функции объединяют несколько строк в одну. Работают похожим способом, но есть некоторые отличия.
CONCATENATE (СЦЕПИТЬ) дословно сцепляют несколько строк между собой по очереди. Синтаксис: =СЦЕПИТЬ(«Hello»;», «;»World»;»!»)
Простой пример использования CONCATENATE. Но такой способ, подойдет, пожалуй, только для немецкого языка.
Чтобы они не слипались между собой, придется каждый раз добавлять между ними пробел (или другой символ). Например: =СЦЕПИТЬ(A15;» «;B15;» «;C15;» «;D15) Или создавать таблицу, где в одном столбце — нужные слова, в другом — соединители. (Если в параметре указан диапазон — минимум по 2 ячейки в высоту и ширину — функция будет объединять значения по строкам.)
В таком случае только надо оставить последнюю ячейку пустой — иначе в конце созданной строки будет лишний пробел
Как вы понимаете, СЦЕПИТЬ далеко не самая удобная функция. JOIN же позволяет сразу задать единый разделитель для всех строк в массиве/диапазоне: =JOIN(» «;A16:A19) даст такой же результат, как на предыдущем скриншоте — при этом не надо беспокоиться за возможный лишний символ в конце строки.
В JOIN можно спокойно объединять сразу несколько массивов данных, например: =JOIN(«&»;;) А если не указать разделитель, то все будет работать так же, как и у CONCATENATE.
Следующая функция подойдет лучше, если в массиве/диапазоне встречаются пустые строки — ведь в JOIN они создают лишние пробелы. А TEXTJOIN делает все то же самое, только есть дополнительный аргумент, который указывает: включать пустые строки или нет. Синтаксис: =TEXTJOIN(» «;ИСТИНА;A16:B20), где первый параметр передает пробел в качестве разделителя, второй говорит, что нужно игнорировать пустые строки, а третий — дает диапазон значений для соединения.
Здесь все правильно соединяется — ничего лишнего
Кстати, помимо функций, можно использовать такой оператор как ‘&’. К примеру, =»Cyber»&»Marketing» спокойно сработает и выдаст «CyberMarketing». Очень удобная фишка при создании всяких динамических таблиц (дашбордов/конструкторов), где нужно кастомизировать запрос для QUERY.
А подробнее о том, что делать с ключевыми словами, вы сможете узнать с помощью статей, вебинаров, курсов по SEO от CyberMarketing.
СЖПРОБЕЛЫ (TRIM)
Если проблема с пробелами все же есть, ее поможет решить специальная функция — СЖПРОБЕЛЫ (TRIM). Она чистит их в начале и конце текста, а также убирает повторяющиеся символы, например, двойные пробелы.
Синтаксис простой и понятный, аргумент один — строка целиком или ссылка на ячейку: =СЖПРОБЕЛЫ(» Hello, World! «)
Так работает СЖПРОБЕЛЫ
Незаменимая штука при работе с большими таблицами, где сотни ключевых фраз, метатегов или других необработанных данных, собранных из разных источников.
ЛЕВСИМВ (LEFT), ПРАВСИМВ (RIGHT), ПСТР (MID)
Все три функции извлекают из строки один или несколько символов: первая берет их слева, вторая — справа, третья — прямо из середины. Примеры:
- =ЛЕВСИМВ(«Привет, мир!»;6) — тут получится просто «Привет».
- =ПРАВСИМВ(«Привет, мир!»; 4) — здесь от строки останется только «мир!».
- =ПСТР(«Привет, мир!»; 4; 3) — выведет лишь «вет».
С ЛЕВСИМВ и ПРАВСИМВ все должно быть и так понятно (кстати, последним параметром может быть ноль, тогда функция просто вернет пустую строку). А вот ПСТР работает так: вторым аргументом принимает номер символа, с которого нужно начать извлечение подстроки, а третьим — длину извлекаемой подстроки. (Важно: отсчет начинается с 1, а не с нуля.)
Конечно, в работе редко бывает заранее известно, где в какой строке и на сколько символов надо отступить. Как правило, такие функции используют в сочетании с другими, например, ДЛСТР и ПОИСК/НАЙТИ.
SPLIT
SPLIT разделяет текст — по одному или нескольким символам-разделителям — и выводит полученные фрагменты в разные ячейки. Сами символы при этом не возвращает. Простой пример: =SPLIT(A10;»/»)
1. Текст, который нужно разделить на части. Строкой или ссылкой на ячейку.
2. Символ(-ы) для разделения — тоже строкой, в кавычках.
3. Тип разделителя (необязательно). По умолчанию ИСТИНА — функция разделяет текст по каждому символу, который указан во втором параметре. Если поставить ЛОЖЬ, то разделение будет происходить только по всей последовательности символов.
Разница между двумя способами разделения теми же разделителями. Во втором случае вообще нет никакого разделения, так как в URL слеш и точка не встречаются подряд.
4. Тоже необязательный параметр, который определяет, нужно ли удалять пустые ячейки после разделения. По умолчанию ИСТИНА — два последовательных разделителя будут считаться одним. Если ЛОЖЬ, то между этими разделителями будет создана пустая ячейка.
Пожалуй, это не пригодится, SPLIT и так занимает слишком много ячеек
Главный недостаток — множество лишних «технических» ячеек, которые могут мешать, может понадобиться их скрывать. Извлекать отдельные фрагменты текста можно и другими способами.
REGEXMATCH, REGEXEXTRACT, REGEXREPLACE
Все эти три функции работают с регулярными выражениями — специальным языком для гибких манипуляций с текстами. REGEXMATCH ищет совпадения, REGEXEXTRACT извлекает по шаблону нужный фрагмент, а REGEXREPLACE заменяет одну часть текста на другую. Синтаксис похожий: первый аргумент — текст, а второй — само регулярное выражение; в REGEXREPLACE есть еще третий — текст, который нужно вставить.
- =REGEXEXTRACT(«https://www.cybermarketing.ru/video-courses/promo/poiskovoe-prodvizhenie.html»;»[^/]+$») — результатом будет «poiskovoe-prodvizhenie.html».
- =REGEXMATCH(«mail_mail.123@gmail.com»;»^([a-z0-9_-]+\.)*[a-z0-9_-]+@[a-z0-9_-]+(\.[a-z0-9_-]+)*\.[a-z]$») — ИСТИНА, так как в строке корректный адрес электронной почты.
- =REGEXREPLACE(«Google — это один из самых посещаемых сайтов в мире, второй по популярности поисковик Рунета и лидер мобильного трафика. Поэтому нет никаких сомнений, что контекстная реклама Google Adwords — эффективный канал для продвижения бизнеса. Google Адвордс позволит охватить большую аудиторию поиска Google и пользователей мобильных устройств на базе Android.»;»(Adwords)|(Адвордс)|(Эдвордс)»;»Ads») — здесь в тексте будет везде правильно написано название сервиса.
Вообще регулярные выражения — тема для отдельного материала. Сначала кажется, что суперсложно, но когда начинаешь разбираться, становится гораздо легче. Практиковаться можно не только в Google Docs, но и на специальных тренажерах, например, regex101.com. Но имейте в виду, что Таблицы поддерживают не все возможности, которые есть в PCRE2 (PHP) или ECMAScript (JavaScript). Вся информация о синтаксисе RE2 (Google).
UNIQUE, COUNTUNIQUE
Функция UNIQUE берет только те строки, которые ни разу не повторяются, то есть позволяет почистить набор данных от дубликатов. Весьма полезная штука в SEO, PPC, веб-аналитике. Уникальные строки возвращаются в том же порядке, в котором располагаются в диапазоне/массиве.
Синтаксис: =UNIQUE(A2:A100) или =UNIQUE() — тут в столбец выйдут по порядку 5 элементов этого массива, а не 6, потому что два из них неуникальные.
Если нужно не выводить, а только подсчитать количество уников, есть функция COUNTUNIQUE. В нее можно передавать набор из произвольного числа аргументов: значений и диапазонов. Синтаксис: =COUNTUNIQUE(1; 1; 2; 3; 5; 8; 13; A2; B6:B9)
ПОИСК (SEARCH), НАЙТИ (FIND)
Обе функции делают одно и то же: возвращают порядковый номер символа, на котором запрос впервые встречается в тексте. Первым аргументом передаем, что искать, вторым — где искать, третьим — с какого символа искать (поиск идет с начала, учитываются все символы, в том числе пробелы). (В отличие от ЛЕВСИМВ и ПРАВСИМВ, нельзя передать ноль в качестве последнего аргумента.)
Разница лишь в том, что НАЙТИ (FIND) учитывает регистр, а ПОИСК (SEARCH) — нет. Примеры:
- =НАЙТИ(«!»;»Привет, мир!»;1) — результатом будет 12.
- =НАЙТИ(«привет»;»Привет, мир!»;1) — тут получится ошибка #ЗНАЧ, так как функция чувствительна к регистру.
- =ПОИСК(«привет»;»Привет, мир!»;1) — здесь функция сработает нормально, вернется 1.
- =ПОИСК(«,»;»Привет, мир!»;4) — тут на выходе будет 7. (Важно: хотя поиск начинается с 4 символа, функция вернет позицию, где происходит совпадение, именно с начала строки — а не с того символа, с которого начинается поиск.)
- =ПОИСК(«р»;»Привет, мир!»;1) — ответ 2, потому что учитывается только первое совпадение, которое ближе к левой части, началу строки. Если встречается дальше повторно, уже никак не влияет.
Результаты выполнения функции — позиции символов/подстрок — пригодятся, например, для дальнейшей работы с ЛЕВСИМВ, ПРАВСИМВ или ПСТР. Но сами по себе они не понадобятся, если нужно просто проверить: есть в тексте нужное слово или нет. TRUE или FALSE вместо бесполезных цифр поможет получить ЕОШИБКА (ISERROR) (или похожая функция ЕСЛИОШИБКА) в сочетании с функциями IF или IFS (иногда еще нужен NOT). К примеру:
- =IF(ISERROR(SEARCH(«paid»;»https://www.cybermarketing.ru/video-courses/paid/prodvinutyj-kurs-seo.html»;1));»Курс бесплатный»;»Курс платный») — здесь функция вернет именно «Курс платный».
- =IFS(НЕ(ЕОШИБКА(НАЙТИ(«paid»;»https://www.cybermarketing.ru/video-courses/promo/kontekstnaya-reklama.html»)));»Курс платный»;НЕ(ЕОШИБКА(НАЙТИ(«promo»;»https://www.cybermarketing.ru/video-courses/promo/kontekstnaya-reklama.html»)));»Курс бесплатный») — а здесь получится «Курс бесплатный» (для двух вариантов, конечно, слишком сложно, но для большого количества развилок — вполне).
ПОДСТАВИТЬ (SUBSTITUTE)
ПОДСТАВИТЬ (SUBSTITUTE) — текстовая функция Google Таблиц, которая сначала находит в тексте нужный фрагмент, затем заменяет его на другой (как обычно можно отправить строки как есть или ячейки с соответствующими данными). Аргументы:
- Текст, для которого нужны поиск и замена.
- Строка, которую нужно найти и удалить.
- Строка, которую нужно подставить вместо.
- Порядковый номер вхождения строки, которую нужно заменить (необязательно). (По умолчанию все совпадения будут заменяться.)
На злобу дня: =ПОДСТАВИТЬ(«Привет, мир!»;»мир»;»карантин») — «Привет, карантин!». Еще пример: =ПОДСТАВИТЬ(«Привет, мир!»;»р»;»г»;2) —»Привет, миг!» (‘р’ встречается раньше, но специально указано, что брать следует второй символ по счету).
Важно: функция SUBSTITUTE не различает, является строка отдельным словом или частью другого. Замена будет производиться в любом случае. В более сложных кейсах лучше использовать функции REGEX — для работы с языком регулярных выражений.
DETECTLANGUAGE и GOOGLETRANSLATE
DETECTLANGUAGE умеет определять язык текста, а GOOGLETRANSLATE — переводить текст с одного языка на другой.
DETECTLANGUAGE принимает один аргумент — текст или диапазон. (Работает только с одномерным столбцом, при передаче диапазона вида A2:B8 выдаст ошибку — обойти можно только через массив.) Если в диапазоне будут тексты на нескольких языках, функция определит язык лишь первого попавшегося фрагмента.
GOOGLETRANSLATE работает с тремя параметрами (два последних необязательные):
- Текст, который нужно перевести.
- Язык оригинала в таком же формате, каким оперирует DETECTLANGUAGE. (Кстати, можно указать «auto» и он будет определен автоматически — то есть вкладывать одну функцию в другую для этих целей не нужно.)
- Язык перевода — аналогично — двумя буквами. (По умолчанию функция берет язык операционной системы, но это не всегда работает, поэтому лучше указывать отдельно.)
С диапазонами не получится, только с отдельными ячейками/строками.
ТЕКСТ (TEXT) И ЗНАЧЕН (VALUE)
Функция ТЕКСТ (TEXT) преобразует числовой формат в текстовый — это может понадобится, например, для динамических таблиц с использованием QUERY, красивого представления данных в отчетах. Первый аргумент — число, дата и/или время, второй — шаблон для форматирования. (‘?’, ‘*’, а также дробные форматы не поддерживаются.) Примеры:
- =ТЕКСТ(«21/12/2012″;»mmm d yyyy») — здесь получится «дек. 21 2012».
- =ТЕКСТ(«199,99»; «#,###») — тут результатом будет 200.
- =ТЕКСТ(100,789; «$0,00») — округление и представление в долларах — $101.
- =ТЕКСТ(«10000001»; «#,##0») — «10 000 001».
(Все варианты можно посмотреть в справке, а также в разделе «Формат» → «Числа» → «Другие форматы».)
ЗНАЧЕН (VALUE) делает совершенно противоположное — преобразует текст или дату в число. На вход принимает единственный аргумент — строку. Изменение формата может понадобиться, например, когда мы извлекаем данные из текста с помощью REGEXEXTRACT, а потом их нужно использовать для функций суммирования, подсчета среднего арифметического и т. п.
ЕТЕКСТ (ISTEXT)
ЕТЕКСТ (ISTEXT) проверяет, есть в ячейке текст или нет. Если там текстовое значение, возвращает ИСТИНА, если найдет число или пустую ячейку — ЛОЖЬ. Так:
- =ЕТЕКСТ(«123») — TRUE, так как данные, заключенные в кавычки, передаются как строка.
- =ЕТЕКСТ(123) — FALSE, потому что числа передаются без кавычек.
- =ЕТЕКСТ(«») — тоже TRUE, строка пустая, но все же это строка.
Эта функция чаще используется в условных конструкциях (IF, IFS).
СОВПАД (EXACT)
СОВПАД (EXACT) — специальная функция Таблиц для сравнения двух строк: если они полностью совпадают, вернется TRUE, если нет — FALSE. Важны и пробелы, и регистры. К примеру, =СОВПАД(«Cybermarketing»;»CyberMarketing») выдаст ЛОЖЬ, так как по одному символу они отличаются.
Если регистр не важен, гораздо проще использовать обычные операторы, например: =ЕСЛИ(«CyberMarketing»=»Cybermarketing»;»Совпадают»;»Не совпадают») — здесь будет «Совпадают».
ГИПЕРССЫЛКА (HYPERLINK)
ГИПЕРССЫЛКА (HYPERLINK) создает в ячейку гиперссылку. Первый аргумент — полный URL, второй (необязательный) — текст ссылки.
Если протокол не указан, по умолчанию используется «http://». Если текстом ссылки указана пустая строка «», ячейка отображается пустой, но ссылка все равно будет работать.
На практике эта функция нужна редко, только если важно скрыть целый URL за анкором. В остальных случаях Google Таблицы автоматически делают адреса страниц/сайтов кликабельными.
А о том, как правильно работать со ссылками на сайте, вы сможете узнать с помощью статей, вебинаров, курсов по SEO от CyberMarketing.
Как найти похожее значение в гугл таблицах
Использование этих двух встроенных функций гугл таблиц позволяет получать результаты, аналогичные ВПР, но чуточку быстрее. Это особенно актуально в таблицах с большими массивами данных.
MATCH (ПОИСКПОЗ)
Формула возвращает относительное положение значения в диапазоне. То есть MATCH возвращает номер позиции элемента в массиве или диапазоне, а не сам элемент.
Устроена MATCH следующим образом:
=MATCH(ЧТО-ИЩЕМ; ГДЕ-ИЩЕМ; КАК-ИЩЕМ)Допустим, есть таблица со следующими данными:
И необходимо получить номер позиции фамилии «Овечкин»
=MATCH("Овечкин";A1:A9;0)Формула вернет значение 6. Потому что это 6 позиция в выбранном диапазоне. Тут важно не перепутать с номером строки, которая по фамилии «Овечкин» так же шестая.
Поясню на примере. Сдвигаю таблицу ниже:
=MATCH("Овечкин";A2:A10;0)Так же вернет 6, хотя номер строки 7. Так как я сдвинул диапазон с А1 на А2 — относительное положение элемента «Овечкин» в самом диапазоне не изменилось. Однако, если изменить формулу на:
=MATCH("Овечкин";A:A;0)То результат будет 7. Так как диапазон начнется с А1.
INDEX (ИНДЕКС)
ИНДЕКС устроен с точностью наоборот. Если честно, мне не очень нравится стандартное объяснение аргументов, потому я буду использовать «свою и понятную» формулировку:
=INDEX(ГДЕ-ИЩЕМ; НОМЕР-ЭЛЕМЕНТА; НОМЕР-СТОЛБЦА)- ГДЕ-ИЩЕМ аналогично как в ВПР.
- НОМЕР-ЭЛЕМЕНТА — номер элемента в выделенном диапазоне ГДЕ-ИЩЕМ
- НОМЕР-СТОЛБЦА — номер столбца в выделенном диапазоне ГДЕ-ИЩЕМ
Итак, у меня есть все та же таблица
где позиция «Овечкин» в диапазоне А:А — седьмая.
Если я поставлю цифру 7 в ИНДЕКС и выберу диапазон А:B, то формула будет следующая:
=INDEX(A:B;7;2)И результат будет буква «М» — как значение седьмой позиции и второго столбца.
Нетрудно догадаться, что ИНДЕКС и ПОИСКПОЗ лучше всего использовать объединенно:
=INDEX(A:B;MATCH("Овечкин";A:A;0);2)И результат будет все та же буква «М» — как всё то же значение седьмой позиции и второго столбца.
Так в чем же разница с ВПР?
Помимо быстродействия ИНДЕКС и ПОИСКПОЗ в отличие от базового функционала ВПР (который, впрочем обходится в гугл таблицах легко — через ) искать значение можно СЛЕВА от заданного элемента.
Например, найду пол Овечкина по возрасту:
=MATCH(40;C:C)Нетрудно догадаться, что результат будет = 7
А поиск значения «М» будет по первому столбцу и выглядеть следующим образом:
=INDEX(B:C;MATCH(40;C:C);1)Ваше мнение важно и может улучшить блог
Я хочу услышать ваше мнение и ваши идеи о том, как сделать этот сайт еще лучше. Примите участие в опросе, чтобы поделиться вашими пожеланиями, предложениями и замечаниями. Пройдите опрос сейчас и помогите сделать этот сайт более полезным для вас!