Excel works

Excel works!

Чтоб сопоставить две ячейки, в которых содержится текст, нужно просто приравнять их в примыкающей ячейке. Но что созодать, если для вас необходимо сопоставить текст с учетом огромных и малеханьких букв? А если с учетом британских букв?

Возникла таковая потребность, как сравнение текста в Excel? Читайте дальше, и, уверен, статья для вас поможет:

1) С обычным сопоставлением разобрались? Непревзойденно 🙂

2) Сравнение текста в Excel по регистрам

Сравнение текстов происходит обычный формулой в примере номер 1, на картинке. Если нужно сопоставить строчные и большие буковкы воспользуйтесь функцией =СОВПАД(). Пример 2.

3) Поиск чисел в ячейки с текстом

Самый обычный метод отыскать числа в строке, пользоваться формулой

Самая всераспространенная ошибка — заместо буковкы О пишут число 0 и напротив
Но непременно воспользуйтесь формулой массива — нажмите ctrl+shift+enter заместо enter опосля ввода формулы.

4) Поиск определенных знаков в тексте

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

Снова же не забываем про формулы массивов!

5) Удалить излишние пробелы в начале и конце строчки

В версии excel 2007 возникла восхитительная функция =СЖПРОБЕЛЫ() — она удаляет все излишние пробелы в начале и конце текста, а так же все задвоенные пробелы. Настоятельно рекомендую уяснить — использую ее чуток ли не любой денек.

6) Поиск знаков

Для поиска знаков можно применять функцию =ПОИСК(), она возвращает (считает), на какой позиции находиться данный вами знак. Подробнее тут .

7) Поиск знаков при помощи условного форматирования либо фильтра. Возможность «Содержит»

Можно окрасить все ячейки цветом, которые содержат данные знаки либо надписи в их. Для этого воспользуйтесь условным форматированием , как показано на рисунке.

Чтоб бросить лишь те ячейки, которые содержат нужные знаки, воспользуйтесь фильтрами. В фильтрах есть возможность отобрать строчки по данным условиям

Excel сравнение текста в ячейках

Если вы желаете сопоставить две либо наиболее текстовых строк на листе с учетом регистра либо без учета регистра, как показано на последующем снимке экрана, в данной статье я расскажу о неких нужных формулах, которые посодействуют для вас совладать с данной задачей в Excel.

• Сравните две либо наиболее текстовых строк с учетом регистра
• Сравните две либо наиболее текстовых строк без учета регистра

Сравните две либо наиболее текстовых строк с учетом регистра

Сравните две текстовые строчки с учетом регистра:

Обычно функция EXACT может посодействовать для вас сопоставить, равны ли две либо наиболее строчки в точности, для сопоставления лишь 2-ух текстовых строк общий синтаксис:

• text1 : 1-ая текстовая строчка, которую вы желаете сопоставить.
• text2 : 2-ая текстовая строчка употребляется для сопоставления с первой текстовой строчкой.

1. Скопируйте либо введите приведенную ниже формулу в пустую ячейку:

2. Потом перетащите дескриптор наполнения вниз к ячейкам, к которым вы желаете применить эту формулу, и вы получите ИСТИНА, если две строчки равны, и ЛОЖЬ, если не равны. Смотрите снимок экрана:

Советы: Если вы желаете применять определенный текст для подмены значения по дефлоту ИСТИНА либо ЛОЖЬ, вы сможете соединить функцию ЕСЛИ с функцией ТОЧНЫЙ, примените последующую формулу:

Сравните несколько текстовых строк с учетом регистра:

Время от времени для вас может потребоваться сопоставить несколько текстовых строк, в этом случае вы должны соединить функцию AND с функцией EXACT, общий синтаксис:

• range : Спектр ячеек, которые вы желаете сопоставить;
• value : Текст либо ячейка в спектре ячеек употребляется для сопоставления.

1. Введите либо скопируйте приведенную ниже формулу в пустую ячейку:

2, Потом нажмите Shift + Ctrl + Enter совместно, чтоб получить верный итог, а потом скопируйте и заполните эту формулу в остальные ячейки, которые вы желаете применять, и вы получите итог, как показано ниже:

Советы: Чтоб показать иной определенный текст в итоге заместо ИСТИНА либо ЛОЖЬ, примените последующую формулу:

Пожалуйста, не забудьте надавить Shift + Ctrl + Enter ключи совместно, чтоб получить верный итог.

Сравните две либо наиболее текстовых строк без учета регистра

Если вы желаете получить итог без учета регистра при сопоставлении 2-ух либо наиболее текстовых строк, используйте последующие формулы:

Сравните две текстовые строчки без учета регистра:

1. Введите либо скопируйте приведенную ниже ординарную формулу в пустую ячейку, в которой вы желаете получить итог:

2. Тогда и вы получите итог, как показано на снимке экрана ниже:

Советы: Чтоб вывести свои собственные тексты для равенства и различий, используйте эту формулу:

Сравните несколько текстовых строк без учета регистра:

1. Чтоб проверить, равны ли несколько текстовых строк без учета регистра, используйте последующую формулу:

Внимание: В приведенной выше формуле A2: D2 это спектр ячеек, которые вы желаете сопоставить, A2 — это значение ячейки в спектре данных, которое использовалось для сопоставления, а число 4 относится к количеству ячеек, которые вы желаете проверить.

2. Потом перетащите маркер наполнения вниз к ячейкам, чтоб применить эту формулу, и итог будет отображаться, как показано ниже:

Советы: Используйте формулу ниже, чтоб получить определенный текст, который вы желаете показать в итоге:

Применяемые относительные функции:

• EXACT :
• Функция EXACT ассоциирует две строчки и возвращает TRUE, если они буквально такие же, либо возвращает FALSE.

• IF :
• Он делает обычный логический тест, который зависит от результата сопоставления, и возвращает одно значение, если итог ИСТИНА, либо другое значение, если итог ЛОЖЬ.

• COUNTIF :
• Функция СЧЁТЕСЛИ — это статистическая функция в Excel, которая употребляется для подсчета количества ячеек, соответственных аспекту.

Остальные статьи:

• Проверьте, соответствует ли ячейка хоть какому значению в перечне
• К примеру, у меня есть перечень текстовых строк в столбце A, и сейчас я желаю проверить, соответствует ли значение ячейки в этом столбце хоть какому значению в другом перечне столбца E. Если равно, показать ИСТИНА, в неприятном случае отображается значение ЛОЖЬ. .

• Если ячейка содержит текст, показать в Excel
• Если у вас есть перечень текстовых строк в столбце A и строчка главных слов, сейчас для вас необходимо проверить, возникают ли главные слова в текстовой строке. Если главные слова возникают в ячейке, отображается их, если нет, отображается пустая ячейка.

• Показать определенный текст на базе значения в Excel
• Представим, у вас есть перечень значений, сейчас вы желаете показать определенный текст на базе значения. К примеру, если значение ячейки больше 1000, текст «Прирастить» отображается в другом столбце, как показано ниже. Чтоб совладать с данной задачей, для вас могут посодействовать функции REPT и IF.

Наилучшие инструменты для работы в кабинете

Kutools for Excel — поможет для вас выделиться из толпы

Желаете стремительно и идеально делать свою ежедневную работу? Kutools for Excel дает массивные расширенные функции 300 (объединение книжек, сумма по цвету, разделение содержимого ячеек, дата преобразования и так дальше . ) и экономия 80% времени вам.

Exceltip

Блог о программке Microsoft Excel: приемы, хитрости, секреты, трюки

Как сопоставить два столбца в Excel — способы сопоставления данных Excel

Умение ассоциировать два массива данных в Excel нередко пригождается для людей, обрабатывающих огромные объемы данных и работающих с большущими таблицами. К примеру, сравнение быть может применено в выявлении циклических значений, правильности занесения данных либо внесение данных в таблицу в срок. В статье ниже описаны несколько приемов сопоставления 2-ух столбцов с данными в Excel.

Внедрение условного оператора ЕСЛИ

Способ использования условного оператора ЕСЛИ различается тем, что для сопоставления 2-ух столбцов употребляется лишь нужная для сопоставления часть, а не весь массив полностью. Ниже описаны шаги по реализации данного способа:

Разместите оба столбца для сопоставления в колонках A и B рабочего листа.

В ячейке С2 введите последующую формулу =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A2;$B$2:$B$11;0));"";A2) и протяните ее до ячейки С11. Данная формула поочередно просматривает наличие всякого элемента из столбца A в столбце B и возвращает значение элемента, если оно было найдено в столбце B.

Внедрение формулы подстановки ВПР

Механизм работы формулы аналогичен предшествующей методике, отличие заключается в использовании формулы ВПР, заместо ПОИСКПОЗ. Отличительной индивидуальностью данного способа также является возможность сопоставления 2-ух горизонтальных массивов, используя формулу ГПР.

Чтоб сопоставить два столбца с данными, находящимися в столбцах A и B(аналогично предшествующему способу), введите последующую формулу =ВПР(A2;$B$2:$B$11;1;0) в ячейку С2 и протяните ее до ячейки С11.

Данная формула просматривает любой элемент из основного массива в сравниваемом массиве и возвращает его значение, если оно было найдено в столбце B. В неприятном случае Excelвернет ошибку #Н/Д.

Внедрение макроса VBA

Внедрение макросов для сопоставления 2-ух столбцов дозволяет унифицировать процесс и уменьшить время на подготовку данных. Решение о том, какой итог сопоставления нужно показать, стопроцентно зависит от вашей фантазии и способностей владения макросами. Ниже представлена методика, размещенная на официальном веб-сайте Микрософт.

Сделайте новейший модуль кода VBA и введите последующий код. О том, как писать макросы вы сможете прочесть наиболее тщательно в статье написания VBA кода.

В данном коде переменной CompareRange присваивается спектр со сравниваемым массивом. Потом запускается цикл, который просматривает любой элемент в выделенном спектре и ассоциирует его с каждым элементом сравниваемого спектра. Если были найдены элементы с схожими значениями, макрос вносит значение элемента в столбец С.

Чтоб применять макрос, вернитесь на рабочий лист, выделите главный спектр (в нашем случае, это ячейки A1:A11), нажмите сочетание кнопок Alt+F8. В показавшемся диалоговом окне изберите макрос Find_Matches и щелкните клавишу выполнить.

Опосля выполнения макроса, итог должен быть последующим:

Внедрение надстройки Inquire

С пуском версии 2013, Excel обзавелась увлекательной надстройкой под заглавием Inquire, которая дозволяет ассоциировать и рассматривать два файла Excel. Если для вас нужно сопоставить две версии 1-го и такого же файла, к примеру, опосля сотворения книжки, ваши коллеги занесли кое-какие конфигурации, и для вас необходимо найти что конкретно они изменили, воспользуйтесь инвентарем WorkbookCompare надстройки Inquire. Подробнее о том, как устанавливать, запускать и применять надстройку читайте в моей предшествующей статье про надстройку Inquire.

Итак, мы разглядели несколько методов сопоставления данных в Excel, которые посодействуют для вас решить некие аналитические задачки и упростят работу в поиске повтояющихся (либо неповторимых) значений.

Для вас также могут быть увлекательны последующие статьи

53 комментария

Спасибо, у вас весьма понятно и прекрасно оформлено, глаз веселит
для меня трудность- осознать работу ПОИСКОЗ. Если не тяжело сделайте пост с пояснениями по данной формуле.

Хороший денек, Сергей. Про формулы подстановки сможете прочесть в одной из моих прошлых статей.

Молодца. Читаю Ваши статьи, наглядно и доходчиво, Спасибо.

Большущее спасибо!
Благодаря приведенной Вами формуле =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A2;$B$2:$B$11;0));"";A2) я смогла сопоставить два перечня (9 и 2 тыщи позиций в любом).

Но выплыла иная неувязка. В перечнях есть однообразные данные, отличающиеся лишь значком *. Опосля выполнения формулы были отмечены, как совпадающие, и данные с * и без *. Что необходимо поменять в формуле, чтоб она возвращала лишь четкие совпадения?
Спасибо.

Пришлите пример, пожалуйста, не совершенно сообразил ситуацию. Видимо сравнение идет по формулам, а с ними уже посложнее будет

Хороший денек, Ренат!
Пробовала при помощи вашей формулы сопоставить два столбца с датами, потом с договорами. К огорчению, не выходит. Ячейки получаются пустыми, хотя большая часть значений совпадают (но excel их тотально не лицезреет). Подскажите, в чем быть может ошибка?

Пришлите, пожалуйста, файл с примером, поглядим

Хорошего времени суток!
Спасибо за полезную статью!
Сравнение прошло удачно, но при попытке сохранить итог сопоставления «Export Result» выходит ошибка «Unable to save the export file. Error: Exception from HRESULT: 0x800AC472» и ничего не сохраняется. Не понимаете в чём быть может дело? Office 2013 Home and Bussiness Windows 8.1 Pro

Запамятовал добавить! Для сопоставления употреблял Inquire.

Хороший денек, Антон. Если честно, не сталкивался с схожей неувязкой, потому кое-чем определенным посодействовать не могу. Но официальном веб-сайте данная ошибка описана, если это для вас поможет, скидываю ссылку на страничку

Ренат, спасибо огромное за статью! Весьма понадобилась в сопоставлении формула!

Денек хороший, Ренат
статья отменная, доступно)))
Большущая просьба, разглядите мою делему.
Почаще требуется не попросту 2 столбца данных сопоставить, а сопоставить два прайса. Индентификатором будет код либо артикул — а при совпадении значений нужно сравнить цены. НАпример А-артикулы основного массива, В-Цены основного массива, Д-Артикулы сравниваемого массива и Е-цены сравниваемого массива. При совпадении артикула в А и Д в столбик С копировать стоимость из соответственного Е.
Обычно по фирмам прайсы составлены по различному, артикулы разбросаны и чтобы сопоставить цены полдня (в наилучшем случае) убиваешь на рутину((((