פונקציית טרנספוזיציה באקסל

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

פונקציית TRANSPOSE - העברה של טווחי תאים באקסל

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

תחביר פונקציה

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

העברת טווחים אנכיים של תאים (עמודות)

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

רצף השלבים לשימוש בנוסחה זו הוא כדלקמן:

1. בחר שורה. במקרה שלנו, יש לו אורך של חמישה תאים.
2. לאחר מכן, העבר את הסמן לשורת הנוסחאות והזן את הנוסחה שם =TRANSP(B2:B6).
3. הקש על צירוף המקשים Ctrl + Shift + Enter.

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

העברת טווחי תאים אופקיים (שורות)

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

1. בחר עמודה זו עם העכבר. אתה יכול גם להשתמש במקשי המקלדת Ctrl ובחץ למטה, לאחר לחיצה על התא העליון ביותר של עמודה זו.
2. אחרי זה נכתוב את הנוסחה =TRANSP(B10:F10)‎ בשורת הנוסחאות.
3. אנו כותבים את זה כנוסחת מערך באמצעות צירוף המקשים Ctrl + Shift + Enter.

טרנספוזיציה עם Paste Special

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

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

3 דרכים להעביר טבלה באקסל

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

שיטה 1: הדבק מיוחד

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

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

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

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

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

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

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

שיטה 2. פונקציית TRANSP באקסל

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

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

1. ראשית, עלינו לבחור את טווח הנתונים שישמש להמרת הטבלה. אתה רק צריך לבחור את האזור להיפך. לדוגמה, בדוגמה זו יש לנו 4 עמודות ו-6 שורות. לכן, יש צורך לבחור אזור בעל מאפיינים מנוגדים: 6 עמודות ו-4 שורות. התמונה מראה את זה טוב מאוד.
2. לאחר מכן, אנו מיד מתחילים למלא את התא הזה. חשוב לא להסיר בטעות את הבחירה. לכן, עליך לציין את הנוסחה ישירות בשורת הנוסחאות.
3. לאחר מכן, הקש על צירוף המקשים Ctrl + Shift + Enter. זכרו שמדובר בנוסחת מערך, מכיוון שאנו עובדים עם קבוצת נתונים גדולה בבת אחת, אשר תועבר לסט גדול אחר של תאים.

לאחר שנזין את הנתונים, נלחץ על מקש Enter ולאחר מכן נקבל את התוצאה הבאה.

אנו רואים שהנוסחה לא הועברה לטבלה החדשה. גם העיצוב אבד. פוטו

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

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

טבלת סיכום

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

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

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

נכון, בעת שימוש בטבלאות ציר, יש לקחת בחשבון את ההגבלות הבאות:

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

כמו כן, לאחר יצירת טבלת ציר, למשתמש אין את היכולת להוסיף מידע חדש.