Возможно ли создать пользовательскую функцию на основе функции ЕСЛИМН, чтобы функция могла использовать подстановочные знаки * и ?. У меня в ячейке логической проверки значение больше 255 символов, а если бы было можно использовать знак * то я бы выбрал ключевое слово и функция бы всё просчитала.
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
, имеется ввиду написание макроса выполняющего нужное Вам действие, Вам бы просто приложить файл-пример с исходными строками (5-10) строк достаточно, и рядом показать результат который вы хотите...
zvolkz, Приходится догадываться что именно Вы пытаетесь реализовать при помощи своей функции... И если я догадался правильно, то Вам может оказаться небезынтересна статья Поиск ключевых слов в тексте и другие статьи из раздела Приёмы
valFind = значение, которое ищем или ссылка на ячейку colFindAs Range = диапазон (1 столбец), в котором нужно найти значение colGetAs Range = диапазон (1 столбец), из которого нужно взять значение, если нашли valFind в colFind Столбцы целиком не выделять
Код
'====================================================================================================
Function MatchSlowpoke(valFind, colFind As Range, colGet As Range) As Variant
Dim aFind, r&, p&
aFind = colFind.Value2
For r = 1 To UBound(colFind, 1)
If aFind(r, 1) = valFind Then p = r: Exit For
Next r
If p = 0 Then MatchSlowpoke = 0 / 0: Exit Function
MatchSlowpoke = colGet.Cells(p, 1).Value2
End Function
'====================================================================================================
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Задача такая - на листе 1 в столбце G:G есть название подразделения состоящие из букв и цифр, иногда с кавычками, иногда это название больше чем 255 символов, названия дублируются, например всего 31 подразделение на 800 000 строк. Так как формулы эксель имеют ограничения длины на 8192 знака, то с помощью формулы ЕСЛИМН название подразделения преобразовывается в краткий шифр в столбце U:U или другом если если в таблице больше столбцов с данными. На листе 2 с помощью формулы СЧЁТЕСЛИМН этот шифр считается с необходимыми условиями.
zvolkz, #8 изучили? Можно ещё в доп. столбец написать формулу "вычленения" ключевых слов типа =ПОИСК() и тянуть по этим выцепленным ключам…
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
#10 01.12.2021 12:17:50 Jack Famous, изучил, но не понял
Код
valFind = значение, которое ищем или ссылка на ячейку
Нужное мне значение вводить через кавычки как формуле? кириллицей? где ссылки на столбцы их № или буквенные обозначения в самом коде или курсором прямо в таблице выделить оба столбца? Могли бы Вы, если не сложно, в вашем коде сделать пример - вроде такого в столбце G:G ищем значение "Яблоко", если есть совпадение то в столбце U:U машина пишет "фрукт". И как сделать если нужных значений в столбце G:G 10, 20 или даже 100? и машине нужно прописать "Фрукт, овощ, ягода и т.д? Вероятно я смешно это написал, но у меня знаний не хватает к сожалению.
zvolkz: Могли бы Вы, если не сложно, в вашем коде сделать пример
то есть вы пример не сделали, а я буду Ахаха - покедова
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Кто читает эту тему и кому интересен итог обсуждения. Наиболее понятным и простым в использовании оказался вариант от Тимофеев из сообщения #2. Значение больше 255 знаков обрезается и обрезок в вставляется в функцию ЕСЛИМН, в моём случае обрезалась та часть наименования без которой возможно отграничить одно значение от другого. Если у кого то значение 255 символов и более, где каждый символ важен то этот способ вам вряд ли поможет.
#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.
давайте сделаем вашу кодировку в столбцах Q, R, S макросом... У вас где-нибудь есть справочники? Что "Московский Государственный Униерситет" - это цифра 1? Или же не важно какая цифра будет, главное, чтобы у всех "Московский Государственный Униерситет" была уникальная цифра? P.S. У вас в файле в слов "Униерситет" пропущена буквы "в" См. файл.
Вы правы ошибся в названии, глаз замылился к вечеру. Справочники есть, но есть проблема - периодически наименование подразделения изменяется, также меняются виды сообщений, регулярно добавляют новые виды решений по сообщениям. Кроме того иногда подразделения объединяют, разъединяют, вообще расформировывают. Из-за этого справочник является "плавающим" т.е, нужна возможность вручную менять переменные как в формуле, если это возможно. Так 01.12.2021 24 подразделения были переименованы. Код подразделения важен к примеру "Московский Государственный Униерситет" всегда должен быть под кодом "1".
Большое спасибо за помощь, но при попытке применить код в реальном примере машина выдала ошибку run time error 9 subscript out of range и выделила жёлтым Set shtReference = Worksheets("Справочник") в коде макроса. Лист "справочник" нужно назвать с большой буквы? может быть это должен быть именно лист2 который переименовали в справочник?
zvolkz, проверьте написание листа "Справочник" у себя в файле. Я заметил, что вы часто делаете опечатки в словах. Написание слова "Справочник" с большой или маленькой буквы - без разницы. А вот английский буквы и пробелы нельзя. Может у вас первая буква С в слове "Справочник" английская буква. Может вы написали "Справочник " - пробел в конце. Вы можете из моего кода скопировать слово "Справочник" и переименовать ваш лист вставив это слово из моего кода (Ctrl+C, Ctrl+V) БМВ, я думал ты там свой макрос скинул, а ты только столбец дата добавил )
Перепроверил, опечаток нет. После проверок жёлтым стал выделяться код 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