הפוך נתונים ב- Excel - סדר נתונים הפוך בעמודה/בשורה

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

עכשיו, אם אתה חושב שחייבת להיות תכונה מובנית כדי לעשות זאת ב- Excel, אני חושש שתתאכזב.

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

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

אז בואו נתחיל!

הפוך נתונים באמצעות SORT ועמוד עוזר

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

הפוך הנתונים בצורה אנכית (סדר הפוך הפוך)

נניח שיש לך מערך נתונים של שמות בעמודה כפי שמוצג להלן וברצונך להפוך את הנתונים האלה:

להלן השלבים להפיכת הנתונים בצורה אנכית:

1. בעמודה הסמוכה, הזן 'עוזר' ככותרת לעמודה
2. בעמודה עוזר, הזן סדרת מספרים (1,2,3 וכן הלאה). אתה יכול להשתמש בשיטות המוצגות כאן כדי לעשות זאת במהירות
3. בחר את מערך הנתונים כולו כולל עמודת העזר
4. לחץ על הכרטיסייה נתונים
5. לחץ על סמל המיון
6. בתיבת הדו -שיח מיין, בחר 'עוזר' בתפריט הנפתח 'מיין לפי'
7. בתפריט הנפתח הזמנה, בחר 'מהגדול עד הקטן ביותר'
8. לחץ על אישור

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

לאחר שתסיים, אל תהסס למחוק את עמודת העזר.

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

הפוך את הנתונים בצורה אופקית

תוכל גם לעקוב אחר אותה מתודולוגיה כדי להפוך את הנתונים לרוחב ב- Excel.

ל- Excel יש אפשרות למיין את הנתונים בצורה אופקית באמצעות תיבת הדו -שיח מיין (התכונה 'מיין שמאל לימין').

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

להלן השלבים לביצוע פעולה זו:

1. בשורה למטה, הזן 'עוזר' ככותרת לשורה
2. בשורת העוזרים הזן סדרת מספרים (1,2,3 וכן הלאה).
3. בחר את מערך הנתונים כולו כולל שורת העוזרים
4. לחץ על הכרטיסייה נתונים
5. לחץ על סמל המיון
6. בתיבת הדו -שיח מיין, לחץ על הלחצן אפשרויות.
7. בתיבת הדו -שיח שנפתחת, לחץ על 'מיין משמאל לימין'
8. לחץ על אישור
9. בתפריט הנפתח מיון לפי, בחר בשורה 3 (או בשורה כלשהי שבה יש את עמודת העזר שלך)
10. בתפריט הנפתח הזמנה, בחר 'מהגדול עד הקטן ביותר'
11. לחץ על אישור

השלבים שלעיל היו הופכים את כל הטבלה לרוחב.

לאחר שתסיים, תוכל להסיר את שורת העוזרים.

הפוך נתונים באמצעות נוסחאות

ל- Microsoft 365 יש כמה נוסחאות חדשות שמקלות מאוד על הפיכת סדר העמודה או הטבלה ב- Excel.

בחלק זה, אראה לך כיצד לעשות זאת באמצעות נוסחת SORTBY (אם אתה משתמש ב- Microsoft 365), או בנוסחת INDEX (אם אינך משתמש ב- Microsoft 365)

שימוש בפונקציית SORTBY (זמין ב- Microsoft 365)

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

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

כעת, השתמש בנוסחה הבאה מתחת לתא בכותרת השמאלית ביותר:

= SORTBY ($ A $ 2: $ B $ 12, שורה (A2: A12),-1)

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

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

ומכיוון שהטיעון השלישי של נוסחה זו הוא -1, הוא יאלץ את הנוסחה למיין את הנתונים בסדר יורד.

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

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

שימוש בפונקציית INDEX

אם אין לך גישה לפונקציית SORTBY, אל תדאג - אתה יכול להשתמש בפונקציית INDEX המדהימה.

נניח שיש לך מערך שמות כפי שמוצג להלן ואתה רוצה להפוך את הנתונים האלה.

להלן הנוסחה לביצוע פעולה זו:

= INDEX ($ A $ 2: $ A $ 12, ROWS (A2: $ A $ 12))

כיצד פועלת נוסחה זו?

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

הקסם האמיתי קורה בטיעון השני שבו השתמשתי בפונקציה ROWS.

מכיוון שנעולתי את החלק השני של ההפניה בפונקציה ROWS, בתא הראשון, הוא יחזיר את מספר השורות בין A2 ל- A12, שיהיו 11.

אבל כשהוא יורד מהשורות, ההתייחסות הראשונה הייתה משתנה ל- A3, ואז ל- A4 וכן הלאה, בעוד שההתייחסות השנייה תישאר כפי שהיא מכיוון שנעצתי אותה והפכתי אותה למוחלטת.

ככל שאנו יורדים בשורות, התוצאה של הפונקציה ROWS תרד ב -1, מ -11 ל -10 ל -9 וכן הלאה.

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

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

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

להלן הנוסחה שתעשה זאת עבורך:

= INDEX ($ A $ 2: $ B $ 12, ROWS (A2: $ A $ 12), COLUMNS ($ A $ 2: A2))

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

כדי להפוך את הנוסחה לדינאמית, השתמשתי בפונקציה COLUMNS שתמשיך לשנות את ערך העמודה מ -1 ל -2 עד 3 תוך העתקתו ימינה.

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

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

הפוך נתונים באמצעות VBA

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

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

תוכל גם לשמור את הקוד בחוברת העבודה המאקרו האישית או כתוסף Excel ולהיות מסוגל להשתמש בו בכל חוברת עבודה במערכת שלך.

להלן קוד VBA שיהפוך את הנתונים שנבחרו אנכית בגליון העבודה.

Sub FlipVerically () 'Code by Sumit Bansal from TrumpExcel.com Dim TopRow as variant Dim LastRow as variant Dim StartNum as Integer Dim EndNum as Integer Application.ScreenUpdating = False StartNum = 1 EndNum = Selection.Rows.Count Do While StartNum <EndNum TopRow = Selection.Rows (StartNum) LastRow = Selection.Rows (EndNum) Selection.Rows (EndNum) = TopRow Selection.Rows (StartNum) = LastRow StartNum = StartNum + 1 EndNum = EndNum - יישום לולאה אחד. ScreenUpdating = Sub End True

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

איך הקוד הזה עובד?

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

לאחר מכן הוא משתמש בלולאת Do While שבה מיון הנתונים מתרחש.

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

הלולאה מסתיימת כאשר כל המיון מתבצע.

הוא משתמש גם במאפיין Application.ScreenUpdating ומגדיר אותו כ- FALSE בעת הפעלת הקוד ולאחר מכן הופך אותו חזרה ל- TRUE לאחר סיום הפעלת הקוד.

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

כיצד להשתמש בקוד?

בצע את השלבים שלהלן כדי להעתיק ולהדביק את הקוד הזה בעורך VB:

1. פתח את קובץ ה- Excel שבו ברצונך להוסיף את קוד VBA
2. החזק את מקש ALT ולחץ על מקש F-11 (תוכל גם לעבור לכרטיסייה מפתחים וללחוץ על סמל Visual Basic)
3. בעורך Visual Basic שנפתח, יהיה סייר פרויקטים בחלק השמאלי של עורך VBA. אם אינך רואה אותו, לחץ על הכרטיסייה 'תצוגה' ולאחר מכן לחץ על 'סייר הפרויקטים'
4. לחץ לחיצה ימנית על כל אחד מהאובייקטים של חוברת העבודה שבה ברצונך להוסיף את הקוד
5. עבור לאפשרות הוספה ולאחר מכן לחץ על מודול. פעולה זו תוסיף מודול חדש לחוברת העבודה
6. לחץ פעמיים על סמל המודול ב- Explorer Explorer. פעולה זו תפתח את חלון הקוד לאותו מודול
7. העתק והדבק את קוד ה- VBA לעיל בחלון הקוד

כדי להריץ את קוד המאקרו VBA, תחילה בחר את מערך הנתונים שברצונך להפוך (לא כולל הכותרות).

כאשר הנתונים נבחרים, עבור אל עורך VB ולחץ על כפתור ההפעלה הירוק בסרגל הכלים, או בחר כל שורה בקוד ולאחר מכן לחץ על מקש F5

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

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

אני מקווה שמצאת הדרכה שימושית.