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

Вход

Регистрация

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

 

= Мир MS Excel/Вытащить из одной ячейки множество значений - Мир MS Excel

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

Excel 2016
Как разделить текст по столбцам в Excel с помощью формулы?
Отличие в том, что надо вытащить из одной ячейки множество значений. В каждой ячейке значение разные.

Например в ячейке следующее значение:
Артикул: 3RT2018-2XF42-0LA2; Производитель: SIEMENS AG; Кол-во: 3 ШТ; Бренд: SIEMENS | Артикул: 3RH2362-2AF00; Производитель: SIEMENS AG; Кол-во: 3 ШТ; Бренд: SIEMENS | Артикул: 3RH2140-1AF00; Производитель: SIEMENS AG; Кол-во: 12 ШТ; Бренд: SIEMENS | Артикул: 3RH2122-2XF40-0LA2; Производитель: SIEMENS AG; Кол-во: 3 ШТ; Бренд: SIEMENS

В результате хотелось бы получить следующее:
Столбец - 3RT2018-2XF42-0LA2 Столбец - 3 ШТ
Столбец - 3RH2362-2AF00 Столбец - 3 ШТ
Столбец - 3RH2140-1AF00 Столбец - 12 ШТ
Столбец - 3RH2122-2XF40-0LA2 Столбец - 3 ШТ
К сообщению приложен файл: ______.xlsx (8.7 Kb)


Сообщение отредактировал Harry - Понедельник, 26.02.2018, 15:01
 
Ответить
СообщениеКак разделить текст по столбцам в Excel с помощью формулы?
Отличие в том, что надо вытащить из одной ячейки множество значений. В каждой ячейке значение разные.

Например в ячейке следующее значение:
Артикул: 3RT2018-2XF42-0LA2; Производитель: SIEMENS AG; Кол-во: 3 ШТ; Бренд: SIEMENS | Артикул: 3RH2362-2AF00; Производитель: SIEMENS AG; Кол-во: 3 ШТ; Бренд: SIEMENS | Артикул: 3RH2140-1AF00; Производитель: SIEMENS AG; Кол-во: 12 ШТ; Бренд: SIEMENS | Артикул: 3RH2122-2XF40-0LA2; Производитель: SIEMENS AG; Кол-во: 3 ШТ; Бренд: SIEMENS

В результате хотелось бы получить следующее:
Столбец - 3RT2018-2XF42-0LA2 Столбец - 3 ШТ
Столбец - 3RH2362-2AF00 Столбец - 3 ШТ
Столбец - 3RH2140-1AF00 Столбец - 12 ШТ
Столбец - 3RH2122-2XF40-0LA2 Столбец - 3 ШТ

Автор - Harry
Дата добавления - 26.02.2018 в 14:44
китин Дата: Понедельник, 26.02.2018, 14:50 | Сообщение № 2
Группа: Модераторы
Ранг: Экселист
Сообщений: 7013
Репутация: 1073 ±
Замечаний: 0% ±

Excel 2007;2010;2016
пример нам самим рисовать? дык у меня файлы пустые закончились. подождете пока подвезут?


Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
Сообщениепример нам самим рисовать? дык у меня файлы пустые закончились. подождете пока подвезут?

Автор - китин
Дата добавления - 26.02.2018 в 14:50
Harry Дата: Понедельник, 26.02.2018, 15:03 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
пример нам самим рисовать?


Sorry...
Уже добавил
 
Ответить
Сообщение
пример нам самим рисовать?


Sorry...
Уже добавил

Автор - Harry
Дата добавления - 26.02.2018 в 15:03
sboy Дата: Понедельник, 26.02.2018, 15:49 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 724 ±
Замечаний: 0% ±

Excel 2010
Добрый день
В примере всего одна строка (маловато для тестов)
с помощью UDF'ки
Код
=ЕСЛИОШИБКА(Harry($B$3;СТОЛБЕЦ(A1));"")

[vba]
Код
Function Harry(txt As String, i As Integer)
    i = (i - 1) * 2
    arr = Split(Replace(txt, "|", ";"), ";")
        With CreateObject("VBScript.RegExp")
            .Global = True
            .Pattern = "[А-Яа-я; :|-]"
            Harry = .Replace(arr(i), "")
        End With
End Function
[/vba]
К сообщению приложен файл: -2-.xlsm (14.7 Kb)


Яндекс: 410016850021169

Сообщение отредактировал sboy - Понедельник, 26.02.2018, 16:02
 
Ответить
СообщениеДобрый день
В примере всего одна строка (маловато для тестов)
с помощью UDF'ки
Код
=ЕСЛИОШИБКА(Harry($B$3;СТОЛБЕЦ(A1));"")

[vba]
Код
Function Harry(txt As String, i As Integer)
    i = (i - 1) * 2
    arr = Split(Replace(txt, "|", ";"), ";")
        With CreateObject("VBScript.RegExp")
            .Global = True
            .Pattern = "[А-Яа-я; :|-]"
            Harry = .Replace(arr(i), "")
        End With
End Function
[/vba]

Автор - sboy
Дата добавления - 26.02.2018 в 15:49
Harry Дата: Понедельник, 26.02.2018, 16:46 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Спасибо, что посмотрели.
Результат просто великолепный!

В примере всего одна строка (маловато для тестов)

Я вложил файл с нормальным объёмом данных.
Может с ним получиться.
К сообщению приложен файл: __2_____.xlsx (97.9 Kb)


Сообщение отредактировал Harry - Понедельник, 26.02.2018, 16:48
 
Ответить
СообщениеСпасибо, что посмотрели.
Результат просто великолепный!

В примере всего одна строка (маловато для тестов)

Я вложил файл с нормальным объёмом данных.
Может с ним получиться.

Автор - Harry
Дата добавления - 26.02.2018 в 16:46
sboy Дата: Понедельник, 26.02.2018, 17:22 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 724 ±
Замечаний: 0% ±

Excel 2010
Если нет Артикула, что вытягивать в столбец?


Яндекс: 410016850021169
 
Ответить
СообщениеЕсли нет Артикула, что вытягивать в столбец?

Автор - sboy
Дата добавления - 26.02.2018 в 17:22
Harry Дата: Вторник, 27.02.2018, 08:44 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Насколько я видел, артикул есть почти всегда.
Если его нет, то можно заменить на "Модель".
 
Ответить
СообщениеНасколько я видел, артикул есть почти всегда.
Если его нет, то можно заменить на "Модель".

Автор - Harry
Дата добавления - 27.02.2018 в 08:44
sboy Дата: Вторник, 27.02.2018, 09:27 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 724 ±
Замечаний: 0% ±

Excel 2010
Что делаем в случае, если и модель отсутствует?))
вот пример из Вашего файла
Цитата
Производитель: "SIEMENS AG A&D ET", DE; Кол-во: 5 ШТ; Бренд: "SIEMENS"
Производитель: SIEMENS AG; Кол-во: 1 ШТ; Бренд: SIEMENS
Производитель: SIEMENS AG; Кол-во: 0; Бренд: SIEMENS


Яндекс: 410016850021169
 
Ответить
СообщениеЧто делаем в случае, если и модель отсутствует?))
вот пример из Вашего файла
Цитата
Производитель: "SIEMENS AG A&D ET", DE; Кол-во: 5 ШТ; Бренд: "SIEMENS"
Производитель: SIEMENS AG; Кол-во: 1 ШТ; Бренд: SIEMENS
Производитель: SIEMENS AG; Кол-во: 0; Бренд: SIEMENS

Автор - sboy
Дата добавления - 27.02.2018 в 09:27
Harry Дата: Вторник, 27.02.2018, 09:55 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Тогда просто количество указать без артикула или других значений.
 
Ответить
СообщениеТогда просто количество указать без артикула или других значений.

Автор - Harry
Дата добавления - 27.02.2018 в 09:55
sv2014 Дата: Вторник, 27.02.2018, 10:29 | Сообщение № 10
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 226
Репутация: 61 ±
Замечаний: 0% ±

Excel 2013
Harry, еще вариант макроса для исходного(первого) файл-примера,кнопка use

[vba]
Код
Sub help()
  Dim t$, i&: t = Range("B2")
With CreateObject("VBScript.RegExp"): .Pattern = "\d+(?= ШТ)": .Global = True
For i = 0 To .Execute(t).Count - 1: Range("F2").Offset(, 2 * i - 2) = CStr(.Execute(t)(i)): Next
End With
End Sub
[/vba]
[vba]
Код
Sub help1()
  Dim t$, i&: t = Range("B2")
With CreateObject("VBScript.RegExp"): .Pattern = "(?:Артикул\: ).+?(?=;)": .Global = True
For i = 0 To .Execute(t).Count - 1: Range("F2").Offset(, 2 * i - 3) = Mid(CStr(.Execute(t)(i)), 10): Next
End With
End Sub
[/vba]
К сообщению приложен файл: example_26_02_2.xls (41.5 Kb)
 
Ответить
СообщениеHarry, еще вариант макроса для исходного(первого) файл-примера,кнопка use

[vba]
Код
Sub help()
  Dim t$, i&: t = Range("B2")
With CreateObject("VBScript.RegExp"): .Pattern = "\d+(?= ШТ)": .Global = True
For i = 0 To .Execute(t).Count - 1: Range("F2").Offset(, 2 * i - 2) = CStr(.Execute(t)(i)): Next
End With
End Sub
[/vba]
[vba]
Код
Sub help1()
  Dim t$, i&: t = Range("B2")
With CreateObject("VBScript.RegExp"): .Pattern = "(?:Артикул\: ).+?(?=;)": .Global = True
For i = 0 To .Execute(t).Count - 1: Range("F2").Offset(, 2 * i - 3) = Mid(CStr(.Execute(t)(i)), 10): Next
End With
End Sub
[/vba]

Автор - sv2014
Дата добавления - 27.02.2018 в 10:29
Harry Дата: Вторник, 27.02.2018, 10:58 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
sv2014
Да это работает!!!

А как можно сделать чтобы не только одну ячейку обрабатывал?
 
Ответить
Сообщениеsv2014
Да это работает!!!

А как можно сделать чтобы не только одну ячейку обрабатывал?

Автор - Harry
Дата добавления - 27.02.2018 в 10:58
sv2014 Дата: Вторник, 27.02.2018, 11:20 | Сообщение № 12
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 226
Репутация: 61 ±
Замечаний: 0% ±

Excel 2013
Harry, добавил еще один цикл,кнопки в столбце O,,продлил,Ваш вариант файл-примера,первого на 10 строк,работает при наличии текста Артикул,второй файл - пример не смотрел пока.
К сообщению приложен файл: 9337336.xls (43.5 Kb)
 
Ответить
СообщениеHarry, добавил еще один цикл,кнопки в столбце O,,продлил,Ваш вариант файл-примера,первого на 10 строк,работает при наличии текста Артикул,второй файл - пример не смотрел пока.

Автор - sv2014
Дата добавления - 27.02.2018 в 11:20
Harry Дата: Вторник, 27.02.2018, 11:31 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
sv2014, Да, все великолепно работает!
В таком большом массиве данных не важно, если несколько строк будут без ключевого значения и часть значений потеряется.

А можно продлить макрос не на 10 строк, а на 1000 или 1500?))))
 
Ответить
Сообщениеsv2014, Да, все великолепно работает!
В таком большом массиве данных не важно, если несколько строк будут без ключевого значения и часть значений потеряется.

А можно продлить макрос не на 10 строк, а на 1000 или 1500?))))

Автор - Harry
Дата добавления - 27.02.2018 в 11:31
sv2014 Дата: Вторник, 27.02.2018, 11:34 | Сообщение № 14
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 226
Репутация: 61 ±
Замечаний: 0% ±

Excel 2013
Harry, в макросе меняем 10 на произвольное значение типа:

[vba]
Код
Sub help3()
  Dim t$, j&, i&
  With CreateObject("VBScript.RegExp"): .Pattern = "\d+(?= ШТ)": .Global = True
For j = 2 To Range("B" & Rows.Count).End(xlUp).Row
  t = Range("B" & j)
For i = 0 To .Execute(t).Count - 1: Range("F" & j).Offset(, 2 * i - 2) = CStr(.Execute(t)(i)): Next
Next
End With
End Sub
[/vba]
К сообщению приложен файл: 8872525.xls (39.5 Kb)


Сообщение отредактировал sv2014 - Вторник, 27.02.2018, 11:50
 
Ответить
СообщениеHarry, в макросе меняем 10 на произвольное значение типа:

[vba]
Код
Sub help3()
  Dim t$, j&, i&
  With CreateObject("VBScript.RegExp"): .Pattern = "\d+(?= ШТ)": .Global = True
For j = 2 To Range("B" & Rows.Count).End(xlUp).Row
  t = Range("B" & j)
For i = 0 To .Execute(t).Count - 1: Range("F" & j).Offset(, 2 * i - 2) = CStr(.Execute(t)(i)): Next
Next
End With
End Sub
[/vba]

Автор - sv2014
Дата добавления - 27.02.2018 в 11:34
Harry Дата: Вторник, 27.02.2018, 11:42 | Сообщение № 15
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
sv2014, я прошу прошения, но у меня возможности в этом вопросе ограниченны((((
Можно попросить такой же пример выложить как файл 9337336.xls с измененным кол-вом?
1000 вполне подойдет)))
 
Ответить
Сообщениеsv2014, я прошу прошения, но у меня возможности в этом вопросе ограниченны((((
Можно попросить такой же пример выложить как файл 9337336.xls с измененным кол-вом?
1000 вполне подойдет)))

Автор - Harry
Дата добавления - 27.02.2018 в 11:42
sv2014 Дата: Вторник, 27.02.2018, 11:52 | Сообщение № 16
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 226
Репутация: 61 ±
Замечаний: 0% ±

Excel 2013
Harry, Harry, добавил в #14 файл-пример,для любого количества строк,с проверкой наличия слова Артикул


[vba]
Код
Sub help5()
  Dim t$, i&, j&
  With CreateObject("VBScript.RegExp"): .Pattern = "(?:Артикул\: ).+?(?=;)": .Global = True
  For j = 2 To Range("B" & Rows.Count).End(xlUp).Row
     t = Range("B" & j)
      If .test(t) Then
  For i = 0 To .Execute(t).Count - 1
  Range("F" & j).Offset(, 2 * i - 3) = Mid(CStr(.Execute(t)(i)), 10)
  Next
     End If
  Next
End With
End Sub
  
[/vba]


Сообщение отредактировал sv2014 - Вторник, 27.02.2018, 12:35
 
Ответить
СообщениеHarry, Harry, добавил в #14 файл-пример,для любого количества строк,с проверкой наличия слова Артикул


[vba]
Код
Sub help5()
  Dim t$, i&, j&
  With CreateObject("VBScript.RegExp"): .Pattern = "(?:Артикул\: ).+?(?=;)": .Global = True
  For j = 2 To Range("B" & Rows.Count).End(xlUp).Row
     t = Range("B" & j)
      If .test(t) Then
  For i = 0 To .Execute(t).Count - 1
  Range("F" & j).Offset(, 2 * i - 3) = Mid(CStr(.Execute(t)(i)), 10)
  Next
     End If
  Next
End With
End Sub
  
[/vba]

Автор - sv2014
Дата добавления - 27.02.2018 в 11:52
Harry Дата: Вторник, 27.02.2018, 12:08 | Сообщение № 17
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
sv2014,
По кол-ву строк получилось, но там теперь ошибка в макросе.
Не указывает значение артикула, только значение кол-ва.
К сообщению приложен файл: 2431118.png (114.4 Kb)
 
Ответить
Сообщениеsv2014,
По кол-ву строк получилось, но там теперь ошибка в макросе.
Не указывает значение артикула, только значение кол-ва.

Автор - Harry
Дата добавления - 27.02.2018 в 12:08
sv2014 Дата: Вторник, 27.02.2018, 12:21 | Сообщение № 18
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 226
Репутация: 61 ±
Замечаний: 0% ±

Excel 2013
Harry, например для 20 строк,аналогичных первому файл-примеру все работает,как в приложенном файле
К сообщению приложен файл: 1629247.xls (44.5 Kb)
 
Ответить
СообщениеHarry, например для 20 строк,аналогичных первому файл-примеру все работает,как в приложенном файле

Автор - sv2014
Дата добавления - 27.02.2018 в 12:21
Harry Дата: Вторник, 27.02.2018, 12:47 | Сообщение № 19
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
sv2014,
Не работает(((
Артикулы не выводятся.

Может получиться хотя бы до 200 довести, чтобы без ошибок.
 
Ответить
Сообщениеsv2014,
Не работает(((
Артикулы не выводятся.

Может получиться хотя бы до 200 довести, чтобы без ошибок.

Автор - Harry
Дата добавления - 27.02.2018 в 12:47
sboy Дата: Вторник, 27.02.2018, 12:53 | Сообщение № 20
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 724 ±
Замечаний: 0% ±

Excel 2010
еще вариант для "артикулов"
[vba]
Код
Sub Harry1()
Dim arr1()
    Application.ScreenUpdating = False
        For Each cl In Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row)
            If InStr(cl.Value, "Артикул") Then
                arr = Split(cl.Value, "Артикул: ")
                u = UBound(arr) * 2
                ReDim arr1(1 To u)
                    For x = 1 To UBound(arr)
                        i = i + 1
                        arr1(i) = Left(arr(x), InStr(arr(x), ";") - 1)
                        k = InStr(arr(x), "Кол-во:") + 8
                        k1 = InStr(arr(x), "ШТ")
                            If k1 = 0 Then k1 = InStr(k, arr(x), ";")
                        arr1(i + 1) = Mid(arr(x), k, k1 - k)
                        i = i + 1
                    Next x
                cl.Offset(0, 1).Resize(1, UBound(arr1)).Value = arr1
                ReDim arr1(0)
                Set arr = Nothing
                i = 0
            End If
        Next
    Application.ScreenUpdating = True
End Sub
[/vba]
К сообщению приложен файл: 5623586.xlsm (58.1 Kb)


Яндекс: 410016850021169
 
Ответить
Сообщениееще вариант для "артикулов"
[vba]
Код
Sub Harry1()
Dim arr1()
    Application.ScreenUpdating = False
        For Each cl In Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row)
            If InStr(cl.Value, "Артикул") Then
                arr = Split(cl.Value, "Артикул: ")
                u = UBound(arr) * 2
                ReDim arr1(1 To u)
                    For x = 1 To UBound(arr)
                        i = i + 1
                        arr1(i) = Left(arr(x), InStr(arr(x), ";") - 1)
                        k = InStr(arr(x), "Кол-во:") + 8
                        k1 = InStr(arr(x), "ШТ")
                            If k1 = 0 Then k1 = InStr(k, arr(x), ";")
                        arr1(i + 1) = Mid(arr(x), k, k1 - k)
                        i = i + 1
                    Next x
                cl.Offset(0, 1).Resize(1, UBound(arr1)).Value = arr1
                ReDim arr1(0)
                Set arr = Nothing
                i = 0
            End If
        Next
    Application.ScreenUpdating = True
End Sub
[/vba]

Автор - sboy
Дата добавления - 27.02.2018 в 12:53
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Вытащить из одной ячейки множество значений (Формулы/Formulas)
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

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