Excelдеги саптарды мамычаларга кантип айландырса болот

Бул колдонмо Excelде саптарды мамычаларга айландыруу үчүн эмне кылуу керектигин түшүндүрөт. 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. Натыйжадагы таблицаны көчүрүп, анан аны которуу үчүн атайын Paste колдонуңуз.

Андан кийин, сиз жөн гана "Тап жана алмаштыруу" диалогун кайра ачып, анын жардамы менен уячалардагы "xxx" ды "=" кылып өзгөртүү үчүн бардык уячалар баштапкы маалымат менен байланышкан.Албетте, бул татаал жана узак, бирок бул ыкма баштапкы таблицага шилтеменин жоктугун айланып өтүүгө мүмкүндүк берет. Бирок бул ыкманын да кемчилиги бар. Бул форматтоону өз алдынча улантуу зарылдыгы менен түшүндүрүлөт.

Формулаларды колдонуу

Саптарды жана мамычаларды ийкемдүү өзгөртүүгө мүмкүндүк берген эки функция бар: ТРАНСП и ДВССЫЛ. Здесь также есть возможность сохранить связь с первоначальной таблиц, но механика работы несколько иная.

TRANSP функциясы

Чынында, бул формула электрондук жадыбалды түздөн-түз көчүрөт. Синтаксис төмөнкүдөй:

=TRANSP(массив)

Эми биз аны айрым штаттардын калкы жөнүндө маалыматты камтыган таблицага колдонууга аракет кылабыз.

1. Таблицадагы мамычалардын жана саптардын санын эсептеп, барактан бирдей өлчөмдөгү бош мейкиндикти табыңыз.
2. F2 баскычын басуу менен түзөтүү режимин баштаңыз.
3. Функция жаз ТРАНСП кашаанын ичиндеги маалыматтар диапазону менен. Бул функцияны колдонууда дайыма абсолюттук шилтемелерди колдонуу маанилүү.
4. Ctrl+Shift+Enter баскычтарын басыңыз. Маанилүү басуу так баскычтар айкалышы, антпесе формула баш тартат иштөө.

Болду, эми жыйынтык ушундай!

Бул формуланын артыкчылыгы мурунку ыкмадагыдай эле («тап жана алмаштыруу» функциясын колдонуу менен) негизги маалымат өзгөргөндө, которулган таблица автоматтык түрдө жаңыланат.

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

1. "Тап жана алмаштыруу" ыкмасындагыдай форматтоо сакталбайт.
2. Баштапкы таблица кээ бир маалыматтарды камтышы керек, антпесе кээ бир уячаларда нөлдөр болот.
3. Излишняя зависимость от источника данных. То есть, бул метод имеет противоположный по недостаток – невозможность изменять транспонированный таблицу. Эсли попытаться это сделать, программа скажет, что невозможно редактировать часть массив.

Ошондой эле, бул функцияны ар дайым ийкемдүү түрдө ар кандай кырдаалдарда колдонуу мүмкүн эмес, андыктан бул жөнүндө билишиңиз керек, бирок натыйжалуураак ыкмаларды колдонуңуз.

КЫЙЫР Формула колдонуу

Бул ыкманын механикасы формуланы колдонууга абдан окшош ТРАНСП, бирок при этом его использование чечет проблему невозможности редактировать транспонированную таблицу без потери связи с основной информацией.

Но одной формула КЫЙЫР жетишсиз: сиз дагы эле функцияны колдонушуңуз керек ADDRESS. Бул мисалда сизге көп керексиз маалымат жүктөлбөш үчүн чоң стол болбойт.

Демек, сизде 4 мамыча жана 5 саптан турган ушундай таблица бар дейли.

Төмөнкү чараларды көрүү зарыл:

1. Бул формуланы киргизиңиз: =КЫЙЫР(ДАРЕК(КАМЫНА(A1),КАТ(A1))) жаңы таблицанын жогорку сол уячасына (биздин мисалда бул A7) жана enter баскычын басыңыз. Эгерде маалымат биринчи сапта же биринчи тилкеде башталбаса, анда татаалыраак формуланы колдонууга туура келет: =ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1)-СТОЛБЕЦ($A$1)+СТРОКА($A$1);СТРОКА(A1)-СТРОКА($A$1)+СТОЛБЕЦ($A$1))). Бул формулада A1 таблицанын жогорку уячасын билдирет, анын негизинде көчүрүлгөн клетка түзүлөт.
2. Формула жадыбалдын жаңы версиясы жайгаштырыла турган бардык аймакка жайылтылат. Бул үчүн, биринчи уячанын төмөнкү оң бурчундагы маркерди болочок таблицанын карама-каршы четине сүйрөңүз.
3. Баары! Таблица ийгиликтүү которулду жана сиз аны дагы эле түзөтө аласыз. Албетте, анын сырткы көрүнүшү көп нерсени талап кылат, бирок аны оңдоо кыйын эмес. Туура форматтоону калыбына келтирүү үчүн, биз көчүргөн таблицаны (башкача айтканда, түпнускасын) көчүрүп алышыңыз керек, андан кийин жаңы түзүлгөн таблицаны тандаңыз. Андан кийин, тандалган диапазонду оң баскыч менен чыкылдатып, анан чаптоо параметрлеринде "Форматтоо" баскычын чыкылдатыңыз.

Ошентип, функцияны колдонуу КЫЙЫР жыйынтыктоочу таблицадагы ар кандай маанини түзөтүүгө мүмкүндүк берет жана булак таблицасында кандайдыр бир өзгөртүүлөр киргизилгенден кийин маалыматтар ар дайым жаңыланып турат.

Албетте, бул ыкма кыйыныраак, бирок иш жүзүндө аны иштеп чыгуу, анда баары ушунчалык коркунучтуу эмес.

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

КЫЙЫР жана ДАРЕК формулаларынын айкалышы кандай иштейт?

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

милдети КЫЙЫР биздин формулада кыйыр клетка шилтемесин түзүү үчүн колдонулат. Мисалы, A8 уячасында B1дегидей маанини көрсөтүү керек болсо, анда формуланы жаза аласыз

=КЫЙЫР("B1")

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

Beyond Function ADDRESS формулада да колдонулат колонна и LINE. Биринчиси уячанын дарегин мурунтан эле белгилүү болгон сап жана мамыча номерлеринин негизинде кайтарат. Бул жерде туура ырааттуулукту сактоо маанилүү. Алгач сап, андан кийин гана тилке көрсөтүлөт. Мисалы, функция ДАРЕК(4;2) $B$2 дарегин кайтарат.

Следующая используемая выше функция – бул колонна. Бул жерде формула белгилүү бир шилтемеден мамычанын номерин алышы үчүн зарыл. Мисалы, эгерде сиз бул функциянын кашааларында B2 параметрин колдонсоңуз, анда ал 2 санын кайтарат, анткени экинчи тилке В тилкеси.

Албетте, ROW функциясы дал ушундай иштейт, ал жөн гана катардын номерин кайтарат. 

Эми биз абстрактуу мисалдарды эмес, жогоруда колдонулган өзгөчө формуланы колдонобуз:

=КЫЙЫР(ДАРЕК(КАМЫНА(A1),КАТ(A1)))

Бул жерде сиз муну биринчи функциядан көрө аласыз ADDRESS тилке көрсөтүлөт, андан кийин гана сызык. Ал эми бул формуланын иштөө жөндөмдүүлүгүнүн сыры дал ушул жерде катылган. Бул функция күзгү түрүндө иштей турганын жана андагы биринчи аргумент саптын номери экенин унутпайбыз. Анан ал жерге тилкенин дарегин жазганда саптын номерине жана тескерисинче болуп калат экен.

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

1. Тиешелүү функцияларды колдонуу менен мамыча жана сап номерлерин алабыз.
2. Функцияны колдонуу ADDRESS саптар мамычага айланат жана тескерисинче.
3. милдети КЫЙЫР уячада чагылдырылган маалыматтарды көрсөтүүгө жардам берет.

Мунун баары ушунчалык жөнөкөй экен!

Көчүрүү үчүн макросту колдонуу

Макро бул кичинекей программа. Ал процессти автоматташтыруу үчүн колдонулушу мүмкүн. Бул макро кээ бир чектөөлөр бар экенин эске алуу маанилүү. Максималдуу Transpose ыкмасы 65536 элемент менен иштөөгө мүмкүндүк берет. Бул чектен ашып кетсе, маалымат жоголушуна алып келет.

Бул бардык негизги, бул эффективдүү методдорду автоматташтыруу, бардык нерсенин маанилүүлүгүн аныктоо.

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

Sub TransposeColumnsRows()

    Dim SourceRange диапазон катары

    Диапазон катары Dim DestRange

    SourceRange коюңуз = Application.InputBox(Prompt:=»Которуу үчүн диапазонду тандаңыз», Аталышы:=»Сатарларды мамычаларга которуу», Түрү:=8)

    DestRange коюңуз = Application.InputBox(Prompt:=»Бара турган диапазондун жогорку сол уячасын тандаңыз», Аталышы:=»Сатарларды мамычаларга которуу», Түрү:=8)

    SourceRange.Copy

    DestRange.Select

    Selection.PasteSpecial Paste:=xlPasteAll, Операция:=xlNone, SkipBlanks:=False, Transpose:=True

    Application.CutCopyMode = False

End Sub

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