Страницы: 1 2 След.
RSS
Возможно ли создать пользовательскую функцию на основе ЕСЛИМН с использованием подстановочного знака *
 
Возможно ли создать пользовательскую функцию на основе функции ЕСЛИМН, чтобы функция могла использовать подстановочные знаки * и ?.
У меня в ячейке логической проверки значение больше 255 символов, а если бы было можно использовать знак * то я бы выбрал ключевое слово и функция бы всё просчитала.  
 
а если завернете?
Код
=ПСТР(A1;1;254)&"*"
 
Объясните пожалуйста чуть поподробней.  
 
Цитата
zvolkz: Возможно ли создать пользовательскую функцию на основе функции ЕСЛИМН, чтобы функция могла использовать подстановочные знаки * и ?
да
Цитата
zvolkz: У меня в ячейке логической проверки значение больше 255 символов
можно написать функцию для ТОЧНОГО поиска по ключам любой длины — и не придётся придумывать ничего

Но лучше не UDF, а по кнопке - гораздо быстрее будет
Изменено: Jack Famous - 01.12.2021 10:26:24
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Цитата
написал:
Но лучше не UDF, а по кнопке - гораздо быстрее будет
Будьте добры расшифруйте пожалуйста что это значит?
 
, имеется ввиду написание макроса выполняющего нужное Вам действие, Вам бы просто приложить файл-пример с исходными строками (5-10) строк достаточно, и рядом показать результат который вы хотите...
Изменено: Mershik - 01.12.2021 11:19:24
Не бойтесь совершенства. Вам его не достичь.
 
zvolkz,
Приходится догадываться что именно Вы пытаетесь реализовать при помощи своей функции...
И если я догадался правильно, то Вам может оказаться небезынтересна статья Поиск ключевых слов в тексте и другие статьи из раздела Приёмы
 
Макрофункция. Не старался и не проверял
Изменено: Jack Famous - 01.12.2021 11:37:40
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
#6 01.12.2021 11:14:02

Задача такая - на листе 1 в столбце G:G есть название подразделения состоящие из букв и цифр, иногда с кавычками, иногда это название больше чем 255 символов, названия дублируются, например всего 31 подразделение на 800 000 строк.
Так как формулы эксель имеют ограничения длины на 8192 знака, то с помощью формулы ЕСЛИМН название подразделения преобразовывается в краткий шифр в столбце U:U или другом если если в таблице больше столбцов с данными. На листе 2 с помощью формулы СЧЁТЕСЛИМН этот шифр считается с необходимыми условиями.  
Изменено: zvolkz - 01.12.2021 12:18:59
 
zvolkz, #8 изучили?
Можно ещё в доп. столбец написать формулу "вычленения" ключевых слов типа =ПОИСК() и тянуть по этим выцепленным ключам…
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 #10 01.12.2021 12:17:50
Jack Famous, изучил, но не понял
Код
valFind = значение, которое ищем или ссылка на ячейку
Нужное мне значение вводить через кавычки как формуле? кириллицей? где ссылки на столбцы их № или буквенные обозначения в самом коде или курсором прямо в таблице выделить оба столбца? Могли бы Вы, если не сложно, в вашем коде сделать пример - вроде такого в столбце G:G ищем значение "Яблоко", если есть совпадение то в столбце U:U машина пишет "фрукт". И как сделать если нужных значений в столбце G:G 10, 20 или даже 100? и машине нужно прописать "Фрукт, овощ, ягода и т.д?
Вероятно я смешно это написал, но у меня знаний не хватает к сожалению.  

 
Изменено: zvolkz - 01.12.2021 12:29:32
 
Цитата
zvolkz: Могли бы Вы, если не сложно, в вашем коде сделать пример
то есть вы пример не сделали, а я буду
Ахаха - покедова  :D
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
#12

Спасибо, что уделили время.  
 
Кто читает эту тему и кому интересен итог обсуждения.
Наиболее понятным и простым в использовании оказался вариант от Тимофеев из сообщения #2.
Значение больше 255 знаков обрезается и обрезок в вставляется в функцию ЕСЛИМН, в моём случае обрезалась та часть наименования без которой возможно отграничить одно значение от другого. Если у кого то значение 255 символов и более, где каждый символ важен то этот способ вам вряд ли поможет.  
 
Вся проблема в том, что нет небольшого файла-примера. Вот такие данные есть, вот такой результат нужен.
Изменено: New - 01.12.2021 17:48:28
 
#15
Вот файл пример, файл тренировочный, в столбцах G:G , H:H, J:J данных на много больше, вплоть до 255 символов в ячейке, самих подразделений до 60 штук может быть, а сама таблица достигает размере 800 000 - 900 000 строк . Вот что нужно сделать
Задача - в столбце G:G содержится наименование подразделения, с помощью функции ЕСЛИМН каждому подразделению присваивается кодировка в столбце Q:Q,
в столбце H:H содержится вид сообщения, с помощью функции ЕСЛИМН каждому виду сообщения присваивается свой шифр (код) в столбце R:R,
в столбце J:J содержится решение по сообщению,  с помощью функции ЕСЛИМН каждому решению по сообщению присваивается свой шифр (код) в столбце S:S.
У формулы ЕСЛИМН есть минус она не принимает подстановочные символы, конкретно *, но главная проблема появились - наименования подразделений название которых больше чем 255 символов, и его не без обрезания не возможно прописать в формулу ЕСЛИМН.
Кроме того сама формула ограничена длиной в 8192 символа.
Возможно ли написать такой код который бы работал по аналогии с функцией ЕСЛИМН т.е. при открытии вкладки VBA в меню разработчик в самом коде по прописывать наименование подразделений/сообщений/решений и в каких столбцах эти данные находятся, а потом прописывать коды на которые эти название должны быть изменены, и так же указать столбцы куда должна быть выведена информация.
В наименованиях и решениях используются буквы и цифры, кавычки. Оригинальные данные в столбцах G:G, H:H,  J:J должны оставаться не изменными.

Сейчас подумал может возможно часть данных вывести на другой лист - например наименования из столбца  G:G , их их код из столбца Q:Q.
Изменено: zvolkz - 01.12.2021 17:56:37
 
а так:
Код
=ЕСЛИМН(ПСТР(A1;1;255)&ПСТР(A1;256;510)=ПСТР(A2;1;255)&ПСТР(A2;256;510);1)
=МОДА(--(ПСТР(A1;ПОСЛЕД(ДЛСТР(A1);1;1);1)=ПСТР(A2;ПОСЛЕД(ДЛСТР(A2);1;1);1)))
=МАКС(--(ПСТР(A1;ПОСЛЕД(ДЛСТР(A1);1;1);1)=ПСТР(A2;ПОСЛЕД(ДЛСТР(A2);1;1);1)))
=СРЗНАЧ(--(ПСТР(A1;ПОСЛЕД(ДЛСТР(A1);1;1);1)=ПСТР(A2;ПОСЛЕД(ДЛСТР(A2);1;1);1)))
=НАИБОЛЬШИЙ(--(ПСТР(A1;ПОСЛЕД(ДЛСТР(A1);1;1);1)=ПСТР(A2;ПОСЛЕД(ДЛСТР(A2);1;1);1));1)
.
Изменено: Тимофеев - 01.12.2021 21:06:20
 
давайте сделаем вашу кодировку в столбцах Q, R, S макросом...
У вас где-нибудь есть справочники? Что "Московский Государственный Униерситет" - это цифра 1? Или же не важно какая цифра будет, главное, чтобы у всех "Московский Государственный Униерситет" была уникальная цифра?
P.S. У вас в файле в слов "Униерситет" пропущена буквы "в"
См. файл.
Изменено: New - 01.12.2021 21:22:05
 
Цитата
написал:
У вас где-нибудь есть справочники?
Вы правы ошибся в названии, глаз замылился к вечеру.
Справочники есть, но есть проблема - периодически наименование подразделения изменяется, также меняются виды сообщений, регулярно добавляют новые виды решений по сообщениям. Кроме того иногда подразделения объединяют, разъединяют, вообще расформировывают. Из-за этого справочник является "плавающим" т.е, нужна возможность вручную менять переменные как в формуле, если это возможно. Так 01.12.2021 24 подразделения были переименованы.
Код подразделения важен к примеру "Московский Государственный Униерситет" всегда должен быть под кодом "1".
Изменено: zvolkz - 02.12.2021 07:03:00
 
Так это не проблема. Можно создать лист "Справочники", и там создать Справочники и макрос будет брать коды оттуда
 
#20 02.12.2021 07:54:32

Вот так примерно должен выглядеть лист справочника? Или на каждую кодировку нужен свой лист справочника?  
 
Добавьте дату , потом спасибо скажете
По вопросам из тем форума, личку не читаю.
 
zvolkz, см. файл
Изменено: New - 02.12.2021 11:56:40
 
#22

Спасибо за дополнение.
Дата поможет отслеживать изменения? или упростит работу макроса?
 
да
Цитата
zvolkz написал:
Дата поможет отслеживать изменения
а вот работу макроса или формул усложнит.
По вопросам из тем форума, личку не читаю.
 
#23

Большое спасибо за помощь, но при попытке применить код в реальном примере машина выдала ошибку run time error 9 subscript out of range и выделила жёлтым Set shtReference = Worksheets("Справочник") в коде макроса.
Лист "справочник" нужно назвать с большой буквы? может быть это должен быть именно лист2 который переименовали в справочник?
 
zvolkz, проверьте написание листа "Справочник" у себя в файле. Я заметил, что вы часто делаете опечатки в словах. Написание слова "Справочник" с большой или маленькой буквы - без разницы. А вот английский буквы и пробелы нельзя. Может у вас первая буква С в слове "Справочник"  английская буква. Может вы написали "Справочник " - пробел в конце. Вы можете из моего кода скопировать слово "Справочник" и переименовать ваш лист вставив это слово из моего кода (Ctrl+C, Ctrl+V)
БМВ, я думал ты там свой макрос скинул, а ты только столбец дата добавил )
Изменено: New - 02.12.2021 09:59:18
 
Цитата
New написал:
я думал ты там свой макрос скинул
я и макрос  - понятие несовместимое  :D
По вопросам из тем форума, личку не читаю.
 
#27

Перепроверил, опечаток нет.
После проверок жёлтым стал выделяться код If Not Dict.exists(arrReference(i, 1)) Then.
Это может быть как то связано с тем что диапазоны имеют разную длину? На листе справочник Подразделений больше чем видов сообщений и решений, а решений больше чем видов сообщений. Сам лист на который должны выводиться коды называется "1". В столбцах G, H, J могут быть пустые ячейки.
В самом коде я прописал иные столбцы куда выводить кодировки.

Дополнение - заменил пустые ячейки в столбцах словом "нет", в справочник записал что слово "нет" должно кодироваться как "0". Всё равно ошибка и жёлтым выделяется  
Код
If Not Dict.exists(arrReference(i, 1)) Then
 
Код
'КОД ПОДРАЗДЕЛЕНИЯ
        LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row
        arrData = .Range("G4:G" & LastRow)
        ReDim arrOut(1 To UBound(arrData), 1 To 1)
        'данные с листа Справочник
        With shtReference
            If .FilterMode Then .ShowAllData
            LastRowReferenceSht = .Cells(.Rows.Count, "A").End(xlUp).Row
            arrReference = .Range("A2:B" & LastRowReferenceSht).Value
        End With
        Set Dict = CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(arrData)
            If Not Dict.exists(arrReference(i, 1)) Then
                Dict.Item(arrReference(i, 1)) = arrReference(i, 2)
            Else
                Dict.Item(arrReference(i, 1)) = arrReference(i, 2)
            End If
        Next i
        'итоговый массив
        For i = 1 To UBound(arrData)
            If Dict.exists(arrData(i, 1)) Then
                arrOut(i, 1) = Dict(arrData(i, 1))
            End If
        Next i
        .Range("Y4").Resize(UBound(arrData, 1), 1).Value = arrOut
    
        'КОД СООБЩЕНИЯ
        LastRow = .Cells(.Rows.Count, "H").End(xlUp).Row
        arrData = .Range("H4:H" & LastRow)
        ReDim arrOut(1 To UBound(arrData), 1 To 1)
        'данные с листа Справочник
        With shtReference
            If .FilterMode Then .ShowAllData
            LastRowReferenceSht = .Cells(.Rows.Count, "D").End(xlUp).Row
            arrReference = .Range("D2:E" & LastRowReferenceSht).Value
        End With
        Set Dict = CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(arrData)
            If Not Dict.exists(arrReference(i, 1)) Then
                Dict.Item(arrReference(i, 1)) = arrReference(i, 2)
            Else
                Dict.Item(arrReference(i, 1)) = arrReference(i, 2)
            End If
        Next i
        'итоговый массив
        For i = 1 To UBound(arrData)
            If Dict.exists(arrData(i, 1)) Then
                arrOut(i, 1) = Dict(arrData(i, 1))
            End If
        Next i
        .Range("X4").Resize(UBound(arrData, 1), 1).Value = arrOut
    
        'КОД РЕШЕНИЯ
        LastRow = .Cells(.Rows.Count, "J").End(xlUp).Row
        arrData = .Range("J4:J" & LastRow)
        ReDim arrOut(1 To UBound(arrData), 1 To 1)
        'данные с листа Справочник
        With shtReference
            If .FilterMode Then .ShowAllData
            LastRowReferenceSht = .Cells(.Rows.Count, "G").End(xlUp).Row
            arrReference = .Range("G2:H" & LastRowReferenceSht).Value
        End With
        Set Dict = CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(arrData)
            If Not Dict.exists(arrReference(i, 1)) Then
                Dict.Item(arrReference(i, 1)) = arrReference(i, 2)
            Else
                Dict.Item(arrReference(i, 1)) = arrReference(i, 2)
            End If
        Next i
        'итоговый массив
        For i = 1 To UBound(arrData)
            If Dict.exists(arrData(i, 1)) Then
                arrOut(i, 1) = Dict(arrData(i, 1))
            End If
        Next i
        .Range("Z4").Resize(UBound(arrData, 1), 1).Value = arrOut
    End With
Изменено: zvolkz - 02.12.2021 10:20:43
 
Цитата
zvolkz написал:
Это может быть как то связано с тем что диапазоны имеют разную длину?
нет, это не влияет
Цитата
zvolkz написал:
В столбцах G, H, J могут быть пустые ячейки.
это нормально
Цитата
zvolkz написал:
Сам лист на который должны выводиться коды называется "1".
это без разницы, главное запускать макрос на нужном листе с данными
Цитата
zvolkz написал:
В самом коде я прописал иные столбцы куда выводить кодировки.
так вы бы сразу и вашем примере данные разместили в правильных столбцах, чтобы вам потом не менять столбцы в коде макроса.
Цитата
zvolkz написал:
заменил пустые ячейки в столбцах словом "нет", в справочник записал что слово "нет"
это не обязательно
да, нашёл косяк в коде. Исправил. Потестируйте приложенный файл.
Изменено: New - 02.12.2021 11:53:10
Страницы: 1 2 След.
Наверх