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

Вход

Регистрация

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

 

= Мир MS Excel/Сцепить данные с автоматическим перебором ячеек - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сцепить данные с автоматическим перебором ячеек (Формулы/Formulas)
Сцепить данные с автоматическим перебором ячеек
SYKim Дата: Понедельник, 18.05.2020, 12:44 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Доброго дня.
В примере - пошаговый вариант сцепки данных, т.е. по сути - сначала сцепляем по строкам, затем полученный столбец в одну ячейку.
Вопрос: можно ли как-то опустить промежуточную сцепку, например, указать некий счетчик, указывающий сколько раз надо пройти по одному и тому же пути сцепки по строкам с автоматическим перебором строки +1 (по столбцу).

Вопрос возник в связи с желанием сделать формулу более компактой, потому как в рабочем файле данные расположены на разных листах (т.е. еще добавляется длина ссылки на лист), а при сцепке даже трех строк - формула громадна.
К сообщению приложен файл: 4858314.xml(4.4 Kb)
 
Ответить
СообщениеДоброго дня.
В примере - пошаговый вариант сцепки данных, т.е. по сути - сначала сцепляем по строкам, затем полученный столбец в одну ячейку.
Вопрос: можно ли как-то опустить промежуточную сцепку, например, указать некий счетчик, указывающий сколько раз надо пройти по одному и тому же пути сцепки по строкам с автоматическим перебором строки +1 (по столбцу).

Вопрос возник в связи с желанием сделать формулу более компактой, потому как в рабочем файле данные расположены на разных листах (т.е. еще добавляется длина ссылки на лист), а при сцепке даже трех строк - формула громадна.

Автор - SYKim
Дата добавления - 18.05.2020 в 12:44
Hugo Дата: Понедельник, 18.05.2020, 13:58 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2946
Репутация: 649 ±
Замечаний: 0% ±

Доброго дня.
Как такой хитрый формат файла удалось создать?
А по вопросу - точно можно написать свою UDF, выводящая всё в одну строку или столбец уже есть, можно подкрутить.
см. скрин: http://prntscr.com/sixg4q


excel@nxt.ru
webmoney: E265281470651 R418926282008 Z422237915069


Сообщение отредактировал Hugo - Понедельник, 18.05.2020, 14:08
 
Ответить
СообщениеДоброго дня.
Как такой хитрый формат файла удалось создать?
А по вопросу - точно можно написать свою UDF, выводящая всё в одну строку или столбец уже есть, можно подкрутить.
см. скрин: http://prntscr.com/sixg4q

Автор - Hugo
Дата добавления - 18.05.2020 в 13:58
Pelena Дата: Понедельник, 18.05.2020, 14:21 | Сообщение № 3
Группа: Админы
Ранг: Местный житель
Сообщений: 15759
Репутация: 3436 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
SYKim, не это ищете?


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеSYKim, не это ищете?

Автор - Pelena
Дата добавления - 18.05.2020 в 14:21
SYKim Дата: Понедельник, 18.05.2020, 14:36 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Hugo, так понимаю, это объединение именно диапазона в одном листе, а как быть если сцепляемая в строку (промежуточная итерация) выглядит по типу:
Код
СЦЕПИТЬ(Лист1!B19;СИМВОЛ(10);" (п.";Лист1!A19;" Описи)")


и в единую ячейку я хочу собрать таких , например, три, соотв-но будет меняться - В19, В20, В21.. и А19, А20, А21
 
Ответить
СообщениеHugo, так понимаю, это объединение именно диапазона в одном листе, а как быть если сцепляемая в строку (промежуточная итерация) выглядит по типу:
Код
СЦЕПИТЬ(Лист1!B19;СИМВОЛ(10);" (п.";Лист1!A19;" Описи)")


и в единую ячейку я хочу собрать таких , например, три, соотв-но будет меняться - В19, В20, В21.. и А19, А20, А21

Автор - SYKim
Дата добавления - 18.05.2020 в 14:36
Hugo Дата: Понедельник, 18.05.2020, 14:36 | Сообщение № 5
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2946
Репутация: 649 ±
Замечаний: 0% ±

Pelena, вроде не то. Но автор тот же :)
[vba]
Код

Function СКЛЕИТЬ2(Диапазон As Range, _
                  Optional Разделитель_Данных$ = "", _
                  Optional Переносить_ДА_НЕТ$ = "ДА") As String
'---------------------------------------------------------------------------------------
' Procedure    : СКЛЕИТЬ
' Author       : Alex_ST(+ доработка Hugo)
' Purpose      : склеить тексты из выделенных ячеек в одну строку с задаваемыми при необходимости разделителями данных из разных ячеек
' Notes        : по умолчанию включен перенос строк внутри ячейки
'---------------------------------------------------------------------------------------
    Dim Слитый_Текст$, Перенос_Строки$, Ячейка As Range, t&, fl As Boolean
    If UCase(Переносить_ДА_НЕТ$) <> "НЕТ" Then Перенос_Строки = vbLf
    t = Диапазон.Row
    For Each Ячейка In Диапазон
        If Len(Ячейка) Then
            If Ячейка.Row <> t Then fl = True
            t = Ячейка.Row
            If Слитый_Текст = "" Then
                Слитый_Текст = Application.WorksheetFunction.Trim(Ячейка.Value)   ' будут удаляться лидирующие и финиширующие пробелы, а также многократные пробелы между словами (используется стандартная функция СЖПРОБЕЛЫ)
            Else
                Слитый_Текст = Слитый_Текст & IIf(fl, vbLf, Разделитель_Данных) & Перенос_Строки & Application.WorksheetFunction.Trim(Ячейка.Value)
                fl = False
            End If
        End If
    Next Ячейка
    СКЛЕИТЬ2 = Слитый_Текст
End Function
[/vba]


excel@nxt.ru
webmoney: E265281470651 R418926282008 Z422237915069
 
Ответить
СообщениеPelena, вроде не то. Но автор тот же :)
[vba]
Код

Function СКЛЕИТЬ2(Диапазон As Range, _
                  Optional Разделитель_Данных$ = "", _
                  Optional Переносить_ДА_НЕТ$ = "ДА") As String
'---------------------------------------------------------------------------------------
' Procedure    : СКЛЕИТЬ
' Author       : Alex_ST(+ доработка Hugo)
' Purpose      : склеить тексты из выделенных ячеек в одну строку с задаваемыми при необходимости разделителями данных из разных ячеек
' Notes        : по умолчанию включен перенос строк внутри ячейки
'---------------------------------------------------------------------------------------
    Dim Слитый_Текст$, Перенос_Строки$, Ячейка As Range, t&, fl As Boolean
    If UCase(Переносить_ДА_НЕТ$) <> "НЕТ" Then Перенос_Строки = vbLf
    t = Диапазон.Row
    For Each Ячейка In Диапазон
        If Len(Ячейка) Then
            If Ячейка.Row <> t Then fl = True
            t = Ячейка.Row
            If Слитый_Текст = "" Then
                Слитый_Текст = Application.WorksheetFunction.Trim(Ячейка.Value)   ' будут удаляться лидирующие и финиширующие пробелы, а также многократные пробелы между словами (используется стандартная функция СЖПРОБЕЛЫ)
            Else
                Слитый_Текст = Слитый_Текст & IIf(fl, vbLf, Разделитель_Данных) & Перенос_Строки & Application.WorksheetFunction.Trim(Ячейка.Value)
                fl = False
            End If
        End If
    Next Ячейка
    СКЛЕИТЬ2 = Слитый_Текст
End Function
[/vba]

Автор - Hugo
Дата добавления - 18.05.2020 в 14:36
SYKim Дата: Понедельник, 18.05.2020, 14:41 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Pelena, не совсем, потому как по ссылке из поста выше - у меня сцепляется не только содержимое ячеек, а с доп.текстом.
Нужно это для создания отчетов - единого перечня документов. Отчеты разные, дополнительный текст разный.
 
Ответить
СообщениеPelena, не совсем, потому как по ссылке из поста выше - у меня сцепляется не только содержимое ячеек, а с доп.текстом.
Нужно это для создания отчетов - единого перечня документов. Отчеты разные, дополнительный текст разный.

Автор - SYKim
Дата добавления - 18.05.2020 в 14:41
Hugo Дата: Понедельник, 18.05.2020, 19:33 | Сообщение № 7
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2946
Репутация: 649 ±
Замечаний: 0% ±

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


excel@nxt.ru
webmoney: E265281470651 R418926282008 Z422237915069
 
Ответить
Сообщение
как быть если сцепляемая в строку (промежуточная итерация)
- я не вникал, я без промежуточных сделал чтоб результат обработки исходных данных был точно такой как показали.

Автор - Hugo
Дата добавления - 18.05.2020 в 19:33
SYKim Дата: Вторник, 19.05.2020, 15:20 | Сообщение № 8
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Hugo, потому и поясняю, что суть не в том, чтобы слитьвсе внесте пусть и в диапазоне, а именно в том чтоб повторить итерацию, пример которой:
Код
СЦЕПИТЬ(Лист1!B19;СИМВОЛ(10);" (п.";Лист1!A19;" Описи)")


таким образом для соединения трех подобных строк у меня будет примерно следующая формула:
[code]СЦЕПИТЬ(Лист1!B19;" (п.";Лист1!A19;" Описи)";СИМВОЛ(10);Лист1!B20;" (п.";Лист1!A20;" Описи)";СИМВОЛ(10);Лист1!B21;" (п.";Лист1!A21;" Описи)")

вот и подумалось - мб есть какая-т0 функция, где можно описать что делать, затем какое кол-во раз, с обозначением начальной ячейки..
 
Ответить
СообщениеHugo, потому и поясняю, что суть не в том, чтобы слитьвсе внесте пусть и в диапазоне, а именно в том чтоб повторить итерацию, пример которой:
Код
СЦЕПИТЬ(Лист1!B19;СИМВОЛ(10);" (п.";Лист1!A19;" Описи)")


таким образом для соединения трех подобных строк у меня будет примерно следующая формула:
[code]СЦЕПИТЬ(Лист1!B19;" (п.";Лист1!A19;" Описи)";СИМВОЛ(10);Лист1!B20;" (п.";Лист1!A20;" Описи)";СИМВОЛ(10);Лист1!B21;" (п.";Лист1!A21;" Описи)")

вот и подумалось - мб есть какая-т0 функция, где можно описать что делать, затем какое кол-во раз, с обозначением начальной ячейки..

Автор - SYKim
Дата добавления - 19.05.2020 в 15:20
Hugo Дата: Вторник, 19.05.2020, 16:07 | Сообщение № 9
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2946
Репутация: 649 ±
Замечаний: 0% ±

Можно в моей UDF добавить параметры "что и сколько", но зачем? Выделили данные, код сразу понял что и сколько... Что не так в результате?


excel@nxt.ru
webmoney: E265281470651 R418926282008 Z422237915069
 
Ответить
СообщениеМожно в моей UDF добавить параметры "что и сколько", но зачем? Выделили данные, код сразу понял что и сколько... Что не так в результате?

Автор - Hugo
Дата добавления - 19.05.2020 в 16:07
SYKim Дата: Вторник, 19.05.2020, 17:33 | Сообщение № 10
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Hugo, все так - если между объединяемыми ячейками не добавлять текст.

вот пример:
Код
СЦЕПИТЬ(Лист1!B19;" (п.";Лист1!A19;" Описи)";СИМВОЛ(10);Лист1!B20;" (п.";Лист1!A20;" Описи)";СИМВОЛ(10);Лист1!B21;" (п.";Лист1!A21;" Описи)")
 
Ответить
СообщениеHugo, все так - если между объединяемыми ячейками не добавлять текст.

вот пример:
Код
СЦЕПИТЬ(Лист1!B19;" (п.";Лист1!A19;" Описи)";СИМВОЛ(10);Лист1!B20;" (п.";Лист1!A20;" Описи)";СИМВОЛ(10);Лист1!B21;" (п.";Лист1!A21;" Описи)")

Автор - SYKim
Дата добавления - 19.05.2020 в 17:33
Hugo Дата: Вторник, 19.05.2020, 17:44 | Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2946
Репутация: 649 ±
Замечаний: 0% ±

Так это уже другая задача, другой перебор, внедрение постороннего текста... Можно и на это написать свою функцию, но зачем? Следующая задача ведь будет снова совершенно другая :)


excel@nxt.ru
webmoney: E265281470651 R418926282008 Z422237915069
 
Ответить
СообщениеТак это уже другая задача, другой перебор, внедрение постороннего текста... Можно и на это написать свою функцию, но зачем? Следующая задача ведь будет снова совершенно другая :)

Автор - Hugo
Дата добавления - 19.05.2020 в 17:44
SYKim Дата: Вторник, 19.05.2020, 18:25 | Сообщение № 12
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Hugo,
соглашусь, задача сформулирована - не достаточно внятно,
мне вот видится некая форма с параметрами.
поля: объединяемые ячейки в кол-ве n - добавляемый текст между по кол-ву (n-1) - количество раз (счетчик) сколько строк подряд идущих объединяем.

Но так понимаю, что проще собирать построчно, а в финальной версии отчета просто объединить строки.
Но при объединении - теряются ссылки/формулы и при внесении изменений уже текст объединенной ячейки не обновляется автоматически.
В таком ключе, может быть подскажете, есть ли возм-ть объединения с сохранением ссылочной структуры??
 
Ответить
СообщениеHugo,
соглашусь, задача сформулирована - не достаточно внятно,
мне вот видится некая форма с параметрами.
поля: объединяемые ячейки в кол-ве n - добавляемый текст между по кол-ву (n-1) - количество раз (счетчик) сколько строк подряд идущих объединяем.

Но так понимаю, что проще собирать построчно, а в финальной версии отчета просто объединить строки.
Но при объединении - теряются ссылки/формулы и при внесении изменений уже текст объединенной ячейки не обновляется автоматически.
В таком ключе, может быть подскажете, есть ли возм-ть объединения с сохранением ссылочной структуры??

Автор - SYKim
Дата добавления - 19.05.2020 в 18:25
Hugo Дата: Вторник, 19.05.2020, 18:31 | Сообщение № 13
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2946
Репутация: 649 ±
Замечаний: 0% ±

Да в эту СКЛЕИТЬ2 что выше не сложно добавить параметром текст, или даже несколько (до и после значения), а сколько ячеек и сколько строк - это что зададите функции то и будет обрабатывать.
Если есть пропускаемые строки - можно и это извращение добавить, если эта работа проще, чем собирать из диапазонов без пропусков строк...
И все ссылки видны! :)


excel@nxt.ru
webmoney: E265281470651 R418926282008 Z422237915069
 
Ответить
СообщениеДа в эту СКЛЕИТЬ2 что выше не сложно добавить параметром текст, или даже несколько (до и после значения), а сколько ячеек и сколько строк - это что зададите функции то и будет обрабатывать.
Если есть пропускаемые строки - можно и это извращение добавить, если эта работа проще, чем собирать из диапазонов без пропусков строк...
И все ссылки видны! :)

Автор - Hugo
Дата добавления - 19.05.2020 в 18:31
SYKim Дата: Среда, 20.05.2020, 10:43 | Сообщение № 14
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Hugo, понятно, плучится - зазря усложненная, как вы выразились - извращенная, структура.
Моя изначальная цель была - упростить, упрощения не получается.

Оставлю просто построчную сцепку.
Спасибо за попытки помочь.
 
Ответить
СообщениеHugo, понятно, плучится - зазря усложненная, как вы выразились - извращенная, структура.
Моя изначальная цель была - упростить, упрощения не получается.

Оставлю просто построчную сцепку.
Спасибо за попытки помочь.

Автор - SYKim
Дата добавления - 20.05.2020 в 10:43
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сцепить данные с автоматическим перебором ячеек (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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