יום שבת, 12 ביולי 2014

חִתּוּךְ (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






                  

אין תגובות:

הוסף רשומת תגובה