Як у екселі знайти значення стовпця. Секрети пошуку у Excel. Пошук однакових значень у діапазоні Excel

У Microsoft Excel часто працюють з більшими обсягами інформації. У ньому створюються великі таблиці на тисячі рядків, стовпців і позицій. Знайти якісь конкретні дані в такому масиві важко. А іноді – взагалі неможливо. Це завдання можна спростити. Розберіться, як знайти в Екселе потрібне слово. Так вам легше буде орієнтуватись у документі. І ви зможете швидко перейти до шуканої інформації.

Щоб відобразилися адреси всіх осередків, в яких є те, що ви шукаєте, зробіть таке:

Якщо у вас Office 2010, перейдіть до Меню - Правка - Знайти.
Відкриється вікно із полем для введення. Напишіть у ньому фразу для пошуку.
У Excel 2007 ця кнопка є в меню "Головна" на панелі "Редагування". Вона знаходиться праворуч.
Аналогічного результату у всіх версіях можна досягти, якщо натиснути клавіші Ctrl+F.
У полі надрукуйте слово, фразу чи цифри, які треба відшукати.
Натисніть «Знайти все», щоб запустити пошук у цілому документі. Якщо клікніть «Далі», програма по одній виділятиме клітини, які знаходяться нижче курсору-осередка Excel.
Зачекайте, поки процес закінчиться. Чим більший документ, тим довше система шукатиме.
З'явиться список із результатами: іменами та адресами клітин, у яких є збіги із заданою фразою, та текстом, який у них написаний.
При кліку на кожну сходинку виділятиметься відповідна комірка.
Для зручності можете "розтягнути" вікно. Так у ньому буде видно більше рядків.
Щоб відсортувати дані, клацніть на назви стовпців над результатами пошуку. Якщо натисніть на «Аркуш», рядки вишикуються за абеткою залежно від назви аркуша, якщо виберіть «Значення» - встануть за значенням.
Ці стовпці також "розтягуються".

Можете поставити свої умови. Наприклад, запустити пошук за кількома знаками. Ось як в Екселі знайти слово, яке ви не пам'ятаєте цілком:

Введіть лише частину напису. Можна хоч одну літеру - будуть виділені всі місця, де вона є.
Використовуйте символи * (зірочка) та? (знак запитання). Вони замінюють пропущені знаки.
Питання означає одну відсутню позицію. Якщо ви напишіть, наприклад, «П???», відобразяться осередки, у яких є слово із чотирьох символів, що починається на «П»: «Плуг», «Поле», «Пара» тощо.
Зірка (*) замінює будь-яку кількість символів. Щоб знайти всі значення, у яких міститься корінь «рост», почніть пошук за ключом «*рост».

Також ви можете зайти в налаштування:

У вікні "Знайти" натисніть "Параметри".
У розділах «Переглядати» та «Область пошуку» вкажіть, де і за якими критеріями треба шукати збіги. Можна вибрати формули, примітки чи значення.
Щоб система розрізняла малі та великі літери, поставте галочку в «Враховувати регістр».
Якщо ви відзначите пункт «Комірка цілком», у результатах з'являться клітини, в яких є лише задана пошукова фраза і нічого більше.

Параметри формату осередків

Щоб знайти значення з певною заливкою або зображенням, використовуйте налаштування. Ось як знайти в Excel слово, якщо воно має відмінний від решти тексту вигляд:

У вікні пошуку натисніть «Параметри» та клацніть на кнопку «Формат». Відкриється меню з кількома вкладками.
Можете вказати певний шрифт, вид рамки, колір тла, формат даних. Система переглядатиме місця, які відповідають заданим критеріям.
Щоб взяти інформацію з поточної клітинки (виділеної в цей момент), натисніть «Використовувати формат цієї комірки». Тоді програма знайде всі значення, які мають той самий розмір і вигляд символів, той самий колір, ті самі межі тощо.

Пошук кількох слів

У Excel можна знайти клітини за цілими фразами. Але якщо ви ввели ключ «Синя куля», система працюватиме саме за цим запитом. У результатах не з'являться значення з «Синя кришталева куля» або «Синя блискуча куля».

Щоб в Екселі знайти не одне слово, а відразу кілька, зробіть таке:

Напишіть їх у рядку пошуку.
Поставте між ними зірочки. Вийде «*Текст* *Текст2* *Текст3*». Так знайдуться всі значення, які містять вказані написи. Незалежно від того, чи є між ними якісь символи чи ні.
Цим способом можна встановити ключ навіть з окремими літерами.

Фільтр

Ось як шукати в Екселі, використовуючи фільтр:

Виділіть якийсь заповнений осередок.
Натисніть Головна - Сортування - Фільтр.
У верхньому рядку у клітин з'являться стрілочки. Це меню, що випадає. Відкрийте його.
Введіть запит у текстовому полі та натисніть «OK».
У стовпці відображатимуться лише комірки, що містять фразу, що шукається.
Щоб скинути результати, у списку, що випадає, позначте «Виділити все».
Щоб вимкнути фільтр, повторно натисніть на нього у сортуванні.

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

Щоб знайти в Excel якусь фразу чи число, використовуйте вбудовані можливості інтерфейсу. Можна вибрати додаткові параметри пошуку та увімкнути фільтр.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Пошук збігів

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

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

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

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

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

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

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

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

Якщо здійснити пошук за функціями підстановки, Google покаже, що ВПР набагато популярніша за функції ІНДЕКС. Воно й зрозуміло, адже щоб надати функції ІНДЕКС той самий функціонал, як і ВПР, необхідно скористатися ще однією формулою – ПОШУКПОЗ. Щодо мене, то завжди було непросто спробувати і освоїти дві нові функції одночасно. Але вони дають більше можливостей та гнучкості у створенні електронних таблиць. Але про все по порядку.

Функція ВПР()

Припустимо, у вас є таблиця з даними працівників. У першій колонці зберігається табельний номер співробітника, у решті – інші дані (ПІБ, відділ тощо). Якщо у вас є табельний номер, можна скористатися функцією ВПР, щоб повернути певну інформацію про співробітника. Синтаксис формули =ВПР(шукане_значення; таблиця; номер_стовпця; [інтервальний_перегляд]).Вона каже Excel: «Знайди в таблицірядок, перший осередок якого збігається з шуканим значенням,та поверни значення комірки з порядковим номером номер_стовпця».

Але трапляються ситуації, коли ви маєте ім'я співробітника і необхідно повернути табельний номер. На малюнку в комірці A10 – ім'я працівника і потрібно визначити табельний номер у комірці B10.

Коли ключове поле знаходиться правіше за дані, які ви хочете отримати, ВПР не допоможе. Якщо, звичайно, була б можливість поставити номер_стовпця-1, Тоді проблем не було. Одним із поширених рішень є додавання нового стовпця A, копіювання імен співробітників у цей стовпець, заповнити табельні номери за допомогою ВВР, зберегти їх як значення та видалити тимчасову колонку A.

Функція ІНДЕКС()

Щоб вирішити нашу проблему в один крок, необхідно скористатися формулами ІНДЕКС та ПОШУКПОЗ. Складність цього підходу полягає в тому, що потрібно застосувати дві функції, які ви, можливо, ніколи не застосовували до цього. Для спрощення розуміння вирішимо це завдання у два етапи.

Почнемо з функції ІНДЕКС. Жахлива назва. Коли хтось говорить «індекс», у мене в голові не виникає жодної асоціації, чим займається ця функція. А вимагає вона цілих три аргументи: = ІНДЕКС (масив; номер_рядка; [номер_стовпця]).

Простіше кажучи, Excel йде в масив даних і повертає значення, що знаходиться на перетині зазначеного рядка і стовпця. Начебто просто. Таким чином, формула = ІНДЕКС($A$2:$C$6;4;2) поверне значення, що знаходиться в осередку B5.

Стосовно нашої проблеми, щоб повернути табельний номер працівника, формула має виглядати так =ІНДЕКС($A$2:$A$6;?;1). Виглядає як нісенітниця, але якщо ми замінимо знак питання формулою ПОШУКПОЗ, у нас є рішення.

Функція ПОШУКПОЗ()

Синтаксис цієї функції такий: =ПОШУКПОЗ(пошукова_значення; проглядаються_масив; [тип_порівнювання]).

Вона каже Excel: «Знайди потрібне_значенняу масиві даних та поверни номер рядка масиву, в якому це значення зустрічається». Таким чином, щоб знайти в якому рядку знаходитися ім'я співробітника в осередку A10, необхідно прописати формулу = ПОШУКПОЗ (A10; $ B $ 2: $ B $ 6; 0). Якщо в осередку A10 буде ім'я «Колін Фарел», тоді ПОШУКПОЗ поверне 5-й рядок масиву B2:B6.

Ну, у принципі, все. Функція ПОШУКПОЗ вказує функції ІНДЕКС, у якому рядку шукати значення. Замініть знак питання у формулі ІНДЕКС формулою ПОШУКОПОЗ і ви отримаєте еквівалент ВПР з можливістю пошуку даних, що знаходяться лівіше за ключовий стовпчик. Вийти так:

ІНДЕКС($A$2:$B$6;ПОШУКПОЗ(A10;$B$2:$B$6;0);1)

На початку було незвично, була велика спокуса вставити ще одну колону і працювати, як завжди працював. Але згодом використання функції ІНДЕКС стало звичкою. Виявилося, що це швидше і потребує менших маніпуляцій. Так що наступного разу, коли у вас виникне бажання задати номеру стовпця негативне число у ВПР, скористайтесь поєднанням двох дивних функцій ІНДЕКС та ПОШУКПОЗ, щоб вирішити свою проблему.

Робота з VB проектом (12)
Умовне форматування (5)
Списки та діапазони (5)
Макроси(VBA процедури) (63)
Різне (39)
Баги та глюки Excel (3)

Знайти в комірці будь-яке слово зі списку

Припустимо, ви отримуєте від постачальника/замовника/клієнта заповнену таблицю із переліком продукції:

І цю таблицю необхідно порівняти з артикулами/кодами товару в наявному у вас каталозі продукції:

Як видно – у нашому каталозі лише артикули без найменувань. У замовника крім артикулів ще й назва товару, тобто. багато зайвого. І вам треба зрозуміти, які товари присутні у вашому каталозі, а які ні:

Стандартних формул в Excel для такого пошуку та порівняння немає. Звичайно, можна спробувати застосувати ВПР з символами підстановки спочатку до однієї таблиці, а потім до іншої. Але якщо подібну операцію необхідно робити раз по раз, то прописувати по кілька формул до кожної таблиці прямо скажемо - не комільфо.
Тому я і вирішив сьогодні продемонструвати формулу, яка без будь-яких додаткових. маніпуляцій допоможе таке порівняння зробити. Щоб розібратися самостійно, рекомендую завантажити файл:
Завантажити файл:

(49,5 KiB, 13 249 завантажень)

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

ПРОГЛЯД(2;1/ПОШУК(Каталог!$A$2:$A$11; A2);Каталог!$A$2:$A$11)
=LOOKUP(2,1/SEARCH(Каталог!$A$2:$A$11,A2),Каталог!$A$2:$A$11)
ця формула поверне назву артикула, якщо в тексті є хоч один артикул з каталогу та #Н/Д (#N/A)якщо артикул не знайдено у каталозі.
Перш ніж облагородити цю формулу будь-якими доповненнями (начебто видалення непотрібних #Н/Д) давайте розберемося як вона працює.
Функція ПЕРЕГЛЯД (LOOKUP) шукає задане значення (2) у вказаному діапазоні (масиві - другий аргумент). Як діапазон зазвичай наводиться масив осередків, але функція ПЕРЕГЛЯД має першу потрібну нам особливість - вона намагається перетворити безпосередньо в масив будь-який вираз, записаний другим аргументом. Іншими словами вона обчислює вираз у цьому аргументі, чим ми і користуємося, підставивши як другий аргумент вираз: 1/ПОШУК(Каталог!$A$2:$A$11;A2) . Частина ПОШУК(Каталог!$A$2:$A$11;A2) шукає по черзі кожне значення зі списку Каталогу в осередку A2 (найменування з таблиці Замовника). Якщо значення знайдено, повертається номер позиції першого символу знайденого значення. Якщо значення не знайдено – повертається значення помилки #ЗНАЧ!(#VALUE!). Тепер друга особливість: функція вимагає розташування даних у масиві в порядку зростання. Якщо дані розташовані інакше - функція буде переглядати масив до тих пір, поки не знайде значення більше шуканого, але максимально до нього наближене (хоча якщо дані дозволяють - для більш точного пошуку все ж таки краще відсортувати список за зростанням). Тому спочатку ми 1 ділимо на вираз ПОШУК(Каталог!$A$2:$A$11;A2) , щоб отримати масив виду: (0,0181818181818182:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ !:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ !}
Ну а як шукане значення ми підсовуємо функції число 2 - Свідомо більше, ніж може взагалі зустрітися в масиві (т.к. одиниця, поділена на будь-яке число буде менше двох). І як результат ми отримаємо позицію в масиві, в якій зустрічається останній збіг із каталогу. Після чого функція ПЕРЕГЛЯД запам'ятає цю позицію і поверне значення з масиву Каталог!$A$2:$A$11 (третій аргумент), записане в цьому масиві для цієї позиції.
Ви можете переглянути етапи обчислення функції самостійно для кожного осередку, я тут просто наведу етапи трохи в розширеному для розуміння вигляді:

=ПРОГЛЯД(2;1/ПОШУК(Каталог!$A$2:$A$11; A2);Каталог!$A$2:$A$11)
= ПЕРЕГЛЯД (2;
1/(55:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ;!}
Каталог!$A$2:$A$11)
=ПРОГЛЯД(2;(0,0181818181818182:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ;Каталог!$A$2:$A$11)!}
= ПЕРЕГЛЯД (2;
1;
("FM2-3320":"CV455689":"Q5949X":"CE321A":"CE322A":"CE323A":"00064073":"CX292708":"CX292709":"CX292710
="FM2-3320"

Тепер трохи ушляхетнуємо функцію і зробимо ще пару реалізацій
Реалізація 1:
Замість артикулів та #Н/Д виведемо для знайдених позицій "Є", а для відсутніх "Не знайдено в каталозі":
=ЯКЩО(ЕНД(ПРОГЛЯД(2;1/ПОШУК(Каталог!$A$2:$A$11;A2))));"Не знайдено в каталозі";"Є"
=IF(ISNA(LOOKUP(2,1/SEARCH(Каталог!$A$2:$A$11,A2))),"Не знайдено в каталозі","Є"
робота функції проста - з ПРОГЛЯД(LOOKUP) розібралися, тому залишилися тільки ЕНД і ЯКЩО.
ЕНД (ISNA) повертає ІСТИНА (TRUE)якщо вираз усередині неї повертає значення помилки #Н/Д (#N/A)і БРЕХНЯ (FALSE)якщо вираз усередині не повертає значення цієї помилки.
ЯКЩО (IF) повертає те, що зазначено другим аргументом якщо вираз у першому дорівнює ІСТИНА (TRUE)і те, що зазначено третім аргументом, якщо вираз першого аргументу БРЕХНЯ (FALSE).

Реалізація 2:
Замість #Н/Д виведемо "Не знайдено в каталозі", але при цьому якщо артикули знайдені - виведемо назви цих артикулів:
=ЯКЩОПОМИЛКА(ПРОГЛЯД(2;1/ПОШУК(Каталог!$A$2:$A$11;A2);Каталог!$A$2:$A$11);"Немає в каталозі")
=IFERROR(LOOKUP(2,1/SEARCH(Каталог!$A$2:$A$11,A2),Каталог!$A$2:$A$11),"Немає в каталозі")
Про функцію ОСЛИПОМИЛКА (IFERROR) я докладно розповідав у цій статті: Як у комірці з формулою замість помилки показати 0 .
Якщо коротко, то якщо вираз, заданий першим аргументом функції, повертає значення будь-якої помилки, то функція поверне те, що записано другим аргументом (у нашому випадку це текст "Не знайдено в каталозі"). Якщо ж вираз не повертає помилку, то функція ЕСЛИПОМИЛКА запише те значення, яке було отримано виразом у першому аргументі (у нашому випадку це буде найменування артикула).

Реалізація 3
Потрібно не просто визначити якому артикулу відповідає, але й вивести ціну для найменування за цим артикулом (самі ціни мають бути розташовані в стовпці B листа Каталог):
=ЯКЩОПОМИЛКА(ПРОГЛЯД(2;1/ПОШУК(Каталог!$A$2:$A$11;A2);Каталог!$B$2:$B$11);"")
=IFERROR(LOOKUP(2,1/SEARCH(Каталог!$A$2:$A$11,A2),Каталог!$B$2:$B$11),"")

Пара важливих зауважень:

дані на аркуші з артикулами не повинні містити порожніх осередків. Інакше з великою ймовірністю формула повертатиме значення саме порожнього осередку, а не те, що підходить під умови пошуку
формула здійснює пошук таким чином, що шукається будь-який збіг. Наприклад, як артикул записано цифру 1 , а в рядку найменувань може зустрічатися крім цілої 1 ще й 123 , 651123 , FG1412NMі т.п. Для всіх цих найменувань можна підібрати артикул 1, т.к. він міститься у кожному найменуванні. Як правило, це може статися, якщо артикул 1 розташований в кінці списку

Тому бажано перед використанням формули відсортувати списокза зростанням (від меншого до більшого, від А до Я).

У прикладеному прикладі ви знайдете всі розібрані варіанти.

Якщо вам знадобиться виводити всі найменування, то можна скористатися функцією ЗМІСТ_ОДНО_З моєї надбудови MulTEx .

Стаття допомогла? Поділися посиланням із друзями! Відео уроки

("Bottom bar":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left"," :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","text ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"text textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; background-color:#333333;opacity:0.6;filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px "Lucida Sans Unicode", "Lucida Grande", sans-serif, Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px "Lucida Sans Unicode", "Lucida Grande", sans-serif, Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssress "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))

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

Дивіться також відеоверсію статті.

Викликати діалогове вікно пошуку можна також за допомогою гарячого сполучення клавіш « Ctrl+F«.

Насправді діалогове вікно називається « Знайти та замінити", оскільки воно вирішує ці дві суміжні завдання і якщо його викликати через гаряче поєднання " Ctrl+H«, то буде все те саме, тільки вікно відкриється з активованою вкладкою «Замінити».

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

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

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

Розглянемо ситуацію, коли потрібно знайти співробітника Комарова Олександра Івановича, але є низка факторів, через які виникають складнощі:

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

Комаров Олександр Іванович може бути записаний як: А. Комаров, Комаров Олександр, К амаров Олександр І. і т.д. варіантом достатньо.

Щоб виконати нестрогий пошук, слід скористатися символами-замінниками їх ще називають джокерними символами. Вікно пошуку в Excel підтримує роботу з двома символами: «*» і «?»:

«*» відповідає будь-якій кількості символів;
"?" відповідає кожному окремому символу.

Тут відразу ж виникає питання, а що робити, якщо необхідно виконати пошук знака питання або астериск (відомий як знак множення)? Все просто, достатньо перед знаками, що шукаються, просто поставити тильду «~», відповідно, якщо необхідно виконати пошук тильди, тоді необхідно поставити дві тильди.

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

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

У той же час, якщо трохи перебудувати запит "*К?маров*", тоді записи будуть знайдені незалежно від активності параметра "Комірка повністю".

При здійсненні пошуку в Excel слід пам'ятати кілька важливих правил:

Пошук виконується у виділеній області, а якщо немає виділення, тоді на всьому аркуші. Тому не варто дивуватися, якщо ви випадково виділіть пару порожніх осередків, запустіть пошукове вікно і не зможете нічого знайти.
При пошуку не враховується форматування, тому ніяких знаків позначення валюти додавати не варто.
При роботі з датами, краще виконувати пошук їх у форматі за промовчанням для конкретної системи, в цьому випадку в Excel будуть знайдені всі дати, які відповідають умові. Наприклад, якщо в системі використовується формат д/м/г, пошуковий запит */12/2015 виведе всі дати за грудень 2015 року, незалежно від того, як вони відформатовані (28.12.2015, 28/12/2015, або 28 грудня 2015 і т.д.).