יום שבת, 2 ביולי 2022

Excel - What is Intersection? Why is it useful? - Part I

 

 

Excel – Intersection: What is it? Why is it useful?

 

The following dataset displays the number of juvenile delinquency cases opened in four US states during the year 2020. (not really, the data are fabricated 😊 )

Pic 1: the dataset

 

Now, suppose you want to analyse the dataset. For example:
How many cases were opened in Californian in May?

This can easily be solved in various methods,
for example, with the following formula:


                                      Pic 2: A "traditional" solution

Or with this one:

                             Pic 3: Another "traditional" solution

 

 

 

However, there's a far better, much simpler method in Excel for cross tab data.

Unfortunately, it is lesser known. It is called Intersection.
The intersection Operation is ideal when dealing with cross tab data: a column data intersecting a row data.

The intersection operation does not refer to cells or ranges, but to names.

So, how do we use this feature?

First of all, we need to define the names (taken from cells B1:E1, the column   headers and from cells A2:A13, the row headers)

 

So, we Create names from selection as explained in the picture:      

                                Pic 4: Create Names from Selection    

 

If we take a close look at the definitions created in the Name Manager, we can clearly see that each State has 12 values (the values of the 12 months) and that each month has four values (one for every state).

                            Pic 5: Name Manager's definitions

 

And now let's turn to some cool applications of Intersection.

Example No.1:
Remember the question we asked ourselves at the beginning?
How many cases were opened in Californian in May?

So, instead of a lengthy formula, we can have the solution in a very short formula:
=CA May

          Pic 6: How many cases were opened in California in May

The formula consists of two elements: the column (CA) and the row (May), separated by the intersection operator " ".

So simple, so easy, so obvious….

Example No.2:

 

How many cases were opened in Texas alone during the entire year?

Pic 7: How many cases were opened in Texas during the entire year?

 

 

Example No.3:


How many cases were opened in Texas in the first 4 months?

      Pic 8: How many cases were opened in Texas during Jan.-Apr.?

 

Example No.4:

How many cases were opened in Texas and California in the first 4 months?


         Pic 9: No. of cases opened in Texas & California in Jan-Apr

 

Example No.5:

How many cases were opened in January (excluding Texas)?


   Pic 10: How many cases were opened in January (excluding Texas)

 

Example No.6:

Total cases in Texas, California and Florida (altogether)

      Pic 11: Total cases in Texas, California and Florida (altogether)

 

 


 


יום שישי, 1 ביולי 2022

How to remove empty rows in a range (without VBA, without PQ, without formulae, even without the mouse)

 

Excel – How to remove empty rows in a range

 

The following technique explains, step by step, how to get rid of empty rows within a range.

Please note that this technique does not use either VBA, PQ or formulae.

It doesn't even use the mouse 😊






יום שני, 13 ביוני 2022

How to get rid of unwanted names in a list

 

How to get rid of unwanted names in a list

(no formulae, no PQ)

Suppose you have a list of names (in col. A) from which you need to exclude names that appear in a different list (col. C)

 

This can be easily done in a few simple steps without formulae or Power Query:

 

A .  we prepare two lists

In col. A – the original list

In col. C – the list of names to exclude from the original list.

                                                    Pic No.1


 

B .  we "color" in yellow all the names in col. A

                                                            Pic No.2

 

 

C.  Find duplicates using Conditional Formatting
select both columns-> Conditional Formatting-> Highlight Cells Rules->Duplicate Values

 

 

                                                Pic No.3

 

D. Filter the list in col. A and select: Filter by cell color.

You should, of course, select the yellow color




                                                     Pic No.4

E. Final Result: now the list in col. A contains only names that do not appear in the list of col. C

 


                                                                Pic No.5

                 

 



 

 

איך נפטרים משמות לא רצויים ברשימה

 

איך נפטרים משמות לא רצויים ברשימה (שיטה מקורית שלי)
(בלי נוסחא ובלי Power Query) !!!

א.    מכינים שתי רשימות (כמו בתמונה):
בעמודה
A – הרשימה המקורית
בעמודה
C – רשימת השמות למחיקה מהרשימה המקורית

תמונה מס.1

ב.     צובעים בצהוב את כל השמות בעמודה A

תמונה מס.2

 

 

ג.      מוצאים כפילויות בעזרת "עיצוב מותנה" (Conditional Formatting)

                                                                     תמונה מס.3

ד.     מפעילים סינון על הרשימה בעמודה A ובוחרים: סינון לפי צבע (Filter by color)
בוחרים, כמובן, בצבע הצהוב

תמונה מס.4

 

ה.    התוצאה הסופית: בעמודה A מופיעים רק השמות שלא מופיעים ברשימה בעמודה C


תמונה מס.5

 








יום שישי, 27 במאי 2022

Four methods to highlight the smallest number in a range

 

4 Methods to highlight the smallest number in a range

 

In Excel, as you might know, there is (almost) always an alternative method to accomplish a certain task.

In this post I’m going to show your 4 different methods to highlight the smallest number in a range:

 


                                                        Method 1: Using Top/Bottom Rules

 

 


Method 2: Using a formula (MIN function)

 

 

Method 3: Using a formula (SMALL function)

 

 

 

          Method 4: Using a formula (LARGE function)

 

 








יום שלישי, 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: לוח כפל מלבני