נוסחה כללית | _+_ | סיכוםכדי לאתר את המיקום של ערך במערך דו -ממדי, תוכל להשתמש בפונקציה SUMPRODUCT. בדוגמה המוצגת, הנוסחאות המשמשות לאיתור מספרי השורות והעמודות של הערך המרבי במערך הן:
= SUMPRODUCT ((data= MAX (data))* ROW (data))- ROW (data)+1
היכן 'הנתונים' הם טווח בשם C5: G14.
הערה: בדוגמה זו אנו מוצאים באופן שרירותי את המיקום של הערך המרבי בנתונים, אך ניתן להחליף נתונים = MAX (נתונים) בכל בדיקה לוגית אחרת שתבודד ערך נתון. שימו לב גם נוסחאות אלה ייכשלו אם יש ערכים כפולים במערך.
נוסחת Excel להוספת שנים לתאריךהֶסבֵּר
כדי לקבל את מספר השורה, הנתונים מושווים לערך המקסימלי, שיוצר מערך של תוצאות TRUE FALSE. אלה מוכפלים בתוצאה של ROW (נתונים) המייצרת ומערך של מספרי שורות הקשורים לטווח 'הנתונים' בשם:
= SUMPRODUCT ((data= MAX (data))* ROW (data))- ROW (data)+1 = SUMPRODUCT ((data= MAX (data))* COLUMN (data))- COLUMN (data)+1
פעולת הכפל גורמת ל- Excel לכפות את ערכי TRUE FALSE במערך הראשון ל- 1s ו- 0s, כך שנוכל לדמיין שלב ביניים כזה:
איך לכתוב בסימון מדעי באקסל
= SUMPRODUCT ({FALSE,FALSE,FALSE,FALSE,FALSEFALSE,FALSE,FALSE,FALSE,FALSEFALSE,FALSE,FALSE,FALSE,FALSEFALSE,FALSE,FALSE,FALSE,FALSEFALSE,FALSE,TRUE,FALSE,FALSEFALSE,FALSE,FALSE,FALSE,FALSEFALSE,FALSE,FALSE,FALSE,FALSE}*{567891011})
SUMPRODUCT מחזיר לאחר מכן תוצאה של 9, המתאימה לשורה ה -9 בגליון העבודה. כדי לקבל אינדקס ביחס לטווח הנתונים 'נתונים', אנו משתמשים:
= SUMPRODUCT ({0,0,0,0,00,0,0,0,00,0,0,0,00,0,0,0,00,0,1,0,00,0,0,0,00,0,0,0,0}*{567891011})
התוצאה הסופית היא המערך {543210-1}, שממנו מוצג רק הערך הראשון (5).
הנוסחה לקביעת מיקום העמודה פועלת באותו אופן.
הערה: נתקלתי בגישה זו בהערה של מייק אריקסון מעל ב- MrExcel.com. יש גם כמה רעיונות טובים אחרים בשרשור הזה, כולל אפשרות לנוסחת מערך.
הסופר דייב בראנס
