Contents [show]
Semalam dalam marathon 30 fungsi Excel dalam 30 hari kami mengira bilangan lajur dalam julat menggunakan fungsi tersebut COLUMNS (NUMBERCOLUMN), dan kini tiba masanya untuk sesuatu yang lebih diminati.
Pada hari ke-13 maraton, kami akan menumpukan kepada kajian fungsi PENGANGKUTAN (TRANSP). Dengan fungsi ini, anda boleh memutarkan data anda dengan menukar kawasan menegak kepada yang mendatar dan sebaliknya. Adakah anda mempunyai keperluan sedemikian? Bolehkah anda melakukan ini menggunakan sisipan khas? Bolehkah fungsi lain melakukannya?
Jadi, mari kita beralih kepada maklumat dan contoh mengenai fungsi tersebut PENGANGKUTAN (TRANSP). Jika anda mempunyai maklumat atau contoh tambahan, sila kongsikannya dalam ulasan.
Fungsi 13: TRANSPOSE
fungsi PENGANGKUTAN (TRANSPOSE) mengembalikan julat mendatar sel sebagai julat menegak, atau sebaliknya.
Bagaimanakah fungsi TRANSPOSE boleh digunakan?
fungsi PENGANGKUTAN (TRANSP) boleh menukar orientasi data, serta berfungsi bersama-sama dengan fungsi lain:
- Tukar susun atur mendatar data kepada menegak.
- Tunjukkan jumlah gaji terbaik dalam beberapa tahun kebelakangan ini.
Untuk menukar orientasi data tanpa membuat pautan ke data asal:
- Penggunaan tampal Khas (Tampal Khas) > Transpose (Transpose).
Sintaks TRANSPOSE (TRANSP)
fungsi PENGANGKUTAN (TRANSPOSE) mempunyai sintaks berikut:
TRANSPOSE(array)
ТРАНСП(массив)
- pelbagai (tatasusunan) ialah tatasusunan atau julat sel yang hendak ditukar.
Perangkap TRANSPOSE (TRANSPOSE)
- fungsi PENGANGKUTAN (TRANSPOSE) mesti dimasukkan sebagai formula tatasusunan, dengan menekan Ctrl + Shift + Enter.
- Julat yang akan terhasil daripada penjelmaan oleh fungsi PENGANGKUTAN (TRANSPOSE) mesti mempunyai bilangan baris dan lajur yang sama seperti julat asal masing-masing mempunyai lajur dan baris.
Contoh 1: Menukar Data Mendatar kepada Data Menegak
Jika data mendatar dalam helaian Excel, anda boleh menggunakan fungsi tersebut PENGANGKUTAN (TRANSPOSE) untuk menukarnya kepada kedudukan menegak, tetapi di tempat yang berbeza pada helaian. Sebagai contoh, dalam jadual penanda aras akhir, susunan menegak akan menjadi lebih mudah. Menggunakan fungsi PENGANGKUTAN (TRANSPOSE), anda boleh merujuk data mendatar asal tanpa mengubah lokasinya.
Untuk menukar julat mendatar 2 × 4 ke dalam julat menegak 4 × 2:
- Pilih 8 sel di mana anda ingin meletakkan julat menegak yang terhasil. Dalam contoh kami, ini ialah sel B4:C7.
- Masukkan formula berikut dan ubahnya menjadi formula tatasusunan dengan mengklik Ctrl + Shift + Enter.
=TRANSPOSE(B1:E2)
=ТРАНСП(B1:E2)
Pendakap kerinting akan ditambah secara automatik pada permulaan dan penghujung formula untuk menunjukkan bahawa formula tatasusunan telah dimasukkan.
Bukan PENGANGKUTAN (TRANSPOSE), Anda boleh menggunakan fungsi lain untuk mengubah data, contohnya, INDEKS (INDEX). Ia tidak memerlukan anda memasukkan formula tatasusunan dan anda tidak perlu memilih semua sel dalam kawasan sasaran semasa mencipta formula.
=INDEX($B$2:$E$2,,ROW()-ROW(C$4)+1)
=ИНДЕКС($B$2:$E$2;;СТРОКА()-СТРОКА(C$4)+1)
Contoh 2: Tukar orientasi tanpa pautan
Jika anda hanya mahu menukar orientasi data anda tanpa menyimpan rujukan kepada data asal, anda boleh menggunakan Tampal Khas:
- Pilih data sumber dan salinnya.
- Pilih sel kiri atas kawasan yang anda mahu letakkan hasilnya.
- Pada tab Lanjutan Laman Utama (Laman Utama) klik pada menu lungsur turun arahan tampal (Sisipkan).
- pilih Transpose (Transpose).
- Padamkan data asal (pilihan).
Contoh 3: Jumlah Gaji Terbaik Pada Tahun-tahun Lalu
fungsi PENGANGKUTAN (TRANSP) boleh digunakan dalam kombinasi dengan ciri lain, seperti dalam formula yang menakjubkan ini. Ia telah disiarkan oleh Harlan Grove dalam Blok Berita Excel dalam perbincangan tentang pengiraan jumlah gaji terbaik untuk 5 tahun yang lalu (berturut-turut!).
=MAX(MMULT(A8:J8, --(ABS(TRANSPOSE(COLUMN(A8:J8))-COLUMN(OFFSET(A8:J8,0,0,1,COLUMNS(A8:J8)-Number+1))-(Number-1)/2)
=МАКС(МУМНОЖ(A8:J8; --(ABS(ТРАНСП(СТОЛБЕЦ(A8:J8))-СТОЛБЕЦ(СМЕЩ(A8:J8;0;0;1;ЧИСЛСТОЛБ(A8:J8)-Number+1))-(Number-1)/2)
Как можно понять по фигурным скобкам в строке формул – это формула массива. Ячейка A5 названа nombor и в этом примере число 4 введено, как значение для количества лет.
Формула проверяет диапазоны, чтобы увидеть достаточно ли в них последовательных столбцов. Результати проверки (1 atau 0) умножаются на значения ячеек, чтобы получить суммарный объём заработной платы.
Для проверки результата на рисунке ниже в строке под значениями зарплат показаны суммарные значения для каждой стармтой стармтой стармтой альное значение выделено жёлтым. Это более долгий путь к тому же результату, что предыдущая формула массива получает в одной ячейке!
