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

Пожалуйста, войдите или зарегистрируйтесь.


Расширенный поиск  

Новости:

Из правил форума: Тема должна отражать суть вопроса, топики типа "help please" будут удаляться!

Автор Тема: Сортировка данных внутри ячейки, как?  (Прочитано 8308 раз)

0 Пользователей и 1 Гость просматривают эту тему.

spono

  • Пользователь
  • **
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 39

Здравствуйте,
Нужно отсортировать данные внутри каждой ячейки не сортируя ячейки между собой.
Каждая строчка данных в ячейке разделена <br>, пример:

многофункциональное устройство - canon clc2620<br>
многофункциональное устройство - canon clc3200<br>
многофункциональное устройство - canon clc3220<br>
многофункциональное устройство - canon ir-c2620<br>
многофункциональное устройство - canon ir-c2620n<br>
многофункциональное устройство - canon ir-c3200<br>
многофункциональное устройство - canon ir-c3200n<br>
многофункциональное устройство - canon ir-c3220<br>
многофункциональное устройство - canon ir-c3220n

Классический метод сортировки сортирует строчки и столбцы.
Подскажите пжл макрос и как его интегрировать в Эксель.
Записан

GWolf

  • Старожил
  • ****
  • Уважение: +50/-0
  • Оффлайн Оффлайн
  • Сообщений: 929

Добрый день!
Я тоже, беря пример с Вас, позволю без примера...

Итак, Интегрировать в Excel просто: Alt+F11 и Вы попадаете в среду разработки макросов VBA. Ну или на ярлычке листа ПКМ (правой кнопкой мышки)  и кликнуть по Исходный текст

Далее в редакторе пишем:
Sub SortNeTarKakNado()
   'Между этими "субчиками" и разместим код макроса
End Sub


собственно сам мкрос, что он делает? Пробегает по ячейкам выделенного блока ячеек (ну, тут наверное следует определять что собственно выделено: часть строки - столбца или часть нескольких строк - столбцов?!). Значения каждой ячейки разбивает по параметру <br>, в одномерный массив. Массив сортирует, ну скажем "методом пузырька". Результат сортировки записывает в ячейку (ту же откуда была взята исходная информация, либо правее - левее -выше - ниже исходной ячейки).

Вот пример функции сортирующей массив, причем одномерный массив в данном случае - частный случай. Эта функция может сортировать и двумерные массивы:

Function sortArr(spisok() As String, nomColSort As Integer, glubina As Integer)
    Dim rrBl() As String
    Dim endArr As Long, i As Long, j As Long, k As Long

    i = 0
    j = 0
    k = 0
    endArr = 0
   
    If glubina > 0 Then
        endArr = UBound(spisok, glubina)
        For i = 1 To endArr
            For j = endArr To i Step -1
                If spisok(nomColSort, j - 1) > spisok(nomColSort, j) Then
                    k = 0
                    For k = LBound(spisok, 1) To UBound(spisok, 1)
                        ReDim Preserve rrBl(k)
                        rrBl(k) = spisok(k, j - 1)
                    Next k

                    k = 0
                    For k = LBound(spisok, 1) To UBound(spisok, 1)
                        spisok(k, j - 1) = spisok(k, j)
                        spisok(k, j) = rrBl(k)
                    Next k
                End If
            Next j
        Next i
    Else
        endArr = UBound(spisok)
        For i = 1 To endArr
            For j = endArr To i Step -1
                If spisok(j - 1) > spisok(j) Then
                    tmp = spisok(j - 1)
                    spisok(j - 1) = spisok(j)
                    spisok(j) = tmp
                End If
            Next j
        Next i
    End If
   
    sortArr = spisok
   
    i = 0
    j = 0
    k = 0
    Erase rrBl
    Erase spisok
End Function
И так, если будут вопросы при реализации - пишите, постараюсь ответить.
« Последнее редактирование: 01.04.2015, 11:54:46 от vikttur »
Записан
Путей к вершине - множество. Этот один из многих!

spono

  • Пользователь
  • **
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 39

Спасибо за помощь, в меру своих возможностей и полноты Вашего мануала сделал слеюдующее:

1. открыл таблицу excel
2. нажал alt+f11
3. вставил
Sub SortNeTarKakNado()
+внутри приведенны Вами код   
End Sub
4. сохранил в среде VBA нажав на дискетку
5. перешел в View Microsoft Excel
6. Выделил столбец с нужными ячейками
7. Сервис > Макрос > Макросы (находится Эта книга) > команда Выполнить

Тогда, получаю ошибку:
"Compile error:
Expected end Sub"

Скрины ошибки и файл .xls прикреплен к письму, спасибо.
Записан

Serge 007

  • Администратор
  • Ветеран
  • *****
  • Уважение: +340/-0
  • Оффлайн Оффлайн
  • Сообщений: 2 951
    • Мир Excel


3. вставил
Sub SortNeTarKakNado()
+внутри приведенны Вами код    
End Sub

Вот этого не надо было делать.
Попытался нарисовать последовательность действий в редакторе VBA
Записан
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Яндекс-деньги: 41001419691823 | WMR:126292472390

spono

  • Пользователь
  • **
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 39

Указанные Вами шаги на картинке я выполнил.

1. На картинке не указанно куда вставлять:
Sub SortNeTarKakNado()
End Sub

2. Как в таком случае, запустить макрос в Эксель?
Сервис > Макрос > Макросы (ПУСТО)
 
Записан

MCH

  • Постоялец
  • ***
  • Уважение: +44/-0
  • Оффлайн Оффлайн
  • Сообщений: 433

чтото типа такого
Записан

Serge 007

  • Администратор
  • Ветеран
  • *****
  • Уважение: +340/-0
  • Оффлайн Оффлайн
  • Сообщений: 2 951
    • Мир Excel

Указанные Вами шаги на картинке я выполнил.
Теперь возвращайтесь в Excel (Alt+F11) и используйте функцию на листе.
Записан
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Яндекс-деньги: 41001419691823 | WMR:126292472390

GWolf

  • Старожил
  • ****
  • Уважение: +50/-0
  • Оффлайн Оффлайн
  • Сообщений: 929

Попытался нарисовать последовательность действий в редакторе VBA
Здравствуйте, Sergio007!
Все хочу написать мануал, со скриншотами, для начинающих пользователей, типа как настроить VBA - редакктор, да все времени не хватает. Может продолжите Вашеп начинание и наверно его следует закрепить в начале раздела! То-то новички на ниве прогерства будут благодарны!
Удачи!
Записан
Путей к вершине - множество. Этот один из многих!

spono

  • Пользователь
  • **
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 39

Это обязательно нужно ))
« Последнее редактирование: 01.04.2015, 11:55:55 от vikttur »
Записан

spono

  • Пользователь
  • **
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 39

Цитировать
чтото типа такого
MCH да, сортировка в данном случае производится!!!
Однако существует косяк - форматирование отсортированных данных в одну строчку внутри ячейки!
Пример, имеем:
222
444
111
333

После использования "Вставка функции" > "Полный алфавитный перечень" > "SortBr" > "Указываем ячейку с исходными данными"
На выходе получаем:
111222333444

Исходные данные вместо 4 строк принимаю вид одной строки.

Интересно, что с моими данными(из первого поста) происходит тоже самое, однако при клике мышью внутри отсортированной ячейки и далее кликом на другой ячейке.
В ячейках с отсортированными данными снова появляются строчки.
« Последнее редактирование: 20.03.2012, 19:46:39 от spono »
Записан

spono

  • Пользователь
  • **
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 39

1. Использую приведенный код:

Function sortArr(spisok() As String, nomColSort As Integer, glubina As Integer)
    Dim rrBl() As String
    Dim endArr As Long, i As Long, j As Long, k As Long

    i = 0
    j = 0
    k = 0
    endArr = 0
   
    If glubina > 0 Then
        endArr = UBound(spisok, glubina)
        For i = 1 To endArr
            For j = endArr To i Step -1
                If spisok(nomColSort, j - 1) > spisok(nomColSort, j) Then
                    k = 0
                    For k = LBound(spisok, 1) To UBound(spisok, 1)
                        ReDim Preserve rrBl(k)
                        rrBl(k) = spisok(k, j - 1)
                    Next k

                    k = 0
                    For k = LBound(spisok, 1) To UBound(spisok, 1)
                        spisok(k, j - 1) = spisok(k, j)
                        spisok(k, j) = rrBl(k)
                    Next k
                End If
            Next j
        Next i
    Else
        endArr = UBound(spisok)
        For i = 1 To endArr
            For j = endArr To i Step -1
                If spisok(j - 1) > spisok(j) Then
                    tmp = spisok(j - 1)
                    spisok(j - 1) = spisok(j)
                    spisok(j) = tmp
                End If
            Next j
        Next i
    End If
   
    sortArr = spisok
   
    i = 0
    j = 0
    k = 0
    Erase rrBl
    Erase spisok
End Function

2. Делаю по Инструкциям Serge 007

3. "Вставка функции" > "Полный алфавитный перечень" > "SortBr" > "Указываем ячейку с исходными данными" > "SortArr"
Окно "Аргументы функции"
Spisok
NomColSort
Glubina

Методом исключения )) указываю различные вариации 3 аргументов выше.
Однако в 100% случаях получаю: #Знач!
Что не так?
Записан

MCH

  • Постоялец
  • ***
  • Уважение: +44/-0
  • Оффлайн Оффлайн
  • Сообщений: 433

сортировка в данном случае производится!!!
Однако существует косяк...
ну так приложите пример в виде xls файла с информацией "что есть" и "что хочу"
А то гадать по картинкам не получается.
ɔнǝɔɐdʇɔʞє ǝн ʁ
Записан

spono

  • Пользователь
  • **
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 39

excel файл с примерами прикреплен к сообщению
« Последнее редактирование: 01.04.2015, 11:56:32 от vikttur »
Записан

MCH

  • Постоялец
  • ***
  • Уважение: +44/-0
  • Оффлайн Оффлайн
  • Сообщений: 433

в Формате ячейки в разделе "выравнивание" поставте галку "переносить по словам"
Записан

GWolf

  • Старожил
  • ****
  • Уважение: +50/-0
  • Оффлайн Оффлайн
  • Сообщений: 929

Добрый день, коллеги!

Ставить галки, дело хорошее. Но, макрос для того и пишется, что бы работал независимо от выставленных галок.
И тут вариантов решения несколько.
Один вариант в том, что бы проверять установленные в системе настройки, важные для работы макроса и, ежли оные отличаются - программно их менять, на время работы макроса, а по окончании работы все возвернуть как було!
Второй вариант, применительно к этой задаче: при формировании строки массива, между элементами массива, вставлять принудительный перевод строки (chr(10)). Приведенная последовательность "111222333444", будет в массиве выглядеть так:
"111" & chr(10) & "222" & chr(10) & "333" & chr(10) & "444".
Записан
Путей к вершине - множество. Этот один из многих!

MCH

  • Постоялец
  • ***
  • Уважение: +44/-0
  • Оффлайн Оффлайн
  • Сообщений: 433

Ставить галки, дело хорошее. Но, макрос для того и пишется, что бы работал независимо от выставленных галок.
А я макрос и не писал.
UDF которую я приводил в примере сама раставляет vbLf /Chr(10) после <br>
если открыть файл SortArray212.xls , то там есть переносы, толко в формате ячейки не разрешено переносить слова, поэтому текст отображается одной строкой
такой же текст (одной строкой), но без сортировки получится если просто записать формулу =A5, и не указать разрешение переносов.
Записан

GWolf

  • Старожил
  • ****
  • Уважение: +50/-0
  • Оффлайн Оффлайн
  • Сообщений: 929

... UDF которую я приводил в примере сама раставляет vbLf /Chr(10) после <br>
если открыть файл SortArray212.xls , то там есть переносы, толко в формате ячейки не разрешено переносить слова, поэтому текст отображается одной строкой
такой же текст (одной строкой), но без сортировки получится если просто записать формулу =A5, и не указать разрешение переносов.

ну тогда, все же более универсальным будет:
Цитировать
Один вариант в том, что бы проверять установленные в системе настройки, важные для работы макроса и, ежли оные отличаются - программно их менять, на время работы макроса, а по окончании работы все возвернуть как було!
Или я чего то путаю?
Записан
Путей к вершине - множество. Этот один из многих!

spono

  • Пользователь
  • **
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 39

MCH Вы представили вариант, когда сортировка происходит в ячейках в конце значений которых стоит <br>
Я думаю всем будет интересно: Реализуйте пжл Макрос, чтобы в сортировка в ячейке происходила по значениям в конце которых стояла ","
или вообще было пусто, примеры:
333,
222,
444,
111

        или
333
222
444
111

« Последнее редактирование: 01.04.2015, 11:57:27 от vikttur »
Записан

MCH

  • Постоялец
  • ***
  • Уважение: +44/-0
  • Оффлайн Оффлайн
  • Сообщений: 433

так?
Записан

spono

  • Пользователь
  • **
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 39

Да, отлично. Я думаю это много кому пригодится, спасибо ))
Записан

zamboga

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 1

так?

Спасибо большое!! 2 дня рыл инет в поисках решения=)
Работает, как надо, сортирует внутри ячейки по алфавиту, слова или фразы разделяются символом, который можно задать в формуле.

Тем, кто как и я, придет через поисковик: готовый файл с уже зашитым скриптом можно скачать с форума только после регистрации. "Ручками" так, как описано на первой странице у меня не получилось

Записан

Ponaroshku

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 1

Форумчане, возникла необходимость отсортировать данные в каждой ячейке массива (в ячейках числа, надо отсортировать в них цифры). Воспользоваться функцией, которую выложил в этой теме МСН, не получилось, тк не понятно, что подставлять в NomColSort и Glubina.
Может быть у кого-то есть подобная функция?
Записан

vikttur

  • Глобальный модератор
  • Старожил
  • *****
  • Уважение: +49/-0
  • Оффлайн Оффлайн
  • Сообщений: 990

Создайте тему, покажите там пример...
Записан

Татьяна Вячеславовна

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 1

так?

Не могу скачать настройки...
Записан
 



Темы без ответов

07.02.2019 01:36 Как удалить дубликаты из выпадающего связанного списка? 105
20.01.2019 12:38 Все варианты частичного суммирования 200
13.01.2019 12:24 Заполнение диапазона числами - в виде кластеров 156
30.09.2018 10:24 Расчет процентов за определенный период (месяц) с учетом изменений и платежей 629
03.03.2018 00:00 Подсчет отработанного времени, за исключением заранее определенных перерывов 1434
14.02.2018 10:11 Подготовить читабельную отчетность по платежам 1437
23.01.2018 13:46 Найти вероятность повторной покупки 1316
12.01.2018 23:56 Сделать отчет на Power BI (Dashboard) 1848
06.09.2017 10:43 Solver VBA не решает гиперболическое уравнение, но при этом решает гармоническое 1604
17.08.2017 12:15 Гиперссылка и фильтр одновременно макрос 2020





Яндекс цитирования msexcel.ru Яндекс.Метрика

Страница сгенерирована за 0.144 секунд. Запросов: 151.