Как работает поиск в Гугл Таблицах?
BY
Где находится поиск в Гугл Таблицах и как им пользоваться? Рассказываем об одном из самых простых и понятных инструментов, который доступен каждому пользователю. Согласитесь, сложно рассчитывать на полноценное погружение в работу/учебу, если вы не можете отыскать нужное слово?
Как искать?
«Где поиск в Гугл Таблице», – вот вопрос, которым постоянно задаются пользователи (особенно новички). Привычной поисковой строки на верхней панели нет. Как же можно находить нужные фрагменты текста, цифры? Что делать?
Для поиска в Гугл Таблице есть сочетание клавиш – это еще проще, чем искать соответствующие настройки в меню!
Если вы хотите просто найти нужное слово, можно воспользоваться таким сочетанием:
- На Windows: Ctrl+F;
- На macOS: Command+F.
Если вы одновременно нажмете на эти клавиши, откроется поисковая строка. Просто вписывайте свой запрос и получайте релевантные результаты.
Вот как открыть поиск в Гугл Таблице. Никаких специальных настроек, вкладок меню… Просто щелкните по паре клавиш. Поисковая строка отобразится в верхнем правом углу, вы сразу ее увидите.
Замена слов
Как включить поиск в Гугл Таблице, вы уже знаете. Что делать, если вы хотите не только отыскать несколько слов, но и заменить их? Допустим, в процессе составления документа вы много раз упомянули число «26», но только потом поняли, что должны были написать «28».
Искать и вручную переписывать каждую цифру (как и букву), не хочется! Поэтому необходимо узнать, как в Гугл Таблице найти слово и сразу поменять его на другое.
- Находим на верхней панели вкладку «Правка» ;
- Нажимаем и ищем в открывшемся меню раздел «Найти и заменить» ;
- Перед вами появится окно, куда нужно ввести данные.
Теперь немного поработаем над настройками! Вспоминайте, что нужно отредактировать и приступайте:
- В первом поле пишем слово, которое хотим найти;
- Во вторую строку вводим текст на замену.
Нужно понимать, как это работает. Если вы знаете, что опечатались и везде написали «карова», система отыщет все совпадения и заменит их на «корова».
Продолжаем? В разделе «Поиск» вы можете выбрать диапазон. Это область, относительно которой будут применены изменения:
- Все листы;
- Этот лист;
- Только выделенный диапазон.
Поиск по Гугл Таблице может быть простым, когда вы ищете конкретное точное совпадение. А можно выставить более тонкие настройки, персонализировать выдачу.
Все опции, о которых сейчас пойдет речь, можно активировать, поставив галочку в соответствующем окне. Если вы не хотите ими пользоваться, снимите все отметки.
- Учитывать регистр. Будет учитываться написание строчных/прописных букв;
- Совпадение с полным содержанием ячейки. Будут показаны только те ячейки, текст в которых на 100% соответствует запросу.
- Использование регулярных выражений. Чтобы найти данные, используются специальные шаблоны.
- По формулам. Можно найти конкретные формулы в документе.
Разумеется, через окно замены вы можете просто найти нужный фрагмент текста. Впишите слово в поле «Найти» , не обязательно менять его на что-то другое.
А теперь о том, как сделать поиск в Гугл Таблицах и заменить найденное слово через горячие клавиши. Очень удобный инструмент, вам не придется искать нужные настройки, можно открыть окно «Заменить» одним кликом.
- На Windows: Ctrl+H;
- На macOS: Command+Shift+H.
Ну а если вы не запомните горячие клавиши, всегда сможете найти параметры в меню.
Полностью изучили вопрос, как искать в Гугл Таблице – вариантов несколько, можете пользоваться одним или комбинировать разные способы. Как видите, сервис разработан с учетом всех пожеланий пользователей и обладает широчайшим функционалом для комфортной работы.
Как реализовать поиск в Google Таблице?
Возможно кто-то уже организовывал подобное или есть мысли как это сделать. Есть Гугл Таблица, в ней два листа( оба листа идентичны, один активный, а другой создан для архивирования данных с первого). Первый лист наполняется сотрудниками — в столбец B вносится позиция товара одним менеджером, в столбцы С, D, E его характеристики другим менеджером. Нужно реализовать поиск по столбцу B, то есть когда первый менеджер начинает добавлять в столбец B какую либо позицию «поиск» должен проверять все строки находящиеся выше по столбцу B + столбец B во втором листе (который архив) и если есть совпадения показывать менеджеру этот вариант (важно! с характеристиками из столбцов С, D, E). В идеале поиск должен работать с использованием регулярных выражений, то есть позиция не смотря на пробелы, дефисы, перемену мест слов в наименовании должна быть найдена.
Почему функция ВПР (VLOOKUP) не работает в Google таблицах?
Рассматривая синтаксис функции ВПР (VLOOKUP), мы уже отмечали, что в случае, если поиск завершится неудачей, функция возвратит ошибку “#Н/Д” (#N/A). Давайте постараемся вместе попробовать ответить на вопрос: «Почему функция ВПР не работает?»
Также мы постараемся показать несколько несложных приёмов, которые позволят подстраховать себя от случайной ошибки с функцией ВПР.
Если функция ВПР не сработала так, как нам нужно, и мы видим сообщение об ошибке, то это ещё не значит, что мы сделали что-то неправильно.
Почему не работает? Разбираем наиболее типичные ошибки при использовании функции ВПР в Google таблицах.
В первую очередь, сообщение об ошибке вы увидите, если значение, которое вы ищете, действительно отсутствует в указанном диапазоне поиска. Здесь мы бессильны. Все остальные случаи, когда ВПР не работает, связаны с тем, что мы что-то не учли или сделали неверно.
Итак, разберём наиболее распространённые ошибки.
Ошибка при вводе данных
В этом случае ничего найти не удастся и вы увидите ошибку #Н/Д. Самое простое решение здесь очень часто оказывается самым верным. Быть может, вы просто ошиблись при вводе данных в ячейку поиска. Это вполне может случиться, в особенности если вы вводите значение поиска не в ячейку, а прямо в формулу.
Проверьте, правильно ли введены данные.
Ошибка при вводе функции.
Если вы видите сообщение об ошибке #ИМЯ? (#NAME?), то это означает, что при вводе названия самой функции ВПР вы допустили ошибку – перепутали или добавили лишнюю букву.
Проверьте синтаксис написания функции, и всё бедет в порядке.
Неверные ссылки в функции ВПР
Проверьте, правильно ли вы указали диапазон поиска а также ячейку со значением, которое мы будем искать.
К примеру, мы ищем цену бананов.
Но в функции вы указали обычные (относительные) ссылки, забыв заменить их на абсолютные ($), и затем произвели какие-то действия с таблицей. К примеру, добавили столбец. В результате ваш диапазон поиска автоматически изменился и стал шире на 1 столбец.
Ваши ссылки в формуле теперь будут ссылаться на неверные ячейки. Цена у вас находится теперь в 3-м столбце, а вы берёте данные из второго. В результате ВПР не работает.
Важно! При изменении ссылок сообщение об ошибке часто не появляется. Поэтому будьте внимательны в использовании относительных и абсолютных ссылок!
Неверно указан параметр “сортировка” (is_sorted).
Включен поиск до первого приблизительного совпадения в отсортированном диапазоне (параметр is_sorted = TRUE), но на самом деле данные не отсортированы.
Об этой ошибке мы подробно говорили, когда рассматривали правила использования функции ВПР в Google таблицах.
Столбец поиска не является первым слева столбцом диапазона поиска.
Часто забывают о том, что функция ВПР (VLOOKUP) ищет совпадающие значения только в первом слева столбце диапазона, который вы указали.
Если ваши значения поиска находятся не в первом, а во втором или другом столбце, то функция ВПР не будет работать и вы вновь увидите сообщение #Н/Д. К примеру, вы ищете цену по артикулу товара, но в первом столбце прайс-листа у вас находятся наименования.
Это важное ограничение функции ВПР нельзя забывать.
Несовпадение форматов данных.
Формат ячейки, откуда берется искомое значение наименования (например D3 в нашем случае), и формат ячеек первого столбца (A3:A21) из диапазона поиска отличаются (например, числовой и текстовый). Этот случай особенно часто встречается при использовании вместо текстовых наименований числовых кодов (номера счетов, артикулы, идентификаторы, даты и т.п.)
Итак, здесь мы пытаемся найти текстовое значение среди чисел. Закономерно функция ВПР возвращает нам ошибку.
Как видите, с виду записи одинаковы, однако в ячейке D6 значение сохранено как число, а в ячейке A10 – как текст. Текст не может быть равен числу, поэтому функция возвращает ошибку.
Как решить эту проблему со случайной ошибкой в формате записи? Можно использовать функцию ТЕКСТ, которая преобразует любые значения в текстовый вид.
Теперь функция ВПР нашей Google таблицы выглядит так:
Обычную ссылку на ячейку D6 мы заменяем функцией
Первый аргумент – это сама ячейка, а второй аргумент означает формат, в который мы будем преобразовывать значение этой ячейки. В данном случае – в текстовый.
Как видите, использование вложенной функции помогло решить проблему с несовпадением форматов данных.
Если же такая ошибка только одна, то можно просто исправить формат данных в ячейке. Нажмите Меню -> Формат-> Число-> Обычный текст. Значение в ячейке будет преобразовано в текст.
Как видите, ошибка исчезла, поскольку теперь текстовое значение сравнивается с таким же текстовым значением.
Лишние пробелы и непечатаемые знаки.
Функция не может найти нужного значения, потому что в коде присутствуют пробелы или невидимые непечатаемые знаки (перенос строки и т.п.).
В нашем примере функция ВПР возвратила ошибку, так как в ячейке D4 при вводе данных случайно были добавлены два пробела после значения. И, поскольку сравниваются символьные значения, поиск, естественно, завершился неудачно.
Такая ошибка может встеречаться довольно часто, и обнаружить её визуально практически невозможно.
К примеру, если наименование состоит из 2 слов, то вполне возможно случайное появление лишнего пробела между этими словами.
Могут быть также случайно добавлены символы, невидимые на экране и на печати (табуляция, перевод строки и т.д.). Визуально определить такие ошибки практически невозможно.
В этом случае можно использовать текстовые функции СЖПРОБЕЛЫ (TRIM) и ПЕЧСИМВ (CLEAN) для удаления лишних пробелов либо других невидимых символов. Вместо
Функция СЖПРОБЕЛЫ (TRIM) удаляет пробелы, а функция ПЕЧСИМВ (CLEAN) удаляет все непечатаемые и невидимые символы из текстового значения.
Эту комбинацию функций рекомендуем применять всегда «на всякий случай», чтобы подстраховать себя от случайных ошибок.
Неправильно указан номер столбца.
Иногда неправильно указывают номер столбца, значение из которого вы хотите получить (третий аргумент функции ВПР).
Номер столбца не может быть меньше 1 и не может быть больше, чем количество столбцов в указанном для поиска диапазоне. Если номер столбца указан неверно, то ВПР возвращает ошибку #VALUE!
Когда вы видите такую ошибку, пересчитайте количество столбцов в диапазоне, который вы указали в функции, и убедитесь, что это значение не меньше, чем номер столбца, указанный третьим аргументом функции ВПР.
И в любом случае – номер столбца должен быть больше 1. Вряд ли кто-то из вас будет руками вводить значение 0 или -1, но если вы получаете номер столбца при помощи каких-то функций, вложенных в ВПР, то здесь-то и может быть ошибка.
Ошибка в ссылке на данные из другой таблицы
Мы с вами говорили о том, что диапазон поиска может быть расположен в другой таблице или даже в другом файле Google таблиц.
Если вы случайно допустили ошибку при указании ссылки на эти данные, то увидите сообщение об ошибке #ССЫЛКА! (#REF!)
Это будет означать, что вы ссылаетесь на неверный диапазон, который программа найти не может.
После изменения таблицы функция перестала работать
Вы сделали все правильно и до тех пор, пока вы не вставили несколько строк или стрлбцов в вашу таблицу, функция работала верно.
Теперь же появилась ошибка #Н/Д. В чем дело?
А дело всё в том, что вы не использовали в функции ВПР абсолютные ссылки, не добавляли к адресам ячеек, строк и столбцов знак $. В результате после добавления (или удаления) строк или столбцов ваши ссылки изменились и формула перестала работать.
Постарайтесь отменить слеланные изменения, благо Google таблицы хранят всю историю изменений вашей таблицы.
Важно! Всегда используйте абсолютные ссылки в формуле, где это необходимо, чтобы застраховать себя от подобных неожиданных изменений.
Как убрать сообщение об ошибке?
Сообщения об ошибке функции ВПР (VLOOKUP) не только показывают, что функция ВПР не работает, но и еще совсем не украшают вашу таблицу. Поэтому, видя сообщение об ошибке, вы, конечно же, захотите от них избавиться.
Но прежде чем приступать к таким радикальным мерам, всё же проверьте – а всё ли вы сделали правильно?
Итак, что нужно сделать прежде всего, увидев сообщение “#Н/Д”.
Во-первых, проверьте адрес диапазона поиска. Действительно ли то значение, которое вы ищете, может находиться в первом столбце диапазона.
Во-вторых, проверьте правильно ли вы указали тип параметра “сортировка” (is_sorted): ИСТИНА или ЛОЖЬ.
В-третьих, проверьте возможные несовпадения форматов, наличие лишних пробелов в условии поиска. Используйте рекомендации, которые мы давали выше.
Если вы все сделали правильно, но диапазоне поиска всё же нет нужного значения, дополните формулу функцией ЕСЛИОШИБКА (IFERROR). Например, так:
Синтаксис функции ЕСЛИОШИБКА очень простой. Первый аргумент – это выражение, значение которого мы проверяем на возниконвение ошибки.
Если ошибки нет, товозвращается значение этого выражения. Если же возникает ошибка, то функция возвращает значение второго аргумента. В данном случае в ячейке F9 будет указано “Не найдено”.
Согласитесь, это гораздо более красиво и информативно, чем стандартное сообщение об ошибке.
Как искать в Google Таблицах (3 простых способа)
С помощью таблиц Google вы можете хранить, поддерживать и обрабатывать средние и даже большие объемы данных. Однако чем больше объем данных, тем труднее найти то, что вам нужно. Представьте, что вам нужно просматривать ячейку за ячейкой данных, чтобы найти, скажем, фамилию конкретного клиента! Это все равно что найти иголку в стоге сена!
К счастью, в Google Таблицах есть функция поиска и замены, которая позволяет выполнять поиск по всем книгам.
В этом руководстве мы продемонстрируем на примере, как выполнять поиск в ваших таблицах Google с помощью функции поиска и замены. Мы также объясним, какие варианты поиска и замены доступны, чтобы помочь вам еще точнее настроить поиск, чтобы вы получили именно то, что ищете.
В конце мы покажем вам, как использовать условное форматирование в Google Таблицах, чтобы найти и выделить все совпадающие ячейки в диапазоне, чтобы вы могли быстро идентифицировать их и работать с ними.
Поиск с использованием параметра поиска для выделения всех совпадающих ячеек на листе
Если у вас есть ячейки с текстом, и вы хотите быстро найти ячейки, содержащие определенную текстовую строку, вы можете сделать это с помощью параметра поиска в Google Таблицах.
Обратите внимание, что это часть последнего варианта замены, который мы рассмотрим в следующем разделе. используйте эту опцию, если вы хотите быстро выполнить поиск по всему листу и выделить ячейки, в которых есть соответствующая строка.
Предположим, у вас есть набор данных, как показано ниже, и вы хотите узнать все ячейки, в которых есть имя «Отметить»:
Ниже приведены шаги по поиску в Google Таблицах с помощью функции поиска:
- Откройте рабочий лист с данными
- Используйте сочетание клавиш Control + F (для Windows) и Cmd + F (для Mac). Это откроет небольшое поле поиска в верхней правой части вашего листа.
- Введите строку, которую вы хотите найти на всем листе
Вышеупомянутые шаги выделят все ячейки с соответствующей текстовой строкой.
Если вы хотите пройти по каждой ячейке одну за другой, вы можете использовать стрелки, указывающие вниз и вверх, в поле «Найти».
Вы также можете визуально просмотреть результат, так как все совпадающие ячейки выделены зеленым цветом.
Это действительно быстрый метод, и вы можете использовать его, чтобы узнать, есть ли какие-либо ячейки, соответствующие текстовой строке, и пройти их по одной.
Если вам нужно немного больше контроля над тем, как вы можете искать данные в Google Таблицах, вы можете использовать полнофункциональную функциональность диалогового окна поиска и замены.
Поиск с помощью функции поиска и замены
Предположим, у нас есть следующие данные на одном листе:
Показанный набор данных очень прост, и вы, очевидно, можете найти то, что вам нужно, просто взглянув на него. Однако мы сделали его простым, чтобы упростить понимание.
Теперь предположим, что мы хотим найти ячейки, содержащие имя «Пол». Вот шаги, которые нужно выполнить, чтобы их найти:
- Щелкните меню «Правка» в строке меню.
- Выберите вариант «Найти и заменить».
- Откроется диалоговое окно «Найти и заменить». Кроме того, вы можете открыть это окно напрямую, нажав CTRL + H (если вы на ПК) или Cmd + H (если вы на Mac).
- В поле ввода рядом с меткой «Найти» введите слово, которое вы хотите найти (в нашем примере мы можем ввести слово «Пол», поскольку это то, что мы ищем).
- Щелкните «Найти».
- Это выберет первую ячейку, содержащую слово.
- Если на листе есть несколько экземпляров слова, нажатие на «Найти» каждый раз выбирает следующую ячейку, содержащую слово.
- Когда вы дойдете до последнего вхождения слова, вы увидите сообщение «Больше результатов не найдено. Зацикливание »в нижней части диалогового окна.
- Если вы нажмете кнопку «Найти» после того, как увидите это сообщение, функция «Найти и заменить» вернется к первому вхождению слова.
- Когда вы закончите поиск (и / или замену), нажмите кнопку «Готово», чтобы закрыть диалоговое окно «Найти и заменить».
Примечание. После шага 6, если вы хотите заменить слово чем-то другим, вы можете ввести новое слово рядом с меткой «Заменить на». В этом случае вам нужно будет нажать кнопку «Заменить» (если вы хотите вручную заменять слово по одному) или кнопку «Заменить все» (если вы хотите заменить все вхождения слова сразу).
Также обратите внимание, что вы можете использовать диалоговое окно «Найти и заменить», чтобы найти то, что вам нужно, на текущем листе, на всех листах или даже в выбранном диапазоне ячеек.
Просто выберите раскрывающийся список со стрелкой рядом с надписью «Поиск» и выберите нужный вариант:
Параметры поиска и замены
Если вы посмотрите на диалоговое окно «Найти и заменить», то обнаружите, что есть много других параметров, помимо поиска, замены и поиска.
Каждый из этих вариантов может помочь вам в дальнейшей настройке поиска, чтобы вы могли найти именно то, что вам нужно.
Давайте посмотрим на некоторые из этих вариантов:
- Учитывать регистр: выбор этого параметра позволяет сделать поиск чувствительным к регистру. Поэтому, если у вас есть ячейка, содержащая «paul» (со строчной буквой «p»), поиск будет игнорировать ячейку.
- Соответствовать содержимому всей ячейки: выбор этого параметра позволяет искать ячейки, которые точно соответствуют поисковому слову. Например, если этот флажок установлен и ваше слово для поиска — «Пол Родригес», то при поиске в качестве совпадения учитывается только ячейка, содержащая точно полное имя. Однако, если искомым словом является просто «Пол», то при поиске полное имя игнорируется, так как оно не является точным совпадением.
- Поиск с использованием регулярных выражений: этот параметр используется, если у вас есть регулярное выражение в поле «Найти». Регулярное выражение — это своего рода строка, содержащая определенный шаблон. Если содержимое ячейки соответствует шаблону, это считается «совпадением». Дальнейшее объяснение регулярных выражений выходит за рамки этого руководства, но если вы хотите узнать о нем больше, вы можете щелкнуть ссылку «Справка» рядом с опцией:
- Также поиск в формулах: как правило, функция «Найти и заменить» просто выполняет поиск по содержимому ячеек и результатам формул. Если вы также хотите увидеть, содержится ли ваше слово для поиска в фактической формуле ячеек, вам необходимо проверить эту опцию.
Поиск с использованием условного форматирования (для поиска и выделения ячеек с помощью строки поиска)
Вышеупомянутый метод позволяет находить ячейки, содержащие строку поиска, и выбирать их одну за другой.
Вместо этого, если вы хотите найти и выделить все ячейки, содержащие поисковое слово, вы можете использовать функцию условного форматирования Google Таблиц.
Итак, для того же набора данных, если вы хотите найти все ячейки, содержащие имя «Пол», выполните следующие действия:
- Щелкните меню Формат в строке меню.
- Выберите параметр «Условное форматирование».
- В правой части окна откроется боковая панель «Правила условного формата».
- В поле ввода под «Применить к диапазону» введите диапазон ячеек, в котором нужно выполнить поиск, или просто выберите диапазон ячеек. В нашем примере мы можем ввести A2: B11.
- При выборе диапазона ячеек открывается диалоговое окно, в котором отображается выбранный диапазон. Когда вы закончите, вы можете нажать ОК. Если вы предпочитаете вводить диапазон вручную, пропустите этот шаг.
- Затем в разделе «Правила форматирования» в разделе «Форматировать ячейки, если» щелкните стрелку раскрывающегося списка.
- В появившемся раскрывающемся списке выберите параметр «Текст содержит».
- Вы увидите поле ввода под раскрывающимся списком. Введите здесь слово для поиска. Если вы ищете ячейки, содержащие слово «Пол», введите слово «Пол».
- В разделе «Стиль форматирования» нажмите кнопку «Цвет заливки» ().
- Выберите цвет, который хотите использовать, чтобы выделить совпадающие ячейки / строки. Мы выбрали «желтый».
- Наконец, нажмите кнопку «Готово», чтобы условное форматирование сделало свою работу.
Вы должны увидеть все ячейки, содержащие слово «Paul» в выбранном диапазоне, выделенные желтым цветом.
Другие параметры поиска в условном форматировании
Функция правил условного формата предоставляет вам несколько вариантов, чтобы вы могли найти то, что вам нужно.
Если вы щелкните стрелку раскрывающегося списка под «Форматировать ячейки, если» (в разделе «Правила форматирования»), вы найдете некоторые из следующих параметров:
- Текст содержит : для поиска ячеек, которые содержат искомое слово как часть своего содержимого.
- Текст не содержит : найти ячейки, в которых нет искомого слова.
- Текст начинается с : для поиска ячеек, которые начинаются с искомого слова.
- Текст заканчивается на : найти ячейки, заканчивающиеся искомым словом.
- Текст точно : найти ячейки, содержащие точно и только искомое слово.
Существует ряд других таких опций, в том числе возможность предоставить вашу собственную формулу. Вы можете воспользоваться этими опциями, чтобы уточнить поиск и выделить то, что вам нужно. Таким образом, вы можете быстро просмотреть лист, найти нужные ячейки и внести необходимые изменения.
В этом руководстве мы рассмотрели два метода, с помощью которых вы можете выполнять поиск в Google Таблицах и находить определенные ячейки с соответствующими данными.
Первый метод использует функцию «Найти и заменить», которая является наиболее часто используемой функцией поиска в Google Таблицах.
Второй метод использует функцию условного форматирования для выделения совпадающих ячеек в заданном диапазоне.
Хотя этот метод не очень часто используется для поиска, вы можете использовать его для выделения жизненно важных ячеек на ваших таблицах и выполнения аналитических исследований ваших данных.