ל- Excel כבר יש כמה דרכים למיין נתונים במהירות.
ניתן למיין ערכת נתונים בקלות באמצעות סמלי המיון שבסרט הכלים או בתיבת הדו -שיח מיון.
אז מדוע אתה צריך לדעת כיצד לעשות זאת באמצעות VBA?
ידיעה כיצד למיין נתונים באמצעות VBA יכולה להיות מועילה כאשר היא נכללת כחלק מהקוד שלך. לדוגמה, נניח שאתה מקבל מערך נתונים יומי/שבועי שעליך לעצב ולמיין בסדר ספציפי.
אתה יכול ליצור מאקרו שיעשה את כל זה עבורך בלחיצה אחת. זה יחסוך לך הרבה זמן ומאמץ בכל פעם שאתה עושה את זה.
כמו כן, אם אתה יוצר לוחות מחוונים של Excel, תוכל לקחת את יכולת המיון של Excel לרמה חדשה על ידי מתן אפשרות למשתמש למיין את הנתונים על ידי לחיצה כפולה על הכותרת (כפי שמוצג להלן).
אעסוק כיצד ליצור זאת בהמשך הדרכה זו. קודם כל נבהיר את היסודות במהירות.
הכרת שיטת Range.Sort ב- Excel VBA
בעת מיון באמצעות VBA, עליך להשתמש בשיטת Range.Sort בקוד שלך.
'הטווח' יהיה הנתונים שאתה מנסה למיין. לדוגמה, אם אתה ממיין את הנתונים ב- A1: A10, אז 'טווח' יהיה טווח ("A1: A10").
תוכל גם ליצור טווח עם שם ולהשתמש בו במקום הפניות לתאים. לדוגמה, אם אני יוצר טווח בשם 'DataRange' עבור התאים A1: A10, אז אני יכול גם להשתמש בטווח ("DataRange")
בעזרת שיטת המיון, עליך לספק מידע נוסף באמצעות פרמטרים. להלן הפרמטרים העיקריים שאתה צריך לדעת:
- מַפְתֵחַ - כאן עליך לציין את העמודה שברצונך למיין. לדוגמה, אם ברצונך למיין עמודה A, עליך להשתמש במפתח: = טווח ("A1")
- להזמין - כאן אתה מציין אם אתה רוצה את המיון בסדר עולה או בסדר יורד. לדוגמה, אם אתה רוצה את המיון בסדר עולה, תשתמש ב- Order: = xlAscending
- כּוֹתֶרֶת - כאן אתה מציין אם מערך הנתונים שלך מכיל כותרות או לא. אם יש לו כותרות, המיון מתחיל מהשורה השנייה של מערך הנתונים, אחרת הוא מתחיל מהשורה הראשונה. כדי לציין שלנתונים שלך יש כותרות, תשתמש בכותרת: = xlYes
בעוד ששלושת אלה מספיקים ברוב המקרים, תוכל לקרוא עוד על הפרמטרים במאמר זה.
כעת נראה כיצד להשתמש בשיטת Range.Sort ב- VBA למיון נתונים ב- Excel.
מיון טור יחיד ללא כותרת
נניח שיש לך עמודה אחת ללא כותרת (כפי שמוצג להלן).
אתה יכול להשתמש בקוד שלהלן כדי למיין אותו בסדר עולה.
Sub SortDataWithoutHeader () טווח ("A1: A12"). מפתח מיון 1: = טווח ("A1"), סדר 1: = xlAscending, Header: = xl אין סוף קצה
שים לב שציינתי את טווח הנתונים באופן ידני כטווח ("A1: A12").
במקרה וייתכנו שינויים בנתונים וייתכן שיתווספו/יימחקו ערכים, תוכל להשתמש בקוד שלהלן המתאים באופן אוטומטי בהתבסס על התאים המלאים במערך הנתונים.
תת SortDataWithoutHeader () טווח ("A1", טווח ("A1"). סוף (xlDown)). מפתח מיון 1: = טווח ("A1"), סדר 1: = xlAscending, כותרת: = xl אין סוף קצה
שים לב שבמקום טווח ("A1: A12"), השתמשתי ב- Range ("A1", Range ("A1"). End (xlDown)).
פעולה זו תבדוק את התא האחרון ברציפות בעמודה ויכלול אותו במיון. במקרה שיש ריקים, הוא ישקול רק נתונים עד לתא הריק הראשון.
תוכל גם ליצור טווח עם שם ולהשתמש בטווח שם זה במקום בהפניות לתאים. לדוגמה, אם הטווח בשם הוא DataSet, הקוד שלך יהיה כעת כפי שמוצג להלן.
Sub SortDataWithoutHeader () טווח ("DataRange"). מפתח מיון 1: = טווח ("A1"), סדר 1: = xlAscending, Header: = xl אין סוף קצה
כעת הרשה לי להסביר במהירות את הפרמטרים המשמשים בדוגמאות שלעיל:
- מפתח 1: = טווח ("A1") - ציין A1 כך שהקוד ידע איזו עמודה למיין.
- סדר 1: = xlAscending - ציין את ההזמנה כ- xlAscending. אם אתה רוצה שזה יהיה בסדר יורד, השתמש ב- xlDescending.
- כותרת: = xlNo - צוין כי אין כותרות. זהו גם ערך ברירת המחדל. אז גם אם תשמיט זאת, הנתונים שלך ימוינו בהתחשב בכך שאין להם כותרות.
תוהים היכן לשים את קוד ה- VBA הזה וכיצד להריץ את המאקרו? קראו את ההדרכה הזו!
מיון טור יחיד עם כותרת
בדוגמה הקודמת, למערך הנתונים לא היה כותרת.
כאשר לנתונים שלך יש כותרות, עליך לציין זאת בקוד כך שהמיון יוכל להתחיל מהשורה השנייה של מערך הנתונים.
נניח שיש לך מערך נתונים כפי שמוצג להלן:
להלן הקוד שימיין את הנתונים בסדר יורד על סמך מכירות החנויות.
תת SortDataWithHeader () טווח ("DataRange"). מפתח מיון 1: = טווח ("C1"), סדר 1: = xl
שים לב שיצרתי טווח בשם - 'DataRange', והשתמשתי בטווח שם זה בקוד.
מיון עמודות מרובות בעזרת כותרות
עד כה במדריך זה, ראינו כיצד למיין עמודה אחת (עם ובלי כותרות).
עכשיו, מה אם אתה רוצה למיין על פי מספר עמודות.
לדוגמה, במערך הנתונים שלהלן, מה אם אני רוצה למיין תחילה לפי קוד המדינה ולאחר מכן לפי החנות.
הנה הקוד שימיין מספר עמודות בבת אחת.
תת SortMultipleColumns () עם ActiveSheet.Sort .SortFields.Add Key: = טווח ("A1"), סדר: = xlAscending .SortFields.Add Key: = טווח ("B1"), סדר: = xlAscending .SetRange טווח ("A1 : C13 ") .Header = xlYes. החל על סוף עם קצה משנה
להלן התוצאה שתקבל.
בדוגמה שלמעלה, הנתונים ממוינים לראשונה לפי קוד המדינה (עמודה A). לאחר מכן בתוך נתוני קוד המדינה, הם ממוינים שוב לפי החנות (עמודה B). סדר זה נקבע על פי הקוד בו אתה מציין אותו.
מיון נתונים באמצעות לחיצה כפולה על כותרת עליונה
אם אתה יוצר לוח מחוונים או רוצה יותר קלות שימוש בדוחות שלך, תוכל לכתוב קוד VBA שימיין את הנתונים בעת לחיצה כפולה על הכותרות.
משהו כפי שמוצג להלן:
להלן הקוד שיאפשר לך לעשות זאת:
תת פרטי גליון עבודה_בפני דאבלקליק (יעד ByVal כטווח, בטל כבולאני) Dim KeyRange As range Dim ColumnCount As Integer ColumnCount = Range ("DataRange"). Columns.Count Cancel = False if Target.Row = 1 And Target.Column <= ColumnCount Then ביטול = True Set KeyRange = טווח (Target.Address) טווח ("DataRange"). מיון Key1: = KeyRange, Header: = xlYes End If End Sub
שים לב שיצרתי טווח בשם ("DataRange") והשתמשתי בו בקוד במקום להשתמש בהפניות לתאים.
ברגע שאתה לוחץ פעמיים על כל אחת מהכותרות, הקוד משבית את הפונקציונליות הרגילה של לחיצה כפולה (כלומר להיכנס למצב העריכה) ומשתמש בתא זה כמפתח בעת מיון הנתונים.
שים לב כי נכון לעכשיו, קוד זה ימיין את כל העמודות בסדר עולה בלבד.
שים לב שלחיצה כפולה היא טריגר המאפשרת ל- Excel להריץ את הקוד שצוין. גורמים אלה כגון לחיצה כפולה, פתיחת חוברת עבודה, הוספת גליון עבודה חדש, שינוי תא וכו 'נקראים אירועים וניתן להשתמש בהם להפעלת פקודות מאקרו ב- Excel. תוכל לקרוא עוד על אירועי VBA של Excel כאן.איפה לשים את הקוד הזה?
עליך להדביק קוד זה בחלון הקוד של הגיליון שבו ברצונך להשתמש בפונקציונליות המיון של לחיצה כפולה.
כדי לעשות זאת:
- לחץ לחיצה ימנית על לשונית הגיליון.
- לחץ על הצג קוד.
- הדבק את הקוד בחלון הקוד של הגיליון בו נמצאים הנתונים שלך.
עכשיו מה אם אתה רוצה למיין את שתי העמודות הראשונות ('מדינה' ו'חנות ') בסדר עולה, אבל העמודה' מכירות 'בסדר יורד.
להלן הקוד שיעשה זאת:
תת פרטי גליון עבודה_בפני דאבלקליק (יעד ByVal כטווח, בטל כבולאני) Dim KeyRange As range Dim ColumnCount As Integer ColumnCount = Range ("DataRange"). Columns.Count Cancel = False if Target.Row = 1 And Target.Column <= ColumnCount Then Cancel = True Set KeyRange = Range (Target.Address) If Target.Value = "Sales" ואז SortOrder = xlDescending Else SortOrder = xlAscending End If Range ("DataRange"). מיון מפתח 1: = KeyRange, Header: = xlYes, Order1: = SortOrder End If End Sub
בקוד לעיל, הוא בודק אם התא שלחיצה כפולה הוא כותרת המכירות או לא. אם כן, אז הוא מקצה את ערך xlDescending למשתנה SortOrder, אחרת הוא הופך אותו ל- xlAscending.
עכשיו בואו ניקח את זה צעד קדימה ונראה סמן ויזואלי (חץ ותא צבעוני) בכותרת כשהוא ממוין.
משהו כפי שמוצג להלן:
כדי להשיג זאת, הוספתי דף עבודה חדש וביצעתי בו את השינויים הבאים (אתה יכול להוריד את הקובץ לדוגמא ולעקוב אחריו):
- שינה את שם הגיליון החדש ל- 'BackEnd'.
- בתא B2, הזן סמל חץ (לשם כך, עבור אל הכנס ולחץ על האפשרות 'סמל').
- העתק והדבק את הכותרות ממערך הנתונים לתא A3: C3 בגיליון 'backend'.
- השתמש בפונקציה הבאה בתא A4: AC4:
= IF (A3 = $ C $ 1, A3 & "" & $ B $ 1, A3)
- שאר התאים יתמלאו אוטומטית על ידי קוד VBA כאשר תלחץ לחיצה כפולה על הכותרות למיון העמודה.
הגיליון האחורי שלך ייראה משהו כפי שמוצג להלן:
כעת תוכל להשתמש בקוד שלהלן כדי למיין את הנתונים על ידי לחיצה כפולה על הכותרות. כאשר אתה לוחץ פעמיים על כותרת, הוא יקבל אוטומטית את החץ בטקסט הכותרת. שים לב שגם השתמשתי בעיצוב מותנה כדי להדגיש את התא.
תת פרטי גליון עבודה_בפני דאבלקליק (יעד ByVal כטווח, בטל כבולאני) Dim KeyRange As range Dim ColumnCount As Integer ColumnCount = Range ("DataRange"). Columns.Count Cancel = False if Target.Row = 1 And Target.Column <= ColumnCount Then ביטול = גליונות עבודה (Trueend) (טווח ("C1")) = Target.Value Set KeyRange = Range (Target.Address) טווח ("DataRange"). מיון מפתח 1: = KeyRange, Header: = xlYes גליונות עבודה ("BackEnd "). טווח (" A1 ") = Target.Column עבור i = 1 לטווח ColumnCount (" DataRange "). תאים (1, i) .Value = גליונות עבודה (" גב "). טווח (" A4 "). קיזוז (0, i - 1). הערך הבא i סוף אם סוף משנה
שים לב שקוד זה פועל היטב עבור אופן בניית הנתונים וחוברת העבודה שלי. אם תשנה את מבנה הנתונים, יהיה עליך לשנות את הקוד בהתאם.
הורד את קובץ הדוגמה
