Хитрости »
Основные понятия (22)Здесь собраны статьи, в которых разъясняются базовые понятия работы в Excel и VBA, а так же проблемы, с которыми сталкивается большинство начинающих
Сводные таблицы и анализ данных (5) Раздел поможет изучить сводные таблицы и научиться их использовать "на полную"
Графики и диаграммы (4) Раздел поможет научиться создавать диаграммы и графики в Excel, в том числе нестандартные
Работа с VB проектом (10) С помощью статей раздела вы научитесь создавать процедуры программно и выполнять различные операции с объектами самого VBA
Power BI и Power Query (5) Здесь собраны статьи, раскрывающие различные возможности мощнейшего инструмента для визуализаций бизнесс-процессов Power BI и надстройки для Excel Power Query
Условное форматирование (5) Этот раздел поможет поближе познакомиться с Условным форматированием на примерах различных ситуаций
Списки и диапазоны (5) Статьи, посвященные работе не только с выпадающими списками, но и с диапазонами и хитростями их применения в рабочих файлах
Макросы(VBA процедуры) (59) Статьи раздела направлены на изучение VBA с детальным разбором кодов. Множество статей с примерами кодов под всевозможные ситуации с комментариями и пояснениями
Разное (34) Собраны статьи, которые не подходят ни под одну из представленных выше категорий или входят сразу в несколько. Но эти статье не менее полезные!

Как сцепить несколько значений в одну ячейку по критерию? СцепитьЕсли

Все чаще вижу на разных форумах вопросы типа: Есть таблица, в одном столбце фамилии, в другом оценки(виды работ и т.д.). Как сцепить в одной ячейке для каждой фамилии только принадлежащие ей оценки? Стандартными функциями это сделать весьма проблематично, т.к. заранее неизвестно сколько будет этих оценок. Вот и решил написать небольшую функцию пользователя на VBA, которая решает данную проблему. Так же подобную функцию называют "многоразовый ВПР", потому что она по критерию возвращает ВСЕ значения для этого критерия, а не только первое.

Для применения функции в своем файле достаточно создать стандартный модуль (о том как создать стандартный модуль: Что такое модуль? Какие бывают модули?) и просто вставить приведенный код. После этого в диспетчере функций появиться новая категория (если до этого её не было) - Определенные пользователем (User Defined). В ней эта функция - СцепитьЕсли.
Синтаксис:
=СцепитьЕсли(B5:B18;B5;C5:C18;"-";0)

По принципу работы функция похожа на стандартную СУММЕСЛИ. Указывается диапазон значений(где просматривать значение), критерий и диапазон значений для сцепления. Символ для разделения слов указывать необязательно.

Диапазон(B5:B18) - диапазон, в котором искать критерий(указывается один столбец)

Критерий(B5) - критерий. Значение, на основании которого необходимо сцеплять значения. Может содержать символы подстановки - * и ? и символы сравнения (<>"", <23, >0, "<>"&A1 и т.п.). Просматривается Диапазон. При совпадении значения ячейки в Диапазоне значение из Диапазона_Сцепления добавляется к результату с выбранным разделителем.

Диапазон_сцепления(C5:C18) - из этого диапазона берется значение для сцепления, если значение в аргументе Диапазон совпадает с аргументом Критерий(указывается один столбец). Если в Диапазоне значение 5-ой строки совпадает с критерием, то из Диапазона_Сцепления будет взято так же значение из 5-ой строк этого диапазона и сцеплено с результатом.

Разделитель("-") - По умолчанию пробел, но можно задать любой другой символ или группу символов.

БезПовторов - если указать 1 или ИСТИНА, то в результате получится строка, в которой нет одинаковых значений. Если указать 0 или ЛОЖЬ, то будут выведены все значения. По умолчанию значение ЛОЖЬ.

Примечание: для работы функции должны быть разрешены макросы

Скачать пример

  Tips_Macro_CoupleIf.xls (49,0 KiB, 5 832 скачиваний)

Также см.:
ВПР_МН
Сцепить_МН
СцепитьЕсли
Что такое функция пользователя(UDF)?


Статья помогла? Сделай твит, поделись ссылкой с друзьями!

Поиск по меткам

Access Multex Outlook Power BI Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Настройка Поиск данных Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx вебинар ссылки статьи тренинг
Обсуждение: оставлено 45 коммент.
  1. Ваня:

    Подскажите пожалуйста, а как сделать чтоб выводились повторы даже из одной ячейки?

  2. Ваня, Вы сами свой вопрос поняли? Что значит "выводить повторы из одной ячейки"? Функция выводит значение ячеек полностью, значит и повторы внутри неё. И к тому же - что будет критерием для этой одной ячейки?

  3. Владимир:

    Добавил новый модуль вставил код, функция "СцепитьЕсли" появилась. Но в результате при вводе формулы появляется ошибка "Syntax error" и подсвечивает в коде "If Диапазон.Count > 1 Then". Что я не так сделал? Когда я ввожу свои данные в Ваш файл, то все работает, а в моем созданном нет.

  4. Владимир:

    Дополнение к выше написанному: Другие функции (коды взяты с этого сайта) добавлял в свой файл, работают. А СцепитьЕсли нехочет. Прошу помощи.

  5. Владимир:

    Большое спасибо, все заработало.

  6. Михаил:

    РАБОТАЕТ!!!
    КРУТО!!!
    АПЛОДИРУЮ СТОЯ!!!

  7. Rashid:

    Замечательный макрос. А вот как бы его подружить с MacOS?
    Я так понимаю нужно как-то обойтись без VBScript.RegExp и
    Scripting.Dictionary. Но вот как?

  8. Николай:

    Дмитрий добрый день! Что нужно поменять в коде чтобы СЦЕПИТЬ НЕСКОЛЬКО ЗНАЧЕНИЙ В ОДНУ ЯЧЕЙКУ ПО КРИТЕРИЮ объединения ячеек. То есть в одном столбце есть объединенные ячейки по 2 и по 3 ячейки, также есть и простые ячейки без объединения. По этому критерию нужно сцепить значения в другом столбце, соответственно 2 ячейки - напротив объединненый 2-х, 3 ячейки - напротив 3-х объединных. Ячейки напротив необЪединенных ячеек просто перенести.
    Возможно ли такое?

  9. Алексей Ф:

    Подскажите, пожалуйста,

    как в качестве разделителя использовать:
    вначале нумерацию, затем значение подобранное по условию, затем точку с запятой,

  10. Алексей:

    Огромное человеческое спасибо за то, что Вы делаете! )))

Поделитесь своим мнением

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


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

Логин
Наши партнеры
Перейти
Перейти
Счетчики
Анализ сайта

Яндекс.Метрика
© 2016 Excel для всех  Войти