Як зробити рядок пошуку в екселі?

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

Зробити рядок пошуку у програмі ексель можна двома способами: через верхню панель налаштувань або гарячими клавішами на клавіатурі. Розглянемо докладно кожний спосіб.

Перший метод. Щоб викликати рядок пошуку через верхнє меню, потрібно у вкладці «Головна», зліва знайти блок «Редагування», де є іконка у вигляді бінокля.

При натисканні на неї з'явиться маленьке меню, в якому необхідно зробити потрібний вибір:

При натисканні на рядок "Знайти" з'явиться спеціальна форма, в якій потрібно заповнювати рядок "Знайти" потрібним словом і числом, а потім натиснути на кнопку "Знайти все" і на екрані з'являться всі відповідні варіанти, вказуючи адреси осередків.

При натисканні на рядок «Замінити» на іконці у вигляді бінокля з'явиться додатковий рядок «Замінити», який можна заповнити. Тоді при натисканні на клавішу «Замінити все» або «Замінити», буде знаходитись шукане число або слово, і міняти відразу у всіх рядках («Замінити все»), або в першому збігається («Замінити»).

Другий спосіб. Ви можете звертатися до обох спеціальних форм через гарячі клавіші, щоб викликати панель «Знайти» одночасно натисніть на клавіатурі клавіші CTRL+F, а щоб викликати форму Замінити натисніть CTRL+H.

Як у Ексель зробити рядок пошуку?

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

Легко створіть вікно пошуку за допомогою функції фільтра.

Увага: Функція ФІЛЬТР доступний у Excel 2019 та пізніші версіїтак само як Excel для Microsoft 365.

Функція ФІЛЬТР забезпечує простий спосіб динамічного пошуку та фільтрації даних. Переваги використання функції ФІЛЬТР:

• Ця функція автоматично оновлює вихідні дані у міру зміни даних.
• Функція ФІЛЬТР може повертати будь-яку кількість результатів: від одного рядка до тисяч, залежно від того, скільки записів у наборі даних відповідає заданим вами критеріям.

Тут я покажу вам, як використовувати функцію фільтр для створення поля пошуку в Excel.

Крок 1. Вставте текстове поле та налаштуйте властивості.

Функції: Якщо для пошуку вмісту вам потрібно тільки ввести комірку і вам не потрібно помітне поле пошуку, ви можете пропустити цей крок і перейти безпосередньо до Крок 2.

1. Перейдіть до Забудовник вкладку натисніть Вставити > Тext Box (елемент керування ActiveX).

Функції: Якщо Забудовник вкладка не відображається на стрічці, ви можете включити її, дотримуючись інструкцій у цьому посібнику: Як показати/відобразити вкладку розробника у стрічці Excel?

Текстове поле дозволяє вводити текст.

Крок 2. Застосуйте функцію ФІЛЬТР.

1. Перш ніж використовувати функцію фільтра, скопіюйте вихідний рядок заголовка в нову область. Тут я розміщую рядок заголовка під вікном пошуку.

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

=FILTER(Sheet2!$A$5:$G$281,Sheet2!$B$5:$B$281=J2,"No data found")

Як показано на знімку екрана вище, оскільки в текстовому полі тепер немає введення, формула відображає результат "Дані не знайдені"У I5.

• У цій формулі:
  • Аркуш2!$A$5:$G$281: $A$5:$G$281 — це діапазон даних, який ви хочете відфільтрувати на Листі2.
  • Аркуш2!$B$5:$B$281=J2: ця частина визначає критерії, які використовуються для фільтрації діапазону. Він перевіряє кожну комірку в стовпці B, від рядка 5 до рядка 281 на Листі 2, щоб переконатися, що вона дорівнює значенню в комірці J2. J2 - це комірка, пов'язана з полем пошуку.
  • Дані не знайдені: Якщо функція ФІЛЬТР не знаходить рядків, у яких значення в стовпці B дорівнює значенню в комірці J2, вона поверне "Дані не знайдені".

  Результат: перевірте вікно пошуку.

  Давайте перевіримо вікно пошуку. У цьому прикладі, коли я вводжу ім'я клієнта у полі пошуку, відповідні результати будуть відфільтровані та негайно відображені.

  Створіть поле пошуку за допомогою умовного форматування

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

  Крок 1. Вставте текстове поле та налаштуйте властивості.

  Функції: Якщо для пошуку вмісту вам потрібно тільки ввести комірку і вам не потрібно помітне поле пошуку, ви можете пропустити цей крок і перейти безпосередньо до Крок 2.

  1. Перейдіть до Забудовник вкладку натисніть Вставити > Тext Box (елемент керування ActiveX).

  Функції: Якщо Забудовник вкладка не відображається на стрічці, ви можете включити її, дотримуючись інструкцій у цьому посібнику: Як показати/відобразити вкладку розробника у стрічці Excel?

  Текстове поле дозволяє вводити текст.

  Крок 2. Використовуйте умовне форматування для пошуку даних.

  1. Виберіть весь діапазон даних для пошуку. Тут я вибираю діапазон A3: G279.
  2. Під Головна вкладку натисніть Умовне форматування >Нове правило.
  1. Виберіть Використовуйте формулу, щоб визначити, які осередки слід форматувати. в Виберіть тип правила налаштування.
  2. Введіть наступну формулу Формат значень, де ця формула є істинною пунктом.

  Тут, $B3 представляє першу комірку в стовпці, який ви хочете зіставити з критеріями пошуку у вибраному діапазоні, та $ J $ 3 це комірка, пов'язана з полем пошуку.

  Результат

  Давайте перевіримо вікно пошуку. У цьому прикладі, коли я вводжу ім'я клієнта в полі пошуку, відповідні рядки, що містять цього клієнта в стовпці B, будуть негайно виділені вказаним кольором заливки.

  Увага: Цей метод без урахування регістру, що означає, що він відповідатиме тексту незалежно від того, чи вводите ви великі або малі літери.

  Створіть поле пошуку з комбінаціями формул

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

  Крок 1Створіть список унікальних значень зі стовпчика пошуку.

  Функції: унікальні значення в новому діапазоні - це критерії, які я використовуватиму в кінцевому вікні пошуку.

  1. В даному випадку я виділяю і копіюю діапазон B4: B281 на новий робочий лист.
  2. Після вставки діапазону на новий лист залиште вставлені дані виділеними, перейдіть до Дані І виберіть Видалити дублікати.

  Крок 2. Вставте поле зі списком та налаштуйте властивості.

  Функції: Якщо вам потрібно тільки ввести комірку для пошуку вмісту і вам не потрібно помітне поле пошуку, ви можете пропустити цей крок і перейти безпосередньо до Крок 3.

  1. Поверніться до аркуша з набором даних, який ви хочете знайти. Перейти до Забудовник вкладку натисніть Вставити >Поле зі списком (елемент керування ActiveX).

  Функції: Якщо Забудовник вкладка не відображається на стрічці, ви можете включити її, дотримуючись інструкцій у цьому посібнику: Як показати/відобразити вкладку розробника у стрічці Excel?

  Зв'яжіть поле зі списком із коміркою, ввівши посилання на комірку в полі Пов'язаний осередок поле. Її я друкуюM2".

  Порада: Вкажіть це поле, щоб усі дані, введені в поле зі списком, автоматично оновлювалися в осередку M2 і навпаки.

  Тепер ви можете вибрати будь-який елемент із поля зі списком або ввести текст для пошуку.

  Крок 3. Застосуйте формули

  1. Створіть три допоміжні стовпці поруч із вихідним діапазоном даних. Дивіться скріншот:
  Тут B5 - це комірка, що містить ім'я першого клієнта стовпця, в якому виконується пошук.

  =IF(ISNUMBER(SEARCH($M$2,B5)),H5,"")

  =IFERROR(SMALL($I$5:$I$281,H5),"")
  

  =IFERROR(INDEX($A$5:$G$281,$J5,COLUMNS($L$4:L4)),"")

  Тут A5: G281 - Це весь діапазон даних, який ви хочете відобразити в осередку результату.
  • Оскільки в полі пошуку немає введення, результати формули відображатимуть необроблені дані.
  • Цей метод нечутливий до регістру, тобто він буде відповідати тексту незалежно від того, чи вводите ви великі або малі літери.

  Результат

  Давайте перевіримо вікно пошуку. У цьому прикладі, коли я вводжу або вибираю ім'я клієнта в полі зі списком, відповідні рядки, що містять це ім'я клієнта в стовпці B, будуть відфільтровані та негайно відображені у діапазоні результатів.

  Створення поля пошуку в Excel може значно покращити взаємодію з даними, зробивши ваші таблиці динамічнішими та зручнішими для користувача. Незалежно від того, чи вибираєте ви простоту функції ФІЛЬТР, наочну допомогу умовного форматування або універсальність комбінацій формул, кожен метод надає цінні інструменти для розширення ваших можливостей маніпулювання даними. Поекспериментуйте з цими методами, щоб знайти той, який найкраще підходить для конкретних потреб і сценаріїв обробки даних. Для тих, хто хоче глибше вивчити можливості Excel, наш веб-сайт може похвалитися безліччю навчальних посібників. Додаткові поради та рекомендації щодо роботи з Excel можна знайти тут.

  Статті на тему

  Повний посібник по списку з можливістю пошуку в Excel
  У цьому посібнику ви познайомитеся з чотирма способами налаштування списку, що розкривається, з можливістю пошуку в Excel.

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

  Знайдіть відповідне значення, виконавши пошук вгору в Excel
  Зазвичай ми знаходимо відповідні значення згори донизу в стовпці Excel. Як щодо пошуку відповідного значення шляхом пошуку вгору? Ця стаття покаже вам методи досягнення цієї мети.

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

  Все про роботу з excel, word, access, powerpoint

  Той, хто працює у сфері послуг, знає — без запису клієнтів нікуди. Мало того, що потрібно бачити свій розклад, але й нагадувати клієнтам про візити також. Знайшли найбюджетніший та оптимальний варіант: Сервіс VisitTime.
  Для нових користувачів перший місяць безкоштовно.
  Чат-бот для майстрів та спеціалістів, який спрощує ведення записів: — Сам записує клієнтів та нагадує їм про візит; — Персоналізує знижки, чайові, кешбек та передоплати; — Збільшує дохідність та допомагає більше заробляти;

  Ви створили або плануєте створити свій сайт, але не знаєте, як просувати? Просування сайту – це не просто процес, а цілий комплекс заходів, спрямованих на збільшення його відвідуваності та підвищення його позицій у пошукових системах.

  Якщо вам важко потрапити на перші місця у пошуку самостійно, спробуйте технологію Буст, вона прискорює поступ у десятки разів, а перші результати з'являються вже протягом перших 7 днів. Якщо жоден запит у вас не просунеться в Топ10 за місяць, то в SeoHammer за бустер повернуть гроші.

  Як зробити рядок пошуку в Excel?

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

  Пошукова функція в Excel

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

  Спосіб 1: простий пошук

  Простий пошук даних у Excel дозволяє знайти всі комірки, в яких міститься введений у пошукове вікно набір символів (літери, цифри, слова, тощо) без урахування регістру.

  1. Перебуваючи у вкладці «Головна», клацаємо по кнопці «Знайти та виділити», яка розташована на стрічці в блоці інструментів «Редагування». У меню вибираємо пункт «Знайти…». Замість цих дій можна просто набрати на клавіатурі клавіші Ctrl+F.
  2. Після того, як ви перейшли по відповідним пунктам на стрічці, або натиснули комбінацію «гарячих клавіш», відкриється вікно «Знайти та замінити» у вкладці «Знайти». Вона нам і потрібна. У полі «Знайти» вводимо слово, символи, або вирази, якими збираємося шукати. Тиснемо на кнопку «Знайти далі», або на кнопку «Знайти все».
  3. При натисканні на кнопку «Знайти далі» ми переміщуємося до першої комірки, де містяться введені групи символів. Сам осередок стає активним. Пошук та видача результатів проводиться рядково. Спочатку обробляються всі осередки першого рядка. Якщо дані, що відповідають умові, не були знайдені, програма починає шукати в другому рядку, і так далі, поки не відшукає задовільний результат. Пошукові символи не обов'язково мають бути самостійними елементами.Так, якщо як запит буде задано вираз «прав», то у видачі будуть представлені всі осередки, які містять даний послідовний набір символів навіть усередині слова. Наприклад, релевантним запитом у разі вважатиметься слово «Направо». Якщо ви поставите в пошуковій системі цифру «1», то у відповідь потраплять комірки, які містять, наприклад, число «516». Щоб перейти до наступного результату, знову натисніть кнопку «Знайти далі». Так можна продовжувати доти, доки відображення результатів не почнеться по новому колу.
  4. У випадку, якщо при запуску пошукової процедури ви натиснете кнопку «Знайти все», всі результати видачі будуть представлені у вигляді списку в нижній частині пошукового вікна. У цьому списку міститься інформація про вміст осередків з даними, що задовольняють запиту пошуку, вказана їх адреса розташування, а також лист і книга, до яких вони належать. Для того, щоб перейти до будь-якого з результатів видачі, просто клікнути по ньому лівою кнопкою миші. Після цього курсор перейде на той осередок Excel, за записом якого користувач зробив клацання.

  Спосіб 2: пошук за вказаним інтервалом осередків

  Якщо у вас досить масштабна таблиця, то в такому разі не завжди зручно проводити пошук по всьому аркушу, адже в пошуковій видачі може виявитися величезна кількість результатів, які в конкретному випадку не потрібні. Існує спосіб обмежити пошуковий простір лише певним діапазоном осередків.

  1. Виділяємо область осередків, у якій хочемо зробити пошук.
  2. Набираємо на клавіатурі комбінацію клавіш Ctrl+F, після чого запуститися знайоме нам вікно «Знайти і замінити». Подальші дії такі самі, як і за попередньому способі.Єдина відмінність полягатиме в тому, що пошук виконується лише у вказаному інтервалі осередків.

  Спосіб 3: Розширений пошук

  Як вже говорилося вище, при звичайному пошуку результати видачі потрапляють абсолютно всі осередки, що містять послідовний набір пошукових символів у будь-якому вигляді незалежно від регістру.

  До того ж, у видачу може потрапити не лише вміст конкретного осередку, а й адреса елемента, на який вона посилається. Наприклад, в осередку E2 міститься формула, яка є сумою осередків A4 і C3. Ця сума дорівнює 10 і саме це число відображається в комірці E2. Але, якщо ми поставимо в пошуку цифру «4», то серед результатів видачі буде все той же осередок E2. Як таке могло вийти? Просто в комірці E2 в якості формули міститься адреса на комірку A4, яка включає в себе шукану цифру 4.

  Але як відсікти такі та інші свідомо неприйнятні результати видачі пошуку? Саме з цією метою існує розширений пошук Excel.

  1. Після відкриття вікна «Знайти та замінити» будь-яким вищеописаним способом, тиснемо на кнопку «Параметри».
  2. У вікні з'являється ряд додаткових інструментів для керування пошуком. За замовчуванням усі ці інструменти перебувають у стані, як у звичайному пошуку, але за необхідності можна виконати коригування. За замовчуванням, функції «Враховувати регістр» та «Комірки повністю» відключені, але, якщо ми поставимо галочки біля відповідних пунктів, то в такому разі, при формуванні результату враховуватиметься введений регістр, і точний збіг. Якщо ви введете слово з маленької літери, то пошукову видачу, комірки, що містять написання цього слова з великої літери, як це було б за умовчанням, вже не потраплять.Крім того, якщо включена функція «Комірки повністю», то видачу будуть додаватися тільки елементи, що містять точне найменування. Наприклад, якщо ви запитаєте пошук «Миколаїв», то осередки, що містять текст «Миколаїв А. Д.», у видачу вже не додані. За замовчуванням пошук здійснюється тільки на активному аркуші Excel. Але, якщо параметр «Шукати» ви переведете в позицію «У книзі», пошук буде здійснюватися по всіх аркушах відкритого файлу. У меню «Перегляд» можна змінити напрямок пошуку. За замовчуванням, як говорилося вище, пошук ведеться по порядку рядково. Переставивши перемикач у позицію «Стовпцями», можна задати порядок формування результатів видачі, починаючи з першого стовпця. У графі «Область пошуку» визначається, серед яких конкретно елементів здійснюється пошук. За умовчанням, це формули, тобто ті дані, які при натисканні на клітинку відображаються в рядку формул. Це може бути слово, число або посилання на комірку. При цьому програма, виконуючи пошук, бачить лише посилання, а не результат. Про цей ефект йшлося вище. Для того, щоб здійснювати пошук саме за результатами, за тими даними, які відображаються в комірці, а не в рядку формул, потрібно переставити перемикач з позиції Формули в позицію Значення. Крім того, існує можливість пошуку за примітками. У цьому випадку перемикач переставляємо в позицію «Примітки». Ще більш точно пошук можна задати, натиснувши кнопку «Формат». При цьому відкривається вікно формату осередків. Тут можна встановити формат осередків, які братимуть участь у пошуку.Можна встановлювати обмеження за числовим форматом, вирівнюванням, шрифтом, кордоном, заливкою та захистом, по одному з цих параметрів, або комбінуючи їх разом. Якщо ви хочете використовувати формат якогось конкретного осередку, то в нижній частині вікна натисніть на кнопку «Використовувати формат цього осередку…». Після цього з'являється інструмент у вигляді піпетки. За допомогою нього можна виділити той осередок, формат якого ви збираєтеся використовувати. Після того, як формат пошуку налаштований, натискаємо кнопку «OK». Бувають випадки, коли потрібно здійснити пошук не за конкретним словосполученням, а знайти осередки, в яких знаходяться пошукові слова в будь-якому порядку, навіть якщо їх поділяють інші слова та символи. Тоді ці слова потрібно виділити з обох сторін знаком «*». Тепер у пошуковій видачі будуть відображені усі осередки, в яких знаходяться дані слова у будь-якому порядку.
  3. Як тільки налаштування пошуку встановлено, слід натиснути кнопку «Знайти все» або «Знайти далі», щоб перейти до пошукової видачі.

  Як бачимо, програма Excel є досить простим, але разом з тим дуже функціональним набором інструментів пошуку. Для того, щоб зробити найпростіший писк, достатньо викликати пошукове вікно, ввести запит, і натиснути на кнопку. Але в той же час існує можливість налаштування індивідуального пошуку з великою кількістю різних параметрів та додаткових налаштувань.

  Ми раді, що змогли допомогти Вам у вирішенні проблеми.

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

  Чи допомогла вам ця стаття?

  Основне призначення офісної програми Excel – здійснення розрахунків.Документ цієї програми (Книга) може містити багато аркушів із довгими таблицями, заповненими числами, текстом чи формулами. Автоматизований швидкий пошук дозволяє знайти у них необхідні осередки.

  Простий пошук

  Щоб здійснити пошук значення в таблиці Excel, необхідно на вкладці «Головна» відкрити список інструмента «Знайти і замінити», що випадає, і клацнути пункт «Знайти». Той самий ефект можна отримати, використовуючи клавіші Ctrl + F.

  У найпростішому випадку у вікні «Знайти і замінити» треба ввести потрібне значення і клацнути «Знайти все».

  Як видно, у нижній частині діалогового вікна з'явились результати пошуку. Знайдені значення підкреслені червоним таблиці. Якщо замість «Знайти все» клацнути «Знайти далі», то спочатку буде здійснено пошук першого осередку з цим значенням, а при повторному натисканні – другий.

  Аналогічно виконується пошук тексту. У цьому випадку в рядку пошуку набирається текст, що шукається.

  Якщо дані або текст шукається не у всій таблиці еселів, то область пошуку попередньо повинна бути виділена.

  Розширений пошук

  Припустимо, що потрібно знайти всі значення в діапазоні від 3000 до 3999. У цьому випадку в рядку пошуку слід набрати 3. Підстановковий знак "?" замінює собою будь-який інший.

  Аналізуючи результати зробленого пошуку, можна відзначити, що поряд з правильними 9 результатами програма також видала несподівані, підкреслені червоним. Вони пов'язані з наявністю в комірці чи формулі цифри 3.

  Можна задовольнятися більшістю одержаних результатів, ігноруючи неправильні. Але функція пошуку ексель 2010 здатна працювати набагато точніше. Для цього призначено інструмент «Параметри» у діалоговому вікні.

  Клацнувши «Параметри», користувач має можливість здійснювати розширений пошук. Насамперед звернемо увагу на пункт «Область пошуку», в якому за умовчанням виставлено значення «Формули».

  Це означає, що пошук проводився, у тому числі й у тих осередках, де знаходиться не значення, а формула. Наявність у них цифри 3 дала три неправильні результати. Якщо в якості області пошуку вибрати «Значення», буде здійснюватись лише пошук даних і неправильні результати, пов'язані з осередками формул, зникнуть.

  Для того щоб позбутися єдиного неправильного результату, що залишився на першому рядку, у вікні розширеного пошуку потрібно вибрати пункт «Комірка цілком». Після цього результат пошуку стає точним на 100%.

  Такий результат можна було б забезпечити, одразу вибравши пункт «Комірка цілком» (навіть залишивши в області пошуку значення «Формули»).

  Тепер звернемося до пункту «Шукати».

  Якщо замість встановленого за замовчуванням «На аркуші» вибрати значення «У книзі», то немає необхідності перебувати на аркуші шуканих осередків. На скріншоті видно, що користувач ініціював пошук, перебуваючи на порожньому аркуші 2.

  Наступний пункт вікна розширеного пошуку - "Переглядати", що має два значення. За замовчуванням встановлено «рядки», що означає послідовність сканування осередків по рядках. Вибір іншого значення – «по стовпцям», змінює лише напрямок пошуку і послідовність видачі результатів.

  При пошуку в документах Microsoft Excel, можна використовувати й інший знак підстановки – «*». Якщо розглянутий "?" означав будь-який символ, то "*" замінює собою не один, а будь-яку кількість символів. Нижче наведено скріншот пошуку за словом Louisiana.

  Іноді під час пошуку необхідно враховувати регістр символів.Якщо слово louisiana буде написано з маленької літери, результати пошуку не зміняться. Але якщо у вікні розширеного пошуку вибрати "Враховувати регістр", то пошук виявиться безуспішним. Програма вважатиме слова Louisiana і louisiana різними, і, звісно, ​​не знайде перше їх.

  Різновиди пошуку

  Пошук збігів

  Іноді буває необхідно виявити в таблиці значення, що повторюються. Щоб здійснити пошук збігів, спочатку потрібно виділити діапазон пошуку. Потім, на тій же вкладці "Головна" у групі "Стилі", відкрити інструмент "Умовне форматування". Далі послідовно вибрати пункти «Правила виділення осередків» та «Повторювані значення».

  Результат представлений на скріншоті нижче.

  При необхідності користувач може змінити колір візуального відображення осередків, що збіглися.

  Фільтрування

  Інший різновид пошуку – фільтрація. Припустимо, що користувач хоче знайти в стовпці B числові значення в діапазоні від 3000 до 4000.

  1. Виділити перший стовпець із заголовком.
  2. На тій же вкладці «Головна» у розділі «Редагування» відкрити інструмент «Сортування та фільтр», а потім натисніть «Фільтр».
  3. У верхньому рядку стовпця B з'являється трикутник – умовний знак списку. Після його відкриття у списку "Числові фільтри" клацнути пункт "між".
  4. У вікні «Автофільтр користувача» слід ввести початкове і кінцеве значення плюс OK.

  Як видно, відображатися стали лише рядки, що задовольняють введену умову. Решта виявилася тимчасово прихованою. Щоб повернутися до початкового стану, слід повторити крок 2.

  Різні варіанти пошуку були розглянуті на прикладі Excel 2010. Як зробити пошук в ексель інших версій? Різниця у переході до фільтрації є у ​​версії 2003 року.У меню «Дані» слід послідовно вибрати команди «Фільтр», «Автофільтр», «Умова» та «Автофільтр користувача».

  Відео: Пошук у таблиці Excel

  Цей приклад навчить вас створювати власний рядок пошуку Excel.

  Ось так виглядає таблиця. Якщо ввести пошуковий запит у комірку B2Excel знайде збіги в стовпці E і видасть результат у стовпці B.

  Щоб створити цей рядок пошуку, виконайте вказівки нижче:

  1. Виділіть комірку D4 та вставте функцію SEARCH (ПОШУК), як показано нижче, вказавши абсолютне посилання на комірку В2. =SEARCH($B$2,E4)
     =ПОШУК($B$2;E4)
  2. Двічі клацніть по маркеру автозаповнення, який знаходиться у правому нижньому кутку комірки D4, щоб швидко скопіювати формулу у всі осередки стовпця, що залишилися. D.Пояснення: Функція SEARCH (ПОШУК) шукає початкову позицію шуканого значення у рядку. Функція SEARCH (ПОШУК) не враховує регістр. У слові "Tunisia" рядок "uni" має початкове положення 2, а в слові "United States" початкове положення дорівнює 1. Чим менше значення, тим вище воно має розташовуватися.
  3. І "United States", і "United Kingdom" повертають значення 1. Як бути? Трохи пізніше ми надамо всім даним унікальні значення за допомогою функції RANK (РАНГ), ​​але для цього нам потрібно трохи скоригувати результат формули в осередку D4, як показано нижче: IFERROR(SEARCH($B$2,E4)+ROW()/100000,"")
     ЯСЛИПОМИЛКА(ПОШУК($B$2;E4)+РЯДОК()/100000;"")
  4. Знову двічі клацніть по правому нижньому кутку комірки D4, щоб швидко скопіювати формулу до інших осередків стовпця.Пояснення: Функція ROW (РЯДОК) повертає номер рядка комірки. Якщо ми розділимо номер рядка на велике число і додамо значення до результату функції SEARCH (ПОШУК), у нас завжди будуть виходити унікальні значення, а невеликий приріст не вплине на ранжування. Тепер значення для United States становить 1,00006, а для United Kingdom - 1,00009. Крім цього, ми додали функцію IFERROR (ЯКІ ПОМИЛКА). Якщо осередок містить помилку, наприклад, коли рядок може бути знайдено, повертається порожній рядок («»).
  5. Виберіть комірку C4 та вставте функцію RANK (РАНГ), ​​як показано нижче: =IFERROR(RANK(D4,$D$4:$D$197,1),"")
     =ЯКЛИПОМИЛКА(РАНГ(D4;$D$4:$D$197;1);"")
  6. Двічі клацніть по правому кутку комірки С4, щоб швидко скопіювати формулу до інших осередків.Пояснення: Функція RANK (РАНГ) повертає порядковий номер значення. Якщо третій аргумент функції дорівнює 1, Excel вибудовує числа зростання: від найменшого до більшого. Оскільки ми додали функцію ROW (РЯДОК), всі значення в стовпці D стали унікальними. Як наслідок, числа у стовпці C також унікальні.
  7. Ми майже закінчили. функцію VLOOKUP (ВПР) ми будемо використовувати, щоб витягти знайдені країни (найменше значення першим, друге найменше другим, і т.д.) Виділіть осередок B4 та вставте функцію VLOOKUP (ВВР), як показано нижче. =IFERROR(VLOOKUP(A4,$C$4:$E$197,3,FALSE),"")
     =ЯКЩОПОМИЛКА(ВПР(A4;$C$4:$E$197;3;БРЕХНЯ);"")
  8. Двічі клацніть правому нижньому кутку комірки B4, щоб швидко скопіювати формулу до інших осередків.
  9. Змініть колір чисел у стовпці А на білий і приховати стовпці З і D.

  Результат: Ваш власний рядок пошуку в Excel.

  Урок підготовлений для Вас командою сайту office-guru.ru
  Джерело: Антон АндроновПравила передруківЩе більше уроків з Microsoft Excel

  Оцініть якість статті. Нам важлива ваша думка: