Lost your password?


MulTEx »

СцепитьЕсли

Данная функция является частью надстройки MulTEx

MulTEx

Объединение текстовых данных в одной ячейке на основе критерия

 

Функция сцепляет в одну строку(ячейку) только ячейки, которые отвечают какому-либо критерию.
Например, необходимо собрать в одну ячейку через запятую фамилии всех сотрудников одного отдела, даже если все сотрудники идут вразнобой:
Исходная таблица
И из этой таблицы надо получить что-то вроде такой:
Результат
Или есть список проектов и этапы по каждому проекту. Для каждого этапа одна строка и необходимо для каждого проекта записать в одну ячейку через запятую все этапы.
Стандартными функциями такие вещи сделать весьма проблематично, т.к. заранее неизвестно сколько будет этих оценок или этапов. Да даже если известно - не так просто это сделать. Через функцию СЦЕПИТЬ(CONCANTENATE)? Для этого для начала потребуется пощелкать по каждой ячейке со значениями.
Функции СцепитьЕсли поможет сделать это в разы быстрее и проще. По принципу работы функция очень похожа на стандартную СУММЕСЛИ(SUMIF). Только она не суммирует значения диапазона, а сцепляет их через разделитель. Указываете диапазон значений (отделы), критерий (конкретный отдел) и диапазон значений для сцепления (сотрудники). Символ для разделения слов может быть как любым символом так и группой символов(т.е. несколько символов или целые слова и фразы).
Плюс можно добавить к сравнению более чем 50 доп.условий.

Вызов команды через стандартный диалог:

Мастер функций-Категория "MulTEx"- СцепитьЕсли

Вызов с панели MulTEx:

Сумма/Поиск/Функции - Текстовые - СцепитьЕсли

Синтаксис:
=СцепитьЕсли($A$2:$A$38;A1;$B$2:$B$38;", ";1)
=СцепитьЕсли($A$2:$A$38;A1;$B$2:$B$38;", ";ИСТИНА)
=СцепитьЕсли($A$2:$A$38;A1;$B$2:$B$38;"; ";ЛОЖЬ;$C$2:$C$38;">=1984")
=СцепитьЕсли($A$2:$A$38;A1;$B$2:$B$38;"; ";1;$C$2:$C$38;">="&D$1)
=СцепитьЕсли($A$2:$A$38;A1;$B$2:$B$38;" + ";1;$E$2:$E$38;"*руководитель*")


Диапазон($A$2:$A$38) - диапазон с критериями(указывается один столбец).

Критерий(A1) - ссылка на ячейку или непосредственно значение, с которым сравниваем значения в Диапазоне. В критерии допускается указание специальных символов подстановки - * и ?. Т.е. указав в качестве Критерия "*план*" Вы сможете отобрать для сцепления ячейки со значениями, в столбце критериев которых встречается слово "план"(план, данные по плановым показателя и т.п.). Подробнее про подстановочные символы >>
Так же данный аргумент может принимать в качестве критерия символы сравнения(<, >, =, <>, <=, =>):

  • ">0" - будут объединены все ячейки в столбце сцепления, значения ячеек критериев для которых больше нуля;
  • ">=2" - будут объединены все ячейки в столбце сцепления, значения ячеек критериев для которых больше или равно двум;
  • "<0" - будут объединены все ячейки в столбце сцепления, значения ячеек критериев для которых меньше нуля;
  • "<=60" - будут объединены все ячейки в столбце сцепления, значения ячеек критериев для которых меньше или равно 60;
  • "<>0" - будут объединены все ячейки в столбце сцепления, значения ячеек критериев для которых не равно нулю;
  • "<>" - будут объединены все ячейки в столбце сцепления, значения ячеек критериев для которых не пустые;
  • Вместо нуля может быть любое число или текст. Так же можно добавить ссылку на ячейку со значением: ">="&A1

Например, если необходимо отобрать и записать через точку-с-запятой и пробел(; ) только тех сотрудников, чей год рождения 1984 и выше(при условии, что год рождения записан в диапазоне $C$2:$C$38), то функцию можно записать так:
=СцепитьЕсли($C$2:$C$38;">=1984";$B$2:$B$38;"; ";ИСТИНА)

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

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

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

ДиапазонКритерий($C$2:$C$38;">1984") - необязательный. Дополнительные диапазоны и критерии для отбора. Важно: при указании очень важен порядок следования: сначала обязательно указывается диапазон и только потом критерий, который в этом диапазоне просматривать. Если указать в другом порядке - функция выдаст ошибку. Если указан диапазон - то обязательно указывать и критерий.
Условия использования те же, что и для критериев Диапазон и Критерий. Т.е. допускается использование подстановочных символов и операторов сравнения.
Допускается указывать до 125 связок Диапазон-Критерий(т.е. всего 250 аргументов - до 125 Диапазонов и до 125 относящихся к ним Критериев). Все условия объединены между собой по принципу совпадения И. Если хоть одно условие связки ДиапазонКритерий не выполняется, условия расположенные далее по списку не проверяются, общее условие по строке считается как несоответствующее и данные этой строки не будут сцеплены.
Например, если необходимо отобрать только тех сотрудников, которые работают в плановом отделе и чей год рождения 1984 и выше(при условии, что год рождения записан в диапазоне $B$2:$B$38), то функцию можно записать так:
=СцепитьЕсли($A$2:$A$38;A1;$B$2:$B$38;"; ";ЛОЖЬ;$E$2:$E$38;">=1984")


Расскажи друзьям, если статья оказалась полезной:
  Плейлист   Видеоинструкции по использованию надстройки MulTEx
Обсуждение: 17 комментариев
  1. Айдар:

    При вводе этой формулы выдает #знач, подскажите в чем может быть проблема устал воевать
    =СцепитьЕсли('Formen RAPORT'!J7:J49;("mid coat");'Formen RAPORT'!H7:I7;".";ЛОЖЬ;".")

    • Айдар, зачем скобки в условии? И что за "." последним аргументом? Что Вы хотели этой точкой сказать формуле? После ЛОЖЬ идет связка аргументов ДиапазонКритерий, которые указываются обязательно попарно и только в порядке: диапазон -критерий. Т.е. нечто вроде:'Formen RAPORT'!I7:I49;"condition", где 'Formen RAPORT'!I7:I49 - диапазон где просматривать доп.значение для поиска, а "condition" - значение, которое надо в этом диапазоне найти.

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

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


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

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2024 Excel для всех   Войти