מיין לפי נוסחה

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

שיטה 1. נתונים מספריים

אם הרשימה מכילה רק מידע מספרי, אזי ניתן לבצע את המיון בקלות באמצעות הפונקציות הכי פחות (קָטָן) и קו (שׁוּרָה):

פונקציה הכי פחות (קָטָן) שולף מהמערך (עמודה A) את האלמנט ה-n הקטן ביותר בשורה. הָהֵן. SMALL(A:A;1) הוא המספר הקטן ביותר בעמודה, SMALL(A:A;2) הוא השני בקטן, וכן הלאה.

פונקציה קו (שׁוּרָה) מחזירה את מספר השורה עבור התא שצוין, כלומר ROW(A1)=1, ROW(A2)=2 וכו'. במקרה זה, הוא משמש פשוט כמחולל של רצף מספרים n=1,2,3... עבור הרשימה הממוינת שלנו. באותה הצלחה, ניתן היה ליצור עמודה נוספת, למלא אותה ידנית ברצף המספרי 1,2,3... ולהתייחס אליה במקום לפונקציית ROW.

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

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

ראשית, בואו נוסיף עמודת שירות עם נוסחה שבה המספר הסידורי של כל שם ברשימה הממוינת העתידית יחושב באמצעות הפונקציה COUNTIF (COUNTIF):

בגרסה האנגלית זה יהיה:

=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)

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

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

ובכן, לבסוף, נותר רק לשלוף את השמות מהרשימה לפי המספרים שלהם. לשם כך, אתה יכול להשתמש בנוסחה הבאה:

פונקציה יותר חשוף (התאמה) מחפש בעמודה ב' את המספר הסידורי הרצוי (1, 2, 3 וכו') ולמעשה מחזיר את מספר השורה שבה נמצא מספר זה. פוּנקצִיָה מדד (אינדקס) שולף מעמודה A את השם במספר שורה זה.

שיטה 3: נוסחת מערך

שיטה זו היא, למעשה, אותו אלגוריתם מיקום כמו בשיטה-2, אך מיושמת על ידי נוסחת מערך. כדי לפשט את הנוסחה, טווח התאים C1:C10 קיבל את השם רשימה (בחר תאים, לחץ Ctrl + F3 ולחצן צור):

בתא E1, העתק את הנוסחה שלנו:

=INDEX(List; MATCH(SMALL(COUNTIF(List; "<"&List); ROW(1:1)); COUNTIF(List; "<"&List; 0))

או בגרסה האנגלית:

=INDEX(List, MATCH(SMALL(COUNTIF(List, «<"&List), ROW(1:1)), COUNTIF(List, "<"&List), 0))

ולדחוף Ctrl + Shift + Enterכדי להזין אותו כנוסחת מערך. לאחר מכן ניתן להעתיק את הנוסחה המתקבלת לכל אורך הרשימה.

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

ראשית, יהיה צורך להגדיר את טווח הרשימה באופן דינמי. לשם כך, בעת היצירה, עליך לציין לא טווח קבוע C3:C10, אלא נוסחה מיוחדת שתתייחס לכל הערכים הזמינים, ללא קשר למספרם. נְקִישָׁה Alt + F3 או פתח את הכרטיסייה נוסחאות - מנהל שמות (נוסחאות - מנהל שמות), צור שם חדש ובשדה קישור (התייחסות) הזן את הנוסחה הבאה (אני מניח שטווח הנתונים שיש למיין מתחיל מתא C1):

=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)

=OFFSET(C1,0,0,SCHÖTZ(C1:C1000),1)

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

=IFERROR(INDEX(List; MATCH(SMALL(COUNTIF(List; "<"&List); ROW(1:1)); COUNTIF(List; "<"&List; 0));»»)

=IFERROR(NDEX(List, MATCH(SMALL(COUNTIF(List, «<"&List), ROW(1:1)), COUNTIF(List, "<"&List), 0));"")

הוא תופס את השגיאה #NUMBER ומוציא במקום ריק (מרכאות ריקות).

:

• מיין טווח לפי צבע
• מהן נוסחאות מערך ולמה הן נחוצות
• מיון מיון ומערכים דינמיים ב-Office 365 החדש