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

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


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

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

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

Оксана · 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

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

Avatar placeholder

Ваш адрес email не будет опубликован. Обязательные поля помечены *

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