ЭКСЕЛЬ КАК УБРАТЬ СКОБКИ
В Microsoft Excel скобки часто используются для различных целей, таких как обозначение формул, функций или вложенных операций. Однако иногда возникает необходимость удалить скобки из ячеек или формул. Это может быть полезно, например, при копировании данных или при работе с текстовыми строками.
Существует несколько способов удалить скобки в Excel. Один из них — использование функций REPLACE и SUBSTITUTE. Функция REPLACE позволяет заменить определенный символ или набор символов в строке, в то время как функция SUBSTITUTE позволяет заменить один набор символов другим. Таким образом, вы можете использовать эти функции для удаления скобок из ячеек.
Давайте рассмотрим пример. Предположим, что у нас есть ячейка A1, содержащая строку с скобками: «(Пример)». Чтобы удалить скобки, вы можете использовать функцию SUBSTITUTE следующим образом:
Это удалит открывающую скобку ( и оставит только текст «Пример».
Аналогично, чтобы удалить закрывающую скобку ), вы можете использовать функцию SUBSTITUTE таким образом:
Если вам необходимо удалить и открывающую скобку и закрывающую скобку одновременно, вы можете использовать функцию SUBSTITUTE дважды:
Таким образом, вы сможете удалить обе скобки из ячейки.
Надеюсь, эти советы помогут вам удалить скобки в Microsoft Excel и облегчат вашу работу с данными!
Повторяющиеся значения в Excel: найти, выделить и удалить дубликаты
Как удалить лишние символы из ячеек в Эксель / Excel.
Мой метод работает��Без расчетов��Следки без шва на любой размер��Авторские
Excel: Удалить (убрать) лишние данные. Лишние пробелы знаки символы. Сделать единый формат к образцу
Сводные таблицы Excel с нуля до профи за полчаса + Дэшборды! — 1-ое Видео курса \
Как сделать в экселе отрицательное число в скобках
5 Трюков Excel, о которых ты еще не знаешь!
Как в Excel удалить формулы, оставив значения — Два быстрых метода
Михаил Захаров
Я являюсь сертифицированным специалистом по Excel с многолетним опытом работы, помогающим пользователям улучшать и оптимизировать их работу с данными. В качестве консультанта, я провел сотни часов, обучая людей использованию сложных функций и возможностей Excel, помогая им стать более продуктивными и эффективными. Моя страсть к обучению и стремление помогать другим достигать профессиональных высот вдохновляют меня продолжать делиться знаниями и опытом через обучающие курсы и материалы.
Вам также может понравиться:
КАК УДАЛИТЬ СКОБКИ В ЯЧЕЙКЕ EXCEL
Скобки могут случайно попасть в ячейку Excel, создавая искажения в данных или мешая выполнению операций. Вот несколько способов удалить скобки в ячейке Excel:
1. Воспользуйтесь функцией НАЙТИ и ЗАМЕНИТЬ:
— Выберите ячейки, в которых нужно удалить скобки.
— Нажмите комбинацию клавиш Ctrl + F, чтобы открыть диалоговое окно «Найти и заменить».
— В поле «Найти» введите открывающую скобку «(» и в поле «Заменить» оставьте пустое поле.
— Нажмите кнопку «Заменить все». Все скобки будут удалены из выбранных ячеек.
2. Используйте функцию ПОДСТРОКА:
— Выберите ячейку, содержащую скобки.
— Введите формулу =ПОДСТРОКА(A1,2,ДЛИНА(A1)-2), где A1 — адрес ячейки, в которой находятся скобки.
— Нажмите Enter, чтобы применить формулу. В результате скобки будут удалены, а оставшаяся часть текста будет отображаться в ячейке.
3. Используйте функцию ЗАМЕНА:
— Выберите ячейки, содержащие скобки.
— Введите формулу =ЗАМЕНА(A1;»(«;»»), где A1 — адрес ячейки, в которой находятся скобки.
— Нажмите Enter, чтобы применить формулу. Открывающая скобка «(» будет заменена на пустую строку, тем самым удаляя ее из ячейки.
Выберите подходящий способ и удалите скобки в ячейке Excel для более чистого и точного представления данных.
5 Трюков Excel, о которых ты еще не знаешь!
Как удалить лишние символы из ячеек в Эксель / Excel.
Поиск и замена в Excel — как удалить символы во всех ячейках согласно условию
Как в Excel удалить формулы, оставив значения — Два быстрых метода
12 трюков при работе с формулами и функциями в Excel
Как убрать лишние пробелы в Excel?
Excel удалить первые, последние буквы, символы (заданное нужное количество знаков). Индекс в ячейках
[2021] 3 простых способа как разбить ячейку в excel с данными
Михаил Захаров
Я являюсь сертифицированным специалистом по Excel с многолетним опытом работы, помогающим пользователям улучшать и оптимизировать их работу с данными. В качестве консультанта, я провел сотни часов, обучая людей использованию сложных функций и возможностей Excel, помогая им стать более продуктивными и эффективными. Моя страсть к обучению и стремление помогать другим достигать профессиональных высот вдохновляют меня продолжать делиться знаниями и опытом через обучающие курсы и материалы.
Вам также может понравиться:
Как удалить круглые скобки в Excel (4 простых способа)
При работе с данными в MS Excel в них могут присутствовать ненужные круглые скобки. Несомненно, мы хотели бы узнать несколько простых и быстрых методов, с помощью которых можно удалить лишние скобки. В этой статье вы узнаете 4 простых метода удаления круглых скобок в excel с подходящими примерами и соответствующими иллюстрациями.
Скачать книгу по практике
Вы можете скачать бесплатный шаблон Excel отсюда и потренироваться самостоятельно.
Смотрите также: Как удалить группировку в Excel (2 подходящих примера)
Убрать круглые скобки.xlsm
4 простых способа удаления круглых скобок в Excel
Метод 1: Используйте команду «Найти и заменить» для удаления круглых скобок в Excel
Давайте сначала познакомимся с нашим набором данных. Я поместил в свой набор данных несколько фруктов и овощей и их цены. Посмотрите, что в скобках у каждого товара есть цифры. Цифры обозначают коды продуктов, а скобки просто лишние.
Теперь мы удалим круглые скобки с помощью функции Найти и заменить командование.
Сначала мы удалим начальную скобку » ( «.
Шаг 1:
➥ Выберите диапазон данных.
➥ Нажмите Ctrl+H на клавиатуре, затем Найти и заменить откроется диалоговое окно.
➥ Тип » ( » в Найдите то, что бар и держать Заменить на бар пуст.
➥ Затем нажмите Заменить все .
Первые скобки успешно удалены.
Теперь мы удалим конечные скобки » ) «.
Смотрите также: Как разделить имена запятой в Excel (3 подходящих способа)
Шаг 2:
➥ Снова введите » ) » в Найдите то, что бар и держать Заменить на бар пуст.
➥ Затем нажмите Заменить все снова .
Теперь вы увидите, что все скобки удалены идеально.
Читать далее: Как удалить знак доллара в Excel (7 простых способов)
Метод 2: Вставка функции SUBSTITUTE для удаления круглых скобок в Excel
В этом методе мы будем использовать функция SUBSTITUTE для удаления круглых скобок в excel. ЗАМЕНИТЬ функция находит текст в ячейке и заменяет его другим текстом.
Мы выполним эту операцию в два простых шага.
Сначала мы удалим начальные скобки в Колонка Выход1 . А затем заключить скобки в Колонка Выход2 Посмотрим ��
Шаг 1:
➥ Активировать Ячейка D5 .
➥ Введите формулу, приведенную ниже:
=SUBSTITUTE(B5,»(«,»»)
➥ Затем просто нажмите кнопку Войти .
➥ Перетащите Наполнительная рукоятка значок , чтобы скопировать формулу для ячеек ниже.
Вскоре после этого вы заметите, что начальные скобки исчезли.
Теперь уберем конечные скобки.
Шаг 2:
➥ В Ячейка E5 напишите формулу.
=SUBSTITUTE(D5,»)»,»»)
➥ Нажмите кнопку Войти чтобы получить результат сейчас.
➥ Затем перетащите Наполнительная рукоятка значок , чтобы скопировать формулу.
Теперь мы видим, что все скобки исчезли.
Подробнее: Как убрать пробелы в Excel: с помощью формул, VBA и Power Query
Похожие чтения:
- Как удалить пустые символы в Excel (5 методов)
- Как удалить непечатаемые символы в Excel (4 простых способа)
- VBA для удаления символов из строки в Excel (7 методов)
- Удаление последних 3 символов в Excel (4 формулы)
- Как удалить нечисловые символы из ячеек в Excel
Метод 3: Комбинируйте функции LEFT и FIND, чтобы стереть круглые скобки с текста в Excel
Здесь мы объединим две функции для удаления круглых скобок в excel, а именно функция ЛЕВША и функция FIND . СЛЕВА функция возвращает первый символ или символы в текстовой строке слева в зависимости от количества символов, которое вы указали. Функция НАЙТИ используется для поиска позиции подстроки в строке.
Теперь давайте рассмотрим все шаги по порядку.
Шаг 1:
➥ Запишите приведенную формулу в Ячейка D5 :
=LEFT(B5,FIND(«(«,B5,1)-1)
➥ Теперь нажмите кнопку Войти для получения выходных данных.
Шаг 2:
➥ Наконец, просто перетащите Наполнительная рукоятка значок , чтобы скопировать формулу.
�� Формула разбивки:
➥ FIND(«(«,B5,1)
Сайт НАЙТИ функция найдет номер позиции начальных скобок, начиная с первой позиции, которая возвращает-.
➥ LEFT(B5,FIND(«(«,B5,1)-1)
Затем СЛЕВА функция сохранит только 6 букв, начинающихся слева, поэтому из вывода функции вычитается 1. НАЙТИ Наконец, она вернет в качестве.
Читать далее: Как удалить символы слева в Excel (6 способов)
Метод 4: Встраивание макросов VBA для удаления круглых скобок в Excel
Если вам нравится работать с кодами в Excel, то вы можете сделать это с помощью функции Приложение Visual Basic или, VBA Здесь мы удалим все круглые скобки, используя VBA коды.
Шаг 1:
➥ Щелкните правой кнопкой мыши на заголовке листа.
➥ Затем выберите Посмотреть код из контекстное меню .
A VBA откроется окно.
Шаг 2:
➥ Напишите коды, приведенные ниже.
Option Explicit Sub DelParentheses() Cells.Select Selection.Replace What:=""(", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:=")", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub
➥ Затем просто нажмите кнопку Значок запуска чтобы запустить коды.
A Макро откроется диалоговое окно.
Шаг 3:
➥ Нажмите Запускайте .
Теперь посмотрите, что все круглые скобки удалены.
Похожие материалы: Как удалить первый символ из строки в Excel с помощью VBA
Заключение
Я надеюсь, что все описанные выше методы будут достаточно хороши для удаления круглых скобок в excel. Не стесняйтесь задавать любые вопросы в разделе комментариев и, пожалуйста, дайте мне свой отзыв. Посетите наш сайт Exceldemy.com чтобы узнать больше.
Предыдущий пост Как сделать круговую диаграмму с несколькими данными в Excel (2 способа)
Следующий пост Как создать облако слов в Excel (2 простых способа)
Hugh West
Хью Уэст — опытный тренер и аналитик Excel с более чем 10-летним опытом работы в отрасли. Он имеет степень бакалавра в области бухгалтерского учета и финансов и степень магистра делового администрирования. Хью страстно любит преподавать и разработал уникальный подход к обучению, которому легко следовать и который легко понять. Его экспертные знания Excel помогли тысячам студентов и специалистов по всему миру улучшить свои навыки и преуспеть в своей карьере. В своем блоге Хью делится своими знаниями со всем миром, предлагая бесплатные учебные пособия по Excel и онлайн-обучение, чтобы помочь отдельным лицам и компаниям полностью раскрыть свой потенциал.
Как убрать скобки в excel
Зачастую текст, который достается нам для работы в ячейках листа Microsoft Excel далек от совершенства. Если он был введен другими пользователями (или выгружен из какой-нибудь корпоративной БД или ERP-системы) не совсем корректно, то он легко может содержать:
- лишние пробелы перед, после или между словами (для красоты!)
- ненужные символы («г.» перед названием города)
- невидимые непечатаемые символы (неразрывный пробел, оставшийся после копирования из Word или «кривой» выгрузки из 1С, переносы строк, табуляция)
- апострофы (текстовый префикс – спецсимвол, задающий текстовый формат у ячейки)
Давайте рассмотрим способы избавления от такого «мусора».
Замена
«Старый, но не устаревший» трюк. Выделяем зачищаемый диапазон ячеек и используем инструмент Заменить с вкладки Главная – Найти и выделить (Home – Find & Select – Replace) или жмем сочетание клавиш Ctrl+H.
Изначально это окно было задумано для оптовой замены одного текста на другой по принципу «найди Маша – замени на Петя», но мы его, в данном случае, можем использовать его и для удаления лишнего текста. Например, в первую строку вводим «г.» (без кавычек!), а во вторую не вводим ничего и жмем кнопку Заменить все (Replace All). Excel удалит все символы «г.» перед названиями городов:
Только не забудьте предварительно выделить нужный диапазон ячеек, иначе замена произойдет на всем листе!
Удаление пробелов
Если из текста нужно удалить вообще все пробелы (например они стоят как тысячные разделители внутри больших чисел), то можно использовать ту же замену: нажать Ctrl+H, в первую строку ввести пробел, во вторую ничего не вводить и нажать кнопку Заменить все (Replace All).
Однако, часто возникает ситуация, когда удалить надо не все подряд пробелы, а только лишние – иначе все слова слипнутся друг с другом. В арсенале Excel есть специальная функция для этого – СЖПРОБЕЛЫ (TRIM) из категории Текстовые. Она удаляет из текста все пробелы, кроме одиночных пробелов между словами, т.е. мы получим на выходе как раз то, что нужно:
Удаление непечатаемых символов
В некоторых случаях, однако, функция СЖПРОБЕЛЫ (TRIM) может не помочь. Иногда то, что выглядит как пробел – на самом деле пробелом не является, а представляет собой невидимый спецсимвол (неразрывный пробел, перенос строки, табуляцию и т.д.). У таких символов внутренний символьный код отличается от кода пробела (32), поэтому функция СЖПРОБЕЛЫ не может их «зачистить».
Вариантов решения два:
- Аккуратно выделить мышью эти спецсимволы в тексте, скопировать их (Ctrl+C) и вставить (Ctrl+V) в первую строку в окне замены (Ctrl+H). Затем нажать кнопку Заменить все (Replace All) для удаления.
- Использовать функцию ПЕЧСИМВ (CLEAN) . Эта функция работает аналогично функции СЖПРОБЕЛЫ, но удаляет из текста не пробелы, а непечатаемые знаки. К сожалению, она тоже способна справится не со всеми спецсимволами, но большинство из них с ее помощью можно убрать.
Функция ПОДСТАВИТЬ
Замену одних символов на другие можно реализовать и с помощью формул. Для этого в категории Текстовые в Excel есть функция ПОДСТАВИТЬ (SUBSTITUTE) . У нее три обязательных аргумента:
- Текст в котором производим замену
- Старый текст – тот, который заменяем
- Новый текст – тот, на который заменяем
С ее помощью можно легко избавиться от ошибок (замена «а» на «о»), лишних пробелов (замена их на пустую строку «»), убрать из чисел лишние разделители (не забудьте умножить потом результат на 1, чтобы текст стал числом):
Удаление апострофов в начале ячеек
Апостроф (‘) в начале ячейки на листе Microsoft Excel – это специальный символ, официально называемый текстовым префиксом. Он нужен для того, чтобы дать понять Excel, что все последующее содержимое ячейки нужно воспринимать как текст, а не как число. По сути, он служит удобной альтернативой предварительной установке текстового формата для ячейки (Главная – Число – Текстовый) и для ввода длинных последовательностей цифр (номеров банковских счетов, кредитных карт, инвентарных номеров и т.д.) он просто незаменим. Но иногда он оказывается в ячейках против нашей воли (после выгрузок из корпоративных баз данных, например) и начинает мешать расчетам. Чтобы его удалить, придется использовать небольшой макрос. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert — Module) и введите туда его текст:
Теперь, если выделить на листе диапазон и запустить наш макрос (Alt+F8 или вкладка Разработчик – кнопка Макросы), то апострофы перед содержимым выделенных ячеек исчезнут.
Английские буквы вместо русских
Это уже, как правило, чисто человеческий фактор. При вводе текстовых данных в ячейку вместо русских букв случайно вводятся похожие английские («це» вместо русской «эс», «игрек» вместо русской «у» и т.д.) Причем снаружи все прилично, ибо начертание у этих символов иногда абсолютно одинаковое, но Excel воспринимает их, конечно же, как разные значения и выдает ошибки в формулах, дубликаты в фильтрах и т.д.
Можно, конечно, вручную заменять символы латинцы на соответствующую им кириллицу, но гораздо быстрее будет сделать это с помощью макроса. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert — Module) и введите туда его текст:
Теперь, если выделить на листе диапазон и запустить наш макрос (Alt+F8 или вкладка Разработчик – кнопка Макросы), то все английские буквы, найденные в выделенных ячейках, будут заменены на равноценные им русские. Только будьте осторожны, чтобы не заменить случайно нужную вам латиницу 🙂
Для удаления всех скобок из текстовых строк в выбранном диапазоне мы предлагаем вам три метода.
Удаление скобок из строки с помощью функции поиска и замены
Освободи Себя Найти и заменить функция может заменить все скобки пробелами в выбранном диапазоне.
1. Выберите диапазон скобок, который необходимо удалить, а затем нажмите кнопку Ctrl + F клавиши одновременно, чтобы открыть Найти и заменить диалоговое окно.
2. в Найти и заменить диалогового окна, перейдите к Заменять вкладку, введите левую часть скобок в Найти то, что коробка, держи Заменить пустое поле, а затем щелкните значок Заменить все кнопка. Смотрите скриншот:
3. В следующем всплывающем диалоговом окне щелкните значок OK кнопку.
4. Не закрывайте Найти и заменить диалоговом окне и повторите шаги 2–3, указанные выше, чтобы удалить оставшуюся правую часть скобок «]». И, наконец, закройте диалоговое окно «Найти и заменить».
Легко удалить все скобки или указанные символы из ячеек в Excel:
Kutools for Excel’s Удалить символы Утилита поможет вам легко удалить все скобки или указанные символы из выбранных ячеек в Excel, как показано в демонстрации ниже. Скачайте и попробуйте прямо сейчас! (30-дневная бесплатная трасса)
Удалите скобки из строки с помощью пользовательской функции
Вы можете удалить скобки из ячейки с помощью пользовательской функции. Пожалуйста, сделайте следующее.
1. Нажмите другой + F11 клавиши одновременно, чтобы открыть Microsoft Visual Basic для приложений окно.
2. в Приложение Microsoft Visual Basic окна, нажмите Вставить > Модуль. Затем скопируйте и вставьте приведенный ниже код VBA в окно модуля.
Код VBA: удалить скобки из строки
3. Нажмите другой + Q одновременно нажмите клавиши, чтобы закрыть окно Microsoft Visual Basic для приложения.
4. Выберите пустую ячейку (говорит ячейка B2) для поиска результата после удаления скобок, введите формулу = Без скобок (A2) в панель формул, а затем нажмите Enter ключ.
Внимание: A2 — ячейка содержит скобки, которые необходимо удалить.
5. Продолжайте выбирать ячейку B2, затем перетащите маркер заполнения в ячейку для применения этой формулы.
Удалите все скобки из строки в диапазоне с помощью Kutools for Excel
Y вы можете легко удалить все скобки из текстовых строк в выбранном диапазоне сразу с помощью Удалить символы полезности Kutools for Excel. Пожалуйста, сделайте следующее.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Выберите диапазон скобок, который необходимо удалить, затем нажмите Kutools > Текст > Удалить символы. Смотрите скриншот:
2. в Удалить символы диалоговое окно, проверьте На заказ поле и введите скобки в текстовое поле, затем щелкните значок OK кнопку.
Теперь все скобки в текстовых строках в выбранном диапазоне немедленно удаляются.
Если вы хотите получить 30-дневную бесплатную пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Если у вас есть несколько данных, и некоторые из них заключены в квадратные или круглые скобки, теперь вы хотите удалить текст в скобках, как показано на скриншотах ниже, как вы можете быстро решить эту проблему?
Удалить текст в круглых скобках на нескольких листах с помощью Kutools for Excel
Удалите скобки с помощью Kutools for Excel
Удалите текст в круглых скобках с помощью функции поиска и замены
Чтобы удалить текст, заключенный в скобки, вы можете найти их, а затем заменить их пустыми.
1. Нажмите Ctrl + H ключи для включения Найти и заменить диалог, в Найти то, что текстовое поле, тип (*) в него и перейдите к Заменить текстовое поле, тип () внутрь. Смотрите скриншот:
2. Затем нажмите Заменить все. Затем появляется диалоговое окно с напоминанием о количестве замен. Нажмите OK закрыть его.
Удалить текст в круглых скобках на нескольких листах с помощью Kutools for Excel
Если вы открыли несколько книг и вам нужно удалить весь текст в скобках в открытых книгах, вы можете использовать Kutools for Excelпродвинутый Найти и заменить для поиска и замены текста в книгах.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Нажмите Kutools > Навигация включить Навигация панель, затем щелкните Найти и заменить значок для перехода Найти и заменить раздел. Смотрите скриншоты:
3. Затем выберите Заменять и в Найти то, что текстовое поле, тип (*) в него и перейдите к Заменить текстовое поле, тип () в него и выберите Все книги под В раскрывающийся список, см. снимок экрана:
Наконечник: Если вы хотите заменить на определенных листах, вы можете проверить имена листов в Книги .
4. Нажмите Заменить все. Затем все тексты в скобках заменяются пустыми.
Работы С Нами Навигация панели, вы можете перечислить все имена открытых листов, имена диапазонов и добавить автоматический текст по своему усмотрению. Щелкните здесь, чтобы узнать больше о навигации.
Удалить содержимое в круглых скобках
Удалите скобки с помощью Kutools for Excel
Если вы хотите удалить скобки или скобки, вы можете использовать Kutools for ExcelАвтора Удалить символы.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Выберите диапазон данных, для которого нужно удалить скобки, и нажмите Kutools > Текст > Удалить символы. Смотрите скриншот:
2. в Удалить символы диалог, только проверка На заказ вариант, а затем введите () в текстовое поле, вы можете просмотреть результат в предварительный просмотр панель. Смотрите скриншот:
3. Нажмите Ok or Применить. Теперь все скобки выделения сняты.
Про то, как можно быстро склеивать текст из нескольких ячеек в одну и, наоборот, разбирать длинную текстовую строку на составляющие я уже писал. Теперь же давайте рассмотрим близкую, но чуть более сложную задачу — как склеивать текст из нескольких ячеек при выполнении определенного заданного условия.
Допустим, что у нас имеется база данных по клиентам, где одному названию компании может соответствовать несколько разных email’ов ее сотрудников. Наша задача состоит в том, чтобы собрать все адреса по названиям компаний и сцепить их (через запятую или точку с запятой), чтобы сделать потом, например, почтовую рассылку по клиентам, т.е. получить на выходе что-то похожее на:
Другими словами, нам нужен инструмент, который будет склеивать (сцеплять) текст по условию — аналог функции СУММЕСЛИ (SUMIF) , но для текста.
Способ 0. Формулой
Не очень изящный, зато самый простой способ. Можно написать несложную формулу, которая будет проверять отличается ли компания в очередной строке от предыдущей. Если не отличается, то приклеиваем через запятую очередной адрес. Если отличается, то «сбрасываем» накопленное, начиная заново:
Минусы такого подхода очевидны: из всех ячеек полученного дополнительного столбца нам нужны только последние по каждой компании (желтые). Если список большой, то чтобы их быстро отобрать придется добавить еще один столбец, использующий функцию ДЛСТР (LEN) , проверяющий длину накопленных строк:
Теперь можно отфильтровать единички и скопировать нужные склейки адресов для дальнейшего использования.
Способ 1. Макрофункция склейки по одному условию
Если исходный список не отсортирован по компаниям, то приведенная выше простая формула не работает, но можно легко выкрутиться с помощью небольшой пользовательской функции на VBA. Откройте редактор Visual Basic нажатием на сочетание клавиш Alt+F11 или с помощью кнопки Visual Basic на вкладке Разработчик (Developer) . В открывшемся окне вставьте новый пустой модуль через меню Insert — Module и скопируйте туда текст нашей функции:
Если теперь вернуться в Microsoft Excel, то в списке функций (кнопка fx в строке формул или вкладка Формулы — Вставить функцию) можно будет найти нашу функцию MergeIf в категории Определенные пользователем (User Defined) . Аргументы у функции следующие:
Способ 2. Сцепить текст по неточному условию
Если заменить в 13-й строчке нашего макроса первый знак = на оператор приблизительного совпадения Like, то можно будет осуществлять склейку по неточному совпадению исходных данных с критерием отбора. Например, если название компании может быть записано в разных вариантах, то мы можем одной функцией проверить и собрать их все:
Поддерживаются стандартные спецсимволы подстановки:
По умолчанию оператор Like регистрочувствительный, т.е. понимает, например, «Орион» и «оРиОн» как разные компании. Чтобы не учитывать регистр можно добавить в самое начало модуля в редакторе Visual Basic строчку Option Compare Text, которая переключит Like в режим, когда он невосприимчив к регистру.
Таким образом можно составлять весьма сложные маски для проверки условий, например:
Способ 3. Макрофункция склейки текста по двум условиям
В работе может встретиться задача, когда сцеплять текст нужно больше, чем по одному условию. Например представим, что в нашей предыдущей таблице добавился еще один столбец с городом и склеивание нужно проводить не только для заданной компании, но еще и для заданного города. В этом случае нашу функцию придется немного модернизировать, добавив к ней проверку еще одного диапазона:
Применяться она будет совершенно аналогично — только аргументов теперь нужно указывать больше:
Способ 4. Группировка и склейка в Power Query
Решить проблему можно и без программирования на VBA, если использовать бесплатную надстройку Power Query. Для Excel 2010-2013 ее можно скачать здесь, а в Excel 2016 она уже встроена по умолчанию. Последовательность действий будет следующей:
Power Query не умеет работать с обычными таблицами, поэтому первым шагом превратим нашу таблицу в «умную». Для этого ее нужно выделить и нажать сочетание Ctrl + T или выбрать на вкладке Главная — Форматировать как таблицу (Home — Format as Table) . На появившейся затем вкладке Конструктор (Design) можно задать имя таблицы (я оставил стандартное Таблица1):
Теперь загрузим нашу таблицу в надстройку Power Query. Для этого на вкладке Данные (если у вас Excel 2016) или на вкладке Power Query (если у вас Excel 2010-2013) жмем Из таблицы (Data — From Table) :
В открывшемся окне редактора запросов выделяем щелчком по заголовку столбец Компания и сверху жмем кнопку Группировать (Group By) . Вводим имя нового столбца и тип операции в группировке — Все строки (All Rows) :
Жмем ОК и получаем для каждой компании мини-таблицу сгруппированных значений. Содержимое таблиц хорошо видно, если щелкать левой кнопкой мыши в белый фон ячеек (не в текст!) в получившемся столбце:
Теперь добавим еще один столбец, где с помощью функции склеим через запятую содержимое столбцов Адрес в каждой из мини-таблиц. Для этого на вкладке Добавить столбец жмем Пользовательский столбец (Add column — Custom column) и в появившемся окне вводим имя нового столбца и формулу сцепки на встроенном в Power Query языке М:
Обратите внимание, что все М-функции регистрочувствительные (в отличие от Excel). После нажатия на ОК получаем новый столбец со склееными адресами:
Осталось удалить ненужный уже столбец ТаблАдресов (правой кнопкой мыши по заголовку — Удалить столбец) и выгрузить результаты на лист, нажав на вкладке Главная — Закрыть и загрузить (Home — Close and load) :
Важный нюанс : в отличие от предыдущих способов (функций), таблицы из Power Query не обновляются автоматически. Если в будущем произойдут какие-либо изменения в исходных данных, то нужно будет щелкнуть правой кнопкой в любое место таблицы результатов и выбрать команду Обновить (Refresh) .
Читайте также:
- Программа егаис как работать 1с
- Решение транспортной задачи в excel видео
- Зафиксировать положение на странице в ворд
- Hvc1 формат чем открыть
- Автокад зависает при запуске