MulTEx »

СцепитьЕсли

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

MulTEx

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

 

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

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

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

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

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

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

Синтаксис:
=СцепитьЕсли(Лист1!$B$9:$B$38;"<>"&D$1;Лист1!$A$9:$A$38;"-";1;Лист1!$B$9:$B$38;"*продажа*")



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

Критерий("<>"&D$1) - ссылка на ячейку или непосредственно значение, с которым сравниваем значения в Диапазоне. Может содержать символы подстановки - * и ?. Просматривается Диапазон. Помимо просто текстовых критериев, можно указывать символы сравнения (<>"", <23, >0, "<>"&A1, >1 и т.п.). Удобно когда необходимо получить список из значений, если критерием является число больше 100, например. Можно указывать значение, объединяя со значением из ячейки: "<>"&D$1. Если предположить, что в ячейке D1 записано значение 23, то функция объединит в одну строку все ячейки диапазона $A$9:$A$38 напротив которых в ячейках диапазона $B$9:$B$38 значение будет не равно 23.

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

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

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

ДиапазонКритерий(Лист1!$B$9:$B$38;"*продажа*") - дополнительные диапазоны и критерии для отбора. Важно: сначала указывается диапазон, затем критерий, который в этом диапазоне просматривать. Условия использования те же, что и для критериев Диапазон и Критерий. Допускается до 125 связок Диапазон-Критерий(т.е. всего 250 аргументов - до 125 Дипазонов и до 125 относящихся к ним Критериев). Все условия объединены между собой по принципу совпадения И. Если хоть одно условие связки ДиапазонКритерий не выполняется, условия расположенные далее по списку не проверяются, общее условие по строке считается как несоответствующее и данные этой строки не будут сцеплены.


Расскажи друзьям, если статья оказалась полезной:
Обсуждение: оставлено 13 коммент.
  1. Роман:

    Добрый день господа,
    помогите плиз - как установить фукцию СцепитьЕсли.
    Как можно подробнее опишите плиз.

    С уважением,
    Роман

  2. Роман, в самом верху статьи написано ВОТ ТАКИМИ БУКВАМИ:
    ДАННАЯ ФУНКЦИЯ ЯВЛЯЕТСЯ ЧАСТЬЮ НАДСТРОЙКИ MULTEX
    и ссылка на MulTEx с описанием установки...

  3. Роман:

    Дмитрий, добрый день.
    Большое спасибо за ответ.
    Формулу СцепитьЕсли - скачал (програмку в модуль листа Ексель). Она работает, но счепляет все значения, а не только уникальные,как описано в комментариях. Подскажите плиз что сделать чтоб сцеплялись только уникальные значения. В формуле нет дополнительного необязательного поля чтоб поставить в него 1 или ИСТИНА.
    Приложение Мултекс не могу скачать с сайта - пишет ошибку.
    Извините если что то не так.
    Заранее благодарен за ответ.
    С уважением к Вам

    • Роман - Вы хоть напишите откуда скачали. Если с моего сайта - то там есть параметр, отвечающий за отсечение повторов(БезПовторов). Если откуда-то еще - то вопрос надо задавать там, откуда скачали.
      А если при скачивании MulTEx ошибка - проблема явно у Вас на ПК(возможно политикой безопасности организации запрещено скачивание исполняемых файлов, даже в архиве).

  4. Роман:

    Еще раз здравствуйте Дмитрий,
    Скачал формулу СцепитьЕсли с форума планета Ексель - но там была ссылка что это ваш продукт.
    Мультекст скачал с Вашего сайта. Ексель стал долго открываться и закрываться. При этом пишет что Мултекст только для чтения. Это можно как то исправить?
    После установки Мултекст - в формуле появился раздел без повторов, но ставлю в него 1 или Истина как у Вас написано - формула не работает.
    Помогите разобраться плиз.

    С уважением,

  5. В функции MulTEx попробуйте указать в обязательном порядке все аргументы: Разделитель и БезПовторов.

    Плюс у меня здесь же на сайте есть статья: http://www.excel-vba.ru/chto-umeet-excel/kak-scepit-neskolko-znachenij-v-odnu-yachejku-po-kriteriyu-scepitesli/
    Почитайте, ознакомьтесь с примером. Все тоже работает.
    По поводу "после установки MulTEx появляется сообщение, что MulTEx.xla только для чтения" - нечего пока сказать, при запуске ничего такого не происходит. Возможно, у Вас нет доступа к определенным папкам(бывает, если надстройку устанавливали с правами локального админа).

    • Роман:

      Спасибо за информацию и помощь Дмитрий.
      Все - формула сцепитьесли заработала.
      Еще подскажите плиз. - где посмотреть такую функцию.
      На одном листе добавлется в колонку информация текстовая но она повторяется, а на другом листе в таблице автоматом добавляется только та текстовая информация в столбец которой не было еще в первоначальной таблице.
      Такое возможно вообще.
      Заранее благодарен за содействие.
      С уважением,

  6. Роман:

    Спасибо огромное Дмитрий за направление в русло поиска.
    С уважением,

  7. Роман:

    Добрый день Дмитрий,
    Извините за назойливость.
    Вот что я нашел на Мир МС Ексель: На сайте http://www.excel-vba.ru The_Prist (Щербаков Дмитрий) нашёл формулу вывода уникальных значений из столбца в назначенный столбец. К сообщению приложен файл: 0258669.xls(38Kb.
    Это почти то что надо. Подскажите плиз как сделать чтоб уникальные значения выбирались с другого листа, а не с одного. На одном листе все значения, на втором только уникальные значения с первого листа.
    Заранее благодарен.

    --------------------------------------------------------------------------------

  8. Роман:

    Добрый день Дмитрий,
    Это опять я.
    Скачал Ваш пример на сайте: Tips_All_ExtractUnique. Но не получается у меня сделать чтобы формула выбора уникальных значений работала у меня с разных листов ( Вашем файле она работает с разных листов). Формулу пробовал и копировал и вручную забивал не получается. При вводе формулы массива ексель пишет "Обновить значения: отчет".И все стопорится.
    Дмитрий окажите плиз содействие чайнику. Напишите подробно что необходимо сделать мне в своем файле чтобы формула ваша заработала. И сообщите сколько Ваша услуга будет стоить и куда перечислить деньги за Вашу услугу.
    Выручите плиз.
    С уважением,

    • Роман, Вы решили все свои проблемы адресовать лично мне именно в комментариях к этой статье? Есть форум - создайте там тему, приложите файл и ждите. Или, если уж так срочно надо и именно моя консультация: на сайте есть мои контакты, можете написать мне на личную почту. А то Вы пишите сюда комментарии:
      1. Не имеющие отношения к статье. Это впоследствии будет мешать другим посетителям, когда они будут искать ответ на вопрос по материалу статьи.
      2. Без примеров Вашей формулы рассказать подробно как надо сделать в ВАШЕМ ФАЙЛЕ и что не так практически нереально.

  9. Роман:

    Я Вас понял Дмитрий,
    Извините за засорение форума.
    Пишу Вам в раздел заказов.

    С уважением.

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

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


Для оформления сообщений Вы можете использовать следующие тэги:
<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 для всех  Войти