Excel-da satrlarni ustunlarga qanday aylantirish mumkin

Ushbu qo'llanma Excelda satrlarni ustunlarga aylantirish uchun nima qilish kerakligini tushuntiradi. Excel-dan foydalanganda ko'pincha jadvaldagi satrlar va ustunlarni almashtirishga to'g'ri keladi. Masalan, odam katta stol yasagan va keyin uni o'girib qo'ysangiz, uni o'qish osonroq bo'lishini tushungan. 

Eta batafsil ko'rsatma rasskajet o neskolkyx sposobah transponirovaniya Excel-tablitsy, va yana bir chasto vstrechaemyx oshibkax, s kotorym mumkin stolknutsya polzovatel. Barchasini lyuboy versiyasi Excel, qanday ochen Staroy, tak va samoy novoyda qo'llash mumkin.

Maxsus joylashtirish xususiyatidan foydalanish

Aytaylik, sizda ikkita ma'lumotlar to'plami bor. Ikkinchisi, siz nima bilan yakunlashni xohlaysiz. Birinchisi, ko'chirilishi kerak bo'lgan jadval. Birinchi versiyada mamlakatlar nomlari turli ustunlarda keltirilgan bo‘lib, o‘qish, undan ham ko‘proq, davlatlar xususiyatlarini bir-biri bilan solishtirish juda noqulay. Shuning uchun, jadvalni mamlakat nomlari turli ustunlarda ko'rsatilishi uchun tashkil qilish yaxshiroqdir.

Qator va ustunlarni almashtirish uchun quyidagilarni bajaring:

1. Asl jadvalni tanlang. Agar siz bir vaqtning o'zida butun jadvalni ko'rishni istasangiz (agar u juda katta bo'lsa), Ctrl + Home tugmalar birikmasini, keyin esa - Ctrl + Shift + End tugmalarini bosishingiz kerak.
2. Keyin hujayralar nusxalanadi. Buni kontekst menyusi orqali yoki Ctrl+C tugmalar birikmasini bosish orqali amalga oshirish mumkin. Darhol oxirgi variantga o'rganish tavsiya etiladi, chunki agar siz tezkor tugmachalarni o'rgansangiz, bir soniyada ko'plab vazifalarni bajarishingiz mumkin.
3. Maqsadli diapazondagi birinchi katakchani tanlang. Pri etom on dolzhen nahoditsya za predelami tablitsy. Takje neobxodimo podobrat yacheyku takim obrazom, chtoby tablitsa posle transponirovaniya ne pereskalas s drugimi dannymi. Misol uchun, agar boshlang'ich jadvalda 4 ta ustun va 10 qator bo'lsa, u holda ushbu amallarning barchasini bajargandan so'ng, u 10 ta katakchani pastga va 4 ta katakchani yon tomonga yoyadi. Shuning uchun, bu oraliqda (maqsadli hujayradan hisoblash) hech narsa bo'lmasligi kerak.
4. Maqsadli katakda siz sichqonchaning o'ng tugmachasini bosib, "Maxsus joylashtirish" ni tanlashingiz kerak, so'ngra "Transpose" yozuvi yonidagi katakchani belgilang.

muhim: agar manba jadvalida formulalar mavjud bo'lsa, har bir katakda mutlaq havolalar ishlatilishini ta'minlash muhimdir. Bu barcha havolalar avtomatik ravishda yangilanishi uchun bajarilishi kerak.

Ogromnoe preimushchestvo optsii «Spetsialnaya vstavka» va zaklyuchaet v vozmojnosti transfer tablitsu vsego lish za neskolko soniya. I pri etom polnostyu sohranyaetsya formatirovanie, chto ham pozvolyaet sekonomit kuchli vremeni.

Nesmotrya na etu yavnye plyusy, est i ryad sereznyh nedostatkov, kotorye masyutet etoma metodu nazyvatsya universalnym:

1. Uni oddiy qiymatlar oralig'iga tushirilmagan to'liq jadvallarni ko'chirish uchun ishlatish yomon. Bunday holda, "Transpose" funksiyasi o'chiriladi. Ushbu muammoni hal qilish uchun jadvalni diapazonga aylantirishingiz kerak.
2. Bu usul bir martalik transpozitsiya uchun juda mos keladi, chunki u yangi jadvalni asl ma'lumotlar bilan bog'lamaydi. Oddiy so'zlar bilan aytganda, jadvallardan birini o'zgartirsangiz, ikkinchisida ma'lumot avtomatik ravishda yangilanmaydi. Shuning uchun transpozitsiyani takrorlash kerak bo'ladi. 

Qator va ustunlarni qanday almashtirish va ularni asosiy jadvalga ulash mumkin?

Xo'sh, "Maxsus joylashtirish" opsiyasi jadvalni asosiy ma'lumotlar va transpozitsiya natijasida olingan ma'lumotlar bilan bog'lashi uchun nima qilish kerak? Axir, hamma ma'lumotlarning avtomatik yangilanishini xohlaydi.

1. Skopirovat tablitsu, uchun kotoroy trebuetsya transponirovanie.
2. Jadvalning bo'sh joyida ma'lumotlar bo'lmagan katakchani tanlang.
3. Oldingi misolga o'xshash maxsus joylashtirish menyusini ishga tushiring. Shundan so'ng, pastki chap tomonda joylashgan "Havola qo'shish" tugmasini bosishingiz kerak.
4. Natija quyidagicha bo'ladi.
5. "Nayti i zamenit" ni bosing Ctrl + H tugmalarini bosing.
6. Zamenit vse znaki vvoda formulasi (=) na xxx (ili lyubuyu drguyu kombinatsiyu znakov, kotoroy net v originalnoy tablitse).
7. Natijada, dahshatli narsa yuz beradi, ammo bu istalgan natijaga erishish uchun majburiy oraliq shartdir. Shunda hamma narsa chiroyli ko'rinadi.
8. Olingan jadvaldan nusxa ko'chiring va uni ko'chirish uchun Paste Special-dan foydalaning.

Shundan so'ng, siz shunchaki "Topish va almashtirish" dialogini qayta ochishingiz kerak va undan barcha hujayralar asl ma'lumot bilan bog'lanishi uchun hujayralardagi "xxx" ni "=" ga o'zgartirish uchun foydalaning.Albatta, bu yanada murakkab va uzoqroq, ammo bu usul sizga asl jadvalga havola yo'qligidan xalos bo'lishga imkon beradi. Ammo bu yondashuvning kamchiliklari ham bor. Bu formatlashni mustaqil ravishda davom ettirish zarurati bilan ifodalanadi.

Formulalarni qo'llash

Satrlar va ustunlarni moslashuvchan tarzda o'zgartirishga imkon beruvchi ikkita funktsiya mavjud: TRANSP va DVSSLY. Zdes takje est vozmojnost soxranit svyaz s pervonacalnoy tablitsey, no mexanika raboty neskolko ina.

TRANSP funktsiyasi

Aslida, bu formula to'g'ridan-to'g'ri elektron jadvalni ko'chiradi. Sintaksis quyidagicha:

=TRANSP(massiv)

Endi biz uni alohida shtatlarning aholisi haqidagi ma'lumotlarni o'z ichiga olgan jadvalga qo'llashga harakat qilamiz.

1. Jadvaldagi ustunlar va qatorlar sonini hisoblang va varaqda bir xil o'lchamdagi bo'sh joyni toping.
2. F2 tugmachasini bosib tahrirlash rejimini boshlang.
3. Funktsiyani yozing TRANSP qavs ichida ma'lumotlar diapazoni bilan. Ushbu funktsiyadan foydalanganda doimo mutlaq havolalardan foydalanish muhimdir.
4. Ctrl+Shift+Enter tugmalar birikmasini bosing. To'liq tugmalar birikmasini bosish muhim, aks holda formula ishlashdan bosh tortadi.

Mana, endi natija shunday ko'rinadi!

Ushbu formulaning afzalligi avvalgi usul bilan bir xil ("topish va almashtirish" funktsiyasidan foydalanish) shundan iboratki, asosiy ma'lumotlar o'zgarganda, ko'chirilgan jadval avtomatik ravishda yangilanadi.

No nedostatki u nee toje imeyutsya:

1. Topish va almashtirish usulidagi kabi formatlash saqlanmaydi.
2. Asl jadvalda ba'zi ma'lumotlar bo'lishi kerak, aks holda ba'zi kataklarda nollar bo'ladi.
3. Izlishnyaya zavisimost ot isochnika dannyx. To est, bu metod imeet protivopolojnyy po nedostatok – nevmojnost izmenyat transponirovannuyu tablitsu. Esli popytatsya bu sdelat, programma skajet, chto nevozmojno redaktirovat chast massiva.

Bundan tashqari, bu funktsiyani har doim ham turli vaziyatlarda moslashuvchan tarzda qo'llash mumkin emas, shuning uchun siz bu haqda bilishingiz kerak, ammo samaraliroq usullardan foydalaning.

INDIRECT formulasidan foydalanish

Ushbu usulning mexanikasi formuladan foydalanishga juda o'xshaydi TRANSP, lekin pri etom ego ispolzovanie bartaraf muammo yangilash redaktori transponirovannuyu tablitsu bez poteri svyazi s osnovnoy informatsiey.

No odnoy formula BILVOSIT etarli emas: siz hali ham funktsiyadan foydalanishingiz kerak ADDRESS. Ushbu misolda sizni juda ko'p keraksiz ma'lumotlar bilan ortiqcha yuklamaslik uchun katta jadval bo'lmaydi.

Demak, sizda 4 ta ustun va 5 qatordan iborat shunday jadval bor deylik.

Quyidagi harakatlar amalga oshirilishi kerak:

1. Ushbu formulani kiriting: =BILOSHIY(MANZIL(USTUN(A1), QAT(A1))) yangi jadvalning yuqori chap katakchasiga (bizning misolimizda bu A7) va Enter tugmasini bosing. Agar ma'lumot birinchi qatorda yoki birinchi ustunda boshlanmasa, unda siz murakkabroq formuladan foydalanishingiz kerak bo'ladi: =ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1)-СТОЛБЕЦ($A$1)+СТРОКА($A$1);СТРОКА(A1)-СТРОКА($A$1)+СТОЛБЕЦ($A$1))). Ushbu formulada A1 jadvalning yuqori katakchasini bildiradi, uning asosida transpozitsiya qilingan hujayra hosil bo'ladi.
2. Formulani jadvalning yangi versiyasi joylashtiriladigan butun maydonga kengaytiring. Buning uchun birinchi katakning pastki o'ng burchagidagi markerni kelajakdagi jadvalning qarama-qarshi tomoniga torting.
3. Hammasi! Jadval muvaffaqiyatli ko'chirildi va siz uni hali ham tahrirlashingiz mumkin. Albatta, uning tashqi ko'rinishi ko'p narsani orzu qiladi, ammo uni tuzatish qiyin emas. To'g'ri formatlashni tiklash uchun siz biz o'tkazgan jadvalni (ya'ni asl nusxasini) nusxalashingiz kerak, keyin yangi yaratilgan jadvalni tanlang. Keyin tanlangan diapazonni sichqonchaning o'ng tugmasi bilan bosing va joylashtirish parametrlarida "Formatlash" ni bosing.

Shunday qilib, funktsiyadan foydalanish BILVOSIT yakuniy jadvaldagi mutlaqo istalgan qiymatni tahrirlash imkonini beradi va manba jadvaliga har qanday o'zgartirish kiritilishi bilan ma'lumotlar har doim yangilanadi.

Albatta, bu usul qiyinroq, lekin agar siz uni amalda ishlab chiqsangiz, unda hamma narsa unchalik qo'rqinchli emas.

Etot metod odin iz samyh luchshix, potomu chto nesmotrya na to, chto ne sohranyaetsya formatirovanie v novosozdannoy tablitse, mumkin ego legko vosstanovit.

BILOVV va MANZIL formulalarining kombinatsiyasi qanday ishlaydi?

Posle togo, kak vy razobralis v tom, kak ispolzovat sovokupnost etix formula uchun transponirovaniya tablitsy, vam, vozmojno, zahochetsya bolee gluboko izuchit printsipi raboty bu metoda.

vazifa BILVOSIT formulamizda bilvosita hujayra havolasini yaratish uchun ishlatiladi. Misol uchun, agar siz A8 katakchasida B1 kabi bir xil qiymatni belgilashingiz kerak bo'lsa, unda siz formulani yozishingiz mumkin.

=BILOSHIY("B1")

Kazalos by, zachem bu delat? Ved mojno prosto napisat ssylku na yacheyku v drugoy yacheyke. No preimushchestvo etoy funktsiya v tom, chto v ssylku mumkin bo'lgan yuqori darajada lyubuyu stroku, i daje tu, kotoraya sozdaetsya s polzovaniem drugyh formula. Sobstvenno, bu my i delaem v formula.

Funktsiyadan tashqari ADDRESS formulada ham ishlatiladi COLUMN и LINE. Birinchisi, allaqachon ma'lum bo'lgan satr va ustun raqamlari asosida hujayraning manzilini qaytaradi. Bu erda to'g'ri ketma-ketlikka rioya qilish muhimdir. Avval satr, keyin esa faqat ustun belgilanadi. Masalan, funktsiya MANZIL(4;2) $B$2 manzilini qaytaradi.

Sleduyushchaya ispolzuemaya yuqori funktsiya – bu COLUMN. Bu erda formula ma'lum bir ma'lumotnomadan ustun raqamini olishi uchun kerak. Misol uchun, agar siz ushbu funktsiyaning qavslarida B2 parametridan foydalansangiz, u 2 raqamini qaytaradi, chunki ikkinchi ustun B ustunidir.

Shubhasiz, ROW funksiyasi xuddi shunday ishlaydi, u faqat qator raqamini qaytaradi. 

Va endi biz mavhum misollardan emas, balki yuqorida ishlatilgan juda aniq formuladan foydalanamiz:

=BILOSHIY(MANZIL(USTUN(A1), QAT(A1)))

Bu erda siz buni birinchi navbatda funktsiyada ko'rishingiz mumkin ADDRESS ustun ko'rsatiladi va faqat keyin chiziq. Va bu formulaning ishlash qobiliyatining siri shu erda yashiringan. Esda tutamizki, bu funktsiya oyna ko'rinishida ishlaydi va undagi birinchi argument qator raqamidir. Va ma'lum bo'lishicha, u erda ustunning manzilini yozganimizda, u satr raqamiga aylanadi va aksincha.

To est, esli podytojit:

1. Tegishli funksiyalar yordamida ustun va satr raqamlarini olamiz.
2. Funktsiyadan foydalanish ADDRESS qatorlar ustunlarga aylanadi va aksincha.
3. vazifa BILVOSIT hujayradagi aks ettirilgan ma'lumotlarni ko'rsatishga yordam beradi.

Hammasi shunchalik oddiy ekan!

Transpoze qilish uchun makrosdan foydalanish

Makros - bu kichik dastur. U jarayonni avtomatlashtirish uchun ishlatilishi mumkin. Makroda ba'zi cheklovlar mavjudligini hisobga olish kerak. Maksimal Transpose usuli sizga 65536 ta element bilan ishlash imkonini beradi. Agar bu chegaradan oshib ketsa, bu ma'lumotlarning yo'qolishiga olib keladi.

Vo vsem ostalnom, bu samarali metodlarni avtomatlashtirish, kotoryy smojet znachitelno oblegchit hayot.

Misol uchun, mumkin bo'lgan napisat takoy kod, kotoryy budet menyat mestami stroki va kolonky.

Sub TransposeColumnsRows()

    SourceRange diapazon sifatida xira

    DestRangeni diapazon sifatida xiralashtiring

    SourceRange = Application.InputBox (Soʻrov:=»Iltimos, koʻchirish uchun diapazonni tanlang», Sarlavha:=»Qatorlarni ustunlarga oʻtkazish», Turi:=8)

    DestRange = Application.InputBox ni o'rnating (Prompt:=»Maqsad diapazonining yuqori chap katakchasini tanlang», Sarlavha:=»Qatorlarni ustunlarga o'tkazish», Turi:=8)

    SourceRange.Copy

    DestRange.Select

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

    Application.CutCopyMode = False

End Sub

Hech qanday noma'lum va dasturmirovanii oso net, nichego strashno. Mojno vospolzovatsya opisannymi vyshe sposobami. A potom uchitsya novomu po mere osvoeniya starogo.