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

[@Nik]

Предлагаю в этой теме задавать интересные задачки "повышенной сложности", с которыми нам приходится периодически сталкиваться...
Ожидается, что в этой теме максимально будут собраны все экселевские "штучки", хитрости, нюансы и прочие полезные моменты.

[@Nik]

У меня есть знакомый, который частенько мне предлагает сделать каверзные задачи в экселе.... Приведу пример последней:

Необходимо реализовать ограничитель букв в словах предложения. Т.е. если длина слова в текстовой ячейке, содержащей несколько слов, скажем, 4 символа или меньше, то оставить его без изменений, если больше - "обрезать" до 4х символов. Например:
из строки: "ДАТА передачи первичной документации"
получить: "ДАТА пере перв доку"

Всем любители "поломать голову" предлагается заняться этим....

Я уже сделал свой вариант, но не знаю как его сюда загрузить... (подскажите плиз...) Очень хотелось бы увидеть кто ещё и каким путём реализует такую задачку...

[Pavel55]

А вот так ? )

Код: Выделить всё

Sub Macro1()
    Dim iText As String
    Dim iArray() As String
    Dim i&
    iText = Range("A1").Text
    iArray = Split(iText, " ")
    For i = 0 To UBound(iArray)
        ActiveCell.Value = ActiveCell.Value & Left(iArray(i), 4) & " "
    Next i
End Sub

[@Nik]

Всё верно, работает.... Только я забыл сказать, что подразумевалось реализовать это именно формулами экселя..... Можете сделать ещё одну попытку.....

[@Nik]

Вроде разобрался как прицепить вложение....
Ниже привожу свой вариант... Буду благодарен всем, кто сможет хоть как-то оптимизировать моё решение... У меня, например, не получилось сцепить ячейки никаким другим способом, кроме как "=I17&I18&I19&I20&.........". Есть ли другой вариант (с указанием сцепляемого диапазона) ?

[anvg]

@Nik
А при чём тут VBA?

[Pavel55]

Хм, что-то у вас больно сложно. Я не силён в формулах и функциях Excel, но думаю это можно всё решить формулой в одной ячейке, а если ещё применить формулы массива, то эта формула будет небольшой.

Вот смотрите, я тут потестировал функцию ПОИСК, с помощью которой я находил пробелы между словами и смог укоротить 2 первых слова.

=ПСТР(ВсяСтрока;1;ПОИСК(" ";ВсяСтрока;1)-1) & ПСТР(ВсяСтрока;ПОИСК(" ";ВсяСтрока;1);ПОИСК(" ";ВсяСтрока;2))

Но кто-то я думаю сделает это и короче и для всех слов сразу. Надо просто подумать, как сделать массив всех слов, используя пробел между словами.

[uhm]

У-у, я то думал...

Высший пилотаж - решить такую задачу с помощью формулы В ОДНОЙ ЯЧЕЙКЕ, которая не будет использовать промежуточных ячеек, и которая не будет зависеть от количества слов в исходной ячейке. Тот вид, в котором задача решена сейчас, ни одному критерию не удовлетворяет, и никакого интереса не представляет - при такой постановке, задача тривиальна.

Подозреваю, правда, что в данной задаче решение, удовлетворяющее моим критериям невозможно.

[@Nik]

Высшый пилотаж в экселе - это научиться разумно сочетать подпрограммы на ВБА с формулами экселя. Т.е. те моменты, которые проще реализовать при помощи формул - делать их формулами, а которые проще программой (как в данном случае) - сделать программой. Оптимальный вариант, я думаю, будет таким:

Код: Выделить всё

Function CutWords(WordsSource As Range, SymbNum As Long) As String
    Dim iText As String
    Dim iArray() As String
    Dim i&
    Dim NewStrng As String
    iText = WordsSource
    iArray = Split(iText, " ")
    For i = 0 To UBound(iArray)
        NewStrng = NewStrng & Left(iArray(i), SymbNum) & " "
    Next i
    CutWords = NewStrng
End Function

т.е. создать пользовательскую функцию.... Тогда формулу "=CutWords(C9;4)" можно будет распространять в любом направлении, чего не сделаешь в случае реализацией формулами.... Получилось предельно просто, наглядно и рационально.... за что я и люблю эксель!

[KL]

У-у, я то думал...

Высший пилотаж - решить такую задачу с помощью формулы В ОДНОЙ ЯЧЕЙКЕ, которая не будет использовать промежуточных ячеек, и которая не будет зависеть от количества слов в исходной ячейке. Тот вид, в котором задача решена сейчас, ни одному критерию не удовлетворяет, и никакого интереса не представляет - при такой постановке, задача тривиальна.

Подозреваю, правда, что в данной задаче решение, удовлетворяющее моим критериям невозможно.

Думаю, что подозреваешь правильно . Чистой формулой в Excel можно перевести строку в массив:

=ПСТР(A1;НАЙТИ("|";ПОДСТАВИТЬ(" "&A1;" ";"|";СТРОКА(ИНДЕКС(A:A;1):ИНДЕКС(A:A;1+ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;" ";""))))));НАЙТИ("|";ПОДСТАВИТЬ(A1&" ";" ";"|";СТРОКА(ИНДЕКС(A:A;1):ИНДЕКС(A:A;1+ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;" ";""))))))-НАЙТИ("|";ПОДСТАВИТЬ(" "&A1;" ";"|";СТРОКА(ИНДЕКС(A:A;1):ИНДЕКС(A:A;1+ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;" ";"")))))))

...а вот обратно, если кол-во членов массива нефиксированно, невозможно.

Уже года 3 прошу Microsoft ввести функцию ARRAY.CONCATENATE(), да все впустую

[KL]

Высшый пилотаж в экселе - это научиться разумно сочетать подпрограммы на ВБА с формулами экселя. Т.е. те моменты, которые проще реализовать при помощи формул - делать их формулами, а которые проще программой (как в данном случае) - сделать программой. Оптимальный вариант, я думаю, будет таким:

Код: Выделить всё

Function CutWords(WordsSource As Range, SymbNum As Long) As String
    Dim iText As String
    Dim iArray() As String
    Dim i&
    Dim NewStrng As String
    iText = WordsSource
    iArray = Split(iText, " ")
    For i = 0 To UBound(iArray)
        NewStrng = NewStrng & Left(iArray(i), SymbNum) & " "
    Next i
    CutWords = NewStrng
End Function

т.е. создать пользовательскую функцию.... Тогда формулу "=CutWords(C9;4)" можно будет распространять в любом направлении, чего не сделаешь в случае реализацией формулами.... Получилось предельно просто, наглядно и рационально.... за что я и люблю эксель!

Еще вариант:

Код: Выделить всё

Function SplitText(strText As String, strSeparator As String) As Variant
    SplitText = Split(strText, strSeparator)
End Function

Function JoinArray(arrSource As Variant, strSeparator As String) As String
    JoinArray = Join(arrSource, strSeparator)
End Function

{=JoinArray(ЛЕВСИМВ(SplitText(A905;" ");4);" ")}

[anvg]

@Nik
А что вернёт твоя функция, если пробелов между словами 2?
Если после слова стоят: двоеточие, точка с запятой, запятая, точка и т. д.; то есть обычное предложение? Будет ли выполняться исходное условие?
Да и

Только я забыл сказать, что подразумевалось реализовать это именно формулами экселя..... Smile Можете сделать ещё одну попытку

твоя реализация - есть использование формул Excel - каких?[/quote]

[KL]

Пока @Nik пишет свой ответ...

А что вернёт твоя функция, если пробелов между словами 2?

по-моему это на результат не повлияет, но и лишние пробелы удалены не будут. Можно в начале функции удалить лишние пробелы, что-нить в этом духе (функции TRIM, LTRIM, RTRIM не подойдут):

iText =Application.Trim(WordsSource(1))

Если после слова стоят: двоеточие, точка с запятой, запятая, точка и т. д.; то есть обычное предложение? Будет ли выполняться исходное условие?

Ну тогда задача будет решаться с пом. Regular Expressions

твоя реализация - есть использование формул Excel - каких?

Думаю, что здесь как раз противоречия нет - решение действительно использует формулы. Другое дело это то, что некоторые могли интерпретировать задачу как "...реализовать это именно..." стандартными операторами и функциями рабочего листа "...экселя..."

[@Nik]

Ну просто нечего больше добавить! Исчерпывающий ответ !
Да, вы действительно демонстрируете высший пилотаж в своих ответах...

[@Nik]

Мой товарищ подкинул очередную задачку:
Функция ПОИСКПОЗ(...;...;0) - ищет смещение до первого совпадения, а задача стоит в том, чтобы найти смещение до последнего совпадения, при чём искать подстроку, а не конкретное числовое значение или целую строку

Те, кто заинтересовался, пока ниже не смотрят, там будет мой вариант решения.....

{=МАКС(ЕСЛИ(ЕОШИБКА(ПОИСК("*" & G2&"*";D3:D11));0;СТРОКА(D3:D11)-СТРОКА(D2)))}
где D3:D11 - просматриваемый массив;
D2 - ячейка, на строку выше начала просматриваемого диапазона;
в G2 - искомая подстрока

[KL]

Мой товарищ подкинул очередную задачку:
Функция ПОИСКПОЗ(...;...;0) - ищет смещение до первого совпадения, а задача стоит в том, чтобы найти смещение до последнего совпадения, при чём искать подстроку, а не конкретное числовое значение или целую строку

Те, кто заинтересовался, пока ниже не смотрят, там будет мой вариант решения.....

{=МАКС(ЕСЛИ(ЕОШИБКА(ПОИСК("*" & G2&"*";D3:D11));0;СТРОКА(D3:D11)-СТРОКА(D2)))}
где D3:D11 - просматриваемый массив;
D2 - ячейка, на строку выше начала просматриваемого диапазона;
в G2 - искомая подстрока

Спецсимволы не нужны:

{=МАКС(ЕСЛИ(ЕЧИСЛО(ПОИСК(G2;D3:D11));СТРОКА(D3:D11)-СТРОКА(D3)+1))}

Можно еще так выпендриться (короче и быстрее):

{=ПОИСКПОЗ(9E+307;ПОИСК(G2;A3:A11))}

[@Nik]

Да, действительно прикольно.... Аж улыбнулся от того, насколько всё совершенное - просто!

[@Nik]

Вот ещё одна задачка для желающих размять свои извилины.....
Есть столбец с перечнем фирм, рядом - перечень товаров. Разные фирмы могут иметь одинаковые товары, однако надо сделать, чтобы совпадений не было... Т.е. при совпадении высветить сообщение о невернов выборе и указать номер строки.
Например:
Фирма1 Товар1
Фирма2 Товар3
Фирма1 Товар2
Фирма2 Товар3
Фирма2 Товар4

Неверный ввод: совпадение в строке 4 !!!

В принцыпе, особых сложностей тут нет, просто интересно, а часто и довольно полезно увидеть другие возможные пути реализации...

Вот мой вариант:
Напр, в яч. А1 разместить:
{=ПОИСКПОЗ(1;--(ПОИСКПОЗ(C3:C11&D3:D11;C3:C11&D3:D11;0)<>СТРОКА(C3:C11)-СТРОКА(C2));0)}
В яч. В1:
=ЕСЛИ(ЕОШИБКА(A1);"";"НЕВЕРНЫЙ ВВОД: повторение в строке "&A1&" !!!")

Единственное что - в моём примере не допускается больше чем 1 пустая строка (2 строки - уже повторение). Чтобы всё работало правильно - диапазоны надо использовать динамические

[Aent2007]

Решение первой задачи (об обрезании слов)
Пусть искомая строка находится в ячейке $A$1.
Через Вставка->Имя->Присвоить определяем имя Result как

Код: Выделить всё

=ВЫЧИСЛИТЬ("ПСТР(""" & ПОДСТАВИТЬ(A1;" ";""";1;4)  & "" "" & ПСТР(""") & """;1;4)")

В ячейку $B$1 пишем формулу =Result
Ву-аля.
Заметьте, что функция ВЫЧИСЛИТЬ (из макроязыка Excel4) не может быть непосредственно применена на листе.

[KL]

Решение первой задачи (об обрезании слов)
Пусть искомая строка находится в ячейке $A$1.
Через Вставка->Имя->Присвоить определяем имя Result как

Код: Выделить всё

=ВЫЧИСЛИТЬ("ПСТР(""" & ПОДСТАВИТЬ(A1;" ";""";1;4)  & "" "" & ПСТР(""") & """;1;4)")

В ячейку $B$1 пишем формулу =Result
Ву-аля.
Заметьте, что функция ВЫЧИСЛИТЬ (из макроязыка Excel4) не может быть непосредственно применена на листе.

Думаю, что Aent2007 забыл упомянуть, что у этого способа есть одна серьезная проблема: функция ВЫЧИСЛИТЬ позволяет использовать в качестве формулы текстовую строку длиной до 255 знаков. Т.е. учитывая, что на длину формулы будут влиять нижеперечисленные факторы, она становится практически непредсказуемой:

- длина слов (все знаки кроме пробелов)
- кол-во пробелов (кол-во слов)
- длина функций в данной локали (напр. MID - ПСТР)
- вообще длина самой конкатенируемой формулы
- и др.

Внимание! В моем примере стоит ручной перерасчет формул.

[KL]

Напр, в яч. А1 разместить:
{=ПОИСКПОЗ(1;--(ПОИСКПОЗ(C3:C11&D3:D11;C3:C11&D3:D11;0)<>СТРОКА(C3:C11)-СТРОКА(C2));0)}

Нет необходимости конвертировать логические значения в числовые.
{=ПОИСКПОЗ(ИСТИНА;ПОИСКПОЗ(C3:C11&D3:D11;C3:C11&D3:D11;0)<>СТРОКА(C3:C11)-СТРОКА(C2);0)}

В этих случаях можно еще использовать комбинацию из Усл.Форматирования и Проверки.

[@Nik]

Нет необходимости конвертировать логические значения в числовые.

Да, я знаю, просто мне синтаксис "ПОИСКПОЗ(1;--(..." больше понравился, чем "ПОИСКПОЗ(ИСТИНА; ....".

В этих случаях можно еще использовать комбинацию из Усл.Форматирования и Проверки.

А можете привести пример, что вы имели в виду... оч. интересно...

[@Nik]

Уважаемые Aent2007 или KL, поясните, плиз, тёмным, вроде меня что это вы тут такое насочиняли и как оно работает ? И что это за функция ВЫЧИСЛИТЬ() такая ? Как её найти в справке ?

[KL]

Да, я знаю, просто мне синтаксис "ПОИСКПОЗ(1;--(..." больше понравился, чем "ПОИСКПОЗ(ИСТИНА; ....".

Представь себе что у тебя диапазон в 5.000 строк - конвертация в числа добавит всего навсего 10.000 операций (хотя и побыстрее 5.000 операций с 0+...)

А можете привести пример, что вы имели в виду... оч. интересно...

см. приложение

[KL]

Уважаемые Aent2007 или KL, поясните, плиз, тёмным, вроде меня что это вы тут такое насочиняли и как оно работает ? И что это за функция ВЫЧИСЛИТЬ() такая ? Как её найти в справке ?

Да мы тут ничего не сочиняли. Решение "широко известное в узких кругах" любителей Excel. На нем же основано довольно часто используемая функция (автор, кажется, Том Огилви) для эмуляции отсутствующей в Excel97 функции VBA Split():

Код: Выделить всё

#If Not VBA6 Then
Function Split(sStr As Variant, sdelim As String) As Variant
    Split = Evaluate("{""" & Application.Substitute(sStr, sdelim, """,""") & """}")
End Function
#End If

Как уже сказал Aent2007, речь идет о макрофункциях XLM, предшественника VBA в Excel, использовавшегося в версиях 4 и 5. Кстати, небезызвестное приложение Solver, кажется, основано на макрофункциях.

Так вот, некоторые (меньшенство) из этих макрофункций способны работать не только из модулей VBA или их предшественников "MacroSheets", но и из именованных формул (меню Вставка-Имя-Присвоить...).

Подробнее...

1) Справка по макрофункциям

http://tinyurl.com/2pbfep

2) Примеры использования в Excel2000+

http://www.jkp-ads.com/Articles/ExcelNames08.htm
http://www.jkp-ads.com/Articles/ExcelNames09.htm

Для работы с примерами может понадобиться переводчик формул:

http://members.chello.nl/jvolk/keepitcool/download.html (TranslateIt)

Внимание! В версиях до 2002, при копировании ячейки содержащей имя с макрофункциями с одного листа на другой произойдет аварийное закрытие Excel с потерей несохраненных изменений.

[@Nik]

И всё-таки, помогите разобраться.... сам не могу:

=ВЫЧИСЛИТЬ("ПСТР(""" & ПОДСТАВИТЬ(A1;" ";""";1;4) & "" "" & ПСТР(""") & """;1;4)")

"ВЫЧИСЛИТЬ" - понятно, "ПСТР(""" & - тоже
а вот ПОДСТАВИТЬ(A1;" ";""";1;4) и дальше - жуть... Как это понять ?

[KL]

И всё-таки, помогите разобраться.... сам не могу:

=ВЫЧИСЛИТЬ("ПСТР(""" & ПОДСТАВИТЬ(A1;" ";""";1;4) & "" "" & ПСТР(""") & """;1;4)")

"ВЫЧИСЛИТЬ" - понятно, "ПСТР(""" & - тоже
а вот ПОДСТАВИТЬ(A1;" ";""";1;4) и дальше - жуть... Как это понять ?

Ну давай попробуем

Суть:
все, что находится внутри ВЫЧИСЛИТЬ(...) направлено на построение искомой формулы ввиде текста(!) методом конкатенации(!) и замены(!). Приблизительно также ты бы строил формулу для ввода в ячейку из-под VBA(!)

Дано:
текстовая строка: "Мама мыла раму"

Задача:
построить формулу след. типа ввиде текстовой строки:
ПСТР("мама";1;2)&" "&ПСТР("мыла";1;2)&" "&ПСТР("раму";1;2)
для последующего вычисления с пом. макрофункции ВЫЧИСЛИТЬ()
и получения в результате: "ма мы ра"

Решение:

Шаг 1:
Создаем начало для первого из трех фрагментов типа "ПСТР("...";1;2)", т.е.: "ПСТР("""
Как известно, для получения кавычек внутри кавычек требуется их повтор.

Шаг 2:
Создаем конец для первого, начало и конец для среднего, начало для последнего из фрагментов путем замены [ПОДСТАВИТЬ()] в изначальном тексте пробелов (" ") на следующий текст:
""";1;2) & "" "" & ПСТР("""
Получаем: "мама"";1;2)&"" ""&ПСТР(""мыла"";1;2)&"" ""&ПСТР(""раму"

Шаг 3:
Создаем конец для последнего из фрагментов: """;1;2)"

В результате получаем:
"ПСТР("""&"мама"";1;2)&"" ""&ПСТР(""мыла"";1;2)&"" ""&ПСТР(""раму"&""";1;2)"
что на языке формул означает:
ПСТР("мама";1;2)&" "&ПСТР("мыла";1;2)&" "&ПСТР("раму";1;2)

Шаг 4:
осталось только произвести вычисление с пом. ВЫЧИСЛИТЬ(), т.е.
=ВЫЧИСЛИТЬ("ПСТР("""&"мама"";1;2)&"" ""&ПСТР(""мыла"";1;2)&"" ""&ПСТР(""раму"&""";1;2)")

Ву-аля!

[@Nik]

Блин.... теперь аж стыдно столо, что всё так просто, а я это сам не смог разобрать.....
Большое спасибо за столь подробное объяснение....

[@Nik]

Ещё такой вопрос: А можно ли как-то вытащить массив, лежащий в другой ячейке ?
Например:
в А1 лежит: ={1;2;6;7;9}
в А2 формула, которой нужны числа из А1

[KL]

Ещё такой вопрос: А можно ли как-то вытащить массив, лежащий в другой ячейке ?
Например:
в А1 лежит: ={1;2;6;7;9}
в А2 формула, которой нужны числа из А1

Насколько я понимаю, только двумя способами

1) Макрофункции

- меню: Вставка->Имя->Присвоить...
- имя: Массив
- формула: =ВЫЧИСЛИТЬ(ПОЛУЧИТЬ.ЯЧЕЙКУ(6+ТДАТА()*0;$A$1))

в ячейке [A2] используем имя Массив по нашему усмотрению.

2) VBA

Принцып тот же