MatchIfs

[vm40946]

MatchIfs

זאת פונקציה שהייתה יכולה לעשות את החיים שלי קלים יותר.

מיכאל, אם אתה בקשר עם מיקרוסופט אולי תוכל להציע ?

 

[מיכאל אבידן]

הם שואלים כמה אתה מוכן לשלם על זה

. . .

אם כל אחד "ימציא" פונקציות שלדעתו הן עשויות להקל על חייו - אנא אנחנו באים.
&nbsp
אז..., במקום "לפנטז" על פונקציה חדשה, - מה דעתך להציג את בעייתך כדי שנוכל להציע פיתרון שמן הסתם לא יעלה לך אגורה.
&nbsp

 

[vm40946]

העלתי את הנושא בעבר

לינק: [URL]http://www.tapuz.co.il/forums/viewmsg/62/180626996/מחשבים/אופיס_(מיקרוסופט)[/URL]

בלינק המצורף הבעיה המוצגת, מציגה עוד סיבה לזה שהייתי מעוניין בפונקציה כזו( matchifs).

(אציין שהרצון לפונקציה הזו זה אך ורק עבור שימוש ב vba.)

כעקרון, חפרתי לא מעט ברשת, דיי הרבה זמן ואני חושב שאין לזה פתרון, אבל הרעיון הוא החזרת מספר שורה (יחסית של טווח או של הגיליון, זה לא חשוב) שמורכבת מעמודות רבות וקריטריונים רבים.
אז נכון, באקסל עצמו עם נוסחת מערך אני עושה את זה ב 4 שניות ונכון אם אפשר לעשות את זה עם נוסחת מערך באקסל אז אפשר גם לכתוב את זה ב vbe עם evaluate אבל אם יש 3-4 קריטריונים זה ממש סיוט לכתוב את הטקסט של זה בתוך ה evaluate.

אה, כן... ולהריץ לופ עם קריטריונים זה נחמד על כמות של עד 40k שורות, אבל לי יש 150k ככה הריץ לופ על כמות כזו של שורות זה לא פתרון (עבורי בכל אופן, מפאת זמן המתנה).

 

[מיכאל אבידן]

אז ככה . . .

אני לא יודע אם שמת לב שב'פתיל' הקודם כלל לא לקחתי חלק וזאת, בעיקר, כי לא חשבתי שהשאלה רצינית דיה.

ההוכחה לכך הגיעה היום - בהבדל התהומי שאז הצגת בעיית אחזור משורה אחת

וכעת אתה מדבר על (ציטוט): "...עמודות רבות וקריטריונים רבים..."

אני גם מרשה לעצמי להניח שהספקת לראות ולהבין שאני לא משתתף בדיונים תיאורטיים (כמו זה העכשווי) המציג "סיפור" על מאות סבבים של לולאה וכו'.

כשתבקש פיתרון למשהו קונקרטי ותפתח את כל הקלפים (כל המתבקש בהודעה הנעוצה בראש הדף) יהיה, אולי, על מה לדבר...

 

[vm40946]

בבקשה כל הקלפים

ומספר תגובות:
1. ״עמודות רבות וקריטריונים רבים״ - עדיין יכולים להחזיר שורה אחת, כזו הייתה השאלה הקודמת - קריטריונים רבים על עמודות רבות שמחזירים שורה אחת.
2. מה בדיוק לא היה רציני בשאלה הקודמת ? הצגתי שם באג מוכח בטבלאות.

* בקובץ המצורף יש ״רק״ 5k שורות. בקובץ המקורי יש פי 30 מזה.
* השאלה מוצגת בתוך המודול ב vbe
* אקסל 2013
* וינדוס 7

 

[ziv98]

באג מוכח (!) זה כנראה עניין יחסי

הסברתי לך בשרשור שצירפת, איך אקסל מתנהג כשהוא צריך "לנחש" למה התכוונת. אם היית מפורש בפונקציה - לא היה צורך בניחושים.

מה שהראית זה שלאקסל, כמו במתימטיקה, יש סדר פעולות. במקרה שלך שרשור לפני match - קודם מעריכים את הפרמטרים לפונקציה, ורק אז את הפונקציה כולה.

בנוסף קיבלת workaround פשוט, ממנו בחרת להתעלם...

 

[vm40946]

מקבל את הערתך

 

[u333]

אני הייתי עושה זאת כך

הכנס את המקרו הבא למודול.
השתמשתי - מטעמי עצלות - רק ב-3 תנאים.
את התנאים ניתן להכניס באמצעות InputBox - לבטח אתה מכיר. אני הכנסתי אותם ישירות לקוד.
ניסיתי על 76000 שורות - לוקח פחות משניה !

Sub FindFirstRow()
    ActiveSheet.Range("$A$1:$N$9503").AutoFilter Field:=4, Criteria1:="אפרת אבן"
    ActiveSheet.Range("$A$1:$N$9503").AutoFilter Field:=5, Criteria1:="מיכה פרץ"
    ActiveSheet.Range("$A$1:$N$9503").AutoFilter Field:=7, Criteria1:="סה""כ כולל מע""מ"
    ir = Cells(Rows.Count, 1).End(xlUp).End(xlUp).Row
    Cells(ir, 1).Select
    Application.SendKeys ("{down}")
    DoEvents
    MsgBox "FirstRow No. is  " & ActiveCell.Row
    ActiveSheet.ShowAllData
End Sub

 

[מיכאל אבידן]

בשלב זה, בלי להיכנס לעובי הצעתך הייתי אומר רק . . .

שכל ה"ממבו שממבו" הזה מיותר אלא אם תטרח לציין - באותיות קידוש לבנה - שאת המקרו הזה יש צורך להריץ בעזרת ALT+F8 (או לחצן ייעודי) שכן, כל דרך אחרת תחזיר 1 בפקודה: ActiveCell.Row

 

[vm40946]

תודה U333 אבל

האמת שבזמנו חשבתי על הכיוון הזה אבל זה קצת מסורבל, ותופס את הפילטרים שלפעמים בשימוש וכו'.

ממתין לראות איזה שפן יש למיכאל בכובע.

 

[מיכאל אבידן]

עכשיו, כשכל הג'וקרים הוצאו מהחפיסה . . .

נשאר לי רק להבין - האם אתה מחפש דרך קלה ופשוטה לבצע זאת בגיליון 'אקסל' (נוסחה) או ב- VBA

היה וב- VBA אפשר, בקלות, להציע פיתרון גם ללא לולאה.

נסה להיות עקבי וברור בתשובתך ואל תגביל אותי/אותנו (למעט השאיפה לבצע את המטלה ללא לולאה).

 

[vm40946]

VBA

 

[מיכאל אבידן]

אם כך, לא נותר לך אלא להתמקד בעיקרון המוצע להלן . . .

תשומת לבך לעובדה שאני "עצלן" ואין לי זמן, מיותר, "להתעסק" עם גרשיים וכו' ועל כן שיניתי (לנוחיותי) את הכיתוב: סה"כ כולל מע"מ ל: סה~כ כולל מע~מ.

אם הגרשיים בראש מעייניך אתה מוזמן ל"שחק איתם" וגם להחיל את הקריטריונים למשתנים (רק אם זה חשוב לך).

הקוד הוא:

Sub Micky()
       MsgBox Evaluate("Match(1, (D1:D9503=""אפרת אבן"")*(E1:E9503=""מיכה פרץ"")*(G1:G9503 = ""סה~כ כולל מע~מ""), 0)")
End Sub

 

[vm40946]

אין חדש תחת השמש

טוב, כמו שכבר כתבתי, עבורי זה ממש סיוט לכתוב נוסחאות בתוך Evaluate, במיוחד עם הרבה קריטריונים ובמיוחד עם גרשיים (שכן שזה חשוב, גם לשאר המשתמשים בקובץ).

בקיצר מיכאל, תודיע למיקרוסופט - MATCHIFS

 

[מיכאל אבידן]

אין הרבה חדש, כש י-ו-ד-ע-י-ם להשתמש באלטרנטיבות . . .

והנה אתה שוב "מתזז" שכן הסיומת IFS מכוונת לפונקצית גיליון כמו: SUMIFS, COUNTIFS וכו'.

הרי גם בפונקציות ה- IFS, אתה אמור להקליד את הקריטריונים או להפנות לתאים בהם הוכנסו ובכך לא חסכת מעצמך כלום.

כשמדובר ב- VBA אתה רשאי להשתמש בכל הכלים העומדים לרשותך ומה שהצגתי הוא (נכון לעכשיו) הקצר והברור מכל קודמיו.

כבר כתבתי (אתה כנראה מתמקד רק במה שנוח לך) שאם הגרשיים חשובים לך - התאם את הקוד, שהצגתי, לדרישותיך.

ביום שני אתקשר למטה MS, ברדמונד, כדי לבקשם לעזוב הכל כדי להוסיף את הפונקציה שכל כך "חסרה" לך

*** בהצלחה ***

 

[vm40946]

לא יודע על מה אתה מדבר

כי אני על בסיס קבוע כותב (לדוגמה): application.countifs ואני נהנה משלל הפונקציות לא בתוך האקסל עצמו.
ואני מניח שאתה יודע את ההבדלים בין לכתוב פונקציה ב vba תחת Evaluate ובין לכתוב פונקציה באמצעות application, הראשון סיוט והשני פינוק.

והיופי הוא שאתה עוד מפנה אותי להתעסק עם הגרשיים, שזה סיבת ה״סיוט״, שבגללם אני לא רוצה לעשות שימוש בכלל בטכניקת Evaluate ואם אתה חושב שתוריד את הגרשיים במילים מע״מ וסה״כ אתה לא תתחמק מזה שחייבים להתעסק עם הגרשיים כאשר רוצים להחיל תנאי של גדול מתאריך x.

בקיצור תחזור בך שצריך matchifs או לחלופין הרבה יותר טוב זה שמיקרוסופט ימצאו דרך נוחה יותר לכתוב נוסחאות מערך בתוך ה vbe מאשר להמיר למחרוזת טקסט ולהמיר ב Evaluate

 

[מיכאל אבידן]

חד משמעית, לדעתי, לא

צריך ובזאת הסתיים הדיון מבחינתי.

 

[iyyi]

נצנוץ קטן תחת השמש (הקלה בסיוט של כתיבת ה-Evaluate)

בקובץ המצורף הוספתי 3 עמודות לכתיבת התחומים, הבדיקה, והתוצאה הנבדקת.
בנוסף לכך הוספתי UDF בשם MatchIfs המקבלת 3 פרמטרים בהתאם לעמודות.
הפונקציה בשלב הראשון בונה את הנוסחה לאיתור השורה, ובשלב השני מפעילה עליה את פונקציית Evaluate להחזרת התוצאה.
ה-UDF מודגמת בתא P10. להלן קוד הפונקציה (UDF):

Function MatchIfs(r As Range, cond As Range, result As Range)
F = "MIN(IF(("
a = """"""
For i = 1 To r.Count
    If Application.WorksheetFunction.IsText(result(i)) Then _
        res = """" & Replace(result(i), """", a) & """" Else res = result(i)
    F = F & r(i) & cond(i) & res & ")*("
Next i
F = Left(F, Len(F) - 2) & ",ROW(" & r(1) & ")))"
MatchIfs = Evaluate(F)
End Function

אני מקווה שזה עוזר

 

[vm40946]

שאפו על היצירתיות

בהחלט כיוון מעניין שאולי אפתח אותו (כי אני לא נוהג להשתמש בתאי עזר מהאקסל)

בזמנו כתבתי פונקציה שעשתה לי צרות צרורות:

Public Function Match4(Rng1 As String, v1 As String, Rng2 As String, v2 As String, Rng3 As String, v3 As String, Rng4 As String, v4 As String) '4 התאמות של MATCH
Match4 = Evaluate("=MATCH(" & v1 & "&" & v2 & "&" & v3 & "&" & v4 & "," & Rng1 & "&" & Rng2 & "&" & Rng3 & "&" & Rng4 & ",0)")
End Function

האופרטורים שנשלחים אל הפונקציה הם באמצעות ADDRESS
לדוגמא:sheet(x).range("a1:a10000").address(true,true,,true)

כיוון המחשבה של הפונקציה הזו לא הוכיח את עצמו מאחר והיא עשתה כל מיני בעיות מכול מיני כיוונים אז נטשתי אותה.

 

[u333]

וזה מחזיר אותי לקישור שהצגתי אתמול ב- 15:32