Любителям задач "со звёздочкой" в экселе...

Программирование на Visual Basic for Applications
KL
Microsoft MVP
 
Сообщения: 483
Зарегистрирован: 30.10.2005 (Вс) 0:31
Откуда: Madrid

Сообщение KL » 18.07.2007 (Ср) 12:57

@Nik писал(а):А вот такая задача :wink: Сделать сортировку таблицы формулами экселя... Т.е. рядом с какой-нить таблицей получить столбец индексов, используя который можно выводить данные из любого параллельного столбца исходной таблицы, отсортированные по заданному столбцу.


Вот тут посмотри (в молодости баловался, а в прошлом году собрал в один файл :-)):

http://www.telefonica.net/web2/kl2/SSHE ... eValues(RU).xls

Для простой сортировки формула слегка попроще:

{=ИНДЕКС($A$1:$B$10;ПОИСКПОЗ(НАИМЕНЬШИЙ(СЧЁТЕСЛИ($A$1:$B$10;"<"&$A$1:$B$10);1+СТРОКА()-СТРОКА($A$1));СЧЁТЕСЛИ($A$1:$B$10;"<"&$A$1:$B$10);0))}
Привет,
KL

@Nik
Обычный пользователь
Обычный пользователь
 
Сообщения: 80
Зарегистрирован: 14.05.2007 (Пн) 12:11

Сообщение @Nik » 18.07.2007 (Ср) 17:28

Вот мой вариант сортировки:
Сортировка только чисел. Выбор столбца по которому сортировать осущ. установкой заглавной буквы "V" над соотв. столбцом.
Вложения
Сортировка.xls
(35 Кб) Скачиваний: 187

KL
Microsoft MVP
 
Сообщения: 483
Зарегистрирован: 30.10.2005 (Вс) 0:31
Откуда: Madrid

Сообщение KL » 18.07.2007 (Ср) 18:36

@Nik писал(а):Вот мой вариант сортировки:
Сортировка только чисел. Выбор столбца по которому сортировать осущ. установкой заглавной буквы "V" над соотв. столбцом.

На самом деле, сортировка только чисел значительно проще :-) См. приложение.
Вложения
Sort.xls
(33 Кб) Скачиваний: 217
Привет,
KL

@Nik
Обычный пользователь
Обычный пользователь
 
Сообщения: 80
Зарегистрирован: 14.05.2007 (Пн) 12:11

Сообщение @Nik » 19.07.2007 (Чт) 9:10

Блин, капец.... у меня просто не хватает слов для восхищения... :)
Ну ничем тебя нельзя ни удивить, ни, тем более, поставить в тупик......

almost_dead
Начинающий
Начинающий
 
Сообщения: 8
Зарегистрирован: 17.08.2006 (Чт) 15:05

Сообщение almost_dead » 19.07.2007 (Чт) 11:20

Уважаемые KL и @Nik (а также все остальные обитатели форума)!
Назрел вопрос, который нужно решить "вот прям сейчас", но экселевских формулах я не силен.
Нужно найти первое значение в строке (массиве), удовлетворяющее заданному условию; условие простое - элемент содержит ненулевое и не ""/Null/Empty значение, причем массив в идеале нужно просматривать с конца.
Строка - одномерный диапазон ячеек, в которой цифры перемежаются с нулями и пустыми ячейками(отсюда и условие про Null, я ведь не знаю, как эксель хранит ячейку, к которой не было обращения); стандартные формулы типа "Поискпоз" не умеют эвалить условия, а ВБА не подходит по ТЗ.
Буду очень благодарен за оперативную помошь!!!

@Nik
Обычный пользователь
Обычный пользователь
 
Сообщения: 80
Зарегистрирован: 14.05.2007 (Пн) 12:11

Сообщение @Nik » 19.07.2007 (Чт) 11:48

Ряд чисел находится в C12:M12.
Вормула такая:
=ПОИСКПОЗ(9E+30;ЕСЛИ(C12:M12;C12:M12))

almost_dead
Начинающий
Начинающий
 
Сообщения: 8
Зарегистрирован: 17.08.2006 (Чт) 15:05

Сообщение almost_dead » 19.07.2007 (Чт) 12:15

Спасибо за ответ!

Не понял условие во втором аргументе - оно же всегда вернет рэйндж, так зачем лишнее?

В аттаче кусок формочки, из-за которой весь сыр-бор 8), "Поискпоз" работает совсем не так, как написанов в хелпе:
(смотрим на столбец АА-АС)
- нормально возвращает лишь при "типе сопоставления" -1, хотя массив ни фига не отсортирован (и не будет :D), но для строки 14 число элементов равно 10, он что, нолик первый посчитал?
- при "0" ничего не работает, непонятно...
- наконец при единице считает так, как надо лишь последнюю строку, в ней 25 элементов, считая нули, а не 9.

Вот поэтому я не использую формулы - никогда не знаешь, каким боком выйдет... прям рулетка какая-то, чесслово :twisted:
Вложения
Пример.xls
(19 Кб) Скачиваний: 177

@Nik
Обычный пользователь
Обычный пользователь
 
Сообщения: 80
Зарегистрирован: 14.05.2007 (Пн) 12:11

Сообщение @Nik » 19.07.2007 (Чт) 14:17

Прошу прощения за невнимательность... Выше была приведена формула массива - вводится при помощи Ctrl+Shift+Enter (должны появиться {}). Она выдаёт смещение до последнего непустого и ненулевого элемента:
{=ПОИСКПОЗ(9E+30;ЕСЛИ(C12:M12;C12:M12))}

Получить само значение посл. элемента можно так:
{=ИНДЕКС(4:4;ПОИСКПОЗ(9E+30;ЕСЛИ(A4:Z4;A4:Z4)))}
Вложения
Пример2.xls
(22 Кб) Скачиваний: 174

almost_dead
Начинающий
Начинающий
 
Сообщения: 8
Зарегистрирован: 17.08.2006 (Чт) 15:05

Сообщение almost_dead » 19.07.2007 (Чт) 14:35

2@Nik:
Огромное спасибо, разобрался, правда я просто посчитал значения - не совсем корректно, но работает :P.
А как написать, чтобы и нули тоже за значение считались?

@Nik
Обычный пользователь
Обычный пользователь
 
Сообщения: 80
Зарегистрирован: 14.05.2007 (Пн) 12:11

Сообщение @Nik » 19.07.2007 (Чт) 14:48

=ИНДЕКС(4:4;ПОИСКПОЗ(9E+30;A4:Z4))

KL
Microsoft MVP
 
Сообщения: 483
Зарегистрирован: 30.10.2005 (Вс) 0:31
Откуда: Madrid

Сообщение KL » 19.07.2007 (Чт) 15:58

см. пример
Вложения
test3.xls
(23 Кб) Скачиваний: 176
Привет,
KL

KL
Microsoft MVP
 
Сообщения: 483
Зарегистрирован: 30.10.2005 (Вс) 0:31
Откуда: Madrid

Сообщение KL » 19.07.2007 (Чт) 17:35

@Nik писал(а):=ИНДЕКС(4:4;ПОИСКПОЗ(9E+30;A4:Z4))


или так:

=ПРОСМОТР(9E+307;A4:Z4)
Привет,
KL

@Nik
Обычный пользователь
Обычный пользователь
 
Сообщения: 80
Зарегистрирован: 14.05.2007 (Пн) 12:11

Сообщение @Nik » 19.07.2007 (Чт) 17:47

Да-да, я обратил внимание... :wink:

KL
Microsoft MVP
 
Сообщения: 483
Зарегистрирован: 30.10.2005 (Вс) 0:31
Откуда: Madrid

Сообщение KL » 20.07.2007 (Пт) 12:12

@Nik писал(а):Да-да, я обратил внимание... :wink:


а в русской версии будет еще короче вот так :-)

=ГПР(99^99;A4:Z4;1)
=ГПР(9E+307;A4:Z4;1)
Привет,
KL

Krasnaja Shapka
Обычный пользователь
Обычный пользователь
Аватара пользователя
 
Сообщения: 87
Зарегистрирован: 26.10.2006 (Чт) 12:13
Откуда: Киев

Сообщение Krasnaja Shapka » 07.09.2007 (Пт) 16:58

вопрос: надо перевернуть массив...
т.е. есть:
Код: Выделить всё
      A      B      С      D
1     1      2      3      4

надо попарно перемножить и сложить {1,2,3,4} (A1:D1) на {4,3,2,1} (формально D1:A1)
что-то типа =СУММПРОИЗВ(A1:D1, перевернутый(A1:D1))
Если ясность вашего объяснения исключает ложное толкование, все равно кто-то поймет вас неправильно.

KL
Microsoft MVP
 
Сообщения: 483
Зарегистрирован: 30.10.2005 (Вс) 0:31
Откуда: Madrid

Сообщение KL » 08.09.2007 (Сб) 13:28

Krasnaja Shapka писал(а):вопрос: надо перевернуть массив...
т.е. есть:
Код: Выделить всё
      A      B      С      D
1     1      2      3      4

надо попарно перемножить и сложить {1,2,3,4} (A1:D1) на {4,3,2,1} (формально D1:A1)
что-то типа =СУММПРОИЗВ(A1:D1, перевернутый(A1:D1))


Можно попробовать так, но формула - летучая:

=СУММПРОИЗВ(A1:D1*Ч(СМЕЩ(A1;;ЧИСЛСТОЛБ(A1:D1)-СТОЛБЕЦ(ИНДЕКС(1:1;1):ИНДЕКС(1:1;ЧИСЛСТОЛБ(A1:D1)));1)))

Или вот такая формула массива (CTRL+SHIFT+ENTER), но зато короче и нелетучая:

=СУММ(A1:D1*ВПР(9E+307;0+A1:D1;НАИБОЛЬШИЙ(СТОЛБЕЦ(A1:D1);СТОЛБЕЦ(A1:D1))))
Последний раз редактировалось KL 08.09.2007 (Сб) 23:55, всего редактировалось 1 раз.
Привет,
KL

Krasnaja Shapka
Обычный пользователь
Обычный пользователь
Аватара пользователя
 
Сообщения: 87
Зарегистрирован: 26.10.2006 (Чт) 12:13
Откуда: Киев

Сообщение Krasnaja Shapka » 10.09.2007 (Пн) 9:28

огромное спасибо!
Если ясность вашего объяснения исключает ложное толкование, все равно кто-то поймет вас неправильно.

Krasnaja Shapka
Обычный пользователь
Обычный пользователь
Аватара пользователя
 
Сообщения: 87
Зарегистрирован: 26.10.2006 (Чт) 12:13
Откуда: Киев

Сообщение Krasnaja Shapka » 10.09.2007 (Пн) 13:12

p.s. один нюанс.. я наваял функцию ReverseArr, которая переворачивает массив, в вба... как проверить что быстрее пашет, ReverseArr или функция, которую привел KL?
Если ясность вашего объяснения исключает ложное толкование, все равно кто-то поймет вас неправильно.

KL
Microsoft MVP
 
Сообщения: 483
Зарегистрирован: 30.10.2005 (Вс) 0:31
Откуда: Madrid

Сообщение KL » 10.09.2007 (Пн) 13:37

Krasnaja Shapka писал(а):p.s. один нюанс.. я наваял функцию ReverseArr, которая переворачивает массив, в вба... как проверить что быстрее пашет, ReverseArr или функция, которую привел KL?


Выкладывай функцию - проверю, но если это перебор...
Привет,
KL

Krasnaja Shapka
Обычный пользователь
Обычный пользователь
Аватара пользователя
 
Сообщения: 87
Зарегистрирован: 26.10.2006 (Чт) 12:13
Откуда: Киев

Сообщение Krasnaja Shapka » 10.09.2007 (Пн) 15:57

гм... а как не перебором? :)
Код: Выделить всё
Function ReverseArr(myArray As Variant, Optional Del As String = ";") As Variant ' Del - это возможный разделитель, если myarray задан как строка (например, как "1;3;2;5")
Dim myCell As Range
Dim i As Long
Dim CellsNum As Long ' кол-во элементов массива
Dim TempArr() As Variant
Dim OriginArr As Variant

Select Case TypeName(myArray)
    Case "Range"
        With myArray
            CellsNum = .Cells.Count - 1
            If CellsNum = 0 Then
                    ReverseArr = ReverseArr(.Value, Del) ' если myarray это одна ячейка - то считаем это массивом заданным как строка
                    Exit Function
            Else
                    ReDim TempArr(0 To CellsNum)
                    i = 0
                    For Each myCell In .Cells
                        TempArr(CellsNum - i) = myCell.Value
                        i = i + 1
                    Next myCell
            End If
        End With
    Case "String"
        OriginArr = Split(myArray, Del)
        CellsNum = UBound(OriginArr)
        ReDim TempArr(0 To CellsNum)
        For i = 0 To CellsNum
            TempArr(i) = OriginArr(CellsNum - i)
        Next i
    Case Else
        ReverseArr = "#ERROR"
        Exit Function
End Select
ReverseArr = TempArr
End Function
Если ясность вашего объяснения исключает ложное толкование, все равно кто-то поймет вас неправильно.

KL
Microsoft MVP
 
Сообщения: 483
Зарегистрирован: 30.10.2005 (Вс) 0:31
Откуда: Madrid

Сообщение KL » 10.09.2007 (Пн) 17:10

Krasnaja Shapka писал(а):гм... а как не перебором? :)


В том-то и проблема :-)

Результаты моих промеров для 256 элементов в 1000 строк в Vista Business, Office 2007 Ultimate, Intel Core 2 Duo 2.16 MHZ, Ram 3325 MB

1) =Ч(СМЕЩ(A1;;ЧИСЛСТОЛБ(A1:IV1)-СТОЛБЕЦ(ИНДЕКС(1:1;1):ИНДЕКС(1:1;ЧИСЛСТОЛБ(A1:IV1)));1))

Время перерасчета (миллисек): 8.08, 7.7, 7.28

2) =ReverseArr(A1:IV1)

Время перерасчета (миллисек): 925.3, 945.31, 938.25

3) {=ВПР(9E+307;0+A1:IV1;НАИБОЛЬШИЙ(СТОЛБЕЦ(A1:IV1);СТОЛБЕЦ(A1:IV1)))}

Время перерасчета (миллисек): 12603.62, 12574.99, 12592.73
т.ч. этот мой вариант сразу на свалку истории :-)
Привет,
KL

Krasnaja Shapka
Обычный пользователь
Обычный пользователь
Аватара пользователя
 
Сообщения: 87
Зарегистрирован: 26.10.2006 (Чт) 12:13
Откуда: Киев

Сообщение Krasnaja Shapka » 11.09.2007 (Вт) 12:52

мда... значит макрос рулит...

еще вопрос... а нафига в первой формуле
...СТОЛБЕЦ(ИНДЕКС(1:1;1):ИНДЕКС(1:1;ЧИСЛСТОЛБ(A1:IV1))),
которое можно заменить на
...СТОЛБЕЦ(A1:IV1)???
Если ясность вашего объяснения исключает ложное толкование, все равно кто-то поймет вас неправильно.

Krasnaja Shapka
Обычный пользователь
Обычный пользователь
Аватара пользователя
 
Сообщения: 87
Зарегистрирован: 26.10.2006 (Чт) 12:13
Откуда: Киев

Сообщение Krasnaja Shapka » 11.09.2007 (Вт) 13:04

а... тьфу... нельзя заменить в общем случае, да... можно лько на
...СТОЛБЕЦ(A1:IV1)+СТОЛБЕЦ(A1)-1
Если ясность вашего объяснения исключает ложное толкование, все равно кто-то поймет вас неправильно.

Krasnaja Shapka
Обычный пользователь
Обычный пользователь
Аватара пользователя
 
Сообщения: 87
Зарегистрирован: 26.10.2006 (Чт) 12:13
Откуда: Киев

Сообщение Krasnaja Shapka » 11.09.2007 (Вт) 13:11

э... так может макрос будет работать быстее, если вместо всего кода написать Evaluate("Ч(СМЕЩ(A1;;ЧИСЛСТОЛБ(A1:IV1)-СТОЛБЕЦ(ИНДЕКС(1:1;1):ИНДЕКС(1:1;ЧИСЛСТОЛБ(A1:IV1)));1))") или посчитать тоже самое через Application.WorksheetFunction???
или в таком случае ReverseArr тоже летучей станет?
Если ясность вашего объяснения исключает ложное толкование, все равно кто-то поймет вас неправильно.

KL
Microsoft MVP
 
Сообщения: 483
Зарегистрирован: 30.10.2005 (Вс) 0:31
Откуда: Madrid

Сообщение KL » 11.09.2007 (Вт) 13:25

Krasnaja Shapka писал(а):...СТОЛБЕЦ(ИНДЕКС(1:1;1):ИНДЕКС(1:1;ЧИСЛСТОЛБ(A1:IV1))),
... можно заменить на
...СТОЛБЕЦ(A1:IV1)???


можно, но при двух условиях:

1) первая ячейка будет всегда в столбце А

2а) если диапазон кончается последним столбцом листа (напр.: А1:IV1 в Excel97-2003 или A1:XFD в Excel2007), никто не введет дополнительных столбцов перед столбцом A

2б) если диапазон не доходит до последнего столбца листа (напр.: А1:Z1), никто не введет дополнительных столбцов между первым и последним столбцами диапазона

Krasnaja Shapka писал(а):а... тьфу... нельзя заменить в общем случае, да... можно лько на
...СТОЛБЕЦ(A1:IV1)+СТОЛБЕЦ(A1)-1

Да, это нормальная альтернатива, даже слегка побыстрее в Excel2007.

Krasnaja Shapka писал(а):э... так может макрос будет работать быстее, если вместо всего кода написать Evaluate("Ч(СМЕЩ(A1;;ЧИСЛСТОЛБ(A1:IV1)-СТОЛБЕЦ(ИНДЕКС(1:1;1):ИНДЕКС(1:1;ЧИСЛСТОЛБ(A1:IV1)));1))")...

насколько мне известно, функция OFFSET(СМЕЩ) утрачивает способность возвращать массив при вызове ее через Evaluate :-( Но даже если закрыть глаза на это обстоятельство, то след. функция в 1000 ячеек затрачивает у меня 414 миллисек:

Код: Выделить всё
Function ReverseArr(rng As Range)
    Dim strAddress As String
    strAddress = rng.Address(, , , True)
    ReverseArr = Evaluate("N(OFFSET(INDEX(" & strAddress & ",1),,COLUMNS(" & strAddress & ")-COLUMN(INDEX(1:1,1):INDEX(1:1,COLUMNS(" & strAddress & "))),1))")
End Function


Krasnaja Shapka писал(а):...или посчитать тоже самое через Application.WorksheetFunction???

придется полностью менять концепцию решения - функция OFFSET недоступна в коллекции WorksheetFunction, а на ней все и строится :-(

Если очень хочется сделать, чтобы в ячейке было что-то короткое, попробуй присвоить формуле имя через меню Вставка-Имя-Присвоить, только следи за относительными ссылками.

Krasnaja Shapka писал(а):или в таком случае ReverseArr тоже летучей станет?

Пользовательскую функцию можно сделать летучей только с помощью инструкции Application.Volatile.
Последний раз редактировалось KL 11.09.2007 (Вт) 13:30, всего редактировалось 1 раз.
Привет,
KL

@Nik
Обычный пользователь
Обычный пользователь
 
Сообщения: 80
Зарегистрирован: 14.05.2007 (Пн) 12:11

Сообщение @Nik » 06.02.2008 (Ср) 12:43

Все, кому была интересна эта тема, приглашаются сюда: http://www.sql.ru/forum/actualthread.aspx?tid=501769&pg=4#5240679

Самир38
Начинающий
Начинающий
 
Сообщения: 1
Зарегистрирован: 11.04.2016 (Пн) 12:50

Re: Любителям задач "со звёздочкой" в экселе...

Сообщение Самир38 » 11.04.2016 (Пн) 12:56

Всем привет, есть некая задачка для решение.
в Эксееле в одной ячейке написан текст,допустим:

Привет||как дела||Нормально||(Это все одна ячейка)

Как мне узнать длину каждого слова в ячейке( без учета знака "||")Можно чтобы длина каждого слова выводилась в разных ячейках.

pronto
Постоялец
Постоялец
 
Сообщения: 580
Зарегистрирован: 04.12.2005 (Вс) 6:20
Откуда: Владивосток

Re: Любителям задач "со звёздочкой" в экселе...

Сообщение pronto » 12.04.2016 (Вт) 4:49

Текстовые функции в помощь!
Вложения
Разбивка строки на подстроки по символу-разделителю.xls
(22 Кб) Скачиваний: 66
O, sancta simplicitas!

ger_kar
Продвинутый гуру
Продвинутый гуру
Аватара пользователя
 
Сообщения: 1888
Зарегистрирован: 19.05.2011 (Чт) 19:23
Откуда: Кыргызстан, Иссык-Куль, г. Каракол

Re: Любителям задач "со звёздочкой" в экселе...

Сообщение ger_kar » 12.04.2016 (Вт) 6:52

Самир38
Это решение чисто с использованием формул Excel, а можно и на VBA тоже самое написать Использую строковые функции:
Split - разбиение на подстроки по разделителю,
InStr - нахождение позиции символа или символов в строке,
Len - длина строки.

Тебе вообще какой вариант то нужен был? С формулами или на VBA?
Бороться и искать, найти и перепрятать

Пред.

Вернуться в VBA

Кто сейчас на конференции

Сейчас этот форум просматривают: нет зарегистрированных пользователей и гости: 5

    TopList