Note: The other languages of the website are Google-translated. Back to English

Як створити власне вікно пошуку в Excel?

За винятком використання функції пошуку в Excel, насправді ви можете створити власне поле пошуку для пошуку необхідних значень. У цій статті детально показано два способи створення власного вікна пошуку в Excel.

Створіть своє власне вікно пошуку за допомогою умовного форматування, щоб виділити всі результати пошуку
Створіть своє власне вікно пошуку з формулами, щоб перерахувати всі результати пошуку


Створіть своє власне вікно пошуку за допомогою умовного форматування, щоб виділити всі результати пошуку

Ви можете зробити наступне, щоб створити власне поле пошуку за допомогою функції умовного форматування в Excel.

1. Виберіть діапазон із даними, які потрібно шукати, у вікні пошуку, а потім натисніть Умовне форматування > Нове правило під Головна вкладку. Дивіться знімок екрана:

2 В Нове правило форматування у діалоговому вікні потрібно:

2.1) Виберіть За допомогою формули визначте, які клітинки потрібно форматувати опція в Виберіть тип правила коробка;

2.2) Введіть формулу = ІЗНОМ (ПОШУК ($ B $ 2, A5)) в Форматувати значення, де ця формула відповідає дійсності коробка;

2.3) Клацніть на сформований кнопка, щоб вказати виділений колір для шуканого значення;

2.4) Клацніть на OK кнопки.

примітки:

1. У формулі $ B $ 2 - це порожня комірка, яку потрібно використовувати як вікно пошуку, а A5 - перша комірка у вибраному діапазоні, яка вам потрібна для пошуку значень усередині. Будь ласка, змініть їх, як вам потрібно.

2. Формула не враховує регістр.

Тепер створюється вікно пошуку, коли ви вводите критерії пошуку у вікно пошуку B2 та натискаєте клавішу Enter, всі відповідні значення у зазначеному діапазоні відшукуються та виділяються негайно, як показано на знімку екрана.


Створіть своє власне вікно пошуку з формулами, щоб перерахувати всі результати пошуку

Припустимо, у вас є список даних, що знаходиться в діапазоні E4: E23, який вам потрібно здійснити для пошуку, якщо ви хочете перерахувати всі співпадаючі значення в іншому стовпці після пошуку за допомогою власного вікна пошуку, ви можете спробувати описаний нижче спосіб.

1. Виділіть порожню комірку, яка прилягає до комірки E4, тут я вибираю комірку D4, а потім вводжу формулу = IFERROR (ПОШУК ($ B $ 2, E4) + РЯД () / 100000, "") у рядок формул, а потім натисніть вводити ключ. Дивіться знімок екрана:

примітки: У формулі $ B $ 2 - це клітинка, яку потрібно використовувати для пошуку як вікно пошуку, E4 - перша комірка у списку даних, яку потрібно шукати. Ви можете змінювати їх, як вам потрібно.

2. Продовжуйте обирати комірку E4, а потім перетягніть ручку заповнення вниз до комірки D23. Дивіться знімок екрана:

3. Тепер виберіть клітинку C4, введіть формулу = IFERROR (RANK (D4, $ D $ 4: $ D $ 23,1), "") в панель формул та натисніть вводити ключ. Виділіть клітинку C4, а потім перетягніть маркер заповнення вниз до C23. Дивіться знімок екрана:

4. Тепер потрібно заповнити діапазон A4: A23 номером серії, який збільшується на 1 з 1 до 20, як показано на знімку екрана нижче:

5. Виберіть порожню клітинку, яка вам потрібна для відображення результату пошуку, введіть формулу = IFERROR (VLOOKUP (A4, $ C $ 4: $ E $ 23,3, FALSE), "") в панель формул і натисніть вводити ключ. Продовжуйте обирати комірку B4, перетягніть ручку заповнення вниз до B23, як показано на знімку екрана нижче.

Відтепер, при введенні даних у вікно пошуку B2, усі співпадаючі значення будуть перераховані в діапазоні B4: B23, як показано нижче.

примітки: цей метод не враховує регістр.


Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

  • Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Понад 300 потужних функцій. Підтримує Office / Excel 2007-2019 та 365. Підтримує всі мови. Простота розгортання на вашому підприємстві чи в організації. Повна функція 30-денної безкоштовної пробної версії. 60-денна гарантія повернення грошей.
вкладка kte 201905

Вкладка Office забезпечує інтерфейс з вкладками для Office і значно спрощує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Коментарі (28)
Поки немає оцінок. Оцініть першим!
Цей коментар був мінімізований модератором на сайті
Дуже дякую за допомогу. Це так легко і просто створити.
Цей коментар був мінімізований модератором на сайті
мої дані використовуються членами офісу.тому я хочу захистити втрату даних паролем.якщо я спробую ввімкнути панель пошуку пароля, також не працює .що я можу зробити, щоб захистити дані та зробити їх доступними для пошуку
Цей коментар був мінімізований модератором на сайті
Хороший день,
Перш ніж захистити аркуш паролем, відформатуйте клітинку, яку потрібно вказати як поле пошуку, як розблоковано. Тоді ваші дані будуть захищені, але доступні для пошуку.
Цей коментар був мінімізований модератором на сайті
як додати інший робочий аркуш як область для пошуку даних
Цей коментар був мінімізований модератором на сайті
Хороший день,
Для пошуку даних на іншому аркуші (наприклад, на аркуші 5), будь ласка, змініть формулу в методі умовного форматування на =(SEARCH(Sheet5!$B$2,A5)).
На жаль, не вдається вирішити проблему за допомогою вищезгаданого другого способу.
Цей коментар був мінімізований модератором на сайті
Якщо у вас є дані з кількох аркушів, ви можете зібрати всі ці дані в один аркуш, щоб зробити все менш складним. Наприклад, у Аркуші 1, Аркуші 2 і Аркуші 3 є дані, які ви хочете "шукати", на "Аркуші 4" ви можете в стовпці А на Аркуші 4 сказати "=If(Sheet1!A1="","",Sheet1!A1) і перетягнути цю формулу вниз, скільки рядків ви бажаєте заповнити (скажімо, 40 рядків вниз). Однак у стовпці A на аркуші 4 ви вводите в клітинку A41 "=if(Sheet2!A1="","",Sheet2!A1) , тощо і перетягніть це вниз, повторюйте, доки все не буде завершено. Потім скористайтеся наведеною вище формулою, і вона шукатиме всі аркуші.
Цей коментар був мінімізований модератором на сайті
як я можу додати робочий аркуш як іншу область пошуку?
Цей коментар був мінімізований модератором на сайті
Що робити, якщо дані будуть продовжувати збільшуватися, нові дані вводяться щодня? Тому я хочу, щоб пошук не лише шукав інформацію в конкретних стовпцях на даний момент, але й усі нові рядки, які будуть додані до цих стовпців пізніше.
Цей коментар був мінімізований модератором на сайті
Привіт Алісія.
На жаль, не можу допомогти з цим. Вам потрібно вручну оновити посилання на формули після додавання нових рядків до стовпців.
Дякуємо за ваші коментарі.
Цей коментар був мінімізований модератором на сайті
я теж... Чи можете ви запропонувати якусь допомогу?
Цей коментар був мінімізований модератором на сайті
Ви можете вибрати діапазон аж до самого низу, щоб під час введення нових даних вони враховувалися під час пошуку.

Наприклад: =$A$3:$U$1048576 (незважаючи на те, що мої фактичні дані введені до рядка 74)
Цей коментар був мінімізований модератором на сайті
^Так, інший спосіб - створити таблицю. 
Ви можете перевірити у «Керувати правилами» > «Застосовується до» Правило умовного форматування автоматично розширюється, коли нові дані додаються до таблиці.
Цей коментар був мінімізований модератором на сайті
Після введення формули =ISNUMBER(SEARCH($B$1,A4)) для умовного форматування, якщо клітинка, яку я використовував для функції пошуку, порожня, усі клітинки, у яких здійснюється пошук (A4:C368), будуть виділені. Але після введення рядка для критеріїв пошуку клітинки, що містять критерії пошуку, виділяються правильно. Чи є спосіб налаштувати формулу, щоб вона не виділялася, поки не буде введено критерії пошуку? Або я щось зробив не так?

Крім того, використання формули в кроці 5 на іншому аркуші в книзі не працює. Я намагаюся виконати пошук і показати результати цього пошуку на одному аркуші з назвою Search & Results, маючи інформацію для пошуку на аркуші з назвою Index. Формула, яку я використовую, є =IFERROR(VLOOKUP(A3,Index!$A$4:$C$368,5,FALSE),""), де A3 є початком моїх пронумерованих клітинок на аркуші пошуку та результатів та пошуку відбувається в клітинках A4:C368 на аркуші покажчика.
Цей коментар був мінімізований модератором на сайті
У мене така ж проблема. Ви отримали відповідь?
Цей коментар був мінімізований модератором на сайті
також я. у мене така ж проблема
Цей коментар був мінімізований модератором на сайті
Привіт Колбі. Ваша формула vlookup=IFERROR(VLOOKUP(A3,Index!$A$4:$C$368,5,FALSE),"") відноситься до діапазону від A4 до C368 в індексі назв аркуша, який має лише 3 стовпці, але ви вводите 5 праворуч після $C$368, що вказує формулі vlookup відображати значення стовпця 5, яке не існує у вибраному діапазоні. Виправте це, і я вірю, що ваша проблема буде вирішена.
Цей коментар був мінімізований модератором на сайті
у вас була така ж проблема з формулою, спробуйте це у вашому правилі умовного форматування "=AND($I$1<>"",ISNUMBER(SEARCH($I$1,$B4)))", це працює для мене
I1 — моє вікно пошуку, B4 — перша клітинка вибраного діапазону
Цей коментар був мінімізований модератором на сайті
я дотримувався наведеного методу пошуку, і він працював ідеально, однак результати мають гіперпосилання, і він показує мені результат без гіперпосилання. Чи є спосіб, щоб він показав мені результат із посиланням?
Цей коментар був мінімізований модератором на сайті
Привіт, я використовую пошук між моїми постачальниками (один стовпець) та брендами (інший стовпець). За допомогою цього налаштування існують бренди (наприклад, Microsoft), які можуть мати більше постачальників (постачальник A, постачальник B, постачальник C,...). Я хотів би шукати назву бренду (Microsoft) і хотів би побачити всіх можливих постачальників (також A, B & C). Але тепер результат – лише перший постачальник і все. Як я можу це змінити/виправити?

Велике спасибі!
Цей коментар був мінімізований модератором на сайті
Привіт Томас,
Можливо, ви зможете змінити порядок своїх даних і створити динамічний спадний список, щоб вирішити проблему. Ви можете переглянути статтю нижче, щоб дізнатися більше.
https://www.extendoffice.com/documents/excel/1350-excel-create-dynamic-drop-down-list.html
Цей коментар був мінімізований модератором на сайті
Привіт! Чи це стосується Google Таблиць? Я намагаюся створити вікно пошуку з формулами, щоб перерахувати всі результати пошуку. Я виконав усі перераховані вище дії, але результати нічого не показують. Є помилка, вона каже "змінити ітераційне обчислення". Але що б я не робив, він нічого не показує – просто порожній. Чи можете ви допомогти мені з цим, будь ласка... Дякую!
Цей коментар був мінімізований модератором на сайті
Привіт! Чи це стосується Google Таблиць? Я намагаюся створити вікно пошуку з формулами, щоб перерахувати всі результати пошуку. Я виконав усі перераховані вище дії, але результати нічого не показують. Є помилка, вона каже "змінити ітераційне обчислення". Але що б я не робив, він нічого не показує – просто порожній. Чи можете ви допомогти мені з цим, будь ласка... Дякую!
Цей коментар був мінімізований модератором на сайті
Добрий день,
Це стосується лише програми Microsoft Excel. Вибачте за незручності.
Цей коментар був мінімізований модератором на сайті
Привіт! Я використовував основну панель пошуку виділення, але маю пару проблем. він прогнозує мій пошук і знаходить його без проблем... однак він завжди виділяє клітинку на пару нижче або вище від шуканої. Чи можете ви допомогти мені з цим, будь ласка?
Цей коментар був мінімізований модератором на сайті
Чи можете ви створити формулу, яка охоплює дві клітинки з інформацією в ретроспективі. Я використовую налаштування, які фіксують імена користувачів і дані про значки, тому мені потрібно, щоб під час фільтрації вона переносила обидві клітинки інформації, а не лише одну
Цей коментар був мінімізований модератором на сайті
Я пробував використовувати це, але я не звик виділяти поле, яке я шукаю, чому це так
Цей коментар був мінімізований модератором на сайті
Привіт, у другій частині: «Створіть власне вікно пошуку з формулами, щоб перерахувати всі результати пошуку», там не вказано, що робити з вікном пошуку, у моєму вікні пошуку немає формули. Я також намагаюся зробити це з кількома стовпцями, це теж працює?
Цей коментар був мінімізований модератором на сайті
me pueden apoyar en cual es la formula para solo buscar
будь ласка
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця