Профессиональные приемы работы в Microsoft Excel

Обмен опытом => Microsoft Excel => Тема начата: Тамара Кубарева от 29.10.2017, 11:05

Название: Сцепка значений ячеек по вариантам
Отправлено: Тамара Кубарева от 29.10.2017, 11:05
ДД, может такой вопрос где-то разбирался, но есть такая сложность. Нужно сцепить варианты цветов с вариантами размеров через " ", чтобы каждому варианту цвета присвоился вариант размера, затем этому же цвету следующий размер и т.д., затем для следующего цвета аналогично все размеры. Все значения цветов и размеров последовательно должны быть в одной ячейке на этой же строке, разделенные ",". Аналогично вниз по всем строкам. Файл во вложении. Значений цветов может быть 10, размеров 40.
Название: Re: Сцепка значений ячеек по вариантам
Отправлено: boa от 30.10.2017, 10:09
По ИМХО, оптимальное решение на пользовательской функции

Function SplitSeparComma(a As String, b As String) As String
    Dim str1$(), str2$(), str$, x, y
    str1 = split(a, ",")
    str2 = split(b, ",")
    For Each x In str1
        For Each y In str2
            str = str & x & " " & y & ","
        Next
    Next
    SplitSeparComma = Mid(str, 1, Len(str) - 1)
End Function
Название: Re: Сцепка значений ячеек по вариантам
Отправлено: Тамара Кубарева от 31.10.2017, 18:10
boa спасибо, да вроде работает ваша функция, но вот при проверке, смотрю рубятся значения в конце ячейки. Что это ограничение экселя на кол-во знаков в ячейке? : http://prntscr.com/h4enyx
Название: Re: Сцепка значений ячеек по вариантам
Отправлено: boa от 01.11.2017, 09:58
начиная с 2007 Excel'я количество символов в ячейке = 32 767 (https://support.office.com/ru-ru/article/%D0%A2%D0%B5%D1%85%D0%BD%D0%B8%D1%87%D0%B5%D1%81%D0%BA%D0%B8%D0%B5-%D1%85%D0%B0%D1%80%D0%B0%D0%BA%D1%82%D0%B5%D1%80%D0%B8%D1%81%D1%82%D0%B8%D0%BA%D0%B8-%D0%B8-%D0%BE%D0%B3%D1%80%D0%B0%D0%BD%D0%B8%D1%87%D0%B5%D0%BD%D0%B8%D1%8F-Microsoft-Excel-1672b34d-7043-467e-8e27-269d656771c3#ID0EBABAAA=Excel_2007).
Возможно вы просто не видите все символы на экране?
попробуйте скопировать ячейку с функцией и вставить как значение, и посмотрите результат...
Название: Re: Сцепка значений ячеек по вариантам
Отправлено: Тамара Кубарева от 01.11.2017, 15:52
да все символы на месте. Благодарю!
Название: Re: Сцепка значений ячеек по вариантам
Отправлено: runner от 02.11.2017, 11:46
А как вместо запятой после пары вставить символ перевода строки?
Внешним добавлением ПОДСТАВИТЬ и СИМВОЛ(10) всё решается, но можно ли это уместить внутрь UDF?
Название: Re: Сцепка значений ячеек по вариантам
Отправлено: vikttur от 02.11.2017, 12:13
Цитироватьможно ли это уместить внутрь UDF
где = Replace(где, что, на_что)
Название: Re: Сцепка значений ячеек по вариантам
Отправлено: runner от 02.11.2017, 12:28
Не совсем понял куда этот кусок вписать .. не силён в VBA, отсюда и проблемы :(
Я имел в виду этот кусок:

        For Each y In str2
            str = str & x & " " & y & ","
        Next

пытался вместо & "," вставить & СИМВОЛ(10) или & CHAR(10), но, похоже, это записывается по-другому
Название: Re: Сцепка значений ячеек по вариантам
Отправлено: boa от 02.11.2017, 14:27
Цитата: runner от 02.11.2017, 12:28
пытался вместо & "," вставить & СИМВОЛ(10) или & CHAR(10), но, похоже, это записывается по-другому

str = str & x & " " & y & Chr(10)
или
str = str & x & " " & y & vbNewLine
или
str = str & x & " " & y & vbCrLf
Название: Re: Сцепка значений ячеек по вариантам
Отправлено: runner от 02.11.2017, 14:46
Спасибо! Всё работает :)
Название: Re: Сцепка значений ячеек по вариантам
Отправлено: Тамара Кубарева от 01.12.2017, 17:16
Добрый день!
Вопрос получил продолжение. Суть в том, что из пар значений для сцепки по предыдущему условию, не все пары должны включаться в сцепку, то есть некоторые пары вариантов Цвет+Размер отсутствуют. То есть, есть табличка (во вложении) где для каждого товара, то есть для каждой строки указывается какие значения пар цвет и размер присутствуют, а какие отсутствуют. Значения из этой таблички ложатся в ячейку "Таблица" второго файла в таком виде как в файле. В файле две строки - первая где все варианты присутствуют, вторая где что-то отсутствует, для второй строки как раз скрин таблицы. Суть того как ложатся значения таблицы в ячейку такова: Сначала идет слово Размер, то что находится в левом верхнем углу, затем перечисляются значения по горизонтали (цвета), т.е. столбцов таблички, затем начинается перечисление значений по вертикали (размеры), т.е. строк таблицы, где для каждого из значений (размер) указываются символы по количеству вертикальных элементов (цветов). Символы могут быть двух видов: есть или временно нет, их можно увидеть в ячейке, там еще между всеми соседними значениями много символов переноса. Ну вот, суть в том, что сцепку пар значений Цвет+Размер нужно получить, исключая отсутствующие варианты. черный и чёрный это наверное одно и то же, но в контексте задачи разные. :)
Название: Re: Сцепка значений ячеек по вариантам
Отправлено: Тамара Кубарева от 01.12.2017, 17:27
P.S. Все значения и пары нужно получить именно из ячейки таблица, нет перечислений через запятую как в прошлой задаче.
Название: Re: Сцепка значений ячеек по вариантам
Отправлено: boa от 02.12.2017, 10:33
Здравствуйте, Тамара
Приложите пример с табличками и примерами готовых результатов.
тот файл, что вы прикрепили к сообщению не читабелен
Название: Re: Сцепка значений ячеек по вариантам
Отправлено: Тамара Кубарева от 02.12.2017, 14:36
Теперь должно стать понятнее, вся таблица ассортимента с картинки содержится в одной ячейке, в прошлом варианте файла там все есть, оно в ячейке, но там много переносов. В этом варианте при получении исходника применена замена переносов и табуляций на пробел. В файле .xlsx примечания.
Название: Re: Сцепка значений ячеек по вариантам
Отправлено: boa от 02.12.2017, 16:45
Тамара, как бы вам объяснить...
то, что вы прислали - обрезок xml кода
обрабатывать искаженный Userом код - не благодарное занятие, т.к. в следующий раз User исказит его по новому
надо либо оригинал XML обрабатывать, либо правильно импортировать xml в Excel.

P.S. Вы не заметили, что ваш XLSX отличается от JPG?
Название: Re: Сцепка значений ячеек по вариантам
Отправлено: Тамара Кубарева от 02.12.2017, 17:46
В ячейке B3 файла Excel находится Xpath данной таблицы "assortment" и к результату применена замена переносов и табуляций на пробел. Без применения переносов и табуляций, было в файле, который в сообщении от 02.12.: http://prntscr.com/hi4omo
Вот ссылка на саму страницу товара: http://cool-kids.ru/product_info.php/info/p3569_Trusy-zhenskie-CONTE-Day-by-Day-RP-0002.html
Искажаться ничего не будет, вот так как есть так и получается.
Название: Re: Сцепка значений ячеек по вариантам
Отправлено: boa от 02.12.2017, 19:28
Если копипастить (во вложении), то можно результат обрабатывать в Excel
Ну а если парсить страницу, то это в коммерческие заказы (https://forum.msexcel.ru/index.php/board,10.0.html)...
Название: Re: Сцепка значений ячеек по вариантам
Отправлено: Тамара Кубарева от 03.12.2017, 04:16
Это и есть результат парсинга страницы, в ячейке B3 в частности результат Xpath таблицы "assortment". Необходимость есть теперь этот результат записать в виде последовательности записанных пар Цвет Размер в одной ячейке через "," как в той задаче, которая была в начале этой темы, и по которой Вы предложили пользовательскую функцию. Но там нужно было последовательно перечислить все размеры и цвета по вариантам для каждого цвета каждый размер. Теперь же не все пары цвет_размер могут быть в этом перечислении. Возможно ли на основании данных в ячейке B3 получить такую последовательность на основании анализа символов, присутствующих в этой ячейке, там два вида символов - есть или нет? 
Название: Re: Сцепка значений ячеек по вариантам
Отправлено: boa от 03.12.2017, 12:33
Здравствуйте, Тамара,
я то написал функцию под ваши условия, но она вряд ли кому-то пригодится из читателей форума :(,
потому я и рекомендовал вам создать тему в "Заказы - платное решение задач по Excel", ведь это частный случай.

Option Explicit
Option Base 0

Public Function NewSplit(a As String) As String
Dim FirstArr$(), NewArr$()
Dim Str, x&, y&, i&, j&
a = Application.WorksheetFunction.Trim(a)
a = Replace(Replace(a, "<!-- -->", "Есть"), "<!-- Временно отсутствует --> ---", "Нет")
FirstArr = Split(a, " ")
For Each Str In FirstArr
    If IsNumeric(Str) Then y = y + 1
    If Not IsNumeric(Str) And y = 0 Then x = x + 1
Next
x = x - 1
ReDim NewArr(x, y)
For Each Str In FirstArr
    NewArr(i, j) = Str
    If i = x Then i = 0: j = j + 1 Else i = i + 1
Next
For i = 1 To x
    For j = 1 To y
    If NewArr(i, j) = "Есть" Then Str = Str & NewArr(i, 0) & " " & NewArr(0, j) & ","
    Next j
Next i
NewSplit = Mid(Str, 1, Len(Str) - 1)
End Function
Название: Re: Сцепка значений ячеек по вариантам
Отправлено: Тамара Кубарева от 06.12.2017, 14:52
boa , спасибо за прояснение. Саму суть вопроса и правду нужно искать и решать еще на выходе из парса, а не танцевать с бубном в экселе.