כיצד להמיר שורות לעמודות באקסל

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

Эта детальная инструкция расскажет о нескольких способах транспонирования Excel-таблицы, а также о чаш которыми может столкнуться пользователь. Все они могут использоваться на любой версии Excel, как очень старой, так и самой новой.

שימוש בתכונת הדבק מיוחד

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

כדי להחליף שורות ועמודות, בצע את הפעולות הבאות:

  1. בחר את הטבלה המקורית. אם אתה רוצה לראות את כל הטבלה בבת אחת (אם היא גדולה מאוד), אתה צריך ללחוץ על צירוף המקשים Ctrl + Home, ולאחר מכן - Ctrl + Shift + End.
  2. לאחר מכן התאים מועתקים. ניתן לעשות זאת דרך תפריט ההקשר או על ידי לחיצה על שילוב המקשים Ctrl+C. מומלץ להרגיל את עצמך מיד לאפשרות האחרונה, כי אם תלמדו מקשים חמים, תוכלו לבצע משימות רבות ממש בשנייה אחת.
  3. בחר את התא הראשון בטווח היעד. При этом он должен находиться за пределами таблицы. Также необходимо подобрать ячейку таким образом, чтобы таблица после транспонирования не переськан. לדוגמה, אם הטבלה הראשונית כוללת 4 עמודות ו-10 שורות, אז לאחר ביצוע כל הפעולות הללו, היא תתפשט 10 תאים למטה ו-4 תאים הצידה. לכן, בטווח הזה (הספירה מתא המטרה) לא אמור להיות כלום.
  4. בתא היעד, עליך ללחוץ באמצעות לחצן העכבר הימני ולבחור "הדבק מיוחד", ולאחר מכן לסמן את התיבה לצד הכיתוב "העברה".כיצד להמיר שורות לעמודות באקסל

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

Огромное преимущество опции «Специальная вставка» ו заключается в возможности транспонировать таблик. И при этом полностью сохраняется форматирование, что также позволяет сэкономить кучу времени.

Несмотря на эти явные плюсы, есть и ряд серьезных недостатков, которые мешают этому методи назывн:

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

כיצד להחליף שורות ועמודות ולקשר אותם לטבלה הראשית?

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

  1. Скопировать таблицу, для которой требуется транспонирование.
  2. בחר תא ללא נתונים באזור ריק בטבלה.
  3. הפעל את תפריט הדבק מיוחד, בדומה לדוגמה הקודמת. לאחר מכן, עליך ללחוץ על כפתור "הוסף קישור", אותו ניתן למצוא בפינה השמאלית התחתונה.כיצד להמיר שורות לעמודות באקסל
  4. התוצאה תהיה הבאה.כיצד להמיר שורות לעמודות באקסל
  5. Выбрать новую таблицу и запустить окно «Найти и заменить» путем нажатия комбинации клавиш Ctrl + H.
  6. Заменить все знаки ввода формулы (=) на ххх (אולי любую другую комбинацию знаков, которой нетинав).כיצד להמיר שורות לעמודות באקסל
  7. כתוצאה מכך יתברר משהו נורא, אבל זהו תנאי ביניים חובה להשגת התוצאה הרצויה. ואז הכל ייראה יפה.כיצד להמיר שורות לעמודות באקסל
  8. העתק את הטבלה שהתקבלה ולאחר מכן השתמש בהדבקה מיוחדת כדי להעביר אותה.

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

יישום נוסחאות

ישנן שתי פונקציות המאפשרות לך לשנות בצורה גמישה שורות ועמודות: ТРАНСП и ДВССЫЛ. Здесь также есть возможность сохранить связь с первоначальной таблицей, но механика работы неиналько.

פונקציה TRANSP

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

=TRANSP(מערך)

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

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

זהו, עכשיו התוצאה נראית כך!כיצד להמיר שורות לעמודות באקסל

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

Но недостатки у нее тоже имеются:

  1. עיצוב, כמו במקרה של שיטת Find and Replace, לא יישמר.
  2. הטבלה המקורית חייבת להכיל כמה נתונים, אחרת יהיו אפסים בחלק מהתאים.
  3. Излишняя зависимость от источника данных. То есть, этот метод имеет противоположный по недостаток – невозможность изменять транспонированниц транспонированниц. Если попытаться это сделать, программа скажет, что невозможно редактировать часть массива.

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

שימוש בנוסחה INDIRECT

המכניקה של שיטה זו דומה מאוד לשימוש בנוסחה TRANSP.

Но одной формулы עקיף לא מספיק: אתה עדיין צריך להשתמש בפונקציה כתובת. לא תהיה טבלה גדולה בדוגמה הזו, כדי לא להעמיס עליך הרבה מידע מיותר.

אז, נניח שיש לך טבלה כזו, המורכבת מ-4 עמודות ו-5 שורות.כיצד להמיר שורות לעמודות באקסל

יש לבצע את הפעולות הבאות:

  1. הזן את הנוסחה הזו: =INDIRECT(ADDRESS(COLUMN(A1),ROW(A1))) לתוך התא השמאלי העליון של הטבלה החדשה (בדוגמה שלנו זה A7) והקש על מקש Enter. אם המידע לא מתחיל בשורה הראשונה או בעמודה הראשונה, תצטרך להשתמש בנוסחה מורכבת יותר: =ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1)-СТОЛБЕЦ($A$1)+СТРОКА($A$1);СТРОКА(A1)-СТРОКА($A$1)+СТОЛБЕЦ($A$1))). בנוסחה זו, A1 פירושו התא העליון של הטבלה, שעל בסיסו יווצר התא המועבר.
  2. הרחב את הנוסחה לכל האזור שבו תוצב הגרסה החדשה של הטבלה. כדי לעשות זאת, גרור את הסמן בפינה הימנית התחתונה של התא הראשון לקצה הנגדי של הטבלה העתידית.כיצד להמיר שורות לעמודות באקסל
  3. את כל! התברר שהטבלה הועברה בהצלחה, ועדיין תוכל לערוך אותה. כמובן, המראה שלה משאיר הרבה כדי להיות הרצוי, אבל לתקן את זה לא קשה.כיצד להמיר שורות לעמודות באקסל כדי לשחזר את העיצוב הנכון, עליך להעתיק את הטבלה שהעברנו (כלומר, את המקורית), ולאחר מכן לבחור את הטבלה החדשה שנוצרה. לאחר מכן, לחץ לחיצה ימנית על הטווח שנבחר ולאחר מכן לחץ על "עיצוב" באפשרויות ההדבקה.כיצד להמיר שורות לעמודות באקסל

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

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

Этот метод один из самых лучших, потому что несмотря на то, что не сохраняется форматирование, что не сохраняется форматирование.

כיצד פועל השילוב של נוסחאות INDIRECT ו-ADDRESS?

После того, как вы разобрались в том, как использовать совокупность этих формул для транспонирования танспонирования,м хочется более глубоко изучить принцип работы этого метода.

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

=INDIRECT("B1")כיצד להמיר שורות לעמודות באקסל

Казалось бы, зачем это делать? Ведь можно просто написать ссылку на ячейку в другой ячейке. Но преимущество этой функции в том, что в ссылку можно превратить абсолютно любую строку, и даже ту, которая создается с использованием других формул. Собственно, это мы и делаем в FORMуле.

מעבר לפונקציה כתובת משמש גם בנוסחה COLUMN и קו. הראשון מחזיר את כתובת התא בהתבסס על מספרי השורות והעמודות הידועים כבר. חשוב לעקוב אחר הרצף הנכון כאן. תחילה מציינים את השורה, ולאחר מכן רק את העמודה. למשל, הפונקציה ADDRESS(4;2) יחזיר את הכתובת $B$2.

Следующая используемая выше функция – это COLUMN. זה הכרחי כאן כדי שהנוסחה תקבל את מספר העמודה מהפניה ספציפית. לדוגמה, אם אתה משתמש בפרמטר B2 בסוגריים של פונקציה זו, הוא יחזיר את המספר 2, מכיוון שהעמודה השנייה היא עמודה B.

ברור שפונקציית ROW פועלת באותו אופן, היא רק מחזירה את מספר השורה. 

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

=INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))

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

То есть, если подытожить:

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

ככה הכל מתברר!

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

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

Во всем остальном, это эффективный метод автоматизации, который сможет значительно облегчизить ж.

Например, можно написать такой код, который будет менять местами строки и колонки.

Sub TransposeColumnsRows()

    עמום טווח מקור כטווח

    עמום DestRange As Range

    הגדר SourceRange = Application.InputBox(Prompt:=»אנא בחר את הטווח להעברה», כותרת:=»העברה של שורות לעמודות», סוג:=8)

    הגדר DestRange = Application.InputBox(Prompt:=»בחר את התא השמאלי העליון של טווח היעד», כותרת:=»העברה של שורות לעמודות», סוג:=8)

    SourceRange.Copy

    DestRange.Select

    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

    Application.CutCopyMode = False

End Sub

Но если знаний в программировании особо нет, ничего страшного. Можно воспользоваться описанными выше способами. А потом учиться новому по мере освоения старого.

השאירו תגובה