הגדרת ויצירת נוסחה

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

בית הספר ניווט

1. למה אתה צריך נוסחאות ותפקידים?
2. הגדרת ויצירת נוסחה
3. התייחסות ייחוס מוחלטת תא, עיצוב
4. פונקציות שימושיות אתה צריך להכיר
5. חיפושים, תרשימים, סטטיסטיקה, לוחות פיבוט

• שורות ועמודות
• function math function: SUM ()
• אופרטורים
• עדיפות מפעיל
• דוגמה פיננסיים פונקציה: PMT (), תשלום ההלוואה
• באמצעות "מחרוזת" פונקציה ("מחרוזת" הוא קצרנות עבור "מחרוזת של טקסט") בתוך הנוסחה ואת פונקציות הקינון

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

שורות ועמודות

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

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

עמודות מסומנות באותיות; שורות לפי מספרים. התא הראשון בגיליון האלקטרוני הוא A1 כלומר טור A, שורה 1. העמודות מסומנות A-Z. כאשר האלפבית נגמר, Excel מציב מכתב נוסף בחזית: AA, AB, AC ... AZ, BA, BC, BC, etc.

דוגמה: Function Sum ()

עכשיו בואו להדגים כיצד להשתמש בפונקציה.

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

האשף אומר לך אילו ארגומנטים אתה צריך לספק עבור כל פונקציה. זה גם מספק קישור להוראות מקוונות אם אתה צריך עזרה להבין מה עושה את הפונקציה וכיצד להשתמש בו. לדוגמה, אם תקליד = sum לתוך תא, האשף המקוון יראה לך אילו ארגומנטים נדרשים עבור הפונקציה SUM.

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

עבור הפונקציה הראשונה שלנו, בואו להשתמש SUM (), אשר מוסיף רשימה של מספרים.

נניח שיש לנו גיליון אלקטרוני זה להכיל תוכניות לתקצוב החופשה של המשפחה שלך:

כדי לחשב את סך כל העלויות שאתה יכול לכתוב = b2 + b3 + b4 + b5 אבל קל יותר להשתמש בפונקציה SUM ().

ב- Excel, חפש את הסמל Σ בפינה השמאלית העליונה של מסך Excel כדי למצוא את הלחצן AutoSum (מתמטיקאים משתמשים באות היוונית Σ להוספת סדרה של מספרים).

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

לחץ על "Enter" כדי לקבל את הטווח שנבחר על ידי Excel או להשתמש בסמן כדי לשנות את התאים שנבחרו.

אם אתה מסתכל על מה Excel לשים את הגיליון האלקטרוני אתה יכול לראות את זה כתב את הפונקציה הזו:

בנוסחה זו, Excel מסכם את המספרים מ- B2 ל- B9. הערה, עזבנו כמה חדרים מתחת שורה 5, כך שתוכל להוסיף את התקציב חופשה משפחתית - העלות בהחלט לעלות כמו רשימת הילדים של מה שהם רוצים לעשות ולאן הם רוצים ללכת גדל עוד!

פונקציות מתמטיות אינן פועלות עם אותיות, לכן אם תשים אותיות בעמודה, התוצאה תוצג כ- "#NAME?" כפי שמוצג למטה.

#NAME? מציין שיש איזו שגיאה. זה יכול להיות כל מספר דברים, כולל:

• התייחסות התא רע
• באמצעות אותיות במתמטיקה פונקציות
• מחיקת טיעונים נדרשים
• שם פונקציית האיות שגוי
• פעולות במתמטיקה בלתי חוקית כמו חלוקת ב 0

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

יש לנו לחץ על החלק העליון של הריבוע נמשך על ידי Excel לקחת "כרטיסי טיסה" מתוך התקציב. ניתן לראות את סמל השיער הצולב שניתן לצייר כדי להפוך את הטווח הנבחר גדול יותר או קטן יותר.

לחץ על "Enter" כדי לאשר את התוצאות.

חישוב מפעילי

ישנם שני סוגים של אופרטורים: מתמטיקה והשוואה.

מפעיל מתמטיקה	הגדרה
+	בנוסף
-	חיסור או שלילה, למשל, 6 * -1 = -6
*	כפל
/	חלוקה
%	אחוזים
^	מעריך, למשל 24 = 2 ^ 4 = 2 * 2 * 2 * 2 = 16

ישנם אופרטורים אחרים שאינם קשורים למתמטיקה כמו "&" כלומר שרשור (להצטרף מקצה לקצה) שתי מחרוזות. לדוגמה, = "Excel" & "הוא כיף" שווה ל- "Excel כיף".

עכשיו אנחנו מסתכלים על אופרטורים השוואה.

מפעיל השוואה	הגדרה
=	שווה, למשל, 2 = 4 או "b" = "b"
>	גדול מ, למשל, 4> 2 או "b"> "a"
<	פחות, למשל, 2 < 4 or “a” < “b”
> 49	גדול או שווה ל - דרך אחרת לחשוב על זה הוא = = פירושו או > או =.
<=	פחות מ או שווה ל.
	לא שווה ל, למשל 46

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

שים לב, אם אתה מזין "a"> "b" לתא הוא יגיד "FALSE" מכיוון ש- "a" אינו גדול מ "b" "b" בא אחרי "a" באלפבית, אז " > "B" או "B"> "a".

סדר מראש של המפעיל

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

סדר עדיפות פירושו הסדר שבו המחשב מחשב את התשובה. כפי שהסברנו בשיעור 1, שטח המעגל הוא πr², אשר זהה π * r * r. זה לא (πr)².

אז אתה צריך להבין את סדר הסדר כאשר אתה כותב נוסחה.

באופן כללי, ניתן לומר זאת:

1. Excel מעריך תחילה פריטים בסוגריים העובדים בחוץ.
2. לאחר מכן היא משתמשת סדר עדיפות הכללים של המתמטיקה.
3. כאשר לשני פריטים יש את אותה עדיפות, Excel עובד משמאל לימין.

הקודמים של מפעילי מתמטיקה מוצג להלן, בסדר יורד.

(ו)	כאשר סוגריים משמשים, הם לעקוף את הכללים הרגילים של קדימות. משמעות הדבר היא כי Excel יעשה את החישוב הראשון. אנו מסבירים זאת בהמשך.
-	שלילה, למשל, -1. זה אותו הכפלת מספר ב -1. -4 = 4 * (-1)
%	אחוז, פירושו להכפיל ב -100. לדוגמה, 0.003 = 0.3%.
^	מעריך, למשל, 10 ^ 2 = 100
* ו /	הכפל וחלק. איך שני מפעילי יש את אותה עדיפות? זה רק אומר שאם הנוסחה יש שני אופרטורים נוספים עם אותה עדיפות, אז החישוב נעשה משמאל לימין.
+ ו -	חיבור וחיסור.

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

דוגמה: חישוב שטח המעגל

שטח המעגל הוא= PI () * רדיוס ^ 2.

בהסתכלות על הטבלה לעיל אנו רואים כי מעריכים באים לפני הכפל. אז המחשב מחשב תחילה רדיוס ^ 2 ואז הוא מכפיל את התוצאה של פי.

דוגמה: חישוב העלאה בשכר

נניח הבוס שלך מחליט שאתה עושה עבודה נהדרת והוא או היא הולך לתת לך להעלות 10%! איך היית לחשב את השכר החדש שלך?

ראשית, זכור כי הכפל מגיע לפני תוספת.

האם זה = משכורת + משכורת * 10% או האם זה = משכורת + (משכורת * 10%)?

נניח שהשכר שלך הוא $ 100. עם העלאה של 10%, המשכורת החדשה שלך תהיה:

= 100 + 100 * 10% = 100 + 10 = 110

אתה יכול גם לכתוב את זה ככה:

= 100 + (100 * 10%) = 100 + 10 = 110

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

אגב, הדרך הקלה יותר לכתוב את זה היא = שכר * 110%

סוגריים יכולים להיות מקוננים זה בתוך זה. לכן, כאשר אנו כותבים (3 + (4 * 2)), עובד מבפנים אל מחוץ, מחשבת תחילה 4 * 2 = 8, ולאחר מכן להוסיף 3 + 8 כדי לקבל 11.

עוד כמה דוגמאות

הנה דוגמה נוספת: = 4 * 3 / 2. מהי התשובה?

אנו רואים מן הכללים בטבלה לעיל כי * ו / יש קדימות שווה. אז Excel עובד משמאל לימין, 4 * 3 = 12 הראשון, ואז מחלק כי 2 כדי לקבל 6.

שוב אתה יכול לעשות את זה מפורש על ידי כתיבה = (4 * 3) / 2

מה לגבי = 4 + 3 * 2?

המחשב רואה שני אופרטורים * ו- +. אז בעקבות הכללים של קדימות (כפל מגיע לפני תוספת) היא מחשבת 3 * 2 = 6 הראשון, ולאחר מכן מוסיף 4 כדי לקבל 10.

אם אתה רוצה לשנות את סדר הקדימות היית כותב = (4 + 3) * 2 = 14.

מה עם זה 1 = ^ 3?

אז התשובה היא -3 כי המחשב מחושב = (-1) ^ 3 = -1 * -1 * -1 = -1.

זכור שלילי פעמים שלילי חיובי חיובי שלילי חיובי הוא שלילי. אתה יכול לראות את זה ככה (-1 * -1) * -1 = = 1 -1 -1 = -1 -1.

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

דוגמה: פונקציה הלוואה תשלום (PMT)

בואו נראה דוגמה לחשב תשלום הלוואה.

התחל על ידי יצירת גליון עבודה חדש.

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

באופן דומה, פורמט תאים אחרים עבור "מטבע" במקום אחוז לבחור "מספר" עבור תקופת ההלוואה.

עכשיו יש לנו:

הוסף את הפונקציה SUM () ל "סך הכל" ההוצאות החודשיות.

הערה, משכנתא התא אינו נכלל בסך הכל. Excel אינו יודע שברצונך לכלול את המספר, מכיוון שאין ערך. אז היזהר להרחיב את SUM () פונקציה למעלה או באמצעות הסמן או הקלדת E2 שבו כתוב E3 לכלול את המשכנתא בסכום.

שים את הסמן בתא התשלום (B4).

בתפריט Formulas בחר את התפריט הנפתח "Financial" ולאחר מכן בחר את הפונקציה PMT. האשף יצוץ:

השתמש הסמן כדי לבחור את "שיעור", "nper" (טווח ההלוואה), "Pv" ("ערך נוכחי" או סכום ההלוואה). שים לב כי אתה צריך לחלק את הריבית ב -12 מאז הריבית מחושבת חודשי. גם אתה צריך להכפיל את טווח ההלוואה בשנים 12 כדי לקבל את תקופת ההלוואה בחודשים. לחץ על "אישור" כדי לשמור את התוצאה בגיליון האלקטרוני.

שים לב שהתשלום מוצג כמספר שלילי: -1013.37062. כדי לעשות את זה חיובי ולהוסיף אותו להוצאות החודשיות, הצבע על תא המשכנתאות (E2). הקלד "= -" ולאחר מכן השתמש בסמן כדי להצביע על שדה התשלום. הנוסחה המתקבלת היא = -B4.

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

ההוצאות החודשיות שלך הם 1,863 $ - Ouch!

דוגמה: פונקציית טקסט

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

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

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

לדוגמה, להסתכל על "וושינגטון, ג'ורג '." הליך לפצל את זה לתוך שתי מילים יהיה:

1. חשב את אורך המחרוזת.
2. מצא את המיקום של פסיק (זה מראה שם מילה אחת מסתיימת והשני מתחיל).
3. העתק את הצד השמאלי של המיתר עד לפסיק.
4. העתק את הצד הימני של המחרוזת מהפסיק עד הסוף.

בואו נדון איך לעשות את זה עם "ג'ורג 'וושינגטון" צעד אחר צעד ב- Excel.

1. חישוב אורך המחרוזת עם הפונקציה = LEN (A3) - התוצאה היא 18.
2. עכשיו למצוא את המיקום של פסיק על ידי הזנת פונקציה זו = FIND (",", A3 ") - התוצאה היא 11.
3. עכשיו קח את הצד השמאלי של החוט למעלה עד הפסיק וצור נוסחה מקוננת זו באמצעות תוצאה משלב 1: = LEFT (A3, FIND (",", A3) -1). שים לב, אנחנו צריכים לחסר 1 מהאורך כי FIND נותן את המיקום של פסיק.

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

אז יש לנו "וושינגטון", עכשיו אנחנו צריכים לקבל את "ג 'ורג'." איך אנחנו עושים את זה?

שים לב, יכולנו לשמור את התוצאה משלב 1 בתא כשלעצמו, נניח, B6, ואז לכתוב נוסחה פשוטה יותר = LEFT (A3, B6-1). אבל זה משתמש בתא אחד עבור הצעד לסירוגין.

1. זכור את המיקום של פסיק או לחשב אותו שוב.
2. חשב את אורך המחרוזת.
3. ספירת התווים מקצה המחרוזת לפסיק.

קח את מספר התווים משלב 3 וחסר 1 כדי להשמיט את הפסיק והחלל.

בוא נעשה את זה צעד אחר צעד.

1. מלמעלה, זה = FIND (",", A3 ")
2. אורך המחרוזת הוא = LEN (A3)
3. יהיה עליך להשתמש במתמטיקה כלשהי כדי למצוא את מספר התווים שיש לנקוט: = LEN (A3) - חפש (",", A3) - 1
4. הצד הימני של החוט שאנחנו רוצים הוא = RIGHT (A3, LEN (A3) - מצא (",", A3) - 1)

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

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

מגיע הבא ...

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

הבא הבא בשיעור 3, נדון התייחסות התא ואת העיצוב, כמו גם העברת נוסחאות העתקה אז אתה לא צריך לשכתב כל נוסחה שוב ושוב!