Вам приходилось использовать поиск в Таблицах Гугл? Иногда это непросто, особенно в больших таблицах.
Поиск в Таблицах Гугл реализован с помощью следующих инструментов:
Ctrl+F (поиск на листе),
Ctrl+H (поиск и замена на всех листах или в диапазоне),
фильтр данных.
У всех этих инструментов есть свои достоинства и свои недостатки.
Например, Ctrl+F и Ctrl+H выводят только одно значение и показывают его не в каком-то заранее определённом месте, а бегают по листам 🙂
Фильтр скрывает только строки.
Здесь я расскажу, как быстро и просто самому создать инструмент, который производит поиск по заранее заданным параметрам и показывает только то, что нужно.
1. Создание формы поиска
Для начала создадим поисковую форму. Просто введите текст, объедините ячейки C3:D4, сделайте заливку по вкусу.
Подготовьте лист, откуда будут извлекаться данные.
Это тело, теперь займёмся мозгами 🙂
2. Создание механизма поиска
По уже сложившейся традиции (см. Визуализация статусов проектов или Анализ затрат с помощью Google) немного теории.
Напомню, что в этих кейсах рассмотрены формулы ЕСЛИОШИБКА, ИНДЕКС, ПОИСКПОЗ, SPARKLINE, а также проверка данных, условное форматирование.
В ячейку C7 введите формулу
=ВПР(«*» & $C$3 & «*»;’Контрагенты’!$A$1:$I;1;ЛОЖЬ)
WordPress упорно заменяет прямые кавычки на угловые, поэтому посмотрите правильное написание формулы на картинке выше в п.1.
Вот её синтаксис:
ВПР(запрос; диапазон; номер_столбца; [отсортировано])
Функция производит поиск по первому столбцу диапазона и возвращает значение из найденной ячейки.
запрос
Поисковый запрос, т.е. то, что ищем.
диапазон
Диапазон, в первом столбце которого будет производиться поиск по указанному запросу.
номер_столбца
Номер столбца, из которого выводим найденные значения. Значение «1» соответствует первому столбцу.
отсортировано — [необязательный]
Указывает на то, отсортирован ли первый столбец в диапазоне. Если это так, то будет возвращен наиболее подходящий результат.
Рекомендуется использовать ЛОЖЬ. В этом случае возвращается только точное совпадение.
Теперь скопируйте эту формулу в ячейки C8:C10.
Исправьте в этих формулах номера столбцов на нужные вам.
Всё готово, проверяйте!
Используйте этот приём в своих проектах.
Здесь можно посмотреть видео, в котором показан процесс: Поиск в Таблицах Google
Узнать больше о таблицах Гугл вы можете на онлайн-курсе Система управленческого учёта в Таблицах и Формах Google.
32 комментария
BAKTYBEK · 20 мая, 2020 в 11:19 дп
Хотел бы с Вами связаться как это сделать?
Boris · 21 мая, 2020 в 4:13 дп
Написал на почту.
rom · 23 июля, 2020 в 6:05 дп
Добрый день!
Второй день бьюсь с организацией поиска, ни как не получается, вроде и делаю всё как у Вас написано, всё равно постоянная ошибка синтаксиса, не могли бы Вы мне помочь разобраться, то я не так делаю?
Boris · 23 июля, 2020 в 9:23 дп
Здравствуйте! Напишите на почту.
Boris · 23 июля, 2020 в 4:20 пп
Поправил ваши формулы, сейчас всё работает. Вы, скорее всего, вводили адрес вручную, а надо было просто во время ввода выделить нужный диапазон ячеек, и Гугл автоматически бы подставил адрес правильно.
rom · 24 июля, 2020 в 4:43 дп
Огромное спасибо за помощь!
Да, диапазон ячеек прописывал вручную.
Boris · 24 июля, 2020 в 5:41 дп
Пожалуйста. Будут ещё вопросы — пишите.
ТАТЬЯНА · 29 июля, 2020 в 3:50 дп
Хотела бы с Вами связаться как это сделать?
Boris · 29 июля, 2020 в 7:38 дп
Написал на почту.
Ольга · 3 февраля, 2021 в 10:31 дп
Добрый день!
Не понимаю что делаю не так, формула выдает Ошибку. Можете помочь разобраться?
Boris · 3 февраля, 2021 в 12:15 пп
Здравствуйте, Ольга. Какая конкретно ошибка, какие у вас данные?
Удалите из вашей таблицы чувствительные данные, дайте доступ на неё и скиньте ссылку на почту — посмотрю, где ошибка.
Boris · 3 февраля, 2021 в 2:16 пп
Посмотрел вашу таблицу.
Поправил формулу, сейчас всё работает.
Вот так она теперь выглядит:
Ольга · 4 февраля, 2021 в 9:17 дп
Спасибо вам большое.
Boris · 4 февраля, 2021 в 10:01 дп
Пожалуйста, обращайтесь.
Виктор · 4 февраля, 2021 в 9:48 дп
Скажите пожалуйста, как составить такую же формулу, но только для поиска номеров телефонов? Так как данная формула работает для буквенного набора, а с числами почему то не работает. Заранее спасибо.
Boris · 4 февраля, 2021 в 9:57 дп
Здравствуйте, Виктор. На самом деле работает с числами (и с датами тоже). Почитайте тут: https://support.google.com/docs/answer/3093318
Опишите вашу ситуацию: какие данные, какая формула. Лучше пришлите доступ к таблице на почту. Помогу разобраться.
Boris · 4 февраля, 2021 в 10:29 дп
Виктор, посмотрел вашу таблицу. Вы забыли знаки &. Добавьте их и всё работает.
Посмотрите правильную формулу на картинке в п.1.
Виктор · 4 февраля, 2021 в 1:43 пп
Спасибо Вам Борис, что желаете помочь в этом. Знаки & я уже поставил и в самом начале тоже их ставил, но всё равно не работает. Странно, что справа такая же простая формула без знаков & отлично работает с буквенным текстом. Если что, можете попробовать сами, по той же ссылке.
Boris · 4 февраля, 2021 в 1:55 пп
Я поправил вашу таблицу, всё работает.
Виктор · 4 февраля, 2021 в 2:12 пп
Большое Вам спасибо! Всё действительно работает)))
Очень интересно в чём была ошибка?
Boris · 4 февраля, 2021 в 2:51 пп
Пожалуйста. Проблема была в формате чисел в столбце A. У вас было применено форматирование Число с разделением разрядов и 0 знаков после запятой. Отображались числа как 123 456, 456 789, т.е. с разделителем разрядов. Я вернул Автоматический формат, он установлен для ячеек по умолчанию.
Boris · 4 февраля, 2021 в 10:03 дп
Как задать вопрос, чтобы получить быструю помощь:
https://support.google.com/docs/thread/3808684?hl=ru
Марина · 21 мая, 2021 в 7:39 дп
Добрый день. Подскажите пожалуйста в чем причина. Нажимаю ctrl+f ,ввожу слово которое точно есть на листе, но он его не находит, ввожу одну букву — находит их все. Т.е. ищет только по одному символу
Boris · 21 мая, 2021 в 7:51 дп
Здравствуйте, Марина. Возможно, то, что вводите, отличается от того, что есть в таблице. Попробуйте скопировать из таблицы и вставить в поисковое поле CTRL+F.
Алия · 14 февраля, 2022 в 9:06 дп
Добрый день! В таблице создаю два раскрывающихся списка, значения во втором зависят от выбранного значения в первом. Прописываю в Проверке данных-Список-Формула Фильтр, но при раскрывании показывает формулу, а не необходимый список. Подскажите, пожалуйста, что делаю не так?
Оксана · 13 декабря, 2022 в 10:02 пп
Здравствуйте! Попробовала данную формулу для поиска бренда из столбца с данными, не корректно выдает запрос, не пойму как еще по другому можно сделать, чтоб отображало позицию правильно.
Boris · 14 декабря, 2022 в 9:02 дп
Здравствуйте. В чём именно проблема?
Оксана · 14 декабря, 2022 в 5:05 пп
Здравствуйте! У меня столбец с наименованием бренда и товара, моя задача найти из этого массива данных определенный бренд и сколько раз он повторяется с каким видом товара, мне нужно сделать это одной формулой. Я использовала функцию ВПР , но результат получился не такой как должен получиться, думаю нужна другая функция поиска.
Ссылка на таблицу, задание 3, если есть минуточка взгляните пожалуйста, что я не так сделала, ответ мой должен быть таким же как рядом в задание под «ответ»
https://docs.google.com/spreadsheets/d/1wUcFnEliKJ8nT0CwuD3_pdo2iPowL1_aKXm-gyz32o0/edit?usp=sharing
Владимир · 1 мая, 2023 в 11:21 дп
Добрый день,подскажите пожалуйста как создать кнопку поиска на всем листе гугл таблиц?нажал кнопку и выходит строка где пишешь слово и таблица ищет его,срочно надо
Boris · 1 мая, 2023 в 3:55 пп
Здравствуйте.
Ctrl+f / Ctrl+h
Снежана · 26 февраля, 2024 в 10:52 дп
https://docs.google.com/spreadsheets/d/1PAv843FycWN-Mt2k1J3VaIvwhM99DfAPhhQP5XFaX8/edit?usp=sharing есть такая страница. нужно реализовать поиск по ряду «название команды», а так же фильтрацию от большего к меньшему и наоборот для ряда «Рейтинг». расположение эллементов сделать +- как в примере. Подскажите, сможете помочь с этим?
Если возможно напишите мне в телеграм
Boris Baublys · 26 февраля, 2024 в 11:09 дп
Ответил в телегу.