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

Вход

Регистрация

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

 

= Мир MS Excel/Сцепить ячейки по условию в столбце - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_, DrMini  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сцепить ячейки по условию в столбце (Формулы/Formulas)
Сцепить ячейки по условию в столбце
Strateg_ru Дата: Суббота, 07.02.2015, 21:37 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Здравствуйте! Нужна помощь в составлении формулы

Есть столбец "B" вида:
14.07.2014 Вася
Маша
Марина
15.07.2014 Оля
Антон
Марина
Женя

То есть количество имён под каждой датой разное.
Нужна формула, которая бы в столбце "А" - в ячейках напротив дат, сцепяла ячейку с датой и все ячейки без дат, идущие под ней - до следующей даты. Напротив ячеек, значения которых начинаются не с даты (не с цифры) - пропускала.
То есть должен получиться результат:
14.07.2014 ВасяМашаМарина
(пусто)
(пусто)
15.07.2014 ОляАнтонМаринаЖеня
(пусто)
(пусто)
(пусто)

В файле примера - в столбце "В" - те значения, которые есть. В столбце "А" - результат, который должна сделать формула (в пустых значениях столбца А формула тоже есть, но выдаёт пустое значение).
К сообщению приложен файл: 0927901.xlsx (8.0 Kb)


Сообщение отредактировал Strateg_ru - Суббота, 07.02.2015, 22:40
 
Ответить
СообщениеЗдравствуйте! Нужна помощь в составлении формулы

Есть столбец "B" вида:
14.07.2014 Вася
Маша
Марина
15.07.2014 Оля
Антон
Марина
Женя

То есть количество имён под каждой датой разное.
Нужна формула, которая бы в столбце "А" - в ячейках напротив дат, сцепяла ячейку с датой и все ячейки без дат, идущие под ней - до следующей даты. Напротив ячеек, значения которых начинаются не с даты (не с цифры) - пропускала.
То есть должен получиться результат:
14.07.2014 ВасяМашаМарина
(пусто)
(пусто)
15.07.2014 ОляАнтонМаринаЖеня
(пусто)
(пусто)
(пусто)

В файле примера - в столбце "В" - те значения, которые есть. В столбце "А" - результат, который должна сделать формула (в пустых значениях столбца А формула тоже есть, но выдаёт пустое значение).
Автор - Strateg_ru
Дата добавления - 07.02.2015 в 21:37
Hugo Дата: Суббота, 07.02.2015, 22:47 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3854
Репутация: 814 ±
Замечаний: 0% ±

365
СЦЕПИТЬ() не подходит? Если не подходит - как практически должна применяться та, что подходит? Каким видите процесс?
Хотя вот вариант UDF - вписываете в A1, тянете вниз.
К сообщению приложен файл: 0927901.xls (29.5 Kb)

webmoney: E265281470651 Z422237915069
USDT TRC20: TN8XeEF17o5KPBD9pNwYzNyruycuAc2mVD

Сообщение отредактировал Hugo - Суббота, 07.02.2015, 23:02
 
Ответить
СообщениеСЦЕПИТЬ() не подходит? Если не подходит - как практически должна применяться та, что подходит? Каким видите процесс?
Хотя вот вариант UDF - вписываете в A1, тянете вниз.
Автор - Hugo
Дата добавления - 07.02.2015 в 22:47
AlexM Дата: Суббота, 07.02.2015, 23:18 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1130 ±
Замечаний: 0% ±

Excel 2003
С дополнительным столбцом формулы массива
К сообщению приложен файл: 0927901_1.xls (26.0 Kb)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеС дополнительным столбцом формулы массива
Автор - AlexM
Дата добавления - 07.02.2015 в 23:18
Strateg_ru Дата: Воскресенье, 08.02.2015, 00:24 | Сообщение № 4
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Цитата Hugo, 07.02.2015 в 22:47, в сообщении № 2
СЦЕПИТЬ() не подходит?

СЦЕПИТЬ как раз и мучал больше часа, подбирая разные условия.
Да, именно так формула и должна была работать. Но я так понял, Вы сделали с макросом? Как его перенести в другой документ (раздел макросов мне ничего не показал)

Цитата AlexM, 07.02.2015 в 23:18, в сообщении № 3
С дополнительным столбцом формулы массива

AlexM, да, это то что нужно. Но тоже не могу перенести формулу в целевой документ - выдаёт ошибку (скопировал даже в те же ячейки). В чём может быть дело?
К сообщению приложен файл: 3802846.png (37.8 Kb)


Сообщение отредактировал Strateg_ru - Воскресенье, 08.02.2015, 00:24
 
Ответить
Сообщение
Цитата Hugo, 07.02.2015 в 22:47, в сообщении № 2
СЦЕПИТЬ() не подходит?

СЦЕПИТЬ как раз и мучал больше часа, подбирая разные условия.
Да, именно так формула и должна была работать. Но я так понял, Вы сделали с макросом? Как его перенести в другой документ (раздел макросов мне ничего не показал)

Цитата AlexM, 07.02.2015 в 23:18, в сообщении № 3
С дополнительным столбцом формулы массива

AlexM, да, это то что нужно. Но тоже не могу перенести формулу в целевой документ - выдаёт ошибку (скопировал даже в те же ячейки). В чём может быть дело?
Автор - Strateg_ru
Дата добавления - 08.02.2015 в 00:24
AlexM Дата: Воскресенье, 08.02.2015, 00:26 | Сообщение № 5
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1130 ±
Замечаний: 0% ±

Excel 2003
В столбце А формулы массива. Их ввод делают тремя клавишами Ctrl+Shift+Enter


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеВ столбце А формулы массива. Их ввод делают тремя клавишами Ctrl+Shift+Enter
Автор - AlexM
Дата добавления - 08.02.2015 в 00:26
Strateg_ru Дата: Воскресенье, 08.02.2015, 00:36 | Сообщение № 6
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Цитата AlexM, 08.02.2015 в 00:26, в сообщении № 5
Их ввод делают тремя клавишами Ctrl+Shift+Enter

Да, получилось. Подскажите ещё, что добавить, для выдачи "пустого результата" в том числе и на "пустую ячейку"?
 
Ответить
Сообщение
Цитата AlexM, 08.02.2015 в 00:26, в сообщении № 5
Их ввод делают тремя клавишами Ctrl+Shift+Enter

Да, получилось. Подскажите ещё, что добавить, для выдачи "пустого результата" в том числе и на "пустую ячейку"?
Автор - Strateg_ru
Дата добавления - 08.02.2015 в 00:36
Richman Дата: Воскресенье, 08.02.2015, 08:28 | Сообщение № 7
Группа: Проверенные
Ранг: Обитатель
Сообщений: 346
Репутация: 54 ±
Замечаний: 0% ±

Excel 2007
Strateg_ru, Как понял:

Код
=ЕСЛИ(B5="";"";ЕСЛИ(ЕЧИСЛО(-ЛЕВБ(B5;10))+(B5="");B5&СМЕЩ(C5;ПОИСКПОЗ(1;--(ЕЧИСЛО(-ЛЕВБ(B6:B$99;10))+(B6:B$99="")>0);)-1;0);""))
К сообщению приложен файл: 6687154.xls (21.0 Kb)

С Уважением, Richman

 
Ответить
СообщениеStrateg_ru, Как понял:

Код
=ЕСЛИ(B5="";"";ЕСЛИ(ЕЧИСЛО(-ЛЕВБ(B5;10))+(B5="");B5&СМЕЩ(C5;ПОИСКПОЗ(1;--(ЕЧИСЛО(-ЛЕВБ(B6:B$99;10))+(B6:B$99="")>0);)-1;0);""))
Автор - Richman
Дата добавления - 08.02.2015 в 08:28
Richman Дата: Воскресенье, 08.02.2015, 09:10 | Сообщение № 8
Группа: Проверенные
Ранг: Обитатель
Сообщений: 346
Репутация: 54 ±
Замечаний: 0% ±

Excel 2007
Strateg_ru, Как вариант без доп столбцов и без массивных формул.
Если у Вас может в реальном файле больше 15 строк, то через "&" добавляйте их,
Если в реальном файле меньше, то можно оставить как есть, а можно лишние удалить

Код
=ЕСЛИОШИБКА(ЛЕВСИМВ(ЕСЛИ(--ЛЕВСИМВ(B2;2)>0;B2&B3&B4&B5&B6&B7&B8&B9&B10&B11&B12&B14&B15&B16&B17;"");ЕСЛИ(ЕОШ(ПОИСК("??.??.???";ЕСЛИ(--ЛЕВСИМВ(B2;2)>0;B2&B3&B4&B5&B6&B7&B8&B9&B10&B11&B12&B14&B15&B16&B17;"");11));100;ПОИСК("??.??.???";ЕСЛИ(--ЛЕВСИМВ(B2;2)>0;B2&B3&B4&B5&B6&B7&B8&B9&B10&B11&B12&B14&B15&B16&B17;"");11))-1);"")
К сообщению приложен файл: 0927901_1.xlsx (8.4 Kb)

С Уважением, Richman



Сообщение отредактировал Richman - Воскресенье, 08.02.2015, 09:41
 
Ответить
СообщениеStrateg_ru, Как вариант без доп столбцов и без массивных формул.
Если у Вас может в реальном файле больше 15 строк, то через "&" добавляйте их,
Если в реальном файле меньше, то можно оставить как есть, а можно лишние удалить

Код
=ЕСЛИОШИБКА(ЛЕВСИМВ(ЕСЛИ(--ЛЕВСИМВ(B2;2)>0;B2&B3&B4&B5&B6&B7&B8&B9&B10&B11&B12&B14&B15&B16&B17;"");ЕСЛИ(ЕОШ(ПОИСК("??.??.???";ЕСЛИ(--ЛЕВСИМВ(B2;2)>0;B2&B3&B4&B5&B6&B7&B8&B9&B10&B11&B12&B14&B15&B16&B17;"");11));100;ПОИСК("??.??.???";ЕСЛИ(--ЛЕВСИМВ(B2;2)>0;B2&B3&B4&B5&B6&B7&B8&B9&B10&B11&B12&B14&B15&B16&B17;"");11))-1);"")
Автор - Richman
Дата добавления - 08.02.2015 в 09:10
Strateg_ru Дата: Воскресенье, 08.02.2015, 11:25 | Сообщение № 9
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Richman, огромное спасибо за формулу. Немного доработал её знаками переноса. Но, видимо, на это самое количество символов сокращается количество знаков в результатирующей ячейке. Подскажите, где оно указано?
К сообщению приложен файл: 0927901_3.xlsx (9.5 Kb)


Сообщение отредактировал Strateg_ru - Воскресенье, 08.02.2015, 12:26
 
Ответить
СообщениеRichman, огромное спасибо за формулу. Немного доработал её знаками переноса. Но, видимо, на это самое количество символов сокращается количество знаков в результатирующей ячейке. Подскажите, где оно указано?
Автор - Strateg_ru
Дата добавления - 08.02.2015 в 11:25
vikttur Дата: Воскресенье, 08.02.2015, 11:41 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

Код
=ЕСЛИ(ЕЧИСЛО(-ЛЕВБ(B1));ПСТР(B1&B2&B3&B4&B5&B6&B7;1;ПОИСК("??.??.??";ПСТР(B1&B2&B3&B4&B5&B6&B7&"11.11.11";11;999))+9);)
 
Ответить
Сообщение
Код
=ЕСЛИ(ЕЧИСЛО(-ЛЕВБ(B1));ПСТР(B1&B2&B3&B4&B5&B6&B7;1;ПОИСК("??.??.??";ПСТР(B1&B2&B3&B4&B5&B6&B7&"11.11.11";11;999))+9);)
Автор - vikttur
Дата добавления - 08.02.2015 в 11:41
Strateg_ru Дата: Воскресенье, 08.02.2015, 12:24 | Сообщение № 11
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
vikttur, да, тоже подходит, и тоже аналогичный дополнительный вопрос - как добавить переносы так, чтобы количество символов в результатирующей ячейке соответствовало количеству символов в обрабатываемых ячейках?
Сейчас у меня это выглядит вот так:
Код
=ЕСЛИ(ЕЧИСЛО(-ЛЕВБ(B2));ПСТР(B2&(СИМВОЛ(10))&B3&(СИМВОЛ(10))&B4&(СИМВОЛ(10))&B5&(СИМВОЛ(10))&B6&(СИМВОЛ(10))&B7&(СИМВОЛ(10))&B8;1;ПОИСК("??.??.????";ПСТР(B2&B3&B4&B5&B6&B7&B8&"11.11.1111";11;999))+15);)

...но в некоторых строках захватывает несколько цифр из следующей ячейки.
 
Ответить
Сообщениеvikttur, да, тоже подходит, и тоже аналогичный дополнительный вопрос - как добавить переносы так, чтобы количество символов в результатирующей ячейке соответствовало количеству символов в обрабатываемых ячейках?
Сейчас у меня это выглядит вот так:
Код
=ЕСЛИ(ЕЧИСЛО(-ЛЕВБ(B2));ПСТР(B2&(СИМВОЛ(10))&B3&(СИМВОЛ(10))&B4&(СИМВОЛ(10))&B5&(СИМВОЛ(10))&B6&(СИМВОЛ(10))&B7&(СИМВОЛ(10))&B8;1;ПОИСК("??.??.????";ПСТР(B2&B3&B4&B5&B6&B7&B8&"11.11.1111";11;999))+15);)

...но в некоторых строках захватывает несколько цифр из следующей ячейки.
Автор - Strateg_ru
Дата добавления - 08.02.2015 в 12:24
vikttur Дата: Воскресенье, 08.02.2015, 12:32 | Сообщение № 12
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

Вставлять между словами пробел, завернуть формулу в СЖПРОБЕЛЫ и
Код
=ПОДСТАВИТЬ(СЖПРОБЕЛЫ(...);" ";СИМВОЛ(10))

Но для случая, когда в текстах ячеек отсутствуют пробелы.
 
Ответить
СообщениеВставлять между словами пробел, завернуть формулу в СЖПРОБЕЛЫ и
Код
=ПОДСТАВИТЬ(СЖПРОБЕЛЫ(...);" ";СИМВОЛ(10))

Но для случая, когда в текстах ячеек отсутствуют пробелы.
Автор - vikttur
Дата добавления - 08.02.2015 в 12:32
Strateg_ru Дата: Воскресенье, 08.02.2015, 14:50 | Сообщение № 13
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
vikttur, так в том-то и дело что пробелы есть - как минимум, в первой строчке стоит пробел после даты ((
И некоторые имена бывают с отчествами, с фамилиями...


Сообщение отредактировал Strateg_ru - Воскресенье, 08.02.2015, 14:51
 
Ответить
Сообщениеvikttur, так в том-то и дело что пробелы есть - как минимум, в первой строчке стоит пробел после даты ((
И некоторые имена бывают с отчествами, с фамилиями...
Автор - Strateg_ru
Дата добавления - 08.02.2015 в 14:50
Hugo Дата: Воскресенье, 08.02.2015, 15:33 | Сообщение № 14
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3854
Репутация: 814 ±
Замечаний: 0% ±

365
Так чем не годится
Код
=strateg($B$1:$B$7;ROW())
? :)
Если нужен перенос строк - ну воткните Chr(10):
Код
s & Chr(10) & a(i, 1)

webmoney: E265281470651 Z422237915069
USDT TRC20: TN8XeEF17o5KPBD9pNwYzNyruycuAc2mVD

Сообщение отредактировал Hugo - Воскресенье, 08.02.2015, 15:38
 
Ответить
СообщениеТак чем не годится
Код
=strateg($B$1:$B$7;ROW())
? :)
Если нужен перенос строк - ну воткните Chr(10):
Код
s & Chr(10) & a(i, 1)
Автор - Hugo
Дата добавления - 08.02.2015 в 15:33
Strateg_ru Дата: Воскресенье, 08.02.2015, 16:55 | Сообщение № 15
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Hugo, если вставлять в целевой файл переносом - то говорит "недопустимое значение". Видимо, это макрос. Ни пользоваться, ни даже скопировать макрос у меня не получилось.
Подскажите, как это сделать. Мой Excel 2013 при нажатии на кнопку "Макросы" - ничего в прикреплённом Вами файле не обнаружил...
 
Ответить
СообщениеHugo, если вставлять в целевой файл переносом - то говорит "недопустимое значение". Видимо, это макрос. Ни пользоваться, ни даже скопировать макрос у меня не получилось.
Подскажите, как это сделать. Мой Excel 2013 при нажатии на кнопку "Макросы" - ничего в прикреплённом Вами файле не обнаружил...
Автор - Strateg_ru
Дата добавления - 08.02.2015 в 16:55
Serge_007 Дата: Воскресенье, 08.02.2015, 16:59 | Сообщение № 16
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Shift+F3 - категория "Определенные пользователем"
А текст макроса можно посмотреть в стандартном модуле нажав Alt+F11

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеShift+F3 - категория "Определенные пользователем"
А текст макроса можно посмотреть в стандартном модуле нажав Alt+F11
Автор - Serge_007
Дата добавления - 08.02.2015 в 16:59
Hugo Дата: Воскресенье, 08.02.2015, 17:21 | Сообщение № 17
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3854
Репутация: 814 ±
Замечаний: 0% ±

365
Можно код функции пользователя поместить в надстройку - тогда пользоваться можно в любом документе на этой машине именно в таком синтаксисе.
Если код в персональной книге макросов или в любой другой открытой книге - тогда в ячейке нужно прописать и путь к функции. Ну если выбирать в мастере - то всё пропишется как надо само.

webmoney: E265281470651 Z422237915069
USDT TRC20: TN8XeEF17o5KPBD9pNwYzNyruycuAc2mVD
 
Ответить
СообщениеМожно код функции пользователя поместить в надстройку - тогда пользоваться можно в любом документе на этой машине именно в таком синтаксисе.
Если код в персональной книге макросов или в любой другой открытой книге - тогда в ячейке нужно прописать и путь к функции. Ну если выбирать в мастере - то всё пропишется как надо само.
Автор - Hugo
Дата добавления - 08.02.2015 в 17:21
Strateg_ru Дата: Воскресенье, 08.02.2015, 18:05 | Сообщение № 18
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Всем большое спасибо за помощь! Совокупными усилиями проблема частично решена.
 
Ответить
СообщениеВсем большое спасибо за помощь! Совокупными усилиями проблема частично решена.
Автор - Strateg_ru
Дата добавления - 08.02.2015 в 18:05
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сцепить ячейки по условию в столбце (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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