הפוך את הנתונים באקסל (הפוך את סדר הנתונים בעמודות/שורות)

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

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

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

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

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

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

אחת הדרכים הקלות ביותר להפוך את סדר הנתונים ב-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. לחץ על אישור

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

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

השתמש ב-Excel כדי להפוך את הנוסחאותהפוך נתונים

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

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

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

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

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

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

=SORTBY($A$2:$B$12,ROW(A2:A12),-1)

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

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

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

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

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

השתמש בפונקציית אינדקס

אם אינכם יכולים להשתמש בפונקציית 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()

עמום TopRow כגרסה
עמום Last Row כווריאציה
עמום StartNum כמספר שלם
עמום EndNum כמספר שלם

Application.ScreenUpdating = False

StartNum = 1
EndNum = בחירה. שורות. ספירה

עשה בזמן StartNum < EndNum
TopRow = בחירה. Rows(StartNum)
LastRow = בחירה. Rows(EndNum)
Selection.Rows(EndNum) = TopRow
Selection.Rows(StartNum) = LastRow
StartNum = StartNum + 1
EndNum = EndNum - 1
לולאה

Application.ScreenUpdating = True
End Sub

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

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

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

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

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

הלולאה מסתיימת כאשר כל המיון הושלם.

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

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

איך משתמשים בקוד?

העתק והדבק קוד זה בעורך VB באופן הבא:

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

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

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

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

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

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