Formula generik
(=COLUMN(range)-COLUMN(range.firstcell)+1)
Ringkasan
Untuk mendapatkan sekumpulan nombor lajur relatif dalam julat, anda boleh menggunakan formula larik berdasarkan fungsi COLUMN.
Dalam contoh yang ditunjukkan, formula larik dalam B4: H4 adalah:
(=COLUMN(B4:H4)-COLUMN(B4)+1)
Pada lembaran kerja, ini mesti dimasukkan sebagai formula susunan berbilang sel menggunakan Control + Shift + Enter
Ini adalah formula kuat yang akan terus menghasilkan nombor relatif walaupun lajur dimasukkan di hadapan julat.
Penjelasan
Fungsi COLUMN pertama menghasilkan susunan 7 nombor seperti ini:
(2,3,4,5,6,7,8)
Fungsi COLUMN kedua menghasilkan array dengan hanya satu item seperti ini:
(2)
yang kemudian dikurangkan dari array pertama untuk menghasilkan:
(0,1,2,3,4,5,6)
Akhirnya, 1 ditambahkan untuk mendapatkan:
(1,2,3,4,5,6,7)
Dengan julat yang dinamakan
Anda boleh menyesuaikan formula ini untuk digunakan dengan julat bernama. Contohnya, dalam contoh di atas, jika anda membuat julat "data" bernama untuk B4: H4, anda boleh menggunakan formula ini untuk menghasilkan nombor lajur:
(=COLUMN(data)-COLUMN(INDEX(data,1,1))+1)
Anda akan menemui formula ini dalam formula larik lain yang perlu memproses data lajur demi lajur.
Dengan KEBERSIHAN
Dengan fungsi SEQUENCE formula untuk mengembalikan lajur baris relatif untuk julat adalah mudah:
=SEQUENCE(COLUMNS(range))
Fungsi COLUMNS memberikan jumlah lajur, yang dikembalikan ke fungsi SEQUENCE. SEQUENCE kemudian membina susunan nombor, bermula dengan nombor 1. Oleh itu, mengikuti contoh asal di atas, formula di bawah mengembalikan hasil yang sama:
=SEQUENCE(COLUMNS(B4:H4)) // returns (1;2;3;4;5;6;7)
Catatan: formula SEQUENCE adalah fungsi array dinamik baru yang hanya terdapat di Excel 365.
