חִתּוּךְ (Intersection) ב"אקסל" - לְמָה/לָמָה זה טוב?

חִתּוּךְ (Intersection) ב"אקסל"  - לְמָה/לָמָה זה טוב?

בפוסט זה נדגים את השמוש באופרטור החִתוּך (" ") בנוסחאות "אקסל".

האופרטור " " (התו: רֶוַחBLANK = ) משמש ליצירת חתוך בין עמודות ובין שורות.

לפנינו תחום נתונים (A1:E13) הבנוי כטבלה.

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

TX=Texas, NY = New York, CA = California, FL = Florida

   Pic 01: Original Data

אנו נגדיר לתחום הנתונים (A1:E13) שמות באמצעות:

Create Names from Selection

           

                                    Pic 02: Create Names from Selection

Formulas*Defined Names*Create from Selection*Create Names from Selection

     Pic 03: Creating Names

יצרנו מעין טבלה שבה לכל עמודה ולכל שורה יש שם:
שם העמודה נלקח מהשורה הראשונה בכל עמודה (שמות המדינות) [Top Row]

שם השורה נלקח מהשמות בכל שורה מהעמודה הראשונה (כלומר, שמות החודשים)

[Left Column]

את השמות אפשר לראות ב: Name Manager

(Formulas*Defined Names*Name Manager)

                                  04. Names in “Name Manager”

שימושי החיתוך – במספר "טריקים":

טריק מס. 1

אם נרצה לדעת את הערך: כמה יחידות נמכרו בטקסס (TX) בחודש ינואר?
כלומר, עלינו למצוא את החיתוך שבין TX ובין ינואר

והפתרון:

=(TX January)

05. Texas Sales in January (1 of 2)

והתשובה: 1 (תא B2)

 06. Texas Sales in January (2 of 2)

שים לב, בין שני הפרמטרים של החיתוך (TX ו- January) חייב להִמָּצֵא התו " " (רווח).

טריק מס. 2

כמה יחידות נמכרו בטקסס בכל התקופה?

בעזרת הפונקציה SUM

=SUM(TX)

07. Texas Sales in the entire Period

והתשובה:  78

טריק מס. 3

מידע יותר מורכב. למשל: כמה יחידות נמכרו בטקסס בחודשים: ינואר-אפריל?

את זה נוכל לבצע בשתי שיטות:

א.    בעזרת הפונקציה SUM

=SUM(January:April TX)

והתוצאה: 10

                             08. Texas Sales January:April (SUM Function)

ב.     בעזרת הפונקציה SUMPRODUCT

=SUMPRODUCT(January:April TX)

והתוצאה (שוב): 10

הפונקציה SUMPRODUCT כופלת איברי מערך אחד באיברי מערך שני ומסכמת את המכפלות. מכיוון שלפנינו רק מערך אחד (January:April) – אזי יש רק סיכום של אברי המערך, והתוצאה זהה לתוצאת פונקצית: SUM

            09. Texas Sales January:April (SUMPRODUCT Function)

טריק מס. 4

סיכום של שני תחומים : כמה יחידות נמכרות בטקסס (TX)  ובקליפורניה (CA) בתקופה הנ"ל (January:April)?

והתשובה, באמצעות הפונקציה SUM

=SUM(January:April TX, January:April CA )

היא: -20

          10. Texas and California: Sales in January:April

טריק מס. 5

כמה יחידות נמכרו במדינת NY בכל התקופה ובחודש ינואר בכל המדינות?

אם נכתוב:

=SUM(January, NY)

נקבל תשובה שאיננה נכונה (!)

הנוסחא:

=SUM(January, NY)

זהה בעצם לנוסחא:

=SUM(January)+Sum(NY)

מכיון ש: ((NY January מסתכם פעמיים [התא: C2] (!): פעם אחת ב-January ופעם נוספת ב- NY...

11. NY (all year) and January (all states) - Wrong solution

לכן, הנוסחא הנכונה חייבת להפחית את תוצאת החיתוך של NY ושל January

=SUM(January, NY) - (January NY)

או:

=SUM(January, NY) – SUM(January NY)

והתוצאה: -409

12. NY (all year) and January (all states) - Correct solution

טריק מס. 6

באיזו מדינה היה מספר המכירות הגבוה ביותר בחודש ינואר?

=LARGE(January,1)

         13. Max Sales in January

טריק מס. 7

באיזו מדינה היה סך המכירות הנמוך ביותר בחודש ינואר?

=LARGE(January,COUNT(January))

                14. Min Sales in January

בדרך כלל משמשת הפונקציה LARGE  למציאת המספר הגדול ביותר מבין סדרת מספרים (כמו בטריק מס. 7= הפרמטר הוא 1, כלומר: נמצא את המספר במקום הראשון=הגדול ביותר).

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

COUNT(January) - נותן את מספר המדינות בטבלה (=4) . לכן, הנוסחא

=LARGE(January,COUNT(January))

נותנת את סך המכירות של המדינה המדורגת במקום הרביעי והנמוך ביותר מבחינת המכירות.

טריק מס. 8

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

למשל, בתא: A16 נציין את February ובתא: A17 נציין את FL

בתא: D16 נכתוב את הנוסחא: 

=INDIRECT(A17) INDIRECT(A16)

והתוצאה:  חיתוך של מדינת פלורידה (FL)  בחודש פברואר, כלומר: תא E3

והתוצאה: 2

                          15. Intersection - Using INDIRECT