excel-formula شرح - Excel:تطابق آخر حرف / سلسلة في سلسلة





functions pdf (9)


متأخر جدًا للحفل ، لكن الحل البسيط هو استخدام VBA لإنشاء وظيفة مخصصة.

إضافة الدالة إلى VBA في WorkBook أو ورقة عمل أو وحدة نمطية VBA

Function LastSegment(S, C)
    LastSegment = Right(S, Len(S) - InStrRev(S, C))
End Function

ثم صيغة الخلية

=lastsegment(B1,"/")

في خلية ثم سيتم ملء السلسلة المراد البحث عنها في الخلية B1 الخلية مع النص زائدة آخر "/" من الخلية B1. لا حدود للطول ، لا صيغ غير غامضة. الجانب السلبي الوحيد يمكنني أن أفكر في الحاجة إلى مصنف تمكين الماكرو.

يمكن استدعاء أي مستخدم VBA دالة بهذه الطريقة لإرجاع قيمة إلى صيغة الخلية ، بما في ذلك كمعلمة لوظيفة Excel مضمنة.

إذا كنت ستستخدم الدالة بشكل كبير ، فستحتاج إلى التحقق من الحالة عندما لا يكون الحرف في السلسلة ، ثم تكون السلسلة فارغة ، إلخ.

هل هناك طريقة فعالة لتحديد آخر تطابق في السلسلة / السلسلة في سلسلة باستخدام وظائف أساسية؟ بمعنى آخر حرف / سلسلة من السلسلة ، ولكن موضع آخر حدث حرف / سلسلة في سلسلة. Search find العمل من اليسار إلى اليمين حتى لا أفكر في كيفية التقديم دون خوارزمية متكررة طويلة. وهذا الحل يبدو الآن بالية.




لقد توصلنا للتو إلى هذا الحل ، فلا حاجة إلى VBA ؛

العثور على آخر من "_" في المثال الخاص بي ؛

=IFERROR(FIND(CHAR(1);SUBSTITUTE(A1;"_";CHAR(1);LEN(A1)-LEN(SUBSTITUTE(A1;"_";"")));0)

شرح الداخل.

SUBSTITUTE(A1;"_";"") => replace "_" by spaces
LEN( *above* ) => count the chars
LEN(A1)- *above*  => indicates amount of chars replaced (= occurrences of "_")
SUBSTITUTE(A1;"_";CHAR(1); *above* ) => replace the Nth occurence of "_" by CHAR(1) (Nth = amount of chars replaced = the last one)
FIND(CHAR(1); *above* ) => Find the CHAR(1), being the last (replaced) occurance of "_" in our case
IFERROR( *above* ;"0") => in case no chars were found, return "0"

آمل أن هذا كان مفيدا.




أنا متأخرة قليلاً للحزب ، لكن ربما هذا يمكن أن يساعد. كان للرابط في السؤال صيغة مشابهة ، لكن لي يستخدم عبارة IF () للتخلص من الأخطاء.

إذا لم تكن خائفاً من Ctrl + Shift + Enter ، فيمكنك القيام بذلك بشكل جيد باستخدام صيغة صفيف.

String (في الخلية A1): "one.two.three.four"

معادلة:

{=MAX(IF(MID(A1,ROW($1:$99),1)=".",ROW($1:$99)))}  use Ctrl+Shift+Enter

النتيجة: 14

أول،

ROW($1:$99)

إرجاع صفيف من الأعداد الصحيحة من 1 إلى 99: {1,2,3,4,...,98,99} .

التالى،

MID(A1,ROW($1:$99),1)

إرجاع صفيف من سلاسل ذات طول واحد موجودة في السلسلة المستهدفة ، ثم إرجاع سلاسل فارغة بعد الوصول إلى طول السلسلة المستهدفة: {"o","n","e",".",..."u","r","","",""...}

التالى،

IF(MID(I16,ROW($1:$99),1)=".",ROW($1:$99))

يقارن كل عنصر في الصفيف إلى السلسلة "." وإرجاع إما فهرس الحرف في السلسلة أو FALSE: {FALSE,FALSE,FALSE,4,FALSE,FALSE,FALSE,8,FALSE,FALSE,FALSE,FALSE,FALSE,14,FALSE,FALSE.....}

الاخير،

=MAX(IF(MID(I16,ROW($1:$99),1)=".",ROW($1:$99)))

إرجاع القيمة القصوى للمصفوفة: 14

مزايا هذه الصيغة هي أنها قصيرة وسهلة الفهم نسبياً ولا تتطلب أي أحرف فريدة.

العيوب هي الاستخدام المطلوب Ctrl + Shift + Enter والحد على طول السلسلة. يمكن التعامل مع هذا مع وجود شكل مبين أدناه ، ولكن هذا الاختلاف يستخدم دالة OFFSET () وهي دالة متطايرة (قراءة: بطيئة).

لست متأكدا ما هي سرعة هذه الصيغة مقابل الآخرين.

الاختلافات:

=MAX((MID(A1,ROW(OFFSET($A$1,,,LEN(A1))),1)=".")*ROW(OFFSET($A$1,,,LEN(A1)))) works the same way, but you don't have to worry about the length of the string

=SMALL(IF(MID(A1,ROW($1:$99),1)=".",ROW($1:$99)),2) determines the 2nd occurrence of the match

=LARGE(IF(MID(A1,ROW($1:$99),1)=".",ROW($1:$99)),2) determines the 2nd-to-last occurrence of the match

=MAX(IF(MID(I16,ROW($1:$99),2)=".t",ROW($1:$99))) matches a 2-character string **Make sure you change the last argument of the MID() function to the number of characters in the string you wish to match!



اقترح tigeravatar و Jean-François Corbett لاستخدام هذه الصيغة لتوليد سلسلة الحق في آخر ظهور للحرف "\"

=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",LEN(A1))),LEN(A1)))

إذا كان الحرف المستخدم كفاصل هو space ، "" ، فيجب تغيير الصيغة إلى:

=SUBSTITUTE(RIGHT(SUBSTITUTE(A1," ",REPT("{",LEN(A1))),LEN(A1))),"{","")

لا داعي للذكر ، يمكن استبدال الحرف "{" بأي حرف لا يحدث "بشكل طبيعي" في النص المطلوب معالجته.




ماذا عن إنشاء وظيفة مخصصة واستخدام ذلك في الصيغة الخاصة بك؟ يحتوي VBA على وظيفة InStrRev ، InStrRev ، تعمل تمامًا على ما تبحث عنه.

ضع هذا في وحدة نمطية جديدة:

Function RSearch(str As String, find As String)
    RSearch = InStrRev(str, find)
End Function

وستبدو وظيفتك هكذا (على افتراض أن السلسلة الأصلية في B1):

=LEFT(B1,RSearch(B1,"\"))



وبالنظر إلى جزء من تعليق @SSilk عن @SSilk هدفي النهائي هو الحصول على كل شيء على يمين هذا الحَدث الأخير ، حيث يتمثل نهج بديل مع صيغة بسيطة للغاية في نسخ عمود (على سبيل المثال A ) من السلاسل والنسخة ( يقول ColumnB) تطبيق البحث والاستبدال. على سبيل المثال أخذ المثال: Drive:\Folder\SubFolder\Filename.ext

هذا يعيد ما تبقى (هنا Filename.ext ) بعد أن تم اختيار المثال الأخير من أي حرف (هنا \ ) وهو في بعض الأحيان الهدف على أي حال ويسهل العثور على موضع آخر حرف من هذا القبيل مع صيغة قصيرة مثل:

=FIND(B1,A1)-1



يمكنك استخدام هذه الوظيفة التي أنشأتها للعثور على آخر نسخة من سلسلة داخل سلسلة.

تأكد من أن صيغة Excel المقبولة تعمل ، ولكن من الصعب جدًا قراءتها واستخدامها. في مرحلة ما ، عليك أن تشرع في قطع أصغر حتى تكون قابلة للصيانة. وظيفتي التالية قابلة للقراءة ، ولكن هذا غير ذي صلة لأنك تسميها في صيغة باستخدام المعلمات المسماة. هذا يجعل استخدامه بسيطا.

Public Function FindLastCharOccurence(fromText As String, searchChar As String) As Integer
Dim lastOccur As Integer
lastOccur = -1
Dim i As Integer
i = 0
For i = Len(fromText) To 1 Step -1
    If Mid(fromText, i, 1) = searchChar Then
        lastOccur = i
        Exit For
    End If
Next i

FindLastCharOccurence = lastOccur
End Function

أنا استخدمه على هذا النحو:

=RIGHT(A2, LEN(A2) - FindLastCharOccurence(A2, "\"))



طريقة بسيطة للقيام بذلك في VBA هي:

YourText = "c:\excel\text.txt"
xString = Mid(YourText, 2 + Len(YourText) - InStr(StrReverse(YourText), "\" ))