Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/СЦЕПИТЬ в одну ячейку формулой массива - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_, DrMini  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » СЦЕПИТЬ в одну ячейку формулой массива
СЦЕПИТЬ в одну ячейку формулой массива
Logotip Дата: Пятница, 03.11.2017, 09:45 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 23
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Всем привет!
Подскажите, пожалуйста, есть ли решение задачи:
Нужно собрать данные по условию в одну ячейку при помощи формулы массива.

Пример во вложении.

Спасибо!

P.S. ЮПД не хочется использовать, так же как и дополнительные ячейки. Если без этого ни как не решить задачу, то подскажите плз любой вариант.
К сообщению приложен файл: 4368410.xlsx (8.8 Kb)
 
Ответить
СообщениеВсем привет!
Подскажите, пожалуйста, есть ли решение задачи:
Нужно собрать данные по условию в одну ячейку при помощи формулы массива.

Пример во вложении.

Спасибо!

P.S. ЮПД не хочется использовать, так же как и дополнительные ячейки. Если без этого ни как не решить задачу, то подскажите плз любой вариант.
Автор - Logotip
Дата добавления - 03.11.2017 в 09:45
_Boroda_ Дата: Пятница, 03.11.2017, 10:03 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 16881
Репутация: 6593 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Если столбцов 5 и без допячеек и без UDF (Вы это имели в виду, написав ЮПД?), то вот так
Код
=ПСТР(ЕСЛИ(ЕЧИСЛО(ПОИСК(", "&M$1&",";", "&A2&","));", "&A$1;"")&ЕСЛИ(ЕЧИСЛО(ПОИСК(", "&M$1&",";", "&B2&","));", "&B$1;"")&ЕСЛИ(ЕЧИСЛО(ПОИСК(", "&M$1&",";", "&C2&","));", "&C$1;"")&ЕСЛИ(ЕЧИСЛО(ПОИСК(", "&M$1&",";", "&D2&","));", "&D$1;"")&ЕСЛИ(ЕЧИСЛО(ПОИСК(", "&M$1&",";", "&E2&","));", "&E$1;"");3;999)

На каждый новый столбец добавляете кусок типа
Код
&ЕСЛИ(ЕЧИСЛО(ПОИСК(", "&M$1&",";", "&E2&","));", "&E$1;"")

Еще в файле покрасия нужные ячейки Условным форматированием
Ячейку М1 можно менять
К сообщению приложен файл: 4368410_1.xlsx (11.0 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЕсли столбцов 5 и без допячеек и без UDF (Вы это имели в виду, написав ЮПД?), то вот так
Код
=ПСТР(ЕСЛИ(ЕЧИСЛО(ПОИСК(", "&M$1&",";", "&A2&","));", "&A$1;"")&ЕСЛИ(ЕЧИСЛО(ПОИСК(", "&M$1&",";", "&B2&","));", "&B$1;"")&ЕСЛИ(ЕЧИСЛО(ПОИСК(", "&M$1&",";", "&C2&","));", "&C$1;"")&ЕСЛИ(ЕЧИСЛО(ПОИСК(", "&M$1&",";", "&D2&","));", "&D$1;"")&ЕСЛИ(ЕЧИСЛО(ПОИСК(", "&M$1&",";", "&E2&","));", "&E$1;"");3;999)

На каждый новый столбец добавляете кусок типа
Код
&ЕСЛИ(ЕЧИСЛО(ПОИСК(", "&M$1&",";", "&E2&","));", "&E$1;"")

Еще в файле покрасия нужные ячейки Условным форматированием
Ячейку М1 можно менять
Автор - _Boroda_
Дата добавления - 03.11.2017 в 10:03
Logotip Дата: Пятница, 03.11.2017, 10:24 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 23
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Цитата _Boroda_, 03.11.2017 в 10:03, в сообщении № 2 ()
Если столбцов 5 и без допячеек и без UDF (Вы это имели в виду, написав ЮПД?), то вот так


Да, именно UDF имел ввиду.

Спасибо большое за решение.
А можно как-то решить эту задачу через формулу массива?
 
Ответить
Сообщение
Цитата _Boroda_, 03.11.2017 в 10:03, в сообщении № 2 ()
Если столбцов 5 и без допячеек и без UDF (Вы это имели в виду, написав ЮПД?), то вот так


Да, именно UDF имел ввиду.

Спасибо большое за решение.
А можно как-то решить эту задачу через формулу массива?
Автор - Logotip
Дата добавления - 03.11.2017 в 10:24
_Boroda_ Дата: Пятница, 03.11.2017, 10:41 | Сообщение № 4
Группа: Админы
Ранг: Местный житель
Сообщений: 16881
Репутация: 6593 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Можно. Введите мою формулу не Ентером, а одновременным нажатием Контрл Шифт Ентер

А если серьезно, то формула массива (да и не только, обычная иногда тоже) дает результатом МАССИВ. Массив можно вывести на лист в столько ячеек, сколько в этом массиве значений (по вертикали и горизонтали). В одну ячейку в общем случае чисто формулой не получится все это засунуть.
Это все с учетом того, что Вам ведь нужно, чтобы автоматически все менялось, правильно?
Но вообще-то для этой задачи как раз прекрасно подходят UDF-ки, которых огромная куча понаписана.
Здесь в Готовых решениях парочка есть http://www.excelworld.ru/forum/3-0-1-0-10-1-[%D1%81%D1%86%D0%B5%D0%BF]

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеМожно. Введите мою формулу не Ентером, а одновременным нажатием Контрл Шифт Ентер

А если серьезно, то формула массива (да и не только, обычная иногда тоже) дает результатом МАССИВ. Массив можно вывести на лист в столько ячеек, сколько в этом массиве значений (по вертикали и горизонтали). В одну ячейку в общем случае чисто формулой не получится все это засунуть.
Это все с учетом того, что Вам ведь нужно, чтобы автоматически все менялось, правильно?
Но вообще-то для этой задачи как раз прекрасно подходят UDF-ки, которых огромная куча понаписана.
Здесь в Готовых решениях парочка есть http://www.excelworld.ru/forum/3-0-1-0-10-1-[%D1%81%D1%86%D0%B5%D0%BF]
Автор - _Boroda_
Дата добавления - 03.11.2017 в 10:41
Logotip Дата: Пятница, 03.11.2017, 12:33 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 23
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Цитата _Boroda_, 03.11.2017 в 10:41, в сообщении № 4 ()
Можно. Введите мою формулу не Ентером, а одновременным нажатием Контрл Шифт Ентер

hands :D Согласен, это соответствует условиям задачи ))

Цитата _Boroda_, 03.11.2017 в 10:41, в сообщении № 4 ()
А если серьезно, то формула массива (да и не только, обычная иногда тоже) дает результатом МАССИВ. Массив можно вывести на лист в столько ячеек, сколько в этом массиве значений (по вертикали и горизонтали). В одну ячейку в общем случае чисто формулой не получится все это засунуть.

Ну, почему же не получится (если я Вас правильно понял)?
Если все на том же примере рассмотреть следующую формулу:
Код
=СЦЕП(ЕСЛИ(--ЕОШ(ПОИСК(M1;A2:E2))=0;СТОЛБЕЦ(A:E);"X"))

Формула массивная и дает результат 1XX45, где 1,4 и 5 это "адреса" столбцов, при помощи которых через
Код
ИНДЕКС
можно подтянуть нужные ячейки и далее аналогично цифрам "1XX45" через
Код
СЦЕПИТЬ
сложить в одну ячейку.
Вот только в эту же формулу подсунуть
Код
ИНДЕКС
у меня не получается.
Даже пробовал через
Код
ДВССЫЛ
, но результата так и не получил.

Ваше решение абсолютно верно решает задачу yes , но хотелось минимизировать количество формул, а также получить универсальный вариант для большого количества столбцов.
 
Ответить
Сообщение
Цитата _Boroda_, 03.11.2017 в 10:41, в сообщении № 4 ()
Можно. Введите мою формулу не Ентером, а одновременным нажатием Контрл Шифт Ентер

hands :D Согласен, это соответствует условиям задачи ))

Цитата _Boroda_, 03.11.2017 в 10:41, в сообщении № 4 ()
А если серьезно, то формула массива (да и не только, обычная иногда тоже) дает результатом МАССИВ. Массив можно вывести на лист в столько ячеек, сколько в этом массиве значений (по вертикали и горизонтали). В одну ячейку в общем случае чисто формулой не получится все это засунуть.

Ну, почему же не получится (если я Вас правильно понял)?
Если все на том же примере рассмотреть следующую формулу:
Код
=СЦЕП(ЕСЛИ(--ЕОШ(ПОИСК(M1;A2:E2))=0;СТОЛБЕЦ(A:E);"X"))

Формула массивная и дает результат 1XX45, где 1,4 и 5 это "адреса" столбцов, при помощи которых через
Код
ИНДЕКС
можно подтянуть нужные ячейки и далее аналогично цифрам "1XX45" через
Код
СЦЕПИТЬ
сложить в одну ячейку.
Вот только в эту же формулу подсунуть
Код
ИНДЕКС
у меня не получается.
Даже пробовал через
Код
ДВССЫЛ
, но результата так и не получил.

Ваше решение абсолютно верно решает задачу yes , но хотелось минимизировать количество формул, а также получить универсальный вариант для большого количества столбцов.
Автор - Logotip
Дата добавления - 03.11.2017 в 12:33
_Boroda_ Дата: Пятница, 03.11.2017, 12:50 | Сообщение № 6
Группа: Админы
Ранг: Местный житель
Сообщений: 16881
Репутация: 6593 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Вы рассуждаете верно. Везде, кроме фразы
Цитата Logotip, 03.11.2017 в 12:33, в сообщении № 5 ()
сложить в одну ячейку
. Формульный массив получится сцепить вместе или посимвольным перебором, что даст Вам формулу гораздо больше моей (в которой, кстати, обратите внимание на то, как ищет ПОИСК и в чем отличие от Вашего ПОИСКа), или макросом.

Не, если хотите, пробуйте, никто ж не запрещает. Я думаю, что в свое время через это прошел каждый из нас. Также, как и через попытку засунуть виртуальный диапазон в Проверку данных.

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеВы рассуждаете верно. Везде, кроме фразы
Цитата Logotip, 03.11.2017 в 12:33, в сообщении № 5 ()
сложить в одну ячейку
. Формульный массив получится сцепить вместе или посимвольным перебором, что даст Вам формулу гораздо больше моей (в которой, кстати, обратите внимание на то, как ищет ПОИСК и в чем отличие от Вашего ПОИСКа), или макросом.

Не, если хотите, пробуйте, никто ж не запрещает. Я думаю, что в свое время через это прошел каждый из нас. Также, как и через попытку засунуть виртуальный диапазон в Проверку данных.
Автор - _Boroda_
Дата добавления - 03.11.2017 в 12:50
Мир MS Excel » Вопросы и решения » Вопросы по Excel » СЦЕПИТЬ в одну ячейку формулой массива
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2025 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!