Excel מערך נוסחה היא נוסחה המבצעת חישובים על הערכים במערך אחד או יותר, ולא בערך נתונים יחיד. בתכניות גיליונות אלקטרוניים, מערך הוא טווח או סדרה של ערכי נתונים קשורים המאוחסנים בדרך כלל בתאים סמוכים בגליון עבודה. מדריך זה מסביר כיצד להשתמש בנוסחאות מערך בכל הגירסאות המודרניות של Excel.
ערכת לימוד זו תואמת את רוב הגירסאות של Excel כולל 2007, 2010, 2013, 2016 ו- Office 365.
מה הם נוסחאות מערך?
נוסחאות מערך דומים לנוסחאות רגילות:
- הם מתחילים עם סימן שווה ( = )
- השתמש באותה תחביר כמו נוסחאות רגילות
- השתמש באותם אופרטורים מתמטיים
- בצע את אותו סדר של הפעולה
ב- Excel, נוסחאות מערך מוקפות בסוגריים מסולסלים { } - אלה הפלטה לא יכול להיות פשוט הקלדת; יש להוסיף אותם לנוסחה על ידי לחיצה על Ctrl , Shift , ו להיכנס מפתחות לאחר הקלדת הנוסחה לתא או לתאים. מסיבה זו, נוסחת מערך מכונה לפעמים CSE נוסחה ב - Excel.
חריג לכלל זה הוא כאשר נעשה שימוש בסוגריים מסולסלים כדי להזין מערך כארגומנט עבור פונקציה שמכילה בדרך כלל רק ערך יחיד או הפניה לתא. לדוגמה, במדריך שמתחת VLOOKUP וה בחר פונקציה כדי ליצור נוסחה חיפוש שמאל, מערך נוצר עבור בחר פונקציה אינדקס ארגומנט על ידי הקלדת הפלטה סביב מערך נכנס.
צעדים ליצירת נוסחת מערך
- הזן את הנוסחה.
- החזק את Ctrl ו Shift מקשים על המקלדת.
- לחץ ושחרר את להיכנס מפתח כדי ליצור את הנוסחה מערך.
- שחרר אתCtrl ו Shift מפתחות.
אם נעשה כראוי, הנוסחה תהיה מוקפת בסוגריים מתולתל, וכל תא מחזיק את הנוסחה יכיל תוצאה שונה.
עריכת נוסחת מערך
בכל פעם שנוסחת מערך נערכת, הפלחים המתולתלים נעלמים מסביב לנוסחת המערך. כדי להחזיר אותם, יש להזין את נוסחת המערך על ידי לחיצה על Ctrl, Shift, ו להיכנס מפתחות בדיוק כמו כאשר הנוסחה מערך נוצר לראשונה.
סוגים של נוסחאות מערך
קיימים שני סוגים עיקריים של נוסחאות מערך:
- יחיד מערך התא נוסחאות אשר לבצע חישובים מרובים בתא גליון אחד.
- Multi-cell מערך נוסחאות אשר ממוקמים בתא גליון עבודה אחד או יותר.
נוסחאות מערך תא יחיד
סוג זה של נוסחת מערך משתמש בפונקציה, כגון SUM, ממוצע, או לספור, כדי לשלב את הפלט של נוסחת מערך מרובה תאים לערך יחיד בתא אחד. דוגמה לנוסחת מערך תא בודד תהיה:
{= SUM (A1: A2 * B1: B2)}
נוסחה זו מוסיפה יחד את תוצר A1 * B1 ו A2 * B2, ולאחר מכן מחזירה תוצאה אחת בתא יחיד בגליון העבודה. דרך נוספת לכתיבת הנוסחה הנ"ל תהיה:
Multi-Cell מערך נוסחאות
כפי שמציע שם, נוסחאות מערך אלה ממוקמות בתאי גליון עבודה מרובים, והן מחזירות מערך כתשובה. במילים אחרות, אותה נוסחה ממוקמת בשני תאים או יותר, והיא מחזירה תשובות שונות בכל תא.
כל עותק, או למשל, של נוסחת המערך, מבצע את אותו חישוב בכל תא שבו הוא נמצא, אך כל מופע של הנוסחה משתמש בנתונים שונים בחישוביו. לכן, כל מקרה מייצר תוצאות שונות. דוגמה לנוסחת מערך תאים מרובים תיראה כך:
{= A1: A2 * B1: B2}
אם הדוגמה לעיל ממוקמת תאים C1 ו C2 בגליון עבודה, אז התוצאות יהיו כדלקמן:
- הנתונים A1 מוכפל בנתונים B1, והתוצאות מאוחסנות ב תא C1.
- הנתונים A2 מוכפל בנתונים B2, והתוצאות מאוחסנות ב תא C2.
באמצעות מערכים כדי לשנות שורות ועמודות
ה טרנספוזיציה פונקציה משמש להעתקת נתונים משורה לתוך עמודה או להיפך. פונקציה זו היא אחת המעטות ב- Excel שיש להשתמש בה תמיד כנוסחת מערך.
מערך נוסחאות ו - Excel פונקציות
רבים של פונקציות מובנות של Excel, כגון SUM, ממוצע, ו לספור, ניתן להשתמש גם בנוסחת מערך. יש גם כמה פונקציות, כגון טרנספוזיציה פונקציה, כי תמיד צריך להיות נכנס כמערך על מנת שזה יעבוד כראוי. התועלת של פונקציות רבות כגון אינדקס ו התאמה או MAX ו אם ניתן להרחיב באמצעות אותם יחד בנוסחת מערך.
דוגמאות של נוסחאות מערך ב- Excel:
01 מתוך 08יצירת מערך יחיד פשוט מערך הנוסחה
תא יחיד מערך נוסחאות בדרך כלל הראשון לבצע חישוב רב תא ולאחר מכן להשתמש בפונקציה כגון ממוצע או SUM כדי לשלב את הפלט של המערך לתוצאה אחת.
02 מתוך 08התעלם מערכי שגיאה בעת איתור נתונים
נוסחת מערך זו משתמשת ממוצע, אם, ו ISNUMBER פונקציות כדי למצוא את הערך הממוצע של הנתונים הקיימים תוך התעלמות ערכי השגיאה כגון # DIV / 0! ו #NAME?
03 מתוך 08ספירת תאים של נתונים
להשתמש ב SUM ואם פונקציות בנוסחת מערך לספור תאים של נתונים העונים על אחד התנאים; טכניקה זו שונה מלהשתמש ב- Excel COUNTIFS פונקציה, המחייבת את כל התנאים להגדיר להיות נפגשו לפני התא נספר.
04 מתוך 08מצא את המספר החיובי ביותר או השלילי
דוגמה זו משלבת את MAX פונקאם פונקציה בנוסחת מערך שתמצא את הערך הגדול ביותר או המרבי עבור מגוון של נתונים כאשר קריטריונים ספציפיים מתקיימים.
05 מתוך 08מצא את המספר החיובי או השלילי הקטן ביותר
בדומה לדוגמה לעיל, זה משלב את MIN פונקציה IF פונקציה בנוסחת מערך כדי למצוא את הערך הקטן ביותר או המינימלי עבור מגוון רחב של נתונים כאשר קריטריונים ספציפיים מתקיימים.
06 מתוך 08מצא את הערך התיכון או החציוני
ה מדיאני פונקציה ב- Excel מוצא את הערך האמצעי עבור רשימה של נתונים. על ידי שילוב זה עם אם פונקציה בנוסחת מערך, הערך האמצעי לקבוצות שונות של נתונים קשורים ניתן למצוא.
07 מתוך 08יצירת נוסחה Lookup עם קריטריונים מרובים
נוסחת מערך זו כוללת קינון התאמה ו אינדקס פונקציות כדי למצוא מידע ספציפי במסד נתונים.
08 מתוך 08הפוך משמאל בדיקת פורמולה
ה VLOOKUP פונקציה בדרך כלל רק מחפש נתונים הממוקם בעמודות מימין, אבל על ידי שילוב עם בחר פונקציה, אתה יכול ליצור נוסחה החיפוש השמאלי כי יחפש עמודות נתונים בצד שמאל של Lookup_value ויכוח.
