Вам приходилось использовать поиск в Таблицах Гугл? Иногда это непросто, особенно в больших таблицах.

Поиск в Таблицах Гугл реализован с помощью следующих инструментов:
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.


25 комментариев

BAKTYBEK · 20 мая, 2020 в 11:19 дп

Хотел бы с Вами связаться как это сделать?

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 дп

Добрый день! В таблице создаю два раскрывающихся списка, значения во втором зависят от выбранного значения в первом. Прописываю в Проверке данных-Список-Формула Фильтр, но при раскрывании показывает формулу, а не необходимый список. Подскажите, пожалуйста, что делаю не так?

Добавить комментарий

Avatar placeholder

Ваш адрес email не будет опубликован.

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.