MulTEx »

СцепитьЕсли

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

Описание, установка, удаление и обновление Полный список команд и функций 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")