Terbalikkan data dalam Excel (terbalikkan susunan data dalam lajur/baris)

Kadangkala, anda mungkin perlu menterbalikkan data dalam Excel, iaitu, membalikkan susunan data dalam set data menegak dan membalikkan susunan data dari kiri ke kanan dalam set data mendatar.

Sekarang, jika anda fikir mesti ada fungsi terbina dalam dalam Excel untuk melakukan ini, saya khuatir anda akan kecewa.

Walaupun terdapat beberapa cara untuk membalikkan data dalam Excel, tiada kefungsian terbina dalam.Tetapi anda boleh melakukannya dengan mudah dengan helah pengisihan mudah, formula atau VBA.

Dalam tutorial ini, saya akan menunjukkan kepada anda cara membalikkan data dalam baris, lajur dan jadual secara terbalik dalam Excel.

Jadi mari kita mulakan!

Balikkan data menggunakan SORT dan lajur tambahan

Salah satu cara paling mudah untuk membalikkan susunan data dalam Excel ialah menggunakan lajur pembantu dan kemudian menggunakan lajur pembantu itu untuk mengisih data.

Balikkan data secara menegak (urutan terbalik)

Katakan anda mempunyai lajur seperti di bawahNamakan set data, dan anda mahu membalikkan data ini:

Berikut ialah langkah-langkah untuk membalikkan data secara menegak:

1. Dalam lajur bersebelahan, masukkan "Pembantu" sebagai tajuk lajur
2. Dalam lajur bantuan, masukkan satu siri nombor (1, 2, 3, dsb.).Anda boleh melakukan ini dengan cepat menggunakan kaedah yang ditunjukkan di sini
3. Pilih keseluruhan set data, termasuk lajur tambahan
4. Klik tab Data
5. Klik ikon isihan
6. Dalam kotak dialog Isih, pilih Pembantu daripada menu lungsur Isih Mengikut
7. Dalam menu lungsur turun pesanan, pilih "Besar ke Kecil"
8. Klik OK

Langkah di atas akan mengisih data berdasarkan nilai lajur pembantu, yang juga akan menyebabkan nama dalam data berada dalam susunan terbalik.

Jangan ragu untuk melepaskan lajur bantuan apabila anda selesai.

Dalam contoh ini saya menunjukkan kepada anda cara membalikkan data apabila terdapat hanya satu lajur, tetapi anda boleh menggunakan teknik yang sama jika anda mempunyai keseluruhan jadual.Cuma pastikan anda memilih keseluruhan jadual, kemudian gunakan lajur pembantu untuk mengisih data dalam tertib menurun.

Balikkan data secara mendatar

Anda juga boleh membalikkan data secara mendatar dalam Excel dengan cara yang sama.

Excel mempunyai pilihan untuk mengisih data secara mendatar menggunakan kotak dialog Isih (ciri Isih Kiri ke Kanan).

Katakan anda mempunyai jadual seperti yang ditunjukkan di bawah dan anda ingin membalikkan data ini secara mendatar.

Berikut adalah langkah-langkah untuk melakukan ini:

1. Dalam baris di bawah, masukkan "Pembantu" sebagai tajuk baris
2. Dalam baris pembantu, masukkan satu siri nombor (1, 2, 3, dsb.).
3. Pilih keseluruhan set data, termasuk baris tambahan
4. Klik tab Data
5. Klik ikon isihan
6. Dalam kotak dialog Isih, klik butang Pilihan.
7. Dalam dialog yang terbuka, klik Isih Kiri ke Kanan
8. Klik OK
9. Dalam senarai juntai bawah Isih Mengikut, pilih Baris 3 (atau apa sahaja yang mempunyai lajur pembantu anda)
10. Dalam menu lungsur turun pesanan, pilih "Besar ke Kecil"
11. Klik OK

Langkah-langkah di atas akan membalikkan keseluruhan jadual secara mendatar.

Apabila selesai, anda boleh memadamkan baris pembantu.

Gunakan Excel untuk menjadikan formula terbalikflip data

Microsoft 365 mempunyai beberapa formula baharu yang menjadikannya sangat mudah untuk membalikkan susunan lajur atau jadual dalam Excel.

Dalam bahagian ini, saya akan menunjukkan kepada anda cara melakukan ini menggunakan formula SORTBY (jika anda menggunakan Microsoft 365) atau formula INDEX (jika anda tidak menggunakan Microsoft 365)

Gunakan fungsi SORTBY (tersedia dalam Microsoft 365)

Katakan anda mempunyai jadual yang kelihatan seperti ini dan anda ingin membalikkan data dalam jadual itu:

Untuk melakukan ini, mula-mula, salin pengepala dan letakkannya di tempat yang anda mahu selak meja

Sekarang gunakan formula berikut di bawah sel pengepala paling kiri:

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

Formula di atas mengisih data dan mengisih berdasarkan hasil fungsi ROW.

Dalam kes ini, fungsi ROW akan mengembalikan tatasusunan nombor yang mewakili nombor baris antara julat yang ditentukan (dalam contoh ini, satu siri nombor, seperti 2, 3, 4, dsb.).

Oleh kerana parameter ketiga formula ini ialah -1, ia memaksa formula untuk mengisih data dalam tertib menurun.

Rekod dengan nombor baris tertinggi akan berada di bahagian atas dan rekod dengan nombor peraturan terendah akan berada di bahagian bawah, pada asasnya membalikkan susunan data.

Setelah selesai, anda boleh menukar formula kepada nilai untuk mendapatkan jadual statik.

Gunakan fungsi indeks

Jika anda tidak boleh menggunakan fungsi SORTBY, jangan risau - anda boleh menggunakan fungsi INDEX yang menakjubkan.

Katakan anda mempunyai set data nama seperti yang ditunjukkan di bawah dan anda ingin membalikkan data ini.

Berikut ialah formula untuk melakukan ini:

=INDEX($A$2:$A$12,ROWS(A2:$A$12))

 Bagaimanakah formula ini berfungsi?

Formula di atas menggunakan fungsi INDEX, yang akan mengembalikan nilai dari sel berdasarkan nombor yang dinyatakan dalam parameter kedua.

Keajaiban sebenar berlaku apabila saya menggunakan parameter kedua fungsi ROWS.

Oleh kerana saya mengunci bahagian kedua rujukan dalam fungsi ROWS, dalam sel pertama ia akan mengembalikan bilangan baris antara A2 dan A12, iaitu 11.

Tetapi apabila ia menuruni baris, rujukan pertama akan bertukar kepada A3, kemudian A4, dan seterusnya, manakala rujukan kedua akan kekal seperti sedia ada kerana saya telah menguncinya dan menjadikannya mutlak.

Apabila kita turun, hasil fungsi ROWS akan berkurangan sebanyak 1, daripada 11 kepada 10 kepada 9, dan seterusnya.

Oleh kerana fungsi INDEX mengembalikan nilai berdasarkan nombor dalam hujah kedua, ini akhirnya memberikan kami data dalam susunan terbalik.

Anda boleh menggunakan formula yang sama walaupun terdapat berbilang lajur dalam set data.Walau bagaimanapun, anda mesti menentukan parameter kedua, yang akan menentukan nombor lajur dari mana data perlu diperolehi.

Katakan anda mempunyai set data yang kelihatan seperti ini dan anda ingin membalikkan susunan keseluruhan jadual:

Berikut ialah formula yang akan melakukan ini untuk anda:

=INDEX($A$2:$B$12,ROWS(A2:$A$12),COLUMNS($A$2:A2))

Berikut ialah formula yang serupa di mana saya juga telah menambah parameter ketiga yang menentukan nombor lajur yang nilainya harus diambil.

Untuk menjadikan formula ini dinamik, saya menggunakan fungsi COLUMNS, yang terus menukar nilai lajur daripada 1 kepada 2 kepada 3 semasa anda menyalinnya ke kanan.

Setelah selesai, anda boleh menukar formula kepada nilai untuk memastikan hasil statik.

Nota: Apabila anda menggunakan formula untuk membalikkan susunan set data dalam Excel, ia tidak mengekalkan pemformatan asal.Jika anda juga memerlukan pemformatan asal data yang diisih, anda boleh menggunakannya secara manual atau menyalin dan menampal pemformatan daripada set data asal ke dalam set data diisih baharu

Selak Data Menggunakan VBA

Jika membalikkan data dalam Excel adalah sesuatu yang anda perlu lakukan dengan kerap, anda juga boleh mencuba kaedah VBA.

Dengan kod makro VBA, anda boleh menyalin dan menampalnya sekali dalam buku kerja dalam editor VBA, kemudian menggunakannya semula berulang kali dalam buku kerja yang sama.

Anda juga boleh menyimpan kod dalam buku kerja makro peribadi atau sebagai tambahan Excel dan boleh menggunakannya dalam mana-mana buku kerja pada sistem anda.

Di bawah ialah kod VBA untuk menyelak data yang dipilih secara menegak dalam lembaran kerja.

Sub FlipVerically()

Malapkan TopRow Sebagai Varian
Malapkan LastRow Sebagai Varian
Malapkan StartNum Sebagai Integer
Malapkan EndNum Sebagai Integer

Aplikasi.ScreenUpdating = Palsu

Nombor Mula = 1
EndNum = Pemilihan. Baris. Kiraan

Lakukan Semasa StartNum < EndNum
TopRow = Pilihan. Baris(StartNum)
LastRow = Pilihan. Rows(EndNum)
Selection.Rows(EndNum) = TopRow
Selection.Rows(StartNum) = LastRow
StartNum = StartNum + 1
EndNum = EndNum - 1
Gelung

Aplikasi.ScreenUpdating = Benar
Akhir Sub

Untuk menggunakan kod ini, anda perlu memilih set data yang anda ingin undurkan dahulu (tidak termasuk pengepala), kemudian jalankan kod ini.

Bagaimanakah kod ini berfungsi?

Kod di atas mula-mula mengira jumlah baris dalam set data dan memberikannya kepada pembolehubah EndNum.

Kemudian ia menggunakan gelung Do While untuk mengisih data.

Cara ia mengisih data ini adalah dengan mengambil baris pertama dan terakhir dan menukarnya.Kemudian ia bergerak ke baris kedua baris kedua terakhir dan kemudian menukarnya.Kemudian ia bergerak ke baris ketiga dari baris ketiga ke baris terakhir, dan seterusnya.

Gelung berakhir apabila semua pengisihan selesai.

Ia juga menggunakan sifat Application.ScreenUpdating dan menetapkannya kepada FALSE apabila kod berjalan, kemudian menukarnya kembali kepada TRUE apabila kod selesai dijalankan.

Ini memastikan bahawa anda tidak melihat perubahan yang berlaku pada skrin dalam masa nyata, dan juga mempercepatkan proses.

Bagaimana untuk menggunakan kod?

Salin dan tampal kod ini dalam editor VB seperti berikut:

1. Buka fail Excel di mana anda ingin menambah kod VBA
2. Tahan ALT dan tekan F-11 (anda juga boleh pergi ke tab pembangun dan klik ikon Visual Basic)
3. Dalam editor Visual Basic yang terbuka, akan terdapat Project Explorer di sebelah kiri editor VBA.Jika anda tidak melihatnya, klik tab View, kemudian klik Project Explorer
4. Klik kanan pada mana-mana objek buku kerja yang anda ingin tambahkan kod
5. Pergi ke pilihan Sisipkan dan klik pada Modul.Ini akan menambah modul baharu pada buku kerja
6. Klik dua kali ikon modul dalam Project Explorer.Ini akan membuka tetingkap kod untuk modul itu
7. Salin dan tampal kod VBA di atas ke dalam tetingkap kod

Untuk menjalankan kod makro VBA, mula-mula pilih set data untuk diselak (tidak termasuk pengepala).

Dengan data yang dipilih, pergi ke editor VB dan klik butang main hijau dalam bar alat, atau pilih mana-mana baris dalam kod dan tekan F5

Jadi, ini adalah beberapa cara anda boleh menyelak data dalam Excel (iaitu, membalikkan susunan set data).

Semua kaedah yang saya bincangkan dalam tutorial ini (formula, fungsi SORT, dan VBA) boleh digunakan untuk membalikkan data secara menegak dan mendatar (anda perlu melaraskan formula dan kod VBA dengan sewajarnya untuk membalikkan data mendatar).

Saya harap anda mendapati tutorial ini berguna.