יום שלישי, 24 במאי 2022

Finding Unique values in VBA

 

VBA  - Finding Unique values

 The problem

Suppose you have a range of cells and you want to count the number of unique values in these cells.

For example, in this picture:

                                 Pic. No.1: A range of strings

 

Using the new UNIQUE function (Excel 365, available also in VBA) will not supply us with the correct answer, since the UNIQUE function isn’t case-sensitive. It won’t recognize the differences between the three cells.


The solution: Using the Dictionary 

 I’m not going to explain the Dictionary feature in VBA.

An excellent explanation (with examples) can be found here:
https://excelmacromastery.com/vba-dictionary/

              

Before running the code, we need to add the Microsoft Scripting Runtime library by using: Tools*References in VBA’s main menu

                     Pic. No. 2: Adding the Dictionary Library (stage 1)

 

  

In the Referenced list, select the Microsoft Scripting Runtime and press: OK

  

                  Pic. No. 3: Adding the Dictionary Library (stage 2)

 

Now you can run the code:

                     Pic. No. 4: Using the Dictionary in VBA

 

As can be seen, using this technique yields the correct answer.
There are 3 unique values in the referenced cells.


Liked the post?
please give it a "Like".









 

יום חמישי, 12 במאי 2022

לוח הכפל -הדור החדש



לוח הכפל -הדור הבא

 

עד לאחרונה, הפוסטים הפופולריים ביותר בבלוג שלי היו אלו שהיו מוקדשים להסבר על יצירת לוח הכפל ב-3 שיטות שונות:

 

https://meniporat.blogspot.com/2013/07/blog-post_8817.html
)
שמוש בכתובת מעורבת(

http://meniporat.blogspot.com/2013/07/blog-post_8935.html
 (שמוש בפונקציה:MMULT )

http://meniporat.blogspot.com/2013/07/blog-post_2151.html

(שמוש בפונקציה:PRODUCT)

אבל עכשיו, כשמיקרוסופט הכניסה את פונקציות המערך הדינמי (dynamic array functions) באקסל 365, הכל יותר קל ויותר פשוט.

הטכניקות היותר מיושנות הופכות למיותרות...
"כך חולפת תהילת העולם" (Sic transit gloria mundi)


התמונה הראשונה מציגה את לוח הכפל תוך שמוש בפונקציה האהובה עלי:  SEQUENCE, פונקצית מערך דינמי שהיא חלק מפונקציות המערך החדשות באקסל 365.

הארגומנטים בתאים: Q1 ו- Q2 מאפשרים לנו להגדיר לוח כפל דינמי. אפשר להרחיב אותו או לצמצם אותו, לפי צרכיך:

אפשר, למשל, להתחיל בטבלה של 3*3, ולהרחיב בהדרגה ל: 5*5, 7*7, 10*10.... כדי להתאים את גודל הטבלה לצרכים של התלמיד שלך.

כפי שאפשר לראות, ישנן שלוש נוסחאות בתמונה:

A2 הגודל האופקי של הטבלה (מספר השורות)

B1 הגודל האנכי של הטבלה (מספר העמודות)

B2 הנוסחא שיוצרת את לוח הכפל

 

תמונה מס. 1: לוח כפל רבועי

 

אינך חייב ל"הצמד" ללוח כפל רבועי, שבו מספר השורות שווה למספר העמודות.

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

Q1 יכיל את הגודל האפקי ו Q2 יכיל את הממד האנכי.

 

 

תמונה מס. 2: לוח כפל מלבני

 

 


 

  

יום שבת, 7 במאי 2022

Another one bites the dust….

 


Another one bites the dust….


For some years now, the most popular posts on my blog were dedicated to explaining how to create the multiplication table in Excel (in Hebrew)
There are 3 posts on that subject, demonstrating 3 different methods:
https://meniporat.blogspot.com/2013/07/blog-post_8817.html
(using mixed references)

http://meniporat.blogspot.com/2013/07/blog-post_8935.html
(with the MMULT function)

http://meniporat.blogspot.com/2013/07/blog-post_2151.html

 (with the PRODUCT function)

But now, with the introduction of the dynamic array functions in Excel 365, everything is so much easier and simpler.
Alas, the old techniques are now obsolete…

“Sic transit gloria mundi” (which in Latin, means: “that is how the world’s glory vanishes”)


Consider the following multiplication table (mt) using my favourite function SEQUENCE, introduced in Excel 365.

The arguments in cells Q1 and Q2 enable you to define a dynamic mt. you can contract it or expand it according to your needs, starting, for instance, with a 3*3 mt, and then gradually increase it to 5*5, 7*7, 10*10… adapting the size to your son’s/daughter’s/pupils’ needs.

There are 3 formulae in the sheet:
A2: the horizontal size

B1: the vertical size

B2: the formula that creates the mt.

 

You don’t have to stick to the square mt, where the horizonal and vertical dimensions are identical.

You can revert to option # 2: a rectangular mt, where the horizonal and vertical dimensions are NOT identical.

So, Q1 will contain the horizontal size whereas in Q2 we will the vertical size.

See attached pictures.

                        Picture 1: The Square Multiplication Table

 

Picture 2: The Rectangular Multiplication Table

 


 

 

 


Excel can speak (English only…)

Excel can speak (English only…)

Want to hear the contents of a cell/group of cells?

Piece o’cake:

 

Step 1:
Add the “Speak Cells” icon to the QAT (Quick Access Toolbar):



 

 

 

Step 2:

Enter some data in a cell/group of cells and then press the new icon in the QAT. That’s all.

Enjoy 😊 😊 😊



 


 



 

 


יום שני, 2 במאי 2022

Three methods to find the number of sheets in a workbook

 

Three methods to find the number of sheets in a workbook

 

Today, I’m going to demonstrate three ways to find the number of sheets in a workbook. These sheets (or some of them) might be empty, so Power Query won’t be able to supply us with an answer

Please pay attention:
Since Methods 2&3 use Macros, the file must be saved as a “macro-enabled” workbook: the file suffix (extension) must be: “.xlsm” and not “.xlsx”

 

Method 1: The SHEETS() Function

The sheets() function is a built-in function in Excel.


                                          Method 1: The SHEETS() Function

 

 

 

 

Method 2: VBA Macro

The VBA Function returns the number of sheets in the workbook.
As can be seen, it can be referred to like any built-in function in Excel.


                                                 Method 2: VBA Macro

 

 

  

 

 

Method 3: Using 4.0 Macro

The GET.WORKBOOK 4.0 Macro Function returns information about the current workbook. The argument 4 – returns the number of worksheets in the workbook. Macro 4.0 functions cannot be defined as regular functions (in a cell) but must be defined, as can be seen in the picture, as a named range.

        Method 3: Using 4.0 Macro

 

 


יום שני, 14 בפברואר 2022

Absence Management

Absence Management


Suppose you are the owner of a small business. You have several employees. Each one of them has a “standard” amount of hours for each working day.

At the end of the day you want to know:

*which employees worked less than the “standard”

*which employees exceeded the “standard”

*which employees worked the exact amount of “standard” hours.

So, the most obvious function you’d think of, off the top of your head, would be the IF function:

 

                   Pic. No 1: The IF solution (not so good..)

 

However, a much simpler solution would be this one, without IF:

                  Pic. No 2: A better solution

 

 




 



יום חמישי, 3 בפברואר 2022

שיטה חדשה לבדיקת ספרת בקורת של תעודת זהות

 

שיטה חדשה לבדיקת ספרת בקורת של תעודת זהות

תעודת זהות ישראלית מורכבת מתשע (9) ספרות, כאשר הספרה התשיעית היא ספרת הבקורת.

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

על השיטה הישנה אפשר לקרוא כאן:

https://www.excelmaster.co.il/2018/08/06/check_id_excel/

 

השיטה שלי הרבה יותר קצרה (יותר מפי שנַים)  וגם הרבה יותר קריאה.
היא משתמשת בשתי פונקציות חדשות באקסל 365:
 SEQUENCE ו- LET

ראשית אציג את הנוסחא, ואז אסביר את האלגוריתם של חישוב ספרת הבקורת.

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

אם המספר מוגדר בתא
B1, אז הנוסחא היא כמו בתמונה:

                                                תמונה מס. 1: הנוסחא המלאה



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

לאחר מכן, אסביר את הנוסחא כולה (כפי שהיא מופיעה בתמונה לעיל)

 

א. האלגוריתם

1.      כל ספרה במקום אי-זוגי (כלומר הספרות במקום: 1,3,5,7,9) מוכפלת ב- 1

2.      כל ספרה במקום זוגי (כלומר הספרות במקום: 2,4,6,8) מוכפלת ב-2

3.      אם המכפלה גדולה מ-9 (וזה יכול, כמובן, לקרות רק לספרה זוגית ורק לספרה זוגית שערכה > 4) אזי יש לסכום את ספרות המכפלה.
לדוגמא:
אם הספרה הרביעית היא 5, תוצאת המכפלה: 5*2 = 10. במקרה כזה, אנו סוכמים את ספרות המכפלה ומקבלים: 1 (1+0=1)

4.      כעת סוכמים את כל המכפלות (כולל המכפלות שעברו "תקון" כמו בסעיף 3) ובודקים אם הסכום מתחלק ב-10 בלי שארית.

אם כן – ספרת הבקורת תקינה, אחרת – הספרה שגויה

 

בדוגמא הראשונה, נשתמש במספר: 924593221


                        תמונה מס. 2: אלגוריתם לחישוב ספרת בקורת למס. 924593221

 

כפי שאפשר לראות בתמונה, פצלנו את המספר לתאים C3:K3

כאשר הספרות האי-זוגיות יושבות בתאים: C3, E3, G3, I3, K3

והספרות הזוגיות יושבות בתאים: D3, F3, H3, J3

תאים C4:K4 מכילים את תוצאות המכפלות של הספרות בתאים C3:K3 ב-1 או ב-2.

הספרה בתא  F3 (5), מוכפלת ב-2 והתוצאה: 10 (בתא  F4). לכן, יש לעשות על תוצאת המכפלה "מניפולציה" ולשנות את ערכה ל-1  (בתא F5)

כעת אנו סוכמים את התאים C5:K5 (התוצאה: 40) ומחלקים ב-10 ובודקים מהי התוצאה:

=MOD(SUM(C5:K5),10)=0

אם התשובה חיובית (TRUE) – אין שארית בחלוקה ל-10. כלומר, ספרת הבקורת של המספר שבדקנו היא תקינה, והמספר כולו הוא מספר ת.ז. תקין.

אחרת, (FALSE) ספרת הבקורת שגויה ולכן גם המספר כולו איננו מספר ת.ז. תקין.

 

דוגמא נוספת: המספר 051651636


            תמונה מס. 3: אלגוריתם לחישוב ספרת בקורת למס. 051651636

 

 

במספר זוגי ישנן שתי ספרות במקום זוגי (D3, F3), שמכפלתן ב-2 נותנת תוצאה גדולה מ-9

לכן את המספר 10 ב- D4 (10=5*2) נהפוך ב-D5  ל: 1+0=1

ואת המספר 12 בתא F4 (12=6*2)  נהפוך ב- F5 ל:  1+2=3

כעת אנו סוכמים את התאים C5:K5 (התוצאה: 30) ומחלקים ב-10 ובודקים מהי התוצאה:

=MOD(SUM(C5:K5),10)=0

התשובה חיובית (TRUE) אין שארית בחלוקה ל-10. ולכן, ספרת הבקורת של המספר שבדקנו היא תקינה, והמספר כולו הוא מספר ת.ז. תקין.

  

 

ב.  הנוסחא

 

הנוסחא מורכבת מ-4 שורות:
בשלוש השורות הראשונות אנו משתמשים בפונקציה
LET
אשר מאפשרת לנו להגדיר קטע נוסחא כדי לחסוך כפילויות מיותרות.
כך, למשל, אנו מגדירים את:
sq במקום: SEQUENCE(9)

את: ev במקום: MID(B1,FILTER(sq,MOD(sq,2)=0),1)

ואת: od במקום: MID(B1,FILTER(sq,MOD(sq,2)=1),1)

ev הוא משתנה המגדיר את הספרות הזוגיות במספר (even)

ו- od הוא משתנה המגדיר את הספרות האי-זוגיות במספר (odd)

בשורה האחרונה, אנו סוכמים את המכפלות (כפי שהסברתי לעיל):

SUM(IF(ev>"4",1+MOD(ev*2,10),ev*2))+SUM(--od))

 

הספרות הזוגיות:

אם הספרה הזוגית גדולה מ-4, אז מכפלתה ב-2 תתן תוצאה של 10 (או יותר) ולכן יש לסכום את הספרות לאחר המכפלה. אחרת – רק לסכום את המכפלות ב-2.

הספרות האי-זוגיות:

סכימה בלבד (כי מספר *1 שווה למספר).

הסכום שהתקבל מצרוף מכפלות המספרים הזוגיים והאי-זוגיים – אנו מחלקים ב-10

(כפי שהסברתי באלגוריתם) ותוצאת החלוקה יכולה להיות, כאמור:

TRUE המספר תקין  J

FALSE המספר אינו תקין L

 

      ג.    חישוב ספרת הבקורת למספר 924593221


                         תמונה מס. 4: חישוב ספרת בקורת למס. 924593221

 

 

 

     ד.  חישוב ספרת הבקורת למספר 051651636

 

                  תמונה מס. 5: חישוב ספרת בקורת למס. 051651636

 

 

 

     ה. חישוב ספרת הבקורת למספר 001234350


                 תמונה מס. 6: חישוב ספרת בקורת למס. 001234350