Выборка значений из столбцов в одну ячейку через запятую - Мир MS Excel

Регистрация

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

= Мир MS Excel/Выборка значений из столбцов в одну ячейку через запятую - Мир MS Excel

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Выборка значений из столбцов в одну ячейку через запятую (Формулы/Formulas)

Выборка значений из столбцов в одну ячейку через запятую

Дата: Среда, 22.05.2019, 14:06 | Сообщение № 1

Группа: Пользователи

Ранг: Новичок

Сообщений: 16

Репутация: 0 ±

Замечаний: 0% ±

Excel для Mac

Добрый день! Уважаемые знатоки! Подскажите пожалуйста, как собрать из столбца по условию (другой столбец) все значения в одну ячейку с разделителем ","
Файл во вложении.
Формула должна просматривать столбец А и пока не сменится значение, все данные из массива столбцов B,C,D скомпилировать в одну строку, но по ячейкам.
То есть должна остаться одна строка с ячейками "цена, ссылки на фото через запятую в одной ячейке, комментарий"...

К сообщению приложен файл: 5808356.xlsx (68.1 Kb)

Сообщение отредактировал glebanidze - Среда, 22.05.2019, 14:53

Ответить

Дата: Среда, 22.05.2019, 16:00 | Сообщение № 2

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2566

Репутация: 724 ±

Замечаний: 0% ±

Excel 2010

Добрый день.
Вариант на Power Query

К сообщению приложен файл: 0855500.xlsx (24.6 Kb)

Яндекс: 410016850021169

Ответить

Дата: Среда, 22.05.2019, 16:48 | Сообщение № 3

Группа: Пользователи

Ранг: Новичок

Сообщений: 16

Репутация: 0 ±

Замечаний: 0% ±

Excel для Mac

sboy, Благодарю, буду разбираться!

Ответить

Дата: Среда, 22.05.2019, 17:04 | Сообщение № 4

Группа: Друзья

Ранг: Старожил

Сообщений: 1850

Репутация: 523 ±

Замечаний: 0% ±

Excel 2013, 2016

И формульное решение. Уникальные. Массивная формула:

Код

=ИНДЕКС(A:A;МИН(ЕСЛИ(СЧЁТЕСЛИ(E$1:E1;A$2:A$99)=0;СТРОКА($2:$99))))&""

и сцепленные данные (не более 10строк):

Код

=ВПР(E2;A:C;3;)&ЕСЛИ(СЧЁТЕСЛИ(A:A;E2)<=1;"";", "&ИНДЕКС(C:C;ПОИСКПОЗ(E2;A:A;)+1))&ЕСЛИ(СЧЁТЕСЛИ(A:A;E2)<=2;"";", "&ИНДЕКС(C:C;ПОИСКПОЗ(E2;A:A;)+2))&ЕСЛИ(СЧЁТЕСЛИ(A:A;E2)<=3;"";", "&ИНДЕКС(C:C;ПОИСКПОЗ(E2;A:A;)+3))&ЕСЛИ(СЧЁТЕСЛИ(A:A;E2)<=4;"";", "&ИНДЕКС(C:C;ПОИСКПОЗ(E2;A:A;)+4))&ЕСЛИ(СЧЁТЕСЛИ(A:A;E2)<=5;"";", "&ИНДЕКС(C:C;ПОИСКПОЗ(E2;A:A;)+5))&ЕСЛИ(СЧЁТЕСЛИ(A:A;E2)<=6;"";", "&ИНДЕКС(C:C;ПОИСКПОЗ(E2;A:A;)+6))&ЕСЛИ(СЧЁТЕСЛИ(A:A;E2)<=7;"";", "&ИНДЕКС(C:C;ПОИСКПОЗ(E2;A:A;)+7))&ЕСЛИ(СЧЁТЕСЛИ(A:A;E2)<=8;"";", "&ИНДЕКС(C:C;ПОИСКПОЗ(E2;A:A;)+8))&ЕСЛИ(СЧЁТЕСЛИ(A:A;E2)<=9;"";", "&ИНДЕКС(C:C;ПОИСКПОЗ(E2;A:A;)+9))

К сообщению приложен файл: 5808356-1.xlsx (14.3 Kb)

Программировать проще, чем писать стихи.

Ответить

Дата: Среда, 22.05.2019, 17:28 | Сообщение № 5

Группа: Пользователи

Ранг: Новичок

Сообщений: 16

Репутация: 0 ±

Замечаний: 0% ±

Excel для Mac

Светлый, Благодарю, вот формульное решение более актуально!

Ответить

Дата: Пятница, 24.05.2019, 21:06 | Сообщение № 6

Группа: Пользователи

Ранг: Новичок

Сообщений: 16

Репутация: 0 ±

Замечаний: 0% ±

Excel для Mac

Светлый, подскажите пожалуйста, а как сделать больше 10 строк... У меня там ведь бывают портянки по несколько тысяч строк... Я вообще голову сломал. У нас работа стоит из-за этой формулы(

Ответить

Дата: Пятница, 24.05.2019, 21:10 | Сообщение № 7

Группа: Пользователи

Ранг: Новичок

Сообщений: 16

Репутация: 0 ±

Замечаний: 0% ±

Excel для Mac

sboy, у меня на маке нету power query ((( может, знаете как формулой сделать это? Ну очень нужно... Голову сломал((((
пробовал так: =ЕСЛИ(B3=B2;F2&","&F3&","&F4&","&F5&","&F6&","&F7&","&F8&","&F9&","&F10;F2)
но проблема в том, что он так берет 10 строк, а их бывает и 3 и 17... какой вот формулой во второй части сделать так, чтобы он останавливался когда b3 не равно b2

Ответить

Дата: Суббота, 25.05.2019, 08:57 | Сообщение № 8

Группа: Друзья

Ранг: Старожил

Сообщений: 1850

Репутация: 523 ±

Замечаний: 0% ±

Excel 2013, 2016

После фрагмента

Код

&ЕСЛИ(СЧЁТЕСЛИ(A:A;E2)<=9;"";", "&ИНДЕКС(C:C;ПОИСКПОЗ(E2;A:A;)+9))

вставить

Код

&ЕСЛИ(СЧЁТЕСЛИ(A:A;E2)<=10;"";", "&ИНДЕКС(C:C;ПОИСКПОЗ(E2;A:A;)+10))

и так далее.
*Чуть подправил формулу, чтобы не выдавал ошибку. 13 строк сцепляет:

Код

=ЕСЛИ(E2="";"";ВПР(E2;A:C;3;)&ЕСЛИ(СЧЁТЕСЛИ(A:A;E2)<=1;"";", "&ИНДЕКС(C:C;ПОИСКПОЗ(E2;A:A;)+1))&ЕСЛИ(СЧЁТЕСЛИ(A:A;E2)<=2;"";", "&ИНДЕКС(C:C;ПОИСКПОЗ(E2;A:A;)+2))&ЕСЛИ(СЧЁТЕСЛИ(A:A;E2)<=3;"";", "&ИНДЕКС(C:C;ПОИСКПОЗ(E2;A:A;)+3))&ЕСЛИ(СЧЁТЕСЛИ(A:A;E2)<=4;"";", "&ИНДЕКС(C:C;ПОИСКПОЗ(E2;A:A;)+4))&ЕСЛИ(СЧЁТЕСЛИ(A:A;E2)<=5;"";", "&ИНДЕКС(C:C;ПОИСКПОЗ(E2;A:A;)+5))&ЕСЛИ(СЧЁТЕСЛИ(A:A;E2)<=6;"";", "&ИНДЕКС(C:C;ПОИСКПОЗ(E2;A:A;)+6))&ЕСЛИ(СЧЁТЕСЛИ(A:A;E2)<=7;"";", "&ИНДЕКС(C:C;ПОИСКПОЗ(E2;A:A;)+7))&ЕСЛИ(СЧЁТЕСЛИ(A:A;E2)<=8;"";", "&ИНДЕКС(C:C;ПОИСКПОЗ(E2;A:A;)+8))&ЕСЛИ(СЧЁТЕСЛИ(A:A;E2)<=9;"";", "&ИНДЕКС(C:C;ПОИСКПОЗ(E2;A:A;)+9))&ЕСЛИ(СЧЁТЕСЛИ(A:A;E2)<=10;"";", "&ИНДЕКС(C:C;ПОИСКПОЗ(E2;A:A;)+10))&ЕСЛИ(СЧЁТЕСЛИ(A:A;E2)<=11;"";", "&ИНДЕКС(C:C;ПОИСКПОЗ(E2;A:A;)+11))&ЕСЛИ(СЧЁТЕСЛИ(A:A;E2)<=12;"";", "&ИНДЕКС(C:C;ПОИСКПОЗ(E2;A:A;)+12)))

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Суббота, 25.05.2019, 09:07

Ответить

Дата: Суббота, 25.05.2019, 09:00 | Сообщение № 9

Группа: Пользователи

Ранг: Новичок

Сообщений: 16

Репутация: 0 ±

Замечаний: 0% ±

Excel для Mac

Светлый, Благодарю, я так и думал, но у меня несколько тысяч строк таблица. это же нереально такую формулу сделать...

Ответить

Дата: Суббота, 25.05.2019, 09:20 | Сообщение № 10

Группа: Друзья

Ранг: Старожил

Сообщений: 1850

Репутация: 523 ±

Замечаний: 0% ±

Excel 2013, 2016

Цитата glebanidze, 25.05.2019 в 09:00, в сообщении № 9 ()

это же нереально такую формулу сделать...

от большого количества строк только время работы будет зависеть. Для каждого уникального может сцеплять как в сообщении №8 до 13 строк.

Программировать проще, чем писать стихи.

Ответить

Дата: Суббота, 25.05.2019, 09:41 | Сообщение № 11

Группа: Пользователи

Ранг: Новичок

Сообщений: 16

Репутация: 0 ±

Замечаний: 0% ±

Excel для Mac

Светлый, Ну мне достаточно и чтобы 10 строк сцеплял, хотя их бывает и по 20 с одинаковым значением столбца А. НО! Формула работает только на первые 10 уникальных значений столбца А. А потом не работает...

К сообщению приложен файл: 4619641.xlsx (56.2 Kb)

Ответить

Дата: Суббота, 25.05.2019, 09:48 | Сообщение № 12

Группа: Модераторы

Ранг: Экселист

Сообщений: 7029

Репутация: 1078 ±

Замечаний: 0% ±

Excel 2007;2010;2016

glebanidze, такую формулу в версии до 2016 без макросов не создать. это или UDF СцепитЕсли или ставить 2016 (или 365 не помню точно) там это уже вшито

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Ответить

Дата: Суббота, 25.05.2019, 11:58 | Сообщение № 13

Группа: Друзья

Ранг: Старожил

Сообщений: 1850

Репутация: 523 ±

Замечаний: 0% ±

Excel 2013, 2016

Цитата китин, 25.05.2019 в 09:48, в сообщении № 12 ()

Формула работает только на первые 10 уникальных значений столбца А

Тогда просто увеличить интервал в формуле уникальных:

Код

=ИНДЕКС(A:A;МИН(ЕСЛИ(СЧЁТЕСЛИ(E$1:E1;A$2:A$9999)=0;СТРОКА($2:$9999))))&""

Десять тысяч строк хватит?

Программировать проще, чем писать стихи.

Ответить

Дата: Суббота, 25.05.2019, 12:16 | Сообщение № 14

Группа: Пользователи

Ранг: Новичок

Сообщений: 16

Репутация: 0 ±

Замечаний: 0% ±

Excel для Mac

Светлый, вот в этом и проблема. я так делал и у меня везде вычисляется только одно первое входящее значение и выдается ошибка по ячейке "несогласованная формула" ((( полтергейст какой-то. :'(

:'(

Ответить

Дата: Суббота, 25.05.2019, 12:17 | Сообщение № 15

Группа: Пользователи

Ранг: Новичок

Сообщений: 16

Репутация: 0 ±

Замечаний: 0% ±

Excel для Mac

китин, Благодарю, только у меня вообще никак не выходит этот макрос сделать... Читаю руководства как UDF этот вставить...

Ответить

Дата: Суббота, 25.05.2019, 13:52 | Сообщение № 16

Группа: Пользователи

Ранг: Новичок

Сообщений: 16

Репутация: 0 ±

Замечаний: 0% ±

Excel для Mac

китин, удалось установить UDF макрос. То есть при вводе в ячейке =сцепитьесли эксель предлагает пользовательскую функцию. Только она не работает. ввожу

Код

=СцепитьЕсли(A:A;A3=A2;C:C;",")

. Помогите пожалуйста, что не так. Ячейка пустая...

К сообщению приложен файл: 0872996.xls (97.0 Kb)

Сообщение отредактировал glebanidze - Суббота, 25.05.2019, 14:01

Ответить

Дата: Суббота, 25.05.2019, 15:32 | Сообщение № 17

Группа: Друзья

Ранг: Старожил

Сообщений: 1850

Репутация: 523 ±

Замечаний: 0% ±

Excel 2013, 2016

Цитата glebanidze, 25.05.2019 в 13:52, в сообщении № 16 ()

только одно первое входящее

Формула массива, вводится одновременным нажатием Ctrl+Shift+Enter.

К сообщению приложен файл: 5808356-2.xlsx (15.8 Kb)

Программировать проще, чем писать стихи.

Ответить

Дата: Воскресенье, 26.05.2019, 11:47 | Сообщение № 18

Группа: Друзья

Ранг: Экселист

Сообщений: 5660

Репутация: 1163 ±

Замечаний: 0% ±

2010

1. UDF нужно вставлять не в UserForm, а в стандартный модуль.
2. Огрызок корежит кодировку. Замените кирилицу на латиницу.

Быть или не быть, вот в чем загвоздка!

Ответить

Дата: Понедельник, 27.05.2019, 19:52 | Сообщение № 19

Группа: Пользователи

Ранг: Новичок

Сообщений: 16

Репутация: 0 ±

Замечаний: 0% ±

Excel для Mac

Светлый, Спасибо вам большое! Ваши формулы работают и помогли мне очень сильно!

Ответить

Выборка значений из столбцов в одну ячейку через запятую (Формулы/Formulas)

Страница 1 из 1
1

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